Purchase order and Receipt Information

-- ---------------------------------------------------------------------------------
-- Purpose : Get the details of the PO, Receipt and line status information
-- Date      : 29.May.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------

SELECT ph.segment1 po_num,
pl.LINE_NUM,
msi.segment1 item_name,
pv.VENDOR_NAME,
pll.QUANTITY ord_qty,
pll.QUANTITY_RECEIVED qty_rcvd,
pll.QUANTITY_BILLED Invcd_qty,
decode((select count(distinct rsh.RECEIPT_NUM) from RCV_SHIPMENT_lines rsl,
rcv_shipment_headers rsh
where rsl.po_line_id = pl.PO_LINE_ID
and rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID),1,
(select distinct rsh.RECEIPT_NUM from RCV_SHIPMENT_lines rsl,
rcv_shipment_headers rsh
where rsl.po_line_id = pl.PO_LINE_ID
and rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID)
,'Received in More than one Receipt') Receipt_num,
pll.PO_LINE_ID,
ph.CLOSED_CODE order_status,
pl.CLOSED_CODE line_status
from po_headers_all ph
,po_lines_all pl
,po_line_locations_all pll
,po_vendors pv
,mtl_system_items_b msi
where 1=1
and ph.type_lookup_code = 'STANDARD'
and pl.PO_HEADER_ID = ph.PO_HEADER_ID
and pll.PO_HEADER_ID = ph.PO_HEADER_ID
and pl.PO_LINE_ID = pll.PO_LINE_ID
and msi.ORGANIZATION_ID = pll.SHIP_TO_ORGANIZATION_ID
and ph.VENDOR_ID = pv.VENDOR_ID
and msi.INVENTORY_ITEM_ID = pl.ITEM_ID
and ph.SEGMENT1 = :po_num;

-- ---------------------------------------------------------------------------