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