KentW Limited

Concurrent Request Sets are great to use when having to submit multiple programs like period-end programs.

However it in large scale projects you may have to submit very large request sets depending on some logic which is virtually impossible to implement as a Concurrent Request Set.

It is easy to submit a program from a PL/SQL program using:

FND_REQUEST.SUBMIT_REQUEST

However how do we keep track of all the submitted sub-programs in similar way as a normal concurrent request set do it – retaining the parent request id?

So what we want to have is:

> Request Set
>> Request Job (parent request id = Request Set)

Also how do we ensure that we do not submit too many sub-programs at any one time in order not to clutter the request queue?

So what we really want to have is:

> Request Set
>> Request Job (parent request id = Request Set)
> Wait for child requests to complete
>> Request Job (parent request id = Request Set)

The problem is that when a parent program wakes up it re-submits itself. So in order to make this work you need to use recursive programming methods.

The trick is to use these three calls:

• FND_CONC_GLOBAL.SET_REQ_GLOBALS with Status=PAUSED and REQUEST_DATA
  Status = PAUSED will put the parent program into a wait state until child processes have completed
  REQUEST_DATA parameter will save data between runs
• FND_CONC_GLOBAL.REQUEST_DATA
  Retrieves data from previous run
• FND_REQUEST.SUBMIT_REQUEST with SUB_REQUEST=>true
  Submits the sub-programs with control of a parent program.
  If set to “false” you have no control of the sub-programs.
  If you set this to “true” but without setting parent program status to PAUSED you will get the issue where child programs are phase=“inactive” and status=“no manager”

So in my test I will create to sets of two jobs:

> Request Set
> Set 1:
>> 1: Request Job (parent request id = Request Set)
>> 2: Request Job (parent request id = Request Set)
> Wait for child requests to complete
> Set 2:
>> 1: Request Job (parent request id = Request Set)
>> 2: Request Job (parent request id = Request Set)

So running my concurrent request set:

Set 1:

So two first programs has just run and parent quest is about to start the second job set.

Set 2:

Note all jobs has the same Parent Request ID.

Nice and controlled.

Create the PL/SQL code:

Package:

create or replace
package XXKWICONCSET authid current_user as
  — this is exposed for conc manager
  procedure XXKWI_REQUEST_SET_CONCUR (
    ERRBUF OUT NOCOPY varchar2,
    RETCODE OUT NOCOPY varchar2
  );
  procedure XXKWI_REQUEST_CONCUR (
    ERRBUF OUT NOCOPY varchar2,
    RETCODE OUT NOCOPY varchar2,
    PARAM IN varchar2
  );
end XXKWICONCSET;
/

Package Body:

create or replace
package body XXKWICONCSET as
  — Concurrent child program
  procedure XXKWI_REQUEST_CONCUR (
    ERRBUF OUT NOCOPY varchar2,
    RETCODE OUT NOCOPY varchar2,
    PARAM IN varchar2
  ) is
  begin
    fnd_file.put_line(fnd_file.output,’Running job: ‘||param);
    ERRBUF:=’Done Job…’;
    RETCODE:=0;
  end XXKWI_REQUEST_CONCUR;
  — Concurrent Request Set
  procedure XXKWI_REQUEST_SET_CONCUR (
    ERRBUF OUT NOCOPY varchar2,
    RETCODE OUT NOCOPY varchar2
  ) is
    L_REQUEST_ID number;
    L_REQ_DATA varchar2(10);
    job_count number;
    set_count number;
  begin
    L_REQ_DATA:=FND_CONC_GLOBAL.REQUEST_DATA; — get previous job data
    if L_REQ_DATA is null then — first run set count to ‘0’
      L_REQ_DATA:=’0′;
    end if;
    set_count:=to_number(l_req_data)+1; — increment for every job
    fnd_file.put_line(fnd_file.output,’Running set: ‘||to_char(set_count));
    if set_count>2 then — stop after 2 jobs
      ERRBUF:=’Done Set…’;
      RETCODE:=0;
      return;
    end if;
    — submit 3 child programs
    for job_count in 1..2 loop
      fnd_file.put_line(fnd_file.output,’Submitting job: ‘||to_char(job_count));
      L_REQUEST_ID:=FND_REQUEST.SUBMIT_REQUEST(
        APPLICATION=>’FND’,
        program=>’XXKWI_REQUEST_CONCUR’,
        DESCRIPTION=>to_char(job_count),
        START_TIME=>null,
        SUB_REQUEST=>true, — True = This is a child program
        ARGUMENT1=>to_char(job_count) — data to job
      );
    end loop;
    — Wait until done and save set count:
    FND_CONC_GLOBAL.SET_REQ_GLOBALS(
      CONC_STATUS=>’PAUSED’, — wait for child request to complete
      REQUEST_DATA=>to_char(set_count) — save set data
    ); 
    Commit;
  end XXKWI_REQUEST_SET_CONCUR;
end XXKWICONCSET;
/

Register the two executables and programs.

Concurrent Job:

Concurrent Set:

Request Group:

Comments are closed.