1 PACKAGE BODY qa_specs_pub AS
2 /* $Header: qltpspcb.plb 120.3 2011/05/19 12:23:20 ntungare ship $ */
3
4
5 --
6 -- Safe globals.
7 --
8
9 TYPE qa_specs_table IS TABLE OF qa_specs%ROWTYPE INDEX BY BINARY_INTEGER;
10 g_qa_specs_array qa_specs_table;
11
12 g_pkg_name CONSTANT VARCHAR2(30):= 'qa_specs_pub';
13 g_user_name_cache fnd_user.user_name%TYPE := NULL;
14 g_user_id_cache NUMBER;
15
16 --
17 -- General utility functions
18 --
19
20 FUNCTION get_user_id(p_name VARCHAR2) RETURN NUMBER IS
21 --
22 -- Decode user name from fnd_user table.
23 --
24 id NUMBER;
25
26 CURSOR user_cursor IS
27 SELECT user_id
28 FROM fnd_user
29 WHERE user_name = p_name;
30 BEGIN
31
32 --
33 -- Code is duplicated in qltpspcb.plb. Any modification here
34 -- should be propagated to that file.
35 --
36
37 IF p_name IS NULL THEN
38 RETURN nvl(fnd_global.user_id, -1);
39 END IF;
40
41 --
42 -- It is very common for the same user to call the
43 -- APIs successively.
44 --
45 IF g_user_name_cache = p_name THEN
46 RETURN g_user_id_cache;
47 END IF;
48
49 OPEN user_cursor;
50 FETCH user_cursor INTO id;
51 IF user_cursor%NOTFOUND THEN
52 CLOSE user_cursor;
53 RETURN -1;
54 END IF;
55 CLOSE user_cursor;
56
57 g_user_name_cache := p_name;
58 g_user_id_cache := id;
59
60 RETURN id;
61 END get_user_id;
62
63 --
64 -- Global caching mechanism. A global array is used to kept
65 -- all fetched qa_specs. If the same spec data is required in
66 -- a future call, the cached data will be used.
67 --
68
69 FUNCTION exists_qa_specs(spec_id IN NUMBER)
70 RETURN BOOLEAN IS
71
72 BEGIN
73
74 RETURN g_qa_specs_array.EXISTS(spec_id);
75
76 END exists_qa_specs;
77
78
79 PROCEDURE fetch_qa_specs(spec_id IN NUMBER) IS
80 --
81 -- Retrieve a specification header and cached.
82 --
83 CURSOR c1 (p_id NUMBER) IS
84 SELECT *
85 FROM qa_specs
86 WHERE spec_id = p_id;
87
88 BEGIN
89 IF NOT exists_qa_specs(spec_id) THEN
90 OPEN c1(spec_id);
91 FETCH c1 INTO g_qa_specs_array(spec_id);
92 CLOSE c1;
93 END IF;
94 END fetch_qa_specs;
95
96
97 PROCEDURE fetch_qa_specs(p_spec_name IN VARCHAR2, x_spec_id OUT NOCOPY NUMBER) IS
98 --
99 -- Retrieve a specification header and cached. Similar to
100 -- the previous function but query by spec name instead of id.
101 --
102 CURSOR c1 IS
103 SELECT *
104 FROM qa_specs
105 WHERE spec_name = p_spec_name;
106
107 temp qa_specs%ROWTYPE;
108
109 BEGIN
110 OPEN c1;
111 FETCH c1 INTO temp;
112 IF c1%NOTFOUND THEN
113 CLOSE c1;
114 RETURN;
115 END IF;
116
117 x_spec_id := temp.spec_id;
118 g_qa_specs_array(x_spec_id) := temp;
119 CLOSE c1;
120 END fetch_qa_specs;
121
122
123 FUNCTION item_id(spec_id IN NUMBER)
124 RETURN NUMBER IS
125
126 BEGIN
127
128 fetch_qa_specs(spec_id);
129 IF NOT exists_qa_specs(spec_id) THEN
130 RETURN NULL;
131 END IF;
132 RETURN g_qa_specs_array(spec_id).item_id;
133
134 END item_id;
135
136
137 FUNCTION organization_id(spec_id IN NUMBER)
138 RETURN NUMBER IS
139
140 BEGIN
141
142 fetch_qa_specs(spec_id);
143 IF NOT exists_qa_specs(spec_id) THEN
144 RETURN NULL;
145 END IF;
146 RETURN g_qa_specs_array(spec_id).organization_id;
147
148 END organization_id;
149
150
151 FUNCTION spec_name(spec_id IN NUMBER)
152 RETURN VARCHAR2 IS
153
154 BEGIN
155
156 fetch_qa_specs(spec_id);
157 IF NOT exists_qa_specs(spec_id) THEN
158 RETURN NULL;
159 END IF;
160 RETURN g_qa_specs_array(spec_id).spec_name;
161
162 END spec_name;
163
164
165 FUNCTION category_set_id(spec_id IN NUMBER)
166 RETURN NUMBER IS
167
168 BEGIN
169
170 fetch_qa_specs(spec_id);
171 IF NOT exists_qa_specs(spec_id) THEN
172 RETURN NULL;
173 END IF;
174 RETURN g_qa_specs_array(spec_id).category_set_id;
175
176 END category_set_id;
177
178
179 FUNCTION category_id(spec_id IN NUMBER)
180 RETURN NUMBER IS
181
182 BEGIN
183
184 fetch_qa_specs(spec_id);
185 IF NOT exists_qa_specs(spec_id) THEN
186 RETURN NULL;
187 END IF;
188 RETURN g_qa_specs_array(spec_id).category_id;
189
190 END category_id;
191
192
193 FUNCTION item_revision(spec_id IN NUMBER)
194 RETURN VARCHAR2 IS
195
196 BEGIN
197
198 fetch_qa_specs(spec_id);
199 IF NOT exists_qa_specs(spec_id) THEN
200 RETURN NULL;
201 END IF;
202 RETURN g_qa_specs_array(spec_id).item_revision;
203
204 END item_revision;
205
206
207 FUNCTION vendor_id(spec_id IN NUMBER)
208 RETURN NUMBER IS
209
210 BEGIN
211
212 fetch_qa_specs(spec_id);
213 IF NOT exists_qa_specs(spec_id) THEN
214 RETURN NULL;
215 END IF;
216 RETURN g_qa_specs_array(spec_id).vendor_id;
217
218 END vendor_id;
219
220
221 FUNCTION customer_id(spec_id IN NUMBER)
222 RETURN NUMBER IS
223
224 BEGIN
225
226 fetch_qa_specs(spec_id);
227 IF NOT exists_qa_specs(spec_id) THEN
228 RETURN NULL;
229 END IF;
230 RETURN g_qa_specs_array(spec_id).customer_id;
231
232 END customer_id;
233
234
235 FUNCTION char_id(spec_id IN NUMBER)
236 RETURN NUMBER IS
237
238 BEGIN
239
240 fetch_qa_specs(spec_id);
241 IF NOT exists_qa_specs(spec_id) THEN
242 RETURN NULL;
243 END IF;
244 RETURN g_qa_specs_array(spec_id).char_id;
245
246 END char_id;
247
248
249 FUNCTION spec_element_value(spec_id IN NUMBER)
250 RETURN VARCHAR2 IS
251
252 BEGIN
253
254 fetch_qa_specs(spec_id);
255 IF NOT exists_qa_specs(spec_id) THEN
256 RETURN NULL;
257 END IF;
258 RETURN g_qa_specs_array(spec_id).spec_element_value;
259
260 END spec_element_value;
261
262
263 FUNCTION get_context_element_id(p_element_name IN VARCHAR2)
264 RETURN NUMBER IS
265
266 CURSOR c IS
267 SELECT char_id
268 FROM qa_chars
269 WHERE char_context_flag = 1 AND
270 enabled_flag = 1 AND
271 name = p_element_name;
272
273 l_char_id NUMBER;
274
275 BEGIN
276
277 OPEN c;
278 FETCH c INTO l_char_id;
279 IF c%NOTFOUND THEN
280 CLOSE c;
281 fnd_message.set_name('QA', 'QA_API_INVALID_ELEMENT');
282 fnd_msg_pub.add();
283 RAISE fnd_api.g_exc_error;
284 END IF;
285
286 CLOSE c;
287 RETURN l_char_id;
288
289 END get_context_element_id;
290
291 --
292 -- The child specs inherit all the spec elements of the master.
293 --
294 -- rkunchal
295 --
296
297 PROCEDURE check_for_spec_element(p_spec_id IN NUMBER) IS
298
299 CURSOR c IS
300 SELECT 1
301 FROM qa_spec_chars qsc, qa_specs qs
302 WHERE qs.spec_id = p_spec_id
303 AND qs.common_spec_id = qsc.spec_id;
304
305 l_dummy NUMBER;
306 l_found BOOLEAN;
307
308 BEGIN
309
310 OPEN c;
311 FETCH c INTO l_dummy;
312 l_found := c%FOUND;
313 CLOSE c;
314
315 IF NOT l_found THEN
316 fnd_message.set_name('QA', 'QA_API_SPEC_MUST_HAVE_CHARS');
317 fnd_msg_pub.add();
318 RAISE fnd_api.g_exc_error;
319 END IF;
320
321 END check_for_spec_element;
322
323
324 FUNCTION get_revision_flag(p_item_id IN NUMBER, p_org_id IN NUMBER)
325 RETURN NUMBER IS
326
327 CURSOR c IS
328 SELECT revision_qty_control_code
329 from mtl_system_items
330 where inventory_item_id = p_item_id
331 and organization_id = p_org_id;
332
333 l_revision_flag NUMBER;
334
335 BEGIN
336
337 OPEN c;
338 FETCH c INTO l_revision_flag;
339 CLOSE c;
340
341 RETURN l_revision_flag;
342
343 END get_revision_flag;
344
345
346 FUNCTION process_item_and_revision(p_item_name IN VARCHAR2, p_item_revision
347 IN VARCHAR2, p_org_id IN NUMBER) RETURN NUMBER IS
348
349 l_item_id NUMBER;
350 l_revision_flag NUMBER;
351
352 BEGIN
353
354 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
355
356 IF (l_item_id IS NULL) THEN
357 fnd_message.set_name('QA', 'QA_API_INVALID_ITEM_NAME');
358 fnd_msg_pub.add();
359 RAISE fnd_api.g_exc_error;
360 ELSE
361 l_revision_flag := get_revision_flag(l_item_id, p_org_id);
362 IF (l_revision_flag = 1) THEN
363
364 IF (p_item_revision IS NOT NULL) THEN
365 fnd_message.set_name('QA', 'QA_API_REVISION_CONTROLLED');
366 fnd_msg_pub.add();
367 RAISE fnd_api.g_exc_error;
368 END IF;
369
370 ELSIF (l_revision_flag = 2) and (p_item_revision IS NULL) THEN
371 fnd_message.set_name('QA', 'QA_API_MANDATORY_REVISION');
372 fnd_msg_pub.add();
373 RAISE fnd_api.g_exc_error;
374
375 ELSE
376 IF NOT qa_plan_element_api.validate_revision(p_org_id,
377 l_item_id, p_item_revision) THEN
378 fnd_message.set_name('QA', 'QA_API_INVALID_REVISION');
379 fnd_msg_pub.add();
380 RAISE fnd_api.g_exc_error;
381 END IF;
382 END IF;
383 END IF;
384
385 RETURN l_item_id;
386
387 END;
388
389
390 PROCEDURE validate_datatype(p_value IN VARCHAR2, p_datatype NUMBER) IS
391
392 temp_number Number;
393 temp_date Date;
394
395 BEGIN
396
397 IF p_value IS NULL THEN
398 RETURN;
399 END IF;
400
401 IF p_datatype = qa_ss_const.number_datatype THEN
402 BEGIN
403 temp_number := to_number(p_value);
404 EXCEPTION WHEN OTHERS THEN
405 fnd_message.set_name('QA', 'QA_INVALID_NUMBER');
406 fnd_msg_pub.add();
407 RAISE fnd_api.g_exc_error;
408 END;
409
410 ELSIF p_datatype = qa_ss_const.date_datatype THEN
411 BEGIN
412 temp_date := qltdate.any_to_date(p_value);
413 EXCEPTION WHEN OTHERS THEN
414 fnd_message.set_name('QA', 'QA_INVALID_DATE');
415 fnd_msg_pub.add();
416 RAISE fnd_api.g_exc_error;
417 END;
418 END IF;
419
420 END validate_datatype;
421
422
423 FUNCTION combination_exists(
424 p_category_set_id IN NUMBER,
425 p_category_id IN NUMBER,
426 p_item_id IN NUMBER,
427 p_item_revision IN VARCHAR2,
428 p_org_id IN NUMBER,
429 p_vendor_id IN NUMBER,
430 p_customer_id IN NUMBER,
431 p_char_id IN NUMBER,
432 p_sub_type_element_value IN VARCHAR2,
433 x_spec_name OUT NOCOPY VARCHAR2)
434 RETURN BOOLEAN IS
435
436 l_spec_name varchar2(30);
437
438 CURSOR c IS
439 SELECT spec_name
440 FROM qa_specs
441 WHERE category_set_id = p_category_set_id
442 AND category_id = p_category_id
443 AND item_id = p_item_id
444 AND item_revision = nvl(p_item_revision, 'NONE')
445 AND organization_id = p_org_id
446 AND vendor_id = p_vendor_id
447 AND customer_id = p_customer_id
448 AND char_id = p_char_id
449 AND spec_element_value = p_sub_type_element_value;
450
451 result BOOLEAN;
452 dummy VARCHAR2(30);
453
454 BEGIN
455
456 OPEN c;
457 FETCH c INTO x_spec_name;
458 result := c%FOUND;
459 CLOSE c;
460
461 RETURN result;
462
463 END combination_exists;
464
465
466 FUNCTION get_category_set_id(
467 p_category_set_name IN VARCHAR2,
468 x_structure_id OUT NOCOPY NUMBER,
469 x_validate_flag OUT NOCOPY mtl_category_sets.validate_flag%TYPE)
470 RETURN NUMBER IS
471
472 CURSOR c IS
473 SELECT category_set_id, structure_id, validate_flag
474 FROM mtl_category_sets
475 WHERE category_set_name = p_category_set_name;
476
477 l_category_set_id NUMBER;
478
479 BEGIN
480
481 OPEN c;
482 FETCH c INTO l_category_set_id, x_structure_id, x_validate_flag;
483 CLOSE c;
484
485 IF (l_category_set_id IS NULL) THEN
486 fnd_message.set_name('QA', 'QA_API_INVALID_CATEGORY_SET');
487 fnd_msg_pub.add();
488 RAISE fnd_api.g_exc_error;
489 END IF;
490
491 RETURN l_category_set_id;
492
493 END get_category_set_id;
494
495
496 FUNCTION get_category_id(
497 p_category_name IN VARCHAR2,
498 p_category_set_id IN NUMBER,
499 p_org_id IN NUMBER,
500 p_structure_id IN NUMBER,
501 p_validate_flag mtl_category_sets.validate_flag%TYPE)
502 RETURN NUMBER IS
503
504 -- Bug 2532177. Removed the Default value for l_category_id variable.
505 l_category_id NUMBER;
506 l_where_clause VARCHAR2(500);
507
508 BEGIN
509
510 IF p_validate_flag = 'Y' THEN
511 l_where_clause :=
512 '(nvl(disable_date, sysdate+1) > sysdate) AND category_id IN
513 (SELECT category_id
514 FROM mtl_category_set_valid_cats vc
515 WHERE vc.category_set_id = ' || p_category_set_id || ')';
516 ELSE
517 l_where_clause := '(nvl(disable_date, sysdate+1) > sysdate)';
518 END IF;
519
520 IF FND_FLEX_KEYVAL.validate_segs(
521 operation => 'CHECK_COMBINATION',
522 key_flex_code => 'MCAT',
523 appl_short_name => 'INV',
524 structure_number => p_structure_id,
525 concat_segments => p_category_name,
526 data_set => p_structure_id,
527 where_clause => l_where_clause) THEN
528
529 l_category_id := FND_FLEX_KEYVAL.combination_id;
530 END IF;
531
532 IF (l_category_id IS NULL) THEN
533 fnd_message.set_name('QA', 'QA_API_INVALID_CATEGORY');
534 fnd_msg_pub.add();
535 RAISE fnd_api.g_exc_error;
536 END IF;
537
538 RETURN l_category_id;
539
540 END get_category_id;
541
542
543 FUNCTION get_spec_id(p_spec_name IN VARCHAR2, p_org_code IN VARCHAR2)
544 RETURN NUMBER IS
545
546 l_spec_id NUMBER;
547 l_org_id NUMBER;
548
549 CURSOR c IS
550 SELECT spec_id
551 FROM qa_specs
552 WHERE spec_name = p_spec_name AND organization_id = l_org_id;
553
554 BEGIN
555
556 l_org_id := qa_plans_api.get_org_id(p_org_code);
557
558 OPEN c;
559 FETCH c INTO l_spec_id;
560
561 IF c%NOTFOUND THEN
562 CLOSE c;
563 fnd_message.set_name('QA', 'QA_API_SPEC_NAME_NOT_FOUND');
564 fnd_msg_pub.add();
565 RAISE fnd_api.g_exc_error;
566 END IF;
567
568 CLOSE c;
569
570 RETURN l_spec_id;
571
572 END get_spec_id;
573
574
575 FUNCTION spec_exists(p_name IN VARCHAR2)
576 RETURN BOOLEAN IS
577
578 CURSOR c IS
579 SELECT 1
580 FROM qa_specs
581 WHERE spec_name = p_name;
582
583 result BOOLEAN;
584 dummy NUMBER;
585
586 BEGIN
587
588 OPEN c;
589 FETCH c INTO dummy;
590 result := c%FOUND;
591 CLOSE c;
592
593 RETURN result;
594
595 END spec_exists;
596
597
598 FUNCTION spec_element_exists(p_spec_id IN VARCHAR2, p_char_id IN NUMBER)
599 RETURN BOOLEAN IS
600
601 CURSOR c IS
602 SELECT 1
603 FROM qa_spec_chars
604 WHERE spec_id = p_spec_id AND char_id = p_char_id;
605
606 result BOOLEAN;
607 dummy NUMBER;
608
609 BEGIN
610
611 OPEN c;
612 FETCH c INTO dummy;
613 result := c%FOUND;
614 CLOSE c;
615
616 RETURN result;
617
618 END spec_element_exists;
619
620
621 FUNCTION convert_flag(p_flag IN VARCHAR2)
622 RETURN NUMBER IS
623
624 BEGIN
625
626 IF p_flag = fnd_api.g_true THEN
627 RETURN 1;
628 END IF;
629
630 RETURN 2;
631
632 END convert_flag;
633
634
635 --
636 -- Global Specifications Enhancements
637 -- Introduced the following internal functions
638 -- Both are overloaded for ease of use
639 --
640 -- Extremely useful to change the API to support
641 -- Global Specifications Enhancements
642 --
643 -- rkunchal
644 --
645 -- is_child_spec returns true if the passed spec is a child spec.
646 -- child_spec_exists returns true if the passed spec is being
647 -- referenced by at least one more spec.
648 --
649
650 FUNCTION is_child_spec(p_spec_id IN NUMBER) RETURN BOOLEAN IS
651
652 CURSOR c IS
653 SELECT 1
654 FROM qa_specs
655 WHERE spec_id = p_spec_id
656 AND spec_id <> common_spec_id;
657
658 result BOOLEAN;
659 dummy NUMBER;
660
661 BEGIN
662
663 OPEN c;
664 FETCH c INTO dummy;
665 result := c%FOUND;
666 CLOSE c;
667
668 RETURN result;
669
670 END is_child_spec;
671
672 FUNCTION is_child_spec(p_spec_name IN VARCHAR2) RETURN BOOLEAN IS
673
674 CURSOR c IS
675 SELECT 1
676 FROM qa_specs
677 WHERE spec_name = p_spec_name
678 AND spec_id <> common_spec_id;
679
680 result BOOLEAN;
681 dummy NUMBER;
682
683 BEGIN
684
685 OPEN c;
686 FETCH c INTO dummy;
687 result := c%FOUND;
688 CLOSE c;
689
690 RETURN result;
691
692 END is_child_spec;
693
694 FUNCTION child_spec_exists(p_spec_id IN NUMBER) RETURN BOOLEAN IS
695
696 CURSOR c IS
697 SELECT 1
698 FROM qa_specs
699 WHERE common_spec_id = p_spec_id
700 AND spec_id <> common_spec_id;
701
702 result BOOLEAN;
703 dummy NUMBER;
704
705 BEGIN
706
707 OPEN c;
708 FETCH c INTO dummy;
709 result := c%FOUND;
710 CLOSE c;
711
712 RETURN result;
713
714 END child_spec_exists;
715
716 FUNCTION child_spec_exists(p_spec_name IN VARCHAR2) RETURN BOOLEAN IS
717
718 CURSOR c IS
719 SELECT 1
720 FROM qa_specs qs1, qa_specs qs2
721 WHERE qs1.spec_name = p_spec_name
722 AND qs1.spec_id = qs2.common_spec_id
723 AND qs2.spec_id <> qs2.common_spec_id;
724
725 result BOOLEAN;
726 dummy NUMBER;
727
728 BEGIN
729
730 OPEN c;
731 FETCH c INTO dummy;
732 result := c%FOUND;
733 CLOSE c;
734
735 RETURN result;
736
737 END child_spec_exists;
738
739 --
740 --
741 -- Start of Public APIs
742 --
743
744 --
745 -- Add a new argument to pass common_spec_id also.
746 -- This API can be used to create a referencing spec (child spec) also.
747 -- Additional validation for a referencing spec is, to check if
748 -- the referenced spec is a master or not.
749 --
750
751 PROCEDURE create_specification(
752 p_api_version IN NUMBER,
753 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
754 p_validation_level IN NUMBER := fnd_api.G_VALID_LEVEL_FULL,
755 p_user_name IN VARCHAR2 := NULL,
756 p_spec_name IN VARCHAR2,
757 p_organization_code IN VARCHAR2,
758 p_reference_spec IN VARCHAR2 := NULL,
759 p_effective_from IN DATE := SYSDATE,
760 p_effective_to IN DATE := NULL,
761 p_assignment_type IN NUMBER := qa_specs_pub.g_spec_type_item,
762 p_category_set_name IN VARCHAR2 := NULL,
763 p_category_name IN VARCHAR2 := NULL,
764 p_item_name IN VARCHAR2 := NULL,
765 p_item_revision IN VARCHAR2 := NULL,
766 p_supplier_name IN VARCHAR2 := NULL,
767 p_customer_name IN VARCHAR2 := NULL,
768 p_sub_type_element IN VARCHAR2 := NULL,
769 p_sub_type_element_value IN VARCHAR2 := NULL,
770 x_spec_id OUT NOCOPY NUMBER,
771 x_msg_count OUT NOCOPY NUMBER,
772 x_msg_data OUT NOCOPY VARCHAR2,
773 x_return_status OUT NOCOPY VARCHAR2,
774 -- Bug 7430441.FP for Bug 6877858.
775 -- Added the attribute parameters in order to populate the DFF
776 -- fields too into the qa_specs table
777 -- pdube Wed Sep 24 03:17:03 PDT 2008
778 p_attribute_category IN VARCHAR2 := NULL,
779 p_attribute1 IN VARCHAR2 := NULL,
780 p_attribute2 IN VARCHAR2 := NULL,
781 p_attribute3 IN VARCHAR2 := NULL,
782 p_attribute4 IN VARCHAR2 := NULL,
783 p_attribute5 IN VARCHAR2 := NULL,
784 p_attribute6 IN VARCHAR2 := NULL,
785 p_attribute7 IN VARCHAR2 := NULL,
786 p_attribute8 IN VARCHAR2 := NULL,
787 p_attribute9 IN VARCHAR2 := NULL,
788 p_attribute10 IN VARCHAR2 := NULL,
789 p_attribute11 IN VARCHAR2 := NULL,
790 p_attribute12 IN VARCHAR2 := NULL,
791 p_attribute13 IN VARCHAR2 := NULL,
792 p_attribute14 IN VARCHAR2 := NULL,
793 p_attribute15 IN VARCHAR2 := NULL,
794 --
795 -- bug 12538300
796 -- Added 2 new attributes to set the supplier and customer
797 -- uniquely using the ids, in case there are duplicate names
798 --
799 p_supplier_id IN NUMBER := NULL,
800 p_customer_id IN NUMBER := NULL) IS
801
802 l_api_name CONSTANT VARCHAR2(30) := 'create_specification_pub';
803 l_api_version CONSTANT NUMBER := 1.0;
804
805 l_user_id NUMBER;
806 l_org_id NUMBER := -1;
807 l_category_set_id NUMBER := -1;
808 l_category_id NUMBER := -1;
809 l_structure_id NUMBER;
810 l_validate_flag mtl_category_sets.validate_flag%TYPE;
811 l_item_id NUMBER := -1;
812 l_vendor_id NUMBER := -1;
813 l_customer_id NUMBER := -1;
814 l_char_id NUMBER := -1;
815 l_sub_type_value qa_specs.spec_element_value%TYPE;
816 l_existing_spec_name VARCHAR2(30);
817 l_datatype NUMBER;
818
819 l_reference_spec_id NUMBER;
820
821 --BUG 3500244
822 --we do NOT support ERES in APIs as documented in QA FDA HLD
823 --so this should behave as though ERES profile is No
824 --which means spec_status of 40 which is No Approval Reqd
825 --should be used for spec creation since it is a NON-Null Column
826 l_spec_status NUMBER := 40;
827
828
829 CURSOR c IS
830 SELECT spec_id
831 FROM qa_specs
832 WHERE spec_name = p_reference_spec;
833
834
835 -- bug 12538300
836 l_validate_vendor NUMBER := 0;
837 l_validate_customer NUMBER := 0;
838
839 BEGIN
840
841 -- Standard Start of API savepoint
842
843 SAVEPOINT create_specification_pub;
844
845 -- Standard call to check for call compatibility.
846 IF NOT fnd_api.compatible_api_call (
847 l_api_version,
848 p_api_version,
849 l_api_name,
850 g_pkg_name) THEN
851 RAISE fnd_api.g_exc_unexpected_error;
852 END IF;
853
854 -- Initialize message list if p_init_msg_list is set to TRUE.
855 IF fnd_api.to_boolean(p_init_msg_list) THEN
856 fnd_msg_pub.initialize;
857 END IF;
858
859
860 -- Initialize API return status to success
861 x_return_status := fnd_api.g_ret_sts_success;
862
863 l_user_id := get_user_id(p_user_name);
864 IF l_user_id = -1 THEN
865 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
866 fnd_msg_pub.add();
867 raise fnd_api.g_exc_error;
868 END IF;
869
870 -- Algorithm
871 --
872 --
873 -- 0. Check for duplicate name
874 --
875 -- 0.5 If the reference_spec is passed, verify if that spec exists and is not a
876 -- referencing any other spec.
877 --
878 -- 1. convert org code to org id
879 --
880 -- 2. validate the effective FROM and TO dates
881 --
882 -- 3.
883 -- if assignment type is supplier then
884 -- validate vendor and generate id
885 -- else if assignment type is customer then
886 -- validate customer and generate id
887 -- end if;
888 --
889 -- 4.
890 -- if item is given then
891 -- validate item and generate id
892 -- validate revision if provided
893 -- else if category set name is given then
894 -- validate category set name and generate id
895 -- else
896 -- generate an error
897 -- end if;
898 --
899 -- 5. if there is spec sub type provided (element) then
900 -- convert it into id
901 --
902 -- 6. Check for existing combination.
903 --
904 -- 7. generate spec id
905 --
906 -- 8. insert the row
907
908 IF (spec_exists(p_spec_name)) THEN
909 fnd_message.set_name('QA', 'QA_API_DUPLICATE_SPEC_NAME');
910 fnd_msg_pub.add();
911 RAISE fnd_api.g_exc_error;
912 END IF;
913
914 -- See if the spec exists with p_common_spec_name
915 IF p_reference_spec IS NOT NULL AND NOT spec_exists(p_reference_spec) THEN
916 fnd_message.set_name('QA', 'QA_SPEC_NOT_EXISTS');
917 fnd_message.set_token('ENTITY1', p_reference_spec);
918 fnd_msg_pub.add();
919 RAISE fnd_api.g_exc_error;
920 END IF;
921
922 -- Force not to reference a child spec if common_spec_name is passed
923 IF p_reference_spec IS NOT NULL AND is_child_spec(p_reference_spec) THEN
924 fnd_message.set_name('QA', 'QA_CANNOT_REFER_CHILD_SPEC');
925 fnd_msg_pub.add();
926 RAISE fnd_api.g_exc_error;
927 END IF;
928
929 l_org_id := qa_plans_api.get_org_id(p_organization_code);
930
931 IF (l_org_id IS NULL) THEN
932 fnd_message.set_name('QA', 'QA_API_INVALID_ORG_CODE');
933 fnd_msg_pub.add();
934 RAISE fnd_api.g_exc_error;
935 END IF;
936
937 IF (p_effective_to IS NOT NULL) THEN
938 IF (p_effective_to < p_effective_from) THEN
939 fnd_message.set_name('QA', 'QA_EFFECTIVE_DATE_RANGE');
940 fnd_msg_pub.add();
941 RAISE fnd_api.g_exc_error;
942 END IF;
943 END IF;
944
945 IF (p_assignment_type = g_spec_type_item) THEN
946 NULL; -- nothing special needs to be done for item spec.
947
948 ELSIF (p_assignment_type = g_spec_type_supplier) THEN
949 --
950 -- bug 12538300
951 -- If the supplier id is passed then it should take precedence
952 --
953 IF (p_supplier_id IS NULL) THEN
954 l_vendor_id := qa_plan_element_api.get_supplier_id(p_supplier_name);
955 ELSE
956 -- Validate vendor
957 SELECT count(*) into l_validate_vendor
958 FROM po_vendors
959 WHERE vendor_id = p_supplier_id
960 AND nvl(end_date_active, sysdate + 1) > sysdate;
961
962 IF l_validate_vendor = 0 THEN
963 l_vendor_id := NULL;
964 ELSE
965 l_vendor_id := p_supplier_id;
966 END IF;
967 END IF;
968
969 IF (l_vendor_id IS NULL) THEN
970 fnd_message.set_name('QA', 'QA_API_INVALID_VENDOR_NAME');
971 fnd_msg_pub.add();
972 RAISE fnd_api.g_exc_error;
973 END IF;
974
975 ELSIF (p_assignment_type = g_spec_type_customer) THEN
976 --
977 -- bug 12538300
978 -- If the customer id is passed then it should take precedence
979 --
980 IF (p_customer_id IS NULL) THEN
981 l_customer_id := qa_plan_element_api.get_customer_id(p_customer_name);
982 ELSE
983 -- Validate customer
984 SELECT count(*) into l_validate_customer
985 FROM qa_customers_lov_v
986 WHERE customer_id = p_customer_id
987 AND status = 'A'
988 AND nvl(customer_prospect_code, 'CUSTOMER') = 'CUSTOMER';
989
990 IF l_validate_customer = 0 THEN
991 l_customer_id := NULL;
992 ELSE
993 l_customer_id := p_customer_id;
994 END IF;
995
996 END IF;
997
998 IF (l_customer_id IS NULL) THEN
999 fnd_message.set_name('QA', 'QA_API_INVALID_CUSTOMER_NAME');
1000 fnd_msg_pub.add();
1001 RAISE fnd_api.g_exc_error;
1002 END IF;
1003
1004 ELSE
1005 fnd_message.set_name('QA', 'QA_API_INVALID_ASSIGNMENT_TYPE');
1006 fnd_msg_pub.add();
1007 RAISE fnd_api.g_exc_error;
1008 END IF;
1009
1010 IF (p_item_name IS NOT NULL) THEN
1011 l_item_id := process_item_and_revision(p_item_name, p_item_revision,
1012 l_org_id);
1013 ELSE
1014 l_category_set_id := get_category_set_id(
1015 p_category_set_name, l_structure_id, l_validate_flag);
1016 l_category_id := get_category_id(p_category_name,
1017 l_category_set_id, l_org_id, l_structure_id, l_validate_flag);
1018 END IF;
1019
1020 IF (p_sub_type_element IS NOT NULL) THEN
1021 l_sub_type_value := p_sub_type_element_value;
1022 l_char_id := qa_chars_api.get_element_id(p_sub_type_element);
1023 /* Fix for bug 3216242 - Check to ensure sub_type_element is a valid collection element.Hence, l_char_id should be not NULL for valid case.Added the following IF-ELSE condition.
1024 */
1025 IF (l_char_id IS NOT NULL) THEN
1026 l_datatype := qa_chars_api.datatype(l_char_id);
1027 validate_datatype(l_sub_type_value, l_datatype);
1028 ELSE
1029 fnd_message.set_name('QA', 'QA_API_INVALID_ELEMENT');
1030 fnd_msg_pub.add();
1031 RAISE fnd_api.g_exc_error;
1032 END IF;
1033 ELSE
1034 l_char_id := -1;
1035 l_sub_type_value := '-1';
1036 END IF;
1037
1038 IF (combination_exists(l_category_set_id, l_category_id, l_item_id,
1039 p_item_revision, l_org_id, l_vendor_id, l_customer_id, l_char_id,
1040 l_sub_type_value, l_existing_spec_name)) THEN
1041
1042 fnd_message.set_name('QA', 'QA_SPEC_COMBINATION_EXISTS');
1043 fnd_message.set_token('ENTITY1', l_existing_spec_name);
1044 fnd_msg_pub.add();
1045 RAISE fnd_api.g_exc_error;
1046 END IF;
1047
1048 SELECT qa_specs_s.nextval INTO x_spec_id FROM DUAL;
1049
1050 IF p_reference_spec IS NOT NULL THEN
1051 OPEN c;
1052 FETCH c INTO l_reference_spec_id;
1053 CLOSE c;
1054 ELSE
1055 l_reference_spec_id := x_spec_id;
1056 END IF;
1057
1058 INSERT INTO qa_specs(
1059 spec_id,
1060 last_update_date,
1061 last_updated_by,
1062 creation_date,
1063 created_by,
1064 last_update_login,
1065 spec_name,
1066 organization_id,
1067 effective_from,
1068 effective_to,
1069 common_spec_id,
1070 assignment_type,
1071 category_set_id,
1072 category_id,
1073 item_id,
1074 item_revision,
1075 vendor_id,
1076 customer_id,
1077 hide_plan_chars_flag,
1078 char_id,
1079 spec_element_value,
1080 spec_status, --Bug 3500244
1081 attribute_category, -- Bug 7430441
1082 attribute1,
1083 attribute2,
1084 attribute3,
1085 attribute4,
1086 attribute5,
1087 attribute6,
1088 attribute7,
1089 attribute8,
1090 attribute9,
1091 attribute10,
1092 attribute11,
1093 attribute12,
1094 attribute13,
1095 attribute14,
1096 attribute15 )
1097 VALUES(
1098 x_spec_id,
1099 sysdate,
1100 l_user_id,
1101 sysdate,
1102 l_user_id,
1103 l_user_id,
1104 p_spec_name,
1105 l_org_id,
1106 p_effective_from,
1107 p_effective_to,
1108 l_reference_spec_id,
1109 p_assignment_type,
1110 l_category_set_id,
1111 l_category_id,
1112 l_item_id,
1113 nvl(p_item_revision, 'NONE'),
1114 l_vendor_id,
1115 l_customer_id,
1116 1,
1117 l_char_id,
1118 l_sub_type_value,
1119 l_spec_status, --Bug 3500244
1120 p_attribute_category , -- Bug 7430441
1121 p_attribute1 ,
1122 p_attribute2 ,
1123 p_attribute3 ,
1124 p_attribute4 ,
1125 p_attribute5 ,
1126 p_attribute6 ,
1127 p_attribute7 ,
1128 p_attribute8 ,
1129 p_attribute9 ,
1130 p_attribute10,
1131 p_attribute11,
1132 p_attribute12,
1133 p_attribute13,
1134 p_attribute14,
1135 p_attribute15 );
1136
1137 EXCEPTION
1138
1139 WHEN fnd_api.g_exc_error THEN
1140 ROLLBACK TO create_specification_pub;
1141 x_return_status := fnd_api.g_ret_sts_error;
1142 fnd_msg_pub.count_and_get(
1143 p_count => x_msg_count,
1144 p_data => x_msg_data
1145 );
1146
1147 WHEN fnd_api.g_exc_unexpected_error THEN
1148 ROLLBACK TO create_specification_pub;
1149 x_return_status := fnd_api.g_ret_sts_unexp_error;
1150 fnd_msg_pub.count_and_get(
1151 p_count => x_msg_count,
1152 p_data => x_msg_data
1153 );
1154
1155 WHEN OTHERS THEN
1156 -- dbms_output.put_line(SQLCODE || SQLERRM);
1157 ROLLBACK TO create_specification_pub;
1158 x_return_status := fnd_api.g_ret_sts_unexp_error;
1159 IF fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1160 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
1161 END IF;
1162 fnd_msg_pub.count_and_get(
1163 p_count => x_msg_count,
1164 p_data => x_msg_data
1165 );
1166
1167 END create_specification;
1168
1169 --
1170 -- Adding spec elements is not allowed for child specs
1171 -- We would throw the same error if attempted.
1172 --
1173 -- rkunchal
1174 --
1175
1176 PROCEDURE add_spec_element(
1177 p_api_version IN NUMBER,
1178 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1179 p_validation_level IN NUMBER := fnd_api.G_VALID_LEVEL_FULL,
1180 p_user_name IN VARCHAR2 := NULL,
1181 p_spec_name IN VARCHAR2,
1182 p_organization_code IN VARCHAR2,
1183 p_element_name IN VARCHAR2,
1184 p_uom_code IN VARCHAR2 := NULL,
1185 p_enabled_flag IN VARCHAR2 := fnd_api.g_true,
1186 p_target_value IN VARCHAR2 := NULL,
1187 p_upper_spec_limit IN VARCHAR2 := NULL,
1188 p_lower_spec_limit IN VARCHAR2 := NULL,
1189 p_upper_reasonable_limit IN VARCHAR2 := NULL,
1190 p_lower_reasonable_limit IN VARCHAR2 := NULL,
1191 p_upper_user_defined_limit IN VARCHAR2 := NULL,
1192 p_lower_user_defined_limit IN VARCHAR2 := NULL,
1193 x_msg_count OUT NOCOPY NUMBER,
1194 x_msg_data OUT NOCOPY VARCHAR2,
1195 x_return_status OUT NOCOPY VARCHAR2,
1196 -- 7430441.FP for Bug 7046198
1197 -- Added the attribute parameters in order to populate the DFF
1198 -- fields too into the qa_spec_chars table
1199 -- pdube Wed Sep 24 03:17:03 PDT 2008
1200 p_attribute_category IN VARCHAR2 := NULL,
1201 p_attribute1 IN VARCHAR2 := NULL,
1202 p_attribute2 IN VARCHAR2 := NULL,
1203 p_attribute3 IN VARCHAR2 := NULL,
1204 p_attribute4 IN VARCHAR2 := NULL,
1205 p_attribute5 IN VARCHAR2 := NULL,
1206 p_attribute6 IN VARCHAR2 := NULL,
1207 p_attribute7 IN VARCHAR2 := NULL,
1208 p_attribute8 IN VARCHAR2 := NULL,
1209 p_attribute9 IN VARCHAR2 := NULL,
1210 p_attribute10 IN VARCHAR2 := NULL,
1211 p_attribute11 IN VARCHAR2 := NULL,
1212 p_attribute12 IN VARCHAR2 := NULL,
1213 p_attribute13 IN VARCHAR2 := NULL,
1214 p_attribute14 IN VARCHAR2 := NULL,
1215 p_attribute15 IN VARCHAR2 := NULL ) IS
1216
1217
1218 l_api_name CONSTANT VARCHAR2(30) := 'add_spec_element';
1219 l_api_version CONSTANT NUMBER := 1.0;
1220
1221 l_user_id NUMBER;
1222 l_enabled_flag NUMBER;
1223 l_char_id NUMBER;
1224 l_spec_id NUMBER;
1225 l_datatype NUMBER;
1226
1227 BEGIN
1228
1229 -- Standard Start of API savepoint
1230
1231 SAVEPOINT add_spec_element;
1232
1233 -- Standard call to check for call compatibility.
1234 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
1235 l_api_name, g_pkg_name) THEN
1236 RAISE fnd_api.g_exc_unexpected_error;
1237 END IF;
1238
1239 -- Initialize message list if p_init_msg_list is set to TRUE.
1240 IF fnd_api.to_boolean(p_init_msg_list) THEN
1241 fnd_msg_pub.initialize;
1242 END IF;
1243
1244 -- Initialize API return status to success
1245 x_return_status := fnd_api.g_ret_sts_success;
1246
1247 l_user_id := get_user_id(p_user_name);
1248 IF l_user_id = -1 THEN
1249 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
1250 fnd_msg_pub.add();
1251 raise fnd_api.g_exc_error;
1252 END IF;
1253
1254 l_char_id := get_context_element_id(p_element_name);
1255 l_spec_id := get_spec_id(p_spec_name, p_organization_code);
1256
1257 -- See if the spec is a child and throw exception appropriately
1258 IF is_child_spec(l_spec_id) THEN
1259 fnd_message.set_name('QA', 'QA_SPEC_ELEM_TO_CHILD');
1260 fnd_msg_pub.add();
1261 raise fnd_api.g_exc_error;
1262 END IF;
1263
1264 IF (spec_element_exists(l_spec_id, l_char_id)) THEN
1265 fnd_message.set_name ('QA', 'QA_API_DUPLICATE_SPEC_ELEMENT');
1266 fnd_msg_pub.add();
1267 RAISE fnd_api.g_exc_error;
1268 END IF;
1269
1270 l_datatype := qa_chars_api.datatype(l_char_id);
1271
1272 validate_datatype(p_target_value, l_datatype);
1273 validate_datatype(p_upper_spec_limit, l_datatype);
1274 validate_datatype(p_lower_spec_limit, l_datatype);
1275 validate_datatype(p_upper_reasonable_limit, l_datatype);
1276 validate_datatype(p_lower_reasonable_limit, l_datatype);
1277 validate_datatype(p_upper_user_defined_limit, l_datatype);
1278 validate_datatype(p_lower_user_defined_limit, l_datatype);
1279
1280 IF qltcompb.compare(p_upper_spec_limit, 6, p_lower_spec_limit, null,
1281 l_datatype) THEN
1282 fnd_message.set_name('QA', 'QA_LSL_GREATER_THAN_USL');
1283 fnd_msg_pub.add();
1284 RAISE fnd_api.g_exc_error;
1285 END IF;
1286
1287 IF qltcompb.compare(p_upper_reasonable_limit, 6, p_lower_reasonable_limit,
1288 null, l_datatype) THEN
1289 fnd_message.set_name('QA', 'QA_LRL_GREATER_THAN_URL');
1290 fnd_msg_pub.add();
1291 RAISE fnd_api.g_exc_error;
1292 END IF;
1293
1294 IF qltcompb.compare(p_upper_user_defined_limit, 6,
1295 p_lower_user_defined_limit, null, l_datatype) THEN
1296 fnd_message.set_name('QA', 'QA_LUL_GREATER_THAN_UUL');
1297 fnd_msg_pub.add();
1298 RAISE fnd_api.g_exc_error;
1299 END IF;
1300
1301 l_enabled_flag := convert_flag(p_enabled_flag);
1302
1303 -- The values getting inserted in to qa_spec_chars in the following insert
1304 -- statement were reversed.Changed the order of parameters for
1305 -- upper_reasonable_limit,lower_reasonable_limit,upper_user_defined_limit,
1306 -- lower_user_defined_limit.
1307 -- Bug 2715786.suramasw Wed Dec 18 23:08:20 PST 2002.
1308
1309 INSERT INTO qa_spec_chars(
1310 spec_id,
1311 char_id,
1312 last_update_date,
1313 last_updated_by,
1314 creation_date,
1315 created_by,
1316 last_update_login,
1317 enabled_flag,
1318 target_value,
1319 upper_spec_limit,
1320 lower_spec_limit,
1321 upper_reasonable_limit,
1322 lower_reasonable_limit,
1323 upper_user_defined_limit,
1324 lower_user_defined_limit,
1325 uom_code,
1326 attribute_category, -- Bug 7430441
1327 attribute1,
1328 attribute2,
1329 attribute3,
1330 attribute4,
1331 attribute5,
1332 attribute6,
1333 attribute7,
1334 attribute8,
1335 attribute9,
1336 attribute10,
1337 attribute11,
1338 attribute12,
1339 attribute13,
1340 attribute14,
1341 attribute15 )
1342 VALUES(
1343 l_spec_id,
1344 l_char_id,
1345 sysdate,
1346 l_user_id,
1347 sysdate,
1348 l_user_id,
1349 l_user_id,
1350 l_enabled_flag,
1351 p_target_value,
1352 p_upper_spec_limit,
1353 p_lower_spec_limit,
1354 p_upper_reasonable_limit,
1355 p_lower_reasonable_limit,
1356 p_upper_user_defined_limit,
1357 p_lower_user_defined_limit,
1358 p_uom_code,
1359 p_attribute_category , -- Bug 7430441
1360 p_attribute1 ,
1361 p_attribute2 ,
1362 p_attribute3 ,
1363 p_attribute4 ,
1364 p_attribute5 ,
1365 p_attribute6 ,
1366 p_attribute7 ,
1367 p_attribute8 ,
1368 p_attribute9 ,
1369 p_attribute10,
1370 p_attribute11,
1371 p_attribute12,
1372 p_attribute13,
1373 p_attribute14,
1374 p_attribute15 );
1375
1376 EXCEPTION
1377
1378 WHEN fnd_api.g_exc_error THEN
1379 ROLLBACK TO add_spec_element;
1380 x_return_status := fnd_api.g_ret_sts_error;
1381 fnd_msg_pub.count_and_get(
1382 p_count => x_msg_count,
1383 p_data => x_msg_data
1384 );
1385
1386 WHEN fnd_api.g_exc_unexpected_error THEN
1387 ROLLBACK TO add_spec_element;
1388 x_return_status := fnd_api.g_ret_sts_unexp_error;
1389 fnd_msg_pub.count_and_get(
1390 p_count => x_msg_count,
1391 p_data => x_msg_data
1392 );
1393
1394 WHEN OTHERS THEN
1395 ROLLBACK TO add_spec_element;
1396 x_return_status := fnd_api.g_ret_sts_unexp_error;
1397 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1398 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1399 END IF;
1400 fnd_msg_pub.count_and_get(
1401 p_count => x_msg_count,
1402 p_data => x_msg_data
1403 );
1404
1405 END add_spec_element;
1406
1407
1408 PROCEDURE complete_spec_processing(
1409 p_api_version IN NUMBER,
1410 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1411 p_user_name IN VARCHAR2 := NULL,
1412 p_spec_name IN VARCHAR2,
1413 p_organization_code IN VARCHAR2,
1414 p_commit IN VARCHAR2 := fnd_api.g_false,
1415 x_msg_count OUT NOCOPY NUMBER,
1416 x_msg_data OUT NOCOPY VARCHAR2,
1417 x_return_status OUT NOCOPY VARCHAR2) IS
1418
1419 l_api_name CONSTANT VARCHAR2(30):= 'complete_spec_definition';
1420 l_api_version CONSTANT NUMBER := 1.0;
1421
1422 l_user_id NUMBER;
1423 l_spec_id NUMBER;
1424
1425 BEGIN
1426
1427 -- Standard Start of API savepoint
1428
1429 SAVEPOINT complete_spec_definition;
1430
1431 -- Standard call to check for call compatibility.
1432 IF NOT fnd_api.compatible_api_call(
1433 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1434 RAISE fnd_api.g_exc_unexpected_error;
1435 END IF;
1436
1437 -- Initialize message list if p_init_msg_list is set to TRUE.
1438 IF fnd_api.to_boolean(p_init_msg_list) THEN
1439 fnd_msg_pub.initialize;
1440 END IF;
1441
1442 -- Initialize API return status to success
1443 x_return_status := fnd_api.g_ret_sts_success;
1444
1445 l_user_id := get_user_id(p_user_name);
1446 IF l_user_id = -1 THEN
1447 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
1448 fnd_msg_pub.add();
1449 raise fnd_api.g_exc_error;
1450 END IF;
1451
1452 l_spec_id := get_spec_id(p_spec_name, p_organization_code);
1453 check_for_spec_element(l_spec_id);
1454
1455 IF fnd_api.to_boolean(p_commit) THEN
1456 COMMIT;
1457 END IF;
1458
1459 EXCEPTION
1460
1461 WHEN fnd_api.g_exc_error THEN
1462 ROLLBACK TO complete_spec_definition;
1463 x_return_status := fnd_api.g_ret_sts_error;
1464 fnd_msg_pub.count_and_get(
1465 p_count => x_msg_count,
1466 p_data => x_msg_data
1467 );
1468
1469 WHEN fnd_api.g_exc_unexpected_error THEN
1470 ROLLBACK TO complete_spec_definition;
1471 x_return_status := fnd_api.g_ret_sts_unexp_error;
1472 fnd_msg_pub.count_and_get(
1473 p_count => x_msg_count,
1474 p_data => x_msg_data
1475 );
1476
1477 WHEN OTHERS THEN
1478 ROLLBACK TO complete_spec_definition;
1479 x_return_status := fnd_api.g_ret_sts_unexp_error;
1480 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1481 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1482 END IF;
1483 fnd_msg_pub.count_and_get(
1484 p_count => x_msg_count,
1485 p_data => x_msg_data
1486 );
1487
1488 END complete_spec_processing;
1489
1490
1491 PROCEDURE delete_spec_private(p_spec_id IN NUMBER) IS
1492 --
1493 -- The real work of deleting a specification and all its
1494 -- spec elements. Do not commit.
1495 --
1496 BEGIN
1497
1498 DELETE
1499 FROM qa_spec_chars
1500 WHERE spec_id = p_spec_id;
1501
1502 DELETE
1503 FROM qa_specs
1504 WHERE spec_id = p_spec_id;
1505
1506 END delete_spec_private;
1507
1508 PROCEDURE delete_spec_element_private(
1509 p_spec_id IN NUMBER,
1510 p_element_id IN NUMBER) IS
1511
1512 BEGIN
1513
1514 DELETE
1515 FROM qa_spec_chars
1516 WHERE spec_id = p_spec_id
1517 AND char_id = p_element_id;
1518
1519 END delete_spec_element_private;
1520
1521
1522 --
1523 -- Should not allow deletion if there exists
1524 -- at least one child spec. Use child_spec_exists
1525 -- rkunchal
1526 --
1527
1528 PROCEDURE delete_specification(
1529 p_api_version IN NUMBER,
1530 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1531 p_user_name IN VARCHAR2 := NULL,
1532 p_spec_name IN VARCHAR2,
1533 p_organization_code IN VARCHAR2,
1534 p_commit IN VARCHAR2 := fnd_api.g_false,
1535 x_msg_count OUT NOCOPY NUMBER,
1536 x_msg_data OUT NOCOPY VARCHAR2,
1537 x_return_status OUT NOCOPY VARCHAR2) IS
1538
1539 l_api_name CONSTANT VARCHAR2(30):= 'delete_specification';
1540 l_api_version CONSTANT NUMBER := 1.0;
1541
1542 l_user_id NUMBER;
1543 l_spec_id NUMBER;
1544 l_org_id NUMBER;
1545
1546 BEGIN
1547
1548 -- Standard Start of API savepoint
1549
1550 SAVEPOINT delete_specification;
1551
1552 -- Standard call to check for call compatibility.
1553 IF NOT fnd_api.compatible_api_call(
1554 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1555 RAISE fnd_api.g_exc_unexpected_error;
1556 END IF;
1557
1558 -- Initialize message list if p_init_msg_list is set to TRUE.
1559 IF fnd_api.to_boolean(p_init_msg_list) THEN
1560 fnd_msg_pub.initialize;
1561 END IF;
1562
1563 -- Initialize API return status to success
1564 x_return_status := fnd_api.g_ret_sts_success;
1565
1566 l_user_id := get_user_id(p_user_name);
1567 IF l_user_id = -1 THEN
1568 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
1569 fnd_msg_pub.add();
1570 raise fnd_api.g_exc_error;
1571 END IF;
1572
1573 l_spec_id := get_spec_id(p_spec_name, p_organization_code);
1574
1575 -- Perfect place to check for child specs
1576 IF child_spec_exists(l_spec_id) THEN
1577 fnd_message.set_name('QA', 'QA_CHILD_SPECS_EXIST');
1578 fnd_msg_pub.add();
1579 raise fnd_api.g_exc_error;
1580 END IF;
1581
1582 delete_spec_private(l_spec_id);
1583
1584 IF fnd_api.to_boolean(p_commit) THEN
1585 COMMIT;
1586 END IF;
1587
1588 EXCEPTION
1589
1590 WHEN fnd_api.g_exc_error THEN
1591 ROLLBACK TO delete_specification;
1592 x_return_status := fnd_api.g_ret_sts_error;
1593 fnd_msg_pub.count_and_get(
1594 p_count => x_msg_count,
1595 p_data => x_msg_data
1596 );
1597
1598 WHEN fnd_api.g_exc_unexpected_error THEN
1599 ROLLBACK TO delete_specification;
1600 x_return_status := fnd_api.g_ret_sts_unexp_error;
1601 fnd_msg_pub.count_and_get(
1602 p_count => x_msg_count,
1603 p_data => x_msg_data
1604 );
1605
1606 WHEN OTHERS THEN
1607 ROLLBACK TO delete_specification;
1608 x_return_status := fnd_api.g_ret_sts_unexp_error;
1609 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1610 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
1611 END IF;
1612 fnd_msg_pub.count_and_get(
1613 p_count => x_msg_count,
1614 p_data => x_msg_data
1615 );
1616
1617 END delete_specification;
1618
1619 --
1620 -- Though this will not effectively do anything for child specs,
1621 -- we want to give an impression to caller that this operation
1622 -- on a child spec is not functionally valid and is discouraged.
1623 --
1624 -- rkunchal
1625 --
1626
1627 PROCEDURE delete_spec_element(
1628 p_api_version IN NUMBER,
1629 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1630 p_user_name IN VARCHAR2 := NULL,
1631 p_spec_name IN VARCHAR2,
1632 p_organization_code IN VARCHAR2,
1633 p_element_name IN VARCHAR2,
1634 p_commit IN VARCHAR2 := fnd_api.g_false,
1635 x_msg_count OUT NOCOPY NUMBER,
1636 x_msg_data OUT NOCOPY VARCHAR2,
1637 x_return_status OUT NOCOPY VARCHAR2) IS
1638
1639 l_api_name CONSTANT VARCHAR2(30):= 'delete_spec_element';
1640 l_api_version CONSTANT NUMBER := 1.0;
1641
1642 l_user_id NUMBER;
1643 l_spec_id NUMBER;
1644 l_char_id NUMBER;
1645 l_org_id NUMBER;
1646
1647 BEGIN
1648
1649 -- Standard Start of API savepoint
1650
1651 SAVEPOINT delete_spec_element;
1652
1653 -- Standard call to check for call compatibility.
1654 IF NOT fnd_api.compatible_api_call(
1655 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1656 RAISE fnd_api.g_exc_unexpected_error;
1657 END IF;
1658
1659 -- Initialize message list if p_init_msg_list is set to TRUE.
1660 IF fnd_api.to_boolean(p_init_msg_list) THEN
1661 fnd_msg_pub.initialize;
1662 END IF;
1663
1664 -- Initialize API return status to success
1665 x_return_status := fnd_api.g_ret_sts_success;
1666
1667 l_user_id := get_user_id(p_user_name);
1668 IF l_user_id = -1 THEN
1669 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
1670 fnd_msg_pub.add();
1671 raise fnd_api.g_exc_error;
1672 END IF;
1673
1674 l_spec_id := get_spec_id(p_spec_name, p_organization_code);
1675
1676 -- We must be checking here if the spec is a child
1677 IF is_child_spec(l_spec_id) THEN
1678 fnd_message.set_name('QA', 'QA_DELETE_SPEC_ELEM_ON_CHILD');
1679 fnd_msg_pub.add();
1680 raise fnd_api.g_exc_error;
1681 END IF;
1682
1683 l_char_id := get_context_element_id(p_element_name);
1684
1685 delete_spec_element_private(l_spec_id, l_char_id);
1686
1687 IF fnd_api.to_boolean(p_commit) THEN
1688 COMMIT;
1689 END IF;
1690
1691 EXCEPTION
1692
1693 WHEN fnd_api.g_exc_error THEN
1694 ROLLBACK TO delete_spec_element;
1695 x_return_status := fnd_api.g_ret_sts_error;
1696 fnd_msg_pub.count_and_get(
1697 p_count => x_msg_count,
1698 p_data => x_msg_data
1699 );
1700
1701 WHEN fnd_api.g_exc_unexpected_error THEN
1702 ROLLBACK TO delete_spec_element;
1703 x_return_status := fnd_api.g_ret_sts_unexp_error;
1704 fnd_msg_pub.count_and_get(
1705 p_count => x_msg_count,
1706 p_data => x_msg_data
1707 );
1708
1709 WHEN OTHERS THEN
1710 ROLLBACK TO delete_spec_element;
1711 x_return_status := fnd_api.g_ret_sts_unexp_error;
1712 IF fnd_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1713 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1714 END IF;
1715 fnd_msg_pub.count_and_get(
1716 p_count => x_msg_count,
1717 p_data => x_msg_data
1718 );
1719
1720 END delete_spec_element;
1721
1722 --
1723 -- We should not copy Spec Elements if the spec being copied is a child.
1724 -- The new, copied spec still references the same master spec.
1725 -- Copying child is seemless and caller will not see any message.
1726 --
1727 -- rkunchal
1728 --
1729
1730 PROCEDURE copy_specification(
1731 p_api_version IN NUMBER,
1732 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1733 p_user_name IN VARCHAR2 := NULL,
1734 p_spec_name IN VARCHAR2,
1735 p_organization_code IN VARCHAR2,
1736 p_to_spec_name IN VARCHAR2,
1737 p_to_organization_code IN VARCHAR2,
1738 p_to_item_name IN VARCHAR2,
1739 p_to_item_revision IN VARCHAR2 := NULL,
1740 p_commit IN VARCHAR2 := fnd_api.g_false,
1741 x_spec_id OUT NOCOPY NUMBER,
1742 x_msg_count OUT NOCOPY NUMBER,
1743 x_msg_data OUT NOCOPY VARCHAR2,
1744 x_return_status OUT NOCOPY VARCHAR2) IS
1745
1746 l_api_name CONSTANT VARCHAR2(30):= 'copy_specification';
1747 l_api_version CONSTANT NUMBER := 1.0;
1748
1749 l_user_id NUMBER;
1750 l_spec_id NUMBER;
1751 l_org_id NUMBER;
1752 l_spec_name NUMBER;
1753 l_to_org_id NUMBER;
1754 l_category_set_id NUMBER;
1755 l_category_id NUMBER;
1756 l_to_item_id NUMBER;
1757 l_to_item_revision NUMBER;
1758 l_vendor_id NUMBER;
1759 l_customer_id NUMBER;
1760 l_char_id NUMBER;
1761 l_spec_element_value VARCHAR2(150);
1762 l_existing_spec_name VARCHAR2(30);
1763
1764 --BUG 3500244
1765 --we do NOT support ERES in APIs as documented in QA FDA HLD
1766 --so this should behave as though ERES profile is No
1767 --which means spec_status of 40 which is No Approval Reqd
1768 --should be used for spec creation since it is a NON-Null Column
1769
1770 l_spec_status NUMBER := 40;
1771
1772 BEGIN
1773
1774 -- Standard Start of API savepoint
1775
1776 SAVEPOINT copy_specification;
1777
1778 -- Standard call to check for call compatibility.
1779 IF NOT fnd_api.compatible_api_call(
1780 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1781 RAISE fnd_api.g_exc_unexpected_error;
1782 END IF;
1783
1784 -- Initialize message list if p_init_msg_list is set to TRUE.
1785 IF fnd_api.to_boolean(p_init_msg_list) THEN
1786 fnd_msg_pub.initialize;
1787 END IF;
1788
1789 -- Initialize API return status to success
1790 x_return_status := fnd_api.g_ret_sts_success;
1791
1792 l_user_id := get_user_id(p_user_name);
1793 IF l_user_id = -1 THEN
1794 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
1795 fnd_msg_pub.add();
1796 raise fnd_api.g_exc_error;
1797 END IF;
1798
1799 l_spec_id := get_spec_id(p_spec_name, p_organization_code);
1800 l_org_id := organization_id(l_spec_id);
1801
1802 l_to_org_id := qa_plans_api.get_org_id(p_to_organization_code);
1803 IF (l_to_org_id IS NULL) THEN
1804 fnd_message.set_name('QA', 'QA_API_INVALID_ORG_CODE');
1805 fnd_msg_pub.add();
1806 RAISE fnd_api.g_exc_error;
1807 END IF;
1808
1809 IF (spec_exists(p_to_spec_name)) THEN
1810 fnd_message.set_name('QA', 'QA_API_DUPLICATE_SPEC_NAME');
1811 fnd_msg_pub.add();
1812 RAISE fnd_api.g_exc_error;
1813 END IF;
1814
1815 l_to_item_id := process_item_and_revision(p_to_item_name,
1816 p_to_item_revision, l_org_id);
1817
1818 l_category_set_id := category_set_id(l_spec_id);
1819 l_category_id := category_id(l_spec_id);
1820 l_vendor_id := vendor_id(l_spec_id);
1821 l_customer_id := customer_id(l_spec_id);
1822 l_char_id := char_id(l_spec_id);
1823 l_spec_element_value := spec_element_value(l_spec_id);
1824
1825 IF (combination_exists(l_category_set_id, l_category_id, l_to_item_id,
1826 p_to_item_revision, l_org_id, l_vendor_id, l_customer_id, l_char_id,
1827 l_spec_element_value, l_existing_spec_name)) THEN
1828
1829 fnd_message.set_name('QA', 'QA_SPEC_COMBINATION_EXISTS');
1830 fnd_message.set_token('ENTITY1', l_existing_spec_name);
1831 fnd_msg_pub.add();
1832 RAISE fnd_api.g_exc_error;
1833 END IF;
1834
1835 SELECT qa_specs_s.nextval INTO x_spec_id FROM DUAL;
1836
1837 INSERT INTO qa_specs(
1838 spec_id,
1839 last_update_date,
1840 last_updated_by,
1841 creation_date,
1842 created_by,
1843 last_update_login,
1844 spec_name,
1845 organization_id,
1846 effective_from,
1847 effective_to,
1848 common_spec_id,
1849 assignment_type,
1850 category_set_id,
1851 category_id,
1852 item_id,
1853 item_revision,
1854 vendor_id,
1855 customer_id,
1856 hide_plan_chars_flag,
1857 char_id,
1858 spec_element_value,
1859 spec_status ) --Bug 3500244
1860 SELECT
1861 x_spec_id,
1862 sysdate,
1863 l_user_id,
1864 sysdate,
1865 l_user_id,
1866 l_user_id,
1867 p_to_spec_name,
1868 l_to_org_id,
1869 effective_from,
1870 effective_to,
1871 common_spec_id,
1872 assignment_type,
1873 category_set_id,
1874 category_id,
1875 l_to_item_id,
1876 nvl(p_to_item_revision, 'NONE'),
1877 vendor_id,
1878 customer_id,
1879 hide_plan_chars_flag,
1880 char_id,
1881 spec_element_value,
1882 40 --Bug 3500244 see note below
1883 FROM qa_specs
1884 WHERE spec_id = l_spec_id;
1885
1886 --Bug 3500244
1887 --In above Select we have not used the variable l_spec_status
1888 --But purposely used the literal 40 so it is very clear for readability
1889 --that we are not selecting from database, but it is kind of a constant
1890
1891
1892 -- Prevent this insertion if the spec being copied is a child spec
1893 IF NOT is_child_spec(l_spec_id) THEN
1894 INSERT INTO qa_spec_chars(
1895 spec_id,
1896 char_id,
1897 last_update_date,
1898 last_updated_by,
1899 creation_date,
1900 created_by,
1901 last_update_login,
1902 enabled_flag,
1903 target_value,
1904 upper_spec_limit,
1905 lower_spec_limit,
1906 upper_reasonable_limit,
1907 lower_reasonable_limit,
1908 upper_user_defined_limit,
1909 lower_user_defined_limit)
1910 SELECT
1911 x_spec_id,
1912 char_id,
1913 sysdate,
1914 l_user_id,
1915 sysdate,
1916 l_user_id,
1917 l_user_id,
1918 enabled_flag,
1919 target_value,
1920 upper_spec_limit,
1921 lower_spec_limit,
1922 upper_user_defined_limit,
1923 lower_user_defined_limit,
1924 upper_reasonable_limit,
1925 lower_reasonable_limit
1926 FROM qa_spec_chars
1927 WHERE spec_id = l_spec_id;
1928 END IF;
1929
1930 --
1931 -- Bug 5231952. After copying, we should copy attachments.
1932 --
1933 qa_specs_pkg.copy_attachment(
1934 p_from_spec_id => l_spec_id,
1935 p_to_spec_id => x_spec_id);
1936
1937 IF fnd_api.to_boolean(p_commit) THEN
1938 COMMIT;
1939 END IF;
1940
1941 EXCEPTION
1942
1943 WHEN fnd_api.g_exc_error THEN
1944 ROLLBACK TO copy_specification;
1945 x_return_status := fnd_api.g_ret_sts_error;
1946 fnd_msg_pub.count_and_get(
1947 p_count => x_msg_count,
1948 p_data => x_msg_data
1949 );
1950
1951 WHEN fnd_api.g_exc_unexpected_error THEN
1952 ROLLBACK TO copy_specification;
1953 x_return_status := fnd_api.g_ret_sts_unexp_error;
1954 fnd_msg_pub.count_and_get(
1955 p_count => x_msg_count,
1956 p_data => x_msg_data
1957 );
1958
1959 WHEN OTHERS THEN
1960 ROLLBACK TO copy_specification;
1961 x_return_status := fnd_api.g_ret_sts_unexp_error;
1962 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1963 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
1964 END IF;
1965 fnd_msg_pub.count_and_get(
1966 p_count => x_msg_count,
1967 p_data => x_msg_data
1968 );
1969
1970 END copy_specification;
1971
1972
1973 END qa_specs_pub;
1974
[Home] [Help]