[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE VARCHAR2(30) := 'UPDATE';
G_DELETE VARCHAR2(30) := 'DELETE';
SELECT COUNT(1)
FROM ozf_act_metrics_all
WHERE activity_metric_id = l_act_metric_id;
SELECT ozf_act_metrics_all_s.NEXTVAL
FROM dual;
ozf_utility_pvt.debug_message(l_full_name ||': insert');
INSERT INTO ozf_act_metrics_all (
activity_metric_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
act_metric_used_by_id,
arc_act_metric_used_by,
purchase_req_raised_flag,
application_id,
sensitive_data_flag,
budget_id,
metric_id,
transaction_currency_code,
trans_forecasted_value,
trans_committed_value,
trans_actual_value,
functional_currency_code,
func_forecasted_value,
dirty_flag,
func_committed_value,
func_actual_value,
last_calculated_date,
variable_value,
computed_using_function_value,
metric_uom_code,
org_id,
attribute_category,
difference_since_last_calc,
activity_metric_origin_id,
arc_activity_metric_origin,
days_since_last_refresh,
scenario_id,
SUMMARIZE_TO_METRIC,
hierarchy_id,
start_node,
from_level,
to_level,
from_date,
TO_DATE,
amount1,
amount2,
amount3,
percent1,
percent2,
percent3,
published_flag,
pre_function_name,
post_function_name,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
description,
act_metric_date,
depend_act_metric,
function_used_by_id,
arc_function_used_by,
/* 05/15/2002 yzhao: 11.5.9 add 6 new columns for top-down bottom-up budgeting */
hierarchy_type,
status_code,
method_code,
action_code,
basis_year,
ex_start_node,
/* 05/15/2002 yzhao: add ends */
product_spread_time_id,
start_period_name,
end_period_name
)
VALUES (
l_act_metrics_rec.activity_metric_id,
sysdate,
Fnd_Global.User_ID,
sysdate,
Fnd_Global.User_ID,
Fnd_Global.Conc_Login_ID,
1, --Object Version Number
l_act_metrics_rec.act_metric_used_by_id,
l_act_metrics_rec.arc_act_metric_used_by,
NVL(l_act_metrics_rec.purchase_req_raised_flag,'N'),
l_act_metrics_rec.application_id,
l_act_metrics_rec.sensitive_data_flag,
l_act_metrics_rec.budget_id,
l_act_metrics_rec.metric_id,
l_act_metrics_rec.transaction_currency_code,
l_act_metrics_rec.trans_forecasted_value,
l_act_metrics_rec.trans_committed_value,
l_act_metrics_rec.trans_actual_value,
l_act_metrics_rec.functional_currency_code,
l_act_metrics_rec.func_forecasted_value,
NVL(l_act_metrics_rec.dirty_flag,'Y'),
l_act_metrics_rec.func_committed_value,
l_act_metrics_rec.func_actual_value,
l_act_metrics_rec.last_calculated_date,
l_act_metrics_rec.variable_value,
l_act_metrics_rec.computed_using_function_value,
l_act_metrics_rec.metric_uom_code,
MO_UTILS.get_default_org_id , -- org_id
l_act_metrics_rec.attribute_category,
l_act_metrics_rec.difference_since_last_calc,
l_act_metrics_rec.activity_metric_origin_id,
l_act_metrics_rec.arc_activity_metric_origin,
l_act_metrics_rec.days_since_last_refresh,
l_act_metrics_rec.scenario_id,
l_act_metrics_rec.SUMMARIZE_TO_METRIC,
l_act_metrics_rec.hierarchy_id,
l_act_metrics_rec.start_node,
l_act_metrics_rec.from_level,
l_act_metrics_rec.to_level,
l_act_metrics_rec.from_date,
l_act_metrics_rec.TO_DATE,
l_act_metrics_rec.amount1,
l_act_metrics_rec.amount2,
l_act_metrics_rec.amount3,
l_act_metrics_rec.percent1,
l_act_metrics_rec.percent2,
l_act_metrics_rec.percent3,
l_act_metrics_rec.published_flag,
l_act_metrics_rec.pre_function_name,
l_act_metrics_rec.post_function_name,
l_act_metrics_rec.attribute1,
l_act_metrics_rec.attribute2,
l_act_metrics_rec.attribute3,
l_act_metrics_rec.attribute4,
l_act_metrics_rec.attribute5,
l_act_metrics_rec.attribute6,
l_act_metrics_rec.attribute7,
l_act_metrics_rec.attribute8,
l_act_metrics_rec.attribute9,
l_act_metrics_rec.attribute10,
l_act_metrics_rec.attribute11,
l_act_metrics_rec.attribute12,
l_act_metrics_rec.attribute13,
l_act_metrics_rec.attribute14,
l_act_metrics_rec.attribute15,
l_act_metrics_rec.description,
l_act_metrics_rec.act_metric_date,
l_act_metrics_rec.depend_act_metric,
l_act_metrics_rec.function_used_by_id,
l_act_metrics_rec.arc_function_used_by,
/* 05/15/2002 yzhao: 11.5.9 add 6 new columns for top-down bottom-up budgeting */
l_act_metrics_rec.hierarchy_type,
l_act_metrics_rec.status_code,
l_act_metrics_rec.method_code,
l_act_metrics_rec.action_code,
l_act_metrics_rec.basis_year,
l_act_metrics_rec.ex_start_node,
/* 05/15/2002 yzhao: add ends */
l_act_metrics_rec.product_spread_time_id,
l_act_metrics_rec.start_period_name,
l_act_metrics_rec.end_period_name
);
PROCEDURE Update_ActMetric (
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_act_metric_rec IN act_metric_rec_type
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_ACTMETRIC';
SAVEPOINT Update_ActMetric_pvt;
p_validation_mode => Jtf_Plsql_Api.G_UPDATE,
x_complete_rec => l_actmet_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ) ;
p_validation_mode => Jtf_Plsql_Api.g_update,
x_return_status => l_return_status
);
p_operation_mode => G_UPDATE,
x_return_status => l_return_status
);
ozf_utility_pvt.debug_message(l_full_name ||': update Activity Metrics Table');
UPDATE ozf_act_metrics_all
SET object_version_number= object_version_number + 1,
act_metric_used_by_id = l_actmet_rec.act_metric_used_by_id,
arc_act_metric_used_by = l_actmet_rec.arc_act_metric_used_by,
purchase_req_raised_flag = l_actmet_rec.purchase_req_raised_flag,
application_id = l_actmet_rec.application_id,
sensitive_data_flag = l_actmet_rec.sensitive_data_flag,
budget_id = l_actmet_rec.budget_id ,
metric_id = l_actmet_rec.metric_id,
transaction_currency_code= l_actmet_rec.transaction_currency_code,
trans_forecasted_value = l_actmet_rec.trans_forecasted_value,
trans_committed_value = l_actmet_rec.trans_committed_value,
trans_actual_value = l_actmet_rec.trans_actual_value,
functional_currency_code = l_actmet_rec.functional_currency_code,
func_forecasted_value = l_actmet_rec.func_forecasted_value,
func_committed_value = l_actmet_rec.func_committed_value,
func_actual_value = l_actmet_rec.func_actual_value,
dirty_flag = l_actmet_rec.dirty_flag,
last_calculated_date = l_actmet_rec.last_calculated_date,
variable_value = l_actmet_rec.variable_value,
computed_using_function_value =
l_actmet_rec.computed_using_function_value,
metric_uom_code = l_actmet_rec.metric_uom_code,
difference_since_last_calc = l_actmet_rec.difference_since_last_calc,
activity_metric_origin_id= l_actmet_rec.activity_metric_origin_id,
arc_activity_metric_origin = l_actmet_rec.arc_activity_metric_origin,
hierarchy_id = l_actmet_rec.hierarchy_id,
start_node = l_actmet_rec.start_node,
from_level = l_actmet_rec.from_level,
to_level = l_actmet_rec.to_level,
from_date = l_actmet_rec.from_date,
TO_DATE = l_actmet_rec.TO_DATE,
amount1 = l_actmet_rec.amount1,
amount2 = l_actmet_rec.amount2,
amount3 = l_actmet_rec.amount3,
percent1 = l_actmet_rec.percent1,
percent2 = l_actmet_rec.percent2,
percent3 = l_actmet_rec.percent3,
published_flag = l_actmet_rec.published_flag,
pre_function_name = l_actmet_rec.pre_function_name,
post_function_name = l_actmet_rec.post_function_name,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.User_ID,
last_update_login = Fnd_Global.Conc_Login_ID,
attribute_category = l_actmet_rec.attribute_category,
attribute1 = l_actmet_rec.attribute1,
attribute2 = l_actmet_rec.attribute2,
attribute3 = l_actmet_rec.attribute3,
attribute4 = l_actmet_rec.attribute4,
attribute5 = l_actmet_rec.attribute5,
attribute6 = l_actmet_rec.attribute6,
attribute7 = l_actmet_rec.attribute7,
attribute8 = l_actmet_rec.attribute8,
attribute9 = l_actmet_rec.attribute9,
attribute10 = l_actmet_rec.attribute10,
attribute11 = l_actmet_rec.attribute11,
attribute12 = l_actmet_rec.attribute12,
attribute13 = l_actmet_rec.attribute13,
attribute14 = l_actmet_rec.attribute14,
attribute15 = l_actmet_rec.attribute15,
description = l_actmet_rec.description,
act_metric_date = l_actmet_rec.act_metric_date,
depend_act_metric = l_actmet_rec.depend_act_metric,
function_used_by_id = l_actmet_rec.function_used_by_id,
arc_function_used_by = l_actmet_rec.arc_function_used_by,
/* 05/15/2002 yzhao: 11.5.9 add 6 new columns for top-down bottom-up budgeting */
hierarchy_type = l_actmet_rec.hierarchy_type,
status_code = l_actmet_rec.status_code,
method_code = l_actmet_rec.method_code,
action_code = l_actmet_rec.action_code,
basis_year = l_actmet_rec.basis_year,
ex_start_node = l_actmet_rec.ex_start_node,
/* 05/15/2002 yzhao: add ends */
/* kvattiku April 23, 04 Update extra paramters in Quota */
product_spread_time_id = l_actmet_rec.product_spread_time_id,
start_period_name = l_actmet_rec.start_period_name,
end_period_name = l_actmet_rec.end_period_name
WHERE activity_metric_id = l_actmet_rec.activity_metric_id;
ROLLBACK TO Update_ActMetric_pvt;
ROLLBACK TO Update_ActMetric_pvt;
ROLLBACK TO Update_ActMetric_pvt;
END Update_ActMetric;
SELECT 'x'
FROM ams_metrics_all_b b
WHERE metric_id = l_metric_id
AND ((metric_calculation_type = 'FUNCTION'
AND NOT EXISTS (SELECT 'x' FROM ozf_act_metrics_all a
WHERE a.metric_id = b.metric_id
AND a.arc_act_metric_used_by = l_arc_act_metric_used_by
AND a.act_metric_used_by_id = l_act_metric_used_by_id
AND NVL(a.arc_function_used_by,'') = NVL(l_arc_function_used_by,'')
AND NVL(a.function_used_by_id,-1) = NVL(l_function_used_by_id,-1)
))
OR metric_calculation_type <> 'FUNCTION');
SELECT (NVL(original_budget, 0) - NVL(holdback_amt, 0) + NVL(transfered_in_amt,0) - NVL(transfered_out_amt, 0))
, NVL(start_date_active, l_default_start_date)
, NVL(end_date_active, l_default_end_date)
, fund_type
FROM ozf_funds_all_b
WHERE fund_id = p_act_metric_rec.act_metric_used_by_id;
SELECT 1
FROM ozf_terr_v
WHERE hierarchy_id = p_act_metric_rec.hierarchy_id
AND level_depth = p_act_metric_rec.from_level
AND node_id = p_act_metric_rec.start_node;
L_ALLOW_ACTUAL_UPDATE_METR CONSTANT VARCHAR2(30) := 'AMS_ALLOW_ACTUAL_UPDATE';
l_allow_actual_update VARCHAR2(1);
SELECT func_actual_value,
trans_forecasted_value
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_act_metric_id;
SELECT *
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_act_metric_rec.activity_metric_id;