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)

2. 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>

3. Save that file as .xml on desktop.
4. Transfer the file to the Database through the Binary Option to the Corresponding application Folder.
5. 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

2. 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.

3. 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.

     

6. 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.

    

7. 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.

8. 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.