[Home] [Help]
84: -- Initialize return status to sucess
85: x_return_status := FND_API.G_RET_STS_SUCCESS;
86:
87: -- check if user or group is valid
88: IF UPPER(p_user_or_group_type) = AMV_UTILITY_PVT.G_USER THEN
89: IF NOT AMV_UTILITY_PVT.Is_UserIdValid(p_user_or_group_id) THEN
90: x_error_msg := 'AMV_RESOURCE_ID_INVALID';
91: x_error_token := p_user_or_group_id;
92: RAISE FND_API.G_EXC_ERROR;
85: x_return_status := FND_API.G_RET_STS_SUCCESS;
86:
87: -- check if user or group is valid
88: IF UPPER(p_user_or_group_type) = AMV_UTILITY_PVT.G_USER THEN
89: IF NOT AMV_UTILITY_PVT.Is_UserIdValid(p_user_or_group_id) THEN
90: x_error_msg := 'AMV_RESOURCE_ID_INVALID';
91: x_error_token := p_user_or_group_id;
92: RAISE FND_API.G_EXC_ERROR;
93: END IF;
90: x_error_msg := 'AMV_RESOURCE_ID_INVALID';
91: x_error_token := p_user_or_group_id;
92: RAISE FND_API.G_EXC_ERROR;
93: END IF;
94: ELSIF UPPER(p_user_or_group_type) = AMV_UTILITY_PVT.G_GROUP THEN
95: IF NOT AMV_UTILITY_PVT.Is_GroupIdValid(p_user_or_group_id) THEN
96: x_error_msg := 'AMV_GROUP_ID_INVALID';
97: x_error_token := p_user_or_group_id;
98: RAISE FND_API.G_EXC_ERROR;
91: x_error_token := p_user_or_group_id;
92: RAISE FND_API.G_EXC_ERROR;
93: END IF;
94: ELSIF UPPER(p_user_or_group_type) = AMV_UTILITY_PVT.G_GROUP THEN
95: IF NOT AMV_UTILITY_PVT.Is_GroupIdValid(p_user_or_group_id) THEN
96: x_error_msg := 'AMV_GROUP_ID_INVALID';
97: x_error_token := p_user_or_group_id;
98: RAISE FND_API.G_EXC_ERROR;
99: END IF;
103: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104: END IF;
105:
106: -- check if channel or category exists
107: IF UPPER(p_subscribing_to_type) = AMV_UTILITY_PVT.G_CATEGORY THEN
108: IF NOT AMV_UTILITY_PVT.Is_CategoryIdValid(p_subscribing_to_id) THEN
109: x_error_msg := 'AMV_CATEGORY_ID_INVALID';
110: x_error_token := p_subscribing_to_id;
111: RAISE FND_API.G_EXC_ERROR;
104: END IF;
105:
106: -- check if channel or category exists
107: IF UPPER(p_subscribing_to_type) = AMV_UTILITY_PVT.G_CATEGORY THEN
108: IF NOT AMV_UTILITY_PVT.Is_CategoryIdValid(p_subscribing_to_id) THEN
109: x_error_msg := 'AMV_CATEGORY_ID_INVALID';
110: x_error_token := p_subscribing_to_id;
111: RAISE FND_API.G_EXC_ERROR;
112: END IF;
109: x_error_msg := 'AMV_CATEGORY_ID_INVALID';
110: x_error_token := p_subscribing_to_id;
111: RAISE FND_API.G_EXC_ERROR;
112: END IF;
113: ELSIF UPPER(p_subscribing_to_type) = AMV_UTILITY_PVT.G_CHANNEL THEN
114: IF NOT AMV_UTILITY_PVT.Is_ChannelIdValid(p_subscribing_to_id) THEN
115: x_error_msg := 'AMV_CHANNEL_ID_INVALID';
116: x_error_token := p_subscribing_to_id;
117: RAISE FND_API.G_EXC_ERROR;
110: x_error_token := p_subscribing_to_id;
111: RAISE FND_API.G_EXC_ERROR;
112: END IF;
113: ELSIF UPPER(p_subscribing_to_type) = AMV_UTILITY_PVT.G_CHANNEL THEN
114: IF NOT AMV_UTILITY_PVT.Is_ChannelIdValid(p_subscribing_to_id) THEN
115: x_error_msg := 'AMV_CHANNEL_ID_INVALID';
116: x_error_token := p_subscribing_to_id;
117: RAISE FND_API.G_EXC_ERROR;
118: END IF;
224: IF FND_API.To_Boolean (p_init_msg_list) THEN
225: FND_MSG_PUB.initialize;
226: END IF;
227: -- Get the current (login) user id.
228: AMV_UTILITY_PVT.Get_UserInfo(
229: x_resource_id => l_resource_id,
230: x_user_id => l_user_id,
231: x_login_id => l_login_user_id,
232: x_user_status => l_login_user_status
431:
432: -- check login user
433: IF (p_check_login_user = FND_API.G_TRUE) THEN
434: -- Get the current (login) user id.
435: AMV_UTILITY_PVT.Get_UserInfo(
436: x_resource_id => l_resource_id,
437: x_user_id => l_user_id,
438: x_login_id => l_login_user_id,
439: x_user_status => l_login_user_status
707: IF FND_API.To_Boolean (p_init_msg_list) THEN
708: FND_MSG_PUB.initialize;
709: END IF;
710: -- Get the current (login) user id.
711: AMV_UTILITY_PVT.Get_UserInfo(
712: x_resource_id => l_resource_id,
713: x_user_id => l_user_id,
714: x_login_id => l_login_user_id,
715: x_user_status => l_login_user_status
924: IF FND_API.To_Boolean (p_init_msg_list) THEN
925: FND_MSG_PUB.initialize;
926: END IF;
927: -- Get the current (login) user id.
928: AMV_UTILITY_PVT.Get_UserInfo(
929: x_resource_id => l_resource_id,
930: x_user_id => l_user_id,
931: x_login_id => l_login_user_id,
932: x_user_status => l_login_user_status
989: RAISE FND_API.G_EXC_ERROR;
990: END IF;
991: ELSE
992: -- check if my channel exists
993: IF AMV_UTILITY_PVT.Is_MyChannelIdValid(p_mychannel_obj.my_channel_id)
994: THEN
995: l_mychannel_id := p_mychannel_obj.my_channel_id;
996: END IF;
997: END IF;
1168: , notify_flag
1169: , notification_interval_type
1170: from amv_u_my_channels
1171: where user_or_group_id = p_user_id
1172: and user_or_group_type = AMV_UTILITY_PVT.G_USER
1173: union
1174: select my_channel_id
1175: , user_or_group_id
1176: , user_or_group_type
1185: where user_or_group_id in (select group_id
1186: from jtf_rs_group_members
1187: where resource_id = p_user_id and
1188: delete_flag = 'N')
1189: and user_or_group_type = AMV_UTILITY_PVT.G_GROUP;
1190:
1191: CURSOR Get_ChannelType_csr IS
1192: select channel_type
1193: , access_level_type
1218: IF FND_API.To_Boolean (p_init_msg_list) THEN
1219: FND_MSG_PUB.initialize;
1220: END IF;
1221: -- Get the current (login) user id.
1222: AMV_UTILITY_PVT.Get_UserInfo(
1223: x_resource_id => l_resource_id,
1224: x_user_id => l_user_id,
1225: x_login_id => l_login_user_id,
1226: x_user_status => l_login_user_status
1246: -- Initialize API return status to sucess
1247: x_return_status := FND_API.G_RET_STS_SUCCESS;
1248:
1249: --
1250: IF AMV_UTILITY_PVT.Is_UserIdValid(p_user_id) THEN
1251: x_mychannel_array := AMV_MY_CHANNEL_VARRAY_TYPE();
1252: OPEN My_Channels;
1253: LOOP
1254: FETCH My_Channels INTO l_mychannel_id,
1261: l_status,
1262: l_notify_flag,
1263: l_notification_interval_type;
1264: EXIT WHEN My_Channels%NOTFOUND;
1265: IF l_subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL THEN
1266: l_channel_id := l_subscribing_to_id;
1267: OPEN Get_ChannelType_csr;
1268: FETCH Get_ChannelType_csr INTO l_channel_type,
1269: l_access_level_type,
1443: or b1.channel_category_id in (
1444: select b2.channel_category_id
1445: from amv_c_categories_b b2
1446: where b2.parent_channel_category_id = p_category_id))
1447: and channel_type = AMV_UTILITY_PVT.G_CONTENT
1448: and access_level_type = AMV_UTILITY_PVT.G_PUBLIC;
1449: --
1450: BEGIN
1451: -- Standard begin of API savepoint
1444: select b2.channel_category_id
1445: from amv_c_categories_b b2
1446: where b2.parent_channel_category_id = p_category_id))
1447: and channel_type = AMV_UTILITY_PVT.G_CONTENT
1448: and access_level_type = AMV_UTILITY_PVT.G_PUBLIC;
1449: --
1450: BEGIN
1451: -- Standard begin of API savepoint
1452: SAVEPOINT Get_MyChannelsPerCategory_PVT;
1469: IF FND_API.To_Boolean (p_init_msg_list) THEN
1470: FND_MSG_PUB.initialize;
1471: END IF;
1472: -- Get the current (login) user id.
1473: AMV_UTILITY_PVT.Get_UserInfo(
1474: x_resource_id => l_resource_id,
1475: x_user_id => l_user_id,
1476: x_login_id => l_login_user_id,
1477: x_user_status => l_login_user_status
1720: IF FND_API.To_Boolean (p_init_msg_list) THEN
1721: FND_MSG_PUB.initialize;
1722: END IF;
1723: -- Get the current (login) user id.
1724: AMV_UTILITY_PVT.Get_UserInfo(
1725: x_resource_id => l_resource_id,
1726: x_user_id => l_user_id,
1727: x_login_id => l_login_user_id,
1728: x_user_status => l_login_user_status
1999: from amv_u_my_channels
2000: where user_or_group_id in (select group_id
2001: from jtf_rs_group_members
2002: where resource_id = p_user_id)
2003: and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2004: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
2005: union
2006: select subscribing_to_id
2007: from amv_u_my_channels
2000: where user_or_group_id in (select group_id
2001: from jtf_rs_group_members
2002: where resource_id = p_user_id)
2003: and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2004: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
2005: union
2006: select subscribing_to_id
2007: from amv_u_my_channels
2008: where user_or_group_id = p_user_id
2005: union
2006: select subscribing_to_id
2007: from amv_u_my_channels
2008: where user_or_group_id = p_user_id
2009: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2010: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL;
2011:
2012: -- NOTE currently not used
2013: CURSOR Get_MyCategories IS
2006: select subscribing_to_id
2007: from amv_u_my_channels
2008: where user_or_group_id = p_user_id
2009: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2010: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL;
2011:
2012: -- NOTE currently not used
2013: CURSOR Get_MyCategories IS
2014: select subscribing_to_id
2016: where user_or_group_id in (select group_id
2017: from jtf_rs_group_members
2018: where resource_id = p_user_id
2019: and delete_flag <> 'Y')
2020: and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2021: and subscribing_to_type = AMV_UTILITY_PVT.G_CATEGORY
2022: union
2023: select subscribing_to_id
2024: from amv_u_my_channels
2017: from jtf_rs_group_members
2018: where resource_id = p_user_id
2019: and delete_flag <> 'Y')
2020: and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2021: and subscribing_to_type = AMV_UTILITY_PVT.G_CATEGORY
2022: union
2023: select subscribing_to_id
2024: from amv_u_my_channels
2025: where user_or_group_id = p_user_id
2022: union
2023: select subscribing_to_id
2024: from amv_u_my_channels
2025: where user_or_group_id = p_user_id
2026: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2027: and subscribing_to_type = AMV_UTILITY_PVT.G_CATEGORY;
2028:
2029: CURSOR Get_CategoryItems_csr IS
2030: select ib.item_id
2023: select subscribing_to_id
2024: from amv_u_my_channels
2025: where user_or_group_id = p_user_id
2026: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2027: and subscribing_to_type = AMV_UTILITY_PVT.G_CATEGORY;
2028:
2029: CURSOR Get_CategoryItems_csr IS
2030: select ib.item_id
2031: , ib.item_name
2036: from amv_u_my_channels
2037: where user_or_group_id in (select group_id
2038: from jtf_rs_group_members
2039: where resource_id = p_user_id)
2040: and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2041: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
2042: union
2043: select subscribing_to_id
2044: from amv_u_my_channels
2037: where user_or_group_id in (select group_id
2038: from jtf_rs_group_members
2039: where resource_id = p_user_id)
2040: and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2041: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
2042: union
2043: select subscribing_to_id
2044: from amv_u_my_channels
2045: where user_or_group_id = p_user_id
2042: union
2043: select subscribing_to_id
2044: from amv_u_my_channels
2045: where user_or_group_id = p_user_id
2046: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2047: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
2048: and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
2049: and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
2050: and cim.available_for_channel_date <= sysdate
2043: select subscribing_to_id
2044: from amv_u_my_channels
2045: where user_or_group_id = p_user_id
2046: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2047: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
2048: and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
2049: and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
2050: and cim.available_for_channel_date <= sysdate
2051: and cim.item_id = ib.item_id
2044: from amv_u_my_channels
2045: where user_or_group_id = p_user_id
2046: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2047: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
2048: and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
2049: and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
2050: and cim.available_for_channel_date <= sysdate
2051: and cim.item_id = ib.item_id
2052: and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
2045: where user_or_group_id = p_user_id
2046: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2047: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
2048: and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
2049: and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
2050: and cim.available_for_channel_date <= sysdate
2051: and cim.item_id = ib.item_id
2052: and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
2053: and nvl(ib.expiration_date, sysdate) >= sysdate
2061: from amv_u_my_channels
2062: where user_or_group_id in (select group_id
2063: from jtf_rs_group_members
2064: where resource_id = p_user_id)
2065: and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2066: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
2067: union
2068: select subscribing_to_id
2069: from amv_u_my_channels
2062: where user_or_group_id in (select group_id
2063: from jtf_rs_group_members
2064: where resource_id = p_user_id)
2065: and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2066: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
2067: union
2068: select subscribing_to_id
2069: from amv_u_my_channels
2070: where user_or_group_id = p_user_id
2067: union
2068: select subscribing_to_id
2069: from amv_u_my_channels
2070: where user_or_group_id = p_user_id
2071: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2072: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
2073: and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
2074: and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
2075: and cim.available_for_channel_date <= sysdate
2068: select subscribing_to_id
2069: from amv_u_my_channels
2070: where user_or_group_id = p_user_id
2071: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2072: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
2073: and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
2074: and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
2075: and cim.available_for_channel_date <= sysdate
2076: and cim.item_id = ib.item_id
2069: from amv_u_my_channels
2070: where user_or_group_id = p_user_id
2071: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2072: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
2073: and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
2074: and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
2075: and cim.available_for_channel_date <= sysdate
2076: and cim.item_id = ib.item_id
2077: and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
2070: where user_or_group_id = p_user_id
2071: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2072: and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
2073: and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
2074: and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
2075: and cim.available_for_channel_date <= sysdate
2076: and cim.item_id = ib.item_id
2077: and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
2078: and nvl(ib.expiration_date, sysdate) >= sysdate;
2099: IF FND_API.To_Boolean (p_init_msg_list) THEN
2100: FND_MSG_PUB.initialize;
2101: END IF;
2102: -- Get the current (login) user id.
2103: AMV_UTILITY_PVT.Get_UserInfo(
2104: x_resource_id => l_resource_id,
2105: x_user_id => l_user_id,
2106: x_login_id => l_login_user_id,
2107: x_user_status => l_login_user_status
2330: IF FND_API.To_Boolean (p_init_msg_list) THEN
2331: FND_MSG_PUB.initialize;
2332: END IF;
2333: -- Get the current (login) user id.
2334: AMV_UTILITY_PVT.Get_UserInfo(
2335: x_resource_id => l_resource_id,
2336: x_user_id => l_user_id,
2337: x_login_id => l_login_user_id,
2338: x_user_status => l_login_user_status
2506:
2507: CURSOR Get_SecAppr_csr IS
2508: select chl_approver_flag
2509: from amv_u_access
2510: where access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
2511: and access_to_table_record_id = p_channel_id
2512: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2513: and user_or_group_id = p_user_id
2514: and effective_start_date <= sysdate
2508: select chl_approver_flag
2509: from amv_u_access
2510: where access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
2511: and access_to_table_record_id = p_channel_id
2512: and user_or_group_type = AMV_UTILITY_PVT.G_USER
2513: and user_or_group_id = p_user_id
2514: and effective_start_date <= sysdate
2515: and nvl(expiration_date, sysdate) >= sysdate;
2516:
2534: IF FND_API.To_Boolean (p_init_msg_list) THEN
2535: FND_MSG_PUB.initialize;
2536: END IF;
2537: -- Get the current (login) user id.
2538: AMV_UTILITY_PVT.Get_UserInfo(
2539: x_resource_id => l_resource_id,
2540: x_user_id => l_user_id,
2541: x_login_id => l_login_user_id,
2542: x_user_status => l_login_user_status