[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 = l_input_achieved_itd,
output_achieved_itd = l_output_achieved_itd,
perf_achieved_itd = l_perf_achieved_itd,
commission_payed_itd = l_commission_payed_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,
-- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
itd_target = l_itd_target,
itd_payment = l_itd_payment,
performance_goal_itd = 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 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 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_acc_period_statuses_v r1
where r1.end_date >= Nvl(x_start_date, pa.start_date)
AND r1.org_id = pa.org_id)
;
('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 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_acc_period_statuses_v r1
where r1.end_date > pa.start_date
AND r1.org_id = pa.org_id);
('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_acc_period_statuses_v r1
where r1.end_date > x_start_date
AND r1.org_id = pa.org_id)
;
('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);
-- Deleted plan assignment
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);
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);
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 = fnd_api.g_miss_num 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);