Method One: MetaInfo.class

The version of a specific Java Class has a one to one relationship with the currently installed version of Oracle XML Publisher.

/home/applprod >strings $OA_JAVA/oracle/apps/xdo/common/MetaInfo.class|grep -i ‘XML Publisher’

Oracle XML Publisher 5.6.3

/home/applprod >

Version 5.6.3 ships with update pack 12.1.3, which is Patch 8919491

“Patch R12.ATG_PF.B.delta.3: Oracle Applications Technology 12.1.3 Product Family Release Update Pack”

Method Two: SQL Query

SELECT DECODE (bug_number
, ‘3554613’, ‘4.5.0’
, ‘3263588’, ‘XDO.H’
, ‘3822219’, ‘5.0.0’
, ‘4236958’, ‘5.0.1’
, ‘4206181’, ‘5.5.0’
, ‘4561451’, ‘5.6.0’
, ‘4905678’, ‘5.6.1’
, ‘5097966’, ‘5.6.2’
, ‘5472959’, ‘5.6.3’) PATCH, bug_number
FROM ad_bugs
WHERE bug_number IN
(‘3554613′
, ‘3263588’
, ‘3822219’
, ‘4236958’
, ‘4206181’
, ‘4561451’
, ‘4905678’
, ‘5097966’
, ‘5472959’);

Method Three: Output PDF of the report (Document Properties)

doc_properties

 

Developing XML Publisher Report – Using Data Template as Data Source

Background: 
Developing XML Publisher Report – Using Data Template(.xml) as Data Source and Template(.rtf) as Layout.
Note that, we can use .rdf file as data source. But for this demo we are using Date XML Template.
Prerequisite for the below Example:

1. Create a table
CREATE TABLE demo_products
(  product_code   NUMBER,
   product_name   VARCHAR2 (100));

2. Insert Values
INSERT INTO demo_products
     VALUES (569, ‘Oracle Cost Management’);
3. Issue Commit

Step1: Define Data Template:

The data template is the method by which you communicate your request for data to the data engine.
The data template is an XML document that consists of four basic sections:  

  • Define parameters: In which parameters are declared in child <parameter> elements
  • Define triggers:
  • Define data query: In which the SQL queries are defined in child <sqlStatement> elements
  • Define data structure: In which the output XML structure is defined
 Create Data Template: (Save this file as XXDPDT.xml)
<?xml version=”1.0″ encoding=”UTF-8″?>
<dataTemplate name=”demoProductsDT” description=”Demo Products Details” version=”1.0″>
   <parameters>
     <parameter name=”p_product_id” datatype=”number”/>
   </parameters>
  <dataQuery>
    <sqlStatement name=”DQ”>
         <![CDATA[ SELECT product_code, product_name FROM demo_products 
                            WHERE product_code = NVL(:p_product_id,product_code) ]]>
    </sqlStatement>
  </dataQuery>
  <dataStructure>
    <group name=”G_DP” source=”DQ”>
      <element name=”PRODUCT_CODE” value=”product_code”/>
      <element name=”PRODUCT_NAME” value=”product_name”/>
    </group>
  </dataStructure>
</dataTemplate>
  • This Data Template selects the product details from the demo_products table. It uses a bind parameter to find the product name against the product code.
  • For each bind parameter in the query , we need to define a Parameter in the Concurrent Program

Step 2: Create Data Definition & Associate with Data Template
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
Screen 1 : Create Data Definition
       Enter the data definition Details and click on Apply. Note down the Code. 
       The code should be used as the short name of the concurrent program.

Screen 2: Associate Data Template
       View Data Definition
Click on ‘Add File’ button to upload Data Template file that was created through step 1
Screen 3: Data Definiton
       Data Template is associated with Data Definition

Step 3: Define a Concurrent Program to generate the Data XML output. 
Note:
1. Output format should be XML
2. Short Name in the concurrent program and Code in the data definition should be same.
Screen 2: Concurrent Program – Parameters
For each parameter in the Data Template, define a parameter in the concurrent program.
The Data Template parameter name should match the concurrent program parameter token
Note:
Token is p_product_id. This is the bind parameter we have used in date template. For every bind parameter used in the data template, we have to define parameter in the concurrent program.
Screen 3: Associate the Concurrent Program to a request group.
Screen 4: Execute the concurrent program “Product Demo Report”and click on the output button get the Data XML. Save the XML file. We will use it to generate the RTF Template.
Screen 5: Concurrent Program Output
Note:
We are getting the output in xml because we didn’t define template & associated yet.

