Opening Oracle EBS Page from APEX

In this article we shall see how to invoke EBS page from APEX page. Create a link in apex page and mention the URL as below:

http://<<servername>>:port/OA_HTML/RF.jsp?function_id=<functionId>

where

servername -> application server name

port -> EBS port

functionId -> function which you want to invoke.

Below code will help in retrieving host URL:

APEX_UTIL.HOST_URL(NULL)

Any additional parameters to the form function can be passed by appending parameters at the end of the URL.

For ex:

http://<<servername>>:port/OA_HTML/RF.jsp?function_id=XXCUST_ICX_POR_REQMGMT_APPR_HIST&param1=<paramvalue1>&param2=<paramvalue2>

If EBS page has to be opened in new tab/new window add below code in Link Attributes section in APEX page:

target="_blank"

for ex:-

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.