Introduction:
User wants to pull the Open Purchase order details data.
Solution:
We provide the view to fulfill their requirement.
View:
CREATE OR REPLACE VIEW “APPS”.”XDMC_OPEN_PO_DETAIL_V” (“BUYER”, “VENDOR_NO”, “VENDOR”, “SITE_NO”, “SITE”, “PO_NUMBER”, “TERMS”, “LINE_NUMBER”, “ITEM”, “DESCRIPTION”, “ITEM_REVISION”, “UNIT_PRICE”, “QTY_OPEN”, “QTY_RCV”, “QTY_CANCEL”, “QTY_BAL”, “APPROVED_DATE”, “PROMISED_DATE”, “NEED_BY_DATE”, “ORGANIZATION_ID”, “NOTE1”, “NOTE2”, “NEW_ITEM_PO”, “ORIGINAL_REQUEST”, “CONFIRMATION”, “SHIP_INFORMATION”, “SHIP_COMPLETE”, “AIR_SHIPMENT”, “NEW_PRODUCT_LAUNCH”, “PO_OTHER”, “CUST_DOCK”) AS
SELECT pb.agent_name buyer, pv.segment1 venor_no, pv.vendor_name vendor,
pvs.vendor_site_code site_no,
pvs.address_line1 || ‘,’ || pvs.address_line2 site,
pha.segment1 po_number, atl.NAME terms, pla.line_num line_number,
msi.segment1 item, pla.item_description description,
pla.item_revision item_revision, pla.unit_price unit_price,
(SUM (NVL (plla.quantity, 0))) qty_open,
SUM (plla.quantity_received) qty_rcv,
SUM (plla.quantity_cancelled) qty_cancel,
( SUM (NVL (plla.quantity, 0))
– SUM (NVL (plla.quantity_cancelled, 0))
– SUM (NVL (plla.quantity_received, 0))
) qty_bal,
pha.approved_date approved_date, plla.promised_date promise_date,
plla.need_by_date need_by_date,
plla.ship_to_organization_id organization_id, NULL note1, NULL note2,
NULL new_item_po, NULL original_request, NULL confirmation,
NULL ship_information, NULL ship_complete, NULL air_shipment,
NULL new_product_launch, NULL po_other, NULL cust_dock
FROM apps.po_headers_all pha,
apps.po_lines_all pla,
apps.po_line_locations_all plla,
apps.ap_suppliers pv,
apps.mtl_system_items msi,
apps.org_organization_definitions ood,
apps.ap_supplier_sites_all pvs,
apps.hr_locations hrl,
apps.po_agents_v pb,
apps.hr_operating_units hou,
apps.ap_terms_tl atl
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND msi.inventory_item_id = pla.item_id
AND pha.terms_id = atl.term_id
AND pha.type_lookup_code = ‘STANDARD’
AND pha.org_id = hou.organization_id
AND pha.vendor_id = pv.vendor_id
AND pha.vendor_site_id = pvs.vendor_site_id
AND msi.organization_id = plla.ship_to_organization_id
AND hrl.location_id = pha.ship_to_location_id
–AND pha.segment1 = ‘313284’ –PO Number
–AND msi.segment1 = ‘37082’ — Item Number
AND ood.organization_id = msi.organization_id
AND pb.agent_id = pha.agent_id
AND pha.closed_code = ‘OPEN’
GROUP BY pb.agent_name,
pv.segment1,
pv.vendor_name,
pvs.vendor_site_code,
pvs.address_line1 || ‘,’ || pvs.address_line2,
pha.segment1,
atl.NAME,
pla.line_num,
msi.segment1,
pla.item_description,
pla.item_revision,
pla.unit_price,
pha.approved_date,
plla.promised_date,
plla.need_by_date,
plla.ship_to_organization_id
UNION
SELECT pb.agent_name buyer, pv.segment1 venor_no, pv.vendor_name vendor,
pvs.vendor_site_code site_no,
pvs.address_line1 || ‘,’ || pvs.address_line2 site,
pha.segment1 po_number, atl.NAME terms, pla.line_num line_number,
msi.segment1 item, pla.item_description description,
pla.item_revision item_revision, pla.unit_price unit_price,
SUM (NVL (plla.quantity, 0)) qty_open,
SUM (plla.quantity_received) qty_rcv,
SUM (NVL (plla.quantity_cancelled, 0)) qty_cancel,
( SUM (NVL (plla.quantity, 0))
– SUM (NVL (plla.quantity_received, 0))
– SUM (NVL (plla.quantity_cancelled, 0))
) qty_bal,
pla.creation_date approved_date, plla.promised_date promise_date,
plla.need_by_date need_by_date,
plla.ship_to_organization_id organization_id, NULL note1, NULL note2,
NULL new_item_po, NULL original_request, NULL confirmation,
NULL ship_information, NULL ship_complete, NULL air_shipment,
NULL new_product_launch, NULL po_other, NULL cust_dock
FROM apps.po_headers_all pha,
apps.po_lines_all pla,
apps.po_line_locations_all plla,
apps.ap_suppliers pv,
apps.po_releases_all pr,
apps.mtl_system_items msi,
apps.org_organization_definitions ood,
apps.ap_supplier_sites_all pvs,
apps.hr_locations hrl,
apps.po_agents_v pb,
apps.hr_operating_units hou,
apps.ap_terms_tl atl
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND msi.inventory_item_id = pla.item_id
AND pha.po_header_id = pr.po_header_id(+)
AND pha.terms_id = atl.term_id
AND NVL (plla.po_release_id, 1) = NVL (pr.po_release_id, 1)
AND pha.type_lookup_code = ‘BLANKET’
AND pha.org_id = hou.organization_id
AND pha.vendor_id = pv.vendor_id
AND pha.vendor_site_id = pvs.vendor_site_id
AND msi.organization_id = plla.ship_to_organization_id
AND hrl.location_id = pha.ship_to_location_id
AND ood.organization_id = msi.organization_id
AND pb.agent_id = pha.agent_id
AND pha.closed_code = ‘OPEN’
AND plla.closed_reason IS NULL
AND NVL (plla.quantity, 0) – NVL (plla.quantity_received, 0) > 0
–AND pha.segment1 = ‘288244’ –PO Number
— AND msi.segment1 = ‘250304’ — Item Number
GROUP BY pb.agent_name,
pv.segment1,
pv.vendor_name,
pvs.vendor_site_code,
pvs.address_line1 || ‘,’ || pvs.address_line2,
pha.segment1,
atl.NAME,
pla.line_num,
msi.segment1,
pla.item_description,
pla.item_revision,
pla.unit_price,
–pla.quantity,
pla.creation_date,
plla.promised_date,
plla.need_by_date,
plla.ship_to_organization_id
ORDER BY
6,
8;
