Tuesday, June 12, 2018

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;
/






No comments:

Post a Comment