Oracle Apps R12: 11/01/2011

Wednesday, 23 November 2011

Create Code combination ( CCID) using API in Oracle Apps R12

We do create code combination using GL setup. We can use standard API to create code combination from backend .
We have oracle standard API fnd_flex_ext.get_ccid, which we can be use to get CCID from backend by passing the segments or concatenated segments. If there is a combination then this will return the value of that CCID. If there is no combination avialable then it will create CCID and return that value. If there is any error then it will return a value less than or equal to zero.

Note: Dynamic insert should be allowed for accounting flexfield, in order to create code combination from backend.

We can use the following Package to get it.
-->

CREATE OR REPLACE PACKAGE BODY APPS.xxran_ccid_pkg

-- create CCID by passing segments

FUNCTION create_a_combination (

p_segment1 IN VARCHAR2,

p_segment2 IN VARCHAR2,

p_segment3 IN VARCHAR2,

p_segment4 IN VARCHAR2,

p_segment5 IN VARCHAR2,

p_segment6 IN VARCHAR2,

p_segment7 IN VARCHAR2,

p_segment8 IN VARCHAR2,

p_segment9 IN VARCHAR2,

p_flex_delimiter IN VARCHAR2,

p_chart_of_accounts_id IN NUMBER,

p_ccid OUT NUMBER

)

RETURN VARCHAR2

ccid NUMBER := 0;

allsegments VARCHAR2 (256) := NULL;

flexerror VARCHAR2 (2560) := NULL;

BEGIN

--p_error_loc := 'Create CCID';

allsegments :=

p_segment1

|| p_flex_delimiter

|| p_segment2

|| p_flex_delimiter

|| p_segment3

|| p_flex_delimiter

|| p_segment4

|| p_flex_delimiter

|| p_segment5

|| p_flex_delimiter

|| p_segment6

|| p_flex_delimiter

|| p_segment7

|| p_flex_delimiter

|| p_segment8

|| p_flex_delimiter

|| p_segment9;

DBMS_OUTPUT.put_line ('Inside create_a_combination-' || allsegments);

ccid :=

fnd_flex_ext.get_ccid

