BI XML Publisher Reporting Info (oracle Apps R12)

-

BI XML Publisher Reporting (oracle Apps R12) :

Steps :

  1. Generate the Query in TOAD and omit the duplicates.

SELECT fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date,
       fu.email_address, fa.application_name, fr.responsibility_id,
       fa.application_id, hou.NAME
  FROM fnd_profile_option_values fpov,
       fnd_profile_options fpo,
       hr_operating_units hou,
       fnd_responsibility_tl fr,
       fnd_application_tl fa,
       fnd_user_resp_groups_direct furg,
       fnd_user fu
 WHERE fpov.profile_option_id = fpo.profile_option_id
   AND fpov.application_id = fpo.application_id
   AND fpo.profile_option_name = 'ORG_ID'
   AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
   AND fpov.level_value = fr.responsibility_id
   AND fa.application_id = fr.application_id
   AND fpov.level_id = 10003
   AND fpov.level_value = fr.responsibility_id
   AND furg.user_id = fu.user_id
   AND furg.responsibility_id = fr.responsibility_id
   AND furg.start_date <= NVL(:todate,sysdate)


  1. Copy and paste Query in the DATAQUERY>Sqlstatement  in the xml template file as  shown below
<dataQuery>
-<sqlStatement name="Q_COMPANY">
<![CDATA[  SELECT fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date,
       fu.email_address, fa.application_name, fr.responsibility_id,
       fa.application_id, hou.NAME
  FROM fnd_profile_option_values fpov,
       fnd_profile_options fpo,
       hr_operating_units hou,
       fnd_responsibility_tl fr,
       fnd_application_tl fa,
       fnd_user_resp_groups_direct furg,
       fnd_user fu
 WHERE fpov.profile_option_id = fpo.profile_option_id
   AND fpov.application_id = fpo.application_id
   AND fpo.profile_option_name = 'ORG_ID'
   AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
   AND fpov.level_value = fr.responsibility_id
   AND fa.application_id = fr.application_id
   AND fpov.level_id = 10003
   AND fpov.level_value = fr.responsibility_id
   AND furg.user_id = fu.user_id
   AND furg.responsibility_id = fr.responsibility_id
   AND furg.start_date <= NVL(:todate,sysdate)
ORDER BY fu.user_name ]]>

Actually the XML template file has one Main Section is Data Template.
And this Data Template contains the multiple Sections. These sections are
I. Parameter
II. Lexical
III. Data Query
IV. Data Trigger
V. Data Structure

And for each section there will be a start and end. Eg. <Parameter> -- it is start of the section.
</Parameter> -- It is an end of the section

Parameter section
 In this section we declare all the parameters as shown below :
  
  <parameters> -- Starting of the Paramerter Section
  <parameter name="P_TITLE" dataType="character" />
  <parameter name="P_ACTIVE_INACTIVE" dataType="character" />
  <parameter name="P_COA_ID" dataType="number" />
  <parameter name="P_CATEGORY_FROM" dataType="character" />
  <parameter name="P_CATEGORY_TO" dataType="character" />
  <parameter name="P_LOCATION" dataType="character" />
  <parameter name="P_ORDERBY" dataType="character" />
  <parameter name="P_ORDERBY_CAT" dataType="character" />
  </parameters> -- END of the Paramerter Section

Lexical
In this section we declare all the lexical parameters as shown below :

