[Home] [Help]
PACKAGE BODY: APPS.FND_REQUEST
Source
1 package body FND_REQUEST as
2 /* $Header: AFCPREQB.pls 120.32.12020000.3 2013/02/19 21:11:12 ckclark ship $ */
3
4 --
5 -- Package
6 -- FND_REQUEST
7 --
8 -- Purpose
9 -- Concurrent processing related utilities
10 --
11 -- History
12 -- XX-XXX-93 Ram Bhoopalam Created
13 -- 08-FEB-94 H Pelimuhandiram Gave life
14 --
15
16 --
17 -- PRIVATE VARIABLES
18 --
19 P_OPS_ID integer := null;
20 P_REL_CLASS_APP_ID integer := null;
21 P_REL_CLASS_ID integer := null;
22 P_REL_STALE_DATE date := null;
23 P_REL_CANCEL_OR_HOLD varchar2(1) := 'H';
24 P_USES_REL_CLASS integer := 0;
25 P_ON_PP_ERR_NOTIFY varchar2(255) := null;
26 P_REPEAT_TIME varchar2(10) := null;
27 P_REPEAT_INTERVAL number(15,10) := null;
28 P_REPEAT_INTERVAL_UNIT varchar2(10) := null;
29 P_REPEAT_INTERVAL_TYPE varchar2(10) := null;
30 P_REPEAT_END date := null;
31 P_INCREMENT_DATES varchar2(1) := null;
32 P_PRINT_STYLE varchar2(30) := null;
33 P_SAVE_OUTPUT char := null;
34 P_PRINT_TOGETHER char := null;
35 P_PROTECTED char := 'N';
36 P_IMPLICIT varchar2(10) := 'N';
37 P_LANGUAGE varchar2(30) := null;
38 P_TERRITORY varchar2(30) := null;
39 /* NLS Project */
40 P_NUMERIC_CHARACTERS varchar2(2) := null;
41 P_DATAGROUP varchar2(30) := null;
42 P_DB_TRIGGER_MODE boolean := FALSE;
43 P_CRITICAL_REQUEST varchar2(1) := null;
44 P_REQUEST_TYPE varchar2(1) := NULL;
45 P_DEFERRED_MODE boolean := FALSE;
46 P_DEF_REQUEST_ID number := 0;
47 P_ORG_ID integer := null;
48 SQL_WAITS NUMBER := 8 ;
49 P_VALIDATE_PRINTER varchar2(10) := 'RESOLVE';
50 P_NODE_NAME1 varchar2(30) := null;
51 P_CONNSTR1 varchar2(255) := null;
52
53
54 TYPE printer_record_type is record
55 ( printer varchar2(30),
56 copies number,
57 lang varchar2(30));
58
59 TYPE printer_tab_type is table of printer_record_type
60 index by binary_integer;
61
62 TYPE notification_record_type is record
63 ( name varchar2(100),
64 orig_system varchar2(48),
65 orig_system_id number,
66 on_normal varchar2(1),
67 on_warning varchar2(1),
68 on_error varchar2(1),
69 lang varchar2(30));
70
71 TYPE notification_tab_type is table of notification_record_type
72 index by binary_integer;
73
74
75 TYPE nls_parameters_record_type is record
76 ( nls_lang varchar2(30),
77 nls_territory varchar2(30),
78 nls_num_char varchar2(2));
79
80 TYPE nls_parameters_tab_type is table of nls_parameters_record_type
81 index by binary_integer;
82
83 TYPE layout_record_type is record
84 ( template_appl_name varchar2(255),
85 template_code varchar2(255),
86 template_language varchar2(255),
87 template_territory varchar2(255),
88 output_format varchar2(255),
89 nls_language varchar2(30));
90
91 TYPE layout_tab_type is table of layout_record_type
92 index by binary_integer;
93
94 TYPE delivery_record_type is record
95 ( argument1 varchar2(255),
96 argument2 varchar2(255),
97 argument3 varchar2(255),
98 argument4 varchar2(255),
99 argument5 varchar2(255),
100 argument6 varchar2(255),
101 argument7 varchar2(255),
102 argument8 varchar2(255),
103 argument9 varchar2(255),
104 argument10 varchar2(255),
105 lang varchar2(30));
106
107 TYPE delivery_tab_type is table of delivery_record_type
108 index by binary_integer;
109
110
111 P_PRINTERS printer_tab_type;
112 P_PRINTER_COUNT number := 0;
113
114 P_NOTIFICATIONS notification_tab_type;
115 P_NOTIFICATION_COUNT number := 0;
116
117 P_NLS_PARAMETERS nls_parameters_tab_type;
118 P_NLS_PARAMETERS_COUNT number := 0;
119
120 P_LAYOUTS layout_tab_type;
121 P_LAYOUT_COUNT number := 0;
122
123 P_DELIVERY_OPTIONS delivery_tab_type;
124 P_DELIV_OPTS_COUNT number := 0;
125
126
127 -- Exception info.
128
129 --
130 -- PRIVATE FUNCTIONS
131 --
132 -- --
133
134 -- Name
135 -- init_pvt_vars
136 -- Purpose
137 -- Called after submitting request to re-initialize repeat options
138 --
139 -- --
140
141 procedure init_pvt_vars is
142 empty_array printer_tab_type;
143 empty_notify_array notification_tab_type;
144 empty_nls_params nls_parameters_tab_type;
145 empty_layouts layout_tab_type;
146 empty_delivery delivery_tab_type;
147
148 begin
149 P_OPS_ID := null;
150 P_REL_CLASS_APP_ID := null;
151 P_REL_CLASS_ID := null;
152 P_REL_STALE_DATE := null;
153 P_REL_CANCEL_OR_HOLD := 'H';
154 P_USES_REL_CLASS := 0;
155 P_ON_PP_ERR_NOTIFY := null;
156 P_REPEAT_TIME := null;
157 P_REPEAT_INTERVAL := null;
158 P_REPEAT_INTERVAL_UNIT := null;
159 P_REPEAT_INTERVAL_TYPE := null;
160 P_REPEAT_END := null;
161 P_PRINT_STYLE := null;
162 P_SAVE_OUTPUT := null;
163 P_PRINT_TOGETHER := null;
164 P_PROTECTED := 'N';
165 P_IMPLICIT := 'N';
166 P_LANGUAGE := null;
167 P_TERRITORY := null;
168 /* NLS Project */
169 P_NUMERIC_CHARACTERS := null;
170 P_DATAGROUP := null;
171 P_DB_TRIGGER_MODE := FALSE;
172 P_CRITICAL_REQUEST := null;
173 P_REQUEST_TYPE := NULL;
174 P_PRINTERS := empty_array;
175 P_PRINTER_COUNT := 0;
176 P_NOTIFICATIONS := empty_notify_array;
177 P_NOTIFICATION_COUNT := 0;
178 P_DEFERRED_MODE := FALSE;
179 P_DEF_REQUEST_ID := 0;
180 P_ORG_ID := null;
181 P_NLS_PARAMETERS_COUNT := 0;
182 P_NLS_PARAMETERS := empty_nls_params;
183 P_LAYOUT_COUNT := 0;
184 P_LAYOUTS := empty_layouts;
185 P_DELIV_OPTS_COUNT := 0;
186 P_DELIVERY_OPTIONS := empty_delivery;
187
188 end init_pvt_vars;
189
190 -- Name
191 -- get_request_set_priority
192 -- Purpose
193 -- The priority of a request set is
194 -- equal to the priority of its highest
195 -- priority member.
196 --
197 -- --
198 function get_request_set_priority (
199 app_id IN number,
200 set_id IN number
201 ) return number is
202 program_priority number;
203 profile_priority_char varchar2(3);
204 profile_priority_number number;
205 begin
206 FND_PROFILE.GET ('CONC_PRIORITY', profile_priority_char);
207 profile_priority_number := to_number(profile_priority_char);
208
209 Select min(nvl(request_priority, profile_priority_number))
210 Into program_priority
211 From Fnd_Concurrent_Programs P,
212 Fnd_Request_Set_Programs S
213 Where S.set_application_id = app_id
214 and S.request_set_id = set_id
215 and P.concurrent_program_id = S.concurrent_program_id
216 and P.application_id = S.program_application_id;
217
218 return program_priority;
219 exception
220 when NO_DATA_FOUND then
221 return null;
222 end;
223
224 procedure set_dest_ops(ops_id IN number default NULL) is
225
226 begin
227 P_OPS_ID := ops_id;
228 end;
229
230
231 procedure set_org_id(org_id IN number default NULL) is
232
233 begin
234 P_ORG_ID := org_id;
235 end;
236
237 -- Name
238 -- is_trace_enabled
239 -- Purpose
240 -- Check whether the trace is enabled for the current db session
241 -- Added this function to fix 4438376
242
243 function is_trace_enabled(trace_type in number) return boolean is
244 event_level number;
245 trace_on boolean;
246 begin
247 if(trace_type < 20) then
248 dbms_system.read_ev(10046,event_level);
249 else
250 dbms_system.read_ev(trace_type,event_level);
251 end if;
252
253 if event_level > 0 then
254 trace_on:= true;
255 else
256 trace_on:= false;
257 end if;
258 return trace_on;
259 end;
260
261 -- Name
262 -- submit
263 -- Purpose
264 -- Submit concurrent request
265 --
266 -- --
267
268 function submit (
269 application IN varchar2,
270 program IN varchar2,
271 description IN varchar2,
272 start_time IN varchar2,
273 sub_request IN boolean,
274 argument1 IN varchar2,
275 argument2 IN varchar2,
276 argument3 IN varchar2,
277 argument4 IN varchar2,
278 argument5 IN varchar2,
279 argument6 IN varchar2,
280 argument7 IN varchar2,
281 argument8 IN varchar2,
282 argument9 IN varchar2,
283 argument10 IN varchar2,
284 argument11 IN varchar2,
285 argument12 IN varchar2,
286 argument13 IN varchar2,
287 argument14 IN varchar2,
288 argument15 IN varchar2,
289 argument16 IN varchar2,
290 argument17 IN varchar2,
291 argument18 IN varchar2,
292 argument19 IN varchar2,
293 argument20 IN varchar2,
294 argument21 IN varchar2,
295 argument22 IN varchar2,
296 argument23 IN varchar2,
297 argument24 IN varchar2,
298 argument25 IN varchar2,
299 argument26 IN varchar2,
300 argument27 IN varchar2,
301 argument28 IN varchar2,
302 argument29 IN varchar2,
303 argument30 IN varchar2,
304 argument31 IN varchar2,
305 argument32 IN varchar2,
306 argument33 IN varchar2,
307 argument34 IN varchar2,
308 argument35 IN varchar2,
309 argument36 IN varchar2,
310 argument37 IN varchar2,
311 argument38 IN varchar2,
312 argument39 IN varchar2,
313 argument40 IN varchar2,
314 argument41 IN varchar2,
315 argument42 IN varchar2,
316 argument43 IN varchar2,
317 argument44 IN varchar2,
318 argument45 IN varchar2,
319 argument46 IN varchar2,
320 argument47 IN varchar2,
321 argument48 IN varchar2,
322 argument49 IN varchar2,
323 argument50 IN varchar2,
324 argument51 IN varchar2,
325 argument52 IN varchar2,
326 argument53 IN varchar2,
327 argument54 IN varchar2,
328 argument55 IN varchar2,
329 argument56 IN varchar2,
330 argument57 IN varchar2,
331 argument58 IN varchar2,
332 argument59 IN varchar2,
333 argument60 IN varchar2,
334 argument61 IN varchar2,
335 argument62 IN varchar2,
336 argument63 IN varchar2,
337 argument64 IN varchar2,
338 argument65 IN varchar2,
339 argument66 IN varchar2,
340 argument67 IN varchar2,
341 argument68 IN varchar2,
342 argument69 IN varchar2,
343 argument70 IN varchar2,
344 argument71 IN varchar2,
345 argument72 IN varchar2,
346 argument73 IN varchar2,
347 argument74 IN varchar2,
348 argument75 IN varchar2,
349 argument76 IN varchar2,
350 argument77 IN varchar2,
351 argument78 IN varchar2,
352 argument79 IN varchar2,
353 argument80 IN varchar2,
354 argument81 IN varchar2,
355 argument82 IN varchar2,
356 argument83 IN varchar2,
357 argument84 IN varchar2,
358 argument85 IN varchar2,
359 argument86 IN varchar2,
360 argument87 IN varchar2,
361 argument88 IN varchar2,
362 argument89 IN varchar2,
363 argument90 IN varchar2,
364 argument91 IN varchar2,
365 argument92 IN varchar2,
366 argument93 IN varchar2,
367 argument94 IN varchar2,
368 argument95 IN varchar2,
369 argument96 IN varchar2,
370 argument97 IN varchar2,
371 argument98 IN varchar2,
372 argument99 IN varchar2,
373 argument100 IN varchar2)
374 return number is
375
376 submit_ops_id number;
377 cur_request_id number;
378 cur_req_pri_id number;
379 par_request_id number;
380 fgloginid number;
381 pri_request_id number;
382 resp_id number := null;
383 resp_appl_id number := null;
384 user_id number := null;
385 oraclacct varchar2(30);
386 conf_set_id number;
387 prog_appl_id number;
388 conc_prog_id number;
389 loginid number := null;
390 subloginid number := null;
391 hold_request varchar2(3);
392 snth_request varchar2(3);
393 language varchar2(15);
394 req_status varchar2(15);
395 requested_start date;
396 priority number := 50;
397 hold_flag varchar2(3) := 'N';
398 st_flag varchar2(3) := 'N';
399 profile_buffer varchar2(80) := null;
400 request_threshold number := -1;
401 print_copies number := 0;
402 req_limit char;
403 phase char := 'P';
404 status char := 'Q';
405 issubreq char := 'N';
406 has_parent boolean := FALSE;
407 program_priority number;
408
409 cd_id number := -1; -- Conf domain id
410 cd_param varchar2(150) := null; -- Conf domain param name
411
412 default_copies number;
413 default_printer varchar2(30);
414 fcr_printer varchar2(30);
415 fcp_printer varchar2(30);
416 curr_printer varchar2(30);
417 curr_copies number;
418 tot_copies number := 0;
419 print_style varchar2(30);
420 valid_style varchar2(30) := null;
421 print_warning char := 'N';
422 reqrd_flag char;
423 minwid number(4); -- bug3205755
424 maxwid number(4) := null; -- bug3205755
425 minlen number(4); -- bug3205755
426 maxlen number(4) := null; -- bug3205755
427 execcode char;
428 saveout char;
429 prtflg char;
430 mult_pending_req char;
431 qctlflg char;
432 styl_ok boolean := FALSE;
433 nargs number := 0;
434 argtxt varchar2(24200) := null;
435 argtxt2 varchar2(240);
436 dummy char;
437 request_set_flag varchar2(1);
438 base_len number;
439 tz_offset number(1);
440 encoded_msg varchar2(4000);
441 -- bug 1518406 define a local name
442 z_security_group_id number;
443
444 portlet_ref varchar2(1) := 'N'; -- 'N'ever (no pp plsql actions)
445 p_on_success varchar2(1) := 'N';
446 p_on_warning varchar2(1) := 'N';
447 p_on_error varchar2(1) := 'N';
448 pp_plsql_step number := -1;
449
450 printer_error exception;
451 style_error exception;
452 srw_style_error exception;
453 printer_styl_error exception;
454 insert_error exception;
455 args_insert_error exception;
456 login_insert_error exception;
457 relclass_insert_error exception;
458 dual_no_rows exception;
459 dual_too_many_rows exception;
460 nls_error exception;
461 appl_prog_error exception;
462 start_time_error exception;
463 already_msg exception;
464 oracle_name_error exception;
465 pp_plsql_exception exception;
466 conc_deferred_insert_error exception;
467 mult_pending_request_error exception;
468
469 rttestid number;
470 i number;
471 new_class boolean;
472 schedule_type varchar2(1);
473 schedule_info varchar2(64);
474 sch_req_date date;
475 sch_end_date date;
476
477 no_pending_req number;
478 lang_exists number;
479 adv_sch_ri number(15,10);
480 adv_sch_riuc varchar2(30);
481 adv_sch_ritc varchar2(30);
482 trace_enabled varchar2(1) :='N';
483 discard_status number;
484
485 begin
486 -- Get next request ID from sequence
487 -- only if it is a normal call without P_DEFERRED_MODE or its deferred call
488 if ( (P_DEFERRED_MODE) OR (P_DEF_REQUEST_ID = 0)) then
489 begin
490 Select Fnd_Concurrent_Requests_S.nextval
491 Into cur_request_id
492 From Sys.Dual;
493 exception
494 when no_data_found then
495 raise dual_no_rows;
496 when too_many_rows then
497 raise dual_too_many_rows;
498 when others then
499 raise;
500 end;
501 end if;
502
503 if (P_DEFERRED_MODE) then
504 Insert Into Fnd_Conc_Deferred_Arguments (
505 request_id, Program_Application_Id, Concurrent_Program_Id,
506 Argument1, Argument2, Argument3, Argument4, Argument5,
507 Argument6, Argument7, Argument8, Argument9, Argument10,
508 Argument11, Argument12, Argument13, Argument14, Argument15,
509 Argument16, Argument17, Argument18, Argument19, Argument20,
510 Argument21, Argument22, Argument23, Argument24, Argument25,
511 Argument26, Argument27, Argument28, Argument29, Argument30,
512 Argument31, Argument32, Argument33, Argument34, Argument35,
513 Argument36, Argument37, Argument38, Argument39, Argument40,
514 Argument41, Argument42, Argument43, Argument44, Argument45,
515 Argument46, Argument47, Argument48, Argument49, Argument50,
516 Argument51, Argument52, Argument53, Argument54, Argument55,
517 Argument56, Argument57, Argument58, Argument59, Argument60,
518 Argument61, Argument62, Argument63, Argument64, Argument65,
519 Argument66, Argument67, Argument68, Argument69, Argument70,
520 Argument71, Argument72, Argument73, Argument74, Argument75,
521 Argument76, Argument77, Argument78, Argument79, Argument80,
522 Argument81, Argument82, Argument83, Argument84, Argument85,
523 Argument86, Argument87, Argument88, Argument89, Argument90,
524 Argument91, Argument92, Argument93, Argument94, Argument95,
525 Argument96, Argument97, Argument98, Argument99, Argument100,
526 Creation_Date, Created_By, Last_Update_Date, Last_Updated_By,
527 Last_Update_Login)
528 Select cur_request_id, FA.Application_ID, FCP.Concurrent_Program_ID,
529 argument1, argument2, argument3, argument4, argument5,
530 argument6, argument7, argument8, argument9, argument10,
531 argument11, argument12, argument13, argument14, argument15,
532 argument16, argument17, argument18, argument19, argument20,
533 argument21, argument22, argument23, argument24, argument25,
534 argument26, argument27, argument28, argument29, argument30,
535 argument31, argument32, argument33, argument34, argument35,
536 argument36, argument37, argument38, argument39, argument40,
537 argument41, argument42, argument43, argument44, argument45,
538 argument46, argument47, argument48, argument49, argument50,
539 argument51, argument52, argument53, argument54, argument55,
540 argument56, argument57, argument58, argument59, argument60,
541 argument61, argument62, argument63, argument64, argument65,
542 argument66, argument67, argument68, argument69, argument70,
543 argument71, argument72, argument73, argument74, argument75,
544 argument76, argument77, argument78, argument79, argument80,
545 argument81, argument82, argument83, argument84, argument85,
546 argument86, argument87, argument88, argument89, argument90,
547 argument91, argument92, argument93, argument94, argument95,
548 argument96, argument97, argument98, argument99, argument100,
549 Sysdate, FND_GLOBAL.conc_login_id, Sysdate,
550 FND_GLOBAL.conc_login_id, FND_GLOBAL.conc_login_id
551 From Fnd_Application FA,
552 Fnd_Concurrent_Programs FCP
553 Where
554 FCP.Enabled_Flag = 'Y'
555 And (FCP.Application_ID = FA.Application_ID And
556 Concurrent_Program_Name = Upper (program))
557 And FA.Application_Short_Name = Upper (application);
558
559 if (sql%rowcount = 0) then
560 raise conc_deferred_insert_error;
561 end if;
562 --
563 -- raising business event for request submitted
564 -- bug6623316
565 --
566 discard_status:=fnd_concurrent_business_event.raise_cp_bi_event(cur_request_id,fnd_concurrent_business_event.request_submitted);
567
568 return (cur_request_id);
569 else
570 -- see if it is deferred call or normal call
571 if (P_DEF_REQUEST_ID <> 0) then
572 cur_request_id := P_DEF_REQUEST_ID;
573 end if;
574 end if;
575
576 new_class := FALSE;
577 -- Get info. about requests origination
578 -- If being submitted from another concurrent request get parent
579 -- request information.
580
581 fgloginid := fnd_global.login_id;
582 par_request_id := FND_GLOBAL.conc_request_id;
583
584 z_security_group_id := FND_GLOBAL.security_group_id;
585
586
587 if (par_request_id > 0) then
588 has_parent := TRUE;
589 pri_request_id := FND_GLOBAL.conc_priority_request;
590
591 -- bug 4438376. check whether the trace is enabled for the parent request.
592 -- if enabled, then enable the trace for child request also
593
594 if (is_trace_enabled(SQL_WAITS)) then
595 trace_enabled := 'Y';
596 else
597 trace_enabled := 'N';
598 end if;
599
600 loginid := FND_GLOBAL.conc_login_id;
601
602 if (loginid is null) then
603 loginid := 0;
604
605 else
606 begin
607 Select Submitted_Login_ID
608 Into subloginid
609 From Fnd_Logins
610 Where Login_ID = loginid;
611
612 exception
613 when no_data_found then
614 subloginid := 0;
615
616 when others then
617 raise;
618 end;
619
620 end if; -- (loginid is null)
621
622 else -- request being submitted from forms session
623 -- Get LOGIN_ID
624 subloginid := FND_GLOBAL.login_id;
625 pri_request_id := cur_request_id;
626
627 end if; -- (par_request_id > 0)
628
629
630 -- Common to both :
631
632 if (sub_request AND pri_request_id > 0) then
633 cur_req_pri_id := pri_request_id;
634 else
635 cur_req_pri_id := cur_request_id;
636 end if;
637
638 user_id := FND_GLOBAL.user_id;
639
640 if (user_id is null) then
641 user_id := -1;
642 end if;
643
644 resp_id := FND_GLOBAL.resp_id;
645
646 if (resp_id is null) then
647 resp_id := -1;
648 end if;
649
650 resp_appl_id := FND_GLOBAL.resp_appl_id;
651
652 if (resp_appl_id is null) then
653 resp_appl_id := -1;
654 end if;
655
656 FND_PROFILE.GET ('CONC_HOLD', hold_flag);
657 if (hold_flag is null) then
658 hold_flag := 'N';
659 end if;
660
661 FND_PROFILE.GET ('CONC_SINGLE_THREAD', st_flag);
662 if (st_flag is null) then
663 st_flag := 'N';
664 else
665 st_flag := substr (st_flag, 1, 1);
666 end if;
667
668 profile_buffer := NULL;
669 FND_PROFILE.GET ('CONC_REQUEST_THRESHOLD_LEVEL', profile_buffer);
670 if ( profile_buffer is null) then
671 request_threshold := -1;
672 else
673 request_threshold := To_Number (profile_buffer);
674 end if;
675
676 profile_buffer := NULL;
677 FND_PROFILE.GET ('CONC_REQUEST_LIMIT', profile_buffer);
678 if ( profile_buffer is null) then
679 req_limit := 'N';
680 elsif (To_Number(profile_buffer) >= 0 and (P_REQUEST_TYPE IS NULL or P_REQUEST_TYPE <> 'S')) then
681 req_limit := 'Y';
682 else
683 req_limit := 'N';
684 end if;
685
686 FND_PROFILE.GET ('CONC_STATUS', req_status);
687 if (req_status = 'HOLD') then
688 status := 'A';
689 elsif (sub_request) then
690 status := 'Z';
691 end if;
692
693 profile_buffer := NULL;
694 FND_PROFILE.GET('CONC_MULTI_TZ', profile_buffer);
695
696 if (profile_buffer in ('Y', 'y')) then
697 tz_offset := 1;
698 else
699 tz_offset := 0;
700 end if;
701
702 profile_buffer := NULL;
703 if (start_time is null) then
704 FND_PROFILE.GET ('CONC_REQ_START', profile_buffer);
705 else
706 profile_buffer := start_time;
707 end if;
708
709 if (profile_buffer is not null) then
710 requested_start := fnd_conc_date.string_to_date(profile_buffer);
711 if (requested_start is null) then
712 raise start_time_error;
713 end if;
714 else
715 requested_start := null;
716 end if;
717
718
719 -- Someone specified a schedule to use.
720 -- If it is a Specific schedule, figure out the correct start date to use.
721 -- If it is a Periodic schedule, populate the correct fields in FND_CONCURRENT_REQUESTS.
722 if (P_USES_REL_CLASS = 1) then
723 begin
724 select class_type, class_info, date1, date2
725 into schedule_type, schedule_info, sch_req_date, sch_end_date
726 from fnd_conc_release_classes
727 where application_id = P_REL_CLASS_APP_ID
728 and release_class_id = P_REL_CLASS_ID;
729
730 if schedule_type = 'S' then
731 -- subtract 1 from sch_req_date to account for the possibility that the
732 -- schedule may need to run on sch_req_date.
733 requested_start := fnd_conc_release_class_utils.calc_specific_startdate(sch_req_date - 1, schedule_info);
734
735 if requested_start > sch_end_date then
736 raise start_time_error;
737 end if;
738 P_REPEAT_END := sch_end_date;
739
740 elsif schedule_type = 'P' then
741 fnd_conc_release_class_utils.parse_periodic_schedule(P_REL_CLASS_APP_ID, P_REL_CLASS_ID,
742 P_REPEAT_INTERVAL,
743 P_REPEAT_INTERVAL_UNIT,
744 P_REPEAT_INTERVAL_TYPE,
745 sch_req_date,
746 P_REPEAT_END);
747
748
749 if sch_req_date > sch_end_date then
750 raise start_time_error;
751 end if;
752
753 requested_start := sch_req_date;
754 end if;
755
756 exception
757 when no_data_found then
758 requested_start := null;
759
760 end;
761 end if;
762
763 if (P_NODE_NAME1 is NULL) then
764 begin
765 select fcpoi.node_name1
766 into P_NODE_NAME1
767 from fnd_conc_prog_onsite_info fcpoi,
768 fnd_concurrent_programs fcp,
769 fnd_application fa
770 where fcp.application_id = fcpoi.program_application_id
771 and fcp.concurrent_program_id = fcpoi.concurrent_program_id
772 and fa.application_id = fcp.application_id
773 and fcp.concurrent_program_name = upper(program)
774 and fa.application_short_name = upper(application);
775 exception
776 when no_data_found then
777 P_NODE_NAME1 := null;
778 end;
779 end if;
780
781 if (P_CONNSTR1 is NULL) then
782 begin
783 select fcpoi.connstr1
784 into P_CONNSTR1
785 from fnd_conc_prog_onsite_info fcpoi,
786 fnd_concurrent_programs fcp,
787 fnd_application fa
788 where fcp.application_id = fcpoi.program_application_id
789 and fcp.concurrent_program_id = fcpoi.concurrent_program_id
790 and fa.application_id = fcp.application_id
791 and fcp.concurrent_program_name = upper(program)
792 and fa.application_short_name = upper(application);
793 exception
794 when no_data_found then
795 P_CONNSTR1 := null;
796 end;
797 end if;
798
799 if (P_PRINT_TOGETHER is NULL) then
800 FND_PROFILE.GET ('CONC_PRINT_TOGETHER', profile_buffer);
801 if (substr (profile_buffer, 1, 1) = 'Y') then
802 P_PRINT_TOGETHER := 'Y';
803 else
804 P_PRINT_TOGETHER := 'N';
805 end if;
806 end if;
807
808 if (sub_request) then
809 issubreq := 'Y';
810 end if;
811
812 -- Default NLS language
813 if (P_LANGUAGE is NULL) then
814 begin
815 select substr(userenv('LANGUAGE'),1,
816 instr(userenv('LANGUAGE'), '_') -1)
817 into P_LANGUAGE
818 from dual;
819
820 exception
821 when no_data_found then
822 raise nls_error;
823 when others then
824 raise;
825 end;
826 end if;
827
828 -- modified by ddhulla to check if the language passed by user is valid language
829 -- if the language specified is invalid raise nls_error
830 begin
831
832 Select count(*) into lang_exists from FND_LANGUAGES L
833 where NLS_LANGUAGE = P_LANGUAGE and
834 L.INSTALLED_FLAG in ('I', 'B');
835
836 if ( lang_exists = 0 ) then
837 raise nls_error;
838 end if;
839 end;
840
841 -- Default NLS territory
842 if (P_TERRITORY is NULL) then
843 begin
844 select substr ( userenv('LANGUAGE') ,
845 instr ( userenv('LANGUAGE') , '_') + 1,
846 (instr ( userenv('LANGUAGE') , '.') - 1 -
847 instr ( userenv('LANGUAGE') , '_') ))
848 into P_TERRITORY
849 from dual;
850
851 exception
852 when no_data_found then
853 raise nls_error;
854 when others then
855 raise;
856 end;
857 end if;
858
859 -- modified by ddhulla to check if the language passed by user is valid language
860 begin
861
862 Select count(*) into lang_exists from FND_LANGUAGES L
863 where NLS_LANGUAGE = P_LANGUAGE and
864 L.INSTALLED_FLAG in ('I', 'B');
865
866 if ( lang_exists = 0 ) then
867 raise nls_error;
868 end if;
869 end;
870
871 -- Default NLS numeric characters
872 if (P_NUMERIC_CHARACTERS is NULL) then
873 begin
874 select substr(value, 0, 2)
875 into P_NUMERIC_CHARACTERS
876 from NLS_SESSION_PARAMETERS
877 where parameter=FND_CONST.NLS_NUMERIC_CHARACTERS;
878
879 exception
880 when no_data_found then
881 raise nls_error;
882 when others then
883 raise;
884 end;
885 end if;
886
887 -- TAKE THIS OUT after testing
888 -- NLS Project : Default NLS Numeric Characters
889 -- VENKAT: indicates we want it to be NULL to start and it will be set
890 -- by the Terrirtory default.
891 -- if (P_NUMERIC_CHARACTERS is NULL) then
892 -- fnd_profile.get('ICX_NUMERIC_CHARACTERS', P_NUMERIC_CHARACTERS);
893 -- end if;
894
895 -- Get program's application ID, Concurrent Program Id, cd_param, Printer, Print Style,
896 -- Get program's application ID, Concurrent Program Id, cd_param, Printer, Print Style,
897 -- Save output flag, priority, submit ops id, request set flag
898 -- and Allow Multiple Pending Request Flag
899 begin
900 Select A.Application_ID, Concurrent_Program_Id, CD_Parameter,
901 Printer_Name, NVL(Output_Print_Style, 'PORTRAIT'),
902 Required_Style, Minimum_Width, Minimum_Length,
903 Execution_Method_Code, Save_Output_Flag, Print_Flag,
904 Queue_Control_Flag, request_priority, request_set_flag,
905 -1,
906 NVL(Refresh_Portlet, 'N'), NVL(Allow_Multiple_Pending_Request,'Y')
907 Into prog_appl_id, conc_prog_id, cd_param,
908 fcp_printer, print_style, reqrd_flag, minwid,
909 minlen, execcode, saveout, prtflg,
910 qctlflg, program_priority, request_set_flag,
911 submit_ops_id, portlet_ref, mult_pending_req
912 From Fnd_Concurrent_Programs P, Fnd_Application A
913 Where Upper(Concurrent_Program_Name) = upper(program)
914 And P.Application_ID = A.Application_ID
915 And Upper(A.Application_Short_Name) = upper(application);
916
917 exception
918 when no_data_found then
919 raise appl_prog_error;
920 when others then
921 raise;
922 end;
923
924
925 -- check if multiple pending requests are allowed.
926 if (mult_pending_req = 'N') then
927 -- see if there are some pending requests for this concurrent program
928 -- definition of pending: phase_code in P and hold_flag in N and
929 -- requested_start_date lessthan sysdate
930 begin
931 select count(*) into no_pending_req
932 from fnd_concurrent_requests
933 where program_application_id = prog_appl_id
934 And concurrent_program_id = conc_prog_id
935 And phase_code = 'P'
936 and hold_flag='N' and requested_start_date <= sysdate;
937
938 exception
939 when others then
940 raise;
941 end;
942 end if;
943
944 -- there are some pending requests
945 if (no_pending_req <> 0) then
946 raise mult_pending_request_error;
947 end if;
948
949
950 -- dishonour request set and stage priority bug #4172967
951 if ((P_REQUEST_TYPE = 'M') OR (P_REQUEST_TYPE = 'S')) then
952 program_priority := NULL;
953 end if;
954
955
956 if (request_set_flag = 'Y') then
957 program_priority := get_request_set_priority(
958 to_number(argument1),
959 to_number(argument2));
960 end if;
961
962 if (program_priority is null) then
963 profile_buffer := NULL;
964 FND_PROFILE.GET ('CONC_PRIORITY', profile_buffer);
965 priority := to_number(profile_buffer);
966 else
967 priority := program_priority;
968 end if;
969
970 -- Set save output flag. First look for the token. Then in FCP,
971 -- profiles, and lastly set it to Y
972
973 if (P_SAVE_OUTPUT in ('Y', 'N')) then
974 saveout := P_SAVE_OUTPUT;
975 else
976 if (not saveout in ('Y', 'N')) then
977 FND_PROFILE.GET ('CONC_SAVE_OUTPUT', profile_buffer);
978 if (not profile_buffer in ('Y', 'N')) then
979 saveout := 'Y';
980 else
981 saveout := profile_buffer;
982 end if;
983 end if;
984 end if;
985
986
987 -- If submitting an MLS parent request, skip all the post-processing code from here
988 if (P_REQUEST_TYPE is null or P_REQUEST_TYPE <> 'B') then
989
990 -- Printer logic
991
992 -- Get default for # of copies
993 FND_PROFILE.GET ('CONC_COPIES', profile_buffer);
994 if (profile_buffer is not null) then
995 default_copies := To_Number (profile_buffer);
996 if (default_copies < 0) then
997 default_copies := 0;
998 end if;
999 else
1000 default_copies := 0;
1001 end if;
1002
1003 -- Is printer specified in FCP?
1004 if (fcp_printer is null) then
1005 FND_PROFILE.GET ('PRINTER', default_printer);
1006 else
1007 default_printer := fcp_printer;
1008 end if;
1009
1010 -- If style is passed as an argument, use it only if style is
1011 -- not required in FCP (fcp.required_style = 'N').
1012 if ((reqrd_flag <> 'Y') and (P_PRINT_STYLE is not null)) then
1013 print_style := P_PRINT_STYLE;
1014 end if;
1015
1016 -- Get ready for loop. If no printers were specified, then
1017 -- we need to set up a default printer if the default copies > 0.
1018 if ((P_PRINTER_COUNT = 0) and (default_copies >= 0)) then
1019 P_PRINTERS(1).printer := default_printer;
1020 P_PRINTERS(1).copies := default_copies;
1021 P_PRINTER_COUNT := 1;
1022 end if;
1023
1024 -- Printer Loop
1025 for i in 1..P_PRINTER_COUNT loop
1026
1027 curr_printer := P_PRINTERS(i).printer;
1028 curr_copies := P_PRINTERS(i).copies;
1029
1030 if (curr_copies is null) then
1031 curr_copies := default_copies;
1032 end if;
1033
1034 if (curr_copies >= 0) then
1035 tot_copies := tot_copies + curr_copies;
1036
1037 if (curr_printer is null) then
1038 curr_printer := default_printer;
1039 end if;
1040
1041 -- Printer validation
1042 -- Validate the printer only if we are going to print, which is,
1043 -- if the number of copies is > 0, print = Yes, and it is not
1044 -- a queue control request (e.g. Deactivate Concurrent Manager).
1045 if ((prtflg = 'Y') and (qctlflg <> 'Y')) then
1046 -- Error if printer is not specified.
1047 if (curr_printer is null) then
1048 raise printer_error;
1049 end if;
1050
1051 -- If printer is specified in FCP and also passed as an
1052 -- argument, but they don't match, check
1053 -- P_VALIDATE_PRINTER
1054 if ((fcp_printer is not null) and (curr_printer <> fcp_printer)) then
1055 if (P_VALIDATE_PRINTER = 'FAIL') then
1056 raise printer_error;
1057 elsif (P_VALIDATE_PRINTER = 'SKIP') then
1058 if (FND_LOG.LEVEL_EVENT >=
1059 FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1060 fnd_message.set_name('FND','CONC-Illegal printer spec');
1061 fnd_log.message(FND_LOG.LEVEL_EVENT,
1062 'fnd.plsql.FND_REQUEST.SUBMIT.printer', FALSE);
1063 end if;
1064 GOTO next_iteration;
1065 else -- P_VALIDATE_PRINTER = 'RESOLVE'
1066 if (FND_LOG.LEVEL_EVENT >=
1067 FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1068 fnd_message.set_name ('FND',
1069 'CONC-Illegal printer spec');
1070 fnd_log.message(FND_LOG.LEVEL_EVENT,
1071 'fnd.plsql.FND_REQUEST.SUBMIT.printer', FALSE);
1072 fnd_log.string(FND_LOG.LEVEL_EVENT,
1073 'fnd.plsql.FND_REQUEST.SUBMIT.printer',
1074 'Invalid printer('||curr_printer||
1075 ') specified for program(' ||program||
1076 '). Attempting to resolve by using printer: '
1077 ||fcp_printer );
1078 end if;
1079 curr_printer := fcp_printer;
1080 end if;
1081 end if; -- curr_printer <> fcp_printer
1082
1083 end if; -- prtflg = 'Y' and qctlflg <> 'Y'
1084
1085 -- Print style logic
1086
1087 -- Print style validation
1088
1089 if ((prtflg = 'Y') and (print_style is null))
1090 then
1091 raise style_error;
1092 end if;
1093
1094 -- We do not need to validate print style if it's a queue
1095 -- control request or if the the style is DYNAMIC
1096 if ((qctlflg <> 'Y') and (print_style <> 'DYNAMIC')) then
1097 styl_ok := fnd_print.get_style (print_style,
1098 minwid, maxwid, minlen, maxlen,
1099 (reqrd_flag = 'Y'), curr_printer,
1100 valid_style);
1101
1102 -- If printer and print_style were incompatible, valid_style
1103 -- is null. Also check to see if fnd_printer.get_style failed.
1104 if ((valid_style is null) or (not styl_ok)) then
1105
1106 -- If we still intend to print, bad news.
1107 if (prtflg = 'Y') then
1108
1109 -- Check for message on stack
1110 encoded_msg := FND_MESSAGE.GET_ENCODED;
1111 if (encoded_msg is not null) then
1112 FND_MESSAGE.SET_ENCODED(encoded_msg);
1113 raise already_msg;
1114 end if;
1115
1116 raise printer_styl_error;
1117
1118 end if; -- ((curr_copies > 0) and (prtflg = 'Y'))
1119 end if; -- ((valid_style is null) or (not styl_ok))
1120 end if; -- ((qctlflg <> 'Y') and (print_style <> 'DYNAMIC'))
1121
1122 -- insert the action
1123 -- (don't print on failure, print on warning only if
1124 -- CONC_PRINT_WARNING is set to 'Y')
1125
1126 profile_buffer := NULL;
1127 FND_PROFILE.GET ('CONC_PRINT_WARNING', profile_buffer);
1128 if (profile_buffer is not null and
1129 profile_buffer in ('y', 'Y')) then
1130 print_warning := 'Y';
1131 end if;
1132
1133 -- if number of copies is zero, no need to perform print action so don't insert
1134 -- into fnd_conc_pp_actions
1135
1136 if curr_copies > 0 then
1137 insert into fnd_conc_pp_actions
1138 (concurrent_request_id, action_type, status_s_flag,
1139 status_w_flag, status_f_flag, last_update_date,
1140 last_updated_by, creation_date, last_update_login,
1141 created_by, arguments, completed, number_of_copies,
1142 sequence,ops_instance)
1143 values
1144 (cur_request_id, 1, 'Y', print_warning, 'N', sysdate,
1145 user_id, sysdate, fgloginid, user_id, curr_printer,
1146 'N', curr_copies, i, submit_ops_id);
1147 end if;
1148
1149 end if; -- Curr_copies > 0
1150 <<next_iteration>> --Go to next loop iteration
1151 null;
1152
1153 end loop;
1154
1155 -- The first printer in the list will be written into
1156 -- fcr. Reports might use it. Product teams might also
1157 -- depend on the printer profile in their requests.
1158 -- bug 2872883 use default settings when the field is null
1159 if (P_PRINTER_COUNT > 0) then
1160 if ( P_PRINTERS(1).printer is null) then
1161 fcr_printer := default_printer;
1162 else
1163 fcr_printer := P_PRINTERS(1).printer;
1164 end if;
1165 else
1166 fcr_printer := fcp_printer;
1167 end if;
1168
1169 -- Even if we aren't going to print, we'll populate
1170 -- the style. Styles are required by Oracle Reports.
1171 if (valid_style is null) then
1172 -- if it's an Oracle Reports request, we must get
1173 -- some (valid) print style
1174 if (execcode = 'P') then
1175 styl_ok := fnd_print.get_style (print_style,
1176 minwid, maxwid,
1177 minlen, maxlen,
1178 (reqrd_flag = 'Y'),
1179 null,
1180 valid_style);
1181
1182 if ((valid_style is null) or (not styl_ok)) then
1183 -- Check for message on stack
1184 encoded_msg := FND_MESSAGE.GET_ENCODED;
1185 if (encoded_msg is not null) then
1186 FND_MESSAGE.SET_ENCODED(encoded_msg);
1187 raise already_msg;
1188 end if;
1189
1190 raise srw_style_error;
1191 end if;
1192 else -- No need to validate style
1193 valid_style := print_style;
1194 end if; -- (execcode = 'P')
1195 end if; -- (valid_style is null)
1196
1197
1198 -- Insert Notifications
1199
1200 for i in 1..P_NOTIFICATION_COUNT loop
1201 insert into fnd_conc_pp_actions
1202 (concurrent_request_id, action_type, status_s_flag,
1203 status_w_flag, status_f_flag, last_update_date,
1204 last_updated_by, creation_date, last_update_login,
1205 created_by, arguments, completed, number_of_copies,
1206 sequence, orig_system, orig_system_id, ops_instance)
1207 values
1208 (cur_request_id, 2,
1209 P_NOTIFICATIONS(i).on_normal,
1210 P_NOTIFICATIONS(i).on_warning,
1211 P_NOTIFICATIONS(i).on_error, sysdate,
1212 user_id, sysdate, fgloginid, user_id,
1213 P_NOTIFICATIONS(i).name, 'N', null, i,
1214 P_NOTIFICATIONS(i).orig_system,
1215 P_NOTIFICATIONS(i).orig_system_id,
1216 submit_ops_id);
1217 end loop;
1218
1219 -- Insert layout options
1220 if (P_LAYOUT_COUNT > 0) then
1221
1222 insert into fnd_conc_pp_actions
1223 (concurrent_request_id, action_type, status_s_flag,
1224 status_w_flag, status_f_flag, last_update_date,
1225 last_updated_by, creation_date, last_update_login,
1226 created_by, completed, sequence, argument1, argument2,
1227 argument3, argument4, argument5, ops_instance)
1228 values
1229 (cur_request_id, 6, 'Y','Y','N',
1230 sysdate, user_id, sysdate, fgloginid, user_id, 'N', 1,
1231 P_LAYOUTS(1).template_appl_name, P_LAYOUTS(1).template_code, P_LAYOUTS(1).template_language,
1232 P_LAYOUTS(1).template_territory, P_LAYOUTS(1).output_format, submit_ops_id);
1233
1234 end if;
1235
1236
1237 -- Insert delivery options
1238 if (P_DELIV_OPTS_COUNT > 0) then
1239
1240 for i in 1..P_DELIV_OPTS_COUNT loop
1241 insert into fnd_conc_pp_actions
1242 (concurrent_request_id, action_type,
1243 status_s_flag, status_w_flag, status_f_flag,
1244 last_update_date, last_updated_by, creation_date,
1245 last_update_login, created_by,
1246 completed, sequence,
1247 argument1, argument2, argument3, argument4, argument5,
1248 argument6, argument7, argument8, argument9, argument10,
1249 ops_instance)
1250 values
1251 (cur_request_id,
1252 decode( P_DELIVERY_OPTIONS(i).argument1, 'B', 8, 7 ),
1253 'Y', 'Y', 'N',
1254 sysdate, user_id, sysdate,
1255 fgloginid, user_id,
1256 'N', i,
1257 P_DELIVERY_OPTIONS(i).argument1, P_DELIVERY_OPTIONS(i).argument2,
1258 P_DELIVERY_OPTIONS(i).argument3, P_DELIVERY_OPTIONS(i).argument4,
1259 P_DELIVERY_OPTIONS(i).argument5, P_DELIVERY_OPTIONS(i).argument6,
1260 P_DELIVERY_OPTIONS(i).argument7, P_DELIVERY_OPTIONS(i).argument8,
1261 P_DELIVERY_OPTIONS(i).argument9, P_DELIVERY_OPTIONS(i).argument10,
1262 submit_ops_id);
1263 end loop;
1264
1265
1266 end if;
1267
1268
1269 end if; -- if (P_REQUEST_TYPE <> 'B')
1270 -- End skipping MLS requests
1271
1272
1273 -- Get Oracle ID to run program from
1274 begin
1275 -- First, see if datagroup is set with options
1276 if (P_DATAGROUP is not null) then
1277 Select Oracle_Username
1278 Into oraclacct
1279 From Fnd_Data_Group_Units U,
1280 Fnd_Data_Groups G,
1281 Fnd_Oracle_Userid O
1282 Where Data_Group_Name = P_DATAGROUP
1283 And U.Data_Group_Id = G.Data_Group_Id
1284 And U.Oracle_Id = O.Oracle_Id
1285 And Application_Id = prog_appl_id;
1286
1287 else -- default dgrp
1288 Select O.Oracle_Username
1289 Into oraclacct
1290 From Fnd_Data_Group_Units U,
1291 Fnd_Responsibility R,
1292 Fnd_Oracle_Userid O
1293 Where R.Application_ID = resp_appl_id
1294 And R.Responsibility_ID = resp_id
1295 And R.Data_Group_ID = U.Data_Group_ID
1296 And O.Oracle_ID = U.Oracle_ID
1297 And U.Application_ID = prog_appl_id;
1298 end if;
1299
1300 exception
1301 when no_data_found then
1302 raise oracle_name_error;
1303 when others then
1304 raise;
1305 end;
1306
1307 -- Fetch Login ID for the request from the sequnce generator.
1308 -- Insert the record in Fnd_Logins if the request submission
1309 -- successful.
1310 begin
1311 Select Fnd_Logins_S.nextval
1312 Into loginid
1313 From Sys.Dual;
1314
1315 exception
1316 when no_data_found then
1317 raise dual_no_rows;
1318 when too_many_rows then
1319 raise dual_too_many_rows;
1320 when others then
1321 raise;
1322 end;
1323
1324 -- Count the number of arguments and bulid argstr
1325 if (argument1 = CHR(0)) then goto end_args; end if;
1326 nargs := 1;
1327 argtxt := argtxt||argument1;
1328 if (argument2 = CHR(0)) then goto end_args; end if;
1329 nargs := 2;
1330 argtxt := argtxt|| ', '||argument2;
1331 if (argument3 = CHR(0)) then goto end_args; end if;
1332 nargs := 3;
1333 argtxt := argtxt|| ', '||argument3;
1334 if (argument4 = CHR(0)) then goto end_args; end if;
1335 nargs := 4;
1336 argtxt := argtxt||', '||argument4;
1337 if (argument5 = CHR(0)) then goto end_args; end if;
1338 nargs := 5;
1339 argtxt := argtxt||', '||argument5;
1340 if (argument6 = CHR(0)) then goto end_args; end if;
1341 nargs := 6;
1342 argtxt := argtxt||', '||argument6;
1343 if (argument7 = CHR(0)) then goto end_args; end if;
1344 nargs := 7;
1345 argtxt := argtxt||', '||argument7;
1346 if (argument8 = CHR(0)) then goto end_args; end if;
1347 nargs := 8;
1348 argtxt := argtxt||', '||argument8;
1349 if (argument9 = CHR(0)) then goto end_args; end if;
1350 nargs := 9;
1351 argtxt := argtxt||', '||argument9;
1352 if (argument10 = CHR(0)) then goto end_args; end if;
1353 nargs := 10;
1354 argtxt := argtxt||', '||argument10;
1355 if (argument11 = CHR(0)) then goto end_args; end if;
1356 nargs := 11;
1357 argtxt := argtxt||', '||argument11;
1358 if (argument12 = CHR(0)) then goto end_args; end if;
1359 nargs := 12;
1360 argtxt := argtxt||', '||argument12;
1361 if (argument13 = CHR(0)) then goto end_args; end if;
1362 nargs := 13;
1363 argtxt := argtxt||', '||argument13;
1364 if (argument14 = CHR(0)) then goto end_args; end if;
1365 nargs := 14;
1366 argtxt := argtxt||', '||argument14;
1367 if (argument15 = CHR(0)) then goto end_args; end if;
1368 nargs := 15;
1369 argtxt := argtxt||', '||argument15;
1370 if (argument16 = CHR(0)) then goto end_args; end if;
1371 nargs := 16;
1372 argtxt := argtxt||', '||argument16;
1373 if (argument17 = CHR(0)) then goto end_args; end if;
1374 nargs := 17;
1375 argtxt := argtxt||', '||argument17;
1376 if (argument18 = CHR(0)) then goto end_args; end if;
1377 nargs := 18;
1378 argtxt := argtxt||', '||argument18;
1379 if (argument19 = CHR(0)) then goto end_args; end if;
1380 nargs := 19;
1381 argtxt := argtxt||', '||argument19;
1382 if (argument20 = CHR(0)) then goto end_args; end if;
1383 nargs := 20;
1384 argtxt := argtxt||', '||argument20;
1385 if (argument21 = CHR(0)) then goto end_args; end if;
1386 nargs := 21;
1387 argtxt := argtxt||', '||argument21;
1388 if (argument22 = CHR(0)) then goto end_args; end if;
1389 nargs := 22;
1390 argtxt := argtxt||', '||argument22;
1391 if (argument23 = CHR(0)) then goto end_args; end if;
1392 nargs := 23;
1393 argtxt := argtxt||', '||argument23;
1394 if (argument24 = CHR(0)) then goto end_args; end if;
1395 nargs := 24;
1396 argtxt := argtxt||', '||argument24;
1397 if (argument25 = CHR(0)) then goto end_args; end if;
1398 nargs := 25;
1399 argtxt := argtxt||', '||argument25;
1400 if (argument26 = CHR(0)) then goto end_args; end if;
1401 nargs := 26;
1402 argtxt := argtxt||', '||argument26;
1403 if (argument27 = CHR(0)) then goto end_args; end if;
1404 nargs := 27;
1405 argtxt := argtxt||', '||argument27;
1406 if (argument28 = CHR(0)) then goto end_args; end if;
1407 nargs := 28;
1408 argtxt := argtxt||', '||argument28;
1409 if (argument29 = CHR(0)) then goto end_args; end if;
1410 nargs := 29;
1411 argtxt := argtxt||', '||argument29;
1412 if (argument30 = CHR(0)) then goto end_args; end if;
1413 nargs := 30;
1414 argtxt := argtxt||', '||argument30;
1415 if (argument31 = CHR(0)) then goto end_args; end if;
1416 nargs := 31;
1417 argtxt := argtxt||', '||argument31;
1418 if (argument32 = CHR(0)) then goto end_args; end if;
1419 nargs := 32;
1420 argtxt := argtxt||', '||argument32;
1421 if (argument33 = CHR(0)) then goto end_args; end if;
1422 nargs := 33;
1423 argtxt := argtxt||', '||argument33;
1424 if (argument34 = CHR(0)) then goto end_args; end if;
1425 nargs := 34;
1426 argtxt := argtxt||', '||argument34;
1427 if (argument35 = CHR(0)) then goto end_args; end if;
1428 nargs := 35;
1429 argtxt := argtxt||', '||argument35;
1430 if (argument36 = CHR(0)) then goto end_args; end if;
1431 nargs := 36;
1432 argtxt := argtxt||', '||argument36;
1433 if (argument37 = CHR(0)) then goto end_args; end if;
1434 nargs := 37;
1435 argtxt := argtxt||', '||argument37;
1436 if (argument38 = CHR(0)) then goto end_args; end if;
1437 nargs := 38;
1438 argtxt := argtxt||', '||argument38;
1439 if (argument39 = CHR(0)) then goto end_args; end if;
1440 nargs := 39;
1441 argtxt := argtxt||', '||argument39;
1442 if (argument40 = CHR(0)) then goto end_args; end if;
1443 nargs := 40;
1444 argtxt := argtxt||', '||argument40;
1445 if (argument41 = CHR(0)) then goto end_args; end if;
1446 nargs := 41;
1447 argtxt := argtxt||', '||argument41;
1448 if (argument42 = CHR(0)) then goto end_args; end if;
1449 nargs := 42;
1450 argtxt := argtxt||', '||argument42;
1451 if (argument43 = CHR(0)) then goto end_args; end if;
1452 nargs := 43;
1453 argtxt := argtxt||', '||argument43;
1454 if (argument44 = CHR(0)) then goto end_args; end if;
1455 nargs := 44;
1456 argtxt := argtxt||', '||argument44;
1457 if (argument45 = CHR(0)) then goto end_args; end if;
1458 nargs := 45;
1459 argtxt := argtxt||', '||argument45;
1460 if (argument46 = CHR(0)) then goto end_args; end if;
1461 nargs := 46;
1462 argtxt := argtxt||', '||argument46;
1463 if (argument47 = CHR(0)) then goto end_args; end if;
1464 nargs := 47;
1465 argtxt := argtxt||', '||argument47;
1466 if (argument48 = CHR(0)) then goto end_args; end if;
1467 nargs := 48;
1468 argtxt := argtxt||', '||argument48;
1469 if (argument49 = CHR(0)) then goto end_args; end if;
1470 nargs := 49;
1471 argtxt := argtxt||', '||argument49;
1472 if (argument50 = CHR(0)) then goto end_args; end if;
1473 nargs := 50;
1474 argtxt := argtxt||', '||argument50;
1475 if (argument51 = CHR(0)) then goto end_args; end if;
1476 nargs := 51;
1477 argtxt := argtxt||', '||argument51;
1478 if (argument52 = CHR(0)) then goto end_args; end if;
1479 nargs := 52;
1480 argtxt := argtxt||', '||argument52;
1481 if (argument53 = CHR(0)) then goto end_args; end if;
1482 nargs := 53;
1483 argtxt := argtxt||', '||argument53;
1484 if (argument54 = CHR(0)) then goto end_args; end if;
1485 nargs := 54;
1486 argtxt := argtxt||', '||argument54;
1487 if (argument55 = CHR(0)) then goto end_args; end if;
1488 nargs := 55;
1489 argtxt := argtxt||', '||argument55;
1490 if (argument56 = CHR(0)) then goto end_args; end if;
1491 nargs := 56;
1492 argtxt := argtxt||', '||argument56;
1493 if (argument57 = CHR(0)) then goto end_args; end if;
1494 nargs := 57;
1495 argtxt := argtxt||', '||argument57;
1496 if (argument58 = CHR(0)) then goto end_args; end if;
1497 nargs := 58;
1498 argtxt := argtxt||', '||argument58;
1499 if (argument59 = CHR(0)) then goto end_args; end if;
1500 nargs := 59;
1501 argtxt := argtxt||', '||argument59;
1502 if (argument60 = CHR(0)) then goto end_args; end if;
1503 nargs := 60;
1504 argtxt := argtxt||', '||argument60;
1505 if (argument61 = CHR(0)) then goto end_args; end if;
1506 nargs := 61;
1507 argtxt := argtxt||', '||argument61;
1508 if (argument62 = CHR(0)) then goto end_args; end if;
1509 nargs := 62;
1510 argtxt := argtxt||', '||argument62;
1511 if (argument63 = CHR(0)) then goto end_args; end if;
1512 nargs := 63;
1513 argtxt := argtxt||', '||argument63;
1514 if (argument64 = CHR(0)) then goto end_args; end if;
1515 nargs := 64;
1516 argtxt := argtxt||', '||argument64;
1517 if (argument65 = CHR(0)) then goto end_args; end if;
1518 nargs := 65;
1519 argtxt := argtxt||', '||argument65;
1520 if (argument66 = CHR(0)) then goto end_args; end if;
1521 nargs := 66;
1522 argtxt := argtxt||', '||argument66;
1523 if (argument67 = CHR(0)) then goto end_args; end if;
1524 nargs := 67;
1525 argtxt := argtxt||', '||argument67;
1526 if (argument68 = CHR(0)) then goto end_args; end if;
1527 nargs := 68;
1528 argtxt := argtxt||', '||argument68;
1529 if (argument69 = CHR(0)) then goto end_args; end if;
1530 nargs := 69;
1531 argtxt := argtxt||', '||argument69;
1532 if (argument70 = CHR(0)) then goto end_args; end if;
1533 nargs := 70;
1534 argtxt := argtxt||', '||argument70;
1535 if (argument71 = CHR(0)) then goto end_args; end if;
1536 nargs := 71;
1537 argtxt := argtxt||', '||argument71;
1538 if (argument72 = CHR(0)) then goto end_args; end if;
1539 nargs := 72;
1540 argtxt := argtxt||', '||argument72;
1541 if (argument73 = CHR(0)) then goto end_args; end if;
1542 nargs := 73;
1543 argtxt := argtxt||', '||argument73;
1544 if (argument74 = CHR(0)) then goto end_args; end if;
1545 nargs := 74;
1546 argtxt := argtxt||', '||argument74;
1547 if (argument75 = CHR(0)) then goto end_args; end if;
1548 nargs := 75;
1549 argtxt := argtxt||', '||argument75;
1550 if (argument76 = CHR(0)) then goto end_args; end if;
1551 nargs := 76;
1552 argtxt := argtxt||', '||argument76;
1553 if (argument77 = CHR(0)) then goto end_args; end if;
1554 nargs := 77;
1555 argtxt := argtxt||', '||argument77;
1556 if (argument78 = CHR(0)) then goto end_args; end if;
1557 nargs := 78;
1558 argtxt := argtxt||', '||argument78;
1559 if (argument79 = CHR(0)) then goto end_args; end if;
1560 nargs := 79;
1561 argtxt := argtxt||', '||argument79;
1562 if (argument80 = CHR(0)) then goto end_args; end if;
1563 nargs := 80;
1564 argtxt := argtxt||', '||argument80;
1565 if (argument81 = CHR(0)) then goto end_args; end if;
1566 nargs := 81;
1567 argtxt := argtxt||', '||argument81;
1568 if (argument82 = CHR(0)) then goto end_args; end if;
1569 nargs := 82;
1570 argtxt := argtxt||', '||argument82;
1571 if (argument83 = CHR(0)) then goto end_args; end if;
1572 nargs := 83;
1573 argtxt := argtxt||', '||argument83;
1574 if (argument84 = CHR(0)) then goto end_args; end if;
1575 nargs := 84;
1576 argtxt := argtxt||', '||argument84;
1577 if (argument85 = CHR(0)) then goto end_args; end if;
1578 nargs := 85;
1579 argtxt := argtxt||', '||argument85;
1580 if (argument86 = CHR(0)) then goto end_args; end if;
1581 nargs := 86;
1582 argtxt := argtxt||', '||argument86;
1583 if (argument87 = CHR(0)) then goto end_args; end if;
1584 nargs := 87;
1585 argtxt := argtxt||', '||argument87;
1586 if (argument88 = CHR(0)) then goto end_args; end if;
1587 nargs := 88;
1588 argtxt := argtxt||', '||argument88;
1589 if (argument89 = CHR(0)) then goto end_args; end if;
1590 nargs := 89;
1591 argtxt := argtxt||', '||argument89;
1592 if (argument90 = CHR(0)) then goto end_args; end if;
1593 nargs := 90;
1594 argtxt := argtxt||', '||argument90;
1595 if (argument91 = CHR(0)) then goto end_args; end if;
1596 nargs := 91;
1597 argtxt := argtxt||', '||argument91;
1598 if (argument92 = CHR(0)) then goto end_args; end if;
1599 nargs := 92;
1600 argtxt := argtxt||', '||argument92;
1601 if (argument93 = CHR(0)) then goto end_args; end if;
1602 nargs := 93;
1603 argtxt := argtxt||', '||argument93;
1604 if (argument94 = CHR(0)) then goto end_args; end if;
1605 nargs := 94;
1606 argtxt := argtxt||', '||argument94;
1607 if (argument95 = CHR(0)) then goto end_args; end if;
1608 nargs := 95;
1609 argtxt := argtxt||', '||argument95;
1610 if (argument96 = CHR(0)) then goto end_args; end if;
1611 nargs := 96;
1612 argtxt := argtxt||', '||argument96;
1613 if (argument97 = CHR(0)) then goto end_args; end if;
1614 nargs := 97;
1615 argtxt := argtxt||', '||argument97;
1616 if (argument98 = CHR(0)) then goto end_args; end if;
1617 nargs := 98;
1618 argtxt := argtxt||', '||argument98;
1619 if (argument99 = CHR(0)) then goto end_args; end if;
1620 nargs := 99;
1621 argtxt := argtxt||', '||argument99;
1622 if (argument100 = CHR(0)) then goto end_args; end if;
1623 nargs := 100;
1624 argtxt := argtxt||', '||argument100;
1625
1626 <<end_args>>
1627
1628 argtxt2 := substrb(argtxt, 1, 240);
1629
1630 -- Get conflicts domain - cd_id
1631 -- First, try program parameter
1632 if (cd_param is not null) then
1633 cd_id := fndcp_crm.get_cd_id (
1634 application, program,
1635 user_id, user_id,
1636 cd_param, nargs,
1637 argument1, argument2, argument3, argument4,
1638 argument5, argument6, argument7, argument8,
1639 argument9, argument10, argument11, argument12,
1640 argument13, argument14, argument15, argument16,
1641 argument17, argument18, argument19, argument20,
1642 argument21, argument22, argument23, argument24,
1643 argument25, argument26, argument27, argument28,
1644 argument29, argument30, argument31, argument32,
1645 argument33, argument34, argument35, argument36,
1646 argument37, argument38, argument39, argument40,
1647 argument41, argument42, argument43, argument44,
1648 argument45, argument46, argument47, argument48,
1649 argument49, argument50, argument51, argument52,
1650 argument53, argument54, argument55, argument56,
1651 argument57, argument58, argument59, argument60,
1652 argument61, argument62, argument63, argument64,
1653 argument65, argument66, argument67, argument68,
1654 argument69, argument70, argument71, argument72,
1655 argument73, argument74, argument75, argument76,
1656 argument77, argument78, argument79, argument80,
1657 argument81, argument82, argument83, argument84,
1658 argument85, argument86, argument87, argument88,
1659 argument89, argument90, argument91, argument92,
1660 argument93, argument94, argument95, argument96,
1661 argument97, argument98, argument99, argument100);
1662 end if;
1663
1664 -- Next try profile
1665 if (cd_id = -1) then
1666 profile_buffer := NULL;
1667 FND_PROFILE.GET ('CONC_CD_ID', profile_buffer);
1668 if (profile_buffer is not null) then
1669 cd_id := To_Number (profile_buffer);
1670 else
1671 cd_id := 0;
1672 end if;
1673 end if;
1674
1675 -- Create request
1676 -- Bug8887553: Added condition of P_REPEAT_TIME for request set resubmission
1677 if (P_REL_CLASS_ID is null) and (P_REPEAT_INTERVAL is not null
1678 or P_REPEAT_TIME is not null) then
1679 new_class := TRUE;
1680 begin
1681 Select FND_CONC_RELEASE_CLASSES_S.nextval, 0
1682 into P_REL_CLASS_ID, P_REL_CLASS_APP_ID from Sys.dual;
1683 exception
1684 when no_data_found then
1685 raise dual_no_rows;
1686 when too_many_rows then
1687 raise dual_too_many_rows;
1688 when others then
1689 raise;
1690 end;
1691 end if;
1692
1693 Insert
1694 Into Fnd_Concurrent_Requests (
1695 Request_Id, security_group_id,
1696 Phase_Code, Status_Code,
1697 Priority, Parent_Request_ID, Priority_Request_ID,
1698 Description, Req_Information,
1699 Is_Sub_Request, Has_Sub_Request, Update_Protected,
1700 Hold_Flag, Enforce_Seriality_Flag, Single_Thread_Flag,
1701 Argument_Input_Method_Code, Implicit_Code,
1702
1703 Request_Date, Requested_Start_Date, Requested_By,
1704 Last_Update_Date, Last_Updated_By, Last_Update_Login,
1705
1706 Oracle_Id, Conc_Login_Id,
1707 Responsibility_Id, Responsibility_Application_Id,
1708 Nls_Language, Nls_Territory, Nls_Numeric_Characters,
1709
1710 Program_Application_Id, Concurrent_Program_Id,
1711 Queue_Method_Code,
1712 Request_Class_Application_Id, Concurrent_Request_Class_Id,
1713
1714 Print_Group, Printer, Print_Style,
1715 Number_Of_Copies, Save_Output_Flag, CRM_THRSHLD,
1716
1717 Resubmit_Time, Resubmit_Interval,
1718 Resubmit_Interval_Unit_Code, Resubmit_Interval_Type_Code,
1719 Resubmit_End_Date, Resubmitted, Critical, Request_Type,
1720
1721 RELEASE_CLASS_APP_ID, RELEASE_CLASS_ID,
1722 STALE_DATE, CANCEL_OR_HOLD,
1723
1724 Output_File_Type, nls_compliant,
1725 CD_ID, Request_Limit, Increment_Dates,
1726 Enable_Trace, OPS_Instance, ORG_ID,
1727 Node_Name1, Connstr1, EDITION_NAME,
1728
1729 Argument_Text, Number_Of_Arguments,
1730 Argument1, Argument2, Argument3, Argument4, Argument5,
1731 Argument6, Argument7, Argument8, Argument9, Argument10,
1732 Argument11, Argument12, Argument13, Argument14, Argument15,
1733 Argument16, Argument17, Argument18, Argument19, Argument20,
1734 Argument21, Argument22, Argument23, Argument24, Argument25)
1735
1736 Select cur_request_id, z_security_group_id,
1737 Phase, Decode (submit.Status,
1738 'A', 'A',
1739 'Z', 'Z',
1740 Decode(FCP.Queue_Method_Code,
1741 'B', 'Q',
1742 Decode (st_flag,
1743 'Y', 'Q',
1744 Decode(Req_Limit, 'Y', 'Q',
1745 'I')))),
1746 Decode (FCP.Queue_Control_Flag,
1747 'Y', NVL(FCP.Request_Priority, 0),
1748 Decode (submit.priority,
1749 null, 50,
1750 submit.priority)),
1751 Decode (par_request_id,
1752 0, NULL,
1753 par_request_id),
1754 cur_req_pri_id,
1755 submit.description, NULL,
1756 issubreq, 'N', P_PROTECTED,
1757 submit.hold_flag, 'Y', st_flag,
1758 'S', P_IMPLICIT,
1759
1760 SYSDATE,
1761 Decode (requested_start,
1762 '', (SYSDATE - tz_offset),
1763 Greatest (requested_start,
1764 Decode (par_request_id,
1765 0, (SYSDATE - tz_offset),
1766 fnd_conc_date.string_to_date (
1767 '01-01-0001')))),
1768 submit.user_id,
1769 SYSDATE, submit.user_id, loginid,
1770
1771 Oracle_ID, loginid,
1772 resp_id, resp_appl_id,
1773 P_LANGUAGE, P_TERRITORY, P_NUMERIC_CHARACTERS,
1774
1775 FA.Application_ID, FCP.Concurrent_Program_ID,
1776 Decode(Req_Limit,
1777 'Y', 'B',
1778 Decode(st_flag,
1779 'N', FCP.Queue_Method_Code,
1780 'B')),
1781 FCP.Class_Application_ID, FCP.Concurrent_Class_ID,
1782
1783 P_PRINT_TOGETHER, fcr_printer, valid_style,
1784 tot_copies, saveout, request_threshold,
1785 Decode (P_REPEAT_TIME,
1786 '', NULL,
1787 To_Char (fnd_conc_date.string_to_date (
1788 '01-01-0001' || P_REPEAT_TIME),
1789 'HH24:MI:SS')),
1790 Decode (P_REPEAT_INTERVAL,
1791 '', NULL,
1792 P_REPEAT_INTERVAL),
1793 P_REPEAT_INTERVAL_UNIT, P_REPEAT_INTERVAL_TYPE,
1794 P_REPEAT_END,
1795 'N', P_CRITICAL_REQUEST, P_REQUEST_TYPE,
1796
1797 P_REL_CLASS_APP_ID, P_REL_CLASS_ID,
1798 P_REL_STALE_DATE, P_REL_CANCEL_OR_HOLD,
1799
1800 FCP.Output_File_Type, FCP.nls_compliant,
1801 CD_ID, Req_limit, P_INCREMENT_DATES,
1802 decode(trace_enabled, 'Y', 'Y', FCP.Enable_Trace),
1803 submit_ops_id, P_ORG_ID,
1804 P_NODE_NAME1, P_CONNSTR1, sys_context('userenv', 'current_edition_name'),
1805 argtxt2, nargs,
1806 submit.argument1, submit.argument2,
1807 submit.argument3, submit.argument4,
1808 submit.argument5, submit.argument6,
1809 submit.argument7, submit.argument8,
1810 submit.argument9, submit.argument10,
1811 submit.argument11, submit.argument12,
1812 submit.argument13, submit.argument14,
1813 submit.argument15, submit.argument16,
1814 submit.argument17, submit.argument18,
1815 submit.argument19, submit.argument20,
1816 submit.argument21, submit.argument22,
1817 submit.argument23, submit.argument24,
1818 submit.argument25
1819
1820 From Fnd_Application FA,
1821 Fnd_Concurrent_Programs FCP,
1822 Fnd_Oracle_Userid
1823 Where
1824 Oracle_Username = oraclacct
1825 And FCP.Enabled_Flag = 'Y'
1826 And (FCP.Application_ID = FA.Application_ID And
1827 Concurrent_Program_Name = Upper (program))
1828 And FA.Application_Short_Name = Upper (application);
1829
1830 if (sql%rowcount = 0) then
1831 raise insert_error;
1832 end if;
1833
1834 if (nargs > 25) then
1835 Insert
1836 Into Fnd_Conc_Request_Arguments (
1837 Request_Id,
1838 Argument26, Argument27, Argument28, Argument29, Argument30,
1839 Argument31, Argument32, Argument33, Argument34, Argument35,
1840 Argument36, Argument37, Argument38, Argument39, Argument40,
1841 Argument41, Argument42, Argument43, Argument44, Argument45,
1842 Argument46, Argument47, Argument48, Argument49, Argument50,
1843 Argument51, Argument52, Argument53, Argument54, Argument55,
1844 Argument56, Argument57, Argument58, Argument59, Argument60,
1845 Argument61, Argument62, Argument63, Argument64, Argument65,
1846 Argument66, Argument67, Argument68, Argument69, Argument70,
1847 Argument71, Argument72, Argument73, Argument74, Argument75,
1848 Argument76, Argument77, Argument78, Argument79, Argument80,
1849 Argument81, Argument82, Argument83, Argument84, Argument85,
1850 Argument86, Argument87, Argument88, Argument89, Argument90,
1851 Argument91, Argument92, Argument93, Argument94, Argument95,
1852 Argument96, Argument97, Argument98, Argument99, Argument100)
1853
1854 Select cur_request_id,
1855 submit.argument26,
1856 submit.argument27, submit.argument28,
1857 submit.argument29, submit.argument30,
1858 submit.argument31, submit.argument32,
1859 submit.argument33, submit.argument34,
1860 submit.argument35, submit.argument36,
1861 submit.argument37, submit.argument38,
1862 submit.argument39, submit.argument40,
1863 submit.argument41, submit.argument42,
1864 submit.argument43, submit.argument44,
1865 submit.argument45, submit.argument46,
1866 submit.argument47, submit.argument48,
1867 submit.argument49, submit.argument50,
1868 submit.argument51, submit.argument52,
1869 submit.argument53, submit.argument54,
1870 submit.argument55, submit.argument56,
1871 submit.argument57, submit.argument58,
1872 submit.argument59, submit.argument60,
1873 submit.argument61, submit.argument62,
1874 submit.argument63, submit.argument64,
1875 submit.argument65, submit.argument66,
1876 submit.argument67, submit.argument68,
1877 submit.argument69, submit.argument70,
1878 submit.argument71, submit.argument72,
1879 submit.argument73, submit.argument74,
1880 submit.argument75, submit.argument76,
1881 submit.argument77, submit.argument78,
1882 submit.argument79, submit.argument80,
1883 submit.argument81, submit.argument82,
1884 submit.argument83, submit.argument84,
1885 submit.argument85, submit.argument86,
1886 submit.argument87, submit.argument88,
1887 submit.argument89, submit.argument90,
1888 submit.argument91, submit.argument92,
1889 submit.argument93, submit.argument94,
1890 submit.argument95, submit.argument96,
1891 submit.argument97, submit.argument98,
1892 submit.argument99, submit.argument100
1893
1894 From Sys.Dual;
1895 end if; -- (nargs > 25)
1896
1897 if (sql%rowcount = 0) then
1898 raise args_insert_error;
1899 end if;
1900
1901 -- assign any Post Processing PL/SQL actions
1902 Begin
1903 if ( portlet_ref = 'S' ) then -- on success
1904 p_on_success := 'Y';
1905 elsif ( portlet_ref = 'W' ) then -- on warning (success and warning)
1906 p_on_success := 'Y';
1907 p_on_warning := 'Y';
1908 elsif (portlet_ref = 'A' ) then -- always (success,warning,error)
1909 p_on_success := 'Y';
1910 p_on_warning := 'Y';
1911 p_on_error := 'Y';
1912 end if;
1913
1914 if ( portlet_ref <> 'N' ) then
1915 pp_plsql_step := FND_CONC_PP.Assign('FND',
1916 'FNDPRUPD',
1917 cur_request_id,
1918 p_on_success, p_on_warning, p_on_error,
1919 null, null, null, null, null,
1920 null, null, null, null, null );
1921 if ( pp_plsql_step < 0 ) then
1922 raise pp_plsql_exception;
1923 end if;
1924 end if;
1925
1926 -- as safe side catching this exception for any fnd_conc_pp.assign
1927 -- call exception
1928 exception when others then
1929 raise pp_plsql_exception;
1930 end;
1931
1932 -- Insert into fnd_conc_rel_classes
1933 if (P_REPEAT_TIME is not NULL) then
1934 P_REPEAT_INTERVAL := 1;
1935 P_REPEAT_INTERVAL_UNIT := 'DAYS';
1936 P_REPEAT_INTERVAL_TYPE := 'START';
1937 end if;
1938
1939
1940 if (new_class) then
1941 begin
1942 INSERT INTO Fnd_Conc_Release_Classes
1943 (APPLICATION_ID, RELEASE_CLASS_ID,
1944 RELEASE_CLASS_NAME, OWNER_REQ_ID,
1945 ENABLED_FLAG, LAST_UPDATE_DATE,
1946 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1947 CREATION_DATE, CREATED_BY,
1948 UPDATED_FLAG, CLASS_TYPE,
1949 CLASS_INFO, START_DATE_ACTIVE,
1950 END_DATE_ACTIVE, DATE1, DATE2)
1951 Select 0, P_REL_CLASS_ID,
1952 'Request - '|| cur_request_id, cur_request_id,
1953 'Y', Sysdate,
1954 submit.user_id, loginid,
1955 Sysdate, submit.user_id,
1956 'Y', decode(P_REPEAT_INTERVAL, NULL, 'O', 'P'),
1957 Decode(P_REPEAT_INTERVAL_UNIT, NULL, NULL,
1958 P_REPEAT_INTERVAL||':'||
1959 Decode(P_REPEAT_INTERVAL_UNIT,
1960 'MINUTES', 'N',
1961 'MONTHS', 'M',
1962 'HOURS', 'H',
1963 'DAYS', 'D') || ':' ||
1964 Decode(P_REPEAT_INTERVAL_TYPE,
1965 'START', 'S', 'C')),
1966 null, null,
1967 Requested_start_date, resubmit_end_date
1968 From Fnd_Concurrent_Requests
1969 Where Request_ID = cur_request_id;
1970
1971 if (sql%rowcount = 0) then
1972 raise relclass_insert_error;
1973 end if;
1974
1975 INSERT INTO Fnd_Conc_Release_Classes_TL
1976 (APPLICATION_ID, RELEASE_CLASS_ID,
1977 LANGUAGE, SOURCE_LANG, USER_RELEASE_CLASS_NAME,
1978 LAST_UPDATE_DATE,
1979 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1980 CREATION_DATE, CREATED_BY)
1981 Select 0, P_REL_CLASS_ID,
1982 L.LANGUAGE_CODE,userenv('LANG'),
1983 'Request - '|| cur_request_id,
1984 Sysdate,
1985 submit.user_id, loginid,
1986 Sysdate, submit.user_id
1987 from FND_LANGUAGES L
1988 where L.INSTALLED_FLAG in ('I', 'B');
1989
1990 if (sql%rowcount = 0) then
1991 raise relclass_insert_error;
1992 end if;
1993
1994 end;
1995 end if;
1996
1997 -- Insert into Fnd_Logins
1998 if (subloginid is not null) then
1999 begin
2000 Insert
2001 into Fnd_Logins (
2002 Login_Id, User_Id,
2003 Start_Time, End_Time,
2004 Pid, Spid, Terminal_ID,
2005 Login_Name, Submitted_Login_Id)
2006 Values (
2007 loginid, submit.user_id,
2008 Sysdate, Null,
2009 Null, Null, 'Concurrent',
2010 Null, subloginid);
2011
2012 if (sql%rowcount = 0) then
2013 raise login_insert_error;
2014 end if;
2015 end;
2016 end if; -- (subloginid is not null)
2017
2018 -- Submitted request successfully
2019
2020 -- Call FND_GLOBAL.RT_TEST_ID() to see if needs to write the request
2021 -- id into FND_RT_REQUESTS table.
2022 rttestid := FND_GLOBAL.RT_TEST_ID;
2023 if (rttestid <> -1) then
2024 FND_RT_REQUEST.LOG_REQUEST(rttestid, cur_request_id);
2025 end if;
2026 IF (P_USES_REL_CLASS = 1 AND schedule_type = 'X') THEN
2027 begin
2028 select c.resubmit_interval, c.resubmit_interval_unit_code, c.resubmit_interval_type_code
2029 into adv_sch_ri, adv_sch_riuc, adv_sch_ritc
2030 from fnd_conc_release_classes c, fnd_concurrent_requests r
2031 where r.request_id = cur_request_id and r.release_class_id = c.release_class_id
2032 and r.release_class_app_id = c.application_id;
2033 exception when others then
2034 adv_sch_ri := null;
2035 adv_sch_riuc := null;
2036 adv_sch_ritc := null;
2037 end;
2038 update fnd_concurrent_requests fcr
2039 set fcr.resubmit_interval = nvl(adv_sch_ri,1),
2040 fcr.resubmit_interval_unit_code = nvl(adv_sch_riuc,'DAYS'),
2041 fcr.resubmit_interval_type_code = nvl(adv_sch_ritc,'START'),
2042 fcr.status_code = 'P'
2043 where fcr.request_id = cur_request_id;
2044 END IF ;
2045 --
2046 -- raising business event for request submitted
2047 -- bug6623316
2048 --
2049 discard_status:=fnd_concurrent_business_event.raise_cp_bi_event(cur_request_id,fnd_concurrent_business_event.request_submitted);
2050
2051 return (cur_request_id);
2052
2053 exception
2054 when printer_error then
2055 fnd_message.set_name ('FND', 'CONC-Illegal printer spec');
2056 return (0);
2057 when style_error then
2058 fnd_message.set_name ('FND', 'SRS-Must specify print style');
2059 return (0);
2060 when printer_styl_error then
2061 fnd_message.set_name ('FND', 'CONC-Invalid printer style');
2062 fnd_message.set_token ('STYLE', print_style, FALSE);
2063 fnd_message.set_token ('PRINTER', curr_printer, FALSE);
2064 return (0);
2065 when srw_style_error then
2066 fnd_message.set_name ('FND', 'SRS-Must specify srw style');
2067 return (0);
2068 when oracle_name_error then
2069 fnd_message.set_name ('FND', 'CONC-Failed to get oracle name');
2070 return (0);
2071 when start_time_error then
2072 fnd_message.set_name ('FND', 'CONC-Invalid Req Start Date');
2073 fnd_message.set_token ('START_DATE', requested_start, FALSE);
2074 return (0);
2075 when dup_val_on_index then
2076 fnd_message.set_name ('FND', 'CONC-Duplicate request id');
2077 fnd_message.set_token ('REQUEST_ID', cur_request_id, FALSE);
2078 return (0);
2079 when nls_error then
2080 fnd_message.set_name ('FND', 'GLI-Not found');
2081 return (0);
2082 when appl_prog_error then -- Appl or program invalid
2083 begin -- Is the appl short name correct?
2084 Select Application_ID
2085 Into prog_appl_id
2086 From Fnd_Application
2087 Where Application_Short_Name = upper (application);
2088
2089 exception
2090 when no_data_found then
2091 fnd_message.set_name ('FND', 'CONC-Invalid Appl Short Name');
2092 fnd_message.set_token ('APPLNAME', application, FALSE);
2093 return (0);
2094 when others then
2095 fnd_message.set_name ('FND', 'SQL-Generic error');
2096 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
2097 fnd_message.set_token ('REASON', sqlerrm, FALSE);
2098 fnd_message.set_token (
2099 'ROUTINE', 'SUBMIT: appl_prog_error', FALSE);
2100 return (0);
2101 end; -- Is the appl short name correct?
2102
2103 -- It must be the program
2104 fnd_message.set_name ('FND', 'CONC-Invalid Conc program');
2105 fnd_message.set_token ('PROGRAM', program, FALSE);
2106 return (0);
2107
2108 when insert_error then -- Find the problem
2109 begin -- Is the program enabled?
2110 Select null
2111 Into Dummy
2112 From Fnd_Concurrent_Programs
2113 Where Application_ID = prog_appl_id
2114 And upper (Concurrent_Program_Name) = upper (program)
2115 And Enabled_Flag = 'Y';
2116
2117 exception
2118 when no_data_found then
2119 fnd_message.set_name ('FND', 'CONC-Conc program disabled');
2120 fnd_message.set_token ('PROGRAM', program, FALSE);
2121 return (0);
2122 when others then
2123 fnd_message.set_name ('FND', 'SQL-Generic error');
2124 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
2125 fnd_message.set_token ('REASON', sqlerrm, FALSE);
2126 fnd_message.set_token (
2127 'ROUTINE', 'SUBMIT: insert_error2', FALSE);
2128 return (0);
2129 end; -- Is the program enabled?
2130
2131 when args_insert_error then
2132 fnd_message.set_name ('FND', 'SQL-Generic error');
2133 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
2134 fnd_message.set_token ('REASON', sqlerrm, FALSE);
2135 fnd_message.set_token (
2136 'ROUTINE', 'SUBMIT: args_insert_error', FALSE);
2137 return (0);
2138 when login_insert_error then
2139 fnd_message.set_name ('FND', 'CONC-Login insert failed');
2140 fnd_message.set_token ('LOGIN', loginid, FALSE);
2141 return (0);
2142 when relclass_insert_error then
2143 fnd_message.set_name ('FND', 'CONC-RelClass insert failed');
2144 fnd_message.set_token ('APPLICATION', P_REL_CLASS_APP_ID, FALSE);
2145 fnd_message.set_token ('CLASS', P_REL_CLASS_ID, FALSE);
2146 return (0);
2147 when dual_no_rows then
2148 fnd_message.set_name ('FND', 'No Rows in Dual');
2149 return (0);
2150 when dual_too_many_rows then
2151 fnd_message.set_name ('FND', 'Too many rows in Dual');
2152 return (0);
2153 when already_msg then
2154 return (0);
2155 when pp_plsql_exception then
2156 fnd_message.set_name ('FND', 'CONC-assign pp action failed');
2157 return (0);
2158 when conc_deferred_insert_error then
2159 fnd_message.set_name ('FND', 'Conc-def-arg insert failed');
2160 fnd_message.set_token ('APPLICATION', application, FALSE);
2161 fnd_message.set_token ('PROGRAM', program, FALSE);
2162 return (0);
2163 when mult_pending_request_error then
2164 fnd_message.set_name ('FND', 'CONC-SINGLE PENDING REQUEST');
2165 fnd_message.set_token ('PROGRAM', program, FALSE);
2166 return (0);
2167 when others then
2168 fnd_message.set_name ('FND', 'SQL-Generic error');
2169 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
2170 fnd_message.set_token ('REASON', sqlerrm, FALSE);
2171 fnd_message.set_token (
2172 'ROUTINE', 'SUBMIT: others', FALSE);
2173 return (0);
2174
2175 end submit;
2176
2177
2178
2179
2180 function submit_mls_request (
2181 application IN varchar2 default NULL,
2182 program IN varchar2 default NULL,
2183 description IN varchar2 default NULL,
2184 start_time IN varchar2 default NULL,
2185 sub_request IN boolean default FALSE,
2186 p_argument1 IN varchar2 default CHR(0),
2187 p_argument2 IN varchar2 default CHR(0),
2188 p_argument3 IN varchar2 default CHR(0),
2189 p_argument4 IN varchar2 default CHR(0),
2190 p_argument5 IN varchar2 default CHR(0),
2191 p_argument6 IN varchar2 default CHR(0),
2192 p_argument7 IN varchar2 default CHR(0),
2193 p_argument8 IN varchar2 default CHR(0),
2194 p_argument9 IN varchar2 default CHR(0),
2195 p_argument10 IN varchar2 default CHR(0),
2196 p_argument11 IN varchar2 default CHR(0),
2197 p_argument12 IN varchar2 default CHR(0),
2198 p_argument13 IN varchar2 default CHR(0),
2199 p_argument14 IN varchar2 default CHR(0),
2200 p_argument15 IN varchar2 default CHR(0),
2201 p_argument16 IN varchar2 default CHR(0),
2202 p_argument17 IN varchar2 default CHR(0),
2203 p_argument18 IN varchar2 default CHR(0),
2204 p_argument19 IN varchar2 default CHR(0),
2205 p_argument20 IN varchar2 default CHR(0),
2206 p_argument21 IN varchar2 default CHR(0),
2207 p_argument22 IN varchar2 default CHR(0),
2208 p_argument23 IN varchar2 default CHR(0),
2209 p_argument24 IN varchar2 default CHR(0),
2210 p_argument25 IN varchar2 default CHR(0),
2211 p_argument26 IN varchar2 default CHR(0),
2212 p_argument27 IN varchar2 default CHR(0),
2213 p_argument28 IN varchar2 default CHR(0),
2214 p_argument29 IN varchar2 default CHR(0),
2215 p_argument30 IN varchar2 default CHR(0),
2216 p_argument31 IN varchar2 default CHR(0),
2217 p_argument32 IN varchar2 default CHR(0),
2218 p_argument33 IN varchar2 default CHR(0),
2219 p_argument34 IN varchar2 default CHR(0),
2220 p_argument35 IN varchar2 default CHR(0),
2221 p_argument36 IN varchar2 default CHR(0),
2222 p_argument37 IN varchar2 default CHR(0),
2223 p_argument38 IN varchar2 default CHR(0),
2224 p_argument39 IN varchar2 default CHR(0),
2225 p_argument40 IN varchar2 default CHR(0),
2226 p_argument41 IN varchar2 default CHR(0),
2227 p_argument42 IN varchar2 default CHR(0),
2228 p_argument43 IN varchar2 default CHR(0),
2229 p_argument44 IN varchar2 default CHR(0),
2230 p_argument45 IN varchar2 default CHR(0),
2231 p_argument46 IN varchar2 default CHR(0),
2232 p_argument47 IN varchar2 default CHR(0),
2233 p_argument48 IN varchar2 default CHR(0),
2234 p_argument49 IN varchar2 default CHR(0),
2235 p_argument50 IN varchar2 default CHR(0),
2236 p_argument51 IN varchar2 default CHR(0),
2237 p_argument52 IN varchar2 default CHR(0),
2238 p_argument53 IN varchar2 default CHR(0),
2239 p_argument54 IN varchar2 default CHR(0),
2240 p_argument55 IN varchar2 default CHR(0),
2241 p_argument56 IN varchar2 default CHR(0),
2242 p_argument57 IN varchar2 default CHR(0),
2243 p_argument58 IN varchar2 default CHR(0),
2244 p_argument59 IN varchar2 default CHR(0),
2245 p_argument60 IN varchar2 default CHR(0),
2246 p_argument61 IN varchar2 default CHR(0),
2247 p_argument62 IN varchar2 default CHR(0),
2248 p_argument63 IN varchar2 default CHR(0),
2249 p_argument64 IN varchar2 default CHR(0),
2250 p_argument65 IN varchar2 default CHR(0),
2251 p_argument66 IN varchar2 default CHR(0),
2252 p_argument67 IN varchar2 default CHR(0),
2253 p_argument68 IN varchar2 default CHR(0),
2254 p_argument69 IN varchar2 default CHR(0),
2255 p_argument70 IN varchar2 default CHR(0),
2256 p_argument71 IN varchar2 default CHR(0),
2257 p_argument72 IN varchar2 default CHR(0),
2258 p_argument73 IN varchar2 default CHR(0),
2259 p_argument74 IN varchar2 default CHR(0),
2260 p_argument75 IN varchar2 default CHR(0),
2261 p_argument76 IN varchar2 default CHR(0),
2262 p_argument77 IN varchar2 default CHR(0),
2263 p_argument78 IN varchar2 default CHR(0),
2264 p_argument79 IN varchar2 default CHR(0),
2265 p_argument80 IN varchar2 default CHR(0),
2266 p_argument81 IN varchar2 default CHR(0),
2267 p_argument82 IN varchar2 default CHR(0),
2268 p_argument83 IN varchar2 default CHR(0),
2269 p_argument84 IN varchar2 default CHR(0),
2270 p_argument85 IN varchar2 default CHR(0),
2271 p_argument86 IN varchar2 default CHR(0),
2272 p_argument87 IN varchar2 default CHR(0),
2273 p_argument88 IN varchar2 default CHR(0),
2274 p_argument89 IN varchar2 default CHR(0),
2275 p_argument90 IN varchar2 default CHR(0),
2276 p_argument91 IN varchar2 default CHR(0),
2277 p_argument92 IN varchar2 default CHR(0),
2278 p_argument93 IN varchar2 default CHR(0),
2279 p_argument94 IN varchar2 default CHR(0),
2280 p_argument95 IN varchar2 default CHR(0),
2281 p_argument96 IN varchar2 default CHR(0),
2282 p_argument97 IN varchar2 default CHR(0),
2283 p_argument98 IN varchar2 default CHR(0),
2284 p_argument99 IN varchar2 default CHR(0),
2285 p_argument100 IN varchar2 default CHR(0),
2286 func_exists IN varchar2 default 'N')
2287 return number is
2288
2289 user_prog_name varchar2(240);
2290 conc_prog_id number;
2291 conc_app_id number;
2292 fcp_printer varchar2(30);
2293 reqid number;
2294 i number;
2295 default_printer varchar2(30);
2296 profile_buffer varchar2(80) := null;
2297 default_copies number;
2298 curr_printer varchar2(30);
2299 curr_copies number;
2300 curr_lang varchar2(30);
2301
2302 begin
2303
2304 -- Set request type to B before submitting the parent request
2305 P_REQUEST_TYPE := 'B';
2306
2307 select fcpt.user_concurrent_program_name, fcp.concurrent_program_id,
2308 fa.application_id, printer_name
2309 into user_prog_name, conc_prog_id, conc_app_id, fcp_printer
2310 from fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcpt, fnd_application fa
2311 where fcp.concurrent_program_id=fcpt.concurrent_program_id
2312 and fcp.application_id=fcpt.application_id
2313 and fa.application_id=fcp.application_id
2314 and fcpt.language = userenv('LANG')
2315 and fcp.concurrent_program_name= upper(program)
2316 and fa.application_short_name = upper(application);
2317
2318
2319 -- Submit the FNDMLSUB parent request
2320 reqid := submit(
2321 'FND',
2322 'FNDMLSUB',
2323 user_prog_name,
2324 start_time,
2325 sub_request,
2326 to_char(conc_app_id),
2327 to_char(conc_prog_id),
2328 func_exists, chr(0),
2329 '','','','','','','','','','',
2330 '','','','','','','','','','',
2331 '','','','','','','','','','',
2332 '','','','','','','','','','',
2333 '','','','','','','','','','',
2334 '','','','','','','','','','',
2335 '','','','','','','','','','',
2336 '','','','','','','','','','',
2337 '','','','','','','','','','',
2338 '','','','','',''
2339 );
2340
2341 -- Return if submission failed
2342 if (reqid = 0) then
2343 return 0;
2344 end if;
2345
2346 -- Return if deferred request was submitted this time
2347 if P_DEF_REQUEST_ID <> 0 then
2348 return reqid;
2349 end if;
2350
2351
2352 -- Insert request details into FND_RUN_REQUESTS
2353 insert into fnd_run_requests
2354 (application_id, concurrent_program_id, parent_request_id,
2355 request_set_program_id, set_application_id, request_set_id,
2356 printer, print_style, save_output_flag, number_of_copies,
2357 nls_language, nls_territory, numeric_characters, ops_instance, description,
2358 argument1, argument2, argument3, argument4, argument5,
2359 argument6, argument7, argument8, argument9, argument10,
2360 argument11, argument12, argument13, argument14, argument15,
2361 argument16, argument17, argument18, argument19, argument20,
2362 argument21, argument22, argument23, argument24, argument25,
2363 argument26, argument27, argument28, argument29, argument30,
2364 argument31, argument32, argument33, argument34, argument35,
2365 argument36, argument37, argument38, argument39, argument40,
2366 argument41, argument42, argument43, argument44, argument45,
2367 argument46, argument47, argument48, argument49, argument50,
2368 argument51, argument52, argument53, argument54, argument55,
2369 argument56, argument57, argument58, argument59, argument60,
2370 argument61, argument62, argument63, argument64, argument65,
2371 argument66, argument67, argument68, argument69, argument70,
2372 argument71, argument72, argument73, argument74, argument75,
2373 argument76, argument77, argument78, argument79, argument80,
2374 argument81, argument82, argument83, argument84, argument85,
2375 argument86, argument87, argument88, argument89, argument90,
2376 argument91, argument92, argument93, argument94, argument95,
2377 argument96, argument97, argument98, argument99, argument100, ORG_ID)
2378 values
2379 (conc_app_id, conc_prog_id, reqid,
2380 0, 0, 0,
2381 null, P_PRINT_STYLE, P_SAVE_OUTPUT, null,
2382 P_LANGUAGE, P_TERRITORY, P_NUMERIC_CHARACTERS, -1, submit_mls_request.description,
2383 p_argument1, p_argument2, p_argument3, p_argument4, p_argument5,
2384 p_argument6, p_argument7, p_argument8, p_argument9, p_argument10,
2385 p_argument11, p_argument12, p_argument13, p_argument14, p_argument15,
2386 p_argument16, p_argument17, p_argument18, p_argument19, p_argument20,
2387 p_argument21, p_argument22, p_argument23, p_argument24, p_argument25,
2388 p_argument26, p_argument27, p_argument28, p_argument29, p_argument30,
2389 p_argument31, p_argument32, p_argument33, p_argument34, p_argument35,
2390 p_argument36, p_argument37, p_argument38, p_argument39, p_argument40,
2391 p_argument41, p_argument42, p_argument43, p_argument44, p_argument45,
2392 p_argument46, p_argument47, p_argument48, p_argument49, p_argument50,
2393 p_argument51, p_argument52, p_argument53, p_argument54, p_argument55,
2394 p_argument56, p_argument57, p_argument58, p_argument59, p_argument60,
2395 p_argument61, p_argument62, p_argument63, p_argument64, p_argument65,
2396 p_argument66, p_argument67, p_argument68, p_argument69, p_argument70,
2397 p_argument71, p_argument72, p_argument73, p_argument74, p_argument75,
2398 p_argument76, p_argument77, p_argument78, p_argument79, p_argument80,
2399 p_argument81, p_argument82, p_argument83, p_argument84, p_argument85,
2400 p_argument86, p_argument87, p_argument88, p_argument89, p_argument90,
2401 p_argument91, p_argument92, p_argument93, p_argument94, p_argument95,
2402 p_argument96, p_argument97, p_argument98, p_argument99, p_argument100, P_ORG_ID);
2403
2404
2405 -- Insert Languages
2406 if ( func_exists = 'N' ) then
2407 for i in 1..P_NLS_PARAMETERS_COUNT loop
2408
2409 insert into fnd_run_req_languages
2410 ( parent_request_id,
2411 nls_language,
2412 nls_territory,
2413 numeric_characters,
2414 created_by,
2415 creation_date,
2416 last_updated_by,
2417 last_update_date,
2418 last_update_login)
2419 values
2420 ( reqid,
2421 P_NLS_PARAMETERS(i).nls_lang,
2422 P_NLS_PARAMETERS(i).nls_territory,
2423 P_NLS_PARAMETERS(i).nls_num_char,
2424 FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.USER_ID,
2425 sysdate, FND_GLOBAL.CONC_LOGIN_ID);
2426
2427 end loop;
2428 end if;
2429
2430 P_NLS_PARAMETERS_COUNT :=0;
2431
2432 -- Insert Notifications
2433 if (P_NOTIFICATION_COUNT > 0) then
2434 for i in 1..P_NOTIFICATION_COUNT loop
2435
2436 insert into fnd_run_req_pp_actions
2437 (parent_request_id,
2438 request_set_id,
2439 set_application_id,
2440 request_set_program_id,
2441 action_type,
2442 status_s_flag,
2443 status_w_flag,
2444 status_f_flag,
2445 program_application_id,
2446 program_id,
2447 arguments,
2448 number_of_copies,
2449 sequence,
2450 orig_system,
2451 orig_system_id,
2452 nls_language,
2453 ops_instance)
2454 values
2455 (reqid,
2456 0, 0, 0,
2457 2,
2458 P_NOTIFICATIONS(i).on_normal,
2459 P_NOTIFICATIONS(i).on_warning,
2460 P_NOTIFICATIONS(i).on_error,
2461 null, null,
2462 P_NOTIFICATIONS(i).name, null, i,
2463 P_NOTIFICATIONS(i).orig_system,
2464 P_NOTIFICATIONS(i).orig_system_id,
2465 P_NOTIFICATIONS(i).lang, -1);
2466 end loop;
2467 end if;
2468
2469 P_NOTIFICATION_COUNT :=0;
2470
2471 -- Insert layout
2472 if (P_LAYOUT_COUNT > 0) then
2473 for i in 1..P_LAYOUT_COUNT loop
2474 insert into fnd_run_req_pp_actions
2475 (parent_request_id,
2476 request_set_id,
2477 set_application_id,
2478 request_set_program_id,
2479 action_type,
2480 status_s_flag,
2481 status_w_flag,
2482 status_f_flag,
2483 program_application_id,
2484 program_id,
2485 sequence,
2486 argument1,
2487 argument2,
2488 argument3,
2489 argument4,
2490 argument5,
2491 nls_language,
2492 ops_instance)
2493 values
2494 (reqid,
2495 0, 0, 0,
2496 6, 'Y', 'N', 'N', conc_app_id, conc_prog_id, i,
2497 P_LAYOUTS(i).template_appl_name,
2498 P_LAYOUTS(i).template_code,
2499 P_LAYOUTS(i).template_language,
2500 P_LAYOUTS(i).template_territory,
2501 P_LAYOUTS(i).output_format,
2502 P_LAYOUTS(i).nls_language,
2503 -1);
2504 end loop;
2505 end if;
2506
2507 P_LAYOUT_COUNT := 0;
2508
2509
2510 -- Insert Printers
2511 if (FCP_PRINTER is null) then
2512 FND_PROFILE.GET ('PRINTER', default_printer);
2513 else
2514 default_printer := fcp_printer;
2515 end if;
2516
2517 FND_PROFILE.GET ('CONC_COPIES', profile_buffer);
2518 if (profile_buffer is not null) then
2519 default_copies := To_Number (profile_buffer);
2520 if (default_copies < 0) then
2521 default_copies := 0;
2522 end if;
2523 else
2524 default_copies := 0;
2525 end if;
2526
2527 if ((P_PRINTER_COUNT = 0) and (default_copies >= 0)) then
2528 P_PRINTERS(1).printer := default_printer;
2529 P_PRINTERS(1).copies := default_copies;
2530 P_PRINTERS(1).lang := null;
2531 P_PRINTER_COUNT := 1;
2532 end if;
2533
2534 for i in 1..P_PRINTER_COUNT loop
2535
2536 curr_printer := P_PRINTERS(i).printer;
2537 curr_copies := P_PRINTERS(i).copies;
2538 curr_lang := P_PRINTERS(i).lang;
2539
2540 if (curr_copies is null) then
2541 curr_copies := default_copies;
2542 end if;
2543
2544 if (curr_printer is null) then
2545 curr_printer := default_printer;
2546 end if;
2547
2548 insert into fnd_run_req_pp_actions
2549 (parent_request_id,
2550 request_set_id,
2551 set_application_id,
2552 request_set_program_id,
2553 action_type,
2554 status_s_flag,
2555 status_w_flag,
2556 status_f_flag,
2557 program_application_id,
2558 program_id,
2559 arguments,
2560 number_of_copies,
2561 sequence,
2562 nls_language,
2563 ops_instance)
2564 values
2565 (reqid,
2566 0, 0, 0,
2567 1, 'Y', 'N', 'N', null, null,
2568 curr_printer, curr_copies, i, curr_lang, -1);
2569 end loop;
2570
2571
2572 -- Insert delivery options
2573 if (P_DELIV_OPTS_COUNT > 0) then
2574 for i in 1..P_DELIV_OPTS_COUNT loop
2575 insert into fnd_run_req_pp_actions
2576 (parent_request_id,
2577 request_set_id,set_application_id,request_set_program_id,
2578 action_type,status_s_flag,status_w_flag,status_f_flag,
2579 program_application_id,program_id,sequence,
2580 argument1,argument2,
2581 argument3,argument4,
2582 argument5,argument6,
2583 argument7,argument8,
2584 argument9,argument10,
2585 nls_language,ops_instance)
2586 values
2587 (reqid,
2588 0, 0, 0,
2589 decode( P_DELIVERY_OPTIONS(i).argument1, 'B', 8, 7 ),
2590 'Y', 'Y', 'N',
2591 conc_app_id, conc_prog_id, i,
2592 P_DELIVERY_OPTIONS(i).argument1,P_DELIVERY_OPTIONS(i).argument2,
2593 P_DELIVERY_OPTIONS(i).argument3,P_DELIVERY_OPTIONS(i).argument4,
2594 P_DELIVERY_OPTIONS(i).argument5,P_DELIVERY_OPTIONS(i).argument6,
2595 P_DELIVERY_OPTIONS(i).argument7,P_DELIVERY_OPTIONS(i).argument8,
2596 P_DELIVERY_OPTIONS(i).argument9,P_DELIVERY_OPTIONS(i).argument10,
2597 P_DELIVERY_OPTIONS(i).lang, -1);
2598 end loop;
2599 end if;
2600
2601 P_DELIV_OPTS_COUNT := 0;
2602
2603
2604 return reqid;
2605
2606 exception
2607 when others then
2608 fnd_message.set_name ('FND', 'SQL-Generic error');
2609 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
2610 fnd_message.set_token ('REASON', sqlerrm, FALSE);
2611 fnd_message.set_token ('ROUTINE', 'FND_REQUEST.SUBMIT_MLS_REQUEST', FALSE);
2612 return (0);
2613
2614
2615 end;
2616
2617 -- PUBLIC FUNCTIONS
2618 --
2619 -- --
2620
2621 --
2622 -- Name
2623 -- set_mode
2624 -- Purpose
2625 -- Called before submitting request to set database trigger mode
2626 --
2627 -- Arguments
2628 -- db_trigger - Set to TRUE for database trigger mode
2629 --
2630 function set_mode (db_trigger IN boolean) return boolean is
2631 begin
2632 P_DB_TRIGGER_MODE := db_trigger;
2633 return TRUE;
2634 end;
2635
2636
2637 -- Name
2638 -- set_options
2639 -- Purpose
2640 -- Called before submitting request to set request attributes
2641 --
2642 -- Arguments
2643 -- implicit - nature of the request to be submitted
2644 -- - NO/YES/ERROR/WARNING
2645 -- protected - Is the request protected against updates
2646 -- - YES/NO default is NO
2647 -- language - NLS language
2648 -- territory - Language territory
2649 -- datagroup
2650 -- numeric_characters - NLS Numeric Characters
2651 --
2652 -- --
2653 function set_options (implicit IN varchar2 default 'NO',
2654 protected IN varchar2 default 'NO',
2655 language IN varchar2 default NULL,
2656 territory IN varchar2 default NULL,
2657 datagroup IN varchar2 default NULL,
2658 numeric_characters IN varchar2 default NULL)
2659 return boolean is
2660 dummy_val varchar2(30);
2661 dummy_count number := 0;
2662 nls_error exception;
2663 begin
2664 if ((implicit is null) or
2665 (upper (implicit) = 'NO')) then
2666 P_IMPLICIT := 'N';
2667 elsif (upper (implicit) = 'WARNING') then
2668 P_IMPLICIT := 'W';
2669 elsif (upper (implicit) = 'YES') then
2670 P_IMPLICIT := 'Y';
2671 elsif (upper (implicit) = 'ERROR') then
2672 P_IMPLICIT := 'E';
2673 else
2674 Fnd_Message.Set_Name ('FND', 'CONC-Bad implicit argument');
2675 init_pvt_vars;
2676 return (FALSE);
2677 end if;
2678
2679 if ( language is not null ) then
2680 begin
2681 select nls_language
2682 into dummy_val
2683 from fnd_languages
2684 where nls_language = upper(language);
2685 exception
2686 when no_data_found then
2687 raise nls_error;
2688 end;
2689 end if;
2690
2691 if ( territory is not null ) then
2692 begin
2693 select count(*)
2694 into dummy_count
2695 from fnd_territories
2696 where nls_territory = upper(territory);
2697 exception
2698 when no_data_found then
2699 raise nls_error;
2700 end;
2701 if ( dummy_count < 1 ) then
2702 raise nls_error;
2703 end if;
2704 end if;
2705
2706 /* NLS Project */
2707 if ( numeric_characters is not null ) then
2708 begin
2709 select lookup_code
2710 into dummy_val
2711 from fnd_lookup_values_vl
2712 where lookup_type = 'ICX_NUMERIC_CHARACTERS' and
2713 lookup_code = numeric_characters;
2714 exception
2715 when no_data_found then
2716 raise nls_error;
2717 end;
2718 end if;
2719
2720 P_LANGUAGE := language;
2721 P_TERRITORY := territory;
2722 /* NLS Project */
2723 P_NUMERIC_CHARACTERS := numeric_characters;
2724
2725 if (datagroup is not null) then
2726 P_DATAGROUP := datagroup;
2727 end if;
2728
2729 return (TRUE);
2730
2731 exception
2732 when nls_error then
2733 fnd_message.set_name ('FND', 'GLI-Not found');
2734 return(FALSE);
2735
2736 end set_options;
2737
2738
2739 --
2740 -- Name
2741 -- set_repeat_options
2742 -- Purpose
2743 -- Called before submitting request if the request to be submitted
2744 -- is a repeating request.
2745 --
2746 -- Arguments
2747 -- repeat_time - Time of day at which it has to be repeated
2748 -- repeat_interval - Frequency at which it has to be repeated
2749 -- - This will be used/applied only when repeat_time
2750 -- - is NULL ( non null repeat_interval overrides )
2751 -- repeat_unit - Unit for repeat interval. Default is DAYS.
2752 -- - MONTHS/DAYS/HOURS/MINUTES
2753 -- repeat_type - Apply repeat interval from START or END of request
2754 -- - default is START. START/END
2755 -- repeat_end_time - Time at which the repetition should be stopped
2756 -- incrment_dates - 'Y' if dates should be incremented each run,
2757 -- otherwise 'N'
2758 --
2759 function set_repeat_options (repeat_time IN varchar2 default NULL,
2760 repeat_interval IN number default NULL,
2761 repeat_unit IN varchar2 default 'DAYS',
2762 repeat_type IN varchar2 default 'START',
2763 repeat_end_time IN varchar2 default NULL,
2764 increment_dates IN varchar2 default NULL)
2765 return boolean is
2766
2767 rpt_other exception;
2768 rpt_time_intvl exception; -- Both resub time and interval are set
2769 rpt_type_error exception; -- Bad resub type
2770 rpt_time_error boolean := TRUE; -- Use this in exception handler to
2771 -- determine where the error occurred:
2772 -- in resub time or end date.
2773 base_len number;
2774 success boolean;
2775 begin
2776 P_REPEAT_TIME := NULL;
2777 P_REPEAT_INTERVAL := NULL;
2778 P_REPEAT_INTERVAL_UNIT := NULL;
2779 P_REPEAT_INTERVAL_TYPE := NULL;
2780 P_REPEAT_END := NULL;
2781
2782 if ((repeat_time is not NULL) and
2783 (repeat_interval is not NULL)) then
2784 raise rpt_time_intvl;
2785
2786 elsif (repeat_time is not NULL) then
2787
2788 Select To_Char (fnd_conc_date.string_to_date (
2789 '01-01-0001' || repeat_time),
2790 'HH24:MI:SS')
2791 Into P_REPEAT_TIME
2792 From Sys.Dual;
2793
2794 elsif (repeat_interval is not NULL) then
2795 P_REPEAT_INTERVAL := repeat_interval;
2796
2797 if (repeat_unit is null) then
2798 P_REPEAT_INTERVAL_UNIT := 'DAYS';
2799 else
2800 Select Lookup_Code
2801 Into P_REPEAT_INTERVAL_UNIT
2802 From Fnd_Lookups
2803 Where upper (Lookup_Code) = upper (repeat_unit)
2804 And Lookup_Type = 'CP_RESUBMIT_INTERVAL_UNIT';
2805 end if;
2806
2807 if (repeat_type is null) then
2808 P_REPEAT_INTERVAL_TYPE := 'START';
2809 elsif (upper (repeat_type) in ('START', 'END')) then
2810 P_REPEAT_INTERVAL_TYPE := upper (repeat_type);
2811 else
2812 raise rpt_type_error;
2813 end if;
2814
2815 end if; -- ((repeat_time is not NULL) and (repeat_interval is..
2816
2817 rpt_time_error := FALSE;
2818
2819 if (repeat_end_time is not null) then
2820 P_REPEAT_END := fnd_conc_date.string_to_date(repeat_end_time);
2821 if (P_REPEAT_END is null) then
2822 raise rpt_other;
2823 end if;
2824 end if;
2825
2826 set_increment_dates_option (increment_dates);
2827
2828 return (TRUE);
2829
2830 exception
2831 when rpt_time_intvl then
2832 fnd_message.set_name ('FND', 'CONC-Time and interval set');
2833 init_pvt_vars;
2834 return (FALSE);
2835
2836 when rpt_type_error then
2837 fnd_message.set_name ('FND', 'CONC-Bad resubmit type');
2838 init_pvt_vars;
2839 return (FALSE);
2840
2841 when no_data_found then
2842 fnd_message.set_name ('FND', 'CONC-Bad resubmit unit');
2843 init_pvt_vars;
2844 return (FALSE);
2845
2846 when others then
2847 if (rpt_time_error) then
2848 fnd_message.set_name ('FND', 'CONC-Invalid resubmit time');
2849 else
2850 fnd_message.set_name ('FND', 'CONC-Invalid Resub End Date');
2851 end if;
2852 init_pvt_vars;
2853 return (FALSE);
2854 end set_repeat_options;
2855
2856 --
2857 -- Name
2858 -- set_increment_dates_option
2859 -- Purpose
2860 -- Called before submitting request if the request to be submitted
2861 -- has a schedule set that repeats. Making this available outside of
2862 -- set_repeat_options in case the repeating schedule is set with
2863 -- set_rel_class_options or fnd_conc_release_class_utils.assign_specific_sch
2864 --
2865 -- Arguments
2866 -- incrment_dates - 'Y' if dates should be incremented each run,
2867 -- otherwise 'N'
2868 --
2869 procedure set_increment_dates_option (increment_dates IN varchar2)
2870 is
2871
2872 begin
2873 P_INCREMENT_DATES := NULL;
2874
2875 if (increment_dates = 'Y') then
2876 P_INCREMENT_DATES := increment_dates;
2877 else
2878 P_INCREMENT_DATES := 'N';
2879 end if;
2880
2881 end set_increment_dates_option;
2882
2883 --
2884 -- Name
2885 -- set_rel_class_options
2886 -- Purpose
2887 -- Called before submitting request if the request to be submitted
2888 -- is using the new scheduler functionality.
2889 --
2890 -- Arguments
2891 -- application - Application Name of Release Class
2892 -- class_name - (Developer) Name of Release Class
2893 -- cancel_or_hold - Cancel or hold flag
2894 -- stale_date - Cancel request on or after this time if not run
2895 --
2896 function set_rel_class_options (application IN varchar2 default NULL,
2897 class_name IN varchar2 default NULL,
2898 cancel_or_hold IN varchar2 default 'H',
2899 stale_date IN varchar2 default NULL)
2900 return boolean is
2901
2902 stale_date_error exception; -- Stale date couldn't be interpreted
2903 appl_class_error exception; -- Couldn't find App or class
2904
2905 begin
2906 P_REL_CLASS_APP_ID := null;
2907 P_REL_CLASS_ID := null;
2908 P_REL_STALE_DATE := null;
2909 P_REL_CANCEL_OR_HOLD := cancel_or_hold;
2910
2911
2912 if (stale_date is not NULL) then
2913 P_REL_STALE_DATE := fnd_conc_date.string_to_date(stale_date);
2914 if (P_REL_STALE_DATE is null) then
2915 raise stale_date_error;
2916 end if;
2917 end if;
2918
2919 if (class_name is not null) then
2920 begin
2921 Select A.Application_ID, C.Release_Class_Id
2922 Into P_REL_CLASS_APP_ID, P_REL_CLASS_ID
2923 From Fnd_Conc_Release_Classes C, Fnd_Application A
2924 Where Upper(Release_class_Name) = upper(class_name)
2925 And C.Application_ID = A.Application_ID
2926 And A.Application_Short_Name = upper(application);
2927
2928 exception
2929 when no_data_found then
2930 raise appl_class_error;
2931 when others then
2932 raise;
2933 end;
2934 end if;
2935
2936 P_USES_REL_CLASS := 1;
2937
2938 return (TRUE);
2939
2940 exception
2941 when stale_date_error then
2942 fnd_message.set_name ('FND', 'CONC-Bad Stale Date');
2943 init_pvt_vars;
2944 return (FALSE);
2945
2946 when appl_class_error then
2947 fnd_message.set_name ('FND', 'CONC-Bad Release Class');
2948 init_pvt_vars;
2949 return (FALSE);
2950
2951 when others then
2952 init_pvt_vars;
2953 raise;
2954 return (FALSE);
2955 end set_rel_class_options;
2956
2957 -- Name
2958 -- set_target_options
2959 -- Purpose
2960 -- Called before submitting request to set target node and or connect
2961 -- string (instance) for the request. Currently only primary target
2962 -- node and connect string are honored and the connect string must be
2963 -- the name of a RAC instance.
2964 --
2965 -- Arguments
2966 -- node_name1 - primary target node
2967 -- instance1 - primary target instance (connect string)
2968 -- node_name2 - secondary target node
2969 -- instance2 - secondary target instance (connect string)
2970 --
2971 -- --
2972 function set_target_options (node_name1 IN varchar2 default NULL,
2973 instance1 IN varchar2 default NULL,
2974 node_name2 IN varchar2 default NULL,
2975 instance2 IN varchar2 default NULL)
2976 return boolean is
2977
2978 dummy_val varchar2(80);
2979
2980 begin
2981
2982 if ( node_name1 is not null ) then
2983 begin
2984 select node_name
2985 into dummy_val
2986 from fnd_nodes
2987 where upper(node_name) = upper(node_name1);
2988 exception
2989 when no_data_found then
2990 fnd_message.set_name ('FND', 'CONC-Node not found');
2991 fnd_message.set_token ('NODE_NAME', node_name1, FALSE);
2992 raise;
2993 end;
2994 end if;
2995
2996 if ( instance1 is not null ) then
2997 begin
2998 select instance
2999 into dummy_val
3000 from v$thread
3001 where upper(instance) = upper(instance1);
3002 exception
3003 when no_data_found then
3004 fnd_message.set_name ('FND', 'CONC-Instance not found');
3005 fnd_message.set_token ('INSTANCE_NAME', instance1, FALSE);
3006 raise;
3007 end;
3008 end if;
3009
3010 P_NODE_NAME1 := node_name1;
3011 P_CONNSTR1 := instance1;
3012
3013 return (TRUE);
3014
3015 exception
3016 when others then
3017 return(FALSE);
3018
3019 end set_target_options;
3020
3021
3022 --
3023 -- Name
3024 -- set_print_options
3025 -- Purpose
3026 -- Called before submitting request if the printing of output has
3027 -- to be controlled with specific printer/style/copies etc.,
3028 --
3029 -- Arguments
3030 -- printer - Printer name where the request o/p should be sent
3031 -- style - Print style that needs to be used for printing
3032 -- copies - Number of copies to print
3033 -- save_output - Should the output file be saved after printing
3034 -- - Default is TRUE. TRUE/FALSE
3035 -- print_together - Applies only for sub requests. If 'Y', output
3036 -- - will not be printed until all the sub requests
3037 -- - complete. Default is N. Y/N
3038 -- validate_printer - Once submit function is called
3039 -- - with a specific program, if the printer specified
3040 -- - here conflicts with a printer setting at the
3041 -- - program level, one of three options is available:
3042 -- - FAIL - raise an error and fail to submit
3043 -- - SKIP - skip this print pp action, but submit anyway
3044 -- - RESOLVE - switch to the valid printer, if printer
3045 -- - and style are compatible
3046 -- - Default is RESOLVE
3047 function set_print_options (printer IN varchar2 default NULL,
3048 style IN varchar2 default NULL,
3049 copies IN number default NULL,
3050 save_output IN boolean default TRUE,
3051 print_together IN varchar2 default 'N',
3052 validate_printer IN varchar2 default 'RESOLVE')
3053 return boolean is
3054
3055 printer_typ varchar2 (30) := null;
3056 dummy_fld varchar (2);
3057
3058 validate_printer_error exception;
3059 print_together_error exception;
3060 printer_error exception;
3061 style_error exception;
3062 printer_style_error exception;
3063 empty_array printer_tab_type;
3064 prec printer_record_type;
3065
3066 begin
3067 -- Clear any old printer options
3068 -- Just in case this was called twice.
3069 if P_PRINTER_COUNT > 0 then
3070 P_PRINTERS := empty_array;
3071 P_PRINTER_COUNT := 0;
3072 P_VALIDATE_PRINTER := 'RESOLVE';
3073 end if;
3074
3075 -- 5645007- set P_VALIDATE_PRINTER to determine
3076 -- behavior during submit if the printer specified here
3077 -- conflicts with a printer setting at the program level
3078
3079 if (UPPER(validate_printer)
3080 in ('FAIL','SKIP','RESOLVE')) then
3081 P_VALIDATE_PRINTER := UPPER(validate_printer);
3082 else
3083 raise validate_printer_error;
3084 end if;
3085
3086
3087 if (upper (print_together) not in ('Y', 'N')) then
3088 raise print_together_error;
3089 end if;
3090
3091 if (upper (print_together) = 'Y') then
3092 P_PRINT_TOGETHER := 'Y';
3093 elsif (upper (print_together) = 'N') then
3094 P_PRINT_TOGETHER := 'N';
3095 else
3096 P_PRINT_TOGETHER := NULL;
3097 end if;
3098
3099 if (save_output is null) then
3100 P_SAVE_OUTPUT := NULL;
3101 elsif (save_output) then
3102 P_SAVE_OUTPUT := 'Y';
3103 else
3104 P_SAVE_OUTPUT := 'N';
3105 end if;
3106
3107 if (printer is not null) then -- Verify printer
3108 begin
3109 Select printer_type
3110 Into printer_typ
3111 From fnd_printer
3112 Where Upper(printer_name) = Upper(printer);
3113
3114 exception
3115 when no_data_found then
3116 raise printer_error;
3117
3118 when others then
3119 raise;
3120 end;
3121 end if; -- Verify printer
3122
3123
3124 if (style is not null) then -- Verify style
3125 begin
3126 Select Printer_Style_Name
3127 Into P_PRINT_STYLE
3128 From Fnd_Printer_styles
3129 Where Upper(Printer_Style_Name) = Upper(style);
3130
3131 exception
3132 when no_data_found then
3133 raise style_error;
3134
3135 when others then
3136 raise;
3137 end;
3138 end if; -- Verify style
3139
3140 if ((printer is not null) and
3141 (style is not null)) then -- Verify printer/style comination
3142 begin
3143 Select 'X'
3144 Into Dummy_fld
3145 From Fnd_Printer_Information
3146 Where Upper(Printer_Style) = Upper(P_PRINT_STYLE)
3147 And Upper(Printer_Type) = Upper(printer_typ);
3148
3149 exception
3150 when no_data_found then
3151 raise printer_style_error;
3152
3153 when others then
3154 raise;
3155 end;
3156 end if; -- Verify printer/style comination
3157
3158 -- Add printer/copies to the list.
3159 -- Note that we will attempt to process the defaults
3160 -- for nulls at submission time. For now, store the
3161 -- nulls.
3162
3163 if (copies is not null or printer is not null) then
3164 P_PRINTER_COUNT := 1;
3165
3166 -- The following inderect assignment was required to get
3167 -- the procedure to compile. The problem, for some reason
3168 -- doesn't seem to affect the other procedures in this package.
3169 prec.printer := printer;
3170 prec.copies := copies;
3171 P_PRINTERS(1) := prec;
3172 end if;
3173
3174
3175 return (TRUE);
3176
3177 exception
3178 when validate_printer_error then
3179 fnd_message.set_name ('FND', 'CONC-Bad Validate Printer Parm');
3180 init_pvt_vars;
3181 return (FALSE);
3182
3183 when print_together_error then
3184 fnd_message.set_name ('FND', 'CONC-Invalid opt:Print Group');
3185 init_pvt_vars;
3186 return (FALSE);
3187
3188 when printer_error then
3189 fnd_message.set_name ('FND', 'PRINTERS-No system printer');
3190 fnd_message.set_token ('PRINTER', printer, FALSE);
3191 init_pvt_vars;
3192 return (FALSE);
3193
3194 when style_error then
3195 fnd_message.set_name ('FND', 'PRT-Invalid print style');
3196 fnd_message.set_token ('STYLE', style, FALSE);
3197 init_pvt_vars;
3198 return (FALSE);
3199
3200 when printer_style_error then
3201 fnd_message.set_name ('FND', 'CONC-Invalid printer style');
3202 fnd_message.set_token ('STYLE', style, FALSE);
3203 fnd_message.set_token ('PRINTER', printer, FALSE);
3204 init_pvt_vars;
3205 return (FALSE);
3206
3207 when others then
3208 init_pvt_vars;
3209 fnd_message.set_name ('FND', 'SQL-Generic error');
3210 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
3211 fnd_message.set_token ('REASON', sqlerrm, FALSE);
3212 fnd_message.set_token ('ROUTINE', 'SET_PRINT_OPTIONS', FALSE);
3213 return (FALSE);
3214 end set_print_options;
3215
3216
3217 --
3218 -- Name
3219 -- use_current_notification
3220 -- Purpose
3221 -- Called before submitting a sub-request to reuse the same notification
3222 -- options as the current request
3223 --
3224 -- Arguments
3225 -- none
3226 function use_current_notification return boolean is
3227 profile_buffer varchar2(80);
3228 reqid number;
3229 username varchar2(255);
3230 result boolean;
3231 empty_notify_array notification_tab_type;
3232
3233 cursor c_users is
3234 select arguments
3235 from fnd_conc_pp_actions
3236 where concurrent_request_id = reqid
3237 and action_type = 2
3238 order by sequence;
3239 begin
3240 -- get current request id
3241 reqid := FND_GLOBAL.conc_request_id;
3242
3243 if ((reqid <= 0) or (reqid is null))
3244 then
3245 init_pvt_vars;
3246 fnd_message.set_name('FND', 'CONC-REQUEST MISSING');
3247 fnd_message.set_token ('ROUTINE', 'USE_CURRENT_NOTIFICATION', FALSE);
3248 return (FALSE);
3249 end if;
3250
3251 -- clear out notifications
3252 P_NOTIFICATIONS := empty_notify_array;
3253 P_NOTIFICATION_COUNT := 0;
3254
3255 -- call add_notification for each user
3256 open c_users;
3257 loop
3258 fetch c_users into username;
3259 exit when c_users%NOTFOUND;
3260
3261 result := add_notification(username);
3262 end loop;
3263 close c_users;
3264
3265 return (TRUE);
3266
3267 exception
3268 when others then
3269 init_pvt_vars;
3270 fnd_message.set_name ('FND', 'SQL-Generic error');
3271 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
3272 fnd_message.set_token ('REASON', sqlerrm, FALSE);
3273 fnd_message.set_token ('ROUTINE', 'USE_CURRENT_NOTIFICATION', FALSE);
3274 return (FALSE);
3275
3276 end use_current_notification;
3277
3278
3279 --
3280 -- Name
3281 -- add_printer
3282 -- Purpose
3283 -- Called after set print options to add a printer to the
3284 -- print list.
3285 --
3286 -- Arguments
3287 -- printer - Printer name where the request o/p should be sent
3288 -- copies - Number of copies to print
3289
3290 -- Added an overload for only 2 parameter processing for function
3291 function add_printer (printer in varchar2 default null,
3292 copies in number default null
3293 ) return boolean is
3294 begin
3295
3296 return(add_printer(printer, copies, null));
3297
3298 end;
3299
3300 function add_printer (printer in varchar2 default null,
3301 copies in number default null,
3302 lang in varchar2) return boolean is
3303 printer_typ varchar2 (30) := null;
3304 dummy_fld varchar (2);
3305 print_together_error exception;
3306 printer_error exception;
3307 style_error exception;
3308 printer_style_error exception;
3309 begin
3310
3311 if (printer is not null) then -- Verify printer
3312 begin
3313 Select printer_type
3314 Into printer_typ
3315 From fnd_printer
3316 Where Upper(printer_name) = Upper(printer);
3317
3318 exception
3319 when no_data_found then
3320 raise printer_error;
3321
3322 when others then
3323 raise;
3324 end;
3325 end if; -- Verify printer
3326
3327
3328 if ((printer is not null) and
3329 (P_PRINT_STYLE is not null)) then -- Verify printer/style combo
3330 begin
3331 Select 'X'
3332 Into Dummy_fld
3333 From Fnd_Printer_Information
3334 Where Upper(Printer_Style) = Upper(P_PRINT_STYLE)
3335 And Upper(Printer_Type) = Upper(printer_typ);
3336
3337 exception
3338 when no_data_found then
3339 raise printer_style_error;
3340
3341 when others then
3342 raise;
3343 end;
3344 end if; -- Verify printer/style comination
3345
3346 -- Add printer/copies to the list.
3347 -- Note that we will attempt to process the defaults
3348 -- for nulls at submission time. For now, store the
3349 -- nulls.
3350 P_PRINTER_COUNT := P_PRINTER_COUNT + 1;
3351 P_PRINTERS(P_PRINTER_COUNT).printer := printer;
3352 P_PRINTERS(P_PRINTER_COUNT).copies := copies;
3353 P_PRINTERS(P_PRINTER_COUNT).lang := lang;
3354
3355 return (TRUE);
3356
3357 exception
3358 when print_together_error then
3359 fnd_message.set_name ('FND', 'CONC-Invalid opt:Print Group');
3360 init_pvt_vars;
3361 return (FALSE);
3362
3363 when printer_error then
3364 fnd_message.set_name ('FND', 'PRINTERS-No system printer');
3365 fnd_message.set_token ('PRINTER', printer, FALSE);
3366 init_pvt_vars;
3367 return (FALSE);
3368
3369 when style_error then
3370 fnd_message.set_name ('FND', 'PRT-Invalid print style');
3371 fnd_message.set_token ('STYLE', P_PRINT_STYLE, FALSE);
3372 init_pvt_vars;
3373 return (FALSE);
3374
3375 when printer_style_error then
3376 fnd_message.set_name ('FND', 'CONC-Invalid printer style');
3377 fnd_message.set_token ('STYLE', P_PRINT_STYLE, FALSE);
3378 fnd_message.set_token ('PRINTER', printer, FALSE);
3379 init_pvt_vars;
3380 return (FALSE);
3381
3382 when others then
3383 init_pvt_vars;
3384 fnd_message.set_name ('FND', 'SQL-Generic error');
3385 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
3386 fnd_message.set_token ('REASON', sqlerrm, FALSE);
3387 fnd_message.set_token ('ROUTINE', 'SET_PRINT_OPTIONS', FALSE);
3388 return (FALSE);
3389 end;
3390
3391
3392 --
3393 -- Name
3394 -- add_notification
3395 -- Purpose
3396 -- Called before submission to add a user to the notify list.
3397 --
3398 -- Arguments
3399 -- User - User name.
3400
3401 -- Added an overload for only 1 parameter processing for function
3402 function add_notification (user in varchar2
3403 ) return boolean is
3404 begin
3405
3406 return(add_notification(user,
3407 null));
3408
3409 end;
3410
3411 function add_notification (user in varchar2,
3412 nls_language in varchar2 ) return boolean is
3413 n_index number;
3414 cursor c1( user_name varchar2) is
3415 select name, orig_system, orig_system_id
3416 from wf_roles
3417 where name = user_name;
3418 begin
3419
3420 -- Same user may exists in different departments(tables).
3421 -- use cursor because we dont know the given user name will return one row
3422 -- multiple rows.
3423 -- we are considering only the first row that matched in wf_roles.
3424
3425 n_index := P_NOTIFICATION_COUNT + 1;
3426 open c1( user );
3427 fetch c1 into P_NOTIFICATIONS(n_index).name,
3428 P_NOTIFICATIONS(n_index).orig_system,
3429 P_NOTIFICATIONS(n_index).orig_system_id;
3430
3431 P_NOTIFICATIONS(n_index).on_normal := 'Y';
3432 P_NOTIFICATIONS(n_index).on_warning := 'Y';
3433 P_NOTIFICATIONS(n_index).on_error := 'Y';
3434
3435 P_NOTIFICATIONS(n_index).lang := nls_language;
3436
3437 if( c1%notfound ) then
3438 fnd_message.set_name('FND', 'CONC-INVALID NOTIFY USER');
3439 close c1;
3440 return FALSE;
3441 else
3442 P_NOTIFICATION_COUNT := P_NOTIFICATION_COUNT + 1;
3443 close c1;
3444 return TRUE;
3445 end if;
3446
3447 end;
3448
3449
3450
3451 --
3452 -- Name
3453 -- add_layout
3454 -- Purpose
3455 -- Called before submission to add layout options for request output.
3456 --
3457 -- Arguments
3458 -- Template_APPL_Name - Template Application Short name.
3459 -- Template_code - Template code
3460 -- Template_Language - Template File language (iso value)
3461 -- Template_Territory - Template File Territory (iso value)
3462 -- Output Format - Output Format
3463 --
3464
3465 -- Added an overload for only 5 paramter processing for function
3466 function add_layout (template_appl_name in varchar2,
3467 template_code in varchar2,
3468 template_language in varchar2,
3469 template_territory in varchar2,
3470 output_format in varchar2
3471 ) return boolean is
3472 begin
3473
3474 return(add_layout(template_appl_name,
3475 template_code,
3476 template_language,
3477 template_territory,
3478 output_format,
3479 null));
3480
3481 end;
3482
3483 function add_layout (template_appl_name in varchar2,
3484 template_code in varchar2,
3485 template_language in varchar2,
3486 template_territory in varchar2,
3487 output_format in varchar2,
3488 nls_language in varchar2) return boolean is
3489 begin
3490
3491 -- It is callers responsibility to provide valid values.
3492 P_LAYOUT_COUNT := P_LAYOUT_COUNT + 1;
3493
3494 P_LAYOUTS(P_LAYOUT_COUNT).template_appl_name := template_appl_name;
3495 P_LAYOUTS(P_LAYOUT_COUNT).template_code := template_code;
3496 P_LAYOUTS(P_LAYOUT_COUNT).template_language := template_language;
3497 P_LAYOUTS(P_LAYOUT_COUNT).template_territory := template_territory;
3498 P_LAYOUTS(P_LAYOUT_COUNT).output_format := output_format;
3499 P_LAYOUTS(P_LAYOUT_COUNT).nls_language := nls_language;
3500
3501 return (TRUE);
3502
3503 end;
3504
3505 --
3506 -- Name
3507 -- submit_request
3508 -- Purpose
3509 -- Submits concurrent request to be processed by a concurrent manager
3510 --
3511 -- Arguments
3512 -- application - Short name of application under which the program
3513 -- - is registered
3514 -- program - concurrent program name for which the request has
3515 -- - to be submitted
3516 -- description - Optional. Will be displayed along with user
3517 -- - concurrent program name
3518 -- start_time - Optional. Time at which the request has to start
3519 -- - running
3520 -- sub_request - Optional. Set to TRUE if the request is submitted
3521 -- - from another running request and has to be treated
3522 -- - as a sub request. Default is FALSE
3523 -- argument1..100 - Optional. Arguments for the concurrent request
3524 --
3525 function submit_request (
3526 application IN varchar2 default NULL,
3527 program IN varchar2 default NULL,
3528 description IN varchar2 default NULL,
3529 start_time IN varchar2 default NULL,
3530 sub_request IN boolean default FALSE,
3531 argument1 IN varchar2 default CHR(0),
3532 argument2 IN varchar2 default CHR(0),
3533 argument3 IN varchar2 default CHR(0),
3534 argument4 IN varchar2 default CHR(0),
3535 argument5 IN varchar2 default CHR(0),
3536 argument6 IN varchar2 default CHR(0),
3537 argument7 IN varchar2 default CHR(0),
3538 argument8 IN varchar2 default CHR(0),
3539 argument9 IN varchar2 default CHR(0),
3540 argument10 IN varchar2 default CHR(0),
3541 argument11 IN varchar2 default CHR(0),
3542 argument12 IN varchar2 default CHR(0),
3543 argument13 IN varchar2 default CHR(0),
3544 argument14 IN varchar2 default CHR(0),
3545 argument15 IN varchar2 default CHR(0),
3546 argument16 IN varchar2 default CHR(0),
3547 argument17 IN varchar2 default CHR(0),
3548 argument18 IN varchar2 default CHR(0),
3549 argument19 IN varchar2 default CHR(0),
3550 argument20 IN varchar2 default CHR(0),
3551 argument21 IN varchar2 default CHR(0),
3552 argument22 IN varchar2 default CHR(0),
3553 argument23 IN varchar2 default CHR(0),
3554 argument24 IN varchar2 default CHR(0),
3555 argument25 IN varchar2 default CHR(0),
3556 argument26 IN varchar2 default CHR(0),
3557 argument27 IN varchar2 default CHR(0),
3558 argument28 IN varchar2 default CHR(0),
3559 argument29 IN varchar2 default CHR(0),
3560 argument30 IN varchar2 default CHR(0),
3561 argument31 IN varchar2 default CHR(0),
3562 argument32 IN varchar2 default CHR(0),
3563 argument33 IN varchar2 default CHR(0),
3564 argument34 IN varchar2 default CHR(0),
3565 argument35 IN varchar2 default CHR(0),
3566 argument36 IN varchar2 default CHR(0),
3567 argument37 IN varchar2 default CHR(0),
3568 argument38 IN varchar2 default CHR(0),
3569 argument39 IN varchar2 default CHR(0),
3570 argument40 IN varchar2 default CHR(0),
3571 argument41 IN varchar2 default CHR(0),
3572 argument42 IN varchar2 default CHR(0),
3573 argument43 IN varchar2 default CHR(0),
3574 argument44 IN varchar2 default CHR(0),
3575 argument45 IN varchar2 default CHR(0),
3576 argument46 IN varchar2 default CHR(0),
3577 argument47 IN varchar2 default CHR(0),
3578 argument48 IN varchar2 default CHR(0),
3579 argument49 IN varchar2 default CHR(0),
3580 argument50 IN varchar2 default CHR(0),
3581 argument51 IN varchar2 default CHR(0),
3582 argument52 IN varchar2 default CHR(0),
3583 argument53 IN varchar2 default CHR(0),
3584 argument54 IN varchar2 default CHR(0),
3585 argument55 IN varchar2 default CHR(0),
3586 argument56 IN varchar2 default CHR(0),
3587 argument57 IN varchar2 default CHR(0),
3588 argument58 IN varchar2 default CHR(0),
3589 argument59 IN varchar2 default CHR(0),
3590 argument60 IN varchar2 default CHR(0),
3591 argument61 IN varchar2 default CHR(0),
3592 argument62 IN varchar2 default CHR(0),
3593 argument63 IN varchar2 default CHR(0),
3594 argument64 IN varchar2 default CHR(0),
3595 argument65 IN varchar2 default CHR(0),
3596 argument66 IN varchar2 default CHR(0),
3597 argument67 IN varchar2 default CHR(0),
3598 argument68 IN varchar2 default CHR(0),
3599 argument69 IN varchar2 default CHR(0),
3600 argument70 IN varchar2 default CHR(0),
3601 argument71 IN varchar2 default CHR(0),
3602 argument72 IN varchar2 default CHR(0),
3603 argument73 IN varchar2 default CHR(0),
3604 argument74 IN varchar2 default CHR(0),
3605 argument75 IN varchar2 default CHR(0),
3606 argument76 IN varchar2 default CHR(0),
3607 argument77 IN varchar2 default CHR(0),
3608 argument78 IN varchar2 default CHR(0),
3609 argument79 IN varchar2 default CHR(0),
3610 argument80 IN varchar2 default CHR(0),
3611 argument81 IN varchar2 default CHR(0),
3612 argument82 IN varchar2 default CHR(0),
3613 argument83 IN varchar2 default CHR(0),
3614 argument84 IN varchar2 default CHR(0),
3615 argument85 IN varchar2 default CHR(0),
3616 argument86 IN varchar2 default CHR(0),
3617 argument87 IN varchar2 default CHR(0),
3618 argument88 IN varchar2 default CHR(0),
3619 argument89 IN varchar2 default CHR(0),
3620 argument90 IN varchar2 default CHR(0),
3621 argument91 IN varchar2 default CHR(0),
3622 argument92 IN varchar2 default CHR(0),
3623 argument93 IN varchar2 default CHR(0),
3624 argument94 IN varchar2 default CHR(0),
3625 argument95 IN varchar2 default CHR(0),
3626 argument96 IN varchar2 default CHR(0),
3627 argument97 IN varchar2 default CHR(0),
3628 argument98 IN varchar2 default CHR(0),
3629 argument99 IN varchar2 default CHR(0),
3630 argument100 IN varchar2 default CHR(0))
3631 return number is
3632
3633 reqid number;
3634 func_exists varchar2(1);
3635 srsflag varchar2(1);
3636 langcnt number := 0;
3637
3638 begin
3639 -- If not in database trigger mode:
3640 -- Rollback to start_of_submission if submit() function fails to
3641 -- submit the request.
3642 if (not (P_DB_TRIGGER_MODE)) then
3643 savepoint start_of_submission;
3644 end if;
3645
3646
3647
3648 -- Check to see if this is going to be an MLS request
3649 select decode(nvl(MLS_EXECUTABLE_ID, -1), -1, 'N', 'Y'),
3650 srs_flag
3651 into func_exists, srsflag
3652 from fnd_concurrent_programs FCP, fnd_application FA
3653 where FCP.CONCURRENT_PROGRAM_NAME = UPPER(program)
3654 and FA.APPLICATION_SHORT_NAME = UPPER(application)
3655 and FCP.APPLICATION_ID = FA.APPLICATION_ID;
3656
3657 if P_DEF_REQUEST_ID <> 0 then
3658 select count(*)
3659 into langcnt
3660 from fnd_run_req_languages
3661 where parent_request_id = P_DEF_REQUEST_ID;
3662 end if;
3663
3664 -- If no MLS function and only 1 language then submit normally
3665 -- Also submit normally if an MLS function exists but srs_flag = N
3666 -- Also submit normally if P_REQUEST_TYPE = C (child MLS request)
3667 -- Also submit normally if this is deferred mode and not more than
3668 -- 1 language has been previously requested.
3669 if (P_REQUEST_TYPE = 'C' or
3670 (P_DEF_REQUEST_ID <> 0 and langcnt <= 1) or
3671 (P_DEF_REQUEST_ID = 0 and
3672 (func_exists = 'N' and P_NLS_PARAMETERS_COUNT <= 1) or
3673 (func_exists = 'Y' and srsflag = 'N'))) then
3674 reqid := submit (
3675 application, program, description, start_time, sub_request,
3676 Argument1, Argument2, Argument3, Argument4, Argument5,
3677 Argument6, Argument7, Argument8, Argument9, Argument10,
3678 Argument11, Argument12, Argument13, Argument14, Argument15,
3679 Argument16, Argument17, Argument18, Argument19, Argument20,
3680 Argument21, Argument22, Argument23, Argument24, Argument25,
3681 Argument26, Argument27, Argument28, Argument29, Argument30,
3682 Argument31, Argument32, Argument33, Argument34, Argument35,
3683 Argument36, Argument37, Argument38, Argument39, Argument40,
3684 Argument41, Argument42, Argument43, Argument44, Argument45,
3685 Argument46, Argument47, Argument48, Argument49, Argument50,
3686 Argument51, Argument52, Argument53, Argument54, Argument55,
3687 Argument56, Argument57, Argument58, Argument59, Argument60,
3688 Argument61, Argument62, Argument63, Argument64, Argument65,
3689 Argument66, Argument67, Argument68, Argument69, Argument70,
3690 Argument71, Argument72, Argument73, Argument74, Argument75,
3691 Argument76, Argument77, Argument78, Argument79, Argument80,
3692 Argument81, Argument82, Argument83, Argument84, Argument85,
3693 Argument86, Argument87, Argument88, Argument89, Argument90,
3694 Argument91, Argument92, Argument93, Argument94, Argument95,
3695 Argument96, Argument97, Argument98, Argument99, Argument100);
3696
3697 else
3698
3699 -- Else submit an MLS parent program
3700 reqid := submit_mls_request(
3701 application, program, description, start_time, sub_request,
3702 Argument1, Argument2, Argument3, Argument4, Argument5,
3703 Argument6, Argument7, Argument8, Argument9, Argument10,
3704 Argument11, Argument12, Argument13, Argument14, Argument15,
3705 Argument16, Argument17, Argument18, Argument19, Argument20,
3706 Argument21, Argument22, Argument23, Argument24, Argument25,
3707 Argument26, Argument27, Argument28, Argument29, Argument30,
3708 Argument31, Argument32, Argument33, Argument34, Argument35,
3709 Argument36, Argument37, Argument38, Argument39, Argument40,
3710 Argument41, Argument42, Argument43, Argument44, Argument45,
3711 Argument46, Argument47, Argument48, Argument49, Argument50,
3712 Argument51, Argument52, Argument53, Argument54, Argument55,
3713 Argument56, Argument57, Argument58, Argument59, Argument60,
3714 Argument61, Argument62, Argument63, Argument64, Argument65,
3715 Argument66, Argument67, Argument68, Argument69, Argument70,
3716 Argument71, Argument72, Argument73, Argument74, Argument75,
3717 Argument76, Argument77, Argument78, Argument79, Argument80,
3718 Argument81, Argument82, Argument83, Argument84, Argument85,
3719 Argument86, Argument87, Argument88, Argument89, Argument90,
3720 Argument91, Argument92, Argument93, Argument94, Argument95,
3721 Argument96, Argument97, Argument98, Argument99, Argument100,
3722 func_exists );
3723
3724
3725 end if;
3726
3727 if (not (P_DB_TRIGGER_MODE)) then
3728 if (reqid = 0) then
3729 rollback to start_of_submission;
3730 end if;
3731 end if;
3732
3733 init_pvt_vars;
3734
3735 return (reqid);
3736
3737
3738 exception
3739 when no_data_found then
3740 fnd_message.set_name ('FND', 'CONC-Invalid Conc program');
3741 fnd_message.set_token ('PROGRAM', program, FALSE);
3742 return (0);
3743 when others then
3744 fnd_message.set_name ('FND', 'SQL-Generic error');
3745 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
3746 fnd_message.set_token ('REASON', sqlerrm, FALSE);
3747 fnd_message.set_token ('ROUTINE', 'SUBMIT_REQUEST', FALSE);
3748 return (0);
3749
3750 end submit_request;
3751
3752 --
3753 -- Name
3754 -- submit_svc_ctl_request
3755 -- Purpose
3756 -- Submits concurrent request to be processed by a concurrent manager
3757 --
3758 -- Arguments
3759 -- command - concurrent program name for which the request has
3760 -- - to be submitted: ACTIVATE, DEACTIVATE, RESTART,
3761 -- - ABORT (ie. terminate), VERIFY, SUSPEND, or RESUME
3762 -- service - Service instance or manager developer name.
3763 -- service_app - manager or service instance's application short name
3764
3765 function submit_svc_ctl_request (
3766 command IN varchar2,
3767 service IN varchar2,
3768 service_app IN varchar2)
3769 return number is
3770
3771 mgr_app_id number; -- Arg2
3772 mgr_id number; -- Arg1
3773 my_descr varchar2(2000); -- DESCRIPTION
3774 now date; -- START_TIME
3775 reqid number;
3776 prog_id number;
3777
3778
3779 begin
3780 begin -- Is the program name correct?
3781 Select concurrent_program_id
3782 Into prog_id
3783 From Fnd_concurrent_programs
3784 Where concurrent_program_name = command
3785 and application_id = 0;
3786
3787 exception
3788 when no_data_found then
3789 fnd_message.set_name ('FND', 'CONC-Invalid Conc program');
3790 fnd_message.set_token ('PROGRAM', command, FALSE);
3791 return (0);
3792 when others then
3793 fnd_message.set_name ('FND', 'SQL-Generic error');
3794 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
3795 fnd_message.set_token ('REASON', sqlerrm, FALSE);
3796 fnd_message.set_token (
3797 'ROUTINE', 'submit_svc_ctl_request', FALSE);
3798 return (0);
3799 end; -- Is the program name correct?
3800
3801 begin -- Is the appl short name correct?
3802 Select Application_ID, sysdate
3803 Into mgr_app_id, now
3804 From Fnd_Application
3805 Where Application_Short_Name = upper (service_app);
3806
3807 exception
3808 when no_data_found then
3809 fnd_message.set_name ('FND', 'CONC-Invalid Appl Short Name');
3810 fnd_message.set_token ('APPLNAME', service_app, FALSE);
3811 return (0);
3812 when others then
3813 fnd_message.set_name ('FND', 'SQL-Generic error');
3814 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
3815 fnd_message.set_token ('REASON', sqlerrm, FALSE);
3816 fnd_message.set_token (
3817 'ROUTINE', 'submit_svc_ctl_request', FALSE);
3818 return (0);
3819 end; -- Is the appl short name correct?
3820
3821 begin -- Is the svc inst short name correct?
3822 Select Concurrent_queue_id
3823 Into mgr_id
3824 From Fnd_Concurrent_queues
3825 Where Application_id = mgr_app_id
3826 and Concurrent_queue_name = service;
3827 exception
3828 when no_data_found then
3829 fnd_message.set_name ('FND', 'CONC-Invalid Queue Name');
3830 fnd_message.set_token ('QUEUENAME', service, FALSE);
3831 return (0);
3832 when others then
3833 fnd_message.set_name ('FND', 'SQL-Generic error');
3834 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
3835 fnd_message.set_token ('REASON', sqlerrm, FALSE);
3836 fnd_message.set_token (
3837 'ROUTINE', 'submit_svc_ctl_request', FALSE);
3838 return (0);
3839 end; -- Is the svc inst short name correct?
3840
3841 my_descr := fnd_concurrent.Build_Svc_Ctrl_Desc(
3842 mgr_id, mgr_app_id, null, command);
3843
3844 if (not (P_DB_TRIGGER_MODE)) then
3845 savepoint start_of_qc_submission;
3846 end if;
3847
3848 reqid := submit_request(
3849 'FND', command, my_descr, now, FALSE, mgr_id, mgr_app_id);
3850
3851 if (reqid = 0) then
3852 return(0);
3853 end if;
3854
3855 begin -- Update queue cntrl code
3856 update fnd_concurrent_queues
3857 set CONTROL_CODE = decode(prog_id, 0, 'A', 1, 'D', 2, 'V', 3, 'R',
3858 4, 'T', 5, 'D', 6, 'A', 7, 'O', 8, 'Q', null)
3859 where concurrent_queue_id = mgr_id
3860 and application_id = mgr_app_id;
3861 exception
3862 when others then
3863 if (not (P_DB_TRIGGER_MODE)) then
3864 rollback to start_of_qc_submission;
3865 end if;
3866 fnd_message.set_name ('FND', 'SQL-Generic error');
3867 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
3868 fnd_message.set_token ('REASON', sqlerrm, FALSE);
3869 fnd_message.set_token (
3870 'ROUTINE', 'submit_svc_ctl_request(update)', FALSE);
3871 return (0);
3872 end; -- update cntl code
3873
3874 commit;
3875 return (reqid);
3876 end submit_svc_ctl_request;
3877
3878 function submit_svc_ctl_by_app (
3879 command IN varchar2,
3880 application IN varchar2,
3881 svc_type IN NUMBER default 2)
3882 return number is
3883
3884 mgr_app_id number; -- Arg2
3885 my_descr varchar2(2000); -- DESCRIPTION
3886 now date; -- START_TIME
3887 reqid number;
3888 prog_id number;
3889
3890
3891 begin
3892 begin -- Is the program name correct?
3893 Select concurrent_program_id
3894 Into prog_id
3895 From Fnd_concurrent_programs
3896 Where concurrent_program_name = command
3897 and application_id = 0;
3898
3899 exception
3900 when no_data_found then
3901 fnd_message.set_name ('FND', 'CONC-Invalid Conc program');
3902 fnd_message.set_token ('PROGRAM', command, FALSE);
3903 return (0);
3904 when others then
3905 fnd_message.set_name ('FND', 'SQL-Generic error');
3906 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
3907 fnd_message.set_token ('REASON', sqlerrm, FALSE);
3908 fnd_message.set_token (
3909 'ROUTINE', 'submit_svc_ctl_by_app', FALSE);
3910 return (0);
3911 end; -- Is the program name correct?
3912
3913 begin -- Is the appl short name correct?
3914 Select Application_ID, sysdate
3915 Into mgr_app_id, now
3916 From Fnd_Application
3917 Where Application_Short_Name = upper (application);
3918
3919 exception
3920 when no_data_found then
3921 fnd_message.set_name ('FND', 'CONC-Invalid Appl Short Name');
3922 fnd_message.set_token ('APPLNAME', application, FALSE);
3923 return (0);
3924 when others then
3925 fnd_message.set_name ('FND', 'SQL-Generic error');
3926 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
3927 fnd_message.set_token ('REASON', sqlerrm, FALSE);
3928 fnd_message.set_token (
3929 'ROUTINE', 'submit_svc_ctl__by_app', FALSE);
3930 return (0);
3931 end; -- Is the appl short name correct?
3932
3933 my_descr := fnd_concurrent.Build_Svc_Ctrl_Desc(
3934 -1, mgr_app_id, svc_type, command);
3935
3936 if (not (P_DB_TRIGGER_MODE)) then
3937 savepoint start_of_qc_submission;
3938 end if;
3939
3940 reqid := submit_request(
3941 'FND', command, my_descr, now, FALSE, -1, mgr_app_id, svc_type);
3942
3943 if (reqid = 0) then
3944 return(0);
3945 end if;
3946
3947 begin -- Update queue cntrl code
3948 update fnd_concurrent_queues
3949 set CONTROL_CODE = decode(prog_id, 0, 'A', 1, 'D', 2, 'V', 3, 'R',
3950 4, 'T', 5, 'D', 6, 'A', 7, 'O', 8, 'Q', null)
3951 where
3952 /* either CM or TM and request is for mgrs (or both) */
3953 (((manager_type = '1') or (manager_type = '3'))
3954 and ((svc_type = 0) or (svc_type = 2)))
3955 or /* or service and request is for services (or both) */
3956 ((manager_type > 999) and ((svc_type = 1) or (svc_type = 2)))
3957 and application_id = mgr_app_id;
3958 exception
3959 when others then
3960 if (not (P_DB_TRIGGER_MODE)) then
3961 rollback to start_of_qc_submission;
3962 end if;
3963 fnd_message.set_name ('FND', 'SQL-Generic error');
3964 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
3965 fnd_message.set_token ('REASON', sqlerrm, FALSE);
3966 fnd_message.set_token (
3967 'ROUTINE', 'submit_svc_ctl_by_app(update)', FALSE);
3968 return (0);
3969 end; -- update cntl code
3970
3971 commit;
3972 return (reqid);
3973 end submit_svc_ctl_by_app;
3974
3975 --
3976 -- Name
3977 -- submit_svc_ctl_by_svc
3978 -- Purpose
3979 -- Submits Queue Control Request
3980 -- WARNING PERFORMS A COMMIT!!!
3981 --
3982 -- Arguments
3983 -- command - concurrent program name for which the request has
3984 -- - to be submitted: ACTIVATE, DEACTIVATE, RESTART,
3985 -- - ABORT (ie. terminate), VERIFY, SUSPEND, or RESUME
3986 -- service - Service instance or manager developer name.
3987
3988 function submit_svc_ctl_by_svc (
3989 command IN varchar2,
3990 service IN varchar2)
3991 return number is
3992
3993 svc_id number; -- Arg2
3994 my_descr varchar2(2000); -- DESCRIPTION
3995 now date; -- START_TIME
3996 reqid number;
3997 prog_id number;
3998
3999
4000 begin
4001 begin -- Is the program name correct?
4002 Select concurrent_program_id
4003 Into prog_id
4004 From Fnd_concurrent_programs
4005 Where concurrent_program_name = command
4006 and application_id = 0;
4007
4008 exception
4009 when no_data_found then
4010 fnd_message.set_name ('FND', 'CONC-Invalid Conc program');
4011 fnd_message.set_token ('PROGRAM', command, FALSE);
4012 return (0);
4013 when others then
4014 fnd_message.set_name ('FND', 'SQL-Generic error');
4015 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
4016 fnd_message.set_token ('REASON', sqlerrm, FALSE);
4017 fnd_message.set_token (
4018 'ROUTINE', 'submit_svc_ctl_by_svc', FALSE);
4019 return (0);
4020 end; -- Is the program name correct?
4021
4022 begin -- Is the svc name correct?
4023 Select service_id
4024 Into svc_id
4025 From Fnd_Cp_services
4026 Where service_handle = service;
4027 exception
4028 when no_data_found then
4029 fnd_message.set_name ('FND', 'CONC-SM INVALID SVC HANDLE');
4030 fnd_message.set_token ('HANDLE', service, FALSE);
4031 return (0);
4032 when others then
4033 fnd_message.set_name ('FND', 'SQL-Generic error');
4034 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
4035 fnd_message.set_token ('REASON', sqlerrm, FALSE);
4036 fnd_message.set_token (
4037 'ROUTINE', 'submit_svc_ctl_by_svc', FALSE);
4038 return (0);
4039 end; -- Is the svc name correct?
4040
4041 my_descr := fnd_concurrent.Build_Svc_Ctrl_Desc(-3, svc_id, null, command);
4042
4043 if (not (P_DB_TRIGGER_MODE)) then
4044 savepoint start_of_qc_submission;
4045 end if;
4046
4047 reqid := submit_request('FND', command, my_descr, now, FALSE, -3, svc_id);
4048
4049 if (reqid = 0) then
4050 return(0);
4051 end if;
4052
4053 begin -- Update queue cntrl code
4054 update fnd_concurrent_queues
4055 set CONTROL_CODE = decode(prog_id, 0, 'A', 1, 'D', 2, 'V', 3, 'R',
4056 4, 'T', 5, 'D', 6, 'A', 7, 'O', 8, 'Q', null)
4057 where manager_type = to_char(svc_id);
4058 exception
4059 when others then
4060 if (not (P_DB_TRIGGER_MODE)) then
4061 rollback to start_of_qc_submission;
4062 end if;
4063 fnd_message.set_name ('FND', 'SQL-Generic error');
4064 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
4065 fnd_message.set_token ('REASON', sqlerrm, FALSE);
4066 fnd_message.set_token (
4067 'ROUTINE', 'submit_svc_ctl_by_svc(update)', FALSE);
4068 return (0);
4069 end; -- update cntl code
4070
4071 commit;
4072 return (reqid);
4073 end submit_svc_ctl_by_svc;
4074
4075 --
4076 -- Name
4077 -- submit_svc_ctl_cpinfra
4078 -- Purpose
4079 -- Submits Queue Control Request
4080 -- WARNING PERFORMS A COMMIT!!!
4081 --
4082 -- Arguments
4083 -- command - concurrent program name for which the request has
4084 -- - to be submitted: ACTIVATE, DEACTIVATE, RESTART,
4085 -- - ABORT (ie. terminate), VERIFY, SUSPEND, or RESUME
4086
4087 function submit_svc_ctl_cpinfra (
4088 command IN varchar2)
4089 return number is
4090
4091 my_descr varchar2(2000); -- DESCRIPTION
4092 now date; -- START_TIME
4093 reqid number;
4094 prog_id number;
4095
4096
4097 begin
4098 begin -- Is the program name correct?
4099 Select concurrent_program_id
4100 Into prog_id
4101 From Fnd_concurrent_programs
4102 Where concurrent_program_name = command
4103 and application_id = 0;
4104
4105 exception
4106 when no_data_found then
4107 fnd_message.set_name ('FND', 'CONC-Invalid Conc program');
4108 fnd_message.set_token ('PROGRAM', command, FALSE);
4109 return (0);
4110 when others then
4111 fnd_message.set_name ('FND', 'SQL-Generic error');
4112 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
4113 fnd_message.set_token ('REASON', sqlerrm, FALSE);
4114 fnd_message.set_token (
4115 'ROUTINE', 'submit_svc_ctl_cpinfra', FALSE);
4116 return (0);
4117 end; -- Is the program name correct?
4118
4119 my_descr := fnd_concurrent.Build_Svc_Ctrl_Desc(-2, null, null, command);
4120
4121 if (not (P_DB_TRIGGER_MODE)) then
4122 savepoint start_of_qc_submission;
4123 end if;
4124
4125 reqid := submit_request('FND', command, my_descr, now, FALSE, -2);
4126
4127 if (reqid = 0) then
4128 return(0);
4129 end if;
4130
4131 begin -- Update queue cntrl code
4132 update fnd_concurrent_queues
4133 set CONTROL_CODE = decode(prog_id, 0, 'A', 1, 'D', 2, 'V', 3, 'R',
4134 4, 'T', 5, 'D', 6, 'A', 7, 'O', 8, 'Q', null)
4135 where manager_type in ('1','3','4','5');
4136 exception
4137 when others then
4138 if (not (P_DB_TRIGGER_MODE)) then
4139 rollback to start_of_qc_submission;
4140 end if;
4141 fnd_message.set_name ('FND', 'SQL-Generic error');
4142 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
4143 fnd_message.set_token ('REASON', sqlerrm, FALSE);
4144 fnd_message.set_token (
4145 'ROUTINE', 'submit_svc_ctl_cpinfra(update)', FALSE);
4146 return (0);
4147 end; -- update cntl code
4148
4149 commit;
4150 return (reqid);
4151 end submit_svc_ctl_cpinfra;
4152
4153 --
4154 -- Name
4155 -- submit_svc_ctl_cpall
4156 -- Purpose
4157 -- Submits Queue Control Request
4158 -- WARNING PERFORMS A COMMIT!!!
4159 --
4160 -- Arguments
4161 -- command - concurrent program name for which the request has
4162 -- - to be submitted: ACTIVATE, DEACTIVATE, RESTART,
4163 -- - ABORT (ie. terminate), VERIFY, SUSPEND, or RESUME
4164
4165 function submit_svc_ctl_cpall (
4166 command IN varchar2)
4167 return number is
4168
4169 my_descr varchar2(2000); -- DESCRIPTION
4170 now date; -- START_TIME
4171 reqid number;
4172 prog_id number;
4173
4174
4175 begin
4176 begin -- Is the program name correct?
4177 Select concurrent_program_id
4178 Into prog_id
4179 From Fnd_concurrent_programs
4180 Where concurrent_program_name = command
4181 and application_id = 0;
4182
4183 exception
4184 when no_data_found then
4185 fnd_message.set_name ('FND', 'CONC-Invalid Conc program');
4186 fnd_message.set_token ('PROGRAM', command, FALSE);
4187 return (0);
4188 when others then
4189 fnd_message.set_name ('FND', 'SQL-Generic error');
4190 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
4191 fnd_message.set_token ('REASON', sqlerrm, FALSE);
4192 fnd_message.set_token (
4193 'ROUTINE', 'submit_svc_ctl_cpall', FALSE);
4194 return (0);
4195 end; -- Is the program name correct?
4196
4197 my_descr := fnd_concurrent.Build_Svc_Ctrl_Desc(-4, null, null, command);
4198
4199 if (not (P_DB_TRIGGER_MODE)) then
4200 savepoint start_of_qc_submission;
4201 end if;
4202
4203 reqid := submit_request('FND', command, my_descr, now, FALSE, -4);
4204
4205 if (reqid = 0) then
4206 return(0);
4207 end if;
4208
4209 commit;
4210 return (reqid);
4211 end submit_svc_ctl_cpall;
4212
4213 procedure internal(critical in varchar2 default null,
4214 type in varchar2 default null) is
4215 begin
4216 P_CRITICAL_REQUEST := critical;
4217 P_REQUEST_TYPE := type;
4218 end;
4219
4220 --
4221 -- Name
4222 -- add_notification
4223 -- Purpose
4224 -- Called before submission to add a user to the notify list.
4225 -- Added this new method since changing the add_notification will need
4226 -- generation of all calling forms.
4227 -- In a major release we can merge this one with previous one with default
4228 -- values.
4229 --
4230 -- Arguments
4231 -- User - User name.
4232 -- on_normal - Notify when normal completion (Values Y/N)
4233 -- on_warning - Notify when request completes with warning (Y/N)
4234 -- on_error - Notify when request completed with error (Y/N)
4235 function add_notification (user in varchar2,
4236 on_normal in varchar2,
4237 on_warning in varchar2,
4238 on_error in varchar2)
4239 return boolean is
4240 n_index number;
4241 ret_val boolean;
4242 begin
4243
4244 ret_val := add_notification(user);
4245 if ( ret_val ) then
4246
4247 n_index := P_NOTIFICATION_COUNT;
4248
4249 P_NOTIFICATIONS(n_index).on_normal := add_notification.on_normal;
4250 P_NOTIFICATIONS(n_index).on_warning := add_notification.on_warning;
4251 P_NOTIFICATIONS(n_index).on_error := add_notification.on_error;
4252
4253 return TRUE;
4254 else
4255 return FALSE;
4256 end if;
4257
4258 end;
4259
4260
4261 -- Name
4262 -- set_deferred
4263 -- Purpose
4264 -- Called before calling request submission page by other product teams
4265 --
4266 -- Arguments
4267 --
4268 --
4269 function set_deferred return boolean is
4270 begin
4271 P_DEFERRED_MODE := TRUE;
4272 return TRUE;
4273 end;
4274
4275
4276 -- Name
4277 -- set_def_request_id
4278 -- Purpose
4279 -- Called for setting global request id and the request id will be used while creating actual request
4280 --
4281 -- Arguments
4282 -- request_id - used while creating actual request in fnd_concurrent_request
4283 --
4284 function set_def_request_id (request_id IN number)
4285 return boolean is
4286 begin
4287 P_DEF_REQUEST_ID := request_id;
4288 return TRUE;
4289 end;
4290
4291 -- Name
4292 -- get_fnd_debug_rules_sequence
4293 -- Purpose
4294 -- Called to obtain the next sequence value for fnd_debug_rules table
4295 --
4296 -- Arguments
4297 -- none
4298 --
4299 function get_fnd_debug_rules_sequence return number is
4300
4301 l_sequence number;
4302 dual_no_rows exception;
4303 dual_too_many_rows exception;
4304
4305 begin
4306
4307 begin
4308 select fnd_debug_rules_s.nextval
4309 into l_sequence
4310 from sys.dual;
4311
4312 exception
4313 when no_data_found then
4314 raise dual_no_rows;
4315 when too_many_rows then
4316 raise dual_too_many_rows;
4317 when others then
4318 raise;
4319 end;
4320
4321 return l_sequence;
4322 end;
4323
4324
4325 -- Name
4326 -- update_fnd_debug_rules_req_id
4327 -- Purpose
4328 -- Called to set the request id for the specific fnd_debug_rule_id
4329 --
4330 -- Arguments
4331 -- request_id - the actual request id that debug options is being set for.
4332 -- fnd_debug_rule_id - the specific row in fnd_debug_rules to get updated
4333 --
4334 function update_fnd_debug_rules_req_id (
4335 req_id IN number,
4336 fnd_debug_rule_id IN number )
4337 return boolean is
4338 begin
4339
4340 begin
4341 update fnd_debug_rules
4342 set request_id = req_id
4343 where debug_rule_id = fnd_debug_rule_id;
4344
4345 exception
4346 when others then
4347 fnd_message.set_name ('FND', 'SQL-Generic error');
4348 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
4349 fnd_message.set_token ('REASON', sqlerrm, FALSE);
4350 fnd_message.set_token (
4351 'ROUTINE', 'update_fnd_debug_rules_req_id', FALSE);
4352 return FALSE;
4353 end;
4354
4355 commit;
4356 return TRUE;
4357 end;
4358
4359 -- Name
4360 -- delete_fnd_debug_rules_id
4361 -- Purpose
4362 -- Called to delete the specific fnd_debug_rule_id from fnd_debug_rules
4363 -- and fnd_debug_rules_options
4364 --
4365 -- Arguments
4366 -- fnd_debug_rule_id - the specific sequence number to be deleted
4367 --
4368 function delete_fnd_debug_rules_id (
4369 fnd_debug_rule_id IN number )
4370 return boolean is
4371 l_count_rows number;
4372 begin
4373 select count(*)
4374 into l_count_rows
4375 from fnd_debug_rules
4376 where debug_rule_id = fnd_debug_rule_id;
4377
4378 if (l_count_rows > 0) then
4379 delete from fnd_debug_rules
4380 where debug_rule_id = fnd_debug_rule_id;
4381 end if;
4382
4383 select count(*)
4384 into l_count_rows
4385 from fnd_debug_rule_options
4386 where debug_rule_id = fnd_debug_rule_id;
4387
4388 if (l_count_rows > 0) then
4389 delete from fnd_debug_rule_options
4390 where debug_rule_id = fnd_debug_rule_id;
4391 end if;
4392
4393 commit;
4394 return TRUE;
4395
4396 end;
4397
4398 -- NAME
4399 -- get_num_char_for_terr
4400 -- Purpose
4401 -- Called to retrieve the num char associated with a territory
4402 --
4403 -- Arguments
4404 -- original_terr
4405 -- check_terr
4406 --
4407 function get_num_char_for_terr (
4408 original_terr IN varchar2,
4409 check_terr IN varchar2 )
4410 return varchar2 is
4411 cursor cparam (param_name in varchar2) is
4412 select value
4413 from V$NLS_PARAMETERS
4414 where parameter = param_name;
4415
4416 l_num_char varchar2(3);
4417 begin
4418
4419 dbms_session.set_nls('NLS_TERRITORY', ''''||UPPER(check_terr)||'''');
4420
4421 open cparam('NLS_NUMERIC_CHARACTERS');
4422 fetch cparam into l_num_char;
4423 close cparam;
4424
4425 dbms_session.set_nls('NLS_TERRITORY', ''''||UPPER(original_terr)||'''');
4426
4427 return (l_num_char);
4428
4429 end;
4430
4431 -- NAME
4432 -- validate_num_char_for_terr
4433 -- Purpose
4434 -- Called to validate the num char associated with a territory
4435 --
4436 -- Arguments
4437 -- num_char
4438 --
4439 function validate_num_char_for_terr (
4440 num_char IN varchar2 )
4441 return boolean is
4442 l_lookup_cnt number;
4443 begin
4444
4445 select count(*)
4446 into l_lookup_cnt
4447 from fnd_lookup_values_vl
4448 where lookup_type = 'ICX_NUMERIC_CHARACTERS'
4449 and lookup_code = num_char;
4450
4451 if ( l_lookup_cnt > 0 ) then
4452 return TRUE;
4453 else
4454 return FALSE;
4455 end if;
4456 end;
4457 FUNCTION INSERT_USER_SCHEDULE(P_RELEASE_CLASS_NAME IN VARCHAR2,
4458 P_REQUESTED_START_DATE IN VARCHAR2,
4459 P_REQUESTED_END_DATE IN VARCHAR2,
4460 P_REPEAT_INTERVAL IN NUMBER,
4461 P_REPEAT_INTERVAL_UNIT in VARCHAR2,
4462 P_REPEAT_INTERVAL_TYPE IN VARCHAR2,
4463 P_CLASS_TYPE IN VARCHAR2,
4464 P_CLASS_INFO IN VARCHAR2,
4465 P_DESCRIPTION IN VARCHAR2,
4466 P_ENABLED_FLAG IN VARCHAR2
4467 ) RETURN BOOLEAN IS
4468
4469
4470 P_REPEAT_VALUE VARCHAR2(10);
4471 RELCLASS_INSERT_ERROR EXCEPTION;
4472 P_RELEASE_CLASS_ID NUMBER;
4473 P_RELEASE_CLASS_APP_ID NUMBER;
4474 P_REPEAT_START_DATE DATE := null;
4475 P_REPEAT_END_DATE DATE := null;
4476
4477 BEGIN
4478
4479 if(P_REQUESTED_START_DATE is null) then
4480 P_REPEAT_START_DATE := null;
4481 else
4482 P_REPEAT_START_DATE := FND_CONC_DATE.STRING_TO_DATE(P_REQUESTED_START_DATE);
4483 end if;
4484
4485 if(P_REQUESTED_END_DATE is null) then
4486 P_REPEAT_END_DATE := null;
4487 else
4488 P_REPEAT_END_DATE := FND_CONC_DATE.STRING_TO_DATE(P_REQUESTED_END_DATE);
4489 end if;
4490
4491 Select FND_CONC_RELEASE_CLASSES_S.nextval, 0
4492 into P_RELEASE_CLASS_ID, P_RELEASE_CLASS_APP_ID from Sys.dual;
4493
4494 SELECT (DECODE(P_CLASS_TYPE, 'P', Decode(P_REPEAT_INTERVAL_UNIT, NULL, NULL,
4495 P_REPEAT_INTERVAL||':'||
4496 Decode(P_REPEAT_INTERVAL_UNIT,
4497 'MINUTES', 'N',
4498 'MONTHS', 'M',
4499 'HOURS', 'H',
4500 'DAYS', 'D') || ':' ||
4501 Decode(P_REPEAT_INTERVAL_TYPE,
4502 'START', 'S', 'C')), NULL)) INTO P_REPEAT_VALUE FROM SYS.DUAL;
4503
4504 -- Bug 6808926. Consider repeat start date as SYSDATE if null
4505
4506 INSERT INTO FND_CONC_RELEASE_CLASSES
4507 (APPLICATION_ID, RELEASE_CLASS_ID, RELEASE_CLASS_NAME,
4508 ENABLED_FLAG, LAST_UPDATE_DATE, LAST_UPDATED_BY,
4509 LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY,
4510 UPDATED_FLAG, DATE1, DATE2,
4511 CLASS_TYPE, CLASS_INFO)
4512 VALUES (P_RELEASE_CLASS_APP_ID, P_RELEASE_CLASS_ID, P_RELEASE_CLASS_NAME,
4513 P_ENABLED_FLAG, SYSDATE, FND_GLOBAL.USER_ID,
4514 FND_GLOBAL.CONC_LOGIN_ID, SYSDATE, FND_GLOBAL.USER_ID,
4515 'Y', decode(P_REPEAT_START_DATE, null, SYSDATE, P_REPEAT_START_DATE), P_REPEAT_END_DATE,
4516 P_CLASS_TYPE, DECODE(P_CLASS_TYPE, 'P', P_REPEAT_VALUE, P_CLASS_INFO));
4517
4518 if (sql%rowcount = 0) then
4519 raise RELCLASS_INSERT_ERROR;
4520 end if;
4521
4522 INSERT INTO FND_CONC_RELEASE_CLASSES_TL
4523 (APPLICATION_ID, RELEASE_CLASS_ID,
4524 LANGUAGE, SOURCE_LANG, USER_RELEASE_CLASS_NAME,
4525 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
4526 CREATION_DATE, CREATED_BY, DESCRIPTION)
4527 SELECT P_RELEASE_CLASS_APP_ID, P_RELEASE_CLASS_ID,
4528 L.LANGUAGE_CODE, USERENV('LANG'), P_RELEASE_CLASS_NAME,
4529 SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID,
4530 SYSDATE, FND_GLOBAL.USER_ID, P_DESCRIPTION
4531 FROM FND_LANGUAGES L
4532 WHERE L.INSTALLED_FLAG in ('I', 'B');
4533
4534 if (sql%rowcount = 0) then
4535 raise RELCLASS_INSERT_ERROR;
4536 end if;
4537 commit;
4538 return (TRUE);
4539
4540 EXCEPTION
4541
4542 when RELCLASS_INSERT_ERROR then
4543 fnd_message.set_name ('FND', 'CONC-RelClass insert failed');
4544 fnd_message.set_token ('APPLICATION', P_REL_CLASS_APP_ID, FALSE);
4545 fnd_message.set_token ('CLASS', P_REL_CLASS_ID, FALSE);
4546 return (FALSE);
4547
4548 when OTHERS then
4549 fnd_message.set_name ('FND', 'SQL-Generic error');
4550 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
4551 fnd_message.set_token ('REASON', sqlerrm, FALSE);
4552 fnd_message.set_token (
4553 'ROUTINE', 'SUBMIT: others', FALSE);
4554 return (FALSE);
4555 END;
4556
4557 FUNCTION UPDATE_USER_SCHEDULE(P_RELEASE_CLASS_NAME IN VARCHAR2,
4558 P_REQUESTED_START_DATE IN VARCHAR2,
4559 P_REQUESTED_END_DATE IN VARCHAR2,
4560 P_REPEAT_INTERVAL IN NUMBER,
4561 P_REPEAT_INTERVAL_UNIT in VARCHAR2,
4562 P_REPEAT_INTERVAL_TYPE IN VARCHAR2,
4563 P_CLASS_TYPE IN VARCHAR2,
4564 P_CLASS_INFO IN VARCHAR2,
4565 P_DESCRIPTION IN VARCHAR2,
4566 P_ENABLED_FLAG IN VARCHAR2
4567 ) RETURN BOOLEAN IS
4568
4569
4570 P_REPEAT_VALUE VARCHAR2(10);
4571 RELCLASS_INSERT_ERROR EXCEPTION;
4572 P_RELEASE_CLASS_ID NUMBER;
4573 P_RELEASE_CLASS_APP_ID NUMBER;
4574 P_REPEAT_START_DATE DATE := null;
4575 P_REPEAT_END_DATE DATE := null;
4576
4577 BEGIN
4578
4579 if(P_REQUESTED_START_DATE is null) then
4580 P_REPEAT_START_DATE := null;
4581 else
4582 P_REPEAT_START_DATE := FND_CONC_DATE.STRING_TO_DATE(P_REQUESTED_START_DATE);
4583 end if;
4584
4585 if(P_REQUESTED_END_DATE is null) then
4586 P_REPEAT_END_DATE := null;
4587 else
4588 P_REPEAT_END_DATE := FND_CONC_DATE.STRING_TO_DATE(P_REQUESTED_END_DATE);
4589 end if;
4590
4591 begin
4592 select rc.application_id, rc.release_class_id into P_RELEASE_CLASS_APP_ID, P_RELEASE_CLASS_ID
4593 from fnd_conc_release_classes_vl rc, fnd_application_vl a
4594 where rc.owner_req_id is null
4595 and rc.application_id = a.application_id
4596 and rc.user_release_class_name = P_RELEASE_CLASS_NAME;
4597 exception
4598 when no_data_found then
4599 return false;
4600 end;
4601
4602 SELECT (DECODE(P_CLASS_TYPE, 'P', Decode(P_REPEAT_INTERVAL_UNIT, NULL, NULL,
4603 P_REPEAT_INTERVAL||':'||
4604 Decode(P_REPEAT_INTERVAL_UNIT,
4605 'MINUTES', 'N',
4606 'MONTHS', 'M',
4607 'HOURS', 'H',
4608 'DAYS', 'D') || ':' ||
4609 Decode(P_REPEAT_INTERVAL_TYPE,
4610 'START', 'S', 'C')), NULL)) INTO P_REPEAT_VALUE FROM SYS.DUAL;
4611
4612 UPDATE FND_CONC_RELEASE_CLASSES
4613 set LAST_UPDATE_DATE = SYSDATE,
4614 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
4615 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
4616 DATE1 = decode(P_REPEAT_START_DATE, null, SYSDATE, P_REPEAT_START_DATE),
4617 DATE2 = P_REPEAT_END_DATE,
4618 CLASS_TYPE = P_CLASS_TYPE,
4619 CLASS_INFO = DECODE(P_CLASS_TYPE, 'P', P_REPEAT_VALUE, P_CLASS_INFO),
4620 ENABLED_FLAG = P_ENABLED_FLAG
4621 where APPLICATION_ID = P_RELEASE_CLASS_APP_ID
4622 and release_class_id = P_RELEASE_CLASS_ID;
4623
4624 commit;
4625 return (TRUE);
4626
4627 EXCEPTION
4628 when OTHERS then
4629 fnd_message.set_name ('FND', 'SQL-Generic error');
4630 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
4631 fnd_message.set_token ('REASON', sqlerrm, FALSE);
4632 fnd_message.set_token (
4633 'ROUTINE', 'SUBMIT: others', FALSE);
4634 return (FALSE);
4635 END;
4636
4637 function delete_user_schedule(schName varchar2)
4638 return boolean is
4639 P_RELEASE_CLASS_ID NUMBER;
4640 P_RELEASE_CLASS_APP_ID NUMBER;
4641 begin
4642
4643 begin
4644 select rc.application_id, rc.release_class_id into P_RELEASE_CLASS_APP_ID, P_RELEASE_CLASS_ID
4645 from fnd_conc_release_classes_vl rc, fnd_application_vl a
4646 where rc.owner_req_id is null
4647 and rc.application_id = a.application_id
4648 and rc.user_release_class_name = schName;
4649 exception
4650 when no_data_found then
4651 return false;
4652 end;
4653
4654 delete from FND_CONC_RELEASE_CLASSES
4655 where APPLICATION_ID = P_RELEASE_CLASS_APP_ID
4656 and release_class_id = P_RELEASE_CLASS_ID;
4657
4658 delete from FND_CONC_RELEASE_CLASSES_TL
4659 where APPLICATION_ID = P_RELEASE_CLASS_APP_ID
4660 and release_class_id = P_RELEASE_CLASS_ID;
4661
4662 commit;
4663 return (TRUE);
4664
4665 EXCEPTION
4666 when OTHERS then
4667 fnd_message.set_name ('FND', 'SQL-Generic error');
4668 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
4669 fnd_message.set_token ('REASON', sqlerrm, FALSE);
4670 fnd_message.set_token (
4671 'ROUTINE', 'SUBMIT: others', FALSE);
4672 return (FALSE);
4673 end;
4674
4675
4676
4677
4678 --
4679 -- Name
4680 -- add_language
4681 -- Purpose
4682 -- Called before submission to submit a request in a particular language
4683 -- Can be called multiple times to submit a request in multiple languages
4684 --
4685 -- Arguments
4686 -- Lang - Language
4687 -- Territory - Territory
4688 -- Num_char - Numeric characters
4689 --
4690 function add_language (
4691 lang IN VARCHAR2,
4692 territory IN VARCHAR2,
4693 num_char IN VARCHAR2) return boolean is
4694
4695 begin
4696
4697 P_NLS_PARAMETERS_COUNT := P_NLS_PARAMETERS_COUNT + 1;
4698
4699 P_NLS_PARAMETERS(P_NLS_PARAMETERS_COUNT).nls_lang := lang;
4700 P_NLS_PARAMETERS(P_NLS_PARAMETERS_COUNT).nls_territory := territory;
4701 P_NLS_PARAMETERS(P_NLS_PARAMETERS_COUNT).nls_num_char := num_char;
4702
4703 return (TRUE);
4704
4705 exception
4706 when others then
4707 fnd_message.set_name ('FND', 'GLI-Not found');
4708 return(FALSE);
4709
4710 end add_language;
4711
4712
4713
4714 --
4715 -- Name
4716 -- add_delivery_option
4717 -- Purpose
4718 -- Called before submission to add a delivery option
4719 --
4720 -- Arguments
4721 -- Type - Delivery type, see FND_DELIVERY
4722 -- p_argument1 - p_argument9 - Options specific to the delivery type
4723 -- nls_language - Add only for this language
4724 --
4725 function add_delivery_option ( type in varchar2,
4726 p_argument1 in varchar2 default null,
4727 p_argument2 in varchar2 default null,
4728 p_argument3 in varchar2 default null,
4729 p_argument4 in varchar2 default null,
4730 p_argument5 in varchar2 default null,
4731 p_argument6 in varchar2 default null,
4732 p_argument7 in varchar2 default null,
4733 p_argument8 in varchar2 default null,
4734 p_argument9 in varchar2 default null,
4735 nls_language in varchar2 default null) return boolean is
4736
4737 begin
4738
4739 P_DELIV_OPTS_COUNT := P_DELIV_OPTS_COUNT + 1;
4740
4741 P_DELIVERY_OPTIONS(P_DELIV_OPTS_COUNT).argument1 := type;
4742 P_DELIVERY_OPTIONS(P_DELIV_OPTS_COUNT).argument2 := p_argument1;
4743 P_DELIVERY_OPTIONS(P_DELIV_OPTS_COUNT).argument3 := p_argument2;
4744 P_DELIVERY_OPTIONS(P_DELIV_OPTS_COUNT).argument4 := p_argument3;
4745 P_DELIVERY_OPTIONS(P_DELIV_OPTS_COUNT).argument5 := p_argument4;
4746 P_DELIVERY_OPTIONS(P_DELIV_OPTS_COUNT).argument6 := p_argument5;
4747 P_DELIVERY_OPTIONS(P_DELIV_OPTS_COUNT).argument7 := p_argument6;
4748 P_DELIVERY_OPTIONS(P_DELIV_OPTS_COUNT).argument8 := p_argument7;
4749 P_DELIVERY_OPTIONS(P_DELIV_OPTS_COUNT).argument9 := p_argument8;
4750 P_DELIVERY_OPTIONS(P_DELIV_OPTS_COUNT).argument10 := p_argument9;
4751 P_DELIVERY_OPTIONS(P_DELIV_OPTS_COUNT).lang := nls_language;
4752
4753 return (TRUE);
4754
4755 end add_delivery_option;
4756
4757
4758 end FND_REQUEST;