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

Number of Pending Gate Entry Report(XML) Done in SONA

FUNCTION

CREATE OR REPLACE FUNCTION APPS.Sona_Receipt_Count (P_Receipt_Date IN DATE)
RETURN NUMBER
IS
   ll_Query1   NUMBER;
   ll_Query2   NUMBER;
   ll_count    NUMBER;
   CURSOR C1 ( P_DATE   DATE) IS   
      SELECT   DISTINCT
                  a.gate_entry_date  || ' ' || TO_CHAR (a.gate_entry_date,
                    'HH:MM')AS "Gate Entry Date and Time",            
                  a.gate_entry_no,
                  pd.destination_subinventory "DEPARTMENT"
           FROM   xx_gate_entry_hdr a,
                  xx_gate_entry_line b,
                  po_headers_all ph,
                  po_lines_all pl,
                  po_distributions_all pd,
                  po_releases_all prel   
WHERE   1 = 1         
      AND  NOT EXISTS (SELECT   * FROM  rcv_shipment_headers  
                                  WHERE a.shipment_no = shipment_num)
      AND  a.gate_entry_hdr_id = b.gate_entry_hdr_id(+)       
      AND  b.po_number = ph.segment1(+)
      AND  ph.po_header_id = pl.po_header_id(+)
      AND  pd.po_header_id(+) = pl.po_header_id
      AND  prel.po_release_id(+) = pd.po_release_id
      AND  pd.destination_subinventory IS NOT NULL
      AND  a.gate_entry_no IS NOT NULL
      AND  TRUNC (a.gate_entry_date) = TRUNC (P_DATE);               
CURSOR C2 (P_date   DATE )  IS                                                    
      SELECT   DISTINCT b.gate_entry_date || ' ' || TO_CHAR (b.gate_entry_date,
                        'HH:MM')AS "Gate Entry Date and Time",        
                        b.gate_entry_no,
                        pd.destination_subinventory "DEPARTMENT"
                 FROM  xx_consignment_gate_entry_hdr a,
                       xx_consignment_gate_entry_line b,
                       po_headers_all ph,
                       po_lines_all pl,
                       po_distributions_all pd,
                       po_releases_all prel
      WHERE  1 = 1
           AND  NOT EXISTS (SELECT   * FROM   rcv_shipment_headers 
                                       WHERE b.shipment_no = shipment_num)
           AND  a.CONSIGNMENT_GATE_ENTRY_HDR_ID = 
                b.CONSIGNMENT_GATE_ENTRY_HDR_ID(+)   
           AND  b.po_number = ph.segment1(+)
           AND  ph.po_header_id = pl.po_header_id(+)
           AND  pd.po_header_id(+) = pl.po_header_id
           AND  prel.po_release_id(+) = pd.po_release_id
           AND  pd.destination_subinventory IS NOT NULL
           AND  b.gate_entry_no IS NOT NULL
           AND  TRUNC (b.gate_entry_date) = TRUNC (P_DATE);             
 BEGIN
   ll_Query1 := 0;
   ll_Query2 := 0;
   FOR c1_rec IN C1 (P_receipt_date)
   LOOP
      ll_Query1 := ll_Query1 + 1;
   END LOOP;
   FOR c2_rec IN C2 (P_receipt_date)
   LOOP
      ll_Query2 := ll_Query2 + 1;
   END LOOP;
   ll_count := ll_Query1 + ll_Query2;
   RETURN ll_count;
   EXCEPTION
      WHEN OTHERS
       THEN
       RETURN 0;
END;                
              
              
                  
 MAIN QUERY:                  

SELECT a.*,sona_receipt_count (a.gate_entry_date) "NO OF PENDING GATE ENTRY"
  FROM (SELECT  DISTINCT a.gate_entry_date
                || ' '|| TO_CHAR (a.gate_entry_date, 'HH24 PM')
                AS "Gate Entry Date and Time",
                a.gate_entry_no,
                pd.destination_subinventory "DEPARTMENT",
                a.gate_entry_Date
           FROM xx_gate_entry_hdr a,
                xx_gate_entry_line b,
                po_headers_all ph,
                po_lines_all pl,
                po_distributions_all pd,
                po_releases_all prel                                  
          WHERE 1 = 1
            AND NOT EXISTS (SELECT * FROM  rcv_shipment_headers
                                     WHERE a.shipment_no = shipment_num)
            AND a.gate_entry_hdr_id = b.gate_entry_hdr_id(+)
            AND b.po_number = ph.segment1(+)
            AND ph.po_header_id = pl.po_header_id(+)
            AND pd.po_header_id(+) = pl.po_header_id
            AND prel.po_release_id(+) = pd.po_release_id
            AND pd.destination_subinventory IS NOT NULL     
            AND a.gate_entry_no IS NOT NULL
            AND a.GATE_ENTRY_DATE BETWEEN              
                NVL(:FROM_GATE_ENTRY_DATE,a.GATE_ENTRY_DATE)
            AND NVL(:TO_GATE_ENTRY_DATE,a.GATE_ENTRY_DATE)
       GROUP BY a.gate_entry_date,
                a.gate_entry_no,
                pd.destination_subinventory
UNION
SELECT DISTINCT  b.gate_entry_date
                 || ' '|| TO_CHAR (b.gate_entry_date, 'HH24 PM')
                 AS "Gate Entry Date and Time",
                 b.gate_entry_no,
                 pd.destination_subinventory "DEPARTMENT",
                 b.gate_entry_date    
            FROM xx_consignment_gate_entry_hdr a,
                 xx_consignment_gate_entry_line b,
                 po_headers_all ph,
                 po_lines_all pl,
                 po_distributions_all pd,
                 po_releases_all prel                         
           WHERE 1 = 1
             AND NOT EXISTS (SELECT * FROM rcv_shipment_headers
                                      WHERE b.shipment_no = shipment_num)
             AND a.consignment_gate_entry_hdr_id=
                 b.consignment_gate_entry_hdr_id(+)
             AND b.po_number = ph.segment1(+)
             AND ph.po_header_id = pl.po_header_id(+)
             AND pd.po_header_id(+) = pl.po_header_id
             AND prel.po_release_id(+) = pd.po_release_id
             AND pd.destination_subinventory IS NOT NULL
             AND b.gate_entry_no IS NOT NULL
             AND b.GATE_ENTRY_DATE BETWEEN
                 NVL(:FROM_GATE_ENTRY_DATE,b.GATE_ENTRY_DATE)
             AND NVL(:TO_GATE_ENTRY_DATE,b.GATE_ENTRY_DATE)
             AND ph.org_id = :ORG_ID
        ORDER BY gate_entry_date ASC)a