[Home] [Help]
PACKAGE BODY: APPS.QP_CATEGORY_MAPPING_RULE
Source
1 PACKAGE BODY QP_CATEGORY_MAPPING_RULE AS
2 /* $Header: QPXPSICB.pls 120.11 2011/03/07 11:31:51 jputta ship $ */
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
12 --8805312
13 l_int_category_ids QP_Attr_Mapping_PUB.t_MultiRecord;
14
15 l_category_id VARCHAR2(30);
16 v_count NUMBER := 1;
17 --l_category_cursor t_cursor;
18 l_appl_id NUMBER;
19 l_category_set_id_7 NUMBER;
20 l_category_set_id_11 NUMBER;
21 l_hierarchy_enabled VARCHAR2(1);
22 l_org_id NUMBER;
23 l_ou_org_id NUMBER;
24 --
25 -- commented out for Product Hierarchy changes
26 /*
27 -- Changed cursor for 3125141
28 CURSOR l_category_cursor (org_id_in number,appl_id_in number) is
29 SELECT a.category_id
30 FROM mtl_item_categories a,
31 mtl_categories_b b,
32 mtl_default_category_sets c,
33 mtl_category_sets_b d
34 WHERE a.inventory_item_id = p_inventory_item_id
35 AND a.organization_id = org_id_in
36 AND a.category_id = b.category_id
37 AND c.category_set_id = d.category_set_id
38 AND c.functional_area_id = decode(appl_id_in,201,2,7)
39 AND b.structure_id = d.structure_id;
40
41 CURSOR l_exploded_category_cursor (org_id_in number, appl_id_in number) is
42 SELECT parent_id
43 FROM eni_denorm_hierarchies a,
44 mtl_item_categories b,
45 mtl_default_category_sets c
46 WHERE inventory_item_id = p_inventory_item_id and
47 organization_id = org_id_in and
48 a.object_type = 'CATEGORY_SET' and
49 a.object_id = c.category_set_id and
50 b.category_id = child_id and
51 functional_area_id = decode(appl_id_in,201,2,7);
52 CURSOR l_exploded_category_cursor (org_id_in number, appl_id_in number,
53 category_set_id_in number) is
54 SELECT parent_id
55 FROM eni_denorm_hierarchies a,
56 mtl_item_categories b,
57 mtl_default_category_sets c
58 WHERE inventory_item_id = p_inventory_item_id and
59 organization_id = org_id_in and
60 b.category_set_id = category_set_id_in and
61 a.object_type = 'CATEGORY_SET' and
62 a.object_id = c.category_set_id and
63 b.category_id = child_id and
64 functional_area_id = decode(appl_id_in,201,2,11);
65 */
66
67 --bug 11666034
68 --Rewriting cursor query for better performance.
69 /*CURSOR l_category_cursor (org_id_in number,req_type_code_in VARCHAR2) is
70 SELECT distinct /*+ ordered use_nl(b c d) */ /*a.category_id
71 FROM mtl_item_categories a,
72 mtl_categories_b b,
73 mtl_default_category_sets c,
74 mtl_category_sets_b d
75 WHERE a.inventory_item_id = p_inventory_item_id
76 AND a.organization_id = org_id_in
77 AND a.category_id = b.category_id
78 AND c.category_set_id = d.category_set_id
79 AND b.structure_id = d.structure_id
80 AND nvl(d.hierarchy_enabled,'N') = 'N'
81 AND c.functional_area_id in (SELECT distinct FNAREA.FUNCTIONAL_AREA_ID
82 FROM QP_PTE_REQUEST_TYPES_B REQ,
83 QP_PTE_SOURCE_SYSTEMS SOU,
84 QP_SOURCESYSTEM_FNAREA_MAP FNAREA
85 WHERE REQ.REQUEST_TYPE_CODE = req_type_code_in and REQ.ENABLED_FLAG = 'Y' and
86 REQ.PTE_CODE = SOU.PTE_CODE and SOU.ENABLED_FLAG = 'Y'and
87 SOU.PTE_SOURCE_SYSTEM_ID = FNAREA.PTE_SOURCE_SYSTEM_ID and
88 FNAREA.ENABLED_FLAG = 'Y');*/
89
90
91 CURSOR l_category_cursor (org_id_in number,req_type_code_in VARCHAR2) IS
92 SELECT /*+ leading(a,b,d,c) use_nl(b c d) */ DISTINCT A.CATEGORY_ID
93 FROM MTL_ITEM_CATEGORIES A,
94 MTL_CATEGORIES_B B,
95 MTL_DEFAULT_CATEGORY_SETS C,
96 MTL_CATEGORY_SETS_B D
97 WHERE A.INVENTORY_ITEM_ID = p_inventory_item_id
98 AND A.ORGANIZATION_ID = org_id_in
99 AND A.CATEGORY_ID = B.CATEGORY_ID
100 AND A.CATEGORY_SET_ID = D.CATEGORY_SET_ID --Bug#11719249
101 AND C.CATEGORY_SET_ID = D.CATEGORY_SET_ID
102 AND B.STRUCTURE_ID = D.STRUCTURE_ID
103 AND NVL(D.HIERARCHY_ENABLED,'N') = 'N'
104 AND C.FUNCTIONAL_AREA_ID IN (SELECT /*+ no_unnest */ DISTINCT FNAREA.FUNCTIONAL_AREA_ID
105 FROM QP_PTE_REQUEST_TYPES_B REQ,
106 QP_PTE_SOURCE_SYSTEMS SOU,
107 QP_SOURCESYSTEM_FNAREA_MAP FNAREA
108 WHERE REQ.REQUEST_TYPE_CODE = req_type_code_in
109 AND REQ.ENABLED_FLAG = 'Y'
110 AND REQ.PTE_CODE = SOU.PTE_CODE
111 AND SOU.ENABLED_FLAG = 'Y'
112 AND SOU.PTE_SOURCE_SYSTEM_ID = FNAREA.PTE_SOURCE_SYSTEM_ID
113 AND FNAREA.ENABLED_FLAG = 'Y');
114 -- bug 8805312
115 -- Changed cursor definition
116 /* CURSOR l_exploded_category_cursor (org_id_in number, req_type_code_in VARCHAR2) is
117 SELECT distinct /*+ ORDERED USE_NL(c d b a) */ /*a.parent_id
118 FROM mtl_default_category_sets c,
119 mtl_category_sets_b d,
120 mtl_item_categories b,
121 eni_denorm_hierarchies a
122 WHERE b.inventory_item_id = p_inventory_item_id and
123 b.organization_id = org_id_in and
124 b.category_set_id = c.category_set_id and
125 a.object_type = 'CATEGORY_SET' and
126 c.category_set_id = d.category_set_id and
127 d.hierarchy_enabled = 'Y' and
128 a.object_id = c.category_set_id and
129 b.category_id = a.child_id and
130 c.functional_area_id in (SELECT /*+ ORDERED USE_NL(REQ SOU FNAREA)*//*
131 distinct FNAREA.FUNCTIONAL_AREA_ID
132 FROM QP_PTE_REQUEST_TYPES_B REQ,
133 QP_PTE_SOURCE_SYSTEMS SOU,
134 QP_SOURCESYSTEM_FNAREA_MAP FNAREA
135 WHERE REQ.REQUEST_TYPE_CODE = req_type_code_in
136 and REQ.ENABLED_FLAG = 'Y'
137 and REQ.PTE_CODE = SOU.PTE_CODE
138 and SOU.ENABLED_FLAG = 'Y'
139 and SOU.PTE_SOURCE_SYSTEM_ID =
140 FNAREA.PTE_SOURCE_SYSTEM_ID
141 and FNAREA.ENABLED_FLAG = 'Y');*/
142
143 CURSOR l_exploded_category_cursor (org_id_in number, req_type_code_in VARCHAR2) is
144 select /*+ leading(x,A) no_merge(x) use_nl_with_index(a ENI_DENORM_HIERARCHIES_N2) */ A.PARENT_ID --bug 8924817
145 from (select /*+ ordered use_nl(req sou fnarea c d b) */ distinct b.category_id,
146 b.category_set_id
147 from QP_PTE_REQUEST_TYPES_B REQ,
148 QP_PTE_SOURCE_SYSTEMS SOU,
149 QP_SOURCESYSTEM_FNAREA_MAP FNAREA,
150 MTL_DEFAULT_CATEGORY_SETS C,
151 mtl_category_sets_b d,
152 mtl_item_categories b
153 WHERE REQ.REQUEST_TYPE_CODE = req_type_code_in AND
154 REQ.ENABLED_FLAG = 'Y' AND
155 REQ.PTE_CODE = SOU.PTE_CODE AND
156 SOU.ENABLED_FLAG = 'Y' AND
157 SOU.PTE_SOURCE_SYSTEM_ID = FNAREA.PTE_SOURCE_SYSTEM_ID AND
158 FNAREA.ENABLED_FLAG = 'Y' AND
159 FNAREA.FUNCTIONAL_AREA_ID = C.FUNCTIONAL_AREA_ID and
160 d.category_set_id = c.category_set_id and
161 d.hierarchy_enabled = 'Y' and
162 b.inventory_item_id = p_inventory_item_id and
163 b.organization_id = org_id_in and
164 b.category_set_id = d.category_set_id) x,
165 ENI_DENORM_HIERARCHIES A
166 WHERE A.OBJECT_TYPE = 'CATEGORY_SET' AND
167 A.OBJECT_ID = x.CATEGORY_SET_ID AND
168 a.child_id = x.category_id ;
169
170
171
172 BEGIN
173
174 l_appl_id := FND_GLOBAL.RESP_APPL_ID;
175
176 IF QP_ATTR_MAPPING_PUB.G_REQ_TYPE_CODE = 'ONT' THEN
177 --passing null org_id to OE_Sys_Parameters for moac so that it will return MASTER_ORGANIZATION_ID
178 --for the org_context set -- build_contexts API or calling app would have set 'single' org context
179 --added for moac to call oe_sys_params only when org_id is not null
180 l_ou_org_id := QP_UTIL.get_org_id;
181 IF l_ou_org_id IS NOT NULL THEN
182 l_org_id := OE_Sys_Parameters.Value('MASTER_ORGANIZATION_ID', l_ou_org_id);
183 ELSE -- get master org from QP profile value
184 l_org_id := FND_PROFILE.Value('QP_ORGANIZATION_ID');
185 END IF;
186 ELSE
187 l_org_id := FND_PROFILE.Value('QP_ORGANIZATION_ID');
188 END IF;
189
190 -- commented out for product hierarchy
191 /*
192 begin
193 --
194 select category_set_id
195 into l_category_set_id_11
196 from mtl_default_category_sets
197 where functional_area_id = 11 and
198 rownum < 2;
199 --
200 --if l_category_set_id_7 = l_category_set_id_11 then
201 select hierarchy_enabled
202 into l_hierarchy_enabled
203 from mtl_category_sets_b
204 where category_set_id = l_category_set_id_11 and
205 hierarchy_enabled = 'Y';
206 --
207 OPEN l_exploded_category_cursor(l_org_id,l_appl_id,
208 l_category_set_id_11);
209 LOOP
210 FETCH l_exploded_category_cursor INTO l_category_id;
211 EXIT WHEN l_exploded_category_cursor%NOTFOUND;
212
213 x_category_ids(v_count) := l_category_id;
214 v_count := v_count + 1;
215 END LOOP;
216 CLOSE l_exploded_category_cursor;
217 --else
218 OPEN l_category_cursor(l_org_id,l_appl_id);
219 LOOP
220 FETCH l_category_cursor INTO l_category_id;
221 EXIT WHEN l_category_cursor%NOTFOUND;
222
223 x_category_ids(v_count) := l_category_id;
224 v_count := v_count + 1;
225 END LOOP;
226 CLOSE l_category_cursor;
227 --end if;
228 exception
229 when others then
230 OPEN l_category_cursor(l_org_id,l_appl_id);
231 LOOP
232 FETCH l_category_cursor INTO l_category_id;
233 EXIT WHEN l_category_cursor%NOTFOUND;
234
235 x_category_ids(v_count) := l_category_id;
236 v_count := v_count + 1;
237 END LOOP;
238 CLOSE l_category_cursor;
239 end;
240 */
241 -- new changes for product heirarchy starts here
242 If qp_util.get_qp_status <>'I' THEN -- Basic pricing
243 OPEN l_category_cursor(l_org_id,QP_ATTR_MAPPING_PUB.G_REQ_TYPE_CODE);
244 LOOP
245 FETCH l_category_cursor INTO l_category_id;
246 EXIT WHEN l_category_cursor%NOTFOUND;
247
248 x_category_ids(v_count) := l_category_id;
249 v_count := v_count + 1;
250 END LOOP;
251 CLOSE l_category_cursor;
252
253 ELSE -- Advanced pricing has product heirarchy feature
254 OPEN l_exploded_category_cursor(l_org_id, QP_ATTR_MAPPING_PUB.G_REQ_TYPE_CODE);
255 LOOP
256
257 --bug 8805312
258 -- Commented code to do bulk collect instead of individual fetches.
259 /*FETCH l_exploded_category_cursor INTO l_category_id;
260 EXIT WHEN l_exploded_category_cursor%NOTFOUND;
261
262 x_category_ids(v_count) := l_category_id;
263 v_count := v_count + 1;*/
264
265 l_int_category_ids.delete;
266 FETCH l_exploded_category_cursor BULK COLLECT INTO l_int_category_ids
267 LIMIT 1000;
268
269 EXIT WHEN l_int_category_ids.Count = 0;
270
271 FOR i IN l_int_category_ids.first..l_int_category_ids.last
272 LOOP
273 x_category_ids(v_count) := l_int_category_ids(i);
274 v_count := v_count + 1;
275 END LOOP;
276
277 --end
278
279 END LOOP;
280 CLOSE l_exploded_category_cursor;
281 OPEN l_category_cursor(l_org_id,QP_ATTR_MAPPING_PUB.G_REQ_TYPE_CODE);
282 LOOP
283
284 --bug 8805312
285 -- Commented code to do bulk collect instead of individual fetches.
286 /* FETCH l_category_cursor INTO l_category_id;
287 EXIT WHEN l_category_cursor%NOTFOUND;
288
289 x_category_ids(v_count) := l_category_id;
290 v_count := v_count + 1;*/
291
292 l_int_category_ids.delete;
293
294 FETCH l_category_cursor BULK COLLECT INTO l_int_category_ids
295 LIMIT 1000;
296
297 EXIT WHEN l_int_category_ids.Count = 0;
298
299 FOR i IN l_int_category_ids.first..l_int_category_ids.last
300 LOOP
301 x_category_ids(v_count) := l_int_category_ids(i);
302 v_count := v_count + 1;
303 END LOOP;
304
305 --end
306
307 END LOOP;
308 CLOSE l_category_cursor;
309 END IF; -- end advacned pricing
310 RETURN x_category_ids;
311
312 END Get_Item_Category;
313
314 /*
315 * Commented out for bug 4753707
316 *
317 FUNCTION Validate_UOM (p_org_id IN NUMBER,
318 p_category_id IN NUMBER,
319 p_product_uom_code IN VARCHAR2)
320 RETURN VARCHAR2
321 IS
322 l_dummy_2 VARCHAR2(3);
323 l_category_set_id NUMBER; -- 11i10 Product Catalog
324 BEGIN
325 -- get category set ID for hierarchical categories (e.g., Product Catalog 11i10)
326 select category_set_id
327 into l_category_set_id
328 from mtl_default_category_sets
329 where functional_area_id = 11 and rownum < 2;
330
331 /* this SQL produces cartesian joins
332 rewriting to use base tables as part of SQL repository fix
333 select distinct uom_code
334 into l_dummy_2
335 from mtl_item_uoms_view
336 where (organization_id = p_org_id or p_org_id is null)
337 and uom_code = p_product_uom_code
338 and inventory_item_id in
339 ...
340 *--/
341 select MTLUOM2.uom_code
342 into l_dummy_2
343 from MTL_SYSTEM_ITEMS_B MTLITM1,
344 MTL_UNITS_OF_MEASURE_VL MTLUOM2,
345 MTL_UOM_CONVERSIONS MTLUCV
346 where ((MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (1,3)
347 AND MTLUCV.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
348 OR ( MTLUCV.INVENTORY_ITEM_ID = 0
349 AND MTLUOM2.BASE_UOM_FLAG = 'Y'
350 AND MTLUOM2.UOM_CLASS = MTLUCV.UOM_CLASS
351 AND MTLUCV.UOM_CLASS IN
352 ( SELECT MTLPRI1.UOM_CLASS
353 FROM MTL_UNITS_OF_MEASURE MTLPRI1
354 WHERE MTLPRI1.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE) )
355 OR (MTLUCV.INVENTORY_ITEM_ID = 0
356 AND MTLUCV.UOM_CODE IN
357 (SELECT MTLUCC1.TO_UOM_CODE
358 FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC1
359 WHERE MTLUCC1.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
360 AND NVL(MTLUCC1.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) ) ) )
361 OR
362 (MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (2,3)
363 AND MTLUCV.INVENTORY_ITEM_ID = 0
364 AND ( MTLUCV.UOM_CLASS IN
365 (SELECT MTLUCC.TO_UOM_CLASS
366 FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC
367 WHERE MTLUCC.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
368 AND NVL(MTLUCC.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE))
369 OR MTLUCV.UOM_CLASS =
370 (SELECT MTLPRI.UOM_CLASS
371 FROM MTL_UNITS_OF_MEASURE MTLPRI
372 WHERE MTLPRI.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE)) ) )
373 AND NVL(MTLUCV.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
374 AND NVL(MTLUOM2.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
375 AND MTLUOM2.UOM_CODE = MTLUCV.UOM_CODE
376 AND (MTLITM1.organization_id = p_org_id or p_org_id is null)
377 AND MTLUOM2.uom_code = p_product_uom_code
378 AND MTLUCV.inventory_item_id in
379 (select inventory_item_id
380 from mtl_item_categories
381 where category_id = p_category_id
382 and (organization_id = p_org_id or p_org_id is null)
383 UNION
384 -- for Product Catalog 11i10
385 select inventory_item_id
386 from eni_denorm_hierarchies eni,
387 mtl_item_categories mtl,
388 mtl_default_category_sets sets
389 where mtl.category_set_id = eni.object_id
390 and mtl.category_set_id = l_category_set_id
391 and eni.object_type = 'CATEGORY_SET'
392 and eni.object_id = sets.category_set_id
393 and mtl.category_id = eni.child_id
394 and sets.functional_area_id = decode(FND_GLOBAL.RESP_APPL_ID, 201,
395 2, 11)
396 and (mtl.organization_id = p_org_id or p_org_id IS NULL)
397 and eni.parent_id = p_category_id)
398 and rownum=1;
399
400 RETURN 'Y';
401 EXCEPTION
402 WHEN NO_DATA_FOUND THEN
403 RETURN 'N';
404 END Validate_UOM;
405 *
406 */
407
408 END QP_CATEGORY_MAPPING_RULE;