Upload Chart of Accounts Values in Oracle eBS

Uploading Chart of Accounts values in Oracle eBS is a common requirement for organizations looking to streamline financial processes and ensure data accuracy. While manual data entry is an option, leveraging Application Programming Interfaces (APIs) provides a more efficient and reliable method for importing COA values into eBS. In this guide, we’ll explore the standard API option available for uploading COA values in Oracle eBS.

API to upload Chart of Accounts values

We will use Oracle standard API (fnd_flex_loader_apis.up_value_set_value) to load the segment lookups and use staging table to track success or failure after each call. We can also set flags including allow budgeting, allow posting and summary flag using this API.

DECLARE
----------------------------Local Variables---------------------------
   l_enabled_flag             VARCHAR2 (2);
   l_errm                     varchar2(1000);
   l_summary_flag             VARCHAR2 (2);
   l_who_type                 FND_FLEX_LOADER_APIS.WHO_TYPE;
   l_user_id                  NUMBER                := FND_GLOBAL.USER_ID;
   l_login_id                 NUMBER                := FND_GLOBAL.LOGIN_ID;
   l_value_set_name           FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%TYPE;
   l_value_set_value          FND_FLEX_VALUES.FLEX_VALUE%TYPE;
  CURSOR C1 IS
select * from coa_vs where l_mesg is null;

begin
  fnd_global.APPS_INITIALIZE(user_id      => 0,	    -- change as per your env
                             resp_id      => 50838, -- change as per your env
                             resp_appl_id => 101);
  FOR r1 IN C1 LOOP
    BEGIN

   l_value_set_name             := 'your valueset  name';
   l_value_set_value            :=r1.vcode;
   l_enabled_flag               := 'Y';
   l_summary_flag               := r1.vsummaryflag; -- PARENT CHECK DISPLAY
   l_who_type.created_by        := l_user_id;
   l_who_type.creation_date     := SYSDATE;
   l_who_type.last_updated_by   := l_user_id;
   l_who_type.last_update_date  := SYSDATE;
   l_who_type.last_update_login := l_login_id;
 
     fnd_flex_loader_apis.up_value_set_value
                  (p_upload_phase               => 'BEGIN',
                   p_upload_mode                => NULL,
                   p_custom_mode                => 'FORCE',
                   p_flex_value_set_name        => l_value_set_name,
                   p_parent_flex_value_low      => NULL,
                   p_flex_value                 => l_value_set_value,
                   p_owner                      => NULL,
                   p_last_update_date           => null,
                   p_enabled_flag               => l_enabled_flag,
                   p_summary_flag               => l_summary_flag,
                   p_start_date_active          => null,
                   p_end_date_active            => NULL,
                   p_parent_flex_value_high     => NULL,
                   p_rollup_flex_value_set_name => NULL,
                   p_rollup_hierarchy_code      => NULL,
                   p_hierarchy_level            => r1.vlevel,
                   p_compiled_value_attributes  =>  r1.vallow_budgeting||CHR(10)||r1.vallow_posting,                
--omitting p_value_category and p_attribute1 to 50 fields                   p_flex_value_meaning         => l_value_set_value,
                   p_description                => r1.vDesc
                   );

UPDATE coa_vs a
         SET l_mesg = 'Processed'
       WHERE r1.vcode = a.vcode;
    
      COMMIT;
    
    EXCEPTION
      WHEN OTHERS THEN
        --NULL;
        L_ERRM := SQLERRM;
        update coa_vs a
           set l_mesg = L_ERRM           
         WHERE r1.vcode = a.vcode;
      
        COMMIT;
      
    END;
  END LOOP;
END;

Staging Table

The above script uses staging table and loops through each row and calls API. Load your data in staging table as shown below.

set escape \
insert into coa_vs(vcode,vdesc,vsummaryflag,vallow_budgeting, vallow_posting) values('00','Consolidated Regions','Y','N','N');
insert into coa_vs(vcode,vdesc,vsummaryflag,vallow_budgeting, vallow_posting) values('01','Head Office \& Central Region','N','Y','Y');
insert into coa_vs(vcode,vdesc,vsummaryflag,vallow_budgeting, vallow_posting) values('03','Southern Region','N','Y','Y');
insert into coa_vs(vcode,vdesc,vsummaryflag,vallow_budgeting, vallow_posting) values('04','Northern Region','N','Y','Y');
insert into coa_vs(vcode,vdesc,vsummaryflag,vallow_budgeting, vallow_posting) values('05','Eastern Region','N','Y','Y');
insert into coa_vs(vcode,vdesc,vsummaryflag,vallow_budgeting, vallow_posting) values('06','West Region','N','Y','Y');
insert into coa_vs(vcode,vdesc,vsummaryflag,vallow_budgeting, vallow_posting) values('07','Est west Region','N','Y','Y');
insert into coa_vs(vcode,vdesc,vsummaryflag,vallow_budgeting, vallow_posting) values('08','North south','N','Y','Y');

Important Note

1. Ensure that you have the necessary privileges to execute the script in database e.g. use APPS DB user.
2. Always follow your organization’s security policies and best practices for flexfield value updates.
3. Test the script in a development environment before applying it to a production system.

Conclusion

Utilizing APIs to upload chart of accounts values in Oracle eBS streamlines financial data management and ensures data integrity. By leveraging the appropriate API and following best practices for integration, organizations can efficiently import COA values into eBS, enabling better financial reporting and analysis.