[Home] [Help]
PACKAGE BODY: APPS.CN_COMP_PLANS_PKG
Source
1 PACKAGE BODY CN_COMP_PLANS_PKG as
2 /* $Header: cnpliplb.pls 120.6.12010000.4 2008/09/08 16:05:26 ramchint ship $ */
3 /*
4 Date Name Description
5 -------------------------------------------------------------------------+-+
6 --06-MAR-95 P Cook Fixed check_unique_rev_class (bug 268849)
7 --07-APR-95 A Erickson In set_status: added call to Comp_Plan_Event.
8 --19-JUL-95 P Cook Added quota period > plan period comparison to
9 -- the validation routine
10 --28-JUL-95 P Cook Added when others to delete_record to prevent
11 -- unhandled exception when deleting an unassigned plan
12 --04-AUG-95 P Cook Removed comparison of quota periods and planm periods
13 -- no restrictions anymore.
14
15 --Purpose
16
17 --When we encounter data problems during execution of a conc program we do not
18 --stop processing and raise an error. We simply write the error to the plsql
19 --table and continue processing.
20 --When we encounter problems in a form we want to either stop further
21 --processing until the problem is fixed or display a warning before continuing.
22
23 -- currently not going to get plan name for insert/update/delete messages
24 -- issued in batch. when we write the batch code and get the message stack
25 -- working can replace this single line messaging with an outlined set
26 -- of messages broken by object. current messaging is targeted at the form
27 -- this limitiation applies to all comp plan objects
28 */
29
30 -- -------------------------------------------------------------------------+
31 -- | Custom Validation
32 -- -------------------------------------------------------------------------+
33
34
35 -------------------------------------------------------------------------++
36 -- ProcedureName: Get_plan_name
37 -------------------------------------------------------------------------++
38 FUNCTION Get_plan_name ( X_Comp_Plan_Id NUMBER) RETURN VARCHAR2 IS
39 g_plan_name VARCHAR2(80);
40 BEGIN
41
42 SELECT name
43 INTO g_plan_name
44 FROM cn_comp_plans
45 WHERE comp_plan_id = x_comp_plan_id;
46
47 return g_plan_name;
48 exception
49 when no_data_found then
50 return null;
51 END Get_plan_name;
52
53 -- Procedure Name
54 --
55 -- Purpose
56 -- Prevent the plan being made inactive in a period that is already
57 -- assigned to a salesrep.
58 --
59 -- Notes
60 --
61 -- -------------------------------------------------------------------------+
62 -- Procedure Name: Check_period_range
63 -- -------------------------------------------------------------------------+
64 FUNCTION check_period_range
65 ( X_Comp_Plan_Id NUMBER
66 ,X_Start_Date DATE
67 ,X_End_Date DATE) RETURN BOOLEAN IS
68
69 -- return true if period range OK, false o/w
70 X_dummy NUMBER;
71 BEGIN
72 SELECT count(1)
73 INTO x_dummy
74 FROM cn_srp_plan_assigns
75 WHERE comp_plan_id = x_comp_plan_id
76 AND ( start_date < x_start_date
77 OR end_date > x_end_date);
78
79 if x_dummy > 0 then
80 fnd_message.set_name('CN','PLN_PLAN_PRD_LT_SRP_PRD');
81 fnd_msg_pub.add;
82 return false;
83 end if;
84 return true;
85 END check_period_range;
86
87 -- Procedure Name
88 -- Check_unique_rev_class
89 -- Purpose
90 -- Ensure there are no duplicate revenue classes assigned to a comp plan.
91 --
92 -- Notes
93 --
94 -- Will not be called if the plan does not have any quota rules.
95 -- (Manual and draw quotas do not have quota rules)
96 -- Gets all subordinate revenue classes by exploding the parent revenue
97 -- classes entered on the plan (explosion includes the parent rev class).
98 -- If there are less unique values than present in the explosion we know
99 -- there are duplicate rev classes.
100 -- ------------------------------------------------------------------------+
101 -- Procedure Name: check_unique_rev_class
102 -- ------------------------------------------------------------------------+
103 -- clku, mblum, fixed bug 2870590
104 FUNCTION check_unique_rev_class
105 ( x_comp_plan_id NUMBER
106 ,x_name VARCHAR2
107 ,x_allow_rev_class_overlap VARCHAR2
108 ,x_sum_trx_flag VARCHAR2) RETURN BOOLEAN IS
109
110 -- return true if okay, false o/w
111 x_rev_class_total NUMBER := 0;
112 x_rev_class_total_unique NUMBER := 0;
113 l_count number := 0;
114 l_start_date date;
115 l_end_date date;
116
117 cursor get_pe_info is
118 select q.quota_id, q.start_date, q.end_date
119 from cn_quotas_v q,
120 cn_quota_assigns qa
121 where qa.comp_plan_id = x_comp_plan_id
122 and q.quota_id = qa.quota_id
123 and q.quota_type_code IN ('EXTERNAL', 'FORMULA');
124
125 type pe_tbl_type is table of get_pe_info%rowtype index by binary_integer;
126 pes pe_tbl_type;
127
128 cursor check_overlap_curs(l_rev_class_hierarchy_id number,l_start_date date,
129 l_end_date date, l_quota_id1 number, l_quota_id2 number) IS
130 SELECT count(de.value_external_id),
131 count(distinct de.value_external_id)
132 FROM cn_dim_explosion de,
133 cn_quota_rules qr,
134 cn_dim_hierarchies dh
135 WHERE
136 -- dh.header_dim_hierarchy_id = l_rev_class_hierarchy_id AND
137 l_start_date <= nvl(dh.end_date, l_start_date)
138 AND dh.start_date <= nvl(l_end_date, dh.start_date)
139 AND de.dim_hierarchy_id = dh.dim_hierarchy_id
140 AND de.ancestor_external_id = qr.revenue_class_id
141 AND qr.quota_id in (l_quota_id1, l_quota_id2);
142
143 begin
144 IF x_allow_rev_class_overlap = 'N' THEN
145 for q in get_pe_info loop
146 pes(l_count) := q;
147 l_count := l_count + 1;
148 end loop;
149
150 for q1 in 0..l_count-1 loop
151 for q2 in q1+1..l_count-1 loop
152 l_start_date := greatest(pes(q1).start_date, pes(q2).start_date);
153 l_end_date := least(nvl(pes(q1).end_date, pes(q2).end_date),
154 nvl(pes(q2).end_date, pes(q1).end_date));
155
156 if l_start_date <= nvl(l_end_date, l_start_date) then
157 OPEN check_overlap_curs(cn_global_var.g_rev_class_hierarchy_id,
158 l_start_date, l_end_date, pes(q1).quota_id, pes(q2).quota_id);
159 FETCH check_overlap_curs INTO x_rev_class_total, x_rev_class_total_unique;
160 CLOSE check_overlap_curs;
161
162 IF x_rev_class_total <> x_rev_class_total_unique THEN
163 fnd_message.set_name('CN', 'PLN_PLAN_DUP_REV_CLASS');
164 fnd_message.set_token('PLAN_NAME',
165 get_plan_name(x_comp_plan_id));
166 fnd_msg_pub.add;
167 return false;
168 END IF;
169 end if;
170 end loop;
171 end loop;
172 END IF;
173 return true;
174 END check_unique_rev_class;
175
176 -- Procedure Name
177 --
178 -- Purpose
179 -- Prevent deletion of a plan if it is currently assigned to a salesrep
180 -- Notes
181 --
182 -- ------------------------------------------------------------------------+
183 -- Procedure Name: Check_Assigned
184 -- ------------------------------------------------------------------------+
185 FUNCTION Check_Assigned( X_Comp_Plan_Id NUMBER) RETURN BOOLEAN IS
186 -- return true if okay, false o/w
187 X_dummy NUMBER;
188 BEGIN
189 SELECT count(1)
190 INTO x_dummy
191 FROM cn_srp_plan_assigns
192 WHERE comp_plan_id = x_comp_plan_id;
193
194 if x_dummy > 0 then
195 fnd_message.set_name('CN', 'PLN_PLAN_DELETE_NA');
196 fnd_msg_pub.add;
197 return false;
198 end if;
199
200 SELECT count(1)
201 INTO x_dummy
202 FROM cn_role_plans
203 WHERE comp_plan_id = x_comp_plan_id;
204
205 if x_dummy > 0 then
206 fnd_message.set_name('CN', 'CN_ROLE_PLAN_ASSIGNED');
207 fnd_msg_pub.add;
208 return false;
209 end if;
210 return true;
211
212 END Check_Assigned;
213
214
215 -------------------------------------------------------------------------++
216 --Procedure Name : Get Status
217 -------------------------------------------------------------------------++
218 PROCEDURE Get_status
219 ( X_Comp_Plan_Id NUMBER
220 ,X_status_code IN OUT NOCOPY VARCHAR2
221 ,X_status IN OUT NOCOPY VARCHAR2) IS
222 BEGIN
223 SELECT lc.meaning
224 ,lc.lookup_code
225 INTO X_Status
226 ,X_status_code
227 FROM cn_lookups lc,
228 cn_comp_plans cp
229 WHERE cp.status_code = lc.lookup_code
230 AND lc.lookup_type = 'PLAN_OBJECT_STATUS'
231 AND cp.comp_plan_id = x_comp_plan_id
232 ;
233
234 EXCEPTION
235 WHEN no_data_found THEN X_status := NULL;
236
237 END Get_status;
238
239 -- Name
240 --
241 -- Purpose
242 --
243 -- Notes
244 --
245 --
246 -------------------------------------------------------------------------++
247 -- Get_Uid
248 -------------------------------------------------------------------------++
249 PROCEDURE Get_Uid ( X_Comp_Plan_Id IN OUT NOCOPY NUMBER ) IS
250 BEGIN
251 IF X_Comp_Plan_Id IS NULL OR X_Comp_Plan_Id = CN_API.G_MISS_ID OR X_Comp_Plan_Id = FND_API.G_MISS_NUM THEN
252 SELECT cn_comp_plans_s.nextval
253 INTO x_comp_plan_id
254 FROM sys.dual;
255 END IF;
256 END Get_uid;
257
258 -- Name
259 --
260 -- Purpose
261 -- Set the plan status to incomplete.
262 -- Notes
263 -- Only one of the 3 id's (comp_plan_id, quota_id,rate_schedule_id) will
264 -- not be null at any one time.
265 -- Unable to make status_code an OUT parameter as it will mark the plan
266 -- Changing the date range on a plan does not impact the srp plan assigns
267 -- and therefore no need to maintain the srp period records
268 --
269 -- Called when
270 -- o The user tries to 'complete' the plan by the cn_comp_plans_pkg.
271 -- end_record
272 -- o After update of rate table
273 -- o After update of quota
274 -------------------------------------------------------------------------+
275 -- Procedure Name : Set_status
276 -------------------------------------------------------------------------+
277 PROCEDURE set_status
278 ( x_comp_plan_id NUMBER
279 ,x_quota_id NUMBER
280 ,x_rate_schedule_id NUMBER
281 ,x_status_code VARCHAR2
282 ,x_event VARCHAR2) IS
283
284 CURSOR schedules IS
285 SELECT pa.salesrep_id
286 ,pa.start_date
287 FROM cn_srp_plan_assigns pa
288 WHERE pa.comp_plan_id IN (SELECT qa.comp_plan_id
289 FROM cn_quota_assigns qa,
290 cn_quotas_v q,
291 cn_rt_quota_asgns rqa
292 WHERE qa.quota_id = q.quota_id
293 AND q.quota_id = rqa.quota_id
294 AND rqa.rate_schedule_id = X_rate_schedule_id)
295 ORDER BY pa.salesrep_id, pa.start_date;
296
297 CURSOR quotas IS
298 SELECT pa.salesrep_id
299 ,pa.start_date
300 FROM cn_srp_plan_assigns pa
301 WHERE pa.comp_plan_id IN (SELECT qa.comp_plan_id
302 FROM cn_quota_assigns qa
303 WHERE qa.quota_id = x_quota_id)
304 ORDER BY pa.salesrep_id, pa.start_date;
305
306 schedule_rec schedules%ROWTYPE;
307 quota_rec quotas%ROWTYPE;
308 x_last_date DATE;
309 x_last_salesrep_id NUMBER;
310
311 BEGIN
312
313 x_last_date := NULL;
314 x_last_salesrep_id := NULL;
315
316 -- Log the comp plan event.
317 /* IF x_comp_plan_id IS NOT NULL THEN
318 cn_event_log_pkg.comp_plan_event( x_comp_plan_id ) ;
319 END IF ;*/
320 IF x_comp_plan_id IS NOT NULL THEN
321
322 -- Called after comp plan update
323 UPDATE cn_comp_plans
324 SET status_code = x_status_code
325 WHERE comp_plan_id = x_comp_plan_id
326 ;
327
328 ELSIF x_quota_id IS NOT NULL THEN
329
330 -- called after quota, quota rule or trx factor update
331 UPDATE cn_comp_plans
332 SET status_code = x_status_code
333 WHERE comp_plan_id IN (SELECT qa.comp_plan_id
334 FROM cn_quota_assigns qa
335 WHERE qa.quota_id = x_quota_id)
336 ;
337
338 IF x_status_code = 'INCOMPLETE' THEN
339 FOR quota_rec IN quotas LOOP
340
341 -- We only need to call mark_event once for each salesrep. If we can
342 -- find the earliest period in which they are assigned to
343 -- this schedule we can mark all periods forward if it
344
345 IF((quota_rec.salesrep_id = nvl(x_last_salesrep_id,
346 quota_rec.salesrep_id)
347 AND quota_rec.start_date < nvl(x_last_date,
348 quota_rec.start_date+1) )
349 OR quota_rec.salesrep_id <> nvl(x_last_salesrep_id,
350 quota_rec.salesrep_id)
351 ) THEN
352
353 x_last_date := quota_rec.start_date;
354 x_last_salesrep_id := quota_rec.salesrep_id;
355
356 -- cn_srp_periods_pkg.Mark_Event(
357 -- X_salesrep_id => quota_rec.salesrep_id
358 --,X_start_period_id => quota_rec.start_period_id
359 --,X_end_period_id => null
360 --,X_event => x_event);*/
361 END IF;
362
363 END LOOP;
364
365 END IF;
366
367 ELSIF x_rate_schedule_id IS NOT NULL THEN
368
369 -- Called after rate table update
370 UPDATE cn_comp_plans
371 SET status_code = x_status_code
372 WHERE comp_plan_id IN (SELECT qa.comp_plan_id
373 FROM cn_quota_assigns qa,
374 cn_quotas_v q,
375 cn_rt_quota_asgns rqa
376 WHERE qa.quota_id = q.quota_id
377 AND q.quota_id = rqa.quota_id
378 AND rqa.rate_schedule_id = X_rate_schedule_id)
379 ;
380
381 IF x_status_code = 'INCOMPLETE' THEN
382 FOR schedule_rec IN schedules LOOP
383
384 -- We only need to call mark_event once for each salesrep. If we can
385 -- find the earliest period in which they are using this schedule
386 -- we can mark all periods forward if it
387 --
388
389 IF((schedule_rec.salesrep_id = nvl(x_last_salesrep_id,
390 schedule_rec.salesrep_id)
391 AND schedule_rec.start_date < nvl(x_last_date,
392 schedule_rec.start_date+1) )
393 OR schedule_rec.salesrep_id <> nvl(x_last_salesrep_id,
394 schedule_rec.salesrep_id)
395 ) THEN
396
397 x_last_date := schedule_rec.start_date;
398 x_last_salesrep_id := quota_rec.salesrep_id;
399
400 -- cn_srp_periods_pkg.Mark_Event(
401 -- X_salesrep_id => schedule_rec.salesrep_id
402 -- ,X_start_period_id => schedule_rec.start_period_id
403 -- ,X_end_period_id => null
404 -- ,X_event => x_event); */
405 END IF;
406 END LOOP;
407
408 END IF;
409
410
411
412 END IF;
413
414 END Set_Status;
415
416
417 -------------------------------------------------------------------------+
418 -- Procedure Name : Insert_record
419 -------------------------------------------------------------------------+
420 PROCEDURE insert_record
421 (X_Rowid IN OUT NOCOPY VARCHAR2 ,
422 X_Comp_Plan_Id IN OUT NOCOPY NUMBER ,
423 X_Name VARCHAR2 ,
424 X_Last_Update_Date DATE ,
425 X_Last_Updated_By NUMBER ,
426 X_Creation_Date DATE ,
427 X_Created_By NUMBER ,
428 X_Last_Update_Login NUMBER ,
429 X_Description VARCHAR2 ,
430 X_Start_Date DATE ,
431 X_End_Date DATE ,
432 x_allow_rev_class_overlap VARCHAR2 ,
433 x_attribute_category VARCHAR2 ,
434 x_attribute1 VARCHAR2 ,
435 x_attribute2 VARCHAR2 ,
436 x_attribute3 VARCHAR2 ,
437 x_attribute4 VARCHAR2 ,
438 x_attribute5 VARCHAR2 ,
439 x_attribute6 VARCHAR2 ,
440 x_attribute7 VARCHAR2 ,
441 x_attribute8 VARCHAR2 ,
442 x_attribute9 VARCHAR2 ,
443 x_attribute10 VARCHAR2 ,
444 x_attribute11 VARCHAR2 ,
445 x_attribute12 VARCHAR2 ,
446 x_attribute13 VARCHAR2 ,
447 x_attribute14 VARCHAR2 ,
448 x_attribute15 VARCHAR2 ,
449 x_org_id NUMBER ,
450 x_sum_trx_flag VARCHAR2
451 ) IS
452
453
454 l_sum_trx_flag varchar2(1) := 'Y';
455
456 BEGIN
457
458 if x_sum_trx_flag = CN_API.G_MISS_CHAR or x_sum_trx_flag = 'N' then
459 l_sum_trx_flag :='N';
460 end if;
461
462
463
464
465 Get_UID( X_Comp_Plan_Id );
466
467 INSERT INTO
468 CN_COMP_PLANS
469 (
470 Comp_Plan_Id ,
471 Name ,
472 Last_Update_Date ,
473 Last_Updated_By ,
474 Creation_Date ,
475 Created_By ,
476 Last_Update_Login ,
477 Description ,
478 Start_date ,
479 End_date ,
480 status_code ,
481 allow_rev_class_overlap,
482 attribute_category ,
483 attribute1 ,
484 attribute2 ,
485 attribute3 ,
486 attribute4 ,
487 attribute5 ,
488 attribute6 ,
489 attribute7 ,
490 attribute8 ,
491 attribute9 ,
492 attribute10 ,
493 attribute11 ,
494 attribute12 ,
495 attribute13 ,
496 attribute14 ,
497 attribute15 ,
498 object_version_number,
499 org_id,
500 sum_trx_flag
501 )
502 VALUES
503 (
504 X_Comp_Plan_Id ,
505 X_Name ,
506 X_Last_Update_Date ,
507 X_Last_Updated_By ,
508 X_Creation_Date ,
509 X_Created_By ,
510 X_Last_Update_Login ,
511 X_Description ,
512 X_Start_Date ,
513 X_End_Date ,
514 'INCOMPLETE' ,
515 x_allow_rev_class_overlap,
516 x_attribute_category ,
517 x_attribute1 ,
518 x_attribute2 ,
519 x_attribute3 ,
520 x_attribute4 ,
521 x_attribute5 ,
522 x_attribute6 ,
523 x_attribute7 ,
524 x_attribute8 ,
525 x_attribute9 ,
526 x_attribute10 ,
527 x_attribute11 ,
528 x_attribute12 ,
529 x_attribute13 ,
530 x_attribute14 ,
531 x_attribute15 ,
532 0,
533 X_org_id,
534 l_sum_trx_flag
535 );
536
537 END Insert_Record;
538
539
540 -- Name
541 --
542 -- Purpose
543 --
544 -- Notes
545 --
546 --
547 -------------------------------------------------------------------------+
548 -- Procedure Name : Update_record
549 -------------------------------------------------------------------------+
550 PROCEDURE update_record
551 (
552 X_Comp_Plan_Id IN OUT NOCOPY NUMBER ,
553 X_Name VARCHAR2 ,
554 X_Last_Update_Date DATE ,
555 X_Last_Updated_By NUMBER ,
556 X_Last_Update_Login NUMBER ,
557 X_Description VARCHAR2 ,
558 X_Start_date DATE ,
559 X_Start_date_old DATE ,
560 X_End_date DATE ,
561 X_End_date_old DATE ,
562 x_status_code VARCHAR2 ,
563 x_allow_rev_class_overlap VARCHAR2 ,
564 x_allow_rev_class_overlap_old VARCHAR2,
565 x_sum_trx_flag VARCHAR2,
566 x_attribute_category VARCHAR2 ,
567 x_attribute1 VARCHAR2 ,
568 x_attribute2 VARCHAR2 ,
569 x_attribute3 VARCHAR2 ,
570 x_attribute4 VARCHAR2 ,
571 x_attribute5 VARCHAR2 ,
572 x_attribute6 VARCHAR2 ,
573 x_attribute7 VARCHAR2 ,
574 x_attribute8 VARCHAR2 ,
575 x_attribute9 VARCHAR2 ,
576 x_attribute10 VARCHAR2 ,
577 x_attribute11 VARCHAR2 ,
578 x_attribute12 VARCHAR2 ,
579 x_attribute13 VARCHAR2 ,
580 x_attribute14 VARCHAR2 ,
581 x_attribute15 VARCHAR2 ) IS
582
583 BEGIN
584 -- Reinstate when the package is called as a batch process
585 -- check_unique( x_comp_plan_id,x_Name);
586 -- check_period_range( x_comp_plan_id
587 -- ,x_start_period_id
588 -- ,x_end_period_id);
589 UPDATE cn_comp_plans
590 SET
591 Comp_Plan_Id = X_Comp_Plan_Id ,
592 Name = X_Name ,
593 Last_Update_Date = X_Last_Update_Date ,
594 Last_Updated_By = X_Last_Updated_By ,
595 Last_Update_Login = X_Last_Update_Login ,
596 Description = X_Description ,
597 Start_Date = X_Start_Date ,
598 End_Date = X_End_Date ,
599 allow_rev_class_overlap= x_allow_rev_class_overlap,
600 sum_trx_flag = x_sum_trx_flag,
601 attribute_category = x_attribute_category,
602 attribute1 = x_attribute1,
603 attribute2 = x_attribute2,
604 attribute3 = x_attribute3,
605 attribute4 = x_attribute4,
606 attribute5 = x_attribute5,
607 attribute6 = x_attribute6,
608 attribute7 = x_attribute7,
609 attribute8 = x_attribute8,
610 attribute9 = x_attribute9,
611 attribute10 = x_attribute10,
612 attribute11 = x_attribute11,
613 attribute12 = x_attribute12,
614 attribute13 = x_attribute13,
615 attribute14 = x_attribute14,
616 attribute15 = x_attribute15,
617 object_version_number = object_version_number + 1,
618 status_code =
619 decode(x_start_date , nvl(x_start_date_old,fnd_api.g_miss_date),
620 decode(x_end_date, nvl(x_end_date_old,fnd_api.g_miss_date),
621 decode(x_allow_rev_class_overlap,
622 nvl(x_allow_rev_class_overlap_old,'X'),
623 status_code, 'INCOMPLETE'
624 ), 'INCOMPLETE'
625 ), 'INCOMPLETE'
626 )
627 WHERE comp_plan_id = X_comp_plan_id;
628
629 IF (SQL%NOTFOUND) THEN
630 RAISE NO_DATA_FOUND;
631 END IF;
632
633 END Update_Record;
634
635
636 -------------------------------------------------------------------------+
637 -- Procedure Name : Lock Record
638 -------------------------------------------------------------------------+
639 PROCEDURE lock_record
640 ( X_Rowid VARCHAR2 ,
641 X_Comp_Plan_Id NUMBER ,
642 X_Name VARCHAR2 ,
643 X_Description VARCHAR2 ,
644 X_Start_date DATE ,
645 X_End_date DATE ,
646 x_allow_rev_class_overlap VARCHAR2 ,
647 x_sum_trx_flag VARCHAR2 ,
648 x_attribute_category VARCHAR2 ,
649 x_attribute1 VARCHAR2 ,
650 x_attribute2 VARCHAR2 ,
651 x_attribute3 VARCHAR2 ,
652 x_attribute4 VARCHAR2 ,
653 x_attribute5 VARCHAR2 ,
654 x_attribute6 VARCHAR2 ,
655 x_attribute7 VARCHAR2 ,
656 x_attribute8 VARCHAR2 ,
657 x_attribute9 VARCHAR2 ,
658 x_attribute10 VARCHAR2 ,
659 x_attribute11 VARCHAR2 ,
660 x_attribute12 VARCHAR2 ,
661 x_attribute13 VARCHAR2 ,
662 x_attribute14 VARCHAR2 ,
663 x_attribute15 VARCHAR2 ) IS
664
665 CURSOR C IS
666 SELECT * FROM CN_COMP_PLANS
667 WHERE comp_plan_id = X_Comp_Plan_id
668 FOR UPDATE OF COMP_PLAN_ID NOWAIT;
669 Recinfo C%ROWTYPE;
670
671 BEGIN
672 OPEN C;
673 FETCH C INTO Recinfo;
674 IF C%NOTFOUND THEN
675 CLOSE C;
676 fnd_message.set_name('FND','FORM_RECORD_DELETED');
677 app_exception.raise_exception;
678 END IF;
679 CLOSE C;
680 IF (
681 ( Recinfo.Comp_Plan_Id = x_comp_plan_id )
682 AND (Recinfo.Name = x_name )
683 AND (Recinfo.allow_rev_class_overlap= X_allow_rev_class_overlap)
684 AND ( (Recinfo.Description = x_description )
685 OR ( (Recinfo.description IS NULL )
686 AND (X_description IS NULL)
687 )
688 )
689 AND (Recinfo.Start_DATE = X_Start_DATE )
690 AND ( (Recinfo.End_date = x_end_date )
691 OR ( (Recinfo.End_date IS NULL )
692 AND (X_End_date IS NULL )
693 )
694 ) ) THEN
695 RETURN;
696 ELSE
697 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
698 app_exception.raise_exception;
699 END IF;
700
701 END Lock_Record;
702
703
704 -------------------------------------------------------------------------+
705 -- Procedure Name : Delete_Record
706 -------------------------------------------------------------------------+
707 PROCEDURE delete_record(x_comp_plan_id NUMBER) IS
708 l_okay boolean;
709 l_junk number;
710 l_exists number;
711 BEGIN
712
713 l_okay := Check_Assigned( X_Comp_Plan_Id);
714
715 IF not l_okay then
716 RAISE FND_API.G_EXC_ERROR ;
717 end if;
718
719 select 1 into l_exists FROM cn_comp_plans
720 WHERE comp_plan_id = x_comp_plan_id;
721
722 IF SQL%FOUND THEN
723 -- Delete all quota assignments that belong to the plan
724 -- this wil cascade to all child tables
725 cn_quota_assigns_pkg.begin_record
726 (X_Operation => 'DELETE',
727 X_Quota_Id => null, -- delete all quotas assigned to plan
728 X_Comp_Plan_Id => x_comp_plan_id,
729 X_Quota_Assign_Id => l_junk, -- not used
730 X_Quota_Sequence => null, -- not used
731 x_quota_id_old => null,
732 x_org_id => NULL);-- not used
733 END IF;
734
735 DELETE FROM cn_comp_plans
736 WHERE comp_plan_id = x_comp_plan_id;
737
738 END Delete_Record;
739 -------------------------------------------------------------------------+
740 -- PUBLIC ROUTINE BODIES
741 -------------------------------------------------------------------------+
742 -- Procedure Name
743 -- Check_Unique
744 -- Purpose
745 -- Detect a value that would cause a constraint violation
746 -------------------------------------------------------------------------+
747 -- CHECK UNIQUE
748 -------------------------------------------------------------------------+
749 FUNCTION Check_Unique( X_Comp_Plan_Id NUMBER
750 ,X_Name VARCHAR2) RETURN BOOLEAN IS
751 -- return true if ok, false o/w
752 X_dummy NUMBER;
753
754 BEGIN
755 SELECT count(1)
756 INTO X_dummy
757 FROM cn_comp_plans
758 WHERE name = X_Name
759 AND (X_Comp_Plan_Id IS NULL
760 OR X_Comp_Plan_Id <> comp_plan_id);
761
762 if x_dummy > 0 then
763 fnd_message.set_name('CN', 'PLN_PLAN_EXISTS');
764 fnd_msg_pub.add;
765 return false;
766 end if;
767 return true;
768
769 END Check_Unique;
770 -------------------------------------------------------------------------+
771 -- BEGIN RECORD
772 -------------------------------------------------------------------------+
773 -- Name
774 --
775 -- Purpose
776 --
777 -- Notes
778 --
779 --
780 PROCEDURE Begin_Record
781 (
782 X_Operation VARCHAR2
783 ,X_Rowid IN OUT NOCOPY VARCHAR2
784 ,X_Comp_Plan_Id IN OUT NOCOPY NUMBER
785 ,X_Name VARCHAR2
786 ,X_Last_Update_Date DATE
787 ,X_Last_Updated_By NUMBER
788 ,X_Creation_Date DATE
789 ,X_Created_By NUMBER
790 ,X_Last_Update_Login NUMBER
791 ,X_Description VARCHAR2
792 ,X_Start_date DATE
793 ,X_Start_date_old DATE
794 ,X_end_date DATE
795 ,X_end_date_old DATE
796 ,X_Program_Type VARCHAR2 -- not used
797 ,x_status_code VARCHAR2
798 ,x_allow_rev_class_overlap VARCHAR2
799 ,x_allow_rev_class_overlap_old VARCHAR2
800 ,x_sum_trx_flag VARCHAR2
801 ,x_attribute_category VARCHAR2
802 ,x_attribute1 VARCHAR2
803 ,x_attribute2 VARCHAR2
804 ,x_attribute3 VARCHAR2
805 ,x_attribute4 VARCHAR2
806 ,x_attribute5 VARCHAR2
807 ,x_attribute6 VARCHAR2
808 ,x_attribute7 VARCHAR2
809 ,x_attribute8 VARCHAR2
810 ,x_attribute9 VARCHAR2
811 ,x_attribute10 VARCHAR2
812 ,x_attribute11 VARCHAR2
813 ,x_attribute12 VARCHAR2
814 ,x_attribute13 VARCHAR2
815 ,x_attribute14 VARCHAR2
816 ,x_attribute15 VARCHAR2
817 ,x_org_id NUMBER
818 ) IS
819
820
821 BEGIN
822
823 IF X_Operation = 'INSERT' THEN
824
825
826 Insert_record ( X_Rowid ,
827 X_Comp_Plan_Id ,
828 X_Name ,
829 X_Last_Update_Date ,
830 X_Last_Updated_By ,
831 X_Creation_Date ,
832 X_Created_By ,
833 X_Last_Update_Login ,
834 X_Description ,
835 X_start_date ,
836 X_end_date ,
837 x_allow_rev_class_overlap,
838 x_attribute_category,
839 x_attribute1,
840 x_attribute2,
841 x_attribute3,
842 x_attribute4,
843 x_attribute5 ,
844 x_attribute6 ,
845 x_attribute7,
846 x_attribute8,
847 x_attribute9,
848 x_attribute10,
849 x_attribute11 ,
850 x_attribute12 ,
851 x_attribute13 ,
852 x_attribute14 ,
853 x_attribute15,
854 x_org_id,
855 x_sum_trx_flag
856 );
857
858 ELSIF X_Operation = 'UPDATE' THEN
859
860 Update_record (X_Comp_Plan_Id ,
861 X_Name ,
862 X_Last_Update_Date ,
863 X_Last_Updated_By ,
864 X_Last_Update_Login ,
865 X_Description ,
866 X_Start_date ,
867 X_Start_date_old ,
868 X_End_date ,
869 X_End_date_old ,
870 x_status_code ,
871 x_allow_rev_class_overlap,
872 x_allow_rev_class_overlap_old,
873 x_sum_trx_flag,
874 x_attribute_category,
875 x_attribute1 ,
876 x_attribute2 ,
877 x_attribute3 ,
878 x_attribute4 ,
879 x_attribute5 ,
880 x_attribute6 ,
881 x_attribute7,
882 x_attribute8,
883 x_attribute9,
884 x_attribute10,
885 x_attribute11 ,
886 x_attribute12 ,
887 x_attribute13 ,
888 x_attribute14 ,
889 x_attribute15 );
890 ELSIF X_Operation = 'LOCK' THEN
891
892 Lock_record ( X_Rowid ,
893 X_Comp_Plan_Id ,
894 X_Name ,
895 X_Description ,
896 X_Start_date ,
897 X_End_date ,
898 x_allow_rev_class_overlap,
899 x_sum_trx_flag,
900 x_attribute_category ,
901 x_attribute1 ,
902 x_attribute2 ,
903 x_attribute3 ,
904 x_attribute4 ,
905 x_attribute5 ,
906 x_attribute6 ,
907 x_attribute7 ,
908 x_attribute8 ,
909 x_attribute9 ,
910 x_attribute10 ,
911 x_attribute11 ,
912 x_attribute12 ,
913 x_attribute13 ,
914 x_attribute14 ,
915 x_attribute15
916 );
917 ELSIF X_Operation = 'DELETE' THEN
918
919 Delete_Record ( X_Comp_Plan_Id);
920
921 END IF;
922
923 END Begin_Record;
924 -------------------------------------------------------------------------+
925 -- END RECORD
926 -------------------------------------------------------------------------+
927 -- Procedure Name
928 --
929 -- Purpose
930 -- Write warning messages if:
931 -- 1. No quotas assigned
932 -- 2. Quotas include overlapping revenue classes
933 -- 3. quota active periods are outside the plan active periods
934 --
935 -- Notes
936 --
937 PROCEDURE End_Record
938 (
939 X_Rowid VARCHAR2 ,
940 X_Comp_Plan_Id NUMBER ,
941 X_Name VARCHAR2 ,
942 X_Description VARCHAR2 ,
943 x_start_date DATE ,
944 x_end_date DATE ,
945 X_Program_Type VARCHAR2 , -- not used
946 x_status_code VARCHAR2 ,
947 x_allow_rev_class_overlap VARCHAR2,
948 x_sum_trx_flag VARCHAR2) IS
949
950 CURSOR quotas IS
951 SELECT qa.quota_id
952 ,q.quota_type_code
953 ,q.name
954 FROM cn_quota_assigns qa,
955 cn_quotas_v q
956 WHERE qa.comp_plan_id = x_comp_plan_id
957 AND qa.quota_id = q.quota_id;
958 quota_rec quotas%ROWTYPE;
959
960 x_quota_status_code VARCHAR2(30);
961 x_dummy NUMBER := NULL;
962 l_Temp_Status_Code VARCHAR2(30);
963 l_okay BOOLEAN;
964 l_return_status varchar2(50);
965 l_msg_count NUMBER;
966 l_msg_data VARCHAR2(2000);
967 BEGIN
968 l_temp_status_code := 'COMPLETE';
969 x_quota_status_code := 'COMPLETE';
970
971 OPEN quotas;
972 LOOP
973 FETCH quotas into quota_rec;
974
975 IF quotas%rowcount = 0 THEN
976 l_temp_status_code := 'INCOMPLETE';
977 fnd_message.set_name('CN', 'PLN_PLAN_NO_QUOTAS');
978 fnd_message.set_token('PLAN_NAME', get_plan_name(x_comp_plan_id));
979 fnd_msg_pub.add;
980 exit;
981 ELSIF quotas%notfound THEN
982 -- at the end of the loop
983 exit;
984 END IF;
985
986 -- validate the quotas
987 /*cn_quotas_pkg.end_record
988 ( X_Rowid => null
989 ,X_Quota_Id => quota_rec.quota_id
990 ,X_Name => null
991 ,X_Rate_Schedule_Id => x_dummy
992 ,X_Target => null
993 ,X_Description => null
994 ,X_Program_Type => null
995 ,x_status_code => x_quota_status_code
996 ,x_plan_name => get_plan_name(x_comp_plan_id)
997 ,x_quota_type_code => quota_rec.quota_type_code);*/
998
999 cn_plan_element_pvt.validate_plan_element(
1000 p_api_version => 1.0 ,
1001 p_init_msg_list =>'F',
1002 p_commit => 'F',
1003 p_validation_level => fnd_api.g_valid_level_full,
1004 p_comp_plan_id => x_comp_plan_id,
1005 p_quota_id => quota_rec.quota_id,
1006 x_status_code => x_quota_status_code ,
1007 x_return_status => l_return_status,
1008 x_msg_count => l_msg_count,
1009 x_msg_data => l_msg_data);
1010
1011 -- return the status code from the quota
1012
1013 -- we want to zoom through all quotas writing their status to
1014 -- a table for reporting
1015 -- then simply set the status of the plan to 'incomplete' if
1016 -- ANY of the quotas are incomplete - hence use of
1017 -- quota_status_code set on first occurence of incomplete
1018 -- however because message stacking is not working yet
1019 -- we only display one message before setting the plan status
1020 -- to invalid
1021
1022 IF x_quota_status_code = 'INCOMPLETE' THEN
1023 l_temp_status_code := 'INCOMPLETE';
1024 exit;
1025 END IF;
1026
1027 END LOOP;
1028
1029 -- if no quotas are assigned or any the assigned quotas do not have quota
1030 -- rules (rev classes) then the status code will be incomplete and this
1031 -- uniqueness check will not be executed
1032
1033 IF l_temp_status_code = 'COMPLETE' THEN
1034
1035 l_okay := check_unique_rev_class
1036 ( x_comp_plan_Id
1037 ,x_name
1038 ,x_allow_rev_class_overlap,x_sum_trx_flag);
1039 if not l_okay then
1040 l_temp_status_code := 'INCOMPLETE';
1041 end if;
1042 END IF;
1043
1044 CLOSE quotas;
1045
1046 set_status ( x_comp_plan_id => x_comp_plan_id
1047 ,x_quota_id => null
1048 ,x_rate_schedule_id => null
1049 ,x_status_code => l_temp_status_code
1050 ,x_event => null);
1051
1052 x_quota_status_code := null;
1053
1054 END End_Record;
1055
1056 END CN_COMP_PLANS_PKG;