Oracle Technical

CODE TO GET RATE ADJUSTMENT

In this article we shall see the code to get Rate Adjustment i.e. adjustment resulting from period rate differences between the reporting period and the previous period.

Below code will return the rate adjustment column in “Trial Balance – Translation” report.

CREATE OR REPLACE package APPS.XXCUST_GL_TRANS_DTLS_PKG

-- =================================================================================

-- Function get_rate_adjustment - to return rate adjustment from secondary ledger

-- =================================================================================

FUNCTION get_rate_adjustment(p_ledger_id NUMBER,

p_ledger_curr_code VARCHAR2,

p_trans_curr_code VARCHAR2,

p_period_name VARCHAR2,

p_code_combination_id NUMBER

) RETURN NUMBER;

-- =================================================================================

-- Function rate_adj_formula - to return rate adjustment after calculating

-- =================================================================================

FUNCTION rate_adj_formula(bb_begin_balance_dr NUMBER,

bb_period_net_dr NUMBER,

bb_begin_balance_cr NUMBER,

bb_period_net_cr NUMBER,

bf_begin_balance_dr NUMBER,

bf_period_net_dr NUMBER,

bf_begin_balance_cr NUMBER,

bf_period_net_cr NUMBER,

DR_OR_CR varchar2

) RETURN NUMBER;

end XXCUST_GL_TRANS_DTLS_PKG;

CREATE OR REPLACE package body APPS.XXCUST_GL_TRANS_DTLS_PKG

-- =================================================================================

-- Function get_rate_adjustment - to return rate adjustment from secondary ledger

-- =================================================================================

FUNCTION get_rate_adjustment

(p_ledger_id NUMBER,

p_ledger_curr_code VARCHAR2,

p_trans_curr_code VARCHAR2,

p_period_name VARCHAR2,

p_code_combination_id NUMBER) RETURN NUMBER

l_rate_adj NUMBER := 0;

BEGIN

SELECT (END_BALANCE - BEGIN_BALANCE - ROUND(PERIOD_NET_DR,2) + ROUND(PERIOD_NET_CR,2)) rate_adj--,tmp.*

INTO l_rate_adj

FROM

