GL INTERFACING (JOURNAL IMPORT PROGRAM)

CREATE OR REPLACE PROCEDURE PROCEDURE_VALIDATION(ERRBUF VARCHAR2,RETCODE VARCHAR2) AS
ERROR_FLAG VARCHAR2(10):='N';
V_ERROR_MSG VARCHAR2(200);
V_ERROR_CODE VARCHAR2(200);
V_CURRENCY_CODE VARCHAR2(20);
V_COMBINATION  VARCHAR2(200);
V_SOURCE_NAME   VARCHAR2(200);
V_BOOKS_ID    NUMBER(20);
CATEGORY_NAME VARCHAR2(200);
V_CREATED_BY  NUMBER(20);
L_COUNT NUMBER(25):=0;
CURSOR C1 IS SELECT * FROM GL_STAGE;
BEGIN
FOR I IN C1 LOOP
L_COUNT:=L_COUNT+1;
BEGIN
IF (I.STATUS='Y') THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Status is valid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
ELSE
ERROR_FLAG :='Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Status is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END IF;
END;


BEGIN
IF (I.ACCOUNTING_DATE)<SYSDATE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'accounting date is  is valid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
ELSE
ERROR_FLAG :='Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'accounting date is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END IF;
END;

BEGIN
IF(I.ACTUAL_FLAG='A') THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'actual flag is valid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
ELSE
ERROR_FLAG :='Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'actual flag is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END IF;
END;

BEGIN
SELECT  (CURRENCY_CODE) INTO V_CURRENCY_CODE FROM FND_CURRENCIES_TL WHERE CURRENCY_CODE=I.CURRENCY_CODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'currency code column FOR THE RECORD IS MISSING PLEASE UPDATE THE currency code COLUMN');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'too many Currency codes');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'currency code is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The error code and error msg:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

BEGIN
SELECT (USER_JE_CATEGORY_NAME) INTO CATEGORY_NAME FROM GL_JE_CATEGORIES_TL WHERE USER_JE_CATEGORY_NAME=I.USER_JE_CATEGORY_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'CATEGORY COLUMN FOR THE RECORD IS MISSING PLEASE UPDATE THE CATEGORY COLUMN');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'TOO MANY CATEGORY_NAMES');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'CATEGORY IS INVALID');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'THE ERROR CODE AND ERROR MSG:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

BEGIN
SELECT USER_JE_SOURCE_NAME INTO V_SOURCE_NAME FROM GL_JE_SOURCES_TL WHERE USER_JE_SOURCE_NAME=I.USER_JE_SOURCE_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'SOURCE NAME COLUMN FOR THE RECORD IS MISSING PLEASE UPDATE THE CREATED BY COLUMN');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'TOO MANY SOURCES_NAMES');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'SOURCE NAME IS INVALID');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'THE ERROR CODE AND ERROR MSG:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

BEGIN
SELECT distinct (CREATED_BY) INTO V_CREATED_BY FROM FND_USER WHERE CREATED_BY=I.CREATED_BY;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'created by Column FOR THE RECORD IS MISSING PLEASE UPDATE THE created by COLUMN');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'too many created_by');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'created by is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The error code and error msg:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

BEGIN
IF I.DATE_CREATED<=SYSDATE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'date created is valid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
ELSE
ERROR_FLAG :='Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'accounting date is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END IF;
END;

BEGIN
SELECT  (SET_OF_BOOKS_ID) INTO V_BOOKS_ID FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=I.SET_OF_BOOKS_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'set of books id column FOR THE RECORD IS MISSING PLEASE UPDATE THE set of books id COLUMN');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'too many category_names');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'set of books id is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The error code and error msg:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

BEGIN
SELECT  DISTINCT (SEGMENT1||SEGMENT2||SEGMENT3||SEGMENT4||SEGMENT5) INTO V_COMBINATION FROM GL_CODE_COMBINATIONS WHERE SEGMENT1=I.SEGMENT1 AND SEGMENT2=I.SEGMENT2 AND  SEGMENT3=I.SEGMENT3 AND SEGMENT4=I.SEGMENT4 AND SEGMENT5=I.SEGMENT5;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'SEGMENTS COLUMN FOR THE RECORD IS MISSING PLEASE UPDATE THE SEGMENTS');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'too many category_names');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'CODE COMBINATION IS INVALID');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'THE ERROR CODE AND ERROR MSG:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

