GL Interface

Inbound Interface time Frame:
=============================

1)Receive the Flat file from client and Understand the flat file   = 1
2)go through the funcation document (CV040)     = 2
3)Understand the Inbound Interface process in Oracle Applications = 2
4)Prepare Technical Documentation (CV060)     = 3
5)develop Interface program (.ctl, Pakcage, Request set)         = 3
6)Test Interface with Sample records     = 1
7)Deliver to Client   = 1


3) Find the Standard Interface Process,  Find out Interface Tables, Find out Base Tables, Error Tables, Standard Program
Error Tracking

---------------------------------------------------
GL_INTERFACE
Used to create journal entries, Transfers source information into a target set of books, Creates accounting for Intercompany Transactions etc.
----------------------------------------------------------------------------------------
Base Tables
Following are the base table for Journal entry.
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

-------------------------------------------------
Concurrent program
Journal Import
Journal Posting


=========================

There is no seperate error table for the GL_interface. All the records

which are in error will be in the gl_ interface table itself.
Check the gl import request output to find the error and the error


===============================



1)we have received flat file from client

2)We have created Staging table as per flat file structre

3)developed Control file and uploaded data

4)Developed PL/SQL Program to upload the data from stage into interface table
   1)declare Cursor
   2)open cursor
   3)Validate each record
   4)If no invalid record then insert into interface table.


Note:1) If Record is valid record then we will insert into interface table other wise
        we will insert into Error tables.
     2)Before Inserting the Data into Interface tables first we have to understand the
       Interface table structure and we should know waht data is valid and what data
      is not valid for the all Mandatroy columns.

2)Once the data is Inserted into the Interface table . Then we will submit concurrent
  program to transfer the data from interface table to Base Tables.


5)Run the journal import from GL => Journal => Import => Run
   Give the two parameters 1)Source
          2)Group ID

6) Open the Output if status is 'SUCEESS' then take Request ID.

7)open Journal Enter screen Query the records based on the %requestid% As batch Name
  Select Review journal button we can see the journal detailed transaction

8) If we want correct the journals we can correct from Journal=>Import=>Correct

9) If we want delete the journals we can delete from Journal=>Import=>Delete



Pre-Requisitions:
==================

Before Going to develop the GL interface we are asuppose to check the following
functional setups has completed or not

1)Source and category Creation (Setup=>Journal=>Sources)
2)Period are defined and Open status(Setup=>open\Close)
3)Accounting structuere should be validated
4)Set of Books should be defined 1)currency 2)Calendar3)Chart of Accounts
5)Currency Conversion Rates should be defined.


1 USD = 45 INR Corporate
INR


Funcational Currency : Local Currency

Forein Currency : Other thatn the local currency whatever is there it is foregn currency.

Ex:
For India users INR is functional currency and USD is foreign currency
For US    users USD is functional currency and INR is foreign currency

Entered_Dr
Entered_Cr  : These columns contains the amount (Dr/Cr) what ever we have entered.

Accounted_Dr
Accounted_Cr : These columns contains the amount which is transfered into Foreirn currency



==================================================================================


In this Interface Interface table is GL_INTERFACE
Mandatroy Columns:
==================
1)STATUS
2)ACCOUNTING_DATE
3)CURRENCY_CODE
4)CREATED_BY
5)CREATION_DATE
6)USER_JE_SOURCE_NAME
7)USER_JE_CATEGORY_NAME
8)ACTUAL_FLAG
9)ENTERED_DR
10)ENTERED_CR
11)GROUP_ID
12)PERIOD_NAME
13)SET_OF_BOOKS_ID


1)Status Column will accept any Data. but we will insert always standard string called
         "NEW". It means that we are bringing new data into Oracle Applications.

2)ACCOUNTING_DATE  : Column will accept valid acc_date as per the accounting Periods
3)CURRENCY_CODE    : from FND_CURRENCIES table we can find out wether CUrrency Code is
            valid or Not if Currecny code is available in the Table and enabled it is
            valid.otherwise Invalid
4)CREATED_BY  : IS nothing but UserID we have to find wether  USerID is valid or not
               By using FND_USER table we can find out wether it is valid or not.
5)CREATION_DATE : Should be valid date
                   date should be <= SYSDATE
6)USER_JE_SOURCE_NAME: Valid Source name
                 By using GL_JE_SOURCES table  we can find wether valid source or not.

7)USER_JE_CATEGORY_NAME : Will accept valid cvategory name
        By using GL_JE_CATEGORIES table we can find out wether valid category or Not.
