Here is the SQL script to get all the open Purchase Orders info, including PO Lines & Shipment info.
The script has been used extensively to provide Open POs info to the business & Corporate, especially for data cleansing and data migration purposes when we migrated our ERP system from Oracle EBS to SAP.
SELECT hou.name OU,
gcc.segment1 OPERATION,
gcc.segment2 COST_CENTER,
gcc.segment3 Natural_Account,
gcc.segment1||’.’||gcc.segment2||’.’||gcc.segment3||’.’||gcc.segment4||’.’||gcc.segment5||’.’||gcc.segment6 po_charge_account,
ph.segment1 PO_NUMBBER,
ph.revision_num,
ph.type_lookup_code document_type,
NVL(ph.closed_code,’OPEN’) Closed_Code,
NVL(ph.authorization_status,’INCOMPLETE’) PO_STATUS,
pv.vendor_name vendor,
pv.segment1 vendor_number,
pvs.vendor_site_code vendor_site,
pv.vendor_type_lookup_code supplier_type,
ph.creation_date PO_CREATION_DATE,
ph.approved_date PO_APPROVED_DATE,
(SELECT NAME FROM apps.AP_TERMS WHERE TERM_ID = PH.TERMS_ID) Payment_term,
(SELECT full_name FROM apps.per_people_f WHERE person_id = ph.agent_id AND (TRUNC (SYSDATE) BETWEEN effective_start_date(+) AND effective_end_date(+))) buyer_name,
ph.currency_code,
(SELECT ROUND(SUM(decode(quantity,null,(amount – nvl(amount_cancelled,0)),(quantity – nvl(quantity_cancelled,0))* nvl(price_override,0))),2)
FROM apps.po_line_locations_all WHERE po_header_id = ph.po_header_id AND NVL(cancel_flag,’N’) = ‘N’) PO_Amount,
/* (SELECT ROUND(SUM(NVL(quantity_received,0) * price_override),2)
FROM apps.po_line_locations_all WHERE po_header_id = ph.po_header_id AND NVL(cancel_flag,’N’) = ‘N’) amount_received,
(SELECT SUM(amount_billed)
FROM apps.po_line_locations_all WHERE po_header_id = ph.po_header_id AND NVL(cancel_flag,’N’) = ‘N’) amount_billed,
*/
— ROUND(SUM(NVL(pll.quantity_received * pll.price_override * NVL(ph.rate,1),0)),2) corp_currency_amount_received
— (SELECT NVL(SUM(INVOICE_AMOUNT-NVL(cancelled_amount,0)),0) FROM apps.ap_invoices_all api WHERE api.INVOICE_TYPE_LOOKUP_CODE = ‘PREPAYMENT’ AND cancelled_date is null
— AND api.QUICK_PO_HEADER_ID = ph.po_header_id) PrePayment_Amount
— Line Info
pl.line_num,
pll.shipment_num,
NVL(pll.closed_code,’OPEN’) SHIPMENT_CLOSED_CODE,
plt.order_type_lookup_code line_type,
msi.segment1 item,
pl.item_description,
DECODE(pl.item_id,NULL,mc.segment1,mc1.segment1) purchasing_category_code,
DECODE(pl.item_id,NULL,mc.description,mc1.description) PO_CATEG_DESCR,
— pl.unit_meas_lookup_code,
pll.PRICE_OVERRIDE Unit_price,
pll.UNIT_MEAS_LOOKUP_CODE UOM,
(pll.quantity – NVL(pll.quantity_cancelled,0)) shipment_Quantity,
(pll.QUANTITY – NVL(pll.QUANTITY_CANCELLED,0)) * pll.PRICE_OVERRIDE “Shipment_Amount”,
NVL(pll.quantity_received,0) QTY_RECEIVED,
(pll.quantity – NVL(pll.quantity_cancelled,0)- NVL(pll.quantity_received,0)) Open_shipment_Quantity,
mp.organization_code SHIP_TO_ORG,
pll.need_by_date,
pll.promised_date,
(SELECT promised_date FROM apps.po_line_locations_archive_all WHERE line_location_id = pll.line_location_id
AND revision_num = (SELECT MIN(revision_num) FROM apps.po_line_locations_archive_all WHERE line_location_id = pll.line_location_id and promised_date IS NOT NULL)) ORIGINAL_PROMISE_DATE,
DECODE(PLL.RECEIPT_REQUIRED_FLAG,’N’,DECODE(PLL.INSPECTION_REQUIRED_FLAG,’N’,’2-Way’,’2-Way’),’Y’,DECODE(PLL.INSPECTION_REQUIRED_FLAG,’N’,’3-Way’,’Y’,’4-Way’,’3-Way’)) PO_Match_Type,
NVL(pll.CONSIGNED_FLAG,’N’) CONSIGNED_FLAG,
pl.line_num||’.’||pll.shipment_num||’.’||pd.distribution_num Dist_line_number,
pd.QUANTITY_ORDERED – NVL(pd.QUANTITY_CANCELLED,0) Distr_Ordered_Quantity,
pd.QUANTITY_DELIVERED Dist_Delivered_Qty,
pd.QUANTITY_BILLED Dist_Billed_Qty,
DECODE (pd.destination_type_code,’EXPENSE’, ‘Expense’,’INVENTORY’, ‘Inventory’,’SHOP FLOOR’, ‘Shop Floor’,NULL) DESTINATION_TYPE,
(SELECT SEGMENT1 FROM apps.PA_PROJECTS_ALL WHERE PROJECT_ID = PD.PROJECT_ID ) project_number,
(SELECT TASK_NUMBER FROM apps.PA_TASKS WHERE TASK_ID = PD.TASK_ID ) TASK_number,
(SELECT DECODE( UPPER( SUBSTR( project_type, 1, 8 ) ),NULL,’INDIRECT’,’INDIRECT’, ‘INDIRECT’,’DIRECT’) FROM apps.PA_PROJECTS_ALL WHERE PROJECT_ID = PD.PROJECT_ID ) project_type,
pd.expenditure_type Exp_Type,
(select name from apps.hr_all_organization_units where organization_id = pd.EXPENDITURE_ORGANIZATION_ID) Exp_Org,
pd.expenditure_item_date Exp_Date
FROM apps.po_headers_all ph,
apps.po_lines_all pl,
apps.po_line_locations_all pll,
apps.po_distributions_all pd,
apps.po_vendors pv,
apps.po_vendor_sites_all pvs,
apps.hr_operating_units hou,
apps.gl_code_combinations gcc,
apps.mtl_categories_vl mc,
apps.mtl_item_categories mic,
apps.mtl_categories_vl mc1,
apps.mtl_parameters mp,
apps.mtl_system_items msi,
apps.po_line_types_b plt
WHERE 1=1
AND ph.type_lookup_code = ‘STANDARD’
AND NVL(ph.authorization_status,’INCOMPLETE’) <> ‘INCOMPLETE’ — IN PROCESS,NULL,REQUIRES REAPPROVAL,INCOMPLETE,REJECTED,APPROVED
AND NVL(ph.closed_code,’OPEN’) = ‘OPEN’ — CLOSED,NULL,OPEN,FINALLY CLOSED -NOT IN (‘CLOSED’, ‘FINALLY CLOSED’)
AND NVL(ph.cancel_flag,’N’) = ‘N’
AND ph.po_header_id = pl.po_header_id
AND NVL(pl.closed_code,’OPEN’) NOT IN (‘CLOSED’, ‘FINALLY CLOSED’) — CLOSED,NULL,OPEN,FINALLY CLOSED
AND NVL(pl.cancel_flag,’N’) = ‘N’
AND pl.po_line_id = pll.po_line_id
AND NVL(pll.closed_code,’OPEN’) NOT IN (‘CLOSED’, ‘FINALLY CLOSED’) — CLOSED,OPEN,FINALLY CLOSED,CLOSED FOR INVOICE,CLOSED FOR RECEIVING
AND NVL(pll.cancel_flag,’N’) = ‘N’
AND pll.line_location_id = pd.line_location_id
— AND pll.quantity – NVL(pll.quantity_cancelled,0) – NVL(pll.quantity_received,0) > 0 — Open for receiving
AND ph.vendor_id = pv.vendor_id
AND ph.vendor_site_id = pvs.vendor_site_Id
AND ph.org_id = hou.organization_id
AND pd.code_combination_id = gcc.code_combination_id
AND pll.ship_to_organization_id = mp.organization_id
— AND pl.category_id = mc.category_id
AND pl.category_id = mc.category_id (+)
AND pl.item_id = mic.inventory_item_id (+)
AND pll.ship_to_organization_id = mic.organization_id (+)
AND mic.category_set_id (+) = 1100000023
AND mic.category_id = mc1.category_id (+)
AND pl.item_id = msi.inventory_item_id (+)
AND NVL (msi.organization_id, pll.ship_to_organization_id) = pll.ship_to_organization_id
and pl.line_type_id = plt.line_type_id
— AND ph.org_id = 1700 — 800 OU
— AND pd.creation_date >= ’01-JAN-2022′
UNION ALL
SELECT hou.name OU,
gcc.segment1 OPERATION,
gcc.segment2 COST_CENTER,
gcc.segment3 Natural_Account,
gcc.segment1||’.’||gcc.segment2||’.’||gcc.segment3||’.’||gcc.segment4||’.’||gcc.segment5||’.’||gcc.segment6 po_charge_account,
ph.segment1||’-‘||pr.release_num PO_NUMBBER,
pr.revision_num,
‘Blanket Releases’ document_type,
NVL(pr.closed_code,’OPEN’) Closed_Code,
NVL(pr.authorization_status,’INCOMPLETE’) PO_STATUS,
pv.vendor_name vendor,
pv.segment1 vendor_number,
pvs.vendor_site_code vendor_site,
pv.vendor_type_lookup_code supplier_type,
pr.creation_date PO_CREATION_DATE,
pr.approved_date PO_APPROVED_DATE,
(SELECT NAME FROM apps.AP_TERMS WHERE TERM_ID = PH.TERMS_ID) Payment_term,
(SELECT full_name FROM apps.per_people_f WHERE person_id = pr.agent_id AND (TRUNC (SYSDATE) BETWEEN effective_start_date(+) AND effective_end_date(+))) buyer_name,
PH.CURRENCY_CODE,
(SELECT ROUND(SUM(decode(quantity,null,(amount – nvl(amount_cancelled,0)),(quantity – nvl(quantity_cancelled,0))* nvl(price_override,0))),2)
FROM apps.po_line_locations_all WHERE po_header_id = ph.po_header_id AND NVL(cancel_flag,’N’) = ‘N’) PO_Amount,
/* (SELECT ROUND(SUM(NVL(quantity_received,0) * price_override),2)
FROM apps.po_line_locations_all WHERE po_header_id = ph.po_header_id AND NVL(cancel_flag,’N’) = ‘N’) amount_received,
(SELECT SUM(amount_billed)
FROM apps.po_line_locations_all WHERE po_header_id = ph.po_header_id AND NVL(cancel_flag,’N’) = ‘N’) amount_billed,
*/
–ROUND(SUM(NVL(pll.quantity_received * pll.price_override * NVL(ph.rate,1),0)),2) corp_currency_amount_received
— (SELECT NVL(SUM(INVOICE_AMOUNT-NVL(cancelled_amount,0)),0) FROM apps.ap_invoices_all api WHERE api.INVOICE_TYPE_LOOKUP_CODE = ‘PREPAYMENT’ AND cancelled_date is null
— AND api.QUICK_PO_HEADER_ID = ph.po_header_id) PrePayment_Amount
— Line Indo
pl.line_num,
pll.shipment_num,
NVL(pll.closed_code,’OPEN’) SHIPMENT_CLOSED_CODE,
plt.order_type_lookup_code line_type,
msi.segment1 item,
pl.item_description,
DECODE(pl.item_id,NULL,mc.segment1,mc1.segment1) purchasing_category_code,
DECODE(pl.item_id,NULL,mc.description,mc1.description) PO_CATEG_DESCR,
— pl.unit_meas_lookup_code,
pll.PRICE_OVERRIDE Unit_price,
pll.UNIT_MEAS_LOOKUP_CODE UOM,
(pll.quantity – NVL(pll.quantity_cancelled,0)) shipment_Quantity,
(pll.QUANTITY – NVL(pll.QUANTITY_CANCELLED,0)) * pll.PRICE_OVERRIDE “Shipment_Amount”,
NVL(pll.quantity_received,0) QTY_RECEIVED,
(pll.quantity – NVL(pll.quantity_cancelled,0)- NVL(pll.quantity_received,0)) Open_shipment_Quantity,
mp.organization_code SHIP_TO_ORG,
pll.need_by_date,
pll.promised_date,
(SELECT promised_date FROM apps.po_line_locations_archive_all WHERE line_location_id = pll.line_location_id
AND revision_num = (SELECT MIN(revision_num) FROM apps.po_line_locations_archive_all WHERE line_location_id = pll.line_location_id and promised_date IS NOT NULL)) ORIGINAL_PROMISE_DATE,
DECODE(PLL.RECEIPT_REQUIRED_FLAG,’N’,DECODE(PLL.INSPECTION_REQUIRED_FLAG,’N’,’2-Way’,’2-Way’),’Y’,DECODE(PLL.INSPECTION_REQUIRED_FLAG,’N’,’3-Way’,’Y’,’4-Way’,’3-Way’)) PO_Match_Type,
NVL(pll.CONSIGNED_FLAG,’N’) CONSIGNED_FLAG,
pl.line_num||’.’||pll.shipment_num||’.’||pd.distribution_num Dist_line_number,
pd.QUANTITY_ORDERED – NVL(pd.QUANTITY_CANCELLED,0) Distr_Ordered_Quantity,
pd.QUANTITY_DELIVERED Dist_Delivered_Qty,
pd. QUANTITY_BILLED Dist_Billed_Qty,
DECODE (pd.destination_type_code,’EXPENSE’, ‘Expense’,’INVENTORY’, ‘Inventory’,’SHOP FLOOR’, ‘Shop Floor’,NULL) DESTINATION_TYPE,
(SELECT SEGMENT1 FROM apps.PA_PROJECTS_ALL WHERE PROJECT_ID = PD.PROJECT_ID ) project_number,
(SELECT TASK_NUMBER FROM apps.PA_TASKS WHERE TASK_ID = PD.TASK_ID ) TASK_number,
(SELECT DECODE( UPPER( SUBSTR(project_type, 1, 8 ) ),NULL,’INDIRECT’,’INDIRECT’, ‘INDIRECT’,’DIRECT’) FROM apps.PA_PROJECTS_ALL WHERE PROJECT_ID = PD.PROJECT_ID ) project_type,
pd.expenditure_type Exp_Type,
(select name from apps.hr_all_organization_units where organization_id = pd.EXPENDITURE_ORGANIZATION_ID) Exp_Org,
pd.EXPENDITURE_ITEM_DATE Exp_Date
FROM apps.po_releases_all pr,
apps.po_headers_all ph,
apps.po_lines_all pl,
apps.po_line_locations_all pll,
apps.po_vendors pv,
apps.po_vendor_sites_all pvs,
apps.hr_operating_units hou,
apps.po_distributions_all pd,
apps.gl_code_combinations gcc,
apps.mtl_categories_vl mc,
apps.mtl_item_categories mic,
apps.mtl_categories_vl mc1,
apps.mtl_parameters mp,
apps.mtl_system_items msi,
apps.po_line_types_b plt
WHERE pr.po_header_id = ph.po_header_id
AND NVL(pr.authorization_status,’INCOMPLETE’) <> ‘INCOMPLETE’
AND NVL(pr.closed_code,’OPEN’) = ‘OPEN’ — CLOSED,NULL,OPEN,FINALLY CLOSED
AND NVL(pr.cancel_flag,’N’) = ‘N’
AND ph.po_header_id = pl.po_header_id
AND NVL(pl.closed_code,’OPEN’) NOT IN (‘CLOSED’, ‘FINALLY CLOSED’) — CLOSED,NULL,OPEN,FINALLY CLOSED
AND NVL(pl.cancel_flag,’N’) = ‘N’
AND pl.po_line_id = pll.po_line_id
AND pr.po_release_id = pll.po_release_id
AND NVL(pll.closed_code,’OPEN’) NOT IN (‘CLOSED’, ‘FINALLY CLOSED’) — CLOSED,OPEN,FINALLY CLOSED,CLOSED FOR INVOICE,CLOSED FOR RECEIVING
AND NVL(pll.cancel_flag,’N’) = ‘N’
AND pll.line_location_id = pd.line_location_id
— AND pll.quantity – NVL(pll.quantity_cancelled,0) – NVL(pll.quantity_received,0) > 0 — oPEN FOR RECEIVING
AND ph.vendor_id = pv.vendor_id
AND ph.vendor_site_id = pvs.vendor_site_Id
AND pr.org_id = hou.organization_id
AND pd.code_combination_id = gcc.code_combination_id
— AND pl.category_id = mc.category_id
AND pl.category_id = mc.category_id (+)
AND pl.item_id = mic.inventory_item_id (+)
AND pll.ship_to_organization_id = mic.organization_id (+)
AND mic.category_set_id (+) = 1100000023
AND mic.category_id = mc1.category_id (+)
AND pll.ship_to_organization_id = mp.organization_id
AND pl.item_id = msi.inventory_item_id (+)
AND NVL (msi.organization_id, pll.ship_to_organization_id) = pll.ship_to_organization_id
and pl.line_type_id = plt.line_type_id
— AND ph.org_id = 1700 — 800 OU
— AND pd.creation_date >= ’01-JAN-2022′
ORDER BY 1,2,6,7,21,22,41