[Home] [Help]
PACKAGE BODY: APPS.FND_CONC_SSWA
Source
1 package body FND_CONC_SSWA as
2 /* $Header: AFCPSSUB.pls 120.7.12020000.2 2012/11/07 22:01:31 ckclark 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;
67 CHR(0), CHR(0), CHR(0), CHR(0),
64 flex_name varchar2(40);
65
66 args args_array := args_array(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,
194 argument74,argument75,argument76,argument77,argument78,
191 argument59,argument60,argument61,argument62,argument63,
192 argument64,argument65,argument66,argument67,argument68,
193 argument69,argument70,argument71,argument72,argument73,
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
259 begin
260
261 fnd_conc_request_pkg.get_phase_status(pcode, scode, hold, enbld,
262 null, stdate, rid,
263 req_phase, req_status,
264 upcode, req_status_code);
265
266 end get_phase_and_status;
267
268 --
269 -- Name
270 -- get_phase
271 -- Purpose
272 -- Returns a translated phase description.
273 --
274 function get_phase (pcode in char,
275 scode in char,
276 hold in char,
277 enbld in char,
278 stdate in date,
279 rid in number) return varchar2 is
280
281 begin
282
283
284 /* Did we already run get_status for this request?
285 * If so, then return the cached phase value.
286 */
287 if (ran_get_status = rid) then
288 ran_get_status := -1;
289 return req_phase;
290 end if;
291
292 /* Get phase and status. Return phase. */
293 get_phase_and_status(pcode, scode, hold, enbld, stdate, rid);
294 ran_get_phase := rid;
295
296 return req_phase;
297
298 exception
299 when others then
300 return 'ORA'||SQLCODE;
301 end;
302
303
304
305 --
306 -- Name
307 -- get_sswa_status
308 -- Purpose
309 -- Returns status code as 'C', 'W','E'.
310 --
311 function get_sswa_status (pcode in char,
312 scode in char,
313 hold in char,
314 enbld in char,
315 stdate in date,
316 rid in number) return varchar2 is
317 begin
318 /* Did we already run get_phase for this request?
319 * If so, then return the cached status value.
320 */
321 if (ran_get_phase = rid) then
322 ran_get_phase := -1;
323 return req_status_code;
324 end if;
325
326 /* Get phase and status. Return status. */
327 get_phase_and_status(pcode, scode, hold, enbld, stdate, rid);
328 ran_get_status := rid;
329 return req_status_code;
330
331 exception
332 when others then
333 return 'ORA'||SQLCODE;
334 end;
335
336 --
337 -- Name
338 -- get_status
339 -- Purpose
340 -- Returns status description for sswa.
341 --
342 function get_status (pcode in char,
343 scode in char,
347 rid in number) return varchar2 is
344 hold in char,
345 enbld in char,
346 stdate in date,
348 begin
349 /* Did we already run get_phase for this request?
350 * If so, then return the cached status value.
351 */
352 if (ran_get_phase = rid) then
353 ran_get_phase := -1;
354 return req_status;
355 end if;
356
357 /* Get phase and status. Return status. */
358 get_phase_and_status(pcode, scode, hold, enbld, stdate, rid);
359 ran_get_status := rid;
360 return req_status;
361
362 exception
363 when others then
364 return 'ORA'||SQLCODE;
365 end;
366
367 -- private function to get the short schedule description
368 -- This function is copied from FNDRSRUN form
369
370 function build_short_schedule (schedule_type varchar2,
371 schedule_name varchar2,
372 date1 date,
373 date2 date,
374 class_info varchar2,
375 req_id number
376 ) return varchar2 is
377 a varchar2(2000) := null;
378 my_schedule_name varchar2(80);
379 interval number := null;
380 int_unit varchar2(30) := null;
381 cnt number;
382 ltype varchar2(32);
383
384 -- variables used for determining the description for advance scheduling
385 weekday_spec boolean := false; -- if true weekday is specified in map
386 date_spec boolean := false; -- if true date is specified in map
387 month_map Varchar2(12) ; -- month bit fields
388 date_map Varchar2(32) ; -- date bit fields
389 weekday_map Varchar2(7) ; -- week days bit fields
390 weekno_map Varchar2(5) ; -- week no bit fields
391 month_msg Varchar2(128) ; -- message for month part
392 date_msg Varchar2(128) ; -- message for date part
393 weekday_msg Varchar2(128) ; -- message for week days part
394 added boolean := false; -- used across no of loops to fill comma in message string
395
396 TYPE temp_record_type is record
397 (
398 meaning varchar2(80)
399 );
400 temp_record temp_record_type;
401 rec_count number;
402 TYPE month_array IS varray(12) OF fnd_lookup_values.meaning%TYPE;
403 TYPE week_array IS varray(6) OF fnd_lookup_values.meaning%TYPE;
404 TYPE weekday_array IS varray(7) OF fnd_lookup_values.meaning%TYPE;
405 -- months month_array := month_array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
406 -- weeks week_array := week_array('First ', 'Second ', 'Third ', 'Fourth ', 'Last ');
407 -- weekdays weekday_array := weekday_array('Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat');
408
409 -- Initialize these arrays
410 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));
411 weeks week_array := week_array(CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0));
412 weekdays weekday_array := weekday_array(CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0));
413
414 begin
415
416 -- get values for weeks array from lookup FND_SCH_WEEKDAY_TYPE
417 rec_count := 1;
418 ltype := 'FND_SCH_WEEKDAY_TYPE';
419 FOR temp_record IN (SELECT meaning
420 FROM fnd_lookup_values_vl
421 WHERE lookup_type = ltype
422 ORDER BY to_number(lookup_code)) LOOP
423 weeks(rec_count) := temp_record.meaning;
424 rec_count := rec_count + 1;
425 END LOOP;
426
427 -- get values for weekdays array from lookup FND_SCH_WEEK_DAYS
428 rec_count := 1;
429 ltype := 'FND_SCH_WEEK_DAYS';
430 FOR temp_record IN (SELECT meaning
431 FROM fnd_lookup_values_vl
432 WHERE lookup_type = ltype
433 ORDER BY to_number(lookup_code)) LOOP
434 weekdays(rec_count) := temp_record.meaning;
435 rec_count := rec_count + 1;
436 END LOOP;
437
438 -- get values for months array from lookup FND_SCH_MONTHS
439 rec_count := 1;
440 ltype := 'FND_SCH_MONTHS';
441 FOR temp_record IN (SELECT meaning
442 FROM fnd_lookup_values_vl
443 WHERE lookup_type = ltype
444 ORDER BY to_number(lookup_code)) LOOP
445 months(rec_count) := temp_record.meaning;
446 rec_count := rec_count + 1;
447 END LOOP;
448
449 my_schedule_name := schedule_name;
450
451
452 /* If this is a temp schedule, erase the name */
453 SELECT COUNT(*)
454 into cnt
455 from FND_CONC_RELEASE_CLASSES
456 WHERE OWNER_REQ_ID is not null
457 AND RELEASE_CLASS_NAME = schedule_name
458 AND rownum < 2;
459
460 if cnt>0 then
461 my_schedule_name := null;
462 end if;
463
464 if (schedule_type in ('A', 'O')) then
465 fnd_message.set_name('FND', 'SCH-NO RECURRENCE');
466 a := fnd_message.get;
467 elsif schedule_type = 'P' then
468 if my_schedule_name is null then
469 select resubmit_interval, resubmit_interval_unit_code
470 into interval, int_unit
471 from fnd_concurrent_requests
472 where request_id = req_id;
473 if date2 is null then
474 fnd_message.set_name('FND','SCH-PERIODIC NO END DATE');
475 fnd_message.set_token('UNIT_INTERVAL', to_char(interval));
476 fnd_message.set_token('UNIT_TYPE', int_unit );
477 else
478 fnd_message.set_name('FND','SCH-PERIODIC WITH END DATE');
482 end if;
479 fnd_message.set_token('UNIT_INTERVAL', to_char(interval));
480 fnd_message.set_token('UNIT_TYPE', int_unit );
481 fnd_message.set_token('END_DATE',to_char(date2));
483 else
484 fnd_message.set_name('FND','SCH-PERIODIC');
485 fnd_message.set_token('SCH_NAME', my_schedule_name);
486 end if;
487 a := fnd_message.get;
488 elsif schedule_type = 'S' then
489 begin -- schedule_type = 'S'
490 -- either 39 char map or 56 char map
491 if (LENGTH(class_info) = 39) then
492 if my_schedule_name is null then
493 if date2 is null then
494 fnd_message.set_name('FND','SCH-SPECIFIC START');
495 fnd_message.set_token('DATE',to_char(date1));
496 else
497 fnd_message.set_name('FND','SCH-SPECIFIC RANGE');
498 fnd_message.set_token('DATE1',to_char(date1));
499 fnd_message.set_token('DATE2',to_char(date2));
500 end if;
501 else
502 fnd_message.set_name('FND','SCH-SPECIFIC');
503 fnd_message.set_token('SCH_NAME', my_schedule_name);
504 end if;
505 a := fnd_message.get || ': ';
506
507 for cnt in 1..39 loop
508 if substr(class_info, cnt, 1) = '1' then
509 a := a || to_char(cnt) || ' ';
510 end if;
511 end loop;
512 else -- handle advance schedule of 56 char map
513 month_map := SUBSTR(class_info,45);
514 date_map := SUBSTR(class_info,1,32);
515 weekday_map := SUBSTR(class_info,33,7);
516 weekno_map := SUBSTR(class_info,40,5);
517
518 if (month_map = '111111111111') then
519 -- all months specified
520 month_msg := month_msg || 'All Months';
521 else
522 for cnt in 1..12 loop
523 if ((SUBSTR(month_map, cnt,1) = '1')) then
524 if (added = true) then
525 month_msg := month_msg || ',';
526 end if;
527
528 month_msg := month_msg || months(cnt);
529 added := true;
530 end if;
531 end loop;
532 end if;
533
534 if (INSTR(date_map,'1') <> 0) then
535 date_spec := true;
536 added := false;
537 for cnt in 1..31 loop
538 if (SUBSTR(date_map,cnt,1) = '1') then
539 if (added = true) then
540 date_msg := date_msg || ',';
541 end if;
542
543 date_msg := date_msg || TO_CHAR(cnt);
544 added := true;
545 end if;
546 end loop;
547
548 if (SUBSTR(date_map,32) = '1') then
549 if (added = true) then
550 date_msg := date_msg || ',';
551 end if;
552
553 date_msg := date_msg || 'Last day of month';
554 end if;
555 end if;
556
557 if (INSTR(weekday_map,'1') <> 0) then
558 weekday_spec := true;
559 added := false;
560
561 /*
562 * need not to add every as the message already contains Every..
563 * Add only if its specific week days like first/third Mon,Tue
564 if (weekno_map = '11111') then
565 weekday_msg := weekday_msg || 'Every ';
566 else
567 */
568 if (weekno_map <> '11111') then
569 -- insert selected weeks only
570 for cnt in 1..5 loop
571 if (SUBSTR(weekno_map, cnt,1) = '1') then
572 weekday_msg := weekday_msg || weeks(cnt) || ' ';
573 end if;
574 end loop;
575 end if;
576
577 -- set the week days Sun/Mon etc
578 for cnt in 1..7 loop
579 if (SUBSTR(weekday_map,cnt,1) = '1') then
580 if (added = true) then
581 weekday_msg := weekday_msg || ',';
582 end if;
583
584 weekday_msg := weekday_msg || weekdays(cnt);
585 added := true;
586 end if;
587 end loop;
588 end if;
589
590 if (date_spec = true AND weekday_spec = true) then
591 begin
592 -- both days and date are specified.. use DAD (date and day) messages
593
594 if (date2 IS null) then
595 fnd_message.set_name('FND','SCH-ADV-START-DAD');
596 fnd_message.set_token('DATES', date_msg);
597 fnd_message.set_token('MONTHS', month_msg);
598 fnd_message.set_token('DAYS', weekday_msg);
599 else
600 fnd_message.set_name('FND','SCH-ADV-RANGE-DAD');
601 fnd_message.set_token('DATES', date_msg);
602 fnd_message.set_token('MONTHS', month_msg);
603 fnd_message.set_token('DAYS', weekday_msg);
604 fnd_message.set_token('EDATE', to_char(date2));
605 end if;
606 end;
607 else
608 begin
609 -- either days and date are specified.. use DOD (date or day) messages
610 if (date2 IS null) then
611 fnd_message.set_name('FND','SCH-ADV-START-DOD');
612
613 if (date_spec = true) then
614 fnd_message.set_token('DOD', date_msg);
615 else
616 fnd_message.set_token('DOD', weekday_msg);
617 end if;
618
619 fnd_message.set_token('MONTHS', month_msg);
620
621 else
622 fnd_message.set_name('FND','SCH-ADV-RANGE-DOD');
623
624 if (date_spec = true) then
625 fnd_message.set_token('DOD', date_msg);
626 else
627 fnd_message.set_token('DOD', weekday_msg);
628 end if;
629
633 end if;
630 fnd_message.set_token('MONTHS', month_msg);
631 fnd_message.set_token('EDATE', to_char(date2));
632
634 end;
635 end if;
636 a := fnd_message.get;
637 end if; -- handle advance schedule of 56 char map
638
639 end; -- schedule_type = 'S'
640
641 else -- must be 'X'
642 fnd_message.set_name('FND','SCH-ADVANCED');
643 fnd_message.set_token('SCH_NAME', my_schedule_name);
644 a := fnd_message.get;
645 end if;
646 return substrb(a, 1, 80);
647 end build_short_schedule;
648
649
650 -- FUnction will return schedule description based on the schedule type
651 -- This function is copied from FNDRSRUN form
652
653 function get_sch_desc( request_id IN number) return varchar2 is
654 l_sch_id number;
655 l_sch_exists number;
656 l_sch_perm number;
657 l_sch_owner_req_id number;
658 l_requested_start_date date;
659 l_request_date date;
660 l_sch_date1 date;
661 l_sch_date2 date;
662 l_sch_type varchar2(1);
663 l_displayed_schedule varchar2(80);
664 l_sch_name varchar2(20);
665 l_sch_curr_values varchar2(80);
666 begin
667 select sch_id, sch_exists, sch_perm, sch_owner_req_id,
668 requested_start_date, request_date, sch_date1, sch_date2, sch_name,
669 sch_curr_values, sch_type
670 into l_sch_id, l_sch_exists, l_sch_perm, l_sch_owner_req_id,
671 l_requested_start_date, l_request_date, l_sch_date1, l_sch_date2,
672 l_sch_name, l_sch_curr_values, l_sch_type
673 from fnd_conc_requests_form_v
674 where request_id = get_sch_desc.request_id;
675
676
677
678 IF (l_sch_id is null) then
679 l_sch_exists := 1;
680 l_sch_perm := 0;
681 l_sch_owner_req_id := get_sch_desc.request_id;
682 if (l_requested_start_date <> l_request_date) then
683 l_sch_date1 := l_requested_start_date;
684 l_sch_type := 'O';
685 else
686 l_sch_type := 'A';
687 end if;
688 end if;
689
690 IF (l_sch_owner_req_id is null) then
691
692 l_displayed_schedule :=
693 build_short_schedule(l_sch_type,
694 l_sch_name,
695 l_sch_date1,
696 l_sch_date2,
697 l_sch_curr_values,
698 request_id);
699 else /* temp schedule - don't show bogus name */
700 l_displayed_schedule :=
701 build_short_schedule(l_sch_type,
702 NULL,
703 l_sch_date1,
704 l_sch_date2,
705 l_sch_curr_values,
706 request_id);
707 end if;
708
709 return l_displayed_schedule;
710 end;
711
712 -- function will return program has arguments or not.
713 -- It will return 'Y'/'N'
714 function program_has_args(program_name in varchar2,
715 program_appl_id in number) return varchar2 is
716
717 i number := 0;
718 has_orgs varchar2(1) := 'N';
719 begin
720
721 select count(*) into i
722 from fnd_descr_flex_column_usages
723 where application_id = program_appl_id
724 and descriptive_flexfield_name = '$SRS$.' || program_name
725 and descriptive_flex_context_code = 'Global Data Elements'
726 and enabled_flag = 'Y'
727 and display_flag = 'Y';
728
729 if (i > 0) then
730 has_orgs := 'Y';
731 else
732 has_orgs := 'N';
733 end if;
734
735 return has_orgs;
736
737 end;
738
739 -- function will return elapsed time between two times in 'HH24:MI:SS' format
740 -- First argument should be later time
741 -- It returns varchar2
742 function elapsed_time (end_time in date,
743 begin_time in date) return varchar2 is
744
745 e_time varchar2(12) := '';
746 begin
747
748 if (end_time is null) then
749 return '';
750 end if;
751
752 select to_char(trunc(sysdate) + (end_time - begin_time), 'HH24:MI:SS')
753 into e_time
754 from sys.dual;
755
756 return e_time;
757
758 end;
759
760 -- function will return notification list as concatinated string
761 -- It returns varchar2(2000)
762 function get_notifications(request_id in number) return varchar2 is
763
764 cursor notifications(req_id number) is
765 select substrb(display_name,1,80) dname
766 from fnd_conc_pp_actions pp,
767 wf_roles wf
768 where pp.concurrent_request_id = req_id
769 and pp.action_type = 2
770 and wf.orig_system_id = pp.orig_system_id
771 and wf.orig_system = pp.orig_system
772 and wf.name = pp.arguments
773 order by sequence;
774
775 notify_string varchar(2000) := null;
776 begin
777
778 for rec in notifications(request_id) loop
779 if (notify_string is null ) then
780 notify_string := rec.dname;
781 else
782 notify_string := notify_string || ',' || rec.dname;
783 end if;
784 exit when nvl(lengthb(notify_string),0 ) = 2000;
785 end loop;
786
787 return notify_string;
788
789 exception
793
790 when others then
791 return null;
792 end;
794 -- This function will return request diagnostics for a given request_id.
795 -- This is a wrapper on top of fnd_conc.diagnose procedure.
796
797 function diagnostics( request_id IN number ) return varchar2 is
798 phase varchar2(80);
799 status varchar2(80);
800 diag varchar2(2000);
801 begin
802 fnd_conc.diagnose(request_id, phase, status, diag );
803
804 return diag;
805 end;
806
807 -- This function will return Y/N based on the request outfile information
808 -- and request status.
809
810 function get_ofile_status(req_id IN number) return varchar2 is
811 lp_code varchar2(1);
812 lof_name varchar2(255);
813 lof_size number;
814 lsave_of varchar2(1);
815 begin
816
817 begin
818
819 select * into lp_code, lof_name, lof_size, lsave_of from
820 (select R.phase_code,
821 decode(nvl(A.Action_type,0), 6, O.file_name, R.outfile_name),
822 decode(nvl(A.action_type,0), 6, O.file_size, R.ofile_size),
823 R.save_output_flag
824 from fnd_concurrent_requests R,
825 fnd_conc_pp_actions A,
826 fnd_conc_req_outputs O
827 where R.request_id = A.concurrent_request_id (+)
828 and R.request_id = O.concurrent_request_id (+)
829 and R.request_id = req_id
830 order by A.action_type desc)
831 where rownum=1;
832
833 exception
834 when no_data_found then
835 return 'N';
836 end;
837
838 if ( lp_code in ('P','I') ) then
839 return 'N';
840 end if;
841
842 if ( lsave_of = 'Y' ) then
843 if ( lof_name is null ) then
844 return 'N';
845 end if;
846
847 if ( lof_size is null ) then
848 return 'N';
849 end if;
850
851 if ( lof_size = 0 ) then
852 return 'N';
853 end if;
854 else
855 return 'N';
856 end if;
857
858 return 'Y';
859
860 end;
861
862
863 -- AFCPSSUB.pls
864 function test_advance_sch (class_info varchar2, edate date) return varchar2 is
865 x varchar2(100);
866 begin
867 x := build_short_schedule('S', '', sysdate, edate, class_info, -1);
868 return x;
869 end;
870
871
872 --
873 -- Name
874 -- layout_enabled
875 -- Purpose
876 -- Returns true if program contains any data definition in xml publisher
877 -- schema.
878 -- Arguments
879 -- ProgramApplName - Concurrent Program Application Short Name
880 -- ProgramShortName - Concurrent Program Short Name
881 --
882 function layout_enabled ( ProgramApplName varchar2,
883 ProgramShortName varchar2) return boolean is
884 sqlstmt varchar2(1000) := 'select count(*) from ' ||
885 ' xdo_templates_vl T, fnd_concurrent_programs P, ' ||
886 ' fnd_application A , xdo_ds_definitions_vl D ' ||
887 ' where T.ds_app_short_name= :1 and T.data_source_code= :2 ' ||
888 ' and T.template_status = ''E'' ' ||
889 ' and D.data_source_status = ''E'' ' ||
890 ' and sysdate between T.start_date and nvl(T.end_date, sysdate) ' ||
891 ' and sysdate between D.start_date and nvl(D.end_date, sysdate) ' ||
892 ' and D.application_short_name = T.ds_app_short_name ' ||
893 ' and D.data_source_code = T.data_source_code ' ||
894 ' and P.concurrent_program_name= T.data_source_code ' ||
895 ' and A.application_short_name = T.ds_app_short_name ' ||
896 ' and P.application_id = A.application_id ' ||
897 ' and P.output_file_type = ''XML''';
898 tablenotfound exception;
899 PRAGMA EXCEPTION_INIT(TableNotFound, -942);
900 cnt number := 0;
901 begin
902 execute immediate sqlstmt into cnt using ProgramApplName, ProgramShortName;
903
904 if ( cnt > 0 ) then
905 return TRUE;
906 else
907 return FALSE;
908 end if;
909
910 exception
911 when TableNotFound then
912 return FALSE;
913 when no_data_found then
914 return FALSE;
915
916 end;
917
918 --
919 -- Name
920 -- layout_enabled_YN
921 -- Purpose
922 -- calls layout_enabled but returns Y or N instead of boolean
923 -- used for calling from C code
924 --
925 function layout_enabled_YN (ProgramApplName varchar2,
926 ProgramShortName varchar2) return varchar2 is
927
928 begin
929 if(layout_enabled(ProgramApplName, ProgramShortName)) then
930 return 'Y';
931 else
932 return 'N';
933 end if;
934 end;
935
936 --
937 -- Name
938 -- publisher_installed
939 -- Purpose
940 -- Returns true if xml publisher installed otherwise false
941 -- Arguments
942 --
943 function publisher_installed return boolean is
944 tablenotfound exception;
945 PRAGMA EXCEPTION_INIT(TableNotFound, -942);
946 cnt number := 0;
947 begin
948 execute immediate 'select count(*) from xdo_templates_vl' into cnt;
949
950 return TRUE;
951
952 exception
953 when TableNotFound then
954 return FALSE;
955
956 end;
957
958 end FND_CONC_SSWA;