Introduction:
This query will display the PO details(Delta extract)
select prha.attribute1 triprojectnumbertx, –project_number,
poh.segment1 cstPONumberTX, –po_number, cstPOUniqueKeyTX
prla.attribute4 cstCostCodeIDTX, — Added this as per the latest Cost code change request.
–SUBSTR(prla.item_description, 1, 23) cstCostCodeIDTX, — Commented as per the latest Cost code change request.
(pol.unit_price* pol.quantity) CSTAMOUNTNU, –Amount
hr.name cstoperatingunitli,
APS.VENDOR_ID cstVendorIdPoTX,
ASSA.VENDOR_SITE_CODE cstVendorSiteCodeTX,
TO_CHAR(poh.creation_date,’MM/DD/YYYY’) date_po_open,
TO_CHAR(poh.approved_date,’MM/DD/YYYY’) date_po_approved
FROM ap_suppliers aps,
po_headers_all poh,
po_lines_all pol,
po_distributions_all pda,
po_req_distributions_all prda,
po_requisition_lines_all prla,
po_requisition_headers_all prha,
FND_FLEX_VALUES FFV,
FND_FLEX_VALUE_SETS FFVS ,
AP_SUPPLIER_SITES_ALL ASSA,
HR_OPERATING_UNITS HR
WHERE 1 = 1
AND POH.VENDOR_ID = APS.VENDOR_ID (+)
AND POH.VENDOR_SITE_ID = ASSA.VENDOR_SITE_ID (+)
AND hr.organization_id (+) = poh.org_id
AND pol.po_header_id = poh.po_header_id
AND PDA.PO_LINE_ID = POL.PO_LINE_ID
AND pda.req_distribution_id (+) = prda.distribution_id
AND prda.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffvs.flex_value_set_name = ‘XXXX’
AND SUBSTR (prha.attribute1, 1, 2) = ffv.flex_value
AND LENGTH (PRHA.ATTRIBUTE1) > 2
AND prha.attribute_category = ‘Yes’
AND prha.attribute1 IS NOT NULL
AND poh.segment1 IS NOT NULL
–Delta Logic starts
–Condition to extract only updated/new records in PO Headers All table
and(
(poh.LAST_UPDATE_DATE>=TO_DATE(TO_CHAR(SYSDATE-1,’mm/dd/yyyy’)||’ 18-00-00′,’mm/dd/yyyy hh24-mi-ss’)
AND POH.LAST_UPDATE_DATE<TO_DATE(TO_CHAR(SYSDATE,’mm/dd/yyyy’)||’ 18-00-00′,’mm/dd/yyyy hh24-mi-ss’)
)
–Condition to extract only updated/new records in PO Lines All table
OR
(pol.LAST_UPDATE_DATE>=TO_DATE(TO_CHAR(SYSDATE-1,’mm/dd/yyyy’)||’ 18-00-00′,’mm/dd/yyyy hh24-mi-ss’)
AND pol.LAST_UPDATE_DATE<TO_DATE(TO_CHAR(SYSDATE,’mm/dd/yyyy’)||’ 18-00-00′,’mm/dd/yyyy hh24-mi-ss’)
)
–Condition to extract only updated/new records in po_distributions_all table
or
(pda.LAST_UPDATE_DATE>=TO_DATE(TO_CHAR(SYSDATE-1,’mm/dd/yyyy’)||’ 18-00-00′,’mm/dd/yyyy hh24-mi-ss’)
AND pda.LAST_UPDATE_DATE<TO_DATE(TO_CHAR(SYSDATE,’mm/dd/yyyy’)||’ 18-00-00′,’mm/dd/yyyy hh24-mi-ss’)
)
)
)
GROUP BY triprojectnumbertx,
cstPONumberTX,
cstCostCodeIDTX,
cstoperatingunitli,
cstvendoridpotx,
cstvendorsitecodetx,
date_po_open,
date_po_approved
ORDER BY 7;
Know more about post.
Doyen.ebiz@gmail.com
