[Home] [Help]
60: IS
61:
62: BEGIN
63:
64: x_return_status := fnd_api.g_ret_sts_success;
65:
66: FOR i in 1..p_index_constraint_tbl.count
67: LOOP
68: IF (p_index_constraint_tbl(i).attribute_category = pn_index_rent_utils.g_pn_miss_char)
189: , p_resp_appl_id => fnd_global.resp_appl_id
190: , p_resp_id => fnd_global.resp_id
191: , p_return_status => x_return_status
192: );
193: IF (x_return_status = fnd_api.g_ret_sts_error)
194: THEN
195: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
196: fnd_message.set_token('ERR_MSG', 'Index Rent constraints descriptive flexfield validation failed due to an error');
197: fnd_msg_pub.ADD;
194: THEN
195: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
196: fnd_message.set_token('ERR_MSG', 'Index Rent constraints descriptive flexfield validation failed due to an error');
197: fnd_msg_pub.ADD;
198: RAISE fnd_api.g_exc_error;
199: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
200: THEN
201: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
202: fnd_message.set_token('ERR_MSG', 'Index Rent constraints descriptive flexfield validation failed due to an unexpected error');
195: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
196: fnd_message.set_token('ERR_MSG', 'Index Rent constraints descriptive flexfield validation failed due to an error');
197: fnd_msg_pub.ADD;
198: RAISE fnd_api.g_exc_error;
199: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
200: THEN
201: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
202: fnd_message.set_token('ERR_MSG', 'Index Rent constraints descriptive flexfield validation failed due to an unexpected error');
203: fnd_msg_pub.ADD;
200: THEN
201: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
202: fnd_message.set_token('ERR_MSG', 'Index Rent constraints descriptive flexfield validation failed due to an unexpected error');
203: fnd_msg_pub.ADD;
204: RAISE fnd_api.g_exc_unexpected_error;
205: END IF;
206: END IF;
207: END IF; -- End of attributes valitions in Index Rent Constraints attributes
208: END LOOP;
346: , p_resp_appl_id => fnd_global.resp_appl_id
347: , p_resp_id => fnd_global.resp_id
348: , p_return_status => x_return_status
349: );
350: IF (x_return_status = fnd_api.g_ret_sts_error)
351: THEN
352: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
353: fnd_message.set_token('ERR_MSG', 'Index Rent descriptive flexfield validation failed due to an error');
354: fnd_msg_pub.ADD;
351: THEN
352: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
353: fnd_message.set_token('ERR_MSG', 'Index Rent descriptive flexfield validation failed due to an error');
354: fnd_msg_pub.ADD;
355: RAISE fnd_api.g_exc_error;
356: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
357: THEN
358: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
359: fnd_message.set_token('ERR_MSG', 'Index Rent descriptive flexfield validation failed due to an unexpected error');
352: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
353: fnd_message.set_token('ERR_MSG', 'Index Rent descriptive flexfield validation failed due to an error');
354: fnd_msg_pub.ADD;
355: RAISE fnd_api.g_exc_error;
356: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
357: THEN
358: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
359: fnd_message.set_token('ERR_MSG', 'Index Rent descriptive flexfield validation failed due to an unexpected error');
360: fnd_msg_pub.ADD;
357: THEN
358: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
359: fnd_message.set_token('ERR_MSG', 'Index Rent descriptive flexfield validation failed due to an unexpected error');
360: fnd_msg_pub.ADD;
361: RAISE fnd_api.g_exc_unexpected_error;
362: END IF;
363: END IF;
364: END IF; -- End of attributes valitions in Index Rent attribute
365:
488: , p_resp_appl_id => fnd_global.resp_appl_id
489: , p_resp_id => fnd_global.resp_id
490: , p_return_status => x_return_status
491: );
492: IF (x_return_status = fnd_api.g_ret_sts_error)
493: THEN
494: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
495: fnd_message.set_token('ERR_MSG', 'Index Rent agreement descriptive flexfield validation failed due to an error');
496: fnd_msg_pub.ADD;
493: THEN
494: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
495: fnd_message.set_token('ERR_MSG', 'Index Rent agreement descriptive flexfield validation failed due to an error');
496: fnd_msg_pub.ADD;
497: RAISE fnd_api.g_exc_error;
498: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
499: THEN
500: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
501: fnd_message.set_token('ERR_MSG', 'Index Rent agreement descriptive flexfield validation failed due to an unexpected error');
494: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
495: fnd_message.set_token('ERR_MSG', 'Index Rent agreement descriptive flexfield validation failed due to an error');
496: fnd_msg_pub.ADD;
497: RAISE fnd_api.g_exc_error;
498: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
499: THEN
500: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
501: fnd_message.set_token('ERR_MSG', 'Index Rent agreement descriptive flexfield validation failed due to an unexpected error');
502: fnd_msg_pub.ADD;
499: THEN
500: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
501: fnd_message.set_token('ERR_MSG', 'Index Rent agreement descriptive flexfield validation failed due to an unexpected error');
502: fnd_msg_pub.ADD;
503: RAISE fnd_api.g_exc_unexpected_error;
504: END IF;
505: END IF;
506: END IF; -- End of attributes valitions in Index Rent agreement attribute
507:
519: IS
520: BEGIN
521: g_program_unit := 'INIT_INDEX_CONSTRAINT_REC';
522:
523: x_return_status := fnd_api.g_ret_sts_success;
524:
525: IF p_index_constraint_rec.index_constraint_id = pn_index_rent_utils.g_pn_miss_num
526: THEN
527: p_index_constraint_rec.index_constraint_id := NULL;
676: THEN
677: p_index_constraint_rec.org_id := NULL;
678: END IF;
679: EXCEPTION
680: WHEN fnd_api.g_exc_error
681: THEN
682: pn_index_rent_pvt.DEBUG ( 'Error occurred in '
683: || g_pkg_name
684: || g_program_unit
684: || g_program_unit
685: || '-'
686: || SQLERRM
687: , 5);
688: x_return_status := fnd_api.g_ret_sts_error;
689:
690: WHEN OTHERS
691: THEN
692: pn_index_rent_pvt.DEBUG ( 'Exception in '
694: || g_program_unit
695: || '-'
696: || SQLERRM
697: , 5);
698: x_return_status := fnd_api.g_ret_sts_unexp_error;
699: END init_index_constraint_rec;
700:
701: ---------------------------------------------------
702: -- Procedure to initialize index rent record.
708: , x_msg_data OUT NOCOPY VARCHAR2)
709: IS
710: BEGIN
711: g_program_unit := 'INIT_INDEX_RENT_REC';
712: x_return_status := fnd_api.g_ret_sts_success;
713:
714: IF p_rent_index_rec.index_lease_id = pn_index_rent_utils.g_pn_miss_num
715: THEN
716: p_rent_index_rec.index_lease_id := NULL;
1145: THEN
1146: p_rent_index_rec.proration_period_start_date := NULL;
1147: END IF;
1148: EXCEPTION
1149: WHEN fnd_api.g_exc_error
1150: THEN
1151: pn_index_rent_pvt.DEBUG ( 'Error occurred in '
1152: || g_pkg_name
1153: || g_program_unit
1153: || g_program_unit
1154: || '-'
1155: || SQLERRM
1156: , 5);
1157: x_return_status := fnd_api.g_ret_sts_error;
1158:
1159: WHEN OTHERS
1160: THEN
1161: pn_index_rent_pvt.DEBUG ( 'Exception in '
1163: || g_program_unit
1164: || '-'
1165: || SQLERRM
1166: , 5);
1167: x_return_status := fnd_api.g_ret_sts_unexp_error;
1168: END init_index_rent_rec;
1169:
1170: -----------------------------------------------------------------------------------------
1171: -- Procedure to update pn_index_leases_all table with the provided index rent new values.
1178: IS
1179: l_rowid VARCHAR2 (18) := NULL;
1180: BEGIN
1181: g_program_unit := 'UPDATE_INDENT_RENT_ROW';
1182: x_return_status := fnd_api.g_ret_sts_success;
1183:
1184: pn_index_rent_pvt.DEBUG ( g_pkg_name
1185: || g_program_unit
1186: || ' Calling pn_index_leases_pkg.update_row'
1265: || ' pn_index_leases_pkg.update_row successful index_lease_id:'|| p_rent_index_rec.index_lease_id
1266: , 3);
1267: IF p_rent_index_rec.index_lease_id>0
1268: THEN
1269: x_return_status := fnd_api.g_ret_sts_success;
1270: ELSE
1271: RAISE fnd_api.g_exc_error;
1272: END IF;
1273: EXCEPTION
1267: IF p_rent_index_rec.index_lease_id>0
1268: THEN
1269: x_return_status := fnd_api.g_ret_sts_success;
1270: ELSE
1271: RAISE fnd_api.g_exc_error;
1272: END IF;
1273: EXCEPTION
1274: WHEN fnd_api.g_exc_error
1275: THEN
1270: ELSE
1271: RAISE fnd_api.g_exc_error;
1272: END IF;
1273: EXCEPTION
1274: WHEN fnd_api.g_exc_error
1275: THEN
1276: pn_index_rent_pvt.DEBUG ( 'Error occurred in '
1277: || g_pkg_name
1278: || g_program_unit
1278: || g_program_unit
1279: || '-'
1280: || SQLERRM
1281: , 5);
1282: x_return_status := fnd_api.g_ret_sts_error;
1283:
1284: WHEN OTHERS
1285: THEN
1286: pn_index_rent_pvt.DEBUG ( 'Exception in '
1288: || g_program_unit
1289: || '-'
1290: || SQLERRM
1291: , 5);
1292: x_return_status := fnd_api.g_ret_sts_unexp_error;
1293: END update_index_rent_row;
1294: ------------------------------------------------------------------------
1295: -- Procedure to insert index rent record into pn_index_leases_all table
1296: ------------------------------------------------------------------------
1302: IS
1303: l_rowid VARCHAR2 (18) := NULL;
1304: BEGIN
1305: g_program_unit := 'INSERT_INDEX_RENT_ROW';
1306: x_return_status := fnd_api.g_ret_sts_success;
1307: init_index_rent_rec (p_rent_index_rec => p_rent_index_rec
1308: , x_return_status => x_return_status
1309: , x_msg_count => x_msg_count
1310: , x_msg_data => x_msg_count);
1401: || ' pn_index_leases_pkg.insert_row successful. index_lease_id:'|| p_rent_index_rec.index_lease_id
1402: , 3);
1403:
1404: --ELSE
1405: -- RAISE fnd_api.g_exc_error;
1406: --END IF;
1407: IF p_rent_index_rec.index_lease_id>0
1408: THEN
1409: x_return_status := fnd_api.g_ret_sts_success;
1405: -- RAISE fnd_api.g_exc_error;
1406: --END IF;
1407: IF p_rent_index_rec.index_lease_id>0
1408: THEN
1409: x_return_status := fnd_api.g_ret_sts_success;
1410: ELSE
1411: RAISE fnd_api.g_exc_error;
1412: END IF;
1413: EXCEPTION
1407: IF p_rent_index_rec.index_lease_id>0
1408: THEN
1409: x_return_status := fnd_api.g_ret_sts_success;
1410: ELSE
1411: RAISE fnd_api.g_exc_error;
1412: END IF;
1413: EXCEPTION
1414: WHEN fnd_api.g_exc_error
1415: THEN
1410: ELSE
1411: RAISE fnd_api.g_exc_error;
1412: END IF;
1413: EXCEPTION
1414: WHEN fnd_api.g_exc_error
1415: THEN
1416: pn_index_rent_pvt.DEBUG ( 'Error occurred in '
1417: || g_pkg_name
1418: || g_program_unit
1418: || g_program_unit
1419: || '-'
1420: || SQLERRM
1421: , 5);
1422: x_return_status := fnd_api.g_ret_sts_error;
1423:
1424: WHEN OTHERS
1425: THEN
1426: pn_index_rent_pvt.DEBUG ( 'Exception in '
1428: || g_program_unit
1429: || '-'
1430: || SQLERRM
1431: , 5);
1432: x_return_status := fnd_api.g_ret_sts_unexp_error;
1433: END insert_index_rent_row;
1434:
1435: -----------------------------------------------------------------------------------------
1436: -- Procedure to get the changed index rent fields for updating pn_index_leases_all table
1523:
1524: l_rent_index_rec_old pn_index_leases_all%ROWTYPE;
1525: BEGIN
1526: g_program_unit := 'GET_CHANGED_INDEX_RENT_REC';
1527: x_return_status := fnd_api.g_ret_sts_success;
1528: pn_index_rent_pvt.DEBUG ( g_pkg_name
1529: || g_program_unit
1530: || ' Inside get_changed_index_rent_rec '
1531: , 3);
1536: INTO l_rent_index_rec_old;
1537:
1538: IF (c_old_index_rent_values%NOTFOUND)
1539: THEN
1540: x_return_status := fnd_api.g_ret_sts_error;
1541:
1542: pn_index_rent_pvt.DEBUG ( g_pkg_name
1543: || g_program_unit
1544: || ' Cursor c_old_index_rent_values failed to return the existing record '
1543: || g_program_unit
1544: || ' Cursor c_old_index_rent_values failed to return the existing record '
1545: , 3);
1546: --ELSE
1547: -- x_return_status := fnd_api.g_ret_sts_success;
1548: END IF;
1549:
1550: IF p_rent_index_rec.lease_id IS NULL OR p_rent_index_rec.lease_id =pn_index_rent_utils.g_pn_miss_num
1551: THEN
1634: x_return_status <> 'S') THEN
1635: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
1636: fnd_message.set_token('ERR_MSG', 'Update location for IR terms failed with status: '||x_return_status);
1637: fnd_msg_pub.ADD;
1638: RAISE fnd_api.g_exc_error;
1639: END IF;
1640: END IF;
1641: END IF;
1642: ELSE
1659: x_return_status <> 'S') THEN
1660: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
1661: fnd_message.set_token('ERR_MSG', 'Update location for IR terms failed with status: '||x_return_status);
1662: fnd_msg_pub.ADD;
1663: RAISE fnd_api.g_exc_error;
1664: END IF;
1665: END IF;
1666: END IF;
1667:
2016: THEN
2017: CLOSE c_old_index_rent_values;
2018: END IF;
2019:
2020: x_return_status := fnd_api.g_ret_sts_unexp_error;
2021: END get_changed_index_rent_rec;
2022:
2023: ----------------------------------------------
2024: -- Procedure to validate index record passed
2024: -- Procedure to validate index record passed
2025: ----------------------------------------------
2026: PROCEDURE validate_index_rent_rec (
2027: p_rent_index_rec IN OUT NOCOPY rent_index_rec
2028: --, p_validate IN VARCHAR2 := fnd_api.g_false
2029: , p_operation IN VARCHAR2 DEFAULT 'CREATE_RENT_INDEX'
2030: , x_return_status OUT NOCOPY VARCHAR2
2031: , x_msg_count OUT NOCOPY NUMBER
2032: , x_msg_data OUT NOCOPY VARCHAR2)
2081: WHERE set_of_books_id = p_set_of_books_id;
2082:
2083: l_error_count NUMBER := 0;
2084: BEGIN
2085: x_return_status := fnd_api.g_ret_sts_success;
2086: g_program_unit := 'VALIDATE_INDEX_RENT_REC';
2087:
2088: pn_index_rent_pvt.DEBUG ( g_pkg_name
2089: || g_program_unit
2137: || g_program_unit
2138: || ' - PN_INDEX_RENT_UTILS.GET_INDEX_LEASE_ID returned index_lease_id - '|| p_rent_index_rec.index_lease_id
2139: , 3);
2140:
2141: IF x_return_status<>fnd_api.g_ret_sts_success
2142: THEN
2143: l_error_count:=l_error_count+1;
2144: fnd_message.set_name('PN','PN_INDEX_UPDATE_REQD_DATA');
2145: fnd_msg_pub.ADD;
2142: THEN
2143: l_error_count:=l_error_count+1;
2144: fnd_message.set_name('PN','PN_INDEX_UPDATE_REQD_DATA');
2145: fnd_msg_pub.ADD;
2146: RAISE fnd_api.g_exc_error;
2147: END IF;
2148: END IF;
2149: --END IF;
2150: END IF;
2173: || ' - PN_INDEX_RENT_UTILS.GET_INDEX_LEASE_ID returned index_lease_id - 3'|| p_rent_index_rec.index_lease_id
2174: , 3);
2175:
2176:
2177: IF x_return_status IS NOT NULL AND x_return_status<>fnd_api.g_ret_sts_success
2178: THEN
2179: l_error_count:=l_error_count+1;
2180: fnd_message.set_name('PN','PN_DUP_INDEX_LEASE_NUMBER');
2181: fnd_msg_pub.ADD;
2178: THEN
2179: l_error_count:=l_error_count+1;
2180: fnd_message.set_name('PN','PN_DUP_INDEX_LEASE_NUMBER');
2181: fnd_msg_pub.ADD;
2182: RAISE fnd_api.g_exc_error;
2183: END IF;
2184: END IF;
2185: END IF;
2186: END IF;
2249: || g_program_unit ||
2250: ' - After calling pn_index_rent_utils.get_lease_id lease_name - '|| p_rent_index_rec.lease_name
2251: , 3);
2252:
2253: IF x_return_status<>fnd_api.g_ret_sts_success
2254: THEN
2255: l_error_count:=l_error_count+1;
2256: fnd_message.set_name('PN','PN_LEASE_ID');
2257: fnd_message.set_token (' ID', p_rent_index_rec.lease_id);
2255: l_error_count:=l_error_count+1;
2256: fnd_message.set_name('PN','PN_LEASE_ID');
2257: fnd_message.set_token (' ID', p_rent_index_rec.lease_id);
2258: fnd_msg_pub.ADD;
2259: RAISE fnd_api.g_exc_error;
2260: END IF;
2261: END IF;
2262:
2263: ------------------------------------------------------------------
2284: || g_program_unit||
2285: ' - After calling pn_index_rent_utils.get_location_id location_code - '|| p_rent_index_rec.location_id
2286: , 3);
2287:
2288: IF x_return_status<>fnd_api.g_ret_sts_success
2289: THEN
2290: l_error_count:=l_error_count+1;
2291: fnd_message.set_name('PN','PN_LOCATION_NOT_EXIST');
2292: fnd_msg_pub.ADD;
2289: THEN
2290: l_error_count:=l_error_count+1;
2291: fnd_message.set_name('PN','PN_LOCATION_NOT_EXIST');
2292: fnd_msg_pub.ADD;
2293: RAISE fnd_api.g_exc_error;
2294: END IF;
2295: END IF;
2296:
2297: pn_index_rent_pvt.DEBUG ( g_pkg_name
2316: || g_program_unit ||
2317: ' - After calling pn_index_rent_utils.get_index_id index - '|| p_rent_index_rec.index_id
2318: , 3);
2319:
2320: IF x_return_status<>fnd_api.g_ret_sts_success
2321: THEN
2322: l_error_count:=l_error_count+1;
2323: fnd_message.set_name('PN','PN_INDEX_TYPE_NOT_EXISTS');
2324: fnd_msg_pub.ADD;
2321: THEN
2322: l_error_count:=l_error_count+1;
2323: fnd_message.set_name('PN','PN_INDEX_TYPE_NOT_EXISTS');
2324: fnd_msg_pub.ADD;
2325: RAISE fnd_api.g_exc_error;
2326: END IF;
2327: END IF;
2328:
2329: ----------------------------------------------
2368: THEN
2369: l_error_count:=l_error_count+1;
2370: fnd_message.set_name ('PN', 'PN_INDEX_COMMENCE_TERMINATE_DT');
2371: fnd_msg_pub.ADD;
2372: RAISE fnd_api.g_exc_error;
2373: END IF;
2374:
2375: IF (p_rent_index_rec.termination_date < p_rent_index_rec.commencement_date)
2376: THEN
2376: THEN
2377: l_error_count:=l_error_count+1;
2378: fnd_message.set_name ('PN', 'PN_INDEX_TERMINATION_DT');
2379: fnd_msg_pub.ADD;
2380: RAISE fnd_api.g_exc_error;
2381: END IF;
2382:
2383: IF ( (p_rent_index_rec.termination_date < l_lease_commencement_date)
2384: OR (p_rent_index_rec.termination_date >
2387: THEN
2388: l_error_count:=l_error_count+1;
2389: fnd_message.set_name ('PN', 'PN_INDEX_COMMENCE_TERMINATE_DT');
2390: fnd_msg_pub.ADD;
2391: RAISE fnd_api.g_exc_error;
2392: END IF;
2393:
2394: IF (p_rent_index_rec.termination_date < p_rent_index_rec.commencement_date)
2395: THEN
2395: THEN
2396: l_error_count:=l_error_count+1;
2397: fnd_message.set_name ('PN', 'PN_INDEX_TERMINATION_DT');
2398: fnd_msg_pub.ADD;
2399: RAISE fnd_api.g_exc_error;
2400: END IF;
2401:
2402: --ELSE
2403: -- p_rent_index_rec.commencement_date := ADD_MONTHS (l_lease_commencement_date, 12);
2435: , p_user_name => p_rent_index_rec.abstracted_by_username
2436: , x_user_id => p_rent_index_rec.abstracted_by
2437: , x_return_status => x_return_status);
2438:
2439: IF x_return_status <> fnd_api.g_ret_sts_success
2440: THEN
2441: l_error_count:=l_error_count+1;
2442: fnd_message.set_name ('PN','PN_INDEX_USER_NOT_EXISTS');
2443: fnd_msg_pub.ADD;
2440: THEN
2441: l_error_count:=l_error_count+1;
2442: fnd_message.set_name ('PN','PN_INDEX_USER_NOT_EXISTS');
2443: fnd_msg_pub.ADD;
2444: RAISE fnd_api.g_exc_error;
2445: END IF;
2446: END IF;
2447:
2448: pn_index_rent_pvt.DEBUG ( g_pkg_name
2467: , x_currency_code => p_rent_index_rec.currency_code
2468: , x_user_conversion_type => l_user_conversion_type
2469: , x_conversion_type => l_conversion_type
2470: , x_return_status => x_return_status);
2471: IF x_return_status<>fnd_api.g_ret_sts_success
2472: THEN
2473: l_error_count:=l_error_count+1;
2474: fnd_message.set_name('PN','PN_CURRENCY_CODE_NOT_EXIST');
2475: fnd_msg_pub.ADD;
2472: THEN
2473: l_error_count:=l_error_count+1;
2474: fnd_message.set_name('PN','PN_CURRENCY_CODE_NOT_EXIST');
2475: fnd_msg_pub.ADD;
2476: RAISE fnd_api.g_exc_error;
2477: END IF;
2478: END IF;*/
2479: p_rent_index_rec.currency_code:= l_func_curr_code;
2480:
2501: THEN
2502: l_error_count:=l_error_count+1;
2503: fnd_message.set_name ('PN', 'PN_INVALID_ASSESS_YEAR');
2504: fnd_msg_pub.ADD;
2505: RAISE fnd_api.g_exc_error;
2506: END IF;
2507: ELSE
2508: p_rent_index_rec.assessment_interval := 1;
2509: END IF;
2542: , p_currency_code => l_func_curr_code
2543: --p_rent_index_rec.currency_code
2544: , x_lookup_code => p_rent_index_rec.increase_on
2545: , x_return_status => x_return_status);
2546: IF x_return_status<>fnd_api.g_ret_sts_success
2547: THEN
2548: l_error_count:=l_error_count+1;
2549: fnd_message.set_name('PN','PN_INDEX_INCREASE_ON_NOT_EXIST');
2550: fnd_msg_pub.ADD;
2547: THEN
2548: l_error_count:=l_error_count+1;
2549: fnd_message.set_name('PN','PN_INDEX_INCREASE_ON_NOT_EXIST');
2550: fnd_msg_pub.ADD;
2551: RAISE fnd_api.g_exc_error;
2552: END IF;
2553:
2554: END IF;
2555:
2585: , p_lookup_meaning => p_rent_index_rec.reference_period_meaning
2586: , p_lookup_type => l_reference_period_type
2587: , x_lookup_type_code => p_rent_index_rec.reference_period
2588: , x_return_status => x_return_status);
2589: IF x_return_status<>fnd_api.g_ret_sts_success
2590: THEN
2591: l_error_count:=l_error_count+1;
2592: fnd_message.set_name('PN','PN_INDEX_REFERENCE_PERIOD');
2593: fnd_msg_pub.ADD;
2590: THEN
2591: l_error_count:=l_error_count+1;
2592: fnd_message.set_name('PN','PN_INDEX_REFERENCE_PERIOD');
2593: fnd_msg_pub.ADD;
2594: RAISE fnd_api.g_exc_error;
2595: END IF;
2596: ELSE
2597: IF LTRIM (RTRIM (p_operation)) <> 'UPDATE_INDEX_RENT'
2598: THEN
2625: , p_lookup_meaning => p_rent_index_rec.basis_type_meaning
2626: , p_lookup_type => l_basis_type_type
2627: , x_lookup_type_code => p_rent_index_rec.basis_type
2628: , x_return_status => x_return_status);
2629: IF x_return_status<>fnd_api.g_ret_sts_success
2630: THEN
2631: l_error_count:=l_error_count+1;
2632: fnd_message.set_name('PN','PN_INDEX_BASIS_TYPE_NOT_EXISTS');
2633: fnd_msg_pub.ADD;
2630: THEN
2631: l_error_count:=l_error_count+1;
2632: fnd_message.set_name('PN','PN_INDEX_BASIS_TYPE_NOT_EXISTS');
2633: fnd_msg_pub.ADD;
2634: RAISE fnd_api.g_exc_error;
2635: END IF;
2636: END IF;
2637:
2638: pn_index_rent_pvt.DEBUG ( g_pkg_name
2659: , p_lookup_meaning => p_rent_index_rec.index_finder_method_meaning
2660: , p_lookup_type => l_index_finder_method_type
2661: , x_lookup_type_code => p_rent_index_rec.index_finder_method
2662: , x_return_status => x_return_status);
2663: IF x_return_status<>fnd_api.g_ret_sts_success
2664: THEN
2665: l_error_count:=l_error_count+1;
2666: fnd_message.set_name('PN','PN_INDEX_FINDER_NOT_EXISTS');
2667: fnd_msg_pub.ADD;
2664: THEN
2665: l_error_count:=l_error_count+1;
2666: fnd_message.set_name('PN','PN_INDEX_FINDER_NOT_EXISTS');
2667: fnd_msg_pub.ADD;
2668: RAISE fnd_api.g_exc_error;
2669: END IF;
2670: ELSE
2671: IF LTRIM (RTRIM (p_operation)) <> 'UPDATE_INDEX_RENT'
2672: THEN
2699: , p_lookup_meaning => p_rent_index_rec.relationship_default_meaning
2700: , p_lookup_type => l_relationship_default_type
2701: , x_lookup_type_code => p_rent_index_rec.relationship_default
2702: , x_return_status => x_return_status);
2703: IF x_return_status<>fnd_api.g_ret_sts_success
2704: THEN
2705: l_error_count:=l_error_count+1;
2706: fnd_message.set_name('PN','PN_INDEX_RELATION_NOT_EXISTS');
2707: fnd_msg_pub.ADD;
2704: THEN
2705: l_error_count:=l_error_count+1;
2706: fnd_message.set_name('PN','PN_INDEX_RELATION_NOT_EXISTS');
2707: fnd_msg_pub.ADD;
2708: RAISE fnd_api.g_exc_error;
2709: END IF;
2710: END IF;
2711:
2712: IF LTRIM (RTRIM (p_operation)) <>'UPDATE_INDEX_RENT'
2748: , p_termtemp_type => l_term_template_type
2749: , p_org_id => p_rent_index_rec.org_id
2750: , x_return_status => x_return_status
2751: , x_termtemp_id => p_rent_index_rec.term_template_id);
2752: IF x_return_status<>fnd_api.g_ret_sts_success
2753: THEN
2754: l_error_count:=l_error_count+1;
2755: fnd_message.set_name('PN','PN_INDEX_TERM_TEMPL_NOT_EXISTS');
2756: fnd_msg_pub.ADD;
2753: THEN
2754: l_error_count:=l_error_count+1;
2755: fnd_message.set_name('PN','PN_INDEX_TERM_TEMPL_NOT_EXISTS');
2756: fnd_msg_pub.ADD;
2757: RAISE fnd_api.g_exc_error;
2758: END IF;
2759: --END IF;
2760:
2761: END IF;
2779: l_error_count:=l_error_count+1;
2780: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
2781: fnd_message.set_token('ERR_MSG', 'Both Term Template and Agreement Flag have been provided. Please provide either Term Template or Agreement Flag');
2782: fnd_msg_pub.ADD;
2783: RAISE fnd_api.g_exc_error;
2784:
2785: END IF;
2786: ----
2787:
2797: l_error_count:=l_error_count+1;
2798: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
2799: fnd_message.set_token('ERR_MSG','The Term Template is required when index commencement date is same as lease commencement date. Please provided a valid term template');
2800: fnd_msg_pub.ADD;
2801: RAISE fnd_api.g_exc_error;
2802: END IF;
2803:
2804: ELSIF (( p_rent_index_rec.term_template_id IS NOT NULL
2805: AND p_rent_index_rec.term_template_id <> pn_lease_utils.g_pn_miss_num))
2830: , p_lookup_meaning => p_rent_index_rec.negative_rent_type_meaning
2831: , p_lookup_type => l_negative_rent_type
2832: , x_lookup_type_code => p_rent_index_rec.negative_rent_type
2833: , x_return_status => x_return_status);
2834: IF x_return_status<>fnd_api.g_ret_sts_success
2835: THEN
2836: l_error_count:=l_error_count+1;
2837: fnd_message.set_name('PN','PN_INDEX_NEGTV_RENT_NOT_EXISTS');
2838: fnd_msg_pub.ADD;
2835: THEN
2836: l_error_count:=l_error_count+1;
2837: fnd_message.set_name('PN','PN_INDEX_NEGTV_RENT_NOT_EXISTS');
2838: fnd_msg_pub.ADD;
2839: RAISE fnd_api.g_exc_error;
2840: END IF;
2841: END IF;
2842:
2843:
2873: , p_lookup_meaning => p_rent_index_rec.spread_frequency_meaning
2874: , p_lookup_type => l_spread_frequency_type
2875: , x_lookup_type_code => p_rent_index_rec.spread_frequency
2876: , x_return_status => x_return_status);
2877: IF x_return_status<>fnd_api.g_ret_sts_success
2878: THEN
2879: l_error_count:=l_error_count+1;
2880: fnd_message.set_name('PN','PN_INDEX_SPREAD_FRQ_NOT_EXISTS');
2881: fnd_msg_pub.ADD;
2878: THEN
2879: l_error_count:=l_error_count+1;
2880: fnd_message.set_name('PN','PN_INDEX_SPREAD_FRQ_NOT_EXISTS');
2881: fnd_msg_pub.ADD;
2882: --RAISE fnd_api.g_exc_error;
2883: END IF;
2884: END IF;
2885:
2886: IF ( ( p_rent_index_rec.spread_frequency IS NULL
2911: THEN
2912: l_error_count:=l_error_count+1;
2913: fnd_message.set_name ('PN', 'PN_INVALID_ASSESS_YEAR');
2914: fnd_msg_pub.ADD;
2915: RAISE fnd_api.g_exc_error;
2916:
2917: END IF;
2918: ELSE
2919: p_rent_index_rec.index_finder_months := -2;
2939: THEN
2940: l_error_count:=l_error_count+1;
2941: fnd_message.set_name ('PN', 'PN_INDEX_INIT_BASIS_REQD');
2942: fnd_msg_pub.ADD;
2943: RAISE fnd_api.g_exc_error;
2944: --ELSE
2945: -- IF ( p_rent_index_rec.initial_basis IS NULL
2946: -- AND pn_r12_util_pkg.is_r12)
2947: -- THEN
2970: THEN
2971: l_error_count:=l_error_count+1;
2972: fnd_message.set_name ('PN', 'PN_ZERO_INDEX_MULTIPLIER');
2973: fnd_msg_pub.ADD;
2974: RAISE fnd_api.g_exc_error;
2975: END IF;
2976: END IF;
2977:
2978: pn_index_rent_pvt.DEBUG ( g_pkg_name
3025: , p_lookup_type => l_constraint_proration_type
3026: , x_lookup_type_code => p_rent_index_rec.proration_rule
3027: , x_return_status => x_return_status);
3028:
3029: IF x_return_status<>fnd_api.g_ret_sts_success
3030: THEN
3031: l_error_count:=l_error_count+1;
3032: fnd_message.set_name('PN','PN_INDEX_PRARATION_NOT_EXISTS');
3033: fnd_msg_pub.ADD;
3030: THEN
3031: l_error_count:=l_error_count+1;
3032: fnd_message.set_name('PN','PN_INDEX_PRARATION_NOT_EXISTS');
3033: fnd_msg_pub.ADD;
3034: RAISE fnd_api.g_exc_error;
3035: END IF;
3036: END IF;
3037: pn_index_rent_pvt.DEBUG ( g_pkg_name
3038: || g_program_unit||
3115: --fnd_message.set_name ('PN', 'PN_INVALID_BASIS_TYPE');
3116: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
3117: fnd_message.set_token('ERR_MSG', 'Please provide ROLLING\COMPOUND basis type for default reference period(Previous Year - Use Previous Current Index )');
3118: fnd_msg_pub.ADD;
3119: RAISE fnd_api.g_exc_error;
3120: ELSIF p_rent_index_rec.reference_period = 'BASE_YEAR'
3121: AND p_rent_index_rec.basis_type <> 'FIXED'
3122: THEN
3123: l_error_count:=l_error_count+1;
3122: THEN
3123: l_error_count:=l_error_count+1;
3124: fnd_message.set_name ('PN', 'PN_INVALID_BASIS_TYPE');
3125: fnd_msg_pub.ADD;
3126: RAISE fnd_api.g_exc_error;
3127: END IF;
3128:
3129: -- Added by Vivek on 22Nov2010
3130: IF p_rent_index_rec.basis_type IS NOT NULL
3170: --fnd_message.set_name ('PN', 'PN_INDEX_REQD_FLDS_INDEX_ONLY');--PN_INDEX_BASE_INDEX_REQUIRED
3171: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
3172: fnd_message.set_token('ERR_MSG', 'The Rent Increase Amount cannot be calculated. For an Index Only relation, the Index Id and Base Index are required');
3173: fnd_msg_pub.ADD;
3174: RAISE fnd_api.g_exc_error;
3175: END IF;
3176:
3177:
3178: IF ( p_rent_index_rec.base_year IS NULL
3181: l_error_count:=l_error_count+1;
3182: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
3183: fnd_message.set_token('ERR_MSG', 'Base Year should be provided when relationship default is '||p_rent_index_rec.relationship_default);
3184: fnd_msg_pub.ADD;
3185: RAISE fnd_api.g_exc_error;
3186: END IF;
3187:
3188: ELSIF p_rent_index_rec.relationship_default IS NOT NULL
3189: AND p_rent_index_rec.relationship_default <> pn_index_rent_utils.g_pn_miss_char
3195: --THEN
3196: --l_error_count:=l_error_count+1;
3197: --fnd_message.set_name ('PN', 'PN_INDEX_REQD_FLDS_BASIS_ONLY');
3198: --fnd_msg_pub.ADD;
3199: --RAISE fnd_api.g_exc_error;
3200: -- p_rent_index_rec.basis_percent_default:=0;
3201:
3202: --END IF;
3203: IF LTRIM (RTRIM (p_operation)) <>'UPDATE_INDEX_RENT' -- Added by vivek on 19NOV2010
3227: THEN
3228: l_error_count:=l_error_count+1;
3229: fnd_message.set_name ('PN', 'PN_INDEX_REQD_FLDS_GT_LT_ONLY'); --PN_INVALID_BASIS_TYPE
3230: fnd_msg_pub.ADD;
3231: RAISE fnd_api.g_exc_error;
3232: END IF;
3233:
3234: IF ( p_rent_index_rec.base_year IS NULL
3235: OR p_rent_index_rec.base_year =pn_index_rent_utils.g_pn_miss_date)
3237: l_error_count:=l_error_count+1;
3238: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
3239: fnd_message.set_token('ERR_MSG', 'Base Year should be provided when relationship_default is '||p_rent_index_rec.relationship_default);
3240: fnd_msg_pub.ADD;
3241: RAISE fnd_api.g_exc_error;
3242: END IF;
3243:
3244: END IF;
3245: pn_index_rent_pvt.DEBUG ( g_pkg_name
3257: THEN
3258: l_error_count:=l_error_count+1;
3259: fnd_message.set_name ('PN', 'PN_MISS_TERM_TEMP_DATA');
3260: fnd_msg_pub.ADD;
3261: RAISE fnd_api.g_exc_error;
3262: END IF;
3263:
3264: IF pn_r12_util_pkg.is_r12
3265: THEN
3270: THEN
3271: l_error_count:=l_error_count+1;
3272: fnd_message.set_name ('PN', 'PN_NO_TCC_FOUND');
3273: fnd_msg_pub.ADD;
3274: RAISE fnd_api.g_exc_error;
3275: --RAISE;
3276: ELSIF l_tccstatus = 'PN_UPG_TCC'
3277: THEN
3278: l_error_count:=l_error_count+1;
3277: THEN
3278: l_error_count:=l_error_count+1;
3279: fnd_message.set_name ('PN', 'PN_UPG_TCC_FOUND');
3280: fnd_msg_pub.ADD;
3281: RAISE fnd_api.g_exc_error;
3282: END IF;
3283: END IF;
3284: END IF;
3285:
3299:
3300: l_error_count:=l_error_count+1;
3301: fnd_message.set_name ('PN', 'PN_PRORATION_PERIOD_START_DATE');
3302: fnd_msg_pub.ADD;
3303: RAISE fnd_api.g_exc_error;
3304: END IF;
3305: END IF;
3306:
3307: IF p_rent_index_rec.proration_rule = 'NO_PRORATION'
3324: l_error_count:=l_error_count+1;
3325: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
3326: fnd_message.set_token('ERR_MSG', 'Proration period start data cannot be provided for No Proration Rule');
3327: fnd_msg_pub.ADD;
3328: RAISE fnd_api.g_exc_error;
3329: END IF;
3330: ELSE
3331: p_rent_index_rec.proration_period_start_date := p_rent_index_rec.commencement_date;
3332:
3363:
3364: IF l_msg = 'PN_TERM_DATE_INV_APPR_PRDS' THEN
3365: fnd_message.set_name ('PN', l_msg);
3366: fnd_msg_pub.ADD;
3367: RAISE fnd_api.g_exc_error;
3368:
3369: ELSE
3370: fnd_message.set_name ('PN', l_msg);
3371: fnd_msg_pub.ADD;
3383: l_error_count:=l_error_count+1;
3384: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
3385: fnd_message.set_token('ERR_MSG', 'Rounding flag should be Y/N');
3386: fnd_msg_pub.ADD;
3387: RAISE fnd_api.g_exc_error;
3388: END IF;
3389:
3390: IF p_rent_index_rec.aggregation_flag IS NULL OR p_rent_index_rec.aggregation_flag = pn_index_rent_utils.g_pn_miss_char
3391: THEN
3397: l_error_count:=l_error_count+1;
3398: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
3399: fnd_message.set_token('ERR_MSG', 'Aggregation flag should be Y/N');
3400: fnd_msg_pub.ADD;
3401: RAISE fnd_api.g_exc_error;
3402: END IF;
3403:
3404:
3405: IF p_rent_index_rec.retain_initial_basis_flag IS NULL OR p_rent_index_rec.retain_initial_basis_flag = pn_index_rent_utils.g_pn_miss_char
3412: l_error_count:=l_error_count+1;
3413: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
3414: fnd_message.set_token('ERR_MSG', 'Aggregation flag should be Y/N');
3415: fnd_msg_pub.ADD;
3416: RAISE fnd_api.g_exc_error;
3417: END IF;
3418:
3419: IF p_rent_index_rec.vr_nbp_flag IS NOT NULL AND p_rent_index_rec.vr_nbp_flag <> pn_index_rent_utils.g_pn_miss_char
3420: THEN
3426: l_error_count:=l_error_count+1;
3427: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
3428: fnd_message.set_token('ERR_MSG', 'Natural Breakpoint basis flag should be N or NULL');
3429: fnd_msg_pub.ADD;
3430: RAISE fnd_api.g_exc_error;
3431: END IF;
3432: END IF;
3433: -- Validate Index Rent Descriptive Fields
3434: validate_index_desc_flex ( p_rent_index_rec => p_rent_index_rec
3461: END IF;
3462: END IF;
3463:
3464: IF l_error_count>0 THEN
3465: x_return_status:=fnd_api.g_ret_sts_error;
3466: ELSE
3467: x_return_status:=fnd_api.g_ret_sts_success;
3468: END IF;
3469: EXCEPTION
3463:
3464: IF l_error_count>0 THEN
3465: x_return_status:=fnd_api.g_ret_sts_error;
3466: ELSE
3467: x_return_status:=fnd_api.g_ret_sts_success;
3468: END IF;
3469: EXCEPTION
3470: WHEN fnd_api.g_exc_error
3471: THEN
3466: ELSE
3467: x_return_status:=fnd_api.g_ret_sts_success;
3468: END IF;
3469: EXCEPTION
3470: WHEN fnd_api.g_exc_error
3471: THEN
3472: pn_index_rent_pvt.DEBUG ( 'Error occurred in '
3473: || g_pkg_name
3474: || g_program_unit
3474: || g_program_unit
3475: || '-'
3476: || SQLERRM
3477: , 5);
3478: x_return_status := fnd_api.g_ret_sts_error;
3479:
3480: WHEN OTHERS
3481: THEN
3482: pn_index_rent_pvt.DEBUG ( 'Exception in '
3484: || g_program_unit
3485: || '-'
3486: || SQLERRM
3487: , 5);
3488: x_return_status := fnd_api.g_ret_sts_unexp_error;
3489: END validate_index_rent_rec;
3490:
3491: ----------------------------------------------------------------------
3492: -- Prcoedure to update Constraint Record with the provided new values
3500: IS
3501: l_rowid VARCHAR2 (18) := NULL;
3502: BEGIN
3503: g_program_unit := 'UPDATE_CONSTRAINT_ROW';
3504: x_return_status := fnd_api.g_ret_sts_success;
3505: /*pn_index_lease_constraints_pkg.check_unq_constraint_scope (
3506: x_return_status
3507: ,x_index_constraint_id
3508: ,x_index_lease_id
3557: || g_program_unit||
3558: ' - pn_index_lease_constraints_pkg.update_row successful. index_constraint_id - '||p_index_constraint_rec.index_constraint_id
3559: ,3);
3560: EXCEPTION
3561: WHEN fnd_api.g_exc_error
3562: THEN
3563: pn_index_rent_pvt.DEBUG ( 'Error occurred in '
3564: || g_pkg_name
3565: || g_program_unit
3565: || g_program_unit
3566: || '-'
3567: || SQLERRM
3568: , 5);
3569: x_return_status := fnd_api.g_ret_sts_error;
3570:
3571: WHEN OTHERS
3572: THEN
3573: pn_index_rent_pvt.DEBUG ( 'Exception in '
3575: || g_program_unit
3576: || '-'
3577: || SQLERRM
3578: , 5);
3579: x_return_status := fnd_api.g_ret_sts_unexp_error;
3580: END update_constraint_row;
3581:
3582: ----------------------------------------------------------------------
3583: -- Prcoedure to insert Constraint Record with the provided values
3592: l_rowid VARCHAR2 (18) := NULL;
3593: BEGIN
3594: g_program_unit := 'INSERT_INDEX_CONSTRAINT_ROW';
3595:
3596: x_return_status := fnd_api.g_ret_sts_success;
3597:
3598: init_index_constraint_rec (p_index_constraint_rec => p_index_constraint_rec
3599: , x_return_status => x_return_status
3600: , x_msg_count => x_msg_count
3660: ' - pn_index_lease_constraints_pkg.insert_row successful.Index_constraint_id - '||p_index_constraint_rec.index_constraint_id
3661: ,3);
3662: IF p_index_constraint_rec.index_constraint_id >0
3663: THEN
3664: x_return_status := fnd_api.g_ret_sts_success;
3665: ELSE
3666: RAISE fnd_api.g_exc_error;
3667: END IF;
3668: EXCEPTION
3662: IF p_index_constraint_rec.index_constraint_id >0
3663: THEN
3664: x_return_status := fnd_api.g_ret_sts_success;
3665: ELSE
3666: RAISE fnd_api.g_exc_error;
3667: END IF;
3668: EXCEPTION
3669: WHEN fnd_api.g_exc_error
3670: THEN
3665: ELSE
3666: RAISE fnd_api.g_exc_error;
3667: END IF;
3668: EXCEPTION
3669: WHEN fnd_api.g_exc_error
3670: THEN
3671: pn_index_rent_pvt.DEBUG ( 'Error occurred in '
3672: || g_pkg_name
3673: || g_program_unit
3675: || SQLERRM
3676: , 5);
3677:
3678:
3679: x_return_status := fnd_api.g_ret_sts_error;
3680:
3681: WHEN OTHERS
3682: THEN
3683: pn_index_rent_pvt.DEBUG ( 'Exception in '
3686: || '-'
3687: || SQLERRM
3688: , 5);
3689:
3690: x_return_status := fnd_api.g_ret_sts_unexp_error;
3691: END insert_index_constraint_row;
3692:
3693: ------------------------------------------------------------------------------
3694: -- Prcoedure to get the changed Constraint values to update Constraint Record
3740:
3741: --l_error_count NUMBER := 0;
3742: BEGIN
3743: g_program_unit := 'GET_CHANGED_CONSTRAINT_REC';
3744: x_return_status := fnd_api.g_ret_sts_success;
3745:
3746: pn_index_rent_pvt.DEBUG ( g_pkg_name
3747: || g_program_unit
3748: || ' Inside get_changed_constraint_rec procedure'
3758: pn_index_rent_pvt.DEBUG ( g_pkg_name
3759: || g_program_unit
3760: || ' CURSOR c_old_constraint_values failed to return the existing record'
3761: , 3);
3762: x_return_status := fnd_api.g_ret_sts_error;
3763: ELSE
3764: x_return_status := fnd_api.g_ret_sts_success;
3765: END IF;
3766:
3760: || ' CURSOR c_old_constraint_values failed to return the existing record'
3761: , 3);
3762: x_return_status := fnd_api.g_ret_sts_error;
3763: ELSE
3764: x_return_status := fnd_api.g_ret_sts_success;
3765: END IF;
3766:
3767:
3768: IF p_index_constraint_rec.SCOPE IS NULL OR p_index_constraint_rec.SCOPE =pn_index_rent_utils.g_pn_miss_char
3998: THEN
3999: CLOSE c_old_constraint_values;
4000: END IF;
4001:
4002: x_return_status := fnd_api.g_ret_sts_unexp_error;
4003: END get_changed_constraint_rec;
4004:
4005: ----------------------------------------------------------------------
4006: -- Prcoedure to validate Constraint Record for the provided values
4007: ----------------------------------------------------------------------
4008: PROCEDURE validate_index_constraint_tbl (
4009: p_index_constraint_tbl IN OUT NOCOPY index_constraint_tbl
4010: , p_rent_index_rec IN OUT NOCOPY rent_index_rec
4011: , p_validate IN VARCHAR2 := fnd_api.g_false
4012: , p_operation IN VARCHAR2 DEFAULT 'CREATE_CONSTRAINTS'
4013: , x_return_status OUT NOCOPY VARCHAR2
4014: , x_msg_count OUT NOCOPY NUMBER
4015: , x_msg_data OUT NOCOPY VARCHAR2)
4019: l_error_count NUMBER := 0;
4020: --l_operation VARCHAR2 (30);
4021: BEGIN
4022: g_program_unit := 'VALIDATE_INDEX_CONSTRAINT_TBL';
4023: x_return_status := fnd_api.g_ret_sts_success;
4024: pn_index_rent_pvt.DEBUG ( g_pkg_name
4025: || g_program_unit
4026: || ' Inside validate_index_constraint_tbl procedure'
4027: , 3);
4047: , p_lookup_meaning => p_index_constraint_tbl (i).scope_meaning
4048: , p_lookup_type => l_constraint_proration_type
4049: , x_lookup_type_code => p_index_constraint_tbl (i).SCOPE
4050: , x_return_status => x_return_status);
4051: IF x_return_status<>fnd_api.g_ret_sts_success
4052: THEN
4053: fnd_message.set_name('PN','PN_CONSTRAINT_SCOPE_NOT_EXISTS');
4054: fnd_msg_pub.ADD;
4055: RAISE fnd_api.g_exc_error;
4051: IF x_return_status<>fnd_api.g_ret_sts_success
4052: THEN
4053: fnd_message.set_name('PN','PN_CONSTRAINT_SCOPE_NOT_EXISTS');
4054: fnd_msg_pub.ADD;
4055: RAISE fnd_api.g_exc_error;
4056: END IF;
4057: END IF;
4058:
4059:
4066: l_error_count:=l_error_count+1;
4067: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
4068: fnd_message.set_token('ERR_MSG', 'Both Constraint amounts and percentages cannot be provided. Please provide either amounts or percentages ');
4069: fnd_msg_pub.ADD;
4070: RAISE fnd_api.g_exc_error;
4071: END IF;
4072:
4073: IF ( p_index_constraint_tbl (i).minimum_amount IS NOT NULL
4074: AND p_index_constraint_tbl (i).minimum_amount <> pn_index_rent_utils.g_pn_miss_num)
4085: -- NULL;
4086: -- ELSE
4087: fnd_message.set_name ('PN', 'PN_INDEX_CONSTRAINT_AMOUNT');
4088: fnd_msg_pub.ADD;
4089: RAISE fnd_api.g_exc_error;
4090: END IF;
4091: --END IF;
4092: ELSE
4093: p_index_constraint_tbl (i).minimum_amount:=NULL;
4112: --ELSE
4113:
4114: fnd_message.set_name ('PN', 'PN_INDEX_CONSTRAINT_AMOUNT');
4115: fnd_msg_pub.ADD;
4116: RAISE fnd_api.g_exc_error;
4117: END IF;
4118: --END IF;
4119: ELSE
4120: p_index_constraint_tbl (i).maximum_amount:=NULL;
4136: -- NULL;
4137: --ELSE
4138: fnd_message.set_name ('PN', 'PN_INDEX_CONSTRAINT_PERCENT');
4139: fnd_msg_pub.ADD;
4140: RAISE fnd_api.g_exc_error;
4141: END IF;
4142: --END IF;
4143: ELSE
4144: p_index_constraint_tbl (i).minimum_percent:=NULL;
4159: -- NULL;
4160: --ELSE
4161: fnd_message.set_name ('PN', 'PN_INDEX_CONSTRAINT_PERCENT');
4162: fnd_msg_pub.ADD;
4163: RAISE fnd_api.g_exc_error;
4164: END IF;
4165: --END IF;
4166: ELSE
4167: p_index_constraint_tbl (i).maximum_percent:=NULL;
4168: END IF;
4169:
4170: IF LTRIM (RTRIM (p_operation)) <> 'UPDATE_INDEX_RENT_CONSTRAINTS'
4171: THEN
4172: IF (x_return_status = fnd_api.g_ret_sts_success) AND p_validate=fnd_api.g_false
4173: THEN
4174:
4175: pn_index_rent_pvt.DEBUG(
4176: g_pkg_name
4184: , x_msg_count => x_msg_count
4185: , x_msg_data => x_msg_data);
4186: END IF;
4187:
4188: IF x_return_status in (fnd_api.g_ret_sts_error,fnd_api.g_ret_sts_unexp_error)
4189: THEN
4190: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
4191: fnd_message.set_token('ERR_MSG', 'Cannot create multiple Constraint records with the scope');
4192: fnd_msg_pub.ADD;
4190: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
4191: fnd_message.set_token('ERR_MSG', 'Cannot create multiple Constraint records with the scope');
4192: fnd_msg_pub.ADD;
4193: --ROLLBACK;
4194: --RAISE fnd_api.g_exc_error;
4195: END IF;
4196:
4197: END IF;
4198:
4219: , x_msg_count => x_msg_count
4220: , x_msg_data => x_msg_data);
4221:
4222:
4223: IF x_return_status =fnd_api.g_ret_sts_error
4224: THEN
4225: RAISE fnd_api.g_exc_error;
4226: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4227: THEN
4221:
4222:
4223: IF x_return_status =fnd_api.g_ret_sts_error
4224: THEN
4225: RAISE fnd_api.g_exc_error;
4226: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4227: THEN
4228:
4229: RAISE fnd_api.g_exc_unexpected_error;
4222:
4223: IF x_return_status =fnd_api.g_ret_sts_error
4224: THEN
4225: RAISE fnd_api.g_exc_error;
4226: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4227: THEN
4228:
4229: RAISE fnd_api.g_exc_unexpected_error;
4230: END IF;
4225: RAISE fnd_api.g_exc_error;
4226: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4227: THEN
4228:
4229: RAISE fnd_api.g_exc_unexpected_error;
4230: END IF;
4231:
4232: END IF;
4233:
4234: END IF;
4235:
4236:
4237:
4238: IF (x_return_status = fnd_api.g_ret_sts_success) AND p_validate=fnd_api.g_false
4239: THEN
4240:
4241:
4242: pn_index_rent_pvt.DEBUG ( g_pkg_name
4268: END IF;
4269:
4270: END IF;
4271:
4272: IF x_return_status in (fnd_api.g_ret_sts_error,fnd_api.g_ret_sts_unexp_error)
4273: THEN
4274: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
4275: fnd_message.set_token('ERR_MSG', 'Cannot update multiple Constraint records with the scope');
4276: fnd_msg_pub.ADD;
4274: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
4275: fnd_message.set_token('ERR_MSG', 'Cannot update multiple Constraint records with the scope');
4276: fnd_msg_pub.ADD;
4277: --ROLLBACK;
4278: --RAISE fnd_api.g_exc_error;
4279: END IF;
4280:
4281:
4282:
4287: , x_return_status => x_return_status
4288: , x_msg_count => x_msg_count
4289: , x_msg_data => x_msg_count);
4290: EXCEPTION
4291: WHEN fnd_api.g_exc_error
4292: THEN
4293: pn_index_rent_pvt.DEBUG ( 'Error occurred in '
4294: || g_pkg_name
4295: || g_program_unit
4295: || g_program_unit
4296: || '-'
4297: || SQLERRM
4298: , 5);
4299: x_return_status := fnd_api.g_ret_sts_error;
4300:
4301: WHEN OTHERS
4302: THEN
4303: pn_index_rent_pvt.DEBUG ( 'Exception in '
4305: || g_program_unit
4306: || '-'
4307: || SQLERRM
4308: , 5);
4309: x_return_status := fnd_api.g_ret_sts_unexp_error;
4310: END validate_index_constraint_tbl;
4311:
4312: ---------------------------------------------------------------------------------------------
4313: -- Prcoedure to validate create Index Rent and Constraint Records with the passed values
4332: l_operation := 'CREATE_RENT_INDEX';
4333: g_pkg_name := 'PN_INDEX_RENT_PVT.';
4334: g_program_unit := 'CREATE_INDEX_RENT';
4335:
4336: x_return_status := fnd_api.g_ret_sts_success;
4337:
4338: IF p_rent_index_rec.org_id IS NOT NULL AND p_rent_index_rec.org_id<>pn_index_rent_utils.g_pn_miss_num
4339: THEN
4340: IF p_rent_index_rec.org_id<>fnd_global.org_id
4341: THEN
4342: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
4343: fnd_message.set_token('ERR_MSG', 'Operating unit provided do not exists in the list of Operating Units that are allowed access to, for a responsibility');
4344: fnd_msg_pub.ADD;
4345: RAISE fnd_api.g_exc_error;
4346: END IF;
4347: ELSE
4348: p_rent_index_rec.org_id:=FND_PROFILE.VALUE('ORG_ID');
4349: END IF;
4365: , x_return_status => x_return_status
4366: , x_msg_count => x_msg_count
4367: , x_msg_data => x_msg_data);
4368:
4369: IF (x_return_status = fnd_api.g_ret_sts_error)
4370: THEN
4371:
4372: pn_index_rent_pvt.DEBUG ( g_pkg_name
4373: || g_program_unit
4373: || g_program_unit
4374: || ' validate_index_rent_rec procedure returned error'
4375: , 3);
4376:
4377: RAISE fnd_api.g_exc_error;
4378:
4379: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4380: THEN
4381:
4375: , 3);
4376:
4377: RAISE fnd_api.g_exc_error;
4378:
4379: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4380: THEN
4381:
4382: pn_index_rent_pvt.DEBUG ( g_pkg_name
4383: || g_program_unit
4383: || g_program_unit
4384: || ' validate_index_rent_rec procedure returned unexpected error'
4385: , 3);
4386:
4387: RAISE fnd_api.g_exc_unexpected_error;
4388: END IF;
4389:
4390:
4391: pn_index_rent_pvt.DEBUG (' Calling INSERT_INDEX_RENT_ROW procedure from main', 3);
4389:
4390:
4391: pn_index_rent_pvt.DEBUG (' Calling INSERT_INDEX_RENT_ROW procedure from main', 3);
4392:
4393: IF (x_return_status = fnd_api.g_ret_sts_success)
4394: THEN
4395: pn_index_rent_pvt.DEBUG ( g_pkg_name
4396: || g_program_unit
4397: || ' Calling insert_index_rent_row procedure from main'
4396: || g_program_unit
4397: || ' Calling insert_index_rent_row procedure from main'
4398: , 3);
4399:
4400: IF p_validate=fnd_api.g_false
4401: THEN
4402:
4403: pn_index_rent_pvt.DEBUG ( g_pkg_name
4404: || g_program_unit
4410: , x_msg_data => x_msg_data);
4411: END IF;
4412: END IF;
4413:
4414: IF (x_return_status = fnd_api.g_ret_sts_error)
4415: THEN
4416: RAISE fnd_api.g_exc_error;
4417: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4418: THEN
4412: END IF;
4413:
4414: IF (x_return_status = fnd_api.g_ret_sts_error)
4415: THEN
4416: RAISE fnd_api.g_exc_error;
4417: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4418: THEN
4419: RAISE fnd_api.g_exc_unexpected_error;
4420: END IF;
4413:
4414: IF (x_return_status = fnd_api.g_ret_sts_error)
4415: THEN
4416: RAISE fnd_api.g_exc_error;
4417: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4418: THEN
4419: RAISE fnd_api.g_exc_unexpected_error;
4420: END IF;
4421:
4415: THEN
4416: RAISE fnd_api.g_exc_error;
4417: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4418: THEN
4419: RAISE fnd_api.g_exc_unexpected_error;
4420: END IF;
4421:
4422: IF (x_return_status = fnd_api.g_ret_sts_success) and (p_index_constraint_tbl.count>0)
4423: THEN
4418: THEN
4419: RAISE fnd_api.g_exc_unexpected_error;
4420: END IF;
4421:
4422: IF (x_return_status = fnd_api.g_ret_sts_success) and (p_index_constraint_tbl.count>0)
4423: THEN
4424:
4425: pn_index_rent_pvt.DEBUG ( g_pkg_name
4426: || g_program_unit
4440: , x_msg_count => x_msg_count
4441: , x_msg_data => x_msg_data);
4442: END IF;
4443:
4444: IF (x_return_status = fnd_api.g_ret_sts_error)
4445: THEN
4446:
4447: RAISE fnd_api.g_exc_error;
4448: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4443:
4444: IF (x_return_status = fnd_api.g_ret_sts_error)
4445: THEN
4446:
4447: RAISE fnd_api.g_exc_error;
4448: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4449: THEN
4450:
4451: RAISE fnd_api.g_exc_unexpected_error;
4444: IF (x_return_status = fnd_api.g_ret_sts_error)
4445: THEN
4446:
4447: RAISE fnd_api.g_exc_error;
4448: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4449: THEN
4450:
4451: RAISE fnd_api.g_exc_unexpected_error;
4452: END IF;
4447: RAISE fnd_api.g_exc_error;
4448: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4449: THEN
4450:
4451: RAISE fnd_api.g_exc_unexpected_error;
4452: END IF;
4453:
4454: l_operation := 'Generating Periods ';
4455:
4455:
4456:
4457:
4458:
4459: IF (x_return_status = fnd_api.g_ret_sts_success)
4460: THEN
4461:
4462: pn_index_rent_pvt.DEBUG ( g_pkg_name
4463: || g_program_unit
4490: || g_program_unit
4491: || ' End of create_index_rent'
4492: , 3);
4493: EXCEPTION
4494: WHEN fnd_api.g_exc_error
4495: THEN
4496: pn_index_rent_pvt.DEBUG ( 'Error occurred in '
4497: || g_pkg_name
4498: || g_program_unit
4498: || g_program_unit
4499: || '-'
4500: || SQLERRM
4501: , 5);
4502: x_return_status := fnd_api.g_ret_sts_error;
4503:
4504: WHEN OTHERS
4505: THEN
4506: pn_index_rent_pvt.DEBUG ( 'Exception in '
4508: || g_program_unit
4509: || '-'
4510: || SQLERRM
4511: , 5);
4512: x_return_status := fnd_api.g_ret_sts_unexp_error;
4513: END create_index_rent;
4514:
4515: ---------------------------------------------------------------------------------------------
4516: -- Prcoedure to validate update Index Rent and Constraint Records with the passed values
4533: BEGIN
4534: l_operation := 'UPDATE_INDEX_RENT';
4535: g_pkg_name := 'PN_INDEX_RENT_PVT.';
4536: g_program_unit := NULL;
4537: x_return_status := fnd_api.g_ret_sts_success;
4538:
4539: IF p_rent_index_rec.org_id IS NOT NULL AND p_rent_index_rec.org_id<>pn_index_rent_utils.g_pn_miss_num
4540: THEN
4541: IF p_rent_index_rec.org_id<>fnd_global.org_id
4542: THEN
4543: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
4544: fnd_message.set_token('ERR_MSG', 'Operating unit provided do not exists in the list of Operating Units that are allowed access to, for a responsibility');
4545: fnd_msg_pub.ADD;
4546: RAISE fnd_api.g_exc_error;
4547: END IF;
4548: ELSE
4549: p_rent_index_rec.org_id:=FND_PROFILE.VALUE('ORG_ID');
4550: END IF;
4559: , p_operation => l_operation
4560: , x_return_status => x_return_status
4561: , x_msg_count => x_msg_count
4562: , x_msg_data => x_msg_data);
4563: IF (x_return_status = fnd_api.g_ret_sts_error)
4564: THEN
4565: RAISE fnd_api.g_exc_error;
4566: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4567: THEN
4561: , x_msg_count => x_msg_count
4562: , x_msg_data => x_msg_data);
4563: IF (x_return_status = fnd_api.g_ret_sts_error)
4564: THEN
4565: RAISE fnd_api.g_exc_error;
4566: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4567: THEN
4568: RAISE fnd_api.g_exc_unexpected_error;
4569: END IF;
4562: , x_msg_data => x_msg_data);
4563: IF (x_return_status = fnd_api.g_ret_sts_error)
4564: THEN
4565: RAISE fnd_api.g_exc_error;
4566: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4567: THEN
4568: RAISE fnd_api.g_exc_unexpected_error;
4569: END IF;
4570:
4564: THEN
4565: RAISE fnd_api.g_exc_error;
4566: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4567: THEN
4568: RAISE fnd_api.g_exc_unexpected_error;
4569: END IF;
4570:
4571:
4572:
4569: END IF;
4570:
4571:
4572:
4573: IF (x_return_status = fnd_api.g_ret_sts_success)
4574: THEN
4575: IF p_validate=fnd_api.g_false
4576: THEN
4577: pn_index_rent_pvt.DEBUG ( g_pkg_name
4571:
4572:
4573: IF (x_return_status = fnd_api.g_ret_sts_success)
4574: THEN
4575: IF p_validate=fnd_api.g_false
4576: THEN
4577: pn_index_rent_pvt.DEBUG ( g_pkg_name
4578: || g_program_unit
4579: || ' Calling update_index_rent_row procedure from update main'
4587:
4588: END IF;
4589: END IF;
4590:
4591: IF (x_return_status = fnd_api.g_ret_sts_error)
4592: THEN
4593: RAISE fnd_api.g_exc_error;
4594: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4595: THEN
4589: END IF;
4590:
4591: IF (x_return_status = fnd_api.g_ret_sts_error)
4592: THEN
4593: RAISE fnd_api.g_exc_error;
4594: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4595: THEN
4596: RAISE fnd_api.g_exc_unexpected_error;
4597: END IF;
4590:
4591: IF (x_return_status = fnd_api.g_ret_sts_error)
4592: THEN
4593: RAISE fnd_api.g_exc_error;
4594: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4595: THEN
4596: RAISE fnd_api.g_exc_unexpected_error;
4597: END IF;
4598:
4592: THEN
4593: RAISE fnd_api.g_exc_error;
4594: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4595: THEN
4596: RAISE fnd_api.g_exc_unexpected_error;
4597: END IF;
4598:
4599:
4600: IF (x_return_status = fnd_api.g_ret_sts_success) and (p_index_constraint_tbl.count>0)
4596: RAISE fnd_api.g_exc_unexpected_error;
4597: END IF;
4598:
4599:
4600: IF (x_return_status = fnd_api.g_ret_sts_success) and (p_index_constraint_tbl.count>0)
4601: THEN
4602:
4603: pn_index_rent_pvt.DEBUG ( g_pkg_name
4604: || g_program_unit
4615: , x_msg_count => x_msg_count
4616: , x_msg_data => x_msg_data);
4617: END IF;
4618:
4619: IF (x_return_status = fnd_api.g_ret_sts_error)
4620: THEN
4621: RAISE fnd_api.g_exc_error;
4622: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4623: THEN
4617: END IF;
4618:
4619: IF (x_return_status = fnd_api.g_ret_sts_error)
4620: THEN
4621: RAISE fnd_api.g_exc_error;
4622: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4623: THEN
4624: RAISE fnd_api.g_exc_unexpected_error;
4625: ELSE
4618:
4619: IF (x_return_status = fnd_api.g_ret_sts_error)
4620: THEN
4621: RAISE fnd_api.g_exc_error;
4622: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4623: THEN
4624: RAISE fnd_api.g_exc_unexpected_error;
4625: ELSE
4626:
4620: THEN
4621: RAISE fnd_api.g_exc_error;
4622: ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4623: THEN
4624: RAISE fnd_api.g_exc_unexpected_error;
4625: ELSE
4626:
4627: pn_index_lease_common_pkg.chk_for_approved_index_periods( p_index_lease_id => p_rent_index_rec.index_lease_id,
4628: p_index_lease_period_id => NULL,
4632: END IF;
4633:
4634: IF l_msg = 'PN_APPROVED_PERIODS_FOUND' THEN
4635: fnd_message.set_name ('PN', l_msg);
4636: RAISE fnd_api.g_exc_error;
4637: ELSE
4638: pn_index_rent_pvt.DEBUG (g_pkg_name
4639: || g_program_unit
4640: || ' Undo Periods if approved periods are not found'
4643: p_msg => l_msg
4644: );
4645: END IF;
4646:
4647: IF (x_return_status = fnd_api.g_ret_sts_success AND l_msg<>'PN_UNDO_PRDS_FAIL_APPROVE_PRDS')
4648: THEN
4649:
4650: pn_index_rent_pvt.DEBUG ( g_pkg_name
4651: || g_program_unit
4671: END IF;
4672: END IF;
4673:
4674: EXCEPTION
4675: WHEN fnd_api.g_exc_error
4676: THEN
4677: pn_index_rent_pvt.DEBUG ( 'Error occurred in '
4678: || g_pkg_name
4679: || g_program_unit
4679: || g_program_unit
4680: || '-'
4681: || SQLERRM
4682: , 5);
4683: x_return_status := fnd_api.g_ret_sts_error;
4684:
4685: WHEN OTHERS
4686: THEN
4687: pn_index_rent_pvt.DEBUG ( 'Exception in '
4689: || g_program_unit
4690: || '-'
4691: || SQLERRM
4692: , 5);
4693: x_return_status := fnd_api.g_ret_sts_unexp_error;
4694: END update_index_rent;
4695:
4696: END pn_index_rent_pvt;