IF (ERROR_FLAG='N') THEN
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,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR
) VALUES
(I.STATUS,
V_BOOKS_ID,
I.ACCOUNTING_DATE,
V_CURRENCY_CODE,
I.DATE_CREATED,
V_CREATED_BY,
I.ACTUAL_FLAG,
CATEGORY_NAME,
V_SOURCE_NAME,
I.SEGMENT1,
I.SEGMENT2,
I.SEGMENT3,
I.SEGMENT4,
I.SEGMENT5,
I.ENTERED_DR,
I.ENTERED_CR,
I.ACCOUNTED_DR,
I.ACCOUNTED_CR);
END IF;
END LOOP;
END PROCEDURE_VALIDATION;
/


Then Once the Data is in GL_Interface then Run the Journal Import Program with Source name and the Group Id then data comes onto GL_JE_HEADERS,GL_JE_LINES and GL_JE_batches
and once you post the balances then we can have data on GL_Balances.

https://docs.oracle.com/cloud/latest/financialscs_gs/OEDMF/GL_INTERFACE_tbl.htm

GL_INTERFACE

GL_INTERFACE contains journal entry batches through Journal Import. You insert rows in this table and then use the Import Journals window to create journal batches. You must supply values for all NOT NULL columns. For a complete description of how to load this table, see the Oracle General Ledger User Guide.

Details

• Schema: FUSION
• Object owner: GL
• Object type: TABLE
• Tablespace: APPS_TS_TX_DATA

Columns

