[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_QUOTA_ASSIGNS_PKG
Source
1 PACKAGE BODY cn_srp_quota_assigns_pkg as
2 -- $Header: cnsrplbb.pls 120.2 2005/09/06 17:40:01 mblum noship $
3
4 /****************************************************************************
5 Package Body Name
6 cn_srp_quota_assigns_pkg
7
8 Purpose
9
10 History
11 -------
12 26-JAN-94 Tony Lower Created
13 10-FEB-94 P Cook Table handler conversion and unit test
14 30-JUN-95 P Cook Added calls to delete records from cn_srp_quota_rules
15 24-JUL-95 P Cook Update all denormalized quota columns whenever the
16 source quota changes.
17 25-JUL-95 P Cook Modified lock record to deral with null target value
18 08-AUG-95 P Cook Default customized_flag to 'Y' instead of 'N'.
19 21-AUG-95 P Cook Fixed update_srp_quota to prevent tiers being added
20 whenever the source quota is updated.
21 12-OCT-95 P Cook Added period_target_unit_code to table handlers.
22 16-FEB-96 P Cook Removed synching of comm rates and targets from
23 update_record procedure. Now done in salesrep form.
24 02-JUN-99 S Kumar Rate Schedule_id, Disc_rate_schedule_id is no more
25 exists in the quota Table.
26 Quota_type values are changed now we have only
27 Formula, External, None
28 Period_type is obsolete.
29 Added two parameters in Update_srp_quota
30 calc_formula_id, calc_formula_id_old
31 changing calc formula will iimpact the rates
32
33 25-AUG-99 S Kumar Added the performance goal at the insert statement
34 insert record.
35
36 Added the performance goal in the lock record, we
37 need to change the goal from the screen
38
39 Update_record has the new columns performance goal
40
41 Update Srp_quota has the new columns performance goal
42
43 25-AUG-99 S Kumar Modified the repective calling programs as well
44 23-MAR-01 K Chen Add changes from customize_flag
45
46 rarajara Added Valid_Assign procedure for payee check
47 20-NOV-03 RARAJARA Bugfix #3241172
48 **************************************************************************/
49
50
51 ----------------------------------------------------------------------------
52 -- FUNCTION IS_CUSTOMIZED
53 ----------------------------------------------------------------------------
54 -- Procedure Name
55 -- IS_CUSTOMIZED
56 -- Purpose
57 --
58 -- Notes
59 -- o Called from update_record to ensure whether the customer has customized
60 -- the plan element at the salesrep level
61 FUNCTION IS_CUSTOMIZED
62 (
63 x_srp_quota_assign_id NUMBER
64 , x_quota_id NUMBER
65 ) RETURN NUMBER
66 IS
67 l_return NUMBER;
68 BEGIN
69
70 l_return := 0 ; -- zero indicates PE not customized
71
72
73 SELECT count(*)
74 INTO l_return
75 FROM
76 (
77 SELECT 'X'
78 FROM cn_srp_quota_assigns_all csqa
79 , cn_quotas_all cq
80 WHERE csqa.srp_quota_assign_id = x_srp_quota_assign_id
81 AND cq.quota_id = x_quota_id
82 AND (
83 csqa.target <> cq.target
84 OR csqa.payment_amount <> cq.payment_amount
85 OR csqa.performance_goal <> cq.performance_goal
86 )
87 ) ;
88
89 IF l_return = 0 THEN
90 SELECT count(*)
91 INTO l_return
92 FROM
93 (
94 SELECT 'X'
95 FROM cn_srp_period_quotas_all cspq
96 , cn_period_quotas_all cpq
97 WHERE cspq.srp_quota_assign_id = x_srp_quota_assign_id
98 AND cpq.quota_id = x_quota_id
99 AND cspq.period_id = cpq.period_id
100 AND (
101 cpq.period_target <> cspq.target_amount
102 OR cpq.period_payment <> cspq.period_payment
103 OR cpq.performance_goal <> cspq.performance_goal_ptd
104 )
105 );
106
107 IF l_return = 0 THEN
108 SELECT count(*)
109 INTO l_return
110 FROM
111 (
112 SELECT 'X'
113 FROM cn_srp_quota_assigns_all csqa
114 , cn_quota_rules_all cqr
115 , cn_srp_quota_rules_all csqr
116 , cn_quota_rule_uplifts_all cqru
117 , cn_srp_rule_uplifts_all csru
118 WHERE csqa.srp_quota_assign_id = x_srp_quota_assign_id
119 AND cqr.quota_id = x_quota_id
120 AND csqr.srp_plan_assign_id = csqa.srp_plan_assign_id
121 AND csqr.srp_quota_assign_id = x_srp_quota_assign_id
122 AND cqr.revenue_class_id = csqr.revenue_class_id
123 AND cqru.quota_rule_id = cqr.quota_rule_id
124 AND csru.srp_quota_rule_id = csqr.srp_quota_rule_id
125 AND cqru.quota_rule_uplift_id = csru.quota_rule_uplift_id
126 AND (
127 (
128 cqr.target <> csqr.target
129 OR cqr.payment_amount <> csqr.payment_amount
130 OR cqr.performance_goal <> csqr.performance_goal
131 )
132 OR
133 (
134 cqru.payment_factor <> csru.payment_factor
135 OR cqru.quota_factor <> csru.quota_factor
136 )
137 )
138 );
139 END IF;
140 END IF;
141 RETURN l_return;
142 END IS_CUSTOMIZED;
143
144 ----------------------------------------------------------------------------
145 -- PROCEDURE Valid Assign
146 ----------------------------------------------------------------------------
147 -- Procedure Name
148 -- Valid_Assign
149 -- Purpose
150 -- To check whether a Plan Element with 'Payee Assign Flag' set is
151 -- is getting assigned to a salesrep who has payee role.
152 --
153 -- Parameters
154 -- x_srp_plan_assign_id As IN parameter
155 -- x_valid As Out parameter
156
157 PROCEDURE Valid_Assign
158 (p_srp_plan_assign_id IN NUMBER,
159 x_valid OUT NOCOPY NUMBER
160 ) IS
161 Begin
162 x_valid := 0;
163 SELECT count(*) INTO x_valid
164 FROM dual
165 WHERE EXISTS
166 (
167 SELECT 1
168 FROM cn_srp_plan_assigns_all csqa
169 WHERE csqa.srp_plan_assign_id = p_srp_plan_assign_id
170 AND EXISTS
171 (
172 SELECT 1 FROM cn_quota_assigns_all cqa
173 WHERE cqa.comp_plan_id = csqa.comp_plan_id
174 AND EXISTS
175 (
176 SELECT 1 FROM cn_quotas_all cq
177 WHERE cq.quota_id = cqa.quota_id
178 AND cq.payee_assign_flag = 'Y'
179 )
180 )
181 )
182 AND
183 EXISTS
184 (
185 SELECT 1
186 FROM cn_srp_plan_assigns_all cspa
187 WHERE cspa.srp_plan_assign_id = p_srp_plan_assign_id
188 AND EXISTS
189 (
190 SELECT 1
191 FROM cn_srp_roles csr
192 WHERE csr.salesrep_id = cspa.salesrep_id
193 AND csr.org_id = cspa.org_id
194 and csr.role_id = 54
195 )
196 );
197
198 End;
199
200
201 ----------------------------------------------------------------------------
202 -- PROCEDURE INSERT RECORD
203 ----------------------------------------------------------------------------
204 -- Procedure Name
205 -- Insert Record
206 -- Purpose
207 -- Insert srp quota assignment(s) and related records.
208 --
209 -- Notes Parameters
210 -- o Called once for each new srp plan assignment. x_srp_plan_assign_id
211
212 -- o Called when assigning quotas to plans.
213 -- Once for each srp plan assignment that x_srp_plan_assign_id
214 -- references the comp plan id on the new comp x_quota_id
215 -- plan quota assignment
216
217 PROCEDURE Insert_Record
218 ( x_srp_plan_assign_id NUMBER
219 ,x_quota_id NUMBER ) IS
220
221 l_user_id NUMBER(15);
222 l_resp_id NUMBER(15);
223 l_login_id NUMBER(15);
224 l_valid NUMBER;
225
226 CURSOR l_srp_quota_assign_csr (c_quota_id cn_quotas.quota_id%TYPE,
227 c_srp_plan_assign_id cn_srp_quota_assigns.srp_plan_assign_id%TYPE) IS
228 SELECT srp_quota_assign_id
229 FROM cn_srp_quota_assigns_all
230 WHERE srp_plan_assign_id = c_srp_plan_assign_id
231 AND quota_id = c_quota_id;
232
233 l_srp_quota_assign_c l_srp_quota_assign_csr%ROWTYPE;
234
235 CURSOR l_srp_quota_assign_csr2 (c_srp_plan_assign_id cn_srp_quota_assigns.srp_plan_assign_id%TYPE) IS
236 SELECT srp_quota_assign_id, quota_id
237 FROM cn_srp_quota_assigns_all
238 WHERE srp_plan_assign_id = c_srp_plan_assign_id;
239
240 l_srp_quota_assign_c2 l_srp_quota_assign_csr2%ROWTYPE;
241
242 CURSOR l_rollover_quota_csr (c_quota_id cn_quotas.quota_id%TYPE) IS
243 SELECT *
244 FROM cn_rollover_quotas_all
245 WHERE quota_id = c_quota_id;
246
247 l_rollover_quota_c l_rollover_quota_csr%ROWTYPE;
248
249 l_rowid VARCHAR2(30);
250 l_srp_rollover_quota_id NUMBER := NULL;
251
252 l_init_msg_list VARCHAR2(32000) := FND_API.G_FALSE;
253
254 BEGIN
255
256 l_user_id := fnd_global.user_id;
257 l_resp_id := fnd_global.resp_id;
258 l_login_id := fnd_global.login_id;
259
260 Valid_Assign (
261 p_srp_plan_assign_id => x_srp_plan_assign_id,
262 x_valid => l_valid
263 );
264
265 IF ( l_valid > 0 ) THEN
266 --FND_MSG_PUB.initialize;
267 IF FND_API.to_Boolean( l_init_msg_list ) THEN
268 FND_MSG_PUB.initialize;
269 END IF;
270 fnd_message.set_name('CN', 'CN_ROLE_PLAN_ASGN_PAYEE_ERROR');
271 FND_MSG_PUB.Add;
272 RAISE FND_API.G_EXC_ERROR;
273 END IF;
274
275 IF (x_srp_plan_assign_id IS NOT NULL AND x_quota_id IS NOT NULL) THEN
276
277 -- inserting a new cn_quota_assign
278 -- called once for each srp_plan_assignment using the comp_plan_id
279
280 INSERT INTO cn_srp_quota_assigns_all
281 (
282 srp_quota_assign_id
283 ,srp_plan_assign_id
284 ,quota_id
285 ,target
286 ,customized_flag
287 ,period_target_dist_rule_code
288 ,quota_type_code
289 ,payment_amount
290 ,performance_goal
291 ,period_target_unit_code
292 ,creation_date
293 ,created_by
294 ,last_update_date
295 ,last_updated_by
296 ,last_update_login
297 ,org_id)
298 SELECT
299 cn_srp_quota_assigns_s.nextval
300 ,x_srp_plan_assign_id
301 ,q.quota_id
302 ,q.target
303 ,cn_system_parameters.value('CN_CUSTOM_FLAG', org_id)
304 ,'EQUAL' -- Currently support only one method
305 ,q.quota_type_code
306 ,q.payment_amount
307 ,q.performance_goal
308 ,'PERIOD' -- Could let the user define this in cn_quotas
309 ,sysdate
310 ,l_user_id
311 ,sysdate
312 ,l_user_id
313 ,l_login_id
314 ,org_id
315 FROM cn_quotas_all q
316 WHERE q.quota_id = x_quota_id
317 ;
318
319 -- clku, populate_srp_rollover_quotas
320 FOR l_srp_quota_assign_c IN l_srp_quota_assign_csr(x_quota_id, x_srp_plan_assign_id) LOOP
321 FOR l_rollover_quota_c IN l_rollover_quota_csr(x_quota_id) LOOP
322
323 CN_SRP_ROLLOVER_QUOTAS_PKG.INSERT_ROW
324 (X_ROWID => l_rowid,
325 X_SRP_ROLLOVER_QUOTA_ID => l_srp_rollover_quota_id,
326 X_SRP_QUOTA_ASSIGN_ID => l_srp_quota_assign_c.srp_quota_assign_id,
327 X_ROLLOVER_QUOTA_ID => l_rollover_quota_c.rollover_quota_id,
328 X_QUOTA_ID => x_quota_id,
329 X_SOURCE_QUOTA_ID => l_rollover_quota_c.source_quota_id,
330 X_ROLLOVER => l_rollover_quota_c.rollover,
331 X_ATTRIBUTE_CATEGORY => l_rollover_quota_c.attribute_category,
332 X_ATTRIBUTE1 => l_rollover_quota_c.attribute1,
333 X_ATTRIBUTE2 => l_rollover_quota_c.attribute2,
334 X_ATTRIBUTE3 => l_rollover_quota_c.attribute3,
335 X_ATTRIBUTE4 => l_rollover_quota_c.attribute4,
336 X_ATTRIBUTE5 => l_rollover_quota_c.attribute5,
337 X_ATTRIBUTE6 => l_rollover_quota_c.attribute6,
338 X_ATTRIBUTE7 => l_rollover_quota_c.attribute7,
339 X_ATTRIBUTE8 => l_rollover_quota_c.attribute8,
340 X_ATTRIBUTE9 => l_rollover_quota_c.attribute9,
341 X_ATTRIBUTE10 => l_rollover_quota_c.attribute10,
342 X_ATTRIBUTE11 => l_rollover_quota_c.attribute11,
343 X_ATTRIBUTE12 => l_rollover_quota_c.attribute12,
344 X_ATTRIBUTE13 => l_rollover_quota_c.attribute13,
345 X_ATTRIBUTE14 => l_rollover_quota_c.attribute14,
346 X_ATTRIBUTE15 => l_rollover_quota_c.attribute15,
347 X_CREATED_BY => fnd_global.user_id,
348 X_CREATION_DATE => sysdate,
349 X_LAST_UPDATE_DATE => sysdate,
350 X_LAST_UPDATED_BY => fnd_global.user_id,
351 X_LAST_UPDATE_LOGIN => fnd_global.login_id
352 );
353
354 END LOOP;
355
356 END LOOP;
357 ELSIF (x_srp_plan_assign_id IS NOT NULL AND x_quota_id IS NULL) THEN
358
359 -- called from after insert of srp plan assignment
360 -- this is also how plan assign period changes are done
361 INSERT INTO cn_srp_quota_assigns_all
362 (
363 srp_quota_assign_id
364 ,srp_plan_assign_id
365 ,quota_id
366 ,target
367 ,customized_flag
368 ,period_target_dist_rule_code
369 ,quota_type_code
370 ,payment_amount
371 ,performance_goal
372 ,period_target_unit_code
373 ,creation_date
374 ,created_by
375 ,last_update_date
376 ,last_updated_by
377 ,last_update_login
378 ,org_id)
379 SELECT
380 cn_srp_quota_assigns_s.nextval
381 ,pa.srp_plan_assign_id
382 ,q.quota_id
383 ,q.target
384 ,cn_system_parameters.value('CN_CUSTOM_FLAG', q.org_id)
385 ,'EQUAL'
386 ,q.quota_type_code
387 ,q.payment_amount
388 ,q.performance_goal
389 ,'PERIOD'
390 ,sysdate
391 ,l_user_id
392 ,sysdate
393 ,l_user_id
394 ,l_login_id
395 ,q.org_id
396 FROM cn_quotas_all q
397 ,cn_srp_plan_assigns_all pa
398 ,cn_quota_assigns_all qa
399 WHERE pa.srp_plan_assign_id = x_srp_plan_assign_id
400 AND pa.comp_plan_id = qa.comp_plan_id
401 AND q.quota_id = qa.quota_id
402 ;
403
404 -- clku, populate_srp_rollover_quotas
405 FOR l_srp_quota_assign_c2 IN l_srp_quota_assign_csr2(x_srp_plan_assign_id) LOOP
406 FOR l_rollover_quota_c IN l_rollover_quota_csr(l_srp_quota_assign_c2.quota_id) LOOP
407
408 CN_SRP_ROLLOVER_QUOTAS_PKG.INSERT_ROW
409 (X_ROWID => l_rowid,
410 X_SRP_ROLLOVER_QUOTA_ID => l_srp_rollover_quota_id,
411 X_SRP_QUOTA_ASSIGN_ID => l_srp_quota_assign_c2.srp_quota_assign_id,
412 X_ROLLOVER_QUOTA_ID => l_rollover_quota_c.rollover_quota_id,
413 X_QUOTA_ID => l_srp_quota_assign_c2.quota_id,
414 X_SOURCE_QUOTA_ID => l_rollover_quota_c.source_quota_id,
415 X_ROLLOVER => l_rollover_quota_c.rollover,
416 X_ATTRIBUTE_CATEGORY => l_rollover_quota_c.attribute_category,
417 X_ATTRIBUTE1 => l_rollover_quota_c.attribute1,
418 X_ATTRIBUTE2 => l_rollover_quota_c.attribute2,
419 X_ATTRIBUTE3 => l_rollover_quota_c.attribute3,
420 X_ATTRIBUTE4 => l_rollover_quota_c.attribute4,
421 X_ATTRIBUTE5 => l_rollover_quota_c.attribute5,
422 X_ATTRIBUTE6 => l_rollover_quota_c.attribute6,
423 X_ATTRIBUTE7 => l_rollover_quota_c.attribute7,
424 X_ATTRIBUTE8 => l_rollover_quota_c.attribute8,
425 X_ATTRIBUTE9 => l_rollover_quota_c.attribute9,
426 X_ATTRIBUTE10 => l_rollover_quota_c.attribute10,
427 X_ATTRIBUTE11 => l_rollover_quota_c.attribute11,
428 X_ATTRIBUTE12 => l_rollover_quota_c.attribute12,
429 X_ATTRIBUTE13 => l_rollover_quota_c.attribute13,
430 X_ATTRIBUTE14 => l_rollover_quota_c.attribute14,
431 X_ATTRIBUTE15 => l_rollover_quota_c.attribute15,
432 X_CREATED_BY => fnd_global.user_id,
433 X_CREATION_DATE => sysdate,
434 X_LAST_UPDATE_DATE => sysdate,
435 X_LAST_UPDATED_BY => fnd_global.user_id,
436 X_LAST_UPDATE_LOGIN => fnd_global.login_id
437 );
438
439 END LOOP;
440 END LOOP;
441 END IF;
442
443 -- period quotas maintained for all quota types.
444 -- Must be maintained before revenue classes because these records
445 -- drive the creation of period dependent rev class records
446
447 -- Feb24,99
448 -- Change from ,x_quota_id => null
449 -- to ,x_quota_id => x_quota_id
450 -- Start Date, End Date added on this package on 10/JUN/99
451 cn_srp_period_quotas_pkg.insert_record
452 (
453 x_srp_plan_assign_id => x_srp_plan_assign_id
454 ,x_quota_id => x_quota_id
455 ,x_start_period_id => NULL
456 ,x_end_period_id => NULL
457 ,x_start_date => NULL
458 ,x_end_date => NULL );
459
460 -- This procedure will ensure that only target and revenue quota types
461 -- get quota rules
462 cn_srp_quota_rules_pkg.insert_record
463 (
464 x_srp_plan_assign_id => x_srp_plan_assign_id
465 ,x_quota_id => x_quota_id
466 ,x_quota_rule_id => NULL
467 ,x_revenue_class_id => NULL);
468
469 -- This procedure will ensure that only target and revenue quota types
470 -- get rate assignments
471
472 cn_srp_rate_assigns_pkg.insert_record
473 (
474 x_srp_plan_assign_id => x_srp_plan_assign_id
475 ,x_srp_quota_assign_id => NULL
476 ,x_srp_rate_assign_id => NULL
477 ,x_quota_id => x_quota_id
478 ,x_rate_schedule_id => NULL
479 ,x_rate_tier_id => NULL
480 ,x_commission_rate => NULL
481 ,x_commission_amount => NULL
482 ,x_disc_rate_table_flag => NULL );
483
484 END insert_record;
485 ----------------------------------------------------------------------------
486 -- PROCEDURE LOCK_RECORD
487 ----------------------------------------------------------------------------
488 PROCEDURE lock_record
489 ( x_srp_quota_assign_id NUMBER
490 ,x_target NUMBER
491 ,x_customized_flag VARCHAR2
492 ,x_period_target_dist_rule_code VARCHAR2
493 ,x_payment_amount NUMBER
494 ,x_performance_goal NUMBER
495 ,x_period_target_unit_code VARCHAR2) IS
496
497 CURSOR C IS
498 SELECT *
499 FROM cn_srp_quota_assigns_all
500 WHERE srp_quota_assign_id = x_srp_quota_assign_id
501 FOR UPDATE OF srp_quota_assign_id NOWAIT;
502 Recinfo C%ROWTYPE;
503
504 BEGIN
505
506 OPEN C;
507 FETCH C INTO Recinfo;
508
509 IF (C%NOTFOUND) THEN
510 CLOSE C;
511 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
512 app_exception.raise_exception;
513 END IF;
514 CLOSE C;
515
516 -- The target column should be nullable, but it isn't. To get around this
517 -- the view decodes the column to null depending upon the quota type
518 -- the column value is actually zero. We nvl the x_target to decode the
519 -- form field null value into its real column value
520
521 IF ( (recinfo.target = nvl(x_target,0) )
522 AND (recinfo.customized_flag = x_customized_flag )
523 AND (recinfo.period_target_dist_rule_code =
524 x_period_target_dist_rule_code )
525 ) THEN
526 RETURN;
527 ELSE
528
529 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
530 app_exception.raise_exception;
531 END IF;
532
533 END Lock_Record;
534 ----------------------------------------------------------------------------
535 -- PROCEDURE UPDATE_RECORD
536 ----------------------------------------------------------------------------
537 -- Procedure Name
538 -- Update_Record
539 -- Purpose
540 --
541 -- Notes
542 -- o Called when the srp quota target or customized_flag are changed
543 -- Simply sets the columns to the field values.
544
545 PROCEDURE update_record(
546 x_srp_quota_assign_id NUMBER
547 ,x_target NUMBER
548 ,x_target_old NUMBER
549 ,x_start_period_id NUMBER
550 ,x_salesrep_id NUMBER
551 ,x_customized_flag VARCHAR2
552 ,x_customized_flag_old VARCHAR2
553 ,x_quota_id NUMBER
554 ,x_rate_schedule_id NUMBER
555 ,x_period_target_dist_rule_code VARCHAR2
556 ,x_attributes_changed VARCHAR2
557 ,x_distribute_target_flag VARCHAR2
558 ,x_payment_amount NUMBER
559 ,x_payment_amount_old NUMBER
560 ,x_performance_goal NUMBER
561 ,x_performance_goal_old NUMBER
562 ,x_quota_type_code VARCHAR2
563 ,x_period_target_unit_code VARCHAR2
564 ,x_last_update_date DATE
565 ,x_last_updated_by NUMBER
566 ,x_last_update_login NUMBER) IS
567
568
569 CURSOR sqr_curs IS
570 SELECT qr.target,
571 qr.payment_amount,
572 qr.performance_goal,
573 sqr.srp_quota_rule_id
574 FROM cn_quota_rules_all qr,
575 cn_srp_quota_rules_all sqr
576 WHERE qr.quota_rule_id = sqr.quota_rule_id
577 AND sqr.srp_quota_assign_id = x_srp_quota_assign_id
578 ;
579
580 l_recinfo sqr_curs%ROWTYPE;
581
582 CURSOR srp_rollover_quota_curs IS
583 SELECT *
584 FROM cn_srp_rollover_quotas_all
585 WHERE srp_quota_assign_id = x_srp_quota_assign_id;
586
587 l_srp_rollover_quota srp_rollover_quota_curs%ROWTYPE;
588
589 l_count NUMBER := 0;
590 l_srp_plan_assign_id cn_srp_quota_assigns.srp_plan_assign_id%TYPE;
591 l_quota_id cn_srp_quota_assigns.quota_id%TYPE;
592
593
594 BEGIN
595 IF x_srp_quota_assign_id IS NOT NULL THEN
596
597 -- Called from srp plan assignment form
598 UPDATE cn_srp_quota_assigns_all
599 SET
600 -- Should be an optional column as it can be null for
601 -- quota types 'revenue' and 'draw'. But it is mandatory and
602 -- this nvl protects against a null value coming back from the
603 -- form
604 target = NVL(x_target,0)
605 ,customized_flag = x_customized_flag
606 ,period_target_dist_rule_code = x_period_target_dist_rule_code
607 ,payment_amount = x_payment_amount
608 ,performance_goal = x_performance_goal
609 ,period_target_unit_code = x_period_target_unit_code
610 ,last_update_date = x_last_update_date
611 ,last_updated_by = x_last_updated_by
612 ,last_update_login = x_last_update_login
613 WHERE srp_quota_assign_id = x_srp_quota_assign_id
614 ;
615
616 IF (sql%notfound) THEN
617 raise no_data_found;
618 END IF;
619
620 IF x_customized_flag = 'N'
621 AND x_customized_flag_old = 'Y' THEN
622
623 -- get plan assign ID
624 SELECT srp_plan_assign_id, quota_id
625 INTO l_srp_plan_assign_id, l_quota_id
626 FROM cn_srp_quota_assigns_all
627 WHERE srp_quota_assign_id = x_srp_quota_assign_id;
628
629 IF IS_CUSTOMIZED(x_srp_quota_assign_id,x_quota_id) > 0 THEN
630 -- rollback all the changes
631
632 -- revert changes on srp_quota_assign
633 -- to do this, simply delete records so calc will use default rate
634
635 update cn_srp_quota_assigns_all
636 set (target,
637 payment_amount,
638 performance_goal) =
639 (select target,
640 payment_amount,
641 performance_goal
642 from cn_quotas_all
643 where quota_id = x_quota_id)
644 where srp_quota_assign_id = x_srp_quota_assign_id;
645
646 -- clku, revert changes in cn_srp_rolling_quotas
647
648 FOR l_srp_rollover_quota IN srp_rollover_quota_curs LOOP
649
650 update cn_srp_rollover_quotas_all csrq
651 set rollover =
652 ( select rollover
653 from cn_rollover_quotas_all crq
654 where crq.rollover_quota_id = l_srp_rollover_quota.rollover_quota_id)
655 where srp_rollover_quota_id = l_srp_rollover_quota.srp_rollover_quota_id;
656 END LOOP;
657
658 -- revert changes on srp_quota_rules and quota_rule_uplifts
659 OPEN sqr_curs;
660 LOOP
661 FETCH sqr_curs INTO l_recinfo;
662 EXIT WHEN sqr_curs%notfound;
663
664 UPDATE cn_srp_quota_rules_all
665 SET target = l_recinfo.target,
666 payment_amount = l_recinfo.payment_amount,
667 performance_goal = l_recinfo.performance_goal
668 WHERE srp_quota_rule_id = l_recinfo.srp_quota_rule_id
669 ;
670
671 UPDATE cn_srp_rule_uplifts_all srp
672 SET (payment_factor, quota_factor)
673 = (SELECT payment_factor, quota_factor
674 FROM cn_quota_rule_uplifts_all q
675 WHERE q.quota_rule_uplift_id = srp.quota_rule_uplift_id)
676 WHERE srp.srp_quota_rule_id = l_recinfo.srp_quota_rule_id
677 ;
678
679 END LOOP;
680 CLOSE sqr_curs;
681
682 -- revert srp_period_quota
683 SELECT count(1)
684 INTO l_count
685 FROM cn_srp_period_quotas_all
686 WHERE srp_quota_assign_id = x_srp_quota_assign_id
687 AND rownum = 1
688 ;
689
690 IF l_count > 0 THEN
691 cn_srp_rate_assigns_pkg.delete_record
692 ( x_srp_plan_assign_id => l_srp_plan_assign_id
693 ,x_srp_rate_assign_id => null
694 ,x_quota_id => l_quota_id
695 ,x_rate_schedule_id => null
696 ,x_rate_tier_id => null);
697
698 cn_srp_period_quotas_pkg.delete_record
699 (
700 x_srp_plan_assign_id => l_srp_plan_assign_id
701 ,x_quota_id => x_quota_id
702 ,x_start_period_id => null
703 ,x_end_period_id => null);
704
705 cn_srp_period_quotas_pkg.insert_record
706 (
707 x_srp_plan_assign_id => l_srp_plan_assign_id
708 ,x_quota_id => x_quota_id
709 ,x_start_period_id => null
710 ,x_end_period_id => null);
711
712 END IF; -- l_count
713
714 END IF; -- is_customized
715
716 -- Revert the SRP level Commission Rates, work with arch of
717 -- Sparse Rate Table implementation
718
719 cn_srp_rate_assigns_pkg.delete_record
720 ( x_srp_plan_assign_id => l_srp_plan_assign_id
721 ,x_quota_id => x_quota_id
722 -- not used
723 ,x_srp_rate_assign_id => null
724 ,x_rate_schedule_id => null
725 ,x_rt_quota_asgn_id => null
726 ,x_rate_tier_id => null);
727
728 END IF; -- custom flag Y -> N
729
730 IF x_customized_flag = 'Y' and
731 x_customized_flag_old = 'N' THEN
732 -- create srp rate assigns
733 SELECT srp_plan_assign_id, quota_id
734 INTO l_srp_plan_assign_id, l_quota_id
735 FROM cn_srp_quota_assigns_all
736 WHERE srp_quota_assign_id = x_srp_quota_assign_id;
737
738 cn_srp_rate_assigns_pkg.insert_record
739 (x_srp_plan_assign_id => l_srp_plan_assign_id
740 ,x_srp_quota_assign_id => NULL
741 ,x_srp_rate_assign_id => NULL
742 ,x_quota_id => l_quota_id
743 ,x_rate_schedule_id => NULL
744 ,x_rate_tier_id => NULL
745 ,x_commission_rate => NULL
746 ,x_commission_amount => NULL
747 ,x_disc_rate_table_flag => NULL );
748 END IF; -- custom flag N -> Y
749 END IF; -- x_srp_quota_assign_id not null
750 -- End Update Record.
751
752 END Update_Record;
753 ----------------------------------------------------------------------------
754 -- PROCEDURE UPDATE_SRP_QUOTA
755 ----------------------------------------------------------------------------
756 -- Procedure Name
757 -- Update_srp_quota
758 -- Purpose
759 -- Maintain srp tables after a change to the source quota
760 -- Notes
761 -- o Called from cn_quotas_pkg.update_record
762 -- o Split from main update record for readability
763
764 PROCEDURE update_srp_quota(
765 x_quota_id NUMBER
766 ,x_target NUMBER
767 ,x_payment_amount NUMBER
768 ,x_performance_goal NUMBER
769 ,x_rate_schedule_id NUMBER -- obsolete
770 ,x_rate_schedule_id_old NUMBER -- obsolete
771 ,x_disc_rate_schedule_id NUMBER -- obsolete
772 ,x_disc_rate_schedule_id_old NUMBER -- obsolete
773 ,x_payment_type_code VARCHAR2 -- obsolete
774 ,x_payment_type_code_old VARCHAR2 -- obsolete
775 ,x_quota_type_code VARCHAR2
776 ,x_quota_type_code_old VARCHAR2
777 ,x_period_type_code VARCHAR2 -- obsolete
778 ,x_calc_formula_id NUMBER := NULL
779 ,x_calc_formula_id_old NUMBER := NULL ) IS
780
781
782 Cursor quota_rt_assigns_curs IS
783 SELECT rate_schedule_id, calc_formula_id
784 FROM cn_rt_quota_asgns_all
785 WHERE quota_id = x_quota_id ;
786
787 l_rate_schedule_id NUMBER;
788 l_calc_formula_id NUMBER;
789
790
791 BEGIN
792
793 -- update the srp quota assigns.
794 -- Payment type code is obsolete, still the below stmt works.
795 -- Modified , removed to check the quota type and payment type
796 UPDATE cn_srp_quota_assigns_all
797 SET target = decode(customized_flag ,'N',x_target ,target)
798 ,payment_amount = decode(customized_flag,'N',x_payment_amount
799 ,payment_amount)
800 ,performance_goal = Decode( customized_flag, 'N', x_performance_goal,
801 performance_goal)
802 ,quota_type_code = x_quota_type_code
803 WHERE quota_id = x_quota_id
804 ;
805
806 IF SQL%FOUND THEN
807 -- If the quota type has been changed from one that supports
808 -- rate tables to one that does not
809 -- OR the a different rate schedule has been assigned to the quota
810 -- we delete all the srp tiers.
811
812 IF
813 (( x_quota_type_code IN ('NONE' )
814 AND x_quota_type_code_old IN ('EXTERNAL',
815 'FORMULA') )
816 OR (nvl(x_calc_formula_id_old,-99) <> nvl(x_calc_formula_id,-99))
817 ) THEN
818
819 open quota_rt_assigns_curs;
820 Loop
821 fetch quota_rt_assigns_curs into l_rate_schedule_id, l_calc_formula_id;
822 exit when quota_rt_assigns_curs%notfound;
823 cn_srp_rate_assigns_pkg.delete_record
824 (
825 x_srp_plan_assign_id => null
826 ,x_srp_rate_assign_id => null
827 ,x_quota_id => x_quota_id
828 ,x_rate_schedule_id => l_rate_schedule_id
829 ,x_rate_tier_id => null);
830
831 end loop;
832 close quota_rt_assigns_curs;
833
834 -- If the rate schedule has changed we must insert the new tiers
835 -- Pass the Calc formula id, yet to be identified.
836
837 IF ( nvl(x_calc_formula_id_old,-99)
838 <> nvl(x_calc_formula_id ,-99) ) THEN
839
840 open quota_rt_assigns_curs;
841 Loop
842 fetch quota_rt_assigns_curs into l_rate_schedule_id, l_calc_formula_id;
843
844 exit when quota_rt_assigns_curs%notfound;
845 cn_srp_rate_assigns_pkg.insert_record
846 (
847 x_srp_plan_assign_id => null
848 ,x_srp_quota_assign_id => null
849 ,x_srp_rate_assign_id => null
850 ,x_quota_id => x_quota_id
851 ,x_rate_schedule_id => l_rate_schedule_id
852 ,x_rate_tier_id => null
853 ,x_commission_rate => null
854 ,x_commission_amount => null
855 ,x_disc_rate_table_flag => 'N' );
856
857 end loop;
858 close quota_rt_assigns_curs;
859 END IF;
860 END IF;
861 END IF;
862
863 END Update_Srp_Quota;
864 ----------------------------------------------------------------------------
865 -- PROCEDURE DELETE_RECORD
866 ----------------------------------------------------------------------------
867 --
868 -- Procedure Name
869 -- Delete_Row
870 -- Purpose
871 -- Delete quota assignment(s) from each rep assigned to the comp plan
872 --
873 -- Notes Passed Parameters
874 -- o Called once for each deleted srp plan assignment. x_srp_plan_assign_id
875
876 -- o Called when a quota assignment is deleted from the x_srp_plan_assign_id
877 -- source comp plans. Called once for each srp plan x_quota_id
878 -- assignment referencing the comp plan on the deleted
879 -- assignment
880
881
882 PROCEDURE delete_record
883 ( x_srp_plan_assign_id NUMBER
884 ,x_quota_id NUMBER ) IS
885
886
887 CURSOR srp_quota_curs IS
888 Select srp_quota_assign_id
889 , quota_id
890 FROM CN_SRP_QUOTA_ASSIGNS_ALL
891 WHERE srp_plan_assign_id = x_srp_plan_assign_id ;
892
893 CURSOR srp_quota_curs_quota IS
894 Select srp_quota_assign_id
895 , quota_id
896 FROM CN_SRP_QUOTA_ASSIGNS_ALL
897 WHERE srp_plan_assign_id = x_srp_plan_assign_id
898 AND quota_id = x_quota_id;
899
900 recinfo srp_quota_curs%ROWTYPE;
901
902 CURSOR l_srp_rollover_quota_csr (c_srp_quota_assign_id cn_srp_quota_assigns.srp_quota_assign_id%TYPE) IS
903 SELECT *
904 FROM cn_srp_rollover_quotas_all
905 WHERE srp_quota_assign_id = c_srp_quota_assign_id;
906
907 l_srp_rollover_quota_c l_srp_rollover_quota_csr%ROWTYPE;
908
909 CURSOR get_payee_del_strdt_cur(p_srp_quota_assign_id NUMBER,
910 p_quota_id NUMBER) IS
911 SELECT srp_payee_assign_id
912 FROM cn_srp_payee_assigns
913 WHERE srp_quota_assign_id = p_srp_quota_assign_id
914 AND quota_id = p_quota_id ;
915
916 BEGIN
917
918 -- o Called once for each deleted srp plan assignment. x_srp_plan_assign_id
919 IF x_srp_plan_assign_id IS NOT NULL AND x_quota_id IS NULL THEN
920 -- Delete srp quota assigns
921
922 open srp_quota_curs;
923 Loop
924 fetch srp_quota_curs into recinfo;
925 exit when srp_quota_curs%NOTFOUND;
926
927 -- clean out payee assignments
928 FOR p IN get_payee_del_strdt_cur
929 (recinfo.srp_quota_assign_id,
930 recinfo.quota_id) LOOP
931
932 cn_srp_payee_assigns_pkg.delete_record
933 (p_srp_payee_assign_id => p.srp_payee_assign_id);
934 END LOOP;
935
936 -- Delete cn_srp_rollover_quotas records
937
938 FOR l_srp_rollover_quota_c IN l_srp_rollover_quota_csr(recinfo.srp_quota_assign_id) LOOP
939 CN_SRP_ROLLOVER_QUOTAS_PKG.DELETE_ROW
940 (X_SRP_ROLLOVER_QUOTA_ID => l_srp_rollover_quota_c.srp_rollover_quota_id);
941 END LOOP;
942 END LOOP;
943 close srp_quota_curs;
944
945 -- delete from cn_srp_quota_assigns
946 -- where srp_plan_assign_id = x_srp_plan_assign_id ;
947
948 ELSIF x_srp_plan_assign_id IS NOT NULL AND x_quota_id IS NOT NULL THEN
949
950 -- Deleting a source quota assignment from cn_quota_assigns
951 -- Delete srp Payee Assigns
952
953 open srp_quota_curs_quota;
954 Loop
955 fetch srp_quota_curs_quota into recinfo;
956 exit when srp_quota_curs_quota%NOTFOUND;
957
958 -- clean out payee assignments
959 FOR p IN get_payee_del_strdt_cur
960 (recinfo.srp_quota_assign_id,
961 recinfo.quota_id) LOOP
962
963 cn_srp_payee_assigns_pkg.delete_record
964 (p_srp_payee_assign_id => p.srp_payee_assign_id);
965
966 END LOOP;
967
968 FOR l_srp_rollover_quota_c IN l_srp_rollover_quota_csr(recinfo.srp_quota_assign_id) LOOP
969
970 CN_SRP_ROLLOVER_QUOTAS_PKG.DELETE_ROW
971 (X_SRP_ROLLOVER_QUOTA_ID => l_srp_rollover_quota_c.srp_rollover_quota_id);
972 END LOOP;
973
974 END LOOP;
975 close srp_quota_curs_quota;
976
977 END IF;
978
979 IF x_srp_plan_assign_id IS NOT NULL THEN
980
981 cn_srp_period_quotas_pkg.delete_record
982 ( x_srp_plan_assign_id => x_srp_plan_assign_id
983 ,x_quota_id => x_quota_id
984 ,x_start_period_id => null
985 ,x_end_period_id => null);
986
987 -- delete srp quota rules
988 cn_srp_quota_rules_pkg.delete_record
989 ( x_srp_plan_assign_id => x_srp_plan_assign_id
990 ,x_srp_quota_assign_id => null
991 ,x_quota_id => x_quota_id
992 ,x_quota_rule_id => null
993 ,x_revenue_class_id => null);
994
995 -- delete srp rate assigns
996
997 cn_srp_rate_assigns_pkg.delete_record
998 ( x_srp_plan_assign_id => x_srp_plan_assign_id
999 ,x_srp_rate_assign_id => null
1000 ,x_quota_id => x_quota_id
1001 ,x_rate_schedule_id => null
1002 ,x_rate_tier_id => null);
1003 END IF;
1004
1005 DELETE FROM cn_srp_quota_assigns_all
1006 WHERE Srp_plan_assign_id = x_srp_plan_assign_id
1007 AND quota_id = nvl(x_quota_id, quota_id);
1008 END delete_record;
1009
1010 END CN_SRP_Quota_Assigns_PKG;