1 PACKAGE BODY PN_VAR_TRUEUP_PKG AS
2 -- $Header: PNVRTRPB.pls 120.0 2007/10/03 14:29:47 rthumma noship $
3
4 TYPE two_nums_rec IS RECORD(
5 period_id NUMBER,
6 amount NUMBER);
7 TYPE NUM_T IS TABLE OF TWO_NUMS_REC INDEX BY BINARY_INTEGER;
8 G_ABATEMENT_APPLIED NUM_T;
9 G_ALLOWANCE_APPLIED NUM_T;
10 G_UNABATED_RENT NUM_T;
11 G_ABATED_RENT NUM_T;
12 G_TOT_ABATEMENT NUM_T;
13 G_IS_TU_CONC_FLAG VARCHAR2(1) := 'T'; /*Is this called as a result of calculate or of true up?*/
14 g_precision NUMBER;
15
16 --------------------------------------------------------------------------------
17 -- NAME : can_do_trueup
18 -- DESCRIPTION :
19 -- PURPOSE :
20 -- INVOKED FROM :
21 -- ARGUMENTS :
22 -- REFERENCE : PN_COMMON.debug()
23 -- HISTORY :
24 -- dd-mon-yyyy name o Created
25 --------------------------------------------------------------------------------
26 FUNCTION can_do_trueup( p_var_rent_id IN NUMBER
27 ,p_period_id IN NUMBER)
28 RETURN BOOLEAN IS
29
30 l_exists_trx BOOLEAN;
31 l_exists_approved_sales BOOLEAN;
32
33 /* check if trx exists */
34 CURSOR trx_exists_c( p_vr_id IN NUMBER
35 ,p_prd_id IN NUMBER) IS
36 SELECT 1 FROM DUAL WHERE EXISTS
37 (SELECT
38 trx_header_id
39 FROM
40 pn_var_trx_headers_all
41 WHERE
42 var_rent_id = p_vr_id AND
43 period_id = p_prd_id AND
44 reporting_group_sales IS NOT NULL);
45
46 /* exists approved sales? */
47 CURSOR approved_sales_c( p_vr_id IN NUMBER
48 ,p_prd_id IN NUMBER) IS
49 SELECT
50 invoice_date
51 FROM
52 pn_var_grp_dates_all g,
53 pn_var_periods_all p
54 WHERE
55 g.var_rent_id = p_vr_id AND
56 g.period_id = p_prd_id AND
57 g.period_id = p.period_id AND
58 g.grp_end_date <= p.end_date
59 MINUS
60 SELECT
61 g.invoice_date
62 FROM
63 pn_var_trx_headers_all t,
64 pn_var_grp_dates_all g,
65 pn_var_periods_all p
66 WHERE
67 t.grp_date_id = g.grp_date_id AND
68 t.var_rent_id = p_vr_id AND
69 t.period_id = p_prd_id AND
70 t.period_id = p.period_id AND
71 g.grp_end_date <= p.end_date AND
72 t.reporting_group_sales IS NOT NULL;
73
74 BEGIN
75
76 l_exists_approved_sales := FALSE;
77 l_exists_trx := FALSE;
78
79 FOR rec IN trx_exists_c( p_vr_id => p_var_rent_id
80 ,p_prd_id => p_period_id) LOOP
81
82 l_exists_approved_sales := TRUE;
83 l_exists_trx := TRUE;
84
85 EXIT;
86
87 END LOOP;
88
89 IF l_exists_trx THEN
90
91 FOR rec IN approved_sales_c( p_vr_id => p_var_rent_id
92 ,p_prd_id => p_period_id) LOOP
93
94 l_exists_approved_sales := FALSE;
95 EXIT;
96
97 END LOOP;
98
99 END IF;
100
101 RETURN l_exists_approved_sales;
102
103 EXCEPTION
104 WHEN OTHERS THEN RAISE;
105
106 END can_do_trueup;
107
108 --------------------------------------------------------------------------------
109 -- NAME : post_summary_trueup - global procedure
110 -- DESCRIPTION :
111 -- PURPOSE :
112 -- INVOKED FROM :
113 -- ARGUMENTS :
114 -- REFERENCE : PN_COMMON.debug()
115 -- HISTORY :
116 --
117 -- dd-mon-yyyy name o Created
118 -- 23-MAY-2007 Lokesh o Added rounding off for Bug # 6031202 for
119 -- trueup_var_rent
120 --------------------------------------------------------------------------------
121 PROCEDURE post_summary_trueup ( p_var_rent_id IN NUMBER
122 ,p_period_id IN NUMBER
123 ,p_proration_rule IN VARCHAR2)
124 IS
125
126 /* get all lines to post summary for */
127 CURSOR trueup_rent_c( p_vr_id IN NUMBER
128 ,p_prd_id IN NUMBER) IS
129 SELECT
130 NVL(SUM(percent_rent_due),0) AS billed_rent
131 ,NVL(SUM(trueup_rent_due),0) AS trueup_rent
132 ,MAX(calc_prd_end_date) AS trueup_date
133 ,line_item_id
134 FROM
135 pn_var_trx_headers_all
136 WHERE
137 var_rent_id = p_vr_id AND
138 period_id = p_prd_id
139 GROUP BY
140 line_item_id
141 ORDER BY
142 line_item_id;
143
144 /* get the billed rent for first partial period */
145 CURSOR first_period_c( p_vr_id IN NUMBER) IS
146 SELECT
147 NVL(SUM(percent_rent_due),0) AS billed_rent
148 ,line_item_id
149 FROM
150 pn_var_periods_all prd,
151 pn_var_rents_all var,
152 pn_var_trx_headers_all trx
153 WHERE
154 prd.var_rent_id = p_vr_id AND
155 prd.var_rent_id = var.var_rent_id AND
156 prd.start_date = var.commencement_date AND
157 trx.var_rent_id = prd.var_rent_id AND
158 trx.period_id = prd.period_id
159 GROUP BY
160 trx.line_item_id
161 ORDER BY
162 trx.line_item_id;
163
164 /* Fetch the second year period id */
165 CURSOR second_yr_cur (p_vr_id IN NUMBER) IS
166 SELECT
167 period_id
168 FROM
169 pn_var_periods_all prd
170 WHERE
171 prd.var_rent_id = p_vr_id AND
172 prd.period_num = 2;
173
174
175 l_vr_summ_id NUMBER;
176 l_billed_rent_fst_yr NUMBER := 0;
177 l_billed_rent NUMBER := 0;
178 l_second_yr_id NUMBER;
179
180 BEGIN
181
182 pnp_debug_pkg.log('post_summary_trueup (+) .... ');
183
184 FOR trueup_rec IN trueup_rent_c( p_vr_id => p_var_rent_id
185 ,p_prd_id => p_period_id)
186 LOOP
187
188 l_billed_rent := trueup_rec.billed_rent;
189 pnp_debug_pkg.log('l_billed_rent:'||l_billed_rent);
190 /* Note:
191 Handling to take the rent for first year in account
192 while calculating the true up for second year in CYNP
193 and CYP */
194 IF p_proration_rule IN (pn_var_rent_calc_pkg.G_PRORUL_CYP
195 ,pn_var_rent_calc_pkg.G_PRORUL_CYNP) THEN
196
197
198 /* Fetch the details for the first partial year */
199 FOR first_period_rec IN first_period_c(p_vr_id => p_var_rent_id) LOOP
200 l_billed_rent_fst_yr := first_period_rec.billed_rent;
201 END LOOP;
202
203 /* Fetch the period id for second period */
204 FOR second_yr_rec IN second_yr_cur(p_vr_id => p_var_rent_id) LOOP
205 l_second_yr_id := second_yr_rec.period_id;
206 END LOOP;
207
208 IF l_second_yr_id = p_period_id THEN
209 l_billed_rent := l_billed_rent + l_billed_rent_fst_yr;
210 END IF;
211
212 END IF;
213
214 pnp_debug_pkg.log('l_billed_rent:'||l_billed_rent);
215 pnp_debug_pkg.log('trup_rent:'||trueup_rec.trueup_rent);
216
217 UPDATE
218 pn_var_rent_summ_all
219 SET
220 trueup_var_rent = 0
221 WHERE
222 var_rent_id = p_var_rent_id AND
223 period_id = p_period_id AND
224 line_item_id = trueup_rec.line_item_id;
225
226 UPDATE
227 pn_var_rent_summ_all
228 SET
229 trueup_var_rent = round((trueup_rec.trueup_rent - l_billed_rent), g_precision)
230 WHERE
231 var_rent_id = p_var_rent_id AND
232 period_id = p_period_id AND
233 line_item_id = trueup_rec.line_item_id AND
234 grp_date_id = (SELECT
235 grp_date_id
236 FROM
237 pn_var_grp_dates_all
238 WHERE
239 period_id = p_period_id AND
240 grp_end_date = trueup_rec.trueup_date)
241 RETURNING
242 var_rent_summ_id
243 INTO
244 l_vr_summ_id;
245
246 END LOOP;
247
248 EXCEPTION
249 WHEN OTHERS THEN RAISE;
250
251 END post_summary_trueup;
252
253 --------------------------------------------------------------------------------
254 -- NAME : insert_invoice_trueup
255 -- DESCRIPTION :
256 -- PURPOSE :
257 -- INVOKED FROM :
258 -- ARGUMENTS :
259 -- REFERENCE : PN_COMMON.debug()
260 -- HISTORY :
261 --
262 -- dd-mon-yyyy name o Created
263 --------------------------------------------------------------------------------
264 PROCEDURE insert_invoice_trueup( p_var_rent_id IN NUMBER
265 ,p_period_id IN NUMBER) IS
266
267 /* get invoice dates for a period */
268 CURSOR trueup_inv_dates_c( p_vr_id IN NUMBER
269 ,p_prd_id IN NUMBER
270 ,p_new_termn_date DATE) IS
271 SELECT
272 MAX(invoice_date) AS trueup_invoice_date
273 ,NVL(SUM(tot_act_vol), 0) AS total_actual_sales
274 ,NVL(SUM(trueup_var_rent), 0) AS total_trueup_rent
275 FROM
276 pn_var_rent_summ_all
277 WHERE
278 var_rent_id = p_vr_id AND
279 period_id = p_prd_id AND
280 invoice_date <= p_new_termn_date;
281
282 /* get latest invoice */
283 CURSOR trueup_invoice2upd_c( p_vr_id IN NUMBER
284 ,p_prd_id IN NUMBER
285 ,p_inv_dt IN DATE) IS
286 SELECT
287 var_rent_inv_id
288 ,var_rent_id
289 ,period_id
290 ,invoice_date
291 ,adjust_num
292 ,true_up_status
293 ,true_up_exp_code
294 ,true_up_amt
295 ,tot_act_vol
296 ,act_per_rent
297 ,actual_invoiced_amount
298 ,rec_abatement_override
299 FROM
300 pn_var_rent_inv_all
301 WHERE
302 var_rent_id = p_vr_id AND
303 period_id = p_prd_id AND
304 invoice_date = p_inv_dt AND
305 /*true_up_amt <> 0 AND*/
306 true_up_status IS NOT NULL AND
307 true_up_exp_code IS NOT NULL
308 ORDER BY adjust_num DESC;
309
310 /* get the period rent */
311 CURSOR period_rent_c ( p_vr_id IN NUMBER
312 ,p_prd_id IN NUMBER) IS
313 SELECT
314 NVL(SUM(act_var_rent), 0) AS total_period_rent
315 ,NVL(SUM(trueup_var_rent), 0) AS total_trueup_rent
316 FROM
317 pn_var_rent_summ_all
318 WHERE
319 var_rent_id = p_vr_id AND
320 period_id = p_prd_id;
321
322 l_total_period_rent NUMBER;
323 l_total_period_unabt_rent NUMBER;
324
325 /* get latest invoice */
326 CURSOR prev_invoiced_c( p_vr_id IN NUMBER
327 ,p_prd_id IN NUMBER) IS
328 SELECT
329 NVL(SUM(actual_invoiced_amount), 0) AS prev_inv_trueup_amt
330 FROM
331 pn_var_rent_inv_all
332 WHERE
333 var_rent_id = p_vr_id AND
334 period_id = p_prd_id AND
335 (true_up_amt IS NULL OR true_up_exp_code = 'Y');
336
337 l_invoice_on VARCHAR2(30);
338 l_row_id ROWID;
339 l_var_rent_inv_id NUMBER;
340 l_max_adjust_num NUMBER;
341 l_prev_inv_trueup_rent NUMBER;
342 l_curr_inv_trueup_rent NUMBER;
343 l_rec_abatement_override NUMBER;
344 /* get ORG ID */
345 CURSOR org_c(p_vr_id IN NUMBER) IS
346 SELECT org_id, termination_date
347 FROM pn_var_rents_all
348 WHERE var_rent_id = p_vr_id;
349
350 l_org_id NUMBER;
351
352
353
354 l_exists_invoice BOOLEAN;
355 l_abated_rent NUMBER;
356 l_tot_abatement NUMBER;
357 l_allowance NUMBER;
358 l_precision NUMBER;
359 l_tot_period_rent NUMBER := NULL;
360 l_vr_termination_date DATE;
361
362 BEGIN
363 pnp_debug_pkg.log('+++++++Insert_invoice_trueup++++++++');
364 FOR vr_rec IN org_c(p_vr_id => p_var_rent_id) LOOP
365 l_org_id := vr_rec.org_id;
366 l_vr_termination_date := vr_rec.termination_date;
367 END LOOP;
368
369 l_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(l_org_id),4);
370 pnp_debug_pkg.log('l_precision:'||l_precision);
371 FOR i IN 1..G_ABATED_RENT.COUNT LOOP
372 IF G_ABATED_RENT(i).period_id = p_period_id THEN
373 l_total_period_rent := G_ABATED_RENT(i).AMOUNT;
374 EXIT;
375 END IF;
376
377 END LOOP;
378
379 FOR i IN 1..G_UNABATED_RENT.COUNT LOOP
380 IF G_UNABATED_RENT(i).period_id = p_period_id THEN
381 l_total_period_unabt_rent := G_UNABATED_RENT(i).AMOUNT;
382 EXIT;
383 END IF;
384 END LOOP;
385
386 --
387 FOR i IN 1..G_TOT_ABATEMENT.COUNT LOOP
388 IF G_TOT_ABATEMENT(i).period_id = p_period_id THEN
389 l_tot_abatement := G_TOT_ABATEMENT(i).AMOUNT;
390 EXIT;
391 END IF;
392 END LOOP;
393
394 FOR i IN 1..G_ALLOWANCE_APPLIED.COUNT LOOP
395 IF G_ALLOWANCE_APPLIED(i).period_id = p_period_id THEN
396 l_allowance := G_ALLOWANCE_APPLIED(i).AMOUNT;
397 EXIT;
398 END IF;
399 END LOOP;
400
401 pnp_debug_pkg.log('s** l_total_period_rent'||l_total_period_rent);
402 /* FOR summ_rec IN period_rent_c ( p_vr_id => p_var_rent_id
403 ,p_prd_id => p_period_id)
404 LOOP
405 l_total_period_rent
406 := summ_rec.total_period_rent + summ_rec.total_trueup_rent;
407 END LOOP;
408 */
409
410
411
412 /* loop for all invoice dates in the period */
413 FOR inv_rec IN trueup_inv_dates_c( p_vr_id => p_var_rent_id
414 ,p_prd_id => p_period_id
415 ,p_new_termn_date => l_vr_termination_date)
416 LOOP
417
418 l_row_id := NULL;
419 l_var_rent_inv_id := NULL;
420 l_max_adjust_num := 0;
421 l_prev_inv_trueup_rent := 0;
422 l_curr_inv_trueup_rent := 0;
423 l_exists_invoice := FALSE;
424
425
426 /* check if there exists an invoice for this invoice date */
427 FOR inv2upd_rec IN trueup_invoice2upd_c ( p_vr_id => p_var_rent_id
428 ,p_prd_id => p_period_id
429 ,p_inv_dt => inv_rec.trueup_invoice_date)
430 LOOP
431
432 /* invoice exists - we only look at the last invoice */
433 l_exists_invoice := TRUE;
434 l_rec_abatement_override := inv2upd_rec.rec_abatement_override;
435 /* invoice updateable? */
436 IF NVL(inv2upd_rec.true_up_exp_code, 'N') <> 'Y' THEN
437
438 /* updateable */
439 l_var_rent_inv_id := inv2upd_rec.var_rent_inv_id;
440 l_max_adjust_num := inv2upd_rec.adjust_num;
441 l_curr_inv_trueup_rent := inv2upd_rec.actual_invoiced_amount;
442 ELSIF NVL(inv2upd_rec.true_up_exp_code, 'N') = 'Y' THEN
443
444 /* NON - updateable */
445 l_var_rent_inv_id := NULL;
446 l_max_adjust_num := inv2upd_rec.adjust_num + 1;
447 l_curr_inv_trueup_rent := 0;
448 l_tot_period_rent := inv2upd_rec.act_per_rent;
449
450 END IF; /* invoice updateable? */
451
452 /* we only look at the last invoice - important to exit here */
453 EXIT;
454
455 END LOOP; /* check if there exists an invoice for this invoice date */
456
457
458 /* get the previously billed amount from approved invoices */
459 FOR prev_inv_rec IN prev_invoiced_c( p_vr_id => p_var_rent_id
460 ,p_prd_id => p_period_id)
461 LOOP
462 l_prev_inv_trueup_rent := prev_inv_rec.prev_inv_trueup_amt;
463 END LOOP;
464
465 /* atleast one invoice exists? */
466 IF NOT l_exists_invoice AND
467 (round(l_total_period_rent, l_precision)- round(l_prev_inv_trueup_rent, l_precision)) <> 0 THEN
468
469 /* first time for this invoice date - create invoice */
470 pn_var_rent_inv_pkg.insert_row
471 ( x_rowid => l_row_id
472 ,x_var_rent_inv_id => l_var_rent_inv_id
473 ,x_adjust_num => l_max_adjust_num
474 ,x_invoice_date => inv_rec.trueup_invoice_date
475 ,x_for_per_rent => NULL
476 ,x_tot_act_vol => inv_rec.total_actual_sales
477 ,x_act_per_rent => l_total_period_unabt_rent
478 ,x_constr_actual_rent => l_total_period_unabt_rent
479 ,x_abatement_appl => l_allowance
480 ,x_rec_abatement => l_tot_abatement
481 ,x_rec_abatement_override => l_rec_abatement_override
482 ,x_negative_rent => 0
483 ,x_actual_invoiced_amount => l_total_period_rent - l_prev_inv_trueup_rent
484 ,x_period_id => p_period_id
485 ,x_var_rent_id => p_var_rent_id
486 ,x_forecasted_term_status => 'N'
487 ,x_variance_term_status => 'N'
488 ,x_actual_term_status => 'N'
489 ,x_forecasted_exp_code => 'N'
490 ,x_variance_exp_code => 'N'
491 ,x_actual_exp_code => 'N'
492 ,x_comments => 'created invoice'
493 ,x_attribute_category => NULL
494 ,x_attribute1 => NULL
495 ,x_attribute2 => NULL
496 ,x_attribute3 => NULL
497 ,x_attribute4 => NULL
498 ,x_attribute5 => NULL
499 ,x_attribute6 => NULL
500 ,x_attribute7 => NULL
501 ,x_attribute8 => NULL
502 ,x_attribute9 => NULL
503 ,x_attribute10 => NULL
504 ,x_attribute11 => NULL
505 ,x_attribute12 => NULL
506 ,x_attribute13 => NULL
507 ,x_attribute14 => NULL
508 ,x_attribute15 => NULL
509 ,x_creation_date => SYSDATE
510 ,x_created_by => NVL(fnd_global.user_id,0)
511 ,x_last_update_date => SYSDATE
512 ,x_last_updated_by => NVL(fnd_global.user_id,0)
513 ,x_last_update_login => NVL(fnd_global.login_id,0)
514 ,x_true_up_amount => l_total_period_rent - l_prev_inv_trueup_rent
515 ,x_true_up_status => 'N'
516 ,x_true_up_exp_code => 'N'
517 ,x_org_id => l_org_id );
518
519 ELSIF l_exists_invoice THEN
520
521 /* no invoice to update - create a new one */
522 IF l_var_rent_inv_id IS NULL THEN
523
524 /* if there a change in rent */
525 IF ((round(l_total_period_rent, l_precision) - round(l_prev_inv_trueup_rent, l_precision)) <> 0
526 OR round(l_total_period_rent, l_precision) <> round(l_tot_period_rent, l_precision)) THEN
527
528 /* create new invoice for difference amt */
529 pn_var_rent_inv_pkg.insert_row
530 ( x_rowid => l_row_id
531 ,x_var_rent_inv_id => l_var_rent_inv_id
532 ,x_adjust_num => l_max_adjust_num
533 ,x_invoice_date => inv_rec.trueup_invoice_date
534 ,x_for_per_rent => NULL
535 ,x_tot_act_vol => inv_rec.total_actual_sales
536 ,x_act_per_rent => l_total_period_unabt_rent
537 ,x_constr_actual_rent => l_total_period_unabt_rent
538 ,x_abatement_appl => l_allowance
539 ,x_rec_abatement => l_tot_abatement
540 ,x_rec_abatement_override => l_rec_abatement_override
541 ,x_negative_rent => 0
542 ,x_actual_invoiced_amount => (l_total_period_rent - l_prev_inv_trueup_rent)
543 ,x_period_id => p_period_id
544 ,x_var_rent_id => p_var_rent_id
545 ,x_forecasted_term_status => 'N'
546 ,x_variance_term_status => 'N'
547 ,x_actual_term_status => 'N'
548 ,x_forecasted_exp_code => 'N'
549 ,x_variance_exp_code => 'N'
550 ,x_actual_exp_code => 'N'
551 ,x_comments => 'created invoice'
552 ,x_attribute_category => NULL
553 ,x_attribute1 => NULL
554 ,x_attribute2 => NULL
555 ,x_attribute3 => NULL
556 ,x_attribute4 => NULL
557 ,x_attribute5 => NULL
558 ,x_attribute6 => NULL
559 ,x_attribute7 => NULL
560 ,x_attribute8 => NULL
561 ,x_attribute9 => NULL
562 ,x_attribute10 => NULL
563 ,x_attribute11 => NULL
564 ,x_attribute12 => NULL
565 ,x_attribute13 => NULL
566 ,x_attribute14 => NULL
567 ,x_attribute15 => NULL
568 ,x_creation_date => SYSDATE
569 ,x_created_by => NVL(fnd_global.user_id,0)
570 ,x_last_update_date => SYSDATE
571 ,x_last_updated_by => NVL(fnd_global.user_id,0)
572 ,x_last_update_login => NVL(fnd_global.login_id,0)
573 ,x_true_up_amount => (l_total_period_rent - l_prev_inv_trueup_rent)
574 ,x_true_up_status => 'N'
575 ,x_true_up_exp_code => 'N'
576 ,x_org_id => l_org_id );
577
578 END IF; /* IF inv_rec.total_actual_rent <> l_prev_inv_trueup_rent THEN */
579
580 ELSIF l_var_rent_inv_id IS NOT NULL THEN
581
582 /* if there a change in rent */
583 IF (round(l_total_period_rent, l_precision) - round(l_prev_inv_trueup_rent, l_precision)) <> round(l_curr_inv_trueup_rent, l_precision)
584 THEN
585
586 DELETE
587 pn_payment_terms_all
588 WHERE
589 var_rent_inv_id = l_var_rent_inv_id AND
590 status <> pn_var_rent_calc_pkg.G_TERM_STATUS_APPROVED;
591
592 /* update the invoice */
593 UPDATE
594 pn_var_rent_inv_all
595 SET
596 act_per_rent = l_total_period_unabt_rent
597 ,constr_actual_rent = l_total_period_unabt_rent
598 ,actual_invoiced_amount = (l_total_period_rent - l_prev_inv_trueup_rent)
599 ,true_up_amt = (l_total_period_rent - l_prev_inv_trueup_rent)
600 ,tot_act_vol = ROUND(inv_rec.total_actual_sales, l_precision) -- bug # 6007571
601 ,actual_term_status = 'N'
602 ,abatement_appl = l_allowance
603 ,rec_abatement = l_tot_abatement
604 ,last_update_date = SYSDATE
605 ,last_updated_by = NVL(fnd_global.user_id,0)
606 ,last_update_login = NVL(fnd_global.login_id,0)
607 WHERE
608 var_rent_inv_id = l_var_rent_inv_id;
609
610 END IF; /* if there a change in rent */
611
612 END IF; /* IF l_var_rent_inv_id IS NULL THEN */
613
614 END IF; /* IF NOT l_exists_invoice THEN */
615
616 END LOOP; /* loop for all invoice dates in the period */
617
618 EXCEPTION
619 WHEN OTHERS THEN RAISE;
620
621 END insert_invoice_trueup;
622
623 /*Procedures to calculate true_up abatements.*/
624
625 --------------------------------------------------------------------------------
626 -- NAME : apply_abatements
627 -- DESCRIPTION : Applies abatements to given periods of a specific VR
628 -- PURPOSE : Applies abatements.
629 -- INVOKED FROM : calculate_trueup
630 -- ARGUMENTS : p_var_rent_id: Vr to apply abatements for.
631 -- p_period_id: Period to calculate for.
632 -- p_flag: If calculate then actual_invoiced amount is
633 -- updated.
634 -- REFERENCE : PN_COMMON.debug()
635 -- HISTORY :
636 -- 25/Nov/06 Shabda Created
637 -- 4/Nov/07 o Shabda fix for bug 5724597. Modified so we now get the true up
638 -- rent based on constr_actual_rent and subtrach the non cumulative
639 -- values to get the TU amount.
640 --------------------------------------------------------------------------------
641 PROCEDURE apply_abatements(p_var_rent_id IN NUMBER,
642 p_period_id IN NUMBER,
643 p_flag IN VARCHAR2)
644 IS
645 -- Allowances first ot abatements?
646 CURSOR order_c(ip_var_rent_id NUMBER) IS
647 SELECT ORDER_OF_APPL_CODE, termination_date, org_id
648 FROM PN_VAR_RENTS_ALL abat
649 WHERE abat.var_rent_id = ip_var_rent_id;
650
651 -- Get the details of
652 CURSOR inv_c( ip_var_rent_id NUMBER,
653 ip_period_id NUMBER,
654 p_new_termn_date DATE
655 ) IS
656 SELECT * FROM
657 (SELECT var_rent_inv_id, constr_actual_rent, true_up_amt,
658 true_up_exp_code, invoice_date
659 FROM pn_var_rent_inv_all inv1
660 WHERE inv1.var_rent_id = ip_var_rent_id
661 AND inv1.period_id = ip_period_id
662 AND inv1.invoice_date <= p_new_termn_date
663 AND inv1.adjust_num= (
664 SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
665 WHERE inv1.var_rent_id = inv2.var_rent_id
666 AND inv1.period_id = inv2.period_id
667 AND inv1.invoice_date = inv2.invoice_date)
668 AND TRUE_UP_AMT IS NULL
669 ORDER BY INVOICE_DATE)
670
671 UNION ALL
672 SELECT
673 NULL as var_rent_inv_id
674 ,(SELECT NVL((SUM(act_var_rent) + NVL(SUM(trueup_var_rent), 0) - NVL(SUM(first_yr_rent), 0)), 0)
675 FROM
676 pn_var_rent_summ_all
677 WHERE
678 var_rent_id = ip_var_rent_id AND
679 period_id = ip_period_id
680 ) AS constr_actual_rent
681 ,(SELECT NVL((SUM(act_var_rent) + NVL(SUM(trueup_var_rent), 0) - NVL(SUM(first_yr_rent), 0)), 0)
682 FROM
683 pn_var_rent_summ_all
684 WHERE
685 var_rent_id = ip_var_rent_id AND
686 period_id = ip_period_id
687 ) AS true_up_amt
688 ,'N' true_up_exp_code
689 , invoice_date
690 FROM
691 pn_var_rent_summ_all summ,
692 pn_var_periods_all per,
693 pn_var_rents_all vr
694 WHERE
695 summ.period_id = per.period_id AND
696 vr.var_rent_id = per.var_rent_id AND
697 (per.period_num <> 1 OR
698 vr.proration_rule NOT IN ('FY', 'FLY')) AND
699 summ.var_rent_id = ip_var_rent_id AND
700 summ.period_id = ip_period_id
701 AND summ.group_date = (select max(group_date) from pn_var_rent_summ_all
702 where period_id = ip_period_id)
703 AND G_IS_TU_CONC_FLAG = 'T'
704
705 UNION ALL
706 SELECT
707 NULL as var_rent_inv_id
708 ,(SELECT NVL(SUM(first_yr_rent), 0)
709 FROM
710 pn_var_rent_summ_all summ,
711 pn_var_periods_all per
712 WHERE
713 per.period_id = summ.period_id AND
714 summ.var_rent_id = ip_var_rent_id AND
715 per.period_num = 2
716 )
717 +
718 (SELECT NVL(SUM(trueup_var_rent), 0)
719 FROM
720 pn_var_rent_summ_all
721 WHERE
722 var_rent_id = ip_var_rent_id AND
723 period_id = ip_period_id
724 )
725 as const_t
726 ,(SELECT NVL(SUM(first_yr_rent), 0)
727 FROM
728 pn_var_rent_summ_all summ,
729 pn_var_periods_all per
730 WHERE
731 per.period_id = summ.period_id AND
732 summ.var_rent_id = ip_var_rent_id AND
733 per.period_num = 2
734 )
735 +
736 (SELECT NVL(SUM(trueup_var_rent), 0)
737 FROM
738 pn_var_rent_summ_all
739 WHERE
740 var_rent_id = ip_var_rent_id AND
741 period_id = ip_period_id
742 )
743 AS true_up_amt
744 ,'N' true_up_exp_code
745 , invoice_date
746 FROM
747 pn_var_rent_summ_all summ,
748 pn_var_periods_all per,
749 pn_var_rents_all vr
750 WHERE
751 summ.period_id = per.period_id AND
752 vr.var_rent_id = per.var_rent_id AND
753 vr.proration_rule IN ('FY', 'FLY') AND
754 per.period_num = 1 AND
755 summ.var_rent_id = ip_var_rent_id AND
756 summ.period_id = ip_period_id
757 AND summ.group_date = (select max(group_date) from pn_var_rent_summ_all
758 where period_id = ip_period_id)
759 AND G_IS_TU_CONC_FLAG = 'T';
760
761 /* get prev invoiced amount. */
762 CURSOR prev_invoiced_c( p_vr_id IN NUMBER
763 ,p_prd_id IN NUMBER
764 ,p_inv_dt IN DATE) IS
765 SELECT
766 NVL(SUM(actual_invoiced_amount), 0) AS prev_invoiced_amt
767 FROM
768 pn_var_rent_inv_all
769 WHERE
770 var_rent_id = p_vr_id AND
771 period_id = p_prd_id AND
772 invoice_date = p_inv_dt AND
773 (actual_exp_code = 'Y'
774 OR variance_exp_code = 'Y')
775 AND
776 (NVL(true_up_amt, 0) = 0 AND
777 true_up_status IS NULL AND
778 true_up_exp_code IS NULL);
779
780 --Previously invoiced true up rent.
781 CURSOR prev_invoiced_tu_c( p_vr_id IN NUMBER
782 ,p_prd_id IN NUMBER
783 ,p_inv_dt IN DATE) IS
784 SELECT
785 NVL(SUM(actual_invoiced_amount), 0) AS prev_inv_trueup_amt
786 FROM
787 pn_var_rent_inv_all
788 WHERE
789 var_rent_id = p_vr_id AND
790 period_id = p_prd_id AND
791 invoice_date = p_inv_dt AND
792 (actual_exp_code = 'Y' OR true_up_exp_code = 'Y' OR variance_exp_code = 'Y') AND
793 /*true_up_amt <> 0 AND */
794 true_up_status IS NOT NULL AND
795 true_up_exp_code IS NOT NULL;
796
797 -- Get the details of rolling allowance
798 CURSOR rolling_allow_c(ip_var_rent_id NUMBER) IS
799 SELECT NVL(amount, 0) rolling_allow
800 ,allowance_applied allow_applied
801 ,start_date
802 ,end_date
803 ,abatement_id
804 FROM PN_VAR_ABAT_DEFAULTS_ALL
805 WHERE var_rent_id = ip_var_rent_id
806 AND type_code = pn_var_rent_calc_pkg.G_ABAT_TYPE_CODE_ALLO
807 ORDER BY start_date;
808
809 -- Get the details of exported status
810 CURSOR is_act_or_rec_exp_c(ip_var_rent_inv_id NUMBER) IS
811 SELECT DECODE(invoice_on,
812 pn_var_rent_calc_pkg.G_INV_ON_ACTUAL, actual_exp_code,
813 pn_var_rent_calc_pkg.G_INV_ON_FORECASTED, variance_exp_code) AS
814 exp_code,
815 inv.actual_invoiced_amount
816 FROM pn_var_rents_all vr,
817 pn_var_rent_inv_all inv
818 WHERE vr.var_rent_id = inv.var_rent_id
819 AND inv.var_rent_inv_id = ip_var_rent_inv_id;
820
821 -- sum of non cumulative rents of a specific period
822 CURSOR non_cumm_rent_c(ip_var_rent_id NUMBER,
823 ip_period_id NUMBER
824 ) IS
825 SELECT SUM(actual_invoiced_amount) tot_nc_rent
826 FROM pn_var_rent_inv_all
827 WHERE var_rent_id = ip_var_rent_id
828 AND period_id = ip_period_id
829 AND true_up_amt IS NULL
830 AND true_up_status IS NULL
831 AND true_up_exp_code IS NULL;
832
833
834 -- Get all the details of a specific invoice.
835 CURSOR inv_all_c(ip_vr_inv_id NUMBER
836 ) IS
837 SELECT *
838 FROM pn_var_rent_inv_all
839 WHERE var_rent_inv_id = ip_vr_inv_id;
840
841
842 l_abat_order VARCHAR(30);
843 l_prev_inv_exp NUMBER;
844 l_abated_rent NUMBER;
845 l_allow_t ALLOW_TBL;--Table to keep track of allowance for non-cumm inv
846 l_allow_tu_t ALLOW_TBL;--Table to keep track of allowance for TU invoices
847 l_diff_amt NUMBER;
848 l_is_inv_exp VARCHAR2(30);
849 l_row_id ROWID;
850 l_var_rent_inv_id NUMBER;
851 l_vr_termination_date DATE;
852 l_actual_invoiced_amount NUMBER;
853 l_org_id NUMBER;
854 l_precision NUMBER;
855
856 BEGIN
857 /*To apply abatements we need to
858 1. Apply deffered negative rents.
859 2. Apply allowances/Abatements.
860 3. Apply/Allowances/Abatements.
861 */
862 pnp_debug_pkg.log('*apply_abatements start(+)*');
863 pnp_debug_pkg.log('p_flag:'||p_flag);
864 FOR rec IN order_c(p_var_rent_id) LOOP
865 l_abat_order := rec.ORDER_OF_APPL_CODE;
866 l_vr_termination_date := rec.termination_date;
867 l_org_id := rec.org_id;
868 --Since each record has same value for ORDER_OF_APPL_CODE exit after one looping.
869 EXIT;
870 END LOOP;
871
872 OPEN rolling_allow_c(p_var_rent_id);
873 FETCH rolling_allow_c BULK COLLECT INTO l_allow_t;
874 CLOSE rolling_allow_c;
875
876 OPEN rolling_allow_c(p_var_rent_id);
877 FETCH rolling_allow_c BULK COLLECT INTO l_allow_tu_t;
878 CLOSE rolling_allow_c;
879
880
881 l_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(l_org_id),4);
882
883 FOR inv_rec IN inv_c(p_var_rent_id, p_period_id, l_vr_termination_date) LOOP
884
885 --If this is a non cummulative apply_defered_neg_rent
886 --Do not apply def_neg_rent in TU invoices
887 IF (inv_rec.true_up_amt IS NULL) THEN
888 PN_VAR_TRUEUP_PKG.apply_def_neg_rent(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
889 ELSE
890 l_abated_rent := inv_rec.constr_actual_rent;
891 G_UNABATED_RENT(G_UNABATED_RENT.COUNT+1).period_id := p_period_id;
892 G_UNABATED_RENT(G_UNABATED_RENT.COUNT).amount := l_abated_rent;
893 END IF;
894
895 IF (l_abat_order = pn_var_rent_calc_pkg.G_ALLOWANCE_FIRST) THEN
896 pnp_debug_pkg.log('call pnp_debug_pkg.log');
897 PN_VAR_TRUEUP_PKG.apply_allow(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_allow_t, l_allow_tu_t, l_abated_rent);
898 pnp_debug_pkg.log('complete');
899 END IF;--Apply allowance.
900
901
902 pnp_debug_pkg.log('call populate_abat');
903 PN_VAR_TRUEUP_PKG.populate_abat(p_var_rent_id , p_period_id, inv_rec.var_rent_inv_id);
904 pnp_debug_pkg.log('complete');
905
906
907 pnp_debug_pkg.log('call apply_abat');
908 PN_VAR_TRUEUP_PKG.apply_abat(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
909 pnp_debug_pkg.log('complete');
910
911
912 IF(l_abat_order <> pn_var_rent_calc_pkg.G_ALLOWANCE_FIRST) THEN
913 pnp_debug_pkg.log('call pnp_debug_pkg.log');
914 PN_VAR_TRUEUP_PKG.apply_allow(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_allow_t, l_allow_tu_t, l_abated_rent);
915 pnp_debug_pkg.log('complete');
916 END IF;--Apply allowance
917
918
919 pnp_debug_pkg.log('called populate_neg_rent');
920 IF (inv_rec.true_up_amt IS NULL) THEN
921 PN_VAR_TRUEUP_PKG.populate_neg_rent(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
922 END IF;
923 pnp_debug_pkg.log('complete');
924
925
926 /* update the invoice */
927 IF (p_flag = 'CALCULATE') THEN
928 IF (inv_rec.true_up_amt IS NULL) THEN
929 --Non true up invoice
930 FOR exp_rec IN is_act_or_rec_exp_c(inv_rec.var_rent_inv_id) LOOP
931 l_is_inv_exp := exp_rec.exp_code;
932 l_actual_invoiced_amount := exp_rec.actual_invoiced_amount;
933 END LOOP;
934 FOR rec IN prev_invoiced_c(p_var_rent_id, p_period_id, inv_rec.invoice_date) LOOP
935 l_prev_inv_exp := rec.prev_invoiced_amt;
936 END LOOP;
937
938 IF (l_is_inv_exp = 'N' ) AND
939 (round(l_abated_rent,l_precision) - round(l_prev_inv_exp,l_precision))<> NVL(l_actual_invoiced_amount, 0) THEN
940
941 DELETE
942 pn_payment_terms_all
943 WHERE
944 var_rent_inv_id = inv_rec.var_rent_inv_id AND
945 status <> pn_var_rent_calc_pkg.G_TERM_STATUS_APPROVED AND
946 var_rent_type = pn_var_rent_calc_pkg.G_INV_ON_ACTUAL;
947
948 -- Update the current invoice
949 pnp_debug_pkg.log('Abatements - updating');
950 UPDATE
951 pn_var_rent_inv_all
952 SET
953 actual_invoiced_amount = (round(l_abated_rent,l_precision) - round(l_prev_inv_exp,l_precision))
954 ,actual_term_status = 'N'
955 ,last_update_date = SYSDATE
956 ,last_updated_by = NVL(fnd_global.user_id,0)
957 ,last_update_login = NVL(fnd_global.login_id,0)
958 WHERE
959 var_rent_inv_id = inv_rec.var_rent_inv_id;
960 ELSIF ((l_abated_rent - l_prev_inv_exp)<>0) AND (l_is_inv_exp = 'Y') THEN
961 -- Insert a new invoice. This happens only if your rents changes as a result of
962 -- applied allowance/abat when you add them after some calc have been done.
963 FOR inv_all_rec IN inv_all_c(inv_rec.var_rent_inv_id) LOOP
964 --This can loop only once
965 pnp_debug_pkg.log('l_prev_inv_exp:'||l_prev_inv_exp);
966 pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
967 pnp_debug_pkg.log('Abatements - inserting');
968 l_row_id := NULL;
969 l_var_rent_inv_id := NULL;
970 pn_var_rent_inv_pkg.insert_row
971 ( x_rowid => l_row_id,
972 x_var_rent_inv_id => l_var_rent_inv_id,
973 x_adjust_num => inv_all_rec.adjust_num+1,
974 x_invoice_date => inv_all_rec.invoice_date,
975 x_for_per_rent => inv_all_rec.for_per_rent,
976 x_tot_act_vol => inv_all_rec.tot_act_vol,
977 x_act_per_rent => round(inv_all_rec.act_per_rent,l_precision),
978 x_constr_actual_rent => round(inv_all_rec.constr_actual_rent,l_precision),
979 x_abatement_appl => inv_all_rec.abatement_appl,
980 x_rec_abatement => inv_all_rec.rec_abatement,
981 x_rec_abatement_override => inv_all_rec.rec_abatement_override,
982 x_negative_rent => inv_all_rec.negative_rent,
983 x_actual_invoiced_amount => (round(l_abated_rent,l_precision) - round(l_prev_inv_exp,l_precision)),
984 x_period_id => inv_all_rec.period_id,
985 x_var_rent_id => inv_all_rec.var_rent_id,
986 x_forecasted_term_status => 'N',
987 x_variance_term_status => 'N',
988 x_actual_term_status => 'N',
989 x_forecasted_exp_code => 'N',
990 x_variance_exp_code => 'N',
991 x_actual_exp_code => 'N',
992 x_comments => 'created invoice',
993 x_attribute_category => NULL,
994 x_attribute1 => NULL,
995 x_attribute2 => NULL,
996 x_attribute3 => NULL,
997 x_attribute4 => NULL,
998 x_attribute5 => NULL,
999 x_attribute6 => NULL,
1000 x_attribute7 => NULL,
1001 x_attribute8 => NULL,
1002 x_attribute9 => NULL,
1003 x_attribute10 => NULL,
1004 x_attribute11 => NULL,
1005 x_attribute12 => NULL,
1006 x_attribute13 => NULL,
1007 x_attribute14 => NULL,
1008 x_attribute15 => NULL,
1009 x_creation_date => SYSDATE,
1010 x_created_by => NVL(fnd_global.user_id,0),
1011 x_last_update_date => SYSDATE,
1012 x_last_updated_by => NVL(fnd_global.user_id,0),
1013 x_last_update_login => NVL(fnd_global.login_id,0),
1014 x_org_id => inv_all_rec.org_id );
1015 END LOOP;
1016 END IF;
1017 ELSE
1018 -- True up invoice
1019 --
1020 /*FOR nc_rent_rec IN non_cumm_rent_c(p_var_rent_id, p_period_id) LOOP
1021 l_diff_amt := nc_rent_rec.tot_nc_rent;
1022 END LOOP;
1023
1024 l_abated_rent := l_abated_rent - l_diff_amt;
1025
1026 FOR rec IN prev_invoiced_tu_c(p_var_rent_id, p_period_id, inv_rec.invoice_date) LOOP
1027 l_prev_inv_exp := rec.prev_inv_trueup_amt;
1028 END LOOP;
1029
1030 IF (inv_rec.true_up_exp_code = 'N') THEN
1031 --Last invoice is not exported
1032 UPDATE
1033 pn_var_rent_inv_all
1034 SET
1035 actual_invoiced_amount = (l_abated_rent - l_prev_inv_exp)
1036 ,actual_term_status = 'N'
1037 ,last_update_date = SYSDATE
1038 ,last_updated_by = NVL(fnd_global.user_id,0)
1039 ,last_update_login = NVL(fnd_global.login_id,0)
1040 WHERE
1041 var_rent_inv_id = inv_rec.var_rent_inv_id;
1042 ELSIF ((l_abated_rent - l_prev_inv_exp)<>0) THEN
1043 -- Last invoice has been exported and an non zero adjustment terms needs to be created
1044 FOR inv_all_rec IN inv_all_c(inv_rec.var_rent_inv_id) LOOP
1045 --This can loop only once
1046 pnp_debug_pkg.log('l_prev_inv_exp:'||l_prev_inv_exp);
1047 pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
1048 pnp_debug_pkg.log('Abatements - inserting');
1049 l_row_id := NULL;
1050 l_var_rent_inv_id := NULL;
1051 pn_var_rent_inv_pkg.insert_row
1052 ( x_rowid => l_row_id,
1053 x_var_rent_inv_id => l_var_rent_inv_id,
1054 x_adjust_num => inv_all_rec.adjust_num+1,
1055 x_invoice_date => inv_all_rec.invoice_date,
1056 x_for_per_rent => inv_all_rec.for_per_rent,
1057 x_tot_act_vol => inv_all_rec.tot_act_vol,
1058 x_act_per_rent => inv_all_rec.act_per_rent,
1059 x_constr_actual_rent => inv_all_rec.constr_actual_rent,
1060 x_abatement_appl => inv_all_rec.abatement_appl,
1061 x_rec_abatement => inv_all_rec.rec_abatement,
1062 x_rec_abatement_override => inv_all_rec.rec_abatement_override,
1063 x_negative_rent => inv_all_rec.negative_rent,
1064 x_actual_invoiced_amount => (l_abated_rent - l_prev_inv_exp),
1065 x_period_id => inv_all_rec.period_id,
1066 x_var_rent_id => inv_all_rec.var_rent_id,
1067 x_forecasted_term_status => 'N',
1068 x_variance_term_status => 'N',
1069 x_actual_term_status => 'N',
1070 x_forecasted_exp_code => 'N',
1071 x_variance_exp_code => 'N',
1072 x_actual_exp_code => 'N',
1073 x_comments => 'created invoice',
1074 x_attribute_category => NULL,
1075 x_attribute1 => NULL,
1076 x_attribute2 => NULL,
1077 x_attribute3 => NULL,
1078 x_attribute4 => NULL,
1079 x_attribute5 => NULL,
1080 x_attribute6 => NULL,
1081 x_attribute7 => NULL,
1082 x_attribute8 => NULL,
1083 x_attribute9 => NULL,
1084 x_attribute10 => NULL,
1085 x_attribute11 => NULL,
1086 x_attribute12 => NULL,
1087 x_attribute13 => NULL,
1088 x_attribute14 => NULL,
1089 x_attribute15 => NULL,
1090 x_creation_date => SYSDATE,
1091 x_created_by => NVL(fnd_global.user_id,0),
1092 x_last_update_date => SYSDATE,
1093 x_last_updated_by => NVL(fnd_global.user_id,0),
1094 x_last_update_login => NVL(fnd_global.login_id,0),
1095 x_org_id => inv_all_rec.org_id );
1096 END LOOP;
1097 END IF;*/
1098 G_ABATED_RENT(G_ABATED_RENT.COUNT+1).period_id := p_period_id;
1099 G_ABATED_RENT(G_ABATED_RENT.COUNT).amount := l_abated_rent;
1100 END IF;
1101
1102 END IF;
1103 END LOOP;--Loop for all required invoices.
1104 pnp_debug_pkg.log('apply_abatements end(-)');
1105
1106 EXCEPTION
1107 WHEN others THEN
1108 pnp_debug_pkg.log('Raised exception');
1109 RAISE;
1110 END;
1111
1112
1113
1114 --------------------------------------------------------------------------------
1115 -- NAME : get_dated_allow
1116 -- DESCRIPTION : Gets allowances between specific dates.
1117 -- PURPOSE : Gets allowances between specific dates.
1118 -- INVOKED FROM : apply_allow()
1119 -- ARGUMENTS : p_allow_t- table constaining all the allowances
1120 -- p_start_date -
1121 -- p_end_date - Dates between which to select the allowances.
1122 -- REFERENCE :
1123 -- HISTORY :
1124 -- 22/Dec/06 Shabda o Found while fixing bug 5724597.If no allowances exist,
1125 -- return a not null value.
1126 --
1127 -- 25/Nov/2006 Shabda o Created
1128 --------------------------------------------------------------------------------
1129 FUNCTION get_dated_allow(p_allow_t ALLOW_TBL,
1130 p_start_date DATE,
1131 p_end_date DATE) RETURN ALLOW_TBL IS
1132 l_allow_t ALLOW_TBL;
1133 l_count NUMBER := 1;
1134 empty_rec ALLOW_REC;
1135 BEGIN
1136 --
1137 pnp_debug_pkg.log('get_dated_allow start (+)');
1138 IF (p_allow_t.COUNT > 0) THEN
1139 --
1140 FOR i IN 1..p_allow_t.COUNT LOOP
1141 pnp_debug_pkg.log('get_dated_allow 1');
1142 IF(p_allow_t(i).start_date <= p_end_date
1143 AND NVL(p_allow_t(i).end_date, p_end_date) >= p_start_date) THEN
1144 l_allow_t(l_count) := p_allow_t(i);
1145 l_count := l_count + 1;
1146 END IF;
1147 pnp_debug_pkg.log('get_dated_allow 2');
1148 END LOOP;
1149 END IF;
1150 pnp_debug_pkg.log('get_dated_allow 3');
1151 IF (l_allow_t.COUNT >0) THEN
1152 RETURN l_allow_t;
1153 ELSE
1154 --
1155 l_allow_t(1) := NULL;
1156 RETURN l_allow_t;
1157 END IF;
1158 EXCEPTION
1159 WHEN others THEN
1160 pnp_debug_pkg.log('get_dated_allow raised exception');
1161 RAISE;
1162 END;
1163
1164 --------------------------------------------------------------------------------
1165 -- NAME : apply_def_neg_rent
1166 -- DESCRIPTION : Applies deffered negative rent.
1167 -- PURPOSE : Applies deffered negative rent.
1168 -- INVOKED FROM : apply_abatements()
1169 -- ARGUMENTS : p_var_rent_id: Vr to apply abatements for.
1170 -- p_period_id: Period to calculate for.
1171 -- p_inv_id: Invoice to calculate for.
1172 -- REFERENCE :
1173 -- HISTORY :
1174 --
1175 -- 25/Nov/2006 Shabda o Created
1176 --------------------------------------------------------------------------------
1177 PROCEDURE apply_def_neg_rent(p_var_rent_id IN NUMBER,
1178 p_period_id IN NUMBER,
1179 p_inv_id IN NUMBER,
1180 x_abated_rent IN OUT NOCOPY NUMBER) IS
1181
1182 -- Get the details of all invoices
1183 CURSOR invoices_c(ip_var_rent_id NUMBER, ip_period_id NUMBER, ip_inv_id NUMBER
1184 ) IS
1185 SELECT constr_actual_rent
1186 ,actual_invoiced_amount
1187 ,true_up_amt
1188 ,negative_rent
1189 ,invoice_date
1190 ,var_rent_inv_id
1191 FROM pn_var_rent_inv_all inv1
1192 WHERE inv1.var_rent_id = ip_var_rent_id
1193 AND inv1.period_id = ip_period_id
1194 AND var_rent_inv_id = ip_inv_id
1195 AND inv1.adjust_num= (
1196 SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
1197 WHERE inv1.var_rent_id = inv2.var_rent_id
1198 AND inv1.period_id = inv2.period_id
1199 AND inv1.invoice_date = inv2.invoice_date);
1200
1201 CURSOR csr_last_inv(p_var_rent_id NUMBER)
1202 IS
1203 SELECT MAX(invoice_date) inv_date
1204 FROM pn_var_grp_dates_all
1205 WHERE var_rent_id = p_var_rent_id
1206 AND period_id = (SELECT max(period_id)
1207 FROM pn_var_periods_all
1208 WHERE var_rent_id = p_var_rent_id
1209 AND NVL(status, pn_var_rent_calc_pkg.G_PERIOD_ACTIVE_STATUS)
1210 <> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS);
1211
1212 CURSOR csr_neg_avail (ip_var_rent_id NUMBER,
1213 ip_invoice_date DATE) IS
1214 SELECT ABS(NVL(SUM(def_neg_rent),0)) negative_available
1215 FROM pn_var_rent_inv_all inv
1216 WHERE inv.var_rent_id = ip_var_rent_id
1217 --AND period_id = p_period_id
1218 AND inv.invoice_date < ip_invoice_date
1219 AND inv.adjust_num = (select MAX(inv1.adjust_num)
1220 from pn_var_rent_inv_all inv1
1221 where inv1.var_rent_id = inv.var_rent_id
1222 AND inv1.invoice_date = inv.invoice_date);
1223
1224 CURSOR csr_neg_appl (ip_var_rent_id NUMBER,
1225 ip_invoice_date DATE) IS
1226 SELECT NVL(SUM(negative_rent),0) negative_applied
1227 FROM pn_var_rent_inv_all inv
1228 WHERE inv.var_rent_id = ip_var_rent_id
1229 --AND period_id = p_period_id
1230 AND inv.invoice_date < ip_invoice_date
1231 AND inv.adjust_num = (select MAX(inv1.adjust_num)
1232 from pn_var_rent_inv_all inv1
1233 where inv1.var_rent_id = inv.var_rent_id
1234 AND inv1.invoice_date = inv.invoice_date);
1235 CURSOR csr_get_abt(ip_var_rent_id NUMBER)
1236 IS
1237 SELECT negative_rent
1238 FROM pn_var_rents_all
1239 WHERE var_rent_id = ip_var_rent_id;
1240
1241
1242 l_negative_rent pn_var_rent_inv.negative_rent%TYPE := 0;
1243 l_negative_available NUMBER := 0;
1244 l_negative_applied NUMBER := 0;
1245 l_negative_remaining NUMBER;
1246 l_abated_rent NUMBER;
1247 l_negative_rent_flag pn_var_rents.negative_rent%TYPE;
1248 l_last_invoice_dt pn_var_grp_dates.invoice_date%TYPE;
1249 BEGIN
1250 pnp_debug_pkg.log('apply_def_neg_rent start(+)');
1251 -- Get the negative rent flag
1252 FOR rec IN csr_get_abt(p_var_rent_id) LOOP
1253 l_negative_rent_flag := rec.negative_rent;
1254 pnp_debug_pkg.log('l_negative_rent_flag:'||l_negative_rent_flag);
1255 END LOOP;
1256 -- Get the last invoice_date
1257 FOR rec IN csr_last_inv(p_var_rent_id) LOOP
1258 l_last_invoice_dt := rec.inv_date;
1259 pnp_debug_pkg.log('l_last_invoice_dt:'||l_last_invoice_dt);
1260 END LOOP;
1261
1262 PNP_DEBUG_PKg.log('p_period_id:'||p_period_id);
1263 PNP_DEBUG_PKG.log('p_var_rent_id:'||p_var_rent_id);
1264
1265 -- Loop for all invoices.
1266 FOR inv_rec IN invoices_c(p_var_rent_id, p_period_id, p_inv_id) LOOP
1267 pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
1268 l_negative_rent := 0;
1269 l_negative_available := 0;
1270 l_negative_applied := 0;
1271 l_abated_rent :=inv_rec.constr_actual_rent;
1272
1273
1274 PNP_DEBUG_PKG.log('inv_rec.invoice_date:'||inv_rec.invoice_date);
1275 -- Get available negative rent.
1276 FOR rec IN csr_neg_avail(p_var_rent_id, inv_rec.invoice_date) LOOP
1277 l_negative_available :=rec.negative_available;
1278 pnp_debug_pkg.log('l_negative_available'||l_negative_available);
1279 END LOOP;
1280
1281 -- Get applied negative rent
1282 FOR rec IN csr_neg_appl (p_var_rent_id, inv_rec.invoice_date) LOOP
1283 l_negative_applied := rec.negative_applied;
1284 pnp_debug_pkg.log('l_negative_applied:'||l_negative_applied);
1285 END LOOP;
1286
1287 l_negative_remaining := ABS(l_negative_available - l_negative_applied);
1288 pnp_debug_pkg.log('l_negative_remaining:'||l_negative_remaining);
1289 IF (l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_DEFER) THEN
1290 -- Deffered negative rent can be applied only when consT-rent >0
1291 IF (l_last_invoice_dt <> inv_rec.invoice_date AND inv_rec.constr_actual_rent > 0) THEN
1292 --This invoice is not the last invoice
1293 l_abated_rent := GREATEST(0, inv_rec.constr_actual_rent - l_negative_remaining);
1294 pnp_debug_pkg.log('l_abated_rent1:'||l_abated_rent);
1295 IF (inv_rec.constr_actual_rent > l_abated_rent) THEN
1296 l_negative_rent := inv_rec.constr_actual_rent - l_abated_rent;
1297 ELSE
1298 l_negative_rent := 0;
1299 END IF;
1300
1301
1302 ELSIF (l_last_invoice_dt = inv_rec.invoice_date) THEN
1303 --This is the last invoice. All deffered negative rent needs to be added
1304 l_abated_rent := inv_rec.constr_actual_rent - l_negative_remaining;
1305 pnp_debug_pkg.log('l_abated_rent2:'||l_abated_rent);
1306 l_negative_rent := inv_rec.constr_actual_rent - l_abated_rent;
1307 END IF;
1308 END IF;
1309 pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
1310 pnp_debug_pkg.log('l_negative_rent:'||l_negative_rent);
1311 pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
1312
1313 UPDATE pn_var_rent_inv_all
1314 SET negative_rent = l_negative_rent
1315 WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
1316 x_abated_rent := l_abated_rent;
1317 END LOOP;
1318 pnp_debug_pkg.log('apply_def_neg_rent end(-)');
1319
1320 EXCEPTION
1321 --
1322 WHEN others THEN
1323 pnp_debug_pkg.log('Raised exception');
1324 RAISE;
1325 END;
1326
1327
1328 --------------------------------------------------------------------------------
1329 -- NAME : populate_neg_rent
1330 -- DESCRIPTION : Populates the negative rent which needs to be deffered.
1331 -- PURPOSE : Populates the negative rent which needs to be deffered.
1332 -- INVOKED FROM : apply_abatements()
1333 -- ARGUMENTS : p_var_rent_id: Vr to apply abatements for.
1334 -- p_period_id: Period to calculate for.
1335 -- p_inv_id: Invoice to calculate for.
1336 -- REFERENCE :
1337 -- HISTORY :
1338 --
1339 -- 25/Nov/2006 Shabda o Created
1340 --------------------------------------------------------------------------------
1341 PROCEDURE populate_neg_rent(p_var_rent_id IN NUMBER,
1342 p_period_id IN NUMBER,
1343 p_inv_id IN NUMBER,
1344 x_abated_rent IN OUT NOCOPY NUMBER) IS
1345
1346 -- Get the details of all invoices
1347 CURSOR invoices_c(ip_var_rent_id NUMBER, ip_period_id NUMBER, ip_inv_id NUMBER
1348 ) IS
1349 SELECT invoice_date
1350 ,var_rent_inv_id
1351 FROM pn_var_rent_inv_all inv1
1352 WHERE inv1.var_rent_id = ip_var_rent_id
1353 AND inv1.period_id = ip_period_id
1354 AND var_rent_inv_id = ip_inv_id
1355 AND inv1.adjust_num= (
1356 SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
1357 WHERE inv1.var_rent_id = inv2.var_rent_id
1358 AND inv1.period_id = inv2.period_id
1359 AND inv1.invoice_date = inv2.invoice_date);
1360
1361 CURSOR csr_last_inv(p_var_rent_id NUMBER)
1362 IS
1363 SELECT MAX(invoice_date) inv_date
1364 FROM pn_var_grp_dates_all
1365 WHERE var_rent_id = p_var_rent_id
1366 AND period_id = (SELECT max(period_id)
1367 FROM pn_var_periods_all
1368 WHERE var_rent_id = p_var_rent_id
1369 AND NVL(status, pn_var_rent_calc_pkg.G_PERIOD_ACTIVE_STATUS)
1370 <> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS);
1371
1372
1373 CURSOR csr_get_abt(ip_var_rent_id NUMBER)
1374 IS
1375 SELECT negative_rent
1376 FROM pn_var_rents_all
1377 WHERE var_rent_id = ip_var_rent_id;
1378
1379 l_neg_rent_def NUMBER;
1380 l_negative_rent pn_var_rent_inv.negative_rent%TYPE := 0;
1381 l_negative_available NUMBER := 0;
1382 l_negative_applied NUMBER := 0;
1383 l_negative_remaining NUMBER;
1384 l_abated_rent NUMBER;
1385 l_negative_rent_flag pn_var_rents.negative_rent%TYPE;
1386 l_last_invoice_dt pn_var_grp_dates.invoice_date%TYPE;
1387 BEGIN
1388 pnp_debug_pkg.log('populate_neg_rent start(+)');
1389 -- Get the negative rent flag
1390 FOR rec IN csr_get_abt(p_var_rent_id) LOOP
1391 l_negative_rent_flag := rec.negative_rent;
1392 pnp_debug_pkg.log('l_negative_rent_flag:'||l_negative_rent_flag);
1393 END LOOP;
1394 -- Get the last invoice_date
1395 FOR rec IN csr_last_inv(p_var_rent_id) LOOP
1396 l_last_invoice_dt := rec.inv_date;
1397 pnp_debug_pkg.log('l_last_invoice_dt:'||l_last_invoice_dt);
1398 END LOOP;
1399 -- Loop for all invoices.
1400 FOR inv_rec IN invoices_c(p_var_rent_id, p_period_id, p_inv_id) LOOP
1401 IF (l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_IGNORE) THEN
1402 --We are ignoring negative rents. Set abated rent =0, if <0.
1403 l_abated_rent := GREATEST(0, x_abated_rent);
1404
1405 ELSIF (l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_CREDIT) THEN
1406 l_abated_rent := x_abated_rent;
1407 ELSIF (l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_DEFER
1408 AND inv_rec.invoice_date <> l_last_invoice_dt) THEN
1409 l_abated_rent := GREATEST(0,x_abated_rent);
1410 l_neg_rent_def := ABS(x_abated_rent - l_abated_rent);
1411 ELSIF (l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_DEFER
1412 AND inv_rec.invoice_date = l_last_invoice_dt) THEN
1413 l_abated_rent := x_abated_rent;
1414 END IF;
1415 pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
1416 pnp_debug_pkg.log('l_neg_rent_def*:'||l_neg_rent_def);
1417 UPDATE pn_var_rent_inv_all
1418 SET def_neg_rent = l_neg_rent_def
1419 WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
1420 x_abated_rent := l_abated_rent;
1421 END LOOP;
1422 pnp_debug_pkg.log('populate_neg_rent end(-)');
1423
1424 EXCEPTION
1425 --
1426 WHEN others THEN
1427 pnp_debug_pkg.log('Raised exception');
1428 RAISE;
1429 END;
1430 --------------------------------------------------------------------------------
1431 -- NAME : apply_abat
1432 -- DESCRIPTION : applies the fixed and recurring abatements.
1433 -- PURPOSE : applies the fixed and recurring abatements.
1434 -- INVOKED FROM : apply_abatements()
1435 -- ARGUMENTS : p_var_rent_id: Vr to apply abatements for.
1436 -- p_period_id: Period to calculate for.
1437 -- p_inv_id: Invoice to calculate for.
1438 -- REFERENCE :
1439 -- HISTORY :
1440 --
1441 -- 25/Nov/2006 Shabda o Created
1442 -- 6/Jan/2007 Shabda o Bug 5731479. Use rec_override field.
1443 --------------------------------------------------------------------------------
1444 PROCEDURE apply_abat(p_var_rent_id IN NUMBER,
1445 p_period_id IN NUMBER,
1446 p_inv_id IN NUMBER,
1447 x_abated_rent IN OUT NOCOPY NUMBER) IS
1448
1449 -- Get the details of all invoices
1450 CURSOR invoices_c(ip_var_rent_id NUMBER, ip_period_id NUMBER,
1451 ip_inv_id NUMBER
1452 ) IS
1453 SELECT actual_invoiced_amount
1454 ,invoice_date
1455 ,NVL(rec_abatement,0) rec_abatement
1456 ,rec_abatement_override
1457 ,true_up_amt
1458 ,var_rent_inv_id
1459 FROM pn_var_rent_inv_all inv1
1460 WHERE inv1.var_rent_id = ip_var_rent_id
1461 AND inv1.period_id = ip_period_id
1462 AND inv1.var_rent_inv_id = ip_inv_id
1463 AND inv1.adjust_num= (
1464 SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
1465 where inv1.var_rent_id = inv2.var_rent_id
1466 AND inv1.period_id = inv2.period_id
1467 AND inv1.invoice_date = inv2.invoice_date)
1468 AND TRUE_UP_AMT IS NULL
1469 UNION ALL
1470 SELECT
1471 (SELECT NVL(SUM(act_var_rent), 0)
1472 FROM
1473 pn_var_rent_summ_all
1474 WHERE
1475 var_rent_id = ip_var_rent_id AND
1476 period_id = ip_period_id
1477 ) AS actual_invoiced_amount
1478 , invoice_date
1479 , null rec_abatement
1480 , null rec_abatement_override
1481 ,(SELECT NVL(SUM(act_var_rent), 0)
1482 FROM
1483 pn_var_rent_summ_all
1484 WHERE
1485 var_rent_id = ip_var_rent_id AND
1486 period_id = ip_period_id
1487 ) AS true_up_amt
1488 ,NULL AS VAR_RENT_INV_ID
1489 FROM PN_VAR_RENT_SUMM_ALL
1490 WHERE ip_inv_id IS NULL
1491 AND var_rent_id = ip_var_rent_id
1492 AND period_id = ip_period_id
1493 AND group_date = (select max(group_date) from pn_var_rent_summ_all
1494 where period_id = ip_period_id)
1495 AND G_IS_TU_CONC_FLAG = 'T';
1496
1497
1498 -- Get the details of fixed abatements
1499 CURSOR fixed_abat_c(ip_var_rent_id NUMBER,
1500 ip_inv_start_date DATE,
1501 ip_inv_end_date DATE) IS
1502 SELECT NVL(SUM(amount),0) fixed_abat
1503 FROM PN_VAR_ABAT_DEFAULTS_ALL
1504 WHERE var_rent_id = ip_var_rent_id
1505 AND start_date <= ip_inv_end_date
1506 AND NVL(end_date, ip_inv_end_date) >= ip_inv_start_date
1507 AND type_code = pn_var_rent_calc_pkg.G_ABAT_TYPE_CODE_ABAT;
1508 -- Get the details of
1509 CURSOR EXCESS_ABAT_C(ip_var_rent_id NUMBER) IS
1510 SELECT EXCESS_ABAT_CODE
1511 FROM PN_VAR_RENTS_ALL ABAT
1512 WHERE abat.var_rent_id = ip_var_rent_id;
1513 -- Get the details of inv_start, end_date
1514 CURSOR invoice_dates_c(ip_var_rent_id NUMBER,
1515 ip_invoice_date DATE
1516 ) IS
1517 SELECT inv_start_date, inv_end_date
1518 FROM pn_var_grp_dates_all
1519 WHERE var_rent_id = ip_var_rent_id
1520 AND invoice_date = ip_invoice_date;
1521 -- Get the details of actual start and end date for FY/LY/FLY
1522 CURSOR invoice_dates_fyly_c(ip_var_rent_inv_id NUMBER
1523 ) IS
1524 SELECT per.start_date, per.end_date
1525 FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1526 WHERE per.period_id = inv.period_id
1527 AND inv.var_rent_inv_id = ip_var_rent_inv_id;
1528
1529 -- Get the id of invoice created for first year.
1530 -- This has meaning only if proration is FY/FLY
1531 CURSOR get_fy_inv_c(ip_var_rent_id NUMBER
1532 ) IS
1533 SELECT inv.var_rent_inv_id
1534 FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1535 WHERE per.period_id = inv.period_id
1536 AND inv.var_rent_id = ip_var_rent_id
1537 AND per.start_date = (SELECT MIN(start_date) from pn_var_periods_all
1538 WHERE var_rent_id = ip_var_rent_id);
1539 -- Get the id of last invoice created.
1540 -- This has meaning only if proration is LY/FLY
1541 CURSOR get_ly_inv_c(ip_var_rent_id NUMBER
1542 ) IS
1543 SELECT inv.var_rent_inv_id
1544 FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1545 WHERE per.period_id = inv.period_id
1546 AND inv.var_rent_id = ip_var_rent_id
1547 AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
1548 WHERE var_rent_id = ip_var_rent_id)
1549 AND inv.invoice_date = (SELECT MAX(invoice_date) FROM pn_var_rent_inv_all
1550 WHERE var_rent_id = ip_var_rent_id)
1551 AND inv.true_up_amt is NULL;
1552
1553
1554
1555 --Get the details of inv_start, end_date for TU
1556 CURSOR invoice_dates_tu_c(ip_var_rent_id NUMBER,
1557 ip_period_id NUMBER
1558 ) IS
1559 SELECT MIN(inv_start_date)inv_start_date,
1560 MAX(inv_end_date) inv_end_date
1561 FROM pn_var_grp_dates_all
1562 WHERE var_rent_id = ip_var_rent_id
1563 AND period_id = ip_period_id;
1564
1565 -- Get the details of negative_rent
1566 CURSOR neg_rent_c(ip_var_rent_id NUMBER
1567 ) IS
1568 SELECT negative_rent
1569 FROM pn_var_rents_all
1570 WHERE var_rent_id = ip_var_rent_id;
1571
1572 -- Get the details of negative_rent
1573 CURSOR calc_freq_c(ip_var_rent_id NUMBER
1574 ) IS
1575 SELECT REPTG_FREQ_CODE
1576 FROM pn_var_rent_dates_all
1577 WHERE var_rent_id = ip_var_rent_id;
1578
1579 -- Get the details of
1580 CURSOR inv_in_prd_c(ip_var_rent_id NUMBER,
1581 ip_period_id NUMBER
1582 ) IS
1583 SELECT COUNT(UNIQUE(invoice_date)) num_inv
1584 FROM pn_var_rent_inv_all
1585 WHERE var_rent_id = ip_var_rent_id
1586 AND period_id = ip_period_id;
1587
1588 -- Get the number of inv which should exist for a period
1589 CURSOR num_inv_c(ip_var_rent_inv_id NUMBER
1590 ) IS
1591 SELECT ceil(months_between(per.end_date, per.start_date)/decode(vrd.invg_freq_code,'YR', 12, 'SA', 6, 'QTR', 3, 'MON', 1, 1)) num_inv
1592 FROM pn_var_rent_inv_all inv, pn_var_periods_all per, pn_var_rents_all vr, pn_var_rent_dates_all vrd
1593 WHERE per.period_id = inv.period_id
1594 AND inv.var_rent_inv_id = ip_var_rent_inv_id
1595 AND per.var_rent_id = vr.var_rent_id
1596 AND vrd.var_rent_id = vr.var_rent_id;
1597
1598 --Get the last invoice of the last year
1599 CURSOR ly_min_inv_c(ip_vr_id NUMBER
1600 ) IS
1601 SELECT inv.invoice_date,
1602 inv.var_rent_inv_id,
1603 inv.period_id
1604 FROM pn_var_rents_all vr,
1605 pn_var_periods_all per,
1606 pn_var_rent_inv_all inv
1607 WHERE per.var_rent_id = vr.var_rent_id
1608 AND inv.period_id = per.period_id
1609 AND per.start_date = (select max(start_date) from pn_var_periods_all per1
1610 where per1.var_rent_id = vr.var_rent_id)
1611 AND inv.invoice_date = (select max(invoice_date) from pn_var_rent_inv_all inv1
1612 where inv1.period_id = per.period_id)
1613 AND vr.var_rent_id = ip_vr_id;
1614
1615 -- Get the proration type
1616 CURSOR proration_type_c(ip_var_rent_id NUMBER
1617 ) IS
1618 SELECT proration_rule
1619 FROM pn_var_rents_all
1620 WHERE var_rent_id = ip_var_rent_id;
1621
1622 -- Get the details of
1623 CURSOR rec_abatement_c(ip_period_id NUMBER
1624 ) IS
1625 SELECT SUM(rec_abatement) AS AMOUNT
1626 FROM pn_var_rent_inv_all inv1
1627 WHERE period_id = ip_period_id
1628 AND inv1.adjust_num= (
1629 SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
1630 where inv1.var_rent_id = inv2.var_rent_id
1631 AND inv1.invoice_date = inv2.invoice_date
1632 AND true_up_amt IS NULL)
1633 AND true_up_amt IS NULL;
1634
1635 -- Get the details of
1636 CURSOR get_rec_abat_over_tu_c(p_period_id NUMBER
1637 ) IS
1638 SELECT rec_abatement_override
1639 FROM pn_var_rent_inv_all
1640 WHERE period_id = p_period_id
1641 AND true_up_amt is NOT NULL
1642 AND adjust_num = (SELECT max(adjust_num)
1643 FROM PN_VAR_RENT_INV_ALL
1644 WHERE period_id = p_period_id
1645 AND TRUE_UP_AMT IS NOT NULL);
1646
1647
1648
1649
1650
1651 l_fixed_abat NUMBER := 0;
1652 l_rec_abat NUMBER := 0;
1653 l_total_abat NUMBER;
1654 l_excess_abat VARCHAR2(30);
1655 l_abated_rent NUMBER;
1656 l_inv_start_date DATE;
1657 l_inv_end_date DATE;
1658 l_neg_rent VARCHAR2(30);
1659 l_grp_in_prd NUMBER; --Number of groups in this period
1660 l_unabated_rent NUMBER;
1661 l_abat_override NUMBER;
1662 l_proration_type VARCHAR2(30);
1663 l_first_inv_id NUMBER;--inv_id of the first inv created
1664 l_last_inv_id NUMBER;--inv_id of the last inv created
1665 l_months_in_inv NUMBER;
1666 l_num_inv NUMBER := 1;
1667 l_ly_max_inv_id NUMBER;
1668 l_ly_max_prd_id NUMBER;
1669
1670 BEGIN
1671 --
1672 pnp_debug_pkg.log('apply_abat start(+)');
1673 --The special cases this needs to handle are
1674 -- FY: The FY invoice would have invoice_date of next period.
1675 -- LY: Non last invoices are dummy, last invoice corresponds to the whole period.
1676 -- FLY: Both LY and FLY
1677 -- Get the proration type
1678 FOR proration_rec IN proration_type_c(p_var_rent_id) LOOP
1679 l_proration_type := proration_rec.proration_rule;
1680 END LOOP;
1681 pnp_debug_pkg.log('apply_abat start(+)');
1682 -- is this in ly first inv? No abatements should be applied to this invoice
1683 FOR ly_inv_rec IN ly_min_inv_c(p_var_rent_id) LOOP
1684 l_ly_max_inv_id := ly_inv_rec.var_rent_inv_id;
1685 l_ly_max_prd_id := ly_inv_rec.period_id;
1686 END LOOP;
1687 pnp_debug_pkg.log('l_ly_max_inv_id:'||l_ly_max_inv_id);
1688 pnp_debug_pkg.log('l_ly_max_prd_id:'||l_ly_max_prd_id);
1689
1690
1691
1692 --For LY/FLY, if last period and not last invoice,
1693 -- Invoices are dummy. Return immdiately.
1694 IF (p_period_id = l_ly_max_prd_id AND NOT(p_inv_id = l_ly_max_inv_id) AND l_proration_type IN (pn_var_rent_calc_pkg.G_PRORUL_FLY, pn_var_rent_calc_pkg.G_PRORUL_LY) ) THEN
1695 pnp_debug_pkg.log('Ly invoice, not last - return immedietly');
1696 RETURN;
1697 END IF;
1698 FOR abat_rec IN EXCESS_ABAT_C(p_var_rent_id) LOOP
1699 l_excess_abat := abat_rec.excess_abat_code;
1700 pnp_debug_pkg.log('l_excess_abat:'||l_excess_abat);
1701 EXIT;
1702 END LOOP;
1703
1704 -- Get the number of invoices in this period
1705 FOR inv_in_prd_rec IN inv_in_prd_c(p_var_rent_id, p_period_id) LOOP
1706 l_grp_in_prd := inv_in_prd_rec.num_inv;
1707 pnp_debug_pkg.log('l_grp_in_prd:'||l_grp_in_prd);
1708 END LOOP;
1709 --
1710 FOR neg_rec IN neg_rent_c(p_var_rent_id) LOOP
1711 l_neg_rent := neg_rec.negative_rent;
1712 pnp_debug_pkg.log('l_neg_rent:'||l_neg_rent);
1713 END LOOP;
1714
1715 --
1716 FOR fy_rec IN get_fy_inv_c(p_var_rent_id) LOOP
1717 l_first_inv_id := fy_rec.var_rent_inv_id;
1718 END LOOP;
1719
1720 --
1721 FOR ly_rec IN get_ly_inv_c(p_var_rent_id) LOOP
1722 l_last_inv_id := ly_rec.var_rent_inv_id;
1723 pnp_debug_pkg.log('l_last_inv_id:'||l_last_inv_id);
1724 END LOOP;
1725
1726 FOR inv_rec IN invoices_c(p_var_rent_id , p_period_id, p_inv_id) LOOP
1727 -- update rec abatements.
1728 IF (inv_rec.true_up_amt IS NULL) THEN
1729 FOR inv_dates_rec IN invoice_dates_c(p_var_rent_id, inv_rec.invoice_date) LOOP
1730 l_inv_start_date := inv_dates_rec.inv_start_date;
1731 l_inv_end_date := inv_dates_rec.inv_end_date;
1732 END LOOP;
1733
1734
1735 pnp_debug_pkg.log('l_inv_end_date:'||l_inv_end_date);
1736
1737 -- The special handling is if this is FL/LY special invoice and non true up.
1738 IF ((l_proration_type IN (pn_var_rent_calc_pkg.G_PRORUL_FY, pn_var_rent_calc_pkg.G_PRORUL_FLY) AND p_inv_id = l_first_inv_id)
1739 OR (l_proration_type IN (pn_var_rent_calc_pkg.G_PRORUL_LY, pn_var_rent_calc_pkg.G_PRORUL_FLY) AND p_inv_id = l_last_inv_id) ) THEN
1740 --
1741 FOR inv_rec IN invoice_dates_fyly_c(p_inv_id) LOOP
1742 l_inv_start_date := inv_rec.start_date;
1743 l_inv_end_date := inv_rec.end_date;
1744 END LOOP;
1745 --
1746 pnp_debug_pkg.log('FY/LY modified dates');
1747 pnp_debug_pkg.log('l_inv_start_date:'||l_inv_start_date);
1748 pnp_debug_pkg.log('l_inv_end_date:'||l_inv_end_date);
1749 FOR num_rec IN num_inv_c(p_inv_id) LOOP
1750 l_num_inv := num_rec.num_inv;
1751 END LOOP;
1752 pnp_debug_pkg.log('l_num_inv:'||l_num_inv);
1753
1754 END IF;
1755 ELSIF (inv_rec.true_up_amt IS NOT NULL) THEN
1756 --
1757 FOR inv_dates_rec IN invoice_dates_tu_c(p_var_rent_id, p_period_id) LOOP
1758 l_inv_start_date := inv_dates_rec.inv_start_date;
1759 l_inv_end_date := inv_dates_rec.inv_end_date;
1760 END LOOP;
1761 END IF;
1762
1763 l_rec_abat := inv_rec.rec_abatement;
1764 pnp_debug_pkg.log('l_rec_abat:'||l_rec_abat);
1765
1766 IF (inv_rec.true_up_amt IS NOT NULL) THEN
1767 pnp_debug_pkg.log('zxc:');
1768 --
1769 FOR i IN 1..G_ABATEMENT_APPLIED.COUNT LOOP
1770 IF G_ABATEMENT_APPLIED(i).PERIOD_ID = p_period_id THEN
1771 l_rec_abat := NVL(G_ABATEMENT_APPLIED(i).AMOUNT, 0);
1772 pnp_debug_pkg.log('l_rec_abat:'||l_rec_abat);
1773 END IF;
1774
1775 END LOOP;
1776
1777
1778 END IF;
1779
1780
1781
1782
1783 l_abat_override := inv_rec.rec_abatement_override;
1784 IF inv_rec.true_up_amt IS NOT NULL THEN
1785 --
1786 FOR rec_over_rec IN get_rec_abat_over_tu_c(p_period_id) LOOP
1787 l_abat_override := rec_over_rec.rec_abatement_override;
1788 END LOOP;
1789
1790 END IF;
1791
1792 pnp_debug_pkg.log('l_abat_override:'||l_abat_override);
1793 l_abated_rent := x_abated_rent;
1794 l_unabated_rent := x_abated_rent;
1795 pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
1796 pnp_debug_pkg.log('l_rec_abat:'||l_rec_abat);
1797 FOR rec IN fixed_abat_c(p_var_rent_id, l_inv_start_date, l_inv_end_date) LOOP
1798 IF (inv_rec.true_up_amt IS NULL) THEN
1799 --Fixed abatement is same sum of fixed abatement.
1800 l_fixed_abat := rec.fixed_abat * l_num_inv;
1801 ELSIF (inv_rec.true_up_amt IS NOT NULL) THEN
1802 --Fixed abatement is total * number of invoices
1803 l_fixed_abat := rec.fixed_abat * l_grp_in_prd;
1804
1805 END IF;
1806 pnp_debug_pkg.log('l_fixed_abat:'||l_fixed_abat);
1807 END LOOP;
1808 l_total_abat := l_fixed_abat + l_rec_abat;
1809 IF (l_abat_override IS NOT NULL) THEN
1810 l_total_abat := l_abat_override;
1811 END IF;
1812
1813 pnp_debug_pkg.log('l_total_abat:'||l_total_abat);
1814 IF (l_excess_abat = pn_var_rent_calc_pkg.G_EXC_ABAT_IGNORE
1815 AND x_abated_rent>0 ) THEN
1816 l_abated_rent := GREATEST(0, x_abated_rent - l_total_abat);
1817 ELSIF (l_excess_abat = pn_var_rent_calc_pkg.G_EXC_ABAT_NEG_RENT ) THEN
1818 l_abated_rent := x_abated_rent - l_total_abat;
1819 END IF;
1820 l_total_abat := l_unabated_rent - l_abated_rent;
1821 pnp_debug_pkg.log('total_abat_applied:'||l_total_abat);
1822 UPDATE pn_var_rent_inv_all
1823 SET rec_abatement = l_total_abat
1824 WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
1825 pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
1826 x_abated_rent := l_abated_rent;
1827 IF inv_rec.var_rent_inv_id IS NULL THEN
1828 G_TOT_ABATEMENT(G_TOT_ABATEMENT.COUNT+1).period_id := p_period_id;
1829 G_TOT_ABATEMENT(G_TOT_ABATEMENT.COUNT).amount := l_total_abat;
1830
1831 END IF;
1832
1833 END LOOP;
1834 pnp_debug_pkg.log('apply_abat end(-)');
1835
1836 EXCEPTION
1837 --
1838 WHEN others THEN
1839 pnp_debug_pkg.log('apply_abat end(-)');
1840 RAISE;
1841 END;
1842 --------------------------------------------------------------------------------
1843 -- NAME : apply_allow
1844 -- DESCRIPTION : Applies the rolling allowance.
1845 -- PURPOSE : Applies the rolling allowance.
1846 -- INVOKED FROM : apply_abatements()
1847 -- ARGUMENTS : p_var_rent_id: Vr to apply abatements for.
1848 -- p_period_id: Period to calculate for.
1849 -- p_inv_id: Invoice to calculate for.
1850 -- REFERENCE :
1851 -- HISTORY :
1852 --
1853 -- 25/Nov/2006 Shabda o Created
1854 --------------------------------------------------------------------------------
1855 PROCEDURE apply_allow(p_var_rent_id IN NUMBER,
1856 p_period_id IN NUMBER,
1857 p_inv_id IN NUMBER,
1858 p_allow_t IN ALLOW_TBL,
1859 p_allow_tu_t IN ALLOW_TBL,
1860 x_abated_rent IN OUT NOCOPY NUMBER
1861 ) IS
1862 -- Get the details of
1863 CURSOR invoices_c(ip_var_rent_id NUMBER,
1864 ip_period_id NUMBER,
1865 ip_inv_id NUMBER
1866 ) IS
1867 SELECT inv1.abatement_appl
1868 ,inv1.invoice_date
1869 ,inv1.var_rent_inv_id
1870 ,inv1.true_up_amt
1871 FROM pn_var_rent_inv_all inv1
1872 WHERE var_rent_id = ip_var_rent_id
1873 AND period_id = ip_period_id
1874 AND var_rent_inv_id = ip_inv_id
1875 AND inv1.adjust_num= (
1876 SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
1877 where inv1.var_rent_id = inv2.var_rent_id
1878 AND inv1.period_id = inv2.period_id
1879 AND inv1.invoice_date = inv2.invoice_date)
1880 AND TRUE_UP_AMT IS NULL
1881 UNION ALL
1882 SELECT
1883 0 abatement_appl
1884 , invoice_date
1885 , null var_rent_inv_id
1886 ,(SELECT NVL(SUM(act_var_rent), 0)
1887 FROM
1888 pn_var_rent_summ_all
1889 WHERE
1890 var_rent_id = ip_var_rent_id AND
1891 period_id = ip_period_id
1892 ) AS true_up_amt
1893 FROM PN_VAR_RENT_SUMM_ALL
1894 WHERE ip_inv_id IS NULL
1895 AND var_rent_id = ip_var_rent_id
1896 AND period_id = ip_period_id
1897 AND group_date = (select max(group_date) from pn_var_rent_summ_all
1898 where period_id = ip_period_id)
1899 AND G_IS_TU_CONC_FLAG = 'T';
1900
1901 -- Get the details of rolling allowance
1902 CURSOR rolling_allow_c(ip_var_rent_id NUMBER,
1903 ip_inv_start_date DATE,
1904 ip_inv_end_date DATE) IS
1905 SELECT NVL(amount, 0) rolling_allow
1906 ,allowance_applied allow_applied
1907 ,abatement_id
1908 FROM PN_VAR_ABAT_DEFAULTS_ALL
1909 WHERE var_rent_id = ip_var_rent_id
1910 AND start_date <= ip_inv_start_date
1911 AND NVL(end_date, ip_inv_end_date) >= ip_inv_end_date
1912 AND type_code = pn_var_rent_calc_pkg.G_ABAT_TYPE_CODE_ALLO
1913 ORDER BY start_date;
1914 -- Get the details of inv_start, end_date
1915 CURSOR invoice_dates_c(ip_var_rent_id NUMBER,
1916 ip_invoice_date DATE
1917 ) IS
1918 SELECT inv_start_date, inv_end_date
1919 FROM pn_var_grp_dates_all
1920 WHERE var_rent_id = ip_var_rent_id
1921 AND invoice_date = ip_invoice_date;
1922 --Get the details of inv_start, end_date for TU
1923 CURSOR invoice_dates_tu_c(ip_var_rent_id NUMBER,
1924 ip_period_id NUMBER
1925 ) IS
1926 SELECT MIN(inv_start_date)inv_start_date,
1927 MAX(inv_end_date) inv_end_date
1928 FROM pn_var_grp_dates_all
1929 WHERE var_rent_id = ip_var_rent_id
1930 AND period_id = ip_period_id;
1931
1932 -- Get the details of actual start and end date for FY/LY/FLY
1933 CURSOR invoice_dates_fyly_c(ip_var_rent_inv_id NUMBER
1934 ) IS
1935 SELECT per.start_date, per.end_date
1936 FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1937 WHERE per.period_id = inv.period_id
1938 AND inv.var_rent_inv_id = ip_var_rent_inv_id;
1939 -- Get the id of invoice created for first year.
1940 -- This has meaning only if proration is FY/FLY
1941 CURSOR get_fy_inv_c(ip_var_rent_id NUMBER
1942 ) IS
1943 SELECT inv.var_rent_inv_id
1944 FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1945 WHERE per.period_id = inv.period_id
1946 AND inv.var_rent_id = ip_var_rent_id
1947 AND per.start_date = (SELECT MIN(start_date) from pn_var_periods_all
1948 WHERE var_rent_id = ip_var_rent_id);
1949 -- Get the id of last invoice created.
1950 -- This has meaning only if proration is LY/FLY
1951 CURSOR get_ly_inv_c(ip_var_rent_id NUMBER
1952 ) IS
1953 SELECT inv.var_rent_inv_id
1954 FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1955 WHERE per.period_id = inv.period_id
1956 AND inv.var_rent_id = ip_var_rent_id
1957 AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
1958 WHERE var_rent_id = ip_var_rent_id)
1959 AND inv.invoice_date = (SELECT MAX(invoice_date) from pn_var_rent_inv_all
1960 WHERE var_rent_id = ip_var_rent_id);
1961
1962 -- Get the proration type
1963 CURSOR proration_type_c(ip_var_rent_id NUMBER
1964 ) IS
1965 SELECT proration_rule
1966 FROM pn_var_rents_all
1967 WHERE var_rent_id = ip_var_rent_id;
1968
1969
1970
1971 l_allow_remain NUMBER;
1972 l_allow_applied NUMBER;--Allowance applied from a specific allow
1973 l_allow_applied_inv NUMBER;--Allowance applied for a invoice
1974 l_cur_abt_rent NUMBER;--Keeps track of abt rent between allowances
1975 l_prev_abt_rent NUMBER;--Keeps track of abt rent between allowances
1976 l_unabated_rent NUMBER;
1977 l_inv_start_date DATE;
1978 l_inv_end_date DATE;
1979 l_allow_tu_t ALLOW_TBL;
1980 l_allow_rec ALLOW_REC;
1981 l_proration_type VARCHAR2(30);
1982 l_first_inv_id NUMBER;
1983 l_last_inv_id NUMBER;
1984
1985
1986 BEGIN
1987 pnp_debug_pkg.log('apply_allow start(+)');
1988
1989 -- Special cases which need to be handled are
1990 -- FY/FLY first invoice AND LY/FLY last invoice.
1991 -- get first inv
1992 --
1993 FOR first_inv_rec IN get_fy_inv_c(p_var_rent_id) LOOP
1994 l_first_inv_id := first_inv_rec.var_rent_inv_id;
1995 END LOOP;
1996
1997 FOR last_inv_rec IN get_ly_inv_c(p_var_rent_id) LOOP
1998 l_last_inv_id := last_inv_rec.var_rent_inv_id;
1999 END LOOP;
2000
2001 --
2002 FOR vr_rec IN proration_type_c(p_var_rent_id) LOOP
2003 l_proration_type := vr_rec.proration_rule;
2004 END LOOP;
2005
2006 FOR inv_rec IN invoices_c(p_var_rent_id, p_period_id, p_inv_id) LOOP
2007 pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
2008 l_allow_applied_inv := inv_rec.abatement_appl;
2009 pnp_debug_pkg.log('l_allow_applied_inv:'||l_allow_applied_inv);
2010 --
2011 IF (inv_rec.true_up_amt IS NULL) THEN
2012 -- Apply allowance to non cumm rents
2013 pnp_debug_pkg.log('Non true up invoice');
2014 FOR inv_dates_rec IN invoice_dates_c(p_var_rent_id, inv_rec.invoice_date) LOOP
2015 l_inv_start_date := inv_dates_rec.inv_start_date;
2016 l_inv_end_date := inv_dates_rec.inv_end_date;
2017 END LOOP;
2018 --Special FY/LY/FLY handling
2019 --If this invoice is FY/FLY and the first year
2020 --Or proration is LY/FLY and the last invoice
2021 IF ((l_proration_type IN (pn_var_rent_calc_pkg.G_PRORUL_FY, pn_var_rent_calc_pkg.G_PRORUL_FLY) AND p_inv_id = l_first_inv_id)
2022 OR (l_proration_type IN (pn_var_rent_calc_pkg.G_PRORUL_LY, pn_var_rent_calc_pkg.G_PRORUL_FLY) AND p_inv_id = l_last_inv_id) ) THEN
2023 --
2024 FOR inv_rec IN invoice_dates_fyly_c(p_inv_id) LOOP
2025 l_inv_start_date := inv_rec.start_date;
2026 l_inv_end_date := inv_rec.end_date;
2027 END LOOP;
2028 END IF;
2029 l_cur_abt_rent := x_abated_rent;
2030 l_prev_abt_rent := x_abated_rent;
2031 FOR allow_rec IN rolling_allow_c(p_var_rent_id, l_inv_start_date, l_inv_end_date) LOOP
2032 --Allowances can only be applied if rent is >0
2033 IF (l_cur_abt_rent > 0) THEN
2034 pnp_debug_pkg.log('allow_rec.abatement_id'||allow_rec.abatement_id);
2035 l_allow_remain := allow_rec.rolling_allow -NVL(allow_rec.allow_applied,0);
2036 pnp_debug_pkg.log('l_allow_remain:'||l_allow_remain);
2037 l_cur_abt_rent := GREATEST(0,l_prev_abt_rent - l_allow_remain);
2038 pnp_debug_pkg.log('l_cur_abated_rent:'||l_cur_abt_rent);
2039 l_allow_applied := l_prev_abt_rent - l_cur_abt_rent;
2040 pnp_debug_pkg.log('l_allow_applied'||l_allow_applied);
2041 l_prev_abt_rent := l_cur_abt_rent;
2042 pnp_debug_pkg.log('l_prev_abt_rent:'||l_prev_abt_rent);
2043 l_allow_applied_inv := l_allow_applied_inv + l_allow_applied;
2044 pnp_debug_pkg.log('l_allow_applied_inv:'||l_allow_applied_inv);
2045 UPDATE pn_var_abat_defaults_all
2046 SET allowance_applied = NVL(allowance_applied,0)+l_allow_applied
2047 WHERE abatement_id = allow_rec.abatement_id;
2048 END IF;
2049 END LOOP;
2050 pnp_debug_pkg.log('l_cur_abt_rent:'||l_cur_abt_rent);
2051 UPDATE pn_var_rent_inv_all
2052 SET abatement_appl = l_allow_applied_inv
2053 WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
2054 x_abated_rent := l_cur_abt_rent;
2055 pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
2056
2057 ELSIF (inv_rec.true_up_amt IS NOT NULL) THEN
2058 pnp_debug_pkg.log('true up invoice');
2059 -- Apply allowance to true up rents
2060 FOR inv_dates_rec IN invoice_dates_tu_c(p_var_rent_id, p_period_id) LOOP
2061 l_inv_start_date := inv_dates_rec.inv_start_date;
2062 l_inv_end_date := inv_dates_rec.inv_end_date;
2063 END LOOP;
2064 l_cur_abt_rent := x_abated_rent;
2065 l_prev_abt_rent := x_abated_rent;
2066 l_allow_tu_t := get_dated_allow(p_allow_tu_t, l_inv_start_date, l_inv_end_date);
2067 IF (l_allow_tu_t(1).abatement_id IS NULL) THEN
2068 --This will happen when there are now allowances. In this case we can skip
2069 --processing for this invoice.
2070 pnp_debug_pkg.log('No allowances for this invoice. Exit');
2071 RETURN;
2072 END IF;
2073
2074 FOR i IN l_allow_tu_t.FIRST..l_allow_tu_t.LAST LOOP
2075 --Allowances can only be applied if rent is >0
2076 l_allow_rec := l_allow_tu_t(i);
2077 IF (l_cur_abt_rent > 0) THEN
2078 pnp_debug_pkg.log('l_allow_rec.abatement_id'||l_allow_rec.abatement_id);
2079 l_allow_remain := l_allow_rec.rolling_allow -NVL(l_allow_rec.allow_applied,0);
2080 pnp_debug_pkg.log('l_allow_remain:'||l_allow_remain);
2081 l_cur_abt_rent := GREATEST(0,l_prev_abt_rent - l_allow_remain);
2082 pnp_debug_pkg.log('l_cur_abated_rent:'||l_cur_abt_rent);
2083 l_allow_applied := l_prev_abt_rent - l_cur_abt_rent;
2084 pnp_debug_pkg.log('l_allow_applied'||l_allow_applied);
2085 l_prev_abt_rent := l_cur_abt_rent;
2086 pnp_debug_pkg.log('l_prev_abt_rent:'||l_prev_abt_rent);
2087 l_allow_applied_inv := l_allow_applied_inv + l_allow_applied;
2088 pnp_debug_pkg.log('l_allow_applied_inv:'||l_allow_applied_inv);
2089 l_allow_tu_t(i).allow_applied := NVL(l_allow_tu_t(i).allow_applied,0)+l_allow_applied;
2090 END IF;
2091 END LOOP;
2092 pnp_debug_pkg.log('l_cur_abt_rent:'||l_cur_abt_rent);
2093 G_ALLOWANCE_APPLIED(G_ALLOWANCE_APPLIED.COUNT+1).period_id := p_period_id;
2094 G_ALLOWANCE_APPLIED(G_ALLOWANCE_APPLIED.COUNT).amount := l_allow_applied_inv;
2095 pnp_debug_pkg.log('***l_allow_applied_inv:'||l_allow_applied_inv);
2096
2097
2098 x_abated_rent := l_cur_abt_rent;
2099 --Whenever we have true up invoice, we need to reset allowances.
2100 --
2101 FOR i IN l_allow_tu_t.FIRST..l_allow_tu_t.LAST LOOP
2102 UPDATE pn_var_abat_defaults_all
2103 SET allowance_applied = l_allow_tu_t(i).allow_applied
2104 WHERE abatement_id = l_allow_tu_t(i).abatement_id;
2105 END LOOP;
2106
2107 pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
2108 END IF;
2109
2110 END LOOP;
2111 pnp_debug_pkg.log('apply_allow end(-)');
2112 EXCEPTION
2113 WHEN others THEN
2114 RAISE;
2115 END;
2116
2117
2118 --------------------------------------------------------------------------------
2119 -- NAME : populate_abat
2120 -- DESCRIPTION : Populates the recurring abatements to abte in
2121 -- pn_var_rent_inv_all.rec_abatement.
2122 -- PURPOSE :
2123 -- INVOKED FROM : apply_abatements()
2124 -- ARGUMENTS : p_var_rent_id: Vr to apply abatements for.
2125 -- p_period_id: Period to calculate for.
2126 -- p_inv_id: Invoice to calculate for.
2127 -- REFERENCE :
2128 -- HISTORY :
2129 --
2130 -- 25/Nov/2006 Shabda o Created
2131 -- 29/may/07 Shabda o Bug 6041521- call overloaded calc_abatement
2132 --------------------------------------------------------------------------------
2133 PROCEDURE populate_abat(p_var_rent_id IN NUMBER,
2134 p_period_id IN NUMBER,
2135 p_inv_id IN NUMBER) IS
2136
2137 -- Get the details of all invoices
2138 CURSOR invoices_c(ip_var_rent_id NUMBER, ip_period_id NUMBER,
2139 ip_inv_id NUMBER
2140 ) IS
2141 SELECT invoice_date
2142 ,true_up_amt
2143 ,var_rent_inv_id
2144 FROM pn_var_rent_inv_all inv1
2145 WHERE inv1.var_rent_id = ip_var_rent_id
2146 AND inv1.period_id = ip_period_id
2147 AND var_rent_inv_id = ip_inv_id
2148 AND inv1.adjust_num= (
2149 SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
2150 where inv1.var_rent_id = inv2.var_rent_id
2151 AND inv1.invoice_date = inv2.invoice_date)
2152 AND TRUE_UP_AMT IS NULL
2153 UNION ALL
2154 SELECT
2155 invoice_date
2156 ,(SELECT NVL(SUM(act_var_rent), 0)
2157 FROM
2158 pn_var_rent_summ_all
2159 WHERE
2160 var_rent_id = ip_var_rent_id AND
2161 period_id = ip_period_id
2162 ) AS true_up_amt
2163 ,null var_rent_inv_id
2164 FROM PN_VAR_RENT_SUMM_ALL
2165 WHERE ip_inv_id IS NULL
2166 AND var_rent_id = ip_var_rent_id
2167 AND period_id = ip_period_id
2168 AND group_date = (select max(group_date) from pn_var_rent_summ_all
2169 where period_id = ip_period_id)
2170 AND G_IS_TU_CONC_FLAG = 'T';
2171
2172 --Get the last and first invoice dates for this period.
2173 CURSOR csr_min_gd(ip_var_rent_id NUMBER, ip_period_id NUMBER) IS
2174 SELECT MIN(grp_start_date) min_date
2175 FROM pn_var_grp_dates_all
2176 WHERE period_id = ip_period_id
2177 AND var_rent_id = ip_var_rent_id;
2178
2179 CURSOR csr_max_gd(ip_var_rent_id NUMBER, ip_period_id NUMBER) IS
2180 SELECT MAX(grp_end_date) max_date
2181 FROM pn_var_grp_dates_all
2182 WHERE period_id = ip_period_id
2183 AND var_rent_id = ip_var_rent_id;
2184
2185 CURSOR get_first_tu_c(ip_period_id NUMBER
2186 ) IS
2187 SELECT var_rent_inv_id
2188 FROM pn_var_rent_inv_all
2189 WHERE period_id = ip_period_id
2190 AND true_up_amt is NOT NULL
2191 AND adjust_num = 0;
2192
2193
2194 l_min_grp_date DATE;
2195 l_max_grp_date DATE;
2196 l_rec_abatement NUMBER;
2197 l_exists_period BOOLEAN := FALSE;
2198 l_tu_inv_id NUMBER;
2199
2200 BEGIN
2201
2202 FOR inv_rec IN invoices_c(p_var_rent_id , p_period_id, p_inv_id) LOOP
2203 -- get the first grp date for this period
2204 IF (inv_rec.true_up_amt IS NOT NULL) THEN
2205
2206 --
2207 FOR rec IN get_first_tu_c(p_period_id) LOOP
2208 l_tu_inv_id := rec.var_rent_inv_id;
2209 pnp_debug_pkg.log('l_tu_inv_id:'||l_tu_inv_id);
2210
2211 END LOOP;
2212
2213 --Populate for TU
2214 FOR min_rec IN csr_min_gd(p_var_rent_id , p_period_id) LOOP
2215 l_min_grp_date := min_rec.min_date;
2216 END LOOP;
2217
2218 FOR max_rec IN csr_max_gd(p_var_rent_id , p_period_id) LOOP
2219 l_max_grp_date := max_rec.max_date;
2220 END LOOP;
2221 l_rec_abatement := pn_var_abatement_amount_pkg.calc_abatement(
2222 p_var_rent_id
2223 ,p_period_id
2224 ,l_tu_inv_id
2225 ,l_min_grp_date
2226 ,l_max_grp_date
2227 ,'Y');
2228 G_ABATEMENT_APPLIED(G_ABATEMENT_APPLIED.COUNT+1).period_id := p_period_id;
2229 G_ABATEMENT_APPLIED(G_ABATEMENT_APPLIED.COUNT).amount := l_rec_abatement;
2230 pnp_debug_pkg.log('G_ABATEMENT_APPLIED(G_ABATEMENT_APPLIED.COUNT).period_id'||G_ABATEMENT_APPLIED(G_ABATEMENT_APPLIED.COUNT).period_id);
2231 pnp_debug_pkg.log('G_ABATEMENT_APPLIED(G_ABATEMENT_APPLIED.COUNT).amount'||G_ABATEMENT_APPLIED(G_ABATEMENT_APPLIED.COUNT).amount);
2232
2233 --
2234 /* FOR i IN 1..G_REC_ABATEMENT.COUNT LOOP
2235 IF G_REC_ABATEMENT(i).PERIOD_ID = p_period_id THEN
2236 G_REC_ABATEMENT(i).AMOUNT = G_REC_ABATEMENT(i).AMOUNT + l_rec_abatement;
2237 l_exists_period := TRUE:
2238 END IF;
2239
2240 END LOOP;
2241
2242 IF NOT l_exists_period THEN
2243 G_REC_ABATEMENT(G_REC_ABATEMENT.COUNT+1).period_id := p_period_id;
2244 G_REC_ABATEMENT(G_REC_ABATEMENT.COUNT).AMOUNT := l_rec_abatement;
2245 END IF;*/
2246
2247
2248 ELSE
2249 --Populate for non cumulative. Just call the normal populate_abat
2250 pn_var_rent_calc_pkg.populate_abat(p_var_rent_id, p_period_id, p_inv_id);
2251
2252 END IF;
2253 END LOOP;
2254
2255 EXCEPTION
2256 --
2257 WHEN others THEN
2258 RAISE;
2259 END;
2260
2261 --------------------------------------------------------------------------------
2262 -- NAME : reset_abatements
2263 -- DESCRIPTION : Resets the allowance applied for each invoice, allowance.
2264 -- This needs to be called between subsequent calls
2265 -- to apply_abatements.
2266 -- PURPOSE : resets the allowances.
2267 -- INVOKED FROM : apply_abatements()
2268 -- ARGUMENTS : p_var_rent_id: Vr to apply abatements for.
2269 -- p_period_id: Period to calculate for.
2270 -- p_inv_id: Invoice to calculate for.
2271 -- REFERENCE :
2272 -- HISTORY :
2273 --
2274 -- 25/Nov/2006 Shabda o Created
2275 --------------------------------------------------------------------------------
2276 PROCEDURE reset_abatements(p_var_rent_id IN NUMBER
2277 ) IS
2278 BEGIN
2279 pnp_debug_pkg.log('Reset_abatement start(+)');
2280 UPDATE pn_var_abat_defaults_all
2281 SET allowance_applied =0
2282 WHERE var_rent_id = p_var_rent_id;
2283 UPDATE pn_var_rent_inv_all
2284 SET abatement_appl =0
2285 WHERE var_rent_id = p_var_rent_id;
2286 pnp_debug_pkg.log('Reset_abatement end(-)');
2287
2288 EXCEPTION
2289 WHEN others THEN
2290 RAISE;
2291 END;
2292
2293
2294
2295 --------------------------------------------------------------------------------
2296 -- NAME : calculate_trueup
2297 -- DESCRIPTION :
2298 -- PURPOSE :
2299 -- INVOKED FROM :
2300 -- ARGUMENTS :
2301 -- REFERENCE : PN_COMMON.debug()
2302 -- HISTORY :
2303 --
2304 -- dd-mon-yyyy name o Created
2305 -- 23/05/07 Lokesh o Modified for bug # 6031202, added rounding off for
2306 -- TRUEUP_RENT_DUE
2307 --------------------------------------------------------------------------------
2308 PROCEDURE calculate_trueup( p_var_rent_id IN NUMBER
2309 ,p_prd_date IN DATE)
2310 IS
2311
2312 CURSOR vr_c(p_vr_id IN NUMBER) IS
2313 SELECT
2314 proration_rule
2315 ,cumulative_vol
2316 ,negative_rent
2317 ,commencement_date
2318 ,termination_date
2319 ,org_id
2320 FROM
2321 pn_var_rents_all
2322 WHERE
2323 var_rent_id = p_vr_id;
2324
2325 l_vr_start_date DATE;
2326 l_vr_end_date DATE;
2327 l_proration_rule VARCHAR2(30);
2328 l_calc_method VARCHAR2(30);
2329 l_org_id pn_var_rents_all.org_id%TYPE;
2330
2331 CURSOR periods_c( p_vr_id IN NUMBER
2332 ,p_date IN DATE) IS
2333 SELECT
2334 org_id
2335 ,period_id
2336 ,start_date
2337 ,end_date
2338 ,partial_period
2339 FROM
2340 pn_var_periods_all
2341 WHERE
2342 var_rent_id = p_vr_id AND
2343 end_date <= p_prd_date
2344 AND NVL(status, pn_var_rent_calc_pkg.G_PERIOD_ACTIVE_STATUS)
2345 <> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS;
2346
2347 -- Get the details of periods to calculate abatements for.
2348 -- We need to calculate abatements for all periods.
2349 -- The idea is that True up calc runs from start to a specific period
2350 -- So we need to run abatements till that period. Now the abatements after that
2351 -- periods might have changed so we need to apply_abatements to subsequent periods.
2352 CURSOR periods_abat_c(ip_var_rent_id NUMBER
2353 ) IS
2354 SELECT period_id
2355 FROM pn_var_periods_all
2356 WHERE var_rent_id = ip_var_rent_id
2357 AND NVL(status, pn_var_rent_calc_pkg.G_PERIOD_ACTIVE_STATUS)
2358 <> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS;
2359
2360
2361
2362 l_prd_counter NUMBER;
2363
2364 /* get line items for a period */
2365 CURSOR lines_c( p_vr_id IN NUMBER
2366 ,p_prd_id IN NUMBER) IS
2367 SELECT
2368 line_item_id
2369 FROM
2370 pn_var_trx_headers_all
2371 WHERE
2372 var_rent_id = p_vr_id AND
2373 period_id = p_prd_id
2374 GROUP BY
2375 line_item_id;
2376
2377 /* get the dates when we need to create the trueup invoice */
2378 CURSOR trueup_trx_c( p_vr_id IN NUMBER
2379 ,p_prd_id IN NUMBER
2380 ,p_line_id IN NUMBER) IS
2381 SELECT
2382 MIN(calc_prd_start_date) AS trueup_start_date
2383 ,MAX(calc_prd_end_date) AS trueup_end_date
2384 ,reset_group_id
2385 FROM
2386 pn_var_trx_headers_all
2387 WHERE
2388 var_rent_id = p_vr_id AND
2389 period_id = p_prd_id AND
2390 line_item_id = p_line_id
2391 GROUP BY
2392 reset_group_id
2393 ORDER BY
2394 trueup_start_date;
2395
2396 /* get YTD sales for the gives calc prd end date */
2397 CURSOR ytd_sales_c( p_vr_id IN NUMBER
2398 ,p_prd_id IN NUMBER
2399 ,p_line_id IN NUMBER
2400 ,p_end_dt IN DATE) IS
2401 SELECT
2402 ytd_sales
2403 FROM
2404 pn_var_trx_headers_all
2405 WHERE
2406 var_rent_id = p_vr_id AND
2407 period_id = p_prd_id AND
2408 line_item_id = p_line_id AND
2409 calc_prd_end_date = p_end_dt;
2410
2411 l_net_trueup_volume NUMBER;
2412 l_tot_trueup_rent NUMBER;
2413 l_line_trueup_rent NUMBER;
2414 l_part_trueup_rent NUMBER;
2415
2416 /* get the bkpts for trueup */
2417 CURSOR trueup_bkpt_c( p_vr_id IN NUMBER
2418 ,p_prd_id IN NUMBER
2419 ,p_line_id IN NUMBER
2420 ,p_reset_grp_id IN NUMBER
2421 ,p_end_dt IN DATE) IS
2422 SELECT
2423 ytd_group_vol_start AS trueup_bkpt_vol_start
2424 ,ytd_group_vol_end AS trueup_bkpt_vol_end
2425 ,bkpt_rate
2426 FROM
2427 pn_var_trx_details_all
2428 WHERE
2429 trx_header_id IN (SELECT
2430 trx_header_id
2431 FROM
2432 pn_var_trx_headers_all
2433 WHERE
2434 var_rent_id = p_vr_id AND
2435 period_id = p_prd_id AND
2436 line_item_id = p_line_id AND
2437 reset_group_id = p_reset_grp_id AND
2438 calc_prd_end_date = p_end_dt)
2439 ORDER BY
2440 trueup_bkpt_vol_start;
2441
2442 TYPE TRUEUP_BKPT_TBL IS TABLE OF trueup_bkpt_c%ROWTYPE INDEX BY BINARY_INTEGER;
2443
2444 trueup_bkpt_t TRUEUP_BKPT_TBL;
2445
2446 CURSOR bkpt_type_c( p_line_id IN NUMBER
2447 ,p_start_dt IN DATE
2448 ,p_end_dt IN DATE) IS
2449 SELECT
2450 bkhd.bkpt_header_id
2451 ,bkhd.breakpoint_type
2452 FROM
2453 pn_var_bkpts_head_all bkhd
2454 WHERE
2455 bkhd.line_item_id = p_line_id AND
2456 bkhd_start_date <= p_end_dt AND
2457 bkhd_end_date >= p_start_dt;
2458
2459
2460 l_bkpt_type VARCHAR2(30);
2461 l_bkpt_start NUMBER;
2462 l_bkpt_end NUMBER;
2463 l_neg_rent_flag VARCHAR2(30);
2464
2465 BEGIN
2466
2467 pnp_debug_pkg.log('+++++ calculate_trueup - START +++++');
2468 pnp_debug_pkg.log(' ');
2469
2470 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
2471
2472 l_proration_rule := vr_rec.proration_rule;
2473 l_calc_method := vr_rec.cumulative_vol;
2474 l_vr_start_date := vr_rec.commencement_date;
2475 l_vr_end_date := vr_rec.termination_date;
2476 l_neg_rent_flag := vr_rec.negative_rent;
2477 l_org_id := vr_rec.org_id;
2478
2479 END LOOP;
2480
2481 g_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(l_org_id),4);
2482
2483 l_prd_counter := 0;
2484
2485 /* loop for all periods ending before p_prd_date */
2486 FOR prd_rec IN periods_c( p_vr_id => p_var_rent_id
2487 ,p_date => p_prd_date)
2488 LOOP
2489
2490 l_prd_counter := l_prd_counter + 1;
2491
2492 pnp_debug_pkg.log('Period Details: ');
2493 pnp_debug_pkg.log(' Period # : '||l_prd_counter);
2494 pnp_debug_pkg.log(' Period Start: '||prd_rec.start_date);
2495 pnp_debug_pkg.log(' Period End : '||prd_rec.end_date);
2496 pnp_debug_pkg.log(' ');
2497
2498 /* init the trueup rent for the period */
2499 l_tot_trueup_rent := 0;
2500 --TODO
2501 G_ABATEMENT_APPLIED.DELETE;
2502 G_ALLOWANCE_APPLIED.DELETE;
2503 G_ABATED_RENT.DELETE;
2504 G_UNABATED_RENT.DELETE;
2505 G_TOT_ABATEMENT.DELETE;
2506
2507 /* check if we need to calculate TRUE UP */
2508 IF prd_rec.start_date = l_vr_start_date AND
2509 l_proration_rule IN ( pn_var_rent_calc_pkg.G_PRORUL_FY
2510 ,pn_var_rent_calc_pkg.G_PRORUL_FLY
2511 ,pn_var_rent_calc_pkg.G_PRORUL_CYP
2512 ,pn_var_rent_calc_pkg.G_PRORUL_CYNP) AND
2513 prd_rec.partial_period = 'Y'
2514 THEN
2515
2516 /* no true up for the first partial in case of
2517 FY, FLY, CYP, CYNP
2518 */
2519 NULL;
2520
2521 ELSIF prd_rec.end_date = l_vr_end_date AND
2522 l_proration_rule IN ( pn_var_rent_calc_pkg.G_PRORUL_LY
2523 ,pn_var_rent_calc_pkg.G_PRORUL_FLY) AND
2524 prd_rec.partial_period = 'Y'
2525 THEN
2526
2527 /* no true up for the last partial in case of
2528 LY, FLY
2529 */
2530 NULL;
2531
2532 ELSIF pn_var_trueup_pkg.can_do_trueup
2533 ( p_var_rent_id => p_var_rent_id
2534 ,p_period_id => prd_rec.period_id)
2535 THEN
2536 /* no true up if calculation not done for all invoiceing periods
2537 assuming all invoicing periods will have some sales populated
2538 - need to validate this
2539 */
2540
2541 /* loop for all lines in the period */
2542 FOR line_rec IN lines_c ( p_vr_id => p_var_rent_id
2543 ,p_prd_id => prd_rec.period_id) LOOP
2544
2545 /* re init the TRUEUP amount */
2546 UPDATE
2547 pn_var_trx_headers_all
2548 SET
2549 trueup_rent_due = 0
2550 WHERE
2551 var_rent_id = p_var_rent_id AND
2552 period_id = prd_rec.period_id AND
2553 line_item_id = line_rec.line_item_id;
2554
2555 /* now start re-calculating the TRUEUP again */
2556 l_line_trueup_rent := 0;
2557
2558 /* loop for all resets for a line */
2559 FOR trueup_rec IN trueup_trx_c( p_vr_id => p_var_rent_id
2560 ,p_prd_id => prd_rec.period_id
2561 ,p_line_id => line_rec.line_item_id)
2562 LOOP
2563
2564 l_part_trueup_rent := 0;
2565
2566 /* get YTD sales for trueup_rec.trueup_end_date */
2567 FOR sales_rec IN ytd_sales_c( p_vr_id => p_var_rent_id
2568 ,p_prd_id => prd_rec.period_id
2569 ,p_line_id => line_rec.line_item_id
2570 ,p_end_dt => trueup_rec.trueup_end_date)
2571 LOOP
2572 l_net_trueup_volume := NVL(sales_rec.ytd_sales,0);
2573 END LOOP; /* get YTD sales for trueup_rec.trueup_end_date */
2574
2575 IF l_net_trueup_volume <> 0 THEN
2576
2577 /* get bkpts */
2578 trueup_bkpt_t.DELETE;
2579
2580 OPEN trueup_bkpt_c( p_vr_id => p_var_rent_id
2581 ,p_prd_id => prd_rec.period_id
2582 ,p_line_id => line_rec.line_item_id
2583 ,p_reset_grp_id => trueup_rec.reset_group_id
2584 ,p_end_dt => trueup_rec.trueup_end_date);
2585 FETCH trueup_bkpt_c BULK COLLECT INTO trueup_bkpt_t;
2586 CLOSE trueup_bkpt_c; /* get bkpts */
2587
2588 IF trueup_bkpt_t.COUNT > 0 THEN
2589
2590 FOR bkpt_hdr_rec IN bkpt_type_c ( p_line_id => line_rec.line_item_id
2591 ,p_start_dt => trueup_rec.trueup_start_date
2592 ,p_end_dt => trueup_rec.trueup_end_date)
2593 LOOP
2594 l_bkpt_type := bkpt_hdr_rec.breakpoint_type;
2595 END LOOP;
2596
2597 /* net volume trips any bkpt? */
2598 IF l_net_trueup_volume < trueup_bkpt_t(1).trueup_bkpt_vol_start THEN
2599
2600 /* this is the functionality that exists today
2601 does not exist in Macerich code
2602 Once no breakpoints are tripped, Macerich consider the rent to be = 0 */
2603 /*Well no, because in case of true up the non cumulative calculations can be negative,
2604 but the true up calculations which are similar to cumulative can not be.
2605 They are not even deferred. - Shabda*/
2606
2607 IF (l_neg_rent_flag = 'IGNORE') THEN
2608 l_part_trueup_rent := 0;
2609 ELSE
2610 l_part_trueup_rent
2611 := (l_net_trueup_volume - trueup_bkpt_t(1).trueup_bkpt_vol_start)
2612 * trueup_bkpt_t(1).bkpt_rate;
2613 END IF;
2614
2615
2616
2617
2618
2619 /* net volume trips any bkpt? - YES */
2620 ELSE
2621
2622 /* get rent based on breakpoint type */
2623 IF l_bkpt_type = pn_var_rent_calc_pkg.G_BKPT_TYP_STRATIFIED
2624 THEN
2625
2626 l_part_trueup_rent := 0;
2627
2628 /* loop for all bkpt details */
2629 FOR i IN trueup_bkpt_t.FIRST..trueup_bkpt_t.LAST LOOP
2630
2631 l_bkpt_start := trueup_bkpt_t(i).trueup_bkpt_vol_start;
2632 l_bkpt_end := trueup_bkpt_t(i).trueup_bkpt_vol_end;
2633
2634 IF l_bkpt_end IS NULL OR l_bkpt_end = 0 THEN
2635 l_bkpt_end := NULL;
2636 END IF;
2637
2638 /* net vol > bkpt start */
2639 IF l_net_trueup_volume >= l_bkpt_start THEN
2640
2641 IF l_net_trueup_volume
2642 <= NVL(l_bkpt_end, l_net_trueup_volume)
2643 THEN
2644
2645 l_part_trueup_rent
2646 := l_part_trueup_rent
2647 + (l_net_trueup_volume - l_bkpt_start)
2648 * trueup_bkpt_t(i).bkpt_rate;
2649
2650 ELSIF l_net_trueup_volume > l_bkpt_end THEN
2651
2652 l_part_trueup_rent
2653 := l_part_trueup_rent
2654 + (l_bkpt_end - l_bkpt_start)
2655 * trueup_bkpt_t(i).bkpt_rate;
2656
2657 END IF;
2658
2659 ELSE
2660
2661 EXIT;
2662
2663 END IF; /* net vol > bkpt start */
2664
2665 END LOOP; /* loop for all bkpt details */
2666
2667 ELSIF l_bkpt_type IN ( pn_var_rent_calc_pkg.G_BKPT_TYP_FLAT
2668 ,pn_var_rent_calc_pkg.G_BKPT_TYP_SLIDING)
2669 THEN
2670
2671 /* loop for all bkpt details */
2672 FOR i IN trueup_bkpt_t.FIRST..trueup_bkpt_t.LAST LOOP
2673
2674 l_bkpt_start := trueup_bkpt_t(i).trueup_bkpt_vol_start;
2675 l_bkpt_end := trueup_bkpt_t(i).trueup_bkpt_vol_end;
2676
2677 IF l_bkpt_end IS NULL OR l_bkpt_end = 0 THEN
2678 l_bkpt_end := NULL;
2679 END IF;
2680
2681 IF l_net_trueup_volume >= l_bkpt_start AND
2682 l_net_trueup_volume <= NVL(l_bkpt_end, l_net_trueup_volume)
2683 THEN
2684
2685 IF l_bkpt_type = pn_var_rent_calc_pkg.G_BKPT_TYP_SLIDING THEN
2686
2687 l_part_trueup_rent
2688 := l_net_trueup_volume * trueup_bkpt_t(i).bkpt_rate;
2689
2690 ELSIF l_bkpt_type = pn_var_rent_calc_pkg.G_BKPT_TYP_FLAT THEN
2691
2692 l_part_trueup_rent
2693 := (l_net_trueup_volume - l_bkpt_start)
2694 * trueup_bkpt_t(i).bkpt_rate;
2695
2696 END IF;
2697
2698 EXIT;
2699
2700 END IF;
2701
2702 END LOOP; /* loop for all bkpt details */
2703
2704 END IF; /* get rent based on breakpoint type */
2705
2706 END IF; /* net volume trips any bkpt? */
2707
2708 END IF; /* IF trueup_bkpt_t.COUNT > 0 THEN */
2709
2710 END IF; /* IF l_net_trueup_volume <> 0 THEN */
2711
2712 pnp_debug_pkg.log('');
2713 /* update the line trueup rent */
2714 l_line_trueup_rent := l_line_trueup_rent + l_part_trueup_rent;
2715
2716 pnp_debug_pkg.log(' part_trueup_rent: '||l_part_trueup_rent);
2717 pnp_debug_pkg.log(' ');
2718
2719 END LOOP; /* loop for all resets for a line */
2720
2721 l_tot_trueup_rent := l_tot_trueup_rent + l_line_trueup_rent;
2722
2723 UPDATE
2724 pn_var_trx_headers_all
2725 SET
2726 trueup_rent_due = round(l_line_trueup_rent,g_precision)
2727 WHERE
2728 var_rent_id = p_var_rent_id AND
2729 period_id = prd_rec.period_id AND
2730 line_item_id = line_rec.line_item_id AND
2731 calc_prd_end_date = prd_rec.end_date;
2732
2733 pnp_debug_pkg.log(' line_trueup_rent: '||l_line_trueup_rent);
2734 pnp_debug_pkg.log(' ');
2735
2736 END LOOP; /* loop for all lines in the period */
2737
2738 pnp_debug_pkg.log(' tot_trueup_rent for Period : '||l_tot_trueup_rent);
2739 pnp_debug_pkg.log(' ');
2740 pn_var_trueup_pkg.post_summary_trueup
2741 ( p_var_rent_id => p_var_rent_id
2742 ,p_period_id => prd_rec.period_id
2743 ,p_proration_rule => l_proration_rule);
2744
2745 END IF; /* check if we need to calculate TRUE UP */
2746
2747 --Rest the abatements, and reapply them.
2748 pn_var_rent_calc_pkg.reset_abatements(p_var_rent_id);
2749
2750 END LOOP;
2751
2752 FOR period_rec IN periods_abat_c(p_var_rent_id) LOOP
2753 apply_abatements(p_var_rent_id,
2754 period_rec.period_id,
2755 'CALCULATE');
2756 END LOOP;
2757
2758 FOR prd_rec IN periods_c( p_vr_id => p_var_rent_id
2759 ,p_date => p_prd_date) LOOP
2760
2761 IF NOT ((prd_rec.start_date = l_vr_start_date AND
2762 l_proration_rule IN (pn_var_rent_calc_pkg.G_PRORUL_CYP
2763 ,pn_var_rent_calc_pkg.G_PRORUL_CYNP
2764 ,pn_var_rent_calc_pkg.G_PRORUL_FY
2765 ,pn_var_rent_calc_pkg.G_PRORUL_FLY) AND
2766 prd_rec.partial_period = 'Y')
2767 OR (prd_rec.end_date = l_vr_end_date AND
2768 l_proration_rule IN (pn_var_rent_calc_pkg.G_PRORUL_LY
2769 ,pn_var_rent_calc_pkg.G_PRORUL_FLY) AND
2770 prd_rec.partial_period = 'Y'))
2771 THEN
2772 pn_var_trueup_pkg.insert_invoice_trueup
2773 ( p_var_rent_id => p_var_rent_id
2774 ,p_period_id => prd_rec.period_id);
2775 END IF;
2776 END LOOP;
2777
2778 EXCEPTION
2779 WHEN OTHERS THEN RAISE;
2780
2781 END calculate_trueup;
2782
2783 --------------------------------------------------------------------------------
2784 -- NAME : trueup_batch_process
2785 -- DESCRIPTION :
2786 -- PURPOSE :
2787 -- INVOKED FROM :
2788 -- ARGUMENTS :
2789 -- REFERENCE : PN_COMMON.debug()
2790 -- HISTORY :
2791 --
2792 -- dd-mon-yyyy name o Created
2793 --------------------------------------------------------------------------------
2794 PROCEDURE trueup_batch_process( errbuf OUT NOCOPY VARCHAR2
2795 ,retcode OUT NOCOPY VARCHAR2
2796 ,p_property_code IN VARCHAR2
2797 ,p_lease_num_low IN VARCHAR2
2798 ,p_lease_num_high IN VARCHAR2
2799 ,p_vr_num_low IN VARCHAR2
2800 ,p_vr_num_high IN VARCHAR2
2801 ,p_date IN VARCHAR2)
2802 IS
2803
2804 l_lease_num_low VARCHAR2(30);
2805 l_lease_num_high VARCHAR2(30);
2806 l_vr_num_low VARCHAR2(30);
2807 l_vr_num_high VARCHAR2(30);
2808 l_date DATE;
2809
2810 /* get the VR to do trueup for */
2811 CURSOR get_vr_c IS
2812 SELECT
2813 vr.var_rent_id
2814 FROM
2815 pn_leases_all lease
2816 ,pn_var_rents_all vr
2817 WHERE
2818 vr.cumulative_vol = 'T' AND
2819 vr.lease_id = lease.lease_id AND
2820 lease.lease_num BETWEEN l_lease_num_low AND l_lease_num_high AND
2821 vr.rent_num BETWEEN l_vr_num_low AND l_vr_num_high
2822 ORDER BY
2823 vr.rent_num;
2824
2825 /* get the VR to do trueup for - used when property code is passed */
2826 CURSOR get_vr_prop_c(p_building_id IN NUMBER) IS
2827 SELECT
2828 vr.var_rent_id
2829 FROM
2830 pn_leases_all lease
2831 ,pn_var_rents_all vr
2832 WHERE
2833 vr.cumulative_vol = pn_var_rent_calc_pkg.G_CALC_TRUE_UP AND
2834 vr.lease_id = lease.lease_id AND
2835 lease.lease_num BETWEEN l_lease_num_low AND l_lease_num_high AND
2836 vr.rent_num BETWEEN l_vr_num_low AND l_vr_num_high AND
2837 vr.location_id IN
2838 (SELECT
2839 location_id
2840 FROM
2841 pn_locations_all
2842 START WITH location_id = p_building_id
2843 CONNECT BY PRIOR location_id = parent_location_id)
2844 ORDER BY
2845 vr.rent_num;
2846
2847 CURSOR get_buildings_c(p_prop_code IN VARCHAR2) IS
2848 SELECT DISTINCT
2849 loc.location_id
2850 FROM
2851 pn_locations_all loc
2852 ,pn_properties_all prop
2853 WHERE
2854 loc.property_id = prop.property_id AND
2855 prop.property_code = p_prop_code;
2856
2857 BEGIN
2858 pnp_debug_pkg.log('+++*process_trueup_batch*++++++');
2859 /* init */
2860 IF p_lease_num_low IS NOT NULL THEN
2861 l_lease_num_low := p_lease_num_low;
2862 ELSE
2863 l_lease_num_low := ' ';
2864 END IF;
2865
2866 IF p_lease_num_high IS NOT NULL THEN
2867 l_lease_num_high := p_lease_num_high;
2868 ELSE
2869 BEGIN
2870 SELECT MAX(lease_num)
2871 INTO l_lease_num_high
2872 FROM pn_leases;
2873 EXCEPTION
2874 WHEN OTHERS THEN RAISE;
2875 END;
2876 END IF;
2877
2878 IF p_vr_num_low IS NOT NULL THEN
2879 l_vr_num_low := p_vr_num_low;
2880 ELSE
2881 l_vr_num_low := ' ';
2882 END IF;
2883
2884 IF p_vr_num_high IS NOT NULL THEN
2885 l_vr_num_high := p_vr_num_high;
2886 ELSE
2887 BEGIN
2888 SELECT MAX(rent_num)
2889 INTO l_vr_num_high
2890 FROM pn_var_rents;
2891 EXCEPTION
2892 WHEN OTHERS THEN RAISE;
2893 END;
2894 END IF;
2895
2896 IF p_date IS NOT NULL THEN
2897 l_date := fnd_date.canonical_to_date(p_date);
2898 ELSIF p_date IS NULL THEN
2899 l_date := TO_DATE('31-12-4712', 'DD-MM-YYYY');
2900 END IF;
2901
2902 IF p_property_code IS NOT NULL THEN
2903
2904 FOR bld_rec IN get_buildings_c(p_prop_code => p_property_code)
2905 LOOP
2906
2907 FOR vr_rec IN get_vr_prop_c(p_building_id => bld_rec.location_id)
2908 LOOP
2909
2910 pn_var_trueup_pkg.calculate_trueup
2911 ( p_var_rent_id => vr_rec.var_rent_id
2912 ,p_prd_date => l_date);
2913
2914 END LOOP;
2915
2916 END LOOP;
2917
2918 ELSIF p_property_code IS NULL THEN
2919
2920 FOR vr_rec IN get_vr_c LOOP
2921
2922 pn_var_trueup_pkg.calculate_trueup
2923 ( p_var_rent_id => vr_rec.var_rent_id
2924 ,p_prd_date => l_date);
2925
2926 END LOOP;
2927
2928 END IF;
2929
2930 EXCEPTION
2931 WHEN OTHERS THEN RAISE;
2932
2933 END trueup_batch_process;
2934
2935
2936 PROCEDURE set_trueup_flag(l_flag VARCHAR2
2937 ) IS
2938 BEGIN
2939 --VALID VALUES ARE T AND C
2940 G_IS_TU_CONC_FLAG := l_flag;
2941 EXCEPTION
2942 WHEN others THEN
2943 RAISE;
2944 END;
2945
2946
2947 END PN_VAR_TRUEUP_PKG;
[Home] [Help]