SELECT(a.gate_entry_date || '-' || TO_CHAR(a.gate_entry_date,
'HH24pm') ) AS "Gate Entry Dt and Time",
a.gate_entry_no, a.receipt_num "Receipt No",
(rsh.creation_date || '-' || TO_CHAR (rsh.creation_date,
'HH24PM')) "Receipt Dt & Time",
ABS(TRUNC ((a.gate_entry_date - (rsh.creation_date)) * 24))"Delay Hrs",
ph.segment1 po_number,
NVL (pd.destination_subinventory, rt.subinventory) "Department",
(select count(1) FROM po_distributions_all pd1,
po_line_locations_all pll1,
po_lines_all pl1,
po_headers_all ph1,
po_releases_all prel1,
rcv_transactions rt1,
rcv_shipment_headers rsh1,
xx_rcv_generation_hdr a1,
xx_rcv_generation_line b1
WHERE 1 = 1
AND pd1.line_location_id(+) = pll1.line_location_id
AND pll1.po_line_id(+) = pl1.po_line_id
AND pl1.po_header_id = ph1.po_header_id
AND prel1.po_release_id(+) = pd1.po_release_id
AND ph1.org_id =nvl(:ORG_ID,ph1.org_id)
AND pll1.line_location_id = rt1.po_line_location_id
AND rt1.transaction_type = 'RECEIVE'
AND rt1.shipment_header_id = rsh1.shipment_header_id
AND a1.shipment_header_id = rsh1.shipment_header_id
AND a1.rcv_gen_hdr_id = b1.rcv_gen_hdr_id
AND ABS (TRUNC ((a1.gate_entry_date - TRUNC(rsh1.creation_date)) * 24)) > 8
and trunc(rsh1.creation_Date) = trunc(rsh.creation_date)) rec_Count
FROM po_distributions_all pd,
po_line_locations_all pll,
po_lines_all pl,
po_headers_all ph,
po_releases_all prel,
rcv_transactions rt,
rcv_shipment_headers rsh,
xx_rcv_generation_hdr a,
xx_rcv_generation_line b
WHERE 1 = 1
AND pd.line_location_id(+) = pll.line_location_id
AND pll.po_line_id(+) = pl.po_line_id
AND pl.po_header_id = ph.po_header_id
AND prel.po_release_id(+) = pd.po_release_id
AND ph.org_id =nvl(:ORG_ID,ph.org_id)
AND pll.line_location_id = rt.po_line_location_id
AND rt.transaction_type = 'RECEIVE'
AND rt.shipment_header_id = rsh.shipment_header_id
AND a.shipment_header_id = rsh.shipment_header_id
AND a.rcv_gen_hdr_id = b.rcv_gen_hdr_id
AND ABS (TRUNC ((a.gate_entry_date - TRUNC(rsh.creation_date)) * 24)) > 8
ORDER BY rsh.creation_date