The PRC: Refresh Project Summary Amounts (PAXACRPT) in Oracle Applications Projects module can be run for all projects or a range of projects.  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 making running PAXACRPT in parallel with a load-balanced number of projects in each batch.

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’.

Create the appropriate Value Sets to use and register as a Concurent 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: 2.0
     Purpose: This Concurrent PL/SQL Program submits
     PRC: Refresh Project Summary Amounts (PAXACRPT) once for a number of projects at a time.
      This lets the PAXACRPT 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)
      OUT None
*/

-- CREATE OR REPLACE PROCEDURE NREL_PA_REFR_SUMM_AMTS
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')
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','PAXACRPT','','',FALSE,
              first_parm,last_parm,'',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','PAXACRPT','','',FALSE,
              first_parm,last_parm,'',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