Name	Database	Length	Precision	Not Null	Comments	Flexfield Mapping
STATUS	VARCHAR2	50		Yes	Journal Import status. Use: NEW.
GL_INTERFACE_ID	NUMBER		18		Interface Identifier. Oracle internal use only. Populated by the journal import program.
CREATION_DATE	TIMESTAMP				Who column: date and time of the creation of the row.
LAST_UPDATE_DATE	TIMESTAMP				Who column: date and time of the last update of the row.
LAST_UPDATE_LOGIN	VARCHAR2	32			Who column: session login associated to the user who last updated the row.
LAST_UPDATED_BY	VARCHAR2	64			Who column: user who last updated the row.
OBJECT_VERSION_NUMBER	NUMBER		9		Used to implement optimistic locking. Incremented every time the row is updated. Compared at the start and end of a transaction to detect whether another session has updated the row since it was queried.
LEDGER_ID	NUMBER		18		Ledger identifier. Use the Manage Primary Ledgers task to find valid values.
JE_SOURCE_NAME	VARCHAR2	25			Oracle internal use only. Use column USER_JE_SOURCE_NAME to populate journal source.
JE_CATEGORY_NAME	VARCHAR2	25			Oracle internal use only. Use column USER_JE_CATEGORY_NAME to populate journal category.
ACCOUNTING_DATE	DATE			Yes	Effective date of the journal entry. Used to assign the accounting period.
CURRENCY_CODE	VARCHAR2	15		Yes	Entered currency of the transaction. Use the Manage Currencies task to find valid values. Use the three character ISO currency code. Example: US Dollars is USD.
DATE_CREATED	DATE			Yes	Who column: date the row was created.
CREATED_BY	VARCHAR2	64			Who column: user who created the row.
ACTUAL_FLAG	VARCHAR2	1		Yes	Balance type of the journal. Use: A. Meaning: Actual.
REQUEST_ID	NUMBER		18		Enterprise Service Scheduler: request ID of the job that created or last updated the row.
ENCUMBRANCE_TYPE_ID	NUMBER				Oracle internal use only.
BUDGET_VERSION_ID	NUMBER				Oracle internal use only.
CURRENCY_CONVERSION_DATE	DATE				Date of exchange rate. Date format: YYYY/MM/DD. Required if CURRENCY_CONVERSION_TYPE is not User.
CURRENCY_CONVERSION_TYPE	VARCHAR2	30			Currency conversion type. Use Manage Currency Conversion Types task to identify valid values. For Fusion ERP in the Cloud, use USER_CURRENCY_CONVERSION_TYPE instead.
CURRENCY_CONVERSION_RATE	NUMBER				Foreign currency exchange rate. Mandatory if CURRENCY_CONVERSION_TYPE is User.
SEGMENT1	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT2	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT3	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT4	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT5	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT6	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT7	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT8	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT9	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT10	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT11	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT12	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT13	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT14	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT15	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT16	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT17	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT18	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT19	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT20	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT21	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT22	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT23	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT24	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT25	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT26	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT27	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT28	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT29	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT30	VARCHAR2	25			Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
ENTERED_DR	NUMBER				Transaction debit amount in the entered currency.
ENTERED_CR	NUMBER				Transaction credit amount in the entered currency.
ACCOUNTED_DR	NUMBER				Journal debit amount in the ledger currency.
ACCOUNTED_CR	NUMBER				Journal credit amount in the ledger currency.
TRANSACTION_DATE	DATE				Oracle internal use only. Date of transaction.
REFERENCE1	VARCHAR2	100			Reference column: batch name. Free text field. Not validated.
REFERENCE2	VARCHAR2	240			Reference column: batch description. Free text field. Not validated.
REFERENCE3	VARCHAR2	100			Oracle internal use only.
REFERENCE4	VARCHAR2	100			Reference column: journal entry name. Free text field. Not validated.
REFERENCE5	VARCHAR2	240			Reference column: journal entry description. Free text field. Not validated.
REFERENCE6	VARCHAR2	100			Reference column: journal entry reference. Free text field. Not validated.
REFERENCE7	VARCHAR2	100			Reference column: journal entry reversal flag. Valid values: Y, N.
REFERENCE8	VARCHAR2	100			Reference column: journal entry reversal period. Validation: mandatory if REFERENCE7, journal entry reversal flag, is Y. If average balance processing is enabled, enter effective date for reversal. This will be used to determine the GL period.
REFERENCE9	VARCHAR2	100			Reference column: journal reversal method. Valid values: Y, N. Meanings: Y changes sign, N switches debits/credits.
REFERENCE10	VARCHAR2	240			Reference column: journal entry line description. Free text field. Not validated.
REFERENCE11	VARCHAR2	240			Oracle internal use only.
REFERENCE12	VARCHAR2	100			Oracle internal use only.
REFERENCE13	VARCHAR2	100			Oracle internal use only.
REFERENCE14	VARCHAR2	100			Oracle internal use only.
REFERENCE15	VARCHAR2	100			Oracle internal use only.
REFERENCE16	VARCHAR2	100			Oracle internal use only.
REFERENCE17	VARCHAR2	100			Oracle internal use only.
REFERENCE18	VARCHAR2	100			Oracle internal use only.
REFERENCE19	VARCHAR2	100			Oracle internal use only.
REFERENCE20	VARCHAR2	100			Oracle internal use only.
REFERENCE21	VARCHAR2	240			Reference column: journal line. Free text field. Not validated.
REFERENCE22	VARCHAR2	240			Reference column: journal line. Free text field. Not validated.
REFERENCE23	VARCHAR2	240			Reference column: journal line. Free text field. Not validated.
REFERENCE24	VARCHAR2	240			Reference column: journal line. Free text field. Not validated.
REFERENCE25	VARCHAR2	240			Reference column: journal line. Free text field. Not validated.
REFERENCE26	VARCHAR2	240			Reference column: journal line. Free text field. Not validated.
REFERENCE27	VARCHAR2	240			Reference column: journal line. Free text field. Not validated.
REFERENCE28	VARCHAR2	240			Reference column: journal line. Free text field. Not validated.
REFERENCE29	VARCHAR2	240			Reference column: journal line. Free text field. Not validated.
REFERENCE30	VARCHAR2	240			Reference column: journal line. Free text field. Not validated.
INTERFACE_RUN_ID	NUMBER		18		Oracle internal use only.
JE_BATCH_ID	NUMBER		18		Oracle internal use only.
PERIOD_NAME	VARCHAR2	15			Period name. Use the Manage Accounting Calendars task to identify valid values.
JE_HEADER_ID	NUMBER		18		Oracle internal use only.
JE_LINE_NUM	NUMBER		18		Oracle internal use only.
CHART_OF_ACCOUNTS_ID	NUMBER		18		Oracle internal use only. Chart of accounts identifier.
FUNCTIONAL_CURRENCY_CODE	VARCHAR2	15			Oracle internal use only. Ledger base currency.
CODE_COMBINATION_ID	NUMBER		18		Use the Manage Account Combinations task, column Account ID, to find valid values. Can be used instead of populating the SEGMENT columns individually. If CODE_COMBINATION_ID and the columns beginning with SEGMENT are populated, the SEGMENT column values take precedence.
DATE_CREATED_IN_GL	DATE				Oracle internal use only. Date journal import created batch. Populated by the journal import program.
WARNING_CODE	VARCHAR2	4			Oracle internal use only.
STATUS_DESCRIPTION	VARCHAR2	240			Oracle internal use only. Journal import status description. Populated by the journal import program.
STAT_AMOUNT	NUMBER				Statistical amount.
USER_JE_CATEGORY_NAME	VARCHAR2	25		Yes	Journal entry category. Use the Manage Journal Categories task to find valid values.
USER_JE_SOURCE_NAME	VARCHAR2	25		Yes	Journal entry source user defined name. Use the Manage Journal Sources page to find valid values.
USER_CURRENCY_CONVERSION_TYPE	VARCHAR2	30			Type of exchange rate. Use the Manage Conversion Rate Types task to find valid values. Translated value for CONVERSION_TYPE. Use either CURRENCY_CONVERSION_TYPE or USER_CURRENCY_CONVERSION_TYPE, but not both.
GROUP_ID	NUMBER		18		Groups lines for journals. Use positive integers. Lines with the same GROUP_ID are grouped into the same journal.
SUBLEDGER_DOC_SEQUENCE_ID	NUMBER				Oracle internal use only. Sequential numbering sequence defining column. Populated by journal import program when journal is sequenced.
SUBLEDGER_DOC_SEQUENCE_VALUE	NUMBER				Oracle internal use only. Sequential numbering sequence value. Populated by journal import program when journal is sequenced.
ATTRIBUTE1	VARCHAR2	150			Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE2	VARCHAR2	150			Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE3	VARCHAR2	150			Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE4	VARCHAR2	150			Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE5	VARCHAR2	150			Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE6	VARCHAR2	150			Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE7	VARCHAR2	150			Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE8	VARCHAR2	150			Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE9	VARCHAR2	150			Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE10	VARCHAR2	150			Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE11	VARCHAR2	150			Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE12	VARCHAR2	150			Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE13	VARCHAR2	150			Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE14	VARCHAR2	150			Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE15	VARCHAR2	150			Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE16	VARCHAR2	150			Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE17	VARCHAR2	150			Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE18	VARCHAR2	150			Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE19	VARCHAR2	150			Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE20	VARCHAR2	150			Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE_CATEGORY	VARCHAR2	150			Context code for Journals Lines descriptive flexfield. Use the Manage General Ledger Descriptive Flexfields task to identify valid values. Use ATTRIBUTE1 to ATTRIBUTE10 for the segment values.
ATTRIBUTE_CATEGORY2	VARCHAR2	150			Context code for Journals Captured Information descriptive flexfield. Use the Manage General Ledger Descriptive Flexfields task to identify valid values. Use ATTRIBUTE11 to ATTRIBUTE20 for the segment values.
INVOICE_DATE	DATE				Oracle internal use only.
TAX_CODE	VARCHAR2	15			Oracle internal use only.
INVOICE_IDENTIFIER	VARCHAR2	20			Oracle internal use only.
INVOICE_AMOUNT	NUMBER				Oracle internal use only.
ATTRIBUTE_CATEGORY3	VARCHAR2	150			Oracle internal use only.
USSGL_TRANSACTION_CODE	VARCHAR2	30			Government transaction code. Oracle internal use only. Only applicable if Oracle Federal Financials is used.
DESCR_FLEX_ERROR_MESSAGE	VARCHAR2	240			Oracle internal use only.
JGZZ_RECON_REF	VARCHAR2	240			Oracle internal use only.
AVERAGE_JOURNAL_FLAG	VARCHAR2	1			Oracle internal use only.
GL_SL_LINK_ID	NUMBER				Link to associated subledger data. Oracle internal use only.
GL_SL_LINK_TABLE	VARCHAR2	30			Table containing associated subledger data. Oracle internal use only.
ORIGINATING_BAL_SEG_VALUE	VARCHAR2	25			Originating balancing segment value for intercompany transaction. Overrides default balancing segment value. Should be a valid value for value set used for intercompany.
REFERENCE_DATE	DATE				Reference Date for sequencing to meet statutory requirements in Italy. Date format: YYYY/MM/DD.
SET_OF_BOOKS_ID	NUMBER		18		Oracle internal use only.
BALANCING_SEGMENT_VALUE	VARCHAR2	25			Oracle internal use only.
MANAGEMENT_SEGMENT_VALUE	VARCHAR2	25			Oracle internal use only.
FUNDS_RESERVED_FLAG	VARCHAR2	1			Oracle internal use only.
CODE_COMBINATION_ID_INTERIM	NUMBER		18		Oracle internal use only.
CURRENCY_CONV_DATE_INTER	DATE				Oracle internal use only.
CURRENCY_CONV_TYPE_INTER	VARCHAR2	30			Oracle internal use only.
CURRENCY_CONV_RATE_INTER	NUMBER		18		Oracle internal use only.
LOAD_REQUEST_ID	NUMBER		18		Enterprise Service Scheduler: request ID of the interface load job that created the row.
LEGAL_ENTITY_ID	NUMBER		18		Legal Entity Identifier. Foreign key to XLE_ENTITY_PROFILES
LEGAL_ENTITY_IDENTIFIER	VARCHAR2	30			Unique number used to identify a legal entity. Foreign key to XLE_ENTITY_PROFILES.LEGAL_ENTITY_IDENTIFIER.
LEDGER_NAME	VARCHAR2	30			Ledger name for the journal to be imported. Used in file based data import.

