[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select t.conversion_rate std_to_rate,
t.uom_class std_to_class,
f.conversion_rate std_from_rate,
f.uom_class std_from_class
from mtl_uom_conversions t,
mtl_uom_conversions f
where t.inventory_item_id in (item_id, 0)
and t.uom_code = to_unit
and nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
and f.inventory_item_id in (item_id, 0)
and f.uom_code = from_unit
and nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
order by t.inventory_item_id desc,
f.inventory_item_id desc;
select conversion_rate std_from_rate,
uom_class std_from_class
from mtl_uom_conversions
where inventory_item_id in (item_id, 0)
and uom_code = from_unit
and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
order by inventory_item_id desc;
select conversion_rate std_to_rate,
uom_class std_to_class
from mtl_uom_conversions
where inventory_item_id in (item_id, 0)
and uom_code = to_unit
and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
order by inventory_item_id desc;
select decode(to_uom_class, to_class, 1, 2) to_flag,
decode(from_uom_class, from_class, 1, to_class, 2, 0) from_flag,
conversion_rate rate
from mtl_uom_class_conversions
where inventory_item_id = item_id
and to_uom_class in (from_class, to_class)
and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
select decode(to_uom_class, to_class, 1, 2) to_flag,
decode(from_uom_class, from_class, 1, to_class, 2, 0) from_flag,
conversion_rate rate
from (
select from_uom_class, to_uom_class , conversion_rate
from mtl_lot_uom_class_conversions
where inventory_item_id = item_id
and organization_id = p_organization_id
and lot_number = p_lot_number
and to_uom_class in (from_class, to_class)
and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
union all
(
select from_uom_class, to_uom_class , conversion_rate
from mtl_uom_class_conversions mucc
where inventory_item_id = item_id
and to_uom_class in (from_class, to_class)
and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
and not exists (
select 1
from mtl_lot_uom_class_conversions mluc
where inventory_item_id = item_id
and organization_id = p_organization_id
and lot_number = p_lot_number
and to_uom_class in (from_class, to_class)
and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
and mluc.from_uom_class = mucc.from_uom_class
and mluc.to_uom_class = mucc.to_uom_class
)
)
) ;
SELECT uom_code INTO fm_unt FROM mtl_units_of_measure
WHERE unit_of_measure = from_name;
SELECT uom_code INTO to_unt FROM mtl_units_of_measure
WHERE unit_of_measure = to_name;
SELECT uom_code INTO fm_unt FROM mtl_units_of_measure
WHERE unit_of_measure = from_name;
SELECT uom_code INTO to_unt FROM mtl_units_of_measure
WHERE unit_of_measure = to_name;
select PRIMARY_UOM_CODE, ALLOWED_UNITS_LOOKUP_CODE
from mtl_system_items msi,
MTL_UNITS_OF_MEASURE MTLUOM2
where msi.ORGANIZATION_ID = p_organization_id AND
msi.INVENTORY_ITEM_ID = p_item_id AND
MTLUOM2.uom_code = msi.PRIMARY_UOM_CODE AND
NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE);
select MTLUOM2.uom_code, MTLUCV.uom_class
from MTL_UNITS_OF_MEASURE MTLUOM2,
MTL_UOM_CONVERSIONS MTLUCV,
MTL_UOM_CLASSES MTLCLS
where
MTLUOM2.uom_code = p_uom_code AND
MTLUCV.uom_code = MTLUOM2.uom_code AND
MTLUCV.inventory_item_id=0 AND
MTLCLS.uom_class = MTLUOM2.uom_class AND
NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
MTLUCV.uom_class = (select MTLPRI1.uom_class
from MTL_UNITS_OF_MEASURE MTLPRI1
where MTLPRI1.uom_code = l_primary_uom_code AND
NVL(MTLPRI1.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
);
select MTLUOM2.uom_code, MTLUCV.uom_class
from MTL_UNITS_OF_MEASURE MTLUOM2,
MTL_UOM_CONVERSIONS MTLUCV,
MTL_UOM_CLASSES MTLCLS
where MTLUOM2.uom_code = p_uom_code AND
MTLUCV.uom_code = MTLUOM2.uom_code AND
MTLUCV.inventory_item_id = p_item_id AND
MTLCLS.uom_class = MTLUOM2.uom_class AND
NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE);
select MTLUOM2.uom_code, MTLUOM2.uom_class
from MTL_UNITS_OF_MEASURE MTLUOM2,
MTL_UOM_CONVERSIONS MTLUCV,
MTL_UOM_CLASSES MTLCLS
where
MTLUOM2.uom_code = p_uom_code AND
MTLUCV.uom_code = MTLUOM2.uom_code AND
MTLCLS.uom_class = MTLUOM2.uom_class AND
NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
l_allowed_units in (1,3) AND MTLUCV.inventory_item_id = p_item_id
UNION ALL
select MTLUOM2.uom_code, MTLUOM2.uom_class
from MTL_UNITS_OF_MEASURE MTLUOM2,
MTL_UOM_CONVERSIONS MTLUCV,
MTL_UOM_CLASSES MTLCLS
where
MTLUOM2.uom_code = p_uom_code AND
MTLUCV.uom_code = MTLUOM2.uom_code AND
MTLCLS.uom_class = MTLUOM2.uom_class AND
NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
l_allowed_units in (1,3) AND MTLUCV.inventory_item_id=0 AND
MTLUCV.uom_class = (select MTLPRI1.uom_class
from MTL_UNITS_OF_MEASURE MTLPRI1
where MTLPRI1.uom_code = l_primary_uom_code
)
UNION ALL
select MTLUOM2.uom_code, MTLUOM2.uom_class
from MTL_UNITS_OF_MEASURE MTLUOM2,
MTL_UOM_CONVERSIONS MTLUCV,
MTL_UOM_CLASSES MTLCLS
where
MTLUOM2.uom_code = p_uom_code AND
MTLUCV.uom_code = MTLUOM2.uom_code AND
MTLCLS.uom_class = MTLUOM2.uom_class AND
NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
l_allowed_units in (1,3) AND MTLUCV.inventory_item_id=0 AND
exists(
select 'UOM_CLASS conversion exists for the class of UOM supplied'
from MTL_UOM_CLASS_CONVERSIONS MTLUCC1
where
MTLUCC1.to_uom_class = MTLUCV.uom_class AND
MTLUCC1.inventory_item_id = p_item_id AND
NVL(MTLUCC1.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
)
UNION ALL
select MTLUOM2.uom_code, MTLUOM2.uom_class
from MTL_UNITS_OF_MEASURE MTLUOM2,
MTL_UOM_CONVERSIONS MTLUCV,
MTL_UOM_CLASSES MTLCLS
where
MTLUOM2.uom_code = p_uom_code AND
MTLUCV.uom_code = MTLUOM2.uom_code AND
MTLCLS.uom_class = MTLUOM2.uom_class AND
NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
l_allowed_units in (2,3) AND MTLUCV.inventory_item_id=0 AND
exists(
select 'UOM_CLASS conversion exists for the class of UOM supplied'
from MTL_UOM_CLASS_CONVERSIONS MTLUCC
where
MTLUCC.to_uom_class = MTLUCV.uom_class AND
MTLUCC.INVENTORY_ITEM_ID = p_item_id AND
NVL(MTLUCC.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
)
UNION ALL
select MTLUOM2.uom_code, MTLUOM2.uom_class
from MTL_UNITS_OF_MEASURE MTLUOM2,
MTL_UOM_CONVERSIONS MTLUCV,
MTL_UOM_CLASSES MTLCLS
where
MTLUOM2.uom_code = p_uom_code AND
MTLUCV.uom_code = MTLUOM2.uom_code AND
MTLCLS.uom_class = MTLUOM2.uom_class AND
NVL(MTLCLS.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUOM2.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
NVL(MTLUCV.DISABLE_DATE,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) AND
l_allowed_units in (2,3) AND MTLUCV.inventory_item_id=0 AND
MTLUCV.uom_class = (select MTLPRI.uom_class
from MTL_UNITS_OF_MEASURE MTLPRI
where MTLPRI.uom_code = l_primary_uom_code
);
SELECT pick_uom_code, inv_ui_item_lovs.get_conversion_rate(pick_uom_code, p_org_Id, p_item_Id)
INTO l_loc_uom, l_uom_string
FROM mtl_item_locations
WHERE organization_id = p_org_id
AND subinventory_code = p_sub_code
AND inventory_location_id = p_loc_id;
SELECT pick_uom_code, inv_ui_item_lovs.get_conversion_rate(pick_uom_code,
p_org_Id,
p_item_Id)
INTO l_loc_uom, l_uom_string
FROM MTL_SECONDARY_INVENTORIES
WHERE secondary_inventory_name = p_sub_code
AND organization_id = p_org_id;
SELECT secondary_transaction_quantity, SECONDARY_UOM_CODE, NVL(fulfillment_base, 'P')
INTO l_mmtt_sec_qty, l_mmtt_sec_uom, l_fulfillment_base
FROM mtl_material_transactions_temp
WHERE organization_id = p_org_id
AND transaction_temp_id = p_temp_id;
SELECT pick_uom_code, inv_ui_item_lovs.get_conversion_rate(pick_uom_code, p_org_Id, p_item_Id, l_fulfillment_base)
INTO l_loc_uom, l_uom_string
FROM mtl_item_locations
WHERE organization_id = p_org_id
AND subinventory_code = p_sub_code
AND inventory_location_id = p_loc_id;
SELECT pick_uom_code, inv_ui_item_lovs.get_conversion_rate(pick_uom_code,
p_org_Id,
p_item_Id,
l_fulfillment_base)
INTO l_loc_uom, l_uom_string
FROM MTL_SECONDARY_INVENTORIES
WHERE secondary_inventory_name = p_sub_code
AND organization_id = p_org_id;
SELECT 'Y'
INTO l_uom_in_same_class
FROM mtl_system_items msi ,
mtl_item_uoms_view mv1,
mtl_item_uoms_view mv2
WHERE msi.organization_id = p_organization_id
AND msi.inventory_item_id = p_item_id
AND msi.inventory_item_id = mv1.inventory_item_id
AND msi.organization_id = mv1.organization_id
AND mv1.organization_id = mv2.organization_id
AND mv1.inventory_item_id = mv2.inventory_item_id
AND mv1.uom_code = p_uom
AND mv2.uom_code = msi.secondary_uom_code
AND mv1.uom_class = mv2.uom_class
AND ROWNUM = 1;
SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_unit;
SELECT unit_of_measure , uom_class , base_uom_flag
INTO l_from_unit_of_measure , l_from_class , l_from_base_uom_flag
FROM MTL_UNITS_OF_MEASURE_VL
WHERE uom_code = p_from_uom_code
AND nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
SELECT unit_of_measure , uom_class , base_uom_flag
INTO l_to_unit_of_measure ,l_to_class , l_to_base_uom_flag
FROM MTL_UNITS_OF_MEASURE_VL
WHERE uom_code = p_to_uom_code
AND nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
SELECT DISTINCT inventory_item_id
INTO l_temp_item_id
FROM mtl_system_items_vl
WHERE inventory_item_id = p_item_id
AND inventory_item_id IN (SELECT DISTINCT I.inventory_item_id FROM mtl_system_items_vl I
WHERE I.enabled_flag = 'Y'
AND (SYSDATE BETWEEN NVL(TRUNC(I.start_date_active),SYSDATE )
AND NVL(TRUNC(I.end_date_active),SYSDATE))
AND ( EXISTS (SELECT A.unit_of_measure FROM mtl_units_of_measure A
WHERE (A.uom_class IN (SELECT to_uom_class FROM mtl_uom_class_conversions B
WHERE B.inventory_item_id = I.inventory_item_id)
OR A.uom_class = (SELECT Z.uom_class FROM mtl_units_of_measure Z
WHERE Z.uom_code = I.primary_uom_code))
AND A.base_uom_flag <> 'Y'
AND NVL(A.disable_date, SYSDATE+1) > SYSDATE
AND A.uom_class = NVL(l_to_class, A.uom_class))));
SELECT DISTINCT x.uom_code
INTO l_temp_uom
FROM mtl_units_of_measure x
WHERE x.uom_code = p_to_uom_code
AND x.uom_code IN (SELECT DISTINCT a.uom_code FROM mtl_units_of_measure a
WHERE (a.uom_class in (select to_uom_class
from mtl_uom_class_conversions b
where b.inventory_item_id = p_item_id)
or a.uom_class =(select DISTINCT z.uom_class
from mtl_units_of_measure z , mtl_system_items_vl m
where m.inventory_item_id = p_item_id
AND z.uom_code = m.primary_uom_code
))
and a.base_uom_flag <> 'Y'
and nvl(a.disable_date,sysdate+1) > SYSDATE);
SELECT 'Y'
INTO l_conversion_exists
FROM mtl_uom_conversions
WHERE inventory_item_id = p_item_id
AND uom_code = p_to_uom_code ;
SELECT 'Y'
INTO l_conversion_exists
FROM mtl_uom_conversions
WHERE inventory_item_id = 0
AND uom_code = p_to_uom_code ;
INSERT INTO mtl_uom_conversions
(inventory_item_id,
unit_of_measure,
uom_code,
uom_class,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
conversion_rate,
default_conversion_flag)
VALUES (p_item_id,
l_to_unit_of_measure,
p_to_uom_code,
l_to_class,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
-1,
p_uom_rate,
'N');
SELECT DISTINCT inventory_item_id , primary_uom_code INTO l_temp_item_id , l_primary_uom_code
FROM mtl_system_items_vl
WHERE inventory_item_id = p_item_id
AND inventory_item_id IN (SELECT DISTINCT I.inventory_item_id FROM mtl_system_items_vl I
WHERE I.enabled_flag = 'Y'
AND (SYSDATE BETWEEN NVL(TRUNC(I.start_date_active),SYSDATE )
AND NVL(TRUNC(I.end_date_active),SYSDATE))
AND ( EXISTS (SELECT A.unit_of_measure FROM mtl_units_of_measure A
WHERE (A.uom_class <> (SELECT R.uom_class FROM mtl_units_of_measure R
WHERE R.uom_code = I.primary_uom_code))
AND A.base_uom_flag = 'Y'
AND NVL(A.disable_date, SYSDATE+1) > SYSDATE
AND A.uom_class = NVL(l_to_class,A.uom_class))));
SELECT 'Y'
INTO l_conversion_exists
FROM mtl_uom_class_conversions
WHERE inventory_item_id = p_item_id
AND to_uom_code = p_to_uom_code ;
INSERT INTO mtl_uom_class_conversions
(inventory_item_id,
from_unit_of_measure,
from_uom_code,
from_uom_class,
to_unit_of_measure,
to_uom_code,
to_uom_class,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
conversion_rate)
VALUES (p_item_id,
l_from_unit_of_measure,
p_from_uom_code,
l_from_class,
l_to_unit_of_measure,
p_to_uom_code,
l_to_class,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
-1,
p_uom_rate);