[Home] [Help]
PACKAGE BODY: APPS.QLTAUFLB
Source
1 PACKAGE BODY QLTAUFLB AS
2 /* $Header: qltauflb.plb 120.4.12000000.2 2007/02/20 23:27:48 shkalyan ship $ */
3
4 -- Fill in missing elements/actions for an Inspection Plan.
5 -- 07/31/97
6 -- Munazza Bukhari
7
8 -- SQL Repository Bug 4958757
9 --
10 -- The cursors c3 and c7 are identical, they are consolidated
11 -- into one c_result_columns to promote code reuse and to avoid
12 -- SQL re-parsing.
13 --
14 -- bso Thu Feb 2 15:27:03 PST 2006
15 --
16
17 CURSOR c_result_columns(p_plan_id NUMBER) IS
18 SELECT to_number(substr(result_column_name,10)) res_column_name
19 FROM qa_plan_chars
20 WHERE plan_id = p_plan_id AND
21 result_column_name like 'CHARACTER%'
22 ORDER BY to_number(substr(result_column_name,10));
23
24
25 -- SQL Repository Bug 4958757
26 --
27 -- The original cursors c5, c1 and Mobile_Inspection_Elements
28 -- are condensed into one single SQL c_plan_chars to promote
29 -- code re-use and to avoid re-parsing.
30 --
31 -- Since a list of char_ids are expected, to avoid literals,
32 -- we will be using the qa_performance_temp_pkg package to
33 -- keep a list of char_ids temporarily.
34 --
35 -- The params to the cursor are:
36 --
37 -- p_from_plan_id NUMBER The copy-from plan ID
38 -- p_to_plan_id NUMBER The copy-to plan ID
39 -- p_char_id_key VARCHAR2 The key to the performance temp table
40 --
41 -- bso Thu Feb 2 15:02:46 PST 2006
42 --
43 -- Bug # 3329507. Modifying the cursor to include read_only,
44 -- Self Service and Information flags.
45 -- saugupta Fri, 26 Dec 2003 04:16:08 -0800 PDT
46
47 CURSOR c_plan_chars(
48 p_from_plan_id NUMBER,
49 p_to_plan_id NUMBER,
50 p_char_id_key VARCHAR2) IS
51 SELECT
52 qpc.plan_id,
53 qpc.char_id,
54 qc.name char_name, -- bug 3926150 needs char_name
55 qpc.last_update_date,
56 qpc.last_updated_by,
57 qpc.creation_date,
58 qpc.created_by,
59 qpc.prompt_sequence,
60 qpc.prompt,
61 qpc.enabled_flag,
62 qpc.mandatory_flag,
63 qpc.read_only_flag,
64 qpc.ss_poplist_flag,
65 qpc.information_flag,
66 qpc.default_value,
67 qc.hardcoded_column,
68 qpc.result_column_name,
69 qpc.values_exist_flag,
70 qpc.displayed_flag,
71 qpc.attribute_category,
72 qpc.attribute1,
73 qpc.attribute2,
74 qpc.attribute3,
75 qpc.attribute4,
76 qpc.attribute5,
77 qpc.attribute6,
78 qpc.attribute7,
79 qpc.attribute8,
80 qpc.attribute9,
81 qpc.attribute10,
82 qpc.attribute11,
83 qpc.attribute12,
84 qpc.attribute13,
85 qpc.attribute14,
86 qpc.attribute15,
87 qpc.default_value_id
88 FROM
89 qa_plan_chars qpc,
90 qa_chars qc
91 WHERE
92 qpc.plan_id = p_from_plan_id AND
93 qpc.char_id IN (
94 SELECT id
95 FROM qa_performance_temp
96 WHERE key = p_char_id_key) AND
97 qpc.char_id NOT IN (
98 SELECT char_id
99 FROM qa_plan_chars
100 WHERE plan_id = p_to_plan_id) AND
101 qpc.char_id = qc.char_id
102 ORDER BY
103 qpc.prompt_sequence;
104
105 --
106 -- Bug 3926150 need to add a new out param (X_DISABLED_INDEXED_ELEMENTS).
107 -- This proc is called from two places:
108 -- QLTPLMDF.QP_TRANSACTIONS_CONTROL various procedures
109 -- QASLSET.pld
110 --
111 FUNCTION auto_fill_missing_char (X_PLAN_ID NUMBER,
112 X_COPY_PLAN_ID NUMBER,
113 X_USER_ID NUMBER,
114 X_DISABLED_INDEXED_ELEMENTS OUT NOCOPY VARCHAR2) RETURN NUMBER IS
115
116 --
117 -- Bug 3926150 add new local variable.
118 -- When defaulting a required element, we should check to
119 -- see if the result_column_name assigned clashes with a
120 -- function based index. If so, we will disable the index
121 -- and collect the element name here in a comma-separated
122 -- list. Since this whole package is not modular, we need
123 -- to do a shortcut by only modifying this auto_fill_missing_char
124 -- function because only it contains softcoded elements, namely
125 -- 8 (Inspection Results) and 112 (UOM Name).
126 --
127 -- Because of the small no. of softcoded elements involved,
128 -- we will also shortcut by not attempting the default result
129 -- column name, and directly move on to just testing for
130 -- clashes. (As oppose to Copy Element (qltcpplb) where we
131 -- try to not disturb the index as much as possible.
132 --
133 -- bso Wed Dec 1 22:07:59 PST 2004
134 --
135 l_disabled_indexed_elements VARCHAR2(3000);
136 l_default_column VARCHAR2(30);
137 dummy NUMBER;
138
139 l_key VARCHAR2(30); -- Bug 4958757 Performance Temp Key
140
141 --
142 -- Bug# 5739330. This cursor fetches all the trigger rules.
143 -- Need only the ones that are missing.
144 -- SHKALYAN. 20-Feb-2007.
145 --
146 /*CURSOR C1 IS
147 SELECT
148 PLAN_CHAR_ACTION_TRIGGER_ID,
149 LAST_UPDATE_DATE,
150 LAST_UPDATED_BY,
151 CREATION_DATE,
152 CREATED_BY,
153 TRIGGER_SEQUENCE,
154 PLAN_ID,
155 CHAR_ID,
156 OPERATOR,
157 LOW_VALUE_LOOKUP,
158 HIGH_VALUE_LOOKUP,
159 LOW_VALUE_OTHER,
160 HIGH_VALUE_OTHER,
161 LOW_VALUE_OTHER_ID,
162 HIGH_VALUE_OTHER_ID
163 FROM QA_PLAN_CHAR_ACTION_TRIGGERS
164 WHERE PLAN_ID = X_COPY_PLAN_ID;*/
165 -- AND PLAN_CHAR_ACTION_TRIGGER_ID NOT IN
166 -- (SELECT PLAN_CHAR_ACTION_TRIGGER_ID
167 -- FROM QA_PLAN_CHAR_ACTION_TRIGGERS
168 -- WHERE PLAN_ID = X_PLAN_ID)
169 -- ORDER BY TRIGGER_SEQUENCE,
170 -- PLAN_CHAR_ACTION_TRIGGER_ID;
171
172
173 --
174 -- Bug# 5739330. This cursor fetches only the missing trigger rules
175 -- that are neccessary for enabling Inspection Transaction.
176 -- SHKALYAN. 20-Feb-2007.
177 --
178 CURSOR C1 IS
179 SELECT a.plan_char_action_trigger_id,
180 a.last_update_date,
181 a.last_updated_by,
182 a.creation_date,
183 a.created_by,
184 a.trigger_sequence,
185 a.plan_id,
186 a.char_id,
187 a.operator,
188 a.low_value_lookup,
189 a.high_value_lookup,
190 a.low_value_other,
191 a.high_value_other,
192 a.low_value_other_id,
193 a.high_value_other_id
194 FROM qa_plan_char_action_triggers a
195 WHERE NOT EXISTS
196 (SELECT 1
197 FROM qa_plan_char_action_triggers b
198 WHERE b.plan_id = X_PLAN_ID
199 AND a.char_id = b.char_id
200 --AND nvl(a.trigger_sequence, 0) = nvl(b.trigger_sequence, 0)
201 AND nvl(a.operator, 0) = nvl(b.operator, 0)
202 AND nvl(a.low_value_lookup, 0) = nvl(b.low_value_lookup, 0)
203 AND nvl(b.low_value_other, 0) = decode(nvl(a.low_value_other, 0),
204 'ACCEPT',
205 (SELECT DISPLAYED_FIELD
206 FROM PO_LOOKUP_CODES
207 WHERE LOOKUP_TYPE = 'ERT RESULTS ACTION'
208 AND LOOKUP_CODE = a.low_value_other),
209 'REJECT',
210 (SELECT DISPLAYED_FIELD
211 FROM PO_LOOKUP_CODES
212 WHERE LOOKUP_TYPE = 'ERT RESULTS ACTION'
213 AND LOOKUP_CODE = a.low_value_other),
214 nvl(a.low_value_other, 0))
215 AND nvl(a.high_value_other, 0) = nvl(b.high_value_other, 0)
216 AND nvl(a.high_value_lookup, 0) = nvl(b.high_value_lookup, 0)
217 AND nvl(a.low_value_other_id, 0) = nvl(b.low_value_other_id, 0)
218 AND nvl(a.high_value_other_id, 0) = nvl(b.high_value_other_id, 0))
219 AND a.plan_id = X_COPY_PLAN_ID;
220
221 CURSOR CS1 IS
222 SELECT QA_PLAN_CHAR_ACTION_TRIGGERS_S.NEXTVAL FROM DUAL;
223
224 ACTION_TRIGGER_ID NUMBER;
225
226 QPCAT C1%ROWTYPE;
227
228 CURSOR C2 IS
229 SELECT
230 PLAN_CHAR_ACTION_ID,
231 LAST_UPDATE_DATE,
232 LAST_UPDATED_BY,
233 CREATION_DATE,
234 CREATED_BY,
235 PLAN_CHAR_ACTION_TRIGGER_ID,
236 ACTION_ID,
237 CAR_NAME_PREFIX,
238 CAR_TYPE_ID,
239 CAR_OWNER,
240 MESSAGE,
241 STATUS_CODE,
242 STATUS_ID,
243 ALR_ACTION_ID,
244 ALR_ACTION_SET_ID,
245 ASSIGNED_CHAR_ID,
246 ASSIGN_TYPE
247 FROM QA_PLAN_CHAR_ACTIONS
248 WHERE PLAN_CHAR_ACTION_TRIGGER_ID = QPCAT.PLAN_CHAR_ACTION_TRIGGER_ID
249 ORDER BY PLAN_CHAR_ACTION_ID;
250
251 QPCA C2%ROWTYPE;
252
253 CURSOR CS2 IS
254 SELECT QA_PLAN_CHAR_ACTIONS_S.NEXTVAL FROM DUAL;
255
256 QPC_ACTION_ID NUMBER;
257
258 --
259 -- Bug# 5739330. Added the cursor C8 and C9. C8 fetches
260 -- plan_char_action_trigger_ids from the template plan and the
261 -- corresponding plan which needs the required actions. This is
262 -- the driving cursor which faciliates cursor C9 to get the missing actions
263 -- for the particular plan_char_action_trigger_id
264 -- SHKALYAN. 20-Feb-2007.
265 --
266 CURSOR C8 IS
267 SELECT a.plan_char_action_trigger_id temp_action_trigger_id,
268 b.plan_char_action_trigger_id action_trigger_id
269 FROM qa_plan_char_action_triggers a, qa_plan_char_action_triggers b
270 WHERE a.char_id = b.char_id
271 AND nvl(a.operator, 0) = nvl(b.operator, 0)
272 AND nvl(a.low_value_lookup, 0) = nvl(b.low_value_lookup, 0)
273 AND nvl(b.low_value_other, 0) = decode(nvl(a.low_value_other, 0),
274 'ACCEPT',
275 (SELECT DISPLAYED_FIELD
276 FROM PO_LOOKUP_CODES
277 WHERE LOOKUP_TYPE = 'ERT RESULTS ACTION'
278 AND LOOKUP_CODE = a.low_value_other),
279 'REJECT',
280 (SELECT DISPLAYED_FIELD
281 FROM PO_LOOKUP_CODES
282 WHERE LOOKUP_TYPE = 'ERT RESULTS ACTION'
283 AND LOOKUP_CODE = a.low_value_other),
284 nvl(a.low_value_other, 0))
285 AND nvl(a.high_value_other, 0) = nvl(b.high_value_other, 0)
286 AND nvl(a.high_value_lookup, 0) = nvl(b.high_value_lookup, 0)
287 AND nvl(a.low_value_other_id, 0) = nvl(b.low_value_other_id, 0)
288 AND nvl(a.high_value_other_id, 0) = nvl(b.high_value_other_id, 0)
289 AND b.plan_id = X_PLAN_ID
290 AND a.plan_id = X_COPY_PLAN_ID;
291
292 QPCAT_TEMP C8%ROWTYPE;
293
294 CURSOR C9 IS
295 SELECT ACTION_ID,
296 CAR_NAME_PREFIX,
297 CAR_TYPE_ID,
298 CAR_OWNER,
299 MESSAGE,
300 STATUS_CODE,
301 STATUS_ID,
302 ALR_ACTION_ID,
303 ALR_ACTION_SET_ID,
304 ASSIGNED_CHAR_ID,
305 ASSIGN_TYPE
306 FROM qa_plan_char_actions a,
307 qa_plan_char_action_triggers c
308 WHERE NOT EXISTS
309 (SELECT 1
310 FROM qa_plan_char_actions b,
311 qa_plan_char_action_triggers d
312 WHERE nvl(a.action_id, 0) = nvl(b.action_id, 0)
313 AND nvl(a.car_name_prefix, 0) = nvl(b.car_name_prefix, 0)
314 AND nvl(a.car_type_id, 0) = nvl(b.car_type_id, 0)
315 AND nvl(a.car_owner, 0) = nvl(b.car_owner, 0)
316 AND nvl(a.message, 0) = nvl(b.message, 0)
317 AND nvl(a.status_code, 0) = nvl(b.status_code, 0)
318 AND nvl(a.alr_action_id, 0) = nvl(b.alr_action_id, 0)
319 AND nvl(a.alr_action_set_id, 0) = nvl(b.alr_action_set_id, 0)
320 AND nvl(a.assigned_char_id, 0) = nvl(b.assigned_char_id, 0)
321 AND nvl(a.assign_type, 0) = nvl(b.assign_type, 0)
322 AND d.plan_id = x_plan_id
323 AND b.plan_char_action_trigger_id = d.plan_char_action_trigger_id
324 AND b.plan_char_action_trigger_id = qpcat_temp.action_trigger_id)
325 AND a.plan_char_action_trigger_id = c.plan_char_action_trigger_id
326 AND c.plan_char_action_trigger_id = qpcat_temp.temp_action_trigger_id
327 AND c.plan_id = x_copy_plan_id;
328
329 QPCA_TEMP C9%ROWTYPE;
330
331
332 -- Bug 4958757. SQL Repository Fix SQL ID: 15008068
333 -- The cursor c5 is completed removed and replaced by
334 -- a generic package level c_plan_chars cursor.
335 -- bso Thu Feb 2 15:04:35 PST 2006
336
337 QPCV c_plan_chars%ROWTYPE;
338
339 -- Bug 4958757. SQL Repository Fix SQL ID: 15008087
340 -- Cursor Mobile_Inspection_Elements completely removed
341 -- and replaced by a generic c_plan_chars cursor.
342 -- bso Thu Feb 2 15:02:19 PST 2006
343
344 MIE c_plan_chars%ROWTYPE;
345
346 -- Bug 4958757. SQL Repository Fix SQL ID: 15008119
347 -- The cursor c7 has been extracted to package level as
348 -- c_result_columns.
349
350 TYPE column_num IS TABLE of BOOLEAN
351 INDEX BY BINARY_INTEGER;
352
353 res_columns column_num;
354 i binary_integer;
355
356 COLUMN_NAME VARCHAR2(30);
357 COLUMN_NUMBER NUMBER;
358 NEW_RESULT_COLUMN_NAME VARCHAR2(30);
359
360 CURSOR C6 IS
361 SELECT MAX(PROMPT_SEQUENCE) FROM QA_PLAN_CHARS
362 WHERE PLAN_ID = X_PLAN_ID;
363
364 NEW_PROMPT_SEQUENCE NUMBER;
365
366 BEGIN
367
368 -- Insert child values
369
370 INSERT INTO QA_PLAN_CHAR_VALUE_LOOKUPS (
371 PLAN_ID,
372 CHAR_ID,
373 SHORT_CODE,
374 LAST_UPDATE_DATE,
375 LAST_UPDATED_BY,
376 CREATION_DATE,
377 CREATED_BY,
378 DESCRIPTION,
379 SHORT_CODE_ID)
380 SELECT
381 X_PLAN_ID,
382 CHAR_ID,
383 SHORT_CODE,
384 SYSDATE,
385 X_USER_ID,
386 SYSDATE,
387 X_USER_ID,
388 DESCRIPTION,
389 SHORT_CODE_ID
390 FROM QA_PLAN_CHAR_VALUE_LOOKUPS
391 WHERE PLAN_ID = X_COPY_PLAN_ID
392 AND CHAR_ID NOT IN (SELECT CHAR_ID FROM QA_PLAN_CHARS
393 WHERE PLAN_ID = X_PLAN_ID);
394
395 --
396 -- Bug# 5739330. These statements delete only the actions 25 and 26 and
397 -- then the parent trigger. The parent trigger should not be deleted
398 -- since there can be other actions associated to it which will get orphaned
399 -- SHKALYAN. 20-Feb-2007.
400 --
401
402 /*DELETE FROM qa_plan_char_actions
403 WHERE action_id in (25, 26)
404 AND plan_char_action_trigger_id IN
405 (SELECT plan_char_action_trigger_id
406 FROM qa_plan_char_action_triggers
407 WHERE plan_id = X_Plan_id
408 AND char_id = 8
409 AND low_value_other IN
410 (SELECT displayed_field
411 FROM po_lookup_codes
412 WHERE lookup_type='ERT RESULTS ACTION'));
413
414 DELETE FROM qa_plan_char_action_triggers
415 WHERE plan_id = X_Plan_id
416 AND char_id = 8
417 AND low_value_other IN
418 (SELECT displayed_field
419 FROM po_lookup_codes
420 WHERE lookup_type='ERT RESULTS ACTION');*/
421
422 --
423 -- Bug# 5739330.
424 -- Added this part of code to insert the missing actions fetched
425 -- from cursor C9 and inserting it into QA_PLAN_CHAR_ACTIONS
426 -- The C8 cursor gets the action_triggers which are already present
427 -- in the plan and it is used for getting the actions in C9
428 -- SHKALYAN. 20-Feb-2007.
429 --
430 OPEN C8;
431 LOOP
432 FETCH C8 INTO QPCAT_TEMP;
433 EXIT WHEN C8%NOTFOUND;
434
435 OPEN C9;
436 LOOP
437 FETCH C9 INTO QPCA_TEMP;
438 EXIT WHEN C9%NOTFOUND;
439
440 OPEN CS2;
441 FETCH CS2 INTO QPC_ACTION_ID;
442 CLOSE CS2;
443
444 INSERT INTO QA_PLAN_CHAR_ACTIONS
445 (PLAN_CHAR_ACTION_ID,
446 LAST_UPDATE_DATE,
447 LAST_UPDATED_BY,
448 CREATION_DATE,
449 CREATED_BY,
450 PLAN_CHAR_ACTION_TRIGGER_ID,
451 ACTION_ID,
452 CAR_NAME_PREFIX,
453 CAR_TYPE_ID,
454 CAR_OWNER,
455 MESSAGE,
456 STATUS_CODE,
457 STATUS_ID,
458 ASSIGNED_CHAR_ID,
459 ASSIGN_TYPE)
460 VALUES
461 (QPC_ACTION_ID,
462 SYSDATE,
463 X_USER_ID,
464 SYSDATE,
465 X_USER_ID,
466 QPCAT_TEMP.ACTION_TRIGGER_ID,
467 QPCA_TEMP.ACTION_ID,
468 QPCA_TEMP.CAR_NAME_PREFIX,
469 QPCA_TEMP.CAR_TYPE_ID,
470 QPCA_TEMP.CAR_OWNER,
471 QPCA_TEMP.MESSAGE,
472 QPCA_TEMP.STATUS_CODE,
473 QPCA_TEMP.STATUS_ID,
474 QPCA_TEMP.ASSIGNED_CHAR_ID,
475 QPCA_TEMP.ASSIGN_TYPE);
476
477 END LOOP;
478 CLOSE C9;
479
480 END LOOP;
481 CLOSE C8;
482
483 OPEN C1;
484 LOOP
485 FETCH C1 INTO QPCAT;
486 EXIT WHEN C1%NOTFOUND;
487
488 OPEN CS1;
489 FETCH CS1 INTO ACTION_TRIGGER_ID;
490 CLOSE CS1;
491
492 INSERT INTO QA_PLAN_CHAR_ACTION_TRIGGERS (
493 PLAN_CHAR_ACTION_TRIGGER_ID,
494 LAST_UPDATE_DATE,
495 LAST_UPDATED_BY,
496 CREATION_DATE,
497 CREATED_BY,
498 TRIGGER_SEQUENCE,
499 PLAN_ID,
500 CHAR_ID,
501 OPERATOR,
502 LOW_VALUE_LOOKUP,
503 HIGH_VALUE_LOOKUP,
504 LOW_VALUE_OTHER,
505 HIGH_VALUE_OTHER,
506 LOW_VALUE_OTHER_ID,
507 HIGH_VALUE_OTHER_ID)
508 VALUES (
509 ACTION_TRIGGER_ID,
510 SYSDATE,
511 X_USER_ID,
512 SYSDATE,
513 X_USER_ID,
514 QPCAT.TRIGGER_SEQUENCE,
515 X_PLAN_ID,
516 QPCAT.CHAR_ID,
517 QPCAT.OPERATOR,
518 QPCAT.LOW_VALUE_LOOKUP,
519 QPCAT.HIGH_VALUE_LOOKUP,
520 QPCAT.LOW_VALUE_OTHER,
521 QPCAT.HIGH_VALUE_OTHER,
522 QPCAT.LOW_VALUE_OTHER_ID,
523 QPCAT.HIGH_VALUE_OTHER_ID);
524
525 --
526 -- Bug 2698812
527 -- Avoided the earlier used Cursor to read
528 -- from PO lookup table. Using variables instead
529 --
530 -- rkunchal Sat Jan 4 02:06:10 PST 2003
531 --
532 -- Bug 5300577
533 -- Included Template OPM Recieving inspection plan because
534 -- conversion is required for these plans as well.
535
536 IF X_COPY_PLAN_ID IN (1,2147483637) AND QPCAT.LOW_VALUE_OTHER IN ('ACCEPT', 'REJECT') THEN
537 UPDATE QA_PLAN_CHAR_ACTION_TRIGGERS
538 SET LOW_VALUE_OTHER = (SELECT DISPLAYED_FIELD
539 FROM PO_LOOKUP_CODES
540 WHERE LOOKUP_TYPE = 'ERT RESULTS ACTION'
541 AND LOOKUP_CODE = QPCAT.LOW_VALUE_OTHER)
542 WHERE PLAN_CHAR_ACTION_TRIGGER_ID = ACTION_TRIGGER_ID;
543 END IF;
544
545 OPEN C2;
546 LOOP
547
548 FETCH C2 INTO QPCA;
549 EXIT WHEN C2%NOTFOUND;
550
551 OPEN CS2;
552 FETCH CS2 INTO QPC_ACTION_ID;
553 CLOSE CS2;
554
555 INSERT INTO QA_PLAN_CHAR_ACTIONS (
556 PLAN_CHAR_ACTION_ID,
557 LAST_UPDATE_DATE,
558 LAST_UPDATED_BY,
559 CREATION_DATE,
560 CREATED_BY,
561 PLAN_CHAR_ACTION_TRIGGER_ID,
562 ACTION_ID,
563 CAR_NAME_PREFIX,
564 CAR_TYPE_ID,
565 CAR_OWNER,
566 MESSAGE,
567 STATUS_CODE,
568 STATUS_ID,
569 ASSIGNED_CHAR_ID,
570 ASSIGN_TYPE)
571 VALUES (
572 QPC_ACTION_ID,
573 SYSDATE,
574 X_USER_ID,
575 SYSDATE,
576 X_USER_ID,
577 ACTION_TRIGGER_ID,
578 QPCA.ACTION_ID,
579 QPCA.CAR_NAME_PREFIX,
580 QPCA.CAR_TYPE_ID,
581 QPCA.CAR_OWNER,
582 QPCA.MESSAGE,
583 QPCA.STATUS_CODE,
584 QPCA.STATUS_ID,
585 QPCA.ASSIGNED_CHAR_ID,
586 QPCA.ASSIGN_TYPE
587 );
588
589 END LOOP;
590 CLOSE C2;
591
592
593 END LOOP;
594 CLOSE C1;
595
596
597 -- Put this insert statement AFTER all the children have been inserted
598 -- so that the last line of the where clauses will work in the above
599 -- statements; i.e. if you insert the plan-chars first, none of the
600 -- children will ever get copied over.
601
602 OPEN C6;
603 FETCH C6 INTO NEW_PROMPT_SEQUENCE;
604 CLOSE C6;
605
606 IF NEW_PROMPT_SEQUENCE IS NULL THEN
607 NEW_PROMPT_SEQUENCE := 0;
608 END IF;
609
610 FOR i IN 1..QLTNINRB.RES_CHAR_COLUMNS LOOP
611 res_columns(i) := FALSE;
612 END LOOP;
613
614 OPEN c_result_columns(x_plan_id);
615 LOOP
616 FETCH c_result_columns INTO i;
617 EXIT WHEN c_result_columns%NOTFOUND;
618
619 res_columns(i) := TRUE;
620 END LOOP;
621 CLOSE c_result_columns;
622
623 i := 1;
624
625 -- This branching was put in to enforce the transaction
626 -- requirement for mobile inspection transactions.
627
628 IF (x_copy_plan_id = 58) THEN
629
630 -- Bug 4958757 SQL Repository tuning.
631 l_key := 'QLTAUFLB.C_MOBILE_INSPECT';
632 qa_performance_temp_pkg.purge_and_add_ids(
633 p_key => l_key,
634 p_id_list => qa_ss_const.QUANTITY || ',' ||
635 qa_ss_const.INSPECTION_RESULT || ',' ||
636 qa_ss_const.ITEM || ',' ||
637 qa_ss_const.UOM);
638
639 OPEN c_plan_chars(x_copy_plan_id, x_plan_id, l_key);
640 LOOP
641
642 FETCH c_plan_chars INTO MIE;
643 EXIT WHEN c_plan_chars%NOTFOUND;
644
645 IF MIE.HARDCODED_COLUMN IS NOT NULL THEN
646 NEW_RESULT_COLUMN_NAME := MIE.HARDCODED_COLUMN;
647 ELSE
648 -- Find the first available column number
649 WHILE ((res_columns(i) = TRUE) AND (i <=
650 QLTNINRB.RES_CHAR_COLUMNS)) LOOP
651 i := i + 1;
652 END LOOP;
653
654 IF i > QLTNINRB.RES_CHAR_COLUMNS THEN
655 -- Exceeded upper limit of maximum number of chars.
656 -- Error out.
657 FND_MESSAGE.SET_NAME('QA', 'QA_EXCEEDED_COLUMN_COUNT');
658 APP_EXCEPTION.RAISE_EXCEPTION;
659 END IF;
660
661 NEW_RESULT_COLUMN_NAME := 'CHARACTER' || TO_CHAR(i);
662
663 --
664 -- Bug 3926150. Check if index is enabled and this
665 -- assignment disrupts the index or not.
666 --
667 l_default_column :=
668 qa_char_indexes_pkg.get_default_result_column(mie.char_id);
669 IF l_default_column IS NOT NULL AND
670 l_default_column <> new_result_column_name THEN
671 --
672 -- Need to warn user because we can't reuse the index.
673 --
674 l_disabled_indexed_elements :=
675 l_disabled_indexed_elements || ', ' || mie.char_name;
676 dummy := qa_char_indexes_pkg.disable_index(mie.char_id);
677 END IF;
678
679 res_columns(i) := TRUE;
680 i := i + 1;
681 END IF;
682
683 NEW_PROMPT_SEQUENCE := NEW_PROMPT_SEQUENCE + 10;
684
685 -- Bug # 3329507. Modifying the query to include read_only,
686 -- Self Service and Information flags.
687 -- saugupta Fri, 26 Dec 2003 04:16:08 -0800 PDT
688
689 INSERT INTO QA_PLAN_CHARS (
690 plan_id,
691 char_id,
692 last_update_date,
693 last_updated_by,
694 creation_date,
695 created_by,
696 prompt_sequence,
697 prompt,
698 enabled_flag,
699 mandatory_flag,
700 read_only_flag,
701 ss_poplist_flag,
702 information_flag,
703 default_value,
704 result_column_name,
705 values_exist_flag,
706 displayed_flag,
707 attribute_category,
708 attribute1,
709 attribute2,
710 attribute3,
711 attribute4,
712 attribute5,
713 attribute6,
714 attribute7,
715 attribute8,
716 attribute9,
717 attribute10,
718 attribute11,
719 attribute12,
720 attribute13,
721 attribute14,
722 attribute15,
723 default_value_id)
724 VALUES (
725 X_PLAN_ID,
726 MIE.CHAR_ID,
727 SYSDATE,
728 X_USER_ID,
729 SYSDATE,
730 X_USER_ID,
731 NEW_PROMPT_SEQUENCE,
732 MIE.PROMPT,
733 MIE.ENABLED_FLAG,
734 MIE.MANDATORY_FLAG,
735 MIE.READ_ONLY_FLAG,
736 MIE.SS_POPLIST_FLAG,
737 MIE.INFORMATION_FLAG,
738 MIE.DEFAULT_VALUE,
739 NEW_RESULT_COLUMN_NAME,
740 MIE.VALUES_EXIST_FLAG,
741 MIE.DISPLAYED_FLAG,
742 MIE.ATTRIBUTE_CATEGORY,
743 MIE.ATTRIBUTE1,
744 MIE.ATTRIBUTE2,
745 MIE.ATTRIBUTE3,
746 MIE.ATTRIBUTE4,
747 MIE.ATTRIBUTE5,
748 MIE.ATTRIBUTE6,
749 MIE.ATTRIBUTE7,
750 MIE.ATTRIBUTE8,
751 MIE.ATTRIBUTE9,
752 MIE.ATTRIBUTE10,
753 MIE.ATTRIBUTE11,
754 MIE.ATTRIBUTE12,
755 MIE.ATTRIBUTE13,
756 MIE.ATTRIBUTE14,
757 MIE.ATTRIBUTE15,
758 MIE.DEFAULT_VALUE_ID);
759
760 END LOOP;
761 CLOSE c_plan_chars;
762
763 --
764 -- Bug 3926150
765 -- Pass back the disabled index names. (use substr to get rid of
766 -- the lead comma and space.
767 --
768 IF l_disabled_indexed_elements IS NOT NULL THEN
769 x_disabled_indexed_elements := substr(l_disabled_indexed_elements, 3);
770 END IF;
771
772 IF NEW_PROMPT_SEQUENCE > 0 THEN
773 RETURN(NEW_PROMPT_SEQUENCE + 10);
774 ELSE
775 RETURN(NEW_PROMPT_SEQUENCE );
776 END IF;
777
778 ELSE
779
780 -- Bug 4958757 SQL Repository tuning.
781 l_key := 'QLTAUFLB.C5';
782 qa_performance_temp_pkg.purge_and_add_ids(
783 p_key => l_key,
784 p_id_list => qa_ss_const.TRANSACTION_DATE || ',' ||
785 qa_ss_const.QUANTITY || ',' ||
786 qa_ss_const.INSPECTION_RESULT || ',' ||
787 qa_ss_const.UOM_NAME);
788
789 OPEN c_plan_chars(x_copy_plan_id, x_plan_id, l_key);
790 LOOP
791
792 FETCH c_plan_chars INTO QPCV;
793 EXIT WHEN c_plan_chars%NOTFOUND;
794
795 IF QPCV.HARDCODED_COLUMN IS NOT NULL THEN
796 NEW_RESULT_COLUMN_NAME := QPCV.HARDCODED_COLUMN;
797 ELSE
798 -- Find the first available column number
799 WHILE ((res_columns(i) = TRUE) AND (i <=
800 QLTNINRB.RES_CHAR_COLUMNS)) LOOP
801 i := i + 1;
802 END LOOP;
803
804 IF i > QLTNINRB.RES_CHAR_COLUMNS THEN
805 -- Exceeded upper limit of maximum number of chars.
806 -- Error out.
807 FND_MESSAGE.SET_NAME('QA', 'QA_EXCEEDED_COLUMN_COUNT');
808 APP_EXCEPTION.RAISE_EXCEPTION;
809 END IF;
810
811 NEW_RESULT_COLUMN_NAME := 'CHARACTER' || TO_CHAR(i);
812
813 --
814 -- Bug 3926150. Check if index is enabled and this
815 -- assignment disrupts the index or not.
816 --
817 l_default_column :=
818 qa_char_indexes_pkg.get_default_result_column(qpcv.char_id);
819 IF l_default_column IS NOT NULL AND
820 l_default_column <> new_result_column_name THEN
821 --
822 -- Need to warn user because we can't reuse the index.
823 --
824 l_disabled_indexed_elements :=
825 l_disabled_indexed_elements || ', ' || qpcv.char_name;
826 dummy := qa_char_indexes_pkg.disable_index(qpcv.char_id);
827 END IF;
828
829 res_columns(i) := TRUE;
830 i := i + 1;
831 END IF;
832
833 NEW_PROMPT_SEQUENCE := NEW_PROMPT_SEQUENCE + 10;
834
835 -- Bug # 3329507. Modifying the query to include read_only,
836 -- Self Service and Information flags.
837 -- saugupta Fri, 26 Dec 2003 04:16:08 -0800 PDT
838
839 INSERT INTO QA_PLAN_CHARS (
840 plan_id,
841 char_id,
842 last_update_date,
843 last_updated_by,
844 creation_date,
845 created_by,
846 prompt_sequence,
847 prompt,
848 enabled_flag,
849 mandatory_flag,
850 read_only_flag,
851 ss_poplist_flag,
852 information_flag,
853 default_value,
854 result_column_name,
855 values_exist_flag,
856 displayed_flag,
857 attribute_category,
858 attribute1,
859 attribute2,
860 attribute3,
861 attribute4,
862 attribute5,
863 attribute6,
864 attribute7,
865 attribute8,
866 attribute9,
867 attribute10,
868 attribute11,
869 attribute12,
870 attribute13,
871 attribute14,
872 attribute15,
873 default_value_id)
874 VALUES (
875 X_PLAN_ID,
876 QPCV.CHAR_ID,
877 SYSDATE,
878 X_USER_ID,
879 SYSDATE,
880 X_USER_ID,
881 NEW_PROMPT_SEQUENCE,
882 QPCV.PROMPT,
883 QPCV.ENABLED_FLAG,
884 QPCV.MANDATORY_FLAG,
885 QPCV.READ_ONLY_FLAG,
886 QPCV.SS_POPLIST_FLAG,
887 QPCV.INFORMATION_FLAG,
888 QPCV.DEFAULT_VALUE,
889 NEW_RESULT_COLUMN_NAME,
890 QPCV.VALUES_EXIST_FLAG,
891 QPCV.DISPLAYED_FLAG,
892 QPCV.ATTRIBUTE_CATEGORY,
893 QPCV.ATTRIBUTE1,
894 QPCV.ATTRIBUTE2,
895 QPCV.ATTRIBUTE3,
896 QPCV.ATTRIBUTE4,
897 QPCV.ATTRIBUTE5,
898 QPCV.ATTRIBUTE6,
899 QPCV.ATTRIBUTE7,
900 QPCV.ATTRIBUTE8,
901 QPCV.ATTRIBUTE9,
902 QPCV.ATTRIBUTE10,
903 QPCV.ATTRIBUTE11,
904 QPCV.ATTRIBUTE12,
905 QPCV.ATTRIBUTE13,
906 QPCV.ATTRIBUTE14,
907 QPCV.ATTRIBUTE15,
908 QPCV.DEFAULT_VALUE_ID);
909
910 END LOOP;
911 CLOSE c_plan_chars;
912
913 --
914 -- Bug 3926150
915 -- Pass back the disabled index names. (use substr to get rid of
916 -- the lead comma and space.
917 --
918 IF l_disabled_indexed_elements IS NOT NULL THEN
919 x_disabled_indexed_elements := substr(l_disabled_indexed_elements, 3);
920 END IF;
921
922 IF NEW_PROMPT_SEQUENCE > 0 THEN
923 RETURN(NEW_PROMPT_SEQUENCE + 10);
924 ELSE
925 RETURN(NEW_PROMPT_SEQUENCE );
926 END IF;
927
928 END IF;
929
930 END auto_fill_missing_char;
931
932
933 FUNCTION add_ss_elements (p_plan_id NUMBER, p_user_id IN NUMBER)
934 RETURN NUMBER IS
935
936 -- Bug # 3329507. Modifying the cursor to include read_only,
937 -- Self Service and Information flags.
938 -- saugupta Fri, 26 Dec 2003 04:16:08 -0800 PDT
939
940 -- Bug 4958757. SQL Repository Fix SQL ID: 15008297
941 -- Cursor c1 completely removed and replaced by a
942 -- generic c_plan_chars cursor.
943 -- bso Thu Feb 2 15:03:41 PST 2006
944
945 CURSOR c2 IS
946 SELECT MAX(prompt_sequence) FROM qa_plan_chars
947 WHERE plan_id = p_plan_id;
948
949 -- Bug 4958757. SQL Repository Fix SQL ID: 15008349
950 -- The cursor c3 has been extracted to package level as
951 -- c_result_columns.
952
953 TYPE column_num IS TABLE of BOOLEAN INDEX BY BINARY_INTEGER;
954
955 res_columns column_num;
956 i BINARY_INTEGER;
957 qpcv c_plan_chars%ROWTYPE;
958 column_name VARCHAR2(30);
959 column_number NUMBER;
960 new_result_column_name VARCHAR2(30);
961 new_prompt_sequence NUMBER;
962
963 l_key VARCHAR2(30); -- Bug 4958757 Performance Temp Key
964
965 BEGIN
966
967 OPEN c2;
968 FETCH c2 INTO new_prompt_sequence;
969 CLOSE c2;
970
971 IF new_prompt_sequence IS NULL THEN
972 new_prompt_sequence := 0;
973 END IF;
974
975 FOR i IN 1..qltninrb.res_char_columns LOOP
976 res_columns(i) := FALSE;
977 END LOOP;
978
979 OPEN c_result_columns(p_plan_id);
980 LOOP
981 FETCH c_result_columns INTO i;
982 EXIT WHEN c_result_columns%NOTFOUND;
983 res_columns(i) := TRUE;
984 END LOOP;
985 CLOSE c_result_columns;
986
987 i := 1;
988
989 -- Bug 4958757 SQL Repository tuning.
990 l_key := 'QLTAUFLB.C1';
991 qa_performance_temp_pkg.purge_and_add_ids(
992 p_key => l_key,
993 p_id_list => qa_ss_const.VENDOR_NAME || ',' ||
994 qa_ss_const.PO_NUMBER);
995
996 OPEN c_plan_chars(7, p_plan_id, l_key); -- 7 is OSP Template Plan ID.
997 LOOP
998 FETCH c_plan_chars INTO qpcv;
999 EXIT WHEN c_plan_chars%NOTFOUND;
1000
1001 IF qpcv.hardcoded_column IS NOT NULL THEN
1002 new_result_column_name := qpcv.hardcoded_column;
1003 ELSE
1004 -- Find the first available column number
1005 WHILE ((res_columns(i) = TRUE) AND(i <= qltninrb.res_char_columns))
1006 LOOP
1007 i := i + 1;
1008 END LOOP;
1009
1010 IF i > qltninrb.res_char_columns THEN
1011 -- Exceeded upper limit of maximum number of chars. Error out.
1012 FND_MESSAGE.SET_NAME('QA', 'QA_EXCEEDED_COLUMN_COUNT');
1013 APP_EXCEPTION.RAISE_EXCEPTION;
1014 END IF;
1015
1016 new_result_column_name := 'CHARACTER' || TO_CHAR(i);
1017 res_columns(i) := TRUE;
1018 i := i + 1;
1019 END IF;
1020
1021 new_prompt_sequence := new_prompt_sequence + 10;
1022
1023 -- Bug # 3329507. Modifying the query to include read_only,
1024 -- Self Service and Information flags.
1025 -- saugupta Fri, 26 Dec 2003 04:16:08 -0800 PDT
1026
1027 INSERT INTO qa_plan_chars(
1028 plan_id,
1029 char_id,
1030 last_update_date,
1031 last_updated_by,
1032 creation_date,
1033 created_by,
1034 prompt_sequence,
1035 prompt,
1036 enabled_flag,
1037 mandatory_flag,
1038 read_only_flag,
1039 ss_poplist_flag,
1040 information_flag,
1041 default_value,
1042 result_column_name,
1043 values_exist_flag,
1044 displayed_flag,
1045 attribute_category,
1046 attribute1,
1047 attribute2,
1048 attribute3,
1049 attribute4,
1050 attribute5,
1051 attribute6,
1052 attribute7,
1053 attribute8,
1054 attribute9,
1055 attribute10,
1056 attribute11,
1057 attribute12,
1058 attribute13,
1059 attribute14,
1060 attribute15,
1061 default_value_id)
1062 VALUES (
1063 p_plan_id,
1064 qpcv.char_id,
1065 SYSDATE,
1066 p_user_id,
1067 SYSDATE,
1068 p_user_id,
1069 new_prompt_sequence,
1070 qpcv.prompt,
1071 qpcv.enabled_flag,
1072 qpcv.mandatory_flag,
1073 qpcv.read_only_flag,
1074 qpcv.ss_poplist_flag,
1075 qpcv.information_flag,
1076 qpcv.default_value,
1077 new_result_column_name,
1078 qpcv.values_exist_flag,
1079 qpcv.displayed_flag,
1080 qpcv.attribute_category,
1081 qpcv.attribute1,
1082 qpcv.attribute2,
1083 qpcv.attribute3,
1084 qpcv.attribute4,
1085 qpcv.attribute5,
1086 qpcv.attribute6,
1087 qpcv.attribute7,
1088 qpcv.attribute8,
1089 qpcv.attribute9,
1090 qpcv.attribute10,
1091 qpcv.attribute11,
1092 qpcv.attribute12,
1093 qpcv.attribute13,
1094 qpcv.attribute14,
1095 qpcv.attribute15,
1096 qpcv.default_value_id);
1097
1098 END LOOP;
1099 CLOSE c_plan_chars;
1100
1101 IF new_prompt_sequence > 0 THEN
1102 RETURN(new_prompt_sequence + 10);
1103 ELSE
1104 RETURN(new_prompt_sequence );
1105 END IF;
1106
1107 END add_ss_elements;
1108
1109 PROCEDURE add_work_req_elements (p_plan_id IN NUMBER, p_user_id IN NUMBER) IS
1110
1111 -- Bug 2368425: The procedure adds mandatory collection elements
1112 -- Asset Group and Asset Number to the Collection Plan
1113 -- when Action 'Create a work request' is assigned to the plan.
1114 -- If both the collection Elements are already present in the plan
1115 -- then this procedure will not be called.
1116 -- This is called from post-forms-commit trigger in QLTPLMDF.fmb
1117 -- suramasw Fri Jun 21 00:42:03 PDT 2002
1118
1119 l_new_prompt_sequence NUMBER;
1120 l_asset_group_char_id CONSTANT NUMBER := 162;
1121 l_asset_number_char_id CONSTANT NUMBER := 163;
1122
1123 -- The following cursor is to find the Max value of Prompt Sequence.
1124
1125 CURSOR prompt_cur IS
1126 SELECT MAX(prompt_sequence) FROM qa_plan_chars
1127 WHERE plan_id = p_plan_id;
1128
1129 -- The following cursor is to find the hardcoded values of the two collection
1130 -- elements Asset Group and Asset Number.
1131
1132 CURSOR element_cur IS
1133 SELECT char_id,prompt,hardcoded_column
1134 FROM qa_chars
1135 WHERE char_id IN (l_asset_group_char_id,l_asset_number_char_id);
1136
1137 BEGIN
1138 -- finding out the max value of the prompt
1139 OPEN prompt_cur;
1140 FETCH prompt_cur INTO l_new_prompt_sequence;
1141 CLOSE prompt_cur;
1142
1143 IF l_new_prompt_sequence IS NULL THEN
1144 l_new_prompt_sequence := 0;
1145 END IF;
1146
1147 -- If any one of the collection Elements Asset Group or Asset Number
1148 -- is present in the Collection plan it is deleted. The deleted Element
1149 -- will be added with the missing element in the following Insert statement.
1150
1151 DELETE FROM qa_plan_chars
1152 WHERE plan_id = p_plan_id
1153 AND char_id IN (l_asset_group_char_id,l_asset_number_char_id);
1154
1155 FOR i IN element_cur LOOP
1156 l_new_prompt_sequence := l_new_prompt_sequence + 10;
1157
1158 -- The following Insert statement is to add both the mandatory
1159 -- Collection Elements for the action.
1160
1161 -- Bug # 3329507. Modifying the query to include read_only,
1162 -- Self Service and Information flags.
1163 -- saugupta Fri, 26 Dec 2003 04:16:08 -0800 PDT
1164
1165 INSERT INTO qa_plan_chars(
1166 plan_id,
1167 char_id,
1168 last_update_date,
1169 last_updated_by,
1170 creation_date,
1171 created_by,
1172 prompt_sequence,
1173 prompt,
1174 enabled_flag,
1175 mandatory_flag,
1176 result_column_name,
1177 values_exist_flag,
1178 displayed_flag,
1179 read_only_flag,
1180 ss_poplist_flag,
1181 information_flag)
1182 VALUES (
1183 p_plan_id,
1184 i.char_id,
1185 SYSDATE,
1186 p_user_id,
1187 SYSDATE,
1188 p_user_id,
1189 l_new_prompt_sequence,
1190 i.prompt,
1191 1,
1192 1,
1193 i.hardcoded_column,
1194 2,
1195 1,
1196 2,
1197 2,
1198 2);
1199 END LOOP;
1200 END add_work_req_elements;
1201
1202 -- Bug 3517598. If lot/serial number is present in plan
1203 -- add LPN automatically. Function to add LPN automatically.
1204 -- Returns the next sequence number.
1205
1206 FUNCTION auto_fill_lpn (X_PLAN_ID NUMBER,
1207 X_USER_ID NUMBER
1208 ) RETURN NUMBER IS
1209 CURSOR prompt_cur IS
1210 SELECT MAX(prompt_sequence) FROM qa_plan_chars
1211 WHERE plan_id = x_plan_id;
1212
1213 -- Cursor to find the hardcoded values of the LPN
1214
1215 CURSOR lpn_cur IS
1216 SELECT char_id cid,prompt pro,hardcoded_column hc
1217 FROM qa_chars
1218 WHERE char_id = 150;
1219
1220 PROMPT_SEQ NUMBER;
1221 LPN_REC lpn_cur%ROWTYPE;
1222
1223 BEGIN
1224
1225 OPEN prompt_cur;
1226 FETCH prompt_cur into PROMPT_SEQ;
1227 CLOSE prompt_cur;
1228
1229 OPEN lpn_cur;
1230 FETCH lpn_cur INTO LPN_REC;
1231 CLOSE lpn_cur;
1232
1233
1234 INSERT INTO qa_plan_chars(
1235 plan_id,
1236 char_id,
1237 last_update_date,
1238 last_updated_by,
1239 creation_date,
1240 created_by,
1241 prompt_sequence,
1242 prompt,
1243 enabled_flag,
1244 mandatory_flag,
1245 result_column_name,
1246 values_exist_flag,
1247 displayed_flag,
1248 read_only_flag,
1249 ss_poplist_flag,
1250 information_flag)
1251 VALUES (
1252 x_plan_id,
1253 LPN_REC.cid,
1254 SYSDATE,
1255 x_user_id,
1256 SYSDATE,
1257 x_user_id,
1258 PROMPT_SEQ +10,
1259 LPN_REC.pro,
1260 1,
1261 2,
1262 LPN_REC.hc,
1263 2,
1264 1,
1265 2,
1266 2,
1267 2);
1268
1269 RETURN (PROMPT_SEQ + 20);
1270
1271 END auto_fill_lpn;
1272
1273 -- Bug 5147965 ksiddhar EAM Transaction Dependency Check
1274 FUNCTION auto_fill_missing_char_eam (X_PLAN_ID NUMBER,
1275 X_COPY_PLAN_ID NUMBER,
1276 X_USER_ID NUMBER
1277 ) RETURN NUMBER IS
1278 CURSOR prompt_cur IS
1279 SELECT MAX(prompt_sequence) FROM qa_plan_chars
1280 WHERE plan_id = X_PLAN_ID;
1281
1282 -- Cursor to find the hardcoded values of the eam
1283
1284 CURSOR eam_cur IS
1285 SELECT char_id cid,prompt pro,hardcoded_column hc,enabled_flag,
1286 mandatory_flag
1287 FROM qa_chars
1288 WHERE char_id in (SELECT char_id
1289 FROM
1290 qa_plan_chars qpc1
1291 WHERE plan_id=X_COPY_PLAN_ID
1292 AND enabled_flag =1
1293 and not exists
1294 ( select 1
1295 from
1296 qa_plan_chars qpc2
1297 where qpc2.plan_id=X_PLAN_ID
1298 and qpc2.char_id = qpc1.char_id)
1299 )
1300 AND enabled_flag =1;
1301
1302
1303 PROMPT_SEQ NUMBER;
1304
1305 BEGIN
1306
1307 OPEN prompt_cur;
1308 FETCH prompt_cur into PROMPT_SEQ;
1309 CLOSE prompt_cur;
1310
1311 IF PROMPT_SEQ IS NULL THEN
1312 PROMPT_SEQ := 0;
1313 END IF;
1314
1315 FOR i IN eam_cur LOOP
1316 PROMPT_SEQ := PROMPT_SEQ + 10;
1317
1318 INSERT INTO qa_plan_chars(
1319 plan_id,
1320 char_id,
1321 last_update_date,
1322 last_updated_by,
1323 creation_date,
1324 created_by,
1325 prompt_sequence,
1326 prompt,
1327 enabled_flag,
1328 mandatory_flag,
1329 result_column_name,
1330 values_exist_flag,
1331 displayed_flag,
1332 read_only_flag,
1333 ss_poplist_flag,
1334 information_flag)
1335 VALUES (
1336 x_plan_id,
1337 i.cid,
1338 SYSDATE,
1339 x_user_id,
1340 SYSDATE,
1341 x_user_id,
1342 PROMPT_SEQ,
1343 i.pro,
1344 i.enabled_flag,
1345 i.mandatory_flag,
1346 i.hc,
1347 2,
1348 1,
1349 2,
1350 2,
1351 2);
1352
1353 END LOOP;
1354 RETURN (PROMPT_SEQ + 10);
1355
1356 END auto_fill_missing_char_eam;
1357 -- Bug 5147965 ksiddhar EAM Transaction Dependency Check
1358 END QLTAUFLB;