Foreign Keys

Table	Foreign Table	Foreign Key Column
GL_INTERFACE	GL_LEDGERS	LEDGER_ID
GL_INTERFACE	GL_JE_BATCHES	JE_BATCH_ID
GL_INTERFACE	GL_JE_HEADERS	JE_HEADER_ID
GL_INTERFACE	GL_JE_LINES	JE_HEADER_ID, JE_LINE_NUM
GL_INTERFACE	GL_CODE_COMBINATIONS	CODE_COMBINATION_ID

Indexes

Index	Uniqueness	Tablespace	Column
GL_INTERFACE_N1	Non Unique	Default	USER_JE_SOURCE_NAME, LEDGER_ID, SET_OF_BOOKS_ID, GROUP_ID
GL_INTERFACE_N2	Non Unique	Default	REQUEST_ID, JE_HEADER_ID, STATUS, CODE_COMBINATION_ID
GL_INTERFACE_N3	Non Unique	Default	SUBLEDGER_DOC_SEQUENCE_VALUE, SUBLEDGER_DOC_SEQUENCE_ID
GL_INTERFACE_N4	Non Unique	Default	REFERENCE26, REFERENCE22, REFERENCE23

https://bhaskarreddyapps.blogspot.com/2011/11/gl-interfaces.html

