[Home] [Help]
PACKAGE BODY: APPS.PA_ACTION_SET_UTILS
Source
1 PACKAGE BODY pa_action_set_utils AS
2 /*$Header: PARASUTB.pls 120.2 2008/05/02 13:05:38 jcgeorge ship $*/
3 --
4
5 FUNCTION get_action_set_id(p_action_set_type_code IN VARCHAR2,
6 p_object_type IN VARCHAR2,
7 p_object_id IN NUMBER) RETURN NUMBER
8 IS
9
10 l_action_set_id NUMBER;
11
12 BEGIN
13
14 SELECT action_set_id INTO l_action_set_id
15 FROM pa_action_sets
16 WHERE object_type = p_object_type
17 AND object_id = p_object_id
18 AND action_set_type_code = p_action_set_type_code
19 AND status_code IN ('NOT_STARTED', 'STARTED', 'PAUSED', 'RESUMED', 'CLOSED');
20
21 RETURN l_action_set_id;
22
23 EXCEPTION
24 WHEN NO_DATA_FOUND THEN
25 RETURN NULL;
26
27 END;
28
29
30 FUNCTION get_action_set_lines(p_action_set_id IN NUMBER)
31 RETURN action_set_lines_tbl_type
32 IS
33
34 l_action_set_lines_tbl pa_action_set_utils.action_set_lines_tbl_type;
35
36 TYPE number_tbl_type IS TABLE OF NUMBER
37 INDEX BY BINARY_INTEGER;
38
39 TYPE varchar_tbl_type IS TABLE OF VARCHAR2(2000)
40 INDEX BY BINARY_INTEGER;
41
42 l_action_set_line_id_tbl number_tbl_type;
43 l_action_set_id_tbl number_tbl_type;
44 l_action_set_line_number_tbl number_tbl_type;
45 l_status_code_tbl varchar_tbl_type;
46 l_description_tbl varchar_tbl_type;
47 l_record_version_number_tbl number_tbl_type;
48 l_action_code_tbl varchar_tbl_type;
49 l_action_attribute1_tbl varchar_tbl_type;
50 l_action_attribute2_tbl varchar_tbl_type;
51 l_action_attribute3_tbl varchar_tbl_type;
52 l_action_attribute4_tbl varchar_tbl_type;
53 l_action_attribute5_tbl varchar_tbl_type;
54 l_action_attribute6_tbl varchar_tbl_type;
55 l_action_attribute7_tbl varchar_tbl_type;
56 l_action_attribute8_tbl varchar_tbl_type;
57 l_action_attribute9_tbl varchar_tbl_type;
58 l_action_attribute10_tbl varchar_tbl_type;
59
60 BEGIN
61
62 SELECT action_set_line_id,
63 action_set_id,
64 action_set_line_number,
65 status_code,
66 description,
67 record_version_number,
68 action_code,
69 action_attribute1,
70 action_attribute2,
71 action_attribute3,
72 action_attribute4,
73 action_attribute5,
74 action_attribute6,
75 action_attribute7,
76 action_attribute8,
77 action_attribute9,
78 action_attribute10
79 BULK COLLECT INTO
80 l_action_set_line_id_tbl,
81 l_action_set_id_tbl,
82 l_action_set_line_number_tbl,
83 l_status_code_tbl,
84 l_description_tbl,
85 l_record_version_number_tbl,
86 l_action_code_tbl,
87 l_action_attribute1_tbl,
88 l_action_attribute2_tbl,
89 l_action_attribute3_tbl,
90 l_action_attribute4_tbl,
91 l_action_attribute5_tbl,
92 l_action_attribute6_tbl,
93 l_action_attribute7_tbl,
94 l_action_attribute8_tbl,
95 l_action_attribute9_tbl,
96 l_action_attribute10_tbl
97 FROM pa_action_set_lines
98 WHERE action_set_id = p_action_set_id;
99
100 IF l_action_set_line_id_tbl.COUNT > 0 THEN
101
102 FOR i IN l_action_set_line_id_tbl.FIRST .. l_action_set_line_id_tbl.LAST LOOP
103
104
105
106 l_action_set_lines_tbl(i).action_set_line_id := l_action_set_line_id_tbl(i);
107 l_action_set_lines_tbl(i).action_set_id := l_action_set_id_tbl(i);
108 l_action_set_lines_tbl(i).action_set_line_number := l_action_set_line_number_tbl(i);
109 l_action_set_lines_tbl(i).status_code := l_status_code_tbl(i);
110 l_action_set_lines_tbl(i).description := l_description_tbl(i);
111 l_action_set_lines_tbl(i).record_version_number := l_record_version_number_tbl(i);
112 l_action_set_lines_tbl(i).action_code := l_action_code_tbl(i);
113 l_action_set_lines_tbl(i).action_attribute1 := l_action_attribute1_tbl(i);
114 l_action_set_lines_tbl(i).action_attribute2 := l_action_attribute2_tbl(i);
115 l_action_set_lines_tbl(i).action_attribute3 := l_action_attribute3_tbl(i);
116 l_action_set_lines_tbl(i).action_attribute4 := l_action_attribute4_tbl(i);
117 l_action_set_lines_tbl(i).action_attribute5 := l_action_attribute5_tbl(i);
118 l_action_set_lines_tbl(i).action_attribute6 := l_action_attribute6_tbl(i);
119 l_action_set_lines_tbl(i).action_attribute7 := l_action_attribute7_tbl(i);
120 l_action_set_lines_tbl(i).action_attribute8 := l_action_attribute8_tbl(i);
121 l_action_set_lines_tbl(i).action_attribute9 := l_action_attribute9_tbl(i);
122 l_action_set_lines_tbl(i).action_attribute10 := l_action_attribute10_tbl(i);
123
124 END LOOP;
125
126 END IF;
127
128 RETURN l_action_set_lines_tbl;
129
130 END;
131
132
133 FUNCTION get_action_set_line (p_action_set_line_id IN NUMBER)
134 RETURN pa_action_set_lines%ROWTYPE
135 IS
136
137 l_action_set_lines_rec pa_action_set_lines%ROWTYPE;
138
139 BEGIN
140
141 SELECT action_set_line_id,
142 action_set_id,
143 action_set_line_number,
144 status_code,
145 description,
146 record_version_number,
147 action_code,
148 action_attribute1,
149 action_attribute2,
150 action_attribute3,
151 action_attribute4,
152 action_attribute5,
153 action_attribute6,
154 action_attribute7,
155 action_attribute8,
156 action_attribute9,
157 action_attribute10
158 INTO
159 l_action_set_lines_rec.action_set_line_id,
160 l_action_set_lines_rec.action_set_id,
161 l_action_set_lines_rec.action_set_line_number,
162 l_action_set_lines_rec.status_code,
163 l_action_set_lines_rec.description,
164 l_action_set_lines_rec.record_version_number,
165 l_action_set_lines_rec.action_code,
166 l_action_set_lines_rec.action_attribute1,
167 l_action_set_lines_rec.action_attribute2,
168 l_action_set_lines_rec.action_attribute3,
169 l_action_set_lines_rec.action_attribute4,
170 l_action_set_lines_rec.action_attribute5,
171 l_action_set_lines_rec.action_attribute6,
172 l_action_set_lines_rec.action_attribute7,
173 l_action_set_lines_rec.action_attribute8,
174 l_action_set_lines_rec.action_attribute9,
175 l_action_set_lines_rec.action_attribute10
176 FROM pa_action_set_lines
177 WHERE action_set_line_id = p_action_set_line_id;
178
179 RETURN l_action_set_lines_rec;
180
181 END;
182
183 FUNCTION get_action_set_details (p_action_set_line_id IN NUMBER)
184 RETURN pa_action_sets%ROWTYPE
185 IS
186
187 l_action_sets_rec pa_action_sets%ROWTYPE;
188
189 BEGIN
190
191 SELECT sets.action_set_id,
192 sets.action_set_name,
193 sets.action_set_type_code,
194 sets.object_type,
195 sets.object_id,
196 sets.start_date_active,
197 sets.end_date_active,
198 sets.description,
199 sets.status_code,
200 sets.actual_start_date,
201 sets.action_set_template_flag,
202 sets.source_action_set_id,
203 sets.attribute_category,
204 sets.attribute1,
205 sets.attribute2,
206 sets.attribute3,
207 sets.attribute4,
208 sets.attribute5,
209 sets.attribute6,
210 sets.attribute7,
211 sets.attribute8,
212 sets.attribute9,
213 sets.attribute10,
214 sets.attribute11,
215 sets.attribute12,
216 sets.attribute13,
217 sets.attribute14,
218 sets.attribute15
219 INTO
220 l_action_sets_rec.action_set_id,
221 l_action_sets_rec.action_set_name,
222 l_action_sets_rec.action_set_type_code,
223 l_action_sets_rec.object_type,
224 l_action_sets_rec.object_id,
225 l_action_sets_rec.start_date_active,
226 l_action_sets_rec.end_date_active,
227 l_action_sets_rec.description,
228 l_action_sets_rec.status_code,
229 l_action_sets_rec.actual_start_date,
230 l_action_sets_rec.action_set_template_flag,
231 l_action_sets_rec.source_action_set_id,
232 l_action_sets_rec.attribute_category,
233 l_action_sets_rec.attribute1,
234 l_action_sets_rec.attribute2,
235 l_action_sets_rec.attribute3,
236 l_action_sets_rec.attribute4,
237 l_action_sets_rec.attribute5,
238 l_action_sets_rec.attribute6,
239 l_action_sets_rec.attribute7,
240 l_action_sets_rec.attribute8,
241 l_action_sets_rec.attribute9,
242 l_action_sets_rec.attribute10,
243 l_action_sets_rec.attribute11,
244 l_action_sets_rec.attribute12,
245 l_action_sets_rec.attribute13,
246 l_action_sets_rec.attribute14,
247 l_action_sets_rec.attribute15
248 FROM pa_action_sets sets,
249 pa_action_set_lines lines
250 WHERE lines.action_set_line_id = p_action_set_line_id
251 AND lines.action_set_id = sets.action_set_id;
252
253 RETURN l_action_sets_rec;
254
255 END;
256
257 FUNCTION get_action_line_conditions (p_action_set_line_id IN NUMBER)
258 RETURN action_line_cond_tbl_type
259 IS
260
261 l_action_line_cond_tbl pa_action_set_utils.action_line_cond_tbl_type;
262
263 TYPE number_tbl_type IS TABLE OF NUMBER
264 INDEX BY BINARY_INTEGER;
265
266 TYPE varchar_tbl_type IS TABLE OF VARCHAR2(2000)
267 INDEX BY BINARY_INTEGER;
268
269 TYPE date_tbl_type IS TABLE OF DATE
270 INDEX BY BINARY_INTEGER;
271
272 l_action_set_line_id_tbl number_tbl_type;
273 l_action_line_cond_id_tbl number_tbl_type;
274 l_condition_date_tbl date_tbl_type;
275 l_condition_code_tbl varchar_tbl_type;
276 l_description_tbl varchar_tbl_type;
277 l_condition_attribute1_tbl varchar_tbl_type;
278 l_condition_attribute2_tbl varchar_tbl_type;
279 l_condition_attribute3_tbl varchar_tbl_type;
280 l_condition_attribute4_tbl varchar_tbl_type;
281 l_condition_attribute5_tbl varchar_tbl_type;
282 l_condition_attribute6_tbl varchar_tbl_type;
283 l_condition_attribute7_tbl varchar_tbl_type;
284 l_condition_attribute8_tbl varchar_tbl_type;
285 l_condition_attribute9_tbl varchar_tbl_type;
286 l_condition_attribute10_tbl varchar_tbl_type;
287
288 BEGIN
289
290 SELECT action_set_line_id,
291 action_set_line_condition_id,
292 description,
293 condition_date,
294 condition_code,
295 condition_attribute1,
296 condition_attribute2,
297 condition_attribute3,
298 condition_attribute4,
299 condition_attribute5,
300 condition_attribute6,
301 condition_attribute7,
302 condition_attribute8,
303 condition_attribute9,
304 condition_attribute10
305 BULK COLLECT INTO
306 l_action_set_line_id_tbl,
307 l_action_line_cond_id_tbl,
308 l_description_tbl,
309 l_condition_date_tbl,
310 l_condition_code_tbl,
311 l_condition_attribute1_tbl,
312 l_condition_attribute2_tbl,
313 l_condition_attribute3_tbl,
314 l_condition_attribute4_tbl,
315 l_condition_attribute5_tbl,
316 l_condition_attribute6_tbl,
317 l_condition_attribute7_tbl,
318 l_condition_attribute8_tbl,
319 l_condition_attribute9_tbl,
320 l_condition_attribute10_tbl
321 FROM pa_action_set_line_cond
322 WHERE action_set_line_id = p_action_set_line_id;
323
324 FOR i IN l_action_line_cond_id_tbl.FIRST .. l_action_line_cond_id_tbl.LAST LOOP
325
326 l_action_line_cond_tbl(i).action_set_line_id := l_action_set_line_id_tbl(i);
327 l_action_line_cond_tbl(i).action_set_line_condition_id := l_action_line_cond_id_tbl(i);
328 l_action_line_cond_tbl(i).description := l_description_tbl(i);
329 l_action_line_cond_tbl(i).condition_date := l_condition_date_tbl(i);
330 l_action_line_cond_tbl(i).condition_code := l_condition_code_tbl(i);
331 l_action_line_cond_tbl(i).condition_attribute1 := l_condition_attribute1_tbl(i);
332 l_action_line_cond_tbl(i).condition_attribute2 := l_condition_attribute2_tbl(i);
333 l_action_line_cond_tbl(i).condition_attribute3 := l_condition_attribute3_tbl(i);
334 l_action_line_cond_tbl(i).condition_attribute4 := l_condition_attribute4_tbl(i);
335 l_action_line_cond_tbl(i).condition_attribute5 := l_condition_attribute5_tbl(i);
336 l_action_line_cond_tbl(i).condition_attribute6 := l_condition_attribute6_tbl(i);
337 l_action_line_cond_tbl(i).condition_attribute7 := l_condition_attribute7_tbl(i);
338 l_action_line_cond_tbl(i).condition_attribute8 := l_condition_attribute8_tbl(i);
339 l_action_line_cond_tbl(i).condition_attribute9 := l_condition_attribute9_tbl(i);
340 l_action_line_cond_tbl(i).condition_attribute10 := l_condition_attribute10_tbl(i);
341
342 END LOOP;
343
344 RETURN l_action_line_cond_tbl;
345
346 END;
347
348 FUNCTION get_active_audit_lines (p_action_set_line_id IN NUMBER)
349 RETURN audit_lines_tbl_type
350 IS
351
352 l_active_audit_lines_tbl audit_lines_tbl_type;
353
354 TYPE number_tbl_type IS TABLE OF NUMBER
355 INDEX BY BINARY_INTEGER;
356
357 TYPE varchar_tbl_type IS TABLE OF VARCHAR2(2000)
358 INDEX BY BINARY_INTEGER;
359
360 TYPE date_tbl_type IS TABLE OF DATE
361 INDEX BY BINARY_INTEGER;
362
363 l_action_set_line_id_tbl number_tbl_type;
364 l_object_type_tbl varchar_tbl_type;
365 l_object_id_tbl number_tbl_type;
366 l_action_set_type_code_tbl varchar_tbl_type;
367 l_status_code_tbl varchar_tbl_type;
368 l_action_code_tbl varchar_tbl_type;
369 l_active_flag_tbl varchar_tbl_type;
370 l_reason_code_tbl varchar_tbl_type;
371 l_audit_display_attribute_tbl varchar_tbl_type;
372 l_audit_attribute_tbl varchar_tbl_type;
373 l_action_date_tbl date_tbl_type;
374 l_reversed_action_set_line_tbl number_tbl_type;
375
376 BEGIN
377
378 SELECT action_set_line_id,
379 object_type,
380 object_id,
381 action_set_type_code,
382 status_code,
383 reason_code,
384 action_code,
385 audit_display_attribute,
386 audit_attribute,
387 action_date,
388 active_flag,
389 reversed_action_set_line_id
390 BULK COLLECT INTO
391 l_action_set_line_id_tbl,
392 l_object_type_tbl,
393 l_object_id_tbl,
394 l_action_set_type_code_tbl,
395 l_status_code_tbl,
396 l_reason_code_tbl,
397 l_action_code_tbl,
398 l_audit_display_attribute_tbl,
399 l_audit_attribute_tbl,
400 l_action_date_tbl,
401 l_active_flag_tbl,
402 l_reversed_action_set_line_tbl
403 FROM pa_action_set_line_aud
404 WHERE action_set_line_id = p_action_set_line_id
405 AND active_flag = 'Y';
406
407
408 IF l_reason_code_tbl.COUNT > 0 THEN
409 FOR i IN l_reason_code_tbl.FIRST .. l_reason_code_tbl.LAST LOOP
410
411 l_active_audit_lines_tbl(i).action_set_line_id := l_action_set_line_id_tbl(i);
412 l_active_audit_lines_tbl(i).object_type := l_object_type_tbl(i);
413 l_active_audit_lines_tbl(i).object_id := l_object_id_tbl(i);
414 l_active_audit_lines_tbl(i).action_set_type_code := l_action_set_type_code_tbl(i);
415 l_active_audit_lines_tbl(i).status_code := l_status_code_tbl(i);
416 l_active_audit_lines_tbl(i).reason_code := l_reason_code_tbl(i);
417 l_active_audit_lines_tbl(i).action_code := l_action_code_tbl(i);
418 l_active_audit_lines_tbl(i).audit_display_attribute := l_audit_display_attribute_tbl(i);
419 l_active_audit_lines_tbl(i).audit_attribute := l_audit_attribute_tbl(i);
420 l_active_audit_lines_tbl(i).action_date := l_action_date_tbl(i);
421 l_active_audit_lines_tbl(i).active_flag := l_active_flag_tbl(i);
422 l_active_audit_lines_tbl(i).reversed_action_set_line_id := l_reversed_action_set_line_tbl(i);
423 END LOOP;
424 END IF;
425
426 RETURN l_active_audit_lines_tbl;
427
428 END;
429
430
431 PROCEDURE add_message(p_app_short_name IN VARCHAR2,
432 p_msg_name IN VARCHAR2,
433 p_token1 IN VARCHAR2 DEFAULT NULL,
434 p_value1 IN VARCHAR2 DEFAULT NULL,
435 p_token2 IN VARCHAR2 DEFAULT NULL,
436 p_value2 IN VARCHAR2 DEFAULT NULL,
437 p_token3 IN VARCHAR2 DEFAULT NULL,
438 p_value3 IN VARCHAR2 DEFAULT NULL,
439 p_token4 IN VARCHAR2 DEFAULT NULL,
440 p_value4 IN VARCHAR2 DEFAULT NULL,
441 p_token5 IN VARCHAR2 DEFAULT NULL,
442 p_value5 IN VARCHAR2 DEFAULT NULL ) IS
443
444 BEGIN
445
446 G_ERROR_EXISTS := 'Y';
447
448 PA_UTILS.Add_Message (p_app_short_name => p_app_short_name
449 ,p_msg_name => p_msg_name
450 ,p_token1 => p_token1
451 ,p_value1 => p_value1
452 ,p_token2 => p_token2
453 ,p_value2 => p_value2
454 ,p_token3 => p_token3
455 ,p_value3 => p_value3
456 ,p_token4 => p_token4
457 ,p_value4 => p_value4
458 ,p_token5 => p_token5
459 ,p_value5 => p_value5
460 );
461
462
463 END;
464
465 FUNCTION is_name_unique_in_type(p_action_set_type_code IN VARCHAR2,
466 p_action_set_name IN VARCHAR2,
467 p_action_set_id IN NUMBER := NULL)
468 RETURN VARCHAR2
469 IS
470
471 l_name_unique VARCHAR2(1);
472
473 CURSOR check_name_unique_in_type IS
474 SELECT 'X'
475 FROM pa_action_sets
476 WHERE action_set_name = p_action_set_name
477 AND action_set_type_code = p_action_set_type_code
478 AND action_set_template_flag = 'Y'
479 AND ((action_set_id <> p_action_set_id AND p_action_set_id IS NOT NULL)
480 OR p_action_set_id IS NULL);
481
482 BEGIN
483
484 OPEN check_name_unique_in_type;
485
486 FETCH check_name_unique_in_type into l_name_unique;
487
488 IF check_name_unique_in_type%FOUND THEN
489 CLOSE check_name_unique_in_type;
490 RETURN 'N';
491
492 ELSE
493 CLOSE check_name_unique_in_type;
494 RETURN 'Y';
495
496 END IF;
497
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 CLOSE check_name_unique_in_type;
502 RAISE;
503
504 END;
505
506
507 FUNCTION is_action_set_a_source(p_action_set_id IN NUMBER)
508 RETURN VARCHAR2
509 IS
510
511 l_is_source VARCHAR2(1);
512
513 CURSOR check_action_set_is_source IS
514 SELECT 'X'
515 FROM pa_action_sets
516 WHERE source_action_set_id = p_action_set_id;
517
518 BEGIN
519
520 OPEN check_action_set_is_source;
521
522 FETCH check_action_set_is_source into l_is_source;
523
524 IF check_action_set_is_source%FOUND THEN
525 CLOSE check_action_set_is_source;
526 RETURN 'Y';
527
528 ELSE
529 CLOSE check_action_set_is_source;
530 RETURN 'N';
531
532 END IF;
533
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 CLOSE check_action_set_is_source;
538 RAISE;
539
540 END;
541
542 FUNCTION do_lines_exist(p_action_set_id IN NUMBER)
543 RETURN VARCHAR2
544 IS
545
546 l_lines_exist VARCHAR2(1);
547
548 CURSOR do_lines_exist IS
549 SELECT 'X'
550 FROM pa_action_set_lines
551 WHERE action_set_id = p_action_set_id;
552
553 BEGIN
554
555 OPEN do_lines_exist;
556
557 FETCH do_lines_exist into l_lines_exist;
558
559 IF do_lines_exist%FOUND THEN
560 CLOSE do_lines_exist;
561 RETURN 'Y';
562
563 ELSE
564 CLOSE do_lines_exist;
565 RETURN 'N';
566
567 END IF;
568
569
570 EXCEPTION
571 WHEN OTHERS THEN
572 CLOSE do_lines_exist;
573 RAISE;
574
575 END;
576
577 FUNCTION do_audit_lines_exist(p_action_set_line_id IN NUMBER)
578 RETURN VARCHAR2
579 IS
580
581 l_audit_lines_exist VARCHAR2(1);
582
583 CURSOR do_audit_lines_exist IS
584 SELECT 'X'
585 FROM pa_action_set_line_aud
586 WHERE action_set_line_id = p_action_set_line_id;
587
588 BEGIN
589
590 OPEN do_audit_lines_exist;
591
592 FETCH do_audit_lines_exist into l_audit_lines_exist;
593
594 IF do_audit_lines_exist%FOUND THEN
595 CLOSE do_audit_lines_exist;
596 RETURN 'Y';
597
598 ELSE
599 CLOSE do_audit_lines_exist;
600 RETURN 'N';
601
602 END IF;
603
604
605 EXCEPTION
606 WHEN OTHERS THEN
607 CLOSE do_audit_lines_exist;
608 RAISE;
609
610 END;
611
612 FUNCTION get_last_performed_date(p_action_set_line_id IN NUMBER)
613 RETURN DATE
614 IS
615
616 l_action_date DATE;
617
618 BEGIN
619
620 SELECT max(trunc(action_date)) INTO l_action_date
621 FROM pa_action_set_line_aud
622 WHERE action_set_line_id = p_action_set_line_id;
623
624 RETURN l_action_date;
625
626 EXCEPTION
627
628 WHEN NO_DATA_FOUND THEN
629 RETURN NULL;
630
631 WHEN OTHERS THEN
632 RAISE;
633
634 END;
635
636 PROCEDURE Check_Action_Set_Name_Or_Id (p_action_set_id IN pa_action_sets.action_set_id%TYPE := NULL
637 ,p_action_set_name IN pa_action_sets.action_set_name%TYPE
638 ,p_action_set_type_code IN pa_action_set_types.action_set_type_code%TYPE
639 ,p_check_id_flag IN VARCHAR2
640 ,p_date IN DATE := SYSDATE
641 ,x_action_set_id OUT NOCOPY pa_action_sets.action_set_id%TYPE --File.Sql.39 bug 4440895
642 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
643 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
644 BEGIN
645 pa_debug.init_err_stack ('pa_action_set_utils.Check_Action_Set_Name_Or_Id');
646
647 IF p_action_set_id IS NOT NULL THEN
648 IF p_check_id_flag = 'Y' THEN
649 SELECT action_set_id
650 INTO x_action_set_id
651 FROM pa_action_sets
652 WHERE action_set_id = p_action_set_id
653 -- 2767129: Modified select stmt to use action_set_template_flag for
654 -- performance improvement. This is OK because the action set LOV
655 -- only displays template action sets.
656 AND action_set_template_flag = 'Y'
657 -- end of 2767129
658 AND action_set_name = p_action_set_name
659 AND action_set_type_code = p_action_set_type_code
660 AND p_date between start_date_active AND nvl(end_date_active, p_date);
661 ELSE
662 x_action_set_id := p_action_set_id;
663
664 END IF;
665 ELSE
666 SELECT action_set_id
667 INTO x_action_set_id
668 FROM pa_action_sets
669 WHERE action_set_name = p_action_set_name
670 -- 2767129: Modified select stmt to use action_set_template_flag for
671 -- performance improvement. This is OK because the action set LOV
672 -- only displays template action sets.
673 AND action_set_template_flag = 'Y'
674 -- end of 2767129
675 AND action_set_type_code = p_action_set_type_code
676 AND p_date between start_date_active AND nvl(end_date_active, p_date);
677 END IF;
678
679 x_return_status := FND_API.G_RET_STS_SUCCESS;
680
681 EXCEPTION
682 WHEN NO_DATA_FOUND THEN
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 x_error_message_code := 'PA_ACTION_SET_INVALID';
685 WHEN TOO_MANY_ROWS THEN
686 x_return_status := FND_API.G_RET_STS_ERROR;
687 x_error_message_code := 'PA_ACTION_SET_INVALID';
688 WHEN OTHERS THEN
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
690
691 END Check_Action_Set_Name_Or_Id;
692
693 PROCEDURE get_line_information_messages(x_line_numbers_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, --File.Sql.39 bug 4440895
694 x_line_messages_tbl OUT NOCOPY SYSTEM.pa_varchar2_2000_tbl_type) --File.Sql.39 bug 4440895
695 IS
696
697 BEGIN
698
699 x_line_numbers_tbl := pa_action_sets_pvt.g_line_number_msg_tbl;
700 x_line_messages_tbl := pa_action_sets_pvt.g_info_msg_tbl;
701
702 END;
703
704 END pa_action_set_utils;