Wednesday, 13 May 2015

Script to assign a concurrent program to a request group from backend

DECLARE
  l_program_short_name  VARCHAR2 (200);
  l_program_application VARCHAR2 (200);
  l_request_group       VARCHAR2 (200);
  l_group_application   VARCHAR2 (200);
  l_check               VARCHAR2 (2);
BEGIN
  --
  L_PROGRAM_SHORT_NAME := '<CONCURRENT PROGRAM SHORTNAME>';
  L_REQUEST_GROUP := '<REQUEST GROUP NAME>';
  begin
  --Query to find application of request group
    SELECT APPLICATION_NAME
    INTO l_group_application
    FROM FND_APPLICATION_TL FA,
      FND_REQUEST_GROUPS FRG
    WHERE FA.APPLICATION_ID   =FRG.APPLICATION_ID
    and FRG.REQUEST_GROUP_NAME=L_REQUEST_GROUP;
    --Query to find application of concurrent program
    SELECT APPLICATION_NAME
    INTO l_program_application
    FROM FND_CONCURRENT_PROGRAMS FC,
      FND_APPLICATION_TL FA
    WHERE FA.APPLICATION_ID       =FC.APPLICATION_ID
    and FC.CONCURRENT_PROGRAM_NAME= L_PROGRAM_SHORT_NAME ;
 --   query to check if concurrent program already assigned to request group
    SELECT 'Y'
    INTO l_check
    FROM fnd_request_groups frg,
      FND_REQUEST_GROUP_UNITS FRGU,
      fnd_concurrent_programs fcp
    WHERE frg.request_group_id      = frgu.request_group_id
    AND frg.application_id          = frgu.application_id
    AND frgu.request_unit_id        = fcp.concurrent_program_id
    AND frgu.unit_application_id    = fcp.application_id
    AND fcp.concurrent_program_name = L_PROGRAM_SHORT_NAME
    AND FRG.REQUEST_GROUP_NAME      = L_REQUEST_GROUP ;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    l_check:='N';
  WHEN OTHERS THEN
    dbms_output.put_line ('Adding Concurrent Program to Request Group Failed: '|| SQLERRM);
  END;
  IF(l_check <> 'Y') THEN
    apps.fnd_program.add_to_group (program_short_name => l_program_short_name, program_application => l_program_application, request_group => l_request_group, group_application => l_group_application );
    dbms_output.put_line ('Program assigned to request group: '|| l_request_group);
  ELSE
    dbms_output.put_line ('Program already assigned to request group: '|| l_request_group);
  END IF;
  --
  COMMIT;
END;

No comments:

Post a Comment