[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.category_id
FROM mtl_item_categories a,
mtl_categories_b b,
mtl_default_category_sets c,
mtl_category_sets_b d
WHERE a.inventory_item_id = p_inventory_item_id
AND a.organization_id = org_id_in
AND a.category_id = b.category_id
AND c.category_set_id = d.category_set_id
AND c.functional_area_id = decode(appl_id_in,201,2,7)
AND b.structure_id = d.structure_id;
SELECT parent_id
FROM eni_denorm_hierarchies a,
mtl_item_categories b,
mtl_default_category_sets c
WHERE inventory_item_id = p_inventory_item_id and
organization_id = org_id_in and
a.object_type = 'CATEGORY_SET' and
a.object_id = c.category_set_id and
b.category_id = child_id and
functional_area_id = decode(appl_id_in,201,2,7);
SELECT parent_id
FROM eni_denorm_hierarchies a,
mtl_item_categories b,
mtl_default_category_sets c
WHERE inventory_item_id = p_inventory_item_id and
organization_id = org_id_in and
b.category_set_id = category_set_id_in and
a.object_type = 'CATEGORY_SET' and
a.object_id = c.category_set_id and
b.category_id = child_id and
functional_area_id = decode(appl_id_in,201,2,11);
SELECT distinct /*+ ordered use_nl(b c d) */ /*a.category_id
FROM mtl_item_categories a,
mtl_categories_b b,
mtl_default_category_sets c,
mtl_category_sets_b d
WHERE a.inventory_item_id = p_inventory_item_id
AND a.organization_id = org_id_in
AND a.category_id = b.category_id
AND c.category_set_id = d.category_set_id
AND b.structure_id = d.structure_id
AND nvl(d.hierarchy_enabled,'N') = 'N'
AND c.functional_area_id in (SELECT distinct FNAREA.FUNCTIONAL_AREA_ID
FROM QP_PTE_REQUEST_TYPES_B REQ,
QP_PTE_SOURCE_SYSTEMS SOU,
QP_SOURCESYSTEM_FNAREA_MAP FNAREA
WHERE REQ.REQUEST_TYPE_CODE = req_type_code_in and REQ.ENABLED_FLAG = 'Y' and
REQ.PTE_CODE = SOU.PTE_CODE and SOU.ENABLED_FLAG = 'Y'and
SOU.PTE_SOURCE_SYSTEM_ID = FNAREA.PTE_SOURCE_SYSTEM_ID and
FNAREA.ENABLED_FLAG = 'Y');*/
SELECT /*+ leading(a,b,d,c) use_nl(b c d) */ DISTINCT A.CATEGORY_ID
FROM MTL_ITEM_CATEGORIES A,
MTL_CATEGORIES_B B,
MTL_DEFAULT_CATEGORY_SETS C,
MTL_CATEGORY_SETS_B D
WHERE A.INVENTORY_ITEM_ID = p_inventory_item_id
AND A.ORGANIZATION_ID = org_id_in
AND A.CATEGORY_ID = B.CATEGORY_ID
AND A.CATEGORY_SET_ID = D.CATEGORY_SET_ID --Bug#11719249
AND C.CATEGORY_SET_ID = D.CATEGORY_SET_ID
AND B.STRUCTURE_ID = D.STRUCTURE_ID
AND NVL(D.HIERARCHY_ENABLED,'N') = 'N'
AND C.FUNCTIONAL_AREA_ID IN (SELECT /*+ no_unnest */ DISTINCT FNAREA.FUNCTIONAL_AREA_ID
FROM QP_PTE_REQUEST_TYPES_B REQ,
QP_PTE_SOURCE_SYSTEMS SOU,
QP_SOURCESYSTEM_FNAREA_MAP FNAREA
WHERE REQ.REQUEST_TYPE_CODE = req_type_code_in
AND REQ.ENABLED_FLAG = 'Y'
AND REQ.PTE_CODE = SOU.PTE_CODE
AND SOU.ENABLED_FLAG = 'Y'
AND SOU.PTE_SOURCE_SYSTEM_ID = FNAREA.PTE_SOURCE_SYSTEM_ID
AND FNAREA.ENABLED_FLAG = 'Y');
SELECT distinct /*+ ORDERED USE_NL(c d b a) */ /*a.parent_id
FROM mtl_default_category_sets c,
mtl_category_sets_b d,
mtl_item_categories b,
eni_denorm_hierarchies a
WHERE b.inventory_item_id = p_inventory_item_id and
b.organization_id = org_id_in and
b.category_set_id = c.category_set_id and
a.object_type = 'CATEGORY_SET' and
c.category_set_id = d.category_set_id and
d.hierarchy_enabled = 'Y' and
a.object_id = c.category_set_id and
b.category_id = a.child_id and
c.functional_area_id in (SELECT /*+ ORDERED USE_NL(REQ SOU FNAREA)*//*
distinct FNAREA.FUNCTIONAL_AREA_ID
FROM QP_PTE_REQUEST_TYPES_B REQ,
QP_PTE_SOURCE_SYSTEMS SOU,
QP_SOURCESYSTEM_FNAREA_MAP FNAREA
WHERE REQ.REQUEST_TYPE_CODE = req_type_code_in
and REQ.ENABLED_FLAG = 'Y'
and REQ.PTE_CODE = SOU.PTE_CODE
and SOU.ENABLED_FLAG = 'Y'
and SOU.PTE_SOURCE_SYSTEM_ID =
FNAREA.PTE_SOURCE_SYSTEM_ID
and FNAREA.ENABLED_FLAG = 'Y');*/
select /*+ leading(x,A) no_merge(x) use_nl_with_index(a ENI_DENORM_HIERARCHIES_N2) */ A.PARENT_ID --bug 8924817
from (select /*+ ordered use_nl(req sou fnarea c d b) */ distinct b.category_id,
b.category_set_id
from QP_PTE_REQUEST_TYPES_B REQ,
QP_PTE_SOURCE_SYSTEMS SOU,
QP_SOURCESYSTEM_FNAREA_MAP FNAREA,
MTL_DEFAULT_CATEGORY_SETS C,
mtl_category_sets_b d,
mtl_item_categories b
WHERE REQ.REQUEST_TYPE_CODE = req_type_code_in AND
REQ.ENABLED_FLAG = 'Y' AND
REQ.PTE_CODE = SOU.PTE_CODE AND
SOU.ENABLED_FLAG = 'Y' AND
SOU.PTE_SOURCE_SYSTEM_ID = FNAREA.PTE_SOURCE_SYSTEM_ID AND
FNAREA.ENABLED_FLAG = 'Y' AND
FNAREA.FUNCTIONAL_AREA_ID = C.FUNCTIONAL_AREA_ID and
d.category_set_id = c.category_set_id and
d.hierarchy_enabled = 'Y' and
b.inventory_item_id = p_inventory_item_id and
b.organization_id = org_id_in and
b.category_set_id = d.category_set_id) x,
ENI_DENORM_HIERARCHIES A
WHERE A.OBJECT_TYPE = 'CATEGORY_SET' AND
A.OBJECT_ID = x.CATEGORY_SET_ID AND
a.child_id = x.category_id ;
select category_set_id
into l_category_set_id_11
from mtl_default_category_sets
where functional_area_id = 11 and
rownum < 2;
select hierarchy_enabled
into l_hierarchy_enabled
from mtl_category_sets_b
where category_set_id = l_category_set_id_11 and
hierarchy_enabled = 'Y';
l_int_category_ids.delete;
l_int_category_ids.delete;
select category_set_id
into l_category_set_id
from mtl_default_category_sets
where functional_area_id = 11 and rownum < 2;
select distinct uom_code
into l_dummy_2
from mtl_item_uoms_view
where (organization_id = p_org_id or p_org_id is null)
and uom_code = p_product_uom_code
and inventory_item_id in
...
*--/
select MTLUOM2.uom_code
into l_dummy_2
from MTL_SYSTEM_ITEMS_B MTLITM1,
MTL_UNITS_OF_MEASURE_VL MTLUOM2,
MTL_UOM_CONVERSIONS MTLUCV
where ((MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (1,3)
AND MTLUCV.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
OR ( MTLUCV.INVENTORY_ITEM_ID = 0
AND MTLUOM2.BASE_UOM_FLAG = 'Y'
AND MTLUOM2.UOM_CLASS = MTLUCV.UOM_CLASS
AND MTLUCV.UOM_CLASS IN
( SELECT MTLPRI1.UOM_CLASS
FROM MTL_UNITS_OF_MEASURE MTLPRI1
WHERE MTLPRI1.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE) )
OR (MTLUCV.INVENTORY_ITEM_ID = 0
AND MTLUCV.UOM_CODE IN
(SELECT MTLUCC1.TO_UOM_CODE
FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC1
WHERE MTLUCC1.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
AND NVL(MTLUCC1.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) ) ) )
OR
(MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (2,3)
AND MTLUCV.INVENTORY_ITEM_ID = 0
AND ( MTLUCV.UOM_CLASS IN
(SELECT MTLUCC.TO_UOM_CLASS
FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC
WHERE MTLUCC.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
AND NVL(MTLUCC.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE))
OR MTLUCV.UOM_CLASS =
(SELECT MTLPRI.UOM_CLASS
FROM MTL_UNITS_OF_MEASURE MTLPRI
WHERE MTLPRI.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE)) ) )
AND NVL(MTLUCV.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
AND NVL(MTLUOM2.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
AND MTLUOM2.UOM_CODE = MTLUCV.UOM_CODE
AND (MTLITM1.organization_id = p_org_id or p_org_id is null)
AND MTLUOM2.uom_code = p_product_uom_code
AND MTLUCV.inventory_item_id in
(select inventory_item_id
from mtl_item_categories
where category_id = p_category_id
and (organization_id = p_org_id or p_org_id is null)
UNION
-- for Product Catalog 11i10
select inventory_item_id
from eni_denorm_hierarchies eni,
mtl_item_categories mtl,
mtl_default_category_sets sets
where mtl.category_set_id = eni.object_id
and mtl.category_set_id = l_category_set_id
and eni.object_type = 'CATEGORY_SET'
and eni.object_id = sets.category_set_id
and mtl.category_id = eni.child_id
and sets.functional_area_id = decode(FND_GLOBAL.RESP_APPL_ID, 201,
2, 11)
and (mtl.organization_id = p_org_id or p_org_id IS NULL)
and eni.parent_id = p_category_id)
and rownum=1;