[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:
492: l_due_date DATE;
493:
494: CURSOR first_item_cursor IS
495: SELECT MIN(due_date)
496: FROM pn_payment_items_all
497: WHERE payment_term_id = p_payment_term_id
498: AND payment_item_type_lookup_code = 'CASH';
499:
500: BEGIN
612: CURSOR lst_sch_dt_cur (p_term_id NUMBER)
613: IS
614: SELECT MAX(pps.schedule_date), COUNT(pps.schedule_date)
615: FROM pn_payment_schedules_all pps,
616: pn_payment_items_all ppi
617: WHERE ppi.payment_term_id = p_term_id
618: AND pps.payment_schedule_id = ppi.payment_schedule_id
619: AND ppi.actual_amount <> 0
620: AND ppi.payment_item_type_lookup_code = 'CASH';
1173: p_cash_act_amt OUT NOCOPY NUMBER,
1174: p_cash_est_amt OUT NOCOPY NUMBER)
1175: IS
1176:
1177: l_cash_act_amt pn_payment_items.actual_amount%TYPE;
1178: l_partial_start VARCHAR2(1);
1179: l_partial_end VARCHAR2(1);
1180:
1181: BEGIN
1369: WHERE lease_id = p_lease_id;
1370:
1371: CURSOR sched_exists(p_payment_term_id NUMBER,p_sch_dt DATE) IS
1372: SELECT sched.payment_schedule_id
1373: FROM pn_payment_items_all item,
1374: pn_payment_schedules_all sched
1375: WHERE item.payment_term_id = p_payment_term_id
1376: AND item.payment_schedule_id = sched.payment_schedule_id
1377: AND sched.schedule_date = p_sch_dt;
1499:
1500: --------------------------------------------------------------------------------------------
1501: -- PROCEDURE : CREATE_CASH_ITEMS
1502: -- DESCRIPTION: This procedure creates the cash items by inserting given amount, schedule Id
1503: -- and date, term Id etc. in the table PN_PAYMENT_ITEMS.
1504: -- 25-JUN-2001 created mmisra
1505: -- 26-APR-2002 modified ftanudja o added p_rate
1506: -- 15-JUL-05 hareesha o Bug 4284035 - Replaced pn_payment_items with _ALL table.
1507: -- 09-JAN-07 Hareesha o M28#16 for Recurring backbills, populate the due_date as
1502: -- DESCRIPTION: This procedure creates the cash items by inserting given amount, schedule Id
1503: -- and date, term Id etc. in the table PN_PAYMENT_ITEMS.
1504: -- 25-JUN-2001 created mmisra
1505: -- 26-APR-2002 modified ftanudja o added p_rate
1506: -- 15-JUL-05 hareesha o Bug 4284035 - Replaced pn_payment_items with _ALL table.
1507: -- 09-JAN-07 Hareesha o M28#16 for Recurring backbills, populate the due_date as
1508: -- calculation date into pn_payment_items_all
1509: -- 24-APR-07 Hareesha o Bug #5899113 Default inv-grping rule set at term-level to
1510: -- items.
1504: -- 25-JUN-2001 created mmisra
1505: -- 26-APR-2002 modified ftanudja o added p_rate
1506: -- 15-JUL-05 hareesha o Bug 4284035 - Replaced pn_payment_items with _ALL table.
1507: -- 09-JAN-07 Hareesha o M28#16 for Recurring backbills, populate the due_date as
1508: -- calculation date into pn_payment_items_all
1509: -- 24-APR-07 Hareesha o Bug #5899113 Default inv-grping rule set at term-level to
1510: -- items.
1511: --------------------------------------------------------------------------------------------
1512: PROCEDURE create_cash_items (p_est_amt NUMBER,
1526:
1527: l_precision NUMBER;
1528: l_ext_precision NUMBER;
1529: l_min_acct_unit NUMBER;
1530: l_payment_item_id pn_payment_items.payment_item_id%TYPE;
1531: l_actual_amount pn_payment_items.actual_amount%TYPE;
1532:
1533: CURSOR org_cur IS
1534: SELECT org_id
1527: l_precision NUMBER;
1528: l_ext_precision NUMBER;
1529: l_min_acct_unit NUMBER;
1530: l_payment_item_id pn_payment_items.payment_item_id%TYPE;
1531: l_actual_amount pn_payment_items.actual_amount%TYPE;
1532:
1533: CURSOR org_cur IS
1534: SELECT org_id
1535: FROM pn_payment_terms_all
1572: FOR rec IN get_inv_grp_rule LOOP
1573: l_inv_grp_rule := rec.grouping_rule_id;
1574: END LOOP;
1575:
1576: INSERT INTO pn_payment_items_all
1577: (payment_item_id,
1578: last_update_date,
1579: last_updated_by,
1580: creation_date,
1600: org_id,
1601: grouping_rule_id)
1602:
1603: VALUES
1604: (PN_PAYMENT_ITEMS_S.NEXTVAL,
1605: SYSDATE,
1606: NVL(fnd_profile.value('USER_ID'),0),
1607: SYSDATE,
1608: NVL(fnd_profile.value('USER_ID'),0),
1644: -- DESCRIPTION:
1645: -- This procedure sums up the Cash amount of a given term,
1646: -- calculates the monthly normalize amount, prorated the first
1647: -- and/or last month's amount if needed and finally creates Normalize
1648: -- items by inserting row in table PN_PAYMENT_ITEMS.
1649: -- 25-JUN-01 Mrinal o Created.
1650: -- 18-OCT-01 Mrinal o Added parameter p_sch_day. Where ever using the
1651: -- ADD_MONTHS function, derived date by concatination
1652: -- of 'DD' component except for end dates.
1677: -- 16-OCT-03 Satish o Fix for BUG# 3201091. Added parameter
1678: -- p_lease_change_id, to use instead of g_lc_id.
1679: -- 24-DEC-03 Satish o Fix for BUG# 3306681. If l_norm_mths <= 1 month then
1680: -- l_monthly_norm_amt := l_total_cash_amt - l_la_amt.
1681: -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_payment_items with _ALL.
1682: -- 24-NOV-05 Kiran o Round amounts befor insert/uptdate into terms OR items.
1683: -- 09-JAN-07 Hareesha o M28#16 for Recurring backbills, populate the due_date as
1684: -- calculation date into pn_payment_items_all
1685: -- 24-APR-07 Hareesha o Bug #5899113 Default inv-grping rule set at term-level to
1680: -- l_monthly_norm_amt := l_total_cash_amt - l_la_amt.
1681: -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_payment_items with _ALL.
1682: -- 24-NOV-05 Kiran o Round amounts befor insert/uptdate into terms OR items.
1683: -- 09-JAN-07 Hareesha o M28#16 for Recurring backbills, populate the due_date as
1684: -- calculation date into pn_payment_items_all
1685: -- 24-APR-07 Hareesha o Bug #5899113 Default inv-grping rule set at term-level to
1686: -- items.
1687: -- 24-AUG-08 RKARTHA o Bug 6829173 - When finding the number of approved schedules,
1688: -- exclude those approved schedules for which on the same
1708: p_rate NUMBER,
1709: p_lease_change_id NUMBER)
1710: IS
1711:
1712: l_total_cash_amt pn_payment_items_all.actual_amount%TYPE;
1713: l_la_amt NUMBER;
1714: l_norm_amt NUMBER;
1715: l_norm_sch_dt DATE;
1716: l_precision NUMBER;
1779: pnp_debug_pkg.log('create_normalize_items IN: p_curr_code : '||p_curr_code);
1780:
1781: SELECT SUM(ppi.actual_amount)
1782: INTO l_total_cash_amt
1783: FROM pn_payment_items_all ppi
1784: WHERE ppi.payment_term_id = p_term_id
1785: AND ppi.payment_item_type_lookup_code = 'CASH';
1786:
1787: pnp_debug_pkg.log('create_normalize_items - total cash : '||TO_CHAR(l_total_cash_amt));
1793: ELSIF p_lease_context IN ('CON','EXP','CONTERM') THEN
1794:
1795: SELECT NVL(SUM(ppi.actual_amount),0)
1796: INTO l_la_amt
1797: FROM pn_payment_items_all ppi,
1798: pn_payment_schedules_all pps
1799: WHERE ppi.payment_term_id = p_term_id
1800: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
1801: AND pps.payment_schedule_id = ppi.payment_schedule_id
1816: IF p_lease_context IN ('CON','EXP','CONTERM') THEN
1817:
1818: SELECT LEAST(p_norm_end_dt, NVL(LAST_DAY(MAX(pps.schedule_date)), p_norm_end_dt))
1819: INTO l_norm_end_dt
1820: FROM pn_payment_items_all ppi,
1821: pn_payment_schedules_all pps
1822: WHERE ppi.payment_term_id = p_term_id
1823: AND pps.payment_schedule_id = ppi.payment_schedule_id
1824: AND pps.payment_status_lookup_code = 'DRAFT';
1826: pnp_debug_pkg.log('l_norm_end_dt : '||TO_CHAR(l_norm_end_dt));
1827:
1828: SELECT COUNT(*)
1829: INTO l_app_sch
1830: FROM pn_payment_items_all ppi,
1831: pn_payment_schedules_all pps
1832: WHERE ppi.payment_term_id = p_term_id
1833: AND pps.payment_schedule_id = ppi.payment_schedule_id
1834: AND pps.payment_status_lookup_code = 'APPROVED'
1836: AND pps.schedule_date between First_Day(p_norm_str_dt) and LAST_DAY(l_norm_end_dt)
1837: AND NOT EXISTS (
1838: SELECT 1
1839: FROM pn_payment_schedules_all ppsi,
1840: pn_payment_items_all ppii
1841: WHERE ppsi.schedule_date = pps.schedule_date
1842: AND ppii.payment_term_id = ppi.payment_term_id
1843: AND ppsi.payment_schedule_id = ppii.payment_schedule_id
1844: AND ppsi.payment_status_lookup_code = 'DRAFT'
1931: fnd_currency.get_info(p_curr_code, l_precision, l_ext_precision, l_min_acct_unit);
1932:
1933: IF p_lease_context IN ('EXP', 'CONTERM') THEN
1934:
1935: DELETE pn_payment_items_all ppi
1936: WHERE ppi.payment_schedule_id IN (SELECT pps.payment_schedule_id
1937: FROM pn_payment_items_all ppi1,
1938: pn_payment_schedules_all pps
1939: WHERE ppi1.payment_term_id = p_term_id
1933: IF p_lease_context IN ('EXP', 'CONTERM') THEN
1934:
1935: DELETE pn_payment_items_all ppi
1936: WHERE ppi.payment_schedule_id IN (SELECT pps.payment_schedule_id
1937: FROM pn_payment_items_all ppi1,
1938: pn_payment_schedules_all pps
1939: WHERE ppi1.payment_term_id = p_term_id
1940: AND ppi1.payment_item_type_lookup_code = 'CASH'
1941: AND pps.payment_schedule_id = ppi1.payment_schedule_id
2008: FOR rec IN get_inv_grp_rule LOOP
2009: l_inv_grp_rule := rec.grouping_rule_id;
2010: END LOOP;
2011:
2012: INSERT INTO pn_payment_items_all
2013: (payment_item_id,
2014: last_update_date,
2015: last_updated_by,
2016: creation_date,
2037: grouping_rule_id)
2038:
2039: VALUES
2040:
2041: (PN_PAYMENT_ITEMS_S.NEXTVAL,
2042: SYSDATE,
2043: NVL(fnd_profile.value('USER_ID'),0),
2044: SYSDATE,
2045: NVL(fnd_profile.value('USER_ID'),0),
2076:
2077: l_norm_amt := l_monthly_norm_amt;
2078: l_prec_norm_amt := ROUND(l_norm_amt,l_precision);
2079:
2080: UPDATE pn_payment_items_all ppi
2081: SET ppi.actual_amount = l_prec_norm_amt,
2082: ppi.export_currency_amount = l_prec_norm_amt,
2083: ppi.last_update_date = SYSDATE,
2084: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2084: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2085: ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
2086: WHERE ppi.payment_schedule_id IN (SELECT pps.payment_schedule_id
2087: FROM pn_payment_schedules_all pps,
2088: pn_payment_items_all ppi1
2089: WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
2090: AND pps.payment_status_lookup_code = 'DRAFT'
2091: AND ppi1.payment_term_id = p_term_id)
2092: AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
2107: l_norm_sch_dt := TO_DATE(TO_CHAR(p_sch_day)||'/'||TO_CHAR(p_norm_str_dt,'MM/YYYY')
2108: ,'DD/MM/YYYY');
2109: l_tot_cum_norm_amt := l_tot_cum_norm_amt - l_prec_norm_amt_upd + l_prec_norm_amt;
2110:
2111: UPDATE pn_payment_items_all ppi
2112: SET ppi.actual_amount = l_prec_norm_amt,
2113: ppi.export_currency_amount = l_prec_norm_amt,
2114: ppi.last_update_date = SYSDATE,
2115: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2115: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2116: ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
2117: WHERE ppi.payment_schedule_id = (SELECT pps.payment_schedule_id
2118: FROM pn_payment_schedules_all pps,
2119: pn_payment_items_all ppi1
2120: WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
2121: AND pps.payment_status_lookup_code = 'DRAFT'
2122: AND ppi1.payment_term_id = p_term_id
2123: AND ppi1.payment_item_type_lookup_code = 'NORMALIZED'
2141: l_tot_cum_diff_amt := (l_total_cash_amt - l_la_amt) - l_tot_cum_norm_amt;
2142: l_prec_norm_amt := ROUND(l_prec_norm_amt + l_tot_cum_diff_amt, l_precision);
2143:
2144: IF l_partial_end_flag = 'Y' OR l_tot_cum_diff_amt <> 0 THEN
2145: UPDATE pn_payment_items_all ppi
2146: SET ppi.actual_amount = l_prec_norm_amt,
2147: ppi.export_currency_amount = l_prec_norm_amt,
2148: ppi.last_update_date = SYSDATE,
2149: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2149: ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2150: ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
2151: WHERE ppi.payment_schedule_id = (SELECT pps.payment_schedule_id
2152: FROM pn_payment_schedules_all pps,
2153: pn_payment_items_all ppi1
2154: WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
2155: AND pps.payment_status_lookup_code = 'DRAFT'
2156: AND ppi1.payment_term_id = p_term_id
2157: AND ppi1.payment_item_type_lookup_code = 'NORMALIZED'
2240: p_norm_str_dt DATE,
2241: p_lease_change_id NUMBER)
2242: IS
2243:
2244: l_cash_act_amt pn_payment_items.actual_amount%TYPE := 0;
2245: l_cash_est_amt pn_payment_items.estimated_amount%TYPE := 0;
2246: l_sch_id pn_payment_schedules.payment_schedule_id%TYPE;
2247: l_sch_str_dt DATE := NULL;
2248: l_sch_end_dt DATE := NULL;
2241: p_lease_change_id NUMBER)
2242: IS
2243:
2244: l_cash_act_amt pn_payment_items.actual_amount%TYPE := 0;
2245: l_cash_est_amt pn_payment_items.estimated_amount%TYPE := 0;
2246: l_sch_id pn_payment_schedules.payment_schedule_id%TYPE;
2247: l_sch_str_dt DATE := NULL;
2248: l_sch_end_dt DATE := NULL;
2249: l_sch_dt pn_payment_schedules.schedule_date%TYPE;
2679:
2680: --------------------------------------------------------------------------------------------
2681: -- PROCEDURE : UPDATE_CASH_ITEM
2682: -- DESCRIPTION: This procedure is used in contracting last schedule of a given term/lease.
2683: -- It updates last Cash Item by updating table PN_PAYMENT_ITEMS with the new
2684: -- prorated amount calculated by procedure GET_AMOUNT..
2685: -- 01-FEB-02 Satish Tripathi o Created.
2686: -- 07-OCT-02 Satish Tripathi o Added payment_status_lookup_code = 'DRAFT' in Update Stmt.
2687: -- Fix for BUG# 2551423.
2741: END LOOP;
2742: l_cash_act_amt := ROUND(l_cash_act_amt, l_precision);
2743: l_cash_est_amt := ROUND(l_cash_est_amt, l_precision);
2744:
2745: UPDATE pn_payment_items_all
2746: SET estimated_amount = l_cash_est_amt,
2747: actual_amount = l_cash_act_amt,
2748: export_currency_amount = l_cash_act_amt,
2749: last_update_date = SYSDATE,
2749: last_update_date = SYSDATE,
2750: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2751: last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
2752: WHERE payment_item_id = (SELECT ppi1.payment_item_id
2753: FROM pn_payment_items_all ppi1,
2754: pn_payment_schedules_all pps
2755: WHERE ppi1.payment_term_id = p_term_id
2756: AND ppi1.payment_item_type_lookup_code = 'CASH'
2757: AND pps.payment_status_lookup_code = 'DRAFT'
2785:
2786: l_sch_str_dt DATE;
2787: l_sch_end_dt DATE;
2788: l_schedule_date DATE;
2789: l_cash_est_amt pn_payment_items.estimated_amount%TYPE;
2790: l_cash_act_amt pn_payment_items.actual_amount%TYPE;
2791: l_frequency NUMBER;
2792:
2793: CURSOR get_terms IS
2786: l_sch_str_dt DATE;
2787: l_sch_end_dt DATE;
2788: l_schedule_date DATE;
2789: l_cash_est_amt pn_payment_items.estimated_amount%TYPE;
2790: l_cash_act_amt pn_payment_items.actual_amount%TYPE;
2791: l_frequency NUMBER;
2792:
2793: CURSOR get_terms IS
2794: SELECT payment_term_id,
2804:
2805: CURSOR get_last_schedule (p_payment_term_id NUMBER) IS
2806: SELECT MAX(schedule_date)
2807: FROM pn_payment_schedules_all pps,
2808: pn_payment_items_all ppi
2809: WHERE ppi.payment_term_id = p_payment_term_id
2810: AND ppi.payment_item_type_lookup_code = 'CASH'
2811: AND ppi.actual_amount <> 0
2812: AND pps.payment_schedule_id = ppi.payment_schedule_id
3270: -- 05-AUG-2001 Amita Singh o Modified to take care of CON+ADD. Added call to ADD_MAIN.
3271: -- 15-AUG-2001 Mrinal Misra o Added calls to routine
3272: -- pn_index_rent_periods_pkg.process_main_lease_term_date.
3273: -- 05-SEP-2001 Mrinal Misra o Added to check for payment_term_id in the update statement
3274: -- of PN_PAYMENT_ITEMS.
3275: -- 24-JAN-2002 Satish Tripathi o Removed the hard coding of p_lease_context being passed to
3276: -- CREATE_NORMALIZE_ITEMS.
3277: -- 07-FEB-2002 Satish Tripathi o Added variable l_norm_str_dt to capture Normalize Start
3278: -- Date, if term was added through AMEND and pass it to
3325: CLOSE get_old_lea_term_dt;
3326:
3327: pnp_debug_pkg.log('CON - MAIN - Deleting Payment Items');
3328:
3329: DELETE pn_payment_items_all
3330: WHERE payment_schedule_id IN (SELECT payment_schedule_id
3331: FROM pn_payment_schedules_all
3332: WHERE lease_id = p_lease_id
3333: AND schedule_date > p_new_lea_term_dt
3346: WHERE lease_id = p_lease_id
3347: AND schedule_date > p_new_lea_term_dt
3348: AND payment_status_lookup_code = 'DRAFT'
3349: AND NOT EXISTS (SELECT null
3350: FROM pn_payment_items_all pitm
3351: WHERE pitm.payment_schedule_id = psch.payment_schedule_id);
3352:
3353: DELETE pn_payment_items_all
3354: WHERE payment_term_id IN (SELECT payment_term_id
3349: AND NOT EXISTS (SELECT null
3350: FROM pn_payment_items_all pitm
3351: WHERE pitm.payment_schedule_id = psch.payment_schedule_id);
3352:
3353: DELETE pn_payment_items_all
3354: WHERE payment_term_id IN (SELECT payment_term_id
3355: FROM pn_payment_terms_all
3356: WHERE start_date > p_new_lea_term_dt
3357: AND lease_id = p_lease_id)
3603: pnp_debug_pkg.log('CONTRACTION_BY_ITM_END_DT - MAIN - Deleting Items');
3604: FOR i IN 1 .. l_item_end_dt_tbl.COUNT LOOP
3605: pnp_debug_pkg.log('+++++++++++Deleting All Items for term:'||l_item_end_dt_tbl(i).term_id||
3606: ' with schedule_date > ' ||l_item_end_dt_tbl(i).item_end_dt);
3607: DELETE pn_payment_items_all
3608: WHERE payment_schedule_id IN (SELECT payment_schedule_id
3609: FROM pn_payment_schedules_all
3610: WHERE lease_id = p_lease_id
3611: AND schedule_date > l_item_end_dt_tbl(i).item_end_dt
3623: WHERE lease_id = p_lease_id
3624: AND schedule_date > p_new_lea_term_dt
3625: AND payment_status_lookup_code = 'DRAFT'
3626: AND NOT EXISTS (SELECT 1
3627: FROM pn_payment_items_all pitm
3628: WHERE pitm.payment_schedule_id = psch.payment_schedule_id);
3629:
3630: /* Delete payment terms for the lease which have term start date
3631: greater than lease termination date. */
3636: WHERE lease_id = p_lease_id
3637: AND start_date > p_new_lea_term_dt
3638: AND index_period_id IS NULL
3639: AND NOT EXISTS(SELECT 1
3640: FROM pn_payment_items_all item,
3641: pn_payment_schedules_all schd
3642: WHERE item.payment_term_id = term.payment_term_id
3643: AND item.payment_schedule_id = schd.payment_schedule_id
3644: AND schd.payment_status_lookup_code = 'APPROVED');
4256: AND ppt.index_period_id IS NULL
4257: AND ppt.var_rent_inv_id IS NULL
4258: AND ppt.period_billrec_id IS NULL
4259: AND NOT EXISTS (SELECT NULL
4260: FROM pn_payment_items_all ppi
4261: WHERE ppt.lease_id = p_lease_id
4262: AND ppi.payment_term_id = ppt.payment_term_id)
4263: AND frequency_code = 'OT'
4264: AND NVL(normalize,'N') = 'N';
4569: p_term_end_dt DATE)
4570: IS
4571: SELECT MAX(pps.schedule_date)
4572: FROM pn_payment_schedules_all pps,
4573: pn_payment_items_all ppi
4574: WHERE pps.payment_schedule_id = ppi.payment_schedule_id
4575: AND ppi.payment_term_id = p_term_id
4576: AND ppi.actual_amount IS NOT NULL
4577: AND ppi.payment_item_type_lookup_code = 'CASH'
4652: END IF;
4653:
4654: IF pay_term_con_cur.normalize = 'Y' THEN
4655:
4656: UPDATE pn_payment_items_all
4657: SET estimated_amount = 0,
4658: actual_amount = 0,
4659: export_currency_amount = 0,
4660: last_update_date = SYSDATE,
4660: last_update_date = SYSDATE,
4661: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
4662: last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
4663: WHERE payment_item_id IN (SELECT ppi1.payment_item_id
4664: FROM pn_payment_items_all ppi1,
4665: pn_payment_schedules_all pps
4666: WHERE ppi1.payment_term_id = pay_term_con_cur.payment_term_id
4667: AND ppi1.payment_item_type_lookup_code = 'CASH'
4668: AND pps.payment_status_lookup_code = 'DRAFT'
4672: pnp_debug_pkg.log('CONTRACT_PAY_TERM - Updated Cash Items Amt to 0. - Normalize');
4673:
4674: ELSE
4675:
4676: DELETE pn_payment_items_all ppi
4677: WHERE ppi.payment_term_id = pay_term_con_cur.payment_term_id
4678: AND ppi.payment_schedule_id IN (SELECT pps1.payment_schedule_id
4679: FROM pn_payment_schedules_all pps1,
4680: pn_payment_items_all ppi1
4676: DELETE pn_payment_items_all ppi
4677: WHERE ppi.payment_term_id = pay_term_con_cur.payment_term_id
4678: AND ppi.payment_schedule_id IN (SELECT pps1.payment_schedule_id
4679: FROM pn_payment_schedules_all pps1,
4680: pn_payment_items_all ppi1
4681: WHERE ppi1.payment_term_id = pay_term_con_cur.payment_term_id
4682: AND pps1.payment_schedule_id = ppi1.payment_schedule_id
4683: AND pps1.payment_status_lookup_code = 'DRAFT'
4684: AND pps1.schedule_date > l_lst_cash_sch_dt);
4691: AND TO_NUMBER(TO_CHAR(pps1.schedule_date,'DD'))
4692: = pay_term_con_cur.schedule_day
4693: AND pps1.schedule_date > l_lst_cash_sch_dt
4694: AND NOT EXISTS (SELECT NULL
4695: FROM pn_payment_items_all ppi
4696: WHERE ppi.payment_schedule_id
4697: = pps1.payment_schedule_id
4698: )
4699: );
5146: l_exist_amount NUMBER;
5147:
5148: CURSOR existing_payment_item_cur (p_sch_id NUMBER) IS
5149: SELECT payment_item_id, actual_amount, estimated_amount
5150: FROM pn_payment_items_all
5151: WHERE payment_schedule_id = p_sch_id
5152: AND payment_term_id = p_payment_term_rec.payment_term_id
5153: AND payment_item_type_lookup_code = 'CASH';
5154:
5153: AND payment_item_type_lookup_code = 'CASH';
5154:
5155: CURSOR exist_appr_item_amt IS
5156: SELECT NVL(SUM(pi.actual_amount),0) amount
5157: FROM pn_payment_items_all pi,
5158: pn_payment_schedules_all ps
5159: WHERE pi.payment_term_id = p_payment_term_rec.payment_term_id
5160: AND pi.payment_schedule_id = ps.payment_schedule_id
5161: AND ps.schedule_date = l_sch_dt
5305: pnp_debug_pkg.log('Retro not enabled... ');
5306: FOR existing_items_rec in existing_payment_item_cur(l_sch_id) LOOP
5307: l_rec_found := TRUE;
5308: IF existing_items_rec.actual_amount <> l_cash_act_amt THEN
5309: UPDATE pn_payment_items_all
5310: SET actual_amount = ROUND(l_cash_act_amt,l_precision),
5311: export_currency_amount = ROUND(l_cash_act_amt,l_precision),
5312: estimated_amount = ROUND(l_cash_est_amt,l_precision),
5313: last_update_date = sysdate,
5343:
5344: FOR existing_items_rec IN existing_payment_item_cur(l_sch_id) LOOP
5345: l_rec_found := TRUE;
5346: IF existing_items_rec.actual_amount <> l_cash_act_amt THEN
5347: UPDATE pn_payment_items_all
5348: SET actual_amount = ROUND(l_cash_act_amt - l_exist_amount, l_precision),
5349: export_currency_amount = ROUND(l_cash_act_amt - l_exist_amount, l_precision),
5350: estimated_amount = ROUND(l_cash_est_amt, l_precision),
5351: last_update_date = sysdate,
5564:
5565: CURSOR last_sch_cur (p_term_id NUMBER) IS
5566: SELECT MAX(sch.schedule_date)
5567: FROM pn_payment_schedules_all sch,
5568: pn_payment_items_all itm
5569: WHERE itm.payment_term_id = p_term_id
5570: AND itm.payment_schedule_id = sch.payment_schedule_id
5571: AND itm.payment_item_type_lookup_code = 'CASH';
5572:
5973: SUM(DECODE(ppi.payment_item_type_lookup_code, 'CASH',
5974: DECODE(ppt.normalize,'Y',NVL(ppi.actual_amount,0), 0), 0)) cash_norm_amt,
5975: SUM(DECODE(ppi.payment_item_type_lookup_code, 'NORMALIZED',
5976: NVL(ppi.actual_amount,0), 0)) norm_amt
5977: FROM pn_payment_items_all ppi,
5978: pn_payment_schedules_all pps,
5979: pn_payment_terms_all ppt
5980: WHERE ppt.lease_id = g_lease_id
5981: AND ppt.lease_id = pps.lease_id
6277:
6278: -------------------------------------------------------------------------------
6279: -- PROCEDURE : UPDATE_CASH_ITEM
6280: -- DESCRIPTION : It updates last Cash Item by updating table
6281: -- PN_PAYMENT_ITEMS_ALL with new adjustment amount
6282: -- NOTE : It is overloaded for mini retro.
6283: -- INVOKED FROM : contract_pay_term_mini_retro
6284: -- ARGUMENTS : IN : p_term_id, p_schedule_dt, p_act_amt
6285: -- HISTORY :
6318: END LOOP;
6319:
6320: IF p_item_id IS NOT NULL THEN
6321:
6322: UPDATE pn_payment_items_all
6323: SET actual_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
6324: export_currency_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
6325: last_update_date = SYSDATE,
6326: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
6328: WHERE payment_item_id = p_item_id;
6329:
6330: ELSE
6331:
6332: UPDATE pn_payment_items_all
6333: SET actual_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
6334: export_currency_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
6335: last_update_date = SYSDATE,
6336: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
6335: last_update_date = SYSDATE,
6336: last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
6337: last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
6338: WHERE payment_item_id = (SELECT ppi.payment_item_id
6339: FROM pn_payment_items_all ppi,
6340: pn_payment_schedules_all pps
6341: WHERE ppi.payment_term_id = p_term_id
6342: AND ppi.payment_item_type_lookup_code = 'CASH'
6343: AND pps.payment_schedule_id = ppi.payment_schedule_id
6374: l_cash_act_amt NUMBER;
6375: l_cash_est_amt NUMBER;
6376: l_amt_due_to_term NUMBER;
6377: l_amt_due_to_old_term NUMBER;
6378: l_payment_item_id pn_payment_items_all.payment_item_id%TYPE;
6379:
6380: CURSOR total_amt_old_term_cur IS
6381: SELECT SUM(ppi.actual_amount) AS total_amount
6382: FROM pn_payment_items_all ppi
6378: l_payment_item_id pn_payment_items_all.payment_item_id%TYPE;
6379:
6380: CURSOR total_amt_old_term_cur IS
6381: SELECT SUM(ppi.actual_amount) AS total_amount
6382: FROM pn_payment_items_all ppi
6383: WHERE ppi.payment_term_id = p_term_id
6384: AND ppi.payment_item_type_lookup_code = 'CASH';
6385:
6386: BEGIN
6437: BEGIN
6438: pnp_debug_pkg.log('Delete term start (+)');
6439:
6440: pnp_debug_pkg.log('Deleting all items for term ....'||p_payment_term_id);
6441: DELETE pn_payment_items_all
6442: WHERE payment_term_id = p_payment_term_id;
6443:
6444: pnp_debug_pkg.log('Deleting term ....'||p_payment_term_id);
6445: DELETE pn_payment_terms_all
6498: l_lst_cash_sch_dt DATE;
6499: l_norm_str_dt DATE;
6500: l_lease_termination_date DATE;
6501: l_active_lease_change_id pn_lease_details.lease_change_id%TYPE;
6502: l_payment_schedule_id pn_payment_items_all.payment_schedule_id%TYPE;
6503: l_payment_item_id pn_payment_items_all.payment_item_id%TYPE;
6504: l_payment_status_lookup_code pn_payment_schedules_all.payment_status_lookup_code%TYPE;
6505: l_adjustment VARCHAR2(1);
6506: l_adj_amount NUMBER;
6499: l_norm_str_dt DATE;
6500: l_lease_termination_date DATE;
6501: l_active_lease_change_id pn_lease_details.lease_change_id%TYPE;
6502: l_payment_schedule_id pn_payment_items_all.payment_schedule_id%TYPE;
6503: l_payment_item_id pn_payment_items_all.payment_item_id%TYPE;
6504: l_payment_status_lookup_code pn_payment_schedules_all.payment_status_lookup_code%TYPE;
6505: l_adjustment VARCHAR2(1);
6506: l_adj_amount NUMBER;
6507:
6511:
6512: /* find id CASH item exists for a schedule */
6513: CURSOR cash_item_exist_cur(p_sched_id NUMBER) IS
6514: SELECT payment_item_id
6515: FROM pn_payment_items_all
6516: WHERE payment_item_type_lookup_code = 'CASH'
6517: AND payment_schedule_id = p_sched_id
6518: AND payment_term_id = p_payment_term_id;
6519:
6551:
6552: IF p_adjustment = 'Y' THEN
6553:
6554: /* delete items beyond the end date */
6555: DELETE pn_payment_items_all
6556: WHERE payment_schedule_id IN
6557: (SELECT payment_schedule_id
6558: FROM pn_payment_schedules_all
6559: WHERE lease_id = p_lease_id
6663:
6664: l_norm_str_dt := NVL(pay_term_con_cur.norm_start_date,
6665: Get_First_Item_Date(pay_term_con_cur.payment_term_id));
6666: /* Added for Bug 6154106*/
6667: DELETE pn_payment_items_all
6668: WHERE payment_schedule_id IN
6669: (SELECT payment_schedule_id
6670: FROM pn_payment_schedules_all
6671: WHERE lease_id = p_lease_id
6675: l_count := 0;
6676: -- Copied from PN_NORM_RENORM_PKG.NORMALIZE_RENORMALIZE
6677: SELECT count(*) into l_count
6678: FROM pn_payment_schedules_all pps,
6679: pn_payment_items_all ppi
6680: WHERE pps.lease_id = p_lease_id
6681: AND pps.schedule_date BETWEEN PN_SCHEDULES_ITEMS.FIRST_DAY(l_norm_str_dt)
6682: AND LAST_DAY(g_new_lea_term_dt)
6683: AND pps.payment_status_lookup_code in ( 'DRAFT', 'ON_HOLD' )
6796: FROM pn_payment_schedules_all
6797: WHERE lease_id = p_lease_ID
6798: AND payment_status_lookup_code = 'APPROVED'
6799: AND payment_schedule_id IN (SELECT payment_schedule_id
6800: FROM pn_payment_items_all
6801: WHERE payment_term_id = p_payment_term_id);
6802:
6803:
6804: BEGIN