1 PACKAGE BODY qa_specs_pkg AS
2 /* $Header: qaspecb.pls 120.2 2006/07/17 04:19:01 bso noship $ */
3
4 --
5 -- Simple utility function to test if an ID is
6 -- null or -1. The IDs as setup in qa_specs are
7 -- usually filled with -1 to indicate not specified.
8 --
9 FUNCTION empty(p_id NUMBER) RETURN BOOLEAN IS
10 BEGIN
11 RETURN (p_id IS NULL) OR (p_id = -1);
12 END empty;
13
14
15 --
16 -- Utility function to test if an item is in a
17 -- particular item category and category set.
18 --
19 FUNCTION item_in_cat(
20 p_organization_id NUMBER,
21 p_item_id NUMBER,
22 p_spec_cat_id NUMBER,
23 p_spec_cat_set_id NUMBER)
24 RETURN BOOLEAN IS
25 dummy INTEGER;
26 CURSOR c IS
27 SELECT 1
28 FROM mtl_item_categories mic
29 WHERE mic.inventory_item_id = p_item_id AND
30 mic.organization_id = p_organization_id AND
31 mic.category_id = p_spec_cat_id AND
32 mic.category_set_id = p_spec_cat_set_id;
33 BEGIN
34 OPEN c;
35 FETCH c INTO dummy;
36 CLOSE c;
37
38 RETURN (dummy IS NOT NULL);
39 END item_in_cat;
40
41
42 --
43 -- Utility function to test if an item is in a
44 -- particular item category set.
45 --
46 FUNCTION item_in_cat_set(
47 p_organization_id NUMBER,
48 p_item_id NUMBER,
49 p_spec_cat_set_id NUMBER)
50 RETURN BOOLEAN IS
51 dummy INTEGER;
52 CURSOR c IS
53 SELECT 1
54 FROM mtl_item_categories mic
55 WHERE mic.inventory_item_id = p_item_id AND
56 mic.organization_id = p_organization_id AND
57 mic.category_set_id = p_spec_cat_set_id;
58 BEGIN
59 OPEN c;
60 FETCH c INTO dummy;
61 CLOSE c;
62
63 RETURN (dummy IS NOT NULL);
64 END item_in_cat_set;
65
66
67 --
68 -- Find out if an item is subsumed by a Spec's setup.
69 -- It is if any one is true:
70 -- 1. p_item_id = p_spec_item_id
71 -- 2. p_spec_item_id is null and
72 -- p_item_id is within the given item category
73 -- and item category set.
74 -- 3. p_spec_item_id is null and p_spec_cat_id is null and
75 -- p_item_id is within the given item category set.
76 -- Return 'T' or 'F'
77 --
78 -- bso Thu May 12 16:54:59 PDT 2005
79 --
80 FUNCTION spec_item_matched(
81 p_organization_id NUMBER,
82 p_item_id NUMBER,
83 p_spec_item_id NUMBER,
84 p_spec_cat_id NUMBER,
85 p_spec_cat_set_id NUMBER)
86 RETURN VARCHAR2 IS
87 BEGIN
88 --
89 -- 3. p_spec_item_id is null and p_spec_cat_id is null and
90 -- p_item_id is within the given item category set.
91 --
92 IF empty(p_spec_item_id) AND
93 empty(p_spec_cat_id) AND
94 item_in_cat_set(
95 p_organization_id,
96 p_item_id,
97 p_spec_cat_set_id) THEN
98 RETURN fnd_api.g_true;
99 END IF;
100
101 --
102 -- 2. p_spec_item_id is null and
103 -- p_item_id is within the given item category
104 -- and item category set.
105 --
106 IF empty(p_spec_item_id) AND
107 item_in_cat(
108 p_organization_id,
109 p_item_id,
110 p_spec_cat_id,
111 p_spec_cat_set_id) THEN
112 RETURN fnd_api.g_true;
113 END IF;
114
115 --
116 -- 1. p_item_id = p_spec_item_id
117 --
118 IF p_item_id = p_spec_item_id THEN
119 RETURN fnd_api.g_true;
120 END IF;
121
122 RETURN fnd_api.g_false;
123 END spec_item_matched;
124
125
126 --
127 -- Tracking Bug 4939897
128 -- R12 Forms Tech Stack Upgrade - Obsolete Oracle Graphics
129 -- Also a generically useful function to fetch spec name.
130 -- bso Tue Feb 7 15:41:15 PST 2006
131 --
132 FUNCTION get_spec_name(p_spec_id NUMBER) RETURN VARCHAR2 IS
133 l_spec_name qa_specs.spec_name%TYPE;
134 BEGIN
135 SELECT spec_name INTO l_spec_name
136 FROM qa_specs
137 WHERE spec_id = p_spec_id AND rownum <= 1;
138
139 RETURN l_spec_name;
140 END get_spec_name;
141
142
143 --
144 -- Bug 5231952
145 -- Add a utility to copy attachments when assigning a parent
146 -- spec to a child.
147 -- bso Sun Jul 16 20:27:24 PDT 2006
148 --
149 PROCEDURE copy_attachment(p_from_spec_id NUMBER, p_to_spec_id NUMBER) IS
150 BEGIN
151 --
152 -- Use standard FND API to copy attachment from master
153 -- spec to child spec.
154 --
155 fnd_attached_documents2_pkg.copy_attachments(
156 x_from_entity_name => 'QA_SPECS',
157 x_from_pk1_value => p_from_spec_id,
158 x_to_entity_name => 'QA_SPECS',
159 x_to_pk1_value => p_to_spec_id);
160
161 END copy_attachment;
162
163 END qa_specs_pkg;