[Home] [Help]
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
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
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
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
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
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
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
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
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
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