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