GRN AP receipt Transaction Type wise (Deliver/Accept/Reject/RTV) Quantity Calculate Package

GRN AP receipt Transaction Type wise (Deliver/Accept/Reject/RTV) Quantity Calculate Package
==========
CREATE OR REPLACE PACKAGE APPS.XX_RCV_TRX_GENERIC_PKG AS
/*  Created by Pavankumar Bondage 20-Aug-2016 bondagepm.erp@gmail.com */

   FUNCTION GRN_LINE_IL_TAX_AMT(p_shipment_header_id number,
                        p_shipment_line_id number)
                        return number;
                     
 /*  Following function to get the quantiy of grn line by transaction type wise like ACCEPT,REJECT,CORRECT,Deliver*/
 --      syntax to use
--      select XX_RCV_TRX_GENERIC_PKG.TRX_ACTUAL_QTY(:P_SHIPMENT_HEADER_ID,:P_SHIPMENT_LINE_ID,:P_TRX_TYPE) actual_qnty
--      from dual                     
    FUNCTION trx_actual_qty (
   p_shipment_header_id   NUMBER,
   p_shipment_line_id     NUMBER,
   p_transaction_type     VARCHAR2
) RETURN NUMBER;
END XX_RCV_TRX_GENERIC_PKG;
/

======================================
CREATE OR REPLACE PACKAGE BODY APPS.XX_RCV_TRX_GENERIC_PKG AS

/*  Created by Pavankumar Bondage 20-Aug-2016 bondagepm.erp@gmail.com */

FUNCTION GRN_LINE_IL_TAX_AMT(p_shipment_header_id number,
                        p_shipment_line_id number)
   RETURN NUMBER IS

   
      /* corsor for recive transactions */
      cursor c1 is
     select rt1.TRANSACTION_ID,rt1.PARENT_TRANSACTION_ID,
      NVL (rsl1.uom_conversion_rate, 1) conversion_rate,
     rt1.currency_code
   from rcv_transactions rt1,
        rcv_shipment_headers rsh1,
        rcv_shipment_lines rsl1
        where rt1.SHIPMENT_HEADER_ID=rsh1.SHIPMENT_HEADER_ID
        and rt1.SHIPMENT_LINE_ID=rsl1.SHIPMENT_LINE_ID
        and rt1.SHIPMENT_HEADER_ID=p_shipment_header_id
        and rt1.SHIPMENT_LINE_ID=p_shipment_line_id
        AND rt1.transaction_type = 'RECEIVE';
     
      /* Cursor c1 to calculate tax amount*/
      cursor c2(p_parent_tran_id number) is
      select rt1.TRANSACTION_ID,
      NVL (rsl1.uom_conversion_rate, 1) conversion_rate,
     rt1.currency_code
   from rcv_transactions rt1,
        rcv_shipment_headers rsh1,
        rcv_shipment_lines rsl1
        where rt1.SHIPMENT_HEADER_ID=rsh1.SHIPMENT_HEADER_ID
        and rt1.SHIPMENT_LINE_ID=rsl1.SHIPMENT_LINE_ID
        and rt1.PARENT_TRANSACTION_ID=p_parent_tran_id--111606
        and rt1.SHIPMENT_HEADER_ID=p_shipment_header_id
        and rt1.SHIPMENT_LINE_ID=p_shipment_line_id
        and rt1.TRANSACTION_TYPE='CORRECT';
        v_tax_amt_f number :=0;
        v_tax_amt number:=0;
        v_base_tax_amt number:=0;
        v_tax_amt_c number:=0;
     
   BEGIN

   
      for i in c1 loop
   
      SELECT   jai_rcv_trx_processing_pkg.get_trxn_tax_amount
                                                  (i.TRANSACTION_ID,
                                                   p_shipment_line_id,
                                                  i.CONVERSION_RATE,
                                                   i.CURRENCY_CODE
                                                  )
                                   into v_base_tax_amt
                                 from dual;
   
   
             for j in c2(i.TRANSACTION_ID)loop
                     SELECT   jai_rcv_trx_processing_pkg.get_trxn_tax_amount
                                                  (j.TRANSACTION_ID,
                                                   p_shipment_line_id,
                                                  j.CONVERSION_RATE,
                                                   j.CURRENCY_CODE
                                                  )
                                   into v_tax_amt
                                 from dual;
             v_tax_amt_c:=v_tax_amt_c+v_tax_amt;
           
             end loop;
   
      v_tax_amt_f:=v_base_tax_amt+v_tax_amt_c;
      end loop;
   
      /*  select rt1.TRANSACTION_ID,rt1.PARENT_TRANSACTION_ID,
      NVL (rsl1.uom_conversion_rate, 1) conversion_rate,rsh1.RECEIPT_NUM,
     rt1.currency_code,grn.GRN_TAX_AMT(rsh1.SHIPMENT_HEADER_ID,rsl1.SHIPMENT_LINE_ID ) tax_amount,rsl1.LINE_NUM
   from rcv_transactions rt1,
        rcv_shipment_headers rsh1,
        rcv_shipment_lines rsl1
        where rt1.SHIPMENT_HEADER_ID=rsh1.SHIPMENT_HEADER_ID
        and rt1.SHIPMENT_LINE_ID=rsl1.SHIPMENT_LINE_ID
       -- and rt1.SHIPMENT_HEADER_ID=48874
        --and rt1.SHIPMENT_LINE_ID=61233
        AND rt1.transaction_type = 'RECEIVE'
        order by rsh1.RECEIPT_NUM,rsl1.LINE_NUM*/
   
      return v_tax_amt_f;
   exception
   when others then
    return (-1);
         
   END GRN_LINE_IL_TAX_AMT;
