The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE_HISTORY CONSTANT VARCHAR2(1) := 'Y';
PROCEDURE UPDATE_VARIABLE
(x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.G_TRUE,
p_object_list IN object_currency_table := Empty_object_currency_table,
p_current_date IN date,
p_func_currency IN varchar2
);
PROCEDURE Update_formulas(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.G_TRUE,
p_object_list IN object_currency_table := Empty_object_currency_table,
p_current_date IN date,
p_func_currency IN varchar2
);
G_RCAM_Parents_table.delete;
G_CSCH_Parents_table.delete;
G_EVEO_Parents_table.delete;
G_EVEH_Parents_table.delete;
G_EONE_Parents_table.delete;
G_FORECASTED_STACK.delete;
G_ACTUAL_STACK.delete;
G_CAMP_status_table.delete;
G_CSCH_status_table.delete;
G_EVEO_status_table.delete;
G_EVEH_status_table.delete;
SELECT --/*+ first_rows */
activity_metric_id, a.metric_id, a.object_version_number,
metric_uom_code, func_forecasted_value, func_actual_value,
functional_currency_code, summarize_to_metric, rollup_to_metric,
last_calculated_date, days_since_last_refresh,
difference_since_last_calc, computed_using_function_value,
activity_metric_origin_id,
--variable_value, forecasted_variable_value,
transaction_currency_code,
G_LEAF_LEVEL, DECODE(l_calc_type,G_ROLLUP,rollup_to_metric,summarize_to_metric),
--NULL,
dirty_flag,
func_actual_value orig_actual_value,
func_forecasted_value orig_forecasted_value,
trans_actual_value, trans_forecasted_value, depend_act_metric,
arc_act_metric_used_by, act_metric_used_by_id,
value_type ,
metric_calculation_type ,
accrual_type ,
compute_using_function ,
default_uom_code, display_type
FROM ams_act_metrics_all a, ams_metrics_all_b b
WHERE arc_act_metric_used_by IN
(select lookup_code from ams_lookups
where lookup_type in ('AMS_METRIC_OBJECT_TYPE', 'AMS_METRIC_ROLLUP_TYPE'))
-- Replaced with metadata lookups above.
--(G_CAMP, G_CSCH, G_DELV, G_EVEO, G_EVEH, G_RCAM, G_EONE)
--BUG2845365: Removed dialogue components.
--G_DILG, G_AMS_COMP_START, G_AMS_COMP_SHOW_WEB_PAGE, G_AMS_COMP_END)
AND dirty_flag = G_IS_DIRTY
AND a.metric_id = b.metric_id
AND b.metric_calculation_type = l_calc_type;
SELECT
activity_metric_id, a.metric_id, a.object_version_number,
metric_uom_code, func_forecasted_value, func_actual_value,
functional_currency_code, summarize_to_metric, rollup_to_metric,
last_calculated_date, days_since_last_refresh,
difference_since_last_calc, computed_using_function_value,
activity_metric_origin_id,
-- variable_value, forecasted_variable_value,
transaction_currency_code,
G_LEAF_LEVEL, DECODE(l_calc_type,G_ROLLUP,rollup_to_metric,summarize_to_metric),
--NULL,
dirty_flag,
func_actual_value orig_actual_value,
func_forecasted_value orig_forecasted_value,
trans_actual_value, trans_forecasted_value, depend_act_metric,
arc_act_metric_used_by, act_metric_used_by_id,
value_type ,
metric_calculation_type ,
accrual_type ,
compute_using_function ,
default_uom_code, display_type
FROM ams_act_metrics_all a, ams_metrics_all_b b
WHERE dirty_flag = G_IS_DIRTY
AND a.metric_id = b.metric_id
AND b.metric_calculation_type = l_calc_type
AND a.arc_act_metric_used_by = l_object_type
AND a.act_metric_used_by_id = l_object_id;
SELECT activity_metric_id, a.metric_id, a.object_version_number,
metric_uom_code, func_forecasted_value, func_actual_value,
functional_currency_code, summarize_to_metric, rollup_to_metric,
last_calculated_date, days_since_last_refresh,
difference_since_last_calc, computed_using_function_value,
activity_metric_origin_id,
-- variable_value, forecasted_variable_value,
transaction_currency_code,
G_LEAF_LEVEL, DECODE(metric_calculation_type,G_ROLLUP,rollup_to_metric,summarize_to_metric),
--NULL,
dirty_flag,
func_actual_value orig_actual_value,
func_forecasted_value orig_forecasted_value,
trans_actual_value, trans_forecasted_value, depend_act_metric,
arc_act_metric_used_by, act_metric_used_by_id,
value_type ,
metric_calculation_type ,
accrual_type ,
compute_using_function ,
default_uom_code, display_type
FROM ams_act_metrics_all a, ams_metrics_all_b b
WHERE activity_metric_id = l_parent_act_metric_id
AND a.metric_id = b.metric_id;
l_used_actmet_ids.DELETE;
SELECT COUNT(1)
FROM ams_metric_accruals
WHERE metric_id = l_metric_id;
l_act_metric_id_levels.DELETE(l_id_outside);
SELECT activity_metric_id,
func_forecasted_value,
func_actual_value,
functional_currency_code,
metric_uom_code,
arc_act_metric_used_by,
act_metric_used_by_id
BULK COLLECT INTO l_activity_metric_ids,
l_func_forecasted_values,
l_func_actual_values,
l_functional_currency_codes,
l_metric_uom_codes,
l_arc_act_metric_used_bys,
l_act_metric_used_by_ids
FROM ams_act_metrics_all
WHERE summarize_to_metric = l_id;
SELECT activity_metric_id,
func_forecasted_value,
func_actual_value,
functional_currency_code,
metric_uom_code,
arc_act_metric_used_by,
act_metric_used_by_id
BULK COLLECT INTO l_activity_metric_ids,
l_func_forecasted_values,
l_func_actual_values,
l_functional_currency_codes,
l_metric_uom_codes,
l_arc_act_metric_used_bys,
l_act_metric_used_by_ids
FROM ams_act_metrics_all
WHERE rollup_to_metric = l_id;
l_activity_metric_ids.DELETE;
l_func_forecasted_values.DELETE;
l_func_actual_values.DELETE;
l_functional_currency_codes.DELETE;
l_metric_uom_codes.DELETE;
l_arc_act_metric_used_bys.DELETE;
l_act_metric_used_by_ids.DELETE;
l_CAMP_status_table.delete;
l_CSCH_status_table.delete;
l_EVEO_status_table.delete;
l_EVEH_status_table.delete;
PROCEDURE Bulk_update(
x_dirty_actmet IN OUT NOCOPY act_met_ref_rec_set_type,
p_calc_type IN VARCHAR2,
p_commit IN varchar2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(100) := 'BULK_UPDATE';
UPDATE ams_act_metrics_all
SET last_calculated_date = l_cal_date_table (l_count),
days_since_last_refresh = l_sin_last_ref(l_count),
func_actual_value = l_func_act_va(l_count),
trans_actual_value = l_tran_act_va(l_count),
func_forecasted_value = l_func_for_value(l_count),
trans_forecasted_value = l_trans_for_value(l_count),
dirty_flag = l_dirty_flag(l_count),
difference_since_last_calc = l_diff(l_count),
functional_currency_code = l_func_curr_code(l_count),
transaction_currency_code = l_trans_curr_code(l_count),
last_update_date = l_today,
object_version_number = object_version_number + 1
WHERE activity_metric_id = l_act_met_id (l_count)
AND OBJECT_VERSION_NUMBER = l_obj_version(l_count);
UPDATE ams_act_metrics_all
SET dirty_flag = G_IS_DIRTY
WHERE activity_metric_id = l_summarize_ids(l_count);
/* 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 ((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 = l_act_met_id (l_count)); */
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 ((b.metric_id in (select decode(c1.summary_metric_id, NULL, f1.source_id,c1.summary_metric_id)
from ams_metric_formulas f1, ams_metrics_all_b c1
where c1.metric_id=f1.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 = l_act_met_id (l_count));
write_msg(L_API_NAME, 'BATCH UPDATE COMMIT: COUNT='||
l_act_met_id.COUNT||', Dirty index='||l_index);
l_act_met_id.delete;
l_cal_date_table.delete;
l_sin_last_ref.delete;
l_func_act_va.delete;
l_tran_act_va.delete;
l_func_for_value.delete;
l_trans_for_value.delete;
l_dirty_flag.delete;
l_diff.delete;
l_func_curr_code.delete;
l_trans_curr_code.delete;
l_obj_version.delete;
l_summarize_ids.delete;
END Bulk_update;
p_update_history IN VARCHAR2 := Fnd_Api.G_FALSE
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
UPDATE_VARIABLE(x_errbuf => x_errbuf,
x_retcode => x_retcode,
p_commit => FND_API.G_FALSE,
p_current_date => l_current_date,
p_func_currency => l_default_currency);
Bulk_Update(
x_dirty_actmet => l_dirty_actmet,
p_calc_type => G_ROLLUP,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status
);
l_dirty_actmet.DELETE;
Bulk_Update(
x_dirty_actmet => l_dirty_actmet,
p_calc_type => G_SUMMARY,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status
);
l_dirty_actmet.DELETE;
Update_formulas(x_errbuf => x_errbuf,
x_retcode => x_retcode,
p_commit => FND_API.G_FALSE,
p_current_date => l_current_date,
p_func_currency => l_default_currency);
IF p_update_history IN (Fnd_Api.G_TRUE, G_UPDATE_HISTORY) THEN
Update_History(p_commit => FND_API.G_FALSE);
p_update_history IN VARCHAR2 := Fnd_Api.G_FALSE
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
UPDATE_VARIABLE(x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_commit => FND_API.G_FALSE,
p_object_list => l_object_list,
p_current_date => l_current_date,
p_func_currency => l_default_currency);
Bulk_Update(
x_dirty_actmet => l_dirty_actmet,
p_calc_type => G_ROLLUP,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status
);
l_dirty_actmet.DELETE;
Bulk_Update(
x_dirty_actmet => l_dirty_actmet,
p_calc_type => G_SUMMARY,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status
);
l_dirty_actmet.DELETE;
Update_formulas(x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_commit => FND_API.G_FALSE,
p_object_list => l_object_list,
p_current_date => l_current_date,
p_func_currency => l_default_currency);
IF p_update_history IN (Fnd_Api.G_TRUE, G_UPDATE_HISTORY) THEN
Update_History(p_commit => FND_API.G_FALSE);
SELECT --/*+ first_rows */
a.metric_parent_id, b.activity_metric_id, b.act_metric_used_by_id,
b.arc_act_metric_used_by
FROM ams_metrics_all_b a, ams_act_metrics_all b
WHERE a.metric_parent_id IS NOT NULL
AND b.rollup_to_metric IS NULL
AND a.metric_id = b.metric_id;
SELECT /*+ first_rows */
a.metric_parent_id, b.activity_metric_id, b.act_metric_used_by_id,
b.arc_act_metric_used_by
FROM ams_metrics_all_b a, ams_act_metrics_all b
WHERE a.metric_parent_id IS NOT NULL
AND b.rollup_to_metric IS NULL
AND a.metric_id = b.metric_id
AND b.arc_act_metric_used_by = p_object_type
AND b.act_metric_used_by_id = p_object_id;
G_RCAM_Parents_table.DELETE;
G_CSCH_Parents_table.DELETE;
G_EVEO_Parents_table.DELETE;
G_EVEH_Parents_table.DELETE;
G_EONE_Parents_table.DELETE;
l_metric_parent_ids.delete;
G_RCAM_Parents_table.DELETE;
G_CSCH_Parents_table.DELETE;
G_EVEO_Parents_table.DELETE;
G_EVEH_Parents_table.DELETE;
G_EONE_Parents_table.DELETE;
UPDATE ams_act_metrics_all
SET rollup_to_metric = l_act_metric_parents (l_row_count)
WHERE activity_metric_id = l_act_metrics (l_row_count);
UPDATE ams_act_metrics_all
SET dirty_flag = G_IS_DIRTY
WHERE activity_metric_id = l_act_metric_parents (l_row_count)
AND dirty_flag = G_NOT_DIRTY;
write_msg(L_API_NAME,'END: ROWS UPDATED='||l_act_metrics.count);
l_act_metric_parents.DELETE;
l_act_metrics.DELETE;
SELECT metric_parent_id
FROM ams_metrics_all_b
WHERE metric_id = l_metric_id;
SELECT parent_campaign_id
FROM ams_campaigns_all_b
WHERE campaign_id = l_act_metric_used_by_id
AND UPPER(show_campaign_flag) = 'Y';
SELECT event_header_id
FROM ams_event_offers_all_b
WHERE event_offer_id = l_act_metric_used_by_id;
SELECT program_id
FROM ams_event_headers_all_b
WHERE event_header_id = l_act_metric_used_by_id;
SELECT program_id
FROM ams_event_headers_all_b
WHERE event_header_id = l_act_metric_used_by_id;
SELECT rollup_type
FROM ams_campaigns_all_b
WHERE campaign_id = l_camp_id;
SELECT campaign_id
FROM ams_campaign_schedules_b
WHERE schedule_id = l_act_metric_used_by_id;
SELECT parent_id
FROM ams_event_offers_all_b
WHERE event_offer_id = l_act_metric_used_by_id
AND parent_type = G_RCAM;
SELECT activity_metric_id
FROM ams_act_metrics_all
WHERE metric_id = l_metric_parent_id
AND act_metric_used_by_id = l_obj_id
AND arc_act_metric_used_by = l_obj_code;
ELSE -- need to update this one
l_count := x_act_metrics.count +1;
SELECT /*+ first_rows */ activity_metric_id,
function_name,
func_actual_value,
last_calculated_date,
metric_category,
TRANSACTION_CURRENCY_CODE,
functional_currency_code
FROM ams_metrics_all_b a, ams_act_metrics_all b,
ams_lookups lkup
WHERE a.metric_id = b.metric_id
-- BUG4924982: Performance, join to metric definition.
AND a.arc_metric_used_for_object = lkup.lookup_code
AND lookup_type = 'AMS_METRIC_OBJECT_TYPE'
AND a.metric_calculation_type = G_FUNCTION
AND a.function_type = G_IS_FUNCTION;
SELECT /*+ first_rows */ activity_metric_id,
function_name,
func_actual_value,
last_calculated_date,
metric_category,
TRANSACTION_CURRENCY_CODE,
functional_currency_code
FROM ams_metrics_all_b a, ams_act_metrics_all b
WHERE a.metric_id = b.metric_id
AND b.arc_act_metric_used_by = l_object_type
AND b.act_metric_used_by_id = l_object_id
AND a.metric_calculation_type = G_FUNCTION
AND a.function_type = G_IS_FUNCTION;
SELECT DISTINCT function_name
FROM ams_metrics_all_b
WHERE arc_metric_used_for_object IN
(select lookup_code from ams_lookups
where lookup_type = 'AMS_METRIC_OBJECT_TYPE')
-- Replaced with metadata lookups above.
--(G_CAMP, G_CSCH, G_DELV, G_EVEO, G_EVEH, G_RCAM, G_EONE)
--BUG2845365: Removed dialogue components.
--G_DILG, G_AMS_COMP_START, G_AMS_COMP_SHOW_WEB_PAGE, G_AMS_COMP_END)
AND metric_calculation_type = G_FUNCTION
AND function_type = G_IS_PROCEDURE
AND enabled_flag = G_IS_ENABLED;
SELECT count(1)
FROM ams_act_metrics_all a, ams_metrics_all_b b
WHERE a.metric_id = b.metric_id
AND b.function_name = l_function_name
AND a.arc_act_metric_used_by = l_obj_type
AND a.act_metric_used_by_id = l_obj_id
AND b.metric_calculation_type = G_FUNCTION
AND b.function_type = G_IS_PROCEDURE
AND b.enabled_flag = G_IS_ENABLED;
l_function_list.DELETE;
l_temp_act_metric_ids.delete;
l_temp_function_names.delete;
l_temp_func_actual_values.delete;
l_temp_last_calculated_dates.delete;
l_temp_categories.delete;
l_temp_trans_currencies.delete;
l_temp_func_currencies.delete;
write_msg(L_API_NAME,'NEW ACTUALS TO UPDATE='||l_new_act_metric_ids.COUNT);
UPDATE ams_act_metrics_all
SET last_calculated_date = l_current_date,
last_update_date = l_current_date,
func_actual_value = l_new_func_actuals(l_count),
-- FUNCTIONAL_CURRENCY_CODE = l_func_currencies(l_count),
days_since_last_refresh = l_days_since_last_refreshs(l_count),
trans_actual_value = l_new_trans_actuals(l_count)
WHERE activity_metric_id = l_new_act_metric_ids(l_count);
UPDATE ams_act_metrics_all
SET dirty_flag = G_IS_DIRTY
WHERE activity_metric_id IN
(SELECT rollup_to_metric FROM ams_act_metrics_all
WHERE activity_metric_id = l_new_act_metric_ids(l_count)
AND rollup_to_metric IS NOT NULL
UNION ALL
SELECT summarize_to_metric FROM ams_act_metrics_all
WHERE activity_metric_id = l_new_act_metric_ids(l_count)
AND summarize_to_metric IS NOT NULL);
l_new_func_actuals.delete;
l_days_since_last_refreshs.delete;
l_new_trans_actuals.delete;
l_new_act_metric_ids.delete;
l_act_metric_ids.delete;
l_function_names.delete;
l_func_actual_values.delete;
l_last_calculated_dates.delete;
l_categories.delete;
l_trans_currencies.delete;
l_func_currencies.delete;
SELECT master_object_type,
master_object_id,
using_object_type,
using_object_id,
TO_NUMBER(DECODE(usage_type,G_CREATED,100,G_USED_BY,
NVL(pct_of_cost_to_charge_used_by,0)))
pct_of_cost_to_charge_used_by,
cost_frozen_flag
FROM ams_object_associations
WHERE object_association_id = p_obj_association_id ;
SELECT func_actual_value
FROM ams_act_metrics_all
WHERE activity_metric_origin_id = p_obj_association_id ;
SELECT metric_id
FROM ams_metrics_all_b
WHERE arc_metric_used_for_object = l_arc_act_metric_used_by
START WITH metric_id = l_metric_id
CONNECT BY PRIOR summary_metric_id = metric_id
ORDER BY LEVEL DESC ;
SELECT act.activity_metric_id activity_metric_id,
met.metric_id metric_id
FROM ams_act_metrics_all act,ams_metrics_all_b met
WHERE met.metric_id = act.metric_id
AND act.arc_act_metric_used_by = l_arc_act_metric_used_by
AND act.act_metric_used_by_id = l_act_metric_used_by_id
AND met.metric_category = l_metric_category ;
SELECT NVL(func_actual_value,0) func_actual_value,
NVL(func_forecasted_value,0) func_forecasted_value,
NVL(func_committed_value,0) func_committed_value
FROM ams_act_metrics_all
WHERE metric_id = l_met_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 ;
SELECT Fnd_Api.G_TRUE
FROM ams_campaigns_all_b
WHERE campaign_id = id
AND status_code = G_CANCELLED;
SELECT Fnd_Api.G_TRUE
FROM ams_campaign_schedules_b
WHERE schedule_id = id
AND status_code = G_CANCELLED;
SELECT Fnd_Api.G_TRUE
FROM ams_deliverables_all_b
WHERE deliverable_id = id
AND status_code = G_CANCELLED;
SELECT Fnd_Api.G_TRUE
FROM ams_event_headers_all_b
WHERE event_header_id = id
AND system_status_code = G_CANCELLED;
SELECT Fnd_Api.G_TRUE
FROM ams_event_offers_all_b
WHERE event_offer_id = id
AND system_status_code IN (G_CANCELLED);
PROCEDURE UPDATE_VARIABLE
(x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.G_TRUE,
p_object_list IN object_currency_table := Empty_object_currency_table,
p_current_date IN date,
p_func_currency IN varchar2
)
IS
L_API_NAME CONSTANT VARCHAR2(100) := 'UPDATE_VARIABLE';
SELECT activity_metric_id,
transaction_currency_code,
functional_currency_code,
actual_multiplier,
forecast_multiplier,
trans_actual_value,
trans_forecasted_value,
variable_value,
forecasted_variable_value,
LAST_CALCULATED_DATE
FROM (
SELECT a.activity_metric_id,
a.transaction_currency_code,
a.functional_currency_code,
a.variable_value,
a.forecasted_variable_value,
a.trans_actual_value,
a.trans_forecasted_value,
a.last_calculated_date,
a.dirty_flag,
sum(c.trans_actual_value) actual_multiplier,
sum(c.trans_forecasted_value) forecast_multiplier,
max(c.last_update_date) last_update_date
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.metric_id = to_number(compute_using_function)
GROUP BY a.activity_metric_id,
a.transaction_currency_code,
a.functional_currency_code,
a.VARIABLE_VALUE,
a.forecasted_variable_value,
a.trans_actual_value,
a.trans_forecasted_value,
a.last_calculated_date,
a.dirty_flag)
WHERE last_update_date > nvl(last_calculated_date,last_update_date-1)
OR functional_currency_code <> l_func_currency_code
OR dirty_flag = G_IS_DIRTY;
SELECT activity_metric_id,
transaction_currency_code,
functional_currency_code,
actual_multiplier,
forecast_multiplier,
trans_actual_value,
trans_forecasted_value,
variable_value,
forecasted_variable_value,
LAST_CALCULATED_DATE
FROM (
SELECT a.activity_metric_id,
a.transaction_currency_code,
a.functional_currency_code,
a.variable_value,
a.forecasted_variable_value,
a.trans_actual_value,
a.trans_forecasted_value,
a.last_calculated_date,
a.dirty_flag,
sum(c.trans_actual_value) actual_multiplier,
sum(c.trans_forecasted_value) forecast_multiplier,
max(c.last_update_date) last_update_date
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 = l_obj_type
AND a.act_metric_used_by_id = l_obj_id
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.metric_id = to_number(compute_using_function)
GROUP BY a.activity_metric_id,
a.transaction_currency_code,
a.functional_currency_code,
a.VARIABLE_VALUE,
a.forecasted_variable_value,
a.trans_actual_value,
a.trans_forecasted_value,
a.last_calculated_date,
a.dirty_flag)
WHERE last_update_date > nvl(last_calculated_date,last_update_date-1)
OR functional_currency_code <> l_func_currency_code
OR dirty_flag = G_IS_DIRTY;
l_temp_activity_metric_ids.delete;
l_temp_trans_currencies.delete;
l_temp_func_currencies.delete;
l_temp_actual_multipliers.delete;
l_temp_forecast_multipliers.delete;
l_temp_trans_actual_values.delete;
l_temp_trans_forecasted_values.delete;
l_temp_variable_values.delete;
l_temp_forecasted_variable_val.delete;
l_temp_LAST_CALCULATED_DATEs.delete;
UPDATE ams_act_metrics_all
SET last_calculated_date = l_current_date,
last_update_date = l_current_date,
object_version_number = object_version_number + 1,
functional_currency_code = l_new_func_currencies(l_index),
DAYS_SINCE_LAST_REFRESH = l_new_days_since_last_refreshs(l_index),
--trans_actual_value = l_new_trans_actual_values(l_index),
func_actual_value = l_new_func_actual_values(l_index),
func_forecasted_value = l_new_func_forecasted_values(l_index),
trans_forecasted_value = l_new_trans_forecasted_values(l_index),
variable_value = l_new_variable_values(l_index),
forecasted_variable_value =
l_new_forecast_variable_values(l_index),
dirty_flag = G_NOT_DIRTY
WHERE activity_metric_id = l_activity_metric_ids(l_index);
UPDATE ams_act_metrics_all
SET dirty_flag = G_IS_DIRTY
WHERE activity_metric_id IN
(SELECT rollup_to_metric FROM ams_act_metrics_all
WHERE activity_metric_id = l_activity_metric_ids(l_index)
UNION ALL
SELECT summarize_to_metric FROM ams_act_metrics_all
WHERE activity_metric_id = l_activity_metric_ids(l_index));
l_activity_metric_ids.delete;
l_trans_currencies.delete;
l_func_currencies.delete;
l_actual_multipliers.delete;
l_forecast_multipliers.delete;
l_trans_actual_values.delete;
l_trans_forecasted_values.delete;
l_variable_values.delete;
l_forecasted_variable_values.delete;
l_LAST_CALCULATED_DATEs.delete;
l_new_activity_metric_ids.delete;
l_new_variable_values.delete;
l_new_func_forecasted_values.delete;
l_new_trans_forecasted_values.delete;
l_new_func_currencies.delete;
l_new_days_since_last_refreshs.delete;
l_new_last_calculated_dates.delete;
l_new_func_actual_values.delete;
l_new_forecast_variable_values.delete;
PROCEDURE Update_Variable (
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
)
is
L_API_NAME CONSTANT VARCHAR2(100) := 'UPDATE_VARIABLE';
savepoint sp_Update_Variable;
Update_Variable(x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_commit => FND_API.G_FALSE,
p_object_list => l_object_list,
p_current_date => l_current_date,
p_func_currency => l_default_currency);
ROLLBACK TO sp_Update_Variable;
ROLLBACK TO sp_Update_Variable;
ROLLBACK TO sp_Update_Variable;
end Update_Variable;
SELECT decode(rollup_type,G_RCAM,G_RCAM,G_CAMP), campaign_id, transaction_currency_code, G_RCAM, parent_campaign_id,
decode(status_code, G_CANCELLED, Fnd_Api.G_TRUE, Fnd_Api.G_FALSE) cancelled_flag,
upper(show_campaign_flag)
FROM ams_campaigns_all_b
WHERE campaign_id = l_act_metric_used_by_id;
SELECT G_CSCH, campaign_id, transaction_currency_code, G_CAMP, campaign_id,
decode(status_code, G_CANCELLED, Fnd_Api.G_TRUE, Fnd_Api.G_FALSE) cancelled_flag,
G_SHOW
FROM ams_campaign_schedules_b
WHERE schedule_id = l_act_metric_used_by_id;
SELECT G_EVEH, event_header_id, currency_code_tc, G_RCAM, program_id,
decode(system_status_code, G_CANCELLED, Fnd_Api.G_TRUE, Fnd_Api.G_FALSE) cancelled_flag,
G_SHOW
FROM ams_event_headers_all_b
WHERE event_header_id = l_act_metric_used_by_id;
SELECT event_object_type, event_offer_id, currency_code_tc,
decode(parent_type,null,G_EVEH,parent_type) parent_type,
decode(parent_type,null, event_header_id, parent_id) parent_id,
decode(system_status_code, G_CANCELLED, Fnd_Api.G_TRUE, Fnd_Api.G_FALSE) cancelled_flag,
G_SHOW
FROM ams_event_offers_all_b
WHERE event_offer_id = l_act_metric_used_by_id;
SELECT G_DELV, deliverable_id, transaction_currency_code, NULL, NULL,
decode(status_code, G_CANCELLED, Fnd_Api.G_TRUE, Fnd_Api.G_FALSE) cancelled_flag,
G_SHOW
FROM ams_deliverables_all_b
WHERE deliverable_id = l_act_metric_used_by_id;
SELECT G_ALIST, act_list_header_id, Ams_Actmetric_Pvt.DEFAULT_FUNC_CURRENCY,
NULL, NULL, Fnd_Api.G_FALSE cancelled_flag,
G_SHOW
FROM ams_act_lists
WHERE act_list_header_id = l_act_metric_used_by_id;
G_OBJECT_CACHE(l_obj_index).object_table.delete;
SELECT campaign_id obj_id, G_RCAM obj_type,
TRANSACTION_CURRENCY_CODE currency
FROM ams_campaigns_all_b
START WITH campaign_id = l_act_metric_used_by_id
CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
UNION ALL
SELECT campaign_id obj_id,
decode(rollup_type,G_RCAM,G_RCAM,G_CAMP) obj_type,
TRANSACTION_CURRENCY_CODE currency
FROM ams_campaigns_all_b
WHERE parent_campaign_id = l_act_metric_used_by_id
UNION ALL
SELECT EVENT_HEADER_ID obj_id, G_EVEH obj_type, CURRENCY_CODE_TC currency
FROM ams_event_headers_all_b
WHERE PROGRAM_ID = l_act_metric_used_by_id
UNION ALL
SELECT EVENT_OFFER_ID obj_id, G_EONE obj_type, CURRENCY_CODE_TC currency
FROM ams_event_offers_all_b
WHERE parent_id = l_act_metric_used_by_id
ORDER BY obj_type, obj_id;
SELECT campaign_id obj_id,
decode(rollup_type,G_RCAM,G_RCAM,G_CAMP) obj_type,
TRANSACTION_CURRENCY_CODE currency
FROM ams_campaigns_all_b
START WITH campaign_id = l_act_metric_used_by_id
CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
UNION ALL
SELECT SCHEDULE_ID obj_id, G_CSCH obj_type,
TRANSACTION_CURRENCY_CODE currency
FROM ams_campaign_schedules_b
WHERE CAMPAIGN_ID = l_act_metric_used_by_id
ORDER BY obj_type, obj_id;
SELECT campaign_id obj_id, G_RCAM obj_type,
TRANSACTION_CURRENCY_CODE currency
FROM ams_campaigns_all_b
START WITH campaign_id = (SELECT PROGRAM_ID FROM ams_event_headers_all_b
WHERE event_header_id = l_act_metric_used_by_id)
CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
UNION ALL
SELECT EVENT_HEADER_ID obj_id, G_EVEH obj_type, CURRENCY_CODE_TC currency
FROM ams_event_headers_all_b
WHERE EVENT_HEADER_ID = l_act_metric_used_by_id
UNION ALL
SELECT EVENT_OFFER_ID obj_id, G_EVEO obj_type, CURRENCY_CODE_TC currency
FROM ams_event_offers_all_b
WHERE EVENT_HEADER_ID = l_act_metric_used_by_id
ORDER BY obj_type, obj_id;
SELECT schedule_id obj_id, G_CSCH obj_type,TRANSACTION_CURRENCY_CODE currency
FROM ams_campaign_schedules_b
WHERE schedule_id = l_act_metric_used_by_id
UNION ALL
SELECT campaign_id obj_id,
decode(rollup_type,G_RCAM,G_RCAM,G_CAMP) obj_type,
TRANSACTION_CURRENCY_CODE currency
FROM ams_campaigns_all_b
START WITH campaign_id = (SELECT campaign_id FROM ams_campaign_schedules_b
WHERE schedule_id = l_act_metric_used_by_id)
CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
ORDER BY obj_type, obj_id;
SELECT EVENT_OFFER_ID obj_id, G_EVEO obj_type, CURRENCY_CODE_TC currency
FROM ams_event_offers_all_b
WHERE EVENT_OFFER_ID = l_act_metric_used_by_id
UNION ALL
SELECT EVENT_HEADER_ID obj_id, G_EVEH obj_type, CURRENCY_CODE_TC currency
FROM ams_event_headers_all_b
WHERE EVENT_HEADER_ID = (SELECT event_header_id FROM ams_event_offers_all_b
WHERE event_offer_id = l_act_metric_used_by_id)
UNION ALL
SELECT campaign_id obj_id, G_RCAM obj_type, TRANSACTION_CURRENCY_CODE currency
FROM ams_campaigns_all_b
START WITH campaign_id = (SELECT PROGRAM_ID FROM ams_event_headers_all_b
WHERE event_header_id = (SELECT event_header_id FROM ams_event_offers_all_b
WHERE event_offer_id = l_act_metric_used_by_id))
CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
ORDER BY obj_type, obj_id;
SELECT campaign_id obj_id, G_RCAM obj_type,
TRANSACTION_CURRENCY_CODE currency
FROM ams_campaigns_all_b
START WITH campaign_id = (SELECT PARENT_ID FROM ams_event_offers_all_b
WHERE event_offer_id = l_act_metric_used_by_id)
CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
UNION ALL
SELECT event_offer_id obj_id, G_EONE obj_type, CURRENCY_CODE_TC currency
FROM ams_event_offers_all_b
WHERE event_offer_id = l_act_metric_used_by_id
ORDER BY obj_type, obj_id;
SELECT deliverable_id obj_id, G_DELV obj_type, transaction_currency_code currency
FROM ams_deliverables_all_b
WHERE deliverable_id = p_deliverable_id;
SELECT act_list_header_id obj_id, G_ALIST obj_type, Ams_Actmetric_Pvt.DEFAULT_FUNC_CURRENCY currency
FROM ams_act_lists
WHERE act_list_header_id = p_act_list_header_id;
SELECT dialog_id, G_DILG, NULL
FROM AMS_DIALOGS_ALL_B
WHERE dialog_id = p_dialog_id
UNION ALL
SELECT flow_component_id obj_id,component_type_code obj_type, NULL
FROM ams_dlg_flow_comps_b
WHERE dialog_id= p_dialog_id;
SELECT campaign_id INTO l_RCAM_id FROM ams_campaigns_all_b
WHERE rollup_type = G_RCAM
AND parent_campaign_id IS NOT NULL
AND ROWNUM = 1;
l_object_list.delete;
SELECT campaign_id into l_camp_id FROM ams_campaigns_all_b
WHERE PARENT_CAMPAIGN_ID IS NOT NULL
AND ROWNUM = 1;
l_object_list.delete;
SELECT schedule_id INTO l_csch_id FROM ams_campaign_schedules_b
WHERE campaign_id IS NOT NULL
AND ROWNUM = 1;
l_object_list.delete;
SELECT event_header_id INTO l_eveh_id FROM ams_event_headers_all_b
WHERE PROGRAM_ID IS NOT NULL
AND ROWNUM = 1;
l_object_list.delete;
SELECT event_offer_id INTO l_eveo_id FROM ams_event_offers_all_b
WHERE event_header_id IS NOT NULL
AND ROWNUM = 1;
l_object_list.delete;
SELECT event_offer_id INTO l_eone_id FROM ams_event_offers_all_b
WHERE PARENT_ID IS NOT NULL
AND ROWNUM = 1;
l_object_list.delete;
PROCEDURE Update_History(p_commit IN VARCHAR2)
IS
L_API_NAME CONSTANT VARCHAR2(100) := 'UPDATE_HISTORY';
SELECT ACT_MET_HST_ID,
a.ACTIVITY_METRIC_ID,
a.LAST_UPDATE_DATE,
a.LAST_UPDATED_BY,
a.CREATION_DATE,
a.CREATED_BY,
a.LAST_UPDATE_LOGIN,
a.OBJECT_VERSION_NUMBER,
a.ACT_METRIC_USED_BY_ID,
a.ARC_ACT_METRIC_USED_BY,
a.APPLICATION_ID,
a.METRIC_ID,
a.TRANSACTION_CURRENCY_CODE,
a.TRANS_FORECASTED_VALUE,
a.TRANS_COMMITTED_VALUE,
a.TRANS_ACTUAL_VALUE,
a.FUNCTIONAL_CURRENCY_CODE,
a.FUNC_FORECASTED_VALUE,
a.FUNC_COMMITTED_VALUE,
a.DIRTY_FLAG,
a.FUNC_ACTUAL_VALUE,
a.LAST_CALCULATED_DATE,
a.VARIABLE_VALUE,
a.COMPUTED_USING_FUNCTION_VALUE,
a.METRIC_UOM_CODE,
a.ORG_ID,
a.DIFFERENCE_SINCE_LAST_CALC,
a.ACTIVITY_METRIC_ORIGIN_ID,
a.ARC_ACTIVITY_METRIC_ORIGIN,
a.DAYS_SINCE_LAST_REFRESH,
a.SUMMARIZE_TO_METRIC,
a.ROLLUP_TO_METRIC,
a.SCENARIO_ID,
a.ATTRIBUTE_CATEGORY,
a.ATTRIBUTE1,
a.ATTRIBUTE2,
a.ATTRIBUTE3,
a.ATTRIBUTE4,
a.ATTRIBUTE5,
a.ATTRIBUTE6,
a.ATTRIBUTE7,
a.ATTRIBUTE8,
a.ATTRIBUTE9,
a.ATTRIBUTE10,
a.ATTRIBUTE11,
a.ATTRIBUTE12,
a.ATTRIBUTE13,
a.ATTRIBUTE14,
a.ATTRIBUTE15,
a.DESCRIPTION,
a.ACT_METRIC_DATE,
a.ARC_FUNCTION_USED_BY,
a.FUNCTION_USED_BY_ID,
a.PURCHASE_REQ_RAISED_FLAG,
a.SENSITIVE_DATA_FLAG,
a.BUDGET_ID,
a.FORECASTED_VARIABLE_VALUE,
a.HIERARCHY_ID,
a.PUBLISHED_FLAG,
a.PRE_FUNCTION_NAME,
a.POST_FUNCTION_NAME,
a.START_NODE,
a.FROM_LEVEL,
a.TO_LEVEL,
a.FROM_DATE,
a.TO_DATE,
a.AMOUNT1,
a.AMOUNT2,
a.AMOUNT3,
a.PERCENT1,
a.PERCENT2,
a.PERCENT3,
a.STATUS_CODE,
a.ACTION_CODE,
a.METHOD_CODE,
a.BASIS_YEAR,
a.EX_START_NODE,
a.HIERARCHY_TYPE,
a.DEPEND_ACT_METRIC,
b.FUNC_FORECASTED_DELTA,
b.FUNC_ACTUAL_DELTA
FROM ams_act_metrics_all a, ams_act_metric_hst b
WHERE b.activity_metric_id = a.activity_metric_id
AND trunc(a.last_update_date) = trunc(b.last_update_date)
AND a.last_update_date > b.last_update_date
AND b.last_update_date =
(SELECT MAX(last_update_date)
FROM ams_act_metric_hst c
WHERE c.activity_metric_id = b.activity_metric_id)
;
l_LAST_UPDATE_DATE date_table_type;
l_LAST_UPDATED_BY num_table_type;
l_LAST_UPDATE_LOGIN num_table_type;
CURSOR c_get_deleted_today IS
SELECT act_met_hst_id
FROM ams_act_metric_hst a
WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
WHERE a.activity_metric_id = b.activity_metric_id)
AND last_update_date =
(SELECT MAX(c.last_update_date)
FROM ams_act_metric_hst c
WHERE c.activity_metric_id = a.activity_metric_id)
AND trunc(last_update_date) = trunc(l_today)
AND (NVL(func_actual_value,0) <> 0 OR NVL(func_forecasted_value,0) <> 0);
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)
SELECT AMS_ACT_METRIC_HST_S.NEXTVAL,
a.ACTIVITY_METRIC_ID,
a.LAST_UPDATE_DATE,
a.LAST_UPDATED_BY,
a.CREATION_DATE,
a.CREATED_BY,
a.LAST_UPDATE_LOGIN,
a.OBJECT_VERSION_NUMBER,
a.ACT_METRIC_USED_BY_ID,
a.ARC_ACT_METRIC_USED_BY,
a.APPLICATION_ID,
a.METRIC_ID,
a.TRANSACTION_CURRENCY_CODE,
a.TRANS_FORECASTED_VALUE,
a.TRANS_COMMITTED_VALUE,
a.TRANS_ACTUAL_VALUE,
a.FUNCTIONAL_CURRENCY_CODE,
a.FUNC_FORECASTED_VALUE,
a.FUNC_COMMITTED_VALUE,
a.DIRTY_FLAG,
a.FUNC_ACTUAL_VALUE,
a.LAST_CALCULATED_DATE,
a.VARIABLE_VALUE,
a.COMPUTED_USING_FUNCTION_VALUE,
a.METRIC_UOM_CODE,
a.ORG_ID,
a.DIFFERENCE_SINCE_LAST_CALC,
a.ACTIVITY_METRIC_ORIGIN_ID,
a.ARC_ACTIVITY_METRIC_ORIGIN,
a.DAYS_SINCE_LAST_REFRESH,
a.SUMMARIZE_TO_METRIC,
a.ROLLUP_TO_METRIC,
a.SCENARIO_ID,
a.ATTRIBUTE_CATEGORY,
a.ATTRIBUTE1,
a.ATTRIBUTE2,
a.ATTRIBUTE3,
a.ATTRIBUTE4,
a.ATTRIBUTE5,
a.ATTRIBUTE6,
a.ATTRIBUTE7,
a.ATTRIBUTE8,
a.ATTRIBUTE9,
a.ATTRIBUTE10,
a.ATTRIBUTE11,
a.ATTRIBUTE12,
a.ATTRIBUTE13,
a.ATTRIBUTE14,
a.ATTRIBUTE15,
a.DESCRIPTION,
a.ACT_METRIC_DATE,
a.ARC_FUNCTION_USED_BY,
a.FUNCTION_USED_BY_ID,
a.PURCHASE_REQ_RAISED_FLAG,
a.SENSITIVE_DATA_FLAG,
a.BUDGET_ID,
a.FORECASTED_VARIABLE_VALUE,
a.HIERARCHY_ID,
a.PUBLISHED_FLAG,
a.PRE_FUNCTION_NAME,
a.POST_FUNCTION_NAME,
a.START_NODE,
a.FROM_LEVEL,
a.TO_LEVEL,
a.FROM_DATE,
a.TO_DATE,
a.AMOUNT1,
a.AMOUNT2,
a.AMOUNT3,
a.PERCENT1,
a.PERCENT2,
a.PERCENT3,
a.STATUS_CODE,
a.ACTION_CODE,
a.METHOD_CODE,
a.BASIS_YEAR,
a.EX_START_NODE,
a.HIERARCHY_TYPE,
a.DEPEND_ACT_METRIC,
NVL(a.FUNC_FORECASTED_VALUE,0) - NVL(b.FUNC_FORECASTED_VALUE,0),
NVL(a.FUNC_ACTUAL_VALUE,0) - NVL(b.FUNC_ACTUAL_VALUE,0)
FROM ams_act_metrics_all a, ams_act_metric_hst b
WHERE a.activity_metric_id = b.activity_metric_id
AND b.last_update_date =
(SELECT MAX(last_update_date)
FROM ams_act_metric_hst c
WHERE c.activity_metric_id = a.activity_metric_id)
AND TRUNC(a.last_update_date) > TRUNC(b.last_update_date)
;
write_msg(L_API_NAME, 'INSERTED EXISTING METRICS: '||SQL%ROWCOUNT);
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)
SELECT AMS_ACT_METRIC_HST_S.NEXTVAL,
a.ACTIVITY_METRIC_ID,
a.LAST_UPDATE_DATE,
a.LAST_UPDATED_BY,
a.CREATION_DATE,
a.CREATED_BY,
a.LAST_UPDATE_LOGIN,
a.OBJECT_VERSION_NUMBER,
a.ACT_METRIC_USED_BY_ID,
a.ARC_ACT_METRIC_USED_BY,
a.APPLICATION_ID,
a.METRIC_ID,
a.TRANSACTION_CURRENCY_CODE,
a.TRANS_FORECASTED_VALUE,
a.TRANS_COMMITTED_VALUE,
a.TRANS_ACTUAL_VALUE,
a.FUNCTIONAL_CURRENCY_CODE,
a.FUNC_FORECASTED_VALUE,
a.FUNC_COMMITTED_VALUE,
a.DIRTY_FLAG,
a.FUNC_ACTUAL_VALUE,
a.LAST_CALCULATED_DATE,
a.VARIABLE_VALUE,
a.COMPUTED_USING_FUNCTION_VALUE,
a.METRIC_UOM_CODE,
a.ORG_ID,
a.DIFFERENCE_SINCE_LAST_CALC,
a.ACTIVITY_METRIC_ORIGIN_ID,
a.ARC_ACTIVITY_METRIC_ORIGIN,
a.DAYS_SINCE_LAST_REFRESH,
a.SUMMARIZE_TO_METRIC,
a.ROLLUP_TO_METRIC,
a.SCENARIO_ID,
a.ATTRIBUTE_CATEGORY,
a.ATTRIBUTE1,
a.ATTRIBUTE2,
a.ATTRIBUTE3,
a.ATTRIBUTE4,
a.ATTRIBUTE5,
a.ATTRIBUTE6,
a.ATTRIBUTE7,
a.ATTRIBUTE8,
a.ATTRIBUTE9,
a.ATTRIBUTE10,
a.ATTRIBUTE11,
a.ATTRIBUTE12,
a.ATTRIBUTE13,
a.ATTRIBUTE14,
a.ATTRIBUTE15,
a.DESCRIPTION,
a.ACT_METRIC_DATE,
a.ARC_FUNCTION_USED_BY,
a.FUNCTION_USED_BY_ID,
a.PURCHASE_REQ_RAISED_FLAG,
a.SENSITIVE_DATA_FLAG,
a.BUDGET_ID,
a.FORECASTED_VARIABLE_VALUE,
a.HIERARCHY_ID,
a.PUBLISHED_FLAG,
a.PRE_FUNCTION_NAME,
a.POST_FUNCTION_NAME,
a.START_NODE,
a.FROM_LEVEL,
a.TO_LEVEL,
a.FROM_DATE,
a.TO_DATE,
a.AMOUNT1,
a.AMOUNT2,
a.AMOUNT3,
a.PERCENT1,
a.PERCENT2,
a.PERCENT3,
a.STATUS_CODE,
a.ACTION_CODE,
a.METHOD_CODE,
a.BASIS_YEAR,
a.EX_START_NODE,
a.HIERARCHY_TYPE,
a.DEPEND_ACT_METRIC,
-- BUG2214496: Initialize to original value.
a.FUNC_FORECASTED_VALUE,
a.FUNC_ACTUAL_VALUE
FROM ams_act_metrics_all a
WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metric_hst b
WHERE a.activity_metric_id = b.activity_metric_id);
write_msg(L_API_NAME, 'INSERTED new metrics: '|| SQL%ROWCOUNT);
l_LAST_UPDATE_DATE,
l_LAST_UPDATED_BY,
l_CREATION_DATE,
l_CREATED_BY,
l_LAST_UPDATE_LOGIN,
l_OBJECT_VERSION_NUMBER,
l_ACT_METRIC_USED_BY_ID,
l_ARC_ACT_METRIC_USED_BY,
l_APPLICATION_ID,
l_METRIC_ID,
l_TRANSACTION_CURRENCY_CODE,
l_TRANS_FORECASTED_VALUE,
l_TRANS_COMMITTED_VALUE,
l_TRANS_ACTUAL_VALUE,
l_FUNCTIONAL_CURRENCY_CODE,
l_FUNC_FORECASTED_VALUE,
l_FUNC_COMMITTED_VALUE,
l_DIRTY_FLAG,
l_FUNC_ACTUAL_VALUE,
l_LAST_CALCULATED_DATE,
l_VARIABLE_VALUE,
l_COMPUTED_USING_FUNCTION_VALU,
l_METRIC_UOM_CODE,
l_ORG_ID,
l_DIFFERENCE_SINCE_LAST_CALC,
l_ACTIVITY_METRIC_ORIGIN_ID,
l_ARC_ACTIVITY_METRIC_ORIGIN,
l_DAYS_SINCE_LAST_REFRESH,
l_SUMMARIZE_TO_METRIC,
l_ROLLUP_TO_METRIC,
l_SCENARIO_ID,
l_ATTRIBUTE_CATEGORY,
l_ATTRIBUTE1,
l_ATTRIBUTE2,
l_ATTRIBUTE3,
l_ATTRIBUTE4,
l_ATTRIBUTE5,
l_ATTRIBUTE6,
l_ATTRIBUTE7,
l_ATTRIBUTE8,
l_ATTRIBUTE9,
l_ATTRIBUTE10,
l_ATTRIBUTE11,
l_ATTRIBUTE12,
l_ATTRIBUTE13,
l_ATTRIBUTE14,
l_ATTRIBUTE15,
l_DESCRIPTION,
l_ACT_METRIC_DATE,
l_ARC_FUNCTION_USED_BY,
l_FUNCTION_USED_BY_ID,
l_PURCHASE_REQ_RAISED_FLAG,
l_SENSITIVE_DATA_FLAG,
l_BUDGET_ID,
l_FORECASTED_VARIABLE_VALUE,
l_HIERARCHY_ID,
l_PUBLISHED_FLAG,
l_PRE_FUNCTION_NAME,
l_POST_FUNCTION_NAME,
l_START_NODE,
l_FROM_LEVEL,
l_TO_LEVEL,
l_FROM_DATE,
l_TO_DATE,
l_AMOUNT1,
l_AMOUNT2,
l_AMOUNT3,
l_PERCENT1,
l_PERCENT2,
l_PERCENT3,
l_STATUS_CODE,
l_ACTION_CODE,
l_METHOD_CODE,
l_BASIS_YEAR,
l_EX_START_NODE,
l_HIERARCHY_TYPE,
l_DEPEND_ACT_METRIC,
l_FUNC_FORECASTED_DELTA,
l_FUNC_ACTUAL_DELTA;
write_msg(L_API_NAME,'UPDATE repeat updates selected: '||l_act_met_hst_id.COUNT);
UPDATE ams_act_metric_hst
SET LAST_UPDATE_DATE = l_LAST_UPDATE_DATE(l_count),
LAST_UPDATED_BY = l_LAST_UPDATED_BY(l_count),
CREATION_DATE = l_CREATION_DATE(l_count),
CREATED_BY = l_CREATED_BY(l_count),
LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN(l_count),
OBJECT_VERSION_NUMBER = l_OBJECT_VERSION_NUMBER(l_count),
ACT_METRIC_USED_BY_ID = l_ACT_METRIC_USED_BY_ID(l_count),
ARC_ACT_METRIC_USED_BY = l_ARC_ACT_METRIC_USED_BY(l_count),
APPLICATION_ID = l_APPLICATION_ID(l_count),
METRIC_ID = l_METRIC_ID(l_count),
TRANSACTION_CURRENCY_CODE = l_TRANSACTION_CURRENCY_CODE(l_count),
TRANS_FORECASTED_VALUE = l_TRANS_FORECASTED_VALUE(l_count),
TRANS_COMMITTED_VALUE = l_TRANS_COMMITTED_VALUE(l_count),
TRANS_ACTUAL_VALUE = l_TRANS_ACTUAL_VALUE(l_count),
FUNCTIONAL_CURRENCY_CODE = l_FUNCTIONAL_CURRENCY_CODE(l_count),
FUNC_FORECASTED_VALUE = l_FUNC_FORECASTED_VALUE(l_count),
FUNC_COMMITTED_VALUE = l_FUNC_COMMITTED_VALUE(l_count),
DIRTY_FLAG = l_DIRTY_FLAG(l_count),
FUNC_ACTUAL_VALUE = l_FUNC_ACTUAL_VALUE(l_count),
LAST_CALCULATED_DATE = l_LAST_CALCULATED_DATE(l_count),
VARIABLE_VALUE = l_VARIABLE_VALUE(l_count),
COMPUTED_USING_FUNCTION_VALUE = l_COMPUTED_USING_FUNCTION_VALU(l_count),
METRIC_UOM_CODE = l_METRIC_UOM_CODE(l_count),
ORG_ID = l_ORG_ID(l_count),
DIFFERENCE_SINCE_LAST_CALC = l_DIFFERENCE_SINCE_LAST_CALC(l_count),
ACTIVITY_METRIC_ORIGIN_ID = l_ACTIVITY_METRIC_ORIGIN_ID(l_count),
ARC_ACTIVITY_METRIC_ORIGIN = l_ARC_ACTIVITY_METRIC_ORIGIN(l_count),
DAYS_SINCE_LAST_REFRESH = l_DAYS_SINCE_LAST_REFRESH(l_count),
SUMMARIZE_TO_METRIC = l_SUMMARIZE_TO_METRIC(l_count),
ROLLUP_TO_METRIC = l_ROLLUP_TO_METRIC(l_count),
SCENARIO_ID = l_SCENARIO_ID(l_count),
ATTRIBUTE_CATEGORY = l_ATTRIBUTE_CATEGORY(l_count),
ATTRIBUTE1 = l_ATTRIBUTE1(l_count),
ATTRIBUTE2 = l_ATTRIBUTE2(l_count),
ATTRIBUTE3 = l_ATTRIBUTE3(l_count),
ATTRIBUTE4 = l_ATTRIBUTE4(l_count),
ATTRIBUTE5 = l_ATTRIBUTE5(l_count),
ATTRIBUTE6 = l_ATTRIBUTE6(l_count),
ATTRIBUTE7 = l_ATTRIBUTE7(l_count),
ATTRIBUTE8 = l_ATTRIBUTE8(l_count),
ATTRIBUTE9 = l_ATTRIBUTE9(l_count),
ATTRIBUTE10 = l_ATTRIBUTE10(l_count),
ATTRIBUTE11 = l_ATTRIBUTE11(l_count),
ATTRIBUTE12 = l_ATTRIBUTE12(l_count),
ATTRIBUTE13 = l_ATTRIBUTE13(l_count),
ATTRIBUTE14 = l_ATTRIBUTE14(l_count),
ATTRIBUTE15 = l_ATTRIBUTE15(l_count),
DESCRIPTION = l_DESCRIPTION(l_count),
ACT_METRIC_DATE = l_ACT_METRIC_DATE(l_count),
ARC_FUNCTION_USED_BY = l_ARC_FUNCTION_USED_BY(l_count),
FUNCTION_USED_BY_ID = l_FUNCTION_USED_BY_ID(l_count),
PURCHASE_REQ_RAISED_FLAG = l_PURCHASE_REQ_RAISED_FLAG(l_count),
SENSITIVE_DATA_FLAG = l_SENSITIVE_DATA_FLAG(l_count),
BUDGET_ID = l_BUDGET_ID(l_count),
FORECASTED_VARIABLE_VALUE = l_FORECASTED_VARIABLE_VALUE(l_count),
HIERARCHY_ID = l_HIERARCHY_ID(l_count),
PUBLISHED_FLAG = l_PUBLISHED_FLAG(l_count),
PRE_FUNCTION_NAME = l_PRE_FUNCTION_NAME(l_count),
POST_FUNCTION_NAME = l_POST_FUNCTION_NAME(l_count),
START_NODE = l_START_NODE(l_count),
FROM_LEVEL = l_FROM_LEVEL(l_count),
TO_LEVEL = l_TO_LEVEL(l_count),
FROM_DATE = l_FROM_DATE(l_count),
TO_DATE = l_TO_DATE(l_count),
AMOUNT1 = l_AMOUNT1(l_count),
AMOUNT2 = l_AMOUNT2(l_count),
AMOUNT3 = l_AMOUNT3(l_count),
PERCENT1 = l_PERCENT1(l_count),
PERCENT2 = l_PERCENT2(l_count),
PERCENT3 = l_PERCENT3(l_count),
STATUS_CODE = l_STATUS_CODE(l_count),
ACTION_CODE = l_ACTION_CODE(l_count),
METHOD_CODE = l_METHOD_CODE(l_count),
BASIS_YEAR = l_BASIS_YEAR(l_count),
EX_START_NODE = l_EX_START_NODE(l_count),
HIERARCHY_TYPE = l_HIERARCHY_TYPE(l_count),
DEPEND_ACT_METRIC = l_DEPEND_ACT_METRIC(l_count),
-- BUG2214496: Wrap values with NVL.
FUNC_FORECASTED_DELTA = NVL(l_FUNC_FORECASTED_DELTA(l_count),0) +
NVL(l_FUNC_FORECASTED_VALUE(l_count),0) -
NVL(FUNC_FORECASTED_VALUE,0),
FUNC_ACTUAL_DELTA = NVL(l_FUNC_ACTUAL_DELTA(l_count),0) +
NVL(l_FUNC_ACTUAL_VALUE(l_count),0) -
NVL(FUNC_ACTUAL_VALUE,0)
WHERE act_met_hst_id = l_ACT_MET_HST_ID(l_count);
l_ACT_MET_HST_ID.delete;
l_ACTIVITY_METRIC_ID.delete;
l_LAST_UPDATE_DATE.delete;
l_LAST_UPDATED_BY.delete;
l_CREATION_DATE.delete;
l_CREATED_BY.delete;
l_LAST_UPDATE_LOGIN.delete;
l_OBJECT_VERSION_NUMBER.delete;
l_ACT_METRIC_USED_BY_ID.delete;
l_ARC_ACT_METRIC_USED_BY.delete;
l_APPLICATION_ID.delete;
l_METRIC_ID.delete;
l_TRANSACTION_CURRENCY_CODE.delete;
l_TRANS_FORECASTED_VALUE.delete;
l_TRANS_COMMITTED_VALUE.delete;
l_TRANS_ACTUAL_VALUE.delete;
l_FUNCTIONAL_CURRENCY_CODE.delete;
l_FUNC_FORECASTED_VALUE.delete;
l_FUNC_COMMITTED_VALUE.delete;
l_DIRTY_FLAG.delete;
l_FUNC_ACTUAL_VALUE.delete;
l_LAST_CALCULATED_DATE.delete;
l_VARIABLE_VALUE.delete;
l_COMPUTED_USING_FUNCTION_VALU.delete;
l_METRIC_UOM_CODE.delete;
l_ORG_ID.delete;
l_DIFFERENCE_SINCE_LAST_CALC.delete;
l_ACTIVITY_METRIC_ORIGIN_ID.delete;
l_ARC_ACTIVITY_METRIC_ORIGIN.delete;
l_DAYS_SINCE_LAST_REFRESH.delete;
l_SUMMARIZE_TO_METRIC.delete;
l_ROLLUP_TO_METRIC.delete;
l_SCENARIO_ID.delete;
l_ATTRIBUTE_CATEGORY.delete;
l_ATTRIBUTE1.delete;
l_ATTRIBUTE2.delete;
l_ATTRIBUTE3.delete;
l_ATTRIBUTE4.delete;
l_ATTRIBUTE5.delete;
l_ATTRIBUTE6.delete;
l_ATTRIBUTE7.delete;
l_ATTRIBUTE8.delete;
l_ATTRIBUTE9.delete;
l_ATTRIBUTE10.delete;
l_ATTRIBUTE11.delete;
l_ATTRIBUTE12.delete;
l_ATTRIBUTE13.delete;
l_ATTRIBUTE14.delete;
l_ATTRIBUTE15.delete;
l_DESCRIPTION.delete;
l_ACT_METRIC_DATE.delete;
l_ARC_FUNCTION_USED_BY.delete;
l_FUNCTION_USED_BY_ID.delete;
l_PURCHASE_REQ_RAISED_FLAG.delete;
l_SENSITIVE_DATA_FLAG.delete;
l_BUDGET_ID.delete;
l_FORECASTED_VARIABLE_VALUE.delete;
l_HIERARCHY_ID.delete;
l_PUBLISHED_FLAG.delete;
l_PRE_FUNCTION_NAME.delete;
l_POST_FUNCTION_NAME.delete;
l_START_NODE.delete;
l_FROM_LEVEL.delete;
l_TO_LEVEL.delete;
l_FROM_DATE.delete;
l_TO_DATE.delete;
l_AMOUNT1.delete;
l_AMOUNT2.delete;
l_AMOUNT3.delete;
l_PERCENT1.delete;
l_PERCENT2.delete;
l_PERCENT3.delete;
l_STATUS_CODE.delete;
l_ACTION_CODE.delete;
l_METHOD_CODE.delete;
l_BASIS_YEAR.delete;
l_EX_START_NODE.delete;
l_HIERARCHY_TYPE.delete;
l_DEPEND_ACT_METRIC.delete;
l_FUNC_FORECASTED_DELTA.DELETE;
l_FUNC_ACTUAL_DELTA.DELETE;
write_msg(L_API_NAME, 'UPDATED repeat updates: count='||SQL%ROWCOUNT);
OPEN c_get_deleted_today;
FETCH c_get_deleted_today
BULK COLLECT INTO l_ACT_MET_HST_ID;
CLOSE c_get_deleted_today;
write_msg(L_API_NAME, 'Deleted before today selected: '||l_ACT_MET_HST_ID.COUNT);
UPDATE ams_act_metric_hst
SET LAST_UPDATE_DATE = SYSDATE,
TRANS_FORECASTED_VALUE = 0,
TRANS_COMMITTED_VALUE = 0,
TRANS_ACTUAL_VALUE = 0,
FUNC_FORECASTED_VALUE = 0,
FUNC_COMMITTED_VALUE = 0,
FUNC_ACTUAL_VALUE = 0,
-- BUG2214496: Wrap values with NVL.
FUNC_FORECASTED_DELTA = NVL(FUNC_FORECASTED_DELTA,0) -
NVL(FUNC_FORECASTED_VALUE,0),
FUNC_ACTUAL_DELTA = NVL(FUNC_ACTUAL_DELTA,0) -
NVL(FUNC_ACTUAL_VALUE,0)
WHERE act_met_hst_id = l_ACT_MET_HST_ID(l_count);
l_ACT_MET_HST_ID.DELETE;
write_msg(L_API_NAME, 'Deleted today UPDATED: '||SQL%ROWCOUNT);
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)
SELECT AMS_ACT_METRIC_HST_S.NEXTVAL,
a.ACTIVITY_METRIC_ID,
l_today,
a.LAST_UPDATED_BY,
a.CREATION_DATE,
a.CREATED_BY,
a.LAST_UPDATE_LOGIN,
a.OBJECT_VERSION_NUMBER+1,
a.ACT_METRIC_USED_BY_ID,
a.ARC_ACT_METRIC_USED_BY,
a.APPLICATION_ID,
a.METRIC_ID,
a.TRANSACTION_CURRENCY_CODE,
0,
0,
0,
a.FUNCTIONAL_CURRENCY_CODE,
0,
0,
a.DIRTY_FLAG,
0,
a.LAST_CALCULATED_DATE,
0,
0,
a.METRIC_UOM_CODE,
a.ORG_ID,
a.DIFFERENCE_SINCE_LAST_CALC,
a.ACTIVITY_METRIC_ORIGIN_ID,
a.ARC_ACTIVITY_METRIC_ORIGIN,
a.DAYS_SINCE_LAST_REFRESH,
a.SUMMARIZE_TO_METRIC,
a.ROLLUP_TO_METRIC,
a.SCENARIO_ID,
a.ATTRIBUTE_CATEGORY,
a.ATTRIBUTE1,
a.ATTRIBUTE2,
a.ATTRIBUTE3,
a.ATTRIBUTE4,
a.ATTRIBUTE5,
a.ATTRIBUTE6,
a.ATTRIBUTE7,
a.ATTRIBUTE8,
a.ATTRIBUTE9,
a.ATTRIBUTE10,
a.ATTRIBUTE11,
a.ATTRIBUTE12,
a.ATTRIBUTE13,
a.ATTRIBUTE14,
a.ATTRIBUTE15,
a.DESCRIPTION,
a.ACT_METRIC_DATE,
a.ARC_FUNCTION_USED_BY,
a.FUNCTION_USED_BY_ID,
a.PURCHASE_REQ_RAISED_FLAG,
a.SENSITIVE_DATA_FLAG,
a.BUDGET_ID,
0,
a.HIERARCHY_ID,
a.PUBLISHED_FLAG,
a.PRE_FUNCTION_NAME,
a.POST_FUNCTION_NAME,
a.START_NODE,
a.FROM_LEVEL,
a.TO_LEVEL,
a.FROM_DATE,
a.TO_DATE,
a.AMOUNT1,
a.AMOUNT2,
a.AMOUNT3,
a.PERCENT1,
a.PERCENT2,
a.PERCENT3,
a.STATUS_CODE,
a.ACTION_CODE,
a.METHOD_CODE,
a.BASIS_YEAR,
a.EX_START_NODE,
a.HIERARCHY_TYPE,
a.DEPEND_ACT_METRIC,
-NVL(a.FUNC_FORECASTED_VALUE,0),
-NVL(a.FUNC_ACTUAL_VALUE,0)
FROM ams_act_metric_hst a
WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
WHERE a.activity_metric_id = b.activity_metric_id)
AND last_update_date =
(SELECT MAX(c.last_update_date)
FROM ams_act_metric_hst c
WHERE c.activity_metric_id = a.activity_metric_id)
AND last_update_date < TRUNC(l_today)
AND (NVL(func_actual_value,0) <> 0 OR NVL(func_forecasted_value,0) <> 0);
write_msg(L_API_NAME, 'Inserted delete before today: '||SQL%ROWCOUNT);
END Update_History;
g_stack.delete(l_index);
G_STACK.delete;
PROCEDURE Update_formulas(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.G_TRUE,
p_object_list IN object_currency_table := Empty_object_currency_table,
p_current_date IN date,
p_func_currency IN varchar2
)
IS
L_API_NAME CONSTANT VARCHAR2(100) := 'UPDATE_FORMULAS';
select a.activity_metric_id, a.metric_id ,
a.arc_act_metric_used_by, a.act_metric_used_by_id,
a.last_calculated_date, b.display_type
from ams_act_metrics_all a, ams_metrics_all_b b
where a.metric_id = b.metric_id
and b.metric_calculation_type = G_FORMULA
and a.dirty_flag = G_IS_DIRTY
order by a.metric_id;
select a.activity_metric_id, a.metric_id,
a.arc_act_metric_used_by, a.act_metric_used_by_id,
a.last_calculated_date, b.display_type
from ams_act_metrics_all a, ams_metrics_all_b b
where a.metric_id = b.metric_id
and b.metric_calculation_type = G_FORMULA
and a.dirty_flag = G_IS_DIRTY
and a.arc_act_metric_used_by = p_object_type
and a.act_metric_used_by_id = p_object_id
order by a.metric_id;
select source_type, source_id, source_sub_id, source_value, token,
use_sub_id_flag
from ams_metric_formulas f
where f.metric_id = p_metric_id
and f.notation_type = G_POSTFIX
order by sequence;
select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
from ams_act_metrics_all a
where a.metric_id = p_metric_id
and a.arc_act_metric_used_by = p_object_type
and a.act_metric_used_by_id = p_object_id
group by functional_currency_code;
select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
from ams_act_metrics_all a, ams_metrics_all_b b
where a.metric_id = b.metric_id
and b.metric_category = p_category_id
and a.arc_act_metric_used_by = p_object_type
and a.act_metric_used_by_id = p_object_id
group by functional_currency_code;
select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
from ams_act_metrics_all a, ams_metrics_all_b b
where a.metric_id = b.metric_id
and b.metric_category = p_category_id
and b.metric_sub_category is null
and a.arc_act_metric_used_by = p_object_type
and a.act_metric_used_by_id = p_object_id
group by functional_currency_code;
select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
from ams_act_metrics_all a, ams_metrics_all_b b
where a.metric_id = b.metric_id
and b.metric_category = p_category_id
and b.metric_sub_category = p_sub_category_id
and a.arc_act_metric_used_by = p_object_type
and a.act_metric_used_by_id = p_object_id
and b.metric_calculation_type in (G_MANUAL, G_FUNCTION, G_ROLLUP)
group by functional_currency_code;
l_temp_activity_metric_ids.delete;
l_temp_metric_ids.delete;
l_temp_arc_act_metric_used_bys.delete;
l_temp_act_metric_used_by_ids.delete;
l_temp_last_calculated_dates.delete;
l_temp_display_types.delete;
l_metric_formula_table.delete;
l_metric_formula_table.delete;
UPDATE ams_act_metrics_all
SET last_calculated_date = p_current_date,
last_update_date = p_current_date,
object_version_number = object_version_number + 1,
days_since_last_refresh = l_days_since_last_refreshs(l_index),
transaction_currency_code = l_transactional_currency_codes(l_index),
functional_currency_code = l_functional_currency_codes(l_index),
trans_forecasted_value = l_trans_forecasted_values(l_index),
func_forecasted_value = l_func_forecasted_values(l_index),
trans_actual_value = l_trans_actual_values(l_index),
func_actual_value = l_func_actual_values(l_index),
dirty_flag = G_NOT_DIRTY
WHERE activity_metric_id = l_activity_metric_ids(l_index);
l_activity_metric_ids.delete;
l_metric_ids.delete;
l_arc_act_metric_used_bys.delete;
l_act_metric_used_by_ids.delete;
l_last_calculated_dates.delete;
l_display_types.delete;
l_functional_currency_codes.delete;
l_func_forecasted_values.delete;
l_func_actual_values.delete;
l_transactional_currency_codes.delete;
l_trans_forecasted_values.delete;
l_trans_actual_values.delete;
l_days_since_last_refreshs.delete;
l_obj_currencies.delete;
end Update_formulas;
PROCEDURE Update_formulas (
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
)
is
L_API_NAME CONSTANT VARCHAR2(100) := 'UPDATE_FORMULAS';
savepoint sp_Update_formulas;
Update_formulas(x_errbuf => l_errbuf,
x_retcode => l_retcode,
p_commit => FND_API.G_FALSE,
p_object_list => l_object_list,
p_current_date => l_current_date,
p_func_currency => l_default_currency);
ROLLBACK TO sp_Update_formulas;
ROLLBACK TO sp_Update_formulas;
ROLLBACK TO sp_Update_formulas;
end Update_formulas;
delete
from ams_act_metrics_all_denorm;
write_msg('populate_all','inserting all entries with metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
insert into ams_act_metrics_all_denorm (creation_date,created_by,last_update_date,last_updated_by,last_update_login,act_metrics_denorm_id,metric_collection_date,object_type
,object_id,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate,dead_leads,dead_leads_ratio,opportunities
,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount,booked_revenue,invoiced_revenue
,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,invoiced_roi
,responses,contact_group_size,target_group_size
,leads_forecast,top_leads_forecast,top_leads_ratio_forecast,leads_accepted_forecast,leads_accepted_ratio_forecast
,dead_leads_forecast,dead_leads_ratio_forecast,opportunities_forecast,lead_opportunity_rate_forecast
,quotes_forecast,quotes_amount_forecast,orders_forecast,orders_amount_forecast
,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
invoiced_roi_forecast,contact_group_size_forecast,target_group_size_forecast,responses_forecast
)
select sysdate,Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,Fnd_Global.Conc_Login_Id,AMS_ACT_METRICS_ALL_DENORM_S.NEXTVAL,
sysdate,object_type,object_id
,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate
,dead_leads,dead_leads_ratio,opportunities,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount
,booked_revenue,invoiced_revenue,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,
invoiced_roi,responses,contact_group_size,target_group_size
,leads_forecast,top_leads_forecast,top_leads_ratio_forecast,leads_accepted_forecast,leads_accepted_ratio_forecast
,dead_leads_forecast,dead_leads_ratio_forecast,opportunities_forecast,lead_opportunity_rate_forecast
,quotes_forecast,quotes_amount_forecast,orders_forecast,orders_amount_forecast
,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
invoiced_roi_forecast,responses_forecast,contact_group_size_forecast,target_group_size_forecast
from (
select h1.arc_act_metric_used_by object_type,h1.act_metric_used_by_id object_id,
sum(decode(met.denorm_code,'METRIC_01',h1.func_actual_value,0)) leads,
sum(decode(met.denorm_code,'METRIC_02',h1.func_actual_value,0)) top_leads,
sum(decode(met.denorm_code,'METRIC_03',h1.func_actual_value,0)) top_leads_ratio,
sum(decode(met.denorm_code,'METRIC_04',h1.func_actual_value,0)) leads_accepted,
sum(decode(met.denorm_code,'METRIC_05',h1.func_actual_value,0)) lead_acceptance_rate,
sum(decode(met.denorm_code,'METRIC_06',h1.func_actual_value,0)) dead_leads,
sum(decode(met.denorm_code,'METRIC_07',h1.func_actual_value,0)) dead_leads_ratio,
sum(decode(met.denorm_code,'METRIC_08',h1.func_actual_value,0)) opportunities,
sum(decode(met.denorm_code,'METRIC_09',h1.func_actual_value,0)) leads_to_opportunity_rate,
sum(decode(met.denorm_code,'METRIC_10',h1.func_actual_value,0)) quotes,
sum(decode(met.denorm_code,'METRIC_11',h1.func_actual_value,0)) quotes_amount,
sum(decode(met.denorm_code,'METRIC_12',h1.func_actual_value,0)) orders,
sum(decode(met.denorm_code,'METRIC_13',h1.func_actual_value,0)) orders_amount,
sum(decode(met.denorm_code,'METRIC_14',h1.func_actual_value,0)) booked_revenue,
sum(decode(met.denorm_code,'METRIC_15',h1.func_actual_value,0)) invoiced_revenue,
sum(decode(met.denorm_code,'METRIC_16',h1.func_actual_value,0)) booked_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_17',h1.func_actual_value,0)) invoiced_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_18',h1.func_actual_value,0)) cost,
sum(decode(met.denorm_code,'METRIC_19',h1.func_actual_value,0)) cost_per_lead,
sum(decode(met.denorm_code,'METRIC_20',h1.func_actual_value,0)) booked_roi,
sum(decode(met.denorm_code,'METRIC_21',h1.func_actual_value,0)) invoiced_roi,
sum(decode(met.denorm_code,'METRIC_22',h1.func_actual_value,0)) responses,
sum(decode(met.denorm_code,'METRIC_23',h1.func_actual_value,0)) contact_group_size,
sum(decode(met.denorm_code,'METRIC_24',h1.func_actual_value,0)) target_group_size,
sum(decode(met.denorm_code,'METRIC_01',h1.func_forecasted_value,0)) leads_forecast,
sum(decode(met.denorm_code,'METRIC_02',h1.func_forecasted_value,0)) top_leads_forecast,
sum(decode(met.denorm_code,'METRIC_03',h1.func_forecasted_value,0)) top_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_04',h1.func_forecasted_value,0)) leads_accepted_forecast,
sum(decode(met.denorm_code,'METRIC_05',h1.func_forecasted_value,0)) leads_accepted_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_06',h1.func_forecasted_value,0)) dead_leads_forecast,
sum(decode(met.denorm_code,'METRIC_07',h1.func_forecasted_value,0)) dead_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_08',h1.func_forecasted_value,0)) opportunities_forecast,
sum(decode(met.denorm_code,'METRIC_09',h1.func_forecasted_value,0)) lead_opportunity_rate_forecast,
sum(decode(met.denorm_code,'METRIC_10',h1.func_forecasted_value,0)) quotes_forecast,
sum(decode(met.denorm_code,'METRIC_11',h1.func_forecasted_value,0)) quotes_amount_forecast,
sum(decode(met.denorm_code,'METRIC_12',h1.func_forecasted_value,0)) orders_forecast,
sum(decode(met.denorm_code,'METRIC_13',h1.func_forecasted_value,0)) orders_amount_forecast,
sum(decode(met.denorm_code,'METRIC_14',h1.func_forecasted_value,0)) booked_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_15',h1.func_forecasted_value,0)) invoiced_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_16',h1.func_forecasted_value,0)) booked_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_17',h1.func_forecasted_value,0)) invoiced_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_18',h1.func_forecasted_value,0)) cost_forecast,
sum(decode(met.denorm_code,'METRIC_19',h1.func_forecasted_value,0)) cost_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_20',h1.func_forecasted_value,0)) booked_roi_forecast,
sum(decode(met.denorm_code,'METRIC_21',h1.func_forecasted_value,0)) invoiced_roi_forecast,
sum(decode(met.denorm_code,'METRIC_22',h1.func_forecasted_value,0)) responses_forecast,
sum(decode(met.denorm_code,'METRIC_23',h1.func_forecasted_value,0)) contact_group_size_forecast,
sum(decode(met.denorm_code,'METRIC_24',h1.func_forecasted_value,0)) target_group_size_forecast
from ams_act_metrics_all h1, ams_metrics_all_b met
where h1.metric_id = met.metric_id
and met.denorm_code in
(
'METRIC_01'
,'METRIC_02'
,'METRIC_03'
,'METRIC_04'
,'METRIC_05'
,'METRIC_06'
,'METRIC_07'
,'METRIC_08'
,'METRIC_09'
,'METRIC_10'
,'METRIC_11'
,'METRIC_12'
,'METRIC_13'
,'METRIC_14'
,'METRIC_15'
,'METRIC_16'
,'METRIC_17'
,'METRIC_18'
,'METRIC_19'
,'METRIC_20'
,'METRIC_21'
,'METRIC_22'
,'METRIC_23'
,'METRIC_24')
group by h1.arc_act_metric_used_by ,h1.act_metric_used_by_id
);
write_msg('populate_all','Done Inserting entries ams_act_metrics_all_denorm');
select distinct concat(concat(arc_act_metric_used_by,'_'),TO_CHAR(act_metric_used_by_id))
from ams_act_metrics_all
where last_update_date > p_date;
update ams_act_metrics_all_denorm a
set
(
a.last_updated_by
,a.last_update_date
,a.last_update_login
,a.metric_collection_date
,a.object_version_number
,a.leads,top_leads,a.top_leads_ratio,a.leads_accepted,a.lead_acceptance_rate
,a.dead_leads,a.dead_leads_ratio,opportunities,a.leads_to_opportunity_rate,a.quotes,a.quotes_amount,a.orders,a.orders_amount
,a.booked_revenue,invoiced_revenue,a.booked_revenue_per_lead,a.invoiced_revenue_per_lead,a.cost,a.cost_per_lead,a.booked_roi
,a.invoiced_roi,a.responses,a.contact_group_size,a.target_group_size
,a.leads_forecast,a.top_leads_forecast,a.top_leads_ratio_forecast,a.leads_accepted_forecast,a.leads_accepted_ratio_forecast
,a.dead_leads_forecast,a.dead_leads_ratio_forecast,a.opportunities_forecast,a.lead_opportunity_rate_forecast
,a.quotes_forecast,a.quotes_amount_forecast,a.orders_forecast,a.orders_amount_forecast
,a.booked_revenue_forecast,a.invoiced_revenue_forecast,a.booked_rev_per_lead_forecast,a.invoiced_rev_per_lead_forecast
,a.cost_forecast,a.cost_per_lead_forecast,a.booked_roi_forecast
,a.invoiced_roi_forecast,a.responses_forecast,a.contact_group_size_forecast,a.target_group_size_forecast
) =
(select Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,sysdate,
nvl(a.object_version_number,1) + 1,
sum(decode(met.denorm_code,'METRIC_01',h1.func_actual_value,0)) leads,
sum(decode(met.denorm_code,'METRIC_02',h1.func_actual_value,0)) top_leads,
sum(decode(met.denorm_code,'METRIC_03',h1.func_actual_value,0)) top_leads_ratio,
sum(decode(met.denorm_code,'METRIC_04',h1.func_actual_value,0)) leads_accepted,
sum(decode(met.denorm_code,'METRIC_05',h1.func_actual_value,0)) lead_acceptance_rate,
sum(decode(met.denorm_code,'METRIC_06',h1.func_actual_value,0)) dead_leads,
sum(decode(met.denorm_code,'METRIC_07',h1.func_actual_value,0)) dead_leads_ratio,
sum(decode(met.denorm_code,'METRIC_08',h1.func_actual_value,0)) opportunities,
sum(decode(met.denorm_code,'METRIC_09',h1.func_actual_value,0)) leads_to_opportunity_rate,
sum(decode(met.denorm_code,'METRIC_10',h1.func_actual_value,0)) quotes,
sum(decode(met.denorm_code,'METRIC_11',h1.func_actual_value,0)) quotes_amount,
sum(decode(met.denorm_code,'METRIC_12',h1.func_actual_value,0)) orders,
sum(decode(met.denorm_code,'METRIC_13',h1.func_actual_value,0)) orders_amount,
sum(decode(met.denorm_code,'METRIC_14',h1.func_actual_value,0)) booked_revenue,
sum(decode(met.denorm_code,'METRIC_15',h1.func_actual_value,0)) invoiced_revenue,
sum(decode(met.denorm_code,'METRIC_16',h1.func_actual_value,0)) booked_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_17',h1.func_actual_value,0)) invoiced_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_18',h1.func_actual_value,0)) cost,
sum(decode(met.denorm_code,'METRIC_19',h1.func_actual_value,0)) cost_per_lead,
sum(decode(met.denorm_code,'METRIC_20',h1.func_actual_value,0)) booked_roi,
sum(decode(met.denorm_code,'METRIC_21',h1.func_actual_value,0)) invoiced_roi,
sum(decode(met.denorm_code,'METRIC_22',h1.func_actual_value,0)) responses,
sum(decode(met.denorm_code,'METRIC_23',h1.func_actual_value,0)) contact_group_size,
sum(decode(met.denorm_code,'METRIC_24',h1.func_actual_value,0)) target_group_size,
sum(decode(met.denorm_code,'METRIC_01',h1.func_forecasted_value,0)) leads_forecast,
sum(decode(met.denorm_code,'METRIC_02',h1.func_forecasted_value,0)) top_leads_forecast,
sum(decode(met.denorm_code,'METRIC_03',h1.func_forecasted_value,0)) top_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_04',h1.func_forecasted_value,0)) leads_accepted_forecast,
sum(decode(met.denorm_code,'METRIC_05',h1.func_forecasted_value,0)) leads_accepted_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_06',h1.func_forecasted_value,0)) dead_leads_forecast,
sum(decode(met.denorm_code,'METRIC_07',h1.func_forecasted_value,0)) dead_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_08',h1.func_forecasted_value,0)) opportunities_forecast,
sum(decode(met.denorm_code,'METRIC_09',h1.func_forecasted_value,0)) lead_opportunity_rate_forecast,
sum(decode(met.denorm_code,'METRIC_10',h1.func_forecasted_value,0)) quotes_forecast,
sum(decode(met.denorm_code,'METRIC_11',h1.func_forecasted_value,0)) quotes_amount_forecast,
sum(decode(met.denorm_code,'METRIC_12',h1.func_forecasted_value,0)) orders_forecast,
sum(decode(met.denorm_code,'METRIC_13',h1.func_forecasted_value,0)) orders_amount_forecast,
sum(decode(met.denorm_code,'METRIC_14',h1.func_forecasted_value,0)) booked_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_15',h1.func_forecasted_value,0)) invoiced_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_16',h1.func_forecasted_value,0)) booked_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_17',h1.func_forecasted_value,0)) invoiced_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_18',h1.func_forecasted_value,0)) cost_forecast,
sum(decode(met.denorm_code,'METRIC_19',h1.func_forecasted_value,0)) cost_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_20',h1.func_forecasted_value,0)) booked_roi_forecast,
sum(decode(met.denorm_code,'METRIC_21',h1.func_forecasted_value,0)) invoiced_roi_forecast,
sum(decode(met.denorm_code,'METRIC_22',h1.func_forecasted_value,0)) responses_forecast,
sum(decode(met.denorm_code,'METRIC_23',h1.func_forecasted_value,0)) contact_group_size_forecast,
sum(decode(met.denorm_code,'METRIC_24',h1.func_forecasted_value,0)) target_group_size_forecast
from ams_act_metrics_all h1, ams_metrics_all_b met
where h1.metric_id = met.metric_id
and met.denorm_code in
(
'METRIC_01'
,'METRIC_02'
,'METRIC_03'
,'METRIC_04'
,'METRIC_05'
,'METRIC_06'
,'METRIC_07'
,'METRIC_08'
,'METRIC_09'
,'METRIC_10'
,'METRIC_11'
,'METRIC_12'
,'METRIC_13'
,'METRIC_14'
,'METRIC_15'
,'METRIC_16'
,'METRIC_17'
,'METRIC_18'
,'METRIC_19'
,'METRIC_20'
,'METRIC_21'
,'METRIC_22'
,'METRIC_23'
,'METRIC_24')
and h1.arc_act_metric_used_by(+) = a.object_type
and h1.act_metric_used_by_id(+) = a.object_id
)
where a.last_update_date > l_calc_since
and exists (select 1 from ams_act_metrics_all amet, ams_metrics_all_b met
where amet.last_update_date > a.last_update_date
and amet.metric_id = met.metric_id
and met.denorm_code is not null
and amet.arc_act_metric_used_by = a.object_type
and amet.act_metric_used_by_id = a.object_id
and rownum = 1
)
;
insert into ams_act_metrics_all_denorm (creation_date,created_by,last_update_date,last_updated_by,last_update_login
,act_metrics_denorm_id,metric_collection_date,object_type
,object_id,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate,dead_leads,dead_leads_ratio,opportunities
,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount,booked_revenue,invoiced_revenue
,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,invoiced_roi
,responses,contact_group_size,target_group_size
,leads_forecast,top_leads_forecast,top_leads_ratio_forecast,leads_accepted_forecast,leads_accepted_ratio_forecast
,dead_leads_forecast,dead_leads_ratio_forecast,opportunities_forecast,lead_opportunity_rate_forecast
,quotes_forecast,quotes_amount_forecast,orders_forecast,orders_amount_forecast
,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
invoiced_roi_forecast,contact_group_size_forecast,target_group_size_forecast,responses_forecast
)
select sysdate,Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,Fnd_Global.Conc_Login_Id,AMS_ACT_METRICS_ALL_DENORM_S.NEXTVAL,
sysdate,object_type,object_id
,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate
,dead_leads,dead_leads_ratio,opportunities,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount
,booked_revenue,invoiced_revenue,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,
invoiced_roi,responses,contact_group_size,target_group_size
,leads_forecast,top_leads_forecast,top_leads_ratio_forecast,leads_accepted_forecast,leads_accepted_ratio_forecast
,dead_leads_forecast,dead_leads_ratio_forecast,opportunities_forecast,lead_opportunity_rate_forecast
,quotes_forecast,quotes_amount_forecast,orders_forecast,orders_amount_forecast
,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
invoiced_roi_forecast,responses_forecast,contact_group_size_forecast,target_group_size_forecast
from (
select h1.arc_act_metric_used_by object_type,h1.act_metric_used_by_id object_id,
sum(decode(met.denorm_code,'METRIC_01',h1.func_actual_value,0)) leads,
sum(decode(met.denorm_code,'METRIC_02',h1.func_actual_value,0)) top_leads,
sum(decode(met.denorm_code,'METRIC_03',h1.func_actual_value,0)) top_leads_ratio,
sum(decode(met.denorm_code,'METRIC_04',h1.func_actual_value,0)) leads_accepted,
sum(decode(met.denorm_code,'METRIC_05',h1.func_actual_value,0)) lead_acceptance_rate,
sum(decode(met.denorm_code,'METRIC_06',h1.func_actual_value,0)) dead_leads,
sum(decode(met.denorm_code,'METRIC_07',h1.func_actual_value,0)) dead_leads_ratio,
sum(decode(met.denorm_code,'METRIC_08',h1.func_actual_value,0)) opportunities,
sum(decode(met.denorm_code,'METRIC_09',h1.func_actual_value,0)) leads_to_opportunity_rate,
sum(decode(met.denorm_code,'METRIC_10',h1.func_actual_value,0)) quotes,
sum(decode(met.denorm_code,'METRIC_11',h1.func_actual_value,0)) quotes_amount,
sum(decode(met.denorm_code,'METRIC_12',h1.func_actual_value,0)) orders,
sum(decode(met.denorm_code,'METRIC_13',h1.func_actual_value,0)) orders_amount,
sum(decode(met.denorm_code,'METRIC_14',h1.func_actual_value,0)) booked_revenue,
sum(decode(met.denorm_code,'METRIC_15',h1.func_actual_value,0)) invoiced_revenue,
sum(decode(met.denorm_code,'METRIC_16',h1.func_actual_value,0)) booked_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_17',h1.func_actual_value,0)) invoiced_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_18',h1.func_actual_value,0)) cost,
sum(decode(met.denorm_code,'METRIC_19',h1.func_actual_value,0)) cost_per_lead,
sum(decode(met.denorm_code,'METRIC_20',h1.func_actual_value,0)) booked_roi,
sum(decode(met.denorm_code,'METRIC_21',h1.func_actual_value,0)) invoiced_roi,
sum(decode(met.denorm_code,'METRIC_22',h1.func_actual_value,0)) responses,
sum(decode(met.denorm_code,'METRIC_23',h1.func_actual_value,0)) contact_group_size,
sum(decode(met.denorm_code,'METRIC_24',h1.func_actual_value,0)) target_group_size,
sum(decode(met.denorm_code,'METRIC_01',h1.func_forecasted_value,0)) leads_forecast,
sum(decode(met.denorm_code,'METRIC_02',h1.func_forecasted_value,0)) top_leads_forecast,
sum(decode(met.denorm_code,'METRIC_03',h1.func_forecasted_value,0)) top_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_04',h1.func_forecasted_value,0)) leads_accepted_forecast,
sum(decode(met.denorm_code,'METRIC_05',h1.func_forecasted_value,0)) leads_accepted_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_06',h1.func_forecasted_value,0)) dead_leads_forecast,
sum(decode(met.denorm_code,'METRIC_07',h1.func_forecasted_value,0)) dead_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_08',h1.func_forecasted_value,0)) opportunities_forecast,
sum(decode(met.denorm_code,'METRIC_09',h1.func_forecasted_value,0)) lead_opportunity_rate_forecast,
sum(decode(met.denorm_code,'METRIC_10',h1.func_forecasted_value,0)) quotes_forecast,
sum(decode(met.denorm_code,'METRIC_11',h1.func_forecasted_value,0)) quotes_amount_forecast,
sum(decode(met.denorm_code,'METRIC_12',h1.func_forecasted_value,0)) orders_forecast,
sum(decode(met.denorm_code,'METRIC_13',h1.func_forecasted_value,0)) orders_amount_forecast,
sum(decode(met.denorm_code,'METRIC_14',h1.func_forecasted_value,0)) booked_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_15',h1.func_forecasted_value,0)) invoiced_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_16',h1.func_forecasted_value,0)) booked_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_17',h1.func_forecasted_value,0)) invoiced_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_18',h1.func_forecasted_value,0)) cost_forecast,
sum(decode(met.denorm_code,'METRIC_19',h1.func_forecasted_value,0)) cost_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_20',h1.func_forecasted_value,0)) booked_roi_forecast,
sum(decode(met.denorm_code,'METRIC_21',h1.func_forecasted_value,0)) invoiced_roi_forecast,
sum(decode(met.denorm_code,'METRIC_22',h1.func_forecasted_value,0)) responses_forecast,
sum(decode(met.denorm_code,'METRIC_23',h1.func_forecasted_value,0)) contact_group_size_forecast,
sum(decode(met.denorm_code,'METRIC_24',h1.func_forecasted_value,0)) target_group_size_forecast
from ams_act_metrics_all h1, ams_metrics_all_b met
where h1.metric_id = met.metric_id
and met.denorm_code in
(
'METRIC_01'
,'METRIC_02'
,'METRIC_03'
,'METRIC_04'
,'METRIC_05'
,'METRIC_06'
,'METRIC_07'
,'METRIC_08'
,'METRIC_09'
,'METRIC_10'
,'METRIC_11'
,'METRIC_12'
,'METRIC_13'
,'METRIC_14'
,'METRIC_15'
,'METRIC_16'
,'METRIC_17'
,'METRIC_18'
,'METRIC_19'
,'METRIC_20'
,'METRIC_21'
,'METRIC_22'
,'METRIC_23'
,'METRIC_24')
and (h1.arc_act_metric_used_by,h1.act_metric_used_by_id) not in
(
select distinct object_type, object_id
from ams_act_metrics_all_denorm
)
group by h1.arc_act_metric_used_by ,h1.act_metric_used_by_id
);
select max(metric_collection_date)
from ams_act_metrics_all_denorm;