[Home] [Help]
PACKAGE BODY: APPS.QLTCPDFB
Source
1 PACKAGE BODY QLTCPDFB as
2 /* $Header: qltcpdfb.plb 115.4 2003/08/25 18:59:54 ksoh ship $ */
3
4 -- Copy Defaults
5 --
6 -- Called by QLTPLMDF form (Quality Plan Workbench) to copy default
7 -- values, action triggers, and actions from qa_char_value_lookups,
8 -- qa_char_action_triggers, and qa_char_actions into qa_plan_char_xxxx
9 -- tables.
10
11 -- dmaggard 110.17/94 created.
12
13
14 PROCEDURE Insert_Rows (
15 X_Copy_Values NUMBER,
16 X_Copy_Actions NUMBER,
17 X_Plan_Id NUMBER,
18 X_Char_Id NUMBER,
19 X_Last_Update_Date DATE,
20 X_Last_Updated_By NUMBER,
21 X_Creation_Date DATE,
22 X_Created_By NUMBER,
23 X_Last_Update_Login NUMBER DEFAULT NULL,
24 X_values_found IN OUT NOCOPY NUMBER,
25 X_actions_found IN OUT NOCOPY NUMBER
26 ) IS
27 X_qa_app_id NUMBER := 250;
28 X_qa_alert_id NUMBER := 10177;
29
30 CURSOR C1 IS
31 SELECT
32 CHAR_ACTION_TRIGGER_ID,
33 LAST_UPDATE_DATE,
34 LAST_UPDATED_BY,
35 CREATION_DATE,
36 CREATED_BY,
37 TRIGGER_SEQUENCE,
38 CHAR_ID,
39 OPERATOR,
40 LOW_VALUE_LOOKUP,
41 HIGH_VALUE_LOOKUP,
42 LOW_VALUE_OTHER,
43 HIGH_VALUE_OTHER,
44 LOW_VALUE_OTHER_ID,
45 HIGH_VALUE_OTHER_ID
46 FROM QA_CHAR_ACTION_TRIGGERS
47 WHERE CHAR_ID = X_Char_Id
48 ORDER BY TRIGGER_SEQUENCE,
49 CHAR_ACTION_TRIGGER_ID;
50
51 QCAT C1%ROWTYPE;
52
53 CURSOR CS1 IS
54 SELECT QA_PLAN_CHAR_ACTION_TRIGGERS_S.NEXTVAL FROM DUAL;
55
56 ACTION_TRIGGER_ID NUMBER;
57
58 CURSOR C2 IS
59 SELECT
60 CHAR_ACTION_ID,
61 LAST_UPDATE_DATE,
62 LAST_UPDATED_BY,
63 CREATION_DATE,
64 CREATED_BY,
65 CHAR_ACTION_TRIGGER_ID,
66 ACTION_ID,
67 CAR_NAME_PREFIX,
68 CAR_TYPE_ID,
69 CAR_OWNER,
70 MESSAGE,
71 STATUS_CODE,
72 STATUS_ID,
73 ALR_ACTION_ID,
74 ALR_ACTION_SET_ID
75 FROM QA_CHAR_ACTIONS
76 WHERE CHAR_ACTION_TRIGGER_ID = QCAT.CHAR_ACTION_TRIGGER_ID;
77
78 QCA C2%ROWTYPE;
79
80 CURSOR CS2 IS
81 SELECT QA_PLAN_CHAR_ACTIONS_S.NEXTVAL FROM DUAL;
82
83 QPC_ACTION_ID NUMBER;
84
85 -- Bug 3111310. Add app_id to WHERE clause of SQL
86 -- to improve performance
87 -- ksoh Fri Aug 22 11:05:00 PST 2003
88 --
89 CURSOR C3 IS
90 SELECT
91 APPLICATION_ID,
92 ACTION_ID,
93 NAME,
94 ALERT_ID,
95 ACTION_TYPE,
96 LAST_UPDATE_DATE,
97 LAST_UPDATED_BY,
98 CREATION_DATE,
99 CREATED_BY,
100 LAST_UPDATE_LOGIN,
101 END_DATE_ACTIVE,
102 ENABLED_FLAG,
103 DESCRIPTION,
104 ACTION_LEVEL_TYPE,
105 DATE_LAST_EXECUTED,
106 FILE_NAME,
107 ARGUMENT_STRING,
108 PROGRAM_APPLICATION_ID,
109 CONCURRENT_PROGRAM_ID,
110 LIST_APPLICATION_ID,
111 LIST_ID,
112 TO_RECIPIENTS,
113 CC_RECIPIENTS,
114 BCC_RECIPIENTS,
115 PRINT_RECIPIENTS,
116 PRINTER,
117 SUBJECT,
118 REPLY_TO,
119 RESPONSE_SET_ID,
120 FOLLOW_UP_AFTER_DAYS,
121 COLUMN_WRAP_FLAG,
122 MAXIMUM_SUMMARY_MESSAGE_WIDTH,
123 BODY,
124 VERSION_NUMBER
125 FROM ALR_ACTIONS
126 WHERE APPLICATION_ID = X_qa_app_id
127 AND ACTION_ID = QCA.ALR_ACTION_ID;
128
129 ALRA C3%ROWTYPE;
130
131 CURSOR CS3 IS
132 SELECT
133 ALR_ACTIONS_S.NEXTVAL,
134 ALR_ACTION_SETS_S.NEXTVAL,
135 ALR_ACTION_SET_MEMBERS_S.NEXTVAL,
136 QA_ALR_ACTION_NAME_S.NEXTVAL,
137 QA_ALR_ACTION_SET_NAME_S.NEXTVAL
138 FROM DUAL;
139
140 NEW_ACTION_ID NUMBER;
141 NEW_ACTION_SET_ID NUMBER;
142 NEW_ACTION_SET_MEMBER_ID NUMBER;
143
144 ACTION_SET_SEQUENCE NUMBER;
145 ACTION_SET_MEMBERS_SEQUENCE NUMBER;
146
147 X_ACTION_NAME NUMBER;
148 X_ACTION_SET_NAME NUMBER;
149 NEW_ACTION_NAME VARCHAR2(80);
150 NEW_ACTION_SET_NAME VARCHAR2(50);
151
152 CURSOR C4 IS
153 SELECT
154 CHAR_ACTION_ID,
155 CHAR_ID,
156 LAST_UPDATE_DATE,
157 LAST_UPDATED_BY,
158 CREATION_DATE,
159 CREATED_BY,
160 LAST_UPDATE_LOGIN,
161 TOKEN_NAME
162 FROM QA_CHAR_ACTION_OUTPUTS
163 WHERE CHAR_ACTION_ID = QCA.CHAR_ACTION_ID;
164
165 QCAO C4%ROWTYPE;
166
167 BEGIN
168
169 -- Initialize "found" flags
170
171 X_values_found := 2; /* no */
172 X_actions_found := 2; /* no */
173
174
175 -- DEFAULT VALUES
176
177 if (X_Copy_Values = 1) then
178
179 -- Delete values
180
181 DELETE FROM qa_plan_char_value_lookups
182 WHERE plan_id = X_Plan_Id and
183 char_id = X_Char_Id;
184
185 -- Insert values
186
187 INSERT INTO qa_plan_char_value_lookups
188 (
189 PLAN_ID,
190 CHAR_ID,
191 SHORT_CODE,
192 LAST_UPDATE_DATE,
193 LAST_UPDATED_BY,
194 CREATION_DATE,
195 CREATED_BY,
196 LAST_UPDATE_LOGIN,
197 DESCRIPTION
198 )
199 SELECT X_Plan_Id,
200 char_id,
201 short_code,
202 X_Last_Update_Date,
203 X_Last_Updated_By,
204 X_Creation_Date,
205 X_Created_By,
206 X_Last_Update_Login,
207 description
208 FROM qa_char_value_lookups
209 WHERE char_id = X_Char_Id;
210
211 if (SQL%ROWCOUNT > 0) then
212 X_values_found := 1; /* yes */
213 end if;
214
215 end if;
216
217
218 -- DEFAULT ACTIONS
219
220 if (X_Copy_Actions = 1) then
221
222 -- Delete existing alr_actions
223 -- Bug 3111310. Rewrite WHERE...EXIST query
224 -- to improve performance
225 -- ksoh Fri Aug 22 11:05:00 PST 2003
226 --
227 DELETE FROM ALR_ACTIONS aa
228 WHERE aa.application_id = X_qa_app_id
229 AND aa.alert_id = X_qa_alert_id
230 AND aa.action_id in
231 (SELECT qpcav.alr_action_id
232 FROM qa_plan_char_actions_v qpcav
233 WHERE PLAN_ID = X_PLAN_ID
234 AND CHAR_ID = X_CHAR_ID);
235
236 -- Delete existing alr_action_sets
237
238 DELETE FROM ALR_ACTION_SETS aas
239 WHERE aas.application_id = X_qa_app_id
240 AND aas.alert_id = X_qa_alert_id
241 AND aas.action_set_id in
242 (SELECT qpcav.alr_action_set_id
243 FROM qa_plan_char_actions_v qpcav
244 WHERE PLAN_ID = X_PLAN_ID
245 AND CHAR_ID = X_CHAR_ID);
246
247 -- Delete existing alr_action_set_members
248
249 DELETE FROM ALR_ACTION_SET_MEMBERS aasm
250 WHERE aasm.application_id = X_qa_app_id
251 AND aasm.alert_id = X_qa_alert_id
252 AND aasm.action_set_id in
253 (SELECT qpcav.alr_action_set_id
254 FROM qa_plan_char_actions_v qpcav
255 WHERE PLAN_ID = X_PLAN_ID
256 AND CHAR_ID = X_CHAR_ID);
257
258 -- Delete existing action triggers
259
260 DELETE FROM qa_plan_char_action_triggers
261 WHERE plan_id = X_Plan_Id and
262 char_id = X_Char_Id;
263
264 -- Delete existing qa_plan_char_actions
265
266 DELETE FROM qa_plan_char_actions
267 WHERE plan_char_action_trigger_id IN
268 (SELECT plan_char_action_trigger_id
269 FROM qa_plan_char_action_triggers
270 WHERE plan_id = X_Plan_Id
271 and char_id = X_Char_Id);
272
273 -- Insert new action triggers
274 -- Insert qa_plan_char_actions
275 -- Insert alr_actions
276 -- Insert alr_action_sets
277 -- Insert alr_action_set_members
278 -- Insert qa_plan_char_action_outputs
279
280 OPEN C1;
281 LOOP
282 FETCH C1 INTO QCAT;
283 EXIT WHEN C1%NOTFOUND;
284
285 OPEN CS1;
286 FETCH CS1 INTO ACTION_TRIGGER_ID;
287 CLOSE CS1;
288
289 INSERT INTO QA_PLAN_CHAR_ACTION_TRIGGERS (
290 PLAN_CHAR_ACTION_TRIGGER_ID,
291 LAST_UPDATE_DATE,
292 LAST_UPDATED_BY,
293 CREATION_DATE,
294 CREATED_BY,
295 TRIGGER_SEQUENCE,
296 PLAN_ID,
297 CHAR_ID,
298 OPERATOR,
299 LOW_VALUE_LOOKUP,
300 HIGH_VALUE_LOOKUP,
301 LOW_VALUE_OTHER,
302 HIGH_VALUE_OTHER,
303 LOW_VALUE_OTHER_ID,
304 HIGH_VALUE_OTHER_ID)
305 VALUES (
306 ACTION_TRIGGER_ID,
307 X_Last_Update_Date,
308 X_Last_Updated_By,
309 X_Creation_Date,
310 X_Created_By,
311 QCAT.TRIGGER_SEQUENCE,
312 X_Plan_Id,
313 QCAT.CHAR_ID,
314 QCAT.OPERATOR,
315 QCAT.LOW_VALUE_LOOKUP,
316 QCAT.HIGH_VALUE_LOOKUP,
317 QCAT.LOW_VALUE_OTHER,
318 QCAT.HIGH_VALUE_OTHER,
319 QCAT.LOW_VALUE_OTHER_ID,
320 QCAT.HIGH_VALUE_OTHER_ID);
321
322 if (X_actions_found = 2) and (SQL%ROWCOUNT > 0) then
323 X_actions_found := 1; /* yes */
324 end if;
325
326 OPEN C2;
327 LOOP
328
329 FETCH C2 INTO QCA;
330 EXIT WHEN C2%NOTFOUND;
331
332 OPEN CS2;
333 FETCH CS2 INTO QPC_ACTION_ID;
334 CLOSE CS2;
335
336 OPEN CS3;
337 FETCH CS3 INTO NEW_ACTION_ID, NEW_ACTION_SET_ID,
338 NEW_ACTION_SET_MEMBER_ID,
339 X_ACTION_NAME, X_ACTION_SET_NAME;
340 CLOSE CS3;
341
342 NEW_ACTION_NAME := 'QA_' || TO_CHAR(X_ACTION_NAME);
343 NEW_ACTION_SET_NAME := 'QA_' || TO_CHAR(X_ACTION_SET_NAME);
344
345 INSERT INTO QA_PLAN_CHAR_ACTIONS (
346 PLAN_CHAR_ACTION_ID,
347 LAST_UPDATE_DATE,
348 LAST_UPDATED_BY,
349 CREATION_DATE,
350 CREATED_BY,
351 PLAN_CHAR_ACTION_TRIGGER_ID,
352 ACTION_ID,
353 CAR_NAME_PREFIX,
354 CAR_TYPE_ID,
355 CAR_OWNER,
356 MESSAGE,
357 STATUS_CODE,
358 STATUS_ID,
359 ALR_ACTION_ID,
360 ALR_ACTION_SET_ID)
361 VALUES (
362 QPC_ACTION_ID,
363 X_Last_Update_Date,
364 X_Last_Updated_By,
365 X_Creation_Date,
366 X_Created_By,
367 ACTION_TRIGGER_ID,
368 QCA.ACTION_ID,
369 QCA.CAR_NAME_PREFIX,
370 QCA.CAR_TYPE_ID,
371 QCA.CAR_OWNER,
372 QCA.MESSAGE,
373 QCA.STATUS_CODE,
374 QCA.STATUS_ID,
375 DECODE (QCA.ACTION_ID,
376 10, NEW_ACTION_ID,
377 11, NEW_ACTION_ID,
378 12, NEW_ACTION_ID,
379 13, NEW_ACTION_ID,
380 NULL),
381 DECODE (QCA.ACTION_ID,
382 10, NEW_ACTION_SET_ID,
383 11, NEW_ACTION_SET_ID,
384 12, NEW_ACTION_SET_ID,
385 13, NEW_ACTION_SET_ID,
386 NULL)
387 );
388
389 OPEN C3;
390 FETCH C3 INTO ALRA;
391
392 IF NOT C3%NOTFOUND THEN
393 INSERT INTO ALR_ACTIONS (
394 APPLICATION_ID,
395 ACTION_ID,
396 NAME,
397 ALERT_ID,
398 ACTION_TYPE,
399 LAST_UPDATE_DATE,
400 LAST_UPDATED_BY,
401 CREATION_DATE,
402 CREATED_BY,
403 END_DATE_ACTIVE,
404 ENABLED_FLAG,
405 DESCRIPTION,
406 ACTION_LEVEL_TYPE,
407 DATE_LAST_EXECUTED,
408 FILE_NAME,
409 ARGUMENT_STRING,
410 PROGRAM_APPLICATION_ID,
411 CONCURRENT_PROGRAM_ID,
412 LIST_APPLICATION_ID,
413 LIST_ID,
414 TO_RECIPIENTS,
415 CC_RECIPIENTS,
416 BCC_RECIPIENTS,
417 PRINT_RECIPIENTS,
418 PRINTER,
419 SUBJECT,
420 REPLY_TO,
421 RESPONSE_SET_ID,
422 FOLLOW_UP_AFTER_DAYS,
423 COLUMN_WRAP_FLAG,
424 MAXIMUM_SUMMARY_MESSAGE_WIDTH,
425 BODY,
426 VERSION_NUMBER)
427 VALUES (
428 ALRA.APPLICATION_ID,
429 NEW_ACTION_ID,
430 NEW_ACTION_NAME,
431 ALRA.ALERT_ID,
432 ALRA.ACTION_TYPE,
433 X_Last_Update_Date,
434 X_Last_Updated_By,
435 X_Creation_Date,
436 X_Created_By,
437 ALRA.END_DATE_ACTIVE,
438 ALRA.ENABLED_FLAG,
439 ALRA.DESCRIPTION,
440 ALRA.ACTION_LEVEL_TYPE,
441 ALRA.DATE_LAST_EXECUTED,
442 ALRA.FILE_NAME,
443 ALRA.ARGUMENT_STRING,
444 ALRA.PROGRAM_APPLICATION_ID,
445 ALRA.CONCURRENT_PROGRAM_ID,
446 ALRA.LIST_APPLICATION_ID,
447 ALRA.LIST_ID,
448 ALRA.TO_RECIPIENTS,
449 ALRA.CC_RECIPIENTS,
450 ALRA.BCC_RECIPIENTS,
451 ALRA.PRINT_RECIPIENTS,
452 ALRA.PRINTER,
453 ALRA.SUBJECT,
454 ALRA.REPLY_TO,
455 ALRA.RESPONSE_SET_ID,
456 ALRA.FOLLOW_UP_AFTER_DAYS,
457 ALRA.COLUMN_WRAP_FLAG,
458 ALRA.MAXIMUM_SUMMARY_MESSAGE_WIDTH,
459 ALRA.BODY,
460 ALRA.VERSION_NUMBER
461 );
462
463 BEGIN
464 SELECT NVL(MAX(SEQUENCE),0)+1
465 INTO ACTION_SET_SEQUENCE
466 FROM ALR_ACTION_SETS
467 WHERE APPLICATION_ID = 250
468 AND ALERT_ID = 10177;
469
470 EXCEPTION
471 WHEN NO_DATA_FOUND THEN
472 ACTION_SET_SEQUENCE := 1;
473 END;
474
475 INSERT INTO ALR_ACTION_SETS (
476 APPLICATION_ID,
477 ACTION_SET_ID,
478 NAME,
479 ALERT_ID,
480 LAST_UPDATE_DATE,
481 LAST_UPDATED_BY,
482 CREATION_DATE,
483 CREATED_BY,
484 END_DATE_ACTIVE,
485 ENABLED_FLAG,
486 RECIPIENTS_VIEW_ONLY_FLAG,
487 DESCRIPTION,
488 SUPPRESS_FLAG,
489 SUPPRESS_DAYS,
490 SEQUENCE)
491 VALUES (
492 250,
493 NEW_ACTION_SET_ID,
494 NEW_ACTION_SET_NAME,
495 10177,
496 X_Last_Update_Date,
497 X_Last_Updated_By,
498 X_Creation_Date,
499 X_Created_By,
500 NULL,
501 'Y',
502 'N',
503 NEW_ACTION_SET_NAME,
504 'N',
505 NULL,
506 ACTION_SET_SEQUENCE
507 );
508
509 BEGIN
510 SELECT NVL(MAX(SEQUENCE),0)+1
511 INTO ACTION_SET_MEMBERS_SEQUENCE
512 FROM ALR_ACTION_SET_MEMBERS
513 WHERE APPLICATION_ID = 250
514 AND ALERT_ID = 10177
515 AND ACTION_SET_ID = NEW_ACTION_SET_ID;
516 EXCEPTION
517 WHEN NO_DATA_FOUND THEN
518 ACTION_SET_MEMBERS_SEQUENCE := 1;
519 END;
520
521 INSERT INTO ALR_ACTION_SET_MEMBERS (
522 APPLICATION_ID,
523 ACTION_SET_MEMBER_ID,
524 ACTION_SET_ID,
525 ACTION_ID,
526 ACTION_GROUP_ID,
527 ALERT_ID,
528 SEQUENCE,
529 LAST_UPDATE_DATE,
530 LAST_UPDATED_BY,
531 CREATION_DATE,
532 CREATED_BY,
533 END_DATE_ACTIVE,
534 ENABLED_FLAG,
535 SUMMARY_THRESHOLD,
536 ABORT_FLAG,
537 ERROR_ACTION_SEQUENCE)
538 VALUES (
539 250,
540 NEW_ACTION_SET_MEMBER_ID,
541 NEW_ACTION_SET_ID,
542 NEW_ACTION_ID,
543 NULL,
544 10177,
545 ACTION_SET_MEMBERS_SEQUENCE,
546 X_Last_Update_Date,
547 X_Last_Updated_By,
548 X_Creation_Date,
549 X_Created_By,
550 NULL,
551 'Y',
552 NULL,
553 'A',
554 NULL
555 );
556
557 OPEN C4;
558 LOOP
559 FETCH C4 INTO QCAO;
560 EXIT WHEN C4%NOTFOUND;
561
562 INSERT INTO QA_PLAN_CHAR_ACTION_OUTPUTS (
563 PLAN_CHAR_ACTION_ID,
564 CHAR_ID,
565 LAST_UPDATE_DATE,
566 LAST_UPDATED_BY,
567 CREATION_DATE,
568 CREATED_BY,
569 TOKEN_NAME)
570 VALUES (
571 QPC_ACTION_ID,
572 QCAO.CHAR_ID,
573 X_Last_Update_Date,
574 X_Last_Updated_By,
575 X_Creation_Date,
576 X_Created_By,
577 QCAO.TOKEN_NAME
578 );
579
580 END LOOP;
581 CLOSE C4;
582
583 END IF;
584
585 CLOSE C3;
586
587 END LOOP;
588 CLOSE C2;
589
590 END LOOP;
591 CLOSE C1;
592
593 end if;
594
595
596 EXCEPTION
597
598 WHEN NO_DATA_FOUND then
599 null;
600
601 END Insert_Rows;
602
603
604 END QLTCPDFB;