The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT metric_id, metric_calculation_type, metric_category, accrual_type
FROM ams_metrics_all_b
WHERE metric_id = p_metric_rec.metric_id;
SELECT 1
FROM ams_metrics_all_b
WHERE metric_parent_id = p_met_id;
SELECT metric_id
FROM ams_metrics_all_b
WHERE summary_metric_id = p_met_id;
SELECT metric_id
FROM ams_metrics_all_b
WHERE to_number(compute_using_function) = p_met_id;
SELECT COUNT(1)
FROM ams_metrics_all_b
WHERE metric_id = l_metric_id;
SELECT ams_metrics_all_b_s.NEXTVAL
FROM dual;
Ams_Utility_Pvt.debug_message(l_full_name ||': insert');
INSERT INTO ams_metrics_all_b (
metric_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
application_id,
arc_metric_used_for_object,
metric_calculation_type,
metric_category,
accrual_type,
value_type,
sensitive_data_flag,
enabled_flag,
metric_sub_category,
function_name,
metric_parent_id,
summary_metric_id,
compute_using_function,
default_uom_code,
uom_type,
formula,
org_id,
hierarchy_id,
set_function_name,
function_type,
display_type,
target_type,
denorm_code
)
VALUES (
l_metrics_rec.metric_id,
SYSDATE,
FND_GLOBAL.User_ID,
SYSDATE,
FND_GLOBAL.User_ID,
FND_GLOBAL.Conc_Login_ID,
1, --Object Version Number
l_metrics_rec.application_id,
l_metrics_rec.arc_metric_used_for_object,
l_metrics_rec.metric_calculation_type,
l_metrics_rec.metric_category,
l_metrics_rec.accrual_type,
l_metrics_rec.value_type,
l_metrics_rec.sensitive_data_flag,
l_metrics_rec.enabled_flag,
l_metrics_rec.metric_sub_category,
l_metrics_rec.function_name,
l_metrics_rec.metric_parent_id,
l_metrics_rec.summary_metric_id,
l_metrics_rec.compute_using_function,
l_metrics_rec.default_uom_code,
l_metrics_rec.uom_type,
l_metrics_rec.formula,
TO_NUMBER (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)), -- org_id
l_metrics_rec.hierarchy_id,
l_metrics_rec.set_function_name,
l_func_type,
l_metrics_rec.display_type,
l_metrics_rec.target_type,
l_metrics_rec.denorm_code
);
Ams_Utility_Pvt.debug_message(l_full_name ||': insert TL ');
INSERT INTO ams_metrics_all_tl (
metric_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_lang,
metrics_name,
description,
formula_display,
LANGUAGE
)
SELECT l_metrics_rec.metric_id,
SYSDATE,
FND_GLOBAL.User_ID,
SYSDATE,
FND_GLOBAL.User_ID,
FND_GLOBAL.Conc_Login_ID,
USERENV ('LANG'),
l_metrics_rec.metrics_name,
l_metrics_rec.description,
l_metrics_rec.formula_display,
l.language_code
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS ( SELECT NULL
FROM ams_metrics_all_tl t
WHERE t.metric_id = l_metrics_rec.metric_id
AND t.LANGUAGE = l.language_code);
PROCEDURE Update_Metric (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_metric_rec IN metric_rec_type
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_METRIC';
SAVEPOINT Update_Metric_pvt;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
UPDATE ams_metrics_all_b
SET object_version_number = l_metrics_rec.object_version_number + 1,
application_id = l_metrics_rec.application_id,
arc_metric_used_for_object =l_metrics_rec.arc_metric_used_for_object,
metric_calculation_type = l_metrics_rec.metric_calculation_type,
metric_category = l_metrics_rec.metric_category,
accrual_type = l_metrics_rec.accrual_type,
value_type = l_metrics_rec.value_type,
sensitive_data_flag = l_metrics_rec.sensitive_data_flag,
enabled_flag = l_metrics_rec.enabled_flag,
metric_sub_category = l_metrics_rec.metric_sub_category,
function_name = l_metrics_rec.function_name,
metric_parent_id = l_metrics_rec.metric_parent_id,
summary_metric_id = l_metrics_rec.summary_metric_id,
compute_using_function = l_metrics_rec.compute_using_function,
default_uom_code = l_metrics_rec.default_uom_code,
uom_type = l_metrics_rec.uom_type,
formula = l_metrics_rec.formula,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.User_ID,
last_update_login = FND_GLOBAL.Conc_Login_ID,
hierarchy_id = l_metrics_rec.hierarchy_id,
set_function_name = l_metrics_rec.set_function_name,
function_type = l_func_type,
display_type = l_metrics_rec.display_type,
target_type = l_metrics_rec.target_type,
denorm_code = l_metrics_rec.denorm_code
WHERE metric_id = l_metrics_rec.metric_id;
Ams_Utility_Pvt.debug_message(l_full_name ||': updateMetrics TL Table');
UPDATE ams_metrics_all_tl
SET metrics_name = l_metrics_rec.metrics_name,
description = l_metrics_rec.description,
formula_display = l_metrics_rec.formula_display,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.User_ID,
last_update_login = FND_GLOBAL.Conc_Login_ID,
source_lang = USERENV ('LANG')
WHERE metric_id = l_metrics_rec.metric_id
AND USERENV ('LANG') IN (LANGUAGE, source_lang);
ROLLBACK TO Update_Metric_pvt;
ROLLBACK TO Update_Metric_pvt;
ROLLBACK TO Update_Metric_pvt;
END Update_Metric;
PROCEDURE Delete_Metric (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_metric_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_METRIC';
SELECT metric_id
FROM ams_metrics_all_b
WHERE metric_parent_id = l_met_id;
SELECT metric_id
FROM ams_metrics_all_b
WHERE COMPUTE_USING_FUNCTION = TO_CHAR(l_met_id);
SELECT metric_id
FROM ams_metrics_all_b
WHERE SUMMARY_METRIC_ID = l_met_id;
SAVEPOINT Delete_Metric_pvt;
Ams_Utility_Pvt.debug_message(l_full_name ||': delete with Validation');
DELETE FROM ams_metrics_all_b
WHERE metric_id = p_metric_id
AND object_version_number = p_object_version_number;
DELETE FROM ams_metrics_all_tl
WHERE metric_id = p_metric_id;
delete from ams_metric_formulas
where metric_id = p_metric_id;
ROLLBACK TO Delete_Metric_pvt;
ROLLBACK TO Delete_Metric_pvt;
ROLLBACK TO Delete_Metric_pvt;
END Delete_Metric;
SELECT metric_id
FROM ams_metrics_all_b
WHERE metric_id = p_metric_id
AND object_version_number = p_object_version_number
FOR UPDATE OF metric_id NOWAIT;
SELECT metric_id
FROM ams_metrics_all_tl
WHERE metric_id = p_metric_id
AND USERENV('LANG') IN (LANGUAGE, source_lang)
FOR UPDATE OF metric_id NOWAIT;
SELECT 1
FROM ams_metrics_vl
WHERE UPPER(METRICS_NAME) = UPPER(p_metrics_name)
AND arc_metric_used_for_object = p_arc_metric_used_for_object;
SELECT 1
FROM ams_metrics_vl
WHERE UPPER(METRICS_NAME) = UPPER(p_metrics_name)
AND arc_metric_used_for_object = p_arc_metric_used_for_object
AND metric_id <> p_metric_id ;
select count(1) from ams_lookups
where lookup_type in ('AMS_METRIC_ROLLUP_TYPE', 'AMS_METRIC_OBJECT_TYPE', 'AMS_METRIC_ALLOCATION_TYPE')
and lookup_code = l_arc_metric_used_for_object;
select count(1) from ams_lookups
where lookup_type in ('AMS_METRIC_SPECIAL_TYPE')
and lookup_code = l_arc_metric_used_for_object;
select count(1) from ams_lookups
where lookup_type in ('AMS_METRIC_SPECIAL_TYPE', 'AMS_METRIC_ROLLUP_TYPE',
'AMS_METRIC_OBJECT_TYPE', 'AMS_METRIC_ALLOCATION_TYPE')
and lookup_code = l_arc_metric_used_for_object;
SELECT 1 from ams_metrics_vl
WHERE METRIC_ID = p_metric_id
AND metric_calculation_type = p_metric_calculation;
SELECT 1 from MTL_UOM_CLASSES
WHERE UOM_CLASS = p_uom_type;
SELECT 1 from AMS_CATEGORIES_VL
WHERE CATEGORY_ID = p_category_id
AND enabled_flag = 'Y'
AND arc_category_created_for = 'METR';
SELECT met.*
FROM ams_metrics_vl met
WHERE met.metric_id = l_metric_id
;
SELECT met.*
FROM ams_metrics_vl met
WHERE met.metric_id = l_metric_id
;
SELECT COUNT(1)
FROM ams_metrics_vl
WHERE metric_parent_id = l_metric_id ;
SELECT COUNT(1)
FROM ams_metrics_vl
WHERE summary_metric_id = l_metric_id ;
select metric_category, ARC_METRIC_USED_FOR_OBJECT, metric_calculation_type
from ams_metrics_all_b
where metric_id = l_metric_id;
SELECT category_name
FROM ams_categories_vl
where category_id = l_category_id;
SELECT 1 from AMS_CATEGORIES_VL
WHERE CATEGORY_ID = p_category_id
AND enabled_flag = 'Y'
AND arc_category_created_for = 'METR'
AND parent_category_id = p_parent_category_id;
SELECT 1 from MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = p_uom_code
AND uom_class = p_uom_class;
SELECT 1 from AMS_ACT_METRICS_ALL
WHERE METRIC_ID = p_metric_id ;
SELECT metric_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_version_number
,application_id
,arc_metric_used_for_object
,metric_calculation_type
,metric_category
,accrual_type
,value_type
,sensitive_data_flag
,enabled_flag
,metric_sub_category
,function_name
,metric_parent_id
,summary_metric_id
,compute_using_function
,default_uom_code
,uom_type
,formula
,metrics_name
,description
,formula_display
,hierarchy_id
,set_function_name
,display_type
,target_type
,denorm_code
FROM ams_metrics_vl
WHERE metric_id = p_metric_rec.metric_id;