<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>
<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>
No comments:
Post a Comment