Oracle EBS XML Publisher Data Template Generator (R12)

<?xml version="1.0" encoding="UTF-8"?>
<dataTemplate name="XXPO_OPEN_PO" description="Open approved purchase orders" defaultPackage="XXPO_OPEN_PO_PKG" version="1.0">
  <parameters>
    <parameter name="P_ORG_ID" dataType="number"/>
    <parameter name="P_FROM_DATE" dataType="character"/>
  </parameters>
  <dataQuery>
    <sqlStatement name="Q_MAIN">
      <![CDATA[
SELECT poh.segment1        AS po_number,
       sup.vendor_name     AS supplier,
       poh.creation_date   AS po_date,
       pol.line_num        AS line_num,
       pol.item_description AS item_description,
       pol.quantity        AS quantity,
       pol.unit_price      AS unit_price
FROM   po_headers_all poh
JOIN   po_lines_all pol ON pol.po_header_id = poh.po_header_id
JOIN   ap_suppliers sup ON sup.vendor_id = poh.vendor_id
WHERE  poh.org_id = :P_ORG_ID
AND    poh.authorization_status = 'APPROVED'
AND    NVL(poh.closed_code, 'OPEN') = 'OPEN'
AND    poh.creation_date >= FND_DATE.CANONICAL_TO_DATE(:P_FROM_DATE)
ORDER  BY poh.segment1, pol.line_num
      ]]>
    </sqlStatement>
  </dataQuery>
  <dataTrigger name="beforeReport" source="XXPO_OPEN_PO_PKG.before_report"/>
  <dataStructure>
    <group name="G_PO_LINE" source="Q_MAIN">
      <element name="PO_NUMBER" value="PO_NUMBER"/>
      <element name="SUPPLIER" value="SUPPLIER"/>
      <element name="PO_DATE" value="PO_DATE"/>
      <element name="LINE_NUM" value="LINE_NUM"/>
      <element name="ITEM_DESCRIPTION" value="ITEM_DESCRIPTION"/>
      <element name="QUANTITY" value="QUANTITY"/>
      <element name="UNIT_PRICE" value="UNIT_PRICE"/>
    </group>
  </dataStructure>
</dataTemplate>

CREATE OR REPLACE PACKAGE XXPO_OPEN_PO_PKG AS
  -- Global variables named exactly like the data template parameters
  P_ORG_ID NUMBER;
  P_FROM_DATE VARCHAR2(30);

  FUNCTION before_report RETURN BOOLEAN;
END XXPO_OPEN_PO_PKG;
/

CREATE OR REPLACE PACKAGE BODY XXPO_OPEN_PO_PKG AS
  FUNCTION before_report RETURN BOOLEAN IS
  BEGIN
    -- Example: set the operating unit context before the query runs
    mo_global.set_policy_context('S', P_ORG_ID);
    RETURN TRUE;
  END before_report;
END XXPO_OPEN_PO_PKG;
/

1. Concurrent program: Application Developer > Concurrent > Program. Short name XXPO_OPEN_PO, executable XDODTEXE (Data Template Executable, application XML Publisher, execution method Java Concurrent Program), output format XML.
2. Parameters: define P_ORG_ID and P_FROM_DATE with Token = P_ORG_ID and P_FROM_DATE. The token must equal the data template parameter name. For a date entered through FND_STANDARD_DATE, keep the dataType character and convert in SQL with FND_DATE.CANONICAL_TO_DATE.
3. Add the program to the request group of the responsibility that will run it.
4. XML Publisher Administrator > Data Definitions > Create: Code = XXPO_OPEN_PO (must equal the program short name), same application as the program. Upload XXPO_OPEN_PO.xml as the Data Template.
5. XML Publisher Administrator > Templates > Create: link to data definition XXPO_OPEN_PO, type RTF, upload the layout built in the Word Template Builder.
6. Run the program; the output post-processor merges the XML with the RTF.

SELECT d.application_short_name, d.data_source_code, d.start_date
FROM   xdo_ds_definitions_b d
WHERE  d.data_source_code = 'XXPO_OPEN_PO';

SELECT t.template_code, t.template_type_code, t.default_language, t.start_date
FROM   xdo_templates_b t
WHERE  t.data_source_code = 'XXPO_OPEN_PO';

SELECT l.lob_code, l.lob_type, l.file_name, l.language, l.last_update_date
FROM   xdo_lobs l
WHERE  l.lob_code = 'XXPO_OPEN_PO';