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