Drilldown from Consolidated GL to Subledger GL

In this article we shall see how to get subledger entered debit and subledger entered credit from consolidated GL. 

Please find below utility function which returns either entered DR/CR(based on p_cr_dr parameter being either ‘DR’ or ‘CR’). 

This utility function takes below parameters

p_je_batch_id   NUMBER             je_batch_id of consolidated GL.

p_je_header_id  NUMBER            je_header_id of consolidated GL.

p_je_line_num   NUMBER            je_line_num of consolidated GL.

p_cr_dr         VARCHAR2             Can be either ‘DR’ or ‘CR’ based on which this function returns entered Dr or entered Cr amount.

p_entered_amt   NUMBER           Entered Dr/Cr of consolidated GL for given je_batch_id/je_header_id/je_line_num. This value will be returned if there doesn’t exist any drill down from consolidated GL to subledger.

CREATE OR REPLACE FUNCTION xxcust_get_gl_entered_amt

  (p_je_batch_id   NUMBER,

   p_je_header_id  NUMBER,

   p_je_line_num   NUMBER,

   p_cr_dr         VARCHAR2,

   p_entered_amt   NUMBER

   ) RETURN NUMBER

IS

l_entered_amt NUMBER := 0;

l_ledger_id NUMBER := 0;

l_code_combination_id NUMBER := 0;

l_from_period_name   VARCHAR2(500);

l_drill_down_cnt NUMBER := 0;

BEGIN

   -- Check if drill down exists

   select count(*)

   INTO l_drill_down_cnt

   from gl_import_references

   where je_header_id = p_je_header_id

        and je_line_num= p_je_line_num;

   -- if no drill down return entered amount need not translate to ENTERED currency

   if (l_drill_down_cnt=0) then

       return p_entered_amt;

   end if;

   SELECT gbal.ledger_id, gbal.code_combination_id, from_period_name

   INTO l_ledger_id,l_code_combination_id,l_from_period_name

    FROM gl_je_cons_balances_v gbal,GL_CONSOLIDATION_HISTORY ghist

   WHERE 1=1

     AND gbal.je_batch_id = p_je_batch_id

     AND gbal.je_header_id = p_je_header_id

     AND gbal.je_line_num = p_je_line_num

     AND gbal.actual_flag = 'A'

     AND gbal.consolidation_id = ghist.consolidation_id

     AND ghist.to_period_name =  gbal.period_name

    AND NVL(ghist.OBSOLETE_CONSOLIDATION_FLAG,'N') = 'N';

   SELECT DECODE(p_cr_dr,'DR',NVL (period_net_dr, 0),NVL (period_net_cr, 0)) ptd

   INTO l_entered_amt

   FROM gl_balances gb, gl_ledgers gl

   WHERE gb.ledger_id = l_ledger_id

        AND gb.ledger_id = gl.ledger_id

        AND gb.code_combination_id = l_code_combination_id

        AND gb.period_name = l_from_period_name

        AND gb.currency_code = gl.currency_code;

   return l_entered_amt;

exception when others then

    dbms_output.put_line('SQLERRM='||SQLERRM);

    return l_entered_amt;

END xxcust_get_gl_entered_amt;

/