The “PRC: Update Project Summary Amounts for a Range of Projects” (PAXACMPT) in Oracle Applications Projects module can take a significant amount of time.  If there are many projects, running it once for all projects may not complete on time.
Running this in batches as new projects are added means perpetual maintenance.  This program simplifies this task by
running PAXACMPT in parallel with a load-balanced number of projects in each batch dynamically determined based on your
selection of the two parameters.  There are two load-balancing options: Specify the ‘number of processes’ or specify the
‘number in each process.’  The parameters are as follows:

? Number or Processes -- Character (numbers only or NULL) Not
Required

? Number in Processes – Character (numbers only or NULL) Not
Required

? Wait for Completion – Character (‘N’ for ‘No’ – Default, ‘Y’
for ‘Yes’)

Number or Processes (Null or Number) is the number or individual submissions.  The total number of projects are divided in equal ranges (except for the last range) and submitted as Concurrent Requests.  This parameter has precedence over the next parameter if both are specified.

Number in Processes (Null or Number) is the number of projects to submit to each Concurrent Request.  All the projects are
submitted with this number of projects in each submission until the remainder is less than this number in which case the
remainder is submitted in the last submission.  This parameter is ignored if the previous parameter is specified.

BOTH PARAMETERS CANNOT BE NULL.

Wait for Completion (‘N’ for No (Default) ‘Y’ for Yes) tells this Concurrent Request to Wait until all submissions are
completed.  This takes up one Concurrent Queue, but it also provides information about any failure in the log if this is set
to ‘Y’.

This program requires that all parameters from PAXACMPT except for the first two parameters (“From Project Number” and “To Project Number”).  Define all the other (currently eight parameters) as they are defined in PAXACMPT or customize the
code to default appropriate values for your installation.

Create the appropriate Value Sets to use and register as a Concurrent Program.

PLEASE TEST THIS OUT ON A TEST SYSTEM SINCE NO WARRANTY IS IMPLIED OR UNDERSTOOD.

Provide the name of the procedure at the prompt and also sleep_interval and total_time for
FND_CONCURRENT.WAIT_FOR_REQUEST.

/*Version: 1.0
   Purpose: This Concurrent PL/SQL Program submits
      PRC: Update Project Summary Amounts for a Range of Projects (PAXACMPT)
      for a number of projects at a time.  This lets the PAXACMPT to run in parallel.
      This takes less time to complete than running once for all projects.
      Parameters:
      IN  Number of Processes (Null or Number)
            is the number of submissions.  The total
            number of projects are divided in equal ranges
            (except for the last range) and  submitted as Concurrent Requests.
            This parameter has precedence over the next parameter if both are
            specified.
          Number in Processes (Null or Number)
            is the number of projects to submit to each Concurrent Request.
            All the projects are submitted with this number of projects
            in each submission until the remainder are submitted
            in the last submission.
            This parameter is ignored if the previous parameter is specified.
      BOTH PARAMETERS CANNOT BE NULL.
          Wait for Completion ('N' for No (Default) 'Y' for Yes)

      This program also requires all the parameters that are part of PAXACMPT except for the first two
       ("From Project Number" and "To Project Number").
      Please define all the remaining parameters as they are defined in the
      program PAXACMPT.  These are: Mode, Summarize Cost,
      Expenditure Type Class, Summarize Revenue, Summarize Budgets, Budget Type,
      Summarize Commitments, and Debug Mode.

      OUT None
*/

