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