--      syntax to use
--      select XX_RCV_TRX_GENERIC_PKG.TRX_ACTUAL_QTY(:P_SHIPMENT_HEADER_ID,:P_SHIPMENT_LINE_ID,:P_TRX_TYPE) actual_qnty
--      from dual 
   FUNCTION trx_actual_qty (
   p_shipment_header_id   NUMBER,
   p_shipment_line_id     NUMBER,
   p_transaction_type     VARCHAR2)
   RETURN NUMBER
IS
   v_qty                NUMBER:=0;
   v_qty_f              NUMBER;
   v_transaction_type   VARCHAR2 (30);
   v_transaction_id     NUMBER;
   v_count number;
   v_routing_name varchar2(30);
   c_qty number:=0;
ret_rcv_qty number:=0;
v_correct_qty number:=0;
v_rtv_qty number:=0;

   CURSOR c1
   IS
      SELECT rt.*
        FROM rcv_shipment_headers rsh,
             rcv_shipment_lines rsl,
             rcv_transactions rt
       WHERE rsh.shipment_header_id = rsl.shipment_header_id
         AND rsl.shipment_header_id = rt.shipment_header_id
         AND rsl.shipment_line_id = rt.shipment_line_id
         AND rsh.shipment_header_id =
                         p_shipment_header_id
                                             --rsh.RECEIPT_NUM='5102140004389'
         AND rt.shipment_line_id = p_shipment_line_id                  --61178
         AND rt.transaction_type = p_transaction_type;
BEGIN
   v_qty_f := 0;

   FOR r1 IN c1
   LOOP
 
 
     
                            begin
                              SELECT count(1)
                              into v_count
                              FROM rcv_transactions rt
                              WHERE rt.parent_transaction_id = r1.transaction_id;
                            exception
                              when no_data_found then
                               v_count:=0;                         
                            end;
                         
         if v_count>0 then
     
       
          FOR i IN (SELECT rt.transaction_type, rt.transaction_id,rt.QUANTITY
                  FROM rcv_transactions rt
                 WHERE rt.parent_transaction_id = r1.transaction_id)
       
              LOOP
             
                 
                         
                         
                            if i.transaction_type='RETURN TO RECEIVING'then
                         
                         
                               
                                 ret_rcv_qty:=ret_rcv_qty+(-1*i.QUANTITY);
                         
                            elsif i.transaction_type='CORRECT' then
                         
                            v_correct_qty:=v_correct_qty+i.QUANTITY;
                            elsif i.transaction_type='RETURN TO VENDOR' then
                            v_rtv_qty:=v_rtv_qty+(-1*i.QUANTITY);
                            end if;
               
               
           
             
              end loop;
              v_qty:=nvl(ret_rcv_qty,0)+nvl(v_correct_qty,0)+nvl(v_rtv_qty,0)+nvl(r1.QUANTITY,0);
         elsif v_count=0 then
       
         v_qty:=r1.QUANTITY;
       
          end if;
           
     
 

      v_qty_f := v_qty;--+r1.quantity;
   END LOOP;                                              --cursor c1 loop end

   RETURN v_qty_f;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN (-1);
   
--      syntax to use
--      select XX_RCV_TRX_GENERIC_PKG.TRX_ACTUAL_QTY(:P_SHIPMENT_HEADER_ID,:P_SHIPMENT_LINE_ID,:P_TRX_TYPE) actual_qnty
--      from dual
END trx_actual_qty;


END XX_RCV_TRX_GENERIC_PKG;
/