<lexicals>
  <lexical type="oracle.apps.fnd.flex.kff.select" name="P_FLEX_CAT" comment="Category" application_short_name="INV" id_flex_code="MCAT" id_flex_num=":P_COA_ID" multiple_id_flex_num="N" code_combination_table_alias="mca" segments="ALL" show_parent_segments="N" output_type="VALUE" />
  <lexical type="oracle.apps.fnd.flex.kff.where" name="P_WHERE_CAT" comment="Flexfield Filter" application_short_name="INV" id_flex_code="MCAT" id_flex_num=":P_COA_ID" code_combination_table_alias="mca" segments="ALL" operator="BETWEEN" operand1=":P_CATEGORY_FROM" operand2=":P_CATEGORY_TO" />
  <lexical type="oracle.apps.fnd.flex.kff.order_by" name="P_ORDERBY_CAT" comment="Flexfield Order By" application_short_name="INV" id_flex_code="MCAT" id_flex_num=":P_COA_ID" code_combination_table_alias="mca" segments="ALL" show_parent_segments="N" />
  </lexicals>


Data Query

This is the section where we write the sql query as shown below. In this section we have one more section which is known as SqlStatement section where actually sql statement is written.

<dataQuery>
- <sqlStatement name="Q_COMPANY">
- <![CDATA[ 
SELECT   gsb.name             c_company
,        fsp.inventory_organization_id c_organization_id
,        gsb.currency_code             GL_CURRENCY
,        gsb.chart_of_accounts_id      STRUCTURE_ACC
,        mdv.structure_id              STRUCTURE_CAT
,        mdv.category_set_id           c_category_set_id
,        flo1.meaning                  c_yes
,        flo2.meaning                  c_no
FROM     gl_sets_of_books              gsb
,        financials_system_parameters  fsp
,        mtl_default_sets_view         mdv
,        fnd_lookups                   flo1
,        fnd_lookups                   flo2
WHERE    gsb.set_of_books_id           = fsp.set_of_books_id
AND      mdv.functional_area_id        = 2
AND      flo1.lookup_type              = 'YES_NO'
AND      flo1.lookup_code              = 'Y'
AND      flo2.lookup_type              = 'YES_NO'
AND      flo2.lookup_code              = 'N'
  ]]>
  </sqlStatement>
</dataQuery>


And for multiple sql quries you can have multiple sections with in the one DATA Query section only as shown below :
- <dataQuery>
- <sqlStatement name="Q_COMPANY">
- <![CDATA[ 
SELECT   gsb.name             c_company
,        fsp.inventory_organization_id c_organization_id
,        gsb.currency_code             GL_CURRENCY
,        gsb.chart_of_accounts_id      STRUCTURE_ACC
,        mdv.structure_id              STRUCTURE_CAT
,        mdv.category_set_id           c_category_set_id
,        flo1.meaning                  c_yes
,        flo2.meaning                  c_no
FROM     gl_sets_of_books              gsb
,        financials_system_parameters  fsp
,        mtl_default_sets_view         mdv
,        fnd_lookups                   flo1
,        fnd_lookups                   flo2
WHERE    gsb.set_of_books_id           = fsp.set_of_books_id
AND      mdv.functional_area_id        = 2
AND      flo1.lookup_type              = 'YES_NO'
AND      flo1.lookup_code              = 'Y'
AND      flo2.lookup_type              = 'YES_NO'
AND      flo2.lookup_code              = 'N'
  ]]>
  </sqlStatement>
- <sqlStatement name="Q_AGENTS">
- <![CDATA[ 
SELECT   hre.full_name  buyer
,        hrl.location_code       location
,        to_char(poa.start_date_active,'DD-MON-YY')   start_date
,        to_char(poa.end_date_active,'DD-MON-YY')     end_date
,decode(INSTR(&P_FLEX_CAT,'may be inconsistent',1),0,&P_FLEX_CAT,null)           FLEX_CAT
FROM     po_agents               poa
,        hr_locations            hrl
,        hr_employees            hre
,        mtl_categories          mca
WHERE    poa.agent_id                     = hre.employee_id
AND      poa.category_id                  = mca.category_id (+)
AND      nvl(poa.location_id,-1)          = hrl.location_id (+)
AND      nvl(hrl.location_code,'~')       = 
         decode(:P_LOCATION, null, nvl(hrl.location_code,'~'), :P_LOCATION)
AND      ((:P_ACTIVE_INACTIVE = 'BOTH')
OR       (:P_ACTIVE_INACTIVE = 'ACTIVE'
            AND sysdate   >= nvl(poa.start_date_active,sysdate - 1)
            AND sysdate   <  nvl(poa.end_date_active,sysdate + 1))
OR       (:P_ACTIVE_INACTIVE = 'INACTIVE'
            AND (sysdate  <  nvl(poa.start_date_active,sysdate - 1)
            OR  sysdate   >= nvl(poa.end_date_active,sysdate + 1))))
AND &P_WHERE_CAT
ORDER BY &P_ORDERBY_CLAUSE
  ]]>
  </sqlStatement>
  </dataQuery>

