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