1 PACKAGE BODY qa_plans_pub AS
2 /* $Header: qltpplnb.plb 120.9.12010000.1 2008/07/25 09:22:03 appldev ship $ */
3
4
5 -- Start of comments
6 -- API name : qa_plans_pub
7 -- Type : Public
8 -- End of comments
9
10
11 TYPE number_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
12
13
14 -- global variables section
15
16
17 g_pkg_name CONSTANT VARCHAR2(30) := 'qa_plans_pub';
18 g_max_char_columns CONSTANT NUMBER := qltninrb.res_char_columns;
19
20 --
21 -- Safe globals.
22 --
23 g_user_name_cache fnd_user.user_name%TYPE := NULL;
24 g_user_id_cache NUMBER;
25
26 --
27 -- Bug 3926150.
28 -- This is used to keep a list of currently used CHARACTER result
29 -- column names, so that an unused result column can be easily
30 -- found (which will be added back to this array).
31 -- bso Fri Dec 3 20:42:27 PST 2004
32 --
33 g_result_columns number_tab;
34 g_plan_id NUMBER; -- a cache for performance purpose
35
36 --
37 -- General utility functions
38 --
39
40 PROCEDURE init_result_column_array(p_plan_id NUMBER) IS
41 --
42 -- Bug 3926150. This procedure is created to keep track of whether a
43 -- CHARACTER column is in-use of free. If p_plan_id is given then we
44 -- load the existing columns from a collection plan into the array.
45 -- We also cached the plan ID in g_plan_id for performance. Only if
46 -- p_plan_id <> g_plan_id shall we reload the array. If the plan_id
47 -- is not found, then we assume a new plan is being created so we
48 -- don't need to do anything. If p_plan_id = -1 then we delete
49 -- the array (same as re-initialization).
50 --
51 -- bso Fri Dec 3 21:01:48 PST 2004
52 --
53 -- Bug 5406294
54 -- Modified the definition of the
55 -- Cursor to get the Substring of the
56 -- result column name as the result
57 -- col will have a value like
58 -- CHARACTERnn where nn would be the number part
59 -- SHKALYAN 24-JUL-2006
60 --
61 CURSOR c IS
62 SELECT to_number(SUBSTR(result_column_name, 10)) num
63 FROM qa_plan_chars
64 WHERE plan_id = p_plan_id AND
65 result_column_name LIKE 'CHARACTER%';
66
67 BEGIN
68 IF p_plan_id = g_plan_id THEN
69 RETURN;
70 END IF;
71
72 --
73 -- Many programmers will initialize the array with such a logic:
74 --
75 -- FOR i 1..g_max_char_columns LOOP
76 -- g_result_columns(i) := 0;
77 -- END LOOP;
78 --
79 -- including our own code in qltcpplb, qltauflb. This is not
80 -- performing well. Use the collection method .EXISTS to test
81 -- for existence is enough to know a member doesn't exist.
82 -- bso Fri Dec 3 21:14:59 PST 2004
83 --
84
85 IF p_plan_id = -1 THEN
86 g_result_columns.DELETE;
87 ELSE
88 FOR r IN c LOOP
89 g_result_columns(r.num) := 1;
90 END LOOP;
91 END IF;
92
93 g_plan_id := p_plan_id;
94
95 END init_result_column_array;
96
97
98 FUNCTION get_user_id(p_name VARCHAR2) RETURN NUMBER IS
99 --
100 -- Decode user name from fnd_user table.
101 --
102 id NUMBER;
103
104 CURSOR user_cursor IS
105 SELECT user_id
106 FROM fnd_user
107 WHERE user_name = p_name;
108 BEGIN
109
110 --
111 -- Code is duplicated in qltpspcb.plb. Any modification here
112 -- should be propagated to that file.
113 --
114
115 IF p_name IS NULL THEN
116 RETURN nvl(fnd_global.user_id, -1);
117 END IF;
118
119 --
120 -- It is very common for the same user to call the
121 -- APIs successively.
122 --
123 IF g_user_name_cache = p_name THEN
124 RETURN g_user_id_cache;
125 END IF;
126
127 OPEN user_cursor;
128 FETCH user_cursor INTO id;
129 IF user_cursor%NOTFOUND THEN
130 CLOSE user_cursor;
131 RETURN -1;
132 END IF;
133 CLOSE user_cursor;
134
135 g_user_name_cache := p_name;
136 g_user_id_cache := id;
137
138 RETURN id;
139 END get_user_id;
140
141
142 FUNCTION illegal_chars(p_name VARCHAR2) RETURN BOOLEAN IS
143 --
144 -- Check for illegal characters in a collection plan name.
145 -- Single quotes and spaces are allowed.
146 --
147 potpourri CONSTANT VARCHAR2(30) := '!@#$%^&*()-+={}[]:;"|><?/\,.~';
148 stars CONSTANT VARCHAR2(30) := '*****************************';
149
150 BEGIN
151 --
152 -- Here is an easy way to do it:
153 -- First translate all illegal chars to asterisks then use
154 -- INSTR to see if * is present. (Compare with the dozens
155 -- of IF statements in QLTPLMDF)
156 -- bso
157 --
158 RETURN instr(translate(p_name, potpourri, stars), '*') > 0;
159 END illegal_chars;
160
161
162 FUNCTION plan_exists(p_name IN VARCHAR2) RETURN NUMBER IS
163 --
164 -- Check if a collection plan already exists. If so, return
165 -- the plan ID, if not return -1.
166 --
167 BEGIN
168
169 RETURN nvl(qa_plans_api.plan_id(p_name), -1);
170
171 END plan_exists;
172
173
174 FUNCTION element_exists(p_plan_id IN NUMBER, p_char_id IN NUMBER)
175 RETURN BOOLEAN IS
176 --
177 -- Check if an element already exists in a plan.
178 --
179 CURSOR c IS
180 SELECT 1
181 FROM qa_plan_chars
182 WHERE plan_id = p_plan_id AND
183 char_id = p_char_id;
184
185 result BOOLEAN;
186 dummy NUMBER;
187
188 BEGIN
189
190 OPEN c;
191 FETCH c INTO dummy;
192 result := c%FOUND;
193 CLOSE c;
194
195 RETURN result;
196
197 END element_exists;
198
199
200 FUNCTION mandatory_element_exists(p_plan_id IN NUMBER)
201 --
202 -- Check if a mandatory and enabled element exists in a plan.
203 -- Needed when completing a new plan.
204 --
205 RETURN BOOLEAN IS
206
207 CURSOR c IS
208 SELECT 1
209 FROM qa_plan_chars
210 WHERE plan_id = p_plan_id AND
211 mandatory_flag = 1 AND
212 enabled_flag = 1;
213
214 result BOOLEAN;
215 dummy NUMBER;
216
217 BEGIN
218
219 OPEN c;
220 FETCH c INTO dummy;
221 result := c%FOUND;
222 CLOSE c;
223
224 RETURN result;
225
226 END mandatory_element_exists;
227
228
229 FUNCTION prompt_sequence_exists(p_plan_id IN NUMBER,
230 p_prompt_sequence IN NUMBER) RETURN BOOLEAN IS
231
232 CURSOR c IS
233 SELECT 1
234 FROM qa_plan_chars
235 WHERE plan_id = p_plan_id AND
236 prompt_sequence = p_prompt_sequence;
237
238 l_dummy NUMBER;
239 l_found BOOLEAN;
240
241 BEGIN
242
243 OPEN c;
244 FETCH c INTO l_dummy;
245 l_found := c%FOUND;
246 CLOSE c;
247
248 RETURN l_found;
249
250 END prompt_sequence_exists;
251
252
253 PROCEDURE validate_datatype(p_value IN VARCHAR2, p_datatype NUMBER) IS
254
255 temp_number Number;
256 temp_date Date;
257
258 BEGIN
259
260 IF p_value IS NULL THEN
261 RETURN;
262 END IF;
263
264 IF p_datatype = qa_ss_const.number_datatype THEN
265 BEGIN
266 temp_number := to_number(p_value);
267 EXCEPTION WHEN OTHERS THEN
268 fnd_message.set_name('QA','QA_INVALID_NUMBER');
269 fnd_msg_pub.add();
270 RAISE fnd_api.g_exc_error;
271 END;
272
273 ELSIF p_datatype = qa_ss_const.date_datatype THEN
274 BEGIN
275 temp_date := qltdate.any_to_date(p_value);
276 EXCEPTION WHEN OTHERS THEN
277 fnd_message.set_name('QA','QA_INVALID_DATE');
278 fnd_msg_pub.add();
279 RAISE fnd_api.g_exc_error;
280 END;
281 END IF;
282
283 END validate_datatype;
284
285
286 FUNCTION convert_flag(p_flag IN VARCHAR2)
287 RETURN NUMBER IS
288
289 BEGIN
290 IF p_flag = fnd_api.g_true THEN
291 RETURN 1;
292 END IF;
293
294 RETURN 2;
295 END convert_flag;
296
297
298 FUNCTION valid_plan_type (p_plan_type IN VARCHAR2)
299 RETURN BOOLEAN IS
300
301 CURSOR c IS
302 SELECT 1
303 FROM fnd_lookup_values
304 WHERE lookup_type = 'COLLECTION_PLAN_TYPE'
305 AND meaning = p_plan_type;
306
307 result BOOLEAN;
308 dummy NUMBER;
309
310 BEGIN
311
312 OPEN c;
313 FETCH c INTO dummy;
314 result := c%FOUND;
315 CLOSE c;
316
317 RETURN result;
318
319 END valid_plan_type;
320
321
322 FUNCTION get_plan_type_code (p_plan_type IN VARCHAR2)
323 RETURN VARCHAR2 IS
324
325 CURSOR c IS
326 SELECT lookup_code
327 FROM fnd_lookup_values
328 WHERE lookup_type = 'COLLECTION_PLAN_TYPE'
329 AND meaning = p_plan_type;
330
331 l_plan_type_code VARCHAR2(30);
332
333 BEGIN
334
335 OPEN c;
336 FETCH c INTO l_plan_type_code;
337 CLOSE c;
338
339 RETURN l_plan_type_code;
340
341 END get_plan_type_code;
342
343
344 --
345 -- Private functions for plan creation and element building.
346 --
347
348 FUNCTION get_next_sequence(p_plan_id NUMBER) RETURN NUMBER IS
349 --
350 -- This is a very specific function that computes
351 -- the next prompt sequence for a plan when a new
352 -- element is going to be added.
353 --
354 CURSOR c IS
355 SELECT max(prompt_sequence)
356 FROM qa_plan_chars
357 WHERE plan_id = p_plan_id;
358
359 p NUMBER;
360
361 BEGIN
362
363 OPEN c;
364 FETCH c INTO p;
365 IF c%NOTFOUND OR p IS NULL THEN
366 --
367 -- p IS NULL is needed. For some reason, this cursor never
368 -- raises the NOTFOUND condition. Even if the plan id does not
369 -- exist, max function returns a row of NULL instead of
370 -- triggering NOTFOUND condition.
371 --
372 p := 10;
373 ELSE
374 --
375 -- Add 10.
376 -- Then, round to the nearest 10, so it has a nice 0 ending.
377 --
378 p := round((p + 10)/10) * 10;
379 END IF;
380
381 CLOSE c;
385
382 RETURN p;
383
384 END get_next_sequence;
386
387 --
388 -- Bug 3926150. This function is obsolete due to this bug fix.
389 -- See new function suggest_result_column() which is intentionally named
390 -- differently to make sure in compile time all references to the old
391 -- function will be found and modified.
392 -- bso Fri Dec 3 21:27:50 PST 2004
393 --
394 /*
395 FUNCTION get_next_result_column_name(p_plan_id NUMBER) RETURN VARCHAR2 IS
396 --
397 -- Another very specific function that computes
398 -- the next result column name for a plan when a new
399 -- softcoded element is going to be added.
400 --
401
402 CURSOR c IS
403 SELECT max(to_number(substr(result_column_name, 10)))
404 FROM qa_plan_chars
405 WHERE plan_id = p_plan_id AND
406 upper(result_column_name) like 'CHARACTER%';
407
408 p NUMBER;
409 result qa_plan_chars.result_column_name%TYPE;
410
411 BEGIN
412
413 OPEN c;
414 FETCH c INTO p;
415 IF c%NOTFOUND OR p IS NULL THEN
416 --
417 -- p IS NULL is needed. For some reason, this cursor never
418 -- raises the NOTFOUND condition. Even if the plan id does not
419 -- exist, max function returns a row of NULL instead of
420 -- triggering NOTFOUND condition.
421 --
422 result := 'CHARACTER1';
423 ELSE
424 p := p + 1;
425 IF p > g_max_char_columns THEN
426 RETURN NULL;
427 END IF;
428 result := 'CHARACTER' || p;
429 END IF;
430
431 CLOSE c;
432 RETURN result;
433
434 END get_next_result_column_name;
435 */
436
437
438 PROCEDURE disable_index_private(p_char_id NUMBER) IS
439 --
440 -- Bug 3926150. Simple helper to disable the function-based index
441 -- and insert informational message to the global msg stack.
442 -- bso Sat Dec 4 16:12:44 PST 2004
443 --
444 dummy NUMBER;
445 BEGIN
446 dummy := qa_char_indexes_pkg.disable_index(p_char_id);
447 fnd_message.set_name('QA', 'QA_CHAR_REGENERATE_INDEX');
448 fnd_message.set_token('ELEMENT_NAME',
449 qa_chars_api.get_element_name(p_char_id));
450 fnd_msg_pub.add;
451 END disable_index_private;
452
453
454 --
455 -- Bug 3926150. A replacement of get_next_result_column_name to
456 -- return the new suggested result column name for a plan element.
457 -- It checks to see if the default result column of a function-based
458 -- index can be used. If not, just find the first available column.
459 -- bso Fri Dec 3 21:30:02 PST 2004
460 --
461 FUNCTION suggest_result_column(p_plan_id NUMBER, p_char_id NUMBER)
462 RETURN VARCHAR2 IS
463
464 l_default_column qa_plan_chars.result_column_name%TYPE;
465 dummy NUMBER;
466
467 BEGIN
468 IF p_plan_id <> g_plan_id THEN
469 --
470 -- This will be a strange exceptional case. It means caller is
471 -- calling add_plan_elements for more than one plan in parallel.
472 -- We can handle this by keep re-initializing.
473 --
474 init_result_column_array(-1);
475 init_result_column_array(p_plan_id);
476 END IF;
477
478 l_default_column := qa_char_indexes_pkg.get_default_result_column(p_char_id);
479 IF l_default_column IS NOT NULL THEN
480 --
481 -- We will be in here if there is a function-based index on this element.
482 --
483 IF NOT g_result_columns.EXISTS(to_number(substr(l_default_column, 10))) THEN
484 --
485 -- Here we know the default column name in that decode function
486 -- is unassigned... great news, just use it.
487 --
488 RETURN l_default_column;
489 ELSE
490 --
491 -- Otherwise, insert the informational message to the stack to ask user
492 -- to regenerate the index. Until then, the index will be disabled.
493 --
494 disable_index_private(p_char_id);
495 END IF;
496 END IF;
497
498 FOR i IN 1 .. g_max_char_columns LOOP
499 IF NOT g_result_columns.EXISTS(i) THEN
500 RETURN 'CHARACTER' || i;
501 END IF;
502 END LOOP;
503
504 RETURN NULL;
505 END suggest_result_column;
506
507
508 --
509 -- Bug 3926150. Mark the result column name CHARACTERxx as in use.
510 --
511 PROCEDURE mark_result_column(p_col_name VARCHAR2) IS
512 BEGIN
513 IF p_col_name LIKE 'CHARACTER%' THEN
514 g_result_columns(to_number(substr(p_col_name, 10))) := 1;
515 END IF;
516 END mark_result_column;
517
518
519 --
520 -- Private functions for plan completion verification.
521 --
522
523 FUNCTION get_plan_view_name(p_name VARCHAR2) RETURN VARCHAR2 IS
524 BEGIN
525 return 'Q_' || translate(substr(p_name, 1, 26), ' ''', '__') || '_V';
526 END get_plan_view_name;
527
528
529 FUNCTION get_import_view_name(p_name VARCHAR2) RETURN VARCHAR2 IS
530 BEGIN
534
531 return 'Q_' || translate(substr(p_name, 1, 25), ' ''', '__') || '_IV';
532 END get_import_view_name;
533
535 PROCEDURE check_element_dependencies(p_plan_id IN NUMBER) IS
536
537 -- Set dependency flags from qa_plan_chars:
538 -- 1 = characteristic exists on the QPlan
539 -- 2 = characteristic does NOT exist on the QPlan
540
541 CURSOR c IS
542 SELECT min(decode(char_id, 10, 1, 2)) item,
543 min(decode(char_id, 13, 1, 2)) revision,
544 min(decode(char_id, 19, 1, 2)) job_name,
545 min(decode(char_id, 20, 1, 2)) WIP_line,
546 min(decode(char_id, 21, 1, 2)) to_op_seq,
547 min(decode(char_id, 22, 1, 2)) from_op_seq,
548 min(decode(char_id, 23, 1, 2)) to_intraop_step,
549 min(decode(char_id, 24, 1, 2)) from_intraop_step,
550 min(decode(char_id, 16, 1, 2)) lot_number,
551 min(decode(char_id, 17, 1, 2)) serial_number,
552 min(decode(char_id, 14, 1, 2)) subinv,
553 min(decode(char_id, 12, 1, 2)) UOM,
554 min(decode(char_id, 15, 1, 2)) locator,
555 min(decode(char_id, 27, 1, 2)) po_number,
556 min(decode(char_id, 110, 1, 2)) po_rel_number,
557 min(decode(char_id, 28, 1, 2)) po_line,
558 min(decode(char_id, 33, 1, 2)) so_number,
559 min(decode(char_id, 35, 1, 2)) so_line,
560 min(decode(char_id, 26, 1, 2)) vendor,
561 min(decode(char_id, 60, 1, 2)) comp_item,
562 min(decode(char_id, 65, 1, 2)) comp_locator,
563 min(decode(char_id, 66, 1, 2)) comp_lot_number,
564 min(decode(char_id, 63, 1, 2)) comp_revision,
565 min(decode(char_id, 67, 1, 2)) comp_serial_number,
566 min(decode(char_id, 64, 1, 2)) comp_subinv,
567 min(decode(char_id, 62, 1, 2)) comp_UOM,
568 min(decode(char_id, 122, 1, 2)) task_number,
569 min(decode(char_id, 121, 1, 2)) project_number,
570 --
571 -- Bug 5680516.
572 -- This is used to set dependency flag for the collection
573 -- element SCARP OP SEQ (char id 144). From/To intra op step
574 -- can exist if either From/To op seq or the Scrap op seq
575 -- exists in the plan.
576 -- skolluku Tue Feb 13, 2007
577 --
578 min(decode(char_id, 144, 1, 2)) scrap_op_seq,
579 -- R12 OPM Deviations. Bug 4345503 Start
580 min(decode(char_id, 2147483556, 1, 2)) process_batch_num,
581 min(decode(char_id, 2147483555, 1, 2)) process_batchstep_num,
582 min(decode(char_id, 2147483554, 1, 2)) process_operation,
583 min(decode(char_id, 2147483553, 1, 2)) process_activity,
584 min(decode(char_id, 2147483552, 1, 2)) process_resources,
585 min(decode(char_id, 2147483551, 1, 2)) process_parameter_name
586 -- R12 OPM Deviations. Bug 4345503 End
587 FROM qa_plan_chars
588 WHERE plan_id = p_plan_id AND enabled_flag = 1;
589
590 -- qa_plan_char flags
591
592 item_flag NUMBER;
593 revision_flag NUMBER;
594 job_name_flag NUMBER;
595 wip_line_flag NUMBER;
596 to_op_seq_flag NUMBER;
597 from_op_seq_flag NUMBER;
598 to_intraop_step_flag NUMBER;
599 from_intraop_step_flag NUMBER;
600 lot_number_flag NUMBER;
601 serial_number_flag NUMBER;
602 subinv_flag NUMBER;
603 uom_flag NUMBER;
604 locator_flag NUMBER;
605 po_number_flag NUMBER;
606 po_rel_number_flag NUMBER;
607 po_line_flag NUMBER;
608 vendor_flag NUMBER;
609 so_number_flag NUMBER;
610 so_line_flag NUMBER;
611 comp_item_flag NUMBER;
612 comp_locator_flag NUMBER;
613 comp_lot_number_flag NUMBER;
614 comp_revision_flag NUMBER;
615 comp_serial_number_flag NUMBER;
616 comp_subinv_flag NUMBER;
617 comp_uom_flag NUMBER;
618 task_num_flag NUMBER;
619 project_num_flag NUMBER;
620 --
621 -- Bug 5680516.
622 -- This is used to set dependency flag for the collection
623 -- element SCARP OP SEQ (char id 144). From/To intra op step
624 -- can exist if either From/To op seq or the Scrap op seq
625 -- exists in the plan.
626 -- skolluku Tue Feb 13, 2007
627 --
628 scrap_op_seq_flag NUMBER;
629
630 -- R12 OPM Deviations. Bug 4345503 Start
631 process_batch_num_flag NUMBER;
632 process_batchstep_num_flag NUMBER;
633 process_operation_flag NUMBER;
634 process_activity_flag NUMBER;
635 process_resource_flag NUMBER;
636 process_parameter_flag NUMBER;
637 -- R12 OPM Deviations. Bug 4345503 End
638 BEGIN
639
640 -- The Quality Plan Workbench should enforce dependencies between
641 -- characteristics. For example, if op seq is a characteristic on a
642 -- plan, then job or line must be on the plan too.
643 --
644 -- WIP:
648 -- - intraop step (to/from) needs OP SEQ
645 -- - op seq (to/from) requires a wip entity ID (JOB NAME)
646 -- or LINE
647 -- - WIP production line requires an ITEM
649 -- --5680516
650 -- or SCRAP OP SEQ
651 -- - you can't have both JOB NAME and LINE on the same QPlan
652 --
653 -- INV:
654 -- - locator requires a SUBINV
655 -- - lot requires an ITEM
656 -- - serial number requires an ITEM
657 -- - revision requires an ITEM
658 -- - if item is on the plan, revision may need to be on it,
659 -- so caution the user
660 -- - if subinventory is on the plan, locator may need
661 -- to be on it as well so caution the user
662 -- - the above 6 items are also true for their component
663 -- counterparts
664 --
665 -- PO:
666 -- - PO line dependent on PO NUMBER
667 -- - PO release number dependent on PO NUMBER
668 --
669 -- SO:
670 -- - SO line dependent on SO NUMBER
671 --
672 -- The Quality Plan Workbench should enforce dependencies between
673 -- characteristics and actions. For example, if a selected action is
674 -- "put job on hold", then JOB NAME must appear on the QPlan.
675 -- Dependencies are:
676 --
677 -- Action Requires
678 -- ----------------------------- -------------------------
679 -- Job on hold Job Name
680 -- Schedule on hold WIP line
681 -- Item status Item
682 -- Lot status code (R11) Lot number
683 -- S/N status code (R11) S/N
684 -- Shop floor status To or From Intra-op step
685 -- Put PO line on hold PO line
686 -- Put vendor on hold Vendor
687
688 OPEN c;
689 FETCH c INTO
690 item_flag,
691 revision_flag,
692 job_name_flag,
693 wip_line_flag,
694 to_op_seq_flag,
695 from_op_seq_flag,
696 to_intraop_step_flag,
697 from_intraop_step_flag,
698 lot_number_flag,
699 serial_number_flag,
700 subinv_flag,
701 uom_flag,
702 locator_flag,
703 po_number_flag,
704 po_rel_number_flag,
705 po_line_flag,
706 so_number_flag,
707 so_line_flag,
708 vendor_flag,
709 comp_item_flag,
710 comp_locator_flag,
711 comp_lot_number_flag,
712 comp_revision_flag,
713 comp_serial_number_flag,
714 comp_subinv_flag,
715 comp_uom_flag,
716 task_num_flag,
717 project_num_flag,
718 --
719 -- Bug 5680516.
720 -- This is used to set dependency flag for the collection
721 -- element SCARP OP SEQ (char id 144). From/To intra op step
722 -- can exist if either From/To op seq or the Scrap op seq
723 -- exists in the plan.
724 -- skolluku Tue Feb 13, 2007
725 --
726 scrap_op_seq_flag,
727 -- R12 OPM Deviations. Bug 4345503 Start
728 process_batch_num_flag,
729 process_batchstep_num_flag,
730 process_operation_flag,
731 process_activity_flag,
732 process_resource_flag,
733 process_parameter_flag;
734 -- R12 OPM Deviations. Bug 4345503 End
735 CLOSE c;
736
737 -- Check dependencies on item
738
739 IF (item_flag = 2) THEN
740
741 IF (lot_number_flag = 1) THEN
742 fnd_message.set_name ('QA', 'QA_DEPENDENT_LOT_ON_ITEM');
743 fnd_msg_pub.add();
744 RAISE fnd_api.g_exc_error;
745 END IF;
746
747 IF (serial_number_flag = 1) THEN
748 fnd_message.set_name ('QA', 'QA_DEPENDENT_SERIAL_ON_ITEM');
749 fnd_msg_pub.add();
750 RAISE fnd_api.g_exc_error;
751 END IF;
752
753 IF (wip_line_flag = 1) THEN
754 fnd_message.set_name ('QA', 'QA_DEPENDENT_WIP_LINE_ON_ITEM');
755 fnd_msg_pub.add();
756 RAISE fnd_api.g_exc_error;
757 END IF;
758
759 IF (revision_flag = 1) THEN
760 fnd_message.set_name ('QA', 'QA_DEPENDENT_REV_ON_ITEM');
761 fnd_msg_pub.add();
762 RAISE fnd_api.g_exc_error;
763 END IF;
764
765 IF (subinv_flag = 1) THEN
766 fnd_message.set_name ('QA', 'QA_DEPENDENT_SUBINV_ON_ITEM');
767 fnd_msg_pub.add();
768 RAISE fnd_api.g_exc_error;
769 END IF;
770
771 IF (uom_flag = 1) THEN
772 fnd_message.set_name ('QA', 'QA_DEPENDENT_UOM_ON_ITEM');
773 fnd_msg_pub.add();
774 RAISE fnd_api.g_exc_error;
775 END IF;
776
777 END IF;
778
779 -- check dependencies on component item
780
781 IF (comp_item_flag = 2) THEN
782
783 IF (comp_lot_number_flag = 1) THEN
787 END IF;
784 fnd_message.set_name ('QA', 'QA_DEPENDENT_COMP_LOT_ITEM');
785 fnd_msg_pub.add();
786 RAISE fnd_api.g_exc_error;
788
789 IF (comp_serial_number_flag = 1) THEN
790 fnd_message.set_name ('QA', 'QA_DEPENDENT_COMP_SERIAL_ITEM');
791 fnd_msg_pub.add();
792 RAISE fnd_api.g_exc_error;
793 END IF;
794
795 IF (comp_revision_flag = 1) THEN
796 fnd_message.set_name ('QA', 'QA_DEPENDENT_COMP_REV_ITEM');
797 fnd_msg_pub.add();
798 RAISE fnd_api.g_exc_error;
799 END IF;
800
801 IF (comp_subinv_flag = 1) THEN
802 fnd_message.set_name ('QA', 'QA_DEPENDENT_COMP_SUBINV_ITEM');
803 fnd_msg_pub.add();
804 RAISE fnd_api.g_exc_error;
805 END IF;
806
807 IF (comp_uom_flag = 1) THEN
808 fnd_message.set_name ('QA', 'QA_DEPENDENT_COMP_UOM_ITEM');
809 fnd_msg_pub.add();
810 RAISE fnd_api.g_exc_error;
811 END IF;
812
813 END IF;
814
815
816 -- Check dependencies on job/line
817
818 IF (job_name_flag = 2 AND wip_line_flag = 2) THEN
819
820 IF (to_op_seq_flag = 1 OR from_op_seq_flag = 1) THEN
821 fnd_message.set_name ('QA', 'QA_DEPENDENT_OPSEQ_ON_JOB');
822 fnd_msg_pub.add();
823 RAISE fnd_api.g_exc_error;
824 END IF;
825
826 END IF;
827
828
829 -- Check dependencies on to/from op seq
830
831 IF (to_op_seq_flag = 2)
832 --
833 -- Bug 5680516.
834 -- Added dependency of To Intra op Step on Scrap Op Seq
835 -- collection element if To Op Seq is not present in the
836 -- plan. Added this condition to throw an error only if
837 -- both To Op Seq and Scrap Op Seq are not present in the
838 -- plan but To Intra Op Step is present in the plan.
839 -- skolluku Tue Feb 13, 2007
840 --
841 AND (scrap_op_seq_flag = 2) THEN
842
843 IF (to_intraop_step_flag = 1) THEN
844 fnd_message.set_name ('QA', 'QA_DEPENDENT_INTRAOP_ON_OPSEQ');
845 fnd_msg_pub.add();
846 RAISE fnd_api.g_exc_error;
847 END IF;
848
849 END IF;
850
851 IF (from_op_seq_flag = 2)
852 --
853 -- Bug 5680516.
854 -- Added dependency of From Intra op Step on Scrap Op Seq
855 -- collection element if From Op Seq is not present in the
856 -- plan. Added this condition to throw an error only if
857 -- both From Op Seq and Scrap Op Seq are not present in the
858 -- plan but From Intra Op Step is present in the plan.
859 -- skolluku Tue Feb 13, 2007
860 --
861 AND (scrap_op_seq_flag = 2) THEN
862
863 IF (from_intraop_step_flag = 1) THEN
864 fnd_message.set_name ('QA', 'QA_DEPENDENT_INTRAOP_ON_OPSEQ');
865 fnd_msg_pub.add();
866 RAISE fnd_api.g_exc_error;
867 END IF;
868
869 END IF;
870
871
872 -- Check dependencies on SUBINV
873
874 IF (subinv_flag = 2) THEN
875
876 IF (locator_flag = 1) THEN
877 fnd_message.set_name ('QA', 'QA_DEPENDENT_LOCATOR_ON_SUB');
878 fnd_msg_pub.add();
879 RAISE fnd_api.g_exc_error;
880 END IF;
881
882 END IF;
883
884 -- Check dependencies on COMP_SUBINV
885
886 IF (comp_subinv_flag = 2) THEN
887
888 IF (comp_locator_flag = 1) THEN
889 fnd_message.set_name ('QA', 'QA_DEPENDENT_COMP_LOCATOR_SUB');
890 fnd_msg_pub.add();
891 RAISE fnd_api.g_exc_error;
892 END IF;
893
894 END IF;
895
896
897 -- Check dependencies on PO NUMBER
898
899 IF (po_number_flag = 2) THEN
900
901 IF (po_line_flag = 1) THEN
902 fnd_message.set_name ('QA', 'QA_DEPENDENT_PO_LINE_ON_HEADER');
903 fnd_msg_pub.add();
904 RAISE fnd_api.g_exc_error;
905 END IF;
906
907 IF (po_rel_number_flag = 1) THEN
908 fnd_message.set_name ('QA', 'QA_DEPENDENT_PO_REL_ON_HEADER');
909 fnd_msg_pub.add();
910 RAISE fnd_api.g_exc_error;
911 END IF;
912
913 END IF;
914
915
916 -- Check dependencies on SO NUMBER
917
918 IF (so_number_flag = 2) THEN
919
920 IF (so_line_flag = 1) THEN
921 fnd_message.set_name ('QA', 'QA_DEPENDENT_SO_LINE_ON_HEADER');
922 fnd_msg_pub.add();
923 RAISE fnd_api.g_exc_error;
924 END IF;
925
926 END IF;
927
928 IF (project_num_flag = 2) THEN
929
930 IF (task_num_flag = 1) THEN
931 fnd_message.set_name ('QA', 'QA_PROJECT_TASK_DEPEND');
932 fnd_msg_pub.add();
933 RAISE fnd_api.g_exc_error;
934 END IF;
935
936 END IF;
937
938 -- R12 OPM Deviations. Bug 4345503 Start
939
940 IF (process_resource_flag = 2) THEN
941 IF (process_parameter_flag = 1) THEN
945 fnd_message.set_token('ELEMENT_LIST',
942 fnd_message.set_name ('QA', 'QA_DEPENDENT_PLAN_CHARS');
943 fnd_message.set_token('CHILD_ELEMENT',
944 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.process_parameter));
946 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.process_batch_num));
947 fnd_msg_pub.add();
948 RAISE fnd_api.g_exc_error;
949 END IF;
950 END IF;
951
952 IF (process_activity_flag = 2) THEN
953 IF (process_resource_flag = 1) THEN
954 fnd_message.set_name ('QA', 'QA_DEPENDENT_PLAN_CHARS');
955 fnd_message.set_token('CHILD_ELEMENT',
956 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.process_resource));
957 fnd_message.set_token('ELEMENT_LIST',
958 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.process_batch_num));
959 fnd_msg_pub.add();
960 RAISE fnd_api.g_exc_error;
961 END IF;
962 END IF;
963
964 IF (process_batchstep_num_flag = 2) THEN
965 IF (process_activity_flag = 1) THEN
966 fnd_message.set_name ('QA', 'QA_DEPENDENT_PLAN_CHARS');
967 fnd_message.set_token('CHILD_ELEMENT',
968 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.process_activity));
969 fnd_message.set_token('ELEMENT_LIST',
970 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.process_batch_num));
971 fnd_msg_pub.add();
972 RAISE fnd_api.g_exc_error;
973 END IF;
974 END IF;
975
976 IF (process_batchstep_num_flag = 2) THEN
977 IF (process_operation_flag = 1) THEN
978 fnd_message.set_name ('QA', 'QA_DEPENDENT_PLAN_CHARS');
979 fnd_message.set_token('CHILD_ELEMENT',
980 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.process_operation));
981 fnd_message.set_token('ELEMENT_LIST',
982 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.process_batch_num));
983 fnd_msg_pub.add();
984 RAISE fnd_api.g_exc_error;
985 END IF;
986 END IF;
987
988 IF (process_batch_num_flag = 2) THEN
989 IF (process_batchstep_num_flag = 1) THEN
990 fnd_message.set_name ('QA', 'QA_DEPENDENT_PLAN_CHARS');
991 fnd_message.set_token('CHILD_ELEMENT',
992 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.process_batchstep_num));
993 fnd_message.set_token('ELEMENT_LIST',
994 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.process_batch_num));
995 fnd_msg_pub.add();
996 RAISE fnd_api.g_exc_error;
997 END IF;
998 END IF;
999
1000 -- R12 OPM Deviations. Bug 4345503 End
1001 END check_element_dependencies;
1002
1003
1004 PROCEDURE complete_plan_private(
1005 p_plan_id NUMBER,
1006 p_plan_name VARCHAR2,
1007 p_commit VARCHAR2,
1008 p_user_id NUMBER) IS
1009
1010 l_request_id NUMBER;
1011 BEGIN
1012 IF NOT mandatory_element_exists(p_plan_id) THEN
1013 fnd_message.set_name('QA', 'QA_QPLAN_MUST_HAVE_CHARS');
1014 fnd_msg_pub.add();
1015 RAISE fnd_api.g_exc_error;
1016 END IF;
1017
1018 check_element_dependencies(p_plan_id);
1019
1020 IF fnd_api.to_boolean(p_commit) THEN
1021 COMMIT;
1022
1023 --
1024 -- Launch the dynamic view creator only if the user
1025 -- commits; otherwise, the view generator will fail
1026 -- because it is run in another database session.
1027 --
1028 -- The init is required to get the concurrent program
1029 -- to run. The resp_id 20561 is the seeded main Quality
1030 -- responsibility. 250 is Oracle Quality's application ID.
1031 --
1032 fnd_global.apps_initialize(
1033 user_id => p_user_id,
1034 resp_id => 20561,
1035 resp_appl_id => 250);
1036
1037 l_request_id := fnd_request.submit_request(
1038 application => 'QA',
1039 program => 'QLTPVWWB',
1040 argument1 => get_plan_view_name(p_plan_name),
1041 argument2 => NULL,
1042 argument3 => to_char(p_plan_id),
1043 argument4 => get_import_view_name(p_plan_name),
1044 argument5 => NULL,
1045 argument6 => 'QA_GLOBAL_RESULTS_V');
1046
1047 COMMIT;
1048 END IF;
1049 END complete_plan_private;
1050
1051
1052 --
1053 -- Private functions for copying a collection plan.
1054 --
1055
1056 FUNCTION copy_plan_header(
1057 p_from_plan_id IN NUMBER,
1058 p_to_plan_name IN VARCHAR2,
1059 p_to_org_id IN NUMBER,
1060 p_user_id IN NUMBER)
1061 RETURN NUMBER IS
1062
1063 l_plan_name qa_plans.name%TYPE;
1064 l_plan_view qa_plans.view_name%TYPE;
1065 l_import_view qa_plans.import_view_name%TYPE;
1066 l_to_plan_id NUMBER;
1067
1068 BEGIN
1069
1070 --
1071 -- Let's see if the target plan already exists
1072 --
1073 l_plan_name := upper(p_to_plan_name);
1074 l_to_plan_id := plan_exists(l_plan_name);
1075
1076 IF l_to_plan_id = -1 THEN
1077 --
1078 -- Create a new plan header in qa_plans table.
1079 --
1083 l_import_view := get_import_view_name(l_plan_name);
1080 SELECT qa_plans_s.nextval INTO l_to_plan_id FROM dual;
1081
1082 l_plan_view := get_plan_view_name(l_plan_name);
1084
1085 -- Bug 3726391. shkalyan 28 June 2004
1086 -- Added insert of missing columns viz.
1087 -- instructions,displayed_flag,attribute_category,
1088 -- attribute1 to attribute15
1089
1090 -- Bug 3726391. shkalyan 30 June 2004
1091 -- Removed insert of attribute_category,attribute1 to attribute15
1092 -- As per code review comments
1093
1094 -- Bug 3763668. ilawler 13 July 2004
1095 -- Removed DISPLAYED_FLAG field, not a valid case column
1096
1097 -- 12.1 QWB Usability Improvements Project
1098 -- Added the Multirow flag column
1099 -- ntungare
1100 INSERT INTO qa_plans(
1101 plan_id,
1102 organization_id,
1103 last_update_date,
1104 last_updated_by,
1105 creation_date,
1106 created_by,
1107 last_update_login,
1108 name,
1109 plan_type_code,
1110 spec_assignment_type,
1111 description,
1112 import_view_name,
1113 view_name,
1114 effective_from,
1115 effective_to,
1116 template_plan_id,
1117 esig_mode,
1118 instructions,
1119 multirow_flag)
1120 SELECT
1121 l_to_plan_id,
1122 p_to_org_id,
1123 sysdate,
1124 p_user_id,
1125 sysdate,
1126 p_user_id,
1127 p_user_id,
1128 p_to_plan_name,
1129 plan_type_code,
1130 spec_assignment_type,
1131 description,
1132 l_import_view,
1133 l_plan_view,
1134 effective_from,
1135 effective_to,
1136 template_plan_id,
1137 esig_mode,
1138 instructions,
1139 multirow_flag
1140 FROM qa_plans
1141 WHERE plan_id = p_from_plan_id;
1142
1143 END IF;
1144
1145 RETURN l_to_plan_id;
1146
1147 END copy_plan_header;
1148
1149
1150 --
1151 -- Bug 3926150. Commenting out copy_plan_elements because it
1152 -- is obsolete by copy_plan_elements_bulk. We don't want to
1153 -- dual maintain two codelines.
1154 -- bso Fri Dec 3 21:59:44 PST 2004
1155 --
1156 /*
1157 PROCEDURE copy_plan_elements(
1158 p_copy_from_plan_id IN NUMBER,
1159 p_copy_to_plan_id IN NUMBER,
1160 p_copy_values_flag IN VARCHAR2,
1161 p_user_id IN NUMBER) IS
1162
1163 --
1164 -- This cursor retrieves all plan elements from the source
1165 -- plan except those that already occur in the target plan
1166 -- (which could be an existing plan).
1167 --
1168 -- Explain plan shows NOT IN performance is OK.
1169 --
1170
1171 -- Tracking Bug : 3104827
1172 -- Modifying to include Three new Flags for Read Only Collection Plan Elements
1173 -- saugupta Thu Aug 28 08:59:59 PDT 2003
1174
1175 -- Bug 3726391. shkalyan 28 June 2004
1176 -- Added missing columns viz.
1177 -- decimal_precision,uom_code,attribute_category,attribute1 to attribute15
1178
1179 -- Bug 3726391. shkalyan 30 June 2004
1180 -- Removed insert of attribute_category,attribute1 to attribute15
1181 -- As per code review comments
1182
1183 CURSOR c IS
1184 SELECT
1185 char_id,
1186 prompt_sequence,
1187 prompt,
1188 enabled_flag,
1189 mandatory_flag,
1190 default_value,
1191 upper(result_column_name) result_column_name,
1192 values_exist_flag,
1193 displayed_flag,
1194 default_value_id,
1195 read_only_flag,
1196 ss_poplist_flag,
1197 information_flag,
1198 decimal_precision,
1199 uom_code
1200 FROM
1201 qa_plan_chars
1202 WHERE
1203 plan_id = p_copy_from_plan_id AND char_id NOT IN
1204 (SELECT char_id
1205 FROM qa_plan_chars
1206 WHERE plan_id = p_copy_to_plan_id)
1207 ORDER BY prompt_sequence;
1208
1209 l_prompt_sequence NUMBER;
1210 l_char_sequence NUMBER;
1211 l_char_column_name VARCHAR2(30);
1212 l_result_column qa_plan_chars.result_column_name%TYPE;
1213
1214 BEGIN
1215
1216 l_prompt_sequence := get_next_sequence(p_copy_to_plan_id);
1217 l_char_column_name := get_next_result_column_name(p_copy_to_plan_id);
1218 IF l_char_column_name IS NULL THEN
1219 --
1220 -- This will guarantee it prints exceed column message later.
1221 --
1222 l_char_sequence := g_max_char_columns + 1;
1223 ELSE
1224 l_char_sequence := to_number(substr(l_char_column_name, 10));
1225 END IF;
1226
1227 --
1228 -- For each record in cursor c, insert into the target plan
1229 -- with the proper prompt_sequence and result_column_name.
1233
1230 --
1231
1232 FOR pc IN c LOOP
1234 IF pc.result_column_name LIKE 'CHARACTER%' THEN
1235 IF l_char_sequence > g_max_char_columns THEN
1236 fnd_message.set_name('QA', 'QA_EXCEEDED_COLUMN_COUNT');
1237 fnd_msg_pub.add();
1238 raise fnd_api.g_exc_error;
1239 END IF;
1240 l_result_column := 'CHARACTER' || l_char_sequence;
1241 l_char_sequence := l_char_sequence + 1;
1242 ELSE
1243 l_result_column := pc.result_column_name;
1244 END IF;
1245
1246 -- Tracking Bug : 3104827
1247 -- Modifying to include Three new Flags for Collection Plan Element
1248 -- saugupta Thu Aug 28 08:59:59 PDT 2003
1249
1250 -- Bug 3726391. shkalyan 28 June 2004
1251 -- Added insert of missing columns viz.
1252 -- decimal_precision,uom_code,attribute_category,
1253 -- attribute1 to attribute15
1254
1255 -- Bug 3726391. shkalyan 30 June 2004
1256 -- Removed insert of attribute_category,attribute1 to attribute15
1257 -- As per code review comments
1258
1259 INSERT INTO qa_plan_chars(
1260 plan_id,
1261 char_id,
1262 last_update_date,
1263 last_updated_by,
1264 creation_date,
1265 created_by,
1266 last_update_login,
1267 prompt_sequence,
1268 prompt,
1269 enabled_flag,
1270 mandatory_flag,
1271 default_value,
1272 result_column_name,
1273 values_exist_flag,
1274 displayed_flag,
1275 default_value_id,
1276 read_only_flag,
1277 ss_poplist_flag,
1278 information_flag,
1279 decimal_precision,
1280 uom_code)
1281 VALUES (
1282 p_copy_to_plan_id,
1283 pc.char_id,
1284 sysdate,
1285 p_user_id,
1286 sysdate,
1287 p_user_id,
1288 p_user_id,
1289 l_prompt_sequence,
1290 pc.prompt,
1291 pc.enabled_flag,
1292 pc.mandatory_flag,
1293 pc.default_value,
1294 l_result_column,
1295 decode(p_copy_values_flag,
1296 fnd_api.g_true, pc.values_exist_flag, 2),
1297 pc.displayed_flag,
1298 pc.default_value_id,
1299 pc.read_only_flag,
1300 pc.ss_poplist_flag,
1301 pc.information_flag,
1302 pc.decimal_precision,
1303 pc.uom_code
1304 );
1305
1306 l_prompt_sequence := l_prompt_sequence + 10;
1307 END LOOP;
1308
1309 END copy_plan_elements;
1310 */
1311
1312
1313 --
1314 -- This version of copy_plan_elements does the same as the
1315 -- above procedure, but uses 8i Bulk bind feature to improve
1316 -- performance.
1317 --
1318 PROCEDURE copy_plan_elements_bulk(
1319 p_copy_from_plan_id IN NUMBER,
1320 p_copy_to_plan_id IN NUMBER,
1321 p_copy_values_flag IN VARCHAR2,
1322 p_user_id IN NUMBER) IS
1323
1324 TYPE prompt_tab IS TABLE OF qa_plan_chars.prompt%TYPE
1325 INDEX BY BINARY_INTEGER;
1326
1327 TYPE default_tab IS TABLE OF qa_plan_chars.default_value%TYPE
1328 INDEX BY BINARY_INTEGER;
1329
1330 TYPE result_tab IS TABLE OF qa_plan_chars.result_column_name%TYPE
1331 INDEX BY BINARY_INTEGER;
1332
1333 char_ids number_tab;
1334 prompt_sequences number_tab;
1335 prompts prompt_tab;
1336 enabled_flags number_tab;
1337 mandatory_flags number_tab;
1338 default_values default_tab;
1339 result_column_names result_tab;
1340 values_exist_flags number_tab;
1341 displayed_flags number_tab;
1342 default_value_ids number_tab;
1343
1344 l_prompt_sequence NUMBER;
1345 l_char_sequence NUMBER;
1346 l_char_column_name VARCHAR2(30);
1347
1348 -- Tracking Bug : 3104827
1349 -- Added to include Three new Flags for Collection Plan Element
1350 -- saugupta Thu Aug 28 08:59:59 PDT 2003
1351 read_only_flags number_tab;
1352 ss_poplist_flags number_tab;
1353 information_flags number_tab;
1354
1355 -- Bug 3726391. shkalyan 28 June 2004
1356 -- Added declaration of missing columns viz.
1357 -- decimal_precision,uom_code,attribute_category,attribute1 to attribute15
1358
1359 -- Bug 3726391. shkalyan 30 June 2004
1360 -- Removed insert of attribute_category,attribute1 to attribute15
1361 -- As per code review comments
1362
1363 TYPE uom_code_tab IS TABLE OF qa_plan_chars.uom_code%TYPE
1364 INDEX BY BINARY_INTEGER;
1365
1366 decimal_precisions number_tab;
1367 uom_codes uom_code_tab;
1368
1369 -- Tracking Bug : 6734330 Device Integration Project
1370 -- Included three columns device_flag,
1371 -- device_id and override_flag for Collection Plan Elements
1372 -- bhsankar Mon Jan 7 22:00:17 PST 2008
1376
1373 device_flags number_tab;
1374 device_ids number_tab;
1375 override_flags number_tab;
1377
1378 BEGIN
1379
1380 --
1381 -- This cursor retrieves all plan elements from the source
1382 -- plan except those that already occur in the target plan
1383 -- (which could be an existing plan).
1384 --
1385 -- Explain plan shows NOT IN performance is OK.
1386 --
1387
1388 -- Bug 3726391. shkalyan 28 June 2004
1389 -- Added select of missing columns viz.
1390 -- decimal_precision,uom_code,attribute_category,attribute1 to attribute15
1391
1392 -- Bug 3726391. shkalyan 30 June 2004
1393 -- Removed insert of attribute_category,attribute1 to attribute15
1394 -- As per code review comments
1395
1396 -- Bug 6734330
1397 -- Device Integration Project
1398 -- Added device columns device_id,
1399 -- device_flag and override_flags.
1400 -- bhsankar Mon Jan 7 22:00:17 PST 2008
1401 SELECT
1402 char_id,
1403 prompt_sequence,
1404 prompt,
1405 enabled_flag,
1406 mandatory_flag,
1407 default_value,
1408 upper(result_column_name) result_column_name,
1409 values_exist_flag,
1410 displayed_flag,
1411 default_value_id,
1412 read_only_flag,
1413 ss_poplist_flag,
1414 information_flag,
1415 decimal_precision,
1416 uom_code,
1417 device_flag,
1418 device_id,
1419 override_flag
1420 BULK COLLECT INTO
1421 char_ids,
1422 prompt_sequences,
1423 prompts,
1424 enabled_flags,
1425 mandatory_flags,
1426 default_values,
1427 result_column_names,
1428 values_exist_flags,
1429 displayed_flags,
1430 default_value_ids,
1431 read_only_flags,
1432 ss_poplist_flags,
1433 information_flags,
1434 decimal_precisions,
1435 uom_codes,
1436 device_flags,
1437 device_ids,
1438 override_flags
1439 FROM
1440 qa_plan_chars
1441 WHERE
1442 plan_id = p_copy_from_plan_id AND char_id NOT IN
1443 (SELECT char_id
1444 FROM qa_plan_chars
1445 WHERE plan_id = p_copy_to_plan_id)
1446 ORDER BY prompt_sequence;
1447
1448 IF char_ids.COUNT = 0 THEN
1449 --
1450 -- This is needed in case the target plan is an existing
1451 -- plan that already contains all elements in the source.
1452 --
1453 RETURN;
1454 END IF;
1455
1456 l_prompt_sequence := get_next_sequence(p_copy_to_plan_id);
1457
1458 --
1459 -- Bug 3926150. This should be done inside the loop for this
1460 -- fix. In fact it was a bug to just increment by 1 in the
1461 -- original logic.
1462 -- bso Fri Dec 3 22:10:27 PST 2004
1463 --
1464 -- l_char_column_name := get_next_result_column_name(p_copy_to_plan_id);
1465 -- IF l_char_column_name IS NULL THEN
1466 -- --
1467 -- -- This will guarantee it prints exceed column message later.
1468 -- --
1469 -- l_char_sequence := g_max_char_columns + 1;
1470 -- ELSE
1471 -- l_char_sequence := to_number(substr(l_char_column_name, 10));
1472 -- END IF;
1473 --
1474
1475 --
1476 -- For each plan element to be copied, adjust the
1477 -- prompt_sequence and result_column_name.
1478 --
1479
1480 FOR i IN char_ids.FIRST .. char_ids.LAST LOOP
1481 IF result_column_names(i) LIKE 'CHARACTER%' THEN
1482 --
1483 -- Bug 3926150.
1484 -- Change the result column assignment code to use the new
1485 -- suggest_result_column function.
1486 -- bso Fri Dec 3 22:30:23 PST 2004
1487 --
1488 result_column_names(i) := suggest_result_column(p_copy_to_plan_id,
1489 char_ids(i));
1490 IF result_column_names(i) IS NULL THEN
1491 fnd_message.set_name('QA', 'QA_EXCEEDED_COLUMN_COUNT');
1492 fnd_msg_pub.add();
1493 raise fnd_api.g_exc_error;
1494 END IF;
1495 mark_result_column(result_column_names(i));
1496 END IF;
1497
1498 prompt_sequences(i) := l_prompt_sequence;
1499 l_prompt_sequence := l_prompt_sequence + 10;
1500 END LOOP;
1501
1502 --
1503 -- Clear the values_exist_flags if values are not copied.
1504 --
1505 IF p_copy_values_flag = fnd_api.g_false THEN
1506 FOR i IN char_ids.FIRST .. char_ids.LAST LOOP
1507 values_exist_flags(i) := 2;
1508 END LOOP;
1509 END IF;
1510
1511 -- Bug 3726391. shkalyan 28 June 2004
1512 -- Added insert of missing columns viz.
1513 -- decimal_precision,uom_code,attribute_category,attribute1 to attribute15
1514
1515 -- Bug 3726391. shkalyan 30 June 2004
1516 -- Removed insert of attribute_category,attribute1 to attribute15
1517 -- As per code review comments
1518
1519 -- Bug 6734330
1520 -- Device Integration Project
1524
1521 -- Added device columns device_id,
1522 -- device_flag and override_flags.
1523 -- bhsankar Mon Jan 7 22:00:17 PST 2008
1525 FORALL i IN char_ids.FIRST .. char_ids.LAST
1526 INSERT INTO qa_plan_chars(
1527 plan_id,
1528 char_id,
1529 last_update_date,
1530 last_updated_by,
1531 creation_date,
1532 created_by,
1533 last_update_login,
1534 prompt_sequence,
1535 prompt,
1536 enabled_flag,
1537 mandatory_flag,
1538 default_value,
1539 result_column_name,
1540 values_exist_flag,
1541 displayed_flag,
1542 default_value_id,
1543 read_only_flag,
1544 ss_poplist_flag,
1545 information_flag,
1546 decimal_precision,
1547 uom_code,
1548 device_flag,
1549 device_id,
1550 override_flag)
1551 VALUES (
1552 p_copy_to_plan_id,
1553 char_ids(i),
1554 sysdate,
1555 p_user_id,
1556 sysdate,
1557 p_user_id,
1558 p_user_id,
1559 prompt_sequences(i),
1560 prompts(i),
1561 enabled_flags(i),
1562 mandatory_flags(i),
1563 default_values(i),
1564 result_column_names(i),
1565 values_exist_flags(i),
1566 displayed_flags(i),
1567 default_value_ids(i),
1568 read_only_flags(i),
1569 ss_poplist_flags(i),
1570 information_flags(i),
1571 decimal_precisions(i),
1572 uom_codes(i),
1573 device_flags(i),
1574 device_ids(i),
1575 override_flags(i)
1576 );
1577
1578 END copy_plan_elements_bulk;
1579
1580
1581 PROCEDURE copy_plan_element_values(p_copy_from_plan_id IN NUMBER,
1582 p_copy_to_plan_id IN NUMBER, p_user_id IN NUMBER) IS
1583
1584 BEGIN
1585
1586 --
1587 -- Explain plan shows NOT IN performance is OK.
1588 --
1589 INSERT INTO qa_plan_char_value_lookups(
1590 plan_id,
1591 char_id,
1592 short_code,
1593 description,
1594 last_update_date,
1595 last_updated_by,
1596 last_update_login,
1597 creation_date,
1598 created_by,
1599 short_code_id)
1600 SELECT
1601 p_copy_to_plan_id,
1602 char_id,
1603 short_code,
1604 description,
1605 sysdate,
1606 p_user_id,
1607 p_user_id,
1608 sysdate,
1609 created_by,
1610 short_code_id
1611 FROM qa_plan_char_value_lookups
1612 WHERE plan_id = p_copy_from_plan_id AND char_id NOT IN
1613 (SELECT char_id
1614 FROM qa_plan_chars
1615 WHERE plan_id = p_copy_to_plan_id);
1616
1617 END copy_plan_element_values;
1618
1619
1620 PROCEDURE copy_plan_element_actions(p_copy_from_plan_id IN NUMBER,
1621 p_copy_to_plan_id IN NUMBER, p_user_id IN NUMBER) IS
1622
1623 CURSOR action_trigger_cursor is
1624 SELECT
1625 plan_char_action_trigger_id,
1626 trigger_sequence,
1627 plan_id,
1628 char_id,
1629 operator,
1630 low_value_lookup,
1631 high_value_lookup,
1632 low_value_other,
1633 high_value_other,
1634 low_value_other_id,
1635 high_value_other_id
1636 FROM qa_plan_char_action_triggers
1637 WHERE plan_id = p_copy_from_plan_id AND char_id NOT IN
1638 (SELECT char_id
1639 FROM qa_plan_chars
1640 WHERE plan_id = p_copy_to_plan_id)
1641 ORDER BY trigger_sequence;
1642
1643 CURSOR action_cursor(x NUMBER) IS
1644 SELECT
1645 plan_char_action_id,
1646 plan_char_action_trigger_id,
1647 action_id,
1648 car_name_prefix,
1649 car_type_id,
1650 car_owner,
1651 message,
1652 status_code,
1653 alr_action_id,
1654 alr_action_set_id,
1655 assigned_char_id,
1656 assign_type
1657 FROM qa_plan_char_actions
1658 WHERE plan_char_action_trigger_id = x
1659 ORDER BY plan_char_action_id;
1660
1661 -- Bug 3111310
1662 -- Modified the cursor for SQL performance fix
1663 -- saugupta Mon Sep 8 06:00:06 PDT 2003
1664
1665 CURSOR alert_cursor(x NUMBER) is
1666 SELECT
1667 application_id,
1668 action_id,
1669 name,
1670 alert_id,
1671 action_type,
1672 end_date_active,
1673 enabled_flag,
1674 description,
1675 action_level_type,
1676 date_last_executed,
1677 file_name,
1678 argument_string,
1679 program_application_id,
1680 concurrent_program_id,
1684 cc_recipients,
1681 list_application_id,
1682 list_id,
1683 to_recipients,
1685 bcc_recipients,
1686 print_recipients,
1687 printer,
1688 subject,
1689 reply_to,
1690 response_set_id,
1691 follow_up_after_days,
1692 column_wrap_flag,
1693 maximum_summary_message_width,
1694 body,
1695 version_number
1696 FROM alr_actions
1697 WHERE action_id = x
1698 AND application_id = 250;
1699
1700 alra alert_cursor%ROWTYPE;
1701
1702 l_qpcat_id NUMBER;
1703 l_qpca_id NUMBER;
1704
1705 l_action_set_seq NUMBER;
1706 l_action_set_members_seq NUMBER;
1707 l_action_name_seq NUMBER;
1708 l_action_set_name_seq NUMBER;
1709
1710 new_action_id NUMBER;
1711 new_action_set_id NUMBER;
1712 new_action_set_member_id NUMBER;
1713 new_action_name alr_actions.name%TYPE;
1714 new_action_set_name alr_action_sets.name%TYPE;
1715
1716
1717 BEGIN
1718
1719 FOR qpcat IN action_trigger_cursor LOOP
1720
1721 SELECT qa_plan_char_action_triggers_s.nextval
1722 INTO l_qpcat_id
1723 FROM dual;
1724
1725 INSERT INTO qa_plan_char_action_triggers (
1726 plan_char_action_trigger_id,
1727 last_update_date,
1728 last_updated_by,
1729 creation_date,
1730 created_by,
1731 last_update_login,
1732 trigger_sequence,
1733 plan_id,
1734 char_id,
1735 operator,
1736 low_value_lookup,
1737 high_value_lookup,
1738 low_value_other,
1739 high_value_other,
1740 low_value_other_id,
1741 high_value_other_id)
1742 VALUES (
1743 l_qpcat_id,
1744 sysdate,
1745 p_user_id,
1746 sysdate,
1747 p_user_id,
1748 p_user_id,
1749 qpcat.trigger_sequence,
1750 p_copy_to_plan_id,
1751 qpcat.char_id,
1752 qpcat.operator,
1753 qpcat.low_value_lookup,
1754 qpcat.high_value_lookup,
1755 qpcat.low_value_other,
1756 qpcat.high_value_other,
1757 qpcat.low_value_other_id,
1758 qpcat.high_value_other_id);
1759
1760 -- Bug 5300577
1761 -- Included this condition to get the translated value of
1762 -- ACCEPT and REJECT so that Action for the inspection Result
1763 -- element fires accurately.
1764 -- Included Template OPM Recieving inspection plan because
1765 -- conversion is required for these plans as well.
1766
1767 IF p_copy_from_plan_id IN (1,2147483637) AND
1768 qpcat.low_value_other IN ('ACCEPT', 'REJECT') THEN
1769
1770 UPDATE QA_PLAN_CHAR_ACTION_TRIGGERS
1771 SET low_value_other = (SELECT displayed_field
1772 FROM PO_LOOKUP_CODES
1773 WHERE lookup_type = 'ERT RESULTS ACTION'
1774 AND lookup_code = qpcat.low_value_other)
1775 WHERE plan_char_action_trigger_id = l_qpcat_id;
1776 END IF;
1777
1778 FOR qpca IN action_cursor(qpcat.plan_char_action_trigger_id) LOOP
1779
1780 SELECT qa_plan_char_actions_s.nextval
1781 INTO l_qpca_id
1782 FROM dual;
1783
1784 --
1785 -- These are alert actions, generate new alert action IDs
1786 --
1787 IF qpca.action_id IN (10, 11, 12, 13) AND
1788 qpca.alr_action_id IS NOT NULL THEN
1789
1790 SELECT
1791 alr_actions_s.nextval,
1792 alr_action_sets_s.nextval,
1793 alr_action_set_members_s.nextval,
1794 qa_alr_action_name_s.nextval,
1795 qa_alr_action_set_name_s.nextval
1796 INTO
1797 new_action_id,
1798 new_action_set_id,
1799 new_action_set_member_id,
1800 l_action_name_seq,
1801 l_action_set_name_seq
1802 FROM dual;
1803
1804 --
1805 -- Some action details are stored in Oracle Alert tables
1806 -- with alert ID 10177. Copy the header and recreate new
1807 -- alert actions for the new plan.
1808 --
1809 OPEN alert_cursor(qpca.alr_action_id);
1810 FETCH alert_cursor INTO alra;
1811 IF alert_cursor%FOUND THEN
1812
1813 new_action_name := 'qa_' || l_action_name_seq;
1814 new_action_set_name := 'qa_' || l_action_set_name_seq;
1815
1816 INSERT INTO alr_actions (
1817 application_id,
1818 action_id,
1819 name,
1820 alert_id,
1824 creation_date,
1821 action_type,
1822 last_update_date,
1823 last_updated_by,
1825 created_by,
1826 last_update_login,
1827 end_date_active,
1828 enabled_flag,
1829 description,
1830 action_level_type,
1831 date_last_executed,
1832 file_name,
1833 argument_string,
1834 program_application_id,
1835 concurrent_program_id,
1836 list_application_id,
1837 list_id,
1838 to_recipients,
1839 cc_recipients,
1840 bcc_recipients,
1841 print_recipients,
1842 printer,
1843 subject,
1844 reply_to,
1845 response_set_id,
1846 follow_up_after_days,
1847 column_wrap_flag,
1848 maximum_summary_message_width,
1849 body,
1850 version_number)
1851 VALUES (
1852 alra.application_id,
1853 new_action_id,
1854 new_action_name,
1855 alra.alert_id,
1856 alra.action_type,
1857 sysdate,
1858 p_user_id,
1859 sysdate,
1860 p_user_id,
1861 p_user_id,
1862 alra.end_date_active,
1863 alra.enabled_flag,
1864 alra.description,
1865 alra.action_level_type,
1866 alra.date_last_executed,
1867 alra.file_name,
1868 alra.argument_string,
1869 alra.program_application_id,
1870 alra.concurrent_program_id,
1871 alra.list_application_id,
1872 alra.list_id,
1873 alra.to_recipients,
1874 alra.cc_recipients,
1875 alra.bcc_recipients,
1876 alra.print_recipients,
1877 alra.printer,
1878 alra.subject,
1879 alra.reply_to,
1880 alra.response_set_id,
1881 alra.follow_up_after_days,
1882 alra.column_wrap_flag,
1883 alra.maximum_summary_message_width,
1884 alra.body,
1885 alra.version_number
1886 );
1887
1888 BEGIN
1889 SELECT nvl(max(sequence),0) + 1
1890 INTO l_action_set_seq
1891 FROM alr_action_sets
1892 WHERE application_id = 250 AND alert_id = 10177;
1893
1894 EXCEPTION
1895 WHEN no_data_found THEN
1896 l_action_set_seq := 1;
1897 END;
1898
1899 INSERT INTO alr_action_sets (
1900 application_id,
1901 action_set_id,
1902 name,
1903 alert_id,
1904 last_update_date,
1905 last_updated_by,
1906 creation_date,
1907 created_by,
1908 last_update_login,
1909 end_date_active,
1910 enabled_flag,
1911 recipients_view_only_flag,
1912 description,
1913 suppress_flag,
1914 suppress_days,
1915 sequence)
1916 VALUES (
1917 250,
1918 new_action_set_id,
1919 new_action_set_name,
1920 10177,
1921 sysdate,
1922 p_user_id,
1923 sysdate,
1924 p_user_id,
1925 p_user_id,
1926 null,
1927 'Y',
1928 'N',
1929 new_action_set_name,
1930 'N',
1931 null,
1932 l_action_set_seq);
1933
1934 BEGIN
1935 SELECT nvl(max(sequence),0) + 1
1936 INTO l_action_set_members_seq
1937 FROM alr_action_set_members
1938 WHERE application_id = 250 AND
1939 alert_id = 10177 AND
1940 action_set_id = new_action_set_id;
1941 EXCEPTION
1942 WHEN no_data_found THEN
1946 INSERT INTO alr_action_set_members (
1943 l_action_set_members_seq := 1;
1944 END;
1945
1947 application_id,
1948 action_set_member_id,
1949 action_set_id,
1950 action_id,
1951 action_group_id,
1952 alert_id,
1953 sequence,
1954 last_update_date,
1955 last_updated_by,
1956 creation_date,
1957 created_by,
1958 last_update_login,
1959 end_date_active,
1960 enabled_flag,
1961 summary_threshold,
1962 abort_flag,
1963 error_action_sequence)
1964 VALUES (
1965 250,
1966 new_action_set_member_id,
1967 new_action_set_id,
1968 new_action_id,
1969 null,
1970 10177,
1971 l_action_set_members_seq,
1972 sysdate,
1973 p_user_id,
1974 sysdate,
1975 p_user_id,
1976 p_user_id,
1977 null,
1978 'Y',
1979 null,
1980 'A',
1981 null
1982 );
1983
1984 END IF; -- alert_cursor%FOUND (this is an alert action)
1985
1986 CLOSE alert_cursor;
1987
1988 END IF; -- the action id is 10, 11, 12, 13 (alert actions)
1989
1990 INSERT INTO qa_plan_char_actions (
1991 plan_char_action_id,
1992 last_update_date,
1993 last_updated_by,
1994 creation_date,
1995 created_by,
1996 last_update_login,
1997 plan_char_action_trigger_id,
1998 action_id,
1999 car_name_prefix,
2000 car_type_id,
2001 car_owner,
2002 message,
2003 status_code,
2004 alr_action_id,
2005 alr_action_set_id,
2006 assigned_char_id,
2007 assign_type)
2008 VALUES (
2009 l_qpca_id,
2010 sysdate,
2011 p_user_id,
2012 sysdate,
2013 p_user_id,
2014 p_user_id,
2015 l_qpcat_id,
2016 qpca.action_id,
2017 qpca.car_name_prefix,
2018 qpca.car_type_id,
2019 qpca.car_owner,
2020 qpca.message,
2021 qpca.status_code,
2022 decode(qpca.action_id,
2023 10, new_action_id,
2024 11, new_action_id,
2025 12, new_action_id,
2026 13, new_action_id,
2027 qpca.action_id),
2028 decode(qpca.action_id,
2029 10, new_action_set_id,
2030 11, new_action_set_id,
2031 12, new_action_set_id,
2032 13, new_action_set_id,
2033 qpca.action_id),
2034 qpca.assigned_char_id,
2035 qpca.assign_type);
2036
2037 INSERT INTO qa_plan_char_action_outputs (
2038 plan_char_action_id,
2039 char_id,
2040 last_update_date,
2041 last_updated_by,
2042 creation_date,
2043 created_by,
2044 last_update_login,
2045 token_name)
2046 SELECT
2047 l_qpca_id,
2048 char_id,
2049 sysdate,
2050 p_user_id,
2051 sysdate,
2052 p_user_id,
2053 p_user_id,
2054 token_name
2055 FROM qa_plan_char_action_outputs
2056 WHERE plan_char_action_id = qpca.plan_char_action_id;
2057
2058 END LOOP; -- action_cursor
2059
2060 END LOOP; -- action_trigger_cursor
2061
2062 END copy_plan_element_actions;
2063
2064
2065 PROCEDURE copy_plan_transactions(p_copy_from_plan_id IN NUMBER,
2066 p_copy_to_plan_id IN NUMBER, p_user_id IN NUMBER) IS
2067
2068 CURSOR txn_cursor IS
2069 SELECT
2070 plan_transaction_id,
2071 last_update_date,
2072 last_updated_by,
2073 creation_date,
2074 created_by,
2075 last_update_login,
2076 plan_id,
2077 transaction_number,
2078 mandatory_collection_flag,
2079 background_collection_flag,
2080 enabled_flag
2081 FROM qa_plan_transactions
2082 WHERE plan_id = p_copy_from_plan_id;
2083
2087 SELECT
2084 l_plan_transaction_id NUMBER;
2085
2086 CURSOR txn_trigger_cursor(x NUMBER) IS
2088 txn_trigger_id,
2089 last_update_date,
2090 last_updated_by,
2091 creation_date,
2092 created_by,
2093 last_update_login,
2094 plan_transaction_id,
2095 collection_trigger_id,
2096 operator,
2097 low_value,
2098 low_value_id,
2099 high_value,
2100 high_value_id
2101 FROM qa_plan_collection_triggers
2102 WHERE plan_transaction_id = x;
2103
2104 BEGIN
2105
2106 FOR qpt IN txn_cursor LOOP
2107
2108 SELECT qa_plan_transactions_s.nextval
2109 INTO l_plan_transaction_id
2110 FROM dual;
2111
2112 INSERT INTO qa_plan_transactions (
2113 plan_transaction_id,
2114 last_update_date,
2115 last_updated_by,
2116 creation_date,
2117 created_by,
2118 last_update_login,
2119 plan_id,
2120 transaction_number,
2121 mandatory_collection_flag,
2122 background_collection_flag,
2123 enabled_flag)
2124 VALUES (
2125 l_plan_transaction_id,
2126 sysdate,
2127 p_user_id,
2128 sysdate,
2129 p_user_id,
2130 p_user_id,
2131 p_copy_to_plan_id,
2132 qpt.transaction_number,
2133 qpt.mandatory_collection_flag,
2134 qpt.background_collection_flag,
2135 qpt.enabled_flag);
2136
2137 FOR qpct IN txn_trigger_cursor(qpt.plan_transaction_id) LOOP
2138
2139 INSERT INTO qa_plan_collection_triggers (
2140 txn_trigger_id,
2141 last_update_date,
2142 last_updated_by,
2143 creation_date,
2144 created_by,
2145 last_update_login,
2146 plan_transaction_id,
2147 collection_trigger_id,
2148 operator,
2149 low_value,
2150 low_value_id,
2151 high_value,
2152 high_value_id)
2153 VALUES (
2154 qa_txn_trigger_ids_s.nextval,
2155 sysdate,
2156 p_user_id,
2157 sysdate,
2158 p_user_id,
2159 p_user_id,
2160 l_plan_transaction_id,
2161 qpct.collection_trigger_id,
2162 qpct.operator,
2163 qpct.low_value,
2164 qpct.low_value_id,
2165 qpct.high_value,
2166 qpct.high_value_id);
2167
2168 END LOOP; -- transaction triggers
2169
2170 END LOOP; -- transaction
2171
2172 END copy_plan_transactions;
2173
2174
2175 --
2176 -- Private functions for plan and plan element deletions.
2177 --
2178
2179 FUNCTION results_exist(p_plan_Id IN NUMBER, p_element_id IN NUMBER)
2180 RETURN BOOLEAN IS
2181
2182 TYPE ref_cursor IS REF CURSOR;
2183 c ref_cursor;
2184
2185 l_dummy NUMBER;
2186 l_found BOOLEAN;
2187 l_result_column_name qa_plan_chars.result_column_name%TYPE;
2188 l_sql_statement VARCHAR2(200);
2189
2190 BEGIN
2191
2192 l_result_column_name := qa_plan_element_api.get_result_column_name(
2193 p_plan_id, p_element_id);
2194
2195 IF l_result_column_name IS NULL THEN
2196 RETURN true;
2197 END IF;
2198
2199 l_sql_statement :=
2200 'SELECT 1 FROM qa_results WHERE plan_id = :id AND rownum = 1 AND ' ||
2201 l_result_column_name || ' IS NOT NULL';
2202
2203 OPEN c FOR l_sql_statement USING p_plan_id;
2204 FETCH c INTO l_dummy;
2205 l_found := c%FOUND;
2206 CLOSE c;
2207
2208 RETURN l_found;
2209
2210 END results_exist;
2211
2212
2213 FUNCTION results_exist(p_plan_id IN NUMBER) RETURN BOOLEAN IS
2214
2215 TYPE numType IS REF CURSOR;
2216
2217 CURSOR c IS
2218 SELECT 1
2219 FROM qa_results
2220 WHERE plan_id = p_plan_id AND rownum = 1;
2221
2222 l_dummy NUMBER;
2223 l_found BOOLEAN;
2224
2225 BEGIN
2226
2227 OPEN c;
2228 FETCH c INTO l_dummy;
2229 l_found := c%FOUND;
2230 CLOSE c;
2231
2232 RETURN l_found;
2233
2234 END results_exist;
2235
2236
2237 PROCEDURE delete_plan_element_actions(p_plan_id IN NUMBER,
2238 p_element_id IN NUMBER) IS
2239
2240 pca_ids number_tab;
2241 pcat_ids number_tab;
2242
2243 BEGIN
2244
2245 DELETE FROM qa_plan_char_action_triggers
2246 WHERE plan_id = p_plan_id AND char_id = p_element_id
2247 RETURNING plan_char_action_trigger_id BULK COLLECT INTO pcat_ids;
2248
2249 IF pcat_ids.COUNT = 0 THEN
2250 RETURN;
2251 END IF;
2252
2256 RETURNING plan_char_action_id BULK COLLECT INTO pca_ids;
2253 FORALL i IN pcat_ids.FIRST .. pcat_ids.LAST
2254 DELETE FROM qa_plan_char_actions
2255 WHERE plan_char_action_trigger_id = pcat_ids(i)
2257
2258 IF pca_ids.COUNT = 0 THEN
2259 RETURN;
2260 END IF;
2261
2262 FORALL i IN pca_ids.FIRST .. pca_ids.LAST
2263 DELETE FROM qa_plan_char_action_outputs
2264 WHERE plan_char_action_id = pca_ids(i);
2265 --
2266 -- ### Do we need to delete the alert records?
2267 --
2268
2269 END delete_plan_element_actions;
2270
2271
2272 PROCEDURE delete_plan_element_values(p_plan_id IN NUMBER,
2273 p_element_id IN NUMBER) IS
2274 BEGIN
2275 DELETE FROM qa_plan_char_value_lookups
2276 WHERE plan_id = p_plan_id AND char_id = p_element_id;
2277 END delete_plan_element_values;
2278
2279
2280 PROCEDURE delete_plan_element(p_plan_id IN NUMBER, p_element_id IN NUMBER) IS
2281 l_result_column qa_plan_chars.result_column_name%TYPE;
2282 BEGIN
2283 DELETE FROM qa_plan_chars
2284 WHERE plan_id = p_plan_id AND char_id = p_element_id
2285 RETURNING result_column_name
2286 INTO l_result_column; -- needed for Bug 3926150
2287
2288 --
2289 -- Bug 3926150. Check if the deleted element will disrupt a
2290 -- function-based index. If so, add info message to the msg stack.
2291 -- bso Sat Dec 4 16:08:07 PST 2004
2292 --
2293 IF l_result_column LIKE 'CHARACTER%' AND l_result_column <>
2294 qa_char_indexes_pkg.get_default_result_column(p_element_id) THEN
2295 disable_index_private(p_element_id);
2296 END IF;
2297
2298 END delete_plan_element;
2299
2300
2301 PROCEDURE delete_plan_elements(p_plan_id IN NUMBER) IS
2302 --
2303 -- Bug 3926150. Need to warn user if function-based index
2304 -- is disrupted due to this action.
2305 --
2306 CURSOR c IS
2307 SELECT qpc.char_id
2308 FROM qa_plan_chars qpc, qa_char_indexes qci
2309 WHERE qpc.plan_id = p_plan_id AND
2310 qpc.char_id = qci.char_id AND
2311 qpc.result_column_name <> qci.default_result_column;
2312 BEGIN
2313 --
2314 -- Bug 3926150. Minor revamp of this procedure from a simple
2315 -- delete of all plan_chars to a disable index and delete.
2316 -- bso Sun Dec 5 11:54:53 PST 2004
2317 --
2318 FOR r IN c LOOP
2319 disable_index_private(r.char_id);
2320 END LOOP;
2321
2322 DELETE FROM qa_plan_chars
2323 WHERE plan_id = p_plan_id;
2324
2325 END delete_plan_elements;
2326
2327
2328 PROCEDURE delete_plan_values(p_plan_id IN NUMBER) IS
2329 BEGIN
2330 DELETE FROM qa_plan_char_value_lookups
2331 WHERE plan_id = p_plan_id;
2332 END delete_plan_values;
2333
2334
2335 PROCEDURE delete_plan_transactions(p_plan_id IN NUMBER) IS
2336
2337 pt_ids number_tab;
2338
2339 BEGIN
2340
2341 DELETE FROM qa_plan_transactions
2342 WHERE plan_id = p_plan_id
2343 RETURNING plan_transaction_id BULK COLLECT INTO pt_ids;
2344
2345 IF pt_ids.COUNT = 0 THEN
2346 RETURN;
2347 END IF;
2348
2349 FORALL i IN pt_ids.FIRST .. pt_ids.LAST
2350 DELETE FROM qa_plan_collection_triggers
2351 WHERE plan_transaction_id = pt_ids(i);
2352
2353 END delete_plan_transactions;
2354
2355
2356 PROCEDURE delete_plan_actions(p_plan_id IN NUMBER) IS
2357
2358 pcat_ids number_tab;
2359 pca_ids number_tab;
2360
2361 BEGIN
2362
2363 --
2364 -- Delete all triggers, collecting their primary keys
2365 --
2366 DELETE FROM qa_plan_char_action_triggers
2367 WHERE plan_id = p_plan_id
2368 RETURNING plan_char_action_trigger_id BULK COLLECT INTO pcat_ids;
2369
2370 IF pcat_ids.COUNT = 0 THEN
2371 RETURN;
2372 END IF;
2373
2374 --
2375 -- Now delete all children actions
2376 --
2377 FORALL i IN pcat_ids.FIRST .. pcat_ids.LAST
2378 DELETE FROM qa_plan_char_actions
2379 WHERE plan_char_action_trigger_id = pcat_ids(i)
2380 RETURNING plan_char_action_id BULK COLLECT INTO pca_ids;
2381
2382 IF pca_ids.COUNT = 0 THEN
2383 RETURN;
2384 END IF;
2385
2386 --
2387 -- Some actions have action outputs... delete them.
2388 --
2389 FORALL i IN pca_ids.FIRST .. pca_ids.LAST
2390 DELETE FROM qa_plan_char_action_outputs
2391 WHERE plan_char_action_id = pca_ids(i);
2392
2393 --
2394 -- ### Do we need to delete the alert records?
2395 --
2396 END delete_plan_actions;
2397
2398
2399 PROCEDURE delete_plan_header(p_plan_id IN NUMBER) IS
2400
2401 BEGIN
2402
2403 DELETE FROM qa_plans
2404 WHERE plan_id = p_plan_id;
2405
2406 END delete_plan_header;
2407
2408
2409 --
2410 -- This procedure is called to commit a deleted plan.
2411 --
2412 PROCEDURE delete_plan_private(
2413 p_plan_name VARCHAR2,
2414 p_commit VARCHAR2,
2415 p_user_id NUMBER) IS
2416
2420 COMMIT;
2417 l_request_id NUMBER;
2418 BEGIN
2419 IF fnd_api.to_boolean(p_commit) THEN
2421
2422 --
2423 -- The dynamic view creator can be used to delete the
2424 -- redundant plan view once a plan is deleted.
2425 --
2426 -- Launch the dynamic view creator only if the user
2427 -- commits; otherwise, the view generator will fail
2428 -- because it is run in another database session.
2429 --
2430 -- The init is required to get the concurrent program
2431 -- to run. The resp_id 20561 is the seeded main Quality
2432 -- responsibility. 250 is Oracle Quality's application ID.
2433 --
2434 fnd_global.apps_initialize(
2435 user_id => p_user_id,
2436 resp_id => 20561,
2437 resp_appl_id => 250);
2438
2439 l_request_id := fnd_request.submit_request(
2440 application => 'QA',
2441 program => 'QLTPVWWB',
2442 argument1 => NULL,
2443 argument2 => get_plan_view_name(p_plan_name),
2444 argument3 => NULL,
2445 argument4 => NULL,
2446 argument5 => get_import_view_name(p_plan_name),
2447 argument6 => 'QA_GLOBAL_RESULTS_V');
2448
2449 COMMIT;
2450 END IF;
2451 END delete_plan_private;
2452
2453 -- 12.1 Device Integration Project
2454 -- Procedure to get the device_id, override_flag
2455 -- for the device_name, sensor_alias combination
2456 -- bhsankar Mon Nov 12 05:51:37 PST 2007
2457 PROCEDURE get_device_details (p_device_name IN VARCHAR2,
2458 p_sensor_alias IN VARCHAR2,
2459 x_device_id OUT NOCOPY NUMBER,
2460 x_override_flag OUT NOCOPY NUMBER) IS
2461
2462 CURSOR c IS
2463 SELECT device_id, override_flag
2464 FROM qa_device_info
2465 WHERE device_name = p_device_name
2466 AND sensor_alias = p_sensor_alias
2467 AND enabled_flag = 1;
2468
2469 BEGIN
2470
2471 OPEN c;
2472 FETCH c INTO x_device_id, x_override_flag;
2473 CLOSE c;
2474
2475 END get_device_details;
2476
2477 --
2478 --
2479 --
2480 -- Start of public API functions
2481 --
2482 --
2483 -- 12.1 QWB USability Improvements
2484 -- Added the parameter P_multirow_flag
2485 -- ntungare
2486 --
2487 PROCEDURE create_collection_plan(
2488 p_api_version IN NUMBER,
2489 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2490 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2491 p_user_name IN VARCHAR2 := NULL,
2492 p_plan_name IN VARCHAR2,
2493 p_organization_code IN VARCHAR2,
2494 p_plan_type IN VARCHAR2,
2495 p_description IN VARCHAR2 := NULL,
2496 p_effective_from IN DATE := sysdate,
2497 p_effective_to IN DATE := NULL,
2498 p_spec_assignment_type IN NUMBER := qa_plans_pub.g_spec_type_none,
2499 p_multirow_flag IN NUMBER := 2,
2500 x_plan_id OUT NOCOPY NUMBER,
2501 x_msg_count OUT NOCOPY NUMBER,
2502 x_msg_data OUT NOCOPY VARCHAR2,
2503 x_return_status OUT NOCOPY VARCHAR2) IS
2504
2505 l_api_name CONSTANT VARCHAR2(30) := 'create_plan';
2506 l_api_version CONSTANT NUMBER := 1.0;
2507
2508 l_org_id NUMBER;
2509 l_user_id NUMBER;
2510 l_plan_type_code VARCHAR2(30);
2511 l_plan_name qa_plans.name%TYPE;
2512 l_plan_view qa_plans.view_name%TYPE;
2513 l_import_view qa_plans.import_view_name%TYPE;
2514
2515 BEGIN
2516
2517 -- Standard Start of API savepoint
2518
2519 SAVEPOINT create_plan_pub;
2520
2521 -- Standard call to check for call compatibility.
2522 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
2523 l_api_name, g_pkg_name) THEN
2524 RAISE fnd_api.g_exc_unexpected_error;
2525 END IF;
2526
2527 -- Initialize message list if p_init_msg_list is set to TRUE.
2528 IF fnd_api.to_boolean(p_init_msg_list) THEN
2529 fnd_msg_pub.initialize;
2530 END IF;
2531
2532 -- Initialize API return status to success
2533 x_return_status := fnd_api.g_ret_sts_success;
2534
2535
2536 -- *** start of logic ***
2537
2538 --
2539 -- Bug 3926150. init the result column array. -1 indicates
2540 -- a brand new plan is being created.
2541 -- bso Fri Dec 3 20:55:05 PST 2004
2542 --
2543 init_result_column_array(-1);
2544
2545 l_user_id := get_user_id(p_user_name);
2546 IF l_user_id = -1 THEN
2547 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
2548 fnd_msg_pub.add();
2549 raise fnd_api.g_exc_error;
2550 END IF;
2551
2552 l_plan_name := upper(p_plan_name);
2553 IF (illegal_chars(l_plan_name)) THEN
2554 fnd_message.set_name('QA', 'QA_NAME_SPECIAL_CHARS');
2558
2555 fnd_msg_pub.add();
2556 raise fnd_api.g_exc_error;
2557 END IF;
2559 l_org_id := qa_plans_api.get_org_id(p_organization_code);
2560 IF (l_org_id IS NULL) THEN
2561 fnd_message.set_name('QA', 'QA_API_INVALID_ORG_CODE');
2562 fnd_msg_pub.add();
2563 raise fnd_api.g_exc_error;
2564 END IF;
2565
2566 IF NOT valid_plan_type(p_plan_type) THEN
2567 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN_TYPE');
2568 fnd_msg_pub.add();
2569 raise fnd_api.g_exc_error;
2570 ELSE
2571 l_plan_type_code := get_plan_type_code(p_plan_type);
2572 END IF;
2573
2574
2575 -- If the name passed as the plan name already exists then
2576 -- generate an error.
2577
2578 IF plan_exists(l_plan_name) > 0 THEN
2579 fnd_message.set_name('QA', 'QA_PLAN_RECORD_EXISTS');
2580 fnd_msg_pub.add();
2581 raise fnd_api.g_exc_error;
2582 END IF;
2583
2584 IF (p_effective_to < p_effective_from) THEN
2585 fnd_message.set_name('QA', 'QA_EFFECTIVE_DATE_RANGE');
2586 fnd_msg_pub.add();
2587 raise fnd_api.g_exc_error;
2588 END IF;
2589
2590 l_plan_view := get_plan_view_name(l_plan_name);
2591 l_import_view := get_import_view_name(l_plan_name);
2592
2593 SELECT qa_plans_s.nextval INTO x_plan_id FROM dual;
2594
2595 INSERT INTO qa_plans(
2596 plan_id,
2597 organization_id,
2598 last_update_date,
2599 last_updated_by,
2600 creation_date,
2601 created_by,
2602 last_update_login,
2603 name,
2604 plan_type_code,
2605 spec_assignment_type,
2606 description,
2607 import_view_name,
2608 view_name,
2609 effective_from,
2610 effective_to,
2611 multirow_flag)
2612 VALUES(
2613 x_plan_id,
2614 l_org_id,
2615 sysdate,
2616 l_user_id,
2617 sysdate,
2618 l_user_id,
2619 l_user_id,
2620 l_plan_name,
2621 l_plan_type_code,
2622 p_spec_assignment_type,
2623 p_description,
2624 l_import_view,
2625 l_plan_view,
2626 p_effective_from,
2627 p_effective_to,
2628 p_multirow_flag);
2629
2630 EXCEPTION
2631
2632 WHEN fnd_api.g_exc_error THEN
2633 ROLLBACK TO create_plan_pub;
2634 x_return_status := fnd_api.g_ret_sts_error;
2635 fnd_msg_pub.count_and_get(
2636 p_count => x_msg_count,
2637 p_data => x_msg_data
2638 );
2639
2640 WHEN fnd_api.g_exc_unexpected_error THEN
2641 ROLLBACK TO create_plan_pub;
2642 x_return_status := fnd_api.g_ret_sts_unexp_error;
2643 fnd_msg_pub.count_and_get(
2644 p_count => x_msg_count,
2645 p_data => x_msg_data
2646 );
2647
2648 WHEN OTHERS THEN
2649 ROLLBACK TO create_plan_pub;
2650 x_return_status := fnd_api.g_ret_sts_unexp_error;
2651 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2652 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2653 END IF;
2654 fnd_msg_pub.count_and_get(
2655 p_count => x_msg_count,
2656 p_data => x_msg_data
2657 );
2658
2659 END create_collection_plan;
2660
2661
2662 PROCEDURE add_plan_element(
2663 p_api_version IN NUMBER,
2664 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2665 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2666 p_user_name IN VARCHAR2 := NULL,
2667 p_plan_name IN VARCHAR2,
2668 p_organization_code IN VARCHAR2,
2669 p_element_name IN VARCHAR2,
2670 p_prompt_sequence IN NUMBER := NULL,
2671 p_prompt IN VARCHAR2 := g_inherit,
2672 p_default_value IN VARCHAR2 := g_inherit,
2673 p_enabled_flag IN VARCHAR2 := fnd_api.g_true,
2674 p_mandatory_flag IN VARCHAR2 := g_inherit,
2675 p_displayed_flag IN VARCHAR2 := fnd_api.g_true,
2676 p_read_only_flag IN VARCHAR2 := NULL,
2677 p_ss_poplist_flag IN VARCHAR2 := NULL,
2678 p_information_flag IN VARCHAR2 := NULL,
2679 p_result_column_name IN VARCHAR2 := NULL,
2680 -- 12.1 Device Integration Project
2681 -- bhsankar Mon Nov 12 05:51:37 PST 2007
2682 p_device_flag IN VARCHAR2 := NULL,
2683 p_device_name IN VARCHAR2 := NULL,
2684 p_sensor_alias IN VARCHAR2 := NULL,
2685 p_override_flag IN VARCHAR2 := NULL,
2686 -- 12.1 Device Integration Project End.
2687 x_msg_count OUT NOCOPY NUMBER,
2688 x_msg_data OUT NOCOPY VARCHAR2,
2689 x_return_status OUT NOCOPY VARCHAR2) IS
2690
2691
2692 l_api_name CONSTANT VARCHAR2(30) := 'add_element';
2693 l_api_version CONSTANT NUMBER := 1.0;
2694
2695 l_user_id NUMBER;
2699
2696 l_plan_id NUMBER;
2697 l_char_id NUMBER;
2698 l_result_column_name VARCHAR2(30);
2700 -- Bug 5406294
2701 -- Modified the variable size from
2702 -- 30 to 150 as default values can be
2703 -- of size upto 150
2704 -- SHKALYAN 24-JUL-2006
2705 --
2706 -- l_default_value VARCHAR2(30);
2707 l_default_value VARCHAR2(150);
2708 l_enabled_flag NUMBER;
2709 l_mandatory_flag NUMBER;
2710 l_displayed_flag NUMBER;
2711 l_prompt VARCHAR2(30);
2712 l_prompt_sequence NUMBER;
2713 l_datatype NUMBER;
2714
2715 -- Tracking Bug : 3104827
2716 -- Added to include Three new Flags for Collection Plan Element
2717 -- saugupta Thu Aug 28 08:59:59 PDT 2003
2718 l_read_only_flag NUMBER;
2719 l_ss_poplist_flag NUMBER;
2720 l_information_flag NUMBER;
2721
2722 -- 12.1 Device Integration Project
2723 -- bhsankar Mon Nov 12 05:51:37 PST 2007
2724 l_device_flag NUMBER;
2725 l_override_flag NUMBER;
2726 x_device_id NUMBER;
2727 x_override_flag NUMBER;
2728
2729 BEGIN
2730
2731 -- Standard Start of API savepoint
2732
2733 SAVEPOINT add_element_pub;
2734
2735 -- Standard call to check for call compatibility.
2736 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
2737 l_api_name, g_pkg_name) THEN
2738 RAISE fnd_api.g_exc_unexpected_error;
2739 END IF;
2740
2741 -- Initialize message list if p_init_msg_list is set to TRUE.
2742 IF fnd_api.to_boolean(p_init_msg_list) THEN
2743 fnd_msg_pub.initialize;
2744 END IF;
2745
2746 -- Initialize API return status to success
2747 x_return_status := fnd_api.g_ret_sts_success;
2748
2749 -- *** start of logic ***
2750
2751 l_user_id := get_user_id(p_user_name);
2752 IF l_user_id = -1 THEN
2753 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
2754 fnd_msg_pub.add();
2755 raise fnd_api.g_exc_error;
2756 END IF;
2757
2758 l_plan_id := qa_plans_api.plan_id(upper(p_plan_name));
2759 IF (l_plan_id IS NULL) THEN
2760 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN');
2761 fnd_msg_pub.add();
2762 raise fnd_api.g_exc_error;
2763 END IF;
2764
2765 l_char_id := qa_chars_api.get_element_id(p_element_name);
2766 IF (l_char_id IS NULL) THEN
2767 fnd_message.set_name('QA', 'QA_API_INVALID_ELEMENT');
2768 fnd_msg_pub.add();
2769 raise fnd_api.g_exc_error;
2770 END IF;
2771
2772 IF element_exists(l_plan_id, l_char_id) THEN
2773 fnd_message.set_name('QA', 'QA_API_ELEMENT_ALREADY_ADDED');
2774 fnd_msg_pub.add();
2775 raise fnd_api.g_exc_error;
2776 END IF;
2777
2778 --
2779 -- Bug 3926150. Since add_plan_element can be called without
2780 -- first calling create_plan (to add elements to an existing plan,
2781 -- we will need to initialize the g_result_columns array here
2782 -- instead of just doing it once in create_plan. Thus we needed
2783 -- to cache the plan_id in the init function to avoid re-initializing
2784 -- every time.
2785 -- bso Fri Dec 3 21:42:13 PST 2004
2786 --
2787 init_result_column_array(l_plan_id);
2788
2789 l_enabled_flag := convert_flag(p_enabled_flag);
2790 l_displayed_flag := convert_flag(p_displayed_flag);
2791 IF p_mandatory_flag = g_inherit THEN
2792 l_mandatory_flag := qa_chars_api.mandatory_flag(l_char_id);
2793 ELSE
2794 l_mandatory_flag := convert_flag(p_mandatory_flag);
2795 END IF;
2796
2797 IF p_prompt IS NULL OR p_prompt = g_inherit THEN
2798 l_prompt := nvl(qa_chars_api.prompt(l_char_id), p_element_name);
2799 ELSE
2800 l_prompt := p_prompt;
2801 END IF;
2802
2803 IF p_prompt_sequence IS NULL THEN
2804 l_prompt_sequence := get_next_sequence(l_plan_id);
2805 ELSE
2806 IF prompt_sequence_exists(l_plan_id, p_prompt_sequence) THEN
2807 fnd_message.set_name('QA', 'QA_API_INVALID_PROMPT_SEQUENCE');
2808 fnd_msg_pub.add();
2809 RAISE fnd_api.g_exc_error;
2810 END IF;
2811 l_prompt_sequence := p_prompt_sequence;
2812 END IF;
2813
2814 IF p_default_value = g_inherit THEN
2815 l_default_value := qa_chars_api.default_value(l_char_id);
2816 ELSE
2817 l_default_value := p_default_value;
2818 END IF;
2819 l_datatype := qa_chars_api.datatype(l_char_id);
2820 validate_datatype(l_default_value, l_datatype);
2821
2822 IF p_result_column_name IS NULL THEN
2823 --
2824 -- Bug 3926150. Modify get_next_result_column_name function to
2825 -- use the new suggest_result_column function. bso
2826 --
2827
2828 --
2829 -- Strange PL/SQL oddity. nvl doesn't seem to use lazy evaluation.
2830 -- that is, suggest_result_column is being called even when
2834 -- l_result_column_name := nvl(qa_chars_api.hardcoded_column(l_char_id),
2831 -- hardcoded_column returns non-NULL. Need to switch to IF..THEN for
2832 -- optimal performance. bso Sat Dec 4 14:23:24 PST 2004
2833 --
2835 -- suggest_result_column(l_plan_id, l_char_id));
2836
2837 l_result_column_name := qa_chars_api.hardcoded_column(l_char_id);
2838 IF l_result_column_name IS NULL THEN
2839 l_result_column_name := suggest_result_column(l_plan_id, l_char_id);
2840 END IF;
2841
2842 IF l_result_column_name IS NULL THEN
2843 fnd_message.set_name('QA', 'QA_EXCEEDED_COLUMN_COUNT');
2844 fnd_msg_pub.add();
2845 raise fnd_api.g_exc_error;
2846 END IF;
2847 ELSE
2848 l_result_column_name := p_result_column_name;
2849 END IF;
2850
2851 --
2852 -- Bug 3926150.
2853 --
2854 mark_result_column(l_result_column_name);
2855
2856 -- added for read only flag
2857 l_read_only_flag := convert_flag(p_read_only_flag);
2858 l_ss_poplist_flag := convert_flag(p_ss_poplist_flag);
2859 l_information_flag := convert_flag(p_information_flag);
2860
2861 -- 12.1 Device Integration Project Start
2862 -- bhsankar Mon Nov 12 05:51:37 PST 2007
2863 l_device_flag := convert_flag(p_device_flag);
2864 l_override_flag := convert_flag(p_override_flag);
2865
2866 IF FND_PROFILE.VALUE('WIP_MES_OPS_FLAG') <> 1
2867 AND (p_device_flag IS NOT NULL AND p_override_flag IS NOT NULL OR p_device_name IS NOT NULL OR p_sensor_alias IS NOT NULL) THEN
2868 fnd_message.set_name('WIP', 'WIP_WS_NO_LICENSE');
2869 fnd_msg_pub.add();
2870 raise fnd_api.g_exc_error;
2871 END IF;
2872
2873 IF l_device_flag = 2 AND (l_override_flag = 1 OR p_device_name IS NOT NULL OR p_sensor_alias IS NOT NULL) THEN
2874 fnd_message.set_name('QA', 'QA_API_INVALID_DEVICE_FLAG');
2875 fnd_msg_pub.add();
2876 raise fnd_api.g_exc_error;
2877 END IF;
2878
2879 IF l_device_flag = 1 AND (p_device_name IS NULL OR p_sensor_alias IS NULL) THEN
2880 fnd_message.set_name('QA', 'QA_API_INVALID_DEVICE_NAME');
2881 fnd_msg_pub.add();
2882 raise fnd_api.g_exc_error;
2883 END IF;
2884
2885 IF l_device_flag = 1 AND p_device_name IS NOT NULL AND p_sensor_alias IS NOT NULL THEN
2886 get_device_details(trim(p_device_name), trim(p_sensor_alias), x_device_id, x_override_flag);
2887
2888 IF (x_device_id IS NULL) THEN
2889 fnd_message.set_name('QA', 'QA_API_INVALID_DEVICE_DETAILS');
2890 fnd_msg_pub.add();
2891 raise fnd_api.g_exc_error;
2892 END IF;
2893 END IF;
2894
2895 IF p_override_flag IS NULL THEN
2896 l_override_flag := x_override_flag;
2897 END IF;
2898 -- 12.1 Device Integration Project End.
2899
2900 INSERT INTO qa_plan_chars(
2901 last_update_date,
2902 last_updated_by,
2903 creation_date,
2904 created_by,
2905 last_update_login,
2906 plan_id,
2907 char_id,
2908 prompt_sequence,
2909 prompt,
2910 enabled_flag,
2911 mandatory_flag,
2912 default_value,
2913 displayed_flag,
2914 read_only_flag,
2915 ss_poplist_flag,
2916 information_flag,
2917 result_column_name,
2918 values_exist_flag,
2919 -- 12.1 Device Integration Project
2920 -- bhsankar Mon Nov 12 05:51:37 PST 2007
2921 device_flag,
2922 device_id,
2923 override_flag)
2924 VALUES(
2925 sysdate,
2926 l_user_id,
2927 sysdate,
2928 l_user_id,
2929 l_user_id,
2930 l_plan_id,
2931 l_char_id,
2932 l_prompt_sequence,
2933 l_prompt,
2934 l_enabled_flag,
2935 l_mandatory_flag,
2936 l_default_value,
2937 l_displayed_flag,
2938 l_read_only_flag,
2939 l_ss_poplist_flag,
2940 l_information_flag,
2941 l_result_column_name,
2942 2, -- values_exist_flag. defaulting a 2 to values flag
2943 -- until user calls add_value
2944 -- 12.1 Device Integration Project
2945 -- bhsankar Mon Nov 12 05:51:37 PST 2007
2946 nvl(l_device_flag, 2),
2947 x_device_id,
2948 nvl(l_override_flag, 2)
2949 );
2950 EXCEPTION
2951
2952 WHEN fnd_api.g_exc_error THEN
2953 ROLLBACK TO add_element_pub;
2954 x_return_status := fnd_api.g_ret_sts_error;
2955 fnd_msg_pub.count_and_get(
2956 p_count => x_msg_count,
2957 p_data => x_msg_data
2958 );
2959
2960 WHEN fnd_api.g_exc_unexpected_error THEN
2961 ROLLBACK TO add_element_pub;
2962 x_return_status := fnd_api.g_ret_sts_unexp_error;
2963 fnd_msg_pub.count_and_get(
2964 p_count => x_msg_count,
2965 p_data => x_msg_data
2966 );
2967
2968 WHEN OTHERS THEN
2969 ROLLBACK TO add_element_pub;
2970 x_return_status := fnd_api.g_ret_sts_unexp_error;
2971 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2975 p_count => x_msg_count,
2972 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2973 END IF;
2974 fnd_msg_pub.count_and_get(
2976 p_data => x_msg_data
2977 );
2978
2979 END add_plan_element;
2980
2981
2982 PROCEDURE complete_plan_processing(
2983 p_api_version IN NUMBER,
2984 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2985 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2986 p_user_name IN VARCHAR2 := NULL,
2987 p_plan_name IN VARCHAR2,
2988 p_organization_code IN VARCHAR2,
2989 p_commit IN VARCHAR2 := fnd_api.g_false,
2990 x_msg_count OUT NOCOPY NUMBER,
2991 x_msg_data OUT NOCOPY VARCHAR2,
2992 x_return_status OUT NOCOPY VARCHAR2) IS
2993
2994 l_api_name CONSTANT VARCHAR2(30) := 'complete_plan_definition';
2995 l_api_version CONSTANT NUMBER := 1.0;
2996
2997 l_user_id NUMBER;
2998 l_plan_id NUMBER;
2999 l_plan_name qa_plans.name%TYPE;
3000
3001 BEGIN
3002
3003 -- Standard Start of API savepoint
3004
3005 SAVEPOINT complete_plan_definition_pub;
3006
3007 -- Standard call to check for call compatibility.
3008 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
3009 l_api_name, g_pkg_name) THEN
3010 RAISE fnd_api.g_exc_unexpected_error;
3011 END IF;
3012
3013 -- Initialize message list if p_init_msg_list is set to TRUE.
3014 IF fnd_api.to_boolean(p_init_msg_list) THEN
3015 fnd_msg_pub.initialize;
3016 END IF;
3017
3018 -- Initialize API return status to success
3019 x_return_status := fnd_api.g_ret_sts_success;
3020
3021 -- *** start of logic ***
3022
3023 l_user_id := get_user_id(p_user_name);
3024 IF l_user_id = -1 THEN
3025 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
3026 fnd_msg_pub.add();
3027 raise fnd_api.g_exc_error;
3028 END IF;
3029
3030 l_plan_name := upper(p_plan_name);
3031 l_plan_id := qa_plans_api.plan_id(l_plan_name);
3032 IF (l_plan_id IS NULL) THEN
3033 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN');
3034 fnd_msg_pub.add();
3035 raise fnd_api.g_exc_error;
3036 END IF;
3037
3038 complete_plan_private(l_plan_id, l_plan_name, p_commit, l_user_id);
3039
3040 --
3041 -- Bug 3926150. Clean up the result column array.
3042 -- bso Fri Dec 3 21:54:48 PST 2004
3043 --
3044 init_result_column_array(-1);
3045
3046 fnd_msg_pub.count_and_get(
3047 p_count => x_msg_count,
3048 p_data => x_msg_data
3049 );
3050
3051 EXCEPTION
3052
3053 WHEN fnd_api.g_exc_error THEN
3054 ROLLBACK TO complete_plan_definition_pub;
3055 x_return_status := fnd_api.g_ret_sts_error;
3056 fnd_msg_pub.count_and_get(
3057 p_count => x_msg_count,
3058 p_data => x_msg_data
3059 );
3060
3061 WHEN fnd_api.g_exc_unexpected_error THEN
3062 ROLLBACK TO complete_plan_definition_pub;
3063 x_return_status := fnd_api.g_ret_sts_unexp_error;
3064 fnd_msg_pub.count_and_get(
3065 p_count => x_msg_count,
3066 p_data => x_msg_data
3067 );
3068
3069 WHEN OTHERS THEN
3070 ROLLBACK TO complete_plan_definition_pub;
3071 x_return_status := fnd_api.g_ret_sts_unexp_error;
3072 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3073 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3074 END IF;
3075 fnd_msg_pub.count_and_get(
3076 p_count => x_msg_count,
3077 p_data => x_msg_data
3078 );
3079
3080 END complete_plan_processing;
3081
3082
3083 PROCEDURE copy_collection_plan(
3084 p_api_version IN NUMBER,
3085 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3086 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3087 p_user_name IN VARCHAR2 := NULL,
3088 p_plan_name IN VARCHAR2,
3089 p_organization_code IN VARCHAR2,
3090 p_to_plan_name IN VARCHAR2,
3091 p_to_organization_code IN VARCHAR2,
3092 p_copy_actions_flag IN VARCHAR2 := fnd_api.g_true,
3093 p_copy_values_flag IN VARCHAR2 := fnd_api.g_true,
3094 p_copy_transactions_flag IN VARCHAR2 := fnd_api.g_true,
3095 p_commit IN VARCHAR2 := fnd_api.g_false,
3096 x_to_plan_id OUT NOCOPY NUMBER,
3097 x_msg_count OUT NOCOPY NUMBER,
3098 x_msg_data OUT NOCOPY VARCHAR2,
3099 x_return_status OUT NOCOPY VARCHAR2) IS
3100
3101 l_api_name CONSTANT VARCHAR2(30) := 'copy_plan';
3102 l_api_version CONSTANT NUMBER := 1.0;
3103
3104 l_user_id NUMBER;
3105 l_from_plan_id NUMBER;
3106 l_to_plan_name qa_plans.name%TYPE;
3107 l_to_org_id NUMBER;
3108
3109 BEGIN
3110
3111 -- Standard Start of API savepoint
3112
3116 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
3113 SAVEPOINT copy_plan_pub;
3114
3115 -- Standard call to check for call compatibility.
3117 l_api_name, g_pkg_name) THEN
3118 RAISE fnd_api.g_exc_unexpected_error;
3119 END IF;
3120
3121 -- Initialize message list if p_init_msg_list is set to TRUE.
3122 IF fnd_api.to_boolean(p_init_msg_list) THEN
3123 fnd_msg_pub.initialize;
3124 END IF;
3125
3126 -- Initialize API return status to success
3127 x_return_status := fnd_api.g_ret_sts_success;
3128
3129 -- *** start of logic ***
3130
3131 l_user_id := get_user_id(p_user_name);
3132 IF l_user_id = -1 THEN
3133 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
3134 fnd_msg_pub.add();
3135 raise fnd_api.g_exc_error;
3136 END IF;
3137
3138 --
3139 -- An unusual case for copy plan. The template plans have
3140 -- mixed case, but all regular plans have upper case. So,
3141 -- try them both.
3142 --
3143 l_from_plan_id := qa_plans_api.plan_id(p_plan_name);
3144 IF (l_from_plan_id IS NULL) THEN
3145 l_from_plan_id := qa_plans_api.plan_id(upper(p_plan_name));
3146 IF (l_from_plan_id IS NULL) THEN
3147 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN');
3148 fnd_msg_pub.add();
3149 raise fnd_api.g_exc_error;
3150 END IF;
3151 END IF;
3152
3153 l_to_plan_name := upper(p_to_plan_name);
3154 IF (illegal_chars(l_to_plan_name)) THEN
3155 fnd_message.set_name('QA', 'QA_NAME_SPECIAL_CHARS');
3156 fnd_msg_pub.add();
3157 raise fnd_api.g_exc_error;
3158 END IF;
3159
3160 l_to_org_id := qa_plans_api.get_org_id(p_to_organization_code);
3161 IF (l_to_org_id IS NULL) THEN
3162 fnd_message.set_name('QA', 'QA_API_INVALID_ORG_CODE');
3163 fnd_msg_pub.add();
3164 raise fnd_api.g_exc_error;
3165 END IF;
3166
3167 x_to_plan_id := copy_plan_header(l_from_plan_id, l_to_plan_name,
3168 l_to_org_id, l_user_id);
3169
3170 IF (p_copy_values_flag = fnd_api.g_true) THEN
3171 copy_plan_element_values(l_from_plan_id, x_to_plan_id,
3172 l_user_id);
3173 END IF;
3174
3175 IF (p_copy_actions_flag = fnd_api.g_true) THEN
3176 copy_plan_element_actions(l_from_plan_id, x_to_plan_id,
3177 l_user_id);
3178 END IF;
3179
3180 IF (p_copy_transactions_flag = fnd_api.g_true) THEN
3181 copy_plan_transactions(l_from_plan_id, x_to_plan_id,
3182 l_user_id);
3183 END IF;
3184
3185 --
3186 -- Bug 3926150. Initialize the result column array before copying
3187 -- the elements.
3188 -- bso Fri Dec 3 22:06:09 PST 2004
3189 --
3190 init_result_column_array(x_to_plan_id);
3191
3192 --
3193 -- Because of a special "where" clause in the above copy_plan...
3194 -- functions, the copy_plan_elements call must be put at this
3195 -- position, after all the above calls.
3196 --
3197 copy_plan_elements_bulk(l_from_plan_id, x_to_plan_id,
3198 p_copy_values_flag, l_user_id);
3199
3200 complete_plan_private(x_to_plan_id, l_to_plan_name, p_commit, l_user_id);
3201
3202 --
3203 -- Bug 3926150. Re-init the result column array afterwards.
3204 -- bso Fri Dec 3 22:06:09 PST 2004
3205 --
3206 init_result_column_array(-1);
3207
3208 fnd_msg_pub.count_and_get(
3209 p_count => x_msg_count,
3210 p_data => x_msg_data
3211 );
3212
3213 EXCEPTION
3214
3215 WHEN fnd_api.g_exc_error THEN
3216 ROLLBACK TO copy_plan_pub;
3217 x_return_status := fnd_api.g_ret_sts_error;
3218 fnd_msg_pub.count_and_get(
3219 p_count => x_msg_count,
3220 p_data => x_msg_data
3221 );
3222
3223 WHEN fnd_api.g_exc_unexpected_error THEN
3224 ROLLBACK TO copy_plan_pub;
3225 x_return_status := fnd_api.g_ret_sts_unexp_error;
3226 fnd_msg_pub.count_and_get(
3227 p_count => x_msg_count,
3228 p_data => x_msg_data
3229 );
3230
3231 WHEN OTHERS THEN
3232 ROLLBACK TO copy_plan_pub;
3233 x_return_status := fnd_api.g_ret_sts_unexp_error;
3234 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3235 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3236 END IF;
3237 fnd_msg_pub.count_and_get(
3238 p_count => x_msg_count,
3239 p_data => x_msg_data
3240 );
3241
3242 END copy_collection_plan;
3243
3244
3245 PROCEDURE delete_plan_element(
3246 p_api_version IN NUMBER,
3247 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3248 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3249 p_user_name IN VARCHAR2 := NULL,
3250 p_plan_name IN VARCHAR2,
3251 p_organization_code IN VARCHAR2,
3252 p_element_name IN VARCHAR2,
3253 p_commit IN VARCHAR2 := fnd_api.g_false,
3257
3254 x_msg_count OUT NOCOPY NUMBER,
3255 x_msg_data OUT NOCOPY VARCHAR2,
3256 x_return_status OUT NOCOPY VARCHAR2) IS
3258 l_api_name CONSTANT VARCHAR2(30) := 'delete_plan_element';
3259 l_api_version CONSTANT NUMBER := 1.0;
3260
3261 l_user_id NUMBER;
3262 l_plan_id NUMBER;
3263 l_element_id NUMBER;
3264 l_org_id NUMBER;
3265 l_plan_name qa_plans.name%TYPE;
3266
3267
3268 BEGIN
3269
3270 -- Standard Start of API savepoint
3271
3272 SAVEPOINT delete_plan_element_pub;
3273
3274 -- Standard call to check for call compatibility.
3275 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
3276 l_api_name, g_pkg_name) THEN
3277 RAISE fnd_api.g_exc_unexpected_error;
3278 END IF;
3279
3280 -- Initialize message list if p_init_msg_list is set to TRUE.
3281 IF fnd_api.to_boolean(p_init_msg_list) THEN
3282 fnd_msg_pub.initialize;
3283 END IF;
3284
3285 -- Initialize API return status to success
3286 x_return_status := fnd_api.g_ret_sts_success;
3287
3288 -- *** start of logic ***
3289
3290 l_user_id := get_user_id(p_user_name);
3291 IF l_user_id = -1 THEN
3292 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
3293 fnd_msg_pub.add();
3294 raise fnd_api.g_exc_error;
3295 END IF;
3296
3297 l_plan_name := upper(p_plan_name);
3298 l_plan_id := qa_plans_api.plan_id(l_plan_name);
3299 IF (l_plan_id IS NULL) THEN
3300 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN');
3301 fnd_msg_pub.add();
3302 RAISE fnd_api.g_exc_error;
3303 END IF;
3304
3305 l_element_id := qa_chars_api.get_element_id(p_element_name);
3306 IF (l_element_id IS NULL) THEN
3307 fnd_message.set_name('QA', 'QA_API_INVALID_ELEMENT');
3308 fnd_msg_pub.add();
3309 RAISE fnd_api.g_exc_error;
3310 END IF;
3311
3312 IF NOT element_exists(l_plan_id, l_element_id) THEN
3313 fnd_message.set_name('QA', 'QA_API_ELEMENT_NOT_IN_PLAN');
3314 fnd_msg_pub.add();
3315 RAISE fnd_api.g_exc_error;
3316 END IF;
3317
3318 IF results_exist(l_plan_id, l_element_id) THEN
3319 fnd_message.set_name('QA', 'QA_RESULTS_EXIST_FOR_PLANCHAR');
3320 fnd_msg_pub.add();
3321 RAISE fnd_api.g_exc_error;
3322 END IF;
3323
3324 delete_plan_element_values(l_plan_id, l_element_id);
3325 delete_plan_element_actions(l_plan_id, l_element_id);
3326 delete_plan_element(l_plan_id, l_element_id);
3327
3328 complete_plan_private(l_plan_id, l_plan_name, p_commit, l_user_id);
3329
3330 fnd_msg_pub.count_and_get(
3331 p_count => x_msg_count,
3332 p_data => x_msg_data
3333 );
3334
3335 EXCEPTION
3336
3337 WHEN fnd_api.g_exc_error THEN
3338 ROLLBACK TO delete_plan_element_pub;
3339 x_return_status := fnd_api.g_ret_sts_error;
3340 fnd_msg_pub.count_and_get(
3341 p_count => x_msg_count,
3342 p_data => x_msg_data
3343 );
3344
3345 WHEN fnd_api.g_exc_unexpected_error THEN
3346 ROLLBACK TO delete_plan_element_pub;
3347 x_return_status := fnd_api.g_ret_sts_unexp_error;
3348 fnd_msg_pub.count_and_get(
3349 p_count => x_msg_count,
3350 p_data => x_msg_data
3351 );
3352
3353 WHEN OTHERS THEN
3354 ROLLBACK TO delete_plan_element_pub;
3355 x_return_status := fnd_api.g_ret_sts_unexp_error;
3356 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3357 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3358 END IF;
3359 fnd_msg_pub.count_and_get(
3360 p_count => x_msg_count,
3361 p_data => x_msg_data
3362 );
3363
3364 END delete_plan_element;
3365
3366
3367 PROCEDURE delete_collection_plan(
3368 p_api_version IN NUMBER,
3369 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3370 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3371 p_user_name IN VARCHAR2 := NULL,
3372 p_plan_name IN VARCHAR2,
3373 p_organization_code IN VARCHAR2,
3374 p_commit IN VARCHAR2 := fnd_api.g_false,
3375 x_msg_count OUT NOCOPY NUMBER,
3376 x_msg_data OUT NOCOPY VARCHAR2,
3377 x_return_status OUT NOCOPY VARCHAR2) IS
3378
3379 l_api_name CONSTANT VARCHAR2(30) := 'delete_plan';
3380 l_api_version CONSTANT NUMBER := 1.0;
3381
3382 l_user_id NUMBER;
3383 l_plan_id NUMBER;
3384 l_org_id NUMBER;
3385 l_plan_name qa_plans.name%TYPE;
3386
3387 BEGIN
3388
3389 -- Standard Start of API savepoint
3390
3391 SAVEPOINT delete_plan_pub;
3392
3396 RAISE fnd_api.g_exc_unexpected_error;
3393 -- Standard call to check for call compatibility.
3394 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
3395 l_api_name, g_pkg_name) THEN
3397 END IF;
3398
3399 -- Initialize message list if p_init_msg_list is set to TRUE.
3400 IF fnd_api.to_boolean(p_init_msg_list) THEN
3401 fnd_msg_pub.initialize;
3402 END IF;
3403
3404 -- Initialize API return status to success
3405 x_return_status := fnd_api.g_ret_sts_success;
3406
3407
3408 -- *** start of logic ***
3409
3410 l_user_id := get_user_id(p_user_name);
3411 IF l_user_id = -1 THEN
3412 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
3413 fnd_msg_pub.add();
3414 raise fnd_api.g_exc_error;
3415 END IF;
3416
3417 l_plan_name := upper(p_plan_name);
3418 l_plan_id := qa_plans_api.plan_id(l_plan_name);
3419 IF l_plan_id IS NULL THEN
3420 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN');
3421 fnd_msg_pub.add();
3422 raise fnd_api.g_exc_error;
3423 END IF;
3424
3425 IF results_exist(l_plan_id) THEN
3426 fnd_message.set_name('QA', 'QA_CANT_DELETE_QPLAN');
3427 fnd_msg_pub.add();
3428 RAISE fnd_api.g_exc_error;
3429 END IF;
3430
3431 delete_plan_elements(l_plan_id);
3432 delete_plan_values(l_plan_id);
3433 delete_plan_transactions(l_plan_id);
3434 delete_plan_actions(l_plan_id);
3435 delete_plan_header(l_plan_id);
3436 delete_plan_private(l_plan_name, p_commit, l_user_id);
3437
3438 fnd_msg_pub.count_and_get(
3439 p_count => x_msg_count,
3440 p_data => x_msg_data
3441 );
3442
3443 EXCEPTION
3444
3445 WHEN fnd_api.g_exc_error THEN
3446 ROLLBACK TO delete_plan_pub;
3447 x_return_status := fnd_api.g_ret_sts_error;
3448 fnd_msg_pub.count_and_get(
3449 p_count => x_msg_count,
3450 p_data => x_msg_data
3451 );
3452
3453 WHEN fnd_api.g_exc_unexpected_error THEN
3454 ROLLBACK TO delete_plan_pub;
3455 x_return_status := fnd_api.g_ret_sts_unexp_error;
3456 fnd_msg_pub.count_and_get(
3457 p_count => x_msg_count,
3458 p_data => x_msg_data
3459 );
3460
3461 WHEN OTHERS THEN
3462 ROLLBACK TO delete_plan_pub;
3463 x_return_status := fnd_api.g_ret_sts_unexp_error;
3464 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3465 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3466 END IF;
3467 fnd_msg_pub.count_and_get(
3468 p_count => x_msg_count,
3469 p_data => x_msg_data
3470 );
3471
3472 END delete_collection_plan;
3473
3474 FUNCTION get_plan_type (p_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
3475
3476 CURSOR c IS
3477 SELECT meaning
3478 FROM fnd_lookup_values
3479 WHERE lookup_type = 'COLLECTION_PLAN_TYPE'
3480 AND lookup_code = p_lookup_code;
3481
3482 ret_val VARCHAR2(80);
3483 BEGIN
3484
3485 OPEN c;
3486 FETCH c INTO ret_val;
3487 IF c%NOTFOUND THEN
3488 ret_val := '';
3489 END IF;
3490
3491 CLOSE c;
3492 RETURN ret_val;
3493
3494 END get_plan_type;
3495
3496 END qa_plans_pub;
3497
[Home] [Help]