[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO cn_event_log_all(
event_log_id
, event_name
, object_name
, object_id
, start_date
, start_date_old
, end_date
, end_date_old
, user_id
, event_log_date
, status
, creation_date
, created_by
, last_update_date
, last_update_login
, last_updated_by
, org_id
)
VALUES (
cn_event_log_s.NEXTVAL
, p_event_name
, p_object_name
, p_object_id
, p_start_date
, p_start_date_old
, p_end_date
, p_end_date_old
, fnd_global.user_id
, SYSDATE
, NULL
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.login_id
, fnd_global.user_id
, p_org_id
)
RETURNING event_log_id INTO x_event_log_id;
SELECT 1
FROM cn_notify_log_all
WHERE period_id = p_period_id
AND org_id = p_org_id
AND (salesrep_id = p_salesrep_id OR salesrep_id = -1000)
AND status = 'INCOMPLETE'
AND (revert_state IN('COL', 'CLS', 'ROLL') OR(revert_state = 'CALC' AND quota_id IS NULL));
SELECT 1
FROM cn_notify_log_all
WHERE period_id = p_period_id
AND org_id = p_org_id
AND (salesrep_id = p_salesrep_id OR salesrep_id = -1000)
AND status = 'INCOMPLETE'
AND (
(l_revert_state = 'POP' AND revert_state IN('CLS', 'ROLL'))
OR (l_revert_state = 'ROLL' AND revert_state IN('CLS'))
)
AND l_start_date BETWEEN start_date AND end_date
AND l_end_date BETWEEN start_date AND end_date;
SELECT 1
FROM cn_notify_log_all
WHERE salesrep_id = p_salesrep_id
AND org_id = p_org_id
AND period_id = p_period_id
AND revert_state IN('CALC', 'POP')
AND status = 'INCOMPLETE'
AND quota_id = p_quota_id;
SELECT notify_log_id
, start_date
, end_date
FROM cn_notify_log_all
WHERE salesrep_id = p_salesrep_id
AND org_id = p_org_id
AND period_id = p_period_id
AND revert_state = p_revert_to_state
AND status = 'INCOMPLETE'
AND quota_id = p_quota_id;
SELECT notify_log_id
, start_date
, end_date
FROM cn_notify_log_all
WHERE (salesrep_id = -1000 OR salesrep_id = p_salesrep_id)
AND org_id = p_org_id
AND period_id = p_period_id
AND revert_state = p_revert_to_state
AND status = 'INCOMPLETE';
SELECT notify_log_id
, start_date
, end_date
FROM cn_notify_log_all
WHERE salesrep_id = -1000
AND org_id = p_org_id
AND period_id = p_period_id
AND revert_state = p_revert_to_state
AND status = 'INCOMPLETE';
SELECT notify_log_id
, start_date
, end_date
FROM cn_notify_log_all
WHERE salesrep_id = -1000
AND org_id = p_org_id
AND period_id = p_period_id
AND revert_state = p_revert_to_state
AND status = 'INCOMPLETE';
SELECT notify_log_id
, start_date
, end_date
FROM cn_notify_log_all
WHERE salesrep_id = -1000
AND org_id = p_org_id
AND period_id = p_period_id
AND revert_state = p_revert_to_state
AND status = 'INCOMPLETE';
SELECT start_date
, end_date
FROM cn_period_statuses_all
WHERE period_id = p_period_id AND org_id = p_org_id;
l_insert_flag BOOLEAN;
l_update_flag BOOLEAN;
l_insert_flag := FALSE;
l_update_flag := FALSE;
l_insert_flag := TRUE;
l_insert_flag := TRUE;
l_insert_flag := TRUE;
l_update_flag := TRUE;
l_insert_flag := TRUE;
l_update_flag := TRUE;
l_insert_flag := TRUE;
l_update_flag := TRUE;
l_insert_flag := TRUE;
l_update_flag := TRUE;
l_insert_flag := TRUE;
l_update_flag := TRUE;
l_insert_flag := TRUE;
IF l_insert_flag THEN
INSERT INTO cn_notify_log_all
(
notify_log_id
, salesrep_id
, period_id
, start_date
, end_date
, quota_id
, revert_state
, event_log_id
, notify_log_date
, status
, revert_sequence
, creation_date
, created_by
, last_update_date
, last_update_login
, last_updated_by
, org_id
)
VALUES (
cn_notify_log_s.NEXTVAL
, p_salesrep_id
, p_period_id
, NVL(p_start_date, l_start_date)
, NVL(p_end_date, l_end_date)
, p_quota_id
, p_revert_to_state
, p_event_log_id
, SYSDATE
, 'INCOMPLETE'
, DECODE(p_revert_to_state, 'COL', 4, 'CLS', 6, 'ROLL', 8, 'POP', 10, 'CALC', 12)
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.login_id
, fnd_global.user_id
, p_org_id
);
IF l_update_flag THEN
IF l_start_date > p_start_date THEN
IF l_end_date < p_end_date THEN
UPDATE cn_notify_log_all
SET start_date = p_start_date
, end_date = p_end_date
WHERE notify_log_id = l_notify_log_id;
UPDATE cn_notify_log
SET start_date = p_start_date
WHERE notify_log_id = l_notify_log_id;
UPDATE cn_notify_log
SET end_date = p_end_date
WHERE notify_log_id = l_notify_log_id;
IF l_update_flag OR l_insert_flag THEN
-- delete higher event entries with date range within date range for the current event
IF p_revert_to_state = 'COL' OR p_revert_to_state = 'CLS' OR p_revert_to_state = 'ROLL' THEN
IF p_salesrep_id = -1000 THEN
DELETE cn_notify_log_all
WHERE period_id = p_period_id
AND org_id = p_org_id
AND status = 'INCOMPLETE'
AND (
(
p_revert_to_state = 'ROLL'
AND revert_state = 'POP'
AND start_date BETWEEN p_start_date AND p_end_date
AND end_date BETWEEN p_start_date AND p_end_date
)
OR (
p_revert_to_state = 'CLS'
AND revert_state IN('POP', 'ROLL')
AND start_date BETWEEN p_start_date AND p_end_date
AND end_date BETWEEN p_start_date AND p_end_date
)
OR (
p_revert_to_state = 'COL'
AND revert_state IN('POP', 'ROLL', 'CLS')
AND start_date BETWEEN p_start_date AND p_end_date
AND end_date BETWEEN p_start_date AND p_end_date
)
OR revert_state = 'CALC'
)
AND action IS NULL
AND action_link_id IS NULL;
DELETE cn_notify_log_all
WHERE period_id = p_period_id
AND org_id = p_org_id
AND salesrep_id = p_salesrep_id
AND status = 'INCOMPLETE'
AND (
(
revert_state = 'POP'
AND start_date BETWEEN p_start_date AND p_end_date
AND end_date BETWEEN p_start_date AND p_end_date
)
OR revert_state = 'CALC'
)
AND action IS NULL
AND action_link_id IS NULL;
DELETE cn_notify_log_all
WHERE period_id = p_period_id
AND org_id = p_org_id
AND salesrep_id <> -1000
AND revert_state = p_revert_to_state
AND status = 'INCOMPLETE'
AND action IS NULL
AND action_link_id IS NULL;
DELETE cn_notify_log_all
WHERE period_id = p_period_id
AND org_id = p_org_id
AND salesrep_id = p_salesrep_id
AND revert_state = 'CALC'
AND quota_id = p_quota_id
AND status = 'INCOMPLETE'
AND action IS NULL
AND action_link_id IS NULL;
SELECT DISTINCT cpit2.cal_period_id
FROM cn_cal_per_int_types_all cpit1, cn_cal_per_int_types_all cpit2
WHERE cpit1.cal_period_id = p_period_id
AND cpit1.org_id = p_org_id
AND cpit2.interval_type_id = cpit1.interval_type_id
AND cpit2.interval_number = cpit1.interval_number
AND cpit2.org_id = p_org_id
AND cpit2.cal_period_id > p_period_id;
SELECT DISTINCT cpit2.cal_period_id
FROM cn_cal_per_int_types_all cpit2
WHERE (cpit2.interval_type_id, cpit2.interval_number) IN(
SELECT interval_type_id
, interval_number
FROM cn_cal_per_int_types_all
WHERE cal_period_id = p_period_id
AND interval_type_id IN(
SELECT DISTINCT q.interval_type_id
FROM cn_quotas_all q
WHERE q.quota_id IN(
SELECT quota_id
FROM cn_srp_quota_assigns_all
WHERE srp_plan_assign_id IN(
SELECT srp_plan_assign_id
FROM cn_srp_plan_assigns_all
WHERE salesrep_id = p_salesrep_id
AND org_id = p_org_id))
AND (
q.incremental_type = 'N'
OR (
q.incremental_type = 'Y'
AND EXISTS(
SELECT 1
FROM cn_calc_formulas_all
WHERE calc_formula_id =
q.calc_formula_id
AND org_id = p_org_id
AND trx_group_code = 'GROUP')
)
))
AND org_id = p_org_id)
AND cpit2.cal_period_id > p_period_id
AND cpit2.org_id = p_org_id
AND EXISTS(
SELECT 1
FROM cn_srp_intel_periods_all
WHERE salesrep_id = p_salesrep_id
AND period_id = cpit2.cal_period_id
AND org_id = p_org_id
AND processing_status_code <> 'CLEAN');
SELECT cpit2.cal_period_id
FROM cn_cal_per_int_types_all cpit2
WHERE (cpit2.interval_type_id, cpit2.interval_number, cpit2.org_id) =
(SELECT cpit1.interval_type_id
, cpit1.interval_number
, cpit1.org_id
FROM cn_cal_per_int_types_all cpit1
WHERE cpit1.cal_period_id = p_period_id
AND cpit1.org_id = p_org_id
AND cpit1.interval_type_id =
(SELECT interval_type_id
FROM cn_quotas_all pe
WHERE pe.quota_id = p_quota_id
AND (
pe.incremental_type = 'N'
OR (
pe.incremental_type = 'Y'
AND EXISTS(
SELECT 1
FROM cn_calc_formulas_all fm
WHERE fm.calc_formula_id = pe.calc_formula_id
AND fm.org_id = pe.org_id
AND fm.trx_group_code = 'GROUP')
)
)))
AND cpit2.cal_period_id > p_period_id
AND EXISTS(
SELECT 1
FROM cn_srp_intel_periods_all intel
WHERE intel.salesrep_id = p_salesrep_id
AND intel.org_id = p_org_id
AND intel.period_id = cpit2.cal_period_id
AND intel.processing_status_code <> 'CLEAN');
l_insert_flag BOOLEAN := FALSE;
SELECT 1
FROM cn_notify_log_all
WHERE period_id = p_period_id
AND org_id = p_org_id
AND (salesrep_id = p_salesrep_id OR salesrep_id = -1000)
AND status = 'INCOMPLETE'
AND (revert_state IN('COL', 'CLS', 'ROLL') OR(revert_state = 'CALC' AND quota_id IS NULL));
SELECT 1
FROM cn_notify_log_all
WHERE period_id = p_period_id
AND org_id = p_org_id
AND salesrep_id = p_salesrep_id
AND status = 'INCOMPLETE'
AND start_date = p_start_date
AND end_date = p_end_date
AND revert_state = p_revert_to_state
AND (p_comp_group_id IS NULL OR comp_group_id = p_comp_group_id)
AND NVL(action, 'DEFAULT') = NVL(p_action, 'DEFAULT')
AND NVL(action_link_id, -999999) = NVL(p_action_link_id, -999999)
AND NVL(base_salesrep_id, -999999) = NVL(p_base_salesrep_id, -999999)
AND NVL(base_comp_group_id, -999999) = NVL(p_base_comp_group_id, -999999)
AND NVL(role_id, -999999) = NVL(p_role_id, -999999);
l_insert_flag := TRUE;
l_insert_flag := TRUE;
l_insert_flag := TRUE;
IF l_insert_flag THEN
SELECT cn_notify_log_s.NEXTVAL
INTO l_notify_log_id
FROM DUAL;
INSERT INTO cn_notify_log_all
(
notify_log_id
, salesrep_id
, period_id
, start_date
, end_date
, revert_state
, event_log_id
, comp_group_id
, action
, action_link_id
, base_salesrep_id
, base_comp_group_id
, role_id
, notify_log_date
, status
, revert_sequence
, creation_date
, created_by
, last_update_date
, last_update_login
, last_updated_by
, org_id
)
VALUES (
l_notify_log_id
, p_salesrep_id
, p_period_id
, p_start_date
, p_end_date
, p_revert_to_state
, p_event_log_id
, p_comp_group_id
, p_action
, p_action_link_id
, p_base_salesrep_id
, p_base_comp_group_id
, p_role_id
, SYSDATE
, 'INCOMPLETE'
, DECODE(p_revert_to_state, 'COL', 4, 'CLS', 6, 'ROLL', 8, 'POP', 10, 'CALC', 12)
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.login_id
, fnd_global.user_id
, p_org_id
);
DELETE cn_notify_log_all
WHERE salesrep_id = p_salesrep_id
AND org_id = p_org_id
AND period_id = p_period_id
AND revert_state = p_revert_to_state
AND status = 'INCOMPLETE'
AND quota_id IS NOT NULL;
SELECT acc.period_id
, DECODE(acc.period_id, l_start_period_id, p_start_date, acc.start_date) start_date
, DECODE(acc.period_id, l_end_period_id, NVL(p_end_date, acc.end_date), acc.end_date)
end_date
FROM cn_acc_period_statuses_v acc
WHERE acc.period_id BETWEEN l_start_period_id AND l_end_period_id
AND acc.period_status = 'O'
AND acc.org_id = p_org_id
ORDER BY acc.period_id DESC;
SELECT period_id
FROM cn_period_statuses_all
WHERE period_set_id = p_period_set_id
AND period_type_id = p_period_type_id
AND p_date BETWEEN start_date AND end_date
AND org_id = p_org_id;
SELECT MAX(period_id)
FROM cn_period_statuses_all
WHERE period_set_id = p_period_set_id
AND period_type_id = p_period_type_id
AND period_status = 'O'
AND org_id = p_org_id;
SELECT acc.period_id
, DECODE(acc.period_id, l_start_period_id, p_start_date, acc.start_date) start_date
, DECODE(acc.period_id, l_end_period_id, NVL(p_end_date, acc.end_date), acc.end_date)
end_date
FROM cn_period_statuses_all acc
WHERE acc.period_set_id = p_period_set_id
AND acc.period_type_id = p_period_type_id
AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
AND acc.period_status = 'O'
AND acc.org_id = p_org_id
ORDER BY acc.period_id DESC;
SELECT period_id, start_date, end_date
FROM cn_period_statuses_all
WHERE period_set_id = p_period_set_id
AND period_type_id = p_period_type_id
AND period_status = 'O'
AND org_id = p_org_id;
SELECT dim.start_date, dim.end_date
FROM cn_dim_hierarchies_all dim
WHERE dim.header_dim_hierarchy_id = l_header_id AND org_id = p_org_id;
SELECT COUNT(*)
INTO x_count
FROM cn_repositories_all
WHERE rev_class_hierarchy_id = x_header_hierarchy_id AND org_id = p_org_id;
SELECT COUNT(*)
INTO x_count
FROM DUAL
WHERE EXISTS(SELECT 1
FROM cn_attribute_rules_all
WHERE dimension_hierarchy_id = x_header_hierarchy_id AND org_id = p_org_id);
IF (p_event_name = 'CHANGE_RC_HIER') OR(p_event_name = 'CHANGE_RC_HIER_DELETE') THEN
mark_notify_dates(p_start_date_old, p_end_date_old, 'ROLL', l_event_log_id, p_org_id);
SELECT start_date
, end_date
FROM cn_rulesets_all
WHERE ruleset_status = 'GENERATED'
AND ruleset_id IN(SELECT DISTINCT ruleset_id
FROM cn_attribute_rules_all
WHERE dimension_hierarchy_id = p_head_hierarchy_id
AND org_id = p_org_id);
IF (p_event_name = 'CHANGE_CLS_HIER') OR(p_event_name = 'CHANGE_CLS_HIER_DELETE') THEN
FOR l_set IN l_ruleset_dates_csr LOOP
cn_api.get_date_range_overlap(
p_start_date_old
, p_end_date_old
, l_set.start_date
, l_set.end_date
, p_org_id
, l_date_range_over_tbl
);
SELECT DISTINCT spq.salesrep_id
, spq.period_id
, spq.quota_id
, DECODE(acc.period_id, l_start_period_id, l_start_date, acc.start_date)
start_date
, DECODE(
acc.period_id
, l_end_period_id, NVL(l_end_date, acc.end_date)
, acc.end_date
) end_date
FROM cn_srp_period_quotas_all spq
, cn_srp_intel_periods_all sip
, cn_period_statuses_all acc
WHERE spq.quota_id = p_object_id
AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
AND sip.salesrep_id = spq.salesrep_id
AND sip.period_id = spq.period_id
AND sip.org_id = spq.org_id
AND sip.processing_status_code <> 'CLEAN'
AND acc.period_id = spq.period_id
AND acc.org_id = spq.org_id
AND acc.period_status IN('O', 'F');
SELECT start_date
, end_date
FROM cn_quotas_all
WHERE quota_id = p_object_id;
SELECT quota_id
FROM cn_srp_period_quotas_all
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT DISTINCT spq.salesrep_id
, spq.period_id
, spq.quota_id
FROM cn_srp_period_quotas_all spq
, cn_srp_intel_periods_all sip
, cn_period_statuses_all acc
WHERE spq.quota_id = p_object_id
AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
AND sip.salesrep_id = spq.salesrep_id
AND sip.period_id = spq.period_id
AND sip.org_id = spq.org_id
AND sip.processing_status_code <> 'CLEAN'
AND acc.period_id = spq.period_id
AND acc.org_id = spq.org_id
AND acc.period_status IN('O', 'F');
SELECT start_date
, end_date
FROM cn_quotas_all
WHERE quota_id = p_object_id;
SELECT quota_id
FROM cn_srp_period_quotas_all
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT DISTINCT spq.salesrep_id
, spq.period_id
, spq.quota_id
, DECODE(acc.period_id, l_start_period_id, l_start_date, acc.start_date)
start_date
, DECODE(
acc.period_id
, l_end_period_id, NVL(l_end_date, acc.end_date)
, acc.end_date
) end_date
FROM cn_srp_period_quotas_all spq
, cn_srp_intel_periods_all sip
, cn_period_statuses_all acc
WHERE spq.quota_id = p_object_id
AND spq.period_id BETWEEN l_start_period_id AND l_end_period_id
AND sip.salesrep_id = spq.salesrep_id
AND sip.period_id = spq.period_id
AND sip.org_id = spq.org_id
AND sip.processing_status_code <> 'CLEAN'
AND acc.period_id = spq.period_id
AND acc.org_id = spq.org_id
AND acc.period_status IN('O', 'F');
SELECT start_date
, end_date
FROM cn_quotas_all
WHERE quota_id = p_object_id;
SELECT quota_id
FROM cn_srp_period_quotas_all
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT sr.salesrep_id salesrep_id
, acc.period_id period_id
, DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
, DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
end_date
FROM cn_srp_roles sr, cn_srp_intel_periods intel, cn_period_statuses acc
WHERE sr.role_id = l_role_id
AND sr.org_id = p_org_id
AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
AND acc.period_status = 'O'
AND acc.org_id = p_org_id
AND intel.salesrep_id = sr.salesrep_id
AND intel.period_id = acc.period_id
AND intel.org_id = p_org_id
AND intel.processing_status_code <> 'CLEAN';
SELECT intel.salesrep_id salesrep_id
, acc.period_id period_id
, DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
, DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
end_date
FROM cn_period_statuses_all acc, cn_srp_intel_periods_all intel
WHERE acc.period_id BETWEEN l_start_period_id AND l_end_period_id
AND acc.org_id = p_org_id
AND acc.period_status = 'O'
AND intel.salesrep_id = l_salesrep_id
AND intel.period_id = acc.period_id
AND intel.org_id = acc.org_id
AND intel.processing_status_code <> 'CLEAN';
SELECT l_salesrep_id salesrep_id
, acc.period_id period_id
, DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
, DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
end_date
FROM cn_period_statuses_all acc, cn_srp_intel_periods_all intel
WHERE acc.org_id = p_org_id
AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
AND acc.period_status = 'O'
AND intel.salesrep_id = l_salesrep_id
AND intel.period_id = acc.period_id
AND intel.org_id = acc.org_id
AND intel.processing_status_code <> 'CLEAN';
SELECT DISTINCT spq.salesrep_id
, spq.period_id
, spq.quota_id
FROM cn_quotas_all cq, cn_srp_period_quotas_all spq
, cn_srp_intel_periods_all intel
WHERE cq.calc_formula_id = p_object_id
AND cq.org_id = p_org_id
AND spq.quota_id = cq.quota_id
AND intel.salesrep_id = spq.salesrep_id
AND intel.period_id = spq.period_id
AND intel.org_id = spq.org_id
AND intel.processing_status_code <> 'CLEAN'
ORDER BY spq.quota_id;
SELECT quota_id
FROM cn_srp_period_quotas_all
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT DISTINCT spq.salesrep_id
, spq.period_id
, spq.quota_id
FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
WHERE spq.quota_id IN(
SELECT rt_assign.quota_id
FROM cn_rate_sch_dims_all rt, cn_rt_quota_asgns_all rt_assign
WHERE rt.rate_dimension_id = p_object_id
AND rt_assign.rate_schedule_id = rt.rate_schedule_id)
AND intel.salesrep_id = spq.salesrep_id
AND intel.period_id = spq.period_id
AND intel.org_id = spq.org_id
AND intel.processing_status_code <> 'CLEAN'
ORDER BY spq.quota_id;
SELECT quota_id
FROM cn_srp_period_quotas_all
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT DISTINCT spq.salesrep_id
, spq.period_id
, spq.quota_id
FROM cn_srp_period_quotas spq, cn_srp_intel_periods intel
WHERE spq.quota_id IN(
SELECT rt_assign.quota_id
FROM cn_rt_quota_asgns rt_assign
WHERE rt_assign.rate_schedule_id = p_dep_object_id)
AND intel.salesrep_id = spq.salesrep_id
AND intel.period_id = spq.period_id
AND intel.processing_status_code <> 'CLEAN'
ORDER BY spq.quota_id;
SELECT quota_id
FROM cn_srp_period_quotas
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT DISTINCT spq.salesrep_id
, spq.period_id
, spq.quota_id
FROM cn_quotas_all q, cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
WHERE q.interval_type_id = p_interval_type_id
AND q.org_id = p_org_id
AND p_end_date >= q.start_date
AND (q.end_date IS NULL OR p_start_date <= q.end_date)
AND spq.quota_id = q.quota_id
AND spq.period_id = p_period_id
AND intel.salesrep_id = spq.salesrep_id
AND intel.period_id = p_period_id
AND intel.org_id = spq.org_id
AND intel.processing_status_code <> 'CLEAN'
ORDER BY spq.quota_id;
SELECT quota_id
FROM cn_srp_period_quotas_all
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT cpit.cal_period_id
, ps.start_date
, ps.end_date
FROM cn_cal_per_int_types_all cpit, cn_period_statuses_all ps
WHERE (
cpit.interval_number = p_old_interval_number
OR cpit.interval_number = p_new_interval_number
)
AND cpit.interval_type_id = p_interval_type_id
AND cpit.org_id = p_org_id
AND ps.period_id = cpit.cal_period_id
AND ps.org_id = cpit.org_id;
SELECT cal_period_id
, interval_number
, interval_type_id
, org_id
FROM cn_cal_per_int_types_all
WHERE cal_per_int_type_id = x_cal_per_int_type_id
FOR UPDATE OF cal_per_int_type_id NOWAIT;
SELECT NAME
FROM cn_interval_types_all_tl
WHERE interval_type_id = rec.interval_type_id AND org_id = p_org_id;
SELECT start_date
, end_date
FROM cn_period_statuses_all
WHERE period_id = rec.cal_period_id AND org_id = p_org_id;
SELECT /*+ LEADING(SPA) */
DISTINCT spa.salesrep_id
, acc.period_id
, acc.start_date
, acc.end_date
FROM cn_srp_plan_assigns_all spa
, cn_srp_intel_periods_all intel
, cn_period_statuses_all acc
WHERE spa.comp_plan_id = p_object_id -- comp_plan_id
AND acc.period_id BETWEEN l_start_period_id AND l_end_period_id
AND acc.org_id = spa.org_id
AND (
(
spa.start_date < acc.start_date
AND (spa.end_date IS NULL OR acc.start_date <= spa.end_date)
)
OR (spa.start_date BETWEEN acc.start_date AND acc.end_date)
)
AND EXISTS(
SELECT 1
FROM cn_srp_period_quotas_all spq
WHERE spa.srp_plan_assign_id = spq.srp_plan_assign_id
AND spq.period_id = acc.period_id)
AND intel.salesrep_id = spa.salesrep_id
AND intel.period_id = acc.period_id
AND intel.org_id = spa.org_id
AND acc.period_status IN('O', 'F')
AND intel.processing_status_code <> 'CLEAN';
SELECT spq.salesrep_id
, spq.period_id
, spq.quota_id
FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods intel
WHERE spq.srp_quota_assign_id = p_srp_object_id -- p_srp_quota_assign_id
AND intel.salesrep_id = spq.salesrep_id
AND intel.period_id = spq.period_id
AND intel.org_id = spq.org_id
-- scannane, bug 7154503, Notify log table update
-- AND intel.processing_status_code <> 'CLEAN'
ORDER BY spq.quota_id;
SELECT quota_id
FROM cn_srp_period_quotas_all
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT spq.salesrep_id
, spq.period_id
, spq.quota_id
, DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
, DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
end_date
FROM cn_srp_quota_rules_all rule
, cn_srp_period_quotas_all spq
, cn_period_statuses_all acc
, cn_srp_intel_periods_all intel
WHERE rule.srp_quota_rule_id = p_srp_object_id --p_srp_quota_rule_id
AND spq.srp_plan_assign_id = rule.srp_plan_assign_id
AND spq.srp_quota_assign_id = rule.srp_quota_assign_id
AND acc.period_id = spq.period_id
AND (acc.period_id BETWEEN l_start_period_id AND l_end_period_id)
AND acc.period_status = 'O'
AND acc.org_id = spq.org_id
AND intel.salesrep_id = spq.salesrep_id
AND intel.period_id = spq.period_id
AND intel.org_id = spq.org_id
AND intel.processing_status_code <> 'CLEAN'
ORDER BY spq.quota_id;
SELECT quota_id
FROM cn_srp_period_quotas_all
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT spq.salesrep_id
, spq.period_id
, spq.quota_id
FROM cn_srp_period_quotas_all spq
, cn_period_statuses_all acc
, cn_srp_intel_periods_all intel
WHERE spq.srp_quota_assign_id = p_srp_object_id
AND acc.period_id = spq.period_id
AND acc.org_id = spq.org_id
AND (acc.period_id BETWEEN l_srp_start_period_id AND l_srp_end_period_id)
AND acc.period_status = 'O'
AND intel.salesrep_id = spq.salesrep_id
AND intel.period_id = spq.period_id
AND intel.org_id = spq.org_id
AND intel.processing_status_code <> 'CLEAN'
ORDER BY spq.quota_id;
SELECT quota_id
FROM cn_srp_period_quotas_all
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT spq.salesrep_id
, spq.period_id
, spq.quota_id
FROM cn_srp_period_quotas_all spq, cn_srp_intel_periods_all intel
WHERE spq.srp_period_quota_id = p_srp_object_id -- p_srp_period_quota_id
AND intel.salesrep_id = spq.salesrep_id
AND intel.period_id = spq.period_id
AND intel.org_id = spq.org_id
-- scannane, bug 7154503, Notify log table update
-- AND intel.processing_status_code <> 'CLEAN'
ORDER BY spq.quota_id;
SELECT quota_id
FROM cn_srp_period_quotas_all
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT spq.salesrep_id
, spq.period_id
, spq.quota_id
, DECODE(acc.period_id, l_start_period_id, l_s_date, acc.start_date) start_date
, DECODE(acc.period_id, l_end_period_id, NVL(l_e_date, acc.end_date), acc.end_date)
end_date
FROM cn_srp_period_quotas_all spq
, cn_period_statuses_all acc
, cn_srp_intel_periods_all intel
WHERE spq.srp_quota_assign_id = p_srp_object_id -- p_srp_quota_assign_id
AND acc.period_id = spq.period_id
AND acc.org_id = spq.org_id
AND (acc.period_id BETWEEN l_start_period_id AND l_end_period_id)
AND acc.period_status = 'O'
AND intel.salesrep_id = spq.salesrep_id
AND intel.period_id = spq.period_id
AND intel.processing_status_code <> 'CLEAN'
AND intel.org_id = spq.org_id
ORDER BY spq.quota_id;
SELECT quota_id
FROM cn_srp_period_quotas_all
WHERE salesrep_id = l_salesrep_id AND period_id = l_period_id AND quota_id = l_quota_id;
SELECT salesrep_id
FROM cn_srp_comp_teams_v
WHERE comp_team_id = p_team_id AND org_id = p_org_id;
SELECT NAME
, start_date_active
, end_date_active
FROM cn_comp_teams
WHERE comp_team_id = p_team_id;
SELECT p.period_id
, GREATEST(p_start_date, p.start_date) start_date
, DECODE(p_end_date, NULL, p.end_date, LEAST(p_end_date, p.end_date)) end_date
FROM cn_srp_intel_periods_all p
WHERE p.salesrep_id = p_salesrep_id
AND p.org_id = p_org_id
AND (p_end_date IS NULL OR p.start_date <= p_end_date)
AND (p.end_date >= p_start_date);
l_action := 'DELETE_TEAM_MEMB';