[Home] [Help]
PACKAGE BODY: APPS.QP_CATEGORY_MAPPING_RULE
Source
1 PACKAGE BODY QP_CATEGORY_MAPPING_RULE AS
2 /* $Header: QPXPSICB.pls 120.7 2006/10/19 23:14:08 spgopal noship $ */
3
4 FUNCTION Get_Item_Category (p_inventory_item_id IN NUMBER)
5 RETURN QP_Attr_Mapping_PUB.t_MultiRecord
6 IS
7
8 --TYPE t_cursor IS REF CURSOR;
9
10 x_category_ids QP_Attr_Mapping_PUB.t_MultiRecord;
11 l_category_id VARCHAR2(30);
12 v_count NUMBER := 1;
13 --l_category_cursor t_cursor;
14 l_appl_id NUMBER;
15 l_category_set_id_7 NUMBER;
16 l_category_set_id_11 NUMBER;
17 l_hierarchy_enabled VARCHAR2(1);
18 l_org_id NUMBER;
19 l_ou_org_id NUMBER;
20 --
21 -- commented out for Product Hierarchy changes
22 /*
23 -- Changed cursor for 3125141
24 CURSOR l_category_cursor (org_id_in number,appl_id_in number) is
25 SELECT a.category_id
26 FROM mtl_item_categories a,
27 mtl_categories_b b,
28 mtl_default_category_sets c,
29 mtl_category_sets_b d
30 WHERE a.inventory_item_id = p_inventory_item_id
31 AND a.organization_id = org_id_in
32 AND a.category_id = b.category_id
33 AND c.category_set_id = d.category_set_id
34 AND c.functional_area_id = decode(appl_id_in,201,2,7)
35 AND b.structure_id = d.structure_id;
36
37 CURSOR l_exploded_category_cursor (org_id_in number, appl_id_in number) is
38 SELECT parent_id
39 FROM eni_denorm_hierarchies a,
40 mtl_item_categories b,
41 mtl_default_category_sets c
42 WHERE inventory_item_id = p_inventory_item_id and
43 organization_id = org_id_in and
44 a.object_type = 'CATEGORY_SET' and
45 a.object_id = c.category_set_id and
46 b.category_id = child_id and
47 functional_area_id = decode(appl_id_in,201,2,7);
48 CURSOR l_exploded_category_cursor (org_id_in number, appl_id_in number,
49 category_set_id_in number) is
50 SELECT parent_id
51 FROM eni_denorm_hierarchies a,
52 mtl_item_categories b,
53 mtl_default_category_sets c
54 WHERE inventory_item_id = p_inventory_item_id and
55 organization_id = org_id_in and
56 b.category_set_id = category_set_id_in and
57 a.object_type = 'CATEGORY_SET' and
58 a.object_id = c.category_set_id and
59 b.category_id = child_id and
60 functional_area_id = decode(appl_id_in,201,2,11);
61 */
62 CURSOR l_category_cursor (org_id_in number,req_type_code_in VARCHAR2) is
63 SELECT distinct /*+ ordered use_nl(b c d) */ a.category_id
64 FROM mtl_item_categories a,
65 mtl_categories_b b,
66 mtl_default_category_sets c,
67 mtl_category_sets_b d
68 WHERE a.inventory_item_id = p_inventory_item_id
69 AND a.organization_id = org_id_in
70 AND a.category_id = b.category_id
71 AND c.category_set_id = d.category_set_id
72 AND b.structure_id = d.structure_id
73 AND nvl(d.hierarchy_enabled,'N') = 'N'
74 AND c.functional_area_id in (SELECT distinct FNAREA.FUNCTIONAL_AREA_ID
75 FROM QP_PTE_REQUEST_TYPES_B REQ,
76 QP_PTE_SOURCE_SYSTEMS SOU,
77 QP_SOURCESYSTEM_FNAREA_MAP FNAREA
78 WHERE REQ.REQUEST_TYPE_CODE = req_type_code_in and REQ.ENABLED_FLAG = 'Y' and
79 REQ.PTE_CODE = SOU.PTE_CODE and SOU.ENABLED_FLAG = 'Y'and
80 SOU.PTE_SOURCE_SYSTEM_ID = FNAREA.PTE_SOURCE_SYSTEM_ID and
81 FNAREA.ENABLED_FLAG = 'Y');
82
83 CURSOR l_exploded_category_cursor (org_id_in number, req_type_code_in VARCHAR2) is
84 SELECT distinct /*+ ORDERED USE_NL(c d b a) */ a.parent_id
85 FROM mtl_default_category_sets c,
86 mtl_category_sets_b d,
87 mtl_item_categories b,
88 eni_denorm_hierarchies a
89 WHERE b.inventory_item_id = p_inventory_item_id and
90 b.organization_id = org_id_in and
91 b.category_set_id = c.category_set_id and
92 a.object_type = 'CATEGORY_SET' and
93 c.category_set_id = d.category_set_id and
94 d.hierarchy_enabled = 'Y' and
95 a.object_id = c.category_set_id and
96 b.category_id = a.child_id and
97 c.functional_area_id in (SELECT /*+ ORDERED USE_NL(REQ SOU FNAREA)*/
98 distinct FNAREA.FUNCTIONAL_AREA_ID
99 FROM QP_PTE_REQUEST_TYPES_B REQ,
100 QP_PTE_SOURCE_SYSTEMS SOU,
101 QP_SOURCESYSTEM_FNAREA_MAP FNAREA
102 WHERE REQ.REQUEST_TYPE_CODE = req_type_code_in
103 and REQ.ENABLED_FLAG = 'Y'
104 and REQ.PTE_CODE = SOU.PTE_CODE
105 and SOU.ENABLED_FLAG = 'Y'
106 and SOU.PTE_SOURCE_SYSTEM_ID =
107 FNAREA.PTE_SOURCE_SYSTEM_ID
108 and FNAREA.ENABLED_FLAG = 'Y');
109
110 BEGIN
111
112 l_appl_id := FND_GLOBAL.RESP_APPL_ID;
113
114 IF QP_ATTR_MAPPING_PUB.G_REQ_TYPE_CODE = 'ONT' THEN
115 --passing null org_id to OE_Sys_Parameters for moac so that it will return MASTER_ORGANIZATION_ID
116 --for the org_context set -- build_contexts API or calling app would have set 'single' org context
117 --added for moac to call oe_sys_params only when org_id is not null
118 l_ou_org_id := QP_UTIL.get_org_id;
119 IF l_ou_org_id IS NOT NULL THEN
120 l_org_id := OE_Sys_Parameters.Value('MASTER_ORGANIZATION_ID', l_ou_org_id);
121 ELSE -- get master org from QP profile value
122 l_org_id := FND_PROFILE.Value('QP_ORGANIZATION_ID');
123 END IF;
124 ELSE
125 l_org_id := FND_PROFILE.Value('QP_ORGANIZATION_ID');
126 END IF;
127
128 -- commented out for product hierarchy
129 /*
130 begin
131 --
132 select category_set_id
133 into l_category_set_id_11
134 from mtl_default_category_sets
135 where functional_area_id = 11 and
136 rownum < 2;
137 --
138 --if l_category_set_id_7 = l_category_set_id_11 then
139 select hierarchy_enabled
140 into l_hierarchy_enabled
141 from mtl_category_sets_b
142 where category_set_id = l_category_set_id_11 and
143 hierarchy_enabled = 'Y';
144 --
145 OPEN l_exploded_category_cursor(l_org_id,l_appl_id,
146 l_category_set_id_11);
147 LOOP
148 FETCH l_exploded_category_cursor INTO l_category_id;
149 EXIT WHEN l_exploded_category_cursor%NOTFOUND;
150
151 x_category_ids(v_count) := l_category_id;
152 v_count := v_count + 1;
153 END LOOP;
154 CLOSE l_exploded_category_cursor;
155 --else
156 OPEN l_category_cursor(l_org_id,l_appl_id);
157 LOOP
158 FETCH l_category_cursor INTO l_category_id;
159 EXIT WHEN l_category_cursor%NOTFOUND;
160
161 x_category_ids(v_count) := l_category_id;
162 v_count := v_count + 1;
163 END LOOP;
164 CLOSE l_category_cursor;
165 --end if;
166 exception
167 when others then
168 OPEN l_category_cursor(l_org_id,l_appl_id);
169 LOOP
170 FETCH l_category_cursor INTO l_category_id;
171 EXIT WHEN l_category_cursor%NOTFOUND;
172
173 x_category_ids(v_count) := l_category_id;
174 v_count := v_count + 1;
175 END LOOP;
176 CLOSE l_category_cursor;
177 end;
178 */
179 -- new changes for product heirarchy starts here
180 If qp_util.get_qp_status <>'I' THEN -- Basic pricing
181 OPEN l_category_cursor(l_org_id,QP_ATTR_MAPPING_PUB.G_REQ_TYPE_CODE);
182 LOOP
183 FETCH l_category_cursor INTO l_category_id;
184 EXIT WHEN l_category_cursor%NOTFOUND;
185
186 x_category_ids(v_count) := l_category_id;
187 v_count := v_count + 1;
188 END LOOP;
189 CLOSE l_category_cursor;
190
191 ELSE -- Advanced pricing has product heirarchy feature
192 OPEN l_exploded_category_cursor(l_org_id, QP_ATTR_MAPPING_PUB.G_REQ_TYPE_CODE);
193 LOOP
194 FETCH l_exploded_category_cursor INTO l_category_id;
195 EXIT WHEN l_exploded_category_cursor%NOTFOUND;
196
197 x_category_ids(v_count) := l_category_id;
198 v_count := v_count + 1;
199 END LOOP;
200 CLOSE l_exploded_category_cursor;
201 OPEN l_category_cursor(l_org_id,QP_ATTR_MAPPING_PUB.G_REQ_TYPE_CODE);
202 LOOP
203 FETCH l_category_cursor INTO l_category_id;
204 EXIT WHEN l_category_cursor%NOTFOUND;
205
206 x_category_ids(v_count) := l_category_id;
207 v_count := v_count + 1;
208 END LOOP;
209 CLOSE l_category_cursor;
210 END IF; -- end advacned pricing
211 RETURN x_category_ids;
212
213 END Get_Item_Category;
214
215 /*
216 * Commented out for bug 4753707
217 *
218 FUNCTION Validate_UOM (p_org_id IN NUMBER,
219 p_category_id IN NUMBER,
220 p_product_uom_code IN VARCHAR2)
221 RETURN VARCHAR2
222 IS
223 l_dummy_2 VARCHAR2(3);
224 l_category_set_id NUMBER; -- 11i10 Product Catalog
225 BEGIN
226 -- get category set ID for hierarchical categories (e.g., Product Catalog 11i10)
227 select category_set_id
228 into l_category_set_id
229 from mtl_default_category_sets
230 where functional_area_id = 11 and rownum < 2;
231
232 /* this SQL produces cartesian joins
233 rewriting to use base tables as part of SQL repository fix
234 select distinct uom_code
235 into l_dummy_2
236 from mtl_item_uoms_view
237 where (organization_id = p_org_id or p_org_id is null)
238 and uom_code = p_product_uom_code
239 and inventory_item_id in
240 ...
241 *--/
242 select MTLUOM2.uom_code
243 into l_dummy_2
244 from MTL_SYSTEM_ITEMS_B MTLITM1,
245 MTL_UNITS_OF_MEASURE_VL MTLUOM2,
246 MTL_UOM_CONVERSIONS MTLUCV
247 where ((MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (1,3)
248 AND MTLUCV.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
249 OR ( MTLUCV.INVENTORY_ITEM_ID = 0
250 AND MTLUOM2.BASE_UOM_FLAG = 'Y'
251 AND MTLUOM2.UOM_CLASS = MTLUCV.UOM_CLASS
252 AND MTLUCV.UOM_CLASS IN
253 ( SELECT MTLPRI1.UOM_CLASS
254 FROM MTL_UNITS_OF_MEASURE MTLPRI1
255 WHERE MTLPRI1.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE) )
256 OR (MTLUCV.INVENTORY_ITEM_ID = 0
257 AND MTLUCV.UOM_CODE IN
258 (SELECT MTLUCC1.TO_UOM_CODE
259 FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC1
260 WHERE MTLUCC1.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
261 AND NVL(MTLUCC1.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) ) ) )
262 OR
263 (MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (2,3)
264 AND MTLUCV.INVENTORY_ITEM_ID = 0
265 AND ( MTLUCV.UOM_CLASS IN
266 (SELECT MTLUCC.TO_UOM_CLASS
267 FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC
268 WHERE MTLUCC.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
269 AND NVL(MTLUCC.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE))
270 OR MTLUCV.UOM_CLASS =
271 (SELECT MTLPRI.UOM_CLASS
272 FROM MTL_UNITS_OF_MEASURE MTLPRI
273 WHERE MTLPRI.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE)) ) )
274 AND NVL(MTLUCV.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
275 AND NVL(MTLUOM2.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
276 AND MTLUOM2.UOM_CODE = MTLUCV.UOM_CODE
277 AND (MTLITM1.organization_id = p_org_id or p_org_id is null)
278 AND MTLUOM2.uom_code = p_product_uom_code
279 AND MTLUCV.inventory_item_id in
280 (select inventory_item_id
281 from mtl_item_categories
282 where category_id = p_category_id
283 and (organization_id = p_org_id or p_org_id is null)
284 UNION
285 -- for Product Catalog 11i10
286 select inventory_item_id
287 from eni_denorm_hierarchies eni,
288 mtl_item_categories mtl,
289 mtl_default_category_sets sets
290 where mtl.category_set_id = eni.object_id
291 and mtl.category_set_id = l_category_set_id
292 and eni.object_type = 'CATEGORY_SET'
293 and eni.object_id = sets.category_set_id
294 and mtl.category_id = eni.child_id
295 and sets.functional_area_id = decode(FND_GLOBAL.RESP_APPL_ID, 201,
296 2, 11)
297 and (mtl.organization_id = p_org_id or p_org_id IS NULL)
298 and eni.parent_id = p_category_id)
299 and rownum=1;
300
301 RETURN 'Y';
302 EXCEPTION
303 WHEN NO_DATA_FOUND THEN
304 RETURN 'N';
305 END Validate_UOM;
306 *
307 */
308
309 END QP_CATEGORY_MAPPING_RULE;