Deriving Oracle GL Account Code Combination ID’s (CCID’s) in PL/SQL

Given a list of Oracle GL account code segment values…

E.g. “11030-001-0000-00000000-INF1018-000000”

…it is better to use Oracle’s API’s to derive the corresponding code_combination_id (ccid). Don’t just search in the gl_code_combinations table – using the following API’s will allow Oracle GL’s cross-validation rules to assess the validity of a given account code combination, and, if GL is configured to allow dynamic insertion of new account codes (see below), a non-existant code combination will be created automatically in gl_code_combinations:

Resp: General Ledger Super User
Nav: Setup > Financials > Flexfields > Key > Segments > Allow Dynamic Inserts

Note: if you are testing from a client PC (using a developer tool such as Toad or SQL*Developer), ensure that the PC’s nls_language is set:. For example:

ALTER SESSION SET nls_language='American'

If the language is not set, the following kind of error occurs:

Error: Value 11030 for the flexfield segment Nominal does not exist in the value set C4GL_NOMINAL.

…even though the segment value exists in Oracle GL.

There are two public API’s available:

1. PROCEDURE: fnd_flex_ext.get_combination_id

DECLARE
  v_ccid    NUMBER;
  v_return  BOOLEAN;
  v_err     VARCHAR2(2000);
  segments  fnd_flex_ext.segmentarray;
  
BEGIN
  segments(1) := '11030';
  segments(2) := '001';
  segments(3) := '0000';
  segments(4) := '00000000';
  segments(5) := 'INF1018';
  segments(6) := '000000';
  
  v_return := fnd_flex_ext.get_combination_id
              (application_short_name => 'SQLGL'
              ,key_flex_code          => 'GL#'
              ,structure_number       => 50143
              ,validation_date        => SYSDATE
              ,n_segments             => 6
              ,segments               => segments
              ,combination_id         => v_ccid
              --,data_set               => -1
              );
              
  IF (v_return) THEN
    dbms_output.put_line('CCID is '||v_ccid); 
  ELSE
    v_err := fnd_flex_ext.get_message;
    dbms_output.put_line('Error: '||v_err);
  END IF; 
                                                   
END;

Input parameters: the “structure_number” is the chart-of-accounts ID, and the segments of the account code combination are passed in an array variable (fnd_flex_ext.segmentarray).

Output: the “combination_id” OUT parameter contains the ccid, if it can be derived. The fnd_flex_ext.get_message function can be used to display an error message, if an error occurs. For example:

Error: Value INF1018xx is longer than its maximum length of 7 characters.

2. FUNCTION: fnd_flex_ext.get_ccid

A shorthand version of the above procedure, which takes a concatenated list of segment values as an input string:

DECLARE
  v_ccid   NUMBER;
  v_err    VARCHAR2(2000);
BEGIN
  v_ccid := fnd_flex_ext.get_ccid
            (application_short_name => 'SQLGL'
            ,key_flex_code          => 'GL#'
            ,structure_number       => 50143
            ,validation_date        => TO_CHAR(SYSDATE,'DD-MON-YYYY')
            ,concatenated_segments  =>'11030-001-0000-00000000-INF1018-000000'
            );
            
  dbms_output.put_line('CCID is '||v_ccid);

  v_err := fnd_flex_ext.get_message;

  IF (v_err IS NOT NULL) THEN
    dbms_output.put_line('Error is '||v_err);
  END IF;
  
END;

The validation_date is actually passed as a VARCHAR2, so it is imperitive to use one of two possible date formats:

‘DD-MON-YYYY’, or
‘YYYY/MM/DD HH24:MI:SS’

Using Oracle Applications Messages

1. Create a message in Oracle Apps

Resp: Application Developer
Nav: Application > Messages

(For example XXAP999_SUPPLIER_ERR, containing the token &VENDOR_NUM)

2. For a token-substituted message use the following PL/SQL to do the substitution:

BEGIN
  fnd_message.set_name('XXAP', 'XXAP999_SUPPLIER_ERR');
  fnd_message.set_token('VENDOR_NUM', '12345678');
END;

Then use fnd_message.get to retrieve the message string. E.g.

SELECT fnd_message.get
FROM   dual

For non-substituted messages, the set_name and set_token commands are not required. Just use:

