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