In SQL LOADER program the following things should be ready.
1. prepare a control file .
2. prepare .csv file .
3. move the files in to the custom directory of the bin folder.
4. register the concurrent program from the EBS
control file
OPTIONS (SKIP = 1)
LOAD DATA
infile '$FILE'
badfile '$FILE'
truncate into table TEMP_INTERFACE_STG
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
STATUS_FLAG constant "N",
RECORD_NUM "RG_FA_TAX_SEQ.NEXTVAL",
ASSET_NUMBER "NVL(:ASSET_NUMBER,Null)",
BOOK_TYPE_CODE "NVL(:BOOK_TYPE_CODE,Null)",
ADJUSTED_RATE "NVL(:ADJUSTED_RATE,Null)",
CREATED_BY FLOAT EXTERNAL "FND_GLOBAL.USER_ID",
CREATION_DATE SYSDATE,
LAST_UPDATED_BY FLOAT EXTERNAL "FND_GLOBAL.USER_ID",
LAST_UPDATE_DATE SYSDATE ,
LAST_UPDATE_LOGIN FLOAT EXTERNAL "FND_GLOBAL.LOGIN_ID",
)
validate the program based on the conditions and move the records from staging table to the interface table .
in this case the staging table is TEMP_INTERFACE_STG
develop a package and perform the validations and call the above registered SQL LOADER program in that package.
SQLloader doesn't populate the request id, File name or other input parameters directly through
control file.
so execute an update statement in the package and register that package in the EBS.
Call SQL*Loader program that load the data from csv file
l_req_id := fnd_request.submit_request(application => 'xxx',
program => 'xx_progam',
description => '',
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 'apps/apps_st/test/custom/bin/file.csv');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Request Id for the sql loader'||l_req_id);
IF l_req_id != 0
THEN
COMMIT;
l_call_status := fnd_concurrent.wait_for_request
( l_req_id,
3, -- interval
0, -- max_wait
l_phase,
l_status,
l_dev_phase,
l_dev_status,
l_message
);
END IF;
--Check Program completion status
IF (l_dev_phase = 'COMPLETE' AND l_dev_status = 'NORMAL')
THEN
fnd_file.put_line( fnd_file.log,'Program Completed Successfully');
UPDATE TEMP_INTERFACE_STG
SET request_id= l_req_id
where creation_date=sysdate;
COMMIT;
ELSE
fnd_file.put_line( fnd_file.log, 'Program did not Completed Successfully: '||l_dev_status);
END IF;
