[Home] [Help]
598: l_period_num NUMBER := 0;
599:
600: BEGIN
601:
602: pnp_debug_pkg.log('++++ get_rent_applicable - START ++++');
603:
604: IF NVL(p_trx_hdr_rec.invoice_flag, 'Y') = 'N' THEN
605: RETURN;
606: END IF;
646: l_net_volume := (l_trx_hdr_rec.ytd_sales - NVL(l_trx_hdr_rec.ytd_deductions,0));
647:
648: END IF;
649:
650: pnp_debug_pkg.log(l_context||' COMPLETE - net Volume: '||l_net_volume);
651:
652:
653: /* --------------------- GET calculated_rent START --------------------- */
654: /* handling the case of volume not tripping any bkpts */
671: (g_proration_rule = pn_var_rent_calc_pkg.G_PRORUL_CYNP AND
672: l_net_volume < l_bkpts_t(1).pr_grp_blended_vol_start))
673: THEN
674:
675: pnp_debug_pkg.log('No volumes tripped.');
676: pnp_debug_pkg.log('l_net_volume:'||l_net_volume);
677: pnp_debug_pkg.log
678: ('l_bkpts_t(1).prorated_grp_vol_start:'||l_bkpts_t(1).prorated_grp_vol_start);
679: /* Once no breakpoints are tripped, Macerich consider the rent to be = 0 */
672: l_net_volume < l_bkpts_t(1).pr_grp_blended_vol_start))
673: THEN
674:
675: pnp_debug_pkg.log('No volumes tripped.');
676: pnp_debug_pkg.log('l_net_volume:'||l_net_volume);
677: pnp_debug_pkg.log
678: ('l_bkpts_t(1).prorated_grp_vol_start:'||l_bkpts_t(1).prorated_grp_vol_start);
679: /* Once no breakpoints are tripped, Macerich consider the rent to be = 0 */
680: IF (g_proration_rule IN ( pn_var_rent_calc_pkg.G_PRORUL_NP
673: THEN
674:
675: pnp_debug_pkg.log('No volumes tripped.');
676: pnp_debug_pkg.log('l_net_volume:'||l_net_volume);
677: pnp_debug_pkg.log
678: ('l_bkpts_t(1).prorated_grp_vol_start:'||l_bkpts_t(1).prorated_grp_vol_start);
679: /* Once no breakpoints are tripped, Macerich consider the rent to be = 0 */
680: IF (g_proration_rule IN ( pn_var_rent_calc_pkg.G_PRORUL_NP
681: ,pn_var_rent_calc_pkg.G_PRORUL_STD
693: := (l_net_volume - l_bkpts_t(1).pr_grp_blended_vol_start)
694: * l_bkpts_t(1).bkpt_rate;
695:
696: END IF;
697: pnp_debug_pkg.log('l_trx_hdr_rec.calculated_rent'||l_trx_hdr_rec.calculated_rent);
698:
699:
700: ELSE
701:
853: END IF; /* breakpoint type */
854:
855: END IF; /* volume trips breakpoint?? */
856:
857: pnp_debug_pkg.log(l_context||' COMPLETE ');
858:
859: /* ---------------------- GET calculated_rent END ---------------------- */
860:
861: /* at this point, we have the calculated rent
872: l_trx_hdr_rec.prorated_rent_due := 0;
873:
874: IF NVL(l_trx_hdr_rec.invoice_flag, 'Y') = 'I' THEN
875:
876: pnp_debug_pkg.log('+++ Get Prorated Rent - START +++');
877: pnp_debug_pkg.log('Invoice Flag: '||l_trx_hdr_rec.invoice_flag);
878:
879: IF g_proration_rule IN ( pn_var_rent_calc_pkg.G_PRORUL_FY
880: ,pn_var_rent_calc_pkg.G_PRORUL_FLY) AND
873:
874: IF NVL(l_trx_hdr_rec.invoice_flag, 'Y') = 'I' THEN
875:
876: pnp_debug_pkg.log('+++ Get Prorated Rent - START +++');
877: pnp_debug_pkg.log('Invoice Flag: '||l_trx_hdr_rec.invoice_flag);
878:
879: IF g_proration_rule IN ( pn_var_rent_calc_pkg.G_PRORUL_FY
880: ,pn_var_rent_calc_pkg.G_PRORUL_FLY) AND
881: ((ADD_MONTHS(g_vr_commencement_date, 12) - 1) BETWEEN
881: ((ADD_MONTHS(g_vr_commencement_date, 12) - 1) BETWEEN
882: l_trx_hdr_rec.calc_prd_start_date AND l_trx_hdr_rec.calc_prd_end_date)
883: THEN
884:
885: pnp_debug_pkg.log('Get Prorated Rent - FY');
886:
887: /* calculate FY prorated rent due */
888: FOR rec IN fy_sales_c
889: ( p_vr_id => l_trx_hdr_rec.var_rent_id
1056: ,pn_var_rent_calc_pkg.G_PRORUL_FLY) AND
1057: g_vr_termination_date = l_trx_hdr_rec.calc_prd_end_date
1058: THEN
1059:
1060: pnp_debug_pkg.log('Get Prorated Rent - LY');
1061:
1062: /* calculate LY prorated rent due */
1063: /* set the calculated rent to 0 */
1064: l_trx_hdr_rec.calculated_rent := 0;
1070: LOOP
1071: l_net_volume := rec.sales;
1072: END LOOP;
1073:
1074: pnp_debug_pkg.log('Sales volume for LY: '||l_net_volume);
1075:
1076:
1077:
1078: /* get l_trx_hdr_rec.prorated_rent_due - PRORATED RENT */
1080: THEN
1081:
1082: l_trx_hdr_rec.prorated_rent_due := 0;
1083:
1084: pnp_debug_pkg.log('Get bkpt trips for');
1085: pnp_debug_pkg.log(' VR ID: '||l_trx_hdr_rec.var_rent_id);
1086: pnp_debug_pkg.log(' VR End Date: '|| g_vr_termination_date);
1087: pnp_debug_pkg.log(' Line Grp: '||l_trx_hdr_rec.line_item_group_id);
1088:
1081:
1082: l_trx_hdr_rec.prorated_rent_due := 0;
1083:
1084: pnp_debug_pkg.log('Get bkpt trips for');
1085: pnp_debug_pkg.log(' VR ID: '||l_trx_hdr_rec.var_rent_id);
1086: pnp_debug_pkg.log(' VR End Date: '|| g_vr_termination_date);
1087: pnp_debug_pkg.log(' Line Grp: '||l_trx_hdr_rec.line_item_group_id);
1088:
1089: /* loop for all bkpt details */
1082: l_trx_hdr_rec.prorated_rent_due := 0;
1083:
1084: pnp_debug_pkg.log('Get bkpt trips for');
1085: pnp_debug_pkg.log(' VR ID: '||l_trx_hdr_rec.var_rent_id);
1086: pnp_debug_pkg.log(' VR End Date: '|| g_vr_termination_date);
1087: pnp_debug_pkg.log(' Line Grp: '||l_trx_hdr_rec.line_item_group_id);
1088:
1089: /* loop for all bkpt details */
1090: FOR bkpt_rec IN ly_bkpts_c
1083:
1084: pnp_debug_pkg.log('Get bkpt trips for');
1085: pnp_debug_pkg.log(' VR ID: '||l_trx_hdr_rec.var_rent_id);
1086: pnp_debug_pkg.log(' VR End Date: '|| g_vr_termination_date);
1087: pnp_debug_pkg.log(' Line Grp: '||l_trx_hdr_rec.line_item_group_id);
1088:
1089: /* loop for all bkpt details */
1090: FOR bkpt_rec IN ly_bkpts_c
1091: ( p_vr_id => l_trx_hdr_rec.var_rent_id
1099: IF l_bkpt_end IS NULL OR l_bkpt_end = 0 THEN
1100: l_bkpt_end := NULL;
1101: END IF;
1102:
1103: pnp_debug_pkg.log('Bkpt trips - Start: '||l_bkpt_start||' End: '||l_bkpt_end);
1104:
1105: IF l_net_volume >= l_bkpt_start THEN
1106:
1107: IF l_net_volume <= NVL(l_bkpt_end, l_net_volume) THEN
1117: + (l_bkpt_end - l_bkpt_start) * bkpt_rec.bkpt_rate;
1118:
1119: END IF;
1120:
1121: pnp_debug_pkg.log('Prorated Rent Due: '||l_trx_hdr_rec.prorated_rent_due);
1122:
1123: ELSE
1124:
1125: EXIT;
1131:
1132: ELSIF l_bkpt_type IN ( pn_var_rent_calc_pkg.G_BKPT_TYP_FLAT
1133: ,pn_var_rent_calc_pkg.G_BKPT_TYP_SLIDING)
1134: THEN
1135: pnp_debug_pkg.log('l_bkpt_type:'||l_bkpt_type);
1136: /* loop for all bkpt details */
1137: FOR bkpt_rec IN ly_bkpts_c
1138: ( p_vr_id => l_trx_hdr_rec.var_rent_id
1139: ,p_vr_term_date => g_vr_termination_date
1141: LOOP
1142:
1143: l_bkpt_start := bkpt_rec.bkpt_start;
1144: l_bkpt_end := bkpt_rec.bkpt_end;
1145: pnp_debug_pkg.log('1:'||bkpt_rec.bkpt_start);
1146: pnp_debug_pkg.log('2:'||bkpt_rec.bkpt_end);
1147:
1148: IF l_bkpt_end IS NULL OR l_bkpt_end = 0 THEN
1149: l_bkpt_end := NULL;
1142:
1143: l_bkpt_start := bkpt_rec.bkpt_start;
1144: l_bkpt_end := bkpt_rec.bkpt_end;
1145: pnp_debug_pkg.log('1:'||bkpt_rec.bkpt_start);
1146: pnp_debug_pkg.log('2:'||bkpt_rec.bkpt_end);
1147:
1148: IF l_bkpt_end IS NULL OR l_bkpt_end = 0 THEN
1149: l_bkpt_end := NULL;
1150: END IF;
1241: END LOOP;
1242: */
1243: END IF; /* calculate FY rent OR LY rent? */
1244:
1245: pnp_debug_pkg.log('+++ Get Prorated Rent - END +++');
1246:
1247: END IF; /* invoice flag = I - means calculate prorated_rent_due */
1248:
1249: pnp_debug_pkg.log(l_context||' COMPLETE ');
1245: pnp_debug_pkg.log('+++ Get Prorated Rent - END +++');
1246:
1247: END IF; /* invoice flag = I - means calculate prorated_rent_due */
1248:
1249: pnp_debug_pkg.log(l_context||' COMPLETE ');
1250:
1251: /* --------------------- GET prorated_rent_due END ---------------------- */
1252:
1253: /* ------------ GET percent_rent_due, ytd_percent_rent START ------------ */
1276: ,p_reset_grp_id => l_trx_hdr_rec.reset_group_id);
1277:
1278: END IF;
1279:
1280: pnp_debug_pkg.log(' get previously billed complete - l_prev_billed: '||l_prev_billed);
1281:
1282: IF g_calculation_method IN ( pn_var_rent_calc_pkg.G_CALC_NON_CUMULATIVE
1283: ,pn_var_rent_calc_pkg.G_CALC_TRUE_UP)
1284: THEN
1343:
1344: END IF;
1345:
1346:
1347: pnp_debug_pkg.log(' ');
1348: pnp_debug_pkg.log(' calculation_method: '||g_calculation_method);
1349: pnp_debug_pkg.log(' percent_rent_due_for: '||l_trx_hdr_rec.percent_rent_due);
1350: pnp_debug_pkg.log(' ytd_percent_rent_for: '||l_trx_hdr_rec.ytd_percent_rent);
1351: pnp_debug_pkg.log(' ');
1344: END IF;
1345:
1346:
1347: pnp_debug_pkg.log(' ');
1348: pnp_debug_pkg.log(' calculation_method: '||g_calculation_method);
1349: pnp_debug_pkg.log(' percent_rent_due_for: '||l_trx_hdr_rec.percent_rent_due);
1350: pnp_debug_pkg.log(' ytd_percent_rent_for: '||l_trx_hdr_rec.ytd_percent_rent);
1351: pnp_debug_pkg.log(' ');
1352:
1345:
1346:
1347: pnp_debug_pkg.log(' ');
1348: pnp_debug_pkg.log(' calculation_method: '||g_calculation_method);
1349: pnp_debug_pkg.log(' percent_rent_due_for: '||l_trx_hdr_rec.percent_rent_due);
1350: pnp_debug_pkg.log(' ytd_percent_rent_for: '||l_trx_hdr_rec.ytd_percent_rent);
1351: pnp_debug_pkg.log(' ');
1352:
1353: pnp_debug_pkg.log(l_context||' COMPLETE ');
1346:
1347: pnp_debug_pkg.log(' ');
1348: pnp_debug_pkg.log(' calculation_method: '||g_calculation_method);
1349: pnp_debug_pkg.log(' percent_rent_due_for: '||l_trx_hdr_rec.percent_rent_due);
1350: pnp_debug_pkg.log(' ytd_percent_rent_for: '||l_trx_hdr_rec.ytd_percent_rent);
1351: pnp_debug_pkg.log(' ');
1352:
1353: pnp_debug_pkg.log(l_context||' COMPLETE ');
1354:
1347: pnp_debug_pkg.log(' ');
1348: pnp_debug_pkg.log(' calculation_method: '||g_calculation_method);
1349: pnp_debug_pkg.log(' percent_rent_due_for: '||l_trx_hdr_rec.percent_rent_due);
1350: pnp_debug_pkg.log(' ytd_percent_rent_for: '||l_trx_hdr_rec.ytd_percent_rent);
1351: pnp_debug_pkg.log(' ');
1352:
1353: pnp_debug_pkg.log(l_context||' COMPLETE ');
1354:
1355: /* ------------- GET percent_rent_due, ytd_percent_rent END ------------- */
1349: pnp_debug_pkg.log(' percent_rent_due_for: '||l_trx_hdr_rec.percent_rent_due);
1350: pnp_debug_pkg.log(' ytd_percent_rent_for: '||l_trx_hdr_rec.ytd_percent_rent);
1351: pnp_debug_pkg.log(' ');
1352:
1353: pnp_debug_pkg.log(l_context||' COMPLETE ');
1354:
1355: /* ------------- GET percent_rent_due, ytd_percent_rent END ------------- */
1356:
1357: l_trx_hdr_rec.update_flag := 'Y';
1357: l_trx_hdr_rec.update_flag := 'Y';
1358:
1359: p_trx_hdr_rec := l_trx_hdr_rec;
1360:
1361: pnp_debug_pkg.log('++++ get_rent_applicable - END ++++');
1362:
1363: EXCEPTION
1364: WHEN OTHERS THEN
1365: pnp_debug_pkg.log
1361: pnp_debug_pkg.log('++++ get_rent_applicable - END ++++');
1362:
1363: EXCEPTION
1364: WHEN OTHERS THEN
1365: pnp_debug_pkg.log
1366: ('**********************************************************************');
1367: pnp_debug_pkg.log('*** ERROR IN get_rent_applicable ***');
1368: pnp_debug_pkg.log('*** ERROR WHEN: '||l_context||' ***');
1369: pnp_debug_pkg.log
1363: EXCEPTION
1364: WHEN OTHERS THEN
1365: pnp_debug_pkg.log
1366: ('**********************************************************************');
1367: pnp_debug_pkg.log('*** ERROR IN get_rent_applicable ***');
1368: pnp_debug_pkg.log('*** ERROR WHEN: '||l_context||' ***');
1369: pnp_debug_pkg.log
1370: ('**********************************************************************');
1371: RAISE;
1364: WHEN OTHERS THEN
1365: pnp_debug_pkg.log
1366: ('**********************************************************************');
1367: pnp_debug_pkg.log('*** ERROR IN get_rent_applicable ***');
1368: pnp_debug_pkg.log('*** ERROR WHEN: '||l_context||' ***');
1369: pnp_debug_pkg.log
1370: ('**********************************************************************');
1371: RAISE;
1372:
1365: pnp_debug_pkg.log
1366: ('**********************************************************************');
1367: pnp_debug_pkg.log('*** ERROR IN get_rent_applicable ***');
1368: pnp_debug_pkg.log('*** ERROR WHEN: '||l_context||' ***');
1369: pnp_debug_pkg.log
1370: ('**********************************************************************');
1371: RAISE;
1372:
1373: END get_rent_applicable;
1451: l_vr_summ_id NUMBER;
1452:
1453: BEGIN
1454:
1455: pnp_debug_pkg.log('post_summary .....(+)');
1456: /* get the invoice date for the group
1457: loops only once
1458: */
1459: FOR grp_rec IN grp_date_c( p_vr_id => p_var_rent_id
1459: FOR grp_rec IN grp_date_c( p_vr_id => p_var_rent_id
1460: ,p_prd_id => p_period_id
1461: ,p_grp_id => p_grp_date_id)
1462: LOOP
1463: pnp_debug_pkg.log('grp_rec.invoice_date...'||grp_rec.invoice_date);
1464: /* get the sum of rents and sales for
1465: vr -> period -> line item -> group combination
1466: from the trx tables
1467: loops only once
1471: ,p_line_id => p_line_item_id
1472: ,p_grp_id => p_grp_date_id)
1473: LOOP
1474:
1475: pnp_debug_pkg.log('summ_rec.rent...'||summ_rec.rent);
1476: pnp_debug_pkg.log('summ_rec.sales...'||summ_rec.sales);
1477: l_vr_summ_id := NULL;
1478:
1479: /* chk if VR SUMM record exists for this
1472: ,p_grp_id => p_grp_date_id)
1473: LOOP
1474:
1475: pnp_debug_pkg.log('summ_rec.rent...'||summ_rec.rent);
1476: pnp_debug_pkg.log('summ_rec.sales...'||summ_rec.sales);
1477: l_vr_summ_id := NULL;
1478:
1479: /* chk if VR SUMM record exists for this
1480: vr -> period -> line item -> group combination */
1485: LOOP
1486: l_vr_summ_id := vr_summ_rec.var_rent_summ_id;
1487: END LOOP;
1488:
1489: pnp_debug_pkg.log('l_vr_summ_id...'||l_vr_summ_id);
1490:
1491: IF l_vr_summ_id IS NULL THEN
1492:
1493: pnp_debug_pkg.log('inserting ...');
1489: pnp_debug_pkg.log('l_vr_summ_id...'||l_vr_summ_id);
1490:
1491: IF l_vr_summ_id IS NULL THEN
1492:
1493: pnp_debug_pkg.log('inserting ...');
1494: /* to insert a new summary record */
1495: INSERT INTO
1496: pn_var_rent_summ_all
1497: (var_rent_summ_id
1534:
1535: ELSIF l_vr_summ_id IS NOT NULL THEN
1536: /* update the summary record */
1537:
1538: pnp_debug_pkg.log('updating ...');
1539: UPDATE
1540: pn_var_rent_summ_all
1541: SET
1542: tot_act_vol = summ_rec.sales
1635: l_line_item_id NUMBER;
1636:
1637: BEGIN
1638:
1639: pnp_debug_pkg.log('+++++++++ post_summary_firstyr START +++++++++++');
1640:
1641:
1642: FOR first_period_rec IN first_period_cur LOOP
1643: l_period_id := first_period_rec.period_id;
1642: FOR first_period_rec IN first_period_cur LOOP
1643: l_period_id := first_period_rec.period_id;
1644: END LOOP;
1645:
1646: pnp_debug_pkg.log('first year period id... '||l_period_id);
1647: /* get the first year rent for this vr agreement */
1648: FOR vr_summ_fy_rec IN vr_summ_fy_c LOOP
1649:
1650: l_vr_summ_id := NULL;
1736: EXIT;
1737:
1738: END LOOP;
1739:
1740: pnp_debug_pkg.log('+++++++++ post_summary_firstyr END +++++++++++');
1741:
1742: END post_summary_firstyr;
1743:
1744: --------------------------------------------------------------------------------
1856: l_tot_act_vol NUMBER := 0;
1857: l_precision NUMBER;
1858: BEGIN
1859:
1860: pnp_debug_pkg.log('+++++++++ insert_invoice_firstyr START +++++++++++');
1861:
1862: FOR vr_rec IN org_c(p_vr_id => p_var_rent_id) LOOP
1863: l_org_id := vr_rec.org_id;
1864: l_vr_commencement_date := vr_rec.commencement_date;
1863: l_org_id := vr_rec.org_id;
1864: l_vr_commencement_date := vr_rec.commencement_date;
1865: END LOOP;
1866: l_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(l_org_id),4);
1867: pnp_debug_pkg.log('l_precision:'||l_precision);
1868: FOR first_period_rec IN first_period_cur(p_vr_id => p_var_rent_id) LOOP
1869: l_period_id := first_period_rec.period_id;
1870: END LOOP;
1871:
1868: FOR first_period_rec IN first_period_cur(p_vr_id => p_var_rent_id) LOOP
1869: l_period_id := first_period_rec.period_id;
1870: END LOOP;
1871:
1872: pnp_debug_pkg.log('first year period id... '||l_period_id);
1873:
1874: /* loop for all invoice dates in the period */
1875: FOR inv_rec IN invoice_dates_c( p_vr_id => p_var_rent_id)
1876: LOOP
1915: EXIT;
1916:
1917: END LOOP; /* check if there exists an invoice for this invoice date */
1918:
1919: pnp_debug_pkg.log('l_exists_invoice...');
1920: /* atleast one invoice exists? */
1921: IF NOT l_exists_invoice
1922: AND round(inv_rec.total_actual_rent, l_precision) <> 0 THEN
1923:
1929: p_period_id => l_period_id,
1930: p_invoice_date => inv_rec.invoice_date,
1931: p_actual_rent => inv_rec.total_actual_rent);
1932:
1933: pnp_debug_pkg.log('inserting the row ...');
1934: /* first time for this invoice date - create invoice */
1935: pn_var_rent_inv_pkg.insert_row
1936: ( x_rowid => l_row_id,
1937: x_var_rent_inv_id => l_var_rent_inv_id,
1994: p_invoice_date => inv_rec.invoice_date,
1995: p_actual_rent => inv_rec.total_actual_rent);
1996: /* no invoice to update - create a new one */
1997: IF l_var_rent_inv_id IS NULL THEN
1998: pnp_debug_pkg.log('l_constr_rent:'||l_constr_rent);
1999: pnp_debug_pkg.log('l_constr_rent:'||l_constr_prev_rent);
2000: /* if there a change in rent */
2001: IF round(inv_rec.total_actual_rent, l_precision) <> round(l_prev_inv_rent, l_precision)
2002: OR round(l_constr_rent, l_precision) <> round(l_constr_prev_rent, l_precision)
1995: p_actual_rent => inv_rec.total_actual_rent);
1996: /* no invoice to update - create a new one */
1997: IF l_var_rent_inv_id IS NULL THEN
1998: pnp_debug_pkg.log('l_constr_rent:'||l_constr_rent);
1999: pnp_debug_pkg.log('l_constr_rent:'||l_constr_prev_rent);
2000: /* if there a change in rent */
2001: IF round(inv_rec.total_actual_rent, l_precision) <> round(l_prev_inv_rent, l_precision)
2002: OR round(l_constr_rent, l_precision) <> round(l_constr_prev_rent, l_precision)
2003: THEN
2087: END IF; /* IF NOT l_exists_invoice THEN */
2088:
2089: END LOOP; /* loop for all invoice dates in the period */
2090:
2091: pnp_debug_pkg.log('+++++++++ insert_invoice END +++++++++++');
2092:
2093: EXCEPTION
2094: WHEN OTHERS THEN RAISE;
2095:
2230: l_period_id NUMBER;
2231: l_precision NUMBER;
2232: BEGIN
2233:
2234: pnp_debug_pkg.log('+++++++++ insert_invoice START (+) +++++++++++');
2235:
2236: FOR vr_rec IN org_c(p_vr_id => p_var_rent_id) LOOP
2237: l_org_id := vr_rec.org_id;
2238: l_vr_termination_date := vr_rec.termination_date;
2238: l_vr_termination_date := vr_rec.termination_date;
2239: END LOOP;
2240:
2241: l_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(l_org_id),4);
2242: pnp_debug_pkg.log('l_precision:'||l_precision);
2243:
2244: /* loop for all invoice dates in the period */
2245: FOR inv_rec IN invoice_dates_c( p_vr_id => p_var_rent_id
2246: ,p_prd_id => p_period_id
2245: FOR inv_rec IN invoice_dates_c( p_vr_id => p_var_rent_id
2246: ,p_prd_id => p_period_id
2247: ,p_new_termn_date => l_vr_termination_date)
2248: LOOP
2249: pnp_debug_pkg.log('inv_rec.invoice_date ...'||inv_rec.invoice_date);
2250: pnp_debug_pkg.log('inv_rec.total_actual_rent ...'||inv_rec.total_actual_rent);
2251:
2252: l_row_id := NULL;
2253: l_var_rent_inv_id := NULL;
2246: ,p_prd_id => p_period_id
2247: ,p_new_termn_date => l_vr_termination_date)
2248: LOOP
2249: pnp_debug_pkg.log('inv_rec.invoice_date ...'||inv_rec.invoice_date);
2250: pnp_debug_pkg.log('inv_rec.total_actual_rent ...'||inv_rec.total_actual_rent);
2251:
2252: l_row_id := NULL;
2253: l_var_rent_inv_id := NULL;
2254: l_max_adjust_num := 0;
2400: p_actual_rent => inv_rec.total_actual_rent);
2401:
2402: /* no invoice to update - create a new one */
2403: IF l_var_rent_inv_id IS NULL THEN
2404: pnp_debug_pkg.log('l_constr_rent:'||l_constr_rent);
2405: pnp_debug_pkg.log('l_constr_rent:'||l_constr_prev_rent);
2406: /* if there a change in rent */
2407: IF round(inv_rec.total_actual_rent, l_precision) <> round(l_prev_inv_rent, l_precision)
2408: OR round(l_constr_rent, l_precision) <> round(l_constr_prev_rent, l_precision)
2401:
2402: /* no invoice to update - create a new one */
2403: IF l_var_rent_inv_id IS NULL THEN
2404: pnp_debug_pkg.log('l_constr_rent:'||l_constr_rent);
2405: pnp_debug_pkg.log('l_constr_rent:'||l_constr_prev_rent);
2406: /* if there a change in rent */
2407: IF round(inv_rec.total_actual_rent, l_precision) <> round(l_prev_inv_rent, l_precision)
2408: OR round(l_constr_rent, l_precision) <> round(l_constr_prev_rent, l_precision)
2409: THEN
2491:
2492: END IF; /* IF l_var_rent_inv_id IS NULL THEN */
2493:
2494: END IF; /* IF NOT l_exists_invoice THEN */
2495: pnp_debug_pkg.log('l_prev_inv_exp_rent:'||l_prev_inv_exp_rent);
2496:
2497: END LOOP; /* loop for all invoice dates in the period */
2498:
2499: pnp_debug_pkg.log('+++++++++ insert_invoice END +++++++++++');
2495: pnp_debug_pkg.log('l_prev_inv_exp_rent:'||l_prev_inv_exp_rent);
2496:
2497: END LOOP; /* loop for all invoice dates in the period */
2498:
2499: pnp_debug_pkg.log('+++++++++ insert_invoice END +++++++++++');
2500:
2501: EXCEPTION
2502: WHEN OTHERS THEN RAISE;
2503:
2538: l_upper_bound NUMBER;
2539: l_constr_act_rent NUMBER;
2540:
2541: BEGIN
2542: pnp_debug_pkg.log( 'pn_variable_amount_pkg.apply_constraints : (+) ');
2543:
2544: FOR rec_get_constr in csr_get_constr(p_period_id)
2545: LOOP
2546: IF rec_get_constr.TYPE_code = 'MIN' THEN
2551: END LOOP;
2552:
2553: /* Apply constraints to Actual variable rent */
2554:
2555: pnp_debug_pkg.log('apply_constraints - Lower bound :'|| l_lower_bound);
2556: pnp_debug_pkg.log('apply_constraints - Upper bound :'|| l_upper_bound);
2557: pnp_debug_pkg.log('apply_constraints - Actual Rent :'|| p_actual_rent);
2558:
2559: IF p_actual_rent < NVL(l_lower_bound,p_actual_rent) THEN
2552:
2553: /* Apply constraints to Actual variable rent */
2554:
2555: pnp_debug_pkg.log('apply_constraints - Lower bound :'|| l_lower_bound);
2556: pnp_debug_pkg.log('apply_constraints - Upper bound :'|| l_upper_bound);
2557: pnp_debug_pkg.log('apply_constraints - Actual Rent :'|| p_actual_rent);
2558:
2559: IF p_actual_rent < NVL(l_lower_bound,p_actual_rent) THEN
2560: l_constr_act_rent := l_lower_bound;
2553: /* Apply constraints to Actual variable rent */
2554:
2555: pnp_debug_pkg.log('apply_constraints - Lower bound :'|| l_lower_bound);
2556: pnp_debug_pkg.log('apply_constraints - Upper bound :'|| l_upper_bound);
2557: pnp_debug_pkg.log('apply_constraints - Actual Rent :'|| p_actual_rent);
2558:
2559: IF p_actual_rent < NVL(l_lower_bound,p_actual_rent) THEN
2560: l_constr_act_rent := l_lower_bound;
2561: ELSIF p_actual_rent > NVL(l_upper_bound,p_actual_rent) THEN
2563: ELSE
2564: l_constr_act_rent := p_actual_rent;
2565: END IF;
2566:
2567: pnp_debug_pkg.log('apply_constraints - Constrained Actual rent :'||l_constr_act_rent);
2568:
2569: RETURN l_constr_act_rent;
2570: pnp_debug_pkg.log( 'pn_variable_amount_pkg.apply_constraints : (-) ');
2571: END apply_constraints;
2566:
2567: pnp_debug_pkg.log('apply_constraints - Constrained Actual rent :'||l_constr_act_rent);
2568:
2569: RETURN l_constr_act_rent;
2570: pnp_debug_pkg.log( 'pn_variable_amount_pkg.apply_constraints : (-) ');
2571: END apply_constraints;
2572:
2573: --------------------------------------------------------------------------------
2574: -- NAME : apply_constraints_fy
2602: l_upper_bound NUMBER;
2603: l_constr_act_rent NUMBER;
2604:
2605: BEGIN
2606: pnp_debug_pkg.log( 'pn_variable_amount_pkg.apply_constraints_fy : (+) ');
2607: pnp_debug_pkg.log('p_period_id'||p_period_id);
2608: FOR rec_get_constr in csr_get_constr(p_period_id)
2609: LOOP
2610: IF rec_get_constr.TYPE_code = 'MIN' THEN
2603: l_constr_act_rent NUMBER;
2604:
2605: BEGIN
2606: pnp_debug_pkg.log( 'pn_variable_amount_pkg.apply_constraints_fy : (+) ');
2607: pnp_debug_pkg.log('p_period_id'||p_period_id);
2608: FOR rec_get_constr in csr_get_constr(p_period_id)
2609: LOOP
2610: IF rec_get_constr.TYPE_code = 'MIN' THEN
2611: l_lower_bound := rec_get_constr.amount;
2615: END LOOP;
2616:
2617: /* Apply constraints to Actual variable rent */
2618:
2619: pnp_debug_pkg.log('apply_constraints - Lower bound :'|| l_lower_bound);
2620: pnp_debug_pkg.log('apply_constraints - Upper bound :'|| l_upper_bound);
2621: pnp_debug_pkg.log('apply_constraints - Actual Rent :'|| p_actual_rent);
2622:
2623: IF p_actual_rent < NVL(l_lower_bound,p_actual_rent) THEN
2616:
2617: /* Apply constraints to Actual variable rent */
2618:
2619: pnp_debug_pkg.log('apply_constraints - Lower bound :'|| l_lower_bound);
2620: pnp_debug_pkg.log('apply_constraints - Upper bound :'|| l_upper_bound);
2621: pnp_debug_pkg.log('apply_constraints - Actual Rent :'|| p_actual_rent);
2622:
2623: IF p_actual_rent < NVL(l_lower_bound,p_actual_rent) THEN
2624: l_constr_act_rent := l_lower_bound;
2617: /* Apply constraints to Actual variable rent */
2618:
2619: pnp_debug_pkg.log('apply_constraints - Lower bound :'|| l_lower_bound);
2620: pnp_debug_pkg.log('apply_constraints - Upper bound :'|| l_upper_bound);
2621: pnp_debug_pkg.log('apply_constraints - Actual Rent :'|| p_actual_rent);
2622:
2623: IF p_actual_rent < NVL(l_lower_bound,p_actual_rent) THEN
2624: l_constr_act_rent := l_lower_bound;
2625: ELSIF p_actual_rent > NVL(l_upper_bound,p_actual_rent) THEN
2627: ELSE
2628: l_constr_act_rent := p_actual_rent;
2629: END IF;
2630:
2631: pnp_debug_pkg.log('apply_constraints - Constrained Actual rent :'||l_constr_act_rent);
2632:
2633: RETURN l_constr_act_rent;
2634: pnp_debug_pkg.log( 'pn_variable_amount_pkg.apply_constraints : (-) ');
2635: END apply_constraints_fy;
2630:
2631: pnp_debug_pkg.log('apply_constraints - Constrained Actual rent :'||l_constr_act_rent);
2632:
2633: RETURN l_constr_act_rent;
2634: pnp_debug_pkg.log( 'pn_variable_amount_pkg.apply_constraints : (-) ');
2635: END apply_constraints_fy;
2636:
2637:
2638: /***************Procedures to apply abatements***********/
2732: 2. Apply allowances/Abatements.
2733: 3. Apply/Allowances/Abatements.
2734: */
2735: --
2736: pnp_debug_pkg.log('apply_abatements start(+)');
2737: pnp_debug_pkg.log('p_flag:'||p_flag);
2738: FOR rec IN order_c(p_var_rent_id) LOOP
2739: l_abat_order := rec.ORDER_OF_APPL_CODE;
2740: l_vr_termination_date := rec.termination_date;
2733: 3. Apply/Allowances/Abatements.
2734: */
2735: --
2736: pnp_debug_pkg.log('apply_abatements start(+)');
2737: pnp_debug_pkg.log('p_flag:'||p_flag);
2738: FOR rec IN order_c(p_var_rent_id) LOOP
2739: l_abat_order := rec.ORDER_OF_APPL_CODE;
2740: l_vr_termination_date := rec.termination_date;
2741: END LOOP;
2740: l_vr_termination_date := rec.termination_date;
2741: END LOOP;
2742: --
2743: FOR inv_rec IN inv_c(p_var_rent_id, p_period_id, l_vr_termination_date) LOOP
2744: pnp_debug_pkg.log('called apply_def_neg_rent');
2745: pn_var_rent_calc_pkg.apply_def_neg_rent(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2746: pnp_debug_pkg.log('complete');
2747: IF (l_abat_order = pn_var_rent_calc_pkg.G_ALLOWANCE_FIRST) THEN
2748: pnp_debug_pkg.log('call pnp_debug_pkg.log');
2742: --
2743: FOR inv_rec IN inv_c(p_var_rent_id, p_period_id, l_vr_termination_date) LOOP
2744: pnp_debug_pkg.log('called apply_def_neg_rent');
2745: pn_var_rent_calc_pkg.apply_def_neg_rent(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2746: pnp_debug_pkg.log('complete');
2747: IF (l_abat_order = pn_var_rent_calc_pkg.G_ALLOWANCE_FIRST) THEN
2748: pnp_debug_pkg.log('call pnp_debug_pkg.log');
2749: pn_var_rent_calc_pkg.apply_allow(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2750: pnp_debug_pkg.log('complete');
2744: pnp_debug_pkg.log('called apply_def_neg_rent');
2745: pn_var_rent_calc_pkg.apply_def_neg_rent(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2746: pnp_debug_pkg.log('complete');
2747: IF (l_abat_order = pn_var_rent_calc_pkg.G_ALLOWANCE_FIRST) THEN
2748: pnp_debug_pkg.log('call pnp_debug_pkg.log');
2749: pn_var_rent_calc_pkg.apply_allow(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2750: pnp_debug_pkg.log('complete');
2751: END IF;--Apply allowance.
2752:
2746: pnp_debug_pkg.log('complete');
2747: IF (l_abat_order = pn_var_rent_calc_pkg.G_ALLOWANCE_FIRST) THEN
2748: pnp_debug_pkg.log('call pnp_debug_pkg.log');
2749: pn_var_rent_calc_pkg.apply_allow(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2750: pnp_debug_pkg.log('complete');
2751: END IF;--Apply allowance.
2752:
2753: pnp_debug_pkg.log('call populate_abat');
2754: pn_var_rent_calc_pkg.populate_abat(p_var_rent_id , p_period_id, inv_rec.var_rent_inv_id);
2749: pn_var_rent_calc_pkg.apply_allow(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2750: pnp_debug_pkg.log('complete');
2751: END IF;--Apply allowance.
2752:
2753: pnp_debug_pkg.log('call populate_abat');
2754: pn_var_rent_calc_pkg.populate_abat(p_var_rent_id , p_period_id, inv_rec.var_rent_inv_id);
2755: pnp_debug_pkg.log('complete');
2756:
2757: pnp_debug_pkg.log('call apply_abat');
2751: END IF;--Apply allowance.
2752:
2753: pnp_debug_pkg.log('call populate_abat');
2754: pn_var_rent_calc_pkg.populate_abat(p_var_rent_id , p_period_id, inv_rec.var_rent_inv_id);
2755: pnp_debug_pkg.log('complete');
2756:
2757: pnp_debug_pkg.log('call apply_abat');
2758: pn_var_rent_calc_pkg.apply_abat(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2759: pnp_debug_pkg.log('complete');
2753: pnp_debug_pkg.log('call populate_abat');
2754: pn_var_rent_calc_pkg.populate_abat(p_var_rent_id , p_period_id, inv_rec.var_rent_inv_id);
2755: pnp_debug_pkg.log('complete');
2756:
2757: pnp_debug_pkg.log('call apply_abat');
2758: pn_var_rent_calc_pkg.apply_abat(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2759: pnp_debug_pkg.log('complete');
2760:
2761: IF(l_abat_order <> pn_var_rent_calc_pkg.G_ALLOWANCE_FIRST) THEN
2755: pnp_debug_pkg.log('complete');
2756:
2757: pnp_debug_pkg.log('call apply_abat');
2758: pn_var_rent_calc_pkg.apply_abat(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2759: pnp_debug_pkg.log('complete');
2760:
2761: IF(l_abat_order <> pn_var_rent_calc_pkg.G_ALLOWANCE_FIRST) THEN
2762: pnp_debug_pkg.log('call pnp_debug_pkg.log');
2763: pn_var_rent_calc_pkg.apply_allow(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2758: pn_var_rent_calc_pkg.apply_abat(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2759: pnp_debug_pkg.log('complete');
2760:
2761: IF(l_abat_order <> pn_var_rent_calc_pkg.G_ALLOWANCE_FIRST) THEN
2762: pnp_debug_pkg.log('call pnp_debug_pkg.log');
2763: pn_var_rent_calc_pkg.apply_allow(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2764: pnp_debug_pkg.log('complete');
2765: END IF;--Apply allowance
2766: pnp_debug_pkg.log('called populate_neg_rent');
2760:
2761: IF(l_abat_order <> pn_var_rent_calc_pkg.G_ALLOWANCE_FIRST) THEN
2762: pnp_debug_pkg.log('call pnp_debug_pkg.log');
2763: pn_var_rent_calc_pkg.apply_allow(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2764: pnp_debug_pkg.log('complete');
2765: END IF;--Apply allowance
2766: pnp_debug_pkg.log('called populate_neg_rent');
2767: pn_var_rent_calc_pkg.populate_neg_rent(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2768: pnp_debug_pkg.log('complete');
2762: pnp_debug_pkg.log('call pnp_debug_pkg.log');
2763: pn_var_rent_calc_pkg.apply_allow(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2764: pnp_debug_pkg.log('complete');
2765: END IF;--Apply allowance
2766: pnp_debug_pkg.log('called populate_neg_rent');
2767: pn_var_rent_calc_pkg.populate_neg_rent(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2768: pnp_debug_pkg.log('complete');
2769:
2770:
2764: pnp_debug_pkg.log('complete');
2765: END IF;--Apply allowance
2766: pnp_debug_pkg.log('called populate_neg_rent');
2767: pn_var_rent_calc_pkg.populate_neg_rent(p_var_rent_id, p_period_id, inv_rec.var_rent_inv_id, l_abated_rent);
2768: pnp_debug_pkg.log('complete');
2769:
2770:
2771: --
2772: FOR rec IN prev_invoiced_c(p_var_rent_id, p_period_id, inv_rec.invoice_date) LOOP
2791: status <> pn_var_rent_calc_pkg.G_TERM_STATUS_APPROVED AND
2792: var_rent_type = pn_var_rent_calc_pkg.G_INV_ON_ACTUAL;
2793:
2794: -- Update the current invoice
2795: pnp_debug_pkg.log('Abatements - updating');
2796: UPDATE
2797: pn_var_rent_inv_all
2798: SET
2799: actual_invoiced_amount = ROUND((l_abated_rent - l_prev_inv_exp), g_precision)
2808: -- Insert a new invoice. This happens only if your rents changes as a result of
2809: -- applied allowance/abat when you add them after some calc have been done.
2810: FOR inv_all_rec IN inv_all_c(inv_rec.var_rent_inv_id) LOOP
2811: --This can loop only once
2812: pnp_debug_pkg.log('l_prev_inv_exp:'||l_prev_inv_exp);
2813: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
2814: pnp_debug_pkg.log('Abatements - inserting');
2815: l_row_id := NULL;
2816: l_var_rent_inv_id := NULL;
2809: -- applied allowance/abat when you add them after some calc have been done.
2810: FOR inv_all_rec IN inv_all_c(inv_rec.var_rent_inv_id) LOOP
2811: --This can loop only once
2812: pnp_debug_pkg.log('l_prev_inv_exp:'||l_prev_inv_exp);
2813: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
2814: pnp_debug_pkg.log('Abatements - inserting');
2815: l_row_id := NULL;
2816: l_var_rent_inv_id := NULL;
2817: pn_var_rent_inv_pkg.insert_row
2810: FOR inv_all_rec IN inv_all_c(inv_rec.var_rent_inv_id) LOOP
2811: --This can loop only once
2812: pnp_debug_pkg.log('l_prev_inv_exp:'||l_prev_inv_exp);
2813: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
2814: pnp_debug_pkg.log('Abatements - inserting');
2815: l_row_id := NULL;
2816: l_var_rent_inv_id := NULL;
2817: pn_var_rent_inv_pkg.insert_row
2818: ( x_rowid => l_row_id,
2866:
2867:
2868:
2869: END LOOP;--Loop for all required invoices.
2870: pnp_debug_pkg.log('apply_abatements end1(-)');
2871: pnp_debug_pkg.log('l_prev_inv_exp:'||l_prev_inv_exp);
2872:
2873:
2874: EXCEPTION
2867:
2868:
2869: END LOOP;--Loop for all required invoices.
2870: pnp_debug_pkg.log('apply_abatements end1(-)');
2871: pnp_debug_pkg.log('l_prev_inv_exp:'||l_prev_inv_exp);
2872:
2873:
2874: EXCEPTION
2875: WHEN others THEN
2872:
2873:
2874: EXCEPTION
2875: WHEN others THEN
2876: pnp_debug_pkg.log('Raised exception');
2877: RAISE;
2878: END;
2879:
2880:
2954: l_abated_rent NUMBER;
2955: l_negative_rent_flag pn_var_rents.negative_rent%TYPE;
2956: l_last_invoice_dt pn_var_grp_dates.invoice_date%TYPE;
2957: BEGIN
2958: pnp_debug_pkg.log('apply_neg_rent start(+)');
2959: -- Get the negative rent flag
2960: FOR rec IN csr_get_abt(p_var_rent_id) LOOP
2961: l_negative_rent_flag := rec.negative_rent;
2962: pnp_debug_pkg.log('l_negative_rent_flag:'||l_negative_rent_flag);
2958: pnp_debug_pkg.log('apply_neg_rent start(+)');
2959: -- Get the negative rent flag
2960: FOR rec IN csr_get_abt(p_var_rent_id) LOOP
2961: l_negative_rent_flag := rec.negative_rent;
2962: pnp_debug_pkg.log('l_negative_rent_flag:'||l_negative_rent_flag);
2963: END LOOP;
2964: -- Get the last invoice_date
2965: FOR rec IN csr_last_inv(p_var_rent_id) LOOP
2966: l_last_invoice_dt := rec.inv_date;
2963: END LOOP;
2964: -- Get the last invoice_date
2965: FOR rec IN csr_last_inv(p_var_rent_id) LOOP
2966: l_last_invoice_dt := rec.inv_date;
2967: pnp_debug_pkg.log('l_last_invoice_dt:'||l_last_invoice_dt);
2968: END LOOP;
2969: -- Loop for all invoices.
2970: FOR inv_rec IN invoices_c(p_var_rent_id, p_period_id, p_inv_id) LOOP
2971: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
2967: pnp_debug_pkg.log('l_last_invoice_dt:'||l_last_invoice_dt);
2968: END LOOP;
2969: -- Loop for all invoices.
2970: FOR inv_rec IN invoices_c(p_var_rent_id, p_period_id, p_inv_id) LOOP
2971: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
2972: l_negative_rent := 0;
2973: l_negative_available := 0;
2974: l_negative_applied := 0;
2975: -- Get available negative rent.
2974: l_negative_applied := 0;
2975: -- Get available negative rent.
2976: FOR rec IN csr_neg_avail(p_var_rent_id, inv_rec.invoice_date) LOOP
2977: l_negative_available :=rec.negative_available;
2978: pnp_debug_pkg.log('l_negative_available'||l_negative_available);
2979: END LOOP;
2980: -- Get applied negative rent
2981: FOR rec IN csr_neg_appl (p_var_rent_id, inv_rec.invoice_date) LOOP
2982: l_negative_applied := rec.negative_applied;
2979: END LOOP;
2980: -- Get applied negative rent
2981: FOR rec IN csr_neg_appl (p_var_rent_id, inv_rec.invoice_date) LOOP
2982: l_negative_applied := rec.negative_applied;
2983: pnp_debug_pkg.log('l_negative_applied:'||l_negative_applied);
2984: END LOOP;
2985: l_negative_remaining := ABS(l_negative_available - l_negative_applied);
2986: pnp_debug_pkg.log('l_negative_remaining:'||l_negative_remaining);
2987: IF (l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_IGNORE) THEN
2982: l_negative_applied := rec.negative_applied;
2983: pnp_debug_pkg.log('l_negative_applied:'||l_negative_applied);
2984: END LOOP;
2985: l_negative_remaining := ABS(l_negative_available - l_negative_applied);
2986: pnp_debug_pkg.log('l_negative_remaining:'||l_negative_remaining);
2987: IF (l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_IGNORE) THEN
2988: l_abated_rent := GREATEST(0, inv_rec.constr_actual_rent);
2989: ELSIF(l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_CREDIT) THEN
2990: l_abated_rent := inv_rec.constr_actual_rent;
3007: l_abated_rent := inv_rec.constr_actual_rent - l_negative_remaining;
3008: l_negative_rent := inv_rec.constr_actual_rent - l_abated_rent;
3009: END IF;
3010: END IF;
3011: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3012: pnp_debug_pkg.log('l_negative_rent:'||l_negative_rent);
3013: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3014:
3015: UPDATE pn_var_rent_inv_all
3008: l_negative_rent := inv_rec.constr_actual_rent - l_abated_rent;
3009: END IF;
3010: END IF;
3011: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3012: pnp_debug_pkg.log('l_negative_rent:'||l_negative_rent);
3013: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3014:
3015: UPDATE pn_var_rent_inv_all
3016: SET abated_rent = l_abated_rent,
3009: END IF;
3010: END IF;
3011: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3012: pnp_debug_pkg.log('l_negative_rent:'||l_negative_rent);
3013: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3014:
3015: UPDATE pn_var_rent_inv_all
3016: SET abated_rent = l_abated_rent,
3017: negative_rent = L_negative_rent
3016: SET abated_rent = l_abated_rent,
3017: negative_rent = L_negative_rent
3018: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3019: END LOOP;
3020: pnp_debug_pkg.log('apply_neg_rent end(-)');
3021:
3022: EXCEPTION
3023: --
3024: WHEN others THEN
3021:
3022: EXCEPTION
3023: --
3024: WHEN others THEN
3025: pnp_debug_pkg.log('Raised exception');
3026: RAISE;
3027: END;
3028: */
3029:
3109: l_abated_rent NUMBER;
3110: l_negative_rent_flag pn_var_rents.negative_rent%TYPE;
3111: l_last_invoice_dt pn_var_grp_dates.invoice_date%TYPE;
3112: BEGIN
3113: pnp_debug_pkg.log('apply_def_neg_rent start(+)');
3114: -- Get the negative rent flag
3115: FOR rec IN csr_get_abt(p_var_rent_id) LOOP
3116: l_negative_rent_flag := rec.negative_rent;
3117: pnp_debug_pkg.log('l_negative_rent_flag:'||l_negative_rent_flag);
3113: pnp_debug_pkg.log('apply_def_neg_rent start(+)');
3114: -- Get the negative rent flag
3115: FOR rec IN csr_get_abt(p_var_rent_id) LOOP
3116: l_negative_rent_flag := rec.negative_rent;
3117: pnp_debug_pkg.log('l_negative_rent_flag:'||l_negative_rent_flag);
3118: END LOOP;
3119: -- Get the last invoice_date
3120: FOR rec IN csr_last_inv(p_var_rent_id) LOOP
3121: l_last_invoice_dt := rec.inv_date;
3118: END LOOP;
3119: -- Get the last invoice_date
3120: FOR rec IN csr_last_inv(p_var_rent_id) LOOP
3121: l_last_invoice_dt := rec.inv_date;
3122: pnp_debug_pkg.log('l_last_invoice_dt:'||l_last_invoice_dt);
3123: END LOOP;
3124: -- Loop for all invoices.
3125: FOR inv_rec IN invoices_c(p_var_rent_id, p_period_id, p_inv_id) LOOP
3126: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3122: pnp_debug_pkg.log('l_last_invoice_dt:'||l_last_invoice_dt);
3123: END LOOP;
3124: -- Loop for all invoices.
3125: FOR inv_rec IN invoices_c(p_var_rent_id, p_period_id, p_inv_id) LOOP
3126: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3127: l_negative_rent := 0;
3128: l_negative_available := 0;
3129: l_negative_applied := 0;
3130: l_abated_rent :=inv_rec.constr_actual_rent;
3130: l_abated_rent :=inv_rec.constr_actual_rent;
3131: -- Get available negative rent.
3132: FOR rec IN csr_neg_avail(p_var_rent_id, inv_rec.invoice_date) LOOP
3133: l_negative_available :=rec.negative_available;
3134: pnp_debug_pkg.log('l_negative_available'||l_negative_available);
3135: END LOOP;
3136: -- Get applied negative rent
3137: FOR rec IN csr_neg_appl (p_var_rent_id, inv_rec.invoice_date) LOOP
3138: l_negative_applied := rec.negative_applied;
3135: END LOOP;
3136: -- Get applied negative rent
3137: FOR rec IN csr_neg_appl (p_var_rent_id, inv_rec.invoice_date) LOOP
3138: l_negative_applied := rec.negative_applied;
3139: pnp_debug_pkg.log('l_negative_applied:'||l_negative_applied);
3140: END LOOP;
3141: l_negative_remaining := ABS(l_negative_available - l_negative_applied);
3142: pnp_debug_pkg.log('l_negative_remaining:'||l_negative_remaining);
3143: IF (l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_DEFER) THEN
3138: l_negative_applied := rec.negative_applied;
3139: pnp_debug_pkg.log('l_negative_applied:'||l_negative_applied);
3140: END LOOP;
3141: l_negative_remaining := ABS(l_negative_available - l_negative_applied);
3142: pnp_debug_pkg.log('l_negative_remaining:'||l_negative_remaining);
3143: IF (l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_DEFER) THEN
3144: -- Deffered negative rent can be applied only when consT-rent >0
3145: IF (l_last_invoice_dt <> inv_rec.invoice_date AND inv_rec.constr_actual_rent > 0) THEN
3146: --This invoice is not the last invoice
3144: -- Deffered negative rent can be applied only when consT-rent >0
3145: IF (l_last_invoice_dt <> inv_rec.invoice_date AND inv_rec.constr_actual_rent > 0) THEN
3146: --This invoice is not the last invoice
3147: l_abated_rent := GREATEST(0, inv_rec.constr_actual_rent - l_negative_remaining);
3148: pnp_debug_pkg.log('l_abated_rent1:'||l_abated_rent);
3149: IF (inv_rec.constr_actual_rent > l_abated_rent) THEN
3150: l_negative_rent := inv_rec.constr_actual_rent - l_abated_rent;
3151: ELSE
3152: l_negative_rent := 0;
3155:
3156: ELSIF (l_last_invoice_dt = inv_rec.invoice_date) THEN
3157: --This is the last invoice. All deffered negative rent needs to be added
3158: l_abated_rent := inv_rec.constr_actual_rent - l_negative_remaining;
3159: pnp_debug_pkg.log('l_abated_rent2:'||l_abated_rent);
3160: l_negative_rent := inv_rec.constr_actual_rent - l_abated_rent;
3161: END IF;
3162: END IF;
3163: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3159: pnp_debug_pkg.log('l_abated_rent2:'||l_abated_rent);
3160: l_negative_rent := inv_rec.constr_actual_rent - l_abated_rent;
3161: END IF;
3162: END IF;
3163: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3164: pnp_debug_pkg.log('l_negative_rent:'||l_negative_rent);
3165: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3166:
3167: UPDATE pn_var_rent_inv_all
3160: l_negative_rent := inv_rec.constr_actual_rent - l_abated_rent;
3161: END IF;
3162: END IF;
3163: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3164: pnp_debug_pkg.log('l_negative_rent:'||l_negative_rent);
3165: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3166:
3167: UPDATE pn_var_rent_inv_all
3168: SET negative_rent = ROUND(l_negative_rent, g_precision)
3161: END IF;
3162: END IF;
3163: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3164: pnp_debug_pkg.log('l_negative_rent:'||l_negative_rent);
3165: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3166:
3167: UPDATE pn_var_rent_inv_all
3168: SET negative_rent = ROUND(l_negative_rent, g_precision)
3169: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3168: SET negative_rent = ROUND(l_negative_rent, g_precision)
3169: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3170: x_abated_rent := l_abated_rent;
3171: END LOOP;
3172: pnp_debug_pkg.log('apply_def_neg_rent end(-)');
3173:
3174: EXCEPTION
3175: --
3176: WHEN others THEN
3173:
3174: EXCEPTION
3175: --
3176: WHEN others THEN
3177: pnp_debug_pkg.log('Raised exception');
3178: RAISE;
3179: END;
3180:
3181: --------------------------------------------------------------------------------
3235: l_abated_rent NUMBER;
3236: l_negative_rent_flag pn_var_rents.negative_rent%TYPE;
3237: l_last_invoice_dt pn_var_grp_dates.invoice_date%TYPE;
3238: BEGIN
3239: pnp_debug_pkg.log('populate_neg_rent start(+)');
3240: -- Get the negative rent flag
3241: FOR rec IN csr_get_abt(p_var_rent_id) LOOP
3242: l_negative_rent_flag := rec.negative_rent;
3243: pnp_debug_pkg.log('l_negative_rent_flag:'||l_negative_rent_flag);
3239: pnp_debug_pkg.log('populate_neg_rent start(+)');
3240: -- Get the negative rent flag
3241: FOR rec IN csr_get_abt(p_var_rent_id) LOOP
3242: l_negative_rent_flag := rec.negative_rent;
3243: pnp_debug_pkg.log('l_negative_rent_flag:'||l_negative_rent_flag);
3244: END LOOP;
3245: -- Get the last invoice_date
3246: FOR rec IN csr_last_inv(p_var_rent_id) LOOP
3247: l_last_invoice_dt := rec.inv_date;
3244: END LOOP;
3245: -- Get the last invoice_date
3246: FOR rec IN csr_last_inv(p_var_rent_id) LOOP
3247: l_last_invoice_dt := rec.inv_date;
3248: pnp_debug_pkg.log('l_last_invoice_dt:'||l_last_invoice_dt);
3249: END LOOP;
3250: -- Loop for all invoices.
3251: FOR inv_rec IN invoices_c(p_var_rent_id, p_period_id, p_inv_id) LOOP
3252: IF (l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_IGNORE) THEN
3262: ELSIF (l_negative_rent_flag = pn_var_rent_calc_pkg.G_NEG_RENT_DEFER
3263: AND inv_rec.invoice_date = l_last_invoice_dt) THEN
3264: l_abated_rent := x_abated_rent;
3265: END IF;
3266: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3267: pnp_debug_pkg.log('l_neg_rent_def*:'||l_neg_rent_def);
3268: UPDATE pn_var_rent_inv_all
3269: SET def_neg_rent = ROUND(l_neg_rent_def, g_precision)
3270: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3263: AND inv_rec.invoice_date = l_last_invoice_dt) THEN
3264: l_abated_rent := x_abated_rent;
3265: END IF;
3266: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3267: pnp_debug_pkg.log('l_neg_rent_def*:'||l_neg_rent_def);
3268: UPDATE pn_var_rent_inv_all
3269: SET def_neg_rent = ROUND(l_neg_rent_def, g_precision)
3270: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3271: x_abated_rent := l_abated_rent;
3269: SET def_neg_rent = ROUND(l_neg_rent_def, g_precision)
3270: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3271: x_abated_rent := l_abated_rent;
3272: END LOOP;
3273: pnp_debug_pkg.log('populate_neg_rent end(-)');
3274:
3275: EXCEPTION
3276: --
3277: WHEN others THEN
3274:
3275: EXCEPTION
3276: --
3277: WHEN others THEN
3278: pnp_debug_pkg.log('Raised exception');
3279: RAISE;
3280: END;
3281:
3282: --------------------------------------------------------------------------------
3437: -- Get the proration type
3438: FOR proration_rec IN proration_type_c(p_var_rent_id) LOOP
3439: l_proration_type := proration_rec.proration_rule;
3440: END LOOP;
3441: pnp_debug_pkg.log('apply_abat start(+)');
3442: -- is this in ly first inv? No abatements should be applied to this invoice
3443: FOR ly_inv_rec IN ly_min_inv_c(p_var_rent_id) LOOP
3444: l_ly_max_inv_id := ly_inv_rec.var_rent_inv_id;
3445: l_ly_max_prd_id := ly_inv_rec.period_id;
3443: FOR ly_inv_rec IN ly_min_inv_c(p_var_rent_id) LOOP
3444: l_ly_max_inv_id := ly_inv_rec.var_rent_inv_id;
3445: l_ly_max_prd_id := ly_inv_rec.period_id;
3446: END LOOP;
3447: pnp_debug_pkg.log('l_ly_max_inv_id:'||l_ly_max_inv_id);
3448: pnp_debug_pkg.log('l_ly_max_prd_id:'||l_ly_max_prd_id);
3449:
3450: --For LY/FLY, if last period and not last invoice,
3451: -- Invoices are dummy. Return immdiately.
3444: l_ly_max_inv_id := ly_inv_rec.var_rent_inv_id;
3445: l_ly_max_prd_id := ly_inv_rec.period_id;
3446: END LOOP;
3447: pnp_debug_pkg.log('l_ly_max_inv_id:'||l_ly_max_inv_id);
3448: pnp_debug_pkg.log('l_ly_max_prd_id:'||l_ly_max_prd_id);
3449:
3450: --For LY/FLY, if last period and not last invoice,
3451: -- Invoices are dummy. Return immdiately.
3452: IF (p_period_id = l_ly_max_prd_id AND NOT(p_inv_id = l_ly_max_inv_id) AND l_proration_type IN (pn_var_rent_calc_pkg.G_PRORUL_FLY, pn_var_rent_calc_pkg.G_PRORUL_LY) ) THEN
3449:
3450: --For LY/FLY, if last period and not last invoice,
3451: -- Invoices are dummy. Return immdiately.
3452: IF (p_period_id = l_ly_max_prd_id AND NOT(p_inv_id = l_ly_max_inv_id) AND l_proration_type IN (pn_var_rent_calc_pkg.G_PRORUL_FLY, pn_var_rent_calc_pkg.G_PRORUL_LY) ) THEN
3453: pnp_debug_pkg.log('Ly invoice, not last - return immedietly');
3454: RETURN;
3455: END IF;
3456:
3457:
3456:
3457:
3458: FOR abat_rec IN EXCESS_ABAT_C(p_var_rent_id) LOOP
3459: l_excess_abat := abat_rec.excess_abat_code;
3460: pnp_debug_pkg.log('l_excess_abat:'||l_excess_abat);
3461: EXIT;
3462: END LOOP;
3463:
3464: --
3491: l_inv_start_date := inv_rec.start_date;
3492: l_inv_end_date := inv_rec.end_date;
3493: END LOOP;
3494: --
3495: pnp_debug_pkg.log('FY/LY modified dates');
3496: pnp_debug_pkg.log('l_inv_start_date:'||l_inv_start_date);
3497: pnp_debug_pkg.log('l_inv_end_date:'||l_inv_end_date);
3498: FOR num_rec IN num_inv_c(p_inv_id) LOOP
3499: l_num_inv := num_rec.num_inv;
3492: l_inv_end_date := inv_rec.end_date;
3493: END LOOP;
3494: --
3495: pnp_debug_pkg.log('FY/LY modified dates');
3496: pnp_debug_pkg.log('l_inv_start_date:'||l_inv_start_date);
3497: pnp_debug_pkg.log('l_inv_end_date:'||l_inv_end_date);
3498: FOR num_rec IN num_inv_c(p_inv_id) LOOP
3499: l_num_inv := num_rec.num_inv;
3500: END LOOP;
3493: END LOOP;
3494: --
3495: pnp_debug_pkg.log('FY/LY modified dates');
3496: pnp_debug_pkg.log('l_inv_start_date:'||l_inv_start_date);
3497: pnp_debug_pkg.log('l_inv_end_date:'||l_inv_end_date);
3498: FOR num_rec IN num_inv_c(p_inv_id) LOOP
3499: l_num_inv := num_rec.num_inv;
3500: END LOOP;
3501: pnp_debug_pkg.log('l_num_inv:'||l_num_inv);
3497: pnp_debug_pkg.log('l_inv_end_date:'||l_inv_end_date);
3498: FOR num_rec IN num_inv_c(p_inv_id) LOOP
3499: l_num_inv := num_rec.num_inv;
3500: END LOOP;
3501: pnp_debug_pkg.log('l_num_inv:'||l_num_inv);
3502:
3503: END IF;
3504: l_rec_abat := inv_rec.rec_abatement;
3505: l_abat_override := inv_rec.rec_abatement_override;
3502:
3503: END IF;
3504: l_rec_abat := inv_rec.rec_abatement;
3505: l_abat_override := inv_rec.rec_abatement_override;
3506: pnp_debug_pkg.log('l_abat_override:'||l_abat_override);
3507: l_abated_rent := x_abated_rent;
3508: l_unabated_rent := x_abated_rent;
3509: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3510: pnp_debug_pkg.log('l_rec_abat:'||l_rec_abat);
3505: l_abat_override := inv_rec.rec_abatement_override;
3506: pnp_debug_pkg.log('l_abat_override:'||l_abat_override);
3507: l_abated_rent := x_abated_rent;
3508: l_unabated_rent := x_abated_rent;
3509: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3510: pnp_debug_pkg.log('l_rec_abat:'||l_rec_abat);
3511:
3512: IF (l_abat_override IS NOT NULL) THEN
3513: l_total_abat := l_abat_override;
3506: pnp_debug_pkg.log('l_abat_override:'||l_abat_override);
3507: l_abated_rent := x_abated_rent;
3508: l_unabated_rent := x_abated_rent;
3509: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3510: pnp_debug_pkg.log('l_rec_abat:'||l_rec_abat);
3511:
3512: IF (l_abat_override IS NOT NULL) THEN
3513: l_total_abat := l_abat_override;
3514: ELSE
3513: l_total_abat := l_abat_override;
3514: ELSE
3515: FOR rec IN fixed_abat_c(p_var_rent_id, l_inv_start_date, l_inv_end_date) LOOP
3516: l_fixed_abat := rec.fixed_abat * l_num_inv;
3517: pnp_debug_pkg.log('l_fixed_abat:'||l_fixed_abat);
3518: END LOOP;
3519: l_total_abat := l_fixed_abat + l_rec_abat;
3520: END IF;
3521:
3518: END LOOP;
3519: l_total_abat := l_fixed_abat + l_rec_abat;
3520: END IF;
3521:
3522: pnp_debug_pkg.log('l_total_abat:'||l_total_abat);
3523: IF (l_excess_abat = pn_var_rent_calc_pkg.G_EXC_ABAT_IGNORE
3524: AND x_abated_rent>0 ) THEN
3525: l_abated_rent := GREATEST(0, x_abated_rent - l_total_abat);
3526: ELSIF (l_excess_abat = pn_var_rent_calc_pkg.G_EXC_ABAT_NEG_RENT ) THEN
3526: ELSIF (l_excess_abat = pn_var_rent_calc_pkg.G_EXC_ABAT_NEG_RENT ) THEN
3527: l_abated_rent := x_abated_rent - l_total_abat;
3528: END IF;
3529:
3530: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3531: x_abated_rent := l_abated_rent;
3532: l_total_abat := l_unabated_rent - l_abated_rent;
3533: pnp_debug_pkg.log('total_abat_applied:'||l_total_abat);
3534: UPDATE pn_var_rent_inv_all
3529:
3530: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3531: x_abated_rent := l_abated_rent;
3532: l_total_abat := l_unabated_rent - l_abated_rent;
3533: pnp_debug_pkg.log('total_abat_applied:'||l_total_abat);
3534: UPDATE pn_var_rent_inv_all
3535: SET rec_abatement = ROUND(l_total_abat, g_precision)
3536: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3537:
3535: SET rec_abatement = ROUND(l_total_abat, g_precision)
3536: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3537:
3538: END LOOP;
3539: pnp_debug_pkg.log('apply_abat end(-)');
3540:
3541: EXCEPTION
3542: --
3543: WHEN others THEN
3540:
3541: EXCEPTION
3542: --
3543: WHEN others THEN
3544: pnp_debug_pkg.log('Exception in apply_abat');
3545: RAISE;
3546: END;
3547:
3548: --------------------------------------------------------------------------------
3652: l_first_inv_id NUMBER;
3653: l_last_inv_id NUMBER;
3654:
3655: BEGIN
3656: pnp_debug_pkg.log('apply_allow start(+)');
3657: -- Special cases which need to be handled are
3658: -- FY/FLY first invoice AND LY/FLY last invoice.
3659: -- get first inv
3660: --
3672: END LOOP;
3673:
3674:
3675: FOR inv_rec IN invoices_c(p_var_rent_id, p_period_id, p_inv_id) LOOP
3676: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3677: l_allow_applied_inv := inv_rec.abatement_appl;
3678: pnp_debug_pkg.log('l_allow_applied_inv:'||l_allow_applied_inv);
3679: --
3680: FOR inv_dates_rec IN invoice_dates_c(p_var_rent_id, inv_rec.invoice_date) LOOP
3674:
3675: FOR inv_rec IN invoices_c(p_var_rent_id, p_period_id, p_inv_id) LOOP
3676: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3677: l_allow_applied_inv := inv_rec.abatement_appl;
3678: pnp_debug_pkg.log('l_allow_applied_inv:'||l_allow_applied_inv);
3679: --
3680: FOR inv_dates_rec IN invoice_dates_c(p_var_rent_id, inv_rec.invoice_date) LOOP
3681: l_inv_start_date := inv_dates_rec.inv_start_date;
3682: l_inv_end_date := inv_dates_rec.inv_end_date;
3696: l_prev_abt_rent := x_abated_rent;
3697: FOR allow_rec IN rolling_allow_c(p_var_rent_id, l_inv_start_date, l_inv_end_date) LOOP
3698: --Allowances can olny be applied if rent is >0
3699: IF (l_cur_abt_rent > 0) THEN
3700: pnp_debug_pkg.log('allow_rec.abatement_id'||allow_rec.abatement_id);
3701: l_allow_remain := allow_rec.rolling_allow -NVL(allow_rec.allow_applied,0);
3702: pnp_debug_pkg.log('l_allow_remain:'||l_allow_remain);
3703: l_cur_abt_rent := GREATEST(0,l_prev_abt_rent - l_allow_remain);
3704: pnp_debug_pkg.log('l_cur_abated_rent:'||l_cur_abt_rent);
3698: --Allowances can olny be applied if rent is >0
3699: IF (l_cur_abt_rent > 0) THEN
3700: pnp_debug_pkg.log('allow_rec.abatement_id'||allow_rec.abatement_id);
3701: l_allow_remain := allow_rec.rolling_allow -NVL(allow_rec.allow_applied,0);
3702: pnp_debug_pkg.log('l_allow_remain:'||l_allow_remain);
3703: l_cur_abt_rent := GREATEST(0,l_prev_abt_rent - l_allow_remain);
3704: pnp_debug_pkg.log('l_cur_abated_rent:'||l_cur_abt_rent);
3705: l_allow_applied := l_prev_abt_rent - l_cur_abt_rent;
3706: pnp_debug_pkg.log('l_allow_applied'||l_allow_applied);
3700: pnp_debug_pkg.log('allow_rec.abatement_id'||allow_rec.abatement_id);
3701: l_allow_remain := allow_rec.rolling_allow -NVL(allow_rec.allow_applied,0);
3702: pnp_debug_pkg.log('l_allow_remain:'||l_allow_remain);
3703: l_cur_abt_rent := GREATEST(0,l_prev_abt_rent - l_allow_remain);
3704: pnp_debug_pkg.log('l_cur_abated_rent:'||l_cur_abt_rent);
3705: l_allow_applied := l_prev_abt_rent - l_cur_abt_rent;
3706: pnp_debug_pkg.log('l_allow_applied'||l_allow_applied);
3707: l_prev_abt_rent := l_cur_abt_rent;
3708: pnp_debug_pkg.log('l_prev_abt_rent:'||l_prev_abt_rent);
3702: pnp_debug_pkg.log('l_allow_remain:'||l_allow_remain);
3703: l_cur_abt_rent := GREATEST(0,l_prev_abt_rent - l_allow_remain);
3704: pnp_debug_pkg.log('l_cur_abated_rent:'||l_cur_abt_rent);
3705: l_allow_applied := l_prev_abt_rent - l_cur_abt_rent;
3706: pnp_debug_pkg.log('l_allow_applied'||l_allow_applied);
3707: l_prev_abt_rent := l_cur_abt_rent;
3708: pnp_debug_pkg.log('l_prev_abt_rent:'||l_prev_abt_rent);
3709: l_allow_applied_inv := l_allow_applied_inv + l_allow_applied;
3710: pnp_debug_pkg.log('l_allow_applied_inv:'||l_allow_applied_inv);
3704: pnp_debug_pkg.log('l_cur_abated_rent:'||l_cur_abt_rent);
3705: l_allow_applied := l_prev_abt_rent - l_cur_abt_rent;
3706: pnp_debug_pkg.log('l_allow_applied'||l_allow_applied);
3707: l_prev_abt_rent := l_cur_abt_rent;
3708: pnp_debug_pkg.log('l_prev_abt_rent:'||l_prev_abt_rent);
3709: l_allow_applied_inv := l_allow_applied_inv + l_allow_applied;
3710: pnp_debug_pkg.log('l_allow_applied_inv:'||l_allow_applied_inv);
3711: UPDATE pn_var_abat_defaults_all
3712: SET allowance_applied = NVL(allowance_applied,0)+l_allow_applied
3706: pnp_debug_pkg.log('l_allow_applied'||l_allow_applied);
3707: l_prev_abt_rent := l_cur_abt_rent;
3708: pnp_debug_pkg.log('l_prev_abt_rent:'||l_prev_abt_rent);
3709: l_allow_applied_inv := l_allow_applied_inv + l_allow_applied;
3710: pnp_debug_pkg.log('l_allow_applied_inv:'||l_allow_applied_inv);
3711: UPDATE pn_var_abat_defaults_all
3712: SET allowance_applied = NVL(allowance_applied,0)+l_allow_applied
3713: WHERE abatement_id = allow_rec.abatement_id;
3714: END IF;
3712: SET allowance_applied = NVL(allowance_applied,0)+l_allow_applied
3713: WHERE abatement_id = allow_rec.abatement_id;
3714: END IF;
3715: END LOOP;
3716: pnp_debug_pkg.log('l_cur_abt_rent:'||l_cur_abt_rent);
3717: UPDATE pn_var_rent_inv_all
3718: SET abatement_appl = ROUND(l_allow_applied_inv, g_precision)
3719: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3720: x_abated_rent := l_cur_abt_rent;
3717: UPDATE pn_var_rent_inv_all
3718: SET abatement_appl = ROUND(l_allow_applied_inv, g_precision)
3719: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3720: x_abated_rent := l_cur_abt_rent;
3721: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3722: END LOOP;
3723: pnp_debug_pkg.log('apply_allow end(-)');
3724: EXCEPTION
3725: WHEN others THEN
3719: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3720: x_abated_rent := l_cur_abt_rent;
3721: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3722: END LOOP;
3723: pnp_debug_pkg.log('apply_allow end(-)');
3724: EXCEPTION
3725: WHEN others THEN
3726: RAISE;
3727: END;
3876: --------------------------------------------------------------------------------
3877: PROCEDURE reset_abatements(p_var_rent_id IN NUMBER
3878: ) IS
3879: BEGIN
3880: pnp_debug_pkg.log('Reset_abatement start(+)');
3881: UPDATE pn_var_abat_defaults_all
3882: SET allowance_applied =0
3883: WHERE var_rent_id = p_var_rent_id;
3884: UPDATE pn_var_rent_inv_all
3883: WHERE var_rent_id = p_var_rent_id;
3884: UPDATE pn_var_rent_inv_all
3885: SET abatement_appl =0
3886: WHERE var_rent_id = p_var_rent_id;
3887: pnp_debug_pkg.log('Reset_abatement end(-)');
3888:
3889: EXCEPTION
3890: WHEN others THEN
3891: RAISE;
4007: l_partial_prd_id NUMBER:=NULL;
4008: l_partial_period VARCHAR2(1) := 'N';
4009:
4010: BEGIN
4011: pnp_debug_pkg.log('+++++++++ calculate_rent START +++++++++++');
4012: pnp_debug_pkg.log('');
4013: /* -------------------------------------------------------------------------- */
4014: /* ------------------------------- CODE BEGIN ------------------------------- */
4015: /* -------------------------------------------------------------------------- */
4008: l_partial_period VARCHAR2(1) := 'N';
4009:
4010: BEGIN
4011: pnp_debug_pkg.log('+++++++++ calculate_rent START +++++++++++');
4012: pnp_debug_pkg.log('');
4013: /* -------------------------------------------------------------------------- */
4014: /* ------------------------------- CODE BEGIN ------------------------------- */
4015: /* -------------------------------------------------------------------------- */
4016:
4018: l_context := 'Call pn_var_rent_calc_pkg.cache_vr_details';
4019:
4020: pn_var_rent_calc_pkg.cache_vr_details(p_var_rent_id => p_var_rent_id);
4021:
4022: pnp_debug_pkg.log(l_context||' COMPLETE');
4023:
4024: /* check if trx tables need to be updated for change in bkts */
4025: l_context := 'Call pn_var_trx_pkg.populate_transactions';
4026:
4025: l_context := 'Call pn_var_trx_pkg.populate_transactions';
4026:
4027: pn_var_trx_pkg.populate_transactions(p_var_rent_id => p_var_rent_id);
4028:
4029: pnp_debug_pkg.log(l_context||' COMPLETE');
4030:
4031: /*Do we need to populate actual sales or forecasted sales?*/
4032:
4033: IF(g_invoice_on=G_INV_ON_ACTUAL) THEN
4033: IF(g_invoice_on=G_INV_ON_ACTUAL) THEN
4034: /* check if trx tables need to be updated for change in sales volume */
4035: l_context := 'Call pn_var_trx_pkg.populate_sales';
4036: pn_var_trx_pkg.populate_sales(p_var_rent_id => p_var_rent_id);
4037: pnp_debug_pkg.log(l_context||' COMPLETE');
4038: ELSIF (g_invoice_on = G_INV_ON_FORECASTED AND g_calc_type = G_CALC_TYPE_CALCULATE) THEN
4039: /* check if trx tables need to be updated for change in forecasted sales volume */
4040: l_context := 'Call pn_var_trx_pkg.populate_sales_for';
4041: pn_var_trx_pkg.populate_sales_for(p_var_rent_id => p_var_rent_id);
4038: ELSIF (g_invoice_on = G_INV_ON_FORECASTED AND g_calc_type = G_CALC_TYPE_CALCULATE) THEN
4039: /* check if trx tables need to be updated for change in forecasted sales volume */
4040: l_context := 'Call pn_var_trx_pkg.populate_sales_for';
4041: pn_var_trx_pkg.populate_sales_for(p_var_rent_id => p_var_rent_id);
4042: pnp_debug_pkg.log(l_context||' COMPLETE');
4043: ELSIF (g_invoice_on = G_INV_ON_FORECASTED AND g_calc_type = G_CALC_TYPE_RECONCILE) THEN
4044: /* check if trx tables need to be updated for change in sales volume */
4045: l_context := 'Call pn_var_trx_pkg.populate_sales';
4046: pn_var_trx_pkg.populate_sales(p_var_rent_id => p_var_rent_id);
4043: ELSIF (g_invoice_on = G_INV_ON_FORECASTED AND g_calc_type = G_CALC_TYPE_RECONCILE) THEN
4044: /* check if trx tables need to be updated for change in sales volume */
4045: l_context := 'Call pn_var_trx_pkg.populate_sales';
4046: pn_var_trx_pkg.populate_sales(p_var_rent_id => p_var_rent_id);
4047: pnp_debug_pkg.log(l_context||' COMPLETE');
4048:
4049: END IF;
4050:
4051: /* Populate deductions - Only need to do this when actuals are being calculated */
4050:
4051: /* Populate deductions - Only need to do this when actuals are being calculated */
4052: IF(g_invoice_on=G_INV_ON_ACTUAL)
4053: OR (g_invoice_on = G_INV_ON_FORECASTED AND g_calc_type = G_CALC_TYPE_RECONCILE) THEN
4054: pnp_debug_pkg.log('');
4055: l_context := 'Call pn_var_trx_pkg.populate_deductions';
4056:
4057: pn_var_trx_pkg.populate_deductions(p_var_rent_id => p_var_rent_id);
4058:
4055: l_context := 'Call pn_var_trx_pkg.populate_deductions';
4056:
4057: pn_var_trx_pkg.populate_deductions(p_var_rent_id => p_var_rent_id);
4058:
4059: pnp_debug_pkg.log(l_context||' COMPLETE');
4060:
4061: END IF;
4062:
4063: pnp_debug_pkg.log('');
4059: pnp_debug_pkg.log(l_context||' COMPLETE');
4060:
4061: END IF;
4062:
4063: pnp_debug_pkg.log('');
4064:
4065: /* populate the trx tables with calculated rents */
4066: l_context := 'Get periods to calculate rent for';
4067:
4088: CLOSE periods_c;
4089:
4090: END IF;
4091:
4092: pnp_debug_pkg.log(l_context||' COMPLETE');
4093: pnp_debug_pkg.log('');
4094:
4095: l_context := 'Loop for all cached periods. Number of periods: '||l_periods_t.COUNT;
4096: pnp_debug_pkg.log(l_context);
4089:
4090: END IF;
4091:
4092: pnp_debug_pkg.log(l_context||' COMPLETE');
4093: pnp_debug_pkg.log('');
4094:
4095: l_context := 'Loop for all cached periods. Number of periods: '||l_periods_t.COUNT;
4096: pnp_debug_pkg.log(l_context);
4097: pnp_debug_pkg.log('');
4092: pnp_debug_pkg.log(l_context||' COMPLETE');
4093: pnp_debug_pkg.log('');
4094:
4095: l_context := 'Loop for all cached periods. Number of periods: '||l_periods_t.COUNT;
4096: pnp_debug_pkg.log(l_context);
4097: pnp_debug_pkg.log('');
4098:
4099: /* loop for all periods cached and calculate rent */
4100: IF l_periods_t.COUNT > 0 THEN
4093: pnp_debug_pkg.log('');
4094:
4095: l_context := 'Loop for all cached periods. Number of periods: '||l_periods_t.COUNT;
4096: pnp_debug_pkg.log(l_context);
4097: pnp_debug_pkg.log('');
4098:
4099: /* loop for all periods cached and calculate rent */
4100: IF l_periods_t.COUNT > 0 THEN
4101:
4105: := '=== Looping for Period - Start Date: '||l_periods_t(p).start_date||
4106: ' -- End Date: '||l_periods_t(p).end_date||
4107: ' === ';
4108:
4109: pnp_debug_pkg.log(l_context);
4110: pnp_debug_pkg.log('');
4111:
4112: l_context := 'Fetch all trx data for period';
4113:
4106: ' -- End Date: '||l_periods_t(p).end_date||
4107: ' === ';
4108:
4109: pnp_debug_pkg.log(l_context);
4110: pnp_debug_pkg.log('');
4111:
4112: l_context := 'Fetch all trx data for period';
4113:
4114: /*Do we need to populate actual or forecasted rents?*/
4126: ,p_prd_id => l_periods_t(p).period_id);
4127: FETCH pn_var_rent_calc_pkg.trx_hdr_c BULK COLLECT INTO l_trx_hrd_t;
4128: CLOSE pn_var_rent_calc_pkg.trx_hdr_c;
4129:
4130: pnp_debug_pkg.log(l_context||' COMPLETE');
4131: pnp_debug_pkg.log('');
4132:
4133: IF l_trx_hrd_t.COUNT > 0 THEN
4134:
4127: FETCH pn_var_rent_calc_pkg.trx_hdr_c BULK COLLECT INTO l_trx_hrd_t;
4128: CLOSE pn_var_rent_calc_pkg.trx_hdr_c;
4129:
4130: pnp_debug_pkg.log(l_context||' COMPLETE');
4131: pnp_debug_pkg.log('');
4132:
4133: IF l_trx_hrd_t.COUNT > 0 THEN
4134:
4135: l_context := 'Loop for all cached transactions. '||
4133: IF l_trx_hrd_t.COUNT > 0 THEN
4134:
4135: l_context := 'Loop for all cached transactions. '||
4136: 'Number of trx: '||l_trx_hrd_t.COUNT;
4137: pnp_debug_pkg.log(l_context);
4138:
4139: FOR t IN l_trx_hrd_t.FIRST..l_trx_hrd_t.LAST LOOP
4140:
4141: l_context
4143: l_trx_hrd_t(t).calc_prd_start_date||
4144: ' -- End Date: '||
4145: l_trx_hrd_t(t).calc_prd_end_date||
4146: ' === ';
4147: pnp_debug_pkg.log(l_context);
4148: pnp_debug_pkg.log('');
4149:
4150: /* if we need to create an invoice for this calc (sub) period
4151: AND if approved sales exist
4144: ' -- End Date: '||
4145: l_trx_hrd_t(t).calc_prd_end_date||
4146: ' === ';
4147: pnp_debug_pkg.log(l_context);
4148: pnp_debug_pkg.log('');
4149:
4150: /* if we need to create an invoice for this calc (sub) period
4151: AND if approved sales exist
4152: */
4159: l_trx_hrd_r := l_trx_hrd_t(t);
4160:
4161: l_context := 'Call pn_var_rent_calc_pkg.get_rent_applicable';
4162: pn_var_rent_calc_pkg.get_rent_applicable(l_trx_hrd_r);
4163: pnp_debug_pkg.log(l_context||' COMPLETE');
4164: pnp_debug_pkg.log('');
4165:
4166: /* if rent changed */
4167: IF NVL(l_trx_hrd_t(t).calculated_rent, 0)
4160:
4161: l_context := 'Call pn_var_rent_calc_pkg.get_rent_applicable';
4162: pn_var_rent_calc_pkg.get_rent_applicable(l_trx_hrd_r);
4163: pnp_debug_pkg.log(l_context||' COMPLETE');
4164: pnp_debug_pkg.log('');
4165:
4166: /* if rent changed */
4167: IF NVL(l_trx_hrd_t(t).calculated_rent, 0)
4168: <> NVL(l_trx_hrd_r.calculated_rent, 0) OR
4193:
4194: ELSE
4195:
4196: l_context := 'No calculation necessary';
4197: pnp_debug_pkg.log(l_context);
4198: pnp_debug_pkg.log('');
4199:
4200: END IF; /* need to create inv 4 this calc (sub) prd AND appr sales exist */
4201:
4194: ELSE
4195:
4196: l_context := 'No calculation necessary';
4197: pnp_debug_pkg.log(l_context);
4198: pnp_debug_pkg.log('');
4199:
4200: END IF; /* need to create inv 4 this calc (sub) prd AND appr sales exist */
4201:
4202: END LOOP; /* loop for all trx in the period */
4201:
4202: END LOOP; /* loop for all trx in the period */
4203:
4204: l_context := 'Loop for all cached transactions.';
4205: pnp_debug_pkg.log(l_context||' COMPLETE');
4206: pnp_debug_pkg.log('');
4207:
4208: END IF;
4209:
4202: END LOOP; /* loop for all trx in the period */
4203:
4204: l_context := 'Loop for all cached transactions.';
4205: pnp_debug_pkg.log(l_context||' COMPLETE');
4206: pnp_debug_pkg.log('');
4207:
4208: END IF;
4209:
4210: ELSIF (g_invoice_on = G_INV_ON_FORECASTED AND g_calc_type = G_CALC_TYPE_CALCULATE) THEN
4219: ,p_prd_id => l_periods_t(p).period_id);
4220: FETCH pn_var_rent_calc_pkg.trx_hdr_for_c BULK COLLECT INTO l_trx_hrd_for_t;
4221: CLOSE pn_var_rent_calc_pkg.trx_hdr_for_c;
4222:
4223: pnp_debug_pkg.log(l_context||' COMPLETE');
4224: pnp_debug_pkg.log('');
4225:
4226: IF l_trx_hrd_for_t.COUNT > 0 THEN
4227:
4220: FETCH pn_var_rent_calc_pkg.trx_hdr_for_c BULK COLLECT INTO l_trx_hrd_for_t;
4221: CLOSE pn_var_rent_calc_pkg.trx_hdr_for_c;
4222:
4223: pnp_debug_pkg.log(l_context||' COMPLETE');
4224: pnp_debug_pkg.log('');
4225:
4226: IF l_trx_hrd_for_t.COUNT > 0 THEN
4227:
4228: l_context := 'Loop for all cached transactions. '||
4226: IF l_trx_hrd_for_t.COUNT > 0 THEN
4227:
4228: l_context := 'Loop for all cached transactions. '||
4229: 'Number of trx: '||l_trx_hrd_for_t.COUNT;
4230: pnp_debug_pkg.log(l_context);
4231:
4232: FOR t IN l_trx_hrd_for_t.FIRST..l_trx_hrd_for_t.LAST LOOP
4233:
4234: l_context
4236: l_trx_hrd_for_t(t).calc_prd_start_date||
4237: ' -- End Date: '||
4238: l_trx_hrd_for_t(t).calc_prd_end_date||
4239: ' === ';
4240: pnp_debug_pkg.log(l_context);
4241: pnp_debug_pkg.log('');
4242:
4243: /* if we need to create an invoice for this calc (sub) period
4244: */
4237: ' -- End Date: '||
4238: l_trx_hrd_for_t(t).calc_prd_end_date||
4239: ' === ';
4240: pnp_debug_pkg.log(l_context);
4241: pnp_debug_pkg.log('');
4242:
4243: /* if we need to create an invoice for this calc (sub) period
4244: */
4245: IF NVL(l_trx_hrd_for_t(t).invoice_flag, 'Y') <> 'N'
4253: l_trx_hrd_for_r := l_trx_hrd_for_t(t);
4254:
4255: l_context := 'Call pn_var_rent_calc_pkg.get_rent_applicable_for';
4256: pn_var_rent_calc_pkg.get_rent_applicable_for(l_trx_hrd_for_r);
4257: pnp_debug_pkg.log(l_context||' COMPLETE');
4258: pnp_debug_pkg.log('');
4259:
4260: /* if rent changed */
4261: IF NVL(l_trx_hrd_for_t(t).calculated_rent_for, 0)
4254:
4255: l_context := 'Call pn_var_rent_calc_pkg.get_rent_applicable_for';
4256: pn_var_rent_calc_pkg.get_rent_applicable_for(l_trx_hrd_for_r);
4257: pnp_debug_pkg.log(l_context||' COMPLETE');
4258: pnp_debug_pkg.log('');
4259:
4260: /* if rent changed */
4261: IF NVL(l_trx_hrd_for_t(t).calculated_rent_for, 0)
4262: <> NVL(l_trx_hrd_for_r.calculated_rent_for, 0) OR
4264: <> NVL(l_trx_hrd_for_r.percent_rent_due_for, 0) OR
4265: NVL(l_trx_hrd_for_t(t).ytd_percent_rent_for, 0)
4266: <> NVL(l_trx_hrd_for_r.ytd_percent_rent_for, 0)
4267: THEN
4268: pnp_debug_pkg.log('updating the trx');
4269: pnp_debug_pkg.log('l_trx_hrd_for_t(t).calculated_rent_for'||l_trx_hrd_for_t(t).calculated_rent_for);
4270: pnp_debug_pkg.log('l_trx_hrd_for_r.calculated_rent_for'||l_trx_hrd_for_r.calculated_rent_for);
4271: pnp_debug_pkg.log('l_trx_hrd_for_r.trx_header_id'||l_trx_hrd_for_r.trx_header_id);
4272:
4265: NVL(l_trx_hrd_for_t(t).ytd_percent_rent_for, 0)
4266: <> NVL(l_trx_hrd_for_r.ytd_percent_rent_for, 0)
4267: THEN
4268: pnp_debug_pkg.log('updating the trx');
4269: pnp_debug_pkg.log('l_trx_hrd_for_t(t).calculated_rent_for'||l_trx_hrd_for_t(t).calculated_rent_for);
4270: pnp_debug_pkg.log('l_trx_hrd_for_r.calculated_rent_for'||l_trx_hrd_for_r.calculated_rent_for);
4271: pnp_debug_pkg.log('l_trx_hrd_for_r.trx_header_id'||l_trx_hrd_for_r.trx_header_id);
4272:
4273: UPDATE
4266: <> NVL(l_trx_hrd_for_r.ytd_percent_rent_for, 0)
4267: THEN
4268: pnp_debug_pkg.log('updating the trx');
4269: pnp_debug_pkg.log('l_trx_hrd_for_t(t).calculated_rent_for'||l_trx_hrd_for_t(t).calculated_rent_for);
4270: pnp_debug_pkg.log('l_trx_hrd_for_r.calculated_rent_for'||l_trx_hrd_for_r.calculated_rent_for);
4271: pnp_debug_pkg.log('l_trx_hrd_for_r.trx_header_id'||l_trx_hrd_for_r.trx_header_id);
4272:
4273: UPDATE
4274: pn_var_trx_headers_all
4267: THEN
4268: pnp_debug_pkg.log('updating the trx');
4269: pnp_debug_pkg.log('l_trx_hrd_for_t(t).calculated_rent_for'||l_trx_hrd_for_t(t).calculated_rent_for);
4270: pnp_debug_pkg.log('l_trx_hrd_for_r.calculated_rent_for'||l_trx_hrd_for_r.calculated_rent_for);
4271: pnp_debug_pkg.log('l_trx_hrd_for_r.trx_header_id'||l_trx_hrd_for_r.trx_header_id);
4272:
4273: UPDATE
4274: pn_var_trx_headers_all
4275: SET
4285:
4286: ELSE
4287:
4288: l_context := 'No calculation necessary';
4289: pnp_debug_pkg.log(l_context);
4290: pnp_debug_pkg.log('');
4291:
4292: END IF; /* need to create inv 4 this calc (sub) prd AND appr sales exist */
4293:
4286: ELSE
4287:
4288: l_context := 'No calculation necessary';
4289: pnp_debug_pkg.log(l_context);
4290: pnp_debug_pkg.log('');
4291:
4292: END IF; /* need to create inv 4 this calc (sub) prd AND appr sales exist */
4293:
4294: END LOOP; /* loop for all trx in the period */
4293:
4294: END LOOP; /* loop for all trx in the period */
4295:
4296: l_context := 'Loop for all cached transactions.';
4297: pnp_debug_pkg.log(l_context||' COMPLETE');
4298: pnp_debug_pkg.log('');
4299:
4300: END IF;
4301:
4294: END LOOP; /* loop for all trx in the period */
4295:
4296: l_context := 'Loop for all cached transactions.';
4297: pnp_debug_pkg.log(l_context||' COMPLETE');
4298: pnp_debug_pkg.log('');
4299:
4300: END IF;
4301:
4302: END IF;/*end g_invoice and g_calc_type*/
4331: CLOSE periods_out_c;
4332:
4333: END IF;
4334:
4335: pnp_debug_pkg.log('set partial flag');
4336: -- Set l_partial_prd_id if 1st partial period exists and proration rule is FY/FLY
4337: IF g_proration_rule IN ( pn_var_rent_calc_pkg.G_PRORUL_FY
4338: ,pn_var_rent_calc_pkg.G_PRORUL_FLY) THEN
4339: FOR fst_rec IN check_fst_partial_prd(p_var_rent_id) LOOP
4341: END LOOP;
4342: END IF;
4343:
4344: g_partial_prd_flag := NULL;
4345: pnp_debug_pkg.log('prior to pop inv date tab');
4346: -- call to populate inv_date_tab for roll fwd
4347: -- IF 1st partial period exists then call pop_inv_date_tab_firstyr ,to populate
4348: -- invoice dates from 2nd annual period
4349: -- ELSE call pop_inv_date_tab
4354: pn_var_rent_calc_pkg.pop_inv_date_tab(p_var_rent_id => p_var_rent_id ,
4355: p_status => 'APPROVED');
4356: END IF;
4357:
4358: /*pnp_debug_pkg.log('data in inv_date_tab');
4359: FOR i IN 1..inv_date_tab.COUNT LOOP
4360: pnp_debug_pkg.log(inv_date_tab(i).inv_date||' '||
4361: inv_date_tab(i).period_id||' '||
4362: inv_date_tab(i).inv_flag||'!!');
4356: END IF;
4357:
4358: /*pnp_debug_pkg.log('data in inv_date_tab');
4359: FOR i IN 1..inv_date_tab.COUNT LOOP
4360: pnp_debug_pkg.log(inv_date_tab(i).inv_date||' '||
4361: inv_date_tab(i).period_id||' '||
4362: inv_date_tab(i).inv_flag||'!!');
4363: END LOOP;*/
4364:
4384: THEN*/
4385: pn_var_rent_calc_pkg.post_summary
4386: ( p_var_rent_id => p_var_rent_id
4387: ,p_period_id => l_periods_t(p).period_id);
4388: pnp_debug_pkg.log(l_context||' COMPLETE');
4389: pnp_debug_pkg.log('');
4390: /*END IF; */
4391:
4392: ELSIF (g_invoice_on=G_INV_ON_FORECASTED AND g_calc_type= G_CALC_TYPE_CALCULATE)
4385: pn_var_rent_calc_pkg.post_summary
4386: ( p_var_rent_id => p_var_rent_id
4387: ,p_period_id => l_periods_t(p).period_id);
4388: pnp_debug_pkg.log(l_context||' COMPLETE');
4389: pnp_debug_pkg.log('');
4390: /*END IF; */
4391:
4392: ELSIF (g_invoice_on=G_INV_ON_FORECASTED AND g_calc_type= G_CALC_TYPE_CALCULATE)
4393: THEN
4394: l_context := 'Call pn_var_rent_calc_pkg.post_summary_for';
4395: pn_var_rent_calc_pkg.post_summary_for
4396: ( p_var_rent_id => p_var_rent_id
4397: ,p_period_id => l_periods_t(p).period_id);
4398: pnp_debug_pkg.log(l_context||' COMPLETE');
4399: pnp_debug_pkg.log('');
4400:
4401: END IF;/*End invoice type and calc_type*/
4402:
4395: pn_var_rent_calc_pkg.post_summary_for
4396: ( p_var_rent_id => p_var_rent_id
4397: ,p_period_id => l_periods_t(p).period_id);
4398: pnp_debug_pkg.log(l_context||' COMPLETE');
4399: pnp_debug_pkg.log('');
4400:
4401: END IF;/*End invoice type and calc_type*/
4402:
4403: l_context := 'Call pn_var_rent_calc_pkg.insert_invoice';
4419: ,p_period_id => l_periods_t(p).period_id);
4420:
4421: END IF;
4422:
4423: pnp_debug_pkg.log(l_context||' COMPLETE');
4424: pnp_debug_pkg.log('');
4425:
4426: END LOOP;/*End loop for all periods*/
4427:
4420:
4421: END IF;
4422:
4423: pnp_debug_pkg.log(l_context||' COMPLETE');
4424: pnp_debug_pkg.log('');
4425:
4426: END LOOP;/*End loop for all periods*/
4427:
4428: /* Create the invoice for first partial year separately */
4462: g_partial_prd_flag := NULL; -- clearing the roll fwd flag of 1st partial prd
4463:
4464: --Reset abatements for the periods for which we donot re-calculate
4465: pn_var_rent_calc_pkg.reset_abatements(p_var_rent_id);
4466: pnp_debug_pkg.log('g_calculation_method3:'||g_calculation_method);
4467: IF l_periods_out_t.COUNT > 0 THEN
4468: FOR p IN l_periods_out_t.FIRST..l_periods_out_t.LAST --
4469: --
4470: LOOP
4469: --
4470: LOOP
4471: IF (g_calculation_method = 'T') THEN
4472: --Take true up invoices into account
4473: pnp_debug_pkg.log('True up');
4474: pn_var_trueup_pkg.set_trueup_flag('C');
4475: pn_var_trueup_pkg.apply_abatements
4476: ( p_var_rent_id => p_var_rent_id
4477: ,p_period_id => l_periods_out_t(p).period_id
4477: ,p_period_id => l_periods_out_t(p).period_id
4478: ,p_flag => 'RESET');
4479: ELSE
4480: --No need to take trueup invoices into account.
4481: pnp_debug_pkg.log('Not true up');
4482: pn_var_rent_calc_pkg.apply_abatements
4483: ( p_var_rent_id => p_var_rent_id
4484: ,p_period_id => l_periods_out_t(p).period_id
4485: ,p_flag => 'RESET');
4493: FOR p IN l_periods_t.FIRST..l_periods_t.LAST --
4494: --
4495: LOOP
4496: IF (g_calculation_method = 'T') THEN
4497: pnp_debug_pkg.log('True up');
4498: NULL;
4499: --Take true up invoices into account
4500: pn_var_trueup_pkg.set_trueup_flag('C');
4501: pn_var_trueup_pkg.apply_abatements
4502: ( p_var_rent_id => p_var_rent_id
4503: ,p_period_id => l_periods_t(p).period_id
4504: ,p_flag => 'CALCULATE');
4505: ELSE
4506: pnp_debug_pkg.log('Not true up');
4507: --No need to take trueup invoices into account.
4508: pn_var_rent_calc_pkg.apply_abatements
4509: ( p_var_rent_id => p_var_rent_id
4510: ,p_period_id => l_periods_t(p).period_id
4517: /* deletes the draft term for which invoice_date or term template has changed.*/
4518: pn_var_rent_calc_pkg.delete_draft_terms( p_var_rent_id => p_var_rent_id);
4519:
4520: l_context := 'Loop for all cached periods';
4521: pnp_debug_pkg.log(l_context||' COMPLETE');
4522: pnp_debug_pkg.log('');
4523:
4524: /* -------------------------------------------------------------------------- */
4525: /* -------------------------------- CODE END -------------------------------- */
4518: pn_var_rent_calc_pkg.delete_draft_terms( p_var_rent_id => p_var_rent_id);
4519:
4520: l_context := 'Loop for all cached periods';
4521: pnp_debug_pkg.log(l_context||' COMPLETE');
4522: pnp_debug_pkg.log('');
4523:
4524: /* -------------------------------------------------------------------------- */
4525: /* -------------------------------- CODE END -------------------------------- */
4526: /* -------------------------------------------------------------------------- */
4524: /* -------------------------------------------------------------------------- */
4525: /* -------------------------------- CODE END -------------------------------- */
4526: /* -------------------------------------------------------------------------- */
4527:
4528: pnp_debug_pkg.log('+++++++++ calculate_rent END +++++++++++');
4529: pnp_debug_pkg.log('');
4530:
4531: EXCEPTION
4532: WHEN OTHERS THEN
4525: /* -------------------------------- CODE END -------------------------------- */
4526: /* -------------------------------------------------------------------------- */
4527:
4528: pnp_debug_pkg.log('+++++++++ calculate_rent END +++++++++++');
4529: pnp_debug_pkg.log('');
4530:
4531: EXCEPTION
4532: WHEN OTHERS THEN
4533: pnp_debug_pkg.log
4529: pnp_debug_pkg.log('');
4530:
4531: EXCEPTION
4532: WHEN OTHERS THEN
4533: pnp_debug_pkg.log
4534: ('**********************************************************************');
4535: pnp_debug_pkg.log('*** ERROR IN calculate_rent ***');
4536: pnp_debug_pkg.log('*** ERROR WHEN: '||l_context||' ***');
4537: pnp_debug_pkg.log
4531: EXCEPTION
4532: WHEN OTHERS THEN
4533: pnp_debug_pkg.log
4534: ('**********************************************************************');
4535: pnp_debug_pkg.log('*** ERROR IN calculate_rent ***');
4536: pnp_debug_pkg.log('*** ERROR WHEN: '||l_context||' ***');
4537: pnp_debug_pkg.log
4538: ('**********************************************************************');
4539: RAISE;
4532: WHEN OTHERS THEN
4533: pnp_debug_pkg.log
4534: ('**********************************************************************');
4535: pnp_debug_pkg.log('*** ERROR IN calculate_rent ***');
4536: pnp_debug_pkg.log('*** ERROR WHEN: '||l_context||' ***');
4537: pnp_debug_pkg.log
4538: ('**********************************************************************');
4539: RAISE;
4540:
4533: pnp_debug_pkg.log
4534: ('**********************************************************************');
4535: pnp_debug_pkg.log('*** ERROR IN calculate_rent ***');
4536: pnp_debug_pkg.log('*** ERROR WHEN: '||l_context||' ***');
4537: pnp_debug_pkg.log
4538: ('**********************************************************************');
4539: RAISE;
4540:
4541: END calculate_rent;
4670: l_exists_invoice BOOLEAN;
4671:
4672: BEGIN
4673:
4674: pnp_debug_pkg.log('+++++++++ insert_invoice_for START +++++++++++');
4675:
4676: FOR vr_rec IN org_c(p_vr_id => p_var_rent_id) LOOP
4677: l_org_id := vr_rec.org_id;
4678: END LOOP;
4677: l_org_id := vr_rec.org_id;
4678: END LOOP;
4679:
4680: l_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(l_org_id),4);
4681: pnp_debug_pkg.log('l_precision:'||l_precision);
4682:
4683: l_calc_type := g_calc_type;
4684: pnp_debug_pkg.log('calc_type:'|| l_calc_type);
4685:
4680: l_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(l_org_id),4);
4681: pnp_debug_pkg.log('l_precision:'||l_precision);
4682:
4683: l_calc_type := g_calc_type;
4684: pnp_debug_pkg.log('calc_type:'|| l_calc_type);
4685:
4686: /* loop for all invoice dates in the period */
4687: FOR inv_rec IN invoice_dates_c( p_vr_id => p_var_rent_id
4688: ,p_prd_id => p_period_id)
4724: /* we only look at the last invoice */
4725: EXIT;
4726:
4727: END LOOP; /* check if there exists an invoice for this invoice date */
4728: pnp_debug_pkg.log('l_var_rent_inv_id:' || l_var_rent_inv_id);
4729:
4730: /* atleast one invoice exists? */
4731: IF NOT l_exists_invoice
4732: /*AND inv_rec.total_actual_rent <> 0*/ THEN
4744: FOR vol_exists_rec IN vol_exists_c( p_var_rent_id,
4745: inv_rec.invoice_date
4746: ) LOOP
4747:
4748: pnp_debug_pkg.log('inv_rec.total_forecasted_rent'||inv_rec.total_forecasted_rent);
4749: pn_var_rent_inv_pkg.insert_row
4750: ( x_rowid => l_row_id,
4751: x_var_rent_inv_id => l_var_rent_inv_id,
4752: x_adjust_num => 0,
4846: l_prev_inv_rent := 0;
4847: l_curr_inv_rent := 0;
4848: l_curr_inv_rent_for := 0;
4849:
4850: pnp_debug_pkg.log('inv_rec.invoice_date'||inv_rec.invoice_date);
4851: pnp_debug_pkg.log('inv_rec.total_actual_rent:'||inv_rec.total_actual_rent);
4852: /* check if there exists an invoice for this invoice date */
4853: FOR inv2upd_rec IN invoice2upd_c( p_vr_id => p_var_rent_id
4854: ,p_prd_id => p_period_id
4847: l_curr_inv_rent := 0;
4848: l_curr_inv_rent_for := 0;
4849:
4850: pnp_debug_pkg.log('inv_rec.invoice_date'||inv_rec.invoice_date);
4851: pnp_debug_pkg.log('inv_rec.total_actual_rent:'||inv_rec.total_actual_rent);
4852: /* check if there exists an invoice for this invoice date */
4853: FOR inv2upd_rec IN invoice2upd_c( p_vr_id => p_var_rent_id
4854: ,p_prd_id => p_period_id
4855: ,p_inv_dt => inv_rec.invoice_date)
4874: l_curr_inv_rent := 0;
4875: l_curr_inv_rent_for := inv2upd_rec.for_per_rent;
4876:
4877: END IF; /* invoice updateable? */
4878: pnp_debug_pkg.log('inv2upd_rec.variance_exp_code:'||inv2upd_rec.variance_exp_code);
4879: pnp_debug_pkg.log('l_var_rent_inv_id:'||l_var_rent_inv_id);
4880: /* we only look at the last invoice */
4881: EXIT;
4882:
4875: l_curr_inv_rent_for := inv2upd_rec.for_per_rent;
4876:
4877: END IF; /* invoice updateable? */
4878: pnp_debug_pkg.log('inv2upd_rec.variance_exp_code:'||inv2upd_rec.variance_exp_code);
4879: pnp_debug_pkg.log('l_var_rent_inv_id:'||l_var_rent_inv_id);
4880: /* we only look at the last invoice */
4881: EXIT;
4882:
4883: END LOOP; /* check if there exists an invoice for this invoice date */
4994:
4995:
4996: END LOOP; /* loop for all invoice dates in the period */
4997:
4998: pnp_debug_pkg.log('+++++++++ insert_invoice_for END +++++++++++');
4999:
5000: EXCEPTION
5001: WHEN OTHERS THEN RAISE;
5002:
5098: l_last_partial VARCHAR2(1);
5099: l_period_num NUMBER := 0;
5100:
5101: BEGIN
5102: pnp_debug_pkg.log('++++++ Start get_rent_applicable ++++++');
5103: pnp_debug_pkg.log('p_trx_hdr_rec.invoice_flag:'||p_trx_hdr_rec.invoice_flag);
5104: IF NVL(p_trx_hdr_rec.invoice_flag, 'Y') = 'N' THEN
5105: RETURN;
5106: END IF;
5099: l_period_num NUMBER := 0;
5100:
5101: BEGIN
5102: pnp_debug_pkg.log('++++++ Start get_rent_applicable ++++++');
5103: pnp_debug_pkg.log('p_trx_hdr_rec.invoice_flag:'||p_trx_hdr_rec.invoice_flag);
5104: IF NVL(p_trx_hdr_rec.invoice_flag, 'Y') = 'N' THEN
5105: RETURN;
5106: END IF;
5107:
5129:
5130: /* get the breakpoint type - flat, sliding, stratified */
5131: FOR rec IN bkpt_type_c(p_bkdt_id => l_bkpts_t(1).bkpt_detail_id)
5132: LOOP
5133: pnp_debug_pkg.log('Breakpoint Type:'||rec.breakpoint_type);
5134: l_bkpt_type := rec.breakpoint_type;
5135: END LOOP;
5136:
5137: IF g_calculation_method IN ( pn_var_rent_calc_pkg.G_CALC_NON_CUMULATIVE
5144: THEN
5145: l_net_volume := l_trx_hdr_rec.ytd_sales_for;
5146:
5147: END IF;
5148: pnp_debug_pkg.log('l_net_volume'||l_net_volume);
5149: /* handling the case of volume not tripping any bkpts */
5150: /* init the calculated rent to 0 - it will be re-calculated */
5151: l_trx_hdr_rec.calculated_rent_for := 0;
5152: IF g_calculation_method IN ( pn_var_rent_calc_pkg.G_CALC_NON_CUMULATIVE
5163: l_trx_hdr_rec.calculated_rent
5164: := (l_net_volume - l_bkpts_t(1).pr_grp_blended_vol_start)
5165: * l_bkpts_t(1).bkpt_rate;
5166: */
5167: pnp_debug_pkg.log('Volume doesnot trip breakpoints');
5168: l_trx_hdr_rec.calculated_rent_for := 0;
5169:
5170: ELSE
5171:
5226:
5227: l_trx_hdr_rec.calculated_rent_for
5228: := l_trx_hdr_rec.calculated_rent_for
5229: + (l_bkpt_end - l_bkpt_start) * l_bkpts_t(i).bkpt_rate;
5230: pnp_debug_pkg.log('rent:'||l_trx_hdr_rec.calculated_rent_for);
5231:
5232: END IF;
5233:
5234: ELSE
5292: ELSIF l_bkpt_type = pn_var_rent_calc_pkg.G_BKPT_TYP_FLAT THEN
5293:
5294: l_trx_hdr_rec.calculated_rent_for
5295: := (l_net_volume - l_bkpt_start) * l_bkpts_t(i).bkpt_rate;
5296: pnp_debug_pkg.log('calculated_rent_for:'||l_trx_hdr_rec.calculated_rent_for);
5297: END IF;
5298: EXIT;
5299:
5300: END IF;
5381: l_trx_hdr_rec.calculated_rent_for := 0;
5382: END IF; */
5383:
5384:
5385: pnp_debug_pkg.log(' ');
5386: pnp_debug_pkg.log(' calculation_method: '||g_calculation_method);
5387: pnp_debug_pkg.log(' percent_rent_due: '||l_trx_hdr_rec.percent_rent_due_for);
5388: pnp_debug_pkg.log(' ytd_percent_rent: '||l_trx_hdr_rec.ytd_percent_rent_for);
5389: pnp_debug_pkg.log(' ');
5382: END IF; */
5383:
5384:
5385: pnp_debug_pkg.log(' ');
5386: pnp_debug_pkg.log(' calculation_method: '||g_calculation_method);
5387: pnp_debug_pkg.log(' percent_rent_due: '||l_trx_hdr_rec.percent_rent_due_for);
5388: pnp_debug_pkg.log(' ytd_percent_rent: '||l_trx_hdr_rec.ytd_percent_rent_for);
5389: pnp_debug_pkg.log(' ');
5390:
5383:
5384:
5385: pnp_debug_pkg.log(' ');
5386: pnp_debug_pkg.log(' calculation_method: '||g_calculation_method);
5387: pnp_debug_pkg.log(' percent_rent_due: '||l_trx_hdr_rec.percent_rent_due_for);
5388: pnp_debug_pkg.log(' ytd_percent_rent: '||l_trx_hdr_rec.ytd_percent_rent_for);
5389: pnp_debug_pkg.log(' ');
5390:
5391:
5384:
5385: pnp_debug_pkg.log(' ');
5386: pnp_debug_pkg.log(' calculation_method: '||g_calculation_method);
5387: pnp_debug_pkg.log(' percent_rent_due: '||l_trx_hdr_rec.percent_rent_due_for);
5388: pnp_debug_pkg.log(' ytd_percent_rent: '||l_trx_hdr_rec.ytd_percent_rent_for);
5389: pnp_debug_pkg.log(' ');
5390:
5391:
5392: /* ------------- GET percent_rent_due, ytd_percent_rent END ------------- */
5385: pnp_debug_pkg.log(' ');
5386: pnp_debug_pkg.log(' calculation_method: '||g_calculation_method);
5387: pnp_debug_pkg.log(' percent_rent_due: '||l_trx_hdr_rec.percent_rent_due_for);
5388: pnp_debug_pkg.log(' ytd_percent_rent: '||l_trx_hdr_rec.ytd_percent_rent_for);
5389: pnp_debug_pkg.log(' ');
5390:
5391:
5392: /* ------------- GET percent_rent_due, ytd_percent_rent END ------------- */
5393:
5391:
5392: /* ------------- GET percent_rent_due, ytd_percent_rent END ------------- */
5393:
5394: END IF; /* volume trips breakpoint?? */
5395: pnp_debug_pkg.log('percent_rent_due_for:'||l_trx_hdr_rec.percent_rent_due_for);
5396: pnp_debug_pkg.log('ytd_rent_for:'||l_trx_hdr_rec.ytd_percent_rent_for);
5397: l_trx_hdr_rec.update_flag := 'Y';
5398: p_trx_hdr_rec := l_trx_hdr_rec;
5399: pnp_debug_pkg.log('------------end get_rent_applicable---------');
5392: /* ------------- GET percent_rent_due, ytd_percent_rent END ------------- */
5393:
5394: END IF; /* volume trips breakpoint?? */
5395: pnp_debug_pkg.log('percent_rent_due_for:'||l_trx_hdr_rec.percent_rent_due_for);
5396: pnp_debug_pkg.log('ytd_rent_for:'||l_trx_hdr_rec.ytd_percent_rent_for);
5397: l_trx_hdr_rec.update_flag := 'Y';
5398: p_trx_hdr_rec := l_trx_hdr_rec;
5399: pnp_debug_pkg.log('------------end get_rent_applicable---------');
5400:
5395: pnp_debug_pkg.log('percent_rent_due_for:'||l_trx_hdr_rec.percent_rent_due_for);
5396: pnp_debug_pkg.log('ytd_rent_for:'||l_trx_hdr_rec.ytd_percent_rent_for);
5397: l_trx_hdr_rec.update_flag := 'Y';
5398: p_trx_hdr_rec := l_trx_hdr_rec;
5399: pnp_debug_pkg.log('------------end get_rent_applicable---------');
5400:
5401: EXCEPTION
5402: WHEN OTHERS THEN RAISE;
5403:
6154: OPEN grp_date_c( p_vr_id => p_var_rent_id
6155: ,p_prd_id => p_period_id);
6156: FETCH grp_date_c BULK COLLECT INTO l_grp_t;
6157: CLOSE grp_date_c;
6158: pnp_debug_pkg.log('total groups:'||l_grp_t.COUNT);
6159: /* loop for all lines in the annual period */
6160: FOR line_rec IN lines_c( p_vr_id => p_var_rent_id
6161: ,p_prd_id => p_period_id)
6162: LOOP
6159: /* loop for all lines in the annual period */
6160: FOR line_rec IN lines_c( p_vr_id => p_var_rent_id
6161: ,p_prd_id => p_period_id)
6162: LOOP
6163: pnp_debug_pkg.log('looping for line:'||line_rec.line_item_id);
6164: /* loop for all calc periods in the annual period */
6165: FOR g IN 1..l_grp_t.COUNT LOOP
6166: pnp_debug_pkg.log('looping for group:'||l_grp_t(g).grp_date_id);
6167: /* get the sum of rents and sales for
6162: LOOP
6163: pnp_debug_pkg.log('looping for line:'||line_rec.line_item_id);
6164: /* loop for all calc periods in the annual period */
6165: FOR g IN 1..l_grp_t.COUNT LOOP
6166: pnp_debug_pkg.log('looping for group:'||l_grp_t(g).grp_date_id);
6167: /* get the sum of rents and sales for
6168: vr -> period -> line item -> group combination
6169: from the trx tables
6170: -- loops only once --
6175: ,p_grp_id => l_grp_t(g).grp_date_id)
6176: LOOP
6177:
6178: l_vr_summ_id := NULL;
6179: pnp_debug_pkg.log('rent'||summ_rec.rent);
6180: /* chk if VR SUMM record exists for this
6181: vr -> period -> line item -> group combination */
6182: FOR vr_summ_rec IN vr_summ_c( p_vr_id => p_var_rent_id
6183: ,p_prd_id => p_period_id
6189:
6190: IF NVL(summ_rec.sales,0) <> NVL(vr_summ_rec.tot_for_vol,0) OR
6191: NVL(summ_rec.rent,0) <> NVL(vr_summ_rec.for_var_rent,0)
6192: THEN
6193: pnp_debug_pkg.log('sales:'||summ_rec.sales);
6194: pnp_debug_pkg.log('rent: '||summ_rec.rent);
6195: UPDATE
6196: pn_var_rent_summ_all
6197: SET
6190: IF NVL(summ_rec.sales,0) <> NVL(vr_summ_rec.tot_for_vol,0) OR
6191: NVL(summ_rec.rent,0) <> NVL(vr_summ_rec.for_var_rent,0)
6192: THEN
6193: pnp_debug_pkg.log('sales:'||summ_rec.sales);
6194: pnp_debug_pkg.log('rent: '||summ_rec.rent);
6195: UPDATE
6196: pn_var_rent_summ_all
6197: SET
6198: tot_for_vol = summ_rec.sales
6209:
6210: IF l_vr_summ_id IS NULL THEN
6211:
6212: /* to insert a new summary record */
6213: pnp_debug_pkg.log('Inserting a new record for var_rent_summ table');
6214: INSERT INTO
6215: pn_var_rent_summ_all
6216: (var_rent_summ_id
6217: ,var_rent_id
6247: RETURNING
6248: var_rent_summ_id
6249: INTO
6250: l_vr_summ_id;
6251: pnp_debug_pkg.log('Inserted rent summ with id: '||l_vr_summ_id);
6252: END IF;
6253: UPDATE
6254: pn_var_trx_headers_all hdr
6255: SET
6470:
6471:
6472: BEGIN
6473:
6474: pnp_debug_pkg.log
6475: ('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
6476: pnp_debug_pkg.log('+++++++++ process_rent_batch START +++++++++++');
6477: pnp_debug_pkg.log(' ');
6478: pnp_debug_pkg.log('Setting invoice_on and calc_type');
6472: BEGIN
6473:
6474: pnp_debug_pkg.log
6475: ('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
6476: pnp_debug_pkg.log('+++++++++ process_rent_batch START +++++++++++');
6477: pnp_debug_pkg.log(' ');
6478: pnp_debug_pkg.log('Setting invoice_on and calc_type');
6479: -- g_invoice_on := p_invoice_on;
6480: g_calc_type := p_calc_type;
6473:
6474: pnp_debug_pkg.log
6475: ('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
6476: pnp_debug_pkg.log('+++++++++ process_rent_batch START +++++++++++');
6477: pnp_debug_pkg.log(' ');
6478: pnp_debug_pkg.log('Setting invoice_on and calc_type');
6479: -- g_invoice_on := p_invoice_on;
6480: g_calc_type := p_calc_type;
6481: pnp_debug_pkg.log('g_invoice_on:'||g_invoice_on);
6474: pnp_debug_pkg.log
6475: ('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
6476: pnp_debug_pkg.log('+++++++++ process_rent_batch START +++++++++++');
6477: pnp_debug_pkg.log(' ');
6478: pnp_debug_pkg.log('Setting invoice_on and calc_type');
6479: -- g_invoice_on := p_invoice_on;
6480: g_calc_type := p_calc_type;
6481: pnp_debug_pkg.log('g_invoice_on:'||g_invoice_on);
6482: pnp_debug_pkg.log('g_calc_type'||g_calc_type);
6477: pnp_debug_pkg.log(' ');
6478: pnp_debug_pkg.log('Setting invoice_on and calc_type');
6479: -- g_invoice_on := p_invoice_on;
6480: g_calc_type := p_calc_type;
6481: pnp_debug_pkg.log('g_invoice_on:'||g_invoice_on);
6482: pnp_debug_pkg.log('g_calc_type'||g_calc_type);
6483:
6484: pnp_debug_pkg.log('pn_var_rent_calc_pkg.process_rent_batch (+)' );
6485:
6478: pnp_debug_pkg.log('Setting invoice_on and calc_type');
6479: -- g_invoice_on := p_invoice_on;
6480: g_calc_type := p_calc_type;
6481: pnp_debug_pkg.log('g_invoice_on:'||g_invoice_on);
6482: pnp_debug_pkg.log('g_calc_type'||g_calc_type);
6483:
6484: pnp_debug_pkg.log('pn_var_rent_calc_pkg.process_rent_batch (+)' );
6485:
6486:
6480: g_calc_type := p_calc_type;
6481: pnp_debug_pkg.log('g_invoice_on:'||g_invoice_on);
6482: pnp_debug_pkg.log('g_calc_type'||g_calc_type);
6483:
6484: pnp_debug_pkg.log('pn_var_rent_calc_pkg.process_rent_batch (+)' );
6485:
6486:
6487: fnd_message.set_name ('PN','PN_VRAM_PRM');
6488: fnd_message.set_token ('PT_NAME', p_property_name);
6496: fnd_message.set_token ('PRD_FRM', p_period_num_from);
6497: fnd_message.set_token ('PRD_TO', p_period_num_to);
6498: fnd_message.set_token ('USR', p_responsible_user);
6499: fnd_message.set_token ('DATE', p_period_date);
6500: pnp_debug_pkg.put_log_msg(fnd_message.get);
6501:
6502: pnp_debug_pkg.log ('process_rent_batch - Calculation TYPE : ' || p_calc_TYPE);
6503:
6504: IF p_property_code IS NOT NULL OR p_property_name IS NOT NULL THEN
6498: fnd_message.set_token ('USR', p_responsible_user);
6499: fnd_message.set_token ('DATE', p_period_date);
6500: pnp_debug_pkg.put_log_msg(fnd_message.get);
6501:
6502: pnp_debug_pkg.log ('process_rent_batch - Calculation TYPE : ' || p_calc_TYPE);
6503:
6504: IF p_property_code IS NOT NULL OR p_property_name IS NOT NULL THEN
6505:
6506: OPEN csr_get_vrent_wprop;
6524: IF csr_get_vrent_wprop%ISOPEN THEN
6525:
6526: FETCH csr_get_vrent_wprop INTO l_var_rent_id,l_invoice_on,l_cumulative,l_rent_num,g_org_id;
6527: EXIT WHEN csr_get_vrent_wprop%NOTFOUND;
6528: pnp_debug_pkg.log ('process_rent_batch - Inside cur : ' );
6529: ELSIF csr_get_vrent_wloc%ISOPEN THEN
6530:
6531: FETCH csr_get_vrent_wloc INTO l_var_rent_id,l_invoice_on,l_cumulative,l_rent_num,g_org_id;
6532: EXIT WHEN csr_get_vrent_wloc%NOTFOUND;
6542: g_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(g_org_id),4);
6543:
6544: fnd_message.set_name ('PN','PN_VRAM_VRN_PROC');
6545: fnd_message.set_token ('NUM',l_rent_num);
6546: pnp_debug_pkg.put_log_msg(fnd_message.get);
6547:
6548: pnp_debug_pkg.log ('process_rent_batch - Variable Rent id :'||l_var_rent_id);
6549: pnp_debug_pkg.log ('process_rent_batch - org_id :'||g_org_id);
6550:
6544: fnd_message.set_name ('PN','PN_VRAM_VRN_PROC');
6545: fnd_message.set_token ('NUM',l_rent_num);
6546: pnp_debug_pkg.put_log_msg(fnd_message.get);
6547:
6548: pnp_debug_pkg.log ('process_rent_batch - Variable Rent id :'||l_var_rent_id);
6549: pnp_debug_pkg.log ('process_rent_batch - org_id :'||g_org_id);
6550:
6551: v_var_id_details_exists :=PN_VAR_RENT_PKG.find_if_volhist_bkpts_exist
6552: ( l_var_rent_id,
6545: fnd_message.set_token ('NUM',l_rent_num);
6546: pnp_debug_pkg.put_log_msg(fnd_message.get);
6547:
6548: pnp_debug_pkg.log ('process_rent_batch - Variable Rent id :'||l_var_rent_id);
6549: pnp_debug_pkg.log ('process_rent_batch - org_id :'||g_org_id);
6550:
6551: v_var_id_details_exists :=PN_VAR_RENT_PKG.find_if_volhist_bkpts_exist
6552: ( l_var_rent_id,
6553: 'VAR_RENT_ID' );
6558: LOOP
6559:
6560: fnd_message.set_name ('PN','PN_VRAM_PRD_PROC');
6561: fnd_message.set_token ('NUM',rec_get_per.period_num);
6562: pnp_debug_pkg.put_log_msg(fnd_message.get);
6563:
6564: pnp_debug_pkg.log ('process_rent_batch - period st date : '||rec_get_per.start_date);
6565: pnp_debug_pkg.log ('process_rent_batch - period end date: '||rec_get_per.end_date);
6566:
6560: fnd_message.set_name ('PN','PN_VRAM_PRD_PROC');
6561: fnd_message.set_token ('NUM',rec_get_per.period_num);
6562: pnp_debug_pkg.put_log_msg(fnd_message.get);
6563:
6564: pnp_debug_pkg.log ('process_rent_batch - period st date : '||rec_get_per.start_date);
6565: pnp_debug_pkg.log ('process_rent_batch - period end date: '||rec_get_per.end_date);
6566:
6567:
6568:
6561: fnd_message.set_token ('NUM',rec_get_per.period_num);
6562: pnp_debug_pkg.put_log_msg(fnd_message.get);
6563:
6564: pnp_debug_pkg.log ('process_rent_batch - period st date : '||rec_get_per.start_date);
6565: pnp_debug_pkg.log ('process_rent_batch - period end date: '||rec_get_per.end_date);
6566:
6567:
6568:
6569: pn_var_rent_calc_pkg.calculate_rent
6586: END IF;
6587:
6588:
6589:
6590: pnp_debug_pkg.log(' ');
6591: pnp_debug_pkg.log('+++++++++ process_rent_batch END +++++++++++');
6592: pnp_debug_pkg.log('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
6593:
6594: EXCEPTION
6587:
6588:
6589:
6590: pnp_debug_pkg.log(' ');
6591: pnp_debug_pkg.log('+++++++++ process_rent_batch END +++++++++++');
6592: pnp_debug_pkg.log('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
6593:
6594: EXCEPTION
6595: WHEN OTHERS THEN
6588:
6589:
6590: pnp_debug_pkg.log(' ');
6591: pnp_debug_pkg.log('+++++++++ process_rent_batch END +++++++++++');
6592: pnp_debug_pkg.log('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
6593:
6594: EXCEPTION
6595: WHEN OTHERS THEN
6596: pnp_debug_pkg.log('Error in pn_var_rent_calc_pkg.process_rent_batch :'||TO_CHAR(sqlcode)||' : '||sqlerrm);
6592: pnp_debug_pkg.log('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
6593:
6594: EXCEPTION
6595: WHEN OTHERS THEN
6596: pnp_debug_pkg.log('Error in pn_var_rent_calc_pkg.process_rent_batch :'||TO_CHAR(sqlcode)||' : '||sqlerrm);
6597: Errbuf := SQLERRM;
6598: Retcode := 2;
6599: rollback;
6600: raise;
6597: Errbuf := SQLERRM;
6598: Retcode := 2;
6599: rollback;
6600: raise;
6601: pnp_debug_pkg.log('pn_var_rent_calc_pkg.process_rent_batch (-) ');
6602:
6603: END process_rent_batch;
6604:
6605: FUNCTION END_BREAKPOINT(bkpt_start IN NUMBER, bkpt_end IN NUMBER) RETURN NUMBER IS
8803: AND gd1.invoice_date=temp.inv_dt(+)
8804: ORDER BY gd1.invoice_date;
8805:
8806: BEGIN
8807: pnp_debug_pkg.log('+++++++++ POP_INV_DATE_TAB START +++++++++++');
8808:
8809: OPEN get_inv_dates_cur(p_var_rent_id,p_status) ;
8810: FETCH get_inv_dates_cur BULK COLLECT INTO inv_date_tab;
8811: CLOSE get_inv_dates_cur;
8809: OPEN get_inv_dates_cur(p_var_rent_id,p_status) ;
8810: FETCH get_inv_dates_cur BULK COLLECT INTO inv_date_tab;
8811: CLOSE get_inv_dates_cur;
8812:
8813: pnp_debug_pkg.log('+++++++++ POP_INV_DATE_TAB END +++++++++++');
8814: END pop_inv_date_tab;
8815: --------------------------------------------------------------------------------
8816: -- NAME : POP_INV_DATE_TAB_FIRSTYR
8817: -- DESCRIPTION :
8872: and gd1.invoice_date=temp.inv_dt(+)
8873: order by gd1.invoice_date;
8874:
8875: BEGIN
8876: pnp_debug_pkg.log('+++++++++ POP_INV_DATE_TAB_FIRSTYR START +++++++++++');
8877:
8878: FOR fst_prd_cur IN get_fst_prd_flag(p_var_rent_id ,p_status) LOOP
8879: g_partial_prd_flag := fst_prd_cur.fst_prd_flag;
8880: END LOOP;
8878: FOR fst_prd_cur IN get_fst_prd_flag(p_var_rent_id ,p_status) LOOP
8879: g_partial_prd_flag := fst_prd_cur.fst_prd_flag;
8880: END LOOP;
8881:
8882: --pnp_debug_pkg.log('g_partial_prd_flag='||g_partial_prd_flag);
8883:
8884: OPEN get_partial_prd_inv_dates(p_var_rent_id,p_status) ;
8885: FETCH get_partial_prd_inv_dates BULK COLLECT INTO inv_date_tab;
8886: CLOSE get_partial_prd_inv_dates;
8884: OPEN get_partial_prd_inv_dates(p_var_rent_id,p_status) ;
8885: FETCH get_partial_prd_inv_dates BULK COLLECT INTO inv_date_tab;
8886: CLOSE get_partial_prd_inv_dates;
8887:
8888: pnp_debug_pkg.log('+++++++++ POP_INV_DATE_TAB_FIRSTYR END +++++++++++');
8889: END POP_INV_DATE_TAB_FIRSTYR;
8890:
8891: --------------------------------------------------------------------------------
8892: -- NAME : ROLL_FWD_PARTIAL_PRD
8982: AND adjust_num=0;
8983:
8984:
8985: BEGIN
8986: pnp_debug_pkg.log('+++++++++ ROLL_FWD_PARTIAL_PRD START +++++++++++');
8987:
8988: FOR rec IN org_cur(p_var_rent_id) LOOP
8989: l_org_id := rec.org_id;
8990: END LOOP;
9001: FOR abtmt_exists_rec IN get_var_abtmt_id(p_var_rent_id) LOOP
9002: l_min_var_abt_id := abtmt_exists_rec.var_abatement_id;
9003: l_min_pmt_term_id := abtmt_exists_rec.payment_term_id;
9004:
9005: --pnp_debug_pkg.log('prior to update');
9006: PN_VAR_ABATEMENTS_PKG.LOCK_ROW(p_var_rent_id,-1,l_min_pmt_term_id);
9007: update pn_var_abatements_all
9008: set var_rent_inv_id = l_fst_inv_id
9009: where var_abatement_id = l_min_var_abt_id;
9017:
9018: l_inv_id:=NULL;
9019: l_prev_inv_id :=NULL;
9020: IF (inv_date_tab(i).inv_flag ='Y') THEN
9021: pnp_debug_pkg.log('invoice '||inv_date_tab(i).inv_flag||inv_date_tab(i).inv_date||inv_date_tab(i).period_id);
9022:
9023: FOR rec IN check_inv_exists(p_var_rent_id ,inv_date_tab(i).inv_date,inv_date_tab(i).period_id) LOOP
9024: l_inv_id:= rec.inv_id;
9025: END LOOP;
9022:
9023: FOR rec IN check_inv_exists(p_var_rent_id ,inv_date_tab(i).inv_date,inv_date_tab(i).period_id) LOOP
9024: l_inv_id:= rec.inv_id;
9025: END LOOP;
9026: pnp_debug_pkg.log('invoice id= '||l_inv_id);
9027:
9028: IF i=1 THEN
9029: l_prev_inv_id:=l_fst_inv_id;
9030: ELSE
9072:
9073: END IF;
9074: END IF;
9075: END LOOP;
9076: pnp_debug_pkg.log('+++++++++ ROLL_FWD_PARTIAL_PRD END +++++++++++');
9077: END ROLL_FWD_PARTIAL_PRD;
9078: --------------------------------------------------------------------------------
9079: -- NAME : ROLL_FWD_LST_PARTIAL_PRD
9080: -- DESCRIPTION : Rolls forward the abatement selections when the last partial
9163: l_var_abmt_id NUMBER :=NULL;
9164: l_last_inv_dt DATE :=NULL;
9165: j NUMBER :=NULL;
9166: BEGIN
9167: pnp_debug_pkg.log('+++++++++ ROLL_FWD_LST_PARTIAL_PRD START +++++++++++');
9168:
9169: --1. Get last partial period id
9170: --2. Get invoice date for the last invoice of this partial period whose grp end date is
9171: -- Variable Rent Agreement's termination date
9182: FOR last_prd_rec IN get_last_partial_prd(p_var_rent_id) LOOP
9183: l_last_prd_id := last_prd_rec.period_id;
9184: END LOOP;
9185:
9186: --pnp_debug_pkg.log('last period id= '||l_last_prd_id);
9187:
9188: IF l_last_prd_id IS NOT NULL THEN
9189:
9190: FOR rec IN get_inv_date(p_var_rent_id,l_last_prd_id,g_vr_termination_date) LOOP
9203:
9204: FOR last_inv_rec IN get_last_invoice_id(p_var_rent_id,l_last_prd_id,inv_date_tab(j).inv_date) LOOP
9205: l_last_inv_id := last_inv_rec.var_rent_inv_id;
9206: END LOOP;
9207: --pnp_debug_pkg.log('last invoice id= '||l_last_inv_id);
9208:
9209: FOR inv_rec IN get_last_prd_inv(p_var_rent_id,l_last_prd_id,l_last_inv_id) LOOP
9210: l_inv_id := inv_rec.var_rent_inv_id;
9211: --pnp_debug_pkg.log('invoice id = '||l_inv_id);
9207: --pnp_debug_pkg.log('last invoice id= '||l_last_inv_id);
9208:
9209: FOR inv_rec IN get_last_prd_inv(p_var_rent_id,l_last_prd_id,l_last_inv_id) LOOP
9210: l_inv_id := inv_rec.var_rent_inv_id;
9211: --pnp_debug_pkg.log('invoice id = '||l_inv_id);
9212:
9213: FOR abtmt_inv_rec IN get_abtmt_terms_inv(l_inv_id) LOOP
9214: l_pmt_term_id := abtmt_inv_rec.payment_term_id;
9215:
9245:
9246: END IF;
9247:
9248: END IF;
9249: pnp_debug_pkg.log('+++++++++ ROLL_FWD_LST_PARTIAL_PRD END +++++++++++');
9250: END ROLL_FWD_LST_PARTIAL_PRD;
9251:
9252: --------------------------------------------------------------------------------
9253: -- NAME : ROLL_FWD_SELECNS
9337: WHERE var_rent_id =p_var_rent_id;
9338:
9339: BEGIN
9340: /* Get 1st invoice */
9341: pnp_debug_pkg.log('+++++++++ ROLL_FWD_SELECNS START +++++++++++');
9342:
9343: FOR rec IN org_cur(p_var_rent_id) LOOP
9344: l_org_id := rec.org_id;
9345: END LOOP;
9346:
9347: FOR min_inv_rec IN get_min_inv(p_var_rent_id) LOOP
9348: l_fst_inv_dt := min_inv_rec.fst_inv_dt;
9349: END LOOP;
9350: --pnp_debug_pkg.log('1st invoice date= '||l_fst_inv_dt);
9351:
9352: FOR i IN 1..inv_date_tab.COUNT LOOP
9353:
9354: l_inv_id:=NULL;
9354: l_inv_id:=NULL;
9355: l_prev_inv_id :=NULL;
9356:
9357: IF (inv_date_tab(i).inv_flag ='Y') THEN
9358: --pnp_debug_pkg.log('invoice '||inv_date_tab(i).inv_flag||inv_date_tab(i).inv_date||p_var_rent_id);
9359:
9360: FOR rec IN check_inv_exists(p_var_rent_id ,inv_date_tab(i).inv_date,inv_date_tab(i).period_id) LOOP
9361: l_inv_id:= rec.inv_id;
9362: END LOOP;
9359:
9360: FOR rec IN check_inv_exists(p_var_rent_id ,inv_date_tab(i).inv_date,inv_date_tab(i).period_id) LOOP
9361: l_inv_id:= rec.inv_id;
9362: END LOOP;
9363: --pnp_debug_pkg.log('invoice id= '||l_inv_id);
9364:
9365: IF (inv_date_tab(i).inv_date=l_fst_inv_dt) THEN
9366: /* updation for 1st invoice */
9367:
9371: FOR abtmt_exists_rec IN get_var_abtmt_id(p_var_rent_id) LOOP
9372:
9373: l_min_var_abt_id:= abtmt_exists_rec.var_abatement_id;
9374: l_min_pmt_term_id:= abtmt_exists_rec.payment_term_id;
9375: --pnp_debug_pkg.log('prior to update');
9376:
9377: PN_VAR_ABATEMENTS_PKG.LOCK_ROW(p_var_rent_id,-1,l_min_pmt_term_id);
9378: UPDATE pn_var_abatements_all
9379: SET var_rent_inv_id=l_inv_id
9430: END IF;
9431: END IF;
9432:
9433: END LOOP;
9434: pnp_debug_pkg.log('+++++++++ ROLL_FWD_SELECNS END +++++++++++');
9435: END ROLL_FWD_SELECNS;
9436: --------------------------------------------------------------------------------
9437: -- NAME : include_increases_firstyr
9438: -- DESCRIPTION : Includes Rent Increase terms to be abated for 1st partial period
9508: l_var_abmt_id NUMBER := NULL;
9509: l_org_id NUMBER;
9510:
9511: BEGIN
9512: pnp_debug_pkg.log('+++++++++ include_increases_firstyr START +++++++++++');
9513: FOR rec IN org_cur(p_var_rent_id) LOOP
9514: l_org_id := rec.org_id;
9515: END LOOP;
9516: IF g_partial_prd_flag='Y' THEN
9518: FOR fst_inv_rec IN get_fst_inv_id(p_var_rent_id) LOOP
9519: l_inv_id := fst_inv_rec.var_rent_inv_id;
9520: END LOOP;
9521:
9522: --pnp_debug_pkg.log('invoice_id = '||l_inv_id);
9523: FOR parent_rec IN get_abtmt_terms_inv(l_inv_id ,p_var_rent_id ) LOOP
9524:
9525: --pnp_debug_pkg.log('parent term = '||parent_rec.payment_term_id);
9526: FOR rent_incr IN get_rent_incr_cur(parent_rec.payment_term_id ,l_inv_id ) LOOP
9521:
9522: --pnp_debug_pkg.log('invoice_id = '||l_inv_id);
9523: FOR parent_rec IN get_abtmt_terms_inv(l_inv_id ,p_var_rent_id ) LOOP
9524:
9525: --pnp_debug_pkg.log('parent term = '||parent_rec.payment_term_id);
9526: FOR rent_incr IN get_rent_incr_cur(parent_rec.payment_term_id ,l_inv_id ) LOOP
9527:
9528: --pnp_debug_pkg.log('child_term = '||rent_incr.rent_incr_term_id);
9529: l_abtmt_exists := NULL;
9524:
9525: --pnp_debug_pkg.log('parent term = '||parent_rec.payment_term_id);
9526: FOR rent_incr IN get_rent_incr_cur(parent_rec.payment_term_id ,l_inv_id ) LOOP
9527:
9528: --pnp_debug_pkg.log('child_term = '||rent_incr.rent_incr_term_id);
9529: l_abtmt_exists := NULL;
9530:
9531: OPEN check_abtmt_exists(rent_incr.rent_incr_term_id,l_inv_id);
9532: FETCH check_abtmt_exists INTO l_abtmt_exists ;
9556: END LOOP;
9557:
9558: END LOOP;
9559: END IF;
9560: pnp_debug_pkg.log('+++++++++ include_increases_firstyr END +++++++++++');
9561: END include_increases_firstyr;
9562: --------------------------------------------------------------------------------
9563: -- NAME : include_increases
9564: -- DESCRIPTION : Includes Rent Increase terms to be abated
9634: l_var_abmt_id NUMBER := NULL;
9635: l_org_id NUMBER;
9636: BEGIN
9637:
9638: pnp_debug_pkg.log('+++++++++ include_increases START +++++++++++');
9639: FOR rec IN org_cur(p_var_rent_id) LOOP
9640: l_org_id := rec.org_id;
9641: END LOOP;
9642:
9645: IF (inv_date_tab(i).inv_flag ='Y') THEN
9646: FOR rec IN check_inv_exists(p_var_rent_id ,inv_date_tab(i).inv_date,inv_date_tab(i).period_id) LOOP
9647: l_inv_id:= rec.inv_id;
9648: END LOOP;
9649: --pnp_debug_pkg.log('invoice_id = '||l_inv_id);
9650:
9651: FOR parent_rec IN get_abtmt_terms_inv(l_inv_id ,p_var_rent_id ) LOOP
9652:
9653: --pnp_debug_pkg.log('parent term = '||parent_rec.payment_term_id);
9649: --pnp_debug_pkg.log('invoice_id = '||l_inv_id);
9650:
9651: FOR parent_rec IN get_abtmt_terms_inv(l_inv_id ,p_var_rent_id ) LOOP
9652:
9653: --pnp_debug_pkg.log('parent term = '||parent_rec.payment_term_id);
9654:
9655: FOR rent_incr IN get_rent_incr_cur(parent_rec.payment_term_id ,l_inv_id ) LOOP
9656:
9657: --pnp_debug_pkg.log('child_term = '||rent_incr.rent_incr_term_id);
9653: --pnp_debug_pkg.log('parent term = '||parent_rec.payment_term_id);
9654:
9655: FOR rent_incr IN get_rent_incr_cur(parent_rec.payment_term_id ,l_inv_id ) LOOP
9656:
9657: --pnp_debug_pkg.log('child_term = '||rent_incr.rent_incr_term_id);
9658:
9659: l_abtmt_exists := NULL;
9660: OPEN check_abtmt_exists(rent_incr.rent_incr_term_id,l_inv_id);
9661: FETCH check_abtmt_exists INTO l_abtmt_exists ;
9688:
9689: END LOOP;
9690: END IF;
9691: END LOOP;
9692: pnp_debug_pkg.log('+++++++++ include_increases END +++++++++++');
9693: END include_increases;
9694:
9695: --------------------------------------------------------------------------------
9696: -- NAME : post_summary - global procedure
9788: l_vr_summ_id NUMBER;
9789:
9790: BEGIN
9791:
9792: pnp_debug_pkg.log('+++++++++ post_summary START +++++++++++');
9793:
9794: l_grp_t.DELETE;
9795:
9796: OPEN grp_date_c( p_vr_id => p_var_rent_id
9802: FOR line_rec IN lines_c( p_vr_id => p_var_rent_id
9803: ,p_prd_id => p_period_id)
9804: LOOP
9805:
9806: pnp_debug_pkg.log('line_rec.line_item_id...'||line_rec.line_item_id);
9807: /* loop for all calc periods in the annual period */
9808: FOR g IN 1..l_grp_t.COUNT LOOP
9809:
9810: /* get the sum of rents and sales for
9811: vr -> period -> line item -> group combination
9812: from the trx tables
9813: -- loops only once --
9814: */
9815: pnp_debug_pkg.log('g..'||g);
9816: FOR summ_rec IN summ_c( p_vr_id => p_var_rent_id
9817: ,p_prd_id => p_period_id
9818: ,p_line_id => line_rec.line_item_id
9819: ,p_grp_id => l_grp_t(g).grp_date_id)
9818: ,p_line_id => line_rec.line_item_id
9819: ,p_grp_id => l_grp_t(g).grp_date_id)
9820: LOOP
9821:
9822: pnp_debug_pkg.log('summ_rec.sales..'||summ_rec.sales);
9823: pnp_debug_pkg.log('summ_rec.rent..'||summ_rec.rent);
9824: pnp_debug_pkg.log('summ_rec.first_yr_rent..'||summ_rec.first_yr_rent);
9825: l_vr_summ_id := NULL;
9826:
9819: ,p_grp_id => l_grp_t(g).grp_date_id)
9820: LOOP
9821:
9822: pnp_debug_pkg.log('summ_rec.sales..'||summ_rec.sales);
9823: pnp_debug_pkg.log('summ_rec.rent..'||summ_rec.rent);
9824: pnp_debug_pkg.log('summ_rec.first_yr_rent..'||summ_rec.first_yr_rent);
9825: l_vr_summ_id := NULL;
9826:
9827: /* chk if VR SUMM record exists for this
9820: LOOP
9821:
9822: pnp_debug_pkg.log('summ_rec.sales..'||summ_rec.sales);
9823: pnp_debug_pkg.log('summ_rec.rent..'||summ_rec.rent);
9824: pnp_debug_pkg.log('summ_rec.first_yr_rent..'||summ_rec.first_yr_rent);
9825: l_vr_summ_id := NULL;
9826:
9827: /* chk if VR SUMM record exists for this
9828: vr -> period -> line item -> group combination */
9833: LOOP
9834:
9835: l_vr_summ_id := vr_summ_rec.var_rent_summ_id;
9836:
9837: pnp_debug_pkg.log('l_vr_summ_id..'||l_vr_summ_id);
9838:
9839: IF summ_rec.sales <> NVL(vr_summ_rec.tot_act_vol, 0) OR
9840: summ_rec.rent <> NVL(vr_summ_rec.act_var_rent, 0) OR
9841: summ_rec.first_yr_rent <> NVL(vr_summ_rec.first_yr_rent, 0) OR
9841: summ_rec.first_yr_rent <> NVL(vr_summ_rec.first_yr_rent, 0) OR
9842: NVL(summ_rec.ded, 0) <> NVL(vr_summ_rec.tot_ded, 0)
9843: THEN
9844:
9845: pnp_debug_pkg.log('before updation ...');
9846: UPDATE
9847: pn_var_rent_summ_all
9848: SET
9849: tot_act_vol = summ_rec.sales
9861: END LOOP; /* chk if VR SUMM exists vr -> prd -> line -> grp combo */
9862:
9863: IF l_vr_summ_id IS NULL THEN
9864:
9865: pnp_debug_pkg.log('before insertion ...');
9866:
9867: /* to insert a new summary record */
9868: INSERT INTO
9869: pn_var_rent_summ_all
9927: END LOOP; /* loop for all calc periods in the annual period */
9928:
9929: END LOOP; /* loop for all lines in the annual period */
9930:
9931: pnp_debug_pkg.log('+++++++++ post_summary END +++++++++++');
9932:
9933: EXCEPTION
9934: WHEN OTHERS THEN RAISE;
9935:
10575: l_ly_calc NUMBER := NULL;
10576: l_new_term_date DATE;
10577:
10578: BEGIN
10579: pnp_debug_pkg.log('+++++++++ VALIDATE_LY_CALC START +++++++++++');
10580:
10581: FOR vr_length_rec IN vr_length
10582: LOOP
10583:
10609: END LOOP;
10610:
10611: return l_ly_calc;
10612:
10613: pnp_debug_pkg.log('+++++++++ VALIDATE_LY_CALC END +++++++++++');
10614:
10615: END VALIDATE_LY_CALC;
10616: --------------------------------------------------------------------------------
10617: -- NAME : full_yr_summary