Step 4: Define the RTF Template using the Generated Data XML

Pre-requisite : Install XML Publisher Desktop
After installation following Menus & Toolbars gets added to the MS Word.

Load XML Data generated by Concurrent Program
Data -> Load XML Data
Message after loading the data
Using the Table Wizard as below to create the ‘Table Report Format’ with the columns of demo_products.

Final Output layout look like this.

Save this file with .rtf extension
Step 5: Registering the Template with BI Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template

Step 6: Run the concurrent program to see the output

Note:

As already mentioned output format can be anything. Here it is pdf. We can select format that we want at the runtime.

References:
http://www.oracle.com/technology/products/xml-publisher/index.htm
http://www.oracle.com/technetwork/middleware/bi-publisher/overview/index.html
http://xdo.us.oracle.com
Oracle® XML Publisher Administration and Developer’s Guide

FAQ:
What is XDODTEXE used in the Executable section of Concurrent Program?

XDODTEXE is a BI Publisher Data Template Executable. The purpose of this executable is to identify data template file (.xml) and execute the data template to generate the raw xml data, that later can be used by BI Publisher formatting engine to format as as per the layout (RTF, PDF etc).
This executable will be used by all the BI Publisher reports (Concurrent Program) which are using Data Template to generate the xml data. 

Pre-requisites :
Step1: Data Definition and Template to be created
Step2: Concurrent program needs to be created

Steps To Create the PL/SQL package:

1. Initialize the  Session Specific variable using fnd_global.APPS_INITIALIZE
2. Set The BI publisher report layout Before submitting the concurrent program
3. Submit the Concurrent Program

Code: (Tested in R12.1.1 )

DECLARE
l_user_id              fnd_user.user_id%TYPE;
l_resp_id              fnd_responsibility.responsibility_id%TYPE;
l_resp_appl_id         fnd_application.application_id%TYPE;
l_set_layout           boolean;
l_request_id           NUMBER;
l_phase                VARCHAR2 (100);
l_status               VARCHAR2 (100);
l_dev_phase            VARCHAR2 (100);
l_dev_status           VARCHAR2 (100);
l_wait_for_request     boolean := FALSE;
l_get_request_status   boolean := FALSE;
Output_layout_failed EXCEPTION;
request_submission_failed EXCEPTION;
request_completion_abnormal EXCEPTION;
BEGIN
l_request_id := NULL;


— Get the Apps Intilization Variables

SELECT   fnd.user_id, fresp.responsibility_id, fresp.application_id
INTO   l_user_id, l_resp_id, l_resp_appl_id
FROM   fnd_user fnd, fnd_responsibility_tl fresp
WHERE   fnd.user_name = ‘OEAG’
AND fresp.responsibility_name = ‘Custom XML Reports’;


–Initialize the Apps Variables

fnd_global.APPS_INITIALIZE (user_id        => l_user_id,
resp_id        => l_resp_id,
resp_appl_id   => l_resp_appl_id);

COMMIT;


— Set the Layout  for BI Publisher Report

l_set_layout :=
fnd_request.add_layout (template_appl_name   => ‘XXERP’,
template_code        => ‘XXORACLEERPAPPSGUIDE’,
–Data Template Code
template_language    => ‘en’,
template_territory   => ‘US’,
output_format        => ‘PDF’);

IF l_set_layout
THEN
— Submit the Request

l_request_id :=
fnd_request.submit_request (application   => ‘XXERP’,
program       => ‘XXOEAG_PG’,
description   => ”,
start_time    => SYSDATE,
sub_request   => FALSE,
argument1     => l_person_id);

COMMIT;

IF l_request_id > 0
THEN

–waits for the request completion

l_wait_for_request :=
fnd_concurrent.wait_for_request (request_id   => l_request_id,
interval     => 60,
max_wait     => 0,
phase        => l_phase,
status       => l_status,
dev_phase    => l_dev_phase,
dev_status   => l_dev_status,
MESSAGE      => l_messase);

COMMIT;


— Get the Request Completion Status.

l_get_request_status :=
fnd_concurrent.get_request_status (
request_id       => l_request_id,
appl_shortname   => NULL,
program          => NULL,
phase            => l_phase,
status           => l_status,
dev_phase        => l_dev_phase,
dev_status       => l_dev_status,
MESSAGE          => l_messase
);


–Check the status if It IS completed Normal Or Not

