[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LAST_UPDATE_DATE DATE := sysdate;
G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
(SELECT CN_API.G_MISS_ID payment_transaction_id,
-1 posting_batch_id,
cl.credited_salesrep_id,
cl.credited_salesrep_id payee_salesrep_id,
cl.quota_id,
cl.pay_period_id,
pe.incentive_type_code,
cl.credit_type_id,
NULL, -- payrun_id
nvl(cl.commission_amount,0) amount,
nvl(cl.commission_amount,0) payment_amount, -- default
'N' hold_flag, -- default N
'N' paid_flag, -- default N
'N' waive_flag, -- default N
'N' recoverable_flag, -- default N
cl.commission_header_id,
cl.commission_line_id,
null, -- pay_element_type_id
cl.srp_plan_assign_id,
cl.processed_date,
cl.processed_period_id,
cl.quota_rule_id,
cl.event_factor,
cl.payment_factor,
cl.quota_factor,
cl.input_achieved,
cl.rate_tier_id,
cl.payee_line_id,
cl.commission_rate,
cl.trx_type,
cl.role_id,
pe.expense_account_id expense_ccid,
pe.liability_account_id liability_ccid,
NULL, --cl.attribute_category,
NULL, --cl.attribute1,
null, --cl.attribute2,
null, --cl.attribute3,
null, --cl.attribute4,
null, --cl.attribute5,
null, --cl.attribute6,
null, --cl.attribute7,
null, --cl.attribute8,
null, --cl.attribute9,
null, --cl.attribute10,
null, --cl.attribute11,
null, --cl.attribute12,
null, --cl.attribute13,
null, --cl.attribute14,
null, --cl.attribute15
cl.org_id,
0
FROM cn_commission_lines_all cl,
cn_quotas_all pe
WHERE cl.commission_line_id = p_commission_line_id
AND cl.quota_id = pe.quota_id
AND cl.srp_payee_assign_id IS NULL)
UNION --this is added for assign payees for fixing bug#2495614
(SELECT CN_API.G_MISS_ID payment_transaction_id,
-1 posting_batch_id,
payee.payee_id credited_salesrep_id,
payee.payee_id payee_salesrep_id,
cl.quota_id,
cl.pay_period_id,
pe.incentive_type_code,
cl.credit_type_id,
NULL, -- payrun_id
nvl(cl.commission_amount,0) amount,
nvl(cl.commission_amount,0) payment_amount, -- default
'N' hold_flag, -- default N
'N' paid_flag, -- default N
'N' waive_flag, -- default N
'N' recoverable_flag, -- default N
cl.commission_header_id,
cl.commission_line_id,
null, -- pay_element_type_id
cl.srp_plan_assign_id,
cl.processed_date,
cl.processed_period_id,
cl.quota_rule_id,
cl.event_factor,
cl.payment_factor,
cl.quota_factor,
cl.input_achieved,
cl.rate_tier_id,
cl.payee_line_id,
cl.commission_rate,
cl.trx_type,
54,--cl.role_id
pe.expense_account_id expense_ccid,
pe.liability_account_id liability_ccid,
NULL, --cl.attribute_category,
NULL, --cl.attribute1,
null, --cl.attribute2,
null, --cl.attribute3,
null, --cl.attribute4,
null, --cl.attribute5,
null, --cl.attribute6,
null, --cl.attribute7,
null, --cl.attribute8,
null, --cl.attribute9,
null, --cl.attribute10,
null, --cl.attribute11,
null, --cl.attribute12,
null, --cl.attribute13,
null, --cl.attribute14,
null, --cl.attribute15
cl.org_id,
0
FROM cn_commission_lines_all cl,
cn_srp_payee_assigns_all payee,
cn_quotas_all pe
WHERE cl.commission_line_id = p_commission_line_id
AND cl.quota_id = pe.quota_id
AND cl.srp_payee_assign_id IS NOT NULL
AND payee.srp_payee_assign_id = cl.srp_payee_assign_id);
CN_PMT_TRANS_PKG.Insert_Record(l_pmt_trans_rec);
update cn_commission_lines
set posting_status = 'REVERTED',
last_update_date = sysdate
where commission_line_id = p_commission_line_id;
PROCEDURE Select_Tier( p_rate_dim_sequence NUMBER,
p_quota_achieved NUMBER,
p_string_value VARCHAR2,
p_direction NUMBER,
x_tier_sequence OUT NOCOPY NUMBER)
IS
l_tier_min NUMBER;
'cn.plsql.cn_formula_common_pkg.select_tier.exception',
sqlerrm);
END Select_Tier;
l_sql_select VARCHAR2(2000);
SELECT dim.number_tier, dim.dim_unit_code, rsd.rate_dimension_id
FROM cn_rate_dimensions_all dim,
cn_rate_sch_dims_all rsd
WHERE rsd.rate_schedule_id = l_rate_schedule_id
AND dim.rate_dimension_id = rsd.rate_dimension_id
ORDER BY rsd.rate_dim_sequence;
SELECT tier_sequence, minimum_amount, maximum_amount, min_exp_id, max_exp_id, string_value
FROM cn_rate_dim_tiers_all
WHERE rate_dimension_id = p_rate_dimension_id
ORDER BY tier_sequence;
SELECT dbms_lob.substr(sql_select) sql_select,
dbms_lob.substr(sql_from) sql_from
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id = p_calc_sql_exp_id;
SELECT commission_amount, rate_tier_id, rate_sequence
FROM cn_srp_rate_assigns_all
WHERE srp_plan_assign_id = p_srp_plan_assign_id
AND rt_quota_asgn_id = l_rt_quota_asgn_id
ORDER BY rate_sequence;
SELECT commission_amount, rate_tier_id, rate_sequence
FROM cn_rate_tiers_all
WHERE rate_schedule_id = l_rate_schedule_id
ORDER BY rate_sequence;
SELECT rate_dim_sequence
FROM cn_formula_inputs_all
WHERE calc_formula_id = p_calc_formula_id
AND nvl(split_flag, 'N') <> 'N';
SELECT rate_schedule_id, rt_quota_asgn_id
INTO l_rate_schedule_id, l_rt_quota_asgn_id
FROM cn_rt_quota_asgns_all
WHERE quota_id = p_quota_id
AND (calc_formula_id = p_calc_formula_id OR (calc_formula_id IS NULL AND p_calc_formula_id IS NULL))
AND (( end_date IS NOT NULL AND p_processed_date BETWEEN start_date AND end_date)
OR (end_date IS NULL AND p_processed_date >= start_date ));
g_dim_tier_table.DELETE;
g_dim_size_table.DELETE;
g_dim_type_table.DELETE;
g_tier_index_table.DELETE;
g_dynamic_tier_table.DELETE;
g_comm_amount_table.DELETE;
FETCH tier_exp INTO l_sql_select, l_sql_from;
execute immediate 'begin select ' || l_sql_select || ' into :x from ' || l_sql_from || l_where_clause || '; end;'
FETCH tier_exp INTO l_sql_select, l_sql_from;
execute immediate 'begin select ' || l_sql_select || ' into :x from ' || l_sql_from || l_where_clause || '; end;'
g_rate_tier_id_table.DELETE;
g_comm_amount_table.DELETE;
SELECT customized_flag
INTO l_customized_flag
FROM cn_srp_quota_assigns_all
WHERE srp_plan_assign_id = p_srp_plan_assign_id
AND quota_id = p_quota_id;
select_tier( p_mul_input_tbl(ctr).rate_dim_sequence,
p_mul_input_tbl(ctr).base_amount,
p_mul_input_tbl(ctr).input_string,
p_mul_input_tbl(ctr).amount,
p_mul_input_tbl(ctr).tier_sequence);
select_tier(p_mul_input_tbl(ctr).rate_dim_sequence,
p_mul_input_tbl(ctr).base_amount,
p_mul_input_tbl(ctr).input_string,
p_mul_input_tbl(ctr).amount,
p_mul_input_tbl(ctr).tier_sequence);
l_split_tbl.delete;
PROCEDURE delete_itd_trx( p_salesrep_id NUMBER ,
p_srp_plan_assign_id NUMBER ,
p_quota_id NUMBER ,
p_period_id NUMBER ) IS
CURSOR l_itd_trx_csr IS
SELECT commission_line_id, commission_header_id,
posting_status, commission_amount
FROM cn_commission_lines_all
WHERE credited_salesrep_id = p_salesrep_id
AND srp_plan_assign_id = p_srp_plan_assign_id
AND processed_period_id = p_period_id
AND quota_id = p_quota_id
AND trx_type = 'ITD'
AND status = 'CALC';
DELETE cn_commission_headers_all
WHERE commission_header_id = l_itd_trx.commission_header_id;
DELETE cn_commission_lines_all
WHERE commission_line_id = l_itd_trx.commission_line_id;
UPDATE cn_srp_period_quotas_all
SET commission_payed_ptd = commission_payed_ptd - l_itd_trx.commission_amount,
commission_payed_itd = commission_payed_itd - l_itd_trx.commission_amount
WHERE salesrep_id = p_salesrep_id
AND period_id = p_period_id
AND srp_plan_assign_id = p_srp_plan_assign_id
AND quota_id = p_quota_id;
END delete_itd_trx;
x_select_status_flag OUT NOCOPY VARCHAR2 )
IS
l_incremental_flag cn_quotas.incremental_type%TYPE;
SELECT q.incremental_type, cr.name, q.interval_type_id, q.org_id
FROM cn_quotas_all q,
cn_credit_types cr
WHERE q.quota_id = l_quota_id
AND cr.credit_type_id = q.credit_type_id
AND cr.org_id = q.org_id;
select input_sequence,
input_achieved_itd,
input_achieved_ptd
from cn_srp_period_quotas_ext_all
where srp_period_quota_id = (select srp_period_quota_id
from cn_srp_period_quotas_all
where srp_plan_assign_id = p_srp_plan_assign_id
and quota_id = p_quota_id
and salesrep_id = p_salesrep_id
and period_id = p_period_id)
order by input_sequence;
SELECT spq.rowid,
spq.srp_period_quota_id,
Nvl(spq.commission_payed_ptd,0) commission_payed_ptd,
Nvl(spq.input_achieved_ptd,0) input_achieved_ptd,
Nvl(spq.output_achieved_ptd,0) output_achieved_ptd,
Nvl(spq.perf_achieved_ptd,0) perf_achieved_ptd,
Nvl(spq.advance_recovered_ptd,0) advance_recovered_ptd ,
Nvl(spq.advance_to_rec_ptd,0) advance_to_rec_ptd,
Nvl(spq.recovery_amount_ptd,0) recovery_amount_ptd,
Nvl(spq.comm_pend_ptd,0)comm_pend_ptd
FROM cn_srp_period_quotas_all spq
WHERE salesrep_id = p_salesrep_id
AND period_id > l_start_period_id
AND quota_id = p_quota_id
AND srp_plan_assign_id = p_srp_plan_assign_id
AND period_id <= l_end_period_id
ORDER BY spq.period_id ASC;
SELECT nvl(input_achieved_ptd, 0) input_achieved_ptd,
nvl(input_achieved_itd, 0) input_achieved_itd,
input_sequence
FROM cn_srp_period_quotas_ext_all
WHERE srp_period_quota_id = p_srp_period_quota_id
ORDER BY input_sequence;
update cn_srp_period_quotas_all
set commission_payed_itd = 0, commission_payed_ptd = 0
where salesrep_id = p_salesrep_id
and quota_id = p_quota_id
and period_id >= l_start_period_id
and period_id < l_end_period_id
and srp_plan_assign_id = p_srp_plan_assign_id;
update cn_srp_period_quotas_all
set perf_achieved_itd = perf_achieved_ptd,
commission_payed_itd = commission_payed_ptd,
input_achieved_itd = input_achieved_ptd,
output_achieved_itd = output_achieved_ptd,
advance_recovered_itd = advance_recovered_ptd,
advance_to_rec_itd = advance_to_rec_ptd,
recovery_amount_itd = recovery_amount_ptd,
comm_pend_itd = comm_pend_ptd
where salesrep_id = p_salesrep_id
and quota_id = p_quota_id
and period_id = l_start_period_id
and srp_plan_assign_id = p_srp_plan_assign_id
and (nvl(perf_achieved_ptd, 0) <> nvl(perf_achieved_itd, 0) or
nvl(commission_payed_ptd, 0) <> nvl(commission_payed_itd, 0) or
nvl(input_achieved_ptd, 0) <> nvl(input_achieved_itd, 0) or
nvl(output_achieved_ptd, 0) <> nvl(output_achieved_itd, 0) or
nvl(advance_recovered_ptd, 0) <> nvl(advance_recovered_itd, 0) or
nvl(advance_to_rec_ptd, 0) <> nvl(advance_to_rec_itd, 0) or
nvl(recovery_amount_ptd, 0) <> nvl(recovery_amount_itd, 0) or
nvl(comm_pend_ptd, 0) <> nvl(comm_pend_itd, 0))
return srp_period_quota_id,
perf_achieved_itd,
commission_payed_itd,
input_achieved_itd,
output_achieved_itd,
advance_recovered_itd,
advance_to_rec_itd,
recovery_amount_itd,
comm_pend_itd
into l_srp_period_quota_id,
l_perf_achieved_itd,
l_commission_payed_itd,
l_input_achieved_itd,
l_output_achieved_itd,
l_advance_recovered_itd,
l_advance_to_rec_itd,
l_recovery_amount_itd,
l_comm_pend_itd;
update cn_srp_period_quotas_ext_all
set input_achieved_itd = input_achieved_ptd
where srp_period_quota_id = l_srp_period_quota_id;
update cn_srp_period_quotas_all
set commission_payed_itd = l_commission_payed_itd,
input_achieved_itd = l_input_achieved_itd,
output_achieved_itd = l_output_achieved_itd,
perf_achieved_itd = l_perf_achieved_itd,
advance_recovered_itd = l_advance_recovered_itd,
advance_to_rec_itd = l_advance_to_rec_itd,
recovery_amount_itd = l_recovery_amount_itd,
comm_pend_itd = l_comm_pend_itd,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id
where rowid = period.rowid;
update cn_srp_period_quotas_ext_all
set input_achieved_itd = l_input_achieved_itd_tbl(period_ext.input_sequence),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id
where srp_period_quota_id = period.srp_period_quota_id
and input_sequence = period_ext.input_sequence;
SELECT nvl(SUM(rollover), 0)
INTO l_same_pe_rollover
FROM cn_srp_period_quotas_all
WHERE srp_plan_assign_id = p_srp_plan_assign_id
AND quota_id = p_quota_id
AND period_id = (SELECT MAX(cal_period_id)
FROM cn_cal_per_int_types_all
WHERE interval_type_id = l_interval_type_id
AND cal_period_id < p_period_id
AND org_id = l_org_id
AND interval_number <> (SELECT interval_number
FROM cn_cal_per_int_types_all
WHERE interval_type_id = l_interval_type_id
AND org_id = l_org_id
AND cal_period_id = p_period_id));
SELECT SUM(nvl(cspq.rollover, 0) * csrq.rollover / 100)
INTO l_source_pe_rollover
FROM cn_srp_rollover_quotas_all csrq,
cn_srp_period_quotas_all cspq
WHERE csrq.quota_id = p_quota_id
AND csrq.srp_quota_assign_id = (SELECT srp_quota_assign_id
FROM cn_srp_quota_assigns_all
WHERE srp_plan_assign_id = p_srp_plan_assign_id
AND quota_id = p_quota_id)
AND cspq.salesrep_id = p_salesrep_id
AND cspq.quota_id = csrq.source_quota_id
AND cspq.period_id = (SELECT MAX(period_id)
FROM cn_srp_period_quotas_all
WHERE salesrep_id = p_salesrep_id
AND quota_id = csrq.source_quota_id
AND srp_quota_assign_id = cspq.srp_quota_assign_id)
AND cspq.period_id < p_period_id
AND NOT exists (SELECT 1
FROM cn_cal_per_int_types_all ccpit,
cn_srp_period_quotas_all cspq2
WHERE ccpit.org_id = l_org_id
AND ccpit.cal_period_id > (SELECT MAX(period_id)
FROM cn_srp_period_quotas_all
WHERE salesrep_id = p_salesrep_id
AND quota_id = csrq.source_quota_id
AND srp_quota_assign_id = cspq.srp_quota_assign_id)
AND ccpit.cal_period_id < p_period_id
AND cspq2.srp_plan_assign_id = p_srp_plan_assign_id
AND cspq2.quota_id = p_quota_id
AND cspq2.period_id = ccpit.cal_period_id
AND ccpit.interval_type_id = l_interval_type_id
AND ccpit.interval_number <> (SELECT interval_number
FROM cn_cal_per_int_types_all
WHERE interval_type_id = l_interval_type_id
AND cal_period_id = p_period_id
AND org_id = l_org_id))
;
UPDATE cn_srp_period_quotas_all
SET total_rollover = l_same_pe_rollover + nvl(l_source_pe_rollover, 0)
WHERE srp_plan_assign_id = p_srp_plan_assign_id
AND quota_id = p_quota_id
AND period_id = p_period_id;
delete_itd_trx( p_salesrep_id, p_srp_plan_assign_id,
p_quota_id, p_period_id );
x_select_status_flag := 'PCX';
x_select_status_flag := 'P';
cn_message_pkg.debug('--p_select_status_flag: ' || x_select_status_flag);
SELECT Nvl(quota.input_achieved_itd, 0) - Nvl(quota.input_achieved_ptd, 0),
Nvl(quota.output_achieved_itd, 0) - Nvl(quota.output_achieved_ptd, 0),
Nvl(quota.perf_achieved_itd, 0) - Nvl(quota.perf_achieved_ptd, 0),
Nvl(quota.commission_payed_itd, 0) - Nvl(quota.commission_payed_ptd, 0)
INTO x_input_achieved_itd(1), x_output_achieved_itd,
x_perf_achieved_itd, x_commission_payed_itd
FROM cn_srp_period_quotas_all quota
WHERE quota.srp_plan_assign_id = p_srp_plan_assign_id
AND quota.quota_id = p_quota_id
AND quota.salesrep_id = p_salesrep_id
AND quota.period_id = p_period_id;
SELECT
SUM(line.input_achieved), SUM(line.output_achieved),
SUM(line.perf_achieved), SUM(line.commission_amount)
INTO l_input_achieved, l_output_achieved,
l_perf_achieved, l_commission_achieved
FROM cn_commission_lines_all line
WHERE line.credited_salesrep_id = p_salesrep_id
AND line.quota_id = p_quota_id
AND line.srp_plan_assign_id = p_srp_plan_assign_id
AND line.status = 'CALC'
AND line.processed_date < p_start_date
AND line.processed_period_id = p_period_id
AND ((g_calc_type ='FORECAST' AND line.trx_type = 'FORECAST')
OR (g_calc_type ='BONUS' AND line.trx_type = 'BONUS')
OR (g_calc_type = 'COMMISSION'
AND line.trx_type NOT IN ('BONUS','FORECAST', 'GRP') ));
SELECT nvl(quota.input_achieved_itd,0), Nvl( quota.input_achieved_ptd, 0),
nvl(quota.output_achieved_itd,0), Nvl(quota.output_achieved_ptd, 0),
nvl(quota.perf_achieved_itd, 0), Nvl(quota.perf_achieved_ptd, 0),
nvl(quota.commission_payed_itd ,0), Nvl(quota.commission_payed_ptd, 0)
INTO x_input_achieved_itd(1), x_input_achieved_ptd(1),
x_output_achieved_itd, x_output_achieved_ptd,
x_perf_achieved_itd, x_perf_achieved_ptd,
x_commission_payed_itd, x_commission_payed_ptd
FROM cn_srp_period_quotas_all quota
WHERE quota.srp_plan_assign_id = p_srp_plan_assign_id
AND quota.quota_id = P_quota_id
AND quota.salesrep_id = P_salesrep_id
AND quota.period_id = P_period_id;
select max(PERIOD_ID)
INTO l_end_period_id
from cn_srp_period_quotas_all
where QUOTA_ID=p_quota_id
and srp_plan_assign_id=p_srp_plan_assign_id;
SELECT MIN(p2.cal_period_id)
INTO l_start_period_id
FROM cn_cal_per_int_types_all p2
WHERE (p2.interval_type_id, p2.org_id, p2.interval_number) IN
(SELECT p1.interval_type_id, p1.org_id, p1.interval_number
FROM cn_cal_per_int_types_all p1,
cn_quotas_all q
WHERE p1.cal_period_id = p_period_id
AND q.quota_id = p_quota_id
AND p1.org_id = q.org_id
AND p1.interval_type_id = q.interval_type_id);
SELECT MAX(p2.cal_period_id)
INTO l_end_period_id
FROM cn_cal_per_int_types_all p2
WHERE (p2.interval_type_id, p2.org_id, p2.interval_number) IN
(SELECT p1.interval_type_id, p1.org_id, p1.interval_number
FROM cn_cal_per_int_types_all p1,
cn_quotas_all q
WHERE p1.cal_period_id = p_period_id
AND q.quota_id = p_quota_id
AND p1.org_id = q.org_id
AND p1.interval_type_id = q.interval_type_id);
select min(a.cal_period_id)
INTO l_start_period_id
from cn_cal_per_int_types_all a,
cn_period_statuses_all b
where (a.interval_type_id, a.org_id) = (select interval_type_id, org_id
from cn_quotas_all
where quota_id = p_quota_id)
and a.interval_number = (select interval_number
from cn_cal_per_int_types_all
where cal_period_id = p_period_id
and (interval_type_id, org_id) = (select interval_type_id, org_id
from cn_quotas_all
where quota_id = p_quota_id))
and a.cal_period_id = b.period_id
and b.quarter_num = (select quarter_num
from cn_period_statuses_all
where period_id = p_period_id
and org_id = (select org_id from cn_quotas_all where quota_id = p_quota_id));
select max(a.cal_period_id)
INTO l_end_period_id
from cn_cal_per_int_types_all a,
cn_period_statuses_all b
where (a.interval_type_id, a.org_id) = (select interval_type_id, org_id
from cn_quotas_all
where quota_id = p_quota_id)
and a.interval_number = (select interval_number from cn_cal_per_int_types_all
where cal_period_id = p_period_id
and (interval_type_id, org_id) = (select interval_type_id, org_id
from cn_quotas_all
where quota_id = p_quota_id))
and a.cal_period_id = b.period_id
and b.quarter_num = (select quarter_num
from cn_period_statuses_all
where period_id = p_period_id
and org_id = (select org_id from cn_quotas_all where quota_id = p_quota_id));
cn_calc_subledger_pvt.update_srp_pe_subledger
( p_api_version => l_api_version,
p_init_msg_list => fnd_api.g_true,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_srp_pe_subledger => l_srp_pe_subledger);
SELECT q.quota_type_code,
q.calc_formula_id,
q.credit_type_id,
q.bonus_credit_type_id,
nvl(f.name, q.package_name),
nvl(f.formula_type, decode(g_calc_type, 'COMMISSION', 'C', 'B')),
q.org_id,
q.name
INTO l_quota_type,
l_formula_id,
l_credit_type_id,
l_bonus_credit_type_id,
l_formula_name,
l_formula_type,
l_org_id,
l_pe_name
FROM cn_quotas_all q,
cn_calc_formulas_all f
WHERE q.quota_id = p_quota_id
AND q.calc_formula_id = f.calc_formula_id(+)
AND q.org_id = f.org_id(+);
select name into l_statement from cn_salesreps where salesrep_id = p_salesrep_id and org_id = l_org_id;
SELECT role.role_id INTO l_role_id
FROM cn_srp_plan_assigns_all spa,
cn_srp_roles role
WHERE spa.srp_plan_assign_id = p_srp_plan_assign_id
AND role.srp_role_id = spa.srp_role_id
AND role.org_id = spa.org_id;
PROCEDURE update_consistency_flag( x_calc_batch_id NUMBER ) IS
BEGIN
UPDATE cn_srp_periods_all
SET consistency_flag = 'Y'
WHERE (salesrep_id, period_id, org_id) IN
( SELECT batch.salesrep_id, batch.period_id, batch.org_id
FROM cn_process_batches_all batch
WHERE batch.physical_batch_id = x_calc_batch_id);
END update_consistency_flag;
SELECT spa.salesrep_id,
spa.srp_plan_assign_id,
prd.period_id,
prd.process_all_flag,
decode(prd.period_id, batch.period_id, batch.start_date, prd.start_date) start_date
FROM cn_process_batches_all batch,
cn_srp_plan_assigns_all spa,
cn_srp_intel_periods_all prd
WHERE batch.physical_batch_id = p_physical_batch_id
AND prd.salesrep_id = batch.salesrep_id
AND prd.period_id BETWEEN batch.period_id AND batch.end_period_id
AND prd.org_id = batch.org_id
AND spa.salesrep_id = batch.salesrep_id
AND spa.org_id = batch.org_id
AND spa.start_date <= prd.end_date
AND nvl(spa.end_date, prd.end_date) >= prd.start_date
ORDER BY spa.salesrep_id, prd.period_id, spa.srp_plan_assign_id;
SELECT spq.quota_id,
qa.quota_sequence
FROM cn_srp_period_quotas_all spq,
cn_quota_assigns_all qa,
cn_quotas_all q
WHERE spq.srp_plan_assign_id = l_srp_plan_assign_id
AND spq.salesrep_id = l_salesrep_id
AND spq.period_id = l_period_id
and qa.comp_plan_id = (select comp_plan_id
from cn_srp_plan_assigns_all
where srp_plan_assign_id = l_srp_plan_assign_id)
and qa.quota_id = spq.quota_id
and q.quota_id = spq.quota_id
and q.incentive_type_code = 'COMMISSION'
order by spq.srp_plan_assign_id, qa.quota_sequence;
SELECT 1
FROM cn_notify_log_all
WHERE (salesrep_id = l_salesrep_id OR salesrep_id = -1000)
AND period_id = l_period_id
AND revert_state = 'CALC'
AND status = 'INCOMPLETE'
AND quota_id IS NULL
AND org_id = l_org_id;
SELECT 1
FROM cn_notify_log_all nlog
WHERE nlog.salesrep_id = l_salesrep_id
AND nlog.period_id = l_period_id
AND nlog.status = 'INCOMPLETE'
AND nlog.quota_id = l_quota_id
AND nlog.revert_state IN ('CALC', 'POP');
SELECT inlv.srp_plan_assign_id, inlv.salesrep_id,
inlv.end_period_id, inlv.end_date, inlv.quota_id, inlv.interval_type_id
FROM (
SELECT spa.srp_plan_assign_id srp_plan_assign_id, batch.salesrep_id salesrep_id, qa.quota_sequence quota_sequence,
batch.end_period_id end_period_id, batch.end_date end_date, pe.quota_id quota_id, pe.interval_type_id interval_type_id
FROM cn_srp_plan_assigns_all spa,
cn_quota_assigns_all qa,
cn_quotas_all pe,
cn_process_batches_all batch
WHERE batch.physical_batch_id = p_physical_batch_id
AND batch.salesrep_id = spa.salesrep_id
AND spa.org_id = batch.org_id
-- find comp plans active on batch.end_date
AND ((spa.end_date IS NOT NULL AND batch.end_date BETWEEN spa.start_date AND spa.end_date)
OR (spa.end_date IS NULL AND batch.end_date >= spa.start_date))
-- find bonus type plan element
AND qa.comp_plan_id = spa.comp_plan_id
AND qa.quota_id = pe.quota_id
AND pe.incentive_type_code = 'BONUS'
AND ((l_interval_type_id = -1000 AND pe.interval_type_id = -1000)
OR (l_interval_type_id = -1001 AND pe.interval_type_id = -1001)
OR (l_interval_type_id = -1002 AND pe.interval_type_id = -1002)
OR (l_interval_type_id = -1003 AND pe.interval_type_id IN (-1000, -1001, -1002)))
-- plan element is effective on batch.end_date
AND ((pe.end_date IS NOT NULL AND batch.end_date BETWEEN pe.start_date AND pe.end_date)
OR (pe.end_date IS NULL AND batch.end_date >= pe.start_date))
-- check if in cn_calc_sub_quotas if that exists
AND (l_calc_sub_batch_id = -1000 OR pe.quota_id IN (SELECT csq.quota_id
FROM cn_calc_sub_quotas csq
WHERE csq.calc_sub_batch_id = l_calc_sub_batch_id))
UNION
SELECT spa.srp_plan_assign_id srp_plan_assign_id, batch.salesrep_id salesrep_id, qa.quota_sequence quota_sequence,
batch.end_period_id end_period_id, batch.end_date end_date, pe.quota_id quota_id, pe.interval_type_id interval_type_id
FROM cn_srp_plan_assigns_all spa,
cn_quota_assigns_all qa,
cn_quotas_all pe,
cn_process_batches_all batch
WHERE batch.physical_batch_id = p_physical_batch_id
AND batch.salesrep_id = spa.salesrep_id
AND spa.org_id = batch.org_id
-- find comp plans active between batch start and end date
AND spa.end_date >= batch.start_date
AND spa.end_date < batch.end_date
-- find bonus type plan element
AND qa.comp_plan_id = spa.comp_plan_id
AND qa.quota_id = pe.quota_id
AND pe.incentive_type_code = 'BONUS'
AND pe.salesreps_enddated_flag = 'Y'
AND ((l_interval_type_id = -1000 AND pe.interval_type_id = -1000)
OR (l_interval_type_id = -1001 AND pe.interval_type_id = -1001)
OR (l_interval_type_id = -1002 AND pe.interval_type_id = -1002)
OR (l_interval_type_id = -1003 AND pe.interval_type_id IN (-1000, -1001, -1002)))
-- plan element is effective on comp_plan.end_date
AND ( (pe.end_date IS NOT NULL AND spa.end_date BETWEEN pe.start_date AND pe.end_date)
OR (spa.end_date >= pe.start_date AND pe.end_date IS NULL))
-- check if in cn_calc_sub_quotas if that exists
AND (l_calc_sub_batch_id = -1000 OR pe.quota_id IN (SELECT csq.quota_id
FROM cn_calc_sub_quotas csq
WHERE csq.calc_sub_batch_id = l_calc_sub_batch_id))) inlv
ORDER BY inlv.salesrep_id, inlv.end_date, inlv.quota_sequence;
select org_id into l_org_id
from cn_process_batches_all
where physical_batch_id = p_physical_batch_id and rownum = 1;
SELECT nvl(latest_processed_date, to_date('01/01/1900', 'DD/MM/YYYY'))
INTO l_current_processed_date
FROM cn_repositories_all
WHERE org_id = l_org_id;
UPDATE cn_commission_lines_all line
SET line.status = 'XCALC',
line.error_reason = 'skip calc with null commission_amount',
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE line.commission_line_id in
(SELECT line2.commission_line_id
FROM cn_process_batches_all batch,
cn_commission_lines_all line2,
cn_commission_headers_all ch
WHERE batch.physical_batch_id = p_physical_batch_id
AND line2.commission_header_id = ch.commission_header_id
AND line2.credited_salesrep_id = batch.salesrep_id
AND line2.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
AND line2.processed_date >= batch.start_date
AND line2.status = 'POP'
AND line2.org_id = batch.org_id
AND line2.trx_type NOT IN ('FORECAST', 'BONUS')
AND substr(line2.pre_processed_code, 4, 1) = 'N'
AND ch.commission_amount is null );
UPDATE cn_commission_lines_all line
SET line.status = 'CALC',
line.commission_amount = (select amthead.commission_amount
from cn_commission_headers_all amthead,
cn_commission_lines_all amtline
where amthead.commission_header_id = amtline.commission_header_id
and amtline.commission_line_id = line.commission_line_id
),
line.credit_type_id = (select credit_type_id from cn_quotas_all where quota_id = line.quota_id),
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE line.commission_line_id in
(SELECT line2.commission_line_id
FROM cn_process_batches_all batch,
cn_commission_lines_all line2,
cn_commission_headers_all ch
WHERE batch.physical_batch_id = p_physical_batch_id
AND line2.commission_header_id = ch.commission_header_id
AND line2.credited_salesrep_id = batch.salesrep_id
AND line2.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
AND line2.processed_date >= batch.start_date
AND line2.org_id = batch.org_id
AND line2.status = 'POP'
AND line2.trx_type NOT IN ('FORECAST', 'BONUS')
AND substr(line2.pre_processed_code, 4, 1) = 'N'
AND ch.commission_amount is not null );
SELECT COUNT(*) INTO l_counter
FROM cn_calc_sub_quotas
WHERE calc_sub_batch_id = l_calc_sub_batch_id;
UPDATE cn_commission_lines_all line
SET line.status = 'XCALC',
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE line.commission_line_id IN
(SELECT line2.commission_line_id
FROM cn_process_batches_all batch,
cn_commission_lines_all line2
WHERE batch.physical_batch_id = p_physical_batch_id
AND line2.org_id = batch.org_id
AND line2.credited_salesrep_id = batch.salesrep_id
AND line2.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
AND line2.processed_date >= batch.start_date
AND line2.status = 'POP'
AND substr(line2.pre_processed_code, 4, 1) = 'C'
AND trx_type NOT IN ('FORECAST', 'BONUS'));
/* UPDATE cn_notify_log_all Log
SET Log.status = 'COMPLETE'
WHERE Log.notify_log_id IN
(SELECT log2.notify_log_id
FROM cn_notify_log_all log2,
cn_process_batches_all batch
WHERE batch.physical_batch_id = p_physical_batch_id
AND log2.org_id = batch.org_id
AND log2.salesrep_id = batch.salesrep_id
AND log2.period_id BETWEEN batch.period_id AND batch.end_period_id
AND log2.status = 'INCOMPLETE'
AND log2.start_date >= batch.start_date);
UPDATE
SET log.status = 'COMPLETE'
WHERE log.status = 'INCOMPLETE';
UPDATE cn_notify_log_all Log
SET Log.status = 'COMPLETE'
WHERE Log.notify_log_id IN (
SELECT event.notify_log_id
FROM cn_notify_log_all event
WHERE event.physical_batch_id = p_physical_batch_id
AND event.action IN ('SOURCE_CLS', 'XROLL', 'ROLL_PULL', 'DELETE_ROLL_PULL')
AND event.status = 'INCOMPLETE'
UNION
SELECT event.notify_log_id
FROM cn_notify_log_all event, cn_process_batches_all batch
WHERE batch.physical_batch_id = p_physical_batch_id
AND batch.salesrep_id = event.salesrep_id
AND event.org_id = batch.org_id
AND event.period_id between batch.period_id and batch.end_period_id
AND event.action IN ('PULL', 'PULL_WITHIN', 'PULL_BELOW')
AND event.status = 'INCOMPLETE') ;
update cn_srp_intel_periods_all a
set a.process_all_flag = 'N'
where a.org_id = l_org_id
and a.salesrep_id in (select salesrep_id from cn_process_batches_all
where physical_batch_id = p_physical_batch_id)
and a.period_id >= (select min(period_id) from cn_process_batches_all
where physical_batch_id = p_physical_batch_id
and salesrep_id = a.salesrep_id)
and a.period_id <= (select max(end_period_id) from cn_process_batches_all
where physical_batch_id = p_physical_batch_id
and salesrep_id = a.salesrep_id);
UPDATE cn_repositories_all
SET latest_processed_date = l_current_processed_date
WHERE latest_processed_date < l_current_processed_date
AND org_id = l_org_id;
cn_calc_subledger_pvt.update_srp_subledger
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_srp_subledger => l_srp_subledger);
select distinct credited_salesrep_id, processed_date, processed_period_id, org_id
FROM cn_commission_lines_all
WHERE status = 'OBSOLETE'
and posting_status = 'UNPOSTED'
and (commission_header_id = (SELECT reversal_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id)
OR commission_header_id = (SELECT parent_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = (SELECT reversal_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id)));
SELECT commission_line_id
FROM cn_commission_lines_all
WHERE (commission_header_id = (SELECT reversal_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id)
OR commission_header_id = (SELECT parent_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = (SELECT reversal_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id)))
AND status = 'CALC'
AND posting_status = 'POSTED';
UPDATE cn_commission_lines_all
SET status = 'OBSOLETE', posting_status = 'UNPOSTED'
WHERE (commission_header_id = (SELECT reversal_header_id FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id)
OR commission_header_id = (SELECT parent_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = (SELECT reversal_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id)));
INSERT INTO cn_commission_lines_all
( commission_line_id, credited_salesrep_id,
processed_period_id, processed_date,
quota_id, credit_type_id, quota_rule_id,
event_factor, payment_factor,
quota_factor, commission_amount,
rate_tier_id, commission_rate,
payee_line_id, status,
trx_type, tier_split,
created_during, created_by,
creation_date, last_updated_by,
last_update_login, last_update_date,
commission_header_id, srp_plan_assign_id,
posting_status, input_achieved,
output_achieved, perf_achieved,
pay_period_id, pending_status,
role_id, pending_date, credited_comp_group_id, org_id )
SELECT cn_commission_lines_s.nextval, line.credited_salesrep_id,
line.processed_period_id, line.processed_date,
line.quota_id, line.credit_type_id, line.quota_rule_id,
line.event_factor, line.payment_factor,
line.quota_factor, -( Nvl(line.commission_amount, 0) ),
line.rate_tier_id, line.commission_rate,
line.commission_line_id, -- specify that it's a negative copy
line.status,
line.trx_type, line.tier_split,
line.created_during, g_created_by,
g_creation_date, g_last_updated_by,
g_last_update_login, sysdate,
p_commission_header_id, line.srp_plan_assign_id,
line.posting_status, -( Nvl(line.input_achieved,0) ),
-( Nvl(line.output_achieved,0)), -( Nvl(line.perf_achieved,0)),
line.pay_period_id, line.pending_status,
line.role_id, line.pending_date, line.credited_comp_group_id, line.org_id
FROM cn_commission_lines_all line
WHERE (line.commission_header_id = (SELECT reversal_header_id FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id)
OR line.commission_header_id = (SELECT parent_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = (SELECT reversal_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id)));
UPDATE cn_commission_headers_all ch SET
ch.status = 'OBSOLETE',
-- clku, update the last updated info
last_update_date = sysdate,
last_updated_by = G_LAST_UPDATED_BY,
last_update_login = G_LAST_UPDATE_LOGIN
WHERE commission_header_id = p_commission_header_id;
UPDATE cn_commission_headers_all ch SET
ch.status = 'OBSOLETE',
-- clku, update the last updated info
last_update_date = sysdate,
last_updated_by = G_LAST_UPDATED_BY,
last_update_login = G_LAST_UPDATE_LOGIN
WHERE commission_header_id
IN (SELECT head.reversal_header_id
FROM cn_commission_headers_all head
WHERE head.commission_header_id = p_commission_header_id );
DELETE FROM cn_commission_headers_all
WHERE commission_header_id = (SELECT parent_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = (SELECT reversal_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id));
UPDATE cn_commission_headers_all
SET parent_header_id = NULL,
-- clku, update the last updated info
last_update_date = sysdate,
last_updated_by = G_LAST_UPDATED_BY,
last_update_login = G_LAST_UPDATE_LOGIN
WHERE parent_header_id = (SELECT parent_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = (SELECT reversal_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id));
SELECT commission_line_id
FROM cn_commission_lines_all
WHERE commission_header_id = p_commission_header_id
AND posting_status = 'POSTED'
AND status = 'CALC';
SELECT commission_line_id
FROM cn_commission_lines_all
WHERE commission_header_id = (SELECT parent_header_id
FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id)
AND posting_status = 'POSTED'
AND status = 'CALC';
DELETE cn_commission_lines_all
WHERE commission_header_id = p_commission_header_id;
DELETE cn_commission_lines_all
WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id);
DELETE cn_commission_headers_all
WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id);
UPDATE cn_commission_headers_all
SET parent_header_id = NULL,
last_update_date = sysdate,
last_updated_by = G_LAST_UPDATED_BY,
last_update_login = G_LAST_UPDATE_LOGIN
WHERE parent_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id);
DELETE cn_commission_lines_all
WHERE commission_header_id = p_commission_header_id
AND created_during IN ( 'POP', 'CALC');
DELETE cn_commission_lines_all
WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id);
DELETE cn_commission_headers_all
WHERE commission_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id);
UPDATE cn_commission_headers_all
SET parent_header_id = NULL,
last_update_date = sysdate,
last_updated_by = G_LAST_UPDATED_BY,
last_update_login = G_LAST_UPDATE_LOGIN
WHERE parent_header_id = (SELECT parent_header_id FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id);
UPDATE cn_commission_lines_all
SET status = 'ROLL',
posting_status = 'UNPOSTED',
event_factor = NULL,
payment_factor = NULL,
quota_factor = NULL,
rate_tier_id = NULL,
commission_rate = NULL,
tier_split = NULL,
input_achieved = NULL,
output_achieved = NULL,
perf_achieved = NULL,
error_reason = NULL,
srp_payee_assign_id = NULL,
threshold_check_status = NULL,
srp_plan_assign_id = NULL,
quota_id = NULL,
quota_rule_id = NULL,
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE commission_header_id = p_commission_header_id;
SELECT member_flag, manager_flag
FROM jtf_rs_roles_b
WHERE role_id = p_role_id
AND role_type_code = 'SALES_COMP';
SELECT batch.salesrep_id, Log.period_id,
Log.start_date, Log.end_date,
Log.revert_state, Log.comp_group_id,
Log.base_salesrep_id, Log.base_comp_group_id,
Log.role_id, Log.action
FROM cn_process_batches_all batch,
cn_notify_log_all Log
WHERE batch.physical_batch_id = p_physical_batch_id
AND log.org_id = batch.org_id
AND Log.period_id BETWEEN batch.period_id AND batch.end_period_id
AND Log.salesrep_id = batch.salesrep_id
AND Log.status = 'INCOMPLETE'
AND Log.action IS NOT NULL
AND Log.action IN ('DELETE_TEAM_MEMB', 'DELETE_SOURCE', 'DELETE_DEST_WITHIN', 'DELETE_DEST_XROLL', 'DELETE_DEST')
ORDER BY batch.salesrep_id, Log.period_id, Log.revert_sequence, Log.notify_log_id;
SELECT batch.salesrep_id, Log.period_id,
Log.start_date, Log.end_date,
Log.revert_state, Log.comp_group_id,
Log.base_salesrep_id, Log.base_comp_group_id,
Log.quota_id
FROM cn_process_batches_all batch,
cn_notify_log_all Log
WHERE batch.physical_batch_id = p_physical_batch_id
AND log.org_id = batch.org_id
AND Log.period_id BETWEEN batch.period_id AND batch.end_period_id
AND ( Log.salesrep_id = batch.salesrep_id
OR Log.salesrep_id = -1000 )
AND Log.status = 'INCOMPLETE'
AND Log.revert_state <> 'NCALC'
ORDER BY batch.salesrep_id, Log.period_id, Log.revert_sequence, Log.notify_log_id;
SELECT line.commission_line_id,
line.posting_status,
line.created_during
FROM cn_commission_lines_all line
WHERE line.credited_salesrep_id = l_salesrep_id
AND line.processed_period_id = l_period_id
AND line.quota_id = l_quota_id
AND line.processed_date BETWEEN l_start_date AND l_end_date
AND line.trx_type NOT IN ('FORECAST', 'BONUS')
AND line.status NOT IN ( 'XPOP', 'OBSOLETE' );
SELECT line.commission_line_id,
line.posting_status,
line.created_during
FROM cn_commission_lines line
WHERE line.credited_salesrep_id = l_salesrep_id
AND line.processed_period_id = l_period_id
AND line.processed_date BETWEEN l_start_date AND l_end_date
AND line.status <> 'OBSOLETE'
AND line.trx_type NOT IN ('FORECAST', 'BONUS')
AND line.org_id = l_org_id;
SELECT line.commission_line_id
FROM cn_commission_lines_all line
WHERE line.commission_header_id
IN ( SELECT header.commission_header_id
FROM cn_commission_headers_all header
WHERE header.direct_salesrep_id = l_salesrep_id
AND header.processed_period_id = l_period_id
AND header.processed_date BETWEEN l_start_date AND l_end_date
AND header.status = 'ROLL'
AND header.org_id = l_org_id
AND header.trx_type NOT IN ('FORECAST', 'BONUS') )
AND line.posting_status = 'POSTED'
AND line.status = 'CALC';
SELECT line.commission_line_id, line.posting_status
FROM cn_commission_lines_all line
WHERE line.credited_salesrep_id = l_salesrep_id
AND line.processed_period_id = l_period_id
AND line.trx_type IN ('ITD', 'GRP')
AND line.org_id = l_org_id
AND ((l_revert_state = 'POP' AND line.quota_id = l_quota_id) OR
(l_revert_state = 'CALC' AND (line.quota_id = l_quota_id or l_quota_id is null)) OR
(l_revert_state not in ('POP', 'CALC')));
CURSOR revert_lines_delete_source(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
p_start_date DATE, p_end_date DATE, p_role_id NUMBER, p_base_comp_group_id NUMBER,
p_base_salesrep_id NUMBER)
IS
SELECT commission_line_id
FROM cn_commission_lines_all cl
WHERE cl.credited_salesrep_id = p_salesrep_id
AND cl.credited_comp_group_id = p_comp_group_id
AND cl.processed_period_id = p_period_id
and cl.status = 'CALC'
and cl.posting_status = 'POSTED'
and cl.org_id = l_org_id
AND cl.processed_date BETWEEN p_start_date AND p_end_date
AND ((p_role_id IS NOT NULL AND cl.role_id = p_role_id ) OR p_role_id IS NULL)
AND exists ( SELECT 1
FROM cn_commission_headers_all ch
WHERE ch.commission_header_id = cl.commission_header_id
AND ch.comp_group_id = p_base_comp_group_id
AND ( p_base_salesrep_id IS NULL OR ch.direct_salesrep_id = p_base_salesrep_id));
SELECT commission_line_id
FROM cn_commission_lines_all cl
WHERE cl.posting_status = 'POSTED'
and cl.status = 'CALC'
AND cl.credited_comp_group_id = p_comp_group_id
AND cl.processed_period_id = p_period_id
AND cl.processed_date BETWEEN p_start_date AND p_end_date
AND cl.org_id = l_org_id
AND ((p_role_id IS NOT NULL AND cl.role_id = p_role_id
AND ((l_mgr_role_flag = 'N' and l_mem_role_flag = 'N' and cl.credited_salesrep_id = p_salesrep_id)
OR (l_mgr_role_flag = 'N' and l_mem_role_flag = 'Y' and cl.credited_salesrep_id = p_salesrep_id and cl.direct_salesrep_id <> p_salesrep_id))
) OR (p_role_id IS NULL AND cl.credited_salesrep_id = p_salesrep_id))
AND exists
( SELECT 1
FROM cn_commission_headers_all ch
WHERE ch.commission_header_id = cl.commission_header_id
AND ch.comp_group_id = p_comp_group_id );
CURSOR revert_lines_delete_dest(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
IS
SELECT commission_line_id
FROM cn_commission_lines_all cl
WHERE cl.credited_salesrep_id = p_salesrep_id
and cl.posting_status = 'POSTED'
and cl.status = 'CALC'
AND cl.credited_comp_group_id = p_comp_group_id
AND cl.processed_period_id = p_period_id
AND cl.processed_date BETWEEN p_start_date AND p_end_date
AND cl.org_id = l_org_id
AND ( (p_role_id IS NOT NULL AND cl.role_id = p_role_id ) OR p_role_id IS NULL );
CURSOR revert_lines_delete_dest2(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
IS
SELECT commission_line_id
FROM cn_commission_lines_all cl
WHERE cl.credited_salesrep_id = p_salesrep_id
and cl.posting_status = 'POSTED'
and cl.status = 'CALC'
AND cl.credited_comp_group_id = p_comp_group_id
AND cl.processed_period_id = p_period_id
AND cl.processed_date BETWEEN p_start_date AND p_end_date
AND cl.org_id = l_org_id
AND ( (p_role_id IS NOT NULL AND cl.role_id = p_role_id ) OR p_role_id IS NULL )
AND NOT exists (SELECT 1
FROM cn_srp_comp_groups_v
WHERE comp_group_id = cl.credited_comp_group_id
AND salesrep_id = cl.credited_salesrep_id
AND role_id = cl.role_id
AND org_id = cl.org_id
AND cl.processed_date BETWEEN start_date_active AND Nvl(end_date_active, cl.processed_date));
CURSOR revert_lines_delete_dest3(p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_period_id NUMBER,
p_start_date DATE, p_end_date DATE, p_role_id NUMBER)
IS
SELECT commission_line_id
FROM cn_commission_lines_all cl
WHERE cl.credited_salesrep_id = p_salesrep_id
and cl.posting_status = 'POSTED'
and cl.status = 'CALC'
and cl.org_id = l_org_id
AND cl.credited_comp_group_id = p_comp_group_id
AND cl.direct_salesrep_id <> p_salesrep_id
AND cl.processed_period_id = p_period_id
AND cl.processed_date BETWEEN p_start_date AND p_end_date
and not exists (select 1
from cn_srp_comp_groups_v
where comp_group_id = p_comp_group_id
and salesrep_id = cl.credited_salesrep_id
and cl.processed_date between start_date_active and nvl(end_date_active, cl.processed_date)
and org_id = cl.org_id
and manager_flag = 'Y')
and exists( select 1
from cn_srp_comp_groups_v
where comp_group_id = p_comp_group_id
and salesrep_id = cl.direct_salesrep_id
and org_id = cl.org_id
and cl.processed_date between start_date_active and nvl(end_date_active, cl.processed_date));
CURSOR revert_lines_delete_team_memb(p_salesrep_id NUMBER, p_period_id NUMBER,
p_start_date DATE, p_end_date DATE) IS
SELECT commission_line_id
from cn_commission_lines_all
where posting_status = 'POSTED'
and (commission_header_id, credited_salesrep_id) in
(select commission_header_id, credited_salesrep_id
FROM cn_commission_lines_all cl
WHERE cl.credited_salesrep_id = p_salesrep_id
AND cl.processed_period_id = p_period_id
AND cl.created_during = 'TROLL'
AND cl.org_id = l_org_id
AND cl.processed_date BETWEEN p_start_date AND p_end_date);
select org_id into l_org_id
from cn_process_batches_all
where physical_batch_id = p_physical_batch_id
and rownum = 1;
UPDATE cn_srp_intel_periods_all
SET process_all_flag = 'Y'
WHERE org_id = l_org_id
AND ( salesrep_id, period_id ) IN
( SELECT DISTINCT batch.salesrep_id, Log.period_id
FROM cn_process_batches_all batch,
cn_notify_log_all Log
WHERE batch.physical_batch_id = p_physical_batch_id
AND log.org_id = batch.org_id
AND Log.period_id BETWEEN batch.period_id AND batch.end_period_id
AND ( Log.salesrep_id = batch.salesrep_id
OR Log.salesrep_id = -1000 )
AND Log.status = 'INCOMPLETE'
AND ( Log.revert_state NOT IN ( 'NCALC', 'CALC', 'POP')
OR ( Log.action IS NOT NULL
AND Log.action IN ('DELETE_TEAM_MEMB', 'DELETE_SOURCE', 'DELETE_DEST_WITHIN', 'DELETE_DEST_XROLL', 'DELETE_DEST') )
)
);
IF l_log.action = 'DELETE_TEAM_MEMB' THEN
FOR line IN revert_lines_delete_team_memb(l_log.salesrep_id,l_log.period_id,l_log.start_date,
l_log.end_date) LOOP
revert_posting_line(line.commission_line_id);
DELETE cn_commission_lines_all
WHERE (commission_header_id, credited_salesrep_id) in
(select commission_header_id, credited_salesrep_id
from cn_commission_lines_all cl
where cl.credited_salesrep_id = l_log.salesrep_id
AND cl.processed_period_id = l_log.period_id
AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND cl.created_during = 'TROLL'
AND cl.org_id = l_org_id);
ELSIF l_log.action = 'DELETE_SOURCE' THEN
FOR line IN revert_lines_delete_source(l_log.salesrep_id,l_log.comp_group_id,l_log.period_id,l_log.start_date,
l_log.end_date,l_log.role_id,l_log.base_comp_group_id, l_log.base_salesrep_id)
LOOP
revert_posting_line(line.commission_line_id);
DELETE cn_commission_lines_all cl
WHERE cl.credited_salesrep_id = l_log.salesrep_id
AND cl.credited_comp_group_id = l_log.comp_group_id
AND cl.processed_period_id = l_log.period_id
AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND cl.org_id = l_org_id
AND ( (l_log.role_id IS NOT NULL AND cl.role_id = l_log.role_id )
OR l_log.role_id IS NULL )
AND exists
(SELECT 1
FROM cn_commission_headers_all ch
WHERE ch.commission_header_id = cl.commission_header_id
AND ch.comp_group_id = l_log.base_comp_group_id
AND ( l_log.base_salesrep_id IS NULL
OR ch.direct_salesrep_id = l_log.base_salesrep_id)
);
ELSIF l_log.action = 'DELETE_DEST_WITHIN' THEN
l_mem_role_flag := 'N';
DELETE cn_commission_lines_all cl
WHERE cl.credited_comp_group_id = l_log.comp_group_id
AND cl.processed_period_id = l_log.period_id
AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND cl.org_id = l_org_id
AND ((l_log.role_id IS NOT NULL AND
cl.role_id = l_log.role_id AND
((l_mgr_role_flag = 'N' and l_mem_role_flag = 'N' and cl.credited_salesrep_id = l_log.salesrep_id) OR
(l_mgr_role_flag = 'N' and l_mem_role_flag = 'Y' and cl.credited_salesrep_id = l_log.salesrep_id and cl.direct_salesrep_id <> l_log.salesrep_id))
)OR (l_log.role_id IS NULL and cl.credited_salesrep_id = l_log.salesrep_id))
AND exists
( SELECT 1
FROM cn_commission_headers_all ch
WHERE ch.commission_header_id = cl.commission_header_id
AND ch.comp_group_id = l_log.comp_group_id );
ELSIF l_log.action = 'DELETE_DEST_XROLL' THEN
FOR line IN revert_lines_delete_dest(l_log.salesrep_id,l_log.comp_group_id,l_log.period_id,l_log.start_date,
l_log.end_date,l_log.role_id)
LOOP
revert_posting_line(line.commission_line_id);
DELETE cn_commission_lines_all cl
WHERE cl.credited_salesrep_id = l_log.salesrep_id
AND cl.credited_comp_group_id = l_log.comp_group_id
AND cl.processed_period_id = l_log.period_id
AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND cl.org_id = l_org_id
AND ( (l_log.role_id IS NOT NULL AND cl.role_id = l_log.role_id )
OR l_log.role_id IS NULL );
UPDATE cn_commission_headers_all ch
SET status = 'XROLL',
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE ch.direct_salesrep_id = l_log.salesrep_id
AND ch.comp_group_id = l_log.comp_group_id
AND ch.processed_period_id = l_log.period_id
AND Nvl(ch.parent_header_id, -1) = -1
AND ch.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND ch.org_id = l_org_id;
ELSIF l_log.action = 'DELETE_DEST' THEN
FOR line IN revert_lines_delete_dest2(l_log.salesrep_id,l_log.comp_group_id,l_log.period_id,l_log.start_date,
l_log.end_date,l_log.role_id)
LOOP
revert_posting_line(line.commission_line_id);
DELETE cn_commission_lines_all cl
WHERE cl.credited_salesrep_id = l_log.salesrep_id
AND cl.credited_comp_group_id = l_log.comp_group_id
AND cl.processed_period_id = l_log.period_id
AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND cl.org_id = l_org_id
AND ((l_log.role_id IS NOT NULL AND cl.role_id = l_log.role_id ) OR l_log.role_id IS NULL )
AND NOT exists (SELECT 1
FROM cn_srp_comp_groups_v
WHERE comp_group_id = l_log.comp_group_id
AND role_id = cl.role_id
AND salesrep_id = cl.credited_salesrep_id
AND org_id = cl.org_id
AND cl.processed_date BETWEEN start_date_active AND Nvl(end_date_active, cl.processed_date));
UPDATE cn_commission_lines_all cl
SET created_during = 'ROLL'
WHERE cl.credited_salesrep_id = l_log.salesrep_id
AND cl.credited_comp_group_id = l_log.comp_group_id
AND cl.processed_period_id = l_log.period_id
AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND cl.created_during = 'POP'
AND org_id = l_org_id
AND NOT exists (SELECT 1
FROM cn_commission_lines_all
WHERE commission_header_id = cl.commission_header_id
AND credited_salesrep_id = cl.credited_salesrep_id
AND credited_comp_group_id = l_log.comp_group_id
AND org_id = cl.org_id
AND created_during = 'ROLL')
AND cl.commission_line_id IN (SELECT MIN(commission_line_id)
FROM cn_commission_lines_all
WHERE credited_salesrep_id = l_log.salesrep_id
AND credited_comp_group_id = l_log.comp_group_id
AND processed_period_id = l_log.period_id
AND processed_date BETWEEN l_log.start_date AND l_log.end_date
AND created_during = 'POP'
AND org_id = l_org_id
GROUP BY commission_header_id);
FOR line IN revert_lines_delete_dest3(l_log.salesrep_id,l_log.comp_group_id,l_log.period_id,l_log.start_date,
l_log.end_date,l_log.role_id)
LOOP
revert_posting_line(line.commission_line_id);
DELETE cn_commission_lines_all cl
WHERE cl.credited_salesrep_id = l_log.salesrep_id
AND cl.credited_comp_group_id = l_log.comp_group_id
AND cl.direct_salesrep_id <> l_log.salesrep_id
AND cl.processed_period_id = l_log.period_id
AND cl.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND cl.org_id = l_org_id
and not exists (select 1
from cn_srp_comp_groups_v
where comp_group_id = l_log.comp_group_id
and salesrep_id = cl.credited_salesrep_id
and cl.processed_date between start_date_active and nvl(end_date_active, cl.processed_date)
and manager_flag = 'Y'
and org_id = cl.org_id)
and exists( select 1
from cn_srp_comp_groups_v
where comp_group_id = l_log.comp_group_id
and salesrep_id = cl.direct_salesrep_id
and cl.processed_date between start_date_active and nvl(end_date_active, cl.processed_date)
and org_id = cl.org_id);
-- 1). delete 'UNPOSTED' and created_during 'CALC'
DELETE cn_commission_lines_all line
WHERE line.credited_salesrep_id = l_log.salesrep_id
AND line.processed_period_id = l_log.period_id
AND line.quota_id = l_log.quota_id
AND line.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND line.trx_type NOT IN ('FORECAST', 'BONUS')
AND line.status <> 'OBSOLETE'
AND ( line.posting_status IS NULL OR line.posting_status <> 'POSTED' )
AND line.created_during = 'CALC';
DELETE FROM cn_commission_lines_all
WHERE credited_salesrep_id = l_log.salesrep_id
AND processed_period_id = l_log.period_id
AND quota_id = l_log.quota_id
AND processed_date BETWEEN l_log.start_date AND l_log.end_date
AND trx_type NOT IN ('FORECAST', 'BONUS')
AND status <> 'OBSOLETE'
AND created_during = 'CALC';
UPDATE cn_commission_lines_all
SET status = 'POP', -- and more
posting_status = 'UNPOSTED',
event_factor = NULL,
payment_factor = NULL,
quota_factor = NULL,
commission_amount = NULL,
rate_tier_id = NULL,
commission_rate = NULL,
tier_split = NULL,
input_achieved = NULL,
output_achieved = NULL,
perf_achieved = NULL,
error_reason = NULL,
srp_payee_assign_id = NULL,
threshold_check_status = NULL,
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE credited_salesrep_id = l_log.salesrep_id
AND processed_period_id = l_log.period_id
AND quota_id = l_log.quota_id
AND processed_date BETWEEN l_log.start_date AND l_log.end_date
AND trx_type NOT IN ('FORECAST', 'BONUS')
AND status NOT IN ('XPOP', 'OBSOLETE');
-- 1). delete trx created during 'POP', 'CALC' and 'UNPOSTED'
DELETE cn_commission_lines_all line
WHERE line.credited_salesrep_id = l_log.salesrep_id
AND line.processed_period_id = l_log.period_id
AND line.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND line.trx_type NOT IN ('FORECAST', 'BONUS')
AND line.status <> 'OBSOLETE'
AND ( line.posting_status IS NULL OR line.posting_status <> 'POSTED')
AND line.created_during IN ('POP','CALC')
AND line.org_id = l_org_id;
DELETE FROM cn_commission_lines_all
WHERE credited_salesrep_id = l_log.salesrep_id
AND processed_period_id = l_log.period_id
AND processed_date BETWEEN l_log.start_date AND l_log.end_date
AND trx_type NOT IN ('FORECAST', 'BONUS')
AND status <> 'OBSOLETE'
AND created_during in ('POP', 'CALC')
AND org_id = l_org_id;
UPDATE cn_commission_lines_all
SET status = 'ROLL', -- and more
posting_status = 'UNPOSTED',
event_factor = NULL,
payment_factor = NULL,
quota_factor = NULL,
commission_amount = NULL,
rate_tier_id = NULL,
commission_rate = NULL,
tier_split = NULL,
input_achieved = NULL,
output_achieved = NULL,
perf_achieved = NULL,
error_reason = NULL,
srp_payee_assign_id = NULL,
threshold_check_status = NULL,
srp_plan_assign_id = NULL,
quota_id = NULL,
quota_rule_id = NULL,
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE credited_salesrep_id = l_log.salesrep_id
AND processed_period_id = l_log.period_id
AND processed_date BETWEEN l_log.start_date AND l_log.end_date
AND trx_type NOT IN ('FORECAST', 'BONUS')
AND status <> 'OBSOLETE'
AND org_id = l_org_id;
-- 2). delete trx created during 'ROLL' 'POP', 'CALC',
-- basically everything from lines table
DELETE cn_commission_lines_all line
WHERE line.org_id = l_org_id
AND line.commission_header_id
IN ( SELECT header.commission_header_id
FROM cn_commission_headers header
WHERE header.direct_salesrep_id = l_log.salesrep_id
AND header.processed_period_id = l_log.period_id
AND header.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND header.status = 'ROLL'
AND header.trx_type NOT IN ('FORECAST', 'BONUS')
AND header.org_id = l_org_id );
-- 3). update header trx status to be 'CLS' ('CLS_SUM' if rolling up summarized trxs)
UPDATE cn_commission_headers_all
SET status = decode(parent_header_id, -1, 'CLS_SUM', 'CLS'),
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE direct_salesrep_id = l_log.salesrep_id
AND processed_period_id = l_log.period_id
AND processed_date BETWEEN l_log.start_date AND l_log.end_date
AND status <> 'OBSOLETE'
AND status = 'ROLL'
AND trx_type NOT IN ('FORECAST', 'BONUS')
AND org_id = l_org_id;
-- 1). delete 'ITD','GRP' trx created in commission_lines
FOR l_itd_grp_trx IN l_itd_grp_trx_csr ( l_log.salesrep_id,
l_log.period_id,
l_log.quota_id,
l_log.revert_state )
LOOP
IF l_itd_grp_trx.posting_status = 'POSTED' THEN
revert_posting_line( l_itd_grp_trx.commission_line_id);
DELETE FROM cn_commission_lines_all line
WHERE line.credited_salesrep_id =l_log.salesrep_id
AND line.processed_period_id = l_log.period_id
AND line.trx_type IN ('ITD', 'GRP')
AND ((l_log.revert_state = 'POP' AND line.quota_id = l_log.quota_id) OR
(l_log.revert_state = 'CALC' AND (line.quota_id = l_log.quota_id or l_log.quota_id is null)) OR
(l_log.revert_state not in ('POP', 'CALC')))
AND line.org_id = l_org_id;
-- 2). delete 'GRP' trx created in commission_headers
-- need to delete 'GRP' trxs in commission_header since its counterpart in line has been deleted
DELETE cn_commission_headers_all header
WHERE header.direct_salesrep_id = l_log.salesrep_id
--AND header.processed_date BETWEEN l_log.start_date AND l_log.end_date
AND header.processed_period_id = l_log.period_id
AND header.trx_type IN ('ITD', 'GRP')
AND ((l_log.revert_state = 'POP' AND header.quota_id = l_log.quota_id) OR
(l_log.revert_state = 'CALC' AND (header.quota_id = l_log.quota_id or l_log.quota_id is null)) OR
(l_log.revert_state not in ('POP', 'CALC')))
AND header.org_id = l_org_id;
SELECT line.commission_line_id
FROM cn_commission_lines_all line,
cn_process_batches_all batch
WHERE batch.physical_batch_id = p_batch_id
AND line.org_id = batch.org_id
AND line.credited_salesrep_id = batch.salesrep_id
AND line.status = 'CALC'
AND line.posting_status = 'POSTED'
AND line.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
AND line.processed_date BETWEEN batch.start_date AND batch.end_date
AND ( (p_calc_type = 'COMMISSION' AND line.trx_type NOT IN ('BONUS', 'FORECAST' ) )
OR (p_calc_type ='FORECAST' AND line.trx_type = 'FORECAST') );
SELECT DISTINCT period_id, end_period_id, start_date, end_date
FROM cn_process_batches_all
WHERE physical_batch_id = p_batch_id;
select org_id into l_org_id
from cn_process_batches_all
where physical_batch_id = p_batch_id
and rownum = 1;
update cn_srp_intel_periods_all a
set a.process_all_flag = 'Y'
where a.org_id = l_org_id
and a.salesrep_id in (select salesrep_id from cn_process_batches_all
where physical_batch_id = p_batch_id)
and a.period_id >= (select min(period_id) from cn_process_batches_all
where physical_batch_id = p_batch_id
and salesrep_id = a.salesrep_id)
and a.period_id <= (select max(end_period_id) from cn_process_batches_all
where physical_batch_id = p_batch_id
and salesrep_id = a.salesrep_id);
DELETE /*+ index(line cn_commission_lines_n7) */ cn_commission_lines_all line
WHERE line.credited_salesrep_id IN (SELECT salesrep_id
FROM cn_process_batches_all
WHERE physical_batch_id = p_batch_id)
AND line.processed_period_id BETWEEN l_period_id AND l_end_period_id
AND line.processed_date BETWEEN l_start_date AND l_end_date
AND line.status <> 'OBSOLETE'
AND line.org_id = l_org_id
AND ((p_calc_type ='FORECAST' AND line.trx_type = 'FORECAST')
OR (p_calc_type = 'COMMISSION' AND line.trx_type NOT IN ('BONUS','FORECAST')))
and rownum < 10000;
DELETE cn_commission_lines_all del_line
WHERE del_line.commission_line_id IN
(SELECT line.commission_line_id
FROM cn_commission_lines_all line,
cn_process_batches_all batch
WHERE batch.physical_batch_id = p_batch_id
AND line.org_id = batch.org_id
AND line.credited_salesrep_id = batch.salesrep_id
AND line.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
AND line.processed_date BETWEEN batch.start_date AND batch.end_date
AND line.status <> 'OBSOLETE'
AND ((p_calc_type ='FORECAST' AND line.trx_type = 'FORECAST')
OR (p_calc_type = 'COMMISSION' AND line.trx_type NOT IN ('BONUS','FORECAST')) ) )
and rownum < 10000;
-- need to delete 'ITD','GRP' trxs in commission_header since its counterpart in line has been deleted
-- bonus trx are all non accumulative, so 'ITD', 'GRP' doesn't apply.
IF (l_unique_flag = 'Y') THEN
delete cn_commission_headers_all ch
where ch.direct_salesrep_id in (select salesrep_id
from cn_process_batches_all
where physical_batch_id = p_batch_id)
and ch.processed_date between l_start_date and l_end_date
AND (ch.trx_type IN ('GRP', 'ITD') OR ch.parent_header_id = -1)
and ch.org_id = l_org_id;
DELETE cn_commission_headers_all head
WHERE head.commission_header_id IN
( SELECT dh.commission_header_id
FROM cn_commission_headers_all dh,
cn_process_batches_all batch
WHERE batch.physical_batch_id = p_batch_id
AND dh.org_id = batch.org_id
AND batch.salesrep_id = dh.direct_salesrep_id
AND dh.processed_date BETWEEN batch.start_date AND batch.end_date
AND (dh.trx_type IN ('GRP', 'ITD') OR dh.parent_header_id = -1));
UPDATE cn_commission_headers_all up_header
SET status = 'COL',
revenue_class_id = decode(substr(pre_processed_code,1,1), 'C', NULL, revenue_class_id),
parent_header_id = NULL,
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE up_header.commission_header_id IN
( SELECT header.commission_header_id
FROM cn_commission_headers_all header,
cn_process_batches_all batch
WHERE batch.physical_batch_id = p_batch_id
AND batch.salesrep_id = header.direct_salesrep_id
AND header.org_id = batch.org_id
AND header.status <> 'OBSOLETE'
AND header.processed_date BETWEEN batch.start_date AND batch.end_date
AND ((p_calc_type ='FORECAST' AND header.trx_type = 'FORECAST')
OR (p_calc_type = 'COMMISSION' AND header.trx_type NOT IN ('BONUS','FORECAST')) ) );
UPDATE cn_commission_headers_all
SET status = 'COL',
revenue_class_id = decode(substr(pre_processed_code,1,1), 'C', NULL, revenue_class_id),
parent_header_id = NULL,
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE org_id = l_org_id
AND direct_salesrep_id IN (SELECT salesrep_id
FROM cn_process_batches_all
WHERE physical_batch_id = p_batch_id)
AND processed_date BETWEEN l_start_date AND l_end_date
AND status <> 'OBSOLETE'
AND ((p_calc_type ='FORECAST' AND trx_type = 'FORECAST')
OR (p_calc_type = 'COMMISSION' AND trx_type NOT IN ('BONUS','FORECAST')));
SELECT calc_sub_batch_id, interval_type_id, org_id
FROM cn_calc_submission_batches_all
WHERE logical_batch_id IN (SELECT logical_batch_id
FROM cn_process_batches_all
WHERE physical_batch_id = p_batch_id
AND rownum = 1);
SELECT COUNT(*)
FROM cn_calc_sub_quotas_all
WHERE calc_sub_batch_id = l_calc_sub_batch_id;
select cl.commission_line_id
FROM cn_commission_lines_all cl,
cn_process_batches_all batch
WHERE batch.physical_batch_id = p_batch_id
AND batch.salesrep_id = cl.credited_salesrep_id
and cl.org_id = l_org_id
--and cl.processed_period_id between batch.period_id and batch.end_period_id
and cl.processed_date between batch.start_date and batch.end_date
and cl.status = 'CALC'
and cl.posting_status = 'POSTED'
and cl.trx_type = 'BONUS'
and (exists (select 1 from cn_quotas_all
where quota_id = cl.quota_id
and (l_interval_type_id = interval_type_id or l_interval_type_id = -1003)))
and (l_calc_sub_batch_id = -1000 or
cl.quota_id in (select quota_id from cn_calc_sub_quotas_all
where calc_sub_batch_id = l_calc_sub_batch_id));
-- delete header lines
delete from cn_commission_headers_all
where commission_header_id in (
select cl.commission_header_id
from cn_commission_lines_all cl,
cn_process_batches_all batch
where batch.physical_batch_id = p_batch_id
and batch.salesrep_id = cl.credited_salesrep_id
and cl.org_id = l_org_id
--and cl.processed_period_id between batch.period_id and batch.end_period_id
and cl.processed_date between batch.start_date and batch.end_date
and cl.trx_type = 'BONUS'
and (exists (select 1 from cn_quotas_all
where quota_id = cl.quota_id
and (l_interval_type_id = interval_type_id or l_interval_type_id = -1003)))
and (l_calc_sub_batch_id = -1000 or
cl.quota_id in (select quota_id from cn_calc_sub_quotas_all
where calc_sub_batch_id = l_calc_sub_batch_id)));
-- delete detail lines
delete from cn_commission_lines_all
where commission_line_id in (
select cl.commission_line_id
from cn_commission_lines_all cl,
cn_process_batches_all batch
where batch.physical_batch_id = p_batch_id
and batch.salesrep_id = cl.credited_salesrep_id
and cl.org_id =l_org_id
--and cl.processed_period_id between batch.period_id and batch.end_period_id
and cl.processed_date between batch.start_date and batch.end_date
and cl.trx_type = 'BONUS'
and (exists (select 1 from cn_quotas_all
where quota_id = cl.quota_id
and (l_interval_type_id = interval_type_id or l_interval_type_id = -1003)))
and (l_calc_sub_batch_id = -1000 or
cl.quota_id in (select quota_id from cn_calc_sub_quotas_all
where calc_sub_batch_id = l_calc_sub_batch_id)));
PROCEDURE update_trx( p_trx_rec_old IN OUT NOCOPY trx_rec_type,
p_trx_rec_new IN OUT NOCOPY trx_rec_type) IS
l_lupd_chg number := 0;
UPDATE cn_commission_lines_all
SET commission_amount = p_trx_rec_new.commission_amount,
commission_rate = p_trx_rec_new.commission_rate,
rate_tier_id = p_trx_rec_new.rate_tier_id,
tier_split = p_trx_rec_new.tier_split,
input_achieved = p_trx_rec_new.input_achieved,
output_achieved = p_trx_rec_new.output_achieved,
perf_achieved = p_trx_rec_new.perf_achieved,
status = p_trx_rec_new.status,
credit_type_id = p_trx_rec_new.credit_type_id,
posting_status = decode(posting_status, 'REVERTED', decode(p_trx_rec_new.status, 'CALC', 'UNPOSTED', posting_status), posting_status),
error_reason = p_trx_rec_new.error_reason,
last_update_date = decode(l_lupd_chg,1,sysdate,last_update_date),
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
WHERE commission_line_id = p_trx_rec_old.commission_line_id;
END update_trx;
SELECT 1
FROM cn_commission_lines_all
WHERE credited_salesrep_id = p_salesrep_id
AND srp_plan_assign_id = p_srp_plan_assign_id
AND quota_id = p_quota_id
AND processed_period_id BETWEEN l_start_period_id AND p_period_id
AND status = 'CALC'
AND created_during <> 'CALC'
AND pending_status = 'Y';
SELECT cn_commission_headers_s.NEXTVAL INTO l_header_id FROM dual;
SELECT role_id, org_id INTO l_role_id, l_org_id
FROM cn_srp_plan_assigns_all
WHERE srp_plan_assign_id = p_trx_rec.srp_plan_assign_id;
INSERT INTO cn_commission_headers_all
(commission_header_id, direct_salesrep_id, processed_date,
processed_period_id, trx_type, status, quota_id,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login, org_id )
VALUES
(l_header_id, p_trx_rec.salesrep_id, p_trx_rec.processed_date ,
p_trx_rec.processed_period_id, p_trx_rec.trx_type, 'ROLL', p_trx_rec.quota_id,
sysdate, g_last_updated_by, g_creation_date,
g_created_by, g_last_update_login, l_org_id);
INSERT INTO cn_commission_lines_all
(commission_line_id, credited_salesrep_id, commission_header_id,
quota_id, credit_type_id, srp_plan_assign_id, role_id, status ,
commission_amount, commission_rate, rate_tier_id, tier_split,
input_achieved, output_achieved,
perf_achieved, posting_status, pending_status,
processed_date, processed_period_id, pay_period_id,
trx_type, created_during, error_reason,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login, org_id )
VALUES
(cn_commission_lines_s.NEXTVAL, p_trx_rec.salesrep_id, l_header_id,
p_trx_rec.quota_id, p_trx_rec.credit_type_id, p_trx_rec.srp_plan_assign_id, l_role_id, p_trx_rec.status,
Round(Nvl(p_trx_rec.commission_amount,0), g_ext_precision),
Round(Nvl(p_trx_rec.commission_rate,0), CN_GLOBAL_VAR.g_ext_precision ),
Nvl(p_trx_rec.rate_tier_id, 0), Nvl(p_trx_rec.tier_split, 0),
Round( Nvl(p_trx_rec.input_achieved, 0), CN_GLOBAL_VAR.g_ext_precision),
Round( Nvl(p_trx_rec.output_achieved, 0), CN_GLOBAL_VAR.g_ext_precision),
Round( Nvl(p_trx_rec.perf_achieved, 0 ), CN_GLOBAL_VAR.g_ext_precision),
p_trx_rec.posting_status, p_trx_rec.pending_status,
p_trx_rec.processed_date, p_trx_rec.processed_period_id,p_trx_rec.pay_period_id,
p_trx_rec.trx_type, p_trx_rec.created_during, p_trx_rec.error_reason,
sysdate, g_last_updated_by, g_creation_date,
g_created_by, g_last_update_login, l_org_id)
return commission_line_id into l_commission_line_id;
update cn_commission_lines_all cl
set srp_payee_assign_id = (SELECT spa.srp_payee_assign_id
FROM cn_srp_quota_assigns_all sqa,
cn_srp_payee_assigns_all spa
WHERE sqa.srp_plan_assign_id = cl.srp_plan_assign_id
AND sqa.quota_id = cl.quota_id
AND nvl(spa.delete_flag, 'N') <> 'Y'
AND sqa.srp_quota_assign_id = spa.srp_quota_assign_id
AND cl.processed_date BETWEEN spa.start_date AND nvl(spa.end_date,cl.processed_date))
where cl.commission_line_id = l_commission_line_id;
PROCEDURE create_update_grp_trx( p_grp_trx_rec IN OUT NOCOPY trx_rec_type) IS
l_commission_line_id NUMBER(15);
SELECT cl.commission_line_id ,cl.commission_header_id,
-- null reversal_header_id, null reversal_flag,
cl.credited_salesrep_id salesrep_id,
cl.srp_plan_assign_id, cl.quota_id, cl.credit_type_id,
cl.processed_date, cl.processed_period_id,
cl.pay_period_id, cl.commission_amount,
cl.commission_rate, cl.rate_tier_id ,
cl.tier_split, cl.input_achieved ,
cl.output_achieved, cl.perf_achieved,
cl.posting_status, cl.pending_status,
cl.created_during, cl.trx_type,
cl.error_reason, cl.status
FROM cn_commission_lines cl
WHERE cl.credited_salesrep_id = p_grp_trx_rec.salesrep_id
AND cl.quota_id = p_grp_trx_rec.quota_id
AND cl.srp_plan_assign_id = p_grp_trx_rec.srp_plan_assign_id
AND cl.created_during = 'CALC'
AND cl.trx_type = 'GRP'
AND cl.processed_period_id = p_grp_trx_rec.processed_period_id;
update_trx( l_grp_trx_rec_old, p_grp_trx_rec );
ELSE -- the grp trx is not created yet or has been deleted
create_new_trx( p_grp_trx_rec);
END create_update_grp_trx;
create_update_grp_trx(p_trx_rec);
SELECT salesrep_id,
period_id,
end_period_id,
start_date,
end_date,
org_id
FROM cn_process_batches_all
WHERE physical_batch_id = p_physical_batch_id;
UPDATE cn_commission_lines_all cl
SET (payment_factor,quota_factor) =
(SELECT squ.payment_factor/100,
squ.quota_factor/100
FROM cn_srp_quota_rules_all sqr,
cn_srp_rule_uplifts_all squ,
cn_quota_rule_uplifts_all qru
WHERE sqr.srp_plan_assign_id = cl.srp_plan_assign_id
AND sqr.quota_rule_id = cl.quota_rule_id
AND sqr.srp_quota_rule_id = squ.srp_quota_rule_id
AND qru.quota_rule_id = cl.quota_rule_id
AND cl.processed_date BETWEEN qru.start_date AND Nvl(qru.end_date, cl.processed_date)
AND qru.quota_rule_uplift_id = squ.quota_rule_uplift_id),
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login,
event_factor = (SELECT event_factor/100
FROM cn_trx_factors_all tf
WHERE tf.quota_rule_id = cl.quota_rule_id
AND tf.trx_type = cl.trx_type),
(srp_payee_assign_id) = (SELECT spa.srp_payee_assign_id
FROM cn_srp_quota_assigns_all sqa,
cn_srp_payee_assigns_all spa
WHERE sqa.srp_plan_assign_id = cl.srp_plan_assign_id
AND sqa.quota_id = cl.quota_id
AND nvl(spa.delete_flag, 'N') <> 'Y'
AND sqa.srp_quota_assign_id = spa.srp_quota_assign_id
AND cl.processed_date BETWEEN spa.start_date AND nvl(spa.end_date,cl.processed_date))
WHERE cl.credited_salesrep_id = salesrep.salesrep_id
AND cl.processed_period_id between salesrep.period_id AND salesrep.end_period_id
AND cl.processed_date BETWEEN salesrep.start_date AND salesrep.end_date
AND cl.org_id = salesrep.org_id
AND cl.status = 'POP' -- IN ('POP', 'CALC', 'XCALC')
AND ((l_calc_type = 'COMMISSION'
AND cl.trx_type NOT IN ('BONUS', 'GRP', 'FORECAST')) OR
(l_calc_type = 'FORECAST'
AND cl.trx_type = 'FORECAST'));
SELECT 1
FROM cn_commission_lines_all
WHERE credited_salesrep_id = p_salesrep_id
AND srp_plan_assign_id = p_srp_plan_assign_id
AND processed_period_id = p_period_id
AND quota_id = p_quota_id
AND status = 'CALC';
SELECT pq.itd_target
FROM cn_period_quotas_all pq
WHERE pq.period_id = p_period_id
AND pq.quota_id = p_quota_id;
SELECT spq.itd_target
FROM cn_srp_period_quotas_all spq
WHERE spq.period_id = p_period_id
AND spq.quota_id = p_quota_id
AND spq.salesrep_id = p_salesrep_id
AND spq.srp_plan_assign_id = p_srp_plan_assign_id;
SELECT pq.itd_payment
FROM cn_period_quotas_all pq
WHERE pq.period_id = p_period_id
AND pq.quota_id = p_quota_id;
SELECT spq.itd_payment
FROM cn_srp_period_quotas_all spq
WHERE spq.period_id = p_period_id
AND spq.quota_id = p_quota_id
AND spq.salesrep_id = p_salesrep_id
AND spq.srp_plan_assign_id = p_srp_plan_assign_id;