(application_short_name => 'SQLGL',

key_flex_code => 'GL#',

structure_number => p_chart_of_accounts_id,

validation_date => TO_CHAR

(SYSDATE,

fnd_flex_ext.DATE_FORMAT

concatenated_segments => allsegments

);

p_ccid := ccid;

IF ccid <= 0

THEN

flexerror := fnd_message.get;

END IF;

RETURN flexerror;

EXCEPTION

WHEN OTHERS

THEN

p_ccid := 0;

flexerror := SQLERRM || ' ' || fnd_message.get;

RETURN flexerror;

END create_a_combination;

-- main function

FUNCTION get_code_combination (

p_segment1 IN gl_code_combinations.segment1%TYPE,

p_segment2 IN gl_code_combinations.segment2%TYPE,

p_segment3 IN gl_code_combinations.segment3%TYPE,

p_segment4 IN gl_code_combinations.segment4%TYPE,

p_segment5 IN gl_code_combinations.segment5%TYPE,

p_segment6 IN gl_code_combinations.segment6%TYPE,

p_segment7 IN gl_code_combinations.segment7%TYPE,

p_segment8 IN gl_code_combinations.segment8%TYPE,

p_segment9 IN gl_code_combinations.segment9%TYPE,

p_set_of_books_name IN gl_ledgers.NAME%TYPE,

p_error_msg OUT VARCHAR2,

p_error_loc OUT VARCHAR2

)

RETURN gl_code_combinations.code_combination_id%TYPE

CURSOR c_ccid_validate (

p_segment1 IN gl_code_combinations.segment1%TYPE,

p_segment2 IN gl_code_combinations.segment2%TYPE,

p_segment3 IN gl_code_combinations.segment3%TYPE,

p_segment4 IN gl_code_combinations.segment4%TYPE,

p_segment5 IN gl_code_combinations.segment5%TYPE,

p_segment6 IN gl_code_combinations.segment6%TYPE,

p_segment7 IN gl_code_combinations.segment7%TYPE,

p_segment8 IN gl_code_combinations.segment8%TYPE,

p_segment9 IN gl_code_combinations.segment9%TYPE

)

SELECT code_combination_id

FROM gl_code_combinations

WHERE segment1 = p_segment1

AND segment2 = p_segment2

AND segment3 = p_segment3

AND segment4 = p_segment4

AND segment5 = p_segment5

AND segment6 = p_segment6

AND segment7 = p_segment7

AND segment8 = p_segment8

AND segment9 = p_segment9;

CURSOR c_flex_details

SELECT id_flex_num

FROM fnd_id_flex_segments

WHERE id_flex_num =

(SELECT chart_of_accounts_id

FROM gl_ledgers

WHERE ledger_id = (SELECT ledger_id

FROM gl_ledgers

WHERE NAME = p_set_of_books_name))

AND application_id = (SELECT application_id

FROM fnd_application

WHERE application_short_name = 'SQLGL')

AND enabled_flag = 'Y'

AND ROWNUM = 1;

l_flex_details c_flex_details%ROWTYPE;

l_flexerror VARCHAR2 (2560) := NULL;

l_acc_segment1 gl_code_combinations.segment1%TYPE;

l_acc_segment2 gl_code_combinations.segment2%TYPE;

l_acc_segment3 gl_code_combinations.segment3%TYPE;

l_acc_segment4 gl_code_combinations.segment4%TYPE;

l_acc_segment5 gl_code_combinations.segment5%TYPE;

l_acc_segment6 gl_code_combinations.segment6%TYPE;

l_acc_segment7 gl_code_combinations.segment7%TYPE;

l_acc_segment8 gl_code_combinations.segment8%TYPE;

l_acc_segment9 gl_code_combinations.segment9%TYPE;

l_ccid gl_code_combinations.code_combination_id%TYPE := NULL;

l_user_name fnd_user.user_name%TYPE

:= fnd_profile.VALUE ('USERNAME');

x_delimiter VARCHAR2 (10);

BEGIN

p_error_loc := 'Validate CCID';

FOR c_ccid_validate_rec IN c_ccid_validate (p_segment1,

p_segment2,

p_segment3,

p_segment4,

p_segment5,

p_segment6,

p_segment7,

p_segment8,

p_segment9

)

LOOP

l_ccid := c_ccid_validate_rec.code_combination_id;

RETURN (l_ccid);

END LOOP;

IF l_ccid IS NULL

THEN

fnd_file.put_line (fnd_file.LOG,

'CCID Not Found in gl_code_combinations'

);

fnd_file.put_line (fnd_file.LOG,

'CCID Not Found..Creating New CCID...'

);

-------- Cursor for getting the SOB details---------------------------

OPEN c_flex_details;

FETCH c_flex_details

INTO l_flex_details;

CLOSE c_flex_details;

fnd_file.put_line (fnd_file.LOG,

'chart of account :-'

|| l_flex_details.id_flex_num

);

x_delimiter :=

fnd_flex_ext.get_delimiter

(application_short_name => 'SQLGL',

key_flex_code => 'GL#',

structure_number => l_flex_details.id_flex_num

);

--Create CCID

l_flexerror :=

create_a_combination (p_segment1,

p_segment2,

p_segment3,

p_segment4,

p_segment5,

p_segment6,

p_segment7,

p_segment8,

p_segment9,

x_delimiter,

l_flex_details.id_flex_num,

l_ccid

);

fnd_file.put_line (fnd_file.LOG, 'Newly Created CCID-' || l_ccid);

IF l_ccid <= 0

THEN

fnd_file.put_line (fnd_file.LOG, 'l_flexerror' || l_flexerror);

p_error_msg := l_flexerror;

RETURN (0);

ELSE

p_error_msg := NULL;

RETURN (l_ccid);

END IF;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_flexerror := SQLERRM;

p_error_msg := l_flexerror;

RETURN 0;

WHEN OTHERS

THEN

l_flexerror := SQLERRM;

p_error_msg := l_flexerror;

RETURN 0;

END get_code_combination;

END xxran_ccid_pkg;

/*We can simply use API as well, if we are inside a package. we can use such kind of stand alone package, if we want to call it from outside.*/

Journal import in oracle Apps R12

We have standard interface for journal import. we can use web ADI to load journals from front end itself.
We can use our custom package as well, in order to import journals for PTD or YTD.
Below is a sample script to import journals from backend.

-->

CREATE OR REPLACE PROCEDURE load_glbalances
   IS
      CURSOR gl_balance_cur
      IS
         SELECT journal_batch_name, journal_batch_description, journal_name,
                period, effective_date, CATEGORY, SOURCE, reference_date,
                journal_description, REFERENCE, currency_code,
                conversion_rate_date, conversion_type, conversion_rate,
                ledger_name, of_balseg, of_dept_code, of_lob_code,
                of_product, of_natural_ac, of_intercoseg, of_channel_code,
                of_futureseg1, of_futureseg2, lopbaldb, lopbalcr, opbaldb,
                opbalcr, journal_line_description
           FROM xxran_gl_bal_stg where status is null;

      l_set_of_books_id         gl_sets_of_books.set_of_books_id%TYPE;
      l_user_je_source_name     gl_je_sources.user_je_source_name%TYPE;
      l_user_je_category_name   gl_je_categories.user_je_category_name%TYPE;
      l_user_id                 NUMBER;
      l_currency_code           fnd_currencies.currency_code%TYPE;
      l_conversion_date         DATE;
      l_conversion_type         gl_interface.user_currency_conversion_type%TYPE;
      l_conversion_rate         gl_interface.currency_conversion_rate%TYPE;
      l_period_name             VARCHAR2 (20);
      l_accounted_dr            gl_interface.accounted_dr%TYPE;
      l_accounted_cr            gl_interface.accounted_cr%TYPE;
      l_entered_dr              gl_interface.entered_dr%TYPE;
      l_entered_cr              gl_interface.entered_cr%TYPE;
      l_segment1                gl_interface.segment1%TYPE;
      l_segment2                gl_interface.segment2%TYPE;
      l_segment3                gl_interface.segment3%TYPE;
      l_segment4                gl_interface.segment4%TYPE;
      l_segment5                gl_interface.segment5%TYPE;
      l_segment6                gl_interface.segment6%TYPE;
      l_segment7                gl_interface.segment7%TYPE;
      l_segment8                gl_interface.segment8%TYPE;
      l_segment9                gl_interface.segment9%TYPE;
      l_actual_flag             gl_interface.actual_flag%TYPE;
      accounting_dt             DATE;
      lc_error_loc              VARCHAR2 (50);
      lc_error_msg              VARCHAR2 (500);
      lc_program_name           VARCHAR2 (150);
      lc_error_flag             VARCHAR2 (1);
      lc_code_comb              VARCHAR2(100);
      ln_receivable_code_comb_id NUMBER;
      --lc_error_msg              VARCHAR2 (240);
      lb_valid_source           BOOLEAN;
      lb_valid_category         BOOLEAN;
      lb_valid_currency         BOOLEAN;
      lb_valid_period           BOOLEAN;
      lb_code_comb              BOOLEAN;
    lc_dummy_var              VARCHAR2(5);
   BEGIN

      lc_program_name := 'GL opening balance upload';

      FOR lr_journal_rec IN gl_balance_cur
      LOOP

         lc_error_flag := 'N';
         l_user_je_source_name := lr_journal_rec.SOURCE;
         l_user_je_category_name := lr_journal_rec.CATEGORY;
         l_set_of_books_id := /*get set of books ID */;
         l_period_name := lr_journal_rec.period;
         l_accounted_dr := lr_journal_rec.lopbaldb;
         l_accounted_cr := lr_journal_rec.lopbalcr;
         l_entered_dr := lr_journal_rec.opbaldb;
         l_entered_cr := lr_journal_rec.opbalcr;
         l_segment1 := lr_journal_rec.of_balseg;
         l_segment2 := lr_journal_rec.of_dept_code;
         l_segment3 := lr_journal_rec.of_lob_code;
         l_segment4 := lr_journal_rec.of_product;
         l_segment5 := lr_journal_rec.of_natural_ac;
         l_segment6 := lr_journal_rec.of_intercoseg;
         l_segment7 := lr_journal_rec.of_channel_code;
         l_segment8 := lr_journal_rec.of_futureseg1;
         l_segment9 := lr_journal_rec.of_futureseg2;
         l_actual_flag := 'A';
         l_currency_code := lr_journal_rec.currency_code;
         l_conversion_type := lr_journal_rec.conversion_type;
         l_conversion_rate := lr_journal_rec.conversion_rate;
         /*-- Check for Valid Source
        SELECT 'X'
           INTO lc_dummy_var
           FROM gl_je_sources gjs
          WHERE gjs.user_je_source_name = p_source;
         */
         /*-- Check for Valid Category
         SELECT 'X'
           INTO lc_dummy_var
           FROM gl_je_categories gjc
          WHERE gjc.user_je_category_name = p_category;
        */

         /*-- Check for Valid Currency
         fnd_currencies
        */

         /*-- Check for Valid Period
         SELECT 'X'
           INTO lc_dummy_var
           FROM gl_period_statuses gps
          WHERE gps.period_name = p_period
            AND gps.application_id = p_appl_id
            AND gps.set_of_books_id = p_sob_id
            AND gps.closing_status NOT IN ('C', 'N');
            */
         lc_code_comb := l_segment1 ||'.'|| l_segment2 ||'.'|| '000' ||'.'|| '0000' ||'.'|| l_segment5 ||'.'||
                            l_segment6 ||'.'|| '00000' ||'.'|| '0000' ||'.'|| '0000';
         lb_code_comb :=
               check_code_combination (lc_code_comb,
                                       lc_error_loc,
                                       lc_error_msg,
                                       ln_receivable_code_comb_id
                                      );
         IF NOT lb_code_comb
         THEN
            lc_error_flag := 'Y';
            lc_error_msg := ('error during code combination validation.'||lc_error_msg);
         END IF;

         IF lc_error_flag = 'N'
         THEN
            BEGIN
               INSERT INTO gl_interface
                           (status, set_of_books_id,
                            accounting_date, currency_code,
                            USER_CURRENCY_CONVERSION_TYPE,
                            CURRENCY_CONVERSION_RATE,CURRENCY_CONVERSION_DATE,
                            date_created, created_by, actual_flag,
                            user_je_source_name, user_je_category_name,
                            period_name, entered_dr, entered_cr,
                            accounted_dr, accounted_cr, /*segment1,
                            segment2, segment3, segment4, segment5,
                            segment6, segment7,l_segment8, l_segment9*/
                            CODE_COMBINATION_ID
                           )
                    VALUES ('NEW', l_set_of_books_id,
                            NVL (accounting_dt, SYSDATE), l_currency_code,
                            l_conversion_type,l_conversion_rate,NVL (accounting_dt, SYSDATE),
                            SYSDATE, l_user_id, l_actual_flag,
                            l_user_je_source_name, l_user_je_category_name,
                            l_period_name, l_entered_dr, l_entered_cr,
                            l_accounted_dr, l_accounted_cr, /*l_segment1,
                            l_segment2, l_segment3, l_segment4, l_segment5,
                            l_segment6, l_segment7,l_segment8, l_segment9*/
                            ln_receivable_code_comb_id
                           );
            EXCEPTION
               WHEN OTHERS
               THEN
                  lc_error_flag := 'Y';
                  lc_error_msg := ('error during source validation.');
            END;
         END IF;

         DBMS_OUTPUT.put_line ('Success');
      END LOOP;
   END;
/

-- Call Journal import standard concurrent program to import data from interface table to GL base tables

script to set profile option using API. MO: Operating unit

We used to set profile options from front end. If we have so many responsibilities and we need to set profile option from backend. We can use following script to set profile option.

-->

CREATE OR REPLACE PROCEDURE APPS.xxran_profile_opt_org

v_responsibility_id NUMBER;

v_application_id NUMBER;

v_org_id NUMBER;

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

v_resp_key VARCHAR2 (32);

v_operating_unit VARCHAR2 (50);

CREATE TABLE APPS.XXRAN_PROFILE_ORG

(

OPERATING_UNIT VARCHAR2(50 BYTE),

RESPONSIBILITY_KEY VARCHAR2(33 BYTE)

)

CURSOR xxcur

SELECT operating_unit, responsibility_key

FROM xxran_profile_org;

stat BOOLEAN;

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

stat := null;

BEGIN

v_resp_key := c.responsibility_key;

v_operating_unit := c.operating_unit;

BEGIN

SELECT application_id, responsibility_id

INTO v_application_id, v_responsibility_id

FROM fnd_responsibility

WHERE responsibility_key = v_resp_key;

SELECT organization_id

INTO v_org_id

FROM hr_operating_units

WHERE NAME = v_operating_unit;

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: '||lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.SAVE ('ORG_ID',

v_org_id,

'RESP',

v_responsibility_id,

v_application_id

);

IF stat

THEN

DBMS_OUTPUT.put_line ( 'Stat = TRUE - profile '

|| v_resp_key

|| ' updated'

);

ELSE

DBMS_OUTPUT.put_line ( 'Stat = TRUE - profile '

|| v_resp_key

|| ' NOT updated'

);

END IF;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ( 'failed to add profile option for '

|| v_resp_key

|| ' : '

|| SQLERRM

);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

Script to create Responsibility using API

We can use below mentioned script to create responsibility from backend using API.

-->

CREATE OR REPLACE procedure APPS.xxran_responsibility

v_rowid varchar2(500);

v_web_host_name varchar2(500);

v_web_agent_name varchar2(500);

v_version varchar2(500) := 4;

v_responsibility_id number; -- some variables

v_resp_name varchar2(100) ;

v_application varchar2(100);

v_resp_key varchar2(100);

v_menu_name varchar2(100) ;

v_data_group varchar2(100);

v_req_group varchar2(100) ;

-- ids and other **** used by the API

v_application_id number;

v_data_group_id number;

v_menu_id number;

v_request_group_id number;

lc_status varchar2(1);

lc_error_msg varchar2(240);

/*--Create Cursor table

CREATE TABLE APPS.XXRAN_RESPONSIBILITY_TAB

(

RESP_NAME VARCHAR2(100 BYTE),

APPLICATION VARCHAR2(50 BYTE),

RESP_KEY VARCHAR2(40 BYTE),

MENU_NAME VARCHAR2(60 BYTE),

DATA_GROUP VARCHAR2(40 BYTE),

REQ_GROUP VARCHAR2(50 BYTE),

STATUS VARCHAR2(1 BYTE),

ERROR_MSG VARCHAR2(240 BYTE)

)

cursor cur is

select

resp_name,application,resp_key,

menu_name,data_group,req_group

from XXRAN_RESPONSIBILITY_TAB ;

begin

for c in cur

Loop

lc_status := 'Y';

begin

v_resp_name := c.resp_name;

v_resp_key := c.resp_key;

v_application := c.application;

v_data_group := c.resp_key;

v_menu_name := c.menu_name;

v_req_group := c.req_group;

v_data_group := c.data_group;

Begin

-- get application_id

select application_id

into v_application_id

from fnd_application_vl

where application_name = v_application;

-- get data group id

select data_group_id

into v_data_group_id

from fnd_data_groups

where data_group_name = v_data_group;

-- get the menu_id

select menu_id

into v_menu_id

from fnd_menus_vl

where user_menu_name = v_menu_name;

-- get the request_group_id

if v_req_group is not null then

select request_group_id

into v_request_group_id

from fnd_request_groups

where request_group_name = v_req_group

and application_id = v_application_id;

End If;

-- get current responsibility_id

select FND_RESPONSIBILITY_S.NEXTVAL

into v_responsibility_id

from DUAL;

-- run API

Exception

When others then

dbms_output.put_line('failed to fetch ids' || sqlerrm);

lc_status := 'E';

End;

if lc_status = 'Y'then

fnd_responsibility_pkg.insert_row(

-- out params

x_rowid => v_rowid,

-- in params

x_responsibility_id => v_responsibility_id,

x_application_id => v_application_id,

x_web_host_name => v_web_host_name,

x_web_agent_name => v_web_agent_name,

x_data_group_application_id => v_application_id,

x_data_group_id => v_data_group_id,

x_menu_id => v_menu_id,

x_start_date => to_char(sysdate,'DD-MON-YYYY'),

x_end_date => null,

x_group_application_id => v_application_id,

x_request_group_id => v_request_group_id,

x_version => v_version,

x_responsibility_key => v_resp_key,

x_responsibility_name => v_resp_name,

x_description => '',

x_creation_date => sysdate,

x_created_by => -1,

x_last_update_date => sysdate,

x_last_updated_by => -1,

x_last_update_login => 0

);

End If;

dbms_output.put_line(' added resp');

dbms_output.put_line('v_rowid '||v_rowid);

Exception

when others then

dbms_output.put_line('failed to add resp' || sqlerrm);

End;

End Loop;

exception

when others then

dbms_output.put_line('failed to main ' || sqlerrm);

end;