SELECT fnd_message.get_string('XXAP', 'XXAP999_SUPPLIER_ERR')
FROM   dual

3. To print to standard concurrent program output or log files do the following:
Set a text string variable, for example

v_msg := fnd_message.get

Then print it to the log or output files:

fnd_file.put_line(fnd_file.log, 'Error message is: ' || v_msg);
fnd_file.put_line(fnd_file.output, 'Error message is: ' || v_msg);

The Ascent of Money

The Ascent of Money – Niall Fergusson (Penguin Books, 2009)

A fantastic read, especially relevant in the aftermath of the global financial crisis of 2008.¬†There’s never a better way to get to grips with a subject than to learn about its history – although I would need to read this a few more times to¬†achieve a full understanding! Highly recommended.

BI Publish an Output File to the File System

Use a bursting control file attached to the BI Publisher Data Definition.

For example:
<?xml version="1.0" encoding="UTF-8" ?> 
<!--                                                                                           -->
<!-- ######################################################################################### -->
<!-- Application : XX General Ledger                                                           -->
<!-- Module      : C4GL075                                                                     -->
<!-- Author      : David Taylor                                                                -->
<!-- Date        : 03-FEB-2011                                                                 -->
<!-- Description : XML Publisher Bursting Control File to publish a HYPERION data extract, in  -->
<!--               MS Excel format, to the directory /erp/FTP/HYPERION/out                     -->
<!--                                                                                           -->
<!-- Modification History                                                                      -->
<!-- ====================                                                                      -->
<!-- Date	  Name	            Ver  Comments                                              -->
<!-- ===========  ================  ===  ===================================================== -->
<!-- 03-FEB-2011  David Taylor      1.0  Created.                                              -->
<!--                                                                                           -->
<!-- ######################################################################################### -->
<!--                                                                                           -->
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
  <xapi:request select="/XXGL075/FILESET">
    <xapi:delivery>
      <xapi:filesystem id="file1" output="/erp/FTP/HYPERION/out/HSP_TEXT_CELL_VALUE.xls" />
    </xapi:delivery>
    <xapi:document output-type="excel" delivery="file1" >
      <xapi:template type="xsl-fo" location="xdo://XXGL.C4GL075.en.00" /> 
    </xapi:document>  
    <xapi:delivery>
      <xapi:filesystem id="file2" output="/erp/FTP/HYPERION/out/archive/HSP_TEXT_CELL_VALUE.xls.${REQUEST_ID}" />
    </xapi:delivery>
    <xapi:document output-type="excel" delivery="file2" >
      <xapi:template type="xsl-fo" location="xdo://XXGL.XXGL075.en.00" /> 
    </xapi:document>
  </xapi:request>
</xapi:requestset>

Generate XML Output from a PL/SQL Concurrent Program

Use the utility DBMS_XMLGEN to generate XML output, as in the following procedure. An example of a query string used to display multiple nested levels of data (using the CURSOR function) is shown below the procedure.
-- -----------------------------------------------------------------------------
   PROCEDURE xml_output_p(p_query  IN VARCHAR2
                         ,p_rowset IN VARCHAR2
                         )
-- -----------------------------------------------------------------------------
IS

l_qryctx     dbms_xmlgen.ctxhandle;
l_length     NUMBER(10);
l_xmlstr     VARCHAR2(32000);
l_offset     NUMBER (10) := 32000;
l_result     CLOB;
l_retrieved  NUMBER (10) := 0;
l_num_rows   NUMBER;

BEGIN

  l_qryctx := dbms_xmlgen.newcontext(p_query);

  -- set rowset tag to ...
  dbms_xmlgen.setrowsettag(l_qryctx,p_rowset);
  dbms_xmlgen.setrowtag(l_qryctx,'ROW');

  -- generate the XML
  l_result := dbms_xmlgen.getxml(l_qryctx);
  l_num_rows := dbms_xmlgen.getNumRowsProcessed(l_qryctx);
  FND_FILE.PUT_LINE(FND_FILE.LOG, 'No of rows processed for XML output = ' || l_num_rows);

  -- format output for 32000 char maximum
  l_length := NVL(dbms_lob.getlength(l_result),0);
  FND_FILE.PUT_LINE(FND_FILE.LOG, 'XML CLOB Length = ' || l_length);

  LOOP EXIT WHEN l_length = l_retrieved;

    IF ((l_length - l_retrieved) &lt; 32000)
    THEN
      SELECT SUBSTR(l_result, l_retrieved + 1)
      INTO   l_xmlstr
      FROM   dual
      ;

      l_retrieved := l_length;
      FND_FILE.PUT(FND_FILE.OUTPUT,l_xmlstr);
    ELSE
      SELECT SUBSTR(l_result,l_retrieved + 1,l_offset)
      INTO   l_xmlstr
      FROM   dual;

      l_retrieved := l_retrieved + l_offset;
      FND_FILE.PUT(FND_FILE.OUTPUT,l_xmlstr);
    END IF;
  END LOOP;

  dbms_xmlgen.closecontext(l_qryctx);

