[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_uom(
p_uom_rec IN mtl_units_of_measure%ROWTYPE
) IS
l_count NUMBER := 0;
-- this condition is not accounted in the NOT EXISTS clause of the insert
select count(*)
into l_count
from mtl_units_of_measure
where (uom_code = p_uom_rec.uom_code and unit_of_measure <> p_uom_rec.unit_of_measure)
or (uom_code <> p_uom_rec.uom_code and unit_of_measure = p_uom_rec.unit_of_measure);
SELECT COUNT(*)
INTO l_count
FROM mtl_units_of_measure
WHERE base_uom_flag = 'Y'
AND uom_class = p_uom_rec.uom_class;
g_action := 'UOM insert';
INSERT INTO mtl_units_of_measure_tl (
unit_of_measure,
unit_of_measure_tl,
uom_code,
uom_class,
base_uom_flag,
disable_date,
description,
last_update_date,
last_updated_by,
creation_date,
created_by,
language,
source_lang
) SELECT p_uom_rec.unit_of_measure,
p_uom_rec.unit_of_measure,
p_uom_rec.uom_code,
p_uom_rec.uom_class,
p_uom_rec.base_uom_flag,
p_uom_rec.disable_date,
p_uom_rec.description,
p_uom_rec.last_update_date,
p_uom_rec.last_updated_by,
p_uom_rec.creation_date,
p_uom_rec.created_by,
l.language_code,
USERENV('LANG')
FROM FND_LANGUAGES l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS (
SELECT NULL
FROM mtl_units_of_measure_tl t
WHERE (t.unit_of_measure = p_uom_rec.unit_of_measure
OR t.uom_code = p_uom_rec.uom_code)
AND t.language = l.language_code);
END insert_uom;
PROCEDURE delete_uom(
p_uom_rec IN mtl_units_of_measure%ROWTYPE
) IS
BEGIN
g_action := 'UOM disable';
UPDATE mtl_units_of_measure_tl
SET disable_date = p_uom_rec.disable_date,
last_update_date = p_uom_rec.last_update_date,
last_updated_by = p_uom_rec.last_updated_by,
--unit_of_measure_tl = p_uom_rec.unit_of_measure_tl, /*null update fails?*/
source_lang = USERENV('LANG')
WHERE unit_of_measure = p_uom_rec.unit_of_measure
AND uom_code = p_uom_rec.uom_code
AND uom_class = p_uom_rec.uom_class
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
END delete_uom;
PROCEDURE update_uom(
p_uom_rec IN mtl_units_of_measure%ROWTYPE
) IS
BEGIN
g_action := 'UOM update';
UPDATE mtl_units_of_measure_tl
SET description = p_uom_rec.description,
disable_date = p_uom_rec.disable_date,
--unit_of_measure_tl = p_uom_rec.unit_of_measure_tl, /*null update?*/
source_lang = USERENV('LANG'),
last_update_date = p_uom_rec.last_update_date,
last_updated_by = p_uom_rec.last_updated_by
WHERE unit_of_measure = p_uom_rec.unit_of_measure
AND uom_code = p_uom_rec.uom_code
AND uom_class = p_uom_rec.uom_class
AND USERENV('LANG') IN (language, source_lang);
END update_uom;
SELECT disable_date
FROM mtl_uom_classes
WHERE uom_class = p_uom_class;
SELECT base_uom_flag
INTO l_base
FROM mtl_units_of_measure
WHERE unit_of_measure = p_uom
AND uom_code = p_uom_code
AND uom_class = p_uom_class;
SELECT 1
INTO l_dummy
FROM mtl_uom_conversions
WHERE uom_code = p_uom_code
AND uom_class = p_uom_class
AND inventory_item_id = 0
AND conversion_rate = TO_NUMBER(p_conv_rate);
SELECT conversion_rate
INTO l_conv_rate
FROM mtl_uom_conversions
WHERE uom_code = p_uom_code AND inventory_item_id = p_itemid;
SELECT m2.uom_code
INTO l_base_uom_code
FROM mtl_units_of_measure m1,
mtl_units_of_measure m2
WHERE m1.uom_code = p_uom_code
AND m1.uom_class = m2.uom_class
AND m2.base_uom_flag = 'Y';
PROCEDURE delete_uom_class(
p_cls_rec IN mtl_uom_classes%ROWTYPE
) IS
BEGIN
g_action := 'UOMClass parameter validation';
UPDATE mtl_uom_classes_tl
SET disable_date = p_cls_rec.disable_date,
last_update_date = p_cls_rec.last_update_date,
last_updated_by = p_cls_rec.last_updated_by,
--uom_class_tl = p_cls_rec.uom_class_tl, /*null update fails*/
source_lang = USERENV('LANG')
WHERE uom_class = p_cls_rec.uom_class
AND USERENV('LANG') IN (language, source_lang);
END delete_uom_class;
PROCEDURE update_uom_class(
p_cls_rec IN mtl_uom_classes%ROWTYPE
) IS
BEGIN
g_action := 'UOMClass update';
UPDATE mtl_uom_classes_tl
SET description = p_cls_rec.description,
disable_date = p_cls_rec.disable_date,
last_update_date = p_cls_rec.last_update_date,
last_updated_by = p_cls_rec.last_updated_by,
--uom_class_tl = p_cls_rec.uom_class_tl, /*null update fails*/
source_lang = USERENV('LANG')
WHERE uom_class = p_cls_rec.uom_class
AND USERENV('LANG') IN (language, source_lang);
END update_uom_class;
PROCEDURE insert_uom_class(
p_cls_rec IN mtl_uom_classes%ROWTYPE
) IS
BEGIN
g_action := 'UOMClass insert';
INSERT INTO mtl_uom_classes_tl (
uom_class,
uom_class_tl,
disable_date,
description,
last_update_date,
last_updated_by,
creation_date,
created_by,
language,
source_lang
) SELECT p_cls_rec.uom_class,
p_cls_rec.uom_class,
p_cls_rec.disable_date,
p_cls_rec.description,
p_cls_rec.last_update_date,
p_cls_rec.last_updated_by,
p_cls_rec.creation_date,
p_cls_rec.created_by,
l.language_code,
USERENV('LANG')
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS
(SELECT NULL
FROM mtl_uom_classes_tl t
WHERE t.uom_class = p_cls_rec.uom_class
AND t.language = l.language_code);
END insert_uom_class;
PROCEDURE delete_uom_class_conv(
p_ccv_rec IN mtl_uom_class_conversions%ROWTYPE
) IS
BEGIN
g_action := 'UOMClass-conversion parameter validation';
g_action := 'UOMClassConversion delete';
UPDATE mtl_uom_class_conversions
SET disable_date = p_ccv_rec.disable_date,
last_update_date = p_ccv_rec.last_update_date,
last_updated_by = p_ccv_rec.last_updated_by
WHERE from_unit_of_measure = p_ccv_rec.from_unit_of_measure
AND from_uom_code = p_ccv_rec.from_uom_code
AND from_uom_class = p_ccv_rec.from_uom_class
AND to_unit_of_measure = p_ccv_rec.to_unit_of_measure
AND to_uom_code = p_ccv_rec.to_uom_code
AND to_uom_class = p_ccv_rec.to_uom_class;
END delete_uom_class_conv;
PROCEDURE update_uom_class_conv(
p_ccv_rec IN mtl_uom_class_conversions%ROWTYPE
) IS
BEGIN
g_action := 'UOMClassConversion update';
UPDATE mtl_uom_class_conversions
SET conversion_rate = p_ccv_rec.conversion_rate,
disable_date = p_ccv_rec.disable_date,
last_update_date = p_ccv_rec.last_update_date,
last_updated_by = p_ccv_rec.last_updated_by
WHERE from_unit_of_measure = p_ccv_rec.from_unit_of_measure
AND from_uom_code = p_ccv_rec.from_uom_code
AND from_uom_class = p_ccv_rec.from_uom_class
AND to_unit_of_measure = p_ccv_rec.to_unit_of_measure
AND to_uom_code = p_ccv_rec.to_uom_code
AND to_uom_class = p_ccv_rec.to_uom_class;
END update_uom_class_conv;
PROCEDURE insert_uom_class_conv(
p_ccv_rec IN mtl_uom_class_conversions%ROWTYPE
) IS
l_count NUMBER;
g_action := 'UOMClass-conv insert';
SELECT count(*)
INTO l_count
FROM mtl_uom_class_conversions
WHERE inventory_item_id = p_ccv_rec.inventory_item_id AND
( to_uom_code = p_ccv_rec.to_uom_code OR
to_uom_class = p_ccv_rec.to_uom_class OR
to_unit_of_measure = p_ccv_rec.to_unit_of_measure );
INSERT INTO mtl_uom_class_conversions (
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,
disable_date,
inventory_item_id,
conversion_rate
) VALUES (
p_ccv_rec.from_unit_of_measure,
p_ccv_rec.from_uom_code,
p_ccv_rec.from_uom_class,
p_ccv_rec.to_unit_of_measure,
p_ccv_rec.to_uom_code,
p_ccv_rec.to_uom_class,
p_ccv_rec.last_update_date,
p_ccv_rec.last_updated_by,
p_ccv_rec.creation_date,
p_ccv_rec.created_by,
p_ccv_rec.disable_date,
p_ccv_rec.inventory_item_id,
p_ccv_rec.conversion_rate
);
END insert_uom_class_conv;
PROCEDURE delete_uom_conv(
p_con_rec IN mtl_uom_conversions%ROWTYPE
) IS
BEGIN
g_action := 'UOM-conversion parameter validation';
g_action := 'UOM-conversion update';
UPDATE mtl_uom_conversions
SET disable_date = p_con_rec.disable_date,
last_update_date = p_con_rec.last_update_date,
last_updated_by = p_con_rec.last_updated_by
WHERE unit_of_measure = p_con_rec.unit_of_measure
AND uom_code = p_con_rec.uom_code
AND uom_class = p_con_rec.uom_class;
END delete_uom_conv;
PROCEDURE update_uom_conv(
p_con_rec IN mtl_uom_conversions%ROWTYPE
) IS
BEGIN
g_action := 'UOM-conversion update';
UPDATE mtl_uom_conversions
SET conversion_rate = p_con_rec.conversion_rate,
disable_date = p_con_rec.disable_date,
last_update_date = p_con_rec.last_update_date,
last_updated_by = p_con_rec.last_updated_by
WHERE unit_of_measure = p_con_rec.unit_of_measure
AND uom_code = p_con_rec.uom_code
AND uom_class = p_con_rec.uom_class;
END update_uom_conv;
PROCEDURE insert_uom_conv(
p_con_rec IN mtl_uom_conversions%ROWTYPE
) IS
l_flag_count NUMBER;
select count(*)
into l_count
from mtl_uom_conversions
where inventory_item_id = p_con_rec.inventory_item_id
and ( unit_of_measure = p_con_rec.unit_of_measure
OR uom_code = p_con_rec.uom_code );
SELECT COUNT(*)
INTO l_flag_count
FROM mtl_uom_conversions
WHERE default_conversion_flag = 'Y'
AND uom_class = p_con_rec.uom_class;
g_action := 'UOM-conversion insert';
INSERT INTO mtl_uom_conversions (
unit_of_measure,
uom_code,
uom_class,
inventory_item_id,
conversion_rate,
default_conversion_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
disable_date
) VALUES (
p_con_rec.unit_of_measure,
p_con_rec.uom_code,
p_con_rec.uom_class,
p_con_rec.inventory_item_id,
p_con_rec.conversion_rate,
p_con_rec.default_conversion_flag,
p_con_rec.last_update_date,
p_con_rec.last_updated_by,
p_con_rec.creation_date,
p_con_rec.created_by,
p_con_rec.disable_date
);
END insert_uom_conv;
l_cls_rec.last_update_date := NVL(p_dt_creation, SYSDATE);
l_cls_rec.last_updated_by := FND_GLOBAL.user_id;
l_cls_rec.creation_date := l_cls_rec.last_update_date;
l_cls_rec.created_by := l_cls_rec.last_updated_by;
l_uom_rec.last_update_date := l_cls_rec.last_update_date;
l_uom_rec.last_updated_by := l_cls_rec.last_updated_by;
l_con_rec.last_update_date := l_cls_rec.last_update_date;
l_con_rec.last_updated_by := l_cls_rec.last_updated_by;
l_ccv_rec.last_update_date := l_cls_rec.last_update_date;
l_ccv_rec.last_updated_by := l_cls_rec.last_updated_by;
-- set disabledate to current date for delete_uom
IF p_syncind = 'D' then
l_cls_rec.disable_date := sysdate;
insert_uom_class (l_cls_rec);
insert_uom_conv (l_con_rec);
insert_uom_class_conv(l_ccv_rec);
insert_uom (l_uom_rec);
update_uom_class (l_cls_rec);
update_uom_conv (l_con_rec);
update_uom_class_conv(l_ccv_rec);
update_uom (l_uom_rec);
delete_uom_class (l_cls_rec);
delete_uom_conv (l_con_rec);
delete_uom_class_conv(l_ccv_rec);
delete_uom (l_uom_rec);
SELECT uom_class
FROM mtl_units_of_measure
WHERE unit_of_measure = l_uom_rec.unit_of_measure;
l_uom_rec.last_update_date := NVL(p_dt_creation, SYSDATE);
l_uom_rec.last_updated_by := FND_GLOBAL.user_id;
l_uom_rec.creation_date := l_uom_rec.last_update_date;
l_uom_rec.created_by := l_uom_rec.last_updated_by;
/* Not inserting a new UOM but no UOM Class passed in.
* Have only to look up the class that goes with the UOM,
* since the UOM exists in Oracle already.
*
* MUST set l_uom_rec.unit_of_measure first!
*/
OPEN l_def_cls_csr;
-- set disabledate to current date for delete_uom
IF p_syncind = 'D' then
l_uom_rec.disable_date := sysdate;
IF p_syncind = 'A' THEN insert_uom(l_uom_rec);
ELSIF p_syncind = 'C' THEN update_uom(l_uom_rec);
ELSIF p_syncind = 'D' THEN delete_uom(l_uom_rec);
SELECT uom_class, unit_of_measure
FROM mtl_units_of_measure
WHERE uom_code = l_uom_code;
l_ccv_rec.last_update_date := NVL(p_dt_creation, SYSDATE);
l_ccv_rec.last_updated_by := FND_GLOBAL.user_id;
l_ccv_rec.creation_date := l_ccv_rec.last_update_date;
l_ccv_rec.created_by := l_ccv_rec.last_updated_by;
IF p_syncind = 'A' THEN insert_uom_class_conv(l_ccv_rec);
ELSIF p_syncind = 'C' THEN update_uom_class_conv(l_ccv_rec);
ELSIF p_syncind = 'D' THEN delete_uom_class_conv(l_ccv_rec);
SELECT uom_class
FROM mtl_units_of_measure
WHERE unit_of_measure = l_con_rec.unit_of_measure;
l_con_rec.last_update_date := NVL(p_dt_creation, SYSDATE);
l_con_rec.last_updated_by := FND_GLOBAL.user_id;
l_con_rec.creation_date := l_con_rec.last_update_date;
l_con_rec.created_by := l_con_rec.last_updated_by;
IF p_syncind = 'A' THEN insert_uom_conv(l_con_rec);
ELSIF p_syncind = 'C' THEN update_uom_conv(l_con_rec);
ELSIF p_syncind = 'D' THEN delete_uom_conv(l_con_rec);
SELECT uom_class, unit_of_measure, uom_code, base_uom_flag
FROM mtl_units_of_measure
WHERE uom_code = p_uom_code
AND base_uom_flag LIKE '%';