[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: /*=============================================================================+
1053:
1054:
1055: BEGIN
1056:
1057: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (+)');
1058: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1059: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1060: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1061: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1054:
1055: BEGIN
1056:
1057: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (+)');
1058: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1059: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1060: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1061: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1062: pnp_debug_pkg.debug ('p_create_flag '||p_create_flag);
1055: BEGIN
1056:
1057: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (+)');
1058: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1059: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1060: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1061: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1062: pnp_debug_pkg.debug ('p_create_flag '||p_create_flag);
1063:
1056:
1057: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (+)');
1058: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1059: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1060: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1061: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1062: pnp_debug_pkg.debug ('p_create_flag '||p_create_flag);
1063:
1064: -- Get dates/info, GL calendar used from Variable Rent record
1057: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (+)');
1058: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1059: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1060: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1061: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1062: pnp_debug_pkg.debug ('p_create_flag '||p_create_flag);
1063:
1064: -- Get dates/info, GL calendar used from Variable Rent record
1065:
1058: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id);
1059: pnp_debug_pkg.debug ('p_cumulative_vol'||p_cumulative_vol);
1060: pnp_debug_pkg.debug ('p_comm_date '||p_comm_date);
1061: pnp_debug_pkg.debug ('p_term_date '||p_term_date);
1062: pnp_debug_pkg.debug ('p_create_flag '||p_create_flag);
1063:
1064: -- Get dates/info, GL calendar used from Variable Rent record
1065:
1066: SELECT vr.commencement_date,
1209: l_period_id := period_rec.period_id;
1210: l_end_date := period_rec.end_date;
1211: l_status := period_rec.status;
1212: l_period_exists := 'Y';
1213: pnp_debug_pkg.debug(' period exists ...'||l_periodId);
1214: pnp_debug_pkg.debug('');
1215: END LOOP;
1216:
1217: IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
1210: l_end_date := period_rec.end_date;
1211: l_status := period_rec.status;
1212: l_period_exists := 'Y';
1213: pnp_debug_pkg.debug(' period exists ...'||l_periodId);
1214: pnp_debug_pkg.debug('');
1215: END LOOP;
1216:
1217: IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
1218: --call to insert into PN_VAR_PERIODS;
1216:
1217: IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
1218: --call to insert into PN_VAR_PERIODS;
1219:
1220: pnp_debug_pkg.debug(' period exists ...N');
1221: PN_VAR_RENT_PKG.INSERT_PERIODS_ROW
1222: (X_ROWID => l_rowId,
1223: X_PERIOD_ID => l_periodId,
1224: X_PERIOD_NUM => l_periodNum,
1252:
1253: l_rowId := NULL;
1254: l_periodId := NULL;
1255: l_periodNum := NULL;
1256: pnp_debug_pkg.debug('period inserted is ...'||l_periodId);
1257:
1258: ELSE /* period exists in the database */
1259:
1260: pnp_debug_pkg.debug('New Period Start Date:'||p_per_start_date);
1256: pnp_debug_pkg.debug('period inserted is ...'||l_periodId);
1257:
1258: ELSE /* period exists in the database */
1259:
1260: pnp_debug_pkg.debug('New Period Start Date:'||p_per_start_date);
1261: pnp_debug_pkg.debug('New Period End Date:'||p_per_end_date);
1262: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
1263:
1264: /* Check if the period in the database is a partial period */
1257:
1258: ELSE /* period exists in the database */
1259:
1260: pnp_debug_pkg.debug('New Period Start Date:'||p_per_start_date);
1261: pnp_debug_pkg.debug('New Period End Date:'||p_per_end_date);
1262: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
1263:
1264: /* Check if the period in the database is a partial period */
1265: IF p_per_end_date = l_end_date THEN
1258: ELSE /* period exists in the database */
1259:
1260: pnp_debug_pkg.debug('New Period Start Date:'||p_per_start_date);
1261: pnp_debug_pkg.debug('New Period End Date:'||p_per_end_date);
1262: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
1263:
1264: /* Check if the period in the database is a partial period */
1265: IF p_per_end_date = l_end_date THEN
1266:
1263:
1264: /* Check if the period in the database is a partial period */
1265: IF p_per_end_date = l_end_date THEN
1266:
1267: pnp_debug_pkg.debug('period end date is equsal to period in data base ..');
1268:
1269: /* Make the period as active if it is inactive */
1270: IF l_status = pn_var_rent_pkg.status THEN
1271:
1268:
1269: /* Make the period as active if it is inactive */
1270: IF l_status = pn_var_rent_pkg.status THEN
1271:
1272: pnp_debug_pkg.debug('period is inactive ..'||l_period_id);
1273: UPDATE pn_var_periods_all
1274: SET status = NULL
1275: WHERE period_id = l_period_id;
1276:
1368:
1369: IF l_group_exists = 'N' THEN
1370:
1371: /* group does not exist in the database and needs to be added */
1372: pnp_debug_pkg.debug ('l_group_exists = N');
1373: ------------------------------------------------------------
1374: --call to insert into PN_VAR_GRP_DATES;
1375: ------------------------------------------------------------
1376: PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
1396: X_LAST_UPDATED_BY => p_created_by,
1397: X_LAST_UPDATE_LOGIN => p_created_by
1398: );
1399:
1400: pnp_debug_pkg.debug ('group dte id of group added ....'||l_grpDateId);
1401: l_rowId := NULL;
1402: l_grpDateId := NULL;
1403:
1404: ELSE
1401: l_rowId := NULL;
1402: l_grpDateId := NULL;
1403:
1404: ELSE
1405: pnp_debug_pkg.debug ('l_group_exists = Y');
1406: /* Check if the group in the database is a partial or if the new group is partial */
1407: IF p_grp_end_date <> l_grp_end_date THEN
1408:
1409: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1405: pnp_debug_pkg.debug ('l_group_exists = Y');
1406: /* Check if the group in the database is a partial or if the new group is partial */
1407: IF p_grp_end_date <> l_grp_end_date THEN
1408:
1409: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1410: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1411: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1412: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1413: /* Updte the end date and proration factor for the group */
1406: /* Check if the group in the database is a partial or if the new group is partial */
1407: IF p_grp_end_date <> l_grp_end_date THEN
1408:
1409: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1410: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1411: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1412: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1413: /* Updte the end date and proration factor for the group */
1414: UPDATE pn_var_grp_dates_all
1407: IF p_grp_end_date <> l_grp_end_date THEN
1408:
1409: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1410: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1411: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1412: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1413: /* Updte the end date and proration factor for the group */
1414: UPDATE pn_var_grp_dates_all
1415: SET grp_end_date = p_grp_end_date,
1408:
1409: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1410: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1411: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1412: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1413: /* Updte the end date and proration factor for the group */
1414: UPDATE pn_var_grp_dates_all
1415: SET grp_end_date = p_grp_end_date,
1416: proration_Factor = round(p_proration_factor,10),
1474:
1475: IF l_group_exists = 'N' THEN
1476:
1477: /* group does not exist in the database and needs to be added */
1478: pnp_debug_pkg.debug ('l_group_exists = N');
1479:
1480: ------------------------------------------------------------
1481: --call to insert into PN_VAR_GRP_DATES;
1482: ------------------------------------------------------------
1503: X_LAST_UPDATED_BY => p_created_by,
1504: X_LAST_UPDATE_LOGIN => p_created_by
1505: );
1506:
1507: pnp_debug_pkg.debug ('group dte id of group added ....'||l_grpDateId);
1508: l_rowId := NULL;
1509: l_grpDateId := NULL;
1510:
1511: ELSE
1508: l_rowId := NULL;
1509: l_grpDateId := NULL;
1510:
1511: ELSE
1512: pnp_debug_pkg.debug ('l_group_exists = Y');
1513: /* Check if the group in the database is a partial or if the new group is partial */
1514: IF p_grp_end_date <> l_grp_end_date THEN
1515:
1516: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1512: pnp_debug_pkg.debug ('l_group_exists = Y');
1513: /* Check if the group in the database is a partial or if the new group is partial */
1514: IF p_grp_end_date <> l_grp_end_date THEN
1515:
1516: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1517: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1518: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1519: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1520: /* Updte the end date and proration factor for the group */
1513: /* Check if the group in the database is a partial or if the new group is partial */
1514: IF p_grp_end_date <> l_grp_end_date THEN
1515:
1516: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1517: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1518: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1519: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1520: /* Updte the end date and proration factor for the group */
1521: UPDATE pn_var_grp_dates_all
1514: IF p_grp_end_date <> l_grp_end_date THEN
1515:
1516: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1517: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1518: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1519: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1520: /* Updte the end date and proration factor for the group */
1521: UPDATE pn_var_grp_dates_all
1522: SET grp_end_date = p_grp_end_date,
1515:
1516: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1517: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1518: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1519: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1520: /* Updte the end date and proration factor for the group */
1521: UPDATE pn_var_grp_dates_all
1522: SET grp_end_date = p_grp_end_date,
1523: proration_Factor = round(p_proration_factor,10),
1582:
1583: IF l_group_exists = 'N' THEN
1584:
1585: /* group does not exist in the database and needs to be added */
1586: pnp_debug_pkg.debug ('l_group_exists = N');
1587:
1588: ------------------------------------------------------------
1589: --call to insert into PN_VAR_GRP_DATES;
1590: ------------------------------------------------------------
1612: X_LAST_UPDATED_BY => p_created_by,
1613: X_LAST_UPDATE_LOGIN => p_created_by
1614: );
1615:
1616: pnp_debug_pkg.debug ('group dte id of group added ....'||l_grpDateId);
1617: l_rowId := NULL;
1618: l_grpDateId := NULL;
1619:
1620: ELSE
1617: l_rowId := NULL;
1618: l_grpDateId := NULL;
1619:
1620: ELSE
1621: pnp_debug_pkg.debug ('l_group_exists = Y');
1622: /* Check if the group in the database is a partial or if the new group is partial */
1623: IF p_grp_end_date <> l_grp_end_date THEN
1624:
1625: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1621: pnp_debug_pkg.debug ('l_group_exists = Y');
1622: /* Check if the group in the database is a partial or if the new group is partial */
1623: IF p_grp_end_date <> l_grp_end_date THEN
1624:
1625: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1626: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1627: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1628: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1629: /* Updte the end date and proration factor for the group */
1622: /* Check if the group in the database is a partial or if the new group is partial */
1623: IF p_grp_end_date <> l_grp_end_date THEN
1624:
1625: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1626: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1627: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1628: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1629: /* Updte the end date and proration factor for the group */
1630: UPDATE pn_var_grp_dates_all
1623: IF p_grp_end_date <> l_grp_end_date THEN
1624:
1625: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1626: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1627: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1628: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1629: /* Updte the end date and proration factor for the group */
1630: UPDATE pn_var_grp_dates_all
1631: SET grp_end_date = p_grp_end_date,
1624:
1625: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1626: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1627: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1628: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1629: /* Updte the end date and proration factor for the group */
1630: UPDATE pn_var_grp_dates_all
1631: SET grp_end_date = p_grp_end_date,
1632: proration_Factor = round(p_proration_factor,10),
1691:
1692: IF l_group_exists = 'N' THEN
1693:
1694: /* group does not exist in the database and needs to be added */
1695: pnp_debug_pkg.debug ('l_group_exists = N');
1696:
1697: ------------------------------------------------------------
1698: --call to insert into PN_VAR_GRP_DATES;
1699: ------------------------------------------------------------
1720: X_LAST_UPDATED_BY => p_created_by,
1721: X_LAST_UPDATE_LOGIN => p_created_by
1722: );
1723:
1724: pnp_debug_pkg.debug ('group dte id of group added ....'||l_grpDateId);
1725: l_rowId := NULL;
1726: l_grpDateId := NULL;
1727:
1728: ELSE
1725: l_rowId := NULL;
1726: l_grpDateId := NULL;
1727:
1728: ELSE
1729: pnp_debug_pkg.debug ('l_group_exists = Y');
1730: /* Check if the group in the database is a partial or if the new group is partial */
1731: IF p_grp_end_date <> l_grp_end_date THEN
1732:
1733: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1729: pnp_debug_pkg.debug ('l_group_exists = Y');
1730: /* Check if the group in the database is a partial or if the new group is partial */
1731: IF p_grp_end_date <> l_grp_end_date THEN
1732:
1733: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1734: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1735: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1736: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1737: /* Updte the end date and proration factor for the group */
1730: /* Check if the group in the database is a partial or if the new group is partial */
1731: IF p_grp_end_date <> l_grp_end_date THEN
1732:
1733: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1734: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1735: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1736: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1737: /* Updte the end date and proration factor for the group */
1738: UPDATE pn_var_grp_dates_all
1731: IF p_grp_end_date <> l_grp_end_date THEN
1732:
1733: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1734: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1735: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1736: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1737: /* Updte the end date and proration factor for the group */
1738: UPDATE pn_var_grp_dates_all
1739: SET grp_end_date = p_grp_end_date,
1732:
1733: pnp_debug_pkg.debug ('updating end date of group '||l_grp_date_id);
1734: pnp_debug_pkg.debug ('updating end date as '||p_grp_end_date);
1735: pnp_debug_pkg.debug ('updating proration factor as '||p_proration_factor);
1736: pnp_debug_pkg.debug ('updating due date as '||p_due_date);
1737: /* Updte the end date and proration factor for the group */
1738: UPDATE pn_var_grp_dates_all
1739: SET grp_end_date = p_grp_end_date,
1740: proration_Factor = round(p_proration_factor,10),
2072: END LOOP;
2073:
2074: END LOOP;
2075:
2076: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS (-)');
2077:
2078: END CREATE_VAR_RENT_PERIODS;
2079:
2080: /*=============================================================================+
2211: AND start_date = p_start_date;
2212:
2213: BEGIN
2214:
2215: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS_NOCAL (+)');
2216: put_log('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS_NOCAL (+)');
2217:
2218: SELECT VR.commencement_date,
2219: VR.termination_date,
2291: l_periodId := period_rec.period_id;
2292: l_end_date := period_rec.end_date;
2293: l_status := period_rec.status;
2294: l_period_exists := 'Y';
2295: pnp_debug_pkg.debug(' period exists ...'||l_periodId);
2296: pnp_debug_pkg.debug('');
2297: END LOOP;
2298:
2299: IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
2292: l_end_date := period_rec.end_date;
2293: l_status := period_rec.status;
2294: l_period_exists := 'Y';
2295: pnp_debug_pkg.debug(' period exists ...'||l_periodId);
2296: pnp_debug_pkg.debug('');
2297: END LOOP;
2298:
2299: IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
2300:
2303: ELSE
2304: l_partial_period := 'Y';
2305: END IF;
2306:
2307: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2308: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2309: pnp_debug_pkg.debug('partial period ...'||l_partial_period);
2310:
2311: PN_VAR_RENT_PKG.INSERT_PERIODS_ROW
2304: l_partial_period := 'Y';
2305: END IF;
2306:
2307: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2308: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2309: pnp_debug_pkg.debug('partial period ...'||l_partial_period);
2310:
2311: PN_VAR_RENT_PKG.INSERT_PERIODS_ROW
2312: ( X_ROWID => l_rowId,
2305: END IF;
2306:
2307: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2308: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2309: pnp_debug_pkg.debug('partial period ...'||l_partial_period);
2310:
2311: PN_VAR_RENT_PKG.INSERT_PERIODS_ROW
2312: ( X_ROWID => l_rowId,
2313: X_PERIOD_ID => VR_periods(i).l_period_id,
2339: X_LAST_UPDATED_BY => l_created_by,
2340: X_LAST_UPDATE_LOGIN => l_created_by
2341: );
2342:
2343: pnp_debug_pkg.debug('period inserted is ...'||VR_periods(i).l_period_id);
2344: l_rowId := NULL;
2345: l_periodNum := NULL;
2346:
2347: ELSE /* period exists in the database */
2345: l_periodNum := NULL;
2346:
2347: ELSE /* period exists in the database */
2348:
2349: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2350: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2351: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
2352:
2353: VR_periods(i).l_period_id := l_periodId;
2346:
2347: ELSE /* period exists in the database */
2348:
2349: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2350: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2351: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
2352:
2353: VR_periods(i).l_period_id := l_periodId;
2354:
2347: ELSE /* period exists in the database */
2348:
2349: pnp_debug_pkg.debug('New Period Start Date:'||vr_periods(i).l_period_start_date);
2350: pnp_debug_pkg.debug('New Period End Date:'||vr_periods(i).l_period_end_date);
2351: pnp_debug_pkg.debug('l_end_date:'||l_end_date);
2352:
2353: VR_periods(i).l_period_id := l_periodId;
2354:
2355: /* Check if the period in the database is a partial period */
2354:
2355: /* Check if the period in the database is a partial period */
2356: IF vr_periods(i).l_period_end_date = l_end_date THEN
2357:
2358: pnp_debug_pkg.debug('period end date is equsal to period in data base ..');
2359:
2360: /* Make the period as active if it is inactive */
2361: IF l_status = pn_var_rent_pkg.status THEN
2362:
2359:
2360: /* Make the period as active if it is inactive */
2361: IF l_status = pn_var_rent_pkg.status THEN
2362:
2363: pnp_debug_pkg.debug('period is inactive ..'||l_periodId);
2364:
2365: UPDATE pn_var_periods_all
2366: SET status = NULL
2367: WHERE period_id = l_periodId;
2369: END IF;
2370:
2371: ELSIF vr_periods(i).l_period_end_date <> l_end_date THEN
2372:
2373: pnp_debug_pkg.debug('Need to update the period end date '||l_periodId);
2374:
2375: IF (vr_periods(i).l_period_end_date
2376: - vr_periods(i).l_period_start_date) + 1 >= 365 THEN
2377:
2494: FOR j IN l_grp_counter..(VR_grp_dates.COUNT - 1) LOOP
2495:
2496: l_group_exists := 'N';
2497:
2498: pnp_debug_pkg.debug(' group start date ...'||VR_grp_dates(j).l_grp_start_date);
2499: pnp_debug_pkg.debug(' group end date ...'||VR_periods(i).l_period_end_date);
2500:
2501: EXIT WHEN VR_grp_dates(j).l_grp_start_date > VR_periods(i).l_period_end_date;
2502:
2495:
2496: l_group_exists := 'N';
2497:
2498: pnp_debug_pkg.debug(' group start date ...'||VR_grp_dates(j).l_grp_start_date);
2499: pnp_debug_pkg.debug(' group end date ...'||VR_periods(i).l_period_end_date);
2500:
2501: EXIT WHEN VR_grp_dates(j).l_grp_start_date > VR_periods(i).l_period_end_date;
2502:
2503: /* Check if the group in PL/SQL table with a specified start date
2510:
2511: IF l_group_exists = 'N' THEN
2512:
2513: /* group does not exist in the database and needs to be added */
2514: pnp_debug_pkg.debug (' Group does not exist - to insert group ');
2515:
2516: /*l_proration_Factor
2517: := ((vr_grp_dates(j).l_grp_end_date - vr_grp_dates(j).l_grp_start_date) + 1 )
2518: / ((LAST_DAY(ADD_MONTHS(LAST_DAY(vr_grp_dates(j).l_group_date),l_reptg_freq_code-1))
2563: X_LAST_UPDATED_BY => l_created_by,
2564: X_LAST_UPDATE_LOGIN => l_created_by
2565: );
2566:
2567: pnp_debug_pkg.debug ('group dte id of group added ....'||l_grpDateId);
2568: l_rowId := NULL;
2569: l_grpDateId := NULL;
2570:
2571: ELSE
2569: l_grpDateId := NULL;
2570:
2571: ELSE
2572:
2573: pnp_debug_pkg.debug (' group exists - update it ');
2574: /* Check if the group in the database is a partial or if the new group is partial */
2575:
2576: IF VR_grp_dates(j).l_grp_end_date <> l_grp_end_date THEN
2577:
2600: l_proration_Factor := 1;
2601:
2602: END IF;
2603:
2604: pnp_debug_pkg.debug ('updating end date of group '||VR_grp_dates(j).l_grp_end_date);
2605: pnp_debug_pkg.debug ('updating proration factor as '||l_proration_Factor);
2606: pnp_debug_pkg.debug ('updating due date of group '||VR_grp_dates(j).l_reptg_due_date);
2607: /* Updte the end date and proration factor for the group */
2608:
2601:
2602: END IF;
2603:
2604: pnp_debug_pkg.debug ('updating end date of group '||VR_grp_dates(j).l_grp_end_date);
2605: pnp_debug_pkg.debug ('updating proration factor as '||l_proration_Factor);
2606: pnp_debug_pkg.debug ('updating due date of group '||VR_grp_dates(j).l_reptg_due_date);
2607: /* Updte the end date and proration factor for the group */
2608:
2609: UPDATE pn_var_grp_dates_all
2602: END IF;
2603:
2604: pnp_debug_pkg.debug ('updating end date of group '||VR_grp_dates(j).l_grp_end_date);
2605: pnp_debug_pkg.debug ('updating proration factor as '||l_proration_Factor);
2606: pnp_debug_pkg.debug ('updating due date of group '||VR_grp_dates(j).l_reptg_due_date);
2607: /* Updte the end date and proration factor for the group */
2608:
2609: UPDATE pn_var_grp_dates_all
2610: SET grp_end_date = VR_grp_dates(j).l_grp_end_date,
2625: PN_VAR_RENT_PKG.delete_report_date_row(p_var_rent_id, NULL);
2626: PN_VAR_RENT_PKG.create_report_dates (p_var_rent_id);
2627:
2628: put_log('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS_NOCAL (-)');
2629: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CREATE_VAR_RENT_PERIODS_NOCAL (-)');
2630:
2631: END CREATE_VAR_RENT_PERIODS_NOCAL;
2632:
2633: /*=============================================================================+
2676: l_date DATE;
2677:
2678: BEGIN
2679:
2680: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_PERIODS (+)');
2681:
2682: l_lines_exist := PN_VAR_RENT_PKG.find_if_lines_exist(p_var_rent_id, NULL, l_date);
2683:
2684: l_constr_exist:= PN_VAR_RENT_PKG.find_if_constr_exist(p_var_rent_id, l_date);
2795: IF SQL%NOTFOUND THEN
2796: NULL;
2797: END IF;
2798:
2799: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_PERIODS (-)');
2800:
2801: END DELETE_VAR_RENT_PERIODS;
2802:
2803: /*=============================================================================+
2848: p_cumulative_vol VARCHAR2(1);
2849:
2850: BEGIN
2851:
2852: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_VAR_RENT_PERIODS (+)');
2853:
2854: -- Get dates/info, GL calendar used from Variable Rent record
2855:
2856: SELECT DECODE(cal.reptg_freq_code,'MON', 1,
2942: AND grp_start_date <= p_term_date
2943: AND grp_end_date >= p_term_date
2944: AND rownum < 2;
2945:
2946: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_VAR_RENT_PERIODS (-)');
2947:
2948: END UPDATE_VAR_RENT_PERIODS;
2949:
2950: /*===========================================================================+
2991: AND end_date > l_date);
2992:
2993: BEGIN
2994:
2995: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_CONSTR (+)');
2996:
2997: FOR constr_rec IN c1 LOOP
2998:
2999: PN_VAR_CONSTRAINTS_PKG.DELETE_ROW(constr_rec.constraint_id);
3006: END IF;
3007:
3008: END LOOP;
3009:
3010: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_CONSTR (-)');
3011:
3012: END DELETE_VAR_RENT_CONSTR;
3013:
3014: /*===========================================================================+
3063: AND end_date > l_date);
3064:
3065: BEGIN
3066:
3067: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_LINES (+)');
3068:
3069: FOR lines_rec IN c1 LOOP
3070:
3071: l_bkptshd_exist := PN_VAR_RENT_PKG.find_if_bkptshd_exist(lines_rec.line_item_id);
3107: END IF;
3108:
3109: END LOOP;
3110:
3111: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_LINES (-)');
3112:
3113: END DELETE_VAR_RENT_LINES;
3114:
3115: /*===========================================================================+
3148: WHERE line_item_id = p_line_item_id;
3149:
3150: BEGIN
3151:
3152: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_DEDUCT (+)');
3153:
3154: FOR deduct_rec IN c1 LOOP
3155:
3156: PN_VAR_DEDUCTIONS_PKG.DELETE_ROW(deduct_rec.deduction_id);
3163: END IF;
3164:
3165: END LOOP;
3166:
3167: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_DEDUCT (-)');
3168:
3169: END DELETE_VAR_RENT_DEDUCT;
3170:
3171: /*===========================================================================+
3206: WHERE line_item_id = p_line_item_id;
3207:
3208: BEGIN
3209:
3210: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_VOL_HIST (+)');
3211:
3212: FOR vol_hist_rec IN c1 LOOP
3213:
3214: PN_VAR_VOL_HIST_PKG.DELETE_ROW(vol_hist_rec.vol_hist_id);
3221: END IF;
3222:
3223: END LOOP;
3224:
3225: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_VOL_HIST (-)');
3226:
3227: END DELETE_VAR_VOL_HIST;
3228:
3229: /*===========================================================================+
3266: WHERE line_item_id = p_line_item_id;
3267:
3268: BEGIN
3269:
3270: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_HEAD (+)');
3271:
3272: FOR head_rec IN c2 LOOP
3273:
3274: l_bkptsdet_exist := PN_VAR_RENT_PKG.find_if_bkptsdet_exist(head_rec.bkpt_header_id);
3284: PN_VAR_BKPTS_HEAD_PKG.DELETE_ROW(head_rec.bkpt_header_id);
3285:
3286: END LOOP;
3287:
3288: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_HEAD (-)');
3289:
3290: END DELETE_VAR_BKPTS_HEAD;
3291:
3292: /*===========================================================================+
3327: WHERE bkpt_header_id = p_bkpt_header_id;
3328:
3329: BEGIN
3330:
3331: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_DET (+)');
3332:
3333: FOR det_rec IN c1 LOOP
3334:
3335: PN_VAR_BKPTS_DET_PKG.DELETE_ROW(det_rec.bkpt_detail_id);
3335: PN_VAR_BKPTS_DET_PKG.DELETE_ROW(det_rec.bkpt_detail_id);
3336:
3337: END LOOP;
3338:
3339: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_DET (-)');
3340:
3341: END DELETE_VAR_BKPTS_DET;
3342:
3343: /*===========================================================================+
3370: l_period_exists NUMBER;
3371:
3372: BEGIN
3373:
3374: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_PERIOD_EXISTS (+)');
3375:
3376: SELECT 1
3377: INTO l_period_exists
3378: FROM dual
3381: WHERE periods.var_rent_id = p_var_rent_id);
3382:
3383: RETURN l_period_exists;
3384:
3385: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_PERIOD_EXISTS (-)');
3386:
3387: EXCEPTION
3388:
3389: WHEN OTHERS THEN
3422: l_calculation_exists NUMBER;
3423:
3424: BEGIN
3425:
3426: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CALCULATION_EXISTS (+)');
3427:
3428: SELECT 1
3429: INTO l_calculation_exists
3430: FROM dual
3433: WHERE inv.var_rent_id = p_var_rent_id);
3434:
3435: RETURN l_calculation_exists;
3436:
3437: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CALCULATION_EXISTS (-)');
3438:
3439: EXCEPTION
3440:
3441: WHEN OTHERS THEN
3473: l_invoice_exists NUMBER;
3474:
3475: BEGIN
3476:
3477: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_INVOICE_EXISTS (+)');
3478:
3479: SELECT 1
3480: INTO l_invoice_exists
3481: FROM dual
3484: WHERE inv.var_rent_id = p_var_rent_id);
3485:
3486: RETURN l_invoice_exists;
3487:
3488: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_INVOICE_EXISTS (-)');
3489:
3490: EXCEPTION
3491:
3492: WHEN OTHERS THEN
3540:
3541:
3542: BEGIN
3543:
3544: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_INVOICES (+)');
3545:
3546: l_date := NVL(p_term_date,(TO_DATE('01/01/1776','mm/dd/yyyy')));
3547:
3548: l_lines_exist := PN_VAR_RENT_PKG.find_if_lines_exist(p_var_rent_id, NULL, l_date);
3637: END IF;
3638:
3639: END LOOP;
3640:
3641: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_INVOICES (-)');
3642:
3643: END DELETE_VAR_RENT_INVOICES;
3644:
3645: /*===========================================================================+
3672: l_vrdates_exists NUMBER;
3673:
3674: BEGIN
3675:
3676: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VRDATES_EXISTS (+)');
3677:
3678: SELECT 1
3679: INTO l_vrdates_exists
3680: FROM dual
3683: WHERE dates.var_rent_id = p_var_rent_id);
3684:
3685: RETURN l_vrdates_exists;
3686:
3687: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VRDATES_EXISTS (-)');
3688:
3689: EXCEPTION
3690:
3691: WHEN OTHERS THEN
3729: l_date DATE;
3730:
3731: BEGIN
3732:
3733: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_LINES_EXIST (+)');
3734:
3735: l_date := nvl(p_term_date,(to_date('01/01/1776','mm/dd/yyyy')));
3736:
3737: SELECT 1
3748: );
3749:
3750: RETURN l_lines_exist;
3751:
3752: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_LINES_EXIST (-)');
3753:
3754: EXCEPTION
3755:
3756: WHEN OTHERS THEN
3788: l_volhist_exist NUMBER;
3789:
3790: BEGIN
3791:
3792: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VOLHIST_EXIST (+)');
3793:
3794: SELECT 1
3795: INTO l_volhist_exist
3796: FROM dual
3800: );
3801:
3802: RETURN l_volhist_exist;
3803:
3804: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VOLHIST_EXIST (-)');
3805:
3806: EXCEPTION
3807:
3808: WHEN OTHERS THEN
3844: l_volhist_approved_exist VARCHAR2(1);
3845:
3846: BEGIN
3847:
3848: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VOLHIST_APPROVED_EXIST (+)');
3849:
3850: SELECT 'Y'
3851: INTO l_volhist_approved_exist
3852: FROM dual
3858: );
3859:
3860: RETURN l_volhist_approved_exist;
3861:
3862: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_VOLHIST_APPROVED_EXIST (-)');
3863:
3864: EXCEPTION
3865:
3866: WHEN OTHERS THEN
3963: END IF;
3964:
3965: RETURN l_volhist_bkpts_exist;
3966:
3967: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTS_EXIST (+)');
3968:
3969: END find_if_volhist_bkpts_exist;
3970:
3971: /*===========================================================================+
4003: l_date DATE;
4004:
4005: BEGIN
4006:
4007: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CONSTR_EXIST (+)');
4008:
4009: l_date := NVL(p_term_date,(TO_DATE('01/01/1776','mm/dd/yyyy')));
4010:
4011: SELECT 1
4021: );
4022:
4023: RETURN l_constr_exist;
4024:
4025: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CONSTR_EXIST (-)');
4026:
4027: EXCEPTION
4028:
4029: WHEN OTHERS THEN
4064: l_abat_exist NUMBER;
4065:
4066: BEGIN
4067:
4068: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_ABAT_DEF_EXIST (+)');
4069:
4070: SELECT 1
4071: INTO l_abat_exist
4072: FROM dual
4076: );
4077:
4078: RETURN l_abat_exist;
4079:
4080: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_ABAT_DEF_EXIST (-)');
4081:
4082: EXCEPTION
4083:
4084: WHEN OTHERS THEN
4120: l_constr_exist NUMBER;
4121:
4122: BEGIN
4123:
4124: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CONSTR_DEF_EXIST (+)');
4125:
4126: SELECT 1
4127: INTO l_constr_exist
4128: FROM dual
4132: );
4133:
4134: RETURN l_constr_exist;
4135:
4136: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_CONSTR_DEF_EXIST (-)');
4137:
4138: EXCEPTION
4139:
4140: WHEN OTHERS THEN
4172: l_deduct_exist NUMBER;
4173:
4174: BEGIN
4175:
4176: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_DEDUCT_EXIST (+)');
4177:
4178: SELECT 1
4179: INTO l_deduct_exist
4180: FROM dual
4184: );
4185:
4186: RETURN l_deduct_exist;
4187:
4188: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_DEDUCT_EXIST (-)');
4189:
4190: EXCEPTION
4191:
4192: WHEN OTHERS THEN
4224: PROCEDURE LOCK_ROW_EXCEPTION (p_column_name IN VARCHAR2,
4225: p_new_value IN VARCHAR2)
4226: IS
4227: BEGIN
4228: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION (+)');
4229:
4230: fnd_message.set_name ('PN','PN_RECORD_CHANGED');
4231: fnd_message.set_token ('COLUMN_NAME',p_column_name);
4232: fnd_message.set_token ('NEW_VALUE',p_new_value);
4231: fnd_message.set_token ('COLUMN_NAME',p_column_name);
4232: fnd_message.set_token ('NEW_VALUE',p_new_value);
4233: app_exception.raise_exception;
4234:
4235: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION (-)');
4236: END lock_row_exception;
4237:
4238: /*===========================================================================+
4239: | FUNCTION
4296: l_reporting_periods NUMBER;
4297:
4298: BEGIN
4299:
4300: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_REPORTING_PERIODS (+)');
4301:
4302: SELECT count(GRP_START_DATE)
4303: INTO l_reporting_periods
4304: FROM pn_var_grp_dates_all
4305: WHERE period_id = p_period_id;
4306:
4307: RETURN l_reporting_periods;
4308:
4309: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_REPORTING_PERIODS (-)');
4310:
4311: EXCEPTION
4312:
4313: WHEN OTHERS THEN
4344: l_reporting_periods NUMBER;
4345:
4346: BEGIN
4347:
4348: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_REPORTING_PERIODS (+)');
4349:
4350: IF p_freq_code = 'MON' THEN
4351:
4352: RETURN 12;
4364: RETURN 1;
4365:
4366: END IF;
4367:
4368: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_REPORTING_PERIODS (-)');
4369:
4370: RETURN l_reporting_periods;
4371:
4372: EXCEPTION
4415: l_base_rent NUMBER;
4416:
4417: BEGIN
4418:
4419: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CALCULATE_BASE_RENT (+)');
4420:
4421: IF (p_base_rent_type = 'ROLLING') THEN
4422:
4423: SELECT SUM(item.ACTUAL_AMOUNT)
4467: AND term.currency_code = var.currency_code;
4468:
4469: END IF;
4470:
4471: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.CALCULATE_BASE_RENT (-)');
4472:
4473: RETURN l_base_rent;
4474:
4475: EXCEPTION
4520: l_grp_date_info GRP_DATE_INFO_REC ;
4521:
4522: BEGIN
4523:
4524: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.GET_GRP_DATE_INFO (+)');
4525:
4526: SELECT grp_date_id,
4527: grp_start_date,
4528: grp_end_date,
4542: AND grp_end_date >= p_start_date
4543: AND grp_end_date >= p_end_date
4544: AND rownum = 1;
4545:
4546: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.GET_GRP_DATE_INFO (-)');
4547:
4548: RETURN l_grp_date_info;
4549:
4550: EXCEPTION
4590: l_proration_factor PRORATION_FACTOR_REC ;
4591:
4592: BEGIN
4593:
4594: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.GET_PRORATION_FACTOR (+)');
4595:
4596: SELECT
4597: (p1.end_date-p1.start_date)+1 first_period_pro_days,
4598: p1.proration_factor first_period_gl_days,
4621: AND g2.grp_date_id = (SELECT max(grp_date_id)
4622: FROM pn_var_grp_dates
4623: WHERE var_rent_id = p_var_rent_id);
4624:
4625: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.GET_PRORATION_FACTOR (-)');
4626:
4627: RETURN l_proration_factor;
4628:
4629: EXCEPTION
4665: l_bkptshd_exists NUMBER;
4666:
4667: BEGIN
4668:
4669: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTSHD_EXIST (+)');
4670:
4671: SELECT 1
4672: INTO l_bkptshd_exists
4673: FROM dual
4674: WHERE EXISTS ( SELECT head.bkpt_header_id
4675: FROM pn_var_bkpts_head_ALL head
4676: WHERE head.line_item_id = p_line_item_id);
4677:
4678: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTSHD_EXIST (-)');
4679:
4680: RETURN l_bkptshd_exists;
4681:
4682: EXCEPTION
4716: l_bkptsdet_exist NUMBER;
4717:
4718: BEGIN
4719:
4720: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTSDET_EXIST (+)');
4721:
4722: SELECT 1
4723: INTO l_bkptsdet_exist
4724: FROM dual
4725: WHERE EXISTS ( SELECT det.bkpt_detail_id
4726: FROM pn_var_bkpts_det_ALL det
4727: WHERE det.bkpt_header_id = p_bkpt_header_id);
4728:
4729: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTSDET_EXIST (-)');
4730:
4731: RETURN l_bkptsdet_exist;
4732:
4733: EXCEPTION
4784: l_exported NUMBER;
4785:
4786: BEGIN
4787:
4788: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_EXPORTED (+)');
4789:
4790: IF p_block IN('SUMMARY_FDR_BLK','VARENT_DATES_BLK') THEN
4791:
4792: SELECT 1
4885:
4886:
4887: END IF;
4888:
4889: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_EXPORTED (-)');
4890:
4891: RETURN l_exported;
4892:
4893: EXCEPTION
4928: l_exported NUMBER;
4929:
4930: BEGIN
4931: NULL;
4932: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_EXPORTED (+)');
4933: SELECT 1
4934: INTO l_exported
4935: FROM dual
4936: WHERE EXISTS ( SELECT vol_hist_id
4938: WHERE vol_hist_id = p_id
4939: AND forecasted_exp_code = 'Y'
4940: );
4941:
4942: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_EXPORTED (-)');
4943:
4944: RETURN l_exported;
4945:
4946: EXCEPTION
4986: l_status VARCHAR2(30);
4987:
4988: BEGIN
4989:
4990: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_STATUS (+)');
4991:
4992: SELECT 'COMPLETE'
4993: INTO l_status
4994: FROM dual
5008: (actual_exp_code = 'N' AND FORECASTED_exp_code ='Y' AND variance_exp_code = 'Y')
5009: )
5010: );
5011:
5012: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_STATUS (-)');
5013:
5014: RETURN l_status;
5015:
5016: EXCEPTION
5071: l_adjust_hist_exists NUMBER := NULL;
5072:
5073: BEGIN
5074:
5075: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_ADJUST_HIST_EXISTS (+)');
5076:
5077: SELECT 1
5078: INTO l_adjust_hist_exists
5079: FROM dual
5082: WHERE inv1.period_id = p_period_id
5083: AND (NVL(inv1.adjust_num,0) <> 0 OR
5084: NVL(inv1.true_up_amt,0) <> 0 ));
5085:
5086: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_ADJUST_HIST_EXISTS (-)');
5087:
5088: RETURN l_adjust_hist_exists;
5089:
5090: EXCEPTION
5138: l_term_exISts VARCHAR2(1) := 'N';
5139:
5140: BEGIN
5141:
5142: pnp_debug_pkg.debug( 'pn_var_rent_pkg.approved_term_exist (+): ');
5143:
5144: FOR chk_term_cur_rec IN chk_term_cur LOOP
5145: l_term_exists := chk_term_cur_rec.term_exists;
5146: END LOOP;
5144: FOR chk_term_cur_rec IN chk_term_cur LOOP
5145: l_term_exists := chk_term_cur_rec.term_exists;
5146: END LOOP;
5147:
5148: pnp_debug_pkg.debug( 'pn_var_rent_pkg.approved_term_exist (-): ');
5149:
5150: RETURN l_term_exists;
5151:
5152: END approved_term_exist;
5174: l_bkptsdet_exISt NUMBER := NULL;
5175:
5176: BEGIN
5177:
5178: pnp_debug_pkg.debug( 'pn_var_rent_pkg.delet_INv_summ(+): ');
5179:
5180: OPEN c_exist;
5181: LOOP
5182:
5232: IF SQL%NOTFOUND THEN
5233: NULL;
5234: END IF;
5235:
5236: pnp_debug_pkg.debug( 'pn_var_rent_pkg.delet_INv_summ(-): ');
5237:
5238: END delete_inv_summ;
5239:
5240: /*===========================================================================+
5270: l_vol_ready_for_adjust NUMBER := NULL;
5271:
5272: BEGIN
5273:
5274: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_VOL_READY_FOR_ADJUST (+)');
5275:
5276: IF p_invoice_on = 'ACTUAL' THEN
5277:
5278: SELECT 1
5302: AND grp.grp_date_id = vh.grp_date_id));
5303:
5304: END IF;
5305:
5306: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_VOL_READY_FOR_ADJUST (-)');
5307:
5308: RETURN l_vol_ready_for_adjust;
5309:
5310: EXCEPTION
5360: TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5361: l_payment_term_id number_tbl_type;
5362:
5363: BEGIN
5364: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_LOCATION_FOR_VR_TERMS (+)');
5365:
5366: OPEN C_UPD_TERMS;
5367: LOOP
5368: FETCH C_UPD_TERMS
5376: EXIT WHEN C_UPD_TERMS%NOTFOUND;
5377:
5378: END LOOP;
5379:
5380: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_LOCATION_FOR_VR_TERMS (-)');
5381:
5382: -- initialize api return status to success
5383: p_return_status := FND_API.G_RET_STS_SUCCESS;
5384:
6332: -- to be created
6333:
6334: FOR var_rent_dates_rec in var_rent_dates_cur
6335: LOOP
6336: pnp_debug_pkg.debug('inside var_rent_dates_rec loop');
6337: generate_cal_periods_tbl(p_var_rent_dates_rec => var_rent_dates_rec ,
6338: p_start_date => p_start_date,
6339: p_end_date => p_end_date,
6340: p_extension_end_date => p_extension_end_date,
6401: l_partial_period := 'Y';
6402: ELSE
6403: l_partial_period := 'N';
6404: END IF;
6405: /*pnp_debug_pkg.debug(' v_new_periods_tbl(i).start_date:'||v_new_periods_tbl(i).start_date);
6406: pnp_debug_pkg.debug(' v_new_periods_tbl(i).end_date:'||v_new_periods_tbl(i).end_date);
6407: pnp_debug_pkg.debug(' l_partial_period:'||l_partial_period);*/
6408: --Srini End 30-JUL-2004
6409:
6402: ELSE
6403: l_partial_period := 'N';
6404: END IF;
6405: /*pnp_debug_pkg.debug(' v_new_periods_tbl(i).start_date:'||v_new_periods_tbl(i).start_date);
6406: pnp_debug_pkg.debug(' v_new_periods_tbl(i).end_date:'||v_new_periods_tbl(i).end_date);
6407: pnp_debug_pkg.debug(' l_partial_period:'||l_partial_period);*/
6408: --Srini End 30-JUL-2004
6409:
6410: UPDATE pn_var_periods_all
6403: l_partial_period := 'N';
6404: END IF;
6405: /*pnp_debug_pkg.debug(' v_new_periods_tbl(i).start_date:'||v_new_periods_tbl(i).start_date);
6406: pnp_debug_pkg.debug(' v_new_periods_tbl(i).end_date:'||v_new_periods_tbl(i).end_date);
6407: pnp_debug_pkg.debug(' l_partial_period:'||l_partial_period);*/
6408: --Srini End 30-JUL-2004
6409:
6410: UPDATE pn_var_periods_all
6411: SET end_date = v_new_periods_tbl(i).end_date,
6439: WHEN OTHERS THEN
6440: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
6441: fnd_message.set_token('ERR_MSG',sqlerrm);
6442: x_return_status := FND_API.G_RET_STS_ERROR;
6443: pnp_debug_pkg.put_log_msg (fnd_message.get);
6444: --PNP_DEBUG_PKG.disable_file_debug;
6445:
6446: END extend_periods ;
6447:
6440: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
6441: fnd_message.set_token('ERR_MSG',sqlerrm);
6442: x_return_status := FND_API.G_RET_STS_ERROR;
6443: pnp_debug_pkg.put_log_msg (fnd_message.get);
6444: --PNP_DEBUG_PKG.disable_file_debug;
6445:
6446: END extend_periods ;
6447:
6448: /*===========================================================================+
6951: END IF;
6952:
6953: EXCEPTION
6954: WHEN OTHERS THEN
6955: pnp_debug_pkg.debug('error ' || sqlerrm);
6956: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
6957: fnd_message.set_token('ERR_MSG',sqlerrm);
6958: x_return_status := FND_API.G_RET_STS_ERROR;
6959: pnp_debug_pkg.debug (fnd_message.get);
6955: pnp_debug_pkg.debug('error ' || sqlerrm);
6956: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
6957: fnd_message.set_token('ERR_MSG',sqlerrm);
6958: x_return_status := FND_API.G_RET_STS_ERROR;
6959: pnp_debug_pkg.debug (fnd_message.get);
6960: pnp_debug_pkg.put_log_msg (fnd_message.get);
6961: --PNP_DEBUG_PKG.disable_file_debug;
6962: END extend_group_dates;
6963:
6956: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
6957: fnd_message.set_token('ERR_MSG',sqlerrm);
6958: x_return_status := FND_API.G_RET_STS_ERROR;
6959: pnp_debug_pkg.debug (fnd_message.get);
6960: pnp_debug_pkg.put_log_msg (fnd_message.get);
6961: --PNP_DEBUG_PKG.disable_file_debug;
6962: END extend_group_dates;
6963:
6964: /*===========================================================================+
6957: fnd_message.set_token('ERR_MSG',sqlerrm);
6958: x_return_status := FND_API.G_RET_STS_ERROR;
6959: pnp_debug_pkg.debug (fnd_message.get);
6960: pnp_debug_pkg.put_log_msg (fnd_message.get);
6961: --PNP_DEBUG_PKG.disable_file_debug;
6962: END extend_group_dates;
6963:
6964: /*===========================================================================+
6965: | PROCEDURE
7048: END LOOP;
7049: put_log ('PN_VAR_RENT_PKG.create_new_bkpts (-)');
7050: EXCEPTION
7051: WHEN OTHERS THEN
7052: pnp_debug_pkg.debug('error ' || sqlerrm);
7053: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
7054: fnd_message.set_token('ERR_MSG',sqlerrm);
7055: x_return_status := FND_API.G_RET_STS_ERROR;
7056: pnp_debug_pkg.debug (fnd_message.get);
7052: pnp_debug_pkg.debug('error ' || sqlerrm);
7053: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
7054: fnd_message.set_token('ERR_MSG',sqlerrm);
7055: x_return_status := FND_API.G_RET_STS_ERROR;
7056: pnp_debug_pkg.debug (fnd_message.get);
7057: pnp_debug_pkg.put_log_msg (fnd_message.get);
7058: END create_new_bkpts;
7059:
7060:
7053: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
7054: fnd_message.set_token('ERR_MSG',sqlerrm);
7055: x_return_status := FND_API.G_RET_STS_ERROR;
7056: pnp_debug_pkg.debug (fnd_message.get);
7057: pnp_debug_pkg.put_log_msg (fnd_message.get);
7058: END create_new_bkpts;
7059:
7060:
7061: FUNCTION GET_PRORATION_RULE(p_var_rent_id IN NUMBER DEFAULT NULL,
7130: l_index NUMBER :=0;
7131: l_start_date DATE ;
7132: l_end_date DATE ;
7133: BEGIN
7134: pnp_debug_pkg.debug('generate_cal periodd(+)');
7135: pnp_debug_pkg.debug('use gl calendar' || p_var_rent_dates_rec.use_gl_calendar);
7136: IF NVL(p_var_rent_dates_rec.use_gl_calendar,'N') = 'Y' THEN
7137: FOR cal_periods_rec in cal_periods_cur(
7138: p_var_rent_dates_rec.gl_period_set_name,
7131: l_start_date DATE ;
7132: l_end_date DATE ;
7133: BEGIN
7134: pnp_debug_pkg.debug('generate_cal periodd(+)');
7135: pnp_debug_pkg.debug('use gl calendar' || p_var_rent_dates_rec.use_gl_calendar);
7136: IF NVL(p_var_rent_dates_rec.use_gl_calendar,'N') = 'Y' THEN
7137: FOR cal_periods_rec in cal_periods_cur(
7138: p_var_rent_dates_rec.gl_period_set_name,
7139: p_var_rent_dates_rec.period_type,
7146: x_cal_periods_tbl(l_index).end_date := cal_periods_rec.end_date ;
7147: END LOOP;
7148: ELSE
7149: l_start_date := p_start_date;
7150: pnp_debug_pkg.debug ('l_end_date' || l_end_date);
7151: pnp_debug_pkg.debug ('l_start_date' || l_start_date);
7152: l_end_date := ADD_MONTHS(p_var_rent_dates_rec.year_start_date , 12) - 1;
7153: WHILE l_start_date < nvl(p_extension_end_date,p_end_date)
7154: LOOP
7147: END LOOP;
7148: ELSE
7149: l_start_date := p_start_date;
7150: pnp_debug_pkg.debug ('l_end_date' || l_end_date);
7151: pnp_debug_pkg.debug ('l_start_date' || l_start_date);
7152: l_end_date := ADD_MONTHS(p_var_rent_dates_rec.year_start_date , 12) - 1;
7153: WHILE l_start_date < nvl(p_extension_end_date,p_end_date)
7154: LOOP
7155: l_index := l_index + 1;
7160: x_cal_periods_tbl(l_index).end_date := l_end_date;
7161: END IF;
7162: l_start_date := l_end_date + 1;
7163: l_end_date := ADD_MONTHS(l_start_date , 12) - 1;
7164: pnp_debug_pkg.debug ('loop l_end_date' || l_end_date);
7165: END LOOP;
7166: END IF;
7167: -- end if of use_gl_calendar
7168: END generate_cal_periods_tbl;
7263: x_group_dates_tbl(l_index).start_date := group_date_rec.start_date;
7264: x_group_dates_tbl(l_index).end_date := group_date_rec.end_date;
7265: END LOOP;
7266: ELSIF p_pn_var_rent_dates_rec.reptg_freq_code = 'QTR' THEN
7267: pnp_debug_pkg.debug('reporting = quarter');
7268: FOR group_date_rec in group_date_qtr(p_period_start_date,
7269: p_period_end_date)
7270: LOOP
7271: l_index := l_index + 1;
7478:
7479: l_dummy VARCHAR2(1) ;
7480: BEGIN
7481:
7482: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7483: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7484: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7485: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7486: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7479: l_dummy VARCHAR2(1) ;
7480: BEGIN
7481:
7482: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7483: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7484: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7485: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7486: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7487: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7480: BEGIN
7481:
7482: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7483: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7484: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7485: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7486: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7487: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7488:
7481:
7482: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7483: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7484: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7485: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7486: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7487: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7488:
7489: x_return_status := FALSE;
7482: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7483: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7484: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7485: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7486: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7487: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7488:
7489: x_return_status := FALSE;
7490: FOR header_rec in header_cur LOOP
7483: pnp_debug_pkg.debug('p_line_default_id =' || p_line_default_id);
7484: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7485: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7486: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7487: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7488:
7489: x_return_status := FALSE;
7490: FOR header_rec in header_cur LOOP
7491:
7489: x_return_status := FALSE;
7490: FOR header_rec in header_cur LOOP
7491:
7492:
7493: pnp_debug_pkg.debug(' header_rec.bkhd_end_date = '|| to_char(header_rec.bkhd_end_date,'MM/DD/RRRR'));
7494: IF header_rec.bkhd_end_date = p_end_date THEN
7495: pnp_debug_pkg.debug('HEADER = TRUE');
7496: x_return_status := TRUE;
7497: ELSE
7491:
7492:
7493: pnp_debug_pkg.debug(' header_rec.bkhd_end_date = '|| to_char(header_rec.bkhd_end_date,'MM/DD/RRRR'));
7494: IF header_rec.bkhd_end_date = p_end_date THEN
7495: pnp_debug_pkg.debug('HEADER = TRUE');
7496: x_return_status := TRUE;
7497: ELSE
7498: l_dummy := NULL;
7499: OPEN exist_next_header_date ( header_rec.bkhd_end_date) ;
7507: END IF;
7508: END IF;
7509:
7510: FOR detail_rec in detail_cur (header_rec.bkhd_default_id) LOOP
7511: pnp_debug_pkg.debug(' detail_rec.bkdt_end_date = '|| to_char(detail_rec.bkdt_end_date,'MM/DD/RRRR'));
7512: IF detail_rec.bkdt_end_date = header_rec.bkhd_end_date THEN
7513: pnp_debug_pkg.debug('DETAIL = TRUE');
7514: x_return_status := TRUE;
7515: ELSE
7509:
7510: FOR detail_rec in detail_cur (header_rec.bkhd_default_id) LOOP
7511: pnp_debug_pkg.debug(' detail_rec.bkdt_end_date = '|| to_char(detail_rec.bkdt_end_date,'MM/DD/RRRR'));
7512: IF detail_rec.bkdt_end_date = header_rec.bkhd_end_date THEN
7513: pnp_debug_pkg.debug('DETAIL = TRUE');
7514: x_return_status := TRUE;
7515: ELSE
7516: l_dummy := NULL;
7517: OPEN exist_next_detail_date ( header_rec.bkhd_default_id,
7585:
7586: l_dummy VARCHAR2(1) ;
7587: BEGIN
7588:
7589: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7590: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7591: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7592: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7593: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7586: l_dummy VARCHAR2(1) ;
7587: BEGIN
7588:
7589: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7590: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7591: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7592: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7593: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7594: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7587: BEGIN
7588:
7589: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7590: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7591: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7592: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7593: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7594: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7595:
7588:
7589: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7590: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7591: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7592: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7593: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7594: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7595:
7596: x_return_status := FALSE;
7589: pnp_debug_pkg.debug('p_var_rent_id = '|| p_var_rent_id);
7590: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7591: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7592: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7593: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7594: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7595:
7596: x_return_status := FALSE;
7597: FOR header_rec in header_cur LOOP
7590: pnp_debug_pkg.debug('p_line_item_id =' || p_line_item_id);
7591: pnp_debug_pkg.debug('p_start_date = '|| p_start_date);
7592: pnp_debug_pkg.debug('p_end_date = '|| p_end_date);
7593: pnp_debug_pkg.debug(' char p_end_date = '|| to_char(p_end_date,'MM/DD/YYYY'));
7594: pnp_debug_pkg.debug(' RR p_end_date = '|| to_char(p_end_date,'MM/DD/RRRR'));
7595:
7596: x_return_status := FALSE;
7597: FOR header_rec in header_cur LOOP
7598:
7596: x_return_status := FALSE;
7597: FOR header_rec in header_cur LOOP
7598:
7599:
7600: pnp_debug_pkg.debug(' header_rec.bkhd_end_date = '|| to_char(header_rec.bkhd_end_date,'MM/DD/RRRR'));
7601: IF header_rec.bkhd_end_date = p_end_date THEN
7602: pnp_debug_pkg.debug('HEADER = TRUE');
7603: x_return_status := TRUE;
7604: ELSE
7598:
7599:
7600: pnp_debug_pkg.debug(' header_rec.bkhd_end_date = '|| to_char(header_rec.bkhd_end_date,'MM/DD/RRRR'));
7601: IF header_rec.bkhd_end_date = p_end_date THEN
7602: pnp_debug_pkg.debug('HEADER = TRUE');
7603: x_return_status := TRUE;
7604: ELSE
7605: l_dummy := NULL;
7606: OPEN exist_next_header_date ( header_rec.bkhd_end_date) ;
7614: END IF;
7615: END IF;
7616:
7617: FOR detail_rec in detail_cur (header_rec.bkpt_header_id) LOOP
7618: pnp_debug_pkg.debug(' detail_rec.bkpt_end_date = '|| to_char(detail_rec.bkpt_end_date,'MM/DD/RRRR'));
7619: IF detail_rec.bkpt_end_Date = header_rec.bkhd_end_date THEN
7620: pnp_debug_pkg.debug('DETAIL = TRUE');
7621: x_return_status := TRUE;
7622: ELSE
7616:
7617: FOR detail_rec in detail_cur (header_rec.bkpt_header_id) LOOP
7618: pnp_debug_pkg.debug(' detail_rec.bkpt_end_date = '|| to_char(detail_rec.bkpt_end_date,'MM/DD/RRRR'));
7619: IF detail_rec.bkpt_end_Date = header_rec.bkhd_end_date THEN
7620: pnp_debug_pkg.debug('DETAIL = TRUE');
7621: x_return_status := TRUE;
7622: ELSE
7623: l_dummy := NULL;
7624: OPEN exist_next_detail_date ( header_rec.bkpt_header_id,
7678: l_bkpts_exists NUMBER;
7679:
7680: BEGIN
7681:
7682: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTS_SETUP_EXISTS (+)');
7683:
7684: /*SELECT 1
7685: INTO l_bkpts_exists
7686: FROM dual
7701: WHEN OTHERS
7702: THEN
7703: RETURN FALSE;
7704:
7705: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_BKPTS_SETUP_EXISTS (-)');
7706:
7707: END FIND_IF_BKPTS_SETUP_EXISTS;
7708:
7709: Procedure put_log(p_string VARCHAR2)
7709: Procedure put_log(p_string VARCHAR2)
7710: IS
7711: BEGIN
7712:
7713: pnp_debug_pkg.debug(p_string);
7714: pnp_debug_pkg.put_log_msg(p_string);
7715:
7716: END;
7717: -- M2M End
7710: IS
7711: BEGIN
7712:
7713: pnp_debug_pkg.debug(p_string);
7714: pnp_debug_pkg.put_log_msg(p_string);
7715:
7716: END;
7717: -- M2M End
7718:
7727: OPEN is_partial_period_cur;
7728: FETCH is_partial_period_cur INTO l_return;
7729: IF is_partial_period_cur%NOTFOUND THEN
7730: CLOSE is_partial_period_cur;
7731: pnp_debug_pkg.debug('Cursor is_partial_period_cur NO_DATA_FOUND');
7732: RAISE NO_DATA_FOUND;
7733: END IF;
7734: CLOSE is_partial_period_cur;
7735: RETURN l_return;
7772: l_freq_code PN_VAR_RENT_DATES_ALL.REPTG_FREQ_CODE%TYPE;
7773:
7774: BEGIN
7775:
7776: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DETERMINE_FREQUENCY (+)');
7777:
7778: SELECT X_VAR_RENT_END_DATE - X_VAR_RENT_START_DATE + 1
7779: INTO l_days
7780: FROM dual;
7797: WHEN OTHERS
7798: THEN
7799: RETURN NULL;
7800:
7801: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DETERMINE_FREQUENCY (-)');
7802:
7803: END DETERMINE_FREQUENCY;
7804:
7805: PROCEDURE update_bkpt_details(p_var_rent_id IN NUMBER,
7852: PROCEDURE delete_vr_setup ( p_var_rent_id IN NUMBER
7853: ,p_new_termn_date IN DATE)
7854: IS
7855: BEGIN
7856: pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_vr_setup (+)');
7857:
7858: /* Delete the data from main tables */
7859: DELETE pn_var_vol_hist_all
7860: WHERE start_date > p_new_termn_date
7916: DELETE pn_var_abat_defaults_all
7917: WHERE start_date > p_new_termn_date
7918: AND var_rent_id = p_var_rent_id;
7919:
7920: pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_vr_setup (-)');
7921:
7922: END delete_vr_setup;
7923:
7924: -------------------------------------------------------------------------------
8068: l_variance_exp_code VARCHAR2(1) := 'N';
8069: l_var_term_status VARCHAR2(1);
8070:
8071: BEGIN
8072: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (+)');
8073: pnp_debug_pkg.debug (' Parameters :');
8074: pnp_debug_pkg.debug (' ------------------------------------------- :');
8075: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8076: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8069: l_var_term_status VARCHAR2(1);
8070:
8071: BEGIN
8072: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (+)');
8073: pnp_debug_pkg.debug (' Parameters :');
8074: pnp_debug_pkg.debug (' ------------------------------------------- :');
8075: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8076: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8077: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8070:
8071: BEGIN
8072: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (+)');
8073: pnp_debug_pkg.debug (' Parameters :');
8074: pnp_debug_pkg.debug (' ------------------------------------------- :');
8075: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8076: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8077: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8078: pnp_debug_pkg.debug (' ------------------------------------------- :');
8071: BEGIN
8072: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (+)');
8073: pnp_debug_pkg.debug (' Parameters :');
8074: pnp_debug_pkg.debug (' ------------------------------------------- :');
8075: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8076: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8077: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8078: pnp_debug_pkg.debug (' ------------------------------------------- :');
8079:
8072: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (+)');
8073: pnp_debug_pkg.debug (' Parameters :');
8074: pnp_debug_pkg.debug (' ------------------------------------------- :');
8075: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8076: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8077: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8078: pnp_debug_pkg.debug (' ------------------------------------------- :');
8079:
8080: /* Delete draft terms for invoices for all periods which lie after the new termination date */
8073: pnp_debug_pkg.debug (' Parameters :');
8074: pnp_debug_pkg.debug (' ------------------------------------------- :');
8075: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8076: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8077: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8078: pnp_debug_pkg.debug (' ------------------------------------------- :');
8079:
8080: /* Delete draft terms for invoices for all periods which lie after the new termination date */
8081: DELETE pn_payment_terms_all
8074: pnp_debug_pkg.debug (' ------------------------------------------- :');
8075: pnp_debug_pkg.debug (' p_var_rent_id = '|| p_var_rent_id);
8076: pnp_debug_pkg.debug (' p_new_termn_date = '|| p_new_termn_date);
8077: pnp_debug_pkg.debug (' p_old_termn_date = '|| p_old_termn_date);
8078: pnp_debug_pkg.debug (' ------------------------------------------- :');
8079:
8080: /* Delete draft terms for invoices for all periods which lie after the new termination date */
8081: DELETE pn_payment_terms_all
8082: WHERE status = 'DRAFT'
8099: FROM pn_var_periods_all
8100: WHERE var_rent_id = p_var_rent_id
8101: AND start_date > p_new_termn_date);
8102:
8103: pnp_debug_pkg.debug ('Loop through the period which start after new termination date');
8104: /* 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 */
8105: FOR period_rec IN period_cur LOOP
8106:
8107: pnp_debug_pkg.debug ('period id = '||period_rec.period_id);
8103: pnp_debug_pkg.debug ('Loop through the period which start after new termination date');
8104: /* 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 */
8105: FOR period_rec IN period_cur LOOP
8106:
8107: pnp_debug_pkg.debug ('period id = '||period_rec.period_id);
8108: l_invoice_exists := 'N';
8109:
8110: /* Check if the period has an invoice associated with it */
8111: FOR invoice_rec IN invoice_cur (period_rec.period_id) LOOP
8108: l_invoice_exists := 'N';
8109:
8110: /* Check if the period has an invoice associated with it */
8111: FOR invoice_rec IN invoice_cur (period_rec.period_id) LOOP
8112: pnp_debug_pkg.debug ('invoice exists ...');
8113: l_invoice_exists := 'Y';
8114: END LOOP;
8115:
8116: IF l_invoice_exists = 'Y' THEN
8114: END LOOP;
8115:
8116: IF l_invoice_exists = 'Y' THEN
8117: /* If invoice exists in this period then exit from the loop as you need not delete any more periods */
8118: pnp_debug_pkg.debug ('exiting ...');
8119: EXIT;
8120: ELSE
8121:
8122: pnp_debug_pkg.debug ('deleting group date and periods ...');
8118: pnp_debug_pkg.debug ('exiting ...');
8119: EXIT;
8120: ELSE
8121:
8122: pnp_debug_pkg.debug ('deleting group date and periods ...');
8123: /* Delete group dates and periods for which there exists no invoice */
8124: DELETE pn_var_grp_dates_all
8125: WHERE period_id = period_rec.period_id;
8126:
8129: END IF;
8130:
8131: END LOOP;
8132:
8133: pnp_debug_pkg.debug ('Loop through the invoices which are not deleted to create negative terms for each of the approved term ...');
8134: /* Loop through the invoices which are not deleted to create negative terms for each of the approved term */
8135: FOR invoice_date_rec IN invoice_date_cur LOOP
8136:
8137: FOR max_adjust_num_rec IN max_adjust_num_cur(invoice_date_rec.period_id, invoice_date_rec.invoice_date)
8164: ELSE
8165: l_var_term_status := rent_inv_rec.variance_term_status;
8166: END IF;
8167:
8168: pnp_debug_pkg.debug ('Actual_invoiced_amount ...'||l_actual_invoiced_amount);
8169: pnp_debug_pkg.debug ('var rent inv id ...'||rent_inv_rec.var_rent_inv_id);
8170:
8171: /* Set var rent inv id to null before inserting a row */
8172: l_var_rent_inv_id := NULL;
8165: l_var_term_status := rent_inv_rec.variance_term_status;
8166: END IF;
8167:
8168: pnp_debug_pkg.debug ('Actual_invoiced_amount ...'||l_actual_invoiced_amount);
8169: pnp_debug_pkg.debug ('var rent inv id ...'||rent_inv_rec.var_rent_inv_id);
8170:
8171: /* Set var rent inv id to null before inserting a row */
8172: l_var_rent_inv_id := NULL;
8173: l_rowid := NULL;
8221: x_true_up_status => rent_inv_rec.true_up_status,
8222: x_true_up_exp_code => rent_inv_rec.true_up_exp_code,
8223: x_org_id => rent_inv_rec.org_id);
8224:
8225: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
8226: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||rent_inv_rec.var_rent_inv_id);
8227:
8228: /*
8229: FOR payment_rec IN payment_cur(invoice_date_rec.invoice_date) LOOP
8222: x_true_up_exp_code => rent_inv_rec.true_up_exp_code,
8223: x_org_id => rent_inv_rec.org_id);
8224:
8225: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
8226: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||rent_inv_rec.var_rent_inv_id);
8227:
8228: /*
8229: FOR payment_rec IN payment_cur(invoice_date_rec.invoice_date) LOOP
8230: pn_variable_term_pkg.create_reversal_terms(p_payment_term_id => payment_rec.payment_term_id
8244: SET status = pn_var_rent_pkg.status
8245: WHERE var_rent_id = p_var_rent_id
8246: AND start_date > p_new_termn_date;
8247:
8248: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (-)');
8249:
8250: EXCEPTION
8251: WHEN OTHERS THEN
8252: pnp_debug_pkg.debug (' Remove Later Periods Error:'||SQLERRM);
8248: pnp_debug_pkg.debug ('pn_var_rent_pkg.remove_later_periods (-)');
8249:
8250: EXCEPTION
8251: WHEN OTHERS THEN
8252: pnp_debug_pkg.debug (' Remove Later Periods Error:'||SQLERRM);
8253: x_return_status := FND_API.G_RET_STS_ERROR;
8254: x_return_message := SQLERRM;
8255:
8256: END remove_later_periods ;
8358: l_inv_sch_date DATE := NULL;
8359: l_period_id NUMBER := 0;
8360:
8361: BEGIN
8362: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_setup (+)');
8363: pnp_debug_pkg.debug ('p_var_rent_id ....'||p_var_rent_id);
8364: pnp_debug_pkg.debug ('p_period_id ......'||p_period_id);
8365: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8366:
8359: l_period_id NUMBER := 0;
8360:
8361: BEGIN
8362: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_setup (+)');
8363: pnp_debug_pkg.debug ('p_var_rent_id ....'||p_var_rent_id);
8364: pnp_debug_pkg.debug ('p_period_id ......'||p_period_id);
8365: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8366:
8367: FOR rec IN vol_his_cur LOOP
8360:
8361: BEGIN
8362: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_setup (+)');
8363: pnp_debug_pkg.debug ('p_var_rent_id ....'||p_var_rent_id);
8364: pnp_debug_pkg.debug ('p_period_id ......'||p_period_id);
8365: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8366:
8367: FOR rec IN vol_his_cur LOOP
8368: PN_VAR_VOL_HIST_PKG.MODIFY_ROW( X_VOL_HIST_ID => rec.vol_hist_id,
8361: BEGIN
8362: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_setup (+)');
8363: pnp_debug_pkg.debug ('p_var_rent_id ....'||p_var_rent_id);
8364: pnp_debug_pkg.debug ('p_period_id ......'||p_period_id);
8365: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8366:
8367: FOR rec IN vol_his_cur LOOP
8368: PN_VAR_VOL_HIST_PKG.MODIFY_ROW( X_VOL_HIST_ID => rec.vol_hist_id,
8369: X_LINE_ITEM_ID => rec.line_item_id,
8499: Partial_period = 'Y'
8500: WHERE var_rent_id = p_var_rent_id
8501: AND period_id = p_period_id;
8502:
8503: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_setup (-)');
8504: END;
8505:
8506: -------------------------------------------------------------------------------
8507: -- NAME : early_terminate_period
8612: l_variance_exp_code VARCHAR2(1) := 'N';
8613: l_var_term_status VARCHAR2(1);
8614:
8615: BEGIN
8616: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (+)');
8617: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8618: pnp_debug_pkg.debug ('p_period_id ....'||p_period_id);
8619: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8620: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8613: l_var_term_status VARCHAR2(1);
8614:
8615: BEGIN
8616: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (+)');
8617: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8618: pnp_debug_pkg.debug ('p_period_id ....'||p_period_id);
8619: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8620: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8621:
8614:
8615: BEGIN
8616: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (+)');
8617: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8618: pnp_debug_pkg.debug ('p_period_id ....'||p_period_id);
8619: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8620: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8621:
8622:
8615: BEGIN
8616: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (+)');
8617: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8618: pnp_debug_pkg.debug ('p_period_id ....'||p_period_id);
8619: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8620: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8621:
8622:
8623: /* Delete draft terms for invoices for this period */
8616: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (+)');
8617: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8618: pnp_debug_pkg.debug ('p_period_id ....'||p_period_id);
8619: pnp_debug_pkg.debug ('p_new_termn_date ...'||p_new_termn_date);
8620: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8621:
8622:
8623: /* Delete draft terms for invoices for this period */
8624: DELETE pn_payment_terms_all
8633: AND actual_exp_code = 'N'
8634: AND forecasted_exp_code = 'N'
8635: AND variance_exp_code = 'N';
8636:
8637: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8638:
8639: pnp_debug_pkg.debug ('Loop for invoices that start after new termination date for this period and create adjustment terms for them');
8640: /* Loop for invoices that start after new termination date for this period and create adjustment terms for them */
8641: FOR invoice_date_rec IN invoice_date_cur LOOP
8635: AND variance_exp_code = 'N';
8636:
8637: pnp_debug_pkg.debug ('p_old_termn_date ....'||p_old_termn_date);
8638:
8639: pnp_debug_pkg.debug ('Loop for invoices that start after new termination date for this period and create adjustment terms for them');
8640: /* Loop for invoices that start after new termination date for this period and create adjustment terms for them */
8641: FOR invoice_date_rec IN invoice_date_cur LOOP
8642:
8643: FOR max_adjust_num_rec IN max_adjust_num_cur(p_period_id, invoice_date_rec.invoice_date)
8670: ELSE
8671: l_var_term_status := invoice_rec.variance_term_status;
8672: END IF;
8673:
8674: pnp_debug_pkg.debug ('Actual_invoiced_amount ...'||l_actual_invoiced_amount);
8675: pnp_debug_pkg.debug ('var rent inv id ...'||invoice_rec.var_rent_inv_id);
8676: /* Set var rent inv id to null before inserting a row */
8677: l_var_rent_inv_id := NULL;
8678: l_rowid := NULL;
8671: l_var_term_status := invoice_rec.variance_term_status;
8672: END IF;
8673:
8674: pnp_debug_pkg.debug ('Actual_invoiced_amount ...'||l_actual_invoiced_amount);
8675: pnp_debug_pkg.debug ('var rent inv id ...'||invoice_rec.var_rent_inv_id);
8676: /* Set var rent inv id to null before inserting a row */
8677: l_var_rent_inv_id := NULL;
8678: l_rowid := NULL;
8679:
8725: x_true_up_status => invoice_rec.true_up_status,
8726: x_true_up_exp_code => invoice_rec.true_up_exp_code,
8727: x_org_id => invoice_rec.org_id);
8728:
8729: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
8730: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||invoice_rec.var_rent_inv_id);
8731: /*
8732:
8733: FOR payment_rec IN payment_cur(invoice_date_rec.invoice_date) LOOP
8726: x_true_up_exp_code => invoice_rec.true_up_exp_code,
8727: x_org_id => invoice_rec.org_id);
8728:
8729: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
8730: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||invoice_rec.var_rent_inv_id);
8731: /*
8732:
8733: FOR payment_rec IN payment_cur(invoice_date_rec.invoice_date) LOOP
8734: pn_variable_term_pkg.create_reversal_terms(p_payment_term_id => payment_rec.payment_term_id
8742: END IF;
8743:
8744: END LOOP;
8745:
8746: pnp_debug_pkg.debug ('pn_var_rent_pkg.early_terminate_period (-)');
8747:
8748: END early_terminate_period;
8749:
8750:
8750:
8751: PROCEDURE delete_var_agreement ( p_var_rent_id IN NUMBER)
8752: IS
8753: BEGIN
8754: pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_var_agreement (+)');
8755:
8756: /* Delete the data from main tables */
8757: DELETE pn_var_vol_hist_all
8758: WHERE period_id IN (SELECT period_id
8815:
8816: DELETE pn_var_rents_all
8817: WHERE var_rent_id = p_var_rent_id;
8818:
8819: pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_var_agreement (-)');
8820:
8821: END delete_var_agreement;
8822:
8823: -------------------------------------------------------------------------------
8909: WHERE var_rent_id = p_var_rent_id
8910: AND inv_end_date <= p_new_termn_date;
8911:
8912: BEGIN
8913: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (+)');
8914: pnp_debug_pkg.debug (' Parameters :');
8915: pnp_debug_pkg.debug (' ------------------------------------------- :');
8916: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8917: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8910: AND inv_end_date <= p_new_termn_date;
8911:
8912: BEGIN
8913: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (+)');
8914: pnp_debug_pkg.debug (' Parameters :');
8915: pnp_debug_pkg.debug (' ------------------------------------------- :');
8916: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8917: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8918: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8911:
8912: BEGIN
8913: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (+)');
8914: pnp_debug_pkg.debug (' Parameters :');
8915: pnp_debug_pkg.debug (' ------------------------------------------- :');
8916: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8917: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8918: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8919: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8912: BEGIN
8913: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (+)');
8914: pnp_debug_pkg.debug (' Parameters :');
8915: pnp_debug_pkg.debug (' ------------------------------------------- :');
8916: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8917: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8918: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8919: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8920: pnp_debug_pkg.debug (' ------------------------------------------- :');
8913: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (+)');
8914: pnp_debug_pkg.debug (' Parameters :');
8915: pnp_debug_pkg.debug (' ------------------------------------------- :');
8916: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8917: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8918: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8919: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8920: pnp_debug_pkg.debug (' ------------------------------------------- :');
8921:
8914: pnp_debug_pkg.debug (' Parameters :');
8915: pnp_debug_pkg.debug (' ------------------------------------------- :');
8916: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8917: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8918: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8919: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8920: pnp_debug_pkg.debug (' ------------------------------------------- :');
8921:
8922: /* Loop through the rent agreements whose termination date > new termination date */
8915: pnp_debug_pkg.debug (' ------------------------------------------- :');
8916: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8917: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8918: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8919: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8920: pnp_debug_pkg.debug (' ------------------------------------------- :');
8921:
8922: /* Loop through the rent agreements whose termination date > new termination date */
8923: FOR var_rent_rec IN var_rent_cur LOOP
8916: pnp_debug_pkg.debug ('p_lease_id..'||p_lease_id);
8917: pnp_debug_pkg.debug ('p_var_rent_id ...'||p_var_rent_id);
8918: pnp_debug_pkg.debug ('p_new_termn_date....'||p_new_termn_date);
8919: pnp_debug_pkg.debug ('p_old_termn_date...'||p_old_termn_date);
8920: pnp_debug_pkg.debug (' ------------------------------------------- :');
8921:
8922: /* Loop through the rent agreements whose termination date > new termination date */
8923: FOR var_rent_rec IN var_rent_cur LOOP
8924:
8921:
8922: /* Loop through the rent agreements whose termination date > new termination date */
8923: FOR var_rent_rec IN var_rent_cur LOOP
8924:
8925: pnp_debug_pkg.debug ('**************************************************************');
8926: pnp_debug_pkg.debug ('looping through variable rent ...'||var_rent_rec.var_rent_id);
8927: pnp_debug_pkg.debug ('**************************************************************');
8928:
8929: l_period_exists := 0;
8922: /* Loop through the rent agreements whose termination date > new termination date */
8923: FOR var_rent_rec IN var_rent_cur LOOP
8924:
8925: pnp_debug_pkg.debug ('**************************************************************');
8926: pnp_debug_pkg.debug ('looping through variable rent ...'||var_rent_rec.var_rent_id);
8927: pnp_debug_pkg.debug ('**************************************************************');
8928:
8929: l_period_exists := 0;
8930:
8923: FOR var_rent_rec IN var_rent_cur LOOP
8924:
8925: pnp_debug_pkg.debug ('**************************************************************');
8926: pnp_debug_pkg.debug ('looping through variable rent ...'||var_rent_rec.var_rent_id);
8927: pnp_debug_pkg.debug ('**************************************************************');
8928:
8929: l_period_exists := 0;
8930:
8931: /* Delete Vol hist, Bkpt Details, bkpt headers, lines, deductions, vr summary rows,
8939: p_old_termn_date => p_old_termn_date,
8940: x_return_status => x_return_status,
8941: x_return_message => x_return_message);
8942:
8943: pnp_debug_pkg.debug ('after remove later periods'||p_var_rent_id||'....'||p_new_termn_date);
8944: /* Fetch the period in which new termination date lies */
8945: FOR period_id_rec IN period_id_cur(var_rent_rec.var_rent_id) LOOP
8946:
8947: pnp_debug_pkg.debug ('current period with new termination date ...'||period_id_rec.period_id);
8943: pnp_debug_pkg.debug ('after remove later periods'||p_var_rent_id||'....'||p_new_termn_date);
8944: /* Fetch the period in which new termination date lies */
8945: FOR period_id_rec IN period_id_cur(var_rent_rec.var_rent_id) LOOP
8946:
8947: pnp_debug_pkg.debug ('current period with new termination date ...'||period_id_rec.period_id);
8948: pnp_debug_pkg.debug ('end date for period with new termination date ...'||period_id_rec.end_date);
8949:
8950: /* Check if the new period is partial. Early terminate the setup and process the
8951: period if the new period is partial */
8944: /* Fetch the period in which new termination date lies */
8945: FOR period_id_rec IN period_id_cur(var_rent_rec.var_rent_id) LOOP
8946:
8947: pnp_debug_pkg.debug ('current period with new termination date ...'||period_id_rec.period_id);
8948: pnp_debug_pkg.debug ('end date for period with new termination date ...'||period_id_rec.end_date);
8949:
8950: /* Check if the new period is partial. Early terminate the setup and process the
8951: period if the new period is partial */
8952: IF period_id_rec.end_date <> p_new_termn_date THEN
8967: x_return_message => x_return_message);
8968:
8969: ELSE
8970:
8971: pnp_debug_pkg.debug(' contract the defaults only ...');
8972:
8973: FOR bkdt_defaults_rec IN bkdt_defaults_cur(var_rent_rec.var_rent_id) LOOP
8974: pn_var_bkdt_defaults_pkg.modify_row (x_bkdt_default_id => bkdt_defaults_rec.bkdt_default_id,
8975: x_bkhd_default_id => bkdt_defaults_rec.bkhd_default_id,
9014: END IF;
9015:
9016: END LOOP;
9017:
9018: pnp_debug_pkg.debug ('after for loop');
9019:
9020: /* update the end date of agreement to new termination date */
9021: UPDATE pn_var_rents_all
9022: SET termination_date = p_new_termn_date
9031:
9032: END LOOP;
9033:
9034: /* Loop through the rent agreements whose commencement date > new termination date */
9035: pnp_debug_pkg.debug ( 'lease id is ...'||p_lease_id);
9036:
9037: IF p_lease_id IS NOT NULL THEN
9038: pnp_debug_pkg.debug(' inside if ..');
9039: FOR variable_rent_rec IN variable_rent_cur LOOP
9034: /* Loop through the rent agreements whose commencement date > new termination date */
9035: pnp_debug_pkg.debug ( 'lease id is ...'||p_lease_id);
9036:
9037: IF p_lease_id IS NOT NULL THEN
9038: pnp_debug_pkg.debug(' inside if ..');
9039: FOR variable_rent_rec IN variable_rent_cur LOOP
9040:
9041: l_appr_term_exists := 'N';
9042: pnp_debug_pkg.debug(' var rent id ...'||variable_rent_rec.var_rent_id);
9038: pnp_debug_pkg.debug(' inside if ..');
9039: FOR variable_rent_rec IN variable_rent_cur LOOP
9040:
9041: l_appr_term_exists := 'N';
9042: pnp_debug_pkg.debug(' var rent id ...'||variable_rent_rec.var_rent_id);
9043:
9044: FOR rec IN approved_term_exists_cur(variable_rent_rec.var_rent_id) LOOP
9045: pnp_debug_pkg.debug (' approved term exists ...');
9046: l_appr_term_exists := rec.approve_term_exits;
9041: l_appr_term_exists := 'N';
9042: pnp_debug_pkg.debug(' var rent id ...'||variable_rent_rec.var_rent_id);
9043:
9044: FOR rec IN approved_term_exists_cur(variable_rent_rec.var_rent_id) LOOP
9045: pnp_debug_pkg.debug (' approved term exists ...');
9046: l_appr_term_exists := rec.approve_term_exits;
9047: END LOOP;
9048:
9049: pnp_debug_pkg.debug ('l_appr_term_exists ...'||l_appr_term_exists);
9045: pnp_debug_pkg.debug (' approved term exists ...');
9046: l_appr_term_exists := rec.approve_term_exits;
9047: END LOOP;
9048:
9049: pnp_debug_pkg.debug ('l_appr_term_exists ...'||l_appr_term_exists);
9050:
9051: IF l_appr_term_exists = 'N' THEN
9052: delete_var_agreement(p_var_rent_id => variable_rent_rec.var_rent_id);
9053: END IF;
9054:
9055: END LOOP;
9056: END IF;
9057:
9058: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (-)');
9059:
9060: EXCEPTION
9061: WHEN OTHERS THEN
9062: pnp_debug_pkg.debug ('Process_vr_early_term Error:'||SQLERRM);
9058: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_early_term (-)');
9059:
9060: EXCEPTION
9061: WHEN OTHERS THEN
9062: pnp_debug_pkg.debug ('Process_vr_early_term Error:'||SQLERRM);
9063: x_return_status := FND_API.G_RET_STS_ERROR;
9064: x_return_message := sqlerrm;
9065: END;
9066:
9134: l_ret_code VARCHAR2(250);
9135: l_bkpt_exists VARCHAR2(1);
9136:
9137: BEGIN
9138: pnp_debug_pkg.debug ('pn_var_rent_pkg.extend_defaults (+)');
9139: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9140: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9141: pnp_debug_pkg.debug ('p_new_termn_date ..'||p_new_termn_date);
9142:
9135: l_bkpt_exists VARCHAR2(1);
9136:
9137: BEGIN
9138: pnp_debug_pkg.debug ('pn_var_rent_pkg.extend_defaults (+)');
9139: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9140: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9141: pnp_debug_pkg.debug ('p_new_termn_date ..'||p_new_termn_date);
9142:
9143: UPDATE pn_var_line_defaults_all
9136:
9137: BEGIN
9138: pnp_debug_pkg.debug ('pn_var_rent_pkg.extend_defaults (+)');
9139: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9140: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9141: pnp_debug_pkg.debug ('p_new_termn_date ..'||p_new_termn_date);
9142:
9143: UPDATE pn_var_line_defaults_all
9144: SET line_end_date = p_new_termn_date
9137: BEGIN
9138: pnp_debug_pkg.debug ('pn_var_rent_pkg.extend_defaults (+)');
9139: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9140: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9141: pnp_debug_pkg.debug ('p_new_termn_date ..'||p_new_termn_date);
9142:
9143: UPDATE pn_var_line_defaults_all
9144: SET line_end_date = p_new_termn_date
9145: WHERE var_rent_id = p_var_rent_id
9163: WHERE var_rent_id = p_var_rent_id
9164: AND constr_end_date = p_old_termn_date;
9165:
9166: /* This generates line items, breakpoint header and details in main tables */
9167: pnp_debug_pkg.debug ('calling procedure pn_var_defaults_pkg.create_default_lines ..');
9168: FOR rec IN bkpt_data_exists LOOP
9169: l_bkpt_exists := rec.bkpt_exists;
9170: END LOOP;
9171:
9186: pn_var_defaults_pkg.create_setup_data (x_var_rent_id => p_var_rent_id);
9187: END IF;
9188:
9189: /* This generates constraint records in main tables */
9190: pnp_debug_pkg.debug ('calling procedure pn_var_defaults_pkg.create_default_constraints ..');
9191: FOR rec IN constr_data_exists LOOP
9192: DELETE FROM pn_var_constraints_all
9193: WHERE period_id IN (SELECT period_id
9194: FROM pn_var_periods_all
9196:
9197: pn_var_defaults_pkg.create_default_constraints (x_var_rent_id => p_var_rent_id);
9198: END LOOP;
9199:
9200: pnp_debug_pkg.debug ('pn_var_rent_pkg.extend_defaults (-)');
9201: END;
9202:
9203: -------------------------------------------------------------------------------
9204: -- NAME : Create_setup_exp
9374: l_constrnum NUMBER := NULL;
9375:
9376: BEGIN
9377:
9378: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_setup_exp (+)');
9379: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9380: pnp_debug_pkg.debug ('p_period_id ..'||p_period_id);
9381:
9382: /* Fetch the end date of period which was the last one before expansion */
9375:
9376: BEGIN
9377:
9378: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_setup_exp (+)');
9379: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9380: pnp_debug_pkg.debug ('p_period_id ..'||p_period_id);
9381:
9382: /* Fetch the end date of period which was the last one before expansion */
9383: FOR last_period_rec IN last_period_cur LOOP
9376: BEGIN
9377:
9378: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_setup_exp (+)');
9379: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9380: pnp_debug_pkg.debug ('p_period_id ..'||p_period_id);
9381:
9382: /* Fetch the end date of period which was the last one before expansion */
9383: FOR last_period_rec IN last_period_cur LOOP
9384: l_end_date := last_period_rec.end_date;
9386:
9387: /* Loop through line records of period which was the last one before expansion */
9388: FOR line_rec IN line_cur LOOP
9389:
9390: pnp_debug_pkg.debug ('refernce line record is ..'||line_rec.line_item_id);
9391:
9392: /* Loop through the periods which have been created due to VR expansion to
9393: create setup data for them */
9394: FOR period_rec IN period_cur LOOP
9392: /* Loop through the periods which have been created due to VR expansion to
9393: create setup data for them */
9394: FOR period_rec IN period_cur LOOP
9395:
9396: pnp_debug_pkg.debug ('period for which data is being inserted is ..'||period_rec.period_id);
9397: l_rowid := NULL;
9398: l_line_item_id := NULL;
9399: l_line_item_num := NULL;
9400:
9432: x_agreement_template_id => line_rec.agreement_template_id,
9433: x_line_default_id => line_rec.line_default_id,
9434: x_var_rent_id => p_var_rent_id);
9435:
9436: pnp_debug_pkg.debug ('line item inserted is l_line_item_id ..'||l_line_item_id);
9437: /* Generate the breakpoint header associated with this line item */
9438: FOR bkpt_head_rec IN bkpt_head_cur (line_rec.line_item_id, l_end_date) LOOP
9439: l_rowid := NULL;
9440: l_bkpt_header_id := NULL;
9473: x_last_update_date => sysdate,
9474: x_last_updated_by => NVL(fnd_profile.value('USER_ID'),0),
9475: x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0));
9476:
9477: pnp_debug_pkg.debug ('breakpoint header inserted is l_bkpt_header_id ..'||l_bkpt_header_id);
9478:
9479: /* Generate the breakpoint details associated with this breakpoint header */
9480: FOR bkpt_detail_rec IN bkpt_detail_cur( bkpt_head_rec.bkpt_header_id, l_end_date) LOOP
9481: l_rowid := NULL;
9520: x_last_update_login => nvl(fnd_profile.value('user_id'),0),
9521: x_annual_basis_amount => bkpt_detail_rec.annual_basis_amount
9522: );
9523:
9524: pnp_debug_pkg.debug ('breakpoint detail inserted is l_bkpt_detail_id ..'||l_bkpt_detail_id);
9525:
9526: END LOOP; /* end loop for bkpt detail */
9527:
9528: END LOOP; /* end loop for bkpt header */
9579: END LOOP;
9580:
9581: END LOOP;
9582:
9583: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_setup_exp (-)');
9584: END;
9585:
9586: -------------------------------------------------------------------------------
9587: -- NAME : update_setup_exp
9604: WHERE var_rent_id = p_var_rent_id
9605: AND period_id = p_period_id;
9606:
9607: BEGIN
9608: pnp_debug_pkg.debug ('pn_var_rent_pkg.update_setup_exp (+)');
9609: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9610: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9611:
9612: FOR period_rec IN period_cur LOOP
9605: AND period_id = p_period_id;
9606:
9607: BEGIN
9608: pnp_debug_pkg.debug ('pn_var_rent_pkg.update_setup_exp (+)');
9609: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9610: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9611:
9612: FOR period_rec IN period_cur LOOP
9613:
9606:
9607: BEGIN
9608: pnp_debug_pkg.debug ('pn_var_rent_pkg.update_setup_exp (+)');
9609: pnp_debug_pkg.debug ('p_var_rent_id .. '||p_var_rent_id);
9610: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9611:
9612: FOR period_rec IN period_cur LOOP
9613:
9614: pnp_debug_pkg.debug ('last_period_rec.period_id ...'||p_period_id);
9610: pnp_debug_pkg.debug ('p_old_termn_date ..'||p_old_termn_date);
9611:
9612: FOR period_rec IN period_cur LOOP
9613:
9614: pnp_debug_pkg.debug ('last_period_rec.period_id ...'||p_period_id);
9615: pnp_debug_pkg.debug ('last_period_rec.end_date ....'||period_rec.end_date);
9616:
9617: UPDATE pn_var_bkpts_head_all
9618: SET bkhd_end_date = period_rec.end_date
9611:
9612: FOR period_rec IN period_cur LOOP
9613:
9614: pnp_debug_pkg.debug ('last_period_rec.period_id ...'||p_period_id);
9615: pnp_debug_pkg.debug ('last_period_rec.end_date ....'||period_rec.end_date);
9616:
9617: UPDATE pn_var_bkpts_head_all
9618: SET bkhd_end_date = period_rec.end_date
9619: WHERE var_rent_id = p_var_rent_id
9633: , p_period_id => p_period_id);
9634:
9635: END LOOP;
9636:
9637: pnp_debug_pkg.debug ('pn_var_rent_pkg.update_setup_exp (-)');
9638:
9639: END;
9640:
9641: -------------------------------------------------------------------------------
9714: l_actual_invoiced_amount NUMBER := 0;
9715: l_invoice_inserted BOOLEAN := FALSE;
9716: BEGIN
9717:
9718: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_rev_term_LY_FLY (+) ');
9719:
9720: DELETE pn_payment_terms_all
9721: WHERE var_rent_inv_id IN (SELECT var_rent_inv_id
9722: FROM pn_var_rent_inv_all
9727: DELETE pn_var_rent_inv_all
9728: WHERE period_id = p_last_period_id
9729: AND NVL(actual_exp_code, 'N') <> 'Y';
9730:
9731: pnp_debug_pkg.debug ('Loop for invoices for true up and for last year calculation. ');
9732: /* Loop for invoices for true up and for last year calculation.*/
9733: FOR invoice_date_rec IN invoice_date_cur(p_last_period_id) LOOP
9734:
9735: /* Fetch the maximum adjust number for a particular invoice date */
9801: x_true_up_status => invoice_rec.true_up_status,
9802: x_true_up_exp_code => invoice_rec.true_up_exp_code,
9803: x_org_id => invoice_rec.org_id);
9804:
9805: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
9806: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||invoice_rec.var_rent_inv_id);
9807: /*
9808: FOR payment_rec IN payment_cur(invoice_rec.var_rent_inv_id) LOOP
9809: pn_variable_term_pkg.create_reversal_terms(p_payment_term_id => payment_rec.payment_term_id
9802: x_true_up_exp_code => invoice_rec.true_up_exp_code,
9803: x_org_id => invoice_rec.org_id);
9804:
9805: pnp_debug_pkg.debug ('l_var_rent_inv_id ...'||l_var_rent_inv_id);
9806: pnp_debug_pkg.debug ('For every period, call create_payment_term...'||invoice_rec.var_rent_inv_id);
9807: /*
9808: FOR payment_rec IN payment_cur(invoice_rec.var_rent_inv_id) LOOP
9809: pn_variable_term_pkg.create_reversal_terms(p_payment_term_id => payment_rec.payment_term_id
9810: ,p_var_rent_inv_id => l_var_rent_inv_id
9815: END LOOP;
9816: END IF;
9817: END LOOP;
9818:
9819: pnp_debug_pkg.debug ('pn_var_rent_pkg.create_rev_term_LY_FLY (-) ');
9820:
9821: END create_rev_term_LY_FLY;
9822:
9823: -------------------------------------------------------------------------------
9884: l_retcode VARCHAR2(250);
9885: l_date DATE := SYSDATE;
9886:
9887: BEGIN
9888: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (+)');
9889: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9890: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9891: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9892: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9885: l_date DATE := SYSDATE;
9886:
9887: BEGIN
9888: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (+)');
9889: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9890: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9891: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9892: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9893: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9886:
9887: BEGIN
9888: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (+)');
9889: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9890: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9891: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9892: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9893: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9894:
9887: BEGIN
9888: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (+)');
9889: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9890: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9891: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9892: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9893: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9894:
9895: FOR main_vr_rec IN main_vr_cur LOOP
9888: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (+)');
9889: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9890: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9891: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9892: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9893: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9894:
9895: FOR main_vr_rec IN main_vr_cur LOOP
9896:
9889: pnp_debug_pkg.debug ('p_lease_id '||p_lease_id );
9890: pnp_debug_pkg.debug ('p_var_rent_id '||p_var_rent_id );
9891: pnp_debug_pkg.debug ('p_new_termn_date'||p_new_termn_date);
9892: pnp_debug_pkg.debug ('p_old_termn_date'||p_old_termn_date);
9893: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9894:
9895: FOR main_vr_rec IN main_vr_cur LOOP
9896:
9897: pnp_debug_pkg.debug ('**************************************************************');
9893: pnp_debug_pkg.debug ('p_extend_setup '||p_extend_setup );
9894:
9895: FOR main_vr_rec IN main_vr_cur LOOP
9896:
9897: pnp_debug_pkg.debug ('**************************************************************');
9898: pnp_debug_pkg.debug ('Processing variable rent agreement ...'||main_vr_rec.var_rent_id);
9899: pnp_debug_pkg.debug ('**************************************************************');
9900:
9901: /* Fetch the last period id and partial flag before expansion */
9894:
9895: FOR main_vr_rec IN main_vr_cur LOOP
9896:
9897: pnp_debug_pkg.debug ('**************************************************************');
9898: pnp_debug_pkg.debug ('Processing variable rent agreement ...'||main_vr_rec.var_rent_id);
9899: pnp_debug_pkg.debug ('**************************************************************');
9900:
9901: /* Fetch the last period id and partial flag before expansion */
9902: FOR last_period_rec IN last_period_cur(main_vr_rec.var_rent_id) LOOP
9895: FOR main_vr_rec IN main_vr_cur LOOP
9896:
9897: pnp_debug_pkg.debug ('**************************************************************');
9898: pnp_debug_pkg.debug ('Processing variable rent agreement ...'||main_vr_rec.var_rent_id);
9899: pnp_debug_pkg.debug ('**************************************************************');
9900:
9901: /* Fetch the last period id and partial flag before expansion */
9902: FOR last_period_rec IN last_period_cur(main_vr_rec.var_rent_id) LOOP
9903: l_partial_flag := last_period_rec.partial_period;
9901: /* Fetch the last period id and partial flag before expansion */
9902: FOR last_period_rec IN last_period_cur(main_vr_rec.var_rent_id) LOOP
9903: l_partial_flag := last_period_rec.partial_period;
9904: l_last_period_id := last_period_rec.period_id;
9905: pnp_debug_pkg.debug ('l_last_period_id ...'||l_last_period_id);
9906: pnp_debug_pkg.debug ('l_partial_flag ...'||l_partial_flag);
9907: END LOOP;
9908:
9909: /* Update the vr agreement with the new termination date */
9902: FOR last_period_rec IN last_period_cur(main_vr_rec.var_rent_id) LOOP
9903: l_partial_flag := last_period_rec.partial_period;
9904: l_last_period_id := last_period_rec.period_id;
9905: pnp_debug_pkg.debug ('l_last_period_id ...'||l_last_period_id);
9906: pnp_debug_pkg.debug ('l_partial_flag ...'||l_partial_flag);
9907: END LOOP;
9908:
9909: /* Update the vr agreement with the new termination date */
9910: pnp_debug_pkg.debug ('Update the vr agreement with the new termination date ...');
9906: pnp_debug_pkg.debug ('l_partial_flag ...'||l_partial_flag);
9907: END LOOP;
9908:
9909: /* Update the vr agreement with the new termination date */
9910: pnp_debug_pkg.debug ('Update the vr agreement with the new termination date ...');
9911:
9912: UPDATE pn_var_rents_all
9913: SET termination_date = p_new_termn_date
9914: WHERE var_rent_id = main_vr_rec.var_rent_id;
9916: /* call appropriate procedures to create new periods or activate the inactive one
9917: beyond the old termination date */
9918: IF NVL(main_vr_rec.use_gl_calendar,'N') = 'N' THEN
9919:
9920: pnp_debug_pkg.debug ('making a call to create_var_rent_periods_nocal ...');
9921: pn_var_rent_pkg.create_var_rent_periods_nocal(p_var_rent_id => main_vr_rec.var_rent_id ,
9922: p_cumulative_vol => main_vr_rec.cumulative_vol ,
9923: p_yr_start_date => main_vr_rec.year_start_date);
9924:
9922: p_cumulative_vol => main_vr_rec.cumulative_vol ,
9923: p_yr_start_date => main_vr_rec.year_start_date);
9924:
9925: ELSIF NVL(main_vr_rec.use_gl_calendar,'N') = 'Y' THEN
9926: pnp_debug_pkg.debug ('making a call to create_var_rent_periods ...');
9927: pn_var_rent_pkg.create_var_rent_periods( p_var_rent_id => main_vr_rec.var_rent_id,
9928: p_cumulative_vol => main_vr_rec.cumulative_vol,
9929: p_comm_date => main_vr_rec.commencement_date,
9930: p_term_date => p_new_termn_date,
9936:
9937: /* Check if data exists in defaults tables for this variable rent */
9938: FOR rec IN default_exists(main_vr_rec.var_rent_id) LOOP
9939: l_default_exists := 'Y';
9940: pnp_debug_pkg.debug ('l_default_exists is Y');
9941: END LOOP;
9942:
9943: pnp_debug_pkg.debug ('calling appropriate proc after checking if defaults exist ...');
9944: IF l_default_exists = 'Y' THEN
9939: l_default_exists := 'Y';
9940: pnp_debug_pkg.debug ('l_default_exists is Y');
9941: END LOOP;
9942:
9943: pnp_debug_pkg.debug ('calling appropriate proc after checking if defaults exist ...');
9944: IF l_default_exists = 'Y' THEN
9945: pnp_debug_pkg.debug ('calling proc extend_defaults .. if defaults exist ...');
9946: extend_defaults ( p_var_rent_id => main_vr_rec.var_rent_id
9947: , p_old_termn_date => p_old_termn_date
9941: END LOOP;
9942:
9943: pnp_debug_pkg.debug ('calling appropriate proc after checking if defaults exist ...');
9944: IF l_default_exists = 'Y' THEN
9945: pnp_debug_pkg.debug ('calling proc extend_defaults .. if defaults exist ...');
9946: extend_defaults ( p_var_rent_id => main_vr_rec.var_rent_id
9947: , p_old_termn_date => p_old_termn_date
9948: , p_new_termn_date => p_new_termn_date );
9949: ELSE
9946: extend_defaults ( p_var_rent_id => main_vr_rec.var_rent_id
9947: , p_old_termn_date => p_old_termn_date
9948: , p_new_termn_date => p_new_termn_date );
9949: ELSE
9950: pnp_debug_pkg.debug ('calling proc update_setup_exp .. if defaults do not exist ...');
9951: update_setup_exp ( p_var_rent_id => main_vr_rec.var_rent_id
9952: , p_old_termn_date => p_old_termn_date
9953: , p_period_id => l_last_period_id);
9954: END IF;
9984: WHERE var_rent_id = main_vr_rec.var_rent_id;
9985:
9986: END LOOP;
9987:
9988: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (-)');
9989:
9990: EXCEPTION
9991: WHEN OTHERS THEN
9992: pnp_debug_pkg.debug ('Process_vr_early_term Error:'||SQLERRM);
9988: pnp_debug_pkg.debug ('pn_var_rent_pkg.process_vr_ext (-)');
9989:
9990: EXCEPTION
9991: WHEN OTHERS THEN
9992: pnp_debug_pkg.debug ('Process_vr_early_term Error:'||SQLERRM);
9993: x_return_status := FND_API.G_RET_STS_ERROR;
9994: x_return_message := sqlerrm;
9995: END;
9996:
10075: FROM pn_lease_details_all
10076: WHERE lease_change_id = p_lease_change_id;
10077:
10078: BEGIN
10079: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +Start+ (+)');
10080: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10081: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10082: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10083: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10076: WHERE lease_change_id = p_lease_change_id;
10077:
10078: BEGIN
10079: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +Start+ (+)');
10080: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10081: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10082: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10083: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10084: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10077:
10078: BEGIN
10079: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +Start+ (+)');
10080: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10081: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10082: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10083: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10084: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10085: pnp_debug_pkg.log('Setup Expansion Context : '||p_setup_exp_context);
10078: BEGIN
10079: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +Start+ (+)');
10080: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10081: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10082: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10083: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10084: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10085: pnp_debug_pkg.log('Setup Expansion Context : '||p_setup_exp_context);
10086:
10079: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +Start+ (+)');
10080: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10081: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10082: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10083: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10084: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10085: pnp_debug_pkg.log('Setup Expansion Context : '||p_setup_exp_context);
10086:
10087: -- Bug 6524475
10080: pnp_debug_pkg.log('Lease_ID : '||p_lease_id);
10081: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10082: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10083: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10084: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10085: pnp_debug_pkg.log('Setup Expansion Context : '||p_setup_exp_context);
10086:
10087: -- Bug 6524475
10088: IF p_request_id IS NOT NULL AND p_request_id <> 0 THEN
10081: pnp_debug_pkg.log('Lease_Chang_ID : '||p_lease_change_id);
10082: pnp_debug_pkg.log('Old Termination date : '||p_old_term_date);
10083: pnp_debug_pkg.log('New Termination date : '||p_new_term_date);
10084: pnp_debug_pkg.log('Variable Rent Context : '||p_vr_context);
10085: pnp_debug_pkg.log('Setup Expansion Context : '||p_setup_exp_context);
10086:
10087: -- Bug 6524475
10088: IF p_request_id IS NOT NULL AND p_request_id <> 0 THEN
10089: l_status := fnd_concurrent.wait_for_request(p_request_id, l_interval, l_max_wait,
10144:
10145: /* Raise an exception that the dates entered by the user do not match with
10146: that in the database */
10147: /*IF (l_old_date <> l_old_term_date) OR (l_new_date <> l_new_term_date ) THEN
10148: pnp_debug_pkg.log('Throwing exception ....');
10149: RAISE INCORRECT_VR_DATES_EXCEPTION ;
10150: END IF;*/
10151:
10152: END IF;
10155:
10156: /* Check if new termination date is same as old termination date */
10157: IF (l_old_term_date <> l_new_term_date) THEN
10158:
10159: pnp_debug_pkg.log('New termination date is not equal to old termination date');
10160: /* Check if the dates and vr context are in sync i.e. if new termination date is less than old termination
10161: date then vr context should be 'CON' and if the new termination date is greater than old termination
10162: date then vr context should be 'EXP'. If this is not the case then throw an exception */
10163: IF (l_new_term_date < l_old_term_date) AND p_vr_context = 'CON' THEN
10161: date then vr context should be 'CON' and if the new termination date is greater than old termination
10162: date then vr context should be 'EXP'. If this is not the case then throw an exception */
10163: IF (l_new_term_date < l_old_term_date) AND p_vr_context = 'CON' THEN
10164:
10165: pnp_debug_pkg.log('Deleting VR agreements starting after new termination date');
10166: PN_VAR_RENTS_PKG.delete_var_rent_agreement(p_lease_id => p_lease_id ,
10167: p_termination_dt => l_new_term_date);
10168:
10169: pnp_debug_pkg.log('Calling VR contraction ....');
10165: pnp_debug_pkg.log('Deleting VR agreements starting after new termination date');
10166: PN_VAR_RENTS_PKG.delete_var_rent_agreement(p_lease_id => p_lease_id ,
10167: p_termination_dt => l_new_term_date);
10168:
10169: pnp_debug_pkg.log('Calling VR contraction ....');
10170: process_vr_early_term ( p_lease_id => p_lease_id
10171: ,p_var_rent_id => NULL
10172: ,p_new_termn_date => l_new_term_date
10173: ,p_old_termn_date => l_old_term_date
10176:
10177: ELSIF (l_new_term_date > l_old_term_date) AND p_vr_context = 'EXP' THEN
10178:
10179: IF p_setup_exp_context IS NULL THEN
10180: pnp_debug_pkg.log('Throwing exception ....');
10181: RAISE MISSING_SETUP_EXCEPTION ;
10182: ELSE
10183: pnp_debug_pkg.log('Calling VR expansion ....');
10184: process_vr_ext ( p_lease_id => p_lease_id
10179: IF p_setup_exp_context IS NULL THEN
10180: pnp_debug_pkg.log('Throwing exception ....');
10181: RAISE MISSING_SETUP_EXCEPTION ;
10182: ELSE
10183: pnp_debug_pkg.log('Calling VR expansion ....');
10184: process_vr_ext ( p_lease_id => p_lease_id
10185: ,p_var_rent_id => NULL
10186: ,p_new_termn_date => l_new_term_date
10187: ,p_old_termn_date => l_old_term_date
10192:
10193: ELSIF (((l_new_term_date < l_old_term_date) AND p_vr_context <> 'CON') OR
10194: ((l_new_term_date > l_old_term_date) AND p_vr_context <> 'EXP')) THEN
10195:
10196: pnp_debug_pkg.log('Throwing exception ....');
10197: RAISE INCORRECT_VR_CONTEXT_EXCEPTION ;
10198: END IF;
10199:
10200: END IF;
10198: END IF;
10199:
10200: END IF;
10201:
10202: pnp_debug_pkg.log('pn_var_rent_pkg.process_vr_exp_con +End+ (+)');
10203:
10204: EXCEPTION
10205: WHEN SCH_ITEMS_FAILED_EXCEPTION THEN
10206: fnd_message.set_name ('PN', 'PN_SCH_ITEMS_REQ_FAILED');
10205: WHEN SCH_ITEMS_FAILED_EXCEPTION THEN
10206: fnd_message.set_name ('PN', 'PN_SCH_ITEMS_REQ_FAILED');
10207: fnd_message.set_token ('REQ_NUM',p_request_id);
10208: errbuf := fnd_message.get;
10209: pnp_debug_pkg.put_log_msg (errbuf);
10210: retcode := 2;
10211: WHEN INCORRECT_VR_CONTEXT_EXCEPTION THEN
10212: fnd_message.set_name ('PN', 'INCORRECT_VR_CONTEXT');
10213: errbuf := fnd_message.get;
10210: retcode := 2;
10211: WHEN INCORRECT_VR_CONTEXT_EXCEPTION THEN
10212: fnd_message.set_name ('PN', 'INCORRECT_VR_CONTEXT');
10213: errbuf := fnd_message.get;
10214: pnp_debug_pkg.put_log_msg (errbuf);
10215: retcode := 2;
10216: WHEN INCORRECT_VR_DATES_EXCEPTION THEN
10217: fnd_message.set_name ('PN', 'INCORRECT_VR_DATES');
10218: errbuf := fnd_message.get;
10215: retcode := 2;
10216: WHEN INCORRECT_VR_DATES_EXCEPTION THEN
10217: fnd_message.set_name ('PN', 'INCORRECT_VR_DATES');
10218: errbuf := fnd_message.get;
10219: pnp_debug_pkg.put_log_msg (errbuf);
10220: retcode := 2;
10221: WHEN MISSING_CHANGE_ID_EXCEPTION THEN
10222: fnd_message.set_name ('PN', 'MISSING_CHANGE_ID');
10223: errbuf := fnd_message.get;
10220: retcode := 2;
10221: WHEN MISSING_CHANGE_ID_EXCEPTION THEN
10222: fnd_message.set_name ('PN', 'MISSING_CHANGE_ID');
10223: errbuf := fnd_message.get;
10224: pnp_debug_pkg.put_log_msg (errbuf);
10225: retcode := 2;
10226: WHEN MISSING_SETUP_EXCEPTION THEN
10227: fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
10228: errbuf := fnd_message.get;
10225: retcode := 2;
10226: WHEN MISSING_SETUP_EXCEPTION THEN
10227: fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
10228: errbuf := fnd_message.get;
10229: pnp_debug_pkg.put_log_msg (errbuf);
10230: retcode := 2;
10231: WHEN OTHERS THEN
10232: Errbuf := SQLERRM;
10233: Retcode := 2;
10563: --Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
10564: --Fnd_File.Put_Line ( Fnd_File.OutPut,' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10565: --Fnd_File.Put_Line ( Fnd_File.OutPut,' cannot be processed. You will need to recreate this agreement manually.');
10566: --Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
10567: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10568: pnp_debug_pkg.log (' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10569: pnp_debug_pkg.log (' cannot be processed. You will need to recreate this agreement manually.');
10570: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10571:
10564: --Fnd_File.Put_Line ( Fnd_File.OutPut,' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10565: --Fnd_File.Put_Line ( Fnd_File.OutPut,' cannot be processed. You will need to recreate this agreement manually.');
10566: --Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
10567: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10568: pnp_debug_pkg.log (' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10569: pnp_debug_pkg.log (' cannot be processed. You will need to recreate this agreement manually.');
10570: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10571:
10572: EXIT outer;
10565: --Fnd_File.Put_Line ( Fnd_File.OutPut,' cannot be processed. You will need to recreate this agreement manually.');
10566: --Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
10567: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10568: pnp_debug_pkg.log (' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10569: pnp_debug_pkg.log (' cannot be processed. You will need to recreate this agreement manually.');
10570: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10571:
10572: EXIT outer;
10573: END IF;
10566: --Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
10567: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10568: pnp_debug_pkg.log (' Agreement ' || per_rec.rent_num || ' has two or more similar lines in same period and hence ');
10569: pnp_debug_pkg.log (' cannot be processed. You will need to recreate this agreement manually.');
10570: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10571:
10572: EXIT outer;
10573: END IF;
10574: END LOOP;
10914:
10915: /* Completed Inserting constraints into constraint defaults table and
10916: populating constraints with the constraints default id */
10917:
10918: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10919: pnp_debug_pkg.log (' Agreement ' || l_rent_num || ' has been Updated');
10920: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10921:
10922: END IF;
10915: /* Completed Inserting constraints into constraint defaults table and
10916: populating constraints with the constraints default id */
10917:
10918: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10919: pnp_debug_pkg.log (' Agreement ' || l_rent_num || ' has been Updated');
10920: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10921:
10922: END IF;
10923:
10916: populating constraints with the constraints default id */
10917:
10918: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10919: pnp_debug_pkg.log (' Agreement ' || l_rent_num || ' has been Updated');
10920: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10921:
10922: END IF;
10923:
10924: END IF;
10923:
10924: END IF;
10925: END LOOP;
10926: ELSE
10927: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10928: pnp_debug_pkg.log (' There is no Variable Rent Agreement with given search criteria');
10929: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10930: END IF;
10931:
10924: END IF;
10925: END LOOP;
10926: ELSE
10927: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10928: pnp_debug_pkg.log (' There is no Variable Rent Agreement with given search criteria');
10929: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10930: END IF;
10931:
10932: EXCEPTION
10925: END LOOP;
10926: ELSE
10927: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10928: pnp_debug_pkg.log (' There is no Variable Rent Agreement with given search criteria');
10929: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10930: END IF;
10931:
10932: EXCEPTION
10933: WHEN BAD_CALL_EXCEPTION THEN
10930: END IF;
10931:
10932: EXCEPTION
10933: WHEN BAD_CALL_EXCEPTION THEN
10934: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10935: pnp_debug_pkg.log (' Input to the program is invalid');
10936: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10937:
10938: END copy_bkpt_main_to_setup;
10931:
10932: EXCEPTION
10933: WHEN BAD_CALL_EXCEPTION THEN
10934: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10935: pnp_debug_pkg.log (' Input to the program is invalid');
10936: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10937:
10938: END copy_bkpt_main_to_setup;
10939:
10932: EXCEPTION
10933: WHEN BAD_CALL_EXCEPTION THEN
10934: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10935: pnp_debug_pkg.log (' Input to the program is invalid');
10936: pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
10937:
10938: END copy_bkpt_main_to_setup;
10939:
10940: /*===========================================================================+
10969: l_inv_exp NUMBER;
10970:
10971: BEGIN
10972:
10973: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_INV_EXP (+)');
10974:
10975: SELECT 1
10976: INTO l_inv_exp
10977: FROM dual
10982: AND (forecasted_exp_code = 'Y'));
10983:
10984: RETURN l_inv_exp;
10985:
10986: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.FIND_IF_INV_EXP (-)');
10987:
10988: EXCEPTION
10989:
10990: WHEN OTHERS THEN
11093: AND bkdt_end_date BETWEEN p_start_date AND p_end_date;
11094:
11095: BEGIN
11096:
11097: pnp_debug_pkg.log('pn_var_rent_pkg.rates_validation (+)');
11098:
11099: FOR var_rec in var_cur(p_vr_id => p_var_rent_id) LOOP
11100: IF p_agr_start_date IS NOT NULL THEN
11101: l_agr_start_date := p_agr_start_date;
11345: END IF;
11346:
11347: RETURN l_return_status;
11348:
11349: pnp_debug_pkg.log('pn_var_rent_pkg.rates_validation (-)');
11350:
11351: END rates_validation;
11352:
11353: