[Home] [Help]
51: WHERE ppt.lease_id = p_lease_id
52: AND ppt.var_rent_inv_id IS NULL
53: AND ppt.period_billrec_id IS NULL
54: AND EXISTS (SELECT NULL
55: FROM pn_payment_items_all ppi
56: WHERE ppt.lease_id = p_lease_id
57: AND ppi.payment_term_id = ppt.payment_term_id)
58: UNION
59: SELECT ppt.payment_term_id,
82: AND ppt.index_period_id IS NOT NULL
83: AND ppt.status = 'APPROVED'
84: AND ppt.index_term_indicator NOT IN ('BACKBILL','ATLEAST-BACKBILL')
85: AND EXISTS (SELECT NULL
86: FROM pn_payment_items_all ppi
87: WHERE ppt.lease_id = p_lease_id
88: AND ppi.payment_term_id = ppt.payment_term_id);
89:
90:
121: FROM pn_payment_terms_all ppt
122: WHERE ppt.lease_id = p_lease_id
123: AND ppt.payment_term_id = p_payment_term_id
124: AND EXISTS (SELECT NULL
125: FROM pn_payment_items_all ppi
126: WHERE ppt.lease_id = p_lease_id
127: AND ppi.payment_term_id = ppt.payment_term_id)
128: FOR UPDATE;
129:
168: WHERE ppt.lease_id = p_lease_id
169: AND NVL(ppt.normalize,'N') = 'Y'
170: AND NVL(ppt.status,'APPROVED') = 'APPROVED'
171: AND EXISTS (SELECT NULL
172: FROM pn_payment_items_all ppi
173: WHERE ppt.lease_id = p_lease_id
174: AND ppi.payment_term_id = ppt.payment_term_id);
175:
176: --------------------------------------------------------------------------------------------
211: AND ppt.var_rent_inv_id IS NULL
212: AND ppt.period_billrec_id IS NULL
213: AND (NVL(ppt.normalize,'N') = 'Y' AND NVL(ppt.norm_end_date, ppt.end_date) > p_cutoff_date) /* 6699877 */
214: AND EXISTS (SELECT NULL
215: FROM pn_payment_items_all ppi
216: WHERE ppt.lease_id = p_lease_id
217: AND ppi.payment_term_id = ppt.payment_term_id)
218: UNION
219: SELECT ppt.payment_term_id,
244: AND ppt.index_term_indicator NOT IN ('BACKBILL','ATLEAST-BACKBILL')
245: AND (( ppt.normalize = 'Y' AND NVL(ppt.norm_end_date, ppt.end_date) > p_cutoff_date) OR
246: ( NVL(ppt.normalize,'N') = 'N' AND ppt.end_date > p_cutoff_date))
247: AND EXISTS (SELECT NULL
248: FROM pn_payment_items_all ppi
249: WHERE ppt.lease_id = p_lease_id
250: AND ppi.payment_term_id = ppt.payment_term_id);
251:
252: --------------------------------------------------------------------------------------------
295: AND ppt.var_rent_inv_id IS NULL
296: AND ppt.period_billrec_id IS NULL
297: AND plc.change_type_lookup_code IN ('EDIT', 'AMEND')
298: AND NOT EXISTS (SELECT NULL
299: FROM pn_payment_items_all ppi
300: WHERE ppt.lease_id = p_lease_id
301: AND ppi.payment_term_id = ppt.payment_term_id);
302:
303: --------------------------------------------------------------------------------------------
334: ppt.norm_start_date
335: FROM pn_payment_terms_all ppt
336: WHERE ppt.payment_term_id = p_term_id
337: AND NOT EXISTS (SELECT NULL
338: FROM pn_payment_items_all ppi
339: WHERE ppt.lease_id = p_lease_id
340: AND ppi.payment_term_id = p_term_id);
341:
342: --------------------------------------------------------------------------------------------
376: AND ppt.index_period_id IS NULL
377: AND ppt.var_rent_inv_id IS NULL
378: AND ppt.period_billrec_id IS NULL
379: AND NOT EXISTS (SELECT NULL
380: FROM pn_payment_items_all ppi
381: WHERE ppt.lease_id = p_lease_id
382: AND ppi.payment_term_id = ppt.payment_term_id);
383:
384: --------------------------------------------------------------------------------------------
417: FROM pn_payment_terms_all ppt
418: WHERE ppt.lease_id = p_lease_id
419: AND ppt.changed_flag = 'Y'
420: AND EXISTS (SELECT NULL
421: FROM pn_payment_items_all ppi
422: WHERE ppt.lease_id = p_lease_id
423: AND ppi.payment_term_id = ppt.payment_term_id)
424: FOR UPDATE;
425:
459:
460: --Fix for bug#14143342
461: CURSOR leave_alone_amt(p_lease_id Number) IS
462: SELECT NVL(SUM(ppi.actual_amount),0)
463: FROM pn_payment_items_all ppi,
464: pn_payment_schedules_all pps,
465: pn_payment_terms_all ppt
466: WHERE ppt.payment_term_id = ppi.payment_term_id
467: AND ppt.lease_id = p_lease_id
490: ppi.org_id,
491: ppi.grouping_rule_id,
492: sum(ppi.actual_amount) actual_amount,
493: sum(nvl(ppi.export_currency_amount,0)) export_currency_amount
494: FROM pn_payment_items_all ppi,
495: pn_payment_schedules_all pps,
496: pn_payment_terms_all ppt
497: WHERE ppt.payment_term_id = ppi.payment_term_id
498: AND ppt.lease_id = p_lease_id
549: sum(decode(payment_item_type_lookup_code,'NORMALIZED',b.actual_amount,0)) diff_act_amt,
550: sum(decode(payment_item_type_lookup_code,'CASH',b.export_currency_amount,0)) -
551: sum(decode(payment_item_type_lookup_code,'NORMALIZED',b.export_currency_amount,0)) diff_exp_amt
552: FROM pn_payment_terms_all a,
553: pn_payment_items_all b,
554: pn_leases_all c
555: WHERE a.payment_term_id = b.payment_term_id
556: AND a.normalize = 'Y'
557: AND a.lease_id = c.lease_id
567: CURSOR get_payment_item(p_lease_id Number, p_payment_term_id Number) IS
568: SELECT MAX(c.payment_item_id)
569: FROM pn_payment_terms_all a,
570: pn_payment_schedules_all b,
571: pn_payment_items_all c
572: WHERE a.lease_id = p_lease_id
573: AND a.payment_term_id = p_payment_term_id
574: AND a.normalize = 'Y'
575: AND a.payment_term_id = c.payment_term_id
621: pnp_debug_pkg.log('Actual Diff Export Currency Amt ' || term_rec.diff_exp_amt);
622:
623: IF ((lp_payment_item_id <> 0) AND (term_rec.diff_act_amt <> 0 OR term_rec.diff_exp_amt <> 0)) THEN
624:
625: UPDATE pn_payment_items_all
626: SET actual_amount = actual_amount + term_rec.diff_act_amt,
627: export_currency_amount = export_currency_amount + term_rec.diff_exp_amt
628: WHERE payment_item_id = lp_payment_item_id;
629:
699: l_due_date DATE;
700:
701: CURSOR first_item_cursor IS
702: SELECT MIN(due_date)
703: FROM pn_payment_items_all
704: WHERE payment_term_id = p_payment_term_id
705: AND payment_item_type_lookup_code = 'CASH';
706:
707: BEGIN
861: CURSOR lst_sch_dt_cur (p_term_id NUMBER)
862: IS
863: SELECT MAX(pps.schedule_date), COUNT(pps.schedule_date)
864: FROM pn_payment_schedules_all pps,
865: pn_payment_items_all ppi
866: WHERE ppi.payment_term_id = p_term_id
867: AND pps.payment_schedule_id = ppi.payment_schedule_id
868: AND ppi.actual_amount <> 0
869: AND ppi.payment_item_type_lookup_code = 'CASH';
1701: WHERE lease_id = p_lease_id;
1702:
1703: CURSOR sched_exists(p_payment_term_id NUMBER,p_sch_dt DATE) IS
1704: SELECT sched.payment_schedule_id, payment_status_lookup_code
1705: FROM pn_payment_items_all item,
1706: pn_payment_schedules_all sched
1707: WHERE item.payment_term_id = p_payment_term_id
1708: AND item.payment_schedule_id = sched.payment_schedule_id
1709: AND sched.schedule_date = p_sch_dt;
1853: -- 25-JUN-2001 created mmisra
1854: -- 26-APR-2002 modified ftanudja o added p_rate
1855: -- 15-JUL-05 hareesha o Bug 4284035 - Replaced pn_payment_items with _ALL table.
1856: -- 09-JAN-07 Hareesha o M28#16 for Recurring backbills, populate the due_date as
1857: -- calculation date into pn_payment_items_all
1858: -- 24-APR-07 Hareesha o Bug #5899113 Default inv-grping rule set at term-level to
1859: -- items.
1860: --------------------------------------------------------------------------------------------
1861: PROCEDURE create_cash_items (p_est_amt NUMBER,
1921: FOR rec IN get_inv_grp_rule LOOP
1922: l_inv_grp_rule := rec.grouping_rule_id;
1923: END LOOP;
1924:
1925: INSERT INTO pn_payment_items_all
1926: (payment_item_id,
1927: last_update_date,
1928: last_updated_by,
1929: creation_date,
2029: -- l_monthly_norm_amt := l_total_cash_amt - l_la_amt.
2030: -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_payment_items with _ALL.
2031: -- 24-NOV-05 Kiran o Round amounts befor insert/uptdate into terms OR items.
2032: -- 09-JAN-07 Hareesha o M28#16 for Recurring backbills, populate the due_date as
2033: -- calculation date into pn_payment_items_all
2034: -- 24-APR-07 Hareesha o Bug #5899113 Default inv-grping rule set at term-level to
2035: -- items.
2036: -- 24-AUG-08 RKARTHA o Bug 6829173 - When finding the number of approved schedules,
2037: -- exclude those approved schedules for which on the same
2069: p_rate NUMBER,
2070: p_lease_change_id NUMBER)
2071: IS
2072:
2073: l_total_cash_amt pn_payment_items_all.actual_amount%TYPE;
2074: l_la_amt NUMBER;
2075: l_norm_amt NUMBER;
2076: l_norm_sch_dt DATE;
2077: l_precision NUMBER;
2120: -- Added distinct clause for bug#12728421, the below cursor was returning duplicate schedule dates in case multiple approved
2121: -- schedules exist for the same period
2122: CURSOR get_drf_sch_date(p_term_id NUMBER) IS
2123: SELECT distinct schedule_date
2124: FROM pn_payment_items_all ppi,
2125: pn_payment_schedules_all pps
2126: WHERE ppi.payment_term_id = p_term_id
2127: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2128: AND pps.payment_schedule_id = ppi.payment_schedule_id
2132: pn_lease_details_all where lease_id = p_lease_id ); /* 7149537 */
2133:
2134: CURSOR get_drf_sch(l_norm_sch_dt DATE) IS
2135: select distinct ppi.payment_schedule_id
2136: from pn_payment_items_all ppi
2137: ,pn_payment_schedules_all pps
2138: where exists
2139: (select 1
2140: from pn_payment_items_all ppi , pn_payment_schedules_all pps
2136: from pn_payment_items_all ppi
2137: ,pn_payment_schedules_all pps
2138: where exists
2139: (select 1
2140: from pn_payment_items_all ppi , pn_payment_schedules_all pps
2141: where ppi.payment_term_id = p_term_id
2142: and pps.payment_schedule_id = ppi.payment_schedule_id
2143: and payment_status_lookup_code <> 'DRAFT'
2144: and due_date = l_norm_sch_dt
2200: END IF;
2201:
2202: Select NVL(SUM(ppi.actual_amount),0)
2203: into l_term_amt
2204: FROM pn_payment_items_all ppi,
2205: pn_payment_schedules_all pps
2206: WHERE ppi.payment_term_id = p_term_id
2207: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2208: AND pps.payment_schedule_id = ppi.payment_schedule_id
2220: pnp_debug_pkg.log('create_normalize_items - l_amd_comn_date1 :=: '||TO_CHAR(l_amd_comn_date));
2221:
2222: SELECT NVL(SUM(ppi.actual_amount),0)
2223: INTO l_app_amt
2224: FROM pn_payment_items_all ppi,
2225: pn_payment_schedules_all pps
2226: WHERE ppi.payment_term_id = p_term_id
2227: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2228: AND pps.payment_schedule_id = ppi.payment_schedule_id
2241: --Fix for bug#14143342
2242: -- Draft Schedules should not be deleted before ACD
2243: SELECT NVL(SUM(ppi.actual_amount),0)
2244: INTO l_dft_amt
2245: FROM pn_payment_items_all ppi,
2246: pn_payment_schedules_all pps
2247: WHERE ppi.payment_term_id = p_term_id
2248: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2249: AND pps.payment_schedule_id = ppi.payment_schedule_id
2269: pnp_debug_pkg.log('create_normalize_items IN: p_curr_code : '||p_curr_code);
2270:
2271: SELECT SUM(ppi.actual_amount)
2272: INTO l_total_cash_amt
2273: FROM pn_payment_items_all ppi
2274: WHERE ppi.payment_term_id = p_term_id
2275: AND ppi.payment_item_type_lookup_code = 'CASH';
2276:
2277: pnp_debug_pkg.log('create_normalize_items - total cash : '||TO_CHAR(l_total_cash_amt));
2293: ELSIF p_lease_context IN ('CON','EXP','CONTERM') THEN
2294:
2295: SELECT NVL(SUM(ppi.actual_amount),0)
2296: INTO l_la_amt
2297: FROM pn_payment_items_all ppi,
2298: pn_payment_schedules_all pps
2299: WHERE ppi.payment_term_id = p_term_id
2300: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2301: AND pps.payment_schedule_id = ppi.payment_schedule_id
2321: pnp_debug_pkg.log('create_normalize_items - p_norm_str_dt :=: '||TO_CHAR(p_norm_str_dt));
2322:
2323: SELECT LEAST(p_norm_end_dt, NVL(LAST_DAY(MAX(pps.schedule_date)), p_norm_end_dt))
2324: INTO l_norm_end_dt
2325: FROM pn_payment_items_all ppi,
2326: pn_payment_schedules_all pps
2327: WHERE ppi.payment_term_id = p_term_id
2328: AND pps.payment_schedule_id = ppi.payment_schedule_id
2329: AND pps.payment_status_lookup_code = 'DRAFT';
2331: pnp_debug_pkg.log('l_norm_end_dt : '||TO_CHAR(l_norm_end_dt));
2332:
2333: SELECT COUNT(*)
2334: INTO l_app_sch
2335: FROM pn_payment_items_all ppi,
2336: pn_payment_schedules_all pps
2337: WHERE ppi.payment_term_id = p_term_id
2338: AND pps.payment_schedule_id = ppi.payment_schedule_id
2339: AND pps.payment_status_lookup_code = 'APPROVED'
2341: AND pps.schedule_date between First_Day(p_norm_str_dt) and LAST_DAY(l_norm_end_dt)
2342: AND NOT EXISTS (
2343: SELECT 1
2344: FROM pn_payment_schedules_all ppsi,
2345: pn_payment_items_all ppii
2346: WHERE ppsi.schedule_date = pps.schedule_date
2347: AND ppii.payment_term_id = ppi.payment_term_id
2348: AND ppsi.payment_schedule_id = ppii.payment_schedule_id
2349: AND ppsi.payment_status_lookup_code = 'DRAFT'
2437:
2438: IF p_lease_context IN ('EXP', 'CONTERM') THEN
2439:
2440: --Fix for bug#14143342
2441: DELETE pn_payment_items_all ppi
2442: WHERE ppi.payment_schedule_id IN (SELECT pps.payment_schedule_id
2443: FROM pn_payment_items_all ppi1,
2444: pn_payment_schedules_all pps
2445: WHERE ppi1.payment_term_id = p_term_id
2439:
2440: --Fix for bug#14143342
2441: DELETE pn_payment_items_all ppi
2442: WHERE ppi.payment_schedule_id IN (SELECT pps.payment_schedule_id
2443: FROM pn_payment_items_all ppi1,
2444: pn_payment_schedules_all pps
2445: WHERE ppi1.payment_term_id = p_term_id
2446: AND ppi1.payment_item_type_lookup_code = 'CASH'
2447: AND pps.payment_schedule_id = ppi1.payment_schedule_id
2537: if l_norm_sch_dt = l_sch_date_1 THEN
2538:
2539: SELECT NVL(sum(actual_amount),0) /* 9457938 */
2540: into l_act_amt
2541: FROM pn_payment_items_all ppi,
2542: pn_payment_schedules_all pps
2543: WHERE ppi.payment_item_type_lookup_code = 'NORMALIZED'
2544: AND pps.payment_schedule_id = ppi.payment_schedule_id
2545: AND ppi.payment_term_id = p_term_id
2562: begin -- Bug 7149537
2563: select 1 /* 7149537 */
2564: into l_draft_exist
2565: from dual
2566: where exists(select * from pn_payment_items_all
2567: where due_date = l_norm_sch_dt
2568: and payment_term_id = p_term_id
2569: and payment_item_type_lookup_code = 'CASH'
2570: and payment_schedule_id = l_sch_id);
2593:
2594: END IF;
2595:
2596: /* NORMALIZED */
2597: INSERT INTO pn_payment_items_all
2598: (payment_item_id,
2599: last_update_date,
2600: last_updated_by,
2601: creation_date,
2656: pnp_debug_pkg.log('create_normalize_items - l_sch_id_1 : '||TO_CHAR(l_sch_id_1));
2657:
2658: SELECT NVL(sum(actual_amount),0)
2659: into l_act_amt
2660: FROM pn_payment_items_all ppi,
2661: pn_payment_schedules_all pps
2662: WHERE ppi.payment_item_type_lookup_code = 'NORMALIZED'
2663: AND pps.payment_schedule_id = ppi.payment_schedule_id
2664: AND ppi.payment_term_id = p_term_id
2667:
2668:
2669: l_prec_norm_amt := ((-1 * nvl(l_act_amt,0)) + l_prec_norm_amt);
2670:
2671: UPDATE pn_payment_items_all ppi
2672: SET ppi.actual_amount = l_prec_norm_amt,
2673: ppi.export_currency_amount = l_prec_norm_amt,
2674: ppi.last_update_date = SYSDATE,
2675: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2694:
2695: l_norm_amt := l_monthly_norm_amt;
2696: l_prec_norm_amt := ROUND(l_norm_amt,l_precision);
2697:
2698: UPDATE pn_payment_items_all ppi
2699: SET ppi.actual_amount = l_prec_norm_amt,
2700: ppi.export_currency_amount = l_prec_norm_amt,
2701: ppi.last_update_date = SYSDATE,
2702: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2702: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2703: ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
2704: WHERE ppi.payment_schedule_id IN (SELECT pps.payment_schedule_id
2705: FROM pn_payment_schedules_all pps,
2706: pn_payment_items_all ppi1
2707: WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
2708: AND pps.payment_status_lookup_code = 'DRAFT'
2709: AND ppi1.payment_term_id = p_term_id)
2710: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2725: l_norm_sch_dt := TO_DATE(TO_CHAR(p_sch_day)||'/'||TO_CHAR(p_norm_str_dt,'MM/YYYY')
2726: ,'DD/MM/YYYY');
2727: l_tot_cum_norm_amt := l_tot_cum_norm_amt - l_prec_norm_amt_upd + l_prec_norm_amt;
2728:
2729: UPDATE pn_payment_items_all ppi
2730: SET ppi.actual_amount = l_prec_norm_amt,
2731: ppi.export_currency_amount = l_prec_norm_amt,
2732: ppi.last_update_date = SYSDATE,
2733: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2733: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2734: ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
2735: WHERE ppi.payment_schedule_id = (SELECT pps.payment_schedule_id
2736: FROM pn_payment_schedules_all pps,
2737: pn_payment_items_all ppi1
2738: WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
2739: AND pps.payment_status_lookup_code = 'DRAFT'
2740: AND ppi1.payment_term_id = p_term_id
2741: AND ppi1.payment_item_type_lookup_code = 'NORMALIZED'
2759: l_tot_cum_diff_amt := (l_total_cash_amt - l_la_amt) - l_tot_cum_norm_amt;
2760: l_prec_norm_amt := ROUND(l_prec_norm_amt + l_tot_cum_diff_amt, l_precision);
2761:
2762: IF l_partial_end_flag = 'Y' OR l_tot_cum_diff_amt <> 0 THEN
2763: UPDATE pn_payment_items_all ppi
2764: SET ppi.actual_amount = l_prec_norm_amt,
2765: ppi.export_currency_amount = l_prec_norm_amt,
2766: ppi.last_update_date = SYSDATE,
2767: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2767: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2768: ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
2769: WHERE ppi.payment_schedule_id = (SELECT pps.payment_schedule_id
2770: FROM pn_payment_schedules_all pps,
2771: pn_payment_items_all ppi1
2772: WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
2773: AND pps.payment_status_lookup_code = 'DRAFT'
2774: AND ppi1.payment_term_id = p_term_id
2775: AND ppi1.payment_item_type_lookup_code = 'NORMALIZED'
3468: nvl(sum(export_currency_amount),0)
3469: into l_cash_act_amt_appr,
3470: l_cash_est_amt_appr,
3471: l_cash_exp_amt_appr
3472: from pn_payment_items_all ppi,
3473: pn_payment_schedules_all pps
3474: where pps.lease_id = g_lease_id
3475: and pps.payment_status_lookup_code = 'APPROVED'
3476: and pps.payment_schedule_id = ppi.payment_schedule_id
3479: and pps.schedule_date = p_schedule_dt;
3480:
3481:
3482:
3483: UPDATE pn_payment_items_all
3484: SET estimated_amount = l_cash_est_amt - l_cash_est_amt_appr, --Bug 9150650
3485: actual_amount = l_cash_act_amt - l_cash_act_amt_appr,
3486: export_currency_amount = l_cash_act_amt - l_cash_exp_amt_appr,
3487: last_update_date = SYSDATE,
3487: last_update_date = SYSDATE,
3488: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
3489: last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
3490: WHERE payment_item_id = (SELECT ppi1.payment_item_id
3491: FROM pn_payment_items_all ppi1,
3492: pn_payment_schedules_all pps
3493: WHERE ppi1.payment_term_id = p_term_id
3494: AND ppi1.payment_item_type_lookup_code = 'CASH'
3495: AND pps.payment_status_lookup_code in ('DRAFT','ON_HOLD') --Fix for bug#12427309
3554:
3555: CURSOR get_last_schedule (p_payment_term_id NUMBER) IS
3556: SELECT MAX(schedule_date)
3557: FROM pn_payment_schedules_all pps,
3558: pn_payment_items_all ppi
3559: WHERE ppi.payment_term_id = p_payment_term_id
3560: AND ppi.payment_item_type_lookup_code = 'CASH'
3561: AND ppi.actual_amount <> 0
3562: AND pps.payment_schedule_id = ppi.payment_schedule_id
4090: CLOSE get_old_lea_term_dt;
4091:
4092: pnp_debug_pkg.log('CON - MAIN - Deleting Payment Items');
4093:
4094: DELETE pn_payment_items_all
4095: WHERE payment_schedule_id IN (SELECT payment_schedule_id
4096: FROM pn_payment_schedules_all
4097: WHERE lease_id = p_lease_id
4098: AND schedule_date > p_new_lea_term_dt
4111: WHERE lease_id = p_lease_id
4112: AND schedule_date > p_new_lea_term_dt
4113: AND payment_status_lookup_code = 'DRAFT'
4114: AND NOT EXISTS (SELECT null
4115: FROM pn_payment_items_all pitm
4116: WHERE pitm.payment_schedule_id = psch.payment_schedule_id);
4117:
4118: DELETE pn_payment_items_all
4119: WHERE payment_term_id IN (SELECT payment_term_id
4114: AND NOT EXISTS (SELECT null
4115: FROM pn_payment_items_all pitm
4116: WHERE pitm.payment_schedule_id = psch.payment_schedule_id);
4117:
4118: DELETE pn_payment_items_all
4119: WHERE payment_term_id IN (SELECT payment_term_id
4120: FROM pn_payment_terms_all
4121: WHERE start_date > p_new_lea_term_dt
4122: AND lease_id = p_lease_id)
4282: l_max_item_end_dt DATE := TO_DATE('01/01/0001', 'MM/DD/YYYY');
4283: l_ri_end_dt DATE := TO_DATE('01/01/0001', 'MM/DD/YYYY');
4284: l_term_end_dt DATE := NULL;
4285: l_sch_dt_1 DATE;
4286: l_payment_schedule_id pn_payment_items_all.payment_schedule_id%TYPE;
4287: CURSOR get_old_lea_term_dt IS
4288: SELECT plh.lease_termination_date
4289: FROM pn_lease_details_history plh,
4290: pn_lease_details_all pld
4292: AND pld.lease_id = p_lease_id;
4293:
4294: CURSOR get_drf_sch_date(p_payment_term_id NUMBER) IS
4295: SELECT distinct schedule_date
4296: FROM pn_payment_items_all ppi,
4297: pn_payment_schedules_all pps
4298: WHERE ppi.payment_term_id = p_payment_term_id
4299: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
4300: AND pps.payment_schedule_id = ppi.payment_schedule_id
4389: pnp_debug_pkg.log('CONTRACTION_BY_ITM_END_DT - MAIN - Deleting Items');
4390: FOR i IN 1 .. l_item_end_dt_tbl.COUNT LOOP
4391: pnp_debug_pkg.log('+++++++++++Deleting All Items for term:'||l_item_end_dt_tbl(i).term_id||
4392: ' with schedule_date > ' ||l_item_end_dt_tbl(i).item_end_dt);
4393: DELETE pn_payment_items_all
4394: WHERE payment_schedule_id IN (SELECT payment_schedule_id
4395: FROM pn_payment_schedules_all
4396: WHERE lease_id = p_lease_id
4397: AND schedule_date > l_item_end_dt_tbl(i).item_end_dt
4409: WHERE lease_id = p_lease_id
4410: AND schedule_date > p_new_lea_term_dt
4411: AND payment_status_lookup_code <> 'APPROVED' /* 9547514 */
4412: AND NOT EXISTS (SELECT 1
4413: FROM pn_payment_items_all pitm
4414: WHERE pitm.payment_schedule_id = psch.payment_schedule_id);
4415:
4416: /* Delete payment terms for the lease which have term start date
4417: greater than lease termination date. */
4422: WHERE lease_id = p_lease_id
4423: AND start_date > p_new_lea_term_dt
4424: AND index_period_id IS NULL
4425: AND NOT EXISTS(SELECT 1
4426: FROM pn_payment_items_all item,
4427: pn_payment_schedules_all schd
4428: WHERE item.payment_term_id = term.payment_term_id
4429: AND item.payment_schedule_id = schd.payment_schedule_id
4430: AND schd.payment_status_lookup_code = 'APPROVED');
5012: FOR zero_item IN (SELECT cash.payment_item_id c,
5013: norm.payment_item_id n
5014: FROM
5015: (SELECT *
5016: FROM pn_payment_items_all
5017: WHERE payment_term_id = l_payment_term_rec.payment_term_id
5018: AND export_to_ap_flag IS NULL
5019: AND export_to_ar_flag IS NULL
5020: AND payment_item_type_lookup_code = 'CASH'
5020: AND payment_item_type_lookup_code = 'CASH'
5021: AND actual_amount = 0
5022: ) cash,
5023: (SELECT *
5024: FROM pn_payment_items_all
5025: WHERE payment_term_id = l_payment_term_rec.payment_term_id
5026: AND export_to_ap_flag IS NULL
5027: AND export_to_ar_flag IS NULL
5028: AND payment_item_type_lookup_code = 'NORMALIZED'
5031: WHERE cash.due_date = norm.due_date
5032: )
5033: LOOP
5034: DELETE
5035: FROM pn_payment_items_all
5036: WHERE payment_item_id IN (zero_item.c,zero_item.n);
5037: END LOOP;
5038:
5039: END IF;
5149: AND ppt.index_period_id IS NULL
5150: AND ppt.var_rent_inv_id IS NULL
5151: AND ppt.period_billrec_id IS NULL
5152: AND NOT EXISTS (SELECT NULL
5153: FROM pn_payment_items_all ppi
5154: WHERE ppt.lease_id = p_lease_id
5155: AND ppi.payment_term_id = ppt.payment_term_id)
5156: AND frequency_code = 'OT'
5157: AND NVL(normalize,'N') = 'N';
5467: p_term_end_dt DATE)
5468: IS
5469: SELECT MAX(pps.schedule_date)
5470: FROM pn_payment_schedules_all pps,
5471: pn_payment_items_all ppi
5472: WHERE pps.payment_schedule_id = ppi.payment_schedule_id
5473: AND ppi.payment_term_id = p_term_id
5474: AND ppi.actual_amount IS NOT NULL
5475: AND ppi.payment_item_type_lookup_code = 'CASH'
5556: END IF;
5557:
5558: IF pay_term_con_cur.normalize = 'Y' THEN
5559:
5560: UPDATE pn_payment_items_all
5561: SET estimated_amount = 0,
5562: actual_amount = 0,
5563: export_currency_amount = 0,
5564: last_update_date = SYSDATE,
5564: last_update_date = SYSDATE,
5565: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
5566: last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
5567: WHERE payment_item_id IN (SELECT ppi1.payment_item_id
5568: FROM pn_payment_items_all ppi1,
5569: pn_payment_schedules_all pps
5570: WHERE ppi1.payment_term_id = pay_term_con_cur.payment_term_id
5571: AND ppi1.payment_item_type_lookup_code = 'CASH'
5572: AND pps.payment_status_lookup_code IN ('DRAFT','ON_HOLD')
5576: pnp_debug_pkg.log('CONTRACT_PAY_TERM - Updated Cash Items Amt to 0. - Normalize');
5577:
5578: ELSE
5579:
5580: DELETE pn_payment_items_all ppi
5581: WHERE ppi.payment_term_id = pay_term_con_cur.payment_term_id
5582: AND ppi.payment_schedule_id IN (SELECT pps1.payment_schedule_id
5583: FROM pn_payment_schedules_all pps1,
5584: pn_payment_items_all ppi1
5580: DELETE pn_payment_items_all ppi
5581: WHERE ppi.payment_term_id = pay_term_con_cur.payment_term_id
5582: AND ppi.payment_schedule_id IN (SELECT pps1.payment_schedule_id
5583: FROM pn_payment_schedules_all pps1,
5584: pn_payment_items_all ppi1
5585: WHERE ppi1.payment_term_id = pay_term_con_cur.payment_term_id
5586: AND pps1.payment_schedule_id = ppi1.payment_schedule_id
5587: AND pps1.payment_status_lookup_code IN ('DRAFT','ON_HOLD')
5588: AND pps1.schedule_date > l_lst_cash_sch_dt);
5595: AND TO_NUMBER(TO_CHAR(pps1.schedule_date,'DD'))
5596: = pay_term_con_cur.schedule_day
5597: AND pps1.schedule_date > l_lst_cash_sch_dt
5598: AND NOT EXISTS (SELECT NULL
5599: FROM pn_payment_items_all ppi
5600: WHERE ppi.payment_schedule_id
5601: = pps1.payment_schedule_id
5602: )
5603: );
6068: l_exist_amount NUMBER;
6069:
6070: CURSOR existing_payment_item_cur (p_sch_id NUMBER) IS
6071: SELECT payment_item_id, actual_amount, estimated_amount
6072: FROM pn_payment_items_all
6073: WHERE payment_schedule_id = p_sch_id
6074: AND payment_term_id = p_payment_term_rec.payment_term_id
6075: AND payment_item_type_lookup_code = 'CASH';
6076:
6075: AND payment_item_type_lookup_code = 'CASH';
6076:
6077: CURSOR exist_appr_item_amt IS
6078: SELECT NVL(SUM(pi.actual_amount),0) amount
6079: FROM pn_payment_items_all pi,
6080: pn_payment_schedules_all ps
6081: WHERE pi.payment_term_id = p_payment_term_rec.payment_term_id
6082: AND pi.payment_schedule_id = ps.payment_schedule_id
6083: AND ps.schedule_date = l_sch_dt
6264: pnp_debug_pkg.log('EXTEND_PAYMENT_TERM - existing_items_rec.actual_amount: ' ||TO_CHAR(existing_items_rec.actual_amount));
6265: pnp_debug_pkg.log('EXTEND_PAYMENT_TERM - l_cash_act_amt: ' ||TO_CHAR(l_cash_act_amt));
6266:
6267: IF existing_items_rec.actual_amount <> l_cash_act_amt THEN
6268: UPDATE pn_payment_items_all
6269: SET actual_amount = ROUND(l_cash_act_amt - l_exist_amount, l_precision),
6270: export_currency_amount = ROUND(l_cash_act_amt - l_exist_amount, l_precision),
6271: estimated_amount = ROUND(l_cash_est_amt,l_precision),
6272: last_update_date = sysdate,
6297: ELSE
6298: DELETE FROM PN_PAYMENT_SCHEDULES_ALL
6299: WHERE payment_schedule_id = l_sch_id
6300: AND NOT EXISTS (select 'Y'
6301: from pn_payment_items_all
6302: where payment_schedule_id = l_sch_id); /* Added Bug 9542483 */
6303: END IF;
6304: END IF; /* NOT(l_rec_found) */
6305:
6313:
6314: FOR existing_items_rec IN existing_payment_item_cur(l_sch_id) LOOP
6315: l_rec_found := TRUE;
6316: IF existing_items_rec.actual_amount <> l_cash_act_amt THEN
6317: UPDATE pn_payment_items_all
6318: SET actual_amount = ROUND(l_cash_act_amt - l_exist_amount, l_precision),
6319: export_currency_amount = ROUND(l_cash_act_amt - l_exist_amount, l_precision),
6320: estimated_amount = ROUND(l_cash_est_amt, l_precision),
6321: last_update_date = sysdate,
6342: ELSE
6343: DELETE FROM PN_PAYMENT_SCHEDULES_ALL
6344: WHERE payment_schedule_id = l_sch_id
6345: and not exists (select NULL
6346: from pn_payment_items_all
6347: where payment_schedule_id = l_sch_id); /* 9322649 */
6348: END IF;
6349:
6350: END IF; /* NOT(l_rec_found) */
6543:
6544: CURSOR last_sch_cur (p_term_id NUMBER) IS
6545: SELECT MAX(sch.schedule_date)
6546: FROM pn_payment_schedules_all sch,
6547: pn_payment_items_all itm
6548: WHERE itm.payment_term_id = p_term_id
6549: AND itm.payment_schedule_id = sch.payment_schedule_id
6550: AND itm.payment_item_type_lookup_code = 'CASH';
6551:
6954: SUM(DECODE(ppi.payment_item_type_lookup_code, 'CASH',
6955: DECODE(ppt.normalize,'Y',NVL(ppi.actual_amount,0), 0), 0)) cash_norm_amt,
6956: SUM(DECODE(ppi.payment_item_type_lookup_code, 'NORMALIZED',
6957: NVL(ppi.actual_amount,0), 0)) norm_amt
6958: FROM pn_payment_items_all ppi,
6959: pn_payment_schedules_all pps,
6960: pn_payment_terms_all ppt
6961: WHERE ppt.lease_id = g_lease_id
6962: AND ppt.lease_id = pps.lease_id
7263:
7264: -------------------------------------------------------------------------------
7265: -- PROCEDURE : UPDATE_CASH_ITEM
7266: -- DESCRIPTION : It updates last Cash Item by updating table
7267: -- PN_PAYMENT_ITEMS_ALL with new adjustment amount
7268: -- NOTE : It is overloaded for mini retro.
7269: -- INVOKED FROM : contract_pay_term_mini_retro
7270: -- ARGUMENTS : IN : p_term_id, p_schedule_dt, p_act_amt
7271: -- HISTORY :
7304: END LOOP;
7305:
7306: IF p_item_id IS NOT NULL THEN
7307:
7308: UPDATE pn_payment_items_all
7309: SET actual_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
7310: export_currency_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
7311: last_update_date = SYSDATE,
7312: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
7314: WHERE payment_item_id = p_item_id;
7315:
7316: ELSE
7317:
7318: UPDATE pn_payment_items_all
7319: SET actual_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
7320: export_currency_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
7321: last_update_date = SYSDATE,
7322: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
7321: last_update_date = SYSDATE,
7322: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
7323: last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
7324: WHERE payment_item_id = (SELECT ppi.payment_item_id
7325: FROM pn_payment_items_all ppi,
7326: pn_payment_schedules_all pps
7327: WHERE ppi.payment_term_id = p_term_id
7328: AND ppi.payment_item_type_lookup_code = 'CASH'
7329: AND pps.payment_schedule_id = ppi.payment_schedule_id
7360: l_cash_act_amt NUMBER;
7361: l_cash_est_amt NUMBER;
7362: l_amt_due_to_term NUMBER;
7363: l_amt_due_to_old_term NUMBER;
7364: l_payment_item_id pn_payment_items_all.payment_item_id%TYPE;
7365:
7366: CURSOR total_amt_old_term_cur IS
7367: SELECT SUM(ppi.actual_amount) AS total_amount
7368: FROM pn_payment_items_all ppi
7364: l_payment_item_id pn_payment_items_all.payment_item_id%TYPE;
7365:
7366: CURSOR total_amt_old_term_cur IS
7367: SELECT SUM(ppi.actual_amount) AS total_amount
7368: FROM pn_payment_items_all ppi
7369: WHERE ppi.payment_term_id = p_term_id
7370: AND ppi.payment_item_type_lookup_code = 'CASH';
7371:
7372: BEGIN
7423: BEGIN
7424: pnp_debug_pkg.log('Delete term start (+)');
7425:
7426: pnp_debug_pkg.log('Deleting all items for term ....'||p_payment_term_id);
7427: DELETE pn_payment_items_all
7428: WHERE payment_term_id = p_payment_term_id;
7429:
7430: pnp_debug_pkg.log('Deleting term ....'||p_payment_term_id);
7431: DELETE pn_payment_terms_all
7493: l_lst_cash_sch_dt DATE;
7494: l_norm_str_dt DATE;
7495: l_lease_termination_date DATE;
7496: l_active_lease_change_id pn_lease_details.lease_change_id%TYPE;
7497: l_payment_schedule_id pn_payment_items_all.payment_schedule_id%TYPE;
7498: l_payment_item_id pn_payment_items_all.payment_item_id%TYPE;
7499: l_payment_status_lookup_code pn_payment_schedules_all.payment_status_lookup_code%TYPE;
7500: l_adjustment VARCHAR2(1);
7501: l_adj_amount NUMBER;
7494: l_norm_str_dt DATE;
7495: l_lease_termination_date DATE;
7496: l_active_lease_change_id pn_lease_details.lease_change_id%TYPE;
7497: l_payment_schedule_id pn_payment_items_all.payment_schedule_id%TYPE;
7498: l_payment_item_id pn_payment_items_all.payment_item_id%TYPE;
7499: l_payment_status_lookup_code pn_payment_schedules_all.payment_status_lookup_code%TYPE;
7500: l_adjustment VARCHAR2(1);
7501: l_adj_amount NUMBER;
7502:
7511:
7512: /* find id CASH item exists for a schedule */
7513: CURSOR cash_item_exist_cur(p_sched_id NUMBER) IS
7514: SELECT payment_item_id
7515: FROM pn_payment_items_all
7516: WHERE payment_item_type_lookup_code = 'CASH'
7517: AND payment_schedule_id = p_sched_id
7518: AND payment_term_id = p_payment_term_id;
7519:
7526: AND pps.payment_status_lookup_code = 'DRAFT';
7527:
7528: CURSOR get_drf_sch_date(p_payment_term_id NUMBER) IS
7529: SELECT distinct schedule_date
7530: FROM pn_payment_items_all ppi,
7531: pn_payment_schedules_all pps
7532: WHERE ppi.payment_term_id = p_payment_term_id
7533: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
7534: AND pps.payment_schedule_id = ppi.payment_schedule_id
7637:
7638: IF p_adjustment = 'Y' THEN
7639:
7640: /* delete items beyond the end date */
7641: DELETE pn_payment_items_all
7642: WHERE payment_schedule_id IN
7643: (SELECT payment_schedule_id
7644: FROM pn_payment_schedules_all
7645: WHERE lease_id = p_lease_id
7776: l_lst_cash_sch_dt := TO_DATE(TO_CHAR(pay_term_con_cur.schedule_day)
7777: ||'/'||TO_CHAR(g_new_lea_term_dt,'MM/YYYY')
7778: ,'DD/MM/YYYY');
7779:
7780: DELETE pn_payment_items_all
7781: WHERE payment_schedule_id IN
7782: (SELECT payment_schedule_id
7783: FROM pn_payment_schedules_all
7784: WHERE lease_id = p_lease_id
7788: l_count := 0;
7789: -- Copied from PN_NORM_RENORM_PKG.NORMALIZE_RENORMALIZE
7790: SELECT count(*) into l_count
7791: FROM pn_payment_schedules_all pps,
7792: pn_payment_items_all ppi
7793: WHERE pps.lease_id = p_lease_id
7794: AND pps.schedule_date BETWEEN PN_SCHEDULES_ITEMS.FIRST_DAY(NVL(l_amd_comn_date,l_norm_str_dt)) /* 7149537 */
7795: AND LAST_DAY(g_new_lea_term_dt)
7796: AND pps.payment_status_lookup_code in ( 'DRAFT', 'ON_HOLD' )
7856: FOR zero_item IN (SELECT cash.payment_item_id c,
7857: norm.payment_item_id n
7858: FROM
7859: (SELECT *
7860: FROM pn_payment_items_all
7861: WHERE payment_term_id = pay_term_con_cur.payment_term_id
7862: AND export_to_ap_flag IS NULL
7863: AND export_to_ar_flag IS NULL
7864: AND payment_item_type_lookup_code = 'CASH'
7864: AND payment_item_type_lookup_code = 'CASH'
7865: AND actual_amount = 0
7866: ) cash,
7867: (SELECT *
7868: FROM pn_payment_items_all
7869: WHERE payment_term_id = pay_term_con_cur.payment_term_id
7870: AND export_to_ap_flag IS NULL
7871: AND export_to_ar_flag IS NULL
7872: AND payment_item_type_lookup_code = 'NORMALIZED'
7874: ) norm
7875: WHERE cash.due_date = norm.due_date
7876: )
7877: LOOP
7878: DELETE FROM pn_payment_items_all
7879: WHERE payment_item_id IN (zero_item.c,zero_item.n);
7880: END LOOP;
7881:
7882: END LOOP;
7953: FROM pn_payment_schedules_all
7954: WHERE lease_id = p_lease_ID
7955: AND payment_status_lookup_code = 'APPROVED'
7956: AND payment_schedule_id IN (SELECT payment_schedule_id
7957: FROM pn_payment_items_all
7958: WHERE payment_term_id = p_payment_term_id);
7959:
7960:
7961: BEGIN