[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or '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;
SELECT event_header_id
FROM ams_event_offers_all_b
WHERE event_offer_id = l_act_metric_used_by_id;
SELECT event_header_id
FROM ams_event_offers_all_b
WHERE event_offer_id = l_act_metric_used_by_id;
SELECT parent_event_header_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 = '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;
UPDATE ams_act_metrics_all
SET rollup_to_metric = l_act_met_id
WHERE activity_metric_id = p_activity_metric_id;
SELECT using_object, used_object, metric_id, metric_type_id
FROM ams_metric_accruals
WHERE ams_metric_acc_id = l_accr_met_acc_id;
SELECT actmet.activity_metric_id,
actmet.metric_id,
actmet.func_actual_value,
actmet.func_forecasted_value,
actmet.functional_currency_code,
objassoc.object_association_id
FROM ams_act_metrics_all actmet,
ams_metrics_all_b met,
ams_object_associations objassoc
WHERE actmet.metric_id = met.metric_id
AND objassoc.master_object_type = l_master_obj_type
AND objassoc.master_object_id = l_master_obj_id
AND actmet.act_metric_used_by_id = objassoc.using_object_id
AND actmet.arc_act_metric_used_by = objassoc.using_object_type
AND met.metric_category = l_metric_type_id
AND met.summary_metric_id IS NULL;
SELECT activity_metric_id
FROM ams_act_metrics_all
WHERE arc_act_metric_used_by = l_master_object_type
AND act_metric_used_by_id = l_master_object_id
AND metric_id = l_metric_id;
Ams_Actmetric_Pvt.update_actmetric (
p_api_version => 1.0,
p_commit => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.g_valid_level_full,
p_act_metric_rec => l_act_metric_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT object_association_id,
using_object_type child_object_type,
using_object_id child_object_id
FROM ams_object_associations
WHERE master_object_type = l_master_object_type
AND master_object_id = l_master_object_id;
SELECT actmet.activity_metric_id child_actmet_id,
met.metric_category child_metric_type_id ,
actmet.metric_id child_metric_id,
met.value_type child_met_value_type
FROM ams_act_metrics_all actmet, ams_metrics_all_b met
WHERE actmet.arc_act_metric_used_by = l_child_object_type
AND actmet.act_metric_used_by_id = l_child_object_id
AND met.summary_metric_id IS NULL
AND actmet.metric_id = met.metric_id;
SELECT activity_metric_id parent_summary_actmet_id,
met.metric_id parent_summary_met_id
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_master_object_type
AND act_metric_used_by_id = l_master_object_id
AND metric_category = l_metric_type_id
AND metric_calculation_type = 'SUMMARY'
AND summarize_to_metric IS NULL;
SELECT accr.ams_metric_acc_id
FROM ams_metric_accruals accr, ams_metrics_all_b met
WHERE accr.using_object = l_master_object_type
AND accr.used_object = l_child_object_type
AND accr.metric_type_id = l_metric_type_id
AND accr.metric_id = met.metric_id;
SELECT category_name
FROM ams_categories_vl
WHERE category_id = l_category_id;
SELECT ams_metric_accruals_s.NEXTVAL
FROM dual;
INSERT INTO ams_metric_accruals(
AMS_METRIC_ACC_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
USING_OBJECT,
USED_OBJECT,
METRIC_ID,
METRIC_TYPE_ID)
VALUES(
l_accrual_metric_id,
SYSDATE,
Fnd_Global.User_ID,
SYSDATE,
Fnd_Global.User_ID,
Fnd_Global.Conc_Login_ID,
1,
p_master_object_type,
l_child_obj_rec.child_object_type,
l_metric_id,
l_child_summary_actmet_rec.child_metric_type_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_vl met
WHERE met.metric_id = act.metric_id
AND act.arc_act_metric_used_by = p_arc_act_metric_used_by
AND act.act_metric_used_by_id = p_act_metric_used_by_id
AND met.metric_category = p_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 = p_arc_act_metric_used_by
AND act_metric_used_by_id = p_act_metric_used_by_id ;
SELECT master_object_type,
master_object_id,
using_object_type,
using_object_id ,
TO_NUMBER(DECODE(usage_type,'CREATED',100,'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 trans_actual_value,
trans_forecasted_value,
transaction_currency_code,
NVL(func_actual_value,0) actual,
NVL(func_forecasted_value, 0) forecasted,
functional_currency_code
FROM ams_act_metrics_all
WHERE activity_metric_id = l_activity_metric_id;
SELECT /*+ first_rows * / activity_metric_id,
function_name,
func_actual_value,
last_calculated_date,
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 IN
('RCAM', 'CAMP', 'CSCH', 'DELV', 'EVEH', 'EVEO', 'EONE')
--BUG2845365: Remove dialogue components.
--'DILG', 'AMS_COMP_START', 'AMS_COMP_SHOW_WEB_PAGE', 'AMS_COMP_END')
AND metric_calculation_type = 'FUNCTION';
Ams_Actmetric_Pvt.Update_ActMetric (
p_api_version => 1.0,
p_init_msg_list => Fnd_Api.g_false,
p_commit => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.g_valid_level_full,
p_act_metric_rec => l_act_metric_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT Fnd_Api.G_TRUE
FROM ams_campaigns_all_b
WHERE campaign_id = id
AND status_code = 'CANCELLED';
SELECT Fnd_Api.G_TRUE
FROM ams_campaign_schedules_b
WHERE schedule_id = id
AND status_code = 'CANCELLED';
SELECT Fnd_Api.G_TRUE
FROM ams_deliverables_all_b
WHERE deliverable_id = id
AND status_code = 'CANCELLED';
SELECT Fnd_Api.G_TRUE
FROM ams_event_headers_all_b
WHERE event_header_id = id
AND system_status_code = 'CANCELLED';
SELECT Fnd_Api.G_TRUE
FROM ams_event_offers_all_b
WHERE event_offer_id = id
AND system_status_code IN ('CANCELLED');
SELECT activity_metric_id,
arc_act_metric_used_by,
act_metric_used_by_id,
--metric_uom_code,
NVL(func_actual_value, 0) func_actual_value,
NVL(func_forecasted_value, 0) func_forecasted_value,
functional_currency_code,
NVL(trans_actual_value, 0) trans_actual_value,
NVL(trans_forecasted_value, 0) trans_forecasted_value,
transaction_currency_code
FROM ams_act_metrics_all
WHERE rollup_to_metric = l_activity_metric_id
ORDER BY arc_act_metric_used_by, act_metric_used_by_id;
SELECT activity_metric_id,
arc_act_metric_used_by,
act_metric_used_by_id,
metric_uom_code
FROM ams_act_metrics_all
WHERE summarize_to_metric = l_activity_metric_id;
SELECT value_type, -- Ratio / Numeric
metric_calculation_type, -- Manual/Function/...
accrual_type, -- Fixed/Variable
function_name , -- will be used only if it is Function Metric
compute_using_function,--Will be used only if it is Variable Metric
default_uom_code, -- Will be Used for UOM Conversion
function_type
--FROM ams_metrics_vl
FROM ams_metrics_all_b
WHERE metric_id = l_metric_id;
SELECT activity_metric_origin_id,
NVL(last_calculated_date,SYSDATE) last_calculated_date,
NVL(func_actual_value,0) func_actual_value,
NVL(func_forecasted_value,0) func_forecasted_value,
metric_id, variable_value, depend_act_metric, dirty_flag,
act_metric_used_by_id, arc_act_metric_used_by,
metric_uom_code,
transaction_currency_code,
functional_currency_code,
NVL(trans_actual_value,0) trans_actual_value,
NVL(trans_forecasted_value,0) trans_forecasted_value
FROM ams_act_metrics_all
WHERE activity_metric_id = l_activity_metric_id
for update of trans_actual_value, trans_forecasted_value,
func_actual_value, func_forecasted_value,
functional_currency_code, computed_using_function_value,
difference_since_last_calc, days_since_last_refresh,
last_calculated_date, dirty_flag,
last_updated_by, last_update_login,
object_version_number
nowait ;
SELECT 1
FROM ams_metric_accruals
WHERE metric_id = l_metric_id;
SELECT func_actual_value, func_forecasted_value
FROM ams_act_metrics_all
WHERE activity_metric_id = l_act_metric_id;
Ams_Actmetric_Pvt.update_actmetric (
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.g_valid_level_full,
p_act_metric_rec => l_act_metrics_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT DISTINCT met.metric_id, met.metric_category, met.accrual_type
FROM ams_metrics_all_b met, ams_met_tpl_details det, ams_met_tpl_headers_b tpl
WHERE
met.arc_metric_used_for_object IN ( l_object_type, 'ANY')
AND met.enabled_flag = 'Y'
AND met.metric_id = det.metric_id
AND tpl.metric_tpl_header_id = det.metric_tpl_header_id
AND tpl.enabled_flag = 'Y'
AND det.enabled_flag = 'Y'
AND ((1 = p_track_flag) OR
(EXISTS (SELECT 1
FROM ams_categories_b cat
WHERE cat.category_id = met.metric_category
AND ((1 = p_cost_flag AND category_id IN (901, 902)) OR
(1 = p_amet_flag AND category_id NOT IN (901, 902))
AND enabled_flag = 'Y'))))
AND ((tpl.association_type = 'OBJECT_TYPE'
AND tpl.used_by_code = l_object_type)
OR (tpl.association_type = 'CUSTOM_SETUP'
AND tpl.used_by_id = l_setup_id))
order by met.accrual_type
;
SELECT metric_id, metric_category
FROM ams_metrics_all_b met
WHERE met.arc_metric_used_for_object = l_object_type
AND met.metric_calculation_type IN ('MANUAL', 'FUNCTION')
AND met.enabled_flag = 'Y'
AND metric_id IN
(SELECT metric_id
FROM ams_met_tpl_details dtl
WHERE enabled_flag = 'Y'
AND metric_tpl_header_id IN
(SELECT metric_tpl_header_id FROM ams_met_tpl_headers_vl
WHERE enabled_flag = 'Y'
AND metric_tpl_header_id IN
(SELECT metric_tpl_header_id FROM ams_met_tpl_assocs
WHERE enabled_flag = 'Y'
AND ASSOCIATION_TYPE = 'OBJECT_TYPE'
AND used_by_code = l_object_type
UNION ALL
SELECT metric_tpl_header_id FROM ams_met_tpl_assocs
WHERE enabled_flag = 'Y'
AND ASSOCIATION_TYPE = 'CUSTOM_SETUP'
AND used_by_id = l_setup_id
)
)
)
;
SELECT MAX (DECODE (object_attribute, 'COST', 1, 0)) cost_flag
, MAX (DECODE (object_attribute, 'AMET', 1, 0)) amet_flag
, MAX (DECODE (object_attribute, 'TRACK', 1, 0)) track_flag
FROM ams_custom_setup_attr
WHERE attr_available_flag = 'Y'
AND custom_setup_id = p_custom_setup_id
AND object_attribute IN ('COST', 'AMET', 'TRACK')
;
SELECT object_attribute
FROM ams_custom_setup_attr
WHERE attr_available_flag = 'Y'
AND custom_setup_id = p_custom_setup_id
AND object_attribute IN ('COST', 'AMET');
SELECT custom_setup_id
FROM ams_campaigns_all_b
WHERE campaign_id = p_campaign_id;
SELECT custom_setup_id
FROM AMS_CAMPAIGN_SCHEDULES_B
WHERE schedule_id = p_schedule_id;
SELECT setup_type_id
FROM AMS_EVENT_HEADERS_ALL_B
WHERE EVENT_HEADER_ID = p_event_id;
SELECT setup_type_id
FROM AMS_EVENT_OFFERS_ALL_B
WHERE EVENT_OFFER_ID = p_event_offer_id;
SELECT custom_setup_id
FROM AMS_DELIVERABLES_ALL_B
WHERE DELIVERABLE_ID = p_deliverable_id;
SELECT master_object_type,
master_object_id
FROM ams_object_associations
WHERE object_association_id = p_obj_association_id ;
SELECT COUNT(1)
FROM ams_act_metrics_all
WHERE activity_metric_origin_id = p_obj_association_id ;
Ams_Actmetric_Pvt.Update_ActMetric (
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.g_valid_level_full,
p_act_metric_rec => l_act_metrics_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
p_update_history IN VARCHAR2 := Fnd_Api.G_FALSE
)
IS
BEGIN
-- DMVINCEN: 04-APR-2001: New routine.
Ams_Actmetrics_Engine_Pvt.Refresh_Act_Metrics_Engine(
x_errbuf => errbuf,
x_retcode => retcode,
p_update_history => p_update_history);
SELECT metric_id,
arc_act_metric_used_by,
act_metric_used_by_id,
metric_uom_code
FROM ams_act_metrics_all
WHERE activity_metric_id = l_act_metric_id;
SELECT activity_metric_id,actmet.metric_id,actmet.dirty_flag,
met.metric_calculation_type
FROM ams_act_metrics_all actmet, ams_metrics_all_b met
WHERE actmet.metric_id = met.metric_id
AND actmet.arc_act_metric_used_by = l_arc_act_metric_used_by
AND actmet.act_metric_used_by_id = l_act_metric_used_by_id
AND actmet.summarize_to_metric IS NULL;
SELECT activity_metric_id,actmet.metric_id,actmet.dirty_flag,
met.metric_calculation_type,actmet.metric_uom_code
FROM ams_act_metrics_all actmet, ams_metrics_all_b met
WHERE actmet.metric_id = met.metric_id
AND actmet.arc_act_metric_used_by = l_arc_act_metric_used_by
AND actmet.act_metric_used_by_id = l_act_metric_used_by_id
AND actmet.summarize_to_metric IS NULL;
SELECT DISTINCT function_name
FROM ams_metrics_all_b
WHERE metric_id IN (SELECT metric_id
FROM ams_act_metrics_all
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 function_type = 'N';
SELECT flow_component_id obj_id,component_type_code obj_type
FROM ams_dlg_flow_comps_b
WHERE dialog_id= p_dialog_id;