IF UPPER (l_dev_phase) != ‘COMPLETED’
AND UPPER (l_dev_status) != ‘NORMAL’
THEN
RAISE request_completion_abnormal;
END IF;
ELSE
RAISE request_submission_failed;
END IF;
ELSE
RAISE Output_layout_failed;
END IF;

p_request_id := l_request_id;
EXCEPTION
WHEN Output_layout_failed
THEN
DBMS_OUTPUT.put_line (‘Out put Layout failed’);
WHEN request_submission_failed
THEN
DBMS_OUTPUT.put_line (‘Concurrent request submission failed’);
WHEN request_completion_abnormal
THEN
DBMS_OUTPUT.put_line (
‘Submitted request completed with error’ || l_request_id
);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘ERROR:’ || SUBSTR (SQLERRM, 0, 240));
END;

/

You can Create this as PL/SQL Procedure and register into Concurrent Program also.

Steps for Report Development….

1. Develop the report as per client requirement using the Report-6i tool.

2. Move the report (.rdf) file from local machine to respective path in the server. If client have the CUST_TOP then move into Cust_Top else move it to the related Standard Top.Custom Top – CUST_TOP/ 11.5.0/ Report/ US/ .rdf Standard Top – PO_TOP/ 11.5.0/ Report/ US/ .rdf (For PO report)

3. Create “Executable” for that report (After log on to Oracle Applications and Select “System Administrator” responsibility)

4. Create “Concurrent Program” and attach Executable to Conc. Prgm. and define Parameters and Incompatibles if any.Concurrent Program: It is an instance of the executable file along with parameters & incompatible.

5. Create “Request Group” and attach Conc. Prgm. to Request Group.Request Group is nothing but a collection of Conc. Prgms.

6. Create “Responsibility” and attach the Request Group to Responsibility.

7. Create “User” attach Responsibility to User”
             so that the user can run this Conc. Prgm. form the “SRS Window” (Standard Request Submission).

Note: All the Conc. Prgms. should run from the SRS window (Even if we run from Back-End)By default the user has the rights of System Administrator or Application Developer responsibilities

Every form in Oracle Applications contains 3-Types of Fields.
    1. Yellow color – Mandatory2. Green Color – Read-Only3. White Color – Optional

How to Create New User in Oracle Apps?
User Creation: Open IE and type path of Oracle Application in address bar enter User Name and Password

User Name: OPERATIONS  Password: WELCOME

Select System Administrator Responsibility
Select Security / User / Define  Give the required information and Save.
 Switch the user to newly created  User.
 Note: When we create any User, the User stored at FND_USER.

How we can find the Table names of Apps Screen?
We can find through Help => Record-History => FND_USER

How to find Column Name from front end Apps Screen :
To find all column names: Help => Diagnostics => Examine

What is WHO Columns and How many WHO Columns in Oracle Apps?
Ans : 4 types of who columns for each table in Oracle Applications
Created By  Creation Date  Updated By  Updated Date

Oracle Applications Standards for New Report Developments…
For developing a Report in Oracle Applications we should follow three standards.
1. Creation of Bind Variable – P_CONC_REQUEST_ID: We must create a Bind Variable called “P_CONC_REQUEST_ID” (We can’t change this name. It is standard name.). If we run Conc. Prgm. from SRS window, it will give a Request ID. It will get store in ‘P_CONC_REQUEST_ID” automatically. This Bind Variable is useful, when we call another Conc. Prgm. with in a Conc. Prgm.

2. FND SRWINIT in Before Report Trigger:
We call the USER_EXIT (‘FND SRWINIT’) form Before Report Trigger. Syntax is
SRW.USER_EXXIT(‘FND SRWINIT’):
This USER_EXIT is initializing the user profiles in the report trigger i.e., before getting the date from the Database.
Note:
While executing the Conc. Prgm. the system allocate memory for the program which contains all details of user. In above syntax,
SRW.USER_EXIT refers to D2K and purpose of this is, when we want to transfer the control from execution of report to other 3rd generation language and again transfer the control to report execution.
FND SRWINIT refers to Oracle Applications. Purpose of this is to get the “User Profile”.
3. FND SRWEXIT in After Report Trigger:
We call the USER_EXIT (‘FND SRWEXIT’) form After Report Trigger. Syntax is
SRW.USER_EXXIT (‘FND SRWEXIT’):
This USER_EXIT is frees the memory which is occupied by user profiles.