XMLP Data Template Report Process OracleApps88
Step1: Prepare the xml data template
<?xml version = '1.0' encoding = 'UTF-8'?>
<!-- XXCUST_SALES_ORDER_DETAILS - XXCUST Sales Order Details Report -->
<dataTemplate name="XXCUST_SALES_ORDER_DETAILS" description= "XXCUST Sales Order Details
Report" version="1.0">
<properties>
<property name="scalable_mode" value="on" />
</properties>
<parameters>
<parameter name="P_ORDER_NUMBER" dataType="NUMBER"
include_in_output="true"/>
</parameters>
<!--<dataTrigger name="beforeReport"
source="XXCUST_SALES_ORDER_DETAILS_PKG.beforeReport"/>-->
<dataQuery>
<sqlStatement name="Q_DATA">
<![CDATA[
SELECT rcta.org_id,
hou.name OU_NAME,
ooh.order_number,
ooh.flow_status_code order_status,
TRUNC (ooh.ordered_date) ordered_date,
ool.line_number so_line_number,
(ool.ordered_quantity * ool.unit_selling_price) so_extended_price,
msi.segment1 item_number,
hp.party_name customer_name,
hca.account_number,
rcta.trx_date invoice_date,
rcta.trx_number invoice_number,
rcta.status_trx invoice_status,
rctla.line_number,
rctla.description,
rctla.extended_amount line_amt,
arp.amount_due_original,
arp.amount_due_remaining
FROM hr_operating_units hou,
ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla,
ra_cust_trx_types_all rctt,
ar_payment_schedules_all arp,
hz_cust_accounts_all hca,
hz_parties hp,
mtl_system_items_b msi,
oe_order_lines_all ool,
oe_order_headers_all ooh
WHERE rcta.customer_trx_id = rctla.customer_trx_id
AND msi.inventory_item_id = rctla.inventory_item_id
AND msi.organization_id = hou.organization_id
AND msi.organization_id = 204
AND rcta.cust_trx_type_id = rctt.cust_trx_type_id
AND arp.customer_trx_id = rcta.customer_trx_id
AND rctla.line_type = 'LINE'
AND rcta.org_id = rctla.org_id
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND TO_CHAR (rctla.interface_line_attribute6) = TO_CHAR (ool.line_id)
RAJU CHINTHAPATLA
XMLP Data Template Report Process OracleApps88
AND ool.header_id = ooh.header_id
AND ooh.order_number = NVL(:P_ORDER_NUMBER,ooh.order_number)
ORDER BY hp.party_name, rcta.trx_number, rctla.line_number
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_HEADER" dataType="VARCHAR2" source="Q_DATA">
<element name="OU_NAME"
dataType="VARCHAR2" value="OU_NAME"/>
<element name="ORDER_NUMBER" dataType="VARCHAR2"
value="ORDER_NUMBER"/>
<element name="ORDER_STATUS" dataType="VARCHAR2"
value="ORDER_STATUS"/>
<element name="ORDERED_DATE" dataType="VARCHAR2"
value="ORDERED_DATE"/>
<element name="SO_LINE_NUMBER" dataType="NUMBER"
value="SO_LINE_NUMBER"/>
<element name="SO_EXTENDED_PRICE" dataType="NUMBER"
value="SO_EXTENDED_PRICE"/>
<element name="ITEM_NUMBER"
dataType="VARCHAR2" value="ITEM_NUMBER"/>
<element name="CUSTOMER_NAME" dataType="VARCHAR2"
value="CUSTOMER_NAME"/>
<element name="ACCOUNT_NUMBER"
dataType="VARCHAR2" value="ACCOUNT_NUMBER"/>
<element name="INVOICE_DATE" dataType="VARCHAR2"
value="INVOICE_DATE"/>
<element name="INVOICE_NUMBER" dataType="NUMBER"
value="INVOICE_NUMBER"/>
<element name="INVOICE_STATUS"
dataType="VARCHAR2" value="INVOICE_STATUS"/>
<element name="LINE_NUMBER" dataType="NUMBER"
value="LINE_NUMBER"/>
<element name="DESCRIPTION"
dataType="VARCHAR2" value="DESCRIPTION"/>
<element name="LINE_AMT" dataType="NUMBER"
value="LINE_AMT"/>
<element name="AMOUNT_DUE_ORIGINAL" dataType="NUMBER"
value="AMOUNT_DUE_ORIGINAL"/>
<element name="AMOUNT_DUE_REMAINING" dataType="NUMBER"
value="AMOUNT_DUE_REMAINING"/>
</group>
</dataStructure>
<!-- <dataTrigger name="afterReport"
source="XXCUST_SALES_ORDER_DETAILS_PKG.afterReport"/>-->
</dataTemplate>
Step2 : Loging to Application and go to Applica Developer responsibility and create the concurrent program
Nav : Application Developer Concurrent Program
RAJU CHINTHAPATLA
XMLP Data Template Report Process OracleApps88
Step5 : Enter the below details and save the form
Field Value
Program XXCUST Sales Order Details Report
Short Name XXCUST_SALES_ORDER_DETAILS
Application Custom Development
Description XXCUST Sales Order Details Report
Executable Name XDODTEXE
Output Format XML
Save and click on the Parameters
RAJU CHINTHAPATLA
XMLP Data Template Report Process OracleApps88
Step 6: Enter the parameter details and save
Note : Concurrent Parameters sequence should follow the Package input parameters sequence
Field Value
Seq 10
Parameter P_ORDER_NUMBER (Any meaningful Name)
Value Set OM:Order Number (Standard Value Set)
Prompt Order Number
RAJU CHINTHAPATLA
XMLP Data Template Report Process OracleApps88
Step 7 : Add the concurrent program to Request group
Nav : System Administrator Security Responsibility Request
RAJU CHINTHAPATLA
XMLP Data Template Report Process OracleApps88
Step8: Query the request group and add the concurrent program
Field Value
Group OM Concurrent Programs
Application Order Management
Program XXCUST Sales Order Details Report
Note: To develop the RTF get the xml from concurrent program
Step9 : Run the program from Purchasing Responsibility
Nav : Purchasing, Vision Operation (USA) View Requests Submit New Requests Single Request
Submit the “XXCUST Sales Order Details Report” program with Order Number parameter as 14481 (Any Order number
value, If you not pass the value It will display for all Order number)
RAJU CHINTHAPATLA
XMLP Data Template Report Process OracleApps88
Step10 : Click on the View Output
Step11 : Save the XML file in desktop with XXCUST_SALES_ORDER_DETAILS.xml (Any Name)
Step12 : Develop the RTF file with Above xml file and save with XXCUST_SALES_ORDER_DETAILS.rtf
Step13 : Test the RTF file in local System
Load the XML File
Click on the Preview File
Step 14 : Create the Data definition and Template and upload the above developed RTF file
Create the Data definition
Nav : XML Publisher Administrator Home Data Definition
RAJU CHINTHAPATLA
XMLP Data Template Report Process OracleApps88
Click on Create Data Definition
Enter the below values and save the page
Field Value
Name XXCUST Sales Order Details Report
Code XXCUST_SALES_ORDER_DETAILS (Data Definition code should be equals to concurrent program
sort name)
Application Custom Development
RAJU CHINTHAPATLA
XMLP Data Template Report Process OracleApps88
Click on the Data template and add the data template xml file.
Create the template with above Data Definition
RAJU CHINTHAPATLA
XMLP Data Template Report Process OracleApps88
Nav : XML Publisher Administrator Home Template
Click on the Create Template
Enter the values and save the page
Field Value
Name XXCUST Sales Order Details Report
Code XXCUST_SALES_ORDER_DETAILS
Application Custom Development
Data Definition XXCUST Sales Order Details Report
Type RTF
Default Output Type PDF
RAJU CHINTHAPATLA
XMLP Data Template Report Process OracleApps88
Now go to Purchasing Responsibility and run the report with parameter
Nav : Purchasing, Vision Operation (USA) View Requests Submit New Requests Single Request
Submit the “XXCUST Sales Order Details Report” program with PO Number parameter as 500 (Any po number value, If
you not pass the value It will disply for all po number)
Note : Once the template is created it will show in concurrent program
RAJU CHINTHAPATLA
XMLP Data Template Report Process OracleApps88
Click on View Output file
RAJU CHINTHAPATLA
