[Home] [Help]
77: l_org_id NUMBER;
78:
79: BEGIN
80:
81: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_PERIODS_ROW (+)');
82:
83: -------------------------------------------------------
84: -- We need to generate the period number
85: -------------------------------------------------------
179: RAISE NO_DATA_FOUND;
180: END IF;
181: CLOSE c;
182:
183: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_PERIODS_ROW (-)');
184:
185: END INSERT_PERIODS_ROW;
186:
187: /*===========================================================================+
218: l_date DATE;
219:
220: BEGIN
221:
222: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_PERIODS_ROW (+)');
223:
224: l_date := NVL(x_term_date,(TO_DATE('01/01/1776','mm/dd/yyyy')));
225:
226: DELETE FROM PN_VAR_PERIODS_ALL
234: RAISE NO_DATA_FOUND;
235: END IF;
236: END IF;
237:
238: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_PERIODS_ROW (-)');
239:
240: END DELETE_PERIODS_ROW;
241:
242:
454: l_org_id NUMBER ;
455:
456: BEGIN
457:
458: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_REPORT_DATE_ROW (+)');
459:
460: -------------------------------------------------------
461: -- SELECT the nextval FOR report date id
462: -------------------------------------------------------
542: RAISE NO_DATA_FOUND;
543: END IF;
544: CLOSE c;
545:
546: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_REPORT_DATE_ROW (-)');
547:
548: END INSERT_REPORT_DATE_ROW;
549:
550:
580: l_date DATE;
581:
582: BEGIN
583:
584: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_REPORT_DATE_ROW (+)');
585:
586: l_date := NVL(X_END_DATE,(TO_DATE('01/01/1776','mm/dd/yyyy')));
587:
588: DELETE FROM PN_VAR_REPORT_DATES_ALL
589: WHERE VAR_RENT_ID = X_VAR_RENT_ID
590: AND REPORT_START_DATE > l_date
591: AND REPORT_END_DATE > l_date;
592:
593: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_REPORT_DATE_ROW (-)');
594:
595: END DELETE_REPORT_DATE_ROW;
596:
597:
674: l_org_id NUMBER ;
675:
676: BEGIN
677:
678: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW (+)');
679:
680: -------------------------------------------------------
681: -- SELECT the nextval FOR group date id
682: -------------------------------------------------------
750: RAISE NO_DATA_FOUND;
751: END IF;
752: CLOSE c;
753:
754: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW (-)');
755:
756: END INSERT_GRP_DATE_ROW;
757:
758: /*===========================================================================+
789: l_date DATE;
790:
791: BEGIN
792:
793: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_GRP_DATE_ROW (+)');
794:
795: l_date := NVL(x_term_date,(TO_DATE('01/01/1776','mm/dd/yyyy')));
796:
797: DELETE FROM PN_VAR_GRP_DATES_ALL
806: RAISE NO_DATA_FOUND;
807: END IF;
808: END IF;
809:
810: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_GRP_DATE_ROW (-)');
811:
812: END DELETE_GRP_DATE_ROW;
813:
814: /*=============================================================================+
1057:
1058:
1059: BEGIN
1060:
1061: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (+)');
1062: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1063: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1064: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1065: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1058:
1059: BEGIN
1060:
1061: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (+)');
1062: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1063: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1064: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1065: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1066: pnp_debug_pkg.debug ('p_create_flag '||p_create_flag);
1059: BEGIN
1060:
1061: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (+)');
1062: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1063: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1064: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1065: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1066: pnp_debug_pkg.debug ('p_create_flag '||p_create_flag);
1067:
1060:
1061: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (+)');
1062: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1063: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1064: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1065: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1066: pnp_debug_pkg.debug ('p_create_flag '||p_create_flag);
1067:
1068: -- Get dates/info, GL calendar used from Variable Rent record
1061: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (+)');
1062: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1063: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1064: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1065: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1066: pnp_debug_pkg.debug ('p_create_flag '||p_create_flag);
1067:
1068: -- Get dates/info, GL calendar used from Variable Rent record
1069:
1062: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1063: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1064: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1065: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1066: pnp_debug_pkg.debug ('p_create_flag '||p_create_flag);
1067:
1068: -- Get dates/info, GL calendar used from Variable Rent record
1069:
1070: SELECT vr.commencement_date,
1213: l_period_id := period_rec.period_id;
1214: l_end_date := period_rec.end_date;
1215: l_status := period_rec.status;
1216: l_period_exists := 'Y';
1217: pnp_debug_pkg.debug(' period exists ...'||l_periodId);
1218: pnp_debug_pkg.debug('');
1219: END LOOP;
1220:
1221: IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
1214: l_end_date := period_rec.end_date;
1215: l_status := period_rec.status;
1216: l_period_exists := 'Y';
1217: pnp_debug_pkg.debug(' period exists ...'||l_periodId);
1218: pnp_debug_pkg.debug('');
1219: END LOOP;
1220:
1221: IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
1222: --call to insert into PN_VAR_PERIODS;
1220:
1221: IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
1222: --call to insert into PN_VAR_PERIODS;
1223:
1224: pnp_debug_pkg.debug(' period exists ...N');
1225: PN_VAR_RENT_PKG.INSERT_PERIODS_ROW
1226: (X_ROWID => l_rowId,
1227: X_PERIOD_ID => l_periodId,
1228: X_PERIOD_NUM => l_periodNum,
1256:
1257: l_rowId := NULL;
1258: l_periodId := NULL;
1259: l_periodNum := NULL;
1260: pnp_debug_pkg.debug('period inserted is ...'||l_periodId);
1261:
1262: ELSE /* period exists in the database */
1263:
1264: pnp_debug_pkg.debug('New Period Start Date:'||p_per_start_date);
1260: pnp_debug_pkg.debug('period inserted is ...'||l_periodId);
1261:
1262: ELSE /* period exists in the database */
1263:
1264: pnp_debug_pkg.debug('New Period Start Date:'||p_per_start_date);
1265: pnp_debug_pkg.debug('New Period End Date:'||p_per_end_date);
1266: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
1267:
1268: /* Check if the period in the database is a partial period */
1261:
1262: ELSE /* period exists in the database */
1263:
1264: pnp_debug_pkg.debug('New Period Start Date:'||p_per_start_date);
1265: pnp_debug_pkg.debug('New Period End Date:'||p_per_end_date);
1266: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
1267:
1268: /* Check if the period in the database is a partial period */
1269: IF p_per_end_date = l_end_date THEN
1262: ELSE /* period exists in the database */
1263:
1264: pnp_debug_pkg.debug('New Period Start Date:'||p_per_start_date);
1265: pnp_debug_pkg.debug('New Period End Date:'||p_per_end_date);
1266: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
1267:
1268: /* Check if the period in the database is a partial period */
1269: IF p_per_end_date = l_end_date THEN
1270:
1267:
1268: /* Check if the period in the database is a partial period */
1269: IF p_per_end_date = l_end_date THEN
1270:
1271: pnp_debug_pkg.debug('period end date is equsal to period in data base ..');
1272:
1273: /* Make the period as active if it is inactive */
1274: IF l_status = pn_var_rent_pkg.status THEN
1275:
1272:
1273: /* Make the period as active if it is inactive */
1274: IF l_status = pn_var_rent_pkg.status THEN
1275:
1276: pnp_debug_pkg.debug('period is inactive ..'||l_period_id);
1277: UPDATE pn_var_periods_all
1278: SET status = NULL
1279: WHERE period_id = l_period_id;
1280:
1372:
1373: IF l_group_exists = 'N' THEN
1374:
1375: /* group does not exist in the database and needs to be added */
1376: pnp_debug_pkg.debug ('l_group_exists = N');
1377: ------------------------------------------------------------
1378: --call to insert into PN_VAR_GRP_DATES;
1379: ------------------------------------------------------------
1380: PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
1400: X_LAST_UPDATED_BY => p_created_by,
1401: X_LAST_UPDATE_LOGIN => p_created_by
1402: );
1403:
1404: pnp_debug_pkg.debug ('group dte id of group added ....'||l_grpDateId);
1405: l_rowId := NULL;
1406: l_grpDateId := NULL;
1407:
1408: ELSE
1405: l_rowId := NULL;
1406: l_grpDateId := NULL;
1407:
1408: ELSE
1409: pnp_debug_pkg.debug ('l_group_exists = Y');
1410: /* Check if the group in the database is a partial or if the new group is partial */
1411: IF p_grp_end_date <> l_grp_end_date THEN
1412:
1413: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1409: pnp_debug_pkg.debug ('l_group_exists = Y');
1410: /* Check if the group in the database is a partial or if the new group is partial */
1411: IF p_grp_end_date <> l_grp_end_date THEN
1412:
1413: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1414: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1415: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1416: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1417: /* Updte the end date and proration factor for the group */
1410: /* Check if the group in the database is a partial or if the new group is partial */
1411: IF p_grp_end_date <> l_grp_end_date THEN
1412:
1413: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1414: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1415: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1416: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1417: /* Updte the end date and proration factor for the group */
1418: UPDATE pn_var_grp_dates_all
1411: IF p_grp_end_date <> l_grp_end_date THEN
1412:
1413: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1414: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1415: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1416: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1417: /* Updte the end date and proration factor for the group */
1418: UPDATE pn_var_grp_dates_all
1419: SET grp_end_date = p_grp_end_date,
1412:
1413: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1414: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1415: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1416: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1417: /* Updte the end date and proration factor for the group */
1418: UPDATE pn_var_grp_dates_all
1419: SET grp_end_date = p_grp_end_date,
1420: proration_Factor = round(p_proration_factor,10),
1478:
1479: IF l_group_exists = 'N' THEN
1480:
1481: /* group does not exist in the database and needs to be added */
1482: pnp_debug_pkg.debug ('l_group_exists = N');
1483:
1484: ------------------------------------------------------------
1485: --call to insert into PN_VAR_GRP_DATES;
1486: ------------------------------------------------------------
1507: X_LAST_UPDATED_BY => p_created_by,
1508: X_LAST_UPDATE_LOGIN => p_created_by
1509: );
1510:
1511: pnp_debug_pkg.debug ('group dte id of group added ....'||l_grpDateId);
1512: l_rowId := NULL;
1513: l_grpDateId := NULL;
1514:
1515: ELSE
1512: l_rowId := NULL;
1513: l_grpDateId := NULL;
1514:
1515: ELSE
1516: pnp_debug_pkg.debug ('l_group_exists = Y');
1517: /* Check if the group in the database is a partial or if the new group is partial */
1518: IF p_grp_end_date <> l_grp_end_date THEN
1519:
1520: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1516: pnp_debug_pkg.debug ('l_group_exists = Y');
1517: /* Check if the group in the database is a partial or if the new group is partial */
1518: IF p_grp_end_date <> l_grp_end_date THEN
1519:
1520: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1521: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1522: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1523: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1524: /* Updte the end date and proration factor for the group */
1517: /* Check if the group in the database is a partial or if the new group is partial */
1518: IF p_grp_end_date <> l_grp_end_date THEN
1519:
1520: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1521: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1522: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1523: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1524: /* Updte the end date and proration factor for the group */
1525: UPDATE pn_var_grp_dates_all
1518: IF p_grp_end_date <> l_grp_end_date THEN
1519:
1520: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1521: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1522: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1523: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1524: /* Updte the end date and proration factor for the group */
1525: UPDATE pn_var_grp_dates_all
1526: SET grp_end_date = p_grp_end_date,
1519:
1520: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1521: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1522: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1523: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1524: /* Updte the end date and proration factor for the group */
1525: UPDATE pn_var_grp_dates_all
1526: SET grp_end_date = p_grp_end_date,
1527: proration_Factor = round(p_proration_factor,10),
1586:
1587: IF l_group_exists = 'N' THEN
1588:
1589: /* group does not exist in the database and needs to be added */
1590: pnp_debug_pkg.debug ('l_group_exists = N');
1591:
1592: ------------------------------------------------------------
1593: --call to insert into PN_VAR_GRP_DATES;
1594: ------------------------------------------------------------
1616: X_LAST_UPDATED_BY => p_created_by,
1617: X_LAST_UPDATE_LOGIN => p_created_by
1618: );
1619:
1620: pnp_debug_pkg.debug ('group dte id of group added ....'||l_grpDateId);
1621: l_rowId := NULL;
1622: l_grpDateId := NULL;
1623:
1624: ELSE
1621: l_rowId := NULL;
1622: l_grpDateId := NULL;
1623:
1624: ELSE
1625: pnp_debug_pkg.debug ('l_group_exists = Y');
1626: /* Check if the group in the database is a partial or if the new group is partial */
1627: IF p_grp_end_date <> l_grp_end_date THEN
1628:
1629: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1625: pnp_debug_pkg.debug ('l_group_exists = Y');
1626: /* Check if the group in the database is a partial or if the new group is partial */
1627: IF p_grp_end_date <> l_grp_end_date THEN
1628:
1629: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1630: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1631: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1632: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1633: /* Updte the end date and proration factor for the group */
1626: /* Check if the group in the database is a partial or if the new group is partial */
1627: IF p_grp_end_date <> l_grp_end_date THEN
1628:
1629: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1630: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1631: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1632: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1633: /* Updte the end date and proration factor for the group */
1634: UPDATE pn_var_grp_dates_all
1627: IF p_grp_end_date <> l_grp_end_date THEN
1628:
1629: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1630: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1631: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1632: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1633: /* Updte the end date and proration factor for the group */
1634: UPDATE pn_var_grp_dates_all
1635: SET grp_end_date = p_grp_end_date,
1628:
1629: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1630: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1631: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1632: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1633: /* Updte the end date and proration factor for the group */
1634: UPDATE pn_var_grp_dates_all
1635: SET grp_end_date = p_grp_end_date,
1636: proration_Factor = round(p_proration_factor,10),
1695:
1696: IF l_group_exists = 'N' THEN
1697:
1698: /* group does not exist in the database and needs to be added */
1699: pnp_debug_pkg.debug ('l_group_exists = N');
1700:
1701: ------------------------------------------------------------
1702: --call to insert into PN_VAR_GRP_DATES;
1703: ------------------------------------------------------------
1724: X_LAST_UPDATED_BY => p_created_by,
1725: X_LAST_UPDATE_LOGIN => p_created_by
1726: );
1727:
1728: pnp_debug_pkg.debug ('group dte id of group added ....'||l_grpDateId);
1729: l_rowId := NULL;
1730: l_grpDateId := NULL;
1731:
1732: ELSE
1729: l_rowId := NULL;
1730: l_grpDateId := NULL;
1731:
1732: ELSE
1733: pnp_debug_pkg.debug ('l_group_exists = Y');
1734: /* Check if the group in the database is a partial or if the new group is partial */
1735: IF p_grp_end_date <> l_grp_end_date THEN
1736:
1737: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1733: pnp_debug_pkg.debug ('l_group_exists = Y');
1734: /* Check if the group in the database is a partial or if the new group is partial */
1735: IF p_grp_end_date <> l_grp_end_date THEN
1736:
1737: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1738: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1739: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1740: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1741: /* Updte the end date and proration factor for the group */
1734: /* Check if the group in the database is a partial or if the new group is partial */
1735: IF p_grp_end_date <> l_grp_end_date THEN
1736:
1737: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1738: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1739: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1740: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1741: /* Updte the end date and proration factor for the group */
1742: UPDATE pn_var_grp_dates_all
1735: IF p_grp_end_date <> l_grp_end_date THEN
1736:
1737: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1738: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1739: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1740: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1741: /* Updte the end date and proration factor for the group */
1742: UPDATE pn_var_grp_dates_all
1743: SET grp_end_date = p_grp_end_date,
1736:
1737: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1738: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1739: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1740: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1741: /* Updte the end date and proration factor for the group */
1742: UPDATE pn_var_grp_dates_all
1743: SET grp_end_date = p_grp_end_date,
1744: proration_Factor = round(p_proration_factor,10),
2076: END LOOP;
2077:
2078: END LOOP;
2079:
2080: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (-)');
2081:
2082: END CREATE_VAR_RENT_PERIODS;
2083:
2084: /*=============================================================================+
2215: AND start_date = p_start_date;
2216:
2217: BEGIN
2218:
2219: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS_NOCAL (+)');
2220: put_log('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS_NOCAL (+)');
2221:
2222: SELECT VR.commencement_date,
2223: VR.termination_date,
2295: l_periodId := period_rec.period_id;
2296: l_end_date := period_rec.end_date;
2297: l_status := period_rec.status;
2298: l_period_exists := 'Y';
2299: pnp_debug_pkg.debug(' period exists ...'||l_periodId);
2300: pnp_debug_pkg.debug('');
2301: END LOOP;
2302:
2303: IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
2296: l_end_date := period_rec.end_date;
2297: l_status := period_rec.status;
2298: l_period_exists := 'Y';
2299: pnp_debug_pkg.debug(' period exists ...'||l_periodId);
2300: pnp_debug_pkg.debug('');
2301: END LOOP;
2302:
2303: IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
2304:
2307: ELSE
2308: l_partial_period := 'Y';
2309: END IF;
2310:
2311: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2312: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2313: pnp_debug_pkg.debug('partial period ...'||l_partial_period);
2314:
2315: PN_VAR_RENT_PKG.INSERT_PERIODS_ROW
2308: l_partial_period := 'Y';
2309: END IF;
2310:
2311: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2312: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2313: pnp_debug_pkg.debug('partial period ...'||l_partial_period);
2314:
2315: PN_VAR_RENT_PKG.INSERT_PERIODS_ROW
2316: ( X_ROWID => l_rowId,
2309: END IF;
2310:
2311: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2312: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2313: pnp_debug_pkg.debug('partial period ...'||l_partial_period);
2314:
2315: PN_VAR_RENT_PKG.INSERT_PERIODS_ROW
2316: ( X_ROWID => l_rowId,
2317: X_PERIOD_ID => VR_periods(i).l_period_id,
2343: X_LAST_UPDATED_BY => l_created_by,
2344: X_LAST_UPDATE_LOGIN => l_created_by
2345: );
2346:
2347: pnp_debug_pkg.debug('period inserted is ...'||VR_periods(i).l_period_id);
2348: l_rowId := NULL;
2349: l_periodNum := NULL;
2350:
2351: ELSE /* period exists in the database */
2349: l_periodNum := NULL;
2350:
2351: ELSE /* period exists in the database */
2352:
2353: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2354: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2355: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
2356:
2357: VR_periods(i).l_period_id := l_periodId;
2350:
2351: ELSE /* period exists in the database */
2352:
2353: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2354: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2355: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
2356:
2357: VR_periods(i).l_period_id := l_periodId;
2358:
2351: ELSE /* period exists in the database */
2352:
2353: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2354: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2355: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
2356:
2357: VR_periods(i).l_period_id := l_periodId;
2358:
2359: /* Check if the period in the database is a partial period */
2358:
2359: /* Check if the period in the database is a partial period */
2360: IF vr_periods(i).l_period_end_date = l_end_date THEN
2361:
2362: pnp_debug_pkg.debug('period end date is equsal to period in data base ..');
2363:
2364: /* Make the period as active if it is inactive */
2365: IF l_status = pn_var_rent_pkg.status THEN
2366:
2363:
2364: /* Make the period as active if it is inactive */
2365: IF l_status = pn_var_rent_pkg.status THEN
2366:
2367: pnp_debug_pkg.debug('period is inactive ..'||l_periodId);
2368:
2369: UPDATE pn_var_periods_all
2370: SET status = NULL
2371: WHERE period_id = l_periodId;
2373: END IF;
2374:
2375: ELSIF vr_periods(i).l_period_end_date <> l_end_date THEN
2376:
2377: pnp_debug_pkg.debug('Need to update the period end date '||l_periodId);
2378:
2379: IF (vr_periods(i).l_period_end_date
2380: - vr_periods(i).l_period_start_date) + 1 >= 365 THEN
2381:
2498: FOR j IN l_grp_counter..(VR_grp_dates.COUNT - 1) LOOP
2499:
2500: l_group_exists := 'N';
2501:
2502: pnp_debug_pkg.debug(' group start date ...'||VR_grp_dates(j).l_grp_start_date);
2503: pnp_debug_pkg.debug(' group end date ...'||VR_periods(i).l_period_end_date);
2504:
2505: EXIT WHEN VR_grp_dates(j).l_grp_start_date > VR_periods(i).l_period_end_date;
2506:
2499:
2500: l_group_exists := 'N';
2501:
2502: pnp_debug_pkg.debug(' group start date ...'||VR_grp_dates(j).l_grp_start_date);
2503: pnp_debug_pkg.debug(' group end date ...'||VR_periods(i).l_period_end_date);
2504:
2505: EXIT WHEN VR_grp_dates(j).l_grp_start_date > VR_periods(i).l_period_end_date;
2506:
2507: /* Check if the group in PL/SQL table with a specified start date
2514:
2515: IF l_group_exists = 'N' THEN
2516:
2517: /* group does not exist in the database and needs to be added */
2518: pnp_debug_pkg.debug (' Group does not exist - to insert group ');
2519:
2520: /*l_proration_Factor
2521: := ((vr_grp_dates(j).l_grp_end_date - vr_grp_dates(j).l_grp_start_date) + 1 )
2522: / ((LAST_DAY(ADD_MONTHS(LAST_DAY(vr_grp_dates(j).l_group_date),l_reptg_freq_code-1))
2567: X_LAST_UPDATED_BY => l_created_by,
2568: X_LAST_UPDATE_LOGIN => l_created_by
2569: );
2570:
2571: pnp_debug_pkg.debug ('group dte id of group added ....'||l_grpDateId);
2572: l_rowId := NULL;
2573: l_grpDateId := NULL;
2574:
2575: ELSE
2573: l_grpDateId := NULL;
2574:
2575: ELSE
2576:
2577: pnp_debug_pkg.debug (' group exists - update it ');
2578: /* Check if the group in the database is a partial or if the new group is partial */
2579:
2580: IF VR_grp_dates(j).l_grp_end_date <> l_grp_end_date THEN
2581:
2604: l_proration_Factor := 1;
2605:
2606: END IF;
2607:
2608: pnp_debug_pkg.debug ('updating end date of group '||VR_grp_dates(j).l_grp_end_date);
2609: pnp_debug_pkg.debug ('updating proration factor as '||l_proration_Factor);
2610: pnp_debug_pkg.debug ('updating due date of group '||VR_grp_dates(j).l_reptg_due_date);
2611: /* Updte the end date and proration factor for the group */
2612:
2605:
2606: END IF;
2607:
2608: pnp_debug_pkg.debug ('updating end date of group '||VR_grp_dates(j).l_grp_end_date);
2609: pnp_debug_pkg.debug ('updating proration factor as '||l_proration_Factor);
2610: pnp_debug_pkg.debug ('updating due date of group '||VR_grp_dates(j).l_reptg_due_date);
2611: /* Updte the end date and proration factor for the group */
2612:
2613: UPDATE pn_var_grp_dates_all
2606: END IF;
2607:
2608: pnp_debug_pkg.debug ('updating end date of group '||VR_grp_dates(j).l_grp_end_date);
2609: pnp_debug_pkg.debug ('updating proration factor as '||l_proration_Factor);
2610: pnp_debug_pkg.debug ('updating due date of group '||VR_grp_dates(j).l_reptg_due_date);
2611: /* Updte the end date and proration factor for the group */
2612:
2613: UPDATE pn_var_grp_dates_all
2614: SET grp_end_date = VR_grp_dates(j).l_grp_end_date,
2629: PN_VAR_RENT_PKG.delete_report_date_row(p_var_rent_id, NULL);
2630: PN_VAR_RENT_PKG.create_report_dates (p_var_rent_id);
2631:
2632: put_log('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS_NOCAL (-)');
2633: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS_NOCAL (-)');
2634:
2635: END CREATE_VAR_RENT_PERIODS_NOCAL;
2636:
2637: /*=============================================================================+
2680: l_date DATE;
2681:
2682: BEGIN
2683:
2684: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_PERIODS (+)');
2685:
2686: l_lines_exist := PN_VAR_RENT_PKG.find_if_lines_exist(p_var_rent_id, NULL, l_date);
2687:
2688: l_constr_exist:= PN_VAR_RENT_PKG.find_if_constr_exist(p_var_rent_id, l_date);
2799: IF SQL%NOTFOUND THEN
2800: NULL;
2801: END IF;
2802:
2803: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_PERIODS (-)');
2804:
2805: END DELETE_VAR_RENT_PERIODS;
2806:
2807: /*=============================================================================+
2852: p_cumulative_vol VARCHAR2(1);
2853:
2854: BEGIN
2855:
2856: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_VAR_RENT_PERIODS (+)');
2857:
2858: -- Get dates/info, GL calendar used from Variable Rent record
2859:
2860: SELECT DECODE(cal.reptg_freq_code,'MON', 1,
2946: AND grp_start_date <= p_term_date
2947: AND grp_end_date >= p_term_date
2948: AND rownum < 2;
2949:
2950: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_VAR_RENT_PERIODS (-)');
2951:
2952: END UPDATE_VAR_RENT_PERIODS;
2953:
2954: /*===========================================================================+
2995: AND end_date > l_date);
2996:
2997: BEGIN
2998:
2999: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_CONSTR (+)');
3000:
3001: FOR constr_rec IN c1 LOOP
3002:
3003: PN_VAR_CONSTRAINTS_PKG.DELETE_ROW(constr_rec.constraint_id);
3010: END IF;
3011:
3012: END LOOP;
3013:
3014: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_CONSTR (-)');
3015:
3016: END DELETE_VAR_RENT_CONSTR;
3017:
3018: /*===========================================================================+
3067: AND end_date > l_date);
3068:
3069: BEGIN
3070:
3071: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_LINES (+)');
3072:
3073: FOR lines_rec IN c1 LOOP
3074:
3075: l_bkptshd_exist := PN_VAR_RENT_PKG.find_if_bkptshd_exist(lines_rec.line_item_id);
3111: END IF;
3112:
3113: END LOOP;
3114:
3115: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_LINES (-)');
3116:
3117: END DELETE_VAR_RENT_LINES;
3118:
3119: /*===========================================================================+
3152: WHERE line_item_id = p_line_item_id;
3153:
3154: BEGIN
3155:
3156: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_DEDUCT (+)');
3157:
3158: FOR deduct_rec IN c1 LOOP
3159:
3160: PN_VAR_DEDUCTIONS_PKG.DELETE_ROW(deduct_rec.deduction_id);
3167: END IF;
3168:
3169: END LOOP;
3170:
3171: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_DEDUCT (-)');
3172:
3173: END DELETE_VAR_RENT_DEDUCT;
3174:
3175: /*===========================================================================+
3210: WHERE line_item_id = p_line_item_id;
3211:
3212: BEGIN
3213:
3214: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_VOL_HIST (+)');
3215:
3216: FOR vol_hist_rec IN c1 LOOP
3217:
3218: PN_VAR_VOL_HIST_PKG.DELETE_ROW(vol_hist_rec.vol_hist_id);
3225: END IF;
3226:
3227: END LOOP;
3228:
3229: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_VOL_HIST (-)');
3230:
3231: END DELETE_VAR_VOL_HIST;
3232:
3233: /*===========================================================================+
3270: WHERE line_item_id = p_line_item_id;
3271:
3272: BEGIN
3273:
3274: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_HEAD (+)');
3275:
3276: FOR head_rec IN c2 LOOP
3277:
3278: l_bkptsdet_exist := PN_VAR_RENT_PKG.find_if_bkptsdet_exist(head_rec.bkpt_header_id);
3288: PN_VAR_BKPTS_HEAD_PKG.DELETE_ROW(head_rec.bkpt_header_id);
3289:
3290: END LOOP;
3291:
3292: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_HEAD (-)');
3293:
3294: END DELETE_VAR_BKPTS_HEAD;
3295:
3296: /*===========================================================================+
3331: WHERE bkpt_header_id = p_bkpt_header_id;
3332:
3333: BEGIN
3334:
3335: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_DET (+)');
3336:
3337: FOR det_rec IN c1 LOOP
3338:
3339: PN_VAR_BKPTS_DET_PKG.DELETE_ROW(det_rec.bkpt_detail_id);
3339: PN_VAR_BKPTS_DET_PKG.DELETE_ROW(det_rec.bkpt_detail_id);
3340:
3341: END LOOP;
3342:
3343: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_DET (-)');
3344:
3345: END DELETE_VAR_BKPTS_DET;
3346:
3347: /*===========================================================================+
3374: l_period_exists NUMBER;
3375:
3376: BEGIN
3377:
3378: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_PERIOD_EXISTS (+)');
3379:
3380: SELECT 1
3381: INTO l_period_exists
3382: FROM dual
3385: WHERE periods.var_rent_id = p_var_rent_id);
3386:
3387: RETURN l_period_exists;
3388:
3389: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_PERIOD_EXISTS (-)');
3390:
3391: EXCEPTION
3392:
3393: WHEN OTHERS THEN
3426: l_calculation_exists NUMBER;
3427:
3428: BEGIN
3429:
3430: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CALCULATION_EXISTS (+)');
3431:
3432: SELECT 1
3433: INTO l_calculation_exists
3434: FROM dual
3437: WHERE inv.var_rent_id = p_var_rent_id);
3438:
3439: RETURN l_calculation_exists;
3440:
3441: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CALCULATION_EXISTS (-)');
3442:
3443: EXCEPTION
3444:
3445: WHEN OTHERS THEN
3477: l_invoice_exists NUMBER;
3478:
3479: BEGIN
3480:
3481: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_INVOICE_EXISTS (+)');
3482:
3483: SELECT 1
3484: INTO l_invoice_exists
3485: FROM dual
3488: WHERE inv.var_rent_id = p_var_rent_id);
3489:
3490: RETURN l_invoice_exists;
3491:
3492: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_INVOICE_EXISTS (-)');
3493:
3494: EXCEPTION
3495:
3496: WHEN OTHERS THEN
3544:
3545:
3546: BEGIN
3547:
3548: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_INVOICES (+)');
3549:
3550: l_date := NVL(p_term_date,(TO_DATE('01/01/1776','mm/dd/yyyy')));
3551:
3552: l_lines_exist := PN_VAR_RENT_PKG.find_if_lines_exist(p_var_rent_id, NULL, l_date);
3641: END IF;
3642:
3643: END LOOP;
3644:
3645: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_INVOICES (-)');
3646:
3647: END DELETE_VAR_RENT_INVOICES;
3648:
3649: /*===========================================================================+
3676: l_vrdates_exists NUMBER;
3677:
3678: BEGIN
3679:
3680: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VRDATES_EXISTS (+)');
3681:
3682: SELECT 1
3683: INTO l_vrdates_exists
3684: FROM dual
3687: WHERE dates.var_rent_id = p_var_rent_id);
3688:
3689: RETURN l_vrdates_exists;
3690:
3691: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VRDATES_EXISTS (-)');
3692:
3693: EXCEPTION
3694:
3695: WHEN OTHERS THEN
3733: l_date DATE;
3734:
3735: BEGIN
3736:
3737: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_LINES_EXIST (+)');
3738:
3739: l_date := nvl(p_term_date,(to_date('01/01/1776','mm/dd/yyyy')));
3740:
3741: SELECT 1
3752: );
3753:
3754: RETURN l_lines_exist;
3755:
3756: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_LINES_EXIST (-)');
3757:
3758: EXCEPTION
3759:
3760: WHEN OTHERS THEN
3792: l_volhist_exist NUMBER;
3793:
3794: BEGIN
3795:
3796: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VOLHIST_EXIST (+)');
3797:
3798: SELECT 1
3799: INTO l_volhist_exist
3800: FROM dual
3804: );
3805:
3806: RETURN l_volhist_exist;
3807:
3808: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VOLHIST_EXIST (-)');
3809:
3810: EXCEPTION
3811:
3812: WHEN OTHERS THEN
3848: l_volhist_approved_exist VARCHAR2(1);
3849:
3850: BEGIN
3851:
3852: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VOLHIST_APPROVED_EXIST (+)');
3853:
3854: SELECT 'Y'
3855: INTO l_volhist_approved_exist
3856: FROM dual
3862: );
3863:
3864: RETURN l_volhist_approved_exist;
3865:
3866: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VOLHIST_APPROVED_EXIST (-)');
3867:
3868: EXCEPTION
3869:
3870: WHEN OTHERS THEN
3967: END IF;
3968:
3969: RETURN l_volhist_bkpts_exist;
3970:
3971: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTS_EXIST (+)');
3972:
3973: END find_if_volhist_bkpts_exist;
3974:
3975: /*===========================================================================+
4007: l_date DATE;
4008:
4009: BEGIN
4010:
4011: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CONSTR_EXIST (+)');
4012:
4013: l_date := NVL(p_term_date,(TO_DATE('01/01/1776','mm/dd/yyyy')));
4014:
4015: SELECT 1
4025: );
4026:
4027: RETURN l_constr_exist;
4028:
4029: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CONSTR_EXIST (-)');
4030:
4031: EXCEPTION
4032:
4033: WHEN OTHERS THEN
4068: l_abat_exist NUMBER;
4069:
4070: BEGIN
4071:
4072: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_ABAT_DEF_EXIST (+)');
4073:
4074: SELECT 1
4075: INTO l_abat_exist
4076: FROM dual
4080: );
4081:
4082: RETURN l_abat_exist;
4083:
4084: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_ABAT_DEF_EXIST (-)');
4085:
4086: EXCEPTION
4087:
4088: WHEN OTHERS THEN
4124: l_constr_exist NUMBER;
4125:
4126: BEGIN
4127:
4128: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CONSTR_DEF_EXIST (+)');
4129:
4130: SELECT 1
4131: INTO l_constr_exist
4132: FROM dual
4136: );
4137:
4138: RETURN l_constr_exist;
4139:
4140: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CONSTR_DEF_EXIST (-)');
4141:
4142: EXCEPTION
4143:
4144: WHEN OTHERS THEN
4176: l_deduct_exist NUMBER;
4177:
4178: BEGIN
4179:
4180: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_DEDUCT_EXIST (+)');
4181:
4182: SELECT 1
4183: INTO l_deduct_exist
4184: FROM dual
4188: );
4189:
4190: RETURN l_deduct_exist;
4191:
4192: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_DEDUCT_EXIST (-)');
4193:
4194: EXCEPTION
4195:
4196: WHEN OTHERS THEN
4228: PROCEDURE LOCK_ROW_EXCEPTION (p_column_name IN VARCHAR2,
4229: p_new_value IN VARCHAR2)
4230: IS
4231: BEGIN
4232: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION (+)');
4233:
4234: fnd_message.set_name ('PN','PN_RECORD_CHANGED');
4235: fnd_message.set_token ('COLUMN_NAME',p_column_name);
4236: fnd_message.set_token ('NEW_VALUE',p_new_value);
4235: fnd_message.set_token ('COLUMN_NAME',p_column_name);
4236: fnd_message.set_token ('NEW_VALUE',p_new_value);
4237: app_exception.raise_exception;
4238:
4239: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION (-)');
4240: END lock_row_exception;
4241:
4242: /*===========================================================================+
4243: | FUNCTION
4300: l_reporting_periods NUMBER;
4301:
4302: BEGIN
4303:
4304: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_REPORTING_PERIODS (+)');
4305:
4306: SELECT count(GRP_START_DATE)
4307: INTO l_reporting_periods
4308: FROM pn_var_grp_dates_all
4309: WHERE period_id = p_period_id;
4310:
4311: RETURN l_reporting_periods;
4312:
4313: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_REPORTING_PERIODS (-)');
4314:
4315: EXCEPTION
4316:
4317: WHEN OTHERS THEN
4348: l_reporting_periods NUMBER;
4349:
4350: BEGIN
4351:
4352: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_REPORTING_PERIODS (+)');
4353:
4354: IF p_freq_code = 'MON' THEN
4355:
4356: RETURN 12;
4368: RETURN 1;
4369:
4370: END IF;
4371:
4372: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_REPORTING_PERIODS (-)');
4373:
4374: RETURN l_reporting_periods;
4375:
4376: EXCEPTION
4419: l_base_rent NUMBER;
4420:
4421: BEGIN
4422:
4423: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CALCULATE_BASE_RENT (+)');
4424:
4425: IF (p_base_rent_type = 'ROLLING') THEN
4426:
4427: SELECT SUM(item.ACTUAL_AMOUNT)
4471: AND term.currency_code = var.currency_code;
4472:
4473: END IF;
4474:
4475: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CALCULATE_BASE_RENT (-)');
4476:
4477: RETURN l_base_rent;
4478:
4479: EXCEPTION
4524: l_grp_date_info GRP_DATE_INFO_REC ;
4525:
4526: BEGIN
4527:
4528: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.GET_GRP_DATE_INFO (+)');
4529:
4530: SELECT grp_date_id,
4531: grp_start_date,
4532: grp_end_date,
4546: AND grp_end_date >= p_start_date
4547: AND grp_end_date >= p_end_date
4548: AND rownum = 1;
4549:
4550: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.GET_GRP_DATE_INFO (-)');
4551:
4552: RETURN l_grp_date_info;
4553:
4554: EXCEPTION
4594: l_proration_factor PRORATION_FACTOR_REC ;
4595:
4596: BEGIN
4597:
4598: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.GET_PRORATION_FACTOR (+)');
4599:
4600: SELECT
4601: (p1.end_date-p1.start_date)+1 first_period_pro_days,
4602: p1.proration_factor first_period_gl_days,
4625: AND g2.grp_date_id = (SELECT max(grp_date_id)
4626: FROM pn_var_grp_dates
4627: WHERE var_rent_id = p_var_rent_id);
4628:
4629: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.GET_PRORATION_FACTOR (-)');
4630:
4631: RETURN l_proration_factor;
4632:
4633: EXCEPTION
4669: l_bkptshd_exists NUMBER;
4670:
4671: BEGIN
4672:
4673: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTSHD_EXIST (+)');
4674:
4675: SELECT 1
4676: INTO l_bkptshd_exists
4677: FROM dual
4678: WHERE EXISTS ( SELECT head.bkpt_header_id
4679: FROM pn_var_bkpts_head_ALL head
4680: WHERE head.line_item_id = p_line_item_id);
4681:
4682: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTSHD_EXIST (-)');
4683:
4684: RETURN l_bkptshd_exists;
4685:
4686: EXCEPTION
4720: l_bkptsdet_exist NUMBER;
4721:
4722: BEGIN
4723:
4724: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTSDET_EXIST (+)');
4725:
4726: SELECT 1
4727: INTO l_bkptsdet_exist
4728: FROM dual
4729: WHERE EXISTS ( SELECT det.bkpt_detail_id
4730: FROM pn_var_bkpts_det_ALL det
4731: WHERE det.bkpt_header_id = p_bkpt_header_id);
4732:
4733: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTSDET_EXIST (-)');
4734:
4735: RETURN l_bkptsdet_exist;
4736:
4737: EXCEPTION
4788: l_exported NUMBER;
4789:
4790: BEGIN
4791:
4792: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_EXPORTED (+)');
4793:
4794: IF p_block IN('SUMMARY_FDR_BLK','VARENT_DATES_BLK') THEN
4795:
4796: SELECT 1
4889:
4890:
4891: END IF;
4892:
4893: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_EXPORTED (-)');
4894:
4895: RETURN l_exported;
4896:
4897: EXCEPTION
4932: l_exported NUMBER;
4933:
4934: BEGIN
4935: NULL;
4936: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_EXPORTED (+)');
4937: SELECT 1
4938: INTO l_exported
4939: FROM dual
4940: WHERE EXISTS ( SELECT vol_hist_id
4942: WHERE vol_hist_id = p_id
4943: AND forecasted_exp_code = 'Y'
4944: );
4945:
4946: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_EXPORTED (-)');
4947:
4948: RETURN l_exported;
4949:
4950: EXCEPTION
4990: l_status VARCHAR2(30);
4991:
4992: BEGIN
4993:
4994: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_STATUS (+)');
4995:
4996: SELECT 'COMPLETE'
4997: INTO l_status
4998: FROM dual
5012: (actual_exp_code = 'N' AND FORECASTED_exp_code ='Y' AND variance_exp_code = 'Y')
5013: )
5014: );
5015:
5016: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_STATUS (-)');
5017:
5018: RETURN l_status;
5019:
5020: EXCEPTION
5075: l_adjust_hist_exists NUMBER := NULL;
5076:
5077: BEGIN
5078:
5079: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_ADJUST_HIST_EXISTS (+)');
5080:
5081: SELECT 1
5082: INTO l_adjust_hist_exists
5083: FROM dual
5086: WHERE inv1.period_id = p_period_id
5087: AND (NVL(inv1.adjust_num,0) <> 0 OR
5088: NVL(inv1.true_up_amt,0) <> 0 ));
5089:
5090: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_ADJUST_HIST_EXISTS (-)');
5091:
5092: RETURN l_adjust_hist_exists;
5093:
5094: EXCEPTION
5142: l_term_exISts VARCHAR2(1) := 'N';
5143:
5144: BEGIN
5145:
5146: pnp_debug_pkg.debug( 'pn_var_rent_pkg.approved_term_exist (+): ');
5147:
5148: FOR chk_term_cur_rec IN chk_term_cur LOOP
5149: l_term_exists := chk_term_cur_rec.term_exists;
5150: END LOOP;
5148: FOR chk_term_cur_rec IN chk_term_cur LOOP
5149: l_term_exists := chk_term_cur_rec.term_exists;
5150: END LOOP;
5151:
5152: pnp_debug_pkg.debug( 'pn_var_rent_pkg.approved_term_exist (-): ');
5153:
5154: RETURN l_term_exists;
5155:
5156: END approved_term_exist;
5178: l_bkptsdet_exISt NUMBER := NULL;
5179:
5180: BEGIN
5181:
5182: pnp_debug_pkg.debug( 'pn_var_rent_pkg.delet_INv_summ(+): ');
5183:
5184: OPEN c_exist;
5185: LOOP
5186:
5236: IF SQL%NOTFOUND THEN
5237: NULL;
5238: END IF;
5239:
5240: pnp_debug_pkg.debug( 'pn_var_rent_pkg.delet_INv_summ(-): ');
5241:
5242: END delete_inv_summ;
5243:
5244: /*===========================================================================+
5274: l_vol_ready_for_adjust NUMBER := NULL;
5275:
5276: BEGIN
5277:
5278: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_VOL_READY_FOR_ADJUST (+)');
5279:
5280: IF p_invoice_on = 'ACTUAL' THEN
5281:
5282: SELECT 1
5306: AND grp.grp_date_id = vh.grp_date_id));
5307:
5308: END IF;
5309:
5310: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_VOL_READY_FOR_ADJUST (-)');
5311:
5312: RETURN l_vol_ready_for_adjust;
5313:
5314: EXCEPTION
5364: TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5365: l_payment_term_id number_tbl_type;
5366:
5367: BEGIN
5368: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_LOCATION_FOR_VR_TERMS (+)');
5369:
5370: OPEN C_UPD_TERMS;
5371: LOOP
5372: FETCH C_UPD_TERMS
5380: EXIT WHEN C_UPD_TERMS%NOTFOUND;
5381:
5382: END LOOP;
5383:
5384: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_LOCATION_FOR_VR_TERMS (-)');
5385:
5386: -- initialize api return status to success
5387: p_return_status := FND_API.G_RET_STS_SUCCESS;
5388:
6336: -- to be created
6337:
6338: FOR var_rent_dates_rec in var_rent_dates_cur
6339: LOOP
6340: pnp_debug_pkg.debug('inside var_rent_dates_rec loop');
6341: generate_cal_periods_tbl(p_var_rent_dates_rec => var_rent_dates_rec ,
6342: p_start_date => p_start_date,
6343: p_end_date => p_end_date,
6344: p_extension_end_date => p_extension_end_date,
6405: l_partial_period := 'Y';
6406: ELSE
6407: l_partial_period := 'N';
6408: END IF;
6409: /*pnp_debug_pkg.debug(' v_new_periods_tbl(i).start_date:'||v_new_periods_tbl(i).start_date);
6410: pnp_debug_pkg.debug(' v_new_periods_tbl(i).end_date:'||v_new_periods_tbl(i).end_date);
6411: pnp_debug_pkg.debug(' l_partial_period:'||l_partial_period);*/
6412: --Srini End 30-JUL-2004
6413:
6406: ELSE
6407: l_partial_period := 'N';
6408: END IF;
6409: /*pnp_debug_pkg.debug(' v_new_periods_tbl(i).start_date:'||v_new_periods_tbl(i).start_date);
6410: pnp_debug_pkg.debug(' v_new_periods_tbl(i).end_date:'||v_new_periods_tbl(i).end_date);
6411: pnp_debug_pkg.debug(' l_partial_period:'||l_partial_period);*/
6412: --Srini End 30-JUL-2004
6413:
6414: UPDATE pn_var_periods_all
6407: l_partial_period := 'N';
6408: END IF;
6409: /*pnp_debug_pkg.debug(' v_new_periods_tbl(i).start_date:'||v_new_periods_tbl(i).start_date);
6410: pnp_debug_pkg.debug(' v_new_periods_tbl(i).end_date:'||v_new_periods_tbl(i).end_date);
6411: pnp_debug_pkg.debug(' l_partial_period:'||l_partial_period);*/
6412: --Srini End 30-JUL-2004
6413:
6414: UPDATE pn_var_periods_all
6415: SET end_date = v_new_periods_tbl(i).end_date,
6443: WHEN OTHERS THEN
6444: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
6445: fnd_message.set_token('ERR_MSG',sqlerrm);
6446: x_return_status := FND_API.G_RET_STS_ERROR;
6447: pnp_debug_pkg.put_log_msg (fnd_message.get);
6448: --PNP_DEBUG_PKG.disable_file_debug;
6449:
6450: END extend_periods ;
6451:
6444: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
6445: fnd_message.set_token('ERR_MSG',sqlerrm);
6446: x_return_status := FND_API.G_RET_STS_ERROR;
6447: pnp_debug_pkg.put_log_msg (fnd_message.get);
6448: --PNP_DEBUG_PKG.disable_file_debug;
6449:
6450: END extend_periods ;
6451:
6452: /*===========================================================================+
6955: END IF;
6956:
6957: EXCEPTION
6958: WHEN OTHERS THEN
6959: pnp_debug_pkg.debug('error ' || sqlerrm);
6960: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
6961: fnd_message.set_token('ERR_MSG',sqlerrm);
6962: x_return_status := FND_API.G_RET_STS_ERROR;
6963: pnp_debug_pkg.debug (fnd_message.get);
6959: pnp_debug_pkg.debug('error ' || sqlerrm);
6960: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
6961: fnd_message.set_token('ERR_MSG',sqlerrm);
6962: x_return_status := FND_API.G_RET_STS_ERROR;
6963: pnp_debug_pkg.debug (fnd_message.get);
6964: pnp_debug_pkg.put_log_msg (fnd_message.get);
6965: --PNP_DEBUG_PKG.disable_file_debug;
6966: END extend_group_dates;
6967:
6960: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
6961: fnd_message.set_token('ERR_MSG',sqlerrm);
6962: x_return_status := FND_API.G_RET_STS_ERROR;
6963: pnp_debug_pkg.debug (fnd_message.get);
6964: pnp_debug_pkg.put_log_msg (fnd_message.get);
6965: --PNP_DEBUG_PKG.disable_file_debug;
6966: END extend_group_dates;
6967:
6968: /*===========================================================================+
6961: fnd_message.set_token('ERR_MSG',sqlerrm);
6962: x_return_status := FND_API.G_RET_STS_ERROR;
6963: pnp_debug_pkg.debug (fnd_message.get);
6964: pnp_debug_pkg.put_log_msg (fnd_message.get);
6965: --PNP_DEBUG_PKG.disable_file_debug;
6966: END extend_group_dates;
6967:
6968: /*===========================================================================+
6969: | PROCEDURE
7052: END LOOP;
7053: put_log ('PN_VAR_RENT_PKG.create_new_bkpts (-)');
7054: EXCEPTION
7055: WHEN OTHERS THEN
7056: pnp_debug_pkg.debug('error ' || sqlerrm);
7057: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
7058: fnd_message.set_token('ERR_MSG',sqlerrm);
7059: x_return_status := FND_API.G_RET_STS_ERROR;
7060: pnp_debug_pkg.debug (fnd_message.get);
7056: pnp_debug_pkg.debug('error ' || sqlerrm);
7057: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
7058: fnd_message.set_token('ERR_MSG',sqlerrm);
7059: x_return_status := FND_API.G_RET_STS_ERROR;
7060: pnp_debug_pkg.debug (fnd_message.get);
7061: pnp_debug_pkg.put_log_msg (fnd_message.get);
7062: END create_new_bkpts;
7063:
7064:
7057: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
7058: fnd_message.set_token('ERR_MSG',sqlerrm);
7059: x_return_status := FND_API.G_RET_STS_ERROR;
7060: pnp_debug_pkg.debug (fnd_message.get);
7061: pnp_debug_pkg.put_log_msg (fnd_message.get);
7062: END create_new_bkpts;
7063:
7064:
7065: FUNCTION GET_PRORATION_RULE(p_var_rent_id IN NUMBER DEFAULT NULL,
7134: l_index NUMBER :=0;
7135: l_start_date DATE ;
7136: l_end_date DATE ;
7137: BEGIN
7138: pnp_debug_pkg.debug('generate_cal periodd(+)');
7139: pnp_debug_pkg.debug('use gl calendar' || p_var_rent_dates_rec.use_gl_calendar);
7140: IF NVL(p_var_rent_dates_rec.use_gl_calendar,'N') = 'Y' THEN
7141: FOR cal_periods_rec in cal_periods_cur(
7142: p_var_rent_dates_rec.gl_period_set_name,
7135: l_start_date DATE ;
7136: l_end_date DATE ;
7137: BEGIN
7138: pnp_debug_pkg.debug('generate_cal periodd(+)');
7139: pnp_debug_pkg.debug('use gl calendar' || p_var_rent_dates_rec.use_gl_calendar);
7140: IF NVL(p_var_rent_dates_rec.use_gl_calendar,'N') = 'Y' THEN
7141: FOR cal_periods_rec in cal_periods_cur(
7142: p_var_rent_dates_rec.gl_period_set_name,
7143: p_var_rent_dates_rec.period_type,
7150: x_cal_periods_tbl(l_index).end_date := cal_periods_rec.end_date ;
7151: END LOOP;
7152: ELSE
7153: l_start_date := p_start_date;
7154: pnp_debug_pkg.debug ('l_end_date' || l_end_date);
7155: pnp_debug_pkg.debug ('l_start_date' || l_start_date);
7156: l_end_date := ADD_MONTHS(p_var_rent_dates_rec.year_start_date , 12) - 1;
7157: WHILE l_start_date < nvl(p_extension_end_date,p_end_date)
7158: LOOP
7151: END LOOP;
7152: ELSE
7153: l_start_date := p_start_date;
7154: pnp_debug_pkg.debug ('l_end_date' || l_end_date);
7155: pnp_debug_pkg.debug ('l_start_date' || l_start_date);
7156: l_end_date := ADD_MONTHS(p_var_rent_dates_rec.year_start_date , 12) - 1;
7157: WHILE l_start_date < nvl(p_extension_end_date,p_end_date)
7158: LOOP
7159: l_index := l_index + 1;
7164: x_cal_periods_tbl(l_index).end_date := l_end_date;
7165: END IF;
7166: l_start_date := l_end_date + 1;
7167: l_end_date := ADD_MONTHS(l_start_date , 12) - 1;
7168: pnp_debug_pkg.debug ('loop l_end_date' || l_end_date);
7169: END LOOP;
7170: END IF;
7171: -- end if of use_gl_calendar
7172: END generate_cal_periods_tbl;
7267: x_group_dates_tbl(l_index).start_date := group_date_rec.start_date;
7268: x_group_dates_tbl(l_index).end_date := group_date_rec.end_date;
7269: END LOOP;
7270: ELSIF p_pn_var_rent_dates_rec.reptg_freq_code = 'QTR' THEN
7271: pnp_debug_pkg.debug('reporting = quarter');
7272: FOR group_date_rec in group_date_qtr(p_period_start_date,
7273: p_period_end_date)
7274: LOOP
7275: l_index := l_index + 1;
7482:
7483: l_dummy VARCHAR2(1) ;
7484: BEGIN
7485:
7486: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7487: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7488: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7489: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7490: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7483: l_dummy VARCHAR2(1) ;
7484: BEGIN
7485:
7486: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7487: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7488: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7489: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7490: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7491: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7484: BEGIN
7485:
7486: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7487: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7488: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7489: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7490: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7491: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7492:
7485:
7486: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7487: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7488: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7489: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7490: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7491: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7492:
7493: x_return_status := FALSE;
7486: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7487: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7488: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7489: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7490: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7491: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7492:
7493: x_return_status := FALSE;
7494: FOR header_rec in header_cur LOOP
7487: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7488: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7489: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7490: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7491: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7492:
7493: x_return_status := FALSE;
7494: FOR header_rec in header_cur LOOP
7495:
7493: x_return_status := FALSE;
7494: FOR header_rec in header_cur LOOP
7495:
7496:
7497: pnp_debug_pkg.debug(' header_rec.bkhd_end_date = '|| to_char(header_rec.bkhd_end_date,'MM/DD/RRRR'));
7498: IF header_rec.bkhd_end_date = p_end_date THEN
7499: pnp_debug_pkg.debug('HEADER = TRUE');
7500: x_return_status := TRUE;
7501: ELSE
7495:
7496:
7497: pnp_debug_pkg.debug(' header_rec.bkhd_end_date = '|| to_char(header_rec.bkhd_end_date,'MM/DD/RRRR'));
7498: IF header_rec.bkhd_end_date = p_end_date THEN
7499: pnp_debug_pkg.debug('HEADER = TRUE');
7500: x_return_status := TRUE;
7501: ELSE
7502: l_dummy := NULL;
7503: OPEN exist_next_header_date ( header_rec.bkhd_end_date) ;
7511: END IF;
7512: END IF;
7513:
7514: FOR detail_rec in detail_cur (header_rec.bkhd_default_id) LOOP
7515: pnp_debug_pkg.debug(' detail_rec.bkdt_end_date = '|| to_char(detail_rec.bkdt_end_date,'MM/DD/RRRR'));
7516: IF detail_rec.bkdt_end_date = header_rec.bkhd_end_date THEN
7517: pnp_debug_pkg.debug('DETAIL = TRUE');
7518: x_return_status := TRUE;
7519: ELSE
7513:
7514: FOR detail_rec in detail_cur (header_rec.bkhd_default_id) LOOP
7515: pnp_debug_pkg.debug(' detail_rec.bkdt_end_date = '|| to_char(detail_rec.bkdt_end_date,'MM/DD/RRRR'));
7516: IF detail_rec.bkdt_end_date = header_rec.bkhd_end_date THEN
7517: pnp_debug_pkg.debug('DETAIL = TRUE');
7518: x_return_status := TRUE;
7519: ELSE
7520: l_dummy := NULL;
7521: OPEN exist_next_detail_date ( header_rec.bkhd_default_id,
7589:
7590: l_dummy VARCHAR2(1) ;
7591: BEGIN
7592:
7593: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7594: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7595: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7596: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7597: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7590: l_dummy VARCHAR2(1) ;
7591: BEGIN
7592:
7593: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7594: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7595: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7596: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7597: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7598: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7591: BEGIN
7592:
7593: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7594: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7595: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7596: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7597: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7598: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7599:
7592:
7593: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7594: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7595: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7596: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7597: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7598: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7599:
7600: x_return_status := FALSE;
7593: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7594: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7595: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7596: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7597: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7598: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7599:
7600: x_return_status := FALSE;
7601: FOR header_rec in header_cur LOOP
7594: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7595: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7596: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7597: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7598: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7599:
7600: x_return_status := FALSE;
7601: FOR header_rec in header_cur LOOP
7602:
7600: x_return_status := FALSE;
7601: FOR header_rec in header_cur LOOP
7602:
7603:
7604: pnp_debug_pkg.debug(' header_rec.bkhd_end_date = '|| to_char(header_rec.bkhd_end_date,'MM/DD/RRRR'));
7605: IF header_rec.bkhd_end_date = p_end_date THEN
7606: pnp_debug_pkg.debug('HEADER = TRUE');
7607: x_return_status := TRUE;
7608: ELSE
7602:
7603:
7604: pnp_debug_pkg.debug(' header_rec.bkhd_end_date = '|| to_char(header_rec.bkhd_end_date,'MM/DD/RRRR'));
7605: IF header_rec.bkhd_end_date = p_end_date THEN
7606: pnp_debug_pkg.debug('HEADER = TRUE');
7607: x_return_status := TRUE;
7608: ELSE
7609: l_dummy := NULL;
7610: OPEN exist_next_header_date ( header_rec.bkhd_end_date) ;
7618: END IF;
7619: END IF;
7620:
7621: FOR detail_rec in detail_cur (header_rec.bkpt_header_id) LOOP
7622: pnp_debug_pkg.debug(' detail_rec.bkpt_end_date = '|| to_char(detail_rec.bkpt_end_date,'MM/DD/RRRR'));
7623: IF detail_rec.bkpt_end_Date = header_rec.bkhd_end_date THEN
7624: pnp_debug_pkg.debug('DETAIL = TRUE');
7625: x_return_status := TRUE;
7626: ELSE
7620:
7621: FOR detail_rec in detail_cur (header_rec.bkpt_header_id) LOOP
7622: pnp_debug_pkg.debug(' detail_rec.bkpt_end_date = '|| to_char(detail_rec.bkpt_end_date,'MM/DD/RRRR'));
7623: IF detail_rec.bkpt_end_Date = header_rec.bkhd_end_date THEN
7624: pnp_debug_pkg.debug('DETAIL = TRUE');
7625: x_return_status := TRUE;
7626: ELSE
7627: l_dummy := NULL;
7628: OPEN exist_next_detail_date ( header_rec.bkpt_header_id,
7682: l_bkpts_exists NUMBER;
7683:
7684: BEGIN
7685:
7686: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTS_SETUP_EXISTS (+)');
7687:
7688: /*SELECT 1
7689: INTO l_bkpts_exists
7690: FROM dual
7705: WHEN OTHERS
7706: THEN
7707: RETURN FALSE;
7708:
7709: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTS_SETUP_EXISTS (-)');
7710:
7711: END FIND_IF_BKPTS_SETUP_EXISTS;
7712:
7713: Procedure put_log(p_string VARCHAR2)
7713: Procedure put_log(p_string VARCHAR2)
7714: IS
7715: BEGIN
7716:
7717: pnp_debug_pkg.debug(p_string);
7718: pnp_debug_pkg.put_log_msg(p_string);
7719:
7720: END;
7721: -- M2M End
7714: IS
7715: BEGIN
7716:
7717: pnp_debug_pkg.debug(p_string);
7718: pnp_debug_pkg.put_log_msg(p_string);
7719:
7720: END;
7721: -- M2M End
7722:
7731: OPEN is_partial_period_cur;
7732: FETCH is_partial_period_cur INTO l_return;
7733: IF is_partial_period_cur%NOTFOUND THEN
7734: CLOSE is_partial_period_cur;
7735: pnp_debug_pkg.debug('Cursor is_partial_period_cur NO_DATA_FOUND');
7736: RAISE NO_DATA_FOUND;
7737: END IF;
7738: CLOSE is_partial_period_cur;
7739: RETURN l_return;
7776: l_freq_code PN_VAR_RENT_DATES_ALL.REPTG_FREQ_CODE%TYPE;
7777:
7778: BEGIN
7779:
7780: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DETERMINE_FREQUENCY (+)');
7781:
7782: SELECT X_VAR_RENT_END_DATE - X_VAR_RENT_START_DATE + 1
7783: INTO l_days
7784: FROM dual;
7801: WHEN OTHERS
7802: THEN
7803: RETURN NULL;
7804:
7805: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DETERMINE_FREQUENCY (-)');
7806:
7807: END DETERMINE_FREQUENCY;
7808:
7809: PROCEDURE update_bkpt_details(p_var_rent_id IN NUMBER,
7858: PROCEDURE delete_vr_setup ( p_var_rent_id IN NUMBER
7859: ,p_new_termn_date IN DATE)
7860: IS
7861: BEGIN
7862: pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_vr_setup (+)');
7863:
7864: /* Delete the data from main tables */
7865: DELETE pn_var_vol_hist_all
7866: WHERE start_date > p_new_termn_date
7922: DELETE pn_var_abat_defaults_all
7923: WHERE start_date > p_new_termn_date
7924: AND var_rent_id = p_var_rent_id;
7925:
7926: pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_vr_setup (-)');
7927:
7928: END delete_vr_setup;
7929:
7930: -------------------------------------------------------------------------------
8074: l_variance_exp_code VARCHAR2(1) := 'N';
8075: l_var_term_status VARCHAR2(1);
8076:
8077: BEGIN
8078: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (+)');
8079: pnp_debug_pkg.debug (' Parameters :');
8080: pnp_debug_pkg.debug (' ------------------------------------------- :');
8081: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8082: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8075: l_var_term_status VARCHAR2(1);
8076:
8077: BEGIN
8078: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (+)');
8079: pnp_debug_pkg.debug (' Parameters :');
8080: pnp_debug_pkg.debug (' ------------------------------------------- :');
8081: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8082: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8083: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8076:
8077: BEGIN
8078: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (+)');
8079: pnp_debug_pkg.debug (' Parameters :');
8080: pnp_debug_pkg.debug (' ------------------------------------------- :');
8081: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8082: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8083: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8084: pnp_debug_pkg.debug (' ------------------------------------------- :');
8077: BEGIN
8078: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (+)');
8079: pnp_debug_pkg.debug (' Parameters :');
8080: pnp_debug_pkg.debug (' ------------------------------------------- :');
8081: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8082: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8083: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8084: pnp_debug_pkg.debug (' ------------------------------------------- :');
8085:
8078: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (+)');
8079: pnp_debug_pkg.debug (' Parameters :');
8080: pnp_debug_pkg.debug (' ------------------------------------------- :');
8081: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8082: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8083: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8084: pnp_debug_pkg.debug (' ------------------------------------------- :');
8085:
8086: /* Delete draft terms for invoices for all periods which lie after the new termination date */
8079: pnp_debug_pkg.debug (' Parameters :');
8080: pnp_debug_pkg.debug (' ------------------------------------------- :');
8081: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8082: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8083: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8084: pnp_debug_pkg.debug (' ------------------------------------------- :');
8085:
8086: /* Delete draft terms for invoices for all periods which lie after the new termination date */
8087: DELETE pn_payment_terms_all
8080: pnp_debug_pkg.debug (' ------------------------------------------- :');
8081: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8082: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8083: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8084: pnp_debug_pkg.debug (' ------------------------------------------- :');
8085:
8086: /* Delete draft terms for invoices for all periods which lie after the new termination date */
8087: DELETE pn_payment_terms_all
8088: WHERE status = 'DRAFT'
8105: FROM pn_var_periods_all
8106: WHERE var_rent_id = p_var_rent_id
8107: AND start_date > p_new_termn_date);
8108:
8109: pnp_debug_pkg.debug ('Loop through the period which start after new termination date');
8110: /* Loop through the period which start after new termination date, starting from last and keep on deleting till you get a period which has an approved term associated with it */
8111: FOR period_rec IN period_cur LOOP
8112:
8113: pnp_debug_pkg.debug ('period id = '||period_rec.period_id);
8109: pnp_debug_pkg.debug ('Loop through the period which start after new termination date');
8110: /* Loop through the period which start after new termination date, starting from last and keep on deleting till you get a period which has an approved term associated with it */
8111: FOR period_rec IN period_cur LOOP
8112:
8113: pnp_debug_pkg.debug ('period id = '||period_rec.period_id);
8114: l_invoice_exists := 'N';
8115:
8116: /* Check if the period has an invoice associated with it */
8117: FOR invoice_rec IN invoice_cur (period_rec.period_id) LOOP
8114: l_invoice_exists := 'N';
8115:
8116: /* Check if the period has an invoice associated with it */
8117: FOR invoice_rec IN invoice_cur (period_rec.period_id) LOOP
8118: pnp_debug_pkg.debug ('invoice exists ...');
8119: l_invoice_exists := 'Y';
8120: END LOOP;
8121:
8122: IF l_invoice_exists = 'Y' THEN
8120: END LOOP;
8121:
8122: IF l_invoice_exists = 'Y' THEN
8123: /* If invoice exists in this period then exit from the loop as you need not delete any more periods */
8124: pnp_debug_pkg.debug ('exiting ...');
8125: EXIT;
8126: ELSE
8127:
8128: pnp_debug_pkg.debug ('deleting group date and periods ...');
8124: pnp_debug_pkg.debug ('exiting ...');
8125: EXIT;
8126: ELSE
8127:
8128: pnp_debug_pkg.debug ('deleting group date and periods ...');
8129: /* Delete group dates and periods for which there exists no invoice */
8130: DELETE pn_var_grp_dates_all
8131: WHERE period_id = period_rec.period_id;
8132:
8135: END IF;
8136:
8137: END LOOP;
8138:
8139: pnp_debug_pkg.debug ('Loop through the invoices which are not deleted to create negative terms for each of the approved term ...');
8140: /* Loop through the invoices which are not deleted to create negative terms for each of the approved term */
8141: FOR invoice_date_rec IN invoice_date_cur LOOP
8142:
8143: FOR max_adjust_num_rec IN max_adjust_num_cur(invoice_date_rec.period_id, invoice_date_rec.invoice_date)
8170: ELSE
8171: l_var_term_status := rent_inv_rec.variance_term_status;
8172: END IF;
8173:
8174: pnp_debug_pkg.debug ('Actual_invoiced_amount ...'||l_actual_invoiced_amount);
8175: pnp_debug_pkg.debug ('var rent inv id ...'||rent_inv_rec.var_rent_inv_id);
8176:
8177: /* Set var rent inv id to null before inserting a row */
8178: l_var_rent_inv_id := NULL;
8171: l_var_term_status := rent_inv_rec.variance_term_status;
8172: END IF;
8173:
8174: pnp_debug_pkg.debug ('Actual_invoiced_amount ...'||l_actual_invoiced_amount);
8175: pnp_debug_pkg.debug ('var rent inv id ...'||rent_inv_rec.var_rent_inv_id);
8176:
8177: /* Set var rent inv id to null before inserting a row */
8178: l_var_rent_inv_id := NULL;
8179: l_rowid := NULL;
8227: x_true_up_status => rent_inv_rec.true_up_status,
8228: x_true_up_exp_code => rent_inv_rec.true_up_exp_code,
8229: x_org_id => rent_inv_rec.org_id);
8230:
8231: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
8232: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||rent_inv_rec.var_rent_inv_id);
8233:
8234: /*
8235: FOR payment_rec IN payment_cur(invoice_date_rec.invoice_date) LOOP
8228: x_true_up_exp_code => rent_inv_rec.true_up_exp_code,
8229: x_org_id => rent_inv_rec.org_id);
8230:
8231: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
8232: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||rent_inv_rec.var_rent_inv_id);
8233:
8234: /*
8235: FOR payment_rec IN payment_cur(invoice_date_rec.invoice_date) LOOP
8236: pn_variable_term_pkg.create_reversal_terms(p_payment_term_id => payment_rec.payment_term_id
8250: SET status = pn_var_rent_pkg.status
8251: WHERE var_rent_id = p_var_rent_id
8252: AND start_date > p_new_termn_date;
8253:
8254: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (-)');
8255:
8256: EXCEPTION
8257: WHEN OTHERS THEN
8258: pnp_debug_pkg.debug (' Remove Later Periods Error:'||SQLERRM);
8254: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (-)');
8255:
8256: EXCEPTION
8257: WHEN OTHERS THEN
8258: pnp_debug_pkg.debug (' Remove Later Periods Error:'||SQLERRM);
8259: x_return_status := FND_API.G_RET_STS_ERROR;
8260: x_return_message := SQLERRM;
8261:
8262: END remove_later_periods ;
8368: l_inv_sch_date DATE := NULL;
8369: l_period_id NUMBER := 0;
8370:
8371: BEGIN
8372: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_setup (+)');
8373: pnp_debug_pkg.debug ('p_var_rent_id ....'||p_var_rent_id);
8374: pnp_debug_pkg.debug ('p_period_id ......'||p_period_id);
8375: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8376:
8369: l_period_id NUMBER := 0;
8370:
8371: BEGIN
8372: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_setup (+)');
8373: pnp_debug_pkg.debug ('p_var_rent_id ....'||p_var_rent_id);
8374: pnp_debug_pkg.debug ('p_period_id ......'||p_period_id);
8375: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8376:
8377: FOR rec IN vol_his_cur LOOP
8370:
8371: BEGIN
8372: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_setup (+)');
8373: pnp_debug_pkg.debug ('p_var_rent_id ....'||p_var_rent_id);
8374: pnp_debug_pkg.debug ('p_period_id ......'||p_period_id);
8375: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8376:
8377: FOR rec IN vol_his_cur LOOP
8378: PN_VAR_VOL_HIST_PKG.MODIFY_ROW( X_VOL_HIST_ID => rec.vol_hist_id,
8371: BEGIN
8372: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_setup (+)');
8373: pnp_debug_pkg.debug ('p_var_rent_id ....'||p_var_rent_id);
8374: pnp_debug_pkg.debug ('p_period_id ......'||p_period_id);
8375: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8376:
8377: FOR rec IN vol_his_cur LOOP
8378: PN_VAR_VOL_HIST_PKG.MODIFY_ROW( X_VOL_HIST_ID => rec.vol_hist_id,
8379: X_LINE_ITEM_ID => rec.line_item_id,
8516: Partial_period = 'Y'
8517: WHERE var_rent_id = p_var_rent_id
8518: AND period_id = p_period_id;
8519:
8520: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_setup (-)');
8521: END;
8522:
8523: -------------------------------------------------------------------------------
8524: -- NAME : early_terminate_period
8629: l_variance_exp_code VARCHAR2(1) := 'N';
8630: l_var_term_status VARCHAR2(1);
8631:
8632: BEGIN
8633: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (+)');
8634: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8635: pnp_debug_pkg.debug ('p_period_id ....'||p_period_id);
8636: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8637: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8630: l_var_term_status VARCHAR2(1);
8631:
8632: BEGIN
8633: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (+)');
8634: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8635: pnp_debug_pkg.debug ('p_period_id ....'||p_period_id);
8636: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8637: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8638:
8631:
8632: BEGIN
8633: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (+)');
8634: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8635: pnp_debug_pkg.debug ('p_period_id ....'||p_period_id);
8636: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8637: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8638:
8639:
8632: BEGIN
8633: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (+)');
8634: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8635: pnp_debug_pkg.debug ('p_period_id ....'||p_period_id);
8636: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8637: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8638:
8639:
8640: /* Delete draft terms for invoices for this period */
8633: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (+)');
8634: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8635: pnp_debug_pkg.debug ('p_period_id ....'||p_period_id);
8636: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8637: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8638:
8639:
8640: /* Delete draft terms for invoices for this period */
8641: DELETE pn_payment_terms_all
8650: AND actual_exp_code = 'N'
8651: AND forecasted_exp_code = 'N'
8652: AND variance_exp_code = 'N';
8653:
8654: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8655:
8656: pnp_debug_pkg.debug ('Loop for invoices that start after new termination date for this period and create adjustment terms for them');
8657: /* Loop for invoices that start after new termination date for this period and create adjustment terms for them */
8658: FOR invoice_date_rec IN invoice_date_cur LOOP
8652: AND variance_exp_code = 'N';
8653:
8654: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8655:
8656: pnp_debug_pkg.debug ('Loop for invoices that start after new termination date for this period and create adjustment terms for them');
8657: /* Loop for invoices that start after new termination date for this period and create adjustment terms for them */
8658: FOR invoice_date_rec IN invoice_date_cur LOOP
8659:
8660: FOR max_adjust_num_rec IN max_adjust_num_cur(p_period_id, invoice_date_rec.invoice_date)
8687: ELSE
8688: l_var_term_status := invoice_rec.variance_term_status;
8689: END IF;
8690:
8691: pnp_debug_pkg.debug ('Actual_invoiced_amount ...'||l_actual_invoiced_amount);
8692: pnp_debug_pkg.debug ('var rent inv id ...'||invoice_rec.var_rent_inv_id);
8693: /* Set var rent inv id to null before inserting a row */
8694: l_var_rent_inv_id := NULL;
8695: l_rowid := NULL;
8688: l_var_term_status := invoice_rec.variance_term_status;
8689: END IF;
8690:
8691: pnp_debug_pkg.debug ('Actual_invoiced_amount ...'||l_actual_invoiced_amount);
8692: pnp_debug_pkg.debug ('var rent inv id ...'||invoice_rec.var_rent_inv_id);
8693: /* Set var rent inv id to null before inserting a row */
8694: l_var_rent_inv_id := NULL;
8695: l_rowid := NULL;
8696:
8742: x_true_up_status => invoice_rec.true_up_status,
8743: x_true_up_exp_code => invoice_rec.true_up_exp_code,
8744: x_org_id => invoice_rec.org_id);
8745:
8746: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
8747: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||invoice_rec.var_rent_inv_id);
8748: /*
8749:
8750: FOR payment_rec IN payment_cur(invoice_date_rec.invoice_date) LOOP
8743: x_true_up_exp_code => invoice_rec.true_up_exp_code,
8744: x_org_id => invoice_rec.org_id);
8745:
8746: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
8747: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||invoice_rec.var_rent_inv_id);
8748: /*
8749:
8750: FOR payment_rec IN payment_cur(invoice_date_rec.invoice_date) LOOP
8751: pn_variable_term_pkg.create_reversal_terms(p_payment_term_id => payment_rec.payment_term_id
8759: END IF;
8760:
8761: END LOOP;
8762:
8763: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (-)');
8764:
8765: END early_terminate_period;
8766:
8767:
8767:
8768: PROCEDURE delete_var_agreement ( p_var_rent_id IN NUMBER)
8769: IS
8770: BEGIN
8771: pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_var_agreement (+)');
8772:
8773: /* Delete the data from main tables */
8774: DELETE pn_var_vol_hist_all
8775: WHERE period_id IN (SELECT period_id
8832:
8833: DELETE pn_var_rents_all
8834: WHERE var_rent_id = p_var_rent_id;
8835:
8836: pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_var_agreement (-)');
8837:
8838: END delete_var_agreement;
8839:
8840: -------------------------------------------------------------------------------
8926: WHERE var_rent_id = p_var_rent_id
8927: AND inv_end_date <= p_new_termn_date;
8928:
8929: BEGIN
8930: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (+)');
8931: pnp_debug_pkg.debug (' Parameters :');
8932: pnp_debug_pkg.debug (' ------------------------------------------- :');
8933: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8934: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8927: AND inv_end_date <= p_new_termn_date;
8928:
8929: BEGIN
8930: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (+)');
8931: pnp_debug_pkg.debug (' Parameters :');
8932: pnp_debug_pkg.debug (' ------------------------------------------- :');
8933: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8934: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8935: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8928:
8929: BEGIN
8930: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (+)');
8931: pnp_debug_pkg.debug (' Parameters :');
8932: pnp_debug_pkg.debug (' ------------------------------------------- :');
8933: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8934: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8935: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8936: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8929: BEGIN
8930: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (+)');
8931: pnp_debug_pkg.debug (' Parameters :');
8932: pnp_debug_pkg.debug (' ------------------------------------------- :');
8933: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8934: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8935: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8936: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8937: pnp_debug_pkg.debug (' ------------------------------------------- :');
8930: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (+)');
8931: pnp_debug_pkg.debug (' Parameters :');
8932: pnp_debug_pkg.debug (' ------------------------------------------- :');
8933: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8934: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8935: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8936: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8937: pnp_debug_pkg.debug (' ------------------------------------------- :');
8938:
8931: pnp_debug_pkg.debug (' Parameters :');
8932: pnp_debug_pkg.debug (' ------------------------------------------- :');
8933: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8934: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8935: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8936: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8937: pnp_debug_pkg.debug (' ------------------------------------------- :');
8938:
8939: /* Loop through the rent agreements whose termination date > new termination date */
8932: pnp_debug_pkg.debug (' ------------------------------------------- :');
8933: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8934: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8935: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8936: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8937: pnp_debug_pkg.debug (' ------------------------------------------- :');
8938:
8939: /* Loop through the rent agreements whose termination date > new termination date */
8940: FOR var_rent_rec IN var_rent_cur LOOP
8933: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8934: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8935: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8936: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8937: pnp_debug_pkg.debug (' ------------------------------------------- :');
8938:
8939: /* Loop through the rent agreements whose termination date > new termination date */
8940: FOR var_rent_rec IN var_rent_cur LOOP
8941:
8938:
8939: /* Loop through the rent agreements whose termination date > new termination date */
8940: FOR var_rent_rec IN var_rent_cur LOOP
8941:
8942: pnp_debug_pkg.debug ('**************************************************************');
8943: pnp_debug_pkg.debug ('looping through variable rent ...'||var_rent_rec.var_rent_id);
8944: pnp_debug_pkg.debug ('**************************************************************');
8945:
8946: l_period_exists := 0;
8939: /* Loop through the rent agreements whose termination date > new termination date */
8940: FOR var_rent_rec IN var_rent_cur LOOP
8941:
8942: pnp_debug_pkg.debug ('**************************************************************');
8943: pnp_debug_pkg.debug ('looping through variable rent ...'||var_rent_rec.var_rent_id);
8944: pnp_debug_pkg.debug ('**************************************************************');
8945:
8946: l_period_exists := 0;
8947:
8940: FOR var_rent_rec IN var_rent_cur LOOP
8941:
8942: pnp_debug_pkg.debug ('**************************************************************');
8943: pnp_debug_pkg.debug ('looping through variable rent ...'||var_rent_rec.var_rent_id);
8944: pnp_debug_pkg.debug ('**************************************************************');
8945:
8946: l_period_exists := 0;
8947:
8948: /* Delete Vol hist, Bkpt Details, bkpt headers, lines, deductions, vr summary rows,
8956: p_old_termn_date => p_old_termn_date,
8957: x_return_status => x_return_status,
8958: x_return_message => x_return_message);
8959:
8960: pnp_debug_pkg.debug ('after remove later periods'||p_var_rent_id||'....'||p_new_termn_date);
8961: /* Fetch the period in which new termination date lies */
8962: FOR period_id_rec IN period_id_cur(var_rent_rec.var_rent_id) LOOP
8963:
8964: pnp_debug_pkg.debug ('current period with new termination date ...'||period_id_rec.period_id);
8960: pnp_debug_pkg.debug ('after remove later periods'||p_var_rent_id||'....'||p_new_termn_date);
8961: /* Fetch the period in which new termination date lies */
8962: FOR period_id_rec IN period_id_cur(var_rent_rec.var_rent_id) LOOP
8963:
8964: pnp_debug_pkg.debug ('current period with new termination date ...'||period_id_rec.period_id);
8965: pnp_debug_pkg.debug ('end date for period with new termination date ...'||period_id_rec.end_date);
8966:
8967: /* Check if the new period is partial. Early terminate the setup and process the
8968: period if the new period is partial */
8961: /* Fetch the period in which new termination date lies */
8962: FOR period_id_rec IN period_id_cur(var_rent_rec.var_rent_id) LOOP
8963:
8964: pnp_debug_pkg.debug ('current period with new termination date ...'||period_id_rec.period_id);
8965: pnp_debug_pkg.debug ('end date for period with new termination date ...'||period_id_rec.end_date);
8966:
8967: /* Check if the new period is partial. Early terminate the setup and process the
8968: period if the new period is partial */
8969: IF period_id_rec.end_date <> p_new_termn_date THEN
8984: x_return_message => x_return_message);
8985:
8986: ELSE
8987:
8988: pnp_debug_pkg.debug(' contract the defaults only ...');
8989:
8990: FOR bkdt_defaults_rec IN bkdt_defaults_cur(var_rent_rec.var_rent_id) LOOP
8991: pn_var_bkdt_defaults_pkg.modify_row (x_bkdt_default_id => bkdt_defaults_rec.bkdt_default_id,
8992: x_bkhd_default_id => bkdt_defaults_rec.bkhd_default_id,
9031: END IF;
9032:
9033: END LOOP;
9034:
9035: pnp_debug_pkg.debug ('after for loop');
9036:
9037: /* update the end date of agreement to new termination date */
9038: UPDATE pn_var_rents_all
9039: SET termination_date = p_new_termn_date
9048:
9049: END LOOP;
9050:
9051: /* Loop through the rent agreements whose commencement date > new termination date */
9052: pnp_debug_pkg.debug ( 'lease id is ...'||p_lease_id);
9053:
9054: IF p_lease_id IS NOT NULL THEN
9055: pnp_debug_pkg.debug(' inside if ..');
9056: FOR variable_rent_rec IN variable_rent_cur LOOP
9051: /* Loop through the rent agreements whose commencement date > new termination date */
9052: pnp_debug_pkg.debug ( 'lease id is ...'||p_lease_id);
9053:
9054: IF p_lease_id IS NOT NULL THEN
9055: pnp_debug_pkg.debug(' inside if ..');
9056: FOR variable_rent_rec IN variable_rent_cur LOOP
9057:
9058: l_appr_term_exists := 'N';
9059: pnp_debug_pkg.debug(' var rent id ...'||variable_rent_rec.var_rent_id);
9055: pnp_debug_pkg.debug(' inside if ..');
9056: FOR variable_rent_rec IN variable_rent_cur LOOP
9057:
9058: l_appr_term_exists := 'N';
9059: pnp_debug_pkg.debug(' var rent id ...'||variable_rent_rec.var_rent_id);
9060:
9061: FOR rec IN approved_term_exists_cur(variable_rent_rec.var_rent_id) LOOP
9062: pnp_debug_pkg.debug (' approved term exists ...');
9063: l_appr_term_exists := rec.approve_term_exits;
9058: l_appr_term_exists := 'N';
9059: pnp_debug_pkg.debug(' var rent id ...'||variable_rent_rec.var_rent_id);
9060:
9061: FOR rec IN approved_term_exists_cur(variable_rent_rec.var_rent_id) LOOP
9062: pnp_debug_pkg.debug (' approved term exists ...');
9063: l_appr_term_exists := rec.approve_term_exits;
9064: END LOOP;
9065:
9066: pnp_debug_pkg.debug ('l_appr_term_exists ...'||l_appr_term_exists);
9062: pnp_debug_pkg.debug (' approved term exists ...');
9063: l_appr_term_exists := rec.approve_term_exits;
9064: END LOOP;
9065:
9066: pnp_debug_pkg.debug ('l_appr_term_exists ...'||l_appr_term_exists);
9067:
9068: IF l_appr_term_exists = 'N' THEN
9069: delete_var_agreement(p_var_rent_id => variable_rent_rec.var_rent_id);
9070: END IF;
9071:
9072: END LOOP;
9073: END IF;
9074:
9075: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (-)');
9076:
9077: EXCEPTION
9078: WHEN OTHERS THEN
9079: pnp_debug_pkg.debug ('Process_vr_early_term Error:'||SQLERRM);
9075: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (-)');
9076:
9077: EXCEPTION
9078: WHEN OTHERS THEN
9079: pnp_debug_pkg.debug ('Process_vr_early_term Error:'||SQLERRM);
9080: x_return_status := FND_API.G_RET_STS_ERROR;
9081: x_return_message := sqlerrm;
9082: END;
9083:
9151: l_ret_code VARCHAR2(250);
9152: l_bkpt_exists VARCHAR2(1);
9153:
9154: BEGIN
9155: pnp_debug_pkg.debug ('pn_var_rent_pkg.extend_defaults (+)');
9156: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9157: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9158: pnp_debug_pkg.debug ('p_new_termn_date ..'||p_new_termn_date);
9159:
9152: l_bkpt_exists VARCHAR2(1);
9153:
9154: BEGIN
9155: pnp_debug_pkg.debug ('pn_var_rent_pkg.extend_defaults (+)');
9156: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9157: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9158: pnp_debug_pkg.debug ('p_new_termn_date ..'||p_new_termn_date);
9159:
9160: UPDATE pn_var_line_defaults_all
9153:
9154: BEGIN
9155: pnp_debug_pkg.debug ('pn_var_rent_pkg.extend_defaults (+)');
9156: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9157: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9158: pnp_debug_pkg.debug ('p_new_termn_date ..'||p_new_termn_date);
9159:
9160: UPDATE pn_var_line_defaults_all
9161: SET line_end_date = p_new_termn_date
9154: BEGIN
9155: pnp_debug_pkg.debug ('pn_var_rent_pkg.extend_defaults (+)');
9156: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9157: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9158: pnp_debug_pkg.debug ('p_new_termn_date ..'||p_new_termn_date);
9159:
9160: UPDATE pn_var_line_defaults_all
9161: SET line_end_date = p_new_termn_date
9162: WHERE var_rent_id = p_var_rent_id
9180: WHERE var_rent_id = p_var_rent_id
9181: AND constr_end_date = p_old_termn_date;
9182:
9183: /* This generates line items, breakpoint header and details in main tables */
9184: pnp_debug_pkg.debug ('calling procedure pn_var_defaults_pkg.create_default_lines ..');
9185: FOR rec IN bkpt_data_exists LOOP
9186: l_bkpt_exists := rec.bkpt_exists;
9187: END LOOP;
9188:
9203: pn_var_defaults_pkg.create_setup_data (x_var_rent_id => p_var_rent_id);
9204: END IF;
9205:
9206: /* This generates constraint records in main tables */
9207: pnp_debug_pkg.debug ('calling procedure pn_var_defaults_pkg.create_default_constraints ..');
9208: FOR rec IN constr_data_exists LOOP
9209: DELETE FROM pn_var_constraints_all
9210: WHERE period_id IN (SELECT period_id
9211: FROM pn_var_periods_all
9213:
9214: pn_var_defaults_pkg.create_default_constraints (x_var_rent_id => p_var_rent_id);
9215: END LOOP;
9216:
9217: pnp_debug_pkg.debug ('pn_var_rent_pkg.extend_defaults (-)');
9218: END;
9219:
9220: -------------------------------------------------------------------------------
9221: -- NAME : Create_setup_exp
9391: l_constrnum NUMBER := NULL;
9392:
9393: BEGIN
9394:
9395: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_setup_exp (+)');
9396: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9397: pnp_debug_pkg.debug ('p_period_id ..'||p_period_id);
9398:
9399: /* Fetch the end date of period which was the last one before expansion */
9392:
9393: BEGIN
9394:
9395: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_setup_exp (+)');
9396: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9397: pnp_debug_pkg.debug ('p_period_id ..'||p_period_id);
9398:
9399: /* Fetch the end date of period which was the last one before expansion */
9400: FOR last_period_rec IN last_period_cur LOOP
9393: BEGIN
9394:
9395: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_setup_exp (+)');
9396: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9397: pnp_debug_pkg.debug ('p_period_id ..'||p_period_id);
9398:
9399: /* Fetch the end date of period which was the last one before expansion */
9400: FOR last_period_rec IN last_period_cur LOOP
9401: l_end_date := last_period_rec.end_date;
9403:
9404: /* Loop through line records of period which was the last one before expansion */
9405: FOR line_rec IN line_cur LOOP
9406:
9407: pnp_debug_pkg.debug ('refernce line record is ..'||line_rec.line_item_id);
9408:
9409: /* Loop through the periods which have been created due to VR expansion to
9410: create setup data for them */
9411: FOR period_rec IN period_cur LOOP
9409: /* Loop through the periods which have been created due to VR expansion to
9410: create setup data for them */
9411: FOR period_rec IN period_cur LOOP
9412:
9413: pnp_debug_pkg.debug ('period for which data is being inserted is ..'||period_rec.period_id);
9414: l_rowid := NULL;
9415: l_line_item_id := NULL;
9416: l_line_item_num := NULL;
9417:
9449: x_agreement_template_id => line_rec.agreement_template_id,
9450: x_line_default_id => line_rec.line_default_id,
9451: x_var_rent_id => p_var_rent_id);
9452:
9453: pnp_debug_pkg.debug ('line item inserted is l_line_item_id ..'||l_line_item_id);
9454: /* Generate the breakpoint header associated with this line item */
9455: FOR bkpt_head_rec IN bkpt_head_cur (line_rec.line_item_id, l_end_date) LOOP
9456: l_rowid := NULL;
9457: l_bkpt_header_id := NULL;
9490: x_last_update_date => sysdate,
9491: x_last_updated_by => NVL(fnd_profile.value('USER_ID'),0),
9492: x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0));
9493:
9494: pnp_debug_pkg.debug ('breakpoint header inserted is l_bkpt_header_id ..'||l_bkpt_header_id);
9495:
9496: /* Generate the breakpoint details associated with this breakpoint header */
9497: FOR bkpt_detail_rec IN bkpt_detail_cur( bkpt_head_rec.bkpt_header_id, l_end_date) LOOP
9498: l_rowid := NULL;
9537: x_last_update_login => nvl(fnd_profile.value('user_id'),0),
9538: x_annual_basis_amount => bkpt_detail_rec.annual_basis_amount
9539: );
9540:
9541: pnp_debug_pkg.debug ('breakpoint detail inserted is l_bkpt_detail_id ..'||l_bkpt_detail_id);
9542:
9543: END LOOP; /* end loop for bkpt detail */
9544:
9545: END LOOP; /* end loop for bkpt header */
9596: END LOOP;
9597:
9598: END LOOP;
9599:
9600: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_setup_exp (-)');
9601: END;
9602:
9603: -------------------------------------------------------------------------------
9604: -- NAME : update_setup_exp
9621: WHERE var_rent_id = p_var_rent_id
9622: AND period_id = p_period_id;
9623:
9624: BEGIN
9625: pnp_debug_pkg.debug ('pn_var_rent_pkg.update_setup_exp (+)');
9626: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9627: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9628:
9629: FOR period_rec IN period_cur LOOP
9622: AND period_id = p_period_id;
9623:
9624: BEGIN
9625: pnp_debug_pkg.debug ('pn_var_rent_pkg.update_setup_exp (+)');
9626: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9627: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9628:
9629: FOR period_rec IN period_cur LOOP
9630:
9623:
9624: BEGIN
9625: pnp_debug_pkg.debug ('pn_var_rent_pkg.update_setup_exp (+)');
9626: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9627: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9628:
9629: FOR period_rec IN period_cur LOOP
9630:
9631: pnp_debug_pkg.debug ('last_period_rec.period_id ...'||p_period_id);
9627: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9628:
9629: FOR period_rec IN period_cur LOOP
9630:
9631: pnp_debug_pkg.debug ('last_period_rec.period_id ...'||p_period_id);
9632: pnp_debug_pkg.debug ('last_period_rec.end_date ....'||period_rec.end_date);
9633:
9634: UPDATE pn_var_bkpts_head_all
9635: SET bkhd_end_date = period_rec.end_date
9628:
9629: FOR period_rec IN period_cur LOOP
9630:
9631: pnp_debug_pkg.debug ('last_period_rec.period_id ...'||p_period_id);
9632: pnp_debug_pkg.debug ('last_period_rec.end_date ....'||period_rec.end_date);
9633:
9634: UPDATE pn_var_bkpts_head_all
9635: SET bkhd_end_date = period_rec.end_date
9636: WHERE var_rent_id = p_var_rent_id
9650: , p_period_id => p_period_id);
9651:
9652: END LOOP;
9653:
9654: pnp_debug_pkg.debug ('pn_var_rent_pkg.update_setup_exp (-)');
9655:
9656: END;
9657:
9658: -------------------------------------------------------------------------------
9731: l_actual_invoiced_amount NUMBER := 0;
9732: l_invoice_inserted BOOLEAN := FALSE;
9733: BEGIN
9734:
9735: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_rev_term_LY_FLY (+) ');
9736:
9737: DELETE pn_payment_terms_all
9738: WHERE var_rent_inv_id IN (SELECT var_rent_inv_id
9739: FROM pn_var_rent_inv_all
9744: DELETE pn_var_rent_inv_all
9745: WHERE period_id = p_last_period_id
9746: AND NVL(actual_exp_code, 'N') <> 'Y';
9747:
9748: pnp_debug_pkg.debug ('Loop for invoices for true up and for last year calculation. ');
9749: /* Loop for invoices for true up and for last year calculation.*/
9750: FOR invoice_date_rec IN invoice_date_cur(p_last_period_id) LOOP
9751:
9752: /* Fetch the maximum adjust number for a particular invoice date */
9818: x_true_up_status => invoice_rec.true_up_status,
9819: x_true_up_exp_code => invoice_rec.true_up_exp_code,
9820: x_org_id => invoice_rec.org_id);
9821:
9822: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
9823: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||invoice_rec.var_rent_inv_id);
9824: /*
9825: FOR payment_rec IN payment_cur(invoice_rec.var_rent_inv_id) LOOP
9826: pn_variable_term_pkg.create_reversal_terms(p_payment_term_id => payment_rec.payment_term_id
9819: x_true_up_exp_code => invoice_rec.true_up_exp_code,
9820: x_org_id => invoice_rec.org_id);
9821:
9822: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
9823: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||invoice_rec.var_rent_inv_id);
9824: /*
9825: FOR payment_rec IN payment_cur(invoice_rec.var_rent_inv_id) LOOP
9826: pn_variable_term_pkg.create_reversal_terms(p_payment_term_id => payment_rec.payment_term_id
9827: ,p_var_rent_inv_id => l_var_rent_inv_id
9832: END LOOP;
9833: END IF;
9834: END LOOP;
9835:
9836: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_rev_term_LY_FLY (-) ');
9837:
9838: END create_rev_term_LY_FLY;
9839:
9840: -------------------------------------------------------------------------------
9901: l_retcode VARCHAR2(250);
9902: l_date DATE := SYSDATE;
9903:
9904: BEGIN
9905: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (+)');
9906: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9907: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9908: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9909: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9902: l_date DATE := SYSDATE;
9903:
9904: BEGIN
9905: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (+)');
9906: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9907: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9908: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9909: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9910: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9903:
9904: BEGIN
9905: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (+)');
9906: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9907: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9908: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9909: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9910: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9911:
9904: BEGIN
9905: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (+)');
9906: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9907: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9908: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9909: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9910: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9911:
9912: FOR main_vr_rec IN main_vr_cur LOOP
9905: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (+)');
9906: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9907: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9908: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9909: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9910: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9911:
9912: FOR main_vr_rec IN main_vr_cur LOOP
9913:
9906: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9907: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9908: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9909: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9910: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9911:
9912: FOR main_vr_rec IN main_vr_cur LOOP
9913:
9914: pnp_debug_pkg.debug ('**************************************************************');
9910: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9911:
9912: FOR main_vr_rec IN main_vr_cur LOOP
9913:
9914: pnp_debug_pkg.debug ('**************************************************************');
9915: pnp_debug_pkg.debug ('Processing variable rent agreement ...'||main_vr_rec.var_rent_id);
9916: pnp_debug_pkg.debug ('**************************************************************');
9917:
9918: /* Fetch the last period id and partial flag before expansion */
9911:
9912: FOR main_vr_rec IN main_vr_cur LOOP
9913:
9914: pnp_debug_pkg.debug ('**************************************************************');
9915: pnp_debug_pkg.debug ('Processing variable rent agreement ...'||main_vr_rec.var_rent_id);
9916: pnp_debug_pkg.debug ('**************************************************************');
9917:
9918: /* Fetch the last period id and partial flag before expansion */
9919: FOR last_period_rec IN last_period_cur(main_vr_rec.var_rent_id) LOOP
9912: FOR main_vr_rec IN main_vr_cur LOOP
9913:
9914: pnp_debug_pkg.debug ('**************************************************************');
9915: pnp_debug_pkg.debug ('Processing variable rent agreement ...'||main_vr_rec.var_rent_id);
9916: pnp_debug_pkg.debug ('**************************************************************');
9917:
9918: /* Fetch the last period id and partial flag before expansion */
9919: FOR last_period_rec IN last_period_cur(main_vr_rec.var_rent_id) LOOP
9920: l_partial_flag := last_period_rec.partial_period;
9918: /* Fetch the last period id and partial flag before expansion */
9919: FOR last_period_rec IN last_period_cur(main_vr_rec.var_rent_id) LOOP
9920: l_partial_flag := last_period_rec.partial_period;
9921: l_last_period_id := last_period_rec.period_id;
9922: pnp_debug_pkg.debug ('l_last_period_id ...'||l_last_period_id);
9923: pnp_debug_pkg.debug ('l_partial_flag ...'||l_partial_flag);
9924: END LOOP;
9925:
9926: /* Update the vr agreement with the new termination date */
9919: FOR last_period_rec IN last_period_cur(main_vr_rec.var_rent_id) LOOP
9920: l_partial_flag := last_period_rec.partial_period;
9921: l_last_period_id := last_period_rec.period_id;
9922: pnp_debug_pkg.debug ('l_last_period_id ...'||l_last_period_id);
9923: pnp_debug_pkg.debug ('l_partial_flag ...'||l_partial_flag);
9924: END LOOP;
9925:
9926: /* Update the vr agreement with the new termination date */
9927: pnp_debug_pkg.debug ('Update the vr agreement with the new termination date ...');
9923: pnp_debug_pkg.debug ('l_partial_flag ...'||l_partial_flag);
9924: END LOOP;
9925:
9926: /* Update the vr agreement with the new termination date */
9927: pnp_debug_pkg.debug ('Update the vr agreement with the new termination date ...');
9928:
9929: UPDATE pn_var_rents_all
9930: SET termination_date = p_new_termn_date
9931: WHERE var_rent_id = main_vr_rec.var_rent_id;
9933: /* call appropriate procedures to create new periods or activate the inactive one
9934: beyond the old termination date */
9935: IF NVL(main_vr_rec.use_gl_calendar,'N') = 'N' THEN
9936:
9937: pnp_debug_pkg.debug ('making a call to create_var_rent_periods_nocal ...');
9938: pn_var_rent_pkg.create_var_rent_periods_nocal(p_var_rent_id => main_vr_rec.var_rent_id ,
9939: p_cumulative_vol => main_vr_rec.cumulative_vol ,
9940: p_yr_start_date => main_vr_rec.year_start_date);
9941:
9939: p_cumulative_vol => main_vr_rec.cumulative_vol ,
9940: p_yr_start_date => main_vr_rec.year_start_date);
9941:
9942: ELSIF NVL(main_vr_rec.use_gl_calendar,'N') = 'Y' THEN
9943: pnp_debug_pkg.debug ('making a call to create_var_rent_periods ...');
9944: pn_var_rent_pkg.create_var_rent_periods( p_var_rent_id => main_vr_rec.var_rent_id,
9945: p_cumulative_vol => main_vr_rec.cumulative_vol,
9946: p_comm_date => main_vr_rec.commencement_date,
9947: p_term_date => p_new_termn_date,
9953:
9954: /* Check if data exists in defaults tables for this variable rent */
9955: FOR rec IN default_exists(main_vr_rec.var_rent_id) LOOP
9956: l_default_exists := 'Y';
9957: pnp_debug_pkg.debug ('l_default_exists is Y');
9958: END LOOP;
9959:
9960: pnp_debug_pkg.debug ('calling appropriate proc after checking if defaults exist ...');
9961: IF l_default_exists = 'Y' THEN
9956: l_default_exists := 'Y';
9957: pnp_debug_pkg.debug ('l_default_exists is Y');
9958: END LOOP;
9959:
9960: pnp_debug_pkg.debug ('calling appropriate proc after checking if defaults exist ...');
9961: IF l_default_exists = 'Y' THEN
9962: pnp_debug_pkg.debug ('calling proc extend_defaults .. if defaults exist ...');
9963: extend_defaults ( p_var_rent_id => main_vr_rec.var_rent_id
9964: , p_old_termn_date => p_old_termn_date
9958: END LOOP;
9959:
9960: pnp_debug_pkg.debug ('calling appropriate proc after checking if defaults exist ...');
9961: IF l_default_exists = 'Y' THEN
9962: pnp_debug_pkg.debug ('calling proc extend_defaults .. if defaults exist ...');
9963: extend_defaults ( p_var_rent_id => main_vr_rec.var_rent_id
9964: , p_old_termn_date => p_old_termn_date
9965: , p_new_termn_date => p_new_termn_date );
9966: ELSE
9963: extend_defaults ( p_var_rent_id => main_vr_rec.var_rent_id
9964: , p_old_termn_date => p_old_termn_date
9965: , p_new_termn_date => p_new_termn_date );
9966: ELSE
9967: pnp_debug_pkg.debug ('calling proc update_setup_exp .. if defaults do not exist ...');
9968: update_setup_exp ( p_var_rent_id => main_vr_rec.var_rent_id
9969: , p_old_termn_date => p_old_termn_date
9970: , p_period_id => l_last_period_id);
9971: END IF;
10001: WHERE var_rent_id = main_vr_rec.var_rent_id;
10002:
10003: END LOOP;
10004:
10005: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (-)');
10006:
10007: EXCEPTION
10008: WHEN OTHERS THEN
10009: pnp_debug_pkg.debug ('Process_vr_early_term Error:'||SQLERRM);
10005: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (-)');
10006:
10007: EXCEPTION
10008: WHEN OTHERS THEN
10009: pnp_debug_pkg.debug ('Process_vr_early_term Error:'||SQLERRM);
10010: x_return_status := FND_API.G_RET_STS_ERROR;
10011: x_return_message := sqlerrm;
10012: END;
10013:
10092: FROM pn_lease_details_all
10093: WHERE lease_change_id = p_lease_change_id;
10094:
10095: BEGIN
10096: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +Start+ (+)');
10097: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10098: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10099: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10100: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10093: WHERE lease_change_id = p_lease_change_id;
10094:
10095: BEGIN
10096: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +Start+ (+)');
10097: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10098: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10099: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10100: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10101: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10094:
10095: BEGIN
10096: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +Start+ (+)');
10097: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10098: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10099: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10100: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10101: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10102: pnp_debug_pkg.log('Setup Expansion Context : '||p_setup_exp_context);
10095: BEGIN
10096: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +Start+ (+)');
10097: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10098: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10099: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10100: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10101: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10102: pnp_debug_pkg.log('Setup Expansion Context : '||p_setup_exp_context);
10103:
10096: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +Start+ (+)');
10097: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10098: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10099: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10100: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10101: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10102: pnp_debug_pkg.log('Setup Expansion Context : '||p_setup_exp_context);
10103:
10104: -- Bug 6524475
10097: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10098: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10099: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10100: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10101: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10102: pnp_debug_pkg.log('Setup Expansion Context : '||p_setup_exp_context);
10103:
10104: -- Bug 6524475
10105: IF p_request_id IS NOT NULL AND p_request_id <> 0 THEN
10098: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10099: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10100: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10101: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10102: pnp_debug_pkg.log('Setup Expansion Context : '||p_setup_exp_context);
10103:
10104: -- Bug 6524475
10105: IF p_request_id IS NOT NULL AND p_request_id <> 0 THEN
10106: l_status := fnd_concurrent.wait_for_request(p_request_id, l_interval, l_max_wait,
10161:
10162: /* Raise an exception that the dates entered by the user do not match with
10163: that in the database */
10164: /*IF (l_old_date <> l_old_term_date) OR (l_new_date <> l_new_term_date ) THEN
10165: pnp_debug_pkg.log('Throwing exception ....');
10166: RAISE INCORRECT_VR_DATES_EXCEPTION ;
10167: END IF;*/
10168:
10169: END IF;
10172:
10173: /* Check if new termination date is same as old termination date */
10174: IF (l_old_term_date <> l_new_term_date) THEN
10175:
10176: pnp_debug_pkg.log('New termination date is not equal to old termination date');
10177: /* Check if the dates and vr context are in sync i.e. if new termination date is less than old termination
10178: date then vr context should be 'CON' and if the new termination date is greater than old termination
10179: date then vr context should be 'EXP'. If this is not the case then throw an exception */
10180: IF (l_new_term_date < l_old_term_date) AND p_vr_context = 'CON' THEN
10178: date then vr context should be 'CON' and if the new termination date is greater than old termination
10179: date then vr context should be 'EXP'. If this is not the case then throw an exception */
10180: IF (l_new_term_date < l_old_term_date) AND p_vr_context = 'CON' THEN
10181:
10182: pnp_debug_pkg.log('Deleting VR agreements starting after new termination date');
10183: PN_VAR_RENTS_PKG.delete_var_rent_agreement(p_lease_id => p_lease_id ,
10184: p_termination_dt => l_new_term_date);
10185:
10186: pnp_debug_pkg.log('Calling VR contraction ....');
10182: pnp_debug_pkg.log('Deleting VR agreements starting after new termination date');
10183: PN_VAR_RENTS_PKG.delete_var_rent_agreement(p_lease_id => p_lease_id ,
10184: p_termination_dt => l_new_term_date);
10185:
10186: pnp_debug_pkg.log('Calling VR contraction ....');
10187: process_vr_early_term ( p_lease_id => p_lease_id
10188: ,p_var_rent_id => NULL
10189: ,p_new_termn_date => l_new_term_date
10190: ,p_old_termn_date => l_old_term_date
10193:
10194: ELSIF (l_new_term_date > l_old_term_date) AND p_vr_context = 'EXP' THEN
10195:
10196: IF p_setup_exp_context IS NULL THEN
10197: pnp_debug_pkg.log('Throwing exception ....');
10198: RAISE MISSING_SETUP_EXCEPTION ;
10199: ELSE
10200: pnp_debug_pkg.log('Calling VR expansion ....');
10201: process_vr_ext ( p_lease_id => p_lease_id
10196: IF p_setup_exp_context IS NULL THEN
10197: pnp_debug_pkg.log('Throwing exception ....');
10198: RAISE MISSING_SETUP_EXCEPTION ;
10199: ELSE
10200: pnp_debug_pkg.log('Calling VR expansion ....');
10201: process_vr_ext ( p_lease_id => p_lease_id
10202: ,p_var_rent_id => NULL
10203: ,p_new_termn_date => l_new_term_date
10204: ,p_old_termn_date => l_old_term_date
10209:
10210: ELSIF (((l_new_term_date < l_old_term_date) AND p_vr_context <> 'CON') OR
10211: ((l_new_term_date > l_old_term_date) AND p_vr_context <> 'EXP')) THEN
10212:
10213: pnp_debug_pkg.log('Throwing exception ....');
10214: RAISE INCORRECT_VR_CONTEXT_EXCEPTION ;
10215: END IF;
10216:
10217: END IF;
10215: END IF;
10216:
10217: END IF;
10218:
10219: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +End+ (+)');
10220:
10221: EXCEPTION
10222: WHEN SCH_ITEMS_FAILED_EXCEPTION THEN
10223: fnd_message.set_name ('PN', 'PN_SCH_ITEMS_REQ_FAILED');
10222: WHEN SCH_ITEMS_FAILED_EXCEPTION THEN
10223: fnd_message.set_name ('PN', 'PN_SCH_ITEMS_REQ_FAILED');
10224: fnd_message.set_token ('REQ_NUM',p_request_id);
10225: errbuf := fnd_message.get;
10226: pnp_debug_pkg.put_log_msg (errbuf);
10227: retcode := 2;
10228: WHEN INCORRECT_VR_CONTEXT_EXCEPTION THEN
10229: fnd_message.set_name ('PN', 'INCORRECT_VR_CONTEXT');
10230: errbuf := fnd_message.get;
10227: retcode := 2;
10228: WHEN INCORRECT_VR_CONTEXT_EXCEPTION THEN
10229: fnd_message.set_name ('PN', 'INCORRECT_VR_CONTEXT');
10230: errbuf := fnd_message.get;
10231: pnp_debug_pkg.put_log_msg (errbuf);
10232: retcode := 2;
10233: WHEN INCORRECT_VR_DATES_EXCEPTION THEN
10234: fnd_message.set_name ('PN', 'INCORRECT_VR_DATES');
10235: errbuf := fnd_message.get;
10232: retcode := 2;
10233: WHEN INCORRECT_VR_DATES_EXCEPTION THEN
10234: fnd_message.set_name ('PN', 'INCORRECT_VR_DATES');
10235: errbuf := fnd_message.get;
10236: pnp_debug_pkg.put_log_msg (errbuf);
10237: retcode := 2;
10238: WHEN MISSING_CHANGE_ID_EXCEPTION THEN
10239: fnd_message.set_name ('PN', 'MISSING_CHANGE_ID');
10240: errbuf := fnd_message.get;
10237: retcode := 2;
10238: WHEN MISSING_CHANGE_ID_EXCEPTION THEN
10239: fnd_message.set_name ('PN', 'MISSING_CHANGE_ID');
10240: errbuf := fnd_message.get;
10241: pnp_debug_pkg.put_log_msg (errbuf);
10242: retcode := 2;
10243: WHEN MISSING_SETUP_EXCEPTION THEN
10244: fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
10245: errbuf := fnd_message.get;
10242: retcode := 2;
10243: WHEN MISSING_SETUP_EXCEPTION THEN
10244: fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
10245: errbuf := fnd_message.get;
10246: pnp_debug_pkg.put_log_msg (errbuf);
10247: retcode := 2;
10248: WHEN OTHERS THEN
10249: Errbuf := SQLERRM;
10250: Retcode := 2;
10580: --Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
10581: --Fnd_File.Put_Line ( Fnd_File.OutPut,' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10582: --Fnd_File.Put_Line ( Fnd_File.OutPut,' cannot be processed. You will need to recreate this agreement manually.');
10583: --Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
10584: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10585: pnp_debug_pkg.log (' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10586: pnp_debug_pkg.log (' cannot be processed. You will need to recreate this agreement manually.');
10587: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10588:
10581: --Fnd_File.Put_Line ( Fnd_File.OutPut,' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10582: --Fnd_File.Put_Line ( Fnd_File.OutPut,' cannot be processed. You will need to recreate this agreement manually.');
10583: --Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
10584: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10585: pnp_debug_pkg.log (' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10586: pnp_debug_pkg.log (' cannot be processed. You will need to recreate this agreement manually.');
10587: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10588:
10589: EXIT outer;
10582: --Fnd_File.Put_Line ( Fnd_File.OutPut,' cannot be processed. You will need to recreate this agreement manually.');
10583: --Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
10584: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10585: pnp_debug_pkg.log (' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10586: pnp_debug_pkg.log (' cannot be processed. You will need to recreate this agreement manually.');
10587: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10588:
10589: EXIT outer;
10590: END IF;
10583: --Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
10584: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10585: pnp_debug_pkg.log (' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10586: pnp_debug_pkg.log (' cannot be processed. You will need to recreate this agreement manually.');
10587: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10588:
10589: EXIT outer;
10590: END IF;
10591: END LOOP;
10931:
10932: /* Completed Inserting constraints into constraint defaults table and
10933: populating constraints with the constraints default id */
10934:
10935: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10936: pnp_debug_pkg.log (' Agreement ' || l_rent_num || ' has been Updated');
10937: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10938:
10939: END IF;
10932: /* Completed Inserting constraints into constraint defaults table and
10933: populating constraints with the constraints default id */
10934:
10935: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10936: pnp_debug_pkg.log (' Agreement ' || l_rent_num || ' has been Updated');
10937: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10938:
10939: END IF;
10940:
10933: populating constraints with the constraints default id */
10934:
10935: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10936: pnp_debug_pkg.log (' Agreement ' || l_rent_num || ' has been Updated');
10937: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10938:
10939: END IF;
10940:
10941: END IF;
10940:
10941: END IF;
10942: END LOOP;
10943: ELSE
10944: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10945: pnp_debug_pkg.log (' There is no Variable Rent Agreement with given search criteria');
10946: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10947: END IF;
10948:
10941: END IF;
10942: END LOOP;
10943: ELSE
10944: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10945: pnp_debug_pkg.log (' There is no Variable Rent Agreement with given search criteria');
10946: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10947: END IF;
10948:
10949: EXCEPTION
10942: END LOOP;
10943: ELSE
10944: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10945: pnp_debug_pkg.log (' There is no Variable Rent Agreement with given search criteria');
10946: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10947: END IF;
10948:
10949: EXCEPTION
10950: WHEN BAD_CALL_EXCEPTION THEN
10947: END IF;
10948:
10949: EXCEPTION
10950: WHEN BAD_CALL_EXCEPTION THEN
10951: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10952: pnp_debug_pkg.log (' Input to the program is invalid');
10953: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10954:
10955: END copy_bkpt_main_to_setup;
10948:
10949: EXCEPTION
10950: WHEN BAD_CALL_EXCEPTION THEN
10951: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10952: pnp_debug_pkg.log (' Input to the program is invalid');
10953: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10954:
10955: END copy_bkpt_main_to_setup;
10956:
10949: EXCEPTION
10950: WHEN BAD_CALL_EXCEPTION THEN
10951: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10952: pnp_debug_pkg.log (' Input to the program is invalid');
10953: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10954:
10955: END copy_bkpt_main_to_setup;
10956:
10957: /*===========================================================================+
10986: l_inv_exp NUMBER;
10987:
10988: BEGIN
10989:
10990: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_INV_EXP (+)');
10991:
10992: SELECT 1
10993: INTO l_inv_exp
10994: FROM dual
10999: AND (forecasted_exp_code = 'Y'));
11000:
11001: RETURN l_inv_exp;
11002:
11003: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_INV_EXP (-)');
11004:
11005: EXCEPTION
11006:
11007: WHEN OTHERS THEN
11110: AND bkdt_end_date BETWEEN p_start_date AND p_end_date;
11111:
11112: BEGIN
11113:
11114: pnp_debug_pkg.log('pn_var_rent_pkg.rates_validation (+)');
11115:
11116: FOR var_rec in var_cur(p_vr_id => p_var_rent_id) LOOP
11117: IF p_agr_start_date IS NOT NULL THEN
11118: l_agr_start_date := p_agr_start_date;
11362: END IF;
11363:
11364: RETURN l_return_status;
11365:
11366: pnp_debug_pkg.log('pn_var_rent_pkg.rates_validation (-)');
11367:
11368: END rates_validation;
11369:
11370: