1 PACKAGE BODY PN_VAR_ABATEMENTS_PKG AS
2 /* $Header: PNVRABTB.pls 120.7 2007/07/02 15:21:29 lbala noship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : INSERT_row procedure
7 -- PURPOSE : INSERTs the row
8 -- HISTORY :
9 -- 14-JUL-05 HRodda o Bug 4284035 - REPLACEd PN_VAR_ABATEMENTS with _ALL
10 -- 28-NOV-05 pikhar o fetched org_id using cursor
11 -------------------------------------------------------------------------------
12 procedure INSERT_ROW (
13 X_ROWID IN out NOCOPY VARCHAR2,
14 X_VAR_ABATEMENT_ID IN out NOCOPY NUMBER,
15 X_VAR_RENT_ID IN NUMBER,
16 X_VAR_RENT_INV_ID IN NUMBER,
17 X_PAYMENT_TERM_ID IN NUMBER,
18 X_INCLUDE_TERM IN VARCHAR2,
19 X_INCLUDE_INCREASES IN VARCHAR2,
20 X_UPDATE_FLAG IN VARCHAR2,
21 X_CREATION_DATE IN DATE,
22 X_CREATED_BY IN NUMBER,
23 X_LAST_UPDATE_DATE IN DATE,
24 X_LAST_UPDATED_BY IN NUMBER,
25 X_LAST_UPDATE_LOGIN IN NUMBER,
26 X_ORG_ID IN NUMBER
27 ) IS
28
29 CURSOR var_abatements IS
30 SELECT ROWID
31 FROM PN_VAR_ABATEMENTS_ALL
32 WHERE VAR_ABATEMENT_ID = X_VAR_ABATEMENT_ID;
33
34 CURSOR org_cur IS
35 SELECT org_id
36 FROM pn_payment_terms_all
37 WHERE payment_term_id = x_payment_term_id;
38
39 l_org_id NUMBER;
40
41
42 BEGIN
43
44 PNP_DEBUG_PKG.debug ('PN_VAR_ABATEMENTS_PKG.INSERT_ROW (+)');
45
46 -------------------------------------------------------
47 -- SELECT the nextval fOR var abatement id
48 -------------------------------------------------------
49
50 IF x_org_id IS NULL THEN
51 FOR rec IN org_cur LOOP
52 l_org_id := rec.org_id;
53 END LOOP;
54 ELSE
55 l_org_id := x_org_id;
56 END IF;
57
58 IF ( X_VAR_ABATEMENT_ID IS NULL) THEN
59 SELECT pn_var_abatements_s.nextval
60 INTO X_VAR_ABATEMENT_ID
61 FROM dual;
62 END IF;
63
64 INSERT INTO PN_VAR_ABATEMENTS_ALL
65 ( VAR_RENT_ID,
66 VAR_ABATEMENT_ID,
67 VAR_RENT_INV_ID,
68 PAYMENT_TERM_ID,
69 INCLUDE_TERM,
70 INCLUDE_INCREASES,
71 UPDATE_FLAG,
72 LAST_UPDATE_DATE,
73 LAST_UPDATED_BY,
74 CREATION_DATE,
75 CREATED_BY,
76 LAST_UPDATE_LOGIN,
77 ORG_ID
78 )
79 VALUES
80 ( X_VAR_RENT_ID,
81 X_VAR_ABATEMENT_ID,
82 X_VAR_RENT_INV_ID,
83 X_PAYMENT_TERM_ID,
84 X_INCLUDE_TERM,
85 X_INCLUDE_INCREASES,
86 X_UPDATE_FLAG,
87 X_LAST_UPDATE_DATE,
88 X_LAST_UPDATED_BY,
89 X_CREATION_DATE,
90 X_CREATED_BY,
91 X_LAST_UPDATE_LOGIN,
92 l_ORG_ID
93 );
94
95 OPEN var_abatements;
96 FETCH var_abatements INTO X_ROWID;
97 IF (var_abatements%notfound) THEN
98 CLOSE var_abatements;
99 RAISE no_data_found;
100 END IF;
101 CLOSE var_abatements;
102
103 PNP_DEBUG_PKG.debug ('PN_VAR_ABATEMENTS_PKG.INSERT_ROW (-)');
104
105 END INSERT_ROW;
106
107 -------------------------------------------------------------------------------
108 -- PROCDURE : lock_row
109 -- INVOKED FROM : lock_row procedure
110 -- PURPOSE : locks the row
111 -- HISTORY :
112 -- 14-JUL-05 hareesha o Bug 4284035 - REPLACEd pn_dIStributions with _ALL table.
113 -------------------------------------------------------------------------------
114 procedure LOCK_ROW
115 (X_VAR_RENT_ID IN NUMBER,
116 X_VAR_RENT_INV_ID IN NUMBER,
117 X_PAYMENT_TERM_ID IN NUMBER
118 ) IS
119
120 CURSOR c1 IS
121 SELECT *
122 FROM PN_VAR_ABATEMENTS_ALL
123 WHERE VAR_RENT_ID = X_VAR_RENT_ID AND
124 VAR_RENT_INV_ID = X_VAR_RENT_INV_ID AND
125 PAYMENT_TERM_ID = X_PAYMENT_TERM_ID
126 FOR UPDATE OF VAR_ABATEMENT_ID NOWAIT;
127
128 tlINfo c1%ROWTYPE;
129
130 BEGIN
131
132 PNP_DEBUG_PKG.debug ('PN_VAR_ABATEMENTS_PKG.LOCK_ROW (+)');
133
134 OPEN c1;
135 FETCH c1 INTO tlINfo;
136 IF (c1%NOTFOUND) THEN
137 CLOSE c1;
138 RETURN;
139 END IF;
140 CLOSE c1;
141 IF (tlINfo.VAR_RENT_ID = X_VAR_RENT_ID) THEN
142 NULL;
143 ELSE
144 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('VAR_RENT_ID',tlINfo.VAR_RENT_ID);
145 END IF;
146
147
148 IF (tlINfo.VAR_RENT_INV_ID = X_VAR_RENT_INV_ID) THEN
149 NULL;
150 ELSE
151 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('VAR_RENT_INV_ID',tlINfo.VAR_RENT_INV_ID);
152 END IF;
153
154 IF (tlINfo.PAYMENT_TERM_ID = X_PAYMENT_TERM_ID) THEN
155 NULL;
156 ELSE
157 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('PAYMENT_TERM_ID',tlINfo.PAYMENT_TERM_ID);
158 END IF;
159
160 PNP_DEBUG_PKG.debug ('PN_VAR_ABATEMENTS_PKG.LOCK_ROW (-)');
161
162 END LOCK_ROW;
163
164 -----------------------------------------------------------------------
165 -- PROCDURE : UPDATE_ROW
166 -----------------------------------------------------------------------
167 procedure UPDATE_ROW
168 (
169 X_VAR_RENT_ID IN NUMBER,
170 X_VAR_RENT_INV_ID IN NUMBER,
171 X_PAYMENT_TERM_ID IN NUMBER,
172 X_INCLUDE_TERM IN VARCHAR2,
173 X_INCLUDE_INCREASES IN VARCHAR2,
174 X_UPDATE_FLAG IN VARCHAR2,
175 X_LAST_UPDATE_DATE IN DATE,
176 X_LAST_UPDATED_BY IN NUMBER,
177 X_LAST_UPDATE_LOGIN IN NUMBER
178 ) IS
179
180 BEGIN
181
182 PNP_DEBUG_PKG.debug ('PN_VAR_ABATEMENTS_PKG.UPDATE_ROW (+)');
183
184 UPDATE PN_VAR_ABATEMENTS_ALL SET
185 VAR_RENT_ID = X_VAR_RENT_ID,
186 VAR_RENT_INV_ID = X_VAR_RENT_INV_ID,
187 PAYMENT_TERM_ID = X_PAYMENT_TERM_ID,
188 INCLUDE_TERM = X_INCLUDE_TERM,
189 INCLUDE_INCREASES = X_INCLUDE_INCREASES,
190 UPDATE_FLAG = X_UPDATE_FLAG,
191 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
192 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
193 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
194 WHERE VAR_RENT_ID = X_VAR_RENT_ID
195 AND VAR_RENT_INV_ID = X_VAR_RENT_INV_ID
196 AND PAYMENT_TERM_ID = X_PAYMENT_TERM_ID;
197
198 IF (SQL%NOTFOUND) THEN
199 RAISE NO_DATA_FOUND;
200 END IF;
201
202 PNP_DEBUG_PKG.debug ('PN_VAR_ABATEMENTS_PKG.UPDATE_ROW (-)');
203
204 END UPDATE_ROW;
205
206 -------------------------------------------------------------------------------
207 -- PROCDURE : delete_row
208 -- INVOKED FROM : delete_row procedure
209 -- PURPOSE : deletes the row
210 -- HISTORY :
211 -- 14-JUL-05 hareesha o Bug 4284035 - REPLACEd pn_dIStributions with _ALL table.
212 -------------------------------------------------------------------------------
213
214 procedure DELETE_ROW
215 ( X_VAR_RENT_ID IN NUMBER,
216 X_VAR_RENT_INV_ID IN NUMBER,
217 X_PAYMENT_TERM_ID IN NUMBER
218 ) IS
219
220 BEGIN
221
222 PNP_DEBUG_PKG.debug ('PN_VAR_ABATEMENTS_PKG.DELETE_ROW (+)');
223
224 DELETE FROM PN_VAR_ABATEMENTS_ALL
225 WHERE VAR_RENT_ID = X_VAR_RENT_ID
226 AND VAR_RENT_INV_ID = X_VAR_RENT_INV_ID
227 AND PAYMENT_TERM_ID = X_PAYMENT_TERM_ID;
228
229 IF (SQL%NOTFOUND) THEN
230 RAISE NO_DATA_FOUND;
231 END IF;
232
233
234 PNP_DEBUG_PKG.debug ('PN_VAR_ABATEMENTS_PKG.DELETE_ROW (-)');
235
236 END DELETE_ROW;
237
238 --------------------------------------------------------------------
239 --
240 -- NAME : CHECK_CALC_INV_EXISTS()
241 -- DESCRIPTION :
242 -- PURPOSE :
243 -- INVOKED FROM : KEY-COMMIT trigger at block level
244 -- ARGUMENTS : NONE
245 -- REFERENCE : PN_COMMON.debug()
246 -- HISTORY :
247 --
248 -- 27-NOV-06 Lokesh Bala o Created
249 --
250 --------------------------------------------------------------------
251
252 FUNCTION CHECK_CALC_INV_EXISTS(p_var_rent_inv_id IN NUMBER,
253 p_var_rent_id IN NUMBER
254 )
255 RETURN VARCHAR2 IS
256 -- Get the invoice date
257 CURSOR get_inv_date(p1_var_rent_inv_id IN NUMBER)
258 IS
259 SELECT invoice_date
260 FROM pn_var_rent_inv_all
261 WHERE var_rent_inv_id=p1_var_rent_inv_id;
262
263 -- Get calculated invoices after this invoice
264 CURSOR calc_inv_exists(p1_inv_date IN DATE,p1_var_rent_id IN NUMBER)
265 IS
266 SELECT 'Y' calc_inv
267 FROM dual WHERE EXISTS
268 (SELECT *
269 FROM pn_var_rent_inv_all
270 WHERE var_rent_id=p1_var_rent_id
271 AND invoice_date > p1_inv_date);
272
273 l_inv_date DATE := NULL;
274 l_dummy VARCHAR2(1) :=NULL;
275
276 BEGIN
277 PNP_DEBUG_PKG.debug ('PNXVRENT_ABATEMENTS_CPG.CHECK_CALC_INV_EXISTS :'||' (+)');
278
279 FOR get_inv_date_rec IN get_inv_date(p_var_rent_inv_id) LOOP
280 l_inv_date := get_inv_date_rec.invoice_date;
281 END LOOP;
282 FOR calc_inv_exists_rec IN calc_inv_exists(l_inv_date,p_var_rent_id)LOOP
283 l_dummy := calc_inv_exists_rec.calc_inv;
284 END LOOP;
285 RETURN l_dummy;
286 PNP_DEBUG_PKG.debug ('PNXVRENT_ABATEMENTS_CPG.CHECK_CALC_INV_EXISTS :'||' (-)');
287 END check_calc_inv_exists;
288
289 --------------------------------------------------------------------
290 --
291 -- NAME : ABTMT_EXISTS()
292 -- DESCRIPTION :
293 -- PURPOSE :
294 -- INVOKED FROM : KEY-COMMIT trigger at block level
295 -- ARGUMENTS : NONE
296 -- REFERENCE : PN_COMMON.debug()
297 -- HISTORY :
298 --
299 -- 27-NOV-06 Lokesh Bala o Created
300 --
301 --------------------------------------------------------------------
302
303 FUNCTION abtmt_exists(p_var_rentId IN NUMBER,
304 p_var_rent_inv_id IN NUMBER,
305 p_pmt_term_id IN NUMBER
306 )
307 RETURN VARCHAR2 IS
308 -- Get the details of
309 CURSOR abtmt_exists_cur(p_var_rentId IN NUMBER,
310 p_var_rent_inv_id IN NUMBER,
311 p_pmt_term_id IN NUMBER)
312 IS
313 SELECT 'y'
314 FROM dual
315 WHERE exists ( select null from pn_var_abatements_all
316 where var_rent_id=p_var_rentId AND
317 var_rent_inv_id=p_var_rent_inv_id AND
318 payment_term_id=p_pmt_term_id);
319
320 l_abtmt_exists VARCHAR2(1):=NULL;
321
322 BEGIN
323 PNP_DEBUG_PKG.debug ('PNXVRENT_ABATEMENTS_CPG.ABTMT_EXISTS :'||' (+)');
324
325 OPEN abtmt_exists_cur(p_var_rentId ,p_var_rent_inv_id ,p_pmt_term_id );
326 FETCH abtmt_exists_cur INTO l_abtmt_exists;
327 CLOSE abtmt_exists_cur;
328
329 RETURN l_abtmt_exists;
330
331 PNP_DEBUG_PKG.debug ('PNXVRENT_ABATEMENTS_CPG.ABTMT_EXISTS :'||' (-)');
332 END abtmt_exists;
333 --------------------------------------------------------------------
334 --
335 -- NAME : RESET_UPDATE_FLAG()
336 -- DESCRIPTION :
337 -- PURPOSE :
338 -- INVOKED FROM : ON-COMMIT trigger at form level
339 -- ARGUMENTS : NONE
340 -- REFERENCE : PN_COMMON.debug()
341 -- HISTORY :
342 --
343 -- 27-NOV-06 Lokesh Bala o Created
344 --
345 --------------------------------------------------------------------
346
347 PROCEDURE RESET_UPDATE_FLAG(p_var_rentId IN NUMBER,
348 p_var_rent_inv_id IN NUMBER
349 )
350 IS
351 -- Get the details of
352 CURSOR get_update_cur(p_var_rentId IN NUMBER,p_var_rent_inv_id IN NUMBER) IS
353 SELECT *
354 FROM pn_var_abatements_all
355 WHERE var_rent_id= p_var_rentId
356 AND var_rent_inv_id = p_var_rent_inv_id
357 AND update_flag = 'Y';
358 BEGIN
359 --
360 FOR get_update_rec IN get_update_cur(p_var_rentId,p_var_rent_inv_id) LOOP
361 PN_VAR_ABATEMENTS_PKG.UPDATE_ROW (
362 X_VAR_RENT_ID => p_var_rentId ,
363 X_VAR_RENT_INV_ID => p_var_rent_inv_id,
364 X_PAYMENT_TERM_ID => get_update_rec.PAYMENT_TERM_ID,
365 X_INCLUDE_TERM => get_update_rec.INCLUDE_TERM ,
366 X_INCLUDE_INCREASES => get_update_rec.INCLUDE_INCREASES,
367 X_UPDATE_FLAG => NULL,
368 X_LAST_UPDATE_DATE => sysdate,
369 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),-1),
370 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),-1)
371 );
372 END LOOP;
373
374 END RESET_UPDATE_FLAG;
375
376 --------------------------------------------------------------------
377 --
378 -- NAME : ROLL_FWD_ON_UPD()
379 -- DESCRIPTION :
380 -- PURPOSE :
381 -- INVOKED FROM : ON-UPDATE trigger at block level,ON-COMMIT trigger
382 -- at form level
383 -- ARGUMENTS : NONE
384 -- REFERENCE : PN_COMMON.debug()
385 -- HISTORY :
386 --
387 -- 27-NOV-06 Lokesh Bala o Created
388 --
389 --------------------------------------------------------------------
390
391 PROCEDURE ROLL_FWD_ON_UPD(p_var_rentId IN NUMBER,
392 p_var_rent_inv_id IN NUMBER,
393 p_pmt_term_id IN NUMBER,
394 flag IN NUMBER
395 )
396 IS
397
398 l_inv_id NUMBER :=NULL;
399 l_row_id VARCHAR2(18):=NULL;
400 l_var_abmt_id NUMBER :=NULL;
401 l_inv_dt DATE :=NULL;
402 l_pmt_exists VARCHAR2(2):=NULL;
403 l_abtmt_exists VARCHAR2(2):=NULL;
404
405 -- Get invoice date
406 CURSOR get_inv_dt(p_var_rent_inv_id IN NUMBER) IS
407 SELECT invoice_date
408 FROM pn_var_rent_inv_all
409 WHERE var_rent_inv_id=p_var_rent_inv_id;
410
411 /*Cursor to get all invoices*/
412 CURSOR get_all_inv(p_var_rent_id IN NUMBER,l_invoice_dt IN DATE) IS
413 SELECT distinct gd1.invoice_date,decode(temp.inv_id,NULL,-1,temp.inv_id) v_inv_id
414 FROM pn_var_grp_dates_all gd1,
415 (SELECT gd.invoice_date inv_dt,vinv.var_rent_inv_id inv_id
416 FROM pn_var_grp_dates_all gd , pn_var_rent_inv_all vinv
417 WHERE vinv.var_rent_id=gd.var_rent_id
418 AND vinv.invoice_date=gd.invoice_date
419 AND vinv.period_id=gd.period_id
420 AND gd.var_rent_id=p_var_rent_id
421 AND vinv.adjust_num=0
422 ) temp
423 WHERE gd1.var_rent_id=p_var_rent_id
424 AND gd1.invoice_date=temp.inv_dt(+)
425 AND gd1.invoice_date>l_invoice_dt
426 ORDER BY gd1.invoice_date;
427
428
429 /*Cursor to check if a pmt term exists for a particular invoice*/
430 CURSOR check_pmt_terms(p_inv_id IN NUMBER,p_term_id IN NUMBER) IS
431 SELECT 'x' pterm_exists
432 FROM dual WHERE EXISTS
433 (SELECT NULL
434 FROM pn_payment_terms_all pterm,
435 pn_var_rents_all vrent,
436 pn_var_rent_inv_all vinv
437 WHERE
438 vrent.lease_id = pterm.lease_id
439 AND vrent.var_rent_id = vinv.var_rent_id
440 AND pterm.start_date <=
441 (SELECT MAX(gd.grp_end_date)
442 FROM pn_var_grp_dates_all gd
443 WHERE gd.period_id = vinv.period_id
444 AND gd.invoice_date = vinv.invoice_date
445 )
446 AND pterm.end_date >=
447 (SELECT MIN(gd1.grp_start_date)
448 FROM pn_var_grp_dates_all gd1
449 WHERE gd1.period_id = vinv.period_id
450 AND gd1.invoice_date = vinv.invoice_date
451 )
452 AND pterm.var_rent_inv_id IS NULL
453 AND pterm.index_period_id IS NULL
454 AND vinv.adjust_num = 0
455 AND vinv.var_rent_inv_id=p_inv_id
456 AND pterm.payment_term_id=p_term_id);
457
458 /*Cursor to check if an abtmt exists for a particular invoice*/
459 CURSOR check_abtmt_terms_inv(p_inv_id IN NUMBER,p_term_id IN NUMBER) IS
460 SELECT 'x' abatement_exists
461 FROM dual
462 WHERE EXISTS (SELECT payment_term_id
463 FROM pn_var_abatements_all
464 WHERE var_rent_inv_id=p_inv_id
465 AND payment_term_id=p_term_id);
466
467 -- Get all abatement terms for an invoice with update_flag='Y'
468 CURSOR get_upd_terms(p_var_rentId IN NUMBER,p_var_rent_inv_id IN NUMBER) IS
469 SELECT payment_term_id,include_term,include_increases
470 FROM pn_var_abatements_all pva
471 WHERE pva.var_rent_id= p_var_rentId
472 AND pva.var_rent_inv_id = p_var_rent_inv_id
473 AND update_flag = 'Y';
474
475 CURSOR org_cur(p_var_rentId IN NUMBER) IS
476 SELECT org_id
477 FROM pn_var_rents_all
478 WHERE var_rent_id =p_var_rentId;
479
480 l_org_id NUMBER;
481
482 BEGIN
483
484 FOR rec IN org_cur(p_var_rentId) LOOP
485 l_org_id := rec.org_id;
486 END LOOP;
487
488 --Get invoice date for the invoice id passed as parameter to this procedure
489 FOR get_inv_dt_rec IN get_inv_dt(p_var_rent_inv_id) LOOP
490 l_inv_dt := get_inv_dt_rec.invoice_date;
491 END LOOP;
492
493 --Get all invoices with invoice_date > l_inv_dt
494 FOR get_inv_rec IN get_all_inv(p_var_rentId,l_inv_dt) LOOP
495 l_inv_id := get_inv_rec.v_inv_id;
496
497 -- If gap exists between 2 invoices then stop roll forward
498 IF ( l_inv_id=-1 ) THEN
499 EXIT;
500 END IF;
501
502 --Case 1 : p_pmt_term_id passed IS NULL , so roll fwd all abtmt terms with update_flag='y'
503 IF p_pmt_term_id IS NULL THEN
504
505 FOR upd_rec IN get_upd_terms(p_var_rentId ,p_var_rent_inv_id) LOOP
506
507 l_pmt_exists:=NULL;
508 l_abtmt_exists:=NULL;
509
510 FOR pmt_term_rec IN check_pmt_terms(l_inv_id,upd_rec.payment_term_id) LOOP
511 l_pmt_exists := pmt_term_rec.pterm_exists;
512 END LOOP;
513
514 IF l_pmt_exists IS NOT NULL THEN
515
516 FOR abtmt_rec IN check_abtmt_terms_inv(l_inv_id,upd_rec.payment_term_id) LOOP
517 l_abtmt_exists := abtmt_rec.abatement_exists;
518 END LOOP;
519
520 IF l_abtmt_exists IS NULL THEN
521 l_row_id := NULL;
522 l_var_abmt_id :=NULL;
523
524 PN_VAR_ABATEMENTS_PKG.INSERT_ROW(
525 X_ROWID => l_row_id,
526 X_VAR_ABATEMENT_ID => l_var_abmt_id,
527 X_VAR_RENT_ID => p_var_rentId,
528 X_VAR_RENT_INV_ID => l_inv_id,
529 X_PAYMENT_TERM_ID => upd_rec.payment_term_id,
530 X_INCLUDE_TERM => upd_rec.include_term,
531 X_INCLUDE_INCREASES => upd_rec.include_increases,
532 X_UPDATE_FLAG => NULL,
533 X_CREATION_DATE => sysdate,
534 X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),-1),
535 X_LAST_UPDATE_DATE => sysdate,
536 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),-1),
537 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),-1),
538 X_ORG_ID => l_org_id );
539
540 ELSE
541 PN_VAR_ABATEMENTS_PKG.UPDATE_ROW(
542 X_VAR_RENT_ID => p_var_rentId,
543 X_VAR_RENT_INV_ID => l_inv_id,
544 X_PAYMENT_TERM_ID => upd_rec.payment_term_id,
545 X_INCLUDE_TERM => upd_rec.include_term,
546 X_INCLUDE_INCREASES => upd_rec.include_increases,
547 X_UPDATE_FLAG => NULL,
548 X_LAST_UPDATE_DATE => sysdate,
549 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),-1),
550 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),-1));
551 END IF;
552 END IF;
553 END LOOP;
554
555 --Case 2 : p_pmt_term_id IS NOT NULL and flag=0 , candidate for delete_row
556 ELSIF flag=0 THEN
557
558 l_pmt_exists:=NULL;
559 l_abtmt_exists:=NULL;
560
561 FOR pmt_term_rec IN check_pmt_terms(l_inv_id,p_pmt_term_id) LOOP
562 l_pmt_exists := pmt_term_rec.pterm_exists;
563 END LOOP;
564
565 IF l_pmt_exists IS NOT NULL THEN
566 FOR abtmt_rec IN check_abtmt_terms_inv(l_inv_id,p_pmt_term_id) LOOP
567 l_abtmt_exists := abtmt_rec.abatement_exists;
568 END LOOP;
569
570 IF l_abtmt_exists IS NOT NULL THEN
571
572 PN_VAR_ABATEMENTS_PKG.DELETE_ROW(
573 X_VAR_RENT_ID => p_var_rentId,
574 X_VAR_RENT_INV_ID => l_inv_id,
575 X_PAYMENT_TERM_ID => p_pmt_term_id);
576
577 END IF;
578 END IF;
579
580
581 END IF;
582
583 END LOOP;
584
585 PNP_DEBUG_PKG.debug ('PNXVRENT_ABATEMENTS_CPG.ROLL_FWD_ON_UPD :'||' (-)');
586
587 END ROLL_FWD_ON_UPD;
588 --------------------------------------------------------------------
589 --
590 -- NAME : get_include_term()
591 -- DESCRIPTION :
592 -- PURPOSE :
593 -- INVOKED FROM : form view of ABATEMENTS_BLK
594 -- ARGUMENTS : NONE
595 -- REFERENCE : PN_COMMON.debug()
596 -- HISTORY :
597 --
598 -- 27-NOV-06 Lokesh Bala o Created
599 --
600 --------------------------------------------------------------------
601
602 FUNCTION get_include_term(p_payment_term_id IN NUMBER,
603 p_var_rent_inv_id IN NUMBER,
604 p_var_rent_id IN NUMBER
605 )
606 RETURN VARCHAR2 IS
607 -- Get the details of
608 CURSOR incl_term_cur(p_payment_term_id IN NUMBER,p_var_rent_inv_id IN NUMBER,
609 p_var_rent_id IN NUMBER) IS
610 SELECT include_term
611 FROM pn_var_abatements_all
612 WHERE var_rent_id=p_var_rent_id
613 AND payment_term_id=p_payment_term_id
614 AND var_rent_inv_id=p_var_rent_inv_id;
615
616 l_incl_term VARCHAR2(1):='N';
617
618 BEGIN
619 OPEN incl_term_cur(p_payment_term_id,p_var_rent_inv_id,p_var_rent_id);
620 FETCH incl_term_cur INTO l_incl_term;
621 IF (incl_term_cur%notfound OR l_incl_term IS NULL) THEN
622 l_incl_term := 'N';
623 END IF;
624 CLOSE incl_term_cur;
625
626 RETURN l_incl_term;
627
628 EXCEPTION
629 WHEN no_data_found THEN
630 RETURN 'N';
631 END get_include_term;
632 --------------------------------------------------------------------
633 --
634 -- NAME : get_include_increases()
635 -- DESCRIPTION :
636 -- PURPOSE :
637 -- INVOKED FROM : form view of ABATEMENTS_BLK
638 -- ARGUMENTS : NONE
639 -- REFERENCE : PN_COMMON.debug()
640 -- HISTORY :
641 --
642 -- 27-NOV-06 Lokesh Bala o Created
643 --
644 --------------------------------------------------------------------
645
646 FUNCTION get_include_increases(p_payment_term_id IN NUMBER,
647 p_var_rent_inv_id IN NUMBER,
648 p_var_rent_id IN NUMBER)
649 RETURN VARCHAR2 IS
650 -- Get the details of
651 CURSOR incl_increases_cur(p_payment_term_id IN NUMBER,p_var_rent_inv_id IN NUMBER,
652 p_var_rent_id IN NUMBER) IS
653 SELECT include_increases
654 FROM pn_var_abatements_all
655 WHERE var_rent_id=p_var_rent_id
656 AND payment_term_id=p_payment_term_id
657 AND var_rent_inv_id=p_var_rent_inv_id;
658 l_incl_incr VARCHAR2(1):='N';
659
660 BEGIN
661 OPEN incl_increases_cur(p_payment_term_id,p_var_rent_inv_id,p_var_rent_id);
662 FETCH incl_increases_cur INTO l_incl_incr;
663 IF (incl_increases_cur%notfound OR l_incl_incr IS NULL) THEN
664 l_incl_incr := 'N';
665 END IF;
666 CLOSE incl_increases_cur;
667
668 RETURN l_incl_incr;
669
670 EXCEPTION
671 WHEN no_data_found THEN
672 RETURN 'N';
673 END get_include_increases;
674 --------------------------------------------------------------------
675 --
676 -- NAME : ROLL_FWD_FST_ON_UPD()
677 -- DESCRIPTION :
678 -- PURPOSE :
679 -- INVOKED FROM : ON-UPDATE trigger at block level,ON-COMMIT trigger
680 -- at form level for 1st partial period
681 -- ARGUMENTS : NONE
682 -- REFERENCE : PN_COMMON.debug()
683 -- HISTORY :
684 --
685 -- 27-NOV-06 Lokesh Bala o Created
686 --
687 --------------------------------------------------------------------
688 PROCEDURE ROLL_FWD_FST_ON_UPD(p_var_rentId IN NUMBER,
689 p_var_rent_inv_id IN NUMBER,
690 p_pmt_term_id IN NUMBER,
691 flag IN NUMBER
692 )
693 IS
694 l_inv_id NUMBER :=NULL;
695 l_row_id ROWID :=NULL;
696 l_var_abmt_id NUMBER :=NULL;
697 l_inv_dt DATE :=NULL;
698 l_pmt_exists VARCHAR2(2):=NULL;
699 l_abtmt_exists VARCHAR2(2):=NULL;
700
701 /*Cursor to get all invoices from 2nd annual period*/
702 CURSOR get_all_inv(p_var_rent_id IN NUMBER) IS
703 SELECT distinct gd1.invoice_date,decode(temp.inv_id,NULL,-1,temp.inv_id) v_inv_id
704 FROM pn_var_grp_dates_all gd1,
705 pn_var_periods_all vp,
706 (SELECT gd.invoice_date inv_dt,vinv.var_rent_inv_id inv_id
707 FROM pn_var_grp_dates_all gd , pn_var_rent_inv_all vinv
708 WHERE vinv.var_rent_id=gd.var_rent_id
709 AND vinv.invoice_date=gd.invoice_date
710 AND vinv.period_id=gd.period_id
711 AND gd.var_rent_id=p_var_rent_id
712 AND adjust_num=0
713 ) temp
714 WHERE gd1.var_rent_id=p_var_rent_id
715 AND gd1.invoice_date=temp.inv_dt(+)
716 AND gd1.period_id=vp.period_id
717 AND vp.period_num >1
718 --AND gd1.invoice_date>l_invoice_dt
719 ORDER BY gd1.invoice_date;
720
721 /*Cursor to check if a pmt term exists for a particular invoice*/
722 CURSOR check_pmt_terms(p_inv_id IN NUMBER,p_term_id IN NUMBER) IS
723 SELECT 'x' pterm_exists
724 FROM dual WHERE EXISTS
725 (SELECT NULL
726 FROM pn_payment_terms_all pterm,
727 pn_var_rents_all vrent,
728 pn_var_rent_inv_all vinv
729 WHERE
730 vrent.lease_id = pterm.lease_id
731 AND vrent.var_rent_id = vinv.var_rent_id
732 AND pterm.start_date <=
733 (SELECT MAX(gd.grp_end_date)
734 FROM pn_var_grp_dates_all gd
735 WHERE gd.period_id = vinv.period_id
736 AND gd.invoice_date = vinv.invoice_date
737 )
738 AND pterm.end_date >=
739 (SELECT MIN(gd1.grp_start_date)
740 FROM pn_var_grp_dates_all gd1
741 WHERE gd1.period_id = vinv.period_id
742 AND gd1.invoice_date = vinv.invoice_date
743 )
744 AND pterm.var_rent_inv_id IS NULL
745 AND pterm.index_period_id IS NULL
746 AND vinv.adjust_num = 0
747 AND vinv.var_rent_inv_id=p_inv_id
748 AND pterm.payment_term_id=p_term_id);
749
750 /*Cursor to check if an abtmt exists for a particular invoice*/
751 CURSOR check_abtmt_terms_inv(p_inv_id IN NUMBER,p_term_id IN NUMBER) IS
752 SELECT 'x' abatement_exists
753 FROM dual
754 WHERE exists (select payment_term_id
755 FROM pn_var_abatements_all
756 WHERE var_rent_inv_id=p_inv_id
757 AND payment_term_id=p_term_id);
758
759 -- Get all abatement terms for an invoice with update_flag='Y'
760 CURSOR get_upd_terms(p_var_rentId IN NUMBER,p_var_rent_inv_id IN NUMBER) IS
761 SELECT payment_term_id,include_term,include_increases
762 FROM pn_var_abatements_all pva
763 WHERE pva.var_rent_id= p_var_rentId
764 AND pva.var_rent_inv_id = p_var_rent_inv_id
765 AND update_flag = 'Y';
766
767 CURSOR org_cur(p_var_rentId IN NUMBER) IS
768 SELECT org_id
769 FROM pn_var_rents_all
770 WHERE var_rent_id =p_var_rentId;
771
772 l_org_id NUMBER;
773
774 BEGIN
775 pnp_debug_pkg.debug ('PNXVRENT_ABATEMENTS_CPG.ROLL_FWD_FST_ON_UPD :'||' (+)');
776 FOR rec IN org_cur(p_var_rentId) LOOP
777 l_org_id := rec.org_id;
778 END LOOP;
779
780 /* Get all invoices from 2nd annual period*/
781 FOR get_inv_rec IN get_all_inv(p_var_rentId) LOOP
782 l_inv_id := get_inv_rec.v_inv_id;
783
784 -- If gap exists between 2 invoices then stop roll forward
785 IF ( l_inv_id=-1 ) THEN
786 EXIT;
787 END IF;
788
789 --Case 1 : p_pmt_term_id passed IS NULL , so roll fwd all abtmt terms with update_flag='y'
790 IF p_pmt_term_id IS NULL THEN
791
792 FOR upd_rec IN get_upd_terms(p_var_rentId ,p_var_rent_inv_id) LOOP
793
794 l_pmt_exists:=NULL;
795 l_abtmt_exists:=NULL;
796
797 FOR pmt_term_rec IN check_pmt_terms(l_inv_id,upd_rec.payment_term_id) LOOP
798 l_pmt_exists := pmt_term_rec.pterm_exists;
799 END LOOP;
800
801 IF l_pmt_exists IS NOT NULL THEN
802
803 FOR abtmt_rec IN check_abtmt_terms_inv(l_inv_id,upd_rec.payment_term_id) LOOP
804 l_abtmt_exists := abtmt_rec.abatement_exists;
805 END LOOP;
806
807 IF l_abtmt_exists IS NULL THEN
808 l_row_id := NULL;
809 l_var_abmt_id :=NULL;
810
811 PN_VAR_ABATEMENTS_PKG.INSERT_ROW(
812 X_ROWID => l_row_id,
813 X_VAR_ABATEMENT_ID => l_var_abmt_id,
814 X_VAR_RENT_ID => p_var_rentId,
815 X_VAR_RENT_INV_ID => l_inv_id,
816 X_PAYMENT_TERM_ID => upd_rec.payment_term_id,
817 X_INCLUDE_TERM => upd_rec.include_term,
818 X_INCLUDE_INCREASES => upd_rec.include_increases,
819 X_UPDATE_FLAG => NULL,
820 X_CREATION_DATE => sysdate,
821 X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),-1),
822 X_LAST_UPDATE_DATE => sysdate,
823 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),-1),
824 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),-1),
825 X_ORG_ID => l_org_id );
826
827 ELSE
828 PN_VAR_ABATEMENTS_PKG.UPDATE_ROW(
829 X_VAR_RENT_ID => p_var_rentId,
830 X_VAR_RENT_INV_ID => l_inv_id,
831 X_PAYMENT_TERM_ID => upd_rec.payment_term_id,
832 X_INCLUDE_TERM => upd_rec.include_term,
833 X_INCLUDE_INCREASES => upd_rec.include_increases,
834 X_UPDATE_FLAG => NULL,
835 X_LAST_UPDATE_DATE => sysdate,
836 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),-1),
837 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),-1));
838 END IF;
839 END IF;
840 END LOOP;
841
842 --Case 2 : p_pmt_term_id IS NOT NULL and flag=0 , candidate for delete_row
843 ELSIF flag=0 THEN
844
845 l_pmt_exists:=NULL;
846 l_abtmt_exists:=NULL;
847
848 FOR pmt_term_rec IN check_pmt_terms(l_inv_id,p_pmt_term_id) LOOP
849 l_pmt_exists := pmt_term_rec.pterm_exists;
850 END LOOP;
851
852 IF l_pmt_exists IS NOT NULL THEN
853 FOR abtmt_rec IN check_abtmt_terms_inv(l_inv_id,p_pmt_term_id) LOOP
854 l_abtmt_exists := abtmt_rec.abatement_exists;
855 END LOOP;
856
857 IF l_abtmt_exists IS NOT NULL THEN
858
859 PN_VAR_ABATEMENTS_PKG.DELETE_ROW(
860 X_VAR_RENT_ID => p_var_rentId,
861 X_VAR_RENT_INV_ID => l_inv_id,
862 X_PAYMENT_TERM_ID => p_pmt_term_id);
863
864 END IF;
865 END IF;
866
867 END IF;
868
869 END LOOP;
870 pnp_debug_pkg.debug ('PNXVRENT_ABATEMENTS_CPG.ROLL_FWD_FST_ON_UPD :'||' (-)');
871
872 END ROLL_FWD_FST_ON_UPD;
873
874 FUNCTION CHECK_TRUE_UP_INVOICE(p_var_rent_inv_id IN NUMBER)
875 RETURN VARCHAR2
876 IS
877 --------------------------------------------------------------------
878 --
879 -- NAME : CHECK_TRUE_UP_INVOICE
880 -- DESCRIPTION :
881 -- PURPOSE :
882 -- INVOKED FROM : WHEN-BUTTON-PRESSED on PERIODS_INV_BLK.ABT_DETAILS_BTN
883 -- ARGUMENTS : NONE
884 -- REFERENCE : PN_COMMON.debug()
885 -- HISTORY :
886 -- 02-JUL-2007 lbala o Determines whether it is a true-up invoice or not
887 --------------------------------------------------------------------
888
889 -- Get the details of
890 CURSOR get_true_up_cur
891 IS
892 SELECT 'Y' as true_up_flag
893 FROM dual
894 WHERE EXISTS(SELECT NULL
895 FROM pn_var_rent_inv_all
896 WHERE var_rent_inv_id = p_var_rent_inv_id
897 AND true_up_amt IS NOT NULL
898 );
899 l_true_up_flag VARCHAR2(1) := 'N';
900
901 BEGIN
902
903 FOR rec IN get_true_up_cur LOOP
904 l_true_up_flag := rec.true_up_flag ;
905 END LOOP;
906
907 RETURN l_true_up_flag;
908
909 EXCEPTION
910 WHEN others THEN
911 NULL;
912 END CHECK_TRUE_UP_INVOICE;
913
914 END PN_VAR_ABATEMENTS_PKG;
[Home] [Help]