[Home] [Help]
PACKAGE BODY: APPS.FND_CONC_SSWA
Source
1 package body FND_CONC_SSWA as
2 /* $Header: AFCPSSUB.pls 120.5 2008/06/13 15:35:38 ddhulla ship $ */
3
4
5 --
6 -- Package
7 -- FND_CONC_SSWA
8 -- Purpose
9 -- Utilities for the Concurrent SelfService Web Applications
10 -- History
11 --
12 -- PRIVATE VARIABLES
13 --
14
15 -- Global request_id for use in multiple calls
16 g_request_id number;
17
18 -- Global argument counter
19 g_arg_count number;
20
21 TYPE map_record_type is record
22 ( attributeno number,
23 enabled varchar2(1),
24 argument varchar2(240)
25 );
26
27 TYPE map_tab_type is table of map_record_type
28 index by binary_integer;
29
30 attr_to_arg map_tab_type;
31
32 req_phase varchar2(80);
33 req_status varchar2(80);
34 req_status_code varchar2(1);
35 ran_get_phase number := -1;
36 ran_get_status number := -1;
37
38 TYPE args_array IS varray(100) OF VARCHAR2(240);
39
40
41 -- Exceptions
42
43 -- Exception Pragmas
44
45 --
46 -- Name
47 -- map_attr_to_arg
48 -- Purpose
49 -- Maps the application column name attribute in fnd_concurrent requests
50 -- to the enabled arguments of the program's desc flexfield
51
52
53
54 function map_attr_to_arg(attrno in number,
55 reqid in number) return varchar2 is
56 app_id number;
57 has_args varchar2(1);
58 attr_counter number := 1;
59 arg_counter number;
60 max_attr number;
61 last_attr_disabled number := 0;
62 prog_short_name varchar(30);
63 loop_count number := 0;
64 flex_name varchar2(40);
65
66 args args_array := args_array(CHR(0), CHR(0), CHR(0), CHR(0),
67 CHR(0), CHR(0), CHR(0), CHR(0),
68 CHR(0), CHR(0), CHR(0), CHR(0),
69 CHR(0), CHR(0), CHR(0), CHR(0),
70 CHR(0), CHR(0), CHR(0), CHR(0),
71 CHR(0), CHR(0), CHR(0), CHR(0),
72 CHR(0), CHR(0), CHR(0), CHR(0),
73 CHR(0), CHR(0), CHR(0), CHR(0),
74 CHR(0), CHR(0), CHR(0), CHR(0),
75 CHR(0), CHR(0), CHR(0), CHR(0),
76 CHR(0), CHR(0), CHR(0), CHR(0),
77 CHR(0), CHR(0), CHR(0), CHR(0),
78 CHR(0), CHR(0), CHR(0), CHR(0),
79 CHR(0), CHR(0), CHR(0), CHR(0),
80 CHR(0), CHR(0), CHR(0), CHR(0),
81 CHR(0), CHR(0), CHR(0), CHR(0),
82 CHR(0), CHR(0), CHR(0), CHR(0),
83 CHR(0), CHR(0), CHR(0), CHR(0),
84 CHR(0), CHR(0), CHR(0), CHR(0),
85 CHR(0), CHR(0), CHR(0), CHR(0),
86 CHR(0), CHR(0), CHR(0), CHR(0),
87 CHR(0), CHR(0), CHR(0), CHR(0),
88 CHR(0), CHR(0), CHR(0), CHR(0),
89 CHR(0), CHR(0), CHR(0), CHR(0),
90 CHR(0), CHR(0), CHR(0), CHR(0));
91 cursor c_attrs is
92 select to_number(substr(application_column_name,10)) ,enabled_flag
93 from fnd_descr_flex_column_usages
94 where application_id = app_id
95 and descriptive_flexfield_name = flex_name
96 order by column_seq_num;
97 begin
98
99
100 if (attrno > 100) then
101 return '';
102 end if;
103
104 -- the argument is already calculated
105 if (g_request_id is NOT NULL and g_request_id = reqid) then
106 return attr_to_arg(attrno).argument;
107 end if;
108
109 -- set g_request_id
110 g_request_id := reqid;
111
112 -- check if there are more than 25 argument
113 select count(*) into g_arg_count
114 from fnd_conc_request_arguments
115 where request_id = reqid;
116
117 -- clear global array
118 attr_counter := 1;
119
120 loop_count := attr_to_arg.COUNT;
121 while (attr_counter <= loop_count) loop
122 attr_to_arg.delete(attr_counter);
123 attr_counter := attr_counter + 1;
124 end loop;
125
126 select r.program_application_id , p.concurrent_program_name
127 into app_id, prog_short_name
128 from fnd_concurrent_requests r, fnd_concurrent_programs p
129 where r.request_id = reqid
130 and r.concurrent_program_id = p.concurrent_program_id
131 and r.program_application_id = p.application_id;
132
133 -- if program has no arguments
134 has_args := program_has_args(prog_short_name, app_id);
135 if (has_args = 'N') then
136 return '';
137 end if;
138
139 attr_counter := 1;
140
141 flex_name := '$SRS$.' || prog_short_name;
142
143 if (attr_to_arg.COUNT = 0 OR attr_to_arg.COUNT is null) then
144 open c_attrs;
145 loop
146 fetch c_attrs
147 into attr_to_arg(attr_counter).attributeno,
148 attr_to_arg(attr_counter).enabled;
149 exit when (c_attrs%NOTFOUND
150 or (c_attrs%NOTFOUND is null)
151 or (g_arg_count = 0 AND attr_counter > 24)
152 or (attr_counter > 100));
153 attr_counter := attr_counter + 1;
154 end loop;
155 close c_attrs;
156
157 max_attr := attr_counter;
158
159 -- Set enabled to N for remaining elements of the map table
160 -- up to maximum possible request arguments (24 or 100)
161 while ((g_arg_count = 0 AND attr_counter <= 24) OR (attr_counter <= 100)) loop
162 attr_to_arg(attr_counter).enabled := 'N';
163 attr_counter := attr_counter + 1;
164 end loop;
165
166 select argument1, argument2, argument3, argument4,
167 argument5, argument6, argument7, argument8,
168 argument9, argument10, argument11, argument12,
169 argument13, argument14, argument15, argument16,
170 argument17, argument18, argument19, argument20,
171 argument21, argument22, argument23, argument24,
172 argument25
173 into args(1), args(2), args(3), args(4),
174 args(5), args(6), args(7), args(8),
175 args(9), args(10), args(11), args(12),
176 args(13), args(14), args(15), args(16),
177 args(17), args(18), args(19), args(20),
178 args(21), args(22), args(23), args(24),
179 args(25)
180 from fnd_concurrent_requests
181 where request_id = reqid;
182
183 if (g_arg_count <> 0) then
184 select argument26,argument27,argument28,
185 argument29,argument30,argument31,argument32,argument33,
186 argument34,argument35,argument36,argument37,argument38,
187 argument39,argument40,argument41,argument42,argument43,
188 argument44,argument45,argument46,argument47,argument48,
189 argument49,argument50,argument51,argument52,argument53,
190 argument54,argument55,argument56,argument57,argument58,
191 argument59,argument60,argument61,argument62,argument63,
192 argument64,argument65,argument66,argument67,argument68,
193 argument69,argument70,argument71,argument72,argument73,
194 argument74,argument75,argument76,argument77,argument78,
195 argument79,argument80,argument81,argument82,argument83,
196 argument84,argument85,argument86,argument87,argument88,
197 argument89,argument90,argument91,argument92,argument93,
198 argument94,argument95,argument96,argument97,argument98,
199 argument99,argument100
200 into args(26),args(27),args(28),args(29),args(30),
201 args(31),args(32),args(33),args(34),args(35),
202 args(36),args(37),args(38),args(39),args(40),
203 args(41),args(42),args(43),args(44),args(45),
204 args(46),args(47),args(48),args(49),args(50),
205 args(51),args(52),args(53),args(54),args(55),
206 args(56),args(57),args(58),args(59),args(60),
207 args(61),args(62),args(63),args(64),args(65),
208 args(66),args(67),args(68),args(69),args(70),
209 args(71),args(72),args(73),args(74),args(75),
210 args(76),args(77),args(78),args(79),args(80),
211 args(81),args(82),args(83),args(84),args(85),
212 args(86),args(87),args(88),args(89),args(90),
213 args(91),args(92),args(93),args(94),args(95),
214 args(96),args(97),args(98),args(99),args(100)
215 from fnd_conc_request_arguments
216 where request_id = reqid;
217 end if;
218
219 last_attr_disabled := 0;
220 arg_counter := 1;
221 loop
222 attr_counter := attr_to_arg(arg_counter).attributeno;
223 if (attr_to_arg(arg_counter).enabled = 'Y') then
224 attr_to_arg(attr_counter).argument := args(arg_counter-last_attr_disabled);
225 else
226 if (arg_counter < max_attr) then
227 -- the attrubute exits but is disabled
228 attr_to_arg(attr_counter).argument := '';
229 last_attr_disabled := last_attr_disabled+1;
230 else
231 -- the attribute doesn't exist
232 attr_to_arg(arg_counter).argument := '';
233 end if;
234 end if;
235 arg_counter := arg_counter + 1;
236 exit when ((g_arg_count = 0 AND arg_counter > 24) OR (arg_counter > 100));
237 end loop;
238 end if;
239 return attr_to_arg(attrno).argument;
240 end;
241
242
243
244 --
245 -- Name
246 -- get_phase_and_status
247 -- Purpose
248 -- Used by get_phase and get_status to get the
249 -- phase and status descriptions.
250 --
251 procedure get_phase_and_status(pcode in char,
252 scode in char,
253 hold in char,
254 enbld in char,
255 stdate in date,
256 rid in number) is
257 upcode varchar2(1);
258 uscode varchar2(1);
259 ltype varchar2(16);
260 begin
261
262 upcode := pcode;
263 uscode := scode;
264
265 if ((pcode = 'P') and (hold = 'Y')) then
266 upcode := 'I';
267 uscode := 'H';
268 elsif ((pcode = 'P') and (enbld = 'N')) then
269 upcode := 'I';
270 uscode := 'U';
271 elsif ((pcode = 'P') and (scode = 'A')) then
272 upcode := 'P';
273 uscode := 'A';
274 elsif (pcode = 'P') then
275 if ((stdate > SYSDATE) or (scode = 'P')) then
276 upcode := 'P';
277 uscode := 'P';
278 else
279 select 'I',
280 'M'
281 into upcode,
282 uscode
283 from sys.dual
284 where not exists (select null
285 from fnd_concurrent_worker_requests
286 where request_id = rid
287 and running_processes > 0
288 and (not (queue_application_id = 0
289 and concurrent_queue_id in (1,4))
290 or queue_control_flag = 'Y'));
291 end if;
292 end if;
293
294 raise no_data_found;
295
296 exception
297 when no_data_found then
298 -- Bug 7021067. Support Inactive Phase for the request.
299 -- if ( upcode = 'I' ) then
300 -- upcode := 'P';
301 -- end if;
302
303 ltype := 'CP_PHASE_CODE';
304 select meaning into req_phase
305 from fnd_lookups
306 where lookup_code = upcode
307 and lookup_type = ltype;
308
309 -- Bug 7021067. Support all the request statues
310 -- if ( uscode in ('R','I','Z','C','W','B','P','Q','A') ) then
311 -- req_status_code := 'C';
312 -- elsif (uscode in ('D','U','M','S','T','G') ) then
313 -- req_status_code := 'G';
314 -- elsif (uscode in ('H') ) then
315 -- req_status_code :='H';
316 -- else
317 -- req_status_code := 'E';
318 -- end if;
319 req_status_code := uscode;
320
321 ltype := 'CP_STATUS_CODE';
322 select meaning into req_status
323 from fnd_lookups
324 where lookup_code = req_status_code
325 and lookup_type = ltype;
326
327 end get_phase_and_status;
328
329 --
330 -- Name
331 -- get_phase
332 -- Purpose
333 -- Returns a translated phase description.
334 --
335 function get_phase (pcode in char,
336 scode in char,
337 hold in char,
338 enbld in char,
339 stdate in date,
340 rid in number) return varchar2 is
341
342 begin
343
344
345 /* Did we already run get_status for this request?
346 * If so, then return the cached phase value.
347 */
348 if (ran_get_status = rid) then
349 ran_get_status := -1;
350 return req_phase;
351 end if;
352
353 /* Get phase and status. Return phase. */
354 get_phase_and_status(pcode, scode, hold, enbld, stdate, rid);
355 ran_get_phase := rid;
356
357 return req_phase;
358
359 exception
360 when others then
361 return 'ORA'||SQLCODE;
362 end;
363
364
365
366 --
367 -- Name
368 -- get_sswa_status
369 -- Purpose
370 -- Returns status code as 'C', 'W','E'.
371 --
372 function get_sswa_status (pcode in char,
373 scode in char,
374 hold in char,
375 enbld in char,
376 stdate in date,
377 rid in number) return varchar2 is
378 begin
379 /* Did we already run get_phase for this request?
380 * If so, then return the cached status value.
381 */
382 if (ran_get_phase = rid) then
383 ran_get_phase := -1;
384 return req_status_code;
385 end if;
386
387 /* Get phase and status. Return status. */
388 get_phase_and_status(pcode, scode, hold, enbld, stdate, rid);
389 ran_get_status := rid;
390 return req_status_code;
391
392 exception
393 when others then
394 return 'ORA'||SQLCODE;
395 end;
396
397 --
398 -- Name
399 -- get_status
400 -- Purpose
401 -- Returns status description for sswa.
402 --
403 function get_status (pcode in char,
404 scode in char,
405 hold in char,
406 enbld in char,
407 stdate in date,
408 rid in number) return varchar2 is
409 begin
410 /* Did we already run get_phase for this request?
411 * If so, then return the cached status value.
412 */
413 if (ran_get_phase = rid) then
414 ran_get_phase := -1;
415 return req_status;
416 end if;
417
418 /* Get phase and status. Return status. */
419 get_phase_and_status(pcode, scode, hold, enbld, stdate, rid);
420 ran_get_status := rid;
421 return req_status;
422
423 exception
424 when others then
425 return 'ORA'||SQLCODE;
426 end;
427
428 -- private function to get the short schedule description
429 -- This function is copied from FNDRSRUN form
430
431 function build_short_schedule (schedule_type varchar2,
432 schedule_name varchar2,
433 date1 date,
434 date2 date,
435 class_info varchar2,
436 req_id number
437 ) return varchar2 is
438 a varchar2(2000) := null;
439 my_schedule_name varchar2(80);
440 interval number := null;
441 int_unit varchar2(30) := null;
442 cnt number;
443 ltype varchar2(32);
444
445 -- variables used for determining the description for advance scheduling
446 weekday_spec boolean := false; -- if true weekday is specified in map
447 date_spec boolean := false; -- if true date is specified in map
448 month_map Varchar2(12) ; -- month bit fields
449 date_map Varchar2(32) ; -- date bit fields
450 weekday_map Varchar2(7) ; -- week days bit fields
451 weekno_map Varchar2(5) ; -- week no bit fields
452 month_msg Varchar2(128) ; -- message for month part
453 date_msg Varchar2(128) ; -- message for date part
454 weekday_msg Varchar2(128) ; -- message for week days part
455 added boolean := false; -- used across no of loops to fill comma in message string
456
457 TYPE temp_record_type is record
458 (
459 meaning varchar2(80)
460 );
461 temp_record temp_record_type;
462 rec_count number;
463 TYPE month_array IS varray(12) OF fnd_lookup_values.meaning%TYPE;
464 TYPE week_array IS varray(6) OF fnd_lookup_values.meaning%TYPE;
465 TYPE weekday_array IS varray(7) OF fnd_lookup_values.meaning%TYPE;
466 -- months month_array := month_array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
467 -- weeks week_array := week_array('First ', 'Second ', 'Third ', 'Fourth ', 'Last ');
468 -- weekdays weekday_array := weekday_array('Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat');
469
470 -- Initialize these arrays
471 months month_array := month_array(CHR(0), CHR(0), CHR(0), CHR(0), CHR(0),CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0));
472 weeks week_array := week_array(CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0));
473 weekdays weekday_array := weekday_array(CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0));
474
475 begin
476
477 -- get values for weeks array from lookup FND_SCH_WEEKDAY_TYPE
478 rec_count := 1;
479 ltype := 'FND_SCH_WEEKDAY_TYPE';
480 FOR temp_record IN (SELECT meaning
481 FROM fnd_lookup_values_vl
482 WHERE lookup_type = ltype
483 ORDER BY to_number(lookup_code)) LOOP
484 weeks(rec_count) := temp_record.meaning;
485 rec_count := rec_count + 1;
486 END LOOP;
487
488 -- get values for weekdays array from lookup FND_SCH_WEEK_DAYS
489 rec_count := 1;
490 ltype := 'FND_SCH_WEEK_DAYS';
491 FOR temp_record IN (SELECT meaning
492 FROM fnd_lookup_values_vl
493 WHERE lookup_type = ltype
494 ORDER BY to_number(lookup_code)) LOOP
495 weekdays(rec_count) := temp_record.meaning;
496 rec_count := rec_count + 1;
497 END LOOP;
498
499 -- get values for months array from lookup FND_SCH_MONTHS
500 rec_count := 1;
501 ltype := 'FND_SCH_MONTHS';
502 FOR temp_record IN (SELECT meaning
503 FROM fnd_lookup_values_vl
504 WHERE lookup_type = ltype
505 ORDER BY to_number(lookup_code)) LOOP
506 months(rec_count) := temp_record.meaning;
507 rec_count := rec_count + 1;
508 END LOOP;
509
510 my_schedule_name := schedule_name;
511
512
513 /* If this is a temp schedule, erase the name */
514 SELECT COUNT(*)
515 into cnt
516 from FND_CONC_RELEASE_CLASSES
517 WHERE OWNER_REQ_ID is not null
518 AND RELEASE_CLASS_NAME = schedule_name
519 AND rownum < 2;
520
521 if cnt>0 then
522 my_schedule_name := null;
523 end if;
524
525 if (schedule_type in ('A', 'O')) then
526 fnd_message.set_name('FND', 'SCH-NO RECURRENCE');
527 a := fnd_message.get;
528 elsif schedule_type = 'P' then
529 if my_schedule_name is null then
530 select resubmit_interval, resubmit_interval_unit_code
531 into interval, int_unit
532 from fnd_concurrent_requests
533 where request_id = req_id;
534 if date2 is null then
535 fnd_message.set_name('FND','SCH-PERIODIC NO END DATE');
536 fnd_message.set_token('UNIT_INTERVAL', to_char(interval));
537 fnd_message.set_token('UNIT_TYPE', int_unit );
538 else
539 fnd_message.set_name('FND','SCH-PERIODIC WITH END DATE');
540 fnd_message.set_token('UNIT_INTERVAL', to_char(interval));
541 fnd_message.set_token('UNIT_TYPE', int_unit );
542 fnd_message.set_token('END_DATE',to_char(date2));
543 end if;
544 else
545 fnd_message.set_name('FND','SCH-PERIODIC');
546 fnd_message.set_token('SCH_NAME', my_schedule_name);
547 end if;
548 a := fnd_message.get;
549 elsif schedule_type = 'S' then
550 begin -- schedule_type = 'S'
551 -- either 39 char map or 56 char map
552 if (LENGTH(class_info) = 39) then
553 if my_schedule_name is null then
554 if date2 is null then
555 fnd_message.set_name('FND','SCH-SPECIFIC START');
556 fnd_message.set_token('DATE',to_char(date1));
557 else
558 fnd_message.set_name('FND','SCH-SPECIFIC RANGE');
559 fnd_message.set_token('DATE1',to_char(date1));
560 fnd_message.set_token('DATE2',to_char(date2));
561 end if;
562 else
563 fnd_message.set_name('FND','SCH-SPECIFIC');
564 fnd_message.set_token('SCH_NAME', my_schedule_name);
565 end if;
566 a := fnd_message.get || ': ';
567
568 for cnt in 1..39 loop
569 if substr(class_info, cnt, 1) = '1' then
570 a := a || to_char(cnt) || ' ';
571 end if;
572 end loop;
573 else -- handle advance schedule of 56 char map
574 month_map := SUBSTR(class_info,45);
575 date_map := SUBSTR(class_info,1,32);
576 weekday_map := SUBSTR(class_info,33,7);
577 weekno_map := SUBSTR(class_info,40,5);
578
579 if (month_map = '111111111111') then
580 -- all months specified
581 month_msg := month_msg || 'All Months';
582 else
583 for cnt in 1..12 loop
584 if ((SUBSTR(month_map, cnt,1) = '1')) then
585 if (added = true) then
586 month_msg := month_msg || ',';
587 end if;
588
589 month_msg := month_msg || months(cnt);
590 added := true;
591 end if;
592 end loop;
593 end if;
594
595 if (INSTR(date_map,'1') <> 0) then
596 date_spec := true;
597 added := false;
598 for cnt in 1..31 loop
599 if (SUBSTR(date_map,cnt,1) = '1') then
600 if (added = true) then
601 date_msg := date_msg || ',';
602 end if;
603
604 date_msg := date_msg || TO_CHAR(cnt);
605 added := true;
606 end if;
607 end loop;
608
609 if (SUBSTR(date_map,32) = '1') then
610 if (added = true) then
611 date_msg := date_msg || ',';
612 end if;
613
614 date_msg := date_msg || 'Last day of month';
615 end if;
616 end if;
617
618 if (INSTR(weekday_map,'1') <> 0) then
619 weekday_spec := true;
620 added := false;
621
622 /*
623 * need not to add every as the message already contains Every..
624 * Add only if its specific week days like first/third Mon,Tue
625 if (weekno_map = '11111') then
626 weekday_msg := weekday_msg || 'Every ';
627 else
628 */
629 if (weekno_map <> '11111') then
630 -- insert selected weeks only
631 for cnt in 1..5 loop
632 if (SUBSTR(weekno_map, cnt,1) = '1') then
633 weekday_msg := weekday_msg || weeks(cnt) || ' ';
634 end if;
635 end loop;
636 end if;
637
638 -- set the week days Sun/Mon etc
639 for cnt in 1..7 loop
640 if (SUBSTR(weekday_map,cnt,1) = '1') then
641 if (added = true) then
642 weekday_msg := weekday_msg || ',';
643 end if;
644
645 weekday_msg := weekday_msg || weekdays(cnt);
646 added := true;
647 end if;
648 end loop;
649 end if;
650
651 if (date_spec = true AND weekday_spec = true) then
652 begin
653 -- both days and date are specified.. use DAD (date and day) messages
654
655 if (date2 IS null) then
656 fnd_message.set_name('FND','SCH-ADV-START-DAD');
657 fnd_message.set_token('DATES', date_msg);
658 fnd_message.set_token('MONTHS', month_msg);
659 fnd_message.set_token('DAYS', weekday_msg);
660 else
661 fnd_message.set_name('FND','SCH-ADV-RANGE-DAD');
662 fnd_message.set_token('DATES', date_msg);
663 fnd_message.set_token('MONTHS', month_msg);
664 fnd_message.set_token('DAYS', weekday_msg);
665 fnd_message.set_token('EDATE', to_char(date2));
666 end if;
667 end;
668 else
669 begin
670 -- either days and date are specified.. use DOD (date or day) messages
671 if (date2 IS null) then
672 fnd_message.set_name('FND','SCH-ADV-START-DOD');
673
674 if (date_spec = true) then
675 fnd_message.set_token('DOD', date_msg);
676 else
677 fnd_message.set_token('DOD', weekday_msg);
678 end if;
679
680 fnd_message.set_token('MONTHS', month_msg);
681
682 else
683 fnd_message.set_name('FND','SCH-ADV-RANGE-DOD');
684
685 if (date_spec = true) then
686 fnd_message.set_token('DOD', date_msg);
687 else
688 fnd_message.set_token('DOD', weekday_msg);
689 end if;
690
691 fnd_message.set_token('MONTHS', month_msg);
692 fnd_message.set_token('EDATE', to_char(date2));
693
694 end if;
695 end;
696 end if;
697 a := fnd_message.get;
698 end if; -- handle advance schedule of 56 char map
699
700 end; -- schedule_type = 'S'
701
702 else -- must be 'X'
703 fnd_message.set_name('FND','SCH-ADVANCED');
704 fnd_message.set_token('SCH_NAME', my_schedule_name);
705 a := fnd_message.get;
706 end if;
707 return substrb(a, 1, 80);
708 end build_short_schedule;
709
710
711 -- FUnction will return schedule description based on the schedule type
712 -- This function is copied from FNDRSRUN form
713
714 function get_sch_desc( request_id IN number) return varchar2 is
715 l_sch_id number;
716 l_sch_exists number;
717 l_sch_perm number;
718 l_sch_owner_req_id number;
719 l_requested_start_date date;
720 l_request_date date;
721 l_sch_date1 date;
722 l_sch_date2 date;
723 l_sch_type varchar2(1);
724 l_displayed_schedule varchar2(80);
725 l_sch_name varchar2(20);
726 l_sch_curr_values varchar2(80);
727 begin
728 select sch_id, sch_exists, sch_perm, sch_owner_req_id,
729 requested_start_date, request_date, sch_date1, sch_date2, sch_name,
730 sch_curr_values, sch_type
731 into l_sch_id, l_sch_exists, l_sch_perm, l_sch_owner_req_id,
732 l_requested_start_date, l_request_date, l_sch_date1, l_sch_date2,
733 l_sch_name, l_sch_curr_values, l_sch_type
734 from fnd_conc_requests_form_v
735 where request_id = get_sch_desc.request_id;
736
737
738
739 IF (l_sch_id is null) then
740 l_sch_exists := 1;
741 l_sch_perm := 0;
742 l_sch_owner_req_id := get_sch_desc.request_id;
743 if (l_requested_start_date <> l_request_date) then
744 l_sch_date1 := l_requested_start_date;
745 l_sch_type := 'O';
746 else
747 l_sch_type := 'A';
748 end if;
749 end if;
750
751 IF (l_sch_owner_req_id is null) then
752
753 l_displayed_schedule :=
754 build_short_schedule(l_sch_type,
755 l_sch_name,
756 l_sch_date1,
757 l_sch_date2,
758 l_sch_curr_values,
759 request_id);
760 else /* temp schedule - don't show bogus name */
761 l_displayed_schedule :=
762 build_short_schedule(l_sch_type,
763 NULL,
764 l_sch_date1,
765 l_sch_date2,
766 l_sch_curr_values,
767 request_id);
768 end if;
769
770 return l_displayed_schedule;
771 end;
772
773 -- function will return program has arguments or not.
774 -- It will return 'Y'/'N'
775 function program_has_args(program_name in varchar2,
776 program_appl_id in number) return varchar2 is
777
778 i number := 0;
779 has_orgs varchar2(1) := 'N';
780 begin
781
782 select count(*) into i
783 from fnd_descr_flex_column_usages
784 where application_id = program_appl_id
785 and descriptive_flexfield_name = '$SRS$.' || program_name
786 and descriptive_flex_context_code = 'Global Data Elements'
787 and enabled_flag = 'Y'
788 and display_flag = 'Y';
789
790 if (i > 0) then
791 has_orgs := 'Y';
792 else
793 has_orgs := 'N';
794 end if;
795
796 return has_orgs;
797
798 end;
799
800 -- function will return elapsed time between two times in 'HH24:MI:SS' format
801 -- First argument should be later time
802 -- It returns varchar2
803 function elapsed_time (end_time in date,
804 begin_time in date) return varchar2 is
805
806 e_time varchar2(12) := '';
807 begin
808
809 if (end_time is null) then
810 return '';
811 end if;
812
813 select to_char(trunc(sysdate) + (end_time - begin_time), 'HH24:MI:SS')
814 into e_time
815 from sys.dual;
816
817 return e_time;
818
819 end;
820
821 -- function will return notification list as concatinated string
822 -- It returns varchar2(2000)
823 function get_notifications(request_id in number) return varchar2 is
824
825 cursor notifications(req_id number) is
826 select substrb(display_name,1,80) dname
827 from fnd_conc_pp_actions pp,
828 wf_roles wf
829 where pp.concurrent_request_id = req_id
830 and pp.action_type = 2
831 and wf.orig_system_id = pp.orig_system_id
832 and wf.orig_system = pp.orig_system
833 and wf.name = pp.arguments
834 order by sequence;
835
836 notify_string varchar(2000) := null;
837 begin
838
839 for rec in notifications(request_id) loop
840 if (notify_string is null ) then
841 notify_string := rec.dname;
842 else
843 notify_string := notify_string || ',' || rec.dname;
844 end if;
845 exit when nvl(lengthb(notify_string),0 ) = 2000;
846 end loop;
847
848 return notify_string;
849
850 exception
851 when others then
852 return null;
853 end;
854
855 -- This function will return request diagnostics for a given request_id.
856 -- This is a wrapper on top of fnd_conc.diagnose procedure.
857
858 function diagnostics( request_id IN number ) return varchar2 is
859 phase varchar2(80);
860 status varchar2(80);
861 diag varchar2(2000);
862 begin
863 fnd_conc.diagnose(request_id, phase, status, diag );
864
865 return diag;
866 end;
867
868 -- This function will return Y/N based on the request outfile information
869 -- and request status.
870
871 function get_ofile_status(req_id IN number) return varchar2 is
872 lp_code varchar2(1);
873 lof_name varchar2(255);
874 lof_size number;
875 lsave_of varchar2(1);
876 begin
877
878 begin
879
880 select * into lp_code, lof_name, lof_size, lsave_of from
881 (select R.phase_code,
882 decode(nvl(A.Action_type,0), 6, O.file_name, R.outfile_name),
883 decode(nvl(A.action_type,0), 6, O.file_size, R.ofile_size),
884 R.save_output_flag
885 from fnd_concurrent_requests R,
886 fnd_conc_pp_actions A,
887 fnd_conc_req_outputs O
888 where R.request_id = A.concurrent_request_id (+)
889 and R.request_id = O.concurrent_request_id (+)
890 and R.request_id = req_id
891 order by A.action_type desc)
892 where rownum=1;
893
894 exception
895 when no_data_found then
896 return 'N';
897 end;
898
899 if ( lp_code in ('P','I') ) then
900 return 'N';
901 end if;
902
903 if ( lsave_of = 'Y' ) then
904 if ( lof_name is null ) then
905 return 'N';
906 end if;
907
908 if ( lof_size is null ) then
909 return 'N';
910 end if;
911
912 if ( lof_size = 0 ) then
913 return 'N';
914 end if;
915 else
916 return 'N';
917 end if;
918
919 return 'Y';
920
921 end;
922
923
924 -- AFCPSSUB.pls
925 function test_advance_sch (class_info varchar2, edate date) return varchar2 is
926 x varchar2(100);
927 begin
928 x := build_short_schedule('S', '', sysdate, edate, class_info, -1);
929 return x;
930 end;
931
932
933 --
934 -- Name
935 -- layout_enabled
936 -- Purpose
937 -- Returns true if program contains any data definition in xml publisher
938 -- schema.
939 -- Arguments
940 -- ProgramApplName - Concurrent Program Application Short Name
941 -- ProgramShortName - Concurrent Program Short Name
942 --
943 function layout_enabled ( ProgramApplName varchar2,
944 ProgramShortName varchar2) return boolean is
945 sqlstmt varchar2(1000) := 'select count(*) from ' ||
946 ' xdo_templates_vl T, fnd_concurrent_programs P, ' ||
947 ' fnd_application A ' ||
948 ' where DS_APP_SHORT_NAME= :1 and data_source_code= :2 ' ||
949 ' and template_status = ''E'' and sysdate between ' ||
950 ' start_date and nvl(end_date, sysdate) ' ||
951 ' and P.concurrent_program_name= T.data_source_code ' ||
952 ' and A.application_short_name = T.DS_APP_SHORT_NAME ' ||
953 ' and P.application_id = A.application_id ' ||
954 ' and P.output_file_type = ''XML''';
955 tablenotfound exception;
956 PRAGMA EXCEPTION_INIT(TableNotFound, -942);
957 cnt number := 0;
958 begin
959 execute immediate sqlstmt into cnt using ProgramApplName, ProgramShortName;
960
961 if ( cnt > 0 ) then
962 return TRUE;
963 else
964 return FALSE;
965 end if;
966
967 exception
968 when TableNotFound then
969 return FALSE;
970 when no_data_found then
971 return FALSE;
972
973 end;
974
975 --
976 -- Name
977 -- publisher_installed
978 -- Purpose
979 -- Returns true if xml publisher installed otherwise false
980 -- Arguments
981 --
982 function publisher_installed return boolean is
983 tablenotfound exception;
984 PRAGMA EXCEPTION_INIT(TableNotFound, -942);
985 cnt number := 0;
986 begin
987 execute immediate 'select count(*) from xdo_templates_vl' into cnt;
988
989 return TRUE;
990
991 exception
992 when TableNotFound then
993 return FALSE;
994
995 end;
996
997 end FND_CONC_SSWA;