Thursday, October 29, 2015

Getting Concurrent Request Id within Sql Loader based Concurrent Programs

None of the following will work in SQL LOADER.
fnd_global.conc_request_id
- fnd_profile.value('conc_request_id')

First will update request_id as -1 and second will update as blank/NULL

You may consider this.. it works for me.

- Create a function, something like below...
===
CREATE OR REPLACE PACKAGE BODY

IS
-- define global variable..
   gn_sqlldr_req_id     NUMBER ;


FUNCTION get_request_id RETURN NUMBER
IS
ln_req_id NUMBER ;
BEGIN


IF gn_sqlldr_req_id IS NULL then

select max(request_id)
INTO gn_sqlldr_req_id
from fnd_concurrent_requests where  concurrent_program_id  in (
select concurrent_program_id from fnd_concurrent_programs where concurrent_program_name =' YOUR_PROG_SHORT_NAME')   ;
END IF ;

RETURN gn_sqlldr_req_id ;

END ;

====
Your SQL Loader control file..
==
load data
INFILE '/tmp/XMITINVD.TXT'
INTO TABLE xxinv_TABLE_stg
APPEND
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS

(
---
---
---
request_id ".get_request_id" -- your function here
--
--
--
)

This worked for me. 

1 comment:

Unknown said...

Nice article,thank you for your valuable information chandra.For more details visit our site..

Oracle Fusion Cloud HCM Training