Technical‎ > ‎Misc‎ > ‎

Oracle Applications - Deriving the GL# code combination id when the account string is known

There might be times when you need to derive the code combination id of a given or known accounting string. The 2 things to known here are:
1. Accounting string
2. Chart of Accounts Id

The following function should do the trick this will ensure that all the cross validation rules are respected and will throw errors in case of violations:

  FUNCTION get_ccid
  (
    p_coa_id              IN NUMBER,
    p_concatenated_string IN VARCHAR2
  ) RETURN NUMBER IS
    l_ccid     NUMBER;
    l_err      VARCHAR2(2000);
    l_mod_name VARCHAR2(100) := get_module_name('get_ccid');
    l_progress VARCHAR2(5) := '00000';
  BEGIN
    l_progress := '00010';
    IF (g_debug > 0)
    THEN
      debug(l_mod_name || ' : ' || l_progress || ' Start');
      debug(l_mod_name || ' : ' || l_progress || ' p_concatenated_string ' ||
            p_concatenated_string);
      debug(l_mod_name || ' : ' || l_progress || ' p_coa_id ' || p_coa_id);
    END IF;
  
    l_ccid := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
                                    key_flex_code          => 'GL#',
                                    structure_number       => p_coa_id,
                                    validation_date        => to_char(SYSDATE,
                                                                      'DD-MON-YYYY'),
                                    concatenated_segments  => p_concatenated_string);
  
    l_progress := '00020';
    IF (g_debug > 0)
    THEN
      debug(l_mod_name || ' : ' || l_progress || ' l_ccid ' || l_ccid);
    END IF;
  
    l_err := fnd_flex_ext.get_message;
  
    l_progress := '00030';
    IF (l_err IS NOT NULL)
    THEN
      debug(l_mod_name || ' : ' || l_progress || ' l_err ' || l_err);
    END IF;
  
    RETURN l_ccid;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      debug(l_mod_name || ' : ' || l_progress ||
            ' Error occured in deriving gl code combination ' || SQLERRM);
      RAISE;
  END get_ccid;

Comments