Real Time (Implementation Project ):GL Interfaces (Single Insertion Process)

Interfaces :

      Flat File                                                                

   |

   |--> SQL *Loader

   |

Staging Tables

   |

   |--> PLSQL Package

   |

Interface Tables

   |

   |--> Standard Program

   |

     Base Tables   

              

GL Interface : (GL Journals)

 Process 
 ---------

Flat File
   |
   |--> SQL *Loader
   |
XXGL_INT_STG
   |
   |--> PLSQL Package
   |
GL_INTERFACE
   |
   |--> Journal Import
   |
GL_JE_HEADERS
GL_JE_LINES

Single Insertion Process :

--------------------------
1. Identify the Mandatory columns from Front End

At Headers :
------------
Journal Name
Ledger Name
Period Name
Category
Effective Date
Currency
Conversion Date

At Lines :
----------
Line Num
Account
Debit Amount
Credit Amount

2. Identify the Mandatory columns from Back End

SELECT COLUMN_NAME
FROM ALL_TAB_COLS
WHERE TABLE_NAME = 'GL_JE_HEADERS'
AND NULLABLE = 'N'
AND     OWNER = 'GL';

GL_JE_HEADERS :
---------------
ACCRUAL_REV_CHANGE_SIGN_FLAG
TAX_STATUS_CODE
JE_HEADER_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
LEDGER_ID
JE_CATEGORY
JE_SOURCE
PERIOD_NAME
NAME
CURRENCY_CODE
STATUS
DATE_CREATED
ACCRUAL_REV_FLAG
MULTI_BAL_SEG_FLAG
ACTUAL_FLAG
DEFAULT_EFFECTIVE_DATE


GL_JE_LINES :
-------------
JE_HEADER_ID
JE_LINE_NUM
LAST_UPDATE_DATE
LAST_UPDATED_BY
LEDGER_ID
CODE_COMBINATION_ID
PERIOD_NAME
EFFECTIVE_DATE
STATUS


GL_INTERFACE :
--------------
STATUS
ACCOUNTING_DATE
CURRENCY_CODE
DATE_CREATED
CREATED_BY
ACTUAL_FLAG
USER_JE_CATEGORY_NAME
USER_JE_SOURCE_NAME


3. Check whether the Base Table Mandatory columns are exist in 
Interface table or not

JE_HEADER_ID
,LEDGER_ID
,PERIOD_NAME
,CURRENCY_CODE
,STATUS
,DATE_CREATED
,ACTUAL_FLAG

JE_HEADER_ID
,JE_LINE_NUM
,LEDGER_ID
,CODE_COMBINATION_ID
,PERIOD_NAME
,STATUS

STATUS
,ACCOUNTING_DATE
,CURRENCY_CODE
,DATE_CREATED
,CREATED_BY
,ACTUAL_FLAG
,USER_JE_CATEGORY_NAME
,USER_JE_SOURCE_NAME


4. Remove the Duplicate Columns from 3rd Step Columns

JE_HEADER_ID
,LEDGER_ID
,PERIOD_NAME
,CURRENCY_CODE
,STATUS
,DATE_CREATED
,ACTUAL_FLAG
,JE_LINE_NUM
,CODE_COMBINATION_ID
,ACCOUNTING_DATE
,CREATED_BY
,USER_JE_CATEGORY_NAME
,USER_JE_SOURCE_NAME


5. Compare the 4th step columns with 1st step columns
 (Compare the Back End Mandatory Columns with Front End Mandatory
   Columns) -- To Check if any columns are missing

 LEDGER_ID
,PERIOD_NAME
,CURRENCY_CODE
,STATUS
,DATE_CREATED
,ACTUAL_FLAG
,JE_LINE_NUM
,CODE_COMBINATION_ID
,ACCOUNTING_DATE
,CREATED_BY
,USER_JE_CATEGORY_NAME
,USER_JE_SOURCE_NAME
,ENTERED_DR
,ENTERED_CR

6. Insert a single record by using the 5th step columns

INSERT INTO GL_INTERFACE
(
 LEDGER_ID
,PERIOD_NAME
,CURRENCY_CODE
,STATUS
,DATE_CREATED
,ACTUAL_FLAG
,ACCOUNTING_DATE
,CREATED_BY
,USER_JE_CATEGORY_NAME
,USER_JE_SOURCE_NAME
,JE_LINE_NUM
,CODE_COMBINATION_ID
,ENTERED_DR
,ENTERED_CR
)
VALUES
(1
,'Dec-12'
,'USD'
,'NEW'
,'25-JAN-2010'
,'A'
,'25-JAN-2010'
,1318
,'Adjustment'
,'Manual'
,1
,12854
,100
,NULL
);


INSERT INTO GL_INTERFACE
(
 LEDGER_ID
,PERIOD_NAME
,CURRENCY_CODE
,STATUS
,DATE_CREATED
,ACTUAL_FLAG
,ACCOUNTING_DATE
,CREATED_BY
,USER_JE_CATEGORY_NAME
,USER_JE_SOURCE_NAME
,JE_LINE_NUM
,CODE_COMBINATION_ID
,ENTERED_DR
,ENTERED_CR
)
VALUES
(1
,'Dec-12'
,'USD'
,'NEW'
,'25-JAN-2010'
,'A'
,'25-JAN-2010'
,1318
,'Adjustment'
,'Manual'
,2
,12833
,NULL
,100
);