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