Sona Sir Generation Report(XML) Done by SUDHA in SONA

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