[Home] [Help]
PACKAGE BODY: APPS.PN_NORM_RENORM_PKG
Source
1 PACKAGE BODY PN_NORM_RENORM_PKG AS
2 -- $Header: PNRENRMB.pls 120.13.12020000.2 2012/07/18 10:00:12 admarath ship $
3
4 /* procedure spec for the private procedures */
5 PROCEDURE RENORMALIZE_ACROSS_ALL_DRAFT
6 (p_lease_context IN VARCHAR2,
7 p_lease_id IN NUMBER,
8 p_term_id IN NUMBER,
9 p_vendor_id IN NUMBER,
10 p_cust_id IN NUMBER,
11 p_vendor_site_id IN NUMBER,
12 p_cust_site_use_id IN NUMBER,
13 p_cust_ship_site_id IN NUMBER,
14 p_sob_id IN NUMBER,
15 p_curr_code IN VARCHAR2,
16 p_sch_day IN NUMBER,
17 p_norm_str_dt IN DATE,
18 p_norm_end_dt IN DATE,
19 p_rate IN NUMBER);
20
21 PROCEDURE RENORMALIZE_IN_FIRST_DRAFT
22 (p_lease_context IN VARCHAR2,
23 p_lease_id IN NUMBER,
24 p_term_id IN NUMBER,
25 p_vendor_id IN NUMBER,
26 p_cust_id IN NUMBER,
27 p_vendor_site_id IN NUMBER,
28 p_cust_site_use_id IN NUMBER,
29 p_cust_ship_site_id IN NUMBER,
30 p_sob_id IN NUMBER,
31 p_curr_code IN VARCHAR2,
32 p_sch_day IN NUMBER,
33 p_norm_str_dt IN DATE,
34 p_norm_end_dt IN DATE,
35 p_rate IN NUMBER);
36
37
38 /* all procedure body */
39
40 /*------------------------------------------------------------------------------
41 NAME : RENORMALIZE_ACROSS_ALL_DRAFT
42 DESCRIPTION : Procedure to handle renormalization when the profile option for
43 'Renormalization across all draft schedules' is Y.
44 The renormalization is done across all original draft schedules.
45 HISTORY :
46 20-OCT-04 atuppad o Created
47 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
48 23-MAR-12 asahoo o Removing the variable l_plr_amt as the total cash has to be distributed accross all the schedules
49 starting from ammendment commencement date. If there are approved schedules before amendment
50 commencement date, payment items for those schedules will be reversed.
51 ------------------------------------------------------------------------------*/
52 PROCEDURE RENORMALIZE_ACROSS_ALL_DRAFT
53 (p_lease_context IN VARCHAR2,
54 p_lease_id IN NUMBER,
55 p_term_id IN NUMBER,
56 p_vendor_id IN NUMBER,
57 p_cust_id IN NUMBER,
58 p_vendor_site_id IN NUMBER,
59 p_cust_site_use_id IN NUMBER,
60 p_cust_ship_site_id IN NUMBER,
61 p_sob_id IN NUMBER,
62 p_curr_code IN VARCHAR2,
63 p_sch_day IN NUMBER,
64 p_norm_str_dt IN DATE,
65 p_norm_end_dt IN DATE,
66 p_rate IN NUMBER)
67 IS
68
69 /* get total cash amount */
70 CURSOR GET_CASH_TOTAL IS
71 SELECT NVL(SUM(actual_amount), 0) total_cash_amount
72 FROM pn_payment_items_all
73 WHERE payment_term_id = p_term_id
74 AND payment_item_type_lookup_code = 'CASH';
75
76 /* get total approved norm amount */
77 CURSOR GET_NORM_TOTAL IS
78 SELECT NVL(SUM(actual_amount), 0) total_norm_amount
79 FROM pn_payment_items_all item,
80 pn_payment_schedules_all schedule
81 WHERE schedule.lease_id = p_lease_id
82 AND item.payment_schedule_id = schedule.payment_schedule_id
83 AND item.payment_term_id = p_term_id
84 AND item.payment_item_type_lookup_code = 'NORMALIZED'
85 AND schedule.payment_status_lookup_code IN ('APPROVED','ON_HOLD');
86
87 l_cash_total NUMBER;
88 l_norm_apprv_total NUMBER;
89 l_total_schedules NUMBER;
90 l_norm_months NUMBER;
91 l_rounding_err NUMBER;
92 l_new_normalized_amount NUMBER;
93 l_day_of_norm_start_dt VARCHAR2(2);
94 l_day_of_norm_end_dt VARCHAR2(2);
95 l_partial_start_flag BOOLEAN;
96 l_partial_start_fraction NUMBER;
97 l_partial_end_flag BOOLEAN;
98 l_partial_end_fraction NUMBER;
99 l_precision NUMBER;
100 l_ext_precision NUMBER;
101 l_min_acct_unit NUMBER;
102 l_norm_start_sch_date DATE;
103 l_norm_end_sch_date DATE;
104 l_end_fraction_amt NUMBER;
105 l_start_fraction_amt NUMBER;
106 l_norm_amt NUMBER; /* 6838211 */
107 l_amd_comn_date DATE; /* 6838211 */
108 l_sch_date_1 DATE; /* 6838211 */
109 l_act_amt NUMBER := 0; /* 6893609 */
110 l_app_amt NUMBER; /* 6893609 */
111 l_dft_amt NUMBER := 0;
112 l_term_amt NUMBER:= 0; /* 8491119 */
113 l_amd_comn_date_tmp DATE; /* 8491119 */
114
115
116 CURSOR org_cur IS
117 SELECT org_id
118 FROM pn_payment_terms_all
119 WHERE payment_term_id = p_term_id;
120
121 /*Bug4956314 */
122 CURSOR check_cash_item (b_schedule_id NUMBER,b_term_id NUMBER)
123 IS
124 SELECT 1 FROM dual
125 WHERE exists
126 (SELECT 1
127 FROM pn_payment_items_all ppi
128 WHERE ppi.payment_schedule_id = b_schedule_id
129 AND ppi.payment_item_type_lookup_code = 'CASH'
130 AND ppi.payment_term_id = b_term_id
131 );
132
133 l_cash_item NUMBER;
134
135 /*Bug4956314 */
136
137
138 l_org_id NUMBER;
139
140 BEGIN
141
142 --Fix for bug#14143342
143 SELECT pn_schedules_items.FIRST_DAY(change_commencement_date)
144 INTO l_amd_comn_date
145 FROM
146 (SELECT *
147 FROM pn_lease_changes_all
148 WHERE lease_id = p_lease_id
149 ORDER BY lease_change_id DESC)
150 WHERE rownum < 2;
151
152 IF l_amd_comn_date IS NULL
153 THEN
154 SELECT lease_commencement_date
155 INTO l_amd_comn_date
156 FROM pn_lease_details_all
157 WHERE lease_id = p_lease_id;
158 END IF;
159
160 pnp_debug_pkg.log(' l_amd_comn_date : ' || l_amd_comn_date );
161 pnp_debug_pkg.log(' p_norm_str_dt : ' || p_norm_str_dt );
162
163 IF l_amd_comn_date > p_norm_str_dt THEN /* 6893609 */
164 l_amd_comn_date_tmp := p_norm_str_dt;
165 ELSE
166 l_amd_comn_date_tmp := l_amd_comn_date;
167 END IF;
168
169 --Fix for bug#14143342
170 SELECT NVL(SUM(ppi.actual_amount),0) /* 6893609 */
171 INTO l_app_amt
172 FROM pn_payment_items_all ppi,
173 pn_payment_schedules_all pps
174 WHERE ppi.payment_term_id = p_term_id
175 AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
176 AND pps.payment_schedule_id = ppi.payment_schedule_id
177 AND pps.payment_status_lookup_code <> 'DRAFT'
178 AND schedule_date >= pn_schedules_items.FIRST_DAY(l_amd_comn_date_tmp); /* 8491119 */
179 --AND schedule_date <= (SELECT last_day(lease_termination_date) from /* 11742270 */
180 --pn_lease_details_all where lease_id = p_lease_id );
181
182 pnp_debug_pkg.log('l_app_amt : ' || l_app_amt);
183
184 --Draft schedules should not be deleted before ACD
185 --Fix for bug#14143342
186 SELECT NVL(SUM(ppi.actual_amount),0)
187 INTO l_dft_amt
188 FROM pn_payment_items_all ppi,
189 pn_payment_schedules_all pps
190 WHERE ppi.payment_term_id = p_term_id
191 AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
192 AND pps.payment_schedule_id = ppi.payment_schedule_id
193 AND pps.payment_status_lookup_code = 'DRAFT'
194 AND schedule_date < pn_schedules_items.FIRST_DAY(l_amd_comn_date_tmp);
195
196 pnp_debug_pkg.log('l_dft_amt : ' || l_dft_amt);
197
198 Select NVL(SUM(ppi.actual_amount),0)
199 into l_term_amt
200 FROM pn_payment_items_all ppi,
201 pn_payment_schedules_all pps
202 WHERE ppi.payment_term_id = p_term_id
203 AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
204 AND pps.payment_schedule_id = ppi.payment_schedule_id
205 AND pps.payment_status_lookup_code <> 'DRAFT'
206 AND to_char(schedule_date,'MON-YY') = to_char(l_amd_comn_date,'MON-YY'); /* 8491119 */
207
208 pnp_debug_pkg.log(' PN_NORM_RENORM_PKG.RENORMALIZE_ACROSS_ALL_DRAFT (+) ');
209 pnp_debug_pkg.log(' Procedure called with Parameters... ');
210 pnp_debug_pkg.log(' p_lease_context : ' || p_lease_context );
211 pnp_debug_pkg.log(' p_lease_id : ' || p_lease_id );
212 pnp_debug_pkg.log(' p_term_id : ' || p_term_id );
213 pnp_debug_pkg.log(' p_vendor_id : ' || p_vendor_id );
214 pnp_debug_pkg.log(' p_cust_id : ' || p_cust_id );
215 pnp_debug_pkg.log(' p_vendor_site_id : ' || p_vendor_site_id );
216 pnp_debug_pkg.log(' p_cust_site_use_id : ' || p_cust_site_use_id );
217 pnp_debug_pkg.log(' p_cust_ship_site_id : ' || p_cust_ship_site_id );
218 pnp_debug_pkg.log(' p_sob_id : ' || p_sob_id );
219 pnp_debug_pkg.log(' p_curr_code : ' || p_curr_code );
220 pnp_debug_pkg.log(' p_sch_day : ' || p_sch_day );
221 pnp_debug_pkg.log(' p_norm_str_dt : ' || p_norm_str_dt );
222 pnp_debug_pkg.log(' p_norm_end_dt : ' || p_norm_end_dt );
223 pnp_debug_pkg.log(' p_rate : ' || p_rate );
224
225 /* initilizations*/
226 l_partial_start_flag := FALSE;
227 l_partial_end_flag := FALSE;
228 l_total_schedules := g_norm_item_tbl.COUNT;
229 l_norm_months := l_total_schedules;
230 l_start_fraction_amt := 0;
231 l_end_fraction_amt := 0;
232 fnd_currency.get_info(p_curr_code, l_precision, l_ext_precision, l_min_acct_unit);
233
234 FOR get_cash_rec IN get_cash_total LOOP
235 l_cash_total := get_cash_rec.total_cash_amount;
236 END LOOP;
237
238 pnp_debug_pkg.log('l_cash_total : '||TO_CHAR(l_cash_total));
239
240 FOR get_norm_rec IN get_norm_total LOOP
241 l_norm_apprv_total := get_norm_rec.total_norm_amount;
242 END LOOP;
243 pnp_debug_pkg.log(' l_norm_apprv_total : '||TO_CHAR( l_norm_apprv_total));
244
245 --asahoo setting Leave alone amount to Zero incase of Contraction and Expansion
246 --Fix for bug#14143342
247 --IF (p_lease_context in ('CON','EXP')) THEN
248 --l_norm_apprv_total := 0;
249 --ELSE
250 l_norm_apprv_total := l_norm_apprv_total - NVL(l_app_amt,0) + l_dft_amt; /* 6893609 */
251 --END IF;
252
253 pnp_debug_pkg.log(' leave alone amount : '||TO_CHAR( l_norm_apprv_total));
254
255 /* find which day of month the lease starts.*/
256 l_day_of_norm_start_dt := TO_CHAR(p_norm_str_dt,'DD');
257
258 /* find which day of month the lease ends. */
259 l_day_of_norm_end_dt := TO_CHAR(p_norm_end_dt,'DD');
260
261 /* get the first norm schedule date */
262 l_norm_start_sch_date := TO_DATE(
263 TO_CHAR(
264 p_sch_day)||'/'||TO_CHAR(p_norm_str_dt,'MM/YYYY'),'DD/MM/YYYY');
265
266 /* get the last norm schedule date */
267 l_norm_end_sch_date := TO_DATE(
268 TO_CHAR(
269 p_sch_day)||'/'||TO_CHAR(p_norm_end_dt,'MM/YYYY'),'DD/MM/YYYY');
270
271 /* partial start month */
272 IF l_day_of_norm_start_dt <> '01' AND
273 l_norm_start_sch_date = g_norm_item_tbl(0).schedule_date THEN
274
275 l_partial_start_flag := TRUE;
276 IF g_pr_rule = 999 THEN
277 l_partial_start_fraction := ((LAST_DAY(p_norm_str_dt) - p_norm_str_dt)+1)/
278 TO_NUMBER(TO_CHAR(LAST_DAY(p_norm_str_dt),'DD'));
279 ELSE
280 l_partial_start_fraction := ((LAST_DAY(p_norm_str_dt) - p_norm_str_dt)+1)*12/g_pr_rule;
281 END IF;
282 l_norm_months := (l_norm_months - 1) + NVL(l_partial_start_fraction,0);
283
284 pnp_debug_pkg.log('create_normalize_items - l_partial_start_fraction - 1 : '
285 ||TO_CHAR(l_partial_start_fraction)); /* 8491119 */
286
287 END IF; /* 6893609 */
288
289 /* partial end month */
290 IF l_day_of_norm_end_dt <> TO_CHAR(LAST_DAY(p_norm_end_dt),'DD') AND
291 l_norm_end_sch_date = g_norm_item_tbl(g_norm_item_tbl.COUNT - 1).schedule_date THEN
292
293 l_partial_end_flag := TRUE;
294 IF g_pr_rule = 999 THEN
295 l_partial_end_fraction := TO_NUMBER(TO_CHAR(p_norm_end_dt,'DD'))/
296 TO_NUMBER(TO_CHAR(LAST_DAY(p_norm_end_dt),'DD'));
297
298 pnp_debug_pkg.log('create_normalize_items - l_partial_end_fraction - 1 : '
299 ||TO_CHAR(l_partial_end_fraction)); /* 6893609 */
300 ELSE
301 l_partial_end_fraction := TO_NUMBER(TO_CHAR(p_norm_end_dt,'DD'))*12/g_pr_rule;
302
303 pnp_debug_pkg.log('create_normalize_items - l_partial_end_fraction - 2 : '
304 ||TO_CHAR(l_partial_end_fraction)); /* 6893609 */
305 END IF;
306 l_norm_months := (l_norm_months - 1) + NVL(l_partial_end_fraction,0);
307 END IF;
308
309 pnp_debug_pkg.log('l_norm_months : '||TO_CHAR(l_norm_months));
310
311 /* calculate new normalized amount */
312 IF l_norm_months <= 1 THEN
313 l_new_normalized_amount := l_cash_total - l_norm_apprv_total ; /* 8491119 */
314 l_partial_start_flag := FALSE;
315 l_partial_end_flag := FALSE;
316 ELSE
317 l_new_normalized_amount := (l_cash_total - l_norm_apprv_total)/l_norm_months; /* 8491119 */
318 END IF;
319
320 pnp_debug_pkg.log('l_new_normalized_amount : '||TO_CHAR(l_new_normalized_amount));
321
322 /*populate the pl/sql table */
323 FOR i IN 0 .. g_norm_item_tbl.COUNT - 1 LOOP
324 g_norm_item_tbl(i).normalized_amount := l_new_normalized_amount;
325 END LOOP;
326
327 IF g_norm_item_tbl.COUNT > 1 THEN
328 /* prorate - partial start */
329 IF l_partial_start_flag THEN
330 g_norm_item_tbl(0).normalized_amount /* 8599816 */
331 := ROUND((l_new_normalized_amount * l_partial_start_fraction) , l_precision); /* 8491119 */
332 END IF;
333
334 /* prorate - partial end */
335 IF l_partial_end_flag THEN
336 g_norm_item_tbl(g_norm_item_tbl.COUNT - 1).normalized_amount
337 := ROUND(l_new_normalized_amount * l_partial_end_fraction, l_precision);
338 END IF;
339
340 ELSIF g_norm_item_tbl.COUNT = 1 THEN
341 /* prorate - partial start */
342 IF l_partial_start_flag THEN
343 l_start_fraction_amt
344 := ROUND(l_new_normalized_amount * l_partial_start_fraction, l_precision);
345 END IF;
346
347 /* prorate - partial start */
348 IF l_partial_end_flag THEN
349 l_end_fraction_amt
350 := ROUND(l_new_normalized_amount * l_partial_end_fraction, l_precision);
351 END IF;
352
353 IF l_partial_start_flag AND l_partial_end_flag THEN
354 g_norm_item_tbl(0).normalized_amount := l_start_fraction_amt + l_end_fraction_amt - l_new_normalized_amount;
355 ELSIF l_partial_start_flag THEN
356 g_norm_item_tbl(0).normalized_amount := l_start_fraction_amt;
357 ELSIF l_partial_end_flag THEN
358 g_norm_item_tbl(0).normalized_amount := l_end_fraction_amt;
359 ELSE
360 g_norm_item_tbl(0).normalized_amount := l_new_normalized_amount;
361 END IF;
362
363 END IF; /* end for g_norm_item_tbl.COUNT > 1 */
364
365 /* start for 7149537
366 adjust rounding error for last item
367 IF g_norm_item_tbl.COUNT > 2 THEN
368 l_rounding_err := l_cash_total - ((l_new_normalized_amount * ((g_norm_item_tbl.COUNT)-2))
369 + g_norm_item_tbl(0).normalized_amount
370 + g_norm_item_tbl(g_norm_item_tbl.COUNT - 1).normalized_amount
371 + l_norm_apprv_total
372 );
373 ELSIF g_norm_item_tbl.COUNT = 2 THEN
374 l_rounding_err := l_cash_total - (g_norm_item_tbl(0).normalized_amount
375 + g_norm_item_tbl(g_norm_item_tbl.COUNT - 1).normalized_amount
376 + l_norm_apprv_total
377 );
378 ELSIF g_norm_item_tbl.COUNT = 1 THEN
379 l_rounding_err := l_cash_total - (g_norm_item_tbl(0).normalized_amount
380 + l_norm_apprv_total
381 );
382 END IF;
383
384 IF l_rounding_err <> 0 THEN
385 g_norm_item_tbl(g_norm_item_tbl.COUNT - 1).normalized_amount
386 := g_norm_item_tbl(g_norm_item_tbl.COUNT - 1).normalized_amount + l_rounding_err;
387 END IF;
388 end 7149537 */
389
390 /* start to create/update normalized items */
391 FOR i IN 0 .. g_norm_item_tbl.COUNT - 1 LOOP
392
393 begin -- ver6
394 SELECT SUM(actual_amount) /* Bug 6893609*/
395 into l_act_amt
396 FROM pn_payment_items_all ppi,
397 pn_payment_schedules_all pps
398 WHERE ppi.payment_item_type_lookup_code = 'NORMALIZED'
399 AND pps.payment_schedule_id = ppi.payment_schedule_id
400 AND ppi.payment_term_id = p_term_id
401 AND pps.payment_status_lookup_code <> 'DRAFT'
402 AND due_date = g_norm_item_tbl(i).schedule_date;
403
404 if l_act_amt <> 0 then
405 g_norm_item_tbl(i).normalized_amount := ((-1 * l_act_amt) + g_norm_item_tbl(i).normalized_amount);
406 END IF;
407 exception -- ver6
408 when no_data_found then
409 l_act_amt := 0;
410 end;
411
412 /* first try to update */
413 UPDATE pn_payment_items_all ppi
414 SET ppi.actual_amount = g_norm_item_tbl(i).normalized_amount,
415 ppi.export_currency_amount = g_norm_item_tbl(i).normalized_amount,
416 ppi.last_update_date = SYSDATE,
417 ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
418 ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
419 WHERE ppi.payment_schedule_id = g_norm_item_tbl(i).schedule_id
420 AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
421 AND ppi.payment_term_id = p_term_id;
422
423 IF NVL(SQL%ROWCOUNT, 0) = 0 THEN
424
425 FOR rec IN org_cur LOOP
426 l_org_id := rec.org_id;
427 END LOOP;
428
429 INSERT INTO pn_payment_items_all
430 (
431 payment_item_id,
432 last_update_date,
433 last_updated_by,
434 creation_date,
435 created_by,
436 last_update_login,
437 actual_amount,
438 estimated_amount,
439 due_date,
440 payment_item_type_lookup_code,
441 payment_term_id,
442 payment_schedule_id,
443 period_fraction,
444 vendor_id,
445 customer_id,
446 vendor_site_id,
447 customer_site_use_id,
448 cust_ship_site_id,
449 set_of_books_id,
450 currency_code,
451 export_currency_code,
452 export_currency_amount,
453 rate,
454 org_id
455 )
456 VALUES
457 (
458 PN_PAYMENT_ITEMS_S.NEXTVAL,
459 SYSDATE,
460 NVL(fnd_profile.value('USER_ID'),0),
461 SYSDATE,
462 NVL(fnd_profile.value('USER_ID'),0),
463 NVL(fnd_profile.value('LOGIN_ID'),0),
464 g_norm_item_tbl(i).normalized_amount,
465 NULL,
466 g_norm_item_tbl(i).schedule_date,
467 'NORMALIZED',
468 p_term_id,
469 g_norm_item_tbl(i).schedule_id,
470 1,
471 p_vendor_id,
472 p_cust_id,
473 p_vendor_site_id,
474 p_cust_site_use_id,
475 p_cust_ship_site_id,
476 p_sob_id,
477 p_curr_code,
478 p_curr_code,
479 g_norm_item_tbl(i).normalized_amount,
480 p_rate,
481 l_org_id
482 );
483 END IF;
484
485 /*S.N. Bug 4956314 */
486
487 l_cash_item:=0;
488
489 FOR r_rec IN check_cash_item(g_norm_item_tbl(i).schedule_id,p_term_id)
490 LOOP
491 l_cash_item:=1;
492 END LOOP;
493
494 IF l_cash_item = 0 THEN
495 pnp_debug_pkg.log(' PN_NORM_RENORM_PKG.RENORMALIZE_ACROSS_ALL_DRAFT -> create $ 0 cash items(+)');
496 pnp_debug_pkg.log(' PN_NORM_RENORM_PKG.RENORMALIZE_ACROSS_ALL_DRAFT -> for schedule id : '||g_norm_item_tbl(i).schedule_id);
497 pn_schedules_items.create_cash_items
498 (p_est_amt => 0,
499 p_act_amt => 0,
500 p_sch_dt => g_norm_item_tbl(i).schedule_date,
501 p_sch_id => g_norm_item_tbl(i).schedule_id,
502 p_term_id => p_term_id,
503 p_vendor_id => p_vendor_id,
504 p_cust_id => p_cust_id,
505 p_vendor_site_id => p_vendor_site_id,
506 p_cust_site_use_id => p_cust_site_use_id,
507 p_cust_ship_site_id => p_cust_ship_site_id,
508 p_sob_id => p_sob_id,
509 p_curr_code => p_curr_code,
510 p_rate => p_rate);
511 pnp_debug_pkg.log(' PN_NORM_RENORM_PKG.RENORMALIZE_ACROSS_ALL_DRAFT -> $ 0 cash item created(-) ');
512 END IF;
513
514 /*E.N. Bug 4956314 */
515
516 END LOOP;
517
518 /* update the table */
519 UPDATE pn_payment_terms_all
520 SET event_type_code = p_lease_context,
521 norm_start_date = p_norm_str_dt,
522 norm_end_date = p_norm_end_dt
523 WHERE normalize ='Y'
524 AND payment_term_id = p_term_id;
525
526 pnp_debug_pkg.log(' PN_NORM_RENORM_PKG.RENORMALIZE_ACROSS_ALL_DRAFT (-) ');
527 EXCEPTION
528 WHEN OTHERS THEN
529 pnp_debug_pkg.log(' RENORMALIZE_ACROSS_ALL_DRAFT - OTHERS ERROR ... ' || sqlerrm);
530
531 END RENORMALIZE_ACROSS_ALL_DRAFT;
532
533
534 /*------------------------------------------------------------------------------
535 NAME : RENORMALIZE_IN_FIRST_DRAFT
536 DESCRIPTION : Procedure to handle renormalization when the profile option for
537 'Renormalization across all draft schedules' is N.
538 The adjustment amount of renormalization is tied to the first
539 draft schedule.
540 HISTORY :
541 20-OCT-04 atuppad o Created
542 ------------------------------------------------------------------------------*/
543 PROCEDURE RENORMALIZE_IN_FIRST_DRAFT
544 (p_lease_context IN VARCHAR2,
545 p_lease_id IN NUMBER,
546 p_term_id IN NUMBER,
547 p_vendor_id IN NUMBER,
548 p_cust_id IN NUMBER,
549 p_vendor_site_id IN NUMBER,
550 p_cust_site_use_id IN NUMBER,
551 p_cust_ship_site_id IN NUMBER,
552 p_sob_id IN NUMBER,
553 p_curr_code IN VARCHAR2,
554 p_sch_day IN NUMBER,
555 p_norm_str_dt IN DATE,
556 p_norm_end_dt IN DATE,
557 p_rate IN NUMBER)
558 IS
559
560 CURSOR GET_CASH_TOTAL IS
561 SELECT NVL(SUM(actual_amount), 0) total_cash_amount
562 FROM pn_payment_items_all
563 WHERE payment_term_id = p_term_id
564 AND payment_item_type_lookup_code = 'CASH';
565
566 CURSOR GET_NORM_TOTAL IS
567 SELECT NVL(SUM(actual_amount), 0) total_norm_amount
568 FROM pn_payment_items_all item,
569 pn_payment_schedules_all schedule
570 WHERE schedule.lease_id = p_lease_id
571 AND item.payment_schedule_id = schedule.payment_schedule_id
572 AND item.payment_term_id = p_term_id
573 AND item.payment_item_type_lookup_code = 'NORMALIZED'
574 AND schedule.payment_status_lookup_code <> 'DRAFT'; /*= 'APPROVED'; for bug# 7149537*/
575
576 CURSOR GET_LAST_APPRV_SCH IS
577 SELECT MAX(schedule.schedule_date) last_apprv_sch
578 FROM pn_payment_schedules_all schedule,
579 pn_payment_items_all item
580 WHERE schedule.lease_id = p_lease_id
581 AND item.payment_schedule_id = schedule.payment_schedule_id
582 AND schedule.payment_status_lookup_code = 'APPROVED'
583 AND item.payment_term_id = p_term_id;
584
585 /* Get schedule ID of a draft schedule containing original payment item
586 post the last approved schedule date */
587 CURSOR GET_ORIG_SCH_AFTER(p_last_apprv_sch DATE) IS
588 SELECT MIN(schedule.schedule_date) first_draft_sch
589 FROM pn_payment_schedules_all schedule,
590 pn_payment_items_all item
591 WHERE schedule.lease_id = p_lease_id
592 AND item.payment_schedule_id = schedule.payment_schedule_id
593 AND schedule.payment_status_lookup_code = 'DRAFT'
594 AND item.payment_term_id = p_term_id
595 AND item.last_adjustment_type_code IS NULL
596 AND schedule.schedule_date > p_last_apprv_sch;
597
598 /* Get schedule ID of a draft schedule containing original payment item */
599 CURSOR GET_FIRST_DRAFT_ORIG_SCH IS
600 SELECT MIN(schedule.schedule_date) first_draft_sch
601 FROM pn_payment_schedules_all schedule,
602 pn_payment_items_all item
603 WHERE schedule.lease_id = p_lease_id
604 AND item.payment_schedule_id = schedule.payment_schedule_id
605 AND schedule.payment_status_lookup_code = 'DRAFT'
606 AND item.payment_term_id = p_term_id
607 AND item.last_adjustment_type_code IS NULL;
608
609 /* Get schedule ID of a draft schedule - original or adjustment */
610 CURSOR GET_FIRST_DRAFT_SCH IS
611 SELECT MIN(schedule.schedule_date) first_draft_sch,
612 schedule.payment_schedule_id pay_schd_id
613 FROM pn_payment_schedules_all schedule,
614 pn_payment_items_all item
615 WHERE item.payment_schedule_id = schedule.payment_schedule_id
616 AND schedule.payment_status_lookup_code = 'DRAFT'
617 AND item.payment_term_id = p_term_id
618 GROUP BY schedule.payment_schedule_id;
619
620 l_cash_total NUMBER;
621 l_norm_apprv_total NUMBER;
622 l_partial_start_flag BOOLEAN;
623 l_partial_start_fraction NUMBER;
624 l_partial_end_flag BOOLEAN;
625 l_partial_end_fraction NUMBER;
626 l_precision NUMBER;
627 l_ext_precision NUMBER;
628 l_min_acct_unit NUMBER;
629 l_total_schedules NUMBER;
630 l_norm_months NUMBER;
631 l_day_of_norm_start_dt VARCHAR2(2);
632 l_day_of_norm_end_dt VARCHAR2(2);
633 l_norm_start_sch_date DATE;
634 l_norm_end_sch_date DATE;
635 l_last_apprv_sch_dt DATE;
636 l_adjustment_amount NUMBER;
637 l_adjustment_sch_dt DATE;
638 l_new_normalized_amount NUMBER;
639 l_start_fraction_amt NUMBER;
640 l_end_fraction_amt NUMBER;
641
642 CURSOR org_cur IS
643 SELECT org_id
644 FROM pn_payment_terms_all
645 WHERE payment_term_id = p_term_id;
646
647 /*Bug4956314 */
648 CURSOR check_cash_item (b_schedule_id NUMBER,b_term_id NUMBER)
649 IS
650 SELECT 1 FROM dual
651 WHERE exists
652 (SELECT 1
653 FROM pn_payment_items_all ppi
654 WHERE ppi.payment_schedule_id = b_schedule_id
655 AND ppi.payment_item_type_lookup_code = 'CASH'
656 AND ppi.payment_term_id = b_term_id
657 );
658
659 l_cash_item NUMBER;
660
661 /*Bug4956314 */
662
663
664 l_org_id NUMBER;
665
666 BEGIN
667 pnp_debug_pkg.log(' PN_NORM_RENORM_PKG.RENORMALIZE_IN_FIRST_DRAFT (+) ');
668 pnp_debug_pkg.log(' Procedure called with Parameters... ');
669 pnp_debug_pkg.log(' p_lease_context : ' || p_lease_context );
670 pnp_debug_pkg.log(' p_lease_id : ' || p_lease_id );
671 pnp_debug_pkg.log(' p_term_id : ' || p_term_id );
672 pnp_debug_pkg.log(' p_vendor_id : ' || p_vendor_id );
673 pnp_debug_pkg.log(' p_cust_id : ' || p_cust_id );
674 pnp_debug_pkg.log(' p_vendor_site_id : ' || p_vendor_site_id );
675 pnp_debug_pkg.log(' p_cust_site_use_id : ' || p_cust_site_use_id );
676 pnp_debug_pkg.log(' p_cust_ship_site_id : ' || p_cust_ship_site_id );
677 pnp_debug_pkg.log(' p_sob_id : ' || p_sob_id );
678 pnp_debug_pkg.log(' p_curr_code : ' || p_curr_code );
679 pnp_debug_pkg.log(' p_sch_day : ' || p_sch_day );
680 pnp_debug_pkg.log(' p_norm_str_dt : ' || p_norm_str_dt );
681 pnp_debug_pkg.log(' p_norm_end_dt : ' || p_norm_end_dt );
682 pnp_debug_pkg.log(' p_rate : ' || p_rate );
683
684 /* initilizations*/
685 l_partial_start_flag := FALSE;
686 l_partial_end_flag := FALSE;
687 l_total_schedules := g_norm_item_tbl.COUNT;
688 l_start_fraction_amt := 0;
689 l_end_fraction_amt := 0;
690 l_partial_start_fraction := 0;
691 l_partial_end_fraction := 0;
692 l_adjustment_amount := 0;
693 l_norm_months := CEIL(MONTHS_BETWEEN(LAST_DAY(p_norm_end_dt),
694 PN_SCHEDULES_ITEMS.FIRST_DAY(p_norm_str_dt)));
695 fnd_currency.get_info(p_curr_code, l_precision, l_ext_precision, l_min_acct_unit);
696
697 FOR get_cash_rec IN get_cash_total LOOP
698 l_cash_total := get_cash_rec.total_cash_amount;
699 END LOOP;
700
701 FOR get_norm_rec IN get_norm_total LOOP
702 l_norm_apprv_total := get_norm_rec.total_norm_amount;
703 END LOOP;
704
705 /* Find which day of month the lease starts.*/
706 l_day_of_norm_start_dt := TO_CHAR(p_norm_str_dt,'DD');
707
708 /* Find which day of month the lease ends. */
709 l_day_of_norm_end_dt := TO_CHAR(p_norm_end_dt,'DD');
710
711 /* get the first norm schedule date */
712 l_norm_start_sch_date := TO_DATE(
713 TO_CHAR(
714 p_sch_day)||'/'||TO_CHAR(p_norm_str_dt,'MM/YYYY'),'DD/MM/YYYY');
715
716 /* get the last norm schedule date */
717 l_norm_end_sch_date := TO_DATE(
718 TO_CHAR(
719 p_sch_day)||'/'||TO_CHAR(p_norm_end_dt,'MM/YYYY'),'DD/MM/YYYY');
720
721 /* partial start month */
722 IF l_day_of_norm_start_dt <> '01' AND
723 l_norm_start_sch_date = g_norm_item_tbl(0).schedule_date THEN
724
725 l_partial_start_flag := TRUE;
726 IF g_pr_rule = 999 THEN
727 l_partial_start_fraction := ((LAST_DAY(p_norm_str_dt) - p_norm_str_dt)+1)/
728 TO_NUMBER(TO_CHAR(LAST_DAY(p_norm_str_dt),'DD'));
729 ELSE
730 l_partial_start_fraction := ((LAST_DAY(p_norm_str_dt) - p_norm_str_dt)+1)*12/g_pr_rule;
731 END IF;
732 l_norm_months := (l_norm_months - 1) + NVL(l_partial_start_fraction,0);
733
734 END IF;
735
736 /* partial end month */
737 IF l_day_of_norm_end_dt <> TO_CHAR(LAST_DAY(p_norm_end_dt),'DD') AND
738 l_norm_end_sch_date = g_norm_item_tbl(g_norm_item_tbl.COUNT - 1).schedule_date THEN
739
740 l_partial_end_flag := TRUE;
741 IF g_pr_rule = 999 THEN
742 l_partial_end_fraction := TO_NUMBER(TO_CHAR(p_norm_end_dt,'DD'))/
743 TO_NUMBER(TO_CHAR(LAST_DAY(p_norm_end_dt),'DD'));
744 ELSE
745 l_partial_end_fraction := TO_NUMBER(TO_CHAR(p_norm_end_dt,'DD'))*12/g_pr_rule;
746 END IF;
747 l_norm_months := (l_norm_months - 1) + NVL(l_partial_end_fraction,0);
748
749 END IF;
750
751 /* calculate new normalized amount */
752 IF l_norm_months <= 1 THEN
753 l_new_normalized_amount := ROUND(l_cash_total - l_norm_apprv_total,
754 l_precision);
755 l_partial_start_flag := FALSE;
756 l_partial_end_flag := FALSE;
757 ELSE
758 l_new_normalized_amount := ROUND(l_cash_total/l_norm_months,
759 l_precision);
760 END IF;
761
762 /*populate the pl/sql table */
763 FOR i IN 0 .. g_norm_item_tbl.COUNT - 1 LOOP
764 g_norm_item_tbl(i).normalized_amount := l_new_normalized_amount;
765 END LOOP;
766
767 IF g_norm_item_tbl.COUNT >= 1 THEN
768 IF g_norm_item_tbl.COUNT > 1 THEN
769
770 /* prorate - partial start */
771 IF l_partial_start_flag THEN
772 g_norm_item_tbl(0).normalized_amount
773 := ROUND(l_new_normalized_amount * l_partial_start_fraction, l_precision);
774 END IF;
775
776 /* prorate - partial start */
777 IF l_partial_end_flag THEN
778 g_norm_item_tbl(g_norm_item_tbl.COUNT - 1).normalized_amount
779 := ROUND(l_new_normalized_amount * l_partial_end_fraction, l_precision);
780 END IF;
781
782 ELSIF g_norm_item_tbl.COUNT = 1 THEN
783
784 /* prorate - partial start */
785 IF l_partial_start_flag THEN
786 l_start_fraction_amt
787 := ROUND(l_new_normalized_amount * l_partial_start_fraction, l_precision);
788 END IF;
789
790 /* prorate - partial start */
791 IF l_partial_end_flag THEN
792 l_end_fraction_amt
793 := ROUND(l_new_normalized_amount * l_partial_end_fraction, l_precision);
794 END IF;
795
796 IF l_partial_start_flag AND l_partial_end_flag THEN
797 g_norm_item_tbl(0).normalized_amount := l_start_fraction_amt + l_end_fraction_amt - l_new_normalized_amount;
798 ELSIF l_partial_start_flag THEN
799 g_norm_item_tbl(0).normalized_amount := l_start_fraction_amt;
800 ELSIF l_partial_end_flag THEN
801 g_norm_item_tbl(0).normalized_amount := l_end_fraction_amt;
802 ELSE
803 g_norm_item_tbl(0).normalized_amount := l_new_normalized_amount;
804 END IF;
805
806 END IF; /* end for g_norm_item_tbl.COUNT > 1 */
807
808 END IF; /* end for g_norm_item_tbl.COUNT >= 1 */
809
810 /* calculate adj amount to be added to first draft schedule */
811 IF g_norm_item_tbl.COUNT > 2 THEN
812 l_adjustment_amount := l_cash_total - ((l_new_normalized_amount * ((g_norm_item_tbl.COUNT)-2))
813 + g_norm_item_tbl(0).normalized_amount
814 + g_norm_item_tbl(g_norm_item_tbl.COUNT - 1).normalized_amount
815 + l_norm_apprv_total
816 );
817 ELSIF g_norm_item_tbl.COUNT = 2 THEN
818 l_adjustment_amount := l_cash_total - (g_norm_item_tbl(0).normalized_amount
819 + g_norm_item_tbl(1).normalized_amount
820 + l_norm_apprv_total
821 );
822 ELSIF g_norm_item_tbl.COUNT = 1 THEN
823 l_adjustment_amount := l_cash_total - (g_norm_item_tbl(0).normalized_amount
824 + l_norm_apprv_total
825 );
826 ELSIF g_norm_item_tbl.COUNT = 0 THEN
827 l_adjustment_amount := l_cash_total - l_norm_apprv_total;
828 END IF;
829
830 /* Find date of last approved schedule, with default value set to the first schedule */
831 l_last_apprv_sch_dt := l_norm_start_sch_date;
832
833 /* Find first available draft schedule and get its ID */
834 FOR sch_rec IN get_last_apprv_sch LOOP
835 l_last_apprv_sch_dt := sch_rec.last_apprv_sch;
836 END LOOP;
837
838 FOR sch_rec IN get_orig_sch_after (l_last_apprv_sch_dt) LOOP
839 l_adjustment_sch_dt := sch_rec.first_draft_sch;
840 END LOOP;
841
842 IF l_adjustment_sch_dt IS NULL THEN
843 FOR sch_rec IN get_first_draft_orig_sch LOOP
844 l_adjustment_sch_dt := sch_rec.first_draft_sch;
845 END LOOP;
846 END IF;
847
848 IF l_adjustment_sch_dt IS NULL THEN
849 FOR sch_rec IN get_first_draft_sch LOOP
850 l_adjustment_sch_dt := sch_rec.first_draft_sch;
851 g_norm_item_tbl(0).schedule_date := l_adjustment_sch_dt;
852 g_norm_item_tbl(0).schedule_id := sch_rec.pay_schd_id;
853 g_norm_item_tbl(0).normalized_amount := 0;
854 END LOOP;
855 END IF;
856
857 /* adjust the amount - if we find a draft schedule */
858 IF l_adjustment_sch_dt IS NOT NULL THEN
859 FOR i IN 0 .. g_norm_item_tbl.COUNT - 1 LOOP
860 IF g_norm_item_tbl(i).schedule_date = l_adjustment_sch_dt THEN
861 g_norm_item_tbl(i).normalized_amount := g_norm_item_tbl(i).normalized_amount
862 + l_adjustment_amount;
863 EXIT;
864 END IF;
865 END LOOP;
866 END IF;
867
868 /* start to create/update normalized items */
869 FOR i IN 0 .. g_norm_item_tbl.COUNT - 1 LOOP
870
871 /* first try to update */
872 UPDATE pn_payment_items_all ppi
873 SET ppi.actual_amount = g_norm_item_tbl(i).normalized_amount,
874 ppi.export_currency_amount = g_norm_item_tbl(i).normalized_amount,
875 ppi.last_update_date = SYSDATE,
876 ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
877 ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
878 WHERE ppi.payment_schedule_id = g_norm_item_tbl(i).schedule_id
879 AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
880 AND ppi.payment_term_id = p_term_id;
881
882 IF NVL(SQL%ROWCOUNT, 0) = 0 THEN
883
884 FOR rec IN org_cur LOOP
885 l_org_id := rec.org_id;
886 END LOOP;
887
888 INSERT INTO pn_payment_items_all
889 (payment_item_id,
890 last_update_date,
891 last_updated_by,
892 creation_date,
893 created_by,
894 last_update_login,
895 actual_amount,
896 estimated_amount,
897 due_date,
898 payment_item_type_lookup_code,
899 payment_term_id,
900 payment_schedule_id,
901 period_fraction,
902 vendor_id,
903 customer_id,
904 vendor_site_id,
905 customer_site_use_id,
906 cust_ship_site_id,
907 set_of_books_id,
908 currency_code,
909 export_currency_code,
910 export_currency_amount,
911 rate,
912 org_id)
913 VALUES
914 (PN_PAYMENT_ITEMS_S.NEXTVAL,
915 SYSDATE,
916 NVL(fnd_profile.value('USER_ID'),0),
917 SYSDATE,
918 NVL(fnd_profile.value('USER_ID'),0),
919 NVL(fnd_profile.value('LOGIN_ID'),0),
920 g_norm_item_tbl(i).normalized_amount,
921 NULL,
922 g_norm_item_tbl(i).schedule_date,
923 'NORMALIZED',
924 p_term_id,
925 g_norm_item_tbl(i).schedule_id,
926 1,
927 p_vendor_id,
928 p_cust_id,
929 p_vendor_site_id,
930 p_cust_site_use_id,
931 p_cust_ship_site_id,
932 p_sob_id,
933 p_curr_code,
934 p_curr_code,
935 g_norm_item_tbl(i).normalized_amount,
936 p_rate,
937 l_org_id
938 );
939 END IF;
940
941
942 /*S.N. Bug 4956314 */
943
944 l_cash_item:=0;
945
946 FOR t_rec IN check_cash_item(g_norm_item_tbl(i).schedule_id,p_term_id)
947 LOOP
948 l_cash_item:=1;
949 END LOOP;
950
951 IF l_cash_item = 0 THEN
952 pnp_debug_pkg.log(' PN_NORM_RENORM_PKG.RENORMALIZE_ACROSS_ALL_DRAFT -> create $ 0 cash items(+)');
953 pnp_debug_pkg.log(' PN_NORM_RENORM_PKG.RENORMALIZE_ACROSS_ALL_DRAFT -> for schedule id : '||g_norm_item_tbl(i).schedule_id);
954 pn_schedules_items.create_cash_items
955 (p_est_amt => 0,
956 p_act_amt => 0,
957 p_sch_dt => g_norm_item_tbl(i).schedule_date,
958 p_sch_id => g_norm_item_tbl(i).schedule_id,
959 p_term_id => p_term_id,
960 p_vendor_id => p_vendor_id,
961 p_cust_id => p_cust_id,
962 p_vendor_site_id => p_vendor_site_id,
963 p_cust_site_use_id => p_cust_site_use_id,
964 p_cust_ship_site_id => p_cust_ship_site_id,
965 p_sob_id => p_sob_id,
966 p_curr_code => p_curr_code,
967 p_rate => p_rate);
968 pnp_debug_pkg.log(' PN_NORM_RENORM_PKG.RENORMALIZE_ACROSS_ALL_DRAFT -> $ 0 cash item created(-) ');
969 END IF;
970
971 /*E.N. Bug 4956314 */
972
973 END LOOP;
974
975 /* update the table */
976 UPDATE pn_payment_terms_all
977 SET event_type_code = p_lease_context,
978 norm_start_date = p_norm_str_dt,
979 norm_end_date = p_norm_end_dt
980 WHERE normalize ='Y'
981 AND payment_term_id = p_term_id;
982
983 pnp_debug_pkg.log(' PN_NORM_RENORM_PKG.RENORMALIZE_IN_FIRST_DRAFT (-) ');
984 EXCEPTION
985 WHEN OTHERS THEN
986 pnp_debug_pkg.log(' RENORMALIZE_IN_FIRST_DRAFT - OTHERS ERROR ... ' || sqlerrm);
987
988 END RENORMALIZE_IN_FIRST_DRAFT;
989
990 /*------------------------------------------------------------------------------
991 NAME : NORMALIZE_RENORMALIZE
992 DESCRIPTION : This is the main procedure for this package. This will handle the
993 normalization/renormalization. Depending on the profile option
994 the appropriate sub procedure is called.
995 HISTORY :
996 20-OCT-04 atuppad o Created
997 23-NOV-05 pikhar o Passed org_id in pn_mo_cache_utils.get_profile_value
998 20-JAN-05 hkulkarn o Bug4956314 : Using Outer Join. Here DRAFT schedules were not
999 picked up because there didn't exist any Items.
1000 Also creating $ 0 'CASH' Items, while creating/adjusting,
1001 NORMALized items for New adjustmetn schedule, if 'CASH'
1002 items doesn't exists for this schedule initially.
1003 24-AUG-08 rkartha o Bug#6829173 : Added code for handling the case where the term
1004 completely falls outside the new lease duration
1005 when early terminating.
1006 07-feb-10 asahoo 0 Bug#13486716: Modified the cursor GET_TERM_SCHEDULES, added PN_SCHEDULES_ITEMS.FIRST_DAY for l_amd_comn_date
1007 If p_norm_str_dt and g_new_lea_term_dt have same month and year, we will pass
1008 PN_SCHEDULES_ITEMS.FIRST_DAY(p_norm_str_dt) to RENORMALIZE_ACROSS_ALL_DRAFT
1009 ------------------------------------------------------------------------------*/
1010 PROCEDURE NORMALIZE_RENORMALIZE
1011 (p_lease_context IN VARCHAR2,
1012 p_lease_id IN NUMBER,
1013 p_term_id IN NUMBER,
1014 p_vendor_id IN NUMBER,
1015 p_cust_id IN NUMBER,
1016 p_vendor_site_id IN NUMBER,
1017 p_cust_site_use_id IN NUMBER,
1018 p_cust_ship_site_id IN NUMBER,
1019 p_sob_id IN NUMBER,
1020 p_curr_code IN VARCHAR2,
1021 p_sch_day IN NUMBER,
1022 p_norm_str_dt IN DATE,
1023 p_norm_end_dt IN DATE,
1024 p_rate IN NUMBER,
1025 p_lease_change_id IN NUMBER)
1026 IS
1027
1028 l_amd_comn_date DATE; /* 6838211 */
1029 /* get the details of draft schedules between norm start and end dates*/
1030 -- Bug#6829173
1031 CURSOR GET_TERM_SCHEDULES(c_norm_str_dt pn_payment_terms_all.norm_start_date%TYPE) IS
1032 SELECT pps.schedule_date schedule_date,
1033 pps.payment_schedule_id payment_schedule_id
1034 FROM pn_payment_schedules_all pps,
1035 pn_payment_items_all ppi
1036 WHERE pps.lease_id = p_lease_id
1037 AND pps.schedule_date BETWEEN
1038 NVL(PN_SCHEDULES_ITEMS.FIRST_DAY(l_amd_comn_date),PN_SCHEDULES_ITEMS.FIRST_DAY(c_norm_str_dt)) /*6838211 */
1039 AND LAST_DAY(g_new_lea_term_dt) --AND LAST_DAY(p_norm_end_dt) /*Bug4956314*/
1040 AND pps.payment_status_lookup_code = 'DRAFT' /* bug 6737971 removed
1041 ON_HOLD */
1042 AND TO_CHAR(pps.schedule_date,'DD') = p_sch_day
1043 AND ppi.PAYMENT_SCHEDULE_ID(+) = pps.PAYMENT_SCHEDULE_ID /*Bug4956314*/
1044 AND ppi.PAYMENT_TERM_ID(+) = p_term_id /*Bug4956314*/
1045 AND ppi.PAYMENT_ITEM_TYPE_LOOKUP_CODE(+) = 'CASH' /*Bug4956314*/
1046 AND ppi.LAST_ADJUSTMENT_TYPE_CODE IS NULL
1047 ORDER BY pps.schedule_date;
1048
1049 CURSOR GET_LEASE_DETAILS IS
1050 SELECT pld.lease_commencement_date lease_commencement_date,
1051 pld.lease_termination_date new_lease_term_date,
1052 pl.payment_term_proration_rule pr_rule
1053 FROM pn_leases_all pl,
1054 pn_lease_details_all pld
1055 WHERE pl.lease_id = p_lease_id
1056 AND pld.lease_id = pl.lease_id;
1057
1058 l_counter NUMBER;
1059 l_system_options VARCHAR2(5);
1060
1061 CURSOR org_cur IS
1062 SELECT org_id
1063 FROM pn_payment_terms_all
1064 WHERE payment_term_id = p_term_id;
1065
1066 l_org_id NUMBER;
1067
1068 -- Bug#6829173
1069 l_lease_comm_date pn_lease_details_all.lease_commencement_date%TYPE;
1070 l_norm_str_dt pn_payment_terms_all.norm_start_date%TYPE;
1071 lp_norm_str_dt pn_payment_terms_all.norm_start_date%TYPE := NULL;
1072
1073
1074 BEGIN
1075 pnp_debug_pkg.log('PN_NORM_RENORM_PKG.NORMALIZE_RENORMALIZE (+) ');
1076
1077 SELECT change_commencement_date
1078 INTO l_amd_comn_date
1079 FROM
1080 (SELECT *
1081 FROM pn_lease_changes_all
1082 WHERE lease_id = p_lease_id
1083 ORDER BY lease_change_id DESC)
1084 WHERE rownum < 2;
1085
1086 IF l_amd_comn_date IS NULL
1087 THEN
1088 SELECT lease_commencement_date
1089 INTO l_amd_comn_date
1090 FROM pn_lease_details_all
1091 WHERE lease_id = p_lease_id;
1092 END IF;
1093
1094 IF l_amd_comn_date > p_norm_str_dt THEN /* 6838211 */
1095 l_amd_comn_date := p_norm_str_dt;
1096 END IF;
1097
1098 FOR rec IN org_cur LOOP
1099 l_org_id := rec.org_id;
1100 END LOOP;
1101
1102 /* initialize variables */
1103 l_system_options := NVL(PN_MO_CACHE_UTILS.get_profile_value ('PN_RENORM_ACC_ALL_DRAFT_SCH',l_org_id),'Y');
1104 l_counter := 0;
1105 g_norm_item_tbl.DELETE;
1106 g_new_lea_term_dt := NULL;
1107 g_pr_rule := NULL;
1108
1109 /*S.N. Bug4956314*/
1110 /* get the lease detail values */
1111 FOR lease_details_rec IN get_lease_details LOOP
1112 -- Bug 6508394
1113 l_lease_comm_date := lease_details_rec.lease_commencement_date;
1114
1115 g_new_lea_term_dt := lease_details_rec.new_lease_term_date;
1116 g_pr_rule := lease_details_rec.pr_rule;
1117 END LOOP;
1118 /*E.N. Bug4956314*/
1119
1120 -- Bug 6829173
1121 l_norm_str_dt := NVL(l_amd_comn_date,p_norm_str_dt); /* 6838211 */
1122 -- Early termination (Term falls outside the new lease duration)
1123 IF p_norm_str_dt > NVL(p_norm_end_dt, g_new_lea_term_dt) THEN
1124 l_norm_str_dt := NVL(l_amd_comn_date,l_lease_comm_date); /* 6838211 */
1125 END IF;
1126
1127 -- Bug 6829173
1128 FOR sch_rec IN get_term_schedules(l_norm_str_dt) LOOP
1129 g_norm_item_tbl(l_counter).schedule_date := sch_rec.schedule_date;
1130 g_norm_item_tbl(l_counter).schedule_id := sch_rec.payment_schedule_id;
1131 l_counter := l_counter + 1;
1132 END LOOP;
1133 pnp_debug_pkg.log(' N-RN COUNTER' || l_counter);
1134 /* donot renormalize if no original draft schedule is not found */
1135 IF (NVL(g_norm_item_tbl.COUNT, 0) <> 0) OR (l_system_options = 'N') THEN
1136
1137 /* call appropriate sub procedure depending upon the system option */
1138 IF l_system_options = 'Y' THEN
1139
1140 --Fix for bug#13486716
1141 IF (p_norm_str_dt IS NOT NULL AND g_new_lea_term_dt IS NOT NULL
1142 AND TO_CHAR(p_norm_str_dt,'MMYY') = TO_CHAR(g_new_lea_term_dt,'MMYY'))
1143 THEN
1144 lp_norm_str_dt := PN_SCHEDULES_ITEMS.FIRST_DAY(p_norm_str_dt);
1145 ELSE
1146 lp_norm_str_dt := p_norm_str_dt;
1147 END IF;
1148
1149 pnp_debug_pkg.log('p_norm_end_dt ' || p_norm_end_dt);
1150
1151 RENORMALIZE_ACROSS_ALL_DRAFT
1152 (p_lease_context => p_lease_context,
1153 p_lease_id => p_lease_id,
1154 p_term_id => p_term_id,
1155 p_vendor_id => p_vendor_id,
1156 p_cust_id => p_cust_id,
1157 p_vendor_site_id => p_vendor_site_id,
1158 p_cust_site_use_id => p_cust_site_use_id,
1159 p_cust_ship_site_id => p_cust_ship_site_id,
1160 p_sob_id => p_sob_id,
1161 p_curr_code => p_curr_code,
1162 p_sch_day => p_sch_day,
1163 p_norm_str_dt => lp_norm_str_dt,
1164 p_norm_end_dt => g_new_lea_term_dt,
1165 p_rate => p_rate);
1166
1167 ELSIF l_system_options = 'N' THEN
1168 RENORMALIZE_IN_FIRST_DRAFT
1169 (p_lease_context => p_lease_context,
1170 p_lease_id => p_lease_id,
1171 p_term_id => p_term_id,
1172 p_vendor_id => p_vendor_id,
1173 p_cust_id => p_cust_id,
1174 p_vendor_site_id => p_vendor_site_id,
1175 p_cust_site_use_id => p_cust_site_use_id,
1176 p_cust_ship_site_id => p_cust_ship_site_id,
1177 p_sob_id => p_sob_id,
1178 p_curr_code => p_curr_code,
1179 p_sch_day => p_sch_day,
1180 p_norm_str_dt => p_norm_str_dt,
1181 p_norm_end_dt => g_new_lea_term_dt,
1182 p_rate => p_rate);
1183
1184 END IF;
1185
1186 END IF;
1187
1188 pnp_debug_pkg.log('PN_NORM_RENORM_PKG.NORMALIZE_RENORMALIZE (-) ');
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 pnp_debug_pkg.log(' NORMALIZE_RENORMALIZE - OTHERS ERROR ... ' || sqlerrm);
1192
1193 END NORMALIZE_RENORMALIZE;
1194
1195
1196 END PN_NORM_RENORM_PKG;