[Home] [Help]
PACKAGE BODY: APPS.FND_SET
Source
1 PACKAGE BODY fnd_set AS
2 /* $Header: AFRSSETB.pls 120.6 2006/04/11 05:17:10 ktanneru ship $ */
3
4
5 /* START_PUBLIC */
6 bad_parameter EXCEPTION;
7 PRAGMA EXCEPTION_INIT(bad_parameter, -06501); -- program error
8 /* END_PUBLIC */
9
10 who_mode VARCHAR2(20) := 'customer_data'; /* customer_data or seed_data */
11
12
13 /* ------------------------------------------------------------ */
14 /* messaging */
15 /* ------------------------------------------------------------ */
16
17
18 debug_mode_on BOOLEAN := FALSE;
19 internal_messages VARCHAR2(10000);
20
21 /* START_PUBLIC */
22 PROCEDURE debug_on IS
23 BEGIN
24 debug_mode_on := TRUE;
25 END;
26
27 PROCEDURE debug_off IS
28 BEGIN
29 debug_mode_on := FALSE;
30 END;
31 /* END_PUBLIC */
32
33 PROCEDURE message(msg VARCHAR2) IS
34 BEGIN
35 internal_messages := internal_messages || msg || fnd_global.newline;
36 END;
37
38 PROCEDURE message_init IS
39 BEGIN
40 internal_messages := '';
41 END;
42
43 /* START_PUBLIC */
44 FUNCTION message RETURN VARCHAR2 IS
45 BEGIN
46 RETURN internal_messages;
47 END;
48 /* END_PUBLIC */
49
50
51 /* print functions only used in testing */
52
53 PROCEDURE print(msg IN VARCHAR2) IS
54 BEGIN
55 IF(debug_mode_on) THEN
56 --dbms_output.enable;
57 --dbms_output.put(msg);
58 null;
59 END IF;
60 END;
61
62 PROCEDURE println IS
63 BEGIN
64 IF(debug_mode_on) THEN
65 --dbms_output.enable;
66 --dbms_output.put_line('');
67 null;
68 END IF;
69 END;
70
71 PROCEDURE println(msg IN VARCHAR2) IS
72 BEGIN
73 IF(debug_mode_on) THEN
74 --dbms_output.enable;
75 --dbms_output.put_line(msg);
76 null;
77 END IF;
78 END;
79
80
81 /* ------------------------------------------------------------ */
82 /* who information */
83 /* ------------------------------------------------------------ */
84
85 PROCEDURE set_session_mode(session_mode IN VARCHAR2) IS
86 BEGIN
87 IF(session_mode NOT IN ('customer_data', 'seed_data')) THEN
88 message('bad mode:'|| session_mode);
89 message('valid values are: customer_data, seed_data');
90 RAISE bad_parameter;
91 END IF;
92 who_mode := session_mode;
93 END;
94
95
96 FUNCTION customer_mode RETURN BOOLEAN IS
97 BEGIN
98 IF(who_mode = 'customer_data') THEN
99 RETURN TRUE;
100 ELSIF(who_mode = 'seed_data') THEN
101 RETURN FALSE;
102 ELSE
103 message('bad session mode:' || who_mode);
104 message('use set_session_mode to specify');
105 RAISE bad_parameter;
106 END IF;
107 END;
108
109
110 FUNCTION created_by_f RETURN NUMBER IS
111 BEGIN
112 IF(customer_mode) THEN
113 RETURN 0;
114 ELSE
115 RETURN 1;
116 END IF;
117 END;
118
119 FUNCTION creation_date_f RETURN DATE IS
120 BEGIN
121 IF(customer_mode) THEN
122 RETURN Sysdate;
123 ELSE
124 RETURN To_date('01011980', 'MMDDYYYY');
125 END IF;
126 END;
127
128 FUNCTION last_updated_by_f RETURN NUMBER IS
129 BEGIN
130 RETURN created_by_f;
131 END;
132
133 FUNCTION last_update_date_f RETURN DATE IS
134 BEGIN
135 RETURN creation_date_f;
136 END;
137
138 FUNCTION last_update_login_f RETURN NUMBER IS
139 BEGIN
140 return 0;
141 END;
142
143
144
145 /* ------------------------------------------------------------ */
146
147 /* get the application id, given the name (or short name) */
148 FUNCTION application_id_f(application_name_in IN VARCHAR2)
149 RETURN fnd_application.application_id%TYPE
150 IS
151 application_id_ret fnd_application.application_id%TYPE;
152 BEGIN
153 IF(application_name_in IS NULL) THEN
154 message('A null application_name was specified.');
155 RAISE bad_parameter;
156 END IF;
157
158 SELECT application_id
159 INTO application_id_ret
160 FROM fnd_application
161 WHERE application_short_name = application_name_in;
162 RETURN application_id_ret;
163 EXCEPTION
164 when NO_DATA_FOUND then
165 message('Invalid application name: ' || application_name_in);
166 RAISE bad_parameter;
167 END;
168
169
170 /* Get a concurrent program ID given appl ID and short name */
171 FUNCTION concurrent_program_id_f(program_application_id number,
172 program_short_name varchar2) return number is
173 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
174 BEGIN
175 SELECT concurrent_program_id
176 INTO program_id
177 FROM fnd_concurrent_programs
178 WHERE application_id = program_application_id
179 AND concurrent_program_name = program_short_name;
180
181 RETURN program_id;
182
183 EXCEPTION
184 WHEN no_data_found THEN
185 message('Could not find program: '||program_short_name);
186 RAISE bad_parameter;
187 END;
188
189
190 /* Get a request group ID given name and appl ID */
191 FUNCTION request_group_id_f(group_application_id number,
192 request_group varchar2) return number is
193 group_id fnd_request_groups.request_group_id%TYPE;
194 BEGIN
195 SELECT request_group_id
196 INTO group_id
197 FROM fnd_request_groups
198 WHERE application_id = group_application_id
199 AND request_group_name = request_group;
200
201 RETURN group_id;
202
203 EXCEPTION
204 WHEN no_data_found THEN
205 message('Could not find group: '|| request_group);
206 RAISE bad_parameter;
207 END;
208
209
210 /* Get a request set ID given appl ID and set short name */
211 FUNCTION request_set_id_f(set_application_id number,
212 request_set varchar2) return number is
213 set_id fnd_request_sets.request_set_id%TYPE;
214 BEGIN
215 SELECT request_set_id
216 INTO set_id
217 FROM fnd_request_sets
218 WHERE application_id = set_application_id
219 AND request_set_name = request_set;
220
221 RETURN set_id;
222
223 EXCEPTION
224 WHEN no_data_found THEN
225 message('Could not find set: '||request_set);
226 RAISE bad_parameter;
227 END;
228
229
230 /* Get a request set stage ID given appl ID, set ID, and
231 stage number */
232 FUNCTION request_set_stage_id_f(set_appl_id number,
233 set_id varchar2,
234 stage varchar2) return number is
235 stage_id fnd_request_set_stages.request_set_stage_id%TYPE;
236 BEGIN
237 SELECT request_set_stage_id
238 INTO stage_id
239 FROM fnd_request_set_stages
240 WHERE set_application_id = set_appl_id
241 AND request_set_id = set_id
242 AND stage = stage_name;
243
244 RETURN stage_id;
245
246 EXCEPTION
247 WHEN no_data_found THEN
248 message('Could not find stage: '||stage);
249 RAISE bad_parameter;
250 END;
251
252
253 /* Get a request set stage function ID given appl ID, function_name */
254 FUNCTION function_id_f(fn_appl_id number,
255 name varchar2) return number is
256 fn_id fnd_executables.executable_id%TYPE;
257 BEGIN
258 SELECT executable_id
259 INTO fn_id
260 FROM fnd_executables
261 WHERE application_id = fn_appl_id
262 AND executable_name = name;
263
264 RETURN fn_id;
265
266 EXCEPTION
267 WHEN no_data_found THEN
268 message('Could not find function: '||name);
269 RAISE bad_parameter;
270 END;
271
272
273 /* Get a stage function parameter ID given appl ID, function_id, param name */
274 FUNCTION parameter_id_f(fn_appl_id number,
275 fn_id number,
276 name varchar2) return number is
277 param_id fnd_stage_fn_parameters_vl.parameter_id%TYPE;
278 BEGIN
279 SELECT parameter_id
280 INTO param_id
281 FROM fnd_stage_fn_parameters_vl
282 WHERE application_id = fn_appl_id
283 AND function_id = fn_id
284 AND parameter_name = name;
285
286 RETURN param_id;
287
288 EXCEPTION
289 WHEN no_data_found THEN
290 message('Could not find parameter: '||name);
291 RAISE bad_parameter;
292 END;
293
294
295 PROCEDURE check_yesno(val IN VARCHAR2) IS
296 BEGIN
297 if (upper(val) not in ('Y', 'N')) then
298 message('Y/N value expected, but not found.');
299 raise bad_parameter;
300 end if;
301 END;
302
303
304 PROCEDURE check_notnull(val IN VARCHAR2) IS
305 BEGIN
306 IF(val IS NULL) THEN
307 message('A NULL value was specified for a NOT NULL parameter.');
308 RAISE bad_parameter;
309 END IF;
310 END;
311
312 PROCEDURE check_notnull(val IN NUMBER) IS
313 BEGIN
314 IF(val IS NULL) THEN
315 message('A NULL value was specified for a NOT NULL parameter.');
316 RAISE bad_parameter;
317 END IF;
318 END;
319
320 PROCEDURE check_notnull(val IN DATE) IS
321 BEGIN
322 IF(val IS NULL) THEN
323 message('A NULL value was specified for a NOT NULL parameter.');
324 RAISE bad_parameter;
325 END IF;
326 END;
327
328
329
330 -- Function
331 -- CREATE_PROGRAM
332 --
333 -- Purpose
334 -- Create a concurrent program for a Request Set or Stage
335 -- that can have incompatibilities.
336 -- This logic is mostly copied from the form FNDRSSET.fmb.
337 --
338 -- Arguments
339 -- request_set - Name of set
340 -- set_application_id - Appl ID of set
341 -- set_id - ID of set
342 -- stage_id - ID of request set stage
343 -- stage_number - Stage number.
344 -- description - Description of set
345 -- lang - language code
346 --
347 FUNCTION create_program(request_set in varchar2,
348 set_application_id in number,
349 set_id in number DEFAULT NULL,
350 stage_id in number DEFAULT NULL,
351 stage_name in VARCHAR2 DEFAULT NULL,
352 description in varchar2,
353 lang in varchar2,
354 set_short_name in varchar2 DEFAULT NULL,
355 stage_short_name in varchar2 DEFAULT NULL)
356 return number is
357 ra fnd_concurrent_programs.run_alone_flag%TYPE;
358 srs fnd_concurrent_programs.srs_flag%TYPE;
359 enabled fnd_concurrent_programs.enabled_flag%TYPE;
360 qc fnd_concurrent_programs.queue_control_flag%TYPE;
361 qmc fnd_concurrent_programs.queue_method_code%TYPE;
362 amc fnd_concurrent_programs.argument_method_code%TYPE;
363 so fnd_concurrent_programs.save_output_flag%TYPE;
364 print fnd_concurrent_programs.print_flag%TYPE;
365 rs fnd_concurrent_programs.required_style%TYPE;
366 printer fnd_concurrent_programs.printer_name%TYPE;
367 style fnd_concurrent_programs.output_print_style%TYPE;
368 eo fnd_concurrent_programs.execution_options%TYPE;
369 ex_app_id fnd_concurrent_programs.executable_application_id%TYPE;
370 ex_id fnd_concurrent_programs.executable_id%TYPE;
371 cl_app_id fnd_concurrent_programs.class_application_id%TYPE;
372 cl_id fnd_concurrent_programs.concurrent_class_id%TYPE;
373
374 last_update_login fnd_concurrent_programs.last_update_login%TYPE
375 := last_update_login_f;
376 last_update_date fnd_concurrent_programs.last_update_date%TYPE
377 := last_update_date_f;
378 last_updated_by fnd_concurrent_programs.last_updated_by%TYPE
379 := last_updated_by_f;
380 creation_date fnd_concurrent_programs.creation_date%TYPE
381 := creation_date_f;
382 created_by fnd_concurrent_programs.created_by%TYPE
383 := created_by_f;
384 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
385 request_set_text varchar2(256);
386
387 program_name varchar2(10);
388 user_program_name varchar2(240);
389 new_name varchar2(30);
390 BEGIN
391 -- insert into fnd concurrent programs.
392 -- get id first
393
394
395 select fnd_concurrent_programs_s.nextval
396 into program_id
397 from sys.dual;
398
399 if (stage_id is not null) then
400 fnd_message.set_name('FND', 'SRS-Request Set Stage');
401 request_set_text := fnd_message.get;
402 program_name := 'FNDRSSTG';
403 user_program_name := substr(request_set_text||' '||stage_name, 1, 240);
404 fnd_message.set_name('FND', 'SRS-Report Set');
405 request_set_text := fnd_message.get;
406 user_program_name := substr(user_program_name||' - '||request_set_text
407 ||' '||request_set, 1, 240);
408 new_name := stage_short_name;
409 else
410 fnd_message.set_name('FND', 'SRS-Report Set');
411 request_set_text := fnd_message.get;
412 program_name := 'FNDRSSUB';
413 user_program_name := substr(request_set_text||' '||request_set, 1, 240);
414 new_name := set_short_name;
415 end if;
416
417 begin
418 select argument_method_code, queue_control_flag, queue_method_code,
419 run_alone_flag, enabled_flag, srs_flag,
420 print_flag, output_print_style, required_style, execution_options,
421 class_application_id, concurrent_class_id,
422 executable_application_id, executable_id,
423 save_output_flag, printer_name
424 into amc, qc, qmc, ra, enabled, srs, print, style, rs, eo,
425 cl_app_id, cl_id, ex_app_id, ex_id, so, printer
426 from fnd_concurrent_programs
427 where application_id = 0
428 and concurrent_program_name = program_name;
429 exception
430 when no_data_found then
431 message('Could not find program '||program_name||' for copy!');
432 raise program_error;
433 END;
434
435 insert into fnd_concurrent_programs
436 (application_id, concurrent_program_id, concurrent_program_name,
437 last_update_date, last_updated_by, execution_method_code,
438 queue_method_code, argument_method_code, queue_control_flag,
439 run_alone_flag, enabled_flag,
440 print_flag, output_print_style, required_style, execution_options,
441 class_application_id, concurrent_class_id, srs_flag,
442 created_by, creation_date, last_update_login,
443 executable_application_id, executable_id, save_output_flag,
444 printer_name, request_set_flag, restart, nls_compliant, enable_trace,
445 output_file_type)
446 values( set_application_id, program_id,
447 new_name, last_update_date, last_updated_by,
448 'I', qmc, 'D', qc, ra, enabled, print, style, rs, eo,
449 cl_app_id, cl_id, srs,
450 created_by, creation_date, last_update_login,
451 ex_app_id, ex_id, so, printer, 'Y', 'Y', 'N', 'N', 'TEXT');
452
453
454 --
455 -- Insert into TL table
456 --
457 insert into fnd_concurrent_programs_tl
458 (application_id,
459 concurrent_program_id,
460 last_update_date,
461 last_updated_by,
462 description,
463 user_concurrent_program_name,
464 created_by,
465 creation_date,
466 last_update_login,
467 language,
468 source_lang
469 )
470 select
471 set_application_id,
472 program_id,
473 create_program.last_update_date,
474 create_program.last_updated_by,
475 create_program.description,
476 user_program_name,
477 create_program.created_by,
478 create_program.creation_date,
479 create_program.last_update_login,
480 l.language_code,
481 lang
482 from fnd_languages l
483 where l.installed_flag in ('I', 'B');
484
485 return program_id;
486
487 END create_program;
488
489
490 --
491 -- Return the ID of the "incompatibility" program for a set or stage
492 --
493 function get_prog_id(set_appl_id number, set_id number, stage_id number)
494 return number is
495 prog_id number;
496 begin
497 /* Get a concurrent program ID from the source set/stage */
498 if (stage_id is not null) then /* source is STAGE */
499 select concurrent_program_id
500 into prog_id
501 from fnd_request_set_stages
502 where set_application_id = set_appl_id
503 and request_set_id = set_id
504 and request_set_stage_id = stage_id;
505
506 if (prog_id is null) then
507 message('Stage is not configured for incompatibilities.');
508 RAISE bad_parameter;
509 end if;
510
511 else /* source is SET */
512 select concurrent_program_id
513 into prog_id
514 from fnd_request_sets
515 where application_id = set_appl_id
516 and request_set_id = set_id;
517
518 if (prog_id is null) then
519 message('Set is not configured for incompatibilities.');
520 RAISE bad_parameter;
521 end if;
522 end if;
523
524 return (prog_id);
525 end;
526
527 /* ------------------------------------------------------------ */
528 /* ------------------------------------------------------------ */
529
530 /* START_PUBLIC */
531
532
533
534
535 -- Procedure
536 -- Incompatibility
537 --
538 -- Purpose
539 -- Register an incompatibility for a set or stage.
540 --
541 -- Examples:
542 -- 1. Set X is incompatible with program Y
543 -- fnd_set.incompatibility(request_set=>'X',
544 -- application=>'APPX',
545 -- inc_prog=>'Y',
546 -- inc_prog_application=>'APPY');
547 --
548 -- 2. Set X is incompatible withset Y.
549 -- fnd_set.incompatibility(request_set=>'X',
550 -- application=>'APPX',
551 -- inc_request_set=>'Y',
552 -- inc_set_application=>'APPY');
553 --
554 -- 3. Set X is incompatible with stage Y of set Y.
555 -- fnd_set.incompatibility(request_set=>'X',
556 -- application=>'APPX',
557 -- inc_request_set=>'Y',
558 -- inc_set_application=>'APPY',
559 -- inc_stage=>'Y');
560 --
561 -- 4. Stage X of set X is incompatable with program Y.
562 -- fnd_set.incompatibility(request_set=>'X',
563 -- application=>'APPX',
564 -- stage=>'X',
565 -- inc_prog=>'Y',
566 -- inc_prog_application=>'APPY');
567 --
568 --
569 -- Arguments
570 -- request_set - Request set short name.
571 -- application - Application short name of request set.
572 -- stage - Stage short name (for stage incompatibility).
573 -- inc_prog - Short name of the incompatible program.
574 -- inc_prog_application- Application of the incompatible program.
575 -- inc_request_set - Sort name of the incompatible reuqest set.
576 -- inc_set_application - Applicaiton short name of the incompatible set.
577 -- inc_stage - Stage short name to the incompatible stage.
578 -- inc_type - Incompatibility type - (D)omain-specific or (G)lobal
579 --
580 PROCEDURE incompatibility(request_set IN VARCHAR2,
581 application IN VARCHAR2,
582 stage IN VARCHAR2 DEFAULT NULL,
583 inc_prog IN VARCHAR2 DEFAULT NULL,
584 inc_prog_application IN VARCHAR2 DEFAULT NULL,
585 inc_request_set IN VARCHAR2 DEFAULT NULL,
586 inc_set_application IN VARCHAR2 DEFAULT NULL,
587 inc_stage IN VARCHAR2 DEFAULT NULL,
588 inc_type IN VARCHAR2 DEFAULT 'D')
589 IS
590 last_update_login fnd_flex_value_sets.last_update_login%TYPE
591 := last_update_login_f;
592 last_update_date fnd_flex_value_sets.last_update_date%TYPE
593 := last_update_date_f;
594 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
595 := last_updated_by_f;
596 creation_date fnd_flex_value_sets.creation_date%TYPE
597 := creation_date_f;
598 created_by fnd_flex_value_sets.created_by%TYPE
599 := created_by_f;
600 application_id_i fnd_application.application_id%TYPE;
601 concurrent_program_id_i
602 fnd_concurrent_programs.concurrent_program_id%TYPE;
603 to_run_concurrent_program_id_i
604 fnd_concurrent_programs.concurrent_program_id%TYPE;
605 to_run_application_id_i fnd_application.application_id%TYPE;
606 set_id number;
607 stage_id number;
608 BEGIN
609 message_init;
610
611 check_notnull(request_set);
612 check_notnull(application);
613 check_notnull(inc_type);
614
615 IF inc_type <> 'D' AND inc_type <> 'G' THEN
616 message('Invalid incompatibility type: ' ||inc_type);
617 RAISE bad_parameter;
618 END IF;
619
620 -- Get ID of first program
621 application_id_i := application_id_f(application);
622 set_id := request_set_id_f(application_id_i, request_set);
623 if (stage is not null) then
624 stage_id := request_set_stage_id_f(application_id_i, set_id,
625 stage);
626 else
627 stage_id := null;
628 end if;
629 concurrent_program_id_i := get_prog_id(application_id_i, set_id, stage_id);
630
631
632 -- Get ID of second program
633 if (inc_prog_application is not null) then /* incompatible PROGRAM */
634 to_run_application_id_i :=
635 application_id_f(inc_prog_application);
636 to_run_concurrent_program_id_i :=
637 concurrent_program_id_f(to_run_application_id_i, inc_prog);
638 else /* incompatible SET or STAGE */
639 to_run_application_id_i :=
640 application_id_f(inc_set_application);
641 set_id := request_set_id_f(to_run_application_id_i, inc_request_set);
642 if (inc_stage is not null) then
643 stage_id := request_set_stage_id_f(to_run_application_id_i, set_id,
644 inc_stage);
645 else
646 stage_id := null;
647 end if;
648 to_run_concurrent_program_id_i := get_prog_id(to_run_application_id_i,
649 set_id, stage_id);
650 end if;
651
652
653 /* update program definitions */
654 UPDATE fnd_concurrent_programs SET
655 queue_method_code = 'B'
656 WHERE ((application_id = application_id_i
657 AND concurrent_program_id = concurrent_program_id_i)
658 OR (application_id = to_run_application_id_i
659 AND concurrent_program_id = to_run_concurrent_program_id_i));
660
661
662 INSERT INTO fnd_concurrent_program_serial(running_concurrent_program_id,
663 running_application_id,
664 running_type,
665 to_run_concurrent_program_id,
666 to_run_application_id,
667 to_run_type,
668 last_update_date,
669 last_updated_by,
670 creation_date,
671 created_by,
672 last_update_login,
673 incompatibility_type)
674 VALUES(concurrent_program_id_i,
675 application_id_i,
676 'S',
677 to_run_concurrent_program_id_i,
678 to_run_application_id_i,
679 'S',
680 last_update_date,
681 last_updated_by,
682 creation_date,
683 created_by,
684 last_update_login,
685 inc_type);
686
687 /* and do the other side as well */
688 IF((concurrent_program_id_i <> to_run_concurrent_program_id_i)
689 OR
690 (application_id_i <> to_run_application_id_i)) THEN
691 BEGIN
692 INSERT
693 INTO fnd_concurrent_program_serial(running_concurrent_program_id,
694 running_application_id,
695 running_type,
696 to_run_concurrent_program_id,
697 to_run_application_id,
698 to_run_type,
699 last_update_date,
700 last_updated_by,
701 creation_date,
702 created_by,
703 last_update_login,
704 incompatibility_type)
705 VALUES(to_run_concurrent_program_id_i,
706 to_run_application_id_i,
707 'S',
708 concurrent_program_id_i,
709 application_id_i,
710 'S',
711 last_update_date,
712 last_updated_by,
713 creation_date,
714 created_by,
715 last_update_login,
716 inc_type);
717 END;
718 END IF;
719
720 println('created incompatibility entries.');
721 END;
722
723
724
725
726 -- Procedure
727 -- CREATE_SET
728 --
729 -- Purpose
730 -- Register a request set.
731 --
732 -- Arguments
733 -- name - Name of request set.
734 -- short_name - Short name. Dev key.
735 -- application - Short name of set's application.
736 -- description - Optional description of the set.
737 -- owner - Optional user ID of set owner. (e.g. SYSADMIN)
738 -- start_date - Date set becomes effective.
739 -- end_date - Optional date set becomes outdated.
740 -- print_together - 'Y' or 'N'
741 -- incompatibilities_allowed - 'Y' or 'N'
742 -- language_code - Language code for the above data. (e.g. US)
743 --
744 PROCEDURE create_set( name IN VARCHAR2,
745 short_name IN VARCHAR2,
746 application IN VARCHAR2,
747 description IN VARCHAR2 DEFAULT NULL,
748 owner IN VARCHAR2 DEFAULT NULL,
749 start_date IN DATE DEFAULT SYSDATE,
750 end_date IN DATE DEFAULT NULL,
751 print_together IN VARCHAR2 DEFAULT 'N',
752 incompatibilities_allowed IN VARCHAR2 DEFAULT 'N',
753 language_code IN VARCHAR2 DEFAULT 'US')
754 is
755 last_update_login fnd_request_groups.last_update_login%TYPE
756 := last_update_login_f;
757 last_update_date fnd_request_groups.last_update_date%TYPE
758 := last_update_date_f;
759 last_updated_by fnd_request_groups.last_updated_by%TYPE
760 := last_updated_by_f;
761 creation_date fnd_request_groups.creation_date%TYPE
762 := creation_date_f;
763 created_by fnd_request_groups.created_by%TYPE
764 := created_by_f;
765 set_appl_id fnd_application.application_id%TYPE;
766 set_id fnd_request_sets.request_set_id%TYPE;
767 owner_id fnd_user.user_id%TYPE;
768 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
769
770 BEGIN
771 message_init;
772
773 check_notnull(application);
774 check_notnull(name);
775 check_notnull(short_name);
776 check_notnull(start_date);
777 check_notnull(print_together);
778 check_notnull(incompatibilities_allowed);
779
780 check_yesno(print_together);
781 check_yesno(incompatibilities_allowed);
782
783
784 set_appl_id := application_id_f(application);
785
786 select fnd_request_sets_s.nextval into set_id from sys.dual;
787
788 if (owner is not null) then
789 BEGIN
790 select user_id
791 into owner_id
792 from fnd_user
793 where user_name = owner;
794 EXCEPTION
795 when no_data_found then
796 message('Could not find user '||owner);
797 raise bad_parameter;
798 END;
799 end if;
800
801 program_id := NULL;
802
803 if (upper(incompatibilities_allowed) = 'Y') then
804 program_id := create_program(request_set=>name,
805 set_application_id=>set_appl_id,
806 set_id=>set_id,
807 description=>description,
808 lang=>language_code,
809 set_short_name => short_name);
810 end if;
811
812 insert into fnd_request_sets
813 (application_id, request_set_id, request_set_name,
814 last_update_date, last_updated_by, creation_date,
815 created_by, last_update_login,
816 allow_constraints_flag, print_together_flag,
817 start_date_active, end_date_active,
818 concurrent_program_id, owner)
819 values
820 (set_appl_id, set_id, short_name,
821 last_update_date, last_updated_by, creation_date,
822 created_by, last_update_login,
823 upper(incompatibilities_allowed), upper(print_together), start_date,
824 end_date, program_id, owner_id);
825
826 -- insert rows for each installed language into the tl table
827 insert into FND_REQUEST_SETS_TL (
828 application_id, request_set_id, creation_date,
829 created_by, last_update_date, last_updated_by,
830 last_update_login, user_request_set_name, description,
831 language, source_lang
832 ) select
833 set_appl_id, set_id, create_set.creation_date,
834 create_set.created_by, create_set.last_update_date,
835 create_set.last_updated_by,
836 create_set.last_update_login,
837 create_set.name, create_set.description,
838 L.LANGUAGE_CODE,
839 create_set.language_code
840 from FND_LANGUAGES L
841 where L.INSTALLED_FLAG in ('I', 'B');
842
843 println('Request set '|| name ||' created.');
844
845 END create_set;
846
847
848
849 -- Procedure
850 -- ADD_STAGE
851 --
852 -- Purpose
853 -- Add a stage to a request set.
854 --
855 -- Arguments
856 -- name - Stage name.
857 -- request_set - Short name of request set.
858 -- set_application - Application short name of the request_set.
859 -- short_name - Stage short (non-translated) name.
860 -- description - Stage description.
861 -- display_sequence - Display sequence.
862 -- function_short_name - Funciton (executable) short name.
863 -- function_application - Function application short name.
864 -- critical - Is this a "critical" stage? (Determines set outcome.)
865 -- incompatibilities_allowed - 'Y' or 'N'
866 -- start_stage - Is this the start stage for the set? 'Y' or 'N'
867 -- language_code - Language code for the above data. (e.g. US)
868 --
869 PROCEDURE add_stage(name IN VARCHAR2,
870 request_set IN VARCHAR2,
871 set_application IN VARCHAR2,
872 short_name IN VARCHAR2,
873 description IN VARCHAR2 DEFAULT NULL,
874 display_sequence IN NUMBER,
875 function_short_name IN VARCHAR2
876 DEFAULT 'FNDRSSTE',
877 function_application IN VARCHAR2 DEFAULT 'FND',
878 critical IN VARCHAR2 DEFAULT 'N',
879 incompatibilities_allowed IN VARCHAR2 DEFAULT 'N',
880 start_stage IN VARCHAR2 DEFAULT 'N',
881 language_code IN VARCHAR2 DEFAULT 'US'
882 )
883 is
884 last_update_login fnd_request_groups.last_update_login%TYPE
885 := last_update_login_f;
886 last_update_date fnd_request_groups.last_update_date%TYPE
887 := last_update_date_f;
888 last_updated_by fnd_request_groups.last_updated_by%TYPE
889 := last_updated_by_f;
890 creation_date fnd_request_groups.creation_date%TYPE
891 := creation_date_f;
892 created_by fnd_request_groups.created_by%TYPE
893 := created_by_f;
894 set_appl_id fnd_application.application_id%TYPE;
895 set_id fnd_request_sets.request_set_id%TYPE;
896 print_style_name fnd_printer_styles.printer_style_name%TYPE;
897 stage_id fnd_request_set_stages_tl.request_set_stage_id%TYPE;
898 dummy varchar2(1);
899 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
900 outcome_meaning varchar2(1);
901 function_id number := null;
902 function_appl_id number := null;
903 set_name varchar2(240);
904
905 BEGIN
906 message_init;
907
908 check_notnull(name);
909 check_notnull(short_name);
910 check_notnull(request_set);
911 check_notnull(set_application);
912 check_notnull(critical);
913 check_notnull(incompatibilities_allowed);
914
915 check_yesno(critical);
916 check_yesno(incompatibilities_allowed);
917
918 if (function_short_name is not null) then
919 function_appl_id := application_id_f(function_application);
920 begin
921 select executable_id
922 into function_id
923 from fnd_executables
924 where application_id = function_appl_id
925 and executable_name = function_short_name;
926 exception
927 when no_data_found then
928 message('Invalid function short name:'||function_short_name);
929 raise bad_parameter;
930 end;
931 end if;
932
933 set_appl_id := application_id_f(set_application);
934 set_id := request_set_id_f(set_appl_id, request_set);
935
936 select fnd_request_set_stages_s.nextval
937 into stage_id
938 from sys.dual;
939
940 if (upper(incompatibilities_allowed) = 'Y') then
941 select user_request_set_name
942 into set_name
943 from fnd_request_sets_vl
944 where request_set_id = set_id
945 and application_id = set_appl_id;
946
947 program_id := create_program(request_set=>set_name,
948 set_application_id=>set_appl_id,
949 stage_id=>stage_id,
950 stage_name=>name,
951 description=>description,
952 lang=>language_code,
953 stage_short_name => short_name);
954 else
955 program_id := null;
956 end if;
957
958 insert into fnd_request_set_stages
959 (set_application_id, request_set_id, request_set_stage_id,
960 stage_name,
961 last_update_date, last_updated_by, creation_date,
962 created_by, last_update_login,
963 allow_constraints_flag,
964 concurrent_program_id, critical, outcome,
965 function_id, function_application_id,
966 display_sequence)
967 values
968 (set_appl_id, set_id, stage_id, short_name,
969 last_update_date, last_updated_by, creation_date,
970 created_by, last_update_login,
971 upper(incompatibilities_allowed),
972 program_id,
973 upper(critical), 'C', function_id, function_appl_id,
974 display_sequence);
975
976 -- insert rows for each installed language into the tl table
977 insert into FND_REQUEST_SET_STAGES_TL (
978 set_application_id, request_set_id, request_set_stage_id,
979 creation_date, created_by, last_update_date,
980 last_updated_by, last_update_login, user_stage_name,
981 description, language, source_lang
982 ) select
983 set_appl_id, set_id, stage_id,
984 add_stage.creation_date, add_stage.created_by,
985 add_stage.last_update_date,
986 add_stage.last_updated_by, add_stage.last_update_login, name,
987 add_stage.description, l.language_code, add_stage.language_code
988 from FND_LANGUAGES L
989 where L.INSTALLED_FLAG in ('I', 'B');
990
991 println('Added stage ' || name ||
992 ' to set ' || request_set || '.');
993
994 if (start_stage = 'Y') then
995 update fnd_request_sets
996 set start_stage = stage_id
997 where request_set_id = set_id
998 and application_id = set_appl_id;
999 end if;
1000
1001 END add_stage;
1002
1003
1004 -- Procedure
1005 -- Link_Stages
1006 --
1007 -- Purpose
1008 -- Link Two Stages.
1009 --
1010 -- Arguments
1011 -- request_set - Short name of request set.
1012 -- set_application - Application of the request set.
1013 -- from_stage - From stage short name.
1014 -- to_stage - To stage short name. (null to erase a link)
1015 -- success - Create success link. 'Y' or 'N'
1016 -- warning - Create warning link. 'Y' or 'N'
1017 -- error - Create error link. 'Y' or 'N'
1018 --
1019 procedure link_stages (request_set varchar2,
1020 set_application varchar2,
1021 from_stage varchar2,
1022 to_stage varchar2 default null,
1023 success varchar2 default 'N',
1024 warning varchar2 default 'N',
1025 error varchar2 default 'N')
1026 is
1027 last_update_login fnd_request_groups.last_update_login%TYPE
1028 := last_update_login_f;
1029 last_update_date fnd_request_groups.last_update_date%TYPE
1030 := last_update_date_f;
1031 last_updated_by fnd_request_groups.last_updated_by%TYPE
1032 := last_updated_by_f;
1033 set_appl_id fnd_application.application_id%TYPE;
1034 set_id fnd_request_sets.request_set_id%TYPE;
1035 from_stage_id fnd_request_set_stages_tl.request_set_stage_id%TYPE;
1036 to_stage_id fnd_request_set_stages_tl.request_set_stage_id%TYPE;
1037 dummy varchar2(1);
1038 BEGIN
1039 message_init;
1040
1041 check_notnull(request_set);
1042 check_notnull(set_application);
1043 check_notnull(from_stage);
1044
1045 set_appl_id := application_id_f(set_application);
1046 set_id := request_set_id_f(set_appl_id, request_set);
1047 from_stage_id := request_set_stage_id_f(set_appl_id, set_id, from_stage);
1048
1049 if (to_stage is not null) then
1050 to_stage_id := request_set_stage_id_f(set_appl_id, set_id, to_stage);
1051 else
1052 to_stage_id := null;
1053 end if;
1054
1055 if (success = 'Y') then
1056 update fnd_request_set_stages
1057 set success_link = to_stage_id
1058 where set_application_id = set_appl_id
1059 and request_set_id = set_id
1060 and request_set_stage_id = from_stage_id;
1061 end if;
1062
1063 if (warning = 'Y') then
1064 update fnd_request_set_stages
1065 set warning_link = to_stage_id
1066 where set_application_id = set_appl_id
1067 and request_set_id = set_id
1068 and request_set_stage_id = from_stage_id;
1069 end if;
1070
1071 if (error = 'Y') then
1072 update fnd_request_set_stages
1073 set error_link = to_stage_id
1074 where set_application_id = set_appl_id
1075 and request_set_id = set_id
1076 and request_set_stage_id = from_stage_id;
1077 end if;
1078 end;
1079
1080
1081 -- Procedure
1082 -- ADD_PROGRAM
1083 --
1084 -- Purpose
1085 -- Add a concurrent program to a request set stage.
1086 --
1087 -- Arguments
1088 -- program - Short name of the program. (e.g. FNDSCRMT)
1089 -- program_application - Application short name of the program.(e.g. 'FND')
1090 -- request_set - Short name of request set.
1091 -- set_application - Application of the request set.
1092 -- stage - Short name of stage.
1093 -- program_sequence - Must be unique!
1094 -- critical - Use this program in stage outcome?
1095 -- number_of_copies - Copies to Print. (optional)
1096 -- save_output - 'Y' or 'N'
1097 -- style - Print style name. (optional)
1098 -- printer - Printer name. (optional)
1099 --
1100 PROCEDURE add_program(program IN VARCHAR2,
1101 program_application IN VARCHAR2,
1102 request_set IN VARCHAR2,
1103 set_application IN VARCHAR2,
1104 stage IN VARCHAR2,
1105 program_sequence IN NUMBER,
1106 critical IN VARCHAR2,
1107 number_of_copies IN NUMBER DEFAULT 0,
1108 save_output IN VARCHAR2 DEFAULT 'Y',
1109 style IN VARCHAR2 DEFAULT NULL,
1110 printer IN VARCHAR2 DEFAULT NULL) is
1111 last_update_login fnd_request_groups.last_update_login%TYPE
1112 := last_update_login_f;
1113 last_update_date fnd_request_groups.last_update_date%TYPE
1114 := last_update_date_f;
1115 last_updated_by fnd_request_groups.last_updated_by%TYPE
1116 := last_updated_by_f;
1117 creation_date fnd_request_groups.creation_date%TYPE
1118 := creation_date_f;
1119 created_by fnd_request_groups.created_by%TYPE
1120 := created_by_f;
1121 set_appl_id fnd_application.application_id%TYPE;
1122 program_appl_id fnd_application.application_id%TYPE;
1123 set_id fnd_request_sets.request_set_id%TYPE;
1124 stage_id fnd_request_set_stages.request_set_stage_id%TYPE;
1125 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
1126 print_style_name fnd_printer_styles.printer_style_name%TYPE;
1127 dummy varchar2(1);
1128 BEGIN
1129 message_init;
1130
1131 check_notnull(program);
1132 check_notnull(program_application);
1133 check_notnull(set_application);
1134 check_notnull(program_sequence);
1135 check_notnull(stage);
1136 check_notnull(critical);
1137
1138 check_yesno(critical);
1139
1140 program_appl_id := application_id_f(program_application);
1141 set_appl_id := application_id_f(set_application);
1142
1143 if (style is not null) then
1144 BEGIN /* First check developer key */
1145 SELECT printer_style_name
1146 INTO print_style_name
1147 FROM fnd_printer_styles
1148 WHERE printer_style_name = style;
1149 EXCEPTION
1150 WHEN no_data_found THEN /* Check translated value */
1151 BEGIN
1152 SELECT printer_style_name
1153 INTO print_style_name
1154 FROM fnd_printer_styles_tl
1155 WHERE user_printer_style_name = style
1156 AND rownum = 1;
1157 EXCEPTION
1158 WHEN no_data_found THEN
1159 message('Cannot find print style: '||style);
1160 RAISE bad_parameter;
1161 END;
1162 END;
1163 end if;
1164
1165 if (printer is not null) then
1166 BEGIN
1167 select 'x'
1168 into dummy
1169 from fnd_printer
1170 where printer_name = printer;
1171 EXCEPTION
1172 when no_data_found then
1173 message('Cannot find printer : '|| printer);
1174 raise bad_parameter;
1175 END;
1176 end if;
1177
1178 program_id :=
1179 concurrent_program_id_f(program_appl_id, program);
1180 set_id := request_set_id_f(set_appl_id, request_set);
1181 stage_id := request_set_stage_id_f(set_appl_id, set_id, stage);
1182
1183 insert into fnd_request_set_programs
1184 (set_application_id, request_set_id, request_set_program_id,
1185 request_set_stage_id,
1186 sequence, last_update_date, last_updated_by,
1187 creation_date, created_by, last_update_login,
1188 program_application_id, concurrent_program_id,
1189 number_of_copies, save_output_flag,
1190 printer, print_style, critical)
1191 values
1192 (set_appl_id, set_id, fnd_request_set_programs_s.nextval,
1193 stage_id,
1194 program_sequence, last_update_date, last_updated_by,
1195 creation_date, created_by, last_update_login,
1196 program_appl_id, program_id,
1197 number_of_copies, save_output,
1198 printer, print_style_name, upper(add_program.critical));
1199
1200 println('Added program ' || program ||
1201 ' to set ' || request_set || '.');
1202
1203 END add_program;
1204
1205
1206
1207 -- Procedure
1208 -- REMOVE_STAGE
1209 --
1210 -- Purpose
1211 -- Remove a stage from a request set.
1212 --
1213 -- Arguments
1214 -- request_set - Short name of request set.
1215 -- set_application - Application short name of the request set.
1216 -- stage - Stage short name
1217 --
1218 PROCEDURE remove_stage(request_set IN VARCHAR2,
1219 set_application IN VARCHAR2,
1220 stage IN VARCHAR2)
1221 is
1222 set_appl_id fnd_application.application_id%TYPE;
1223 set_id fnd_request_sets.request_set_id%TYPE;
1224 stage_id fnd_request_set_stages.request_set_stage_id%TYPE;
1225 program_short_name fnd_concurrent_programs.concurrent_program_name%TYPE;
1226 BEGIN
1227 message_init;
1228
1229 check_notnull(set_application);
1230 check_notnull(request_set);
1231 check_notnull(stage);
1232
1233 set_appl_id := application_id_f(set_application);
1234 set_id := request_set_id_f(set_appl_id, request_set);
1235 stage_id := request_set_stage_id_f(set_appl_id, set_id, stage);
1236
1237 -- Delete program args
1238 delete from fnd_request_set_program_args
1239 where (application_id, request_set_id, request_set_program_id) in
1240 (select p.set_application_id, p.request_set_id,
1241 p.request_set_program_id
1242 from fnd_request_set_programs p
1243 where p.set_application_id = set_appl_id
1244 and p.request_set_id = set_id
1245 and p.request_set_stage_id = stage_id);
1246
1247 -- Delete member programs
1248 delete from fnd_request_set_programs
1249 where set_application_id = set_appl_id
1250 and request_set_id = set_id
1251 and request_set_stage_id = stage_id;
1252
1253 -- Delete stage program (if any)
1254 begin
1255 select concurrent_program_name
1256 into program_short_name
1257 from fnd_concurrent_programs p,
1258 fnd_request_set_stages r
1259 where r.set_application_id = set_appl_id
1260 and r.request_set_id = set_id
1261 and r.request_set_stage_id = stage_id
1262 and r.set_application_id = p.application_id
1263 and r.concurrent_program_id = p.concurrent_program_id;
1264
1265 fnd_program.delete_program(program_short_name, set_application);
1266 exception
1267 when others then
1268 message_init;
1269 null; -- We don't care if the program does not exist
1270 end;
1271
1272 -- Delete fn params
1273 delete from fnd_stage_fn_parameter_values
1274 where set_application_id = set_appl_id
1275 and request_set_id = set_id
1276 and request_set_stage_id = stage_id;
1277
1278 -- Delete translations
1279 delete from fnd_request_set_stages_tl
1280 where set_application_id = set_appl_id
1281 and request_set_id = set_id
1282 and request_set_stage_id = stage_id;
1283
1284 -- Delete stage
1285 delete from fnd_request_set_stages
1286 where set_application_id = set_appl_id
1287 and request_set_id = set_id
1288 and request_set_stage_id = stage_id;
1289
1290 -- Update links
1291 update fnd_request_set_stages
1292 set success_link = null
1293 where set_application_id = set_appl_id
1294 and request_set_id = set_id
1295 and success_link = stage_id;
1296
1297 update fnd_request_set_stages
1298 set warning_link = null
1299 where set_application_id = set_appl_id
1300 and request_set_id = set_id
1301 and warning_link = stage_id;
1302
1303 update fnd_request_set_stages
1304 set error_link = null
1305 where set_application_id = set_appl_id
1306 and request_set_id = set_id
1307 and error_link = stage_id;
1308
1309 END remove_stage;
1310
1311
1312
1313 -- Procedure
1314 -- REMOVE_PROGRAM
1315 --
1316 -- Purpose
1317 -- Remove a concurrent program from a request set.
1318 --
1319 -- Arguments
1320 -- program - Short name of the program. (e.g. FNDSCRMT)
1321 -- program_application - Application of the program. (e.g. 'FND')
1322 -- request_set - Short name of request set.
1323 -- set_application - Application short name of the request set.
1324 -- stage r - Stage short name.
1325 -- program_sequence - Program sequence number.
1326 --
1327 PROCEDURE remove_program(program IN VARCHAR2,
1328 program_application IN VARCHAR2,
1329 request_set IN VARCHAR2,
1330 set_application IN VARCHAR2,
1331 stage IN VARCHAR2,
1332 program_sequence IN NUMBER)
1333 is
1334 set_appl_id fnd_application.application_id%TYPE;
1335 program_appl_id fnd_application.application_id%TYPE;
1336 set_id fnd_request_sets.request_set_id%TYPE;
1337 stage_id fnd_request_set_stages.request_set_stage_id%TYPE;
1338 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
1339 BEGIN
1340 message_init;
1341
1342 check_notnull(program);
1343 check_notnull(program_application);
1344 check_notnull(set_application);
1345 check_notnull(program_sequence);
1346 check_notnull(stage);
1347 check_notnull(request_set);
1348
1349 program_appl_id := application_id_f(program_application);
1350 set_appl_id := application_id_f(set_application);
1351 program_id :=
1352 concurrent_program_id_f(program_appl_id, program);
1353 set_id := request_set_id_f(set_appl_id, request_set);
1354 stage_id := request_set_stage_id_f(set_appl_id, set_id, stage);
1355
1356 delete from fnd_request_set_program_args
1357 where (application_id, request_set_id, request_set_program_id) in
1358 (select p.set_application_id, p.request_set_id,
1359 p.request_set_program_id
1360 from fnd_request_set_programs p
1361 where p.set_application_id = set_appl_id
1362 and p.request_set_id = set_id
1363 and p.program_application_id = program_appl_id
1364 and p.concurrent_program_id = program_id
1365 and p.request_set_stage_id = stage_id
1366 and p.sequence = program_sequence);
1367
1368 delete from fnd_request_set_programs
1369 where set_application_id = set_appl_id
1370 and request_set_id = set_id
1371 and program_application_id = program_appl_id
1372 and concurrent_program_id = program_id
1373 and request_set_stage_id = stage_id
1374 and sequence = program_sequence;
1375
1376 END remove_program;
1377
1378
1379
1380 -- Procedure
1381 -- PROGRAM_PARAMETER
1382 --
1383 -- Purpose
1384 -- Register a parameter for a request set program
1385 --
1386 -- Arguments:
1387 -- program - e.g. FNDSCRMT
1388 -- application - Program application. e.g.'FND'
1389 -- request_set - Short name of request set.
1390 -- set_application - Application short name of the request set.
1391 -- stage - Stage short name.
1392 -- program_sequence - Program sequence number.
1393 -- parameter - Name of the program parameter. (NOT the prompt!)
1394 -- display - 'Y' or 'N'
1395 -- modify - 'Y' or 'N'
1396 -- shared_parameter - Name of shared parameter. (optional)
1397 -- default_type - 'Constant', 'Profile', 'SQL Statement', 'Segment'
1398 -- (Optional)
1399 -- default_value - Parameter default (Required if default_type is not
1400 -- Null)
1401 --
1402 PROCEDURE program_parameter(
1403 program IN VARCHAR2,
1404 program_application IN VARCHAR2,
1405 request_set IN VARCHAR2,
1406 set_application IN VARCHAR2,
1407 stage IN VARCHAR2,
1408 program_sequence IN NUMBER,
1409 parameter IN VARCHAR2,
1410 display IN VARCHAR2 DEFAULT 'Y',
1411 modify IN VARCHAR2 DEFAULT 'Y',
1412 shared_parameter IN VARCHAR2 DEFAULT NULL,
1413 default_type IN VARCHAR2 DEFAULT NULL,
1414 default_value IN VARCHAR2 DEFAULT NULL) is
1415 last_update_login fnd_request_groups.last_update_login%TYPE
1416 := last_update_login_f;
1417 last_update_date fnd_request_groups.last_update_date%TYPE
1418 := last_update_date_f;
1419 last_updated_by fnd_request_groups.last_updated_by%TYPE
1420 := last_updated_by_f;
1421 creation_date fnd_request_groups.creation_date%TYPE
1422 := creation_date_f;
1423 created_by fnd_request_groups.created_by%TYPE
1424 := created_by_f;
1425 set_appl_id fnd_application.application_id%TYPE;
1426 program_appl_id fnd_application.application_id%TYPE;
1427 set_id fnd_request_sets.request_set_id%TYPE;
1428 stage_id fnd_request_set_stages.request_set_stage_id%TYPE;
1429 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
1430 default_type_code fnd_request_set_program_args.default_type%TYPE;
1431 set_program_id fnd_request_set_programs.request_set_program_id%TYPE;
1432 appl_column_name
1433 fnd_descr_flex_column_usages.application_column_name%TYPE;
1434
1435 BEGIN
1436 message_init;
1437
1438 check_notnull(program);
1439 check_notnull(program_application);
1440 check_notnull(set_application);
1441 check_notnull(program_sequence);
1442 check_notnull(stage);
1443 check_notnull(parameter);
1444 check_notnull(display);
1445 check_notnull(modify);
1446
1447 check_yesno(display);
1448 check_yesno(modify);
1449
1450
1451 program_appl_id := application_id_f(program_application);
1452 set_appl_id := application_id_f(set_application);
1453 program_id :=
1454 concurrent_program_id_f(program_appl_id, program);
1455 set_id := request_set_id_f(set_appl_id, request_set);
1456 stage_id := request_set_stage_id_f(set_appl_id, set_id, stage);
1457
1458 BEGIN
1459 select request_set_program_id
1460 into set_program_id
1461 from fnd_request_set_programs
1462 where request_set_id = set_id
1463 and set_application_id = set_appl_id
1464 and concurrent_program_id = program_id
1465 and program_application_id = program_appl_id
1466 and request_set_stage_id = stage_id
1467 and sequence = program_sequence;
1468 EXCEPTION
1469 when no_data_found then
1470
1471 message('Could not find program ' || program ||
1472 ' in set ' || request_set);
1473 raise bad_parameter;
1474 END;
1475
1476 -- validate default type name
1477 BEGIN
1478 if(default_type is not null) then
1479 select lookup_code
1480 into default_type_code
1481 from fnd_lookup_values l
1482 where l.lookup_type = 'FLEX_DEFAULT_TYPE'
1483 and l.enabled_flag = 'Y'
1484 and sysdate between nvl(l.start_date_active, Sysdate)
1485 and nvl(l.end_date_active, Sysdate)
1486 and upper(meaning) = upper(default_type)
1487 and rownum = 1;
1488 end if;
1489 EXCEPTION
1490 when no_data_found then
1491 message('bad default type:' || default_type);
1492 raise bad_parameter;
1493 END;
1494
1495 BEGIN
1496 select application_column_name
1497 into appl_column_name
1498 from fnd_descr_flex_column_usages
1499 where descriptive_flexfield_name = '$SRS$.'||program
1500 and descriptive_flex_context_code = 'Global Data Elements'
1501 and end_user_column_name = parameter
1502 and application_id = program_appl_id;
1503 EXCEPTION
1504 when no_data_found then
1505 message('Could not find paramter' || parameter ||
1506 ' for program ' || program);
1507 raise bad_parameter;
1508 END;
1509
1510
1511 if((default_type is null) and (default_value is not null)) then
1512 message('default type required when default value specified');
1513 raise bad_parameter;
1514 end if;
1515
1516 insert into fnd_request_set_program_args
1517 (application_id, request_set_id, request_set_program_id,
1518 descriptive_flex_appl_id, descriptive_flexfield_name,
1519 application_column_name, last_update_date, last_updated_by,
1520 creation_date, created_by, last_update_login, display_flag,
1521 insert_flag, update_flag, default_type, default_value,
1522 shared_parameter_name)
1523 values
1524 (set_appl_id, set_id, set_program_id,
1525 program_appl_id, '$SRS$.'||program,
1526 appl_column_name, last_update_date, last_updated_by,
1527 creation_date, created_by, last_update_login, upper(display),
1528 upper(modify), upper(modify), default_type_code, default_value,
1529 shared_parameter);
1530
1531 END program_parameter;
1532
1533
1534
1535 -- Procedure
1536 -- ADD_SET_TO_GROUP
1537 --
1538 -- Purpose
1539 -- Add a request set to a request group.
1540 --
1541 -- Arguments
1542 -- request_set - Short name of set.
1543 -- set_application - Application short name of the set.
1544 -- request_group - Name of request group.
1545 -- group_application - Application of the request group.
1546 --
1547 PROCEDURE add_set_to_group(request_set IN VARCHAR2,
1548 set_application IN VARCHAR2,
1549 request_group IN VARCHAR2,
1550 group_application IN VARCHAR2)
1551 is
1552 last_update_login fnd_request_groups.last_update_login%TYPE
1553 := last_update_login_f;
1554 last_update_date fnd_request_groups.last_update_date%TYPE
1555 := last_update_date_f;
1556 last_updated_by fnd_request_groups.last_updated_by%TYPE
1557 := last_updated_by_f;
1558 creation_date fnd_request_groups.creation_date%TYPE
1559 := creation_date_f;
1560 created_by fnd_request_groups.created_by%TYPE
1561 := created_by_f;
1562 set_application_id
1563 fnd_application.application_id%TYPE;
1564 group_application_id
1565 fnd_application.application_id%TYPE;
1566 set_id
1567 fnd_request_sets.request_set_id%TYPE;
1568 group_id
1569 fnd_request_groups.request_group_id%TYPE;
1570 BEGIN
1571 message_init;
1572
1573 check_notnull(set_application);
1574 check_notnull(request_set);
1575 check_notnull(request_group);
1576 check_notnull(group_application);
1577
1578 set_application_id := application_id_f(set_application);
1579 group_application_id := application_id_f(group_application);
1580
1581 set_id := request_set_id_f(set_application_id, request_set);
1582 group_id :=
1583 request_group_id_f(group_application_id, request_group);
1584
1585 insert into Fnd_Request_Group_Units( APPLICATION_ID,
1586 REQUEST_GROUP_ID,
1587 REQUEST_UNIT_TYPE,
1588 UNIT_APPLICATION_ID,
1589 REQUEST_UNIT_ID,
1590 LAST_UPDATE_DATE,
1591 LAST_UPDATED_BY,
1592 CREATION_DATE,
1593 CREATED_BY,
1594 LAST_UPDATE_LOGIN)
1595 values( group_application_id,
1596 group_id,
1597 'S',
1598 set_application_id,
1599 set_id,
1600 last_update_date,
1601 last_updated_by,
1602 creation_date,
1603 created_by,
1604 last_update_login);
1605
1606 println('Added set '|| request_set || ' to group '
1607 ||request_group);
1608
1609 END;
1610
1611
1612
1613
1614 -- Procedure
1615 -- REMOVE_SET_FROM_GROUP
1616 --
1617 -- Purpose
1618 -- Remove a set from a request group.
1619 --
1620 -- Arguments
1621 -- request_set - Short name of set.
1622 -- set_application - Application short name of the set.
1623 -- request_group - Name of request group.
1624 -- group_application - Application of the request group.
1625 --
1626 PROCEDURE remove_set_from_group(request_set IN VARCHAR2,
1627 set_application IN VARCHAR2,
1628 request_group IN VARCHAR2,
1629 group_application IN VARCHAR2)
1630 is
1631 set_application_id
1632 fnd_application.application_id%TYPE;
1633 group_application_id
1634 fnd_application.application_id%TYPE;
1635 set_id
1636 fnd_request_sets.request_set_id%TYPE;
1637 group_id
1638 fnd_request_groups.request_group_id%TYPE;
1639 BEGIN
1640 message_init;
1641
1642 check_notnull(set_application);
1643 check_notnull(request_set);
1644 check_notnull(request_group);
1645 check_notnull(group_application);
1646
1647 set_application_id := application_id_f(set_application);
1648 group_application_id := application_id_f(group_application);
1649
1650 set_id := request_set_id_f(set_application_id, request_set);
1651 group_id :=
1652 request_group_id_f(group_application_id, request_group);
1653
1654 delete from fnd_request_group_units u
1655 where application_id = group_application_id
1656 and request_group_id = group_id
1657 and request_unit_type = 'S'
1658 and unit_application_id = set_application_id
1659 and request_unit_id = set_id;
1660 END;
1661
1662
1663
1664 -- Procedure
1665 -- DELETE_PROGRAM_PARAMETER
1666 --
1667 -- Purpose
1668 -- Delete a concurrent program request set parameter.
1669 --
1670 -- Arguments
1671 -- program - Short name of the program. (e.g. FNDSCRMT)
1672 -- program_application - Application short name of the program. (e.g. 'FND')
1673 -- request_set - Short name of request set.
1674 -- set_application - Application short name of the request set.
1675 -- stage - Stage number.
1676 -- program_sequence - Program sequence number.
1677 -- parameter - Name of the program parameter. (NOT the prompt!)
1678 --
1679 PROCEDURE delete_program_parameter(program IN VARCHAR2,
1680 program_application IN VARCHAR2,
1681 request_set IN VARCHAR2 DEFAULT NULL,
1682 stage IN VARCHAR2,
1683 set_application IN VARCHAR2,
1684 program_sequence IN NUMBER,
1685 parameter IN VARCHAR2)
1686 is
1687 set_id
1688 fnd_request_sets.request_set_id%TYPE;
1689 stage_id
1690 fnd_request_set_stages.request_set_stage_id%TYPE;
1691 program_appl_id
1692 fnd_application.application_id%TYPE;
1693 set_appl_id
1694 fnd_application.application_id%TYPE;
1695 set_program_id
1696 fnd_request_set_program_args.request_set_program_id%TYPE;
1697 BEGIN
1698 message_init;
1699
1700 check_notnull(program);
1701 check_notnull(program_application);
1702 check_notnull(set_application);
1703 check_notnull(stage);
1704 check_notnull(program_sequence);
1705 check_notnull(parameter);
1706
1707 set_appl_id := application_id_f(set_application);
1708 program_appl_id := application_id_f(program_application);
1709
1710 set_id := request_set_id_f(set_appl_id, request_set);
1711 stage_id := request_set_stage_id_f(set_appl_id, set_id, stage);
1712
1713 BEGIN
1714 select request_set_program_id
1715 into set_program_id
1716 from fnd_request_set_programs s,
1717 fnd_concurrent_programs p
1718 where p.concurrent_program_name = program
1719 and p.application_id = program_appl_id
1720 and p.concurrent_program_id = s.concurrent_program_id
1721 and p.application_id = s.program_application_id
1722 and s.request_set_id = set_id
1723 and s.set_application_id = set_appl_id
1724 and s.request_set_stage_id = stage_id
1725 and s.sequence = program_sequence;
1726 EXCEPTION
1727 WHEN no_data_found THEN
1728 message('Could not find program '|| program
1729 || ' in set '|| request_set || ' with seq# ' ||
1730 to_char(program_sequence));
1731 RAISE bad_parameter;
1732 END;
1733
1734 delete from fnd_request_set_program_args a
1735 where a.application_id = set_appl_id
1736 and a.request_set_id = set_id
1737 and a.request_set_program_id = set_program_id
1738 and (a.descriptive_flex_appl_id,
1739 a.descriptive_flexfield_name,
1740 a.application_column_name) in
1741 (select u.application_id,
1742 u.descriptive_flexfield_name,
1743 u.application_column_name
1744 from fnd_descr_flex_column_usages u
1745 where u.application_id = program_appl_id
1746 and u.descriptive_flexfield_name = '$SRS$.'||program
1747 and u.descriptive_flex_context_code = 'Global Data Elements'
1748 and u.end_user_column_name = parameter);
1749
1750 END delete_program_parameter;
1751
1752
1753
1754
1755 -- Procedure
1756 -- DELETE_SET
1757 --
1758 -- Purpose
1759 -- Delete a request set, and references to that set.
1760 --
1761 -- Arguments
1762 -- request_set - Short name of the set.
1763 -- application - Application short name of the executable.
1764 --
1765 PROCEDURE delete_set(request_set IN VARCHAR2,
1766 application IN VARCHAR2) is
1767 set_appl_id
1768 fnd_application.application_id%TYPE;
1769 set_id
1770 fnd_request_sets.request_set_id%TYPE;
1771 program_short_name
1772 fnd_concurrent_programs.concurrent_program_name%TYPE;
1773
1774 cursor c (set_appl_id number, set_id number) is
1775 select request_set_stage_id
1776 from fnd_request_set_stages
1777 where request_set_id = set_id
1778 and set_application_id = set_appl_id;
1779 BEGIN
1780 message_init;
1781
1782 check_notnull(application);
1783 check_notnull(request_set);
1784
1785 set_appl_id := application_id_f(application);
1786
1787 BEGIN
1788 set_id := request_set_id_f(set_appl_id, request_set);
1789 EXCEPTION
1790 when others then -- If set doesn't exist then exit normally
1791 println('Set '|| request_set || ' not found.');
1792 message_init;
1793 return;
1794 END;
1795
1796 -- Delete the program if it exists
1797 BEGIN
1798 select concurrent_program_name
1799 into program_short_name
1800 from fnd_concurrent_programs p,
1801 fnd_request_sets r
1802 where r.application_id = set_appl_id
1803 and r.request_set_id = set_id
1804 and r.application_id = p.application_id
1805 and r.concurrent_program_id = p.concurrent_program_id;
1806
1807 fnd_program.delete_program(program_short_name, application);
1808 EXCEPTION
1809 when others then
1810 message_init;
1811 null; -- We don't care if the program does not exist
1812 END;
1813
1814 -- Of course delete program might have already executed the
1815 -- following deletes, but there's really no way to tell.
1816 -- It doesn't matter anyway. This will be rare.
1817
1818 -- Remove set from request groups
1819
1820 DELETE FROM fnd_request_group_units
1821 WHERE request_unit_type = 'S'
1822 AND request_unit_id = set_id
1823 AND unit_application_id = set_appl_id;
1824
1825 -- Remove member program arguments
1826
1827 DELETE FROM fnd_request_set_program_args
1828 WHERE application_id = set_appl_id
1829 AND request_set_id = set_id;
1830
1831 -- Remove member programs
1832
1833 DELETE FROM fnd_request_set_programs
1834 WHERE set_application_id = set_appl_id
1835 AND request_set_id = set_id;
1836
1837 -- Delete stage master programs (if any)
1838 for rec in c(set_appl_id, set_id) loop
1839 begin
1840 select concurrent_program_name
1841 into program_short_name
1842 from fnd_concurrent_programs p,
1843 fnd_request_set_stages r
1844 where r.set_application_id = set_application_id
1845 and r.request_set_id = set_id
1846 and r.request_set_stage_id = rec.request_set_stage_id
1847 and r.set_application_id = p.application_id
1848 and r.concurrent_program_id = p.concurrent_program_id;
1849
1850 fnd_program.delete_program(program_short_name, application);
1851 exception
1852 when others then
1853 message_init;
1854 null; -- We don't care if the program does not exist
1855 end;
1856 end loop;
1857
1858 -- Remove stages
1859 DELETE from fnd_stage_fn_parameter_values
1860 where set_application_id = set_appl_id
1861 and request_set_id = set_id;
1862
1863 DELETE FROM fnd_request_set_stages_tl
1864 WHERE set_application_id = set_appl_id
1865 AND request_set_id = set_id;
1866
1867 DELETE FROM fnd_request_set_stages
1868 WHERE set_application_id = set_appl_id
1869 AND request_set_id = set_id;
1870
1871 -- Remove the set
1872
1873 DELETE FROM fnd_request_sets_tl
1874 WHERE application_id = set_appl_id
1875 AND request_set_id = set_id;
1876
1877 DELETE FROM fnd_request_sets
1878 WHERE application_id = set_appl_id
1879 AND request_set_id = set_id;
1880
1881 END delete_set;
1882
1883
1884
1885 -- Procedure
1886 -- DELETE_INCOMPATIBILITY
1887 --
1888 -- Purpose
1889 -- Delete a request set incompatibility rule.
1890 --
1891 -- Arguments
1892 -- request_set - Short name of the request set.
1893 -- application - Application short name of the request set.
1894 -- stage - Stage short name (for stage incompatibility).
1895 -- inc_prog_short_name - Short name of the incompatible program.
1896 -- inc_prog_application- Application of the incompatible program.
1897 -- inc_request_set - Sort name of the incompatible reuqest set.
1898 -- inc_set_application - Application short name of the incompatible set.
1899 -- inc_stage - Stage short name to the incompatible stage.
1900 --
1901 -- See examples from fnd_set.incompatibility() for argument usage.
1902 --
1903 PROCEDURE delete_incompatibility(request_set IN VARCHAR2,
1904 application IN VARCHAR2,
1905 stage IN VARCHAR2 DEFAULT NULL,
1906 inc_prog IN VARCHAR2 DEFAULT NULL,
1907 inc_prog_application IN VARCHAR2 DEFAULT NULL,
1908 inc_request_set IN VARCHAR2 DEFAULT NULL,
1909 inc_set_application IN VARCHAR2 DEFAULT NULL,
1910 inc_stage IN VARCHAR2 DEFAULT NULL)
1911 IS
1912 application_id_i fnd_application.application_id%TYPE;
1913 concurrent_program_id_i
1914 fnd_concurrent_programs.concurrent_program_id%TYPE;
1915 to_run_concurrent_program_id_i
1916 fnd_concurrent_programs.concurrent_program_id%TYPE;
1917 to_run_application_id_i fnd_application.application_id%TYPE;
1918 dummy varchar2(1);
1919 set_id number;
1920 stage_id number;
1921 BEGIN
1922 message_init;
1923
1924 check_notnull(request_set);
1925 check_notnull(application);
1926
1927 -- Get ID of first program
1928 application_id_i := application_id_f(application);
1929 set_id := request_set_id_f(application_id_i, request_set);
1930 if (stage is not null) then
1931 stage_id := request_set_stage_id_f(application_id_i, set_id,
1932 stage);
1933 else
1934 stage_id := null;
1935 end if;
1936 concurrent_program_id_i := get_prog_id(application_id_i, set_id, stage_id);
1937
1938 -- Get ID of second program
1939 if (inc_prog_application is not null) then /* incompatible PROGRAM */
1940 to_run_application_id_i :=
1941 application_id_f(inc_prog_application);
1942 to_run_concurrent_program_id_i :=
1943 concurrent_program_id_f(to_run_application_id_i, inc_prog);
1944 else /* incompatible SET or STAGE */
1945 to_run_application_id_i :=
1946 application_id_f(inc_set_application);
1947 set_id := request_set_id_f(to_run_application_id_i, inc_request_set);
1948 if (inc_stage is not null) then
1949 stage_id := request_set_stage_id_f(to_run_application_id_i, set_id,
1950 inc_stage);
1951 else
1952 stage_id := null;
1953 end if;
1954 to_run_concurrent_program_id_i := get_prog_id(to_run_application_id_i,
1955 set_id, stage_id);
1956 end if;
1957
1958 delete from fnd_concurrent_program_serial
1959 where to_run_application_id = to_run_application_id_i
1960 and to_run_concurrent_program_id = to_run_concurrent_program_id_i
1961 and running_application_id = application_id_i
1962 and running_concurrent_program_id = concurrent_program_id_i;
1963
1964 delete from fnd_concurrent_program_serial
1965 where to_run_application_id = application_id_i
1966 and to_run_concurrent_program_id = concurrent_program_id_i
1967 and running_application_id = to_run_application_id_i
1968 and running_concurrent_program_id = to_run_concurrent_program_id_i;
1969
1970 /* update incompatibilies */
1971 BEGIN
1972 update fnd_concurrent_programs
1973 set queue_method_code = 'I'
1974 WHERE application_id = application_id_i
1975 AND concurrent_program_id = concurrent_program_id_i
1976 AND NOT EXISTS
1977 (select 'x'
1978 from fnd_concurrent_program_serial
1979 where running_application_id = application_id_i
1980 and running_concurrent_program_id =
1981 concurrent_program_id_i);
1982 EXCEPTION
1983 when no_data_found then null;
1984 END;
1985
1986 BEGIN
1987 update fnd_concurrent_programs
1988 set queue_method_code = 'I'
1989 WHERE application_id = to_run_application_id_i
1990 AND concurrent_program_id = to_run_concurrent_program_id_i
1991 AND NOT EXISTS
1992 (select 'x'
1993 from fnd_concurrent_program_serial
1994 where running_application_id = to_run_application_id_i
1995 and running_concurrent_program_id =
1996 to_run_concurrent_program_id_i);
1997 EXCEPTION
1998 when no_data_found then null;
1999 END;
2000
2001 END delete_incompatibility;
2002
2003
2004
2005 -- Function
2006 -- INCOMPATIBILITY_EXISTS
2007 --
2008 -- Purpose
2009 -- Return TRUE if an incompatibility exists.
2010 --
2011 -- Arguments
2012 -- request_set - Short name of the request set.
2013 -- application - Application short name of the request set.
2014 -- stage - Stage short name (for stage incompatibility).
2015 -- inc_prog_short_name - Short name of the incompatible program.
2016 -- inc_prog_application- Application of the incompatible program.
2017 -- inc_request_set - Sort name of the incompatible reuqest set.
2018 -- inc_set_application - Application short name of the incompatible set.
2019 -- inc_stage - Stageshort name to the incompatible stage.
2020 --
2021 -- See examples from fnd_set.incompatibility() for argument usage.
2022 --
2023 FUNCTION incompatibility_exists(request_set IN VARCHAR2,
2024 application IN VARCHAR2,
2025 stage IN VARCHAR2 DEFAULT NULL,
2026 inc_prog IN VARCHAR2 DEFAULT NULL,
2027 inc_prog_application IN VARCHAR2 DEFAULT NULL,
2028 inc_request_set IN VARCHAR2 DEFAULT NULL,
2029 inc_set_application IN VARCHAR2 DEFAULT NULL,
2030 inc_stage IN VARCHAR2 DEFAULT NULL)
2031 return boolean is
2032
2033 application_id_i fnd_application.application_id%TYPE;
2034 concurrent_program_id_i fnd_concurrent_programs.concurrent_program_id%TYPE;
2035 to_run_concurrent_program_id_i
2036 fnd_concurrent_programs.concurrent_program_id%TYPE;
2037 to_run_application_id_i fnd_application.application_id%TYPE;
2038 dummy varchar2(1);
2039 set_id number;
2040 stage_id number;
2041
2042 BEGIN
2043 message_init;
2044
2045 -- Get ID of first program
2046 application_id_i := application_id_f(application);
2047 set_id := request_set_id_f(application_id_i, request_set);
2048 if (stage is not null) then
2049 stage_id := request_set_stage_id_f(application_id_i, set_id,
2050 stage);
2051 else
2052 stage_id := null;
2053 end if;
2054 concurrent_program_id_i := get_prog_id(application_id_i, set_id, stage_id);
2055
2056 -- Get ID of second program
2057 if (inc_prog_application is not null) then /* incompatible PROGRAM */
2058 to_run_application_id_i :=
2059 application_id_f(inc_prog_application);
2060 to_run_concurrent_program_id_i :=
2061 concurrent_program_id_f(to_run_application_id_i, inc_prog);
2062 else /* incompatible SET or STAGE */
2063 to_run_application_id_i :=
2064 application_id_f(inc_set_application);
2065 set_id := request_set_id_f(to_run_application_id_i, inc_request_set);
2066 if (inc_stage is not null) then
2067 stage_id := request_set_stage_id_f(to_run_application_id_i, set_id,
2068 inc_stage);
2069 else
2070 stage_id := null;
2071 end if;
2072 to_run_concurrent_program_id_i := get_prog_id(to_run_application_id_i,
2073 set_id, stage_id);
2074 end if;
2075
2076 select 'Y'
2077 into dummy
2078 from fnd_concurrent_program_serial
2079 where to_run_application_id = to_run_application_id_i
2080 and to_run_concurrent_program_id = to_run_concurrent_program_id_i
2081 and running_application_id = application_id_i
2082 and running_concurrent_program_id = concurrent_program_id_i;
2083
2084 return TRUE;
2085
2086 EXCEPTION
2087 when others then
2088 return FALSE;
2089 END incompatibility_exists;
2090
2091
2092
2093 -- Function
2094 -- REQUEST_SET_EXISTS
2095 --
2096 -- Purpose
2097 -- Returns TRUE if a request set exists.
2098 --
2099 -- Arguments
2100 -- request_set - Short name of the set.
2101 -- application - Application short name of the request set.
2102 --
2103 FUNCTION request_set_exists(request_set IN VARCHAR2,
2104 application IN VARCHAR2)
2105 RETURN BOOLEAN is
2106 set_application_id fnd_application.application_id%TYPE;
2107 set_id fnd_request_sets.request_set_id%TYPE;
2108
2109 BEGIN
2110 message_init;
2111
2112 set_application_id := application_id_f(application);
2113 set_id := request_set_id_f(set_application_id, request_set);
2114
2115 return TRUE;
2116 EXCEPTION
2117 when others then
2118 return FALSE;
2119 END request_set_exists;
2120
2121
2122
2123
2124 -- Function
2125 -- STAGE_IN_SET
2126 --
2127 -- Purpose
2128 -- Return TRUE if a stage is in a request set.
2129 --
2130 -- Arguments
2131 -- stage - Stage short name.
2132 -- request_set - Short name of request set.
2133 -- set_application - Application short name of the request set.
2134 -- program_sequence - Program sequence number.
2135 -- request_set_id - Optional. Overrides parameter 'request_set'.
2136 --
2137 FUNCTION stage_in_set(stage IN VARCHAR2,
2138 request_set IN VARCHAR2,
2139 set_application IN VARCHAR2)
2140 RETURN BOOLEAN is
2141
2142 set_appl_id fnd_application.application_id%TYPE;
2143 set_id fnd_request_sets.request_set_id%TYPE;
2144 dummy varchar2(1);
2145
2146 BEGIN
2147 message_init;
2148
2149 set_appl_id := application_id_f(set_application);
2150 set_id := request_set_id_f(set_appl_id, request_set);
2151
2152 select 'y'
2153 into dummy
2154 from fnd_request_set_stages f
2155 where set_application_id = set_appl_id
2156 and request_set_id = set_id
2157 and f.stage_name = stage;
2158
2159 return TRUE;
2160 EXCEPTION
2161 when others then
2162 return FALSE;
2163 END stage_in_set;
2164
2165
2166
2167 -- Function
2168 -- PROGRAM_IN_STAGE
2169 --
2170 -- Purpose
2171 -- Return TRUE if a program is in a request set stage.
2172 --
2173 -- Arguments
2174 -- program - Short name of the program.
2175 -- program_application - Application short name of the program.
2176 -- request_set - Short name of request set.
2177 -- set_application - Application short name of the request set.
2178 -- stage - Stage short name.
2179 -- program_sequence - Program sequence number.
2180 --
2181 FUNCTION program_in_stage(program IN VARCHAR2,
2182 program_application IN VARCHAR2,
2183 request_set IN VARCHAR2,
2184 set_application IN VARCHAR2,
2185 stage IN VARCHAR2,
2186 program_sequence IN NUMBER)
2187 RETURN BOOLEAN is
2188
2189 set_appl_id fnd_application.application_id%TYPE;
2190 program_appl_id fnd_application.application_id%TYPE;
2191 set_id fnd_request_sets.request_set_id%TYPE;
2192 stage_id fnd_request_set_stages.request_set_stage_id%TYPE;
2193 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
2194 dummy varchar2(1);
2195
2196 BEGIN
2197 message_init;
2198
2199 program_appl_id := application_id_f(program_application);
2200 set_appl_id := application_id_f(set_application);
2201 program_id := concurrent_program_id_f(program_appl_id, program);
2202 set_id := request_set_id_f(set_appl_id, request_set);
2203 stage_id := request_set_stage_id_f(set_appl_id, set_id, stage);
2204
2205 select 'y'
2206 into dummy
2207 from fnd_request_set_programs
2208 where set_application_id = set_appl_id
2209 and request_set_id = set_id
2210 and request_set_stage_id = stage_id
2211 and program_application_id = program_appl_id
2212 and concurrent_program_id = program_id
2213 and sequence = program_sequence;
2214
2215 return TRUE;
2216 EXCEPTION
2217 when others then
2218 return FALSE;
2219 END program_in_stage;
2220
2221
2222
2223
2224 -- Function
2225 -- PROGRAM_PARAMETER_EXISTS
2226 --
2227 -- Purpose
2228 -- Return TRUE if a parameter has been registered for a request set.
2229 --
2230 -- Arguments
2231 -- program - Short name of the program.
2232 -- program_application - Application short name of the program.
2233 -- request_set - Short name of request set.
2234 -- set_application - Application short name of the request set.
2235 -- stage - Stage short name.
2236 -- program_sequence - Program sequence number.
2237 -- parameter - Name of the program parameter. (NOT the prompt!)
2238 --
2239 FUNCTION program_parameter_exists(program IN VARCHAR2,
2240 program_application IN VARCHAR2,
2241 request_set IN VARCHAR2,
2242 set_application IN VARCHAR2,
2243 stage IN VARCHAR2,
2244 program_sequence IN NUMBER,
2245 parameter IN VARCHAR2)
2246 RETURN BOOLEAN is
2247
2248 set_id fnd_request_sets.request_set_id%TYPE;
2249 stage_id fnd_request_set_stages.request_set_stage_id%TYPE;
2250 program_appl_id fnd_application.application_id%TYPE;
2251 set_appl_id fnd_application.application_id%TYPE;
2252 set_program_id fnd_request_set_program_args.request_set_program_id%TYPE;
2253 dummy varchar2(1);
2254
2255 BEGIN
2256 message_init;
2257
2258 set_appl_id := application_id_f(set_application);
2259 program_appl_id := application_id_f(program_application);
2260
2261 set_id := request_set_id_f(set_appl_id, request_set);
2262 stage_id := request_set_stage_id_f(set_appl_id, set_id, stage);
2263
2264 select request_set_program_id
2265 into set_program_id
2266 from fnd_request_set_programs s,
2267 fnd_concurrent_programs p
2268 where p.concurrent_program_name = program
2269 and p.application_id = program_appl_id
2270 and p.concurrent_program_id = s.concurrent_program_id
2271 and p.application_id = s.program_application_id
2272 and s.request_set_id = set_id
2273 and s.request_set_stage_id = stage_id
2274 and s.set_application_id = set_appl_id
2275 and s.sequence = program_sequence;
2276
2277 select 'y'
2278 into dummy
2279 from fnd_request_set_program_args a
2280 where a.application_id = set_appl_id
2281 and a.request_set_id = set_id
2282 and a.request_set_program_id = set_program_id
2283 and (a.descriptive_flex_appl_id,
2284 a.descriptive_flexfield_name,
2285 a.application_column_name) in
2286 (select u.application_id,
2287 u.descriptive_flexfield_name,
2288 u.application_column_name
2289 from fnd_descr_flex_column_usages u
2290 where u.application_id = program_appl_id
2291 and u.descriptive_flexfield_name = '$SRS$.'||program
2292 and u.descriptive_flex_context_code = 'Global Data Elements'
2293 and u.end_user_column_name = parameter);
2294
2295 return TRUE;
2296 EXCEPTION
2297 when others then
2298 return FALSE;
2299 END program_parameter_exists;
2300
2301
2302
2303
2304 -- Function
2305 -- SET_IN_GROUP
2306 --
2307 -- Purpose
2308 -- Return TRUE if a request set is in a request group.
2309 --
2310 -- Arguments
2311 -- request_set - Short name of set.
2312 -- set_application - Application short name of the set.
2313 -- request_group - Name of request group.
2314 -- group_application - Application of the request group.
2315 --
2316 FUNCTION set_in_group(request_set IN VARCHAR2 DEFAULT NULL,
2317 set_application IN VARCHAR2,
2318 request_group IN VARCHAR2,
2319 group_application IN VARCHAR2)
2320 RETURN BOOLEAN is
2321
2322 set_application_id fnd_application.application_id%TYPE;
2323 group_application_id fnd_application.application_id%TYPE;
2324 set_id fnd_request_sets.request_set_id%TYPE;
2325 group_id fnd_request_groups.request_group_id%TYPE;
2326 dummy varchar2(1);
2327
2328 BEGIN
2329 message_init;
2330
2331 set_application_id := application_id_f(set_application);
2332 group_application_id := application_id_f(group_application);
2333
2334 set_id := request_set_id_f(set_application_id, request_set);
2335 group_id := request_group_id_f(group_application_id, request_group);
2336
2337 select 'y'
2338 into dummy
2339 from fnd_request_group_units u
2340 where application_id = group_application_id
2341 and request_group_id = group_id
2342 and request_unit_type = 'S'
2343 and unit_application_id = set_application_id
2344 and request_unit_id = set_id;
2345
2346 return TRUE;
2347 EXCEPTION
2348 when others then
2349 return FALSE;
2350 END set_in_group;
2351
2352
2353 -- Procedure
2354 -- STAGE_FUNCTION
2355 --
2356 -- Purpose
2357 -- Register a request set stage function.
2358 --
2359 -- Arguments
2360 -- function_name - Name of function. (e.g. 'My Function')
2361 -- application - Short name of function's application.
2362 -- (e.g. 'FND')
2363 -- short_name - Short (non-translated) name of the function.
2364 -- description - Optional description of the function.
2365 -- plsql_name - Name of pl/sql stored function.
2366 -- icon_name - For future web interface. Use null for now.
2367 -- language_code - Language code for the name and description.
2368 -- (e.g. 'US')
2369 --
2370 PROCEDURE stage_function(function_name IN VARCHAR2,
2371 application IN VARCHAR2,
2372 short_name IN VARCHAR2,
2373 description IN VARCHAR2 DEFAULT NULL,
2374 plsql_name IN VARCHAR2,
2375 icon_name IN VARCHAR2 DEFAULT NULL,
2376 language_code IN VARCHAR2 DEFAULT 'US')
2377 is
2378 application_id number;
2379 executable_id number;
2380 last_update_date fnd_executables.last_update_date%TYPE;
2381 last_updated_by fnd_executables.last_updated_by%TYPE;
2382 creation_date fnd_executables.creation_date%TYPE;
2383 created_by fnd_executables.created_by%TYPE;
2384 last_update_login fnd_executables.last_update_login%TYPE;
2385 row_id fnd_executables_vl.row_id%type;
2386
2387 begin
2388 message_init;
2389
2390 check_notnull(function_name);
2391 check_notnull(short_name);
2392 check_notnull(application);
2393 check_notnull(plsql_name);
2394
2395 last_update_date := last_update_date_f;
2396 last_updated_by := last_updated_by_f;
2397 creation_date := creation_date_f;
2398 created_by := created_by_f;
2399 last_update_login := last_update_login_f;
2400 application_id := application_id_f(application);
2401
2402 select fnd_executables_s.nextval into executable_id from sys.dual;
2403
2404 insert into FND_EXECUTABLES (
2405 APPLICATION_ID, EXECUTABLE_ID, EXECUTABLE_NAME,
2406 EXECUTION_METHOD_CODE, EXECUTION_FILE_NAME, SUBROUTINE_NAME,
2407 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
2408 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, icon_name
2409 ) values (
2410 application_id, executable_id, short_name,
2411 'B', plsql_name, null,
2412 creation_date, created_by, last_update_date,
2413 last_updated_by, last_update_login, icon_name
2414 );
2415
2416 insert into FND_EXECUTABLES_TL (
2417 APPLICATION_ID, EXECUTABLE_ID, CREATION_DATE,
2418 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
2419 LAST_UPDATE_LOGIN, USER_EXECUTABLE_NAME, DESCRIPTION,
2420 LANGUAGE, SOURCE_LANG
2421 ) select
2422 stage_function.application_id, executable_id,
2423 stage_function.creation_date,
2424 stage_function.created_by, stage_function.last_update_date,
2425 stage_function.last_updated_by,
2426 stage_function.last_update_login, function_name,
2427 stage_function.description,
2428 l.language_code, stage_function.language_code
2429 from FND_LANGUAGES L
2430 where L.INSTALLED_FLAG in ('I', 'B');
2431
2432 println('created function for:'|| short_name);
2433
2434 end stage_function;
2435
2436
2437
2438 -- Function
2439 -- FUNCTION_EXISTS
2440 --
2441 -- Purpose
2442 -- Return TRUE if a stage function exists.
2443 --
2444 -- Arguments
2445 -- function_short_name - Short name of the function.
2446 -- application - Application short name of the function.
2447 --
2448 FUNCTION function_exists(function_short_name IN VARCHAR2,
2449 application IN VARCHAR2) RETURN BOOLEAN is
2450 exec_application_id fnd_application.application_id%TYPE;
2451 dummy varchar2(1);
2452
2453 begin
2454 message_init;
2455
2456 exec_application_id := application_id_f(application);
2457
2458 select 'y'
2459 into dummy
2460 from fnd_executables
2461 where application_id = exec_application_id
2462 and executable_name = function_short_name;
2463
2464 return TRUE;
2465
2466 EXCEPTION
2467 when others then
2468 return FALSE;
2469 end function_exists;
2470
2471
2472 -- Function
2473 -- DELETE_FUNCTION
2474 --
2475 -- Purpose
2476 -- Delete a stage function.
2477 --
2478 -- Arguments
2479 -- function_short_name - Short name of the function.
2480 -- application - Application short name of the function.
2481 --
2482 PROCEDURE delete_function(function_short_name IN VARCHAR2,
2483 application IN VARCHAR2) is
2484 exec_application_id
2485 fnd_application.application_id%TYPE;
2486 exec_id
2487 fnd_executables.executable_id%TYPE;
2488 dummy varchar2(1);
2489
2490 begin
2491 message_init;
2492
2493 check_notnull(function_short_name);
2494 check_notnull(application);
2495
2496 exec_application_id := application_id_f(application);
2497
2498 BEGIN
2499 SELECT executable_id
2500 INTO exec_id
2501 FROM fnd_executables
2502 WHERE application_id = exec_application_id
2503 AND executable_name = function_short_name;
2504 EXCEPTION
2505 WHEN no_data_found THEN
2506 println('Could not find executable: '||function_short_name);
2507 return;
2508 END;
2509
2510
2511 -- Can't delete an executable if it is in use
2512 BEGIN
2513 select 'x' into dummy
2514 from sys.dual
2515 where not exists
2516 (select 1
2517 from fnd_request_set_stages
2518 where function_application_id = exec_application_id
2519 and function_id = exec_id);
2520 EXCEPTION
2521 when no_data_found then
2522 message('Error - function is assigned to a stage.');
2523 RAISE bad_parameter;
2524 END;
2525
2526 delete from fnd_stage_fn_parameters_tl
2527 where function_id = exec_id
2528 and application_id = exec_application_id;
2529
2530 delete from fnd_executables_tl
2531 where executable_id = exec_id
2532 and application_id = exec_application_id;
2533
2534 delete from fnd_executables
2535 where executable_id = exec_id
2536 and application_id = exec_application_id;
2537
2538 delete from fnd_request_group_units
2539 where request_unit_id = exec_id
2540 and unit_application_id = exec_application_id
2541 and request_unit_type = 'F';
2542
2543 end delete_function;
2544
2545
2546 -- Function
2547 -- FUNCTION_IN_GROUP
2548 --
2549 -- Purpose
2550 -- Return TRUE if a stage is in a request group.
2551 --
2552 -- Arguments
2553 -- function_short_name - Short name of set.
2554 -- function_application - Application short name of the function.
2555 -- request_group - Name of request group.
2556 -- group_application - Application of the request group.
2557 --
2558 FUNCTION function_in_group(function_short_name IN VARCHAR2,
2559 function_application IN VARCHAR2,
2560 request_group IN VARCHAR2,
2561 group_application IN VARCHAR2)
2562 RETURN BOOLEAN is
2563 fn_application_id fnd_application.application_id%TYPE;
2564 group_application_id fnd_application.application_id%TYPE;
2565 fn_id number;
2566 group_id fnd_request_groups.request_group_id%TYPE;
2567 dummy varchar2(1);
2568
2569 begin
2570 message_init;
2571
2572 fn_application_id := application_id_f(function_application);
2573 group_application_id := application_id_f(group_application);
2574
2575 fn_id := function_id_f(fn_application_id, function_short_name);
2576 group_id := request_group_id_f(group_application_id, request_group);
2577
2578 select 'y'
2579 into dummy
2580 from fnd_request_group_units u
2581 where application_id = group_application_id
2582 and request_group_id = group_id
2583 and request_unit_type = 'F'
2584 and unit_application_id = fn_application_id
2585 and request_unit_id = fn_id;
2586
2587 return TRUE;
2588 exception
2589 when others then
2590 return FALSE;
2591 end function_in_group;
2592
2593
2594 -- Function
2595 -- ADD_FUNCTION_TO_GROUP
2596 --
2597 -- Purpose
2598 -- Adds a stage function to a request_group.
2599 --
2600 -- Arguments
2601 -- function_short_name - Short name of set.
2602 -- function_application - Application short name of the function.
2603 -- request_group - Name of request group.
2604 -- group_application - Application of the request group.
2605 --
2606 procedure add_function_to_group(function_short_name IN VARCHAR2,
2607 function_application IN VARCHAR2,
2608 request_group IN VARCHAR2,
2609 group_application IN VARCHAR2) is
2610 last_update_login fnd_request_groups.last_update_login%TYPE
2611 := last_update_login_f;
2612 last_update_date fnd_request_groups.last_update_date%TYPE
2613 := last_update_date_f;
2614 last_updated_by fnd_request_groups.last_updated_by%TYPE
2615 := last_updated_by_f;
2616 creation_date fnd_request_groups.creation_date%TYPE
2617 := creation_date_f;
2618 created_by fnd_request_groups.created_by%TYPE
2619 := created_by_f;
2620 fn_application_id
2621 fnd_application.application_id%TYPE;
2622 group_application_id
2623 fnd_application.application_id%TYPE;
2624 fn_id
2625 fnd_request_sets.request_set_id%TYPE;
2626 group_id
2627 fnd_request_groups.request_group_id%TYPE;
2628 begin
2629 message_init;
2630
2631 check_notnull(function_application);
2632 check_notnull(function_short_name);
2633 check_notnull(request_group);
2634 check_notnull(group_application);
2635
2636 fn_application_id := application_id_f(function_application);
2637 group_application_id := application_id_f(group_application);
2638
2639 fn_id := function_id_f(fn_application_id, function_short_name);
2640 group_id :=
2641 request_group_id_f(group_application_id, request_group);
2642
2643 insert into Fnd_Request_Group_Units( APPLICATION_ID,
2644 REQUEST_GROUP_ID,
2645 REQUEST_UNIT_TYPE,
2646 UNIT_APPLICATION_ID,
2647 REQUEST_UNIT_ID,
2648 LAST_UPDATE_DATE,
2649 LAST_UPDATED_BY,
2650 CREATION_DATE,
2651 CREATED_BY,
2652 LAST_UPDATE_LOGIN)
2653 values( group_application_id,
2654 group_id,
2655 'F',
2656 fn_application_id,
2657 fn_id,
2658 last_update_date,
2659 last_updated_by,
2660 creation_date,
2661 created_by,
2662 last_update_login);
2663
2664 println('Added function '|| function_short_name || ' to group '
2665 ||request_group);
2666 end;
2667
2668
2669 -- Function
2670 -- REMOVE_FUNCTION_FROM_GROUP
2671 --
2672 -- Purpose
2673 -- Removes a stage function from a request_group.
2674 --
2675 -- Arguments
2676 -- function_short_name - Short name of set.
2677 -- function_application - Application short name of the function.
2678 -- request_group - Name of request group.
2679 -- group_application - Application of the request group.
2680 --
2681 procedure remove_function_from_group(function_short_name IN VARCHAR2,
2682 function_application IN VARCHAR2,
2683 request_group IN VARCHAR2,
2684 group_application IN VARCHAR2) is
2685 fn_application_id
2686 fnd_application.application_id%TYPE;
2687 group_application_id
2688 fnd_application.application_id%TYPE;
2689 fn_id number;
2690 group_id
2691 fnd_request_groups.request_group_id%TYPE;
2692 begin
2693 message_init;
2694
2695 check_notnull(function_application);
2696 check_notnull(function_short_name);
2697 check_notnull(request_group);
2698 check_notnull(group_application);
2699
2700 fn_application_id := application_id_f(function_application);
2701 group_application_id := application_id_f(group_application);
2702
2703 fn_id := function_id_f(fn_application_id, function_short_name);
2704 group_id :=
2705 request_group_id_f(group_application_id, request_group);
2706
2707 delete from fnd_request_group_units u
2708 where application_id = group_application_id
2709 and request_group_id = group_id
2710 and request_unit_type = 'F'
2711 and unit_application_id = fn_application_id
2712 and request_unit_id = fn_id;
2713 end;
2714
2715
2716 -- Procedure
2717 -- FUNCTION_PARAMETER
2718 --
2719 -- Purpose
2720 -- Register a request set stage function parameter.
2721 --
2722 -- Arguments
2723 -- function_short_name - Short (non-translated) name of the function.
2724 -- application - Short name of function's application.
2725 -- (e.g. 'FND')
2726 -- paramter_name - Displayed name of parameter.
2727 -- parameter_short_name - Short (non-translated) name of parameter.
2728 -- description - Optional description of the function.
2729 -- language_code - Language code for the name and description.
2730 -- (e.g. 'US')
2731 --
2732 PROCEDURE function_parameter(function_short_name IN VARCHAR2,
2733 application IN VARCHAR2,
2734 parameter_name IN VARCHAR2,
2735 parameter_short_name IN VARCHAR2,
2736 description IN VARCHAR2 DEFAULT NULL,
2737 language_code IN VARCHAR2 DEFAULT 'US')
2738 is
2739 last_update_date fnd_executables.last_update_date%TYPE;
2740 last_updated_by fnd_executables.last_updated_by%TYPE;
2741 creation_date fnd_executables.creation_date%TYPE;
2742 created_by fnd_executables.created_by%TYPE;
2743 last_update_login fnd_executables.last_update_login%TYPE;
2744 row_id fnd_executables_vl.row_id%type;
2745 fn_appl_id fnd_application.application_id%TYPE;
2746 fn_id number;
2747 param_id number;
2748 begin
2749 message_init;
2750
2751 check_notnull(function_short_name);
2752 check_notnull(application);
2753 check_notnull(parameter_name);
2754 check_notnull(parameter_short_name);
2755
2756 last_update_date := last_update_date_f;
2757 last_updated_by := last_updated_by_f;
2758 creation_date := creation_date_f;
2759 created_by := created_by_f;
2760 last_update_login := last_update_login_f;
2761 fn_appl_id := application_id_f(application);
2762 fn_id := function_id_f(fn_appl_id, function_short_name);
2763
2764 select fnd_stage_fn_parameters_s.nextval into param_id from sys.dual;
2765
2766 insert into FND_STAGE_FN_PARAMETERS_TL (
2767 APPLICATION_ID, FUNCTION_ID, PARAMETER_ID, CREATION_DATE,
2768 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
2769 LAST_UPDATE_LOGIN, PARAMETER_NAME, USER_PARAMETER_NAME, DESCRIPTION,
2770 LANGUAGE, SOURCE_LANG
2771 ) select
2772 fn_appl_id,fn_id, param_id, function_parameter.creation_date,
2773 function_parameter.created_by, function_parameter.last_update_date,
2774 function_parameter.last_updated_by,
2775 function_parameter.last_update_login, parameter_short_name,
2776 parameter_name, function_parameter.description,
2777 l.language_code, function_parameter.language_code
2778 from FND_LANGUAGES L
2779 where L.INSTALLED_FLAG in ('I', 'B');
2780
2781 println('created function for:'|| function_short_name);
2782 end;
2783
2784
2785 -- Function
2786 -- FUNCTION_PARAMETER_EXISTS
2787 --
2788 -- Purpose
2789 -- Return TRUE if a stage function parameter exists.
2790 --
2791 -- Arguments
2792 -- function_short_name - Short name of the function.
2793 -- application - Application short name of the function.
2794 -- parameter - Short (non-translated) name of parameter.
2795 --
2796 FUNCTION function_parameter_exists(function_short_name IN VARCHAR2,
2797 application IN VARCHAR2,
2798 parameter IN VARCHAR2)
2799 RETURN BOOLEAN is
2800 fn_appl_id fnd_application.application_id%TYPE;
2801 fn_id number;
2802 dummy varchar2(1);
2803
2804 begin
2805 message_init;
2806
2807 fn_appl_id := application_id_f(application);
2808 fn_id := function_id_f(fn_appl_id, function_short_name);
2809
2810 select 'y'
2811 into dummy
2812 from fnd_stage_fn_parameters_vl
2813 where application_id = fn_appl_id
2814 and function_id = fn_id
2815 and parameter_name = parameter;
2816
2817 return TRUE;
2818
2819 EXCEPTION
2820 when others then
2821 return FALSE;
2822 end;
2823
2824
2825 -- Function
2826 -- DELETE_FUNCTION_PARAMETER
2827 --
2828 -- Purpose
2829 -- Delete a stage function parameter.
2830 --
2831 -- Arguments
2832 -- function_short_name - Short name of the function.
2833 -- application - Application short name of the function.
2834 -- parameter - Short (non-translated) name of parameter.
2835 --
2836 PROCEDURE delete_function_parameter(function_short_name IN VARCHAR2,
2837 application IN VARCHAR2,
2838 parameter IN VARCHAR2) is
2839 fn_appl_id fnd_application.application_id%TYPE;
2840 fn_id number;
2841 param_id number;
2842 dummy varchar2(1);
2843
2844 begin
2845 message_init;
2846
2847 check_notnull(function_short_name);
2848 check_notnull(application);
2849 check_notnull(parameter);
2850
2851 fn_appl_id := application_id_f(application);
2852 fn_id := function_id_f(fn_appl_id, function_short_name);
2853
2854 begin
2855 select parameter_id
2856 into param_id
2857 from fnd_stage_fn_parameters_vl
2858 where application_id = fn_appl_id
2859 and function_id = fn_id
2860 and parameter_name = parameter;
2861 exception
2862 when no_data_found then
2863 println('Could not find parameter: '||parameter);
2864 return;
2865 end;
2866
2867
2868 delete from fnd_stage_fn_parameters_tl
2869 where function_id = fn_id
2870 and application_id = fn_appl_id
2871 and parameter_id = param_id;
2872
2873 delete from fnd_stage_fn_parameter_values
2874 where function_id = fn_id
2875 and function_application_id = fn_appl_id
2876 and parameter_id = param_id;
2877
2878
2879 end delete_function_parameter;
2880
2881
2882 -- Function
2883 -- FUNCTION_PARAMETER_VALUE
2884 --
2885 -- Purpose
2886 -- Sets the value of a stage function parameter for a given stage.
2887 --
2888 -- Arguments
2889 -- request_set - Short name of the request set.
2890 -- set_application - Application short name of the set.
2891 -- stage_short_name - Short name of stage.
2892 -- parameter - Short name of parameter.
2893 -- value - Value to which the paraemter is to be set.
2894 --
2895 PROCEDURE function_parameter_value(request_set IN VARCHAR2,
2896 set_application IN VARCHAR2,
2897 stage IN VARCHAR2,
2898 parameter IN VARCHAR2,
2899 value IN VARCHAR2) is
2900 last_update_date fnd_executables.last_update_date%TYPE;
2901 last_updated_by fnd_executables.last_updated_by%TYPE;
2902 creation_date fnd_executables.creation_date%TYPE;
2903 created_by fnd_executables.created_by%TYPE;
2904 last_update_login fnd_executables.last_update_login%TYPE;
2905 row_id fnd_executables_vl.row_id%type;
2906 stage_id number;
2907 param_id number;
2908 set_id number;
2909 set_appl_id number;
2910 fn_id number;
2911 fn_appl_id number;
2912 begin
2913 message_init;
2914
2915 check_notnull(request_set);
2916 check_notnull(set_application);
2917 check_notnull(stage);
2918 check_notnull(parameter);
2919
2920 last_update_date := last_update_date_f;
2921 last_updated_by := last_updated_by_f;
2922 creation_date := creation_date_f;
2923 created_by := created_by_f;
2924 last_update_login := last_update_login_f;
2925 set_appl_id := application_id_f(set_application);
2926 set_id := request_set_id_f(set_appl_id, request_set);
2927 stage_id := request_set_stage_id_f(set_appl_id, set_id, stage);
2928
2929 begin
2930 select s.function_id, s.function_application_id, p.parameter_id
2931 into fn_id, fn_appl_id, param_id
2932 from fnd_request_set_stages s, fnd_stage_fn_parameters_vl p
2933 where s.request_set_id = set_id
2934 and s.set_application_id = set_appl_id
2935 and s.request_set_stage_id = stage_id
2936 and s.function_id = p.function_id
2937 and s.function_application_id = p.application_id
2938 and p.parameter_name = parameter;
2939 exception
2940 when no_data_found then
2941 println('Could not find parameter "'||parameter||'" in stage.' );
2942 return;
2943 end;
2944
2945
2946
2947 insert into FND_STAGE_FN_PARAMETER_VALUES (
2948 SET_APPLICATION_ID, REQUEST_SET_ID, REQUEST_SET_STAGE_ID,
2949 FUNCTION_APPLICATION_ID, FUNCTION_ID,
2950 PARAMETER_ID, CREATION_DATE,
2951 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
2952 LAST_UPDATE_LOGIN, VALUE
2953 ) values
2954 (set_appl_id, set_id, stage_id, fn_appl_id, fn_id,
2955 param_id, function_parameter_value.creation_date,
2956 function_parameter_value.created_by,
2957 function_parameter_value.last_update_date,
2958 function_parameter_value.last_updated_by,
2959 function_parameter_value.last_update_login,
2960 function_parameter_value.value );
2961
2962 println('Added value');
2963 end;
2964 /* END_PUBLIC */
2965
2966
2967 -- Function
2968 -- RESTART_REQUEST_SET
2969 --
2970 -- Purpose
2971 -- Restarts an Request Set only if it was failed in the last run.
2972 --
2973 -- Arguments
2974 -- request_set_id - request_id of request set
2975 --
2976 -- Return
2977 -- returns true if request set can be restarted, otherwise false.
2978 --
2979 FUNCTION restart_request_set( request_set_id IN number) RETURN BOOLEAN IS
2980 requestId number;
2981 phasecode varchar2(1);
2982 statuscode varchar2(1);
2983 queuemethod varchar2(1);
2984 rset_id number;
2985 req_type varchar2(1);
2986 req_request_date date;
2987 rset_last_updated_date date;
2988 error_stage_id number;
2989 appl_id number;
2990 tmpDate date;
2991 pos number;
2992 pos2 number;
2993 req_data varchar2(30);
2994 current_run_number number;
2995 tmp_number number;
2996 BEGIN
2997 requestId := request_set_id;
2998 begin
2999 select phase_code, status_code, queue_method_code, request_type,
3000 REQUEST_DATE, to_number(argument2),to_number(argument1), req_information, RUN_NUMBER
3001 into phasecode, statuscode, queuemethod, req_type,
3002 req_request_date, rset_id, appl_id, req_data, current_run_number
3003 from fnd_concurrent_requests
3004 where request_id = requestId;
3005 exception
3006 when NO_DATA_FOUND then
3007 message('Invalid request id : ' || requestId);
3008 RAISE bad_parameter;
3009 end;
3010 BEGIN
3011 pos := instr(req_data, ',', 1, 1);
3012 pos2 := instr(req_data, ',', pos + 1, 1);
3013 error_stage_id := to_number(substr(req_data, pos + 1, pos2 - pos -1));
3014 select LAST_UPDATE_DATE into rset_last_updated_date from FND_REQUEST_SETS
3015 where REQUEST_SET_ID = rset_id AND application_id = appl_id;
3016 SELECT max(last_update_date) INTO tmpDate FROM fnd_request_set_stages
3017 WHERE request_set_id = rset_id AND set_application_id = appl_id;
3018 IF( tmpDate > rset_last_updated_date) THEN
3019 rset_last_updated_date := tmpDate;
3020 END IF;
3021 SELECT max(last_update_date) INTO tmpDate FROM fnd_request_set_programs
3022 WHERE request_set_id = rset_id AND set_application_id = appl_id;
3023 IF( tmpDate > rset_last_updated_date) THEN
3024 rset_last_updated_date := tmpDate;
3025 END IF;
3026 if( rset_last_updated_date > req_request_date ) then
3027 return false;
3028 end if;
3029 exception
3030 when no_data_found then
3031 NULL;
3032 end;
3033 /*check whether any errored out program got deleted */
3034 select count(r.request_set_program_id) INTO tmp_number
3035 from fnd_run_requests r,
3036 fnd_concurrent_requests fcr1, fnd_concurrent_requests fcr2
3037 where r.parent_request_id = requestId
3038 and fcr1.parent_request_id = fcr2.request_id
3039 and fcr1.concurrent_program_id = r.concurrent_program_id
3040 and r.request_id = fcr1.request_id
3041 and fcr1.status_code = 'E'
3042 and fcr2.parent_request_id = requestId
3043 and fcr2.run_number = current_run_number
3044 and error_stage_id = to_number(fcr2.argument3)
3045 and r.request_set_program_id IS NOT NULL
3046 AND r.request_set_program_id NOT IN
3047 (
3048 SELECT REQUEST_SET_PROGRAM_ID FROM FND_REQUEST_SET_PROGRAMS WHERE SET_APPLICATION_ID = appl_id
3049 AND REQUEST_SET_ID = rset_id AND REQUEST_SET_STAGE_ID = error_stage_id
3050 );
3051 if( tmp_number <> 0)then
3052 return false ;
3053 end if;
3054
3055 IF( not(req_type = 'M' and phasecode ='C' and statuscode = 'E') ) then
3056 return false;
3057 end if;
3058 if( queuemethod = 'B' ) then
3059 update fnd_concurrent_requests set phase_code = 'P', status_code = 'Q' where request_id = requestId;
3060 else if ( queuemethod = 'I' ) then
3061 update fnd_concurrent_requests set phase_code = 'P', status_code = 'I' where request_id = requestId;
3062 else
3063 return false;
3064 end if;
3065 end if;
3066 return true;
3067 END;
3068 END fnd_set;
3069 /* end package */