[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT end_date
INTO l_next_end_date
FROM cn_acc_period_statuses_v
WHERE p_end_date BETWEEN start_date AND end_date
AND org_id = p_org_id;
SELECT MAX(cal_period_id) max_cal_period_id
FROM cn_cal_per_int_types_all
WHERE interval_type_id = p_interval_type_id
AND org_id = p_org_id
AND cal_period_id < p_start_period_id
AND interval_number =
(SELECT interval_number
FROM cn_cal_per_int_types_all
WHERE cal_period_id = p_start_period_id
AND org_id = p_org_id
AND interval_type_id = p_interval_type_id
);
SELECT cal_period_id
FROM cn_cal_per_int_types_all
WHERE interval_type_id = p_interval_type_id
AND org_id = p_org_id
AND cal_period_id >= p_start_period_id
AND interval_number =
(SELECT interval_number
FROM cn_cal_per_int_types_all
WHERE cal_period_id = p_start_period_id
AND org_id = p_org_id
AND interval_type_id = p_interval_type_id
);
SELECT period_id ,
salesrep_id ,
srp_plan_assign_id,
quota_id ,
org_id
INTO l_start_period_id,
l_salesrep_id ,
l_srp_plan_assign_id ,
l_quota_id ,
l_org_id
FROM cn_srp_period_quotas_all
WHERE srp_period_quota_id = x_start_srp_period_quota_id;
SELECT interval_type_id
INTO l_interval_type_id
FROM cn_quotas_all
WHERE quota_id = l_quota_id;
SELECT NVL(spq.input_achieved_itd,0),
NVL(spq.output_achieved_itd,0) ,
NVL(spq.perf_achieved_itd,0) ,
NVL(spq.commission_payed_itd,0) ,
NVL(spq.advance_recovered_itd,0) ,
NVL(spq.advance_to_rec_itd,0) ,
NVL(spq.recovery_amount_itd,0) ,
NVL(spq.comm_pend_itd,0) ,
-- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
NVL(spq.itd_target,0) ,
NVL(spq.itd_payment,0),
NVL(spq.performance_goal_itd,0)
INTO l_input_achieved_itd,
l_output_achieved_itd ,
l_perf_achieved_itd ,
l_commission_payed_itd ,
l_advance_recovered_itd ,
l_advance_to_rec_itd ,
l_recovery_amount_itd ,
l_comm_pend_itd ,
-- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
l_itd_target ,
l_itd_payment,
l_performance_goal_itd
FROM cn_srp_period_quotas_all spq
WHERE salesrep_id = l_salesrep_id
AND srp_plan_assign_id = l_srp_plan_assign_id
AND quota_id = l_quota_id
AND period_id = l_previous_period_id;
UPDATE cn_srp_period_quotas_all
SET input_achieved_itd = nvl(input_achieved_ptd,0) + l_input_achieved_itd ,
output_achieved_itd = nvl(output_achieved_ptd,0) + l_output_achieved_itd ,
perf_achieved_itd = nvl(perf_achieved_ptd,0) + l_perf_achieved_itd ,
commission_payed_itd = nvl(commission_payed_ptd,0) + l_commission_payed_itd ,
advance_recovered_itd = nvl(advance_recovered_ptd,0) + l_advance_recovered_itd,
advance_to_rec_itd = nvl(advance_to_rec_ptd,0) + l_advance_to_rec_itd ,
recovery_amount_itd = nvl(recovery_amount_ptd,0) + l_recovery_amount_itd ,
comm_pend_itd = nvl(comm_pend_ptd,0) + l_comm_pend_itd ,
-- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
itd_target = nvl(target_amount,0) + l_itd_target ,
itd_payment = nvl(period_payment,0) + l_itd_payment,
performance_goal_itd = nvl(performance_goal_ptd,0) + l_performance_goal_itd
WHERE salesrep_id = l_salesrep_id
AND srp_plan_assign_id = l_srp_plan_assign_id
AND quota_id = l_quota_id
AND period_id = i_period_id.cal_period_id;
SELECT NVL(spq.input_achieved_itd,0),
NVL(spq.output_achieved_itd,0) ,
NVL(spq.perf_achieved_itd,0) ,
NVL(spq.commission_payed_itd,0) ,
NVL(spq.advance_recovered_itd,0) ,
NVL(spq.advance_to_rec_itd,0) ,
NVL(spq.recovery_amount_itd,0) ,
NVL(spq.comm_pend_itd,0) ,
-- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
NVL(spq.itd_target,0) ,
NVL(spq.itd_payment,0),
NVL(spq.performance_goal_itd,0)
INTO l_input_achieved_itd,
l_output_achieved_itd ,
l_perf_achieved_itd ,
l_commission_payed_itd ,
l_advance_recovered_itd ,
l_advance_to_rec_itd ,
l_recovery_amount_itd ,
l_comm_pend_itd ,
-- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
l_itd_target ,
l_itd_payment,
l_performance_goal_itd
FROM cn_srp_period_quotas_all spq
WHERE salesrep_id = l_salesrep_id
AND srp_plan_assign_id = l_srp_plan_assign_id
AND quota_id = l_quota_id
AND period_id = i_period_id.cal_period_id;
SELECT spq.srp_period_quota_id srp_period_quota_id ,
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.commission_payed_ptd,0) commission_payed_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 ,
NVL(spq.target_amount,0) target_amount ,
NVL(spq.period_payment,0) period_payment ,
NVL(spq.performance_goal_ptd,0) performance_goal_ptd
FROM cn_srp_period_quotas_all spq,
cn_period_statuses_all cp ,
cn_cal_per_int_types_all cpit ,
cn_quotas_all cq
WHERE spq.quota_id = x_quota_id
AND spq.quota_id = cq.quota_id
AND spq.period_id = cp.period_id
AND spq.org_id = cp.org_id
AND spq.period_id = cpit.cal_period_id
AND spq.org_id = cpit.org_id
AND spq.srp_quota_assign_id = l_srp_quota_assign_id
AND cpit.interval_type_id = cq.interval_type_id
AND cpit.interval_number = l_interval_number
AND cp.period_year = l_period_year
ORDER BY spq.period_id;
SELECT p.srp_quota_assign_id srp_quota_assign_id,
COUNT(p.srp_period_quota_id) interval_count ,
cpit.interval_number interval_number ,
p.period_year period_year
FROM cn_srp_period_quotas_v p,
cn_period_statuses cp ,
cn_cal_per_int_types_all cpit ,
cn_quotas_all cq
WHERE p.quota_id = x_quota_id
AND p.quota_id = cq.quota_id
AND p.period_id = cp.period_id
AND cp.period_status IN ('O', 'F')
AND cq.org_id = cp.org_id
AND cp.period_id = cpit.cal_period_id
AND cp.org_id = cpit.org_id
AND cpit.interval_type_id = cq.interval_type_id
GROUP BY p.srp_quota_assign_id,
cpit.interval_number ,
p.period_year ;
UPDATE cn_srp_period_quotas_all
SET itd_target = NVL(l_target_total,0) ,
itd_payment = NVL(l_payment_total,0) ,
performance_goal_itd = NVL(l_performance_goal_total,0) ,
input_achieved_itd = NVL(l_input_achieved_total, 0) ,
output_achieved_itd = NVL(l_output_achieved_total, 0) ,
perf_achieved_itd = NVL(l_perf_achieved_total, 0) ,
commission_payed_itd = NVL(l_commission_payed_total, 0) ,
advance_recovered_itd = NVL(l_advance_recovered_total, 0),
advance_to_rec_itd = NVL(l_advance_to_rec_total, 0) ,
recovery_amount_itd = NVL(l_recovery_amount_total, 0) ,
comm_pend_itd = NVL(l_comm_pend_total, 0)
WHERE srp_period_quota_id = pq_rec.srp_period_quota_id ;
PROCEDURE Insert_Record
(
x_srp_plan_assign_id NUMBER ,
x_quota_id NUMBER ,
x_start_period_id NUMBER ,
x_end_period_id NUMBER ,
x_start_date DATE := NULL ,
x_end_date DATE := NULL )
IS
l_user_id NUMBER(15);
SELECT q.quota_id quota_id,
q.org_id
FROM cn_srp_quota_assigns_all qa ,
cn_quotas_all q
WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
-- do not need itd and formula id anymore, bug 2462767,AND q.calc_formula_id = cf.calc_formula_id(+)
AND qa.quota_id = q.quota_id;
SELECT srp_period_quota_id
FROM cn_srp_period_quotas_all
WHERE quota_id = l_quota_id
AND srp_plan_assign_id = l_srp_plan_assign_id ;
SELECT srp_period_quota_id
FROM cn_srp_period_quotas_all
WHERE quota_id = l_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 period_id = x_start_period_id;
SELECT MAX(srp_period_quota_id)
FROM cn_srp_period_quotas_all
WHERE quota_id = p_quota_id
AND period_id = x_start_period_id
GROUP BY srp_plan_assign_id;
SELECT ccf.number_dim
FROM cn_quotas_all cq,
cn_calc_formulas_all ccf
WHERE cq.quota_id = l_quota_id
AND cq.calc_formula_id = ccf.calc_formula_id;
INSERT
INTO cn_srp_period_quotas_all
(
srp_period_quota_id ,
srp_plan_assign_id ,
srp_quota_assign_id ,
salesrep_id ,
period_id ,
quota_id ,
target_amount ,
itd_target ,
period_payment ,
itd_payment ,
performance_goal_ptd ,
performance_goal_itd ,
commission_payed_ptd ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
org_id
)
SELECT cn_srp_period_quotas_s.nextval ,
qa.srp_plan_assign_id ,
qa.srp_quota_assign_id ,
pa.salesrep_id ,
p.period_id ,
qa.quota_id ,
NVL(pq.period_target,0) ,
pq.itd_target ,
pq.period_payment ,
pq.itd_payment ,
pq.performance_goal ,
pq.performance_goal_itd ,
0 ,
sysdate ,
l_user_id ,
sysdate ,
l_user_id ,
l_login_id ,
qa.org_id
FROM cn_srp_quota_assigns_all qa ,
cn_period_quotas_all pq ,
cn_srp_plan_assigns_all pa ,
cn_period_statuses p
WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
AND pa.srp_plan_assign_id = x_srp_plan_assign_id
AND pa.srp_plan_assign_id = qa.srp_plan_assign_id --bugfix3633222
AND qa.quota_id = pq.quota_id
AND pq.period_id = p.period_id
AND p.period_status IN ('O', 'F')
AND pq.org_id = p.org_id
AND QA.ORG_ID = PQ.ORG_ID --bug fix 7381426
AND pq.quota_id = itd_p_rec.quota_id
AND greatest(p.start_date, NVL(x_start_date, pa.start_date)) <= least(p.end_date, NVL(x_end_date, NVL(pa.end_date,p.end_date)))
AND NOT EXISTS
(SELECT 'srp_period_quota already exists'
FROM cn_srp_period_quotas_all spq
WHERE spq.srp_quota_assign_id = qa.srp_quota_assign_id
AND spq.period_id = p.period_id
)
-- bug 2460926, clku, check if all the open period ends before the specified start_date
-- 2479359, Nvl(x_start_date, pa.start_date) added to deal with NULL x_start_date
AND EXISTS
(SELECT r1.end_date
FROM CN_PERIOD_STATUSES_ALL R1
WHERE r1.end_date >= NVL(x_start_date, pa.start_date)
AND (R1.PERIOD_SET_ID, R1.PERIOD_TYPE_ID ) IN
(SELECT CR.PERIOD_SET_ID,
CR.PERIOD_TYPE_ID
FROM CN_REPOSITORIES_ALL CR
WHERE cr.org_id= r1.org_id
)
AND R1.PERIOD_STATUS IN ('O', 'F')
AND r1.org_id = pa.org_id
) ;
populate_srp_period_quotas_ext ('INSERT',srp_period_quota_id_rec1.srp_period_quota_id, itd_p_rec.org_id, l_number_dim);
INSERT
INTO cn_srp_period_quotas_all
(
srp_period_quota_id ,
srp_plan_assign_id ,
srp_quota_assign_id ,
salesrep_id ,
period_id ,
quota_id ,
target_amount ,
itd_target ,
performance_goal_ptd ,
performance_goal_itd ,
period_payment ,
itd_payment ,
commission_payed_ptd ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
org_id
)
SELECT cn_srp_period_quotas_s.nextval ,
qa.srp_plan_assign_id ,
qa.srp_quota_assign_id ,
pa.salesrep_id ,
p.period_id ,
qa.quota_id ,
NVL(pq.period_target,0) ,
pq.itd_target ,
pq.performance_goal ,
pq.performance_goal_itd ,
pq.period_payment ,
pq.itd_payment ,
0 ,
sysdate ,
l_user_id ,
sysdate ,
l_user_id ,
l_login_id ,
qa.org_id
FROM cn_srp_quota_assigns_all qa ,
cn_period_quotas_all pq ,
cn_srp_plan_assigns_all pa ,
cn_period_statuses p
WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
AND pa.srp_plan_assign_id = qa.srp_plan_assign_id
AND qa.quota_id = x_quota_id
AND greatest(pa.start_date, p.start_date) <= least(NVL(pa.end_date,p.end_date), p.end_date)
AND pq.period_id = p.period_id
AND p.period_status IN ('O', 'F')
AND QA.ORG_ID = PQ.ORG_ID --bug fix 7381426
AND pq.org_id = p.org_id
AND pq.quota_id = qa.quota_id
AND NOT EXISTS
(SELECT 'srp_period_quota already exists'
FROM cn_srp_period_quotas_all spq
WHERE spq.srp_quota_assign_id = qa.srp_quota_assign_id
AND spq.period_id = p.period_id
)
AND EXISTS
(SELECT r1.end_date
FROM CN_PERIOD_STATUSES_ALL R1
WHERE r1.end_date >= NVL(x_start_date, pa.start_date)
AND (R1.PERIOD_SET_ID, R1.PERIOD_TYPE_ID ) IN
(SELECT CR.PERIOD_SET_ID,
CR.PERIOD_TYPE_ID
FROM CN_REPOSITORIES_ALL CR
WHERE cr.org_id= r1.org_id
)
AND R1.PERIOD_STATUS IN ('O', 'F')
AND r1.org_id = pa.org_id
);
populate_srp_period_quotas_ext ('INSERT',srp_period_quota_id_rec1.srp_period_quota_id, itd_p_rec.org_id, l_number_dim);
INSERT
INTO cn_srp_period_quotas_all
(
srp_period_quota_id ,
srp_plan_assign_id ,
srp_quota_assign_id ,
salesrep_id ,
period_id ,
quota_id ,
target_amount ,
itd_target ,
period_payment ,
itd_payment ,
performance_goal_ptd ,
performance_goal_itd ,
commission_payed_ptd ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
org_id
)
SELECT cn_srp_period_quotas_s.nextval ,
qa.srp_plan_assign_id ,
qa.srp_quota_assign_id ,
pa.salesrep_id ,
p.period_id ,
qa.quota_id ,
0 -- clku, enhancement 2431086, Nvl(q.payment_amount,0)
,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
sysdate ,
l_user_id ,
sysdate ,
l_user_id ,
l_login_id ,
qa.org_id
FROM cn_srp_quota_assigns_all qa ,
cn_quotas_all q ,
cn_srp_plan_assigns_all pa ,
cn_acc_period_statuses_v p
-- bug fix 4042235
,
cn_period_statuses p2 ,
cn_period_statuses p3
WHERE qa.srp_plan_assign_id = pa.srp_plan_assign_id
AND qa.quota_id = x_quota_id
AND q.quota_id = x_quota_id
AND q.quota_id = qa.quota_id --bugfix#3633222
AND p.org_id = qa.org_id
AND (
-- bug 2150333, changed to improved performance
-- set 1: pa.start_date
(pa.start_date BETWEEN p2.start_date AND p2.end_date
AND pa.org_id = p2.org_id)
AND p.start_date >= p2.start_date
AND p.period_type_id = p2.period_type_id
AND p.period_set_id = p2.period_set_id
-- set 2: pa.end_date
-- clku, fixed a date insert issue
AND (least(NVL(pa.end_date,p.end_date), p.end_date) BETWEEN p3.start_date AND p3.end_date)
AND p.end_date <= p3.end_date
AND p.org_id = p3.org_id
AND p.period_type_id = p3.period_type_id
AND p.period_set_id = p3.period_set_id )
AND greatest(p.start_date, x_start_date) <= least(p.end_date, NVL(x_end_date, p.end_date))
AND NOT EXISTS
(SELECT 'srp_quota_assign already exists'
FROM cn_srp_period_quotas_all pq
WHERE pq.srp_quota_assign_id = qa.srp_quota_assign_id
AND pq.period_id = p.period_id
)
-- bug 2460926, check if all the open period ends before the specified start_date
AND EXISTS
(SELECT r1.end_date
FROM CN_PERIOD_STATUSES_ALL R1
WHERE r1.end_date >= NVL(x_start_date, pa.start_date)
AND (R1.PERIOD_SET_ID, R1.PERIOD_TYPE_ID ) IN
(SELECT CR.PERIOD_SET_ID,
CR.PERIOD_TYPE_ID
FROM CN_REPOSITORIES_ALL CR
WHERE cr.org_id= r1.org_id
)
AND R1.PERIOD_STATUS IN ('O', 'F')
AND r1.org_id = pa.org_id
) ;
populate_srp_period_quotas_ext ('INSERT',srp_period_quota_id_rec2.srp_period_quota_id, itd_p_rec.org_id, l_number_dim);
END Insert_Record;
SELECT target_amount
FROM cn_srp_period_quotas_all
WHERE srp_period_quota_id = x_srp_period_quota_id FOR UPDATE OF srp_period_quota_id NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_record
(
x_period_target_unit_code VARCHAR2 ,
x_srp_period_quota_id NUMBER ,
x_srp_quota_assign_id NUMBER ,
x_period_id NUMBER ,
x_target_amount NUMBER ,
x_period_payment NUMBER ,
x_performance_goal NUMBER ,
x_quarter_num NUMBER ,
x_period_year NUMBER ,
x_quota_type_code VARCHAR2 ,
x_quota_id NUMBER := NULL -- only for bonus pay
,
x_salesrep_id NUMBER := NULL -- only for bonus pay
,
x_end_date DATE := NULL -- only for bonus pay
,
x_commission_payed_ptd NUMBER := NULL -- only for bonus pay
,
x_last_update_date DATE ,
x_last_updated_by NUMBER ,
x_last_update_login NUMBER)
IS
-- Count the number of periods in each quarter/year combination that the
-- quota assignment covers
CURSOR quart_counts
IS
SELECT COUNT(srp_period_quota_id) quart_yr_count ,
quarter_num ,
period_year
FROM cn_srp_period_quotas_v
WHERE srp_quota_assign_id = x_srp_quota_assign_id
GROUP BY quarter_num,
period_year ;
SELECT COUNT(srp_period_quota_id) year_count ,
period_year
FROM cn_srp_period_quotas_v
WHERE srp_quota_assign_id = x_srp_quota_assign_id
GROUP BY period_year ;
SELECT spq.srp_period_quota_id ,
spq.target_amount ,
spq.period_payment ,
spq.performance_goal_ptd
FROM cn_srp_period_quotas_v spq,
cn_period_statuses cp ,
cn_cal_per_int_types_all cpit ,
cn_quotas_all cq
WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
AND spq.quota_id = cq.quota_id
AND spq.period_id = cp.period_id
AND cp.period_status IN ('O', 'F')
AND cq.org_id = cp.org_id
AND cp.period_id = cpit.cal_period_id
AND cp.org_id = cpit.org_id
AND cpit.interval_type_id = cq.interval_type_id
AND cpit.interval_number = l_interval_number
AND spq.period_year = l_period_year
ORDER BY spq.period_id ;
SELECT COUNT(spq.srp_period_quota_id) interval_count,
cpit.interval_number interval_number ,
spq.period_year period_year
FROM cn_srp_period_quotas_v spq,
cn_period_statuses cp ,
cn_cal_per_int_types_all cpit ,
cn_quotas_all cq
WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
AND spq.quota_id = cq.quota_id
AND spq.period_id = cp.period_id
AND cp.period_status IN ('O', 'F')
AND cq.org_id = cp.org_id
AND cp.period_id = cpit.cal_period_id
AND cp.org_id = cpit.org_id
AND cpit.interval_type_id = cq.interval_type_id
GROUP BY cpit.interval_number,
spq.period_year ;
SELECT period.target_amount,
period.period_payment ,
srp.name ,
acc.start_date ,
acc.end_date ,
srp.org_id
FROM cn_srp_period_quotas_all period,
cn_salesreps srp ,
cn_period_statuses acc
WHERE period.srp_period_quota_id = x_srp_period_quota_id
AND acc.period_id = period.period_id
AND acc.org_id = period.org_id
AND acc.period_status IN ('O', 'F')
AND srp.salesrep_id = period.salesrep_id
AND srp.org_id = period.org_id;
SELECT c.extended_precision
INTO g_ext_precision
FROM cn_repositories r,
gl_sets_of_books b ,
fnd_currencies c ,
cn_srp_period_quotas spq
WHERE r.org_id = spq.org_id
AND r.set_of_books_id = b.set_of_books_id
AND b.currency_code = c.currency_code
AND spq.srp_period_quota_id = x_srp_period_quota_id;
UPDATE cn_srp_period_quotas_all
SET target_amount = ROUND(NVL(x_target_amount, 0), g_ext_precision),
period_payment = ROUND(NVL(x_period_payment,0), g_ext_precision),
performance_goal_ptd = ROUND(NVL(x_performance_goal,0), g_ext_precision)
WHERE srp_period_quota_id = x_srp_period_quota_id ;
UPDATE cn_srp_period_quotas_all
SET itd_target = ROUND(NVL(l_target_total,0), g_ext_precision) ,
itd_payment = ROUND(NVL(l_payment_total,0), g_ext_precision),
performance_goal_itd = ROUND(NVL(l_performance_goal_total,0),g_ext_precision)
WHERE srp_period_quota_id = pq_rec.srp_period_quota_id ;
UPDATE cn_srp_period_quotas_all spq
SET spq.commission_payed_ptd = x_commission_payed_ptd +spq.commission_payed_ptd
WHERE spq.salesrep_id = x_salesrep_id
AND spq.quota_id = x_quota_id
AND EXISTS
(SELECT 1
FROM cn_period_statuses aps
WHERE x_end_date BETWEEN aps.start_date AND aps.end_date
AND aps.period_id = spq.period_id
AND aps.period_status IN ('O', 'F')
AND aps.org_id = spq.org_id
) ;
END Update_Record;
PROCEDURE Delete_Record
(
x_srp_plan_assign_id NUMBER ,
x_quota_id NUMBER ,
x_start_period_id NUMBER ,
x_end_period_id NUMBER ,
x_start_date DATE := NULL ,
x_end_date DATE := NULL )
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS ;
SELECT srp_period_quota_id,
org_id
FROM cn_srp_period_quotas_all
WHERE quota_id = l_quota_id
AND srp_plan_assign_id = NVL(x_srp_plan_assign_id, srp_plan_assign_id)
AND EXISTS
(SELECT 1
FROM cn_period_statuses p
WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
AND TRUNC(p.end_date) <= TRUNC(NVL(x_end_date ,p.end_date))
AND cn_srp_period_quotas_all.period_id = p.period_id
AND p.period_status IN ('O', 'F')
AND cn_srp_period_quotas_all.org_id = p.org_id
);
SELECT salesrep_id,
period_id ,
credit_type_id ,
role_id ,
balance2_dtd ,
balance3_ctd ,
srp_period_id
FROM cn_srp_periods_all
WHERE quota_id = x_quota_id
AND org_id = l_org_id
AND EXISTS
(SELECT 1
FROM cn_period_statuses p
WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
AND TRUNC(p.end_date) <= TRUNC(NVL(x_end_date ,p.end_date))
AND cn_srp_periods_all.period_id = p.period_id
AND p.period_status IN ('O', 'F')
AND cn_srp_periods_all.org_id = p.org_id
)
AND (balance2_dtd <> 0
OR balance3_ctd <> 0)
ORDER BY salesrep_id,
credit_type_id ,
role_id ,
period_id;
SELECT ccf.number_dim,
cq.org_id
FROM cn_quotas_all cq,
cn_calc_formulas_all ccf
WHERE cq.quota_id = l_quota_id
AND cq.calc_formula_id = ccf.calc_formula_id;
SELECT quota_id
FROM cn_srp_quota_assigns_all
WHERE srp_plan_assign_id = x_srp_plan_assign_id;
populate_srp_period_quotas_ext('DELETE',srp_period_quota_id_rec.srp_period_quota_id, srp_period_quota_id_rec.org_id);
populate_srp_period_quotas_ext('DELETE',srp_period_quota_id_rec.srp_period_quota_id, srp_period_quota_id_rec.org_id);
DELETE
FROM cn_srp_period_quotas_all
WHERE srp_plan_assign_id = x_srp_plan_assign_id ;
SELECT org_id
INTO l_org_id
FROM cn_srp_plan_assigns
WHERE srp_plan_assign_id = x_srp_plan_assign_id;
DELETE
FROM cn_srp_period_quotas_all
WHERE srp_plan_assign_id = x_srp_plan_assign_id
AND EXISTS
(SELECT 1
FROM cn_period_statuses p
WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
-- following line changed for bug 4424669, 4885986
AND TRUNC(p.end_date) <= NVL(l_end_date_pd, p.end_date)
--AND trunc(p.end_date) <= trunc(cn_end_date_period(nvl(x_end_date ,p.end_date), p.org_id))
AND p.period_status IN ('O', 'F')
AND cn_srp_period_quotas_all.period_id = p.period_id
AND cn_srp_period_quotas_all.org_id = p.org_id
AND ( ( p.start_date <> p.end_date)
OR (p.start_date = p.end_date AND p.start_date > TRUNC(x_start_date)) ) -- Fix for bug 13583329
);
DELETE
FROM cn_srp_period_quotas_all
WHERE srp_plan_assign_id = x_srp_plan_assign_id
AND quota_id = x_quota_id
AND NVL(x_start_period_id, period_id) <= period_id -- Bug 3848446, Fixed by Jagpreet Singh.
;
cn_srp_periods_pvt.Update_Delta_Srp_Pds_No_Sync (p_api_version => 1.0, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_del_srp_prd_rec => l_srp_prd_rec, x_loading_status => l_loading_status);
DELETE
FROM cn_srp_period_quotas_all
WHERE quota_id = x_quota_id
AND EXISTS
(SELECT 1
FROM cn_period_statuses p
WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
AND TRUNC(p.end_date) <= TRUNC(NVL(x_end_date ,p.end_date))
AND cn_srp_period_quotas_all.period_id = p.period_id
AND p.period_status IN ('O', 'F')
AND cn_srp_period_quotas_all.org_id = p.org_id
AND ( ( p.start_date <> p.end_date)
OR (p.start_date = p.end_date AND p.start_date > TRUNC(x_start_date)) ) -- Fix for bug 13583329
);
END Delete_Record;
x_last_update_date DATE ,
x_last_updated_by NUMBER ,
x_last_update_login NUMBER )
IS
BEGIN
IF x_operation = 'INSERT' THEN
-- insert the record for the given quota and salesrep
Insert_Record( x_srp_plan_assign_id => x_srp_plan_assign_id ,x_quota_id => x_quota_id ,x_start_period_id => NULL ,x_end_period_id => NULL ,x_start_date => NULL ,x_end_date => NULL );
ELSIF x_operation = 'UPDATE' THEN
-- Update record
Update_Record ( x_period_target_unit_code => x_period_target_unit_code ,
x_srp_period_quota_id => x_srp_period_quota_id ,
x_srp_quota_assign_id => x_srp_quota_assign_id ,
x_period_id => x_period_id ,
x_target_amount => x_target_amount ,
x_period_payment => x_period_payment ,
x_performance_goal => x_performance_goal ,
x_quarter_num => x_quarter_num ,
x_period_year => x_period_year ,
x_quota_type_code => x_quota_type_code ,
x_quota_id => x_quota_id ,
x_salesrep_id => x_salesrep_id ,
x_end_date => x_end_date ,
x_commission_payed_ptd => x_commission_payed_ptd ,
x_last_update_date => x_last_update_date ,
x_last_updated_by => x_last_updated_by ,
x_last_update_login => x_last_update_login);
ELSIF X_Operation = 'DELETE' THEN
-- Delete Record
Delete_Record( x_srp_plan_assign_id => x_srp_plan_assign_id ,x_quota_id => x_quota_id ,x_start_period_id => NULL ,x_end_period_id => NULL ,x_start_date => NULL ,x_end_date => NULL );
SELECT ccf.number_dim
FROM cn_srp_period_quotas_all cspq,
cn_quotas_all cq ,
cn_calc_formulas_all ccf
WHERE cspq.srp_period_quota_id = x_srp_period_quota_id
AND cq.quota_id = cspq.quota_id
AND cq.calc_formula_id = ccf.calc_formula_id ;
IF x_operation = 'INSERT' THEN
-- get number dim if necessary
IF x_number_dim is NULL THEN
l_number_dim := 0;
INSERT
INTO cn_srp_period_quotas_ext_all
(
srp_period_quota_ext_id,
srp_period_quota_id ,
input_sequence ,
created_by ,
creation_date ,
last_update_login ,
last_update_date ,
last_updated_by ,
org_id
)
SELECT cn_srp_period_quotas_ext_s.nextval,
x_srp_period_quota_id ,
i_seq ,
l_user_id ,
sysdate ,
l_login_id ,
sysdate ,
l_user_id ,
x_org_id
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM cn_srp_period_quotas_ext_all
WHERE srp_period_quota_id = x_srp_period_quota_id
AND input_sequence = i_seq
);
ELSIF x_operation = 'DELETE' THEN
DELETE
FROM cn_srp_period_quotas_ext_all
WHERE srp_period_quota_id = x_srp_period_quota_id;
PROCEDURE select_summary
(
x_srp_quota_assign_id NUMBER ,
x_total IN OUT NOCOPY NUMBER ,
x_total_rtot_db IN OUT NOCOPY NUMBER)
IS
BEGIN
SELECT NVL(SUM(target_amount),0)
INTO x_total
FROM cn_srp_period_quotas_all
WHERE srp_quota_assign_id = x_srp_quota_assign_id ;
END select_summary;
SELECT spq.srp_period_quota_id
FROM cn_srp_period_quotas_v spq,
cn_period_statuses cp ,
cn_cal_per_int_types_all cpit ,
cn_quotas_all cq
WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
AND spq.quota_id = cq.quota_id
AND spq.period_id = cp.period_id
AND cp.period_status IN ('O', 'F')
AND cq.org_id = cp.org_id
AND cp.period_id = cpit.cal_period_id
AND cp.org_id = cpit.org_id
AND cpit.interval_type_id = cq.interval_type_id
AND cpit.interval_number = l_interval_number
AND spq.period_year = l_period_year
ORDER BY spq.period_id ;
SELECT COUNT(spq.srp_period_quota_id) interval_count,
cpit.interval_number interval_number ,
spq.period_year period_year
FROM cn_srp_period_quotas_v spq,
cn_period_statuses cp ,
cn_cal_per_int_types_all cpit ,
cn_quotas_all cq
WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
AND spq.quota_id = cq.quota_id
AND spq.period_id = cp.period_id
AND cp.period_status IN ('O', 'F')
AND cq.org_id = cp.org_id
AND cp.period_id = cpit.cal_period_id
AND cp.org_id = cpit.org_id
AND cpit.interval_type_id = cq.interval_type_id
GROUP BY cpit.interval_number,
spq.period_year ;
SELECT c.extended_precision
INTO g_ext_precision
FROM cn_repositories r,
gl_sets_of_books b ,
fnd_currencies c ,
cn_srp_quota_assigns sqa
WHERE r.org_id = sqa.org_id
AND r.set_of_books_id = b.set_of_books_id
AND b.currency_code = c.currency_code
AND sqa.srp_quota_assign_id = x_srp_quota_assign_id;
SELECT NVL(qa.target,0) ,
NVL(qa.payment_amount, 0) ,
NVL(qa.performance_goal,0) ,
qa.period_target_dist_rule_code ,
cn_chk_plan_element_pkg.get_interval_name(q.interval_type_id, q.org_id) period_type_code
INTO l_quota_target ,
l_quota_payment ,
l_performance_goal ,
l_dist_rule_code ,
l_period_type_code
FROM cn_srp_quota_assigns_all qa,
cn_quotas_all q
WHERE qa.srp_quota_assign_id = x_srp_quota_assign_id
AND q.quota_id = qa.quota_id
AND qa.period_target_dist_rule_code <> 'USER_DEFINED' ;
UPDATE cn_srp_period_quotas_all
SET target_amount = ROUND(NVL(l_period_target, 0), g_ext_precision) ,
itd_target = ROUND(NVL(l_running_total_target,0), g_ext_precision) ,
period_payment = ROUND(NVL(l_period_payment,0), g_ext_precision) ,
itd_payment = ROUND(NVL(l_running_total_payment,0), g_ext_precision) ,
performance_goal_ptd = ROUND(NVL(l_period_performance_goal,0), g_ext_precision),
performance_goal_itd = ROUND(NVL(l_running_performance_goal,0),g_ext_precision)
WHERE srp_period_quota_id = pq_rec.srp_period_quota_id ;
cn_srp_period_quotas_pkg.delete_record ( x_srp_plan_assign_id => x_srp_plan_assign_id ,x_quota_id => x_quota_id ,x_start_period_id => NULL ,x_end_period_id => NULL);
cn_srp_period_quotas_pkg.insert_record ( x_srp_plan_assign_id => x_srp_plan_assign_id ,x_quota_id => x_quota_id ,x_start_period_id => NULL ,x_end_period_id => NULL);