LOAD DATA INFILE '/app/Integrations/AP/Response/*.csv'
APPEND
INTO TABLE XXJG_PAY_FILE
FIELDS TERMINATED BY ' '
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
bank_acc_number ,
narration,
payment_no,
amount,
status
)
Showing posts with label SQL Loader. Show all posts
Showing posts with label SQL Loader. Show all posts
Oracle EBS Control File Creation
--------------------------------------------------------------------------------
--TYPE : CONTROL FILE 1
--NAME : XXJG_GL_INT_DATA_LOAD.CTL
--RICEW OBJECT ID :
--DATE WRITTEN : 3-MAR-2012
--PURPOSE : CONTROL FILE FOR LOADING GL DATA INTO STAGING TABLE
--MAINTENANCE HISTORY:
--
--DATE ISSUE# NAME REMARKS
------------- -------- ----------------- -------------------------------
--3-MAR-2012 1.0 JG INITIAL VERSION
--------------------------------------------------------------------------------
OPTIONS (SKIP = 1)
LOAD DATA
APPEND
INTO TABLE XXJG.XXJG_GL_DETAILS_STG
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
LOAD_ID "REPLACE(RTRIM(LTRIM(:LOAD_ID)),CHR(13),'')"
, ENTITY_CODE "REPLACE(RTRIM(LTRIM(:ENTITY_CODE)),CHR(13),'')"
, HEADER_DESCRIPTION "REPLACE(RTRIM(LTRIM(:HEADER_DESCRIPTION)),CHR(13),'')"
, JOURNAL_SOURCE "REPLACE(RTRIM(LTRIM(:JOURNAL_SOURCE)),CHR(13),'')"
, JOURNAL_CATEGORY "REPLACE(RTRIM(LTRIM(:JOURNAL_CATEGORY)),CHR(13),'')"
, GL_DATE "REPLACE(RTRIM(LTRIM(:GL_DATE)),CHR(13),'')"
, CURRENCY_CODE "REPLACE(RTRIM(LTRIM(:CURRENCY_CODE)),CHR(13),'')"
, ENTERED_AMOUNT_DR "REPLACE(RTRIM(LTRIM(:ENTERED_AMOUNT_DR)),CHR(13),'')"
, ENTERED_AMOUNT_CR "REPLACE(RTRIM(LTRIM(:ENTERED_AMOUNT_CR)),CHR(13),'')"
, CONVERSION_DATE "REPLACE(RTRIM(LTRIM(:CONVERSION_DATE)),CHR(13),'')"
, CONVERSION_TYPE "REPLACE(RTRIM(LTRIM(:CONVERSION_TYPE)),CHR(13),'')"
, CONVERSION_RATE "REPLACE(RTRIM(LTRIM(:CONVERSION_RATE)),CHR(13),'')"
, ACCOUNTED_AMOUNT_DR "REPLACE(RTRIM(LTRIM(:ACCOUNTED_AMOUNT_DR)),CHR(13),'')"
, ACCOUNTED_AMOUNT_CR "REPLACE(RTRIM(LTRIM(:ACCOUNTED_AMOUNT_CR)),CHR(13),'')"
, LINE_DESCRIPTION "REPLACE(RTRIM(LTRIM(:LINE_DESCRIPTION)),CHR(13),'')"
, BRANCH "REPLACE(RTRIM(LTRIM(:BRANCH)),CHR(13),'')"
, NATURAL_ACCOUNT "REPLACE(RTRIM(LTRIM(:NATURAL_ACCOUNT)),CHR(13),'')"
, COST_CENTER "REPLACE(RTRIM(LTRIM(:COST_CENTER)),CHR(13),'')"
, LOB "REPLACE(RTRIM(LTRIM(:LOB)),CHR(13),'')"
, PRODUCT "REPLACE(RTRIM(LTRIM(:PRODUCT)),CHR(13),'')"
, CHANNEL "REPLACE(RTRIM(LTRIM(:CHANNEL)),CHR(13),'')"
, INTER_BRANCH "REPLACE(RTRIM(LTRIM(:INTER_BRANCH)),CHR(13),'')"
, SOURCE_SYSTEM "REPLACE(RTRIM(LTRIM(:SOURCE_SYSTEM)),CHR(13),'')"
, FUTURE1 "REPLACE(RTRIM(LTRIM(:FUTURE1)),CHR(13),'')"
, FUTURE2 "REPLACE(RTRIM(LTRIM(:FUTURE2)),CHR(13),'')"
, FUTURE3 "REPLACE(RTRIM(LTRIM(:FUTURE3)),CHR(13),'')"
, ATTRIBUTE1 "REPLACE(RTRIM(LTRIM(:ATTRIBUTE1)),CHR(13),'')"
, ATTRIBUTE2 "REPLACE(RTRIM(LTRIM(:ATTRIBUTE2)),CHR(13),'')"
)
SQL*Loader Concurrent Program Oracle Apps R12
Oracle R12 Register SQL*Loader as a Concurrent Program
Step 1:
Create the SQL*Loader Control and Data file and place them in Server(ex: $CUSTOM_TOP/bin). Create or check the interface table structures in the backend.
Step 2:
Go to Application Developer => Concurrent => Executables. Define a Concurrent Program Executable.
Choose the Execution Method as SQL*Loader and give the Execution File Name as the name of the SQL*Loader control file.
Step 3:
Go to Application Developer > Concurrent > Program. Define the Concurrent Program. Attach the executable defined above.
Step 4:
Go to parameters of the concurrent program. Create a parameter to take the server path of the data file. You can also place the default value.
Step 5:
Added the Concurrent Program to a Custom Responsibility through a Request Group.
Step 6:
Go to that Custom Responsibility and Run the Concurrent Program. If successful check the output file that have all data uploading information.
Step 7:
Check in the backend whether the tables got updated or not.
The Bad and Discard files will be created in /conc/out file of the server or you set in control file .
Note:
Bad file: The bad file contains rows that were rejected because of errors.
Discard file: The discard file contains rows that were discarded because they were filtered out control file.
SQL Loader Shell Script example
#SQL Loader Shell Script
Open Linux (Putty)-> login application user - > run below command
After run command successfully JG_ITEM_MASTER_DATA.csv data upload in Oracle database table.
Subscribe to:
Posts (Atom)