1 PACKAGE BODY pn_variable_amount_pkg AS
2 -- $Header: PNVRAMTB.pls 120.13 2007/05/21 10:36:21 lbala noship $
3
4 /* Declare all the appropriate cursors to get actual/forecasted volume
5 history */
6
7 /* cursor for picking up the group dates AND their VH , for 'CALCULATION',
8 if invoicing is on 'ACTUAL' */
9
10 CURSOR csr_actual_vol (p_line_item_id number,
11 p_period_id number,
12 p_period_date DATE) IS
13 SELECT SUM(vh.actual_amount) actual_amt,
14 vh.grp_date_id
15 FROM pn_var_vol_hist_all vh,
16 pn_var_grp_dates_all gd
17 WHERE vh.line_item_id = p_line_item_id
18 AND vh.grp_date_id = gd.grp_date_id
19 AND vh.period_id = gd.period_id
20 AND NVL(gd.actual_exp_code,'N') = 'N'
21 AND gd.period_id = p_period_id
22 AND vh.actual_amount is not null
23 AND gd.grp_end_date <= p_period_date
24 GROUP BY vh.grp_date_id, vh.group_date
25 ORDER BY vh.group_date;
26
27 /* cursor for picking up the group dates AND their VH , for 'CALCULATION', if invoicing is on
28 'FORECASTED' */
29
30 CURSOR csr_forecast_vol (p_line_item_id number,
31 p_period_id number,
32 p_period_date DATE) IS
33 SELECT SUM(vh.forecasted_amount) forecasted_amt,
34 vh.grp_date_id
35 FROM pn_var_vol_hist_all vh,
36 pn_var_grp_dates_all gd
37 WHERE vh.line_item_id = p_line_item_id
38 AND vh.grp_date_id = gd.grp_date_id
39 AND vh.period_id = gd.period_id
40 AND NVL(gd.forecasted_exp_code,'N') = 'N'
41 AND gd.period_id = p_period_id
42 AND vh.forecasted_amount is not null
43 AND gd.grp_end_date <= p_period_date
44 GROUP BY vh.grp_date_id, vh.group_date
45 ORDER BY vh.group_date;
46
47 /* cursor for picking up the group dates AND their VH , for 'RECONCILIATION',
48 if invoicing is on 'FORECASTED'.Here we are asSUMing that the UI will
49 validate the invoice date of a given period to ensure that the forecasted rent
50 has been transferred for all of TYPE group dates belonging to the invoice date,
51 prior to calling this routine to do the reconciliation for a given invoice date
52 NOTE:
53 dont reconcile if act-for rent has been exported,do an adjustment */
54 CURSOR csr_reconcile_vol (p_invoice_date DATE,
55 p_period_id NUMBER,
56 p_line_item_id NUMBER,
57 p_period_date DATE) IS
58 SELECT SUM(vh.actual_amount) actual_amt,
59 vh.grp_date_id,
60 vh.line_item_id
61 FROM pn_var_vol_hist_all vh,
62 pn_var_grp_dates_all gd
63 WHERE vh.grp_date_id = gd.grp_date_id
64 AND vh.period_id = gd.period_id
65 AND gd.period_id = p_period_id
66 AND vh.line_item_id = p_line_item_id
67 AND gd.invoice_date = p_invoice_date
68 AND NVL(gd.variance_exp_code,'N') = 'N'
69 AND NVL(gd.forecasted_exp_code,'N') = 'Y'
70 AND vh.actual_amount is not null
71 AND gd.grp_end_date <= p_period_date
72 GROUP BY vh.line_item_id, vh.grp_date_id, vh.group_date
73 ORDER BY vh.line_item_id, vh.group_date;
74
75 /* cursor for picking up the group dates AND their VH , for 'ADJUSTMENT' */
76
77 CURSOR csr_adjust_vol (p_period_id number,
78 p_line_item_id number,
79 p_invoice_on varchar2,
80 p_min_group_date date) IS
81 SELECT SUM(vh.actual_amount) actual_amt,
82 vh.grp_date_id,
83 vh.line_item_id
84 FROM pn_var_vol_hist_all vh,
85 pn_var_grp_dates_all gd
86 WHERE vh.grp_date_id = gd.grp_date_id
87 AND vh.period_id = gd.period_id
88 AND DECODE(p_invoice_on,'ACTUAL',NVL(gd.actual_exp_code,'N'),
89 'FORECASTED',NVL(gd.variance_exp_code,'N')) = 'Y'
90 AND gd.period_id = p_period_id
91 AND vh.line_item_id = p_line_item_id
92 AND gd.group_date >= p_min_group_date
93 GROUP BY vh.line_item_id, vh.grp_date_id, vh.group_date
94 ORDER BY vh.line_item_id, vh.group_date;
95
96 /* Get the break point range details */
97
98 CURSOR csr_bkpt_range(p_line_item_id NUMBER)IS
99 SELECT det.period_bkpt_vol_start,
100 det.period_bkpt_vol_end,
101 det.group_bkpt_vol_start,
102 det.group_bkpt_vol_end,
103 det.bkpt_rate,
104 head.breakpoint_TYPE,
105 head.line_item_id
106 FROM pn_var_bkpts_head_all head,
107 pn_var_bkpts_det_all det
108 WHERE det.bkpt_header_id = head.bkpt_header_id
109 AND head.line_item_id = p_line_item_id
110 ORDER BY det.period_bkpt_vol_start;
111
112
113 /* Get the cumulative volume for all the group dates in a
114 line item */
115
116 CURSOR csr_cumulative_vol(p_line_item_id NUMBER)
117 IS
118 SELECT SUM(actual_amount) cum_actual_vol,
119 SUM(forecasted_amount) cum_for_vol,
120 line_item_id,
121 grp_date_id,
122 group_date
123 FROM pn_var_vol_hist_all
124 WHERE line_item_id = p_line_item_id
125 GROUP BY line_item_id,grp_date_id,group_date
126 ORDER BY line_item_id,group_date;
127
128 /* Get the percent days open for all the group dates in a line
129 item. Required when prorating breakpoints */
130
131 Cursor csr_get_gd(p_period_id NUMBER) is
132 SELECT grp_date_id,
133 proration_factor,
134 invoice_date,
135 group_date,
136 grp_start_date,
137 grp_end_date
138 FROM pn_var_grp_dates_all
139 WHERE period_id = p_period_id;
140
141 /* Get the deductions for all the group dates */
142
143 Cursor csr_get_ded (p_line_item_id NUMBER) is
144 SELECT line_item_id,
145 grp_date_id,
146 SUM(deduction_amount) deduction_amt
147 FROM pn_var_deductions_all
148 WHERE line_item_id = p_line_item_id
149 GROUP BY line_item_id,grp_date_id;
150
151
152 TYPE bkpt_range_TYPE IS
153 TABLE OF csr_bkpt_range%ROWTYPE
154 INDEX BY BINARY_INTEGER;
155
156 TYPE cumulative_vol_rec IS
157 RECORD (cum_actual_vol pn_var_vol_hist.actual_amount%TYPE,
158 cum_for_vol pn_var_vol_hist.forecasted_amount%TYPE,
159 line_item_id pn_var_vol_hist.line_item_id%TYPE,
160 grp_date_id pn_var_vol_hist.grp_date_id%TYPE,
161 cum_ded pn_var_deductions.deduction_amount%TYPE);
162
163 TYPE cumulative_vol_TYPE IS
164 TABLE OF cumulative_vol_rec
165 INDEX BY BINARY_INTEGER;
166
167 TYPE grd_date_TYPE IS
168 TABLE OF csr_get_gd%ROWTYPE
169 INDEX BY BINARY_INTEGER;
170
171 TYPE deduction_TYPE IS
172 TABLE OF csr_get_ded%ROWTYPE
173 INDEX BY BINARY_INTEGER;
174
175 TYPE invoice_TYPE IS
176 TABLE OF pn_var_rent_inv%ROWTYPE
177 INDEX BY BINARY_INTEGER;
178
179 /* PL/SQL table to store the breakpoint details */
180 bkpt_range_tbl bkpt_range_TYPE;
181
182 /* PLSQL table cumulative volumes details */
183 cum_vol_tbl cumulative_vol_TYPE;
184
185 /* PL/SQL table percent days open */
186 grd_date_tbl grd_date_TYPE;
187
188 /* PL/SQL table deduction_tbl to store deductions for all the group dates */
189 deduction_tbl deduction_TYPE;
190
191 /* PL/SQL table to store the invoice details */
192 invoice_tbl invoice_TYPE;
193
194
195 /* global variables */
196 g_breakpoint_TYPE pn_var_bkpts_head.breakpoint_TYPE%TYPE;
197 g_var_rent_id pn_var_rents.var_rent_id%TYPE;
198 g_period_id pn_var_periods.period_id%TYPE;
199 g_invoice_date pn_var_grp_dates.invoice_date%TYPE;
200 g_group_date pn_var_grp_dates.group_date%TYPE;
201 g_invoice_on pn_var_rents.invoice_on%TYPE;
202 g_cumulative pn_var_rents.cumulative_vol%TYPE;
203 g_rent_TYPE varchar2(20);
204 g_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE;
205 g_currency_code gl_sets_of_books.currency_code%TYPE;
206 g_precision number;
207 g_org_id pn_leases.org_id%TYPE;
208 g_period_date pn_var_periods.start_date%TYPE;
209
210 /* Uncomment for multi-org support */
211 /*l_gt pn_mo_cache_utils.GlobalsTable; */
212
213
214 -------------------------------------------------------------------------------
215 -- PROCEDURE : process_variable_rent
216 --
217 --
218 -- Main procedure to be called during adjustment(p_calc_TYPE='ADJUST'),
219 -- calculation(p_calc_TYPE='CALCULATE') or reconciliation(p_calc_TYPE='RECONCILE')
220 --
221 -- Parameters:
222 -- p_var_rent_id Variable rent id
223 -- p_period_id Period id
224 -- p_line_item_id Line item id
225 -- p_calc_TYPE The calcuation TYPE ('CALCULATE','ADJUST' or 'RECONCILE')
226 -- p_invoice_on Invoice on ('ACTUAL' or 'FORECASTED')
227 -- p_cumulative Cumulative Flag ('Y' or 'N')
228 -- p_invoice_date During Adjustment has a value.
229 -------------------------------------------------------------------------------
230
231 PROCEDURE process_variable_rent (p_var_rent_id IN NUMBER,
232 p_period_id IN NUMBER,
233 p_line_item_id IN NUMBER,
234 p_cumulative IN VARCHAR2,
235 p_invoice_on IN VARCHAR2,
236 p_calc_TYPE IN VARCHAR2,
237 p_invoice_date IN DATE )
238 IS
239
240 CURSOR csr_get_lines(ip_line_item_id NUMBER) IS
241 SELECT lines.line_item_num,
242 lines.period_id,
243 lines.line_item_id
244 FROM pn_var_periods_all per,
245 pn_var_lines_all lines
246 WHERE lines.period_id= per.period_id
247 AND per.var_rent_id = p_var_rent_id
248 AND per.period_id = p_period_id
249 AND lines.line_item_id = NVL(ip_line_item_id,lines.line_item_id);
250
251 BEGIN
252
253 pnp_debug_pkg.log('pn_variable_amount_pkg.process_variable_rent (+) ');
254
255 pnp_debug_pkg.log('process_variable_rent - Cumulative : '||p_cumulative);
256 pnp_debug_pkg.log('process_variable_rent - Invoice On : '||p_invoice_on);
257
258 /* intialize the global variables for later use when inserting/updating into
259 pn_var_rent_SUMm */
260
261 g_var_rent_id := p_var_rent_id;
262 g_period_id := p_period_id;
263 g_invoice_on := p_invoice_on;
264 g_cumulative := p_cumulative;
265
266
267 /* Initialize pl/sql table */
268
269 invoice_tbl.delete;
270
271
272 /* get all the line items for the period */
273
274 FOR rec_get_lines in csr_get_lines(p_line_item_id)
275 LOOP
276 fnd_message.set_name ('PN','PN_VRAM_LN_NO');
277 fnd_message.set_token ('NUM',rec_get_lines.line_item_num);
278 pnp_debug_pkg.put_log_msg(fnd_message.get);
279
280 pnp_debug_pkg.log('process_variable_rent - Period id :'||rec_get_lines.period_id);
281 pnp_debug_pkg.log('process_variable_rent - Line item id : '||rec_get_lines.line_item_id);
282
283 process_calculate_TYPE(p_line_item_id => rec_get_lines.line_item_id,
284 p_cumulative => p_cumulative,
285 p_calc_TYPE => p_calc_TYPE,
286 p_period_id => rec_get_lines.period_id,
287 p_invoice_date => p_invoice_date);
288 END LOOP;
289
290 /* Insert/Update the invoices for the period */
291
292 Insert_invoice(p_calc_TYPE => p_calc_TYPE,
293 p_period_id => p_period_id,
294 p_var_rent_id => p_var_rent_id);
295
296
297 pnp_debug_pkg.log('pn_variable_amount_pkg.process_variable_rent (-) ');
298
299 END process_variable_rent;
300
301
302 -------------------------------------------------------------------------------
303 -- PROCEDURE : process_calculate_TYPE
304 -- PARAMETERS :
305 --
306 -- Based up on if there is an adjustment, reconcilation or calculation of
307 -- variable rent, open the appropriate cursors AND make a call to
308 -- calculate_var_rent to calculate variable for each line_item_id.
309 --
310 -- HISTORY
311 -- 22-Feb-04 Kiran Hegde o Added new cursor to get the min group date in a
312 -- period which needs an adjustment.
313 -- The min group date is used to get all the group
314 -- dates in a period that need adjustment.
315 -- Bug # 3237575
316 -------------------------------------------------------------------------------
317
318 PROCEDURE process_calculate_TYPE (p_line_item_id IN NUMBER,
319 p_cumulative IN VARCHAR2,
320 p_calc_TYPE IN VARCHAR2,
321 p_period_id IN NUMBER,
322 p_invoice_date IN DATE)
323 IS
324 l_act_cum_vol NUMBER;
325 l_for_cum_vol NUMBER;
326 l_cum_ded NUMBER;
327
328 -- Get the min group_date for adjustment
329 CURSOR csr_min_group_date_adj (p_period_id number,
330 p_line_item_id number,
331 p_invoice_on varchar2) IS
332 SELECT MIN(gd.group_date) min_group_date
333 FROM pn_var_vol_hist_all vh,
334 pn_var_grp_dates_all gd
335 WHERE vh.grp_date_id = gd.grp_date_id
336 AND vh.period_id = gd.period_id
337 AND DECODE(p_invoice_on,'ACTUAL',NVL(gd.actual_exp_code,'N'),
338 'FORECASTED',NVL(gd.variance_exp_code,'N')) = 'Y'
339 AND gd.period_id = p_period_id
340 AND vh.line_item_id = p_line_item_id
341 AND exists (SELECT null
342 FROM pn_var_vol_hist_all vh1
343 WHERE vh1.period_id = gd.period_id
344 AND vh1.grp_date_id = gd.grp_date_id
345 AND DECODE(p_invoice_on,'ACTUAL' ,NVL( vh1.actual_exp_code,'N'),
346 'FORECASTED',NVL( vh1.variance_exp_code,'N')
347 ) = 'N'
348 AND vh1.line_item_id = vh.line_item_id
349 AND vh1.actual_amount is not null);
350
351 l_min_grp_date DATE;
352
353 BEGIN
354
355 pnp_debug_pkg.log('pn_variable_amount_pkg.process_calculate_TYPE (+) ');
356
357
358 /* get the Breakpoint info. For the line item */
359
360 get_bkp_details(p_line_item_id => p_line_item_id);
361
362
363 /* get deduction if invoicing is on actual AND store the information
364 in a PL/SQL table deductions_tbl */
365
366 get_deductions(p_line_item_id => p_line_item_id);
367
368 /* get Cumulative Volume for each line item id AND store the
369 information in pl/sql table cum_vol_tbl */
370
371 IF p_cumulative = 'Y' THEN
372 get_cumulative_volume(p_line_item_id => p_line_item_id);
373 END IF;
374
375
376 /* for calculate get the volume history for forecasted AND/or actual.
377 open the appropriate cursors to get the volume history */
378
379 IF g_invoice_on = 'ACTUAL' AND p_calc_TYPE = 'CALCULATE' THEN
380
381 FOR rec_actual_vol in csr_actual_vol(p_line_item_id => p_line_item_id,
382 p_period_id => p_period_id,
383 p_period_date => g_period_date)
384 LOOP
385 IF p_cumulative = 'Y' THEN
386
387 get_cum_vol_by_grpdt(p_grp_date_id => rec_actual_vol.grp_date_id,
388 p_cum_actual_vol => l_act_cum_vol ,
389 p_cum_for_vol => l_for_cum_vol,
390 p_cum_ded => l_cum_ded);
391 END IF;
392
393 g_rent_TYPE := 'ACTUAL';
394
395 calculate_var_rent(p_grp_date_id => rec_actual_vol.grp_date_id,
396 p_line_item_id => p_line_item_id,
397 p_cum_volume => l_act_cum_vol,
398 p_volume => rec_actual_vol.actual_amt,
399 p_cum_ded => l_cum_ded,
400 p_cumulative => p_cumulative,
401 p_calc_TYPE => p_calc_TYPE);
402
403 END LOOP;
404
405 ELSIF g_invoice_on = 'FORECASTED' AND p_calc_TYPE = 'CALCULATE' THEN
406
407 FOR rec_forecast_vol in csr_forecast_vol(p_line_item_id => p_line_item_id,
408 p_period_id => p_period_id,
409 p_period_date => g_period_date)
410 LOOP
411
412 IF p_cumulative = 'Y' THEN
413
414 get_cum_vol_by_grpdt(p_grp_date_id => rec_forecast_vol.grp_date_id,
415 p_cum_actual_vol => l_act_cum_vol ,
416 p_cum_for_vol => l_for_cum_vol,
417 p_cum_ded => l_cum_ded);
418 END IF;
419
420 g_rent_TYPE := 'FORECASTED';
421
422 /* Calculate the forecasted rent */
423
424
425 calculate_var_rent(p_grp_date_id => rec_forecast_vol.grp_date_id,
426 p_line_item_id => p_line_item_id,
427 p_volume => rec_forecast_vol.forecasted_amt,
428 p_cum_volume => l_for_cum_vol,
429 p_cum_ded => l_cum_ded,
430 p_cumulative => p_cumulative,
431 p_calc_TYPE => p_calc_TYPE);
432
433 END LOOP;
434
435 ELSIF p_calc_TYPE = 'RECONCILE' AND g_invoice_on = 'FORECASTED' THEN
436
437 FOR rec_reconcile_vol in csr_reconcile_vol(p_invoice_date => p_invoice_date,
438 p_period_id => p_period_id,
439 p_line_item_id => p_line_item_id,
440 p_period_date => g_period_date)
441 LOOP
442 IF p_cumulative = 'Y' THEN
443
444 get_cum_vol_by_grpdt(p_grp_date_id => rec_reconcile_vol.grp_date_id,
445 p_cum_actual_vol => l_act_cum_vol ,
446 p_cum_for_vol => l_for_cum_vol,
447 p_cum_ded => l_cum_ded);
448 END IF;
449
450 g_rent_TYPE := 'ACTUAL';
451
452 /* calculate actual rent */
453
454 calculate_var_rent(p_grp_date_id => rec_reconcile_vol.grp_date_id,
455 p_line_item_id => rec_reconcile_vol.line_item_id,
456 p_volume => rec_reconcile_vol.actual_amt ,
457 p_cum_volume => l_act_cum_vol,
458 p_cum_ded => l_cum_ded,
459 p_cumulative => p_cumulative,
460 p_calc_TYPE => p_calc_TYPE);
461 END LOOP;
462
463 ELSIF p_calc_TYPE = 'ADJUST' THEN
464
465 -- get the min group date
466 FOR min_date in csr_min_group_date_adj (p_period_id => p_period_id,
467 p_invoice_on => g_invoice_on,
468 p_line_item_id => p_line_item_id)
469 LOOP
470 l_min_grp_date := min_date.min_group_date;
471 END LOOP;
472
473 FOR rec_adjust_vol in csr_adjust_vol(p_period_id => p_period_id,
474 p_invoice_on => g_invoice_on,
475 p_line_item_id => p_line_item_id,
476 p_min_group_date => l_min_grp_date)
477 LOOP
478 IF p_cumulative = 'Y' THEN
479
480 get_cum_vol_by_grpdt(p_grp_date_id => rec_adjust_vol.grp_date_id,
481 p_cum_actual_vol => l_act_cum_vol ,
482 p_cum_for_vol => l_for_cum_vol,
483 p_cum_ded => l_cum_ded);
484
485 END IF;
486
487 g_rent_TYPE := 'ACTUAL';
488
489 /* calculate actual rent */
490
491 calculate_var_rent(p_grp_date_id => rec_adjust_vol.grp_date_id,
492 p_line_item_id => rec_adjust_vol.line_item_id,
493 p_volume => rec_adjust_vol.actual_amt,
494 p_cum_volume => l_act_cum_vol,
495 p_cum_ded => l_cum_ded,
496 p_cumulative => p_cumulative,
497 p_calc_TYPE => p_calc_TYPE);
498
499 END LOOP;
500
501 END IF;
502
503
504 pnp_debug_pkg.log('pn_variable_amount_pkg.process_calculate_TYPE (-) ');
505
506 END process_calculate_TYPE;
507
508
509 ------------------------------------------------------------------------
510 -- PROCEDURE : calculate_var_rent
511 --
512 -- Calculate variable rent for each group date AND line item id
513 -- called for when calculating variable rent for each group date
514 ------------------------------------------------------------------------
515
516 PROCEDURE calculate_var_rent (p_grp_date_id IN NUMBER,
517 p_line_item_id IN NUMBER,
518 p_volume IN NUMBER,
519 p_cum_volume IN NUMBER,
520 p_cum_ded IN NUMBER,
521 p_cumulative IN VARCHAR2,
522 p_calc_TYPE IN VARCHAR2)
523 IS
524 l_proration_factor NUMBER;
525 l_variable_rent NUMBER := 0;
526 l_tot_ded NUMBER;
527 l_net_volume NUMBER := 0;
528 l_volume NUMBER := 0;
529 l_group_date DATE;
530 l_COUNT NUMBER := 0;
531 i NUMBER := 0;
532 j NUMBER := 0;
533
534 BEGIN
535
536 pnp_debug_pkg.log('pn_variable_amount_pkg.calculate_var_rent (+) ');
537
538
539 /* Get the percent days open for the group date*/
540
541 FOR l_COUNT in 1 .. grd_date_tbl.COUNT
542 LOOP
543 i := i + 1;
544
545 IF grd_date_tbl(i).grp_date_id = p_grp_date_id THEN
546 l_proration_factor := grd_date_tbl(i).proration_factor;
547 g_invoice_date := null;
548 g_invoice_date := grd_date_tbl(i).invoice_date;
549 l_group_date := grd_date_tbl(i).group_date;
550 exit;
551 END IF;
552
553 END LOOP;
554
555
556 pnp_debug_pkg.put_log_msg('===============================================================================');
557 fnd_message.set_name ('PN','PN_SOI_INV_DT');
558 fnd_message.set_token ('DATE',g_invoice_date);
559 pnp_debug_pkg.put_log_msg(fnd_message.get);
560
561 fnd_message.set_name ('PN','PN_VRAM_GRP_DATE');
562 fnd_message.set_token ('DATE',l_group_date);
563 pnp_debug_pkg.put_log_msg(fnd_message.get);
564 pnp_debug_pkg.put_log_msg('===============================================================================');
565
566 /* Initialize variables with total volume AND deduction*/
567
568 IF p_cumulative = 'N' THEN
569
570 l_volume := p_volume;
571
572 IF g_rent_TYPE = 'ACTUAL' THEN
573
574 /*Get the deductions to be applied on non cumulative volume*/
575 FOR l_ded_COUNT in 1 .. deduction_tbl.COUNT
576 LOOP
577 j := j + 1;
578 IF deduction_tbl(j).grp_date_id = p_grp_date_id THEN
579 l_tot_ded:= deduction_tbl(j).deduction_amt;
580 exit;
581 END IF;
582 END LOOP;
583
584 /*If no deductions have been entered for the group date but we are calculating
585 the net volume AND also the actual rent then default the deduction to 0*/
586
587 IF l_volume is not null THEN
588 l_tot_ded := NVL(l_tot_ded,0);
589 END IF;
590
591 END IF;
592
593 ELSIF p_cumulative = 'Y' THEN
594
595 l_volume := p_cum_volume;
596 l_tot_ded := p_cum_ded;
597
598 END IF;
599
600 IF g_rent_TYPE = 'ACTUAL' THEN
601
602 /* Apply the deduction on the volume history to get the volume applicable if
603 actual rent is being calculated */
604
605 l_net_volume := l_volume - NVL(l_tot_ded,0);
606 ELSE
607 l_net_volume := l_volume;
608 END IF;
609
610
611 /* Apply the break points on the volume history AND get the rent applicable */
612
613 pnp_debug_pkg.log('calculate_var_rent : p_cumulative '|| p_cumulative);
614 pnp_debug_pkg.log('calculate_var_rent : p_net_volume '|| l_net_volume);
615 pnp_debug_pkg.log('calculate_var_rent : p_percent_days_open '|| l_proration_factor);
616
617 l_variable_rent := get_rent_applicable(
618 p_cumulative => p_cumulative,
619 p_net_volume => l_net_volume,
620 p_percent_days_open => l_proration_factor);
621
622
623
624 /* Insert/Update pn_var_rent_SUM_all */
625 process_rent(P_VAR_RENT_ID => g_var_rent_id,
626 P_PERIOD_ID => g_period_id ,
627 P_LINE_ITEM_ID => p_line_item_id,
628 P_INVOICE_DATE => g_invoice_date,
629 P_GROUP_DATE => l_group_date,
630 P_TOT_VOL => l_volume ,
631 P_TOT_DED => l_tot_ded,
632 P_VAR_RENT => ROUND(l_variable_rent,g_precision),
633 P_GRP_DATE_ID => p_grp_date_id,
634 P_CALC_TYPE => p_calc_TYPE,
635 P_CUMULATIVE => p_cumulative);
636
637
638
639 pnp_debug_pkg.log('pn_variable_amount_pkg.calculate_var_rent (-) ');
640
641 END calculate_var_rent;
642
643
644
645 -------------------------------------------------------------------------------
646 -- FUNCTION : get_rent_applicable
647 -- DESCRIPTION : Apply breakpoints to volume amount applicable
648 -- INVOKED FROM :
649 -- ARGUMENTS : IN : p_cumulative, p_net_volume, p_percent_days_open
650 -- RETURNS : variable rent
651 -- HISTORY :
652 -- 28-dec-05 piagrawa o Bug#3800523 - Added handling to calculate negative
653 -- rent if sales volume does not trip the breakpoint
654 -- in case of non-cumulative volume.Also commented
655 -- the code.
656 -------------------------------------------------------------------------------
657
658
659 FUNCTION get_rent_applicable (p_cumulative IN VARCHAR2,
660 p_net_volume IN NUMBER,
661 p_percent_days_open IN NUMBER)
662 RETURN NUMBER
663 IS
664 l_bkpt_vol_start NUMBER;
665 l_bkpt_vol_end NUMBER;
666 l_volume NUMBER := 0;
667 l_rent NUMBER := 0;
668 i NUMBER := 0;
669 BEGIN
670
671 pnp_debug_pkg.log('pn_variable_amount_pkg.get_rent_applicable (+) ');
672
673
674 IF p_cumulative = 'N' AND
675 p_net_volume < (bkpt_range_tbl(1).group_bkpt_vol_start * p_percent_days_open)
676 THEN
677
678 l_rent := ( p_net_volume -
679 (bkpt_range_tbl(1).group_bkpt_vol_start * p_percent_days_open) )
680 * bkpt_range_tbl(1).bkpt_rate;
681
682 ELSE
683
684 IF g_breakpoint_TYPE in ('STRATIFIED') THEN
685
686 i := 1;
687
688 FOR i in 1 .. bkpt_range_tbl.COUNT
689 LOOP
690
691 l_bkpt_vol_start := null;
692 l_bkpt_vol_end := null;
693
694
695 IF p_cumulative = 'N' THEN
696 l_bkpt_vol_start := bkpt_range_tbl(i).group_bkpt_vol_start;
697 l_bkpt_vol_end := bkpt_range_tbl(i).group_bkpt_vol_end;
698 ELSIF p_cumulative = 'Y' THEN
699 l_bkpt_vol_start := bkpt_range_tbl(i).period_bkpt_vol_start;
700 l_bkpt_vol_end := bkpt_range_tbl(i).period_bkpt_vol_end;
701 END IF;
702
703
704 IF (l_bkpt_vol_start * p_percent_days_open) <= p_net_volume THEN
705
706 IF p_net_volume <= NVL((l_bkpt_vol_end * p_percent_days_open),
707 p_net_volume) THEN
708
709 l_rent := l_rent + (p_net_volume - (l_bkpt_vol_start *
710 p_percent_days_open)
711 ) * bkpt_range_tbl(i).bkpt_rate;
712
713 pnp_debug_pkg.log('get_rent_applicable - Breakpoint TYPE :'||g_breakpoint_TYPE);
714 pnp_debug_pkg.log('p_net_volume <= l_bkpt_vol_end');
715 pnp_debug_pkg.log('get_rent_applicable - Net Volume :'|| p_net_volume);
716 pnp_debug_pkg.log('get_rent_applicable - Volume Applicable :'|| (p_net_volume - (l_bkpt_vol_start *
717 p_percent_days_open)));
718 pnp_debug_pkg.log('get_rent_applicable - bkpt rate :'||bkpt_range_tbl(i).bkpt_rate);
719 pnp_debug_pkg.log('get_rent_applicable - rent :'||l_rent);
720
721
722 ELSE
723 l_rent := l_rent + ((l_bkpt_vol_end * p_percent_days_open)-
724 (l_bkpt_vol_start * p_percent_days_open)
725 ) * bkpt_range_tbl(i).bkpt_rate;
726
727 pnp_debug_pkg.log('get_rent_applicable - Breakpoint TYPE :'||g_breakpoint_TYPE);
728 pnp_debug_pkg.log('p_net_volume > l_bkpt_vol_end ');
729 pnp_debug_pkg.log('get_rent_applicable - Net Volume :'|| p_net_volume);
730 pnp_debug_pkg.log('get_rent_applicable - Volume Applicable :'|| ((l_bkpt_vol_end *
731 p_percent_days_open)- (l_bkpt_vol_start * p_percent_days_open)));
732 pnp_debug_pkg.log('get_rent_applicable - bkpt rate :'||bkpt_range_tbl(i).bkpt_rate);
733 pnp_debug_pkg.log('get_rent_applicable - rent :'||l_rent);
734
735
736 END IF;
737 ELSE
738 exit; -- get out NOCOPY of the loop;
739 END IF;
740
741 END LOOP;
742
743 ELSIF g_breakpoint_TYPE in ('SLIDING', 'FLAT') THEN
744 i := 1;
745
746 FOR i in 1 .. bkpt_range_tbl.COUNT
747 LOOP
748
749 l_bkpt_vol_start := null;
750 l_bkpt_vol_end := null;
751
752 IF p_cumulative = 'N' THEN
753 l_bkpt_vol_start := bkpt_range_tbl(i).group_bkpt_vol_start;
754 l_bkpt_vol_end := bkpt_range_tbl(i).group_bkpt_vol_end;
755 ELSIF p_cumulative = 'Y' THEN
756 l_bkpt_vol_start := bkpt_range_tbl(i).period_bkpt_vol_start;
757 l_bkpt_vol_end := bkpt_range_tbl(i).period_bkpt_vol_end;
758 END IF;
759
760
761 IF (l_bkpt_vol_start * p_percent_days_open) <= p_net_volume AND
762 p_net_volume <= NVL((l_bkpt_vol_end * p_percent_days_open), p_net_volume) THEN
763
764 IF g_breakpoint_TYPE = 'SLIDING' THEN
765
766 l_rent := p_net_volume * bkpt_range_tbl(i).bkpt_rate;
767
768 pnp_debug_pkg.log('get_rent_applicable - Breakpoint TYPE :'||g_breakpoint_TYPE);
769 pnp_debug_pkg.log('get_rent_applicable - Net Volume :'|| p_net_volume);
770 pnp_debug_pkg.log('get_rent_applicable - Volume Applicable :'|| p_net_volume);
771 pnp_debug_pkg.log('get_rent_applicable - bkpt rate :'||bkpt_range_tbl(i).bkpt_rate);
772 pnp_debug_pkg.log('get_rent_applicable - rent :'||l_rent);
773
774
775 ELSIF g_breakpoint_TYPE = 'FLAT' THEN
776
777 l_rent := (p_net_volume - (l_bkpt_vol_start * p_percent_days_open)) *
778 bkpt_range_tbl(i).bkpt_rate;
779
780 pnp_debug_pkg.log('get_rent_applicable - Breakpoint TYPE :'||g_breakpoint_TYPE);
781 pnp_debug_pkg.log('get_rent_applicable - Net Volume :'|| p_net_volume);
782 pnp_debug_pkg.log('get_rent_applicable - Volume Applicable :'||
783 (p_net_volume - (l_bkpt_vol_start * p_percent_days_open)));
784 pnp_debug_pkg.log('get_rent_applicable - bkpt rate :'||bkpt_range_tbl(i).bkpt_rate);
785 pnp_debug_pkg.log('get_rent_applicable - Rent :'||l_rent);
786
787
788
789 END IF;
790 exit; -- get out NOCOPY of the loop
791 END IF;
792
793 END LOOP;
794
795 END IF;
796 END IF;
797 RETURN l_rent;
798
799 pnp_debug_pkg.log('pn_variable_amount_pkg.get_rent_applicable (-) ');
800
801 END get_rent_applicable;
802
803
804 -----------------------------------------------------------------------------
805 -- PROCEDURE : process_rent
806 --
807 -- insert/update pn_var_rent_SUMm with the new variable rent
808 --
809 -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_rent_SUMm with _ALL table.
810 -- 21-MAY-07 Lokesh o Added rounding off for bug # 6031202 in
811 -- pn_var_rent_summ_all
812 -----------------------------------------------------------------------------
813
814 PROCEDURE process_rent (p_var_rent_id IN NUMBER,
815 p_period_id IN NUMBER,
816 p_line_item_id IN NUMBER,
817 p_grp_date_id IN NUMBER,
818 p_invoice_date IN DATE,
819 p_group_date IN DATE,
820 p_tot_vol IN NUMBER,
821 p_tot_ded IN NUMBER,
822 p_var_rent IN NUMBER,
823 p_calc_TYPE IN VARCHAR2,
824 p_cumulative IN VARCHAR2
825 )
826 IS
827 l_varrent_exists VARCHAR2(1) := 'N';
828 l_tot_act_vol NUMBER := NULL;
829 l_act_var_rent NUMBER := NULL;
830 l_tot_for_vol NUMBER := NULL;
831 l_for_var_rent NUMBER := NULL;
832 l_var_rent NUMBER := 0;
833 l_cum_rent NUMBER := 0;
834 l_adjust_num NUMBER := 0;
835 l_var_rent_SUMm_id NUMBER := NULL;
836 l_invoice_date DATE := NULL;
837 l_period_id NUMBER := NULL;
838 l_COUNT NUMBER := 0;
839
840 CURSOR csr_cum_rent(p_line_item_id NUMBER,
841 p_group_date DATE) IS
842 SELECT NVL(decode(g_rent_TYPE,'FORECASTED',SUM(for_var_rent),SUM(act_var_rent)),0)
843 FROM pn_var_rent_summ_all
844 WHERE line_item_id = p_line_item_id
845 AND group_date < p_group_date;
846
847 BEGIN
848
849 pnp_debug_pkg.log('pn_variable_amount_pkg.process_rent (+) ');
850
851 /* insert into PL/SQL table invoice_tbl all the invoices dates
852 for the period for which rent has been calculated. Info
853 needed to update pn_var_Rent_inv table for the new amounts */
854
855 FOR i in 1 .. invoice_tbl.COUNT
856 LOOP
857 if invoice_tbl(i).invoice_date = p_invoice_date AND
858 invoice_tbl(i).period_id = p_period_id then
859 l_invoice_date := invoice_tbl(i).invoice_date;
860 l_period_id := invoice_tbl(i).period_id;
861 exit;
862 end if;
863
864 END LOOP;
865
866 IF l_invoice_date is null AND l_period_id is null THEN
867 l_COUNT := invoice_tbl.COUNT + 1;
868 invoice_tbl(l_COUNT).invoice_date := p_invoice_date;
869 invoice_tbl(l_COUNT).period_id := p_period_id;
870 END IF;
871
872
873 /* if invoicing is on cumulative volume then subtract the SUM of the rent of
874 of group dates less than the current group date from the rent of the
875 current group date */
876
877 l_var_rent := p_var_rent;
878
879 IF p_cumulative ='Y' THEN
880
881 OPEN csr_cum_rent(p_line_item_id,p_group_date);
882 FETCH csr_cum_rent INTO l_cum_rent;
883 CLOSE csr_cum_rent;
884
885 l_var_rent := l_var_rent - l_cum_rent;
886 END IF;
887
888 /* find if a record exists in pn_var_rent_SUMm for the combination
889 of line_item_id AND grp_date_id */
890
891 l_varrent_exists := find_varrent_exists(
892 p_line_item_id => p_line_item_id,
893 p_grp_date_id => p_grp_date_id);
894 fnd_message.set_name ('PN','PN_VRAM_RENT');
895 fnd_message.set_token ('TYPE',INITCAP(g_rent_TYPE));
896 fnd_message.set_token ('AMT',l_var_rent);
897 pnp_debug_pkg.put_log_msg(fnd_message.get);
898
899 IF NVL(l_varrent_exists,'N') = 'N' THEN
900
901 select pn_var_rent_SUMm_s.nextval
902 into l_var_rent_SUMm_id
903 from dual;
904
905 IF g_rent_TYPE = 'FORECASTED' THEN
906 l_tot_for_vol := p_tot_vol;
907 l_for_var_rent := l_var_rent;
908 ELSIF g_rent_TYPE = 'ACTUAL' THEN
909 l_tot_act_vol := p_tot_vol;
910 l_act_var_rent := l_var_rent;
911 END IF;
912
913
914 INSERT INTO pn_var_rent_summ_all
915 (VAR_RENT_SUMM_ID
916 ,VAR_RENT_ID
917 ,PERIOD_ID
918 ,LINE_ITEM_ID
919 ,INVOICE_DATE
920 ,TOT_ACT_VOL
921 ,TOT_FOR_VOL
922 ,TOT_DED
923 ,ACT_VAR_RENT
924 ,FOR_VAR_RENT
925 ,GRP_DATE_ID
926 ,GROUP_DATE
927 ,LAST_UPDATE_DATE
928 ,LAST_UPDATED_BY
929 ,CREATION_DATE
930 ,CREATED_BY
931 ,LAST_UPDATE_LOGIN
932 ,ORG_ID)
933 VALUES
934 (L_VAR_RENT_SUMM_ID
935 ,P_VAR_RENT_ID
936 ,P_PERIOD_ID
937 ,P_LINE_ITEM_ID
938 ,P_INVOICE_DATE
939 ,L_TOT_ACT_VOL
940 ,L_TOT_FOR_VOL
941 ,P_TOT_DED
942 ,round(L_ACT_VAR_RENT,g_precision)
943 ,round(L_FOR_VAR_RENT,g_precision)
944 ,P_GRP_DATE_ID
945 ,P_GROUP_DATE
946 ,SYSDATE
947 ,NVL(fnd_profile.value('USER_ID'),0)
948 ,SYSDATE
949 ,NVL(fnd_profile.value('USER_ID'),0)
950 ,NVL(fnd_profile.value('LOGIN_ID'),0)
951 ,g_org_id);
952
953 ELSE
954
955 /* update pn_var_rent_SUMm for the combination of line_item_id AND grp_date_id*/
956
957 UPDATE pn_var_rent_SUMm_all
958 SET tot_act_vol = decode(g_rent_TYPE,'ACTUAL',p_tot_vol,tot_act_vol),
959 tot_ded = decode(g_rent_TYPE,'ACTUAL',p_tot_ded,tot_ded),
960 act_var_rent = decode(g_rent_TYPE,'ACTUAL',round(l_var_rent,g_precision),
961 round(act_var_rent,g_precision)),
962 tot_for_vol = decode(g_rent_TYPE,'FORECASTED',p_tot_vol,tot_for_vol),
963 for_var_rent = decode(g_rent_TYPE,'FORECASTED',round(l_var_rent,g_precision),
964 round(act_var_rent,g_precision)),
965 last_update_date = SYSDATE,
966 last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
967 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
968 WHERE line_item_id = p_line_item_id
969 AND grp_date_id = p_grp_date_id;
970
971 END IF;
972
973
974
975 pnp_debug_pkg.log('pn_variable_amount_pkg.process_rent (-) ');
976
977 EXCEPTION
978 when others then
979 pnp_debug_pkg.log('Error in pn_variable_amount_pkg.process_rent :'||TO_CHAR(sqlcode)||' : '||sqlerrm);
980 raise;
981
982 END process_rent;
983
984
985 ----------------------------------------------------------------------------
986 -- PROCEDURE : Insert_invoice
987 --
988 --
989 -----------------------------------------------------------------------------
990 PROCEDURE Insert_invoice(p_calc_TYPE IN VARCHAR2,
991 p_period_id IN NUMBER,
992 p_var_rent_id IN NUMBER)
993 IS
994 CURSOR csr_get_rent(ip_period_id NUMBER,
995 ip_invoice_date DATE) is
996 SELECT ROUND(SUM(act_var_rent),g_precision) actual_rent,
997 ROUND(SUM(for_var_rent),g_precision) for_rent,
998 ROUND( decode( SUM(act_var_rent),null,SUM(act_var_rent),
999 apply_constraints(ip_period_id,SUM(act_var_rent))
1000 ),g_precision
1001 )constr_act_rent,
1002 (SUM(tot_act_vol) - SUM(tot_ded)) actual_volume
1003 FROM pn_var_rent_summ_all
1004 WHERE period_id= ip_period_id
1005 AND invoice_date = ip_invoice_date;
1006
1007 CURSOR csr_get_invoice(ip_period_id NUMBER,
1008 ip_invoice_date DATE)IS
1009 SELECT inv_rent.rowid,
1010 inv_rent.var_rent_inv_id,
1011 inv_rent.adjust_num,
1012 inv_rent.for_per_rent,
1013 inv_rent.abatement_appl,
1014 inv_rent.negative_rent,
1015 inv_rent.rec_abatement,
1016 inv_rent.rec_abatement_override,
1017 inv_rent.forecasted_term_status,
1018 inv_rent.forecasted_exp_code,
1019 inv_rent.actual_exp_code,
1020 inv_rent.variance_exp_code
1021 FROM pn_var_rent_inv_all inv_rent
1022 WHERE inv_rent.period_id = ip_period_id
1023 AND inv_rent.invoice_date = ip_invoice_date
1024 AND inv_rent.adjust_num = (SELECT MAX(inv.adjust_num)
1025 FROM pn_var_rent_inv_all inv
1026 WHERE inv.invoice_date = inv_rent.invoice_date
1027 AND inv.period_id = inv_rent.period_id);
1028
1029 CURSOR csr_cum_vol (ip_period_id NUMBER,
1030 ip_invoice_date DATE) IS
1031 SELECT (summ.tot_act_vol - NVL(summ.tot_ded,0)) cum_act_vol
1032 FROM pn_var_rent_summ_all summ
1033 WHERE summ.group_date = (SELECT MAX(summ1.group_date)
1034 FROM pn_var_rent_summ_all summ1
1035 WHERE summ1.invoice_date = ip_invoice_date
1036 AND summ1.period_id = ip_period_id)
1037 AND summ.period_id = ip_period_id;
1038
1039 l_actual_rent NUMBER;
1040 l_forecast_rent NUMBER;
1041 l_constr_act_rent NUMBER;
1042 l_rowid ROWID := null;
1043 l_adjust_num NUMBER := 0;
1044 l_for_rent NUMBER;
1045 l_abt_appl NUMBER := 0;
1046 l_negative_rent NUMBER := 0;
1047 l_for_term_status pn_var_rent_inv.forecasted_term_status%TYPE:= 'N';
1048 l_for_exp_code pn_var_rent_inv.forecasted_exp_code%TYPE:= 'N';
1049 l_actual_exp_code pn_var_rent_inv.actual_exp_code%TYPE :='N';
1050 l_variance_exp_code pn_var_rent_inv.variance_exp_code%TYPE :='N';
1051 l_var_rent_inv_id NUMBER := null;
1052 l_rent_TYPE VARCHAR2(30);
1053 l_rent_inv_id NUMBER := null;
1054 l_rowid_out ROWID ;
1055 l_insert BOOLEAN := FALSE;
1056 l_actual_invoiced_amt pn_var_rent_inv.actual_invoiced_amount%TYPE := 0;
1057 l_actual_volume pn_var_rent_inv.tot_act_vol%TYPE;
1058 l_rec_abatement pn_var_rent_inv.rec_abatement%TYPE;
1059 l_rec_abatement_override pn_var_rent_inv.rec_abatement_override%TYPE;
1060
1061 BEGIN
1062 pnp_debug_pkg.log('pn_variable_amount_pkg.Insert_Invoice (+) ');
1063
1064 if g_invoice_on = 'FORECASTED' AND g_rent_TYPE = 'ACTUAL' then
1065 l_rent_TYPE := 'VARIANCE';
1066 elsif g_invoice_on = 'FORECASTED' AND g_rent_TYPE = 'FORECASTED' then
1067 l_rent_TYPE := 'FORECASTED';
1068 elsif g_invoice_on = 'ACTUAL' AND g_rent_TYPE = 'ACTUAL' then
1069 l_rent_TYPE := 'ACTUAL';
1070 end if;
1071
1072
1073 /* Insert/Update pn_var_rent_inv only for those invoice dates for
1074 which calculation has been done by checking if that invoice
1075 date AND period id exists in invoice_tbl. This table has been populated
1076 in procedure process_rent */
1077
1078 FOR i in 1.. invoice_tbl.COUNT
1079 LOOP
1080
1081 open csr_get_rent (invoice_tbl(i).period_id, invoice_tbl(i).invoice_date);
1082 fetch csr_get_rent into l_actual_rent, l_forecast_rent, l_constr_act_rent,l_actual_volume;
1083 close csr_get_rent;
1084
1085 /* If invoicing is on cumulative get the actual volume of the MAX group date from table
1086 pn_var_rent_SUMm because that will have the cumulative volume for the invoice date */
1087
1088 IF g_cumulative = 'Y' THEN
1089
1090 open csr_cum_vol(invoice_tbl(i).period_id, invoice_tbl(i).invoice_date);
1091 fetch csr_cum_vol into l_actual_volume;
1092 close csr_cum_vol;
1093
1094 END IF;
1095
1096
1097 /* Get all the neccessary info from pn_var_rent_inv for invoice date AND period_id
1098 where adjust_num is MAXimum adjust num for that invoice date */
1099
1100 l_rowid := null;
1101
1102 open csr_get_invoice(invoice_tbl(i).period_id,invoice_tbl(i).invoice_date);
1103 fetch csr_get_invoice INTO l_rowid,l_var_rent_inv_id,l_adjust_num,l_for_rent,l_abt_appl,
1104 l_negative_rent,l_rec_abatement,l_rec_abatement_override,
1105 l_for_term_status,l_for_exp_code,l_actual_exp_code,l_variance_exp_code;
1106
1107 IF csr_get_invoice%NOTFOUND THEN
1108
1109 /*A record doesnt exist in pn_var_rent_inv for the combination of period_id AND invoice_date */
1110
1111 l_insert := TRUE;
1112 l_adjust_num := 0;
1113 l_for_term_status := 'N';
1114 l_for_exp_code := 'N';
1115 l_rec_abatement := NULL;
1116 l_rec_abatement_override := NULL;
1117
1118 IF l_rent_TYPE = 'FORECASTED' THEN
1119 l_abt_appl := NULL;
1120 l_negative_rent := NULL;
1121 l_actual_invoiced_amt := NULL;
1122 ELSE
1123 l_abt_appl := 0;
1124 l_negative_rent := 0;
1125 END IF;
1126
1127
1128 ELSE
1129
1130 l_insert := FALSE;
1131
1132 IF (g_invoice_on = 'ACTUAL' AND l_actual_exp_code = 'Y' ) OR
1133 (g_invoice_on = 'FORECASTED' AND l_variance_exp_code = 'Y' ) THEN
1134 l_insert := TRUE;
1135 l_adjust_num := l_adjust_num + 1;
1136 ELSE
1137 l_insert := FALSE;
1138 END IF;
1139
1140 END IF;
1141 close csr_get_invoice;
1142
1143 IF l_insert THEN
1144
1145 pnp_debug_pkg.log('Insert_Invoice - inserting into pn_var_rent_inv');
1146
1147
1148 PN_VAR_RENT_INV_PKG.INSERT_ROW (
1149 X_ROWID => l_rowid_out,
1150 X_VAR_RENT_INV_ID => l_rent_inv_id,
1151 X_ADJUST_NUM => l_adjust_num,
1152 X_INVOICE_DATE => invoice_tbl(i).invoice_date,
1153 X_FOR_PER_RENT => l_forecast_rent,
1154 X_TOT_ACT_VOL => l_actual_volume,
1155 X_ACT_PER_RENT => l_actual_rent,
1156 X_CONSTR_ACTUAL_RENT => l_constr_act_rent,
1157 X_ABATEMENT_APPL => l_abt_appl,
1158 X_REC_ABATEMENT => l_rec_abatement,
1159 X_REC_ABATEMENT_OVERRIDE => l_rec_abatement_override,
1160 X_NEGATIVE_RENT => l_negative_rent,
1161 X_ACTUAL_INVOICED_AMOUNT => l_actual_invoiced_amt,
1162 X_PERIOD_ID => invoice_tbl(i).period_id,
1163 X_VAR_RENT_ID => p_var_rent_id,
1164 X_FORECASTED_TERM_STATUS => l_for_term_status,
1165 X_VARIANCE_TERM_STATUS => 'N',
1166 X_ACTUAL_TERM_STATUS => 'N',
1167 X_FORECASTED_EXP_CODE => l_for_exp_code,
1168 X_VARIANCE_EXP_CODE => 'N',
1169 X_ACTUAL_EXP_CODE => 'N',
1170 X_COMMENTS => null,
1171 X_ATTRIBUTE_CATEGORY => null,
1172 X_ATTRIBUTE1 => null,
1173 X_ATTRIBUTE2 => null,
1174 X_ATTRIBUTE3 => null,
1175 X_ATTRIBUTE4 => null,
1176 X_ATTRIBUTE5 => null,
1177 X_ATTRIBUTE6 => null,
1178 X_ATTRIBUTE7 => null,
1179 X_ATTRIBUTE8 => null,
1180 X_ATTRIBUTE9 => null,
1181 X_ATTRIBUTE10 => null,
1182 X_ATTRIBUTE11 => null,
1183 X_ATTRIBUTE12 => null,
1184 X_ATTRIBUTE13 => null,
1185 X_ATTRIBUTE14 => null,
1186 X_ATTRIBUTE15 => null,
1187 X_CREATION_DATE => SYSDATE,
1188 X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
1189 X_LAST_UPDATE_DATE => SYSDATE,
1190 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
1191 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('LOGIN_ID'),0),
1192 X_ORG_ID => g_org_id );
1193
1194 l_rent_inv_id := null;
1195 l_rowid_out := null;
1196
1197
1198 ELSE
1199 pnp_debug_pkg.log('Insert_Invoice - Updating PN_VAR_RENT_INV ');
1200
1201 /* Delete payment terms from pn_payment_terms created
1202 for the combination of var_rent_inv_id AND rent_TYPE
1203 that are in the draft status since we are recalculating
1204 AND updating the invoice for forecasted rent*/
1205
1206 DELETE from pn_payment_terms_all
1207 WHERE var_rent_inv_id = l_var_rent_inv_id
1208 AND status <> c_payment_term_status_approved
1209 AND var_rent_TYPE = l_rent_TYPE;
1210
1211 UPDATE pn_var_rent_inv_all
1212 SET for_per_rent = l_forecast_rent,
1213 act_per_rent = l_actual_rent,
1214 constr_actual_rent = l_constr_act_rent,
1215 tot_act_vol = ROUND(l_actual_volume,g_precision), -- bug # 6007571
1216 forecasted_term_status = decode(l_rent_TYPE,'FORECASTED','N',forecasted_term_status),
1217 variance_term_status = decode(l_rent_TYPE,'VARIANCE','N',variance_term_status),
1218 actual_term_status = decode(l_rent_TYPE,'ACTUAL','N',actual_term_status),
1219 last_update_date = SYSDATE,
1220 last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
1221 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
1222 WHERE rowid = l_rowid;
1223
1224 END IF;
1225
1226
1227 END LOOP; -- invoice_tbl
1228
1229 pnp_debug_pkg.log('pn_variable_amount_pkg.Insert_Invoice (-)');
1230
1231 EXCEPTION
1232 when others then
1233 pnp_debug_pkg.log('Error in pn_variable_amount_pkg.Insert_invoice :'||TO_CHAR(sqlcode)||' : '||sqlerrm);
1234 raise;
1235
1236 END Insert_invoice;
1237
1238 ----------------------------------------------------------------------------
1239 -- PROCEDURE : apply_abatements
1240 --
1241 -- 30-Jan-2002 Pooja Sidhu o Fix for bug# 2207343. Calculate negative rent available
1242 -- for every invoice date fetched from the cursor csr_get_inv.
1243 -- when applying negative rent.Removed condition to calculate
1244 -- negative rent available only for the first record fetched.
1245 --
1246 -- 11-Mar-2002 Pooja Sidhu o Added logic for applying recurring abatements.
1247 --
1248 -----------------------------------------------------------------------------
1249
1250 PROCEDURE apply_abatements (p_var_rent_id IN NUMBER)
1251 IS
1252
1253 /*get the invoices for all the periods from pn_var_rent_inv where the actual /actual-forecasted
1254 variable rent amount has not been transferred */
1255
1256 CURSOR csr_get_inv(p_var_rent_id NUMBER) is
1257 SELECT inv.rowid,
1258 inv.adjust_num,
1259 inv.var_rent_inv_id,
1260 inv.constr_actual_rent,
1261 inv.actual_invoiced_amount,
1262 inv.abatement_appl,
1263 inv.negative_rent,
1264 inv.rec_abatement,
1265 inv.rec_abatement_override,
1266 inv.invoice_date
1267 FROM pn_var_rent_inv_all inv
1268 WHERE inv.var_rent_id = p_var_rent_id
1269 AND inv.constr_actual_rent is not null
1270 AND decode(g_invoice_on,'ACTUAL',inv.actual_exp_code,'FORECASTED',inv.variance_exp_code)='N'
1271 ORDER BY inv.period_id,inv.invoice_date,inv.adjust_num;
1272
1273 /* Get the abatement amount defined in the agreements tab */
1274
1275 CURSOR csr_get_abt(ip_var_rent_id NUMBER)
1276 IS
1277 SELECT NVL(abatement_amount,0),
1278 negative_rent
1279 FROM pn_var_rents_all
1280 WHERE var_rent_id = ip_var_rent_id;
1281
1282 /* Get the abatement amount that has been applied to transferred invoices.
1283 Here we only consider those invoices where the adjust num is the MAXimum
1284 number for all the transferred invoices belonging to that invoice date */
1285
1286 CURSOR csr_get_abt_appl(ip_var_rent_id NUMBER)
1287 IS
1288 SELECT SUM(inv.abatement_appl)
1289 FROM pn_var_rent_inv_all inv
1290 WHERE inv.adjust_num =(SELECT MAX(inv1.adjust_num)
1291 FROM pn_var_rent_inv_all inv1
1292 WHERE inv1.invoice_date = inv.invoice_date
1293 AND inv1.var_rent_id = inv.var_rent_id
1294 AND decode(g_invoice_on,'ACTUAL',inv1.actual_exp_code,
1295 'FORECASTED',inv1.variance_exp_code)='Y')
1296 AND inv.var_rent_id = ip_var_rent_id;
1297
1298 /* get the last invoice belonging to the variable rent.*/
1299
1300 CURSOR csr_last_inv(p_var_rent_id NUMBER)
1301 IS
1302 SELECT MAX(invoice_date)
1303 FROM pn_var_grp_dates_all
1304 WHERE var_rent_id = p_var_rent_id;
1305
1306 CURSOR csr_neg_avail (ip_var_rent_id NUMBER,
1307 ip_invoice_date DATE) IS
1308 SELECT ABS(NVL(SUM(constr_actual_rent),0)) l_negative_avialable
1309 FROM pn_var_rent_inv_all inv
1310 WHERE inv.var_rent_id = ip_var_rent_id
1311 AND inv.invoice_date < ip_invoice_date
1312 AND inv.adjust_num = (select MAX(inv1.adjust_num)
1313 from pn_var_rent_inv_all inv1
1314 where inv1.var_rent_id = inv.var_rent_id
1315 AND inv1.invoice_date = inv.invoice_date)
1316 AND inv.constr_actual_rent < 0;
1317
1318 CURSOR csr_neg_appl (ip_var_rent_id NUMBER,
1319 ip_invoice_date DATE) IS
1320 SELECT NVL(SUM(negative_rent),0)
1321 FROM pn_var_rent_inv_all inv
1322 WHERE inv.var_rent_id = ip_var_rent_id
1323 AND inv.invoice_date < ip_invoice_date
1324 AND inv.adjust_num = (select MAX(inv1.adjust_num)
1325 from pn_var_rent_inv_all inv1
1326 where inv1.var_rent_id = inv.var_rent_id
1327 AND inv1.invoice_date = inv.invoice_date);
1328
1329 CURSOR csr_prev_inv_amt (ip_var_rent_id NUMBER,
1330 ip_invoice_date DATE,
1331 ip_adjust_num NUMBER) IS
1332 SELECT NVL(SUM(actual_invoiced_amount),0)
1333 FROM pn_var_rent_inv_all inv
1334 WHERE inv.var_rent_id = ip_var_rent_id
1335 AND inv.invoice_date = ip_invoice_date
1336 AND inv.adjust_num < ip_adjust_num;
1337
1338
1339 l_total_abatement pn_var_rents.abatement_amount%TYPE := 0;
1340 l_total_abt_appl NUMBER := 0;
1341 l_negative_rent pn_var_rent_inv.negative_rent%TYPE := 0;
1342 l_actual_invoice_amt pn_var_rent_inv.actual_invoiced_amount%TYPE := 0;
1343 l_negative_available NUMBER := 0;
1344 l_negative_applied NUMBER := 0;
1345 l_abatement_applied NUMBER := 0;
1346 l_negative_rent_flag pn_var_rents.negative_rent%TYPE;
1347 l_last_invoice_dt pn_var_grp_dates.invoice_date%TYPE;
1348 l_prev_invoiced_amt NUMBER;
1349 l_rec_abatement NUMBER := 0;
1350
1351 BEGIN
1352 pnp_debug_pkg.log('pn_variable_amount_pkg.apply_abatements (+) :');
1353
1354
1355 OPEN csr_get_abt(p_var_rent_id);
1356 FETCH csr_get_abt into l_total_abatement,l_negative_rent_flag;
1357 CLOSE csr_get_abt;
1358 OPEN csr_get_abt_appl(p_var_rent_id);
1359 FETCH csr_get_abt_appl into l_total_abt_appl;
1360 CLOSE csr_get_abt_appl;
1361
1362
1363 /* Get the abatement avaiable as the difference of the total abatement avail to the
1364 variable rent AND the abatement that has already been applied to tranferred invoices*/
1365
1366 l_total_abatement := l_total_abatement - NVL(l_total_abt_appl,0);
1367
1368 /* Get the last invoice date belonging to the variable rent.*/
1369
1370 OPEN csr_last_inv(p_var_rent_id);
1371 FETCH csr_last_inv into l_last_invoice_dt;
1372 CLOSE csr_last_inv;
1373
1374 FOR rec_get_inv in csr_get_inv (p_var_rent_id => p_var_rent_id)
1375 LOOP
1376 l_negative_available := 0;
1377 l_negative_applied := 0;
1378 l_negative_rent :=0;
1379 l_abatement_applied := 0;
1380 l_actual_invoice_amt := 0;
1381 l_prev_invoiced_amt := 0;
1382 l_rec_abatement := 0;
1383
1384 IF l_negative_rent_flag = 'DEFER' AND (rec_get_inv.invoice_date = l_last_invoice_dt or
1385 rec_get_inv.constr_actual_rent > 0) THEN
1386
1387 /* Get the negative rent avaiable */
1388 open csr_neg_avail(p_var_rent_id, rec_get_inv.invoice_date);
1389 fetch csr_neg_avail into l_negative_available;
1390 close csr_neg_avail;
1391
1392 /* Get the negative rent applied */
1393 open csr_neg_appl(p_var_rent_id, rec_get_inv.invoice_date);
1394 fetch csr_neg_appl into l_negative_applied;
1395 close csr_neg_appl;
1396
1397 pnp_debug_pkg.log('apply_abatements - Invoice Date :'||rec_get_inv.invoice_date);
1398 pnp_debug_pkg.log('apply_abatements - Negative Rent Avaiable :'||l_negative_available);
1399 pnp_debug_pkg.log('apply_abatements - Negative Rent Applied :'||l_negative_applied);
1400
1401 l_negative_available := l_negative_available - l_negative_applied;
1402
1403 END IF;
1404
1405
1406 /* Get the rent transferred for the invoice date */
1407
1408 open csr_prev_inv_amt(p_var_rent_id, rec_get_inv.invoice_date, rec_get_inv.adjust_num);
1409 fetch csr_prev_inv_amt into l_prev_invoiced_amt;
1410 close csr_prev_inv_amt;
1411
1412 /* Apply negative rent */
1413
1414 IF l_negative_rent_flag = 'DEFER' AND
1415 rec_get_inv.invoice_date = l_last_invoice_dt then
1416
1417 l_actual_invoice_amt := (rec_get_inv.constr_actual_rent - l_negative_available)
1418 - l_prev_invoiced_amt;
1419 l_negative_rent := l_negative_available;
1420
1421 ELSIF rec_get_inv.constr_actual_rent < 0 then
1422
1423 l_negative_rent := 0;
1424
1425 IF l_negative_rent_flag = 'IGNORE' THEN
1426 l_actual_invoice_amt := 0 - l_prev_invoiced_amt;
1427
1428 ELSIF l_negative_rent_flag = 'CREDIT' THEN
1429
1430 l_actual_invoice_amt := rec_get_inv.constr_actual_rent - l_prev_invoiced_amt;
1431
1432 ELSIF l_negative_rent_flag = 'DEFER' THEN
1433
1434 l_actual_invoice_amt := 0 - l_prev_invoiced_amt;
1435
1436 END IF;
1437
1438 ELSE
1439 l_negative_rent := LEAST(rec_get_inv.constr_actual_rent, l_negative_available);
1440
1441 l_actual_invoice_amt := (rec_get_inv.constr_actual_rent - l_prev_invoiced_amt) -
1442 l_negative_rent;
1443 END IF;
1444
1445
1446 /* Apply abatements */
1447
1448 IF l_actual_invoice_amt > 0 THEN
1449 IF rec_get_inv.adjust_num = 0 AND l_total_abatement >= 0 THEN
1450 l_abatement_applied := LEAST(l_actual_invoice_amt, l_total_abatement);
1451 l_actual_invoice_amt:= l_actual_invoice_amt - LEAST(l_actual_invoice_amt, l_total_abatement);
1452 l_total_abatement := l_total_abatement - l_abatement_applied;
1453 ELSE
1454 l_actual_invoice_amt:= l_actual_invoice_amt - NVL(rec_get_inv.abatement_appl,0);
1455 l_abatement_applied := rec_get_inv.abatement_appl;
1456 END IF;
1457 END IF;
1458
1459 /* Apply recurring Abatements */
1460
1461 pnp_debug_pkg.log('apply_abatements - l_actual_invoice_amt :'||l_actual_invoice_amt);
1462 pnp_debug_pkg.log('applying recurring Abatements ... ');
1463
1464 IF rec_get_inv.adjust_num > 0 THEN
1465 l_rec_abatement := NVL(rec_get_inv.rec_abatement_override,rec_get_inv.rec_abatement);
1466 ELSIF l_actual_invoice_amt > 0 THEN
1467 l_rec_abatement := NVL(rec_get_inv.rec_abatement_override,
1468 LEAST(l_actual_invoice_amt,rec_get_inv.rec_abatement));
1469 ELSIF l_actual_invoice_amt <= 0 THEN
1470 l_rec_abatement := rec_get_inv.rec_abatement_override;
1471 END IF;
1472
1473 l_actual_invoice_amt := l_actual_invoice_amt - NVL(l_rec_abatement,0) ;
1474
1475 pnp_debug_pkg.log('apply_abatements - l_rec_abatement : '||l_rec_abatement);
1476 pnp_debug_pkg.log('apply_abatements - l_actual_invoice_amt :'|| l_actual_invoice_amt);
1477 pnp_debug_pkg.log('apply_abatements - l_negative_rent :'||l_negative_rent);
1478 pnp_debug_pkg.log('apply_abatements - rec_get_inv.negative_rent :'||rec_get_inv.negative_rent);
1479 pnp_debug_pkg.log('apply_abatements - l_abatement_applied :'||l_abatement_applied);
1480 pnp_debug_pkg.log('apply_abatements - rec_get_inv.abatement_appl :'||rec_get_inv.abatement_appl);
1481 pnp_debug_pkg.log('apply_abatements - l_abatement_applied :'||l_abatement_applied);
1482 pnp_debug_pkg.log('recurring abt applied - l_rec_abatement:'||l_rec_abatement);
1483 pnp_debug_pkg.log('apply_abatements - l_actual_invoice_amt :'||l_actual_invoice_amt);
1484 pnp_debug_pkg.log('apply_abatements- rec_get_inv.actual_invoiced_amount :'||rec_get_inv. actual_invoiced_amount);
1485
1486 IF l_negative_rent = rec_get_inv.negative_rent AND
1487 l_abatement_applied = rec_get_inv.abatement_appl AND
1488 l_actual_invoice_amt = rec_get_inv.actual_invoiced_amount or
1489 ((l_negative_rent is null AND rec_get_inv.negative_rent is null) AND
1490 (l_abatement_applied is null AND rec_get_inv.abatement_appl is null) AND
1491 (l_actual_invoice_amt is null AND rec_get_inv.actual_invoiced_amount is null)) THEN
1492
1493 null;
1494
1495 ELSE
1496
1497 DELETE from pn_payment_terms_all
1498 WHERE status <> c_payment_term_status_approved
1499 AND var_rent_inv_id = rec_get_inv.var_rent_inv_id
1500 AND var_rent_TYPE = decode(g_invoice_on,'ACTUAL','ACTUAL','FORECASTED','VARIANCE');
1501
1502 UPDATE pn_var_rent_inv_all
1503 SET abatement_appl = l_abatement_applied,
1504 actual_invoiced_amount = l_actual_invoice_amt,
1505 negative_rent = l_negative_rent,
1506 actual_term_status = 'N',
1507 variance_term_status = 'N',
1508 last_update_date = SYSDATE,
1509 last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
1510 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
1511 WHERE rowid = rec_get_inv.rowid;
1512
1513 END IF;
1514
1515
1516 END LOOP;
1517
1518 pnp_debug_pkg.log('pn_variable_amount_pkg.apply_abatements (-):');
1519
1520 EXCEPTION
1521 when others then
1522 pnp_debug_pkg.log('Error in pn_variable_amount_pkg.apply_abatements :'||TO_CHAR(sqlcode)||' : '||sqlerrm);
1523 raise;
1524
1525 END apply_abatements;
1526
1527 -----------------------------------------------------------------------------
1528 -- PROCEDURE : apply_constraints
1529 --
1530 -- Based upon the value passed for constr_cat_code get the constraints by period
1531 --
1532 -----------------------------------------------------------------------------
1533 FUNCTION apply_constraints(p_period_id IN NUMBER,
1534 p_actual_rent IN NUMBER)
1535 RETURN NUMBER
1536 IS
1537 Cursor csr_get_constr(p_period_id NUMBER) is
1538 SELECT TYPE_code,
1539 amount
1540 FROM pn_var_constraints_all
1541 WHERE period_id = p_period_id
1542 AND constr_cat_code = 'VARENT';
1543
1544 l_lower_bound NUMBER;
1545 l_upper_bound NUMBER;
1546 l_constr_act_rent NUMBER;
1547
1548 BEGIN
1549 pnp_debug_pkg.log( 'pn_variable_amount_pkg.apply_constraints : (+) ');
1550
1551 FOR rec_get_constr in csr_get_constr(p_period_id)
1552 LOOP
1553 IF rec_get_constr.TYPE_code = 'MIN' THEN
1554 l_lower_bound := rec_get_constr.amount;
1555 ELSIF rec_get_constr.TYPE_code = 'MAX' THEN
1556 l_upper_bound := rec_get_constr.amount;
1557 END IF;
1558 END LOOP;
1559
1560 /* Apply constraints to Actual variable rent */
1561
1562 pnp_debug_pkg.log('apply_constraints - Lower bound :'|| l_lower_bound);
1563 pnp_debug_pkg.log('apply_constraints - Upper bound :'|| l_upper_bound);
1564 pnp_debug_pkg.log('apply_constraints - Actual Rent :'|| p_actual_rent);
1565
1566 IF p_actual_rent < NVL(l_lower_bound,p_actual_rent) THEN
1567 l_constr_act_rent := l_lower_bound;
1568 ELSIF p_actual_rent > NVL(l_upper_bound,p_actual_rent) THEN
1569 l_constr_act_rent := l_upper_bound;
1570 ELSE
1571 l_constr_act_rent := p_actual_rent;
1572 END IF;
1573
1574 pnp_debug_pkg.log('apply_constraints - Constrained Actual rent :'||l_constr_act_rent);
1575
1576 RETURN l_constr_act_rent;
1577 pnp_debug_pkg.log( 'pn_variable_amount_pkg.apply_constraints : (-) ');
1578 END apply_constraints;
1579
1580 -----------------------------------------------------------------------------
1581 -- FUNCTION : find_varrent_exists
1582 --
1583 -- find if row exists for line_item_id,grp_date_id combination
1584 -- in table pn_var_rent_SUMm
1585 --
1586 -----------------------------------------------------------------------------
1587 FUNCTION find_varrent_exists (p_line_item_id IN NUMBER,
1588 p_grp_date_id IN NUMBER)
1589 RETURN VARCHAR2
1590 IS
1591
1592 l_varrent_exists VARCHAR2(1) := 'N';
1593
1594 BEGIN
1595
1596 pnp_debug_pkg.log('pn_variable_amount_pkg.find_varrent_exists (+) ');
1597
1598 SELECT 'Y'
1599 INTO l_varrent_exists
1600 FROM dual
1601 WHERE EXISTS (SELECT null
1602 FROM pn_var_rent_summ_all
1603 WHERE line_item_id = p_line_item_id
1604 AND grp_date_id = p_grp_date_id);
1605
1606 RETURN l_varrent_exists;
1607
1608 EXCEPTION
1609 WHEN no_data_found
1610 THEN RETURN 'N';
1611
1612 pnp_debug_pkg.log('pn_variable_amount_pkg.find_varrent_exists (-) ');
1613
1614 END find_varrent_exists;
1615
1616
1617 ----------------------------------------------------------------------------
1618 -- PROCEDURE : get_transferred_flag
1619 --
1620 ----------------------------------------------------------------------------
1621
1622 PROCEDURE get_transferred_flag(p_period_id IN NUMBER,
1623 p_invoice_date IN DATE,
1624 p_actual_flag OUT NOCOPY VARCHAR2,
1625 p_forecasted_flag OUT NOCOPY VARCHAR2,
1626 p_variance_flag OUT NOCOPY VARCHAR2)
1627 IS
1628 BEGIN
1629 pnp_debug_pkg.log('pn_variable_amount_pkg.get_transferred_flag (+) ');
1630
1631 SELECT distinct actual_exp_code,forecasted_exp_code,variance_exp_code
1632 INTO p_actual_flag,p_forecasted_flag,p_variance_flag
1633 FROM pn_var_grp_dates_all
1634 WHERE period_id = p_period_id
1635 AND invoice_date = p_invoice_date;
1636
1637 EXCEPTION
1638 WHEN no_data_found then null;
1639 WHEN others then pnp_debug_pkg.log ('Error in pn_variable_amount_pkg.get_transferred_flag :'||
1640 TO_CHAR(sqlcode)||': '||sqlerrm);
1641
1642 pnp_debug_pkg.log('pn_variable_amount_pkg.get_transferred_flag (-) ');
1643
1644 END get_transferred_flag;
1645
1646
1647 ------------------------------------------------------------------------
1648 -- PROCEDURE : get_percent_open
1649 -- PARAMETERS : p_var_rent_id
1650 --
1651 -- get the percent days open for each group date
1652 ------------------------------------------------------------------------
1653
1654
1655 PROCEDURE get_percent_open (p_period_id IN NUMBER,
1656 p_cumulative IN VARCHAR2,
1657 p_start_date IN DATE,
1658 p_end_date IN DATE )
1659 IS
1660 l_proration_factor NUMBER;
1661 l_no_of_days NUMBER;
1662 i NUMBER :=0;
1663
1664 BEGIN
1665 pnp_debug_pkg.log('pn_variable_amount_pkg.get_percent_open (+) ');
1666
1667 /* Initialize the PL/SQL table */
1668 grd_date_tbl.delete;
1669
1670 /* Get the group dates proration_factor */
1671
1672 FOR rec_get_gd in csr_get_gd(p_period_id)
1673 LOOP
1674 i := i+1;
1675
1676 /* While deriving the proration factor we are asSUMing the following :
1677 o If invoicing is on cumulative proration factor is the number of days in the period/
1678 the number of days in the in the gl calendar period. Proration factor should have a
1679 value of 1 if the period is not the first or last period.
1680 o if invoicing is on non cumulative proration factor is the number of days in the
1681 reporting period divided by the number of days specified in gl calendar period.
1682 Proration factor should have a value of 1 if the group date is not the first or last grp
1683 date of the variable rent agreement.*/
1684
1685 IF NVL(p_cumulative,'N') = 'Y' THEN
1686 l_no_of_days := (p_end_date - p_start_date) + 1;
1687 ELSE
1688 l_no_of_days := (rec_get_gd.grp_end_date - rec_get_gd.grp_start_date) + 1;
1689 END IF;
1690
1691 l_proration_factor := l_no_of_days / rec_get_gd.proration_factor;
1692
1693 grd_date_tbl(i).grp_date_id := rec_get_gd.grp_date_id;
1694 grd_date_tbl(i).proration_factor := l_proration_factor;
1695 grd_date_tbl(i).invoice_date := rec_get_gd.invoice_date;
1696 grd_date_tbl(i).group_date := rec_get_gd.group_date;
1697
1698 pnp_debug_pkg.log('get_percent_open - group date : '||rec_get_gd.group_date);
1699 pnp_debug_pkg.log('get_percent_open - l_no_of_days : '||l_no_of_days);
1700 pnp_debug_pkg.log('get_percent_open - l_proration_factor : '||l_proration_factor);
1701
1702 END LOOP;
1703
1704 pnp_debug_pkg.log('pn_variable_amount_pkg.get_percent_open (-) ');
1705
1706 EXCEPTION
1707 when others then
1708 pnp_debug_pkg.log('Error in pn_variable_amount_pkg.get_percent_open :'||TO_CHAR(sqlcode)||' : '||sqlerrm);
1709
1710 END get_percent_open;
1711
1712
1713 -------------------------------------------
1714 -- Function : get_deductions
1715 --
1716 -- Get the deductions applicable for all the group dates in a line_item_id
1717 --
1718 -------------------------------------------
1719
1720 PROCEDURE get_deductions(p_line_item_id IN NUMBER)
1721 IS
1722 i NUMBER :=0;
1723
1724 BEGIN
1725
1726 pnp_debug_pkg.log('pn_variable_amount_pkg.get_deductions (+) ');
1727
1728 /* Initialize the PL/SQL table */
1729 deduction_tbl.delete;
1730
1731 /* store the deductions in PL/SQL table */
1732 FOR rec_get_ded in csr_get_ded(p_line_item_id => p_line_item_id)
1733 LOOP
1734
1735 i := i + 1;
1736
1737 deduction_tbl(i).grp_date_id := rec_get_ded.grp_date_id;
1738 deduction_tbl(i).deduction_amt := rec_get_ded.deduction_amt;
1739
1740 END LOOP;
1741
1742 pnp_debug_pkg.log('pn_variable_amount_pkg.get_deductions (-) ');
1743
1744 END get_deductions;
1745
1746
1747
1748 -------------------------------------------------------------------------------
1749 -- Procedure : get_bkp_details
1750 -- Parameter : p_line_item_id
1751 --
1752 -- Store the period breakpoint details AND group breakpoint details for a line
1753 -- item id in a PL/SQL table.
1754 -------------------------------------------------------------------------------
1755
1756 PROCEDURE get_bkp_details (p_line_item_id IN NUMBER)
1757 IS
1758
1759 i NUMBER :=0;
1760
1761 BEGIN
1762
1763 pnp_debug_pkg.log('pn_variable_amount_pkg.get_bkp_details (+) ');
1764
1765 /* Initialize the PL/SQL table */
1766 bkpt_range_tbl.delete;
1767
1768 /* store the break point range in PL/SQL table */
1769 FOR rec_bkpt_range in csr_bkpt_range(p_line_item_id => p_line_item_id)
1770 LOOP
1771
1772 i := i + 1;
1773
1774 bkpt_range_tbl(i).period_bkpt_vol_start := rec_bkpt_range.period_bkpt_vol_start;
1775 bkpt_range_tbl(i).period_bkpt_vol_end := rec_bkpt_range.period_bkpt_vol_end;
1776 bkpt_range_tbl(i).group_bkpt_vol_start := rec_bkpt_range.group_bkpt_vol_start;
1777 bkpt_range_tbl(i).group_bkpt_vol_end := rec_bkpt_range.group_bkpt_vol_end;
1778 bkpt_range_tbl(i).bkpt_rate := rec_bkpt_range.bkpt_rate;
1779 g_breakpoint_TYPE := rec_bkpt_range.breakpoint_TYPE;
1780
1781
1782 END LOOP;
1783
1784 pnp_debug_pkg.log('pn_variable_amount_pkg.get_bkp_details (-) ');
1785
1786 END get_bkp_details;
1787
1788
1789 ------------------------------------------------------------------------
1790 -- PROCEDURE : get_cum_vol_by_grpdt
1791 --
1792 -- Get the cumulative volume from the PL/SQL table cum_vol_tbl
1793 --
1794 ------------------------------------------------------------------------
1795 PROCEDURE get_cum_vol_by_grpdt(p_grp_date_id IN NUMBER,
1796 p_cum_actual_vol OUT NOCOPY NUMBER,
1797 p_cum_for_vol OUT NOCOPY NUMBER,
1798 p_cum_ded OUT NOCOPY NUMBER)
1799 IS
1800 i NUMBER := 1;
1801 BEGIN
1802
1803 pnp_debug_pkg.log('pn_variable_amount_pkg.get_cum_vol_by_grpdt (+) ');
1804
1805 /* Get the existing cumulative actual AND forecasted volume for all the
1806 group date prior to this group date AND put them in the
1807 p_act_cum_vol AND p_for_cum_vol */
1808
1809 FOR i in 1.. cum_vol_tbl.COUNT
1810 LOOP
1811 IF cum_vol_tbl(i).grp_date_id = p_grp_date_id THEN
1812
1813 p_cum_actual_vol := cum_vol_tbl(i).cum_actual_vol;
1814 p_cum_for_vol := cum_vol_tbl(i).cum_for_vol;
1815 p_cum_ded := cum_vol_tbl(i).cum_ded;
1816 exit;
1817
1818 END IF;
1819 END LOOP;
1820
1821 pnp_debug_pkg.log('pn_variable_amount_pkg.get_cum_vol_by_grpdt (-) ');
1822
1823 END;
1824
1825 ------------------------------------------------------------------------
1826 -- PROCEDURE : get_cumulative_volume
1827 -- PARAMETERS :
1828 --
1829 -- Get cumulative actual amount AND cumulative forecasted amount
1830 -- from volume history table for each group date AND store in the
1831 -- PL/SQL table cum_vol_tbl per line.
1832 ------------------------------------------------------------------------
1833 PROCEDURE get_cumulative_volume (p_line_item_id IN NUMBER)
1834 IS
1835
1836 l_cum_actual_vol NUMBER := 0;
1837 l_cum_for_vol NUMBER := 0;
1838 l_cum_ded NUMBER := 0;
1839 l_deduction NUMBER := 0;
1840 i NUMBER := 0;
1841 j NUMBER := 0;
1842
1843
1844 BEGIN
1845
1846 pnp_debug_pkg.log('pn_variable_amount_pkg.get_cumulative_volume (+) ');
1847
1848 /* Initialize the PL/SQL table */
1849 cum_vol_tbl.delete;
1850
1851 FOR rec_cumulative_vol in csr_cumulative_vol(p_line_item_id => p_line_item_id)
1852 LOOP
1853
1854 i := i + 1;
1855 j := 0;
1856
1857 cum_vol_tbl(i).grp_date_id := rec_cumulative_vol.grp_date_id;
1858 l_deduction := 0;
1859
1860 FOR l_ded_COUNT in 1 .. deduction_tbl.COUNT
1861 LOOP
1862 j := j + 1;
1863 IF deduction_tbl(j).grp_date_id = rec_cumulative_vol.grp_date_id THEN
1864 l_deduction := NVL(deduction_tbl(j).deduction_amt,0);
1865 exit;
1866 END IF;
1867
1868 END LOOP;
1869
1870 /* if actual volume for that grp date is null then dont calculate cumulative vol */
1871
1872 IF rec_cumulative_vol.cum_actual_vol is null THEN
1873 cum_vol_tbl(i).cum_actual_vol := null;
1874 ELSE
1875 l_cum_actual_vol := l_cum_actual_vol + rec_cumulative_vol.cum_actual_vol;
1876 cum_vol_tbl(i).cum_actual_vol := l_cum_actual_vol;
1877 l_cum_ded := l_cum_ded + NVL(l_deduction,0);
1878 cum_vol_tbl(i).cum_ded := l_cum_ded;
1879 END IF;
1880
1881 /* if forecasted volume for that grp date is null then cumulative vol for that grp date is null also*/
1882
1883 IF rec_cumulative_vol.cum_for_vol is null THEN
1884 cum_vol_tbl(i).cum_for_vol := null;
1885 ELSE
1886 l_cum_for_vol := l_cum_for_vol + rec_cumulative_vol.cum_for_vol;
1887 cum_vol_tbl(i).cum_for_vol := l_cum_for_vol;
1888 END IF;
1889
1890 END LOOP;
1891
1892 pnp_debug_pkg.log('pn_variable_amount_pkg.get_cumulative_volume (-) ');
1893
1894 END get_cumulative_volume;
1895
1896
1897 ------------------------------------------------------------------------------------
1898 -- PROCEDURE : get_varrent_details
1899 --
1900 --
1901 -----------------------------------------------------------------------------------
1902
1903 PROCEDURE get_varrent_details (p_var_rent_id IN NUMBER,
1904 p_cumulative OUT NOCOPY VARCHAR2,
1905 p_invoice_on OUT NOCOPY VARCHAR2,
1906 p_negative_rent OUT NOCOPY VARCHAR2)
1907 IS
1908 BEGIN
1909
1910 pnp_debug_pkg.log( 'pn_variable_amount_pkg.get_varrent_details : (+) ');
1911
1912 SELECT cumulative_vol,
1913 invoice_on,
1914 negative_rent
1915 INTO p_cumulative,
1916 p_invoice_on,
1917 p_negative_rent
1918 FROM pn_var_rents_all
1919 WHERE var_rent_id = p_var_rent_id;
1920
1921 pnp_debug_pkg.log( 'pn_variable_amount_pkg.get_varrent_details : (-) ');
1922
1923 END get_varrent_details;
1924
1925 ------------------------------------------------------------------------------------
1926 -- PROCEDURE : find_if_term_exists
1927 --
1928 --
1929 -----------------------------------------------------------------------------------
1930
1931 FUNCTION find_if_term_exists (p_var_rent_inv_id IN NUMBER,
1932 p_var_rent_type IN VARCHAR2)
1933 RETURN VARCHAR2
1934 IS
1935 l_term_exists VARCHAR2(1) := 'N';
1936 BEGIN
1937
1938 put_log( 'pn_variable_amount_pkg.find_if_term_exists (+): ');
1939
1940 /* pinky - codev */
1941 IF p_var_rent_type IN ('ADJUSTMENT') THEN
1942
1943 SELECT 'Y'
1944 INTO l_term_exists
1945 FROM DUAL
1946 WHERE exists ( SELECT null
1947 FROM pn_payment_terms_all
1948 WHERE var_rent_inv_id = p_var_rent_inv_id
1949 AND var_rent_TYPE IN ('ACTUAL', 'VARIANCE'));
1950 ELSE
1951 SELECT 'Y'
1952 INTO l_term_exists
1953 FROM DUAL
1954 WHERE exists ( SELECT null
1955 FROM pn_payment_terms_all
1956 WHERE var_rent_inv_id = p_var_rent_inv_id
1957 AND var_rent_TYPE = p_var_rent_TYPE);
1958 END IF;
1959
1960
1961
1962 /* pinky - codev */
1963
1964 RETURN l_term_exists;
1965 EXCEPTION
1966 WHEN no_data_found then
1967 RETURN 'N';
1968
1969 put_log( 'pn_variable_amount_pkg.find_if_term_exists (-): ');
1970
1971 END find_if_term_exists;
1972
1973
1974 ------------------------------------------------------------------------------------
1975 -- PROCEDURE : find_volume_exists
1976 --
1977 -- Find if actual or forecasted volume history exists for all the group dates
1978 -- AND line items in an invoice date
1979 -- 04-OCT-2002 Ashish Kumar --BUG#2643435 remove the outer join (+) in the select stmt.
1980 -----------------------------------------------------------------------------------
1981
1982 FUNCTION find_volume_exists (p_period_id IN NUMBER,
1983 p_invoice_date IN DATE,
1984 p_var_rent_TYPE IN VARCHAR2)
1985 RETURN VARCHAR2
1986 IS
1987 l_volume_exists VARCHAR2(1) := 'N';
1988
1989 BEGIN
1990 pnp_debug_pkg.log('pn_variable_amount_pkg.find_volume_exists (+) : ');
1991
1992 SELECT 'Y'
1993 INTO l_volume_exists
1994 FROM dual
1995 WHERE not exists (SELECT null
1996 FROM pn_var_rent_summ_all summ,
1997 (SELECT gd.period_id,
1998 lines.line_item_id,
1999 gd.grp_date_id
2000 FROM pn_var_lines_all lines,
2001 pn_var_grp_dates_all gd
2002 WHERE gd.period_id = lines.period_id
2003 AND gd.period_id= p_period_id
2004 AND gd.invoice_date =p_invoice_date) itemp
2005 WHERE SUMm.grp_date_id = itemp.grp_date_id
2006 AND SUMm.line_item_id = itemp.line_item_id
2007 GROUP by itemp.period_id,itemp.line_item_id,itemp.grp_date_id
2008 HAVING ((SUM(SUMm.tot_act_vol) is null AND p_var_rent_TYPE = 'ACTUAL') OR
2009 (SUM(SUMm.tot_for_vol) is null AND p_var_rent_TYPE = 'FORECASTED'))
2010 );
2011
2012
2013 RETURN l_volume_exists;
2014
2015 EXCEPTION
2016 WHEN no_data_found then
2017 RETURN 'N';
2018
2019 pnp_debug_pkg.log('pn_variable_amount_pkg.find_volume_exists (-) : ');
2020 END find_volume_exists;
2021
2022 ----------------------------------------------------------------------------
2023 -- FUNCTION : get_prior_transfer_flag
2024 -- Returns 'Y' if there exists a payment term belonging to an invoice date
2025 -- prior to the p_invoice_date that has not been transferred
2026 ----------------------------------------------------------------------------
2027
2028 FUNCTION get_prior_transfer_flag(p_var_rent_inv_id NUMBER,
2029 p_var_rent_TYPE VARCHAR2,
2030 p_var_rent_id NUMBER)
2031 RETURN VARCHAR2 IS
2032
2033 l_exists VARCHAR2(1) := 'N';
2034
2035 CURSOR get_inv_info(p_var_rent_inv_id NUMBER,
2036 p_var_rent_TYPE VARCHAR2,
2037 p_var_rent_id NUMBER,
2038 p_period_id NUMBER) IS
2039 SELECT 'Y' term_exists
2040 FROM dual
2041 WHERE EXISTS (SELECT null
2042 FROM pn_var_rent_inv_all inv
2043 WHERE inv.forecasted_exp_code = decode(p_var_rent_TYPE,'FORECASTED','N',inv.forecasted_exp_code)
2044 AND inv.actual_exp_code = decode(p_var_rent_TYPE,'ACTUAL','N',inv.actual_exp_code)
2045 AND inv.variance_exp_code = decode(p_var_rent_TYPE,'VARIANCE','N',inv.variance_exp_code)
2046 AND inv.invoice_date < ( SELECT inv1.invoice_date
2047 FROM pn_var_rent_inv_all inv1
2048 WHERE inv1.var_rent_inv_id = p_var_rent_inv_id)
2049 AND inv.period_id = nvl(p_period_id,inv.period_id)
2050 AND inv.var_rent_id = p_var_rent_id);
2051
2052
2053 /* Cursor for adjustment invoices */
2054 CURSOR get_inv_adj_info(p_var_rent_inv_id NUMBER,
2055 p_var_rent_TYPE VARCHAR2,
2056 p_var_rent_id NUMBER,
2057 p_period_id NUMBER) IS
2058 SELECT 'Y' term_exists
2059 FROM dual
2060 WHERE EXISTS (SELECT null
2061 FROM pn_var_rent_inv_all inv
2062 WHERE inv.actual_exp_code = 'N'
2063 AND inv.forecasted_exp_code = 'N'
2064 AND inv.variance_exp_code = 'N'
2065 AND inv.invoice_date < ( SELECT inv1.invoice_date
2066 FROM pn_var_rent_inv_all inv1
2067 WHERE inv1.var_rent_inv_id = p_var_rent_inv_id)
2068 AND inv.period_id = nvl(p_period_id,inv.period_id)
2069 AND inv.var_rent_id = p_var_rent_id);
2070
2071
2072 CURSOR first_period_cur IS
2073 SELECT pvp.period_id
2074 FROM pn_var_rents_all pvr , pn_var_periods_all pvp
2075 WHERE pvr.var_rent_id = p_var_rent_id
2076 AND pvr.var_rent_id = pvp.var_rent_id
2077 AND proration_rule IN ('FY', 'FLY')
2078 AND pvp.start_date = pvr.commencement_date;
2079
2080
2081 -- Get the details of
2082 CURSOR period_cur IS
2083 SELECT period_id
2084 FROM pn_var_rent_inv_all
2085 WHERE var_rent_inv_id = p_var_rent_inv_id;
2086
2087 l_period_id NUMBER;
2088 l_cur_period_id NUMBER := NULL;
2089
2090 BEGIN
2091 pnp_debug_pkg.log('pn_variable_amount_pkg.get_prior_transfer_flag (+) : ');
2092
2093
2094 FOR rec IN period_cur LOOP
2095 l_period_id := rec.period_id;
2096 END LOOP;
2097
2098 /* This is a special handling done for FY/FLY as in these we create first year term with an
2099 invoice date lying in second year has period id of first year */
2100 FOR first_period_rec IN first_period_cur LOOP
2101 IF first_period_rec.period_id = l_period_id THEN
2102 l_cur_period_id := l_period_id;
2103 END IF;
2104 END LOOP;
2105
2106 IF p_var_rent_TYPE IN ('FORECASTED', 'ACTUAL', 'VARIANCE') THEN
2107
2108 FOR for_rec IN get_inv_info (p_var_rent_inv_id, p_var_rent_TYPE, p_var_rent_id, l_cur_period_id ) LOOP
2109 l_exists := for_rec.term_exists;
2110 END LOOP;
2111
2112 ELSIF p_var_rent_TYPE = 'ADJUSTMENT' THEN
2113 FOR adj_rec IN get_inv_adj_info (p_var_rent_inv_id, p_var_rent_TYPE, p_var_rent_id, l_cur_period_id) LOOP
2114 l_exists := adj_rec.term_exists;
2115 END LOOP;
2116
2117 END IF;
2118
2119 RETURN l_exists;
2120
2121 pnp_debug_pkg.log('pn_variable_amount_pkg.get_prior_transfer_flag (-) : ');
2122
2123 END get_prior_transfer_flag;
2124
2125 ----------------------------------------------------------------------------
2126 -- FUNCTION : get_prev_inv_amt
2127 --
2128 ----------------------------------------------------------------------------
2129
2130 FUNCTION get_prev_inv_amt (p_var_rent_id NUMBER,
2131 p_invoice_date DATE,
2132 p_adjust_num NUMBER)
2133 RETURN NUMBER IS
2134 CURSOR csr_prev_inv_amt (ip_var_rent_id NUMBER,
2135 ip_invoice_date DATE,
2136 ip_adjust_num NUMBER) IS
2137 SELECT SUM(actual_invoiced_amount)
2138 FROM pn_var_rent_inv_all inv
2139 WHERE inv.var_rent_id = ip_var_rent_id
2140 AND inv.invoice_date = ip_invoice_date
2141 AND inv.adjust_num < ip_adjust_num;
2142
2143 l_prev_inv_amt NUMBER := 0;
2144
2145 BEGIN
2146
2147 pnp_debug_pkg.log('pn_variable_amount_pkg.get_prev_inv_amt (+) : ');
2148
2149 OPEN csr_prev_inv_amt(p_var_rent_id,p_invoice_date,p_adjust_num);
2150 FETCH csr_prev_inv_amt INTO l_prev_inv_amt;
2151 CLOSE csr_prev_inv_amt;
2152
2153 RETURN l_prev_inv_amt;
2154
2155 pnp_debug_pkg.log('pn_variable_amount_pkg.get_prev_inv_amt (-) : ');
2156
2157 END get_prev_inv_amt;
2158
2159 -------------------------------------------------------------------------------------------------
2160 ---
2161 --- FUNCTION get_vol_ded
2162 ---
2163 -------------------------------------------------------------------------------------------------
2164
2165 FUNCTION get_vol_ded(p_line_item_id NUMBER,
2166 p_group_date DATE,
2167 p_TYPE VARCHAR2)
2168 RETURN NUMBER IS
2169 CURSOR csr_vol (p_line_item_id NUMBER,
2170 p_group_date DATE) IS
2171 SELECT summ.tot_act_vol,
2172 summ.tot_for_vol,
2173 summ.tot_ded,
2174 decode(vrent.cumulative_vol ,'N','N','T','N','Y')
2175 FROM pn_var_rent_summ_all summ,
2176 pn_var_rents_all vrent
2177 WHERE summ.line_item_id = p_line_item_id
2178 AND summ.group_date = p_group_date
2179 AND summ.var_rent_id = vrent.var_rent_id;
2180
2181 CURSOR csr_prev_vol(p_line_item_id NUMBER,
2182 p_group_date DATE) IS
2183 SELECT nvl(summ.tot_act_vol,0),
2184 nvl(summ.tot_for_vol,0),
2185 nvl(summ.tot_ded,0)
2186 FROM pn_var_rent_summ_all summ
2187 WHERE summ.group_date =(SELECT max(summ1.group_date)
2188 FROM pn_var_rent_summ_all summ1
2189 WHERE summ1.group_date < p_group_date
2190 AND summ1.line_item_id = p_line_item_id)
2191 AND summ.line_item_id = p_line_item_id;
2192
2193 l_tot_act_vol NUMBER :=0;
2194 l_tot_for_vol NUMBER :=0;
2195 l_tot_ded NUMBER :=0;
2196 l_cumulative_vol pn_var_rents.cumulative_vol%type;
2197 l_prev_act_vol NUMBER := 0;
2198 l_prev_for_vol NUMBER := 0;
2199 l_prev_ded NUMBER := 0;
2200 l_ret_val NUMBER ;
2201
2202 BEGIN
2203
2204 put_log('pn_variable_amount_pkg.get_vol_ded (+) : ');
2205 OPEN csr_vol(p_line_item_id,p_group_date);
2206 FETCH csr_vol into l_tot_act_vol,l_tot_for_vol,l_tot_ded,l_cumulative_vol;
2207 IF csr_vol%NOTFOUND THEN
2208 RETURN 0;
2209 END IF;
2210 CLOSE csr_vol;
2211
2212 OPEN csr_prev_vol(p_line_item_id,p_group_date);
2213 FETCH csr_prev_vol into l_prev_act_vol,l_prev_for_vol,l_prev_ded;
2214 CLOSE csr_prev_vol;
2215
2216 IF p_type = 'FOR' and l_cumulative_vol in('Y ','N') THEN
2217
2218 l_ret_val := l_tot_for_vol - l_prev_for_vol;
2219
2220 ELSIF p_type = 'ACT' and l_cumulative_vol = 'Y' THEN
2221
2222 l_ret_val := NVL(l_tot_act_vol,0) - NVL(l_prev_act_vol,0);
2223
2224 ELSIF p_type = 'DED' and l_cumulative_vol = 'Y' THEN
2225
2226 l_ret_val := l_tot_ded - l_prev_ded;
2227
2228 ELSIF p_type = 'FOR' and l_cumulative_vol = 'N' THEN
2229
2230 l_ret_val := l_tot_for_vol;
2231
2232 ELSIF p_type = 'ACT' and l_cumulative_vol = 'N' THEN
2233
2234 l_ret_val := l_tot_act_vol;
2235
2236 ELSIF p_type = 'DED' and l_cumulative_vol = 'N' THEN
2237
2238 l_ret_val := l_tot_ded;
2239
2240 END IF;
2241
2242 RETURN NVL(l_ret_val,0);
2243
2244 put_log('pn_variable_amount_pkg.get_vol_ded (-) : ');
2245
2246 EXCEPTION
2247 when others then
2248 put_log('Error in pn_variable_amount_pkg.get_vol_ded :'||to_char(sqlcode)||' : '||sqlerrm);
2249
2250 END get_vol_ded;
2251
2252 -------------------------------------------------------------------------------
2253 -- PROCEDURE : process_rent_batch
2254 --
2255 -- It's referenced in the Concurrent Program executable definition -
2256 --
2257 -- Main procedure to be called during calculation(p_calc_TYPE='CALCULATE') or
2258 -- reconciliation(p_calc_TYPE='RECONCILE') from the SRS screen.
2259 --
2260 -- 15-Aug-02 DThota o Added p_period_date parameter to
2261 -- process_rent_batch, CURSOR csr_get_per for Mass
2262 -- Calculate Variable Rent.
2263 -- 09-Jan-03 DThota o Changed p_period_date parameter to VARCHAR2 from
2264 -- DATE in process_rent_batch, CURSOR csr_get_per.
2265 -- Added fnd_date.canonical_to_date before
2266 -- p_period_date in the WHERE clause.
2267 -- Fix for bug # 2733870
2268 -- 23-Jan-03 DThota o Removed comparison of pn_periods_all.end_date
2269 -- to p_period_date from the predicate of the
2270 -- cursor csr_get_per. Fix for bug # 2766223
2271 -- 14-JUL-05 Hrodda o Bug 4284035 - Replaced pn_leases with _ALL table.
2272 -- 23-NOV-05 pikhar o Passed org_id in pn_mo_cache_utils.get_profile_value
2273 -------------------------------------------------------------------------------
2274 PROCEDURE process_rent_batch (
2275 errbuf OUT NOCOPY VARCHAR2,
2276 retcode OUT NOCOPY VARCHAR2,
2277 p_lease_num_from IN VARCHAR2,
2278 p_lease_num_to IN VARCHAR2,
2279 p_location_code_from IN VARCHAR2,
2280 p_location_code_to IN VARCHAR2,
2281 p_vrent_num_from IN VARCHAR2,
2282 p_vrent_num_to IN VARCHAR2,
2283 p_period_num_from IN NUMBER,
2284 p_period_num_to IN NUMBER,
2285 p_responsible_user IN NUMBER,
2286 p_invoice_on IN VARCHAR2 ,
2287 p_var_rent_id IN NUMBER,
2288 p_period_id IN NUMBER,
2289 p_line_item_id IN NUMBER,
2290 p_invoice_date IN DATE,
2291 p_calc_TYPE IN VARCHAR2,
2292 p_period_date IN VARCHAR2,
2293 p_org_id IN NUMBER ) IS
2294
2295 CURSOR csr_get_vrent_wloc IS
2296 SELECT pvr.var_rent_id,
2297 pvr.invoice_on,
2298 pvr.cumulative_vol,
2299 pvr.rent_num,
2300 pl.org_id
2301 FROM pn_leases pl,
2302 pn_lease_details_all pld,
2303 pn_var_rents_all pvr,
2304 pn_locations_all ploc
2305 WHERE pl.lease_id = pvr.lease_id
2306 AND pld.lease_id = pvr.lease_id
2307 AND ploc.location_id = pvr.location_id
2308 AND pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
2309 AND pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
2310 AND ploc.location_code >= NVL(p_location_code_from, ploc.location_code)
2311 AND ploc.location_code <= NVL(p_location_code_to, ploc.location_code)
2312 AND pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
2313 AND pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
2314 AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
2315 AND pvr.invoice_on = NVL(p_invoice_on,pvr.invoice_on)
2316 AND (pl.org_id = p_org_id or p_org_id is null)
2317 ORDER BY pl.lease_id, pvr.var_rent_id;
2318
2319 CURSOR csr_get_vrent_woloc IS
2320 SELECT pvr.var_rent_id,
2321 pvr.invoice_on,
2322 pvr.cumulative_vol,
2323 pvr.rent_num,
2324 pl.org_id
2325 FROM pn_var_rents_all pvr,
2326 pn_leases pl,
2327 pn_lease_details_all pld
2328 WHERE pl.lease_id = pvr.lease_id
2329 AND pld.lease_id = pvr.lease_id
2330 AND pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
2331 AND pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
2332 AND pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
2333 AND pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
2334 AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
2335 AND pvr.var_rent_id = NVL(p_var_rent_id,pvr.var_rent_id)
2336 AND pvr.invoice_on = NVL(p_invoice_on,pvr.invoice_on)
2337 AND (pl.org_id = p_org_id or p_org_id is null)
2338 ORDER BY pl.lease_id, pvr.var_rent_id;
2339
2340 CURSOR csr_get_per(ip_var_rent_id NUMBER) IS
2341 SELECT period_id,
2342 period_num,
2343 start_date,
2344 end_date
2345 FROM pn_var_periods_all
2346 WHERE var_rent_id = ip_var_rent_id
2347 AND period_id = NVL(p_period_id,period_id)
2348 AND start_date <= NVL(fnd_date.canonical_to_date(p_period_date),TO_DATE('12/31/4712','mm/dd/yyyy'))
2349 AND period_num >= NVL(p_period_num_from,period_num)
2350 AND period_num <= NVL(p_period_num_to,period_num);
2351
2352 /* Get all the invoice dates for a period when doing reconciliation */
2353 CURSOR csr_get_invdt(ip_period_id NUMBER) IS
2354 SELECT distinct invoice_date
2355 FROM pn_var_grp_dates_all
2356 WHERE period_id = ip_period_id
2357 AND invoice_date = NVL(p_invoice_date,invoice_date)
2358 ORDER BY invoice_date;
2359
2360 l_org_id NUMBER;
2361
2362
2363 /* Get the currency code AND set of books */
2364
2365 /* Remove for multi-org support */
2366
2367 ---- begin ---
2368
2369 CURSOR csr_currency_code(p_org_ID IN NUMBER) is
2370 SELECT currency_code,
2371 set_of_books_id
2372 FROM gl_sets_of_books
2373 WHERE set_of_books_id = pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',p_org_ID);
2374
2375 ---- end ---
2376
2377 l_var_rent_id pn_var_rents.var_rent_id%TYPE;
2378 l_invoice_on pn_var_rents.invoice_on%TYPE;
2379 l_cumulative pn_var_rents.cumulative_vol%TYPE;
2380 l_rent_num pn_var_rents.rent_num%TYPE;
2381 l_errbuf VARCHAR2(2000);
2382 l_retcode VARCHAR2(2000);
2383 l_ext_precision NUMBER;
2384 l_min_acct_unit NUMBER;
2385
2386 /* Uncomment for multi-org support */
2387
2388 /* l_global_rec pn_mo_cache_utils.GlobalsRecord; */
2389
2390
2391 BEGIN
2392 pnp_debug_pkg.log('pn_variable_amount_pkg.process_rent_batch (+)' );
2393 g_period_date := NVL(fnd_date.canonical_to_date(p_period_date),TO_DATE('12/31/4712','mm/dd/yyyy'));
2394
2395 fnd_message.set_name ('PN','PN_VRAM_PRM');
2396 fnd_message.set_token ('LSNO_FRM', p_lease_num_from);
2397 fnd_message.set_token ('LSNO_TO', p_lease_num_to);
2398 fnd_message.set_token ('LOC_FRM', p_location_code_from);
2399 fnd_message.set_token ('LOC_TO', p_location_code_to);
2400 fnd_message.set_token ('VR_FRM', p_vrent_num_from);
2401 fnd_message.set_token ('VR_TO', p_vrent_num_to);
2402 fnd_message.set_token ('PRD_FRM', p_period_num_from);
2403 fnd_message.set_token ('PRD_TO', p_period_num_to);
2404 fnd_message.set_token ('USR', p_responsible_user);
2405 fnd_message.set_token ('DATE', p_period_date);
2406 pnp_debug_pkg.put_log_msg(fnd_message.get);
2407
2408 pnp_debug_pkg.log ('process_rent_batch - Calculation TYPE : ' || p_calc_TYPE);
2409
2410
2411 /* Retrieve operating unit attributes AND store them in the cache */
2412
2413 /* Uncomment for multi-org support */
2414
2415 ---- begin ----
2416 /* pn_mo_global_cache.populate; */
2417
2418 ---- end ----
2419
2420
2421
2422 /* Remove for multi-org support */
2423
2424 ---- begin ----
2425
2426 l_org_id := pn_mo_cache_utils.get_current_org_id;
2427
2428 OPEN csr_currency_code(l_org_id);
2429 FETCH csr_currency_code into g_currency_code,g_set_of_books_id;
2430 CLOSE csr_currency_code;
2431
2432 fnd_currency.get_info(g_currency_code, g_precision,l_ext_precision, l_min_acct_unit);
2433
2434 pnp_debug_pkg.log('process_rent_batch - currency_code :'||g_currency_code);
2435 pnp_debug_pkg.log('process_rent_batch - set_of_books_id :'||g_set_of_books_id);
2436 pnp_debug_pkg.log('process_rent_batch - precision :'||g_precision);
2437
2438 ---- end ----
2439
2440 IF p_location_code_from IS NOT NULL or p_location_code_to IS NOT NULL THEN
2441 -----------------------------------------------------------------------------
2442 -- Checking Location Code From, Location Code To to open appropriate cursor.
2443 -----------------------------------------------------------------------------
2444 OPEN csr_get_vrent_wloc;
2445 ELSE
2446 OPEN csr_get_vrent_woloc;
2447 END IF;
2448
2449 LOOP
2450
2451 IF csr_get_vrent_wloc%ISOPEN THEN
2452 FETCH csr_get_vrent_wloc INTO l_var_rent_id,l_invoice_on,l_cumulative,l_rent_num,g_org_id;
2453 EXIT WHEN csr_get_vrent_wloc%NOTFOUND;
2454 ELSIF csr_get_vrent_woloc%ISOPEN THEN
2455 FETCH csr_get_vrent_woloc INTO l_var_rent_id,l_invoice_on,l_cumulative,l_rent_num,g_org_id;
2456 EXIT WHEN csr_get_vrent_woloc%NOTFOUND;
2457 END IF;
2458
2459 fnd_message.set_name ('PN','PN_VRAM_VRN_PROC');
2460 fnd_message.set_token ('NUM',l_rent_num);
2461 pnp_debug_pkg.put_log_msg(fnd_message.get);
2462
2463 pnp_debug_pkg.log ('process_rent_batch - Variable Rent id :'||l_var_rent_id);
2464 pnp_debug_pkg.log ('process_rent_batch - org_id :'||g_org_id);
2465
2466
2467 /* get the currency code, set of books id AND initialize global variable */
2468
2469 /* Uncomment for multi-org support */
2470
2471 ---- begin ----
2472
2473 /*IF mo_utils.get_multi_org_flag = 'Y' THEN
2474 l_global_rec := pn_mo_global_cache.get_org_attributes(g_org_id);
2475 ELSE
2476 l_global_rec := pn_mo_global_cache.get_org_attributes(-3115);
2477 END IF;
2478
2479
2480 g_currency_code := l_global_rec.functional_currency_code;
2481 g_set_of_books_id := l_global_rec.set_of_books_id;
2482
2483 fnd_currency.get_info(g_currency_code, g_precision,l_ext_precision, l_min_acct_unit);
2484
2485 put_log('process_rent_batch - currency_code :'||g_currency_code);
2486 put_log('process_rent_batch - set_of_books_id :'||g_set_of_books_id); */
2487
2488 ---- end ----
2489
2490
2491 FOR rec_get_per in csr_get_per(l_var_rent_id)
2492 LOOP
2493
2494 fnd_message.set_name ('PN','PN_VRAM_PRD_PROC');
2495 fnd_message.set_token ('NUM',rec_get_per.period_num);
2496 pnp_debug_pkg.put_log_msg(fnd_message.get);
2497
2498 pnp_debug_pkg.log ('process_rent_batch - period st date : '||rec_get_per.start_date);
2499 pnp_debug_pkg.log ('process_rent_batch - period end date: '||rec_get_per.end_date);
2500
2501 /* get the group date ids AND proration factor for the group dates for the period */
2502
2503 get_percent_open(p_period_id => rec_get_per.period_id,
2504 p_cumulative => l_cumulative,
2505 p_start_date => rec_get_per.start_date,
2506 p_end_date => rec_get_per.end_date);
2507
2508
2509 IF p_calc_TYPE in('CALCULATE','ADJUST') THEN
2510
2511 process_variable_rent (
2512 p_var_rent_id => l_var_rent_id,
2513 p_period_id => rec_get_per.period_id,
2514 p_line_item_id => p_line_item_id,
2515 p_cumulative => l_cumulative,
2516 p_invoice_on => l_invoice_on,
2517 p_calc_TYPE => p_calc_TYPE);
2518
2519
2520 ELSIF p_calc_TYPE = 'RECONCILE' THEN
2521 FOR rec_get_invdt in csr_get_invdt(rec_get_per.period_id)
2522 LOOP
2523 process_variable_rent (
2524 p_var_rent_id => l_var_rent_id,
2525 p_period_id => rec_get_per.period_id,
2526 p_line_item_id => null,
2527 p_cumulative => l_cumulative,
2528 p_invoice_on => l_invoice_on,
2529 p_calc_TYPE => p_calc_TYPE,
2530 p_invoice_date => rec_get_invdt.invoice_date);
2531 END LOOP;
2532 END IF;
2533
2534 END LOOP;
2535
2536 /* Recalculate abatements for all non transferred invoices belonging to the variable rent*/
2537
2538 apply_abatements(p_var_rent_id => l_var_rent_id);
2539
2540
2541 END LOOP;
2542
2543 IF csr_get_vrent_wloc%ISOPEN THEN
2544 CLOSE csr_get_vrent_wloc;
2545 ELSIF csr_get_vrent_woloc%ISOPEN THEN
2546 CLOSE csr_get_vrent_woloc;
2547 END IF;
2548
2549 EXCEPTION
2550
2551 When OTHERS Then
2552 pnp_debug_pkg.log('Error in pn_variable_amount_pkg.process_rent_batch :'||TO_CHAR(sqlcode)||' : '||sqlerrm);
2553 Errbuf := SQLERRM;
2554 Retcode := 2;
2555 rollback;
2556 raise;
2557
2558 pnp_debug_pkg.log('pn_variable_amount_pkg.process_rent_batch (-) ');
2559 END process_rent_batch;
2560
2561
2562 ----------------------------------------------------------------------------
2563 -- PROCEDURE : process_vol_hist
2564 --
2565 -- Program to process rent when volume history is deleted. Checks to see
2566 -- if any volume history exists for an invoice date AND period. If none
2567 -- exists then the corresponding row in pn_var_rent_inv should be deleted.
2568 -- Also if no volume history exists for a group date AND line item then
2569 -- the corresponding record in pn_var_rent_SUMm should be deleted.
2570 --
2571 ----------------------------------------------------------------------------
2572 PROCEDURE process_vol_hist (
2573 p_grp_date_id IN NUMBER,
2574 p_invoice_date IN DATE,
2575 p_period_id IN NUMBER,
2576 p_line_item_id IN NUMBER,
2577 p_invoice_on IN VARCHAR2,
2578 p_calc_TYPE OUT NOCOPY VARCHAR2) IS
2579
2580 /* Cursor to chk if volume history exists for a line_item_id AND grp_date_id */
2581 CURSOR csr_any_vol_exists (ip_line_item_id NUMBER,
2582 ip_grp_date_id NUMBER) IS
2583 SELECT 'Y'
2584 FROM dual
2585 WHERE exists (SELECT null
2586 FROM pn_var_vol_hist_all
2587 WHERE line_item_id = ip_line_item_id
2588 AND grp_date_id = ip_grp_date_id);
2589
2590 /* Cursor to chk if volume history exists for a period AND invoice date */
2591 CURSOR csr_vol_exists(ip_period_id NUMBER,
2592 ip_invoice_date DATE,
2593 ip_rent_TYPE VARCHAR2) IS
2594 SELECT 'Y'
2595 FROM dual
2596 WHERE exists (SELECT null
2597 FROM pn_var_vol_hist_all vh,
2598 pn_var_grp_dates_all gd
2599 WHERE vh.period_id = gd.period_id
2600 AND vh.grp_date_id = gd.grp_date_id
2601 AND gd.period_id = ip_period_id
2602 AND gd.invoice_date = ip_invoice_date
2603 AND vh.variance_exp_code = decode(ip_rent_TYPE,'VARIANCE','N',vh.variance_exp_code)
2604 AND vh.forecasted_exp_code = decode(ip_rent_TYPE,'FORECASTED','N',vh.forecasted_exp_code)
2605 AND vh.actual_exp_code = decode(ip_rent_TYPE,'ACTUAL','N',vh.actual_exp_code)
2606 AND ((ip_rent_TYPE = 'VARIANCE' AND vh.actual_amount is not null) OR
2607 ip_rent_TYPE in('FORECASTED','ACTUAL'))
2608 );
2609
2610 /* Cursor to get the last non transferred invoice belonging to a period_id AND invoice_date */
2611 CURSOR csr_get_inv(ip_period_id NUMBER,
2612 ip_invoice_date DATE) IS
2613 SELECT inv.adjust_num,
2614 inv.forecasted_exp_code,
2615 inv.variance_exp_code,
2616 inv.rowid,
2617 inv.var_rent_inv_id
2618 FROM pn_var_rent_inv_all inv
2619 WHERE inv.period_id = ip_period_id
2620 AND inv.invoice_date = ip_invoice_date
2621 AND inv.adjust_num =(Select MAX(inv1.adjust_num)
2622 from pn_var_rent_inv_all inv1
2623 where inv1.period_id = ip_period_id
2624 AND inv1.invoice_date = ip_invoice_date);
2625
2626 l_any_vol_exists VARCHAR2(1) := 'N';
2627 l_vol_exists VARCHAR2(1) := 'N';
2628 l_varrent_exists VARCHAR2(1) := 'N';
2629 l_rent_TYPE VARCHAR2(30):= null;
2630 l_adjust_num NUMBER := 0;
2631 l_rowid ROWID;
2632 l_delete BOOLEAN := FALSE;
2633 l_for_exp_code pn_var_rent_inv.forecasted_exp_code%TYPE := null;
2634 l_var_exp_code pn_var_rent_inv.variance_exp_code%TYPE := null;
2635 l_var_rent_inv_id pn_var_rent_inv.var_rent_inv_id%TYPE;
2636
2637 BEGIN
2638 pnp_debug_pkg.log('pn_variable_amount_pkg.process_vol_hist (+) ');
2639
2640 /* Does a row exist in pn_var_rent_SUMm for grp_date_id AND line_item_id */
2641 l_varrent_exists := find_varrent_exists(
2642 p_line_item_id => p_line_item_id,
2643 p_grp_date_id => p_grp_date_id);
2644
2645 /* Does a row exist in pn_var_vol_hist for grp_date_id AND line_item_id */
2646 OPEN csr_any_vol_exists (p_line_item_id,p_grp_date_id);
2647 FETCH csr_any_vol_exists into l_any_vol_exists;
2648 CLOSE csr_any_vol_exists;
2649
2650 /* Delete from pn_var_rent_SUMm */
2651 IF l_any_vol_exists = 'N' THEN
2652 DELETE from pn_var_rent_SUMm_all
2653 WHERE grp_date_id = p_grp_date_id
2654 AND line_item_id = p_line_item_id;
2655 END IF;
2656
2657 OPEN csr_get_inv(p_period_id,p_invoice_date);
2658 FETCH csr_get_inv into l_adjust_num,l_for_exp_code,l_var_exp_code,l_rowid,l_var_rent_inv_id;
2659 CLOSE csr_get_inv;
2660
2661 IF l_adjust_num = 0 AND p_invoice_on = 'FORECASTED' AND l_for_exp_code = 'Y' AND
2662 l_var_exp_code='N' THEN
2663 l_delete := FALSE;
2664 p_calc_TYPE := 'RECONCILE';
2665 ELSIF l_adjust_num = 0 AND p_invoice_on = 'FORECASTED' AND l_for_exp_code = 'N' THEN
2666 l_rent_TYPE := 'FORECASTED';
2667 l_delete := TRUE;
2668 p_calc_TYPE := 'CALCULATE';
2669 ELSIF l_adjust_num > 0 AND p_invoice_on = 'FORECASTED' THEN
2670 l_rent_TYPE := 'VARIANCE';
2671 l_delete := TRUE;
2672 p_calc_TYPE := 'ADJUST';
2673 ELSIF l_adjust_num = 0 AND p_invoice_on = 'ACTUAL' THEN
2674 l_rent_TYPE := 'ACTUAL';
2675 l_delete := TRUE;
2676 p_calc_TYPE := 'CALCULATE';
2677 ELSIF l_adjust_num > 0 AND p_invoice_on = 'ACTUAL' THEN
2678 l_rent_TYPE := 'ACTUAL';
2679 l_delete := TRUE;
2680 p_calc_TYPE := 'ADJUST';
2681 END IF;
2682
2683 IF NVL(l_varrent_exists,'N') = 'N' THEN
2684 p_calc_TYPE := null;
2685 END IF;
2686
2687 IF l_delete THEN
2688 /* Does any volume history exist for the invoice date,period AND rent TYPE */
2689 OPEN csr_vol_exists(p_period_id,p_invoice_date,l_rent_TYPE);
2690 FETCH csr_vol_exists into l_vol_exists;
2691 CLOSE csr_vol_exists;
2692
2693 IF l_vol_exists = 'N' THEN
2694
2695 /* Delete from pn_payment_terms if a payment term exists */
2696 DELETE from pn_payment_terms_all
2697 WHERE var_rent_inv_id = l_var_rent_inv_id
2698 AND var_rent_TYPE = l_rent_TYPE
2699 AND status <> c_payment_term_status_approved;
2700
2701 /* Delete from pn_var_rent_inv */
2702 DELETE from pn_var_rent_inv_all
2703 WHERE rowid = l_rowid;
2704 END IF;
2705 END IF;
2706
2707 EXCEPTION
2708
2709 When OTHERS Then
2710 pnp_debug_pkg.log('Error in pn_variable_amount_pkg.process_vol_hist : '||TO_CHAR(sqlcode)||' : '||sqlerrm);
2711 rollback;
2712 raise;
2713
2714 pnp_debug_pkg.log('pn_variable_amount_pkg.process_vol_hist (-) ');
2715
2716 END process_vol_hist;
2717
2718 ----------------------------------------------------------------------------
2719 -- FUNCTION : get_msg
2720 --
2721 ----------------------------------------------------------------------------
2722
2723 FUNCTION get_msg (p_calc IN VARCHAR2,
2724 p_adj IN VARCHAR2,
2725 p_rec IN VARCHAR2)
2726 RETURN varchar2 IS
2727
2728 l_msg varchar2(200) := null;
2729
2730 BEGIN
2731 pnp_debug_pkg.log('pn_variable_amount_pkg.get_msg (+) ');
2732
2733 IF p_calc is not null THEN
2734 IF p_adj is not null AND p_rec is not null THEN
2735 l_msg := p_calc||', '||p_adj||' AND '||p_rec||' processes';
2736 ELSIF p_adj is not null AND p_rec is null THEN
2737 l_msg := p_calc||' AND '||p_adj||' processes';
2738 ELSIF p_adj is null AND p_rec is not null THEN
2739 l_msg := p_calc||' AND '||p_rec||' processes';
2740 ELSE
2741 l_msg := p_calc||' process';
2742 END IF;
2743 ELSE
2744 IF p_adj is not null AND p_rec is not null THEN
2745 l_msg := p_adj||' AND '||p_rec||' processes';
2746 ELSIF p_adj is not null AND p_rec is null THEN
2747 l_msg :=p_adj||' process';
2748 ELSIF p_adj is null AND p_rec is not null THEN
2749 l_msg :=p_rec||' process';
2750 ELSE
2751 l_msg := null;
2752 END IF;
2753 END IF;
2754
2755 RETURN l_msg;
2756
2757 pnp_debug_pkg.log('pn_variable_amount_pkg.get_msg (-) ');
2758 END get_msg;
2759
2760 ----------------------------------------------------------------------------
2761 -- FUNCTION : get_prorated_bkpt
2762 --
2763 -- Description :
2764 --
2765 -- Function used by form views PN_ACT_RENT_DETAILS_V AND PN_FOR_RENT_DETAILS_V
2766 -- to return prorated breakpoints based up on whether invoicing in on cumulative
2767 -- volumes or non cumulative volume.
2768
2769 -- 25-Feb-2002 Pooja Sidhu o Created.
2770 ----------------------------------------------------------------------------
2771 FUNCTION get_prorated_bkpt(p_cumulative IN VARCHAR2,
2772 p_grp_st_dt IN DATE,
2773 p_grp_end_dt IN DATE,
2774 p_per_st_dt IN DATE,
2775 p_per_end_dt IN DATE,
2776 p_per_bkpt IN NUMBER,
2777 p_grp_bkpt IN NUMBER,
2778 p_pror_factor IN NUMBER)
2779 RETURN NUMBER IS
2780 l_pror_bkpt NUMBER := null;
2781 l_no_of_days NUMBER := null;
2782 BEGIN
2783 pnp_debug_pkg.log('pn_variable_amount_pkg.get_prorated_bkpt (+) :');
2784 IF NVL(p_cumulative,'N') = 'Y' THEN
2785 l_no_of_days := (p_per_end_dt - p_per_st_dt) + 1;
2786 l_pror_bkpt := (l_no_of_days/p_pror_factor) * p_per_bkpt;
2787 ELSE
2788 l_no_of_days := (p_grp_end_dt - p_grp_st_dt) + 1;
2789 l_pror_bkpt := (l_no_of_days/p_pror_factor) * p_grp_bkpt;
2790 END IF;
2791 pnp_debug_pkg.log('pn_variable_amount_pkg.get_prorated_bkpt (-) :');
2792 RETURN l_pror_bkpt;
2793
2794 END get_prorated_bkpt;
2795
2796 ----------------------------------------------------------------------------
2797 -- FUNCTION : derive_actual_invoiced_amt
2798 --
2799 ----------------------------------------------------------------------------
2800 FUNCTION derive_actual_invoiced_amt(p_constr_actual_rent number,
2801 p_negative_rent_flag varchar2,
2802 p_abatement_appl number,
2803 p_negative_rent number,
2804 p_rec_abatement number,
2805 p_rec_abatement_override number)
2806 RETURN NUMBER IS
2807 l_constr_actual_rent pn_var_rent_inv.constr_actual_rent%TYPE := 0 ;
2808 l_actual_invoiced_amount pn_var_rent_inv.actual_invoiced_amount%TYPE := 0;
2809 l_rec_abatement pn_var_rent_inv.rec_abatement%TYPE := 0;
2810
2811 BEGIN
2812 pnp_debug_pkg.log('pn_variable_amount_pkg.derive_actual_invoiced_amt (+) :');
2813
2814 l_constr_actual_rent := p_constr_actual_rent;
2815
2816 IF (p_constr_actual_rent < 0 AND p_negative_rent_flag in('DEFER','IGNORE')) THEN
2817 l_constr_actual_rent := 0;
2818 END IF;
2819
2820 pnp_debug_pkg.log('pn_variable_amount_pkg - l_constr_actual_rent :'||l_constr_actual_rent);
2821 pnp_debug_pkg.log('pn_variable_amount_pkg - p_abatement_appl :'||p_abatement_appl);
2822 pnp_debug_pkg.log('pn_variable_amount_pkg - p_negative_rent :'||p_negative_rent);
2823
2824 l_actual_invoiced_amount := l_constr_actual_rent - (p_abatement_appl + p_negative_rent);
2825
2826 pnp_debug_pkg.log('pn_variable_amount_pkg - l_actual_invoiced_amount '||l_actual_invoiced_amount);
2827
2828 IF l_actual_invoiced_amount > 0 THEN
2829 l_rec_abatement := NVL(p_rec_abatement_override,
2830 LEAST(l_actual_invoiced_amount,p_rec_abatement));
2831 ELSE
2832 l_rec_abatement := p_rec_abatement_override;
2833 END IF;
2834
2835 pnp_debug_pkg.log('pn_variable_amount_pkg - l_rec_abatement '||l_rec_abatement);
2836
2837 l_actual_invoiced_amount := l_actual_invoiced_amount - NVL(l_rec_abatement,0) ;
2838
2839 RETURN l_actual_invoiced_amount;
2840
2841 pnp_debug_pkg.log('pn_variable_amount_pkg.derive_actual_invoiced_amt (-) :');
2842
2843 END derive_actual_invoiced_amt;
2844
2845 ----------------------------------------------------------------------------
2846 -- PROCEDURE : put_log
2847 --
2848 ----------------------------------------------------------------------------
2849 PROCEDURE put_log(p_string VARCHAR2)
2850 IS
2851 BEGIN
2852 pnp_debug_pkg.log(p_string);
2853 END put_log;
2854
2855 ----------------------------------------------------------------------------
2856 -- PROCEDURE : put_output
2857 --
2858 ----------------------------------------------------------------------------
2859 PROCEDURE put_output(p_string VARCHAR2)
2860 IS
2861 BEGIN
2862 pnp_debug_pkg.put_log_msg(p_string);
2863 END put_output;
2864
2865 ----------------------------------------------------------------------------
2866 -- PROCEDURE : get_approved_flag
2867 --
2868 ----------------------------------------------------------------------------
2869
2870 PROCEDURE get_approved_flag(p_period_id IN NUMBER,
2871 p_invoice_date IN DATE,
2872 p_true_up_flag IN VARCHAR2,
2873 p_actual_flag OUT NOCOPY VARCHAR2,
2874 p_forecasted_flag OUT NOCOPY VARCHAR2,
2875 p_variance_flag OUT NOCOPY VARCHAR2)
2876 IS
2877 CURSOR get_aprvd_cur(ip_period_id NUMBER,ip_invoice_date DATE) IS
2878 SELECT actual_exp_code,forecasted_exp_code,variance_exp_code
2879 FROM pn_var_rent_inv_all
2880 WHERE period_id = ip_period_id
2881 AND invoice_date = ip_invoice_date
2882 AND true_up_amt IS NULL
2883 AND adjust_num = (select max(adjust_num) FROM pn_var_rent_inv_all
2884 WHERE period_id = ip_period_id
2885 AND invoice_date = ip_invoice_date
2886 AND true_up_amt IS NULL -- Bug # 5991106
2887 );
2888 CURSOR get_aprvd_cur_tu(ip_period_id NUMBER,ip_invoice_date DATE) IS
2889 SELECT actual_exp_code,forecasted_exp_code,variance_exp_code
2890 FROM pn_var_rent_inv_all
2891 WHERE period_id = ip_period_id
2892 AND invoice_date = ip_invoice_date
2893 AND true_up_amt IS NOT NULL
2894 AND adjust_num = (select max(adjust_num) FROM pn_var_rent_inv_all
2895 WHERE period_id = ip_period_id
2896 AND invoice_date = ip_invoice_date
2897 AND true_up_amt IS NOT NULL -- Bug # 5991106
2898 );
2899
2900 BEGIN
2901 pnp_debug_pkg.log('pn_variable_amount_pkg.get_approved_flag (+) ');
2902 IF p_true_up_flag = 'N' THEN
2903 FOR rec IN get_aprvd_cur(p_period_id, p_invoice_date )LOOP
2904 p_actual_flag := rec.actual_exp_code;
2905 p_forecasted_flag := rec.forecasted_exp_code;
2906 p_variance_flag := rec.variance_exp_code;
2907 END LOOP;
2908 ELSE
2909 FOR rec IN get_aprvd_cur_tu(p_period_id, p_invoice_date )LOOP
2910 p_actual_flag := rec.actual_exp_code;
2911 p_forecasted_flag := rec.forecasted_exp_code;
2912 p_variance_flag := rec.variance_exp_code;
2913 END LOOP;
2914 END IF;
2915
2916
2917
2918 EXCEPTION
2919 WHEN others then pnp_debug_pkg.log ('Error in pn_variable_amount_pkg.get_approved_flag :'||
2920 TO_CHAR(sqlcode)||': '||sqlerrm);
2921
2922 pnp_debug_pkg.log('pn_variable_amount_pkg.get_approved_flag (-) ');
2923
2924 END get_approved_flag;
2925
2926
2927 END pn_variable_amount_pkg;
2928
[Home] [Help]