Thursday, November 8, 2018


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
is

-- =================================================================================
-- 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
IS
-- =================================================================================
-- 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
IS
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')
              OR
            (nvl(bf.begin_balance_dr, 0) - nvl(bf.begin_balance_cr, 0) != 0)
              OR
            (nvl(bf.period_net_dr, 0) != 0)
              OR
            (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
IS
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.

No comments:

Post a Comment