[Home] [Help]
PACKAGE BODY: APPS.CN_RT_QUOTA_ASGNS_PKG
Source
1 PACKAGE BODY cn_rt_quota_asgns_pkg AS
2 -- $Header: cnplirqab.pls 120.3 2006/01/10 04:27:22 rarajara ship $
3
4 -- Date Name Description
5 --------------------------------------------------------------------------------+
6 -- 10-MAR-99 Kumar Sivasankaran Created
7
8 -- Name : CN_RT_QUOTA_ASGNS_PKG
9 -- Purpose : Holds all server side packages used to insert a
10 -- rate quota asngs
11 -- Desc : Begin Record is called at the start of the commit cycle.
12 --------------------------------------------------------------------------------+
13
14 --------------------------------------------------------------------------------+
15 --
16 -- PRIVATE VARIABLES
17 --
18 --------------------------------------------------------------------------------+
19 g_temp_status_code VARCHAR2 (30) := NULL;
20 g_program_type VARCHAR2 (30) := NULL;
21
22 --------------------------------------------------------------------------------+
23 --
24 -- PRIVATE ROUTINES
25 --
26 --------------------------------------------------------------------------------+
27 -- Procedure Name
28 -- Get_UID
29 -- Purpose
30 -- Get the Sequence Number to Create a new rate quota Asgns
31 --------------------------------------------------------------------------------+
32 --
33 -- Procedure Get UID
34 --
35 --------------------------------------------------------------------------------+
36 PROCEDURE get_uid (
37 x_rt_quota_asgn_id IN OUT NOCOPY NUMBER
38 )
39 IS
40 BEGIN
41 SELECT cn_rt_quota_asgns_s.NEXTVAL
42 INTO x_rt_quota_asgn_id
43 FROM SYS.DUAL;
44 END get_uid;
45
46 --------------------------------------------------------------------------------+
47 --
48 -- Procedure Name Insert_Record
49 --
50 --------------------------------------------------------------------------------+
51 PROCEDURE INSERT_RECORD (
52 x_org_id IN NUMBER,
53 x_rowid IN OUT NOCOPY VARCHAR2,
54 x_rt_quota_asgn_id IN OUT NOCOPY NUMBER,
55 x_calc_formula_id NUMBER,
56 x_quota_id NUMBER,
57 x_start_date DATE,
58 x_end_date DATE,
59 x_rate_schedule_id NUMBER,
60 x_attribute_category VARCHAR2,
61 x_attribute1 VARCHAR2,
62 x_attribute2 VARCHAR2,
63 x_attribute3 VARCHAR2,
64 x_attribute4 VARCHAR2,
65 x_attribute5 VARCHAR2,
66 x_attribute6 VARCHAR2,
67 x_attribute7 VARCHAR2,
68 x_attribute8 VARCHAR2,
69 x_attribute9 VARCHAR2,
70 x_attribute10 VARCHAR2,
71 x_attribute11 VARCHAR2,
72 x_attribute12 VARCHAR2,
73 x_attribute13 VARCHAR2,
74 x_attribute14 VARCHAR2,
75 x_attribute15 VARCHAR2,
76 x_last_update_date DATE,
77 x_last_updated_by NUMBER,
78 x_creation_date DATE,
79 x_created_by NUMBER,
80 x_last_update_login NUMBER,
81 x_object_version_number IN OUT NOCOPY NUMBER
82 )
83 IS
84 BEGIN
85 -- Get Sequence Number
86 get_uid (x_rt_quota_asgn_id);
87 x_object_version_number := 1;
88
89 INSERT INTO cn_rt_quota_asgns
90 (org_id,
91 rt_quota_asgn_id,
92 calc_formula_id,
93 quota_id,
94 start_date,
95 end_date,
96 rate_schedule_id,
97 attribute_category,
98 attribute1,
99 attribute2,
100 attribute3,
101 attribute4,
102 attribute5,
103 attribute6,
104 attribute7,
105 attribute8,
106 attribute9,
107 attribute10,
108 attribute11,
109 attribute12,
110 attribute13,
111 attribute14,
112 attribute15,
113 last_update_date,
114 last_updated_by,
115 creation_date,
116 created_by,
117 last_update_login,
118 object_version_number
119 )
120 VALUES (x_org_id,
121 x_rt_quota_asgn_id,
122 x_calc_formula_id,
123 x_quota_id,
124 x_start_date,
125 x_end_date,
126 x_rate_schedule_id,
127 x_attribute_category,
128 x_attribute1,
129 x_attribute2,
130 x_attribute3,
131 x_attribute4,
132 x_attribute5,
133 x_attribute6,
134 x_attribute7,
135 x_attribute8,
136 x_attribute9,
137 x_attribute10,
138 x_attribute11,
139 x_attribute12,
140 x_attribute13,
141 x_attribute14,
142 x_attribute15,
143 x_last_update_date,
144 x_last_updated_by,
145 x_creation_date,
146 x_created_by,
147 x_last_update_login,
148 x_object_version_number
149 );
150
151 -- Insert the srp quota assigns, if srp plan plans assgins
152 cn_srp_rate_assigns_pkg.INSERT_RECORD (x_srp_plan_assign_id => NULL,
153 x_srp_quota_assign_id => NULL,
154 x_srp_rate_assign_id => NULL,
155 x_quota_id => x_quota_id,
156 x_rate_schedule_id => x_rate_schedule_id,
157 x_rt_quota_asgn_id => x_rt_quota_asgn_id,
158 x_rate_tier_id => NULL,
159 x_commission_rate => NULL,
160 x_commission_amount => NULL,
161 x_disc_rate_table_flag => NULL
162 );
163 END INSERT_RECORD;
164
165 -- Procedure Name
166 -- Lock_Record
167 -- Purpose
168 -- Lock db row after form record is changed
169 -- Notes
170 -- Only called from the form
171
172 --------------------------------------------------------------------------------+
173 --
174 -- Procedure Name Lock_Record
175 --
176 --------------------------------------------------------------------------------+
177 PROCEDURE LOCK_RECORD (
178 x_rowid VARCHAR2,
179 x_rt_quota_asgn_id NUMBER,
180 x_rate_schedule_id NUMBER,
181 x_start_date DATE,
182 x_end_date DATE
183 )
184 IS
185 CURSOR c
186 IS
187 SELECT *
188 FROM cn_rt_quota_asgns
189 WHERE rt_quota_asgn_id = x_rt_quota_asgn_id
190 FOR UPDATE OF rt_quota_asgn_id NOWAIT;
191
192 recinfo c%ROWTYPE;
193 BEGIN
194 OPEN c;
195
196 FETCH c
197 INTO recinfo;
198
199 IF (c%NOTFOUND)
200 THEN
201 CLOSE c;
202
203 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
204 app_exception.raise_exception;
205 END IF;
206
207 CLOSE c;
208
209 IF ( (recinfo.rt_quota_asgn_id = x_rt_quota_asgn_id)
210 AND (recinfo.rate_schedule_id = x_rate_schedule_id)
211 AND (TRUNC (recinfo.start_date) = TRUNC (x_start_date))
212 AND (TRUNC (recinfo.end_date) = TRUNC (x_end_date) OR (recinfo.end_date IS NULL AND x_end_date IS NULL))
213 )
214 THEN
215 RETURN;
216 ELSE
217 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
218 app_exception.raise_exception;
219 END IF;
220 END LOCK_RECORD;
221
222 -- Procedure Name
223 -- Update Record
224 -- Purpose
225 --
226 --------------------------------------------------------------------------------+
227 --
228 -- Procedure Name Update_Record
229 --
230 --------------------------------------------------------------------------------+
231 PROCEDURE UPDATE_RECORD (
232 x_rt_quota_asgn_id IN OUT NOCOPY NUMBER,
233 x_calc_formula_id NUMBER,
234 x_quota_id NUMBER,
235 x_start_date DATE,
236 x_end_date DATE,
237 x_rate_schedule_id NUMBER,
238 x_attribute_category VARCHAR2,
239 x_attribute1 VARCHAR2,
240 x_attribute2 VARCHAR2,
241 x_attribute3 VARCHAR2,
242 x_attribute4 VARCHAR2,
243 x_attribute5 VARCHAR2,
244 x_attribute6 VARCHAR2,
245 x_attribute7 VARCHAR2,
246 x_attribute8 VARCHAR2,
247 x_attribute9 VARCHAR2,
248 x_attribute10 VARCHAR2,
249 x_attribute11 VARCHAR2,
250 x_attribute12 VARCHAR2,
251 x_attribute13 VARCHAR2,
252 x_attribute14 VARCHAR2,
253 x_attribute15 VARCHAR2,
254 x_last_update_date DATE,
255 x_last_updated_by NUMBER,
256 x_creation_date DATE,
257 x_created_by NUMBER,
258 x_last_update_login NUMBER,
259 x_object_version_number IN OUT NOCOPY NUMBER
260 )
261 IS
262 CURSOR c
263 IS
264 SELECT *
265 FROM cn_rt_quota_asgns_all
266 WHERE rt_quota_asgn_id = x_rt_quota_asgn_id
267 FOR UPDATE OF rt_quota_asgn_id NOWAIT;
268
269 recinfo c%ROWTYPE;
270 BEGIN
271 OPEN c;
272
273 FETCH c
274 INTO recinfo;
275
276 CLOSE c;
277
278 x_object_version_number := NVL (recinfo.object_version_number, 1) + 1;
279
280 UPDATE cn_rt_quota_asgns
281 SET start_date = x_start_date,
282 end_date = x_end_date,
283 rate_schedule_id = x_rate_schedule_id,
284 calc_formula_id = x_calc_formula_id,
285 attribute_category = x_attribute_category,
286 attribute1 = x_attribute1,
287 attribute2 = x_attribute2,
288 attribute3 = x_attribute3,
289 attribute4 = x_attribute4,
290 attribute5 = x_attribute5,
291 attribute6 = x_attribute6,
292 attribute7 = x_attribute7,
293 attribute8 = x_attribute8,
294 attribute9 = x_attribute9,
295 attribute10 = x_attribute10,
296 attribute11 = x_attribute11,
297 attribute12 = x_attribute12,
298 attribute13 = x_attribute13,
299 attribute14 = x_attribute15,
300 attribute15 = x_attribute15,
301 last_update_date = x_last_update_date,
302 last_updated_by = x_last_updated_by,
303 last_update_login = x_last_update_login,
304 object_version_number = x_object_version_number
305 WHERE rt_quota_asgn_id = x_rt_quota_asgn_id;
306
307 IF (SQL%NOTFOUND)
308 THEN
309 RAISE NO_DATA_FOUND;
310 END IF;
311
312 -- set complan status
313 cn_comp_plans_pkg.set_status (x_comp_plan_id => NULL,
314 x_quota_id => x_quota_id,
315 x_rate_schedule_id => NULL,
316 x_status_code => 'INCOMPLETE',
317 x_event => 'CHANGE_TIERS'
318 );
319
320 -- srp rate assigns
321 IF (x_rate_schedule_id <> recinfo.rate_schedule_id) OR (x_calc_formula_id <> recinfo.calc_formula_id)
322 THEN
323 cn_srp_rate_assigns_pkg.DELETE_RECORD (x_srp_plan_assign_id => NULL,
324 x_srp_rate_assign_id => NULL,
325 x_quota_id => x_quota_id,
326 x_rate_schedule_id => recinfo.rate_schedule_id,
327 x_rt_quota_asgn_id => x_rt_quota_asgn_id,
328 x_rate_tier_id => NULL
329 );
330 -- Srp Rate Assigs
331 cn_srp_rate_assigns_pkg.INSERT_RECORD (x_srp_plan_assign_id => NULL,
332 x_srp_quota_assign_id => NULL,
333 x_srp_rate_assign_id => NULL,
334 x_quota_id => x_quota_id,
335 x_rate_schedule_id => x_rate_schedule_id,
336 x_rt_quota_asgn_id => x_rt_quota_asgn_id,
337 x_rate_tier_id => NULL,
338 x_commission_rate => NULL,
339 x_commission_amount => NULL,
340 x_disc_rate_table_flag => NULL
341 );
342 END IF;
343 -- Update Record
344 END UPDATE_RECORD;
345
346 -- Procedure Name
347
348 -- Delete_Record
349 -- Purpose
350 -- Logic yet to be discussed
351 --
352 --
353 --------------------------------------------------------------------------------+
354 --
355 -- Procedure Name Insert Record
356 --
357 --------------------------------------------------------------------------------+
358 PROCEDURE INSERT_RECORD (
359 x_calc_formula_id IN NUMBER,
360 x_quota_id IN NUMBER
361 )
362 IS
363 CURSOR calc_edge_curs (
364 l_parent_id NUMBER
365 )
366 IS
367 SELECT DISTINCT child_id
368 FROM cn_calc_edges
369 WHERE edge_type = 'FE' AND parent_id IN (SELECT calc_sql_exp_id
370 FROM cn_formula_inputs
371 WHERE calc_formula_id = l_parent_id
372 UNION
373 SELECT output_exp_id
374 FROM cn_calc_formulas
375 WHERE calc_formula_id = l_parent_id);
376
377 TYPE stack_type IS TABLE OF cn_calc_formulas.calc_formula_id%TYPE;
378
379 l_stack stack_type;
380 l_parent_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
381 l_child_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
382
383 CURSOR rt_quota_asgn_curs (
384 l_calc_formula_id NUMBER,
385 l_quota_id NUMBER
386 )
387 IS
388 SELECT rt_quota_asgn_id
389 FROM cn_rt_quota_asgns
390 WHERE quota_id = l_quota_id AND calc_formula_id = l_calc_formula_id;
391
392 l_rt_quota_asgn_id cn_rt_quota_asgns.rt_quota_asgn_id%TYPE;
393 BEGIN
394 l_stack := stack_type (x_calc_formula_id);
395
396 WHILE (l_stack.COUNT > 0)
397 LOOP
398 l_parent_calc_formula_id := l_stack (l_stack.LAST);
399 l_stack.DELETE (l_stack.LAST);
400
401 -- clku, bug 2812184, only insert if we have not seen this quota/formula
402 -- combination before
403 OPEN rt_quota_asgn_curs (l_parent_calc_formula_id, x_quota_id);
404
405 FETCH rt_quota_asgn_curs
406 INTO l_rt_quota_asgn_id;
407
408 IF rt_quota_asgn_curs%NOTFOUND
409 THEN
410 insert_node_record (l_parent_calc_formula_id, x_quota_id);
411 END IF;
412
413 CLOSE rt_quota_asgn_curs;
414
415 OPEN calc_edge_curs (l_parent_calc_formula_id);
416
417 LOOP
418 FETCH calc_edge_curs
419 INTO l_child_calc_formula_id;
420
421 IF calc_edge_curs%FOUND
422 THEN
423 l_stack.EXTEND;
424 l_stack (l_stack.LAST) := l_child_calc_formula_id;
425 ELSE
426 EXIT;
427 END IF;
428 END LOOP;
429
430 CLOSE calc_edge_curs;
431 END LOOP;
432 END INSERT_RECORD;
433
434 --------------------------------------------------------------------------------+
435 --
436 -- Procedure Name Insert Node Record
437 --
438 --------------------------------------------------------------------------------+
439 PROCEDURE insert_node_record (
440 x_calc_formula_id IN NUMBER,
441 x_quota_id IN NUMBER
442 )
443 IS
444 -- Procedure is use to call for inserting the record when you insert or
445 -- Update the Quotas. Called from CN_QUOTAS_PKG
446 -- cn_rt_quota-assings is a batch insert
447 -- insert the srp_rate_assigns
448 CURSOR srp_rate_insert_curs
449 IS
450 SELECT quota_id,
451 rate_schedule_id,
452 rt_quota_asgn_id
453 FROM cn_rt_quota_asgns_all
454 WHERE quota_id = x_quota_id AND calc_formula_id = x_calc_formula_id;
455
456 recinfo srp_rate_insert_curs%ROWTYPE;
457
458 --clku
459 CURSOR rate_formula_date_curs
460 IS
461 SELECT start_date,
462 end_date,
463 rate_schedule_id
464 FROM cn_rt_formula_asgns_all
465 WHERE calc_formula_id = x_calc_formula_id;
466
467 rt_date rate_formula_date_curs%ROWTYPE;
468 l_quota_start_date DATE := NULL;
469 l_quota_end_date DATE := NULL;
470 l_rt_start_date DATE := NULL;
471 l_rt_end_date DATE := NULL;
472 l_start_date DATE := NULL;
473 l_end_date DATE := NULL;
474 l_org_id NUMBER;
475 BEGIN
476 --clku
477 SELECT start_date,
478 end_date,
479 org_id
480 INTO l_quota_start_date,
481 l_quota_end_date,
482 l_org_id
483 FROM cn_quotas_all
484 WHERE quota_id = x_quota_id;
485
486 FOR rt_date IN rate_formula_date_curs
487 LOOP
488 l_rt_start_date := rt_date.start_date;
489 l_rt_end_date := rt_date.end_date;
490 -- bug 3602452 - reinitialize variables
491 l_start_date := NULL;
492 l_end_date := NULL;
493
494 -- 4 cases to get the overlap of l_rt_dates and l_quota_dates
495 IF (l_rt_end_date IS NULL AND l_quota_end_date IS NULL)
496 THEN
497 IF TRUNC (l_rt_start_date) >= TRUNC (l_quota_start_date)
498 THEN
499 l_start_date := l_rt_start_date;
500 ELSE
501 l_start_date := l_quota_start_date;
502 END IF;
503
504 l_end_date := NULL;
505 ELSIF (l_rt_end_date IS NULL AND (TRUNC (l_quota_end_date) > TRUNC (l_rt_start_date)))
506 THEN
507 IF TRUNC (l_rt_start_date) >= TRUNC (l_quota_start_date)
508 THEN
509 l_start_date := l_rt_start_date;
510 ELSE
511 l_start_date := l_quota_start_date;
512 END IF;
513
514 l_end_date := l_quota_end_date;
515 ELSIF (l_quota_end_date IS NULL AND (TRUNC (l_rt_end_date) > TRUNC (l_quota_start_date)))
516 THEN
517 IF TRUNC (l_rt_start_date) >= TRUNC (l_quota_start_date)
518 THEN
519 l_start_date := l_rt_start_date;
520 ELSE
521 l_start_date := l_quota_start_date;
522 END IF;
523
524 l_end_date := l_rt_end_date;
525 ELSIF ((TRUNC (l_rt_end_date) > TRUNC (l_quota_start_date)) OR (TRUNC (l_quota_end_date) > TRUNC (l_rt_start_date)))
526 THEN
527 IF TRUNC (l_rt_start_date) >= TRUNC (l_quota_start_date)
528 THEN
529 l_start_date := l_rt_start_date;
530 ELSE
531 l_start_date := l_quota_start_date;
532 END IF;
533
534 IF TRUNC (l_rt_end_date) <= TRUNC (l_quota_end_date)
535 THEN
536 l_end_date := l_rt_end_date;
537 ELSE
538 l_end_date := l_quota_end_date;
539 END IF;
540 END IF;
541
542 -- we only insert if there are overlap
543 -- clku, fix the date not overlap issue
544 IF ((l_start_date IS NOT NULL) AND (TRUNC (l_start_date) <= TRUNC (NVL (l_end_date, l_start_date))))
545 THEN
546 INSERT INTO cn_rt_quota_asgns_all
547 (rt_quota_asgn_id,
548 calc_formula_id,
549 quota_id,
550 start_date,
551 end_date,
552 rate_schedule_id,
553 org_id
554 )
555 SELECT cn_rt_quota_asgns_s.NEXTVAL,
556 x_calc_formula_id,
557 x_quota_id,
558 l_start_date,
559 l_end_date,
560 rt_date.rate_schedule_id,
561 l_org_id
562 FROM DUAL;
563 END IF;
564 END LOOP; -- for rt_date in rate_formula_date_curs LOOP
565
566 OPEN srp_rate_insert_curs;
567
568 LOOP
569 FETCH srp_rate_insert_curs
570 INTO recinfo;
571
572 EXIT WHEN srp_rate_insert_curs%NOTFOUND;
573 -- insert srp rate assigns for each insert int the rt_quota_assigns
574 cn_srp_rate_assigns_pkg.INSERT_RECORD (x_srp_plan_assign_id => NULL,
575 x_srp_quota_assign_id => NULL,
576 x_srp_rate_assign_id => NULL,
577 x_quota_id => recinfo.quota_id,
578 x_rate_schedule_id => recinfo.rate_schedule_id,
579 x_rt_quota_asgn_id => recinfo.rt_quota_asgn_id,
580 x_rate_tier_id => NULL,
581 x_commission_rate => NULL,
582 x_commission_amount => NULL,
583 x_disc_rate_table_flag => NULL
584 );
585 END LOOP;
586
587 CLOSE srp_rate_insert_curs;
588 END insert_node_record;
589
590 --------------------------------------------------------------------------------+
591 --
592 -- Procedure Name delete Record
593 --
594 --------------------------------------------------------------------------------+
595 PROCEDURE DELETE_RECORD (
596 x_quota_id IN NUMBER,
597 x_calc_formula_id IN NUMBER,
598 x_rt_quota_asgn_id IN NUMBER
599 )
600 IS
601 -- Procedure is use to call for deleting the record when you update the
602 -- Called from CN_QUOTAS_PKG
603 -- The folllowing query is re-written as two queries for fixing the
604 -- sql perf bug # 4932376
605 -- CURSOR srp_rate_assigns_delete IS
606 -- SELECT quota_id,
607 -- rate_schedule_id,
608 -- calc_formula_id
609 -- FROM cn_rt_quota_asgns
610 -- WHERE rt_quota_asgn_id = NVL (x_rt_quota_asgn_id, rt_quota_asgn_id)
611 -- AND quota_id = NVL (x_quota_id, quota_id);
612
613
614 CURSOR srp_rate_assigns_delete
615 IS
616 SELECT quota_id,
617 rate_schedule_id,
618 calc_formula_id
619 FROM cn_rt_quota_asgns
620 WHERE rt_quota_asgn_id = x_rt_quota_asgn_id AND quota_id = quota_id;
621
622 CURSOR srp_rate_assigns_delete1
623 IS
624 SELECT quota_id,
625 rate_schedule_id,
626 calc_formula_id
627 FROM cn_rt_quota_asgns
628 WHERE rt_quota_asgn_id = rt_quota_asgn_id AND quota_id = x_quota_id;
629
630 recinfo srp_rate_assigns_delete%ROWTYPE;
631 BEGIN
632 IF x_rt_quota_asgn_id IS NOT NULL
633 THEN
634 OPEN srp_rate_assigns_delete;
635
636 LOOP
637 FETCH srp_rate_assigns_delete
638 INTO recinfo;
639
640 EXIT WHEN srp_rate_assigns_delete%NOTFOUND;
641 -- delete srp rate assigns for each insert int the rt_quota_assigns
642 cn_srp_rate_assigns_pkg.DELETE_RECORD (x_srp_plan_assign_id => NULL,
643 x_srp_rate_assign_id => NULL,
644 x_quota_id => recinfo.quota_id,
645 x_rate_schedule_id => recinfo.rate_schedule_id,
646 x_rt_quota_asgn_id => x_rt_quota_asgn_id,
647 x_rate_tier_id => NULL
648 );
649 END LOOP;
650
651 CLOSE srp_rate_assigns_delete;
652
653 DELETE FROM cn_rt_quota_asgns
654 WHERE rt_quota_asgn_id = x_rt_quota_asgn_id;
655
656 cn_comp_plans_pkg.set_status (x_comp_plan_id => NULL,
657 x_quota_id => x_quota_id,
658 x_rate_schedule_id => NULL,
659 x_status_code => 'INCOMPLETE',
660 x_event => 'CHANGE_TIERS'
661 );
662 ELSIF x_quota_id IS NOT NULL
663 THEN
664 OPEN srp_rate_assigns_delete1;
665
666 LOOP
667 FETCH srp_rate_assigns_delete1
668 INTO recinfo;
669
670 EXIT WHEN srp_rate_assigns_delete1%NOTFOUND;
671 -- delete srp rate assigns for each insert int the rt_quota_assigns
672 cn_srp_rate_assigns_pkg.DELETE_RECORD (x_srp_plan_assign_id => NULL,
673 x_srp_rate_assign_id => NULL,
674 x_quota_id => recinfo.quota_id,
675 x_rate_schedule_id => recinfo.rate_schedule_id,
676 x_rate_tier_id => NULL
677 );
678 END LOOP;
679
680 CLOSE srp_rate_assigns_delete1;
681
682 -- drp rt Quota Asgns
683 DELETE FROM cn_rt_quota_asgns
684 WHERE quota_id = x_quota_id;
685 END IF;
686 END DELETE_RECORD;
687
688 --------------------------------------------------------------------------------+
689 --
690 -- PUBLIC ROUTINES
691 --
692 --------------------------------------------------------------------------------+
693 PROCEDURE begin_record (
694 x_org_id NUMBER,
695 x_operation VARCHAR2,
696 x_rowid IN OUT NOCOPY VARCHAR2,
697 x_rt_quota_asgn_id IN OUT NOCOPY NUMBER,
698 x_calc_formula_id NUMBER,
699 x_quota_id NUMBER,
700 x_start_date DATE,
701 x_end_date DATE,
702 x_rate_schedule_id NUMBER,
703 x_attribute_category VARCHAR2,
704 x_attribute1 VARCHAR2,
705 x_attribute2 VARCHAR2,
706 x_attribute3 VARCHAR2,
707 x_attribute4 VARCHAR2,
708 x_attribute5 VARCHAR2,
709 x_attribute6 VARCHAR2,
710 x_attribute7 VARCHAR2,
711 x_attribute8 VARCHAR2,
712 x_attribute9 VARCHAR2,
713 x_attribute10 VARCHAR2,
714 x_attribute11 VARCHAR2,
715 x_attribute12 VARCHAR2,
716 x_attribute13 VARCHAR2,
717 x_attribute14 VARCHAR2,
718 x_attribute15 VARCHAR2,
719 x_last_update_date DATE,
720 x_last_updated_by NUMBER,
721 x_creation_date DATE,
722 x_created_by NUMBER,
723 x_last_update_login NUMBER,
724 x_program_type VARCHAR2,
725 x_object_version_number IN OUT NOCOPY NUMBER
726 )
727 IS
728 BEGIN
729 -- Saves passing it around
730 g_program_type := x_program_type;
731 g_temp_status_code := 'COMPLETE'; -- Assume it is good to begin with
732
733 IF x_operation = 'INSERT'
734 THEN
735 INSERT_RECORD (x_org_id,
736 x_rowid,
737 x_rt_quota_asgn_id,
738 x_calc_formula_id,
739 x_quota_id,
740 x_start_date,
741 x_end_date,
742 x_rate_schedule_id,
743 x_attribute_category,
744 x_attribute1,
745 x_attribute2,
746 x_attribute3,
747 x_attribute4,
748 x_attribute5,
749 x_attribute6,
750 x_attribute7,
751 x_attribute8,
752 x_attribute9,
753 x_attribute10,
754 x_attribute11,
755 x_attribute12,
756 x_attribute13,
757 x_attribute14,
758 x_attribute15,
759 x_last_update_date,
760 x_last_updated_by,
761 x_creation_date,
762 x_created_by,
763 x_last_update_login,
764 x_object_version_number
765 );
766 ELSIF x_operation = 'UPDATE'
767 THEN
768 UPDATE_RECORD (x_rt_quota_asgn_id,
769 x_calc_formula_id,
770 x_quota_id,
771 x_start_date,
772 x_end_date,
773 x_rate_schedule_id,
774 x_attribute_category,
775 x_attribute1,
776 x_attribute2,
777 x_attribute3,
778 x_attribute4,
779 x_attribute5,
780 x_attribute6,
781 x_attribute7,
782 x_attribute8,
783 x_attribute9,
784 x_attribute10,
785 x_attribute11,
786 x_attribute12,
787 x_attribute13,
788 x_attribute14,
789 x_attribute15,
790 x_last_update_date,
791 x_last_updated_by,
792 x_creation_date,
793 x_created_by,
794 x_last_update_login,
795 x_object_version_number
796 );
797 ELSIF x_operation = 'DELETE'
798 THEN
799 DELETE_RECORD (x_quota_id, x_calc_formula_id, x_rt_quota_asgn_id);
800 ELSIF x_operation = 'LOCK'
801 THEN
802 LOCK_RECORD (x_rowid, x_rt_quota_asgn_id, x_rate_schedule_id, x_start_date, x_end_date);
803 END IF;
804 END begin_record;
805 END cn_rt_quota_asgns_pkg;