SQL LOADER USING THAT POPULATES THE REQUEST_ID

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;