1 PACKAGE BODY qa_plans_pub AS
2 /* $Header: qltpplnb.plb 120.9.12010000.2 2010/04/30 10:16:02 ntungare 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;
382 RETURN p;
383
384 END get_next_sequence;
385
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
531 return 'Q_' || translate(substr(p_name, 1, 25), ' ''', '__') || '_IV';
532 END get_import_view_name;
533
534
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:
645 -- - op seq (to/from) requires a wip entity ID (JOB NAME)
646 -- or LINE
647 -- - WIP production line requires an ITEM
648 -- - intraop step (to/from) needs OP SEQ
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
784 fnd_message.set_name ('QA', 'QA_DEPENDENT_COMP_LOT_ITEM');
785 fnd_msg_pub.add();
786 RAISE fnd_api.g_exc_error;
787 END IF;
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
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));
945 fnd_message.set_token('ELEMENT_LIST',
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 --
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);
1083 l_import_view := get_import_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 --
1101 -- bug 9562325
1102 -- Added new parameters to set the DFF attributes
1103 -- on the plan header level
1104 --
1105 INSERT INTO qa_plans(
1106 plan_id,
1107 organization_id,
1108 last_update_date,
1109 last_updated_by,
1110 creation_date,
1111 created_by,
1112 last_update_login,
1113 name,
1114 plan_type_code,
1115 spec_assignment_type,
1116 description,
1117 import_view_name,
1118 view_name,
1119 effective_from,
1120 effective_to,
1121 template_plan_id,
1122 esig_mode,
1123 instructions,
1124 multirow_flag,
1125 attribute_category,
1126 attribute1,
1127 attribute2,
1128 attribute3,
1129 attribute4,
1130 attribute5,
1131 attribute6,
1132 attribute7,
1133 attribute8,
1134 attribute9,
1135 attribute10,
1136 attribute11,
1137 attribute12,
1138 attribute13,
1139 attribute14,
1140 attribute15)
1141 SELECT
1142 l_to_plan_id,
1143 p_to_org_id,
1144 sysdate,
1145 p_user_id,
1146 sysdate,
1147 p_user_id,
1148 p_user_id,
1149 p_to_plan_name,
1150 plan_type_code,
1151 spec_assignment_type,
1152 description,
1153 l_import_view,
1154 l_plan_view,
1155 effective_from,
1156 effective_to,
1157 template_plan_id,
1158 esig_mode,
1159 instructions,
1160 multirow_flag,
1161 attribute_category,
1162 attribute1,
1163 attribute2,
1164 attribute3,
1165 attribute4,
1166 attribute5,
1167 attribute6,
1168 attribute7,
1169 attribute8,
1170 attribute9,
1171 attribute10,
1172 attribute11,
1173 attribute12,
1174 attribute13,
1175 attribute14,
1176 attribute15
1177 FROM qa_plans
1178 WHERE plan_id = p_from_plan_id;
1179
1180 END IF;
1181
1182 RETURN l_to_plan_id;
1183
1184 END copy_plan_header;
1185
1186
1187 --
1188 -- Bug 3926150. Commenting out copy_plan_elements because it
1189 -- is obsolete by copy_plan_elements_bulk. We don't want to
1190 -- dual maintain two codelines.
1191 -- bso Fri Dec 3 21:59:44 PST 2004
1192 --
1193 /*
1194 PROCEDURE copy_plan_elements(
1195 p_copy_from_plan_id IN NUMBER,
1196 p_copy_to_plan_id IN NUMBER,
1197 p_copy_values_flag IN VARCHAR2,
1198 p_user_id IN NUMBER) IS
1199
1200 --
1201 -- This cursor retrieves all plan elements from the source
1202 -- plan except those that already occur in the target plan
1203 -- (which could be an existing plan).
1204 --
1205 -- Explain plan shows NOT IN performance is OK.
1206 --
1207
1208 -- Tracking Bug : 3104827
1209 -- Modifying to include Three new Flags for Read Only Collection Plan Elements
1210 -- saugupta Thu Aug 28 08:59:59 PDT 2003
1211
1212 -- Bug 3726391. shkalyan 28 June 2004
1213 -- Added missing columns viz.
1214 -- decimal_precision,uom_code,attribute_category,attribute1 to attribute15
1215
1216 -- Bug 3726391. shkalyan 30 June 2004
1217 -- Removed insert of attribute_category,attribute1 to attribute15
1218 -- As per code review comments
1219
1220 CURSOR c IS
1221 SELECT
1222 char_id,
1223 prompt_sequence,
1224 prompt,
1225 enabled_flag,
1226 mandatory_flag,
1227 default_value,
1228 upper(result_column_name) result_column_name,
1229 values_exist_flag,
1230 displayed_flag,
1231 default_value_id,
1232 read_only_flag,
1233 ss_poplist_flag,
1234 information_flag,
1235 decimal_precision,
1236 uom_code
1237 FROM
1238 qa_plan_chars
1239 WHERE
1240 plan_id = p_copy_from_plan_id AND char_id NOT IN
1241 (SELECT char_id
1242 FROM qa_plan_chars
1243 WHERE plan_id = p_copy_to_plan_id)
1244 ORDER BY prompt_sequence;
1245
1246 l_prompt_sequence NUMBER;
1247 l_char_sequence NUMBER;
1248 l_char_column_name VARCHAR2(30);
1249 l_result_column qa_plan_chars.result_column_name%TYPE;
1250
1251 BEGIN
1252
1253 l_prompt_sequence := get_next_sequence(p_copy_to_plan_id);
1254 l_char_column_name := get_next_result_column_name(p_copy_to_plan_id);
1255 IF l_char_column_name IS NULL THEN
1256 --
1257 -- This will guarantee it prints exceed column message later.
1258 --
1259 l_char_sequence := g_max_char_columns + 1;
1260 ELSE
1261 l_char_sequence := to_number(substr(l_char_column_name, 10));
1262 END IF;
1263
1264 --
1265 -- For each record in cursor c, insert into the target plan
1266 -- with the proper prompt_sequence and result_column_name.
1267 --
1268
1269 FOR pc IN c LOOP
1270
1271 IF pc.result_column_name LIKE 'CHARACTER%' THEN
1272 IF l_char_sequence > g_max_char_columns THEN
1273 fnd_message.set_name('QA', 'QA_EXCEEDED_COLUMN_COUNT');
1274 fnd_msg_pub.add();
1275 raise fnd_api.g_exc_error;
1276 END IF;
1277 l_result_column := 'CHARACTER' || l_char_sequence;
1278 l_char_sequence := l_char_sequence + 1;
1279 ELSE
1280 l_result_column := pc.result_column_name;
1281 END IF;
1282
1283 -- Tracking Bug : 3104827
1284 -- Modifying to include Three new Flags for Collection Plan Element
1285 -- saugupta Thu Aug 28 08:59:59 PDT 2003
1286
1287 -- Bug 3726391. shkalyan 28 June 2004
1288 -- Added insert of missing columns viz.
1289 -- decimal_precision,uom_code,attribute_category,
1290 -- attribute1 to attribute15
1291
1292 -- Bug 3726391. shkalyan 30 June 2004
1293 -- Removed insert of attribute_category,attribute1 to attribute15
1294 -- As per code review comments
1295
1296 INSERT INTO qa_plan_chars(
1297 plan_id,
1298 char_id,
1299 last_update_date,
1300 last_updated_by,
1301 creation_date,
1302 created_by,
1303 last_update_login,
1304 prompt_sequence,
1305 prompt,
1306 enabled_flag,
1307 mandatory_flag,
1308 default_value,
1309 result_column_name,
1310 values_exist_flag,
1311 displayed_flag,
1312 default_value_id,
1313 read_only_flag,
1314 ss_poplist_flag,
1315 information_flag,
1316 decimal_precision,
1317 uom_code)
1318 VALUES (
1319 p_copy_to_plan_id,
1320 pc.char_id,
1321 sysdate,
1322 p_user_id,
1323 sysdate,
1324 p_user_id,
1325 p_user_id,
1326 l_prompt_sequence,
1327 pc.prompt,
1328 pc.enabled_flag,
1329 pc.mandatory_flag,
1330 pc.default_value,
1331 l_result_column,
1332 decode(p_copy_values_flag,
1333 fnd_api.g_true, pc.values_exist_flag, 2),
1334 pc.displayed_flag,
1335 pc.default_value_id,
1336 pc.read_only_flag,
1337 pc.ss_poplist_flag,
1338 pc.information_flag,
1339 pc.decimal_precision,
1340 pc.uom_code
1341 );
1342
1343 l_prompt_sequence := l_prompt_sequence + 10;
1344 END LOOP;
1345
1346 END copy_plan_elements;
1347 */
1348
1349
1350 --
1351 -- This version of copy_plan_elements does the same as the
1352 -- above procedure, but uses 8i Bulk bind feature to improve
1353 -- performance.
1354 --
1355 PROCEDURE copy_plan_elements_bulk(
1356 p_copy_from_plan_id IN NUMBER,
1357 p_copy_to_plan_id IN NUMBER,
1358 p_copy_values_flag IN VARCHAR2,
1359 p_user_id IN NUMBER) IS
1360
1361 TYPE prompt_tab IS TABLE OF qa_plan_chars.prompt%TYPE
1362 INDEX BY BINARY_INTEGER;
1363
1364 TYPE default_tab IS TABLE OF qa_plan_chars.default_value%TYPE
1365 INDEX BY BINARY_INTEGER;
1366
1367 TYPE result_tab IS TABLE OF qa_plan_chars.result_column_name%TYPE
1368 INDEX BY BINARY_INTEGER;
1369
1370 char_ids number_tab;
1371 prompt_sequences number_tab;
1372 prompts prompt_tab;
1373 enabled_flags number_tab;
1374 mandatory_flags number_tab;
1375 default_values default_tab;
1376 result_column_names result_tab;
1377 values_exist_flags number_tab;
1378 displayed_flags number_tab;
1379 default_value_ids number_tab;
1380
1381 l_prompt_sequence NUMBER;
1382 l_char_sequence NUMBER;
1383 l_char_column_name VARCHAR2(30);
1384
1385 -- Tracking Bug : 3104827
1386 -- Added to include Three new Flags for Collection Plan Element
1387 -- saugupta Thu Aug 28 08:59:59 PDT 2003
1388 read_only_flags number_tab;
1389 ss_poplist_flags number_tab;
1390 information_flags number_tab;
1391
1392 -- Bug 3726391. shkalyan 28 June 2004
1393 -- Added declaration of missing columns viz.
1394 -- decimal_precision,uom_code,attribute_category,attribute1 to attribute15
1395
1396 -- Bug 3726391. shkalyan 30 June 2004
1397 -- Removed insert of attribute_category,attribute1 to attribute15
1398 -- As per code review comments
1399
1400 TYPE uom_code_tab IS TABLE OF qa_plan_chars.uom_code%TYPE
1401 INDEX BY BINARY_INTEGER;
1402
1403 decimal_precisions number_tab;
1404 uom_codes uom_code_tab;
1405
1406 -- Tracking Bug : 6734330 Device Integration Project
1407 -- Included three columns device_flag,
1408 -- device_id and override_flag for Collection Plan Elements
1409 -- bhsankar Mon Jan 7 22:00:17 PST 2008
1410 device_flags number_tab;
1411 device_ids number_tab;
1412 override_flags number_tab;
1413
1414 --
1415 -- bug 9562325
1416 -- Added new parameters to set the DFF attributes
1417 -- on the plan element level
1418 --
1419 TYPE attr_cat_tab IS TABLE OF qa_plan_chars.attribute_category%TYPE
1420 INDEX BY BINARY_INTEGER;
1421
1422 TYPE attr_tab IS TABLE OF qa_plan_chars.attribute1%TYPE;
1423
1424 attribute_categories attr_cat_tab;
1425 attribute1s attr_tab;
1426 attribute2s attr_tab;
1427 attribute3s attr_tab;
1428 attribute4s attr_tab;
1429 attribute5s attr_tab;
1430 attribute6s attr_tab;
1431 attribute7s attr_tab;
1432 attribute8s attr_tab;
1433 attribute9s attr_tab;
1434 attribute10s attr_tab;
1435 attribute11s attr_tab;
1436 attribute12s attr_tab;
1437 attribute13s attr_tab;
1438 attribute14s attr_tab;
1439 attribute15s attr_tab;
1440 BEGIN
1441
1442 --
1443 -- This cursor retrieves all plan elements from the source
1444 -- plan except those that already occur in the target plan
1445 -- (which could be an existing plan).
1446 --
1447 -- Explain plan shows NOT IN performance is OK.
1448 --
1449
1450 -- Bug 3726391. shkalyan 28 June 2004
1451 -- Added select of missing columns viz.
1452 -- decimal_precision,uom_code,attribute_category,attribute1 to attribute15
1453
1454 -- Bug 3726391. shkalyan 30 June 2004
1455 -- Removed insert of attribute_category,attribute1 to attribute15
1456 -- As per code review comments
1457
1458 -- Bug 6734330
1459 -- Device Integration Project
1460 -- Added device columns device_id,
1461 -- device_flag and override_flags.
1462 -- bhsankar Mon Jan 7 22:00:17 PST 2008
1463 SELECT
1464 char_id,
1465 prompt_sequence,
1466 prompt,
1467 enabled_flag,
1468 mandatory_flag,
1469 default_value,
1470 upper(result_column_name) result_column_name,
1471 values_exist_flag,
1472 displayed_flag,
1473 default_value_id,
1474 read_only_flag,
1475 ss_poplist_flag,
1476 information_flag,
1477 decimal_precision,
1478 uom_code,
1479 device_flag,
1480 device_id,
1481 override_flag,
1482 attribute_category,
1483 attribute1,
1484 attribute2,
1485 attribute3,
1486 attribute4,
1487 attribute5,
1488 attribute6,
1489 attribute7,
1490 attribute8,
1491 attribute9,
1492 attribute10,
1493 attribute11,
1494 attribute12,
1495 attribute13,
1496 attribute14,
1497 attribute15
1498 BULK COLLECT INTO
1499 char_ids,
1500 prompt_sequences,
1501 prompts,
1502 enabled_flags,
1503 mandatory_flags,
1504 default_values,
1505 result_column_names,
1506 values_exist_flags,
1507 displayed_flags,
1508 default_value_ids,
1509 read_only_flags,
1510 ss_poplist_flags,
1511 information_flags,
1512 decimal_precisions,
1513 uom_codes,
1514 device_flags,
1515 device_ids,
1516 override_flags,
1517 attribute_categories,
1518 attribute1s,
1519 attribute2s,
1520 attribute3s,
1521 attribute4s,
1522 attribute5s,
1523 attribute6s,
1524 attribute7s,
1525 attribute8s,
1526 attribute9s,
1527 attribute10s,
1528 attribute11s,
1529 attribute12s,
1530 attribute13s,
1531 attribute14s,
1532 attribute15s
1533 FROM
1534 qa_plan_chars
1535 WHERE
1536 plan_id = p_copy_from_plan_id AND char_id NOT IN
1537 (SELECT char_id
1538 FROM qa_plan_chars
1539 WHERE plan_id = p_copy_to_plan_id)
1540 ORDER BY prompt_sequence;
1541
1542 IF char_ids.COUNT = 0 THEN
1543 --
1544 -- This is needed in case the target plan is an existing
1545 -- plan that already contains all elements in the source.
1546 --
1547 RETURN;
1548 END IF;
1549
1550 l_prompt_sequence := get_next_sequence(p_copy_to_plan_id);
1551
1552 --
1553 -- Bug 3926150. This should be done inside the loop for this
1554 -- fix. In fact it was a bug to just increment by 1 in the
1555 -- original logic.
1556 -- bso Fri Dec 3 22:10:27 PST 2004
1557 --
1558 -- l_char_column_name := get_next_result_column_name(p_copy_to_plan_id);
1559 -- IF l_char_column_name IS NULL THEN
1560 -- --
1561 -- -- This will guarantee it prints exceed column message later.
1562 -- --
1563 -- l_char_sequence := g_max_char_columns + 1;
1564 -- ELSE
1565 -- l_char_sequence := to_number(substr(l_char_column_name, 10));
1566 -- END IF;
1567 --
1568
1569 --
1570 -- For each plan element to be copied, adjust the
1571 -- prompt_sequence and result_column_name.
1572 --
1573
1574 FOR i IN char_ids.FIRST .. char_ids.LAST LOOP
1575 IF result_column_names(i) LIKE 'CHARACTER%' THEN
1576 --
1577 -- Bug 3926150.
1578 -- Change the result column assignment code to use the new
1579 -- suggest_result_column function.
1580 -- bso Fri Dec 3 22:30:23 PST 2004
1581 --
1582 result_column_names(i) := suggest_result_column(p_copy_to_plan_id,
1583 char_ids(i));
1584 IF result_column_names(i) IS NULL THEN
1585 fnd_message.set_name('QA', 'QA_EXCEEDED_COLUMN_COUNT');
1586 fnd_msg_pub.add();
1587 raise fnd_api.g_exc_error;
1588 END IF;
1589 mark_result_column(result_column_names(i));
1590 END IF;
1591
1592 prompt_sequences(i) := l_prompt_sequence;
1593 l_prompt_sequence := l_prompt_sequence + 10;
1594 END LOOP;
1595
1596 --
1597 -- Clear the values_exist_flags if values are not copied.
1598 --
1599 IF p_copy_values_flag = fnd_api.g_false THEN
1600 FOR i IN char_ids.FIRST .. char_ids.LAST LOOP
1601 values_exist_flags(i) := 2;
1602 END LOOP;
1603 END IF;
1604
1605 -- Bug 3726391. shkalyan 28 June 2004
1606 -- Added insert of missing columns viz.
1607 -- decimal_precision,uom_code,attribute_category,attribute1 to attribute15
1608
1609 -- Bug 3726391. shkalyan 30 June 2004
1610 -- Removed insert of attribute_category,attribute1 to attribute15
1611 -- As per code review comments
1612
1613 -- Bug 6734330
1614 -- Device Integration Project
1615 -- Added device columns device_id,
1616 -- device_flag and override_flags.
1617 -- bhsankar Mon Jan 7 22:00:17 PST 2008
1618 --
1619 -- bug 9562325
1620 -- Added new parameters to set the DFF attributes
1621 -- on the plan element level
1622 --
1623 FORALL i IN char_ids.FIRST .. char_ids.LAST
1624 INSERT INTO qa_plan_chars(
1625 plan_id,
1626 char_id,
1627 last_update_date,
1628 last_updated_by,
1629 creation_date,
1630 created_by,
1631 last_update_login,
1632 prompt_sequence,
1633 prompt,
1634 enabled_flag,
1635 mandatory_flag,
1636 default_value,
1637 result_column_name,
1638 values_exist_flag,
1639 displayed_flag,
1640 default_value_id,
1641 read_only_flag,
1642 ss_poplist_flag,
1643 information_flag,
1644 decimal_precision,
1645 uom_code,
1646 device_flag,
1647 device_id,
1648 override_flag,
1649 attribute_category,
1650 attribute1,
1651 attribute2,
1652 attribute3,
1653 attribute4,
1654 attribute5,
1655 attribute6,
1656 attribute7,
1657 attribute8,
1658 attribute9,
1659 attribute10,
1660 attribute11,
1661 attribute12,
1662 attribute13,
1663 attribute14,
1664 attribute15)
1665 VALUES (
1666 p_copy_to_plan_id,
1667 char_ids(i),
1668 sysdate,
1669 p_user_id,
1670 sysdate,
1671 p_user_id,
1672 p_user_id,
1673 prompt_sequences(i),
1674 prompts(i),
1675 enabled_flags(i),
1676 mandatory_flags(i),
1677 default_values(i),
1678 result_column_names(i),
1679 values_exist_flags(i),
1680 displayed_flags(i),
1681 default_value_ids(i),
1682 read_only_flags(i),
1683 ss_poplist_flags(i),
1684 information_flags(i),
1685 decimal_precisions(i),
1686 uom_codes(i),
1687 device_flags(i),
1688 device_ids(i),
1689 override_flags(i),
1690 attribute_categories(i),
1691 attribute1s(i),
1692 attribute2s(i),
1693 attribute3s(i),
1694 attribute4s(i),
1695 attribute5s(i),
1696 attribute6s(i),
1697 attribute7s(i),
1698 attribute8s(i),
1699 attribute9s(i),
1700 attribute10s(i),
1701 attribute11s(i),
1702 attribute12s(i),
1703 attribute13s(i),
1704 attribute14s(i),
1705 attribute15s(i)
1706 );
1707
1708 END copy_plan_elements_bulk;
1709
1710
1711 PROCEDURE copy_plan_element_values(p_copy_from_plan_id IN NUMBER,
1712 p_copy_to_plan_id IN NUMBER, p_user_id IN NUMBER) IS
1713
1714 BEGIN
1715
1716 --
1717 -- Explain plan shows NOT IN performance is OK.
1718 --
1719 INSERT INTO qa_plan_char_value_lookups(
1720 plan_id,
1721 char_id,
1722 short_code,
1723 description,
1724 last_update_date,
1725 last_updated_by,
1726 last_update_login,
1727 creation_date,
1728 created_by,
1729 short_code_id)
1730 SELECT
1731 p_copy_to_plan_id,
1732 char_id,
1733 short_code,
1734 description,
1735 sysdate,
1736 p_user_id,
1737 p_user_id,
1738 sysdate,
1739 created_by,
1740 short_code_id
1741 FROM qa_plan_char_value_lookups
1742 WHERE plan_id = p_copy_from_plan_id AND char_id NOT IN
1743 (SELECT char_id
1744 FROM qa_plan_chars
1745 WHERE plan_id = p_copy_to_plan_id);
1746
1747 END copy_plan_element_values;
1748
1749
1750 PROCEDURE copy_plan_element_actions(p_copy_from_plan_id IN NUMBER,
1751 p_copy_to_plan_id IN NUMBER, p_user_id IN NUMBER) IS
1752
1753 CURSOR action_trigger_cursor is
1754 SELECT
1755 plan_char_action_trigger_id,
1756 trigger_sequence,
1757 plan_id,
1758 char_id,
1759 operator,
1760 low_value_lookup,
1761 high_value_lookup,
1762 low_value_other,
1763 high_value_other,
1764 low_value_other_id,
1765 high_value_other_id
1766 FROM qa_plan_char_action_triggers
1767 WHERE plan_id = p_copy_from_plan_id AND char_id NOT IN
1768 (SELECT char_id
1769 FROM qa_plan_chars
1770 WHERE plan_id = p_copy_to_plan_id)
1771 ORDER BY trigger_sequence;
1772
1773 CURSOR action_cursor(x NUMBER) IS
1774 SELECT
1775 plan_char_action_id,
1776 plan_char_action_trigger_id,
1777 action_id,
1778 car_name_prefix,
1779 car_type_id,
1780 car_owner,
1781 message,
1782 status_code,
1783 alr_action_id,
1784 alr_action_set_id,
1785 assigned_char_id,
1786 assign_type
1787 FROM qa_plan_char_actions
1788 WHERE plan_char_action_trigger_id = x
1789 ORDER BY plan_char_action_id;
1790
1791 -- Bug 3111310
1792 -- Modified the cursor for SQL performance fix
1793 -- saugupta Mon Sep 8 06:00:06 PDT 2003
1794
1795 CURSOR alert_cursor(x NUMBER) is
1796 SELECT
1797 application_id,
1798 action_id,
1799 name,
1800 alert_id,
1801 action_type,
1802 end_date_active,
1803 enabled_flag,
1804 description,
1805 action_level_type,
1806 date_last_executed,
1807 file_name,
1808 argument_string,
1809 program_application_id,
1810 concurrent_program_id,
1811 list_application_id,
1812 list_id,
1813 to_recipients,
1814 cc_recipients,
1815 bcc_recipients,
1816 print_recipients,
1817 printer,
1818 subject,
1819 reply_to,
1820 response_set_id,
1821 follow_up_after_days,
1822 column_wrap_flag,
1823 maximum_summary_message_width,
1824 body,
1825 version_number
1826 FROM alr_actions
1827 WHERE action_id = x
1828 AND application_id = 250;
1829
1830 alra alert_cursor%ROWTYPE;
1831
1832 l_qpcat_id NUMBER;
1833 l_qpca_id NUMBER;
1834
1835 l_action_set_seq NUMBER;
1836 l_action_set_members_seq NUMBER;
1837 l_action_name_seq NUMBER;
1838 l_action_set_name_seq NUMBER;
1839
1840 new_action_id NUMBER;
1841 new_action_set_id NUMBER;
1842 new_action_set_member_id NUMBER;
1843 new_action_name alr_actions.name%TYPE;
1844 new_action_set_name alr_action_sets.name%TYPE;
1845
1846
1847 BEGIN
1848
1849 FOR qpcat IN action_trigger_cursor LOOP
1850
1851 SELECT qa_plan_char_action_triggers_s.nextval
1852 INTO l_qpcat_id
1853 FROM dual;
1854
1855 INSERT INTO qa_plan_char_action_triggers (
1856 plan_char_action_trigger_id,
1857 last_update_date,
1858 last_updated_by,
1859 creation_date,
1860 created_by,
1861 last_update_login,
1862 trigger_sequence,
1863 plan_id,
1864 char_id,
1865 operator,
1866 low_value_lookup,
1867 high_value_lookup,
1868 low_value_other,
1869 high_value_other,
1870 low_value_other_id,
1871 high_value_other_id)
1872 VALUES (
1873 l_qpcat_id,
1874 sysdate,
1875 p_user_id,
1876 sysdate,
1877 p_user_id,
1878 p_user_id,
1879 qpcat.trigger_sequence,
1880 p_copy_to_plan_id,
1881 qpcat.char_id,
1882 qpcat.operator,
1883 qpcat.low_value_lookup,
1884 qpcat.high_value_lookup,
1885 qpcat.low_value_other,
1886 qpcat.high_value_other,
1887 qpcat.low_value_other_id,
1888 qpcat.high_value_other_id);
1889
1890 -- Bug 5300577
1891 -- Included this condition to get the translated value of
1892 -- ACCEPT and REJECT so that Action for the inspection Result
1893 -- element fires accurately.
1894 -- Included Template OPM Recieving inspection plan because
1895 -- conversion is required for these plans as well.
1896
1897 IF p_copy_from_plan_id IN (1,2147483637) AND
1898 qpcat.low_value_other IN ('ACCEPT', 'REJECT') THEN
1899
1900 UPDATE QA_PLAN_CHAR_ACTION_TRIGGERS
1901 SET low_value_other = (SELECT displayed_field
1902 FROM PO_LOOKUP_CODES
1903 WHERE lookup_type = 'ERT RESULTS ACTION'
1904 AND lookup_code = qpcat.low_value_other)
1905 WHERE plan_char_action_trigger_id = l_qpcat_id;
1906 END IF;
1907
1908 FOR qpca IN action_cursor(qpcat.plan_char_action_trigger_id) LOOP
1909
1910 SELECT qa_plan_char_actions_s.nextval
1911 INTO l_qpca_id
1912 FROM dual;
1913
1914 --
1915 -- These are alert actions, generate new alert action IDs
1916 --
1917 IF qpca.action_id IN (10, 11, 12, 13) AND
1918 qpca.alr_action_id IS NOT NULL THEN
1919
1920 SELECT
1921 alr_actions_s.nextval,
1922 alr_action_sets_s.nextval,
1923 alr_action_set_members_s.nextval,
1924 qa_alr_action_name_s.nextval,
1925 qa_alr_action_set_name_s.nextval
1926 INTO
1927 new_action_id,
1928 new_action_set_id,
1929 new_action_set_member_id,
1930 l_action_name_seq,
1931 l_action_set_name_seq
1932 FROM dual;
1933
1934 --
1935 -- Some action details are stored in Oracle Alert tables
1936 -- with alert ID 10177. Copy the header and recreate new
1937 -- alert actions for the new plan.
1938 --
1939 OPEN alert_cursor(qpca.alr_action_id);
1940 FETCH alert_cursor INTO alra;
1941 IF alert_cursor%FOUND THEN
1942
1943 new_action_name := 'qa_' || l_action_name_seq;
1944 new_action_set_name := 'qa_' || l_action_set_name_seq;
1945
1946 INSERT INTO alr_actions (
1947 application_id,
1948 action_id,
1949 name,
1950 alert_id,
1951 action_type,
1952 last_update_date,
1953 last_updated_by,
1954 creation_date,
1955 created_by,
1956 last_update_login,
1957 end_date_active,
1958 enabled_flag,
1959 description,
1960 action_level_type,
1961 date_last_executed,
1962 file_name,
1963 argument_string,
1964 program_application_id,
1965 concurrent_program_id,
1966 list_application_id,
1967 list_id,
1968 to_recipients,
1969 cc_recipients,
1970 bcc_recipients,
1971 print_recipients,
1972 printer,
1973 subject,
1974 reply_to,
1975 response_set_id,
1976 follow_up_after_days,
1977 column_wrap_flag,
1978 maximum_summary_message_width,
1979 body,
1980 version_number)
1981 VALUES (
1982 alra.application_id,
1983 new_action_id,
1984 new_action_name,
1985 alra.alert_id,
1986 alra.action_type,
1987 sysdate,
1988 p_user_id,
1989 sysdate,
1990 p_user_id,
1991 p_user_id,
1992 alra.end_date_active,
1993 alra.enabled_flag,
1994 alra.description,
1995 alra.action_level_type,
1996 alra.date_last_executed,
1997 alra.file_name,
1998 alra.argument_string,
1999 alra.program_application_id,
2000 alra.concurrent_program_id,
2001 alra.list_application_id,
2002 alra.list_id,
2003 alra.to_recipients,
2004 alra.cc_recipients,
2005 alra.bcc_recipients,
2006 alra.print_recipients,
2007 alra.printer,
2008 alra.subject,
2009 alra.reply_to,
2010 alra.response_set_id,
2011 alra.follow_up_after_days,
2012 alra.column_wrap_flag,
2013 alra.maximum_summary_message_width,
2014 alra.body,
2015 alra.version_number
2016 );
2017
2018 BEGIN
2019 SELECT nvl(max(sequence),0) + 1
2020 INTO l_action_set_seq
2021 FROM alr_action_sets
2022 WHERE application_id = 250 AND alert_id = 10177;
2023
2024 EXCEPTION
2025 WHEN no_data_found THEN
2026 l_action_set_seq := 1;
2027 END;
2028
2029 INSERT INTO alr_action_sets (
2030 application_id,
2031 action_set_id,
2032 name,
2033 alert_id,
2034 last_update_date,
2035 last_updated_by,
2036 creation_date,
2037 created_by,
2038 last_update_login,
2039 end_date_active,
2040 enabled_flag,
2041 recipients_view_only_flag,
2042 description,
2043 suppress_flag,
2044 suppress_days,
2045 sequence)
2046 VALUES (
2047 250,
2048 new_action_set_id,
2049 new_action_set_name,
2050 10177,
2051 sysdate,
2052 p_user_id,
2053 sysdate,
2054 p_user_id,
2055 p_user_id,
2056 null,
2057 'Y',
2058 'N',
2059 new_action_set_name,
2060 'N',
2061 null,
2062 l_action_set_seq);
2063
2064 BEGIN
2065 SELECT nvl(max(sequence),0) + 1
2066 INTO l_action_set_members_seq
2067 FROM alr_action_set_members
2068 WHERE application_id = 250 AND
2069 alert_id = 10177 AND
2070 action_set_id = new_action_set_id;
2071 EXCEPTION
2072 WHEN no_data_found THEN
2073 l_action_set_members_seq := 1;
2074 END;
2075
2076 INSERT INTO alr_action_set_members (
2077 application_id,
2078 action_set_member_id,
2079 action_set_id,
2080 action_id,
2081 action_group_id,
2082 alert_id,
2083 sequence,
2084 last_update_date,
2085 last_updated_by,
2086 creation_date,
2087 created_by,
2088 last_update_login,
2089 end_date_active,
2090 enabled_flag,
2091 summary_threshold,
2092 abort_flag,
2093 error_action_sequence)
2094 VALUES (
2095 250,
2096 new_action_set_member_id,
2097 new_action_set_id,
2098 new_action_id,
2099 null,
2100 10177,
2101 l_action_set_members_seq,
2102 sysdate,
2103 p_user_id,
2104 sysdate,
2105 p_user_id,
2106 p_user_id,
2107 null,
2108 'Y',
2109 null,
2110 'A',
2111 null
2112 );
2113
2114 END IF; -- alert_cursor%FOUND (this is an alert action)
2115
2116 CLOSE alert_cursor;
2117
2118 END IF; -- the action id is 10, 11, 12, 13 (alert actions)
2119
2120 INSERT INTO qa_plan_char_actions (
2121 plan_char_action_id,
2122 last_update_date,
2123 last_updated_by,
2124 creation_date,
2125 created_by,
2126 last_update_login,
2127 plan_char_action_trigger_id,
2128 action_id,
2129 car_name_prefix,
2130 car_type_id,
2131 car_owner,
2132 message,
2133 status_code,
2134 alr_action_id,
2135 alr_action_set_id,
2136 assigned_char_id,
2137 assign_type)
2138 VALUES (
2139 l_qpca_id,
2140 sysdate,
2141 p_user_id,
2142 sysdate,
2143 p_user_id,
2144 p_user_id,
2145 l_qpcat_id,
2146 qpca.action_id,
2147 qpca.car_name_prefix,
2148 qpca.car_type_id,
2149 qpca.car_owner,
2150 qpca.message,
2151 qpca.status_code,
2152 decode(qpca.action_id,
2153 10, new_action_id,
2154 11, new_action_id,
2155 12, new_action_id,
2156 13, new_action_id,
2157 qpca.action_id),
2158 decode(qpca.action_id,
2159 10, new_action_set_id,
2160 11, new_action_set_id,
2161 12, new_action_set_id,
2162 13, new_action_set_id,
2163 qpca.action_id),
2164 qpca.assigned_char_id,
2165 qpca.assign_type);
2166
2167 INSERT INTO qa_plan_char_action_outputs (
2168 plan_char_action_id,
2169 char_id,
2170 last_update_date,
2171 last_updated_by,
2172 creation_date,
2173 created_by,
2174 last_update_login,
2175 token_name)
2176 SELECT
2177 l_qpca_id,
2178 char_id,
2179 sysdate,
2180 p_user_id,
2181 sysdate,
2182 p_user_id,
2183 p_user_id,
2184 token_name
2185 FROM qa_plan_char_action_outputs
2186 WHERE plan_char_action_id = qpca.plan_char_action_id;
2187
2188 END LOOP; -- action_cursor
2189
2190 END LOOP; -- action_trigger_cursor
2191
2192 END copy_plan_element_actions;
2193
2194
2195 PROCEDURE copy_plan_transactions(p_copy_from_plan_id IN NUMBER,
2196 p_copy_to_plan_id IN NUMBER, p_user_id IN NUMBER) IS
2197
2198 CURSOR txn_cursor IS
2199 SELECT
2200 plan_transaction_id,
2201 last_update_date,
2202 last_updated_by,
2203 creation_date,
2204 created_by,
2205 last_update_login,
2206 plan_id,
2207 transaction_number,
2208 mandatory_collection_flag,
2209 background_collection_flag,
2210 enabled_flag
2211 FROM qa_plan_transactions
2212 WHERE plan_id = p_copy_from_plan_id;
2213
2214 l_plan_transaction_id NUMBER;
2215
2216 CURSOR txn_trigger_cursor(x NUMBER) IS
2217 SELECT
2218 txn_trigger_id,
2219 last_update_date,
2220 last_updated_by,
2221 creation_date,
2222 created_by,
2223 last_update_login,
2224 plan_transaction_id,
2225 collection_trigger_id,
2226 operator,
2227 low_value,
2228 low_value_id,
2229 high_value,
2230 high_value_id
2231 FROM qa_plan_collection_triggers
2232 WHERE plan_transaction_id = x;
2233
2234 BEGIN
2235
2236 FOR qpt IN txn_cursor LOOP
2237
2238 SELECT qa_plan_transactions_s.nextval
2239 INTO l_plan_transaction_id
2240 FROM dual;
2241
2242 INSERT INTO qa_plan_transactions (
2243 plan_transaction_id,
2244 last_update_date,
2245 last_updated_by,
2246 creation_date,
2247 created_by,
2248 last_update_login,
2249 plan_id,
2250 transaction_number,
2251 mandatory_collection_flag,
2252 background_collection_flag,
2253 enabled_flag)
2254 VALUES (
2255 l_plan_transaction_id,
2256 sysdate,
2257 p_user_id,
2258 sysdate,
2259 p_user_id,
2260 p_user_id,
2261 p_copy_to_plan_id,
2262 qpt.transaction_number,
2263 qpt.mandatory_collection_flag,
2264 qpt.background_collection_flag,
2265 qpt.enabled_flag);
2266
2267 FOR qpct IN txn_trigger_cursor(qpt.plan_transaction_id) LOOP
2268
2269 INSERT INTO qa_plan_collection_triggers (
2270 txn_trigger_id,
2271 last_update_date,
2272 last_updated_by,
2273 creation_date,
2274 created_by,
2275 last_update_login,
2276 plan_transaction_id,
2277 collection_trigger_id,
2278 operator,
2279 low_value,
2280 low_value_id,
2281 high_value,
2282 high_value_id)
2283 VALUES (
2284 qa_txn_trigger_ids_s.nextval,
2285 sysdate,
2286 p_user_id,
2287 sysdate,
2288 p_user_id,
2289 p_user_id,
2290 l_plan_transaction_id,
2291 qpct.collection_trigger_id,
2292 qpct.operator,
2293 qpct.low_value,
2294 qpct.low_value_id,
2295 qpct.high_value,
2296 qpct.high_value_id);
2297
2298 END LOOP; -- transaction triggers
2299
2300 END LOOP; -- transaction
2301
2302 END copy_plan_transactions;
2303
2304
2305 --
2306 -- Private functions for plan and plan element deletions.
2307 --
2308
2309 FUNCTION results_exist(p_plan_Id IN NUMBER, p_element_id IN NUMBER)
2310 RETURN BOOLEAN IS
2311
2312 TYPE ref_cursor IS REF CURSOR;
2313 c ref_cursor;
2314
2315 l_dummy NUMBER;
2316 l_found BOOLEAN;
2317 l_result_column_name qa_plan_chars.result_column_name%TYPE;
2318 l_sql_statement VARCHAR2(200);
2319
2320 BEGIN
2321
2322 l_result_column_name := qa_plan_element_api.get_result_column_name(
2323 p_plan_id, p_element_id);
2324
2325 IF l_result_column_name IS NULL THEN
2326 RETURN true;
2327 END IF;
2328
2329 l_sql_statement :=
2330 'SELECT 1 FROM qa_results WHERE plan_id = :id AND rownum = 1 AND ' ||
2331 l_result_column_name || ' IS NOT NULL';
2332
2333 OPEN c FOR l_sql_statement USING p_plan_id;
2334 FETCH c INTO l_dummy;
2335 l_found := c%FOUND;
2336 CLOSE c;
2337
2338 RETURN l_found;
2339
2340 END results_exist;
2341
2342
2343 FUNCTION results_exist(p_plan_id IN NUMBER) RETURN BOOLEAN IS
2344
2345 TYPE numType IS REF CURSOR;
2346
2347 CURSOR c IS
2348 SELECT 1
2349 FROM qa_results
2350 WHERE plan_id = p_plan_id AND rownum = 1;
2351
2352 l_dummy NUMBER;
2353 l_found BOOLEAN;
2354
2355 BEGIN
2356
2357 OPEN c;
2358 FETCH c INTO l_dummy;
2359 l_found := c%FOUND;
2360 CLOSE c;
2361
2362 RETURN l_found;
2363
2364 END results_exist;
2365
2366
2367 PROCEDURE delete_plan_element_actions(p_plan_id IN NUMBER,
2368 p_element_id IN NUMBER) IS
2369
2370 pca_ids number_tab;
2371 pcat_ids number_tab;
2372
2373 BEGIN
2374
2375 DELETE FROM qa_plan_char_action_triggers
2376 WHERE plan_id = p_plan_id AND char_id = p_element_id
2377 RETURNING plan_char_action_trigger_id BULK COLLECT INTO pcat_ids;
2378
2379 IF pcat_ids.COUNT = 0 THEN
2380 RETURN;
2381 END IF;
2382
2383 FORALL i IN pcat_ids.FIRST .. pcat_ids.LAST
2384 DELETE FROM qa_plan_char_actions
2385 WHERE plan_char_action_trigger_id = pcat_ids(i)
2386 RETURNING plan_char_action_id BULK COLLECT INTO pca_ids;
2387
2388 IF pca_ids.COUNT = 0 THEN
2389 RETURN;
2390 END IF;
2391
2392 FORALL i IN pca_ids.FIRST .. pca_ids.LAST
2393 DELETE FROM qa_plan_char_action_outputs
2394 WHERE plan_char_action_id = pca_ids(i);
2395 --
2396 -- ### Do we need to delete the alert records?
2397 --
2398
2399 END delete_plan_element_actions;
2400
2401
2402 PROCEDURE delete_plan_element_values(p_plan_id IN NUMBER,
2403 p_element_id IN NUMBER) IS
2404 BEGIN
2405 DELETE FROM qa_plan_char_value_lookups
2406 WHERE plan_id = p_plan_id AND char_id = p_element_id;
2407 END delete_plan_element_values;
2408
2409
2410 PROCEDURE delete_plan_element(p_plan_id IN NUMBER, p_element_id IN NUMBER) IS
2411 l_result_column qa_plan_chars.result_column_name%TYPE;
2412 BEGIN
2413 DELETE FROM qa_plan_chars
2414 WHERE plan_id = p_plan_id AND char_id = p_element_id
2415 RETURNING result_column_name
2416 INTO l_result_column; -- needed for Bug 3926150
2417
2418 --
2419 -- Bug 3926150. Check if the deleted element will disrupt a
2420 -- function-based index. If so, add info message to the msg stack.
2421 -- bso Sat Dec 4 16:08:07 PST 2004
2422 --
2423 IF l_result_column LIKE 'CHARACTER%' AND l_result_column <>
2424 qa_char_indexes_pkg.get_default_result_column(p_element_id) THEN
2425 disable_index_private(p_element_id);
2426 END IF;
2427
2428 END delete_plan_element;
2429
2430
2431 PROCEDURE delete_plan_elements(p_plan_id IN NUMBER) IS
2432 --
2433 -- Bug 3926150. Need to warn user if function-based index
2434 -- is disrupted due to this action.
2435 --
2436 CURSOR c IS
2437 SELECT qpc.char_id
2438 FROM qa_plan_chars qpc, qa_char_indexes qci
2439 WHERE qpc.plan_id = p_plan_id AND
2440 qpc.char_id = qci.char_id AND
2441 qpc.result_column_name <> qci.default_result_column;
2442 BEGIN
2443 --
2444 -- Bug 3926150. Minor revamp of this procedure from a simple
2445 -- delete of all plan_chars to a disable index and delete.
2446 -- bso Sun Dec 5 11:54:53 PST 2004
2447 --
2448 FOR r IN c LOOP
2449 disable_index_private(r.char_id);
2450 END LOOP;
2451
2452 DELETE FROM qa_plan_chars
2453 WHERE plan_id = p_plan_id;
2454
2455 END delete_plan_elements;
2456
2457
2458 PROCEDURE delete_plan_values(p_plan_id IN NUMBER) IS
2459 BEGIN
2460 DELETE FROM qa_plan_char_value_lookups
2461 WHERE plan_id = p_plan_id;
2462 END delete_plan_values;
2463
2464
2465 PROCEDURE delete_plan_transactions(p_plan_id IN NUMBER) IS
2466
2467 pt_ids number_tab;
2468
2469 BEGIN
2470
2471 DELETE FROM qa_plan_transactions
2472 WHERE plan_id = p_plan_id
2473 RETURNING plan_transaction_id BULK COLLECT INTO pt_ids;
2474
2475 IF pt_ids.COUNT = 0 THEN
2476 RETURN;
2477 END IF;
2478
2479 FORALL i IN pt_ids.FIRST .. pt_ids.LAST
2480 DELETE FROM qa_plan_collection_triggers
2481 WHERE plan_transaction_id = pt_ids(i);
2482
2483 END delete_plan_transactions;
2484
2485
2486 PROCEDURE delete_plan_actions(p_plan_id IN NUMBER) IS
2487
2488 pcat_ids number_tab;
2489 pca_ids number_tab;
2490
2491 BEGIN
2492
2493 --
2494 -- Delete all triggers, collecting their primary keys
2495 --
2496 DELETE FROM qa_plan_char_action_triggers
2497 WHERE plan_id = p_plan_id
2498 RETURNING plan_char_action_trigger_id BULK COLLECT INTO pcat_ids;
2499
2500 IF pcat_ids.COUNT = 0 THEN
2501 RETURN;
2502 END IF;
2503
2504 --
2505 -- Now delete all children actions
2506 --
2507 FORALL i IN pcat_ids.FIRST .. pcat_ids.LAST
2508 DELETE FROM qa_plan_char_actions
2509 WHERE plan_char_action_trigger_id = pcat_ids(i)
2510 RETURNING plan_char_action_id BULK COLLECT INTO pca_ids;
2511
2512 IF pca_ids.COUNT = 0 THEN
2513 RETURN;
2514 END IF;
2515
2516 --
2517 -- Some actions have action outputs... delete them.
2518 --
2519 FORALL i IN pca_ids.FIRST .. pca_ids.LAST
2520 DELETE FROM qa_plan_char_action_outputs
2521 WHERE plan_char_action_id = pca_ids(i);
2522
2523 --
2524 -- ### Do we need to delete the alert records?
2525 --
2526 END delete_plan_actions;
2527
2528
2529 PROCEDURE delete_plan_header(p_plan_id IN NUMBER) IS
2530
2531 BEGIN
2532
2533 DELETE FROM qa_plans
2534 WHERE plan_id = p_plan_id;
2535
2536 END delete_plan_header;
2537
2538
2539 --
2540 -- This procedure is called to commit a deleted plan.
2541 --
2542 PROCEDURE delete_plan_private(
2543 p_plan_name VARCHAR2,
2544 p_commit VARCHAR2,
2545 p_user_id NUMBER) IS
2546
2547 l_request_id NUMBER;
2548 BEGIN
2549 IF fnd_api.to_boolean(p_commit) THEN
2550 COMMIT;
2551
2552 --
2553 -- The dynamic view creator can be used to delete the
2554 -- redundant plan view once a plan is deleted.
2555 --
2556 -- Launch the dynamic view creator only if the user
2557 -- commits; otherwise, the view generator will fail
2558 -- because it is run in another database session.
2559 --
2560 -- The init is required to get the concurrent program
2561 -- to run. The resp_id 20561 is the seeded main Quality
2562 -- responsibility. 250 is Oracle Quality's application ID.
2563 --
2564 fnd_global.apps_initialize(
2565 user_id => p_user_id,
2566 resp_id => 20561,
2567 resp_appl_id => 250);
2568
2569 l_request_id := fnd_request.submit_request(
2570 application => 'QA',
2571 program => 'QLTPVWWB',
2572 argument1 => NULL,
2573 argument2 => get_plan_view_name(p_plan_name),
2574 argument3 => NULL,
2575 argument4 => NULL,
2576 argument5 => get_import_view_name(p_plan_name),
2577 argument6 => 'QA_GLOBAL_RESULTS_V');
2578
2579 COMMIT;
2580 END IF;
2581 END delete_plan_private;
2582
2583 -- 12.1 Device Integration Project
2584 -- Procedure to get the device_id, override_flag
2585 -- for the device_name, sensor_alias combination
2586 -- bhsankar Mon Nov 12 05:51:37 PST 2007
2587 PROCEDURE get_device_details (p_device_name IN VARCHAR2,
2588 p_sensor_alias IN VARCHAR2,
2589 x_device_id OUT NOCOPY NUMBER,
2590 x_override_flag OUT NOCOPY NUMBER) IS
2591
2592 CURSOR c IS
2593 SELECT device_id, override_flag
2594 FROM qa_device_info
2595 WHERE device_name = p_device_name
2596 AND sensor_alias = p_sensor_alias
2597 AND enabled_flag = 1;
2598
2599 BEGIN
2600
2601 OPEN c;
2602 FETCH c INTO x_device_id, x_override_flag;
2603 CLOSE c;
2604
2605 END get_device_details;
2606
2607 --
2608 --
2609 --
2610 -- Start of public API functions
2611 --
2612 --
2613 -- 12.1 QWB USability Improvements
2614 -- Added the parameter P_multirow_flag
2615 -- ntungare
2616 --
2617 --
2618 -- bug 9562325
2619 -- Added new parameters to set the DFF attributes
2620 -- on the plan header level
2621 --
2622 PROCEDURE create_collection_plan(
2623 p_api_version IN NUMBER,
2624 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2625 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2626 p_user_name IN VARCHAR2 := NULL,
2627 p_plan_name IN VARCHAR2,
2628 p_organization_code IN VARCHAR2,
2629 p_plan_type IN VARCHAR2,
2630 p_description IN VARCHAR2 := NULL,
2631 p_effective_from IN DATE := sysdate,
2632 p_effective_to IN DATE := NULL,
2633 p_spec_assignment_type IN NUMBER := qa_plans_pub.g_spec_type_none,
2634 p_multirow_flag IN NUMBER := 2,
2635 x_plan_id OUT NOCOPY NUMBER,
2636 x_msg_count OUT NOCOPY NUMBER,
2637 x_msg_data OUT NOCOPY VARCHAR2,
2638 x_return_status OUT NOCOPY VARCHAR2,
2639 p_attribute_category IN VARCHAR2 := NULL,
2640 p_attribute1 IN VARCHAR2 := NULL,
2641 p_attribute2 IN VARCHAR2 := NULL,
2642 p_attribute3 IN VARCHAR2 := NULL,
2643 p_attribute4 IN VARCHAR2 := NULL,
2644 p_attribute5 IN VARCHAR2 := NULL,
2645 p_attribute6 IN VARCHAR2 := NULL,
2646 p_attribute7 IN VARCHAR2 := NULL,
2647 p_attribute8 IN VARCHAR2 := NULL,
2648 p_attribute9 IN VARCHAR2 := NULL,
2649 p_attribute10 IN VARCHAR2 := NULL,
2650 p_attribute11 IN VARCHAR2 := NULL,
2651 p_attribute12 IN VARCHAR2 := NULL,
2652 p_attribute13 IN VARCHAR2 := NULL,
2653 p_attribute14 IN VARCHAR2 := NULL,
2654 p_attribute15 IN VARCHAR2 := NULL) IS
2655
2656 l_api_name CONSTANT VARCHAR2(30) := 'create_plan';
2657 l_api_version CONSTANT NUMBER := 1.0;
2658
2659 l_org_id NUMBER;
2660 l_user_id NUMBER;
2661 l_plan_type_code VARCHAR2(30);
2662 l_plan_name qa_plans.name%TYPE;
2663 l_plan_view qa_plans.view_name%TYPE;
2664 l_import_view qa_plans.import_view_name%TYPE;
2665
2666 BEGIN
2667
2668 -- Standard Start of API savepoint
2669
2670 SAVEPOINT create_plan_pub;
2671
2672 -- Standard call to check for call compatibility.
2673 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
2674 l_api_name, g_pkg_name) THEN
2675 RAISE fnd_api.g_exc_unexpected_error;
2676 END IF;
2677
2678 -- Initialize message list if p_init_msg_list is set to TRUE.
2679 IF fnd_api.to_boolean(p_init_msg_list) THEN
2680 fnd_msg_pub.initialize;
2681 END IF;
2682
2683 -- Initialize API return status to success
2684 x_return_status := fnd_api.g_ret_sts_success;
2685
2686
2687 -- *** start of logic ***
2688
2689 --
2690 -- Bug 3926150. init the result column array. -1 indicates
2691 -- a brand new plan is being created.
2692 -- bso Fri Dec 3 20:55:05 PST 2004
2693 --
2694 init_result_column_array(-1);
2695
2696 l_user_id := get_user_id(p_user_name);
2697 IF l_user_id = -1 THEN
2698 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
2699 fnd_msg_pub.add();
2700 raise fnd_api.g_exc_error;
2701 END IF;
2702
2703 l_plan_name := upper(p_plan_name);
2704 IF (illegal_chars(l_plan_name)) THEN
2705 fnd_message.set_name('QA', 'QA_NAME_SPECIAL_CHARS');
2706 fnd_msg_pub.add();
2707 raise fnd_api.g_exc_error;
2708 END IF;
2709
2710 l_org_id := qa_plans_api.get_org_id(p_organization_code);
2711 IF (l_org_id IS NULL) THEN
2712 fnd_message.set_name('QA', 'QA_API_INVALID_ORG_CODE');
2713 fnd_msg_pub.add();
2714 raise fnd_api.g_exc_error;
2715 END IF;
2716
2717 IF NOT valid_plan_type(p_plan_type) THEN
2718 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN_TYPE');
2719 fnd_msg_pub.add();
2720 raise fnd_api.g_exc_error;
2721 ELSE
2722 l_plan_type_code := get_plan_type_code(p_plan_type);
2723 END IF;
2724
2725
2726 -- If the name passed as the plan name already exists then
2727 -- generate an error.
2728
2729 IF plan_exists(l_plan_name) > 0 THEN
2730 fnd_message.set_name('QA', 'QA_PLAN_RECORD_EXISTS');
2731 fnd_msg_pub.add();
2732 raise fnd_api.g_exc_error;
2733 END IF;
2734
2735 IF (p_effective_to < p_effective_from) THEN
2736 fnd_message.set_name('QA', 'QA_EFFECTIVE_DATE_RANGE');
2737 fnd_msg_pub.add();
2738 raise fnd_api.g_exc_error;
2739 END IF;
2740
2741 l_plan_view := get_plan_view_name(l_plan_name);
2742 l_import_view := get_import_view_name(l_plan_name);
2743
2744 SELECT qa_plans_s.nextval INTO x_plan_id FROM dual;
2745
2746 --
2747 -- bug 9562325
2748 -- Added new parameters to set the DFF attributes
2749 -- on the plan header level
2750 --
2751 INSERT INTO qa_plans(
2752 plan_id,
2753 organization_id,
2754 last_update_date,
2755 last_updated_by,
2756 creation_date,
2757 created_by,
2758 last_update_login,
2759 name,
2760 plan_type_code,
2761 spec_assignment_type,
2762 description,
2763 import_view_name,
2764 view_name,
2765 effective_from,
2766 effective_to,
2767 multirow_flag,
2768 attribute_category,
2769 attribute1,
2770 attribute2,
2771 attribute3,
2772 attribute4,
2773 attribute5,
2774 attribute6,
2775 attribute7,
2776 attribute8,
2777 attribute9,
2778 attribute10,
2779 attribute11,
2780 attribute12,
2781 attribute13,
2782 attribute14,
2783 attribute15)
2784 VALUES(
2785 x_plan_id,
2786 l_org_id,
2787 sysdate,
2788 l_user_id,
2789 sysdate,
2790 l_user_id,
2791 l_user_id,
2792 l_plan_name,
2793 l_plan_type_code,
2794 p_spec_assignment_type,
2795 p_description,
2796 l_import_view,
2797 l_plan_view,
2798 p_effective_from,
2799 p_effective_to,
2800 p_multirow_flag,
2801 p_attribute_category ,
2802 p_attribute1,
2803 p_attribute2,
2804 p_attribute3,
2805 p_attribute4,
2806 p_attribute5,
2807 p_attribute6,
2808 p_attribute7,
2809 p_attribute8,
2810 p_attribute9,
2811 p_attribute10,
2812 p_attribute11,
2813 p_attribute12,
2814 p_attribute13,
2815 p_attribute14,
2816 p_attribute15);
2817
2818 EXCEPTION
2819
2820 WHEN fnd_api.g_exc_error THEN
2821 ROLLBACK TO create_plan_pub;
2822 x_return_status := fnd_api.g_ret_sts_error;
2823 fnd_msg_pub.count_and_get(
2824 p_count => x_msg_count,
2825 p_data => x_msg_data
2826 );
2827
2828 WHEN fnd_api.g_exc_unexpected_error THEN
2829 ROLLBACK TO create_plan_pub;
2830 x_return_status := fnd_api.g_ret_sts_unexp_error;
2831 fnd_msg_pub.count_and_get(
2832 p_count => x_msg_count,
2833 p_data => x_msg_data
2834 );
2835
2836 WHEN OTHERS THEN
2837 ROLLBACK TO create_plan_pub;
2838 x_return_status := fnd_api.g_ret_sts_unexp_error;
2839 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2840 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2841 END IF;
2842 fnd_msg_pub.count_and_get(
2843 p_count => x_msg_count,
2844 p_data => x_msg_data
2845 );
2846
2847 END create_collection_plan;
2848
2849 --
2850 -- bug 9562325
2851 -- Added new parameters to set the DFF attributes
2852 -- on the plan element level
2853 --
2854 PROCEDURE add_plan_element(
2855 p_api_version IN NUMBER,
2856 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2857 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2858 p_user_name IN VARCHAR2 := NULL,
2859 p_plan_name IN VARCHAR2,
2860 p_organization_code IN VARCHAR2,
2861 p_element_name IN VARCHAR2,
2862 p_prompt_sequence IN NUMBER := NULL,
2863 p_prompt IN VARCHAR2 := g_inherit,
2864 p_default_value IN VARCHAR2 := g_inherit,
2865 p_enabled_flag IN VARCHAR2 := fnd_api.g_true,
2866 p_mandatory_flag IN VARCHAR2 := g_inherit,
2867 p_displayed_flag IN VARCHAR2 := fnd_api.g_true,
2868 p_read_only_flag IN VARCHAR2 := NULL,
2869 p_ss_poplist_flag IN VARCHAR2 := NULL,
2870 p_information_flag IN VARCHAR2 := NULL,
2871 p_result_column_name IN VARCHAR2 := NULL,
2872 -- 12.1 Device Integration Project
2873 -- bhsankar Mon Nov 12 05:51:37 PST 2007
2874 p_device_flag IN VARCHAR2 := NULL,
2875 p_device_name IN VARCHAR2 := NULL,
2876 p_sensor_alias IN VARCHAR2 := NULL,
2877 p_override_flag IN VARCHAR2 := NULL,
2878 -- 12.1 Device Integration Project End.
2879 x_msg_count OUT NOCOPY NUMBER,
2880 x_msg_data OUT NOCOPY VARCHAR2,
2881 x_return_status OUT NOCOPY VARCHAR2 ,
2882 p_attribute_category IN VARCHAR2 := NULL,
2883 p_attribute1 IN VARCHAR2 := NULL,
2884 p_attribute2 IN VARCHAR2 := NULL,
2885 p_attribute3 IN VARCHAR2 := NULL,
2886 p_attribute4 IN VARCHAR2 := NULL,
2887 p_attribute5 IN VARCHAR2 := NULL,
2888 p_attribute6 IN VARCHAR2 := NULL,
2889 p_attribute7 IN VARCHAR2 := NULL,
2890 p_attribute8 IN VARCHAR2 := NULL,
2891 p_attribute9 IN VARCHAR2 := NULL,
2892 p_attribute10 IN VARCHAR2 := NULL,
2893 p_attribute11 IN VARCHAR2 := NULL,
2894 p_attribute12 IN VARCHAR2 := NULL,
2895 p_attribute13 IN VARCHAR2 := NULL,
2896 p_attribute14 IN VARCHAR2 := NULL,
2897 p_attribute15 IN VARCHAR2 := NULL ) IS
2898
2899
2900 l_api_name CONSTANT VARCHAR2(30) := 'add_element';
2901 l_api_version CONSTANT NUMBER := 1.0;
2902
2903 l_user_id NUMBER;
2904 l_plan_id NUMBER;
2905 l_char_id NUMBER;
2906 l_result_column_name VARCHAR2(30);
2907
2908 -- Bug 5406294
2909 -- Modified the variable size from
2910 -- 30 to 150 as default values can be
2911 -- of size upto 150
2912 -- SHKALYAN 24-JUL-2006
2913 --
2914 -- l_default_value VARCHAR2(30);
2915 l_default_value VARCHAR2(150);
2916 l_enabled_flag NUMBER;
2917 l_mandatory_flag NUMBER;
2918 l_displayed_flag NUMBER;
2919 l_prompt VARCHAR2(30);
2920 l_prompt_sequence NUMBER;
2921 l_datatype NUMBER;
2922
2923 -- Tracking Bug : 3104827
2924 -- Added to include Three new Flags for Collection Plan Element
2925 -- saugupta Thu Aug 28 08:59:59 PDT 2003
2926 l_read_only_flag NUMBER;
2927 l_ss_poplist_flag NUMBER;
2928 l_information_flag NUMBER;
2929
2930 -- 12.1 Device Integration Project
2931 -- bhsankar Mon Nov 12 05:51:37 PST 2007
2932 l_device_flag NUMBER;
2933 l_override_flag NUMBER;
2934 x_device_id NUMBER;
2935 x_override_flag NUMBER;
2936
2937 BEGIN
2938
2939 -- Standard Start of API savepoint
2940
2941 SAVEPOINT add_element_pub;
2942
2943 -- Standard call to check for call compatibility.
2944 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
2945 l_api_name, g_pkg_name) THEN
2946 RAISE fnd_api.g_exc_unexpected_error;
2947 END IF;
2948
2949 -- Initialize message list if p_init_msg_list is set to TRUE.
2950 IF fnd_api.to_boolean(p_init_msg_list) THEN
2951 fnd_msg_pub.initialize;
2952 END IF;
2953
2954 -- Initialize API return status to success
2955 x_return_status := fnd_api.g_ret_sts_success;
2956
2957 -- *** start of logic ***
2958
2959 l_user_id := get_user_id(p_user_name);
2960 IF l_user_id = -1 THEN
2961 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
2962 fnd_msg_pub.add();
2963 raise fnd_api.g_exc_error;
2964 END IF;
2965
2966 l_plan_id := qa_plans_api.plan_id(upper(p_plan_name));
2967 IF (l_plan_id IS NULL) THEN
2968 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN');
2969 fnd_msg_pub.add();
2970 raise fnd_api.g_exc_error;
2971 END IF;
2972
2973 l_char_id := qa_chars_api.get_element_id(p_element_name);
2974 IF (l_char_id IS NULL) THEN
2975 fnd_message.set_name('QA', 'QA_API_INVALID_ELEMENT');
2976 fnd_msg_pub.add();
2977 raise fnd_api.g_exc_error;
2978 END IF;
2979
2980 IF element_exists(l_plan_id, l_char_id) THEN
2981 fnd_message.set_name('QA', 'QA_API_ELEMENT_ALREADY_ADDED');
2982 fnd_msg_pub.add();
2983 raise fnd_api.g_exc_error;
2984 END IF;
2985
2986 --
2987 -- Bug 3926150. Since add_plan_element can be called without
2988 -- first calling create_plan (to add elements to an existing plan,
2989 -- we will need to initialize the g_result_columns array here
2990 -- instead of just doing it once in create_plan. Thus we needed
2991 -- to cache the plan_id in the init function to avoid re-initializing
2992 -- every time.
2993 -- bso Fri Dec 3 21:42:13 PST 2004
2994 --
2995 init_result_column_array(l_plan_id);
2996
2997 l_enabled_flag := convert_flag(p_enabled_flag);
2998 l_displayed_flag := convert_flag(p_displayed_flag);
2999 IF p_mandatory_flag = g_inherit THEN
3000 l_mandatory_flag := qa_chars_api.mandatory_flag(l_char_id);
3001 ELSE
3002 l_mandatory_flag := convert_flag(p_mandatory_flag);
3003 END IF;
3004
3005 IF p_prompt IS NULL OR p_prompt = g_inherit THEN
3006 l_prompt := nvl(qa_chars_api.prompt(l_char_id), p_element_name);
3007 ELSE
3008 l_prompt := p_prompt;
3009 END IF;
3010
3011 IF p_prompt_sequence IS NULL THEN
3012 l_prompt_sequence := get_next_sequence(l_plan_id);
3013 ELSE
3014 IF prompt_sequence_exists(l_plan_id, p_prompt_sequence) THEN
3015 fnd_message.set_name('QA', 'QA_API_INVALID_PROMPT_SEQUENCE');
3016 fnd_msg_pub.add();
3017 RAISE fnd_api.g_exc_error;
3018 END IF;
3019 l_prompt_sequence := p_prompt_sequence;
3020 END IF;
3021
3022 IF p_default_value = g_inherit THEN
3023 l_default_value := qa_chars_api.default_value(l_char_id);
3024 ELSE
3025 l_default_value := p_default_value;
3026 END IF;
3027 l_datatype := qa_chars_api.datatype(l_char_id);
3028 validate_datatype(l_default_value, l_datatype);
3029
3030 IF p_result_column_name IS NULL THEN
3031 --
3032 -- Bug 3926150. Modify get_next_result_column_name function to
3033 -- use the new suggest_result_column function. bso
3034 --
3035
3036 --
3037 -- Strange PL/SQL oddity. nvl doesn't seem to use lazy evaluation.
3038 -- that is, suggest_result_column is being called even when
3039 -- hardcoded_column returns non-NULL. Need to switch to IF..THEN for
3040 -- optimal performance. bso Sat Dec 4 14:23:24 PST 2004
3041 --
3042 -- l_result_column_name := nvl(qa_chars_api.hardcoded_column(l_char_id),
3043 -- suggest_result_column(l_plan_id, l_char_id));
3044
3045 l_result_column_name := qa_chars_api.hardcoded_column(l_char_id);
3046 IF l_result_column_name IS NULL THEN
3047 l_result_column_name := suggest_result_column(l_plan_id, l_char_id);
3048 END IF;
3049
3050 IF l_result_column_name IS NULL THEN
3051 fnd_message.set_name('QA', 'QA_EXCEEDED_COLUMN_COUNT');
3052 fnd_msg_pub.add();
3053 raise fnd_api.g_exc_error;
3054 END IF;
3055 ELSE
3056 l_result_column_name := p_result_column_name;
3057 END IF;
3058
3059 --
3060 -- Bug 3926150.
3061 --
3062 mark_result_column(l_result_column_name);
3063
3064 -- added for read only flag
3065 l_read_only_flag := convert_flag(p_read_only_flag);
3066 l_ss_poplist_flag := convert_flag(p_ss_poplist_flag);
3067 l_information_flag := convert_flag(p_information_flag);
3068
3069 -- 12.1 Device Integration Project Start
3070 -- bhsankar Mon Nov 12 05:51:37 PST 2007
3071 l_device_flag := convert_flag(p_device_flag);
3072 l_override_flag := convert_flag(p_override_flag);
3073
3074 IF FND_PROFILE.VALUE('WIP_MES_OPS_FLAG') <> 1
3075 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
3076 fnd_message.set_name('WIP', 'WIP_WS_NO_LICENSE');
3077 fnd_msg_pub.add();
3078 raise fnd_api.g_exc_error;
3079 END IF;
3080
3081 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
3082 fnd_message.set_name('QA', 'QA_API_INVALID_DEVICE_FLAG');
3083 fnd_msg_pub.add();
3084 raise fnd_api.g_exc_error;
3085 END IF;
3086
3087 IF l_device_flag = 1 AND (p_device_name IS NULL OR p_sensor_alias IS NULL) THEN
3088 fnd_message.set_name('QA', 'QA_API_INVALID_DEVICE_NAME');
3089 fnd_msg_pub.add();
3090 raise fnd_api.g_exc_error;
3091 END IF;
3092
3093 IF l_device_flag = 1 AND p_device_name IS NOT NULL AND p_sensor_alias IS NOT NULL THEN
3094 get_device_details(trim(p_device_name), trim(p_sensor_alias), x_device_id, x_override_flag);
3095
3096 IF (x_device_id IS NULL) THEN
3097 fnd_message.set_name('QA', 'QA_API_INVALID_DEVICE_DETAILS');
3098 fnd_msg_pub.add();
3099 raise fnd_api.g_exc_error;
3100 END IF;
3101 END IF;
3102
3103 IF p_override_flag IS NULL THEN
3104 l_override_flag := x_override_flag;
3105 END IF;
3106 -- 12.1 Device Integration Project End.
3107
3108 INSERT INTO qa_plan_chars(
3109 last_update_date,
3110 last_updated_by,
3111 creation_date,
3112 created_by,
3113 last_update_login,
3114 plan_id,
3115 char_id,
3116 prompt_sequence,
3117 prompt,
3118 enabled_flag,
3119 mandatory_flag,
3120 default_value,
3121 displayed_flag,
3122 read_only_flag,
3123 ss_poplist_flag,
3124 information_flag,
3125 result_column_name,
3126 values_exist_flag,
3127 -- 12.1 Device Integration Project
3128 -- bhsankar Mon Nov 12 05:51:37 PST 2007
3129 device_flag,
3130 device_id,
3131 override_flag,
3132 attribute_category,
3133 attribute1,
3134 attribute2,
3135 attribute3,
3136 attribute4,
3137 attribute5,
3138 attribute6,
3139 attribute7,
3140 attribute8,
3141 attribute9,
3142 attribute10,
3143 attribute11,
3144 attribute12,
3145 attribute13,
3146 attribute14,
3147 attribute15 )
3148 VALUES(
3149 sysdate,
3150 l_user_id,
3151 sysdate,
3152 l_user_id,
3153 l_user_id,
3154 l_plan_id,
3155 l_char_id,
3156 l_prompt_sequence,
3157 l_prompt,
3158 l_enabled_flag,
3159 l_mandatory_flag,
3160 l_default_value,
3161 l_displayed_flag,
3162 l_read_only_flag,
3163 l_ss_poplist_flag,
3164 l_information_flag,
3165 l_result_column_name,
3166 2, -- values_exist_flag. defaulting a 2 to values flag
3167 -- until user calls add_value
3168 -- 12.1 Device Integration Project
3169 -- bhsankar Mon Nov 12 05:51:37 PST 2007
3170 nvl(l_device_flag, 2),
3171 x_device_id,
3172 nvl(l_override_flag, 2),
3173 p_attribute_category,
3174 p_attribute1 ,
3175 p_attribute2 ,
3176 p_attribute3 ,
3177 p_attribute4 ,
3178 p_attribute5 ,
3179 p_attribute6 ,
3180 p_attribute7 ,
3181 p_attribute8 ,
3182 p_attribute9 ,
3183 p_attribute10,
3184 p_attribute11,
3185 p_attribute12,
3186 p_attribute13,
3187 p_attribute14,
3188 p_attribute15
3189 );
3190 EXCEPTION
3191
3192 WHEN fnd_api.g_exc_error THEN
3193 ROLLBACK TO add_element_pub;
3194 x_return_status := fnd_api.g_ret_sts_error;
3195 fnd_msg_pub.count_and_get(
3196 p_count => x_msg_count,
3197 p_data => x_msg_data
3198 );
3199
3200 WHEN fnd_api.g_exc_unexpected_error THEN
3201 ROLLBACK TO add_element_pub;
3202 x_return_status := fnd_api.g_ret_sts_unexp_error;
3203 fnd_msg_pub.count_and_get(
3204 p_count => x_msg_count,
3205 p_data => x_msg_data
3206 );
3207
3208 WHEN OTHERS THEN
3209 ROLLBACK TO add_element_pub;
3210 x_return_status := fnd_api.g_ret_sts_unexp_error;
3211 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3212 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3213 END IF;
3214 fnd_msg_pub.count_and_get(
3215 p_count => x_msg_count,
3216 p_data => x_msg_data
3217 );
3218
3219 END add_plan_element;
3220
3221
3222 PROCEDURE complete_plan_processing(
3223 p_api_version IN NUMBER,
3224 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3225 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3226 p_user_name IN VARCHAR2 := NULL,
3227 p_plan_name IN VARCHAR2,
3228 p_organization_code IN VARCHAR2,
3229 p_commit IN VARCHAR2 := fnd_api.g_false,
3230 x_msg_count OUT NOCOPY NUMBER,
3231 x_msg_data OUT NOCOPY VARCHAR2,
3232 x_return_status OUT NOCOPY VARCHAR2) IS
3233
3234 l_api_name CONSTANT VARCHAR2(30) := 'complete_plan_definition';
3235 l_api_version CONSTANT NUMBER := 1.0;
3236
3237 l_user_id NUMBER;
3238 l_plan_id NUMBER;
3239 l_plan_name qa_plans.name%TYPE;
3240
3241 BEGIN
3242
3243 -- Standard Start of API savepoint
3244
3245 SAVEPOINT complete_plan_definition_pub;
3246
3247 -- Standard call to check for call compatibility.
3248 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
3249 l_api_name, g_pkg_name) THEN
3250 RAISE fnd_api.g_exc_unexpected_error;
3251 END IF;
3252
3253 -- Initialize message list if p_init_msg_list is set to TRUE.
3254 IF fnd_api.to_boolean(p_init_msg_list) THEN
3255 fnd_msg_pub.initialize;
3256 END IF;
3257
3258 -- Initialize API return status to success
3259 x_return_status := fnd_api.g_ret_sts_success;
3260
3261 -- *** start of logic ***
3262
3263 l_user_id := get_user_id(p_user_name);
3264 IF l_user_id = -1 THEN
3265 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
3266 fnd_msg_pub.add();
3267 raise fnd_api.g_exc_error;
3268 END IF;
3269
3270 l_plan_name := upper(p_plan_name);
3271 l_plan_id := qa_plans_api.plan_id(l_plan_name);
3272 IF (l_plan_id IS NULL) THEN
3273 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN');
3274 fnd_msg_pub.add();
3275 raise fnd_api.g_exc_error;
3276 END IF;
3277
3278 complete_plan_private(l_plan_id, l_plan_name, p_commit, l_user_id);
3279
3280 --
3281 -- Bug 3926150. Clean up the result column array.
3282 -- bso Fri Dec 3 21:54:48 PST 2004
3283 --
3284 init_result_column_array(-1);
3285
3286 fnd_msg_pub.count_and_get(
3287 p_count => x_msg_count,
3288 p_data => x_msg_data
3289 );
3290
3291 EXCEPTION
3292
3293 WHEN fnd_api.g_exc_error THEN
3294 ROLLBACK TO complete_plan_definition_pub;
3295 x_return_status := fnd_api.g_ret_sts_error;
3296 fnd_msg_pub.count_and_get(
3297 p_count => x_msg_count,
3298 p_data => x_msg_data
3299 );
3300
3301 WHEN fnd_api.g_exc_unexpected_error THEN
3302 ROLLBACK TO complete_plan_definition_pub;
3303 x_return_status := fnd_api.g_ret_sts_unexp_error;
3304 fnd_msg_pub.count_and_get(
3305 p_count => x_msg_count,
3306 p_data => x_msg_data
3307 );
3308
3309 WHEN OTHERS THEN
3310 ROLLBACK TO complete_plan_definition_pub;
3311 x_return_status := fnd_api.g_ret_sts_unexp_error;
3312 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3313 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3314 END IF;
3315 fnd_msg_pub.count_and_get(
3316 p_count => x_msg_count,
3317 p_data => x_msg_data
3318 );
3319
3320 END complete_plan_processing;
3321
3322
3323 PROCEDURE copy_collection_plan(
3324 p_api_version IN NUMBER,
3325 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3326 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3327 p_user_name IN VARCHAR2 := NULL,
3328 p_plan_name IN VARCHAR2,
3329 p_organization_code IN VARCHAR2,
3330 p_to_plan_name IN VARCHAR2,
3331 p_to_organization_code IN VARCHAR2,
3332 p_copy_actions_flag IN VARCHAR2 := fnd_api.g_true,
3333 p_copy_values_flag IN VARCHAR2 := fnd_api.g_true,
3334 p_copy_transactions_flag IN VARCHAR2 := fnd_api.g_true,
3335 p_commit IN VARCHAR2 := fnd_api.g_false,
3336 x_to_plan_id OUT NOCOPY NUMBER,
3337 x_msg_count OUT NOCOPY NUMBER,
3338 x_msg_data OUT NOCOPY VARCHAR2,
3339 x_return_status OUT NOCOPY VARCHAR2) IS
3340
3341 l_api_name CONSTANT VARCHAR2(30) := 'copy_plan';
3342 l_api_version CONSTANT NUMBER := 1.0;
3343
3344 l_user_id NUMBER;
3345 l_from_plan_id NUMBER;
3346 l_to_plan_name qa_plans.name%TYPE;
3347 l_to_org_id NUMBER;
3348
3349 BEGIN
3350
3351 -- Standard Start of API savepoint
3352
3353 SAVEPOINT copy_plan_pub;
3354
3355 -- Standard call to check for call compatibility.
3356 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
3357 l_api_name, g_pkg_name) THEN
3358 RAISE fnd_api.g_exc_unexpected_error;
3359 END IF;
3360
3361 -- Initialize message list if p_init_msg_list is set to TRUE.
3362 IF fnd_api.to_boolean(p_init_msg_list) THEN
3363 fnd_msg_pub.initialize;
3364 END IF;
3365
3366 -- Initialize API return status to success
3367 x_return_status := fnd_api.g_ret_sts_success;
3368
3369 -- *** start of logic ***
3370
3371 l_user_id := get_user_id(p_user_name);
3372 IF l_user_id = -1 THEN
3373 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
3374 fnd_msg_pub.add();
3375 raise fnd_api.g_exc_error;
3376 END IF;
3377
3378 --
3379 -- An unusual case for copy plan. The template plans have
3380 -- mixed case, but all regular plans have upper case. So,
3381 -- try them both.
3382 --
3383 l_from_plan_id := qa_plans_api.plan_id(p_plan_name);
3384 IF (l_from_plan_id IS NULL) THEN
3385 l_from_plan_id := qa_plans_api.plan_id(upper(p_plan_name));
3386 IF (l_from_plan_id IS NULL) THEN
3387 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN');
3388 fnd_msg_pub.add();
3389 raise fnd_api.g_exc_error;
3390 END IF;
3391 END IF;
3392
3393 l_to_plan_name := upper(p_to_plan_name);
3394 IF (illegal_chars(l_to_plan_name)) THEN
3395 fnd_message.set_name('QA', 'QA_NAME_SPECIAL_CHARS');
3396 fnd_msg_pub.add();
3397 raise fnd_api.g_exc_error;
3398 END IF;
3399
3400 l_to_org_id := qa_plans_api.get_org_id(p_to_organization_code);
3401 IF (l_to_org_id IS NULL) THEN
3402 fnd_message.set_name('QA', 'QA_API_INVALID_ORG_CODE');
3403 fnd_msg_pub.add();
3404 raise fnd_api.g_exc_error;
3405 END IF;
3406
3407 x_to_plan_id := copy_plan_header(l_from_plan_id, l_to_plan_name,
3408 l_to_org_id, l_user_id);
3409
3410 IF (p_copy_values_flag = fnd_api.g_true) THEN
3411 copy_plan_element_values(l_from_plan_id, x_to_plan_id,
3412 l_user_id);
3413 END IF;
3414
3415 IF (p_copy_actions_flag = fnd_api.g_true) THEN
3416 copy_plan_element_actions(l_from_plan_id, x_to_plan_id,
3417 l_user_id);
3418 END IF;
3419
3420 IF (p_copy_transactions_flag = fnd_api.g_true) THEN
3421 copy_plan_transactions(l_from_plan_id, x_to_plan_id,
3422 l_user_id);
3423 END IF;
3424
3425 --
3426 -- Bug 3926150. Initialize the result column array before copying
3427 -- the elements.
3428 -- bso Fri Dec 3 22:06:09 PST 2004
3429 --
3430 init_result_column_array(x_to_plan_id);
3431
3432 --
3433 -- Because of a special "where" clause in the above copy_plan...
3434 -- functions, the copy_plan_elements call must be put at this
3435 -- position, after all the above calls.
3436 --
3437 copy_plan_elements_bulk(l_from_plan_id, x_to_plan_id,
3438 p_copy_values_flag, l_user_id);
3439
3440 complete_plan_private(x_to_plan_id, l_to_plan_name, p_commit, l_user_id);
3441
3442 --
3443 -- Bug 3926150. Re-init the result column array afterwards.
3444 -- bso Fri Dec 3 22:06:09 PST 2004
3445 --
3446 init_result_column_array(-1);
3447
3448 fnd_msg_pub.count_and_get(
3449 p_count => x_msg_count,
3450 p_data => x_msg_data
3451 );
3452
3453 EXCEPTION
3454
3455 WHEN fnd_api.g_exc_error THEN
3456 ROLLBACK TO copy_plan_pub;
3457 x_return_status := fnd_api.g_ret_sts_error;
3458 fnd_msg_pub.count_and_get(
3459 p_count => x_msg_count,
3460 p_data => x_msg_data
3461 );
3462
3463 WHEN fnd_api.g_exc_unexpected_error THEN
3464 ROLLBACK TO copy_plan_pub;
3465 x_return_status := fnd_api.g_ret_sts_unexp_error;
3466 fnd_msg_pub.count_and_get(
3467 p_count => x_msg_count,
3468 p_data => x_msg_data
3469 );
3470
3471 WHEN OTHERS THEN
3472 ROLLBACK TO copy_plan_pub;
3473 x_return_status := fnd_api.g_ret_sts_unexp_error;
3474 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3475 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3476 END IF;
3477 fnd_msg_pub.count_and_get(
3478 p_count => x_msg_count,
3479 p_data => x_msg_data
3480 );
3481
3482 END copy_collection_plan;
3483
3484
3485 PROCEDURE delete_plan_element(
3486 p_api_version IN NUMBER,
3487 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3488 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3489 p_user_name IN VARCHAR2 := NULL,
3490 p_plan_name IN VARCHAR2,
3491 p_organization_code IN VARCHAR2,
3492 p_element_name IN VARCHAR2,
3493 p_commit IN VARCHAR2 := fnd_api.g_false,
3494 x_msg_count OUT NOCOPY NUMBER,
3495 x_msg_data OUT NOCOPY VARCHAR2,
3496 x_return_status OUT NOCOPY VARCHAR2) IS
3497
3498 l_api_name CONSTANT VARCHAR2(30) := 'delete_plan_element';
3499 l_api_version CONSTANT NUMBER := 1.0;
3500
3501 l_user_id NUMBER;
3502 l_plan_id NUMBER;
3503 l_element_id NUMBER;
3504 l_org_id NUMBER;
3505 l_plan_name qa_plans.name%TYPE;
3506
3507
3508 BEGIN
3509
3510 -- Standard Start of API savepoint
3511
3512 SAVEPOINT delete_plan_element_pub;
3513
3514 -- Standard call to check for call compatibility.
3515 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
3516 l_api_name, g_pkg_name) THEN
3517 RAISE fnd_api.g_exc_unexpected_error;
3518 END IF;
3519
3520 -- Initialize message list if p_init_msg_list is set to TRUE.
3521 IF fnd_api.to_boolean(p_init_msg_list) THEN
3522 fnd_msg_pub.initialize;
3523 END IF;
3524
3525 -- Initialize API return status to success
3526 x_return_status := fnd_api.g_ret_sts_success;
3527
3528 -- *** start of logic ***
3529
3530 l_user_id := get_user_id(p_user_name);
3531 IF l_user_id = -1 THEN
3532 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
3533 fnd_msg_pub.add();
3534 raise fnd_api.g_exc_error;
3535 END IF;
3536
3537 l_plan_name := upper(p_plan_name);
3538 l_plan_id := qa_plans_api.plan_id(l_plan_name);
3539 IF (l_plan_id IS NULL) THEN
3540 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN');
3541 fnd_msg_pub.add();
3542 RAISE fnd_api.g_exc_error;
3543 END IF;
3544
3545 l_element_id := qa_chars_api.get_element_id(p_element_name);
3546 IF (l_element_id IS NULL) THEN
3547 fnd_message.set_name('QA', 'QA_API_INVALID_ELEMENT');
3548 fnd_msg_pub.add();
3549 RAISE fnd_api.g_exc_error;
3550 END IF;
3551
3552 IF NOT element_exists(l_plan_id, l_element_id) THEN
3553 fnd_message.set_name('QA', 'QA_API_ELEMENT_NOT_IN_PLAN');
3554 fnd_msg_pub.add();
3555 RAISE fnd_api.g_exc_error;
3556 END IF;
3557
3558 IF results_exist(l_plan_id, l_element_id) THEN
3559 fnd_message.set_name('QA', 'QA_RESULTS_EXIST_FOR_PLANCHAR');
3560 fnd_msg_pub.add();
3561 RAISE fnd_api.g_exc_error;
3562 END IF;
3563
3564 delete_plan_element_values(l_plan_id, l_element_id);
3565 delete_plan_element_actions(l_plan_id, l_element_id);
3566 delete_plan_element(l_plan_id, l_element_id);
3567
3568 complete_plan_private(l_plan_id, l_plan_name, p_commit, l_user_id);
3569
3570 fnd_msg_pub.count_and_get(
3571 p_count => x_msg_count,
3572 p_data => x_msg_data
3573 );
3574
3575 EXCEPTION
3576
3577 WHEN fnd_api.g_exc_error THEN
3578 ROLLBACK TO delete_plan_element_pub;
3579 x_return_status := fnd_api.g_ret_sts_error;
3580 fnd_msg_pub.count_and_get(
3581 p_count => x_msg_count,
3582 p_data => x_msg_data
3583 );
3584
3585 WHEN fnd_api.g_exc_unexpected_error THEN
3586 ROLLBACK TO delete_plan_element_pub;
3587 x_return_status := fnd_api.g_ret_sts_unexp_error;
3588 fnd_msg_pub.count_and_get(
3589 p_count => x_msg_count,
3590 p_data => x_msg_data
3591 );
3592
3593 WHEN OTHERS THEN
3594 ROLLBACK TO delete_plan_element_pub;
3595 x_return_status := fnd_api.g_ret_sts_unexp_error;
3596 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3597 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3598 END IF;
3599 fnd_msg_pub.count_and_get(
3600 p_count => x_msg_count,
3601 p_data => x_msg_data
3602 );
3603
3604 END delete_plan_element;
3605
3606
3607 PROCEDURE delete_collection_plan(
3608 p_api_version IN NUMBER,
3609 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3610 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3611 p_user_name IN VARCHAR2 := NULL,
3612 p_plan_name IN VARCHAR2,
3613 p_organization_code IN VARCHAR2,
3614 p_commit IN VARCHAR2 := fnd_api.g_false,
3615 x_msg_count OUT NOCOPY NUMBER,
3616 x_msg_data OUT NOCOPY VARCHAR2,
3617 x_return_status OUT NOCOPY VARCHAR2) IS
3618
3619 l_api_name CONSTANT VARCHAR2(30) := 'delete_plan';
3620 l_api_version CONSTANT NUMBER := 1.0;
3621
3622 l_user_id NUMBER;
3623 l_plan_id NUMBER;
3624 l_org_id NUMBER;
3625 l_plan_name qa_plans.name%TYPE;
3626
3627 BEGIN
3628
3629 -- Standard Start of API savepoint
3630
3631 SAVEPOINT delete_plan_pub;
3632
3633 -- Standard call to check for call compatibility.
3634 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
3635 l_api_name, g_pkg_name) THEN
3636 RAISE fnd_api.g_exc_unexpected_error;
3637 END IF;
3638
3639 -- Initialize message list if p_init_msg_list is set to TRUE.
3640 IF fnd_api.to_boolean(p_init_msg_list) THEN
3641 fnd_msg_pub.initialize;
3642 END IF;
3643
3644 -- Initialize API return status to success
3645 x_return_status := fnd_api.g_ret_sts_success;
3646
3647
3648 -- *** start of logic ***
3649
3650 l_user_id := get_user_id(p_user_name);
3651 IF l_user_id = -1 THEN
3652 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
3653 fnd_msg_pub.add();
3654 raise fnd_api.g_exc_error;
3655 END IF;
3656
3657 l_plan_name := upper(p_plan_name);
3658 l_plan_id := qa_plans_api.plan_id(l_plan_name);
3659 IF l_plan_id IS NULL THEN
3660 fnd_message.set_name('QA', 'QA_API_INVALID_PLAN');
3661 fnd_msg_pub.add();
3662 raise fnd_api.g_exc_error;
3663 END IF;
3664
3665 IF results_exist(l_plan_id) THEN
3666 fnd_message.set_name('QA', 'QA_CANT_DELETE_QPLAN');
3667 fnd_msg_pub.add();
3668 RAISE fnd_api.g_exc_error;
3669 END IF;
3670
3671 delete_plan_elements(l_plan_id);
3672 delete_plan_values(l_plan_id);
3673 delete_plan_transactions(l_plan_id);
3674 delete_plan_actions(l_plan_id);
3675 delete_plan_header(l_plan_id);
3676 delete_plan_private(l_plan_name, p_commit, l_user_id);
3677
3678 fnd_msg_pub.count_and_get(
3679 p_count => x_msg_count,
3680 p_data => x_msg_data
3681 );
3682
3683 EXCEPTION
3684
3685 WHEN fnd_api.g_exc_error THEN
3686 ROLLBACK TO delete_plan_pub;
3687 x_return_status := fnd_api.g_ret_sts_error;
3688 fnd_msg_pub.count_and_get(
3689 p_count => x_msg_count,
3690 p_data => x_msg_data
3691 );
3692
3693 WHEN fnd_api.g_exc_unexpected_error THEN
3694 ROLLBACK TO delete_plan_pub;
3695 x_return_status := fnd_api.g_ret_sts_unexp_error;
3696 fnd_msg_pub.count_and_get(
3697 p_count => x_msg_count,
3698 p_data => x_msg_data
3699 );
3700
3701 WHEN OTHERS THEN
3702 ROLLBACK TO delete_plan_pub;
3703 x_return_status := fnd_api.g_ret_sts_unexp_error;
3704 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3705 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3706 END IF;
3707 fnd_msg_pub.count_and_get(
3708 p_count => x_msg_count,
3709 p_data => x_msg_data
3710 );
3711
3712 END delete_collection_plan;
3713
3714 FUNCTION get_plan_type (p_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
3715
3716 CURSOR c IS
3717 SELECT meaning
3718 FROM fnd_lookup_values
3719 WHERE lookup_type = 'COLLECTION_PLAN_TYPE'
3720 AND lookup_code = p_lookup_code;
3721
3722 ret_val VARCHAR2(80);
3723 BEGIN
3724
3725 OPEN c;
3726 FETCH c INTO ret_val;
3727 IF c%NOTFOUND THEN
3728 ret_val := '';
3729 END IF;
3730
3731 CLOSE c;
3732 RETURN ret_val;
3733
3734 END get_plan_type;
3735
3736 END qa_plans_pub;
3737
[Home] [Help]