[Home] [Help]
PACKAGE BODY: APPS.QLTCPPLB
Source
1 PACKAGE BODY QLTCPPLB AS
2 /* $Header: qltcpplb.plb 120.3.12010000.1 2008/07/25 09:21:39 appldev ship $ */
3
4 -- Insert rows for copying plans
5 -- 2/5/96
6 -- Jacqueline Chang
7
8 PROCEDURE insert_plan_chars (X_PLAN_ID NUMBER,
9 X_COPY_PLAN_ID NUMBER,
10 X_USER_ID NUMBER,
11 X_DISABLED_INDEXED_ELEMENTS OUT NOCOPY VARCHAR2) IS
12
13 --
14 -- Bug 3926150
15 --
16 l_disabled_indexed_elements VARCHAR2(3000);
17 l_default_column VARCHAR2(30);
18 dummy NUMBER;
19
20 CURSOR C1 IS
21 SELECT
22 PLAN_CHAR_ACTION_TRIGGER_ID,
23 LAST_UPDATE_DATE,
24 LAST_UPDATED_BY,
25 CREATION_DATE,
26 CREATED_BY,
27 TRIGGER_SEQUENCE,
28 PLAN_ID,
29 CHAR_ID,
30 OPERATOR,
31 LOW_VALUE_LOOKUP,
32 HIGH_VALUE_LOOKUP,
33 LOW_VALUE_OTHER,
34 HIGH_VALUE_OTHER,
35 LOW_VALUE_OTHER_ID,
36 HIGH_VALUE_OTHER_ID
37 FROM QA_PLAN_CHAR_ACTION_TRIGGERS
38 WHERE PLAN_ID = X_COPY_PLAN_ID AND
39 CHAR_ID NOT IN (SELECT CHAR_ID FROM QA_PLAN_CHARS
40 WHERE PLAN_ID = X_PLAN_ID)
41 ORDER BY TRIGGER_SEQUENCE,
42 PLAN_CHAR_ACTION_TRIGGER_ID;
43
44
45 CURSOR CS1 IS
46 SELECT QA_PLAN_CHAR_ACTION_TRIGGERS_S.NEXTVAL FROM DUAL;
47
48 ACTION_TRIGGER_ID NUMBER;
49
50 QPCAT C1%ROWTYPE;
51
52 CURSOR C2 IS
53 SELECT
54 PLAN_CHAR_ACTION_ID,
55 LAST_UPDATE_DATE,
56 LAST_UPDATED_BY,
57 CREATION_DATE,
58 CREATED_BY,
59 PLAN_CHAR_ACTION_TRIGGER_ID,
60 ACTION_ID,
61 CAR_NAME_PREFIX,
62 CAR_TYPE_ID,
63 CAR_OWNER,
64 MESSAGE,
65 STATUS_CODE,
66 ALR_ACTION_ID,
67 ALR_ACTION_SET_ID,
68 ASSIGNED_CHAR_ID,
69 ASSIGN_TYPE
70 FROM QA_PLAN_CHAR_ACTIONS
71 WHERE PLAN_CHAR_ACTION_TRIGGER_ID = QPCAT.PLAN_CHAR_ACTION_TRIGGER_ID
72 ORDER BY PLAN_CHAR_ACTION_ID;
73
74 QPCA C2%ROWTYPE;
75
76 CURSOR CS2 IS
77 SELECT QA_PLAN_CHAR_ACTIONS_S.NEXTVAL FROM DUAL;
78
79 QPC_ACTION_ID NUMBER;
80
81 -- Bug 3111310. Add app_id to WHERE clause of SQL
82 -- to improve performance
83 -- ksoh Fri Aug 22 11:05:00 PST 2003
84 --
85 CURSOR C3 IS
86 SELECT
87 APPLICATION_ID,
88 ACTION_ID,
89 NAME,
90 ALERT_ID,
91 ACTION_TYPE,
92 LAST_UPDATE_DATE,
93 LAST_UPDATED_BY,
94 CREATION_DATE,
95 CREATED_BY,
96 LAST_UPDATE_LOGIN,
97 END_DATE_ACTIVE,
98 ENABLED_FLAG,
99 DESCRIPTION,
100 ACTION_LEVEL_TYPE,
101 DATE_LAST_EXECUTED,
102 FILE_NAME,
103 ARGUMENT_STRING,
104 PROGRAM_APPLICATION_ID,
105 CONCURRENT_PROGRAM_ID,
106 LIST_APPLICATION_ID,
107 LIST_ID,
108 TO_RECIPIENTS,
109 CC_RECIPIENTS,
110 BCC_RECIPIENTS,
111 PRINT_RECIPIENTS,
112 PRINTER,
113 SUBJECT,
114 REPLY_TO,
115 RESPONSE_SET_ID,
116 FOLLOW_UP_AFTER_DAYS,
117 COLUMN_WRAP_FLAG,
118 MAXIMUM_SUMMARY_MESSAGE_WIDTH,
119 BODY,
120 VERSION_NUMBER
121 FROM ALR_ACTIONS
122 WHERE APPLICATION_ID = 250
123 AND ACTION_ID = QPCA.ALR_ACTION_ID;
124
125 ALRA C3%ROWTYPE;
126
127 CURSOR CS3 IS
128 SELECT
129 ALR_ACTIONS_S.NEXTVAL,
130 ALR_ACTION_SETS_S.NEXTVAL,
131 ALR_ACTION_SET_MEMBERS_S.NEXTVAL,
132 QA_ALR_ACTION_NAME_S.NEXTVAL,
133 QA_ALR_ACTION_SET_NAME_S.NEXTVAL
134 FROM DUAL;
135
136 NEW_ACTION_ID NUMBER;
137 NEW_ACTION_SET_ID NUMBER;
138 NEW_ACTION_SET_MEMBER_ID NUMBER;
139
140 ACTION_SET_SEQUENCE NUMBER;
141 ACTION_SET_MEMBERS_SEQUENCE NUMBER;
142
143 X_ACTION_NAME NUMBER;
144 X_ACTION_SET_NAME NUMBER;
145 NEW_ACTION_NAME VARCHAR2(80);
146 NEW_ACTION_SET_NAME VARCHAR2(50);
147
148 CURSOR C4 IS
149 SELECT
150 PLAN_CHAR_ACTION_ID,
151 CHAR_ID,
152 LAST_UPDATE_DATE,
153 LAST_UPDATED_BY,
154 CREATION_DATE,
155 CREATED_BY,
156 LAST_UPDATE_LOGIN,
157 TOKEN_NAME
158 FROM QA_PLAN_CHAR_ACTION_OUTPUTS
159 WHERE PLAN_CHAR_ACTION_ID = QPCA.PLAN_CHAR_ACTION_ID
160 ORDER BY PLAN_CHAR_ACTION_ID;
161
162 QPCAO C4%ROWTYPE;
163
164 --
165 -- See Bug 2624112
166 -- The decimal precision for a number type collection
167 -- element is to be configured at plan level.
168 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
169 --
170 -- Modified the cursor to read decimal precision of the element
171 -- in master plan also.
172 --
173 -- Changed for UOM Code also
174 --
175 -- Tracking Bug : 3104827. Review Tracking Bug : 3148873
176 -- Modified the cursor to include three new flags for collection plan elements
177 -- saugupta Mon Sep 22 23:38:15 PDT 2003
178
179 -- Bug 4958761. SQL Repository Fix SQL ID: 15008182
180 CURSOR C5 IS
181 SELECT
182 qpc.plan_id,
183 qpc.char_id,
184 qc.name char_name,
185 qc.datatype,
186 qpc.last_update_date,
187 qpc.last_updated_by,
188 qpc.creation_date,
189 qpc.created_by,
190 qpc.prompt_sequence,
191 qpc.prompt,
192 qpc.enabled_flag,
193 qpc.mandatory_flag,
194 qpc.read_only_flag,
195 qpc.ss_poplist_flag,
196 qpc.information_flag,
197 qpc.default_value,
198 qc.hardcoded_column,
199 qpc.result_column_name,
200 qpc.values_exist_flag,
201 qpc.displayed_flag,
202 -- 12.1 Device Integration Project.
203 -- Added device fields.
204 -- bhsankar Fri Oct 19 01:51:57 PDT 2007
205 qpc.device_flag,
206 qpc.device_id,
207 qpc.override_flag,
208 -- Device Integration Project End.
209 qpc.attribute_category,
210 qpc.attribute1,
211 qpc.attribute2,
212 qpc.attribute3,
213 qpc.attribute4,
214 qpc.attribute5,
215 qpc.attribute6,
216 qpc.attribute7,
217 qpc.attribute8,
218 qpc.attribute9,
219 qpc.attribute10,
220 qpc.attribute11,
221 qpc.attribute12,
222 qpc.attribute13,
223 qpc.attribute14,
224 qpc.attribute15,
225 qpc.default_value_id,
226 nvl(qpc.decimal_precision, qc.decimal_precision) decimal_precision ,
227 nvl(qpc.uom_code, qc.uom_code) uom_code
228 FROM qa_plan_chars qpc,
229 qa_chars qc
230 WHERE qpc.plan_id = X_COPY_PLAN_ID
231 AND qc.char_id = qpc.char_id
232 AND qc.char_id not in
233 (SELECT char_id
234 FROM qa_plan_chars
235 WHERE plan_id = X_PLAN_ID )
236 ORDER BY prompt_sequence;
237 /*
238 SELECT
239 PLAN_ID,
240 CHAR_ID,
241 CHAR_NAME, -- Bug 3926150 needed name.
242 DATATYPE,
243 LAST_UPDATE_DATE,
244 LAST_UPDATED_BY,
245 CREATION_DATE,
246 CREATED_BY,
247 PROMPT_SEQUENCE,
248 PROMPT,
249 ENABLED_FLAG,
250 MANDATORY_FLAG,
251 READ_ONLY_FLAG,
252 SS_POPLIST_FLAG,
253 INFORMATION_FLAG,
254 DEFAULT_VALUE,
255 HARDCODED_COLUMN,
256 RESULT_COLUMN_NAME,
257 VALUES_EXIST_FLAG,
258 DISPLAYED_FLAG,
259 ATTRIBUTE_CATEGORY,
260 ATTRIBUTE1,
261 ATTRIBUTE2,
262 ATTRIBUTE3,
263 ATTRIBUTE4,
264 ATTRIBUTE5,
265 ATTRIBUTE6,
266 ATTRIBUTE7,
267 ATTRIBUTE8,
268 ATTRIBUTE9,
269 ATTRIBUTE10,
270 ATTRIBUTE11,
271 ATTRIBUTE12,
272 ATTRIBUTE13,
273 ATTRIBUTE14,
274 ATTRIBUTE15,
275 DEFAULT_VALUE_ID,
276 DECIMAL_PRECISION,
277 UOM_CODE
278 FROM QA_PLAN_CHARS_V
279 WHERE PLAN_ID = X_COPY_PLAN_ID
280 AND CHAR_ID NOT IN (SELECT CHAR_ID FROM QA_PLAN_CHARS
281 WHERE PLAN_ID = X_PLAN_ID)
282 ORDER BY PROMPT_SEQUENCE;
283 */
284
285 QPCV C5%ROWTYPE;
286
287 -- rkaza; 05/01/2002. added datatype restriction
288
289 -- Bug 3229810. Modifying the code as Collection element copying
290 -- failing for DATETIME datatype elements.
291 -- saugupta Wed Nov 12 23:08:34 PST 2003
292
293 -- -- Bug 4958761. SQL Repository Fix SQL ID: 15008205
294 CURSOR C7 IS
295 SELECT TO_NUMBER(SUBSTR(QPC.RESULT_COLUMN_NAME,10,3)) RES_COLUMN_NAME
296 FROM QA_PLAN_CHARS QPC, QA_CHARS QC
297 WHERE PLAN_ID = X_PLAN_ID
298 AND qc.char_id = qpc.char_id
299 AND QC.HARDCODED_COLUMN IS NULL
300 AND QC.DATATYPE in (1,2,3,6)
301 ORDER BY TO_NUMBER(SUBSTR(QPC.RESULT_COLUMN_NAME,10,3));
302
303 /*
304 SELECT TO_NUMBER(SUBSTR(RESULT_COLUMN_NAME,10,3)) RES_COLUMN_NAME
305 FROM QA_PLAN_CHARS_V
306 WHERE PLAN_ID = X_PLAN_ID
307 AND HARDCODED_COLUMN IS NULL
308 AND DATATYPE in (1,2,3,6)
309 ORDER BY TO_NUMBER(SUBSTR(RESULT_COLUMN_NAME,10,3));
310 */
311
312 TYPE column_num IS TABLE of BOOLEAN
313 INDEX BY BINARY_INTEGER;
314
315 res_columns column_num;
316 i binary_integer;
317
318 COLUMN_NAME VARCHAR2(30);
319 COLUMN_NUMBER NUMBER;
320 NEW_RESULT_COLUMN_NAME VARCHAR2(30);
321
322 -- rkaza; 05/01/2002. added the following cursor for comments
323
324 -- Bug 4958761. SQL Repository Fix SQL ID: 15008222
325 CURSOR C8 IS
326 SELECT TO_NUMBER(SUBSTR(RESULT_COLUMN_NAME,8,3)) RES_COLUMN_NAME
327 FROM QA_PLAN_CHARS QPC, QA_CHARS QC
328 WHERE QPC.PLAN_ID = X_PLAN_ID
329 and qc.char_id = qpc.char_id
330 AND QC.HARDCODED_COLUMN IS NULL
331 AND QC.DATATYPE = 4
332 ORDER BY TO_NUMBER(SUBSTR(QPC.RESULT_COLUMN_NAME,8,3));
333 /*
334 SELECT TO_NUMBER(SUBSTR(RESULT_COLUMN_NAME,8,3)) RES_COLUMN_NAME
335 FROM QA_PLAN_CHARS_V
336 WHERE PLAN_ID = X_PLAN_ID
337 AND HARDCODED_COLUMN IS NULL
338 AND DATATYPE = 4
339 ORDER BY TO_NUMBER(SUBSTR(RESULT_COLUMN_NAME,8,3));
340 */
341
342 comment_cols column_num;
343 j binary_integer;
344
345 CURSOR C6 IS
346 SELECT MAX(PROMPT_SEQUENCE) FROM QA_PLAN_CHARS
347 WHERE PLAN_ID = X_PLAN_ID;
348
349 NEW_PROMPT_SEQUENCE NUMBER;
350
351 BEGIN
352
353 -- Insert child values
354
355
356 INSERT INTO QA_PLAN_CHAR_VALUE_LOOKUPS (
357 PLAN_ID,
358 CHAR_ID,
359 SHORT_CODE,
360 LAST_UPDATE_DATE,
361 LAST_UPDATED_BY,
362 CREATION_DATE,
363 CREATED_BY,
364 DESCRIPTION,
365 SHORT_CODE_ID)
366 SELECT
367 X_PLAN_ID,
368 CHAR_ID,
369 SHORT_CODE,
370 SYSDATE,
371 X_USER_ID,
372 SYSDATE,
373 CREATED_BY,
374 DESCRIPTION,
375 SHORT_CODE_ID
376 FROM QA_PLAN_CHAR_VALUE_LOOKUPS
377 WHERE PLAN_ID = X_COPY_PLAN_ID
378 AND CHAR_ID NOT IN (SELECT CHAR_ID FROM QA_PLAN_CHARS
379 WHERE PLAN_ID = X_PLAN_ID);
380
381 OPEN C1;
382 LOOP
383
384 FETCH C1 INTO QPCAT;
385 EXIT WHEN C1%NOTFOUND;
386
387 OPEN CS1;
388 FETCH CS1 INTO ACTION_TRIGGER_ID;
389 CLOSE CS1;
390
391 INSERT INTO QA_PLAN_CHAR_ACTION_TRIGGERS (
392 PLAN_CHAR_ACTION_TRIGGER_ID,
393 LAST_UPDATE_DATE,
394 LAST_UPDATED_BY,
395 CREATION_DATE,
396 CREATED_BY,
397 TRIGGER_SEQUENCE,
398 PLAN_ID,
399 CHAR_ID,
400 OPERATOR,
401 LOW_VALUE_LOOKUP,
402 HIGH_VALUE_LOOKUP,
403 LOW_VALUE_OTHER,
404 HIGH_VALUE_OTHER,
405 LOW_VALUE_OTHER_ID,
406 HIGH_VALUE_OTHER_ID)
407 VALUES (
408 ACTION_TRIGGER_ID,
409 SYSDATE,
410 X_USER_ID,
411 SYSDATE,
412 X_USER_ID,
413 QPCAT.TRIGGER_SEQUENCE,
414 X_PLAN_ID,
415 QPCAT.CHAR_ID,
416 QPCAT.OPERATOR,
417 QPCAT.LOW_VALUE_LOOKUP,
418 QPCAT.HIGH_VALUE_LOOKUP,
419 QPCAT.LOW_VALUE_OTHER,
420 QPCAT.HIGH_VALUE_OTHER,
421 QPCAT.LOW_VALUE_OTHER_ID,
422 QPCAT.HIGH_VALUE_OTHER_ID);
423
424 --
425 -- Bug 2698812
426 -- Avoided the earlier used Cursor to read
427 -- from PO lookup table. Using variables instead
428 --
429 -- rkunchal Sat Jan 4 02:06:10 PST 2003
430 --
431 -- Bug 5300577
432 -- Included Template OPM Recieving inspection plan because
433 -- conversion is required for these plans as well.
434
435 IF X_COPY_PLAN_ID IN (1,2147483637) AND QPCAT.LOW_VALUE_OTHER IN ('ACCEPT', 'REJECT') THEN
436 UPDATE QA_PLAN_CHAR_ACTION_TRIGGERS
437 SET LOW_VALUE_OTHER = (SELECT DISPLAYED_FIELD
438 FROM PO_LOOKUP_CODES
439 WHERE LOOKUP_TYPE = 'ERT RESULTS ACTION'
440 AND LOOKUP_CODE = QPCAT.LOW_VALUE_OTHER)
441 WHERE PLAN_CHAR_ACTION_TRIGGER_ID = ACTION_TRIGGER_ID;
442 END IF;
443
444 OPEN C2;
445 LOOP
446
447 FETCH C2 INTO QPCA;
448 EXIT WHEN C2%NOTFOUND;
449
450 OPEN CS2;
451 FETCH CS2 INTO QPC_ACTION_ID;
452 CLOSE CS2;
453
454 OPEN CS3;
455 FETCH CS3 INTO NEW_ACTION_ID, NEW_ACTION_SET_ID,
456 NEW_ACTION_SET_MEMBER_ID,
457 X_ACTION_NAME, X_ACTION_SET_NAME;
458 CLOSE CS3;
459
460 NEW_ACTION_NAME := 'QA_' || TO_CHAR(X_ACTION_NAME);
461 NEW_ACTION_SET_NAME := 'QA_' || TO_CHAR(X_ACTION_SET_NAME);
462
463 INSERT INTO QA_PLAN_CHAR_ACTIONS (
464 PLAN_CHAR_ACTION_ID,
465 LAST_UPDATE_DATE,
466 LAST_UPDATED_BY,
467 CREATION_DATE,
468 CREATED_BY,
469 PLAN_CHAR_ACTION_TRIGGER_ID,
470 ACTION_ID,
471 CAR_NAME_PREFIX,
472 CAR_TYPE_ID,
473 CAR_OWNER,
474 MESSAGE,
475 STATUS_CODE,
476 ALR_ACTION_ID,
477 ALR_ACTION_SET_ID,
478 ASSIGNED_CHAR_ID,
479 ASSIGN_TYPE)
480 VALUES (
481 QPC_ACTION_ID,
482 SYSDATE,
483 X_USER_ID,
484 SYSDATE,
485 X_USER_ID,
486 ACTION_TRIGGER_ID,
487 QPCA.ACTION_ID,
488 QPCA.CAR_NAME_PREFIX,
489 QPCA.CAR_TYPE_ID,
490 QPCA.CAR_OWNER,
491 QPCA.MESSAGE,
492 QPCA.STATUS_CODE,
493 DECODE (QPCA.ACTION_ID,
494 10, NEW_ACTION_ID,
495 11, NEW_ACTION_ID,
496 12, NEW_ACTION_ID,
497 13, NEW_ACTION_ID,
498 NULL),
499 DECODE (QPCA.ACTION_ID,
500 10, NEW_ACTION_SET_ID,
501 11, NEW_ACTION_SET_ID,
502 12, NEW_ACTION_SET_ID,
503 13, NEW_ACTION_SET_ID,
504 NULL),
505 QPCA.ASSIGNED_CHAR_ID,
506 QPCA.ASSIGN_TYPE
507 );
508
509 OPEN C3;
510 FETCH C3 INTO ALRA;
511
512 IF NOT C3%NOTFOUND THEN
513 INSERT INTO ALR_ACTIONS (
514 APPLICATION_ID,
515 ACTION_ID,
516 NAME,
517 ALERT_ID,
518 ACTION_TYPE,
519 LAST_UPDATE_DATE,
520 LAST_UPDATED_BY,
521 CREATION_DATE,
522 CREATED_BY,
523 END_DATE_ACTIVE,
524 ENABLED_FLAG,
525 DESCRIPTION,
526 ACTION_LEVEL_TYPE,
527 DATE_LAST_EXECUTED,
528 FILE_NAME,
529 ARGUMENT_STRING,
530 PROGRAM_APPLICATION_ID,
531 CONCURRENT_PROGRAM_ID,
532 LIST_APPLICATION_ID,
533 LIST_ID,
534 TO_RECIPIENTS,
535 CC_RECIPIENTS,
536 BCC_RECIPIENTS,
537 PRINT_RECIPIENTS,
538 PRINTER,
539 SUBJECT,
540 REPLY_TO,
541 RESPONSE_SET_ID,
542 FOLLOW_UP_AFTER_DAYS,
543 COLUMN_WRAP_FLAG,
544 MAXIMUM_SUMMARY_MESSAGE_WIDTH,
545 BODY,
546 VERSION_NUMBER)
547 VALUES (
548 ALRA.APPLICATION_ID,
549 NEW_ACTION_ID,
550 NEW_ACTION_NAME,
551 ALRA.ALERT_ID,
552 ALRA.ACTION_TYPE,
553 SYSDATE,
554 X_USER_ID,
555 SYSDATE,
556 X_USER_ID,
557 ALRA.END_DATE_ACTIVE,
558 ALRA.ENABLED_FLAG,
559 ALRA.DESCRIPTION,
560 ALRA.ACTION_LEVEL_TYPE,
561 ALRA.DATE_LAST_EXECUTED,
562 ALRA.FILE_NAME,
563 ALRA.ARGUMENT_STRING,
564 ALRA.PROGRAM_APPLICATION_ID,
565 ALRA.CONCURRENT_PROGRAM_ID,
566 ALRA.LIST_APPLICATION_ID,
567 ALRA.LIST_ID,
568 ALRA.TO_RECIPIENTS,
569 ALRA.CC_RECIPIENTS,
570 ALRA.BCC_RECIPIENTS,
571 ALRA.PRINT_RECIPIENTS,
572 ALRA.PRINTER,
573 ALRA.SUBJECT,
574 ALRA.REPLY_TO,
575 ALRA.RESPONSE_SET_ID,
576 ALRA.FOLLOW_UP_AFTER_DAYS,
577 ALRA.COLUMN_WRAP_FLAG,
578 ALRA.MAXIMUM_SUMMARY_MESSAGE_WIDTH,
579 ALRA.BODY,
580 ALRA.VERSION_NUMBER
581 );
582
583 BEGIN
584 SELECT NVL(MAX(SEQUENCE),0)+1
585 INTO ACTION_SET_SEQUENCE
586 FROM ALR_ACTION_SETS
587 WHERE APPLICATION_ID = 250
588 AND ALERT_ID = 10177;
589
590 EXCEPTION
591 WHEN NO_DATA_FOUND THEN
592 ACTION_SET_SEQUENCE := 1;
593 END;
594
595 INSERT INTO ALR_ACTION_SETS (
596 APPLICATION_ID,
597 ACTION_SET_ID,
598 NAME,
599 ALERT_ID,
600 LAST_UPDATE_DATE,
601 LAST_UPDATED_BY,
602 CREATION_DATE,
603 CREATED_BY,
604 END_DATE_ACTIVE,
605 ENABLED_FLAG,
606 RECIPIENTS_VIEW_ONLY_FLAG,
607 DESCRIPTION,
608 SUPPRESS_FLAG,
609 SUPPRESS_DAYS,
610 SEQUENCE)
611 VALUES (
612 250,
613 NEW_ACTION_SET_ID,
614 NEW_ACTION_SET_NAME,
615 10177,
616 SYSDATE,
617 X_USER_ID,
618 SYSDATE,
619 X_USER_ID,
620 NULL,
621 'Y',
622 'N',
623 NEW_ACTION_SET_NAME,
624 'N',
625 NULL,
626 ACTION_SET_SEQUENCE
627 );
628
629 BEGIN
630 SELECT NVL(MAX(SEQUENCE),0)+1
631 INTO ACTION_SET_MEMBERS_SEQUENCE
632 FROM ALR_ACTION_SET_MEMBERS
633 WHERE APPLICATION_ID = 250
634 AND ALERT_ID = 10177
635 AND ACTION_SET_ID = NEW_ACTION_SET_ID;
636 EXCEPTION
637 WHEN NO_DATA_FOUND THEN
638 ACTION_SET_MEMBERS_SEQUENCE := 1;
639 END;
640
641 INSERT INTO ALR_ACTION_SET_MEMBERS (
642 APPLICATION_ID,
643 ACTION_SET_MEMBER_ID,
644 ACTION_SET_ID,
645 ACTION_ID,
646 ACTION_GROUP_ID,
647 ALERT_ID,
648 SEQUENCE,
649 LAST_UPDATE_DATE,
650 LAST_UPDATED_BY,
651 CREATION_DATE,
652 CREATED_BY,
653 END_DATE_ACTIVE,
654 ENABLED_FLAG,
655 SUMMARY_THRESHOLD,
656 ABORT_FLAG,
657 ERROR_ACTION_SEQUENCE)
658 VALUES (
659 250,
660 NEW_ACTION_SET_MEMBER_ID,
661 NEW_ACTION_SET_ID,
662 NEW_ACTION_ID,
663 NULL,
664 10177,
665 ACTION_SET_MEMBERS_SEQUENCE,
666 SYSDATE,
667 X_USER_ID,
668 SYSDATE,
669 X_USER_ID,
670 NULL,
671 'Y',
672 NULL,
673 'A',
674 NULL
675 );
676
677 END IF;
678
679 CLOSE C3;
680
681 OPEN C4;
682 LOOP
683 FETCH C4 INTO QPCAO;
684 EXIT WHEN C4%NOTFOUND;
685
686 INSERT INTO QA_PLAN_CHAR_ACTION_OUTPUTS (
687 PLAN_CHAR_ACTION_ID,
688 CHAR_ID,
689 LAST_UPDATE_DATE,
690 LAST_UPDATED_BY,
691 CREATION_DATE,
692 CREATED_BY,
693 TOKEN_NAME)
694 VALUES (
695 QPC_ACTION_ID,
696 QPCAO.CHAR_ID,
697 SYSDATE,
698 X_USER_ID,
699 SYSDATE,
700 X_USER_ID,
701 QPCAO.TOKEN_NAME
702 );
703
704 END LOOP;
705 CLOSE C4;
706
707 END LOOP;
708 CLOSE C2;
709
710 END LOOP;
711 CLOSE C1;
712
713 -- Put this insert statement AFTER all the children have been inserted
714 -- so that the last line of the where clauses will work in the above
715 -- statements; i.e. if you insert the plan-chars first, none of the
716 -- children will ever get copied over.
717
718 OPEN C6;
719 FETCH C6 INTO NEW_PROMPT_SEQUENCE;
720 CLOSE C6;
721
722 IF NEW_PROMPT_SEQUENCE IS NULL THEN
723 NEW_PROMPT_SEQUENCE := 0;
724 END IF;
725
726 FOR i IN 1..QLTNINRB.RES_CHAR_COLUMNS LOOP
727 res_columns(i) := FALSE;
728 END LOOP;
729
730 -- rkaza; 05/01/2002. following for loop for comments
731 FOR j IN 1..5 LOOP
732 comment_cols(j) := FALSE;
733 END LOOP;
734
735 OPEN C7;
736 LOOP
737 FETCH C7 INTO i;
738 EXIT WHEN C7%NOTFOUND;
739
740 res_columns(i) := TRUE;
741 END LOOP;
742 CLOSE C7;
743
744 -- rkaza; 05/01/2002. following cursor for comments
745 OPEN C8;
746 LOOP
747 FETCH C8 INTO j;
748 EXIT WHEN C8%NOTFOUND;
749
750 comment_cols(j) := TRUE;
751 END LOOP;
752 CLOSE C8;
753
754 -- rkaza; 05/01/2002. initialize j for comments
755 --
756 -- Need to move i to inside loop for Bug 3926150
757 --
758 -- i := 1;
759 --
760 j := 1;
761
762 OPEN C5;
763 LOOP
764
765 FETCH C5 INTO QPCV;
766 EXIT WHEN C5%NOTFOUND;
767
768 IF QPCV.HARDCODED_COLUMN IS NOT NULL THEN
769 NEW_RESULT_COLUMN_NAME := QPCV.HARDCODED_COLUMN;
770 ELSE
771
772 /* rkaza; 05/01/2002. copy the same result_column_name as in the original plan
773 for sequence element
774 */
775 IF QPCV.DATATYPE = 5 THEN
776 NEW_RESULT_COLUMN_NAME := QPCV.RESULT_COLUMN_NAME;
777 END IF;
778
779 -- rkaza; 05/01/2002. character columns
780
781 -- Bug 3229810. Modifying the code as Collection element copying
782 -- failing for DATETIME datatype elements.
783 -- saugupta Wed Nov 12 23:08:34 PST 2003
784 IF QPCV.DATATYPE IN (1,2,3,6) THEN
785
786 --
787 -- Bug 3926150. Test if there is a function based index
788 -- associated. If so, we will try to use it.
789 --
790 new_result_column_name := NULL;
791 l_default_column :=
792 qa_char_indexes_pkg.get_default_result_column(qpcv.char_id);
793 IF l_default_column IS NOT NULL THEN
794 IF NOT res_columns(to_number(substr(l_default_column, 10))) THEN
795 --
796 -- Now we know the default column is available.
797 --
798 new_result_column_name := l_default_column;
799 ELSE
800 --
801 -- Need to warn user because we can't reuse the index.
802 --
803 l_disabled_indexed_elements :=
804 l_disabled_indexed_elements || ', ' || qpcv.char_name;
805 dummy := qa_char_indexes_pkg.disable_index(qpcv.char_id);
806 END IF;
807 END IF;
808
809 IF new_result_column_name IS NULL THEN
810 -- Find the first available column number for character columns
811 --
812 -- Bug 3926150. Moved i to here to re-scan entire rg
813 -- for more foolproof operation.
814 --
815 i := 1;
816 WHILE ((res_columns(i) = TRUE) AND (i <= QLTNINRB.RES_CHAR_COLUMNS)) LOOP
817 i := i + 1;
818 END LOOP;
819
820 IF i > QLTNINRB.RES_CHAR_COLUMNS THEN
821 -- Exceeded upper limit of maximum number of chars. Error out.
822 FND_MESSAGE.SET_NAME('QA', 'QA_EXCEEDED_COLUMN_COUNT');
823 APP_EXCEPTION.RAISE_EXCEPTION;
824 END IF;
825
826 NEW_RESULT_COLUMN_NAME := 'CHARACTER' || TO_CHAR(i);
827 END IF;
828
829 res_columns(to_number(substr(new_result_column_name, 10))) := TRUE;
830 --
831 -- Bug 3926150
832 -- i := i + 1;
833 --
834 END IF;
835
836 -- rkaza; 05/01/2002. for comments
837 IF QPCV.DATATYPE = 4 THEN
838 -- Find the first available column number for comment columns
839 WHILE ((comment_cols(j) = TRUE) AND (j <= 5)) LOOP
840 j := j + 1;
841 END LOOP;
842
843 IF j > 5 THEN
844 -- Exceeded upper limit of maximum number of chars. Error out.
845 FND_MESSAGE.SET_NAME('QA', 'QA_EXCEEDED_COLUMN_COUNT');
846 APP_EXCEPTION.RAISE_EXCEPTION;
847 END IF;
848
849 NEW_RESULT_COLUMN_NAME := 'COMMENT' || TO_CHAR(j);
850 comment_cols(j) := TRUE;
851 j := j + 1;
852 END IF;
853
854 END IF;
855
856 NEW_PROMPT_SEQUENCE := NEW_PROMPT_SEQUENCE + 10;
857
858 --
859 -- See Bug 2624112
860 -- The decimal precision for a number type collection
861 -- element is to be configured at plan level.
862 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
863 --
864 -- Modified the INSERT statement to write decimal_precision also
865 -- from the master plan.
866 --
867 -- Tracking Bug : 3104827. Review Tracking Bug : 3148873
868 -- Modified the INSERT statement to include three new flags for collection plan elements
869 -- saugupta Mon Sep 22 23:38:15 PDT 2003
870
871 INSERT INTO QA_PLAN_CHARS (
872 plan_id,
873 char_id,
874 last_update_date,
875 last_updated_by,
876 creation_date,
877 created_by,
878 prompt_sequence,
879 prompt,
880 enabled_flag,
881 mandatory_flag,
882 read_only_flag,
883 ss_poplist_flag,
884 information_flag,
885 default_value,
886 result_column_name,
887 values_exist_flag,
888 displayed_flag,
889 attribute_category,
890 attribute1,
891 attribute2,
892 attribute3,
893 attribute4,
894 attribute5,
895 attribute6,
896 attribute7,
897 attribute8,
898 attribute9,
899 attribute10,
900 attribute11,
901 attribute12,
902 attribute13,
903 attribute14,
904 attribute15,
905 default_value_id,
906 decimal_precision,
907 uom_code,
908 -- 12.1 Device Integration Project.
909 -- Added device fields.
910 -- bhsankar Fri Oct 19 01:51:57 PDT 2007
911 device_flag,
912 device_id,
913 override_flag)
914 VALUES (
915 X_PLAN_ID,
916 QPCV.CHAR_ID,
917 SYSDATE,
918 X_USER_ID,
919 SYSDATE,
920 X_USER_ID,
921 NEW_PROMPT_SEQUENCE,
922 QPCV.PROMPT,
923 QPCV.ENABLED_FLAG,
924 QPCV.MANDATORY_FLAG,
925 QPCV.READ_ONLY_FLAG,
926 QPCV.SS_POPLIST_FLAG,
927 QPCV.INFORMATION_FLAG,
928 QPCV.DEFAULT_VALUE,
929 NEW_RESULT_COLUMN_NAME,
930 QPCV.VALUES_EXIST_FLAG,
931 QPCV.DISPLAYED_FLAG,
932 QPCV.ATTRIBUTE_CATEGORY,
933 QPCV.ATTRIBUTE1,
934 QPCV.ATTRIBUTE2,
935 QPCV.ATTRIBUTE3,
936 QPCV.ATTRIBUTE4,
937 QPCV.ATTRIBUTE5,
938 QPCV.ATTRIBUTE6,
939 QPCV.ATTRIBUTE7,
940 QPCV.ATTRIBUTE8,
941 QPCV.ATTRIBUTE9,
942 QPCV.ATTRIBUTE10,
943 QPCV.ATTRIBUTE11,
944 QPCV.ATTRIBUTE12,
945 QPCV.ATTRIBUTE13,
946 QPCV.ATTRIBUTE14,
947 QPCV.ATTRIBUTE15,
948 QPCV.DEFAULT_VALUE_ID,
949 QPCV.DECIMAL_PRECISION,
950 QPCV.UOM_CODE,
951 -- Bug 6350580
952 -- 12.1 Device Integration Project.
953 -- Added device fields.
954 -- bhsankar Fri Oct 19 01:51:57 PDT 2007
955 QPCV.DEVICE_FLAG,
956 QPCV.DEVICE_ID,
957 QPCV.OVERRIDE_FLAG
958 );
959
960 END LOOP;
961 CLOSE C5;
962
963 --
964 -- Bug 3926150
965 -- Pass back the disabled index names. (use substr to get rid of
966 -- the lead comma and space.
967 --
968 IF l_disabled_indexed_elements IS NOT NULL THEN
969 x_disabled_indexed_elements := substr(l_disabled_indexed_elements, 3);
970 END IF;
971
972 -- the following insert statement has to be replicated for the
973 -- collection triggers in-lists. Comment it out for now; in-lists
974 -- haven't been implemented in QLTPLMDF yet. (Need to pass in an
975 -- additional argument indicating whether it's an action trigger or a
976 -- collection trigger in-list.)
977
978 /* INSERT INTO QA_IN_LISTS (
979 LIST_ELEM_ID,
980 LAST_UPDATE_DATE,
981 LAST_UPDATED_BY,
982 CREATION_DATE,
983 CREATED_BY,
984 LIST_ID,
985 PARENT_BLOCK_NAME,
986 VALUE,
987 VALUE_ID,
988 CHAR_ID)
989 SELECT
990 *** nextval
991 SYSDATE,
992 X_USER_ID,
993 SYSDATE,
994 X_USER_ID,
995 QPCAT.PLAN_CHAR_ACTION_TRIGGER_ID,
996 'QPC_ACTION_TRIGGERS',
997 QIL.VALUE,
998 QIL.VALUE_ID,
999 QIL.CHAR_ID
1000 FROM
1001 QA_PLAN_CHAR_ACTION_TRIGGERS QPCAT,
1002 QA_IN_LISTS QIL
1003 WHERE QPCAT.PLAN_ID = X_COPY_PLAN_ID
1004 AND QPCAT.CHAR_ID = QIL.CHAR_ID
1005 AND ???? */
1006
1007 END insert_plan_chars;
1008
1009
1010
1011 END QLTCPPLB;
1012