8)ACTUL_FLAG    : This Column will accept single Character
                  Either 'A' 'B' 'E'
                  A=Actuval Amounts
                  B=Budeget Amounts
                  E=Encumbrance Amounts
9)ENTERED_DR
10)ENTERED_CR : Both Columns we accept Numbers Only but same number we have to insert
                into both the columns.
      Always ENTERED_CR = ENTERED_DR . Otherwise Suspense Account will be
               Created.
11)GROUP_ID   : Will accept any Number
12)period_name : Will Accept valid period and that period should be in the Open status
                By using GL_PERIODS we can find wether Period is there or not
                         GL_PERIOD_STATUSES table we can find wether it is in "Open"
                status or Not.
13)SET_OF_BOOKS_ID  : This column will accept valid set of Books ID. By using
              GL_SETS_OF_BOOKS table we can fnd out wether valuid set_of_books_id or
              not.





CREATE OR REPLACE PROCEDURE GE_Pro30(Errbuf  OUT VARCHAR2,
                                      Retcode OUT VARCHAR2) IS
  -- cursor declaration
  CURSOR gl_cur IS
    SELECT
        status,
        set_of_books_id,
        accounting_date,
        currency,
        date_created,
        created_by,
        actual_flag,
        category,
        source,
        curr_conversion,
          segment1,
          segment2,
          segment3,
          segment4,
          segment5,
          entered_dr,
          entered_cr,
          accounted_dr,
          accounted_cr,
          group_id
          FROM XX_TEMP;
l_currencycode    VARCHAR2(25);
l_set_of_books_id NUMBER(3);
l_flag            VARCHAR2(2);
l_error_msg       VARCHAR2(100);
l_err_flag        VARCHAR2(10);
l_category        VARCHAR2(100);
l_USERID          NUMBER(10);
l_count           NUMBER(9) default 0;
BEGIN
 DELETE FROM  gl_interface;
 COMMIT;
FOR rec_cur IN gl_cur LOOP
l_count:=l_count+1;
l_flag    :='A';
--Category Column Validation
BEGIN
 SELECT USER_JE_CATEGORY_NAME
 INTO   l_CATEGORY
 FROM   GL_JE_CATEGORIES
 WHERE  USER_JE_CATEGORY_NAME = REC_CUR.Category;
 EXCEPTION
     WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='Category does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
END;
 --End Category Column Validation
 --User ID column validation
BEGIN
SELECT USER_ID
INTO   L_USERID
FROM   FND_USER
WHERE  USER_ID = REC_CUR.created_by;
EXCEPTION
WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='User ID does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
END;
 --End of Created_by OR UserID column Validation
 --Set of  books Validation
 BEGIN
     SELECT set_of_books_id
     INTO   l_set_of_books_id
     FROM   GL_SETS_OF_BOOKS
     WHERE set_of_books_id=rec_cur.set_of_books_id;
     EXCEPTION
     WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='set of Books ID does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
 END;
--Cuurency Code Validation
 BEGIN
     SELECT currency_code
     INTO   l_currencycode
     FROM   fnd_currencies
     WHERE currency_code=rec_cur.currency
     AND currency_code='USD';
     EXCEPTION
      WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='currency code does not exists';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
 END;
IF   l_flag!='E' THEN
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE');
INSERT INTO gl_interface(status,
      set_of_books_id,
      accounting_date,
      currency_code,
      date_created,
      created_by,
      actual_flag,
      user_je_category_name,
      user_je_source_name,
      user_currency_conversion_type,
      segment1,
      segment2,
      segment3,
      segment4,
      segment5,
      entered_dr,
      entered_cr,
      accounted_dr,
      accounted_cr,
      group_id)
      VALUES
      (rec_cur.status,
       rec_cur.set_of_books_id,
       rec_cur.accounting_date ,
       rec_cur.currency,
       rec_cur.date_created,
       rec_cur.created_by ,
       rec_cur.actual_flag,
       rec_cur.category,
       rec_cur.source,
       rec_cur.curr_conversion,
       rec_cur.segment1  ,
       rec_cur.segment2  ,
       rec_cur.segment3  ,
       rec_cur.segment4  ,
       rec_cur.segment5  ,
       rec_cur.entered_dr,
       rec_cur.entered_cr,
       rec_cur.accounted_dr,
       rec_cur.accounted_cr,
       rec_cur.group_id);
 END IF;
 l_flag:=NULL;
 l_error_msg:=NULL;
  END LOOP;
COMMIT;
END  GE_Pro30;
/