Tag: PL/SQL

Again, a simple piece of code:

This also runs Active User concurrent program:

declare
  v_request_id number := -1;
begin
  --fnd_global.apps_initialize(user_id, resp_id, appl_resp_id)
  fnd_global.apps_initialize(0, 20420, 1); --So sysadmin using responsibility System Administrator in application System Administration
  --v_request_id : fnd_request.submit_request(application short name,concurrent program short name,argument1 =>,  argument2 =>))
  v_request_id := fnd_request.submit_request(application=>'FND',program=>'FNDSCURS');
  commit;
  if v_request_id > 0
  then
    dbms_output.put_line('Successfully submitted: '||v_request_id);
  else
    dbms_output.put_line('Not Submitted');
  end if;
end;
/

You can work out the application ids etc using these pieces of SQL:

select * from fnd_application_tl
where application_id = 1;

select * from fnd_user
where user_name = 'SYSADMIN';

select * from fnd_responsibility_tl
where responsibility_name = 'System Administrator';

Easiest place to check the program short name etc is in eBusiness Suite itself.

Once you get this working, you can increase the complexity. For example I took the above code and added calls to get users id:

declare
  procedure fSubmitJob ( vSourceName varchar2, vTargetName varchar2)
  is
    v_request_id number := -1;
    v_source_id  number :=null;
    v_target_id  number :=null;
  begin
    select user_id
    into  v_source_id 
    from fnd_user
    where user_name = vSourceName; 
    select user_id
    into  v_target_id
    from fnd_user
    where user_name = vTargetName; 
    if ( v_source_id is not null and v_target_id is not null )
    then
      v_request_id := fnd_request.submit_request(application=>'<our app>',program=>'<our prog>',argument1 => v_target_id,  argument2 =>v_source_id);
      commit;
      if v_request_id > 0
      then
        dbms_output.put_line('Successfully submitted'); -–Removed rest of message
      else
        dbms_output.put_line('Failed submit'); -–Removed rest of message
      end if;
    else
      dbms_output.put_line('Missing ID'); -–Removed rest of message
    end if;
  end fSubmitJob;
begin
  fnd_global.apps_initialize(0, 20420, 1);
  fSubmitJob('<user>','<another user>');
  fSubmitJob('<user>','<another user>');
end;
/

In this it was easier to code a function to find user ids and run a job each time, than do the equivalent code in shell script.