[Home] [Help]
81:
82: BEGIN
83:
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
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;
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';
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;
100: ELSE
101: x_error_msg := 'AMV_USER_OR_GROUP_TYPE_INVALID';
102: x_error_token := p_user_or_group_type;
99: END IF;
100: ELSE
101: x_error_msg := 'AMV_USER_OR_GROUP_TYPE_INVALID';
102: x_error_token := p_user_or_group_type;
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
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;
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';
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;
119: ELSE
120: x_error_msg := 'AMV_SUB_TO_TYPE_INVALID';
121: x_error_token := p_subscribing_to_type;
118: END IF;
119: ELSE
120: x_error_msg := 'AMV_SUB_TO_TYPE_INVALID';
121: x_error_token := p_subscribing_to_type;
122: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123: END IF;
124:
125: OPEN My_Channel;
126: FETCH My_Channel INTO x_mychannel_id;
124:
125: OPEN My_Channel;
126: FETCH My_Channel INTO x_mychannel_id;
127: IF My_Channel%NOTFOUND THEN
128: x_mychannel_id := FND_API.G_MISS_NUM;
129: END IF;
130: CLOSE My_Channel;
131:
132: -- set validity to true
129: END IF;
130: CLOSE My_Channel;
131:
132: -- set validity to true
133: x_valid_flag := FND_API.G_TRUE;
134:
135: EXCEPTION
136: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
132: -- set validity to true
133: x_valid_flag := FND_API.G_TRUE;
134:
135: EXCEPTION
136: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138: x_valid_flag := FND_API.G_FALSE;
139: WHEN FND_API.G_EXC_ERROR THEN
140: x_return_status := FND_API.G_RET_STS_ERROR;
133: x_valid_flag := FND_API.G_TRUE;
134:
135: EXCEPTION
136: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138: x_valid_flag := FND_API.G_FALSE;
139: WHEN FND_API.G_EXC_ERROR THEN
140: x_return_status := FND_API.G_RET_STS_ERROR;
141: x_valid_flag := FND_API.G_FALSE;
134:
135: EXCEPTION
136: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138: x_valid_flag := FND_API.G_FALSE;
139: WHEN FND_API.G_EXC_ERROR THEN
140: x_return_status := FND_API.G_RET_STS_ERROR;
141: x_valid_flag := FND_API.G_FALSE;
142: WHEN OTHERS THEN
135: EXCEPTION
136: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138: x_valid_flag := FND_API.G_FALSE;
139: WHEN FND_API.G_EXC_ERROR THEN
140: x_return_status := FND_API.G_RET_STS_ERROR;
141: x_valid_flag := FND_API.G_FALSE;
142: WHEN OTHERS THEN
143: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
136: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138: x_valid_flag := FND_API.G_FALSE;
139: WHEN FND_API.G_EXC_ERROR THEN
140: x_return_status := FND_API.G_RET_STS_ERROR;
141: x_valid_flag := FND_API.G_FALSE;
142: WHEN OTHERS THEN
143: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144: x_valid_flag := FND_API.G_FALSE;
137: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138: x_valid_flag := FND_API.G_FALSE;
139: WHEN FND_API.G_EXC_ERROR THEN
140: x_return_status := FND_API.G_RET_STS_ERROR;
141: x_valid_flag := FND_API.G_FALSE;
142: WHEN OTHERS THEN
143: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144: x_valid_flag := FND_API.G_FALSE;
145: x_error_msg := 'AMV_SUB_VALIDATION_FAILED';
139: WHEN FND_API.G_EXC_ERROR THEN
140: x_return_status := FND_API.G_RET_STS_ERROR;
141: x_valid_flag := FND_API.G_FALSE;
142: WHEN OTHERS THEN
143: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144: x_valid_flag := FND_API.G_FALSE;
145: x_error_msg := 'AMV_SUB_VALIDATION_FAILED';
146: END Validate_Subscription;
147:
140: x_return_status := FND_API.G_RET_STS_ERROR;
141: x_valid_flag := FND_API.G_FALSE;
142: WHEN OTHERS THEN
143: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144: x_valid_flag := FND_API.G_FALSE;
145: x_error_msg := 'AMV_SUB_VALIDATION_FAILED';
146: END Validate_Subscription;
147:
148: --------------------------------------------------------------------------------
154: -- Function : Launch workflow to subscribe a channel for an user
155: -- Parameters :
156: -- IN p_api_version IN NUMBER Required
157: -- p_init_msg_list IN VARCHAR2 Optional
158: -- Default = FND_API.G_FALSE
159: -- p_commit IN VARCHAR2 Optional
160: -- Default = FND_API.G_FALSE
161: -- p_validation_level IN NUMBER Optional
162: -- Default = FND_API.G_VALID_LEVEL_FULL
156: -- IN p_api_version IN NUMBER Required
157: -- p_init_msg_list IN VARCHAR2 Optional
158: -- Default = FND_API.G_FALSE
159: -- p_commit IN VARCHAR2 Optional
160: -- Default = FND_API.G_FALSE
161: -- p_validation_level IN NUMBER Optional
162: -- Default = FND_API.G_VALID_LEVEL_FULL
163: -- p_check_login_user IN VARCHAR2 Optional
164: -- Default = FND_API.G_TRUE
158: -- Default = FND_API.G_FALSE
159: -- p_commit IN VARCHAR2 Optional
160: -- Default = FND_API.G_FALSE
161: -- p_validation_level IN NUMBER Optional
162: -- Default = FND_API.G_VALID_LEVEL_FULL
163: -- p_check_login_user IN VARCHAR2 Optional
164: -- Default = FND_API.G_TRUE
165: -- p_channel_id IN NUMBER Required
166: -- OUT : x_return_status OUT VARCHAR2
160: -- Default = FND_API.G_FALSE
161: -- p_validation_level IN NUMBER Optional
162: -- Default = FND_API.G_VALID_LEVEL_FULL
163: -- p_check_login_user IN VARCHAR2 Optional
164: -- Default = FND_API.G_TRUE
165: -- p_channel_id IN NUMBER Required
166: -- OUT : x_return_status OUT VARCHAR2
167: -- x_msg_count OUT NUMBER
168: -- x_msg_data OUT VARCHAR2
173: --
174: -- End of comments
175: PROCEDURE Subscribe_Channel
176: ( p_api_version IN NUMBER,
177: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178: p_commit IN VARCHAR2 := FND_API.G_FALSE,
179: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
181: p_channel_id IN NUMBER,
174: -- End of comments
175: PROCEDURE Subscribe_Channel
176: ( p_api_version IN NUMBER,
177: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178: p_commit IN VARCHAR2 := FND_API.G_FALSE,
179: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
181: p_channel_id IN NUMBER,
182: x_return_status OUT NOCOPY VARCHAR2,
175: PROCEDURE Subscribe_Channel
176: ( p_api_version IN NUMBER,
177: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178: p_commit IN VARCHAR2 := FND_API.G_FALSE,
179: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
181: p_channel_id IN NUMBER,
182: x_return_status OUT NOCOPY VARCHAR2,
183: x_msg_count OUT NOCOPY NUMBER,
176: ( p_api_version IN NUMBER,
177: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178: p_commit IN VARCHAR2 := FND_API.G_FALSE,
179: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
181: p_channel_id IN NUMBER,
182: x_return_status OUT NOCOPY VARCHAR2,
183: x_msg_count OUT NOCOPY NUMBER,
184: x_msg_data OUT NOCOPY VARCHAR2
189: l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
190: --
191: l_resource_id number;
192: l_user_id number;
193: l_login_user_id number := FND_API.G_MISS_NUM;
194: l_login_user_status varchar2(30);
195: --
196: l_owner_name varchar2(100);
197: l_owner_id number;
205: BEGIN
206: -- Standard begin of API savepoint
207: SAVEPOINT Subscribe_Channel_PVT;
208: -- Standard call to check for call compatibility.
209: IF NOT FND_API.Compatible_API_Call (
210: l_api_version,
211: p_api_version,
212: l_api_name,
213: G_PKG_NAME)
211: p_api_version,
212: l_api_name,
213: G_PKG_NAME)
214: THEN
215: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216: END IF;
217: -- Debug Message
218: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
219: FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
220: FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
221: FND_MSG_PUB.Add;
222: END IF;
223: --Initialize message list if p_init_msg_list is TRUE.
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(
231: x_login_id => l_login_user_id,
232: x_user_status => l_login_user_status
233: );
234: -- check login user
235: IF (p_check_login_user = FND_API.G_TRUE) THEN
236: -- Check if user is login and has the required privilege.
237: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
238: -- User is not login.
239: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
233: );
234: -- check login user
235: IF (p_check_login_user = FND_API.G_TRUE) THEN
236: -- Check if user is login and has the required privilege.
237: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
238: -- User is not login.
239: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
240: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
241: FND_MSG_PUB.Add;
239: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
240: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
241: FND_MSG_PUB.Add;
242: END IF;
243: RAISE FND_API.G_EXC_ERROR;
244: END IF;
245: END IF;
246: -- This fix is for executing api in sqlplus mode
247: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
243: RAISE FND_API.G_EXC_ERROR;
244: END IF;
245: END IF;
246: -- This fix is for executing api in sqlplus mode
247: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
248: l_login_user_id := -1;
249: l_user_id := -1;
250: l_resource_id := -1;
251: END IF;
249: l_user_id := -1;
250: l_resource_id := -1;
251: END IF;
252: -- Initialize API return status to sucess
253: x_return_status := FND_API.G_RET_STS_SUCCESS;
254:
255: OPEN Get_OwnerIDAndName_csr (l_resource_id);
256: FETCH Get_OwnerIDAndName_csr INTO l_owner_name, l_owner_id;
257: IF (Get_OwnerIDAndName_csr%NOTFOUND) THEN
261: FND_MESSAGE.Set_Token('RECORD', 'AMV_CHANNEL_TK', TRUE);
262: FND_MESSAGE.Set_Token('ID', to_char(nvl(l_resource_id,-1)));
263: FND_MSG_PUB.Add;
264: END IF;
265: RAISE FND_API.G_EXC_ERROR;
266: END IF;
267: CLOSE Get_OwnerIDAndName_csr;
268:
269: -- Lauch WorkFlow Process: 'AMV_SUBSCRIPTION_APPROVAL'
281: FND_MESSAGE.Set_Token('ROW', l_full_name);
282: FND_MSG_PUB.Add;
283: END IF;
284: -- Standard check of commit
285: IF FND_API.To_Boolean ( p_commit ) THEN
286: COMMIT WORK;
287: END IF;
288: -- Debug Message
289: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
292: FND_MSG_PUB.Add;
293: END IF;
294: --Standard call to get message count and if count=1, get the message
295: FND_MSG_PUB.Count_And_Get (
296: p_encoded => FND_API.G_FALSE,
297: p_count => x_msg_count,
298: p_data => x_msg_data
299: );
300: EXCEPTION
297: p_count => x_msg_count,
298: p_data => x_msg_data
299: );
300: EXCEPTION
301: WHEN FND_API.G_EXC_ERROR THEN
302: ROLLBACK TO Subscribe_Channel_PVT;
303: x_return_status := FND_API.G_RET_STS_ERROR;
304: -- Standard call to get message count and if count=1, get the message
305: FND_MSG_PUB.Count_And_Get (
299: );
300: EXCEPTION
301: WHEN FND_API.G_EXC_ERROR THEN
302: ROLLBACK TO Subscribe_Channel_PVT;
303: x_return_status := FND_API.G_RET_STS_ERROR;
304: -- Standard call to get message count and if count=1, get the message
305: FND_MSG_PUB.Count_And_Get (
306: p_encoded => FND_API.G_FALSE,
307: p_count => x_msg_count,
302: ROLLBACK TO Subscribe_Channel_PVT;
303: x_return_status := FND_API.G_RET_STS_ERROR;
304: -- Standard call to get message count and if count=1, get the message
305: FND_MSG_PUB.Count_And_Get (
306: p_encoded => FND_API.G_FALSE,
307: p_count => x_msg_count,
308: p_data => x_msg_data
309: );
310: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
306: p_encoded => FND_API.G_FALSE,
307: p_count => x_msg_count,
308: p_data => x_msg_data
309: );
310: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
311: ROLLBACK TO Subscribe_Channel_PVT;
312: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313: -- Standard call to get message count and if count=1, get the message
314: FND_MSG_PUB.Count_And_Get (
308: p_data => x_msg_data
309: );
310: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
311: ROLLBACK TO Subscribe_Channel_PVT;
312: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313: -- Standard call to get message count and if count=1, get the message
314: FND_MSG_PUB.Count_And_Get (
315: p_encoded => FND_API.G_FALSE,
316: p_count => x_msg_count,
311: ROLLBACK TO Subscribe_Channel_PVT;
312: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313: -- Standard call to get message count and if count=1, get the message
314: FND_MSG_PUB.Count_And_Get (
315: p_encoded => FND_API.G_FALSE,
316: p_count => x_msg_count,
317: p_data => x_msg_data
318: );
319: WHEN OTHERS THEN
317: p_data => x_msg_data
318: );
319: WHEN OTHERS THEN
320: ROLLBACK TO Subscribe_Channel_PVT;
321: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
322: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
323: THEN
324: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
325: END IF;
324: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
325: END IF;
326: -- Standard call to get message count and if count=1, get the message
327: FND_MSG_PUB.Count_And_Get (
328: p_encoded => FND_API.G_FALSE,
329: p_count => x_msg_count,
330: p_data => x_msg_data
331: );
332: --
344: -- Function : Subscribe a channel/category for an user
345: -- Parameters :
346: -- IN p_api_version IN NUMBER Required
347: -- p_init_msg_list IN VARCHAR2 Optional
348: -- Default = FND_API.G_FALSE
349: -- p_commit IN VARCHAR2 Optional
350: -- Default = FND_API.G_FALSE
351: -- p_validation_level IN NUMBER Optional
352: -- Default = FND_API.G_VALID_LEVEL_FULL
346: -- IN p_api_version IN NUMBER Required
347: -- p_init_msg_list IN VARCHAR2 Optional
348: -- Default = FND_API.G_FALSE
349: -- p_commit IN VARCHAR2 Optional
350: -- Default = FND_API.G_FALSE
351: -- p_validation_level IN NUMBER Optional
352: -- Default = FND_API.G_VALID_LEVEL_FULL
353: -- p_check_login_user IN VARCHAR2 Optional
354: -- Default = FND_API.G_TRUE
348: -- Default = FND_API.G_FALSE
349: -- p_commit IN VARCHAR2 Optional
350: -- Default = FND_API.G_FALSE
351: -- p_validation_level IN NUMBER Optional
352: -- Default = FND_API.G_VALID_LEVEL_FULL
353: -- p_check_login_user IN VARCHAR2 Optional
354: -- Default = FND_API.G_TRUE
355: -- p_mychannel_obj IN
356: -- AMV_MY_CHANNEL_OBJ_TYPE Required
350: -- Default = FND_API.G_FALSE
351: -- p_validation_level IN NUMBER Optional
352: -- Default = FND_API.G_VALID_LEVEL_FULL
353: -- p_check_login_user IN VARCHAR2 Optional
354: -- Default = FND_API.G_TRUE
355: -- p_mychannel_obj IN
356: -- AMV_MY_CHANNEL_OBJ_TYPE Required
357: -- MyChannel data to be created.
358: -- OUT : x_return_status OUT VARCHAR2
367: -- End of comments
368: --
369: PROCEDURE Add_Subscription
370: ( p_api_version IN NUMBER,
371: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
372: p_commit IN VARCHAR2 := FND_API.G_FALSE,
373: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
374: x_return_status OUT NOCOPY VARCHAR2,
375: x_msg_count OUT NOCOPY NUMBER,
368: --
369: PROCEDURE Add_Subscription
370: ( p_api_version IN NUMBER,
371: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
372: p_commit IN VARCHAR2 := FND_API.G_FALSE,
373: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
374: x_return_status OUT NOCOPY VARCHAR2,
375: x_msg_count OUT NOCOPY NUMBER,
376: x_msg_data OUT NOCOPY VARCHAR2,
369: PROCEDURE Add_Subscription
370: ( p_api_version IN NUMBER,
371: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
372: p_commit IN VARCHAR2 := FND_API.G_FALSE,
373: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
374: x_return_status OUT NOCOPY VARCHAR2,
375: x_msg_count OUT NOCOPY NUMBER,
376: x_msg_data OUT NOCOPY VARCHAR2,
377: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
373: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
374: x_return_status OUT NOCOPY VARCHAR2,
375: x_msg_count OUT NOCOPY NUMBER,
376: x_msg_data OUT NOCOPY VARCHAR2,
377: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
378: p_mychannel_obj IN AMV_MY_CHANNEL_OBJ_TYPE,
379: x_mychannel_id OUT NOCOPY NUMBER
380: )
381: IS
384: l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
385: --
386: l_resource_id number;
387: l_user_id number;
388: l_login_user_id number := FND_API.G_MISS_NUM;
389: l_login_user_status varchar2(30);
390: l_Error_Msg varchar2(2000);
391: l_Error_Token varchar2(80);
392: l_object_version_number number := 1;
409: BEGIN
410: -- Standard begin of API savepoint
411: SAVEPOINT Add_Subscription_PVT;
412: -- Standard call to check for call compatibility.
413: IF NOT FND_API.Compatible_API_Call (
414: l_api_version,
415: p_api_version,
416: l_api_name,
417: G_PKG_NAME)
415: p_api_version,
416: l_api_name,
417: G_PKG_NAME)
418: THEN
419: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420: END IF;
421: -- Debug Message
422: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
423: FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
424: FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
425: FND_MSG_PUB.Add;
426: END IF;
427: --Initialize message list if p_init_msg_list is TRUE.
428: IF FND_API.To_Boolean (p_init_msg_list) THEN
429: FND_MSG_PUB.initialize;
430: END IF;
431:
432: -- check login user
429: FND_MSG_PUB.initialize;
430: END IF;
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
440: );
441: -- Check if user is login and has the required privilege.
442: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
443: -- User is not login.
444: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
445: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
446: FND_MSG_PUB.Add;
444: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
445: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
446: FND_MSG_PUB.Add;
447: END IF;
448: RAISE FND_API.G_EXC_ERROR;
449: END IF;
450: END IF;
451: -- This fix is for executing api in sqlplus mode
452: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
448: RAISE FND_API.G_EXC_ERROR;
449: END IF;
450: END IF;
451: -- This fix is for executing api in sqlplus mode
452: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
453: l_login_user_id := -1;
454: l_user_id := -1;
455: l_resource_id := -1;
456: END IF;
454: l_user_id := -1;
455: l_resource_id := -1;
456: END IF;
457: -- Initialize API return status to sucess
458: x_return_status := FND_API.G_RET_STS_SUCCESS;
459: --
460:
461: -- Validate the subscription
462: Validate_Subscription (
471: x_error_token => l_Error_Token
472: );
473:
474: -- create subscription after validation
475: IF l_valid_flag = FND_API.G_TRUE THEN
476: IF l_mychannel_id <> FND_API.G_MISS_NUM THEN
477: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
478: THEN
479: FND_MESSAGE.Set_Name('AMV', 'AMV_SUBSCRIPTION_EXISTS');
472: );
473:
474: -- create subscription after validation
475: IF l_valid_flag = FND_API.G_TRUE THEN
476: IF l_mychannel_id <> FND_API.G_MISS_NUM THEN
477: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
478: THEN
479: FND_MESSAGE.Set_Name('AMV', 'AMV_SUBSCRIPTION_EXISTS');
480: FND_MESSAGE.Set_Token('TKN',p_mychannel_obj.user_or_group_type);
479: FND_MESSAGE.Set_Name('AMV', 'AMV_SUBSCRIPTION_EXISTS');
480: FND_MESSAGE.Set_Token('TKN',p_mychannel_obj.user_or_group_type);
481: FND_MSG_PUB.Add;
482: END IF;
483: RAISE FND_API.G_EXC_ERROR;
484: ELSE
485: -- set mychannel order
486: IF p_mychannel_obj.order_number = FND_API.G_MISS_NUM THEN
487: OPEN MyChannelOrder;
482: END IF;
483: RAISE FND_API.G_EXC_ERROR;
484: ELSE
485: -- set mychannel order
486: IF p_mychannel_obj.order_number = FND_API.G_MISS_NUM THEN
487: OPEN MyChannelOrder;
488: FETCH MyChannelOrder INTO l_order;
489: CLOSE MyChannelOrder;
490: ELSE
542: FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
543: FND_MESSAGE.Set_Token('TKN',l_Error_Token);
544: FND_MSG_PUB.Add;
545: END IF;
546: RAISE FND_API.G_EXC_ERROR;
547: END IF;
548: --
549: -- Success message
550: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
553: FND_MESSAGE.Set_Token('ROW', l_full_name);
554: FND_MSG_PUB.Add;
555: END IF;
556: --Standard check of commit
557: IF FND_API.To_Boolean ( p_commit ) THEN
558: COMMIT WORK;
559: END IF;
560: -- Debug Message
561: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
564: FND_MSG_PUB.Add;
565: END IF;
566: --Standard call to get message count and if count=1, get the message
567: FND_MSG_PUB.Count_And_Get (
568: p_encoded => FND_API.G_FALSE,
569: p_count => x_msg_count,
570: p_data => x_msg_data
571: );
572: EXCEPTION
569: p_count => x_msg_count,
570: p_data => x_msg_data
571: );
572: EXCEPTION
573: WHEN FND_API.G_EXC_ERROR THEN
574: ROLLBACK TO Add_Subscription_PVT;
575: x_return_status := FND_API.G_RET_STS_ERROR;
576: -- Standard call to get message count and if count=1, get the message
577: FND_MSG_PUB.Count_And_Get (
571: );
572: EXCEPTION
573: WHEN FND_API.G_EXC_ERROR THEN
574: ROLLBACK TO Add_Subscription_PVT;
575: x_return_status := FND_API.G_RET_STS_ERROR;
576: -- Standard call to get message count and if count=1, get the message
577: FND_MSG_PUB.Count_And_Get (
578: p_encoded => FND_API.G_FALSE,
579: p_count => x_msg_count,
574: ROLLBACK TO Add_Subscription_PVT;
575: x_return_status := FND_API.G_RET_STS_ERROR;
576: -- Standard call to get message count and if count=1, get the message
577: FND_MSG_PUB.Count_And_Get (
578: p_encoded => FND_API.G_FALSE,
579: p_count => x_msg_count,
580: p_data => x_msg_data
581: );
582: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
578: p_encoded => FND_API.G_FALSE,
579: p_count => x_msg_count,
580: p_data => x_msg_data
581: );
582: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
583: ROLLBACK TO Add_Subscription_PVT;
584: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585: -- Standard call to get message count and if count=1, get the message
586: FND_MSG_PUB.Count_And_Get (
580: p_data => x_msg_data
581: );
582: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
583: ROLLBACK TO Add_Subscription_PVT;
584: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585: -- Standard call to get message count and if count=1, get the message
586: FND_MSG_PUB.Count_And_Get (
587: p_encoded => FND_API.G_FALSE,
588: p_count => x_msg_count,
583: ROLLBACK TO Add_Subscription_PVT;
584: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585: -- Standard call to get message count and if count=1, get the message
586: FND_MSG_PUB.Count_And_Get (
587: p_encoded => FND_API.G_FALSE,
588: p_count => x_msg_count,
589: p_data => x_msg_data
590: );
591: WHEN OTHERS THEN
589: p_data => x_msg_data
590: );
591: WHEN OTHERS THEN
592: ROLLBACK TO Add_Subscription_PVT;
593: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
594: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
595: THEN
596: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
597: END IF;
596: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
597: END IF;
598: -- Standard call to get message count and if count=1, get the message
599: FND_MSG_PUB.Count_And_Get (
600: p_encoded => FND_API.G_FALSE,
601: p_count => x_msg_count,
602: p_data => x_msg_data
603: );
604: --
611: -- Function : Delete the subscription given p_mychannel_id.
612: -- Parameters :
613: -- IN p_api_version IN NUMBER Required
614: -- p_init_msg_list IN VARCHAR2 Optional
615: -- Default = FND_API.G_FALSE
616: -- p_commit IN VARCHAR2 Optional
617: -- Default = FND_API.G_FALSE
618: -- p_validation_level IN NUMBER Optional
619: -- Default = FND_API.G_VALID_LEVEL_FULL
613: -- IN p_api_version IN NUMBER Required
614: -- p_init_msg_list IN VARCHAR2 Optional
615: -- Default = FND_API.G_FALSE
616: -- p_commit IN VARCHAR2 Optional
617: -- Default = FND_API.G_FALSE
618: -- p_validation_level IN NUMBER Optional
619: -- Default = FND_API.G_VALID_LEVEL_FULL
620: -- p_check_login_user IN VARCHAR2 Optional
621: -- Default = FND_API.G_TRUE
615: -- Default = FND_API.G_FALSE
616: -- p_commit IN VARCHAR2 Optional
617: -- Default = FND_API.G_FALSE
618: -- p_validation_level IN NUMBER Optional
619: -- Default = FND_API.G_VALID_LEVEL_FULL
620: -- p_check_login_user IN VARCHAR2 Optional
621: -- Default = FND_API.G_TRUE
622: -- p_mychannel_id IN NUMBER Required
623: -- MyChannel Id
617: -- Default = FND_API.G_FALSE
618: -- p_validation_level IN NUMBER Optional
619: -- Default = FND_API.G_VALID_LEVEL_FULL
620: -- p_check_login_user IN VARCHAR2 Optional
621: -- Default = FND_API.G_TRUE
622: -- p_mychannel_id IN NUMBER Required
623: -- MyChannel Id
624: -- p_user_or_group_id IN NUMBER Optional
625: -- Default = FND_API.G_MISS_NUM
621: -- Default = FND_API.G_TRUE
622: -- p_mychannel_id IN NUMBER Required
623: -- MyChannel Id
624: -- p_user_or_group_id IN NUMBER Optional
625: -- Default = FND_API.G_MISS_NUM
626: -- p_user_or_group_type IN VARCHAR2 Optional
627: -- Default = FND_API.G_MISS_CHAR
628: -- p_subscribing_to_id IN NUMBER Optional
629: -- Default = FND_API.G_MISS_NUM
623: -- MyChannel Id
624: -- p_user_or_group_id IN NUMBER Optional
625: -- Default = FND_API.G_MISS_NUM
626: -- p_user_or_group_type IN VARCHAR2 Optional
627: -- Default = FND_API.G_MISS_CHAR
628: -- p_subscribing_to_id IN NUMBER Optional
629: -- Default = FND_API.G_MISS_NUM
630: -- p_subscribing_to_type IN VARCHAR2 Optional
631: -- Default = FND_API.G_MISS_CHAR
625: -- Default = FND_API.G_MISS_NUM
626: -- p_user_or_group_type IN VARCHAR2 Optional
627: -- Default = FND_API.G_MISS_CHAR
628: -- p_subscribing_to_id IN NUMBER Optional
629: -- Default = FND_API.G_MISS_NUM
630: -- p_subscribing_to_type IN VARCHAR2 Optional
631: -- Default = FND_API.G_MISS_CHAR
632: -- Pass the user and subscription id and types
633: -- instead of mychannel id.
627: -- Default = FND_API.G_MISS_CHAR
628: -- p_subscribing_to_id IN NUMBER Optional
629: -- Default = FND_API.G_MISS_NUM
630: -- p_subscribing_to_type IN VARCHAR2 Optional
631: -- Default = FND_API.G_MISS_CHAR
632: -- Pass the user and subscription id and types
633: -- instead of mychannel id.
634: -- Must pass the mychannel id or the joint key of
635: -- above four values
644: -- End of comments
645: --
646: PROCEDURE Remove_Subscription
647: ( p_api_version IN NUMBER,
648: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
649: p_commit IN VARCHAR2 := FND_API.G_FALSE,
650: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
651: x_return_status OUT NOCOPY VARCHAR2,
652: x_msg_count OUT NOCOPY NUMBER,
645: --
646: PROCEDURE Remove_Subscription
647: ( p_api_version IN NUMBER,
648: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
649: p_commit IN VARCHAR2 := FND_API.G_FALSE,
650: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
651: x_return_status OUT NOCOPY VARCHAR2,
652: x_msg_count OUT NOCOPY NUMBER,
653: x_msg_data OUT NOCOPY VARCHAR2,
646: PROCEDURE Remove_Subscription
647: ( p_api_version IN NUMBER,
648: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
649: p_commit IN VARCHAR2 := FND_API.G_FALSE,
650: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
651: x_return_status OUT NOCOPY VARCHAR2,
652: x_msg_count OUT NOCOPY NUMBER,
653: x_msg_data OUT NOCOPY VARCHAR2,
654: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
650: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
651: x_return_status OUT NOCOPY VARCHAR2,
652: x_msg_count OUT NOCOPY NUMBER,
653: x_msg_data OUT NOCOPY VARCHAR2,
654: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
655: p_mychannel_id IN NUMBER := FND_API.G_MISS_NUM,
656: p_user_or_group_id IN NUMBER := FND_API.G_MISS_NUM,
657: p_user_or_group_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
658: p_subscribing_to_id IN NUMBER := FND_API.G_MISS_NUM,
651: x_return_status OUT NOCOPY VARCHAR2,
652: x_msg_count OUT NOCOPY NUMBER,
653: x_msg_data OUT NOCOPY VARCHAR2,
654: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
655: p_mychannel_id IN NUMBER := FND_API.G_MISS_NUM,
656: p_user_or_group_id IN NUMBER := FND_API.G_MISS_NUM,
657: p_user_or_group_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
658: p_subscribing_to_id IN NUMBER := FND_API.G_MISS_NUM,
659: p_subscribing_to_type IN VARCHAR2 := FND_API.G_MISS_CHAR
652: x_msg_count OUT NOCOPY NUMBER,
653: x_msg_data OUT NOCOPY VARCHAR2,
654: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
655: p_mychannel_id IN NUMBER := FND_API.G_MISS_NUM,
656: p_user_or_group_id IN NUMBER := FND_API.G_MISS_NUM,
657: p_user_or_group_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
658: p_subscribing_to_id IN NUMBER := FND_API.G_MISS_NUM,
659: p_subscribing_to_type IN VARCHAR2 := FND_API.G_MISS_CHAR
660: )
653: x_msg_data OUT NOCOPY VARCHAR2,
654: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
655: p_mychannel_id IN NUMBER := FND_API.G_MISS_NUM,
656: p_user_or_group_id IN NUMBER := FND_API.G_MISS_NUM,
657: p_user_or_group_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
658: p_subscribing_to_id IN NUMBER := FND_API.G_MISS_NUM,
659: p_subscribing_to_type IN VARCHAR2 := FND_API.G_MISS_CHAR
660: )
661: IS
654: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
655: p_mychannel_id IN NUMBER := FND_API.G_MISS_NUM,
656: p_user_or_group_id IN NUMBER := FND_API.G_MISS_NUM,
657: p_user_or_group_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
658: p_subscribing_to_id IN NUMBER := FND_API.G_MISS_NUM,
659: p_subscribing_to_type IN VARCHAR2 := FND_API.G_MISS_CHAR
660: )
661: IS
662: l_api_name CONSTANT VARCHAR2(30) := 'Remove_Subscription';
655: p_mychannel_id IN NUMBER := FND_API.G_MISS_NUM,
656: p_user_or_group_id IN NUMBER := FND_API.G_MISS_NUM,
657: p_user_or_group_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
658: p_subscribing_to_id IN NUMBER := FND_API.G_MISS_NUM,
659: p_subscribing_to_type IN VARCHAR2 := FND_API.G_MISS_CHAR
660: )
661: IS
662: l_api_name CONSTANT VARCHAR2(30) := 'Remove_Subscription';
663: l_api_version CONSTANT NUMBER := 1.0;
688: BEGIN
689: -- Standard begin of API savepoint
690: SAVEPOINT Remove_Subscription_PVT;
691: -- Standard call to check for call compatibility.
692: IF NOT FND_API.Compatible_API_Call (
693: l_api_version,
694: p_api_version,
695: l_api_name,
696: G_PKG_NAME)
694: p_api_version,
695: l_api_name,
696: G_PKG_NAME)
697: THEN
698: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699: END IF;
700: -- Debug Message
701: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
702: FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
703: FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
704: FND_MSG_PUB.Add;
705: END IF;
706: --Initialize message list if p_init_msg_list is TRUE.
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(
714: x_login_id => l_login_user_id,
715: x_user_status => l_login_user_status
716: );
717: -- check login user
718: IF (p_check_login_user = FND_API.G_TRUE) THEN
719: -- Check if user is login and has the required privilege.
720: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
721: -- User is not login.
722: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
716: );
717: -- check login user
718: IF (p_check_login_user = FND_API.G_TRUE) THEN
719: -- Check if user is login and has the required privilege.
720: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
721: -- User is not login.
722: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
723: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
724: FND_MSG_PUB.Add;
722: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
723: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
724: FND_MSG_PUB.Add;
725: END IF;
726: RAISE FND_API.G_EXC_ERROR;
727: END IF;
728: END IF;
729: -- This fix is for executing api in sqlplus mode
730: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
726: RAISE FND_API.G_EXC_ERROR;
727: END IF;
728: END IF;
729: -- This fix is for executing api in sqlplus mode
730: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
731: l_login_user_id := -1;
732: l_user_id := -1;
733: l_resource_id := -1;
734: END IF;
732: l_user_id := -1;
733: l_resource_id := -1;
734: END IF;
735: -- Initialize API return status to sucess
736: x_return_status := FND_API.G_RET_STS_SUCCESS;
737:
738: --
739: IF p_mychannel_id = FND_API.G_MISS_NUM OR
740: p_mychannel_id IS NULL
735: -- Initialize API return status to sucess
736: x_return_status := FND_API.G_RET_STS_SUCCESS;
737:
738: --
739: IF p_mychannel_id = FND_API.G_MISS_NUM OR
740: p_mychannel_id IS NULL
741: THEN
742: -- Validate the subscription
743: Validate_Subscription (
750: x_mychannel_id => l_mychannel_id,
751: x_error_msg => l_Error_Msg,
752: x_error_token => l_Error_Token
753: );
754: IF l_valid_flag = FND_API.G_FALSE THEN
755: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
756: THEN
757: FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
758: FND_MESSAGE.Set_Token('TKN',l_Error_Token);
757: FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
758: FND_MESSAGE.Set_Token('TKN',l_Error_Token);
759: FND_MSG_PUB.Add;
760: END IF;
761: RAISE FND_API.G_EXC_ERROR;
762: END IF;
763: -- set to local variables
764: l_myuser_id := p_user_or_group_id;
765: l_myuser_type := p_user_or_group_type;
787: FND_MESSAGE.Set_Token('ROW', l_full_name);
788: FND_MSG_PUB.Add;
789: END IF;
790: --Standard check of commit
791: IF FND_API.To_Boolean ( p_commit ) THEN
792: COMMIT WORK;
793: END IF;
794: -- Debug Message
795: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
798: FND_MSG_PUB.Add;
799: END IF;
800: --Standard call to get message count and if count=1, get the message
801: FND_MSG_PUB.Count_And_Get (
802: p_encoded => FND_API.G_FALSE,
803: p_count => x_msg_count,
804: p_data => x_msg_data
805: );
806: EXCEPTION
803: p_count => x_msg_count,
804: p_data => x_msg_data
805: );
806: EXCEPTION
807: WHEN FND_API.G_EXC_ERROR THEN
808: ROLLBACK TO Remove_Subscription_PVT;
809: x_return_status := FND_API.G_RET_STS_ERROR;
810: -- Standard call to get message count and if count=1, get the message
811: FND_MSG_PUB.Count_And_Get (
805: );
806: EXCEPTION
807: WHEN FND_API.G_EXC_ERROR THEN
808: ROLLBACK TO Remove_Subscription_PVT;
809: x_return_status := FND_API.G_RET_STS_ERROR;
810: -- Standard call to get message count and if count=1, get the message
811: FND_MSG_PUB.Count_And_Get (
812: p_encoded => FND_API.G_FALSE,
813: p_count => x_msg_count,
808: ROLLBACK TO Remove_Subscription_PVT;
809: x_return_status := FND_API.G_RET_STS_ERROR;
810: -- Standard call to get message count and if count=1, get the message
811: FND_MSG_PUB.Count_And_Get (
812: p_encoded => FND_API.G_FALSE,
813: p_count => x_msg_count,
814: p_data => x_msg_data
815: );
816: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
812: p_encoded => FND_API.G_FALSE,
813: p_count => x_msg_count,
814: p_data => x_msg_data
815: );
816: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
817: ROLLBACK TO Remove_Subscription_PVT;
818: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
819: -- Standard call to get message count and if count=1, get the message
820: FND_MSG_PUB.Count_And_Get (
814: p_data => x_msg_data
815: );
816: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
817: ROLLBACK TO Remove_Subscription_PVT;
818: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
819: -- Standard call to get message count and if count=1, get the message
820: FND_MSG_PUB.Count_And_Get (
821: p_encoded => FND_API.G_FALSE,
822: p_count => x_msg_count,
817: ROLLBACK TO Remove_Subscription_PVT;
818: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
819: -- Standard call to get message count and if count=1, get the message
820: FND_MSG_PUB.Count_And_Get (
821: p_encoded => FND_API.G_FALSE,
822: p_count => x_msg_count,
823: p_data => x_msg_data
824: );
825: WHEN OTHERS THEN
823: p_data => x_msg_data
824: );
825: WHEN OTHERS THEN
826: ROLLBACK TO Remove_Subscription_PVT;
827: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
828: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
829: THEN
830: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
831: END IF;
830: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
831: END IF;
832: -- Standard call to get message count and if count=1, get the message
833: FND_MSG_PUB.Count_And_Get (
834: p_encoded => FND_API.G_FALSE,
835: p_count => x_msg_count,
836: p_data => x_msg_data
837: );
838: --
845: -- Function : Update the mychannel given p_mychannel_id.
846: -- Parameters :
847: -- IN p_api_version IN NUMBER Required
848: -- p_init_msg_list IN VARCHAR2 Optional
849: -- Default = FND_API.G_FALSE
850: -- p_commit IN VARCHAR2 Optional
851: -- Default = FND_API.G_FALSE
852: -- p_validation_level IN NUMBER Optional
853: -- Default = FND_API.G_VALID_LEVEL_FULL
847: -- IN p_api_version IN NUMBER Required
848: -- p_init_msg_list IN VARCHAR2 Optional
849: -- Default = FND_API.G_FALSE
850: -- p_commit IN VARCHAR2 Optional
851: -- Default = FND_API.G_FALSE
852: -- p_validation_level IN NUMBER Optional
853: -- Default = FND_API.G_VALID_LEVEL_FULL
854: -- p_check_login_user IN VARCHAR2 Optional
855: -- Default = FND_API.G_TRUE
849: -- Default = FND_API.G_FALSE
850: -- p_commit IN VARCHAR2 Optional
851: -- Default = FND_API.G_FALSE
852: -- p_validation_level IN NUMBER Optional
853: -- Default = FND_API.G_VALID_LEVEL_FULL
854: -- p_check_login_user IN VARCHAR2 Optional
855: -- Default = FND_API.G_TRUE
856: -- p_mychannel_obj IN AMV_MY_CHANNEL_OBJ_TYPE Required
857: -- OUT : x_return_status OUT VARCHAR2
851: -- Default = FND_API.G_FALSE
852: -- p_validation_level IN NUMBER Optional
853: -- Default = FND_API.G_VALID_LEVEL_FULL
854: -- p_check_login_user IN VARCHAR2 Optional
855: -- Default = FND_API.G_TRUE
856: -- p_mychannel_obj IN AMV_MY_CHANNEL_OBJ_TYPE Required
857: -- OUT : x_return_status OUT VARCHAR2
858: -- x_msg_count OUT NUMBER
859: -- x_msg_data OUT VARCHAR2
865: -- End of comments
866: --
867: PROCEDURE Update_MyChannel
868: ( p_api_version IN NUMBER,
869: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
870: p_commit IN VARCHAR2 := FND_API.G_FALSE,
871: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
872: x_return_status OUT NOCOPY VARCHAR2,
873: x_msg_count OUT NOCOPY NUMBER,
866: --
867: PROCEDURE Update_MyChannel
868: ( p_api_version IN NUMBER,
869: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
870: p_commit IN VARCHAR2 := FND_API.G_FALSE,
871: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
872: x_return_status OUT NOCOPY VARCHAR2,
873: x_msg_count OUT NOCOPY NUMBER,
874: x_msg_data OUT NOCOPY VARCHAR2,
867: PROCEDURE Update_MyChannel
868: ( p_api_version IN NUMBER,
869: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
870: p_commit IN VARCHAR2 := FND_API.G_FALSE,
871: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
872: x_return_status OUT NOCOPY VARCHAR2,
873: x_msg_count OUT NOCOPY NUMBER,
874: x_msg_data OUT NOCOPY VARCHAR2,
875: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
871: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
872: x_return_status OUT NOCOPY VARCHAR2,
873: x_msg_count OUT NOCOPY NUMBER,
874: x_msg_data OUT NOCOPY VARCHAR2,
875: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
876: p_mychannel_obj IN AMV_MY_CHANNEL_OBJ_TYPE
877: )
878: IS
879: l_api_name CONSTANT VARCHAR2(30) := 'Update_MyChannel';
905: BEGIN
906: -- Standard begin of API savepoint
907: SAVEPOINT Update_MyChannel_PVT;
908: -- Standard call to check for call compatibility.
909: IF NOT FND_API.Compatible_API_Call (
910: l_api_version,
911: p_api_version,
912: l_api_name,
913: G_PKG_NAME)
911: p_api_version,
912: l_api_name,
913: G_PKG_NAME)
914: THEN
915: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
916: END IF;
917: -- Debug Message
918: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
919: FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
920: FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
921: FND_MSG_PUB.Add;
922: END IF;
923: --Initialize message list if p_init_msg_list is TRUE.
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(
931: x_login_id => l_login_user_id,
932: x_user_status => l_login_user_status
933: );
934: -- check login user
935: IF (p_check_login_user = FND_API.G_TRUE) THEN
936: -- Check if user is login and has the required privilege.
937: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
938: -- User is not login.
939: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
933: );
934: -- check login user
935: IF (p_check_login_user = FND_API.G_TRUE) THEN
936: -- Check if user is login and has the required privilege.
937: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
938: -- User is not login.
939: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
940: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
941: FND_MSG_PUB.Add;
939: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
940: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
941: FND_MSG_PUB.Add;
942: END IF;
943: RAISE FND_API.G_EXC_ERROR;
944: END IF;
945: END IF;
946: -- This fix is for executing api in sqlplus mode
947: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
943: RAISE FND_API.G_EXC_ERROR;
944: END IF;
945: END IF;
946: -- This fix is for executing api in sqlplus mode
947: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
948: l_login_user_id := -1;
949: l_user_id := -1;
950: l_resource_id := -1;
951: END IF;
949: l_user_id := -1;
950: l_resource_id := -1;
951: END IF;
952: -- Initialize API return status to sucess
953: x_return_status := FND_API.G_RET_STS_SUCCESS;
954:
955: --
956: IF p_mychannel_obj.my_channel_id = FND_API.G_MISS_NUM OR
957: p_mychannel_obj.my_channel_id IS NULL
952: -- Initialize API return status to sucess
953: x_return_status := FND_API.G_RET_STS_SUCCESS;
954:
955: --
956: IF p_mychannel_obj.my_channel_id = FND_API.G_MISS_NUM OR
957: p_mychannel_obj.my_channel_id IS NULL
958: THEN
959: -- Validate the subscription
960: Validate_Subscription (
967: x_mychannel_id => l_mychannel_id,
968: x_error_msg => l_Error_Msg,
969: x_error_token => l_Error_Token
970: );
971: IF l_valid_flag = FND_API.G_TRUE THEN
972: IF l_mychannel_id = FND_API.G_MISS_NUM THEN
973: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
974: THEN
975: FND_MESSAGE.Set_Name('AMV', 'AMV_SUBSCRIPTION_INVALID');
968: x_error_msg => l_Error_Msg,
969: x_error_token => l_Error_Token
970: );
971: IF l_valid_flag = FND_API.G_TRUE THEN
972: IF l_mychannel_id = FND_API.G_MISS_NUM THEN
973: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
974: THEN
975: FND_MESSAGE.Set_Name('AMV', 'AMV_SUBSCRIPTION_INVALID');
976: FND_MESSAGE.Set_Token('TKN',
976: FND_MESSAGE.Set_Token('TKN',
977: p_mychannel_obj.subscribing_to_id);
978: FND_MSG_PUB.Add;
979: END IF;
980: RAISE FND_API.G_EXC_ERROR;
981: END IF;
982: ELSE
983: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
984: THEN
985: FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
986: FND_MESSAGE.Set_Token('TKN',l_Error_Token);
987: FND_MSG_PUB.Add;
988: END IF;
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)
1003: l_notify_flag,
1004: l_notif_interval_type;
1005: CLOSE MyChannel;
1006:
1007: IF p_mychannel_obj.order_number <> FND_API.G_MISS_NUM THEN
1008: l_order_number := p_mychannel_obj.order_number;
1009: END IF;
1010:
1011: IF p_mychannel_obj.status <> FND_API.G_MISS_CHAR OR
1007: IF p_mychannel_obj.order_number <> FND_API.G_MISS_NUM THEN
1008: l_order_number := p_mychannel_obj.order_number;
1009: END IF;
1010:
1011: IF p_mychannel_obj.status <> FND_API.G_MISS_CHAR OR
1012: p_mychannel_obj.status IS NOT NULL
1013: THEN
1014: l_status := p_mychannel_obj.status;
1015: END IF;
1013: THEN
1014: l_status := p_mychannel_obj.status;
1015: END IF;
1016:
1017: IF p_mychannel_obj.notify_flag <> FND_API.G_MISS_CHAR OR
1018: p_mychannel_obj.notify_flag IS NOT NULL
1019: THEN
1020: l_notify_flag := p_mychannel_obj.notify_flag;
1021: END IF;
1019: THEN
1020: l_notify_flag := p_mychannel_obj.notify_flag;
1021: END IF;
1022:
1023: IF p_mychannel_obj.notification_interval_type = FND_API.G_MISS_CHAR THEN
1024: l_notif_interval_type := p_mychannel_obj.notification_interval_type;
1025: END IF;
1026:
1027: UPDATE amv_u_my_channels
1039: FND_MESSAGE.Set_Token('ROW',l_full_name) ;
1040: FND_MSG_PUB.Add;
1041: END IF;
1042: --Standard check of commit
1043: IF FND_API.To_Boolean ( p_commit ) THEN
1044: COMMIT WORK;
1045: END IF;
1046: -- Debug Message
1047: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1050: FND_MSG_PUB.Add;
1051: END IF;
1052: --Standard call to get message count and if count=1, get the message
1053: FND_MSG_PUB.Count_And_Get (
1054: p_encoded => FND_API.G_FALSE,
1055: p_count => x_msg_count,
1056: p_data => x_msg_data
1057: );
1058: EXCEPTION
1055: p_count => x_msg_count,
1056: p_data => x_msg_data
1057: );
1058: EXCEPTION
1059: WHEN FND_API.G_EXC_ERROR THEN
1060: ROLLBACK TO Update_MyChannel_PVT;
1061: x_return_status := FND_API.G_RET_STS_ERROR;
1062: -- Standard call to get message count and if count=1, get the message
1063: FND_MSG_PUB.Count_And_Get (
1057: );
1058: EXCEPTION
1059: WHEN FND_API.G_EXC_ERROR THEN
1060: ROLLBACK TO Update_MyChannel_PVT;
1061: x_return_status := FND_API.G_RET_STS_ERROR;
1062: -- Standard call to get message count and if count=1, get the message
1063: FND_MSG_PUB.Count_And_Get (
1064: p_encoded => FND_API.G_FALSE,
1065: p_count => x_msg_count,
1060: ROLLBACK TO Update_MyChannel_PVT;
1061: x_return_status := FND_API.G_RET_STS_ERROR;
1062: -- Standard call to get message count and if count=1, get the message
1063: FND_MSG_PUB.Count_And_Get (
1064: p_encoded => FND_API.G_FALSE,
1065: p_count => x_msg_count,
1066: p_data => x_msg_data
1067: );
1068: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1064: p_encoded => FND_API.G_FALSE,
1065: p_count => x_msg_count,
1066: p_data => x_msg_data
1067: );
1068: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1069: ROLLBACK TO Update_MyChannel_PVT;
1070: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1071: -- Standard call to get message count and if count=1, get the message
1072: FND_MSG_PUB.Count_And_Get (
1066: p_data => x_msg_data
1067: );
1068: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1069: ROLLBACK TO Update_MyChannel_PVT;
1070: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1071: -- Standard call to get message count and if count=1, get the message
1072: FND_MSG_PUB.Count_And_Get (
1073: p_encoded => FND_API.G_FALSE,
1074: p_count => x_msg_count,
1069: ROLLBACK TO Update_MyChannel_PVT;
1070: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1071: -- Standard call to get message count and if count=1, get the message
1072: FND_MSG_PUB.Count_And_Get (
1073: p_encoded => FND_API.G_FALSE,
1074: p_count => x_msg_count,
1075: p_data => x_msg_data
1076: );
1077: WHEN OTHERS THEN
1075: p_data => x_msg_data
1076: );
1077: WHEN OTHERS THEN
1078: ROLLBACK TO Update_MyChannel_PVT;
1079: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1080: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1081: THEN
1082: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1083: END IF;
1082: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1083: END IF;
1084: -- Standard call to get message count and if count=1, get the message
1085: FND_MSG_PUB.Count_And_Get (
1086: p_encoded => FND_API.G_FALSE,
1087: p_count => x_msg_count,
1088: p_data => x_msg_data
1089: );
1090: --
1097: -- Function : Get all the channels for a given user (p_user_id)
1098: -- Parameters :
1099: -- IN p_api_version IN NUMBER Required
1100: -- p_init_msg_list IN VARCHAR2 Optional
1101: -- Default = FND_API.G_FALSE
1102: -- p_validation_level IN NUMBER Optional
1103: -- Default = FND_API.G_VALID_LEVEL_FULL
1104: -- p_check_login_user IN VARCHAR2 Optional
1105: -- Default = FND_API.G_TRUE
1099: -- IN p_api_version IN NUMBER Required
1100: -- p_init_msg_list IN VARCHAR2 Optional
1101: -- Default = FND_API.G_FALSE
1102: -- p_validation_level IN NUMBER Optional
1103: -- Default = FND_API.G_VALID_LEVEL_FULL
1104: -- p_check_login_user IN VARCHAR2 Optional
1105: -- Default = FND_API.G_TRUE
1106: -- p_user_id IN NUMBER Required
1107: -- the given user
1101: -- Default = FND_API.G_FALSE
1102: -- p_validation_level IN NUMBER Optional
1103: -- Default = FND_API.G_VALID_LEVEL_FULL
1104: -- p_check_login_user IN VARCHAR2 Optional
1105: -- Default = FND_API.G_TRUE
1106: -- p_user_id IN NUMBER Required
1107: -- the given user
1108: -- OUT : x_return_status OUT VARCHAR2
1109: -- x_msg_count OUT NUMBER
1117: -- End of comments
1118: --
1119: PROCEDURE Get_MyChannels
1120: ( p_api_version IN NUMBER,
1121: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1122: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1123: x_return_status OUT NOCOPY VARCHAR2,
1124: x_msg_count OUT NOCOPY NUMBER,
1125: x_msg_data OUT NOCOPY VARCHAR2,
1118: --
1119: PROCEDURE Get_MyChannels
1120: ( p_api_version IN NUMBER,
1121: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1122: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1123: x_return_status OUT NOCOPY VARCHAR2,
1124: x_msg_count OUT NOCOPY NUMBER,
1125: x_msg_data OUT NOCOPY VARCHAR2,
1126: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1122: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1123: x_return_status OUT NOCOPY VARCHAR2,
1124: x_msg_count OUT NOCOPY NUMBER,
1125: x_msg_data OUT NOCOPY VARCHAR2,
1126: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1127: p_user_id IN NUMBER,
1128: x_mychannel_array OUT NOCOPY AMV_MY_CHANNEL_VARRAY_TYPE
1129: )
1130: IS
1199: BEGIN
1200: -- Standard begin of API savepoint
1201: SAVEPOINT Get_MyChannels_PVT;
1202: -- Standard call to check for call compatibility.
1203: IF NOT FND_API.Compatible_API_Call (
1204: l_api_version,
1205: p_api_version,
1206: l_api_name,
1207: G_PKG_NAME)
1205: p_api_version,
1206: l_api_name,
1207: G_PKG_NAME)
1208: THEN
1209: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1210: END IF;
1211: -- Debug Message
1212: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1213: FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1214: FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1215: FND_MSG_PUB.Add;
1216: END IF;
1217: --Initialize message list if p_init_msg_list is TRUE.
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(
1225: x_login_id => l_login_user_id,
1226: x_user_status => l_login_user_status
1227: );
1228: -- check login user
1229: IF (p_check_login_user = FND_API.G_TRUE) THEN
1230: -- Check if user is login and has the required privilege.
1231: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1232: -- User is not login.
1233: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1227: );
1228: -- check login user
1229: IF (p_check_login_user = FND_API.G_TRUE) THEN
1230: -- Check if user is login and has the required privilege.
1231: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1232: -- User is not login.
1233: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1234: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1235: FND_MSG_PUB.Add;
1233: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1234: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1235: FND_MSG_PUB.Add;
1236: END IF;
1237: RAISE FND_API.G_EXC_ERROR;
1238: END IF;
1239: END IF;
1240: -- This fix is for executing api in sqlplus mode
1241: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1237: RAISE FND_API.G_EXC_ERROR;
1238: END IF;
1239: END IF;
1240: -- This fix is for executing api in sqlplus mode
1241: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1242: l_login_user_id := -1;
1243: l_user_id := -1;
1244: l_resource_id := -1;
1245: END IF;
1243: l_user_id := -1;
1244: l_resource_id := -1;
1245: END IF;
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();
1326: FND_MESSAGE.Set_Name('AMV', 'AMV_RESOURCE_ID_INVALID');
1327: FND_MESSAGE.Set_Token('TKN',p_user_id);
1328: FND_MSG_PUB.Add;
1329: END IF;
1330: RAISE FND_API.G_EXC_ERROR;
1331: END IF;
1332: --
1333:
1334: -- Debug Message
1338: FND_MSG_PUB.Add;
1339: END IF;
1340: --Standard call to get message count and if count=1, get the message
1341: FND_MSG_PUB.Count_And_Get (
1342: p_encoded => FND_API.G_FALSE,
1343: p_count => x_msg_count,
1344: p_data => x_msg_data
1345: );
1346: EXCEPTION
1343: p_count => x_msg_count,
1344: p_data => x_msg_data
1345: );
1346: EXCEPTION
1347: WHEN FND_API.G_EXC_ERROR THEN
1348: ROLLBACK TO Get_MyChannels_PVT;
1349: x_return_status := FND_API.G_RET_STS_ERROR;
1350: -- Standard call to get message count and if count=1, get the message
1351: FND_MSG_PUB.Count_And_Get (
1345: );
1346: EXCEPTION
1347: WHEN FND_API.G_EXC_ERROR THEN
1348: ROLLBACK TO Get_MyChannels_PVT;
1349: x_return_status := FND_API.G_RET_STS_ERROR;
1350: -- Standard call to get message count and if count=1, get the message
1351: FND_MSG_PUB.Count_And_Get (
1352: p_encoded => FND_API.G_FALSE,
1353: p_count => x_msg_count,
1348: ROLLBACK TO Get_MyChannels_PVT;
1349: x_return_status := FND_API.G_RET_STS_ERROR;
1350: -- Standard call to get message count and if count=1, get the message
1351: FND_MSG_PUB.Count_And_Get (
1352: p_encoded => FND_API.G_FALSE,
1353: p_count => x_msg_count,
1354: p_data => x_msg_data
1355: );
1356: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1352: p_encoded => FND_API.G_FALSE,
1353: p_count => x_msg_count,
1354: p_data => x_msg_data
1355: );
1356: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1357: ROLLBACK TO Get_MyChannels_PVT;
1358: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1359: -- Standard call to get message count and if count=1, get the message
1360: FND_MSG_PUB.Count_And_Get (
1354: p_data => x_msg_data
1355: );
1356: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1357: ROLLBACK TO Get_MyChannels_PVT;
1358: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1359: -- Standard call to get message count and if count=1, get the message
1360: FND_MSG_PUB.Count_And_Get (
1361: p_encoded => FND_API.G_FALSE,
1362: p_count => x_msg_count,
1357: ROLLBACK TO Get_MyChannels_PVT;
1358: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1359: -- Standard call to get message count and if count=1, get the message
1360: FND_MSG_PUB.Count_And_Get (
1361: p_encoded => FND_API.G_FALSE,
1362: p_count => x_msg_count,
1363: p_data => x_msg_data
1364: );
1365: WHEN OTHERS THEN
1363: p_data => x_msg_data
1364: );
1365: WHEN OTHERS THEN
1366: ROLLBACK TO Get_MyChannels_PVT;
1367: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1368: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1369: THEN
1370: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1371: END IF;
1370: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1371: END IF;
1372: -- Standard call to get message count and if count=1, get the message
1373: FND_MSG_PUB.Count_And_Get (
1374: p_encoded => FND_API.G_FALSE,
1375: p_count => x_msg_count,
1376: p_data => x_msg_data
1377: );
1378: --
1385: -- Function : Get all channels in a category which an user has access to
1386: -- Parameters :
1387: -- IN p_api_version IN NUMBER Required
1388: -- p_init_msg_list IN VARCHAR2 Optional
1389: -- Default = FND_API.G_FALSE
1390: -- p_validation_level IN NUMBER Optional
1391: -- Default = FND_API.G_VALID_LEVEL_FULL
1392: -- p_check_login_user IN VARCHAR2 Optional
1393: -- Default = FND_API.G_TRUE
1387: -- IN p_api_version IN NUMBER Required
1388: -- p_init_msg_list IN VARCHAR2 Optional
1389: -- Default = FND_API.G_FALSE
1390: -- p_validation_level IN NUMBER Optional
1391: -- Default = FND_API.G_VALID_LEVEL_FULL
1392: -- p_check_login_user IN VARCHAR2 Optional
1393: -- Default = FND_API.G_TRUE
1394: -- p_user_id IN NUMBER Required
1395: -- the given user
1389: -- Default = FND_API.G_FALSE
1390: -- p_validation_level IN NUMBER Optional
1391: -- Default = FND_API.G_VALID_LEVEL_FULL
1392: -- p_check_login_user IN VARCHAR2 Optional
1393: -- Default = FND_API.G_TRUE
1394: -- p_user_id IN NUMBER Required
1395: -- the given user
1396: -- p_category_id IN NUMBER Required
1397: -- OUT : x_return_status OUT VARCHAR2
1406: -- End of comments
1407: --
1408: PROCEDURE Get_MyChannelsPerCategory
1409: ( p_api_version IN NUMBER,
1410: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1411: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1412: x_return_status OUT NOCOPY VARCHAR2,
1413: x_msg_count OUT NOCOPY NUMBER,
1414: x_msg_data OUT NOCOPY VARCHAR2,
1407: --
1408: PROCEDURE Get_MyChannelsPerCategory
1409: ( p_api_version IN NUMBER,
1410: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1411: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1412: x_return_status OUT NOCOPY VARCHAR2,
1413: x_msg_count OUT NOCOPY NUMBER,
1414: x_msg_data OUT NOCOPY VARCHAR2,
1415: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1411: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1412: x_return_status OUT NOCOPY VARCHAR2,
1413: x_msg_count OUT NOCOPY NUMBER,
1414: x_msg_data OUT NOCOPY VARCHAR2,
1415: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1416: p_user_id IN NUMBER,
1417: p_category_id IN NUMBER,
1418: x_channel_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE
1419: )
1450: BEGIN
1451: -- Standard begin of API savepoint
1452: SAVEPOINT Get_MyChannelsPerCategory_PVT;
1453: -- Standard call to check for call compatibility.
1454: IF NOT FND_API.Compatible_API_Call (
1455: l_api_version,
1456: p_api_version,
1457: l_api_name,
1458: G_PKG_NAME)
1456: p_api_version,
1457: l_api_name,
1458: G_PKG_NAME)
1459: THEN
1460: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1461: END IF;
1462: -- Debug Message
1463: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1464: FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1465: FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1466: FND_MSG_PUB.Add;
1467: END IF;
1468: --Initialize message list if p_init_msg_list is TRUE.
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(
1476: x_login_id => l_login_user_id,
1477: x_user_status => l_login_user_status
1478: );
1479: -- check login user
1480: IF (p_check_login_user = FND_API.G_TRUE) THEN
1481: -- Check if user is login and has the required privilege.
1482: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1483: -- User is not login.
1484: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1478: );
1479: -- check login user
1480: IF (p_check_login_user = FND_API.G_TRUE) THEN
1481: -- Check if user is login and has the required privilege.
1482: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1483: -- User is not login.
1484: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1485: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1486: FND_MSG_PUB.Add;
1484: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1485: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1486: FND_MSG_PUB.Add;
1487: END IF;
1488: RAISE FND_API.G_EXC_ERROR;
1489: END IF;
1490: END IF;
1491: -- This fix is for executing api in sqlplus mode
1492: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1488: RAISE FND_API.G_EXC_ERROR;
1489: END IF;
1490: END IF;
1491: -- This fix is for executing api in sqlplus mode
1492: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1493: l_login_user_id := -1;
1494: l_user_id := -1;
1495: l_resource_id := -1;
1496: END IF;
1494: l_user_id := -1;
1495: l_resource_id := -1;
1496: END IF;
1497: -- Initialize API return status to sucess
1498: x_return_status := FND_API.G_RET_STS_SUCCESS;
1499:
1500: --
1501: OPEN Get_CategoryChannels;
1502: x_channel_array := AMV_NUMBER_VARRAY_TYPE();
1517: FND_MSG_PUB.Add;
1518: END IF;
1519: --Standard call to get message count and if count=1, get the message
1520: FND_MSG_PUB.Count_And_Get (
1521: p_encoded => FND_API.G_FALSE,
1522: p_count => x_msg_count,
1523: p_data => x_msg_data
1524: );
1525: EXCEPTION
1522: p_count => x_msg_count,
1523: p_data => x_msg_data
1524: );
1525: EXCEPTION
1526: WHEN FND_API.G_EXC_ERROR THEN
1527: ROLLBACK TO Get_MyChannelsPerCategory_PVT;
1528: x_return_status := FND_API.G_RET_STS_ERROR;
1529: -- Standard call to get message count and if count=1, get the message
1530: FND_MSG_PUB.Count_And_Get (
1524: );
1525: EXCEPTION
1526: WHEN FND_API.G_EXC_ERROR THEN
1527: ROLLBACK TO Get_MyChannelsPerCategory_PVT;
1528: x_return_status := FND_API.G_RET_STS_ERROR;
1529: -- Standard call to get message count and if count=1, get the message
1530: FND_MSG_PUB.Count_And_Get (
1531: p_encoded => FND_API.G_FALSE,
1532: p_count => x_msg_count,
1527: ROLLBACK TO Get_MyChannelsPerCategory_PVT;
1528: x_return_status := FND_API.G_RET_STS_ERROR;
1529: -- Standard call to get message count and if count=1, get the message
1530: FND_MSG_PUB.Count_And_Get (
1531: p_encoded => FND_API.G_FALSE,
1532: p_count => x_msg_count,
1533: p_data => x_msg_data
1534: );
1535: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1531: p_encoded => FND_API.G_FALSE,
1532: p_count => x_msg_count,
1533: p_data => x_msg_data
1534: );
1535: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1536: ROLLBACK TO Get_MyChannelsPerCategory_PVT;
1537: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1538: -- Standard call to get message count and if count=1, get the message
1539: FND_MSG_PUB.Count_And_Get (
1533: p_data => x_msg_data
1534: );
1535: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1536: ROLLBACK TO Get_MyChannelsPerCategory_PVT;
1537: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1538: -- Standard call to get message count and if count=1, get the message
1539: FND_MSG_PUB.Count_And_Get (
1540: p_encoded => FND_API.G_FALSE,
1541: p_count => x_msg_count,
1536: ROLLBACK TO Get_MyChannelsPerCategory_PVT;
1537: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1538: -- Standard call to get message count and if count=1, get the message
1539: FND_MSG_PUB.Count_And_Get (
1540: p_encoded => FND_API.G_FALSE,
1541: p_count => x_msg_count,
1542: p_data => x_msg_data
1543: );
1544: WHEN OTHERS THEN
1542: p_data => x_msg_data
1543: );
1544: WHEN OTHERS THEN
1545: ROLLBACK TO Get_MyChannelsPerCategory_PVT;
1546: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1547: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1548: THEN
1549: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1550: END IF;
1549: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1550: END IF;
1551: -- Standard call to get message count and if count=1, get the message
1552: FND_MSG_PUB.Count_And_Get (
1553: p_encoded => FND_API.G_FALSE,
1554: p_count => x_msg_count,
1555: p_data => x_msg_data
1556: );
1557: --
1565: -- Function : Get all channels in a category which an user has access to
1566: -- Parameters :
1567: -- IN p_api_version IN NUMBER Required
1568: -- p_init_msg_list IN VARCHAR2 Optional
1569: -- Default = FND_API.G_FALSE
1570: -- p_validation_level IN NUMBER Optional
1571: -- Default = FND_API.G_VALID_LEVEL_FULL
1572: -- p_check_login_user IN VARCHAR2 Optional
1573: -- Default = FND_API.G_TRUE
1567: -- IN p_api_version IN NUMBER Required
1568: -- p_init_msg_list IN VARCHAR2 Optional
1569: -- Default = FND_API.G_FALSE
1570: -- p_validation_level IN NUMBER Optional
1571: -- Default = FND_API.G_VALID_LEVEL_FULL
1572: -- p_check_login_user IN VARCHAR2 Optional
1573: -- Default = FND_API.G_TRUE
1574: -- p_resource_id IN NUMBER Optional
1575: -- Default = FND_API.G_MISS_NUM
1569: -- Default = FND_API.G_FALSE
1570: -- p_validation_level IN NUMBER Optional
1571: -- Default = FND_API.G_VALID_LEVEL_FULL
1572: -- p_check_login_user IN VARCHAR2 Optional
1573: -- Default = FND_API.G_TRUE
1574: -- p_resource_id IN NUMBER Optional
1575: -- Default = FND_API.G_MISS_NUM
1576: -- resource manager user id
1577: -- p_user_id IN NUMBER Optional
1571: -- Default = FND_API.G_VALID_LEVEL_FULL
1572: -- p_check_login_user IN VARCHAR2 Optional
1573: -- Default = FND_API.G_TRUE
1574: -- p_resource_id IN NUMBER Optional
1575: -- Default = FND_API.G_MISS_NUM
1576: -- resource manager user id
1577: -- p_user_id IN NUMBER Optional
1578: -- Default = FND_API.G_MISS_NUM
1579: -- fnd user id
1574: -- p_resource_id IN NUMBER Optional
1575: -- Default = FND_API.G_MISS_NUM
1576: -- resource manager user id
1577: -- p_user_id IN NUMBER Optional
1578: -- Default = FND_API.G_MISS_NUM
1579: -- fnd user id
1580: -- p_user_name IN VARCHAR2 Optional
1581: -- Default = FND_API.G_MISS_CHAR
1582: -- fnd user name
1577: -- p_user_id IN NUMBER Optional
1578: -- Default = FND_API.G_MISS_NUM
1579: -- fnd user id
1580: -- p_user_name IN VARCHAR2 Optional
1581: -- Default = FND_API.G_MISS_CHAR
1582: -- fnd user name
1583: -- p_notification_type IN VARCHAR2 Optional
1584: -- OUT : x_return_status OUT VARCHAR2
1585: -- x_msg_count OUT NUMBER
1593: -- End of comments
1594: --
1595: PROCEDURE Get_MyNotifications
1596: ( p_api_version IN NUMBER,
1597: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1598: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1599: x_return_status OUT NOCOPY VARCHAR2,
1600: x_msg_count OUT NOCOPY NUMBER,
1601: x_msg_data OUT NOCOPY VARCHAR2,
1594: --
1595: PROCEDURE Get_MyNotifications
1596: ( p_api_version IN NUMBER,
1597: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1598: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1599: x_return_status OUT NOCOPY VARCHAR2,
1600: x_msg_count OUT NOCOPY NUMBER,
1601: x_msg_data OUT NOCOPY VARCHAR2,
1602: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1598: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1599: x_return_status OUT NOCOPY VARCHAR2,
1600: x_msg_count OUT NOCOPY NUMBER,
1601: x_msg_data OUT NOCOPY VARCHAR2,
1602: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1603: p_resource_id IN NUMBER := FND_API.G_MISS_NUM,
1604: p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1605: p_user_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1606: p_notification_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
1599: x_return_status OUT NOCOPY VARCHAR2,
1600: x_msg_count OUT NOCOPY NUMBER,
1601: x_msg_data OUT NOCOPY VARCHAR2,
1602: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1603: p_resource_id IN NUMBER := FND_API.G_MISS_NUM,
1604: p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1605: p_user_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1606: p_notification_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
1607: x_notification_url OUT NOCOPY VARCHAR2,
1600: x_msg_count OUT NOCOPY NUMBER,
1601: x_msg_data OUT NOCOPY VARCHAR2,
1602: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1603: p_resource_id IN NUMBER := FND_API.G_MISS_NUM,
1604: p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1605: p_user_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1606: p_notification_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
1607: x_notification_url OUT NOCOPY VARCHAR2,
1608: x_notifications_array OUT NOCOPY AMV_WF_NOTIF_VARRAY_TYPE
1601: x_msg_data OUT NOCOPY VARCHAR2,
1602: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1603: p_resource_id IN NUMBER := FND_API.G_MISS_NUM,
1604: p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1605: p_user_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1606: p_notification_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
1607: x_notification_url OUT NOCOPY VARCHAR2,
1608: x_notifications_array OUT NOCOPY AMV_WF_NOTIF_VARRAY_TYPE
1609: )
1602: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1603: p_resource_id IN NUMBER := FND_API.G_MISS_NUM,
1604: p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1605: p_user_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1606: p_notification_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
1607: x_notification_url OUT NOCOPY VARCHAR2,
1608: x_notifications_array OUT NOCOPY AMV_WF_NOTIF_VARRAY_TYPE
1609: )
1610: IS
1701: BEGIN
1702: -- Standard begin of API savepoint
1703: SAVEPOINT Get_MyNotifications;
1704: -- Standard call to check for call compatibility.
1705: IF NOT FND_API.Compatible_API_Call (
1706: l_api_version,
1707: p_api_version,
1708: l_api_name,
1709: G_PKG_NAME)
1707: p_api_version,
1708: l_api_name,
1709: G_PKG_NAME)
1710: THEN
1711: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1712: END IF;
1713: -- Debug Message
1714: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1715: FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1716: FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1717: FND_MSG_PUB.Add;
1718: END IF;
1719: --Initialize message list if p_init_msg_list is TRUE.
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(
1727: x_login_id => l_login_user_id,
1728: x_user_status => l_login_user_status
1729: );
1730: -- check login user
1731: IF (p_check_login_user = FND_API.G_TRUE) THEN
1732: -- Check if user is login and has the required privilege.
1733: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1734: -- User is not login.
1735: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1729: );
1730: -- check login user
1731: IF (p_check_login_user = FND_API.G_TRUE) THEN
1732: -- Check if user is login and has the required privilege.
1733: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1734: -- User is not login.
1735: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1736: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1737: FND_MSG_PUB.Add;
1735: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1736: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1737: FND_MSG_PUB.Add;
1738: END IF;
1739: RAISE FND_API.G_EXC_ERROR;
1740: END IF;
1741: END IF;
1742: -- This fix is for executing api in sqlplus mode
1743: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1739: RAISE FND_API.G_EXC_ERROR;
1740: END IF;
1741: END IF;
1742: -- This fix is for executing api in sqlplus mode
1743: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1744: l_login_user_id := -1;
1745: l_user_id := -1;
1746: l_resource_id := -1;
1747: END IF;
1745: l_user_id := -1;
1746: l_resource_id := -1;
1747: END IF;
1748: -- Initialize API return status to sucess
1749: x_return_status := FND_API.G_RET_STS_SUCCESS;
1750:
1751: x_notification_url := wf_core.translate('WF_WEB_AGENT');
1752: --
1753: IF p_user_id = FND_API.G_MISS_NUM OR
1749: x_return_status := FND_API.G_RET_STS_SUCCESS;
1750:
1751: x_notification_url := wf_core.translate('WF_WEB_AGENT');
1752: --
1753: IF p_user_id = FND_API.G_MISS_NUM OR
1754: p_user_id is null
1755: THEN
1756: IF p_resource_id = FND_API.G_MISS_NUM OR
1757: p_resource_id is null
1752: --
1753: IF p_user_id = FND_API.G_MISS_NUM OR
1754: p_user_id is null
1755: THEN
1756: IF p_resource_id = FND_API.G_MISS_NUM OR
1757: p_resource_id is null
1758: THEN
1759: IF p_user_name is not null THEN
1760: l_recipient_role := p_user_name;
1763: THEN
1764: FND_MESSAGE.Set_Name('AMV', 'AMV_NOTIF_NO_VALS');
1765: FND_MSG_PUB.Add;
1766: END IF;
1767: RAISE FND_API.G_EXC_ERROR;
1768: END IF;
1769: ELSE
1770: OPEN Get_ResourceUserName;
1771: FETCH Get_ResourceUserName INTO l_recipient_role;
1777: FND_MESSAGE.Set_Name('AMV', 'AMV_RESOURCE_ID_INVALID');
1778: FND_MESSAGE.Set_Token('TKN',p_resource_id);
1779: FND_MSG_PUB.Add;
1780: END IF;
1781: RAISE FND_API.G_EXC_ERROR;
1782: END IF;
1783: END IF;
1784: ELSE
1785: OPEN Get_FndUserName;
1792: FND_MESSAGE.Set_Name('AMV', 'AMV_INVALID_USER_ID');
1793: FND_MESSAGE.Set_Token('TKN',p_user_id);
1794: FND_MSG_PUB.Add;
1795: END IF;
1796: RAISE FND_API.G_EXC_ERROR;
1797: END IF;
1798: END IF;
1799:
1800: IF p_notification_type = 'NOTIFICATION' THEN
1890: FND_MSG_PUB.Add;
1891: END IF;
1892: --Standard call to get message count and if count=1, get the message
1893: FND_MSG_PUB.Count_And_Get (
1894: p_encoded => FND_API.G_FALSE,
1895: p_count => x_msg_count,
1896: p_data => x_msg_data
1897: );
1898: EXCEPTION
1895: p_count => x_msg_count,
1896: p_data => x_msg_data
1897: );
1898: EXCEPTION
1899: WHEN FND_API.G_EXC_ERROR THEN
1900: ROLLBACK TO Get_MyNotifications;
1901: x_return_status := FND_API.G_RET_STS_ERROR;
1902: -- Standard call to get message count and if count=1, get the message
1903: FND_MSG_PUB.Count_And_Get (
1897: );
1898: EXCEPTION
1899: WHEN FND_API.G_EXC_ERROR THEN
1900: ROLLBACK TO Get_MyNotifications;
1901: x_return_status := FND_API.G_RET_STS_ERROR;
1902: -- Standard call to get message count and if count=1, get the message
1903: FND_MSG_PUB.Count_And_Get (
1904: p_encoded => FND_API.G_FALSE,
1905: p_count => x_msg_count,
1900: ROLLBACK TO Get_MyNotifications;
1901: x_return_status := FND_API.G_RET_STS_ERROR;
1902: -- Standard call to get message count and if count=1, get the message
1903: FND_MSG_PUB.Count_And_Get (
1904: p_encoded => FND_API.G_FALSE,
1905: p_count => x_msg_count,
1906: p_data => x_msg_data
1907: );
1908: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1904: p_encoded => FND_API.G_FALSE,
1905: p_count => x_msg_count,
1906: p_data => x_msg_data
1907: );
1908: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1909: ROLLBACK TO Get_MyNotifications;
1910: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1911: -- Standard call to get message count and if count=1, get the message
1912: FND_MSG_PUB.Count_And_Get (
1906: p_data => x_msg_data
1907: );
1908: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1909: ROLLBACK TO Get_MyNotifications;
1910: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1911: -- Standard call to get message count and if count=1, get the message
1912: FND_MSG_PUB.Count_And_Get (
1913: p_encoded => FND_API.G_FALSE,
1914: p_count => x_msg_count,
1909: ROLLBACK TO Get_MyNotifications;
1910: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1911: -- Standard call to get message count and if count=1, get the message
1912: FND_MSG_PUB.Count_And_Get (
1913: p_encoded => FND_API.G_FALSE,
1914: p_count => x_msg_count,
1915: p_data => x_msg_data
1916: );
1917: WHEN OTHERS THEN
1915: p_data => x_msg_data
1916: );
1917: WHEN OTHERS THEN
1918: ROLLBACK TO Get_MyNotifications;
1919: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1920: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1921: THEN
1922: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1923: END IF;
1922: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1923: END IF;
1924: -- Standard call to get message count and if count=1, get the message
1925: FND_MSG_PUB.Count_And_Get (
1926: p_encoded => FND_API.G_FALSE,
1927: p_count => x_msg_count,
1928: p_data => x_msg_data
1929: );
1930: --
1938: -- Function : Return all items a user can access based channel/cat access
1939: -- Parameters :
1940: -- IN p_api_version IN NUMBER Required
1941: -- p_init_msg_list IN VARCHAR2 Optional
1942: -- Default = FND_API.G_FALSE
1943: -- p_validation_level IN NUMBER Optional
1944: -- Default = FND_API.G_VALID_LEVEL_FULL
1945: -- p_category_id IN NUMBER Required
1946: -- p_include_subcats IN VARCHAR2 Optional
1940: -- IN p_api_version IN NUMBER Required
1941: -- p_init_msg_list IN VARCHAR2 Optional
1942: -- Default = FND_API.G_FALSE
1943: -- p_validation_level IN NUMBER Optional
1944: -- Default = FND_API.G_VALID_LEVEL_FULL
1945: -- p_category_id IN NUMBER Required
1946: -- p_include_subcats IN VARCHAR2 Optional
1947: -- Default = FND_API.G_FALSE
1948: -- OUT : x_return_status OUT VARCHAR2
1943: -- p_validation_level IN NUMBER Optional
1944: -- Default = FND_API.G_VALID_LEVEL_FULL
1945: -- p_category_id IN NUMBER Required
1946: -- p_include_subcats IN VARCHAR2 Optional
1947: -- Default = FND_API.G_FALSE
1948: -- OUT : x_return_status OUT VARCHAR2
1949: -- x_msg_count OUT NUMBER
1950: -- x_msg_data OUT VARCHAR2
1951: -- x_items_array OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
1957: -- End of comments
1958: --
1959: PROCEDURE Get_ItemsPerUser
1960: ( p_api_version IN NUMBER,
1961: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1962: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1963: x_return_status OUT NOCOPY VARCHAR2,
1964: x_msg_count OUT NOCOPY NUMBER,
1965: x_msg_data OUT NOCOPY VARCHAR2,
1958: --
1959: PROCEDURE Get_ItemsPerUser
1960: ( p_api_version IN NUMBER,
1961: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1962: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1963: x_return_status OUT NOCOPY VARCHAR2,
1964: x_msg_count OUT NOCOPY NUMBER,
1965: x_msg_data OUT NOCOPY VARCHAR2,
1966: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1962: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1963: x_return_status OUT NOCOPY VARCHAR2,
1964: x_msg_count OUT NOCOPY NUMBER,
1965: x_msg_data OUT NOCOPY VARCHAR2,
1966: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1967: p_user_id IN NUMBER,
1968: p_request_obj IN AMV_REQUEST_OBJ_TYPE,
1969: x_return_obj OUT NOCOPY AMV_RETURN_OBJ_TYPE,
1970: x_items_array OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
2080: BEGIN
2081: -- Standard begin of API savepoint
2082: SAVEPOINT Get_ItemsPerUser;
2083: -- Standard call to check for call compatibility.
2084: IF NOT FND_API.Compatible_API_Call (
2085: l_api_version,
2086: p_api_version,
2087: l_api_name,
2088: G_PKG_NAME)
2086: p_api_version,
2087: l_api_name,
2088: G_PKG_NAME)
2089: THEN
2090: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2091: END IF;
2092: -- Debug Message
2093: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2094: FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2095: FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2096: FND_MSG_PUB.Add;
2097: END IF;
2098: --Initialize message list if p_init_msg_list is TRUE.
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(
2106: x_login_id => l_login_user_id,
2107: x_user_status => l_login_user_status
2108: );
2109: -- check login user
2110: IF (p_check_login_user = FND_API.G_TRUE) THEN
2111: -- Check if user is login and has the required privilege.
2112: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2113: -- User is not login.
2114: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2108: );
2109: -- check login user
2110: IF (p_check_login_user = FND_API.G_TRUE) THEN
2111: -- Check if user is login and has the required privilege.
2112: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2113: -- User is not login.
2114: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2115: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2116: FND_MSG_PUB.Add;
2114: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2115: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2116: FND_MSG_PUB.Add;
2117: END IF;
2118: RAISE FND_API.G_EXC_ERROR;
2119: END IF;
2120: END IF;
2121: -- This fix is for executing api in sqlplus mode
2122: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2118: RAISE FND_API.G_EXC_ERROR;
2119: END IF;
2120: END IF;
2121: -- This fix is for executing api in sqlplus mode
2122: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2123: l_login_user_id := -1;
2124: l_user_id := -1;
2125: l_resource_id := -1;
2126: END IF;
2124: l_user_id := -1;
2125: l_resource_id := -1;
2126: END IF;
2127: -- Initialize API return status to sucess
2128: x_return_status := FND_API.G_RET_STS_SUCCESS;
2129:
2130: --
2131: x_items_array := AMV_CAT_HIERARCHY_VARRAY_TYPE();
2132:
2184: FND_MSG_PUB.Add;
2185: END IF;
2186: --Standard call to get message count and if count=1, get the message
2187: FND_MSG_PUB.Count_And_Get (
2188: p_encoded => FND_API.G_FALSE,
2189: p_count => x_msg_count,
2190: p_data => x_msg_data
2191: );
2192: EXCEPTION
2189: p_count => x_msg_count,
2190: p_data => x_msg_data
2191: );
2192: EXCEPTION
2193: WHEN FND_API.G_EXC_ERROR THEN
2194: ROLLBACK TO Get_ItemsPerUser;
2195: x_return_status := FND_API.G_RET_STS_ERROR;
2196: -- Standard call to get message count and if count=1, get the message
2197: FND_MSG_PUB.Count_And_Get (
2191: );
2192: EXCEPTION
2193: WHEN FND_API.G_EXC_ERROR THEN
2194: ROLLBACK TO Get_ItemsPerUser;
2195: x_return_status := FND_API.G_RET_STS_ERROR;
2196: -- Standard call to get message count and if count=1, get the message
2197: FND_MSG_PUB.Count_And_Get (
2198: p_encoded => FND_API.G_FALSE,
2199: p_count => x_msg_count,
2194: ROLLBACK TO Get_ItemsPerUser;
2195: x_return_status := FND_API.G_RET_STS_ERROR;
2196: -- Standard call to get message count and if count=1, get the message
2197: FND_MSG_PUB.Count_And_Get (
2198: p_encoded => FND_API.G_FALSE,
2199: p_count => x_msg_count,
2200: p_data => x_msg_data
2201: );
2202: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2198: p_encoded => FND_API.G_FALSE,
2199: p_count => x_msg_count,
2200: p_data => x_msg_data
2201: );
2202: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2203: ROLLBACK TO Get_ItemsPerUser;
2204: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2205: -- Standard call to get message count and if count=1, get the message
2206: FND_MSG_PUB.Count_And_Get (
2200: p_data => x_msg_data
2201: );
2202: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2203: ROLLBACK TO Get_ItemsPerUser;
2204: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2205: -- Standard call to get message count and if count=1, get the message
2206: FND_MSG_PUB.Count_And_Get (
2207: p_encoded => FND_API.G_FALSE,
2208: p_count => x_msg_count,
2203: ROLLBACK TO Get_ItemsPerUser;
2204: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2205: -- Standard call to get message count and if count=1, get the message
2206: FND_MSG_PUB.Count_And_Get (
2207: p_encoded => FND_API.G_FALSE,
2208: p_count => x_msg_count,
2209: p_data => x_msg_data
2210: );
2211: WHEN OTHERS THEN
2209: p_data => x_msg_data
2210: );
2211: WHEN OTHERS THEN
2212: ROLLBACK TO Get_ItemsPerUser;
2213: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2214: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2215: THEN
2216: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2217: END IF;
2216: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2217: END IF;
2218: -- Standard call to get message count and if count=1, get the message
2219: FND_MSG_PUB.Count_And_Get (
2220: p_encoded => FND_API.G_FALSE,
2221: p_count => x_msg_count,
2222: p_data => x_msg_data
2223: );
2224: --
2232: -- Function : Return all items user owns
2233: -- Parameters :
2234: -- IN p_api_version IN NUMBER Required
2235: -- p_init_msg_list IN VARCHAR2 Optional
2236: -- Default = FND_API.G_FALSE
2237: -- p_validation_level IN NUMBER Optional
2238: -- Default = FND_API.G_VALID_LEVEL_FULL
2239: -- p_category_id IN NUMBER Required
2240: -- p_include_subcats IN VARCHAR2 Optional
2234: -- IN p_api_version IN NUMBER Required
2235: -- p_init_msg_list IN VARCHAR2 Optional
2236: -- Default = FND_API.G_FALSE
2237: -- p_validation_level IN NUMBER Optional
2238: -- Default = FND_API.G_VALID_LEVEL_FULL
2239: -- p_category_id IN NUMBER Required
2240: -- p_include_subcats IN VARCHAR2 Optional
2241: -- Default = FND_API.G_FALSE
2242: -- OUT : x_return_status OUT VARCHAR2
2237: -- p_validation_level IN NUMBER Optional
2238: -- Default = FND_API.G_VALID_LEVEL_FULL
2239: -- p_category_id IN NUMBER Required
2240: -- p_include_subcats IN VARCHAR2 Optional
2241: -- Default = FND_API.G_FALSE
2242: -- OUT : x_return_status OUT VARCHAR2
2243: -- x_msg_count OUT NUMBER
2244: -- x_msg_data OUT VARCHAR2
2245: -- x_items_array OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
2251: -- End of comments
2252: --
2253: PROCEDURE Get_UserItems
2254: ( p_api_version IN NUMBER,
2255: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2256: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2257: x_return_status OUT NOCOPY VARCHAR2,
2258: x_msg_count OUT NOCOPY NUMBER,
2259: x_msg_data OUT NOCOPY VARCHAR2,
2252: --
2253: PROCEDURE Get_UserItems
2254: ( p_api_version IN NUMBER,
2255: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2256: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2257: x_return_status OUT NOCOPY VARCHAR2,
2258: x_msg_count OUT NOCOPY NUMBER,
2259: x_msg_data OUT NOCOPY VARCHAR2,
2260: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2256: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2257: x_return_status OUT NOCOPY VARCHAR2,
2258: x_msg_count OUT NOCOPY NUMBER,
2259: x_msg_data OUT NOCOPY VARCHAR2,
2260: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2261: p_application_id IN NUMBER,
2262: p_user_id IN NUMBER,
2263: p_request_obj IN AMV_REQUEST_OBJ_TYPE,
2264: x_return_obj OUT NOCOPY AMV_RETURN_OBJ_TYPE,
2311: BEGIN
2312: -- Standard begin of API savepoint
2313: SAVEPOINT Get_UserItems;
2314: -- Standard call to check for call compatibility.
2315: IF NOT FND_API.Compatible_API_Call (
2316: l_api_version,
2317: p_api_version,
2318: l_api_name,
2319: G_PKG_NAME)
2317: p_api_version,
2318: l_api_name,
2319: G_PKG_NAME)
2320: THEN
2321: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2322: END IF;
2323: -- Debug Message
2324: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2325: FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2326: FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2327: FND_MSG_PUB.Add;
2328: END IF;
2329: --Initialize message list if p_init_msg_list is TRUE.
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(
2337: x_login_id => l_login_user_id,
2338: x_user_status => l_login_user_status
2339: );
2340: -- check login user
2341: IF (p_check_login_user = FND_API.G_TRUE) THEN
2342: -- Check if user is login and has the required privilege.
2343: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2344: -- User is not login.
2345: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2339: );
2340: -- check login user
2341: IF (p_check_login_user = FND_API.G_TRUE) THEN
2342: -- Check if user is login and has the required privilege.
2343: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2344: -- User is not login.
2345: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2346: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2347: FND_MSG_PUB.Add;
2345: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2346: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2347: FND_MSG_PUB.Add;
2348: END IF;
2349: RAISE FND_API.G_EXC_ERROR;
2350: END IF;
2351: END IF;
2352: -- This fix is for executing api in sqlplus mode
2353: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2349: RAISE FND_API.G_EXC_ERROR;
2350: END IF;
2351: END IF;
2352: -- This fix is for executing api in sqlplus mode
2353: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2354: l_login_user_id := -1;
2355: l_user_id := -1;
2356: l_resource_id := -1;
2357: END IF;
2355: l_user_id := -1;
2356: l_resource_id := -1;
2357: END IF;
2358: -- Initialize API return status to sucess
2359: x_return_status := FND_API.G_RET_STS_SUCCESS;
2360:
2361: --
2362: x_items_array := AMV_ITEMDISPLAY_VARRAY_TYPE();
2363:
2402: FND_MSG_PUB.Add;
2403: END IF;
2404: --Standard call to get message count and if count=1, get the message
2405: FND_MSG_PUB.Count_And_Get (
2406: p_encoded => FND_API.G_FALSE,
2407: p_count => x_msg_count,
2408: p_data => x_msg_data
2409: );
2410: EXCEPTION
2407: p_count => x_msg_count,
2408: p_data => x_msg_data
2409: );
2410: EXCEPTION
2411: WHEN FND_API.G_EXC_ERROR THEN
2412: ROLLBACK TO Get_UserItems;
2413: x_return_status := FND_API.G_RET_STS_ERROR;
2414: -- Standard call to get message count and if count=1, get the message
2415: FND_MSG_PUB.Count_And_Get (
2409: );
2410: EXCEPTION
2411: WHEN FND_API.G_EXC_ERROR THEN
2412: ROLLBACK TO Get_UserItems;
2413: x_return_status := FND_API.G_RET_STS_ERROR;
2414: -- Standard call to get message count and if count=1, get the message
2415: FND_MSG_PUB.Count_And_Get (
2416: p_encoded => FND_API.G_FALSE,
2417: p_count => x_msg_count,
2412: ROLLBACK TO Get_UserItems;
2413: x_return_status := FND_API.G_RET_STS_ERROR;
2414: -- Standard call to get message count and if count=1, get the message
2415: FND_MSG_PUB.Count_And_Get (
2416: p_encoded => FND_API.G_FALSE,
2417: p_count => x_msg_count,
2418: p_data => x_msg_data
2419: );
2420: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2416: p_encoded => FND_API.G_FALSE,
2417: p_count => x_msg_count,
2418: p_data => x_msg_data
2419: );
2420: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2421: ROLLBACK TO Get_UserItems;
2422: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2423: -- Standard call to get message count and if count=1, get the message
2424: FND_MSG_PUB.Count_And_Get (
2418: p_data => x_msg_data
2419: );
2420: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2421: ROLLBACK TO Get_UserItems;
2422: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2423: -- Standard call to get message count and if count=1, get the message
2424: FND_MSG_PUB.Count_And_Get (
2425: p_encoded => FND_API.G_FALSE,
2426: p_count => x_msg_count,
2421: ROLLBACK TO Get_UserItems;
2422: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2423: -- Standard call to get message count and if count=1, get the message
2424: FND_MSG_PUB.Count_And_Get (
2425: p_encoded => FND_API.G_FALSE,
2426: p_count => x_msg_count,
2427: p_data => x_msg_data
2428: );
2429: WHEN OTHERS THEN
2427: p_data => x_msg_data
2428: );
2429: WHEN OTHERS THEN
2430: ROLLBACK TO Get_UserItems;
2431: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2432: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2433: THEN
2434: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2435: END IF;
2434: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2435: END IF;
2436: -- Standard call to get message count and if count=1, get the message
2437: FND_MSG_PUB.Count_And_Get (
2438: p_encoded => FND_API.G_FALSE,
2439: p_count => x_msg_count,
2440: p_data => x_msg_data
2441: );
2442: --
2450: -- Function : Return all items published by the user
2451: -- Parameters :
2452: -- IN p_api_version IN NUMBER Required
2453: -- p_init_msg_list IN VARCHAR2 Optional
2454: -- Default = FND_API.G_FALSE
2455: -- p_validation_level IN NUMBER Optional
2456: -- Default = FND_API.G_VALID_LEVEL_FULL
2457: -- p_user_id IN NUMBER Required
2458: -- p_include_subcats IN VARCHAR2 Optional
2452: -- IN p_api_version IN NUMBER Required
2453: -- p_init_msg_list IN VARCHAR2 Optional
2454: -- Default = FND_API.G_FALSE
2455: -- p_validation_level IN NUMBER Optional
2456: -- Default = FND_API.G_VALID_LEVEL_FULL
2457: -- p_user_id IN NUMBER Required
2458: -- p_include_subcats IN VARCHAR2 Optional
2459: -- Default = FND_API.G_FALSE
2460: -- OUT : x_return_status OUT VARCHAR2
2455: -- p_validation_level IN NUMBER Optional
2456: -- Default = FND_API.G_VALID_LEVEL_FULL
2457: -- p_user_id IN NUMBER Required
2458: -- p_include_subcats IN VARCHAR2 Optional
2459: -- Default = FND_API.G_FALSE
2460: -- OUT : x_return_status OUT VARCHAR2
2461: -- x_msg_count OUT NUMBER
2462: -- x_msg_data OUT VARCHAR2
2463: -- x_items_array OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
2469: -- End of comments
2470: --
2471: PROCEDURE Can_UserMaintainChannel
2472: ( p_api_version IN NUMBER,
2473: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2474: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2475: x_return_status OUT NOCOPY VARCHAR2,
2476: x_msg_count OUT NOCOPY NUMBER,
2477: x_msg_data OUT NOCOPY VARCHAR2,
2470: --
2471: PROCEDURE Can_UserMaintainChannel
2472: ( p_api_version IN NUMBER,
2473: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2474: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2475: x_return_status OUT NOCOPY VARCHAR2,
2476: x_msg_count OUT NOCOPY NUMBER,
2477: x_msg_data OUT NOCOPY VARCHAR2,
2478: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2474: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2475: x_return_status OUT NOCOPY VARCHAR2,
2476: x_msg_count OUT NOCOPY NUMBER,
2477: x_msg_data OUT NOCOPY VARCHAR2,
2478: p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2479: p_user_id IN NUMBER,
2480: p_channel_id IN NUMBER,
2481: x_maintain_flag OUT NOCOPY VARCHAR2
2482: )
2515: and nvl(expiration_date, sysdate) >= sysdate;
2516:
2517: BEGIN
2518: -- Standard call to check for call compatibility.
2519: IF NOT FND_API.Compatible_API_Call (
2520: l_api_version,
2521: p_api_version,
2522: l_api_name,
2523: G_PKG_NAME)
2521: p_api_version,
2522: l_api_name,
2523: G_PKG_NAME)
2524: THEN
2525: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2526: END IF;
2527: -- Debug Message
2528: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2529: FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2530: FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2531: FND_MSG_PUB.Add;
2532: END IF;
2533: --Initialize message list if p_init_msg_list is TRUE.
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(
2541: x_login_id => l_login_user_id,
2542: x_user_status => l_login_user_status
2543: );
2544: -- check login user
2545: IF (p_check_login_user = FND_API.G_TRUE) THEN
2546: -- Check if user is login and has the required privilege.
2547: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2548: -- User is not login.
2549: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2543: );
2544: -- check login user
2545: IF (p_check_login_user = FND_API.G_TRUE) THEN
2546: -- Check if user is login and has the required privilege.
2547: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2548: -- User is not login.
2549: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2550: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2551: FND_MSG_PUB.Add;
2549: IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2550: FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2551: FND_MSG_PUB.Add;
2552: END IF;
2553: RAISE FND_API.G_EXC_ERROR;
2554: END IF;
2555: END IF;
2556: -- This fix is for executing api in sqlplus mode
2557: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2553: RAISE FND_API.G_EXC_ERROR;
2554: END IF;
2555: END IF;
2556: -- This fix is for executing api in sqlplus mode
2557: IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2558: l_login_user_id := -1;
2559: l_user_id := -1;
2560: l_resource_id := -1;
2561: END IF;
2559: l_user_id := -1;
2560: l_resource_id := -1;
2561: END IF;
2562: -- Initialize API return status to sucess
2563: x_return_status := FND_API.G_RET_STS_SUCCESS;
2564:
2565: --
2566: OPEN Get_OwnerAppr_csr;
2567: FETCH Get_OwnerAppr_csr INTO l_approver_id, l_owner_id;
2567: FETCH Get_OwnerAppr_csr INTO l_approver_id, l_owner_id;
2568: CLOSE Get_OwnerAppr_csr;
2569:
2570: IF l_approver_id = p_user_id THEN
2571: x_maintain_flag := FND_API.G_TRUE;
2572: ELSIF l_owner_id = p_user_id THEN
2573: x_maintain_flag := FND_API.G_TRUE;
2574: ELSE
2575: OPEN Get_SecAppr_csr;
2569:
2570: IF l_approver_id = p_user_id THEN
2571: x_maintain_flag := FND_API.G_TRUE;
2572: ELSIF l_owner_id = p_user_id THEN
2573: x_maintain_flag := FND_API.G_TRUE;
2574: ELSE
2575: OPEN Get_SecAppr_csr;
2576: FETCH Get_SecAppr_csr INTO x_maintain_flag;
2577: CLOSE Get_SecAppr_csr;
2575: OPEN Get_SecAppr_csr;
2576: FETCH Get_SecAppr_csr INTO x_maintain_flag;
2577: CLOSE Get_SecAppr_csr;
2578:
2579: IF x_maintain_flag <> FND_API.G_TRUE THEN
2580: x_maintain_flag := FND_API.G_FALSE;
2581: END IF;
2582: END IF;
2583: --
2576: FETCH Get_SecAppr_csr INTO x_maintain_flag;
2577: CLOSE Get_SecAppr_csr;
2578:
2579: IF x_maintain_flag <> FND_API.G_TRUE THEN
2580: x_maintain_flag := FND_API.G_FALSE;
2581: END IF;
2582: END IF;
2583: --
2584:
2589: FND_MSG_PUB.Add;
2590: END IF;
2591: --Standard call to get message count and if count=1, get the message
2592: FND_MSG_PUB.Count_And_Get (
2593: p_encoded => FND_API.G_FALSE,
2594: p_count => x_msg_count,
2595: p_data => x_msg_data
2596: );
2597: EXCEPTION
2594: p_count => x_msg_count,
2595: p_data => x_msg_data
2596: );
2597: EXCEPTION
2598: WHEN FND_API.G_EXC_ERROR THEN
2599: x_return_status := FND_API.G_RET_STS_ERROR;
2600: -- Standard call to get message count and if count=1, get the message
2601: FND_MSG_PUB.Count_And_Get (
2602: p_encoded => FND_API.G_FALSE,
2595: p_data => x_msg_data
2596: );
2597: EXCEPTION
2598: WHEN FND_API.G_EXC_ERROR THEN
2599: x_return_status := FND_API.G_RET_STS_ERROR;
2600: -- Standard call to get message count and if count=1, get the message
2601: FND_MSG_PUB.Count_And_Get (
2602: p_encoded => FND_API.G_FALSE,
2603: p_count => x_msg_count,
2598: WHEN FND_API.G_EXC_ERROR THEN
2599: x_return_status := FND_API.G_RET_STS_ERROR;
2600: -- Standard call to get message count and if count=1, get the message
2601: FND_MSG_PUB.Count_And_Get (
2602: p_encoded => FND_API.G_FALSE,
2603: p_count => x_msg_count,
2604: p_data => x_msg_data
2605: );
2606: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2602: p_encoded => FND_API.G_FALSE,
2603: p_count => x_msg_count,
2604: p_data => x_msg_data
2605: );
2606: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2607: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2608: -- Standard call to get message count and if count=1, get the message
2609: FND_MSG_PUB.Count_And_Get (
2610: p_encoded => FND_API.G_FALSE,
2603: p_count => x_msg_count,
2604: p_data => x_msg_data
2605: );
2606: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2607: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2608: -- Standard call to get message count and if count=1, get the message
2609: FND_MSG_PUB.Count_And_Get (
2610: p_encoded => FND_API.G_FALSE,
2611: p_count => x_msg_count,
2606: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2607: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2608: -- Standard call to get message count and if count=1, get the message
2609: FND_MSG_PUB.Count_And_Get (
2610: p_encoded => FND_API.G_FALSE,
2611: p_count => x_msg_count,
2612: p_data => x_msg_data
2613: );
2614: WHEN OTHERS THEN
2611: p_count => x_msg_count,
2612: p_data => x_msg_data
2613: );
2614: WHEN OTHERS THEN
2615: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2616: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2617: THEN
2618: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2619: END IF;
2618: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2619: END IF;
2620: -- Standard call to get message count and if count=1, get the message
2621: FND_MSG_PUB.Count_And_Get (
2622: p_encoded => FND_API.G_FALSE,
2623: p_count => x_msg_count,
2624: p_data => x_msg_data
2625: );
2626: --