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;
/
Return to : Oracle
Database, SQL, Application, Programming Tips