EXCEPTION
  WHEN OTHERS THEN
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error generating XML output data: '||SQLERRM);
    raise_application_error(-20001,'Error generating XML output data in c4fnd018_apps_cleanup_pkg.xml_output_p');
END xml_output_p;

Using the CURSOR function in the SQL query string:

l_query := '
             SELECT fcr.argument1   rep_mode
             ,      fcr.argument2   rep_appl
             ,      fcr.argument3   rep_obj_type
             ,      fcr.argument4   rep_srch_str
             ,      TO_CHAR(SYSDATE,''DD-MM-YYYY'')           rep_date
             ,      TO_CHAR(fcr.request_id,''999999999999'')  rep_request_id
             ,      CURSOR
                    (
                    SELECT cac.object_type
                    ,      cac.object_id
                    ,      cac.object_short_name
                    ,      DECODE(cac.object_type
                                 ,''CONCURRENT PROGRAM'',fcpv.user_concurrent_program_name
                                 ,''REQUEST GROUP UNIT'',frg.request_group_name
                                 )                          object_name
                    ,      cac.request_group_id
                    ,      DECODE(cac.object_type
                                 ,''CONCURRENT PROGRAM'',NULL
                                 ,''REQUEST GROUP UNIT'',cac.group_appl_short_name
                                 ,''REQUEST SET'',NULL
                                 ,''EXECUTABLE'',NULL
                                 ,NULL
                                 )                          group_appl_short_name
                    ,      DECODE(cac.object_type
                                 ,''CONCURRENT PROGRAM'',NULL
                                 ,''REQUEST GROUP UNIT'',NULL
                                 ,''REQUEST SET'',cac.request_set_appl_short_name
                                 ,''EXECUTABLE'',NULL
                                 ,NULL
                                 )                          request_set_appl_short_name
                    ,      DECODE(cac.object_type
                                 ,''CONCURRENT PROGRAM'',cac.conc_prog_appl_short_name
                                 ,''REQUEST GROUP UNIT'',NULL
                                 ,''REQUEST SET'',NULL
                                 ,''EXECUTABLE'',NULL
                                 ,NULL
                                 )                          conc_prog_appl_short_name
                    ,      DECODE(cac.object_type
                                 ,''CONCURRENT PROGRAM'',NULL
                                 ,''REQUEST GROUP UNIT'',NULL
                                 ,''REQUEST SET'',NULL
                                 ,''EXECUTABLE'',cac.executable_appl_short_name
                                 ,NULL
                                 )                          executable_appl_short_name
                    ,      cac.executable_appl_short_name
                    ,      cac.action_type
                    ,      cac.action_date
                    ,      fu.user_name                action_by
                    FROM   c4fnd018_apps_cleanup       cac
                    ,      fnd_user                    fu
                    ,      fnd_concurrent_programs_vl  fcpv
                    ,      fnd_request_groups          frg
                    WHERE  1 = 1
                    AND    cac.action_BY = fu.user_id
                    AND    DECODE(cac.object_type,''CONCURRENT PROGRAM'',cac.object_id,-1) = fcpv.concurrent_program_id(+)
                    AND    DECODE(cac.object_type,''REQUEST GROUP UNIT'',cac.request_group_id,-1) = frg.request_group_id(+)
                    ORDER BY cac.object_short_name ASC
                    ,      cac.object_type ASC
                    )      object_list
             FROM   fnd_concurrent_requests     fcr
             WHERE  fcr.request_id = fnd_global.conc_request_id
             ';