Data Trigger

This is section where we mention the report triggers. The report triggers are
  1. beforeParameterform
  2. afterParameterform
  3. beforeReport
  4. betweenPages
  5. afterReport
If you want to use the triggers in the XML report then you have to write an procedure in a package and then you have declare as below.
<dataTrigger name="beforeReport" source="XXBI_POXAGLST_PKG.before_report" />

Data Structure

In this section we map the template (.rtf) names with the query column names as shown below :

If you have the groups in it then you have to start a group section and also once the columns are mentioned then close the group section.

<dataStructure>
  <element name="TITLE" value="XXBI_POXAGLST_PKG.get_title" />
  <element name="ACTINACTIVE" value="XXBI_POXAGLST_PKG.get_active_inactive" />
  <element name="CATEGORYFROM" value="XXBI_POXAGLST_PKG.get_category_from" />
  <element name="CATEGORYTO" value="XXBI_POXAGLST_PKG.get_category_to" />
  <element name="LOCATION" value="XXBI_POXAGLST_PKG.get_location" />
  <element name="ORDERBY" value="XXBI_POXAGLST_PKG.get_orderby" />
  <element name="REPORTDATE" value="XXBI_POXAGLST_PKG.get_report_date" />
- <group name="G_COMPANY" source="Q_COMPANY">
  <element name="C_COMPANY" value="C_COMPANY" />
  <element name="GL_CURRENCY" value="GL_CURRENCY" />
- <group name="G_AGENTS" source="Q_AGENTS">
  <element name="BUYER" value="BUYER" />
  <element name="LOCATION" value="LOCATION" />
  <element name="FLEX_CAT" value="FLEX_CAT" />
  <element name="START_DATE" value="START_DATE" />
  <element name="END_DATE" value="END_DATE" />
  </group>
  </group>
  </dataStructure>


And lastly you have to close the datatemplate section as this </dataTemplate>

  1. Save that file as .xml on desktop.
  2. Transfer the file to the Database through the Binary Option to the Corresponding application Folder.
  3. Go to Apps 

  1. Define Concurrent Program 

      Navigate to System Administrator>concurrent>program>define

Executable

Name    : XDODTEXE
Method : Java Concurrent Program (Common for all the XML BI Publisher programs)


Output Format XML as shown in the below screen
 If Parameter exists then

  1. Attach the concurrent program to request group.
            Navigate to System Administrator>Security>Responsibility>Request
Press F11
Enter   Group -- GL Concurrent Program Group
            Press Ctrl+F11

           
Click at the Request> Type

And click new(  ) one new column is created and their enter as below

Type                            Name                                       Application
Program           XXXX Users Responsibilities Report    Custom Application

Save it.

  1. Submit the concurrent program view the output an save as .xml.
This .xml file is used when we create the template. At that time we load the data and preview the output.
     
  1. Open an Word  and draw an template as shown below or as per the provided format.

User Responsibilities Report


Circle/Location Name
Application Name
Responsibility Name
User ID
Email ID
Start Date
End Date
F_ONNAME
APPLICATION_NAME
RESPONSIBILITY_NAME
USER_NAME
EMAIL_ID
START_DATE
END_DATE E_ON


