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