-- CREATE OR REPLACE PROCEDURE NREL_PA_UPDATE_PROJ_SUMM
CREATE OR REPLACE PROCEDURE &procedure_name
( errbuf                 OUT VARCHAR2,
  retcode                OUT NUMBER,
  num_of_procs           IN NUMBER,
  num_in_procs           IN NUMBER,
  wait_for_completion    IN VARCHAR2 DEFAULT 'N',
  mode_for_accumulation  IN VARCHAR2 DEFAULT NULL,
  summarize_cost         IN VARCHAR2 DEFAULT 'Y',
  expenditure_type_class IN VARCHAR2 DEFAULT NULL,
  summarize_revenue      IN VARCHAR2 DEFAULT 'Y',
  summarize_budgets      IN VARCHAR2 DEFAULT 'Y',
  budget_type            IN VARCHAR2 DEFAULT NULL,
  summarize_commitments  IN VARCHAR2 DEFAULT 'Y',
  debug_mode             IN VARCHAR2 DEFAULT 'N')
IS

CURSOR proj_cur IS
  SELECT segment1
  FROM pa_projects
  ORDER BY segment1;

req_id            NUMBER;
proj_number       pa_projects.segment1%TYPE;
proj_count        NUMBER;
increment         NUMBER;
counter           NUMBER := 0;
nbr_submitted     NUMBER := 0;
first_parm        pa_projects.segment1%TYPE;
last_parm         pa_projects.segment1%TYPE;

TYPE t_request_ids IS TABLE OF NUMBER NOT NULL INDEX BY BINARY_INTEGER;
array_of_request_ids  t_request_ids;

all_done            BOOLEAN;
returned_phase      VARCHAR2(30);
returned_status     VARCHAR2(30);
returned_dev_phase  VARCHAR2(30);
returned_dev_status VARCHAR2(30);
returned_message    VARCHAR2(241);

get_problem             BOOLEAN;
get_returned_phase      VARCHAR2(30);
get_returned_status     VARCHAR2(30);
get_returned_dev_phase  VARCHAR2(30);
get_returned_dev_status VARCHAR2(30);
get_returned_message    VARCHAR2(241);

-- Local Error Buffer
errbuff           VARCHAR2(241) DEFAULT NULL;

both_param_null     EXCEPTION;
completion_abnormal EXCEPTION;
run_abnormal        EXCEPTION;

BEGIN
  errbuf := '';
  retcode := 0;

IF num_of_procs IS NULL
AND num_in_procs IS NULL
  THEN RAISE both_param_null;
END IF;

SELECT count(*)
INTO proj_count
FROM pa_projects;

IF num_of_procs IS NOT NULL
THEN
  increment := CEIL(proj_count/(num_of_procs));
ELSE
  increment := CEIL(num_in_procs);
END IF;

OPEN proj_cur;

LOOP
 FETCH proj_cur
 INTO proj_number;
 EXIT WHEN proj_cur%NOTFOUND;

counter := counter + 1;

IF counter = 1
THEN
  first_parm := proj_number;
ELSE
  IF counter = increment
  THEN
    last_parm := proj_number;

    counter := 0;

    req_id := FND_REQUEST.SUBMIT_REQUEST('PA','PAXACMPT','','',FALSE,
              first_parm,last_parm,mode_for_accumulation,
              summarize_cost,expenditure_type_class,
              summarize_revenue,summarize_budgets,budget_type,
              summarize_commitments,debug_mode,
              chr(0),'','','','','','','','','',
              '','','','','','','','','','',
              '','','','','','','','','','',
              '','','','','','','','','','',
              '','','','','','','','','','',
              '','','','','','','','','','',
              '','','','','','','','','','',
              '','','','','','','','','','',
              '','','','','','','','','','');
 

nbr_submitted := nbr_submitted + 1;

IF req_id = 0
THEN
  RAISE invalid_number;
END IF;

array_of_request_ids(req_id) := req_id;

-- Initialize the parms
    first_parm := NULL;
    last_parm := NULL;
  END IF;
END IF;

END LOOP;

