XML Publisher Reporting (oracle Apps 11i)

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 in the Query Dialog of Data Model in Report Builder.

3. Save the report as .rdf on desktop.
4. Transfer the file to the Database through the Binary Option to the Corresponding application Folder.
5. Go to Apps 

1. Create Executable

2. Define Concurrent Program 

      Navigate to System Administrator>concurrent>program>define

Output Format XML as shown in the below screen

 If Parameter exists then

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

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

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