[Home] [Help]
PACKAGE BODY: APPS.PN_VAR_CHG_CAL_PKG
Source
1 package body PN_VAR_CHG_CAL_PKG as
2 -- $Header: PNCHCALB.pls 120.0 2007/10/03 14:24:39 rthumma noship $
3
4 /*===========================================================================+
5 | PROCEDURE COPY_PARENT_CONSTRAINTS
6 |
7 |
8 | DESCRIPTION
9 | Create records in the change calendar PN_VAR_CONSTRAINTS_ALL table from
10 | records in the parent variable rent agreement.
11 |
12 | SCOPE - PUBLIC
13 |
14 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
15 |
16 | ARGUMENTS : IN:
17 | X_VAR_RENT_ID
18 | X_CHG_VAR_RENT_ID
19 |
20 | OUT:
21 |
22 | RETURNS : None
23 |
24 |
25 | MODIFICATION HISTORY
26 |
27 | 13-FEB-2003 Gary Olson o Created
28 +===========================================================================*/
29 procedure copy_parent_constraints (
30 X_VAR_RENT_ID in NUMBER,
31 X_CHG_VAR_RENT_ID in NUMBER
32 ) IS
33
34 l_var_rent_id NUMBER := NULL;
35 l_chg_var_rent_id NUMBER := NULL;
36 l_last_constr_cat_code VARCHAR2(30) := NULL;
37 l_last_type_code VARCHAR2(30) := NULL;
38 l_last_amount NUMBER := 0;
39 l_constr_Num NUMBER := 0;
40
41 cursor c_new_periods is
42 select period_id, start_date, end_date, org_id
43 from pn_var_periods
44 where var_rent_id = l_var_rent_id;
45
46 cursor c_old_periods (p_chg_var_rent_id NUMBER, p_start DATE, p_end DATE) is
47 select distinct period_id
48 from pn_var_periods
49 where var_rent_id = p_chg_var_rent_id
50 and (start_date between p_start and p_end
51 or end_date between p_start and p_end);
52
53
54 cursor c_old_constraints (p_old_periodId NUMBER) is
55 select *
56 from pn_var_constraints
57 where period_id = p_old_periodId
58 ORDER BY constr_cat_code, type_code, amount;
59
60 BEGIN
61
62 l_var_rent_id := X_VAR_RENT_ID;
63 l_chg_var_rent_id := X_CHG_VAR_RENT_ID;
64
65 FOR c1_rec IN c_new_periods LOOP
66
67 l_last_constr_cat_code := NULL;
68 l_last_type_code := NULL;
69 l_last_amount := 0;
70 l_constr_num := 0;
71
72 FOR c2_rec IN c_old_periods(l_chg_var_rent_id, c1_rec.start_date, c1_rec.end_date) LOOP
73
74 FOR c3_rec IN c_old_constraints(c2_rec.period_id) LOOP
75
76 IF c3_rec.constr_cat_code = l_last_constr_cat_code and
77 c3_rec.type_code = l_last_type_code and
78 c3_rec.amount = l_last_amount then
79 NULL;
80 ELSE
81 l_constr_num := l_constr_num + 1;
82
83 INSERT INTO pn_var_constraints_all (
84 constraint_id,
85 constraint_num,
86 last_update_date,
87 last_updated_by,
88 creation_date,
89 created_by,
90 last_update_login,
91 period_id,
92 constr_cat_code,
93 type_code,
94 amount,
95 comments,
96 attribute_category,
97 attribute1,
98 attribute2,
99 attribute3,
100 attribute4,
101 attribute5,
102 attribute6,
103 attribute7,
104 attribute8,
105 attribute9,
106 attribute10,
107 attribute11,
108 attribute12,
109 attribute13,
110 attribute14,
111 attribute15,
112 org_id,
113 constr_template_id,
114 agreement_template_id,
115 constr_default_id
116 ) values (
117 pn_var_constraints_s.nextval,
118 l_constr_num,
119 sysdate,
120 NVL(fnd_profile.value('USER_ID'),0),
121 sysdate,
122 NVL(fnd_profile.value('USER_ID'),0),
123 NVL(fnd_profile.value('USER_ID'),0),
124 c1_rec.period_id,
125 c3_rec.constr_cat_code,
126 c3_rec.type_code,
127 c3_rec.amount,
128 c3_rec.comments,
129 c3_rec.attribute_category,
130 c3_rec.attribute1,
131 c3_rec.attribute2,
132 c3_rec.attribute3,
133 c3_rec.attribute4,
134 c3_rec.attribute5,
135 c3_rec.attribute6,
136 c3_rec.attribute7,
137 c3_rec.attribute8,
138 c3_rec.attribute9,
139 c3_rec.attribute10,
140 c3_rec.attribute11,
141 c3_rec.attribute12,
142 c3_rec.attribute13,
143 c3_rec.attribute14,
144 c3_rec.attribute15,
145 c3_rec.org_id,
146 c3_rec.constr_template_id,
147 c3_rec.agreement_template_id,
148 c3_rec.constr_default_id
149 );
150
151 l_last_constr_cat_code := c3_rec.constr_cat_code;
152 l_last_type_code := c3_rec.type_code;
153 l_last_amount := c3_rec.amount;
154
155 END IF;
156
157 END LOOP;
158
159 END LOOP;
160
161 END LOOP;
162
163
164 END copy_parent_constraints;
165
166 /*===========================================================================+
167 | PROCEDURE COPY_PARENT_LINES
168 |
169 |
170 | DESCRIPTION
171 | Create records in the change calendar PN_VAR_LINES_ALL table from
172 | records in the parent variable rent agreement.
173 |
174 | SCOPE - PUBLIC
175 |
176 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
177 |
178 | ARGUMENTS : IN:
179 | X_VAR_RENT_ID
180 | X_CHG_VAR_RENT_ID
181 |
182 | OUT:
183 |
184 | RETURNS : None
185 |
186 |
187 | MODIFICATION HISTORY
188 |
189 | 14-FEB-2003 Gary Olson o Created
190 +===========================================================================*/
191 procedure copy_parent_lines (
192 X_VAR_RENT_ID in NUMBER,
193 X_CHG_VAR_RENT_ID in NUMBER
194 ) IS
195
196 l_var_rent_id NUMBER := NULL;
197 l_chg_var_rent_id NUMBER := NULL;
198 l_last_sales_type_code VARCHAR2(30) := NULL;
199 l_last_item_cat_code VARCHAR2(30) := NULL;
200 l_line_Num NUMBER := 0;
201 l_lineitemid NUMBER := 0;
202 l_bkptheadid NUMBER := 0;
203 l_bkdt_num NUMBER := 0;
204 l_line_start_date DATE;
205 l_line_end_date DATE;
206
207 cursor c_new_periods is
208 select period_id, start_date, end_date,
209 org_id, proration_factor
210 from pn_var_periods
211 where var_rent_id = l_var_rent_id;
212
213 cursor c_old_periods (p_chg_var_rent_id NUMBER, p_start DATE, p_end DATE) is
214 select distinct period_id
215 from pn_var_periods
216 where var_rent_id = p_chg_var_rent_id
217 and (start_date between p_start and p_end
218 or end_date between p_start and p_end);
219
220 cursor c_old_lines (p_old_periodId NUMBER) is
221 select *
222 from pn_var_lines
223 where period_id = p_old_periodId
224 ORDER BY sales_type_code, item_category_code;
225
226 cursor c_old_bkpt_head (p_old_periodId NUMBER) is
227 select *
228 from pn_var_bkpts_head
229 where period_id = p_old_periodId;
230
231 cursor c_old_bkpt_det (p_bkptheadid NUMBER) IS
232 select *
233 from pn_var_bkpts_det
234 where bkpt_header_id = p_bkptheadid;
235
236 BEGIN
237
238 l_var_rent_id := X_VAR_RENT_ID;
239 l_chg_var_rent_id := X_CHG_VAR_RENT_ID;
240
241 FOR c1_rec IN c_new_periods LOOP
242
243 l_last_sales_type_code := NULL;
244 l_last_item_cat_code := NULL;
245 l_line_num := 0;
246 l_bkdt_num := 0;
247
248 FOR c2_rec IN c_old_periods(l_chg_var_rent_id, c1_rec.start_date, c1_rec.end_date) LOOP
249
250 FOR c3_rec IN c_old_lines(c2_rec.period_id) LOOP
251
252 IF c3_rec.sales_type_code = l_last_sales_type_code and
253 c3_rec.item_category_code = l_last_item_cat_code then
254 NULL;
255 ELSE
256 l_line_num := l_line_num + 1;
257 SELECT pn_var_lines_s.nextval into l_lineitemid from dual;
258
259 INSERT INTO pn_var_lines_all (
260 line_item_id,
261 line_item_num,
262 last_update_date,
263 last_updated_by,
264 creation_date,
265 created_by,
266 last_update_login,
267 period_id,
268 sales_type_code,
269 item_category_code,
270 comments,
271 attribute_category,
272 attribute1,
273 attribute2,
274 attribute3,
275 attribute4,
276 attribute5,
277 attribute6,
278 attribute7,
279 attribute8,
280 attribute9,
281 attribute10,
282 attribute11,
283 attribute12,
284 attribute13,
285 attribute14,
286 attribute15,
287 org_id,
288 line_template_id,
289 agreement_template_id,
290 line_default_id
291 ) values (
292 l_lineitemid,
293 l_line_num,
294 sysdate,
295 NVL(fnd_profile.value('USER_ID'),0),
296 sysdate,
297 NVL(fnd_profile.value('USER_ID'),0),
298 NVL(fnd_profile.value('USER_ID'),0),
299 c1_rec.period_id,
300 c3_rec.sales_type_code,
301 c3_rec.item_category_code,
302 c3_rec.comments,
303 c3_rec.attribute_category,
304 c3_rec.attribute1,
305 c3_rec.attribute2,
306 c3_rec.attribute3,
307 c3_rec.attribute4,
308 c3_rec.attribute5,
309 c3_rec.attribute6,
310 c3_rec.attribute7,
311 c3_rec.attribute8,
312 c3_rec.attribute9,
313 c3_rec.attribute10,
314 c3_rec.attribute11,
315 c3_rec.attribute12,
316 c3_rec.attribute13,
317 c3_rec.attribute14,
318 c3_rec.attribute15,
319 c3_rec.org_id,
320 c3_rec.line_template_id,
321 c3_rec.agreement_template_id,
322 c3_rec.line_default_id
323 );
324
325 l_last_sales_type_code := c3_rec.sales_type_code;
326 l_last_item_cat_code := c3_rec.item_category_code;
327
328
329 FOR c4_rec IN c_old_bkpt_head(c2_rec.period_id) LOOP
330 SELECT pn_var_bkpts_head_s.nextval into l_bkptheadid from dual;
331
332 INSERT INTO pn_var_bkpts_head_all (
333 bkpt_header_id,
334 last_update_date,
335 last_updated_by,
336 creation_date,
337 created_by,
338 last_update_login,
339 line_item_id,
340 period_id,
341 break_type,
342 base_rent_type,
343 natural_break_rate,
344 base_rent,
345 breakpoint_type,
346 attribute_category,
347 attribute1,
348 attribute2,
349 attribute3,
350 attribute4,
351 attribute5,
352 attribute6,
353 attribute7,
354 attribute8,
355 attribute9,
356 attribute10,
357 attribute11,
358 attribute12,
359 attribute13,
360 attribute14,
361 attribute15,
362 org_id
363 ) values (
364 l_bkptheadid,
365 sysdate,
366 NVL(fnd_profile.value('USER_ID'),0),
367 sysdate,
368 NVL(fnd_profile.value('USER_ID'),0),
369 NVL(fnd_profile.value('USER_ID'),0),
370 l_lineitemid,
371 c1_rec.period_id,
372 c4_rec.break_type,
373 c4_rec.base_rent_type,
374 c4_rec.natural_break_rate,
375 c4_rec.base_rent,
376 c4_rec.breakpoint_type,
377 c4_rec.attribute_category,
378 c4_rec.attribute1,
379 c4_rec.attribute2,
380 c4_rec.attribute3,
381 c4_rec.attribute4,
382 c4_rec.attribute5,
383 c4_rec.attribute6,
384 c4_rec.attribute7,
385 c4_rec.attribute8,
386 c4_rec.attribute9,
387 c4_rec.attribute10,
388 c4_rec.attribute11,
389 c4_rec.attribute12,
390 c4_rec.attribute13,
391 c4_rec.attribute14,
392 c4_rec.attribute15,
393 c4_rec.org_id
394 );
395
396 l_bkdt_num := 0;
397
398 FOR c5_rec IN c_old_bkpt_det(c4_rec.bkpt_header_id) LOOP
399
400 l_bkdt_num := l_bkdt_num + 1;
401 IF c1_rec.start_date > NVL(c5_rec.bkpt_start_date, c1_rec.start_date - 1) THEN
402 l_line_start_date := c1_rec.start_date;
403 ELSE
404 l_line_start_date := c5_rec.bkpt_start_date;
405 END IF;
406
407 IF c1_rec.end_date < NVL(c5_rec.bkpt_end_date, c1_rec.end_date + 1) THEN
408 l_line_end_date := c1_rec.end_date;
409 ELSE
410 l_line_end_date := c5_rec.bkpt_end_date;
411 END IF;
412
413 -- need to build routine to calculate volumes based on period and group proration.
414 -- use period volumes as control amounts for calculations.
415
416 INSERT INTO pn_var_bkpts_det_all (
417 bkpt_detail_id,
418 bkpt_detail_num,
419 last_update_date,
420 last_updated_by,
421 creation_date,
422 created_by,
423 last_update_login,
424 bkpt_header_id,
425 bkpt_start_date,
426 bkpt_end_date,
427 period_bkpt_vol_start,
428 period_bkpt_vol_end,
429 group_bkpt_vol_start,
430 group_bkpt_vol_end,
431 bkpt_rate,
432 comments,
433 attribute_category,
434 attribute1,
435 attribute2,
436 attribute3,
437 attribute4,
438 attribute5,
439 attribute6,
440 attribute7,
441 attribute8,
442 attribute9,
443 attribute10,
444 attribute11,
445 attribute12,
446 attribute13,
447 attribute14,
448 attribute15,
449 org_id
450 ) values (
451 pn_var_bkpts_det_s.nextval,
452 l_bkdt_num,
453 sysdate,
454 NVL(fnd_profile.value('USER_ID'),0),
455 sysdate,
456 NVL(fnd_profile.value('USER_ID'),0),
457 NVL(fnd_profile.value('USER_ID'),0),
458 l_bkptheadid,
459 l_line_start_date,
460 l_line_end_date,
461 c5_rec.period_bkpt_vol_start,
462 c5_rec.period_bkpt_vol_end,
463 c5_rec.group_bkpt_vol_start,
464 c5_rec.group_bkpt_vol_end,
465 c5_rec.bkpt_rate,
466 c5_rec.comments,
467 c5_rec.attribute_category,
468 c5_rec.attribute1,
469 c5_rec.attribute2,
470 c5_rec.attribute3,
471 c5_rec.attribute4,
472 c5_rec.attribute5,
473 c5_rec.attribute6,
474 c5_rec.attribute7,
475 c5_rec.attribute8,
476 c5_rec.attribute9,
477 c5_rec.attribute10,
478 c5_rec.attribute11,
479 c5_rec.attribute12,
480 c5_rec.attribute13,
481 c5_rec.attribute14,
482 c5_rec.attribute15,
483 c5_rec.org_id
484 );
485
486 END LOOP;
487
488 END LOOP;
489
490 END IF;
491
492 END LOOP;
493
494 END LOOP;
495
496 END LOOP;
497
498
499 end copy_parent_lines;
500
501 /*===========================================================================+
502 | PROCEDURE COPY_PARENT_VOLHIST
503 |
504 |
505 | DESCRIPTION
506 | Create records in the change calendar PN_VAR_VOL_HIST_ALL table from
507 | records in the parent variable rent agreement VOL HIST TABLE when group dates
508 | are the same for both the parent and the change calendar periods.
509 |
510 | SCOPE - PUBLIC
511 |
512 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
513 |
514 | ARGUMENTS : IN:
515 | X_VAR_RENT_ID
516 | X_CHG_VAR_RENT_ID
517 |
518 | OUT:
519 |
520 | RETURNS : None
521 |
522 |
523 | MODIFICATION HISTORY
524 |
525 | 18-FEB-2003 Gary Olson o Created
526 +===========================================================================*/
527 procedure copy_parent_volhist (
528 X_VAR_RENT_ID in NUMBER,
529 X_CHG_VAR_RENT_ID in NUMBER
530 ) IS
531
532 l_chg_var_rent_id NUMBER := 0;
533 l_var_rent_id NUMBER := 0;
534 l_comm_date DATE;
535 l_vol_num NUMBER := 0;
536 l_null VARCHAR2(10) := NULL;
537 l_start_date DATE;
538 l_end_date DATE;
539 l_actual_amount NUMBER := 0;
540
541 cursor c_start is
542 select commencement_date
543 from pn_var_rents_all
544 where var_rent_id = l_var_rent_id;
545
546 cursor c_new_grplineperiod is
547 select a.grp_date_id,
548 b.line_item_id,
549 c.period_id,
550 c.start_date,
551 c.end_date,
552 a.group_date,
553 a.invoice_date invoicing_date,
554 a.reptg_due_date reporting_date
555 from pn_var_grp_dates_all a,
556 pn_var_lines_all b,
557 pn_var_periods_all c
558 where a.var_rent_id = l_var_rent_id
559 and a.period_id = b.period_id
560 and b.period_id = c.period_id;
561
562 cursor c_old_grplineperiod (p_comm_date DATE, p_start_date DATE, p_end_date DATE) is
563 select d.vol_hist_id vol_hist_id,
564 d.line_item_id line_item_id,
565 d.period_id period_id,
566 d.start_date start_date,
567 d.end_date end_date,
568 d.grp_date_id grp_date_id,
569 d.group_date group_date,
570 d.actual_gl_account_id actual_gl_account_id,
571 d.actual_amount actual_amount,
572 d.daily_actual_amount daily_actual_amount,
573 d.vol_hist_status_code vol_hist_status_code,
574 d.report_type_code report_type_code,
575 d.certified_by certified_by,
576 d.actual_exp_code actual_exp_code,
577 d.for_gl_account_id for_gl_account_id,
578 d.forecasted_amount forecasted_amount,
579 d.forecasted_exp_code forecasted_exp_code,
580 d.variance_exp_code variance_exp_code,
581 d.comments comments,
582 d.attribute_category attribute_category,
583 d.attribute1 attribute1,
584 d.attribute2 attribute2,
585 d.attribute3 attribute3,
586 d.attribute4 attribute4,
587 d.attribute5 attribute5,
588 d.attribute6 attribute6,
589 d.attribute7 attribute7,
590 d.attribute8 attribute8,
591 d.attribute9 attribute9,
592 d.attribute10 attribute10,
593 d.attribute11 attribute11,
594 d.attribute12 attribute12,
595 d.attribute13 attribute13,
596 d.attribute14 attribute14,
597 d.attribute15 attribute15,
598 d.org_id org_id
599 from pn_var_grp_dates_all a,
600 pn_var_lines_all b,
601 pn_var_periods_all c,
602 pn_var_vol_hist_all d
603 where a.var_rent_id = l_chg_var_rent_id
604 and a.period_id = b.period_id
605 and b.period_id = c.period_id
606 and d.period_id = c.period_id
607 and d.line_item_id = b.line_item_id
608 and d.grp_date_id = a.grp_date_id
609 and (d.start_date between p_start_date and p_end_date
610 or d.end_date between p_start_date and p_end_date)
611 and a.grp_start_date >= p_comm_date;
612
613 BEGIN
614
615 l_chg_var_rent_id := X_CHG_VAR_RENT_ID;
616 l_var_rent_id := X_VAR_RENT_ID;
617
618 open c_start;
619 fetch c_start into l_comm_date;
620 close c_start;
621
622 FOR c1_rec IN c_new_grplineperiod LOOP
623 l_vol_num := 0;
624 FOR c2_rec IN c_old_grplineperiod (l_comm_date, c1_rec.start_date, c1_rec.end_date) LOOP
625 l_vol_num := l_vol_num + 1;
626 IF c1_rec.start_date > c2_rec.start_date THEN
627 l_start_date := c1_rec.start_date;
628 ELSE
629 l_start_date := c2_rec.start_date;
630 END IF;
631
632 IF c1_rec.end_date < c2_rec.end_date THEN
633 l_end_date := c1_rec.end_date;
634 ELSE
635 l_end_date := c2_rec.end_date;
636 END IF;
637
638 /**
639 l_actual_amount := ROUND(c2_rec.daily_actual_amount *
640 to_number(to_char(l_end_date,'YYMMDD'))-
641 to_number(to_char(l_start_date,'YYMMDD')),2);
642 **/
643
644 INSERT into pn_var_vol_hist_all (
645 vol_hist_id,
646 vol_hist_num,
647 last_update_date,
648 last_updated_by,
649 creation_date,
650 created_by,
651 last_update_login,
652 line_item_id,
653 period_id,
654 start_date,
655 end_date,
656 grp_date_id,
657 group_date,
658 reporting_date,
659 due_date,
660 invoicing_date,
661 actual_gl_account_id,
662 actual_amount,
663 daily_actual_amount,
664 vol_hist_status_code,
665 report_type_code,
666 certified_by,
667 actual_exp_code,
668 for_gl_account_id,
669 forecasted_amount,
670 forecasted_exp_code,
671 variance_exp_code,
672 comments,
673 attribute_category,
674 attribute1,
675 attribute2,
676 attribute3,
677 attribute4,
678 attribute5,
679 attribute6,
680 attribute7,
681 attribute8,
682 attribute9,
683 attribute10,
684 attribute11,
685 attribute12,
686 attribute13,
687 attribute14,
688 attribute15,
689 org_id
690 ) values (
691 pn_var_vol_hist_s.nextval,
692 l_vol_num,
693 sysdate,
694 NVL(fnd_profile.value('USER_ID'),0),
695 sysdate,
696 NVL(fnd_profile.value('USER_ID'),0),
697 NVL(fnd_profile.value('USER_ID'),0),
698 c1_rec.line_item_id,
699 c1_rec.period_id,
700 l_start_date,
701 l_end_date,
702 c1_rec.grp_date_id,
703 c1_rec.group_date,
704 c1_rec.reporting_date,
705 l_null,
706 c1_rec.invoicing_date,
707 l_null,
708 l_actual_amount,
709 c2_rec.daily_actual_amount,
710 c2_rec.vol_hist_status_code,
711 c2_rec.report_type_code,
712 l_null,
713 l_null,
714 l_null,
715 l_null,
716 l_null,
717 l_null,
718 c2_rec.comments,
719 c2_rec.attribute_category,
720 c2_rec.attribute1,
721 c2_rec.attribute2,
722 c2_rec.attribute3,
723 c2_rec.attribute4,
724 c2_rec.attribute5,
725 c2_rec.attribute6,
726 c2_rec.attribute7,
727 c2_rec.attribute8,
728 c2_rec.attribute9,
729 c2_rec.attribute10,
730 c2_rec.attribute11,
731 c2_rec.attribute12,
732 c2_rec.attribute13,
733 c2_rec.attribute14,
734 c2_rec.attribute15,
735 c2_rec.org_id
736 );
737
738 END LOOP;
739
740 END LOOP;
741
742
743 END copy_parent_volhist;
744
745 /*===========================================================================+
746 | PROCEDURE POPULATE_TRANSACTIONS
747 |
748 |
749 | DESCRIPTION
750 | Populate the variable rent transactions table when periods,
751 | group dates and breakpoint details have been created.
752 |
753 | SCOPE - PUBLIC
754 |
755 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
756 |
757 | ARGUMENTS : IN:
758 | X_VAR_RENT_ID
759 |
760 | OUT:
761 |
762 | RETURNS : None
763 |
764 |
765 | MODIFICATION HISTORY
766 |
767 | 10-MAR-2003 Gary Olson o Created
768 +===========================================================================*/
769 PROCEDURE populate_transactions (p_var_rent_id IN NUMBER,
770 p_period_id IN NUMBER,
771 p_line_item_id IN NUMBER)
772 IS
773
774 CURSOR csr_get_groups (ip_var_rent_id NUMBER, ip_period_id NUMBER) IS
775 SELECT a.var_rent_id,
776 a.period_id,
777 a.grp_date_id,
778 a.grp_start_date,
779 a.grp_end_date,
780 a.group_date,
781 (a.grp_end_date - a.grp_start_date)+1 no_of_group_days,
782 a.invoice_date
783 ,a.proration_factor
784 ,b.start_date
785 ,b.end_date
786 ,c.commencement_date
787 ,c.proration_rule --Chris.T. 10FEB2004
788 FROM pn_var_grp_dates_all a
789 ,pn_var_periods_all b
790 ,pn_var_rents_all c
791 WHERE c.var_rent_id = ip_var_rent_id
792 AND c.var_rent_id = a.var_rent_id
793 AND a.period_id = b.period_id
794 AND a.period_id = NVL(ip_period_id,a.period_id)
795 ORDER by grp_start_date;
796
797 CURSOR csr_get_bkpts (ip_period_id NUMBER) IS
798 SELECT bkpt.bkpt_detail_id bkpt_detail_id,
799 bkpt.bkpt_start_date bkpt_start_date,
800 bkpt.bkpt_end_date bkpt_end_date,
801 bkpt.group_bkpt_vol_start group_bkpt_vol_start,
802 bkpt.group_bkpt_vol_end group_bkpt_vol_end,
803 bkpt.period_bkpt_vol_start period_bkpt_vol_start, --Chris.T. 10FEB2004
804 bkpt.period_bkpt_vol_end period_bkpt_vol_end, --Chris.T. 10FEB2004
805 bkpt.bkpt_rate bkpt_rate,
806 bkpt.bkpt_header_id bkpt_header_id,
807 head.line_item_id line_item_id
808 FROM pn_var_bkpts_head_all head,
809 pn_var_bkpts_det_all bkpt
810 WHERE head.bkpt_header_id = bkpt.bkpt_header_id
811 AND head.period_id = ip_period_id
812 AND head.line_item_id = NVL(p_line_item_id,line_item_id)
813 ORDER by head.line_item_id,bkpt.bkpt_start_date, bkpt.bkpt_rate, bkpt.group_bkpt_vol_start;
814
815 /*CURSOR csr_trans_exists (p_grp_date_id NUMBER,
816 p_bkpt_detail_id NUMBER) IS
817 SELECT 1
818 FROM pn_var_transactions_all
819 WHERE grp_date_id = p_grp_date_id
820 AND bkpt_detail_id = p_bkpt_detail_id;
821
822 CURSOR csr_find_resets (ip_var_rent_id NUMBER) IS
823 SELECT distinct a.period_id
824 ,a.group_date
825 ,a.line_item_id
826 ,a.bkpt_start_date
827 ,b.item_category_code
828 ,b.sales_type_code
829 FROM pn_var_transactions_all a
830 ,pn_var_lines_all b
831 WHERE a.var_rent_id = ip_var_rent_id
832 AND a.line_item_id = NVL(p_line_item_id,b.line_item_id)
833 AND a.line_item_id = b.line_item_id --24SEP03 Chris.T.
834 ORDER BY b.item_category_code ,b.sales_type_code ,a.group_date; -- 11DEC03 Chris.T.
835
836 CURSOR csr_reset_check( ip_var_rent_id NUMBER, p_start_date DATE) IS
837 SELECT 1
838 FROM pn_var_transactions_all a
839 WHERE a.var_rent_id = p_var_rent_id
840 AND a.bkpt_end_date = p_start_date - 1
841 AND a.bkpt_rate not in (select b.bkpt_rate
842 FROM pn_var_transactions_all b
843 where b.var_rent_id = ip_var_rent_id
844 and b.bkpt_start_date = p_start_date);
845
846 CURSOR l_row_exists_cur (ip_var_rent_id NUMBER
847 ,ip_grp_date_id NUMBER
848 ,ip_line_item_id NUMBER
849 ,ip_bkpt_detail_id NUMBER
850 ,ip_bkpt_start_date DATE
851 ,ip_bkpt_end_date DATE) IS
852
853 SELECT 'x'
854 FROM DUAL
855 WHERE EXISTS( SELECT var_rent_id
856 FROM pn_var_transactions_all
857 WHERE var_rent_id = ip_var_rent_id
858 AND line_item_id = ip_line_item_id
859 AND bkpt_detail_id = ip_bkpt_detail_id
860 AND grp_date_id = ip_grp_date_id);
861 --AND bkpt_start_date = ip_bkpt_start_date
862 --AND bkpt_End_date = ip_bkpt_end_date);*/
863
864 CURSOR pn_var_grp_dt (p_var_rent_id NUMBER) IS
865 SELECT min(grp_start_date) fy_start_date
866 , ADD_MONTHS(min(grp_start_date), 12) - 1 fy_end_date
867 , ADD_MONTHS(max(grp_end_date), -12) + 1 ly_start_date
868 , max(grp_end_date) ly_end_date
869 FROM pn_var_grp_dates_all
870 WHERE var_rent_id = p_var_rent_id;
871 /* Srini 14JUL2004
872 SELECT min(grp_start_date)
873 , min(grp_start_date)+364
874 , max(grp_end_date) - 364
875 , max(grp_end_date)
876 */
877
878 /*CURSOR update_365_days_bkpt(p_ly_365_end_dt DATE)
879 IS
880 SELECT *
881 FROM pn_var_transactions_all
882 WHERE var_rent_id = p_var_rent_id
883 AND bkpt_end_date = p_ly_365_end_dt
884 AND NVL(pr_grp_blended_vol_end,0) <> 0;
885
886 CURSOR update_365_days_bkpt_strat(p_ly_365_end_dt DATE)
887 IS
888 SELECT *
889 FROM pn_var_transactions_all
890 WHERE var_rent_id = p_var_rent_id
891 AND bkpt_end_date = p_ly_365_end_dt
892 AND NVL(pr_grp_blended_vol_end,0) = 0;*/
893
894
895 l_found NUMBER := 0;
896 l_row_exists VARCHAR2(1);
897 l_reset_found NUMBER := 0;
898 l_calc_days NUMBER;
899 l_proration_factor NUMBER;
900 l_prorate_start NUMBER; --(12,2); Chris.T. 09MAR2004
901 l_prorate_end NUMBER; --(12,2); Chris.T. 09MAR2004
902 l_pr_prorate_start NUMBER; --(12,2); Chris.T. 09MAR2004
903 l_pr_prorate_end NUMBER; --(12,2); Chris.T. 09MAR2004
904 l_resets_flag VARCHAR2(1) := 'N';
905 l_reset_flag VARCHAR2(1) := 'N';
906 l_reset_group_id NUMBER := 0;
907 l_reset_group_id_cnt NUMBER := 0;
908 l_pro_reset_group_id NUMBER := NULL;
909 l_pro_reset_group_id_cnt NUMBER := 0;
910 g_pro_reset_group_id_cnt NUMBER := 0;
911 l_proration_rule VARCHAR2(30);
912 l_period_start DATE;
913 l_period_end DATE;
914 l_start_date DATE;
915 l_end_date DATE;
916 l_commencement_date DATE;
917 l_filename VARCHAR2(50) := 'POPULATE-'||p_var_rent_id||'-'||to_char(sysdate,'MMDDYYHHMMSS');
918 l_pathname VARCHAR2(60) := '/u04/app/crp4comn/admin/plsql';
919
920 l_fy_start_date DATE;
921 l_fy_end_date DATE;
922 l_ly_start_date DATE;
923 l_ly_end_date DATE;
924 l_fy_factor NUMBER;
925 l_ly_factor NUMBER;
926 l_factor NUMBER;
927 g_reset_complete VARCHAR2(1) := NULL;
928 l_last_complete_period_id NUMBER;
929 l_ly_365_start_date DATE;
930 l_fy_365_end_date DATE;
931 l_vr_term_dt DATE;
932 l_cumulative_vol PN_VAR_RENTS_ALL.CUMULATIVE_VOL%TYPE;
933 v_reset_grp_id NUMBER;
934 l_bkpt_days1 NUMBER;
935 l_bkpt_days2 NUMBER;
936 l_cnt NUMBER;
937 v_bkpt_start_date DATE;
938 l_ly_365_end_dt DATE;
939 l_ly_365_start_dt DATE;
940 l_invg_freq VARCHAR2(20);
941 l_pr_start NUMBER;
942 l_pr_end NUMBER;
943
944 BEGIN
945
946 --pnp_debug_pkg.enable_file_debug(l_pathname,l_filename);
947 PNP_DEBUG_PKG.log('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
948 PNP_DEBUG_PKG.log(' Parameters :');
949 pNP_DEBUG_PKG.log(' p_var_rent_id = '|| p_var_rent_id);
950 pNP_DEBUG_PKG.log(' p_period_id = '|| p_period_id);
951 pnp_debug_pkg.log(' Call pn_var_rent_pkg.get_proration_rule');
952 PNP_DEBUG_PKG.DEBUG('PN_VAR_CHG_CAL_PKG.populate_transactions (+)');
953 PNP_DEBUG_PKG.DEBUG('Parameters :');
954 pNP_DEBUG_PKG.DEBUG('p_var_rent_id = '|| p_var_rent_id);
955 pNP_DEBUG_PKG.DEBUG('p_period_id = '|| p_period_id);
956 pnp_debug_pkg.debug('Call pn_var_rent_pkg.get_proration_rule');
957
958 l_proration_rule := pn_var_rent_pkg.get_proration_rule(p_var_rent_id =>p_var_rent_id);
959 pnp_debug_pkg.log(' Proration Rule = '||l_proration_rule);
960 pnp_debug_pkg.debug('Proration Rule = '||l_proration_rule);
961
962 SELECT termination_date, cumulative_vol
963 INTO l_vr_term_dt, l_cumulative_vol
964 FROM pn_var_rents_all
965 WHERE var_rent_id = p_var_rent_id;
966 pnp_debug_pkg.log(' Variable Rent Termination Date:'||l_vr_term_dt||' Cumulative Volume:'||l_cumulative_vol);
967 pnp_debug_pkg.debug(' Variable Rent Termination Date:'||l_vr_term_dt);
968
969 IF l_proration_rule in ('FY','LY','FLY') THEN
970 OPEN pn_var_grp_dt(p_var_rent_id);
971 FETCH pn_var_grp_dt INTO
972 l_fy_start_date
973 ,l_fy_end_date
974 ,l_ly_start_date
975 ,l_ly_end_date;
976 CLOSE pn_var_grp_dt;
977
978 SELECT MAX(end_date), ADD_MONTHS(MAX(end_date), -12)+1
979 INTO l_ly_365_end_dt, l_ly_365_start_dt
980 FROM pn_var_periods_all
981 WHERE var_rent_id = p_var_rent_id;
982
983 --pnp_debug_pkg.log(' l_fy_start_date = '|| l_fy_start_date);
984 --pnp_debug_pkg.log(' l_fy_end_date = '|| l_fy_end_date);
985 --pnp_debug_pkg.log(' l_ly_start_date = '|| l_ly_start_date);
986 --pnp_debug_pkg.log(' l_ly_end_date = '|| l_ly_end_date);
987 pnp_debug_pkg.debug('l_fy_start_date = '|| l_fy_start_date);
988 pnp_debug_pkg.debug('l_fy_end_date = '|| l_fy_end_date);
989 pnp_debug_pkg.debug('l_ly_start_date = '|| l_ly_start_date);
990 pnp_debug_pkg.debug('l_ly_end_date = '|| l_ly_end_date);
991 /*DBMS_OUTPUT.PUT_LINE(' l_fy_start_date = '|| l_fy_start_date);
992 DBMS_OUTPUT.PUT_LINE(' l_fy_end_date = '|| l_fy_end_date);
993 DBMS_OUTPUT.PUT_LINE(' l_ly_start_date = '|| l_ly_start_date);
994 DBMS_OUTPUT.PUT_LINE(' l_ly_end_date = '|| l_ly_end_date);*/
995 END IF;
996 /*dbms_output.put_line('Step - 1'); */
997 FOR c_grp IN csr_get_groups (p_var_rent_id,p_period_id) LOOP
998 l_commencement_date := c_grp.commencement_date;
999 --PNP_DEBUG_PKG.log(' Calling csr_get_groups');
1000 pnp_debug_pkg.debug('Cursor csr_get_groups');
1001 l_period_start := c_grp.start_date;
1002 l_period_end := c_grp.end_date;
1003 --pnp_debug_pkg.log(' Period start = '|| l_period_start);
1004 --pnp_debug_pkg.log(' Period end = '|| l_period_end);
1005 pnp_debug_pkg.debug('Period start = '|| l_period_start);
1006 pnp_debug_pkg.debug('Period end = '|| l_period_end);
1007
1008 PNP_DEBUG_PKG.DEBUG('Opening cursor csr_get_bkpts ');
1009 /* dbms_output.put_line('Step - 2'); */
1010 FOR c_bkpt IN csr_get_bkpts (c_grp.period_id) LOOP
1011 l_found := 0;
1012
1013 --pnp_debug_pkg.log(' GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1014 --pnp_debug_pkg.log(' GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1015 --pnp_debug_pkg.log(' BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1016 --pnp_debug_pkg.log(' BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1017 pnp_debug_pkg.debug('GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1018 pnp_debug_pkg.debug('GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1019 pnp_debug_pkg.debug('BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1020 pnp_debug_pkg.debug('BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY'));
1021 PNP_DEBUG_PKG.DEBUG('Opening cursor csr_trans_exists ');
1022 l_resets_flag := 'N';
1023
1024 pnp_debug_pkg.debug('l_found = ' || l_found);
1025
1026 /* dbms_output.put_line('Step - 3'); */
1027 IF c_grp.grp_start_date between c_bkpt.bkpt_start_date and c_bkpt.bkpt_end_date
1028 OR c_grp.grp_end_date between c_bkpt.bkpt_start_date and c_bkpt.bkpt_end_date
1029 OR c_bkpt.bkpt_start_date between c_grp.grp_start_date and c_grp.grp_end_date
1030 OR c_bkpt.bkpt_end_date between c_grp.grp_start_date and c_grp.grp_end_date THEN
1031
1032 --pnp_debug_pkg.log(' Group Start Date OR End Date Between BKPT Start date or End Date');
1033 pnp_debug_pkg.debug('Group Start Date OR End Date Between BKPT Start date or End Date');
1034
1035 IF c_grp.grp_start_date >= c_bkpt.bkpt_start_date THEN
1036 l_start_date := c_grp.grp_start_date;
1037 ELSE
1038 l_start_date := c_bkpt.bkpt_start_date;
1039 END IF;
1040 IF c_grp.grp_end_date <= c_bkpt.bkpt_end_date THEN
1041 l_end_date := c_grp.grp_end_date;
1042 ELSE
1043 l_end_date := c_bkpt.bkpt_end_date;
1044 END IF;
1045
1046 l_calc_days := (l_end_date - l_start_date)+1;
1047
1048 IF l_proration_rule = 'NP' THEN
1049 --IF l_proration_rule IN ('NP', 'LY', 'FLY') THEN
1050 l_proration_factor := 1;
1051 ELSE
1052 l_proration_factor := l_calc_days/c_grp.no_of_group_days;
1053 END IF;
1054 /*DBMS_OUTPUT.PUT_LINE(' Calculate Days = '||to_char(l_calc_days));
1055 DBMS_OUTPUT.PUT_LINE(' Proration Factor = '||to_char(l_proration_factor));
1056 DBMS_OUTPUT.PUT_LINE(' GRP Start Date '||to_char(c_grp.grp_start_date ,'DD-MON-YYYY'));
1057 DBMS_OUTPUT.PUT_LINE(' GRP End Date '||to_char(c_grp.grp_end_date ,'DD-MON-YYYY'));
1058 DBMS_OUTPUT.PUT_LINE(' BKPT Start Date '||to_char(c_bkpt.bkpt_start_date ,'DD-MON-YYYY'));
1059 DBMS_OUTPUT.PUT_LINE(' BKPT End Date '||to_char(c_bkpt.bkpt_end_date ,'DD-MON-YYYY')); */
1060
1061 IF l_proration_rule = 'NP' THEN
1062 l_prorate_start := c_bkpt.period_bkpt_vol_start;
1063 ELSE
1064 l_prorate_start := (c_bkpt.group_bkpt_vol_start*c_grp.proration_factor)*l_proration_factor;
1065 END IF;
1066
1067 IF c_bkpt.group_bkpt_vol_end > 0 THEN
1068 IF l_proration_rule = 'NP' THEN
1069 l_prorate_end := c_bkpt.period_bkpt_vol_end;
1070 ELSE
1071 l_prorate_end := (c_bkpt.group_bkpt_vol_end*c_grp.proration_factor)*l_proration_factor;
1072 END IF;
1073 ELSE
1074 l_prorate_end := 0;
1075 END IF;
1076 /* DBMS_OUTPUT.PUT_LINE(' l_prorate_start = '||l_prorate_start);
1077 DBMS_OUTPUT.PUT_LINE(' l_prorate_end = '||l_prorate_end); */
1078
1079 pnp_debug_pkg.debug('Prorated GRP Vol Start '||to_char(l_prorate_start));
1080 pnp_debug_pkg.debug('Prorated GRP Vol End '||to_char(l_prorate_end));
1081 PNP_DEBUG_PKG.DEBUG('l_start_date = '|| l_start_date);
1082 PNP_DEBUG_PKG.DEBUG('l_end_date = '|| l_end_date);
1083 PNP_DEBUG_PKG.DEBUG('Calculate Days = '||to_char(l_calc_days));
1084 PNP_DEBUG_PKG.DEBUG('no of_group_Days = '||to_char(c_grp.no_of_group_days));
1085 PNP_DEBUG_PKG.DEBUG('l_proration_factor = '||to_char(l_proration_factor));
1086 PNP_DEBUG_PKG.DEBUG('c_grp.proration_factor = '||to_char(c_grp.proration_factor));
1087 PNP_DEBUG_PKG.DEBUG('GRP BKPT 1 = '||to_char(c_bkpt.group_bkpt_vol_start));
1088 PNP_DEBUG_PKG.DEBUG('GRP BKPT 2 = '||(c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor));
1089 PNP_DEBUG_PKG.DEBUG('GRP BKPT 3 = '||((c_bkpt.group_bkpt_vol_start* c_grp.proration_Factor)* l_proration_Factor));
1090
1091 PNP_DEBUG_PKG.DEBUG('insert pn_var_Transactions');
1092 pnp_debug_pkg.debug('l_startdate = '|| l_start_date);
1093 pnp_debug_pkg.debug('l_enddae = '|| l_end_date);
1094 l_fy_factor := 1;
1095 l_ly_factor := 1;
1096 l_factor := l_proration_factor;
1097 l_pr_prorate_start := l_prorate_start ;
1098 l_pr_prorate_end := l_prorate_end ;
1099 pnp_debug_pkg.debug('l_proration_rule='|| l_proration_rule);
1100 IF l_proration_rule in ('FY','LY','FLY') THEN
1101
1102 IF l_fy_end_date BETWEEN l_start_date and l_end_date THEN
1103 IF l_proration_rule = 'LY' AND l_fy_end_date > l_ly_start_date THEN
1104 l_fy_factor := 1;
1105 ELSE
1106 l_fy_factor := ((l_fy_end_date - l_start_date)+1)/((l_end_Date-l_start_date)+1);
1107 l_pr_prorate_start := l_pr_prorate_start * l_fy_factor;
1108 l_pr_prorate_end := l_pr_prorate_end * l_fy_factor;
1109 pnp_debug_pkg.debug('l_fy_factor='|| l_fy_factor);
1110 l_factor := l_fy_factor;
1111 /*DBMS_OUTPUT.PUT_LINE('********* l_fy_factor ********* : '||l_fy_factor); */
1112 END IF;
1113 END IF;
1114
1115 IF l_ly_start_date BETWEEN l_start_date and l_end_date THEN
1116 IF l_proration_rule = 'FY' AND l_ly_start_date < l_fy_end_date THEN
1117 l_ly_factor := 1;
1118 ELSE
1119 l_ly_factor := ((l_end_date - l_ly_start_date)+1)/((l_end_date-l_start_date)+1);
1120 l_pr_prorate_start := l_pr_prorate_start * l_ly_factor;
1121 l_pr_prorate_end := l_pr_prorate_end * l_ly_factor;
1122 pnp_debug_pkg.debug('l_ly_factor='|| l_ly_factor);
1123 l_factor := l_ly_factor;
1124 /* DBMS_OUTPUT.PUT_LINE('********* l_ly_factor ********* : '||l_ly_factor); */
1125 END IF;
1126 END IF;
1127
1128 END IF;
1129
1130 l_row_exists := NULL;
1131 /* dbms_output.put_line('Step - 4'); */
1132 /*OPEN l_row_exists_cur ( p_var_rent_id
1133 ,c_grp.grp_date_id
1134 ,c_bkpt.line_item_id
1135 ,c_bkpt.bkpt_detail_id
1136 ,l_start_date
1137 ,l_end_date);
1138 FETCH l_row_exists_cur into l_row_exists;
1139 CLOSE l_row_exists_cur;*/
1140
1141 --PNP_DEBUG_PKG.log(' l_row_exists = '||l_row_exists);
1142 PNP_DEBUG_PKG.DEBUG('l_row_exists = '||l_row_exists);
1143 pnp_debug_pkg.debug('l_pr_prorate_start = ' || l_pr_prorate_start);
1144 pnp_debug_pkg.debug('l_pr_prorate_end = ' || l_pr_prorate_end);
1145 /* DBMS_OUTPUT.PUT_LINE('p_var_rent_id = '||p_var_rent_id);
1146 DBMS_OUTPUT.PUT_LINE('c_grp.grp_date_id = '||c_grp.grp_date_id);
1147 DBMS_OUTPUT.PUT_LINE('c_bkpt.line_item_id = '||c_bkpt.line_item_id);
1148 DBMS_OUTPUT.PUT_LINE('c_bkpt.bkpt_detail_id = '||c_bkpt.bkpt_detail_id);
1149 DBMS_OUTPUT.PUT_LINE('l_start_date = '||l_start_date);
1150 DBMS_OUTPUT.PUT_LINE('l_end_date = '||l_end_date);
1151 DBMS_OUTPUT.PUT_LINE('l_row_exists = '||l_row_exists);
1152 DBMS_OUTPUT.PUT_LINE('l_proration_factor = '||l_proration_factor);
1153 DBMS_OUTPUT.PUT_LINE('l_proration_rule = '||l_proration_rule);*/
1154 IF l_row_exists IS NULL THEN
1155 /* dbms_output.put_line('Step - 5 - Insert'); */
1156 PNP_DEBUG_PKG.DEBUG('inserting into pn_var_Transactions');
1157 pnp_debug_pkg.debug('l_factor = ' || l_factor);
1158 /*INSERT INTO pn_var_transactions_all (
1159 transaction_id
1160 ,grp_date_id
1161 ,bkpt_detail_id
1162 ,var_rent_id
1163 ,line_item_id
1164 ,period_id
1165 ,period_start_date
1166 ,period_end_date
1167 ,group_date
1168 ,invoice_date
1169 ,bkpt_start_date
1170 ,bkpt_end_date
1171 ,no_of_group_days
1172 ,no_of_bkpt_days
1173 ,prorated_grp_vol_start
1174 ,prorated_grp_vol_end
1175 ,pr_grp_blended_vol_start
1176 ,pr_grp_blended_vol_end
1177 ,bkpt_rate
1178 ,reset_group_id
1179 ,proration_reset_group_id
1180 ,proration_rule_factor
1181 ,last_update_date
1182 ,last_updated_by
1183 ,creation_date
1184 ,created_by
1185 ,last_update_login
1186 ,org_id
1187 )values(
1188 pn_var_transactions_s.nextval
1189 ,c_grp.grp_date_id
1190 ,c_bkpt.bkpt_detail_id
1191 ,p_VAR_RENT_ID
1192 ,c_bkpt.line_item_id
1193 ,c_grp.period_id
1194 ,l_period_start
1195 ,l_period_end
1196 ,c_grp.group_date
1197 ,c_grp.invoice_date
1198 ,l_start_date
1199 ,l_end_date
1200 ,c_grp.no_of_group_days
1201 ,l_calc_days
1202 ,l_prorate_start
1203 ,l_prorate_end
1204 ,l_pr_prorate_start
1205 ,l_pr_prorate_end
1206 ,c_bkpt.bkpt_rate
1207 ,l_reset_group_id
1208 ,l_pro_reset_group_id
1209 ,l_factor
1210 ,sysdate
1211 ,NVL(fnd_profile.value('USER_ID'),0)
1212 ,sysdate
1213 ,NVL(fnd_profile.value('USER_ID'),0)
1214 ,NVL(fnd_profile.value('USER_ID'),0)
1215 ,to_number(decode(substr(userenv('CLIENT_INFO'),1,1),' ',null,substr(userenv('CLIENT_INFO'),1,10)))
1216 );*/
1217 PNP_DEBUG_PKG.DEBUG('Rows Inserted='||to_char(sql%rowcount));
1218
1219 ELSE
1220 /*dbms_output.put_line('Step - 5 - update'); */
1221 --PNP_DEBUG_PKG.log(' update pn_var_Transactions');
1222 --PNP_DEBUG_PKG.log(' l_factor ='|| l_factor);
1223 PNP_DEBUG_PKG.DEBUG('update pn_var_Transactions');
1224 PNP_DEBUG_PKG.DEBUG('l_factor ='|| l_factor);
1225 /*UPDATE pn_var_transactions_all
1226 SET no_of_group_days =c_grp.no_of_group_days
1227 ,no_of_bkpt_days =l_calc_days
1228 ,prorated_grp_vol_start =l_prorate_start
1229 ,prorated_grp_vol_end =l_prorate_end
1230 ,pr_grp_blended_vol_start =l_pr_prorate_start
1231 ,pr_grp_blended_vol_end =l_pr_prorate_end
1232 ,proration_rule_factor = l_factor
1233 ,bkpt_rate = c_bkpt.bkpt_rate
1234 ,last_update_date =sysdate
1235 ,last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
1236 ,last_update_login = NVL(fnd_profile.value('USER_ID'),0 )
1237 WHERE var_rent_id = p_var_rent_id
1238 AND grp_date_id = c_grp.grp_date_id
1239 AND line_item_id = c_bkpt.line_item_id
1240 AND bkpt_detail_id = c_bkpt.bkpt_detail_id
1241 AND bkpt_start_date = l_start_date
1242 AND bkpt_end_date = l_end_date;*/
1243 /*DBMS_OUTPUT.PUT_LINE(' Step1 l_pr_prorate_start='||l_pr_prorate_start);
1244 DBMS_OUTPUT.PUT_LINE(' Step1 Rows Updated='||to_char(sql%rowcount));*/
1245 END IF;
1246
1247 END IF;
1248 END LOOP;
1249
1250 END LOOP;
1251
1252 -------------------------
1253 -- Get Proration Rule
1254 -------------------------
1255 pnp_debug_pkg.debug('Call pn_var_rent_pkg.get_proration_rule');
1256 l_proration_rule := pn_var_rent_pkg.get_proration_rule(
1257 p_var_rent_id =>p_var_rent_id);
1258 pnp_debug_pkg.debug('Proration Rule = '||l_proration_rule);
1259
1260 ------------------------
1261 -- Initialize - reset_grp_id_cnt and proration_reset_grp_id_cnt
1262 ------------------------
1263 -- 11-AUG-03 Chris.T. - Proration Rule Specific - End --
1264 pnp_debug_pkg.debug('Determine_resets');
1265 l_reset_group_id_cnt := 0; -- 24-JUL-03 Chris T --
1266 l_pro_reset_group_id_cnt := NULL; -- 11-AUG-03 Chris.T --
1267
1268 ---------------------------------------
1269 --- IF pro-ration rule is LY or FLY, then get the
1270 -- last complete period id and the start of the 365 days
1271 -- We will then trip the proration_reset_group_id at that
1272 -- point so that the ytdsales and breakpoint will start
1273 -- from the 365 calendar start.
1274 --------------------------------------
1275
1276 IF l_proration_rule IN ('FY','LY','FLY') THEN --Chris.T Added FY 11DEC03
1277
1278 l_last_complete_period_id := get_last_complete_period_id ( p_var_rent_id => p_var_rent_id) ;
1279 l_ly_365_start_date := get_ly_365_start_date ( p_var_rent_id => p_var_rent_id);
1280 l_fy_365_end_Date := get_fy_365_end_date ( p_var_rent_id => p_var_rent_id);
1281
1282 --dbms_output.put_line('l_last_complete_period = '|| l_last_complete_period_id);
1283 --dbms_output.put_line('l_ly_365_start_date = '|| l_ly_365_start_date);
1284
1285 --Srini 11AUG2004 if last year is not a complete year then the
1286 -- proration_reset_group_id is not set properly
1287 IF l_last_complete_period_id IS NULL THEN
1288 IF l_proration_rule IN ('LY', 'FLY') THEN
1289 BEGIN
1290 SELECT MAX(period_id)
1291 INTO l_last_complete_period_id
1292 FROM pn_var_periods_all
1293 WHERE var_rent_id = p_var_rent_id
1294 AND l_ly_365_start_date BETWEEN start_date AND end_date;
1295 EXCEPTION
1296 WHEN OTHERS THEN
1297 l_last_complete_period_id := NULL;
1298 END;
1299 END IF;
1300 END IF;
1301
1302 END IF;
1303
1304 /*FOR c_flag IN csr_find_resets (p_var_rent_id) LOOP
1305
1306 --pnp_debug_pkg.log(' Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1307 --pnp_debug_pkg.log(' Period ID ='||to_char(c_flag.period_id));
1308 --pnp_debug_pkg.log(' Line Item ID ='||to_char(c_flag.line_item_id));
1309 --pnp_debug_pkg.log(' Item Category Code ='||c_flag.item_category_code);
1310 --pnp_debug_pkg.log(' Sales Type Code ='||c_flag.sales_type_code);
1311 --pnp_debug_pkg.log(' Group Date ='||c_flag.group_date);
1312 pnp_debug_pkg.debug('Calling determine reset flag for Start Date = '||to_char(c_flag.bkpt_start_date,'DD-MON-YY'));
1313 pnp_debug_pkg.debug('Period ID ='||to_char(c_flag.period_id));
1314 pnp_debug_pkg.debug('Line Item ID ='||to_char(c_flag.line_item_id));
1315 pnp_debug_pkg.debug('Item Category Code ='||c_flag.item_category_code);
1316 pnp_debug_pkg.debug('Sales Type Code ='||c_flag.sales_type_code);
1317 pnp_debug_pkg.debug('Group Date ='||c_flag.group_date);
1318 determine_reset_flag (p_var_rent_id => p_var_rent_id,
1319 p_period_id => c_flag.period_id,
1320 p_item_category_code => c_flag.item_category_code,
1321 p_sales_type_code => c_flag.sales_type_code,
1322 p_start_date => c_flag.bkpt_start_date,
1323 x_reset_flag => l_reset_flag);
1324 pnp_debug_pkg.debug('Update pn_var_Transactions with reset flag= '||l_reset_flag);
1325 -- 24-JUL-03 Chris T - Start --
1326 IF l_reset_flag = 'Y' THEN
1327 l_reset_group_id_cnt := l_reset_group_id_cnt + 1;
1328 --pnp_debug_pkg.log(' Reset Group ID Count = '||to_char(l_reset_group_id_cnt));
1329 pnp_debug_pkg.debug('Reset Group ID Count = '||to_char(l_reset_group_id_cnt));
1330 END IF;
1331
1332 /*UPDATE pn_var_transactions_all
1333 SET reset_group_id = l_reset_group_id_cnt --24-JUL-03 Chris T--
1334 WHERE var_rent_id = p_VAR_RENT_ID
1335 AND period_id = c_flag.period_id
1336 AND line_item_id = c_flag.line_item_id
1337 AND bkpt_start_date = c_flag.bkpt_start_date;
1338 -- 24-JUL-03 Chris T - End --
1339
1340 -- 11-AUG-03 Chris.T. - Proration Rule Specific - Start --
1341 IF l_proration_rule in ( 'CYNP','CYP','FY','LY','FLY') THEN
1342
1343 determine_reset_flag (p_var_rent_id => p_var_rent_id,
1344 p_period_id => NULL,
1345 p_item_category_code => c_flag.item_category_code,
1346 p_sales_type_code => c_flag.sales_type_code,
1347 p_start_date => c_flag.bkpt_start_date,
1348 x_reset_flag => l_reset_flag);
1349
1350 /* dbms_output.put_line(' l_proration_rule = ' || l_proration_rule);
1351 dbms_output.put_line(' c_flag.period_id = ' || c_flag.period_id);
1352 dbms_output.put_line(' l_last_complete_period_id= ' || l_last_complete_period_id);
1353 dbms_output.put_line(' c_flag.group_date = ' || c_flag.group_date );
1354 dbms_output.put_line(' l_ly_365_start_date= ' || l_ly_365_start_date);
1355 dbms_output.put_line(' g_reset_complete = '|| nvl(g_reset_complete,'x'));
1356
1357 IF l_proration_rule in ('LY' , 'FLY') AND
1358 c_flag.period_id = l_last_complete_period_id AND
1359 c_flag.group_date = l_ly_365_start_date AND
1360 g_reset_complete is NULL THEN
1361 g_reset_complete := 'Y' ;
1362 l_pro_reset_group_id_cnt := nvl(l_pro_reset_group_id_cnt ,0)+ 1;
1363 g_pro_reset_group_id_cnt := l_pro_reset_group_id;
1364 --l_pro_reset_group_id_cnt := nvl(g_pro_reset_group_id_cnt ,0)+ 1;
1365 --g_pro_reset_group_id_cnt := l_pro_reset_group_id;
1366 --pnp_debug_pkg.log(' 365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1367 pnp_debug_pkg.debug('365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1368 /* dbms_output.put_line('365 New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1369 pnp_debug_pkg.debug('365 New G Proration Reset Group ID Count = '||to_char(g_pro_reset_group_id_cnt));
1370 END IF;
1371 --pnp_debug_pkg.log(' l_reset_flag='||l_reset_flag);
1372 --pnp_debug_pkg.log(' l_proration_rule = '||l_proration_rule);
1373 --pnp_debug_pkg.log(' Group Date = '||c_flag.group_date);
1374 --pnp_debug_pkg.log(' l_fy_365_end_date = '||l_fy_365_end_date);
1375 --pnp_debug_pkg.log(' l_ly_365_start_date = '||l_ly_365_start_date);
1376 pnp_debug_pkg.debug('l_reset_flag='||l_reset_flag);
1377 pnp_debug_pkg.debug('l_proration_rule = '||l_proration_rule);
1378 pnp_debug_pkg.debug('Group Date = '||c_flag.group_date);
1379 pnp_debug_pkg.debug('l_fy_365_end_date = '||l_fy_365_end_date);
1380 pnp_debug_pkg.debug('l_ly_365_start_date = '||l_ly_365_start_date);
1381 /* dbms_output.put_line('l_reset_flag='||l_reset_flag);
1382 dbms_output.put_line('l_proration_rule = '||l_proration_rule);
1383 dbms_output.put_line('Group Date = '||c_flag.group_date);
1384 dbms_output.put_line('l_fy_365_end_date = '||l_fy_365_end_date);
1385 dbms_output.put_line('l_ly_365_start_date = '||l_ly_365_start_date);
1386 IF l_reset_flag = 'Y' THEN
1387 IF l_proration_rule = 'FY' AND
1388 c_flag.group_date > l_fy_365_end_date THEN
1389 l_pro_reset_group_id := NULL;
1390 pnp_debug_pkg.debug('FY');
1391 ELSIF l_proration_rule = 'LY' AND
1392 c_flag.group_date < l_ly_365_start_date THEN
1393 l_pro_reset_group_id := NULL;
1394 /* dbms_output.put_line('aaa');
1395 pnp_debug_pkg.debug('LY');
1396 ELSIF l_proration_rule = 'FLY' and
1397 (c_flag.group_Date > l_fy_365_end_date and
1398 c_flag.group_Date < l_ly_365_start_date) THEN
1399 l_pro_reset_group_id := NULL;
1400 pnp_debug_pkg.debug('FLY');
1401 /* dbms_output.put_line('Reset to NULL FLY');
1402 ELSE
1403 l_pro_reset_group_id_cnt := nvl(l_pro_reset_group_id_cnt ,0)+ 1;
1404 g_pro_reset_group_id_cnt := l_pro_reset_group_id;
1405 pnp_debug_pkg.debug('New Proration Reset Group ID Count = '||to_char(l_pro_reset_group_id_cnt));
1406 pnp_debug_pkg.debug('New G Proration Reset Group ID Count = '||to_char(g_pro_reset_group_id_cnt));
1407 END IF;
1408 ELSE
1409 IF l_proration_rule = 'FY' AND
1410 c_flag.group_date > l_fy_365_end_date THEN
1411 l_pro_reset_group_id := NULL;
1412 ELSIF l_proration_rule = 'LY' AND
1413 c_flag.group_date < l_ly_365_start_date THEN
1414 l_pro_reset_group_id := NULL;
1415 /* dbms_output.put_line('aaa');
1416 ELSIF l_proration_rule = 'FLY' THEN -- and
1417 pnp_debug_pkg.debug('FLY');
1418 --pnp_debug_pkg.log(' FLY');
1419 IF c_flag.group_Date > l_fy_365_end_date and --Chris.T. 17MAR2004
1420 c_flag.group_Date < l_ly_365_start_date THEN
1421 l_pro_reset_group_id := NULL;
1422 /* dbms_output.put_line('Reset to NULL FLY');
1423 ELSIF c_flag.group_Date >= l_ly_365_start_date THEN
1424 NULL;
1425 /* dbms_output.put_line('FLY - l_pro_reset_group_id_cnt = '||l_pro_reset_group_id_cnt);
1426 END IF;
1427 END IF;
1428 END IF;
1429
1430 /*UPDATE pn_var_transactions_all
1431 SET proration_reset_group_id = l_pro_reset_group_id_cnt
1432 WHERE var_rent_id = p_VAR_RENT_ID
1433 AND period_id = c_flag.period_id
1434 AND line_item_id = c_flag.line_item_id
1435 AND bkpt_start_date = c_flag.bkpt_start_date;
1436
1437 END IF; --IF l_proration_rule in ( 'CYNP','CYP','FY','LY','FLY') THEN
1438
1439 pnp_debug_pkg.debug('------------------------------- ');
1440 END LOOP;*/
1441 NULL;
1442
1443 --Start Srini 08SEP2004
1444 IF l_proration_rule IN ('LY', 'FLY') AND l_ly_365_start_dt < l_fy_end_date THEN
1445 BEGIN
1446 --Findout invoicing freq
1447 SELECT invg_freq_code
1448 INTO l_invg_freq
1449 FROM pn_var_rent_dates_all
1450 WHERE var_rent_id = p_var_rent_id;
1451 /* DBMS_OUTPUT.PUT_LINE(' l_invg_freq:'||l_invg_freq); */
1452 pnp_debug_pkg.log(' l_invg_freq:'||l_invg_freq);
1453 EXCEPTION
1454 WHEN OTHERS THEN
1455 l_invg_freq := 'MON';
1456 END;
1457
1458 /*FOR i4 IN update_365_days_bkpt(l_ly_365_end_dt)
1459 LOOP
1460
1461 FOR i2 IN csr_get_groups (p_var_rent_id,p_period_id)
1462 LOOP
1463 l_commencement_date := i2.commencement_date;
1464 l_period_start := i2.start_date;
1465 l_period_end := i2.end_date;
1466
1467 FOR i3 IN csr_get_bkpts (i2.period_id)
1468 LOOP
1469 IF i2.grp_start_date between i3.bkpt_start_date and i3.bkpt_end_date
1470 OR i2.grp_end_date between i3.bkpt_start_date and i3.bkpt_end_date
1471 OR i3.bkpt_start_date between i2.grp_start_date and i2.grp_end_date
1472 OR i3.bkpt_end_date between i2.grp_start_date and i2.grp_end_date THEN
1473 IF i2.grp_start_date >= i3.bkpt_start_date THEN
1474 l_start_date := i2.grp_start_date;
1475 ELSE
1476 l_start_date := i3.bkpt_start_date;
1477 END IF;
1478 IF i2.grp_end_date <= i3.bkpt_end_date THEN
1479 l_end_date := i2.grp_end_date;
1480 ELSE
1481 l_end_date := i3.bkpt_end_date;
1482 END IF;
1483
1484 IF l_end_date = l_ly_365_end_dt THEN
1485 l_calc_days := (l_end_date - l_start_date)+1;
1486
1487 IF l_proration_rule = 'NP' THEN
1488 --IF l_proration_rule IN ('NP', 'LY', 'FLY') THEN
1489 l_proration_factor := 1;
1490 ELSE
1491 l_proration_factor := l_calc_days/i2.no_of_group_days;
1492 END IF;
1493
1494 IF l_proration_rule = 'NP' THEN
1495 l_prorate_start := i3.period_bkpt_vol_start;
1496 ELSE
1497 l_prorate_start := (i3.group_bkpt_vol_start*i2.proration_factor)*l_proration_factor;
1498 END IF;
1499
1500 IF i3.group_bkpt_vol_end > 0 THEN
1501 IF l_proration_rule = 'NP' THEN
1502 l_prorate_end := i3.period_bkpt_vol_end;
1503 ELSE
1504 l_prorate_end := (i3.group_bkpt_vol_end*i2.proration_factor)*l_proration_factor;
1505 END IF;
1506 ELSE
1507 l_prorate_end := 0;
1508 END IF;
1509
1510 IF l_prorate_end <> 0 THEN
1511 /*DBMS_OUTPUT.PUT_LINE(' Step1 l_prorate_start = '||l_prorate_start);
1512 DBMS_OUTPUT.PUT_LINE(' Step1 l_prorate_end = '||l_prorate_end);
1513 DBMS_OUTPUT.PUT_LINE(' Step1 i4.reset_group_id = '||i4.reset_group_id);
1514 DBMS_OUTPUT.PUT_LINE(' Step1 i4.proration_reset_group_id = '||i4.proration_reset_group_id);
1515
1516 BEGIN
1517 SELECT COUNT(*)
1518 INTO l_cnt
1519 FROM pn_var_transactions_all
1520 WHERE var_rent_id = p_var_rent_id
1521 AND bkpt_end_date = l_ly_365_end_dt
1522 AND NVL(pr_grp_blended_vol_end, 0) <> 0;
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 l_cnt := 1;
1526 END;
1527 /* DBMS_OUTPUT.PUT_LINE(' l_cnt:'||l_cnt);
1528 BEGIN
1529 --Determine no of bkpt days
1530 SELECT SUM(no_of_bkpt_days)/l_cnt
1531 INTO l_bkpt_days1
1532 FROM pn_var_transactions_all
1533 WHERE var_rent_id = p_var_rent_id
1534 AND NVL(proration_reset_group_id, 0) = NVL(i4.proration_reset_group_id, 0)
1535 --AND NVL(reset_group_id, 0) = NVL(i4.reset_group_id, 0)
1536 AND NVL(pr_grp_blended_vol_end, 0) <> 0;
1537 --AND prorated_group_sales IS NOT NULL;
1538 pnp_debug_pkg.log(' l_bkpt_days1 = '||l_bkpt_days1);
1539 /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days1:'||l_bkpt_days1);
1540
1541 --Determine no of days for which bkpt is missing
1542 l_bkpt_days2 := (l_ly_365_end_dt - ADD_MONTHS(l_ly_365_end_dt, -12)) - l_bkpt_days1;
1543 pnp_debug_pkg.log(' l_bkpt_days2 = '||l_bkpt_days2);
1544 /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days2:'||l_bkpt_days2);
1545
1546 EXCEPTION
1547 WHEN OTHERS THEN
1548 l_bkpt_days2 := NULL;
1549 /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days2:'||l_bkpt_days2);
1550 END;
1551
1552 IF l_bkpt_days2 IS NOT NULL THEN
1553 IF l_bkpt_days2 <> 0 THEN
1554 --Determine Actual bkpt start and bkpt end
1555 l_pr_start := l_prorate_start;
1556 l_pr_end := l_prorate_end;
1557 BEGIN
1558 SELECT DECODE(l_invg_freq, 'MON', 0, l_prorate_start) + ((SUM(pr_grp_blended_vol_start) /
1559 SUM(no_of_bkpt_days)) * l_bkpt_days2),
1560 DECODE(l_invg_freq, 'MON', 0, l_prorate_end) + ((SUM(pr_grp_blended_vol_end) /
1561 SUM(no_of_bkpt_days)) * l_bkpt_days2)
1562 INTO l_prorate_start, l_prorate_end
1563 FROM pn_var_transactions_all
1564 WHERE var_rent_id = p_var_rent_id
1565 AND NVL(proration_reset_group_id, 0) <> NVL(i4.proration_reset_group_id, 0)
1566 --AND NVL(reset_group_id, 0) <> NVL(i4.reset_group_id, 0)
1567 AND bkpt_end_date BETWEEN ADD_MONTHS(i4.bkpt_start_date, -11) AND i4.bkpt_start_date
1568 AND NVL(pr_grp_blended_vol_end, 0) <> 0;
1569 --AND prorated_group_sales IS NOT NULL;
1570 EXCEPTION
1571 WHEN OTHERS THEN
1572 --Will not come here
1573 l_prorate_start := l_pr_start;
1574 l_prorate_end := l_pr_end;
1575 END;
1576 END IF;
1577 IF l_prorate_start IS NULL THEN
1578 l_prorate_start := l_pr_start;
1579 l_prorate_end := l_pr_end;
1580 END IF;
1581
1582 pnp_debug_pkg.log(' l_prorate_start = '||l_prorate_start);
1583 pnp_debug_pkg.log(' l_prorate_end = '||l_prorate_end);
1584 /* DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_start = '||l_prorate_start);
1585 DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_end = '||l_prorate_end);
1586
1587 UPDATE pn_var_transactions_all
1588 SET prorated_grp_vol_start = l_prorate_start
1589 ,prorated_grp_vol_end = l_prorate_end
1590 ,pr_grp_blended_vol_start = l_prorate_start
1591 ,pr_grp_blended_vol_end = l_prorate_end
1592 ,last_update_date = SYSDATE
1593 ,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),0)
1594 ,last_update_login = NVL(FND_PROFILE.VALUE('USER_ID'),0 )
1595 WHERE transaction_id = i4.transaction_id;
1596 /* DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_start='||l_prorate_start);
1597 DBMS_OUTPUT.PUT_LINE(' Step2 Rows Updated='||TO_CHAR(SQL%ROWCOUNT));
1598
1599 END IF; --l_bkpt_days2 IS NOT NULL
1600 END IF; --l_bkpt_end <> 0
1601 END IF; --l_end_date = l_ly_365_end_dt
1602 END IF; --multiple condition
1603 END LOOP; --i3
1604 END LOOP; --i2
1605 END LOOP; --i4 */
1606
1607 /*FOR i4 IN update_365_days_bkpt_strat(l_ly_365_end_dt)
1608 LOOP
1609
1610 FOR i2 IN csr_get_groups (p_var_rent_id,p_period_id)
1611 LOOP
1612 l_commencement_date := i2.commencement_date;
1613 l_period_start := i2.start_date;
1614 l_period_end := i2.end_date;
1615
1616 FOR i3 IN csr_get_bkpts (i2.period_id)
1617 LOOP
1618 IF i2.grp_start_date between i3.bkpt_start_date and i3.bkpt_end_date
1619 OR i2.grp_end_date between i3.bkpt_start_date and i3.bkpt_end_date
1620 OR i3.bkpt_start_date between i2.grp_start_date and i2.grp_end_date
1621 OR i3.bkpt_end_date between i2.grp_start_date and i2.grp_end_date THEN
1622 IF i2.grp_start_date >= i3.bkpt_start_date THEN
1623 l_start_date := i2.grp_start_date;
1624 ELSE
1625 l_start_date := i3.bkpt_start_date;
1626 END IF;
1627 IF i2.grp_end_date <= i3.bkpt_end_date THEN
1628 l_end_date := i2.grp_end_date;
1629 ELSE
1630 l_end_date := i3.bkpt_end_date;
1631 END IF;
1632
1633 IF l_end_date = l_ly_365_end_dt THEN
1634 l_calc_days := (l_end_date - l_start_date)+1;
1635
1636 IF l_proration_rule = 'NP' THEN
1637 --IF l_proration_rule IN ('NP', 'LY', 'FLY') THEN
1638 l_proration_factor := 1;
1639 ELSE
1640 l_proration_factor := l_calc_days/i2.no_of_group_days;
1641 END IF;
1642
1643 IF l_proration_rule = 'NP' THEN
1644 l_prorate_start := i3.period_bkpt_vol_start;
1645 ELSE
1646 l_prorate_start := (i3.group_bkpt_vol_start*i2.proration_factor)*l_proration_factor;
1647 END IF;
1648
1649 IF i3.group_bkpt_vol_end > 0 THEN
1650 IF l_proration_rule = 'NP' THEN
1651 l_prorate_end := i3.period_bkpt_vol_end;
1652 ELSE
1653 l_prorate_end := (i3.group_bkpt_vol_end*i2.proration_factor)*l_proration_factor;
1654 END IF;
1655 ELSE
1656 l_prorate_end := 0;
1657 END IF;
1658
1659 IF l_prorate_end = 0 THEN
1660 /* DBMS_OUTPUT.PUT_LINE(' Step1 l_prorate_start = '||l_prorate_start);
1661 DBMS_OUTPUT.PUT_LINE(' Step1 l_prorate_end = '||l_prorate_end);
1662 DBMS_OUTPUT.PUT_LINE(' Step1 i4.reset_group_id = '||i4.reset_group_id);
1663 DBMS_OUTPUT.PUT_LINE(' Step1 i4.proration_reset_group_id = '||i4.proration_reset_group_id);
1664
1665 BEGIN
1666 SELECT COUNT(*)
1667 INTO l_cnt
1668 FROM pn_var_transactions_all
1669 WHERE var_rent_id = p_var_rent_id
1670 AND bkpt_end_date = l_ly_365_end_dt
1671 AND NVL(pr_grp_blended_vol_end, 0) = 0;
1672 EXCEPTION
1673 WHEN OTHERS THEN
1674 l_cnt := 1;
1675 END;
1676 /* DBMS_OUTPUT.PUT_LINE(' l_cnt:'||l_cnt);
1677 BEGIN
1678 --Determine no of bkpt days
1679 SELECT SUM(no_of_bkpt_days)/l_cnt
1680 INTO l_bkpt_days1
1681 FROM pn_var_transactions_all
1682 WHERE var_rent_id = p_var_rent_id
1683 AND NVL(proration_reset_group_id, 0) = NVL(i4.proration_reset_group_id, 0)
1684 --AND NVL(reset_group_id, 0) = NVL(i4.reset_group_id, 0)
1685 AND NVL(pr_grp_blended_vol_end, 0) = 0;
1686 --AND prorated_group_sales IS NOT NULL;
1687 pnp_debug_pkg.log(' l_bkpt_days1 = '||l_bkpt_days1);
1688 /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days1:'||l_bkpt_days1);
1689
1690 --Determine no of days for which bkpt is missing
1691 l_bkpt_days2 := (l_ly_365_end_dt - ADD_MONTHS(l_ly_365_end_dt, -12)) - l_bkpt_days1;
1692 pnp_debug_pkg.log(' l_bkpt_days2 = '||l_bkpt_days2);
1693 /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days2:'||l_bkpt_days2);
1694
1695 EXCEPTION
1696 WHEN OTHERS THEN
1697 l_bkpt_days2 := NULL;
1698 /* DBMS_OUTPUT.PUT_LINE(' l_bkpt_days2:'||l_bkpt_days2);
1699 END;
1700
1701 IF l_bkpt_days2 IS NOT NULL THEN
1702 IF l_bkpt_days2 <> 0 THEN
1703 --Determine Actual bkpt start and bkpt end
1704 l_pr_start := l_prorate_start;
1705 l_pr_end := l_prorate_end;
1706 BEGIN
1707 SELECT DECODE(l_invg_freq, 'MON', 0, l_prorate_start) + ((SUM(pr_grp_blended_vol_start) /
1708 SUM(no_of_bkpt_days)) * l_bkpt_days2),
1709 DECODE(l_invg_freq, 'MON', 0, l_prorate_end) + ((SUM(pr_grp_blended_vol_end) /
1710 SUM(no_of_bkpt_days)) * l_bkpt_days2)
1711 INTO l_prorate_start, l_prorate_end
1712 FROM pn_var_transactions_all
1713 WHERE var_rent_id = p_var_rent_id
1714 AND NVL(proration_reset_group_id, 0) <> NVL(i4.proration_reset_group_id, 0)
1715 --AND NVL(reset_group_id, 0) <> NVL(i4.reset_group_id, 0)
1716 AND bkpt_end_date BETWEEN ADD_MONTHS(i4.bkpt_start_date, -11) AND i4.bkpt_start_date
1717 AND NVL(pr_grp_blended_vol_end, 0) = 0;
1718 --AND prorated_group_sales IS NOT NULL;
1719 EXCEPTION
1720 WHEN OTHERS THEN
1721 --Will not come here
1722 l_prorate_start := l_pr_start;
1723 l_prorate_end := l_pr_end;
1724 END;
1725 END IF;
1726 IF l_prorate_start IS NULL THEN
1727 l_prorate_start := l_pr_start;
1728 l_prorate_end := l_pr_end;
1729 END IF;
1730
1731 pnp_debug_pkg.log(' l_prorate_start = '||l_prorate_start);
1732 pnp_debug_pkg.log(' l_prorate_end = '||l_prorate_end);
1733 /* DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_start = '||l_prorate_start);
1734 DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_end = '||l_prorate_end);
1735
1736 UPDATE pn_var_transactions_all
1737 SET prorated_grp_vol_start = l_prorate_start
1738 ,prorated_grp_vol_end = l_prorate_end
1739 ,pr_grp_blended_vol_start = l_prorate_start
1740 ,pr_grp_blended_vol_end = l_prorate_end
1741 ,last_update_date = SYSDATE
1742 ,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),0)
1743 ,last_update_login = NVL(FND_PROFILE.VALUE('USER_ID'),0 )
1744 WHERE transaction_id = i4.transaction_id;
1745 /* DBMS_OUTPUT.PUT_LINE(' Step2 l_prorate_start='||l_prorate_start);
1746 DBMS_OUTPUT.PUT_LINE(' Step2 Rows Updated='||TO_CHAR(SQL%ROWCOUNT));
1747
1748 END IF; --l_bkpt_days2 IS NOT NULL
1749 END IF; --l_bkpt_end = 0
1750 END IF; --l_end_date = l_ly_365_end_dt
1751 END IF; --multiple condition
1752 END LOOP; --i3
1753 END LOOP; --i2
1754 END LOOP; --i4 */
1755 END IF;
1756 --End Srini 08SEP2004
1757
1758 -- Call Procedure to update blended_period_vol_start and
1759 -- belnded_period_vol_end with the appropriate sum of the
1760 -- prorated_grp_vol_start and prorated_grp_vol_end, grouped
1761 -- by period_id, line_item_id, reset_group_id and bkpt_rate
1762 -- for the current var_rent_id
1763 -- 24-JUL-03 Chris.T.
1764 ---------------------------------------------------------
1765 pnp_debug_pkg.log(' Call to Update Blended Period Volume - Start and END');
1766 pnp_debug_pkg.debug('Call to Update Blended Period Volume - Start and END');
1767
1768
1769 IF l_proration_rule IN ('CYNP','CYP') THEN
1770 update_blended_period(p_var_rent_id => p_var_rent_id,
1771 p_start_date => l_commencement_date,
1772 p_proration_rule => l_proration_rule);
1773 ELSE
1774 --IF l_proration_rule <> 'NP' THEN --Chris.T. 10FEB2004
1775 update_blended_period(p_var_rent_id => p_var_rent_id);
1776 --END IF; --Chris.T. 10FEB2004
1777 END IF;
1778
1779 pnp_debug_pkg.log(' Call update_ytd_bkpts');
1780 pnp_debug_pkg.debug('Call update_ytd_bkpts');
1781 update_ytd_bkpts ( p_var_rent_id => p_var_rent_id,
1782 p_period_id => p_period_id);
1783
1784 COMMIT;
1785
1786 pnp_debug_pkg.debug('End of Populate_Transactions');
1787 PNP_DEBUG_PKG.log('PN_VAR_CHG_CAL_PKG.populate_transactions (-)');
1788 --pnp_debug_pkg.disable_file_debug;
1789 EXCEPTION
1790 When OTHERS THEN
1791 /* dbms_output.put_line(' Error While Running Populate Transactions:' || SQLERRM); */
1792 null;
1793
1794 END populate_transactions;
1795
1796 /*===========================================================================+
1797 | PROCEDURE UPDATE_YTD_BKPTS
1798 |
1799 | DESCRIPTION
1800 | This procedure will add the grup breakpoints to arrive at the YTD breakpoints
1801 | the summation is reset whenever there is rate change between groups.
1802 | In case of proration rule being combined sales with no proration or
1803 | combined year sales with proration we will add across the
1804 | periods. i.e the summation does not reset when the period changes. In all
1805 | other cases the ytd summation resets when the period changes.
1806 |
1807 | SCOPE - PUBLIC
1808 |
1809 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1810 |
1811 | ARGUMENTS : IN:
1812 | P_VAR_RENT_ID
1813 |
1814 | OUT:
1815 |
1816 | RETURNS : None
1817 |
1818 |
1819 | MODIFICATION HISTORY
1820 |
1821 | 16-MAR-2003 graghuna o Created
1822 +===========================================================================*/
1823 PROCEDURE update_ytd_bkpts(p_var_rent_id IN NUMBER,
1824 p_period_id IN NUMBER,
1825 p_start_date IN DATE ,
1826 p_end_date IN DATE)
1827 IS
1828
1829 /* Chris.T. 13-Aug-03 -- To accomodate Proration rules -- Start*/
1830 CURSOR get_periods_cur IS
1831 SELECT pvt.period_id,pvt.start_date,pvt.end_date,pvt.partial_period
1832 ,pvr.proration_rule
1833 FROM pn_var_periods_all pvt
1834 ,pn_var_rents_all pvr
1835 WHERE pvt.var_Rent_id = p_var_rent_id
1836 AND pvt.var_rent_id = pvr.var_rent_id
1837 ORDER by pvt.start_date;
1838 /* Chris.T. 13-Aug-03 -- To accomodate Proration rules -- End*/
1839
1840 /* Chris.T. 11-Aug-03 -- To accomodate Proration rules -- Start*/
1841 /*CURSOR pn_var_trx_cur IS
1842 SELECT pvt.*
1843 ,pvl.sales_type_code
1844 ,pvl.item_category_code
1845 ,pvr.proration_rule
1846 ,per.partial_period
1847 ,per.start_date
1848 ,per.end_date
1849 FROM pn_var_transactions_all pvt
1850 ,pn_var_lines_all pvl
1851 ,pn_var_periods_all per
1852 ,pn_var_rents_all pvr
1853 WHERE pvt.var_rent_id = p_var_rent_id
1854 AND per.period_id = NVL(p_period_id,per.period_id)
1855 AND pvt.period_id = per.period_id
1856 AND pvt.period_id = pvl.period_id
1857 AND per.var_rent_id = pvr.var_rent_id
1858 AND pvt.line_item_id = pvl.line_item_id
1859 AND pvt.bkpt_start_date >= NVL(p_start_date,pvt.bkpt_start_date)
1860 AND pvt.bkpt_end_date <= NVL(p_end_date , pvt.bkpt_end_date)
1861 ORDER BY pvl.sales_type_code,pvl.item_category_code,pvt.period_id,
1862 pvt.reset_group_id,pvt.bkpt_rate,pvt.bkpt_start_date,pvt.pr_grp_blended_vol_start;
1863
1864 CURSOR pn_var_trx_cur_pro IS
1865 SELECT pvt.*
1866 ,pvl.sales_type_code
1867 ,pvl.item_category_code
1868 ,pvr.proration_rule
1869 ,per.partial_period
1870 ,per.start_date
1871 ,per.end_date
1872 FROM pn_var_transactions_all pvt
1873 ,pn_var_lines_all pvl
1874 ,pn_var_periods_all per
1875 ,pn_var_rents_all pvr
1876 WHERE pvt.var_rent_id = p_var_rent_id
1877 AND per.period_id = NVL(p_period_id,per.period_id)
1878 AND pvt.period_id = per.period_id
1879 AND pvt.period_id = pvl.period_id
1880 AND per.var_rent_id = pvr.var_rent_id
1881 AND pvt.line_item_id = pvl.line_item_id
1882 AND pvt.bkpt_start_date >= NVL(p_start_date,pvt.bkpt_start_date)
1883 AND pvt.bkpt_end_date <= NVL(p_end_date , pvt.bkpt_end_date)
1884 AND pvr.proration_rule NOT IN ('STD','NP')
1885 ORDER BY pvl.sales_type_code,pvl.item_category_code,
1886 pvt.proration_reset_group_id,pvt.bkpt_rate,
1887 pvt.bkpt_start_date,pvt.pr_grp_blended_vol_start;*/
1888
1889 CURSOR pn_var_grp_dt (p_var_rent_id NUMBER) IS
1890 SELECT max(group_date), min(group_date), min(grp_start_date)+364
1891 FROM pn_var_grp_dates_all
1892 WHERE var_rent_id = p_var_rent_id;
1893
1894 CURSOR get_max_per_grp_dt (p_var_rent_id NUMBER --Chris.T. 19-NOV-03
1895 ,p_period_id NUMBER) IS
1896 SELECT max(group_date)
1897 FROM pn_var_grp_dates_all
1898 WHERE var_rent_id = p_var_rent_id
1899 AND period_id = p_period_id;
1900
1901
1902 CURSOR get_invoice_grp_dt (p_var_rent_id NUMBER
1903 ,p_period_id NUMBER
1904 ,p_min_grp_dt_364 DATE) IS
1905 SELECT group_date
1906 FROM pn_var_grp_dates_all
1907 WHERE var_rent_id = p_var_rent_id
1908 --AND period_id = p_period_id
1909 AND grp_start_date <= p_min_grp_dt_364
1910 AND grp_end_date >= p_min_grp_dt_364;
1911 /* Chris.T. 11-Aug-03 -- To accomodate Proration rules -- End*/
1912
1913 Cursor is_last_year_partial IS
1914 SELECT period_id,partial_period
1915 FROM pn_var_periods_all
1916 WHERE period_id = (SELECT max(period_id)
1917 FROM pn_var_periods_all
1918 WHERE var_rent_id = p_var_rent_id);
1919
1920 l_summ_vol_start NUMBER := 0;
1921 l_summ_vol_end NUMBER := 0;
1922 l_pro_bkpt_vol_start NUMBER := 0;
1923 l_pro_bkpt_vol_end NUMBER := 0;
1924 l_old_period_id NUMBER := 0;
1925 l_old_line_item_id NUMBER := 0;
1926 l_old_bkpt_rate NUMBER := 0;
1927 l_period_rownum NUMBER := 0;
1928 l_proration_rule VARCHAR2(30) := NULL;
1929 l_sales_type_code VARCHAR2(30) := 'X'; /*11-AUG-03 Chris.T.*/
1930 l_item_category_code VARCHAR2(30) := 'X'; /*11-AUG-03 Chris.T.*/
1931 l_first_partial_period VARCHAR2(1):= 'X';
1932 l_last_partial_period VARCHAR2(1):= 'X';
1933 l_last_partial_period_id NUMBER;
1934 l_first_full_period VARCHAR2(1):= 'X';
1935 l_old_reset_group_id NUMBER := 99.99;
1936 l_old_pro_reset_group_id NUMBER := 99.99;
1937 l_prv_partial_period VARCHAR2(1) := 'X'; /*13-AUG-03 Chris.T.*/
1938 l_pro_invoice_flag VARCHAR2(1) := 'X'; /*13-AUG-03 Chris.T.*/
1939 l_invoice_grp_dt DATE; /*13-AUG-03 Chris.T.*/
1940 l_365_grp_dt DATE; /*13-AUG-03 Chris.T.*/
1941 l_max_grp_dt DATE; /*13-AUG-03 Chris.T.*/
1942 l_min_grp_dt DATE; /*13-AUG-03 Chris.T.*/
1943 l_min_grp_dt_364 DATE; /*13-AUG-03 Chris.T.*/
1944 l_first_partial_year VARCHAR2(4); /*18-AUG-03 Chris.T.*/
1945 l_last_partial_year VARCHAR2(4); /*18-AUG-03 Chris.T.*/
1946 l_curr_grp_date_year VARCHAR2(4); /*18-AUG-03 Chris.T.*/
1947 l_old_partial_period VARCHAR2(1);
1948 /*l_old_grp_date_id pn_var_transactions_all.grp_date_id%TYPE; --Chris.T 19-NOV-03
1949 l_old_bkpt_start_date pn_var_transactions_all.bkpt_start_date%TYPE; --Chris.T 19-NOV-03
1950 l_old_bkpt_detail_id pn_var_transactions_all.bkpt_detail_id%TYPE; --Chris.T 19-NOV-03
1951 l_max_per_grp_dt pn_var_grp_dates_all.group_date%TYPE; --Chris.T 19-NOV-03
1952 l_old_group_date pn_var_transactions_all.group_date%TYPE; --Chris.T 19-NOV-03 */
1953 l_ly_365_start_date DATE;
1954 l_fy_365_end_Date DATE;
1955 BEGIN
1956
1957 /*13-AUG-03 Chris.T. -Start*/
1958 pnp_debug_pkg.debug('UPDATE_YTD_BKPTS (+)');
1959 pnp_debug_pkg.debug('Parameter : p_var_rent_id = '|| p_var_rent_id);
1960 pnp_debug_pkg.debug('Parameter : p_period_id = '|| p_period_id);
1961 OPEN pn_var_grp_dt(p_var_rent_id);
1962 FETCH pn_var_grp_dt into l_max_grp_dt, l_min_grp_dt, l_min_grp_dt_364;
1963 l_first_partial_year := to_char(l_min_grp_dt,'YYYY');
1964 l_last_partial_year := to_char(l_max_grp_dt,'YYYY');
1965 IF pn_var_grp_dt%NOTFOUND THEN
1966 CLOSE pn_var_grp_dt;
1967 pnp_debug_pkg.debug('Raising no data found');
1968 RAISE NO_DATA_FOUND;
1969 ELSE
1970 pnp_debug_pkg.debug('Max = '||to_char(l_max_grp_dt,'MM/DD/YY')||
1971 'Min = '||to_char(l_min_grp_dt,'MM/DD/YY')||
1972 'Min_364 = '||to_char(l_min_grp_dt_364,'MM/DD/YY'));
1973 END IF;
1974 CLOSE pn_var_grp_dt;
1975 /*13-AUG-03 Chris.T. -End*/
1976
1977 pnp_debug_pkg.debug('opening periods cursor');
1978
1979 /*FOR pn_var_trx_rec in pn_var_trx_cur LOOP
1980
1981 IF l_old_reset_group_id <> pn_var_trx_rec.reset_group_id
1982 OR l_old_period_id <> pn_var_trx_rec.period_id
1983 OR l_old_line_item_id <> pn_var_trx_rec.line_item_id
1984 OR l_old_bkpt_rate <> pn_var_trx_rec.bkpt_rate THEN
1985 l_summ_vol_start := 0;
1986 l_summ_vol_end := 0;
1987 pnp_debug_pkg.debug('Reset/Initialize YTD Group Vol Start/End');
1988 END IF;
1989
1990 l_summ_vol_start := l_summ_vol_start + pn_var_trx_rec.prorated_grp_vol_start;
1991 l_summ_vol_end := l_summ_vol_end + pn_var_trx_rec.prorated_grp_vol_end;
1992
1993 UPDATE pn_var_transactions_all
1994 SET ytd_group_vol_start = l_summ_vol_start,
1995 ytd_group_vol_end = l_summ_vol_end
1996 WHERE grp_date_id = pn_var_trx_rec.grp_date_id
1997 AND bkpt_start_date = pn_var_trx_rec.bkpt_start_date
1998 AND bkpt_rate = pn_var_trx_rec.bkpt_rate
1999 AND line_item_id = pn_var_trx_rec.line_item_id
2000 AND bkpt_detail_id = pn_var_trx_rec.bkpt_detail_id
2001 AND reset_group_id = pn_var_trx_rec.reset_group_id;
2002 pnp_debug_pkg.debug('Rows Updated = '||to_char(sql%rowcount));
2003
2004 l_sales_type_code := pn_var_trx_rec.sales_type_code;
2005 l_item_category_code := pn_var_trx_rec.item_category_code;
2006 /* 11-AUG-03 Chris.T. - Proration Rule Specific -- End
2007
2008 l_old_period_id := pn_var_trx_rec.period_id;
2009 l_old_line_item_id := pn_var_trx_rec.line_item_id;
2010 l_old_reset_group_id := pn_var_trx_rec.reset_group_id;
2011 l_old_bkpt_rate := pn_var_trx_rec.bkpt_rate;
2012
2013 END LOOP; */-- pn_var_trex_rec end loop;
2014
2015 /*l_sales_type_code := 'X';
2016 l_item_category_code := 'X';
2017 l_old_period_id := 0;
2018 l_old_line_item_id := 0;
2019 l_old_reset_group_id := 0;
2020 l_old_bkpt_rate := 0;
2021 l_old_grp_date_id := 0; --Chris.T 19-NOV-03
2022 l_old_bkpt_start_date := NULL; --Chris.T 19-NOV-03
2023 l_old_bkpt_detail_id := 0; --Chris.T 19-NOV-03
2024 l_old_pro_reset_group_id := 0; --Chris.T 19-NOV-03
2025
2026
2027 FOR pn_var_trx_rec in pn_var_trx_cur_pro LOOP
2028
2029 IF (l_old_period_id <> pn_var_trx_rec.period_id)THEN
2030 l_prv_partial_period := l_old_partial_period; /*13-AUG-03 Chris.T.
2031 --Chris.T 19-NOV-03 Start
2032 pnp_debug_pkg.debug('Prev partial period = '||pn_var_trx_rec.partial_period);
2033 pnp_debug_pkg.debug('-----------------------------------------------');
2034
2035 END IF;
2036 l_pro_invoice_flag := 'X'; /*Chris.T. 13-Aug-03
2037 l_curr_grp_date_year := to_char(pn_var_trx_rec.group_date,'YYYY'); /*Chris.T. 18-Aug-03
2038
2039 --
2040 -- Proration Rule Based Processing
2041 --
2042 pnp_debug_pkg.debug('proration rule = '|| pn_var_trx_rec.proration_rule);
2043 IF pn_var_trx_rec.proration_rule IN ('FY','LY','FLY','CYNP','CYP') THEN /*Chris.T. 18-Aug-03
2044
2045 IF l_ly_365_start_date IS NULL THEN
2046 l_ly_365_start_date := get_ly_365_start_date ( p_var_rent_id => p_var_rent_id);
2047 --dbms_output.put_line('l_ly_365_start_date = '|| l_ly_365_start_date);
2048 OPEN get_invoice_grp_dt (pn_var_trx_rec.var_rent_id
2049 ,pn_var_trx_rec.period_id
2050 ,l_ly_365_start_date);
2051 FETCH get_invoice_grp_dt INTO l_365_grp_dt;
2052 CLOSE get_invoice_grp_dt;
2053 --dbms_output.put_line('l_365_grp_dt = '|| l_365_grp_dt);
2054
2055 FOR last_period_rec in is_last_year_partial LOOP
2056 l_last_partial_period_id := last_period_rec.period_id;
2057 l_last_partial_period := last_period_rec.partial_period;
2058 EXIT;
2059 END LOOP;
2060 END IF;
2061
2062 /* 11-AUG-03 Chris.T. - Proration Rule Specific -- Start
2063
2064 pnp_debug_pkg.debug(' group_date = ' || pn_var_trx_rec.group_date);
2065 pnp_debug_pkg.debug(' l_old_pro_reset_group_id = ' || l_old_pro_reset_group_id);
2066 pnp_debug_pkg.debug(' pn_var_trx_rec.proration_reset_group_id = ' || pn_var_trx_rec.proration_reset_group_id);
2067 pnp_debug_pkg.debug(' l_sales_type_code = ' || l_sales_type_code);
2068 pnp_debug_pkg.debug(' pn_var_trx_rec.sales_type_code = ' || pn_var_trx_rec.sales_type_code);
2069 pnp_debug_pkg.debug(' l_item_category_code = ' || l_item_category_code);
2070 pnp_debug_pkg.debug(' pn_var_trx_rec.item_category_code = ' || pn_var_trx_rec.item_category_code);
2071 pnp_debug_pkg.debug(' l_old_bkpt_rate = ' || l_old_bkpt_rate);
2072 pnp_debug_pkg.debug(' pn_var_trx_rec.bkpt_rate = ' || pn_var_trx_rec.bkpt_rate);
2073 pnp_debug_pkg.debug(' l_pro_bkpt_vol_start = ' || l_pro_bkpt_vol_start);
2074 pnp_debug_pkg.debug(' l_pro_bkpt_vol_end = ' || l_pro_bkpt_vol_end);
2075
2076 /*dbms_output.put_line('Current group_date = ' || pn_var_trx_rec.group_date);
2077 --dbms_output.put_line(' l_old_pro_reset_group_id = ' || l_old_pro_reset_group_id);
2078 --dbms_output.put_line(' pn_var_trx_rec.proration_reset_group_id = ' || pn_var_trx_rec.proration_reset_group_id);
2079 --dbms_output.put_line(' l_sales_type_code = ' || l_sales_type_code);
2080 --dbms_output.put_line(' pn_var_trx_rec.sales_type_code = ' || pn_var_trx_rec.sales_type_code);
2081 --dbms_output.put_line(' l_item_category_code = ' || l_item_category_code);
2082 --dbms_output.put_line(' pn_var_trx_rec.item_category_code = ' || pn_var_trx_rec.item_category_code);
2083 --dbms_output.put_line(' l_old_bkpt_rate = ' || l_old_bkpt_rate);
2084 --dbms_output.put_line(' pn_var_trx_rec.bkpt_rate = ' || pn_var_trx_rec.bkpt_rate);
2085 --dbms_output.put_line(' l_pro_bkpt_vol_start = ' || l_pro_bkpt_vol_start);
2086 --dbms_output.put_line(' l_pro_bkpt_vol_end = ' || l_pro_bkpt_vol_end);
2087 dbms_output.put_line(' l_old_period_id = ' || l_old_period_id);
2088 dbms_output.put_line(' current period id = ' || pn_var_trx_rec.period_id);
2089 dbms_output.put_line(' Current Invoice Flag = ' || pn_var_trx_rec.invoice_flag);
2090
2091 IF l_old_pro_reset_group_id <> pn_var_trx_rec.proration_reset_group_id
2092 OR l_sales_type_code <> pn_var_trx_rec.sales_type_code
2093 OR l_item_category_code <> pn_var_trx_rec.item_category_code
2094 OR l_old_bkpt_rate <> pn_var_trx_rec.bkpt_rate THEN
2095
2096 l_pro_bkpt_vol_start := 0;
2097 l_pro_bkpt_vol_end := 0;
2098 pnp_debug_pkg.debug('Reset/Initialize Pro Group Vol Start/End');
2099
2100 END IF;
2101
2102 IF pn_var_trx_rec.proration_rule IN ('CYP','CYNP') AND --Chris.T. 17MAR2004 Start
2103 l_old_period_id <> 0 AND
2104 l_old_period_id <> pn_var_trx_rec.period_id AND
2105 nvl(pn_var_trx_rec.invoice_flag,'X') <> 'P' THEN
2106
2107 l_pro_bkpt_vol_start := 0;
2108 l_pro_bkpt_vol_end := 0;
2109 pnp_debug_pkg.debug(' CYNP - Reset/Initialize Pro Group Vol Start/End');
2110 /* dbms_output.put_line(' CYNP - Reset/Initialize Pro Group Vol Start/End');
2111
2112 END IF; --Chris.T. 17MAR2004 End
2113
2114 /* dbms_output.put_line(' STEP1 l_pro_bkpt_vol_start = ' || l_pro_bkpt_vol_start);
2115 dbms_output.put_line(' STEP1 pn_var_trx_rec.pr_grp_blended_vol_start = ' || pn_var_trx_rec.pr_grp_blended_vol_start);
2116 l_pro_bkpt_vol_start := l_pro_bkpt_vol_start + pn_var_trx_rec.pr_grp_blended_vol_start;
2117 l_pro_bkpt_vol_end := l_pro_bkpt_vol_end + pn_var_trx_rec.pr_grp_blended_vol_end;
2118
2119 /* dbms_output.put_line(' STEP2 l_pro_bkpt_vol_start = ' || l_pro_bkpt_vol_start);
2120 dbms_output.put_line(' l_pro_bkpt_vol_end = ' || l_pro_bkpt_vol_end);
2121
2122 IF pn_var_trx_rec.proration_rule IN ('FY','LY','FLY') THEN
2123 UPDATE pn_var_transactions_all
2124 SET pr_ytd_blended_vol_start = l_pro_bkpt_vol_start,
2125 pr_ytd_blended_vol_end = l_pro_bkpt_vol_end
2126 WHERE grp_date_id = pn_var_trx_rec.grp_date_id
2127 AND bkpt_start_date = pn_var_trx_rec.bkpt_start_date
2128 AND bkpt_rate = pn_var_trx_rec.bkpt_rate
2129 AND line_item_id = pn_var_trx_rec.line_item_id
2130 AND bkpt_detail_id = pn_var_trx_rec.bkpt_detail_id
2131 AND proration_reset_group_id = pn_var_trx_rec.proration_reset_group_id;
2132 pnp_debug_pkg.debug('Rows Updated = '||to_char(sql%rowcount));
2133
2134 ELSIF pn_var_trx_rec.proration_rule IN ('CYNP','CYP') THEN
2135 UPDATE pn_var_transactions_all
2136 SET pr_ytd_blended_vol_start = l_pro_bkpt_vol_start,
2137 pr_ytd_blended_vol_end = l_pro_bkpt_vol_end,
2138 ytd_group_vol_start = l_pro_bkpt_vol_start,
2139 ytd_group_vol_end = l_pro_bkpt_vol_end
2140 WHERE grp_date_id = pn_var_trx_rec.grp_date_id
2141 AND bkpt_start_date = pn_var_trx_rec.bkpt_start_date
2142 AND bkpt_rate = pn_var_trx_rec.bkpt_rate
2143 AND line_item_id = pn_var_trx_rec.line_item_id
2144 AND bkpt_detail_id = pn_var_trx_rec.bkpt_detail_id
2145 AND proration_reset_group_id = pn_var_trx_rec.proration_reset_group_id;
2146 pnp_debug_pkg.debug('Rows Updated = '||to_char(sql%rowcount));
2147
2148 END IF; --IF pn_var_trx_rec.proration_rule IN ('FY','LY','FLY','CYNP','CYP')
2149
2150 IF pn_var_trx_rec.partial_period = 'Y' THEN -- Partial Year
2151 pnp_debug_pkg.debug('Partial Period');
2152 /* dbms_output.put_line(' Partial Period ');
2153 l_max_per_grp_dt := NULL;
2154 OPEN get_max_per_grp_dt (pn_var_trx_rec.var_rent_id
2155 ,pn_var_trx_rec.period_id);
2156 FETCH get_max_per_grp_dt INTO l_max_per_grp_dt;
2157 IF get_max_per_grp_dt%NOTFOUND THEN
2158 CLOSE get_max_per_grp_dt;
2159 RAISE NO_DATA_FOUND;
2160 END IF;
2161 CLOSE get_max_per_grp_dt;
2162
2163 l_first_partial_year := to_char(l_max_per_grp_dt,'YYYY');
2164 /* dbms_output.put_line(' l_first_partial_year ='|| l_first_partial_year);
2165 dbms_output.put_line(' l_curr_grp_date_year ='|| l_curr_grp_date_year);
2166 dbms_output.put_line(' l_last_partial_period := '||l_last_partial_period);
2167 dbms_output.put_line(' l_last_partial_period_id := '||l_last_partial_period_id);
2168 dbms_output.put_line(' pn_var_trx_rec.proration_reset_group_id:= '||pn_var_trx_rec.proration_reset_group_id);
2169 dbms_output.put_line(' l_old_pro_reset_group_id := '||l_old_pro_reset_group_id); */
2170
2171 /*
2172 IF (pn_var_trx_rec.proration_rule IN ('FLY','FY') AND
2173 l_curr_grp_date_year <= l_first_partial_year AND
2174 l_last_partial_period_id <> pn_var_trx_rec.period_id) THEN
2175 IF l_old_pro_reset_group_id <> 0 AND
2176 pn_var_trx_rec.proration_reset_group_id <> 1 AND
2177 l_old_pro_reset_group_id <> pn_var_trx_rec.proration_reset_group_id THEN
2178 --l_last_partial_period = 'Y' THEN
2179 l_pro_invoice_flag := 'F';
2180 dbms_output.put_line(' l_pro_invoice_flag := F');
2181 ELSE
2182 l_pro_invoice_flag := 'N';
2183 --dbms_output.put_line(' Step1 l_curr_grp_date_year ='|| l_curr_grp_date_year);
2184 --dbms_output.put_line(' Step1 l_first_partial_year ='|| l_first_partial_year);
2185 --dbms_output.put_line(' Step1 l_pro_invoice_flag := N');
2186 END IF;
2187 END IF;
2188 */
2189
2190 /* commented above code as it will not take care of first year partial year
2191 followed with a another partial year Srini 09-AUG-2004
2192 IF pn_var_trx_rec.proration_rule IN ('FLY','FY') THEN
2193 IF (l_curr_grp_date_year <= l_first_partial_year AND
2194 l_last_partial_period_id <> pn_var_trx_rec.period_id) THEN
2195 IF l_old_pro_reset_group_id <> 0 AND
2196 pn_var_trx_rec.proration_reset_group_id <> 1 AND
2197 l_old_pro_reset_group_id <> pn_var_trx_rec.proration_reset_group_id THEN
2198 --l_last_partial_period = 'Y' THEN
2199 l_pro_invoice_flag := 'F';
2200 /* dbms_output.put_line(' l_pro_invoice_flag := F');
2201 ELSE
2202 l_pro_invoice_flag := 'N';
2203 --dbms_output.put_line(' Step1 l_curr_grp_date_year ='|| l_curr_grp_date_year);
2204 --dbms_output.put_line(' Step1 l_first_partial_year ='|| l_first_partial_year);
2205 --dbms_output.put_line(' Step1 l_pro_invoice_flag := N');
2206 END IF;
2207 ELSIF (l_curr_grp_date_year = l_first_partial_year AND
2208 l_last_partial_period_id = pn_var_trx_rec.period_id AND
2209 l_prv_partial_period = 'Y') THEN
2210 l_invoice_grp_dt := NULL;
2211 OPEN get_invoice_grp_dt (pn_var_trx_rec.var_rent_id
2212 ,pn_var_trx_rec.period_id
2213 ,l_min_grp_dt_364);
2214 FETCH get_invoice_grp_dt INTO l_invoice_grp_dt;
2215 IF get_invoice_grp_dt%NOTFOUND THEN
2216 CLOSE get_invoice_grp_dt;
2217 RAISE NO_DATA_FOUND;
2218 END IF;
2219 CLOSE get_invoice_grp_dt;
2220 IF pn_var_trx_rec.group_date = l_invoice_grp_dt THEN
2221 l_pro_invoice_flag := 'I';
2222 END IF;
2223 END IF;
2224 END IF;
2225
2226 /* dbms_output.put_line(' LY/FLY l_last_partial_period := '||l_last_partial_period);
2227 dbms_output.put_line(' LY/FLY l_last_partial_period_id := '||l_last_partial_period_id);
2228 dbms_output.put_line(' LY/FLY pn_var_trx_rec.group_date := '||pn_var_trx_rec.group_date);
2229 dbms_output.put_line(' LY/FLY l_old_pro_reset_group_id := '||l_old_pro_reset_group_id);
2230 dbms_output.put_line(' LY/FLY l_max_grp_dt := '||l_max_grp_dt);
2231 dbms_output.put_line(' Step1 LY/FLY l_curr_grp_date_year := '||l_curr_grp_date_year);
2232 dbms_output.put_line(' Step1 LY/FLY pn_var_trx_rec.period_id := '||pn_var_trx_rec.period_id);
2233
2234 IF (pn_var_trx_rec.proration_rule IN ('FLY','LY') AND
2235 l_last_partial_period_id = pn_var_trx_rec.period_id AND
2236 l_last_partial_period = 'Y' ) THEN
2237 IF pn_var_trx_rec.group_date = l_max_grp_dt THEN
2238 l_pro_invoice_flag := 'I';
2239 ELSIF l_old_pro_reset_group_id <> 0 AND
2240 pn_var_trx_rec.proration_reset_group_id <> 1 AND
2241 l_old_pro_reset_group_id <> pn_var_trx_rec.proration_reset_group_id THEN
2242 l_pro_invoice_flag := 'L';
2243
2244 IF pn_var_trx_rec.proration_rule = 'FLY' THEN
2245 l_fy_365_end_date := NULL;
2246 l_ly_365_start_date := NULL;
2247 l_fy_365_end_date := get_fy_365_end_date(p_var_rent_id => p_var_rent_id);
2248 l_ly_365_start_date := get_ly_365_start_date(p_var_rent_id => p_var_rent_id);
2249 IF l_ly_365_start_date < l_fy_365_end_date THEN
2250 l_pro_invoice_flag := 'F';
2251 END IF;
2252 END IF;
2253
2254 ELSE
2255 l_pro_invoice_flag := 'N';
2256 /*dbms_output.put_line(' Step2 l_pro_invoice_flag := N');
2257 END IF;--IF pn_var_trx_rec.group_date = l_max_grp_dt
2258 END IF;--IF pn_var_trx_rec.proration_rule IN ('FY','FLY')
2259
2260 pnp_debug_pkg.debug('Invoice Flag = '||l_pro_invoice_flag);
2261 /* dbms_output.put_line(' Invoice Flag = '||l_pro_invoice_flag);
2262
2263 ELSE -- Complete Year
2264 pnp_debug_pkg.debug('Complete Year');
2265 /* dbms_output.put_line('Complete Year '||pn_var_trx_rec.period_id );
2266 DBMS_OUTPUT.PUT_LINE(' Previous Partial Period :'||l_prv_partial_period);
2267 l_pro_invoice_flag := NULL;
2268 IF (pn_var_trx_rec.proration_rule IN ('FY','FLY')) AND
2269 l_prv_partial_period = 'Y' THEN
2270 l_invoice_grp_dt := NULL;
2271 OPEN get_invoice_grp_dt (pn_var_trx_rec.var_rent_id
2272 ,pn_var_trx_rec.period_id
2273 ,l_min_grp_dt_364);
2274 FETCH get_invoice_grp_dt INTO l_invoice_grp_dt;
2275 IF get_invoice_grp_dt%NOTFOUND THEN
2276 CLOSE get_invoice_grp_dt;
2277 RAISE NO_DATA_FOUND;
2278 END IF;
2279 CLOSE get_invoice_grp_dt;
2280 pnp_debug_pkg.debug('l_invoice_grp_date = ' || l_invoice_grp_dt);
2281 pnp_debug_pkg.debug('pn_var_trx_rec.group_date = ' || pn_var_trx_rec.group_date);
2282 /* dbms_output.put_line('l_invoice_grp_date = ' || l_invoice_grp_dt);
2283 dbms_output.put_line('pn_var_trx_rec.group_date = ' || pn_var_trx_rec.group_date);
2284 dbms_output.put_line('l_365_grp_dt = ' || l_365_grp_dt);
2285
2286 IF pn_var_trx_rec.group_date = l_invoice_grp_dt THEN
2287 l_pro_invoice_flag := 'I';
2288 ELSIF l_old_pro_reset_group_id <> 0 AND
2289 pn_var_trx_rec.group_date < l_invoice_grp_dt AND
2290 l_old_pro_reset_group_id <> pn_var_trx_rec.proration_reset_group_id THEN
2291 l_pro_invoice_flag := 'F';
2292 END IF;--IF pn_var_trx_rec.group_date = l_invoice_grp_dt
2293
2294 END IF;
2295 /* dbms_output.put_line(' l_pro_invoice_flag for FY or FLY = ' || l_pro_invoice_flag);
2296
2297 IF (pn_var_trx_rec.proration_rule IN ('LY','FLY')) AND
2298 pn_var_trx_rec.group_date >= l_365_grp_dt THEN
2299 l_invoice_grp_dt := NULL;
2300 OPEN get_invoice_grp_dt (pn_var_trx_rec.var_rent_id
2301 ,pn_var_trx_rec.period_id
2302 ,l_min_grp_dt_364);
2303 FETCH get_invoice_grp_dt INTO l_invoice_grp_dt;
2304 IF get_invoice_grp_dt%NOTFOUND THEN
2305 CLOSE get_invoice_grp_dt;
2306 RAISE NO_DATA_FOUND;
2307 END IF;
2308 CLOSE get_invoice_grp_dt;
2309 pnp_debug_pkg.debug('l_invoice_grp_date = ' || l_invoice_grp_dt);
2310 pnp_debug_pkg.debug('pn_var_trx_rec.group_date = ' || pn_var_trx_rec.group_date);
2311
2312 /* dbms_output.put_line(' Step1');
2313 dbms_output.put_line(' l_old_group_date:'|| l_old_group_date);
2314 dbms_output.put_line(' l_365_grp_dt:'|| l_365_grp_dt);
2315 dbms_output.put_line(' l_old_pro_reset_group_id:'|| l_old_pro_reset_group_id);
2316 dbms_output.put_line(' pn_var_trx_rec.proration_reset_group_id:'|| pn_var_trx_rec.proration_reset_group_id);
2317
2318 IF l_old_group_date >= l_365_grp_dt AND
2319 l_old_pro_reset_group_id <> 0 AND
2320 l_old_pro_reset_group_id <> pn_var_trx_rec.proration_reset_group_id THEN
2321 /* dbms_output.put_line(' Step2');
2322 --l_pro_invoice_flag := 'L';
2323 IF pn_var_trx_rec.proration_rule = 'FLY' THEN
2324 l_pro_invoice_flag := 'L';
2325 END IF;
2326 ELSE
2327 /* dbms_output.put_line(' Step3');
2328 IF l_pro_invoice_flag = 'N' THEN
2329 l_pro_invoice_flag := NULL;
2330 END IF;
2331 END IF;--IF pn_var_trx_rec.group_date = l_invoice_grp_dt
2332
2333 END IF;
2334 /* dbms_output.put_line(' l_pro_invoice_flag for LY or FLY = ' || l_pro_invoice_flag);
2335 pnp_debug_pkg.debug('Complete Year Invoice Flag = '||l_pro_invoice_flag);
2336
2337 END IF; --IF pn_var_trx_rec.partial_period = 'Y'
2338
2339 IF l_pro_invoice_flag IN ('N','I') THEN
2340
2341 UPDATE pn_var_transactions_all
2342 SET invoice_flag = l_pro_invoice_flag
2343 WHERE bkpt_start_date = pn_var_trx_rec.bkpt_start_date
2344 AND line_item_id = pn_var_trx_rec.line_item_id
2345 AND NVL(proration_reset_group_id,0) = NVL(pn_var_trx_rec.proration_reset_group_id, 0);
2346 --AND proration_reset_group_id = pn_var_trx_rec.proration_reset_group_id; Srini 11AUG2004
2347
2348 pnp_debug_pkg.debug('Current Updated for N,I = '||to_char(sql%rowcount));
2349 /* dbms_output.put_line(' UPDATE for N or I, SQL%ROWCOUNT := '||SQL%ROWCOUNT);
2350 ELSIF l_pro_invoice_flag IN ('F','L') THEN
2351 --IF pn_var_trx_rec.partial_period = 'Y' AND
2352 -- pn_var_trx_rec.proration_rule = 'FLY' THEN
2353 UPDATE pn_var_transactions_all
2354 SET invoice_flag = l_pro_invoice_flag
2355 WHERE group_date = l_old_group_date
2356 AND line_item_id = l_old_line_item_id
2357 AND NVL(proration_reset_group_id, 0) = NVL(l_old_pro_reset_group_id, 0);
2358 --AND proration_reset_group_id = l_old_pro_reset_group_id; Srini 11AUG2004
2359 pnp_debug_pkg.debug('Previous Updated for F,L = '||to_char(sql%rowcount));
2360 /* dbms_output.put_line(' UPDATE for F or L, SQL%ROWCOUNT := '||SQL%ROWCOUNT);
2361
2362
2363 --Following should not happen
2364 IF pn_var_trx_rec.proration_rule = 'LY' THEN
2365 UPDATE pn_var_transactions_all
2366 SET invoice_flag = NULL
2367 WHERE var_rent_id = p_var_rent_id
2368 AND invoice_flag = 'L';
2369 END IF;
2370
2371 --Following should not happen
2372 IF pn_var_trx_rec.proration_rule = 'FY' THEN
2373 UPDATE pn_var_transactions_all
2374 SET invoice_flag = NULL
2375 WHERE var_rent_id = p_var_rent_id
2376 AND invoice_flag = 'F';
2377 END IF;
2378
2379 IF pn_var_trx_rec.partial_period = 'Y' AND
2380 pn_var_trx_rec.proration_rule IN ('FLY','LY','FY') THEN
2381 UPDATE pn_var_transactions_all
2382 SET invoice_flag = 'N'
2383 WHERE bkpt_start_date = pn_var_trx_rec.bkpt_start_date
2384 AND line_item_id = pn_var_trx_rec.line_item_id
2385 AND NVL(proration_reset_group_id, 0) = NVL(pn_var_trx_rec.proration_reset_group_id, 0);
2386 --AND proration_reset_group_id = pn_var_trx_rec.proration_reset_group_id; Srini 11AUG2004
2387
2388 /* dbms_output.put_line(' Current Updated for N,I = '||to_char(sql%rowcount));
2389 pnp_debug_pkg.debug('Current Updated for N,I = '||to_char(sql%rowcount));
2390 END IF;
2391
2392 END IF;
2393
2394 --Srini 31AUG2004
2395 --For FLY, invoice_flag is not set to I for a overlapping first and last partial year
2396 IF pn_var_trx_rec.proration_rule = 'FLY' THEN
2397 l_fy_365_end_date := NULL;
2398 l_ly_365_start_date := NULL;
2399 l_fy_365_end_date := get_fy_365_end_date(p_var_rent_id => p_var_rent_id);
2400 l_ly_365_start_date := get_ly_365_start_date(p_var_rent_id => p_var_rent_id);
2401
2402 IF pn_var_trx_rec.bkpt_start_date = l_fy_365_end_date AND
2403 l_ly_365_start_date < l_fy_365_end_date THEN
2404 UPDATE pn_var_transactions_all
2405 SET invoice_flag = 'I'
2406 WHERE bkpt_start_date = pn_var_trx_rec.bkpt_start_date
2407 AND line_item_id = pn_var_trx_rec.line_item_id
2408 AND NVL(proration_reset_group_id, 0) = NVL(pn_var_trx_rec.proration_reset_group_id, 0);
2409 END IF;
2410
2411 /*
2412 --Srini 01SEP2004
2413 IF pn_var_trx_rec.bkpt_end_date = ADD_MONTHS(l_ly_365_start_date, 12) - 1 AND
2414 l_ly_365_start_date < l_fy_365_end_date THEN
2415 UPDATE pn_var_transactions_all
2416 SET invoice_flag = 'C'
2417 WHERE bkpt_start_date = pn_var_trx_rec.bkpt_start_date
2418 AND line_item_id = pn_var_trx_rec.line_item_id
2419 AND NVL(proration_reset_group_id, 0) = NVL(pn_var_trx_rec.proration_reset_group_id, 0);
2420 END IF;
2421
2422
2423 END IF;
2424
2425 END IF; --IF pn_var_trx_rec.proration_rule IN ('FY','LY','FLY','CYP','CYNP')
2426
2427 l_old_group_date := pn_var_trx_rec.group_date;
2428 l_old_grp_date_id := pn_var_trx_rec.grp_date_id;
2429 l_old_bkpt_start_date := pn_var_trx_rec.bkpt_start_date;
2430 l_old_bkpt_detail_id := pn_var_trx_rec.bkpt_detail_id;
2431 l_old_pro_reset_group_id := pn_var_trx_rec.proration_reset_group_id;
2432 l_sales_type_code := pn_var_trx_rec.sales_type_code;
2433 l_item_category_code := pn_var_trx_rec.item_category_code;
2434 l_old_period_id := pn_var_trx_rec.period_id;
2435 l_old_line_item_id := pn_var_trx_rec.line_item_id;
2436 l_old_reset_group_id := pn_var_trx_rec.reset_group_id;
2437 l_old_bkpt_rate := pn_var_trx_rec.bkpt_rate;
2438 l_old_partial_period := pn_var_trx_rec.partial_period;
2439
2440 END LOOP; -- pn_var_trex_rec end loop; */
2441 NULL;
2442 END update_ytd_bkpts;
2443
2444 /*===========================================================================+
2445 | PROCEDURE DETERMINE_RESET_FLAG
2446 |
2447 | DESCRIPTION
2448 |
2449 | This procedure will set the reset flag for a particular row in pn_var_transactions_all
2450 | table. This flag is then used to determine the summary for ytd breakpoints and
2451 | the cumulative sales. The rule being followed is that if the rates between the
2452 | current group and the previous group and different either in count or in number
2453 | then the reset flag is set to Y for the current group. This will set the summary
2454 | counter to 0 when we do ytd breakpoints and ytd sales.
2455 | SCOPE - PUBLIC
2456 |
2457 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2458 |
2459 | ARGUMENTS : IN:
2460 | P_VAR_RENT_ID
2461 | P_START_DATE
2462 |
2463 | OUT: X_RESET_FLAG
2464 |
2465 | MODIFICATION HISTORY
2466 |
2467 | 16-MAR-2003 graghuna o Created
2468 | 25-JUL-2003 cthangai o Added param period_id and line_item_id
2469 +===========================================================================*/
2470 PROCEDURE determine_reset_flag ( p_var_rent_id IN NUMBER,
2471 p_period_id IN NUMBER,
2472 p_item_category_code IN VARCHAR2,
2473 p_sales_type_code IN VARCHAR2,
2474 p_start_date IN DATE ,
2475 x_reset_flag OUT NOCOPY VARCHAR2)
2476 IS
2477
2478 Type rate_rec is RECORD (
2479 rate NUMBER );
2480
2481 TYPE rate_tbl is table of rate_rec index by binary_integer;
2482
2483 v_rate_tbl_1 rate_tbl;
2484 v_rate_tbl_2 rate_tbl;
2485 l_start_date DATE := NULL;
2486 l_end_date DATE := NULL;
2487 I INTEGER := 0;
2488 l_reset_flag VARCHAR2(1) := 'N';
2489 l_x VARCHAR2(2000) := 'N';
2490 /*cursor get_distinct_rates_cur( p_var_Rent_id NUMBER,
2491 p_start_date DATE,
2492 p_end_date DATE) IS
2493 SELECT a.bkpt_rate
2494 FROM pn_var_transactions_all a
2495 ,pn_var_lines_all b
2496 WHERE a.var_rent_id = p_var_rent_id
2497 AND a.period_id = NVL(p_period_id,a.period_id)
2498 AND a.line_item_id = b.line_item_id
2499 AND b.item_category_code = p_item_category_code
2500 AND b.sales_type_code = p_sales_type_code
2501 AND a.bkpt_start_date = NVL( p_start_date,a.bkpt_start_date)
2502 AND a.bkpt_end_date = NVL( p_end_date , a.bkpt_end_date)
2503 ORDER BY a.period_id,a.bkpt_start_date, a.prorated_grp_vol_start; /*25-JUL-03 Chris T*/
2504
2505 BEGIN
2506
2507 /*l_start_date := p_start_date;
2508 i := 0;
2509 FOR distinct_rates_rec in get_distinct_rates_cur (
2510 p_var_rent_id,
2511 l_start_date,
2512 l_end_date) LOOP
2513 i := i+1;
2514 v_rate_tbl_1(i).rate := distinct_rates_rec.bkpt_rate;
2515 END LOOP;
2516
2517 i:=0;
2518 l_start_date:= NULL;
2519 l_end_date := p_start_date-1;
2520 FOR distinct_rates_rec in get_distinct_rates_cur (
2521 p_var_rent_id,
2522 l_start_date,
2523 l_end_date) LOOP
2524 i := i+1;
2525 v_rate_tbl_2(i).rate := distinct_rates_rec.bkpt_rate;
2526 END LOOP;
2527
2528 l_x := 'v_rate_tbl_1.count = ' || v_rate_tbl_1.count;
2529 pnp_debug_pkg.debug(l_x);
2530 l_x := 'v_rate_tbl_2.count = ' || v_rate_tbl_2.count;
2531 pnp_debug_pkg.debug(l_x);
2532
2533 IF v_rate_tbl_1.count <> v_rate_tbl_2.count THEN
2534 x_reset_flag := 'Y';
2535 ELSE
2536 FOR i in v_rate_tbl_1.first .. v_rate_tbl_1.last LOOP
2537
2538 l_x := 'i= '|| i;
2539 pnp_debug_pkg.debug(l_x);
2540 l_x := 'v_rate_tbl_1= '|| v_rate_tbl_1(i).rate;
2541 pnp_debug_pkg.debug(l_x);
2542 l_x := 'v_rate_tbl_2= '|| v_rate_tbl_2(i).rate;
2543 pnp_debug_pkg.debug(l_x);
2544 IF v_rate_tbl_1(i).rate <> v_rate_tbl_2(i).rate THEN
2545 x_reset_flag := 'Y';
2546 END IF;
2547
2548 END LOOP;
2549 END IF;*/ NULL;
2550 END determine_reset_flag;
2551
2552 /*===========================================================================+
2553 | PROCEDURE update_blended_period
2554 |
2555 | DESCRIPTION
2556 |
2557 | SCOPE : PUBLIC
2558 |
2559 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2560 |
2561 | ARGUMENTS : IN:
2562 | P_VAR_RENT_ID
2563 |
2564 | OUT:
2565 |
2566 | MODIFICATION HISTORY
2567 |
2568 | 24-JUL-2003 CTHANGAI o Created
2569 +===========================================================================*/
2570 PROCEDURE update_blended_period ( p_var_rent_id IN NUMBER)
2571 IS
2572
2573 /*CURSOR get_prorated_grp_vol_cur IS
2574 SELECT period_id
2575 ,line_item_id
2576 ,reset_group_id
2577 ,bkpt_rate
2578 ,ROUND(SUM(prorated_grp_vol_start),2) blend_period_start
2579 ,ROUND(SUM(prorated_grp_vol_end),2) blend_period_end
2580 FROM pn_var_transactions_all
2581 WHERE var_rent_id = p_var_rent_id
2582 GROUP BY period_id
2583 ,line_item_id
2584 ,reset_group_id
2585 ,bkpt_rate;
2586
2587 CURSOR get_prorated_grp_vol_np_cur IS --Chris.T. 11FEB2004
2588 SELECT period_id
2589 ,line_item_id
2590 ,reset_group_id
2591 ,bkpt_rate
2592 ,prorated_grp_vol_start blend_period_start
2593 ,prorated_grp_vol_end blend_period_end
2594 FROM pn_var_transactions_all
2595 WHERE var_rent_id = p_var_rent_id;*/
2596
2597 l_proration_rule VARCHAR2(10) := NULL; --Chris.T. 11FEB2004
2598
2599 BEGIN
2600
2601 --Get Proration Rule for VR
2602 /* l_proration_rule := pn_var_rent_pkg.get_proration_rule(p_var_rent_id => p_var_rent_id);
2603 pnp_debug_pkg.log(' Proration Rule = '||l_proration_rule);
2604
2605 IF l_proration_rule = 'NP' THEN --Chris.T. 11FEB2004
2606 FOR get_prorated_grp_vol_rec IN get_prorated_grp_vol_np_cur
2607 LOOP
2608
2609 UPDATE pn_var_transactions_all
2610 SET blended_period_vol_start = get_prorated_grp_vol_rec.blend_period_start
2611 ,blended_period_vol_end = get_prorated_grp_vol_rec.blend_period_end
2612 WHERE var_rent_id = p_var_rent_id
2613 AND period_id = get_prorated_grp_vol_rec.period_id
2614 AND line_item_id = get_prorated_grp_vol_rec.line_item_id
2615 AND reset_group_id = get_prorated_grp_vol_rec.reset_group_id
2616 AND bkpt_rate = get_prorated_grp_vol_rec.bkpt_rate;
2617
2618 pnp_debug_pkg.debug('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
2619 ||to_char(get_prorated_grp_vol_rec.period_id)||'/'
2620 ||to_char(get_prorated_grp_vol_rec.line_item_id)||'/'
2621 ||to_char(get_prorated_grp_vol_rec.reset_group_id)||'/'
2622 ||to_char(get_prorated_grp_vol_rec.bkpt_rate));
2623 pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
2624 pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
2625
2626 END LOOP;
2627
2628 ELSE
2629
2630 FOR get_prorated_grp_vol_rec IN get_prorated_grp_vol_cur
2631 LOOP
2632
2633 UPDATE pn_var_transactions_all
2634 SET blended_period_vol_start = get_prorated_grp_vol_rec.blend_period_start
2635 ,blended_period_vol_end = get_prorated_grp_vol_rec.blend_period_end
2636 WHERE var_rent_id = p_var_rent_id
2637 AND period_id = get_prorated_grp_vol_rec.period_id
2638 AND line_item_id = get_prorated_grp_vol_rec.line_item_id
2639 AND reset_group_id = get_prorated_grp_vol_rec.reset_group_id
2640 AND bkpt_rate = get_prorated_grp_vol_rec.bkpt_rate;
2641
2642 pnp_debug_pkg.debug('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
2643 ||to_char(get_prorated_grp_vol_rec.period_id)||'/'
2644 ||to_char(get_prorated_grp_vol_rec.line_item_id)||'/'
2645 ||to_char(get_prorated_grp_vol_rec.reset_group_id)||'/'
2646 ||to_char(get_prorated_grp_vol_rec.bkpt_rate));
2647 pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
2648 pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
2649
2650 END LOOP;
2651
2652 END IF; --IF l_proration_rule = 'NP' THEN --Chris.T. 11FEB2004*/
2653 NULL;
2654
2655 END update_blended_period;
2656
2657 PROCEDURE update_blended_period (p_var_rent_id IN NUMBER,
2658 p_start_date IN DATE,
2659 p_proration_rule IN VARCHAR2)
2660 IS
2661
2662 l_partial_period_id NUMBER;
2663 l_partial_period_start_date DATE;
2664 l_partial_period_end_date DATE;
2665 l_p_parital_period_flag VARCHAR2(1);
2666 l_complete_period_id NUMBER;
2667 l_complete_period_start_date DATE;
2668 l_complete_period_end_date DATE;
2669 l_c_parital_period_flag VARCHAR2(1);
2670 l_date DATE;
2671
2672 l_months_in_group NUMBER(12,2);
2673 l_months_in_complete_period NUMBER(12,2);
2674 l_months_in_partial_period NUMBER(12,2);
2675 l_total_months NUMBER(12,2);
2676 l_period_proration_factor NUMBER;
2677 l_group_proration_factor NUMBER;
2678 l_period_from NUMBER(12,2);
2679 l_period_to NUMBER(12,2);
2680 l_group_from NUMBER(12,2);
2681 l_group_to NUMBER(12,2);
2682 l_rate_count NUMBER;
2683 l_invg_freq_code NUMBER;
2684
2685 CURSOR first_partial_period_cur IS
2686 SELECT period_id,start_date,end_date,partial_period
2687 FROM pn_var_periods_all
2688 WHERE var_rent_id = p_var_rent_id
2689 AND start_Date = p_start_date ; -- p_start date = VR_agreement_start_date
2690
2691 CURSOR first_complete_period_cur (p_date DATE) IS
2692 SELECT period_id,Start_date,end_date,partial_period
2693 FROM pn_var_periods_all
2694 WHERE var_rent_id = p_var_rent_id
2695 AND start_Date = p_date ; -- p_date = partial_period_end_date +1;
2696
2697 /*CURSOR annual_blended_bkpts_cur (p_end_date DATE) IS
2698 SELECT proration_reset_group_id
2699 ,a.bkpt_rate
2700 ,b.item_category_code
2701 ,b.sales_type_code
2702 ,min(a.group_date) min_group_date
2703 ,max(a.group_date) max_group_date
2704 ,sum(a.prorated_grp_vol_start) sum_grp_vol_start
2705 ,sum(a.prorated_grp_vol_end) sum_grp_vol_end
2706 FROM pn_var_transactions_all a
2707 ,pn_var_lines_all b
2708 WHERE a.var_rent_id = p_var_rent_id
2709 AND a.group_date <= p_end_date -- end date of the complete period
2710 AND a.line_item_id = b.line_item_id
2711 GROUP BY a.proration_reset_group_id,
2712 a.bkpt_rate
2713 ,b.item_category_code
2714 ,b.sales_type_code;
2715
2716 --Chris.T. 15MAR2004 --Cursor To fetch other than first partial and first complete period
2717 CURSOR get_prorated_grp_vol_cur (ip_end_date DATE) IS
2718 SELECT period_id
2719 ,line_item_id
2720 ,reset_group_id
2721 ,bkpt_rate
2722 --,min(group_date) min_group_date
2723 --,max(group_date) max_group_date
2724 ,min(bkpt_start_date) min_group_date
2725 ,max(bkpt_end_date) max_group_date
2726 ,ROUND(SUM(prorated_grp_vol_start),2) blend_period_start
2727 ,ROUND(SUM(prorated_grp_vol_end),2) blend_period_end
2728 FROM pn_var_transactions_all
2729 WHERE var_rent_id = p_var_rent_id
2730 AND group_date > ip_end_date -- end date of the complete period
2731 GROUP BY period_id
2732 ,line_item_id
2733 ,reset_group_id
2734 ,bkpt_rate;
2735
2736 /*Chris.T. 16MAR2004 - Commented as Cursor not used
2737 CURSOR get_dates_for_reset_grp ( p_reset_group_id NUMBER) IS
2738 SELECT min(group_date)
2739 ,max(group_date)
2740 FROM pn_var_transactions_all
2741 WHERE var_rent_id = p_var_rent_id
2742 AND proration_reset_group_id = p_reset_group_id;
2743 */
2744 --Srini Start 10-Jun-2004
2745
2746 l_vr_term_dt DATE;
2747 l_grp_end_dt DATE;
2748 v_grp_st_dt DATE;
2749 v_grp_end_dt DATE;
2750
2751 /*CURSOR process_grp_dates_cur(p_min_grp_dt DATE,
2752 p_max_grp_dt DATE,
2753 p_pr_re_grp_id NUMBER,
2754 p_bkpt_rate NUMBER,
2755 p_vr_term_dt DATE)
2756 IS SELECT *
2757 FROM pn_var_transactions_all trx
2758 WHERE trx.var_rent_id = p_var_rent_id
2759 AND trx.group_date >= p_min_grp_dt
2760 AND trx.group_date <= p_max_grp_dt
2761 AND trx.proration_reset_group_id = p_pr_re_grp_id
2762 AND trx.bkpt_rate = p_bkpt_rate
2763 AND EXISTS (SELECT 'Partial Month'
2764 FROM pn_var_grp_dates_all grp
2765 WHERE grp.grp_date_id = trx.grp_date_id
2766 AND grp.grp_end_date > p_vr_term_dt);
2767
2768 CURSOR process_first_cynp_cur(p_var_rent_id NUMBER,
2769 p_bkpt_st_dt DATE,
2770 p_period_from NUMBER,
2771 p_period_to NUMBER)
2772 IS SELECT *
2773 FROM pn_var_transactions_all trx
2774 WHERE trx.var_rent_id = p_var_rent_id
2775 AND trx.bkpt_start_date = p_bkpt_st_dt
2776 AND trx.blended_period_vol_start = p_period_from
2777 AND trx.blended_period_vol_end = p_period_to;
2778
2779 CURSOR process_last_cynp_cur(p_var_rent_id NUMBER,
2780 p_bkpt_end_dt DATE,
2781 p_period_from NUMBER,
2782 p_period_to NUMBER)
2783 IS SELECT *
2784 FROM pn_var_transactions_all trx
2785 WHERE trx.var_rent_id = p_var_rent_id
2786 AND trx.bkpt_end_date = p_bkpt_end_dt
2787 AND trx.blended_period_vol_start = p_period_from
2788 AND trx.blended_period_vol_end = p_period_to;*/
2789
2790 l_counter NUMBER(12,6);
2791 l_counter1 NUMBER(12,6);
2792 l_counter2 NUMBER(12,6);
2793 l_date1 DATE;
2794 l_st_dt1 DATE;
2795 l_end_dt1 DATE;
2796 l_cynp_days NUMBER;
2797 l_tot_days NUMBER;
2798 l_mth_cynp_days NUMBER;
2799 l_mth_tot_days NUMBER;
2800 l_cnt NUMBER;
2801 l_mths_bet NUMBER(12,6);
2802 l_bkpt_days NUMBER;
2803 l_bkpt_tot_days NUMBER;
2804 l_new_period_from NUMBER(12,2);
2805 l_new_period_to NUMBER(12,2);
2806
2807 --Srini End 10-Jun-2004
2808
2809 BEGIN
2810 /*
2811 SELECT termination_date
2812 INTO l_vr_term_dt
2813 FROM pn_var_rents_all
2814 WHERE var_rent_id = p_var_rent_id;
2815 pnp_debug_pkg.log(' Variable Rent Termination Date:'||l_vr_term_dt);
2816 pnp_debug_pkg.debug(' Variable Rent Termination Date:'||l_vr_term_dt);
2817
2818 OPEN first_partial_period_cur;
2819 FETCH first_partial_period_cur INTO
2820 l_partial_period_id ,
2821 l_partial_period_start_date ,
2822 l_partial_period_end_date ,
2823 l_p_parital_period_flag ;
2824 CLOSE first_partial_period_cur;
2825
2826 l_date := l_partial_period_end_date + 1;
2827 OPEN first_complete_period_cur(l_date);
2828 FETCH first_complete_period_cur INTO
2829 l_complete_period_id ,
2830 l_complete_period_start_date ,
2831 l_complete_period_end_date ,
2832 l_c_parital_period_flag ;
2833 CLOSE first_complete_period_cur;
2834
2835 l_months_in_complete_period :=round(MONTHS_BETWEEN(l_complete_period_end_date + 1,
2836 l_complete_period_start_date));
2837 l_months_in_partial_period :=round(MONTHS_BETWEEN(l_partial_period_end_date + 1,
2838 l_partial_period_start_date));
2839
2840 IF p_proration_rule = 'CYP' THEN --Chris.T. 09JUN2004
2841 l_total_months := l_months_in_complete_period + l_months_in_partial_period;
2842 ELSE
2843 --Start Srini 10SEP2004
2844 --Based on discussions with Sean, SSpar,Liam and Kathleen,
2845 --constant 12 months will be replaced with actual months in period after first partial period
2846 --l_total_months := 12; --Complete period treated as a 12 month
2847 l_total_months := l_months_in_complete_period;
2848 BEGIN
2849 --Findout invoicing freq
2850 SELECT DECODE(invg_freq_code, 'MON', 1,
2851 'QTR', 3,
2852 'SA', 6,
2853 'YR', 12,
2854 NULL)
2855 INTO l_invg_freq_code
2856 FROM pn_var_rent_dates_all
2857 WHERE var_rent_id = p_var_rent_id;
2858 /*DBMS_OUTPUT.PUT_LINE(' l_invg_freq_code:'||l_invg_freq_code);
2859 EXCEPTION
2860 WHEN OTHERS THEN
2861 l_invg_freq_code := 1;
2862 END;
2863 --End Srini 10SEP2004
2864 END IF;
2865
2866 --pnp_debug_pkg.log('Proration Rule = '|| p_proration_rule);
2867 --pnp_debug_pkg.log('Months in complete period = '|| l_months_in_complete_period);
2868 --pnp_debug_pkg.log('Months in partial period = '|| l_months_in_partial_period);
2869 --pnp_debug_pkg.log('Total period/Months = '|| l_total_months);
2870 --pnp_debug_pkg.log('l_complete_period_end_date = '|| l_complete_period_end_date);
2871
2872
2873 FOR annual_blended_bkpts_rec IN
2874 annual_blended_bkpts_cur (l_complete_period_end_date)
2875 LOOP
2876
2877 --Srini Start 10-Jun-2004
2878
2879 BEGIN
2880 SELECT grp_end_date
2881 INTO l_grp_end_dt
2882 FROM pn_var_grp_dates_all
2883 WHERE var_rent_id = p_var_rent_id
2884 AND group_date = annual_blended_bkpts_rec.max_group_date;
2885 EXCEPTION
2886 WHEN OTHERS THEN
2887 l_grp_end_dt := annual_blended_bkpts_rec.max_group_date;
2888 END;
2889
2890 IF l_vr_term_dt < l_grp_end_dt THEN
2891 l_months_in_group := ROUND(MONTHS_BETWEEN(l_vr_term_dt + 1,
2892 annual_blended_bkpts_rec.min_group_date), 2);
2893 ELSE
2894 l_months_in_group := ROUND(MONTHS_BETWEEN(l_grp_end_dt + 1,
2895 annual_blended_bkpts_rec.min_group_date), 2);
2896
2897 END IF;
2898 --Srini End 10-Jun-2004
2899 /* DBMS_OUTPUT.PUT_LINE(' min group date = '||annual_blended_bkpts_rec.min_group_date);
2900 DBMS_OUTPUT.PUT_LINE(' max group date = '||annual_blended_bkpts_rec.max_group_date);
2901 DBMS_OUTPUT.PUT_LINE(' l_vr_term_dt = '||l_vr_term_dt);
2902 DBMS_OUTPUT.PUT_LINE(' l_months_in_group = '||l_months_in_group);
2903
2904 --Start Srini 14SEP2004
2905 IF p_proration_rule = 'CYNP' THEN
2906 l_counter := 0;
2907 l_counter1 := 0;
2908 l_counter2 := 0;
2909 l_date1 := l_partial_period_start_date;
2910 l_cynp_days := (l_complete_period_end_date - l_complete_period_start_date) + 1;
2911 l_tot_days := (TO_DATE('31-12-'||TO_CHAR(l_complete_period_start_date, 'YYYY'), 'DD-MM-YYYY')
2912 - l_complete_period_start_date) + 1;
2913
2914 l_cnt := ROUND(MONTHS_BETWEEN(LAST_DAY(l_complete_period_end_date),
2915 TO_DATE('01-'||TO_CHAR(l_partial_period_start_date, 'MM-YYYY'), 'DD-MM-YYYY')));
2916
2917 FOR l_counter IN 0 .. l_cnt - 1
2918 LOOP
2919 l_st_dt1 := l_date1;
2920 l_end_dt1 := LAST_DAY(l_date1);
2921
2922 IF l_end_dt1 > l_complete_period_end_date THEN
2923 l_end_dt1 := l_complete_period_end_date;
2924 END IF;
2925
2926 l_mth_cynp_days:= (l_end_dt1 - l_st_dt1) + 1;
2927 l_mth_tot_days := (LAST_DAY(l_end_dt1) - TO_DATE('01-'||TO_CHAR(l_st_dt1,'MM-YYYY'), 'DD-MM-YYYY')) + 1;
2928
2929 l_counter1 := l_counter1 + (l_mth_cynp_days/l_mth_tot_days);
2930
2931 IF l_date1 >= l_complete_period_start_date THEN
2932 l_counter2 := l_counter2 + (l_mth_cynp_days/l_mth_tot_days);
2933 END IF;
2934
2935 l_date1 := l_end_dt1 + 1;
2936 /*DBMS_OUTPUT.PUT_LINE(' l_mth_cynp_days:'||l_mth_cynp_days);
2937 DBMS_OUTPUT.PUT_LINE(' l_mth_tot_days:'||l_mth_tot_days);
2938 DBMS_OUTPUT.PUT_LINE(' l_counter:'||l_counter);
2939 DBMS_OUTPUT.PUT_LINE(' l_counter1:'||l_counter1);
2940 DBMS_OUTPUT.PUT_LINE(' l_counter2:'||l_counter2);
2941 DBMS_OUTPUT.PUT_LINE(' l_date1:'||l_date1);
2942
2943 END LOOP;
2944
2945 /* DBMS_OUTPUT.PUT_LINE(' l_counter1:'||l_counter1);
2946 DBMS_OUTPUT.PUT_LINE(' l_counter2:'||l_counter2);
2947 DBMS_OUTPUT.PUT_LINE(' l_tot_days:'||l_tot_days);
2948 DBMS_OUTPUT.PUT_LINE(' l_cynp_days:'||l_cynp_days);
2949
2950 --IF p_proration_rule = 'CYNP' THEN
2951 l_period_from := ((annual_blended_bkpts_rec.sum_grp_vol_start/ l_counter1) * 12) * (l_cynp_days/l_tot_days);
2952 l_period_to := ((annual_blended_bkpts_rec.sum_grp_vol_end/ l_counter1) * 12) * (l_cynp_days/l_tot_days);
2953 l_group_from := (l_period_from/l_counter1)*l_invg_freq_code;
2954 l_group_to := (l_period_to/l_counter1)*l_invg_freq_code;
2955 ELSIF p_proration_rule = 'CYP' THEN
2956 l_period_from := annual_blended_bkpts_rec.sum_grp_vol_start;
2957 l_period_to := annual_blended_bkpts_rec.sum_grp_vol_end;
2958 END IF;
2959 --End Srini 14SEP2004
2960
2961 IF p_proration_rule = 'CYNP' THEN
2962 /* DBMS_OUTPUT.PUT_LINE(' l_period_from = '||l_period_from);
2963 DBMS_OUTPUT.PUT_LINE(' l_period_to = '||l_period_to);
2964 --pnp_debug_pkg.log('updating for CYNP');
2965
2966 UPDATE pn_var_transactions_all trx
2967 SET trx.pr_grp_blended_vol_start = l_group_from
2968 ,trx.pr_grp_blended_vol_end = l_group_to
2969 ,trx.blended_period_vol_start = l_period_from
2970 ,trx.blended_period_vol_end = l_period_to
2971 ,trx.invoice_flag = 'P'
2972 WHERE var_rent_id = p_var_rent_id
2973 AND group_date >= annual_blended_bkpts_rec.min_group_date
2974 AND group_date <= annual_blended_bkpts_rec.max_group_date
2975 AND proration_reset_group_id = annual_blended_bkpts_rec.proration_reset_group_id
2976 AND bkpt_rate = annual_blended_bkpts_rec.bkpt_rate
2977 AND bkpt_end_date <= l_complete_period_end_date;
2978
2979 /* DBMS_OUTPUT.PUT_LINE('Number Of Records Updated: '||SQL%ROWCOUNT);
2980
2981 --Process ,if any, partial months in first partial and complete year
2982 FOR i IN process_first_cynp_cur(p_var_rent_id,
2983 l_partial_period_start_date,
2984 l_period_from,
2985 l_period_to)
2986 LOOP
2987 l_bkpt_days := (i.bkpt_end_date - i.bkpt_start_date) + 1;
2988 l_bkpt_tot_days := (((ADD_MONTHS(i.bkpt_start_date, l_invg_freq_code) - 1) - i.bkpt_start_date) + 1);
2989 l_mths_bet := l_bkpt_days/l_bkpt_tot_days;
2990
2991 l_group_from := (l_period_from/l_counter1) * l_invg_freq_code * l_mths_bet;
2992 l_group_to := (l_period_to/l_counter1) * l_invg_freq_code * l_mths_bet;
2993
2994 /* DBMS_OUTPUT.PUT_LINE('Partial l_bkpt_days = '||l_bkpt_days);
2995 DBMS_OUTPUT.PUT_LINE('Partial l_bkpt_tot_days = '||l_bkpt_tot_days);
2996 DBMS_OUTPUT.PUT_LINE('Partial l_mths_bet = '||l_mths_bet);
2997 DBMS_OUTPUT.PUT_LINE('Partial l_group_from = '||l_group_from);
2998 DBMS_OUTPUT.PUT_LINE('Partial l_group_to = '||l_group_to);
2999 DBMS_OUTPUT.PUT_LINE('Partial i.transaction_id = '||i.transaction_id);
3000
3001 UPDATE pn_var_transactions_all trx
3002 SET trx.PR_GRP_BLENDED_VOL_START = l_group_from
3003 ,trx.PR_GRP_BLENDED_VOL_END = l_group_to
3004 ,trx.BLENDED_PERIOD_VOL_START = l_period_from
3005 ,trx.BLENDED_PERIOD_VOL_END = l_period_to
3006 ,trx.invoice_flag = 'P'
3007 WHERE transaction_id = i.transaction_id;
3008 /* DBMS_OUTPUT.PUT_LINE('Number Of First Partial Records Updated: '||SQL%ROWCOUNT);
3009
3010 END LOOP;
3011
3012 FOR i IN process_last_cynp_cur(p_var_rent_id,
3013 l_complete_period_end_date,
3014 l_period_from,
3015 l_period_to)
3016 LOOP
3017 l_bkpt_days := (i.bkpt_end_date - i.bkpt_start_date) + 1;
3018 l_bkpt_tot_days := (((ADD_MONTHS(i.bkpt_start_date, l_invg_freq_code) - 1) - i.bkpt_start_date) + 1);
3019 l_mths_bet := l_bkpt_days/l_bkpt_tot_days;
3020
3021 l_group_from := (l_period_from/l_counter1) * l_invg_freq_code * l_mths_bet;
3022 l_group_to := (l_period_to/l_counter1) * l_invg_freq_code * l_mths_bet;
3023 /* DBMS_OUTPUT.PUT_LINE('Partial l_bkpt_days = '||l_bkpt_days);
3024 DBMS_OUTPUT.PUT_LINE('Partial l_bkpt_tot_days = '||l_bkpt_tot_days);
3025 DBMS_OUTPUT.PUT_LINE('Partial l_mths_bet = '||l_mths_bet);
3026 DBMS_OUTPUT.PUT_LINE('Partial l_group_from = '||l_group_from);
3027 DBMS_OUTPUT.PUT_LINE('Partial l_group_to = '||l_group_to);
3028 DBMS_OUTPUT.PUT_LINE('Partial i.transaction_id = '||i.transaction_id);
3029
3030 UPDATE pn_var_transactions_all trx
3031 SET trx.PR_GRP_BLENDED_VOL_START = l_group_from
3032 ,trx.PR_GRP_BLENDED_VOL_END = l_group_to
3033 ,trx.BLENDED_PERIOD_VOL_START = l_period_from
3034 ,trx.BLENDED_PERIOD_VOL_END = l_period_to
3035 ,trx.invoice_flag = 'P'
3036 WHERE transaction_id = i.transaction_id;
3037 /* DBMS_OUTPUT.PUT_LINE('Number Of Last Partial Records Updated: '||SQL%ROWCOUNT);
3038
3039 END LOOP;
3040
3041 BEGIN
3042 SELECT SUM(pr_grp_blended_vol_start), SUM(pr_grp_blended_vol_end)
3043 INTO l_new_period_from, l_new_period_to
3044 FROM pn_var_transactions_all
3045 WHERE var_rent_id = p_var_rent_id
3046 AND group_date >= annual_blended_bkpts_rec.min_group_date
3047 AND group_date <= annual_blended_bkpts_rec.max_group_date
3048 AND proration_reset_group_id = annual_blended_bkpts_rec.proration_reset_group_id
3049 AND bkpt_rate = annual_blended_bkpts_rec.bkpt_rate
3050 AND bkpt_end_date <= l_complete_period_end_date
3051 AND blended_period_vol_start = l_period_from
3052 AND blended_period_vol_end = l_period_to;
3053
3054 /* DBMS_OUTPUT.PUT_LINE(' l_new_period_from = '||l_new_period_from);
3055 DBMS_OUTPUT.PUT_LINE(' l_new_period_to = '||l_new_period_to);
3056
3057 UPDATE pn_var_transactions_all trx
3058 SET trx.blended_period_vol_start = l_new_period_from
3059 ,trx.blended_period_vol_end = l_new_period_to
3060 ,trx.invoice_flag = 'P'
3061 WHERE trx.var_rent_id = p_var_rent_id
3062 AND trx.group_date >= annual_blended_bkpts_rec.min_group_date
3063 AND trx.group_date <= annual_blended_bkpts_rec.max_group_date
3064 AND trx.proration_reset_group_id = annual_blended_bkpts_rec.proration_reset_group_id
3065 AND trx.bkpt_rate = annual_blended_bkpts_rec.bkpt_rate
3066 AND trx.bkpt_end_date <= l_complete_period_end_date
3067 AND trx.blended_period_vol_start = l_period_from
3068 AND trx.blended_period_vol_end = l_period_to;
3069 /* DBMS_OUTPUT.PUT_LINE('Number Of CYNP Records Updated: '||SQL%ROWCOUNT);
3070 EXCEPTION
3071 WHEN OTHERS THEN
3072 /* DBMS_OUTPUT.PUT_LINE(' Exception - l_new_period_from = '||l_new_period_from);
3073 DBMS_OUTPUT.PUT_LINE(' Exception - l_new_period_to = '||l_new_period_to);
3074 null;
3075 END;
3076
3077 ELSIF p_proration_rule = 'CYP' THEN
3078 --pnp_debug_pkg.log('updating for CYP');
3079 UPDATE pn_var_transactions_all
3080 SET blended_period_vol_start = l_period_from
3081 ,blended_period_vol_end = l_period_to
3082 ,invoice_flag = 'P' --denote first partial or complete period
3083 WHERE var_rent_id = p_var_rent_id
3084 AND group_date >= annual_blended_bkpts_rec.min_group_date
3085 AND group_date <= annual_blended_bkpts_rec.max_group_date
3086 AND proration_reset_group_id = annual_blended_bkpts_rec.proration_reset_group_id
3087 AND bkpt_rate = annual_blended_bkpts_rec.bkpt_rate;
3088 /* DBMS_OUTPUT.PUT_LINE(' Number Of Records Updated For CYP: '||SQL%ROWCOUNT);
3089 DBMS_OUTPUT.PUT_LINE(' l_group_from: '||l_group_from);
3090 DBMS_OUTPUT.PUT_LINE(' l_group_to: '||l_group_to);
3091 DBMS_OUTPUT.PUT_LINE(' l_period_from: '||l_period_from);
3092 DBMS_OUTPUT.PUT_LINE(' l_period_to: '||l_period_to);
3093 END IF;
3094
3095 END LOOP;
3096
3097 --To handle other than first partial and first complete period
3098 FOR get_prorated_grp_vol_rec IN get_prorated_grp_vol_cur (l_complete_period_end_date)
3099 LOOP
3100
3101 l_months_in_group := NULL;
3102 l_period_from := NULL;
3103 l_period_to := NULL;
3104 l_group_from := NULL;
3105 l_group_to := NULL;
3106
3107 l_months_in_group := ROUND(MONTHS_BETWEEN(get_prorated_grp_vol_rec.max_group_date + 1,
3108 get_prorated_grp_vol_rec.min_group_date), 1);
3109
3110 IF p_proration_rule = 'CYNP' THEN
3111 pnp_debug_pkg.debug('updating CYNP');
3112 --pnp_debug_pkg.log('updating for CYNP');
3113
3114 l_period_from := get_prorated_grp_vol_rec.blend_period_start;
3115 l_period_to := get_prorated_grp_vol_rec.blend_period_end;
3116 l_group_from := (l_period_from/l_months_in_group)*l_invg_freq_code;
3117 l_group_to := (l_period_to/l_months_in_group)*l_invg_freq_code;
3118
3119 /* DBMS_OUTPUT.PUT_LINE(' l_months_in_group: '||l_months_in_group);
3120 DBMS_OUTPUT.PUT_LINE(' l_group_from: '||l_group_from);
3121 DBMS_OUTPUT.PUT_LINE(' l_group_to: '||l_group_to);
3122 DBMS_OUTPUT.PUT_LINE(' l_period_from: '||l_period_from);
3123 DBMS_OUTPUT.PUT_LINE(' l_period_to: '||l_period_to);
3124
3125 UPDATE pn_var_transactions_all
3126 SET blended_period_vol_start = l_period_from
3127 ,blended_period_vol_end = l_period_to
3128 ,PR_GRP_BLENDED_VOL_START = l_group_from
3129 ,PR_GRP_BLENDED_VOL_END = l_group_to
3130 WHERE var_rent_id = p_var_rent_id
3131 AND period_id = get_prorated_grp_vol_rec.period_id
3132 AND line_item_id = get_prorated_grp_vol_rec.line_item_id
3133 AND reset_group_id = get_prorated_grp_vol_rec.reset_group_id
3134 AND bkpt_rate = get_prorated_grp_vol_rec.bkpt_rate;
3135
3136 --Process ,if any, partial months after first complete year
3137 FOR i IN process_last_cynp_cur(p_var_rent_id,
3138 l_vr_term_dt,
3139 l_period_from,
3140 l_period_to)
3141 LOOP
3142 l_bkpt_days := (i.bkpt_end_date - i.bkpt_start_date) + 1;
3143 l_bkpt_tot_days := (((ADD_MONTHS(i.bkpt_start_date, l_invg_freq_code) - 1) - i.bkpt_start_date) + 1);
3144 l_mths_bet := l_bkpt_days/l_bkpt_tot_days;
3145
3146 l_group_from := (l_period_from/l_months_in_group) * l_invg_freq_code * l_mths_bet;
3147 l_group_to := (l_period_to/l_months_in_group) * l_invg_freq_code * l_mths_bet;
3148 /* DBMS_OUTPUT.PUT_LINE(' Partial l_group_from = '||l_group_from);
3149 DBMS_OUTPUT.PUT_LINE(' Partial l_group_to = '||l_group_to);
3150 DBMS_OUTPUT.PUT_LINE(' Partial i.transaction_id = '||i.transaction_id);
3151
3152 UPDATE pn_var_transactions_all trx
3153 SET trx.PR_GRP_BLENDED_VOL_START = l_group_from
3154 ,trx.PR_GRP_BLENDED_VOL_END = l_group_to
3155 ,trx.BLENDED_PERIOD_VOL_START = l_period_from
3156 ,trx.BLENDED_PERIOD_VOL_END = l_period_to
3157 WHERE transaction_id = i.transaction_id;
3158 /* DBMS_OUTPUT.PUT_LINE(' Number Of Partial Records Updated: '||SQL%ROWCOUNT);
3159
3160 END LOOP;
3161
3162 ELSIF p_proration_rule = 'CYP' THEN
3163 pnp_debug_pkg.debug('updating CYP');
3164 --pnp_debug_pkg.log('updating for CYP');
3165
3166 UPDATE pn_var_transactions_all
3167 SET blended_period_vol_start = get_prorated_grp_vol_rec.blend_period_start
3168 ,blended_period_vol_end = get_prorated_grp_vol_rec.blend_period_end
3169 WHERE var_rent_id = p_var_rent_id
3170 AND period_id = get_prorated_grp_vol_rec.period_id
3171 AND line_item_id = get_prorated_grp_vol_rec.line_item_id
3172 AND reset_group_id = get_prorated_grp_vol_rec.reset_group_id
3173 AND bkpt_rate = get_prorated_grp_vol_rec.bkpt_rate;
3174
3175 END IF;
3176
3177 pnp_debug_pkg.debug('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
3178 ||to_char(get_prorated_grp_vol_rec.period_id)
3179 ||'/'||to_char(get_prorated_grp_vol_rec.line_item_id)
3180 ||'/'||to_char(get_prorated_grp_vol_rec.reset_group_id)
3181 ||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3182 pnp_debug_pkg.debug('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
3183 pnp_debug_pkg.debug('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
3184 pnp_debug_pkg.debug('Pr Grp Blended Period Vol Start = '||l_group_from);
3185 pnp_debug_pkg.debug('Pr Grp Blended Period Vol End = '||l_group_to);
3186 --pnp_debug_pkg.log('Period_id/line_item_id/reset_grp_id/bkpt_rate = '
3187 --||to_char(get_prorated_grp_vol_rec.period_id)
3188 --||'/'||to_char(get_prorated_grp_vol_rec.line_item_id)
3189 --||'/'||to_char(get_prorated_grp_vol_rec.reset_group_id)
3190 --||'/'||to_char(get_prorated_grp_vol_rec.bkpt_rate));
3191 --pnp_debug_pkg.log('Blended Period Vol Start = '||to_char(get_prorated_grp_vol_rec.blend_period_start));
3192 --pnp_debug_pkg.log('Blended Period Vol End = '||to_char(get_prorated_grp_vol_rec.blend_period_end));
3193 --pnp_debug_pkg.log('Pr Grp Blended Period Vol Start = '||l_group_from);
3194 --pnp_debug_pkg.log('Pr Grp Blended Period Vol End = '||l_group_to);
3195
3196 END LOOP;*/
3197 NULL;
3198
3199 END update_blended_period;
3200
3201 /*===========================================================================+
3202 | PROCEDURE copy_var_rent_agreement
3203 |
3204 | DESCRIPTION
3205 |
3206 | SCOPE : PUBLIC
3207 |
3208 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3209 |
3210 | ARGUMENTS : IN:
3211 |
3212 | OUT:
3213 |
3214 | MODIFICATION HISTORY
3215 |
3216 | 06-SEP-2003 graghuna o Created
3217 +===========================================================================*/
3218 procedure copy_var_rent_agreement (
3219 p_old_var_rent_id IN NUMBER,
3220 p_start_date IN DATE DEFAULT NULL,
3221 p_end_date IN DATE DEFAULT NULL,
3222 p_proration_rule IN VARCHAR2 DEFAULT 'STD',
3223 p_create_periods IN VARCHAR2 DEFAULT 'N',
3224 x_var_rent_id OUT NOCOPY NUMBER,
3225 x_var_rent_num OUT NOCOPY VARCHAR2) IS
3226
3227 CURSOR var_rent_cur IS
3228 SELECT *
3229 FROM pn_var_rents_all
3230 WHERE var_rent_id = p_old_var_rent_id;
3231
3232 CURSOR var_rent_dates_cur IS
3233 SELECT *
3234 FROM pn_var_rent_dates_all
3235 WHERE var_rent_id = p_old_var_rent_id;
3236
3237 l_length NUMBER;
3238 l_instr NUMBER;
3239 l_return_status VARCHAR2(32767);
3240 l_return_msg VARCHAR2(32767);
3241 l_old_var_rent_id NUMBER;
3242 l_effective_date DATE;
3243 BEGIN
3244
3245
3246 FOR var_rent_rec in var_rent_cur LOOP
3247
3248 var_rent_rec.commencement_date := NVL(p_start_date,var_rent_Rec.commencement_date);
3249 var_rent_rec.termination_Date := NVL(p_end_date,var_rent_Rec.termination_date);
3250 var_rent_rec.proration_rule := NVL(p_proration_rule,var_rent_Rec.proration_rule);
3251 var_rent_rec.chg_cal_var_rent_id := NULL;
3252 l_length := LENGTH(var_rent_rec.rent_num);
3253 l_instr := INSTR(var_rent_rec.rent_num,'-');
3254 IF l_instr = 0 THEN
3255 var_rent_rec.rent_num := var_rent_rec.rent_num||'-1';
3256 ELSE
3257 var_rent_rec.rent_num := SUBSTR(var_rent_rec.rent_num,1,l_instr)||
3258 '-'||to_number(SUBSTR(var_rent_rec.rent_num,l_instr+1,10))+1;
3259 end if;
3260
3261
3262
3263 FOR var_rent_dates_rec in var_rent_dates_cur LOOP
3264 var_rent_dates_rec.effective_date := NULL;
3265 var_rent_dates_rec.use_gl_calendar := NULL;
3266 var_rent_dates_rec.gl_period_set_name := NULL;
3267 var_rent_dates_rec.period_type := NULL;
3268 var_rent_dates_rec.year_start_date := NULL;
3269 pn_var_rents_pkg.create_var_rent_agreement (
3270 p_pn_var_rents_rec => var_rent_rec,
3271 p_var_rent_dates_rec => var_rent_dates_rec,
3272 p_create_periods => p_create_periods,
3273 x_var_rent_id => x_var_rent_id,
3274 x_var_rent_num => x_var_rent_num);
3275 l_old_var_rent_id := var_rent_rec.var_rent_id;
3276 l_effective_date := var_rent_dates_rec.effective_date;
3277 EXIT;
3278 END LOOP;
3279 EXIT;
3280 END LOOP;
3281
3282
3283 END copy_var_rent_agreement;
3284
3285 Procedure process_calendar_change (
3286 p_var_rent_id IN NUMBER ,
3287 p_old_var_rent_id IN NUMBER ,
3288 p_effective_date IN DATE,
3289 x_return_status OUT NOCOPY VARCHAR2,
3290 x_return_message OUT NOCOPY VARCHAR2
3291 )
3292
3293 IS
3294
3295 CURSOR period_exists_cur IS
3296 SELECT 'x' period_exists
3297 FROM dual
3298 WHERE EXISTS ( SELECT period_id
3299 FROM pn_var_periods_all
3300 WHERE var_rent_id = p_var_rent_id);
3301
3302 CURSOR old_var_rent_cur IS
3303 SELECT chg_cal_var_rent_id
3304 FROM pn_var_rents_all
3305 WHERE var_rent_id = p_var_rent_id;
3306
3307 l_period_exists BOOLEAN := FALSE;
3308 l_defaults_exists BOOLEAN := FALSE;
3309 l_old_var_rent_id NUMBER;
3310 l_line_exists NUMBER :=0 ;
3311 l_constr_exists NUMBER :=0 ;
3312
3313 user_exception exception;
3314
3315 BEGIN
3316
3317
3318 -- Check if periods have been genereated for the new
3319 -- variable rent agreement. IF there are no periods
3320 -- generated then raise an error. When called from a form periods will always
3321 -- be generated before this process is called.
3322
3323 FOR periods_exists_rec in period_exists_cur LOOP
3324 l_period_exists := TRUE;
3325 EXIT;
3326 END LOOP;
3327
3328 IF NOT(l_period_exists) THEN
3329 x_return_message := 'PN_PERIODS_NOT_FOUND';
3330 Raise user_exception;
3331 END IF;
3332
3333 -- Check if there are defaults. IF defaults are present then
3334 -- copy them over and call create_default_lines and populate_transactions
3335 -- This will take care of generating period level data.
3336
3337 l_old_var_rent_id := p_old_var_rent_id;
3338
3339 l_line_exists := pn_var_defaults_pkg.find_if_line_defaults_exist (
3340 p_var_rent_id => l_old_var_rent_Id) ;
3341
3342 /* dbms_output.put_line('l_line_exists = '|| l_line_exists);
3343 dbms_output.put_line('l_old_var_rent_id = '|| l_old_var_rent_id); */
3344 IF l_line_exists > 0 THEN
3345 /* dbms_output.put_line('copy_line_defaults'); */
3346
3347 pn_var_chg_cal_pkg.copy_line_defaults (
3348 p_old_var_rent_id=> l_old_var_rent_id,
3349 p_new_var_rent_id => p_var_rent_id,
3350 p_effective_date => p_effective_date);
3351
3352 pn_var_defaults_pkg.create_default_lines (
3353 x_var_rent_id => p_var_rent_id);
3354 ELSE
3355 pn_var_chg_cal_pkg.copy_parent_lines (
3356 x_var_rent_id => p_var_rent_id,
3357 x_chg_var_rent_id => l_old_var_rent_id);
3358
3359
3360
3361 END IF;
3362
3363 l_constr_exists := pn_var_defaults_pkg.find_if_constr_defaults_exist (
3364 p_var_rent_id => l_old_var_rent_Id) ;
3365 IF l_constr_exists > 0 THEN
3366 pn_var_chg_cal_pkg.copy_constr_defaults (
3367 p_old_var_rent_id => p_var_rent_id ,
3368 p_new_var_rent_id => l_old_var_rent_id,
3369 p_effective_date => p_effective_date);
3370
3371 pn_var_defaults_pkg.create_default_constraints (
3372 x_var_rent_id => p_var_rent_id);
3373 END IF;
3374
3375 pn_var_chg_cal_pkg.copy_parent_volhist (
3376 x_var_rent_id => p_var_rent_id ,
3377 x_chg_var_rent_id => l_old_var_rent_id);
3378
3379 pn_var_chg_cal_pkg.populate_transactions (
3380 p_var_rent_id => p_var_rent_id);
3381
3382
3383 create_credit_invoice ( p_var_rent_id => p_var_rent_id,
3384 p_effective_date => p_effective_date);
3385
3386 EXCEPTION
3387
3388 When user_exception THEN
3389 x_return_status := FND_API.G_RET_STS_ERROR;
3390 When OTHERS THEN
3391 x_return_status := FND_API.G_RET_STS_ERROR;
3392
3393 END process_calendar_change ;
3394
3395 Function get_last_complete_period_id ( p_var_rent_id IN NUMBER)
3396 RETURN NUMBER
3397 IS
3398
3399 CURSOR last_period_cur IS
3400 SELECT max(period_id) period_id
3401 FROM pn_var_periods_all
3402 WHERE var_rent_id = p_var_rent_id
3403 AND partial_period = 'N';
3404
3405 l_last_complete_period_id NUMBER;
3406 BEGIN
3407
3408 FOR last_period_rec in last_period_cur LOOP
3409 l_last_complete_period_id := last_period_rec.period_id;
3410 EXIT;
3411 END LOOP;
3412
3413 RETURN l_last_complete_period_id;
3414
3415 END ;
3416
3417 Function get_fy_365_end_date ( p_var_rent_id IN NUMBER)
3418 RETURN DATE
3419 IS
3420
3421 CURSOR get_group_date(ip_grp_date DATE) IS
3422 SELECT group_date
3423 FROM pn_var_grp_dates_all
3424 WHERE var_rent_id = p_var_rent_id
3425 AND ip_grp_date between grp_start_date and grp_end_date;
3426
3427
3428 CURSOR get_365_end_cur IS
3429 SELECT min(grp_start_date) + 364 enddate
3430 FROM pn_var_grp_dates_all
3431 WHERE var_rent_id = p_var_rent_id ;
3432
3433
3434 l_fy_365_end_date DATE;
3435 l_date DATE;
3436 BEGIN
3437
3438 FOR get_365_end_rec in get_365_end_cur LOOP
3439 l_fy_365_end_date := get_365_end_rec.enddate;
3440 EXIT;
3441 END LOOP;
3442
3443 FOR get_group_date_rec in get_group_date(l_fy_365_end_date) LOOP
3444 l_date := get_group_date_rec.group_date;
3445 EXIT;
3446 END LOOP;
3447 RETURN l_date;
3448
3449 END ;
3450
3451
3452 Function get_ly_365_start_date ( p_var_rent_id IN NUMBER)
3453 RETURN DATE
3454 IS
3455
3456 CURSOR get_group_date(ip_grp_date DATE) IS
3457 SELECT group_date
3458 FROM pn_var_grp_dates_all
3459 WHERE var_rent_id = p_var_rent_id
3460 AND ip_grp_date between grp_start_date and grp_end_date;
3461
3462
3463 CURSOR get_365_start_cur IS
3464 SELECT max(grp_end_date) - 364 startdate
3465 FROM pn_var_grp_dates_all
3466 WHERE var_rent_id = p_var_rent_id ;
3467
3468
3469 L_ly_365_start_date DATE;
3470 l_date DATE;
3471 BEGIN
3472
3473 FOR get_365_start_rec in get_365_start_cur LOOP
3474 l_ly_365_start_date := get_365_start_rec.startdate;
3475 EXIT;
3476 END LOOP;
3477
3478 FOR get_group_date_rec in get_group_date(l_ly_365_start_date) LOOP
3479 l_date := get_group_date_rec.group_date;
3480 EXIT;
3481 END LOOP;
3482 RETURN l_date;
3483
3484 END ;
3485
3486
3487 PROCEDURE copy_line_defaults(p_old_var_rent_id NUMBER
3488 ,p_new_var_rent_id NUMBER
3489 ,p_effective_date DATE )
3490
3491 IS
3492
3493 CURSOR source_cur IS
3494 SELECT *
3495 FROM pn_var_line_defaults_all
3496 WHERE var_rent_id = p_old_var_rent_id
3497 AND line_end_date > p_effective_date;
3498
3499 CURSOR bkhd_source_cur (ip_line_default_id NUMBER) IS
3500 SELECT *
3501 FROM pn_var_bkhd_defaults_all
3502 WHERE line_default_id = ip_line_default_id
3503 AND var_rent_id = p_old_var_rent_id
3504 AND bkhd_end_date > p_effective_date;
3505
3506 CURSOR bkdt_source_cur (ip_bkhd_default_id NUMBER) IS
3507 SELECT *
3508 FROM pn_var_bkdt_defaults_all
3509 WHERE var_rent_id = p_old_var_rent_id
3510 AND bkhd_default_id = ip_bkhd_default_id
3511 AND bkdt_end_date > p_effective_date;
3512
3513
3514 l_rowid VARCHAR2(32767);
3515 l_start_date DATE;
3516 l_line_default_id NUMBER ;
3517 l_line_num NUMBER;
3518 l_bkhd_default_id NUMBER ;
3519 l_bkhd_detail_num NUMBER;
3520 l_bkdt_default_id NUMBER ;
3521 l_bkdt_detail_num NUMBER;
3522 BEGIN
3523
3524 FOR source_rec in source_cur LOOP
3525 /* dbms_output.put_line('line defaults'); */
3526 l_rowid := NULL;
3527 l_start_date :=NULL;
3528 l_line_default_id := NULL;
3529 l_line_num := NULL;
3530 IF source_Rec.line_start_date < p_effective_date THEN
3531 l_start_date := p_effective_date ;
3532 ELSE
3533 l_start_date := source_rec.line_start_date;
3534 END IF;
3535 PN_VAR_LINE_DEFAULTS_PKG.INSERT_ROW (
3536 X_ROWID => l_rowid,
3537 X_LINE_DEFAULT_ID => l_line_default_id,
3538 X_LINE_NUM => l_line_num,
3539 X_VAR_RENT_ID => p_new_var_rent_id,
3540 X_SALES_TYPE_CODE => source_rec.sales_type_code,
3541 X_ITEM_CATEGORY_CODE => source_rec.item_category_code ,
3542 X_LINE_TEMPLATE_ID => source_rec.line_template_id,
3543 X_AGREEMENT_TEMPLATE_ID => source_rec.agreement_template_id,
3544 X_LINE_START_DATE => l_start_date,
3545 X_LINE_END_DATE => source_rec.line_end_date,
3546 X_PROCESSED_FLAG => NULL,
3547 X_CREATION_DATE => sysdate,
3548 X_CREATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
3549 X_LAST_UPDATE_DATE => sysdate,
3550 X_LAST_UPDATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
3551 X_LAST_UPDATE_LOGIN => NVL(FND_PROFILE.VALUE('LOGIN_ID'),1),
3552 X_ORG_ID => source_rec.org_id,
3553 X_ATTRIBUTE_CATEGORY => source_rec.ATTRIBUTE_CATEGORY,
3554 X_ATTRIBUTE1 => source_rec.ATTRIBUTE1,
3555 X_ATTRIBUTE2 => source_rec.ATTRIBUTE2,
3556 X_ATTRIBUTE3 => source_rec.ATTRIBUTE3,
3557 X_ATTRIBUTE4 => source_rec.ATTRIBUTE4,
3558 X_ATTRIBUTE5 => source_rec.ATTRIBUTE5,
3559 X_ATTRIBUTE6 => source_rec.ATTRIBUTE6,
3560 X_ATTRIBUTE7 => source_rec.ATTRIBUTE7,
3561 X_ATTRIBUTE8 => source_rec.ATTRIBUTE8,
3562 X_ATTRIBUTE9 => source_rec.ATTRIBUTE9,
3563 X_ATTRIBUTE10 => source_rec.ATTRIBUTE10,
3564 X_ATTRIBUTE11 => source_rec.ATTRIBUTE11,
3565 X_ATTRIBUTE12 => source_rec.ATTRIBUTE12,
3566 X_ATTRIBUTE13 => source_rec.ATTRIBUTE13,
3567 X_ATTRIBUTE14 => source_rec.ATTRIBUTE14,
3568 X_ATTRIBUTE15 => source_rec.ATTRIBUTE15);
3569
3570 FOR bkhd_source_rec in bkhd_source_cur(source_rec.line_default_id)
3571 LOOP
3572 /* dbms_output.put_line('bkhd_defaults defaults'); */
3573 l_rowid := NULL;
3574 l_bkhd_default_id := NULL;
3575 l_bkhd_detail_num := NULL;
3576 IF bkhd_Source_rec.bkhd_start_date < p_effective_date THEN
3577 l_start_date := p_effective_date;
3578 ELSE
3579 l_start_date := bkhd_source_rec.bkhd_start_date;
3580 END IF;
3581 PN_VAR_BKHD_DEFAULTS_PKG.INSERT_ROW (
3582 X_ROWID => l_rowid,
3583 X_BKHD_DEFAULT_ID => l_bkhd_default_id,
3584 X_BKHD_DETAIL_NUM => l_bkhd_detail_num,
3585 X_LINE_DEFAULT_ID => l_line_default_id,
3586 X_BKPT_HEAD_TEMPLATE_ID => bkhd_source_rec.bkpt_head_template_id,
3587 X_AGREEMENT_TEMPLATE_ID => bkhd_source_rec.agreement_template_id,
3588 X_BKHD_START_DATE => l_start_date,
3589 X_BKHD_END_DATE => bkhd_source_rec.bkhd_end_date,
3590 X_BREAK_TYPE => bkhd_source_rec.break_type,
3591 X_BASE_RENT_TYPE => bkhd_source_rec.base_rent_type,
3592 X_NATURAL_BREAK_RATE => bkhd_source_rec.natural_break_rate,
3593 X_BASE_RENT => bkhd_source_rec.base_rent,
3594 X_BREAKPOINT_TYPE => bkhd_source_rec.breakpoint_type,
3595 X_BREAKPOINT_LEVEL => bkhd_source_rec.breakpoint_level,
3596 X_PROCESSED_FLAG => NULL,
3597 X_VAR_RENT_ID => p_new_var_rent_id,
3598 X_CREATION_DATE => sysdate,
3599 X_CREATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
3600 X_LAST_UPDATE_DATE => sysdate,
3601 X_LAST_UPDATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
3602 X_LAST_UPDATE_LOGIN => NVL(FND_PROFILE.VALUE('LOGIN_ID'),1),
3603 X_ORG_ID => source_rec.org_id,
3604 X_ATTRIBUTE_CATEGORY => bkhd_source_rec.ATTRIBUTE_CATEGORY,
3605 X_ATTRIBUTE1 => bkhd_source_rec.ATTRIBUTE1,
3606 X_ATTRIBUTE2 => bkhd_source_rec.ATTRIBUTE2,
3607 X_ATTRIBUTE3 => bkhd_source_rec.ATTRIBUTE3,
3608 X_ATTRIBUTE4 => bkhd_source_rec.ATTRIBUTE4,
3609 X_ATTRIBUTE5 => bkhd_source_rec.ATTRIBUTE5,
3610 X_ATTRIBUTE6 => bkhd_source_rec.ATTRIBUTE6,
3611 X_ATTRIBUTE7 => bkhd_source_rec.ATTRIBUTE7,
3612 X_ATTRIBUTE8 => bkhd_source_rec.ATTRIBUTE8,
3613 X_ATTRIBUTE9 => bkhd_source_rec.ATTRIBUTE9,
3614 X_ATTRIBUTE10 => bkhd_source_rec.ATTRIBUTE10,
3615 X_ATTRIBUTE11 => bkhd_source_rec.ATTRIBUTE11,
3616 X_ATTRIBUTE12 => bkhd_source_rec.ATTRIBUTE12,
3617 X_ATTRIBUTE13 => bkhd_source_rec.ATTRIBUTE13,
3618 X_ATTRIBUTE14 => bkhd_source_rec.ATTRIBUTE14,
3619 X_ATTRIBUTE15 => bkhd_source_rec.ATTRIBUTE15);
3620
3621 FOR bkdt_source_rec in bkdt_source_cur(bkhd_source_rec.bkhd_default_id)
3622 LOOP
3623
3624 /* dbms_output.put_line('bkdt defaults'); */
3625 l_bkdt_default_id := NULL;
3626 l_bkhd_detail_num := NULL;
3627 l_rowid := NULL;
3628 IF bkdt_source_rec.bkdt_start_date < p_effective_date THEN
3629 l_start_date := p_effective_date;
3630 ELSE
3631 l_start_date := bkdt_source_rec.bkdt_start_date;
3632 END IF;
3633 PN_VAR_BKDT_DEFAULTS_PKG.INSERT_ROW (
3634 X_ROWID => l_rowid,
3635 X_BKDT_DEFAULT_ID => l_bkhd_default_id,
3636 X_BKDT_DETAIL_NUM => l_bkdt_detail_num,
3637 X_BKHD_DEFAULT_ID => l_bkhd_default_id,
3638 X_BKDT_START_DATE => l_start_date,
3639 X_BKDT_END_DATE => bkdt_source_rec.bkdt_end_date,
3640 X_PERIOD_BKPT_VOL_START => bkdt_source_rec.period_bkpt_vol_start,
3641 X_PERIOD_BKPT_VOL_END => bkdt_source_rec.period_bkpt_vol_end,
3642 X_GROUP_BKPT_VOL_START => bkdt_source_rec.group_bkpt_vol_start,
3643 X_GROUP_BKPT_VOL_END => bkdt_source_rec.group_bkpt_vol_end,
3644 X_BKPT_RATE => bkdt_source_rec.bkpt_rate,
3645 X_PROCESSED_FLAG => NULL,
3646 X_VAR_RENT_ID => p_new_var_rent_id,
3647 X_CREATION_DATE => sysdate,
3648 X_CREATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
3649 X_LAST_UPDATE_DATE => sysdate,
3650 X_LAST_UPDATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
3651 X_LAST_UPDATE_LOGIN => NVL(FND_PROFILE.VALUE('LOGIN_ID'),1),
3652 X_ORG_ID => source_rec.org_id,
3653 X_ANNUAL_BASIS_AMOUNT => bkdt_source_rec.annual_basis_amount,
3654 X_ATTRIBUTE_CATEGORY => bkdt_source_rec.ATTRIBUTE_CATEGORY,
3655 X_ATTRIBUTE1 => bkdt_source_rec.ATTRIBUTE1,
3656 X_ATTRIBUTE2 => bkdt_source_rec.ATTRIBUTE2,
3657 X_ATTRIBUTE3 => bkdt_source_rec.ATTRIBUTE3,
3658 X_ATTRIBUTE4 => bkdt_source_rec.ATTRIBUTE4,
3659 X_ATTRIBUTE5 => bkdt_source_rec.ATTRIBUTE5,
3660 X_ATTRIBUTE6 => bkdt_source_rec.ATTRIBUTE6,
3661 X_ATTRIBUTE7 => bkdt_source_rec.ATTRIBUTE7,
3662 X_ATTRIBUTE8 => bkdt_source_rec.ATTRIBUTE8,
3663 X_ATTRIBUTE9 => bkdt_source_rec.ATTRIBUTE9,
3664 X_ATTRIBUTE10 => bkdt_source_rec.ATTRIBUTE10,
3665 X_ATTRIBUTE11 => bkdt_source_rec.ATTRIBUTE11,
3666 X_ATTRIBUTE12 => bkdt_source_rec.ATTRIBUTE12,
3667 X_ATTRIBUTE13 => bkdt_source_rec.ATTRIBUTE13,
3668 X_ATTRIBUTE14 => bkdt_source_rec.ATTRIBUTE14,
3669 X_ATTRIBUTE15 => bkdt_source_rec.ATTRIBUTE15
3670 );
3671 END LOOP;
3672 END LOOP;
3673
3674 END LOOP;
3675 END copy_line_defaults;
3676
3677 procedure copy_constr_defaults (
3678 p_old_var_rent_id in NUMBER,
3679 p_new_var_rent_id in NUMBER,
3680 p_effective_date in DATE
3681 ) IS
3682
3683 l_consrowid VARCHAR2(18) := NULL;
3684 l_consDefId NUMBER := NULL;
3685 l_consNum NUMBER := 0;
3686 l_rowid VARCHAR2(32767);
3687 l_start_date DATE ;
3688
3689 cursor c_get_consdef is
3690 select * from pn_var_constr_defaults_all
3691 where var_rent_id = p_old_var_rent_id
3692 AND constr_end_date > p_effective_date;
3693
3694 BEGIN
3695
3696 FOR c_crec IN c_get_consdef LOOP
3697
3698 IF c_crec.constr_start_date < p_effective_date THEN
3699 l_start_date := p_effective_date;
3700 ELSE
3701 l_start_date := c_crec.constr_start_date;
3702 END IF;
3703
3704 l_rowid := NULL;
3705 l_consdefid := NULL;
3706 l_consnum := NULL;
3707 pn_var_constr_defaults_pkg.insert_row (
3708 X_ROWID => l_rowid,
3709 X_CONSTR_DEFAULT_ID => l_consDefid,
3710 X_CONSTR_DEFAULT_NUM => l_consNum,
3711 X_VAR_RENT_ID => p_new_var_rent_id,
3712 X_AGREEMENT_TEMPLATE_ID => c_crec.agreement_template_id,
3713 X_CONSTR_TEMPLATE_ID => c_crec.constr_template_id ,
3714 X_CONSTR_START_DATE => l_start_date,
3715 X_CONSTR_END_DATE => c_crec.CONSTR_END_DATE,
3716 X_CONSTR_CAT_CODE => c_crec.CONSTR_CAT_CODE,
3717 X_TYPE_CODE => c_crec.type_code,
3718 X_AMOUNT => c_crec.amount,
3719 X_CREATION_DATE => sysdate,
3720 X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
3721 X_LAST_UPDATE_DATE => sysdate,
3722 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
3723 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('LOGIN_ID'),0),
3724 X_ORG_ID => c_crec.ORG_ID,
3725 X_ATTRIBUTE_CATEGORY => c_crec.ATTRIBUTE_CATEGORY,
3726 X_ATTRIBUTE1 => c_crec.ATTRIBUTE1,
3727 X_ATTRIBUTE2 => c_crec.ATTRIBUTE2,
3728 X_ATTRIBUTE3 => c_crec.ATTRIBUTE3,
3729 X_ATTRIBUTE4 => c_crec.ATTRIBUTE4,
3730 X_ATTRIBUTE5 => c_crec.ATTRIBUTE5,
3731 X_ATTRIBUTE6 => c_crec.ATTRIBUTE6,
3732 X_ATTRIBUTE7 => c_crec.ATTRIBUTE7,
3733 X_ATTRIBUTE8 => c_crec.ATTRIBUTE8,
3734 X_ATTRIBUTE9 => c_crec.ATTRIBUTE9,
3735 X_ATTRIBUTE10 => c_crec.ATTRIBUTE10,
3736 X_ATTRIBUTE11 => c_crec.ATTRIBUTE11,
3737 X_ATTRIBUTE12 => c_crec.ATTRIBUTE12,
3738 X_ATTRIBUTE13 => c_crec.ATTRIBUTE13,
3739 X_ATTRIBUTE14 => c_crec.ATTRIBUTE14,
3740 X_ATTRIBUTE15 => c_crec.ATTRIBUTE15
3741 );
3742
3743 END LOOP;
3744
3745 END copy_constr_defaults;
3746
3747 PROCEDURE create_credit_invoice ( p_var_rent_id NUMBER,
3748 p_effective_date DATE)
3749 IS
3750
3751 CURSOR inv_grp_dates_cur (ip_date DATE) IS
3752 SELECT invoice_date,
3753 inv_start_date
3754 ,inv_end_date
3755 ,inv_schedule_date
3756 FROM pn_var_grp_dates_all
3757 WHERE var_rent_id = p_var_rent_id
3758 AND ip_date between inv_start_date and inv_end_date;
3759
3760 CURSOR invoices_cur (ip_invoice_date DATE) IS
3761 SELECT decode(actual_Exp_code ,'Y',NVL(actual_invoiced_amount,0),0) invoiced_amt
3762 ,invoice_date
3763 FROM pn_var_rent_inv_all
3764 WHERE var_rent_id = p_var_rent_id
3765 AND invoice_date >= ip_invoice_date;
3766
3767 CURSOR max_invoice_date_cur Is
3768 SELECT max(invoice_date) invoice_date
3769 FROM pn_var_grp_dates_all
3770 WHERE var_rent_id = p_var_rent_id;
3771
3772 CURSOR get_periods_cur(ip_date DATE) Is
3773
3774 SELECT period_id
3775 FROM pn_var_periods_all
3776 WHERE var_rent_id = p_var_rent_id
3777 AND ip_date between start_date and end_date ;
3778
3779 CURSOR get_adjust_num ( ip_period_id NUMBER , ip_invoice_date DATE) IS
3780 SELECT max(adjust_num) adjust_num
3781 FROM pn_var_rent_inv_all
3782 WHERE period_id = ip_period_id
3783 AND invoice_date = ip_invoice_date;
3784
3785 l_proration_factor NUMBER;
3786 l_invoice_date DATE;
3787 l_invoice_create_date DATE;
3788 l_period_id NUMBER;
3789 l_rent_inv_id NUMBER;
3790 l_credit_amount NUMBER;
3791 l_rowid VARCHAR2(32767);
3792 l_adjust_num NUMBER;
3793
3794 CURSOR get_invoice_date_cur IS
3795 SELECT invoice_date
3796 from pn_var_grp_dates_all
3797 where var_rent_id = p_var_rent_id
3798 and p_effective_date between inv_start_date and inv_end_date ;
3799
3800 BEGIN
3801
3802 -- If the effective date lies between the invoice start_date and
3803 -- invoice end date, prorate the $$ for that invoice period. Otherwise
3804 -- include the entire $$ amount.
3805
3806 FOR inv_grp_date_rec in inv_grp_dates_cur(p_effective_date) LOOP
3807
3808 l_proration_factor := (p_effective_date - inv_grp_date_rec.inv_start_date)/
3809 (inv_grp_date_rec.inv_end_date - inv_grp_date_rec.inv_start_date);
3810
3811 l_invoice_date := inv_grp_date_rec.invoice_date;
3812 EXIT;
3813 END LOOP;
3814
3815 FOR invoices_rec in invoices_cur(l_invoice_date) LOOP
3816 IF invoices_rec.invoice_date = l_invoice_date THEN
3817 l_credit_amount := NVL(l_credit_amount,0) + (nvl(invoices_rec.invoiced_amt,0) *l_proration_factor );
3818 ELSE
3819 l_credit_amount := NVL(l_credit_amount,0) + nvl(invoices_rec.invoiced_amt ,0) ;
3820 END IF;
3821 END LOOP;
3822
3823
3824 FOR inv_grp_date_rec in inv_grp_dates_cur(sysdate) LOOP
3825 l_invoice_create_date := inv_grp_date_rec.invoice_date;
3826 EXIT;
3827 END LOOP;
3828
3829 IF l_invoice_create_date IS NULL THEN
3830 FOR max_invoice_date_rec in max_invoice_date_cur LOOP
3831 l_invoice_create_date := max_invoice_date_rec.invoice_date;
3832 EXIT;
3833 END LOOP;
3834 END IF;
3835
3836 IF l_invoice_create_date IS NOT NULL AND
3837 NVL(l_credit_amount,0) <> 0 THEN
3838 FOR periods_rec in get_periods_cur(l_invoice_create_date) LOOP
3839 l_period_id := periods_rec.period_id;
3840 EXIT;
3841 END LOOP;
3842
3843 FOR get_adjust_rec IN get_adjust_num ( l_period_id , l_invoice_create_date) LOOP
3844 l_adjust_num := get_adjust_rec.adjust_num;
3845 END LOOP;
3846 l_adjust_num := nvl(l_adjust_num,0) + 1;
3847 l_rent_inv_id := null;
3848 l_rowid := null;
3849 PN_VAR_RENT_INV_PKG.INSERT_ROW (
3850 X_ROWID => l_rowid,
3851 X_VAR_RENT_INV_ID => l_rent_inv_id,
3852 X_ADJUST_NUM => l_adjust_num,
3853 X_INVOICE_DATE => l_invoice_create_date,
3854 X_FOR_PER_RENT => NULL,
3855 X_TOT_ACT_VOL => NULL,
3856 X_ACT_PER_RENT => NULL,
3857 X_CONSTR_ACTUAL_RENT => NULL,
3858 X_ABATEMENT_APPL => NULL,
3859 X_REC_ABATEMENT => NULL,
3860 X_REC_ABATEMENT_OVERRIDE => NULL,
3861 X_NEGATIVE_RENT => NULL,
3862 X_ACTUAL_INVOICED_AMOUNT => round(l_credit_amount,2),
3863 X_PERIOD_ID => l_period_id,
3864 X_VAR_RENT_ID => p_var_rent_id,
3865 X_FORECASTED_TERM_STATUS => 'N',
3866 X_VARIANCE_TERM_STATUS => 'N',
3867 X_ACTUAL_TERM_STATUS => 'N',
3868 X_FORECASTED_EXP_CODE => 'N',
3869 X_VARIANCE_EXP_CODE => 'N',
3870 X_ACTUAL_EXP_CODE => 'N',
3871 X_COMMENTS => null,
3872 X_ATTRIBUTE_CATEGORY => null,
3873 X_ATTRIBUTE1 => null,
3874 X_ATTRIBUTE2 => null,
3875 X_ATTRIBUTE3 => null,
3876 X_ATTRIBUTE4 => null,
3877 X_ATTRIBUTE5 => null,
3878 X_ATTRIBUTE6 => null,
3879 X_ATTRIBUTE7 => null,
3880 X_ATTRIBUTE8 => null,
3881 X_ATTRIBUTE9 => null,
3882 X_ATTRIBUTE10 => null,
3883 X_ATTRIBUTE11 => null,
3884 X_ATTRIBUTE12 => null,
3885 X_ATTRIBUTE13 => null,
3886 X_ATTRIBUTE14 => null,
3887 X_ATTRIBUTE15 => null,
3888 X_CREATION_DATE => sysdate,
3889 X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
3890 X_LAST_UPDATE_DATE => sysdate,
3891 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
3892 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('LOGIN_ID'),0),
3893 X_ORG_ID => NVL(fnd_profile.value('org_id') ,239)
3894 );
3895
3896 END IF;
3897 END;
3898
3899
3900
3901 end PN_VAR_CHG_CAL_PKG;