[Home] [Help]
PACKAGE BODY: APPS.PN_VAR_TRX_PKG
Source
1 PACKAGE BODY pn_var_trx_pkg AS
2 -- $Header: PNVRTRXB.pls 120.0 2007/10/03 14:30:10 rthumma noship $
3 /*
4 need to fix the following in PNVRFUNB - replace existing code with the
5 code below
6
7 need to add this code to PNVRFUNS/B.pls in the mainline code
8 */
9
10 /* -------------------------------------------------------------------------
11 ------------------------- COMMON DATA STRUCTURES ------------------------
12 ------------------------------------------------------------------------- */
13 TYPE NUM_T IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
14
15 /* -------------------------------------------------------------------------
16 ------------------------- GLOBAL VARIABLES ------------------------------
17 ------------------------------------------------------------------------- */
18 g_precision NUMBER;
19
20 /* -------------------------------------------------------------------------
21 -------------------------- CURSORS FOR LOCKING --------------------------
22 ------------------------------------------------------------------------- */
23
24 /* get all line items for a period */
25 CURSOR line_items_lock4bkpt_c(p_vr_id IN NUMBER) IS
26 SELECT
27 line.line_item_id
28 FROM
29 pn_var_lines_all line
30 WHERE
31 line.var_rent_id = p_vr_id AND
32 line.bkpt_update_flag = 'Y'
33 /*FOR UPDATE NOWAIT*/;
34
35 /* get all line items for a period */
36 CURSOR line_items_lock4salesvol_c(p_vr_id IN NUMBER) IS
37 SELECT
38 line.line_item_id
39 FROM
40 pn_var_lines_all line
41 WHERE
42 line.var_rent_id = p_vr_id AND
43 line.sales_vol_update_flag = 'Y'
44 /*FOR UPDATE NOWAIT*/;
45
46 /* get all line items for a periods, and dont lock them */
47 CURSOR line_items_c(p_vr_id IN NUMBER) IS
48 SELECT
49 line.line_item_id
50 FROM
51 pn_var_lines_all line
52 WHERE
53 line.var_rent_id = p_vr_id
54 /*FOR UPDATE NOWAIT*/;
55
56 /* -------------------------------------------------------------------------
57 ----------------------- PROCEDURES AND FUNCTIONS -----------------------
58 ------------------------------------------------------------------------- */
59
60
61 --------------------------------------------------------------------------------
62 -- NAME : get_proration_rule
63 -- DESCRIPTION : gets proration rule frm the VR table given VR ID or period ID
64 -- INVOKED FROM : utility fn - used from several places
65 -- PN_VAR_POP_TRX_PKG
66 -- ARGUMENTS : p_var_rent_id - VR ID
67 -- p_period_id - Period ID
68 -- REFERENCE :
69 -- HISTORY
70 --
71 -- 27-Mar-06 Kiran o Created
72 --------------------------------------------------------------------------------
73 FUNCTION get_proration_rule(p_var_rent_id IN NUMBER DEFAULT NULL,
74 p_period_id IN NUMBER DEFAULT NULL)
75 RETURN VARCHAR2 IS
76
77 CURSOR proration_rule_vr_c IS
78 SELECT proration_rule
79 FROM pn_var_rents_all
80 WHERE var_rent_id = p_var_rent_id;
81
82 CURSOR proration_rule_prd_c IS
83 SELECT proration_rule
84 FROM pn_var_rents_all vr
85 ,pn_var_periods_all prd
86 WHERE vr.var_rent_id = prd.var_rent_id
87 AND prd.period_id = p_period_id;
88
89 l_proration_rule VARCHAR2(30);
90
91 BEGIN
92
93 l_proration_rule := NULL;
94
95 IF p_var_rent_id IS NOT NULL THEN
96
97 FOR rec IN proration_rule_vr_c LOOP
98 l_proration_rule := rec.proration_rule;
99 END LOOP;
100
101 ELSIF p_period_id IS NOT NULL THEN
102
103 FOR rec IN proration_rule_prd_c LOOP
104 l_proration_rule := rec.proration_rule;
105 END LOOP;
106
107 END IF;
108
109 RETURN l_proration_rule;
110
111 END get_proration_rule;
112
113 --------------------------------------------------------------------------------
114 -- NAME : exists_trx_hdr
115 -- DESCRIPTION : returns trx hdr ID if found else returns NULL
116 -- INVOKED FROM : exists_trx_hdr
117 -- ARGUMENTS :
118 -- REFERENCE : PN_COMMON.debug()
119 -- HISTORY :
120 --
121 -- dd-mon-yyyy name o Created
122 --------------------------------------------------------------------------------
123 FUNCTION exists_trx_hdr( p_vr_id IN NUMBER
124 ,p_period_id IN NUMBER
125 ,p_line_item_id IN NUMBER
126 ,p_grp_date_id IN NUMBER
127 ,p_calc_prd_st_dt IN DATE
128 ,p_calc_prd_end_dt IN DATE)
129 RETURN NUMBER IS
130
131 CURSOR trx_header_exists_c IS
132 SELECT
133 trx_header_id
134 FROM
135 pn_var_trx_headers_all
136 WHERE
137 var_rent_id = p_vr_id AND
138 period_id = p_period_id AND
139 line_item_id = p_line_item_id AND
140 grp_date_id = p_grp_date_id AND
141 calc_prd_start_date = p_calc_prd_st_dt AND
142 calc_prd_end_date = p_calc_prd_end_dt;
143
144 l_trx_hdr_ID NUMBER;
145
146 BEGIN
147
148 l_trx_hdr_ID := NULL;
149
150 FOR rec IN trx_header_exists_c LOOP
151 l_trx_hdr_ID := rec.trx_header_id;
152 END LOOP;
153
154 RETURN l_trx_hdr_ID;
155
156 EXCEPTION
157 WHEN OTHERS THEN RAISE;
158
159 END exists_trx_hdr;
160
161 --------------------------------------------------------------------------------
162 -- NAME : exists_trx_dtl
163 -- DESCRIPTION : returns trx dtl ID if found else returns NULL
164 -- INVOKED FROM : exists_trx_dtl
165 -- ARGUMENTS :
166 -- REFERENCE : PN_COMMON.debug()
167 -- HISTORY :
168 --
169 -- dd-mon-yyyy name o Created
170 --------------------------------------------------------------------------------
171 FUNCTION exists_trx_dtl( p_trx_hdr_id IN NUMBER
172 ,p_bkpt_dtl_id IN NUMBER)
173 RETURN NUMBER IS
174
175 CURSOR trx_detail_exists_c IS
176 SELECT
177 trx_detail_id
178 FROM
179 pn_var_trx_details_all
180 WHERE
181 trx_header_id = p_trx_hdr_id AND
182 bkpt_detail_id = p_bkpt_dtl_id;
183
184 l_trx_dtl_ID NUMBER;
185
186 BEGIN
187
188 l_trx_dtl_ID := NULL;
189
190 FOR rec IN trx_detail_exists_c LOOP
191 l_trx_dtl_ID := rec.trx_detail_id;
192 END LOOP;
193
194 RETURN l_trx_dtl_ID;
195
196 EXCEPTION
197 WHEN OTHERS THEN RAISE;
198
199 END exists_trx_dtl;
200
201 --------------------------------------------------------------------------------
202 -- NAME : insert_trx_hdr
203 -- DESCRIPTION :
204 -- PURPOSE :
205 -- INVOKED FROM :
206 -- ARGUMENTS :
207 -- REFERENCE : PN_COMMON.debug()
208 -- HISTORY :
209 --
210 -- dd-mon-yyyy name o Created
211 -- 23-MAY-2007 Lokesh o Added rounding off for Bug # 6031202
212 --------------------------------------------------------------------------------
213 PROCEDURE insert_trx_hdr(p_trx_header_id IN OUT NOCOPY NUMBER
214 ,p_var_rent_id IN NUMBER
215 ,p_period_id IN NUMBER
216 ,p_line_item_id IN NUMBER
217 ,p_grp_date_id IN NUMBER
218 ,p_calc_prd_start_date IN DATE
219 ,p_calc_prd_end_date IN DATE
220 ,p_var_rent_summ_id IN NUMBER
221 ,p_line_item_group_id IN NUMBER
222 ,p_reset_group_id IN NUMBER
223 ,p_proration_factor IN NUMBER
224 ,p_reporting_group_sales IN NUMBER
225 ,p_prorated_group_sales IN NUMBER
226 ,p_ytd_sales IN NUMBER
227 ,p_fy_proration_sales IN NUMBER
228 ,p_ly_proration_sales IN NUMBER
229 ,p_percent_rent_due IN NUMBER
230 ,p_ytd_percent_rent IN NUMBER
231 ,p_calculated_rent IN NUMBER
232 ,p_prorated_rent_due IN NUMBER
233 ,p_invoice_flag IN VARCHAR2
234 ,p_org_id IN NUMBER
235 ,p_last_update_date IN DATE
236 ,p_last_updated_by IN NUMBER
237 ,p_creation_date IN DATE
238 ,p_created_by IN NUMBER
239 ,p_last_update_login IN NUMBER) IS
240
241 BEGIN
242
243 INSERT INTO pn_var_trx_headers_all
244 (trx_header_id
245 ,var_rent_id
246 ,period_id
247 ,line_item_id
248 ,grp_date_id
249 ,calc_prd_start_date
250 ,calc_prd_end_date
251 ,var_rent_summ_id
252 ,line_item_group_id
253 ,reset_group_id
254 ,proration_factor
255 ,reporting_group_sales
256 ,prorated_group_sales
257 ,ytd_sales
258 ,fy_proration_sales
259 ,ly_proration_sales
260 ,percent_rent_due
261 ,ytd_percent_rent
262 ,calculated_rent
263 ,prorated_rent_due
264 ,invoice_flag
265 ,org_id
266 ,last_update_date
267 ,last_updated_by
268 ,creation_date
269 ,created_by
270 ,last_update_login)
271 VALUES
272 (pn_var_trx_headers_S.NEXTVAL
273 ,p_var_rent_id
274 ,p_period_id
275 ,p_line_item_id
276 ,p_grp_date_id
277 ,p_calc_prd_start_date
278 ,p_calc_prd_end_date
279 ,p_var_rent_summ_id
280 ,p_line_item_group_id
281 ,p_reset_group_id
282 ,round(p_proration_factor,10)
283 ,p_reporting_group_sales
284 ,p_prorated_group_sales
285 ,p_ytd_sales
286 ,p_fy_proration_sales
287 ,p_ly_proration_sales
288 ,round(p_percent_rent_due,g_precision) /*Bug # 6031202*/
289 ,round(p_ytd_percent_rent,g_precision)
290 ,round(p_calculated_rent,g_precision)
291 ,round(p_prorated_rent_due,g_precision)
292 ,p_invoice_flag
293 ,p_org_id
294 ,SYSDATE
295 ,NVL(fnd_global.user_id,0)
296 ,SYSDATE
297 ,NVL(fnd_global.user_id,0)
298 ,NVL(fnd_global.login_id,0))
299 RETURNING trx_header_id INTO p_trx_header_id;
300
301 EXCEPTION
302 WHEN OTHERS THEN RAISE;
303
304 END insert_trx_hdr;
305
306 --------------------------------------------------------------------------------
307 -- NAME : insert_trx_dtl
308 -- DESCRIPTION :
309 -- PURPOSE :
310 -- INVOKED FROM :
311 -- ARGUMENTS :
312 -- REFERENCE : PN_COMMON.debug()
313 -- HISTORY :
314 --
315 -- dd-mon-yyyy name o Created
316 --------------------------------------------------------------------------------
317 PROCEDURE insert_trx_dtl(p_trx_detail_id IN OUT NOCOPY NUMBER
318 ,p_trx_header_id IN NUMBER
319 ,p_bkpt_detail_id IN NUMBER
320 ,p_bkpt_rate IN NUMBER
321 ,p_prorated_grp_vol_start IN NUMBER
322 ,p_prorated_grp_vol_end IN NUMBER
323 ,p_fy_pr_grp_vol_start IN NUMBER
324 ,p_fy_pr_grp_vol_end IN NUMBER
325 ,p_ly_pr_grp_vol_start IN NUMBER
326 ,p_ly_pr_grp_vol_end IN NUMBER
327 ,p_pr_grp_blended_vol_start IN NUMBER
328 ,p_pr_grp_blended_vol_end IN NUMBER
329 ,p_ytd_group_vol_start IN NUMBER
330 ,p_ytd_group_vol_end IN NUMBER
331 ,p_blended_period_vol_start IN NUMBER
332 ,p_blended_period_vol_end IN NUMBER
333 ,p_org_id IN NUMBER
334 ,p_last_update_date IN DATE
335 ,p_last_updated_by IN NUMBER
336 ,p_creation_date IN DATE
337 ,p_created_by IN NUMBER
338 ,p_last_update_login IN NUMBER) IS
339
340 BEGIN
341
342 INSERT INTO pn_var_trx_details_all
343 (trx_detail_id
344 ,trx_header_id
345 ,bkpt_detail_id
346 ,bkpt_rate
347 ,prorated_grp_vol_start
348 ,prorated_grp_vol_end
349 ,fy_pr_grp_vol_start
350 ,fy_pr_grp_vol_end
351 ,ly_pr_grp_vol_start
352 ,ly_pr_grp_vol_end
353 ,pr_grp_blended_vol_start
354 ,pr_grp_blended_vol_end
355 ,ytd_group_vol_start
356 ,ytd_group_vol_end
357 ,blended_period_vol_start
358 ,blended_period_vol_end
359 ,org_id
360 ,last_update_date
361 ,last_updated_by
362 ,creation_date
363 ,created_by
364 ,last_update_login)
365 VALUES
366 (pn_var_trx_details_S.NEXTVAL
367 ,p_trx_header_id
368 ,p_bkpt_detail_id
369 ,p_bkpt_rate
370 ,p_prorated_grp_vol_start
371 ,p_prorated_grp_vol_end
372 ,p_fy_pr_grp_vol_start
373 ,p_fy_pr_grp_vol_end
374 ,p_ly_pr_grp_vol_start
375 ,p_ly_pr_grp_vol_end
376 ,p_pr_grp_blended_vol_start
377 ,p_pr_grp_blended_vol_end
378 ,p_ytd_group_vol_start
379 ,p_ytd_group_vol_end
380 ,p_blended_period_vol_start
381 ,p_blended_period_vol_end
382 ,p_org_id
383 ,SYSDATE
384 ,NVL(fnd_global.user_id,0)
385 ,SYSDATE
386 ,NVL(fnd_global.user_id,0)
387 ,NVL(fnd_global.login_id,0))
388 RETURNING trx_detail_id INTO p_trx_detail_id;
389
390 EXCEPTION
391 WHEN OTHERS THEN RAISE;
392 END insert_trx_dtl;
393
394
395 /* ----------------------------------------------------------------------
396 ----- PROCEDURES TO CREATE TRX HEADERS, DETAILS, POPULATE BKPTS -----
397 ---------------------------------------------------------------------- */
398
399
400 --------------------------------------------------------------------------------
401 -- NAME : populate_line_grp_id
402 -- DESCRIPTION :
403 -- PURPOSE :
404 -- INVOKED FROM :
405 -- ARGUMENTS :
406 -- REFERENCE : PN_COMMON.debug()
407 -- HISTORY :
408 --
409 -- dd-mon-yyyy name o Created
410 --------------------------------------------------------------------------------
411 PROCEDURE populate_line_grp_id(p_var_rent_id IN NUMBER) IS
412
413 l_proration_rule VARCHAR2(30);
414
415 /* check if line group ID is already populated */
416 CURSOR check_line_grp(p_vr_id IN NUMBER) IS
417 SELECT 1
418 FROM dual
419 WHERE EXISTS
420 (SELECT
421 trx_header_id
422 FROM
423 pn_var_trx_headers_all
424 WHERE
425 var_rent_id = p_vr_id AND
426 line_item_group_id IS NULL
427 AND rownum = 1);
428
429 l_populate_line_grp BOOLEAN;
430
431 /* get distinct line type-category */
432 CURSOR line_type_cat_c(p_vr_id IN NUMBER) IS
433 SELECT
434 NVL(line.sales_type_code, 'NULL') AS sales_type_code
435 ,NVL(line.item_category_code, 'NULL') AS item_category_code
436 FROM
437 pn_var_lines_all line
438 WHERE
439 line.var_rent_id = p_vr_id
440 GROUP BY
441 NVL(line.sales_type_code, 'NULL')
442 ,NVL(line.item_category_code, 'NULL');
443
444 l_line_grp_id NUMBER;
445
446 BEGIN
447
448 l_populate_line_grp := FALSE;
449
450 FOR rec IN check_line_grp(p_vr_id => p_var_rent_id) LOOP
451 l_populate_line_grp := TRUE;
452 END LOOP;
453
454 IF l_populate_line_grp THEN
455
456 l_line_grp_id := 1;
457
458 FOR line_typ_rec IN line_type_cat_c(p_vr_id => p_var_rent_id) LOOP
459
460 UPDATE
461 pn_var_trx_headers_all
462 SET
463 line_item_group_id = l_line_grp_id
464 WHERE
465 line_item_id IN
466 ( SELECT
467 line.line_item_id
468 FROM
469 pn_var_lines_all line
470 WHERE
471 line.var_rent_id = p_var_rent_id AND
472 NVL(line.sales_type_code, 'NULL')
473 = NVL(line_typ_rec.sales_type_code, 'NULL') AND
474 NVL(line.item_category_code, 'NULL')
475 = NVL(line_typ_rec.item_category_code, 'NULL')
476 );
477
478 l_line_grp_id := l_line_grp_id + 1;
479
480 END LOOP;
481
482 END IF;
483
484 EXCEPTION
485 WHEN OTHERS THEN RAISE;
486
487 END populate_line_grp_id;
488
489 --------------------------------------------------------------------------------
490 -- NAME : populate_reset_grp_id
491 -- DESCRIPTION : populates reset grp ID and proration reset grp ID in the
492 -- trx table.
493 -- INVOKED FROM :
494 -- ARGUMENTS :
495 -- REFERENCE : PN_COMMON.debug()
496 -- HISTORY :
497 --
498 -- dd-mon-yyyy name o Created
499 --------------------------------------------------------------------------------
500 PROCEDURE populate_reset_grp_id( p_var_rent_id IN NUMBER) IS
501
502 /* get all the trx headers */
503 CURSOR trx_hdrs_c(p_vr_id IN NUMBER) IS
504 SELECT
505 trx_header_id
506 ,calc_prd_start_date
507 ,line_item_group_id
508 FROM
509 pn_var_trx_headers_all
510 WHERE
511 var_rent_id = p_vr_id
512 ORDER BY
513 line_item_group_id
514 ,calc_prd_start_date;
515
516 l_line_item_group_id NUMBER;
517
518 /* get rates for a trx header */
519 CURSOR bkpt_rates_c(p_trx_hrd_id IN NUMBER) IS
520 SELECT
521 bkpt_rate
522 FROM
523 pn_var_trx_details_all
524 WHERE
525 trx_header_id = p_trx_hrd_id
526 ORDER BY
527 prorated_grp_vol_start;
528
529 l_hdr_counter NUMBER;
530 l_reset_counter NUMBER;
531
532 rate_tbl_1 NUM_T;
533 rate_tbl_2 NUM_T;
534
535 trx_hdr_tbl NUM_T;
536 reset_ctr_tbl NUM_T;
537
538 l_bkpt_changed BOOLEAN;
539
540 BEGIN
541
542 /* init counters and tables */
543 l_hdr_counter := 0;
544 l_reset_counter := 0;
545
546 trx_hdr_tbl.DELETE;
547 reset_ctr_tbl.DELETE;
548
549 rate_tbl_1.DELETE;
550 rate_tbl_2.DELETE;
551
552 /* loop for all trx headers */
553 FOR hdr_rec IN trx_hdrs_c(p_vr_id => p_var_rent_id) LOOP
554
555 l_hdr_counter := l_hdr_counter + 1;
556
557 /* copy current rates to the prev */
558 rate_tbl_1.DELETE;
559
560 FOR i IN 1..rate_tbl_2.COUNT LOOP
561 rate_tbl_1(i) := rate_tbl_2(i);
562 END LOOP;
563
564 /* get new rates */
565 rate_tbl_2.DELETE;
566
567 OPEN bkpt_rates_c(hdr_rec.trx_header_id);
568 FETCH bkpt_rates_c BULK COLLECT INTO rate_tbl_2;
569 CLOSE bkpt_rates_c;
570
571 l_bkpt_changed := FALSE;
572
573 /* check if bkpts changed */
574 IF rate_tbl_1.COUNT <> rate_tbl_2.COUNT THEN
575
576 /* bkpts changed if number of bkpt details changed */
577 l_bkpt_changed := TRUE;
578
579 ELSE
580
581 FOR i IN 1..rate_tbl_2.COUNT LOOP
582 IF rate_tbl_1(i) <> rate_tbl_2(i) THEN
583 /* bkpts changed if rate in bkpt details changed */
584 l_bkpt_changed := TRUE;
585 EXIT;
586 END IF;
587 END LOOP;
588
589 END IF;
590
591 /* if bkpts changed */
592 IF l_bkpt_changed THEN
593
594 /* then, increment reset ctr; init line item grp ID */
595 l_reset_counter := l_reset_counter + 1;
596 l_line_item_group_id := hdr_rec.line_item_group_id;
597
598 ELSE
599
600 /* else, if line item grp changed */
601 IF l_line_item_group_id <> hdr_rec.line_item_group_id THEN
602
603 /* then, increment reset ctr; init line item grp ID */
604 l_reset_counter := l_reset_counter + 1;
605 l_line_item_group_id := hdr_rec.line_item_group_id;
606
607 END IF;
608
609 END IF;
610
611 /* cache trx hdr ID, reset grp ID */
612 trx_hdr_tbl(l_hdr_counter) := hdr_rec.trx_header_id;
613 reset_ctr_tbl(l_hdr_counter) := l_reset_counter;
614
615 END LOOP;
616
617 /* update trx hdr, set reset grp ID */
618 FORALL hdr_rec IN 1..trx_hdr_tbl.COUNT
619 UPDATE
620 pn_var_trx_headers_all
621 SET
622 reset_group_id = reset_ctr_tbl(hdr_rec)
623 WHERE
624 trx_header_id = trx_hdr_tbl(hdr_rec);
625
626 EXCEPTION
627 WHEN OTHERS THEN RAISE;
628
629 END populate_reset_grp_id;
630
631 --------------------------------------------------------------------------------
632 -- NAME : populate_ly_pro_vol
633 -- DESCRIPTION :
634 -- PURPOSE :
635 -- INVOKED FROM :
636 -- ARGUMENTS :
637 -- REFERENCE : PN_COMMON.debug()
638 -- HISTORY :
639 --
640 -- dd-mon-yyyy name o Created
641 --------------------------------------------------------------------------------
642 PROCEDURE populate_ly_pro_vol ( p_var_rent_id IN NUMBER
643 ,p_proration_rule IN VARCHAR2
644 ,p_vr_commencement_dt IN DATE
645 ,p_vr_termination_dt IN DATE) IS
646
647 /* get VR details */
648 CURSOR vr_c(p_vr_id IN NUMBER) IS
649 SELECT
650 vr.var_rent_id
651 ,vr.commencement_date
652 ,vr.termination_date
653 ,vr.proration_rule
654 FROM
655 pn_var_rents_all vr
656 WHERE
657 vr.var_rent_id = p_vr_id;
658
659 /* trx header containing LY start date */
660 CURSOR trx_ly_c( p_vr_id IN NUMBER
661 ,p_date IN DATE) IS
662 SELECT
663 hdr.trx_header_id
664 ,hdr.calc_prd_start_date
665 ,hdr.calc_prd_end_date
666 FROM
667 pn_var_trx_headers_all hdr
668 WHERE
669 hdr.var_rent_id = p_vr_id AND
670 p_date BETWEEN (hdr.calc_prd_start_date + 1) AND hdr.calc_prd_end_date;
671
672 l_vr_commencement_date DATE;
673 l_vr_termination_date DATE;
674 l_vr_proration_rule VARCHAR2(30);
675 l_ly_start_date DATE;
676
677 l_proration_factor NUMBER;
678
679 /* get the last partial period */
680 CURSOR last_period_c( p_vr_id IN NUMBER
681 ,p_term_date IN DATE) IS
682 SELECT
683 prd.period_id
684 ,prd.partial_period
685 FROM
686 pn_var_periods_all prd
687 WHERE
688 prd.var_rent_id = p_var_rent_id AND
689 prd.end_date = p_term_date;
690
691 l_last_period_id NUMBER;
692 l_partial_period VARCHAR2(1);
693
694 BEGIN
695
696 IF p_proration_rule IS NULL OR
697 p_vr_commencement_dt IS NULL OR
698 p_vr_termination_dt IS NULL
699 THEN
700 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
701 l_vr_commencement_date := vr_rec.commencement_date;
702 l_vr_termination_date := vr_rec.termination_date;
703 l_vr_proration_rule := vr_rec.proration_rule;
704 END LOOP;
705 ELSE
706 l_vr_commencement_date := p_vr_commencement_dt;
707 l_vr_termination_date := p_vr_termination_dt;
708 l_vr_proration_rule := p_proration_rule;
709 END IF;
710
711 l_ly_start_date := ADD_MONTHS(l_vr_termination_date, -12) + 1;
712
713 IF l_vr_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_LY
714 ,pn_var_trx_pkg.G_PRORUL_FLY) THEN
715
716 /* -- POPULATE INVOICE FLAG - START -- */
717 FOR prd_rec IN last_period_c( p_vr_id => p_var_rent_id
718 ,p_term_date => l_vr_termination_date)
719 LOOP
720 l_last_period_id := prd_rec.period_id;
721 l_partial_period := NVL(prd_rec.partial_period, 'N');
722 END LOOP;
723
724 /* init invoice flag */
725 UPDATE
726 pn_var_trx_headers_all
727 SET
728 invoice_flag = NULL
729 WHERE
730 var_rent_id = p_var_rent_id AND
731 invoice_flag IN ('N', 'I');
732
733 IF l_partial_period = 'Y' THEN
734
735 /* populate invoice flag = N */
736 UPDATE
737 pn_var_trx_headers_all
738 SET
739 invoice_flag = 'N'
740 WHERE
741 var_rent_id = p_var_rent_id AND
742 period_id = l_last_period_id;
743
744 /* populate invoice flag = I */
745 UPDATE
746 pn_var_trx_headers_all
747 SET
748 invoice_flag = 'I'
749 WHERE
750 var_rent_id = p_var_rent_id AND
751 calc_prd_end_date = l_vr_termination_date;
752
753 END IF;
754 /* -- POPULATE INVOICE FLAG - END -- */
755
756 /* -- POPULATE ly_pr_grp_vol_start - ly_pr_grp_vol_end - START -- */
757
758 /* init ly_pr_grp_vol_start - ly_pr_grp_vol_end */
759 UPDATE
760 pn_var_trx_details_all
761 SET
762 ly_pr_grp_vol_start = NULL
763 ,ly_pr_grp_vol_end = NULL
764 WHERE
765 trx_header_id IN (SELECT
766 trx_header_id
767 FROM
768 pn_var_trx_headers_all
769 WHERE
770 var_rent_id = p_var_rent_id);
771
772 IF l_partial_period = 'Y' THEN
773
774 /* populate ly_pr_grp_vol_start - ly_pr_grp_vol_end */
775 UPDATE
776 pn_var_trx_details_all
777 SET
778 ly_pr_grp_vol_start = prorated_grp_vol_start
779 ,ly_pr_grp_vol_end = prorated_grp_vol_end
780 WHERE
781 trx_header_id IN (SELECT
782 trx_header_id
783 FROM
784 pn_var_trx_headers_all
785 WHERE
786 var_rent_id = p_var_rent_id AND
787 calc_prd_start_date >= l_ly_start_date);
788
789 FOR trx_rec IN trx_ly_c( p_vr_id => p_var_rent_id
790 ,p_date => l_ly_start_date) LOOP
791
792 /* ly proration factor */
793 l_proration_factor
794 := ((trx_rec.calc_prd_end_date - l_ly_start_date) + 1)
795 / ((trx_rec.calc_prd_end_date - trx_rec.calc_prd_start_date) + 1);
796
797 UPDATE
798 pn_var_trx_details_all
799 SET
800 ly_pr_grp_vol_start = prorated_grp_vol_start * l_proration_factor
801 ,ly_pr_grp_vol_end = prorated_grp_vol_end * l_proration_factor
802 WHERE
803 trx_header_id = trx_rec.trx_header_id;
804
805 END LOOP;
806
807 END IF;
808
809 /* -- POPULATE ly_pr_grp_vol_start - ly_pr_grp_vol_end - START -- */
810
811 END IF;
812
813 EXCEPTION
814 WHEN OTHERS THEN RAISE;
815
816 END populate_ly_pro_vol;
817
818 --------------------------------------------------------------------------------
819 -- NAME : populate_fy_pro_vol
820 -- DESCRIPTION :
821 -- PURPOSE :
822 -- INVOKED FROM :
823 -- ARGUMENTS :
824 -- REFERENCE : PN_COMMON.debug()
825 -- HISTORY :
826 --
827 -- dd-mon-yyyy name o Created
828 --------------------------------------------------------------------------------
829 PROCEDURE populate_fy_pro_vol( p_var_rent_id IN NUMBER
830 ,p_proration_rule IN VARCHAR2
831 ,p_vr_commencement_dt IN DATE
832 ,p_vr_termination_dt IN DATE) IS
833
834 /* get VR details */
835 CURSOR vr_c(p_vr_id IN NUMBER) IS
836 SELECT
837 vr.var_rent_id
838 ,vr.commencement_date
839 ,vr.termination_date
840 ,vr.proration_rule
841 FROM
842 pn_var_rents_all vr
843 WHERE
844 vr.var_rent_id = p_vr_id;
845
846 /* get rates for trx header containing FY end date */
847 CURSOR trx_fy_c( p_vr_id IN NUMBER
848 ,p_date IN DATE) IS
849 SELECT
850 hdr.trx_header_id
851 ,hdr.calc_prd_start_date
852 ,hdr.calc_prd_end_date
853 FROM
854 pn_var_trx_headers_all hdr
855 WHERE
856 hdr.var_rent_id = p_vr_id AND
857 p_date BETWEEN hdr.calc_prd_start_date AND (hdr.calc_prd_end_date - 1);
858
859 l_vr_commencement_date DATE;
860 l_vr_termination_date DATE;
861 l_vr_proration_rule VARCHAR2(30);
862 l_fy_end_date DATE;
863
864 l_proration_factor NUMBER;
865
866 /* get the first partial period */
867 CURSOR first_period_c( p_vr_id IN NUMBER
868 ,p_comm_date IN DATE) IS
869 SELECT
870 prd.period_id
871 ,prd.partial_period
872 FROM
873 pn_var_periods_all prd
874 WHERE
875 prd.var_rent_id = p_var_rent_id AND
876 prd.start_date = p_comm_date;
877
878 l_first_period_id NUMBER;
879 l_partial_period VARCHAR2(1);
880
881 BEGIN
882
883 IF p_proration_rule IS NULL OR
884 p_vr_commencement_dt IS NULL OR
885 p_vr_termination_dt IS NULL
886 THEN
887 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
888 l_vr_commencement_date := vr_rec.commencement_date;
889 l_vr_termination_date := vr_rec.termination_date;
890 l_vr_proration_rule := vr_rec.proration_rule;
891 END LOOP;
892 ELSE
893 l_vr_commencement_date := p_vr_commencement_dt;
894 l_vr_termination_date := p_vr_termination_dt;
895 l_vr_proration_rule := p_proration_rule;
896 END IF;
897
898 l_fy_end_date := ADD_MONTHS(l_vr_commencement_date, 12) - 1;
899
900 IF l_vr_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
901 ,pn_var_trx_pkg.G_PRORUL_FLY) THEN
902
903 FOR prd_rec IN first_period_c( p_vr_id => p_var_rent_id
904 ,p_comm_date => l_vr_commencement_date)
905 LOOP
906 l_first_period_id := prd_rec.period_id;
907 l_partial_period := NVL(prd_rec.partial_period, 'N');
908 END LOOP;
909
910 /* -- POPULATE INVOICE FLAG - START -- */
911 IF l_vr_proration_rule = pn_var_trx_pkg.G_PRORUL_FY THEN
912
913 /* init invoice flag */
914 UPDATE
915 pn_var_trx_headers_all
916 SET
917 invoice_flag = NULL
918 WHERE
919 var_rent_id = p_var_rent_id AND
920 invoice_flag IN ('N', 'I');
921
922 END IF;
923
924 IF l_partial_period = 'Y' THEN
925
926 /* populate invoice flag = N */
927 UPDATE
928 pn_var_trx_headers_all
929 SET
930 invoice_flag = 'N'
931 WHERE
932 var_rent_id = p_var_rent_id AND
933 period_id = (SELECT
934 prd.period_id
935 FROM
936 pn_var_periods_all prd
937 WHERE
938 prd.var_rent_id = p_var_rent_id AND
939 prd.start_date = l_vr_commencement_date AND
940 prd.partial_period = 'Y');
941
942 /* populate invoice flag = I */
943 UPDATE
944 pn_var_trx_headers_all
945 SET
946 invoice_flag = 'I'
947 WHERE
948 var_rent_id = p_var_rent_id AND
949 l_fy_end_date BETWEEN calc_prd_start_date AND calc_prd_end_date;
950
951 END IF;
952
953 /* init fy_pr_grp_vol_start - fy_pr_grp_vol_end */
954 UPDATE
955 pn_var_trx_details_all
956 SET
957 fy_pr_grp_vol_start = NULL
958 ,fy_pr_grp_vol_end = NULL
959 WHERE
960 trx_header_id IN (SELECT
961 trx_header_id
962 FROM
963 pn_var_trx_headers_all
964 WHERE
965 var_rent_id = p_var_rent_id);
966
967 IF l_partial_period = 'Y' THEN
968 /* populate fy_pr_grp_vol_start - fy_pr_grp_vol_end */
969 UPDATE
970 pn_var_trx_details_all
971 SET
972 fy_pr_grp_vol_start = prorated_grp_vol_start
973 ,fy_pr_grp_vol_end = prorated_grp_vol_end
974 WHERE
975 trx_header_id IN (SELECT
976 trx_header_id
977 FROM
978 pn_var_trx_headers_all
979 WHERE
980 var_rent_id = p_var_rent_id AND
981 calc_prd_end_date <= l_fy_end_date);
982
983
984 FOR trx_rec IN trx_fy_c( p_vr_id => p_var_rent_id
985 ,p_date => l_fy_end_date) LOOP
986
987 /* fy proration factor */
988 l_proration_factor
989 := ((l_fy_end_date - trx_rec.calc_prd_start_date) + 1)
990 / ((trx_rec.calc_prd_end_date - trx_rec.calc_prd_start_date) + 1);
991
992 /* populate fy_pr_grp_vol_start - fy_pr_grp_vol_end */
993 UPDATE
994 pn_var_trx_details_all
995 SET
996 fy_pr_grp_vol_start = prorated_grp_vol_start * l_proration_factor
997 ,fy_pr_grp_vol_end = prorated_grp_vol_end * l_proration_factor
998 WHERE
999 trx_header_id = trx_rec.trx_header_id;
1000
1001 END LOOP;
1002 END IF;
1003
1004 END IF;
1005
1006 EXCEPTION
1007 WHEN OTHERS THEN RAISE;
1008
1009 END populate_fy_pro_vol;
1010
1011 --------------------------------------------------------------------------------
1012 -- NAME : populate_blended_grp_vol
1013 -- DESCRIPTION :
1014 -- PURPOSE :
1015 -- INVOKED FROM :
1016 -- ARGUMENTS :
1017 -- REFERENCE : PN_COMMON.debug()
1018 -- HISTORY :
1019 --
1020 -- dd-mon-yyyy name o Created
1021 --------------------------------------------------------------------------------
1022 PROCEDURE populate_blended_grp_vol( p_var_rent_id IN NUMBER
1023 ,p_proration_rule IN VARCHAR2) IS
1024
1025 /* get VR details */
1026 CURSOR vr_c(p_vr_id IN NUMBER) IS
1027 SELECT
1028 vr.var_rent_id
1029 ,vr.commencement_date
1030 ,vr.termination_date
1031 ,vr.proration_rule
1032 FROM
1033 pn_var_rents_all vr
1034 WHERE
1035 vr.var_rent_id = p_vr_id;
1036
1037 /* VR info */
1038 l_vr_commencement_date DATE;
1039 l_vr_termination_date DATE;
1040 l_vr_proration_rule VARCHAR2(30);
1041
1042 /* get the period details - we use the first 2 periods */
1043 CURSOR periods_c(p_vr_id IN NUMBER) IS
1044 SELECT
1045 period_id
1046 ,start_date
1047 ,end_date
1048 ,partial_period
1049 FROM
1050 pn_var_periods_all
1051 WHERE
1052 var_rent_id = p_vr_id
1053 ORDER BY
1054 start_date;
1055
1056 /* period info */
1057 l_part_prd_id NUMBER;
1058 l_part_prd_start_dt DATE;
1059 l_part_prd_end_dt DATE;
1060 l_part_prd_partial_flag VARCHAR2(1);
1061
1062 l_full_prd_id NUMBER;
1063 l_full_prd_start_dt DATE;
1064 l_full_prd_end_dt DATE;
1065 l_full_prd_partial_flag VARCHAR2(1);
1066
1067 /* sum the proration factor in groups - OLD */
1068 /*
1069 CURSOR grp_pro_factor_sum_c(p_prd_id IN NUMBER) IS
1070 SELECT
1071 SUM(grp.proration_factor) proration_factor_sum
1072 FROM
1073 pn_var_grp_dates_all grp
1074 WHERE
1075 prd.period_id = p_prd_id
1076 GROUP BY
1077 grp.period_id;
1078 */
1079
1080 /* sum the proration factor in groups */
1081 CURSOR grp_pro_factor_sum_c(p_prd_id IN NUMBER) IS
1082 SELECT
1083 SUM(grp.proration_factor) proration_factor_sum
1084 FROM
1085 pn_var_grp_dates_all grp
1086 ,pn_var_periods_all prd
1087 WHERE
1088 prd.period_id = p_prd_id AND
1089 grp.period_id = prd.period_id AND
1090 grp.grp_end_date <= prd.end_date
1091 GROUP BY
1092 grp.period_id;
1093
1094 /* period length in calc period units */
1095 l_part_prd_length NUMBER;
1096 l_full_prd_length NUMBER;
1097
1098 /* blended period volumes for CYP and CYNP for combined period */
1099 CURSOR blended_prd_vol_cs( p_vr_id IN NUMBER
1100 ,p_part_prd_ID IN NUMBER
1101 ,p_full_prd_ID IN NUMBER) IS
1102 SELECT /*+ LEADING(hdr) */
1103 hdr.line_item_group_id
1104 ,dtl.bkpt_rate
1105 ,SUM(dtl.prorated_grp_vol_start) AS blended_period_vol_start
1106 ,SUM(dtl.prorated_grp_vol_end) AS blended_period_vol_end
1107 FROM
1108 pn_var_trx_headers_all hdr
1109 ,pn_var_trx_details_all dtl
1110 WHERE
1111 hdr.var_rent_id = p_vr_id AND
1112 hdr.period_id IN (p_part_prd_ID, p_full_prd_ID) AND
1113 dtl.trx_header_id = hdr.trx_header_id
1114 GROUP BY
1115 hdr.line_item_group_id
1116 ,dtl.bkpt_rate;
1117
1118 /* handle first partial calculation period */
1119 CURSOR first_partial_cs_c( p_vr_id IN NUMBER
1120 ,p_prd_id IN NUMBER
1121 ,p_prd_st_dt IN DATE) IS
1122 SELECT
1123 hdr.trx_header_id
1124 ,grp.grp_date_id
1125 ,grp.proration_factor AS grp_prorat_factor
1126 ,hdr.proration_factor AS calc_prd_prorat_factor
1127 FROM
1128 pn_var_trx_headers_all hdr
1129 ,pn_var_grp_dates_all grp
1130 WHERE
1131 hdr.var_rent_id = p_vr_id AND
1132 hdr.period_id = p_prd_id /*AND
1133 hdr.calc_prd_start_date = p_prd_st_dt*/ AND
1134 grp.grp_date_id = hdr.grp_date_id;
1135
1136 /* handle last partial calculation period */
1137 CURSOR last_partial_cs_c( p_vr_id IN NUMBER
1138 ,p_prd_id IN NUMBER
1139 ,p_prd_end_dt IN DATE) IS
1140 SELECT
1141 hdr.trx_header_id
1142 ,grp.grp_date_id
1143 ,grp.proration_factor AS grp_prorat_factor
1144 ,hdr.proration_factor AS calc_prd_prorat_factor
1145 FROM
1146 pn_var_trx_headers_all hdr
1147 ,pn_var_grp_dates_all grp
1148 WHERE
1149 hdr.var_rent_id = p_vr_id AND
1150 hdr.period_id = p_prd_id /*AND
1151 hdr.calc_prd_end_date = p_prd_end_dt*/ AND
1152 grp.grp_date_id = hdr.grp_date_id;
1153
1154 /* counters */
1155 l_counter1 NUMBER;
1156
1157 l_context VARCHAR2(255);
1158
1159 BEGIN
1160
1161 pnp_debug_pkg.log('');
1162 pnp_debug_pkg.log('--- pn_var_trx_pkg.populate_blended_grp_vol START ---');
1163 pnp_debug_pkg.log('');
1164
1165 /* get proration RULE */
1166 IF p_proration_rule IS NULL THEN
1167
1168 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
1169 l_vr_proration_rule := vr_rec.proration_rule;
1170 END LOOP;
1171
1172 ELSE
1173
1174 l_vr_proration_rule := p_proration_rule;
1175
1176 END IF;
1177
1178 pnp_debug_pkg.log('VR ID: '||p_var_rent_id||
1179 ' Proration Rule: '||l_vr_proration_rule);
1180 pnp_debug_pkg.log('');
1181
1182 l_counter1 := 1;
1183
1184 /* fetch partial and full period details */
1185 l_context := 'Get first 2 period details';
1186
1187 FOR prd_rec IN periods_c(p_vr_id => p_var_rent_id) LOOP
1188
1189 IF l_counter1 = 1 THEN
1190 l_part_prd_id := prd_rec.period_id;
1191 l_part_prd_start_dt := prd_rec.start_date;
1192 l_part_prd_end_dt := prd_rec.end_date;
1193 l_part_prd_partial_flag := prd_rec.partial_period;
1194
1195 FOR rec IN grp_pro_factor_sum_c(p_prd_id => l_part_prd_id) LOOP
1196 l_part_prd_length := rec.proration_factor_sum;
1197 END LOOP;
1198
1199 ELSIF l_counter1 = 2 THEN
1200 l_full_prd_id := prd_rec.period_id;
1201 l_full_prd_start_dt := prd_rec.start_date;
1202 l_full_prd_end_dt := prd_rec.end_date;
1203 l_full_prd_partial_flag := prd_rec.partial_period;
1204
1205 FOR rec IN grp_pro_factor_sum_c(p_prd_id => l_full_prd_id) LOOP
1206 l_full_prd_length := rec.proration_factor_sum;
1207 END LOOP;
1208
1209 ELSE
1210 EXIT;
1211
1212 END IF;
1213
1214 l_counter1 := l_counter1 + 1;
1215
1216 END LOOP;
1217
1218 pnp_debug_pkg.log(l_context||' COMPLETE');
1219 pnp_debug_pkg.log('Part period Start Date: '||l_part_prd_start_dt);
1220 pnp_debug_pkg.log('Part period Length: '||l_part_prd_length);
1221 pnp_debug_pkg.log('Full period Start Date: '||l_full_prd_start_dt);
1222 pnp_debug_pkg.log('Full period Length: '||l_part_prd_start_dt);
1223 pnp_debug_pkg.log('');
1224
1225 IF l_vr_proration_rule = pn_var_trx_pkg.G_PRORUL_CYP THEN
1226
1227 l_context := 'CYP - update invoice_flag';
1228 /* reset the invoice flag */
1229 UPDATE
1230 pn_var_trx_headers_all
1231 SET
1232 invoice_flag = NULL
1233 WHERE
1234 var_rent_id = p_var_rent_id AND
1235 invoice_flag = 'P';
1236
1237 /* populate invoice_flag */
1238 UPDATE
1239 pn_var_trx_headers_all
1240 SET
1241 invoice_flag = 'P'
1242 WHERE
1243 trx_header_id IN (SELECT
1244 trx_header_id
1245 FROM
1246 pn_var_trx_headers_all
1247 WHERE
1248 var_rent_id = p_var_rent_id AND
1249 period_id IN (l_part_prd_id, l_full_prd_id)
1250 );
1251
1252 pnp_debug_pkg.log(l_context||' COMPLETE');
1253 pnp_debug_pkg.log('');
1254
1255
1256 l_context := 'CYP - update pr_grp_blended_vol_start - end';
1257 /* reset pr_grp_blended_vol_start - pr_grp_blended_vol_end */
1258 UPDATE
1259 pn_var_trx_details_all
1260 SET
1261 pr_grp_blended_vol_start = NULL
1262 ,pr_grp_blended_vol_end = NULL
1263 WHERE
1264 trx_header_id IN (SELECT
1265 trx_header_id
1266 FROM
1267 pn_var_trx_headers_all
1268 WHERE
1269 var_rent_id = p_var_rent_id);
1270
1271 /* populate pr_grp_blended_vol_start - pr_grp_blended_vol_end */
1272 UPDATE
1273 pn_var_trx_details_all
1274 SET
1275 pr_grp_blended_vol_start = prorated_grp_vol_start
1276 ,pr_grp_blended_vol_end = prorated_grp_vol_end
1277 WHERE
1278 trx_header_id IN (SELECT
1279 trx_header_id
1280 FROM
1281 pn_var_trx_headers_all
1282 WHERE
1283 var_rent_id = p_var_rent_id AND
1284 period_id IN (l_part_prd_id, l_full_prd_id)
1285 );
1286
1287 pnp_debug_pkg.log(l_context||' COMPLETE');
1288 pnp_debug_pkg.log('');
1289
1290 /* populate blended_period_vol_start - blended_period_vol_end */
1291 l_context := 'CYP - update blended_period_vol_start - end';
1292
1293 FOR cyp_rec IN blended_prd_vol_cs( p_vr_id => p_var_rent_id
1294 ,p_part_prd_ID => l_part_prd_id
1295 ,p_full_prd_ID => l_full_prd_id)
1296 LOOP
1297
1298 UPDATE
1299 pn_var_trx_details_all
1300 SET
1301 blended_period_vol_start = cyp_rec.blended_period_vol_start
1302 ,blended_period_vol_end = cyp_rec.blended_period_vol_end
1303 WHERE
1304 trx_header_id IN (SELECT
1305 trx_header_id
1306 FROM
1307 pn_var_trx_headers_all
1308 WHERE
1309 var_rent_id = p_var_rent_id AND
1310 period_id IN (l_part_prd_id, l_full_prd_id) AND
1311 line_item_group_id = cyp_rec.line_item_group_id
1312 ) AND
1313 bkpt_rate = cyp_rec.bkpt_rate;
1314
1315 END LOOP;
1316
1317 pnp_debug_pkg.log(l_context||' COMPLETE');
1318 pnp_debug_pkg.log('');
1319
1320 ELSIF l_vr_proration_rule = pn_var_trx_pkg.G_PRORUL_CYNP THEN
1321
1322 l_context := 'CYNP - update invoice_flag';
1323
1324 /* reset the invoice flag */
1325 UPDATE
1326 pn_var_trx_headers_all
1327 SET
1328 invoice_flag = NULL
1329 WHERE
1330 var_rent_id = p_var_rent_id AND
1331 invoice_flag = 'P';
1332
1333 /* populate fy_pr_grp_vol_start - fy_pr_grp_vol_end */
1334 UPDATE
1335 pn_var_trx_headers_all
1336 SET
1337 invoice_flag = 'P'
1338 WHERE
1339 trx_header_id IN (SELECT
1340 trx_header_id
1341 FROM
1342 pn_var_trx_headers_all
1343 WHERE
1344 var_rent_id = p_var_rent_id AND
1345 period_id IN (l_part_prd_id, l_full_prd_id)
1346 );
1347
1348 pnp_debug_pkg.log(l_context||' COMPLETE');
1349 pnp_debug_pkg.log('');
1350
1351 l_context
1352 := 'CYNP - update pr_grp_blended_vol_start - end, blended_period_vol_start - end';
1353 /* reset pr_grp_blended_vol_start - pr_grp_blended_vol_end */
1354 UPDATE
1355 pn_var_trx_details_all
1356 SET
1357 pr_grp_blended_vol_start = NULL
1358 ,pr_grp_blended_vol_end = NULL
1359 WHERE
1360 trx_header_id IN (SELECT
1361 trx_header_id
1362 FROM
1363 pn_var_trx_headers_all
1364 WHERE
1365 var_rent_id = p_var_rent_id);
1366
1367 FOR cynp_rec IN blended_prd_vol_cs( p_vr_id => p_var_rent_id
1368 ,p_part_prd_ID => l_part_prd_id
1369 ,p_full_prd_ID => l_full_prd_id)
1370 LOOP
1371
1372 UPDATE
1373 pn_var_trx_details_all
1374 SET
1375 blended_period_vol_start
1376 = (cynp_rec.blended_period_vol_start / (l_part_prd_length + l_full_prd_length))
1377 * l_full_prd_length
1378 ,blended_period_vol_end
1379 = (cynp_rec.blended_period_vol_end /(l_part_prd_length + l_full_prd_length))
1380 * l_full_prd_length
1381 ,pr_grp_blended_vol_start
1382 = (cynp_rec.blended_period_vol_start /(l_part_prd_length + l_full_prd_length))
1383 * (l_full_prd_length / (l_part_prd_length + l_full_prd_length))
1384 ,pr_grp_blended_vol_end
1385 = (cynp_rec.blended_period_vol_end /(l_part_prd_length + l_full_prd_length))
1386 * (l_full_prd_length / (l_part_prd_length + l_full_prd_length))
1387 WHERE
1388 trx_header_id IN (SELECT
1389 trx_header_id
1390 FROM
1391 pn_var_trx_headers_all
1392 WHERE
1393 var_rent_id = p_var_rent_id AND
1394 period_id IN (l_part_prd_id, l_full_prd_id)AND
1395 line_item_group_id = cynp_rec.line_item_group_id
1396 ) AND
1397 bkpt_rate = cynp_rec.bkpt_rate;
1398
1399 END LOOP;
1400
1401 pnp_debug_pkg.log(l_context||' COMPLETE');
1402 pnp_debug_pkg.log('');
1403
1404 l_context
1405 := 'CYNP - update pr_grp_blended_vol_start - end for first/last partial';
1406
1407 /* update first partial calc sub period pr_grp_blended_vol_start - end */
1408 FOR first_part_rec IN first_partial_cs_c( p_vr_id => p_var_rent_id
1409 ,p_prd_id => l_part_prd_id
1410 ,p_prd_st_dt => l_part_prd_start_dt)
1411 LOOP
1412
1413 UPDATE
1414 pn_var_trx_details_all
1415 SET
1416 pr_grp_blended_vol_start
1417 = pr_grp_blended_vol_start
1418 * first_part_rec.grp_prorat_factor
1419 * first_part_rec.calc_prd_prorat_factor
1420 ,pr_grp_blended_vol_end
1421 = pr_grp_blended_vol_end
1422 * first_part_rec.grp_prorat_factor
1423 * first_part_rec.calc_prd_prorat_factor
1424 WHERE
1425 trx_header_id = first_part_rec.trx_header_id;
1426
1427 END LOOP;
1428
1429 /* update last partial calc sub period pr_grp_blended_vol_start - end */
1430 FOR last_part_rec IN last_partial_cs_c( p_vr_id => p_var_rent_id
1431 ,p_prd_id => l_full_prd_id
1432 ,p_prd_end_dt => l_full_prd_end_dt)
1433 LOOP
1434
1435 UPDATE
1436 pn_var_trx_details_all
1437 SET
1438 pr_grp_blended_vol_start
1439 = pr_grp_blended_vol_start
1440 * last_part_rec.grp_prorat_factor
1441 * last_part_rec.calc_prd_prorat_factor
1442 ,pr_grp_blended_vol_end
1443 = pr_grp_blended_vol_end
1444 * last_part_rec.grp_prorat_factor
1445 * last_part_rec.calc_prd_prorat_factor
1446 WHERE
1447 trx_header_id = last_part_rec.trx_header_id;
1448
1449 END LOOP;
1450
1451 pnp_debug_pkg.log(l_context||' COMPLETE');
1452 pnp_debug_pkg.log('');
1453
1454 END IF;
1455
1456 pnp_debug_pkg.log('');
1457 pnp_debug_pkg.log('--- pn_var_trx_pkg.populate_blended_grp_vol START ---');
1458 pnp_debug_pkg.log('');
1459
1460 EXCEPTION
1461 WHEN OTHERS THEN
1462 pnp_debug_pkg.log
1463 ('**********************************************************************');
1464 pnp_debug_pkg.log('*** ERROR IN calculate_rent ***');
1465 pnp_debug_pkg.log('*** ERROR WHEN: '||l_context||' ***');
1466 pnp_debug_pkg.log
1467 ('**********************************************************************');
1468 RAISE;
1469
1470 END populate_blended_grp_vol;
1471
1472 --------------------------------------------------------------------------------
1473 -- NAME : populate_ytd_pro_vol
1474 -- DESCRIPTION :
1475 -- PURPOSE :
1476 -- INVOKED FROM :
1477 -- ARGUMENTS :
1478 -- REFERENCE : PN_COMMON.debug()
1479 -- HISTORY :
1480 --
1481 -- dd-mon-yyyy name o Created
1482 --------------------------------------------------------------------------------
1483 PROCEDURE populate_ytd_pro_vol( p_var_rent_id IN NUMBER
1484 ,p_proration_rule IN VARCHAR2) IS
1485
1486 /* get VR info */
1487 CURSOR vr_c(p_vr_id IN NUMBER) IS
1488 SELECT
1489 vr.org_id
1490 ,vr.var_rent_id
1491 ,vr.commencement_date
1492 ,vr.termination_date
1493 ,vr.proration_rule
1494 ,vr.cumulative_vol
1495 FROM
1496 pn_var_rents_all vr
1497 WHERE
1498 vr.var_rent_id = p_vr_id;
1499
1500 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
1501
1502 /* get the period details - we use the first 2 periods */
1503 CURSOR periods_c(p_vr_id IN NUMBER) IS
1504 SELECT
1505 period_id
1506 ,start_date
1507 ,end_date
1508 ,partial_period
1509 FROM
1510 pn_var_periods_all
1511 WHERE
1512 var_rent_id = p_vr_id
1513 ORDER BY
1514 start_date;
1515
1516 /* period info */
1517 l_part_prd_id NUMBER;
1518 l_part_prd_start_dt DATE;
1519 l_part_prd_end_dt DATE;
1520 l_part_prd_partial_flag VARCHAR2(1);
1521
1522 l_full_prd_id NUMBER;
1523 l_full_prd_start_dt DATE;
1524 l_full_prd_end_dt DATE;
1525 l_full_prd_partial_flag VARCHAR2(1);
1526
1527 /* get the line items to update */
1528 CURSOR lines_c(p_vr_id IN NUMBER) IS
1529 SELECT
1530 period_id
1531 ,line_item_id
1532 FROM
1533 pn_var_lines_all
1534 WHERE
1535 var_rent_id = p_vr_id AND
1536 bkpt_update_flag = 'Y'
1537 ORDER BY
1538 period_id
1539 ,line_item_id;
1540
1541 /* get the line items to update */
1542 CURSOR lines_cs_c ( p_vr_id IN NUMBER
1543 ,p_part_prd_id IN NUMBER
1544 ,p_full_prd_id IN NUMBER) IS
1545 SELECT
1546 period_id
1547 ,line_item_id
1548 FROM
1549 pn_var_lines_all
1550 WHERE
1551 var_rent_id = p_vr_id AND
1552 bkpt_update_flag = 'Y' AND
1553 period_id NOT IN (p_part_prd_id, p_full_prd_id)
1554 ORDER BY
1555 period_id
1556 ,line_item_id;
1557
1558 /* ytd for STD, NP, FY, LY, FLY */
1559 CURSOR ytd_group_vol_c( p_vr_ID IN NUMBER
1560 ,p_prd_ID IN NUMBER
1561 ,p_line_ID IN NUMBER) IS
1562 SELECT /*+ LEADING(hdr) */
1563 dtl.trx_detail_id
1564 ,SUM(prorated_grp_vol_start) OVER
1565 (PARTITION BY
1566 hdr.period_id
1567 ,hdr.line_item_id
1568 ,hdr.reset_group_id
1569 ,dtl.bkpt_rate
1570 ORDER BY
1571 hdr.calc_prd_start_date
1572 ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_start
1573 ,SUM(prorated_grp_vol_end) OVER
1574 (PARTITION BY
1575 hdr.period_id
1576 ,hdr.line_item_id
1577 ,hdr.reset_group_id
1578 ,dtl.bkpt_rate
1579 ORDER BY
1580 hdr.calc_prd_start_date
1581 ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_end
1582 FROM
1583 pn_var_trx_headers_all hdr
1584 ,pn_var_trx_details_all dtl
1585 WHERE
1586 hdr.var_rent_id = p_vr_id AND
1587 hdr.period_id = p_prd_ID AND
1588 hdr.line_item_id = p_line_ID AND
1589 dtl.trx_header_id = hdr.trx_header_id
1590 ORDER BY
1591 hdr.period_id
1592 ,hdr.line_item_id
1593 ,hdr.calc_prd_start_date;
1594
1595 /* ytd for CYP, CYNP combined period */
1596 CURSOR ytd_group_vol_cs_c( p_vr_ID IN NUMBER
1597 ,p_part_prd_id IN NUMBER
1598 ,p_full_prd_id IN NUMBER) IS
1599 SELECT /*+ LEADING(hdr) */
1600 dtl.trx_detail_id
1601 ,SUM(pr_grp_blended_vol_start) OVER
1602 (PARTITION BY
1603 hdr.line_item_group_id
1604 ,dtl.bkpt_rate
1605 ORDER BY
1606 hdr.calc_prd_start_date
1607 ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_start
1608 ,SUM(pr_grp_blended_vol_end) OVER
1609 (PARTITION BY
1610 hdr.line_item_group_id
1611 ,dtl.bkpt_rate
1612 ORDER BY
1613 hdr.calc_prd_start_date
1614 ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_end
1615 FROM
1616 pn_var_trx_headers_all hdr
1617 ,pn_var_trx_details_all dtl
1618 WHERE
1619 hdr.var_rent_id = p_vr_id AND
1620 hdr.period_id IN (p_part_prd_id, p_full_prd_id) AND
1621 dtl.trx_header_id = hdr.trx_header_id
1622 ORDER BY
1623 hdr.line_item_group_id
1624 ,hdr.calc_prd_start_date;
1625
1626 /* counters */
1627 l_counter1 NUMBER;
1628
1629 trx_detail_t NUM_T;
1630 ytd_grp_vol_start_t NUM_T;
1631 ytd_grp_vol_end_t NUM_T;
1632
1633 BEGIN
1634
1635 IF p_proration_rule IS NULL THEN
1636
1637 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
1638 l_proration_rule := vr_rec.proration_rule;
1639 END LOOP;
1640
1641 ELSE
1642
1643 l_proration_rule := p_proration_rule;
1644
1645 END IF;
1646
1647 l_counter1 := 1;
1648
1649 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
1650 ,pn_var_trx_pkg.G_PRORUL_LY
1651 ,pn_var_trx_pkg.G_PRORUL_FLY
1652 ,pn_var_trx_pkg.G_PRORUL_NP
1653 ,pn_var_trx_pkg.G_PRORUL_STD) THEN
1654
1655 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id) LOOP
1656
1657 trx_detail_t.DELETE;
1658 ytd_grp_vol_start_t.DELETE;
1659 ytd_grp_vol_end_t.DELETE;
1660
1661 OPEN ytd_group_vol_c( p_vr_ID => p_var_rent_id
1662 ,p_prd_ID => line_rec.period_id
1663 ,p_line_ID => line_rec.line_item_id);
1664
1665 FETCH ytd_group_vol_c BULK COLLECT INTO
1666 trx_detail_t
1667 ,ytd_grp_vol_start_t
1668 ,ytd_grp_vol_end_t;
1669
1670 CLOSE ytd_group_vol_c;
1671
1672 FORALL i IN 1..trx_detail_t.COUNT
1673 UPDATE
1674 pn_var_trx_details_all
1675 SET
1676 ytd_group_vol_start = ytd_grp_vol_start_t(i)
1677 ,ytd_group_vol_end = ytd_grp_vol_end_t(i)
1678 WHERE
1679 trx_detail_id = trx_detail_t(i);
1680
1681 END LOOP;
1682
1683 ELSIF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
1684 ,pn_var_trx_pkg.G_PRORUL_CYNP) THEN
1685
1686 /* fetch partial and full period details */
1687 FOR prd_rec IN periods_c(p_vr_id => p_var_rent_id) LOOP
1688
1689 IF l_counter1 = 1 THEN
1690 l_part_prd_id := prd_rec.period_id;
1691 l_part_prd_start_dt := prd_rec.start_date;
1692 l_part_prd_end_dt := prd_rec.end_date;
1693 l_part_prd_partial_flag := prd_rec.partial_period;
1694
1695 ELSIF l_counter1 = 2 THEN
1696 l_full_prd_id := prd_rec.period_id;
1697 l_full_prd_start_dt := prd_rec.start_date;
1698 l_full_prd_end_dt := prd_rec.end_date;
1699 l_full_prd_partial_flag := prd_rec.partial_period;
1700
1701 ELSE
1702 EXIT;
1703
1704 END IF;
1705
1706 l_counter1 := l_counter1 + 1;
1707
1708 END LOOP;
1709
1710 trx_detail_t.DELETE;
1711 ytd_grp_vol_start_t.DELETE;
1712 ytd_grp_vol_end_t.DELETE;
1713
1714 OPEN ytd_group_vol_cs_c ( p_vr_ID => p_var_rent_id
1715 ,p_part_prd_id => l_part_prd_id
1716 ,p_full_prd_id => l_full_prd_id);
1717
1718 FETCH ytd_group_vol_cs_c BULK COLLECT INTO
1719 trx_detail_t
1720 ,ytd_grp_vol_start_t
1721 ,ytd_grp_vol_end_t;
1722
1723 CLOSE ytd_group_vol_cs_c;
1724
1725 FORALL i IN 1..trx_detail_t.COUNT
1726 UPDATE
1727 pn_var_trx_details_all
1728 SET
1729 ytd_group_vol_start = ytd_grp_vol_start_t(i)
1730 ,ytd_group_vol_end = ytd_grp_vol_end_t(i)
1731 WHERE
1732 trx_detail_id = trx_detail_t(i);
1733
1734 FOR line_rec IN lines_cs_c( p_vr_id => p_var_rent_id
1735 ,p_part_prd_id => l_part_prd_id
1736 ,p_full_prd_id => l_full_prd_id) LOOP
1737
1738 trx_detail_t.DELETE;
1739 ytd_grp_vol_start_t.DELETE;
1740 ytd_grp_vol_end_t.DELETE;
1741
1742 OPEN ytd_group_vol_c( p_vr_ID => p_var_rent_id
1743 ,p_prd_ID => line_rec.period_id
1744 ,p_line_ID => line_rec.line_item_id);
1745
1746 FETCH ytd_group_vol_c BULK COLLECT INTO
1747 trx_detail_t
1748 ,ytd_grp_vol_start_t
1749 ,ytd_grp_vol_end_t;
1750
1751 CLOSE ytd_group_vol_c;
1752
1753 FORALL i IN 1..trx_detail_t.COUNT
1754 UPDATE
1755 pn_var_trx_details_all
1756 SET
1757 ytd_group_vol_start = ytd_grp_vol_start_t(i)
1758 ,ytd_group_vol_end = ytd_grp_vol_end_t(i)
1759 WHERE
1760 trx_detail_id = trx_detail_t(i);
1761
1762 END LOOP;
1763
1764 END IF;
1765
1766 EXCEPTION
1767 WHEN OTHERS THEN RAISE;
1768
1769 END populate_ytd_pro_vol;
1770
1771 --------------------------------------------------------------------------------
1772 -- NAME : populate_blended_period_vol
1773 -- DESCRIPTION :
1774 -- PURPOSE :
1775 -- INVOKED FROM :
1776 -- ARGUMENTS :
1777 -- REFERENCE : PN_COMMON.debug()
1778 -- HISTORY :
1779 --
1780 -- dd-mon-yyyy name o Created
1781 --------------------------------------------------------------------------------
1782 PROCEDURE populate_blended_period_vol( p_var_rent_id IN NUMBER
1783 ,p_proration_rule IN VARCHAR2
1784 ,p_calc_method IN VARCHAR2)
1785 IS
1786
1787 /* get VR info */
1788 CURSOR vr_c(p_vr_id IN NUMBER) IS
1789 SELECT
1790 vr.org_id
1791 ,vr.var_rent_id
1792 ,vr.commencement_date
1793 ,vr.termination_date
1794 ,vr.proration_rule
1795 ,vr.cumulative_vol
1796 FROM
1797 pn_var_rents_all vr
1798 WHERE
1799 vr.var_rent_id = p_vr_id;
1800
1801 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
1802 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
1803
1804 /* get the period details - we use the first 2 periods for CYP, CYNP */
1805 CURSOR periods_c(p_vr_id IN NUMBER) IS
1806 SELECT
1807 period_id
1808 FROM
1809 pn_var_periods_all
1810 WHERE
1811 var_rent_id = p_vr_id
1812 ORDER BY
1813 start_date;
1814
1815 /* data structures */
1816 l_period_t NUM_T;
1817
1818 /* counters */
1819 l_prd_counter NUMBER;
1820
1821 /* user defined exceptions */
1822 DO_NOT_PROCESS EXCEPTION;
1823
1824 /* blended period volumes */
1825 CURSOR blended_prd_vol_c( p_vr_id IN NUMBER
1826 ,p_prd_id IN NUMBER) IS
1827 SELECT /*+ LEADING(hdr) */
1828 hdr.period_id
1829 ,hdr.line_item_id
1830 ,hdr.reset_group_id
1831 ,dtl.bkpt_rate
1832 ,SUM(prorated_grp_vol_start) AS blended_period_vol_start
1833 ,SUM(prorated_grp_vol_end) AS blended_period_vol_end
1834 FROM
1835 pn_var_trx_headers_all hdr
1836 ,pn_var_trx_details_all dtl
1837 WHERE
1838 hdr.var_rent_id = p_vr_id AND
1839 hdr.period_id = p_prd_id AND
1840 hdr.line_item_id IN (SELECT
1841 line_item_id
1842 FROM
1843 pn_var_lines_all
1844 WHERE
1845 var_rent_id = p_vr_id AND
1846 period_id = p_prd_id AND
1847 bkpt_update_flag = 'Y') AND
1848 dtl.trx_header_id = hdr.trx_header_id
1849 GROUP BY
1850 hdr.period_id
1851 ,hdr.line_item_id
1852 ,hdr.reset_group_id
1853 ,dtl.bkpt_rate;
1854
1855
1856 -- Get first partial period id
1857 CURSOR check_fst_partial_prd(p_period_id IN NUMBER) IS
1858 SELECT period_id
1859 FROM pn_var_periods_all
1860 WHERE period_id = p_period_id
1861 AND period_num=1
1862 AND partial_period='Y';
1863
1864 /* get the last partial period */
1865 CURSOR last_period_c( p_period_id IN NUMBER) IS
1866 SELECT
1867 prd.period_id
1868 ,prd.partial_period
1869 FROM
1870 pn_var_periods_all prd,
1871 pn_var_rents_all var
1872 WHERE
1873 prd.period_id = p_period_id AND
1874 prd.var_rent_id = var.var_rent_id AND
1875 prd.end_date = var.termination_date AND
1876 prd.partial_period='Y';
1877
1878 BEGIN
1879
1880 IF p_proration_rule IS NULL OR
1881 p_calc_method IS NULL
1882 THEN
1883 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
1884 l_proration_rule := vr_rec.proration_rule;
1885 l_calculation_method := vr_rec.cumulative_vol;
1886 END LOOP;
1887
1888 ELSE
1889 l_proration_rule := p_proration_rule;
1890 l_calculation_method := p_calc_method;
1891
1892 END IF;
1893
1894 l_period_t.DELETE;
1895
1896 OPEN periods_c(p_vr_id => p_var_rent_id);
1897 FETCH periods_c BULK COLLECT INTO l_period_t;
1898 CLOSE periods_c;
1899
1900 FOR prd_rec IN 1..l_period_t.COUNT LOOP
1901
1902 BEGIN
1903
1904 IF prd_rec = 1 THEN
1905 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
1906 ,pn_var_trx_pkg.G_PRORUL_CYNP)
1907 THEN
1908 RAISE DO_NOT_PROCESS;
1909
1910 END IF;
1911
1912 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
1913 ,pn_var_trx_pkg.G_PRORUL_FLY)
1914 THEN
1915 FOR fst_rec IN check_fst_partial_prd(l_period_t(prd_rec)) LOOP
1916 RAISE DO_NOT_PROCESS;
1917 END LOOP;
1918 END IF;
1919
1920 ELSIF prd_rec = 2 THEN
1921
1922 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
1923 ,pn_var_trx_pkg.G_PRORUL_CYNP)
1924 THEN
1925 RAISE DO_NOT_PROCESS;
1926
1927 END IF;
1928
1929 ELSIF prd_rec = l_period_t.COUNT THEN
1930
1931 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_LY
1932 ,pn_var_trx_pkg.G_PRORUL_FLY)
1933 THEN
1934 FOR fst_rec IN last_period_c(l_period_t(prd_rec)) LOOP
1935 RAISE DO_NOT_PROCESS;
1936 END LOOP;
1937
1938 END IF;
1939 END IF;
1940
1941 FOR rec IN blended_prd_vol_c( p_vr_id => p_var_rent_id
1942 ,p_prd_id => l_period_t(prd_rec)) LOOP
1943
1944 UPDATE
1945 pn_var_trx_details_all
1946 SET
1947 blended_period_vol_start = rec.blended_period_vol_start
1948 ,blended_period_vol_end = rec.blended_period_vol_end
1949 WHERE
1950 trx_header_id IN
1951 (SELECT
1952 trx_header_id
1953 FROM
1954 pn_var_trx_headers_all
1955 WHERE
1956 var_rent_id = p_var_rent_id AND
1957 period_id = rec.period_id AND
1958 line_item_id = rec.line_item_id AND
1959 reset_group_id = rec.reset_group_id) AND
1960 bkpt_rate = rec.bkpt_rate;
1961
1962 END LOOP;
1963
1964 EXCEPTION
1965
1966 WHEN DO_NOT_PROCESS THEN NULL;
1967 WHEN OTHERS THEN RAISE;
1968
1969 END;
1970
1971 END LOOP;
1972
1973 EXCEPTION
1974 WHEN OTHERS THEN
1975 RAISE;
1976
1977 END populate_blended_period_vol;
1978
1979 --------------------------------------------------------------------------------
1980 -- NAME : delete_transactions
1981 -- DESCRIPTION :
1982 -- PURPOSE :
1983 -- INVOKED FROM :
1984 -- ARGUMENTS :
1985 -- REFERENCE : PN_COMMON.debug()
1986 -- HISTORY :
1987 --
1988 -- dd-mon-yyyy name o Created
1989 --------------------------------------------------------------------------------
1990 PROCEDURE delete_transactions( p_var_rent_id IN NUMBER
1991 ,p_period_id IN NUMBER
1992 ,p_line_item_id IN NUMBER) IS
1993
1994 BEGIN
1995
1996 IF p_line_item_id IS NOT NULL AND
1997 p_period_id IS NOT NULL AND
1998 p_var_rent_id IS NOT NULL
1999 THEN
2000 pnp_debug_pkg.log('Deleting for lines');
2001 DELETE
2002 pn_var_trx_details_all
2003 WHERE
2004 trx_header_id IN
2005 ( SELECT
2006 trx_header_id
2007 FROM
2008 pn_var_trx_headers_all
2009 WHERE
2010 var_rent_id = p_var_rent_id AND
2011 period_id = p_period_id AND
2012 line_item_id = p_line_item_id );
2013
2014 DELETE
2015 pn_var_trx_headers_all
2016 WHERE
2017 var_rent_id = p_var_rent_id AND
2018 period_id = p_period_id AND
2019 line_item_id = p_line_item_id;
2020
2021 ELSIF p_line_item_id IS NULL AND
2022 p_period_id IS NOT NULL AND
2023 p_var_rent_id IS NOT NULL
2024 THEN
2025 pnp_debug_pkg.log('Deleting for periods');
2026 DELETE
2027 pn_var_trx_details_all
2028 WHERE
2029 trx_header_id IN
2030 ( SELECT
2031 trx_header_id
2032 FROM
2033 pn_var_trx_headers_all
2034 WHERE
2035 var_rent_id = p_var_rent_id AND
2036 period_id = p_period_id);
2037
2038 DELETE
2039 pn_var_trx_headers_all
2040 WHERE
2041 var_rent_id = p_var_rent_id AND
2042 period_id = p_period_id;
2043
2044 ELSIF p_line_item_id IS NULL AND
2045 p_period_id IS NULL AND
2046 p_var_rent_id IS NOT NULL
2047 THEN
2048 pnp_debug_pkg.log('Deleting for VR');
2049 DELETE
2050 pn_var_trx_details_all
2051 WHERE
2052 trx_header_id IN
2053 ( SELECT
2054 trx_header_id
2055 FROM
2056 pn_var_trx_headers_all
2057 WHERE
2058 var_rent_id = p_var_rent_id);
2059
2060 DELETE
2061 pn_var_trx_headers_all
2062 WHERE
2063 var_rent_id = p_var_rent_id;
2064
2065 END IF;
2066
2067 EXCEPTION
2068 WHEN OTHERS THEN RAISE;
2069
2070 END delete_transactions;
2071
2072
2073 --------------------------------------------------------------------------------
2074 -- NAME : populate_transactions
2075 -- DESCRIPTION : inserts/updates the transactions table.
2076 -- PURPOSE :
2077 -- INVOKED FROM :
2078 -- ARGUMENTS :
2079 -- REFERENCE : PN_COMMON.debug()
2080 -- HISTORY :
2081 --
2082 -- dd-mon-yyyy name o Created
2083 -- 23-MAY-2007 Lokesh o Added rounding off for Bug # 6031202
2084 --------------------------------------------------------------------------------
2085 PROCEDURE populate_transactions(p_var_rent_id IN NUMBER) IS
2086
2087 /* get VR info */
2088 CURSOR vr_c(p_vr_id IN NUMBER) IS
2089 SELECT
2090 vr.org_id
2091 ,vr.var_rent_id
2092 ,vr.commencement_date
2093 ,vr.termination_date
2094 ,vr.proration_rule
2095 ,vr.cumulative_vol
2096 FROM
2097 pn_var_rents_all vr
2098 WHERE
2099 vr.var_rent_id = p_vr_id;
2100
2101 /* variables for vr_c */
2102 l_org_id pn_var_rents_all.org_id%TYPE;
2103 l_vr_commencement_date pn_var_rents_all.commencement_date%TYPE;
2104 l_vr_termination_date pn_var_rents_all.termination_date%TYPE;
2105 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
2106 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
2107
2108 /* get the periods that do not exist anymore */
2109 CURSOR chk_for_del_prd_c(p_vr_id IN NUMBER) IS
2110 SELECT
2111 period_id
2112 FROM
2113 pn_var_trx_headers_all
2114 WHERE
2115 var_rent_id = p_vr_id
2116 MINUS
2117 SELECT
2118 period_id
2119 FROM
2120 pn_var_periods_all
2121 WHERE
2122 var_rent_id = p_vr_id AND
2123 status IS NULL;
2124
2125 /* get all periods for the VR */
2126 CURSOR periods_c(p_vr_id IN NUMBER) IS
2127 SELECT
2128 prd.var_rent_id
2129 ,prd.period_id
2130 ,prd.start_date
2131 ,prd.end_date
2132 FROM
2133 pn_var_periods_all prd
2134 WHERE
2135 prd.var_rent_id = p_vr_id AND
2136 prd.status IS NULL
2137 ORDER BY
2138 prd.start_date;
2139
2140 /* get the line items that do not exist anymore in a period */
2141 CURSOR chk_for_del_line_c( p_vr_id IN NUMBER
2142 ,p_prd_id IN NUMBER) IS
2143 SELECT
2144 line_item_id
2145 FROM
2146 pn_var_trx_headers_all
2147 WHERE
2148 var_rent_id = p_vr_id AND
2149 period_id = p_prd_id
2150 MINUS
2151 SELECT
2152 line_item_id
2153 FROM
2154 pn_var_lines_all
2155 WHERE
2156 var_rent_id = p_vr_id AND
2157 period_id = p_prd_id;
2158
2159 -- Get the details of forecasted data
2160 CURSOR for_data_c(ip_vr_id IN NUMBER,
2161 ip_prd_id IN NUMBER
2162 ) IS
2163 SELECT *
2164 FROM pn_var_trx_headers_all
2165 WHERE var_rent_id = ip_vr_id
2166 AND period_id = ip_prd_id;
2167
2168 TYPE for_data IS TABLE OF pn_var_trx_headers_all%ROWTYPE INDEX BY BINARY_INTEGER;
2169 for_data_t for_data;
2170
2171
2172 /* get all groups for a period */
2173 /*
2174 CURSOR groups_c(p_prd_id IN NUMBER) IS
2175 SELECT
2176 grp.grp_date_id
2177 ,grp.grp_start_date
2178 ,grp.grp_end_date
2179 ,grp.group_date
2180 ,grp.invoice_date
2181 ,grp.proration_factor
2182 FROM
2183 pn_var_grp_dates_all grp
2184 WHERE
2185 grp.period_id = p_prd_id
2186 ORDER BY
2187 grp.grp_start_date;
2188 */
2189
2190 CURSOR groups_c(p_prd_id IN NUMBER) IS
2191 SELECT
2192 grp.grp_date_id
2193 ,grp.grp_start_date
2194 ,grp.grp_end_date
2195 ,grp.group_date
2196 ,grp.invoice_date
2197 ,grp.proration_factor
2198 FROM
2199 pn_var_grp_dates_all grp
2200 ,pn_var_periods_all prd
2201 WHERE
2202 prd.period_id = p_prd_id AND
2203 grp.period_id = prd.period_id AND
2204 grp.grp_end_date <= prd.end_date
2205 ORDER BY
2206 grp.grp_start_date;
2207
2208 /* data structures for groups_c */
2209 TYPE GROUPS_CUR_T IS TABLE OF groups_c%ROWTYPE INDEX BY BINARY_INTEGER;
2210 groups_cur_tbl GROUPS_CUR_T;
2211
2212 /* get all line items for a period */
2213 /*CURSOR line_items_c(p_prd_id IN NUMBER) IS
2214 SELECT
2215 line.line_item_id
2216 ,line.line_default_id
2217 FROM
2218 pn_var_lines_all line
2219 WHERE
2220 line.period_id = p_prd_id AND
2221 line.bkpt_update_flag = 'Y' AND
2222 EXISTS (SELECT null
2223 FROM pn_var_bkpts_det_all
2224 WHERE bkpt_header_id IN ( SELECT bkpt_header_id
2225 FROM pn_var_bkpts_head_all
2226 WHERE line_item_id = line.line_item_id))
2227 ORDER BY
2228 line_item_id;*/
2229
2230 CURSOR line_items_c(p_prd_id IN NUMBER) IS
2231 SELECT
2232 line.line_item_id
2233 ,line.line_default_id
2234 FROM
2235 pn_var_lines_all line,
2236 pn_var_bkpts_head_all bph
2237 WHERE
2238 line.period_id = p_prd_id AND
2239 line.bkpt_update_flag = 'Y' AND
2240 bph.period_id = line.period_id AND
2241 EXISTS (SELECT null
2242 FROM pn_var_bkpts_det_all bpd
2243 WHERE bpd.bkpt_header_id = bph.bkpt_header_id
2244 AND rownum = 1)
2245 order BY line_item_id;
2246
2247 /* get all breakpoints for a line item */
2248 CURSOR breakpoints_c(p_line_item_id IN NUMBER) IS
2249 SELECT
2250 bkpt.bkpt_detail_id
2251 ,bkpt.bkpt_start_date
2252 ,bkpt.bkpt_end_date
2253 ,bkpt.group_bkpt_vol_start
2254 ,bkpt.group_bkpt_vol_end
2255 ,bkpt.period_bkpt_vol_start
2256 ,bkpt.period_bkpt_vol_end
2257 ,bkpt.bkpt_rate
2258 FROM
2259 pn_var_bkpts_head_all head
2260 ,pn_var_bkpts_det_all bkpt
2261 WHERE
2262 head.line_item_id = p_line_item_id AND
2263 bkpt.bkpt_header_id = head.bkpt_header_id
2264 ORDER BY
2265 bkpt.bkpt_start_date
2266 ,bkpt.group_bkpt_vol_start;
2267
2268 CURSOR trueup_cur (p_var_rent_id IN NUMBER) IS
2269 SELECT /*+ LEADING(hdr) */
2270 hdr.line_item_id
2271 ,hdr.calc_prd_start_date
2272 ,hdr.calc_prd_end_date
2273 ,hdr.period_id
2274 ,dtls.bkpt_rate
2275 ,hdr.reset_group_id
2276 ,hdr.trueup_rent_due
2277 FROM
2278 pn_var_trx_headers_all hdr
2279 ,pn_var_trx_details_all dtls
2280 WHERE
2281 hdr.trx_header_id = dtls.trx_header_id AND
2282 hdr.var_rent_id = p_var_rent_id
2283 ORDER BY
2284 hdr.line_item_id
2285 ,hdr.calc_prd_start_date
2286 ,dtls.bkpt_rate;
2287
2288
2289 /* data structures for breakpoints_c */
2290 TYPE BKPT_DTLS_T IS TABLE OF breakpoints_c%ROWTYPE INDEX BY BINARY_INTEGER;
2291
2292 TYPE BKPTS_R IS RECORD
2293 ( bkpt_start_date DATE
2294 ,bkpt_end_date DATE
2295 ,bkpt_dtls_tbl BKPT_DTLS_T);
2296
2297 TYPE BKPTS_T IS TABLE OF BKPTS_R INDEX BY BINARY_INTEGER;
2298 bkpts_tbl BKPTS_T;
2299
2300 TYPE TRUEUP_DTLS_T IS TABLE OF trueup_cur%ROWTYPE INDEX BY BINARY_INTEGER;
2301 trueup_table TRUEUP_DTLS_T;
2302
2303 TYPE PERIOD_DTLS_T IS TABLE OF periods_c%ROWTYPE INDEX BY BINARY_INTEGER;
2304 periods_table PERIOD_DTLS_T;
2305
2306 /* counters */
2307 l_counter1 NUMBER;
2308 l_curr_bkpt_ctr NUMBER;
2309
2310 l_bkpt_counter NUMBER;
2311 l_bkpt_dtl_counter NUMBER;
2312
2313 l_prd_counter NUMBER;
2314 l_line_item_counter NUMBER;
2315 l_group_counter NUMBER;
2316
2317 /* other variables */
2318 l_trx_hdr_id NUMBER;
2319 l_trx_dtl_id NUMBER;
2320
2321 l_calc_prd_start_dt DATE;
2322 l_calc_prd_end_dt DATE;
2323
2324 l_proration_factor NUMBER;
2325
2326 l_prorated_grp_vol_start NUMBER;
2327 l_prorated_grp_vol_end NUMBER;
2328
2329 /* flags */
2330 l_trx_create_upd_flag BOOLEAN;
2331
2332 l_line_items_lock4bkpt_t NUM_T;
2333 l_max_end_date DATE;
2334
2335 BEGIN
2336
2337 pnp_debug_pkg.log
2338 ('+++++++++++++ pn_var_trx_pkg.populate_transactions START +++++++++++++');
2339
2340 /* lock the line items */
2341 l_line_items_lock4bkpt_t.DELETE;
2342
2343 OPEN line_items_lock4bkpt_c(p_vr_id => p_var_rent_id);
2344 FETCH line_items_lock4bkpt_c BULK COLLECT INTO l_line_items_lock4bkpt_t;
2345 CLOSE line_items_lock4bkpt_c;
2346 /* get the VR details */
2347 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
2348
2349 l_org_id := vr_rec.org_id;
2350 l_vr_commencement_date := vr_rec.commencement_date;
2351 l_vr_termination_date := vr_rec.termination_date;
2352 l_proration_rule := vr_rec.proration_rule;
2353 l_calculation_method := vr_rec.cumulative_vol;
2354 END LOOP;
2355
2356 g_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(l_org_id),4); /*Bug # 6031202*/
2357
2358 for_data_t.DELETE;
2359 FOR period_rec IN periods_c(p_vr_id => p_var_rent_id) LOOP
2360
2361 FOR rec IN for_data_c(p_var_rent_id, period_rec.period_id) LOOP
2362 for_data_t (for_data_t.COUNT+1):= rec;
2363 END LOOP;
2364 END LOOP;
2365
2366 trueup_table.DELETE;
2367 FOR rec_trueup IN trueup_cur(p_var_rent_id) LOOP
2368 trueup_table (trueup_table.COUNT+1):= rec_trueup;
2369 END LOOP;
2370
2371 periods_table.DELETE;
2372 FOR rec_periods IN periods_c(p_var_rent_id) LOOP
2373 periods_table (periods_table.COUNT+1):= rec_periods;
2374 END LOOP;
2375
2376 /* assume we will not create/update trx */
2377 l_trx_create_upd_flag := FALSE;
2378 /* delete trx records for periods that do not exist anymore */
2379 FOR del_rec IN chk_for_del_prd_c(p_vr_id => p_var_rent_id) LOOP
2380
2381 l_trx_create_upd_flag := TRUE;
2382
2383 pn_var_trx_pkg.delete_transactions
2384 ( p_var_rent_id => p_var_rent_id
2385 ,p_period_id => del_rec.period_id
2386 ,p_line_item_id => NULL);
2387 END LOOP;
2388
2389 /* delete trx records for a contracted period */
2390 BEGIN
2391
2392 DELETE FROM pn_var_trx_headers_all
2393 WHERE
2394 var_rent_id = p_var_rent_id AND
2395 calc_prd_end_date > l_vr_termination_date;
2396
2397 IF SQL%ROWCOUNT > 0 THEN
2398 l_trx_create_upd_flag := TRUE;
2399 END IF;
2400 EXCEPTION
2401
2402 WHEN OTHERS THEN RAISE;
2403
2404 END;
2405
2406
2407 /* init the period counter */
2408 l_prd_counter := 1;
2409 /* loop for all periods,
2410 create TRX headres and details */
2411 FOR period_rec IN periods_c(p_vr_id => p_var_rent_id) LOOP
2412 /* delete trx records for lines that do not exist anymore in this period */
2413 FOR del_rec IN chk_for_del_line_c( p_vr_id => p_var_rent_id
2414 ,p_prd_id => period_rec.period_id) LOOP
2415
2416 l_trx_create_upd_flag := TRUE;
2417
2418 pn_var_trx_pkg.delete_transactions
2419 ( p_var_rent_id => p_var_rent_id
2420 ,p_period_id => period_rec.period_id
2421 ,p_line_item_id => del_rec.line_item_id);
2422 END LOOP;
2423
2424 /* get all the groups for the period, cache */
2425 groups_cur_tbl.DELETE;
2426
2427 OPEN groups_c(p_prd_id => period_rec.period_id);
2428 FETCH groups_c BULK COLLECT INTO groups_cur_tbl;
2429 CLOSE groups_c;
2430 l_line_item_counter := 1;
2431
2432 /* create trx for all line items that have bkpts updated */
2433 FOR line_item_rec IN line_items_c(p_prd_id => period_rec.period_id) LOOP
2434
2435 l_trx_create_upd_flag := TRUE;
2436 pn_var_trx_pkg.delete_transactions
2437 ( p_var_rent_id => p_var_rent_id
2438 ,p_period_id => period_rec.period_id
2439 ,p_line_item_id => line_item_rec.line_item_id);
2440
2441 l_counter1 := 1;
2442 l_bkpt_counter := 1;
2443 l_bkpt_dtl_counter := 1;
2444 bkpts_tbl.DELETE;
2445
2446 /* get bkpts */
2447 FOR bkpt_rec IN breakpoints_c(p_line_item_id => line_item_rec.line_item_id)
2448 LOOP
2449
2450 IF l_counter1 = 1 THEN
2451
2452 bkpts_tbl(l_bkpt_counter).bkpt_start_date := bkpt_rec.bkpt_start_date;
2453 bkpts_tbl(l_bkpt_counter).bkpt_end_date := bkpt_rec.bkpt_end_date;
2454 bkpts_tbl(l_bkpt_counter).bkpt_dtls_tbl(l_bkpt_dtl_counter) := bkpt_rec;
2455
2456 ELSE /* l_counter1 > 1 */
2457
2458 /* if we have stratified bkpt ranges */
2459 IF bkpt_rec.bkpt_start_date = bkpts_tbl(l_bkpt_counter).bkpt_start_date AND
2460 bkpt_rec.bkpt_end_date = bkpts_tbl(l_bkpt_counter).bkpt_end_date
2461 THEN
2462
2463 /* add a bkpt detail */
2464 l_bkpt_dtl_counter := l_bkpt_dtl_counter + 1;
2465 bkpts_tbl(l_bkpt_counter).bkpt_dtls_tbl(l_bkpt_dtl_counter) := bkpt_rec;
2466
2467 /* else if bkpt date range changed */
2468 ELSE
2469
2470 /* reset the bkpt detail counter */
2471 l_bkpt_dtl_counter := 1;
2472 /* add a new bkpt header and a detail */
2473 l_bkpt_counter := l_bkpt_counter + 1;
2474 bkpts_tbl(l_bkpt_counter).bkpt_start_date := bkpt_rec.bkpt_start_date;
2475 bkpts_tbl(l_bkpt_counter).bkpt_end_date := bkpt_rec.bkpt_end_date;
2476 bkpts_tbl(l_bkpt_counter).bkpt_dtls_tbl(l_bkpt_dtl_counter) := bkpt_rec;
2477
2478 END IF;
2479
2480 END IF;
2481
2482 l_counter1 := l_counter1 + 1;
2483
2484 END LOOP; /* loop for all bkpts for a line item */
2485 /* we have the bkpt details */
2486
2487 l_group_counter := 1;
2488
2489 FOR grp_rec IN 1..groups_cur_tbl.COUNT LOOP
2490
2491 l_calc_prd_start_dt := groups_cur_tbl(grp_rec).grp_start_date;
2492 l_calc_prd_end_dt := groups_cur_tbl(grp_rec).grp_end_date;
2493
2494 l_curr_bkpt_ctr := 1;
2495
2496 FOR bkpt_rec IN 1..bkpts_tbl.COUNT LOOP
2497
2498 l_curr_bkpt_ctr := bkpt_rec;
2499
2500 IF bkpts_tbl(bkpt_rec).bkpt_start_date > l_calc_prd_end_dt THEN
2501 /* exit the loop no more intersections possible
2502 - let us go to the next group */
2503 l_curr_bkpt_ctr := bkpt_rec - 1;
2504 EXIT;
2505
2506 ELSIF bkpts_tbl(bkpt_rec).bkpt_start_date
2507 BETWEEN (l_calc_prd_start_dt + 1) AND l_calc_prd_end_dt
2508 THEN
2509
2510 l_calc_prd_end_dt := bkpts_tbl(bkpt_rec - 1).bkpt_end_date;
2511
2512 /* determine proration */
2513 l_proration_factor
2514 := ((l_calc_prd_end_dt - l_calc_prd_start_dt) + 1)
2515 /((groups_cur_tbl(grp_rec).grp_end_date
2516 - groups_cur_tbl(grp_rec).grp_start_date) + 1);
2517
2518 /* need to create TRX headers and details */
2519
2520 /* create header */
2521 pn_var_trx_pkg.insert_trx_hdr
2522 (p_trx_header_id => l_trx_hdr_id
2523 ,p_var_rent_id => period_rec.var_rent_id
2524 ,p_period_id => period_rec.period_id
2525 ,p_line_item_id => line_item_rec.line_item_id
2526 ,p_grp_date_id => groups_cur_tbl(grp_rec).grp_date_id
2527 ,p_calc_prd_start_date => l_calc_prd_start_dt
2528 ,p_calc_prd_end_date => l_calc_prd_end_dt
2529 ,p_var_rent_summ_id => NULL
2530 ,p_line_item_group_id => line_item_rec.line_default_id
2531 ,p_reset_group_id => NULL
2532 ,p_proration_factor => l_proration_factor
2533 ,p_reporting_group_sales => NULL
2534 ,p_prorated_group_sales => NULL
2535 ,p_ytd_sales => NULL
2536 ,p_fy_proration_sales => NULL
2537 ,p_ly_proration_sales => NULL
2538 ,p_percent_rent_due => NULL
2539 ,p_ytd_percent_rent => NULL
2540 ,p_calculated_rent => NULL
2541 ,p_prorated_rent_due => NULL
2542 ,p_invoice_flag => NULL
2543 ,p_org_id => l_org_id
2544 ,p_last_update_date => NULL
2545 ,p_last_updated_by => NULL
2546 ,p_creation_date => NULL
2547 ,p_created_by => NULL
2548 ,p_last_update_login => NULL);
2549
2550 /* create details */
2551 FOR bkpt_dtl_rec IN 1..bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl.COUNT
2552 LOOP
2553
2554 IF l_proration_rule = pn_var_trx_pkg.G_PRORUL_NP THEN
2555 IF l_calculation_method = pn_var_trx_pkg.G_CALC_CUMULATIVE THEN
2556 l_prorated_grp_vol_start
2557 := bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).period_bkpt_vol_start;
2558
2559 l_prorated_grp_vol_end
2560 := NVL(bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).period_bkpt_vol_end, 0);
2561
2562 ELSE
2563 l_prorated_grp_vol_start
2564 := bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_start;
2565
2566 l_prorated_grp_vol_end
2567 := NVL(bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_end, 0);
2568
2569 END IF;
2570
2571 ELSE
2572
2573 l_prorated_grp_vol_start
2574 := bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_start
2575 * groups_cur_tbl(grp_rec).proration_factor
2576 * l_proration_factor;
2577
2578 l_prorated_grp_vol_end
2579 := NVL(bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_end, 0)
2580 * groups_cur_tbl(grp_rec).proration_factor
2581 * l_proration_factor;
2582
2583 END IF;
2584
2585 pn_var_trx_pkg.insert_trx_dtl
2586 (p_trx_detail_id => l_trx_dtl_id
2587 ,p_trx_header_id => l_trx_hdr_id
2588 ,p_bkpt_detail_id => bkpts_tbl(bkpt_rec - 1).
2589 bkpt_dtls_tbl(bkpt_dtl_rec).
2590 bkpt_detail_id
2591 ,p_bkpt_rate => bkpts_tbl(bkpt_rec - 1).
2592 bkpt_dtls_tbl(bkpt_dtl_rec).
2593 bkpt_rate
2594 ,p_prorated_grp_vol_start => l_prorated_grp_vol_start
2595 ,p_prorated_grp_vol_end => l_prorated_grp_vol_end
2596 ,p_fy_pr_grp_vol_start => NULL
2597 ,p_fy_pr_grp_vol_end => NULL
2598 ,p_ly_pr_grp_vol_start => NULL
2599 ,p_ly_pr_grp_vol_end => NULL
2600 ,p_pr_grp_blended_vol_start => NULL
2601 ,p_pr_grp_blended_vol_end => NULL
2602 ,p_ytd_group_vol_start => NULL
2603 ,p_ytd_group_vol_end => NULL
2604 ,p_blended_period_vol_start => NULL
2605 ,p_blended_period_vol_end => NULL
2606 ,p_org_id => l_org_id
2607 ,p_last_update_date => NULL
2608 ,p_last_updated_by => NULL
2609 ,p_creation_date => NULL
2610 ,p_created_by => NULL
2611 ,p_last_update_login => NULL);
2612
2613 END LOOP; /* FOR bkpt_dtl_rec IN 0..bkpts_tbl(bkpt_rec).bkpt_dtls_tbl.COUNT */
2614
2615 l_calc_prd_start_dt := bkpts_tbl(bkpt_rec).bkpt_start_date;
2616 l_calc_prd_end_dt := groups_cur_tbl(grp_rec).grp_end_date;
2617
2618 END IF;
2619
2620 END LOOP; /* loop for breakpoints */
2621
2622 /* determine proration */
2623 l_proration_factor
2624 := ((l_calc_prd_end_dt - l_calc_prd_start_dt) + 1)
2625 /((groups_cur_tbl(grp_rec).grp_end_date
2626 - groups_cur_tbl(grp_rec).grp_start_date) + 1);
2627
2628 /* need to create TRX headers and details */
2629
2630 /* create header */
2631 pn_var_trx_pkg.insert_trx_hdr
2632 (p_trx_header_id => l_trx_hdr_id
2633 ,p_var_rent_id => period_rec.var_rent_id
2634 ,p_period_id => period_rec.period_id
2635 ,p_line_item_id => line_item_rec.line_item_id
2636 ,p_grp_date_id => groups_cur_tbl(grp_rec).grp_date_id
2637 ,p_calc_prd_start_date => l_calc_prd_start_dt
2638 ,p_calc_prd_end_date => l_calc_prd_end_dt
2639 ,p_var_rent_summ_id => NULL
2640 ,p_line_item_group_id => line_item_rec.line_default_id
2641 ,p_reset_group_id => NULL
2642 ,p_proration_factor => l_proration_factor
2643 ,p_reporting_group_sales => NULL
2644 ,p_prorated_group_sales => NULL
2645 ,p_ytd_sales => NULL
2646 ,p_fy_proration_sales => NULL
2647 ,p_ly_proration_sales => NULL
2648 ,p_percent_rent_due => NULL
2649 ,p_ytd_percent_rent => NULL
2650 ,p_calculated_rent => NULL
2651 ,p_prorated_rent_due => NULL
2652 ,p_invoice_flag => NULL
2653 ,p_org_id => l_org_id
2654 ,p_last_update_date => NULL
2655 ,p_last_updated_by => NULL
2656 ,p_creation_date => NULL
2657 ,p_created_by => NULL
2658 ,p_last_update_login => NULL);
2659
2660 /* create details */
2661 FOR bkpt_dtl_rec IN 1..bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl.COUNT LOOP
2662
2663 IF l_proration_rule = pn_var_trx_pkg.G_PRORUL_NP THEN
2664 IF l_calculation_method = pn_var_trx_pkg.G_CALC_CUMULATIVE THEN
2665 l_prorated_grp_vol_start
2666 := bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).period_bkpt_vol_start;
2667 l_prorated_grp_vol_end
2668 := NVL(bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).period_bkpt_vol_end, 0);
2669
2670 ELSE
2671 l_prorated_grp_vol_start
2672 := bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_start;
2673 l_prorated_grp_vol_end
2674 := NVL(bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_end, 0);
2675 END IF;
2676
2677
2678 ELSE
2679
2680 l_prorated_grp_vol_start
2681 := bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_start
2682 * groups_cur_tbl(grp_rec).proration_factor
2683 * l_proration_factor;
2684
2685 l_prorated_grp_vol_end
2686 := NVL(bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_end, 0)
2687 * groups_cur_tbl(grp_rec).proration_factor
2688 * l_proration_factor;
2689
2690 END IF;
2691
2692 pn_var_trx_pkg.insert_trx_dtl
2693 (p_trx_detail_id => l_trx_dtl_id
2694 ,p_trx_header_id => l_trx_hdr_id
2695 ,p_bkpt_detail_id => bkpts_tbl(l_curr_bkpt_ctr).
2696 bkpt_dtls_tbl(bkpt_dtl_rec).
2697 bkpt_detail_id
2698 ,p_bkpt_rate => bkpts_tbl(l_curr_bkpt_ctr).
2699 bkpt_dtls_tbl(bkpt_dtl_rec).
2700 bkpt_rate
2701 ,p_prorated_grp_vol_start => l_prorated_grp_vol_start
2702 ,p_prorated_grp_vol_end => l_prorated_grp_vol_end
2703 ,p_fy_pr_grp_vol_start => NULL
2704 ,p_fy_pr_grp_vol_end => NULL
2705 ,p_ly_pr_grp_vol_start => NULL
2706 ,p_ly_pr_grp_vol_end => NULL
2707 ,p_pr_grp_blended_vol_start => NULL
2708 ,p_pr_grp_blended_vol_end => NULL
2709 ,p_ytd_group_vol_start => NULL
2710 ,p_ytd_group_vol_end => NULL
2711 ,p_blended_period_vol_start => NULL
2712 ,p_blended_period_vol_end => NULL
2713 ,p_org_id => l_org_id
2714 ,p_last_update_date => NULL
2715 ,p_last_updated_by => NULL
2716 ,p_creation_date => NULL
2717 ,p_created_by => NULL
2718 ,p_last_update_login => NULL);
2719
2720 END LOOP; /* FOR bkpt_dtl_rec IN 0..bkpts_tbl(bkpt_rec).bkpt_dtls_tbl.COUNT */
2721
2722 l_group_counter := l_group_counter + 1;
2723
2724 END LOOP; /* loop for all groups */
2725
2726 l_line_item_counter := l_line_item_counter + 1;
2727
2728 END LOOP; /* loop for all line items in a period */
2729
2730 l_prd_counter := l_prd_counter + 1;
2731
2732
2733 END LOOP; /* loop for all periods */
2734
2735 /* get the grp IDs right if any trx was updated */
2736 IF l_trx_create_upd_flag THEN
2737 pnp_debug_pkg.log('Trx updated');
2738 /* groups the lines across the periods */
2739 pn_var_trx_pkg.populate_line_grp_id(p_var_rent_id => p_var_rent_id);
2740
2741 /* populate the reset group IDs */
2742 pn_var_trx_pkg.populate_reset_grp_id(p_var_rent_id => p_var_rent_id);
2743 /* populate fy_pr_grp_vol_start - end,
2744 ly_pr_grp_vol_start - end,
2745 invoice_flag
2746 for FY, LY, FLY */
2747 FOR i in 1..for_data_t.COUNT LOOP
2748 pnp_debug_pkg.log('line_item_id:'||for_data_t(i).line_item_id);
2749 pnp_debug_pkg.log('grp_date_id:'||for_data_t(i).grp_date_id);
2750 pnp_debug_pkg.log('reset_group_id:'||for_data_t(i).reset_group_id);
2751 pnp_debug_pkg.log('var_rent_id:'||for_data_t(i).var_rent_id);
2752 pnp_debug_pkg.log('REPORTING_GROUP_SALES_FOR:'||for_data_t(i).REPORTING_GROUP_SALES_FOR);
2753 pnp_debug_pkg.log('CALCULATED_RENT_FOR:'||for_data_t(i).CALCULATED_RENT_FOR);
2754
2755 UPDATE
2756 pn_var_trx_headers_all
2757 SET
2758 REPORTING_GROUP_SALES_FOR = for_data_t(i).REPORTING_GROUP_SALES_FOR
2759 ,PRORATED_GROUP_SALES_FOR = for_data_t(i).PRORATED_GROUP_SALES_FOR
2760 ,YTD_SALES_FOR = for_data_t(i).YTD_SALES_FOR
2761 ,CALCULATED_RENT_FOR = round(for_data_t(i).CALCULATED_RENT_FOR,g_precision) /*Bug # 6031202*/
2762 ,PERCENT_RENT_DUE_FOR = round(for_data_t(i).PERCENT_RENT_DUE_FOR,g_precision)
2763 ,YTD_PERCENT_RENT_FOR = round(for_data_t(i).YTD_PERCENT_RENT_FOR,g_precision)
2764 WHERE var_rent_id = for_data_t(i).var_rent_id AND
2765 grp_date_id = for_data_t(i).grp_date_id AND
2766 line_item_id = for_data_t(i).line_item_id AND
2767 reset_group_id = for_data_t(i).reset_group_id;
2768 END LOOP;
2769
2770 IF l_proration_rule = pn_var_trx_pkg.G_PRORUL_LY THEN
2771
2772 /* populate ly_pr_grp_vol_start - end, invoice_flag */
2773 pn_var_trx_pkg.populate_ly_pro_vol
2774 ( p_var_rent_id => p_var_rent_id
2775 ,p_proration_rule => l_proration_rule
2776 ,p_vr_commencement_dt => l_vr_commencement_date
2777 ,p_vr_termination_dt => l_vr_termination_date);
2778
2779 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FY THEN
2780
2781 /* populate fy_pr_grp_vol_start - end, invoice_flag */
2782 pn_var_trx_pkg.populate_fy_pro_vol
2783 ( p_var_rent_id => p_var_rent_id
2784 ,p_proration_rule => l_proration_rule
2785 ,p_vr_commencement_dt => l_vr_commencement_date
2786 ,p_vr_termination_dt => l_vr_termination_date);
2787
2788 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FLY THEN
2789
2790 /* populate ly_pr_grp_vol_start - end, invoice_flag */
2791 pn_var_trx_pkg.populate_ly_pro_vol
2792 ( p_var_rent_id => p_var_rent_id
2793 ,p_proration_rule => l_proration_rule
2794 ,p_vr_commencement_dt => l_vr_commencement_date
2795 ,p_vr_termination_dt => l_vr_termination_date);
2796
2797 /* populate fy_pr_grp_vol_start - end, invoice_flag */
2798 pn_var_trx_pkg.populate_fy_pro_vol
2799 ( p_var_rent_id => p_var_rent_id
2800 ,p_proration_rule => l_proration_rule
2801 ,p_vr_commencement_dt => l_vr_commencement_date
2802 ,p_vr_termination_dt => l_vr_termination_date);
2803
2804 ELSIF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
2805 ,pn_var_trx_pkg.G_PRORUL_CYNP) THEN
2806
2807 /* populate blended_period/grp_vol_start - end */
2808 pn_var_trx_pkg.populate_blended_grp_vol
2809 ( p_var_rent_id => p_var_rent_id
2810 ,p_proration_rule => l_proration_rule);
2811
2812 END IF;
2813
2814 /* ALWAYS populate the ytd_group_vol_start - end - useful */
2815 pn_var_trx_pkg.populate_ytd_pro_vol
2816 ( p_var_rent_id => p_var_rent_id
2817 ,p_proration_rule => l_proration_rule);
2818
2819 IF l_calculation_method = pn_var_trx_pkg.G_CALC_CUMULATIVE THEN
2820 /* populate the blended_period_vol_start - end */
2821 pn_var_trx_pkg.populate_blended_period_vol
2822 ( p_var_rent_id => p_var_rent_id
2823 ,p_proration_rule => l_proration_rule
2824 ,p_calc_method => l_calculation_method);
2825
2826 END IF;
2827
2828 END IF;
2829
2830 FOR j IN 1..periods_table.COUNT LOOP
2831
2832 l_max_end_date := periods_table(j).start_date;
2833
2834 FOR i IN 1..trueup_table.COUNT LOOP
2835 IF l_max_end_date < trueup_table(i).calc_prd_end_date AND
2836 trueup_table(i).period_id = periods_table(j).period_id THEN
2837 l_max_end_date := trueup_table(i).calc_prd_end_date;
2838 END IF;
2839 END LOOP;
2840
2841 FOR i IN 1..trueup_table.COUNT LOOP
2842 IF periods_table(j).end_date = l_max_end_date THEN
2843 /* Added var_rent_id filter to improve the performance */
2844 UPDATE
2845 pn_var_trx_headers_all
2846 SET
2847 trueup_rent_due = round(trueup_table(i).trueup_rent_due,g_precision) /*Bug # 6031202*/
2848 WHERE
2849 var_rent_id = p_var_rent_id AND
2850 line_item_id = trueup_table(i).line_item_id AND
2851 calc_prd_start_date = trueup_table(i).calc_prd_start_date AND
2852 calc_prd_end_date = trueup_table(i).calc_prd_end_date AND
2853 reset_group_id = trueup_table(i).reset_group_id AND
2854 period_id = periods_table(j).period_id;
2855 END IF;
2856 END LOOP;
2857
2858 END LOOP;
2859
2860 /* reset the bkpt_update_flag */
2861 FORALL line_rec IN 1..l_line_items_lock4bkpt_t.COUNT
2862 UPDATE
2863 pn_var_lines_all
2864 SET
2865 bkpt_update_flag = NULL
2866 ,sales_vol_update_flag = 'Y'
2867 WHERE
2868 line_item_id = l_line_items_lock4bkpt_t(line_rec);
2869
2870 /* UN-lock the line items */
2871 COMMIT;
2872
2873 EXCEPTION
2874 WHEN OTHERS THEN RAISE;
2875
2876 END populate_transactions;
2877
2878 /* ----------------------------------------------------------------------
2879 -------------------- PROCEDURES TO POPULATE SALES --------------------
2880 ---------------------------------------------------------------------- */
2881
2882 --------------------------------------------------------------------------------
2883 -- NAME : get_calc_prd_sales
2884 -- DESCRIPTION :
2885 -- PURPOSE :
2886 -- INVOKED FROM :
2887 -- ARGUMENTS :
2888 -- REFERENCE : PN_COMMON.debug()
2889 -- HISTORY :
2890 --
2891 -- dd-mon-yyyy name o Created
2892 --------------------------------------------------------------------------------
2893 PROCEDURE get_calc_prd_sales( p_var_rent_id IN NUMBER
2894 ,p_period_id IN NUMBER
2895 ,p_line_item_id IN NUMBER
2896 ,p_grp_date_id IN NUMBER
2897 ,p_start_date IN DATE
2898 ,p_end_date IN DATE
2899 ,x_pro_sales OUT NOCOPY NUMBER
2900 ,x_sales OUT NOCOPY NUMBER) IS
2901
2902 /* get volumes for a calculation period */
2903 CURSOR vol_hist_c1 ( p_prd_id IN NUMBER
2904 ,p_line_id IN NUMBER
2905 ,p_grp_id IN NUMBER) IS
2906 SELECT
2907 SUM(actual_amount) AS calc_prd_actual_volume
2908 FROM
2909 pn_var_vol_hist_all sales
2910 WHERE
2911 sales.period_id = p_prd_id AND
2912 sales.line_item_id = p_line_id AND
2913 sales.grp_date_id = p_grp_id AND
2914 vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
2915
2916 /* get volumes for a calculation sub-period */
2917 CURSOR vol_hist_c2 ( p_prd_id IN NUMBER
2918 ,p_line_id IN NUMBER
2919 ,p_grp_id IN NUMBER
2920 ,p_start_dt IN DATE
2921 ,p_end_dt IN DATE) IS
2922 SELECT
2923 sales.actual_amount
2924 ,sales.start_date
2925 ,sales.end_date
2926 FROM
2927 pn_var_vol_hist_all sales
2928 WHERE
2929 sales.period_id = p_prd_id AND
2930 sales.line_item_id = p_line_id AND
2931 sales.grp_date_id = p_grp_id AND
2932 sales.start_date <= p_end_dt AND
2933 sales.end_date >= p_start_dt AND
2934 vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
2935
2936 /* get grp dates */
2937 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
2938 SELECT
2939 grp.grp_start_date
2940 ,grp.grp_end_date
2941 FROM
2942 pn_var_grp_dates_all grp
2943 WHERE
2944 grp.grp_date_id = p_grp_id;
2945
2946 l_grp_start_date DATE;
2947 l_grp_end_date DATE;
2948
2949 l_calc_prd_sales NUMBER;
2950 l_pro_calc_prd_sales NUMBER;
2951
2952 BEGIN
2953
2954 /* get group / calc period dates */
2955 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
2956 l_grp_start_date := grp_rec.grp_start_date;
2957 l_grp_end_date := grp_rec.grp_end_date;
2958 END LOOP;
2959
2960 l_calc_prd_sales := 0;
2961 l_pro_calc_prd_sales := 0;
2962
2963 /* get all APPROVED sales for a group / calc period */
2964 FOR sales_rec IN vol_hist_c1 ( p_prd_id => p_period_id
2965 ,p_line_id => p_line_item_id
2966 ,p_grp_id => p_grp_date_id)
2967 LOOP
2968 l_calc_prd_sales := l_calc_prd_sales + sales_rec.calc_prd_actual_volume;
2969 END LOOP;
2970
2971 /* if calc sub period dates are same as grp / calc period start-end dates */
2972 IF l_grp_start_date = p_start_date AND
2973 l_grp_end_date = p_end_date
2974 THEN
2975
2976 /* then prorated sales = total sales */
2977 l_pro_calc_prd_sales := l_calc_prd_sales;
2978
2979 ELSE
2980
2981 /* else, sum all sales to get the prorated sales */
2982 FOR sales_rec IN vol_hist_c2 ( p_prd_id => p_period_id
2983 ,p_line_id => p_line_item_id
2984 ,p_grp_id => p_grp_date_id
2985 ,p_start_dt => p_start_date
2986 ,p_end_dt => p_end_date)
2987 LOOP
2988
2989 /* if sales volume dates between calc sub period dates */
2990 IF sales_rec.start_date >= p_start_date AND
2991 sales_rec.end_date <= p_end_date
2992 THEN
2993 /* consider full volume */
2994 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.actual_amount;
2995
2996 /* else if sales volume dates overlap calc sub period dates */
2997 ELSIF sales_rec.start_date <= p_end_date AND
2998 sales_rec.end_date >= p_start_date
2999 THEN
3000 /* then consider prorated volume */
3001 l_pro_calc_prd_sales
3002 := l_pro_calc_prd_sales
3003 + sales_rec.actual_amount
3004 * ((LEAST(sales_rec.end_date, p_end_date)
3005 - GREATEST(sales_rec.start_date, p_start_date)) + 1)
3006 / ((sales_rec.end_date - sales_rec.start_date) + 1);
3007
3008 END IF;
3009
3010 END LOOP;
3011
3012 END IF;
3013
3014 x_pro_sales := l_pro_calc_prd_sales;
3015 x_sales := l_calc_prd_sales;
3016
3017 EXCEPTION
3018 WHEN OTHERS THEN RAISE;
3019
3020 END get_calc_prd_sales;
3021
3022 --------------------------------------------------------------------------------
3023 -- NAME : get_calc_prd_sales
3024 -- DESCRIPTION :
3025 -- PURPOSE :
3026 -- INVOKED FROM :
3027 -- ARGUMENTS :
3028 -- REFERENCE : PN_COMMON.debug()
3029 -- HISTORY :
3030 --
3031 -- dd-mon-yyyy name o Created
3032 --------------------------------------------------------------------------------
3033 FUNCTION get_calc_prd_sales( p_var_rent_id IN NUMBER
3034 ,p_period_id IN NUMBER
3035 ,p_line_item_id IN NUMBER
3036 ,p_grp_date_id IN NUMBER
3037 ,p_start_date IN DATE
3038 ,p_end_date IN DATE)
3039 RETURN NUMBER IS
3040
3041 /* get volumes for a calculation period */
3042 CURSOR vol_hist_c1 ( p_prd_id IN NUMBER
3043 ,p_line_id IN NUMBER
3044 ,p_grp_id IN NUMBER) IS
3045 SELECT
3046 SUM(actual_amount) AS calc_prd_actual_volume
3047 FROM
3048 pn_var_vol_hist_all sales
3049 WHERE
3050 sales.period_id = p_prd_id AND
3051 sales.line_item_id = p_line_id AND
3052 sales.grp_date_id = p_grp_id AND
3053 vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
3054
3055 /* get volumes for a calculation sub-period */
3056 CURSOR vol_hist_c2 ( p_prd_id IN NUMBER
3057 ,p_line_id IN NUMBER
3058 ,p_grp_id IN NUMBER
3059 ,p_start_dt IN DATE
3060 ,p_end_dt IN DATE) IS
3061 SELECT
3062 sales.actual_amount
3063 ,sales.start_date
3064 ,sales.end_date
3065 FROM
3066 pn_var_vol_hist_all sales
3067 WHERE
3068 sales.period_id = p_prd_id AND
3069 sales.line_item_id = p_line_id AND
3070 sales.grp_date_id = p_grp_id AND
3071 sales.start_date <= p_end_dt AND
3072 sales.end_date >= p_start_dt AND
3073 vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
3074
3075 /* get grp dates */
3076 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
3077 SELECT
3078 grp.grp_start_date
3079 ,grp.grp_end_date
3080 FROM
3081 pn_var_grp_dates_all grp
3082 WHERE
3083 grp.grp_date_id = p_grp_id;
3084
3085 l_grp_start_date DATE;
3086 l_grp_end_date DATE;
3087
3088 l_calc_prd_sales NUMBER;
3089 l_pro_calc_prd_sales NUMBER;
3090
3091 BEGIN
3092
3093 /* get group / calc period dates */
3094 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
3095 l_grp_start_date := grp_rec.grp_start_date;
3096 l_grp_end_date := grp_rec.grp_end_date;
3097 END LOOP;
3098
3099 l_pro_calc_prd_sales := 0;
3100
3101 /* if calc sub period dates are same as grp / calc period start-end dates */
3102 IF l_grp_start_date = p_start_date AND
3103 l_grp_end_date = p_end_date
3104 THEN
3105
3106 /* get all APPROVED sales for a group / calc period
3107 prorated sales = total sales */
3108 FOR sales_rec IN vol_hist_c1 ( p_prd_id => p_period_id
3109 ,p_line_id => p_line_item_id
3110 ,p_grp_id => p_grp_date_id)
3111 LOOP
3112 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.calc_prd_actual_volume;
3113 END LOOP;
3114
3115 ELSE
3116
3117 /* else, sum all sales to get the prorated sales */
3118 FOR sales_rec IN vol_hist_c2 ( p_prd_id => p_period_id
3119 ,p_line_id => p_line_item_id
3120 ,p_grp_id => p_grp_date_id
3121 ,p_start_dt => p_start_date
3122 ,p_end_dt => p_end_date)
3123 LOOP
3124
3125 /* if sales volume dates between calc sub period dates */
3126 IF sales_rec.start_date >= p_start_date AND
3127 sales_rec.end_date <= p_end_date
3128 THEN
3129 /* consider full volume */
3130 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.actual_amount;
3131
3132 /* else if sales volume dates overlap calc sub period dates */
3133 ELSIF sales_rec.start_date <= p_end_date AND
3134 sales_rec.end_date >= p_start_date
3135 THEN
3136 /* then consider prorated volume */
3137 l_pro_calc_prd_sales
3138 := l_pro_calc_prd_sales
3139 + sales_rec.actual_amount
3140 * ((LEAST(sales_rec.end_date, p_end_date)
3141 - GREATEST(sales_rec.start_date, p_start_date)) + 1)
3142 / ((sales_rec.end_date - sales_rec.start_date) + 1);
3143
3144 END IF;
3145
3146 END LOOP;
3147
3148 END IF;
3149
3150 RETURN l_pro_calc_prd_sales;
3151
3152 EXCEPTION
3153 WHEN OTHERS THEN RAISE;
3154
3155 END get_calc_prd_sales;
3156
3157 --------------------------------------------------------------------------------
3158 -- NAME : populate_ly_pro_sales
3159 -- DESCRIPTION :
3160 -- PURPOSE :
3161 -- INVOKED FROM :
3162 -- ARGUMENTS :
3163 -- REFERENCE : PN_COMMON.debug()
3164 -- HISTORY :
3165 --
3166 -- dd-mon-yyyy name o Created
3167 --------------------------------------------------------------------------------
3168 PROCEDURE populate_ly_pro_sales( p_var_rent_id IN NUMBER
3169 ,p_proration_rule IN VARCHAR2
3170 ,p_vr_commencement_dt IN DATE
3171 ,p_vr_termination_dt IN DATE) IS
3172
3173 /* get VR details */
3174 CURSOR vr_c(p_vr_id IN NUMBER) IS
3175 SELECT
3176 vr.var_rent_id
3177 ,vr.commencement_date
3178 ,vr.termination_date
3179 ,vr.proration_rule
3180 FROM
3181 pn_var_rents_all vr
3182 WHERE
3183 vr.var_rent_id = p_vr_id;
3184
3185 l_vr_commencement_date DATE;
3186 l_vr_termination_date DATE;
3187 l_vr_proration_rule VARCHAR2(30);
3188 l_ly_start_date DATE;
3189
3190 /* get the last partial period */
3191 CURSOR last_period_c( p_vr_id IN NUMBER
3192 ,p_term_date IN DATE) IS
3193 SELECT
3194 prd.period_id
3195 ,prd.partial_period
3196 FROM
3197 pn_var_periods_all prd
3198 WHERE
3199 prd.var_rent_id = p_var_rent_id AND
3200 prd.end_date = p_term_date;
3201
3202 l_last_period_id NUMBER;
3203 l_partial_period VARCHAR2(1);
3204
3205 BEGIN
3206
3207 /* get VR details */
3208 IF p_proration_rule IS NULL OR
3209 p_vr_commencement_dt IS NULL OR
3210 p_vr_termination_dt IS NULL
3211 THEN
3212 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
3213 l_vr_commencement_date := vr_rec.commencement_date;
3214 l_vr_termination_date := vr_rec.termination_date;
3215 l_vr_proration_rule := vr_rec.proration_rule;
3216 END LOOP;
3217 ELSE
3218 l_vr_commencement_date := p_vr_commencement_dt;
3219 l_vr_termination_date := p_vr_termination_dt;
3220 l_vr_proration_rule := p_proration_rule;
3221 END IF;
3222
3223 l_ly_start_date := ADD_MONTHS(l_vr_termination_date, -12) + 1;
3224
3225 IF l_vr_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_LY
3226 ,pn_var_trx_pkg.G_PRORUL_FLY) THEN
3227
3228 FOR prd_rec IN last_period_c( p_vr_id => p_var_rent_id
3229 ,p_term_date => l_vr_termination_date)
3230 LOOP
3231 l_last_period_id := prd_rec.period_id;
3232 l_partial_period := NVL(prd_rec.partial_period, 'N');
3233 END LOOP;
3234
3235 /* init ly_proration_sales */
3236 UPDATE
3237 pn_var_trx_headers_all
3238 SET
3239 ly_proration_sales = NULL
3240 WHERE
3241 var_rent_id = p_var_rent_id;
3242
3243 IF l_partial_period = 'Y' THEN
3244
3245 /* populate ly_proration_sales */
3246 UPDATE
3247 pn_var_trx_headers_all hdr
3248 SET
3249 hdr.ly_proration_sales = hdr.prorated_group_sales
3250 WHERE
3251 hdr.var_rent_id = p_var_rent_id AND
3252 hdr.calc_prd_start_date >= l_ly_start_date;
3253
3254 /* populate ly_proration_sales if LY start date does not
3255 coincide with a calc prd start date */
3256 UPDATE
3257 pn_var_trx_headers_all
3258 SET
3259 ly_proration_sales
3260 = pn_var_trx_pkg.get_calc_prd_sales( var_rent_id
3261 ,period_id
3262 ,line_item_id
3263 ,grp_date_id
3264 ,l_ly_start_date
3265 ,calc_prd_end_date)
3266 WHERE
3267 var_rent_id = p_var_rent_id AND
3268 l_ly_start_date BETWEEN (calc_prd_start_date + 1)
3269 AND calc_prd_end_date;
3270
3271 END IF;
3272
3273 END IF;
3274
3275 EXCEPTION
3276 WHEN OTHERS THEN RAISE;
3277
3278 END populate_ly_pro_sales;
3279
3280 --------------------------------------------------------------------------------
3281 -- NAME : populate_fy_pro_sales
3282 -- DESCRIPTION :
3283 -- PURPOSE :
3284 -- INVOKED FROM :
3285 -- ARGUMENTS :
3286 -- REFERENCE : PN_COMMON.debug()
3287 -- HISTORY :
3288 --
3289 -- dd-mon-yyyy name o Created
3290 --------------------------------------------------------------------------------
3291 PROCEDURE populate_fy_pro_sales( p_var_rent_id IN NUMBER
3292 ,p_proration_rule IN VARCHAR2
3293 ,p_vr_commencement_dt IN DATE
3294 ,p_vr_termination_dt IN DATE) IS
3295
3296 /* get VR details */
3297 CURSOR vr_c(p_vr_id IN NUMBER) IS
3298 SELECT
3299 vr.var_rent_id
3300 ,vr.commencement_date
3301 ,vr.termination_date
3302 ,vr.proration_rule
3303 FROM
3304 pn_var_rents_all vr
3305 WHERE
3306 vr.var_rent_id = p_vr_id;
3307
3308 l_vr_commencement_date DATE;
3309 l_vr_termination_date DATE;
3310 l_vr_proration_rule VARCHAR2(30);
3311 l_fy_end_date DATE;
3312
3313 /* get the first partial period */
3314 CURSOR first_period_c( p_vr_id IN NUMBER
3315 ,p_comm_date IN DATE) IS
3316 SELECT
3317 prd.period_id
3318 ,prd.partial_period
3319 FROM
3320 pn_var_periods_all prd
3321 WHERE
3322 prd.var_rent_id = p_var_rent_id AND
3323 prd.start_date = p_comm_date;
3324
3325 l_first_period_id NUMBER;
3326 l_partial_period VARCHAR2(1);
3327
3328 BEGIN
3329
3330 /* get VR details */
3331 IF p_proration_rule IS NULL OR
3332 p_vr_commencement_dt IS NULL OR
3333 p_vr_termination_dt IS NULL
3334 THEN
3335 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
3336 l_vr_commencement_date := vr_rec.commencement_date;
3337 l_vr_termination_date := vr_rec.termination_date;
3338 l_vr_proration_rule := vr_rec.proration_rule;
3339 END LOOP;
3340 ELSE
3341 l_vr_commencement_date := p_vr_commencement_dt;
3342 l_vr_termination_date := p_vr_termination_dt;
3343 l_vr_proration_rule := p_proration_rule;
3344 END IF;
3345
3346 l_fy_end_date := ADD_MONTHS(l_vr_commencement_date, 12) - 1;
3347
3348 IF l_vr_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
3349 ,pn_var_trx_pkg.G_PRORUL_FLY) THEN
3350
3351 FOR prd_rec IN first_period_c( p_vr_id => p_var_rent_id
3352 ,p_comm_date => l_vr_commencement_date)
3353 LOOP
3354 l_first_period_id := prd_rec.period_id;
3355 l_partial_period := NVL(prd_rec.partial_period, 'N');
3356 END LOOP;
3357
3358 /* init ly_proration_sales */
3359 UPDATE
3360 pn_var_trx_headers_all
3361 SET
3362 fy_proration_sales = NULL
3363 WHERE
3364 var_rent_id = p_var_rent_id;
3365
3366 IF l_partial_period = 'Y' THEN
3367 /* populate ly_proration_sales */
3368 UPDATE
3369 pn_var_trx_headers_all hdr
3370 SET
3371 hdr.fy_proration_sales = hdr.prorated_group_sales
3372 WHERE
3373 hdr.var_rent_id = p_var_rent_id AND
3374 hdr.calc_prd_end_date <= l_fy_end_date;
3375
3376 /* populate fy_proration_sales if FY end date does not
3377 coincide with a calc prd end date */
3378 UPDATE
3379 pn_var_trx_headers_all
3380 SET
3381 fy_proration_sales
3382 = pn_var_trx_pkg.get_calc_prd_sales( var_rent_id
3383 ,period_id
3384 ,line_item_id
3385 ,grp_date_id
3386 ,calc_prd_start_date
3387 ,l_fy_end_date)
3388 WHERE
3389 var_rent_id = p_var_rent_id AND
3390 l_fy_end_date BETWEEN calc_prd_start_date
3391 AND (calc_prd_end_date - 1);
3392 END IF;
3393
3394 END IF;
3395
3396 EXCEPTION
3397 WHEN OTHERS THEN RAISE;
3398
3399 END populate_fy_pro_sales;
3400
3401 --------------------------------------------------------------------------------
3402 -- NAME : populate_ytd_sales
3403 -- DESCRIPTION :
3404 -- PURPOSE :
3405 -- INVOKED FROM :
3406 -- ARGUMENTS :
3407 -- REFERENCE : PN_COMMON.debug()
3408 -- HISTORY :
3409 --
3410 -- dd-mon-yyyy name o Created
3411 --------------------------------------------------------------------------------
3412 PROCEDURE populate_ytd_sales( p_var_rent_id IN NUMBER
3413 ,p_proration_rule IN VARCHAR2) IS
3414
3415 /* get VR info */
3416 CURSOR vr_c(p_vr_id IN NUMBER) IS
3417 SELECT
3418 vr.var_rent_id
3419 ,vr.proration_rule
3420 FROM
3421 pn_var_rents_all vr
3422 WHERE
3423 vr.var_rent_id = p_vr_id;
3424
3425 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
3426
3427 /* get the line items with updated sales for FY, LY, FLY, STD, NP */
3428 CURSOR lines_c(p_vr_id IN NUMBER) IS
3429 SELECT
3430 period_id
3431 ,line_item_id
3432 FROM
3433 pn_var_lines_all
3434 WHERE
3435 var_rent_id = p_vr_id AND
3436 sales_vol_update_flag = 'Y'
3437 ORDER BY
3438 period_id
3439 ,line_item_id;
3440
3441 /* get the line items with updated sales for CYP, CYNP */
3442 CURSOR lines_cs_c( p_vr_id IN NUMBER
3443 ,p_part_prd_id IN NUMBER
3444 ,p_full_prd_id IN NUMBER) IS
3445 SELECT
3446 period_id
3447 ,line_item_id
3448 FROM
3449 pn_var_lines_all
3450 WHERE
3451 var_rent_id = p_vr_id AND
3452 sales_vol_update_flag = 'Y' AND
3453 period_id NOT IN (p_part_prd_id, p_full_prd_id)
3454 ORDER BY
3455 period_id
3456 ,line_item_id;
3457
3458 /* get the period details - we use the first 2 periods */
3459 CURSOR periods_c(p_vr_id IN NUMBER) IS
3460 SELECT
3461 period_id
3462 ,start_date
3463 ,end_date
3464 ,partial_period
3465 FROM
3466 pn_var_periods_all
3467 WHERE
3468 var_rent_id = p_vr_id
3469 ORDER BY
3470 start_date;
3471
3472 /* period info */
3473 l_part_prd_id NUMBER;
3474 l_part_prd_start_dt DATE;
3475 l_part_prd_end_dt DATE;
3476 l_part_prd_partial_flag VARCHAR2(1);
3477
3478 l_full_prd_id NUMBER;
3479 l_full_prd_start_dt DATE;
3480 l_full_prd_end_dt DATE;
3481 l_full_prd_partial_flag VARCHAR2(1);
3482
3483 /* ytd for STD, NP, FY, LY, FLY */
3484 CURSOR ytd_sales_c( p_vr_ID IN NUMBER
3485 ,p_prd_ID IN NUMBER
3486 ,p_line_ID IN NUMBER) IS
3487 SELECT
3488 hdr.trx_header_id
3489 ,SUM(hdr.prorated_group_sales) OVER
3490 (PARTITION BY
3491 hdr.period_id
3492 ,hdr.line_item_id
3493 ,hdr.reset_group_id
3494 ORDER BY
3495 hdr.calc_prd_start_date
3496 ROWS UNBOUNDED PRECEDING) AS ytd_sales
3497 FROM
3498 pn_var_trx_headers_all hdr
3499 WHERE
3500 hdr.var_rent_id = p_vr_id AND
3501 hdr.period_id = p_prd_ID AND
3502 hdr.line_item_id = p_line_ID
3503 ORDER BY
3504 hdr.period_id
3505 ,hdr.line_item_id
3506 ,hdr.calc_prd_start_date;
3507
3508 /* ytd for CYP, CYNP combined period */
3509 CURSOR ytd_sales_cs_c( p_vr_ID IN NUMBER
3510 ,p_part_prd_id IN NUMBER
3511 ,p_full_prd_id IN NUMBER) IS
3512 SELECT
3513 hdr.trx_header_id
3514 ,SUM(hdr.prorated_group_sales) OVER
3515 (PARTITION BY
3516 hdr.line_item_group_id
3517 ORDER BY
3518 hdr.calc_prd_start_date
3519 ROWS UNBOUNDED PRECEDING) AS ytd_sales
3520 FROM
3521 pn_var_trx_headers_all hdr
3522 WHERE
3523 hdr.var_rent_id = p_vr_id AND
3524 hdr.period_id IN (p_part_prd_id, p_full_prd_id)
3525 ORDER BY
3526 hdr.line_item_group_id
3527 ,hdr.calc_prd_start_date;
3528
3529 /* counters */
3530 l_counter1 NUMBER;
3531
3532 /* plsql tables for ytd dates and trx hdr */
3533 trx_hdr_t NUM_T;
3534 ytd_sales_t NUM_T;
3535
3536 BEGIN
3537
3538 pnp_debug_pkg.log('++++ pn_var_trx_pkg.populate_ytd_sales START ++++');
3539
3540 /* get VR details */
3541 IF p_proration_rule IS NULL THEN
3542
3543 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
3544
3545 l_proration_rule := vr_rec.proration_rule;
3546
3547 END LOOP;
3548
3549 ELSE
3550
3551 l_proration_rule := p_proration_rule;
3552
3553 END IF;
3554
3555 pnp_debug_pkg.log('Called with: ');
3556 pnp_debug_pkg.log(' p_var_rent_id: '||p_var_rent_id);
3557 pnp_debug_pkg.log(' l_proration_rule: '||l_proration_rule);
3558
3559 /* l_proration_rule based decisions */
3560 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
3561 ,pn_var_trx_pkg.G_PRORUL_LY
3562 ,pn_var_trx_pkg.G_PRORUL_FLY
3563 ,pn_var_trx_pkg.G_PRORUL_NP
3564 ,pn_var_trx_pkg.G_PRORUL_STD) THEN
3565
3566 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id) LOOP
3567
3568 trx_hdr_t.DELETE;
3569 ytd_sales_t.DELETE;
3570
3571 OPEN ytd_sales_c( p_vr_ID => p_var_rent_id
3572 ,p_prd_ID => line_rec.period_id
3573 ,p_line_ID => line_rec.line_item_id);
3574
3575 FETCH ytd_sales_c BULK COLLECT INTO
3576 trx_hdr_t
3577 ,ytd_sales_t;
3578
3579 CLOSE ytd_sales_c;
3580
3581 FORALL i IN 1..trx_hdr_t.COUNT
3582 UPDATE
3583 pn_var_trx_headers_all
3584 SET
3585 ytd_sales = ytd_sales_t(i)
3586 WHERE
3587 trx_header_id = trx_hdr_t(i);
3588
3589 END LOOP;
3590
3591 ELSIF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
3592 ,pn_var_trx_pkg.G_PRORUL_CYNP) THEN
3593
3594 /* fetch partial and full period details */
3595 l_counter1 := 0;
3596 FOR prd_rec IN periods_c(p_vr_id => p_var_rent_id) LOOP
3597
3598 l_counter1 := l_counter1 + 1;
3599
3600 IF l_counter1 = 1 THEN
3601 l_part_prd_id := prd_rec.period_id;
3602 l_part_prd_start_dt := prd_rec.start_date;
3603 l_part_prd_end_dt := prd_rec.end_date;
3604 l_part_prd_partial_flag := prd_rec.partial_period;
3605
3606 ELSIF l_counter1 = 2 THEN
3607 l_full_prd_id := prd_rec.period_id;
3608 l_full_prd_start_dt := prd_rec.start_date;
3609 l_full_prd_end_dt := prd_rec.end_date;
3610 l_full_prd_partial_flag := prd_rec.partial_period;
3611
3612 ELSE
3613 EXIT;
3614
3615 END IF;
3616
3617 END LOOP; /* fetch partial and full period details */
3618
3619 trx_hdr_t.DELETE;
3620 ytd_sales_t.DELETE;
3621
3622 OPEN ytd_sales_cs_c( p_vr_ID => p_var_rent_id
3623 ,p_part_prd_id => l_part_prd_id
3624 ,p_full_prd_id => l_full_prd_id);
3625
3626 FETCH ytd_sales_cs_c BULK COLLECT INTO
3627 trx_hdr_t
3628 ,ytd_sales_t;
3629
3630 CLOSE ytd_sales_cs_c;
3631
3632 FORALL i IN 1..trx_hdr_t.COUNT
3633 UPDATE
3634 pn_var_trx_headers_all
3635 SET
3636 ytd_sales = ytd_sales_t(i)
3637 WHERE
3638 trx_header_id = trx_hdr_t(i);
3639
3640 /* loop for all lines */
3641 FOR line_rec IN lines_cs_c ( p_vr_id => p_var_rent_id
3642 ,p_part_prd_id => l_part_prd_id
3643 ,p_full_prd_id => l_full_prd_id)
3644 LOOP
3645
3646 trx_hdr_t.DELETE;
3647 ytd_sales_t.DELETE;
3648
3649 OPEN ytd_sales_c( p_vr_ID => p_var_rent_id
3650 ,p_prd_ID => line_rec.period_id
3651 ,p_line_ID => line_rec.line_item_id);
3652
3653 FETCH ytd_sales_c BULK COLLECT INTO
3654 trx_hdr_t
3655 ,ytd_sales_t;
3656
3657 CLOSE ytd_sales_c;
3658
3659 FORALL i IN 1..trx_hdr_t.COUNT
3660 UPDATE
3661 pn_var_trx_headers_all
3662 SET
3663 ytd_sales = ytd_sales_t(i)
3664 WHERE
3665 trx_header_id = trx_hdr_t(i);
3666
3667 END LOOP; /* loop for all lines */
3668
3669 END IF; /* l_proration_rule based decisions */
3670
3671 EXCEPTION
3672 WHEN OTHERS THEN RAISE;
3673
3674 END populate_ytd_sales;
3675
3676 --------------------------------------------------------------------------------
3677 -- NAME : populate_sales
3678 -- DESCRIPTION :
3679 -- PURPOSE :
3680 -- INVOKED FROM :
3681 -- ARGUMENTS :
3682 -- REFERENCE : PN_COMMON.debug()
3683 -- HISTORY :
3684 --
3685 -- dd-mon-yyyy name o Created
3686 --------------------------------------------------------------------------------
3687 PROCEDURE populate_sales(p_var_rent_id IN NUMBER) IS
3688
3689 /* get VR info */
3690 CURSOR vr_c(p_vr_id IN NUMBER) IS
3691 SELECT
3692 vr.org_id
3693 ,vr.var_rent_id
3694 ,vr.commencement_date
3695 ,vr.termination_date
3696 ,vr.proration_rule
3697 ,vr.cumulative_vol
3698 FROM
3699 pn_var_rents_all vr
3700 WHERE
3701 vr.var_rent_id = p_vr_id;
3702
3703 l_org_id pn_var_rents_all.org_id%TYPE;
3704 l_vr_commencement_date pn_var_rents_all.commencement_date%TYPE;
3705 l_vr_termination_date pn_var_rents_all.termination_date%TYPE;
3706 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
3707 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
3708
3709 /* get the line items with updated sales */
3710 CURSOR lines_c(p_vr_id IN NUMBER) IS
3711 SELECT
3712 period_id
3713 ,line_item_id
3714 FROM
3715 pn_var_lines_all
3716 WHERE
3717 var_rent_id = p_vr_id AND
3718 sales_vol_update_flag = 'Y'
3719 ORDER BY
3720 period_id
3721 ,line_item_id;
3722
3723 /* get the calc periods to update sales data */
3724 CURSOR calc_periods_c( p_vr_id IN NUMBER
3725 ,p_prd_id IN NUMBER
3726 ,p_line_id IN NUMBER) IS
3727 SELECT
3728 hdr.trx_header_id
3729 ,hdr.var_rent_id
3730 ,hdr.period_id
3731 ,hdr.line_item_id
3732 ,hdr.grp_date_id
3733 ,hdr.calc_prd_start_date
3734 ,hdr.calc_prd_end_date
3735 FROM
3736 pn_var_trx_headers_all hdr
3737 WHERE
3738 hdr.var_rent_id = p_vr_id AND
3739 hdr.period_id = p_prd_id AND
3740 hdr.line_item_id = p_line_id
3741 ORDER BY
3742 hdr.period_id
3743 ,hdr.line_item_id
3744 ,hdr.calc_prd_start_date
3745 ,hdr.calc_prd_end_date;
3746
3747 /* data structures */
3748 trx_hdr_t NUM_T;
3749 reporting_grp_sales_t NUM_T;
3750 prorate_grp_sales_t NUM_T;
3751
3752 l_counter NUMBER;
3753
3754 /* flags */
3755 l_sales_create_upd_flag BOOLEAN;
3756
3757 l_line_items_lock4salesvol_t NUM_T;
3758
3759 BEGIN
3760
3761 /* lock the lines with updated sales */
3762 l_line_items_lock4salesvol_t.DELETE;
3763
3764 OPEN line_items_lock4salesvol_c(p_vr_id => p_var_rent_id);
3765 FETCH line_items_lock4salesvol_c BULK COLLECT INTO l_line_items_lock4salesvol_t;
3766 CLOSE line_items_lock4salesvol_c;
3767
3768 /* get the VR details */
3769 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
3770
3771 l_org_id := vr_rec.org_id;
3772 l_vr_commencement_date := vr_rec.commencement_date;
3773 l_vr_termination_date := vr_rec.termination_date;
3774 l_proration_rule := vr_rec.proration_rule;
3775 l_calculation_method := vr_rec.cumulative_vol;
3776
3777 END LOOP;
3778
3779 l_sales_create_upd_flag := FALSE;
3780
3781 l_counter := 0;
3782
3783 /* for all line items with changed sales volume */
3784 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id)
3785 LOOP
3786
3787 l_sales_create_upd_flag := TRUE;
3788
3789 trx_hdr_t.DELETE;
3790 reporting_grp_sales_t.DELETE;
3791 prorate_grp_sales_t.DELETE;
3792
3793 /* for all calc sub periods for the line item, get the prorated sales */
3794 FOR trx_rec IN calc_periods_c( p_vr_id => p_var_rent_id
3795 ,p_prd_id => line_rec.period_id
3796 ,p_line_id => line_rec.line_item_id)
3797 LOOP
3798
3799 l_counter := l_counter + 1;
3800
3801 trx_hdr_t(l_counter) := trx_rec.trx_header_id;
3802
3803 pn_var_trx_pkg.get_calc_prd_sales
3804 ( p_var_rent_id => trx_rec.var_rent_id
3805 ,p_period_id => trx_rec.period_id
3806 ,p_line_item_id => trx_rec.line_item_id
3807 ,p_grp_date_id => trx_rec.grp_date_id
3808 ,p_start_date => trx_rec.calc_prd_start_date
3809 ,p_end_date => trx_rec.calc_prd_end_date
3810 ,x_pro_sales => prorate_grp_sales_t(l_counter)
3811 ,x_sales => reporting_grp_sales_t(l_counter));
3812
3813 END LOOP;
3814
3815 /* for all calc sub periods for the line item,
3816 update the trx headers with the sales */
3817 IF trx_hdr_t.COUNT > 0 THEN
3818
3819 FORALL i IN trx_hdr_t.FIRST..trx_hdr_t.LAST
3820 UPDATE
3821 pn_var_trx_headers_all
3822 SET
3823 reporting_group_sales = reporting_grp_sales_t(i)
3824 ,prorated_group_sales = prorate_grp_sales_t(i)
3825 WHERE
3826 trx_header_id = trx_hdr_t(i);
3827
3828 END IF;
3829
3830 END LOOP;
3831
3832 IF l_sales_create_upd_flag THEN
3833
3834 IF l_proration_rule = pn_var_trx_pkg.G_PRORUL_LY THEN
3835
3836 pn_var_trx_pkg.populate_ly_pro_sales
3837 ( p_var_rent_id => p_var_rent_id
3838 ,p_proration_rule => l_proration_rule
3839 ,p_vr_commencement_dt => l_vr_commencement_date
3840 ,p_vr_termination_dt => l_vr_termination_date);
3841
3842 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FY THEN
3843
3844 pn_var_trx_pkg.populate_fy_pro_sales
3845 ( p_var_rent_id => p_var_rent_id
3846 ,p_proration_rule => l_proration_rule
3847 ,p_vr_commencement_dt => l_vr_commencement_date
3848 ,p_vr_termination_dt => l_vr_termination_date);
3849
3850 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FLY THEN
3851
3852 pn_var_trx_pkg.populate_ly_pro_sales
3853 ( p_var_rent_id => p_var_rent_id
3854 ,p_proration_rule => l_proration_rule
3855 ,p_vr_commencement_dt => l_vr_commencement_date
3856 ,p_vr_termination_dt => l_vr_termination_date);
3857
3858 pn_var_trx_pkg.populate_fy_pro_sales
3859 ( p_var_rent_id => p_var_rent_id
3860 ,p_proration_rule => l_proration_rule
3861 ,p_vr_commencement_dt => l_vr_commencement_date
3862 ,p_vr_termination_dt => l_vr_termination_date);
3863
3864 END IF;
3865
3866 /* always populate YTD sales - very useful */
3867 pn_var_trx_pkg.populate_ytd_sales
3868 ( p_var_rent_id => p_var_rent_id
3869 ,p_proration_rule => l_proration_rule);
3870
3871 END IF;
3872
3873 FORALL line_rec IN 1..l_line_items_lock4salesvol_t.COUNT
3874 UPDATE
3875 pn_var_lines_all
3876 SET
3877 sales_vol_update_flag = NULL
3878 WHERE
3879 line_item_id = l_line_items_lock4salesvol_t(line_rec);
3880
3881 /* UN-lock the lines with updated sales */
3882 COMMIT;
3883
3884 EXCEPTION
3885 WHEN OTHERS THEN RAISE;
3886
3887 END populate_sales;
3888
3889 --------------------------------------------------------------------------------
3890 --------------- PROCEDURES TO POPULATE PRORATED FORECASTED SALES ---------------
3891 --------------------------------------------------------------------------------
3892
3893
3894 --------------------------------------------------------------------------------
3895 -- NAME : get_calc_prd_sales_for
3896 -- DESCRIPTION : get forecasted volumes for a calculation period, sub period
3897 -- PURPOSE :
3898 -- INVOKED FROM :
3899 -- ARGUMENTS :
3900 -- REFERENCE : PN_COMMON.debug()
3901 -- HISTORY :
3902 --
3903 -- 13-SEP-06 Shabda o Created
3904 --------------------------------------------------------------------------------
3905 PROCEDURE get_calc_prd_sales_for( p_var_rent_id IN NUMBER
3906 ,p_period_id IN NUMBER
3907 ,p_line_item_id IN NUMBER
3908 ,p_grp_date_id IN NUMBER
3909 ,p_start_date IN DATE
3910 ,p_end_date IN DATE
3911 ,x_pro_sales OUT NOCOPY NUMBER
3912 ,x_sales OUT NOCOPY NUMBER) IS
3913
3914 /* get forecasted volumes for a calculation period */
3915 CURSOR vol_hist_sum_c( p_prd_id IN NUMBER
3916 ,p_line_id IN NUMBER
3917 ,p_grp_id IN NUMBER) IS
3918 SELECT
3919 SUM(forecasted_amount) AS calc_prd_forecasted_volume
3920 FROM
3921 pn_var_vol_hist_all sales
3922 WHERE
3923 sales.period_id = p_prd_id AND
3924 sales.line_item_id = p_line_id AND
3925 sales.grp_date_id = p_grp_id;
3926
3927 /* get forecasted volumes for a calculation sub-period */
3928 CURSOR vol_hist_c( p_prd_id IN NUMBER
3929 ,p_line_id IN NUMBER
3930 ,p_grp_id IN NUMBER
3931 ,p_start_dt IN DATE
3932 ,p_end_dt IN DATE) IS
3933 SELECT
3934 sales.forecasted_amount
3935 ,sales.start_date
3936 ,sales.end_date
3937 FROM
3938 pn_var_vol_hist_all sales
3939 WHERE
3940 sales.period_id = p_prd_id AND
3941 sales.line_item_id = p_line_id AND
3942 sales.grp_date_id = p_grp_id AND
3943 sales.start_date <= p_end_dt AND
3944 sales.end_date >= p_start_dt;
3945
3946 /* get grp dates */
3947 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
3948 SELECT
3949 grp.grp_start_date
3950 ,grp.grp_end_date
3951 FROM
3952 pn_var_grp_dates_all grp
3953 WHERE
3954 grp.grp_date_id = p_grp_id;
3955
3956 l_grp_start_date DATE;
3957 l_grp_end_date DATE;
3958
3959 l_calc_prd_sales NUMBER;
3960 l_pro_calc_prd_sales NUMBER;
3961
3962 BEGIN
3963
3964 /* get group / calc period dates */
3965 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
3966 l_grp_start_date := grp_rec.grp_start_date;
3967 l_grp_end_date := grp_rec.grp_end_date;
3968 END LOOP;
3969
3970 l_calc_prd_sales := 0;
3971 l_pro_calc_prd_sales := 0;
3972
3973 /* get all APPROVED sales for a group / calc period */
3974 FOR sales_rec IN vol_hist_sum_c ( p_prd_id => p_period_id
3975 ,p_line_id => p_line_item_id
3976 ,p_grp_id => p_grp_date_id)
3977 LOOP
3978 l_calc_prd_sales := l_calc_prd_sales + sales_rec.calc_prd_forecasted_volume;
3979 END LOOP;
3980
3981 /* if calc sub period dates are same as grp / calc period start-end dates */
3982 IF l_grp_start_date = p_start_date AND
3983 l_grp_end_date = p_end_date
3984 THEN
3985
3986 /* then prorated sales = total sales */
3987 l_pro_calc_prd_sales := l_calc_prd_sales;
3988
3989 ELSE
3990
3991 /* else, sum all sales to get the prorated sales */
3992 FOR sales_rec IN vol_hist_c ( p_prd_id => p_period_id
3993 ,p_line_id => p_line_item_id
3994 ,p_grp_id => p_grp_date_id
3995 ,p_start_dt => p_start_date
3996 ,p_end_dt => p_end_date)
3997 LOOP
3998
3999 /* if sales volume dates between calc sub period dates */
4000 IF sales_rec.start_date >= p_start_date AND
4001 sales_rec.end_date <= p_end_date
4002 THEN
4003 /* consider full volume */
4004 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.forecasted_amount;
4005
4006 /* else if sales volume dates overlap calc sub period dates */
4007 ELSIF sales_rec.start_date <= p_end_date AND
4008 sales_rec.end_date >= p_start_date
4009 THEN
4010 /* then consider prorated volume */
4011 l_pro_calc_prd_sales
4012 := l_pro_calc_prd_sales
4013 + sales_rec.forecasted_amount
4014 * ((LEAST(sales_rec.end_date, p_end_date)
4015 - GREATEST(sales_rec.start_date, p_start_date)) + 1)
4016 / ((sales_rec.end_date - sales_rec.start_date) + 1);
4017
4018 END IF;
4019
4020 END LOOP;
4021
4022 END IF;
4023
4024 x_pro_sales := l_pro_calc_prd_sales;
4025 x_sales := l_calc_prd_sales;
4026
4027 EXCEPTION
4028 WHEN OTHERS THEN RAISE;
4029
4030 END get_calc_prd_sales_for;
4031
4032 --------------------------------------------------------------------------------
4033 -- NAME : get_calc_prd_sales_for
4034 -- DESCRIPTION :get volumes for a calculation period, sub period
4035 -- PURPOSE :
4036 -- INVOKED FROM :
4037 -- ARGUMENTS :
4038 -- REFERENCE : PN_COMMON.debug()
4039 -- HISTORY :
4040 --
4041 -- 13-SEP-06 Shabda o Created
4042 --------------------------------------------------------------------------------
4043 FUNCTION get_calc_prd_sales_for( p_var_rent_id IN NUMBER
4044 ,p_period_id IN NUMBER
4045 ,p_line_item_id IN NUMBER
4046 ,p_grp_date_id IN NUMBER
4047 ,p_start_date IN DATE
4048 ,p_end_date IN DATE)
4049 RETURN NUMBER IS
4050
4051 /* get volumes for a calculation period */
4052 CURSOR vol_hist_sum_c ( p_prd_id IN NUMBER
4053 ,p_line_id IN NUMBER
4054 ,p_grp_id IN NUMBER) IS
4055 SELECT
4056 SUM(forecasted_amount) AS calc_prd_forecasted_volume
4057 FROM
4058 pn_var_vol_hist_all sales
4059 WHERE
4060 sales.period_id = p_prd_id AND
4061 sales.line_item_id = p_line_id AND
4062 sales.grp_date_id = p_grp_id;
4063
4064 /* get volumes for a calculation sub-period */
4065 CURSOR vol_hist_c ( p_prd_id IN NUMBER
4066 ,p_line_id IN NUMBER
4067 ,p_grp_id IN NUMBER
4068 ,p_start_dt IN DATE
4069 ,p_end_dt IN DATE) IS
4070 SELECT
4071 sales.forecasted_amount
4072 ,sales.start_date
4073 ,sales.end_date
4074 FROM
4075 pn_var_vol_hist_all sales
4076 WHERE
4077 sales.period_id = p_prd_id AND
4078 sales.line_item_id = p_line_id AND
4079 sales.grp_date_id = p_grp_id AND
4080 sales.start_date <= p_end_dt AND
4081 sales.end_date >= p_start_dt;
4082
4083 /* get grp dates */
4084 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
4085 SELECT
4086 grp.grp_start_date
4087 ,grp.grp_end_date
4088 FROM
4089 pn_var_grp_dates_all grp
4090 WHERE
4091 grp.grp_date_id = p_grp_id;
4092
4093 l_grp_start_date DATE;
4094 l_grp_end_date DATE;
4095
4096 l_calc_prd_sales NUMBER;
4097 l_pro_calc_prd_sales NUMBER;
4098
4099 BEGIN
4100
4101 /* get group / calc period dates */
4102 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
4103 l_grp_start_date := grp_rec.grp_start_date;
4104 l_grp_end_date := grp_rec.grp_end_date;
4105 END LOOP;
4106
4107 l_pro_calc_prd_sales := 0;
4108
4109 /* if calc sub period dates are same as grp / calc period start-end dates */
4110 IF l_grp_start_date = p_start_date AND
4111 l_grp_end_date = p_end_date
4112 THEN
4113
4114 /* get all APPROVED sales for a group / calc period
4115 prorated sales = total sales */
4116 FOR sales_rec IN vol_hist_sum_c ( p_prd_id => p_period_id
4117 ,p_line_id => p_line_item_id
4118 ,p_grp_id => p_grp_date_id)
4119 LOOP
4120 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.calc_prd_forecasted_volume;
4121 END LOOP;
4122
4123 ELSE
4124
4125 /* else, sum all sales to get the prorated sales */
4126 FOR sales_rec IN vol_hist_c ( p_prd_id => p_period_id
4127 ,p_line_id => p_line_item_id
4128 ,p_grp_id => p_grp_date_id
4129 ,p_start_dt => p_start_date
4130 ,p_end_dt => p_end_date)
4131 LOOP
4132
4133 /* if sales volume dates between calc sub period dates */
4134 IF sales_rec.start_date >= p_start_date AND
4135 sales_rec.end_date <= p_end_date
4136 THEN
4137 /* consider full volume */
4138 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.forecasted_amount;
4139
4140 /* else if sales volume dates overlap calc sub period dates */
4141 ELSIF sales_rec.start_date <= p_end_date AND
4142 sales_rec.end_date >= p_start_date
4143 THEN
4144 /* then consider prorated volume */
4145 l_pro_calc_prd_sales
4146 := l_pro_calc_prd_sales
4147 + sales_rec.forecasted_amount
4148 * ((LEAST(sales_rec.end_date, p_end_date)
4149 - GREATEST(sales_rec.start_date, p_start_date)) + 1)
4150 / ((sales_rec.end_date - sales_rec.start_date) + 1);
4151
4152 END IF;
4153
4154 END LOOP;
4155
4156 END IF;
4157
4158 RETURN l_pro_calc_prd_sales;
4159
4160 EXCEPTION
4161 WHEN OTHERS THEN RAISE;
4162
4163 END get_calc_prd_sales_for;
4164
4165
4166
4167 --------------------------------------------------------------------------------
4168 -- NAME : populate_ytd_sales_for
4169 -- DESCRIPTION : gets forecasted YTD sales
4170 -- PURPOSE :
4171 -- INVOKED FROM :
4172 -- ARGUMENTS :
4173 -- REFERENCE : PN_COMMON.debug()
4174 -- HISTORY :
4175 --
4176 -- 13-SEP-06 Shabda o Created
4177 --------------------------------------------------------------------------------
4178 PROCEDURE populate_ytd_sales_for( p_var_rent_id IN NUMBER
4179 ,p_calc_method IN VARCHAR2) IS
4180
4181 /* get VR info */
4182 CURSOR vr_c(p_vr_id IN NUMBER) IS
4183 SELECT
4184 vr.var_rent_id
4185 ,vr.cumulative_vol
4186 FROM
4187 pn_var_rents_all vr
4188 WHERE
4189 vr.var_rent_id = p_vr_id;
4190
4191 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
4192 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
4193
4194 /* get the line items with updated sales for FY, LY, FLY, STD, NP */
4195 CURSOR lines_c(p_vr_id IN NUMBER) IS
4196 SELECT
4197 period_id
4198 ,line_item_id
4199 FROM
4200 pn_var_lines_all
4201 WHERE
4202 var_rent_id = p_vr_id AND
4203 sales_vol_update_flag = 'Y'
4204 ORDER BY
4205 period_id
4206 ,line_item_id;
4207
4208 /* ytd for STD, NP */
4209 CURSOR ytd_sales_c( p_vr_ID IN NUMBER
4210 ,p_prd_ID IN NUMBER
4211 ,p_line_ID IN NUMBER) IS
4212 SELECT
4213 hdr.trx_header_id
4214 ,SUM(hdr.prorated_group_sales_for) OVER
4215 (PARTITION BY
4216 hdr.period_id
4217 ,hdr.line_item_id
4218 ,hdr.reset_group_id
4219 ORDER BY
4220 hdr.calc_prd_start_date
4221 ROWS UNBOUNDED PRECEDING) AS ytd_sales_for
4222 FROM
4223 pn_var_trx_headers_all hdr
4224 WHERE
4225 hdr.var_rent_id = p_vr_id AND
4226 hdr.period_id = p_prd_ID AND
4227 hdr.line_item_id = p_line_ID
4228 ORDER BY
4229 hdr.period_id
4230 ,hdr.line_item_id
4231 ,hdr.calc_prd_start_date;
4232
4233 /* counters */
4234 l_counter1 NUMBER;
4235
4236 /* plsql tables for ytd dates and trx hdr */
4237 trx_hdr_t NUM_T;
4238 ytd_sales_for_t NUM_T;
4239
4240 BEGIN
4241
4242 /* get VR details */
4243 IF p_calc_method IS NULL THEN
4244 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
4245 l_calculation_method := vr_rec.cumulative_vol;
4246 END LOOP;
4247 ELSE
4248 l_calculation_method := p_calc_method;
4249 END IF;
4250
4251 IF l_calculation_method IN ( pn_var_trx_pkg.G_CALC_YTD
4252 ,pn_var_trx_pkg.G_CALC_CUMULATIVE) THEN
4253
4254 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id) LOOP
4255
4256 trx_hdr_t.DELETE;
4257 ytd_sales_for_t.DELETE;
4258
4259 OPEN ytd_sales_c( p_vr_ID => p_var_rent_id
4260 ,p_prd_ID => line_rec.period_id
4261 ,p_line_ID => line_rec.line_item_id);
4262
4263 FETCH ytd_sales_c BULK COLLECT INTO
4264 trx_hdr_t
4265 ,ytd_sales_for_t;
4266
4267 CLOSE ytd_sales_c;
4268
4269 FORALL i IN 1..trx_hdr_t.COUNT
4270 UPDATE
4271 pn_var_trx_headers_all
4272 SET
4273 ytd_sales_for = ytd_sales_for_t(i)
4274 WHERE
4275 trx_header_id = trx_hdr_t(i);
4276
4277 END LOOP;
4278
4279 END IF; /* IF l_calculation_method IN */
4280
4281 EXCEPTION
4282 WHEN OTHERS THEN RAISE;
4283
4284 END populate_ytd_sales_for;
4285
4286 --------------------------------------------------------------------------------
4287 -- NAME : populate_sales_for
4288 -- DESCRIPTION : populates forecasted sales in trx header tables
4289 -- PURPOSE :
4290 -- INVOKED FROM :
4291 -- ARGUMENTS :
4292 -- REFERENCE : PN_COMMON.debug()
4293 -- HISTORY :
4294 --
4295 -- 13-SEP-06 Shabda o Created
4296 --------------------------------------------------------------------------------
4297 PROCEDURE populate_sales_for(p_var_rent_id IN NUMBER) IS
4298
4299 /* get VR info */
4300 CURSOR vr_c(p_vr_id IN NUMBER) IS
4301 SELECT
4302 vr.org_id
4303 ,vr.var_rent_id
4304 ,vr.commencement_date
4305 ,vr.termination_date
4306 ,vr.proration_rule
4307 ,vr.cumulative_vol
4308 FROM
4309 pn_var_rents_all vr
4310 WHERE
4311 vr.var_rent_id = p_vr_id;
4312
4313 l_org_id pn_var_rents_all.org_id%TYPE;
4314 l_vr_commencement_date pn_var_rents_all.commencement_date%TYPE;
4315 l_vr_termination_date pn_var_rents_all.termination_date%TYPE;
4316 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
4317 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
4318
4319 /* get the line items with updated sales */
4320 CURSOR lines_c(p_vr_id IN NUMBER) IS
4321 SELECT
4322 period_id
4323 ,line_item_id
4324 FROM
4325 pn_var_lines_all
4326 WHERE
4327 var_rent_id = p_vr_id AND
4328 sales_vol_update_flag = 'Y'
4329 ORDER BY
4330 period_id
4331 ,line_item_id;
4332
4333 /* get the calc periods to update sales data */
4334 CURSOR calc_periods_c( p_vr_id IN NUMBER
4335 ,p_prd_id IN NUMBER
4336 ,p_line_id IN NUMBER) IS
4337 SELECT
4338 hdr.trx_header_id
4339 ,hdr.var_rent_id
4340 ,hdr.period_id
4341 ,hdr.line_item_id
4342 ,hdr.grp_date_id
4343 ,hdr.calc_prd_start_date
4344 ,hdr.calc_prd_end_date
4345 FROM
4346 pn_var_trx_headers_all hdr
4347 WHERE
4348 hdr.var_rent_id = p_vr_id AND
4349 hdr.period_id = p_prd_id AND
4350 hdr.line_item_id = p_line_id
4351 ORDER BY
4352 hdr.period_id
4353 ,hdr.line_item_id
4354 ,hdr.calc_prd_start_date
4355 ,hdr.calc_prd_end_date;
4356
4357 /* data structures */
4358 trx_hdr_t NUM_T;
4359 reporting_grp_sales_t NUM_T;
4360 prorate_grp_sales_t NUM_T;
4361
4362 l_counter NUMBER;
4363
4364 /* flags */
4365 l_sales_create_upd_flag BOOLEAN;
4366
4367 l_line_items_lock4salesvol_t NUM_T;
4368
4369
4370 BEGIN
4371
4372 /* lock the lines with updated sales */
4373 l_line_items_lock4salesvol_t.DELETE;
4374
4375 OPEN line_items_lock4salesvol_c(p_vr_id => p_var_rent_id);
4376 FETCH line_items_lock4salesvol_c BULK COLLECT INTO l_line_items_lock4salesvol_t;
4377 CLOSE line_items_lock4salesvol_c;
4378
4379 /* get the VR details */
4380 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
4381
4382 l_org_id := vr_rec.org_id;
4383 l_vr_commencement_date := vr_rec.commencement_date;
4384 l_vr_termination_date := vr_rec.termination_date;
4385 l_proration_rule := vr_rec.proration_rule;
4386 l_calculation_method := vr_rec.cumulative_vol;
4387
4388 END LOOP;
4389
4390 l_sales_create_upd_flag := FALSE;
4391
4392 l_counter := 0;
4393
4394 /* for all line items with changed sales volume */
4395 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id)
4396 LOOP
4397
4398 l_sales_create_upd_flag := TRUE;
4399
4400 trx_hdr_t.DELETE;
4401 reporting_grp_sales_t.DELETE;
4402 prorate_grp_sales_t.DELETE;
4403
4404 /* for all calc sub periods for the line item, get the prorated sales */
4405 FOR trx_rec IN calc_periods_c( p_vr_id => p_var_rent_id
4406 ,p_prd_id => line_rec.period_id
4407 ,p_line_id => line_rec.line_item_id)
4408 LOOP
4409
4410 l_counter := l_counter + 1;
4411
4412 trx_hdr_t(l_counter) := trx_rec.trx_header_id;
4413
4414 pn_var_trx_pkg.get_calc_prd_sales_for
4415 ( p_var_rent_id => trx_rec.var_rent_id
4416 ,p_period_id => trx_rec.period_id
4417 ,p_line_item_id => trx_rec.line_item_id
4418 ,p_grp_date_id => trx_rec.grp_date_id
4419 ,p_start_date => trx_rec.calc_prd_start_date
4420 ,p_end_date => trx_rec.calc_prd_end_date
4421 ,x_pro_sales => prorate_grp_sales_t(l_counter)
4422 ,x_sales => reporting_grp_sales_t(l_counter));
4423
4424 END LOOP;
4425 /* for all calc sub periods for the line item,
4426 update the trx headers with the sales */
4427 IF trx_hdr_t.COUNT > 0 THEN
4428
4429 FORALL i IN trx_hdr_t.FIRST..trx_hdr_t.LAST
4430 UPDATE
4431 pn_var_trx_headers_all
4432 SET
4433 reporting_group_sales_for = reporting_grp_sales_t(i)
4434 ,prorated_group_sales_for = prorate_grp_sales_t(i)
4435 WHERE
4436 trx_header_id = trx_hdr_t(i);
4437
4438 END IF;
4439
4440 END LOOP;
4441
4442 /*Similar to actuals - We always populate YTD sales*/
4443
4444 pn_var_trx_pkg.populate_ytd_sales_for
4445 ( p_var_rent_id => p_var_rent_id
4446 ,p_calc_method => l_calculation_method);
4447
4448 /*This would be needed in actual sales, but not for forecasted.
4449 FORALL line_rec IN 1..l_line_items_lock4salesvol_t.COUNT
4450 UPDATE
4451 pn_var_lines_all
4452 SET
4453 sales_vol_update_flag = NULL
4454 WHERE
4455 line_item_id = l_line_items_lock4salesvol_t(line_rec);*/
4456
4457 /* UN-lock the lines with updated sales */
4458 COMMIT;
4459
4460 EXCEPTION
4461 WHEN OTHERS THEN RAISE;
4462
4463 END populate_sales_for;
4464 /*-----------------------------------------------------------------------------
4465 ----------------PROCEDURES TO POPULATE DEDUCTIONS------------------------------
4466 -----------------------------------------------------------------------------*/
4467 --------------------------------------------------------------------------------
4468 -- NAME : get_calc_prd_dedc
4469 -- DESCRIPTION :
4470 -- PURPOSE :
4471 -- INVOKED FROM :
4472 -- ARGUMENTS :
4473 -- REFERENCE : PN_COMMON.debug()
4474 -- HISTORY :
4475 --
4476 -- 8/10/06 Shabda o Created
4477 --------------------------------------------------------------------------------
4478 PROCEDURE get_calc_prd_dedc( p_var_rent_id IN NUMBER
4479 ,p_period_id IN NUMBER
4480 ,p_line_item_id IN NUMBER
4481 ,p_grp_date_id IN NUMBER
4482 ,p_start_date IN DATE
4483 ,p_end_date IN DATE
4484 ,x_pro_dedc OUT NOCOPY NUMBER
4485 ,x_dedc OUT NOCOPY NUMBER) IS
4486
4487
4488 /* get volumes for a calculation period */
4489 CURSOR dedc_c1 ( p_prd_id IN NUMBER
4490 ,p_line_id IN NUMBER
4491 ,p_grp_id IN NUMBER) IS
4492 SELECT
4493 SUM(deduction_amount) AS calc_prd_dedc
4494 FROM
4495 pn_var_deductions_all dedc
4496 WHERE
4497 dedc.period_id = p_prd_id AND
4498 dedc.line_item_id = p_line_id AND
4499 dedc.grp_date_id = p_grp_id;
4500
4501 /* get volumes for a calculation sub-period */
4502 CURSOR dedc_c2 ( p_prd_id IN NUMBER
4503 ,p_line_id IN NUMBER
4504 ,p_grp_id IN NUMBER
4505 ,p_start_dt IN DATE
4506 ,p_end_dt IN DATE) IS
4507 SELECT
4508 dedc.deduction_amount
4509 ,dedc.start_date
4510 ,dedc.end_date
4511 FROM
4512 pn_var_deductions_all dedc
4513 WHERE
4514 dedc.period_id = p_prd_id AND
4515 dedc.line_item_id = p_line_id AND
4516 dedc.grp_date_id = p_grp_id AND
4517 dedc.start_date <= p_end_dt AND
4518 dedc.end_date >= p_start_dt;
4519
4520 /* get grp dates */
4521 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
4522 SELECT
4523 grp.grp_start_date
4524 ,grp.grp_end_date
4525 FROM
4526 pn_var_grp_dates_all grp
4527 WHERE
4528 grp.grp_date_id = p_grp_id;
4529
4530 l_grp_start_date DATE;
4531 l_grp_end_date DATE;
4532
4533 l_calc_prd_dedc NUMBER;
4534 l_pro_calc_prd_dedc NUMBER;
4535
4536 BEGIN
4537 /* get group / calc period dates */
4538 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
4539 l_grp_start_date := grp_rec.grp_start_date;
4540 l_grp_end_date := grp_rec.grp_end_date;
4541 END LOOP;
4542
4543 l_calc_prd_dedc := 0;
4544 l_pro_calc_prd_dedc := 0;
4545
4546 /* get all APPROVED deductions for a group / calc period */
4547 FOR dedc_rec IN dedc_c1 ( p_prd_id => p_period_id
4548 ,p_line_id => p_line_item_id
4549 ,p_grp_id => p_grp_date_id)
4550 LOOP
4551 l_calc_prd_dedc := l_calc_prd_dedc + dedc_rec.calc_prd_dedc;
4552 END LOOP;
4553
4554 /* if calc sub period dates are same as grp / calc period start-end dates */
4555 IF l_grp_start_date = p_start_date AND
4556 l_grp_end_date = p_end_date
4557 THEN
4558
4559 /* then prorated deductions = total deductions */
4560 l_pro_calc_prd_dedc := l_calc_prd_dedc;
4561
4562 ELSE
4563
4564 /* else, sum all deductions to get the prorated deductions */
4565 FOR dedc_rec IN dedc_c2 ( p_prd_id => p_period_id
4566 ,p_line_id => p_line_item_id
4567 ,p_grp_id => p_grp_date_id
4568 ,p_start_dt => p_start_date
4569 ,p_end_dt => p_end_date)
4570 LOOP
4571
4572 /* if deductions volume dates between calc sub period dates */
4573 IF dedc_rec.start_date >= p_start_date AND
4574 dedc_rec.end_date <= p_end_date
4575 THEN
4576 /* consider full volume */
4577 l_pro_calc_prd_dedc := l_pro_calc_prd_dedc + dedc_rec.deduction_amount;
4578
4579 /* else if deductions volume dates overlap calc sub period dates */
4580 ELSIF dedc_rec.start_date <= p_end_date AND
4581 dedc_rec.end_date >= p_start_date
4582 THEN
4583 /* then consider prorated volume */
4584 l_pro_calc_prd_dedc
4585 := l_pro_calc_prd_dedc
4586 + dedc_rec.deduction_amount
4587 * ((LEAST(dedc_rec.end_date, p_end_date)
4588 - GREATEST(dedc_rec.start_date, p_start_date)) + 1)
4589 / ((dedc_rec.end_date - dedc_rec.start_date) + 1);
4590
4591 END IF;
4592
4593 END LOOP;
4594
4595 END IF;
4596 x_pro_dedc := l_pro_calc_prd_dedc;
4597 x_dedc := l_calc_prd_dedc;
4598
4599 EXCEPTION
4600 WHEN OTHERS THEN RAISE;
4601
4602 END get_calc_prd_dedc;
4603
4604 --------------------------------------------------------------------------------
4605 -- NAME : get_calc_prd_dedc
4606 -- DESCRIPTION :
4607 -- PURPOSE :
4608 -- INVOKED FROM :
4609 -- ARGUMENTS :
4610 -- REFERENCE : PN_COMMON.debug()
4611 -- HISTORY :
4612 --
4613 -- 8/10/06 Shabda o Created
4614 --------------------------------------------------------------------------------
4615 FUNCTION get_calc_prd_dedc( p_var_rent_id IN NUMBER
4616 ,p_period_id IN NUMBER
4617 ,p_line_item_id IN NUMBER
4618 ,p_grp_date_id IN NUMBER
4619 ,p_start_date IN DATE
4620 ,p_end_date IN DATE)
4621
4622 RETURN NUMBER IS
4623
4624
4625 /* get deductions for a calculation period */
4626 CURSOR dedc_c1 ( p_prd_id IN NUMBER
4627 ,p_line_id IN NUMBER
4628 ,p_grp_id IN NUMBER) IS
4629 SELECT
4630 SUM(deduction_amount) AS calc_prd_dedc
4631 FROM
4632 pn_var_deductions_all dedc
4633 WHERE
4634 dedc.period_id = p_prd_id AND
4635 dedc.line_item_id = p_line_id AND
4636 dedc.grp_date_id = p_grp_id;
4637
4638 /* get deductions for a calculation sub-period */
4639 CURSOR dedc_c2 ( p_prd_id IN NUMBER
4640 ,p_line_id IN NUMBER
4641 ,p_grp_id IN NUMBER
4642 ,p_start_dt IN DATE
4643 ,p_end_dt IN DATE) IS
4644 SELECT
4645 dedc.deduction_amount
4646 ,dedc.start_date
4647 ,dedc.end_date
4648 FROM
4649 pn_var_deductions_all dedc
4650 WHERE
4651 dedc.period_id = p_prd_id AND
4652 dedc.line_item_id = p_line_id AND
4653 dedc.grp_date_id = p_grp_id AND
4654 dedc.start_date <= p_end_dt AND
4655 dedc.end_date >= p_start_dt;
4656
4657 /* get grp dates */
4658 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
4659 SELECT
4660 grp.grp_start_date
4661 ,grp.grp_end_date
4662 FROM
4663 pn_var_grp_dates_all grp
4664 WHERE
4665 grp.grp_date_id = p_grp_id;
4666
4667 l_grp_start_date DATE;
4668 l_grp_end_date DATE;
4669
4670 l_calc_prd_dedc NUMBER;
4671 l_pro_calc_prd_dedc NUMBER;
4672
4673 BEGIN
4674 /* get group / calc period dates */
4675 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
4676 l_grp_start_date := grp_rec.grp_start_date;
4677 l_grp_end_date := grp_rec.grp_end_date;
4678 END LOOP;
4679
4680 l_calc_prd_dedc := 0;
4681 l_pro_calc_prd_dedc := 0;
4682
4683 /* get all APPROVED deductions for a group / calc period */
4684 FOR dedc_rec IN dedc_c1 ( p_prd_id => p_period_id
4685 ,p_line_id => p_line_item_id
4686 ,p_grp_id => p_grp_date_id)
4687 LOOP
4688 l_calc_prd_dedc := l_calc_prd_dedc + dedc_rec.calc_prd_dedc;
4689 END LOOP;
4690
4691 /* if calc sub period dates are same as grp / calc period start-end dates */
4692 IF l_grp_start_date = p_start_date AND
4693 l_grp_end_date = p_end_date
4694 THEN
4695
4696 /* then prorated deductions = total deductions */
4697 l_pro_calc_prd_dedc := l_calc_prd_dedc;
4698
4699 ELSE
4700
4701 /* else, sum all deductions to get the prorated deductions */
4702 FOR dedc_rec IN dedc_c2 ( p_prd_id => p_period_id
4703 ,p_line_id => p_line_item_id
4704 ,p_grp_id => p_grp_date_id
4705 ,p_start_dt => p_start_date
4706 ,p_end_dt => p_end_date)
4707 LOOP
4708
4709 /* if deductions volume dates between calc sub period dates */
4710 IF dedc_rec.start_date >= p_start_date AND
4711 dedc_rec.end_date <= p_end_date
4712 THEN
4713 /* consider full volume */
4714 l_pro_calc_prd_dedc := l_pro_calc_prd_dedc + dedc_rec.deduction_amount;
4715
4716 /* else if deductions volume dates overlap calc sub period dates */
4717 ELSIF dedc_rec.start_date <= p_end_date AND
4718 dedc_rec.end_date >= p_start_date
4719 THEN
4720 /* then consider prorated volume */
4721 l_pro_calc_prd_dedc
4722 := l_pro_calc_prd_dedc
4723 + dedc_rec.deduction_amount
4724 * ((LEAST(dedc_rec.end_date, p_end_date)
4725 - GREATEST(dedc_rec.start_date, p_start_date)) + 1)
4726 / ((dedc_rec.end_date - dedc_rec.start_date) + 1);
4727
4728 END IF;
4729
4730 END LOOP;
4731
4732 END IF;
4733 RETURN l_pro_calc_prd_dedc;
4734
4735 EXCEPTION
4736 WHEN OTHERS THEN RAISE;
4737
4738 END get_calc_prd_dedc;
4739 --------------------------------------------------------------------------------
4740 -- NAME : populate_ly_pro_dedc
4741 -- DESCRIPTION :
4742 -- PURPOSE :
4743 -- INVOKED FROM :
4744 -- ARGUMENTS :
4745 -- REFERENCE : PN_COMMON.debug()
4746 -- HISTORY :
4747 --
4748 -- 12/10/06 Shabda o Created
4749 --------------------------------------------------------------------------------
4750 PROCEDURE populate_ly_pro_dedc( p_var_rent_id IN NUMBER
4751 ,p_proration_rule IN VARCHAR2
4752 ,p_vr_commencement_dt IN DATE
4753 ,p_vr_termination_dt IN DATE) IS
4754
4755 /* get VR details */
4756 CURSOR vr_c(p_vr_id IN NUMBER) IS
4757 SELECT
4758 vr.var_rent_id
4759 ,vr.commencement_date
4760 ,vr.termination_date
4761 ,vr.proration_rule
4762 FROM
4763 pn_var_rents_all vr
4764 WHERE
4765 vr.var_rent_id = p_vr_id;
4766
4767 l_vr_commencement_date DATE;
4768 l_vr_termination_date DATE;
4769 l_vr_proration_rule VARCHAR2(30);
4770 l_ly_start_date DATE;
4771
4772 BEGIN
4773
4774 /* get VR details */
4775 IF p_proration_rule IS NULL OR
4776 p_vr_commencement_dt IS NULL OR
4777 p_vr_termination_dt IS NULL
4778 THEN
4779 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
4780 l_vr_commencement_date := vr_rec.commencement_date;
4781 l_vr_termination_date := vr_rec.termination_date;
4782 l_vr_proration_rule := vr_rec.proration_rule;
4783 END LOOP;
4784 ELSE
4785 l_vr_commencement_date := p_vr_commencement_dt;
4786 l_vr_termination_date := p_vr_termination_dt;
4787 l_vr_proration_rule := p_proration_rule;
4788 END IF;
4789
4790 l_ly_start_date := ADD_MONTHS(l_vr_termination_date, -12) + 1;
4791
4792 IF l_vr_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_LY
4793 ,pn_var_trx_pkg.G_PRORUL_FLY) THEN
4794
4795 /* init ly_proration_sales */
4796 UPDATE
4797 pn_var_trx_headers_all
4798 SET
4799 ly_proration_deductions = NULL
4800 WHERE
4801 var_rent_id = p_var_rent_id;
4802
4803 /* populate ly_proration_sales */
4804 UPDATE
4805 pn_var_trx_headers_all hdr
4806 SET
4807 hdr.ly_proration_deductions = hdr.prorated_group_deductions
4808 WHERE
4809 hdr.var_rent_id = p_var_rent_id AND
4810 hdr.calc_prd_start_date >= l_ly_start_date;
4811
4812 /* populate ly_proration_sales if LY start date does not
4813 coincide with a calc prd start date */
4814 UPDATE
4815 pn_var_trx_headers_all
4816 SET
4817 ly_proration_deductions
4818 = pn_var_trx_pkg.get_calc_prd_dedc( var_rent_id
4819 ,period_id
4820 ,line_item_id
4821 ,grp_date_id
4822 ,l_ly_start_date
4823 ,calc_prd_end_date)
4824 WHERE
4825 var_rent_id = p_var_rent_id AND
4826 l_ly_start_date BETWEEN (calc_prd_start_date + 1)
4827 AND calc_prd_end_date;
4828
4829 END IF;
4830
4831 EXCEPTION
4832 WHEN OTHERS THEN RAISE;
4833
4834 END populate_ly_pro_dedc;
4835
4836
4837 --------------------------------------------------------------------------------
4838 -- NAME : populate_fy_pro_dedc
4839 -- DESCRIPTION :
4840 -- PURPOSE :
4841 -- INVOKED FROM :
4842 -- ARGUMENTS :
4843 -- REFERENCE : PN_COMMON.debug()
4844 -- HISTORY :
4845 --
4846 -- 10/10/06 Shabda o Created
4847 --------------------------------------------------------------------------------
4848 PROCEDURE populate_fy_pro_dedc( p_var_rent_id IN NUMBER
4849 ,p_proration_rule IN VARCHAR2
4850 ,p_vr_commencement_dt IN DATE
4851 ,p_vr_termination_dt IN DATE) IS
4852
4853 /* get VR details */
4854 CURSOR vr_c(p_vr_id IN NUMBER) IS
4855 SELECT
4856 vr.var_rent_id
4857 ,vr.commencement_date
4858 ,vr.termination_date
4859 ,vr.proration_rule
4860 FROM
4861 pn_var_rents_all vr
4862 WHERE
4863 vr.var_rent_id = p_vr_id;
4864
4865 l_vr_commencement_date DATE;
4866 l_vr_termination_date DATE;
4867 l_vr_proration_rule VARCHAR2(30);
4868 l_fy_end_date DATE;
4869
4870 BEGIN
4871
4872 /* get VR details */
4873 IF p_proration_rule IS NULL OR
4874 p_vr_commencement_dt IS NULL OR
4875 p_vr_termination_dt IS NULL
4876 THEN
4877 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
4878 l_vr_commencement_date := vr_rec.commencement_date;
4879 l_vr_termination_date := vr_rec.termination_date;
4880 l_vr_proration_rule := vr_rec.proration_rule;
4881 END LOOP;
4882 ELSE
4883 l_vr_commencement_date := p_vr_commencement_dt;
4884 l_vr_termination_date := p_vr_termination_dt;
4885 l_vr_proration_rule := p_proration_rule;
4886 END IF;
4887
4888 l_fy_end_date := ADD_MONTHS(l_vr_commencement_date, 12) - 1;
4889
4890 IF l_vr_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
4891 ,pn_var_trx_pkg.G_PRORUL_FLY) THEN
4892
4893 /* init ly_proration_deductions */
4894 UPDATE
4895 pn_var_trx_headers_all
4896 SET
4897 fy_proration_deductions = NULL
4898 WHERE
4899 var_rent_id = p_var_rent_id;
4900
4901 /* populate ly_proration_deductions */
4902 UPDATE
4903 pn_var_trx_headers_all hdr
4904 SET
4905 hdr.fy_proration_deductions = hdr.prorated_group_deductions
4906 WHERE
4907 hdr.var_rent_id = p_var_rent_id AND
4908 hdr.calc_prd_end_date <= l_fy_end_date;
4909
4910 /* populate fy_proration_deductions if FY end date does not
4911 coincide with a calc prd end date */
4912 UPDATE
4913 pn_var_trx_headers_all
4914 SET
4915 fy_proration_deductions
4916 = pn_var_trx_pkg.get_calc_prd_dedc( var_rent_id
4917 ,period_id
4918 ,line_item_id
4919 ,grp_date_id
4920 ,calc_prd_start_date
4921 ,l_fy_end_date)
4922 WHERE
4923 var_rent_id = p_var_rent_id AND
4924 l_fy_end_date BETWEEN calc_prd_start_date
4925 AND (calc_prd_end_date - 1);
4926
4927 END IF;
4928
4929 EXCEPTION
4930 WHEN OTHERS THEN RAISE;
4931
4932 END populate_fy_pro_dedc;
4933
4934 --------------------------------------------------------------------------------
4935 -- NAME : populate_ytd_deductions
4936 -- DESCRIPTION :
4937 -- PURPOSE :
4938 -- INVOKED FROM :
4939 -- ARGUMENTS :
4940 -- REFERENCE : PN_COMMON.debug()
4941 -- HISTORY :
4942 --
4943 -- 10-10-06 Shabda o Created
4944 --------------------------------------------------------------------------------
4945 PROCEDURE populate_ytd_deductions( p_var_rent_id IN NUMBER
4946 ,p_proration_rule IN VARCHAR2) IS
4947
4948 /* get VR info */
4949 CURSOR vr_c(p_vr_id IN NUMBER) IS
4950 SELECT
4951 vr.var_rent_id
4952 ,vr.proration_rule
4953 FROM
4954 pn_var_rents_all vr
4955 WHERE
4956 vr.var_rent_id = p_vr_id;
4957
4958 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
4959
4960 /* get the line items with updated deductions for FY, LY, FLY, STD, NP */
4961 CURSOR lines_c(p_vr_id IN NUMBER) IS
4962 SELECT
4963 period_id
4964 ,line_item_id
4965 FROM
4966 pn_var_lines_all
4967 WHERE
4968 var_rent_id = p_vr_id
4969 ORDER BY
4970 period_id
4971 ,line_item_id;
4972
4973 /* get the line items with updated deductions for CYP, CYNP */
4974 CURSOR lines_cs_c( p_vr_id IN NUMBER
4975 ,p_part_prd_id IN NUMBER
4976 ,p_full_prd_id IN NUMBER) IS
4977 SELECT
4978 period_id
4979 ,line_item_id
4980 FROM
4981 pn_var_lines_all
4982 WHERE
4983 var_rent_id = p_vr_id AND
4984 period_id NOT IN (p_part_prd_id, p_full_prd_id)
4985 ORDER BY
4986 period_id
4987 ,line_item_id;
4988
4989 /* get the period details - we use the first 2 periods */
4990 CURSOR periods_c(p_vr_id IN NUMBER) IS
4991 SELECT
4992 period_id
4993 ,start_date
4994 ,end_date
4995 ,partial_period
4996 FROM
4997 pn_var_periods_all
4998 WHERE
4999 var_rent_id = p_vr_id
5000 ORDER BY
5001 start_date;
5002
5003 /* period info */
5004 l_part_prd_id NUMBER;
5005 l_part_prd_start_dt DATE;
5006 l_part_prd_end_dt DATE;
5007 l_part_prd_partial_flag VARCHAR2(1);
5008
5009 l_full_prd_id NUMBER;
5010 l_full_prd_start_dt DATE;
5011 l_full_prd_end_dt DATE;
5012 l_full_prd_partial_flag VARCHAR2(1);
5013
5014 /* ytd for STD, NP, FY, LY, FLY */
5015 CURSOR ytd_deductions_c( p_vr_ID IN NUMBER
5016 ,p_prd_ID IN NUMBER
5017 ,p_line_ID IN NUMBER) IS
5018 SELECT
5019 hdr.trx_header_id
5020 ,SUM(hdr.prorated_group_deductions) OVER
5021 (PARTITION BY
5022 hdr.period_id
5023 ,hdr.line_item_id
5024 ,hdr.reset_group_id
5025 ORDER BY
5026 hdr.calc_prd_start_date
5027 ROWS UNBOUNDED PRECEDING) AS ytd_deductions
5028 FROM
5029 pn_var_trx_headers_all hdr
5030 WHERE
5031 hdr.var_rent_id = p_vr_id AND
5032 hdr.period_id = p_prd_ID AND
5033 hdr.line_item_id = p_line_ID
5034 ORDER BY
5035 hdr.period_id
5036 ,hdr.line_item_id
5037 ,hdr.calc_prd_start_date;
5038
5039 /* ytd for CYP, CYNP combined period */
5040 CURSOR ytd_deductions_cs_c( p_vr_ID IN NUMBER
5041 ,p_part_prd_id IN NUMBER
5042 ,p_full_prd_id IN NUMBER) IS
5043 SELECT
5044 hdr.trx_header_id
5045 ,SUM(hdr.prorated_group_deductions) OVER
5046 (PARTITION BY
5047 hdr.line_item_group_id
5048 ORDER BY
5049 hdr.calc_prd_start_date
5050 ROWS UNBOUNDED PRECEDING) AS ytd_deductions
5051 FROM
5052 pn_var_trx_headers_all hdr
5053 WHERE
5054 hdr.var_rent_id = p_vr_id AND
5055 hdr.period_id IN (p_part_prd_id, p_full_prd_id)
5056 ORDER BY
5057 hdr.line_item_group_id
5058 ,hdr.calc_prd_start_date;
5059
5060 /* counters */
5061 l_counter1 NUMBER;
5062
5063 /* plsql tables for ytd dates and trx hdr */
5064 trx_hdr_t NUM_T;
5065 ytd_deductions_t NUM_T;
5066
5067 BEGIN
5068
5069 pnp_debug_pkg.log('++++ pn_var_trx_pkg.populate_ytd_deductions START ++++');
5070
5071 /* get VR details */
5072 IF p_proration_rule IS NULL THEN
5073
5074 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
5075
5076 l_proration_rule := vr_rec.proration_rule;
5077
5078 END LOOP;
5079
5080 ELSE
5081
5082 l_proration_rule := p_proration_rule;
5083
5084 END IF;
5085
5086 pnp_debug_pkg.log('Called with: ');
5087 pnp_debug_pkg.log(' p_var_rent_id: '||p_var_rent_id);
5088 pnp_debug_pkg.log(' l_proration_rule: '||l_proration_rule);
5089
5090 /* l_proration_rule based decisions */
5091 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
5092 ,pn_var_trx_pkg.G_PRORUL_LY
5093 ,pn_var_trx_pkg.G_PRORUL_FLY
5094 ,pn_var_trx_pkg.G_PRORUL_NP
5095 ,pn_var_trx_pkg.G_PRORUL_STD) THEN
5096
5097 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id) LOOP
5098
5099 trx_hdr_t.DELETE;
5100 ytd_deductions_t.DELETE;
5101
5102 OPEN ytd_deductions_c( p_vr_ID => p_var_rent_id
5103 ,p_prd_ID => line_rec.period_id
5104 ,p_line_ID => line_rec.line_item_id);
5105
5106 FETCH ytd_deductions_c BULK COLLECT INTO
5107 trx_hdr_t
5108 ,ytd_deductions_t;
5109
5110 CLOSE ytd_deductions_c;
5111
5112 FORALL i IN 1..trx_hdr_t.COUNT
5113 UPDATE
5114 pn_var_trx_headers_all
5115 SET
5116 ytd_deductions = ytd_deductions_t(i)
5117 WHERE
5118 trx_header_id = trx_hdr_t(i);
5119
5120 END LOOP;
5121
5122 ELSIF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
5123 ,pn_var_trx_pkg.G_PRORUL_CYNP) THEN
5124
5125 /* fetch partial and full period details */
5126 l_counter1 := 0;
5127 FOR prd_rec IN periods_c(p_vr_id => p_var_rent_id) LOOP
5128
5129 l_counter1 := l_counter1 + 1;
5130
5131 IF l_counter1 = 1 THEN
5132 l_part_prd_id := prd_rec.period_id;
5133 l_part_prd_start_dt := prd_rec.start_date;
5134 l_part_prd_end_dt := prd_rec.end_date;
5135 l_part_prd_partial_flag := prd_rec.partial_period;
5136
5137 ELSIF l_counter1 = 2 THEN
5138 l_full_prd_id := prd_rec.period_id;
5139 l_full_prd_start_dt := prd_rec.start_date;
5140 l_full_prd_end_dt := prd_rec.end_date;
5141 l_full_prd_partial_flag := prd_rec.partial_period;
5142
5143 ELSE
5144 EXIT;
5145
5146 END IF;
5147
5148 END LOOP; /* fetch partial and full period details */
5149
5150 trx_hdr_t.DELETE;
5151 ytd_deductions_t.DELETE;
5152
5153 OPEN ytd_deductions_cs_c( p_vr_ID => p_var_rent_id
5154 ,p_part_prd_id => l_part_prd_id
5155 ,p_full_prd_id => l_full_prd_id);
5156
5157 FETCH ytd_deductions_cs_c BULK COLLECT INTO
5158 trx_hdr_t
5159 ,ytd_deductions_t;
5160
5161 CLOSE ytd_deductions_cs_c;
5162
5163 FORALL i IN 1..trx_hdr_t.COUNT
5164 UPDATE
5165 pn_var_trx_headers_all
5166 SET
5167 ytd_deductions = ytd_deductions_t(i)
5168 WHERE
5169 trx_header_id = trx_hdr_t(i);
5170
5171 /* loop for all lines */
5172 FOR line_rec IN lines_cs_c ( p_vr_id => p_var_rent_id
5173 ,p_part_prd_id => l_part_prd_id
5174 ,p_full_prd_id => l_full_prd_id)
5175 LOOP
5176
5177 trx_hdr_t.DELETE;
5178 ytd_deductions_t.DELETE;
5179
5180 OPEN ytd_deductions_c( p_vr_ID => p_var_rent_id
5181 ,p_prd_ID => line_rec.period_id
5182 ,p_line_ID => line_rec.line_item_id);
5183
5184 FETCH ytd_deductions_c BULK COLLECT INTO
5185 trx_hdr_t
5186 ,ytd_deductions_t;
5187
5188 CLOSE ytd_deductions_c;
5189
5190 FORALL i IN 1..trx_hdr_t.COUNT
5191 UPDATE
5192 pn_var_trx_headers_all
5193 SET
5194 ytd_deductions = ytd_deductions_t(i)
5195 WHERE
5196 trx_header_id = trx_hdr_t(i);
5197
5198 END LOOP; /* loop for all lines */
5199
5200 END IF; /* l_proration_rule based decisions */
5201
5202 EXCEPTION
5203 WHEN OTHERS THEN RAISE;
5204
5205 END populate_ytd_deductions;
5206
5207 --------------------------------------------------------------------------------
5208 -- NAME : populate_deductions
5209 -- DESCRIPTION :
5210 -- PURPOSE :
5211 -- INVOKED FROM :
5212 -- ARGUMENTS :
5213 -- REFERENCE : PN_COMMON.debug()
5214 -- HISTORY :
5215 --
5216 -- 10/10/06 Shabda o Created
5217 --------------------------------------------------------------------------------
5218 PROCEDURE populate_deductions(p_var_rent_id IN NUMBER) IS
5219
5220 /* get VR info */
5221 CURSOR vr_c(p_vr_id IN NUMBER) IS
5222 SELECT
5223 vr.org_id
5224 ,vr.var_rent_id
5225 ,vr.commencement_date
5226 ,vr.termination_date
5227 ,vr.proration_rule
5228 ,vr.cumulative_vol
5229 FROM
5230 pn_var_rents_all vr
5231 WHERE
5232 vr.var_rent_id = p_vr_id;
5233
5234 l_org_id pn_var_rents_all.org_id%TYPE;
5235 l_vr_commencement_date pn_var_rents_all.commencement_date%TYPE;
5236 l_vr_termination_date pn_var_rents_all.termination_date%TYPE;
5237 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
5238 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
5239
5240 /* get the line items*/
5241 CURSOR lines_c(p_vr_id IN NUMBER) IS
5242 SELECT
5243 period_id
5244 ,line_item_id
5245 FROM
5246 pn_var_lines_all
5247 WHERE
5248 var_rent_id = p_vr_id
5249 ORDER BY
5250 period_id
5251 ,line_item_id;
5252
5253 /* get the calc periods to update deductions data */
5254 CURSOR calc_periods_c( p_vr_id IN NUMBER
5255 ,p_prd_id IN NUMBER
5256 ,p_line_id IN NUMBER) IS
5257 SELECT
5258 hdr.trx_header_id
5259 ,hdr.var_rent_id
5260 ,hdr.period_id
5261 ,hdr.line_item_id
5262 ,hdr.grp_date_id
5263 ,hdr.calc_prd_start_date
5264 ,hdr.calc_prd_end_date
5265 FROM
5266 pn_var_trx_headers_all hdr
5267 WHERE
5268 hdr.var_rent_id = p_vr_id AND
5269 hdr.period_id = p_prd_id AND
5270 hdr.line_item_id = p_line_id
5271 ORDER BY
5272 hdr.period_id
5273 ,hdr.line_item_id
5274 ,hdr.calc_prd_start_date
5275 ,hdr.calc_prd_end_date;
5276
5277 /* data structures */
5278 trx_hdr_t NUM_T;
5279 reporting_grp_dedc_t NUM_T;
5280 prorate_grp_dedc_t NUM_T;
5281
5282 l_counter NUMBER;
5283
5284 /* flags */
5285
5286 l_line_items_t NUM_T;
5287
5288 BEGIN
5289 l_line_items_t.DELETE;
5290
5291 OPEN line_items_c(p_vr_id => p_var_rent_id);
5292 FETCH line_items_c BULK COLLECT INTO l_line_items_t;
5293 CLOSE line_items_c;
5294
5295 /* get the VR details */
5296 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
5297
5298 l_org_id := vr_rec.org_id;
5299 l_vr_commencement_date := vr_rec.commencement_date;
5300 l_vr_termination_date := vr_rec.termination_date;
5301 l_proration_rule := vr_rec.proration_rule;
5302 l_calculation_method := vr_rec.cumulative_vol;
5303
5304 END LOOP;
5305
5306 /* for all line items */
5307 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id)
5308 LOOP
5309 l_counter := 0;
5310 trx_hdr_t.DELETE;
5311 reporting_grp_dedc_t.DELETE;
5312 prorate_grp_dedc_t.DELETE;
5313
5314 /* for all calc sub periods for the line item, get the prorated deductions */
5315 FOR trx_rec IN calc_periods_c( p_vr_id => p_var_rent_id
5316 ,p_prd_id => line_rec.period_id
5317 ,p_line_id => line_rec.line_item_id)
5318 LOOP
5319
5320 l_counter := l_counter + 1;
5321
5322 trx_hdr_t(l_counter) := trx_rec.trx_header_id;
5323
5324 pn_var_trx_pkg.get_calc_prd_dedc
5325 ( p_var_rent_id => trx_rec.var_rent_id
5326 ,p_period_id => trx_rec.period_id
5327 ,p_line_item_id => trx_rec.line_item_id
5328 ,p_grp_date_id => trx_rec.grp_date_id
5329 ,p_start_date => trx_rec.calc_prd_start_date
5330 ,p_end_date => trx_rec.calc_prd_end_date
5331 ,x_pro_dedc => prorate_grp_dedc_t(l_counter)
5332 ,x_dedc => reporting_grp_dedc_t(l_counter));
5333
5334 END LOOP;
5335
5336 /* for all calc sub periods for the line item,
5337 update the trx headers with the deductions */
5338 IF trx_hdr_t.COUNT > 0 THEN
5339
5340 FORALL i IN trx_hdr_t.FIRST..trx_hdr_t.LAST
5341 UPDATE
5342 pn_var_trx_headers_all
5343 SET
5344 reporting_group_deductions = reporting_grp_dedc_t(i)
5345 ,prorated_group_deductions = prorate_grp_dedc_t(i)
5346 WHERE
5347 trx_header_id = trx_hdr_t(i);
5348
5349 END IF;
5350
5351 END LOOP;
5352
5353
5354 IF l_proration_rule = pn_var_trx_pkg.G_PRORUL_LY THEN
5355
5356 pn_var_trx_pkg.populate_ly_pro_dedc
5357 ( p_var_rent_id => p_var_rent_id
5358 ,p_proration_rule => l_proration_rule
5359 ,p_vr_commencement_dt => l_vr_commencement_date
5360 ,p_vr_termination_dt => l_vr_termination_date);
5361
5362 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FY THEN
5363
5364 pn_var_trx_pkg.populate_fy_pro_dedc
5365 ( p_var_rent_id => p_var_rent_id
5366 ,p_proration_rule => l_proration_rule
5367 ,p_vr_commencement_dt => l_vr_commencement_date
5368 ,p_vr_termination_dt => l_vr_termination_date);
5369
5370 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FLY THEN
5371
5372 pn_var_trx_pkg.populate_ly_pro_dedc
5373 ( p_var_rent_id => p_var_rent_id
5374 ,p_proration_rule => l_proration_rule
5375 ,p_vr_commencement_dt => l_vr_commencement_date
5376 ,p_vr_termination_dt => l_vr_termination_date);
5377
5378 pn_var_trx_pkg.populate_fy_pro_dedc
5379 ( p_var_rent_id => p_var_rent_id
5380 ,p_proration_rule => l_proration_rule
5381 ,p_vr_commencement_dt => l_vr_commencement_date
5382 ,p_vr_termination_dt => l_vr_termination_date);
5383
5384 END IF;
5385
5386 /* always populate YTD deductions - Because we always populate YTD sales */
5387 pn_var_trx_pkg.populate_ytd_deductions
5388 ( p_var_rent_id => p_var_rent_id
5389 ,p_proration_rule => l_proration_rule);
5390
5391 EXCEPTION
5392 WHEN OTHERS THEN RAISE;
5393
5394 END populate_deductions;
5395
5396
5397
5398
5399 END pn_var_trx_pkg;