Flat File
The Structure of the flat file must match the structure of the Oracle standard interface tables. If in some cases, they do not match, then we need to get the values from the base tables using the interface programs (validations). These flat files generated may be in text form or a comma or space delimited, variable or fixed format data file.
Upload Program
Once the data has been extracted to a flat file, it is then moved / loaded into user defined staging tables in the target database using SQL Loader or UTL_FILE utilities.
Interface Program
Then PLSQL interface programs are written and run which validate the data in the staging tables and insert the same into the Oracle provided standard Interface tables. If the staging table columns are not matching with the Interface tables, the validations are carried out. For instance,
BEGIN
SELECT USER_JE_CATEGORY_NAME --column name
INTO i_CATEGORY --cursor variable name
FROM GL_JE_CATEGORIES --standard interface table name
WHERE USER_JE_CATEGORY_NAME = REC_CUR.CATEGORY;
Suppose we have 'Category' column in the staging table, but we need the values of 'USER_JE_CATEGORY_NAME'. We need to get the values of 'USER_JE_CATEGORY_NAME' from the 'GL_JE_CATEGORIES' interface table and temporary store the values in 'l_CATEGORY' which is a cursor.
Later we can store the value of 'l_CATEGORY' to the interface table.
Standard Interface Program
Once the interface tables rae populated, the respective interface program (each data element interface has a specific interface program to run) is submitted. the Interface programs validate the data, derive and assign the default values and ultimately populated the production base tables.
The Structure of the flat file must match the structure of the Oracle standard interface tables. If in some cases, they do not match, then we need to get the values from the base tables using the interface programs (validations). These flat files generated may be in text form or a comma or space delimited, variable or fixed format data file.
Upload Program
Once the data has been extracted to a flat file, it is then moved / loaded into user defined staging tables in the target database using SQL Loader or UTL_FILE utilities.
Interface Program
Then PLSQL interface programs are written and run which validate the data in the staging tables and insert the same into the Oracle provided standard Interface tables. If the staging table columns are not matching with the Interface tables, the validations are carried out. For instance,
BEGIN
SELECT USER_JE_CATEGORY_NAME --column name
INTO i_CATEGORY --cursor variable name
FROM GL_JE_CATEGORIES --standard interface table name
WHERE USER_JE_CATEGORY_NAME = REC_CUR.CATEGORY;
Suppose we have 'Category' column in the staging table, but we need the values of 'USER_JE_CATEGORY_NAME'. We need to get the values of 'USER_JE_CATEGORY_NAME' from the 'GL_JE_CATEGORIES' interface table and temporary store the values in 'l_CATEGORY' which is a cursor.
Later we can store the value of 'l_CATEGORY' to the interface table.
Standard Interface Program
Once the interface tables rae populated, the respective interface program (each data element interface has a specific interface program to run) is submitted. the Interface programs validate the data, derive and assign the default values and ultimately populated the production base tables.
No comments:
Post a Comment