1 PACKAGE BODY PN_VAR_TRUEUP_PKG AS
2 -- $Header: PNVRTRPB.pls 120.0.12010000.2 2009/12/22 11:11:37 jsundara ship $
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 -- 22/12/2009 jsundara o Bug#9117652 Consider Deductions while calculating l_net_trueup_volume
2308 --------------------------------------------------------------------------------
2309 PROCEDURE calculate_trueup( p_var_rent_id IN NUMBER
2310 ,p_prd_date IN DATE)
2311 IS
2312
2313 CURSOR vr_c(p_vr_id IN NUMBER) IS
2314 SELECT
2315 proration_rule
2316 ,cumulative_vol
2317 ,negative_rent
2318 ,commencement_date
2319 ,termination_date
2320 ,org_id
2321 FROM
2322 pn_var_rents_all
2323 WHERE
2324 var_rent_id = p_vr_id;
2325
2326 l_vr_start_date DATE;
2327 l_vr_end_date DATE;
2328 l_proration_rule VARCHAR2(30);
2329 l_calc_method VARCHAR2(30);
2330 l_org_id pn_var_rents_all.org_id%TYPE;
2331
2332 CURSOR periods_c( p_vr_id IN NUMBER
2333 ,p_date IN DATE) IS
2334 SELECT
2335 org_id
2336 ,period_id
2337 ,start_date
2338 ,end_date
2339 ,partial_period
2340 FROM
2341 pn_var_periods_all
2342 WHERE
2343 var_rent_id = p_vr_id AND
2344 end_date <= p_prd_date
2345 AND NVL(status, pn_var_rent_calc_pkg.G_PERIOD_ACTIVE_STATUS)
2346 <> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS;
2347
2348 -- Get the details of periods to calculate abatements for.
2349 -- We need to calculate abatements for all periods.
2350 -- The idea is that True up calc runs from start to a specific period
2351 -- So we need to run abatements till that period. Now the abatements after that
2352 -- periods might have changed so we need to apply_abatements to subsequent periods.
2353 CURSOR periods_abat_c(ip_var_rent_id NUMBER
2354 ) IS
2355 SELECT period_id
2356 FROM pn_var_periods_all
2357 WHERE var_rent_id = ip_var_rent_id
2358 AND NVL(status, pn_var_rent_calc_pkg.G_PERIOD_ACTIVE_STATUS)
2359 <> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS;
2360
2361
2362
2363 l_prd_counter NUMBER;
2364
2365 /* get line items for a period */
2366 CURSOR lines_c( p_vr_id IN NUMBER
2367 ,p_prd_id IN NUMBER) IS
2368 SELECT
2369 line_item_id
2370 FROM
2371 pn_var_trx_headers_all
2372 WHERE
2373 var_rent_id = p_vr_id AND
2374 period_id = p_prd_id
2375 GROUP BY
2376 line_item_id;
2377
2378 /* get the dates when we need to create the trueup invoice */
2379 CURSOR trueup_trx_c( p_vr_id IN NUMBER
2380 ,p_prd_id IN NUMBER
2381 ,p_line_id IN NUMBER) IS
2382 SELECT
2383 MIN(calc_prd_start_date) AS trueup_start_date
2384 ,MAX(calc_prd_end_date) AS trueup_end_date
2385 ,reset_group_id
2386 FROM
2387 pn_var_trx_headers_all
2388 WHERE
2389 var_rent_id = p_vr_id AND
2390 period_id = p_prd_id AND
2391 line_item_id = p_line_id
2392 GROUP BY
2393 reset_group_id
2394 ORDER BY
2395 trueup_start_date;
2396
2397 /* get YTD sales for the gives calc prd end date */
2398 CURSOR ytd_sales_c( p_vr_id IN NUMBER
2399 ,p_prd_id IN NUMBER
2400 ,p_line_id IN NUMBER
2401 ,p_end_dt IN DATE) IS
2402 SELECT
2403 ytd_sales,
2404 ytd_deductions -- Bug#9117652
2405 FROM
2406 pn_var_trx_headers_all
2407 WHERE
2408 var_rent_id = p_vr_id AND
2409 period_id = p_prd_id AND
2410 line_item_id = p_line_id AND
2411 calc_prd_end_date = p_end_dt;
2412
2413 l_net_trueup_volume NUMBER;
2414 l_tot_trueup_rent NUMBER;
2415 l_line_trueup_rent NUMBER;
2416 l_part_trueup_rent NUMBER;
2417
2418 /* get the bkpts for trueup */
2419 CURSOR trueup_bkpt_c( p_vr_id IN NUMBER
2420 ,p_prd_id IN NUMBER
2421 ,p_line_id IN NUMBER
2422 ,p_reset_grp_id IN NUMBER
2423 ,p_end_dt IN DATE) IS
2424 SELECT
2425 ytd_group_vol_start AS trueup_bkpt_vol_start
2426 ,ytd_group_vol_end AS trueup_bkpt_vol_end
2427 ,bkpt_rate
2428 FROM
2429 pn_var_trx_details_all
2430 WHERE
2431 trx_header_id IN (SELECT
2432 trx_header_id
2433 FROM
2434 pn_var_trx_headers_all
2435 WHERE
2436 var_rent_id = p_vr_id AND
2437 period_id = p_prd_id AND
2438 line_item_id = p_line_id AND
2439 reset_group_id = p_reset_grp_id AND
2440 calc_prd_end_date = p_end_dt)
2441 ORDER BY
2442 trueup_bkpt_vol_start;
2443
2444 TYPE TRUEUP_BKPT_TBL IS TABLE OF trueup_bkpt_c%ROWTYPE INDEX BY BINARY_INTEGER;
2445
2446 trueup_bkpt_t TRUEUP_BKPT_TBL;
2447
2448 CURSOR bkpt_type_c( p_line_id IN NUMBER
2449 ,p_start_dt IN DATE
2450 ,p_end_dt IN DATE) IS
2451 SELECT
2452 bkhd.bkpt_header_id
2453 ,bkhd.breakpoint_type
2454 FROM
2455 pn_var_bkpts_head_all bkhd
2456 WHERE
2457 bkhd.line_item_id = p_line_id AND
2458 bkhd_start_date <= p_end_dt AND
2459 bkhd_end_date >= p_start_dt;
2460
2461
2462 l_bkpt_type VARCHAR2(30);
2463 l_bkpt_start NUMBER;
2464 l_bkpt_end NUMBER;
2465 l_neg_rent_flag VARCHAR2(30);
2466
2467 BEGIN
2468
2469 pnp_debug_pkg.log('+++++ calculate_trueup - START +++++');
2470 pnp_debug_pkg.log(' ');
2471
2472 FOR vr_rec IN vr_c(p_vr_id => p_var_rent_id) LOOP
2473
2474 l_proration_rule := vr_rec.proration_rule;
2475 l_calc_method := vr_rec.cumulative_vol;
2476 l_vr_start_date := vr_rec.commencement_date;
2477 l_vr_end_date := vr_rec.termination_date;
2478 l_neg_rent_flag := vr_rec.negative_rent;
2479 l_org_id := vr_rec.org_id;
2480
2481 END LOOP;
2482
2483 g_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(l_org_id),4);
2484
2485 l_prd_counter := 0;
2486
2487 /* loop for all periods ending before p_prd_date */
2488 FOR prd_rec IN periods_c( p_vr_id => p_var_rent_id
2489 ,p_date => p_prd_date)
2490 LOOP
2491
2492 l_prd_counter := l_prd_counter + 1;
2493
2494 pnp_debug_pkg.log('Period Details: ');
2495 pnp_debug_pkg.log(' Period # : '||l_prd_counter);
2496 pnp_debug_pkg.log(' Period Start: '||prd_rec.start_date);
2497 pnp_debug_pkg.log(' Period End : '||prd_rec.end_date);
2498 pnp_debug_pkg.log(' ');
2499
2500 /* init the trueup rent for the period */
2501 l_tot_trueup_rent := 0;
2502 --TODO
2503 G_ABATEMENT_APPLIED.DELETE;
2504 G_ALLOWANCE_APPLIED.DELETE;
2505 G_ABATED_RENT.DELETE;
2506 G_UNABATED_RENT.DELETE;
2507 G_TOT_ABATEMENT.DELETE;
2508
2509 /* check if we need to calculate TRUE UP */
2510 IF prd_rec.start_date = l_vr_start_date AND
2511 l_proration_rule IN ( pn_var_rent_calc_pkg.G_PRORUL_FY
2512 ,pn_var_rent_calc_pkg.G_PRORUL_FLY
2513 ,pn_var_rent_calc_pkg.G_PRORUL_CYP
2514 ,pn_var_rent_calc_pkg.G_PRORUL_CYNP) AND
2515 prd_rec.partial_period = 'Y'
2516 THEN
2517
2518 /* no true up for the first partial in case of
2519 FY, FLY, CYP, CYNP
2520 */
2521 NULL;
2522
2523 ELSIF prd_rec.end_date = l_vr_end_date AND
2524 l_proration_rule IN ( pn_var_rent_calc_pkg.G_PRORUL_LY
2525 ,pn_var_rent_calc_pkg.G_PRORUL_FLY) AND
2526 prd_rec.partial_period = 'Y'
2527 THEN
2528
2529 /* no true up for the last partial in case of
2530 LY, FLY
2531 */
2532 NULL;
2533
2534 ELSIF pn_var_trueup_pkg.can_do_trueup
2535 ( p_var_rent_id => p_var_rent_id
2536 ,p_period_id => prd_rec.period_id)
2537 THEN
2538 /* no true up if calculation not done for all invoiceing periods
2539 assuming all invoicing periods will have some sales populated
2540 - need to validate this
2541 */
2542
2543 /* loop for all lines in the period */
2544 FOR line_rec IN lines_c ( p_vr_id => p_var_rent_id
2545 ,p_prd_id => prd_rec.period_id) LOOP
2546
2547 /* re init the TRUEUP amount */
2548 UPDATE
2549 pn_var_trx_headers_all
2550 SET
2551 trueup_rent_due = 0
2552 WHERE
2553 var_rent_id = p_var_rent_id AND
2554 period_id = prd_rec.period_id AND
2555 line_item_id = line_rec.line_item_id;
2556
2557 /* now start re-calculating the TRUEUP again */
2558 l_line_trueup_rent := 0;
2559
2560 /* loop for all resets for a line */
2561 FOR trueup_rec IN trueup_trx_c( p_vr_id => p_var_rent_id
2562 ,p_prd_id => prd_rec.period_id
2563 ,p_line_id => line_rec.line_item_id)
2564 LOOP
2565
2566 l_part_trueup_rent := 0;
2567
2568 /* get YTD sales for trueup_rec.trueup_end_date */
2569 FOR sales_rec IN ytd_sales_c( p_vr_id => p_var_rent_id
2570 ,p_prd_id => prd_rec.period_id
2571 ,p_line_id => line_rec.line_item_id
2572 ,p_end_dt => trueup_rec.trueup_end_date)
2573 LOOP
2574 l_net_trueup_volume := NVL(sales_rec.ytd_sales,0)-NVL(sales_rec.ytd_deductions,0); -- Bug#9117652
2575 END LOOP; /* get YTD sales for trueup_rec.trueup_end_date */
2576
2577 IF l_net_trueup_volume <> 0 THEN
2578
2579 /* get bkpts */
2580 trueup_bkpt_t.DELETE;
2581
2582 OPEN trueup_bkpt_c( p_vr_id => p_var_rent_id
2583 ,p_prd_id => prd_rec.period_id
2584 ,p_line_id => line_rec.line_item_id
2585 ,p_reset_grp_id => trueup_rec.reset_group_id
2586 ,p_end_dt => trueup_rec.trueup_end_date);
2587 FETCH trueup_bkpt_c BULK COLLECT INTO trueup_bkpt_t;
2588 CLOSE trueup_bkpt_c; /* get bkpts */
2589
2590 IF trueup_bkpt_t.COUNT > 0 THEN
2591
2592 FOR bkpt_hdr_rec IN bkpt_type_c ( p_line_id => line_rec.line_item_id
2593 ,p_start_dt => trueup_rec.trueup_start_date
2594 ,p_end_dt => trueup_rec.trueup_end_date)
2595 LOOP
2596 l_bkpt_type := bkpt_hdr_rec.breakpoint_type;
2597 END LOOP;
2598
2599 /* net volume trips any bkpt? */
2600 IF l_net_trueup_volume < trueup_bkpt_t(1).trueup_bkpt_vol_start THEN
2601
2602 /* this is the functionality that exists today
2603 does not exist in Macerich code
2604 Once no breakpoints are tripped, Macerich consider the rent to be = 0 */
2605 /*Well no, because in case of true up the non cumulative calculations can be negative,
2606 but the true up calculations which are similar to cumulative can not be.
2607 They are not even deferred. - Shabda*/
2608
2609 IF (l_neg_rent_flag = 'IGNORE') THEN
2610 l_part_trueup_rent := 0;
2611 ELSE
2612 l_part_trueup_rent
2613 := (l_net_trueup_volume - trueup_bkpt_t(1).trueup_bkpt_vol_start)
2614 * trueup_bkpt_t(1).bkpt_rate;
2615 END IF;
2616
2617
2618
2619
2620
2621 /* net volume trips any bkpt? - YES */
2622 ELSE
2623
2624 /* get rent based on breakpoint type */
2625 IF l_bkpt_type = pn_var_rent_calc_pkg.G_BKPT_TYP_STRATIFIED
2626 THEN
2627
2628 l_part_trueup_rent := 0;
2629
2630 /* loop for all bkpt details */
2631 FOR i IN trueup_bkpt_t.FIRST..trueup_bkpt_t.LAST LOOP
2632
2633 l_bkpt_start := trueup_bkpt_t(i).trueup_bkpt_vol_start;
2634 l_bkpt_end := trueup_bkpt_t(i).trueup_bkpt_vol_end;
2635
2636 IF l_bkpt_end IS NULL OR l_bkpt_end = 0 THEN
2637 l_bkpt_end := NULL;
2638 END IF;
2639
2640 /* net vol > bkpt start */
2641 IF l_net_trueup_volume >= l_bkpt_start THEN
2642
2643 IF l_net_trueup_volume
2644 <= NVL(l_bkpt_end, l_net_trueup_volume)
2645 THEN
2646
2647 l_part_trueup_rent
2648 := l_part_trueup_rent
2649 + (l_net_trueup_volume - l_bkpt_start)
2650 * trueup_bkpt_t(i).bkpt_rate;
2651
2652 ELSIF l_net_trueup_volume > l_bkpt_end THEN
2653
2654 l_part_trueup_rent
2655 := l_part_trueup_rent
2656 + (l_bkpt_end - l_bkpt_start)
2657 * trueup_bkpt_t(i).bkpt_rate;
2658
2659 END IF;
2660
2661 ELSE
2662
2663 EXIT;
2664
2665 END IF; /* net vol > bkpt start */
2666
2667 END LOOP; /* loop for all bkpt details */
2668
2669 ELSIF l_bkpt_type IN ( pn_var_rent_calc_pkg.G_BKPT_TYP_FLAT
2670 ,pn_var_rent_calc_pkg.G_BKPT_TYP_SLIDING)
2671 THEN
2672
2673 /* loop for all bkpt details */
2674 FOR i IN trueup_bkpt_t.FIRST..trueup_bkpt_t.LAST LOOP
2675
2676 l_bkpt_start := trueup_bkpt_t(i).trueup_bkpt_vol_start;
2677 l_bkpt_end := trueup_bkpt_t(i).trueup_bkpt_vol_end;
2678
2679 IF l_bkpt_end IS NULL OR l_bkpt_end = 0 THEN
2680 l_bkpt_end := NULL;
2681 END IF;
2682
2683 IF l_net_trueup_volume >= l_bkpt_start AND
2684 l_net_trueup_volume <= NVL(l_bkpt_end, l_net_trueup_volume)
2685 THEN
2686
2687 IF l_bkpt_type = pn_var_rent_calc_pkg.G_BKPT_TYP_SLIDING THEN
2688
2689 l_part_trueup_rent
2690 := l_net_trueup_volume * trueup_bkpt_t(i).bkpt_rate;
2691
2692 ELSIF l_bkpt_type = pn_var_rent_calc_pkg.G_BKPT_TYP_FLAT THEN
2693
2694 l_part_trueup_rent
2695 := (l_net_trueup_volume - l_bkpt_start)
2696 * trueup_bkpt_t(i).bkpt_rate;
2697
2698 END IF;
2699
2700 EXIT;
2701
2702 END IF;
2703
2704 END LOOP; /* loop for all bkpt details */
2705
2706 END IF; /* get rent based on breakpoint type */
2707
2708 END IF; /* net volume trips any bkpt? */
2709
2710 END IF; /* IF trueup_bkpt_t.COUNT > 0 THEN */
2711
2712 END IF; /* IF l_net_trueup_volume <> 0 THEN */
2713
2714 pnp_debug_pkg.log('');
2715 /* update the line trueup rent */
2716 l_line_trueup_rent := l_line_trueup_rent + l_part_trueup_rent;
2717
2718 pnp_debug_pkg.log(' part_trueup_rent: '||l_part_trueup_rent);
2719 pnp_debug_pkg.log(' ');
2720
2721 END LOOP; /* loop for all resets for a line */
2722
2723 l_tot_trueup_rent := l_tot_trueup_rent + l_line_trueup_rent;
2724
2725 UPDATE
2726 pn_var_trx_headers_all
2727 SET
2728 trueup_rent_due = round(l_line_trueup_rent,g_precision)
2729 WHERE
2730 var_rent_id = p_var_rent_id AND
2731 period_id = prd_rec.period_id AND
2732 line_item_id = line_rec.line_item_id AND
2733 calc_prd_end_date = prd_rec.end_date;
2734
2735 pnp_debug_pkg.log(' line_trueup_rent: '||l_line_trueup_rent);
2736 pnp_debug_pkg.log(' ');
2737
2738 END LOOP; /* loop for all lines in the period */
2739
2740 pnp_debug_pkg.log(' tot_trueup_rent for Period : '||l_tot_trueup_rent);
2741 pnp_debug_pkg.log(' ');
2742 pn_var_trueup_pkg.post_summary_trueup
2743 ( p_var_rent_id => p_var_rent_id
2744 ,p_period_id => prd_rec.period_id
2745 ,p_proration_rule => l_proration_rule);
2746
2747 END IF; /* check if we need to calculate TRUE UP */
2748
2749 --Rest the abatements, and reapply them.
2750 pn_var_rent_calc_pkg.reset_abatements(p_var_rent_id);
2751
2752 END LOOP;
2753
2754 FOR period_rec IN periods_abat_c(p_var_rent_id) LOOP
2755 apply_abatements(p_var_rent_id,
2756 period_rec.period_id,
2757 'CALCULATE');
2758 END LOOP;
2759
2760 FOR prd_rec IN periods_c( p_vr_id => p_var_rent_id
2761 ,p_date => p_prd_date) LOOP
2762
2763 IF NOT ((prd_rec.start_date = l_vr_start_date AND
2764 l_proration_rule IN (pn_var_rent_calc_pkg.G_PRORUL_CYP
2765 ,pn_var_rent_calc_pkg.G_PRORUL_CYNP
2766 ,pn_var_rent_calc_pkg.G_PRORUL_FY
2767 ,pn_var_rent_calc_pkg.G_PRORUL_FLY) AND
2768 prd_rec.partial_period = 'Y')
2769 OR (prd_rec.end_date = l_vr_end_date AND
2770 l_proration_rule IN (pn_var_rent_calc_pkg.G_PRORUL_LY
2771 ,pn_var_rent_calc_pkg.G_PRORUL_FLY) AND
2772 prd_rec.partial_period = 'Y'))
2773 THEN
2774 pn_var_trueup_pkg.insert_invoice_trueup
2775 ( p_var_rent_id => p_var_rent_id
2776 ,p_period_id => prd_rec.period_id);
2777 END IF;
2778 END LOOP;
2779
2780 EXCEPTION
2781 WHEN OTHERS THEN RAISE;
2782
2783 END calculate_trueup;
2784
2785 --------------------------------------------------------------------------------
2786 -- NAME : trueup_batch_process
2787 -- DESCRIPTION :
2788 -- PURPOSE :
2789 -- INVOKED FROM :
2790 -- ARGUMENTS :
2791 -- REFERENCE : PN_COMMON.debug()
2792 -- HISTORY :
2793 --
2794 -- dd-mon-yyyy name o Created
2795 --------------------------------------------------------------------------------
2796 PROCEDURE trueup_batch_process( errbuf OUT NOCOPY VARCHAR2
2797 ,retcode OUT NOCOPY VARCHAR2
2798 ,p_property_code IN VARCHAR2
2799 ,p_lease_num_low IN VARCHAR2
2800 ,p_lease_num_high IN VARCHAR2
2801 ,p_vr_num_low IN VARCHAR2
2802 ,p_vr_num_high IN VARCHAR2
2803 ,p_date IN VARCHAR2)
2804 IS
2805
2806 l_lease_num_low VARCHAR2(30);
2807 l_lease_num_high VARCHAR2(30);
2808 l_vr_num_low VARCHAR2(30);
2809 l_vr_num_high VARCHAR2(30);
2810 l_date DATE;
2811
2812 /* get the VR to do trueup for */
2813 CURSOR get_vr_c IS
2814 SELECT
2815 vr.var_rent_id
2816 FROM
2817 pn_leases_all lease
2818 ,pn_var_rents_all vr
2819 WHERE
2820 vr.cumulative_vol = 'T' AND
2821 vr.lease_id = lease.lease_id AND
2822 lease.lease_num BETWEEN l_lease_num_low AND l_lease_num_high AND
2823 vr.rent_num BETWEEN l_vr_num_low AND l_vr_num_high
2824 ORDER BY
2825 vr.rent_num;
2826
2827 /* get the VR to do trueup for - used when property code is passed */
2828 CURSOR get_vr_prop_c(p_building_id IN NUMBER) IS
2829 SELECT
2830 vr.var_rent_id
2831 FROM
2832 pn_leases_all lease
2833 ,pn_var_rents_all vr
2834 WHERE
2835 vr.cumulative_vol = pn_var_rent_calc_pkg.G_CALC_TRUE_UP AND
2836 vr.lease_id = lease.lease_id AND
2837 lease.lease_num BETWEEN l_lease_num_low AND l_lease_num_high AND
2838 vr.rent_num BETWEEN l_vr_num_low AND l_vr_num_high AND
2839 vr.location_id IN
2840 (SELECT
2841 location_id
2842 FROM
2843 pn_locations_all
2844 START WITH location_id = p_building_id
2845 CONNECT BY PRIOR location_id = parent_location_id)
2846 ORDER BY
2847 vr.rent_num;
2848
2849 CURSOR get_buildings_c(p_prop_code IN VARCHAR2) IS
2850 SELECT DISTINCT
2851 loc.location_id
2852 FROM
2853 pn_locations_all loc
2854 ,pn_properties_all prop
2855 WHERE
2856 loc.property_id = prop.property_id AND
2857 prop.property_code = p_prop_code;
2858
2859 BEGIN
2860 pnp_debug_pkg.log('+++*process_trueup_batch*++++++');
2861 /* init */
2862 IF p_lease_num_low IS NOT NULL THEN
2863 l_lease_num_low := p_lease_num_low;
2864 ELSE
2865 l_lease_num_low := ' ';
2866 END IF;
2867
2868 IF p_lease_num_high IS NOT NULL THEN
2869 l_lease_num_high := p_lease_num_high;
2870 ELSE
2871 BEGIN
2872 SELECT MAX(lease_num)
2873 INTO l_lease_num_high
2874 FROM pn_leases;
2875 EXCEPTION
2876 WHEN OTHERS THEN RAISE;
2877 END;
2878 END IF;
2879
2880 IF p_vr_num_low IS NOT NULL THEN
2881 l_vr_num_low := p_vr_num_low;
2882 ELSE
2883 l_vr_num_low := ' ';
2884 END IF;
2885
2886 IF p_vr_num_high IS NOT NULL THEN
2887 l_vr_num_high := p_vr_num_high;
2888 ELSE
2889 BEGIN
2890 SELECT MAX(rent_num)
2891 INTO l_vr_num_high
2892 FROM pn_var_rents;
2893 EXCEPTION
2894 WHEN OTHERS THEN RAISE;
2895 END;
2896 END IF;
2897
2898 IF p_date IS NOT NULL THEN
2899 l_date := fnd_date.canonical_to_date(p_date);
2900 ELSIF p_date IS NULL THEN
2901 l_date := TO_DATE('31-12-4712', 'DD-MM-YYYY');
2902 END IF;
2903
2904 IF p_property_code IS NOT NULL THEN
2905
2906 FOR bld_rec IN get_buildings_c(p_prop_code => p_property_code)
2907 LOOP
2908
2909 FOR vr_rec IN get_vr_prop_c(p_building_id => bld_rec.location_id)
2910 LOOP
2911
2912 pn_var_trueup_pkg.calculate_trueup
2913 ( p_var_rent_id => vr_rec.var_rent_id
2914 ,p_prd_date => l_date);
2915
2916 END LOOP;
2917
2918 END LOOP;
2919
2920 ELSIF p_property_code IS NULL THEN
2921
2922 FOR vr_rec IN get_vr_c LOOP
2923
2924 pn_var_trueup_pkg.calculate_trueup
2925 ( p_var_rent_id => vr_rec.var_rent_id
2926 ,p_prd_date => l_date);
2927
2928 END LOOP;
2929
2930 END IF;
2931
2932 EXCEPTION
2933 WHEN OTHERS THEN RAISE;
2934
2935 END trueup_batch_process;
2936
2937
2938 PROCEDURE set_trueup_flag(l_flag VARCHAR2
2939 ) IS
2940 BEGIN
2941 --VALID VALUES ARE T AND C
2942 G_IS_TU_CONC_FLAG := l_flag;
2943 EXCEPTION
2944 WHEN others THEN
2945 RAISE;
2946 END;
2947
2948
2949 END PN_VAR_TRUEUP_PKG;