And save as XX_USERRESP.rtf

F_ON     click    and then enter <?for-each:G_USER_NAME?>
This is used to print all the records. Just like (cursor for loop)





E_ON  click    and then enter <?end for-each?>
It shows the end of the record.(in cursor end for loop)



And for each column Name below you have enter as below
Circle/Location Name  NAME  to get this you have select the ab which was shown in below screen.  
   




To get that tool in your header you have right click on the header task bar and select Forms as shown in the below screen.

In this way you have to repeat for all the columns. Then save with .rtf extension.
    
  1. Go to Oracle Applications
       Navigate to XML Publisher Administrator>Data Definition

Click    Create Data Definition

Attach the .xml file at the Data Template.


Please Enter the details as mentioned in the below screen.
  1. Navigate to XML Publisher Administrator>Template
Click    Create Template


Please Enter the details as mentioned in the below screen.

And attach the .rtf file at File section and also
enter
Language – English
Territory – United States

And also you can specify the output format here. Which is the LOV where you can have excel
Pdf
.
.
Etc.

Comments

Post a Comment

Popular posts from this blog

Oracle Purchasing Module Step by Step in R12

-
Image
PURCHASING  All the Purchasing related activity information will be declared in Purchasing              Procure 2 Pay Cycle : Requisition à RFQ à Quotation à Quotation Analysis à Purchasing à Receiving à Invoice  à  Payment à Reconciliation PO-Integration :  PO  is integrated by GL, AP, I Supplier, Procurement, and Project Accounting  PO  Responsibility             Step1: Define  PO  Responsibility                         Nav: System administrator à Security à Responsibility à Define             Step2: Assign  PO  options to the PO Responsibility                         Nav: System administrator à Profile à system             Step3: Assign  PO  Responsibility to User                         Nav: System administrator à security à user à define Note:  PO  is including some of i.e. Options, Supplier, Item, Expenses, and Purchasing . Options : Options nothing but a Controlling future at the operating unit level.                         Th
Read more

Sub Ledger Accounting SLA (Complete Functional Information)

-
Image
SubLegder Accounting in R12 SLA Part -1 In these article, we will explore the very basics of SLA and why it exists. Firstly, what exactly does SLA do? SLA is a module which now sits between the SubLedgers like AP/AR etc and the General Ledger. Have a look at this diagram below.  As you will notice, SLA can act as a mediator between the subledgers and Oracle General Ledger. Before we progress, some terminologies of R12 must be revisited. In 11i we had set of books, and in R12 we call them Ledgers. Likewise in R12 we also have secondary ledgers and reporting ledgers. Hence from 11i perspective think of Ledger as Set of Books. As for Subledger, a Subledger is nothing but a module like AP/AR/PO/Inventory etc. In the diagram below, the second scenario is explained whereby let's say that payables module generates a charge account segment combination for an invoice distribution line as A.B.C.D. In such case, if SLA module is not customized, then the very same A.B.C.D
Read more

Difference between ATO and PTO in oracle apps

-
Difference between ATO and PTO in oracle apps 1) MAKE TO ORDER: This is based on Customer specification, you make the product and supply it to them. This is applicable for Engineering items or any Product which you can make based on Customer order Example: Engineering Items 2) ASSEMBLE TO ORDER: This is you have items in parts and it needs to be assembled according to customer requirements For example you have Laptop coming with standard configuration of 160GB Hard disk and 1 GB RAM. But there are customer orders coming in asking for 200GB Hard Disk and 2 GB RAM and if you can assemble them to suit Customer Requirement 3) CONFIGURE TO ORDER: This is Configuring the Product itself to suit Customer requirements Example. The Thread Manufacturing unit Machineries are configured. I have seen Laxmi Machine Works in INDIA which manufactures textile Machineries configure the machineries based on No.of spindles 4) PICK TO ORDER: This is same as Computer Example in ATO. But the  difference is
Read more