General Ledger Interface, Journal Import and posting using PL/SQL

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Oct 6, 2017

General Ledger Interface, Journal Import and posting using PL/SQL

There are many companies using a wide range of legacy systems to manage different areas of business, along with EBS. EBS GL is one of the crucial modules, companies generally come up with a requirement to import data from different types of legacy system. This blog will help those who want a solution which is automated, flexible and easy to manage.

Introductions:
The blog covers the basic requirements to import journal entries from any third party tool to Oracle General Ledger (GL). Once Journals are imported into the system then we need to post them.
This process is similar to any other interface where you will use Oracle provided open interface is the table GL_INTERFACE. You will use standard programs to import and post the journals corresponding to entries made in the GL_INTERFACE.

You will follow the sequence of steps as shown in below image:
import data oracle interface

Let’s discuss the steps in detail:

  1. Import data from third party tool to Staging table in Oracle:

Data from third party can be inserted into a staging table in several ways, such as direct table entries, through SQLLOADER etc., whichever is suitable and acceptable. 

  1. Validate data in staging table:

Apart from the custom data validation, we should make some additional checks such as:

  1. Accounting period is valid and open.
  2. Accounting Date or GL Date must fall in Open or Future period only.
  3. Set of books and chart of accounts are configured.
  4. Valid code combination is available for use. 
  1. Insert data in GL_INTERFACE table

GL_INTERFACE is the base table which is available for entries. This table must have data in accordance with the format which can be used by Import program to make records as per General Ledger system.

You must use following columns which are mandatory:

STATUS

LEDGER_ID

USER_JE_SOURCE_NAME

USER_JE_CATEGORY_NAME

ACCOUNTING_DATE

CURRENCY_CODE

DATE_CREATED

CREATED_BY

ACTUAL_FLAG

ENCUMBRANCE_TYPE_ID: Only when ACTUAL_FLAG=’E’

BUDGET_VERSION_ID: Only when ACTUAL_FLAG=’B’

PERIOD_NAME: Only when ACTUAL_FLAG=’B’

ENTERED_DR

ENTERED_CR

You should not populate below columns which are used by system while processing import:

REFERENCE3

REFERENCE11-REFERENCE20

TRANSACTION_DATE

JE_BATCH_ID

JE_HEADER_ID

JE_LINE_NUM

CHART_OF_ACCOUNTS_ID

FUNCTIONAL_CURRENCY_CODE

DATE_CREATED_IN_GL

WARNING_CODE

STATUS_DESCRIPTION

DESC_FLEX_ERROR_MESSAGE

REQUEST_ID

SUBLEDGER_DOC_SEQUENCE_ID

SUBLEDGER_DOC_SEQUENCE_VALUE

GL_SL_LINK_ID

GL_SL_LINK_TABLE

BALANCING_SEGMENT_VALUE

MANAGEMENT_SEGMENT_VALUE

FUNDS_RESERVED_FLAG

GROUP_ID is the key column to maintain reference for each staging entry to journal entry. You will use this column to identify our journal entries in General Ledger after import and post process.

Sample code: 

FUNCTION INSERT_INTERFACE_DATA RETURN CHAR

IS 

CURSOR CUR_DATA

IS

   Write cursor on staging table                                        