FND LOAD SCRIPTS

1. Lookups
-- ------------- FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

2. Concurrent Program
-- ----------------------------- 
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

3. Profile
-- --------- 
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

4. Request Set and Link
-- ------------------------------ 
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

5. FND Message
-- --------------------- FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

6. D2K FORMS
-- ------------------ 
$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME" 
       

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

7. Form Function
-- --------------------- 
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt FUNCTION FUNCTION_NAME="FORM_FUNCTION_NAME"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

8. Alerts
-- --------- 
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE

9. Value Set
-- -------------- 
$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

10. Data Definition and Associated Template
--- ---------------------------------------------------------- 
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt

11. DATA_TEMPLATE (Data Source .xml file)
--- ---------------------------------------------------------- 
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

12. RTF TEMPLATE (Report Layout .rtf file)
--- ------------------------------------------------------- 
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -TERRITORY US -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

. /bo/201/app/oracle/product/appsdev/appl/appsdev.env

                                                           (or)

 . /bo/201/app/oracle/product/appsdev/appl/APPSappsdev_boulder.env

first we need to set the environment in order to run the FND Scripts

 so depending on the Client setups we need to run the Scripts check for the .env file folder and run the script once the environment is setup then we are good to go......

FNDLOAD apps/develop O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="CMGFIN" CONCURRENT_PROGRAM_NAME="CMG_PUB_PAYABLES"--- use the concurrent program short name


FNDLOAD apps/develop O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct CMG_MAG_PAYABLES.ldt PROGRAM APPLICATION_SHORT_NAME="CMGFIN" CONCURRENT_PROGRAM_NAME="CMG_MAG_PAYABLES"-- use concurrent program short name


FNDLOAD apps/develop O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct CMG_CORE_PAYABLES.ldt PROGRAM APPLICATION_SHORT_NAME="CMGFIN" CONCURRENT_PROGRAM_NAME="CMG_CORE_PAYABLES"-- use concurrent p[rogram short name



SELECT fa.application_id           "Application ID",
       fat.application_name        "Application Name",
       fa.application_short_name   "Application Short Name",
       fa.basepath                 "Basepath"
  FROM fnd_application     fa,
       fnd_application_tl  fat
 WHERE fa.application_id = fat.application_id
   AND fat.language      = USERENV('LANG')
   -- AND fat.application_name = 'Payables'  -- <change it>
 ORDER BY fat.application_name;

 . /bo/201/app/oracle/product/appsdev/appl/APPSappsdev_boulder.env





#-- 
#----------------------------------------------------------------------------
#--- 
#-- Program Name  : CMG PPA Reforecast and Recalculate PBV
#--                                                                           
#--  TYPE         : Installation Shell Script
#--                                                                            
#--  AUTHOR       : 
#--                                                                            
#--  DATE         : 
#--                                                                            
#--  VERSION      : 1.0                                                         
#--                                                                            
#--  DESCRIPTION  : Shell Script for Uploading pfr objects for Migration/Upgrade
#--                                                                            
#--  CHANGE HISTORY                                                            
#-- 
#----------------------------------------------------------------------------
#--- 
#--  DATE     AUTHOR           VERSION        REASON                                
#-- 
#----------------------------------------------------------------------------
#-- 26-July-17        Siddharth  1.0 SCR 8759
#-- 
#----------------------------------------------------------------------------

#- define parameters
 apps_pwd=$1; export apps_pwd

#-  call fndload scripts

#- concurrent programs use CONCURRENT_PROGRAM_NAME


FNDLOAD apps/$apps_pwd O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct  $HOME/CMGPPA_forecast.ldt


 FNDLOAD apps/develop 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Then LDT SCripts are generated at     /export/home/sgutha

It is merely time consuming task to create same setup data on each instance separately. Hence to migrate setup data from 1 instance to other (Eg. From DEV to PROD), ldt & lct files are used.

LDT (Data Loader Files)- This files are Used to upload & download setup data on different instances.

LCT (Data Config Files)- This files are used to create LDT Files. There are difference lct files for different sets of data.

Eg: Request Set- afcpreqg.lct, Concurrent Program- afcpprog.lct, Value Set- afffload.lct..etc..

** Now lets take a simple example where you want to migrate 1 concurrent program from Development (DEV) instance to Production (PROD).

Step 1: Login to Unix Development server and change the directory using following Command where you can store your LDT files:

cd $CUST_TOP/ patch/115/import/US/ (where CUST_TOP is your custom top on server)

Step 2: Run the following command by supplying the parameters given in {} as per your need

FNDLOAD {userid/password@connect_string} O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct {file_name.ldt} PROGRAM APPLICATION_SHORT_NAME="{your_application_short_name}" CONCURRENT_PROGRAM_NAME="{your_concurrent_name}"

The above command will generate ldt file in your custom top directory ($CUST_TOP/ patch/115/import/US/ )

Step 3: Copy the LDT file from Development Server to your local desktop

Step 4: Login to Unix Production Server and copy the LDT files from local desktop to Production Server on following path:

$CUST_TOP/ patch/115/import/US/ {file_name.ldt}

Step 5: Finally Run the following command by supplying all parameters in {} to upload concurrent program definition on production application server.
$ FNDLOAD {userid/password@connect_string} 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $CUST_TOP/patch/115/import/US/{file_name}.ldt

Note: 
1) All above FNDLOAD commands are single line commands.
2) All above steps will remain exactly same for all kind of setup data (Eg: Value Set, CP, Req.group, DFF…etc) except the name of LCT file (.lct). LCT file will be different for every different component because the setup data is different.

 sudo su -applmgr

