[Home] [Help]
PACKAGE BODY: APPS.PN_VAR_TRX_PKG
Source
1 PACKAGE BODY pn_var_trx_pkg AS
2 -- $Header: PNVRTRXB.pls 120.3 2011/04/09 16:40:32 asahoo ship $
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 );
480 END LOOP;
477
478 l_line_grp_id := l_line_grp_id + 1;
479
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
667 pn_var_trx_headers_all hdr
664 ,hdr.calc_prd_start_date
665 ,hdr.calc_prd_end_date
666 FROM
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
834 /* get VR details */
831 ,p_vr_commencement_dt IN DATE
832 ,p_vr_termination_dt IN DATE) IS
833
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
999 trx_header_id = trx_rec.trx_header_id;
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
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
1171
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;
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
1332
1329 WHERE
1330 var_rent_id = p_var_rent_id AND
1331 invoice_flag = 'P';
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 :
1481 -- dd-mon-yyyy name o Created
1478 -- REFERENCE : PN_COMMON.debug()
1479 -- HISTORY :
1480 --
1482 -- 20-Apr-2010 jsundara o Bug 9609370 Introduced 2 new cursors to handle the
1483 -- diff bkpt range within same yr, diff durn, same rate
1484 --------------------------------------------------------------------------------
1485 PROCEDURE populate_ytd_pro_vol( p_var_rent_id IN NUMBER
1486 ,p_proration_rule IN VARCHAR2) IS
1487
1488 /* get VR info */
1489 CURSOR vr_c(p_vr_id IN NUMBER) IS
1490 SELECT
1491 vr.org_id
1492 ,vr.var_rent_id
1493 ,vr.commencement_date
1494 ,vr.termination_date
1495 ,vr.proration_rule
1496 ,vr.cumulative_vol
1497 FROM
1498 pn_var_rents_all vr
1499 WHERE
1500 vr.var_rent_id = p_vr_id;
1501
1502 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
1503
1504 /* get the period details - we use the first 2 periods */
1505 CURSOR periods_c(p_vr_id IN NUMBER) IS
1506 SELECT
1507 period_id
1508 ,start_date
1509 ,end_date
1510 ,partial_period
1511 FROM
1512 pn_var_periods_all
1513 WHERE
1514 var_rent_id = p_vr_id
1515 ORDER BY
1516 start_date;
1517
1518 /* period info */
1519 l_part_prd_id NUMBER;
1520 l_part_prd_start_dt DATE;
1521 l_part_prd_end_dt DATE;
1522 l_part_prd_partial_flag VARCHAR2(1);
1523
1524 l_full_prd_id NUMBER;
1525 l_full_prd_start_dt DATE;
1526 l_full_prd_end_dt DATE;
1527 l_full_prd_partial_flag VARCHAR2(1);
1528 l_var_no NUMBER:= 0; /* 9609370 */
1529
1530 /* get the line items to update */
1531 CURSOR lines_c(p_vr_id IN NUMBER) IS
1532 SELECT
1533 period_id
1534 ,line_item_id
1535 FROM
1536 pn_var_lines_all
1537 WHERE
1538 var_rent_id = p_vr_id AND
1539 bkpt_update_flag = 'Y'
1540 ORDER BY
1541 period_id
1542 ,line_item_id;
1543
1544 /* get the line items to update */
1545 CURSOR lines_cs_c ( p_vr_id IN NUMBER
1546 ,p_part_prd_id IN NUMBER
1547 ,p_full_prd_id IN NUMBER) IS
1548 SELECT
1549 period_id
1550 ,line_item_id
1551 FROM
1552 pn_var_lines_all
1553 WHERE
1554 var_rent_id = p_vr_id AND
1555 bkpt_update_flag = 'Y' AND
1556 period_id NOT IN (p_part_prd_id, p_full_prd_id)
1557 ORDER BY
1558 period_id
1559 ,line_item_id;
1560
1561 /* ytd for STD, NP, FY, LY, FLY */
1562 CURSOR ytd_group_vol_c( p_vr_ID IN NUMBER
1563 ,p_prd_ID IN NUMBER
1564 ,p_line_ID IN NUMBER) IS
1565 SELECT /*+ LEADING(hdr) */
1566 dtl.trx_detail_id
1567 ,SUM(prorated_grp_vol_start) OVER
1568 (PARTITION BY
1569 hdr.period_id
1570 ,hdr.line_item_id
1571 ,hdr.reset_group_id
1572 ,pbd.group_bkpt_vol_start
1573 ,pbd.group_bkpt_vol_end
1574 ORDER BY
1575 hdr.calc_prd_start_date
1576 ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_start
1577 ,SUM(prorated_grp_vol_end) OVER
1578 (PARTITION BY
1579 hdr.period_id
1580 ,hdr.line_item_id
1581 ,hdr.reset_group_id
1582 ,pbd.group_bkpt_vol_start
1583 ,pbd.group_bkpt_vol_end
1584 ORDER BY
1585 hdr.calc_prd_start_date
1586 ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_end
1587 FROM
1588 pn_var_trx_headers_all hdr
1589 ,pn_var_trx_details_all dtl
1590 ,PN_VAR_BKPTS_DET_ALL pbd
1591 ,pn_var_bkpts_head_all pbh
1592 WHERE
1593 hdr.var_rent_id = p_vr_id AND
1594 hdr.period_id = p_prd_ID AND
1595 hdr.line_item_id = p_line_ID AND
1596 dtl.trx_header_id = hdr.trx_header_id
1597 and pbd.var_rent_id = hdr.var_rent_id
1598 and pbd.bkpt_rate = dtl.bkpt_rate
1599 and pbd.bkpt_header_id = pbh.bkpt_header_id
1600 and pbd.bkpt_detail_id = dtl.bkpt_detail_id
1601 and pbh.line_item_id = hdr.line_item_id
1602 and pbh.period_id = hdr.period_id /* 8616530 */
1603 ORDER BY
1604 hdr.period_id
1605 ,hdr.line_item_id
1606 ,hdr.calc_prd_start_date;
1607
1608 CURSOR ytd_group_vol1_c( p_vr_ID IN NUMBER
1609 ,p_prd_ID IN NUMBER
1610 ,p_line_ID IN NUMBER) IS
1611 SELECT /*+ LEADING(hdr) */
1612 dtl.trx_detail_id
1613 ,SUM(prorated_grp_vol_start) OVER
1614 (PARTITION BY
1615 hdr.period_id
1616 ,hdr.line_item_id
1617 ,hdr.reset_group_id
1618 ,dtl.bkpt_rate
1619 ORDER BY
1620 hdr.calc_prd_start_date
1621 ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_start
1622 ,SUM(prorated_grp_vol_end) OVER
1623 (PARTITION BY
1624 hdr.period_id
1625 ,hdr.line_item_id
1626 ,hdr.reset_group_id
1627 ,dtl.bkpt_rate
1628 ORDER BY
1629 hdr.calc_prd_start_date
1630 ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_end
1631 FROM
1632 pn_var_trx_headers_all hdr
1633 ,pn_var_trx_details_all dtl
1634 WHERE
1635 hdr.var_rent_id = p_vr_id AND
1636 hdr.period_id = p_prd_ID AND
1637 hdr.line_item_id = p_line_ID AND
1638 dtl.trx_header_id = hdr.trx_header_id
1639 ORDER BY
1640 hdr.period_id
1641 ,hdr.line_item_id
1642 ,hdr.calc_prd_start_date;
1643
1644
1645 /* ytd for CYP, CYNP combined period */
1646 CURSOR ytd_group_vol_cs_c( p_vr_ID IN NUMBER
1650 dtl.trx_detail_id
1647 ,p_part_prd_id IN NUMBER
1648 ,p_full_prd_id IN NUMBER) IS
1649 SELECT /*+ LEADING(hdr) */
1651 ,SUM(pr_grp_blended_vol_start) OVER
1652 (PARTITION BY
1653 hdr.line_item_group_id
1654 ,dtl.bkpt_rate
1655 ORDER BY
1656 hdr.calc_prd_start_date
1657 ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_start
1658 ,SUM(pr_grp_blended_vol_end) OVER
1659 (PARTITION BY
1660 hdr.line_item_group_id
1661 ,dtl.bkpt_rate
1662 ORDER BY
1663 hdr.calc_prd_start_date
1664 ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_end
1665 FROM
1666 pn_var_trx_headers_all hdr
1667 ,pn_var_trx_details_all dtl
1668 WHERE
1669 hdr.var_rent_id = p_vr_id AND
1670 hdr.period_id IN (p_part_prd_id, p_full_prd_id) AND
1671 dtl.trx_header_id = hdr.trx_header_id
1672 ORDER BY
1673 hdr.line_item_group_id
1674 ,hdr.calc_prd_start_date;
1675
1676 CURSOR rent_no_c1(p_vr_ID IN NUMBER) IS /* 9609370 */
1677 select distinct v.var_rent_id
1678 from pn_var_rents_all v, pn_var_bkhd_defaults_all hd,
1679 pn_var_bkdt_defaults_all dd
1680 WHERE V.VAR_RENT_ID = HD.VAR_RENT_ID
1681 and v.var_rent_id = p_vr_ID
1682 and hd.bkhd_default_id = dd.bkhd_default_id
1683 and hd.breakpoint_type = 'STRATIFIED'
1684 and exists (select 'Y'
1685 from pn_var_bkdt_defaults_all dd2
1686 where dd2.bkhd_default_id = dd.bkhd_default_id
1687 and dd2.bkdt_default_id <> dd.bkdt_default_id
1688 and dd2.bkpt_rate = dd.bkpt_rate
1689 AND DD2.BKDT_START_DATE = DD.BKDT_START_DATE
1690 and dd2.bkdt_end_date = dd.bkdt_end_date);
1691
1692 /* counters */
1693 l_counter1 NUMBER;
1694
1695 trx_detail_t NUM_T;
1696 ytd_grp_vol_start_t NUM_T;
1697 ytd_grp_vol_end_t NUM_T;
1698
1699 BEGIN
1700
1701 For i in rent_no_c1(p_vr_id => p_var_rent_id) loop
1702 l_var_no := i.var_rent_id;
1703 end loop;
1704
1705 IF p_proration_rule IS NULL THEN
1706
1707 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
1708 l_proration_rule := vr_rec.proration_rule;
1709 END LOOP;
1710
1711 ELSE
1712
1713 l_proration_rule := p_proration_rule;
1714
1715 END IF;
1716
1717 l_counter1 := 1;
1718
1719 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
1720 ,pn_var_trx_pkg.G_PRORUL_LY
1721 ,pn_var_trx_pkg.G_PRORUL_FLY
1722 ,pn_var_trx_pkg.G_PRORUL_NP
1723 ,pn_var_trx_pkg.G_PRORUL_STD) THEN
1724
1725 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id) LOOP
1726
1727 trx_detail_t.DELETE;
1728 ytd_grp_vol_start_t.DELETE;
1729 ytd_grp_vol_end_t.DELETE;
1730
1731 If l_var_no = p_var_rent_id then /* 9609370 */
1732 OPEN ytd_group_vol_c( p_vr_ID => p_var_rent_id
1733 ,p_prd_ID => line_rec.period_id
1734 ,p_line_ID => line_rec.line_item_id);
1735
1736 FETCH ytd_group_vol_c BULK COLLECT INTO
1737 trx_detail_t
1738 ,ytd_grp_vol_start_t
1739 ,ytd_grp_vol_end_t;
1740
1741 CLOSE ytd_group_vol_c;
1742 ELSE
1743 OPEN ytd_group_vol1_c( p_vr_ID => p_var_rent_id
1744 ,p_prd_ID => line_rec.period_id
1745 ,p_line_ID => line_rec.line_item_id);
1746
1747 FETCH ytd_group_vol1_c BULK COLLECT INTO
1748 trx_detail_t
1749 ,ytd_grp_vol_start_t
1750 ,ytd_grp_vol_end_t;
1751
1752 CLOSE ytd_group_vol1_c;
1753 END IF;
1754 pnp_debug_pkg.log('line_rec.period_id - '||line_rec.period_id);
1755 pnp_debug_pkg.log('line_rec.line_item_id- '||line_rec.line_item_id);
1756
1757
1758 FORALL i IN 1..trx_detail_t.COUNT
1759 UPDATE
1760 pn_var_trx_details_all
1761 SET
1762 ytd_group_vol_start = ytd_grp_vol_start_t(i)
1763 ,ytd_group_vol_end = ytd_grp_vol_end_t(i)
1764 WHERE
1765 trx_detail_id = trx_detail_t(i);
1766
1767 END LOOP;
1768
1769 ELSIF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
1770 ,pn_var_trx_pkg.G_PRORUL_CYNP) THEN
1771
1772 /* fetch partial and full period details */
1773 FOR prd_rec IN periods_c(p_vr_id => p_var_rent_id) LOOP
1774
1775 IF l_counter1 = 1 THEN
1776 l_part_prd_id := prd_rec.period_id;
1777 l_part_prd_start_dt := prd_rec.start_date;
1778 l_part_prd_end_dt := prd_rec.end_date;
1779 l_part_prd_partial_flag := prd_rec.partial_period;
1780
1781 ELSIF l_counter1 = 2 THEN
1782 l_full_prd_id := prd_rec.period_id;
1783 l_full_prd_start_dt := prd_rec.start_date;
1784 l_full_prd_end_dt := prd_rec.end_date;
1785 l_full_prd_partial_flag := prd_rec.partial_period;
1786
1787 ELSE
1788 EXIT;
1789
1790 END IF;
1791
1792 l_counter1 := l_counter1 + 1;
1793
1794 END LOOP;
1795
1796 trx_detail_t.DELETE;
1797 ytd_grp_vol_start_t.DELETE;
1798 ytd_grp_vol_end_t.DELETE;
1799
1800 OPEN ytd_group_vol_cs_c ( p_vr_ID => p_var_rent_id
1801 ,p_part_prd_id => l_part_prd_id
1802 ,p_full_prd_id => l_full_prd_id);
1803
1804 FETCH ytd_group_vol_cs_c BULK COLLECT INTO
1805 trx_detail_t
1806 ,ytd_grp_vol_start_t
1807 ,ytd_grp_vol_end_t;
1808
1809 CLOSE ytd_group_vol_cs_c;
1813 pn_var_trx_details_all
1810
1811 FORALL i IN 1..trx_detail_t.COUNT
1812 UPDATE
1814 SET
1815 ytd_group_vol_start = ytd_grp_vol_start_t(i)
1816 ,ytd_group_vol_end = ytd_grp_vol_end_t(i)
1817 WHERE
1818 trx_detail_id = trx_detail_t(i);
1819
1820 FOR line_rec IN lines_cs_c( p_vr_id => p_var_rent_id
1821 ,p_part_prd_id => l_part_prd_id
1822 ,p_full_prd_id => l_full_prd_id) LOOP
1823
1824 trx_detail_t.DELETE;
1825 ytd_grp_vol_start_t.DELETE;
1826 ytd_grp_vol_end_t.DELETE;
1827
1828 OPEN ytd_group_vol_c( p_vr_ID => p_var_rent_id
1829 ,p_prd_ID => line_rec.period_id
1830 ,p_line_ID => line_rec.line_item_id);
1831
1832 FETCH ytd_group_vol_c BULK COLLECT INTO
1833 trx_detail_t
1834 ,ytd_grp_vol_start_t
1835 ,ytd_grp_vol_end_t;
1836
1837 CLOSE ytd_group_vol_c;
1838
1839 FORALL i IN 1..trx_detail_t.COUNT
1840 UPDATE
1841 pn_var_trx_details_all
1842 SET
1843 ytd_group_vol_start = ytd_grp_vol_start_t(i)
1844 ,ytd_group_vol_end = ytd_grp_vol_end_t(i)
1845 WHERE
1846 trx_detail_id = trx_detail_t(i);
1847
1848 END LOOP;
1849
1850 END IF;
1851
1852 EXCEPTION
1853 WHEN OTHERS THEN RAISE;
1854
1855 END populate_ytd_pro_vol;
1856
1857 --------------------------------------------------------------------------------
1858 -- NAME : populate_blended_period_vol
1859 -- DESCRIPTION :
1860 -- PURPOSE :
1861 -- INVOKED FROM :
1862 -- ARGUMENTS :
1863 -- REFERENCE : PN_COMMON.debug()
1864 -- HISTORY :
1865 --
1866 -- dd-mon-yyyy name o Created
1867 --------------------------------------------------------------------------------
1868 PROCEDURE populate_blended_period_vol( p_var_rent_id IN NUMBER
1869 ,p_proration_rule IN VARCHAR2
1870 ,p_calc_method IN VARCHAR2)
1871 IS
1872
1873 /* get VR info */
1874 CURSOR vr_c(p_vr_id IN NUMBER) IS
1875 SELECT
1876 vr.org_id
1877 ,vr.var_rent_id
1878 ,vr.commencement_date
1879 ,vr.termination_date
1880 ,vr.proration_rule
1881 ,vr.cumulative_vol
1882 FROM
1883 pn_var_rents_all vr
1884 WHERE
1885 vr.var_rent_id = p_vr_id;
1886
1887 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
1888 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
1889
1890 /* get the period details - we use the first 2 periods for CYP, CYNP */
1891 CURSOR periods_c(p_vr_id IN NUMBER) IS
1892 SELECT
1893 period_id
1894 FROM
1895 pn_var_periods_all
1896 WHERE
1897 var_rent_id = p_vr_id
1898 ORDER BY
1899 start_date;
1900
1901 /* data structures */
1902 l_period_t NUM_T;
1903
1904 /* counters */
1905 l_prd_counter NUMBER;
1906
1907 /* user defined exceptions */
1908 DO_NOT_PROCESS EXCEPTION;
1909
1910 /* blended period volumes */
1911 CURSOR blended_prd_vol_c( p_vr_id IN NUMBER
1912 ,p_prd_id IN NUMBER) IS
1913 SELECT /*+ LEADING(hdr) */
1914 hdr.period_id
1915 ,hdr.line_item_id
1916 ,hdr.reset_group_id
1917 ,dtl.bkpt_rate
1918 ,dtl.bkpt_detail_id
1919 ,SUM(prorated_grp_vol_start) AS blended_period_vol_start
1920 ,SUM(prorated_grp_vol_end) AS blended_period_vol_end
1921 FROM
1922 pn_var_trx_headers_all hdr
1923 ,pn_var_trx_details_all dtl
1924 WHERE
1925 hdr.var_rent_id = p_vr_id AND
1926 hdr.period_id = p_prd_id AND
1927 hdr.line_item_id IN (SELECT
1928 line_item_id
1929 FROM
1930 pn_var_lines_all
1931 WHERE
1932 var_rent_id = p_vr_id AND
1933 period_id = p_prd_id AND
1934 bkpt_update_flag = 'Y') AND
1935 dtl.trx_header_id = hdr.trx_header_id
1936 GROUP BY
1937 hdr.period_id
1938 ,hdr.line_item_id
1939 ,hdr.reset_group_id
1940 ,dtl.bkpt_rate
1941 ,dtl.bkpt_detail_id; --bug#8631183
1942
1943
1944 -- Get first partial period id
1945 CURSOR check_fst_partial_prd(p_period_id IN NUMBER) IS
1946 SELECT period_id
1947 FROM pn_var_periods_all
1948 WHERE period_id = p_period_id
1949 AND period_num=1
1950 AND partial_period='Y';
1951
1952 /* get the last partial period */
1953 CURSOR last_period_c( p_period_id IN NUMBER) IS
1954 SELECT
1955 prd.period_id
1956 ,prd.partial_period
1957 FROM
1958 pn_var_periods_all prd,
1959 pn_var_rents_all var
1960 WHERE
1961 prd.period_id = p_period_id AND
1962 prd.var_rent_id = var.var_rent_id AND
1963 prd.end_date = var.termination_date AND
1964 prd.partial_period='Y';
1965
1966 BEGIN
1967
1968 IF p_proration_rule IS NULL OR
1969 p_calc_method IS NULL
1970 THEN
1971 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
1972 l_proration_rule := vr_rec.proration_rule;
1973 l_calculation_method := vr_rec.cumulative_vol;
1974 END LOOP;
1975
1976 ELSE
1977 l_proration_rule := p_proration_rule;
1978 l_calculation_method := p_calc_method;
1979
1980 END IF;
1981
1982 l_period_t.DELETE;
1983
1984 OPEN periods_c(p_vr_id => p_var_rent_id);
1988 FOR prd_rec IN 1..l_period_t.COUNT LOOP
1985 FETCH periods_c BULK COLLECT INTO l_period_t;
1986 CLOSE periods_c;
1987
1989
1990 BEGIN
1991
1992 IF prd_rec = 1 THEN
1993 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
1994 ,pn_var_trx_pkg.G_PRORUL_CYNP)
1995 THEN
1996 RAISE DO_NOT_PROCESS;
1997
1998 END IF;
1999
2000 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
2001 ,pn_var_trx_pkg.G_PRORUL_FLY)
2002 THEN
2003 FOR fst_rec IN check_fst_partial_prd(l_period_t(prd_rec)) LOOP
2004 RAISE DO_NOT_PROCESS;
2005 END LOOP;
2006 END IF;
2007
2008 ELSIF prd_rec = 2 THEN
2009
2010 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
2011 ,pn_var_trx_pkg.G_PRORUL_CYNP)
2012 THEN
2013 RAISE DO_NOT_PROCESS;
2014
2015 END IF;
2016
2017 ELSIF prd_rec = l_period_t.COUNT THEN
2018
2019 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_LY
2020 ,pn_var_trx_pkg.G_PRORUL_FLY)
2021 THEN
2022 FOR fst_rec IN last_period_c(l_period_t(prd_rec)) LOOP
2023 RAISE DO_NOT_PROCESS;
2024 END LOOP;
2025
2026 END IF;
2027 END IF;
2028
2029 FOR rec IN blended_prd_vol_c( p_vr_id => p_var_rent_id
2030 ,p_prd_id => l_period_t(prd_rec)) LOOP
2031
2032 UPDATE
2033 pn_var_trx_details_all
2034 SET
2035 blended_period_vol_start = rec.blended_period_vol_start
2036 ,blended_period_vol_end = rec.blended_period_vol_end
2037 WHERE
2038 trx_header_id IN
2039 (SELECT
2040 trx_header_id
2041 FROM
2042 pn_var_trx_headers_all
2043 WHERE
2044 var_rent_id = p_var_rent_id AND
2045 period_id = rec.period_id AND
2046 line_item_id = rec.line_item_id AND
2047 reset_group_id = rec.reset_group_id) AND
2048 bkpt_rate = rec.bkpt_rate
2049 AND bkpt_detail_id = rec.bkpt_detail_id; --bug#8631183
2050
2051 END LOOP;
2052
2053 EXCEPTION
2054
2055 WHEN DO_NOT_PROCESS THEN NULL;
2056 WHEN OTHERS THEN RAISE;
2057
2058 END;
2059
2060 END LOOP;
2061
2062 EXCEPTION
2063 WHEN OTHERS THEN
2064 RAISE;
2065
2066 END populate_blended_period_vol;
2067
2068 --------------------------------------------------------------------------------
2069 -- NAME : delete_transactions
2070 -- DESCRIPTION :
2071 -- PURPOSE :
2072 -- INVOKED FROM :
2073 -- ARGUMENTS :
2074 -- REFERENCE : PN_COMMON.debug()
2075 -- HISTORY :
2076 --
2077 -- dd-mon-yyyy name o Created
2078 --------------------------------------------------------------------------------
2079 PROCEDURE delete_transactions( p_var_rent_id IN NUMBER
2080 ,p_period_id IN NUMBER
2081 ,p_line_item_id IN NUMBER) IS
2082
2083 BEGIN
2084
2085 IF p_line_item_id IS NOT NULL AND
2086 p_period_id IS NOT NULL AND
2087 p_var_rent_id IS NOT NULL
2088 THEN
2089 pnp_debug_pkg.log('Deleting for lines');
2090 DELETE
2091 pn_var_trx_details_all
2092 WHERE
2093 trx_header_id IN
2094 ( SELECT
2095 trx_header_id
2096 FROM
2097 pn_var_trx_headers_all
2098 WHERE
2099 var_rent_id = p_var_rent_id AND
2100 period_id = p_period_id AND
2101 line_item_id = p_line_item_id );
2102
2103 DELETE
2104 pn_var_trx_headers_all
2105 WHERE
2106 var_rent_id = p_var_rent_id AND
2107 period_id = p_period_id AND
2108 line_item_id = p_line_item_id;
2109
2110 ELSIF p_line_item_id IS NULL AND
2111 p_period_id IS NOT NULL AND
2112 p_var_rent_id IS NOT NULL
2113 THEN
2114 pnp_debug_pkg.log('Deleting for periods');
2115 DELETE
2116 pn_var_trx_details_all
2117 WHERE
2118 trx_header_id IN
2119 ( SELECT
2120 trx_header_id
2121 FROM
2122 pn_var_trx_headers_all
2123 WHERE
2124 var_rent_id = p_var_rent_id AND
2125 period_id = p_period_id);
2126
2127 DELETE
2128 pn_var_trx_headers_all
2129 WHERE
2130 var_rent_id = p_var_rent_id AND
2131 period_id = p_period_id;
2132
2133 ELSIF p_line_item_id IS NULL AND
2134 p_period_id IS NULL AND
2135 p_var_rent_id IS NOT NULL
2136 THEN
2137 pnp_debug_pkg.log('Deleting for VR');
2138 DELETE
2139 pn_var_trx_details_all
2140 WHERE
2141 trx_header_id IN
2142 ( SELECT
2143 trx_header_id
2144 FROM
2145 pn_var_trx_headers_all
2146 WHERE
2147 var_rent_id = p_var_rent_id);
2148
2149 DELETE
2150 pn_var_trx_headers_all
2151 WHERE
2152 var_rent_id = p_var_rent_id;
2153
2154 END IF;
2155
2156 EXCEPTION
2157 WHEN OTHERS THEN RAISE;
2158
2159 END delete_transactions;
2160
2161
2162 --------------------------------------------------------------------------------
2163 -- NAME : populate_transactions
2164 -- DESCRIPTION : inserts/updates the transactions table.
2165 -- PURPOSE :
2166 -- INVOKED FROM :
2167 -- ARGUMENTS :
2171 -- dd-mon-yyyy name o Created
2168 -- REFERENCE : PN_COMMON.debug()
2169 -- HISTORY :
2170 --
2172 -- 23-MAY-2007 Lokesh o Added rounding off for Bug # 6031202
2173 --------------------------------------------------------------------------------
2174 PROCEDURE populate_transactions(p_var_rent_id IN NUMBER) IS
2175
2176 /* get VR info */
2177 CURSOR vr_c(p_vr_id IN NUMBER) IS
2178 SELECT
2179 vr.org_id
2180 ,vr.var_rent_id
2181 ,vr.commencement_date
2182 ,vr.termination_date
2183 ,vr.proration_rule
2184 ,vr.cumulative_vol
2185 FROM
2186 pn_var_rents_all vr
2187 WHERE
2188 vr.var_rent_id = p_vr_id;
2189
2190 /* variables for vr_c */
2191 l_org_id pn_var_rents_all.org_id%TYPE;
2192 l_vr_commencement_date pn_var_rents_all.commencement_date%TYPE;
2193 l_vr_termination_date pn_var_rents_all.termination_date%TYPE;
2194 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
2195 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
2196
2197 /* get the periods that do not exist anymore */
2198 CURSOR chk_for_del_prd_c(p_vr_id IN NUMBER) IS
2199 SELECT
2200 period_id
2201 FROM
2202 pn_var_trx_headers_all
2203 WHERE
2204 var_rent_id = p_vr_id
2205 MINUS
2206 SELECT
2207 period_id
2208 FROM
2209 pn_var_periods_all
2210 WHERE
2211 var_rent_id = p_vr_id AND
2212 status IS NULL;
2213
2214 /* get all periods for the VR */
2215 CURSOR periods_c(p_vr_id IN NUMBER) IS
2216 SELECT
2217 prd.var_rent_id
2218 ,prd.period_id
2219 ,prd.start_date
2220 ,prd.end_date
2221 FROM
2222 pn_var_periods_all prd
2223 WHERE
2224 prd.var_rent_id = p_vr_id AND
2225 prd.status IS NULL
2226 ORDER BY
2227 prd.start_date;
2228
2229 /* get the line items that do not exist anymore in a period */
2230 CURSOR chk_for_del_line_c( p_vr_id IN NUMBER
2231 ,p_prd_id IN NUMBER) IS
2232 SELECT
2233 line_item_id
2234 FROM
2235 pn_var_trx_headers_all
2236 WHERE
2237 var_rent_id = p_vr_id AND
2238 period_id = p_prd_id
2239 MINUS
2240 SELECT
2241 line_item_id
2242 FROM
2243 pn_var_lines_all
2244 WHERE
2245 var_rent_id = p_vr_id AND
2246 period_id = p_prd_id;
2247
2248 -- Get the details of forecasted data
2249 CURSOR for_data_c(ip_vr_id IN NUMBER,
2250 ip_prd_id IN NUMBER
2251 ) IS
2252 SELECT *
2253 FROM pn_var_trx_headers_all
2254 WHERE var_rent_id = ip_vr_id
2255 AND period_id = ip_prd_id;
2256
2257 TYPE for_data IS TABLE OF pn_var_trx_headers_all%ROWTYPE INDEX BY BINARY_INTEGER;
2258 for_data_t for_data;
2259
2260
2261 /* get all groups for a period */
2262 /*
2263 CURSOR groups_c(p_prd_id IN NUMBER) IS
2264 SELECT
2265 grp.grp_date_id
2266 ,grp.grp_start_date
2267 ,grp.grp_end_date
2268 ,grp.group_date
2269 ,grp.invoice_date
2270 ,grp.proration_factor
2271 FROM
2272 pn_var_grp_dates_all grp
2273 WHERE
2274 grp.period_id = p_prd_id
2275 ORDER BY
2276 grp.grp_start_date;
2277 */
2278
2279 CURSOR groups_c(p_prd_id IN NUMBER) IS
2280 SELECT
2281 grp.grp_date_id
2282 ,grp.grp_start_date
2283 ,grp.grp_end_date
2284 ,grp.group_date
2285 ,grp.invoice_date
2286 ,grp.proration_factor
2287 FROM
2288 pn_var_grp_dates_all grp
2289 ,pn_var_periods_all prd
2290 WHERE
2291 prd.period_id = p_prd_id AND
2292 grp.period_id = prd.period_id AND
2293 grp.grp_end_date <= prd.end_date
2294 ORDER BY
2295 grp.grp_start_date;
2296
2297 /* data structures for groups_c */
2298 TYPE GROUPS_CUR_T IS TABLE OF groups_c%ROWTYPE INDEX BY BINARY_INTEGER;
2299 groups_cur_tbl GROUPS_CUR_T;
2300
2301 /* get all line items for a period */
2302 /*CURSOR line_items_c(p_prd_id IN NUMBER) IS
2303 SELECT
2304 line.line_item_id
2305 ,line.line_default_id
2306 FROM
2307 pn_var_lines_all line
2308 WHERE
2309 line.period_id = p_prd_id AND
2310 line.bkpt_update_flag = 'Y' AND
2311 EXISTS (SELECT null
2312 FROM pn_var_bkpts_det_all
2313 WHERE bkpt_header_id IN ( SELECT bkpt_header_id
2314 FROM pn_var_bkpts_head_all
2315 WHERE line_item_id = line.line_item_id))
2316 ORDER BY
2317 line_item_id;*/
2318
2319 CURSOR line_items_c(p_prd_id IN NUMBER) IS
2320 SELECT
2321 line.line_item_id
2322 ,line.line_default_id
2323 FROM
2324 pn_var_lines_all line,
2325 pn_var_bkpts_head_all bph
2326 WHERE
2327 line.period_id = p_prd_id AND
2328 line.bkpt_update_flag = 'Y' AND
2329 bph.period_id = line.period_id AND
2330 EXISTS (SELECT null
2331 FROM pn_var_bkpts_det_all bpd
2332 WHERE bpd.bkpt_header_id = bph.bkpt_header_id
2333 AND rownum = 1)
2334 order BY line_item_id;
2335
2336 /* get all breakpoints for a line item */
2337 CURSOR breakpoints_c(p_line_item_id IN NUMBER) IS
2338 SELECT
2339 bkpt.bkpt_detail_id
2340 ,bkpt.bkpt_start_date
2341 ,bkpt.bkpt_end_date
2342 ,bkpt.group_bkpt_vol_start
2343 ,bkpt.group_bkpt_vol_end
2344 ,bkpt.period_bkpt_vol_start
2345 ,bkpt.period_bkpt_vol_end
2346 ,bkpt.bkpt_rate
2350 WHERE
2347 FROM
2348 pn_var_bkpts_head_all head
2349 ,pn_var_bkpts_det_all bkpt
2351 head.line_item_id = p_line_item_id AND
2352 bkpt.bkpt_header_id = head.bkpt_header_id
2353 ORDER BY
2354 bkpt.bkpt_start_date
2355 ,bkpt.group_bkpt_vol_start;
2356
2357 CURSOR trueup_cur (p_var_rent_id IN NUMBER) IS
2358 SELECT /*+ LEADING(hdr) */
2359 hdr.line_item_id
2360 ,hdr.calc_prd_start_date
2361 ,hdr.calc_prd_end_date
2362 ,hdr.period_id
2363 ,dtls.bkpt_rate
2364 ,hdr.reset_group_id
2365 ,hdr.trueup_rent_due
2366 FROM
2367 pn_var_trx_headers_all hdr
2368 ,pn_var_trx_details_all dtls
2369 WHERE
2370 hdr.trx_header_id = dtls.trx_header_id AND
2371 hdr.var_rent_id = p_var_rent_id
2372 ORDER BY
2373 hdr.line_item_id
2374 ,hdr.calc_prd_start_date
2375 ,dtls.bkpt_rate;
2376
2377
2378 /* data structures for breakpoints_c */
2379 TYPE BKPT_DTLS_T IS TABLE OF breakpoints_c%ROWTYPE INDEX BY BINARY_INTEGER;
2380
2381 TYPE BKPTS_R IS RECORD
2382 ( bkpt_start_date DATE
2383 ,bkpt_end_date DATE
2384 ,bkpt_dtls_tbl BKPT_DTLS_T);
2385
2386 TYPE BKPTS_T IS TABLE OF BKPTS_R INDEX BY BINARY_INTEGER;
2387 bkpts_tbl BKPTS_T;
2388
2389 TYPE TRUEUP_DTLS_T IS TABLE OF trueup_cur%ROWTYPE INDEX BY BINARY_INTEGER;
2390 trueup_table TRUEUP_DTLS_T;
2391
2392 TYPE PERIOD_DTLS_T IS TABLE OF periods_c%ROWTYPE INDEX BY BINARY_INTEGER;
2393 periods_table PERIOD_DTLS_T;
2394
2395 /* counters */
2396 l_counter1 NUMBER;
2397 l_curr_bkpt_ctr NUMBER;
2398
2399 l_bkpt_counter NUMBER;
2400 l_bkpt_dtl_counter NUMBER;
2401
2402 l_prd_counter NUMBER;
2403 l_line_item_counter NUMBER;
2404 l_group_counter NUMBER;
2405
2406 /* other variables */
2407 l_trx_hdr_id NUMBER;
2408 l_trx_dtl_id NUMBER;
2409
2410 l_calc_prd_start_dt DATE;
2411 l_calc_prd_end_dt DATE;
2412
2413 l_proration_factor NUMBER;
2414
2415 l_prorated_grp_vol_start NUMBER;
2416 l_prorated_grp_vol_end NUMBER;
2417
2418 /* flags */
2419 l_trx_create_upd_flag BOOLEAN;
2420
2421 l_line_items_lock4bkpt_t NUM_T;
2422 l_max_end_date DATE;
2423
2424 BEGIN
2425
2426 pnp_debug_pkg.log
2427 ('+++++++++++++ pn_var_trx_pkg.populate_transactions START +++++++++++++');
2428
2429 /* lock the line items */
2430 l_line_items_lock4bkpt_t.DELETE;
2431
2432 OPEN line_items_lock4bkpt_c(p_vr_id => p_var_rent_id);
2433 FETCH line_items_lock4bkpt_c BULK COLLECT INTO l_line_items_lock4bkpt_t;
2434 CLOSE line_items_lock4bkpt_c;
2435 /* get the VR details */
2436 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
2437
2438 l_org_id := vr_rec.org_id;
2439 l_vr_commencement_date := vr_rec.commencement_date;
2440 l_vr_termination_date := vr_rec.termination_date;
2441 l_proration_rule := vr_rec.proration_rule;
2442 l_calculation_method := vr_rec.cumulative_vol;
2443 END LOOP;
2444
2445 g_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(l_org_id),4); /*Bug # 6031202*/
2446
2447 for_data_t.DELETE;
2448 FOR period_rec IN periods_c(p_vr_id => p_var_rent_id) LOOP
2449
2450 FOR rec IN for_data_c(p_var_rent_id, period_rec.period_id) LOOP
2451 for_data_t (for_data_t.COUNT+1):= rec;
2452 END LOOP;
2453 END LOOP;
2454
2455 trueup_table.DELETE;
2456 FOR rec_trueup IN trueup_cur(p_var_rent_id) LOOP
2457 trueup_table (trueup_table.COUNT+1):= rec_trueup;
2458 END LOOP;
2459
2460 periods_table.DELETE;
2461 FOR rec_periods IN periods_c(p_var_rent_id) LOOP
2462 periods_table (periods_table.COUNT+1):= rec_periods;
2463 END LOOP;
2464
2465 /* assume we will not create/update trx */
2466 l_trx_create_upd_flag := FALSE;
2467 /* delete trx records for periods that do not exist anymore */
2468 FOR del_rec IN chk_for_del_prd_c(p_vr_id => p_var_rent_id) LOOP
2469
2470 l_trx_create_upd_flag := TRUE;
2471
2472 pn_var_trx_pkg.delete_transactions
2473 ( p_var_rent_id => p_var_rent_id
2474 ,p_period_id => del_rec.period_id
2475 ,p_line_item_id => NULL);
2476 END LOOP;
2477
2478 /* delete trx records for a contracted period */
2479 BEGIN
2480
2481 DELETE FROM pn_var_trx_headers_all
2482 WHERE
2483 var_rent_id = p_var_rent_id AND
2484 calc_prd_end_date > l_vr_termination_date;
2485
2486 IF SQL%ROWCOUNT > 0 THEN
2487 l_trx_create_upd_flag := TRUE;
2488 END IF;
2489 EXCEPTION
2490
2491 WHEN OTHERS THEN RAISE;
2492
2493 END;
2494
2495
2496 /* init the period counter */
2497 l_prd_counter := 1;
2498 /* loop for all periods,
2499 create TRX headres and details */
2500 FOR period_rec IN periods_c(p_vr_id => p_var_rent_id) LOOP
2501 /* delete trx records for lines that do not exist anymore in this period */
2502 FOR del_rec IN chk_for_del_line_c( p_vr_id => p_var_rent_id
2503 ,p_prd_id => period_rec.period_id) LOOP
2504
2505 l_trx_create_upd_flag := TRUE;
2506
2507 pn_var_trx_pkg.delete_transactions
2508 ( p_var_rent_id => p_var_rent_id
2509 ,p_period_id => period_rec.period_id
2510 ,p_line_item_id => del_rec.line_item_id);
2511 END LOOP;
2512
2513 /* get all the groups for the period, cache */
2514 groups_cur_tbl.DELETE;
2515
2516 OPEN groups_c(p_prd_id => period_rec.period_id);
2517 FETCH groups_c BULK COLLECT INTO groups_cur_tbl;
2518 CLOSE groups_c;
2522 FOR line_item_rec IN line_items_c(p_prd_id => period_rec.period_id) LOOP
2519 l_line_item_counter := 1;
2520
2521 /* create trx for all line items that have bkpts updated */
2523
2524 l_trx_create_upd_flag := TRUE;
2525 pn_var_trx_pkg.delete_transactions
2526 ( p_var_rent_id => p_var_rent_id
2527 ,p_period_id => period_rec.period_id
2528 ,p_line_item_id => line_item_rec.line_item_id);
2529
2530 l_counter1 := 1;
2531 l_bkpt_counter := 1;
2532 l_bkpt_dtl_counter := 1;
2533 bkpts_tbl.DELETE;
2534
2535 /* get bkpts */
2536 FOR bkpt_rec IN breakpoints_c(p_line_item_id => line_item_rec.line_item_id)
2537 LOOP
2538
2539 IF l_counter1 = 1 THEN
2540
2541 bkpts_tbl(l_bkpt_counter).bkpt_start_date := bkpt_rec.bkpt_start_date;
2542 bkpts_tbl(l_bkpt_counter).bkpt_end_date := bkpt_rec.bkpt_end_date;
2543 bkpts_tbl(l_bkpt_counter).bkpt_dtls_tbl(l_bkpt_dtl_counter) := bkpt_rec;
2544
2545 ELSE /* l_counter1 > 1 */
2546
2547 /* if we have stratified bkpt ranges */
2548 IF bkpt_rec.bkpt_start_date = bkpts_tbl(l_bkpt_counter).bkpt_start_date AND
2549 bkpt_rec.bkpt_end_date = bkpts_tbl(l_bkpt_counter).bkpt_end_date
2550 THEN
2551
2552 /* add a bkpt detail */
2553 l_bkpt_dtl_counter := l_bkpt_dtl_counter + 1;
2554 bkpts_tbl(l_bkpt_counter).bkpt_dtls_tbl(l_bkpt_dtl_counter) := bkpt_rec;
2555
2556 /* else if bkpt date range changed */
2557 ELSE
2558
2559 /* reset the bkpt detail counter */
2560 l_bkpt_dtl_counter := 1;
2561 /* add a new bkpt header and a detail */
2562 l_bkpt_counter := l_bkpt_counter + 1;
2563 bkpts_tbl(l_bkpt_counter).bkpt_start_date := bkpt_rec.bkpt_start_date;
2564 bkpts_tbl(l_bkpt_counter).bkpt_end_date := bkpt_rec.bkpt_end_date;
2565 bkpts_tbl(l_bkpt_counter).bkpt_dtls_tbl(l_bkpt_dtl_counter) := bkpt_rec;
2566
2567 END IF;
2568
2569 END IF;
2570
2571 l_counter1 := l_counter1 + 1;
2572
2573 END LOOP; /* loop for all bkpts for a line item */
2574 /* we have the bkpt details */
2575
2576 l_group_counter := 1;
2577
2578 FOR grp_rec IN 1..groups_cur_tbl.COUNT LOOP
2579
2580 l_calc_prd_start_dt := groups_cur_tbl(grp_rec).grp_start_date;
2581 l_calc_prd_end_dt := groups_cur_tbl(grp_rec).grp_end_date;
2582
2583 l_curr_bkpt_ctr := 1;
2584
2585 FOR bkpt_rec IN 1..bkpts_tbl.COUNT LOOP
2586
2587 l_curr_bkpt_ctr := bkpt_rec;
2588
2589 IF bkpts_tbl(bkpt_rec).bkpt_start_date > l_calc_prd_end_dt THEN
2590 /* exit the loop no more intersections possible
2591 - let us go to the next group */
2592 l_curr_bkpt_ctr := bkpt_rec - 1;
2593 EXIT;
2594
2595 ELSIF bkpts_tbl(bkpt_rec).bkpt_start_date
2596 BETWEEN (l_calc_prd_start_dt + 1) AND l_calc_prd_end_dt
2597 THEN
2598
2599 l_calc_prd_end_dt := bkpts_tbl(bkpt_rec - 1).bkpt_end_date;
2600
2601 /* determine proration */
2602 l_proration_factor
2603 := ((l_calc_prd_end_dt - l_calc_prd_start_dt) + 1)
2604 /((groups_cur_tbl(grp_rec).grp_end_date
2605 - groups_cur_tbl(grp_rec).grp_start_date) + 1);
2606
2607 /* need to create TRX headers and details */
2608
2609 /* create header */
2610 pn_var_trx_pkg.insert_trx_hdr
2611 (p_trx_header_id => l_trx_hdr_id
2612 ,p_var_rent_id => period_rec.var_rent_id
2613 ,p_period_id => period_rec.period_id
2614 ,p_line_item_id => line_item_rec.line_item_id
2615 ,p_grp_date_id => groups_cur_tbl(grp_rec).grp_date_id
2616 ,p_calc_prd_start_date => l_calc_prd_start_dt
2617 ,p_calc_prd_end_date => l_calc_prd_end_dt
2618 ,p_var_rent_summ_id => NULL
2619 ,p_line_item_group_id => line_item_rec.line_default_id
2620 ,p_reset_group_id => NULL
2621 ,p_proration_factor => l_proration_factor
2622 ,p_reporting_group_sales => NULL
2623 ,p_prorated_group_sales => NULL
2624 ,p_ytd_sales => NULL
2625 ,p_fy_proration_sales => NULL
2626 ,p_ly_proration_sales => NULL
2627 ,p_percent_rent_due => NULL
2628 ,p_ytd_percent_rent => NULL
2629 ,p_calculated_rent => NULL
2630 ,p_prorated_rent_due => NULL
2631 ,p_invoice_flag => NULL
2632 ,p_org_id => l_org_id
2633 ,p_last_update_date => NULL
2634 ,p_last_updated_by => NULL
2635 ,p_creation_date => NULL
2636 ,p_created_by => NULL
2637 ,p_last_update_login => NULL);
2638
2639 /* create details */
2640 FOR bkpt_dtl_rec IN 1..bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl.COUNT
2641 LOOP
2642
2643 IF l_proration_rule = pn_var_trx_pkg.G_PRORUL_NP THEN
2644 IF l_calculation_method = pn_var_trx_pkg.G_CALC_CUMULATIVE THEN
2645 l_prorated_grp_vol_start
2646 := bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).period_bkpt_vol_start;
2647
2648 l_prorated_grp_vol_end
2649 := NVL(bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).period_bkpt_vol_end, 0);
2650
2651 ELSE
2652 l_prorated_grp_vol_start
2656 := NVL(bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_end, 0);
2653 := bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_start;
2654
2655 l_prorated_grp_vol_end
2657
2658 END IF;
2659
2660 ELSE
2661
2662 l_prorated_grp_vol_start
2663 := bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_start
2664 * groups_cur_tbl(grp_rec).proration_factor
2665 * l_proration_factor;
2666
2667 l_prorated_grp_vol_end
2668 := NVL(bkpts_tbl(bkpt_rec - 1).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_end, 0)
2669 * groups_cur_tbl(grp_rec).proration_factor
2670 * l_proration_factor;
2671
2672 END IF;
2673
2674 pn_var_trx_pkg.insert_trx_dtl
2675 (p_trx_detail_id => l_trx_dtl_id
2676 ,p_trx_header_id => l_trx_hdr_id
2677 ,p_bkpt_detail_id => bkpts_tbl(bkpt_rec - 1).
2678 bkpt_dtls_tbl(bkpt_dtl_rec).
2679 bkpt_detail_id
2680 ,p_bkpt_rate => bkpts_tbl(bkpt_rec - 1).
2681 bkpt_dtls_tbl(bkpt_dtl_rec).
2682 bkpt_rate
2683 ,p_prorated_grp_vol_start => l_prorated_grp_vol_start
2684 ,p_prorated_grp_vol_end => l_prorated_grp_vol_end
2685 ,p_fy_pr_grp_vol_start => NULL
2686 ,p_fy_pr_grp_vol_end => NULL
2687 ,p_ly_pr_grp_vol_start => NULL
2688 ,p_ly_pr_grp_vol_end => NULL
2689 ,p_pr_grp_blended_vol_start => NULL
2690 ,p_pr_grp_blended_vol_end => NULL
2691 ,p_ytd_group_vol_start => NULL
2692 ,p_ytd_group_vol_end => NULL
2693 ,p_blended_period_vol_start => NULL
2694 ,p_blended_period_vol_end => NULL
2695 ,p_org_id => l_org_id
2696 ,p_last_update_date => NULL
2697 ,p_last_updated_by => NULL
2698 ,p_creation_date => NULL
2699 ,p_created_by => NULL
2700 ,p_last_update_login => NULL);
2701
2702 END LOOP; /* FOR bkpt_dtl_rec IN 0..bkpts_tbl(bkpt_rec).bkpt_dtls_tbl.COUNT */
2703
2704 l_calc_prd_start_dt := bkpts_tbl(bkpt_rec).bkpt_start_date;
2705 l_calc_prd_end_dt := groups_cur_tbl(grp_rec).grp_end_date;
2706
2707 END IF;
2708
2709 END LOOP; /* loop for breakpoints */
2710
2711 /* determine proration */
2712 l_proration_factor
2713 := ((l_calc_prd_end_dt - l_calc_prd_start_dt) + 1)
2714 /((groups_cur_tbl(grp_rec).grp_end_date
2715 - groups_cur_tbl(grp_rec).grp_start_date) + 1);
2716
2717 /* need to create TRX headers and details */
2718
2719 /* create header */
2720 pn_var_trx_pkg.insert_trx_hdr
2721 (p_trx_header_id => l_trx_hdr_id
2722 ,p_var_rent_id => period_rec.var_rent_id
2723 ,p_period_id => period_rec.period_id
2724 ,p_line_item_id => line_item_rec.line_item_id
2725 ,p_grp_date_id => groups_cur_tbl(grp_rec).grp_date_id
2726 ,p_calc_prd_start_date => l_calc_prd_start_dt
2727 ,p_calc_prd_end_date => l_calc_prd_end_dt
2728 ,p_var_rent_summ_id => NULL
2729 ,p_line_item_group_id => line_item_rec.line_default_id
2730 ,p_reset_group_id => NULL
2731 ,p_proration_factor => l_proration_factor
2732 ,p_reporting_group_sales => NULL
2733 ,p_prorated_group_sales => NULL
2734 ,p_ytd_sales => NULL
2735 ,p_fy_proration_sales => NULL
2736 ,p_ly_proration_sales => NULL
2737 ,p_percent_rent_due => NULL
2738 ,p_ytd_percent_rent => NULL
2739 ,p_calculated_rent => NULL
2740 ,p_prorated_rent_due => NULL
2741 ,p_invoice_flag => NULL
2742 ,p_org_id => l_org_id
2743 ,p_last_update_date => NULL
2744 ,p_last_updated_by => NULL
2745 ,p_creation_date => NULL
2746 ,p_created_by => NULL
2747 ,p_last_update_login => NULL);
2748
2749 /* create details */
2750 FOR bkpt_dtl_rec IN 1..bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl.COUNT LOOP
2751
2752 IF l_proration_rule = pn_var_trx_pkg.G_PRORUL_NP THEN
2753 IF l_calculation_method = pn_var_trx_pkg.G_CALC_CUMULATIVE THEN
2754 l_prorated_grp_vol_start
2755 := bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).period_bkpt_vol_start;
2756 l_prorated_grp_vol_end
2757 := NVL(bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).period_bkpt_vol_end, 0);
2758
2759 ELSE
2760 l_prorated_grp_vol_start
2761 := bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_start;
2762 l_prorated_grp_vol_end
2763 := NVL(bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_end, 0);
2764 END IF;
2765
2766
2767 ELSE
2768
2769 l_prorated_grp_vol_start
2770 := bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_start
2771 * groups_cur_tbl(grp_rec).proration_factor
2772 * l_proration_factor;
2773
2774 l_prorated_grp_vol_end
2778
2775 := NVL(bkpts_tbl(l_curr_bkpt_ctr).bkpt_dtls_tbl(bkpt_dtl_rec).group_bkpt_vol_end, 0)
2776 * groups_cur_tbl(grp_rec).proration_factor
2777 * l_proration_factor;
2779 END IF;
2780
2781 pn_var_trx_pkg.insert_trx_dtl
2782 (p_trx_detail_id => l_trx_dtl_id
2783 ,p_trx_header_id => l_trx_hdr_id
2784 ,p_bkpt_detail_id => bkpts_tbl(l_curr_bkpt_ctr).
2785 bkpt_dtls_tbl(bkpt_dtl_rec).
2786 bkpt_detail_id
2787 ,p_bkpt_rate => bkpts_tbl(l_curr_bkpt_ctr).
2788 bkpt_dtls_tbl(bkpt_dtl_rec).
2789 bkpt_rate
2790 ,p_prorated_grp_vol_start => l_prorated_grp_vol_start
2791 ,p_prorated_grp_vol_end => l_prorated_grp_vol_end
2792 ,p_fy_pr_grp_vol_start => NULL
2793 ,p_fy_pr_grp_vol_end => NULL
2794 ,p_ly_pr_grp_vol_start => NULL
2795 ,p_ly_pr_grp_vol_end => NULL
2796 ,p_pr_grp_blended_vol_start => NULL
2797 ,p_pr_grp_blended_vol_end => NULL
2798 ,p_ytd_group_vol_start => NULL
2799 ,p_ytd_group_vol_end => NULL
2800 ,p_blended_period_vol_start => NULL
2801 ,p_blended_period_vol_end => NULL
2802 ,p_org_id => l_org_id
2803 ,p_last_update_date => NULL
2804 ,p_last_updated_by => NULL
2805 ,p_creation_date => NULL
2806 ,p_created_by => NULL
2807 ,p_last_update_login => NULL);
2808
2809 END LOOP; /* FOR bkpt_dtl_rec IN 0..bkpts_tbl(bkpt_rec).bkpt_dtls_tbl.COUNT */
2810
2811 l_group_counter := l_group_counter + 1;
2812
2813 END LOOP; /* loop for all groups */
2814
2815 l_line_item_counter := l_line_item_counter + 1;
2816
2817 END LOOP; /* loop for all line items in a period */
2818
2819 l_prd_counter := l_prd_counter + 1;
2820
2821
2822 END LOOP; /* loop for all periods */
2823
2824 /* get the grp IDs right if any trx was updated */
2825 IF l_trx_create_upd_flag THEN
2826 pnp_debug_pkg.log('Trx updated');
2827 /* groups the lines across the periods */
2828 pn_var_trx_pkg.populate_line_grp_id(p_var_rent_id => p_var_rent_id);
2829
2830 /* populate the reset group IDs */
2831 pn_var_trx_pkg.populate_reset_grp_id(p_var_rent_id => p_var_rent_id);
2832 /* populate fy_pr_grp_vol_start - end,
2833 ly_pr_grp_vol_start - end,
2834 invoice_flag
2835 for FY, LY, FLY */
2836 FOR i in 1..for_data_t.COUNT LOOP
2837 pnp_debug_pkg.log('line_item_id:'||for_data_t(i).line_item_id);
2838 pnp_debug_pkg.log('grp_date_id:'||for_data_t(i).grp_date_id);
2839 pnp_debug_pkg.log('reset_group_id:'||for_data_t(i).reset_group_id);
2840 pnp_debug_pkg.log('var_rent_id:'||for_data_t(i).var_rent_id);
2841 pnp_debug_pkg.log('REPORTING_GROUP_SALES_FOR:'||for_data_t(i).REPORTING_GROUP_SALES_FOR);
2842 pnp_debug_pkg.log('CALCULATED_RENT_FOR:'||for_data_t(i).CALCULATED_RENT_FOR);
2843
2844 UPDATE
2845 pn_var_trx_headers_all
2846 SET
2847 REPORTING_GROUP_SALES_FOR = for_data_t(i).REPORTING_GROUP_SALES_FOR
2848 ,PRORATED_GROUP_SALES_FOR = for_data_t(i).PRORATED_GROUP_SALES_FOR
2849 ,YTD_SALES_FOR = for_data_t(i).YTD_SALES_FOR
2850 ,CALCULATED_RENT_FOR = round(for_data_t(i).CALCULATED_RENT_FOR,g_precision) /*Bug # 6031202*/
2851 ,PERCENT_RENT_DUE_FOR = round(for_data_t(i).PERCENT_RENT_DUE_FOR,g_precision)
2852 ,YTD_PERCENT_RENT_FOR = round(for_data_t(i).YTD_PERCENT_RENT_FOR,g_precision)
2853 WHERE var_rent_id = for_data_t(i).var_rent_id AND
2854 grp_date_id = for_data_t(i).grp_date_id AND
2855 line_item_id = for_data_t(i).line_item_id AND
2856 reset_group_id = for_data_t(i).reset_group_id;
2857 END LOOP;
2858
2859 IF l_proration_rule = pn_var_trx_pkg.G_PRORUL_LY THEN
2860
2861 /* populate ly_pr_grp_vol_start - end, invoice_flag */
2862 pn_var_trx_pkg.populate_ly_pro_vol
2863 ( p_var_rent_id => p_var_rent_id
2864 ,p_proration_rule => l_proration_rule
2865 ,p_vr_commencement_dt => l_vr_commencement_date
2866 ,p_vr_termination_dt => l_vr_termination_date);
2867
2868 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FY THEN
2869
2870 /* populate fy_pr_grp_vol_start - end, invoice_flag */
2871 pn_var_trx_pkg.populate_fy_pro_vol
2872 ( p_var_rent_id => p_var_rent_id
2873 ,p_proration_rule => l_proration_rule
2874 ,p_vr_commencement_dt => l_vr_commencement_date
2875 ,p_vr_termination_dt => l_vr_termination_date);
2876
2877 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FLY THEN
2878
2879 /* populate ly_pr_grp_vol_start - end, invoice_flag */
2880 pn_var_trx_pkg.populate_ly_pro_vol
2881 ( p_var_rent_id => p_var_rent_id
2882 ,p_proration_rule => l_proration_rule
2883 ,p_vr_commencement_dt => l_vr_commencement_date
2884 ,p_vr_termination_dt => l_vr_termination_date);
2885
2886 /* populate fy_pr_grp_vol_start - end, invoice_flag */
2887 pn_var_trx_pkg.populate_fy_pro_vol
2888 ( p_var_rent_id => p_var_rent_id
2889 ,p_proration_rule => l_proration_rule
2890 ,p_vr_commencement_dt => l_vr_commencement_date
2891 ,p_vr_termination_dt => l_vr_termination_date);
2892
2893 ELSIF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
2894 ,pn_var_trx_pkg.G_PRORUL_CYNP) THEN
2895
2899 ,p_proration_rule => l_proration_rule);
2896 /* populate blended_period/grp_vol_start - end */
2897 pn_var_trx_pkg.populate_blended_grp_vol
2898 ( p_var_rent_id => p_var_rent_id
2900
2901 END IF;
2902
2903 pnp_debug_pkg.log(' call pn_var_trx_pkg.populate_ytd_pro_vol'); /* 8616530 */
2904 /* ALWAYS populate the ytd_group_vol_start - end - useful */
2905 pn_var_trx_pkg.populate_ytd_pro_vol
2906 ( p_var_rent_id => p_var_rent_id
2907 ,p_proration_rule => l_proration_rule);
2908
2909 IF l_calculation_method = pn_var_trx_pkg.G_CALC_CUMULATIVE THEN
2910 /* populate the blended_period_vol_start - end */
2911 pn_var_trx_pkg.populate_blended_period_vol
2912 ( p_var_rent_id => p_var_rent_id
2913 ,p_proration_rule => l_proration_rule
2914 ,p_calc_method => l_calculation_method);
2915
2916 END IF;
2917
2918 END IF;
2919
2920 FOR j IN 1..periods_table.COUNT LOOP
2921
2922 l_max_end_date := periods_table(j).start_date;
2923
2924 FOR i IN 1..trueup_table.COUNT LOOP
2925 IF l_max_end_date < trueup_table(i).calc_prd_end_date AND
2926 trueup_table(i).period_id = periods_table(j).period_id THEN
2927 l_max_end_date := trueup_table(i).calc_prd_end_date;
2928 END IF;
2929 END LOOP;
2930
2931 FOR i IN 1..trueup_table.COUNT LOOP
2932 IF periods_table(j).end_date = l_max_end_date THEN
2933 /* Added var_rent_id filter to improve the performance */
2934 UPDATE
2935 pn_var_trx_headers_all
2936 SET
2937 trueup_rent_due = round(trueup_table(i).trueup_rent_due,g_precision) /*Bug # 6031202*/
2938 WHERE
2939 var_rent_id = p_var_rent_id AND
2940 line_item_id = trueup_table(i).line_item_id AND
2941 calc_prd_start_date = trueup_table(i).calc_prd_start_date AND
2942 calc_prd_end_date = trueup_table(i).calc_prd_end_date AND
2943 reset_group_id = trueup_table(i).reset_group_id AND
2944 period_id = periods_table(j).period_id;
2945 END IF;
2946 END LOOP;
2947
2948 END LOOP;
2949
2950 /* reset the bkpt_update_flag */
2951 FORALL line_rec IN 1..l_line_items_lock4bkpt_t.COUNT
2952 UPDATE
2953 pn_var_lines_all
2954 SET
2955 bkpt_update_flag = NULL
2956 ,sales_vol_update_flag = 'Y'
2957 WHERE
2958 line_item_id = l_line_items_lock4bkpt_t(line_rec);
2959
2960 /* UN-lock the line items */
2961 COMMIT;
2962
2963 EXCEPTION
2964 WHEN OTHERS THEN RAISE;
2965
2966 END populate_transactions;
2967
2968 /* ----------------------------------------------------------------------
2969 -------------------- PROCEDURES TO POPULATE SALES --------------------
2970 ---------------------------------------------------------------------- */
2971
2972 --------------------------------------------------------------------------------
2973 -- NAME : get_calc_prd_sales
2974 -- DESCRIPTION :
2975 -- PURPOSE :
2976 -- INVOKED FROM :
2977 -- ARGUMENTS :
2978 -- REFERENCE : PN_COMMON.debug()
2979 -- HISTORY :
2980 --
2981 -- dd-mon-yyyy name o Created
2982 --------------------------------------------------------------------------------
2983 PROCEDURE get_calc_prd_sales( p_var_rent_id IN NUMBER
2984 ,p_period_id IN NUMBER
2985 ,p_line_item_id IN NUMBER
2986 ,p_grp_date_id IN NUMBER
2987 ,p_start_date IN DATE
2988 ,p_end_date IN DATE
2989 ,x_pro_sales OUT NOCOPY NUMBER
2990 ,x_sales OUT NOCOPY NUMBER) IS
2991
2992 /* get volumes for a calculation period */
2993 CURSOR vol_hist_c1 ( p_prd_id IN NUMBER
2994 ,p_line_id IN NUMBER
2995 ,p_grp_id IN NUMBER) IS
2996 SELECT
2997 SUM(actual_amount) AS calc_prd_actual_volume
2998 FROM
2999 pn_var_vol_hist_all sales
3000 WHERE
3001 sales.period_id = p_prd_id AND
3002 sales.line_item_id = p_line_id AND
3003 sales.grp_date_id = p_grp_id AND
3004 vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
3005
3006 /* get volumes for a calculation sub-period */
3007 CURSOR vol_hist_c2 ( p_prd_id IN NUMBER
3008 ,p_line_id IN NUMBER
3009 ,p_grp_id IN NUMBER
3010 ,p_start_dt IN DATE
3011 ,p_end_dt IN DATE) IS
3012 SELECT
3013 sales.actual_amount
3014 ,sales.start_date
3015 ,sales.end_date
3016 FROM
3017 pn_var_vol_hist_all sales
3018 WHERE
3019 sales.period_id = p_prd_id AND
3020 sales.line_item_id = p_line_id AND
3021 sales.grp_date_id = p_grp_id AND
3022 sales.start_date <= p_end_dt AND
3023 sales.end_date >= p_start_dt AND
3024 vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
3025
3026 /* get grp dates */
3027 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
3028 SELECT
3029 grp.grp_start_date
3030 ,grp.grp_end_date
3031 FROM
3032 pn_var_grp_dates_all grp
3033 WHERE
3034 grp.grp_date_id = p_grp_id;
3035
3036 l_grp_start_date DATE;
3037 l_grp_end_date DATE;
3038
3039 l_calc_prd_sales NUMBER;
3040 l_pro_calc_prd_sales NUMBER;
3041
3042 BEGIN
3043
3044 /* get group / calc period dates */
3045 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
3046 l_grp_start_date := grp_rec.grp_start_date;
3047 l_grp_end_date := grp_rec.grp_end_date;
3048 END LOOP;
3049
3053 /* get all APPROVED sales for a group / calc period */
3050 l_calc_prd_sales := 0;
3051 l_pro_calc_prd_sales := 0;
3052
3054 FOR sales_rec IN vol_hist_c1 ( p_prd_id => p_period_id
3055 ,p_line_id => p_line_item_id
3056 ,p_grp_id => p_grp_date_id)
3057 LOOP
3058 l_calc_prd_sales := l_calc_prd_sales + sales_rec.calc_prd_actual_volume;
3059 END LOOP;
3060
3061 /* if calc sub period dates are same as grp / calc period start-end dates */
3062 IF l_grp_start_date = p_start_date AND
3063 l_grp_end_date = p_end_date
3064 THEN
3065
3066 /* then prorated sales = total sales */
3067 l_pro_calc_prd_sales := l_calc_prd_sales;
3068
3069 ELSE
3070
3071 /* else, sum all sales to get the prorated sales */
3072 FOR sales_rec IN vol_hist_c2 ( p_prd_id => p_period_id
3073 ,p_line_id => p_line_item_id
3074 ,p_grp_id => p_grp_date_id
3075 ,p_start_dt => p_start_date
3076 ,p_end_dt => p_end_date)
3077 LOOP
3078
3079 /* if sales volume dates between calc sub period dates */
3080 IF sales_rec.start_date >= p_start_date AND
3081 sales_rec.end_date <= p_end_date
3082 THEN
3083 /* consider full volume */
3084 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.actual_amount;
3085
3086 /* else if sales volume dates overlap calc sub period dates */
3087 ELSIF sales_rec.start_date <= p_end_date AND
3088 sales_rec.end_date >= p_start_date
3089 THEN
3090 /* then consider prorated volume */
3091 l_pro_calc_prd_sales
3092 := l_pro_calc_prd_sales
3093 + sales_rec.actual_amount
3094 * ((LEAST(sales_rec.end_date, p_end_date)
3095 - GREATEST(sales_rec.start_date, p_start_date)) + 1)
3096 / ((sales_rec.end_date - sales_rec.start_date) + 1);
3097
3098 END IF;
3099
3100 END LOOP;
3101
3102 END IF;
3103
3104 x_pro_sales := l_pro_calc_prd_sales;
3105 x_sales := l_calc_prd_sales;
3106
3107 EXCEPTION
3108 WHEN OTHERS THEN RAISE;
3109
3110 END get_calc_prd_sales;
3111
3112 --------------------------------------------------------------------------------
3113 -- NAME : get_calc_prd_sales
3114 -- DESCRIPTION :
3115 -- PURPOSE :
3116 -- INVOKED FROM :
3117 -- ARGUMENTS :
3118 -- REFERENCE : PN_COMMON.debug()
3119 -- HISTORY :
3120 --
3121 -- dd-mon-yyyy name o Created
3122 --------------------------------------------------------------------------------
3123 FUNCTION get_calc_prd_sales( p_var_rent_id IN NUMBER
3124 ,p_period_id IN NUMBER
3125 ,p_line_item_id IN NUMBER
3126 ,p_grp_date_id IN NUMBER
3127 ,p_start_date IN DATE
3128 ,p_end_date IN DATE)
3129 RETURN NUMBER IS
3130
3131 /* get volumes for a calculation period */
3132 CURSOR vol_hist_c1 ( p_prd_id IN NUMBER
3133 ,p_line_id IN NUMBER
3134 ,p_grp_id IN NUMBER) IS
3135 SELECT
3136 SUM(actual_amount) AS calc_prd_actual_volume
3137 FROM
3138 pn_var_vol_hist_all sales
3139 WHERE
3140 sales.period_id = p_prd_id AND
3141 sales.line_item_id = p_line_id AND
3142 sales.grp_date_id = p_grp_id AND
3143 vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
3144
3145 /* get volumes for a calculation sub-period */
3146 CURSOR vol_hist_c2 ( p_prd_id IN NUMBER
3147 ,p_line_id IN NUMBER
3148 ,p_grp_id IN NUMBER
3149 ,p_start_dt IN DATE
3150 ,p_end_dt IN DATE) IS
3151 SELECT
3152 sales.actual_amount
3153 ,sales.start_date
3154 ,sales.end_date
3155 FROM
3156 pn_var_vol_hist_all sales
3157 WHERE
3158 sales.period_id = p_prd_id AND
3159 sales.line_item_id = p_line_id AND
3160 sales.grp_date_id = p_grp_id AND
3161 sales.start_date <= p_end_dt AND
3162 sales.end_date >= p_start_dt AND
3163 vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
3164
3165 /* get grp dates */
3166 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
3167 SELECT
3168 grp.grp_start_date
3169 ,grp.grp_end_date
3170 FROM
3171 pn_var_grp_dates_all grp
3172 WHERE
3173 grp.grp_date_id = p_grp_id;
3174
3175 l_grp_start_date DATE;
3176 l_grp_end_date DATE;
3177
3178 l_calc_prd_sales NUMBER;
3179 l_pro_calc_prd_sales NUMBER;
3180
3181 BEGIN
3182
3183 /* get group / calc period dates */
3184 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
3185 l_grp_start_date := grp_rec.grp_start_date;
3186 l_grp_end_date := grp_rec.grp_end_date;
3187 END LOOP;
3188
3189 l_pro_calc_prd_sales := 0;
3190
3191 /* if calc sub period dates are same as grp / calc period start-end dates */
3192 IF l_grp_start_date = p_start_date AND
3193 l_grp_end_date = p_end_date
3194 THEN
3195
3196 /* get all APPROVED sales for a group / calc period
3197 prorated sales = total sales */
3198 FOR sales_rec IN vol_hist_c1 ( p_prd_id => p_period_id
3199 ,p_line_id => p_line_item_id
3200 ,p_grp_id => p_grp_date_id)
3201 LOOP
3202 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.calc_prd_actual_volume;
3203 END LOOP;
3204
3205 ELSE
3209 ,p_line_id => p_line_item_id
3206
3207 /* else, sum all sales to get the prorated sales */
3208 FOR sales_rec IN vol_hist_c2 ( p_prd_id => p_period_id
3210 ,p_grp_id => p_grp_date_id
3211 ,p_start_dt => p_start_date
3212 ,p_end_dt => p_end_date)
3213 LOOP
3214
3215 /* if sales volume dates between calc sub period dates */
3216 IF sales_rec.start_date >= p_start_date AND
3217 sales_rec.end_date <= p_end_date
3218 THEN
3219 /* consider full volume */
3220 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.actual_amount;
3221
3222 /* else if sales volume dates overlap calc sub period dates */
3223 ELSIF sales_rec.start_date <= p_end_date AND
3224 sales_rec.end_date >= p_start_date
3225 THEN
3226 /* then consider prorated volume */
3227 l_pro_calc_prd_sales
3228 := l_pro_calc_prd_sales
3229 + sales_rec.actual_amount
3230 * ((LEAST(sales_rec.end_date, p_end_date)
3231 - GREATEST(sales_rec.start_date, p_start_date)) + 1)
3232 / ((sales_rec.end_date - sales_rec.start_date) + 1);
3233
3234 END IF;
3235
3236 END LOOP;
3237
3238 END IF;
3239
3240 RETURN l_pro_calc_prd_sales;
3241
3242 EXCEPTION
3243 WHEN OTHERS THEN RAISE;
3244
3245 END get_calc_prd_sales;
3246
3247 --------------------------------------------------------------------------------
3248 -- NAME : populate_ly_pro_sales
3249 -- DESCRIPTION :
3250 -- PURPOSE :
3251 -- INVOKED FROM :
3252 -- ARGUMENTS :
3253 -- REFERENCE : PN_COMMON.debug()
3254 -- HISTORY :
3255 --
3256 -- dd-mon-yyyy name o Created
3257 --------------------------------------------------------------------------------
3258 PROCEDURE populate_ly_pro_sales( p_var_rent_id IN NUMBER
3259 ,p_proration_rule IN VARCHAR2
3260 ,p_vr_commencement_dt IN DATE
3261 ,p_vr_termination_dt IN DATE) IS
3262
3263 /* get VR details */
3264 CURSOR vr_c(p_vr_id IN NUMBER) IS
3265 SELECT
3266 vr.var_rent_id
3267 ,vr.commencement_date
3268 ,vr.termination_date
3269 ,vr.proration_rule
3270 FROM
3271 pn_var_rents_all vr
3272 WHERE
3273 vr.var_rent_id = p_vr_id;
3274
3275 l_vr_commencement_date DATE;
3276 l_vr_termination_date DATE;
3277 l_vr_proration_rule VARCHAR2(30);
3278 l_ly_start_date DATE;
3279
3280 /* get the last partial period */
3281 CURSOR last_period_c( p_vr_id IN NUMBER
3282 ,p_term_date IN DATE) IS
3283 SELECT
3284 prd.period_id
3285 ,prd.partial_period
3286 FROM
3287 pn_var_periods_all prd
3288 WHERE
3289 prd.var_rent_id = p_var_rent_id AND
3290 prd.end_date = p_term_date;
3291
3292 l_last_period_id NUMBER;
3293 l_partial_period VARCHAR2(1);
3294
3295 BEGIN
3296
3297 /* get VR details */
3298 IF p_proration_rule IS NULL OR
3299 p_vr_commencement_dt IS NULL OR
3300 p_vr_termination_dt IS NULL
3301 THEN
3302 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
3303 l_vr_commencement_date := vr_rec.commencement_date;
3304 l_vr_termination_date := vr_rec.termination_date;
3305 l_vr_proration_rule := vr_rec.proration_rule;
3306 END LOOP;
3307 ELSE
3308 l_vr_commencement_date := p_vr_commencement_dt;
3309 l_vr_termination_date := p_vr_termination_dt;
3310 l_vr_proration_rule := p_proration_rule;
3311 END IF;
3312
3313 l_ly_start_date := ADD_MONTHS(l_vr_termination_date, -12) + 1;
3314
3315 IF l_vr_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_LY
3316 ,pn_var_trx_pkg.G_PRORUL_FLY) THEN
3317
3318 FOR prd_rec IN last_period_c( p_vr_id => p_var_rent_id
3319 ,p_term_date => l_vr_termination_date)
3320 LOOP
3321 l_last_period_id := prd_rec.period_id;
3322 l_partial_period := NVL(prd_rec.partial_period, 'N');
3323 END LOOP;
3324
3325 /* init ly_proration_sales */
3326 UPDATE
3327 pn_var_trx_headers_all
3328 SET
3329 ly_proration_sales = NULL
3330 WHERE
3331 var_rent_id = p_var_rent_id;
3332
3333 IF l_partial_period = 'Y' THEN
3334
3335 /* populate ly_proration_sales */
3336 UPDATE
3337 pn_var_trx_headers_all hdr
3338 SET
3339 hdr.ly_proration_sales = hdr.prorated_group_sales
3340 WHERE
3341 hdr.var_rent_id = p_var_rent_id AND
3342 hdr.calc_prd_start_date >= l_ly_start_date;
3343
3344 /* populate ly_proration_sales if LY start date does not
3345 coincide with a calc prd start date */
3346 UPDATE
3347 pn_var_trx_headers_all
3348 SET
3349 ly_proration_sales
3350 = pn_var_trx_pkg.get_calc_prd_sales( var_rent_id
3351 ,period_id
3352 ,line_item_id
3353 ,grp_date_id
3354 ,l_ly_start_date
3355 ,calc_prd_end_date)
3356 WHERE
3357 var_rent_id = p_var_rent_id AND
3358 l_ly_start_date BETWEEN (calc_prd_start_date + 1)
3359 AND calc_prd_end_date;
3360
3361 END IF;
3362
3363 END IF;
3364
3365 EXCEPTION
3366 WHEN OTHERS THEN RAISE;
3370 --------------------------------------------------------------------------------
3367
3368 END populate_ly_pro_sales;
3369
3371 -- NAME : populate_fy_pro_sales
3372 -- DESCRIPTION :
3373 -- PURPOSE :
3374 -- INVOKED FROM :
3375 -- ARGUMENTS :
3376 -- REFERENCE : PN_COMMON.debug()
3377 -- HISTORY :
3378 --
3379 -- dd-mon-yyyy name o Created
3380 --------------------------------------------------------------------------------
3381 PROCEDURE populate_fy_pro_sales( p_var_rent_id IN NUMBER
3382 ,p_proration_rule IN VARCHAR2
3383 ,p_vr_commencement_dt IN DATE
3384 ,p_vr_termination_dt IN DATE) IS
3385
3386 /* get VR details */
3387 CURSOR vr_c(p_vr_id IN NUMBER) IS
3388 SELECT
3389 vr.var_rent_id
3390 ,vr.commencement_date
3391 ,vr.termination_date
3392 ,vr.proration_rule
3393 FROM
3394 pn_var_rents_all vr
3395 WHERE
3396 vr.var_rent_id = p_vr_id;
3397
3398 l_vr_commencement_date DATE;
3399 l_vr_termination_date DATE;
3400 l_vr_proration_rule VARCHAR2(30);
3401 l_fy_end_date DATE;
3402
3403 /* get the first partial period */
3404 CURSOR first_period_c( p_vr_id IN NUMBER
3405 ,p_comm_date IN DATE) IS
3406 SELECT
3407 prd.period_id
3408 ,prd.partial_period
3409 FROM
3410 pn_var_periods_all prd
3411 WHERE
3412 prd.var_rent_id = p_var_rent_id AND
3413 prd.start_date = p_comm_date;
3414
3415 l_first_period_id NUMBER;
3416 l_partial_period VARCHAR2(1);
3417
3418 BEGIN
3419
3420 /* get VR details */
3421 IF p_proration_rule IS NULL OR
3422 p_vr_commencement_dt IS NULL OR
3423 p_vr_termination_dt IS NULL
3424 THEN
3425 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
3426 l_vr_commencement_date := vr_rec.commencement_date;
3427 l_vr_termination_date := vr_rec.termination_date;
3428 l_vr_proration_rule := vr_rec.proration_rule;
3429 END LOOP;
3430 ELSE
3431 l_vr_commencement_date := p_vr_commencement_dt;
3432 l_vr_termination_date := p_vr_termination_dt;
3433 l_vr_proration_rule := p_proration_rule;
3434 END IF;
3435
3436 l_fy_end_date := ADD_MONTHS(l_vr_commencement_date, 12) - 1;
3437
3438 IF l_vr_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
3439 ,pn_var_trx_pkg.G_PRORUL_FLY) THEN
3440
3441 FOR prd_rec IN first_period_c( p_vr_id => p_var_rent_id
3442 ,p_comm_date => l_vr_commencement_date)
3443 LOOP
3444 l_first_period_id := prd_rec.period_id;
3445 l_partial_period := NVL(prd_rec.partial_period, 'N');
3446 END LOOP;
3447
3448 /* init ly_proration_sales */
3449 UPDATE
3450 pn_var_trx_headers_all
3451 SET
3452 fy_proration_sales = NULL
3453 WHERE
3454 var_rent_id = p_var_rent_id;
3455
3456 IF l_partial_period = 'Y' THEN
3457 /* populate ly_proration_sales */
3458 UPDATE
3459 pn_var_trx_headers_all hdr
3460 SET
3461 hdr.fy_proration_sales = hdr.prorated_group_sales
3462 WHERE
3463 hdr.var_rent_id = p_var_rent_id AND
3464 hdr.calc_prd_end_date <= l_fy_end_date;
3465
3466 /* populate fy_proration_sales if FY end date does not
3467 coincide with a calc prd end date */
3468 UPDATE
3469 pn_var_trx_headers_all
3470 SET
3471 fy_proration_sales
3472 = pn_var_trx_pkg.get_calc_prd_sales( var_rent_id
3473 ,period_id
3474 ,line_item_id
3475 ,grp_date_id
3476 ,calc_prd_start_date
3477 ,l_fy_end_date)
3478 WHERE
3479 var_rent_id = p_var_rent_id AND
3480 l_fy_end_date BETWEEN calc_prd_start_date
3481 AND (calc_prd_end_date - 1);
3482 END IF;
3483
3484 END IF;
3485
3486 EXCEPTION
3487 WHEN OTHERS THEN RAISE;
3488
3489 END populate_fy_pro_sales;
3490
3491 --------------------------------------------------------------------------------
3492 -- NAME : populate_ytd_sales
3493 -- DESCRIPTION :
3494 -- PURPOSE :
3495 -- INVOKED FROM :
3496 -- ARGUMENTS :
3497 -- REFERENCE : PN_COMMON.debug()
3498 -- HISTORY :
3499 --
3500 -- dd-mon-yyyy name o Created
3501 --------------------------------------------------------------------------------
3502 PROCEDURE populate_ytd_sales( p_var_rent_id IN NUMBER
3503 ,p_proration_rule IN VARCHAR2) IS
3504
3505 /* get VR info */
3506 CURSOR vr_c(p_vr_id IN NUMBER) IS
3507 SELECT
3508 vr.var_rent_id
3509 ,vr.proration_rule
3510 FROM
3511 pn_var_rents_all vr
3512 WHERE
3513 vr.var_rent_id = p_vr_id;
3514
3515 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
3516
3517 /* get the line items with updated sales for FY, LY, FLY, STD, NP */
3518 CURSOR lines_c(p_vr_id IN NUMBER) IS
3519 SELECT
3520 period_id
3521 ,line_item_id
3522 FROM
3523 pn_var_lines_all
3524 WHERE
3525 var_rent_id = p_vr_id AND
3526 sales_vol_update_flag = 'Y'
3527 ORDER BY
3528 period_id
3529 ,line_item_id;
3530
3531 /* get the line items with updated sales for CYP, CYNP */
3532 CURSOR lines_cs_c( p_vr_id IN NUMBER
3536 period_id
3533 ,p_part_prd_id IN NUMBER
3534 ,p_full_prd_id IN NUMBER) IS
3535 SELECT
3537 ,line_item_id
3538 FROM
3539 pn_var_lines_all
3540 WHERE
3541 var_rent_id = p_vr_id AND
3542 sales_vol_update_flag = 'Y' AND
3543 period_id NOT IN (p_part_prd_id, p_full_prd_id)
3544 ORDER BY
3545 period_id
3546 ,line_item_id;
3547
3548 /* get the period details - we use the first 2 periods */
3549 CURSOR periods_c(p_vr_id IN NUMBER) IS
3550 SELECT
3551 period_id
3552 ,start_date
3553 ,end_date
3554 ,partial_period
3555 FROM
3556 pn_var_periods_all
3557 WHERE
3558 var_rent_id = p_vr_id
3559 ORDER BY
3560 start_date;
3561
3562 /* period info */
3563 l_part_prd_id NUMBER;
3564 l_part_prd_start_dt DATE;
3565 l_part_prd_end_dt DATE;
3566 l_part_prd_partial_flag VARCHAR2(1);
3567
3568 l_full_prd_id NUMBER;
3569 l_full_prd_start_dt DATE;
3570 l_full_prd_end_dt DATE;
3571 l_full_prd_partial_flag VARCHAR2(1);
3572
3573 /* ytd for STD, NP, FY, LY, FLY */
3574 CURSOR ytd_sales_c( p_vr_ID IN NUMBER
3575 ,p_prd_ID IN NUMBER
3576 ,p_line_ID IN NUMBER) IS
3577 SELECT
3578 hdr.trx_header_id
3579 ,SUM(hdr.prorated_group_sales) OVER
3580 (PARTITION BY
3581 hdr.period_id
3582 ,hdr.line_item_id
3583 ,hdr.reset_group_id
3584 ORDER BY
3585 hdr.calc_prd_start_date
3586 ROWS UNBOUNDED PRECEDING) AS ytd_sales
3587 FROM
3588 pn_var_trx_headers_all hdr
3589 WHERE
3590 hdr.var_rent_id = p_vr_id AND
3591 hdr.period_id = p_prd_ID AND
3592 hdr.line_item_id = p_line_ID
3593 ORDER BY
3594 hdr.period_id
3595 ,hdr.line_item_id
3596 ,hdr.calc_prd_start_date;
3597
3598 /* ytd for CYP, CYNP combined period */
3599 CURSOR ytd_sales_cs_c( p_vr_ID IN NUMBER
3600 ,p_part_prd_id IN NUMBER
3601 ,p_full_prd_id IN NUMBER) IS
3602 SELECT
3603 hdr.trx_header_id
3604 ,SUM(hdr.prorated_group_sales) OVER
3605 (PARTITION BY
3606 hdr.line_item_group_id
3607 ORDER BY
3608 hdr.calc_prd_start_date
3609 ROWS UNBOUNDED PRECEDING) AS ytd_sales
3610 FROM
3611 pn_var_trx_headers_all hdr
3612 WHERE
3613 hdr.var_rent_id = p_vr_id AND
3614 hdr.period_id IN (p_part_prd_id, p_full_prd_id)
3615 ORDER BY
3616 hdr.line_item_group_id
3617 ,hdr.calc_prd_start_date;
3618
3619 /* counters */
3620 l_counter1 NUMBER;
3621
3622 /* plsql tables for ytd dates and trx hdr */
3623 trx_hdr_t NUM_T;
3624 ytd_sales_t NUM_T;
3625
3626 BEGIN
3627
3628 pnp_debug_pkg.log('++++ pn_var_trx_pkg.populate_ytd_sales START ++++');
3629
3630 /* get VR details */
3631 IF p_proration_rule IS NULL THEN
3632
3633 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
3634
3635 l_proration_rule := vr_rec.proration_rule;
3636
3637 END LOOP;
3638
3639 ELSE
3640
3641 l_proration_rule := p_proration_rule;
3642
3643 END IF;
3644
3645 pnp_debug_pkg.log('Called with: ');
3646 pnp_debug_pkg.log(' p_var_rent_id: '||p_var_rent_id);
3647 pnp_debug_pkg.log(' l_proration_rule: '||l_proration_rule);
3648
3649 /* l_proration_rule based decisions */
3650 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
3651 ,pn_var_trx_pkg.G_PRORUL_LY
3652 ,pn_var_trx_pkg.G_PRORUL_FLY
3653 ,pn_var_trx_pkg.G_PRORUL_NP
3654 ,pn_var_trx_pkg.G_PRORUL_STD) THEN
3655
3656 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id) LOOP
3657
3658 trx_hdr_t.DELETE;
3659 ytd_sales_t.DELETE;
3660
3661 OPEN ytd_sales_c( p_vr_ID => p_var_rent_id
3662 ,p_prd_ID => line_rec.period_id
3663 ,p_line_ID => line_rec.line_item_id);
3664
3665 FETCH ytd_sales_c BULK COLLECT INTO
3666 trx_hdr_t
3667 ,ytd_sales_t;
3668
3669 CLOSE ytd_sales_c;
3670
3671 FORALL i IN 1..trx_hdr_t.COUNT
3672 UPDATE
3673 pn_var_trx_headers_all
3674 SET
3675 ytd_sales = ytd_sales_t(i)
3676 WHERE
3677 trx_header_id = trx_hdr_t(i);
3678
3679 END LOOP;
3680
3681 ELSIF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
3682 ,pn_var_trx_pkg.G_PRORUL_CYNP) THEN
3683
3684 /* fetch partial and full period details */
3685 l_counter1 := 0;
3686 FOR prd_rec IN periods_c(p_vr_id => p_var_rent_id) LOOP
3687
3688 l_counter1 := l_counter1 + 1;
3689
3690 IF l_counter1 = 1 THEN
3691 l_part_prd_id := prd_rec.period_id;
3692 l_part_prd_start_dt := prd_rec.start_date;
3693 l_part_prd_end_dt := prd_rec.end_date;
3694 l_part_prd_partial_flag := prd_rec.partial_period;
3695
3696 ELSIF l_counter1 = 2 THEN
3697 l_full_prd_id := prd_rec.period_id;
3698 l_full_prd_start_dt := prd_rec.start_date;
3699 l_full_prd_end_dt := prd_rec.end_date;
3700 l_full_prd_partial_flag := prd_rec.partial_period;
3701
3702 ELSE
3703 EXIT;
3704
3705 END IF;
3706
3707 END LOOP; /* fetch partial and full period details */
3708
3712 OPEN ytd_sales_cs_c( p_vr_ID => p_var_rent_id
3709 trx_hdr_t.DELETE;
3710 ytd_sales_t.DELETE;
3711
3713 ,p_part_prd_id => l_part_prd_id
3714 ,p_full_prd_id => l_full_prd_id);
3715
3716 FETCH ytd_sales_cs_c BULK COLLECT INTO
3717 trx_hdr_t
3718 ,ytd_sales_t;
3719
3720 CLOSE ytd_sales_cs_c;
3721
3722 FORALL i IN 1..trx_hdr_t.COUNT
3723 UPDATE
3724 pn_var_trx_headers_all
3725 SET
3726 ytd_sales = ytd_sales_t(i)
3727 WHERE
3728 trx_header_id = trx_hdr_t(i);
3729
3730 /* loop for all lines */
3731 FOR line_rec IN lines_cs_c ( p_vr_id => p_var_rent_id
3732 ,p_part_prd_id => l_part_prd_id
3733 ,p_full_prd_id => l_full_prd_id)
3734 LOOP
3735
3736 trx_hdr_t.DELETE;
3737 ytd_sales_t.DELETE;
3738
3739 OPEN ytd_sales_c( p_vr_ID => p_var_rent_id
3740 ,p_prd_ID => line_rec.period_id
3741 ,p_line_ID => line_rec.line_item_id);
3742
3743 FETCH ytd_sales_c BULK COLLECT INTO
3744 trx_hdr_t
3745 ,ytd_sales_t;
3746
3747 CLOSE ytd_sales_c;
3748
3749 FORALL i IN 1..trx_hdr_t.COUNT
3750 UPDATE
3751 pn_var_trx_headers_all
3752 SET
3753 ytd_sales = ytd_sales_t(i)
3754 WHERE
3755 trx_header_id = trx_hdr_t(i);
3756
3757 END LOOP; /* loop for all lines */
3758
3759 END IF; /* l_proration_rule based decisions */
3760
3761 EXCEPTION
3762 WHEN OTHERS THEN RAISE;
3763
3764 END populate_ytd_sales;
3765
3766 --------------------------------------------------------------------------------
3767 -- NAME : populate_sales
3768 -- DESCRIPTION :
3769 -- PURPOSE :
3770 -- INVOKED FROM :
3771 -- ARGUMENTS :
3772 -- REFERENCE : PN_COMMON.debug()
3773 -- HISTORY :
3774 --
3775 -- dd-mon-yyyy name o Created
3776 --------------------------------------------------------------------------------
3777 PROCEDURE populate_sales(p_var_rent_id IN NUMBER) IS
3778
3779 /* get VR info */
3780 CURSOR vr_c(p_vr_id IN NUMBER) IS
3781 SELECT
3782 vr.org_id
3783 ,vr.var_rent_id
3784 ,vr.commencement_date
3785 ,vr.termination_date
3786 ,vr.proration_rule
3787 ,vr.cumulative_vol
3788 FROM
3789 pn_var_rents_all vr
3790 WHERE
3791 vr.var_rent_id = p_vr_id;
3792
3793 l_org_id pn_var_rents_all.org_id%TYPE;
3794 l_vr_commencement_date pn_var_rents_all.commencement_date%TYPE;
3795 l_vr_termination_date pn_var_rents_all.termination_date%TYPE;
3796 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
3797 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
3798
3799 /* get the line items with updated sales */
3800 CURSOR lines_c(p_vr_id IN NUMBER) IS
3801 SELECT
3802 period_id
3803 ,line_item_id
3804 FROM
3805 pn_var_lines_all
3806 WHERE
3807 var_rent_id = p_vr_id AND
3808 sales_vol_update_flag = 'Y'
3809 ORDER BY
3810 period_id
3811 ,line_item_id;
3812
3813 /* get the calc periods to update sales data */
3814 CURSOR calc_periods_c( p_vr_id IN NUMBER
3815 ,p_prd_id IN NUMBER
3816 ,p_line_id IN NUMBER) IS
3817 SELECT
3818 hdr.trx_header_id
3819 ,hdr.var_rent_id
3820 ,hdr.period_id
3821 ,hdr.line_item_id
3822 ,hdr.grp_date_id
3823 ,hdr.calc_prd_start_date
3824 ,hdr.calc_prd_end_date
3825 FROM
3826 pn_var_trx_headers_all hdr
3827 WHERE
3828 hdr.var_rent_id = p_vr_id AND
3829 hdr.period_id = p_prd_id AND
3830 hdr.line_item_id = p_line_id
3831 ORDER BY
3832 hdr.period_id
3833 ,hdr.line_item_id
3834 ,hdr.calc_prd_start_date
3835 ,hdr.calc_prd_end_date;
3836
3837 /* data structures */
3838 trx_hdr_t NUM_T;
3839 reporting_grp_sales_t NUM_T;
3840 prorate_grp_sales_t NUM_T;
3841
3842 l_counter NUMBER;
3843
3844 /* flags */
3845 l_sales_create_upd_flag BOOLEAN;
3846
3847 l_line_items_lock4salesvol_t NUM_T;
3848
3849 BEGIN
3850
3851 /* lock the lines with updated sales */
3852 l_line_items_lock4salesvol_t.DELETE;
3853
3854 OPEN line_items_lock4salesvol_c(p_vr_id => p_var_rent_id);
3855 FETCH line_items_lock4salesvol_c BULK COLLECT INTO l_line_items_lock4salesvol_t;
3856 CLOSE line_items_lock4salesvol_c;
3857
3858 /* get the VR details */
3859 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
3860
3861 l_org_id := vr_rec.org_id;
3862 l_vr_commencement_date := vr_rec.commencement_date;
3863 l_vr_termination_date := vr_rec.termination_date;
3864 l_proration_rule := vr_rec.proration_rule;
3865 l_calculation_method := vr_rec.cumulative_vol;
3866
3867 END LOOP;
3868
3869 l_sales_create_upd_flag := FALSE;
3870
3871 l_counter := 0;
3872
3873 /* for all line items with changed sales volume */
3874 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id)
3875 LOOP
3876
3877 l_sales_create_upd_flag := TRUE;
3878
3879 trx_hdr_t.DELETE;
3880 reporting_grp_sales_t.DELETE;
3881 prorate_grp_sales_t.DELETE;
3882
3883 /* for all calc sub periods for the line item, get the prorated sales */
3884 FOR trx_rec IN calc_periods_c( p_vr_id => p_var_rent_id
3888
3885 ,p_prd_id => line_rec.period_id
3886 ,p_line_id => line_rec.line_item_id)
3887 LOOP
3889 l_counter := l_counter + 1;
3890
3891 trx_hdr_t(l_counter) := trx_rec.trx_header_id;
3892
3893 pn_var_trx_pkg.get_calc_prd_sales
3894 ( p_var_rent_id => trx_rec.var_rent_id
3895 ,p_period_id => trx_rec.period_id
3896 ,p_line_item_id => trx_rec.line_item_id
3897 ,p_grp_date_id => trx_rec.grp_date_id
3898 ,p_start_date => trx_rec.calc_prd_start_date
3899 ,p_end_date => trx_rec.calc_prd_end_date
3900 ,x_pro_sales => prorate_grp_sales_t(l_counter)
3901 ,x_sales => reporting_grp_sales_t(l_counter));
3902
3903 END LOOP;
3904
3905 /* for all calc sub periods for the line item,
3906 update the trx headers with the sales */
3907 IF trx_hdr_t.COUNT > 0 THEN
3908
3909 FORALL i IN trx_hdr_t.FIRST..trx_hdr_t.LAST
3910 UPDATE
3911 pn_var_trx_headers_all
3912 SET
3913 reporting_group_sales = reporting_grp_sales_t(i)
3914 ,prorated_group_sales = prorate_grp_sales_t(i)
3915 WHERE
3916 trx_header_id = trx_hdr_t(i);
3917
3918 END IF;
3919
3920 END LOOP;
3921
3922 IF l_sales_create_upd_flag THEN
3923
3924 IF l_proration_rule = pn_var_trx_pkg.G_PRORUL_LY THEN
3925
3926 pn_var_trx_pkg.populate_ly_pro_sales
3927 ( p_var_rent_id => p_var_rent_id
3928 ,p_proration_rule => l_proration_rule
3929 ,p_vr_commencement_dt => l_vr_commencement_date
3930 ,p_vr_termination_dt => l_vr_termination_date);
3931
3932 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FY THEN
3933
3934 pn_var_trx_pkg.populate_fy_pro_sales
3935 ( p_var_rent_id => p_var_rent_id
3936 ,p_proration_rule => l_proration_rule
3937 ,p_vr_commencement_dt => l_vr_commencement_date
3938 ,p_vr_termination_dt => l_vr_termination_date);
3939
3940 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FLY THEN
3941
3942 pn_var_trx_pkg.populate_ly_pro_sales
3943 ( p_var_rent_id => p_var_rent_id
3944 ,p_proration_rule => l_proration_rule
3945 ,p_vr_commencement_dt => l_vr_commencement_date
3946 ,p_vr_termination_dt => l_vr_termination_date);
3947
3948 pn_var_trx_pkg.populate_fy_pro_sales
3949 ( p_var_rent_id => p_var_rent_id
3950 ,p_proration_rule => l_proration_rule
3951 ,p_vr_commencement_dt => l_vr_commencement_date
3952 ,p_vr_termination_dt => l_vr_termination_date);
3953
3954 END IF;
3955
3956 /* always populate YTD sales - very useful */
3957 pn_var_trx_pkg.populate_ytd_sales
3958 ( p_var_rent_id => p_var_rent_id
3959 ,p_proration_rule => l_proration_rule);
3960
3961 END IF;
3962
3963 FORALL line_rec IN 1..l_line_items_lock4salesvol_t.COUNT
3964 UPDATE
3965 pn_var_lines_all
3966 SET
3967 sales_vol_update_flag = NULL
3968 WHERE
3969 line_item_id = l_line_items_lock4salesvol_t(line_rec);
3970
3971 /* UN-lock the lines with updated sales */
3972 COMMIT;
3973
3974 EXCEPTION
3975 WHEN OTHERS THEN RAISE;
3976
3977 END populate_sales;
3978
3979 --------------------------------------------------------------------------------
3980 --------------- PROCEDURES TO POPULATE PRORATED FORECASTED SALES ---------------
3981 --------------------------------------------------------------------------------
3982
3983
3984 --------------------------------------------------------------------------------
3985 -- NAME : get_calc_prd_sales_for
3986 -- DESCRIPTION : get forecasted volumes for a calculation period, sub period
3987 -- PURPOSE :
3988 -- INVOKED FROM :
3989 -- ARGUMENTS :
3990 -- REFERENCE : PN_COMMON.debug()
3991 -- HISTORY :
3992 --
3993 -- 13-SEP-06 Shabda o Created
3994 --------------------------------------------------------------------------------
3995 PROCEDURE get_calc_prd_sales_for( p_var_rent_id IN NUMBER
3996 ,p_period_id IN NUMBER
3997 ,p_line_item_id IN NUMBER
3998 ,p_grp_date_id IN NUMBER
3999 ,p_start_date IN DATE
4000 ,p_end_date IN DATE
4001 ,x_pro_sales OUT NOCOPY NUMBER
4002 ,x_sales OUT NOCOPY NUMBER) IS
4003
4004 /* get forecasted volumes for a calculation period */
4005 CURSOR vol_hist_sum_c( p_prd_id IN NUMBER
4006 ,p_line_id IN NUMBER
4007 ,p_grp_id IN NUMBER) IS
4008 SELECT
4009 SUM(forecasted_amount) AS calc_prd_forecasted_volume
4010 FROM
4011 pn_var_vol_hist_all sales
4012 WHERE
4013 sales.period_id = p_prd_id AND
4014 sales.line_item_id = p_line_id AND
4015 sales.grp_date_id = p_grp_id;
4016
4017 /* get forecasted volumes for a calculation sub-period */
4018 CURSOR vol_hist_c( p_prd_id IN NUMBER
4019 ,p_line_id IN NUMBER
4020 ,p_grp_id IN NUMBER
4021 ,p_start_dt IN DATE
4022 ,p_end_dt IN DATE) IS
4023 SELECT
4024 sales.forecasted_amount
4025 ,sales.start_date
4026 ,sales.end_date
4027 FROM
4028 pn_var_vol_hist_all sales
4029 WHERE
4030 sales.period_id = p_prd_id AND
4031 sales.line_item_id = p_line_id AND
4032 sales.grp_date_id = p_grp_id AND
4036 /* get grp dates */
4033 sales.start_date <= p_end_dt AND
4034 sales.end_date >= p_start_dt;
4035
4037 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
4038 SELECT
4039 grp.grp_start_date
4040 ,grp.grp_end_date
4041 FROM
4042 pn_var_grp_dates_all grp
4043 WHERE
4044 grp.grp_date_id = p_grp_id;
4045
4046 l_grp_start_date DATE;
4047 l_grp_end_date DATE;
4048
4049 l_calc_prd_sales NUMBER;
4050 l_pro_calc_prd_sales NUMBER;
4051
4052 BEGIN
4053
4054 /* get group / calc period dates */
4055 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
4056 l_grp_start_date := grp_rec.grp_start_date;
4057 l_grp_end_date := grp_rec.grp_end_date;
4058 END LOOP;
4059
4060 l_calc_prd_sales := 0;
4061 l_pro_calc_prd_sales := 0;
4062
4063 /* get all APPROVED sales for a group / calc period */
4064 FOR sales_rec IN vol_hist_sum_c ( p_prd_id => p_period_id
4065 ,p_line_id => p_line_item_id
4066 ,p_grp_id => p_grp_date_id)
4067 LOOP
4068 l_calc_prd_sales := l_calc_prd_sales + sales_rec.calc_prd_forecasted_volume;
4069 END LOOP;
4070
4071 /* if calc sub period dates are same as grp / calc period start-end dates */
4072 IF l_grp_start_date = p_start_date AND
4073 l_grp_end_date = p_end_date
4074 THEN
4075
4076 /* then prorated sales = total sales */
4077 l_pro_calc_prd_sales := l_calc_prd_sales;
4078
4079 ELSE
4080
4081 /* else, sum all sales to get the prorated sales */
4082 FOR sales_rec IN vol_hist_c ( p_prd_id => p_period_id
4083 ,p_line_id => p_line_item_id
4084 ,p_grp_id => p_grp_date_id
4085 ,p_start_dt => p_start_date
4086 ,p_end_dt => p_end_date)
4087 LOOP
4088
4089 /* if sales volume dates between calc sub period dates */
4090 IF sales_rec.start_date >= p_start_date AND
4091 sales_rec.end_date <= p_end_date
4092 THEN
4093 /* consider full volume */
4094 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.forecasted_amount;
4095
4096 /* else if sales volume dates overlap calc sub period dates */
4097 ELSIF sales_rec.start_date <= p_end_date AND
4098 sales_rec.end_date >= p_start_date
4099 THEN
4100 /* then consider prorated volume */
4101 l_pro_calc_prd_sales
4102 := l_pro_calc_prd_sales
4103 + sales_rec.forecasted_amount
4104 * ((LEAST(sales_rec.end_date, p_end_date)
4105 - GREATEST(sales_rec.start_date, p_start_date)) + 1)
4106 / ((sales_rec.end_date - sales_rec.start_date) + 1);
4107
4108 END IF;
4109
4110 END LOOP;
4111
4112 END IF;
4113
4114 x_pro_sales := l_pro_calc_prd_sales;
4115 x_sales := l_calc_prd_sales;
4116
4117 EXCEPTION
4118 WHEN OTHERS THEN RAISE;
4119
4120 END get_calc_prd_sales_for;
4121
4122 --------------------------------------------------------------------------------
4123 -- NAME : get_calc_prd_sales_for
4124 -- DESCRIPTION :get volumes for a calculation period, sub period
4125 -- PURPOSE :
4126 -- INVOKED FROM :
4127 -- ARGUMENTS :
4128 -- REFERENCE : PN_COMMON.debug()
4129 -- HISTORY :
4130 --
4131 -- 13-SEP-06 Shabda o Created
4132 --------------------------------------------------------------------------------
4133 FUNCTION get_calc_prd_sales_for( p_var_rent_id IN NUMBER
4134 ,p_period_id IN NUMBER
4135 ,p_line_item_id IN NUMBER
4136 ,p_grp_date_id IN NUMBER
4137 ,p_start_date IN DATE
4138 ,p_end_date IN DATE)
4139 RETURN NUMBER IS
4140
4141 /* get volumes for a calculation period */
4142 CURSOR vol_hist_sum_c ( p_prd_id IN NUMBER
4143 ,p_line_id IN NUMBER
4144 ,p_grp_id IN NUMBER) IS
4145 SELECT
4146 SUM(forecasted_amount) AS calc_prd_forecasted_volume
4147 FROM
4148 pn_var_vol_hist_all sales
4149 WHERE
4150 sales.period_id = p_prd_id AND
4151 sales.line_item_id = p_line_id AND
4152 sales.grp_date_id = p_grp_id;
4153
4154 /* get volumes for a calculation sub-period */
4155 CURSOR vol_hist_c ( p_prd_id IN NUMBER
4156 ,p_line_id IN NUMBER
4157 ,p_grp_id IN NUMBER
4158 ,p_start_dt IN DATE
4159 ,p_end_dt IN DATE) IS
4160 SELECT
4161 sales.forecasted_amount
4162 ,sales.start_date
4163 ,sales.end_date
4164 FROM
4165 pn_var_vol_hist_all sales
4166 WHERE
4167 sales.period_id = p_prd_id AND
4168 sales.line_item_id = p_line_id AND
4169 sales.grp_date_id = p_grp_id AND
4170 sales.start_date <= p_end_dt AND
4171 sales.end_date >= p_start_dt;
4172
4173 /* get grp dates */
4174 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
4175 SELECT
4176 grp.grp_start_date
4177 ,grp.grp_end_date
4178 FROM
4179 pn_var_grp_dates_all grp
4180 WHERE
4181 grp.grp_date_id = p_grp_id;
4182
4183 l_grp_start_date DATE;
4184 l_grp_end_date DATE;
4185
4186 l_calc_prd_sales NUMBER;
4187 l_pro_calc_prd_sales NUMBER;
4188
4189 BEGIN
4190
4191 /* get group / calc period dates */
4192 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
4193 l_grp_start_date := grp_rec.grp_start_date;
4197 l_pro_calc_prd_sales := 0;
4194 l_grp_end_date := grp_rec.grp_end_date;
4195 END LOOP;
4196
4198
4199 /* if calc sub period dates are same as grp / calc period start-end dates */
4200 IF l_grp_start_date = p_start_date AND
4201 l_grp_end_date = p_end_date
4202 THEN
4203
4204 /* get all APPROVED sales for a group / calc period
4205 prorated sales = total sales */
4206 FOR sales_rec IN vol_hist_sum_c ( p_prd_id => p_period_id
4207 ,p_line_id => p_line_item_id
4208 ,p_grp_id => p_grp_date_id)
4209 LOOP
4210 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.calc_prd_forecasted_volume;
4211 END LOOP;
4212
4213 ELSE
4214
4215 /* else, sum all sales to get the prorated sales */
4216 FOR sales_rec IN vol_hist_c ( p_prd_id => p_period_id
4217 ,p_line_id => p_line_item_id
4218 ,p_grp_id => p_grp_date_id
4219 ,p_start_dt => p_start_date
4220 ,p_end_dt => p_end_date)
4221 LOOP
4222
4223 /* if sales volume dates between calc sub period dates */
4224 IF sales_rec.start_date >= p_start_date AND
4225 sales_rec.end_date <= p_end_date
4226 THEN
4227 /* consider full volume */
4228 l_pro_calc_prd_sales := l_pro_calc_prd_sales + sales_rec.forecasted_amount;
4229
4230 /* else if sales volume dates overlap calc sub period dates */
4231 ELSIF sales_rec.start_date <= p_end_date AND
4232 sales_rec.end_date >= p_start_date
4233 THEN
4234 /* then consider prorated volume */
4235 l_pro_calc_prd_sales
4236 := l_pro_calc_prd_sales
4237 + sales_rec.forecasted_amount
4238 * ((LEAST(sales_rec.end_date, p_end_date)
4239 - GREATEST(sales_rec.start_date, p_start_date)) + 1)
4240 / ((sales_rec.end_date - sales_rec.start_date) + 1);
4241
4242 END IF;
4243
4244 END LOOP;
4245
4246 END IF;
4247
4248 RETURN l_pro_calc_prd_sales;
4249
4250 EXCEPTION
4251 WHEN OTHERS THEN RAISE;
4252
4253 END get_calc_prd_sales_for;
4254
4255
4256
4257 --------------------------------------------------------------------------------
4258 -- NAME : populate_ytd_sales_for
4259 -- DESCRIPTION : gets forecasted YTD sales
4260 -- PURPOSE :
4261 -- INVOKED FROM :
4262 -- ARGUMENTS :
4263 -- REFERENCE : PN_COMMON.debug()
4264 -- HISTORY :
4265 --
4266 -- 13-SEP-06 Shabda o Created
4267 --------------------------------------------------------------------------------
4268 PROCEDURE populate_ytd_sales_for( p_var_rent_id IN NUMBER
4269 ,p_calc_method IN VARCHAR2) IS
4270
4271 /* get VR info */
4272 CURSOR vr_c(p_vr_id IN NUMBER) IS
4273 SELECT
4274 vr.var_rent_id
4275 ,vr.cumulative_vol
4276 FROM
4277 pn_var_rents_all vr
4278 WHERE
4279 vr.var_rent_id = p_vr_id;
4280
4281 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
4282 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
4283
4284 /* get the line items with updated sales for FY, LY, FLY, STD, NP */
4285 CURSOR lines_c(p_vr_id IN NUMBER) IS
4286 SELECT
4287 period_id
4288 ,line_item_id
4289 FROM
4290 pn_var_lines_all
4291 WHERE
4292 var_rent_id = p_vr_id AND
4293 sales_vol_update_flag = 'Y'
4294 ORDER BY
4295 period_id
4296 ,line_item_id;
4297
4298 /* ytd for STD, NP */
4299 CURSOR ytd_sales_c( p_vr_ID IN NUMBER
4300 ,p_prd_ID IN NUMBER
4301 ,p_line_ID IN NUMBER) IS
4302 SELECT
4303 hdr.trx_header_id
4304 ,SUM(hdr.prorated_group_sales_for) OVER
4305 (PARTITION BY
4306 hdr.period_id
4307 ,hdr.line_item_id
4308 ,hdr.reset_group_id
4309 ORDER BY
4310 hdr.calc_prd_start_date
4311 ROWS UNBOUNDED PRECEDING) AS ytd_sales_for
4312 FROM
4313 pn_var_trx_headers_all hdr
4314 WHERE
4315 hdr.var_rent_id = p_vr_id AND
4316 hdr.period_id = p_prd_ID AND
4317 hdr.line_item_id = p_line_ID
4318 ORDER BY
4319 hdr.period_id
4320 ,hdr.line_item_id
4321 ,hdr.calc_prd_start_date;
4322
4323 /* counters */
4324 l_counter1 NUMBER;
4325
4326 /* plsql tables for ytd dates and trx hdr */
4327 trx_hdr_t NUM_T;
4328 ytd_sales_for_t NUM_T;
4329
4330 BEGIN
4331
4332 /* get VR details */
4333 IF p_calc_method IS NULL THEN
4334 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
4335 l_calculation_method := vr_rec.cumulative_vol;
4336 END LOOP;
4337 ELSE
4338 l_calculation_method := p_calc_method;
4339 END IF;
4340
4341 IF l_calculation_method IN ( pn_var_trx_pkg.G_CALC_YTD
4342 ,pn_var_trx_pkg.G_CALC_CUMULATIVE) THEN
4343
4344 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id) LOOP
4345
4346 trx_hdr_t.DELETE;
4347 ytd_sales_for_t.DELETE;
4348
4349 OPEN ytd_sales_c( p_vr_ID => p_var_rent_id
4350 ,p_prd_ID => line_rec.period_id
4351 ,p_line_ID => line_rec.line_item_id);
4352
4353 FETCH ytd_sales_c BULK COLLECT INTO
4354 trx_hdr_t
4355 ,ytd_sales_for_t;
4356
4357 CLOSE ytd_sales_c;
4358
4359 FORALL i IN 1..trx_hdr_t.COUNT
4360 UPDATE
4361 pn_var_trx_headers_all
4365 trx_header_id = trx_hdr_t(i);
4362 SET
4363 ytd_sales_for = ytd_sales_for_t(i)
4364 WHERE
4366
4367 END LOOP;
4368
4369 END IF; /* IF l_calculation_method IN */
4370
4371 EXCEPTION
4372 WHEN OTHERS THEN RAISE;
4373
4374 END populate_ytd_sales_for;
4375
4376 --------------------------------------------------------------------------------
4377 -- NAME : populate_sales_for
4378 -- DESCRIPTION : populates forecasted sales in trx header tables
4379 -- PURPOSE :
4380 -- INVOKED FROM :
4381 -- ARGUMENTS :
4382 -- REFERENCE : PN_COMMON.debug()
4383 -- HISTORY :
4384 --
4385 -- 13-SEP-06 Shabda o Created
4386 --------------------------------------------------------------------------------
4387 PROCEDURE populate_sales_for(p_var_rent_id IN NUMBER) IS
4388
4389 /* get VR info */
4390 CURSOR vr_c(p_vr_id IN NUMBER) IS
4391 SELECT
4392 vr.org_id
4393 ,vr.var_rent_id
4394 ,vr.commencement_date
4395 ,vr.termination_date
4396 ,vr.proration_rule
4397 ,vr.cumulative_vol
4398 FROM
4399 pn_var_rents_all vr
4400 WHERE
4401 vr.var_rent_id = p_vr_id;
4402
4403 l_org_id pn_var_rents_all.org_id%TYPE;
4404 l_vr_commencement_date pn_var_rents_all.commencement_date%TYPE;
4405 l_vr_termination_date pn_var_rents_all.termination_date%TYPE;
4406 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
4407 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
4408
4409 /* get the line items with updated sales */
4410 CURSOR lines_c(p_vr_id IN NUMBER) IS
4411 SELECT
4412 period_id
4413 ,line_item_id
4414 FROM
4415 pn_var_lines_all
4416 WHERE
4417 var_rent_id = p_vr_id AND
4418 sales_vol_update_flag = 'Y'
4419 ORDER BY
4420 period_id
4421 ,line_item_id;
4422
4423 /* get the calc periods to update sales data */
4424 CURSOR calc_periods_c( p_vr_id IN NUMBER
4425 ,p_prd_id IN NUMBER
4426 ,p_line_id IN NUMBER) IS
4427 SELECT
4428 hdr.trx_header_id
4429 ,hdr.var_rent_id
4430 ,hdr.period_id
4431 ,hdr.line_item_id
4432 ,hdr.grp_date_id
4433 ,hdr.calc_prd_start_date
4434 ,hdr.calc_prd_end_date
4435 FROM
4436 pn_var_trx_headers_all hdr
4437 WHERE
4438 hdr.var_rent_id = p_vr_id AND
4439 hdr.period_id = p_prd_id AND
4440 hdr.line_item_id = p_line_id
4441 ORDER BY
4442 hdr.period_id
4443 ,hdr.line_item_id
4444 ,hdr.calc_prd_start_date
4445 ,hdr.calc_prd_end_date;
4446
4447 /* data structures */
4448 trx_hdr_t NUM_T;
4449 reporting_grp_sales_t NUM_T;
4450 prorate_grp_sales_t NUM_T;
4451
4452 l_counter NUMBER;
4453
4454 /* flags */
4455 l_sales_create_upd_flag BOOLEAN;
4456
4457 l_line_items_lock4salesvol_t NUM_T;
4458
4459
4460 BEGIN
4461
4462 /* lock the lines with updated sales */
4463 l_line_items_lock4salesvol_t.DELETE;
4464
4465 OPEN line_items_lock4salesvol_c(p_vr_id => p_var_rent_id);
4466 FETCH line_items_lock4salesvol_c BULK COLLECT INTO l_line_items_lock4salesvol_t;
4467 CLOSE line_items_lock4salesvol_c;
4468
4469 /* get the VR details */
4470 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
4471
4472 l_org_id := vr_rec.org_id;
4473 l_vr_commencement_date := vr_rec.commencement_date;
4474 l_vr_termination_date := vr_rec.termination_date;
4475 l_proration_rule := vr_rec.proration_rule;
4476 l_calculation_method := vr_rec.cumulative_vol;
4477
4478 END LOOP;
4479
4480 l_sales_create_upd_flag := FALSE;
4481
4482 l_counter := 0;
4483
4484 /* for all line items with changed sales volume */
4485 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id)
4486 LOOP
4487
4488 l_sales_create_upd_flag := TRUE;
4489
4490 trx_hdr_t.DELETE;
4491 reporting_grp_sales_t.DELETE;
4492 prorate_grp_sales_t.DELETE;
4493
4494 /* for all calc sub periods for the line item, get the prorated sales */
4495 FOR trx_rec IN calc_periods_c( p_vr_id => p_var_rent_id
4496 ,p_prd_id => line_rec.period_id
4497 ,p_line_id => line_rec.line_item_id)
4498 LOOP
4499
4500 l_counter := l_counter + 1;
4501
4502 trx_hdr_t(l_counter) := trx_rec.trx_header_id;
4503
4504 pn_var_trx_pkg.get_calc_prd_sales_for
4505 ( p_var_rent_id => trx_rec.var_rent_id
4506 ,p_period_id => trx_rec.period_id
4507 ,p_line_item_id => trx_rec.line_item_id
4508 ,p_grp_date_id => trx_rec.grp_date_id
4509 ,p_start_date => trx_rec.calc_prd_start_date
4510 ,p_end_date => trx_rec.calc_prd_end_date
4511 ,x_pro_sales => prorate_grp_sales_t(l_counter)
4512 ,x_sales => reporting_grp_sales_t(l_counter));
4513
4514 END LOOP;
4515 /* for all calc sub periods for the line item,
4516 update the trx headers with the sales */
4517 IF trx_hdr_t.COUNT > 0 THEN
4518
4519 FORALL i IN trx_hdr_t.FIRST..trx_hdr_t.LAST
4520 UPDATE
4521 pn_var_trx_headers_all
4522 SET
4523 reporting_group_sales_for = reporting_grp_sales_t(i)
4524 ,prorated_group_sales_for = prorate_grp_sales_t(i)
4525 WHERE
4526 trx_header_id = trx_hdr_t(i);
4527
4528 END IF;
4529
4530 END LOOP;
4531
4532 /*Similar to actuals - We always populate YTD sales*/
4533
4537
4534 pn_var_trx_pkg.populate_ytd_sales_for
4535 ( p_var_rent_id => p_var_rent_id
4536 ,p_calc_method => l_calculation_method);
4538 /*This would be needed in actual sales, but not for forecasted.
4539 FORALL line_rec IN 1..l_line_items_lock4salesvol_t.COUNT
4540 UPDATE
4541 pn_var_lines_all
4542 SET
4543 sales_vol_update_flag = NULL
4544 WHERE
4545 line_item_id = l_line_items_lock4salesvol_t(line_rec);*/
4546
4547 /* UN-lock the lines with updated sales */
4548 COMMIT;
4549
4550 EXCEPTION
4551 WHEN OTHERS THEN RAISE;
4552
4553 END populate_sales_for;
4554 /*-----------------------------------------------------------------------------
4555 ----------------PROCEDURES TO POPULATE DEDUCTIONS------------------------------
4556 -----------------------------------------------------------------------------*/
4557 --------------------------------------------------------------------------------
4558 -- NAME : get_calc_prd_dedc
4559 -- DESCRIPTION :
4560 -- PURPOSE :
4561 -- INVOKED FROM :
4562 -- ARGUMENTS :
4563 -- REFERENCE : PN_COMMON.debug()
4564 -- HISTORY :
4565 --
4566 -- 8/10/06 Shabda o Created
4567 --------------------------------------------------------------------------------
4568 PROCEDURE get_calc_prd_dedc( p_var_rent_id IN NUMBER
4569 ,p_period_id IN NUMBER
4570 ,p_line_item_id IN NUMBER
4571 ,p_grp_date_id IN NUMBER
4572 ,p_start_date IN DATE
4573 ,p_end_date IN DATE
4574 ,x_pro_dedc OUT NOCOPY NUMBER
4575 ,x_dedc OUT NOCOPY NUMBER) IS
4576
4577
4578 /* get volumes for a calculation period */
4579 CURSOR dedc_c1 ( p_prd_id IN NUMBER
4580 ,p_line_id IN NUMBER
4581 ,p_grp_id IN NUMBER) IS
4582 SELECT
4583 SUM(deduction_amount) AS calc_prd_dedc
4584 FROM
4585 pn_var_deductions_all dedc
4586 WHERE
4587 dedc.period_id = p_prd_id AND
4588 dedc.line_item_id = p_line_id AND
4589 dedc.grp_date_id = p_grp_id;
4590
4591 /* get volumes for a calculation sub-period */
4592 CURSOR dedc_c2 ( p_prd_id IN NUMBER
4593 ,p_line_id IN NUMBER
4594 ,p_grp_id IN NUMBER
4595 ,p_start_dt IN DATE
4596 ,p_end_dt IN DATE) IS
4597 SELECT
4598 dedc.deduction_amount
4599 ,dedc.start_date
4600 ,dedc.end_date
4601 FROM
4602 pn_var_deductions_all dedc
4603 WHERE
4604 dedc.period_id = p_prd_id AND
4605 dedc.line_item_id = p_line_id AND
4606 dedc.grp_date_id = p_grp_id AND
4607 dedc.start_date <= p_end_dt AND
4608 dedc.end_date >= p_start_dt;
4609
4610 /* get grp dates */
4611 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
4612 SELECT
4613 grp.grp_start_date
4614 ,grp.grp_end_date
4615 FROM
4616 pn_var_grp_dates_all grp
4617 WHERE
4618 grp.grp_date_id = p_grp_id;
4619
4620 l_grp_start_date DATE;
4621 l_grp_end_date DATE;
4622
4623 l_calc_prd_dedc NUMBER;
4624 l_pro_calc_prd_dedc NUMBER;
4625
4626 BEGIN
4627 /* get group / calc period dates */
4628 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
4629 l_grp_start_date := grp_rec.grp_start_date;
4630 l_grp_end_date := grp_rec.grp_end_date;
4631 END LOOP;
4632
4633 l_calc_prd_dedc := 0;
4634 l_pro_calc_prd_dedc := 0;
4635
4636 /* get all APPROVED deductions for a group / calc period */
4637 FOR dedc_rec IN dedc_c1 ( p_prd_id => p_period_id
4638 ,p_line_id => p_line_item_id
4639 ,p_grp_id => p_grp_date_id)
4640 LOOP
4641 l_calc_prd_dedc := l_calc_prd_dedc + dedc_rec.calc_prd_dedc;
4642 END LOOP;
4643
4644 /* if calc sub period dates are same as grp / calc period start-end dates */
4645 IF l_grp_start_date = p_start_date AND
4646 l_grp_end_date = p_end_date
4647 THEN
4648
4649 /* then prorated deductions = total deductions */
4650 l_pro_calc_prd_dedc := l_calc_prd_dedc;
4651
4652 ELSE
4653
4654 /* else, sum all deductions to get the prorated deductions */
4655 FOR dedc_rec IN dedc_c2 ( p_prd_id => p_period_id
4656 ,p_line_id => p_line_item_id
4657 ,p_grp_id => p_grp_date_id
4658 ,p_start_dt => p_start_date
4659 ,p_end_dt => p_end_date)
4660 LOOP
4661
4662 /* if deductions volume dates between calc sub period dates */
4663 IF dedc_rec.start_date >= p_start_date AND
4664 dedc_rec.end_date <= p_end_date
4665 THEN
4666 /* consider full volume */
4667 l_pro_calc_prd_dedc := l_pro_calc_prd_dedc + dedc_rec.deduction_amount;
4668
4669 /* else if deductions volume dates overlap calc sub period dates */
4670 ELSIF dedc_rec.start_date <= p_end_date AND
4671 dedc_rec.end_date >= p_start_date
4672 THEN
4673 /* then consider prorated volume */
4674 l_pro_calc_prd_dedc
4675 := l_pro_calc_prd_dedc
4676 + dedc_rec.deduction_amount
4677 * ((LEAST(dedc_rec.end_date, p_end_date)
4678 - GREATEST(dedc_rec.start_date, p_start_date)) + 1)
4679 / ((dedc_rec.end_date - dedc_rec.start_date) + 1);
4680
4681 END IF;
4682
4683 END LOOP;
4684
4685 END IF;
4686 x_pro_dedc := l_pro_calc_prd_dedc;
4690 WHEN OTHERS THEN RAISE;
4687 x_dedc := l_calc_prd_dedc;
4688
4689 EXCEPTION
4691
4692 END get_calc_prd_dedc;
4693
4694 --------------------------------------------------------------------------------
4695 -- NAME : get_calc_prd_dedc
4696 -- DESCRIPTION :
4697 -- PURPOSE :
4698 -- INVOKED FROM :
4699 -- ARGUMENTS :
4700 -- REFERENCE : PN_COMMON.debug()
4701 -- HISTORY :
4702 --
4703 -- 8/10/06 Shabda o Created
4704 --------------------------------------------------------------------------------
4705 FUNCTION get_calc_prd_dedc( p_var_rent_id IN NUMBER
4706 ,p_period_id IN NUMBER
4707 ,p_line_item_id IN NUMBER
4708 ,p_grp_date_id IN NUMBER
4709 ,p_start_date IN DATE
4710 ,p_end_date IN DATE)
4711
4712 RETURN NUMBER IS
4713
4714
4715 /* get deductions for a calculation period */
4716 CURSOR dedc_c1 ( p_prd_id IN NUMBER
4717 ,p_line_id IN NUMBER
4718 ,p_grp_id IN NUMBER) IS
4719 SELECT
4720 SUM(deduction_amount) AS calc_prd_dedc
4721 FROM
4722 pn_var_deductions_all dedc
4723 WHERE
4724 dedc.period_id = p_prd_id AND
4725 dedc.line_item_id = p_line_id AND
4726 dedc.grp_date_id = p_grp_id;
4727
4728 /* get deductions for a calculation sub-period */
4729 CURSOR dedc_c2 ( p_prd_id IN NUMBER
4730 ,p_line_id IN NUMBER
4731 ,p_grp_id IN NUMBER
4732 ,p_start_dt IN DATE
4733 ,p_end_dt IN DATE) IS
4734 SELECT
4735 dedc.deduction_amount
4736 ,dedc.start_date
4737 ,dedc.end_date
4738 FROM
4739 pn_var_deductions_all dedc
4740 WHERE
4741 dedc.period_id = p_prd_id AND
4742 dedc.line_item_id = p_line_id AND
4743 dedc.grp_date_id = p_grp_id AND
4744 dedc.start_date <= p_end_dt AND
4745 dedc.end_date >= p_start_dt;
4746
4747 /* get grp dates */
4748 CURSOR grp_dates_c(p_grp_id IN NUMBER) IS
4749 SELECT
4750 grp.grp_start_date
4751 ,grp.grp_end_date
4752 FROM
4753 pn_var_grp_dates_all grp
4754 WHERE
4755 grp.grp_date_id = p_grp_id;
4756
4757 l_grp_start_date DATE;
4758 l_grp_end_date DATE;
4759
4760 l_calc_prd_dedc NUMBER;
4761 l_pro_calc_prd_dedc NUMBER;
4762
4763 BEGIN
4764 /* get group / calc period dates */
4765 FOR grp_rec IN grp_dates_c(p_grp_id => p_grp_date_id) LOOP
4766 l_grp_start_date := grp_rec.grp_start_date;
4767 l_grp_end_date := grp_rec.grp_end_date;
4768 END LOOP;
4769
4770 l_calc_prd_dedc := 0;
4771 l_pro_calc_prd_dedc := 0;
4772
4773 /* get all APPROVED deductions for a group / calc period */
4774 FOR dedc_rec IN dedc_c1 ( p_prd_id => p_period_id
4775 ,p_line_id => p_line_item_id
4776 ,p_grp_id => p_grp_date_id)
4777 LOOP
4778 l_calc_prd_dedc := l_calc_prd_dedc + dedc_rec.calc_prd_dedc;
4779 END LOOP;
4780
4781 /* if calc sub period dates are same as grp / calc period start-end dates */
4782 IF l_grp_start_date = p_start_date AND
4783 l_grp_end_date = p_end_date
4784 THEN
4785
4786 /* then prorated deductions = total deductions */
4787 l_pro_calc_prd_dedc := l_calc_prd_dedc;
4788
4789 ELSE
4790
4791 /* else, sum all deductions to get the prorated deductions */
4792 FOR dedc_rec IN dedc_c2 ( p_prd_id => p_period_id
4793 ,p_line_id => p_line_item_id
4794 ,p_grp_id => p_grp_date_id
4795 ,p_start_dt => p_start_date
4796 ,p_end_dt => p_end_date)
4797 LOOP
4798
4799 /* if deductions volume dates between calc sub period dates */
4800 IF dedc_rec.start_date >= p_start_date AND
4801 dedc_rec.end_date <= p_end_date
4802 THEN
4803 /* consider full volume */
4804 l_pro_calc_prd_dedc := l_pro_calc_prd_dedc + dedc_rec.deduction_amount;
4805
4806 /* else if deductions volume dates overlap calc sub period dates */
4807 ELSIF dedc_rec.start_date <= p_end_date AND
4808 dedc_rec.end_date >= p_start_date
4809 THEN
4810 /* then consider prorated volume */
4811 l_pro_calc_prd_dedc
4812 := l_pro_calc_prd_dedc
4813 + dedc_rec.deduction_amount
4814 * ((LEAST(dedc_rec.end_date, p_end_date)
4815 - GREATEST(dedc_rec.start_date, p_start_date)) + 1)
4816 / ((dedc_rec.end_date - dedc_rec.start_date) + 1);
4817
4818 END IF;
4819
4820 END LOOP;
4821
4822 END IF;
4823 RETURN l_pro_calc_prd_dedc;
4824
4825 EXCEPTION
4826 WHEN OTHERS THEN RAISE;
4827
4828 END get_calc_prd_dedc;
4829 --------------------------------------------------------------------------------
4830 -- NAME : populate_ly_pro_dedc
4831 -- DESCRIPTION :
4832 -- PURPOSE :
4833 -- INVOKED FROM :
4834 -- ARGUMENTS :
4835 -- REFERENCE : PN_COMMON.debug()
4836 -- HISTORY :
4837 --
4838 -- 12/10/06 Shabda o Created
4839 --------------------------------------------------------------------------------
4840 PROCEDURE populate_ly_pro_dedc( p_var_rent_id IN NUMBER
4841 ,p_proration_rule IN VARCHAR2
4842 ,p_vr_commencement_dt IN DATE
4843 ,p_vr_termination_dt IN DATE) IS
4844
4845 /* get VR details */
4849 ,vr.commencement_date
4846 CURSOR vr_c(p_vr_id IN NUMBER) IS
4847 SELECT
4848 vr.var_rent_id
4850 ,vr.termination_date
4851 ,vr.proration_rule
4852 FROM
4853 pn_var_rents_all vr
4854 WHERE
4855 vr.var_rent_id = p_vr_id;
4856
4857 l_vr_commencement_date DATE;
4858 l_vr_termination_date DATE;
4859 l_vr_proration_rule VARCHAR2(30);
4860 l_ly_start_date DATE;
4861
4862 BEGIN
4863
4864 /* get VR details */
4865 IF p_proration_rule IS NULL OR
4866 p_vr_commencement_dt IS NULL OR
4867 p_vr_termination_dt IS NULL
4868 THEN
4869 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
4870 l_vr_commencement_date := vr_rec.commencement_date;
4871 l_vr_termination_date := vr_rec.termination_date;
4872 l_vr_proration_rule := vr_rec.proration_rule;
4873 END LOOP;
4874 ELSE
4875 l_vr_commencement_date := p_vr_commencement_dt;
4876 l_vr_termination_date := p_vr_termination_dt;
4877 l_vr_proration_rule := p_proration_rule;
4878 END IF;
4879
4880 l_ly_start_date := ADD_MONTHS(l_vr_termination_date, -12) + 1;
4881
4882 IF l_vr_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_LY
4883 ,pn_var_trx_pkg.G_PRORUL_FLY) THEN
4884
4885 /* init ly_proration_sales */
4886 UPDATE
4887 pn_var_trx_headers_all
4888 SET
4889 ly_proration_deductions = NULL
4890 WHERE
4891 var_rent_id = p_var_rent_id;
4892
4893 /* populate ly_proration_sales */
4894 UPDATE
4895 pn_var_trx_headers_all hdr
4896 SET
4897 hdr.ly_proration_deductions = hdr.prorated_group_deductions
4898 WHERE
4899 hdr.var_rent_id = p_var_rent_id AND
4900 hdr.calc_prd_start_date >= l_ly_start_date;
4901
4902 /* populate ly_proration_sales if LY start date does not
4903 coincide with a calc prd start date */
4904 UPDATE
4905 pn_var_trx_headers_all
4906 SET
4907 ly_proration_deductions
4908 = pn_var_trx_pkg.get_calc_prd_dedc( var_rent_id
4909 ,period_id
4910 ,line_item_id
4911 ,grp_date_id
4912 ,l_ly_start_date
4913 ,calc_prd_end_date)
4914 WHERE
4915 var_rent_id = p_var_rent_id AND
4916 l_ly_start_date BETWEEN (calc_prd_start_date + 1)
4917 AND calc_prd_end_date;
4918
4919 END IF;
4920
4921 EXCEPTION
4922 WHEN OTHERS THEN RAISE;
4923
4924 END populate_ly_pro_dedc;
4925
4926
4927 --------------------------------------------------------------------------------
4928 -- NAME : populate_fy_pro_dedc
4929 -- DESCRIPTION :
4930 -- PURPOSE :
4931 -- INVOKED FROM :
4932 -- ARGUMENTS :
4933 -- REFERENCE : PN_COMMON.debug()
4934 -- HISTORY :
4935 --
4936 -- 10/10/06 Shabda o Created
4937 --------------------------------------------------------------------------------
4938 PROCEDURE populate_fy_pro_dedc( p_var_rent_id IN NUMBER
4939 ,p_proration_rule IN VARCHAR2
4940 ,p_vr_commencement_dt IN DATE
4941 ,p_vr_termination_dt IN DATE) IS
4942
4943 /* get VR details */
4944 CURSOR vr_c(p_vr_id IN NUMBER) IS
4945 SELECT
4946 vr.var_rent_id
4947 ,vr.commencement_date
4948 ,vr.termination_date
4949 ,vr.proration_rule
4950 FROM
4951 pn_var_rents_all vr
4952 WHERE
4953 vr.var_rent_id = p_vr_id;
4954
4955 l_vr_commencement_date DATE;
4956 l_vr_termination_date DATE;
4957 l_vr_proration_rule VARCHAR2(30);
4958 l_fy_end_date DATE;
4959
4960 BEGIN
4961
4962 /* get VR details */
4963 IF p_proration_rule IS NULL OR
4964 p_vr_commencement_dt IS NULL OR
4965 p_vr_termination_dt IS NULL
4966 THEN
4967 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
4968 l_vr_commencement_date := vr_rec.commencement_date;
4969 l_vr_termination_date := vr_rec.termination_date;
4970 l_vr_proration_rule := vr_rec.proration_rule;
4971 END LOOP;
4972 ELSE
4973 l_vr_commencement_date := p_vr_commencement_dt;
4974 l_vr_termination_date := p_vr_termination_dt;
4975 l_vr_proration_rule := p_proration_rule;
4976 END IF;
4977
4978 l_fy_end_date := ADD_MONTHS(l_vr_commencement_date, 12) - 1;
4979
4980 IF l_vr_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
4981 ,pn_var_trx_pkg.G_PRORUL_FLY) THEN
4982
4983 /* init ly_proration_deductions */
4984 UPDATE
4985 pn_var_trx_headers_all
4986 SET
4987 fy_proration_deductions = NULL
4988 WHERE
4989 var_rent_id = p_var_rent_id;
4990
4991 /* populate ly_proration_deductions */
4992 UPDATE
4993 pn_var_trx_headers_all hdr
4994 SET
4995 hdr.fy_proration_deductions = hdr.prorated_group_deductions
4996 WHERE
4997 hdr.var_rent_id = p_var_rent_id AND
4998 hdr.calc_prd_end_date <= l_fy_end_date;
4999
5000 /* populate fy_proration_deductions if FY end date does not
5001 coincide with a calc prd end date */
5002 UPDATE
5003 pn_var_trx_headers_all
5004 SET
5005 fy_proration_deductions
5006 = pn_var_trx_pkg.get_calc_prd_dedc( var_rent_id
5007 ,period_id
5008 ,line_item_id
5009 ,grp_date_id
5013 var_rent_id = p_var_rent_id AND
5010 ,calc_prd_start_date
5011 ,l_fy_end_date)
5012 WHERE
5014 l_fy_end_date BETWEEN calc_prd_start_date
5015 AND (calc_prd_end_date - 1);
5016
5017 END IF;
5018
5019 EXCEPTION
5020 WHEN OTHERS THEN RAISE;
5021
5022 END populate_fy_pro_dedc;
5023
5024 --------------------------------------------------------------------------------
5025 -- NAME : populate_ytd_deductions
5026 -- DESCRIPTION :
5027 -- PURPOSE :
5028 -- INVOKED FROM :
5029 -- ARGUMENTS :
5030 -- REFERENCE : PN_COMMON.debug()
5031 -- HISTORY :
5032 --
5033 -- 10-10-06 Shabda o Created
5034 --------------------------------------------------------------------------------
5035 PROCEDURE populate_ytd_deductions( p_var_rent_id IN NUMBER
5036 ,p_proration_rule IN VARCHAR2) IS
5037
5038 /* get VR info */
5039 CURSOR vr_c(p_vr_id IN NUMBER) IS
5040 SELECT
5041 vr.var_rent_id
5042 ,vr.proration_rule
5043 FROM
5044 pn_var_rents_all vr
5045 WHERE
5046 vr.var_rent_id = p_vr_id;
5047
5048 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
5049
5050 /* get the line items with updated deductions for FY, LY, FLY, STD, NP */
5051 CURSOR lines_c(p_vr_id IN NUMBER) IS
5052 SELECT
5053 period_id
5054 ,line_item_id
5055 FROM
5056 pn_var_lines_all
5057 WHERE
5058 var_rent_id = p_vr_id
5059 ORDER BY
5060 period_id
5061 ,line_item_id;
5062
5063 /* get the line items with updated deductions for CYP, CYNP */
5064 CURSOR lines_cs_c( p_vr_id IN NUMBER
5065 ,p_part_prd_id IN NUMBER
5066 ,p_full_prd_id IN NUMBER) IS
5067 SELECT
5068 period_id
5069 ,line_item_id
5070 FROM
5071 pn_var_lines_all
5072 WHERE
5073 var_rent_id = p_vr_id AND
5074 period_id NOT IN (p_part_prd_id, p_full_prd_id)
5075 ORDER BY
5076 period_id
5077 ,line_item_id;
5078
5079 /* get the period details - we use the first 2 periods */
5080 CURSOR periods_c(p_vr_id IN NUMBER) IS
5081 SELECT
5082 period_id
5083 ,start_date
5084 ,end_date
5085 ,partial_period
5086 FROM
5087 pn_var_periods_all
5088 WHERE
5089 var_rent_id = p_vr_id
5090 ORDER BY
5091 start_date;
5092
5093 /* period info */
5094 l_part_prd_id NUMBER;
5095 l_part_prd_start_dt DATE;
5096 l_part_prd_end_dt DATE;
5097 l_part_prd_partial_flag VARCHAR2(1);
5098
5099 l_full_prd_id NUMBER;
5100 l_full_prd_start_dt DATE;
5101 l_full_prd_end_dt DATE;
5102 l_full_prd_partial_flag VARCHAR2(1);
5103
5104 /* ytd for STD, NP, FY, LY, FLY */
5105 CURSOR ytd_deductions_c( p_vr_ID IN NUMBER
5106 ,p_prd_ID IN NUMBER
5107 ,p_line_ID IN NUMBER) IS
5108 SELECT
5109 hdr.trx_header_id
5110 ,SUM(hdr.prorated_group_deductions) OVER
5111 (PARTITION BY
5112 hdr.period_id
5113 ,hdr.line_item_id
5114 ,hdr.reset_group_id
5115 ORDER BY
5116 hdr.calc_prd_start_date
5117 ROWS UNBOUNDED PRECEDING) AS ytd_deductions
5118 FROM
5119 pn_var_trx_headers_all hdr
5120 WHERE
5121 hdr.var_rent_id = p_vr_id AND
5122 hdr.period_id = p_prd_ID AND
5123 hdr.line_item_id = p_line_ID
5124 ORDER BY
5125 hdr.period_id
5126 ,hdr.line_item_id
5127 ,hdr.calc_prd_start_date;
5128
5129 /* ytd for CYP, CYNP combined period */
5130 CURSOR ytd_deductions_cs_c( p_vr_ID IN NUMBER
5131 ,p_part_prd_id IN NUMBER
5132 ,p_full_prd_id IN NUMBER) IS
5133 SELECT
5134 hdr.trx_header_id
5135 ,SUM(hdr.prorated_group_deductions) OVER
5136 (PARTITION BY
5137 hdr.line_item_group_id
5138 ORDER BY
5139 hdr.calc_prd_start_date
5140 ROWS UNBOUNDED PRECEDING) AS ytd_deductions
5141 FROM
5142 pn_var_trx_headers_all hdr
5143 WHERE
5144 hdr.var_rent_id = p_vr_id AND
5145 hdr.period_id IN (p_part_prd_id, p_full_prd_id)
5146 ORDER BY
5147 hdr.line_item_group_id
5148 ,hdr.calc_prd_start_date;
5149
5150 /* counters */
5151 l_counter1 NUMBER;
5152
5153 /* plsql tables for ytd dates and trx hdr */
5154 trx_hdr_t NUM_T;
5155 ytd_deductions_t NUM_T;
5156
5157 BEGIN
5158
5159 pnp_debug_pkg.log('++++ pn_var_trx_pkg.populate_ytd_deductions START ++++');
5160
5161 /* get VR details */
5162 IF p_proration_rule IS NULL THEN
5163
5164 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
5165
5166 l_proration_rule := vr_rec.proration_rule;
5167
5168 END LOOP;
5169
5170 ELSE
5171
5172 l_proration_rule := p_proration_rule;
5173
5174 END IF;
5175
5176 pnp_debug_pkg.log('Called with: ');
5177 pnp_debug_pkg.log(' p_var_rent_id: '||p_var_rent_id);
5178 pnp_debug_pkg.log(' l_proration_rule: '||l_proration_rule);
5179
5180 /* l_proration_rule based decisions */
5181 IF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_FY
5182 ,pn_var_trx_pkg.G_PRORUL_LY
5183 ,pn_var_trx_pkg.G_PRORUL_FLY
5184 ,pn_var_trx_pkg.G_PRORUL_NP
5185 ,pn_var_trx_pkg.G_PRORUL_STD) THEN
5186
5190 ytd_deductions_t.DELETE;
5187 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id) LOOP
5188
5189 trx_hdr_t.DELETE;
5191
5192 OPEN ytd_deductions_c( p_vr_ID => p_var_rent_id
5193 ,p_prd_ID => line_rec.period_id
5194 ,p_line_ID => line_rec.line_item_id);
5195
5196 FETCH ytd_deductions_c BULK COLLECT INTO
5197 trx_hdr_t
5198 ,ytd_deductions_t;
5199
5200 CLOSE ytd_deductions_c;
5201
5202 FORALL i IN 1..trx_hdr_t.COUNT
5203 UPDATE
5204 pn_var_trx_headers_all
5205 SET
5206 ytd_deductions = ytd_deductions_t(i)
5207 WHERE
5208 trx_header_id = trx_hdr_t(i);
5209
5210 END LOOP;
5211
5212 ELSIF l_proration_rule IN ( pn_var_trx_pkg.G_PRORUL_CYP
5213 ,pn_var_trx_pkg.G_PRORUL_CYNP) THEN
5214
5215 /* fetch partial and full period details */
5216 l_counter1 := 0;
5217 FOR prd_rec IN periods_c(p_vr_id => p_var_rent_id) LOOP
5218
5219 l_counter1 := l_counter1 + 1;
5220
5221 IF l_counter1 = 1 THEN
5222 l_part_prd_id := prd_rec.period_id;
5223 l_part_prd_start_dt := prd_rec.start_date;
5224 l_part_prd_end_dt := prd_rec.end_date;
5225 l_part_prd_partial_flag := prd_rec.partial_period;
5226
5227 ELSIF l_counter1 = 2 THEN
5228 l_full_prd_id := prd_rec.period_id;
5229 l_full_prd_start_dt := prd_rec.start_date;
5230 l_full_prd_end_dt := prd_rec.end_date;
5231 l_full_prd_partial_flag := prd_rec.partial_period;
5232
5233 ELSE
5234 EXIT;
5235
5236 END IF;
5237
5238 END LOOP; /* fetch partial and full period details */
5239
5240 trx_hdr_t.DELETE;
5241 ytd_deductions_t.DELETE;
5242
5243 OPEN ytd_deductions_cs_c( p_vr_ID => p_var_rent_id
5244 ,p_part_prd_id => l_part_prd_id
5245 ,p_full_prd_id => l_full_prd_id);
5246
5247 FETCH ytd_deductions_cs_c BULK COLLECT INTO
5248 trx_hdr_t
5249 ,ytd_deductions_t;
5250
5251 CLOSE ytd_deductions_cs_c;
5252
5253 FORALL i IN 1..trx_hdr_t.COUNT
5254 UPDATE
5255 pn_var_trx_headers_all
5256 SET
5257 ytd_deductions = ytd_deductions_t(i)
5258 WHERE
5259 trx_header_id = trx_hdr_t(i);
5260
5261 /* loop for all lines */
5262 FOR line_rec IN lines_cs_c ( p_vr_id => p_var_rent_id
5263 ,p_part_prd_id => l_part_prd_id
5264 ,p_full_prd_id => l_full_prd_id)
5265 LOOP
5266
5267 trx_hdr_t.DELETE;
5268 ytd_deductions_t.DELETE;
5269
5270 OPEN ytd_deductions_c( p_vr_ID => p_var_rent_id
5271 ,p_prd_ID => line_rec.period_id
5272 ,p_line_ID => line_rec.line_item_id);
5273
5274 FETCH ytd_deductions_c BULK COLLECT INTO
5275 trx_hdr_t
5276 ,ytd_deductions_t;
5277
5278 CLOSE ytd_deductions_c;
5279
5280 FORALL i IN 1..trx_hdr_t.COUNT
5281 UPDATE
5282 pn_var_trx_headers_all
5283 SET
5284 ytd_deductions = ytd_deductions_t(i)
5285 WHERE
5286 trx_header_id = trx_hdr_t(i);
5287
5288 END LOOP; /* loop for all lines */
5289
5290 END IF; /* l_proration_rule based decisions */
5291
5292 EXCEPTION
5293 WHEN OTHERS THEN RAISE;
5294
5295 END populate_ytd_deductions;
5296
5297 --------------------------------------------------------------------------------
5298 -- NAME : populate_deductions
5299 -- DESCRIPTION :
5300 -- PURPOSE :
5301 -- INVOKED FROM :
5302 -- ARGUMENTS :
5303 -- REFERENCE : PN_COMMON.debug()
5304 -- HISTORY :
5305 --
5306 -- 10/10/06 Shabda o Created
5307 --------------------------------------------------------------------------------
5308 PROCEDURE populate_deductions(p_var_rent_id IN NUMBER) IS
5309
5310 /* get VR info */
5311 CURSOR vr_c(p_vr_id IN NUMBER) IS
5312 SELECT
5313 vr.org_id
5314 ,vr.var_rent_id
5315 ,vr.commencement_date
5316 ,vr.termination_date
5317 ,vr.proration_rule
5318 ,vr.cumulative_vol
5319 FROM
5320 pn_var_rents_all vr
5321 WHERE
5322 vr.var_rent_id = p_vr_id;
5323
5324 l_org_id pn_var_rents_all.org_id%TYPE;
5325 l_vr_commencement_date pn_var_rents_all.commencement_date%TYPE;
5326 l_vr_termination_date pn_var_rents_all.termination_date%TYPE;
5327 l_proration_rule pn_var_rents_all.proration_rule%TYPE;
5328 l_calculation_method pn_var_rents_all.cumulative_vol%TYPE;
5329
5330 /* get the line items*/
5331 CURSOR lines_c(p_vr_id IN NUMBER) IS
5332 SELECT
5333 period_id
5334 ,line_item_id
5335 FROM
5336 pn_var_lines_all
5337 WHERE
5338 var_rent_id = p_vr_id
5339 ORDER BY
5340 period_id
5341 ,line_item_id;
5342
5343 /* get the calc periods to update deductions data */
5344 CURSOR calc_periods_c( p_vr_id IN NUMBER
5345 ,p_prd_id IN NUMBER
5346 ,p_line_id IN NUMBER) IS
5347 SELECT
5348 hdr.trx_header_id
5349 ,hdr.var_rent_id
5350 ,hdr.period_id
5351 ,hdr.line_item_id
5352 ,hdr.grp_date_id
5353 ,hdr.calc_prd_start_date
5354 ,hdr.calc_prd_end_date
5355 FROM
5356 pn_var_trx_headers_all hdr
5357 WHERE
5358 hdr.var_rent_id = p_vr_id AND
5362 hdr.period_id
5359 hdr.period_id = p_prd_id AND
5360 hdr.line_item_id = p_line_id
5361 ORDER BY
5363 ,hdr.line_item_id
5364 ,hdr.calc_prd_start_date
5365 ,hdr.calc_prd_end_date;
5366
5367 /* data structures */
5368 trx_hdr_t NUM_T;
5369 reporting_grp_dedc_t NUM_T;
5370 prorate_grp_dedc_t NUM_T;
5371
5372 l_counter NUMBER;
5373
5374 /* flags */
5375
5376 l_line_items_t NUM_T;
5377
5378 BEGIN
5379 l_line_items_t.DELETE;
5380
5381 OPEN line_items_c(p_vr_id => p_var_rent_id);
5382 FETCH line_items_c BULK COLLECT INTO l_line_items_t;
5383 CLOSE line_items_c;
5384
5385 /* get the VR details */
5386 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
5387
5388 l_org_id := vr_rec.org_id;
5389 l_vr_commencement_date := vr_rec.commencement_date;
5390 l_vr_termination_date := vr_rec.termination_date;
5391 l_proration_rule := vr_rec.proration_rule;
5392 l_calculation_method := vr_rec.cumulative_vol;
5393
5394 END LOOP;
5395
5396 /* for all line items */
5397 FOR line_rec IN lines_c(p_vr_id => p_var_rent_id)
5398 LOOP
5399 l_counter := 0;
5400 trx_hdr_t.DELETE;
5401 reporting_grp_dedc_t.DELETE;
5402 prorate_grp_dedc_t.DELETE;
5403
5404 /* for all calc sub periods for the line item, get the prorated deductions */
5405 FOR trx_rec IN calc_periods_c( p_vr_id => p_var_rent_id
5406 ,p_prd_id => line_rec.period_id
5407 ,p_line_id => line_rec.line_item_id)
5408 LOOP
5409
5410 l_counter := l_counter + 1;
5411
5412 trx_hdr_t(l_counter) := trx_rec.trx_header_id;
5413
5414 pn_var_trx_pkg.get_calc_prd_dedc
5415 ( p_var_rent_id => trx_rec.var_rent_id
5416 ,p_period_id => trx_rec.period_id
5417 ,p_line_item_id => trx_rec.line_item_id
5418 ,p_grp_date_id => trx_rec.grp_date_id
5419 ,p_start_date => trx_rec.calc_prd_start_date
5420 ,p_end_date => trx_rec.calc_prd_end_date
5421 ,x_pro_dedc => prorate_grp_dedc_t(l_counter)
5422 ,x_dedc => reporting_grp_dedc_t(l_counter));
5423
5424 END LOOP;
5425
5426 /* for all calc sub periods for the line item,
5427 update the trx headers with the deductions */
5428 IF trx_hdr_t.COUNT > 0 THEN
5429
5430 FORALL i IN trx_hdr_t.FIRST..trx_hdr_t.LAST
5431 UPDATE
5432 pn_var_trx_headers_all
5433 SET
5434 reporting_group_deductions = reporting_grp_dedc_t(i)
5435 ,prorated_group_deductions = prorate_grp_dedc_t(i)
5436 WHERE
5437 trx_header_id = trx_hdr_t(i);
5438
5439 END IF;
5440
5441 END LOOP;
5442
5443
5444 IF l_proration_rule = pn_var_trx_pkg.G_PRORUL_LY THEN
5445
5446 pn_var_trx_pkg.populate_ly_pro_dedc
5447 ( p_var_rent_id => p_var_rent_id
5448 ,p_proration_rule => l_proration_rule
5449 ,p_vr_commencement_dt => l_vr_commencement_date
5450 ,p_vr_termination_dt => l_vr_termination_date);
5451
5452 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FY THEN
5453
5454 pn_var_trx_pkg.populate_fy_pro_dedc
5455 ( p_var_rent_id => p_var_rent_id
5456 ,p_proration_rule => l_proration_rule
5457 ,p_vr_commencement_dt => l_vr_commencement_date
5458 ,p_vr_termination_dt => l_vr_termination_date);
5459
5460 ELSIF l_proration_rule = pn_var_trx_pkg.G_PRORUL_FLY THEN
5461
5462 pn_var_trx_pkg.populate_ly_pro_dedc
5463 ( p_var_rent_id => p_var_rent_id
5464 ,p_proration_rule => l_proration_rule
5465 ,p_vr_commencement_dt => l_vr_commencement_date
5466 ,p_vr_termination_dt => l_vr_termination_date);
5467
5468 pn_var_trx_pkg.populate_fy_pro_dedc
5469 ( p_var_rent_id => p_var_rent_id
5470 ,p_proration_rule => l_proration_rule
5471 ,p_vr_commencement_dt => l_vr_commencement_date
5472 ,p_vr_termination_dt => l_vr_termination_date);
5473
5474 END IF;
5475
5476 /* always populate YTD deductions - Because we always populate YTD sales */
5477 pn_var_trx_pkg.populate_ytd_deductions
5478 ( p_var_rent_id => p_var_rent_id
5479 ,p_proration_rule => l_proration_rule);
5480
5481 EXCEPTION
5482 WHEN OTHERS THEN RAISE;
5483
5484 END populate_deductions;
5485
5486
5487
5488
5489 END pn_var_trx_pkg;