BEGIN  

   FOR REC_DATA IN CUR_DATA

   LOOP

         IF NVL(REC_DATA.amount, NULL) IS NOT NULL

      THEN

         lv_segment1 := NULL;

         lv_segment2 := NULL;

         lv_segment3 := NULL;

         lv_segment4 := NULL;

         lv_segment6 := NULL;

         lv_segment7 := NULL;        

         SELECT gcc.segment1 seg1, gcc.segment2 seg2,

                gcc.segment3 seg3,

                gcc.segment4 seg4,

                gcc.segment6 seg6,

                gcc.segment7 seg7

           INTO lv_segment1,

                lv_segment2,

                lv_segment3,

                lv_segment4,

                lv_segment6,

                lv_segment7     

           FROM apps.mtl_parameters mp,

                apps.gl_code_combinations gcc

          WHERE mp.organization_code = REC_DATA.ship_FROM

            AND mp.material_account = gcc.code_combination_id;           

      END IF; 

      IF (    (lv_segment1 IS NOT NULL)

          AND (lv_segment2 IS NOT NULL)

          AND (lv_segment3 IS NOT NULL)

          AND (lv_segment4 IS NOT NULL)

          AND (lv_segment5 IS NOT NULL)

          AND (lv_segment7 IS NOT NULL)

         )

      THEN

              lv_vendor := REC_DATA.VENDOR_CODE;

              ln_amount := REC_DATA.amount;

              ld_ship_date := REC_DATA.ship_date;

              BEGIN 

                      INSERT INTO gl.gl_interface

                      (

                      status,

                      ledger_id,

                      set_of_books_id,

                      accounting_date,

                      currency_code,

                      date_created,

                      created_by,

                      actual_flag,

                      user_je_category_name,

                      user_je_source_name,

                      segment1,

                      segment2,

                      segment3,

                      segment4,

                      segment5,

                      segment6,

                      segment7,

                      entered_dr,

                      entered_cr,

                      date_created_In_gl,

                      Reference10,

                      group_id

                      )

                      VALUES

                      (

                      'NEW',

                      2021,

                      -1,

                      REC_DATA.ship_date,

                      'USD',

                      REC_DATA.ship_date,

                      fnd_global.user_id, 

                      'A',

                      'AutoFreight',

                      'Freight',

                      lv_segment1,

                      '0000',                       --l_segment2,

                      lv_segment3,

                      lv_segment4,

                      lv_frt_accrual,                --l_segment5,

                      '0',         --l_segment6,

                      lv_segment7,

                      NULL,

                      REC_DATA.amount,

                      TRUNC(SYSDATE),

                      lv_reference10,

                      ln_group_id

                      );                      

                      INSERT INTO gl.gl_interface

                      (

                      status,

                      ledger_id,

                      set_of_books_id,

                      accounting_date,

                      currency_code,

                      date_created,

                      created_by,

                      actual_flag,

                      user_je_category_name,

                      user_je_source_name,

                      segment1,

                      segment2,

                      segment3,

                      segment4,

                      segment5,

                      segment6,

                      segment7,

                      entered_dr,

                      entered_cr,

                      date_created_In_gl,

                      Reference10,

                      group_id

                      )

                      VALUES

                      (

                      'NEW',

                      2021,

                      -1,

                      REC_DATA.ship_date,

                      'USD',

                      REC_DATA.ship_date,

                      fnd_global.user_id,

                      'A',

                      'AutoFreight',

                      'Freight',

                      lv_segment1,

                      lv_segment2,

                      lv_segment3,

                      lv_segment4,

                      lv_segment5,

                      '0',

                      lv_segment7,

                      REC_DATA.amount,

                      NULL,

                      TRUNC(SYSDATE),

                      lv_reference10,

                      ln_group_id

                      );                      

              EXCEPTION

              WHEN OTHERS THEN

                   PRINT('Line could not insert in GL Interface: BOL-CARRIER-EQUIPMENT-ORDER TYPE:'||lv_Reference10,'logerrr');             

              END;                  

         END IF;   

   ELSE

         PRINT('Amount 0 so line did not insert in GL Interface: BOL-CARRIER-EQUIPMENT-ORDER TYPE:'||REC_DATA.bol_no

                                                                                                   || ' - '

                                                                                                   || REC_DATA.CARRIER_CODE

                                                                                                   || ' - '

                                                                                                   || REC_DATA.equipment_no

                                                                                                   || ' - '

                                                                                                   || REC_DATA.ORDER_TYPE,'logerrr');

                          

   END IF;                                    

   END LOOP;   

   COMMIT;    

   SELECT SUM(entered_dr), SUM(entered_cr)

     INTO ln_entered_dr, ln_entered_cr

     FROM gl.gl_interface

    WHERE GROUP_ID = ln_group_id;  

   if ln_entered_dr != ln_entered_cr

   then

        PRINT('Debit and Credit amount do not match in GL Interface for Group ID:'||ln_group_id,'logerr');

        PRINT('Debit amount:'||nvl(ln_entered_dr,0),'logout'); 

        PRINT('Credit amount:'||nvl(ln_entered_cr,0),'logout'); 

        DELETE FROM gl.gl_interface

         WHERE GROUP_ID = ln_group_id;     

        COMMIT;       

                           UPDATE_RECORDS('PROCESS_FLAG',

                                        'Error',

                                          'ALL',

                                          'Debit and Credit amount do not match in GL Interface for Group ID:'||ln_group_id);       

        fnd_file.put_line (fnd_file.OUTPUT,'All lines removed FROM GL Interface for Group ID:'||ln_group_id);       

        RETURN 'N';

   else

        fnd_file.put_line (fnd_file.OUTPUT,'Debit amount:'||nvl(ln_entered_dr,0)); 

        fnd_file.put_line (fnd_file.OUTPUT,'Credit amount:'||nvl(ln_entered_cr,0));

          RETURN 'Y';

   end if;  

   RETURN 'Y'; 

