1 PACKAGE BODY CN_SRP_PERIOD_QUOTAS_PKG AS
2 /* $Header: cnsrpqob.pls 120.14.12020000.2 2012/07/25 09:34:19 scannane ship $ */
3 --Date Name Description
4 ---------------------------------------------------------------------------+
5 -- 25-JUL-95 P Cook Split the inserts into 3 statements for performance
6 -- Added delete stmnt for source quota type changes
7 -- Fixed locking error by removing period_id comparison.
8 -- Modified update to handle quarters and years
9 -- 22-AUG-95 P Cook Removed maintenance of cn_srp_per_quota_rc. This is
10 -- now done in other routines
11 -- 20-NOV-95 P Cook Added who columns.
12 -- 07-MAR-96 P Cook Bug:346506. distribute_target modified to distribute
13 -- according to the quotas period_type_code(Interval).
14 -- 10-JUN-99 S Kumar Modified extensively like changing from start period
15 -- ids start date and end period id to end date.
16 -- 1. insert_record procedure is modified to pass the
17 -- start date and end date, still start period id and
18 -- end period id exists but always null value
19 -- 25-AUG-99 Added the performance_goal column in the
20 -- srp_period_quotas and itd_performance_goal.
21 --
22 -- 22-Apr-03 rarajara Fixed the bug #2874991
23 -- 14-Sep-04 jasingh Fixed the Bug# 3848446
24 FUNCTION cn_end_date_period
25 (
26 p_end_date DATE,
27 p_org_id NUMBER)
28 RETURN cn_acc_period_statuses_v.end_date%TYPE
29 IS
30 l_next_end_date cn_acc_period_statuses_v.end_date%TYPE;
31 BEGIN
32 SELECT end_date
33 INTO l_next_end_date
34 FROM cn_acc_period_statuses_v
35 WHERE p_end_date BETWEEN start_date AND end_date
36 AND org_id = p_org_id;
37 RETURN l_next_end_date;
38 EXCEPTION
39 WHEN no_data_found THEN
40 RETURN NULL;
41 END cn_end_date_period;
42 --bugfix #2874991 starts
43 -- Name
44 --
45 -- Purpose
46 -- Insert period quota for each rep using the quota in a period
47 --
48 -- Notes
49 -- This method is called whenever a quota is made
50 -- active for a new period
51 PROCEDURE populate_itd_values
52 (
53 x_start_srp_period_quota_id NUMBER)
54 IS
55 l_previous_period_id NUMBER :=0;
56 l_end_period_id NUMBER :=0;
57 l_interval_type_id NUMBER :=0;
58 l_start_period_id NUMBER :=0;
59 l_salesrep_id NUMBER :=0;
60 l_srp_plan_assign_id NUMBER :=0;
61 l_quota_id NUMBER :=0;
62 l_org_id NUMBER :=0;
63 l_input_achieved_itd NUMBER :=0;
64 l_output_achieved_itd NUMBER :=0;
65 l_perf_achieved_itd NUMBER :=0;
66 l_commission_payed_itd NUMBER :=0;
67 l_advance_recovered_itd NUMBER :=0;
68 l_advance_to_rec_itd NUMBER :=0;
69 l_recovery_amount_itd NUMBER :=0;
70 l_comm_pend_itd NUMBER :=0;
71 --clku, related to bug 2874991
72 l_itd_target NUMBER := 0;
73 l_itd_payment NUMBER := 0;
74 l_performance_goal_itd NUMBER := 0;
75 CURSOR max_prev_period_csr(p_interval_type_id NUMBER, p_start_period_id NUMBER, p_org_id NUMBER)
76 IS
77 SELECT MAX(cal_period_id) max_cal_period_id
78 FROM cn_cal_per_int_types_all
79 WHERE interval_type_id = p_interval_type_id
80 AND org_id = p_org_id
81 AND cal_period_id < p_start_period_id
82 AND interval_number =
83 (SELECT interval_number
84 FROM cn_cal_per_int_types_all
85 WHERE cal_period_id = p_start_period_id
86 AND org_id = p_org_id
87 AND interval_type_id = p_interval_type_id
88 );
89 CURSOR max_period_csr(p_interval_type_id NUMBER, p_start_period_id NUMBER, p_org_id NUMBER)
90 IS
91 SELECT cal_period_id
92 FROM cn_cal_per_int_types_all
93 WHERE interval_type_id = p_interval_type_id
94 AND org_id = p_org_id
95 AND cal_period_id >= p_start_period_id
96 AND interval_number =
97 (SELECT interval_number
98 FROM cn_cal_per_int_types_all
99 WHERE cal_period_id = p_start_period_id
100 AND org_id = p_org_id
101 AND interval_type_id = p_interval_type_id
102 );
103 BEGIN
104 SELECT period_id ,
105 salesrep_id ,
106 srp_plan_assign_id,
107 quota_id ,
108 org_id
109 INTO l_start_period_id,
110 l_salesrep_id ,
111 l_srp_plan_assign_id ,
112 l_quota_id ,
113 l_org_id
114 FROM cn_srp_period_quotas_all
115 WHERE srp_period_quota_id = x_start_srp_period_quota_id;
116 SELECT interval_type_id
117 INTO l_interval_type_id
118 FROM cn_quotas_all
119 WHERE quota_id = l_quota_id;
120
121 OPEN max_prev_period_csr(l_interval_type_id,l_start_period_id,l_org_id);
122 FETCH max_prev_period_csr INTO l_previous_period_id ;
123 IF max_prev_period_csr%NOTFOUND THEN
124 raise NO_DATA_FOUND;
125 END IF;
126 CLOSE max_prev_period_csr;
127 IF l_previous_period_id > 0 THEN
128 SELECT NVL(spq.input_achieved_itd,0),
129 NVL(spq.output_achieved_itd,0) ,
130 NVL(spq.perf_achieved_itd,0) ,
131 NVL(spq.commission_payed_itd,0) ,
132 NVL(spq.advance_recovered_itd,0) ,
133 NVL(spq.advance_to_rec_itd,0) ,
134 NVL(spq.recovery_amount_itd,0) ,
135 NVL(spq.comm_pend_itd,0) ,
136 -- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
137 NVL(spq.itd_target,0) ,
138 NVL(spq.itd_payment,0),
139 NVL(spq.performance_goal_itd,0)
140 INTO l_input_achieved_itd,
141 l_output_achieved_itd ,
142 l_perf_achieved_itd ,
143 l_commission_payed_itd ,
144 l_advance_recovered_itd ,
145 l_advance_to_rec_itd ,
146 l_recovery_amount_itd ,
147 l_comm_pend_itd ,
148 -- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
149 l_itd_target ,
150 l_itd_payment,
151 l_performance_goal_itd
152 FROM cn_srp_period_quotas_all spq
153 WHERE salesrep_id = l_salesrep_id
154 AND srp_plan_assign_id = l_srp_plan_assign_id
155 AND quota_id = l_quota_id
156 AND period_id = l_previous_period_id;
157
158 FOR i_period_id IN max_period_csr(l_interval_type_id,l_start_period_id,l_org_id)
159 LOOP
160 UPDATE cn_srp_period_quotas_all
161 SET input_achieved_itd = nvl(input_achieved_ptd,0) + l_input_achieved_itd ,
162 output_achieved_itd = nvl(output_achieved_ptd,0) + l_output_achieved_itd ,
163 perf_achieved_itd = nvl(perf_achieved_ptd,0) + l_perf_achieved_itd ,
164 commission_payed_itd = nvl(commission_payed_ptd,0) + l_commission_payed_itd ,
165 advance_recovered_itd = nvl(advance_recovered_ptd,0) + l_advance_recovered_itd,
166 advance_to_rec_itd = nvl(advance_to_rec_ptd,0) + l_advance_to_rec_itd ,
167 recovery_amount_itd = nvl(recovery_amount_ptd,0) + l_recovery_amount_itd ,
168 comm_pend_itd = nvl(comm_pend_ptd,0) + l_comm_pend_itd ,
169 -- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
170 itd_target = nvl(target_amount,0) + l_itd_target ,
171 itd_payment = nvl(period_payment,0) + l_itd_payment,
172 performance_goal_itd = nvl(performance_goal_ptd,0) + l_performance_goal_itd
173 WHERE salesrep_id = l_salesrep_id
174 AND srp_plan_assign_id = l_srp_plan_assign_id
175 AND quota_id = l_quota_id
176 AND period_id = i_period_id.cal_period_id;
177
178 SELECT NVL(spq.input_achieved_itd,0),
179 NVL(spq.output_achieved_itd,0) ,
180 NVL(spq.perf_achieved_itd,0) ,
181 NVL(spq.commission_payed_itd,0) ,
182 NVL(spq.advance_recovered_itd,0) ,
183 NVL(spq.advance_to_rec_itd,0) ,
184 NVL(spq.recovery_amount_itd,0) ,
185 NVL(spq.comm_pend_itd,0) ,
186 -- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
187 NVL(spq.itd_target,0) ,
188 NVL(spq.itd_payment,0),
189 NVL(spq.performance_goal_itd,0)
190 INTO l_input_achieved_itd,
191 l_output_achieved_itd ,
192 l_perf_achieved_itd ,
193 l_commission_payed_itd ,
194 l_advance_recovered_itd ,
195 l_advance_to_rec_itd ,
196 l_recovery_amount_itd ,
197 l_comm_pend_itd ,
198 -- clku, we need to take care of itd_target, itd_payment, performance_goal_itd also
199 l_itd_target ,
200 l_itd_payment,
201 l_performance_goal_itd
202 FROM cn_srp_period_quotas_all spq
203 WHERE salesrep_id = l_salesrep_id
204 AND srp_plan_assign_id = l_srp_plan_assign_id
205 AND quota_id = l_quota_id
206 AND period_id = i_period_id.cal_period_id;
207 END LOOP;
208 END IF;
209 EXCEPTION
210 WHEN NO_DATA_FOUND THEN
211 NULL;
212 WHEN OTHERS THEN
213 NULL;
214 END populate_itd_values;
215 --bugfix #2874991 ends
216 --bugfix #2874991 starts
217 -- Name
218 --
219 -- Purpose
220 -- Insert period quota for each rep using the quota in a period
221 --
222 -- Notes
223 -- This method is called whenever a quota is made
224 -- active for a new period
225 PROCEDURE sync_ITD_values
226 (
227 x_quota_id NUMBER)
228 IS
229 CURSOR srp_period_quotas(l_srp_quota_assign_id NUMBER, l_interval_number NUMBER, l_period_year NUMBER)
230 IS
231 SELECT spq.srp_period_quota_id srp_period_quota_id ,
232 NVL(spq.input_achieved_ptd,0) input_achieved_ptd ,
233 NVL(spq.output_achieved_ptd,0) output_achieved_ptd ,
234 NVL(spq.perf_achieved_ptd,0) perf_achieved_ptd ,
235 NVL(spq.commission_payed_ptd,0) commission_payed_ptd ,
236 NVL(spq.advance_recovered_ptd,0) advance_recovered_ptd,
237 NVL(spq.advance_to_rec_ptd,0) advance_to_rec_ptd ,
238 NVL(spq.recovery_amount_ptd,0) recovery_amount_ptd ,
239 NVL(spq.comm_pend_ptd,0) comm_pend_ptd ,
240 NVL(spq.target_amount,0) target_amount ,
241 NVL(spq.period_payment,0) period_payment ,
242 NVL(spq.performance_goal_ptd,0) performance_goal_ptd
243 FROM cn_srp_period_quotas_all spq,
244 cn_period_statuses_all cp ,
245 cn_cal_per_int_types_all cpit ,
246 cn_quotas_all cq
247 WHERE spq.quota_id = x_quota_id
248 AND spq.quota_id = cq.quota_id
249 AND spq.period_id = cp.period_id
250 AND spq.org_id = cp.org_id
251 AND spq.period_id = cpit.cal_period_id
252 AND spq.org_id = cpit.org_id
253 AND spq.srp_quota_assign_id = l_srp_quota_assign_id
254 AND cpit.interval_type_id = cq.interval_type_id
255 AND cpit.interval_number = l_interval_number
256 AND cp.period_year = l_period_year
257 ORDER BY spq.period_id;
258
259 pq_rec srp_period_quotas%ROWTYPE;
260 -- Get the period quotas that belong to the quota assignment for each
261 -- interval
262 CURSOR interval_counts
263 IS
264 SELECT p.srp_quota_assign_id srp_quota_assign_id,
265 COUNT(p.srp_period_quota_id) interval_count ,
266 cpit.interval_number interval_number ,
267 p.period_year period_year
268 FROM cn_srp_period_quotas_v p,
269 cn_period_statuses cp ,
270 cn_cal_per_int_types_all cpit ,
271 cn_quotas_all cq
272 WHERE p.quota_id = x_quota_id
273 AND p.quota_id = cq.quota_id
274 AND p.period_id = cp.period_id
275 AND cp.period_status IN ('O', 'F')
276 AND cq.org_id = cp.org_id
277 AND cp.period_id = cpit.cal_period_id
278 AND cp.org_id = cpit.org_id
279 AND cpit.interval_type_id = cq.interval_type_id
280 GROUP BY p.srp_quota_assign_id,
281 cpit.interval_number ,
282 p.period_year ;
283
284 interval_rec interval_counts%ROWTYPE;
285 l_target_total NUMBER := 0;
286 l_payment_total NUMBER := 0;
287 l_performance_goal_total NUMBER := 0;
288 l_input_achieved_total NUMBER :=0;
289 l_output_achieved_total NUMBER :=0;
290 l_perf_achieved_total NUMBER :=0;
291 l_commission_payed_total NUMBER :=0;
292 l_advance_recovered_total NUMBER :=0;
293 l_advance_to_rec_total NUMBER :=0;
294 l_recovery_amount_total NUMBER :=0;
295 l_comm_pend_total NUMBER :=0;
296 BEGIN
297 FOR interval_rec IN interval_counts
298 LOOP
299 -- Initialize for each interval
300 l_target_total := 0;
301 l_payment_total := 0;
302 l_performance_goal_total := 0;
303 l_input_achieved_total :=0;
304 l_output_achieved_total :=0;
305 l_perf_achieved_total :=0;
306 l_commission_payed_total :=0;
307 l_advance_recovered_total :=0;
308 l_advance_to_rec_total :=0;
309 l_recovery_amount_total :=0;
310 l_comm_pend_total :=0;
311 -- Now that we know the counts per quarter/year we can divide the
312 -- quota target correctly for each quarter and set the period quota
313 -- target.
314 FOR pq_rec IN srp_period_quotas ( l_srp_quota_assign_id => interval_rec.srp_quota_assign_id ,l_interval_number => interval_rec.interval_number ,l_period_year => interval_rec.period_year)
315 LOOP
316 l_target_total := l_target_total + pq_rec.target_amount;
317 l_payment_total := l_payment_total + pq_rec.period_payment;
318 l_performance_goal_total := l_performance_goal_total + pq_rec.performance_goal_ptd;
319 l_input_achieved_total := l_input_achieved_total + pq_rec.input_achieved_ptd;
320 l_output_achieved_total := l_output_achieved_total + pq_rec.output_achieved_ptd;
321 l_perf_achieved_total := l_perf_achieved_total + pq_rec.perf_achieved_ptd;
322 l_commission_payed_total := l_commission_payed_total + pq_rec.commission_payed_ptd;
323 l_advance_recovered_total := l_advance_recovered_total + pq_rec.advance_recovered_ptd;
324 l_advance_to_rec_total := l_advance_to_rec_total + pq_rec.advance_to_rec_ptd;
325 l_recovery_amount_total := l_recovery_amount_total + pq_rec.recovery_amount_ptd;
326 l_comm_pend_total := l_comm_pend_total + pq_rec.comm_pend_ptd;
327 UPDATE cn_srp_period_quotas_all
328 SET itd_target = NVL(l_target_total,0) ,
329 itd_payment = NVL(l_payment_total,0) ,
330 performance_goal_itd = NVL(l_performance_goal_total,0) ,
331 input_achieved_itd = NVL(l_input_achieved_total, 0) ,
332 output_achieved_itd = NVL(l_output_achieved_total, 0) ,
333 perf_achieved_itd = NVL(l_perf_achieved_total, 0) ,
334 commission_payed_itd = NVL(l_commission_payed_total, 0) ,
335 advance_recovered_itd = NVL(l_advance_recovered_total, 0),
336 advance_to_rec_itd = NVL(l_advance_to_rec_total, 0) ,
337 recovery_amount_itd = NVL(l_recovery_amount_total, 0) ,
338 comm_pend_itd = NVL(l_comm_pend_total, 0)
339 WHERE srp_period_quota_id = pq_rec.srp_period_quota_id ;
340 END LOOP;
341 END LOOP;
342 EXCEPTION
343 WHEN NO_DATA_FOUND THEN
344 NULL;
345 END sync_ITD_values;
346 -- Name
347 --
348 -- Purpose
349 -- Insert period quota for each rep using the quota in a period
350 --
351 -- Notes Parameters
352 -- o Called once for each new srp plan assignment. x_srp_plan_assign_id
353 -- o Called one insert of new quota assignment -+
354 -- once for each srp plan assignment that x_srp_plan_assign_id
355 -- references the comp plan id on the new comp x_quota_id
356 -- plan quota assignment
357 -- The quota_id restriction ensures only the newly
358 -- assigned quota is inserted.
359 -- o Called on update of srp plan assign period range x_srp_plan_assign_id
360 -- The not exists subselect is specifically for this
361 -- situation. refer to delete_row procedure for more info
362 --
363 -- |-----Plan Assignment Active range---|
364 -- |--------- Comp Plan active Range--------|
365 -- |-----|----------Quota Active Range--|-------|
366 -- o All quota types have cn_srp_period_quotas althouhg revenue types
367 -- do not display or allow the user to maintain a target value
368 -- New Comments Added on 10/JUN/99
369 -- Start period id and End period is not used any more in the sales comp
370 -- Instead we pass start Date and End Date
371 -- We are not removing the column, we assign default null to
372 -- start_period_id and end period_id
373 -- o Called once for each new srp plan assignment. x_srp_plan_assign_id
374 -- o Called one insert of new quota assignment x_srp_plan_assign_id,
375 -- quota_id
376 -- o calling Place 1.cn_srp_quota_assigns_pkg.insert record
377 ---------------------------------------------------------------------------+
378 -- PROCEDURE INSERT_RECORD
379 -- Description:
380 -- CASE 1: Quota period has changed like end date changed from NOT NULL to
381 -- null ( means extending the quota active range ) or
382 -- new end date is greater than old end date
383 -- called from cn_quotas_pkg
384 -- Value passed x_quota_id, x_start_date, x_end_date
385 ---------------------------------------------------------------------------+
386 PROCEDURE Insert_Record
387 (
388 x_srp_plan_assign_id NUMBER ,
389 x_quota_id NUMBER ,
390 x_start_period_id NUMBER ,
391 x_end_period_id NUMBER ,
392 x_start_date DATE := NULL ,
393 x_end_date DATE := NULL )
394 IS
395 l_user_id NUMBER(15);
396 l_resp_id NUMBER(15);
397 l_login_id NUMBER(15);
398 x_itd_flag_checked VARCHAR2(1);
399 -- Get the itd_flag for each quota
400 CURSOR ytd_flag
401 IS
402 SELECT q.quota_id quota_id,
403 q.org_id
404 FROM cn_srp_quota_assigns_all qa ,
405 cn_quotas_all q
406 WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
407 -- do not need itd and formula id anymore, bug 2462767,AND q.calc_formula_id = cf.calc_formula_id(+)
408 AND qa.quota_id = q.quota_id;
409 -- clku bug 2845024, performance fix, avoid full table scan by avoiding
410 -- is null condition of the cursor.
411 CURSOR srp_period_quota_ids1(l_quota_id NUMBER, l_srp_plan_assign_id NUMBER)
412 IS
413 SELECT srp_period_quota_id
414 FROM cn_srp_period_quotas_all
415 WHERE quota_id = l_quota_id
416 AND srp_plan_assign_id = l_srp_plan_assign_id ;
417 CURSOR srp_period_quota_ids2(l_quota_id NUMBER)
418 IS
419 SELECT srp_period_quota_id
420 FROM cn_srp_period_quotas_all
421 WHERE quota_id = l_quota_id;
422 --bugfix for #2874991 starts
423 CURSOR start_period_quota_id_csr1(p_srp_plan_assign_id NUMBER,p_quota_id NUMBER,x_start_period_id NUMBER)
424 IS
425 SELECT srp_period_quota_id
426 FROM cn_srp_period_quotas_all
427 WHERE srp_plan_assign_id = p_srp_plan_assign_id
428 AND quota_id = p_quota_id
429 AND period_id = x_start_period_id;
430 CURSOR start_period_quota_id_csr2(p_quota_id NUMBER,x_start_period_id NUMBER)
431 IS
432 SELECT MAX(srp_period_quota_id)
433 FROM cn_srp_period_quotas_all
434 WHERE quota_id = p_quota_id
435 AND period_id = x_start_period_id
436 GROUP BY srp_plan_assign_id;
437
438 l_srp_start_period_quota_id NUMBER :=0;
439 l_count NUMBER :=0;
440 l_value NUMBER;
441 TYPE l_start_period_quota_id_type
442 IS
443 TABLE OF NUMBER INDEX BY BINARY_INTEGER;
444 l_start_period_quota_id_tbl l_start_period_quota_id_type;
445 --bugfix for #2874991 ends
446 itd_p_rec ytd_flag%ROWTYPE;
447 --clku bug 2845024
448 srp_period_quota_id_rec1 srp_period_quota_ids1%ROWTYPE;
449 srp_period_quota_id_rec2 srp_period_quota_ids2%ROWTYPE;
450 -- get number_dim
451 CURSOR get_number_dim(l_quota_id NUMBER)
452 IS
453 SELECT ccf.number_dim
454 FROM cn_quotas_all cq,
455 cn_calc_formulas_all ccf
456 WHERE cq.quota_id = l_quota_id
457 AND cq.calc_formula_id = ccf.calc_formula_id;
458
459 l_number_dim NUMBER;
460 BEGIN
461 l_user_id := fnd_global.user_id;
462 l_resp_id := fnd_global.resp_id;
463 l_login_id := fnd_global.login_id;
464 IF x_srp_plan_assign_id IS NOT NULL AND x_quota_id IS NULL THEN
465 -- A new plan is assigned to a salesrep
466 -- case 1: callled from cn_srp_quota_assigns_pkg
467 FOR itd_p_rec IN ytd_flag
468 LOOP
469 -- enhancement, clku, 2431086,we do not check if the PE is ITD or not.
470 --IF (itd_p_rec.itd_flag = 'Y') THEN
471 INSERT
472 INTO cn_srp_period_quotas_all
473 (
474 srp_period_quota_id ,
475 srp_plan_assign_id ,
476 srp_quota_assign_id ,
477 salesrep_id ,
478 period_id ,
479 quota_id ,
480 target_amount ,
481 itd_target ,
482 period_payment ,
483 itd_payment ,
484 performance_goal_ptd ,
485 performance_goal_itd ,
486 commission_payed_ptd ,
487 creation_date ,
488 created_by ,
489 last_update_date ,
490 last_updated_by ,
491 last_update_login ,
492 org_id
493 )
494 SELECT cn_srp_period_quotas_s.nextval ,
495 qa.srp_plan_assign_id ,
496 qa.srp_quota_assign_id ,
497 pa.salesrep_id ,
498 p.period_id ,
499 qa.quota_id ,
500 NVL(pq.period_target,0) ,
501 pq.itd_target ,
502 pq.period_payment ,
503 pq.itd_payment ,
504 pq.performance_goal ,
505 pq.performance_goal_itd ,
506 0 ,
507 sysdate ,
508 l_user_id ,
509 sysdate ,
510 l_user_id ,
511 l_login_id ,
512 qa.org_id
513 FROM cn_srp_quota_assigns_all qa ,
514 cn_period_quotas_all pq ,
515 cn_srp_plan_assigns_all pa ,
516 cn_period_statuses p
517 WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
518 AND pa.srp_plan_assign_id = x_srp_plan_assign_id
519 AND pa.srp_plan_assign_id = qa.srp_plan_assign_id --bugfix3633222
520 AND qa.quota_id = pq.quota_id
521 AND pq.period_id = p.period_id
522 AND p.period_status IN ('O', 'F')
523 AND pq.org_id = p.org_id
524 AND QA.ORG_ID = PQ.ORG_ID --bug fix 7381426
525 AND pq.quota_id = itd_p_rec.quota_id
526 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)))
527 AND NOT EXISTS
528 (SELECT 'srp_period_quota already exists'
529 FROM cn_srp_period_quotas_all spq
530 WHERE spq.srp_quota_assign_id = qa.srp_quota_assign_id
531 AND spq.period_id = p.period_id
532 )
533 -- bug 2460926, clku, check if all the open period ends before the specified start_date
534 -- 2479359, Nvl(x_start_date, pa.start_date) added to deal with NULL x_start_date
535 AND EXISTS
536 (SELECT r1.end_date
537 FROM CN_PERIOD_STATUSES_ALL R1
538 WHERE r1.end_date >= NVL(x_start_date, pa.start_date)
539 AND (R1.PERIOD_SET_ID, R1.PERIOD_TYPE_ID ) IN
540 (SELECT CR.PERIOD_SET_ID,
541 CR.PERIOD_TYPE_ID
542 FROM CN_REPOSITORIES_ALL CR
543 WHERE cr.org_id= r1.org_id
544 )
545 AND R1.PERIOD_STATUS IN ('O', 'F')
546 AND r1.org_id = pa.org_id
547 ) ;
548 --added for bugfix#2874991
549 l_value := NULL;
550 OPEN start_period_quota_id_csr1(x_srp_plan_assign_id,itd_p_rec.quota_id,x_start_period_id);
551 FETCH start_period_quota_id_csr1 INTO l_value;
552 IF l_value IS NOT NULL THEN
553 l_count := l_count+1;
554 l_start_period_quota_id_tbl(l_count) := l_value;
555 END IF;
556 CLOSE start_period_quota_id_csr1;
557 --added for bugfix#2874991 ends here
558 -- get number_dim
559 l_number_dim := 0;
560 OPEN get_number_dim(itd_p_rec.quota_id);
561 FETCH get_number_dim INTO l_number_dim;
562
563 CLOSE get_number_dim;
564 --clku bug 2845024
565 IF l_number_dim > 1 THEN
566 FOR srp_period_quota_id_rec1 IN srp_period_quota_ids1(itd_p_rec.quota_id, x_srp_plan_assign_id)
567 LOOP
568 populate_srp_period_quotas_ext ('INSERT',srp_period_quota_id_rec1.srp_period_quota_id, itd_p_rec.org_id, l_number_dim);
569 END LOOP;
570 END IF;
571 END LOOP;
572 --bugfix #2874991
573 IF l_start_period_quota_id_tbl.count > 0 THEN
574 FOR counter IN 1..l_start_period_quota_id_tbl.count
575 LOOP
576 populate_itd_values(l_start_period_quota_id_tbl(counter));
577 END LOOP;
578 END IF;
579 --added for bugfix#2874991 ends here
580 ELSIF (x_srp_plan_assign_id IS NOT NULL AND x_quota_id IS NOT NULL) THEN
581 -- A new quota has been assigned to a compensation plan
582 -- Check whether itd_flag for this quota is checked
583 -- case 1: called from cn_srp_quota_assigns_pkg.
584 -- enhancement, clku, 2431086, we do not check if the PE is ITD or not.
585 --IF x_itd_flag_checked = 'Y' THEN
586 INSERT
587 INTO cn_srp_period_quotas_all
588 (
589 srp_period_quota_id ,
590 srp_plan_assign_id ,
591 srp_quota_assign_id ,
592 salesrep_id ,
593 period_id ,
594 quota_id ,
595 target_amount ,
596 itd_target ,
597 performance_goal_ptd ,
598 performance_goal_itd ,
599 period_payment ,
600 itd_payment ,
601 commission_payed_ptd ,
602 creation_date ,
603 created_by ,
604 last_update_date ,
605 last_updated_by ,
606 last_update_login ,
607 org_id
608 )
609 SELECT cn_srp_period_quotas_s.nextval ,
610 qa.srp_plan_assign_id ,
611 qa.srp_quota_assign_id ,
612 pa.salesrep_id ,
613 p.period_id ,
614 qa.quota_id ,
615 NVL(pq.period_target,0) ,
616 pq.itd_target ,
617 pq.performance_goal ,
618 pq.performance_goal_itd ,
619 pq.period_payment ,
620 pq.itd_payment ,
621 0 ,
622 sysdate ,
623 l_user_id ,
624 sysdate ,
625 l_user_id ,
626 l_login_id ,
627 qa.org_id
628 FROM cn_srp_quota_assigns_all qa ,
629 cn_period_quotas_all pq ,
630 cn_srp_plan_assigns_all pa ,
631 cn_period_statuses p
632 WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
633 AND pa.srp_plan_assign_id = qa.srp_plan_assign_id
634 AND qa.quota_id = x_quota_id
635 AND greatest(pa.start_date, p.start_date) <= least(NVL(pa.end_date,p.end_date), p.end_date)
636 AND pq.period_id = p.period_id
637 AND p.period_status IN ('O', 'F')
638 AND QA.ORG_ID = PQ.ORG_ID --bug fix 7381426
639 AND pq.org_id = p.org_id
640 AND pq.quota_id = qa.quota_id
641 AND NOT EXISTS
642 (SELECT 'srp_period_quota already exists'
643 FROM cn_srp_period_quotas_all spq
644 WHERE spq.srp_quota_assign_id = qa.srp_quota_assign_id
645 AND spq.period_id = p.period_id
646 )
647 AND EXISTS
648 (SELECT r1.end_date
649 FROM CN_PERIOD_STATUSES_ALL R1
650 WHERE r1.end_date >= NVL(x_start_date, pa.start_date)
651 AND (R1.PERIOD_SET_ID, R1.PERIOD_TYPE_ID ) IN
652 (SELECT CR.PERIOD_SET_ID,
653 CR.PERIOD_TYPE_ID
654 FROM CN_REPOSITORIES_ALL CR
655 WHERE cr.org_id= r1.org_id
656 )
657 AND R1.PERIOD_STATUS IN ('O', 'F')
658 AND r1.org_id = pa.org_id
659 );
660 --bugfix #2874991 starts
661 OPEN start_period_quota_id_csr1(x_srp_plan_assign_id,itd_p_rec.quota_id,x_start_period_id);
662 FETCH start_period_quota_id_csr1 INTO l_srp_start_period_quota_id;
663
664 CLOSE start_period_quota_id_csr1;
665 IF l_srp_start_period_quota_id <> 0 THEN
666 populate_itd_values(l_srp_start_period_quota_id);
667 END IF;
668 --bugfix #2874991 ends
669 -- get number_dim
670 l_number_dim := 0;
671 OPEN get_number_dim(x_quota_id);
672 FETCH get_number_dim INTO l_number_dim;
673
674 CLOSE get_number_dim;
675 --clku bug 2845024
676 IF l_number_dim > 1 THEN
677 FOR srp_period_quota_id_rec1 IN srp_period_quota_ids1(x_quota_id, x_srp_plan_assign_id)
678 LOOP
679 populate_srp_period_quotas_ext ('INSERT',srp_period_quota_id_rec1.srp_period_quota_id, itd_p_rec.org_id, l_number_dim);
680 END LOOP;
681 END IF;
682 ELSIF x_srp_plan_assign_id IS NULL AND x_quota_id IS NOT NULL THEN
683 -- Quota's period range has been changed and we are inserting a
684 -- new set of records based on the period interval
685 INSERT
686 INTO cn_srp_period_quotas_all
687 (
688 srp_period_quota_id ,
689 srp_plan_assign_id ,
690 srp_quota_assign_id ,
691 salesrep_id ,
692 period_id ,
693 quota_id ,
694 target_amount ,
695 itd_target ,
696 period_payment ,
697 itd_payment ,
698 performance_goal_ptd ,
699 performance_goal_itd ,
700 commission_payed_ptd ,
701 creation_date ,
702 created_by ,
703 last_update_date ,
704 last_updated_by ,
705 last_update_login ,
706 org_id
707 )
708 SELECT cn_srp_period_quotas_s.nextval ,
709 qa.srp_plan_assign_id ,
710 qa.srp_quota_assign_id ,
711 pa.salesrep_id ,
712 p.period_id ,
713 qa.quota_id ,
714 0 -- clku, enhancement 2431086, Nvl(q.payment_amount,0)
715 ,
716 0 ,
717 0 ,
718 0 ,
719 0 ,
720 0 ,
721 0 ,
722 sysdate ,
723 l_user_id ,
724 sysdate ,
725 l_user_id ,
726 l_login_id ,
727 qa.org_id
728 FROM cn_srp_quota_assigns_all qa ,
729 cn_quotas_all q ,
730 cn_srp_plan_assigns_all pa ,
731 cn_acc_period_statuses_v p
732 -- bug fix 4042235
733 ,
734 cn_period_statuses p2 ,
735 cn_period_statuses p3
736 WHERE qa.srp_plan_assign_id = pa.srp_plan_assign_id
737 AND qa.quota_id = x_quota_id
738 AND q.quota_id = x_quota_id
739 AND q.quota_id = qa.quota_id --bugfix#3633222
740 AND p.org_id = qa.org_id
741 AND (
742 -- bug 2150333, changed to improved performance
743 -- set 1: pa.start_date
744 (pa.start_date BETWEEN p2.start_date AND p2.end_date
745 AND pa.org_id = p2.org_id)
746 AND p.start_date >= p2.start_date
747 AND p.period_type_id = p2.period_type_id
748 AND p.period_set_id = p2.period_set_id
749 -- set 2: pa.end_date
750 -- clku, fixed a date insert issue
751 AND (least(NVL(pa.end_date,p.end_date), p.end_date) BETWEEN p3.start_date AND p3.end_date)
752 AND p.end_date <= p3.end_date
753 AND p.org_id = p3.org_id
754 AND p.period_type_id = p3.period_type_id
755 AND p.period_set_id = p3.period_set_id )
756 AND greatest(p.start_date, x_start_date) <= least(p.end_date, NVL(x_end_date, p.end_date))
757 AND NOT EXISTS
758 (SELECT 'srp_quota_assign already exists'
759 FROM cn_srp_period_quotas_all pq
760 WHERE pq.srp_quota_assign_id = qa.srp_quota_assign_id
761 AND pq.period_id = p.period_id
762 )
763 -- bug 2460926, check if all the open period ends before the specified start_date
764 AND EXISTS
765 (SELECT r1.end_date
766 FROM CN_PERIOD_STATUSES_ALL R1
767 WHERE r1.end_date >= NVL(x_start_date, pa.start_date)
768 AND (R1.PERIOD_SET_ID, R1.PERIOD_TYPE_ID ) IN
769 (SELECT CR.PERIOD_SET_ID,
770 CR.PERIOD_TYPE_ID
771 FROM CN_REPOSITORIES_ALL CR
772 WHERE cr.org_id= r1.org_id
773 )
774 AND R1.PERIOD_STATUS IN ('O', 'F')
775 AND r1.org_id = pa.org_id
776 ) ;
777 --bugfix #2874991 starts
778 OPEN start_period_quota_id_csr2(x_quota_id,x_start_period_id);
779 FETCH start_period_quota_id_csr2 BULK COLLECT
780 INTO l_start_period_quota_id_tbl;
781
782 CLOSE start_period_quota_id_csr2;
783 IF l_start_period_quota_id_tbl.count > 0 THEN
784 FOR counter IN 1..l_start_period_quota_id_tbl.count
785 LOOP
786 populate_itd_values(l_start_period_quota_id_tbl(counter));
787 END LOOP;
788 END IF;
789 --bugfix #2874991 ends
790 -- get number_dim
791 l_number_dim := 0;
792 OPEN get_number_dim(x_quota_id);
793 FETCH get_number_dim INTO l_number_dim;
794
795 CLOSE get_number_dim;
796 --clku bug 2845024
797 IF l_number_dim > 1 THEN
798 FOR srp_period_quota_id_rec2 IN srp_period_quota_ids2(x_quota_id)
799 LOOP
800 populate_srp_period_quotas_ext ('INSERT',srp_period_quota_id_rec2.srp_period_quota_id, itd_p_rec.org_id, l_number_dim);
801 END LOOP;
802 END IF;
803 END IF;
804 -- End Insert Record.
805 END Insert_Record;
806 ---------------------------------------------------------------------------+
807 -- PROCEDURE LOCK RECORD
808 ---------------------------------------------------------------------------+
809 PROCEDURE lock_record
810 (
811 x_srp_period_quota_id NUMBER ,
812 x_period_id NUMBER ,
813 x_target_amount NUMBER)
814 IS
815 CURSOR c
816 IS
817 SELECT target_amount
818 FROM cn_srp_period_quotas_all
819 WHERE srp_period_quota_id = x_srp_period_quota_id FOR UPDATE OF srp_period_quota_id NOWAIT;
820
821 recinfo c%ROWTYPE;
822 BEGIN
823 OPEN C;
824 FETCH C INTO Recinfo;
825 IF (c%notfound) THEN
826 CLOSE C;
827 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
828 app_exception.raise_exception;
829 END IF;
830 CLOSE C;
831 IF ( recinfo.target_amount = x_target_amount) THEN
832 RETURN;
833 ELSE
834 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
835 app_exception.raise_exception;
836 END IF;
837 -- End Lock Record.
838 END lock_record;
839 ---------------------------------------------------------------------------+
840 -- PROCEDURE UPDATE RECORD
841 ---------------------------------------------------------------------------+
842 PROCEDURE update_record
843 (
844 x_period_target_unit_code VARCHAR2 ,
845 x_srp_period_quota_id NUMBER ,
846 x_srp_quota_assign_id NUMBER ,
847 x_period_id NUMBER ,
848 x_target_amount NUMBER ,
849 x_period_payment NUMBER ,
850 x_performance_goal NUMBER ,
851 x_quarter_num NUMBER ,
852 x_period_year NUMBER ,
853 x_quota_type_code VARCHAR2 ,
854 x_quota_id NUMBER := NULL -- only for bonus pay
855 ,
856 x_salesrep_id NUMBER := NULL -- only for bonus pay
857 ,
858 x_end_date DATE := NULL -- only for bonus pay
859 ,
860 x_commission_payed_ptd NUMBER := NULL -- only for bonus pay
861 ,
862 x_last_update_date DATE ,
863 x_last_updated_by NUMBER ,
864 x_last_update_login NUMBER)
865 IS
866 -- Count the number of periods in each quarter/year combination that the
867 -- quota assignment covers
868 CURSOR quart_counts
869 IS
870 SELECT COUNT(srp_period_quota_id) quart_yr_count ,
871 quarter_num ,
872 period_year
873 FROM cn_srp_period_quotas_v
874 WHERE srp_quota_assign_id = x_srp_quota_assign_id
875 GROUP BY quarter_num,
876 period_year ;
877
878 quart_rec quart_counts%ROWTYPE;
879 -- Count the number of periods in each year that the quota assignment
880 -- covers.
881 CURSOR year_counts
882 IS
883 SELECT COUNT(srp_period_quota_id) year_count ,
884 period_year
885 FROM cn_srp_period_quotas_v
886 WHERE srp_quota_assign_id = x_srp_quota_assign_id
887 GROUP BY period_year ;
888
889 year_rec year_counts%ROWTYPE;
890 CURSOR period_quotas(l_interval_number NUMBER, l_period_year NUMBER)
891 IS
892 SELECT spq.srp_period_quota_id ,
893 spq.target_amount ,
894 spq.period_payment ,
895 spq.performance_goal_ptd
896 FROM cn_srp_period_quotas_v spq,
897 cn_period_statuses cp ,
898 cn_cal_per_int_types_all cpit ,
899 cn_quotas_all cq
900 WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
901 AND spq.quota_id = cq.quota_id
902 AND spq.period_id = cp.period_id
903 AND cp.period_status IN ('O', 'F')
904 AND cq.org_id = cp.org_id
905 AND cp.period_id = cpit.cal_period_id
906 AND cp.org_id = cpit.org_id
907 AND cpit.interval_type_id = cq.interval_type_id
908 AND cpit.interval_number = l_interval_number
909 AND spq.period_year = l_period_year
910 ORDER BY spq.period_id ;
911
912 pq_rec period_quotas%ROWTYPE;
913 -- Get the period quotas that belong to the quota assignment for each
914 -- interval
915 CURSOR interval_counts
916 IS
917 SELECT COUNT(spq.srp_period_quota_id) interval_count,
918 cpit.interval_number interval_number ,
919 spq.period_year period_year
920 FROM cn_srp_period_quotas_v spq,
921 cn_period_statuses cp ,
922 cn_cal_per_int_types_all cpit ,
923 cn_quotas_all cq
924 WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
925 AND spq.quota_id = cq.quota_id
926 AND spq.period_id = cp.period_id
927 AND cp.period_status IN ('O', 'F')
928 AND cq.org_id = cp.org_id
929 AND cp.period_id = cpit.cal_period_id
930 AND cp.org_id = cpit.org_id
931 AND cpit.interval_type_id = cq.interval_type_id
932 GROUP BY cpit.interval_number,
933 spq.period_year ;
934
935 interval_rec interval_counts%ROWTYPE;
936 -- added for intelligent calculation
937 CURSOR l_get_intel_temp_csr
938 IS
939 SELECT period.target_amount,
940 period.period_payment ,
941 srp.name ,
942 acc.start_date ,
943 acc.end_date ,
944 srp.org_id
945 FROM cn_srp_period_quotas_all period,
946 cn_salesreps srp ,
947 cn_period_statuses acc
948 WHERE period.srp_period_quota_id = x_srp_period_quota_id
949 AND acc.period_id = period.period_id
950 AND acc.org_id = period.org_id
951 AND acc.period_status IN ('O', 'F')
952 AND srp.salesrep_id = period.salesrep_id
953 AND srp.org_id = period.org_id;
954
955 l_temp_target_amount NUMBER;
956 l_temp_period_payment NUMBER;
957 l_temp_salesrep_name VARCHAR2(240);
958 l_temp_start_date DATE;
959 l_temp_end_date DATE;
960 l_temp_org_id NUMBER;
961 -- end of add
962 l_target_total NUMBER;
963 l_payment_total NUMBER;
964 l_performance_goal_total NUMBER;
965 l_commission_payed_total NUMBER;
966 l_target_amount NUMBER;
967 l_period_payment NUMBER;
968 l_performance_goal NUMBER;
969 l_commission_payed NUMBER;
970 g_ext_precision NUMBER;
971 BEGIN
972 -- get precision
973 SELECT c.extended_precision
974 INTO g_ext_precision
975 FROM cn_repositories r,
976 gl_sets_of_books b ,
977 fnd_currencies c ,
978 cn_srp_period_quotas spq
979 WHERE r.org_id = spq.org_id
980 AND r.set_of_books_id = b.set_of_books_id
981 AND b.currency_code = c.currency_code
982 AND spq.srp_period_quota_id = x_srp_period_quota_id;
983 IF x_quota_type_code IN ('EXTERNAL','FORMULA') THEN
984 -- newly added by Kai Chen for intellegent calculaltion
985 -- make event on target_amount and period_amount
986 OPEN l_get_intel_temp_csr;
987 FETCH l_get_intel_temp_csr
988 INTO l_temp_target_amount,
989 l_temp_period_payment ,
990 l_temp_salesrep_name ,
991 l_temp_start_date ,
992 l_temp_end_date ,
993 l_temp_org_id;
994
995 CLOSE l_get_intel_temp_csr;
996 -- end of addition
997 UPDATE cn_srp_period_quotas_all
998 SET target_amount = ROUND(NVL(x_target_amount, 0), g_ext_precision),
999 period_payment = ROUND(NVL(x_period_payment,0), g_ext_precision),
1000 performance_goal_ptd = ROUND(NVL(x_performance_goal,0), g_ext_precision)
1001 WHERE srp_period_quota_id = x_srp_period_quota_id ;
1002 -- newly added for intel calc
1003 IF (l_temp_target_amount <> x_target_amount ) OR (l_temp_period_payment <> x_period_payment) THEN
1004 cn_mark_events_pkg.mark_event_srp_period_quota( 'CHANGE_SRP_QUOTA_CALC', l_temp_salesrep_name, x_srp_period_quota_id, NULL, NULL, l_temp_start_date, NULL, l_temp_end_date, l_temp_org_id);
1005 END IF;
1006 -- end of addition
1007 FOR interval_rec IN interval_counts
1008 LOOP
1009 -- Initialize for each interval
1010 l_target_total := 0;
1011 l_payment_total := 0;
1012 l_performance_goal_total := 0;
1013 -- Now that we know the counts per quarter/year we can divide the
1014 -- quota target correctly for each quarter and set the period quota
1015 -- target.
1016 FOR pq_rec IN period_quotas ( l_interval_number => interval_rec.interval_number ,l_period_year => interval_rec.period_year)
1017 LOOP
1018 l_target_total := l_target_total + pq_rec.target_amount;
1019 l_payment_total := l_payment_total + pq_rec.period_payment;
1020 l_performance_goal_total := l_performance_goal_total + pq_rec.performance_goal_ptd;
1021 UPDATE cn_srp_period_quotas_all
1022 SET itd_target = ROUND(NVL(l_target_total,0), g_ext_precision) ,
1023 itd_payment = ROUND(NVL(l_payment_total,0), g_ext_precision),
1024 performance_goal_itd = ROUND(NVL(l_performance_goal_total,0),g_ext_precision)
1025 WHERE srp_period_quota_id = pq_rec.srp_period_quota_id ;
1026 END LOOP;
1027 END LOOP;
1028 ELSE
1029 -- only in the case of bonus at the time payee run Bonus commission update
1030 IF x_salesrep_id IS NOT NULL AND x_end_date IS NOT NULL AND x_quota_id IS NOT NULL THEN
1031 UPDATE cn_srp_period_quotas_all spq
1032 SET spq.commission_payed_ptd = x_commission_payed_ptd +spq.commission_payed_ptd
1033 WHERE spq.salesrep_id = x_salesrep_id
1034 AND spq.quota_id = x_quota_id
1035 AND EXISTS
1036 (SELECT 1
1037 FROM cn_period_statuses aps
1038 WHERE x_end_date BETWEEN aps.start_date AND aps.end_date
1039 AND aps.period_id = spq.period_id
1040 AND aps.period_status IN ('O', 'F')
1041 AND aps.org_id = spq.org_id
1042 ) ;
1043 END IF;
1044 END IF;
1045 END Update_Record;
1046 -- Name
1047 --
1048 -- Purpose
1049 -- Delete period quota from each rep using the quota in a period
1050 --
1051 -- When the procedure is called Passed Parameters
1052 -- 1. after delete of srp plan assignment. x_srp_plan_assign_id
1053 -- 2. after update of srp plan assign period range x_srp_plan_assign_id
1054 -- We cannot delete all period quotas and then x_start_date
1055 -- simply resinsert for the new period range x_end_date
1056 -- Note:
1057 -- because we want to keep the original target
1058 -- distribution on the periods that remain.
1059 -- NB This is an oracle internal requirement.29/mar/95
1060 -- 3. after delete of comp plan quota assignment x_srp_plan_assign_id
1061 -- x_quota_id
1062 --
1063 ---------------------------------------------------------------------------+
1064 -- PROCEDURE DELETE_RECORD
1065 -- Description:
1066 -- Case 1: Delete will be called from cn_quotas date range has changed
1067 -- if end date is less than the end date of the old quota date
1068 -- Delete will be called from cn_quotas if start_date <> old_start_dt
1069 -- called from cn_quotas_pkg.
1070 -- Values Passed x_quota_id( M) , x_start_date, x_end_date
1071 --
1072 -- Case 2: Delete will be called from cn_period_quotas if the amount
1073 -- columns get updated and check the srp_plan_assigns customised
1074 -- flag if N then delete delete the srp_period_quotas and re create.
1075 -- called from cn_period_quotas
1076 -- Values Passed x_quota_id (M )
1077 --
1078 -- Case 3: Delete will be called from cn_srp_plan_assigns date range has
1079 -- changed if end date is less than the end date of the old plan date
1080 -- Delete will be called from cn_srp_plan_assigns if start_date
1081 -- <> old_start_dt called from cn_srp_plan_assigns_pkg
1082 -- Values Passed : x_srp_plan_assign_id, x_start_date, x_end_date
1083 --
1084 -- Case 4 Delete srp_plan_assigns Called cn_srp_quota_assigns and
1085 -- cn_srp_quota_assigns make a call here
1086 -- srp_plan_assign_id and/or quota_id
1087 ---------------------------------------------------------------------------+
1088 PROCEDURE Delete_Record
1089 (
1090 x_srp_plan_assign_id NUMBER ,
1091 x_quota_id NUMBER ,
1092 x_start_period_id NUMBER ,
1093 x_end_period_id NUMBER ,
1094 x_start_date DATE := NULL ,
1095 x_end_date DATE := NULL )
1096 IS
1097 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS ;
1098 l_msg_count NUMBER;
1099 l_msg_data VARCHAR2(2000);
1100 l_loading_status VARCHAR2(2000);
1101 l_org_id NUMBER;
1102 CURSOR srp_period_quota_ids(l_quota_id NUMBER)
1103 IS
1104 SELECT srp_period_quota_id,
1105 org_id
1106 FROM cn_srp_period_quotas_all
1107 WHERE quota_id = l_quota_id
1108 AND srp_plan_assign_id = NVL(x_srp_plan_assign_id, srp_plan_assign_id)
1109 AND EXISTS
1110 (SELECT 1
1111 FROM cn_period_statuses p
1112 WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
1113 AND TRUNC(p.end_date) <= TRUNC(NVL(x_end_date ,p.end_date))
1114 AND cn_srp_period_quotas_all.period_id = p.period_id
1115 AND p.period_status IN ('O', 'F')
1116 AND cn_srp_period_quotas_all.org_id = p.org_id
1117 );
1118
1119 l_srp_prd_rec cn_srp_periods_pvt.delta_srp_period_rec_type := cn_srp_periods_pvt.g_miss_delta_srp_period_rec;
1120 CURSOR l_bal_id
1121 IS
1122 SELECT salesrep_id,
1123 period_id ,
1124 credit_type_id ,
1125 role_id ,
1126 balance2_dtd ,
1127 balance3_ctd ,
1128 srp_period_id
1129 FROM cn_srp_periods_all
1130 WHERE quota_id = x_quota_id
1131 AND org_id = l_org_id
1132 AND EXISTS
1133 (SELECT 1
1134 FROM cn_period_statuses p
1135 WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
1136 AND TRUNC(p.end_date) <= TRUNC(NVL(x_end_date ,p.end_date))
1137 AND cn_srp_periods_all.period_id = p.period_id
1138 AND p.period_status IN ('O', 'F')
1139 AND cn_srp_periods_all.org_id = p.org_id
1140 )
1141 AND (balance2_dtd <> 0
1142 OR balance3_ctd <> 0)
1143 ORDER BY salesrep_id,
1144 credit_type_id ,
1145 role_id ,
1146 period_id;
1147
1148 l_bal_rec l_bal_id%ROWTYPE;
1149 srp_period_quota_id_rec srp_period_quota_ids%ROWTYPE;
1150 -- get number_dim
1151 CURSOR get_number_dim(l_quota_id NUMBER)
1152 IS
1153 SELECT ccf.number_dim,
1154 cq.org_id
1155 FROM cn_quotas_all cq,
1156 cn_calc_formulas_all ccf
1157 WHERE cq.quota_id = l_quota_id
1158 AND cq.calc_formula_id = ccf.calc_formula_id;
1159
1160 l_number_dim NUMBER;
1161 CURSOR get_quotas
1162 IS
1163 SELECT quota_id
1164 FROM cn_srp_quota_assigns_all
1165 WHERE srp_plan_assign_id = x_srp_plan_assign_id;
1166
1167 l_last_period_id NUMBER := -1;
1168 l_last_salesrep_id NUMBER := -1;
1169 l_last_ct_id NUMBER := -1;
1170 l_last_role_id NUMBER := -1;
1171 l_end_date_pd DATE;
1172 BEGIN
1173 -- maybe we don't need to check srp_period_quotas_ext
1174 IF x_quota_id IS NOT NULL THEN
1175 l_number_dim := 0;
1176 OPEN get_number_dim(x_quota_id);
1177 FETCH get_number_dim INTO l_number_dim, l_org_id;
1178
1179 CLOSE get_number_dim;
1180 IF l_number_dim > 1 THEN
1181 FOR srp_period_quota_id_rec IN srp_period_quota_ids(x_quota_id)
1182 LOOP
1183 populate_srp_period_quotas_ext('DELETE',srp_period_quota_id_rec.srp_period_quota_id, srp_period_quota_id_rec.org_id);
1184 END LOOP;
1185 END IF;
1186 ELSE
1187 FOR q IN get_quotas
1188 LOOP
1189 l_number_dim := 0;
1190 OPEN get_number_dim(q.quota_id);
1191 FETCH get_number_dim INTO l_number_dim, l_org_id;
1192
1193 CLOSE get_number_dim;
1194 IF l_number_dim > 1 THEN
1195 FOR srp_period_quota_id_rec IN srp_period_quota_ids(q.quota_id)
1196 LOOP
1197 populate_srp_period_quotas_ext('DELETE',srp_period_quota_id_rec.srp_period_quota_id, srp_period_quota_id_rec.org_id);
1198 END LOOP;
1199 END IF;
1200 END LOOP;
1201 END IF;
1202 IF x_srp_plan_assign_id IS NOT NULL THEN
1203 IF x_quota_id IS NULL THEN
1204 IF x_start_date IS NULL THEN
1205 -- Deleted plan assignment
1206 DELETE
1207 FROM cn_srp_period_quotas_all
1208 WHERE srp_plan_assign_id = x_srp_plan_assign_id ;
1209 ELSE
1210 -- make sure we have the right org ID
1211 SELECT org_id
1212 INTO l_org_id
1213 FROM cn_srp_plan_assigns
1214 WHERE srp_plan_assign_id = x_srp_plan_assign_id;
1215 -- get end date period of x_end_date
1216 l_end_date_pd := TRUNC(cn_end_date_period(x_end_date, l_org_id));
1217 -- plan assignment range changed
1218 DELETE
1219 FROM cn_srp_period_quotas_all
1220 WHERE srp_plan_assign_id = x_srp_plan_assign_id
1221 AND EXISTS
1222 (SELECT 1
1223 FROM cn_period_statuses p
1224 WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
1225 -- following line changed for bug 4424669, 4885986
1226 AND TRUNC(p.end_date) <= NVL(l_end_date_pd, p.end_date)
1227 --AND trunc(p.end_date) <= trunc(cn_end_date_period(nvl(x_end_date ,p.end_date), p.org_id))
1228 AND p.period_status IN ('O', 'F')
1229 AND cn_srp_period_quotas_all.period_id = p.period_id
1230 AND cn_srp_period_quotas_all.org_id = p.org_id
1231 AND ( ( p.start_date <> p.end_date)
1232 OR (p.start_date = p.end_date AND p.start_date > TRUNC(x_start_date)) ) -- Fix for bug 13583329
1233 );
1234 END IF;
1235 ELSE -- Quota id IS NOT NULL
1236 -- quota is no longer assigned to the comp plan
1237 -- same as the start date us null
1238 DELETE
1239 FROM cn_srp_period_quotas_all
1240 WHERE srp_plan_assign_id = x_srp_plan_assign_id
1241 AND quota_id = x_quota_id
1242 AND NVL(x_start_period_id, period_id) <= period_id -- Bug 3848446, Fixed by Jagpreet Singh.
1243 ;
1244 END IF;
1245 ELSE -- srp_plan_assign_id is NULL
1246 -- changed to no_sync for bug 4019235
1247 FOR l_bal_rec IN l_bal_id
1248 LOOP
1249 l_srp_prd_rec.srp_period_id := l_bal_rec.srp_period_id;
1250 l_srp_prd_rec.del_balance2_dtd := l_bal_rec.balance2_dtd*(-1);
1251 l_srp_prd_rec.del_balance3_ctd := l_bal_rec.balance3_ctd*(-1);
1252 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);
1253 IF l_bal_rec.salesrep_id <> l_last_salesrep_id OR l_bal_rec.role_id <> l_last_role_id OR l_bal_rec.credit_type_id <> l_last_ct_id THEN
1254 IF l_last_salesrep_id <> -1 THEN
1255 cn_srp_periods_pvt.sync_accum_balances_start_pd (p_salesrep_id => l_last_salesrep_id, p_org_id => l_org_id, p_credit_type_id => l_last_ct_id, p_role_id => l_last_role_id, p_start_period_id => l_last_period_id);
1256 END IF;
1257 l_last_salesrep_id := l_bal_rec.salesrep_id;
1258 l_last_role_id := l_bal_rec.role_id;
1259 l_last_ct_id := l_bal_rec.credit_type_id;
1260 l_last_period_id := l_bal_rec.period_id;
1261 END IF;
1262 END LOOP;
1263 IF l_last_salesrep_id <> -1 THEN
1264 cn_srp_periods_pvt.sync_accum_balances_start_pd (p_salesrep_id => l_last_salesrep_id, p_org_id => l_org_id, p_credit_type_id => l_last_ct_id, p_role_id => l_last_role_id, p_start_period_id => l_last_period_id);
1265 END IF;
1266 -- done with changes for bug 4019235
1267 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1269 END IF;
1270 -- quota date range changed. remove the old periods in preparation
1271 -- for insert of new range
1272 DELETE
1273 FROM cn_srp_period_quotas_all
1274 WHERE quota_id = x_quota_id
1275 AND EXISTS
1276 (SELECT 1
1277 FROM cn_period_statuses p
1278 WHERE TRUNC(p.start_date) >= TRUNC(NVL(x_start_date,p.start_date))
1279 AND TRUNC(p.end_date) <= TRUNC(NVL(x_end_date ,p.end_date))
1280 AND cn_srp_period_quotas_all.period_id = p.period_id
1281 AND p.period_status IN ('O', 'F')
1282 AND cn_srp_period_quotas_all.org_id = p.org_id
1283 AND ( ( p.start_date <> p.end_date)
1284 OR (p.start_date = p.end_date AND p.start_date > TRUNC(x_start_date)) ) -- Fix for bug 13583329
1285 );
1286 END IF;
1287 END Delete_Record;
1288 ---------------------------------------------------------------------------+
1289 -- PROCEDURE BEGIN_RECORD
1290 ---------------------------------------------------------------------------+
1291 PROCEDURE Begin_Record
1292 (
1293 x_operation VARCHAR2 ,
1294 x_period_target_unit_code VARCHAR2 ,
1295 x_srp_period_quota_id NUMBER ,
1296 x_srp_quota_assign_id NUMBER ,
1297 x_srp_plan_assign_id NUMBER ,
1298 x_quota_id NUMBER ,
1299 x_period_id NUMBER ,
1300 x_target_amount NUMBER ,
1301 x_period_payment NUMBER ,
1302 x_performance_goal NUMBER ,
1303 x_quarter_num NUMBER ,
1304 x_period_year NUMBER ,
1305 x_quota_type_code VARCHAR2 ,
1306 x_salesrep_id NUMBER := NULL -- only for bonus pay
1307 ,
1308 x_end_date DATE := NULL -- only for
1309 ,
1310 x_commission_payed_ptd NUMBER := NULL -- only for bonus pay
1311 ,
1312 x_creation_date DATE ,
1313 x_created_by NUMBER ,
1314 x_last_update_date DATE ,
1315 x_last_updated_by NUMBER ,
1316 x_last_update_login NUMBER )
1317 IS
1318 BEGIN
1319 IF x_operation = 'INSERT' THEN
1320 -- insert the record for the given quota and salesrep
1321 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 );
1322 ELSIF x_operation = 'UPDATE' THEN
1323 -- Update record
1324 Update_Record ( x_period_target_unit_code => x_period_target_unit_code ,
1325 x_srp_period_quota_id => x_srp_period_quota_id ,
1326 x_srp_quota_assign_id => x_srp_quota_assign_id ,
1327 x_period_id => x_period_id ,
1328 x_target_amount => x_target_amount ,
1329 x_period_payment => x_period_payment ,
1330 x_performance_goal => x_performance_goal ,
1331 x_quarter_num => x_quarter_num ,
1332 x_period_year => x_period_year ,
1333 x_quota_type_code => x_quota_type_code ,
1334 x_quota_id => x_quota_id ,
1335 x_salesrep_id => x_salesrep_id ,
1336 x_end_date => x_end_date ,
1337 x_commission_payed_ptd => x_commission_payed_ptd ,
1338 x_last_update_date => x_last_update_date ,
1339 x_last_updated_by => x_last_updated_by ,
1340 x_last_update_login => x_last_update_login);
1341 ELSIF x_operation = 'LOCK' THEN
1342 -- Lock Record
1343 Lock_Record ( x_srp_period_quota_id => x_srp_period_quota_id ,x_period_id => x_period_id ,x_target_amount => x_target_amount);
1344 ELSIF X_Operation = 'DELETE' THEN
1345 -- Delete Record
1346 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 );
1347 END IF;
1348 END Begin_Record;
1349 -- Name
1350 -- Populate_srp_period_quotas_ext
1351 -- Purpose
1352 -- Populate cn_srp_period_quota_ext table
1353 -- We take the following operations as parameters:
1354 -- INSERT : insert records in cn_srp_period_quotas_ext if necessary
1355 -- DELETE : delete all records in cn_srp_period_quotas_ext for certain srp_period_quota_id
1356 PROCEDURE populate_srp_period_quotas_ext
1357 (
1358 x_operation VARCHAR2,
1359 x_srp_period_quota_id NUMBER,
1360 x_org_id NUMBER,
1361 x_number_dim NUMBER)
1362 IS
1363 l_user_id NUMBER(15);
1364 l_login_id NUMBER(15);
1365 l_number_dim NUMBER(15);
1366 -- only use this if number_dim not used
1367 CURSOR DIM_NUMBER_CUR
1368 IS
1369 SELECT ccf.number_dim
1370 FROM cn_srp_period_quotas_all cspq,
1371 cn_quotas_all cq ,
1372 cn_calc_formulas_all ccf
1373 WHERE cspq.srp_period_quota_id = x_srp_period_quota_id
1374 AND cq.quota_id = cspq.quota_id
1375 AND cq.calc_formula_id = ccf.calc_formula_id ;
1376
1377 l_count NUMBER;
1378 BEGIN
1379 l_user_id := fnd_global.user_id;
1380 l_login_id := fnd_global.login_id;
1381 IF x_operation = 'INSERT' THEN
1382 -- get number dim if necessary
1383 IF x_number_dim is NULL THEN
1384 l_number_dim := 0;
1385 OPEN dim_number_cur;
1386 FETCH dim_number_cur INTO l_number_dim;
1387
1388 CLOSE dim_number_cur;
1389 ELSE
1390 l_number_dim := x_number_dim;
1391 END IF;
1392 FOR i_seq IN 2..l_number_dim
1393 LOOP
1394 INSERT
1395 INTO cn_srp_period_quotas_ext_all
1396 (
1397 srp_period_quota_ext_id,
1398 srp_period_quota_id ,
1399 input_sequence ,
1400 created_by ,
1401 creation_date ,
1402 last_update_login ,
1403 last_update_date ,
1404 last_updated_by ,
1405 org_id
1406 )
1407 SELECT cn_srp_period_quotas_ext_s.nextval,
1408 x_srp_period_quota_id ,
1409 i_seq ,
1410 l_user_id ,
1411 sysdate ,
1412 l_login_id ,
1413 sysdate ,
1414 l_user_id ,
1415 x_org_id
1416 FROM dual
1417 WHERE NOT EXISTS
1418 (SELECT 1
1419 FROM cn_srp_period_quotas_ext_all
1420 WHERE srp_period_quota_id = x_srp_period_quota_id
1421 AND input_sequence = i_seq
1422 );
1423 END LOOP;
1424 ELSIF x_operation = 'DELETE' THEN
1425 DELETE
1426 FROM cn_srp_period_quotas_ext_all
1427 WHERE srp_period_quota_id = x_srp_period_quota_id;
1428 END IF;
1429 END;
1430 -- Name
1431 -- select_summary
1432 -- Purpose
1433 -- Maintain running totals
1434 ---------------------------------------------------------------------------+
1435 -- PROCEDURE SELECT_SUMMARY
1436 ---------------------------------------------------------------------------+
1437 PROCEDURE select_summary
1438 (
1439 x_srp_quota_assign_id NUMBER ,
1440 x_total IN OUT NOCOPY NUMBER ,
1441 x_total_rtot_db IN OUT NOCOPY NUMBER)
1442 IS
1443 BEGIN
1444 SELECT NVL(SUM(target_amount),0)
1445 INTO x_total
1446 FROM cn_srp_period_quotas_all
1447 WHERE srp_quota_assign_id = x_srp_quota_assign_id ;
1448
1449 x_total_rtot_db := x_total;
1450 EXCEPTION
1451 WHEN no_data_found THEN
1452 NULL;
1453 END select_summary;
1454 -- Name
1455 -- Period_target
1456 -- Purpose
1457 -- Distribute target/payment amount over periods
1458 -- Notes
1459 --
1460 -- If period_type_code(a.k.a "Interval") = "PERIOD"
1461 -- We do not need to divide up the quota target and distribute it
1462 -- over the srp periods because the entire quota target is applied
1463 -- to each srp period. No math required.
1464 -- If the period_type_code = "QUARTER"
1465 -- We need to apply the quota target to each quarter which means
1466 -- dividing the target by the number of periods in the quarter and
1467 -- assigning that amount to each period
1468 -- e.g. Target = 100 Jan 33.3333
1469 -- Feb 33.3333
1470 -- Mar 33.3333
1471 -- If a quarter has less than 3 periods the target amount
1472 -- will be divided over the reduced number of periods.
1473 -- e.g. Target = 100 Jan 33.3333
1474 -- Feb 33.3333
1475 -- Mar 33.3333
1476 -- Apr 100
1477 -- If the period_type_code = "YEAR"
1478 -- The target will be divided by the number of periods in each year that
1479 -- the quota is active. This deals with situations where the quota is
1480 -- assigned for less than 12 periods in any year.
1481 ---------------------------------------------------------------------------+
1482 -- PROCEDURE DISTRIBUTE_TARGET
1483 ---------------------------------------------------------------------------+
1484 PROCEDURE Distribute_Target
1485 (
1486 x_srp_quota_assign_id NUMBER ,
1487 x_target NUMBER ,
1488 x_period_target_unit_code VARCHAR2)
1489 IS
1490 CURSOR period_quotas(l_interval_number NUMBER, l_period_year NUMBER)
1491 IS
1492 SELECT spq.srp_period_quota_id
1493 FROM cn_srp_period_quotas_v spq,
1494 cn_period_statuses cp ,
1495 cn_cal_per_int_types_all cpit ,
1496 cn_quotas_all cq
1497 WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
1498 AND spq.quota_id = cq.quota_id
1499 AND spq.period_id = cp.period_id
1500 AND cp.period_status IN ('O', 'F')
1501 AND cq.org_id = cp.org_id
1502 AND cp.period_id = cpit.cal_period_id
1503 AND cp.org_id = cpit.org_id
1504 AND cpit.interval_type_id = cq.interval_type_id
1505 AND cpit.interval_number = l_interval_number
1506 AND spq.period_year = l_period_year
1507 ORDER BY spq.period_id ;
1508
1509 pq_rec period_quotas%ROWTYPE;
1510 -- Get the period quotas that belong to the quota assignment for each
1511 -- interval
1512 CURSOR interval_counts
1513 IS
1514 SELECT COUNT(spq.srp_period_quota_id) interval_count,
1515 cpit.interval_number interval_number ,
1516 spq.period_year period_year
1517 FROM cn_srp_period_quotas_v spq,
1518 cn_period_statuses cp ,
1519 cn_cal_per_int_types_all cpit ,
1520 cn_quotas_all cq
1521 WHERE spq.srp_quota_assign_id = x_srp_quota_assign_id
1522 AND spq.quota_id = cq.quota_id
1523 AND spq.period_id = cp.period_id
1524 AND cp.period_status IN ('O', 'F')
1525 AND cq.org_id = cp.org_id
1526 AND cp.period_id = cpit.cal_period_id
1527 AND cp.org_id = cpit.org_id
1528 AND cpit.interval_type_id = cq.interval_type_id
1529 GROUP BY cpit.interval_number,
1530 spq.period_year ;
1531
1532 interval_rec interval_counts%ROWTYPE;
1533 l_period_count NUMBER;
1534 l_running_total_target NUMBER;
1535 l_total_periods NUMBER;
1536 l_period_target NUMBER;
1537 l_running_total_payment NUMBER;
1538 l_period_payment NUMBER;
1539 l_running_performance_goal NUMBER;
1540 l_performance_goal NUMBER;
1541 l_srp_quota_assign_id NUMBER(15);
1542 l_quota_target NUMBER;
1543 l_quota_payment NUMBER;
1544 l_quota_performance_goal NUMBER;
1545 l_dist_rule_code VARCHAR2(30);
1546 l_period_type_code VARCHAR2(30);
1547 l_period_performance_goal NUMBER;
1548 g_ext_precision NUMBER;
1549 BEGIN
1550 -- get precision
1551 SELECT c.extended_precision
1552 INTO g_ext_precision
1553 FROM cn_repositories r,
1554 gl_sets_of_books b ,
1555 fnd_currencies c ,
1556 cn_srp_quota_assigns sqa
1557 WHERE r.org_id = sqa.org_id
1558 AND r.set_of_books_id = b.set_of_books_id
1559 AND b.currency_code = c.currency_code
1560 AND sqa.srp_quota_assign_id = x_srp_quota_assign_id;
1561 -- Get quota assignment info for the quota to be distributed
1562 --
1563 SELECT NVL(qa.target,0) ,
1564 NVL(qa.payment_amount, 0) ,
1565 NVL(qa.performance_goal,0) ,
1566 qa.period_target_dist_rule_code ,
1567 cn_chk_plan_element_pkg.get_interval_name(q.interval_type_id, q.org_id) period_type_code
1568 INTO l_quota_target ,
1569 l_quota_payment ,
1570 l_performance_goal ,
1571 l_dist_rule_code ,
1572 l_period_type_code
1573 FROM cn_srp_quota_assigns_all qa,
1574 cn_quotas_all q
1575 WHERE qa.srp_quota_assign_id = x_srp_quota_assign_id
1576 AND q.quota_id = qa.quota_id
1577 AND qa.period_target_dist_rule_code <> 'USER_DEFINED' ;
1578 -- Currently this is the only distribution rule we support
1579 IF l_dist_rule_code = 'EQUAL' THEN
1580 FOR interval_rec IN interval_counts
1581 LOOP
1582 -- Initialize for each interval
1583 l_period_count := 0;
1584 l_running_total_target := 0;
1585 l_period_target := 0;
1586 l_running_total_payment := 0;
1587 l_period_payment := 0;
1588 l_running_performance_goal := 0;
1589 l_period_performance_goal := 0;
1590 -- Now that we know the counts per quarter/year we can divide the
1591 -- quota target correctly for each quarter and set the period quota
1592 -- target.
1593 FOR pq_rec IN period_quotas ( l_interval_number => interval_rec.interval_number ,l_period_year => interval_rec.period_year)
1594 LOOP
1595 l_period_count := l_period_count +1;
1596 l_period_target := ( ( l_quota_target * (l_period_count / interval_rec.interval_count) ) - l_running_total_target );
1597 l_running_total_target := l_running_total_target + l_period_target;
1598 l_period_payment := ( ( l_quota_payment * (l_period_count / interval_rec.interval_count) ) - l_running_total_payment );
1599 l_running_total_payment := l_running_total_payment + l_period_payment;
1600 l_period_performance_goal := ( ( l_performance_goal * (l_period_count / interval_rec.interval_count) ) - l_running_performance_goal );
1601 l_running_performance_goal := l_running_performance_goal + l_period_performance_goal;
1602 UPDATE cn_srp_period_quotas_all
1603 SET target_amount = ROUND(NVL(l_period_target, 0), g_ext_precision) ,
1604 itd_target = ROUND(NVL(l_running_total_target,0), g_ext_precision) ,
1605 period_payment = ROUND(NVL(l_period_payment,0), g_ext_precision) ,
1606 itd_payment = ROUND(NVL(l_running_total_payment,0), g_ext_precision) ,
1607 performance_goal_ptd = ROUND(NVL(l_period_performance_goal,0), g_ext_precision),
1608 performance_goal_itd = ROUND(NVL(l_running_performance_goal,0),g_ext_precision)
1609 WHERE srp_period_quota_id = pq_rec.srp_period_quota_id ;
1610 END LOOP;
1611 END LOOP;
1612 END IF;
1613 END distribute_target;
1614 --
1615 -- Purpose: synchronize the target / payment of srp_period_quotas table
1616 -- with period_quotas table when customized_flag is changed to
1617 -- 'N' and itd_flag is 'Y'
1618 --
1619 ---------------------------------------------------------------------------+
1620 -- PROCEDURE SYNCH_TARGET
1621 ---------------------------------------------------------------------------+
1622 PROCEDURE synch_target
1623 (
1624 x_srp_plan_assign_id NUMBER,
1625 x_quota_id NUMBER)
1626 IS
1627 BEGIN
1628 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);
1629 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);
1630 END synch_target;
1631 END CN_SRP_PERIOD_QUOTAS_PKG;
[Home] [Help]