1 PACKAGE BODY PAY_FLOW_REQUESTS_PKG AS
2 /* $Header: pyprflow.pkb 120.1.12020000.1 2013/03/22 08:07:40 nvankadh noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ******************************************************************
22
23 ======================================================================
24
25
26 Change List
27 ===========
28
29 Version Date Author ER/CR No. Description of Change
30 -------+---------+----------+---------+-------------------------------
31 115.0 03/03/08 ckesanap 6820127 Created the file.
32 115.1 03/03/08 ckesanap 6820127 Changed the dbdrv phase to plb
33 ----------------------------------------------------------------------
34 */
35
36 -- ----------------------------------------------------------------------------
37 -- |-------------------------< create_request >--------------------------------|
38 -- ----------------------------------------------------------------------------
39 --
40 -- Description:
41 -- This procedure creates a request in the data model - pay_process_requests table
42 -- to be picked up for processing
43 --
44 -- ----------------------------------------------------------------------------
45 procedure create_request ( p_process_flow_id in number,
46 p_no_of_params in number,
47 p_asg_action in number,
48 p_asg_set_var_id in number,
49 p_param_details in out NOCOPY PARAM_DETAIL_ARRAY,
50 p_process_request_id OUT NOCOPY number
51 ) IS
52
53 -- Variable Declaration
54
55 l_curr_request_id PAY_PROCESS_REQUESTS.process_req_seq_id%TYPE;
56 TYPE type_process_request_ids
57 is table of PAY_PROCESS_REQUESTS.process_req_seq_id%TYPE
58 index by BINARY_INTEGER;
59 p_process_request_ids type_process_request_ids;
60 l_request_details REQUEST_DETAIL_ARRAY;
61 l_flow_set_id pay_process_flows.set_id%TYPE;
62 l_var_value varchar2(255);
63
64
65 -- Cursor for identifying the sets associated with a flow's individual processes
66 CURSOR csr_process_sets(p_process_flow_id pay_process_flows.process_flow_id%TYPE)
67 is
68 select set_id, process_flow_set_id
69 from PAY_PROCESS_FLOW_SETS
70 where process_flow_id = p_process_flow_id;
71 ---
72
73 -- Cursor for identifying parameters in a set
74 CURSOR csr_set_parameters(p_csr_set_id PAY_PROCESS_FLOW_SETS.set_id%TYPE)
75 is
76 select * from pay_para_set_variables
77 where set_id = p_csr_set_id;
78 ---
79
80 -- Cursor for identifying shared variables with flow parameters
81 CURSOR csr_override_set_vars (p_csr_source_set_var_id pay_para_set_override_vars.source_set_var_id%TYPE)
82 is
83 select destin_set_var_id, source_set_var_id
84 from pay_para_set_override_vars
85 where source_set_var_id = p_csr_source_set_var_id;
86 ---
87 l_proc varchar2(72) := 'PAY_FLOW_REQUESTS_PKG.CREATE_REQUEST';
88 ---
89
90 BEGIN
91
92 hr_utility.set_location('Entering: '||l_proc, 5);
93
94 IF(hr_utility.debug_enabled) then
95
96 -- Debug Section ----------
97 hr_utility.trace('Process Flow ID = '||p_process_flow_id);
98 hr_utility.trace('No of Records = '||p_no_of_params);
99 hr_utility.trace('Asg Action = '||p_asg_action);
100 hr_utility.trace('ParameterName ParameterId SetVariableId SetVariableValue SetVariableSequence DefaultType');
101
102 select set_id into l_flow_set_id
103 from pay_process_flows
104 where process_flow_id = p_process_flow_id;
105
106 FOR table_row in 1 .. p_no_of_params
107 LOOP
108 hr_utility.trace(nvl(to_char(p_param_details(table_row).param_name),'null')||' '||
109 nvl(to_char(p_param_details(table_row).param_id),'null')||' '||
110 nvl(to_char(p_param_details(table_row).set_var_id),'null')||' '||
111 nvl(to_char(p_param_details(table_row).set_var_seq),'null')||' '||
112 nvl(to_char(p_param_details(table_row).set_var_val),'null')||' '||
113 nvl(to_char(p_param_details(table_row).default_type),'null'));
114 END LOOP;
115
116 END if;
117
118 -- End of Debug Section --
119
120 hr_utility.set_location(l_proc, 10);
121
122 select pay_void_process_request_s1.NEXTVAL
123 into l_curr_request_id from dual;
124
125 -- Create a process request
126 insert into PAY_PROCESS_REQUESTS ( process_req_seq_id,
127 process_request_id,
128 fnd_request_id,
129 parent_fnd_request_id,
130 assignment_action_id,
131 process_flow_id,
132 last_update_date,
133 last_updated_by,
134 last_update_login,
135 created_by,
136 creation_date,
137 status,
138 rollback_mode,
139 sync_mode,
140 validation_level ) values
141 (
142 l_curr_request_id,
143 pay_void_process_request_s2.NEXTVAL,
144 null,
145 null,
146 p_asg_action,
147 p_process_flow_id,
148 null,
149 null,
150 null,
151 fnd_global.user_id,
152 sysdate,
153 'U',
154 'N',
155 'G',
156 'F' );
157
158
159 hr_utility.set_location(l_proc, 20);
160
161 -- Find out the sets for the particular process (with process id)
162 FOR l_process_set in csr_process_sets(p_process_flow_id)
163 LOOP
164 -- Create a record in pay_para_set_var_values for each parameter in the individual sets
165 FOR l_set_param in csr_set_parameters(l_process_set.set_id)
166 LOOP
167
168 insert into pay_para_set_var_values (
169 set_variable_value_id,
170 process_req_seq_id,
171 set_variable_id,
172 set_variable_value,
173 last_update_date,
174 last_updated_by,
175 last_update_login,
176 created_by,
177 creation_date ) values
178 (
179 pay_void_set_var_val_id_s.NEXTVAL,
180 l_curr_request_id,
181 l_set_param.set_variable_id,
182 null,
183 null,
184 null,
185 null,
186 fnd_global.user_id,
187 sysdate );
188
189 END LOOP;
190 END LOOP;
191
192 hr_utility.set_location(l_proc, 30);
193
194 -- for each set_var_id in the param_array
195 -- for each record in pay_para_set_override_vars for this set_var_id(i)
196 -- update pay_para_set_var_values with the value in set_var_val(i)
197 -- if the default type is 'INCREMENT_VALUE', then
198 -- increment the var value (gets passed back as out variable)
199 -- end loop
200 -- end loop
201 -- Find out the individual process associated with that set
202
203 FOR no_of_row in 1 .. p_no_of_params
204 LOOP
205
206 FOR source_set_var in csr_override_set_vars(p_param_details(no_of_row).set_var_id)
207 LOOP
208
209 update pay_para_set_var_values
210 set set_variable_value = p_param_details(no_of_row).set_var_val,
211 last_update_date = sysdate,
212 last_updated_by = fnd_global.user_id,
213 last_update_login = fnd_global.login_id
214 where set_variable_id = source_set_var.destin_set_var_id
215 and process_req_seq_id = l_curr_request_id;
216
217 select set_variable_value into l_var_value
218 from pay_para_set_var_values
219 where set_Variable_id = source_set_var.destin_set_var_id
220 and process_req_seq_id = l_curr_request_id;
221
222 if(p_param_details(no_of_row).default_type = 'INCREMENT_VALUE') THEN
223 --
224 p_param_details(no_of_row).set_var_val := p_param_details(no_of_row).set_var_val + 1;
225 --
226 END IF;
227
228 END LOOP;
229
230 END LOOP;
231
232 hr_utility.set_location(l_proc, 40);
233
234 -- Update the pay_para_set_var_values with Assignment_action_id value
235
236 FOR source_set_var in csr_override_set_vars(p_asg_set_var_id)
237 LOOP
238
239 update pay_para_set_var_values
240 set set_variable_value = p_asg_action,
241 last_update_date = sysdate,
242 last_updated_by = fnd_global.user_id,
243 last_update_login = fnd_global.login_id
244 where set_variable_id = source_set_var.destin_set_var_id
245 and process_req_seq_id = l_curr_request_id;
246
247 END LOOP;
248
249 p_process_request_id := l_curr_request_id;
250
251 COMMIT;
252 --
253 hr_utility.set_location('Leaving: '||l_proc, 50);
254 --
255 END create_request;
256
257 -- ----------------------------------------------------------------------------
258 -- |------------------------------< invoke_request>----------------------------|
259 -- ----------------------------------------------------------------------------
260 --
261 -- Description:
262 -- This procedure invokes a flow for all the requests in
263 -- The individual processes in the flow are invoked sequentially
264 -- THIS SHOULD BE A CONCURRENT REQUEST which can handle many assignments
265 -- parallely.
266 --
267 -- ----------------------------------------------------------------------------
268 procedure invoke_request (errbuf OUT NOCOPY VARCHAR2,
269 retcode OUT NOCOPY NUMBER,
270 p_request_id in number) IS
271 p_dummy_num number;
272 l_process_id number;
273 l_wrapper_pkg pay_parameter_sets.wrapper_pkg%TYPE;
274 l_process_request_row PAY_PROCESS_REQUESTS%ROWTYPE;
275
276 l_sql_stmt varchar2(200);
277 l_request_id number;
278 l_group_id number;
279 l_proc_req_seq_id number;
280 l_process_status varchar2(1);
281
282 -- Cursor for getting the set_if for the requests in a flow.
283
284 CURSOR get_set_ids(p_process_flow_id pay_process_flows.process_flow_id%TYPE) is
285 select set_id
286 from PAY_PROCESS_FLOW_SETS
287 where process_flow_id = p_process_flow_id
288 order by process_flow_seq;
289
290 -- Cursor for getting the variable values for a particular request.
291
292 CURSOR get_process_params(p_set_id pay_para_set_variables.set_id%TYPE) is
293 select set_variable_value, set_variable_value_id
294 from pay_para_set_var_values ppsvv,
295 pay_para_set_variables ppsv
296 where ppsvv.set_variable_id = ppsv.set_variable_id
297 and ppsv.set_id = p_set_id
298 and ppsvv.process_req_seq_id = p_request_id
299 order by ppsv.set_var_sequence;
300
301 -- Cursor for getting the process_req_seq_id, primary key of the pay_process_requests table.
302
303 CURSOR get_proc_req_seq_id(p_fnd_request_id pay_process_requests.fnd_request_id%TYPE) is
304 select process_req_seq_id
305 from pay_process_requests
306 where fnd_request_id = p_fnd_request_id;
307
308 -- Cursor for getting the assignment action id for a process request
309
310 CURSOR get_asg_action_id(p_process_req_seq_id pay_process_requests.process_req_seq_id%TYPE) is
311 select ppr.assignment_action_id
312 from pay_process_requests ppr
313 where ppr.process_req_seq_id = p_process_req_seq_id;
314
315 -- Cursor for retrieving the details of the flow request in PAY_PROCESS_REQUESTS
316
320 where process_req_seq_id = p_process_req_seq_id;
317 CURSOR get_flow_req_details(p_process_req_seq_id pay_process_requests.process_req_seq_id%TYPE) is
318 select *
319 from PAY_PROCESS_REQUESTS
321 --
322 l_proc varchar2(72) := 'PAY_FLOW_REQUESTS_PKG.INVOKE_REQUEST';
323 --
324 l_wait_outcome BOOLEAN;
325 l_phase VARCHAR2(80);
326 l_status VARCHAR2(80);
327 l_dev_phase VARCHAR2(80);
328 l_dev_status VARCHAR2(80);
329 l_message VARCHAR2(80);
330 l_assignment_action_id number;
331 --
332 BEGIN
333
334 hr_utility.set_location('Entering: '||l_proc, 5);
335
336 -- With the request_id get the process_id from PAY_PROCESS_REQUESTS
337
338 BEGIN
339 --
340 OPEN get_flow_req_details(p_request_id);
341 FETCH get_flow_req_details INTO l_process_request_row;
342 CLOSE get_flow_req_details;
343 --
344 exception
345 when no_data_found
346 then fnd_file.put_line(FND_FILE.LOG,'Requested Id does not exist');
347 raise;
348 END;
349
350 hr_utility.set_location(l_proc, 10);
351
352 -- selecting the process flow id
353 BEGIN
354 --
355 select process_flow_id into l_process_id
356 from PAY_PROCESS_REQUESTS
357 where process_req_seq_id = p_request_id;
358 --
359 exception
360 when no_data_found then
361 fnd_file.put_line(FND_FILE.LOG,'Specified process flow not defined');
362 raise;
363 END;
364
365 hr_utility.set_location(l_proc, 20);
366
367 -- For each set_id
368 FOR l_set_id in get_set_ids(l_process_id)
369 LOOP
370 -- Get the wrapper package name from pay_parameter_sets
371 BEGIN
372 --
373 select wrapper_pkg into l_wrapper_pkg
374 from pay_parameter_sets
375 where set_id = l_set_id.set_id;
376 --
377 exception
378 when no_data_found then
379 fnd_file.put_line(FND_FILE.LOG,'No wrapper package found');
380 raise;
381 END;
382
383 hr_utility.set_location(l_proc, 30);
384
385 -- Get the group id
386 BEGIN
387 --
388 select distinct group_id into l_group_id
389 from pay_para_set_variables
390 where set_id = l_set_id.set_id;
391 --
392 exception
393 when no_data_found then
394 fnd_file.put_line(FND_FILE.LOG,'No group id specified');
395 raise;
396 END;
397
398 hr_utility.set_location(l_proc, 40);
399
400 l_sql_stmt := 'begin ';
401 l_sql_stmt := l_sql_stmt||l_wrapper_pkg||'(';
402
403 -- Get the values of params order by sequence from
404 -- pay_para_set_variable with set_id
405
406 FOR l_set_variable_value in get_process_params(l_set_id.set_id)
407 LOOP
408 --
409 l_sql_stmt := l_sql_stmt || '''' ||to_char(l_set_variable_value.set_variable_value) ||'''' ||',';
410 --
411 END LOOP;
412
413 l_sql_stmt := l_sql_stmt || l_group_id||',';
414 l_sql_stmt := l_sql_stmt || ':1';
415 l_sql_stmt := l_sql_stmt || ');';
416 l_sql_stmt := l_sql_stmt || ' end;';
417
418 hr_utility.set_location(l_proc, 50);
419
420 BEGIN
421 --
422 execute immediate l_sql_stmt using OUT l_request_id;
423 if l_request_id = -1 then
424 fnd_file.put_line(FND_FILE.LOG,'Invalid data submitted for processing');
425 raise no_data_found;
426 end if;
427 exception
428 when others
429 then fnd_file.put_line(FND_FILE.LOG,'Couldnt invoke the wrapper package succesfully');
430 raise;
431 END;
432
433 fnd_file.put_line(FND_FILE.LOG, l_request_id||' => ' ||l_sql_stmt);
434
435 hr_utility.set_location(l_proc, 60);
436
437 -- Get the assignment action id to be passed to insert_child_request
438 open get_asg_action_id(p_request_id);
439 fetch get_asg_action_id into l_assignment_action_id;
440 close get_asg_action_id;
441
442 insert_child_request(l_assignment_action_id,
443 l_request_id);
444
445 -- Update the process_req_seq_id column in Pay_para_set_var_values
446
447 open get_proc_req_seq_id(l_request_id);
448 fetch get_proc_req_seq_id into l_proc_req_seq_id;
449 close get_proc_req_seq_id;
450
451 FOR l_set_variable_value in get_process_params(l_set_id.set_id)
452 LOOP
453 --
454 UPDATE PAY_PARA_SET_VAR_VALUES
455 SET PROCESS_REQ_SEQ_ID = l_proc_req_seq_id,
456 last_update_date = sysdate,
457 last_updated_by = fnd_global.user_id,
458 last_update_login = fnd_global.login_id
459 WHERE SET_VARIABLE_VALUE_ID = l_set_variable_value.set_variable_value_id;
460 --
461 END LOOP;
462
463 COMMIT;
464
465 hr_utility.set_location(l_proc, 70);
466
467 l_wait_outcome := fnd_concurrent.wait_for_request
468 (
469 request_id => l_request_id,
470 interval => 5,
471 phase => l_phase,
472 status => l_status,
473 dev_phase => l_dev_phase,
474 dev_status => l_dev_status,
475 message => l_message
476 );
477
478 hr_utility.set_location(l_proc, 80);
479
480 BEGIN
481 --
482 select status_code into l_process_status
483 from fnd_concurrent_requests
487 if l_process_status = 'E' then
484 where request_id = l_request_id;
485
486 -- If any of the child process has errored, abort the flow
488 raise no_data_found;
489 end if;
490
491 -- If any of the child process has been canceleed, abort the flow
492 if l_process_status in ('D','X') then
493 raise value_error;
494 end if;
495 exception
496 when no_data_found
497 then
498 fnd_file.put_line(FND_FILE.LOG,'One of the child process with id'||
499 l_request_id ||' has errored out');
500 fnd_file.put_line(FND_FILE.LOG,'Aborting the flow !!');
501 raise ;
502 when value_error
503 then
504 fnd_file.put_line(FND_FILE.LOG,'One of the child process with id'||
505 l_request_id ||' has been cancelled');
506 fnd_file.put_line(FND_FILE.LOG,'Aborting the flow !!');
507 raise;
508 END;
509
510 END LOOP;
511 -- For every set loop ends here
512 --
513 hr_utility.set_location('Leaving: '||l_proc, 90);
514 --
515 exception
516 when others
517 then fnd_file.put_line(FND_FILE.LOG,'Process could not complete due to above reason. Aborting.....');
518 raise PROGRAM_ERROR;
519 --
520 END invoke_request;
521
522
523 -- ----------------------------------------------------------------------------
524 -- |-------------------------< update_process_status >------------------------|
525 -- ----------------------------------------------------------------------------
526 --
527 -- Description:
528 -- This function updates the PAY_PROCESS_REQUESTS table with the current status
529 -- and request_id of the concurrent request
530 --
531 -- ----------------------------------------------------------------------------
532 procedure update_process_status (p_process_request_id in number,
533 p_fnd_request_id in number) IS
534 l_proc varchar2(72) := 'PAY_FLOW_REQUESTS_PKG.UPDATE_PROCESS_STATUS';
535
536 BEGIN
537
538 hr_utility.set_location('Entering: '||l_proc, 5);
539
540 update PAY_PROCESS_REQUESTS
541 set fnd_request_id = p_fnd_request_id,
542 last_update_date = sysdate,
543 last_updated_by = fnd_global.user_id,
544 last_update_login = fnd_global.login_id
545 where process_req_seq_id = p_process_request_id;
546
547 hr_utility.set_location('Leaving: '||l_proc, 10);
548
549 END update_process_status;
550
551
552
553
554 -- ----------------------------------------------------------------------------
555 -- |-----------------------------< execute_request >---------------------------|
556 -- ----------------------------------------------------------------------------
557 --
558 -- Description:
559 -- This function invokes the request and then updates the status of the
560 -- request in PAY_PROCESS_REQUESTS
561 --
562 -- ----------------------------------------------------------------------------
563 procedure execute_request(p_request_id in number) IS
564
565 l_fnd_request_id number;
566 l_proc varchar2(72) := 'PAY_FLOW_REQUESTS_PKG.EXECUTE_REQUEST';
567
568 BEGIN
569
570 hr_utility.set_location('Entering: '||l_proc, 5);
571
572 l_fnd_request_id := fnd_request.submit_request(
573 application => 'PAY',
574 program => 'PYINVREQ',
575 description => null,
576 start_time => fnd_date.date_to_canonical(sysdate),
577 sub_request => null,
578 argument1 => p_request_id);
579
580 update_process_status(p_request_id, l_fnd_request_id);
581
582 commit;
583
584 hr_utility.set_location('Leaving: '||l_proc, 10);
585
586 END execute_request;
587
588 -- ----------------------------------------------------------------------------
589 -- |-----------------------------< purge_requests >---------------------------|
590 -- ----------------------------------------------------------------------------
591 --
592 -- Description:
593 -- This function is called from the UI and purges the completed requests and
594 -- associated parameter values from the tables PAY_PROCESS_REQUESTS and
595 -- PAY_PARA_SET_VAR_VALUES.
596 --
597 -- ----------------------------------------------------------------------------
598 procedure purge_requests(p_fnd_request_id in number default null,
599 p_message out NOCOPY varchar2) IS
600 --
601 -- Cursor to get the parent fnd requests which have completed processing
602 CURSOR get_parent_fnd_requests is
603 select ppr.fnd_request_id
604 from PAY_PROCESS_REQUESTS ppr,
605 fnd_concurrent_requests fcr
606 where ppr.fnd_request_id is not null
607 and ppr.PARENT_FND_REQUEST_ID is null
608 and ppr.fnd_request_id = fcr.request_id
609 and fcr.phase_code = 'C'
610 order by fnd_request_id desc ;
611 --
612 -- Cursor to get the child fnd requests which have completed processing
613 -- for a particular parent request.
614 CURSOR get_child_requests(p_fnd_request_id pay_process_requests.fnd_request_id%TYPE) IS
615 select ppr_child.fnd_request_id
616 from PAY_PROCESS_REQUESTS ppr_child,
617 pay_process_requests ppr_parent,
618 fnd_concurrent_requests fcr
619 where ppr_child.parent_fnd_request_id = p_fnd_request_id
620 and ppr_parent.fnd_request_id = ppr_child.parent_fnd_request_id
621 and ppr_parent.fnd_request_id = fcr.request_id
622 and fcr.phase_code = 'C'
623 order by ppr_child.fnd_request_id desc ;
624 --
625 -- Cursor to get the Variable values for the request being deleted
626 CURSOR get_set_var_values(p_fnd_request_id pay_process_requests.fnd_request_id%TYPE) IS
627 select ppsv.set_variable_value_id
628 from pay_para_set_var_values ppsv,
632 --
629 pay_process_requests ppr
630 where ppr.fnd_request_id = p_fnd_request_id
631 and ppsv.process_req_seq_id = ppr.process_req_seq_id ;
633 l_fnd_req_completed boolean;
634 l_request_count number := 0;
635 l_proc varchar2(72) := 'PAY_FLOW_REQUESTS_PKG.PURGE_REQUESTS';
636 --
637 /*
638 This procedure is called when a single parent request id is passed and all the child requests
639 for that request have to be purged. This procedure is called multiple times when no parent
640 request is passed and all completed requests have to be purged.
641 */
642 PROCEDURE purge_single_flow_request(p_fnd_request_id in number,
643 l_fnd_req_completed in out NOCOPY BOOLEAN ) is
644 --
645 l_delete_parent varchar2(2);
646 --
647 begin
648
649 l_delete_parent := 'N';
650
651 -- First get all the child requests
652 for fnd_child_rec in get_child_requests(p_fnd_request_id)
653 loop
654 -- Delete the variable values for the child request first
655 for fnd_set_var_value_rec in get_set_var_values(fnd_child_rec.fnd_request_id)
656 loop
657
658 delete
659 from pay_para_set_var_values
660 where set_variable_value_id = fnd_set_var_value_rec.set_variable_value_id;
661
662 end loop;
663
664 -- Now, delete the child requests
665 delete
666 from pay_process_requests
667 where fnd_request_id = fnd_child_rec.fnd_request_id;
668
669 end loop;
670
671 l_delete_parent := 'Y';
672 -- If child requests have been successfully deleted, delete the parent request.
673
674 if (l_delete_parent = 'Y') then
675
676 -- Delete the variable values for the parent request
677 for fnd_set_var_value_rec in get_set_var_values(p_fnd_request_id)
678 loop
679
680 delete
681 from pay_para_set_var_values
682 where set_variable_value_id = fnd_set_var_value_rec.set_variable_value_id;
683
684 end loop;
685
686 -- Now, delete the parent request
687 delete
688 from pay_process_requests
689 where fnd_request_id = p_fnd_request_id;
690
691 l_fnd_req_completed := true;
692
693 ELSE
694 -- Pass back a message to UI informing that the request has not been purged.
695 hr_utility.set_message(801, 'PAY_33462_REQ_ACTION_INFO');
696 hr_utility.set_message_token(801,'REQUEST_ID',p_fnd_request_id);
697 hr_utility.set_message_token(801,'ACTION','purged');
698 p_message := hr_utility.get_message;
699 hr_utility.clear_message;
700
701 end if;
702 --
703 end purge_single_flow_request;
704 ----
705
706 BEGIN
707 hr_utility.set_location('Entering: '||l_proc, 5);
708
709 l_fnd_req_completed := false;
710
711 if(p_fnd_request_id is not null) THEN
712
713 l_request_count := 1;
714 purge_single_flow_request(p_fnd_request_id,l_fnd_req_completed);
715
716 ELSE
717 -- fnd_request_id passed is null. Purge all completed requests.
718 -- Get the parent requests which are in completed state and call
719 -- purge_single_flow_request for each of the parent requests.
720
721 for fnd_parent_rec in get_parent_fnd_requests
722 loop
723
724 l_request_count := l_request_count +1 ;
725 purge_single_flow_request(fnd_parent_rec.fnd_request_id,l_fnd_req_completed);
726
727 end loop;
728
729 end if;
730 COMMIT;
731
732 hr_utility.set_location(l_proc, 10);
733
734 if(l_fnd_req_completed) THEN
735
736 if (p_fnd_request_id IS NOT null) THEN
737
738 hr_utility.set_message(801, 'PAY_33461_REQ_ACTION_CONFIRM');
739 hr_utility.set_message_token(801,'REQUEST_ID',p_fnd_request_id);
740 hr_utility.set_message_token(801,'ACTION','purged');
741
742 ELSE
743
744 hr_utility.set_message(801, 'PAY_33463_REQ_ACTION_CONFIRM');
745 hr_utility.set_message_token(801,'NO_OF_REQUESTS',l_request_count);
746 hr_utility.set_message_token(801,'ACTION','purged');
747
748 end if;
749
750 p_message := hr_utility.get_message;
751 hr_utility.clear_message;
752
753 end if;
754
755 hr_utility.set_location('Leaving: '||l_proc, 20);
756 --
757 END purge_requests;
758
759 -- ----------------------------------------------------------------------------
760 -- |-----------------------------< cancel_request >---------------------------|
761 -- ----------------------------------------------------------------------------
762 --
763 -- Description:
764 -- This function is called from the UI and cancels the pending requests.
765 --
766 -- ----------------------------------------------------------------------------
767
768 procedure cancel_request(p_fnd_request_id in number,
769 p_message out NOCOPY varchar2) IS
770 --
771 -- Cursor to get the child request which is spawned first by the
772 -- parent request and is in Pending state.
773 -- Only pending requests should be cancelled. Once the flow processing starts
774 -- and the child request is running, the flow cannot be cancelled
775 CURSOR get_pending_child_request(p_fnd_request_id number) IS
776 select ppr.fnd_request_id
777 from pay_process_requests ppr,
778 fnd_concurrent_requests fcr
779 where ppr.fnd_request_id =
780 ( select min(fnd_request_id)
781 from pay_process_requests
782 where parent_fnd_request_id = p_fnd_request_id
783 )
784 and ppr.fnd_request_id = fcr.request_id
785 and fcr.phase_code = 'P';
786
787 --
788 l_cancelled boolean := false ;
792 --
789 l_proc varchar2(72) := 'PAY_FLOW_REQUESTS_PKG.CANCEL_REQUEST';
790 l_fnd_request_id number;
791 l_message varchar2(255);
793 BEGIN
794 --
795 hr_utility.set_location('Entering: '||l_proc, 5);
796 --
797 -- Get the pending child request and cancel it
798 open get_pending_child_request(p_fnd_request_id);
799 fetch get_pending_child_request into l_fnd_request_id;
800 if get_pending_child_request%FOUND then
801 l_cancelled := fnd_concurrent.cancel_request(l_fnd_request_id,l_message);
802 end if;
803 close get_pending_child_request;
804
805 -- If child request has been cancelled then, cancel the parent request also.
806 if l_cancelled THEN
807
808 l_cancelled := fnd_concurrent.cancel_request(p_fnd_request_id,l_message);
809
810 hr_utility.set_message(801, 'PAY_33461_REQ_ACTION_CONFIRM');
811 hr_utility.set_message_token(801,'REQUEST_ID',p_fnd_request_id);
812 hr_utility.set_message_token(801,'ACTION','cancelled');
813 p_message := hr_utility.get_message;
814 p_message := p_message || ' ' || l_message;
815 hr_utility.clear_message;
816
817 else
818 hr_utility.set_message(801, 'PAY_33462_REQ_ACTION_INFO');
819 hr_utility.set_message_token(801,'REQUEST_ID',p_fnd_request_id);
820 hr_utility.set_message_token(801,'ACTION','cancelled');
821 p_message := hr_utility.get_message;
822 hr_utility.clear_message;
823 end if;
824
825 hr_utility.set_location('Leaving: '||l_proc, 10);
826 --
827 END cancel_request;
828
829 -- ----------------------------------------------------------------------------
830 -- |-------------------------< insert_child_request >--------------------------|
831 -- ----------------------------------------------------------------------------
832 --
833 -- Description:
834 -- This procedure inserts the child requests in the pay_process_requests table.
835 -- This can be called from the wrapper packages of individual processes to
836 -- update the table before doing any other processing post request completion.
837 --
838 -- ----------------------------------------------------------------------------
839 procedure insert_child_request(p_assignment_action_id in number,
840 p_fnd_request_id in number) IS
841 --
842 -- Cursor to get the parent request details from the assignment action id
843 -- If there are more than one record with the same assignment action, get the
844 -- request with the maximum process_request_id.
845 CURSOR get_parent_request IS
846 select ppr_outer.process_request_id,
847 ppr_outer.fnd_request_id,
848 ppr_outer.process_flow_id
849 from pay_process_requests ppr_outer
850 where ppr_outer.process_request_id =
851 ( SELECT max(ppr_inner.process_request_id)
852 FROM pay_process_requests ppr_inner
853 WHERE ppr_inner.assignment_action_id = p_assignment_action_id
854 AND ppr_inner.parent_fnd_request_id IS null
855 );
856
857 -- Cursor to verify if the child request record is already inserted in
858 -- the PAY_PROCESS_REQUESTS table.
859 CURSOR child_request_exists IS
860 select 'Y'
861 from pay_process_requests
862 where fnd_request_id = p_fnd_request_id;
863
864 l_proc_req_seq_id pay_process_requests.process_req_seq_id%TYPE;
865 l_process_request_id pay_process_requests.process_request_id%TYPE;
866 l_parent_fnd_req_id pay_process_requests.fnd_request_id%TYPE;
867 l_process_flow_id pay_process_flows.process_flow_id%TYPE;
868 l_child_req_exists varchar2(5) default 'N';
869 --
870 l_proc varchar2(72) := 'PAY_FLOW_REQUESTS_PKG.INSERT_CHILD_REQUEST';
871 --
872 BEGIN
873
874 -- First check if the child request has already been inserted
875 open child_request_exists;
876 fetch child_request_exists into l_child_req_exists;
877 --
878 if (child_request_exists%FOUND and l_child_req_exists = 'Y') then
879 --
880 close child_request_exists;
881 --
882 else
883 -- Update the PAY_PROCESS_REQUESTS table here
884 --
885 open get_parent_request;
886 fetch get_parent_request into l_process_request_id,
887 l_parent_fnd_req_id,
888 l_process_flow_id;
889 if (get_parent_request%FOUND) then
890 --
891 close get_parent_request;
892
893 insert into PAY_PROCESS_REQUESTS ( process_req_seq_id,
894 process_request_id,
895 fnd_request_id,
896 parent_fnd_request_id,
897 assignment_action_id,
898 process_flow_id,
899 last_update_date,
900 last_updated_by,
901 last_update_login,
902 created_by,
903 creation_date,
904 status,
905 rollback_mode,
906 sync_mode,
907 validation_level ) values
908 (
909 pay_void_process_request_s1.NEXTVAL,
910 l_process_request_id,
911 p_fnd_request_id,
912 l_parent_fnd_req_id,
913 p_assignment_action_id,
914 l_process_flow_id,
915 null,
916 null,
917 null,
918 fnd_global.user_id,
919 sysdate,
920 'U',
921 'N',
922 'G',
923 'F' );
924
925 COMMIT;
929 close get_parent_request;
926 --
927 else
928 --
930 raise no_data_found;
931 --
932 end if;
933 --
934 close child_request_exists;
935 --
936 end if;
937 --
938 hr_utility.set_location('Leaving: '||l_proc, 30);
939
940 exception
941 when others
942 then fnd_file.put_line(FND_FILE.LOG,'Request creation for the child process failed');
943 raise;
944 --
945 END INSERT_CHILD_REQUEST;
946
947 END PAY_FLOW_REQUESTS_PKG;
[Home] [Help]