Approved Requisition Report

<dataTemplate name="POREQAPPR" version="1.0">
<parameters>
<parameter name="P_APPROVED_FROM_DT" dataType="DATE"/>
<parameter name="P_APPROVED_TO_DT" dataType="DATE"/>
</parameters>
<dataQuery>
<sqlStatement name="Q_HEAD_PARAMTERS">
<![CDATA[
Select to_char(:P_APPROVED_FROM_DT,'DD-Mon-RRRR') FROM_DATE,to_char(:P_APPROVED_TO_DT,'DD-Mon-RRRR') TO_DATE from dual
]]>
</sqlStatement>
<sqlStatement name="Q_1">
<![CDATA[
/* Formatted on 2019/02/19 11:17 (Formatter Plus v4.8.8) */
SELECT   r.segment1 req_number,
         (SELECT vendor_name
            FROM apps.ap_suppliers
           WHERE vendor_id = rl.vendor_id) supplier_name,
         (SELECT vendor_site_code
            FROM apps.ap_supplier_sites_all
           WHERE vendor_site_id = rl.vendor_site_id) supplier_site_code,
         rl.line_num req_line, rl.suggested_vendor_product_code supplier_item,
         (SELECT DISTINCT description
                     FROM apps.mtl_categories
                    WHERE category_id = rl.category_id) req_line_cat_desc,
         (SELECT DISTINCT segment1 || '.' || segment2
                     FROM apps.mtl_categories
                    WHERE category_id = rl.category_id) req_line_category,
         TO_CHAR (r.approved_date, 'dd-MON-RR HH24:MI:SS') req_approved_date,
         p.segment1 po_number, p.revision_num po_revision_num,
         pl.line_num po_line,
         TO_CHAR (p.creation_date, 'DD-MON-RRRR HH24:MI:SS') po_created_date,
         pagen.agent_name po_buyer, p.attribute10 contract_type,
         p.org_id org_id, (SELECT NVL (short_code, NAME)
                             FROM apps.hr_operating_units
                            WHERE organization_id = p.org_id) org_code
    FROM apps.po_headers_all p,
         apps.po_lines_all pl,
         apps.po_distributions_all d,
         apps.po_agents_v pagen,
         apps.po_req_distributions_all rd,
         apps.po_requisition_lines_all rl,
         apps.po_requisition_headers_all r
   --apps.ap_suppliers aps
WHERE    1 = 1
     AND p.po_header_id = d.po_header_id
     AND p.po_header_id = pl.po_header_id
     AND pl.po_line_id = d.po_line_id
     AND pagen.agent_id = p.agent_id
--and aps.vendor_id            = p.vendor_id
     AND d.req_distribution_id = rd.distribution_id
     AND rd.requisition_line_id = rl.requisition_line_id
     AND rl.requisition_header_id = r.requisition_header_id
     AND p.org_id IN
            (82, 83, 367, 370, 627, 628, 629, 388, 630, 395, 376, 396, 382,
             393, 378, 828)
     --AND trunc(TO_DATE(r.approved_date,'RRRR/MM/DD HH24:MI:SS')) >= trunc(TO_DATE(:p_approved_from_dt,'RRRR/MM/DD HH24:MI:SS'))
     --AND trunc(TO_DATE(r.approved_date,'RRRR/MM/DD HH24:MI:SS')) <= trunc(TO_DATE(:p_approved_to_dt,'RRRR/MM/DD HH24:MI:SS'))
AND trunc(r.approved_date) >= trunc(:p_approved_from_dt)
     AND trunc(r.approved_date) <= trunc(:p_approved_to_dt)
UNION
SELECT   r.segment1 req_number,
         (SELECT vendor_name
            FROM apps.ap_suppliers
           WHERE vendor_id = rl.vendor_id) supplier_name,
         (SELECT vendor_site_code
            FROM apps.ap_supplier_sites_all
           WHERE vendor_site_id = rl.vendor_site_id) supplier_site_code,
         rl.line_num req_line, rl.suggested_vendor_product_code supplier_item,
         (SELECT DISTINCT description
                     FROM apps.mtl_categories
                    WHERE category_id = rl.category_id) req_line_cat_desc,
         (SELECT DISTINCT segment1 || '.' || segment2
                     FROM apps.mtl_categories
                    WHERE category_id = rl.category_id) req_line_category,
         TO_CHAR (r.approved_date, 'dd-MON-RR HH24:MI:SS') req_approved_date,
         NULL po_number, NULL po_revision_num, NULL po_line,
         NULL po_created_date, NULL po_buyer, NULL contract_type,
         r.org_id org_id, (SELECT NVL (short_code, NAME)
                             FROM apps.hr_operating_units
                            WHERE organization_id = r.org_id) org_code
    --aps.vendor_name
FROM     apps.po_requisition_headers_all r,
         apps.po_requisition_lines_all rl,
         apps.po_req_distributions_all rd
   WHERE 1 = 1
     AND rl.requisition_header_id = r.requisition_header_id
     AND rd.requisition_line_id = rl.requisition_line_id
     AND NOT EXISTS (SELECT req_distribution_id
                       FROM apps.po_distributions_all
                      WHERE req_distribution_id = rd.distribution_id)
     AND r.org_id IN
            (82, 83, 367, 370, 627, 628, 629, 388, 630, 395, 376, 396, 382,
             393, 378, 828)
     --AND trunc(TO_DATE(r.approved_date,'RRRR/MM/DD HH24:MI:SS')) >= trunc(TO_DATE(:p_approved_from_dt,'RRRR/MM/DD HH24:MI:SS'))
     --AND trunc(TO_DATE(r.approved_date,'RRRR/MM/DD HH24:MI:SS')) <= trunc(TO_DATE(:p_approved_to_dt,'RRRR/MM/DD HH24:MI:SS'))
AND trunc(r.approved_date) >= trunc(:p_approved_from_dt)
     AND trunc(r.approved_date) <= trunc(:p_approved_to_dt)
ORDER BY 15, 1,4,9,11
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_HEAD_PARAMTERS" dataType="varchar2" source="Q_HEAD_PARAMTERS" >
<element name="FROM_DATE" value="FROM_DATE"/>
<element name="TO_DATE" value="TO_DATE"/>
<group name="G_MAIN" dataType="varchar2" source="Q_1">
<element name="REQ_NUMBER" value="REQ_NUMBER"/>
<element name="SUPPLIER_NAME" value="SUPPLIER_NAME"/>
<element name="SUPPLIER_SITE_CODE" value="SUPPLIER_SITE_CODE"/>
<element name="REQ_LINE" value="REQ_LINE"/>
<element name="SUPPLIER_ITEM" value="SUPPLIER_ITEM"/>
<element name="REQ_LINE_CAT_DESC" value="REQ_LINE_CAT_DESC"/>
<element name="REQ_LINE_CATEGORY" value="REQ_LINE_CATEGORY"/>
<element name="REQ_APPROVED_DATE" value="REQ_APPROVED_DATE"/>
<element name="PO_NUMBER" value="PO_NUMBER"/>
<element name="PO_REVISION_NUM" value="PO_REVISION_NUM"/>
<element name="PO_LINE" value="PO_LINE"/>
<element name="PO_CREATED_DATE" value="PO_CREATED_DATE"/>
<element name="PO_BUYER" value="PO_BUYER"/>
<element name="CONTRACT_TYPE" value="CONTRACT_TYPE"/>
<element name="ORG_ID" value="ORG_ID"/>
<element name="ORG_CODE" value="ORG_CODE"/>
</group>
</group>
</dataStructure>
</dataTemplate>