1 package body FND_SUBMIT as
2 /* $Header: AFCPRSSB.pls 120.6 2008/01/15 11:29:29 ggupta ship $ */
3
4 --
5 -- Package
6 -- FND_SUBMIT
7 --
8 -- Purpose
9 -- Concurrent processing related utilities
10 --
11
12 --
13 -- PRIVATE VARIABLES
14 --
15 P_OPS_ID integer := null;
16 P_PRINT_STYLE varchar2(30) := null;
17 P_SAVE_OUTPUT char := null;
18 P_PRINT_TOGETHER char := null;
19 P_LANGUAGE varchar2(30) := null;
20 P_TERRITORY varchar2(30) := null;
21 P_DATAGROUP varchar2(30) := null;
22 P_DB_TRIGGER_MODE boolean := FALSE;
23 P_CRITICAL_REQUEST varchar2(1) := null;
24 P_REQUEST_TYPE varchar2(1) := NULL;
25 P_SET_APP_ID integer := NULL;
26 P_SET_ID integer := NULL;
27 P_PHASE_CODE varchar2(1) := NULL;
28 P_STATUS_CODE varchar2(1) := NULL;
29 P_RS_REQUEST_ID integer := 0;
30 P_TEMPLATE_APPL varchar2(30) := NULL;
31 P_TEMPLATE_CODE varchar2(80) := NULL;
32 P_TEMPLATE_LANG varchar2(6) := NULL;
33 P_TEMPLATE_TERR varchar2(6) := NULL;
34 P_OUTPUT_FORMAT varchar2(30) := NULL;
35 P_ORG_ID integer := null;
36 P_NUMERIC_CHARACTERS varchar2(2) := NULL;
37
38
39 TYPE printer_record_type is record
40 (printer varchar2(30),
41 copies number);
42
43 TYPE printer_tab_type is table of printer_record_type
44 index by binary_integer;
45
46 -- 12.1 Project Changes: Added orig_system and orig_system_id
47 TYPE notification_record_type is record
48 (name varchar2(100),
49 orig_system varchar2(48),
50 orig_system_id number,
51 on_normal varchar2(1),
52 on_warning varchar2(1),
53 on_error varchar2(1));
54
55 TYPE notification_tab_type is table of notification_record_type
56 index by binary_integer;
57
58 -- bug 1679626 (ckclark): When there is more than one occurance
59 -- of a specific program within the stage, need to check the
60 -- RSP.sequence within the stage to avoid ORA-1422
61
62 TYPE rs_program_record_type is record
63 (stage varchar2(30),
64 program varchar2(30),
65 seq number(15),
66 flag boolean);
67
68 TYPE rs_program_tab_type is table of rs_program_record_type
69 index by binary_integer;
70
71 P_PRINTERS printer_tab_type;
72 P_PRINTER_COUNT number := 0;
73 P_NOTIFICATIONS notification_tab_type;
74 P_NOTIFICATION_COUNT number := 0;
75 P_SET_PROGRAMS rs_program_tab_type;
76 P_SET_PROGRAM_COUNT number := 0;
77
78 -- Exception info.
79
80 --
81 -- PRIVATE FUNCTIONS
82 --
83 -- --
84
85 -- Name
86 -- init_pvt_vars
87 -- Purpose
88 -- Called after submitting request to re-initialize repeat options
89 --
90 -- --
91
92 procedure init_pvt_vars( roll_back boolean default FALSE ) is
93 empty_array printer_tab_type;
94 empty_notify_array notification_tab_type;
95 empty_rs_array rs_program_tab_type;
96 begin
97 -- if not db_trigger_mode and roll_back
98 -- and the set_request_set program called then rollback to
99 -- start_of_submission
100
101 if(( not (P_DB_TRIGGER_MODE)) and roll_back and
102 not ( P_SET_APP_ID is null or P_SET_ID is null) ) then
103 rollback to start_of_submission;
104 end if;
105
106 P_OPS_ID := null;
107 P_PRINT_STYLE := null;
108 P_SAVE_OUTPUT := null;
109 P_PRINT_TOGETHER := null;
110 P_LANGUAGE := null;
111 P_TERRITORY := null;
112 P_DATAGROUP := null;
113 P_DB_TRIGGER_MODE := FALSE;
114 P_CRITICAL_REQUEST := null;
115 P_REQUEST_TYPE := NULL;
116 P_PRINTERS := empty_array;
117 P_PRINTER_COUNT := 0;
118 P_NOTIFICATIONS := empty_notify_array;
119 P_NOTIFICATION_COUNT := 0;
120 P_SET_APP_ID := null;
121 P_SET_ID := null;
122 P_SET_PROGRAMS := empty_rs_array;
123 P_SET_PROGRAM_COUNT := 0;
124 P_PHASE_CODE := null;
125 P_STATUS_CODE := null;
126 P_RS_REQUEST_ID := 0;
127 P_TEMPLATE_APPL := NULL;
128 P_TEMPLATE_CODE := NULL;
129 P_TEMPLATE_LANG := NULL;
130 P_TEMPLATE_TERR := NULL;
131 P_OUTPUT_FORMAT := NULL;
132 P_ORG_ID := NULL;
133
134 end init_pvt_vars;
135
136
137 -- Name
138 -- init_prog_pvt_vars
139 -- Purpose
140 -- Called after submitting program to re-initialize print options
141 --
142 -- --
143
144 procedure init_prog_pvt_vars is
145 empty_array printer_tab_type;
146 empty_notify_array notification_tab_type;
147 empty_rs_array rs_program_tab_type;
148 begin
149 P_PRINT_STYLE := null;
150 P_SAVE_OUTPUT := null;
151 P_PRINT_TOGETHER := null;
152 P_LANGUAGE := null;
153 P_TERRITORY := null;
154 P_REQUEST_TYPE := NULL;
155 P_PRINTERS := empty_array;
156 P_PRINTER_COUNT := 0;
157 P_NOTIFICATIONS := empty_notify_array;
158 P_NOTIFICATION_COUNT := 0;
159 P_TEMPLATE_APPL := NULL;
160 P_TEMPLATE_CODE := NULL;
161 P_TEMPLATE_LANG := NULL;
162 P_TEMPLATE_TERR := NULL;
163 P_OUTPUT_FORMAT := NULL;
164 P_ORG_ID := NULL;
165
166 end init_prog_pvt_vars;
167
168
169 -- --
170 -- Name
171 -- set_request_set
172 -- Purpose
173 -- To set the request set context. Call this function at very
174 -- beginning of the submission of a concurrent request set.
175 -- It returns TRUE on sucessful completion, and FALSE otherwise.
176 -- --
177
178 function set_request_set (
179 application IN varchar2,
180 request_set IN varchar2
181 ) return boolean is
182 incompatibilities_allowed varchar2(1);
183 print_together varchar2(1);
184 rs_printer varchar2(30);
185 rs_print_style varchar2(30);
186 rs_application_id Fnd_Request_Sets.Application_id%TYPE;
187 rs_id Fnd_Request_Sets.Request_Set_Id%TYPE;
188 rs_owner Fnd_Request_Sets.Owner%TYPE;
189 rs_conc_program varchar2(30);
190 par_request_id number;
191 sub_request boolean := FALSE;
192 success_failure boolean;
193 user_rs_name varchar2(240);
194 i number;
195 rs_submission_program varchar2(30);
196 rs_program_application varchar2(50);
197 invalid_program boolean;
198
199 -- bug 1679626 (ckclark): When there is more than one occurance
200 -- of a specific program within the stage, need to check the
201 -- RSP.sequence within the stage to avoid ORA-1422
202
203 cursor set_programs( set_app_id number, set_id number) is
204 select CP.concurrent_program_name, CP.enabled_flag, RSS.stage_name,
205 RSP.sequence
206 from fnd_request_sets RS, fnd_request_set_stages RSS,
207 fnd_concurrent_programs CP, fnd_request_set_programs RSP
208 where RS.application_id = set_app_id
209 and RS.request_set_id = set_id
210 and RSS.set_application_id = RS.application_id
211 and RSS.request_set_id = RS.request_set_id
212 and RSP.set_application_id = RSS.set_application_id
213 and RSP.request_set_id = RSS.request_set_id
214 and RSP.request_set_stage_id = RSS.request_set_stage_id
215 and RSP.program_application_id = CP.application_id(+)
216 and RSP.concurrent_program_id = CP.concurrent_program_id(+)
217 order by RSP.sequence;
218
219 crec set_programs%ROWTYPE;
220
221 set_not_found exception;
222 sql_generic_error exception;
223 set_print_option_failed exception;
224 fndrssub_failed exception;
225 programs_not_available exception;
226 request_not_found exception;
227 update_failed exception;
228 invalid_program_in_set exception;
229
230 begin
231 -- If not in database trigger mode;
232 -- Rollback to start_of_submission if any of the functions fails
233 --
234 if ( not (P_DB_TRIGGER_MODE) ) then
235 savepoint start_of_submission;
236 end if;
237
238 -- Get info. about requests origination
239 -- if being submitted from another concurrent request get parent
240 -- request information.
241
242 par_request_id := FND_GLOBAL.conc_request_id;
243
244 -- Bug - 1162507
245 -- The fact that a concurrent request is submitting
246 -- request set using the API's does not implicitly make the request
247 -- that is being submitted a "sub request"
248 -- Caller of the API's need to explicitly indicate that it plans
249 -- to manage the request(set) being submitted as a sub request
250 -- Commenting the following code ...
251
252 -- if ( to_number(par_request_id) > 0 ) then
253 -- sub_request := TRUE;
254 -- else
255 -- sub_request := FALSE;
256 -- end if;
257
258 -- Get Request set info
259
260 begin
261 select RS.application_id, RS.request_set_id,
262 allow_constraints_flag, RS.print_together_flag,
263 RS.owner, RS.printer, RS.print_style,
264 CP.Concurrent_Program_Name, RS.User_Request_Set_Name
265 into P_SET_APP_ID, P_SET_ID,
266 incompatibilities_allowed, print_together,
267 rs_owner, rs_printer, rs_print_style,
268 rs_conc_program, user_rs_name
269 from Fnd_Request_Sets_Vl RS, Fnd_Application A,
270 Fnd_Concurrent_Programs CP
271 where
272 RS.Application_id = A.Application_id
273 And RS.Request_Set_Name = upper(request_set)
274 And A.Application_Short_Name = upper(application)
275 And RS.Start_Date_Active <= sysdate
276 And nvl(RS.End_Date_Active,sysdate) >= sysdate
277 And RS.Concurrent_Program_Id = CP.Concurrent_Program_Id(+)
278 And RS.Application_Id = CP.Application_Id(+);
279
280 exception
281 when no_data_found then
282 raise set_not_found;
283
284 when others then
285 raise sql_generic_error;
286 end;
287
288 -- Populate the P_SET_PROGRAMS with all the programs that are available
289 -- in the given set.
290
291 i := 0;
292 invalid_program := FALSE;
293
294 for crec in set_programs( P_SET_APP_ID, P_SET_ID ) loop
295 -- check program exists in fnd_concurrent_programs or not
296 -- check program enabled or not
297 -- Bug 5680619
298 /*if( crec.concurrent_program_name is null
299 or crec.enabled_flag = 'N') then
300 invalid_program := TRUE;
301 end if;*/
302 if( crec.concurrent_program_name is null) then
303 invalid_program := TRUE;
304 end if;
305
306 if (crec.enabled_flag <> 'N') then
307 P_SET_PROGRAMS(i).program := crec.concurrent_program_name;
308 P_SET_PROGRAMS(i).stage := crec.stage_name;
309 P_SET_PROGRAMS(i).seq := crec.sequence;
310 P_SET_PROGRAMS(i).flag := FALSE;
311 i := i + 1;
312 end if;
313 end loop;
314
315 if ( invalid_program ) then
316 raise invalid_program_in_set;
317 end if;
318
319 P_SET_PROGRAM_COUNT := i;
320
321 if ( i = 0 ) then
322 raise programs_not_available;
323 end if;
324
325
326 -- Set the Print options for the FNDRSSUB concurrent program
327
328 success_failure := fnd_request.set_print_options (
329 rs_printer,
330 rs_print_style,
331 0,
332 NULL,
333 print_together);
334
335 -- if set_print_options failes then just return
336 -- it is up to the caller to retrive the error message set by
337 -- set_print_options
338
339 if ( not success_failure ) then
340 raise set_print_option_failed;
341 end if;
342
343 -- Set the request set type before submitting the FNDRSSUB request
344
345 fnd_request.internal( NULL, 'M' );
346
347 if ( nvl(incompatibilities_allowed,'N') = 'Y' ) then
348 rs_submission_program := rs_conc_program;
349 rs_program_application := application;
350 else
351 rs_submission_program := 'FNDRSSUB';
352 rs_program_application := 'FND';
353 end if;
354
355 -- Submit concurrent request for request set
356
357 P_RS_REQUEST_ID := fnd_request.submit_request(
358 rs_program_application,
359 rs_submission_program,
360 user_rs_name,
361 NULL,
362 sub_request,
363 P_SET_APP_ID,
364 P_SET_ID,
365 chr(0),
366 '','','','','','','',
367 '','','','','','','','','','',
368 '','','','','','','','','','',
369 '','','','','','','','','','',
370 '','','','','','','','','','',
371 '','','','','','','','','','',
372 '','','','','','','','','','',
373 '','','','','','','','','','',
374 '','','','','','','','','','',
375 '','','','','','','','','','');
376
377 if ( P_RS_REQUEST_ID = 0 OR P_RS_REQUEST_ID is NULL) then
378 raise fndrssub_failed;
379 end if;
380
381
382 -- Before returning update the P_RS_REQUEST_IDs phase and status to
383 -- completed with error, we will updated them back to original values
384 -- in submit_set function.
385 -- If we don't do this then in some cases the set_request_set will
386 -- submit the request and if the later calls (submit_program,submit_set..)
387 -- fails then the transaction will be in inconsistance state.
388
389 begin
390 select phase_code, status_code
391 into P_PHASE_CODE, P_STATUS_CODE
392 from fnd_concurrent_requests
393 where request_id = P_RS_REQUEST_ID;
394
395 exception
396 when no_data_found then
397 raise request_not_found;
398 when others then
399 raise sql_generic_error;
400 end;
401
402 -- Update the request phase_code and status_code to completed with error
403
404 update fnd_concurrent_requests
405 set phase_code = 'C', status_code = 'E',
406 completion_text =
407 'Errored during request submission using request see APIs '
408 where request_id = P_RS_REQUEST_ID;
409
410 if (sql%rowcount = 0 ) then
411 raise update_failed;
412 end if;
413
414 return( TRUE );
415
416 exception
417 when set_not_found then
418 fnd_message.set_name('FND', 'CONC-Request Set Not Found');
419 fnd_message.set_token('REQUEST_SET', request_set, FALSE);
420 init_pvt_vars (TRUE);
421 return( FALSE );
422
423 when sql_generic_error then
424 fnd_message.set_name('FND', 'SQL-Generic error');
425 fnd_message.set_token('ERROR', sqlcode, FALSE);
426 fnd_message.set_token('REASON', sqlerrm, FALSE);
427 init_pvt_vars(TRUE);
428 return( FALSE );
429
430 when set_print_option_failed then
431 init_pvt_vars(TRUE);
432 return( FALSE );
433
434 when fndrssub_failed then
435 init_pvt_vars(TRUE);
436 return( FALSE );
437
438 when programs_not_available then
439 fnd_message.set_name('FND', 'SRS-EMPTY SET');
440 init_pvt_vars(TRUE);
441 return( FALSE );
442
443 when request_not_found then
444 fnd_message.set_name('FND', 'CONC-MISSING REQUEST');
445 fnd_message.set_token('ROUTINE', 'set_request_set', FALSE);
446 fnd_message.set_token('REQUEST', rs_submission_program, FALSE);
447 init_pvt_vars(TRUE);
448 return(FALSE);
449
450 when update_failed then
451 fnd_message.set_name('FND', 'SQL-NO UPDATE');
452 fnd_message.set_token('TABLE', 'fnd_concurrent_requests', FALSE);
453 init_pvt_vars(TRUE);
454 return( FALSE );
455
456 when invalid_program_in_set then
457 fnd_message.set_name('FND', 'CONC-Invalid program in set');
458 fnd_message.set_token('SET_NAME', request_set, FALSE);
459 init_pvt_vars(TRUE);
460 return(FALSE);
461
462 end set_request_set;
463
464 procedure set_dest_ops(ops_id IN number default NULL) is
465
466 begin
467 P_OPS_ID := ops_id;
468 end;
469
470 -- --
471 -- Name
472 -- Submit_Program
473 -- Purpose
474 -- It inserts rows into FND_RUN_REQUESTS table for program specified.
475 -- The program should exists in Request Set. It also inserts rows into
476 -- FND_RUN_REQ_PP_ACTIONS table based on the options set before calling
477 -- this function.
478 -- Call set_request_set function before calling this function to set the
479 -- context for the report set submission. Call this function for each
480 -- program in the report set.
481 -- Function will return TRUE on success and FALSE on failure.
482
483 function submit_program (
484 application IN varchar2 default null,
485 program IN varchar2 default null,
486 stage IN varchar2 default null,
487 argument1 IN varchar2 default CHR(0),
488 argument2 IN varchar2 default CHR(0),
489 argument3 IN varchar2 default CHR(0),
490 argument4 IN varchar2 default CHR(0),
491 argument5 IN varchar2 default CHR(0),
492 argument6 IN varchar2 default CHR(0),
493 argument7 IN varchar2 default CHR(0),
494 argument8 IN varchar2 default CHR(0),
495 argument9 IN varchar2 default CHR(0),
496 argument10 IN varchar2 default CHR(0),
497 argument11 IN varchar2 default CHR(0),
498 argument12 IN varchar2 default CHR(0),
499 argument13 IN varchar2 default CHR(0),
500 argument14 IN varchar2 default CHR(0),
501 argument15 IN varchar2 default CHR(0),
502 argument16 IN varchar2 default CHR(0),
503 argument17 IN varchar2 default CHR(0),
504 argument18 IN varchar2 default CHR(0),
505 argument19 IN varchar2 default CHR(0),
506 argument20 IN varchar2 default CHR(0),
507 argument21 IN varchar2 default CHR(0),
508 argument22 IN varchar2 default CHR(0),
509 argument23 IN varchar2 default CHR(0),
510 argument24 IN varchar2 default CHR(0),
511 argument25 IN varchar2 default CHR(0),
512 argument26 IN varchar2 default CHR(0),
513 argument27 IN varchar2 default CHR(0),
514 argument28 IN varchar2 default CHR(0),
515 argument29 IN varchar2 default CHR(0),
516 argument30 IN varchar2 default CHR(0),
517 argument31 IN varchar2 default CHR(0),
518 argument32 IN varchar2 default CHR(0),
519 argument33 IN varchar2 default CHR(0),
520 argument34 IN varchar2 default CHR(0),
521 argument35 IN varchar2 default CHR(0),
522 argument36 IN varchar2 default CHR(0),
523 argument37 IN varchar2 default CHR(0),
524 argument38 IN varchar2 default CHR(0),
525 argument39 IN varchar2 default CHR(0),
526 argument40 IN varchar2 default CHR(0),
527 argument41 IN varchar2 default CHR(0),
528 argument42 IN varchar2 default CHR(0),
529 argument43 IN varchar2 default CHR(0),
530 argument44 IN varchar2 default CHR(0),
531 argument45 IN varchar2 default CHR(0),
532 argument46 IN varchar2 default CHR(0),
533 argument47 IN varchar2 default CHR(0),
534 argument48 IN varchar2 default CHR(0),
535 argument49 IN varchar2 default CHR(0),
536 argument50 IN varchar2 default CHR(0),
537 argument51 IN varchar2 default CHR(0),
538 argument52 IN varchar2 default CHR(0),
539 argument53 IN varchar2 default CHR(0),
540 argument54 IN varchar2 default CHR(0),
541 argument55 IN varchar2 default CHR(0),
542 argument56 IN varchar2 default CHR(0),
543 argument57 IN varchar2 default CHR(0),
544 argument58 IN varchar2 default CHR(0),
545 argument59 IN varchar2 default CHR(0),
546 argument60 IN varchar2 default CHR(0),
547 argument61 IN varchar2 default CHR(0),
548 argument62 IN varchar2 default CHR(0),
549 argument63 IN varchar2 default CHR(0),
550 argument64 IN varchar2 default CHR(0),
551 argument65 IN varchar2 default CHR(0),
552 argument66 IN varchar2 default CHR(0),
553 argument67 IN varchar2 default CHR(0),
554 argument68 IN varchar2 default CHR(0),
555 argument69 IN varchar2 default CHR(0),
556 argument70 IN varchar2 default CHR(0),
557 argument71 IN varchar2 default CHR(0),
558 argument72 IN varchar2 default CHR(0),
559 argument73 IN varchar2 default CHR(0),
560 argument74 IN varchar2 default CHR(0),
561 argument75 IN varchar2 default CHR(0),
562 argument76 IN varchar2 default CHR(0),
563 argument77 IN varchar2 default CHR(0),
564 argument78 IN varchar2 default CHR(0),
565 argument79 IN varchar2 default CHR(0),
566 argument80 IN varchar2 default CHR(0),
567 argument81 IN varchar2 default CHR(0),
568 argument82 IN varchar2 default CHR(0),
569 argument83 IN varchar2 default CHR(0),
570 argument84 IN varchar2 default CHR(0),
571 argument85 IN varchar2 default CHR(0),
572 argument86 IN varchar2 default CHR(0),
573 argument87 IN varchar2 default CHR(0),
574 argument88 IN varchar2 default CHR(0),
575 argument89 IN varchar2 default CHR(0),
576 argument90 IN varchar2 default CHR(0),
577 argument91 IN varchar2 default CHR(0),
578 argument92 IN varchar2 default CHR(0),
579 argument93 IN varchar2 default CHR(0),
580 argument94 IN varchar2 default CHR(0),
581 argument95 IN varchar2 default CHR(0),
582 argument96 IN varchar2 default CHR(0),
583 argument97 IN varchar2 default CHR(0),
584 argument98 IN varchar2 default CHR(0),
585 argument99 IN varchar2 default CHR(0),
586 argument100 IN varchar2 default CHR(0))
587 return boolean is
588
589 par_request_id number;
590 profile_buffer varchar2(80) := null;
591 request_threshold number := 0;
592 print_copies number := 0;
593 req_limit char;
594 issubreq char := 'N';
595
596
597 default_copies number;
598 default_printer varchar2(30);
599 fcr_printer varchar2(30);
600 fcp_printer varchar2(30);
601 curr_printer varchar2(30);
602 curr_copies number;
603 tot_copies number := 0;
604 print_style varchar2(30);
605 valid_style varchar2(30) := null;
606 reqrd_flag char;
607 minwid number(3);
608 maxwid number(3) := null;
609 minlen number(3);
610 maxlen number(3) := null;
611 execcode char;
612 saveout char;
613 prtflg char;
614 qctlflg char;
615 styl_ok boolean := FALSE;
616 dummy char;
617 request_set_flag varchar2(1);
618 base_len number;
619 encoded_msg varchar2(4000);
620
621 rsp_program_id number;
622 rsp_prog_app_id number;
623 rsp_save_output varchar2(1);
624 rsp_conc_prog_id number;
625 rsp_nls_lang varchar2(30);
626 rsp_nls_territory varchar2(30);
627 rsp_copies number;
628 rsp_printer varchar2(30);
629 rsp_style varchar2(30);
630 rsp_save_output_flag varchar2(1);
631
632 TAB_INDEX number := 0;
633 FOUND boolean := FALSE;
634
635 printer_error exception;
636 style_error exception;
637 srw_style_error exception;
638 printer_styl_error exception;
639 insert_error exception;
640 dual_no_rows exception;
641 dual_too_many_rows exception;
642 nls_error exception;
643 appl_prog_error exception;
644 already_msg exception;
645 program_not_found exception;
646 context_not_set exception;
647
648 i number;
649 new_class boolean;
650
651 begin
652 -- findout the set_request_set called or not.
653 if( P_SET_APP_ID is null or P_SET_ID is null ) then
654 raise context_not_set;
655 end if;
656
657
658
659 -- check the program exists in the specified stage by checking
660 -- in the table.
661 --
662 -- bug 1679626 (ckclark): When there is more than one occurance
663 -- of a specific program within the stage, need to check the
664 -- flag to see whether this instance of the program has already
665 -- been submitted within the stage. The cursor used to populate
666 -- P_SET_PROGRAMS was already in order by RSP.sequence, so we
667 -- should be picking out the lowest ordered instance of the program
668 -- each time
669
670 TAB_INDEX := 0;
671 FOUND := false;
672
673 while (TAB_INDEX < P_SET_PROGRAM_COUNT) and (not FOUND) loop
674 if ( upper( P_SET_PROGRAMS(TAB_INDEX).program )
675 = upper( program )
676 and
677 upper( P_SET_PROGRAMS(TAB_INDEX).stage )
678 = upper( stage )
679 and
680 not ( P_SET_PROGRAMS(TAB_INDEX).flag) ) then
681
682 FOUND := true;
683 else
684 TAB_INDEX := TAB_INDEX + 1;
685 end if;
686 end loop;
687
688 if ( not FOUND ) then
689 raise program_not_found;
690 end if;
691
692 -- get the request_set_program_id,prog_app_id, conc_prog_id and
693 -- all options from the fnd_request_set_programs
694
695 begin
696 select request_set_program_id, program_application_id,
697 RSP.concurrent_program_id, RSP.nls_language,
698 RSP.nls_territory, RSP.number_of_copies, RSP.printer,
699 RSP.print_style, RSP.save_output_flag
700 into rsp_program_id, rsp_prog_app_id,
701 rsp_conc_prog_id, rsp_nls_lang,
702 rsp_nls_territory, rsp_copies, rsp_printer,
703 rsp_style, rsp_save_output_flag
704 from fnd_request_set_programs RSP, fnd_request_set_stages RSS,
705 fnd_concurrent_programs CP
706 where RSP.set_application_id = P_SET_APP_ID
707 and RSP.request_set_id = P_SET_ID
708 and RSP.set_application_id = RSS.set_application_id
709 and RSP.request_set_id = RSS.request_set_id
710 and RSP.request_set_stage_id = RSS.request_set_stage_id
711 and upper(RSS.stage_name) =
712 upper( P_SET_PROGRAMS(TAB_INDEX).stage)
713 and CP.application_id = RSP.program_application_id
714 and CP.concurrent_program_id = RSP.concurrent_program_id
715 and upper(CP.concurrent_program_name) =
716 upper( P_SET_PROGRAMS(TAB_INDEX).program )
717 and RSP.sequence = P_SET_PROGRAMS(TAB_INDEX).seq;
718
719 exception
720 when no_data_found then
721 raise program_not_found;
722 when others then
723 raise;
724 end;
725
726
727 if (P_PRINT_TOGETHER is NULL) then
728 FND_PROFILE.GET ('CONC_PRINT_TOGETHER', profile_buffer);
729 if (substr (profile_buffer, 1, 1) = 'Y') then
730 P_PRINT_TOGETHER := 'Y';
731 else
732 P_PRINT_TOGETHER := 'N';
733 end if;
734 end if;
735
736
737 -- Default NLS language
738 if (P_LANGUAGE is NULL) then
739 begin
740 select substr(userenv('LANGUAGE'),1,
741 instr(userenv('LANGUAGE'), '_') -1)
742 into P_LANGUAGE
743 from dual;
744
745 exception
746 when no_data_found then
747 raise nls_error;
748 when others then
749 raise;
750 end;
751 end if;
752
753 -- Default NLS territory
754 if (P_TERRITORY is NULL) then
755 begin
756 select substr ( userenv('LANGUAGE') ,
757 instr ( userenv('LANGUAGE') , '_') + 1,
758 (instr ( userenv('LANGUAGE') , '.') - 1 -
759 instr ( userenv('LANGUAGE') , '_') ))
760 into P_TERRITORY
761 from dual;
762
763 exception
764 when no_data_found then
765 raise nls_error;
766 when others then
767 raise;
768 end;
769 end if;
770
771 -- Get program's Printer, Print Style,
772 -- Save output flag, priority, and request set flag
773 -- from fnd_concurrent_programs
774 begin
775 Select
776 Printer_Name, NVL(Output_Print_Style, 'PORTRAIT'),
777 Required_Style, Minimum_Width,
778 Minimum_Length,
779 Execution_Method_Code, Save_Output_Flag, Print_Flag,
780 Queue_Control_Flag
781 Into
782 fcp_printer, print_style, reqrd_flag, minwid,
783 minlen, execcode, saveout, prtflg,
784 qctlflg
785 From Fnd_Concurrent_Programs P
786 Where P.Application_ID = rsp_prog_app_id
787 and P.concurrent_program_id = rsp_conc_prog_id;
788
789
790 exception
791 when no_data_found then
792 raise appl_prog_error;
793 when others then
794 raise;
795 end;
796
797
798 -- Set save output flag. First look for the token.
799 -- Then in Request_set_programs, FCP,
800 -- profiles, and lastly set it to Y
801
802 if (P_SAVE_OUTPUT in ('Y', 'N')) then
803 saveout := P_SAVE_OUTPUT;
804 else
805 if (rsp_save_output in ('Y', 'N')) then
806 saveout := rsp_save_output;
807 else
808 if ( not saveout in ('Y', 'N')) then
809 FND_PROFILE.GET ('CONC_SAVE_OUTPUT', profile_buffer);
810 if (not profile_buffer in ('Y', 'N')) then
811 saveout := 'Y';
812 else
813 saveout := profile_buffer;
814 end if;
815 end if;
816 end if;
817 end if;
818
819
820 -- Printer logic
821
822 -- Get default for # of copies
823 FND_PROFILE.GET ('CONC_COPIES', profile_buffer);
824 if (profile_buffer is not null) then
825 default_copies := To_Number (profile_buffer);
826 if (default_copies < 0) then
827 default_copies := 0;
828 end if;
829 else
830 default_copies := 0;
831 end if;
832
833 -- Is printer specified in FCP?
834 if (fcp_printer is null) then
835 FND_PROFILE.GET ('PRINTER', default_printer);
836 else
837 default_printer := fcp_printer;
838 end if;
839
840 -- If style is passed as an argument, use it only if style is
841 -- not required in FCP (fcp.required_style = 'N').
842 if ((reqrd_flag <> 'Y') and (P_PRINT_STYLE is not null)) then
843 print_style := P_PRINT_STYLE;
844 end if;
845
846 -- Get ready for loop. If no printers were specified, then
847 -- we need to set up a default printer if the default copies > 0.
848 if ((P_PRINTER_COUNT = 0) and (default_copies > 0)) then
849 P_PRINTERS(1).printer := default_printer;
850 P_PRINTERS(1).copies := default_copies;
851 P_PRINTER_COUNT := 1;
852 end if;
853
854 -- Printer Loop
855 for i in 1..P_PRINTER_COUNT loop
856
857 curr_printer := P_PRINTERS(i).printer;
858 curr_copies := P_PRINTERS(i).copies;
859
860 if (curr_copies is null) then
861 curr_copies := default_copies;
862 end if;
863
864 if (curr_copies > 0) then
865 tot_copies := tot_copies + curr_copies;
866
867 if (curr_printer is null) then
868 curr_printer := default_printer;
869 end if;
870
871 -- Printer validation
872 -- Validate the printer only if we are going to print, which is,
873 -- if the number of copies is > 0, print = Yes, and it is not
874 -- a queue control request (e.g. Deactivate Concurrent Manager).
875 if ((prtflg = 'Y') and (qctlflg <> 'Y')) then
876 -- Error if printer is not specified.
877 -- Or, if printer is specified in FCP and also passed as an
878 -- argument, but they don't match.
879 if ((curr_printer is null) or
880 ((curr_printer is not null) and (fcp_printer is not null) and
881 (curr_printer <> fcp_printer))) then
882 raise printer_error;
883 end if;
884 end if;
885
886 -- Print style logic
887
888 -- Print style validation
889
890 if ((prtflg = 'Y') and (print_style is null))
891 then
892 raise style_error;
893 end if;
894
895 -- We do not need to validate print style if it's a queue
896 -- control request or if the the style is DYNAMIC
897 if ((qctlflg <> 'Y') and (print_style <> 'DYNAMIC')) then
898 styl_ok := fnd_print.get_style (print_style,
899 minwid, maxwid, minlen, maxlen,
900 (reqrd_flag = 'Y'), curr_printer,
901 valid_style);
902
903 -- If printer and print_style were incompatible, valid_style
904 -- is null. Also check to see if fnd_printer.get_style failed.
905 if ((valid_style is null) or (not styl_ok)) then
906
907 -- If we still intend to print, bad news.
908 if (prtflg = 'Y') then
909
910 -- Check for message on stack
911 encoded_msg := FND_MESSAGE.GET_ENCODED;
912 if (encoded_msg is not null) then
913 FND_MESSAGE.SET_ENCODED(encoded_msg);
914 raise already_msg;
915 end if;
916
917 raise printer_styl_error;
918
919 end if; -- ((curr_copies > 0) and (prtflg = 'Y'))
920 end if; -- ((valid_style is null) or (not styl_ok))
921 end if; -- ((qctlflg <> 'Y') and (print_style <> 'DYNAMIC'))
922
923 -- insert the action
924 -- (don't print on warning or failure)
925
926 insert into fnd_run_req_pp_actions
927 (parent_request_id, request_set_program_id,
928 set_application_id, request_set_id,
929 action_type, status_s_flag,
930 status_w_flag, status_f_flag,
931 program_application_id, program_id,
932 arguments, number_of_copies, sequence,ops_instance)
933 values
934 (P_RS_REQUEST_ID, rsp_program_id,
935 P_SET_APP_ID, P_SET_ID,
936 1, 'Y', 'N', 'N', NULL, NULL, curr_printer,
937 curr_copies, i, NVL(P_OPS_ID,fnd_conc_global.ops_inst_num));
938
939 end if; -- Curr_copies > 0
940 end loop;
941
942 -- The first printer in the list will be written into
943 -- fcr. Reports might use it. Product teams might also
944 -- depend on the printer profile in their requests.
945 if (P_PRINTER_COUNT > 0) then
946 fcr_printer := P_PRINTERS(1).printer;
947 else
948 fcr_printer := fcp_printer;
949 end if;
950
951 -- Even if we aren't going to print, we'll populate
952 -- the style. Styles are required by Oracle Reports.
953 if (valid_style is null) then
954 -- if it's an Oracle Reports request, we must get
955 -- some (valid) print style
956 if (execcode = 'P') then
957 styl_ok := fnd_print.get_style (print_style,
958 minwid, maxwid,
959 minlen, maxlen,
960 (reqrd_flag = 'Y'),
961 null,
962 valid_style);
963
964 if ((valid_style is null) or (not styl_ok)) then
965 -- Check for message on stack
966 encoded_msg := FND_MESSAGE.GET_ENCODED;
967 if (encoded_msg is not null) then
968 FND_MESSAGE.SET_ENCODED(encoded_msg);
969 raise already_msg;
970 end if;
971
972 raise srw_style_error;
973 end if;
974 else -- No need to validate style
975 valid_style := print_style;
976 end if; -- (execcode = 'P')
977 end if; -- (valid_style is null)
978
979
980 -- Insert Notifications
981 -- 12.1 Project Changes: Added orig_system and orig_system_id
982 for i in 1..P_NOTIFICATION_COUNT loop
983 insert into fnd_run_req_pp_actions
984 (parent_request_id, request_set_program_id,
985 set_application_id, request_set_id,
986 action_type, status_s_flag,
987 status_w_flag, status_f_flag,
988 program_application_id, program_id,
989 arguments, number_of_copies, sequence,ops_instance, orig_system, orig_system_id)
990 values
991 (P_RS_REQUEST_ID, rsp_program_id,
992 P_SET_APP_ID, P_SET_ID, 2,
993 P_NOTIFICATIONS(i).on_normal, P_NOTIFICATIONS(i).on_warning, P_NOTIFICATIONS(i).on_error,
994 NULL, NULL, P_NOTIFICATIONS(i).name,
995 NULL, i,NVL(P_OPS_ID,fnd_conc_global.ops_inst_num),
996 P_NOTIFICATIONS(i).orig_system,
997 P_NOTIFICATIONS(i).orig_system_id);
998 end loop;
999
1000
1001 -- Insert layout options
1002 if P_TEMPLATE_CODE is not null then
1003 insert into fnd_run_req_pp_actions
1004 (parent_request_id, request_set_program_id,
1005 set_application_id, request_set_id,
1006 action_type, status_s_flag,
1007 status_w_flag, status_f_flag,
1008 program_application_id, program_id,
1009 arguments, number_of_copies, sequence, ops_instance,
1010 argument1, argument2, argument3, argument4, argument5)
1011 values
1012 (P_RS_REQUEST_ID, rsp_program_id,
1013 P_SET_APP_ID, P_SET_ID,
1014 6, 'Y', 'N', 'N', NULL, NULL, NULL,
1015 NULL, 1, NVL(P_OPS_ID, fnd_conc_global.ops_inst_num),
1016 P_TEMPLATE_APPL, P_TEMPLATE_CODE, P_TEMPLATE_LANG,
1017 P_TEMPLATE_TERR, P_OUTPUT_FORMAT);
1018 end if;
1019
1020
1021 -- Insert into fnd_run_requests table
1022 --
1023 -- bug5676655/bug5709193 added numeric_characters
1024 --
1025 insert
1026 into fnd_run_requests (
1027 application_id, concurrent_program_id,
1028 parent_request_id, request_set_program_id,
1029 set_application_id, request_set_id,
1030 number_of_copies, printer, print_style,
1031 save_output_flag, nls_language, nls_territory, OPS_Instance,
1032 argument1, argument2, argument3, argument4,
1033 argument5, argument6, argument7, argument8,
1034 argument9, argument10, argument11, argument12,
1035 argument13, argument14, argument15, argument16,
1036 argument17, argument18, argument19, argument20,
1037 argument21, argument22, argument23, argument24,
1038 argument25, argument26, argument27, argument28,
1039 argument29, argument30, argument31, argument32,
1040 argument33, argument34, argument35, argument36,
1041 argument37, argument38, argument39, argument40,
1042 argument41, argument42, argument43, argument44,
1043 argument45, argument46, argument47, argument48,
1044 argument49, argument50, argument51, argument52,
1045 argument53, argument54, argument55, argument56,
1046 argument57, argument58, argument59, argument60,
1047 argument61, argument62, argument63, argument64,
1048 argument65, argument66, argument67, argument68,
1049 argument69, argument70, argument71, argument72,
1050 argument73, argument74, argument75, argument76,
1051 argument77, argument78, argument79, argument80,
1052 argument81, argument82, argument83, argument84,
1053 argument85, argument86, argument87, argument88,
1054 argument89, argument90, argument91, argument92,
1055 argument93, argument94, argument95, argument96,
1056 argument97, argument98, argument99, argument100, org_id,
1057 numeric_characters)
1058 select rsp_prog_app_id, rsp_conc_prog_id,
1059 P_RS_REQUEST_ID, rsp_program_id,
1060 P_SET_APP_ID, P_SET_ID,
1061 tot_copies, fcr_printer, valid_style,
1062 saveout, P_LANGUAGE, P_TERRITORY,
1063 NVL(P_OPS_ID,fnd_conc_global.ops_inst_num),
1064 submit_program.argument1, submit_program.argument2,
1065 submit_program.argument3, submit_program.argument4,
1066 submit_program.argument5, submit_program.argument6,
1067 submit_program.argument7, submit_program.argument8,
1068 submit_program.argument9, submit_program.argument10,
1069 submit_program.argument11, submit_program.argument12,
1070 submit_program.argument13, submit_program.argument14,
1071 submit_program.argument15, submit_program.argument16,
1072 submit_program.argument17, submit_program.argument18,
1073 submit_program.argument19, submit_program.argument20,
1074 submit_program.argument21, submit_program.argument22,
1075 submit_program.argument23, submit_program.argument24,
1076 submit_program.argument25, submit_program.argument26,
1077 submit_program.argument27, submit_program.argument28,
1078 submit_program.argument29, submit_program.argument30,
1079 submit_program.argument31, submit_program.argument32,
1080 submit_program.argument33, submit_program.argument34,
1081 submit_program.argument35, submit_program.argument36,
1082 submit_program.argument37, submit_program.argument38,
1083 submit_program.argument39, submit_program.argument40,
1084 submit_program.argument41, submit_program.argument42,
1085 submit_program.argument43, submit_program.argument44,
1086 submit_program.argument45, submit_program.argument46,
1087 submit_program.argument47, submit_program.argument48,
1088 submit_program.argument49, submit_program.argument50,
1089 submit_program.argument51, submit_program.argument52,
1090 submit_program.argument53, submit_program.argument54,
1091 submit_program.argument55, submit_program.argument56,
1092 submit_program.argument57, submit_program.argument58,
1093 submit_program.argument59, submit_program.argument60,
1094 submit_program.argument61, submit_program.argument62,
1095 submit_program.argument63, submit_program.argument64,
1096 submit_program.argument65, submit_program.argument66,
1097 submit_program.argument67, submit_program.argument68,
1098 submit_program.argument69, submit_program.argument70,
1099 submit_program.argument71, submit_program.argument72,
1100 submit_program.argument73, submit_program.argument74,
1101 submit_program.argument75, submit_program.argument76,
1102 submit_program.argument77, submit_program.argument78,
1103 submit_program.argument79, submit_program.argument80,
1104 submit_program.argument81, submit_program.argument82,
1105 submit_program.argument83, submit_program.argument84,
1106 submit_program.argument85, submit_program.argument86,
1107 submit_program.argument87, submit_program.argument88,
1108 submit_program.argument89, submit_program.argument90,
1109 submit_program.argument91, submit_program.argument92,
1110 submit_program.argument93, submit_program.argument94,
1111 submit_program.argument95, submit_program.argument96,
1112 submit_program.argument97, submit_program.argument98,
1113 submit_program.argument99, submit_program.argument100,
1114 P_ORG_ID,
1115 P_NUMERIC_CHARACTERS
1116 from sys.dual;
1117
1118 if (sql%rowcount = 0) then
1119 raise insert_error;
1120 end if;
1121
1122 -- set the P_SET_PROGRAMS table flag to TRUE, which indicates that
1123 -- the program is submitted. We already got the index for the program
1124 P_SET_PROGRAMS(TAB_INDEX).flag := TRUE;
1125
1126 init_prog_pvt_vars;
1127 return (TRUE);
1128
1129 exception
1130 when context_not_set then
1131 fnd_message.set_name('FND', 'CONC-Context not set');
1132 fnd_message.set_token('ROUTINE', 'submit_program', FALSE);
1133 init_pvt_vars(TRUE);
1134 return(FALSE);
1135 when program_not_found then
1136 fnd_message.set_name ('FND', 'CONC-Invalid prog for stage');
1137 fnd_message.set_token('PROGRAM', program, FALSE);
1138 fnd_message.set_token('STAGE', stage, FALSE);
1139 init_pvt_vars(TRUE);
1140 return(FALSE);
1141 when printer_error then
1142 fnd_message.set_name ('FND', 'CONC-Illegal printer spec');
1143 init_pvt_vars(TRUE);
1144 return (FALSE);
1145 when style_error then
1146 fnd_message.set_name ('FND', 'SRS-Must specify print style');
1147 init_pvt_vars(TRUE);
1148 return (FALSE);
1149 when printer_styl_error then
1150 fnd_message.set_name ('FND', 'CONC-Invalid printer style');
1151 fnd_message.set_token ('STYLE', print_style, FALSE);
1152 fnd_message.set_token ('PRINTER', curr_printer, FALSE);
1153 init_pvt_vars(TRUE);
1154 return (FALSE);
1155 when srw_style_error then
1156 fnd_message.set_name ('FND', 'SRS-Must specify srw style');
1157 init_pvt_vars(TRUE);
1158 return (FALSE);
1159 when nls_error then
1160 fnd_message.set_name ('FND', 'GLI-Not found');
1161 init_pvt_vars(TRUE);
1162 return (FALSE);
1163
1164 when insert_error then
1165 fnd_message.set_name ('FND', 'SQL-Generic error');
1166 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
1167 fnd_message.set_token ('REASON', sqlerrm, FALSE);
1168 fnd_message.set_token (
1169 'ROUTINE', 'SUBMIT_PROGRAM: insert_error2', FALSE);
1170 init_pvt_vars(TRUE);
1171 return (FALSE);
1172 when dual_no_rows then
1173 fnd_message.set_name ('FND', 'No Rows in SYS.Dual');
1174 init_pvt_vars(TRUE);
1175 return (FALSE);
1176 when dual_too_many_rows then
1177 fnd_message.set_name ('FND', 'Too many rows in SYS.Dual');
1178 init_pvt_vars(TRUE);
1179 return (FALSE);
1180 when already_msg then
1181 init_pvt_vars(TRUE);
1182 return (FALSE);
1183 when others then
1184 fnd_message.set_name ('FND', 'SQL-Generic error');
1185 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
1186 fnd_message.set_token ('REASON', sqlerrm, FALSE);
1187 fnd_message.set_token (
1188 'ROUTINE', 'SUBMIT_PROGRAM: others', FALSE);
1189 init_pvt_vars(TRUE);
1190 return (FALSE);
1191
1192 end submit_program;
1193
1194 -- --
1195 -- Name
1196 -- submit_set
1197 -- Purpose
1198 -- Call this function to submit the request set which is set by using the
1199 -- set_request_set.
1200 -- It will check whether each program in the request set is submitted
1201 -- or not.
1202 -- If the request completes successfully, thsi function returns the
1203 -- concurrent request ID (P_RS_REQUEST_ID); otherwise; it returns 0.
1204
1205 function submit_set( start_time IN varchar2 default NULL,
1206 sub_request IN boolean default FALSE)
1207 return integer is
1208 TAB_INDEX integer;
1209 req_id integer;
1210 profile_buffer varchar2(80) := null;
1211 tz_offset integer;
1212 requested_start date;
1213 issubreq varchar2(1) := 'N';
1214 par_request_id number;
1215
1216 program_not_called exception;
1217 update_failed exception;
1218 context_not_set exception;
1219 start_time_error exception;
1220
1221 begin
1222 -- findout the set_request_set called or not.
1223 if( P_SET_APP_ID is null or P_SET_ID is null ) then
1224 raise context_not_set;
1225 end if;
1226
1227 -- Check submit_program is called for all the programs in the request set.
1228 -- If not rollback to the start_of_transaction.
1229 TAB_INDEX := 0;
1230
1231 while (TAB_INDEX < P_SET_PROGRAM_COUNT) loop
1232 if ( not P_SET_PROGRAMS(TAB_INDEX).flag ) then
1233 raise program_not_called;
1234 end if;
1235 TAB_INDEX := TAB_INDEX + 1;
1236 end loop;
1237
1238 -- Get info. about requests origination
1239 -- if being submitted from another concurrent request get parent
1240 -- request information.
1241
1242 par_request_id := FND_GLOBAL.conc_request_id;
1243
1244 profile_buffer := NULL;
1245 FND_PROFILE.GET('CONC_MULTI_TZ', profile_buffer);
1246
1247 if (profile_buffer in ('Y', 'y')) then
1248 tz_offset := 1;
1249 else
1250 tz_offset := 0;
1251 end if;
1252
1253 profile_buffer := NULL;
1254 if (start_time is null) then
1255 FND_PROFILE.GET ('CONC_REQ_START', profile_buffer);
1256 else
1257 profile_buffer := start_time;
1258 end if;
1259
1260 if (profile_buffer is not null) then
1261 requested_start := fnd_conc_date.string_to_date(profile_buffer);
1262 if (requested_start is null) then
1263 raise start_time_error;
1264 end if;
1265 else
1266 requested_start := null;
1267 end if;
1268
1269 if( sub_request ) then
1270 issubreq := 'Y';
1271 end if;
1272
1273 -- Update back the request phase_code and status_code to the original
1274 -- values.
1275 update fnd_concurrent_requests
1276 set phase_code = P_PHASE_CODE,
1277 status_code = Decode(issubreq, 'Y', 'Z', P_STATUS_CODE),
1278 completion_text = '',
1279 is_sub_request = issubreq,
1280 requested_start_date =
1281 Decode (requested_start,
1282 '', (SYSDATE - tz_offset),
1283 Greatest (requested_start,
1284 Decode (par_request_id,
1285 0, (SYSDATE - tz_offset),
1286 To_Date ('01-01-0001',
1287 'DD-MM-YYYY'))))
1288 where request_id = P_RS_REQUEST_ID;
1289
1290 if (sql%rowcount = 0 ) then
1291 raise update_failed;
1292 end if;
1293
1294 -- initialize the private variables and return the request id of the
1295 -- set submitted in set_request_set
1296 req_id := P_RS_REQUEST_ID;
1297 init_pvt_vars(FALSE);
1298 return( req_id );
1299
1300 exception
1301 when context_not_set then
1302 fnd_message.set_name('FND', 'CONC-Context not set');
1303 fnd_message.set_token('ROUTINE', 'submit_set', FALSE);
1304 init_pvt_vars(TRUE);
1305 return(0);
1306
1307 when program_not_called then
1308 fnd_message.set_name('FND', 'CONC-submit program not called');
1309 fnd_message.set_token('PROGRAM', P_SET_PROGRAMS(TAB_INDEX).program,
1310 FALSE);
1311 fnd_message.set_token('STAGE',P_SET_PROGRAMS(TAB_INDEX).stage, FALSE);
1312 init_pvt_vars(TRUE);
1313 return ( 0 );
1314 when update_failed then
1315 fnd_message.set_name('FND', 'SQL-NO UPDATE');
1316 fnd_message.set_token('TABLE', 'fnd_concurrent_requests', FALSE);
1317 init_pvt_vars(TRUE);
1318 return( 0 );
1319 when start_time_error then
1320 fnd_message.set_name ('FND', 'CONC-Invalid Req Start Date');
1321 fnd_message.set_token ('START_DATE', requested_start, FALSE);
1322 init_pvt_vars(TRUE);
1323 return (0);
1324 end submit_set;
1325
1326 --
1327 -- Name
1328 -- set_mode
1329 -- Purpose
1330 -- Called before submitting request to set database trigger mode
1331 --
1332 -- Arguments
1333 -- db_trigger - Set to TRUE for database trigger mode
1334 --
1335 function set_mode (db_trigger IN boolean) return boolean is
1336 begin
1337 P_DB_TRIGGER_MODE := db_trigger;
1338 return (fnd_request.set_mode(db_trigger));
1339 return TRUE;
1340 end;
1341
1342
1343 -- Name
1344 -- set_nls_options
1345 -- bug5676655/bug5709193 - added p_numeric_characters
1346 -- Purpose
1347 -- Called before submitting request to set request attributes
1348 --
1349 -- Arguments
1350 --
1351 -- language - NLS language
1352 -- territory - Language territory
1353 -- numeric_characters - Numeric Characters
1354 --
1355 -- --
1356
1357 function set_nls_options( language IN varchar2 default NULL,
1358 territory IN varchar2 default NULL,
1359 numeric_characters IN varchar2 default NULL)
1360 return boolean is
1361 begin
1362
1363 P_LANGUAGE := language;
1364 P_TERRITORY := territory;
1365 P_NUMERIC_CHARACTERS := numeric_characters;
1366
1367
1368 return (TRUE);
1369
1370 end set_nls_options;
1371
1372
1373 --
1374 -- Name
1375 -- set_repeat_options
1376 -- Purpose
1377 -- Called before submitting request if the request to be submitted
1378 -- is a repeating request.
1379 -- All the messages are set by fnd_request.set_repeat_options function.
1380 --
1381 -- Arguments
1382 -- repeat_time - Time of day at which it has to be repeated
1383 -- repeat_interval - Frequency at which it has to be repeated
1384 -- - This will be used/applied only when repeat_time
1385 -- - is NULL ( non null repeat_interval overrides )
1386 -- repeat_unit - Unit for repeat interval. Default is DAYS.
1387 -- - MONTHS/DAYS/HOURS/MINUTES
1388 -- repeat_type - Apply repeat interval from START or END of request
1389 -- - default is START. START/END
1390 -- repeat_end_time - Time at which the repetition should be stopped
1391 --
1392 function set_repeat_options (repeat_time IN varchar2 default NULL,
1393 repeat_interval IN number default NULL,
1394 repeat_unit IN varchar2 default 'DAYS',
1395 repeat_type IN varchar2 default 'START',
1396 repeat_end_time IN varchar2 default NULL)
1397 return boolean is
1398 success boolean;
1399 begin
1400
1401 -- Just call the fnd_request.set_repeat_options with the passed parameters
1402
1403 success := fnd_request.set_repeat_options(repeat_time,
1404 repeat_interval,
1405 repeat_unit,
1406 repeat_type,
1407 repeat_end_time);
1408 if ( not success ) then
1409 init_pvt_vars(FALSE);
1410 return( FALSE );
1411 else
1412 return ( TRUE );
1413 end if;
1414
1415 end set_repeat_options;
1416
1417
1418
1419 --
1420 -- Name
1421 -- set_rel_class_options
1422 -- Purpose
1423 -- Called before submitting request if the request to be submitted
1424 -- is using the new scheduler functionality.
1425 -- All the failure messages are from the fnd_request package.
1426 --
1427 -- Arguments
1428 -- application - Application Name of Release Class
1429 -- class_name - (Developer) Name of Release Class
1430 -- cancel_or_hold - Cancel or hold flag
1431 -- stale_date - Cancel request on or after this time if not run
1432 --
1433 function set_rel_class_options (application IN varchar2 default NULL,
1434 class_name IN varchar2 default NULL,
1435 cancel_or_hold IN varchar2 default 'H',
1436 stale_date IN varchar2 default NULL)
1437 return boolean is
1438
1439 success boolean;
1440 begin
1441 -- just call fnd_request.set_rel_class_options and return the return code
1442 -- set_rel_class_options is with respect to set_request_set function.
1443
1444 success := fnd_request.set_rel_class_options (
1445 application,
1446 class_name,
1447 cancel_or_hold,
1448 stale_date
1449 );
1450
1451 if ( success ) then
1452 return ( TRUE );
1453 else
1454 init_pvt_vars(FALSE);
1455 return (FALSE);
1456 end if;
1457
1458 end set_rel_class_options;
1459
1460 --
1461 -- Name
1462 -- set_org_id
1463 -- Purpose
1464 -- Called before submitting request if the program is 'Sinle' multi org catagory.,
1465 --
1466 -- Arguments
1467 -- org_id - Operating unit id
1468 --
1469 procedure set_org_id(org_id IN number default NULL) is
1470 begin
1471 P_ORG_ID := org_id;
1472 end;
1473
1474 --
1475 -- Name
1476 -- set_print_options
1477 -- Purpose
1478 -- Called before submitting request if the printing of output has
1479 -- to be controlled with specific printer/style/copies etc.,
1480 --
1481 -- Arguments
1482 -- printer - Printer name where the request o/p should be sent
1483 -- style - Print style that needs to be used for printing
1484 -- copies - Number of copies to print
1485 -- save_output - Should the output file be saved after printing
1486 -- - Default is TRUE. TRUE/FALSE
1487 -- print_together - Applies only for sub requests. If 'Y', output
1488 -- - will not be printed until all the sub requests
1489 -- - complete. Default is N. Y/N
1490 function set_print_options (printer IN varchar2 default NULL,
1491 style IN varchar2 default NULL,
1492 copies IN number default NULL,
1493 save_output IN boolean default TRUE,
1494 print_together IN varchar2 default 'N')
1495 return boolean is
1496
1497 printer_typ varchar2 (30) := null;
1498 dummy_fld varchar (2);
1499
1500 print_together_error exception;
1501 printer_error exception;
1502 style_error exception;
1503 printer_style_error exception;
1504 empty_array printer_tab_type;
1505 prec printer_record_type;
1506
1507 begin
1508 -- Clear any old printers
1509 -- Just in case this was called twice.
1510 if P_PRINTER_COUNT > 0 then
1511 P_PRINTERS := empty_array;
1512 P_PRINTER_COUNT := 0;
1513 end if;
1514
1515 if (upper (print_together) not in ('Y', 'N')) then
1516 raise print_together_error;
1517 end if;
1518
1519 if (upper (print_together) = 'Y') then
1520 P_PRINT_TOGETHER := 'Y';
1521 elsif (upper (print_together) = 'N') then
1522 P_PRINT_TOGETHER := 'N';
1523 else
1524 P_PRINT_TOGETHER := NULL;
1525 end if;
1526
1527 if (save_output is null) then
1528 P_SAVE_OUTPUT := NULL;
1529 elsif (save_output) then
1530 P_SAVE_OUTPUT := 'Y';
1531 else
1532 P_SAVE_OUTPUT := 'N';
1533 end if;
1534
1535 if (printer is not null) then -- Verify printer
1536 begin
1537 Select printer_type
1538 Into printer_typ
1539 From fnd_printer
1540 Where printer_name = printer;
1541
1542 exception
1543 when no_data_found then
1544 raise printer_error;
1545
1546 when others then
1547 raise;
1548 end;
1549 end if; -- Verify printer
1550
1551
1552 if (style is not null) then -- Verify style
1553 begin
1554 Select Printer_Style_Name
1555 Into P_PRINT_STYLE
1556 From Fnd_Printer_styles
1557 Where Printer_Style_Name = style;
1558
1559 exception
1560 when no_data_found then
1561 raise style_error;
1562
1563 when others then
1564 raise;
1565 end;
1566 end if; -- Verify style
1567
1568 if ((printer is not null) and
1569 (style is not null)) then -- Verify printer/style comination
1570 begin
1571 Select 'X'
1572 Into Dummy_fld
1573 From Fnd_Printer_Information
1574 Where Printer_Style = P_PRINT_STYLE
1575 And Printer_Type = printer_typ;
1576
1577 exception
1578 when no_data_found then
1579 raise printer_style_error;
1580
1581 when others then
1582 raise;
1583 end;
1584 end if; -- Verify printer/style comination
1585
1586 -- Add printer/copies to the list.
1587 -- Note that we will attempt to process the defaults
1588 -- for nulls at submission time. For now, store the
1589 -- nulls.
1590
1591 if (copies is not null or printer is not null) then
1592 P_PRINTER_COUNT := 1;
1593
1594 -- The following inderect assignment was required to get
1595 -- the procedure to compile. The problem, for some reason
1596 -- doesn't seem to affect the other procedures in this package.
1597 prec.printer := printer;
1598 prec.copies := copies;
1599 P_PRINTERS(1) := prec;
1600 end if;
1601
1602
1603 return (TRUE);
1604
1605 exception
1606 when print_together_error then
1607 fnd_message.set_name ('FND', 'CONC-Invalid opt:Print Group');
1608 init_pvt_vars (TRUE);
1609 return (FALSE);
1610
1611 when printer_error then
1612 fnd_message.set_name ('FND', 'PRINTERS-No system printer');
1613 fnd_message.set_token ('PRINTER', printer, FALSE);
1614 init_pvt_vars (TRUE);
1615 return (FALSE);
1616
1617 when style_error then
1618 fnd_message.set_name ('FND', 'PRT-Invalid print style');
1619 fnd_message.set_token ('STYLE', style, FALSE);
1620 init_pvt_vars(TRUE);
1621 return (FALSE);
1622
1623 when printer_style_error then
1624 fnd_message.set_name ('FND', 'CONC-Invalid printer style');
1625 fnd_message.set_token ('STYLE', style, FALSE);
1626 fnd_message.set_token ('PRINTER', printer, FALSE);
1627 init_pvt_vars(TRUE);
1628 return (FALSE);
1629
1630 when others then
1631 init_pvt_vars(TRUE);
1632 fnd_message.set_name ('FND', 'SQL-Generic error');
1633 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
1634 fnd_message.set_token ('REASON', sqlerrm, FALSE);
1635 fnd_message.set_token ('ROUTINE', 'SET_PRINT_OPTIONS', FALSE);
1636 return (FALSE);
1637 end set_print_options;
1638
1639 --
1640 -- Name
1641 -- add_printer
1642 -- Purpose
1643 -- Called after set print options to add a printer to the
1644 -- print list.
1645 --
1646 -- Arguments
1647 -- printer - Printer name where the request o/p should be sent
1648 -- copies - Number of copies to print
1649 function add_printer (printer in varchar2 default null,
1650 copies in number default null) return boolean is
1651 printer_typ varchar2 (30) := null;
1652 dummy_fld varchar (2);
1653 print_together_error exception;
1654 printer_error exception;
1655 style_error exception;
1656 printer_style_error exception;
1657 begin
1658 if (printer is not null) then -- Verify printer
1659 begin
1660 Select printer_type
1661 Into printer_typ
1662 From fnd_printer
1663 Where printer_name = printer;
1664
1665 exception
1666 when no_data_found then
1667 raise printer_error;
1668
1669 when others then
1670 raise;
1671 end;
1672 end if; -- Verify printer
1673
1674
1675 if ((printer is not null) and
1676 (P_PRINT_STYLE is not null)) then -- Verify printer/style combo
1677 begin
1678 Select 'X'
1679 Into Dummy_fld
1680 From Fnd_Printer_Information
1681 Where Printer_Style = P_PRINT_STYLE
1682 And Printer_Type = printer_typ;
1683
1684 exception
1685 when no_data_found then
1686 raise printer_style_error;
1687
1688 when others then
1689 raise;
1690 end;
1691 end if; -- Verify printer/style comination
1692
1693 -- Add printer/copies to the list.
1694 -- Note that we will attempt to process the defaults
1695 -- for nulls at submission time. For now, store the
1696 -- nulls.
1697 P_PRINTER_COUNT := P_PRINTER_COUNT + 1;
1698 P_PRINTERS(P_PRINTER_COUNT).printer := printer;
1699 P_PRINTERS(P_PRINTER_COUNT).copies := copies;
1700
1701 return (TRUE);
1702
1703 exception
1704 when print_together_error then
1705 fnd_message.set_name ('FND', 'CONC-Invalid opt:Print Group');
1706 init_pvt_vars(TRUE);
1707 return (FALSE);
1708
1709 when printer_error then
1710 fnd_message.set_name ('FND', 'PRINTERS-No system printer');
1711 fnd_message.set_token ('PRINTER', printer, FALSE);
1712 init_pvt_vars(TRUE);
1713 return (FALSE);
1714
1715 when style_error then
1716 fnd_message.set_name ('FND', 'PRT-Invalid print style');
1717 fnd_message.set_token ('STYLE', P_PRINT_STYLE, FALSE);
1718 init_pvt_vars(TRUE);
1719 return (FALSE);
1720
1721 when printer_style_error then
1722 fnd_message.set_name ('FND', 'CONC-Invalid printer style');
1723 fnd_message.set_token ('STYLE', P_PRINT_STYLE, FALSE);
1724 fnd_message.set_token ('PRINTER', printer, FALSE);
1725 init_pvt_vars(TRUE);
1726 return (FALSE);
1727
1728 when others then
1729 init_pvt_vars(TRUE);
1730 fnd_message.set_name ('FND', 'SQL-Generic error');
1731 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
1732 fnd_message.set_token ('REASON', sqlerrm, FALSE);
1733 fnd_message.set_token ('ROUTINE', 'SET_PRINT_OPTIONS', FALSE);
1734 return (FALSE);
1735
1736 end;
1737
1738
1739 --
1740 -- Name
1741 -- add_notification
1742 -- Purpose
1743 -- Called before submission to add a user to the notify list.
1744 --
1745 -- Arguments
1746 -- User - User name.
1747 -- 12.1 Project Changes: Added orig_system and orig_system_id
1748
1749 function add_notification (
1750 user in varchar2,
1751 on_normal in varchar2 default 'Y',
1752 on_warning in varchar2 default 'N',
1753 on_error in varchar2 default 'N' ) return boolean is
1754 n_index number;
1755 /* c number;
1756 begin
1757 select count(*)
1758 into c
1759 from wf_roles
1760 where user = name;
1761
1762 if (c > 0) then
1763 P_NOTIFICATION_COUNT := P_NOTIFICATION_COUNT + 1;
1764 P_NOTIFICATIONS(P_NOTIFICATION_COUNT).name := user;
1765 P_NOTIFICATIONS(P_NOTIFICATION_COUNT).on_normal := on_normal;
1766 P_NOTIFICATIONS(P_NOTIFICATION_COUNT).on_warning := on_warning;
1767 P_NOTIFICATIONS(P_NOTIFICATION_COUNT).on_error := on_error;
1768 return TRUE;
1769 else
1770 fnd_message.set_name('FND', 'CONC-INVALID NOTIFY USER');
1771 return FALSE;
1772 end if;*/
1773 cursor c1( user_name varchar2) is
1774 select name, orig_system, orig_system_id
1775 from wf_roles
1776 where name = user_name;
1777 begin
1778
1779 -- Same user may exists in different departments(tables).
1780 -- use cursor because we dont know the given user name will return one row
1781 -- multiple rows.
1782 -- we are considering only the first row that matched in wf_roles.
1783
1784 n_index := P_NOTIFICATION_COUNT + 1;
1785 open c1( user );
1786 fetch c1 into P_NOTIFICATIONS(n_index).name,
1787 P_NOTIFICATIONS(n_index).orig_system,
1788 P_NOTIFICATIONS(n_index).orig_system_id;
1789
1790 P_NOTIFICATIONS(n_index).on_normal := on_normal;
1791 P_NOTIFICATIONS(n_index).on_warning := on_warning;
1792 P_NOTIFICATIONS(n_index).on_error := on_error;
1793
1794 if( c1%notfound ) then
1795 fnd_message.set_name('FND', 'CONC-INVALID NOTIFY USER');
1796 close c1;
1797 return FALSE;
1798 else
1799 P_NOTIFICATION_COUNT := P_NOTIFICATION_COUNT + 1;
1800 close c1;
1801 return TRUE;
1802 end if;
1803 end;
1804
1805
1806 --
1807 -- Name
1808 -- add_layout
1809 -- Purpose
1810 -- Called before submission to add layout options to a request.
1811 --
1812 -- Arguments
1813 -- template_appl_name - Template application short name
1814 -- template_code - Template code
1815 -- template_language - ISO 2-letter language code
1816 -- template_territory - ISO 2-letter territory code
1817 -- output_format - Output format type of the final output
1818 function add_layout(template_appl_name in varchar2,
1819 template_code in varchar2,
1820 template_language in varchar2,
1821 template_territory in varchar2,
1822 output_format in varchar2) return boolean is
1823
1824
1825 begin
1826 -- It is callers responsibility to provide valid values.
1827 P_TEMPLATE_APPL := template_appl_name;
1828 P_TEMPLATE_CODE := template_code;
1829 P_TEMPLATE_LANG := template_language;
1830 P_TEMPLATE_TERR := template_territory;
1831 P_OUTPUT_FORMAT := output_format;
1832 return (TRUE);
1833
1834 end;
1835
1836 -- Bug5680619 & 5680669
1837 -- Name
1838 -- justify_program
1839 -- Purpose
1840 -- It lists all the disabled program in request set
1841 -- Call this function at the first step of the submission of a concurrent
1842 -- request set transaction.
1843 -- It returns a string containing all disabled program name based on
1844 -- the criticality
1845 -- Arguments
1846 -- template_appl_name - Template application short name
1847 -- template_request_set_name - Template Request Set Name
1848
1849 function justify_program(template_appl_name in varchar2,
1850 template_request_set_name in varchar2)
1851 return varchar2 is
1852 cursor program_cursor is
1853 select fcp.concurrent_program_name, frsp.critical
1854 FROM fnd_request_set_programs frsp,
1855 fnd_concurrent_programs_vl fcp,
1856 fnd_request_sets_vl frs,
1857 fnd_application fa
1858 WHERE fa.application_short_name = template_appl_name
1859 AND fa.application_id = frs.application_id
1860 AND frs.request_set_name = template_request_set_name
1861 AND frs.request_set_id = frsp.request_set_id
1862 AND frs.application_id = frsp.set_application_id
1863 AND frsp.program_application_id = fcp.application_id
1864 AND frsp.concurrent_program_id = fcp.concurrent_program_id
1865 AND fcp.srs_flag IN('Y', 'Q')
1866 AND fcp.enabled_flag = 'N'
1867 AND fcp.request_set_flag = 'N';
1868
1869 err_buf varchar2(240) default('E');
1870 warn_buf varchar2(240) default('W');
1871 err_flag varchar2(1) default('N');
1872 warn_flag varchar2(1) default('N');
1873 begin
1874 for i in program_cursor
1875 loop
1876 if i.critical = 'Y' then
1877 err_buf := err_buf||','||i.concurrent_program_name;
1878 err_flag := 'Y';
1879 else
1880 warn_buf := warn_buf||','||i.concurrent_program_name;
1881 warn_flag := 'Y';
1882 end if;
1883 end loop;
1884 if err_flag = 'Y' then
1885 return err_buf;
1886 elsif warn_flag = 'Y' then
1887 return warn_buf;
1888 else
1889 return null;
1890 end if;
1891 end;
1892
1893 end FND_SUBMIT;
[Home] [Help]