1 PACKAGE BODY qa_specs_pub AS
2 /* $Header: qltpspcb.plb 120.1.12010000.2 2008/09/24 11:29:02 pdube 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 ) IS
794
795 l_api_name CONSTANT VARCHAR2(30) := 'create_specification_pub';
796 l_api_version CONSTANT NUMBER := 1.0;
797
798 l_user_id NUMBER;
799 l_org_id NUMBER := -1;
800 l_category_set_id NUMBER := -1;
801 l_category_id NUMBER := -1;
802 l_structure_id NUMBER;
803 l_validate_flag mtl_category_sets.validate_flag%TYPE;
804 l_item_id NUMBER := -1;
805 l_vendor_id NUMBER := -1;
806 l_customer_id NUMBER := -1;
807 l_char_id NUMBER := -1;
808 l_sub_type_value qa_specs.spec_element_value%TYPE;
809 l_existing_spec_name VARCHAR2(30);
810 l_datatype NUMBER;
811
812 l_reference_spec_id NUMBER;
813
814 --BUG 3500244
815 --we do NOT support ERES in APIs as documented in QA FDA HLD
816 --so this should behave as though ERES profile is No
817 --which means spec_status of 40 which is No Approval Reqd
818 --should be used for spec creation since it is a NON-Null Column
819 l_spec_status NUMBER := 40;
820
821
822 CURSOR c IS
823 SELECT spec_id
824 FROM qa_specs
825 WHERE spec_name = p_reference_spec;
826
827 BEGIN
828
829 -- Standard Start of API savepoint
830
831 SAVEPOINT create_specification_pub;
832
833 -- Standard call to check for call compatibility.
834 IF NOT fnd_api.compatible_api_call (
835 l_api_version,
836 p_api_version,
837 l_api_name,
838 g_pkg_name) THEN
839 RAISE fnd_api.g_exc_unexpected_error;
840 END IF;
841
842 -- Initialize message list if p_init_msg_list is set to TRUE.
843 IF fnd_api.to_boolean(p_init_msg_list) THEN
844 fnd_msg_pub.initialize;
845 END IF;
846
847
848 -- Initialize API return status to success
849 x_return_status := fnd_api.g_ret_sts_success;
850
851 l_user_id := get_user_id(p_user_name);
852 IF l_user_id = -1 THEN
853 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
854 fnd_msg_pub.add();
855 raise fnd_api.g_exc_error;
856 END IF;
857
858 -- Algorithm
859 --
860 --
861 -- 0. Check for duplicate name
862 --
863 -- 0.5 If the reference_spec is passed, verify if that spec exists and is not a
864 -- referencing any other spec.
865 --
866 -- 1. convert org code to org id
867 --
868 -- 2. validate the effective FROM and TO dates
869 --
870 -- 3.
871 -- if assignment type is supplier then
872 -- validate vendor and generate id
873 -- else if assignment type is customer then
874 -- validate customer and generate id
875 -- end if;
876 --
877 -- 4.
878 -- if item is given then
879 -- validate item and generate id
880 -- validate revision if provided
881 -- else if category set name is given then
882 -- validate category set name and generate id
883 -- else
884 -- generate an error
885 -- end if;
886 --
887 -- 5. if there is spec sub type provided (element) then
888 -- convert it into id
889 --
890 -- 6. Check for existing combination.
891 --
892 -- 7. generate spec id
893 --
894 -- 8. insert the row
895
896 IF (spec_exists(p_spec_name)) THEN
897 fnd_message.set_name('QA', 'QA_API_DUPLICATE_SPEC_NAME');
898 fnd_msg_pub.add();
899 RAISE fnd_api.g_exc_error;
900 END IF;
901
902 -- See if the spec exists with p_common_spec_name
903 IF p_reference_spec IS NOT NULL AND NOT spec_exists(p_reference_spec) THEN
904 fnd_message.set_name('QA', 'QA_SPEC_NOT_EXISTS');
905 fnd_message.set_token('ENTITY1', p_reference_spec);
906 fnd_msg_pub.add();
907 RAISE fnd_api.g_exc_error;
908 END IF;
909
910 -- Force not to reference a child spec if common_spec_name is passed
911 IF p_reference_spec IS NOT NULL AND is_child_spec(p_reference_spec) THEN
912 fnd_message.set_name('QA', 'QA_CANNOT_REFER_CHILD_SPEC');
913 fnd_msg_pub.add();
914 RAISE fnd_api.g_exc_error;
915 END IF;
916
917 l_org_id := qa_plans_api.get_org_id(p_organization_code);
918
919 IF (l_org_id IS NULL) THEN
920 fnd_message.set_name('QA', 'QA_API_INVALID_ORG_CODE');
921 fnd_msg_pub.add();
922 RAISE fnd_api.g_exc_error;
923 END IF;
924
925 IF (p_effective_to IS NOT NULL) THEN
926 IF (p_effective_to < p_effective_from) THEN
927 fnd_message.set_name('QA', 'QA_EFFECTIVE_DATE_RANGE');
928 fnd_msg_pub.add();
929 RAISE fnd_api.g_exc_error;
930 END IF;
931 END IF;
932
933 IF (p_assignment_type = g_spec_type_item) THEN
934 NULL; -- nothing special needs to be done for item spec.
935
936 ELSIF (p_assignment_type = g_spec_type_supplier) THEN
937 l_vendor_id := qa_plan_element_api.get_supplier_id(p_supplier_name);
938 IF (l_vendor_id IS NULL) THEN
939 fnd_message.set_name('QA', 'QA_API_INVALID_VENDOR_NAME');
940 fnd_msg_pub.add();
941 RAISE fnd_api.g_exc_error;
942 END IF;
943
944 ELSIF (p_assignment_type = g_spec_type_customer) THEN
945 l_customer_id := qa_plan_element_api.get_customer_id(p_customer_name);
946 IF (l_customer_id IS NULL) THEN
947 fnd_message.set_name('QA', 'QA_API_INVALID_CUSTOMER_NAME');
948 fnd_msg_pub.add();
949 RAISE fnd_api.g_exc_error;
950 END IF;
951
952 ELSE
953 fnd_message.set_name('QA', 'QA_API_INVALID_ASSIGNMENT_TYPE');
954 fnd_msg_pub.add();
955 RAISE fnd_api.g_exc_error;
956 END IF;
957
958 IF (p_item_name IS NOT NULL) THEN
959 l_item_id := process_item_and_revision(p_item_name, p_item_revision,
960 l_org_id);
961 ELSE
962 l_category_set_id := get_category_set_id(
963 p_category_set_name, l_structure_id, l_validate_flag);
964 l_category_id := get_category_id(p_category_name,
965 l_category_set_id, l_org_id, l_structure_id, l_validate_flag);
966 END IF;
967
968 IF (p_sub_type_element IS NOT NULL) THEN
969 l_sub_type_value := p_sub_type_element_value;
970 l_char_id := qa_chars_api.get_element_id(p_sub_type_element);
971 /* 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.
972 */
973 IF (l_char_id IS NOT NULL) THEN
974 l_datatype := qa_chars_api.datatype(l_char_id);
975 validate_datatype(l_sub_type_value, l_datatype);
976 ELSE
977 fnd_message.set_name('QA', 'QA_API_INVALID_ELEMENT');
978 fnd_msg_pub.add();
979 RAISE fnd_api.g_exc_error;
980 END IF;
981 ELSE
982 l_char_id := -1;
983 l_sub_type_value := '-1';
984 END IF;
985
986 IF (combination_exists(l_category_set_id, l_category_id, l_item_id,
987 p_item_revision, l_org_id, l_vendor_id, l_customer_id, l_char_id,
988 l_sub_type_value, l_existing_spec_name)) THEN
989
990 fnd_message.set_name('QA', 'QA_SPEC_COMBINATION_EXISTS');
991 fnd_message.set_token('ENTITY1', l_existing_spec_name);
992 fnd_msg_pub.add();
993 RAISE fnd_api.g_exc_error;
994 END IF;
995
996 SELECT qa_specs_s.nextval INTO x_spec_id FROM DUAL;
997
998 IF p_reference_spec IS NOT NULL THEN
999 OPEN c;
1000 FETCH c INTO l_reference_spec_id;
1001 CLOSE c;
1002 ELSE
1003 l_reference_spec_id := x_spec_id;
1004 END IF;
1005
1006 INSERT INTO qa_specs(
1007 spec_id,
1008 last_update_date,
1009 last_updated_by,
1010 creation_date,
1011 created_by,
1012 last_update_login,
1013 spec_name,
1014 organization_id,
1015 effective_from,
1016 effective_to,
1017 common_spec_id,
1018 assignment_type,
1019 category_set_id,
1020 category_id,
1021 item_id,
1022 item_revision,
1023 vendor_id,
1024 customer_id,
1025 hide_plan_chars_flag,
1026 char_id,
1027 spec_element_value,
1028 spec_status, --Bug 3500244
1029 attribute_category, -- Bug 7430441
1030 attribute1,
1031 attribute2,
1032 attribute3,
1033 attribute4,
1034 attribute5,
1035 attribute6,
1036 attribute7,
1037 attribute8,
1038 attribute9,
1039 attribute10,
1040 attribute11,
1041 attribute12,
1042 attribute13,
1043 attribute14,
1044 attribute15 )
1045 VALUES(
1046 x_spec_id,
1047 sysdate,
1048 l_user_id,
1049 sysdate,
1050 l_user_id,
1051 l_user_id,
1052 p_spec_name,
1053 l_org_id,
1054 p_effective_from,
1055 p_effective_to,
1056 l_reference_spec_id,
1057 p_assignment_type,
1058 l_category_set_id,
1059 l_category_id,
1060 l_item_id,
1061 nvl(p_item_revision, 'NONE'),
1062 l_vendor_id,
1063 l_customer_id,
1064 1,
1065 l_char_id,
1066 l_sub_type_value,
1067 l_spec_status, --Bug 3500244
1068 p_attribute_category , -- Bug 7430441
1069 p_attribute1 ,
1070 p_attribute2 ,
1071 p_attribute3 ,
1072 p_attribute4 ,
1073 p_attribute5 ,
1074 p_attribute6 ,
1075 p_attribute7 ,
1076 p_attribute8 ,
1077 p_attribute9 ,
1078 p_attribute10,
1079 p_attribute11,
1080 p_attribute12,
1081 p_attribute13,
1082 p_attribute14,
1083 p_attribute15 );
1084
1085 EXCEPTION
1086
1087 WHEN fnd_api.g_exc_error THEN
1088 ROLLBACK TO create_specification_pub;
1089 x_return_status := fnd_api.g_ret_sts_error;
1090 fnd_msg_pub.count_and_get(
1091 p_count => x_msg_count,
1092 p_data => x_msg_data
1093 );
1094
1095 WHEN fnd_api.g_exc_unexpected_error THEN
1096 ROLLBACK TO create_specification_pub;
1097 x_return_status := fnd_api.g_ret_sts_unexp_error;
1098 fnd_msg_pub.count_and_get(
1099 p_count => x_msg_count,
1100 p_data => x_msg_data
1101 );
1102
1103 WHEN OTHERS THEN
1104 -- dbms_output.put_line(SQLCODE || SQLERRM);
1105 ROLLBACK TO create_specification_pub;
1106 x_return_status := fnd_api.g_ret_sts_unexp_error;
1107 IF fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1108 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
1109 END IF;
1110 fnd_msg_pub.count_and_get(
1111 p_count => x_msg_count,
1112 p_data => x_msg_data
1113 );
1114
1115 END create_specification;
1116
1117 --
1118 -- Adding spec elements is not allowed for child specs
1119 -- We would throw the same error if attempted.
1120 --
1121 -- rkunchal
1122 --
1123
1124 PROCEDURE add_spec_element(
1125 p_api_version IN NUMBER,
1126 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1127 p_validation_level IN NUMBER := fnd_api.G_VALID_LEVEL_FULL,
1128 p_user_name IN VARCHAR2 := NULL,
1129 p_spec_name IN VARCHAR2,
1130 p_organization_code IN VARCHAR2,
1131 p_element_name IN VARCHAR2,
1132 p_uom_code IN VARCHAR2 := NULL,
1133 p_enabled_flag IN VARCHAR2 := fnd_api.g_true,
1134 p_target_value IN VARCHAR2 := NULL,
1135 p_upper_spec_limit IN VARCHAR2 := NULL,
1136 p_lower_spec_limit IN VARCHAR2 := NULL,
1137 p_upper_reasonable_limit IN VARCHAR2 := NULL,
1138 p_lower_reasonable_limit IN VARCHAR2 := NULL,
1139 p_upper_user_defined_limit IN VARCHAR2 := NULL,
1140 p_lower_user_defined_limit IN VARCHAR2 := NULL,
1141 x_msg_count OUT NOCOPY NUMBER,
1142 x_msg_data OUT NOCOPY VARCHAR2,
1143 x_return_status OUT NOCOPY VARCHAR2,
1144 -- 7430441.FP for Bug 7046198
1145 -- Added the attribute parameters in order to populate the DFF
1146 -- fields too into the qa_spec_chars table
1147 -- pdube Wed Sep 24 03:17:03 PDT 2008
1148 p_attribute_category IN VARCHAR2 := NULL,
1149 p_attribute1 IN VARCHAR2 := NULL,
1150 p_attribute2 IN VARCHAR2 := NULL,
1151 p_attribute3 IN VARCHAR2 := NULL,
1152 p_attribute4 IN VARCHAR2 := NULL,
1153 p_attribute5 IN VARCHAR2 := NULL,
1154 p_attribute6 IN VARCHAR2 := NULL,
1155 p_attribute7 IN VARCHAR2 := NULL,
1156 p_attribute8 IN VARCHAR2 := NULL,
1157 p_attribute9 IN VARCHAR2 := NULL,
1158 p_attribute10 IN VARCHAR2 := NULL,
1159 p_attribute11 IN VARCHAR2 := NULL,
1160 p_attribute12 IN VARCHAR2 := NULL,
1161 p_attribute13 IN VARCHAR2 := NULL,
1162 p_attribute14 IN VARCHAR2 := NULL,
1163 p_attribute15 IN VARCHAR2 := NULL ) IS
1164
1165
1166 l_api_name CONSTANT VARCHAR2(30) := 'add_spec_element';
1167 l_api_version CONSTANT NUMBER := 1.0;
1168
1169 l_user_id NUMBER;
1170 l_enabled_flag NUMBER;
1171 l_char_id NUMBER;
1172 l_spec_id NUMBER;
1173 l_datatype NUMBER;
1174
1175 BEGIN
1176
1177 -- Standard Start of API savepoint
1178
1179 SAVEPOINT add_spec_element;
1180
1181 -- Standard call to check for call compatibility.
1182 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
1183 l_api_name, g_pkg_name) THEN
1184 RAISE fnd_api.g_exc_unexpected_error;
1185 END IF;
1186
1187 -- Initialize message list if p_init_msg_list is set to TRUE.
1188 IF fnd_api.to_boolean(p_init_msg_list) THEN
1189 fnd_msg_pub.initialize;
1190 END IF;
1191
1192 -- Initialize API return status to success
1193 x_return_status := fnd_api.g_ret_sts_success;
1194
1195 l_user_id := get_user_id(p_user_name);
1196 IF l_user_id = -1 THEN
1197 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
1198 fnd_msg_pub.add();
1199 raise fnd_api.g_exc_error;
1200 END IF;
1201
1202 l_char_id := get_context_element_id(p_element_name);
1203 l_spec_id := get_spec_id(p_spec_name, p_organization_code);
1204
1205 -- See if the spec is a child and throw exception appropriately
1206 IF is_child_spec(l_spec_id) THEN
1207 fnd_message.set_name('QA', 'QA_SPEC_ELEM_TO_CHILD');
1208 fnd_msg_pub.add();
1209 raise fnd_api.g_exc_error;
1210 END IF;
1211
1212 IF (spec_element_exists(l_spec_id, l_char_id)) THEN
1213 fnd_message.set_name ('QA', 'QA_API_DUPLICATE_SPEC_ELEMENT');
1214 fnd_msg_pub.add();
1215 RAISE fnd_api.g_exc_error;
1216 END IF;
1217
1218 l_datatype := qa_chars_api.datatype(l_char_id);
1219
1220 validate_datatype(p_target_value, l_datatype);
1221 validate_datatype(p_upper_spec_limit, l_datatype);
1222 validate_datatype(p_lower_spec_limit, l_datatype);
1223 validate_datatype(p_upper_reasonable_limit, l_datatype);
1224 validate_datatype(p_lower_reasonable_limit, l_datatype);
1225 validate_datatype(p_upper_user_defined_limit, l_datatype);
1226 validate_datatype(p_lower_user_defined_limit, l_datatype);
1227
1228 IF qltcompb.compare(p_upper_spec_limit, 6, p_lower_spec_limit, null,
1229 l_datatype) THEN
1230 fnd_message.set_name('QA', 'QA_LSL_GREATER_THAN_USL');
1231 fnd_msg_pub.add();
1232 RAISE fnd_api.g_exc_error;
1233 END IF;
1234
1235 IF qltcompb.compare(p_upper_reasonable_limit, 6, p_lower_reasonable_limit,
1236 null, l_datatype) THEN
1237 fnd_message.set_name('QA', 'QA_LRL_GREATER_THAN_URL');
1238 fnd_msg_pub.add();
1239 RAISE fnd_api.g_exc_error;
1240 END IF;
1241
1242 IF qltcompb.compare(p_upper_user_defined_limit, 6,
1243 p_lower_user_defined_limit, null, l_datatype) THEN
1244 fnd_message.set_name('QA', 'QA_LUL_GREATER_THAN_UUL');
1245 fnd_msg_pub.add();
1246 RAISE fnd_api.g_exc_error;
1247 END IF;
1248
1249 l_enabled_flag := convert_flag(p_enabled_flag);
1250
1251 -- The values getting inserted in to qa_spec_chars in the following insert
1252 -- statement were reversed.Changed the order of parameters for
1253 -- upper_reasonable_limit,lower_reasonable_limit,upper_user_defined_limit,
1254 -- lower_user_defined_limit.
1255 -- Bug 2715786.suramasw Wed Dec 18 23:08:20 PST 2002.
1256
1257 INSERT INTO qa_spec_chars(
1258 spec_id,
1259 char_id,
1260 last_update_date,
1261 last_updated_by,
1262 creation_date,
1263 created_by,
1264 last_update_login,
1265 enabled_flag,
1266 target_value,
1267 upper_spec_limit,
1268 lower_spec_limit,
1269 upper_reasonable_limit,
1270 lower_reasonable_limit,
1271 upper_user_defined_limit,
1272 lower_user_defined_limit,
1273 uom_code,
1274 attribute_category, -- Bug 7430441
1275 attribute1,
1276 attribute2,
1277 attribute3,
1278 attribute4,
1279 attribute5,
1280 attribute6,
1281 attribute7,
1282 attribute8,
1283 attribute9,
1284 attribute10,
1285 attribute11,
1286 attribute12,
1287 attribute13,
1288 attribute14,
1289 attribute15 )
1290 VALUES(
1291 l_spec_id,
1292 l_char_id,
1293 sysdate,
1294 l_user_id,
1295 sysdate,
1296 l_user_id,
1297 l_user_id,
1298 l_enabled_flag,
1299 p_target_value,
1300 p_upper_spec_limit,
1301 p_lower_spec_limit,
1302 p_upper_reasonable_limit,
1303 p_lower_reasonable_limit,
1304 p_upper_user_defined_limit,
1305 p_lower_user_defined_limit,
1306 p_uom_code,
1307 p_attribute_category , -- Bug 7430441
1308 p_attribute1 ,
1309 p_attribute2 ,
1310 p_attribute3 ,
1311 p_attribute4 ,
1312 p_attribute5 ,
1313 p_attribute6 ,
1314 p_attribute7 ,
1315 p_attribute8 ,
1316 p_attribute9 ,
1317 p_attribute10,
1318 p_attribute11,
1319 p_attribute12,
1320 p_attribute13,
1321 p_attribute14,
1322 p_attribute15 );
1323
1324 EXCEPTION
1325
1326 WHEN fnd_api.g_exc_error THEN
1327 ROLLBACK TO add_spec_element;
1328 x_return_status := fnd_api.g_ret_sts_error;
1329 fnd_msg_pub.count_and_get(
1330 p_count => x_msg_count,
1331 p_data => x_msg_data
1332 );
1333
1334 WHEN fnd_api.g_exc_unexpected_error THEN
1335 ROLLBACK TO add_spec_element;
1336 x_return_status := fnd_api.g_ret_sts_unexp_error;
1337 fnd_msg_pub.count_and_get(
1338 p_count => x_msg_count,
1339 p_data => x_msg_data
1340 );
1341
1342 WHEN OTHERS THEN
1343 ROLLBACK TO add_spec_element;
1344 x_return_status := fnd_api.g_ret_sts_unexp_error;
1345 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1346 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1347 END IF;
1348 fnd_msg_pub.count_and_get(
1349 p_count => x_msg_count,
1350 p_data => x_msg_data
1351 );
1352
1353 END add_spec_element;
1354
1355
1356 PROCEDURE complete_spec_processing(
1357 p_api_version IN NUMBER,
1358 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1359 p_user_name IN VARCHAR2 := NULL,
1360 p_spec_name IN VARCHAR2,
1361 p_organization_code IN VARCHAR2,
1362 p_commit IN VARCHAR2 := fnd_api.g_false,
1363 x_msg_count OUT NOCOPY NUMBER,
1364 x_msg_data OUT NOCOPY VARCHAR2,
1365 x_return_status OUT NOCOPY VARCHAR2) IS
1366
1367 l_api_name CONSTANT VARCHAR2(30):= 'complete_spec_definition';
1368 l_api_version CONSTANT NUMBER := 1.0;
1369
1370 l_user_id NUMBER;
1371 l_spec_id NUMBER;
1372
1373 BEGIN
1374
1375 -- Standard Start of API savepoint
1376
1377 SAVEPOINT complete_spec_definition;
1378
1379 -- Standard call to check for call compatibility.
1380 IF NOT fnd_api.compatible_api_call(
1381 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1382 RAISE fnd_api.g_exc_unexpected_error;
1383 END IF;
1384
1385 -- Initialize message list if p_init_msg_list is set to TRUE.
1386 IF fnd_api.to_boolean(p_init_msg_list) THEN
1387 fnd_msg_pub.initialize;
1388 END IF;
1389
1390 -- Initialize API return status to success
1391 x_return_status := fnd_api.g_ret_sts_success;
1392
1393 l_user_id := get_user_id(p_user_name);
1394 IF l_user_id = -1 THEN
1395 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
1396 fnd_msg_pub.add();
1397 raise fnd_api.g_exc_error;
1398 END IF;
1399
1400 l_spec_id := get_spec_id(p_spec_name, p_organization_code);
1401 check_for_spec_element(l_spec_id);
1402
1403 IF fnd_api.to_boolean(p_commit) THEN
1404 COMMIT;
1405 END IF;
1406
1407 EXCEPTION
1408
1409 WHEN fnd_api.g_exc_error THEN
1410 ROLLBACK TO complete_spec_definition;
1411 x_return_status := fnd_api.g_ret_sts_error;
1412 fnd_msg_pub.count_and_get(
1413 p_count => x_msg_count,
1414 p_data => x_msg_data
1415 );
1416
1417 WHEN fnd_api.g_exc_unexpected_error THEN
1418 ROLLBACK TO complete_spec_definition;
1419 x_return_status := fnd_api.g_ret_sts_unexp_error;
1420 fnd_msg_pub.count_and_get(
1421 p_count => x_msg_count,
1422 p_data => x_msg_data
1423 );
1424
1425 WHEN OTHERS THEN
1426 ROLLBACK TO complete_spec_definition;
1427 x_return_status := fnd_api.g_ret_sts_unexp_error;
1428 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1429 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1430 END IF;
1431 fnd_msg_pub.count_and_get(
1432 p_count => x_msg_count,
1433 p_data => x_msg_data
1434 );
1435
1436 END complete_spec_processing;
1437
1438
1439 PROCEDURE delete_spec_private(p_spec_id IN NUMBER) IS
1440 --
1441 -- The real work of deleting a specification and all its
1442 -- spec elements. Do not commit.
1443 --
1444 BEGIN
1445
1446 DELETE
1447 FROM qa_spec_chars
1448 WHERE spec_id = p_spec_id;
1449
1450 DELETE
1451 FROM qa_specs
1452 WHERE spec_id = p_spec_id;
1453
1454 END delete_spec_private;
1455
1456 PROCEDURE delete_spec_element_private(
1457 p_spec_id IN NUMBER,
1458 p_element_id IN NUMBER) IS
1459
1460 BEGIN
1461
1462 DELETE
1463 FROM qa_spec_chars
1464 WHERE spec_id = p_spec_id
1465 AND char_id = p_element_id;
1466
1467 END delete_spec_element_private;
1468
1469
1470 --
1471 -- Should not allow deletion if there exists
1472 -- at least one child spec. Use child_spec_exists
1473 -- rkunchal
1474 --
1475
1476 PROCEDURE delete_specification(
1477 p_api_version IN NUMBER,
1478 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1479 p_user_name IN VARCHAR2 := NULL,
1480 p_spec_name IN VARCHAR2,
1481 p_organization_code IN VARCHAR2,
1482 p_commit IN VARCHAR2 := fnd_api.g_false,
1483 x_msg_count OUT NOCOPY NUMBER,
1484 x_msg_data OUT NOCOPY VARCHAR2,
1485 x_return_status OUT NOCOPY VARCHAR2) IS
1486
1487 l_api_name CONSTANT VARCHAR2(30):= 'delete_specification';
1488 l_api_version CONSTANT NUMBER := 1.0;
1489
1490 l_user_id NUMBER;
1491 l_spec_id NUMBER;
1492 l_org_id NUMBER;
1493
1494 BEGIN
1495
1496 -- Standard Start of API savepoint
1497
1498 SAVEPOINT delete_specification;
1499
1500 -- Standard call to check for call compatibility.
1501 IF NOT fnd_api.compatible_api_call(
1502 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1503 RAISE fnd_api.g_exc_unexpected_error;
1504 END IF;
1505
1506 -- Initialize message list if p_init_msg_list is set to TRUE.
1507 IF fnd_api.to_boolean(p_init_msg_list) THEN
1508 fnd_msg_pub.initialize;
1509 END IF;
1510
1511 -- Initialize API return status to success
1512 x_return_status := fnd_api.g_ret_sts_success;
1513
1514 l_user_id := get_user_id(p_user_name);
1515 IF l_user_id = -1 THEN
1516 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
1517 fnd_msg_pub.add();
1518 raise fnd_api.g_exc_error;
1519 END IF;
1520
1521 l_spec_id := get_spec_id(p_spec_name, p_organization_code);
1522
1523 -- Perfect place to check for child specs
1524 IF child_spec_exists(l_spec_id) THEN
1525 fnd_message.set_name('QA', 'QA_CHILD_SPECS_EXIST');
1526 fnd_msg_pub.add();
1527 raise fnd_api.g_exc_error;
1528 END IF;
1529
1530 delete_spec_private(l_spec_id);
1531
1532 IF fnd_api.to_boolean(p_commit) THEN
1533 COMMIT;
1534 END IF;
1535
1536 EXCEPTION
1537
1538 WHEN fnd_api.g_exc_error THEN
1539 ROLLBACK TO delete_specification;
1540 x_return_status := fnd_api.g_ret_sts_error;
1541 fnd_msg_pub.count_and_get(
1542 p_count => x_msg_count,
1543 p_data => x_msg_data
1544 );
1545
1546 WHEN fnd_api.g_exc_unexpected_error THEN
1547 ROLLBACK TO delete_specification;
1548 x_return_status := fnd_api.g_ret_sts_unexp_error;
1549 fnd_msg_pub.count_and_get(
1550 p_count => x_msg_count,
1551 p_data => x_msg_data
1552 );
1553
1554 WHEN OTHERS THEN
1555 ROLLBACK TO delete_specification;
1556 x_return_status := fnd_api.g_ret_sts_unexp_error;
1557 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1558 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
1559 END IF;
1560 fnd_msg_pub.count_and_get(
1561 p_count => x_msg_count,
1562 p_data => x_msg_data
1563 );
1564
1565 END delete_specification;
1566
1567 --
1568 -- Though this will not effectively do anything for child specs,
1569 -- we want to give an impression to caller that this operation
1570 -- on a child spec is not functionally valid and is discouraged.
1571 --
1572 -- rkunchal
1573 --
1574
1575 PROCEDURE delete_spec_element(
1576 p_api_version IN NUMBER,
1577 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1578 p_user_name IN VARCHAR2 := NULL,
1579 p_spec_name IN VARCHAR2,
1580 p_organization_code IN VARCHAR2,
1581 p_element_name IN VARCHAR2,
1582 p_commit IN VARCHAR2 := fnd_api.g_false,
1583 x_msg_count OUT NOCOPY NUMBER,
1584 x_msg_data OUT NOCOPY VARCHAR2,
1585 x_return_status OUT NOCOPY VARCHAR2) IS
1586
1587 l_api_name CONSTANT VARCHAR2(30):= 'delete_spec_element';
1588 l_api_version CONSTANT NUMBER := 1.0;
1589
1590 l_user_id NUMBER;
1591 l_spec_id NUMBER;
1592 l_char_id NUMBER;
1593 l_org_id NUMBER;
1594
1595 BEGIN
1596
1597 -- Standard Start of API savepoint
1598
1599 SAVEPOINT delete_spec_element;
1600
1601 -- Standard call to check for call compatibility.
1602 IF NOT fnd_api.compatible_api_call(
1603 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1604 RAISE fnd_api.g_exc_unexpected_error;
1605 END IF;
1606
1607 -- Initialize message list if p_init_msg_list is set to TRUE.
1608 IF fnd_api.to_boolean(p_init_msg_list) THEN
1609 fnd_msg_pub.initialize;
1610 END IF;
1611
1612 -- Initialize API return status to success
1613 x_return_status := fnd_api.g_ret_sts_success;
1614
1615 l_user_id := get_user_id(p_user_name);
1616 IF l_user_id = -1 THEN
1617 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
1618 fnd_msg_pub.add();
1619 raise fnd_api.g_exc_error;
1620 END IF;
1621
1622 l_spec_id := get_spec_id(p_spec_name, p_organization_code);
1623
1624 -- We must be checking here if the spec is a child
1625 IF is_child_spec(l_spec_id) THEN
1626 fnd_message.set_name('QA', 'QA_DELETE_SPEC_ELEM_ON_CHILD');
1627 fnd_msg_pub.add();
1628 raise fnd_api.g_exc_error;
1629 END IF;
1630
1631 l_char_id := get_context_element_id(p_element_name);
1632
1633 delete_spec_element_private(l_spec_id, l_char_id);
1634
1635 IF fnd_api.to_boolean(p_commit) THEN
1636 COMMIT;
1637 END IF;
1638
1639 EXCEPTION
1640
1641 WHEN fnd_api.g_exc_error THEN
1642 ROLLBACK TO delete_spec_element;
1643 x_return_status := fnd_api.g_ret_sts_error;
1644 fnd_msg_pub.count_and_get(
1645 p_count => x_msg_count,
1646 p_data => x_msg_data
1647 );
1648
1649 WHEN fnd_api.g_exc_unexpected_error THEN
1650 ROLLBACK TO delete_spec_element;
1651 x_return_status := fnd_api.g_ret_sts_unexp_error;
1652 fnd_msg_pub.count_and_get(
1653 p_count => x_msg_count,
1654 p_data => x_msg_data
1655 );
1656
1657 WHEN OTHERS THEN
1658 ROLLBACK TO delete_spec_element;
1659 x_return_status := fnd_api.g_ret_sts_unexp_error;
1660 IF fnd_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1661 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1662 END IF;
1663 fnd_msg_pub.count_and_get(
1664 p_count => x_msg_count,
1665 p_data => x_msg_data
1666 );
1667
1668 END delete_spec_element;
1669
1670 --
1671 -- We should not copy Spec Elements if the spec being copied is a child.
1672 -- The new, copied spec still references the same master spec.
1673 -- Copying child is seemless and caller will not see any message.
1674 --
1675 -- rkunchal
1676 --
1677
1678 PROCEDURE copy_specification(
1679 p_api_version IN NUMBER,
1680 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1681 p_user_name IN VARCHAR2 := NULL,
1682 p_spec_name IN VARCHAR2,
1683 p_organization_code IN VARCHAR2,
1684 p_to_spec_name IN VARCHAR2,
1685 p_to_organization_code IN VARCHAR2,
1686 p_to_item_name IN VARCHAR2,
1687 p_to_item_revision IN VARCHAR2 := NULL,
1688 p_commit IN VARCHAR2 := fnd_api.g_false,
1689 x_spec_id OUT NOCOPY NUMBER,
1690 x_msg_count OUT NOCOPY NUMBER,
1691 x_msg_data OUT NOCOPY VARCHAR2,
1692 x_return_status OUT NOCOPY VARCHAR2) IS
1693
1694 l_api_name CONSTANT VARCHAR2(30):= 'copy_specification';
1695 l_api_version CONSTANT NUMBER := 1.0;
1696
1697 l_user_id NUMBER;
1698 l_spec_id NUMBER;
1699 l_org_id NUMBER;
1700 l_spec_name NUMBER;
1701 l_to_org_id NUMBER;
1702 l_category_set_id NUMBER;
1703 l_category_id NUMBER;
1704 l_to_item_id NUMBER;
1705 l_to_item_revision NUMBER;
1706 l_vendor_id NUMBER;
1707 l_customer_id NUMBER;
1708 l_char_id NUMBER;
1709 l_spec_element_value VARCHAR2(150);
1710 l_existing_spec_name VARCHAR2(30);
1711
1712 --BUG 3500244
1713 --we do NOT support ERES in APIs as documented in QA FDA HLD
1714 --so this should behave as though ERES profile is No
1715 --which means spec_status of 40 which is No Approval Reqd
1716 --should be used for spec creation since it is a NON-Null Column
1717
1718 l_spec_status NUMBER := 40;
1719
1720 BEGIN
1721
1722 -- Standard Start of API savepoint
1723
1724 SAVEPOINT copy_specification;
1725
1726 -- Standard call to check for call compatibility.
1727 IF NOT fnd_api.compatible_api_call(
1728 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1729 RAISE fnd_api.g_exc_unexpected_error;
1730 END IF;
1731
1732 -- Initialize message list if p_init_msg_list is set to TRUE.
1733 IF fnd_api.to_boolean(p_init_msg_list) THEN
1734 fnd_msg_pub.initialize;
1735 END IF;
1736
1737 -- Initialize API return status to success
1738 x_return_status := fnd_api.g_ret_sts_success;
1739
1740 l_user_id := get_user_id(p_user_name);
1741 IF l_user_id = -1 THEN
1742 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
1743 fnd_msg_pub.add();
1744 raise fnd_api.g_exc_error;
1745 END IF;
1746
1747 l_spec_id := get_spec_id(p_spec_name, p_organization_code);
1748 l_org_id := organization_id(l_spec_id);
1749
1750 l_to_org_id := qa_plans_api.get_org_id(p_to_organization_code);
1751 IF (l_to_org_id IS NULL) THEN
1752 fnd_message.set_name('QA', 'QA_API_INVALID_ORG_CODE');
1753 fnd_msg_pub.add();
1754 RAISE fnd_api.g_exc_error;
1755 END IF;
1756
1757 IF (spec_exists(p_to_spec_name)) THEN
1758 fnd_message.set_name('QA', 'QA_API_DUPLICATE_SPEC_NAME');
1759 fnd_msg_pub.add();
1760 RAISE fnd_api.g_exc_error;
1761 END IF;
1762
1763 l_to_item_id := process_item_and_revision(p_to_item_name,
1764 p_to_item_revision, l_org_id);
1765
1766 l_category_set_id := category_set_id(l_spec_id);
1767 l_category_id := category_id(l_spec_id);
1768 l_vendor_id := vendor_id(l_spec_id);
1769 l_customer_id := customer_id(l_spec_id);
1770 l_char_id := char_id(l_spec_id);
1771 l_spec_element_value := spec_element_value(l_spec_id);
1772
1773 IF (combination_exists(l_category_set_id, l_category_id, l_to_item_id,
1774 p_to_item_revision, l_org_id, l_vendor_id, l_customer_id, l_char_id,
1775 l_spec_element_value, l_existing_spec_name)) THEN
1776
1777 fnd_message.set_name('QA', 'QA_SPEC_COMBINATION_EXISTS');
1778 fnd_message.set_token('ENTITY1', l_existing_spec_name);
1779 fnd_msg_pub.add();
1780 RAISE fnd_api.g_exc_error;
1781 END IF;
1782
1783 SELECT qa_specs_s.nextval INTO x_spec_id FROM DUAL;
1784
1785 INSERT INTO qa_specs(
1786 spec_id,
1787 last_update_date,
1788 last_updated_by,
1789 creation_date,
1790 created_by,
1791 last_update_login,
1792 spec_name,
1793 organization_id,
1794 effective_from,
1795 effective_to,
1796 common_spec_id,
1797 assignment_type,
1798 category_set_id,
1799 category_id,
1800 item_id,
1801 item_revision,
1802 vendor_id,
1803 customer_id,
1804 hide_plan_chars_flag,
1805 char_id,
1806 spec_element_value,
1807 spec_status ) --Bug 3500244
1808 SELECT
1809 x_spec_id,
1810 sysdate,
1811 l_user_id,
1812 sysdate,
1813 l_user_id,
1814 l_user_id,
1815 p_to_spec_name,
1816 l_to_org_id,
1817 effective_from,
1818 effective_to,
1819 common_spec_id,
1820 assignment_type,
1821 category_set_id,
1822 category_id,
1823 l_to_item_id,
1824 nvl(p_to_item_revision, 'NONE'),
1825 vendor_id,
1826 customer_id,
1827 hide_plan_chars_flag,
1828 char_id,
1829 spec_element_value,
1830 40 --Bug 3500244 see note below
1831 FROM qa_specs
1832 WHERE spec_id = l_spec_id;
1833
1834 --Bug 3500244
1835 --In above Select we have not used the variable l_spec_status
1836 --But purposely used the literal 40 so it is very clear for readability
1837 --that we are not selecting from database, but it is kind of a constant
1838
1839
1840 -- Prevent this insertion if the spec being copied is a child spec
1841 IF NOT is_child_spec(l_spec_id) THEN
1842 INSERT INTO qa_spec_chars(
1843 spec_id,
1844 char_id,
1845 last_update_date,
1846 last_updated_by,
1847 creation_date,
1848 created_by,
1849 last_update_login,
1850 enabled_flag,
1851 target_value,
1852 upper_spec_limit,
1853 lower_spec_limit,
1854 upper_reasonable_limit,
1855 lower_reasonable_limit,
1856 upper_user_defined_limit,
1857 lower_user_defined_limit)
1858 SELECT
1859 x_spec_id,
1860 char_id,
1861 sysdate,
1862 l_user_id,
1863 sysdate,
1864 l_user_id,
1865 l_user_id,
1866 enabled_flag,
1867 target_value,
1868 upper_spec_limit,
1869 lower_spec_limit,
1870 upper_user_defined_limit,
1871 lower_user_defined_limit,
1872 upper_reasonable_limit,
1873 lower_reasonable_limit
1874 FROM qa_spec_chars
1875 WHERE spec_id = l_spec_id;
1876 END IF;
1877
1878 --
1879 -- Bug 5231952. After copying, we should copy attachments.
1880 --
1881 qa_specs_pkg.copy_attachment(
1882 p_from_spec_id => l_spec_id,
1883 p_to_spec_id => x_spec_id);
1884
1885 IF fnd_api.to_boolean(p_commit) THEN
1886 COMMIT;
1887 END IF;
1888
1889 EXCEPTION
1890
1891 WHEN fnd_api.g_exc_error THEN
1892 ROLLBACK TO copy_specification;
1893 x_return_status := fnd_api.g_ret_sts_error;
1894 fnd_msg_pub.count_and_get(
1895 p_count => x_msg_count,
1896 p_data => x_msg_data
1897 );
1898
1899 WHEN fnd_api.g_exc_unexpected_error THEN
1900 ROLLBACK TO copy_specification;
1901 x_return_status := fnd_api.g_ret_sts_unexp_error;
1902 fnd_msg_pub.count_and_get(
1903 p_count => x_msg_count,
1904 p_data => x_msg_data
1905 );
1906
1907 WHEN OTHERS THEN
1908 ROLLBACK TO copy_specification;
1909 x_return_status := fnd_api.g_ret_sts_unexp_error;
1910 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1911 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
1912 END IF;
1913 fnd_msg_pub.count_and_get(
1914 p_count => x_msg_count,
1915 p_data => x_msg_data
1916 );
1917
1918 END copy_specification;
1919
1920
1921 END qa_specs_pub;
1922
[Home] [Help]