[Home] [Help]
PACKAGE BODY: APPS.FND_PROGRAM
Source
1 PACKAGE BODY fnd_program AS
2 /* $Header: AFCPRCPB.pls 115.23 2003/09/30 20:29:01 pferguso 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 -- internal_messages := internal_messages || Sqlerrm; /* error stamp */
37 END;
38
39 PROCEDURE message_init IS
40 BEGIN
41 internal_messages := '';
42 END;
43
44 /* START_PUBLIC */
45 FUNCTION message RETURN VARCHAR2 IS
46 BEGIN
47 RETURN internal_messages;
48 END;
49 /* END_PUBLIC */
50
51
52 /* print functions only used in testing */
53 /***********************************
54 * Uncomment dbms_output calls and upload to database to enable debugging
55 * commented to remove GSCC dated warnings
56 ***********************************/
57 PROCEDURE print(msg IN VARCHAR2) IS
58 BEGIN
59 IF(debug_mode_on) THEN
60 -- dbms_output.enable;
61 -- dbms_output.put(msg);
62 null;
63 END IF;
64 END;
65
66 PROCEDURE println IS
67 BEGIN
68 IF(debug_mode_on) THEN
69 -- dbms_output.enable;
70 -- dbms_output.put_line('');
71 null;
72 END IF;
73 END;
74
75 PROCEDURE println(msg IN VARCHAR2) IS
76 BEGIN
77 IF(debug_mode_on) THEN
78 -- dbms_output.enable;
79 -- dbms_output.put_line(msg);
80 null;
81 END IF;
82 END;
83
84
85 /* ------------------------------------------------------------ */
86 /* who information */
87 /* ------------------------------------------------------------ */
88
89 PROCEDURE set_session_mode(session_mode IN VARCHAR2) IS
90 BEGIN
91 IF(session_mode NOT IN ('customer_data', 'seed_data')) THEN
92 message('bad mode:'|| session_mode);
93 message('valid values are: customer_data, seed_data');
94 RAISE bad_parameter;
95 END IF;
96 who_mode := session_mode;
97 END;
98
99
100 FUNCTION customer_mode RETURN BOOLEAN IS
101 BEGIN
102 IF(who_mode = 'customer_data') THEN
103 RETURN TRUE;
104 ELSIF(who_mode = 'seed_data') THEN
105 RETURN FALSE;
106 ELSE
107 message('bad session mode:' || who_mode);
108 message('use set_session_mode to specify');
109 RAISE bad_parameter;
110 END IF;
111 END;
112
113
114 FUNCTION created_by_f RETURN NUMBER IS
115 BEGIN
116 IF(customer_mode) THEN
117 RETURN 0;
118 ELSE
119 RETURN 1;
120 END IF;
121 END;
122
123 FUNCTION creation_date_f RETURN DATE IS
124 BEGIN
125 IF(customer_mode) THEN
126 RETURN Sysdate;
127 ELSE
128 RETURN To_date('01011980', 'MMDDYYYY');
129 END IF;
130 END;
131
132 FUNCTION last_updated_by_f RETURN NUMBER IS
133 BEGIN
134 RETURN created_by_f;
135 END;
136
137 FUNCTION last_update_date_f RETURN DATE IS
138 BEGIN
139 RETURN creation_date_f;
140 END;
141
142 FUNCTION last_update_login_f RETURN NUMBER IS
143 BEGIN
144 return 0;
145 END;
146
147
148
149 /* ------------------------------------------------------------ */
150
151 /* get the application id, given the name (or short name) */
152 FUNCTION application_id_f(application_name_in IN VARCHAR2)
153 RETURN fnd_application.application_id%TYPE
154 IS
155 application_id_ret fnd_application.application_id%TYPE;
156 BEGIN
157 IF(application_name_in IS NULL) THEN
158 message('A null application_name was specified.');
159 RAISE bad_parameter;
160 END IF;
161 /* Check the short name first. */
162 SELECT application_id
163 INTO application_id_ret
164 FROM fnd_application
165 WHERE application_short_name = application_name_in;
166 RETURN application_id_ret;
167 EXCEPTION
168 WHEN no_data_found THEN /* Try the translated name. */
169 BEGIN
170 SELECT application_id
171 INTO application_id_ret
172 FROM fnd_application_tl
173 WHERE application_name = application_name_in
174 AND rownum = 1;
175 RETURN application_id_ret;
176 EXCEPTION
177 when NO_DATA_FOUND then
178 message('Invalid application name: ' || application_name_in);
179 RAISE bad_parameter;
180 END;
181 END;
182
183
184 /* Get the application short name, given the name (or short name).
185 * This is a kludge used because the CP and Flex APIs
186 * handle application_name and short_name parameters
187 * differently.
188 */
189 FUNCTION application_short_name_f(application_name_in IN VARCHAR2)
190 RETURN fnd_application.application_short_name%TYPE
191 IS
192 application_sn_ret fnd_application.application_short_name%TYPE;
193 BEGIN
194 IF(application_name_in IS NULL) THEN
195 message('A null application_name was specified.');
196 RAISE bad_parameter;
197 END IF;
198 /* Check the short name first. */
199 SELECT application_short_name
200 INTO application_sn_ret
201 FROM fnd_application
202 WHERE application_short_name = application_name_in;
203 RETURN application_sn_ret;
204 EXCEPTION
205 WHEN no_data_found THEN /* Try the translated name. */
206 BEGIN
207 SELECT application_short_name
208 INTO application_sn_ret
209 FROM fnd_application_tl t,
210 fnd_application b
211 WHERE t.application_name = application_name_in
212 AND rownum = 1
213 AND t.application_id = b.application_id;
214 RETURN application_sn_ret;
215 EXCEPTION
216 when NO_DATA_FOUND then
217 message('Invalid application name: ' || application_name_in);
218 RAISE bad_parameter;
219 END;
220 END;
221
222
223 /* Get a concurrent program ID given appl ID and short name */
224 FUNCTION concurrent_program_id_f(program_application_id number,
225 program_short_name varchar2) return number is
226 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
227 BEGIN
228 SELECT concurrent_program_id
229 INTO program_id
230 FROM fnd_concurrent_programs
231 WHERE application_id = program_application_id
232 AND concurrent_program_name = program_short_name;
233
234 RETURN program_id;
235
236 EXCEPTION
237 WHEN no_data_found THEN
238 message('Could not find program: '||program_short_name);
239 RAISE bad_parameter;
240 END;
241
242
243 /* Get a request group ID given name and appl ID */
244 FUNCTION request_group_id_f(group_application_id number,
245 request_group varchar2) return number is
246 group_id fnd_request_groups.request_group_id%TYPE;
247 BEGIN
248 SELECT request_group_id
249 INTO group_id
250 FROM fnd_request_groups
251 WHERE application_id = group_application_id
252 AND request_group_name = request_group;
253
254 RETURN group_id;
255
256 EXCEPTION
257 WHEN no_data_found THEN
258 message('Could not find group: '|| request_group);
259 RAISE bad_parameter;
260 END;
261
262
263 PROCEDURE check_yesno(val IN VARCHAR2) IS
264 BEGIN
265 if (upper(val) not in ('Y', 'N')) then
266 message('Y/N value expected, but not found.');
267 raise bad_parameter;
268 end if;
269 END;
270
271
272 PROCEDURE check_notnull(val IN VARCHAR2) IS
273 BEGIN
274 IF(val IS NULL) THEN
275 message('A NULL value was specified for a NOT NULL parameter.');
276 RAISE bad_parameter;
277 END IF;
278 END;
279
280 PROCEDURE check_notnull(val IN NUMBER) IS
281 BEGIN
282 IF(val IS NULL) THEN
283 message('A NULL value was specified for a NOT NULL parameter.');
284 RAISE bad_parameter;
285 END IF;
286 END;
287
288 PROCEDURE check_notnull(val IN DATE) IS
289 BEGIN
290 IF(val IS NULL) THEN
291 message('A NULL value was specified for a NOT NULL parameter.');
292 RAISE bad_parameter;
293 END IF;
294 END;
295
296
297
298 PROCEDURE check_srs(application_id_i IN fnd_application.application_id%TYPE,
299 concurrent_pgm_name_in IN VARCHAR2)
300 IS
301 srs_flag_i fnd_concurrent_programs.srs_flag%TYPE;
302 /* make sure the program exists, and can support SRS */
303 BEGIN
304 SELECT srs_flag
305 INTO srs_flag_i
306 FROM fnd_concurrent_programs
307 WHERE concurrent_program_name = concurrent_pgm_name_in
308 AND application_id = application_id_i;
309 IF(srs_flag_i = 'N') THEN
310 message('SRS not enabled for: '||concurrent_pgm_name_in);
311 RAISE bad_parameter;
312 END IF;
313 EXCEPTION
314 WHEN no_data_found THEN
315 message('Could not find definition of program: '||
316 concurrent_pgm_name_in);
317 RAISE bad_parameter;
318 END;
319
320
321 /* ------------------------------------------------------------ */
322 /* ------------------------------------------------------------ */
323
324 /* START_PUBLIC */
325
326 -- Procedure
327 -- PARAMETER
328 --
329 -- Purpose
330 -- Register an SRS parameter for a program
331 --
332 -- Arguments:
333 -- program_short_name - e.g. FNDSCRMT
334 -- application - Program application.
335 -- e.g.'Application Object Library'
336 -- sequence - Parameter sequence number
337 -- parameter - Name of parameter
338 -- description - Parameter description (Optional)
339 -- enabled - 'Y' or 'N'
340 -- value_set - Name of value set (e.g. '30 Characters Optional')
341 -- default_type - 'Constant', 'Profile', 'SQL Statement', 'Segment'
342 -- (Optional)
343 -- default_value - Parameter default (Required if default_type is not
344 -- Null)
345 -- required - 'Y' or 'N'
346 -- enable_security - 'Y' or 'N', Enables security if value set allows.
347 -- range - 'High', 'Low', or 'Pair' (Optional)
348 -- display - 'Y' or 'N'
349 -- display_size - Length of item in parameter window
350 -- description_size - Length of item description in parameter window
351 -- concatenated_description_size - Length of description in concatenated
352 -- parameters field.
353 -- prompt - Item prompt in parameter window
354 -- token - Required token for Oracle Reports parameters
355 -- cd_parameter - 'Y' sets this parameter to be this program's cd_parameter
356 --
357 PROCEDURE parameter(
358 program_short_name IN VARCHAR2,
359 application IN VARCHAR2,
360 sequence IN NUMBER,
361 parameter IN VARCHAR2,
362 description IN VARCHAR2 DEFAULT NULL,
363 enabled IN VARCHAR2 DEFAULT 'Y',
364 value_set IN VARCHAR2,
365 default_type IN VARCHAR2 DEFAULT NULL,
366 default_value IN VARCHAR2 DEFAULT NULL,
367 required IN VARCHAR2 DEFAULT 'N',
368 enable_security IN VARCHAR2 DEFAULT 'N',
369 range IN VARCHAR2 DEFAULT NULL,
370 display IN VARCHAR2 DEFAULT 'Y',
371 display_size IN NUMBER,
372 description_size IN NUMBER,
373 concatenated_description_size IN NUMBER,
374 prompt IN VARCHAR2 DEFAULT NULL,
375 token IN VARCHAR2 DEFAULT NULL,
376 cd_parameter IN VARCHAR2 DEFAULT 'N')
377 IS
378 descriptive_flexfield_name_in
379 fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
380 form_prompt fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
381 application_id_i fnd_application.application_id%TYPE;
382 column_name VARCHAR2(100);
383 emc varchar2(1);
384 appl_short_name fnd_application.application_short_name%TYPE;
385 BEGIN
386 message_init;
387
388 fnd_flex_dsc_api.set_session_mode(session_mode=>who_mode);
389
390 check_notnull(program_short_name);
391 check_notnull(application);
392 check_notnull(sequence);
393 check_notnull(parameter);
394 check_notnull(enabled);
395 check_notnull(value_set);
396 check_notnull(required);
397 check_notnull(enable_security);
398 check_notnull(display);
399
400 check_yesno(enabled);
401 check_yesno(required);
402 check_yesno(enable_security);
403 check_yesno(display);
404 check_yesno(cd_parameter);
405
406 application_id_i := application_id_f(application);
407 appl_short_name := application_short_name_f(application);
408
409
410
411 BEGIN
412 select execution_method_code
413 into emc
414 from fnd_concurrent_programs
415 where application_id_i = application_id
416 and concurrent_program_name = program_short_name;
417 EXCEPTION
418 when no_data_found then
419 message('Could not find definition of program: '
420 || program_short_name);
421 raise bad_parameter;
422 END;
423
424 if ((emc in ('P','K')) and (token is null)) then
425 message('Token required for Oracle Reports and Java Concurrent Program parameters.');
426 raise bad_parameter;
427 end if;
428
429 if ((emc not in ('P','K')) and (token is not null)) then
430 message('Token must be null except for Oracle Reports and Java Concurrent program parameters.');
431 raise bad_parameter;
432 end if;
433
434 descriptive_flexfield_name_in := '$SRS$.'||program_short_name;
435 check_srs(application_id_i, program_short_name);
436
437 IF(prompt IS NOT NULL) THEN
438 form_prompt := prompt;
439 ELSE
440 form_prompt := parameter;
441 END IF;
442 /* get the first available column name (viz ATTRIBUTEn)
443 and use it in creating the segment */
444 DECLARE
445 CURSOR column_name_cursor IS
446 SELECT column_name
447 FROM fnd_columns c, fnd_tables t
448 WHERE t.table_id = c.table_id
449 AND t.table_name = 'FND_SRS_MASTER' -- hardwired
450 AND t.application_id = 0 -- hardwired
451 AND c.application_id = t.application_id
452 AND c.flexfield_usage_code = 'D'
453 AND NOT EXISTS (SELECT NULL
454 FROM fnd_descr_flex_column_usages cu
455 WHERE cu.application_id = application_id_i
459 BEGIN
456 AND cu.descriptive_flexfield_name
457 = descriptive_flexfield_name_in
458 AND cu.application_column_name = c.column_name);
460 OPEN column_name_cursor;
461 FETCH column_name_cursor INTO column_name;
462 IF column_name_cursor%NOTFOUND THEN
463 message('No available columns in FND_SRS_MASTER.');
464 RAISE bad_parameter;
465 END IF;
466 BEGIN
467 fnd_flex_dsc_api.create_segment(
468 appl_short_name => appl_short_name,
469 flexfield_name => descriptive_flexfield_name_in,
470 context_name => 'Global Data Elements',
471 name => parameter,
472 column => column_name,
473 description => description,
474 sequence_number => sequence,
475 enabled => upper(enabled),
476 displayed => upper(display),
477 value_set => value_set,
478 default_type => default_type,
479 default_value => default_value,
480 required => upper(required),
481 security_enabled => upper(enable_security),
482 display_size => display_size,
483 description_size => description_size,
484 concatenated_description_size => concatenated_description_size,
485 list_of_values_prompt => prompt,
486 window_prompt => prompt,
487 range => range,
488 srw_parameter => token);
489 EXCEPTION
490 WHEN bad_parameter THEN
491 message(fnd_flex_dsc_api.message);
492 RAISE bad_parameter;
493 END;
494 CLOSE column_name_cursor;
495
496 END;
497
498 IF cd_parameter = 'Y' THEN
499 UPDATE fnd_concurrent_programs
500 SET cd_parameter = parameter
501 WHERE application_id = application_id_i
502 AND concurrent_program_name = program_short_name;
503 END IF;
504
505 println('created parameter:'||parameter);
506 END;
507
508
509
510 -- Procedure
511 -- REGISTER
512 --
513 -- Purpose
514 -- Register a concurrent program.
515 --
516 -- Arguments
517 -- program - User-visible program name. e.g. 'Menu Report'
518 -- application - Program application.
519 -- e.g. 'Application Object Library'
520 -- enabled - 'Y' or 'N'
521 -- short_name - Internal program name. e.g. 'FNDSCRMT'
522 -- description - Description of program.
523 -- executable_name - Name of the registered executable.
524 -- executable_application - Name of the application under which the
525 -- executable is registered.
526 -- execution_options - Special options string for certain executables.
527 -- priority - Program level priority. 1..99
528 -- save_output - Save 'report' file? 'Y' or 'N'
529 -- print - 'Y' or 'N'
530 -- cols - Report columns (page width).
531 -- rows - Report rows (page length).
532 -- style - Print style name. (e.g. 'Landwide')
533 -- style_required - Prevent style changes in SRS form. 'Y' or 'N'
534 -- printer - Named printer cannot be changed in SRS form.
535 -- request_type - User-defined request type
536 -- request_type_application - Application of request type.
537 -- use_in_srs - Allow program to be submitted from SRS form
538 -- 'Y' or 'N'
539 -- allow_disabled_values - Allow parameters based on outdated value sets
540 -- to validate anyway.
541 -- run_alone - Program must have the whole system to itself.
542 -- 'Y' or 'N'
543 -- output_type - Type of output generated by the concurrent
544 -- program. 'HTML', 'PS', 'PDF', 'TEXT', 'PCL',
545 -- 'XML'
546 -- enable_trace - Enable SQL trace for this program.
547 -- restart - Restart program if it was running during a
548 -- general system failure.
549 -- nls_compliant - Certifies NLS standards compliance.
550 -- icon_name - Icon used in Web Interfaces. Not yet supported.
551 -- language_code - Code for language of name and description.
552 -- e.g. 'US'
553 -- mls_function_short_name- Name of the registered mls function
554 -- mls_function_application- Name of the application under which mls function
555 -- was registered
556 -- incrementor - Incrementor pl/sql function name
557 -- refresh_portlet - Refresh Portlet based on the specified program
558 -- outcome ('Never','On Success', 'Always',
559 -- 'On Success or Warning')
560 --
561 PROCEDURE register(program IN VARCHAR2,
562 application IN VARCHAR2,
563 enabled IN VARCHAR2,
564 short_name IN VARCHAR2,
565 description IN VARCHAR2 DEFAULT NULL,
566 executable_short_name IN VARCHAR2,
567 executable_application IN VARCHAR2,
568 execution_options IN VARCHAR2 DEFAULT NULL,
569 priority IN NUMBER DEFAULT NULL,
570 save_output IN VARCHAR2 DEFAULT 'Y',
571 print IN VARCHAR2 DEFAULT 'Y',
572 cols IN NUMBER DEFAULT NULL,
576 printer IN VARCHAR2 DEFAULT NULL,
573 rows IN NUMBER DEFAULT NULL,
574 style IN VARCHAR2 DEFAULT NULL,
575 style_required IN VARCHAR2 DEFAULT 'N',
577 request_type IN VARCHAR2 DEFAULT NULL,
578 request_type_application IN VARCHAR2 DEFAULT NULL,
579 use_in_srs IN VARCHAR2 DEFAULT 'N',
580 allow_disabled_values IN VARCHAR2 DEFAULT 'N',
581 run_alone IN VARCHAR2 DEFAULT 'N',
582 output_type IN VARCHAR2 DEFAULT 'TEXT',
583 enable_trace IN VARCHAR2 DEFAULT 'N',
584 restart IN VARCHAR2 DEFAULT 'Y',
585 nls_compliant IN VARCHAR2 DEFAULT 'Y',
586 icon_name IN VARCHAR2 DEFAULT NULL,
587 language_code IN VARCHAR2 DEFAULT 'US',
588 mls_function_short_name IN VARCHAR2 DEFAULT NULL,
589 mls_function_application IN VARCHAR2 DEFAULT NULL,
590 incrementor IN VARCHAR2 DEFAULT NULL,
591 refresh_portlet IN VARCHAR2 DEFAULT NULL
592 )
593 IS
594 last_update_login fnd_flex_value_sets.last_update_login%TYPE
595 := last_update_login_f;
596 last_update_date fnd_flex_value_sets.last_update_date%TYPE
597 := last_update_date_f;
598 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
599 := last_updated_by_f;
600 creation_date fnd_flex_value_sets.creation_date%TYPE
601 := creation_date_f;
602 created_by fnd_flex_value_sets.created_by%TYPE
603 := created_by_f;
604 execution_method_code_i
605 fnd_executables.execution_method_code%TYPE;
606 argument_method_code_i
607 fnd_concurrent_programs.argument_method_code%TYPE;
608 srs_flag_i VARCHAR2(1);
609 application_id_i fnd_application.application_id%TYPE;
610 concurrent_program_id_i
611 fnd_concurrent_programs.concurrent_program_id%TYPE;
612 type_application_id_i fnd_application.application_id%TYPE;
613 concurrent_class_id_i
614 fnd_concurrent_request_class.request_class_id%TYPE;
615 executable_application_id_i fnd_application.application_id%TYPE;
616 executable_id_i fnd_executables.executable_id%TYPE;
617 output_print_style_i fnd_printer_styles.printer_style_name%TYPE;
618 swidth fnd_printer_styles.width%TYPE;
619 slength fnd_printer_styles.length%TYPE;
620 appl_short_name fnd_application.application_short_name%TYPE;
621 mls_function_application_id_i fnd_application.application_id%TYPE;
622 mls_function_exe_id_i fnd_executables.executable_id%TYPE;
623
624 srs1_i VARCHAR2(1);
625 srs2_i VARCHAR2(1);
626 dummy VARCHAR2(1);
627 refresh_portlet_code VARCHAR2(1);
628 BEGIN
629 message_init;
630 println('creating concurrent program...');
631
632 check_notnull(program);
633 check_notnull(application);
634 check_notnull(enabled);
635 check_notnull(short_name);
636 check_notnull(executable_short_name);
637 check_notnull(executable_application);
638 check_notnull(save_output);
639 check_notnull(print);
640 check_notnull(use_in_srs);
641 check_notnull(allow_disabled_values);
642 check_notnull(run_alone);
643 check_notnull(enable_trace);
644 check_notnull(restart);
645 check_notnull(nls_compliant);
646
647 check_yesno(enabled);
648 check_yesno(save_output);
649 check_yesno(print);
650 check_yesno(style_required);
651 check_yesno(run_alone);
652 check_yesno(enable_trace);
653 check_yesno(restart);
654 check_yesno(nls_compliant);
655
656 check_yesno(use_in_srs);
657 check_yesno(allow_disabled_values);
658
659 srs1_i := upper(use_in_srs);
660 srs2_i := upper(allow_disabled_values);
661
662 SELECT fnd_concurrent_programs_s.NEXTVAL /* get next cp id */
663 INTO concurrent_program_id_i
664 FROM sys.dual;
665
666 application_id_i := application_id_f(application);
667 appl_short_name := application_short_name_f(application);
668
669 executable_application_id_i :=
670 application_id_f(executable_application);
671 /* figure out the executable info */
672 BEGIN
673 SELECT executable_id, execution_method_code
674 INTO executable_id_i, execution_method_code_i
675 FROM fnd_executables
676 WHERE application_id = executable_application_id_i
677 AND fnd_executables.executable_name = executable_short_name;
678 EXCEPTION
679 WHEN no_data_found THEN
680 message('Invalid executable short name: '||executable_short_name);
681 message('Application: '||executable_application);
682 RAISE bad_parameter;
683 END;
684 IF (execution_method_code_i = 'A') THEN
685 argument_method_code_i := 'D';
686 ELSE
687 argument_method_code_i := '4';
688 END IF;
689
690 /* set the srs flag */
691 IF(srs1_i = 'Y') THEN
692 IF(srs2_i = 'N') THEN
693 srs_flag_i := 'Y';
694 ELSE
695 srs_flag_i := 'Q';
696 END IF;
697 ELSE
698 IF(srs2_i = 'N') THEN
699 srs_flag_i := 'N';
700 ELSE
704 END IF;
701 message('Cannot "allow disabled values" for non-SRS programs.');
702 RAISE bad_parameter;
703 END IF;
705
706 IF(request_type IS NOT null) THEN
707 type_application_id_i := application_id_f(request_type_application);
708 BEGIN
709 SELECT request_class_id
710 INTO concurrent_class_id_i
711 FROM fnd_concurrent_request_class
712 WHERE application_id = type_application_id_i
713 AND request_class_name = request_type;
714 EXCEPTION
715 WHEN no_data_found THEN
716 message('Invalid request_type: '||request_type);
717 RAISE bad_parameter;
718 END;
719 END IF;
720
721 IF(output_type IS NOT null) THEN
722 if (upper(output_type) not in ('PS', 'PDF', 'HTML', 'TEXT', 'PCL', 'XML')) then
723 message('Output_Type must be ''PS'', ''PDF'', ''HTML'', ''TEXT'', ''PCL'', or ''XML''');
724 RAISE bad_parameter;
725 end if;
726 END IF;
727
728 /* printer style */
729 IF(style IS NOT NULL) THEN
730 BEGIN /* First check developer key */
731 SELECT printer_style_name, width, length
732 INTO output_print_style_i, swidth, slength
733 FROM fnd_printer_styles
734 WHERE printer_style_name = style;
735 EXCEPTION
736 WHEN no_data_found THEN /* Check translated value */
737 BEGIN
738 SELECT b.printer_style_name, b.width, b.length
739 INTO output_print_style_i, swidth, slength
740 FROM fnd_printer_styles_tl t,
741 fnd_printer_styles b
742 WHERE t.user_printer_style_name = style
743 AND rownum = 1
744 AND b.printer_style_name = t.printer_style_name;
745 EXCEPTION
746 WHEN no_data_found THEN
747 message('Cannot find print style: '||style);
748 RAISE bad_parameter;
749 END;
750 END;
751
752 IF ((output_print_style_i = 'DYNAMIC') and (cols is not null)) then
753 message('Number of columns cannot be specified with DYNAMIC styles.');
754 RAISE bad_parameter;
755 END IF;
756
757 IF (((cols is not null) and (swidth < cols)) or
758 ((rows is not null) and (slength < rows))) then
759 message('Style dimensions are less than program columns and rows.');
760 RAISE bad_parameter;
761 END IF;
762
763 ELSIF(upper(style_required) = 'Y') THEN -- if no print style, can't require
764 message('Cannot require a NULL print style.');
765 RAISE bad_parameter;
766 END IF;
767
768 /* Check printer */
769 if (printer is not null) then
770 BEGIN
771 select 'x' into dummy
772 from sys.dual
773 where printer in
774 (select distinct printer_name
775 from fnd_printer p
776 where (output_print_style_i is not null
777 and p.printer_type in
778 (select i.printer_type
779 from fnd_printer_information i,
780 fnd_printer_styles s
781 where i.printer_style = output_print_style_i
782 and s.printer_style_name = i.printer_style))
783 or (output_print_style_i is null
784 and p.printer_type in
785 (select i.printer_type
786 from fnd_printer_information i,
787 fnd_printer_styles s
788 where i.printer_style = s.printer_style_name
789 and s.width >= nvl(cols, s.width)
790 and s.length >= nvl(rows, s.length))));
791 EXCEPTION
792 when no_data_found then
793 message('Invalid or non-existent printer.');
794 RAISE bad_parameter;
795 END;
796 end if;
797
798 IF((output_print_style_i = 'DYNAMIC') AND
799 (upper(style_required) <> 'Y')) THEN
800 message('Style_required must be "Y" for DYNAMIC style: '||style);
801 RAISE bad_parameter;
802 END IF;
803 IF((execution_method_code_i = 'P') AND
804 (style IS NULL)) THEN
805 message('A print style is required for the executable: ' ||
806 executable_short_name);
807 RAISE bad_parameter;
808 END IF;
809
810 if ((execution_options is not null) and
811 (execution_method_code_i not in ('R','P','X','H','L','K','E'))) then
812 message ('Execution options are not allowed for this executable.');
813 RAISE bad_parameter;
814 end if;
815
816 if ((mls_function_short_name is not null) and
817 (mls_function_application is not null)) then
818 mls_function_application_id_i :=
819 application_id_f(mls_function_application);
820 /* figure out the executable info */
821 BEGIN
822 SELECT executable_id
823 INTO mls_function_exe_id_i
824 FROM fnd_executables
825 WHERE application_id = mls_function_application_id_i
826 AND fnd_executables.executable_name = mls_function_short_name;
827 EXCEPTION
828 WHEN no_data_found THEN
829 message('Invalid mls function short name: '||mls_function_short_name);
833 end if;
830 message('Application: '||mls_function_application);
831 RAISE bad_parameter;
832 END;
834
835 if ( refresh_portlet is not null ) then
836
837 BEGIN
838 select lookup_code
839 into refresh_portlet_code
840 from fnd_lookup_values
841 where upper(meaning) = upper(refresh_portlet)
842 and lookup_type = 'CONC_REFRESH_PORTLET'
843 and enabled_flag = 'Y'
844 and rownum = 1;
845 EXCEPTION
846 WHEN no_data_found THEN
847 message('Unknown Refresh Portlet value: ' || refresh_portlet);
848 RAISE bad_parameter;
849 END;
850
851 end if;
852
853 INSERT INTO fnd_concurrent_programs(
854 application_id, concurrent_program_id, concurrent_program_name,
855 last_update_date, last_updated_by,
856 creation_date, created_by, last_update_login,
857 executable_application_id, executable_id, execution_method_code,
858 argument_method_code, queue_control_flag, queue_method_code,
859 request_set_flag, enabled_flag, print_flag,
860 run_alone_flag, srs_flag,
861 class_application_id, concurrent_class_id, execution_options,
862 save_output_flag, required_style, output_print_style,
863 printer_name, minimum_width, minimum_length,
864 request_priority, output_file_type, enable_trace,
865 restart, nls_compliant, icon_name,
866 mls_executable_app_id, mls_executable_id, increment_proc,
867 refresh_portlet)
868 VALUES (application_id_i, concurrent_program_id_i, upper(short_name),
869 last_update_date, last_updated_by,
870 creation_date, created_by, last_update_login,
871 executable_application_id_i, executable_id_i,
872 execution_method_code_i,
873 argument_method_code_i, 'N', 'I',
874 'N', upper(enabled), upper(print),
875 upper(run_alone), srs_flag_i,
876 type_application_id_i, concurrent_class_id_i,
877 execution_options,
878 upper(save_output), upper(style_required), output_print_style_i,
879 printer, cols, rows,
880 priority, output_type, enable_trace,
881 restart, nls_compliant, null,
882 mls_function_application_id_i, mls_function_exe_id_i, incrementor,
883 refresh_portlet_code);
884 println('inserted into concurrent programs');
885
886 -- insert rows for each installed language into the tl table */
887
888 insert into FND_CONCURRENT_PROGRAMS_TL (
889 APPLICATION_ID, CONCURRENT_PROGRAM_ID, USER_CONCURRENT_PROGRAM_NAME,
890 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
891 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION,
892 LANGUAGE, SOURCE_LANG
893 ) select
894 application_id_i, concurrent_program_id_i, program,
895 register.creation_date, register.created_by, register.last_update_date,
896 register.last_updated_by, register.last_update_login,
897 register.description,
898 L.LANGUAGE_CODE,
899 register.language_code
900 from FND_LANGUAGES L
901 where L.INSTALLED_FLAG in ('I', 'B')
902 and not exists
903 (select NULL
904 from FND_CONCURRENT_PROGRAMS_TL T
905 where T.APPLICATION_ID = application_id_i
906 and T.CONCURRENT_PROGRAM_ID = concurrent_program_id_i
907 and T.LANGUAGE = L.LANGUAGE_CODE);
908
909
910 println('inserted into concurrent programs tl');
911
912 -- create the flexfield now as well
913 -- since we are not supporting changes is is cleaner to
914 -- do it here, rather than in parameters.
915 IF(srs_flag_i <> 'N') THEN
916 println('creating a descriptive flexfield');
917
918 fnd_flex_dsc_api.set_session_mode(session_mode=>who_mode);
919
920 DECLARE
921 descriptive_flexfield_name
922 fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
923 title fnd_descriptive_flexs_vl.title%TYPE;
924 BEGIN
925 descriptive_flexfield_name := '$SRS$.'||short_name;
926 title := '$SRS$.' || short_name;
927 fnd_flex_dsc_api.register(
928 appl_short_name => appl_short_name,
929 flexfield_name => descriptive_flexfield_name,
930 title => title,
931 description => NULL,
932 table_appl_short_name => 'FND',
933 table_name => 'FND_SRS_MASTER',
934 structure_column => 'STRUCTURE_COLUMN',
935 protected_flag => 'S');
936 fnd_flex_dsc_api.setup_context_field(
937 appl_short_name => appl_short_name,
938 flexfield_name => descriptive_flexfield_name,
939 segment_separator => ':',
940 prompt => 'Context Value',
941 default_value => NULL,
942 reference_field => NULL,
943 value_required => 'N',
944 override_allowed => 'N',
945 freeze_flexfield_definition => 'Y');
946 EXCEPTION
947 WHEN bad_parameter THEN
948 message(fnd_flex_dsc_api.message);
949 RAISE bad_parameter;
950 END;
951 END IF;
952 println('created concurrent program:' || program);
953 END;
954
955
956
957 -- Procedure
958 -- Incompatibility
959 --
960 -- Purpose
961 -- Register a concurrent program incompatibility.
962 --
963 -- Arguments
964 -- program_short_name - Short name of the first program. (e.g. FNDSCRMT)
968 -- inc_prog_application- Application of the incompatible program.
965 -- application - Application of the first program.
966 -- (e.g. 'Application Object Library')
967 -- inc_prog_short_name - Short name of the incompatible program.
969 -- scope - 'Set' or 'Program Only'
970 -- inc_type - Incompatibility type - (D)omain-specific or (G)lobal
971 --
972 PROCEDURE incompatibility(program_short_name IN VARCHAR2,
973 application IN VARCHAR2,
974 inc_prog_short_name IN VARCHAR2,
975 inc_prog_application IN VARCHAR2,
976 scope IN VARCHAR2 DEFAULT 'Set',
977 inc_type IN VARCHAR2 DEFAULT 'D')
978 IS
979 last_update_login fnd_flex_value_sets.last_update_login%TYPE
980 := last_update_login_f;
981 last_update_date fnd_flex_value_sets.last_update_date%TYPE
982 := last_update_date_f;
983 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
984 := last_updated_by_f;
985 creation_date fnd_flex_value_sets.creation_date%TYPE
986 := creation_date_f;
987 created_by fnd_flex_value_sets.created_by%TYPE
988 := created_by_f;
989 application_id_i fnd_application.application_id%TYPE;
990 concurrent_program_id_i
991 fnd_concurrent_programs.concurrent_program_id%TYPE;
992 to_run_concurrent_program_id_i
993 fnd_concurrent_programs.concurrent_program_id%TYPE;
994 to_run_application_id_i fnd_application.application_id%TYPE;
995 scope_code_i fnd_concurrent_program_serial.to_run_type%TYPE;
996 BEGIN
997 message_init;
998
999 check_notnull(program_short_name);
1000 check_notnull(application);
1001 check_notnull(inc_prog_short_name);
1002 check_notnull(inc_prog_application);
1003 check_notnull(scope);
1004 check_notnull(inc_type);
1005
1006 application_id_i := application_id_f(application);
1007 to_run_application_id_i :=
1008 application_id_f(inc_prog_application);
1009 BEGIN
1010 SELECT lookup_code
1011 INTO scope_code_i
1012 FROM fnd_lookup_values
1013 WHERE lookup_type = 'CP_SERIAL_TYPE'
1014 AND upper(meaning) = upper(scope)
1015 AND rownum = 1;
1016 EXCEPTION
1017 WHEN no_data_found THEN
1018 message('Invalid scope: '||scope);
1019 RAISE bad_parameter;
1020 END;
1021
1022 concurrent_program_id_i :=
1023 concurrent_program_id_f(application_id_i, program_short_name);
1024
1025 to_run_concurrent_program_id_i :=
1026 concurrent_program_id_f(to_run_application_id_i, inc_prog_short_name);
1027
1028 IF inc_type <> 'D' AND inc_type <> 'G' THEN
1029 message('Invalid incompatibility type: ' ||inc_type);
1030 RAISE bad_parameter;
1031 END IF;
1032
1033 /* update incompatibilies */
1034 UPDATE fnd_concurrent_programs SET
1035 queue_method_code = 'B'
1036 WHERE ((application_id = application_id_i
1037 AND concurrent_program_id = concurrent_program_id_i)
1038 OR (application_id = to_run_application_id_i
1039 AND concurrent_program_id = to_run_concurrent_program_id_i));
1040
1041
1042 INSERT INTO fnd_concurrent_program_serial(running_concurrent_program_id,
1043 running_application_id,
1044 running_type,
1045 to_run_concurrent_program_id,
1046 to_run_application_id,
1047 to_run_type,
1048 last_update_date,
1049 last_updated_by,
1050 creation_date,
1051 created_by,
1052 last_update_login,
1053 incompatibility_type)
1054 VALUES(concurrent_program_id_i,
1055 application_id_i,
1056 scope_code_i,
1057 to_run_concurrent_program_id_i,
1058 to_run_application_id_i,
1059 scope_code_i,
1060 last_update_date,
1061 last_updated_by,
1062 creation_date,
1063 created_by,
1064 last_update_login,
1065 inc_type);
1066
1067 /* and do the other side as well */
1068 IF((concurrent_program_id_i <> to_run_concurrent_program_id_i)
1069 OR
1070 (application_id_i <> to_run_application_id_i)) THEN
1071 BEGIN
1072 INSERT
1073 INTO fnd_concurrent_program_serial(running_concurrent_program_id,
1074 running_application_id,
1075 running_type,
1076 to_run_concurrent_program_id,
1077 to_run_application_id,
1078 to_run_type,
1079 last_update_date,
1080 last_updated_by,
1081 creation_date,
1082 created_by,
1083 last_update_login,
1084 incompatibility_type)
1085 VALUES(to_run_concurrent_program_id_i,
1086 to_run_application_id_i,
1087 scope_code_i,
1088 concurrent_program_id_i,
1089 application_id_i,
1090 scope_code_i,
1091 last_update_date,
1092 last_updated_by,
1093 creation_date,
1094 created_by,
1095 last_update_login,
1096 inc_type);
1097 END;
1098 END IF;
1099
1100 println('created incompatibility entries for:'||
1101 program_short_name || ' with:' ||
1102 inc_prog_short_name);
1103 END;
1107 -- Procedure
1104
1105
1106
1108 -- EXECUTABLE
1109 --
1110 -- Purpose
1111 -- Register a concurrent program executable.
1112 --
1113 -- Arguments
1114 -- executable - Name of executable. (e.g. 'FNDSCRMT')
1115 -- application - Name of executable's application.
1116 -- (e.g. 'Application Object Library')
1117 -- short_name - Short (non-translated) name of the executable.
1118 -- description - Optional description of the executable.
1119 -- execution method - 'FlexRpt', 'FlexSQL', 'Host', 'Immediate',
1120 -- 'Oracle Reports', 'PL/SQL Stored Procedure',
1121 -- 'Spawned', 'SQL*Loader', 'SQL*Plus', 'SQL*Report',
1122 -- 'Request Set Stage Function',
1123 -- 'Multi Language Function','Java Stored Procedure'
1124 -- execution_file_name - Required for all but 'Immediate' programs.
1125 -- Cannot contain spaces or periods.
1126 -- subroutine_name - Used only for 'Immediate' programs.
1127 -- Cannot contain spaces or periods.
1128 -- icon_name - For future web interface.
1129 -- language_code - Language code for the name and description.
1130 -- (e.g. 'US')
1131 -- execution_file_path - Used only for 'Java Concurrent Program'
1132 -- It is the package path for the class
1133 --
1134 PROCEDURE executable(executable IN VARCHAR2,
1135 application IN VARCHAR2,
1136 short_name IN VARCHAR2,
1137 description IN VARCHAR2 DEFAULT NULL,
1138 execution_method IN VARCHAR2,
1139 execution_file_name IN VARCHAR2 DEFAULT NULL,
1140 subroutine_name IN VARCHAR2 DEFAULT NULL,
1141 icon_name IN VARCHAR2 DEFAULT NULL,
1142 language_code IN VARCHAR2 DEFAULT 'US',
1143 execution_file_path IN VARCHAR2 DEFAULT NULL) is
1144 application_id number;
1145 execution_method_code varchar2(1);
1146 executable_id number;
1147 last_update_date fnd_executables.last_update_date%TYPE;
1148 last_updated_by fnd_executables.last_updated_by%TYPE;
1149 creation_date fnd_executables.creation_date%TYPE;
1150 created_by fnd_executables.created_by%TYPE;
1151 last_update_login fnd_executables.last_update_login%TYPE;
1152 row_id fnd_executables_vl.row_id%type;
1153
1154 BEGIN
1155 message_init;
1156
1157 check_notnull(executable);
1158 check_notnull(short_name);
1159 check_notnull(application);
1160 check_notnull(execution_method);
1161
1162 last_update_date := last_update_date_f;
1163 last_updated_by := last_updated_by_f;
1164 creation_date := creation_date_f;
1165 created_by := created_by_f;
1166 last_update_login := last_update_login_f;
1167 application_id := application_id_f(application);
1168 BEGIN
1169 select lookup_code
1170 into execution_method_code
1171 from fnd_lookup_values
1172 where upper(meaning) = upper(execution_method)
1173 and lookup_type = 'CP_EXECUTION_METHOD_CODE'
1174 and enabled_flag = 'Y'
1175 and rownum = 1;
1176 EXCEPTION
1177 WHEN no_data_found THEN
1178 message('Unknown execution method: ' || execution_method);
1179 RAISE bad_parameter;
1180 END;
1181
1182 if ((execution_method_code = 'S') and (subroutine_name is null)) then
1183 message('Subroutine name required for Immediate Concurrent Programs.');
1184 RAISE bad_parameter;
1185 elsif ((execution_method_code <> 'S') and
1186 (execution_file_name is null)) then
1187 message('Execution file name required.');
1188 RAISE bad_parameter;
1189 elsif ((subroutine_name is not null) and
1190 (execution_method_code not in ('A','S'))) then
1191 message('Subroutine name must be null for the selected execution method.');
1192 RAISE bad_parameter;
1193 end if;
1194
1195 if ((execution_method_code = 'K') and (execution_file_path is null)) then
1196 message('Execution File Path is required for Java Concurrent Programs.');
1197 RAISE bad_parameter;
1198 elsif ((execution_method_code <> 'K') and
1199 (execution_file_path is not null)) then
1200 message('Execution File Path not required for the selected execution method.');
1201 RAISE bad_parameter;
1202 end if;
1203
1204 if ((instr(subroutine_name, ' ') > 0)
1205 or (instr(subroutine_name, '.') > 0)) then
1206 message('Subroutine name must not contain spaces or periods.');
1207 RAISE bad_parameter;
1208 end if;
1209
1210 if ((instr(execution_file_name, ' ') > 0)
1211 or ((execution_method_code not in ('I', 'B', 'M', 'J', 'Z', 'E')) and
1212 (instr(execution_file_name, '.') > 0))) then
1213 message('Execution file name must not contain spaces or periods.');
1214 RAISE bad_parameter;
1215 end if;
1216
1217
1218 select fnd_executables_s.nextval into executable_id from sys.dual;
1219
1220 insert into FND_EXECUTABLES (
1221 APPLICATION_ID, EXECUTABLE_ID, EXECUTABLE_NAME,
1222 EXECUTION_METHOD_CODE, EXECUTION_FILE_NAME, SUBROUTINE_NAME,
1223 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
1224 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, icon_name, execution_file_path
1228 creation_date, created_by, last_update_date,
1225 ) values (
1226 application_id, executable_id, short_name,
1227 execution_method_code, execution_file_name, subroutine_name,
1229 last_updated_by, last_update_login, icon_name, execution_file_path
1230 );
1231
1232 insert into FND_EXECUTABLES_TL (
1233 APPLICATION_ID, EXECUTABLE_ID, CREATION_DATE,
1234 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1235 LAST_UPDATE_LOGIN, USER_EXECUTABLE_NAME, DESCRIPTION,
1236 LANGUAGE, SOURCE_LANG
1237 ) select
1238 application_id, executable_id, fnd_program.executable.creation_date,
1239 fnd_program.executable.created_by, fnd_program.executable.last_update_date,
1240 fnd_program.executable.last_updated_by,
1241 fnd_program.executable.last_update_login, executable,
1242 fnd_program.executable.description,
1243 l.language_code, fnd_program.executable.language_code
1244 from FND_LANGUAGES L
1245 where L.INSTALLED_FLAG in ('I', 'B');
1246
1247 println('created executable for:'|| executable);
1248
1249 END executable;
1250
1251
1252
1253 -- Procedure
1254 -- REQUEST_GROUP
1255 --
1256 -- Purpose
1257 -- Registers a request group.
1258 --
1259 -- Arguments
1260 -- group - Name of request group.
1261 -- application - Name of group's application.
1262 -- (e.g. 'Application Object Library')
1263 -- code - Optional group code.
1264 -- description - Optional description of the set.
1265 PROCEDURE request_group(request_group IN VARCHAR2,
1266 application IN VARCHAR2,
1267 code IN VARCHAR2 DEFAULT NULL,
1268 description IN VARCHAR2 DEFAULT NULL) is
1269 last_update_login fnd_request_groups.last_update_login%TYPE
1270 := last_update_login_f;
1271 last_update_date fnd_request_groups.last_update_date%TYPE
1272 := last_update_date_f;
1273 last_updated_by fnd_request_groups.last_updated_by%TYPE
1274 := last_updated_by_f;
1275 creation_date fnd_request_groups.creation_date%TYPE
1276 := creation_date_f;
1277 created_by fnd_request_groups.created_by%TYPE
1278 := created_by_f;
1279 group_application_id fnd_application.application_id%TYPE;
1280 BEGIN
1281 message_init;
1282
1283 check_notnull(request_group);
1284 check_notnull(application);
1285
1286 group_application_id := application_id_f(application);
1287
1288 insert into fnd_request_groups
1289 (application_id,
1290 request_group_id,
1291 request_group_name,
1292 last_update_date,
1293 last_updated_by,
1294 creation_date,
1295 created_by,
1296 last_update_login,
1297 description,
1298 request_group_code)
1299 values
1300 (group_application_id,
1301 fnd_request_groups_s.nextval,
1302 request_group,
1303 last_update_date,
1304 last_updated_by,
1305 creation_date,
1306 created_by,
1307 last_update_login,
1308 description,
1309 code);
1310 END;
1311
1312
1313
1314 -- Procedure
1315 -- ADD_TO_GROUP
1316 --
1317 -- Purpose
1318 -- Add a concurrent program to a request group.
1319 --
1320 -- Arguments
1321 -- program_short_name - Short name of the program. (e.g. FNDSCRMT)
1322 -- program_application - Application of the program.
1323 -- (e.g. 'Application Object Library')
1324 -- request_group - Name of request group.
1325 -- group_application - Application of the request group.
1326 --
1327 PROCEDURE add_to_group(program_short_name IN VARCHAR2,
1328 program_application IN VARCHAR2,
1329 request_group IN VARCHAR2,
1330 group_application IN VARCHAR2) is
1331 last_update_login fnd_request_groups.last_update_login%TYPE
1332 := last_update_login_f;
1333 last_update_date fnd_request_groups.last_update_date%TYPE
1334 := last_update_date_f;
1335 last_updated_by fnd_request_groups.last_updated_by%TYPE
1336 := last_updated_by_f;
1337 creation_date fnd_request_groups.creation_date%TYPE
1338 := creation_date_f;
1339 created_by fnd_request_groups.created_by%TYPE
1340 := created_by_f;
1341 program_application_id
1342 fnd_application.application_id%TYPE;
1343 group_application_id
1344 fnd_application.application_id%TYPE;
1345 concurrent_program_id
1346 fnd_concurrent_programs.concurrent_program_id%TYPE;
1347 group_id
1348 fnd_request_groups.request_group_id%TYPE;
1349 BEGIN
1350 message_init;
1351
1352 check_notnull(program_short_name);
1353 check_notnull(program_application);
1354 check_notnull(request_group);
1355 check_notnull(group_application);
1356
1357 program_application_id := application_id_f(program_application);
1358 group_application_id := application_id_f(group_application);
1359
1360 concurrent_program_id :=
1361 concurrent_program_id_f(program_application_id, program_short_name);
1362
1363 group_id := request_group_id_f(group_application_id, request_group);
1364
1368 UNIT_APPLICATION_ID,
1365 insert into Fnd_Request_Group_Units(APPLICATION_ID,
1366 REQUEST_GROUP_ID,
1367 REQUEST_UNIT_TYPE,
1369 REQUEST_UNIT_ID,
1370 LAST_UPDATE_DATE,
1371 LAST_UPDATED_BY,
1372 CREATION_DATE,
1373 CREATED_BY,
1374 LAST_UPDATE_LOGIN)
1375 values( group_application_id,
1376 group_id,
1377 'P',
1378 program_application_id,
1379 concurrent_program_id,
1380 last_update_date,
1381 last_updated_by,
1382 creation_date,
1383 created_by,
1384 last_update_login);
1385
1386 println('Added program '|| program_short_name || ' to group '
1387 ||request_group);
1388
1389 END;
1390
1391
1392
1393 -- Procedure
1394 -- REMOVE_FROM_GROUP
1395 --
1396 -- Purpose
1397 -- Remove a concurrent program to a request group.
1398 --
1399 -- Arguments
1400 -- program_short_name - Short name of the program. (e.g. FNDSCRMT)
1401 -- program_application - Application of the program.
1402 -- (e.g. 'Application Object Library')
1403 -- request_group - Name of request group.
1404 -- group_application - Application of the request group.
1405 --
1406 PROCEDURE remove_from_group(program_short_name IN VARCHAR2,
1407 program_application IN VARCHAR2,
1408 request_group IN VARCHAR2,
1409 group_application IN VARCHAR2) is
1410 program_application_id
1411 fnd_application.application_id%TYPE;
1412 group_application_id
1413 fnd_application.application_id%TYPE;
1414 concurrent_program_id
1415 fnd_concurrent_programs.concurrent_program_id%TYPE;
1416 group_id
1417 fnd_request_groups.request_group_id%TYPE;
1418 BEGIN
1419 message_init;
1420
1421 check_notnull(program_short_name);
1422 check_notnull(program_application);
1423 check_notnull(request_group);
1424 check_notnull(group_application);
1425
1426 program_application_id := application_id_f(program_application);
1427 group_application_id := application_id_f(group_application);
1428
1429
1430 concurrent_program_id :=
1431 concurrent_program_id_f(program_application_id, program_short_name);
1432
1433 group_id := request_group_id_f(group_application_id, request_group);
1434
1435 delete from fnd_request_group_units u
1436 where application_id = group_application_id
1437 and request_group_id = group_id
1438 and request_unit_type = 'P'
1439 and unit_application_id = program_application_id
1440 and request_unit_id = concurrent_program_id;
1441 END;
1442
1443
1444
1445 -- Procedure
1446 -- DELETE_PROGRAM
1447 --
1448 -- Purpose
1449 -- Delete a concurrent program.
1450 --
1451 -- Arguments
1452 -- program_short_name - Short name of the program. (e.g. FNDSCRMT)
1453 -- application - Application of the program.
1454 -- (e.g. 'Application Object Library')
1455 --
1456 PROCEDURE delete_program(program_short_name IN VARCHAR2,
1457 application IN VARCHAR2) is
1458 program_application_id fnd_application.application_id%TYPE;
1459 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
1460 desc_flex_name fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
1461 rs_flag fnd_concurrent_programs.request_set_flag%TYPE;
1462 set_id fnd_request_sets.request_set_id%TYPE;
1463 prog_appl_short_name fnd_application.application_short_name%TYPE;
1464
1465 cursor req_cursor(appl_id number, prog_id number) is
1466 select request_id
1467 from fnd_concurrent_requests
1468 where concurrent_program_id = prog_id
1469 and program_application_id = appl_id;
1470
1471 BEGIN
1472 message_init;
1473
1474 check_notnull(program_short_name);
1475 check_notnull(application);
1476
1477 program_application_id := application_id_f(application);
1478 prog_appl_short_name := application_short_name_f(application);
1479
1480 desc_flex_name := '$SRS$.'||program_short_name;
1481
1482 BEGIN
1483 SELECT concurrent_program_id, request_set_flag
1484 INTO program_id, rs_flag
1485 FROM fnd_concurrent_programs
1486 WHERE application_id = program_application_id
1487 AND concurrent_program_name = program_short_name;
1488 EXCEPTION
1489 WHEN no_data_found THEN
1490 println('Could not find program: '||program_short_name);
1491 return;
1492 END;
1493
1494 -- If the program is a request_set parent, then
1495 -- we delete the entire request set definition
1496
1497 if (rs_flag = 'Y') then
1498
1499 BEGIN
1500
1501 SELECT request_set_id
1502 INTO set_id
1503 FROM fnd_request_sets
1504 WHERE application_id = program_application_id
1505 AND concurrent_program_id = program_id;
1506
1510 WHERE request_unit_type = 'S'
1507 -- Remove set from request groups
1508
1509 DELETE FROM fnd_request_group_units
1511 AND request_unit_id = set_id
1512 AND unit_application_id = program_application_id;
1513
1514 -- Remove member program arguments
1515
1516 DELETE FROM fnd_request_set_program_args
1517 WHERE application_id = program_application_id
1518 AND request_set_id = set_id;
1519
1520 -- Remove member programs
1521
1522 DELETE FROM fnd_request_set_programs
1523 WHERE set_application_id = program_application_id
1524 AND request_set_id = set_id;
1525
1526 -- Remove the set
1527
1528 DELETE FROM fnd_request_sets_tl
1529 WHERE application_id = program_application_id
1530 AND REQUEST_SET_ID = set_id;
1531
1532 DELETE FROM fnd_request_sets
1533 WHERE application_id = program_application_id
1534 AND request_set_id = set_id;
1535
1536 exception
1537 when NO_DATA_FOUND then
1538 null; -- The request set did not exist for
1539 -- some starnge reason.
1540 -- Just continue.
1541 end;
1542 else -- Program is not a set
1543
1544 -- Remove any request set program arguments if this program is
1545 -- a set member
1546
1547 DELETE FROM fnd_request_set_program_args
1548 WHERE application_id = program_application_id
1549 AND request_set_program_id in
1550 (SELECT request_set_program_id
1551 FROM fnd_request_set_programs frsp
1552 WHERE frsp.program_application_id
1553 = delete_program.program_application_id
1554 AND concurrent_program_id = program_id);
1555
1556 -- Remove the program from request sets
1557
1558 DELETE FROM fnd_request_set_programs frs
1559 WHERE frs.program_application_id = delete_program.program_application_id
1560 AND concurrent_program_id = program_id;
1561
1562 -- Remove the program from request groups
1563
1564 DELETE fnd_request_group_units
1565 WHERE request_unit_type = 'P'
1566 AND request_unit_id = program_id
1567 AND unit_application_id = program_application_id;
1568
1569 end if;
1570
1571 -- Delete the program's incompatibilities
1572
1573 DELETE fnd_concurrent_program_serial
1574 WHERE running_concurrent_program_id = program_id
1575 AND running_application_id = program_application_id;
1576
1577 DELETE fnd_concurrent_program_serial
1578 WHERE to_run_concurrent_program_id = program_id
1579 AND to_run_application_id = program_application_id;
1580
1581 -- Finally, delete the program
1582 DELETE fnd_concurrent_programs_tl
1583 WHERE application_id = program_application_id
1584 AND concurrent_program_id = program_id;
1585
1586 DELETE fnd_concurrent_programs
1587 WHERE concurrent_program_id = program_id
1588 AND application_id = program_application_id;
1589
1590 -- Delete flex.
1591 fnd_flex_dsc_api.delete_flexfield(
1592 appl_short_name => prog_appl_short_name,
1593 flexfield_name =>desc_flex_name);
1594
1595 for req in req_cursor(program_application_id, program_id) loop
1596 delete from fnd_concurrent_requests
1597 where request_id = req.request_id;
1598
1599 delete from fnd_run_requests
1600 where request_id = req.request_id;
1601
1602 delete from fnd_conc_release_periods_tl
1603 where (application_id, concurrent_period_id) in
1604 (select application_id, concurrent_period_id
1605 from fnd_conc_release_periods
1606 where owner_req_id = req.request_id);
1607
1608 delete from fnd_conc_release_periods
1609 where owner_req_id = req.request_id;
1610
1611 delete from fnd_conc_release_states_tl
1612 where (application_id, concurrent_state_id) in
1613 (select application_id, concurrent_state_id
1614 from fnd_conc_release_states
1615 where owner_req_id = req.request_id);
1616
1617 delete from fnd_conc_release_states
1618 where owner_req_id = req.request_id;
1619
1620 delete from fnd_conc_release_classes_tl
1621 where (application_id, release_class_id) in
1622 (select application_id, release_class_id
1623 from fnd_conc_release_classes
1624 where owner_req_id = req.request_id);
1625
1626 delete from fnd_conc_release_classes
1627 where owner_req_id = req.request_id;
1628
1629 delete from fnd_conc_release_disjs_tl
1630 where (application_id, disjunction_id) in
1631 (select application_id, disjunction_id
1632 from fnd_conc_release_disjs
1633 where owner_req_id = req.request_id);
1634
1635 delete from fnd_conc_release_disjs
1636 where owner_req_id = req.request_id;
1637
1638 delete from fnd_conc_rel_disj_members
1639 where owner_req_id = req.request_id;
1640
1641 delete from fnd_conc_rel_conj_members
1642 where owner_req_id = req.request_id;
1643
1644 delete from fnd_conc_pp_actions
1645 where concurrent_request_id = req.request_id;
1646
1647 delete from fnd_run_req_pp_actions
1648 where parent_request_id = req.request_id;
1649
1650 delete from fnd_file_temp
1654 delete from fnd_run_requests
1651 where request_id = req.request_id;
1652
1653 /* in case parent was cancelled before running: */
1655 where parent_request_id = req.request_id;
1656
1657 delete from fnd_conc_request_arguments
1658 where request_id = req.request_id;
1659 end loop;
1660
1661 println('Program '|| program_short_name || 'deleted.');
1662
1663 END delete_program;
1664
1665
1666
1667 -- Procedure
1668 -- DELETE_PARAMETER
1669 --
1670 -- Purpose
1671 -- Delete a concurrent program parameter.
1672 --
1673 -- Arguments
1674 -- program_short_name - Short name of the program. (e.g. FNDSCRMT)
1675 -- application - Application of the program.
1676 -- (e.g. 'Application Object Library')
1677 -- parameter - Parameter name.
1678 PROCEDURE delete_parameter(program_short_name IN VARCHAR2,
1679 application IN VARCHAR2,
1680 parameter IN VARCHAR2) is
1681
1682 program_appl_id
1683 fnd_application.application_id%TYPE;
1684 program_id
1685 fnd_concurrent_programs.concurrent_program_id%TYPE;
1686 desc_flex_name
1687 fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
1688 prog_appl_short_name fnd_application.application_short_name%TYPE;
1689
1690 BEGIN
1691 message_init;
1692
1693 check_notnull(program_short_name);
1694 check_notnull(application);
1695 check_notnull(parameter);
1696
1697 program_appl_id := application_id_f(application);
1698 prog_appl_short_name := application_short_name_f(application);
1699
1700 desc_flex_name := '$SRS$.'||program_short_name;
1701
1702 program_id := concurrent_program_id_f(program_appl_id, program_short_name);
1703
1704 -- Delete param references in request sets
1705 delete from fnd_request_set_program_args a
1706 where (a.application_id, a.request_set_id, a.request_set_program_id)
1707 in (select sp.set_application_id,
1708 sp.request_set_id,
1709 sp.request_set_program_id
1710 from fnd_request_set_programs sp
1711 where sp.program_application_id = program_appl_id
1712 and sp.concurrent_program_id = program_id)
1713 and (a.descriptive_flex_appl_id,
1714 a.descriptive_flexfield_name,
1715 a.application_column_name) in
1716 (select u.application_id,
1717 u.descriptive_flexfield_name,
1718 u.application_column_name
1719 from fnd_descr_flex_column_usages u
1720 where u.application_id = program_appl_id
1721 and u.descriptive_flexfield_name = desc_flex_name
1722 and u.descriptive_flex_context_code = 'Global Data Elements'
1723 and u.end_user_column_name = parameter);
1724
1725 -- Delete the param
1726 fnd_flex_dsc_api.delete_segment(appl_short_name=> prog_appl_short_name,
1727 flexfield_name=>desc_flex_name,
1728 context=>'Global Data Elements',
1729 segment=>parameter);
1730
1731 END delete_parameter;
1732
1733
1734
1735 -- Procedure
1736 -- DELETE_EXECUTABLE
1737 --
1738 -- Purpose
1739 -- Delete a concurrent program executable.
1740 --
1741 -- Arguments
1742 -- executable_short_name - Name of the executable. (e.g. FNDSCRMT)
1743 -- application - Application of the executable.
1744 -- (e.g. 'Application Object Library')
1745 --
1746 PROCEDURE delete_executable(executable_short_name IN VARCHAR2,
1747 application IN VARCHAR2) is
1748 exec_application_id
1749 fnd_application.application_id%TYPE;
1750 exec_id
1751 fnd_executables.executable_id%TYPE;
1752 dummy varchar2(1);
1753
1754 BEGIN
1755 message_init;
1756
1757 check_notnull(executable_short_name);
1758 check_notnull(application);
1759
1760 exec_application_id := application_id_f(application);
1761
1762 BEGIN
1763 SELECT executable_id
1764 INTO exec_id
1765 FROM fnd_executables
1766 WHERE application_id = exec_application_id
1767 AND executable_name = executable_short_name;
1768 EXCEPTION
1769 WHEN no_data_found THEN
1770 println('Could not find executable: '||executable_short_name);
1771 return;
1772 END;
1773
1774 -- Can't delete an executable if it is in use
1775 BEGIN
1776 select 'x' into dummy
1777 from sys.dual
1778 where not exists
1779 (select 1
1780 from fnd_concurrent_programs
1781 where executable_application_id = exec_application_id
1782 and executable_id = exec_id);
1783 EXCEPTION
1784 when no_data_found then
1785 message('Error - Executable is assigned to a concurrent program.');
1786 RAISE bad_parameter;
1787 END;
1788
1789 delete from fnd_executables_tl
1790 where executable_id = exec_id
1791 and application_id = exec_application_id;
1792
1793 delete from fnd_executables
1794 where executable_id = exec_id
1795 and application_id = exec_application_id;
1796
1797 END delete_executable;
1798
1799
1800
1801 -- Procedure
1802 -- DELETE_GROUP
1806 --
1803 --
1804 -- Purpose
1805 -- Delete a request group.
1807 -- Arguments
1808 -- request_group - Name of the group. (e.g. FNDSCRMT)
1809 -- application - Application of the executable.
1810 -- (e.g. 'Application Object Library')
1811 --
1812 PROCEDURE delete_group(request_group IN VARCHAR2,
1813 application IN VARCHAR2) is
1814 group_application_id
1815 fnd_application.application_id%TYPE;
1816 BEGIN
1817 message_init;
1818
1819 check_notnull(request_group);
1820 check_notnull(application);
1821
1822 group_application_id := application_id_f(application);
1823
1824 delete from fnd_request_group_units
1825 where (request_group_id, application_id) in
1826 (select request_group_id, application_id
1827 from fnd_request_groups
1828 where request_group_name = request_group
1829 and application_id = group_application_id);
1830
1831 delete from fnd_request_groups
1832 where request_group_name = request_group
1833 and application_id = group_application_id;
1834
1835 END delete_group;
1836
1837
1838 -- Procedure
1839 -- Delete_Incompatibility
1840 --
1841 -- Purpose
1842 -- Delete a concurrent program incompatibility rule.
1843 --
1844 -- Arguments
1845 -- program_short_name - Short name of the first program. (e.g. FNDSCRMT)
1846 -- application - Application of the first program.
1847 -- (e.g. 'Application Object Library')
1848 -- inc_prog_short_name - Short name of the incompatible program.
1849 -- inc_prog_application- Application of the incompatible program.
1850 --
1851 PROCEDURE delete_incompatibility(program_short_name IN VARCHAR2,
1852 application IN VARCHAR2,
1853 inc_prog_short_name IN VARCHAR2,
1854 inc_prog_application IN VARCHAR2)
1855 IS
1856 application_id_i fnd_application.application_id%TYPE;
1857 concurrent_program_id_i
1858 fnd_concurrent_programs.concurrent_program_id%TYPE;
1859 to_run_concurrent_program_id_i
1860 fnd_concurrent_programs.concurrent_program_id%TYPE;
1861 to_run_application_id_i fnd_application.application_id%TYPE;
1862 scope_code_i fnd_concurrent_program_serial.to_run_type%TYPE;
1863 dummy varchar2(1);
1864 BEGIN
1865 message_init;
1866
1867 check_notnull(program_short_name);
1868 check_notnull(application);
1869 check_notnull(inc_prog_short_name);
1870 check_notnull(inc_prog_application);
1871
1872 application_id_i := application_id_f(application);
1873 to_run_application_id_i :=
1874 application_id_f(inc_prog_application);
1875
1876 concurrent_program_id_i :=
1877 concurrent_program_id_f(application_id_i, program_short_name);
1878
1879 to_run_concurrent_program_id_i :=
1880 concurrent_program_id_f(to_run_application_id_i, inc_prog_short_name);
1881
1882 delete from fnd_concurrent_program_serial
1883 where to_run_application_id = to_run_application_id_i
1884 and to_run_concurrent_program_id = to_run_concurrent_program_id_i
1885 and running_application_id = application_id_i
1886 and running_concurrent_program_id = concurrent_program_id_i;
1887
1888 delete from fnd_concurrent_program_serial
1889 where to_run_application_id = application_id_i
1890 and to_run_concurrent_program_id = concurrent_program_id_i
1891 and running_application_id = to_run_application_id_i
1892 and running_concurrent_program_id = to_run_concurrent_program_id_i;
1893
1894 /* update incompatibilies */
1895 BEGIN
1896 update fnd_concurrent_programs
1897 set queue_method_code = 'I'
1898 WHERE application_id = application_id_i
1899 AND concurrent_program_id = concurrent_program_id_i
1900 AND NOT EXISTS
1901 (select 'x'
1902 from fnd_concurrent_program_serial
1903 where running_application_id = application_id_i
1904 and running_concurrent_program_id =
1905 concurrent_program_id_i);
1906 EXCEPTION
1907 when no_data_found then null;
1908 END;
1909
1910 BEGIN
1911 update fnd_concurrent_programs
1912 set queue_method_code = 'I'
1913 WHERE application_id = to_run_application_id_i
1914 AND concurrent_program_id = to_run_concurrent_program_id_i
1915 AND NOT EXISTS
1916 (select 'x'
1917 from fnd_concurrent_program_serial
1918 where running_application_id = to_run_application_id_i
1919 and running_concurrent_program_id =
1920 to_run_concurrent_program_id_i);
1921 EXCEPTION
1922 when no_data_found then null;
1923 END;
1924
1925 END delete_incompatibility;
1926
1927
1928 -- Procedure
1929 -- enable_program
1930 --
1931 -- Purpose
1932 -- enable or disable the concurrent program.
1933 --
1934 -- Arguments
1935 -- program_short_name - Short name of the program.
1936 -- program_application - Application of the program.
1937 -- enabled - 'Y' or 'N' values.
1938 --
1939 PROCEDURE enable_program(short_name IN VARCHAR2,
1940 application IN VARCHAR2,
1941 enabled IN VARCHAR2)
1942 IS
1943
1944 last_update_login_p fnd_flex_value_sets.last_update_login%TYPE
1948 last_updated_by_p fnd_flex_value_sets.last_updated_by%TYPE
1945 := last_update_login_f;
1946 last_update_date_p fnd_flex_value_sets.last_update_date%TYPE
1947 := last_update_date_f;
1949 := last_updated_by_f;
1950 creation_date fnd_flex_value_sets.creation_date%TYPE
1951 := creation_date_f;
1952 created_by fnd_flex_value_sets.created_by%TYPE
1953 := created_by_f;
1954
1955 application_id_p fnd_application.application_id%TYPE;
1956 concurrent_program_id_p
1957 fnd_concurrent_programs.concurrent_program_id%TYPE;
1958 temp_enabled_flag VARCHAR2(10);
1959
1960 BEGIN
1961
1962 /* Check to ensure caller passed in all parameters */
1963 check_notnull(short_name);
1964 check_notnull(application);
1965 check_notnull(enabled);
1966
1967 /* Check to ensure caller passed in Y or N for enabled */
1968 check_yesno(enabled);
1969
1970
1971 /* Get the application id based on the application code
1972 passed in */
1973 application_id_p := application_id_f(application);
1974
1975 /* Make sure the concurrent program exists */
1976 BEGIN
1977 SELECT concurrent_program_id
1978 INTO concurrent_program_id_p
1979 FROM fnd_concurrent_programs
1980 WHERE application_id = application_id_p
1981 AND concurrent_program_name = upper(short_name);
1982
1983 EXCEPTION
1984 when no_data_found THEN
1985 message('fnd_program.enable_program: Program Not Found');
1986 RAISE bad_parameter;
1987 when others then
1988 message('fnd_program.enable_program: '||SQLERRM);
1989 RETURN;
1990 END;
1991
1992 /* Now go and update the concurrent program table */
1993 UPDATE fnd_concurrent_programs
1994 SET enabled_flag = enabled,
1995 last_update_date = last_update_date_p,
1996 last_updated_by = last_updated_by_p,
1997 last_update_login = last_update_login_p
1998 WHERE application_id = application_id_p
1999 AND concurrent_program_id = concurrent_program_id_p;
2000
2001 EXCEPTION
2002 when OTHERS then
2003 message('Error updating concurrent program');
2004 message(SQLERRM);
2005 return;
2006
2007 END enable_program;
2008
2009
2010 -- Function
2011 -- PROGRAM_EXISTS
2012 --
2013 -- Purpose
2014 -- Return TRUE if a concurrent program exists.
2015 --
2016 -- Arguments
2017 -- program - Short name of the program.
2018 -- application - Application short name of the program.
2019 --
2020 FUNCTION program_exists(program IN VARCHAR2,
2021 application IN VARCHAR2) RETURN BOOLEAN is
2022 program_application_id fnd_application.application_id%TYPE;
2023 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
2024 BEGIN
2025 message_init;
2026
2027 program_application_id := application_id_f(application);
2028 program_id := concurrent_program_id_f(program_application_id, program);
2029
2030 return TRUE;
2031
2032 EXCEPTION
2033 when others then
2034 return FALSE;
2035 END program_exists;
2036
2037
2038
2039
2040 -- Function
2041 -- PARAMETER_EXISTS
2042 --
2043 -- Purpose
2044 -- Return TRUE if a program parameter exists.
2045 --
2046 -- Arguments
2047 -- program_short_name - Short name of program.
2048 -- application - Application short name of the program.
2049 -- parameter - Name of the parameter.
2050 --
2051 FUNCTION parameter_exists(program_short_name IN VARCHAR2,
2052 application IN VARCHAR2,
2053 parameter IN VARCHAR2) RETURN BOOLEAN is
2054
2055 program_appl_id fnd_application.application_id%TYPE;
2056 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
2057 desc_flex_name fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
2058 prog_appl_short_name fnd_application.application_short_name%TYPE;
2059 dummy varchar2(1);
2060
2061 BEGIN
2062 message_init;
2063
2064 program_appl_id := application_id_f(application);
2065
2066 desc_flex_name := '$SRS$.'||program_short_name;
2067
2068
2069 select 'y'
2070 into dummy
2071 from fnd_descr_flex_column_usages
2072 where application_id = program_appl_id
2073 and descriptive_flexfield_name = desc_flex_name
2074 and descriptive_flex_context_code = 'Global Data Elements'
2075 and end_user_column_name = parameter;
2076
2077 return TRUE;
2078
2079 EXCEPTION
2080 when others then
2081 return FALSE;
2082 END parameter_exists;
2083
2084
2085
2086
2087 -- Function
2088 -- INCOMPATIBILITY_EXISTS
2089 --
2090 -- Purpose
2091 -- Return TRUE if a program incompatibility exists.
2092 --
2093 -- Arguments
2094 -- program_short_name - Short name of the first program.
2095 -- application - Application short name of the first program.
2096 -- inc_prog_short_name - Short name of the incompatible program.
2097 -- inc_prog_application- Application short name of the incompatible program.
2098 --
2099 FUNCTION incompatibility_exists(program_short_name IN VARCHAR2,
2100 application IN VARCHAR2,
2104
2101 inc_prog_short_name IN VARCHAR2,
2102 inc_prog_application IN VARCHAR2)
2103 RETURN BOOLEAN is
2105 application_id_i fnd_application.application_id%TYPE;
2106 concurrent_program_id_i fnd_concurrent_programs.concurrent_program_id%TYPE;
2107 to_run_concurrent_program_id_i
2108 fnd_concurrent_programs.concurrent_program_id%TYPE;
2109 to_run_application_id_i fnd_application.application_id%TYPE;
2110 scope_code_i fnd_concurrent_program_serial.to_run_type%TYPE;
2111 dummy varchar2(1);
2112
2113 BEGIN
2114 message_init;
2115
2116 application_id_i := application_id_f(application);
2117 to_run_application_id_i := application_id_f(inc_prog_application);
2118
2119 concurrent_program_id_i :=
2120 concurrent_program_id_f(application_id_i, program_short_name);
2121
2122 to_run_concurrent_program_id_i :=
2123 concurrent_program_id_f(to_run_application_id_i, inc_prog_short_name);
2124
2125 select 'Y'
2126 into dummy
2127 from fnd_concurrent_program_serial
2128 where to_run_application_id = to_run_application_id_i
2129 and to_run_concurrent_program_id = to_run_concurrent_program_id_i
2130 and running_application_id = application_id_i
2131 and running_concurrent_program_id = concurrent_program_id_i;
2132
2133 return TRUE;
2134
2135 EXCEPTION
2136 when others then
2137 return FALSE;
2138 END incompatibility_exists;
2139
2140
2141
2142
2143 -- Function
2144 -- EXECUTABLE_EXISTS
2145 --
2146 -- Purpose
2147 -- Return TRUE if a program executable exists.
2148 --
2149 -- Arguments
2150 -- executable_short_name - Short name of the executable.
2151 -- application - Application short name of the executable.
2152 --
2153 FUNCTION executable_exists(executable_short_name IN VARCHAR2,
2154 application IN VARCHAR2) RETURN BOOLEAN is
2155
2156 exec_application_id fnd_application.application_id%TYPE;
2157 dummy varchar2(1);
2158
2159 BEGIN
2160 message_init;
2161
2162 exec_application_id := application_id_f(application);
2163
2164 select 'y'
2165 into dummy
2166 from fnd_executables
2167 where application_id = exec_application_id
2168 and executable_name = executable_short_name;
2169
2170 return TRUE;
2171
2172 EXCEPTION
2173 when others then
2174 return FALSE;
2175 END executable_exists;
2176
2177
2178
2179
2180 -- Function
2181 -- REQUEST_GROUP_EXISTS
2182 --
2183 -- Purpose
2184 -- Return TRUE if a request group exists.
2185 --
2186 -- Arguments
2187 -- group - Name of the group.
2188 -- application - Application short name of the executable.
2189 --
2190 FUNCTION request_group_exists(request_group IN VARCHAR2,
2191 application IN VARCHAR2) RETURN BOOLEAN is
2192
2193 group_application_id fnd_application.application_id%TYPE;
2194 dummy varchar2(1);
2195
2196 BEGIN
2197 message_init;
2198
2199 group_application_id := application_id_f(application);
2200
2201 select 'y'
2202 into dummy
2203 from fnd_request_groups
2204 where request_group_name = request_group
2205 and application_id = group_application_id;
2206
2207 return TRUE;
2208
2209 EXCEPTION
2210 when others then
2211 return FALSE;
2212 END request_group_exists;
2213
2214
2215
2216
2217 -- Function
2218 -- PROGRAM_IN_GROUP
2219 --
2220 -- Purpose
2221 -- Returns true if a program is in a request group.
2222 --
2223 -- Arguments
2224 -- program_short_name - Short name of the program.
2225 -- program_application - Application of the program.
2226 -- request_group - Name of request group.
2227 -- group_application - Application of the request group.
2228 --
2229 FUNCTION program_in_group(program_short_name IN VARCHAR2,
2230 program_application IN VARCHAR2,
2231 request_group IN VARCHAR2,
2232 group_application IN VARCHAR2) RETURN BOOLEAN is
2233
2234 program_application_id fnd_application.application_id%TYPE;
2235 group_application_id fnd_application.application_id%TYPE;
2236 concurrent_program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
2237 group_id fnd_request_groups.request_group_id%TYPE;
2238 dummy varchar2(1);
2239
2240 BEGIN
2241 message_init;
2242
2243 program_application_id := application_id_f(program_application);
2244 group_application_id := application_id_f(group_application);
2245
2246 concurrent_program_id :=
2247 concurrent_program_id_f(program_application_id, program_short_name);
2248
2249 group_id := request_group_id_f(group_application_id, request_group);
2250
2251 select 'y'
2252 into dummy
2253 from fnd_request_group_units u
2254 where application_id = group_application_id
2255 and request_group_id = group_id
2256 and request_unit_type = 'P'
2260 return TRUE;
2257 and unit_application_id = program_application_id
2258 and request_unit_id = concurrent_program_id;
2259
2261
2262 EXCEPTION
2263 when others then
2264 return FALSE;
2265 END program_in_group;
2266
2267 -- Procedure
2268 -- ADD_APPLICATION_TO_GROUP
2269 --
2270 -- Purpose
2271 -- Add a applicaiton to a request group.
2272 --
2273 -- Arguments
2274 -- application_name - Application of the program.
2275 -- (e.g. 'Application Object Library')
2276 -- request_group - Name of request group.
2277 -- group_application - Application of the request group.
2278 --
2279 PROCEDURE add_application_to_group(
2280 application_name IN VARCHAR2,
2281 request_group IN VARCHAR2,
2282 group_application IN VARCHAR2) is
2283 last_update_login fnd_request_groups.last_update_login%TYPE
2284 := last_update_login_f;
2285 last_update_date fnd_request_groups.last_update_date%TYPE
2286 := last_update_date_f;
2287 last_updated_by fnd_request_groups.last_updated_by%TYPE
2288 := last_updated_by_f;
2289 creation_date fnd_request_groups.creation_date%TYPE
2290 := creation_date_f;
2291 created_by fnd_request_groups.created_by%TYPE
2292 := created_by_f;
2293 application_id
2294 fnd_application.application_id%TYPE;
2295 group_application_id
2296 fnd_application.application_id%TYPE;
2297 group_id
2298 fnd_request_groups.request_group_id%TYPE;
2299 BEGIN
2300 message_init;
2301
2302 check_notnull(application_name);
2303 check_notnull(request_group);
2304 check_notnull(group_application);
2305
2306 application_id := application_id_f(application_name);
2307 group_application_id := application_id_f(group_application);
2308
2309
2310 group_id := request_group_id_f(group_application_id, request_group);
2311
2312 insert into Fnd_Request_Group_Units(APPLICATION_ID,
2313 REQUEST_GROUP_ID,
2314 REQUEST_UNIT_TYPE,
2315 UNIT_APPLICATION_ID,
2316 REQUEST_UNIT_ID,
2317 LAST_UPDATE_DATE,
2318 LAST_UPDATED_BY,
2319 CREATION_DATE,
2320 CREATED_BY,
2321 LAST_UPDATE_LOGIN)
2322 values( group_application_id,
2323 group_id,
2324 'A',
2325 application_id,
2326 application_id,
2327 last_update_date,
2328 last_updated_by,
2329 creation_date,
2330 created_by,
2331 last_update_login);
2332
2333 println('Added Application '|| application_name || ' to group '
2334 ||request_group);
2335
2336 END;
2337
2338 -- Procedure
2339 -- REMOVE_APPLICATION_FROM_GROUP
2340 --
2341 -- Purpose
2342 -- Remove a application from a request group.
2343 --
2344 -- Arguments
2345 -- application_name - Application of the program.
2346 -- (e.g. 'Application Object Library')
2347 -- request_group - Name of request group.
2348 -- group_application - Application of the request group.
2349 --
2350 PROCEDURE remove_application_from_group(
2351 application_name IN VARCHAR2,
2352 request_group IN VARCHAR2,
2353 group_application IN VARCHAR2) is
2354 application_id
2355 fnd_application.application_id%TYPE;
2356 group_application_id
2357 fnd_application.application_id%TYPE;
2358 group_id
2359 fnd_request_groups.request_group_id%TYPE;
2360 BEGIN
2361 message_init;
2362
2363 check_notnull(application_name);
2364 check_notnull(request_group);
2365 check_notnull(group_application);
2366
2367 application_id := application_id_f(application_name);
2368 group_application_id := application_id_f(group_application);
2369
2370
2371 group_id := request_group_id_f(group_application_id, request_group);
2372
2373 delete from fnd_request_group_units u
2374 where application_id = group_application_id
2375 and request_group_id = group_id
2376 and request_unit_type = 'A'
2377 and request_unit_id = application_id;
2378 END;
2379
2380
2381 -- Function
2382 -- APPLICATION_IN_GROUP
2383 --
2384 -- Purpose
2385 -- Returns true if a program is in a request group.
2386 --
2387 -- Arguments
2388 -- application_name - Application of the program.
2389 -- request_group - Name of request group.
2390 -- group_application - Application of the request group.
2391 --
2392 FUNCTION application_in_group(application_name IN VARCHAR2,
2393 request_group IN VARCHAR2,
2394 group_application IN VARCHAR2) RETURN BOOLEAN is
2395
2396 application_id fnd_application.application_id%TYPE;
2397 group_application_id fnd_application.application_id%TYPE;
2398 group_id fnd_request_groups.request_group_id%TYPE;
2399 dummy varchar2(1);
2400
2401 BEGIN
2402 message_init;
2403
2404 application_id := application_id_f(application_name);
2405 group_application_id := application_id_f(group_application);
2406
2407 group_id := request_group_id_f(group_application_id, request_group);
2408
2409 select 'y'
2410 into dummy
2411 from fnd_request_group_units u
2412 where application_id = group_application_id
2413 and request_group_id = group_id
2414 and request_unit_type = 'A'
2415 and request_unit_id = application_id;
2416
2417 return TRUE;
2418
2419 EXCEPTION
2420 when others then
2421 return FALSE;
2422 END application_in_group;
2423
2424
2425 /* END_PUBLIC */
2426
2427 END fnd_program; /* end package */