(

SELECT nvl(bf.begin_balance_dr, 0) - nvl(bf.begin_balance_cr, 0) BEGIN_BALANCE,

decode(cc.account_type,

'R', nvl(bf.period_net_dr, 0),

'E', nvl(bf.period_net_dr, 0),

'O',DECODE(FND_PROFILE.VALUE('GL_OWNERS_EQUITY_TRANSLATION_RULE'),'PTD', nvl(bf.period_net_dr, 0),

XXCUST_GL_TRANS_DTLS_PKG.rate_adj_formula(bb.begin_balance_dr,

bb.period_net_dr ,

bb.begin_balance_cr,

bb.period_net_cr ,

bf.begin_balance_dr,

bf.period_net_dr ,

bf.begin_balance_cr,

bf.period_net_cr ,

'DR')

XXCUST_GL_TRANS_DTLS_PKG.rate_adj_formula(bb.begin_balance_dr,

bb.period_net_dr ,

bb.begin_balance_cr,

bb.period_net_cr ,

bf.begin_balance_dr,

bf.period_net_dr ,

bf.begin_balance_cr,

bf.period_net_cr ,

'DR')

)PERIOD_NET_DR,

decode(cc.account_type,

'R', nvl(bf.period_net_cr, 0),

'E', nvl(bf.period_net_cr, 0),

'O',DECODE(FND_PROFILE.VALUE('GL_OWNERS_EQUITY_TRANSLATION_RULE'),'PTD', nvl(bf.period_net_cr, 0),

XXCUST_GL_TRANS_DTLS_PKG.rate_adj_formula(bb.begin_balance_dr,

bb.period_net_dr ,

bb.begin_balance_cr,

bb.period_net_cr ,

bf.begin_balance_dr,

bf.period_net_dr ,

bf.begin_balance_cr,

bf.period_net_cr ,

'CR')

XXCUST_GL_TRANS_DTLS_PKG.rate_adj_formula(bb.begin_balance_dr,

bb.period_net_dr ,

bb.begin_balance_cr,

bb.period_net_cr ,

bf.begin_balance_dr,

bf.period_net_dr ,

bf.begin_balance_cr,

bf.period_net_cr ,

'CR')

)PERIOD_NET_CR,

nvl(bf.begin_balance_dr,0) - nvl(bf.begin_balance_cr,0) + nvl(bf.period_net_dr,0) - nvl(bf.period_net_cr,0) END_BALANCE

FROM gl_code_combinations cc,

gl_balances bf, -- Translated ledger ex:- GBP

gl_balances bb -- Ledger to which translation is done ex:- USD

WHERE bf.ledger_id = P_LEDGER_ID

AND bf.currency_code = P_TRANS_CURR_CODE

AND bf.template_id is null

AND bf.actual_flag = 'A'

AND bf.period_name = P_PERIOD_NAME

AND nvl(bf.translated_flag, 'R') in ('Y', 'N')

AND bb.code_combination_id (+) = bf.code_combination_id

AND bb.ledger_id (+) = P_LEDGER_ID

AND bb.currency_code (+) = P_LEDGER_CURR_CODE

AND bb.template_id (+) is null

AND bb.actual_flag (+) = 'A'

AND bb.period_name (+) = P_PERIOD_NAME

AND bb.translated_flag (+) is null

AND cc.code_combination_id = bf.code_combination_id

AND cc.code_combination_id = P_CODE_COMBINATION_ID

AND ((bf.translated_flag = 'N')

(nvl(bf.begin_balance_dr, 0) - nvl(bf.begin_balance_cr, 0) != 0)

(nvl(bf.period_net_dr, 0) != 0)

(nvl(bf.period_net_cr, 0) != 0))

) tmp;

return l_rate_adj;

exception when others then

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

return l_rate_adj;

END get_rate_adjustment;

-- =================================================================================

-- Function rate_adj_formula - to return rate adjustment after calculating

-- =================================================================================

FUNCTION rate_adj_formula(bb_begin_balance_dr NUMBER,

bb_period_net_dr NUMBER,

bb_begin_balance_cr NUMBER,

bb_period_net_cr NUMBER,

bf_begin_balance_dr NUMBER,

bf_period_net_dr NUMBER,

bf_begin_balance_cr NUMBER,

bf_period_net_cr NUMBER,

DR_OR_CR varchar2

) RETURN NUMBER

l_rate_adj NUMBER;

BEGIN

IF DR_OR_CR = 'DR' THEN

SELECT decode(nvl(bb_begin_balance_dr,0) + nvl(bb_period_net_dr,0),

0, decode(nvl(bb_begin_balance_cr,0) + nvl(bb_period_net_cr,0),

0, bf_period_net_dr,

bb_period_net_dr *

((nvl(bf_begin_balance_cr,0) + nvl(bf_period_net_cr,0)) /

(nvl(bb_begin_balance_cr,0)+nvl(bb_period_net_cr,0)))),

bb_period_net_dr *

((nvl(bf_begin_balance_dr, 0) + nvl(bf_period_net_dr, 0)) /

(nvl(bb_begin_balance_dr, 0) + nvl(bb_period_net_dr, 0))))

INTO l_rate_adj

FROM DUAL;

ELSE

SELECT decode(nvl(bb_begin_balance_cr,0) + nvl(bb_period_net_cr,0),

0, decode(nvl(bb_begin_balance_dr,0) + nvl(bb_period_net_dr,0),

0, bf_period_net_cr,

bb_period_net_cr *

((nvl(bf_begin_balance_dr,0) + nvl(bf_period_net_dr,0)) /

(nvl(bb_begin_balance_dr,0)+nvl(bb_period_net_dr,0)))),

bb_period_net_cr *

((nvl(bf_begin_balance_cr, 0) + nvl(bf_period_net_cr, 0)) /

(nvl(bb_begin_balance_cr, 0) + nvl(bb_period_net_cr, 0))))

INTO l_rate_adj

FROM DUAL;

END IF;

RETURN l_rate_adj;

END;

end XXCUST_GL_TRANS_DTLS_PKG;

Use below SQL to get rate adjustment for particular ledger, ledger currency, translated currency , period name, code combination id

select

XXCUST_GL_TRANS_DTLS_PKG.get_rate_adjustment

(p_ledger_id =>12,

p_ledger_curr_code =>'GBP',

p_trans_curr_code => 'USD',

p_period_name => 'DEC-17',

p_code_combination_id => 11111111)

from dual

Please note that values are only shown for illustration and should be changed as per your data.

Oracle Technical

Thursday, November 8, 2018

No comments:

Post a Comment