SELECT DISTINCT
pha.segment1 ponum,
pha.revision_num revnum,
pla.line_num polinenum,
pha.document_status postatus,
esiv.item_number itemnum,
pla.item_description itemdesc,
rsh.comments comments,
rsh.receipt_num rcptnum
psv.vendor_name supplier,
TO_CHAR(rct.transaction_date,'Mon DD, YYYY','NLS_DATE_LANGUAGE=American')
trxdate,
(CASE WHEN flv.meaning IN ('Receive', 'Deliver')
THEN 'RECEIPT'
WHEN flv.meaning = 'Return to supplier'
THEN 'RETURN'
END) issuetype,
DECODE(TRIM(TO_CHAR(CASE WHEN pla.unit_price IS NULL
THEN 0
ELSE pla.unit_price
END,
'9,999,999,999.99')),
'.00', '0.00',
TRIM(TO_CHAR(CASE WHEN pla.unit_price IS NULL
THEN 0
ELSE pla.unit_price
END,
'9,999,999,999.99'))) currunit,
(SELECT prh.requisition_number
FROM fusion.por_requisition_headers_all prh,
fusion.por_requisition_lines_all prl
WHERE 1 = 1
AND prh.requisition_header_id = prl.requisition_header_id
AND prl.po_header_id = pha.po_header_id
AND prl.po_line_id = pla.po_line_id) prnumber,
(SELECT itl.segment1
FROM fusion.inv_item_locations itl
WHERE 1 = 1
AND itl.inventory_location_id = rsl.locator_id
AND itl.subinventory_code = pda.destination_subinventory)
tobin,
(SELECT rls.lot_num
FROM fusion.rcv_lots_supply rls
WHERE 1 = 1
AND rls.shipment_line_id = rsl.shipment_line_id
AND rls.transaction_id = rct.transaction_id) lotnum,
NVL(rct.subinventory, pda.destination_subinventory) subinv,
rsh.bill_of_lading billoflad,
plt.line_type linetype
FROM fusion.po_headers_all pha,
fusion.po_lines_all pla,
fusion.po_line_locations_all plla,
fusion.po_distributions_all pda,
fusion.egp_system_items_all_v esiv,
fusion.rcv_shipment_headers rsh,
fusion.rcv_shipment_lines rsl,
fusion.rcv_transactions rct,
fusion.poz_suppliers_v psv,
fusion.fnd_lookup_values flv,
fusion.po_line_types_vl plt
WHERE 1 = 1
AND pha.po_header_id = pla.po_header_id
AND pha.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pha.po_header_id = pda.po_header_id
AND pla.po_line_id = pda.po_line_id
AND plla.line_location_id = pda.line_location_id
AND pla.item_id = esiv.inventory_item_id(+)
AND plla.ship_to_organization_id = esiv.organization_id(+)
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rct.shipment_header_id = rsh.shipment_header_id
AND rct.shipment_line_id = rsl.shipment_line_id
AND rct.po_header_id = pha.po_header_id
AND rct.po_line_id = pla.po_line_id
AND rct.po_line_location_id = plla.line_location_id
AND rct.po_distribution_id = pda.po_distribution_id
AND psv.vendor_id = pha.vendor_id
AND rct.transaction_type = flv.lookup_code
AND flv.lookup_type = 'RCV_TRANSACTION_TYPE'
AND flv.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(flv.start_date_active) AND TRUNC(NVL(flv.end_date_active, SYSDATE))
AND flv.language = USERENV('LANG')
AND flv.lookup_code IN ('RECEIVE', 'RETURN TO VENDOR', 'DELIVER')
AND plt.line_type_id = pla.line_type_id
AND TRUNC(rsh.creation_date) >= TO_DATE(SUBSTR(:P_DATE_START, 1, 10), 'RRRR-MM-DD')
AND TRUNC(rsh.creation_date) <= TO_DATE(SUBSTR(:P_DATE_END, 1, 10), 'RRRR-MM-DD')
AND plt.line_type = NVL(:P_PO_LINE_TYPE, plt.line_type)
ORDER BY 1, 3, 2