[Home] [Help]
296: ,act_per_rent
297: ,actual_invoiced_amount
298: ,rec_abatement_override
299: FROM
300: pn_var_rent_inv_all
301: WHERE
302: var_rent_id = p_vr_id AND
303: period_id = p_prd_id AND
304: invoice_date = p_inv_dt AND
327: ,p_prd_id IN NUMBER) IS
328: SELECT
329: NVL(SUM(actual_invoiced_amount), 0) AS prev_inv_trueup_amt
330: FROM
331: pn_var_rent_inv_all
332: WHERE
333: var_rent_id = p_vr_id AND
334: period_id = p_prd_id AND
335: (true_up_amt IS NULL OR true_up_exp_code = 'Y');
590: status <> pn_var_rent_calc_pkg.G_TERM_STATUS_APPROVED;
591:
592: /* update the invoice */
593: UPDATE
594: pn_var_rent_inv_all
595: SET
596: act_per_rent = l_total_period_unabt_rent
597: ,constr_actual_rent = l_total_period_unabt_rent
598: ,actual_invoiced_amount = (l_total_period_rent - l_prev_inv_trueup_rent)
655: ) IS
656: SELECT * FROM
657: (SELECT var_rent_inv_id, constr_actual_rent, true_up_amt,
658: true_up_exp_code, invoice_date
659: FROM pn_var_rent_inv_all inv1
660: WHERE inv1.var_rent_id = ip_var_rent_id
661: AND inv1.period_id = ip_period_id
662: AND inv1.invoice_date <= p_new_termn_date
663: AND inv1.adjust_num= (
660: WHERE inv1.var_rent_id = ip_var_rent_id
661: AND inv1.period_id = ip_period_id
662: AND inv1.invoice_date <= p_new_termn_date
663: AND inv1.adjust_num= (
664: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
665: WHERE inv1.var_rent_id = inv2.var_rent_id
666: AND inv1.period_id = inv2.period_id
667: AND inv1.invoice_date = inv2.invoice_date)
668: AND TRUE_UP_AMT IS NULL
764: ,p_inv_dt IN DATE) IS
765: SELECT
766: NVL(SUM(actual_invoiced_amount), 0) AS prev_invoiced_amt
767: FROM
768: pn_var_rent_inv_all
769: WHERE
770: var_rent_id = p_vr_id AND
771: period_id = p_prd_id AND
772: invoice_date = p_inv_dt AND
783: ,p_inv_dt IN DATE) IS
784: SELECT
785: NVL(SUM(actual_invoiced_amount), 0) AS prev_inv_trueup_amt
786: FROM
787: pn_var_rent_inv_all
788: WHERE
789: var_rent_id = p_vr_id AND
790: period_id = p_prd_id AND
791: invoice_date = p_inv_dt AND
813: pn_var_rent_calc_pkg.G_INV_ON_FORECASTED, variance_exp_code) AS
814: exp_code,
815: inv.actual_invoiced_amount
816: FROM pn_var_rents_all vr,
817: pn_var_rent_inv_all inv
818: WHERE vr.var_rent_id = inv.var_rent_id
819: AND inv.var_rent_inv_id = ip_var_rent_inv_id;
820:
821: -- sum of non cumulative rents of a specific period
822: CURSOR non_cumm_rent_c(ip_var_rent_id NUMBER,
823: ip_period_id NUMBER
824: ) IS
825: SELECT SUM(actual_invoiced_amount) tot_nc_rent
826: FROM pn_var_rent_inv_all
827: WHERE var_rent_id = ip_var_rent_id
828: AND period_id = ip_period_id
829: AND true_up_amt IS NULL
830: AND true_up_status IS NULL
834: -- Get all the details of a specific invoice.
835: CURSOR inv_all_c(ip_vr_inv_id NUMBER
836: ) IS
837: SELECT *
838: FROM pn_var_rent_inv_all
839: WHERE var_rent_inv_id = ip_vr_inv_id;
840:
841:
842: l_abat_order VARCHAR(30);
947:
948: -- Update the current invoice
949: pnp_debug_pkg.log('Abatements - updating');
950: UPDATE
951: pn_var_rent_inv_all
952: SET
953: actual_invoiced_amount = (round(l_abated_rent,l_precision) - round(l_prev_inv_exp,l_precision))
954: ,actual_term_status = 'N'
955: ,last_update_date = SYSDATE
1029:
1030: IF (inv_rec.true_up_exp_code = 'N') THEN
1031: --Last invoice is not exported
1032: UPDATE
1033: pn_var_rent_inv_all
1034: SET
1035: actual_invoiced_amount = (l_abated_rent - l_prev_inv_exp)
1036: ,actual_term_status = 'N'
1037: ,last_update_date = SYSDATE
1187: ,true_up_amt
1188: ,negative_rent
1189: ,invoice_date
1190: ,var_rent_inv_id
1191: FROM pn_var_rent_inv_all inv1
1192: WHERE inv1.var_rent_id = ip_var_rent_id
1193: AND inv1.period_id = ip_period_id
1194: AND var_rent_inv_id = ip_inv_id
1195: AND inv1.adjust_num= (
1192: WHERE inv1.var_rent_id = ip_var_rent_id
1193: AND inv1.period_id = ip_period_id
1194: AND var_rent_inv_id = ip_inv_id
1195: AND inv1.adjust_num= (
1196: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
1197: WHERE inv1.var_rent_id = inv2.var_rent_id
1198: AND inv1.period_id = inv2.period_id
1199: AND inv1.invoice_date = inv2.invoice_date);
1200:
1211:
1212: CURSOR csr_neg_avail (ip_var_rent_id NUMBER,
1213: ip_invoice_date DATE) IS
1214: SELECT ABS(NVL(SUM(def_neg_rent),0)) negative_available
1215: FROM pn_var_rent_inv_all inv
1216: WHERE inv.var_rent_id = ip_var_rent_id
1217: --AND period_id = p_period_id
1218: AND inv.invoice_date < ip_invoice_date
1219: AND inv.adjust_num = (select MAX(inv1.adjust_num)
1216: WHERE inv.var_rent_id = ip_var_rent_id
1217: --AND period_id = p_period_id
1218: AND inv.invoice_date < ip_invoice_date
1219: AND inv.adjust_num = (select MAX(inv1.adjust_num)
1220: from pn_var_rent_inv_all inv1
1221: where inv1.var_rent_id = inv.var_rent_id
1222: AND inv1.invoice_date = inv.invoice_date);
1223:
1224: CURSOR csr_neg_appl (ip_var_rent_id NUMBER,
1223:
1224: CURSOR csr_neg_appl (ip_var_rent_id NUMBER,
1225: ip_invoice_date DATE) IS
1226: SELECT NVL(SUM(negative_rent),0) negative_applied
1227: FROM pn_var_rent_inv_all inv
1228: WHERE inv.var_rent_id = ip_var_rent_id
1229: --AND period_id = p_period_id
1230: AND inv.invoice_date < ip_invoice_date
1231: AND inv.adjust_num = (select MAX(inv1.adjust_num)
1228: WHERE inv.var_rent_id = ip_var_rent_id
1229: --AND period_id = p_period_id
1230: AND inv.invoice_date < ip_invoice_date
1231: AND inv.adjust_num = (select MAX(inv1.adjust_num)
1232: from pn_var_rent_inv_all inv1
1233: where inv1.var_rent_id = inv.var_rent_id
1234: AND inv1.invoice_date = inv.invoice_date);
1235: CURSOR csr_get_abt(ip_var_rent_id NUMBER)
1236: IS
1309: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
1310: pnp_debug_pkg.log('l_negative_rent:'||l_negative_rent);
1311: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
1312:
1313: UPDATE pn_var_rent_inv_all
1314: SET negative_rent = l_negative_rent
1315: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
1316: x_abated_rent := l_abated_rent;
1317: END LOOP;
1347: CURSOR invoices_c(ip_var_rent_id NUMBER, ip_period_id NUMBER, ip_inv_id NUMBER
1348: ) IS
1349: SELECT invoice_date
1350: ,var_rent_inv_id
1351: FROM pn_var_rent_inv_all inv1
1352: WHERE inv1.var_rent_id = ip_var_rent_id
1353: AND inv1.period_id = ip_period_id
1354: AND var_rent_inv_id = ip_inv_id
1355: AND inv1.adjust_num= (
1352: WHERE inv1.var_rent_id = ip_var_rent_id
1353: AND inv1.period_id = ip_period_id
1354: AND var_rent_inv_id = ip_inv_id
1355: AND inv1.adjust_num= (
1356: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
1357: WHERE inv1.var_rent_id = inv2.var_rent_id
1358: AND inv1.period_id = inv2.period_id
1359: AND inv1.invoice_date = inv2.invoice_date);
1360:
1413: l_abated_rent := x_abated_rent;
1414: END IF;
1415: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
1416: pnp_debug_pkg.log('l_neg_rent_def*:'||l_neg_rent_def);
1417: UPDATE pn_var_rent_inv_all
1418: SET def_neg_rent = l_neg_rent_def
1419: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
1420: x_abated_rent := l_abated_rent;
1421: END LOOP;
1455: ,NVL(rec_abatement,0) rec_abatement
1456: ,rec_abatement_override
1457: ,true_up_amt
1458: ,var_rent_inv_id
1459: FROM pn_var_rent_inv_all inv1
1460: WHERE inv1.var_rent_id = ip_var_rent_id
1461: AND inv1.period_id = ip_period_id
1462: AND inv1.var_rent_inv_id = ip_inv_id
1463: AND inv1.adjust_num= (
1460: WHERE inv1.var_rent_id = ip_var_rent_id
1461: AND inv1.period_id = ip_period_id
1462: AND inv1.var_rent_inv_id = ip_inv_id
1463: AND inv1.adjust_num= (
1464: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
1465: where inv1.var_rent_id = inv2.var_rent_id
1466: AND inv1.period_id = inv2.period_id
1467: AND inv1.invoice_date = inv2.invoice_date)
1468: AND TRUE_UP_AMT IS NULL
1521: -- Get the details of actual start and end date for FY/LY/FLY
1522: CURSOR invoice_dates_fyly_c(ip_var_rent_inv_id NUMBER
1523: ) IS
1524: SELECT per.start_date, per.end_date
1525: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1526: WHERE per.period_id = inv.period_id
1527: AND inv.var_rent_inv_id = ip_var_rent_inv_id;
1528:
1529: -- Get the id of invoice created for first year.
1530: -- This has meaning only if proration is FY/FLY
1531: CURSOR get_fy_inv_c(ip_var_rent_id NUMBER
1532: ) IS
1533: SELECT inv.var_rent_inv_id
1534: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1535: WHERE per.period_id = inv.period_id
1536: AND inv.var_rent_id = ip_var_rent_id
1537: AND per.start_date = (SELECT MIN(start_date) from pn_var_periods_all
1538: WHERE var_rent_id = ip_var_rent_id);
1540: -- This has meaning only if proration is LY/FLY
1541: CURSOR get_ly_inv_c(ip_var_rent_id NUMBER
1542: ) IS
1543: SELECT inv.var_rent_inv_id
1544: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1545: WHERE per.period_id = inv.period_id
1546: AND inv.var_rent_id = ip_var_rent_id
1547: AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
1548: WHERE var_rent_id = ip_var_rent_id)
1545: WHERE per.period_id = inv.period_id
1546: AND inv.var_rent_id = ip_var_rent_id
1547: AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
1548: WHERE var_rent_id = ip_var_rent_id)
1549: AND inv.invoice_date = (SELECT MAX(invoice_date) FROM pn_var_rent_inv_all
1550: WHERE var_rent_id = ip_var_rent_id)
1551: AND inv.true_up_amt is NULL;
1552:
1553:
1580: CURSOR inv_in_prd_c(ip_var_rent_id NUMBER,
1581: ip_period_id NUMBER
1582: ) IS
1583: SELECT COUNT(UNIQUE(invoice_date)) num_inv
1584: FROM pn_var_rent_inv_all
1585: WHERE var_rent_id = ip_var_rent_id
1586: AND period_id = ip_period_id;
1587:
1588: -- Get the number of inv which should exist for a period
1588: -- Get the number of inv which should exist for a period
1589: CURSOR num_inv_c(ip_var_rent_inv_id NUMBER
1590: ) IS
1591: SELECT ceil(months_between(per.end_date, per.start_date)/decode(vrd.invg_freq_code,'YR', 12, 'SA', 6, 'QTR', 3, 'MON', 1, 1)) num_inv
1592: FROM pn_var_rent_inv_all inv, pn_var_periods_all per, pn_var_rents_all vr, pn_var_rent_dates_all vrd
1593: WHERE per.period_id = inv.period_id
1594: AND inv.var_rent_inv_id = ip_var_rent_inv_id
1595: AND per.var_rent_id = vr.var_rent_id
1596: AND vrd.var_rent_id = vr.var_rent_id;
1602: inv.var_rent_inv_id,
1603: inv.period_id
1604: FROM pn_var_rents_all vr,
1605: pn_var_periods_all per,
1606: pn_var_rent_inv_all inv
1607: WHERE per.var_rent_id = vr.var_rent_id
1608: AND inv.period_id = per.period_id
1609: AND per.start_date = (select max(start_date) from pn_var_periods_all per1
1610: where per1.var_rent_id = vr.var_rent_id)
1607: WHERE per.var_rent_id = vr.var_rent_id
1608: AND inv.period_id = per.period_id
1609: AND per.start_date = (select max(start_date) from pn_var_periods_all per1
1610: where per1.var_rent_id = vr.var_rent_id)
1611: AND inv.invoice_date = (select max(invoice_date) from pn_var_rent_inv_all inv1
1612: where inv1.period_id = per.period_id)
1613: AND vr.var_rent_id = ip_vr_id;
1614:
1615: -- Get the proration type
1622: -- Get the details of
1623: CURSOR rec_abatement_c(ip_period_id NUMBER
1624: ) IS
1625: SELECT SUM(rec_abatement) AS AMOUNT
1626: FROM pn_var_rent_inv_all inv1
1627: WHERE period_id = ip_period_id
1628: AND inv1.adjust_num= (
1629: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
1630: where inv1.var_rent_id = inv2.var_rent_id
1625: SELECT SUM(rec_abatement) AS AMOUNT
1626: FROM pn_var_rent_inv_all inv1
1627: WHERE period_id = ip_period_id
1628: AND inv1.adjust_num= (
1629: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
1630: where inv1.var_rent_id = inv2.var_rent_id
1631: AND inv1.invoice_date = inv2.invoice_date
1632: AND true_up_amt IS NULL)
1633: AND true_up_amt IS NULL;
1635: -- Get the details of
1636: CURSOR get_rec_abat_over_tu_c(p_period_id NUMBER
1637: ) IS
1638: SELECT rec_abatement_override
1639: FROM pn_var_rent_inv_all
1640: WHERE period_id = p_period_id
1641: AND true_up_amt is NOT NULL
1642: AND adjust_num = (SELECT max(adjust_num)
1643: FROM PN_VAR_RENT_INV_ALL
1639: FROM pn_var_rent_inv_all
1640: WHERE period_id = p_period_id
1641: AND true_up_amt is NOT NULL
1642: AND adjust_num = (SELECT max(adjust_num)
1643: FROM PN_VAR_RENT_INV_ALL
1644: WHERE period_id = p_period_id
1645: AND TRUE_UP_AMT IS NOT NULL);
1646:
1647:
1818: l_abated_rent := x_abated_rent - l_total_abat;
1819: END IF;
1820: l_total_abat := l_unabated_rent - l_abated_rent;
1821: pnp_debug_pkg.log('total_abat_applied:'||l_total_abat);
1822: UPDATE pn_var_rent_inv_all
1823: SET rec_abatement = l_total_abat
1824: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
1825: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
1826: x_abated_rent := l_abated_rent;
1867: SELECT inv1.abatement_appl
1868: ,inv1.invoice_date
1869: ,inv1.var_rent_inv_id
1870: ,inv1.true_up_amt
1871: FROM pn_var_rent_inv_all inv1
1872: WHERE var_rent_id = ip_var_rent_id
1873: AND period_id = ip_period_id
1874: AND var_rent_inv_id = ip_inv_id
1875: AND inv1.adjust_num= (
1872: WHERE var_rent_id = ip_var_rent_id
1873: AND period_id = ip_period_id
1874: AND var_rent_inv_id = ip_inv_id
1875: AND inv1.adjust_num= (
1876: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
1877: where inv1.var_rent_id = inv2.var_rent_id
1878: AND inv1.period_id = inv2.period_id
1879: AND inv1.invoice_date = inv2.invoice_date)
1880: AND TRUE_UP_AMT IS NULL
1932: -- Get the details of actual start and end date for FY/LY/FLY
1933: CURSOR invoice_dates_fyly_c(ip_var_rent_inv_id NUMBER
1934: ) IS
1935: SELECT per.start_date, per.end_date
1936: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1937: WHERE per.period_id = inv.period_id
1938: AND inv.var_rent_inv_id = ip_var_rent_inv_id;
1939: -- Get the id of invoice created for first year.
1940: -- This has meaning only if proration is FY/FLY
1940: -- This has meaning only if proration is FY/FLY
1941: CURSOR get_fy_inv_c(ip_var_rent_id NUMBER
1942: ) IS
1943: SELECT inv.var_rent_inv_id
1944: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1945: WHERE per.period_id = inv.period_id
1946: AND inv.var_rent_id = ip_var_rent_id
1947: AND per.start_date = (SELECT MIN(start_date) from pn_var_periods_all
1948: WHERE var_rent_id = ip_var_rent_id);
1950: -- This has meaning only if proration is LY/FLY
1951: CURSOR get_ly_inv_c(ip_var_rent_id NUMBER
1952: ) IS
1953: SELECT inv.var_rent_inv_id
1954: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
1955: WHERE per.period_id = inv.period_id
1956: AND inv.var_rent_id = ip_var_rent_id
1957: AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
1958: WHERE var_rent_id = ip_var_rent_id)
1955: WHERE per.period_id = inv.period_id
1956: AND inv.var_rent_id = ip_var_rent_id
1957: AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
1958: WHERE var_rent_id = ip_var_rent_id)
1959: AND inv.invoice_date = (SELECT MAX(invoice_date) from pn_var_rent_inv_all
1960: WHERE var_rent_id = ip_var_rent_id);
1961:
1962: -- Get the proration type
1963: CURSOR proration_type_c(ip_var_rent_id NUMBER
2047: WHERE abatement_id = allow_rec.abatement_id;
2048: END IF;
2049: END LOOP;
2050: pnp_debug_pkg.log('l_cur_abt_rent:'||l_cur_abt_rent);
2051: UPDATE pn_var_rent_inv_all
2052: SET abatement_appl = l_allow_applied_inv
2053: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
2054: x_abated_rent := l_cur_abt_rent;
2055: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
2117:
2118: --------------------------------------------------------------------------------
2119: -- NAME : populate_abat
2120: -- DESCRIPTION : Populates the recurring abatements to abte in
2121: -- pn_var_rent_inv_all.rec_abatement.
2122: -- PURPOSE :
2123: -- INVOKED FROM : apply_abatements()
2124: -- ARGUMENTS : p_var_rent_id: Vr to apply abatements for.
2125: -- p_period_id: Period to calculate for.
2140: ) IS
2141: SELECT invoice_date
2142: ,true_up_amt
2143: ,var_rent_inv_id
2144: FROM pn_var_rent_inv_all inv1
2145: WHERE inv1.var_rent_id = ip_var_rent_id
2146: AND inv1.period_id = ip_period_id
2147: AND var_rent_inv_id = ip_inv_id
2148: AND inv1.adjust_num= (
2145: WHERE inv1.var_rent_id = ip_var_rent_id
2146: AND inv1.period_id = ip_period_id
2147: AND var_rent_inv_id = ip_inv_id
2148: AND inv1.adjust_num= (
2149: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
2150: where inv1.var_rent_id = inv2.var_rent_id
2151: AND inv1.invoice_date = inv2.invoice_date)
2152: AND TRUE_UP_AMT IS NULL
2153: UNION ALL
2184:
2185: CURSOR get_first_tu_c(ip_period_id NUMBER
2186: ) IS
2187: SELECT var_rent_inv_id
2188: FROM pn_var_rent_inv_all
2189: WHERE period_id = ip_period_id
2190: AND true_up_amt is NOT NULL
2191: AND adjust_num = 0;
2192:
2279: pnp_debug_pkg.log('Reset_abatement start(+)');
2280: UPDATE pn_var_abat_defaults_all
2281: SET allowance_applied =0
2282: WHERE var_rent_id = p_var_rent_id;
2283: UPDATE pn_var_rent_inv_all
2284: SET abatement_appl =0
2285: WHERE var_rent_id = p_var_rent_id;
2286: pnp_debug_pkg.log('Reset_abatement end(-)');
2287: