[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE CONSTANT VARCHAR2(30) := 'UPDATE';
G_DELETE CONSTANT VARCHAR2(30) := 'DELETE';
SELECT NVL (transaction_currency_code, functional_currency_code) AS currency_code, status_code
FROM ams_campaigns_all_b
WHERE campaign_id = p_camp_id;
SELECT NVL (transaction_currency_code, functional_currency_code) AS currency_code, status_code
FROM ams_campaign_schedules_b
WHERE schedule_id = p_schedule_id;
SELECT NVL (transaction_currency_code, currency_code) AS currency_code, status_code
FROM ams_deliverables_all_b
WHERE deliverable_id = p_deliv_id;
SELECT currency_code_tc AS currency_code, system_status_code
-- active_flag, active_from_date
FROM ams_event_headers_all_b
WHERE event_header_id = p_event_id;
SELECT currency_code_tc AS currency_code, system_status_code
FROM ams_event_offers_all_b
WHERE event_offer_id = p_ev_id;
SELECT 1
FROM ams_campaigns_all_b
WHERE campaign_id = p_camp_id
AND UPPER(status_code) IN ('ACTIVE', 'AVAILABLE', 'CANCELLED', 'PLANNING');
SELECT 1
FROM ams_campaign_schedules_b
WHERE schedule_id = p_schedule_id
AND UPPER(status_code) IN ('ACTIVE', 'AVAILABLE', 'CANCELLED', 'PLANNING');
SELECT 1
FROM ams_deliverables_all_b
WHERE deliverable_id = p_deliv_id
AND UPPER(status_code) IN ('CANCELLED', 'AVAILABLE');
SELECT 1
FROM ams_event_headers_all_b
WHERE event_header_id = p_event_id
AND UPPER(system_status_code) IN
('ACTIVE', 'AVAILABLE', 'CANCELLED', 'PLANNING')
--AND active_flag = 'Y'
--AND active_from_date < SYSDATE
;
SELECT 1
FROM ams_event_offers_all_b
WHERE event_offer_id = p_ev_id
AND UPPER(system_status_code) IN
('ACTIVE', 'AVAILABLE', 'CANCELLED', 'PLANNING');
x_act_metric_rec.last_update_date := Fnd_Api.G_MISS_DATE ;
x_act_metric_rec.last_updated_by := Fnd_Api.G_MISS_NUM ;
x_act_metric_rec.last_update_login := Fnd_Api.G_MISS_NUM ;
UPDATE ams_act_metrics_all
SET dirty_flag = G_IS_DIRTY
WHERE activity_metric_id IN (
SELECT activity_metric_id
FROM ams_act_metrics_all
START WITH activity_metric_id = p_activity_metric_id
CONNECT BY activity_metric_id = PRIOR summarize_to_metric)
AND dirty_flag <> G_IS_DIRTY
;
UPDATE ams_act_metrics_all
SET dirty_flag = G_IS_DIRTY
WHERE activity_metric_id IN (
SELECT activity_metric_id
FROM ams_act_metrics_all
START WITH activity_metric_id = p_activity_metric_id
CONNECT BY activity_metric_id = PRIOR rollup_to_metric)
AND dirty_flag <> G_IS_DIRTY
;
update ams_act_metrics_all
set dirty_flag = G_IS_DIRTY
where activity_metric_id in
(select a.activity_metric_id
from ams_act_metrics_all a, ams_metrics_all_b m, ams_metric_formulas f,
ams_act_metrics_all b, ams_metrics_all_b c
where a.metric_id = m.metric_id
and m.metric_id = f.metric_id
and b.metric_id = c.metric_id
and a.arc_act_metric_used_by = b.arc_act_metric_used_by
and a.act_metric_used_by_id = b.act_metric_used_by_id
and m.metric_calculation_type = G_FORMULA
and a.last_update_date > b.last_update_date
and ((b.metric_id = f.source_id and f.source_type = G_METRIC)
or (c.metric_category = f.source_id and f.source_type = G_CATEGORY))
and b.activity_metric_id = p_activity_metric_id)
and dirty_flag <> G_IS_DIRTY;
UPDATE ams_act_metrics_all
SET dirty_flag = G_IS_DIRTY
WHERE activity_metric_id IN
(SELECT a.activity_metric_id
FROM ams_act_metrics_all a, ams_metrics_all_b b,
ams_act_metrics_all c
WHERE a.metric_id = b.metric_id
AND b.accrual_type = G_VARIABLE
AND a.arc_act_metric_used_by = c.arc_act_metric_used_by
AND a.act_metric_used_by_id = c.act_metric_used_by_id
AND c.activity_metric_id = p_activity_metric_id
AND TO_NUMBER(NVL(b.compute_using_function,'-1')) = c.metric_id);
SELECT activity_metric_id
FROM ams_act_metrics_all
WHERE metric_id = cv_metric_id
AND act_metric_used_by_id = cv_act_metric_used_by_id
AND arc_act_metric_used_by = cv_arc_act_metric_used_by;
SELECT transaction_currency_code
FROM ams_campaigns_all_b
WHERE campaign_id = l_obj_id;
SELECT transaction_currency_code
FROM ams_campaign_schedules_b
WHERE schedule_id = l_obj_id;
SELECT currency_code_tc
FROM ams_event_offers_all_b
WHERE event_offer_id = l_obj_id;
SELECT currency_code_tc
FROM ams_event_headers_all_b
WHERE event_header_id = l_obj_id;
SELECT transaction_currency_code
FROM ams_deliverables_all_b
WHERE deliverable_id = l_obj_id;
SELECT activity_metric_id, transaction_currency_code,
trans_actual_value, trans_forecasted_value
FROM ams_act_metrics_all actmet, ams_metrics_all_b met
WHERE actmet.metric_id = met.metric_id
AND metric_calculation_type IN ('ROLLUP', 'SUMMARY')
AND arc_act_metric_used_by = l_obj_type
AND act_metric_used_by_id = l_obj_id
AND transaction_currency_code IS NOT NULL;
SELECT metric_category
FROM ams_metrics_all_b
WHERE metric_id = l_met_id;
Ams_Actmetric_Pvt.update_actmetric (
p_api_version => 1.0,
p_act_metric_rec => l_act_metric_rec,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT sensitive_data_flag,
default_uom_code,
metric_calculation_type,
metric_category, accrual_type,
display_type
FROM ams_metrics_all_b
WHERE metric_id = l_met_id ;
p_operation_mode IN VARCHAR2) -- 'C','U','D' for Create, Update, or Delete
RETURN VARCHAR2
IS
-- Cursors for checking of statuses when the object is active.
-- Relevant in Update.
CURSOR c_camp_active(id NUMBER) IS
SELECT Fnd_Api.G_TRUE
FROM ams_campaigns_all_b
WHERE campaign_id = id
AND status_code IN ('SUBMITTED_BA', 'ACTIVE');
SELECT Fnd_Api.G_TRUE
FROM ams_campaign_schedules_b
WHERE schedule_id = id
AND status_code = 'ACTIVE';
SELECT Fnd_Api.G_TRUE
FROM ams_deliverables_all_b
WHERE deliverable_id = id
AND status_code IN ('SUBMITTED_BA', 'AVAILABLE');
SELECT Fnd_Api.G_TRUE
FROM ams_event_headers_all_b
WHERE event_header_id = id
AND system_status_code IN ('ACTIVE', 'SUBMITTED_BA');
SELECT Fnd_Api.G_TRUE
FROM ams_event_offers_all_b
WHERE event_offer_id = id
AND system_status_code IN ('SUBMITTED_BA', 'ACTIVE');
CURSOR c_camp_delete(id NUMBER) IS
SELECT Fnd_Api.G_TRUE
FROM ams_campaigns_all_b
WHERE campaign_id = id
AND status_code IN ('SUBMITTED_BA', 'ACTIVE', 'COMPLETED', 'CANCELLED');
CURSOR c_csch_delete(id NUMBER) IS
SELECT Fnd_Api.G_TRUE
FROM ams_campaign_schedules_b
WHERE schedule_id = id
AND status_code IN ('SUBMITTED_BA', 'ACTIVE', 'COMPLETED', 'CANCELLED');
CURSOR c_delv_delete(id NUMBER) IS
SELECT Fnd_Api.G_TRUE
FROM ams_deliverables_all_b
WHERE deliverable_id = id
AND status_code IN ('SUBMITTED_BA', 'AVAILABLE', 'CANCELLED', 'ARCHIVED');
CURSOR c_eveh_delete(id NUMBER) IS
SELECT Fnd_Api.G_TRUE
FROM ams_event_headers_all_b
WHERE event_header_id = id
AND system_status_code IN ('ACTIVE', 'CANCELLED', 'SUBMITTED_BA','COMPLETED');
CURSOR c_eveo_delete(id NUMBER) IS
SELECT Fnd_Api.G_TRUE
FROM ams_event_offers_all_b
WHERE event_offer_id = id
AND system_status_code IN ('SUBMITTED_BA', 'ACTIVE', 'CANCELLED', 'COMPLETED');
IF p_operation_mode =G_UPDATE THEN
IF p_object_type IN ('RCAM', 'CAMP') THEN
OPEN c_camp_active(p_object_id);
ELSIF p_operation_mode =G_DELETE THEN
IF p_object_type IN ('RCAM', 'CAMP') THEN
OPEN c_camp_delete(p_object_id);
FETCH c_camp_delete INTO l_return_value;
CLOSE c_camp_delete;
OPEN c_csch_delete(p_object_id);
FETCH c_csch_delete INTO l_return_value;
CLOSE c_csch_delete;
OPEN c_delv_delete(p_object_id);
FETCH c_delv_delete INTO l_return_value;
CLOSE c_delv_delete;
OPEN c_eveh_delete(p_object_id);
FETCH c_eveh_delete INTO l_return_value;
CLOSE c_eveh_delete;
OPEN c_eveo_delete(p_object_id);
FETCH c_eveo_delete INTO l_return_value;
CLOSE c_eveo_delete;
p_operation_mode IN VARCHAR2 , -- 'C','U','D' for Create, Update, or Delete
x_freeze_status OUT NOCOPY VARCHAR2, -- True or False
x_return_status OUT NOCOPY VARCHAR2)
IS
l_return_value VARCHAR2(30);
SELECT COUNT(1)
FROM ams_act_metrics_all
WHERE activity_metric_id = l_act_metric_id;
SELECT ams_act_metrics_all_s.NEXTVAL
FROM dual;
SELECT enabled_flag, metric_calculation_type,
summary_metric_id, sensitive_data_flag,
accrual_type, compute_using_function
FROM ams_metrics_all_b
WHERE metric_id = l_met_id;
SELECT activity_metric_id
FROM ams_act_metrics_all
WHERE arc_act_metric_used_by = l_object_type
AND act_metric_used_by_id = l_object_id
AND metric_id = l_metric_id;
Ams_Utility_Pvt.debug_message(l_full_name ||': insert');
INSERT INTO ams_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,
forecasted_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 */
)
VALUES (
l_act_metrics_rec.activity_metric_id,
l_today,
Fnd_Global.User_ID,
l_today,
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,G_IS_DIRTY),
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.forecasted_variable_value,
l_act_metrics_rec.computed_using_function_value,
l_act_metrics_rec.metric_uom_code,
TO_NUMBER (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)) , -- 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 */
);
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';
SELECT depend_act_metric
FROM ams_act_metrics_all a, ams_metrics_all_b b
WHERE activity_metric_id = l_activity_metric_id
AND a.metric_id = b.metric_id
AND b.accrual_type <> G_VARIABLE;
SAVEPOINT Update_ActMetric_pvt;
Ams_Utility_Pvt.debug_message(l_full_name ||': select calc type, metric_id ='||l_actmet_rec.metric_id);
SELECT metric_calculation_type
INTO l_calc_type
FROM ams_metrics_all_b
WHERE metric_id = l_actmet_rec.metric_id;
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
);
Ams_Utility_Pvt.debug_message(l_full_name ||': update Activity Metrics Table');
UPDATE ams_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,
forecasted_variable_value= l_actmet_rec.forecasted_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 */
WHERE activity_metric_id = l_actmet_rec.activity_metric_id;
UPDATE ams_act_metrics_all
SET dirty_flag = G_IS_DIRTY
WHERE activity_metric_id = l_depend_act_metric;
Record_History(l_actmet_rec.activity_metric_id, G_UPDATE,
x_return_status, x_msg_count, x_msg_data);
ROLLBACK TO Update_ActMetric_pvt;
ROLLBACK TO Update_ActMetric_pvt;
ROLLBACK TO Update_ActMetric_pvt;
END Update_ActMetric;
PROCEDURE Delete_ActMetric (
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_activity_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_ACTMETRIC';
SELECT activity_metric_id, 'SUMMARY'
FROM ams_act_metrics_all
WHERE summarize_to_metric = l_act_metric_id
UNION ALL
SELECT activity_metric_id, 'ROLLUP'
FROM ams_act_metrics_all
WHERE rollup_to_metric = l_act_metric_id;
SELECT activity_metric_id,
last_update_date,
last_updated_by,
creation_date,
created_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,
forecasted_variable_value,
computed_using_function_value,
metric_uom_code,
org_id,
difference_since_last_calc,
activity_metric_origin_id,
arc_activity_metric_origin,
days_since_last_refresh,
scenario_id,
SUMMARIZE_TO_METRIC,
ROLLUP_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,
attribute_category,
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 */
FROM ams_act_metrics_all
WHERE activity_metric_id = l_act_metric_id;
SELECT depend_act_metric, object_version_number
FROM ams_act_metrics_all
WHERE activity_metric_id = l_act_met_id;
SAVEPOINT Delete_ActMetric_pvt;
UPDATE ams_act_metrics_all
SET rollup_to_metric = NULL
WHERE activity_metric_id = l_child_activity_metric_id;
Fnd_Message.set_name('AMS', 'AMS_METR_CANT_DELETE_PARENT');
G_DELETE, -- Delete is operation mode
l_freeze_status,
l_return_status);
Fnd_Message.Set_Name('AMS', 'AMS_METR_DELETE');
Ams_Utility_Pvt.debug_message(l_full_name ||': delete with Validation');
Record_History(p_activity_metric_id, G_DELETE,
x_return_status, x_msg_count, x_msg_data);
DELETE FROM ams_act_metrics_all
WHERE activity_metric_id = p_activity_metric_id
AND object_version_number = p_object_version_number;
UPDATE ams_act_metrics_all
SET depend_act_metric = NULL
WHERE activity_metric_id = l_depend_act_met_id;
Delete_ActMetric (
p_api_version => p_api_version,
p_init_msg_list => Fnd_Api.G_FALSE,
p_commit => Fnd_Api.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_activity_metric_id => l_depend_act_met_id,
p_object_version_number => l_depend_version_num);
ROLLBACK TO Delete_ActMetric_pvt;
ROLLBACK TO Delete_ActMetric_pvt;
ROLLBACK TO Delete_ActMetric_pvt;
END Delete_ActMetric;
PROCEDURE Delete_ActMetric (
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_arc_act_metric_used_by IN VARCHAR2,
p_act_metric_used_by_id IN NUMBER,
p_activity_metric_id IN NUMBER := NULL,
p_object_version_number IN NUMBER := NULL
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_ACTMETRIC';
SELECT activity_metric_id, object_version_number
FROM ams_act_metrics_all
WHERE summarize_to_metric is NULL
AND arc_act_metric_used_by = l_arc_act_metric_used_by
AND act_metric_used_by_id = l_act_metric_used_by_id;
SELECT activity_metric_id, object_version_number
FROM ams_act_metrics_all
WHERE summarize_to_metric = l_activity_metric_id
AND arc_act_metric_used_by = l_arc_act_metric_used_by
AND act_metric_used_by_id = l_act_metric_used_by_id;
Delete_actmetric(
p_api_version => p_api_version,
p_init_msg_list => Fnd_Api.G_FALSE,
p_commit => Fnd_Api.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_arc_act_metric_used_by => p_arc_act_metric_used_by,
p_act_metric_used_by_id => p_act_metric_used_by_id,
p_activity_metric_id => l_activity_metric_ids(l_index),
p_object_version_number => l_object_version_numbers(l_index));
Delete_actmetric(
p_api_version => p_api_version,
p_init_msg_list => Fnd_Api.G_FALSE,
p_commit => Fnd_Api.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_arc_act_metric_used_by => p_arc_act_metric_used_by,
p_act_metric_used_by_id => p_act_metric_used_by_id,
p_activity_metric_id => l_activity_metric_ids(l_index),
p_object_version_number => l_object_version_numbers(l_index));
Delete_actmetric(
p_api_version => p_api_version,
p_init_msg_list => Fnd_Api.G_FALSE,
p_commit => Fnd_Api.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_activity_metric_id => p_activity_metric_id,
p_object_version_number => p_object_version_number);
END Delete_ActMetric;
SELECT activity_metric_id
FROM ams_act_metrics_all
WHERE activity_metric_id = p_activity_metric_id
AND object_version_number = p_object_version_number
FOR UPDATE OF activity_metric_id NOWAIT;
SELECT metrics_name
FROM ams_metrics_vl m, ams_act_metrics_all a
WHERE m.metric_id = a.metric_id
AND activity_metric_id = p_act_metric_id;
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 ams_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,'IS NULL') =
NVL(l_arc_function_used_by,'IS NULL')
AND NVL(a.function_used_by_id,-1) = NVL(l_function_used_by_id,-1)
))
OR metric_calculation_type <> 'FUNCTION');
SELECT 1
FROM ams_lookups
WHERE lookup_type IN ('AMS_METRIC_OBJECT_TYPE', 'AMS_METRIC_ROLLUP_TYPE', 'AMS_METRIC_ALLOCATION_TYPE')
AND lookup_code = p_object_type
;
SELECT 1 from ams_metrics_all_b
WHERE METRIC_ID = p_metric_id;
SELECT 1 from FND_CURRENCIES
WHERE CURRENCY_CODE = p_CURRENCY_CODE
and enabled_flag = p_enabled_flag;
L_ALLOW_ACTUAL_UPDATE_METR CONSTANT VARCHAR2(30) := 'AMS_ALLOW_ACTUAL_UPDATE';
l_allow_actual_update VARCHAR2(1);
SELECT uom_type,
metric_calculation_type,
arc_metric_used_for_object,
metric_category
FROM ams_metrics_all_b
WHERE metric_id = p_metric_id;
SELECT func_actual_value,
trans_forecasted_value,
forecasted_variable_value
FROM ams_act_metrics_all
WHERE activity_metric_id = p_act_metric_id;
SELECT 1 from MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = p_uom_code
AND uom_class = p_uom_class;
G_UPDATE, -- Update is operation mode
l_freeze_status,
l_return_status);
IF p_operation_mode = G_UPDATE THEN
--
-- choang - 11-may-2005 - validate edit metric access
IF AMS_Access_PVT.check_update_access (
p_object_id => l_act_metrics_rec.act_metric_used_by_id,
p_object_type => l_act_metrics_rec.arc_act_metric_used_by,
p_user_or_role_id => AMS_Utility_PVT.get_resource_id (FND_GLOBAL.user_id),
p_user_or_role_type => 'USER') <> 'F' THEN
x_return_status := Fnd_Api.G_RET_STS_ERROR;
FND_MESSAGE.set_name ('AMS', 'AMS_NO_METRIC_UPDATE_ACCESS');
Fnd_Message.set_name('AMS', 'AMS_UPDATE_FORECAST');
l_allow_actual_update :=
NVL(Fnd_Profile.Value (L_ALLOW_ACTUAL_UPDATE_METR),'N');
IF (l_allow_actual_update = 'N') THEN
--object is not active and profile is N hence do not allow update
IF (l_freeze_status = Fnd_Api.G_FALSE) THEN
IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
Fnd_Message.set_name('AMS', 'AMS_METR_UPDATE_ACTUAL');
l_allow_actual_update :=
NVL(Fnd_Profile.Value (L_ALLOW_ACTUAL_UPDATE_METR),'N');
IF (l_allow_actual_update = 'N') THEN
--object is not active and profile is N hence do not allow update
IF (l_freeze_status = Fnd_Api.G_FALSE) THEN
IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
Fnd_Message.set_name('AMS', 'AMS_METR_UPDATE_ACTUAL');
ELSIF p_operation_mode = G_DELETE THEN
NULL; -- nothing here yet
SELECT *
FROM ams_act_metrics_all
WHERE activity_metric_id = p_act_metric_rec.activity_metric_id;
Ams_Utility_Pvt.debug_message(l_full_name||': Update');
UPDATE ams_act_metrics_all
SET func_committed_value = p_func_committed_value
WHERE arc_act_metric_used_by = p_arc_act_metric_used_by
AND act_metric_used_by_id = p_act_metric_used_by_id
AND metric_id = p_metric_id ;
SELECT metric_id
FROM ams_act_metrics_all
WHERE activity_metric_id =l_act_met_id;
SELECT budget_amount_tc, transaction_currency_code,
budget_amount_fc, functional_currency_code
FROM ams_campaigns_all_b
WHERE campaign_id = l_obj_id;
SELECT budget_amount_tc, transaction_currency_code,
budget_amount_fc, functional_currency_code
FROM ams_campaign_schedules_b
WHERE schedule_id = l_obj_id;
SELECT fund_amount_tc, currency_code_tc,
fund_amount_fc, currency_code_fc
FROM ams_event_offers_all_b
WHERE event_offer_id = l_obj_id;
SELECT fund_amount_tc, currency_code_tc,
fund_amount_fc, currency_code_fc
FROM ams_event_headers_all_b
WHERE event_header_id = l_obj_id;
SELECT budget_amount_tc, transaction_currency_code,
budget_amount_fc, functional_currency_code
FROM ams_deliverables_all_b
WHERE deliverable_id = l_obj_id;
SELECT trans_forecasted_value,
transaction_currency_code,
func_forecasted_value,
functional_currency_code
FROM ams_act_metrics_all actmet, ams_metrics_all_b met
WHERE actmet.metric_id = met.metric_id
AND arc_act_metric_used_by = l_obj_type
AND act_metric_used_by_id = l_obj_id
AND metric_category = l_category_id
AND summarize_to_metric IS NULL
AND metric_calculation_type <> 'FORMULA';
SELECT activity_metric_id
FROM ams_act_metrics_all
WHERE arc_act_metric_used_by = l_used_by_type
AND act_metric_used_by_id = l_used_by_id
AND rollup_to_metric IS NOT NULL;
UPDATE ams_act_metrics_all
SET rollup_to_metric = NULL
WHERE arc_act_metric_used_by = l_used_by_type
AND act_metric_used_by_id = p_used_by_id
AND rollup_to_metric IS NOT NULL;
SELECT activity_metric_id,
func_actual_value,
'' description,
functional_currency_code
FROM ams_act_metrics_all amet
WHERE arc_act_metric_used_by = l_obj_type
AND act_metric_used_by_id = l_obj_id
AND (published_flag IS NULL OR published_flag = 'N')
AND EXISTS
(SELECT 'x' FROM ams_metrics_all_b met
WHERE met.metric_id = amet.metric_id
AND metric_calculation_type IN
('FUNCTION', 'MANUAL')
AND metric_category = 901) -- Costs only
FOR UPDATE OF published_flag NOWAIT;
UPDATE ams_act_metrics_all
SET published_flag = 'Y'
WHERE CURRENT OF c_get_post_metrics;
SELECT NVL( minimum_accountable_unit, POWER( 10, (-1 * PRECISION))),
POWER( 10, (-1 * EXTENDED_PRECISION))
INTO x_mau, x_xau
FROM FND_CURRENCIES
WHERE currency_code = x_currency;
SELECT func_forecasted_value, func_actual_value, functional_currency_code,
last_update_date, act_met_hst_id, object_version_number,
func_forecasted_delta, func_actual_delta
FROM ams_act_metric_hst
WHERE activity_metric_id = l_act_metric_id
AND last_update_date =
(SELECT MAX(last_update_date)
FROM ams_act_metric_hst
WHERE activity_metric_id = l_act_metric_id);
SELECT activity_metric_id,
last_update_date,
last_updated_by,
creation_date,
created_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_update_date,
variable_value,
forecasted_variable_value,
computed_using_function_value,
metric_uom_code,
org_id,
difference_since_last_calc,
activity_metric_origin_id,
arc_activity_metric_origin,
days_since_last_refresh,
scenario_id,
SUMMARIZE_TO_METRIC,
ROLLUP_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,
attribute_category,
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: 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 */
FROM ams_act_metrics_all
WHERE activity_metric_id = l_act_metric_id;
SELECT ams_act_metric_hst_s.NEXTVAL
FROM dual;
SELECT COUNT(*)
FROM ams_act_metric_hst
WHERE act_met_hst_id = l_test_id;
l_last_update_date DATE;
IF p_action = G_DELETE THEN
l_actmet_rec.last_update_date := SYSDATE;
l_last_update_date,
l_act_met_hst_id,
l_object_version_number,
l_func_forecasted_delta,
l_func_actual_delta;
(p_action = G_DELETE OR
NVL(l_func_forecasted_value,0) <>
NVL(l_actmet_rec.func_forecasted_value,0) OR
NVL(l_func_actual_value,0) <> NVL(l_actmet_rec.func_actual_value,0) OR
NVL(l_functional_currency_code,'NULL') <>
NVL(l_actmet_rec.functional_currency_code,'NULL'))
THEN
-- If change occurs within the same day as last, update.
IF TRUNC(l_last_update_date) = TRUNC(l_actmet_rec.last_update_date)
THEN
-- update the current history record.
-- l_actmet_rec := p_actmet_rec;
UPDATE ams_act_metric_hst
SET LAST_UPDATE_DATE = l_actmet_rec.LAST_UPDATE_DATE,
LAST_UPDATED_BY = l_actmet_rec.LAST_UPDATED_BY,
CREATION_DATE = l_actmet_rec.CREATION_DATE,
CREATED_BY = l_actmet_rec.CREATED_BY,
LAST_UPDATE_LOGIN = l_actmet_rec.LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER = l_actmet_rec.OBJECT_VERSION_NUMBER,
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,
APPLICATION_ID = l_actmet_rec.APPLICATION_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,
DIRTY_FLAG = l_actmet_rec.DIRTY_FLAG,
FUNC_ACTUAL_VALUE = l_actmet_rec.FUNC_ACTUAL_VALUE,
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,
ORG_ID = l_actmet_rec.ORG_ID,
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,
DAYS_SINCE_LAST_REFRESH = l_actmet_rec.DAYS_SINCE_LAST_REFRESH,
SUMMARIZE_TO_METRIC = l_actmet_rec.SUMMARIZE_TO_METRIC,
ROLLUP_TO_METRIC = l_actmet_rec.ROLLUP_TO_METRIC,
SCENARIO_ID = l_actmet_rec.SCENARIO_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,
ARC_FUNCTION_USED_BY = l_actmet_rec.ARC_FUNCTION_USED_BY,
FUNCTION_USED_BY_ID = l_actmet_rec.FUNCTION_USED_BY_ID,
PURCHASE_REQ_RAISED_FLAG = l_actmet_rec.PURCHASE_REQ_RAISED_FLAG,
SENSITIVE_DATA_FLAG = l_actmet_rec.SENSITIVE_DATA_FLAG,
BUDGET_ID = l_actmet_rec.BUDGET_ID,
FORECASTED_VARIABLE_VALUE = l_actmet_rec.FORECASTED_VARIABLE_VALUE,
HIERARCHY_ID = l_actmet_rec.HIERARCHY_ID,
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,
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,
STATUS_CODE = l_actmet_rec.STATUS_CODE,
ACTION_CODE = l_actmet_rec.ACTION_CODE,
METHOD_CODE = l_actmet_rec.METHOD_CODE,
BASIS_YEAR = l_actmet_rec.BASIS_YEAR,
EX_START_NODE = l_actmet_rec.EX_START_NODE,
HIERARCHY_TYPE = l_actmet_rec.HIERARCHY_TYPE,
DEPEND_ACT_METRIC = l_actmet_rec.DEPEND_ACT_METRIC,
FUNC_FORECASTED_DELTA = l_func_forecasted_delta,
FUNC_ACTUAL_DELTA = l_func_actual_delta
WHERE act_met_hst_id = l_act_met_hst_id
AND object_version_number = l_object_version_number;
INSERT INTO ams_act_metric_hst
(ACT_MET_HST_ID,
ACTIVITY_METRIC_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
ACT_METRIC_USED_BY_ID,
ARC_ACT_METRIC_USED_BY,
APPLICATION_ID,
METRIC_ID,
TRANSACTION_CURRENCY_CODE,
TRANS_FORECASTED_VALUE,
TRANS_COMMITTED_VALUE,
TRANS_ACTUAL_VALUE,
FUNCTIONAL_CURRENCY_CODE,
FUNC_FORECASTED_VALUE,
FUNC_COMMITTED_VALUE,
DIRTY_FLAG,
FUNC_ACTUAL_VALUE,
LAST_CALCULATED_DATE,
VARIABLE_VALUE,
COMPUTED_USING_FUNCTION_VALUE,
METRIC_UOM_CODE,
ORG_ID,
DIFFERENCE_SINCE_LAST_CALC,
ACTIVITY_METRIC_ORIGIN_ID,
ARC_ACTIVITY_METRIC_ORIGIN,
DAYS_SINCE_LAST_REFRESH,
SUMMARIZE_TO_METRIC,
ROLLUP_TO_METRIC,
SCENARIO_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
DESCRIPTION,
ACT_METRIC_DATE,
ARC_FUNCTION_USED_BY,
FUNCTION_USED_BY_ID,
PURCHASE_REQ_RAISED_FLAG,
SENSITIVE_DATA_FLAG,
BUDGET_ID,
FORECASTED_VARIABLE_VALUE,
HIERARCHY_ID,
PUBLISHED_FLAG,
PRE_FUNCTION_NAME,
POST_FUNCTION_NAME,
START_NODE,
FROM_LEVEL,
TO_LEVEL,
FROM_DATE,
TO_DATE,
AMOUNT1,
AMOUNT2,
AMOUNT3,
PERCENT1,
PERCENT2,
PERCENT3,
STATUS_CODE,
ACTION_CODE,
METHOD_CODE,
BASIS_YEAR,
EX_START_NODE,
HIERARCHY_TYPE,
DEPEND_ACT_METRIC,
FUNC_FORECASTED_DELTA,
FUNC_ACTUAL_DELTA)
VALUES
(L_ACT_MET_HST_ID,
l_actmet_rec.ACTIVITY_METRIC_ID,
l_actmet_rec.LAST_UPDATE_DATE,
l_actmet_rec.LAST_UPDATED_BY,
l_actmet_rec.CREATION_DATE,
l_actmet_rec.CREATED_BY,
l_actmet_rec.LAST_UPDATE_LOGIN,
l_actmet_rec.OBJECT_VERSION_NUMBER,
l_actmet_rec.ACT_METRIC_USED_BY_ID,
l_actmet_rec.ARC_ACT_METRIC_USED_BY,
l_actmet_rec.APPLICATION_ID,
l_actmet_rec.METRIC_ID,
l_actmet_rec.TRANSACTION_CURRENCY_CODE,
l_actmet_rec.TRANS_FORECASTED_VALUE,
l_actmet_rec.TRANS_COMMITTED_VALUE,
l_actmet_rec.TRANS_ACTUAL_VALUE,
l_actmet_rec.FUNCTIONAL_CURRENCY_CODE,
l_actmet_rec.FUNC_FORECASTED_VALUE,
l_actmet_rec.FUNC_COMMITTED_VALUE,
l_actmet_rec.DIRTY_FLAG,
l_actmet_rec.FUNC_ACTUAL_VALUE,
l_actmet_rec.LAST_CALCULATED_DATE,
l_actmet_rec.VARIABLE_VALUE,
l_actmet_rec.COMPUTED_USING_FUNCTION_VALUE,
l_actmet_rec.METRIC_UOM_CODE,
l_actmet_rec.ORG_ID,
l_actmet_rec.DIFFERENCE_SINCE_LAST_CALC,
l_actmet_rec.ACTIVITY_METRIC_ORIGIN_ID,
l_actmet_rec.ARC_ACTIVITY_METRIC_ORIGIN,
l_actmet_rec.DAYS_SINCE_LAST_REFRESH,
l_actmet_rec.SUMMARIZE_TO_METRIC,
l_actmet_rec.ROLLUP_TO_METRIC,
l_actmet_rec.SCENARIO_ID,
l_actmet_rec.ATTRIBUTE_CATEGORY,
l_actmet_rec.ATTRIBUTE1,
l_actmet_rec.ATTRIBUTE2,
l_actmet_rec.ATTRIBUTE3,
l_actmet_rec.ATTRIBUTE4,
l_actmet_rec.ATTRIBUTE5,
l_actmet_rec.ATTRIBUTE6,
l_actmet_rec.ATTRIBUTE7,
l_actmet_rec.ATTRIBUTE8,
l_actmet_rec.ATTRIBUTE9,
l_actmet_rec.ATTRIBUTE10,
l_actmet_rec.ATTRIBUTE11,
l_actmet_rec.ATTRIBUTE12,
l_actmet_rec.ATTRIBUTE13,
l_actmet_rec.ATTRIBUTE14,
l_actmet_rec.ATTRIBUTE15,
l_actmet_rec.DESCRIPTION,
l_actmet_rec.ACT_METRIC_DATE,
l_actmet_rec.ARC_FUNCTION_USED_BY,
l_actmet_rec.FUNCTION_USED_BY_ID,
l_actmet_rec.PURCHASE_REQ_RAISED_FLAG,
l_actmet_rec.SENSITIVE_DATA_FLAG,
l_actmet_rec.BUDGET_ID,
l_actmet_rec.FORECASTED_VARIABLE_VALUE,
l_actmet_rec.HIERARCHY_ID,
l_actmet_rec.PUBLISHED_FLAG,
l_actmet_rec.PRE_FUNCTION_NAME,
l_actmet_rec.POST_FUNCTION_NAME,
l_actmet_rec.START_NODE,
l_actmet_rec.FROM_LEVEL,
l_actmet_rec.TO_LEVEL,
l_actmet_rec.FROM_DATE,
l_actmet_rec.TO_DATE,
l_actmet_rec.AMOUNT1,
l_actmet_rec.AMOUNT2,
l_actmet_rec.AMOUNT3,
l_actmet_rec.PERCENT1,
l_actmet_rec.PERCENT2,
l_actmet_rec.PERCENT3,
l_actmet_rec.STATUS_CODE,
l_actmet_rec.ACTION_CODE,
l_actmet_rec.METHOD_CODE,
l_actmet_rec.BASIS_YEAR,
l_actmet_rec.EX_START_NODE,
l_actmet_rec.HIERARCHY_TYPE,
l_actmet_rec.DEPEND_ACT_METRIC,
l_func_forecasted_delta,
l_func_actual_delta
);
l_date_buckets.DELETE;
l_date_buckets.delete(l_last_bucket+1,l_date_buckets.last);
SELECT min(last_update_date)
FROM ams_act_metric_hst
WHERE metric_id = l_metric_id
AND arc_act_metric_used_by = l_object_type
AND act_metric_used_by_id = l_object_id;
SELECT metric_calculation_type, metric_category, display_type
FROM ams_metrics_all_b
WHERE metric_id = l_metric_id;
SELECT a.last_update_date slice_date,
a.functional_currency_code currency_code,
NVL(SUM(a.func_forecasted_value),0) forecasted_value,
NVL(SUM(a.func_actual_value),0) actual_value
FROM ams_act_metric_hst a
WHERE (a.act_met_hst_id, a.activity_metric_id) IN
(SELECT MAX(b.act_met_hst_id), b.activity_metric_id
FROM ams_act_metric_hst b
WHERE b.metric_id = l_metric_id
AND b.arc_act_metric_used_by = l_object_type
AND b.act_metric_used_by_id = l_object_id
AND TRUNC(b.last_update_date) <= TRUNC(l_slice_date)
GROUP BY b.activity_metric_id)
GROUP BY a.last_update_date, a.functional_currency_code;
SELECT a.last_update_date slice_date,
a.functional_currency_code currency_code,
NVL(a.func_forecasted_value,0) forecasted_value,
NVL(a.func_actual_value,0) actual_value
FROM ams_act_metric_hst a
WHERE a.last_update_date =
(SELECT MAX(b.last_update_date)
FROM ams_act_metric_hst b
WHERE b.metric_id = l_metric_id
AND b.arc_act_metric_used_by = l_object_type
AND b.act_metric_used_by_id = l_object_id
AND TRUNC(b.last_update_date) <= TRUNC(l_slice_date))
AND a.metric_id = l_metric_id
AND a.arc_act_metric_used_by = l_object_type
AND a.act_metric_used_by_id = l_object_id;
l_result_table.delete(l_result_table.first);
SELECT activity_metric_id,
last_update_date,
last_updated_by,
creation_date,
created_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,
forecasted_variable_value,
computed_using_function_value,
metric_uom_code,
org_id,
difference_since_last_calc,
activity_metric_origin_id,
arc_activity_metric_origin,
days_since_last_refresh,
scenario_id,
SUMMARIZE_TO_METRIC,
ROLLUP_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,
attribute_category,
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,
hierarchy_type,
status_code,
method_code,
action_code,
basis_year,
ex_start_node
FROM ams_act_metrics_all
WHERE arc_act_metric_used_by = c_source_object_type
and act_metric_used_by_id = c_source_object_id;
SELECT activity_metric_id
FROM ams_act_metrics_all
WHERE metric_id = cv_metric_id
AND act_metric_used_by_id = cv_act_metric_used_by_id
AND arc_act_metric_used_by = cv_arc_act_metric_used_by;
select accrual_type
from ams_metrics_all_b
where metric_id = ca_metric_id;
UPDATE ams_act_metrics_all
SET
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.User_ID,
last_update_login = Fnd_Global.Conc_Login_ID,
variable_value = metrics_rec.variable_value,
forecasted_variable_value = metrics_rec.forecasted_variable_value
WHERE activity_metric_id = l_activity_metric_id
and metric_id = metrics_rec.metric_id
and arc_act_metric_used_by = l_source_object_type
and act_metric_used_by_id = l_target_object_id;
SELECT Fnd_Api.G_TRUE
FROM ams_campaigns_all_b
WHERE campaign_id = id;
SELECT Fnd_Api.G_TRUE
FROM ams_campaign_schedules_b
WHERE schedule_id = id;
SELECT Fnd_Api.G_TRUE
FROM ams_deliverables_all_b
WHERE deliverable_id = id;
SELECT Fnd_Api.G_TRUE
FROM ams_event_headers_all_b
WHERE event_header_id = id;
SELECT Fnd_Api.G_TRUE
FROM ams_event_offers_all_b
WHERE event_offer_id = id;
l_sql := 'UPDATE ' || UPPER(l_table_name) ||
' SET object_version_number = object_version_number '||
' WHERE ' || UPPER(l_pk_name) || ' = :b1 ';
PROCEDURE delete_actmetrics_assoc (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_object_type IN VARCHAR2,
p_object_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(60) := 'Delete ActMetric Associations';
SAVEPOINT delete_actmetrics_assoc;
DELETE FROM ams_act_metrics_all
WHERE arc_act_metric_used_by = p_object_type
AND act_metric_used_by_id = p_object_id;
ROLLBACK TO delete_actmetrics_assoc;
END delete_actmetrics_assoc;
SELECT 1 FROM AMS_CAMPAIGNS_ALL_B
WHERE CAMPAIGN_ID = p_campaign_id;
SELECT 1 FROM AMS_CAMPAIGN_SCHEDULES_B
WHERE schedule_ID = p_campaign_schedule_id;
SELECT 1 FROM AMS_DELIVERABLES_ALL_B
WHERE deliverable_ID = p_deliverable_id;
SELECT 1 FROM AMS_EVENT_OFFERS_ALL_B
WHERE EVENT_OFFER_ID = p_event_schedule_id
and event_object_type = 'EVEO';
SELECT 1 FROM AMS_EVENT_OFFERS_ALL_B
WHERE EVENT_OFFER_ID = p_one_off_id
and event_object_type = 'EONE';
SELECT 1 FROM AMS_EVENT_HEADERS_ALL_B
WHERE EVENT_HEADER_ID = p_event_id;
SELECT 1 FROM AMS_ACT_LISTS
WHERE ACT_LIST_HEADER_ID = p_act_list_id;
select count(1) budget_count
from ozf_act_budgets
where transfer_type = 'REQUEST'
and arc_act_budget_used_by = l_object_type
and act_budget_used_by_id = l_object_id
and budget_source_type = 'FUND'
and status_code = 'APPROVED';