- OracleApps Epicenter - http://www.oracleappshub.com -

Generation of GL code combinations by Sql

Here is workaround for automatic/mass CCID generation or insertion of new CCID’s in GL_CODE_COMBINATIONS Table, when new values in Accounting Flexfield Segment are created, EXCEPT “Dynamic Insert” option activated .

you can use the api (function) -> fnd_flex_ext.get_ccid to create the ccid.

If the combination already exists, it returns the existing ccid else creates a new one and returns the id. Explicit commit command needs to be executed after calling this function.

Use below function which returns the CCID if it is exist and create the CCID if it doesn't exist in the application

 

  1.  
  2. <pre>fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
  3. key_flex_code =>'GL#',
  4. structure_number =>l_coa_id,
  5. validation_date =>to_char(SYSDATE [1],apps.fnd_flex_ext.date_format),
  6. concatenated_segments =>l_account);
  7.  

Make sure you have intialize the manadtory parameter if you are running the code in SQL Plus/TOAD

  1.  
  2. BEGIN [2]
  3. FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_appl_id);
  4. END [3];
  5.  

Moreover Flex Structure you can find by this query

  1.  
  2. SELECT [4] fifst.STRUCTURE_VIEW_NAME,
  3. fifst.CONCATENATED_SEGMENT_DELIMITER,
  4. fifs.application_column_name,
  5. fifs.segment_name,
  6. fifs.segment_num,
  7. ffs.flex_value_set_name
  8. FROM [5] fnd_id_flex_structures_vl fifst,
  9. fnd_id_flex_segments fifs,
  10. fnd_flex_value_sets ffs
  11. WHERE [6] fifst.id_flex_structure_code = 'ACCOUNTING_FLEXFIELD'
  12. AND [7] fifst.application_id = 101
  13. AND [7] fifst.id_flex_code = 'GL#'
  14. AND [7] fifs.application_id = fifst.application_id
  15. AND [7] fifs.id_flex_code = fifst.id_flex_code
  16. AND [7] fifs.id_flex_num = fifst.id_flex_num
  17. AND [7] ffs.flex_value_set_id = fifs.flex_value_set_id;
  18.