IF first_parm IS NOT NULL
AND last_parm IS NULL
THEN
   req_id := FND_REQUEST.SUBMIT_REQUEST('PA','PAXACMPT','','',FALSE,
             first_parm,last_parm,mode_for_accumulation,
             summarize_cost,expenditure_type_class,
             summarize_revenue,summarize_budgets,budget_type,
             summarize_commitments,debug_mode,
             chr(0),'','','','','','','','','',
             '','','','','','','','','','',
             '','','','','','','','','','',
             '','','','','','','','','','',
             '','','','','','','','','','',
             '','','','','','','','','','',
             '','','','','','','','','','',
             '','','','','','','','','','',
             '','','','','','','','','','');

    first_parm := NULL;
    last_parm := NULL;

    nbr_submitted := nbr_submitted + 1;

  END IF;

IF req_id = 0
THEN
  RAISE invalid_number;
END IF;

array_of_request_ids(req_id) := req_id;

CLOSE proj_cur;
COMMIT;

errbuff := 'All Concurrent Programs Submitted Successfully.  ';
errbuff := errbuff||'Number Submitted: '||to_char(nbr_submitted);
errbuf := errbuff;
retcode := 0;

IF wait_for_completion = 'Y'
THEN

   req_id := NVL(array_of_request_ids.first, 0);
   WHILE req_id <> 0
   LOOP

   all_done := FND_CONCURRENT.WAIT_FOR_REQUEST(req_id,
                                               &sleep_interval,
                                               &total_time,
                                               returned_phase,
                                               returned_status,
                                               returned_dev_phase,
                                               returned_dev_status,
                                               returned_message);

   IF all_done = FALSE
     THEN RAISE run_abnormal;
   END IF;

   IF all_done = TRUE
     THEN
     get_problem := FND_CONCURRENT.GET_REQUEST_STATUS(req_id,
                                                      NULL,
                                                      NULL,
                                                      get_returned_phase,
                                                      get_returned_status,
                                                      get_returned_dev_phase,
                                                      get_returned_dev_status,
                                                      get_returned_message);
   END IF;

   IF get_returned_dev_phase != 'COMPLETED'
   AND get_returned_dev_status != 'NORMAL'
     THEN RAISE completion_abnormal;
   END IF;

   req_id := NVL(array_of_request_ids.next(req_id), 0);

   END LOOP;
END IF;

EXCEPTION

WHEN invalid_number
THEN
errbuff := 'Invalid Project Number Encountered.  ';
errbuff := errbuff||'Pertinent Info: '||first_parm||'-'||last_parm;
errbuff := errbuff||'  Number Submitted: '||to_char(nbr_submitted);
errbuf  := errbuff;
retcode := 2;

WHEN both_param_null
THEN
errbuff := 'Both Parameters are NULL  ';
errbuff := errbuff||'Must specify Number of Processes or Number in Processes. ';
errbuff := errbuff||'Number of Processes has precedence over ';
errbuff := errbuff||'Number in Processes';
errbuf := errbuff;
retcode := 2;

WHEN run_abnormal
THEN
errbuff := 'The submitted request(s) did not end successfully...  ';
errbuff := errbuff||'Returned Phase: '||returned_phase;
errbuff := errbuff||'  Returned Status: '||returned_status;
errbuff := errbuff||'  Request ID: '||to_char(req_id);
errbuff := errbuff||'  Message: '||returned_message;
errbuf := errbuff;
retcode := 2;

WHEN completion_abnormal
THEN
errbuff := 'The submitted request(s) completed with error...  ';
errbuff := errbuff||'  Returned Phase: '||get_returned_phase;
errbuff := errbuff||'  Returned Status: '||get_returned_status;
errbuff := errbuff||'  Req. ID: '||to_char(req_id);
errbuff := errbuff||'  Message: '||get_returned_message;
errbuf := errbuff;
retcode := 2;

WHEN others
THEN
errbuff := 'Following Error Encountered -- ';
errbuff := errbuff||'Oracle Error: '||to_char(SQLCODE);
errbuff := errbuff||' Oracle Message: '||SQLERRM;
errbuf := errbuff;
retcode := 2;

END;
/

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips