1 PACKAGE BODY pn_var_abatement_amount_pkg AS
2 -- $Header: PNVRCABB.pls 120.8 2007/05/31 11:40:21 sraaj noship $
3
4 -------------------------------------------------------------------------------
5 -- FUNCTION : calc_abatement
6 --
7 -- Description : Function to calculate recurring abatement amount for
8 -- an invoice DATE and period_id.
9 --
10 -- 08-Mar-2002 Pooja Sidhu o Created.
11 -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_rent_inv,pn_var_rents with _ALL table.
12 -- 30-jan-2006 Shabda o Bug 5729157 - Instead of checking for inv_id passed,
13 -- check for invoice_id for given date and period
14 ------------------------------------------------------------------------------
15
16 FUNCTION calc_abatement(p_var_rent_inv_id IN NUMBER,
17 p_min_grp_dt IN DATE,
18 p_max_grp_dt IN DATE)
19 RETURN NUMBER IS
20 CURSOR csr_get_amt IS
21 SELECT NVL(SUM(ppi.actual_amount),0)
22 FROM pn_payment_items_all ppi,
23 pn_var_rent_inv_all inv, --BUG#2452909 /* hrodda_MOAC -changed to tablename_all*/
24 pn_var_rents_all var , --BUG#2452909 /* hrodda_MOAC -changed to tablename_all*/
25 pn_payment_schedules_all pps,
26 pn_var_abatements_all abt
27 WHERE ppi.payment_term_id = abt.payment_term_id
28 AND abt.var_rent_inv_id = (SELECT inv2.var_rent_inv_id FROM pn_var_rent_inv_all inv1, pn_var_rent_inv_all inv2
29 WHERE inv1.var_rent_id = inv2.var_rent_id
30 AND inv1.period_id = inv2.period_id
31 AND inv1.invoice_date = inv2.invoice_date
32 AND inv1.var_rent_inv_id = p_var_rent_inv_id
33 AND inv2.true_up_amt IS NULL
34 AND inv2.adjust_num =0)
35 AND abt.include_term = pn_var_abatement_amount_pkg.G_INCLUDE_TERM_YES
36 AND inv.var_rent_inv_id = p_var_rent_inv_id --BUG#2452909,
37 AND var.var_rent_id = inv.var_rent_id --BUG#2452909
38 AND ppi.currency_code = var.currency_code --BUG#2452909
39 AND ppi.payment_schedule_id = pps.payment_schedule_id
40 AND ppi.payment_item_type_lookup_code = 'CASH'
41 AND TRUNC(pps.schedule_date,'MM') BETWEEN
42 TRUNC(p_min_grp_dt,'MM') AND TRUNC(p_max_grp_dt,'MM');
43
44 CURSOR csr_get_tu_amt IS
45 SELECT NVL(SUM(ppi.actual_amount),0)
46 FROM pn_payment_items_all ppi,
47 pn_var_rent_inv_all inv, --BUG#2452909 /* hrodda_MOAC -changed to tablename_all*/
48 pn_var_rents_all var , --BUG#2452909 /* hrodda_MOAC -changed to tablename_all*/
49 pn_payment_schedules_all pps,
50 pn_var_abatements_all abt
51 WHERE ppi.payment_term_id = abt.payment_term_id
52 AND abt.var_rent_inv_id = (SELECT inv2.var_rent_inv_id FROM pn_var_rent_inv_all inv1, pn_var_rent_inv_all inv2
53 WHERE inv1.var_rent_id = inv2.var_rent_id
54 AND inv1.period_id = inv2.period_id
55 AND inv1.invoice_date = inv2.invoice_date
56 AND inv1.var_rent_inv_id = p_var_rent_inv_id
57 AND inv2.true_up_amt IS NOT NULL
58 AND inv2.adjust_num =0)
59 AND abt.include_term = pn_var_abatement_amount_pkg.G_INCLUDE_TERM_YES
60 AND inv.var_rent_inv_id = p_var_rent_inv_id --BUG#2452909,
61 AND var.var_rent_id = inv.var_rent_id --BUG#2452909
62 AND ppi.currency_code = var.currency_code --BUG#2452909
63 AND ppi.payment_schedule_id = pps.payment_schedule_id
64 AND ppi.payment_item_type_lookup_code = 'CASH'
65 AND TRUNC(pps.schedule_date,'MM') BETWEEN
66 TRUNC(p_min_grp_dt,'MM') AND TRUNC(p_max_grp_dt,'MM');
67
68 -- Get the details of
69 CURSOR is_inv_tu_c(ip_var_rent_inv_id NUMBER
70 ) IS
71 SELECT true_up_amt
72 FROM pn_var_rent_inv_all inv
73 WHERE inv.var_rent_inv_id = ip_var_rent_inv_id;
74
75 l_abt_amt NUMBER;
76 l_true_up_amt NUMBER;
77
78 BEGIN
79 pnp_debug_pkg.log('pn_var_abatement_amount_pkg.calc_abatement (+) :');
80 --
81 FOR tu_rec IN is_inv_tu_c(p_var_rent_inv_id) LOOP
82 l_true_up_amt := tu_rec.true_up_amt;
83 pnp_debug_pkg.log('l_true_up_amt'||l_true_up_amt);
84 END LOOP;
85
86 IF l_true_up_amt IS NULL THEN
87 -- This is non true up amt
88 pnp_debug_pkg.log('Non true up inv');
89 OPEN csr_get_amt;
90 FETCH csr_get_amt INTO l_abt_amt;
91 CLOSE csr_get_amt;
92 ELSE
93 -- This is true up amt
94 pnp_debug_pkg.log('true up inv');
95 OPEN csr_get_tu_amt;
96 FETCH csr_get_tu_amt INTO l_abt_amt;
97 CLOSE csr_get_tu_amt;
98 END IF;
99 pnp_debug_pkg.log('Amount:'||l_abt_amt);
100 pnp_debug_pkg.log('pn_var_abatement_amount_pkg.calc_abatement (-) :');
101 RETURN l_abt_amt;
102 EXCEPTION
103 WHEN no_data_found THEN
104 return 0;
105 WHEN others THEN
106 pnp_debug_pkg.log('Error IN pn_var_abatement_amount_pkg.calc_abatement -'||TO_CHAR(sqlcode)||' - '||sqlerrm);
107 RAISE;
108
109 END calc_abatement;
110
111 -------------------------------------------------------------------------------
112 -- FUNCTION : calc_abatement
113 --
114 -- Description : Function to calculate recurring abatement amount for
115 -- an invoice DATE and period_id.
116 --
117 -- Shabda 29-5-07 o Created. _ bug 6041521.
118 ------------------------------------------------------------------------------
119
120 FUNCTION calc_abatement(p_var_rent_id IN NUMBER,
121 p_period_id IN NUMBER,
122 p_var_rent_inv_id IN NUMBER,
123 p_min_grp_dt IN DATE,
124 p_max_grp_dt IN DATE,
125 p_trp_flag IN VARCHAR2)
126 RETURN NUMBER IS
127 CURSOR csr_get_amt(ip_var_rent_inv_id NUMBER,
128 ip_min_grp_dt DATE,
129 ip_max_grp_dt DATE) IS
130 SELECT NVL(SUM(ppi.actual_amount),0) amount
131 FROM pn_payment_items_all ppi,
132 pn_var_rent_inv_all inv, --BUG#2452909 /* hrodda_MOAC -changed to tablename_all*/
133 pn_var_rents_all var , --BUG#2452909 /* hrodda_MOAC -changed to tablename_all*/
134 pn_payment_schedules_all pps,
135 pn_var_abatements_all abt
136 WHERE ppi.payment_term_id = abt.payment_term_id
137 AND abt.var_rent_inv_id = (SELECT inv2.var_rent_inv_id FROM pn_var_rent_inv_all inv1, pn_var_rent_inv_all inv2
138 WHERE inv1.var_rent_id = inv2.var_rent_id
139 AND inv1.period_id = inv2.period_id
140 AND inv1.invoice_date = inv2.invoice_date
141 AND inv1.var_rent_inv_id = ip_var_rent_inv_id
142 AND inv2.true_up_amt IS NULL
143 AND inv2.adjust_num =0)
144 AND abt.include_term = pn_var_abatement_amount_pkg.G_INCLUDE_TERM_YES
145 AND inv.var_rent_inv_id = ip_var_rent_inv_id --BUG#2452909,
146 AND var.var_rent_id = inv.var_rent_id --BUG#2452909
147 AND ppi.currency_code = var.currency_code --BUG#2452909
148 AND ppi.payment_schedule_id = pps.payment_schedule_id
149 AND ppi.payment_item_type_lookup_code = 'CASH'
150 AND TRUNC(pps.schedule_date,'MM') BETWEEN
151 TRUNC(ip_min_grp_dt,'MM') AND TRUNC(ip_max_grp_dt,'MM');
152
153 CURSOR csr_get_tu_amt IS
154 SELECT NVL(SUM(ppi.actual_amount),0)
155 FROM pn_payment_items_all ppi,
156 pn_var_rent_inv_all inv, --BUG#2452909 /* hrodda_MOAC -changed to tablename_all*/
157 pn_var_rents_all var , --BUG#2452909 /* hrodda_MOAC -changed to tablename_all*/
158 pn_payment_schedules_all pps,
159 pn_var_abatements_all abt
160 WHERE ppi.payment_term_id = abt.payment_term_id
161 AND abt.var_rent_inv_id = (SELECT inv2.var_rent_inv_id FROM pn_var_rent_inv_all inv1, pn_var_rent_inv_all inv2
162 WHERE inv1.var_rent_id = inv2.var_rent_id
163 AND inv1.period_id = inv2.period_id
164 AND inv1.invoice_date = inv2.invoice_date
165 AND inv1.var_rent_inv_id = p_var_rent_inv_id
166 AND inv2.true_up_amt IS NOT NULL
167 AND inv2.adjust_num =0)
168 AND abt.include_term = pn_var_abatement_amount_pkg.G_INCLUDE_TERM_YES
169 AND inv.var_rent_inv_id = p_var_rent_inv_id --BUG#2452909,
170 AND var.var_rent_id = inv.var_rent_id --BUG#2452909
171 AND ppi.currency_code = var.currency_code --BUG#2452909
172 AND ppi.payment_schedule_id = pps.payment_schedule_id
173 AND ppi.payment_item_type_lookup_code = 'CASH'
174 AND TRUNC(pps.schedule_date,'MM') BETWEEN
175 TRUNC(p_min_grp_dt,'MM') AND TRUNC(p_max_grp_dt,'MM');
176
177 -- Get the details of
178 CURSOR is_inv_tu_c(ip_var_rent_inv_id NUMBER
179 ) IS
180 SELECT true_up_amt
181 FROM pn_var_rent_inv_all inv
182 WHERE inv.var_rent_inv_id = ip_var_rent_inv_id;
183
184 -- Get the details of invoices in a specific period
185 CURSOR get_inv_in_prd(ip_period_id NUMBER
186 ) IS
187 SELECT var_rent_inv_id
188 FROM pn_var_rent_inv_all
189 WHERE period_id = ip_period_id;
190
191 -- Get the details of invoice date
192 CURSOR get_inv_date(ip_inv_id NUMBER
193 ) IS
194 SELECT invoice_date
195 FROM pn_var_rent_inv_all
196 WHERE var_rent_inv_id = ip_inv_id
197 AND true_up_amt IS NULL
198 AND adjust_num = 0;
199 -- Get the proration type
200 CURSOR proration_type_c(ip_var_rent_id NUMBER
201 ) IS
202 SELECT proration_rule
203 FROM pn_var_rents_all
204 WHERE var_rent_id = ip_var_rent_id;
205 -- Get the first period
206 CURSOR get_fy_prd_c(ip_var_rent_id NUMBER
207 ) IS
208 SELECT period_id
209 FROM pn_var_periods_all
210 WHERE start_date = (SELECT min(start_date) from pn_var_periods_all WHERE var_rent_id = ip_var_rent_id)
211 AND var_rent_id = ip_var_rent_id;
212
213 CURSOR invoice_dates_fy_c(ip_period_id NUMBER
214 ) IS
215 SELECT per.start_date, per.end_date
216 FROM pn_var_periods_all per
217 WHERE period_id = ip_period_id;
218
219
220 l_abt_amt NUMBER;
221 l_true_up_amt NUMBER;
222 l_inv_date DATE;
223 l_min_grp_date DATE;
224 l_max_grp_date DATE;
225 l_proration_type VARCHAR2(30);
226 l_min_prd_id NUMBER;
227
228 BEGIN
229 l_true_up_amt := 0;
230 pnp_debug_pkg.log('pn_var_abatement_amount_pkg.calc_abatement (+) :');
231 --
232 FOR rec IN proration_type_c(p_var_rent_id) LOOP
233 l_proration_type := rec.proration_rule;
234 END LOOP;
235
236 --
237 FOR rec IN get_fy_prd_c(p_var_rent_id) LOOP
238 l_min_prd_id := rec.period_id;
239 END LOOP;
240
241
242 --
243 FOR tu_rec IN is_inv_tu_c(p_var_rent_inv_id) LOOP
244 l_true_up_amt := tu_rec.true_up_amt;
245 pnp_debug_pkg.log('l_true_up_amt'||l_true_up_amt);
246 END LOOP;
247
248 IF l_true_up_amt IS NULL THEN
249 -- This is non true up amt
250 pnp_debug_pkg.log('Non true up inv');
251 OPEN csr_get_amt(p_var_rent_inv_id, p_min_grp_dt, p_max_grp_dt);
252 FETCH csr_get_amt INTO l_abt_amt;
253 CLOSE csr_get_amt;
254 ELSE
255 -- This is true up amt
256 pnp_debug_pkg.log('true up inv');
257 --
258 l_abt_amt := 0;
259 FOR inv_in_prd_rec IN get_inv_in_prd(p_period_id) LOOP
260 --
261 pnp_debug_pkg.log('inv_in_prd_rec.var_rent_inv_id:'||inv_in_prd_rec.var_rent_inv_id);
262 FOR inv_date_rec IN get_inv_date(inv_in_prd_rec.var_rent_inv_id) LOOP
263 l_inv_date := inv_date_rec.invoice_date;
264 END LOOP;
265 pnp_debug_pkg.log('l_inv_date:'||l_inv_date);
266 l_min_grp_date := get_group_dt(l_inv_date, p_period_id, 'MIN');
267 l_max_grp_date := get_group_dt(l_inv_date, p_period_id, 'MAX');
268 IF (l_proration_type IN ('FY', 'FLY') AND p_period_id = l_min_prd_id) THEN
269 --
270 FOR rec IN invoice_dates_fy_c(p_period_id) LOOP
271 l_min_grp_date := rec.start_date;
272 l_max_grp_date := rec.end_date;
273 END LOOP;
274
275
276 END IF;
277
278 pnp_debug_pkg.log('l_min_grp_date:'||l_min_grp_date);
279 pnp_debug_pkg.log('l_max_grp_date:'||l_max_grp_date);
280 --
281 FOR amt_rec IN csr_get_amt(inv_in_prd_rec.var_rent_inv_id, l_min_grp_date, l_max_grp_date) LOOP
282 l_abt_amt := l_abt_amt + amt_rec.amount;
283 END LOOP;
284 pnp_debug_pkg.log('l_abt_amt:'||l_abt_amt);
285
286 END LOOP;
287
288 END IF;
289 pnp_debug_pkg.log('Amount:'||l_abt_amt);
290 pnp_debug_pkg.log('pn_var_abatement_amount_pkg.calc_abatement (-) :');
291 RETURN l_abt_amt;
292 EXCEPTION
293 WHEN no_data_found THEN
294 return 0;
295 WHEN others THEN
296 pnp_debug_pkg.log('Error IN pn_var_abatement_amount_pkg.calc_abatement -'||TO_CHAR(sqlcode)||' - '||sqlerrm);
297 RAISE;
298
299 END calc_abatement;
300
301
302
303 ----------------------------------------------------------------------------
304 -- PROCEDURE : process_abatement
305 --
306 -- Called FROM the View Variable Rent by Period window when
307 -- the user calculates abatement amount.
308 --
309 -- 08-Mar-2002 Pooja Sidhu o Created.
313 PROCEDURE process_abatement(p_var_rent_inv_id IN NUMBER,
310 -- 16-Jan-2003 Daniel Thota o Added parameter l_exported to check if an
311 -- invoice has been transferred. Fix for bug # 2722191
312 ---------------------------------------------------------------------------
314 p_negative_rent_flag IN VARCHAR2,
315 p_term_exists IN VARCHAR2,
316 p_var_rent_type IN VARCHAR2,
317 p_min_grp_dt IN DATE,
318 p_max_grp_dt IN DATE)
319 IS
320 l_abt_amt NUMBER := 0;
321 l_exported NUMBER := null;
322 BEGIN
323 pnp_debug_pkg.log('pn_var_abatement_amount_pkg.process_abatement (+) :');
324
325 /* Calculate the recurring abatement amount */
326
327 --Fix for bug # 2722191
328 l_exported:= PN_VAR_RENT_PKG.FIND_IF_EXPORTED(p_var_rent_inv_id,'PERIODS_INV_BLK');
329
330
331 /*If an actual/variance term exists for the invoice DATE then
332 DELETE the term FROM pn_payment_terms */
333
334 IF l_exported IS NULL THEN --Fix for bug # 2722191
335
336 l_abt_amt := NVL(calc_abatement(p_var_rent_inv_id,p_min_grp_dt,p_max_grp_dt),0);
337
338 IF NVL(p_term_exists,'N') = 'Y' THEN
339 DELETE FROM pn_payment_terms_all
340 WHERE var_rent_inv_id=p_var_rent_inv_id
341 and var_rent_type = p_var_rent_type
342 and NVL(status,'DRAFT')='DRAFT';
343 END IF;
344
345 /* Update pn_var_rent_inv with the recurring abatement amount and also
346 UPDATE the actual invoiced amount */
347
348 UPDATE pn_var_rent_inv_all
349 SET rec_abatement = l_abt_amt,
350 actual_term_status = DECODE(p_var_rent_type,'ACTUAL','N',actual_term_status),
351 variance_term_status = DECODE(p_var_rent_type,'VARIANCE','N',variance_term_status),
352 actual_invoiced_amount = pn_variable_amount_pkg.derive_actual_invoiced_amt(
353 constr_actual_rent,
354 p_negative_rent_flag,
355 abatement_appl,
356 negative_rent,
357 l_abt_amt,
358 rec_abatement_override)
359 WHERE var_rent_inv_id = p_var_rent_inv_id;
360
361 END IF;
362
363
364 pnp_debug_pkg.log('pn_var_abatement_amount_pkg.process_abatement (-) :');
365
366 EXCEPTION
367 WHEN OTHERS THEN
368 pnp_debug_pkg.log('Error IN pn_var_abatement_amount_pkg.process_abatement -'||TO_CHAR(sqlcode)||' - '||sqlerrm);
369 raise;
370
371 END process_abatement;
372
373 -----------------------------------------------------------------------------------------------------
374 -- FUNCTION : get_group_dt
375 --
376 -- Description : Function to get the minimum or maximum group_date
377 -- FROM pn_var_grp_dates table for an invoice DATE and period_id
378 --
379 -----------------------------------------------------------------------------------------------------
380
381 FUNCTION get_group_dt(
382 p_invoice_date DATE,
383 p_period_id NUMBER,
384 p_date_type IN VARCHAR2)
385 RETURN DATE IS
386 CURSOR csr_min_gd IS
387 SELECT MIN(grp_start_date)
388 FROM pn_var_grp_dates_all
389 WHERE invoice_date = p_invoice_date
390 AND period_id = p_period_id;
391
392 CURSOR csr_max_gd IS
393 SELECT MAX(grp_end_date)
394 FROM pn_var_grp_dates_all
395 WHERE invoice_date = p_invoice_date
396 AND period_id = p_period_id;
397
398 l_grp_date DATE;
399
400 BEGIN
401
402 pnp_debug_pkg.log('pn_var_abatement_amount_pkg.get_group_dt (-) :');
403
404 IF p_date_type ='MIN' THEN
405 OPEN csr_min_gd;
406 FETCH csr_min_gd INTO l_grp_date;
407 CLOSE csr_min_gd;
408 ELSIF p_date_type = 'MAX' THEN
409 OPEN csr_max_gd;
410 FETCH csr_max_gd into l_grp_date;
411 CLOSE csr_max_gd;
412 END IF;
413
414 RETURN l_grp_date;
415
416 pnp_debug_pkg.log('pn_var_abatement_amount_pkg.get_group_dt (-) :');
417
418 END get_group_dt;
419
420 ---------------------------------------------------------------------------------------------------------------
421 -- FUNCTION : get_term_exists
422 --
423 -- Description : Return Y' if a record
424 -- exists IN pn_var_abatements table for the
425 -- combination of payment_term_id and var_rent_inv_id.
426 --
427 -- 08-Mar-2002 Pooja sidhu o Created
428 -------------------------------------------------------------------------------------------------------------
429 FUNCTION get_term_exists (p_payment_term_id IN NUMBER,
430 p_var_rent_inv_id NUMBER)
431 RETURN VARCHAR2 IS
432 CURSOR csr_term_exists IS
433 SELECT 'Y'
434 FROM dual
435 WHERE EXISTS(SELECT null
436 FROM pn_var_abatements_all
437 WHERE payment_term_id = p_payment_term_id
438 AND var_rent_inv_id = p_var_rent_inv_id);
439
440 l_term_exists VARCHAR2(1) := 'N';
441
442 BEGIN
443 OPEN csr_term_exists;
444 FETCH csr_term_exists into l_term_exists;
445 if csr_term_exists%notfound then
446 l_term_exists := 'N';
447 end if;
448 CLOSE csr_term_exists;
449
450 return l_term_exists;
451
452 EXCEPTION
453 WHEN no_data_found THEN
454 RETURN 'N';
455
456 END get_term_exists;
457
458
459 END pn_var_abatement_amount_pkg;
460
461
[Home] [Help]