[Home] [Help]
140: END IF;
141:
142: IF (AMS_DEBUG_HIGH_ON) THEN
143:
144: AMS_Utility_PVT.debug_message('SQL statement: '||l_sql);
145:
146: END IF;
147:
148: IF l_additional_where_clause IS NOT NULL THEN
354: IF (AMS_DEBUG_HIGH_ON) THEN
355:
356:
357:
358: AMS_Utility_PVT.debug_message(l_full_name||': start');
359:
360: END IF;
361:
362: IF FND_API.to_boolean(p_init_msg_list) THEN
386:
387: IF (p_evo_rec.event_level = 'MAIN' and l_copy_flag = 'Y' and p_evo_rec.event_object_type = 'EVEO') then
388: IF (AMS_DEBUG_HIGH_ON) THEN
389:
390: AMS_Utility_PVT.debug_message(l_full_name ||': copy header to offer');
391: END IF;
392: copy_ev_header_to_offer(
393: p_api_version => l_api_version,
394: p_init_msg_list => FND_API.g_false,
439: -- added sugupta 07/20/2000 for event agenda, stastuses shouldnt be defaulted to 1/NEW
440: -- for main event, while creation.. user status shud always be 1, system status always NEW
441:
442: IF l_evo_rec.event_level = 'MAIN' then
443: l_evo_rec.user_status_id := ams_utility_pvt.get_default_user_status('AMS_EVENT_STATUS','NEW');
444: l_evo_rec.system_status_code := 'NEW';
445: ELSE
446: -- pick up the correct system_status_code
447: IF l_evo_rec.user_status_id IS NOT NULL THEN
453:
454: ----------------------- validate -----------------------
455: IF (AMS_DEBUG_HIGH_ON) THEN
456:
457: AMS_Utility_PVT.debug_message(l_full_name ||': validate');
458: END IF;
459:
460:
461: validate_event_offer(
478: IF l_evo_rec.event_level = 'SUB' THEN
479: l_user_id := FND_GLOBAL.User_Id;
480: IF (AMS_DEBUG_HIGH_ON) THEN
481:
482: AMS_Utility_PVT.debug_message(' CHECK ACCESS l_user_id is ' ||l_user_id );
483: END IF;
484: IF l_user_id IS NOT NULL then
485: open get_res_id(l_user_id);
486: fetch get_res_id into l_res_id;
497:
498: -------------------------- insert --------------------------
499: IF (AMS_DEBUG_HIGH_ON) THEN
500:
501: AMS_Utility_PVT.debug_message(l_full_name ||': insert');
502: END IF;
503: -- ==========================================================
504: -- Following code is added by mukumar on 10/30/2000
505: -- the code will convert the transaction currency in to
1037: null;
1038: ELSE
1039: IF (AMS_DEBUG_HIGH_ON) THEN
1040:
1041: AMS_Utility_PVT.debug_message(l_full_name ||': transaction id for copy :'|| x_transaction_id );
1042: END IF;
1043: END IF;
1044: END IF;
1045:
1086: IF (AMS_DEBUG_HIGH_ON) THEN
1087:
1088:
1089:
1090: AMS_Utility_PVT.debug_message(l_full_name ||': end');
1091:
1092: END IF;
1093:
1094: EXCEPTION
1192: IF (AMS_DEBUG_HIGH_ON) THEN
1193:
1194:
1195:
1196: AMS_Utility_PVT.debug_message(l_full_name||': start');
1197:
1198: END IF;
1199:
1200: IF FND_API.to_boolean(p_init_msg_list) THEN
1215: ----------added sugupta 07/25/2000
1216: l_user_id := FND_GLOBAL.User_Id;
1217: IF (AMS_DEBUG_HIGH_ON) THEN
1218:
1219: AMS_Utility_PVT.debug_message(' CHECK ACCESS l_user_id is ' ||l_user_id );
1220: END IF;
1221: IF l_user_id IS NOT NULL then
1222: open get_res_id(l_user_id);
1223: fetch get_res_id into l_res_id;
1245:
1246: ------------------------ delete ------------------------
1247: IF (AMS_DEBUG_HIGH_ON) THEN
1248:
1249: AMS_Utility_PVT.debug_message(l_full_name ||': delete');
1250: END IF;
1251:
1252: UPDATE ams_event_offers_all_b
1253: SET active_flag = 'N'
1277: IF (AMS_DEBUG_HIGH_ON) THEN
1278:
1279:
1280:
1281: AMS_Utility_PVT.debug_message(l_full_name ||': end');
1282:
1283: END IF;
1284:
1285: EXCEPTION
1364:
1365: -------------------- initialize ------------------------
1366: IF (AMS_DEBUG_HIGH_ON) THEN
1367:
1368: AMS_Utility_PVT.debug_message(l_full_name||': start');
1369: END IF;
1370:
1371: IF FND_API.to_boolean(p_init_msg_list) THEN
1372: FND_MSG_PUB.initialize;
1385:
1386: ------------------------ lock -------------------------
1387: IF (AMS_DEBUG_HIGH_ON) THEN
1388:
1389: AMS_Utility_PVT.debug_message(l_full_name||': lock');
1390: END IF;
1391:
1392: OPEN c_evo_b;
1393: FETCH c_evo_b INTO l_evo_id;
1414: IF (AMS_DEBUG_HIGH_ON) THEN
1415:
1416:
1417:
1418: AMS_Utility_PVT.debug_message(l_full_name ||': end');
1419:
1420: END IF;
1421:
1422: EXCEPTION
1420: END IF;
1421:
1422: EXCEPTION
1423:
1424: WHEN AMS_Utility_PVT.resource_locked THEN
1425: x_return_status := FND_API.g_ret_sts_error;
1426: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1427: FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
1428: FND_MSG_PUB.add;
1669: IF (AMS_DEBUG_HIGH_ON) THEN
1670:
1671:
1672:
1673: AMS_Utility_PVT.debug_message(l_full_name||': entered update');
1674:
1675: END IF;
1676:
1677: -------------------- initialize -------------------------
1678: SAVEPOINT update_event_offer;
1679:
1680: IF (AMS_DEBUG_HIGH_ON) THEN
1681:
1682: AMS_Utility_PVT.debug_message(l_full_name||': start');
1683:
1684: END IF;
1685:
1686: IF FND_API.to_boolean(p_init_msg_list) THEN
1703: IF (AMS_DEBUG_HIGH_ON) THEN
1704:
1705:
1706:
1707: AMS_Utility_PVT.debug_message(l_full_name ||': validate');
1708:
1709: END IF;
1710: IF (AMS_DEBUG_HIGH_ON) THEN
1711:
1708:
1709: END IF;
1710: IF (AMS_DEBUG_HIGH_ON) THEN
1711:
1712: AMS_Utility_PVT.debug_message(p_evo_rec.event_offer_id ||': validate1');
1713: END IF;
1714: -- replace g_miss_char/num/date with current column values
1715: complete_evo_rec(p_evo_rec, l_evo_rec);
1716:
1717: IF (AMS_DEBUG_HIGH_ON) THEN
1718:
1719:
1720:
1721: AMS_Utility_PVT.debug_message(l_full_name ||': check items');
1722:
1723: END IF;
1724: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1725: check_evo_items(
1738: IF (AMS_DEBUG_HIGH_ON) THEN
1739:
1740:
1741:
1742: AMS_Utility_PVT.debug_message(l_full_name ||': check records');
1743:
1744: END IF;
1745:
1746: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1758: END IF;
1759: -- inter-entity level
1760: IF (AMS_DEBUG_HIGH_ON) THEN
1761:
1762: AMS_Utility_PVT.debug_message(l_full_name ||': check inter-entity');
1763: END IF;
1764: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_inter_entity THEN
1765: check_evo_inter_entity(
1766: p_evo_rec => p_evo_rec,
1797: -- check rules specific to update evo.. eg. status id should trigger approval workflow
1798: -- other checks regarding dates and numbers are included in check_evo_record
1799: IF (AMS_DEBUG_HIGH_ON) THEN
1800:
1801: AMS_Utility_PVT.debug_message(l_full_name ||': check update level');
1802: END IF;
1803:
1804: check_evo_update(
1805: p_evo_rec => l_evo_rec,
1814:
1815: -- handle source code update
1816: IF (AMS_DEBUG_HIGH_ON) THEN
1817:
1818: AMS_Utility_PVT.debug_message(l_full_name ||': update source code');
1819: END IF;
1820: -- only for main and non active events
1821:
1822:
1828: IF (AMS_DEBUG_HIGH_ON) THEN
1829:
1830:
1831:
1832: AMS_Utility_PVT.debug_message('The value of p_source_code is ' || l_evo_rec.source_code);
1833:
1834: END IF;
1835:
1836: l_traget_list_exists := FND_API.G_TRUE;
1839: FETCH c_evo INTO l_global_flag, l_source_code;
1840: CLOSE c_evo;
1841: IF (AMS_DEBUG_HIGH_ON) THEN
1842:
1843: AMS_Utility_PVT.debug_message('The value of p_source_code is ' || l_evo_rec.event_offer_id ||l_evo_rec.event_object_type );
1844: END IF;
1845: l_traget_list_exists := AMS_ScheduleRules_PVT.Target_Group_Exist( p_schedule_id => l_evo_rec.event_offer_id
1846: , p_obj_type => l_evo_rec.event_object_type);
1847: if (l_traget_list_exists = FND_API.G_FALSE) THEN
1846: , p_obj_type => l_evo_rec.event_object_type);
1847: if (l_traget_list_exists = FND_API.G_FALSE) THEN
1848: IF (AMS_DEBUG_HIGH_ON) THEN
1849:
1850: AMS_Utility_PVT.debug_message('l_traget_list_exists is false' );
1851: END IF;
1852: end if;
1853:
1854: IF p_evo_rec.source_code <> FND_API.g_miss_char
1913: THEN
1914: IF l_evo_rec.event_object_type = 'EVEO' THEN
1915: IF (AMS_DEBUG_HIGH_ON) THEN
1916:
1917: AMS_Utility_PVT.debug_message('Entered AMS_EvhRules_PVT.update_evo_source_code');
1918: END IF;
1919: -- extracting out source code modified by soagrawa
1920: -- 21-jan-2003 bug# 2761612
1921: AMS_EvhRules_PVT.update_evo_source_code(
1963: ----------added sugupta 07/25/2000
1964: l_user_id := FND_GLOBAL.User_Id;
1965: IF (AMS_DEBUG_HIGH_ON) THEN
1966:
1967: AMS_Utility_PVT.debug_message(' CHECK ACCESS l_user_id is ' ||l_user_id );
1968: END IF;
1969: if l_user_id IS NOT NULL then
1970: open get_res_id(l_user_id);
1971: fetch get_res_id into l_res_id;
1977: l_evo_id := l_evo_rec.event_offer_id;
1978: END IF;
1979: IF (AMS_DEBUG_HIGH_ON) THEN
1980:
1981: AMS_Utility_PVT.debug_message(l_evo_id || l_evo_rec.event_object_type || l_user_id ||': CHECK ACCESS');
1982: END IF;
1983:
1984:
1985: -- soagrawa added if clause on 11-feb-2003 for INTERNAL bug# 2795823
2041:
2042:
2043:
2044:
2045: AMS_Utility_PVT.debug_message( 'delv meth code from cursor ' || l_dlv_code);
2046:
2047:
2048: END IF;
2049: IF (AMS_DEBUG_HIGH_ON) THEN
2047:
2048: END IF;
2049: IF (AMS_DEBUG_HIGH_ON) THEN
2050:
2051: AMS_Utility_PVT.debug_message( 'delv meth Code in the Rec ' || l_evo_rec.event_delivery_method_code);
2052: END IF;
2053:
2054: IF (AMS_DEBUG_HIGH_ON) THEN
2055:
2054: IF (AMS_DEBUG_HIGH_ON) THEN
2055:
2056:
2057:
2058: AMS_Utility_PVT.debug_message( 'delv meth id from cursor ' || l_dlv_id);
2059:
2060: END IF;
2061:
2062: IF l_evo_rec.event_delivery_method_code <> FND_API.g_miss_char
2147: IF l_evo_rec.INVENTORY_ITEM <> FND_API.g_miss_char
2148: AND l_evo_rec.INVENTORY_ITEM IS NOT NULL THEN
2149: IF (AMS_DEBUG_HIGH_ON) THEN
2150:
2151: AMS_Utility_PVT.debug_message(l_full_name ||': calling create inv item');
2152: END IF;
2153: l_inv_item_number := l_evo_rec.INVENTORY_ITEM;
2154: l_inv_item_desc := l_evo_rec.EVENT_OFFER_NAME;
2155: l_inv_long_desc := l_evo_rec.DESCRIPTION;
2338: CLOSE c_get_min_max_session_time;
2339:
2340: IF l_min_session_time IS NOT NULL AND l_max_session_time IS NOT NULL THEN
2341: IF (AMS_DEBUG_HIGH_ON) THEN
2342: AMS_Utility_PVT.debug_message('l_min_session_time: '||to_char(l_min_session_time,'DD-MON-YYYY HH24:MI:SS'));
2343: AMS_Utility_PVT.debug_message('l_max_session_time: '||to_char(l_max_session_time,'DD-MON-YYYY HH24:MI:SS'));
2344: END IF;
2345: END IF;
2346:
2339:
2340: IF l_min_session_time IS NOT NULL AND l_max_session_time IS NOT NULL THEN
2341: IF (AMS_DEBUG_HIGH_ON) THEN
2342: AMS_Utility_PVT.debug_message('l_min_session_time: '||to_char(l_min_session_time,'DD-MON-YYYY HH24:MI:SS'));
2343: AMS_Utility_PVT.debug_message('l_max_session_time: '||to_char(l_max_session_time,'DD-MON-YYYY HH24:MI:SS'));
2344: END IF;
2345: END IF;
2346:
2347: IF l_min_session_time IS NOT NULL AND l_min_session_time < l_evo_rec.event_start_date_time THEN
2345: END IF;
2346:
2347: IF l_min_session_time IS NOT NULL AND l_min_session_time < l_evo_rec.event_start_date_time THEN
2348: IF (AMS_DEBUG_HIGH_ON) THEN
2349: AMS_Utility_PVT.debug_message('Event Start is after Min Session Start');
2350: END IF;
2351: Fnd_Message.set_name('AMS', 'AMS_EVT_SESS_STDT_CONFLICT');
2352: Fnd_Msg_Pub.ADD;
2353: x_return_status := Fnd_Api.g_ret_sts_error;
2355: END IF;
2356:
2357: IF l_max_session_time IS NOT NULL AND l_max_session_time > l_evo_rec.event_end_date_time THEN
2358: IF (AMS_DEBUG_HIGH_ON) THEN
2359: AMS_Utility_PVT.debug_message('Event End is before Max Session End');
2360: END IF;
2361: Fnd_Message.set_name('AMS', 'AMS_EVT_SESS_EDDT_CONFLICT');
2362: Fnd_Msg_Pub.ADD;
2363: x_return_status := Fnd_Api.g_ret_sts_error;
2479:
2480: -------------------------- update --------------------
2481: IF (AMS_DEBUG_HIGH_ON) THEN
2482:
2483: AMS_Utility_PVT.debug_message(l_full_name ||': update');
2484: END IF;
2485: IF (AMS_DEBUG_HIGH_ON) THEN
2486:
2487: AMS_Utility_PVT.debug_message('obj-ver number'||l_evo_rec.object_version_number);
2483: AMS_Utility_PVT.debug_message(l_full_name ||': update');
2484: END IF;
2485: IF (AMS_DEBUG_HIGH_ON) THEN
2486:
2487: AMS_Utility_PVT.debug_message('obj-ver number'||l_evo_rec.object_version_number);
2488: END IF;
2489: IF (AMS_DEBUG_HIGH_ON) THEN
2490:
2491: AMS_Utility_PVT.debug_message('obj-id'||l_evo_rec.event_offer_id);
2487: AMS_Utility_PVT.debug_message('obj-ver number'||l_evo_rec.object_version_number);
2488: END IF;
2489: IF (AMS_DEBUG_HIGH_ON) THEN
2490:
2491: AMS_Utility_PVT.debug_message('obj-id'||l_evo_rec.event_offer_id);
2492: END IF;
2493: IF (AMS_DEBUG_HIGH_ON) THEN
2494:
2495: AMS_Utility_PVT.debug_message('obj-flag'||l_evo_rec.overflow_flag);
2491: AMS_Utility_PVT.debug_message('obj-id'||l_evo_rec.event_offer_id);
2492: END IF;
2493: IF (AMS_DEBUG_HIGH_ON) THEN
2494:
2495: AMS_Utility_PVT.debug_message('obj-flag'||l_evo_rec.overflow_flag);
2496: END IF;
2497:
2498: -- GDEODHAR : Sept. 26, 2000 added two separate update statements.
2499: -- One for the main events where the workflow has to be kicked off for status change
2895: IF (AMS_DEBUG_HIGH_ON) THEN
2896:
2897:
2898:
2899: AMS_Utility_PVT.debug_message(l_full_name ||': end');
2900:
2901: END IF;
2902:
2903: EXCEPTION
2978:
2979: ----------------------- initialize --------------------
2980: IF (AMS_DEBUG_HIGH_ON) THEN
2981:
2982: AMS_Utility_PVT.debug_message(l_full_name||': start');
2983: END IF;
2984:
2985: IF FND_API.to_boolean(p_init_msg_list) THEN
2986: FND_MSG_PUB.initialize;
2999:
3000: ---------------------- validate ------------------------
3001: IF (AMS_DEBUG_HIGH_ON) THEN
3002:
3003: AMS_Utility_PVT.debug_message(l_full_name||': check items');
3004: END IF;
3005:
3006: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
3007: check_evo_items(
3019: IF (AMS_DEBUG_HIGH_ON) THEN
3020:
3021:
3022:
3023: AMS_Utility_PVT.debug_message(l_full_name||': check record');
3024:
3025: END IF;
3026:
3027: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
3040: IF (AMS_DEBUG_HIGH_ON) THEN
3041:
3042:
3043:
3044: AMS_Utility_PVT.debug_message(l_full_name||': check inter-entity');
3045:
3046: END IF;
3047:
3048: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_inter_entity THEN
3070: IF (AMS_DEBUG_HIGH_ON) THEN
3071:
3072:
3073:
3074: AMS_Utility_PVT.debug_message(l_full_name ||': end');
3075:
3076: END IF;
3077:
3078: EXCEPTION
3229: -- check if this event_offer_id is unique.
3230: IF p_validation_mode = JTF_PLSQL_API.g_create
3231: AND p_evo_rec.event_offer_id IS NOT NULL
3232: THEN
3233: IF AMS_Utility_PVT.check_uniqueness(
3234: 'ams_event_offers_all_b',
3235: 'event_offer_id = ' || p_evo_rec.event_offer_id
3236: ) = FND_API.g_false
3237: THEN
3251: IF p_validation_mode = JTF_PLSQL_API.g_create
3252: AND p_evo_rec.source_code IS NOT NULL
3253: THEN
3254: /*
3255: IF AMS_Utility_PVT.check_uniqueness(
3256: 'ams_event_offers_vl',
3257: 'source_code = ''' || p_evo_rec.source_code || ''''
3258: ) = FND_API.g_false
3259: */
3299: -- modified sugupta use ams_jtf_rs_emp_v instead of ams_jtf_rs_emp_v
3300: IF p_evo_rec.owner_user_id <> FND_API.g_miss_num AND
3301: p_evo_rec.owner_user_id is NOT NULL
3302: THEN
3303: IF AMS_Utility_PVT.check_fk_exists(
3304: 'ams_jtf_rs_emp_v',
3305: 'resource_id',
3306: p_evo_rec.owner_user_id
3307: ) = FND_API.g_false
3318: --------------------- application_id ------------------------
3319: IF p_evo_rec.application_id <> FND_API.g_miss_num AND
3320: p_evo_rec.application_id is NOT NULL
3321: THEN
3322: IF AMS_Utility_PVT.check_fk_exists(
3323: 'fnd_application',
3324: 'application_id',
3325: p_evo_rec.application_id
3326: ) = FND_API.g_false
3337: --------------------- inbound_script_name ------------------------
3338: IF p_evo_rec.inbound_script_name <> FND_API.g_miss_char
3339: AND p_evo_rec.inbound_script_name IS NOT NULL
3340: THEN
3341: IF AMS_Utility_PVT.check_fk_exists(
3342: 'ies_deployed_scripts',
3343: 'dscript_name',
3344: p_evo_rec.inbound_script_name,
3345: AMS_Utility_PVT.g_varchar2
3341: IF AMS_Utility_PVT.check_fk_exists(
3342: 'ies_deployed_scripts',
3343: 'dscript_name',
3344: p_evo_rec.inbound_script_name,
3345: AMS_Utility_PVT.g_varchar2
3346: ) = FND_API.g_false
3347: THEN
3348: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3349: THEN
3357: ----------------------- event_header_id ------------------------
3358: IF p_evo_rec.event_header_id <> FND_API.g_miss_num AND
3359: p_evo_rec.event_header_id is NOT NULL THEN
3360: IF p_evo_rec.EVENT_STANDALONE_FLAG = 'N'THEN
3361: IF AMS_Utility_PVT.check_fk_exists(
3362: 'ams_event_headers_all_b',
3363: 'event_header_id',
3364: p_evo_rec.event_header_id
3365: ) = FND_API.g_false
3377: ----------------------- parent_event_offer_id ------------------------
3378: IF p_evo_rec.parent_event_offer_id <> FND_API.g_miss_num
3379: AND p_evo_rec.parent_event_offer_id IS NOT NULL THEN
3380: IF p_evo_rec.EVENT_STANDALONE_FLAG = 'Y'THEN
3381: IF AMS_Utility_PVT.check_fk_exists(
3382: 'ams_event_offers_all_b',
3383: 'event_offer_id',
3384: p_evo_rec.parent_event_offer_id
3385: ) = FND_API.g_false
3395: END IF;
3396: END IF;
3397: ----------------------- parent_id ------------------------
3398: IF p_evo_rec.parent_id <> FND_API.g_miss_num THEN
3399: IF AMS_Utility_PVT.check_fk_exists(
3400: 'AMS_CAMPAIGNS_ALL_B',
3401: 'CAMPAIGN_ID',
3402: p_evo_rec.parent_id
3403: ) = FND_API.g_false
3413: END IF;
3414: ----------------------- duration_uom_code ------------------------
3415: IF p_evo_rec.event_duration_uom_code <> FND_API.g_miss_char
3416: AND p_evo_rec.event_duration_uom_code IS NOT NULL THEN
3417: IF AMS_Utility_PVT.check_fk_exists(
3418: 'mtl_units_of_measure_tl',
3419: 'uom_code',
3420: p_evo_rec.event_duration_uom_code,
3421: AMS_Utility_PVT.g_varchar2
3417: IF AMS_Utility_PVT.check_fk_exists(
3418: 'mtl_units_of_measure_tl',
3419: 'uom_code',
3420: p_evo_rec.event_duration_uom_code,
3421: AMS_Utility_PVT.g_varchar2
3422: ) = FND_API.g_false
3423: THEN
3424: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3425: THEN
3432: END IF;
3433: ----------------------- PRICELIST_HEADER_ID ------------------------
3434: IF p_evo_rec.pricelist_header_id <> FND_API.g_miss_num
3435: AND p_evo_rec.pricelist_header_id IS NOT NULL THEN
3436: IF AMS_Utility_PVT.check_fk_exists(
3437: 'qp_list_headers_v',
3438: 'list_header_id',
3439: p_evo_rec.pricelist_header_id
3440: ) = FND_API.g_false
3450: END IF;
3451: ----------------------- PRICELIST_LINE_ID ------------------------
3452: IF p_evo_rec.pricelist_line_id <> FND_API.g_miss_num
3453: AND p_evo_rec.pricelist_line_id IS NOT NULL THEN
3454: IF AMS_Utility_PVT.check_fk_exists(
3455: 'qp_list_lines_v',
3456: 'list_line_id',
3457: p_evo_rec.pricelist_line_id
3458: ) = FND_API.g_false
3468: END IF;
3469: ----------------------- TIMEZONE_ID ------------------------
3470: IF p_evo_rec.timezone_id <> FND_API.g_miss_num
3471: AND p_evo_rec.timezone_id IS NOT NULL THEN
3472: IF AMS_Utility_PVT.check_fk_exists(
3473: 'fnd_timezones_b',
3474: 'upgrade_tz_id',
3475: p_evo_rec.timezone_id
3476: ) = FND_API.g_false
3486: END IF;
3487: ----------------------- REG_TIMEZONE_ID ------------------------
3488: IF p_evo_rec.reg_timezone_id <> FND_API.g_miss_num
3489: AND p_evo_rec.reg_timezone_id IS NOT NULL THEN
3490: IF AMS_Utility_PVT.check_fk_exists(
3491: 'fnd_timezones_b',
3492: 'upgrade_tz_id',
3493: p_evo_rec.reg_timezone_id
3494: ) = FND_API.g_false
3504: END IF;
3505: ----------------------- event_venue_id ------------------------
3506: IF p_evo_rec.event_venue_id <> FND_API.g_miss_num
3507: AND p_evo_rec.event_venue_id IS NOT NULL THEN
3508: IF AMS_Utility_PVT.check_fk_exists(
3509: 'ams_venues_vl',
3510: 'venue_id',
3511: p_evo_rec.event_venue_id
3512: ) = FND_API.g_false
3522: END IF;
3523: ----------------------- event_language_code ------------------------
3524: IF p_evo_rec.event_language_code <> FND_API.g_miss_char
3525: AND p_evo_rec.event_language_code IS NOT NULL THEN
3526: IF AMS_Utility_PVT.check_fk_exists(
3527: 'fnd_languages',
3528: 'language_code',
3529: p_evo_rec.event_language_code,
3530: AMS_Utility_PVT.g_varchar2
3526: IF AMS_Utility_PVT.check_fk_exists(
3527: 'fnd_languages',
3528: 'language_code',
3529: p_evo_rec.event_language_code,
3530: AMS_Utility_PVT.g_varchar2
3531: ) = FND_API.g_false
3532: THEN
3533: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3534: THEN
3541: END IF;
3542: ----------------------- event_location_id ------------------------
3543: IF p_evo_rec.event_location_id <> FND_API.g_miss_num
3544: AND p_evo_rec.event_location_id IS NOT NULL THEN
3545: IF AMS_Utility_PVT.check_fk_exists(
3546: 'hz_locations',
3547: 'location_id',
3548: p_evo_rec.event_location_id
3549: ) = FND_API.g_false
3565: p_evo_rec.country_code IS NOT NULL THEN
3566:
3567: l_table_name := 'jtf_loc_hierarchies_b';
3568: l_pk_name := 'location_hierarchy_id' ;
3569: l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
3570: l_pk_value := to_number(p_evo_rec.country_code);
3571: l_additional_where_clause := null ;
3572:
3573: IF AMS_Utility_PVT.check_fk_exists(
3569: l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
3570: l_pk_value := to_number(p_evo_rec.country_code);
3571: l_additional_where_clause := null ;
3572:
3573: IF AMS_Utility_PVT.check_fk_exists(
3574: p_table_name => l_table_name,
3575: p_pk_name => l_pk_name,
3576: p_pk_value => l_pk_value,
3577: p_pk_data_type => l_pk_data_type,
3577: p_pk_data_type => l_pk_data_type,
3578: p_additional_where_clause => l_additional_where_clause
3579: ) = FND_API.g_false
3580: THEN
3581: AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_COUNTRY') ;
3582: x_return_status := FND_API.g_ret_sts_error;
3583: RETURN;
3584: END IF;
3585: END IF;
3585: END IF;
3586: ----------------------- user_status_id ------------------------
3587: IF p_evo_rec.user_status_id <> FND_API.g_miss_num
3588: AND p_evo_rec.user_status_id IS NOT NULL THEN
3589: IF AMS_Utility_PVT.check_fk_exists(
3590: 'ams_user_statuses_b',
3591: 'user_status_id',
3592: p_evo_rec.user_status_id
3593: ) = FND_API.g_false
3607: uniqueness in call to create_inv_item procedure call
3608: ----------------------- inventory_item_id ------------------------
3609: -- gdeodhar: There was a WHERE in the following clause. Took it out
3610: -- This was Ravi's instruction.
3611: -- looks like AMS_Utility_PVT takes care of adding WHERE and AND accordingly.
3612: l_where_clause := ' organization_id = ' || p_evo_rec.organization_id;
3613:
3614: IF p_evo_rec.inventory_item_id <> FND_API.g_miss_num
3615: AND p_evo_rec.inventory_item_id IS NOT NULL AND p_evo_rec.organization_id IS NOT NULL THEN
3612: l_where_clause := ' organization_id = ' || p_evo_rec.organization_id;
3613:
3614: IF p_evo_rec.inventory_item_id <> FND_API.g_miss_num
3615: AND p_evo_rec.inventory_item_id IS NOT NULL AND p_evo_rec.organization_id IS NOT NULL THEN
3616: IF AMS_Utility_PVT.check_fk_exists(
3617: 'mtl_system_items_b',
3618: 'inventory_item_id',
3619: p_evo_rec.inventory_item_id,
3620: AMS_UTILITY_PVT.g_number,
3616: IF AMS_Utility_PVT.check_fk_exists(
3617: 'mtl_system_items_b',
3618: 'inventory_item_id',
3619: p_evo_rec.inventory_item_id,
3620: AMS_UTILITY_PVT.g_number,
3621: l_where_clause
3622: ) = FND_API.g_false
3623: THEN
3624: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3660: ----------------------- event_type_code ------------------------
3661: IF p_evo_rec.event_type_code <> FND_API.g_miss_char
3662: AND p_evo_rec.event_type_code IS NOT NULL
3663: THEN
3664: IF AMS_Utility_PVT.check_lookup_exists(
3665: p_lookup_type => 'AMS_EVENT_TYPE',
3666: p_lookup_code => p_evo_rec.event_type_code
3667: ) = FND_API.g_false
3668: THEN
3678: ----------------------- event_level ------------------------
3679: IF p_evo_rec.event_level <> FND_API.g_miss_char
3680: AND p_evo_rec.event_level IS NOT NULL
3681: THEN
3682: IF AMS_Utility_PVT.check_lookup_exists(
3683: p_lookup_type => 'AMS_EVENT_LEVEL',
3684: p_lookup_code => p_evo_rec.event_level
3685: ) = FND_API.g_false
3686: THEN
3696: ----------------------- waitlist_action_type_code ------------------------
3697: IF p_evo_rec.waitlist_action_type_code <> FND_API.g_miss_char
3698: AND p_evo_rec.waitlist_action_type_code IS NOT NULL
3699: THEN
3700: IF AMS_Utility_PVT.check_lookup_exists(
3701: p_lookup_type => 'AMS_EVENT_WAITLIST_ACTION',
3702: p_lookup_code => p_evo_rec.waitlist_action_type_code
3703: ) = FND_API.g_false
3704: THEN
3714: ----------------------- stream_type_code ------------------------
3715: IF p_evo_rec.stream_type_code <> FND_API.g_miss_char
3716: AND p_evo_rec.stream_type_code IS NOT NULL
3717: THEN
3718: IF AMS_Utility_PVT.check_lookup_exists(
3719: p_lookup_type => 'AMS_EVENT_STREAM_TYPE',
3720: p_lookup_code => p_evo_rec.stream_type_code
3721: ) = FND_API.g_false
3722: THEN
3732: ----------------------- fund_source_type_code ------------------------
3733: IF p_evo_rec.fund_source_type_code <> FND_API.g_miss_char
3734: AND p_evo_rec.fund_source_type_code IS NOT NULL
3735: THEN
3736: IF AMS_Utility_PVT.check_lookup_exists(
3737: p_lookup_type => 'AMS_FUND_SOURCE',
3738: p_lookup_code => p_evo_rec.fund_source_type_code
3739: ) = FND_API.g_false
3740: THEN
3750: ----------------------- priority ------------------------
3751: IF p_evo_rec.priority_type_code <> FND_API.g_miss_char
3752: AND p_evo_rec.priority_type_code IS NOT NULL
3753: THEN
3754: IF AMS_Utility_PVT.check_lookup_exists(
3755: p_lookup_type => 'AMS_PRIORITY',
3756: p_lookup_code => p_evo_rec.priority_type_code
3757: ) = FND_API.g_false
3758: THEN
3769: ----------------------- certification_credit_type ------------------------
3770: IF p_evo_rec.cert_credit_type_code <> FND_API.g_miss_char
3771: AND p_evo_rec.cert_credit_type_code IS NOT NULL
3772: THEN
3773: IF AMS_Utility_PVT.check_lookup_exists(
3774: p_lookup_type => 'AMS_EVENT_CERT_CREDIT_TYPE',
3775: p_lookup_code => p_evo_rec.cert_credit_type_code
3776: ) = FND_API.g_false
3777: THEN
3788: ----------------------- cancellation_reason_code ------------------------
3789: IF p_evo_rec.cancellation_reason_code <> FND_API.g_miss_char
3790: AND p_evo_rec.cancellation_reason_code IS NOT NULL
3791: THEN
3792: IF AMS_Utility_PVT.check_lookup_exists(
3793: p_lookup_type => 'AMS_EVENT_CANCEL_REASON',
3794: p_lookup_code => p_evo_rec.cancellation_reason_code
3795: ) = FND_API.g_false
3796: THEN
3827: ----------------------- active_flag ------------------------
3828: IF p_evo_rec.active_flag <> FND_API.g_miss_char
3829: AND p_evo_rec.active_flag IS NOT NULL
3830: THEN
3831: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.active_flag) = FND_API.g_false
3832: THEN
3833: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3834: THEN
3835: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_ACTIVE_FLAG');
3844: ----------------------- private_flag ------------------------
3845: IF p_evo_rec.private_flag <> FND_API.g_miss_char
3846: AND p_evo_rec.private_flag IS NOT NULL
3847: THEN
3848: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.private_flag) = FND_API.g_false
3849: THEN
3850: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3851: THEN
3852: FND_MESSAGE.set_name('AMS', 'AMS_evo_BAD_PRIVATE_FLAG');
3859: ----------------------- event_full_flag ------------------------
3860: IF p_evo_rec.event_full_flag <> FND_API.g_miss_char
3861: AND p_evo_rec.event_full_flag IS NOT NULL
3862: THEN
3863: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.event_full_flag) = FND_API.g_false
3864: THEN
3865: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3866: THEN
3867: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_FULL_FLAG');
3875: ----------------------- auto_register_flag ------------------------
3876: IF p_evo_rec.auto_register_flag <> FND_API.g_miss_char
3877: AND p_evo_rec.auto_register_flag IS NOT NULL
3878: THEN
3879: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.auto_register_flag) = FND_API.g_false
3880: THEN
3881: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3882: THEN
3883: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_AUTOREG_FLAG');
3891: ----------------------- event_standalone_flag ------------------------
3892: IF p_evo_rec.event_standalone_flag <> FND_API.g_miss_char
3893: AND p_evo_rec.event_standalone_flag IS NOT NULL
3894: THEN
3895: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.event_standalone_flag) = FND_API.g_false
3896: THEN
3897: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3898: THEN
3899: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_STANDALONE_FLAG');
3907: ----------------------- reg_required_flag ------------------------
3908: IF p_evo_rec.reg_required_flag <> FND_API.g_miss_char
3909: AND p_evo_rec.reg_required_flag IS NOT NULL
3910: THEN
3911: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.reg_required_flag) = FND_API.g_false
3912: THEN
3913: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3914: THEN
3915: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_REG_REQUIRED_FLAG');
3924: ----------------------- reg_invited_only_flag ------------------------
3925: IF p_evo_rec.reg_invited_only_flag <> FND_API.g_miss_char
3926: AND p_evo_rec.reg_invited_only_flag IS NOT NULL
3927: THEN
3928: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.reg_invited_only_flag) = FND_API.g_false
3929: THEN
3930: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3931: THEN
3932: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_REG_INVITED_ONLY_FLAG');
3940: ----------------------- reg_waitlist_allowed_flag ------------------------
3941: IF p_evo_rec.reg_waitlist_allowed_flag <> FND_API.g_miss_char
3942: AND p_evo_rec.reg_waitlist_allowed_flag IS NOT NULL
3943: THEN
3944: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.reg_waitlist_allowed_flag) = FND_API.g_false
3945: THEN
3946: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3947: THEN
3948: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_REG_WAITLIST_ALLOWED_FLAG');
3956: ----------------------- reg_overbook_allowed_flag ------------------------
3957: IF p_evo_rec.reg_overbook_allowed_flag <> FND_API.g_miss_char
3958: AND p_evo_rec.reg_overbook_allowed_flag IS NOT NULL
3959: THEN
3960: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.reg_overbook_allowed_flag) = FND_API.g_false
3961: THEN
3962: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3963: THEN
3964: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_REG_OVERBOOK_ALLOWED_FLAG');
3972: ----------------------- reg_charge_flag ------------------------
3973: IF p_evo_rec.reg_charge_flag <> FND_API.g_miss_char
3974: AND p_evo_rec.reg_charge_flag IS NOT NULL
3975: THEN
3976: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.reg_charge_flag) = FND_API.g_false
3977: THEN
3978: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3979: THEN
3980: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_REG_CHARGE_FLAG');
3988: ----------------------- reg_frozen_flag ------------------------
3989: IF p_evo_rec.reg_frozen_flag <> FND_API.g_miss_char
3990: AND p_evo_rec.reg_frozen_flag IS NOT NULL
3991: THEN
3992: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.reg_frozen_flag) = FND_API.g_false
3993: THEN
3994: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3995: THEN
3996: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_REG_FROZEN_FLAG');
4005: ----------------------- overflow_flag ------------------------
4006: IF p_evo_rec.overflow_flag <> FND_API.g_miss_char
4007: AND p_evo_rec.overflow_flag IS NOT NULL
4008: THEN
4009: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.overflow_flag) = FND_API.g_false
4010: THEN
4011: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
4012: THEN
4013: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_OVERFLOW_FLAG');
4021: ----------------------- partner_flag ------------------------
4022: IF p_evo_rec.partner_flag <> FND_API.g_miss_char
4023: AND p_evo_rec.partner_flag IS NOT NULL
4024: THEN
4025: IF AMS_Utility_PVT.is_Y_or_N(p_evo_rec.partner_flag) = FND_API.g_false
4026: THEN
4027: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
4028: THEN
4029: FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_PARTNER_FLAG');
4058: -- for example, changes to source_code should not be allowed at any update.
4059: -- Also when the event is in active stage, changes to marketing message and budget related columns should not be allowed.
4060: IF (AMS_DEBUG_HIGH_ON) THEN
4061:
4062: AMS_UTILITY_PVT.debug_message('before ok_items');
4063: END IF;
4064: IF p_validation_mode = JTF_PLSQL_API.g_update THEN
4065: check_evo_update_ok_items(
4066: p_evo_rec => p_evo_rec,
4074: END IF;
4075: --------------------------------------Create mode--------------------------
4076: IF (AMS_DEBUG_HIGH_ON) THEN
4077:
4078: AMS_UTILITY_PVT.debug_message('before uk_items');
4079: END IF;
4080:
4081: check_evo_uk_items(
4082: p_evo_rec => p_evo_rec,
4086:
4087: -------------------------- Create or Update Mode ----------------------------
4088: IF (AMS_DEBUG_HIGH_ON) THEN
4089:
4090: AMS_UTILITY_PVT.debug_message('before req_items');
4091: END IF;
4092:
4093: check_evo_req_items(
4094: p_evo_rec => p_evo_rec,
4103: RETURN;
4104: END IF;
4105: IF (AMS_DEBUG_HIGH_ON) THEN
4106:
4107: AMS_UTILITY_PVT.debug_message('before fk_items');
4108: END IF;
4109:
4110: check_evo_fk_items(
4111: p_evo_rec => p_evo_rec,
4116: RETURN;
4117: END IF;
4118: IF (AMS_DEBUG_HIGH_ON) THEN
4119:
4120: AMS_UTILITY_PVT.debug_message('before lookup_items');
4121: END IF;
4122:
4123: check_evo_lookup_items(
4124: p_evo_rec => p_evo_rec,
4129: RETURN;
4130: END IF;
4131: IF (AMS_DEBUG_HIGH_ON) THEN
4132:
4133: AMS_UTILITY_PVT.debug_message('before flag_items');
4134: END IF;
4135:
4136: check_evo_flag_items(
4137: p_evo_rec => p_evo_rec,
4272: l_evo_end_date_time := to_date(to_char(l_evo_end_date_time, 'DD-MM-YYYY') || '23:59','DD-MM-YYYY HH24:MI');
4273: END IF;
4274: IF (AMS_DEBUG_HIGH_ON) THEN
4275:
4276: AMS_UTILITY_PVT.debug_message('End Date Time is ' ||to_char(l_evo_end_date_time, 'DD-MM-YYYY HH24:MI'));
4277: END IF;
4278: IF (AMS_DEBUG_HIGH_ON) THEN
4279:
4280: AMS_UTILITY_PVT.debug_message('Start Date Time is ' ||to_char(l_evo_start_date_time,'DD-MM-YYYY HH24:MI') );
4276: AMS_UTILITY_PVT.debug_message('End Date Time is ' ||to_char(l_evo_end_date_time, 'DD-MM-YYYY HH24:MI'));
4277: END IF;
4278: IF (AMS_DEBUG_HIGH_ON) THEN
4279:
4280: AMS_UTILITY_PVT.debug_message('Start Date Time is ' ||to_char(l_evo_start_date_time,'DD-MM-YYYY HH24:MI') );
4281: END IF;
4282:
4283: IF l_evo_start_date_time > l_evo_end_date_time THEN
4284: IF (AMS_DEBUG_HIGH_ON) THEN
4282:
4283: IF l_evo_start_date_time > l_evo_end_date_time THEN
4284: IF (AMS_DEBUG_HIGH_ON) THEN
4285:
4286: AMS_UTILITY_PVT.debug_message('Entered the loop');
4287: END IF;
4288:
4289: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
4290: THEN
4302: OR p_complete_rec.EVENT_DURATION IS NOT NULL
4303: THEN
4304: IF (AMS_DEBUG_HIGH_ON) THEN
4305:
4306: AMS_Utility_PVT.debug_message('Entered Here1');
4307: END IF;
4308:
4309: IF ( p_evo_rec.EVENT_DURATION_UOM_CODE IS NULL
4310: OR
4313:
4314: THEN
4315: IF (AMS_DEBUG_HIGH_ON) THEN
4316:
4317: AMS_Utility_PVT.debug_message('Entered Here2');
4318: END IF;
4319: AMS_Utility_PVT.error_message('AMS_EVO_NO_DUR_UOM_CODE');
4320: x_return_status := FND_API.g_ret_sts_error;
4321: return;
4315: IF (AMS_DEBUG_HIGH_ON) THEN
4316:
4317: AMS_Utility_PVT.debug_message('Entered Here2');
4318: END IF;
4319: AMS_Utility_PVT.error_message('AMS_EVO_NO_DUR_UOM_CODE');
4320: x_return_status := FND_API.g_ret_sts_error;
4321: return;
4322: END IF;
4323: END IF;
4327: THEN
4328: IF p_evo_rec.EVENT_DURATION = FND_API.g_miss_num
4329: AND p_complete_rec.EVENT_DURATION IS NULL
4330: THEN
4331: AMS_Utility_PVT.error_message('AMS_EVO_NO_DUR_WITH_CODE');
4332: x_return_status := FND_API.g_ret_sts_error;
4333: return;
4334: END IF;
4335: END IF;
4340: THEN
4341: IF p_evo_rec.CURRENCY_CODE_TC = FND_API.g_miss_char
4342: AND p_complete_rec.CURRENCY_CODE_TC IS NULL
4343: THEN
4344: AMS_Utility_PVT.error_message('AMS_CAMP_BUDGET_NO_CURRENCY'); -- reusing campaign message
4345: x_return_status := FND_API.g_ret_sts_error;
4346: return;
4347: END IF;
4348: END IF;
4367: IF (AMS_DEBUG_HIGH_ON) THEN
4368:
4369:
4370:
4371: AMS_UTILITY_PVT.debug_message('End Date Time is ' ||to_char(l_evo_end_date_time, 'DD-MM-YYYY HH24:MI'));
4372:
4373: END IF;
4374: IF (AMS_DEBUG_HIGH_ON) THEN
4375:
4372:
4373: END IF;
4374: IF (AMS_DEBUG_HIGH_ON) THEN
4375:
4376: AMS_UTILITY_PVT.debug_message('Start Date Time is ' ||to_char(l_evo_start_date_time,'DD-MM-YYYY HH24:MI') );
4377: END IF;
4378:
4379:
4380: IF l_evo_start_date_time > l_evo_end_date_time
4450: IF (AMS_DEBUG_HIGH_ON) THEN
4451:
4452:
4453:
4454: AMS_Utility_PVT.debug_message(p_evo_rec.event_offer_id ||': check evo_update');
4455:
4456: END IF;
4457: x_return_status := FND_API.g_ret_sts_success;
4458:
4588: IF (AMS_DEBUG_HIGH_ON) THEN
4589:
4590:
4591:
4592: AMS_Utility_Pvt.Debug_Message('l_reg_overbook_allowed_flag ' || l_reg_overbook_allowed_flag);
4593:
4594: END IF;
4595: IF (AMS_DEBUG_HIGH_ON) THEN
4596:
4593:
4594: END IF;
4595: IF (AMS_DEBUG_HIGH_ON) THEN
4596:
4597: AMS_Utility_Pvt.Debug_Message('l_max_cap: ' || l_max_cap);
4598: END IF;
4599: IF l_reg_overbook_allowed_flag <> 'Y'
4600: THEN
4601: l_effective_capacity := l_max_cap;
4601: l_effective_capacity := l_max_cap;
4602: ELSE
4603: IF (AMS_DEBUG_HIGH_ON) THEN
4604:
4605: AMS_Utility_Pvt.Debug_Message('l_reg_overbook_pct ' || l_reg_overbook_pct);
4606: END IF;
4607: l_effective_capacity := round((1 + (l_reg_overbook_pct/100)) * l_max_cap);
4608: END IF;
4609: l_num_registered := AMS_EvtRegs_PVT.check_number_registered(p_event_offer_id => l_evo_rec.event_offer_id);
4609: l_num_registered := AMS_EvtRegs_PVT.check_number_registered(p_event_offer_id => l_evo_rec.event_offer_id);
4610: l_event_availability := l_effective_capacity - l_num_registered;
4611: IF (AMS_DEBUG_HIGH_ON) THEN
4612:
4613: AMS_Utility_Pvt.Debug_Message('Slots available: ' || l_event_availability);
4614: END IF;
4615: IF nvl(l_event_availability, 1) < 0
4616: THEN
4617: -- Capacity is too small - error
4614: END IF;
4615: IF nvl(l_event_availability, 1) < 0
4616: THEN
4617: -- Capacity is too small - error
4618: AMS_Utility_PVT.Error_Message( p_message_name => 'AMS_EVO_REG_CAP_LT_ROSTER'
4619: , p_token_name => 'REGCNT'
4620: , p_token_value => to_char(l_num_registered)
4621: );
4622: x_return_status := FND_API.g_ret_sts_error;
4650: , x_msg_data => l_msg_data
4651: );
4652: IF (x_return_status = FND_API.g_ret_sts_error)
4653: THEN
4654: AMS_Utility_PVT.Error_Message( p_message_name => 'AMS_EVO_REG_WAITLIST_CAP');
4655: END IF;
4656: END LOOP;
4657: END IF; -- auto_register_flag
4658: END IF;
4708: IF l_evo_rec.system_status_code <> 'NEW' THEN
4709: IF p_evo_rec.event_offer_name <> FND_API.g_miss_char
4710: AND p_evo_rec.event_offer_name <> l_evo_rec.event_offer_name
4711: THEN
4712: AMS_Utility_PVT.error_message('AMS_EVO_UPDATE_OFFER_NAME');
4713: x_return_status := FND_API.g_ret_sts_error;
4714: END IF;
4715:
4716: IF p_evo_rec.event_type_code <> fnd_api.g_miss_char
4717: and p_evo_rec.event_type_code <> l_evo_rec.event_type_code
4718: and (p_evo_rec.event_type_code IS NOT NULL
4719: OR l_evo_rec.event_type_code IS NOT NULL)
4720: THEN
4721: AMS_Utility_PVT.error_message('AMS_EVO_UPDATE_EVENT_TYPE');
4722: x_return_status := FND_API.g_ret_sts_error;
4723: END IF;
4724:
4725: IF p_evo_rec.event_start_date <> fnd_api.g_miss_date
4726: and p_evo_rec.event_start_date <> l_evo_rec.event_start_date
4727: and (p_evo_rec.event_start_date IS NOT NULL
4728: OR l_evo_rec.event_start_date IS NOT NULL)
4729: THEN
4730: AMS_Utility_PVT.error_message('AMS_EVO_UPDATE_START_DATE');
4731: x_return_status := FND_API.g_ret_sts_error;
4732: END IF;
4733:
4734: IF p_evo_rec.event_end_date <> fnd_api.g_miss_date
4735: and p_evo_rec.event_end_date <> l_evo_rec.event_end_date
4736: and (p_evo_rec.event_end_date IS NOT NULL
4737: OR l_evo_rec.event_end_date IS NOT NULL)
4738: THEN
4739: AMS_Utility_PVT.error_message('AMS_EVO_UPDATE_END_DATE');
4740: x_return_status := FND_API.g_ret_sts_error;
4741: END IF;
4742: END IF; -- status code <> new
4743: end if; -- event_level MAIN
5129:
5130: BEGIN
5131: IF (AMS_DEBUG_HIGH_ON) THEN
5132:
5133: AMS_UTILITY_PVT.debug_message('complete_evo_rec :'|| p_evo_rec.event_offer_id);
5134: END IF;
5135:
5136: x_complete_rec := p_evo_rec;
5137:
5854:
5855:
5856:
5857:
5858: AMS_Utility_PVT.debug_message(l_full_name ||': create pricelist header...');
5859:
5860:
5861: END IF;
5862:
6024: IF (AMS_DEBUG_HIGH_ON) THEN
6025:
6026:
6027:
6028: AMS_Utility_PVT.debug_message(l_full_name ||': create pricelist line...');
6029:
6030: END IF;
6031:
6032: QP_PRICE_LIST_PUB.Process_Price_List(
6142: BEGIN
6143: SAVEPOINT copy_ev_header_to_offer;
6144: IF (AMS_DEBUG_HIGH_ON) THEN
6145:
6146: ams_utility_pvt.debug_message(l_full_name || ': start');
6147: END IF;
6148:
6149: IF fnd_api.to_boolean(p_init_msg_list)
6150: THEN
6163: x_return_status := fnd_api.g_ret_sts_success;
6164: ----------------------- insert -----------------------
6165: IF (AMS_DEBUG_HIGH_ON) THEN
6166:
6167: ams_utility_pvt.debug_message(l_full_name || ': start');
6168: END IF;
6169:
6170: ams_utility_pvt.get_lookup_meaning(
6171: 'AMS_SYS_ARC_QUALIFIER',
6166:
6167: ams_utility_pvt.debug_message(l_full_name || ': start');
6168: END IF;
6169:
6170: ams_utility_pvt.get_lookup_meaning(
6171: 'AMS_SYS_ARC_QUALIFIER',
6172: 'EVEH',
6173: l_return_status,
6174: l_lookup_meaning);
6295: ROLLBACK TO copy_ev_header_to_offer;
6296: x_return_status := fnd_api.g_ret_sts_unexp_error;
6297: IF (AMS_DEBUG_HIGH_ON) THEN
6298:
6299: ams_utility_pvt.debug_message(l_full_name || ': debug');
6300: END IF;
6301: fnd_msg_pub.count_and_get(
6302: p_encoded => fnd_api.g_false,
6303: p_count => x_msg_count,
6392: BEGIN
6393: SAVEPOINT copy_ev_header_associations;
6394: IF (AMS_DEBUG_HIGH_ON) THEN
6395:
6396: ams_utility_pvt.debug_message(l_full_name || ': start');
6397: END IF;
6398:
6399: IF fnd_api.to_boolean(p_init_msg_list)
6400: THEN
6416: IF (AMS_DEBUG_HIGH_ON) THEN
6417:
6418:
6419:
6420: ams_utility_pvt.debug_message(l_full_name || ': start');
6421:
6422: END IF;
6423: -- refresh the log
6424:
6423: -- refresh the log
6424:
6425: AMS_CPYutility_pvt.refresh_log_mesg;
6426:
6427: ams_utility_pvt.get_lookup_meaning(
6428: 'AMS_SYS_ARC_QUALIFIER',
6429: 'EVEH',
6430: l_return_status,
6431: l_lookup_meaning);
6748: ROLLBACK TO copy_ev_header_associations;
6749: x_return_status := fnd_api.g_ret_sts_unexp_error;
6750: IF (AMS_DEBUG_HIGH_ON) THEN
6751:
6752: ams_utility_pvt.debug_message(l_full_name || ': debug');
6753: END IF;
6754: fnd_msg_pub.count_and_get(
6755: p_encoded => fnd_api.g_false,
6756: p_count => x_msg_count,
6839:
6840: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
6841: --dbms_output.put_line(l_return_status);
6842: --dbms_output.put_line(l_msg_count);
6843: AMS_UTILITY_PVT.display_messages;
6844: --dbms_output.put_line('AMS_EVENTOFFER_PVT.update ERROR');
6845: ELSE
6846: commit work;
6847: --dbms_output.put_line(l_return_status);
6844: --dbms_output.put_line('AMS_EVENTOFFER_PVT.update ERROR');
6845: ELSE
6846: commit work;
6847: --dbms_output.put_line(l_return_status);
6848: AMS_UTILITY_PVT.display_messages;
6849: END IF;
6850:
6851: END Unit_Test_Insert;
6852:
7035: IF (p_evo_rec.event_start_date IS NOT NULL AND l_start_date IS NOT NULL ) THEN
7036: IF (p_evo_rec.event_start_date < l_start_date) THEN
7037: IF (AMS_DEBUG_HIGH_ON) THEN
7038:
7039: Ams_Utility_Pvt.debug_message('The start date of Event can not be lesser than that of Program');
7040: END IF;
7041: IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
7042:
7043: IF (p_evo_rec.event_level = 'MAIN') THEN
7061: IF (p_evo_rec.event_end_date IS NOT NULL AND l_end_date IS NOT NULL ) THEN
7062: IF (p_evo_rec.event_end_date > l_end_date) THEN
7063: IF (AMS_DEBUG_HIGH_ON) THEN
7064:
7065: Ams_Utility_Pvt.debug_message('The end date of Event can not be greater than that of Program');
7066: END IF;
7067: IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
7068:
7069: IF (p_evo_rec.event_object_type = 'EVEO') THEN
7080: ELSE
7081: IF ( p_evo_rec.event_end_date IS NULL AND l_end_date IS NOT NULL ) THEN
7082: IF (AMS_DEBUG_HIGH_ON) THEN
7083:
7084: Ams_Utility_Pvt.debug_message('The end date of Event can not be greater than that of Program');
7085: END IF;
7086: IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
7087:
7088: IF (p_evo_rec.event_object_type = 'EVEO') THEN
7155:
7156: IF p_evo_rec.event_object_type = 'EONE' THEN
7157: IF (AMS_DEBUG_HIGH_ON) THEN
7158:
7159: Ams_Utility_Pvt.debug_message('The Parent is not Active');
7160: END IF;
7161: Fnd_Message.set_name('AMS', 'AMS_PROGRAM_NOT_ACTIVE');
7162: ELSIF p_evo_rec.event_object_type = 'EVEO' THEN
7163: IF (AMS_DEBUG_HIGH_ON) THEN
7161: Fnd_Message.set_name('AMS', 'AMS_PROGRAM_NOT_ACTIVE');
7162: ELSIF p_evo_rec.event_object_type = 'EVEO' THEN
7163: IF (AMS_DEBUG_HIGH_ON) THEN
7164:
7165: Ams_Utility_Pvt.debug_message('The Parent is not Active');
7166: END IF;
7167: Fnd_Message.set_name('AMS', 'AMS_EVENT_NOT_ACTIVE');
7168: END IF;
7169:
7269:
7270: l_fulfilment := FND_PROFILE.Value('AMS_FULFILL_ENABLE_FLAG');
7271:
7272: IF (AMS_DEBUG_HIGH_ON) THEN
7273: AMS_Utility_PVT.debug_message(' Fulfilment Profile :' || l_fulfilment);
7274: END IF;
7275:
7276: IF( l_fulfilment <> 'N' )
7277: THEN
7292: IF (AMS_DEBUG_HIGH_ON) THEN
7293:
7294:
7295:
7296: AMS_Utility_PVT.debug_message('The p_evo_rec.user_status_id is :' || p_evo_rec.user_status_id);
7297:
7298: END IF;
7299: IF (AMS_DEBUG_HIGH_ON) THEN
7300:
7297:
7298: END IF;
7299: IF (AMS_DEBUG_HIGH_ON) THEN
7300:
7301: AMS_Utility_PVT.debug_message('The l_sys_status_code is :' || l_sys_status_code);
7302: END IF;
7303: IF (AMS_DEBUG_HIGH_ON) THEN
7304:
7305: AMS_Utility_PVT.debug_message('The l_system_status_code is :' || l_system_status_code);
7301: AMS_Utility_PVT.debug_message('The l_sys_status_code is :' || l_sys_status_code);
7302: END IF;
7303: IF (AMS_DEBUG_HIGH_ON) THEN
7304:
7305: AMS_Utility_PVT.debug_message('The l_system_status_code is :' || l_system_status_code);
7306: END IF;
7307:
7308:
7309: IF(l_sys_status_code = 'CANCELLED' AND l_sys_status_code <> l_system_status_code)
7310: THEN
7311:
7312: IF (AMS_DEBUG_HIGH_ON) THEN
7313:
7314: AMS_Utility_PVT.debug_message('Entered Here');
7315: END IF;
7316: IF (AMS_DEBUG_HIGH_ON) THEN
7317:
7318: AMS_Utility_PVT.debug_message('Calling Fulfilment Procedure for Cancel');
7314: AMS_Utility_PVT.debug_message('Entered Here');
7315: END IF;
7316: IF (AMS_DEBUG_HIGH_ON) THEN
7317:
7318: AMS_Utility_PVT.debug_message('Calling Fulfilment Procedure for Cancel');
7319: END IF;
7320:
7321: l_bind_names(1) := 'EVENT_OFFER_ID' ;
7322: l_bind_values(1):= TO_CHAR(p_evo_rec.event_offer_id);
7342: -- anchaudh added starts for ER3037795
7343: IF (l_sys_status_code = 'ACTIVE' AND l_sys_status_code <> l_system_status_code AND l_system_status_code <> 'ON_HOLD') THEN
7344:
7345: IF (AMS_DEBUG_HIGH_ON) THEN
7346: AMS_Utility_PVT.debug_message('Calling Fulfilment Procedure for Invitation');
7347: END IF;
7348:
7349: l_bind_names(1) := 'EVENT_OFFER_ID' ;
7350: l_bind_values(1):= TO_CHAR(p_evo_rec.event_offer_id);
7377: OR (l_end_date <> p_evo_rec.event_end_date ))
7378: THEN
7379: IF (AMS_DEBUG_HIGH_ON) THEN
7380:
7381: AMS_Utility_PVT.debug_message('Calling Fulfilment Procedure for Date change');
7382: END IF;
7383: IF (AMS_DEBUG_HIGH_ON) THEN
7384:
7385: AMS_Utility_PVT.debug_message('Old start date is '||l_start_date);
7381: AMS_Utility_PVT.debug_message('Calling Fulfilment Procedure for Date change');
7382: END IF;
7383: IF (AMS_DEBUG_HIGH_ON) THEN
7384:
7385: AMS_Utility_PVT.debug_message('Old start date is '||l_start_date);
7386: END IF;
7387: IF (AMS_DEBUG_HIGH_ON) THEN
7388:
7389: AMS_Utility_PVT.debug_message('New start date is '||p_evo_rec.event_start_date);
7385: AMS_Utility_PVT.debug_message('Old start date is '||l_start_date);
7386: END IF;
7387: IF (AMS_DEBUG_HIGH_ON) THEN
7388:
7389: AMS_Utility_PVT.debug_message('New start date is '||p_evo_rec.event_start_date);
7390: END IF;
7391: IF (AMS_DEBUG_HIGH_ON) THEN
7392:
7393: AMS_Utility_PVT.debug_message('Old end date is '||l_end_date);
7389: AMS_Utility_PVT.debug_message('New start date is '||p_evo_rec.event_start_date);
7390: END IF;
7391: IF (AMS_DEBUG_HIGH_ON) THEN
7392:
7393: AMS_Utility_PVT.debug_message('Old end date is '||l_end_date);
7394: END IF;
7395: IF (AMS_DEBUG_HIGH_ON) THEN
7396:
7397: AMS_Utility_PVT.debug_message('New end date is '||p_evo_rec.event_end_date);
7393: AMS_Utility_PVT.debug_message('Old end date is '||l_end_date);
7394: END IF;
7395: IF (AMS_DEBUG_HIGH_ON) THEN
7396:
7397: AMS_Utility_PVT.debug_message('New end date is '||p_evo_rec.event_end_date);
7398: END IF;
7399:
7400: l_bind_names(1) := 'OLD_START_DATE';
7401: l_bind_names(2) := 'OLD_END_DATE';
7420: l_bind_names.DELETE;
7421:
7422: IF (AMS_DEBUG_HIGH_ON) THEN
7423:
7424: AMS_Utility_PVT.debug_message('The Return Status is ' || l_return_status);
7425: END IF;
7426:
7427: IF l_return_status <> FND_API.g_ret_sts_success THEN
7428: x_return_status := l_return_status;
7434: IF( l_venue_id IS NOT NULL AND l_venue_id <> p_evo_rec.event_venue_id)
7435: THEN
7436: IF (AMS_DEBUG_HIGH_ON) THEN
7437:
7438: AMS_Utility_PVT.debug_message('Calling Fulfilment Procedure for Venue change');
7439: END IF;
7440: IF (AMS_DEBUG_HIGH_ON) THEN
7441:
7442: AMS_Utility_PVT.debug_message('Old Venue Id is '||l_venue_id);
7438: AMS_Utility_PVT.debug_message('Calling Fulfilment Procedure for Venue change');
7439: END IF;
7440: IF (AMS_DEBUG_HIGH_ON) THEN
7441:
7442: AMS_Utility_PVT.debug_message('Old Venue Id is '||l_venue_id);
7443: END IF;
7444: IF (AMS_DEBUG_HIGH_ON) THEN
7445:
7446: AMS_Utility_PVT.debug_message('New Venue Id is '||p_evo_rec.event_venue_id);
7442: AMS_Utility_PVT.debug_message('Old Venue Id is '||l_venue_id);
7443: END IF;
7444: IF (AMS_DEBUG_HIGH_ON) THEN
7445:
7446: AMS_Utility_PVT.debug_message('New Venue Id is '||p_evo_rec.event_venue_id);
7447: END IF;
7448:
7449: l_bind_names(1) := 'OLD_VENUE_ID';
7450: l_bind_names(2) := 'EVENT_OFFER_ID';
7466: l_bind_names.DELETE;
7467:
7468: IF (AMS_DEBUG_HIGH_ON) THEN
7469:
7470: AMS_Utility_PVT.debug_message('The Return Status is ' || l_return_status);
7471:
7472: END IF;
7473:
7474: IF l_return_status <> FND_API.g_ret_sts_success THEN
7481: THEN
7482:
7483: IF (AMS_DEBUG_HIGH_ON) THEN
7484:
7485: AMS_Utility_PVT.debug_message('Calling Fulfilment api Venue has been created');
7486:
7487: END IF;
7488: IF (AMS_DEBUG_HIGH_ON) THEN
7489:
7486:
7487: END IF;
7488: IF (AMS_DEBUG_HIGH_ON) THEN
7489:
7490: AMS_Utility_PVT.debug_message('Old Venue Id is '||l_venue_id);
7491: END IF;
7492: IF (AMS_DEBUG_HIGH_ON) THEN
7493:
7494: AMS_Utility_PVT.debug_message('New Venue Id is '||p_evo_rec.event_venue_id);
7490: AMS_Utility_PVT.debug_message('Old Venue Id is '||l_venue_id);
7491: END IF;
7492: IF (AMS_DEBUG_HIGH_ON) THEN
7493:
7494: AMS_Utility_PVT.debug_message('New Venue Id is '||p_evo_rec.event_venue_id);
7495: END IF;
7496:
7497: --defaulting to 0 if its is null
7498: l_venue_id := 0;
7517: l_bind_names.DELETE;
7518:
7519: IF (AMS_DEBUG_HIGH_ON) THEN
7520:
7521: AMS_Utility_PVT.debug_message('The Return Status is ' || l_return_status);
7522:
7523: END IF;
7524:
7525: IF l_return_status <> FND_API.g_ret_sts_success THEN