[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_RULES_PKG
Source
1 PACKAGE BODY cn_quota_rules_pkg AS
2 /* $Header: cnpliqrb.pls 120.5.12000000.2 2007/10/09 22:19:24 rnagired ship $ */
3
4 /*
5 Date Name Description
6 ---------------------------------------------------------------------------+
7 15-FEB-95 P Cook Unit tested
8 14-APR-95 P Cook Moved %notfound in delete_record in front of srp
9 deletes to prevent no recs found error on plan
10 no assigned to salesreps.
11 07-AUG-95 P Cook Pass CHANGE_RULE to mark_event instead of NEW_RULE
12 17-MAR-99 S Kumar Added the Start Date and End Date Column
13 Commented the code for checking the Active Hierarchy.
14 under discussion on 05/12/99
15 Update the Revenue Class column in trx factor is
16 included
17 Modified more during 3i Changes.
18 Name
19
20 Purpose
21
22 Notes
23
24
25 */
26
27 ---------------------------------------------------------------------------+
28 -- Variables
29 ---------------------------------------------------------------------------+
30 g_program_type VARCHAR2 (30) := NULL;
31
32 ---------------------------------------------------------------------------+
33 -- Private Routines
34 ---------------------------------------------------------------------------+
35 -- Name
36
37 -- Purpose
38
39 -- Notes
40
41 ---------------------------------------------------------------------------+
42 -- Get UID
43 ---------------------------------------------------------------------------+
44 PROCEDURE get_uid (
45 x_quota_rule_id IN OUT NOCOPY NUMBER
46 )
47 IS
48 BEGIN
49 SELECT cn_quota_rules_s.NEXTVAL
50 INTO x_quota_rule_id
51 FROM SYS.DUAL;
52 END get_uid;
53
54 -- Name
55
56 -- Purpose
57
58 -- Notes
59
60 ---------------------------------------------------------------------------+
61 -- Update_quota
62 ---------------------------------------------------------------------------+
63 PROCEDURE update_quota (
64 p_quota_id NUMBER
65 )
66 IS
67 l_target NUMBER;
68 l_payment_amount NUMBER;
69 l_performance_goal NUMBER;
70 g_last_update_date DATE := SYSDATE;
71 g_last_updated_by NUMBER := fnd_global.user_id;
72 g_creation_date DATE := SYSDATE;
73 g_created_by NUMBER := fnd_global.user_id;
74 g_last_update_login NUMBER := fnd_global.login_id;
75 g_rowid VARCHAR2 (30);
76 g_program_type VARCHAR2 (30);
77 l_pe_rec cn_quotas%ROWTYPE;
78 BEGIN
79 SELECT *
80 INTO l_pe_rec
81 FROM cn_quotas
82 WHERE quota_id = p_quota_id;
83
84 IF l_pe_rec.addup_from_rev_class_flag = 'Y'
85 THEN
86 SELECT SUM (NVL (target, 0)),
87 SUM (NVL (payment_amount, 0)),
88 SUM (NVL (performance_goal, 0))
89 INTO l_target,
90 l_payment_amount,
91 l_performance_goal
92 FROM cn_quota_rules
93 WHERE quota_id = p_quota_id;
94
95 cn_quotas_pkg.begin_record (x_operation => 'UPDATE',
96 x_rowid => g_rowid,
97 x_quota_id => l_pe_rec.quota_id,
98 x_object_version_number => l_pe_rec.object_version_number,
99 x_name => l_pe_rec.NAME,
100 x_target => NVL (l_target, 0),
101 x_quota_type_code => l_pe_rec.quota_type_code,
102 x_usage_code => NULL,
103 x_payment_amount => NVL (l_payment_amount, 0),
104 x_description => l_pe_rec.description,
105 x_start_date => l_pe_rec.start_date,
106 x_end_date => l_pe_rec.end_date,
107 x_quota_status => l_pe_rec.quota_status,
108 x_calc_formula_id => l_pe_rec.calc_formula_id,
109 x_incentive_type_code => l_pe_rec.incentive_type_code,
110 x_credit_type_id => l_pe_rec.credit_type_id,
111 x_rt_sched_custom_flag => l_pe_rec.rt_sched_custom_flag,
112 x_package_name => l_pe_rec.package_name,
113 x_performance_goal => NVL (l_performance_goal, 0),
114 x_interval_type_id => l_pe_rec.interval_type_id,
115 x_payee_assign_flag => l_pe_rec.payee_assign_flag,
116 x_vesting_flag => l_pe_rec.vesting_flag,
117 x_expense_account_id => l_pe_rec.expense_account_id,
118 x_liability_account_id => l_pe_rec.liability_account_id,
119 x_quota_group_code => l_pe_rec.quota_group_code,
120 x_payment_group_code => l_pe_rec.payment_group_code,
121 x_quota_unspecified => NULL,
122 x_last_update_date => g_last_update_date,
123 x_last_updated_by => g_last_updated_by,
124 x_creation_date => g_creation_date,
125 x_created_by => g_created_by,
126 x_last_update_login => g_last_update_login,
127 x_program_type => g_program_type,
128 --x_status_code => NULL,
129 x_period_type_code => NULL,
130 x_start_num => NULL,
131 x_end_num => NULL,
132 x_addup_from_rev_class_flag => l_pe_rec.addup_from_rev_class_flag,
133 x_attribute_category => l_pe_rec.attribute_category,
134 x_attribute1 => l_pe_rec.attribute1,
135 x_attribute2 => l_pe_rec.attribute2,
136 x_attribute3 => l_pe_rec.attribute3,
137 x_attribute4 => l_pe_rec.attribute4,
138 x_attribute5 => l_pe_rec.attribute5,
139 x_attribute6 => l_pe_rec.attribute6,
140 x_attribute7 => l_pe_rec.attribute7,
141 x_attribute8 => l_pe_rec.attribute8,
142 x_attribute9 => l_pe_rec.attribute9,
143 x_attribute10 => l_pe_rec.attribute10,
144 x_attribute11 => l_pe_rec.attribute11,
145 x_attribute12 => l_pe_rec.attribute12,
146 x_attribute13 => l_pe_rec.attribute13,
147 x_attribute14 => l_pe_rec.attribute14,
148 x_attribute15 => l_pe_rec.attribute15,
149 x_indirect_credit => l_pe_rec.indirect_credit,
150 x_org_id => l_pe_rec.org_id,
151 x_salesrep_end_flag => l_pe_rec.salesreps_enddated_flag
152 );
153 END IF;
154 END update_quota;
155
156 -- Name
157
158 -- Purpose
159
160 -- Notes
161
162 -------------------------------------------------------------------------+
163 -- Insert_record
164 ---------------------------------------------------------------------------+
165 PROCEDURE INSERT_RECORD (
166 x_org_id NUMBER,
167 x_revenue_class_id NUMBER,
168 x_quota_id NUMBER,
169 x_last_update_date DATE,
170 x_last_updated_by NUMBER,
171 x_creation_date DATE,
172 x_created_by NUMBER,
173 x_last_update_login NUMBER,
174 x_target NUMBER,
175 x_payment_amount NUMBER,
176 x_performance_goal NUMBER,
177 x_quota_rule_id IN OUT NOCOPY NUMBER,
178 x_revenue_class_name VARCHAR2,
179 x_object_version_number IN OUT NOCOPY NUMBER
180 )
181 IS
182 l_name cn_quotas_all.NAME%TYPE;
183 BEGIN
184 -- Change the comp Status
185 cn_comp_plans_pkg.set_status (x_comp_plan_id => NULL,
186 x_quota_id => x_quota_id,
187 x_rate_schedule_id => NULL,
188 x_status_code => 'INCOMPLETE',
189 x_event => 'CHANGE_RULE'
190 );
191
192 x_object_version_number := 1 ;
193 IF x_quota_rule_id IS NULL THEN
194 select cn_quota_rules_s.nextval into x_quota_rule_id from dual;
195 END IF;
196 -- Insert Quota Rules
197 -- object version number insert added, clku
198 INSERT INTO cn_quota_rules
199 (quota_rule_id,
200 quota_id,
201 revenue_class_id,
202 org_id,
203 target,
204 payment_amount,
205 performance_goal,
206 NAME, -- unmaintained should drop it
207 last_update_date,
208 last_updated_by,
209 creation_date,
210 created_by,
211 last_update_login,
212 object_version_number
213 )
214 VALUES (x_quota_rule_id,
215 x_quota_id,
216 x_revenue_class_id,
217 x_org_id,
218 x_target,
219 x_payment_amount,
220 x_performance_goal,
221 'QUOTA RULE NAME',
222 x_last_update_date,
223 x_last_updated_by,
224 x_creation_date,
225 x_created_by,
226 x_last_update_login,
227 x_object_version_number
228 );
229
230 ----------------------------------------------------------------------------------
231 -- Code from the trigger CN_QUOTA_RULE_TL
232 ----------------------------------------------------------------------------------
233 SELECT NAME
234 INTO l_name
235 FROM cn_quotas_all
236 WHERE quota_id = x_quota_id;
237
238 cn_mark_events_pkg.mark_event_quota (p_event_name => 'CHANGE_QUOTA_ROLL',
239 p_object_name => l_name,
240 p_object_id => x_quota_id,
241 p_start_date => NULL,
242 p_start_date_old => NULL,
243 p_end_date => NULL,
244 p_end_date_old => NULL,
245 p_org_id => x_org_id
246 );
247 ----------------------------------------------------------------------------------
248 -- End of trigger code
249 ----------------------------------------------------------------------------------
250
251 -- update the target , payment, performance goal to quota.
252 update_quota (x_quota_id);
253 -- Create trx factors
254 cn_trx_factors_pkg.INSERT_RECORD (x_quota_id, x_quota_rule_id, x_revenue_class_id);
255 -- Create Srp quota Rules
256 cn_srp_quota_rules_pkg.INSERT_RECORD (x_srp_plan_assign_id => NULL,
257 x_quota_id => x_quota_id,
258 x_quota_rule_id => x_quota_rule_id,
259 x_revenue_class_id => x_revenue_class_id
260 );
261 END INSERT_RECORD;
262
263 -- Name
264
265 -- Purpose
266
267 -- Notes
268
269 ---------------------------------------------------------------------------+
270 -- Update_record
271 ---------------------------------------------------------------------------+
272 PROCEDURE UPDATE_RECORD (
273 x_quota_rule_id NUMBER,
274 x_revenue_class_id NUMBER,
275 x_quota_id NUMBER,
276 x_object_version_number OUT NOCOPY NUMBER,
277 x_last_update_date DATE,
278 x_last_updated_by NUMBER,
279 x_last_update_login NUMBER,
280 x_target NUMBER,
281 x_target_old NUMBER,
282 x_payment_amount NUMBER,
283 x_payment_amount_old NUMBER,
284 x_performance_goal NUMBER,
285 x_performance_goal_old NUMBER,
286 x_revenue_class_name VARCHAR2,
287 x_status_code VARCHAR2,
288 x_revenue_class_id_old NUMBER
289 )
290 IS
291 l_name cn_quotas.NAME%TYPE;
292 l_org_id cn_quotas.org_id%TYPE;
293 BEGIN
294 IF g_program_type = 'FORM'
295 THEN
296 IF (x_revenue_class_id_old <> x_revenue_class_id)
297 THEN
298 -- If the key values have changed update the status
299 cn_comp_plans_pkg.set_status (x_comp_plan_id => NULL,
300 x_quota_id => x_quota_id,
301 x_rate_schedule_id => NULL,
302 x_status_code => 'INCOMPLETE',
303 x_event => 'CHANGE_RULE'
304 );
305 ELSIF ( NVL (x_target_old, 0) <> x_target
306 OR NVL (x_payment_amount_old, 0) <> x_payment_amount
307 OR NVL (x_performance_goal_old, 0) <> x_performance_goal
308 )
309 THEN
310 -- if the key values have changed update the status
311 cn_comp_plans_pkg.set_status (x_comp_plan_id => NULL,
312 x_quota_id => x_quota_id,
313 x_rate_schedule_id => NULL,
314 x_status_code => 'INCOMPLETE',
315 x_event => 'CHANGE_FACTORS'
316 );
317 END IF;
318 ELSIF g_program_type = 'BATCH'
319 THEN
320 NULL; -- need procedure to check db against new values
321 END IF;
322
323 SELECT (NVL (object_version_number, 1) + 1)
324 INTO x_object_version_number
325 FROM cn_quota_rules
326 WHERE quota_rule_id = x_quota_rule_id;
327
328 UPDATE cn_quota_rules
329 SET revenue_class_id = x_revenue_class_id,
330 quota_id = x_quota_id,
331 target = x_target,
332 payment_amount = x_payment_amount,
333 performance_goal = x_performance_goal,
334 quota_rule_id = x_quota_rule_id,
335 last_update_date = x_last_update_date,
336 last_updated_by = x_last_updated_by,
337 last_update_login = x_last_update_login,
338 object_version_number = x_object_version_number,
339 NAME = 'QUOTA RULE NAME'
340 WHERE quota_rule_id = x_quota_rule_id;
341
342 IF (SQL%NOTFOUND)
343 THEN
344 RAISE NO_DATA_FOUND;
345 END IF;
346
347 IF NVL (x_revenue_class_id_old, x_revenue_class_id) <> x_revenue_class_id
348 THEN
349 cn_srp_quota_rules_pkg.DELETE_RECORD (x_srp_plan_assign_id => NULL,
350 x_srp_quota_assign_id => NULL,
351 x_quota_id => x_quota_id,
352 x_quota_rule_id => x_quota_rule_id,
353 x_revenue_class_id => x_revenue_class_id_old
354 );
355 cn_srp_quota_rules_pkg.INSERT_RECORD (x_srp_plan_assign_id => NULL,
356 x_quota_id => x_quota_id,
357 x_quota_rule_id => x_quota_rule_id,
358 x_revenue_class_id => x_revenue_class_id
359 );
360
361 -- Added recently
362 UPDATE cn_trx_factors
363 SET revenue_class_id = x_revenue_class_id
364 WHERE quota_rule_id = x_quota_rule_id;
365 ELSIF NVL (x_target, 0) <> NVL (x_target_old, 0)
366 OR NVL (x_payment_amount, 0) <> NVL (x_payment_amount_old, 0)
367 OR NVL (x_performance_goal, 0) <> NVL (x_performance_goal_old, 0)
368 THEN
369 cn_srp_quota_rules_pkg.UPDATE_RECORD (x_quota_rule_id => x_quota_rule_id,
370 x_target => x_target,
371 x_payment_amount => x_payment_amount,
372 x_performance_goal => x_performance_goal
373 );
374 END IF;
375
376 update_quota (x_quota_id);
377
378 ----------------------------------------------------------------------------------
379 -- Code from the trigger CN_QUOTA_RULE_TL
380 ----------------------------------------------------------------------------------
381 SELECT NAME,
382 org_id
383 INTO l_name,
384 l_org_id
385 FROM cn_quotas_all
386 WHERE quota_id = x_quota_id;
387
388 cn_mark_events_pkg.mark_event_quota (p_event_name => 'CHANGE_QUOTA_ROLL',
389 p_object_name => l_name,
390 p_object_id => x_quota_id,
391 p_start_date => NULL,
392 p_start_date_old => NULL,
393 p_end_date => NULL,
394 p_end_date_old => NULL,
395 p_org_id => l_org_id
396 );
397 ----------------------------------------------------------------------------------
398 -- End of code from CN_QUOTA_RULE_TL
399 ----------------------------------------------------------------------------------
400 END UPDATE_RECORD;
401
402 -- Name
403
404 -- Purpose
405
406 -- Notes
407
408 ---------------------------------------------------------------------------+
409 -- Lock_record
410 ---------------------------------------------------------------------------+
411 PROCEDURE LOCK_RECORD (
412 x_org_id NUMBER,
413 x_revenue_class_id NUMBER,
414 x_quota_id NUMBER,
415 x_target NUMBER,
416 x_payment_amount NUMBER,
417 x_performance_goal NUMBER,
418 x_quota_rule_id NUMBER
419 )
420 IS
421 CURSOR c
422 IS
423 SELECT *
424 FROM cn_quota_rules
425 WHERE quota_rule_id = x_quota_rule_id
426 FOR UPDATE OF quota_rule_id NOWAIT;
427
428 recinfo c%ROWTYPE;
429 BEGIN
430 OPEN c;
431
432 FETCH c
433 INTO recinfo;
434
435 IF c%NOTFOUND
436 THEN
437 CLOSE c;
438
439 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
440 app_exception.raise_exception;
441 END IF;
442
443 CLOSE c;
444
445 IF ( (recinfo.revenue_class_id = x_revenue_class_id)
446 AND (recinfo.quota_id = x_quota_id)
447 AND (recinfo.quota_rule_id = x_quota_rule_id)
448 AND ((recinfo.target = x_target) OR (recinfo.target IS NULL AND x_target IS NULL))
449 AND ((recinfo.payment_amount = x_payment_amount) OR (recinfo.payment_amount IS NULL AND x_payment_amount IS NULL))
450 AND ((recinfo.performance_goal = x_performance_goal) OR (recinfo.performance_goal IS NULL AND x_performance_goal IS NULL))
451 )
452 THEN
453 RETURN;
454 ELSE
455 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
456 app_exception.raise_exception;
457 END IF;
458 END LOCK_RECORD;
459
460 -- Name
461
462 -- Purpose
463
464 -- Notes
465 ---------------------------------------------------------------------------+
466 -- Delete_record
467 ---------------------------------------------------------------------------+
468 PROCEDURE DELETE_RECORD (
469 x_quota_id NUMBER,
470 x_quota_rule_id NUMBER,
471 x_revenue_class_id NUMBER
472 )
473 IS
474 l_name cn_quotas.NAME%TYPE;
475 l_org_id cn_quotas.org_id%TYPE;
476 BEGIN
477 IF x_quota_id IS NOT NULL
478 THEN
479 IF x_quota_rule_id IS NOT NULL
480 THEN
481 -- We are deleting an individual quota rule
482 cn_comp_plans_pkg.set_status (x_comp_plan_id => NULL,
483 x_quota_id => x_quota_id,
484 x_rate_schedule_id => NULL,
485 x_status_code => 'INCOMPLETE',
486 x_event => 'CHANGE_RULE'
487 );
488 --delete the quota rule uplifts if there is delete in the
489 -- quota rules, and we should take care all the quota rule uplifts
490 -- must be deleted if there is a full delete in quota rules using
491 --quota id
492 cn_quota_rule_uplifts_pkg.DELETE_RECORD (x_quota_rule_uplift_id => NULL, x_quota_rule_id => x_quota_rule_id, x_quota_id => x_quota_id);
493 cn_srp_quota_rules_pkg.DELETE_RECORD (x_srp_plan_assign_id => NULL,
494 x_srp_quota_assign_id => NULL,
495 x_quota_id => x_quota_id,
496 x_quota_rule_id => x_quota_rule_id,
497 x_revenue_class_id => x_revenue_class_id
498 );
499
500 DELETE FROM cn_quota_rules
501 WHERE quota_rule_id = x_quota_rule_id;
502
503 IF (SQL%NOTFOUND)
504 THEN
505 RAISE NO_DATA_FOUND;
506 END IF;
507 ELSE
508 --delete the quota rule uplifts if there is delete in the
509 -- quota rules, and we should take care all the quota rule uplifts
510 -- must be deleted if there is a full delete in quota rules using
511 --quota id
512 cn_quota_rule_uplifts_pkg.DELETE_RECORD (x_quota_rule_uplift_id => NULL, x_quota_rule_id => x_quota_rule_id, x_quota_id => x_quota_id);
513 -- Deleting an entire quota OR changing its type to one that does not
514 -- support revenue classes and therefore trx factors
515 -- If we are deleting a quota there is no need to maintain the srp
516 -- tables because you can't delete a quota that is assigned to a rep.
517 -- However we don't discriminate between deletinga quota and changing
518 -- its type so we must maintain the srp tables in this statement.
519 cn_srp_quota_rules_pkg.DELETE_RECORD (x_srp_plan_assign_id => NULL,
520 x_srp_quota_assign_id => NULL,
521 x_quota_id => x_quota_id,
522 x_quota_rule_id => x_quota_rule_id,
523 x_revenue_class_id => x_revenue_class_id
524 );
525
526 DELETE FROM cn_quota_rules
527 WHERE quota_id = x_quota_id;
528 END IF;
529
530 update_quota (x_quota_id);
531 -- quota rule id and revenue class id will be null if deleting an
532 -- entire quota or changing the type
533 cn_trx_factors_pkg.DELETE_RECORD (x_trx_factor_id => NULL, x_quota_rule_id => x_quota_rule_id, x_quota_id => x_quota_id);
534 END IF;
535
536 ----------------------------------------------------------------------------------
537 -- Code from the trigger CN_QUOTA_RULE_TL
538 ----------------------------------------------------------------------------------
539 SELECT NAME,
540 org_id
541 INTO l_name,
542 l_org_id
543 FROM cn_quotas_all
544 WHERE quota_id = x_quota_id;
545
546 cn_mark_events_pkg.mark_event_quota (p_event_name => 'CHANGE_QUOTA_ROLL',
547 p_object_name => l_name,
548 p_object_id => x_quota_id,
549 p_start_date => NULL,
550 p_start_date_old => NULL,
551 p_end_date => NULL,
552 p_end_date_old => NULL,
553 p_org_id => l_org_id
554 );
555 -------------------------------------
556 -- End of code from CN_QUOTA_RULE_TL
557 -------------------------------------
558 END DELETE_RECORD;
559
560 ---------------------------------------------------------------------------+
561 -- Public Routine Bodies
562 ---------------------------------------------------------------------------+
563
564 -- Purpose
565 ---------------------------------------------------------------------------+
566 -- get_rev_class_name
567 ---------------------------------------------------------------------------+
568 PROCEDURE get_rev_class_name (
569 x_revenue_class_id NUMBER,
570 x_revenue_class_name IN OUT NOCOPY VARCHAR2
571 )
572 IS
573 BEGIN
574 IF x_revenue_class_id IS NOT NULL
575 THEN
576 SELECT NAME
577 INTO x_revenue_class_name
578 FROM cn_revenue_classes
579 WHERE revenue_class_id = x_revenue_class_id;
580 END IF;
581 EXCEPTION
582 WHEN NO_DATA_FOUND
583 THEN
584 RAISE NO_DATA_FOUND;
585 END get_rev_class_name;
586
587 -- Name
588
589 -- Purpose
590
591 -- Notes
592
593 ---------------------------------------------------------------------------+
594 -- Begin_Record
595 ---------------------------------------------------------------------------+
596 PROCEDURE begin_record (
597 x_quota_rule_id IN OUT NOCOPY NUMBER,
598 x_object_version_number IN OUT NOCOPY NUMBER,
599 x_org_id NUMBER,
600 x_operation VARCHAR2,
601 x_revenue_class_id NUMBER,
602 x_quota_id NUMBER,
603 x_last_update_date DATE,
604 x_last_updated_by NUMBER,
605 x_creation_date DATE,
606 x_created_by NUMBER,
607 x_last_update_login NUMBER,
608 x_target NUMBER,
609 x_target_old NUMBER,
610 x_payment_amount NUMBER,
611 x_payment_amount_old NUMBER,
612 x_performance_goal NUMBER,
613 x_performance_goal_old NUMBER,
614 x_revenue_class_name VARCHAR2,
615 x_program_type VARCHAR2,
616 x_status_code VARCHAR2,
617 x_revenue_class_id_old NUMBER
618 )
619 IS
620 BEGIN
621 g_program_type := x_program_type;
622
623 IF x_operation = 'INSERT'
624 THEN
625 INSERT_RECORD (x_org_id,
626 x_revenue_class_id,
627 x_quota_id,
628 x_last_update_date,
629 x_last_updated_by,
630 x_creation_date,
631 x_created_by,
632 x_last_update_login,
633 x_target,
634 x_payment_amount,
635 x_performance_goal,
636 x_quota_rule_id,
637 x_revenue_class_name,
638 x_object_version_number
639 );
640 ELSIF x_operation = 'UPDATE'
641 THEN
642 UPDATE_RECORD (x_quota_rule_id,
643 x_revenue_class_id,
644 x_quota_id,
645 x_object_version_number,
646 x_last_update_date,
647 x_last_updated_by,
648 x_last_update_login,
649 x_target,
650 x_target_old,
651 x_payment_amount,
652 x_payment_amount_old,
653 x_performance_goal,
654 x_performance_goal_old,
655 x_revenue_class_name,
656 x_status_code,
657 x_revenue_class_id_old
658 );
659 ELSIF x_operation = 'LOCK'
660 THEN
661 LOCK_RECORD (x_org_id, x_revenue_class_id, x_quota_id, x_target, x_payment_amount, x_performance_goal, x_quota_rule_id);
662 ELSIF x_operation = 'DELETE'
663 THEN
664 DELETE_RECORD (x_quota_id, x_quota_rule_id, x_revenue_class_id);
665 END IF;
666 END begin_record;
667
668 -- Name
669
670 -- Purpose
671
672 -- Notes
673 ---------------------------------------------------------------------------+
674 -- End Record
675 ---------------------------------------------------------------------------+
676 PROCEDURE end_record (
677 x_rowid VARCHAR2,
678 x_revenue_class_id NUMBER,
679 x_quota_id NUMBER,
680 x_target NUMBER,
681 x_payment_amount NUMBER,
682 x_performance_goal NUMBER,
683 x_quota_rule_id NUMBER,
684 x_program_type VARCHAR2
685 )
686 IS
687 BEGIN
688 -- Saves passing it around
689 g_program_type := x_program_type;
690 -- no validation perfromed here. All validation aimed at changing the
691 -- status of the quota is performed in the quota package.
692 NULL;
693 END end_record;
694
695 -- Purpose :
696 -- Checks if X_revenue_class_id is a parent in a hierarchy
697 -- for any other revenue_class_id already saved in the database
698 -- for the X_quota_id
699
700 -- Most of the Period check is commented and the logic is yet
701 -- to be derived.
702
703 ---------------------------------------------------------------------------+
704 -- Check_rev_class_hier
705 ---------------------------------------------------------------------------+
706 FUNCTION check_rev_class_hier (
707 x_revenue_class_id NUMBER,
708 x_revenue_class_id_old NUMBER,
709 x_quota_id NUMBER,
710 x_start_period_id NUMBER,
711 x_end_period_id NUMBER
712 )
713 RETURN BOOLEAN
714 IS
715 CURSOR c1_cur
716 IS
717 SELECT a.dim_hierarchy_id
718 FROM cn_dim_hierarchies a,
719 cn_head_hierarchies b,
720 cn_repositories c
721 WHERE b.dimension_id = -1001 /* Revenue Classes */
722 AND a.header_dim_hierarchy_id = b.head_hierarchy_id
723 AND b.head_hierarchy_id = c.rev_class_hierarchy_id; /* Active hierar
724 chy */
725
726 -- and ((X_start_period_id between a.start_period_id and a.end_perio
727 --d_id)
728 -- OR(X_end_period_id between a.start_period_id and a.end_period_
729 --id)
730 -- OR(a.start_period_id between X_start_period_id and X_end_perio
731 -- d_id));
732 CURSOR c2_csr (
733 l_dim_hierarchy_id NUMBER
734 )
735 IS
736 SELECT rv.NAME
737 FROM cn_dim_explosion de1,
738 cn_dim_explosion de2,
739 cn_quota_rules qr,
740 cn_revenue_classes rv
741 WHERE de1.dim_hierarchy_id = l_dim_hierarchy_id
742 AND de2.dim_hierarchy_id = l_dim_hierarchy_id
743 AND de1.value_external_id = de2.value_external_id
744 AND de1.ancestor_external_id = x_revenue_class_id
745 AND qr.quota_id = x_quota_id
746 AND de2.ancestor_external_id = qr.revenue_class_id
747 AND rv.revenue_class_id = qr.revenue_class_id
748 AND qr.revenue_class_id <> NVL (x_revenue_class_id_old, -999);
749
750 l_count NUMBER;
751 l_flag BOOLEAN;
752 l_periods VARCHAR2 (30);
753 l_rev_class_name_parent VARCHAR2 (30);
754 l_rev_class_name_child VARCHAR2 (30);
755 BEGIN
756 l_flag := TRUE;
757
758 FOR c1_row IN c1_cur
759 LOOP
760 SELECT COUNT (*)
761 INTO l_count
762 FROM cn_dim_explosion a,
763 cn_quota_rules b
764 WHERE a.dim_hierarchy_id = c1_row.dim_hierarchy_id
765 AND a.hierarchy_level <> 0 /*Do not self reference */
766 AND b.quota_id = x_quota_id
767 AND b.revenue_class_id <> NVL (x_revenue_class_id_old, -999)
768 AND ( (b.revenue_class_id = a.value_external_id AND a.ancestor_external_id = x_revenue_class_id)
769 OR (b.revenue_class_id = a.ancestor_external_id AND a.value_external_id = x_revenue_class_id)
770 );
771
772 IF (l_count > 0)
773 THEN /* error condition */
774 -- select a.period_name||' to '||b.period_name into l_periods
775 -- from cn_periods a, cn_periods b
776 -- where a.period_id = X_start_period_id
777 -- and b.period_id = X_end_period_id;
778 SELECT a.NAME,
779 b.NAME
780 INTO l_rev_class_name_parent,
781 l_rev_class_name_child
782 FROM cn_revenue_classes a,
783 cn_revenue_classes b,
784 cn_quota_rules c,
785 cn_dim_explosion d
786 WHERE d.hierarchy_level <> 0
787 AND d.dim_hierarchy_id = c1_row.dim_hierarchy_id
788 AND c.revenue_class_id <> NVL (x_revenue_class_id_old, -999)
789 AND b.revenue_class_id = c.revenue_class_id
790 AND a.revenue_class_id = x_revenue_class_id
791 AND c.quota_id = x_quota_id
792 AND ( (d.ancestor_external_id = x_revenue_class_id AND d.value_external_id = c.revenue_class_id)
793 OR (c.revenue_class_id = d.ancestor_external_id AND d.value_external_id = x_revenue_class_id)
794 )
795 AND ROWNUM = 1;
796
797 l_flag := FALSE;
798 fnd_message.set_name ('CN', 'CN_REV_CLASS_HIER_CHECK');
799 --fnd_message.set_token ('PERIODS', l_periods);
800 fnd_message.set_token ('REV_CLASS_NAME_PARENT', l_rev_class_name_parent);
801 fnd_message.set_token ('REV_CLASS_NAME_CHILD', l_rev_class_name_child);
802 RETURN (l_flag); /* return - error*/
803 END IF;
804
805 OPEN c2_csr (c1_row.dim_hierarchy_id);
806
807 FETCH c2_csr
808 INTO l_rev_class_name_child;
809
810 IF c2_csr%ROWCOUNT <> 0
811 THEN /* error condition */
812 l_flag := FALSE;
813
814 -- select a.period_name||' to '||b.period_name into l_periods
815 -- from cn_periods a, cn_periods b
816 -- where a.period_id = X_start_period_id
817 -- and b.period_id = X_end_period_id;
818 SELECT a.NAME
819 INTO l_rev_class_name_parent
820 FROM cn_revenue_classes a
821 WHERE a.revenue_class_id = x_revenue_class_id;
822
823 fnd_message.set_name ('CN', 'REV_CLASS_COMMON_CHILD');
824 fnd_message.set_token ('PERIODS', l_periods);
825 fnd_message.set_token ('REV_CLASS_PARENT1', l_rev_class_name_parent);
826 fnd_message.set_token ('REV_CLASS_PARENT2', l_rev_class_name_child);
827
828 CLOSE c2_csr;
829
830 RETURN (l_flag); /* return - error*/
831 END IF;
832
833 CLOSE c2_csr;
834 END LOOP;
835
836 RETURN (l_flag); /* return - success */
837 END check_rev_class_hier;
838 END cn_quota_rules_pkg;