PO Details Query

execute fnd_client_info.set_org_context('1');


SELECT
decode(por.release_num,NULL, poh.segment1, poh.segment1 ||'-'|| por.release_num) PO_Number
, pol.line_num Line,
msi.segment1 Item_number,
msi.organization_id
, pov.vendor_name Vendor
, pol.item_revision Rev
, pol.item_description Description
, pll.shipment_num
, pod.distribution_num Distribution
, decode(plt.order_type_lookup_code, 'AMOUNT',NULL,pll.price_override) Unit_Price
, pll.promised_date Promised_Date
, pol.unit_meas_lookup_code Unit
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered,
'FIXED PRICE', POD.amount_ordered,
POD.quantity_ordered) Quantity_Amount_Ordered
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_billed,
'FIXED PRICE', POD.amount_billed,
POD.quantity_billed) Quantity_Amount_Billed
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_delivered,
'FIXED PRICE', POD.amount_delivered,
POD.quantity_delivered) Qty_Amount_Delivered
, DECODE (POL.order_type_lookup_code,
'RATE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
'FIXED PRICE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
(NVL(POD.quantity_ordered, 0) - NVL(POD.quantity_billed, 0)) /
DECODE (NVL(POD.quantity_ordered, 0), 0, 1, POD.quantity_ordered)) * 100 Percent_Unbilled
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
'FIXED PRICE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
(POD.quantity_ordered - NVL(POD.quantity_cancelled, 0)- NVL(POD.quantity_billed, 0)) * PLL.price_override) C_AMOUNT_OPEN_INV
, poh.po_header_id
, pol.po_line_id
, por.release_num
, poh.currency_code C_CURRENCY
, nvl(por.po_release_id,-1) release_id
FROM po_distributions_all pod
, mtl_system_items_b msi
, po_line_locations_all pll
, po_lines_all pol
, po_releases_all por
, po_headers_all poh
, po_vendors pov
, financials_system_parameters fsp
, po_line_types plt
WHERE poh.segment1='602088'
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pol.item_id = msi.inventory_item_id (+)
AND msi.organization_id = fsp.inventory_organization_id
AND poh.vendor_id = pov.vendor_id (+)
AND pll.po_release_id = por.po_release_id (+)
AND pol.line_type_id = plt.line_type_id
AND pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED')
AND nvl(pol.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(pll.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(por.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(por.cancel_flag,'N') = 'N'
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pll.cancel_flag,'N') = 'N'
ORDER BY pll.line_location_id
/