EXCEPTION

WHEN OTHERS

THEN

     PRINT('ERROR: in function INSERT_INTERFACE_DATA:'||sqlerrm,'logerrr');

END INSERT_INTERFACE_DATA; 

Run Journal Import program:

Journal import program receives data from GL_INTERFACE, validates it and then converts it into Journal entries compatible with the GL system. 

Base tables:

GL_JE_HEADERS

GL_JE_LINES

GL_JE_BATCHES 

Once the Journal import program completes then it can create one or more GL batches for the same GROUP_ID. This is the main reason that we are using GROUP_ID to identify records and batches for our data. 

For this process we must configure as ACCESS SET in the system for each ledger in operation and Source for each custom source. 

--Access Set

BEGIN

         SELECT access_set_id

           INTO ln_access_set_id

           FROM (SELECT gas.access_set_id

                   FROM gl_access_sets gas, gl_ledgers gl

                  WHERE gas.default_ledger_id = gl.ledger_id

                    AND gl.ledger_id = 2021

               ORDER BY gas.access_set_id)

          WHERE ROWNUM = 1;

EXCEPTION

WHEN OTHERS

THEN

     ln_access_set_id := fnd_profile.VALUE ('GL_ACCESS_SET_ID');

END; 

--Source

SELECT JE_SOURCE_NAME

        INTO lv_source

        FROM gl_je_sources

       WHERE USER_JE_SOURCE_NAME = ‘XXX Source’ 

Submit Program: 

fnd_request.submit_request ('SQLGL',

                            'GLLEZLSRS', -- Short Name of program

                            NULL,

                            NULL,

                            FALSE,

                            ln_access_set_id, --Data Access Set ID

                            lv_source,      --Source 'Freight'

                            2021,           --Ledger

                            g_group_id,     --Group ID

                             'N',         --Post Errors to Suspense

                             'N',         --Create Summary Journals

                             'O'          --Import DFF

                             ); 

You can check for batches that were created by Import program for our set of data. 

SELECT DISTINCT JE_BATCH_ID , NAME

  FROM GL_JE_BATCHES

 WHERE GROUP_ID = g_group_id 

Once all entries have been verified you can then proceed to posting the Journals. You can use ‘Journal Import Execution Report’ to check any error corresponding to Request ID of program and Group ID. 

  1. Run the Journal Post program

Posting the journal means that balance will be updated in the corresponding details and summary accounts. 

To move with this process you must configure Auto Posting Set for each source in the system. 

SELECT AUTOPOST_SET_ID

   INTO ln_autopost_set_id

   FROM GL_AUTOMATIC_POSTING_SETS_V

  WHERE AUTOPOST_SET_NAME = 'XXX_SET'

    AND ENABLED_FLAG = 'Y'; 

Submit Program - Automatic Posting: 

fnd_request.submit_request('SQLGL',

                           'GLPAUTOP', '', '',

                           FALSE,

                           ln_access_set_id,

                           ln_autopost_set_id,

                           '',

                           chr(0),

                                    '','','','','','','','','','','','','','','','','','','','',

                                    '','','','','','','','','','','','','','','','','','','','',

                                    '','','','','','','','','','','','','','','','','','','','',

                                    '','','','','','','','','','','','','','','','','','','','',

                                    '','','','','','','','','','','','','','',''); 

This program submits another program ‘Posting: Single Ledger’ that solely takes care of posting the batches. 

You can track errors or unprocessed batches using any of the two methods:

  1. Check details of report ‘Posting Execution Report’
  2. Using query: 

SELECT count(*) 

  FROM GL_JE_BATCHES

          WHERE GROUP_ID = g_group_id

   AND STATUS != 'P'; 

Conclusion:

There are many companies using a wide range of legacy systems to manage different areas of business, along with EBS. EBS GL is one of the crucial modules, companies generally come up with a requirement to import data from different types of legacy system. This blog will help those who want a solution which is automated, flexible and easy to manage.

This approach covers all steps of GL data flow technically, which are generally performed manually at application front end. For any query on this topic, click below:


Ask Shobhit