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