[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTAS_PKG
Source
1 PACKAGE BODY cn_quotas_pkg AS
2 /* $Header: cnpliqob.pls 120.4.12000000.5 2007/10/10 00:48:56 rnagired ship $ */
3
4 /*
5 Date Name Description
6 ---------------------------------------------------------------------------+
7 06-FEB-95 P Cook Allow plan completion of key-factors do not equal 100.
8 15-FEB-95 P Cook Unit tested
9 06-MAR-95 P Cook Test for schedule presence(get_schedule_id) regardless
10 of whether rate_schedule_id is passed in as a
11 parameter (bug 268849).
12 19-JUL-95 P Cook Added no data found to check_tiers
13 26-JUL-95 P Cook Fixed quota change range error message name
14 27-JUL-95 P Cook Added cumulative_flag
15 12-AUG-95 P Cook removed recursive loop in check_quota_rules
16 08-MAR-99 S Kumar Modified and More Parameters in the Begin Insert
17 27-APR-99 S Kumar Modified and More Parameters in the Begin Insert
18 17-MAY-99 S Kumar Calling delete record, insert record for changing the
19 calc formula name
20 26-MAY-99 S Kumar Changes made due to 3i
21
22 25-JUN-99 S Kumar Modified the procedure call to cn_srp_per_rc
23 and cn_srp_per_quota. instead of period id to
24 end date
25
26 03-AUG-99 S Kumar get_schedule_id procedure go changed due to formulas
27 now we do not have a rate schedule directly assiged
28 to the quotas, it has to go through the formula
29 we need to validate against cn_rt_quota_assigns.
30
31 03-AUG-99 S Kumar End_record.rate_schedules_pkg call for was made from
32 quota end record, now we are looping through each
33 rate schedule we have at the rt_quota_asgns.
34 it means that validating all the rate schedule and
35 its tiers.
36
37 25-AUG-99 S Kumar Added more parameter to update the performance goal
38 in the srp_quota_assigns.
39 update_srp_quota.performance_goal.
40
41 25-AUG-99 S Kumar Changed the g_temp_Status with nvl in the update
42 added delete call to the rt_quota_asgns
43
44 20-SEP-99 S Kumar Modified the Package to mark the event.
45
46 Name
47
48 Purpose
49
50 Notes
51
52 */
53
54 -- -------------------------------------------------------------------------+
55 -- | Variables |
56 ----------------------------------------------------------------------------+
57 g_temp_status_code VARCHAR2 (30) := NULL;
58 g_program_type VARCHAR2 (30) := NULL;
59 g_quota_name VARCHAR2 (80) := NULL;
60 g_plan_name VARCHAR2 (30) := NULL;
61 g_schedule_name VARCHAR2 (30) := NULL;
62
63 ----------------------------------------------------------------------------+
64 -- | Private Routines |
65 ----------------------------------------------------------------------------+
66 -- Procedure Name
67
68 -- Purpose
69 -- cover
70 PROCEDURE set_message (
71 message_name VARCHAR2,
72 token_name VARCHAR2,
73 token_value VARCHAR2
74 )
75 IS
76 BEGIN
77 cn_message_pkg.set_message (appl_short_name => 'CN',
78 message_name => message_name,
79 token_name1 => 'QUOTA_NAME',
80 token_value1 => g_quota_name,
81 token_name2 => 'PLAN_NAME',
82 token_value2 => g_plan_name,
83 token_name3 => token_name,
84 token_value3 => token_value,
85 token_name4 => NULL,
86 token_value4 => NULL,
87 TRANSLATE => TRUE
88 );
89 fnd_msg_pub.ADD;
90 END set_message;
91
92 -- Purpose
93
94 -- Notes
95 PROCEDURE get_uid (
96 x_quota_id IN OUT NOCOPY NUMBER
97 )
98 IS
99 BEGIN
100 SELECT cn_quotas_s.NEXTVAL
101 INTO x_quota_id
102 FROM SYS.DUAL;
103 END get_uid;
104
105 -- Name
106 -- Purpose
107 -- Notes
108 -- You can't delete a quota that is assigned to a rep so there is no need
109 -- to cascade this delete to the srp tables
110 PROCEDURE DELETE_RECORD (
111 x_quota_id NUMBER,
112 x_name VARCHAR2
113 )
114 IS
115 BEGIN
116 -- delete quota rules
117 cn_quota_rules_pkg.DELETE_RECORD (x_quota_id => x_quota_id, x_quota_rule_id => NULL, x_revenue_class_id => NULL);
118 -- delete period qutoas
119 cn_period_quotas_pkg.DELETE_RECORD (x_quota_id => x_quota_id);
120 -- delete rt quota asgns id
121 cn_rt_quota_asgns_pkg.DELETE_RECORD (x_quota_id => x_quota_id, x_calc_formula_id => NULL, x_rt_quota_asgn_id => NULL);
122
123 UPDATE cn_quotas_all
124 SET delete_flag = 'Y'
125 WHERE quota_id = x_quota_id;
126 END DELETE_RECORD;
127
128 -- Name
129 -- Purpose
130 -- Notes
131 PROCEDURE INSERT_RECORD (
132 x_rowid IN OUT NOCOPY VARCHAR2,
133 x_quota_id IN OUT NOCOPY NUMBER,
134 x_object_version_number OUT NOCOPY NUMBER,
135 x_name VARCHAR2,
136 x_target NUMBER,
137 x_quota_type_code VARCHAR2,
138 x_period_type_code VARCHAR2,
139 x_usage_code VARCHAR2,
140 x_payment_amount NUMBER,
141 x_description VARCHAR2,
142 x_start_date DATE,
143 x_end_date DATE,
144 x_quota_status VARCHAR2,
145 x_start_num NUMBER,
146 x_end_num NUMBER,
147 x_last_update_date DATE,
148 x_last_updated_by NUMBER,
149 x_creation_date DATE,
150 x_created_by NUMBER,
151 x_last_update_login NUMBER,
152 x_incentive_type_code VARCHAR2,
153 x_credit_type_id NUMBER,
154 x_calc_formula_id NUMBER,
155 x_rt_sched_custom_flag VARCHAR2,
156 x_package_name VARCHAR2,
157 x_performance_goal NUMBER,
158 x_interval_type_id NUMBER,
159 x_payee_assign_flag VARCHAR2,
160 x_vesting_flag VARCHAR2,
161 x_quota_unspecified NUMBER,
162 x_addup_from_rev_class_flag VARCHAR2,
163 x_expense_account_id NUMBER,
164 x_liability_account_id NUMBER,
165 x_quota_group_code VARCHAR2,
166 --clku, PAYMENT ENHANCEMENT
167 x_payment_group_code VARCHAR2 := 'STANDARD',
168 --clku, 2854576
169 x_attribute_category VARCHAR := NULL,
170 x_attribute1 VARCHAR2,
171 x_attribute2 VARCHAR2,
172 x_attribute3 VARCHAR2,
173 x_attribute4 VARCHAR2,
174 x_attribute5 VARCHAR2,
175 x_attribute6 VARCHAR2,
176 x_attribute7 VARCHAR2,
177 x_attribute8 VARCHAR2,
178 x_attribute9 VARCHAR2,
179 x_attribute10 VARCHAR2,
180 x_attribute11 VARCHAR2,
181 x_attribute12 VARCHAR2,
182 x_attribute13 VARCHAR2,
183 x_attribute14 VARCHAR2,
184 x_attribute15 VARCHAR2,
185 -- fmburu r12
186 x_indirect_credit VARCHAR2,
187 x_org_id NUMBER,
188 x_salesrep_end_flag VARCHAR2
189 )
190 IS
191 x_incremental_type VARCHAR2 (1) := 'N';
192 BEGIN
193 --- pass null so that the primary key is populated by the PKG
194 IF x_quota_id IS NULL
195 THEN
196 get_uid (x_quota_id);
197 END IF;
198
199 INSERT INTO cn_quotas
200 (quota_id,
201 NAME,
202 target,
203 quota_type_code,
204 payment_amount,
205 description,
206 start_date,
207 end_date,
208 quota_status,
209 last_update_date,
210 last_updated_by,
211 creation_date,
212 created_by,
213 last_update_login,
214 incremental_type,
215 calc_formula_id,
216 incentive_type_code,
217 credit_type_id,
218 rt_sched_custom_flag,
219 package_name,
220 performance_goal,
221 interval_type_id,
222 payee_assign_flag,
223 vesting_flag,
224 addup_from_rev_class_flag,
225 expense_account_id,
226 liability_account_id,
227 quota_group_code,
228 --clku, PAYMENT ENHANCEMENT
229 payment_group_code,
230 --clku
231 attribute_category,
232 attribute1,
233 attribute2,
234 attribute3,
235 attribute4,
236 attribute5,
237 attribute6,
238 attribute7,
239 attribute8,
240 attribute9,
241 attribute10,
242 attribute11,
243 attribute12,
244 attribute13,
245 attribute14,
246 attribute15,
247 object_version_number,
248 -- fmburu r12
249 indirect_credit,
250 org_id,
251 salesreps_enddated_flag
252 )
253 VALUES (x_quota_id,
254 x_name,
255 x_target,
256 x_quota_type_code,
257 x_payment_amount,
258 x_description,
259 x_start_date,
260 x_end_date,
261 x_quota_status,
262 x_last_update_date,
263 x_last_updated_by,
264 x_creation_date,
265 x_created_by,
266 x_last_update_login,
267 x_incremental_type,
268 x_calc_formula_id,
269 x_incentive_type_code,
270 x_credit_type_id,
271 x_rt_sched_custom_flag,
272 x_package_name,
273 x_performance_goal,
274 x_interval_type_id,
275 x_payee_assign_flag,
276 x_vesting_flag,
277 x_addup_from_rev_class_flag,
278 DECODE (x_expense_account_id, cn_api.g_miss_id, NULL, x_expense_account_id),
279 DECODE (x_liability_account_id, cn_api.g_miss_id, NULL, x_liability_account_id),
280 x_quota_group_code,
281 --clku, PAYMENT ENHANCEMENT
282 x_payment_group_code,
283 --clku, 2854578
284 x_attribute_category,
285 x_attribute1,
286 x_attribute2,
287 x_attribute3,
288 x_attribute4,
289 x_attribute5,
290 x_attribute6,
291 x_attribute7,
292 x_attribute8,
293 x_attribute9,
294 x_attribute10,
295 x_attribute11,
296 x_attribute12,
297 x_attribute13,
298 x_attribute14,
299 x_attribute15,
300 1,
301 -- fmburu r12
302 x_indirect_credit,
303 x_org_id,
304 nvl(x_salesrep_end_flag,'N')
305 );
306
307 x_object_version_number := 1;
308 END INSERT_RECORD;
309
310 -- temp use
311 PROCEDURE UPDATE_RECORD (
312 x_quota_id NUMBER,
313 x_start_date DATE,
314 x_end_date DATE
315 )
316 IS
317 BEGIN
318 UPDATE cn_quotas
319 SET start_date = x_start_date,
320 end_date = x_end_date
321 WHERE quota_id = x_quota_id;
322 END;
323
324 -- Name
325 -- Purpose
326 -- Notes
327 PROCEDURE UPDATE_RECORD (
328 x_quota_id NUMBER,
329 x_object_version_number OUT NOCOPY NUMBER,
330 x_name VARCHAR2,
331 x_target NUMBER,
332 x_quota_type_code VARCHAR2,
333 x_period_type_code VARCHAR2,
334 x_usage_code VARCHAR2,
335 x_payment_amount NUMBER,
336 x_description VARCHAR2,
337 x_start_date DATE,
338 x_end_date DATE,
339 x_quota_status VARCHAR2,
340 x_start_num NUMBER,
341 x_end_num NUMBER,
342 x_last_update_date DATE,
343 x_last_updated_by NUMBER,
344 x_last_update_login NUMBER,
345 x_incentive_type_code VARCHAR2,
346 x_credit_type_id NUMBER,
347 x_calc_formula_id NUMBER,
348 x_rt_sched_custom_flag VARCHAR2,
349 x_package_name VARCHAR2,
350 x_performance_goal NUMBER,
351 x_interval_type_id NUMBER,
352 x_payee_assign_flag VARCHAR2,
353 x_vesting_flag VARCHAR2,
354 x_quota_unspecified NUMBER,
355 x_addup_from_rev_class_flag VARCHAR2,
356 x_expense_account_id NUMBER,
357 x_liability_account_id NUMBER,
358 x_quota_group_code VARCHAR2,
359 x_payment_group_code VARCHAR2 := 'STANDARD',
360 x_attribute_category VARCHAR := NULL,
361 x_attribute1 VARCHAR2,
362 x_attribute2 VARCHAR2,
363 x_attribute3 VARCHAR2,
364 x_attribute4 VARCHAR2,
365 x_attribute5 VARCHAR2,
366 x_attribute6 VARCHAR2,
367 x_attribute7 VARCHAR2,
368 x_attribute8 VARCHAR2,
369 x_attribute9 VARCHAR2,
370 x_attribute10 VARCHAR2,
371 x_attribute11 VARCHAR2,
372 x_attribute12 VARCHAR2,
373 x_attribute13 VARCHAR2,
374 x_attribute14 VARCHAR2,
375 x_attribute15 VARCHAR2,
376 x_indirect_credit VARCHAR2,
377 x_salesrep_end_flag VARCHAR2
378 )
379 IS
380 l_modified BOOLEAN := FALSE;
381 x_incremental_type VARCHAR2 (1) := 'N';
382 x_rate_schedule_id NUMBER;
383 x_discount_option_code VARCHAR2 (100);
384 x_disc_rate_schedule_id NUMBER;
385 x_trx_group_code VARCHAR2 (100);
386 x_cumulative_flag VARCHAR2 (100);
387 x_split_flag VARCHAR2 (100);
388 x_itd_flag VARCHAR2 (100);
389 x_payment_type_code VARCHAR2 (100);
390 l_return_status VARCHAR2 (100);
391 l_msg_count NUMBER;
392 l_msg_data VARCHAR2 (100);
393 l_role_id NUMBER;
394 l_comp_plan_id NUMBER;
395 l_salesrep_id NUMBER;
396 l_start_date DATE;
397 l_end_date DATE;
398 l_loading_status VARCHAR2 (100);
399 l_next_status NUMBER;
400 l_ovn NUMBER;
401
402 CURSOR c
403 IS
404 SELECT *
405 FROM cn_quotas
406 WHERE quota_id = x_quota_id;
407
408 -- clku
409 CURSOR srp_quota_assigns_curs
410 IS
411 SELECT *
412 FROM cn_srp_quota_assigns
413 WHERE quota_id = x_quota_id;
414
415 recinfo c%ROWTYPE;
416 srp_quota_assigns_info srp_quota_assigns_curs%ROWTYPE;
417 l_old_ovn NUMBER;
418 BEGIN
419 IF g_temp_status_code <> 'FAILED'
420 THEN
421 OPEN c;
422
423 FETCH c
424 INTO recinfo;
425
426 IF c%NOTFOUND
427 THEN
428 CLOSE c;
429 END IF;
430
431 CLOSE c;
432
433 SELECT object_version_number
434 INTO l_old_ovn
435 FROM cn_quotas
436 WHERE quota_id = x_quota_id;
437
438 l_ovn := (NVL (l_old_ovn, 1) + 1);
439
440 UPDATE cn_quotas
441 SET quota_id = x_quota_id,
442 NAME = x_name,
443 target = x_target,
444 quota_type_code = x_quota_type_code,
445 payment_amount = x_payment_amount,
446 description = x_description,
447 start_date = x_start_date,
448 end_date = x_end_date,
449 quota_status = x_quota_status,
450 last_update_date = x_last_update_date,
451 last_updated_by = x_last_updated_by,
452 last_update_login = x_last_update_login,
453 incremental_type = x_incremental_type,
454 calc_formula_id = x_calc_formula_id,
455 incentive_type_code = x_incentive_type_code,
456 credit_type_id = x_credit_type_id,
457 performance_goal = x_performance_goal,
458 rt_sched_custom_flag = x_rt_sched_custom_flag,
459 package_name = x_package_name,
460 interval_type_id = x_interval_type_id,
461 payee_assign_flag = x_payee_assign_flag,
462 vesting_flag = x_vesting_flag,
463 addup_from_rev_class_flag = x_addup_from_rev_class_flag,
464 expense_account_id = DECODE (x_expense_account_id, cn_api.g_miss_id, recinfo.expense_account_id, x_expense_account_id),
465 liability_account_id = DECODE (x_liability_account_id, cn_api.g_miss_id, recinfo.liability_account_id, x_liability_account_id),
466 quota_group_code = x_quota_group_code,
467 --clku, PAYMENT ENHANCEMENT
468 payment_group_code = x_payment_group_code,
469 -- clku, 2854576
470 attribute_category = x_attribute_category,
471 attribute1 = x_attribute1,
472 attribute2 = x_attribute2,
473 attribute3 = x_attribute3,
474 attribute4 = x_attribute4,
475 attribute5 = x_attribute5,
476 attribute6 = x_attribute6,
477 attribute7 = x_attribute7,
478 attribute8 = x_attribute8,
479 attribute9 = x_attribute9,
480 attribute10 = x_attribute10,
481 attribute11 = x_attribute11,
482 attribute12 = x_attribute12,
483 attribute13 = x_attribute13,
484 attribute14 = x_attribute14,
485 attribute15 = x_attribute15,
486 object_version_number = l_ovn,
487 -- release 12
488 indirect_credit = x_indirect_credit,
489 salesreps_enddated_flag=x_salesrep_end_flag
490 WHERE quota_id = x_quota_id;
491
492 x_object_version_number := l_ovn;
493
494 IF (SQL%NOTFOUND)
495 THEN
496 RAISE NO_DATA_FOUND;
497 ELSE
498 l_modified := TRUE;
499 cn_comp_plans_pkg.set_status (x_comp_plan_id => NULL,
500 x_quota_id => x_quota_id,
501 x_rate_schedule_id => NULL,
502 x_status_code => 'INCOMPLETE',
503 x_event => 'CHANGE_TIERS'
504 );
505 END IF;
506
507 -- To simplify things we no longer discriminate between changes
508 -- to the rate schedule over other attributes.
509 IF l_modified
510 THEN
511 IF (x_indirect_credit <> recinfo.indirect_credit) THEN
512 cn_mark_events_pkg.mark_event_quota
513 (p_event_name => 'CHANGE_PE_DIRECT_INDIRECT',
514 p_object_name => x_name,
515 p_object_id => x_quota_id,
516 p_start_date => x_start_date,
517 p_end_date => x_end_date,
518 p_start_date_old => recinfo.start_date,
519 p_end_date_old => recinfo.end_date,
520 p_org_id => recinfo.org_id
521 );
522
523 END IF;
524
525 -- Delete the Quota rules if there is a change in the Quota Type
526 -- Quota Type none cannot have a revenue class
527 IF (x_quota_type_code IN ('NONE') AND recinfo.quota_type_code IN ('FORMULA', 'EXTERNAL'))
528 THEN
529 cn_quota_rules_pkg.DELETE_RECORD (x_quota_id => x_quota_id, x_quota_rule_id => NULL, x_revenue_class_id => NULL);
530 END IF;
531
532 -- update the srp quota assigns
533 -- delete the srp_rt_quota_assigns if the formula changes
534 cn_srp_quota_assigns_pkg.update_srp_quota (x_quota_id => x_quota_id,
535 x_target => x_target,
536 x_payment_amount => x_payment_amount,
537 x_performance_goal => x_performance_goal,
538 x_rate_schedule_id => NULL
539 -- obsolete
540 ,
541 x_rate_schedule_id_old => NULL
542 -- obsolete
543 ,
544 x_disc_rate_schedule_id => NULL
545 -- obsolete
546 ,
547 x_disc_rate_schedule_id_old => NULL
548 -- obsolete
549 ,
550 x_payment_type_code => NULL
551 -- obsolete
552 ,
553 x_payment_type_code_old => NULL
554 -- obsolete
555 ,
556 x_quota_type_code => x_quota_type_code,
557 x_quota_type_code_old => recinfo.quota_type_code,
558 x_period_type_code => x_period_type_code
559 -- pening, how to handle the new
560 ,
561 x_calc_formula_id => x_calc_formula_id
562 -- not used.
563 ,
564 x_calc_formula_id_old => recinfo.calc_formula_id
565 );
566
567 --clku
568 IF (x_credit_type_id <> recinfo.credit_type_id)
569 THEN
570 FOR srp_quota_assigns_info IN srp_quota_assigns_curs
571 LOOP
572 SELECT role_id,
573 comp_plan_id,
574 salesrep_id,
575 start_date,
576 end_date
577 INTO l_role_id,
578 l_comp_plan_id,
579 l_salesrep_id,
580 l_start_date,
581 l_end_date
582 FROM cn_srp_plan_assigns
583 WHERE srp_plan_assign_id = srp_quota_assigns_info.srp_plan_assign_id;
584
585 -- Create entry in cn_srp_periods
586 cn_srp_periods_pvt.create_srp_periods (p_api_version => 1.0,
587 x_return_status => l_return_status,
588 x_msg_count => l_msg_count,
589 x_msg_data => l_msg_data,
590 p_role_id => l_role_id,
591 p_comp_plan_id => l_comp_plan_id,
592 p_salesrep_id => l_salesrep_id,
593 p_start_date => l_start_date,
594 p_end_date => l_end_date,
595 x_loading_status => l_loading_status
596 );
597
598 IF (l_return_status <> fnd_api.g_ret_sts_success)
599 THEN
600 RAISE fnd_api.g_exc_error;
601 END IF;
602 END LOOP;
603 END IF;
604
605 --clku, we need to sync up the ITD values whenever interval type is updated
606 IF (x_interval_type_id <> recinfo.interval_type_id)
607 THEN
608 cn_period_quotas_pkg.sync_itd_values (x_quota_id);
609 cn_srp_period_quotas_pkg.sync_itd_values (x_quota_id);
610 END IF;
611 END IF;
612
613 IF (TRUNC (x_start_date) <> TRUNC (recinfo.start_date))
614 OR (NVL (x_end_date, fnd_api.g_miss_date) <> NVL (recinfo.end_date, fnd_api.g_miss_date))
615 THEN
616 -- start_date remanin unchanged
617 IF TRUNC (x_start_date) = TRUNC (recinfo.start_date)
618 THEN
619 IF x_end_date IS NULL
620 THEN
621 -- recinfo.end_date is not null and greater end_date
622 cn_srp_period_quotas_pkg.insert_record
623 (x_srp_plan_assign_id => NULL,
624 x_quota_id => x_quota_id,
625 x_start_period_id => NULL -- obsolete
626 ,
627 x_end_period_id => NULL -- obsolete
628 ,
629 x_start_date => cn_api.next_period (recinfo.end_date, recinfo.org_id),
630 x_end_date => x_end_date
631 );
632 -- mark the Event only mark the new period records
633 -- bug 3646625, fix the passed in p_start_end
634 cn_mark_events_pkg.mark_event_quota
635 (p_event_name => 'CHANGE_QUOTA_DATE',
636 p_object_name => x_name,
637 p_object_id => x_quota_id,
638 p_start_date => x_start_date,
639 p_end_date => x_end_date,
640 p_start_date_old => recinfo.start_date,
641 p_end_date_old => recinfo.end_date,
642 p_org_id => recinfo.org_id
643 );
644 -- recinfo end date is not null and greater end date
645 -- RC 2
646 cn_srp_per_quota_rc_pkg.INSERT_RECORD
647 (x_srp_plan_assign_id => NULL,
648 x_quota_id => x_quota_id,
649 x_revenue_class_id => NULL,
650 x_start_period_id => NULL,
651 x_end_period_id => NULL,
652 x_start_date => cn_api.next_period (recinfo.end_date, recinfo.org_id),
653 x_end_date => x_end_date
654 );
655 ELSIF recinfo.end_date IS NULL
656 THEN
657 -- mark the Event only mark the delete period records
658 -- bug 3646625, fix the passed in p_start_end
659 cn_mark_events_pkg.mark_event_quota
660 (p_event_name => 'CHANGE_QUOTA_DATE',
661 p_object_name => x_name,
662 p_object_id => x_quota_id,
663 p_start_date => x_start_date,
664 p_end_date => x_end_date,
665 p_start_date_old => recinfo.start_date,
666 p_end_date_old => recinfo.end_date,
667 p_org_id => recinfo.org_id
668 );
669 -- x_end_date is not null and less end_date
670 cn_srp_period_quotas_pkg.DELETE_RECORD
671 (x_srp_plan_assign_id => NULL,
672 x_quota_id => x_quota_id,
673 x_start_period_id => NULL -- obsolete
674 ,
675 x_end_period_id => NULL -- obsolete
676 ,
677 x_start_date => cn_api.next_period (x_end_date, recinfo.org_id),
678 x_end_date => recinfo.end_date
679 );
680 -- x_end_date is not null and less end_date
681 -- RC 1
682 cn_srp_per_quota_rc_pkg.DELETE_RECORD
683 (x_srp_plan_assign_id => NULL,
684 x_quota_id => x_quota_id,
685 x_revenue_class_id => NULL,
686 x_start_period_id => NULL,
687 x_end_period_id => NULL,
688 x_start_date => cn_api.next_period (x_end_date, recinfo.org_id),
689 x_end_date => recinfo.end_date
690 );
691 ELSIF TRUNC (x_end_date) > TRUNC (recinfo.end_date)
692 THEN
693 -- Greater end_date
694 cn_srp_period_quotas_pkg.INSERT_RECORD
695 (x_srp_plan_assign_id => NULL,
696 x_quota_id => x_quota_id,
697 x_start_period_id => NULL -- obsolete
698 ,
699 x_end_period_id => NULL -- obsolete
700 ,
701 x_start_date => cn_api.next_period (recinfo.end_date, recinfo.org_id),
702 x_end_date => x_end_date
703 );
704 -- mark the Event only mark the new period records
705 -- bug 3646625, fix the passed in p_start_end
706 cn_mark_events_pkg.mark_event_quota
707 (p_event_name => 'CHANGE_QUOTA_DATE',
708 p_object_name => x_name,
709 p_object_id => x_quota_id,
710 p_start_date => x_start_date,
711 p_end_date => x_end_date,
712 p_start_date_old => recinfo.start_date,
713 p_end_date_old => recinfo.end_date,
714 p_org_id => recinfo.org_id
715 );
716 -- Greater end_date
717 -- RC 0
718 cn_srp_per_quota_rc_pkg.INSERT_RECORD
719 (x_srp_plan_assign_id => NULL,
720 x_quota_id => x_quota_id,
721 x_revenue_class_id => NULL,
722 x_start_period_id => NULL,
723 x_end_period_id => NULL,
724 x_start_date => cn_api.next_period (recinfo.end_date, recinfo.org_id),
725 x_end_date => x_end_date
726 );
727 ELSE
728 -- shorten end_date
729
730 -- mark the Event only mark the deleted records records
731 -- bug 3646625, fix the passed in p_start_end
732 cn_mark_events_pkg.mark_event_quota
733 (p_event_name => 'CHANGE_QUOTA_DATE',
734 p_object_name => x_name,
735 p_object_id => x_quota_id,
736 p_start_date => x_start_date,
737 p_end_date => x_end_date,
738 p_start_date_old => recinfo.start_date,
739 p_end_date_old => recinfo.end_date,
740 p_org_id => recinfo.org_id
741 );
742 cn_srp_period_quotas_pkg.DELETE_RECORD
743 (x_srp_plan_assign_id => NULL,
744 x_quota_id => x_quota_id,
745 x_start_period_id => NULL -- obsolete
746 ,
747 x_end_period_id => NULL -- obsolete
748 ,
749 x_start_date => cn_api.next_period (x_end_date, recinfo.org_id),
750 x_end_date => cn_api.next_period (recinfo.end_date, recinfo.org_id) -- bugfix 4042235
751 );
752 cn_srp_per_quota_rc_pkg.DELETE_RECORD
753 (x_srp_plan_assign_id => NULL,
754 x_quota_id => x_quota_id,
755 x_revenue_class_id => NULL,
756 x_start_period_id => NULL,
757 x_end_period_id => NULL,
758 x_start_date => cn_api.next_period (x_end_date, recinfo.org_id),
759 x_end_date => cn_api.next_period (recinfo.end_date, recinfo.org_id) -- bugfix 4042235
760 );
761 END IF;
762 ELSE
763 -- mark the Event only mark the delete period records
764 cn_mark_events_pkg.mark_event_quota
765 (p_event_name => 'CHANGE_QUOTA_DATE',
766 p_object_name => x_name,
767 p_object_id => x_quota_id,
768 p_start_date => x_start_date,
769 p_end_date => x_end_date,
770 p_start_date_old => recinfo.start_date,
771 p_end_date_old => recinfo.end_date,
772 p_org_id => recinfo.org_id
773 );
774 -- Remove all assignments for this quota
775 cn_srp_per_quota_rc_pkg.DELETE_RECORD
776 (x_srp_plan_assign_id => NULL,
777 x_quota_id => x_quota_id,
778 x_revenue_class_id => NULL,
779 x_start_period_id => NULL,
780 x_end_period_id => NULL,
781 x_start_date => recinfo.start_date,
782 x_end_date => recinfo.end_date
783 );
784 cn_srp_period_quotas_pkg.DELETE_RECORD
785 (x_srp_plan_assign_id => NULL,
786 x_quota_id => x_quota_id,
787 x_start_period_id => NULL
788 -- obsolete
789 ,
790 x_end_period_id => NULL
791 -- obsolete
792 ,
793 x_start_date => recinfo.start_date,
794 x_end_date => recinfo.end_date
795 );
796 cn_srp_period_quotas_pkg.INSERT_RECORD
797 (x_srp_plan_assign_id => NULL,
798 x_quota_id => x_quota_id,
799 x_start_period_id => NULL
800 -- obsolete
801 ,
802 x_end_period_id => NULL
803 -- obsolete
804 ,
805 x_start_date => x_start_date,
806 x_end_date => x_end_date
807 );
808 cn_mark_events_pkg.mark_event_quota
809 (p_event_name => 'CHANGE_QUOTA_DATE',
810 p_object_name => x_name,
811 p_object_id => x_quota_id,
812 p_start_date => x_start_date,
813 p_end_date => x_end_date,
814 p_start_date_old => recinfo.start_date,
815 p_end_date_old => recinfo.end_date,
816 p_org_id => recinfo.org_id
817 );
818 cn_srp_per_quota_rc_pkg.INSERT_RECORD
819 (x_srp_plan_assign_id => NULL,
820 x_quota_id => x_quota_id,
821 x_revenue_class_id => NULL,
822 x_start_period_id => NULL,
823 x_end_period_id => NULL,
824 x_start_date => x_start_date,
825 x_end_date => x_end_date
826 );
827 -- mark the newly inserted record as calc, because of the
828 -- newly inserted srp_period_quotas
829 -- clku, bug 3646625
830 cn_mark_events_pkg.mark_event_quota
831 (p_event_name => 'CHANGE_QUOTA_CALC',
832 p_object_name => x_name,
833 p_object_id => x_quota_id,
834 p_start_date => x_start_date,
835 p_end_date => x_end_date,
836 p_start_date_old => NULL,
837 p_end_date_old => NULL,
838 p_org_id => recinfo.org_id
839 );
840 END IF;
841 ELSE
842 IF x_performance_goal <> recinfo.performance_goal
843 OR NVL (x_target, 0) <> recinfo.target
844 OR NVL (x_payment_amount, 0) <> recinfo.payment_amount
845 OR x_credit_type_id <> recinfo.credit_type_id
846 OR x_interval_type_id <> recinfo.interval_type_id
847 OR x_vesting_flag <> recinfo.vesting_flag
848 OR NVL (x_calc_formula_id, -99) <> NVL (recinfo.calc_formula_id, -99)
849 THEN
850 cn_mark_events_pkg.mark_event_quota
851 (p_event_name => 'CHANGE_QUOTA_CALC',
852 p_object_name => x_name,
853 p_object_id => x_quota_id,
854 p_start_date => NULL,
855 p_end_date => NULL,
856 p_start_date_old => NULL,
857 p_end_date_old => NULL,
858 p_org_id => recinfo.org_id
859 );
860 END IF;
861 END IF;
862 -- delete the associated rows from cn_period_quots table and
863 -- insert the new one. Can not use only difference since
864 -- have to reselect the whole thing.
865
866 -- clku, 1/9/2002, commented out this part because we want to prevent inserting
867 -- records in cn_period_quotas with checking the formula's ytd flag. The insert of rows
868 -- will be taken care of in CN_PLAN_ELEMENT_PUB.Update_Period_Quota right after
869 -- the call of cn_period_quotas.begin_record
870 /*cn_period_quotas_pkg.delete_record (
871 x_quota_id => x_quota_id);
872 cn_period_quotas_pkg.insert_record (
873 x_quota_id => x_quota_id);*/
874 END IF;
875 -- The period_type_code has changed. Delete rows from
876 -- cn_period_quotas associated with x_quota_id and add the
877 -- new rows according to the new period_type_code.
878
879 /*IF ( recinfo.period_type_code <> x_period_type_code ) THEN
880
881 cn_period_quotas_pkg.delete_record (
882 x_quota_id => x_quota_id);
883
884 cn_period_quotas_pkg.insert_record (
885 x_quota_id => x_quota_id);
886
887 END IF;*/
888 END UPDATE_RECORD;
889
890 -- Name
891
892 -- Purpose
893
894 -- Notes
895 PROCEDURE LOCK_RECORD (
896 x_rowid VARCHAR2,
897 x_quota_id NUMBER,
898 x_object_version_number NUMBER,
899 x_name VARCHAR2,
900 x_target NUMBER,
901 x_description VARCHAR2,
902 x_quota_type_code VARCHAR2,
903 x_period_type_code VARCHAR2,
904 x_usage_code VARCHAR2,
905 x_payment_amount NUMBER,
906 x_start_date DATE,
907 x_end_date DATE,
908 x_quota_status VARCHAR2,
909 x_start_num NUMBER,
910 x_end_num NUMBER,
911 x_incentive_type_code VARCHAR2,
912 x_credit_type_id NUMBER,
913 x_calc_formula_id NUMBER,
914 x_rt_sched_custom_flag VARCHAR2,
915 x_package_name VARCHAR2,
916 x_performance_goal NUMBER,
917 x_interval_type_id NUMBER,
918 x_payee_assign_flag VARCHAR2,
919 x_vesting_flag VARCHAR2,
920 x_quota_group_code VARCHAR2,
921 x_quota_unspecified NUMBER,
922 x_addup_from_rev_class_flag VARCHAR2,
923 x_attribute1 VARCHAR2,
924 x_attribute2 VARCHAR2,
925 x_attribute3 VARCHAR2,
926 x_attribute4 VARCHAR2,
927 x_attribute5 VARCHAR2,
928 x_attribute6 VARCHAR2,
929 x_attribute7 VARCHAR2,
930 x_attribute8 VARCHAR2,
931 x_attribute9 VARCHAR2,
932 x_attribute10 VARCHAR2,
933 x_attribute11 VARCHAR2,
934 x_attribute12 VARCHAR2,
935 x_attribute13 VARCHAR2,
936 x_attribute14 VARCHAR2,
937 x_attribute15 VARCHAR2,
938 -- fmburu r12
939 x_indirect_credit VARCHAR2
940 )
941 IS
942 CURSOR c
943 IS
944 SELECT *
945 FROM cn_quotas
946 WHERE quota_id = x_quota_id
947 FOR UPDATE OF quota_id NOWAIT;
948
949 recinfo c%ROWTYPE;
950 temp DATE;
951 BEGIN
952 OPEN c;
953
954 FETCH c
955 INTO recinfo;
956
957 IF c%NOTFOUND
958 THEN
959 CLOSE c;
960
961 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
962 app_exception.raise_exception;
963 END IF;
964
965 CLOSE c;
966
967 IF ( (recinfo.quota_id = x_quota_id)
968 AND (recinfo.NAME = x_name OR (recinfo.NAME IS NULL AND x_name IS NULL))
969 AND (recinfo.quota_type_code = x_quota_type_code OR (recinfo.quota_type_code IS NULL AND x_quota_type_code IS NULL))
970 AND (recinfo.description = x_description OR (recinfo.description IS NULL AND x_description IS NULL))
971 AND (TRUNC (recinfo.start_date) = TRUNC (x_start_date) OR (recinfo.start_date IS NULL AND x_start_date IS NULL))
972 AND (TRUNC (recinfo.end_date) = TRUNC (x_end_date) OR (recinfo.end_date IS NULL AND x_end_date IS NULL))
973 AND (recinfo.payee_assign_flag = x_payee_assign_flag OR (recinfo.payee_assign_flag IS NULL AND x_payee_assign_flag IS NULL))
974 AND (recinfo.vesting_flag = x_vesting_flag OR (recinfo.vesting_flag IS NULL AND x_vesting_flag IS NULL))
975 AND (recinfo.calc_formula_id = x_calc_formula_id OR (recinfo.calc_formula_id IS NULL AND x_calc_formula_id IS NULL))
976 AND (recinfo.credit_type_id = x_credit_type_id OR (recinfo.credit_type_id IS NULL AND x_credit_type_id IS NULL))
977 AND (recinfo.package_name = x_package_name OR (recinfo.package_name IS NULL AND x_package_name IS NULL))
978 AND (recinfo.interval_type_id = x_interval_type_id OR (recinfo.interval_type_id IS NULL AND x_interval_type_id IS NULL))
979 AND ( recinfo.addup_from_rev_class_flag = x_addup_from_rev_class_flag
980 OR (recinfo.addup_from_rev_class_flag IS NULL AND x_addup_from_rev_class_flag IS NULL)
981 )
982 )
983 THEN
984 RETURN;
985 ELSE
986 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
987 app_exception.raise_exception;
988 END IF;
989 END LOCK_RECORD;
990
991 -----------------------------------------------------------------------------+
992 --| Public Routine Bodies |
993 -----------------------------------------------------------------------------+
994 -- Name
995
996 -- Purpose
997
998 -- Notes
999 PROCEDURE begin_record (
1000 x_operation VARCHAR2,
1001 x_rowid IN OUT NOCOPY VARCHAR2,
1002 x_quota_id IN OUT NOCOPY NUMBER,
1003 x_object_version_number OUT NOCOPY NUMBER,
1004 x_name VARCHAR2,
1005 x_target NUMBER,
1006 x_quota_type_code VARCHAR2,
1007 x_period_type_code VARCHAR2,
1008 x_usage_code VARCHAR2,
1009 x_payment_amount NUMBER,
1010 x_description VARCHAR2,
1011 x_start_date DATE,
1012 x_end_date DATE,
1013 x_quota_status VARCHAR2,
1014 x_start_num NUMBER,
1015 x_end_num NUMBER,
1016 x_program_type VARCHAR2,
1017 --x_status_code VARCHAR2,
1018 x_last_update_date DATE,
1019 x_last_updated_by NUMBER,
1020 x_creation_date DATE,
1021 x_created_by NUMBER,
1022 x_last_update_login NUMBER,
1023 x_incentive_type_code VARCHAR2,
1024 x_credit_type_id NUMBER,
1025 x_calc_formula_id NUMBER,
1026 x_rt_sched_custom_flag VARCHAR2,
1027 x_package_name VARCHAR2,
1028 x_performance_goal NUMBER,
1029 x_interval_type_id NUMBER,
1030 x_payee_assign_flag VARCHAR2,
1031 x_vesting_flag VARCHAR2,
1032 x_quota_unspecified NUMBER,
1033 x_addup_from_rev_class_flag VARCHAR2,
1034 x_expense_account_id NUMBER,
1035 x_liability_account_id NUMBER,
1036 x_quota_group_code VARCHAR2,
1037 --clku PAYMENT ENHANCEMENT
1038 x_payment_group_code VARCHAR2 := 'STANDARD',
1039 --clku, bug 2854576
1040 x_attribute_category VARCHAR2 := NULL,
1041 x_attribute1 VARCHAR2,
1042 x_attribute2 VARCHAR2,
1043 x_attribute3 VARCHAR2,
1044 x_attribute4 VARCHAR2,
1045 x_attribute5 VARCHAR2,
1046 x_attribute6 VARCHAR2,
1047 x_attribute7 VARCHAR2,
1048 x_attribute8 VARCHAR2,
1049 x_attribute9 VARCHAR2,
1050 x_attribute10 VARCHAR2,
1051 x_attribute11 VARCHAR2,
1052 x_attribute12 VARCHAR2,
1053 x_attribute13 VARCHAR2,
1054 x_attribute14 VARCHAR2,
1055 x_attribute15 VARCHAR2,
1056 -- fmburu r12
1057 x_indirect_credit VARCHAR2,
1058 x_org_id NUMBER,
1059 x_salesrep_end_flag VARCHAR2
1060 )
1061 IS
1062 BEGIN
1063 -- Saves passing it around
1064 g_program_type := x_program_type;
1065 g_temp_status_code := 'COMPLETE';
1066
1067 IF x_operation = 'INSERT'
1068 THEN
1069 INSERT_RECORD (x_rowid,
1070 x_quota_id,
1071 x_object_version_number,
1072 x_name,
1073 x_target,
1074 x_quota_type_code,
1075 x_period_type_code,
1076 x_usage_code,
1077 x_payment_amount,
1078 x_description,
1079 x_start_date,
1080 x_end_date,
1081 x_quota_status,
1082 x_start_num,
1083 x_end_num,
1084 x_last_update_date,
1085 x_last_updated_by,
1086 x_creation_date,
1087 x_created_by,
1088 x_last_update_login,
1089 x_incentive_type_code,
1090 x_credit_type_id,
1091 x_calc_formula_id,
1092 x_rt_sched_custom_flag,
1093 x_package_name,
1094 x_performance_goal,
1095 x_interval_type_id,
1096 x_payee_assign_flag,
1097 x_vesting_flag,
1098 x_quota_unspecified,
1099 x_addup_from_rev_class_flag,
1100 x_expense_account_id,
1101 x_liability_account_id,
1102 x_quota_group_code,
1103 --clku, PAYMENT ENHANCEMENT
1104 x_payment_group_code,
1105 --clku, 2854576
1106 x_attribute_category,
1107 x_attribute1,
1108 x_attribute2,
1109 x_attribute3,
1110 x_attribute4,
1111 x_attribute5,
1112 x_attribute6,
1113 x_attribute7,
1114 x_attribute8,
1115 x_attribute9,
1116 x_attribute10,
1117 x_attribute11,
1118 x_attribute12,
1119 x_attribute13,
1120 x_attribute14,
1121 x_attribute15,
1122 -- fmburu r12
1123 x_indirect_credit,
1124 x_org_id,
1125 nvl(x_salesrep_end_flag,'N')
1126 );
1127 ELSIF x_operation = 'UPDATE'
1128 THEN
1129 UPDATE_RECORD (x_quota_id,
1130 x_object_version_number,
1131 x_name,
1132 x_target,
1133 x_quota_type_code,
1134 x_period_type_code,
1135 x_usage_code,
1136 x_payment_amount,
1137 x_description,
1138 x_start_date,
1139 x_end_date,
1140 x_quota_status,
1141 x_start_num,
1142 x_end_num,
1143 x_last_update_date,
1144 x_last_updated_by,
1145 x_last_update_login,
1146 x_incentive_type_code,
1147 x_credit_type_id,
1148 x_calc_formula_id,
1149 x_rt_sched_custom_flag,
1150 x_package_name,
1151 x_performance_goal,
1152 x_interval_type_id,
1153 x_payee_assign_flag,
1154 x_vesting_flag,
1155 x_quota_unspecified,
1156 x_addup_from_rev_class_flag,
1157 x_expense_account_id,
1158 x_liability_account_id,
1159 x_quota_group_code,
1160 --clku, PAYMENT ENHANCEMENT
1161 x_payment_group_code,
1162 --clku, 2854576
1163 x_attribute_category,
1164 x_attribute1,
1165 x_attribute2,
1166 x_attribute3,
1167 x_attribute4,
1168 x_attribute5,
1169 x_attribute6,
1170 x_attribute7,
1171 x_attribute8,
1172 x_attribute9,
1173 x_attribute10,
1174 x_attribute11,
1175 x_attribute12,
1176 x_attribute13,
1177 x_attribute14,
1178 x_attribute15,
1179 -- fmburu r12
1180 x_indirect_credit,
1181 x_salesrep_end_flag
1182 );
1183 NULL;
1184 ELSIF x_operation = 'LOCK'
1185 THEN
1186 LOCK_RECORD (x_rowid,
1187 x_quota_id,
1188 x_object_version_number,
1189 x_name,
1190 x_target,
1191 x_description,
1192 x_quota_type_code,
1193 x_period_type_code,
1194 x_usage_code,
1195 x_payment_amount,
1196 x_start_date,
1197 x_end_date,
1198 x_quota_status,
1199 x_start_num,
1200 x_end_num,
1201 x_incentive_type_code,
1202 x_credit_type_id,
1203 x_calc_formula_id,
1204 x_rt_sched_custom_flag,
1205 x_package_name,
1206 x_performance_goal,
1207 x_interval_type_id,
1208 x_payee_assign_flag,
1209 x_vesting_flag,
1210 x_quota_group_code,
1211 x_quota_unspecified,
1212 x_addup_from_rev_class_flag,
1213 x_attribute1,
1214 x_attribute2,
1215 x_attribute3,
1216 x_attribute4,
1217 x_attribute5,
1218 x_attribute6,
1219 x_attribute7,
1220 x_attribute8,
1221 x_attribute9,
1222 x_attribute10,
1223 x_attribute11,
1224 x_attribute12,
1225 x_attribute13,
1226 x_attribute14,
1227 x_attribute15,
1228 -- fmburu r12
1229 x_indirect_credit
1230 );
1231 ELSIF x_operation = 'DELETE'
1232 THEN
1233 DELETE_RECORD (x_quota_id, x_name);
1234 END IF;
1235 END begin_record;
1236 END cn_quotas_pkg;