SQL LOADER GUIDE

SQL LOADER is an Oracle utility used to load data into table given a datafile which has the records that need to be loaded. SQL*Loader takes data file, as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files called log file, bad file or reject file, discard file.

• Log file tells you the state of the tables and indexes and the number of logical records already read from the input datafile. This information can be used to resume the load where it left off.
• Bad file or reject file gives you the records that were rejected because of formatting errors or because they caused Oracle errors.
• Discard file specifies the records that do not meet any of the loading criteria like when any of the WHEN clauses specified in the control file. These records differ from rejected records.

Structure of the data file:

The data file can be in fixed record format or variable record format.

Fixed Record Format would look like the below. In this case you give a specific position where the Control file can expect a data field:

7369 SMITH      CLERK        7902  12/17/1980         800

7499 ALLEN      SALESMAN  7698  2/20/1981           1600

7521 WARD      SALESMAN  7698  2/22/1981           1250

7566 JONES      MANAGER   7839  4/2/1981             2975

7654 MARTIN    SALESMAN  7698  9/28/1981           1250

7698 BLAKE      MANAGER   7839  5/1/1981             2850

7782 CLARK      MANAGER   7839  6/9/1981             2450

7788 SCOTT      ANALYST    7566  12/9/1982           3000

7839 KING        PRESIDENT          11/17/1981         5000

7844 TURNER    SALESMAN  7698  9/8/1981            1500

7876 ADAMS     CLERK         7788  1/12/1983          1100

7900 JAMES      CLERK         7698  12/3/1981          950

7902 FORD        ANALYST     7566  12/3/1981          3000

7934 MILLER     CLERK         7782  1/23/1982          1300

Variable Record Format would like below where the data fields are separated by a delimiter.

Note: The Delimiter can be anything you like. In this case it is “|”

1196700|9|0|692.64

1378901|2|3900|488.62

1418700|2|2320|467.92

1418702|14|8740|4056.36

1499100|1|0|3.68

1632800|3|0|1866.66

1632900|1|70|12.64

1637600|50|0|755.5

Structure of a Control file:

Sample CTL file for loading a Variable record data file:

OPTIONS (SKIP = 1)   --The first row in the data file is skipped without loading

LOAD DATA

INFILE '$FILE'             -- Specify the data file  path and name

APPEND                       -- type of loading (INSERT, APPEND, REPLACE, TRUNCATE

INTO TABLE "APPS"."BUDGET"   -- the table to be loaded into

FIELDS TERMINATED BY '|'           -- Specify the delimiter if variable format datafile

OPTIONALLY ENCLOSED BY '"'   --the values of the data fields may be enclosed in "

TRAILING NULLCOLS     -- columns that are not present in the record treated as null

(ITEM_NUMBER    "TRIM(:ITEM_NUMBER)", -- Can use all SQL functions on columns

QTY                 DECIMAL EXTERNAL,

REVENUE             DECIMAL EXTERNAL,

EXT_COST            DECIMAL EXTERNAL TERMINATED BY WHITESPACE "(TRIM(:EXT_COST))"  ,

MONTH           "to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD-MON-YY')" ,

DIVISION_CODE    CONSTANT "AUD"  -- Can specify constant value instead of Getting value from datafile

)

OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.

SKIP = n — Number of logical records to skip (Default 0)

LOAD = n — Number of logical records to load (Default all)

ERRORS = n — Number of errors to allow (Default 50)

ROWS = n   — Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)

BINDSIZE = n — Size of conventional path bind array in bytes (System-dependent default)

SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} — Suppress messages during run

(header, feedback, errors, discards, partitions, all)

DIRECT = {TRUE | FALSE} –Use direct path (Default FALSE)

PARALLEL = {TRUE | FALSE} — Perform parallel load (Default FALSE)

LOADDATA statement is required at the beginning of the control file.

INFILE: INFILE keyword is used to specify location of the datafile or datafiles.

INFILE* specifies that the data is found in the control file and not in an external file. INFILE ‘$FILE’, can be used to send the filepath and filename as a parameter when registered as a concurrent program.

INFILE   ‘/home/vision/kap/import2.csv’ specifies the filepath and the filename.

Example where datafile is an external file:

LOAD DATA

INFILE   ‘/home/vision/kap/import2.csv’

INTO TABLE kap_emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name, department_num, department_name )

Example where datafile is in the Control file:

LOAD DATA

INFILE *

INTO TABLE kap_emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name, department_num, department_name )

BEGINDATA

7369,SMITH,7902,Accounting

7499,ALLEN,7698,Sales

7521,WARD,7698,Accounting

7566,JONES,7839,Sales

7654,MARTIN,7698,Accounting

Example where file name and path is sent as a parameter when registered as a concurrent program

LOAD DATA

INFILE ‘$FILE’

INTO TABLE kap_emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name, department_num, department_name )

TYPE OF LOADING:

INSERT   — If the table you are loading is empty, INSERT can be used.

APPEND  — If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn’t already exist, the new rows are simply loaded.

REPLACE — All rows in the table are deleted and the new data is loaded

TRUNCATE — SQL*Loader uses the SQL TRUNCATE command.

INTOTABLEis required to identify the table to be loaded into. In the above example INTO TABLE “APPS”.”BUDGET”, APPS refers to the Schema and BUDGET is the Table name.

FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc)

OPTIONALLY ENCLOSED BY ‘”‘ specifies that data fields may also be enclosed by quotation marks.

TRAILINGNULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

Loading a fixed format data file:

LOAD DATA

INFILE ‘sample.dat’

INTO TABLE emp

( empno         POSITION(01:04)   INTEGER EXTERNAL,

ename          POSITION(06:15)   CHAR,

job            POSITION(17:25)   CHAR,

mgr            POSITION(27:30)   INTEGER EXTERNAL,

sal            POSITION(32:39)   DECIMAL EXTERNAL,

comm           POSITION(41:48)   DECIMAL EXTERNAL,

deptno         POSITION(50:51)   INTEGER EXTERNAL)

Steps to Run the SQL* LOADER from UNIX:

At the prompt, invoke SQL*Loader as follows:

sqlldr USERID=scott/tiger CONTROL=<control filename> LOG=<Log file name>

SQL*Loader loads the tables, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.

Register as concurrent Program:

Place the Control file in $CUSTOM_TOP/bin.

Define the Executable. Give the Execution Method as SQL*LOADER.

Define the Program. Add the Parameter for FILENAME.

Skip columns:

You can skip columns using the ‘FILLER’ option.

Load Data
—
—
—
TRAILING  NULLCOLS
(
name Filler,
Empno ,
sal
)

here the column name will be skipped.

Please continue to read our part-2 of SQL Loader article

SQL LOADER(Control File to send the .csv file dynamically from the front end through EBS Suite)

LOAD DATA 
INFILE '$FILE'
TRUNCATE INTO TABLE CMG_TEST2
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(PPA_SUBCLASS_ID,
GL_number,
REMARKS,
BIPAD,
ISSUE_CODE,
AMOUNT,
CUR_CODE)

1.)Then Place the '.CSV' file in the above mentioned Location.
('/hdgstg01/prodapps/plsql/incoming/Canadian_Exchange_201701.csv')
2.)Then always Place the .ctl (control file ) in the Bin folder depending upon the application..

(/bo/201/app/oracle/product/appsdev/appl/fnd/11.5.0/bin/LOAD_TEST.ctl)

3.)Then create the executable file according to the Control file i.e call the LOAD_test file as executablie file and assign it to the concurrent job.

4.Then Look for log and output file to find the errors if we have any....