[Home] [Help]
PACKAGE BODY: APPS.AMV_ITEM_PUB
Source
1 PACKAGE BODY amv_item_pub AS
2 /* $Header: amvpitmb.pls 115.27 2003/03/13 12:27:02 anraman ship $ */
3 --
4 -- NAME
5 -- AMV_ITEM_PUB
6 --
7 -- HISTORY
8 -- 08/30/1999 PWU CREATED
9 -- 12/03/1999 PWU modify to call jtf amv item api
10 --
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMV_ITEM_PUB';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amvpitmb.pls';
13 --
14 G_USED_BY_ITEM CONSTANT VARCHAR2(30) := 'ITEM';
15 -- Debug mode
16 --g_debug boolean := FALSE;
17 g_debug BOOLEAN := TRUE;
18 --
19 TYPE CursorType IS REF CURSOR;
20 --
21 --------------------------------------------------------------------------------
22 --------------------------------------------------------------------------------
23 PROCEDURE Create_Item
24 (
25 p_api_version IN NUMBER,
26 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
27 p_commit IN VARCHAR2 := FND_API.G_FALSE,
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_count OUT NOCOPY NUMBER,
30 x_msg_data OUT NOCOPY VARCHAR2,
31 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
32 p_channel_id_array IN AMV_NUMBER_VARRAY_TYPE := NULL,
33 p_item_obj IN AMV_ITEM_OBJ_TYPE,
34 p_file_array IN AMV_NUMBER_VARRAY_TYPE,
35 p_persp_array IN AMV_NAMEID_VARRAY_TYPE,
36 p_author_array IN AMV_CHAR_VARRAY_TYPE,
37 p_keyword_array IN AMV_CHAR_VARRAY_TYPE,
38 x_item_id OUT NOCOPY NUMBER
39 ) IS
40 l_api_name CONSTANT VARCHAR2(30) := 'Create_Item';
41 l_api_version CONSTANT NUMBER := 1.0;
42 l_resource_id NUMBER := -1;
43 l_current_user_id NUMBER := -1;
44 l_current_login_id NUMBER := -1;
45 l_current_user_status VARCHAR2(80);
46 --
47 l_admin_flag VARCHAR2(1);
48 l_item_id NUMBER;
49 l_return_status VARCHAR2(1);
50 l_item_rec JTF_AMV_ITEM_PUB.ITEM_REC_TYPE;
51 l_item_obj AMV_ITEM_OBJ_TYPE := p_item_obj;
52 l_persp_id_array AMV_PERSPECTIVE_PVT.AMV_NUMBER_VARRAY_TYPE;
53 --
54 BEGIN
55 -- Standard call to check for call compatibility.
56 SAVEPOINT Create_Item_Pub;
57
58
59 IF NOT FND_API.Compatible_API_Call (
60 l_api_version,
61 p_api_version,
62 l_api_name,
63 G_PKG_NAME) THEN
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65 END IF;
66 --Initialize message list if p_init_msg_list is TRUE.
67 IF FND_API.To_Boolean (p_init_msg_list) THEN
68 FND_MSG_PUB.initialize;
69 END IF;
70 -- Initialize API return status to success
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72 -- Get the current (login) user id.
73 AMV_UTILITY_PVT.Get_UserInfo(
74 x_resource_id => l_resource_id,
75 x_user_id => l_current_user_id,
76 x_login_id => l_current_login_id,
77 x_user_status => l_current_user_status
78 );
79
80
81 IF (p_check_login_user = FND_API.G_TRUE) THEN
82 -- Check if user is login and has the required privilege.
83 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
84 -- User is not login.
85 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
86 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
87 FND_MSG_PUB.ADD;
88 END IF;
89 RAISE FND_API.G_EXC_ERROR;
90 END IF;
91 AMV_USER_PVT.Is_Administrator
92 (
93 p_api_version => 1.0,
94 x_return_status => x_return_status,
95 x_msg_count => x_msg_count,
96 x_msg_data => x_msg_data,
97 p_check_login_user => FND_API.G_FALSE,
98 p_resource_id => l_resource_id,
99 x_result_flag => l_admin_flag
100 );
101 IF (l_admin_flag <> FND_API.G_TRUE) THEN
102 -- User is not an administrator.
103 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
104 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
105 FND_MSG_PUB.ADD;
106 END IF;
107 RAISE FND_API.G_EXC_ERROR;
108 END IF;
109 END IF;
110 --
111 --Inserted the following check to fix bug # 2740293
112 --Because the application id should not be 0 while publishing an item
113 --
114 IF l_item_obj.application_id = 0 THEN
115
116 l_item_obj.application_id := AMV_UTILITY_PVT.G_AMV_APP_ID;
117
118 END IF;
119
120 -- MAKE SURE THE PASSED ITEM OBJECT HAS ALL THE RIGHT INFORMATION.
121 -- Check if the object is really passed.
122 IF (p_item_obj.item_name IS NULL) THEN
123 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
124 FND_MESSAGE.Set_name('AMV','AMV_NEED_ITEM_INFO');
125 FND_MSG_PUB.ADD;
126 END IF;
127 RAISE FND_API.G_EXC_ERROR;
128 END IF;
129 IF (l_item_obj.application_id = AMV_UTILITY_PVT.G_AMV_APP_ID ) THEN
130 -- Check if item type in the item object is null
131 IF (l_item_obj.item_type IS NULL OR
132 l_item_obj.item_type = FND_API.G_MISS_CHAR ) THEN
133 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
134 FND_MESSAGE.Set_name('AMV','AMV_NULL_ITEM_TYPE');
135 FND_MSG_PUB.ADD;
136 END IF;
137 RAISE FND_API.G_EXC_ERROR;
138 END IF;
139 -- Check if owner_id is valid
140 IF (l_item_obj.owner_id IS NULL OR
141 l_item_obj.owner_id = 0 OR
142 l_item_obj.owner_id = FND_API.G_MISS_NUM) THEN
143 l_item_obj.owner_id := l_resource_id;
144 ELSE
145 IF (AMV_UTILITY_PVT.Is_ResourceIdValid(l_item_obj.owner_id)
146 <> TRUE) THEN
147 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
148 FND_MESSAGE.Set_name('AMV','AMV_INVALID_OWNER_USER_ID');
149 FND_MESSAGE.Set_Token('ID', TO_CHAR(l_item_obj.owner_id));
150 FND_MSG_PUB.ADD;
151 END IF;
152 RAISE FND_API.G_EXC_ERROR;
153 END IF;
154 END IF;
155 --
156 -- Check if default_approver_id is valid
157 IF (l_item_obj.default_approver_id IS NULL OR
158 l_item_obj.default_approver_id = 0 OR
159 l_item_obj.default_approver_id = FND_API.G_MISS_NUM) THEN
160 l_item_obj.default_approver_id := l_resource_id;
161 ELSE
162 IF(AMV_UTILITY_PVT.Is_ResourceIdValid(l_item_obj.default_approver_id)
163 <> TRUE) THEN
164 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
165 FND_MESSAGE.Set_name('AMV','AMV_INVALID_APPROVER_ID');
166 FND_MESSAGE.Set_Token('ID',
167 TO_CHAR(l_item_obj.default_approver_id));
168 FND_MSG_PUB.ADD;
169 END IF;
170 RAISE FND_API.G_EXC_ERROR;
171 END IF;
172 END IF;
173 --
174 -- Check if content type id in the item object is valid
175 IF (l_item_obj.content_type_id IS NULL OR
176 l_item_obj.content_type_id = FND_API.G_MISS_NUM) THEN
177 l_item_obj.content_type_id := NULL;
178 ELSIF (AMV_UTILITY_PVT.Is_ContentTypeIdValid(l_item_obj.content_type_id)
179 <> TRUE) THEN
180 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
181 FND_MESSAGE.Set_name('AMV','AMV_INVALID_CONTENT_TYPE_ID');
182 FND_MESSAGE.Set_Token('ID',
183 TO_CHAR( l_item_obj.content_type_id ) );
184 FND_MSG_PUB.ADD;
185 END IF;
186 RAISE FND_API.G_EXC_ERROR;
187 END IF;
188 -- Check if item_name(title) in the item object is null
189 IF (l_item_obj.item_name IS NULL OR
190 l_item_obj.item_name = FND_API.G_MISS_CHAR) THEN
191 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
192 FND_MESSAGE.Set_name('AMV','AMV_NULL_ITEM_TITLE');
193 FND_MSG_PUB.ADD;
194 END IF;
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197 END IF; --END OF IF (l_item_obj.application_id = ...G_AMV_APP_ID )
198
199 --Do create the record now.
200
201 l_item_rec.application_id := l_item_obj.application_id;
202 l_item_rec.external_access_flag := l_item_obj.external_access_flag;
203 l_item_rec.item_name := l_item_obj.item_name;
204 l_item_rec.description := l_item_obj.description;
205 l_item_rec.text_string := l_item_obj.text_string;
206 l_item_rec.language_code := l_item_obj.language_code;
207 l_item_rec.status_code := l_item_obj.status_code;
208 l_item_rec.effective_start_date := l_item_obj.effective_start_date;
209 l_item_rec.expiration_date := l_item_obj.expiration_date;
210 l_item_rec.item_type := l_item_obj.item_type;
211 l_item_rec.url_string := l_item_obj.url_string;
212 l_item_rec.publication_date := l_item_obj.publication_date;
213 l_item_rec.priority := l_item_obj.priority;
214 l_item_rec.content_type_id := l_item_obj.content_type_id;
215 l_item_rec.owner_id := l_item_obj.owner_id;
216 l_item_rec.default_approver_id := l_item_obj.default_approver_id;
217 l_item_rec.item_destination_type := l_item_obj.item_destination_type;
218
219 JTF_AMV_ITEM_PUB.Create_Item
220 (
221 p_api_version => p_api_version,
222 p_init_msg_list => FND_API.G_FALSE,
223 p_commit => FND_API.G_FALSE,
224 x_return_status => x_return_status,
225 x_msg_count => x_msg_count,
226 x_msg_data => x_msg_data,
227 p_item_rec => l_item_rec,
228 x_item_id => l_item_id
229 );
230 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
232 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
234 END IF;
235 -- Add item's perspectives
236 IF (p_persp_array IS NOT NULL) THEN
237 l_persp_id_array := AMV_PERSPECTIVE_PVT.AMV_NUMBER_VARRAY_TYPE();
238 FOR i IN 1..p_persp_array.COUNT LOOP
239 l_persp_id_array.extend;
240 l_persp_id_array(i) := p_persp_array(i).id;
241 END LOOP;
242 amv_perspective_pvt.Add_ItemPersps
243 (
244 p_api_version => l_api_version,
245 x_return_status => l_return_status,
246 x_msg_count => x_msg_count,
247 x_msg_data => x_msg_data,
248 p_check_login_user => FND_API.G_FALSE,
249 p_item_id => l_item_id,
250 p_perspective_array => l_persp_id_array
251 );
252 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
254 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
255 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
256 x_return_status := FND_API.G_RET_STS_ERROR;
257 END IF;
258 END IF;
259 -- Add item's keywords
260 IF (p_keyword_array IS NOT NULL) THEN
261 Add_ItemKeyword
262 (
263 p_api_version => l_api_version,
264 x_return_status => l_return_status,
265 x_msg_count => x_msg_count,
266 x_msg_data => x_msg_data,
267 p_check_login_user => FND_API.G_FALSE,
268 p_item_id => l_item_id,
269 p_keyword_varray => p_keyword_array
270 );
271 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
274 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 END IF;
277 END IF;
278 -- Add item's authors
279 IF (p_author_array IS NOT NULL) THEN
280 Add_ItemAuthor
281 (
282 p_api_version => l_api_version,
283 x_return_status => l_return_status,
284 x_msg_count => x_msg_count,
285 x_msg_data => x_msg_data,
286 p_check_login_user => FND_API.G_FALSE,
287 p_item_id => l_item_id,
288 p_author_varray => p_author_array
289 );
290 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
292 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
293 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
294 x_return_status := FND_API.G_RET_STS_ERROR;
295 END IF;
296 END IF;
297 IF (p_file_array IS NOT NULL) THEN
298 Add_ItemFile
299 (
300 p_api_version => l_api_version,
301 x_return_status => l_return_status,
302 x_msg_count => x_msg_count,
303 x_msg_data => x_msg_data,
304 p_check_login_user => FND_API.G_FALSE,
305 p_application_id => l_item_obj.application_id,
306 p_item_id => l_item_id,
307 p_file_id_varray => p_file_array
308 );
309 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
312 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
313 x_return_status := FND_API.G_RET_STS_ERROR;
314 END IF;
315 END IF;
316 --Check if channel id array is passed and if so, make sure each id is valid.
317 IF (p_channel_id_array IS NOT NULL) THEN
318 FOR i IN 1..p_channel_id_array.COUNT LOOP
319 IF (AMV_UTILITY_PVT.Is_ChannelIdValid(p_channel_id_array(i))=TRUE)
320 THEN
321 -- match the channel with the newly created content item
322 AMV_MATCH_PVT.Do_ItemChannelMatch
323 (
324 p_api_version => l_api_version,
325 x_return_status => l_return_status,
326 x_msg_count => x_msg_count,
327 x_msg_data => x_msg_data,
328 p_check_login_user => FND_API.G_FALSE,
329 p_channel_id => p_channel_id_array(i),
330 p_item_id => l_item_id,
331 p_table_name_code => G_USED_BY_ITEM,
332 p_match_type => AMV_UTILITY_PVT.G_PUSH
333 );
334 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
336 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
337 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
338 x_return_status := FND_API.G_RET_STS_ERROR;
339 END IF;
340 ELSE
341 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
342 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
343 FND_MESSAGE.Set_Token('RECORD', 'AMV_CHANNEL_TK', TRUE);
344 FND_MESSAGE.Set_Token('ID',
345 TO_CHAR(NVL(p_channel_id_array(i), -1)));
346 FND_MSG_PUB.ADD;
347 END IF;
348 END IF;
349 END LOOP;
350 END IF;
351 --IF (l_item_obj.application_id = AMV_UTILITY_PVT.G_AMV_APP_ID) THEN
352 -- insert a request to matching engine to process item match.
353 -- Ignore the Messages
354 IF (l_item_rec.item_type <> 'MESSAGE_ITEM' ) THEN
355 AMV_MATCH_PVT.Request_ItemMatch
356 (
357 p_api_version => l_api_version,
358 x_return_status => l_return_status,
359 x_msg_count => x_msg_count,
360 x_msg_data => x_msg_data,
361 p_check_login_user => FND_API.G_FALSE,
362 p_item_id => l_item_id
363 );
364 END IF;
365 -- pass back the item id.
366 x_item_id := l_item_id;
367 --Standard check of commit
368 IF FND_API.To_Boolean ( p_commit ) THEN
369 COMMIT WORK;
370 END IF;
371 --Standard call to get message count and if count=1, get the message
372 FND_MSG_PUB.Count_And_Get (
373 p_encoded => FND_API.G_FALSE,
374 p_count => x_msg_count,
375 p_data => x_msg_data
376 );
377 EXCEPTION
378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
379 ROLLBACK TO Create_Item_Pub;
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
381 -- Standard call to get message count and if count=1, get the message
382 FND_MSG_PUB.Count_And_Get (
383 p_encoded => FND_API.G_FALSE,
384 p_count => x_msg_count,
385 p_data => x_msg_data
386 );
387 WHEN FND_API.G_EXC_ERROR THEN
388 ROLLBACK TO Create_Item_Pub;
389 x_return_status := FND_API.G_RET_STS_ERROR;
390 -- Standard call to get message count and if count=1, get the message
391 FND_MSG_PUB.Count_And_Get (
392 p_encoded => FND_API.G_FALSE,
393 p_count => x_msg_count,
394 p_data => x_msg_data
395 );
396 WHEN OTHERS THEN
397 ROLLBACK TO Create_Item_Pub;
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
399 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
400 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
401 END IF;
402 -- Standard call to get message count and if count=1, get the message
403 FND_MSG_PUB.Count_And_Get (
404 p_encoded => FND_API.G_FALSE,
405 p_count => x_msg_count,
406 p_data => x_msg_data
407 );
408 END Create_Item;
409 --------------------------------------------------------------------------------
410 PROCEDURE Delete_Item
411 (
412 p_api_version IN NUMBER,
413 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
414 p_commit IN VARCHAR2 := FND_API.G_FALSE,
415 x_return_status OUT NOCOPY VARCHAR2,
416 x_msg_count OUT NOCOPY NUMBER,
417 x_msg_data OUT NOCOPY VARCHAR2,
418 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
419 p_item_id IN NUMBER
420 ) AS
421 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Item';
422 l_api_version CONSTANT NUMBER := 1.0;
423 l_resource_id NUMBER := -1;
424 l_current_user_id NUMBER := -1;
425 l_current_login_id NUMBER := -1;
426 l_current_user_status VARCHAR2(80);
427 --
428 l_admin_flag VARCHAR2(1);
429 l_return_status VARCHAR2(1);
430 --
431 BEGIN
432 -- Standard call to check for call compatibility.
433 SAVEPOINT Delete_Item_Pub;
434 IF NOT FND_API.Compatible_API_Call (
435 l_api_version,
436 p_api_version,
437 l_api_name,
438 G_PKG_NAME) THEN
439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
440 END IF;
441 --Initialize message list if p_init_msg_list is TRUE.
442 IF FND_API.To_Boolean (p_init_msg_list) THEN
443 FND_MSG_PUB.initialize;
444 END IF;
445 -- Initialize API return status to success
446 x_return_status := FND_API.G_RET_STS_SUCCESS;
447 -- Get the current (login) user id.
448 AMV_UTILITY_PVT.Get_UserInfo(
449 x_resource_id => l_resource_id,
450 x_user_id => l_current_user_id,
451 x_login_id => l_current_login_id,
452 x_user_status => l_current_user_status
453 );
454 IF (p_check_login_user = FND_API.G_TRUE) THEN
455 -- Check if user is login and has the required privilege.
456 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
457 -- User is not login.
458 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
459 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
460 FND_MSG_PUB.ADD;
461 END IF;
462 RAISE FND_API.G_EXC_ERROR;
463 END IF;
464 AMV_USER_PVT.Is_Administrator
465 (
466 p_api_version => 1.0,
467 x_return_status => x_return_status,
468 x_msg_count => x_msg_count,
469 x_msg_data => x_msg_data,
470 p_check_login_user => FND_API.G_FALSE,
471 p_resource_id => l_resource_id,
472 x_result_flag => l_admin_flag
473 );
474 IF (l_admin_flag <> FND_API.G_TRUE) THEN
475 -- User is not an administrator.
476 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
477 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
478 FND_MSG_PUB.ADD;
479 END IF;
480 RAISE FND_API.G_EXC_ERROR;
481 END IF;
482 END IF;
483 -- Check if item id is valid.
484 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
485 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
486 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
487 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
488 FND_MESSAGE.Set_Token('ID', TO_CHAR(p_item_id));
489 FND_MSG_PUB.ADD;
490 END IF;
491 RAISE FND_API.G_EXC_ERROR;
492 END IF;
493 -- Now do the deleting (real job).
494 -- Delete item's perspectives
495 amv_perspective_pvt.Delete_ItemPersps
496 (
497 p_api_version => p_api_version,
498 x_return_status => l_return_status,
499 x_msg_count => x_msg_count,
500 x_msg_data => x_msg_data,
501 p_check_login_user => FND_API.G_FALSE,
502 p_item_id => p_item_id
503 );
504 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
506 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
507 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 END IF;
510 -- Remove item from all channels.
511 DELETE FROM amv_c_chl_item_match
512 WHERE item_id = p_item_id
513 AND table_name_code = G_USED_BY_ITEM;
514 -- Remove item's access.
515 DELETE FROM amv_u_access
516 WHERE access_to_table_record_id = p_item_id
517 AND access_to_table_code = G_USED_BY_ITEM;
518 /*
519 AMV_DistRule_Pvt.Delete_ItemFromDistRules
520 (
521 p_api_version => p_api_version,
522 x_return_status => l_return_status,
523 x_msg_count => x_msg_count,
524 x_msg_data => x_msg_data,
525 p_item_id => p_item_id
526 );
527 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
530 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
531 x_return_status := FND_API.G_RET_STS_ERROR;
532 END IF;
533 */
534 -- Finally delete the item itself.
535 JTF_AMV_ITEM_PUB.Delete_Item
536 (
537 p_api_version => p_api_version,
538 p_init_msg_list => FND_API.G_FALSE,
539 p_commit => p_commit,
540 x_return_status => l_return_status,
541 x_msg_count => x_msg_count,
542 x_msg_data => x_msg_data,
543 p_item_id => p_item_id
544 );
545 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
547 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
548 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
549 x_return_status := FND_API.G_RET_STS_ERROR;
550 END IF;
551 --Standard call to get message count and if count=1, get the message
552 FND_MSG_PUB.Count_And_Get (
553 p_encoded => FND_API.G_FALSE,
554 p_count => x_msg_count,
555 p_data => x_msg_data
556 );
557 EXCEPTION
558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
559 ROLLBACK TO Delete_Item_Pub;
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 -- Standard call to get message count and if count=1, get the message
562 FND_MSG_PUB.Count_And_Get (
563 p_encoded => FND_API.G_FALSE,
564 p_count => x_msg_count,
565 p_data => x_msg_data
566 );
567 WHEN FND_API.G_EXC_ERROR THEN
568 ROLLBACK TO Delete_Item_Pub;
569 x_return_status := FND_API.G_RET_STS_ERROR;
570 -- Standard call to get message count and if count=1, get the message
571 FND_MSG_PUB.Count_And_Get (
572 p_encoded => FND_API.G_FALSE,
573 p_count => x_msg_count,
574 p_data => x_msg_data
575 );
576 WHEN OTHERS THEN
577 ROLLBACK TO Delete_Item_Pub;
578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
579 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
580 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
581 END IF;
582 -- Standard call to get message count and if count=1, get the message
583 FND_MSG_PUB.Count_And_Get (
584 p_encoded => FND_API.G_FALSE,
585 p_count => x_msg_count,
586 p_data => x_msg_data
587 );
588 END Delete_Item;
589 --------------------------------------------------------------------------------
590 PROCEDURE Update_Item
591 (
592 p_api_version IN NUMBER,
593 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
594 p_commit IN VARCHAR2 := FND_API.G_FALSE,
595 x_return_status OUT NOCOPY VARCHAR2,
596 x_msg_count OUT NOCOPY NUMBER,
597 x_msg_data OUT NOCOPY VARCHAR2,
598 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
599 p_channel_id_array IN AMV_NUMBER_VARRAY_TYPE := NULL,
600 p_item_obj IN AMV_ITEM_OBJ_TYPE,
601 p_file_array IN AMV_NUMBER_VARRAY_TYPE,
602 p_persp_array IN AMV_NAMEID_VARRAY_TYPE,
603 p_author_array IN AMV_CHAR_VARRAY_TYPE,
604 p_keyword_array IN AMV_CHAR_VARRAY_TYPE
605 ) AS
606 l_api_name CONSTANT VARCHAR2(30) := 'Update_Item';
607 l_api_version CONSTANT NUMBER := 1.0;
608 l_resource_id NUMBER := -1;
609 l_current_user_id NUMBER := -1;
610 l_current_login_id NUMBER := -1;
611 l_current_user_status VARCHAR2(80);
612 --
613 l_admin_flag VARCHAR2(1);
614 l_return_status VARCHAR2(1);
615 l_item_rec JTF_AMV_ITEM_PUB.ITEM_REC_TYPE;
616 l_persp_id_array AMV_PERSPECTIVE_PVT.AMV_NUMBER_VARRAY_TYPE;
617 l_channel_id_array AMV_NAMEID_VARRAY_TYPE;
618 l_channel_add_id AMV_NUMBER_VARRAY_TYPE;
619 l_channel_remove_id AMV_NUMBER_VARRAY_TYPE;
620 l_flag VARCHAR2(10);
621 l_rec_num NUMBER := 1;
622 l_application_id NUMBER := p_item_obj.application_id;
623
624 CURSOR GetChannelMatch_csr IS
625 SELECT channel_id
626 FROM amv_c_chl_item_match
627 WHERE item_id = p_item_obj.item_id
628 AND available_due_to_type = AMV_UTILITY_PVT.G_PUSH;
629 --
630 BEGIN
631 -- Standard call to check for call compatibility.
632 SAVEPOINT Update_Item_Pub;
633 IF NOT FND_API.Compatible_API_Call (
634 l_api_version,
635 p_api_version,
636 l_api_name,
637 G_PKG_NAME) THEN
638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639 END IF;
640 --Initialize message list if p_init_msg_list is TRUE.
641 IF FND_API.To_Boolean (p_init_msg_list) THEN
642 FND_MSG_PUB.initialize;
643 END IF;
644 -- Initialize API return status to success
645 x_return_status := FND_API.G_RET_STS_SUCCESS;
646 -- Get the current (login) user id.
647 AMV_UTILITY_PVT.Get_UserInfo(
648 x_resource_id => l_resource_id,
649 x_user_id => l_current_user_id,
650 x_login_id => l_current_login_id,
651 x_user_status => l_current_user_status
652 );
653 IF (p_check_login_user = FND_API.G_TRUE) THEN
654 -- Check if user is login and has the required privilege.
655 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
656 -- User is not login.
657 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
658 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
659 FND_MSG_PUB.ADD;
660 END IF;
661 RAISE FND_API.G_EXC_ERROR;
662 END IF;
663 AMV_USER_PVT.Is_Administrator
664 (
665 p_api_version => 1.0,
666 x_return_status => x_return_status,
667 x_msg_count => x_msg_count,
668 x_msg_data => x_msg_data,
669 p_check_login_user => FND_API.G_FALSE,
670 p_resource_id => l_resource_id,
671 x_result_flag => l_admin_flag
672 );
673 IF (l_admin_flag <> FND_API.G_TRUE) THEN
674 -- User is not an administrator.
675 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
676 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
677 FND_MSG_PUB.ADD;
678 END IF;
679 RAISE FND_API.G_EXC_ERROR;
680 END IF;
681 END IF;
682
683 --
684 --Inserted the following check to fix bug # 2740293
685 --Because the application id should not be 0 while publishing an item
686 --
687 IF l_application_id = 0 THEN
688
689 l_application_id := AMV_UTILITY_PVT.G_AMV_APP_ID;
690
691 END IF;
692
693
694 -- MAKE SURE THE PASSED ITEM OBJECT HAS ALL THE RIGHT INFORMATION.
695 -- Check if the object is really passed.
696 IF (p_item_obj.item_id IS NULL) THEN
697 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
698 FND_MESSAGE.Set_name('AMV','AMV_NEED_ITEM_INFO');
699 FND_MSG_PUB.ADD;
700 END IF;
701 RAISE FND_API.G_EXC_ERROR;
702 END IF;
703 --Do update the record now.
704
705 l_item_rec.item_id := p_item_obj.item_id;
706 l_item_rec.object_version_number := p_item_obj.object_version_number;
707 l_item_rec.application_id := l_application_id;
708 l_item_rec.external_access_flag := p_item_obj.external_access_flag;
709 l_item_rec.item_name := p_item_obj.item_name;
710 l_item_rec.description := p_item_obj.description;
711 l_item_rec.text_string := p_item_obj.text_string;
712 l_item_rec.language_code := p_item_obj.language_code;
713 l_item_rec.status_code := p_item_obj.status_code;
714 l_item_rec.effective_start_date := p_item_obj.effective_start_date;
715 l_item_rec.expiration_date := p_item_obj.expiration_date;
716 l_item_rec.item_type := p_item_obj.item_type;
717 l_item_rec.url_string := p_item_obj.url_string;
718 l_item_rec.publication_date := p_item_obj.publication_date;
719 l_item_rec.priority := p_item_obj.priority;
720 l_item_rec.content_type_id := p_item_obj.content_type_id;
721 l_item_rec.owner_id := p_item_obj.owner_id;
722 l_item_rec.default_approver_id := p_item_obj.default_approver_id;
723 l_item_rec.item_destination_type := p_item_obj.item_destination_type;
724 IF (l_item_rec.application_id = AMV_UTILITY_PVT.G_AMV_APP_ID) THEN
725 IF (l_item_rec.external_access_flag <> FND_API.G_TRUE AND
726 l_item_rec.external_access_flag <> FND_API.G_FALSE) THEN
727 l_item_rec.external_access_flag := FND_API.G_MISS_CHAR;
728 END IF;
729 IF (l_item_rec.item_name IS NULL) THEN
730 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
731 FND_MESSAGE.Set_name('AMV','AMV_NULL_ITEM_TITLE');
732 FND_MSG_PUB.ADD;
733 END IF;
734 l_item_rec.item_name := FND_API.G_MISS_CHAR;
735 END IF;
736 IF (l_item_rec.item_type IS NULL) THEN
737 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
738 FND_MESSAGE.Set_name('AMV','AMV_NULL_ITEM_TYPE');
739 FND_MSG_PUB.ADD;
740 END IF;
741 l_item_rec.item_type := FND_API.G_MISS_CHAR;
742 END IF;
743 IF ( l_item_rec.content_type_id IS NOT NULL AND
744 l_item_rec.content_type_id <> FND_API.G_MISS_NUM) THEN
745 IF (AMV_UTILITY_PVT.Is_ContentTypeIdValid(l_item_rec.content_type_id)
746 <> TRUE) THEN
747 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
748 FND_MESSAGE.Set_name('AMV','AMV_INVALID_CONTENT_TYPE_ID');
749 FND_MESSAGE.Set_Token('ID',
750 TO_CHAR( l_item_rec.content_type_id ) );
751 FND_MSG_PUB.ADD;
752 END IF;
753 l_item_rec.content_type_id := FND_API.G_MISS_NUM;
754 END IF;
755 END IF;
756 END IF;
757 JTF_AMV_ITEM_PUB.Update_Item
758 (
759 p_api_version => p_api_version,
760 p_init_msg_list => FND_API.G_FALSE,
761 p_commit => FND_API.G_FALSE,
762 x_return_status => x_return_status,
763 x_msg_count => x_msg_count,
764 x_msg_data => x_msg_data,
765 p_item_rec => l_item_rec
766 );
767 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
768 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
769 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771 END IF;
772
773 IF (p_persp_array IS NOT NULL) THEN
774 l_persp_id_array := AMV_PERSPECTIVE_PVT.AMV_NUMBER_VARRAY_TYPE();
775 FOR i IN 1..p_persp_array.COUNT LOOP
776 l_persp_id_array.extend;
777 l_persp_id_array(i) := p_persp_array(i).id;
778 END LOOP;
779 amv_perspective_pvt.Update_ItemPersps
780 (
781 p_api_version => p_api_version,
782 x_return_status => l_return_status,
783 x_msg_count => x_msg_count,
784 x_msg_data => x_msg_data,
785 p_check_login_user => FND_API.G_FALSE,
786 p_item_id => p_item_obj.item_id,
787 p_perspective_array => l_persp_id_array
788 );
789 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
791 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
792 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
793 x_return_status := FND_API.G_RET_STS_ERROR;
794 END IF;
795 END IF;
796 IF (p_author_array IS NOT NULL) THEN
797 Replace_ItemAuthor
798 (
799 p_api_version => p_api_version,
800 x_return_status => l_return_status,
801 x_msg_count => x_msg_count,
802 x_msg_data => x_msg_data,
803 p_check_login_user => FND_API.G_FALSE,
804 p_item_id => p_item_obj.item_id,
805 p_author_varray => p_author_array
806 );
807 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
808 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
809 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
810 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
811 x_return_status := FND_API.G_RET_STS_ERROR;
812 END IF;
813 END IF;
814 IF (p_file_array IS NOT NULL) THEN
815 Replace_ItemFile
816 (
817 p_api_version => l_api_version,
818 x_return_status => l_return_status,
819 x_msg_count => x_msg_count,
820 x_msg_data => x_msg_data,
821 p_check_login_user => FND_API.G_FALSE,
822 p_item_id => p_item_obj.item_id,
823 p_file_id_varray => p_file_array
824 );
825 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
827 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
828 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
829 x_return_status := FND_API.G_RET_STS_ERROR;
830 END IF;
831 END IF;
832
833
834 IF (p_keyword_array IS NOT NULL) THEN
835 Replace_ItemKeyword
836 (
837 p_api_version => p_api_version,
838 x_return_status => l_return_status,
839 x_msg_count => x_msg_count,
840 x_msg_data => x_msg_data,
841 p_check_login_user => FND_API.G_FALSE,
842 p_item_id => p_item_obj.item_id,
843 p_keyword_varray => p_keyword_array
844 );
845 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
847 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
848 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
849 x_return_status := FND_API.G_RET_STS_ERROR;
850 END IF;
851 END IF;
852 --Check if channel id array is passed and if so, make sure each id is valid.
853 l_channel_id_array := AMV_NAMEID_VARRAY_TYPE();
854 l_rec_num := 1;
855 IF (p_channel_id_array IS NOT NULL) THEN
856 -- get all channels matched with item on force mode
857 OPEN GetChannelMatch_csr;
858 LOOP
859 l_channel_id_array.extend;
860 FETCH GetChannelMatch_csr INTO l_channel_id_array(l_rec_num).id;
861 EXIT WHEN GetChannelMatch_csr%NOTFOUND;
862 l_rec_num := l_rec_num + 1;
863 END LOOP;
864 CLOSE GetChannelMatch_csr;
865
866 -- initialize rec num
867 l_rec_num := 1;
868 l_channel_add_id := AMV_NUMBER_VARRAY_TYPE();
869
870 -- check if channel exists in the list
871 FOR i IN 1..p_channel_id_array.COUNT LOOP
872 FOR j IN 1..l_channel_id_array.COUNT LOOP
873 IF p_channel_id_array(i) = l_channel_id_array(j).id THEN
874 l_flag := 'EXISTS';
875 l_channel_id_array(j).name := 'T';
876 ELSE
877 l_flag := 'ADDED';
878 END IF;
879 EXIT WHEN p_channel_id_array(i) = l_channel_id_array(j).id;
880 END LOOP;
881 IF l_flag = 'ADDED' THEN
882 -- build channels list to add
883 l_channel_add_id.extend;
884 l_channel_add_id(l_rec_num) := p_channel_id_array(i);
885 l_rec_num := l_rec_num + 1;
886 ELSE
887 l_flag := 'ADDED';
888 END IF;
889 END LOOP;
890
891 -- initialize rec num
892 l_rec_num := 1;
893 l_channel_remove_id := AMV_NUMBER_VARRAY_TYPE();
894
895 -- build channels list to delete
896 FOR i in 1..l_channel_id_array.count LOOP
897 IF l_channel_id_array(i).name is null THEN
898 l_channel_remove_id.extend;
899 l_channel_remove_id(l_rec_num) := l_channel_id_array(i).id;
900 l_rec_num := l_rec_num + 1;
901 END IF;
902 END LOOP;
903
904 -- delete removed channels
905 FOR i IN 1..l_channel_remove_id.COUNT LOOP
906 IF (AMV_UTILITY_PVT.Is_ChannelIdValid(l_channel_remove_id(i))=TRUE)
907 THEN
908 -- remove the channel from content item
909 AMV_MATCH_PVT.Remove_ItemChannelMatch
910 (
911 p_api_version => l_api_version,
912 x_return_status => l_return_status,
913 x_msg_count => x_msg_count,
914 x_msg_data => x_msg_data,
915 p_check_login_user => FND_API.G_FALSE,
916 p_channel_id => l_channel_remove_id(i),
917 p_item_id => p_item_obj.item_id,
918 p_table_name_code => G_USED_BY_ITEM
919 );
920 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
922 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
923 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
924 x_return_status := FND_API.G_RET_STS_ERROR;
925 END IF;
926 ELSE
927 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
928 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
929 FND_MESSAGE.Set_Token('RECORD', 'AMV_CHANNEL_TK', TRUE);
930 FND_MESSAGE.Set_Token('ID',
931 TO_CHAR(NVL(l_channel_remove_id(i), -1)));
932 FND_MSG_PUB.ADD;
933 END IF;
934 END IF;
935 END LOOP;
936
937 -- Add items to new channels
938 FOR i IN 1..l_channel_add_id.COUNT LOOP
939 IF (AMV_UTILITY_PVT.Is_ChannelIdValid(l_channel_add_id(i))=TRUE)
940 THEN
941 -- match the channel with the newly created content item
942 AMV_MATCH_PVT.Do_ItemChannelMatch
943 (
944 p_api_version => l_api_version,
945 x_return_status => l_return_status,
946 x_msg_count => x_msg_count,
947 x_msg_data => x_msg_data,
948 p_check_login_user => FND_API.G_FALSE,
949 p_channel_id => l_channel_add_id(i),
950 p_item_id => p_item_obj.item_id,
951 p_table_name_code => G_USED_BY_ITEM,
952 p_match_type => AMV_UTILITY_PVT.G_PUSH
953 );
954 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
956 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
957 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
958 x_return_status := FND_API.G_RET_STS_ERROR;
959 END IF;
960 ELSE
961 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
962 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
963 FND_MESSAGE.Set_Token('RECORD', 'AMV_CHANNEL_TK', TRUE);
964 FND_MESSAGE.Set_Token('ID',
965 TO_CHAR(NVL(p_channel_id_array(i), -1)));
966 FND_MSG_PUB.ADD;
967 END IF;
968 END IF;
969 END LOOP;
970 END IF;
971 --IF (p_item_obj.application_id = AMV_UTILITY_PVT.G_AMV_APP_ID) THEN
972 -- insert a request to matching engine to process item match.
973 IF (p_item_obj.item_type <> 'MESSAGE_ITEM' ) THEN
974 AMV_MATCH_PVT.Request_ItemMatch
975 (
976 p_api_version => l_api_version,
977 x_return_status => l_return_status,
978 x_msg_count => x_msg_count,
979 x_msg_data => x_msg_data,
980 p_check_login_user => FND_API.G_FALSE,
981 p_item_id => p_item_obj.item_id
982 );
983 END IF;
984 --Standard check of commit
985 IF FND_API.To_Boolean ( p_commit ) THEN
986 COMMIT WORK;
987 END IF;
988 --Standard call to get message count and if count=1, get the message
989 FND_MSG_PUB.Count_And_Get (
990 p_encoded => FND_API.G_FALSE,
991 p_count => x_msg_count,
992 p_data => x_msg_data
993 );
994 EXCEPTION
995 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
996 ROLLBACK TO Update_Item_Pub;
997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
998 -- Standard call to get message count and if count=1, get the message
999 FND_MSG_PUB.Count_And_Get (
1000 p_encoded => FND_API.G_FALSE,
1001 p_count => x_msg_count,
1002 p_data => x_msg_data
1003 );
1004 WHEN FND_API.G_EXC_ERROR THEN
1005 ROLLBACK TO Update_Item_Pub;
1006 x_return_status := FND_API.G_RET_STS_ERROR;
1007 -- Standard call to get message count and if count=1, get the message
1008 FND_MSG_PUB.Count_And_Get (
1009 p_encoded => FND_API.G_FALSE,
1010 p_count => x_msg_count,
1011 p_data => x_msg_data
1012 );
1013 WHEN OTHERS THEN
1014 ROLLBACK TO Update_Item_Pub;
1015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1016 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1017 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1018 END IF;
1019 -- Standard call to get message count and if count=1, get the message
1020 FND_MSG_PUB.Count_And_Get (
1021 p_encoded => FND_API.G_FALSE,
1022 p_count => x_msg_count,
1023 p_data => x_msg_data
1024 );
1025 END Update_Item;
1026 --------------------------------------------------------------------------------
1027 PROCEDURE Get_Item
1028 (
1029 p_api_version IN NUMBER,
1030 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1031 x_return_status OUT NOCOPY VARCHAR2,
1032 x_msg_count OUT NOCOPY NUMBER,
1033 x_msg_data OUT NOCOPY VARCHAR2,
1034 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1035 p_item_id IN NUMBER,
1036 x_item_obj OUT NOCOPY AMV_ITEM_OBJ_TYPE,
1037 x_file_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE,
1038 x_persp_array OUT NOCOPY AMV_NAMEID_VARRAY_TYPE,
1039 x_author_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE,
1040 x_keyword_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE
1041 ) AS
1042 l_api_name CONSTANT VARCHAR2(30) := 'Get_Item';
1043 l_api_version CONSTANT NUMBER := 1.0;
1044 l_resource_id NUMBER := -1;
1045 l_current_user_id NUMBER := -1;
1046 l_current_login_id NUMBER := -1;
1047 l_current_user_status VARCHAR2(80);
1048 --
1049 l_return_status VARCHAR2(1);
1050 l_item_rec JTF_AMV_ITEM_PUB.ITEM_REC_TYPE;
1051 l_persp_obj_varray AMV_PERSPECTIVE_PVT.AMV_PERSPECTIVE_OBJ_VARRAY;
1052 l_persp_varray AMV_NAMEID_VARRAY_TYPE;
1053 l_author_varray AMV_CHAR_VARRAY_TYPE;
1054 l_keyword_varray AMV_CHAR_VARRAY_TYPE;
1055 l_file_id_varray AMV_NUMBER_VARRAY_TYPE;
1056 --
1057 BEGIN
1058 -- Standard call to check for call compatibility.
1059 IF NOT FND_API.Compatible_API_Call (
1060 l_api_version,
1061 p_api_version,
1062 l_api_name,
1063 G_PKG_NAME) THEN
1064 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1065 END IF;
1066 --Initialize message list if p_init_msg_list is TRUE.
1067 IF FND_API.To_Boolean (p_init_msg_list) THEN
1068 FND_MSG_PUB.initialize;
1069 END IF;
1070 -- Initialize API return status to success
1071 x_return_status := FND_API.G_RET_STS_SUCCESS;
1072 -- Get the current (login) user id.
1073 AMV_UTILITY_PVT.Get_UserInfo(
1074 x_resource_id => l_resource_id,
1075 x_user_id => l_current_user_id,
1076 x_login_id => l_current_login_id,
1077 x_user_status => l_current_user_status
1078 );
1079 IF (p_check_login_user = FND_API.G_TRUE) THEN
1080 -- Check if user is login and has the required privilege.
1081 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1082 -- User is not login.
1083 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1084 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1085 FND_MSG_PUB.ADD;
1086 END IF;
1087 RAISE FND_API.G_EXC_ERROR;
1088 END IF;
1089 END IF;
1090 -- Check if item id is valid.
1091 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
1092 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1093 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
1094 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
1095 FND_MESSAGE.Set_Token('ID', TO_CHAR(p_item_id));
1096 FND_MSG_PUB.ADD;
1097 END IF;
1098 RAISE FND_API.G_EXC_ERROR;
1099 END IF;
1100 JTF_AMV_ITEM_PUB.Get_Item
1101 (
1102 p_api_version => p_api_version,
1103 p_init_msg_list => FND_API.G_FALSE,
1104 x_return_status => x_return_status,
1105 x_msg_count => x_msg_count,
1106 x_msg_data => x_msg_data,
1107 p_item_id => p_item_id,
1108 x_item_rec => l_item_rec
1109 );
1110 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1112 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1114 END IF;
1115 -- Get item's perspectives.
1116 --l_persp_id_varray := AMV_NUMBER_VARRAY_TYPE();
1117 --l_persp_name_varray := AMV_CHAR_VARRAY_TYPE();
1118 l_persp_varray := AMV_NAMEID_VARRAY_TYPE();
1119 l_persp_obj_varray := AMV_PERSPECTIVE_PVT.AMV_PERSPECTIVE_OBJ_VARRAY();
1120 AMV_PERSPECTIVE_PVT.Get_ItemPersps
1121 (
1122 p_api_version => p_api_version,
1123 x_return_status => l_return_status,
1124 x_msg_count => x_msg_count,
1125 x_msg_data => x_msg_data,
1126 p_check_login_user => FND_API.G_FALSE,
1127 p_item_id => p_item_id,
1128 x_perspective_obj_varray => l_persp_obj_varray
1129 );
1130 IF (l_persp_obj_varray IS NOT NULL) THEN
1131 FOR i IN 1..l_persp_obj_varray.COUNT LOOP
1132 l_persp_varray.extend;
1133 l_persp_varray(i).id := l_persp_obj_varray(i).perspective_id;
1134 l_persp_varray(i).name := l_persp_obj_varray(i).perspective_name;
1135 END LOOP;
1136 END IF;
1137 -- Get item's keywords.
1138 Get_ItemKeyword
1139 (
1140 p_api_version => p_api_version,
1141 x_return_status => l_return_status,
1142 x_msg_count => x_msg_count,
1143 x_msg_data => x_msg_data,
1144 p_check_login_user => FND_API.G_FALSE,
1145 p_item_id => p_item_id,
1146 x_keyword_varray => l_keyword_varray
1147 );
1148 IF (l_keyword_varray IS NULL) THEN
1149 l_keyword_varray := AMV_CHAR_VARRAY_TYPE();
1150 END IF;
1151 -- Get item's authors.
1152 Get_ItemAuthor
1153 (
1154 p_api_version => p_api_version,
1155 x_return_status => l_return_status,
1156 x_msg_count => x_msg_count,
1157 x_msg_data => x_msg_data,
1158 p_check_login_user => FND_API.G_FALSE,
1159 p_item_id => p_item_id,
1160 x_author_varray => l_author_varray
1161 );
1162 IF (l_author_varray IS NULL) THEN
1163 l_author_varray := AMV_CHAR_VARRAY_TYPE();
1164 END IF;
1165 -- Get item's file id.
1166 Get_ItemFile
1167 (
1168 p_api_version => p_api_version,
1169 x_return_status => l_return_status,
1170 x_msg_count => x_msg_count,
1171 x_msg_data => x_msg_data,
1172 p_check_login_user => FND_API.G_FALSE,
1173 p_item_id => p_item_id,
1174 x_file_id_varray => l_file_id_varray
1175 );
1176 IF (l_file_id_varray IS NULL) THEN
1177 l_file_id_varray := AMV_NUMBER_VARRAY_TYPE();
1178 END IF;
1179 -- Finally construct the return object.
1180 x_item_obj.item_id := p_item_id;
1181 x_item_obj.object_version_number := l_item_rec.OBJECT_VERSION_NUMBER;
1182 x_item_obj.creation_date := l_item_rec.CREATION_DATE;
1183 x_item_obj.created_by := l_item_rec.CREATED_BY;
1184 x_item_obj.last_update_date := l_item_rec.LAST_UPDATE_DATE;
1185 x_item_obj.last_updated_by := l_item_rec.LAST_UPDATED_BY;
1186 x_item_obj.last_update_login := l_item_rec.LAST_UPDATE_LOGIN;
1187 x_item_obj.application_id := l_item_rec.APPLICATION_ID;
1188 x_item_obj.external_access_flag := l_item_rec.EXTERNAL_ACCESS_FLAG;
1189 x_item_obj.item_name := l_item_rec.ITEM_NAME;
1190 x_item_obj.description := l_item_rec.DESCRIPTION;
1191 x_item_obj.text_string := l_item_rec.TEXT_STRING;
1192 x_item_obj.language_code := l_item_rec.LANGUAGE_CODE;
1193 x_item_obj.status_code := l_item_rec.STATUS_CODE;
1194 x_item_obj.effective_start_date := l_item_rec.EFFECTIVE_START_DATE;
1195 x_item_obj.expiration_date := l_item_rec.EXPIRATION_DATE;
1196 x_item_obj.item_type := l_item_rec.ITEM_TYPE;
1197 x_item_obj.url_string := l_item_rec.URL_STRING;
1198 x_item_obj.publication_date := l_item_rec.PUBLICATION_DATE;
1199 x_item_obj.priority := l_item_rec.PRIORITY;
1200 x_item_obj.content_type_id := l_item_rec.CONTENT_TYPE_ID;
1201 x_item_obj.owner_id := l_item_rec.OWNER_ID;
1202 x_item_obj.default_approver_id := l_item_rec.DEFAULT_APPROVER_ID;
1203 x_item_obj.item_destination_type := l_item_rec.ITEM_DESTINATION_TYPE;
1204
1205 x_file_array := AMV_NUMBER_VARRAY_TYPE();
1206 x_file_array := l_file_id_varray;
1207 x_persp_array := AMV_NAMEID_VARRAY_TYPE();
1208 x_persp_array := l_persp_varray;
1209 x_author_array := AMV_CHAR_VARRAY_TYPE();
1210 x_author_array := l_author_varray;
1211 x_keyword_array := AMV_CHAR_VARRAY_TYPE();
1212 x_keyword_array := l_keyword_varray;
1213 --Standard call to get message count and if count=1, get the message
1214 FND_MSG_PUB.Count_And_Get (
1215 p_encoded => FND_API.G_FALSE,
1216 p_count => x_msg_count,
1217 p_data => x_msg_data
1218 );
1219 EXCEPTION
1220 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1222 -- Standard call to get message count and if count=1, get the message
1223 FND_MSG_PUB.Count_And_Get (
1224 p_encoded => FND_API.G_FALSE,
1225 p_count => x_msg_count,
1226 p_data => x_msg_data
1227 );
1228 WHEN FND_API.G_EXC_ERROR THEN
1229 x_return_status := FND_API.G_RET_STS_ERROR;
1230 -- Standard call to get message count and if count=1, get the message
1231 FND_MSG_PUB.Count_And_Get (
1232 p_encoded => FND_API.G_FALSE,
1233 p_count => x_msg_count,
1234 p_data => x_msg_data
1235 );
1236 WHEN OTHERS THEN
1237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1238 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1239 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1240 END IF;
1241 -- Standard call to get message count and if count=1, get the message
1242 FND_MSG_PUB.Count_And_Get (
1243 p_encoded => FND_API.G_FALSE,
1244 p_count => x_msg_count,
1245 p_data => x_msg_data
1246 );
1247 END Get_Item;
1248 --------------------------------------------------------------------------------
1249 PROCEDURE Find_Item
1250 (
1251 p_api_version IN NUMBER,
1252 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1253 x_return_status OUT NOCOPY VARCHAR2,
1254 x_msg_count OUT NOCOPY NUMBER,
1255 x_msg_data OUT NOCOPY VARCHAR2,
1256 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1257 p_item_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1258 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
1259 p_item_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
1260 p_subset_request_obj IN AMV_REQUEST_OBJ_TYPE,
1261 x_subset_return_obj OUT NOCOPY AMV_RETURN_OBJ_TYPE,
1262 x_item_obj_array OUT NOCOPY AMV_SIMPLE_ITEM_OBJ_VARRAY
1263 ) AS
1264 --
1265 l_api_name CONSTANT VARCHAR2(30) := 'Find_Item';
1266 l_api_version CONSTANT NUMBER := 1.0;
1267 l_resource_id NUMBER := -1;
1268 l_current_user_id NUMBER := -1;
1269 l_current_login_id NUMBER := -1;
1270 l_current_user_status VARCHAR2(80);
1271 --
1272 l_cursor CursorType;
1273 l_sql_statement VARCHAR2(2000);
1274 l_sql_statement2 VARCHAR2(2000);
1275 l_where_clause VARCHAR2(2000);
1276 l_total_count NUMBER := 1;
1277 l_fetch_count NUMBER := 0;
1278 l_start_with NUMBER;
1279 l_total_record_count NUMBER;
1280 --
1281 l_item_id NUMBER;
1282 l_object_version_number NUMBER;
1283 l_creation_date DATE;
1284 l_created_by NUMBER;
1285 l_last_update_date DATE;
1286 l_last_updated_by NUMBER;
1287 l_last_update_login NUMBER;
1288 l_application_id NUMBER;
1289 l_external_access VARCHAR2(1);
1290 l_item_name VARCHAR2(240);
1291 l_description VARCHAR2(4000);
1292 l_text_string VARCHAR2(4000);
1293 l_language_code VARCHAR2(4);
1294 l_status_code VARCHAR2(30);
1295 l_owner_id NUMBER;
1296 l_effective_start_date DATE;
1297 l_expiration_date DATE;
1298 l_item_type VARCHAR2(240);
1299 l_content_type_id NUMBER;
1300 l_publication_date DATE;
1301 l_priority VARCHAR2(30);
1302 l_default_approver_id NUMBER;
1303 l_url_string VARCHAR2(2000);
1304 l_item_destination_type VARCHAR2(240);
1305 --
1306 l_return_status VARCHAR2(1);
1307 l_persp_obj_varray AMV_PERSPECTIVE_PVT.AMV_PERSPECTIVE_OBJ_VARRAY;
1308 l_persp_id_list VARCHAR2(2000);
1309 l_persp_name_list VARCHAR2(2000);
1310 l_keyword_varray AMV_CHAR_VARRAY_TYPE;
1311 l_keyword_list VARCHAR2(2000);
1312 l_author_varray AMV_CHAR_VARRAY_TYPE;
1313 l_author_list VARCHAR2(2000);
1314 l_file_id_varray AMV_NUMBER_VARRAY_TYPE;
1315 l_file_id_list VARCHAR2(2000);
1316 --
1317 BEGIN
1318 -- Standard call to check for call compatibility.
1319 IF NOT FND_API.Compatible_API_Call (
1320 l_api_version,
1321 p_api_version,
1322 l_api_name,
1323 G_PKG_NAME) THEN
1324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1325 END IF;
1326 -- Debug Message
1327 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1328 FND_MESSAGE.Set_name('AMV','PVT Find Item API: Start');
1329 FND_MSG_PUB.ADD;
1330 END IF;
1331 --Initialize message list if p_init_msg_list is TRUE.
1332 IF FND_API.To_Boolean (p_init_msg_list) THEN
1333 FND_MSG_PUB.initialize;
1334 END IF;
1335 -- Initialize API return status to success
1336 x_return_status := FND_API.G_RET_STS_SUCCESS;
1337 -- Get the current (login) user id.
1338 AMV_UTILITY_PVT.Get_UserInfo(
1339 x_resource_id => l_resource_id,
1340 x_user_id => l_current_user_id,
1341 x_login_id => l_current_login_id,
1342 x_user_status => l_current_user_status
1343 );
1344 IF (p_check_login_user = FND_API.G_TRUE) THEN
1345 -- Check if user is login and has the required privilege.
1346 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1347 -- User is not login.
1348 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1349 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1350 FND_MSG_PUB.ADD;
1351 END IF;
1352 RAISE FND_API.G_EXC_ERROR;
1353 END IF;
1354 END IF;
1355 -- This is to make database happy
1356 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1357 l_current_login_id := -1;
1358 l_current_user_id := -1;
1359 END IF;
1360 -- Now create SQL statement and find the results:
1361 l_sql_statement :=
1362 'Select ' ||
1363 'ITEM_ID, ' ||
1364 'OBJECT_VERSION_NUMBER, ' ||
1365 'CREATION_DATE, ' ||
1366 'CREATED_BY, ' ||
1367 'LAST_UPDATE_DATE, ' ||
1368 'LAST_UPDATED_BY, ' ||
1369 'LAST_UPDATE_LOGIN, ' ||
1370 'APPLICATION_ID, ' ||
1371 'EXTERNAL_ACCESS_FLAG, ' ||
1372 'ITEM_NAME, ' ||
1373 'DESCRIPTION, ' ||
1374 'TEXT_STRING, ' ||
1375 'LANGUAGE_CODE, ' ||
1376 'STATUS_CODE, ' ||
1377 'EFFECTIVE_START_DATE, ' ||
1378 'EXPIRATION_DATE, ' ||
1379 'ITEM_TYPE, ' ||
1380 'URL_STRING, ' ||
1381 'PUBLICATION_DATE, ' ||
1382 'PRIORITY, ' ||
1383 'CONTENT_TYPE_ID, ' ||
1384 'OWNER_ID, ' ||
1385 'DEFAULT_APPROVER_ID, ' ||
1386 'ITEM_DESTINATION_TYPE ' ||
1387 'From JTF_AMV_ITEMS_VL';
1388 l_sql_statement2 :=
1389 'Select count(*) ' ||
1390 'From JTF_AMV_ITEMS_VL';
1391 --
1392 l_where_clause := ' ';
1393 IF (p_item_name IS NOT NULL AND
1394 p_item_name <> FND_API.G_MISS_CHAR) THEN
1395 l_where_clause := l_where_clause ||
1396 'And ITEM_NAME Like ''' || p_item_name || ''' ';
1397 END IF;
1398 IF (p_description IS NOT NULL AND
1399 p_description <> FND_API.G_MISS_CHAR) THEN
1400 l_where_clause := l_where_clause ||
1401 'And DESCRIPTION Like ''' || p_description || ''' ';
1402 END IF;
1403 IF (p_item_type IS NOT NULL AND
1404 p_item_type <> FND_API.G_MISS_CHAR) THEN
1405 l_where_clause := l_where_clause ||
1406 'And ITEM_TYPE Like ''' || p_item_type || ''' ';
1407 END IF;
1408 l_sql_statement := l_sql_statement ||
1409 l_where_clause || 'ORDER BY ITEM_NAME ';
1410 l_sql_statement2 := l_sql_statement2 ||
1411 l_where_clause;
1412 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1413 FND_MESSAGE.Set_name('AMV','AMV_API_DEBUG_MESSAGE');
1414 FND_MESSAGE.Set_Token('ROW', l_sql_statement);
1415 FND_MSG_PUB.ADD;
1416 --
1417 FND_MESSAGE.Set_name('AMV','AMV_API_DEBUG_MESSAGE');
1418 FND_MESSAGE.Set_Token('ROW', l_sql_statement2);
1419 FND_MSG_PUB.ADD;
1420 END IF;
1421 --Execute the SQL statements to get the total count:
1422 IF (p_subset_request_obj.return_total_count_flag = FND_API.G_TRUE) THEN
1423 OPEN l_cursor FOR l_sql_statement2;
1424 FETCH l_cursor INTO l_total_record_count;
1425 CLOSE l_cursor;
1426 END IF;
1427 --Execute the SQL statements to get records
1428 l_start_with := p_subset_request_obj.start_record_position;
1429 x_item_obj_array := AMV_SIMPLE_ITEM_OBJ_VARRAY();
1430 OPEN l_cursor FOR l_sql_statement;
1431 LOOP
1432 FETCH l_cursor INTO
1433 l_item_id,
1434 l_object_version_number,
1435 l_creation_date,
1436 l_created_by,
1437 l_last_update_date,
1438 l_last_updated_by,
1439 l_last_update_login,
1440 l_application_id,
1441 l_external_access,
1442 l_item_name,
1443 l_description,
1444 l_text_string,
1445 l_language_code,
1446 l_status_code,
1447 l_effective_start_date,
1448 l_expiration_date,
1449 l_item_type,
1450 l_url_string,
1451 l_publication_date,
1452 l_priority,
1453 l_content_type_id,
1454 l_owner_id,
1455 l_default_approver_id,
1456 l_item_destination_type;
1457 EXIT WHEN l_cursor%NOTFOUND;
1458 IF (l_start_with <= l_total_count AND
1459 l_fetch_count < p_subset_request_obj.records_requested) THEN
1460 l_fetch_count := l_fetch_count + 1;
1461 -- Get item's perspectives.
1462 AMV_PERSPECTIVE_PVT.Get_ItemPersps
1463 (
1464 p_api_version => p_api_version,
1465 x_return_status => l_return_status,
1466 x_msg_count => x_msg_count,
1467 x_msg_data => x_msg_data,
1468 p_check_login_user => FND_API.G_FALSE,
1469 p_item_id => l_item_id,
1470 x_perspective_obj_varray => l_persp_obj_varray
1471 );
1472 l_persp_id_list := '';
1473 l_persp_name_list := '';
1474 IF (l_persp_obj_varray IS NOT NULL) THEN
1475 FOR i IN 1..l_persp_obj_varray.COUNT LOOP
1476 l_persp_id_list := l_persp_id_list ||
1477 l_persp_obj_varray(i).perspective_id || ' ';
1478 l_persp_name_list := l_persp_name_list ||
1479 l_persp_obj_varray(i).perspective_name || ' ';
1480 END LOOP;
1481 END IF;
1482 -- Get item's keywords.
1483 Get_ItemKeyword
1484 (
1485 p_api_version => p_api_version,
1486 x_return_status => l_return_status,
1487 x_msg_count => x_msg_count,
1488 x_msg_data => x_msg_data,
1489 p_check_login_user => FND_API.G_FALSE,
1490 p_item_id => l_item_id,
1491 x_keyword_varray => l_keyword_varray
1492 );
1493 l_keyword_list := '';
1494 IF (l_keyword_varray IS NOT NULL) THEN
1495 FOR i IN 1..l_keyword_varray.COUNT LOOP
1496 l_keyword_list := l_keyword_list ||
1497 l_keyword_varray(i) || ' ';
1498 END LOOP;
1499 END IF;
1500 -- Get item's authors.
1501 Get_ItemAuthor
1502 (
1503 p_api_version => p_api_version,
1504 x_return_status => l_return_status,
1505 x_msg_count => x_msg_count,
1506 x_msg_data => x_msg_data,
1507 p_check_login_user => FND_API.G_FALSE,
1508 p_item_id => l_item_id,
1509 x_author_varray => l_author_varray
1510 );
1511 l_author_list := '';
1512 IF (l_author_varray IS NOT NULL) THEN
1513 FOR i IN 1..l_keyword_varray.COUNT LOOP
1514 l_author_list := l_author_list ||
1515 l_author_varray(i) || ' ';
1516 END LOOP;
1517 END IF;
1518 -- Get item's file id.
1519 Get_ItemFile
1520 (
1521 p_api_version => p_api_version,
1522 x_return_status => l_return_status,
1523 x_msg_count => x_msg_count,
1524 x_msg_data => x_msg_data,
1525 p_check_login_user => FND_API.G_FALSE,
1526 p_item_id => l_item_id,
1527 x_file_id_varray => l_file_id_varray
1528 );
1529 l_file_id_list := '';
1530 IF (l_file_id_varray IS NOT NULL) THEN
1531 FOR i IN 1..l_file_id_varray.COUNT LOOP
1532 l_file_id_list := l_file_id_list||l_file_id_varray(i)||' ';
1533 END LOOP;
1534 END IF;
1535 x_item_obj_array.extend;
1536 x_item_obj_array(l_fetch_count).item_id := l_item_id;
1537 x_item_obj_array(l_fetch_count).object_version_number := l_OBJECT_VERSION_NUMBER;
1538 x_item_obj_array(l_fetch_count).creation_date := l_CREATION_DATE;
1539 x_item_obj_array(l_fetch_count).created_by := l_CREATED_BY;
1540 x_item_obj_array(l_fetch_count).last_update_date := l_LAST_UPDATE_DATE;
1541 x_item_obj_array(l_fetch_count).last_updated_by := l_LAST_UPDATED_BY;
1542 x_item_obj_array(l_fetch_count).last_update_login := l_LAST_UPDATE_LOGIN;
1543 x_item_obj_array(l_fetch_count).application_id := l_APPLICATION_ID;
1544 x_item_obj_array(l_fetch_count).external_access_flag := l_EXTERNAL_ACCESS;
1545 x_item_obj_array(l_fetch_count).item_name := l_ITEM_NAME;
1546 x_item_obj_array(l_fetch_count).description := l_DESCRIPTION;
1547 x_item_obj_array(l_fetch_count).text_string := l_TEXT_STRING;
1548 x_item_obj_array(l_fetch_count).language_code := l_LANGUAGE_CODE;
1549 x_item_obj_array(l_fetch_count).status_code := l_STATUS_CODE;
1550 x_item_obj_array(l_fetch_count).effective_start_date := l_EFFECTIVE_START_DATE;
1551 x_item_obj_array(l_fetch_count).expiration_date := l_EXPIRATION_DATE;
1552 x_item_obj_array(l_fetch_count).item_type := l_ITEM_TYPE;
1553 x_item_obj_array(l_fetch_count).url_string := l_URL_STRING;
1554 x_item_obj_array(l_fetch_count).publication_date := l_PUBLICATION_DATE;
1555 x_item_obj_array(l_fetch_count).priority := l_PRIORITY;
1556 x_item_obj_array(l_fetch_count).content_type_id := l_CONTENT_TYPE_ID;
1557 x_item_obj_array(l_fetch_count).owner_id := l_OWNER_ID;
1558 x_item_obj_array(l_fetch_count).default_approver_id := l_DEFAULT_APPROVER_ID;
1559 x_item_obj_array(l_fetch_count).item_destination_type := l_ITEM_DESTINATION_TYPE;
1560 x_item_obj_array(l_fetch_count).file_id_list := l_file_id_list;
1561 x_item_obj_array(l_fetch_count).persp_id_list := l_persp_id_list;
1562 x_item_obj_array(l_fetch_count).persp_name_list := l_persp_name_list;
1563 x_item_obj_array(l_fetch_count).author_list := l_author_list;
1564 x_item_obj_array(l_fetch_count).keyword_list := l_keyword_list;
1565
1566 END IF;
1567 IF (l_fetch_count >= p_subset_request_obj.records_requested) THEN
1568 EXIT;
1569 END IF;
1570 l_total_count := l_total_count + 1;
1571 END LOOP;
1572 CLOSE l_cursor;
1573 x_subset_return_obj.returned_record_count := l_fetch_count;
1574 x_subset_return_obj.next_record_position :=
1575 p_subset_request_obj.start_record_position + l_fetch_count;
1576 x_subset_return_obj.total_record_count := l_total_record_count;
1577 --Standard call to get message count and if count=1, get the message
1578 FND_MSG_PUB.Count_And_Get (
1579 p_encoded => FND_API.G_FALSE,
1580 p_count => x_msg_count,
1581 p_data => x_msg_data
1582 );
1583 EXCEPTION
1584 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1586 -- Standard call to get message count and if count=1, get the message
1587 FND_MSG_PUB.Count_And_Get (
1588 p_encoded => FND_API.G_FALSE,
1589 p_count => x_msg_count,
1590 p_data => x_msg_data
1591 );
1592 WHEN FND_API.G_EXC_ERROR THEN
1593 x_return_status := FND_API.G_RET_STS_ERROR;
1594 -- Standard call to get message count and if count=1, get the message
1595 FND_MSG_PUB.Count_And_Get (
1596 p_encoded => FND_API.G_FALSE,
1597 p_count => x_msg_count,
1598 p_data => x_msg_data
1599 );
1600 WHEN OTHERS THEN
1601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1602 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1603 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1604 END IF;
1605 -- Standard call to get message count and if count=1, get the message
1606 FND_MSG_PUB.Count_And_Get (
1607 p_encoded => FND_API.G_FALSE,
1608 p_count => x_msg_count,
1609 p_data => x_msg_data
1610 );
1611 END Find_Item;
1612 --------------------------------------------------------------------------------
1613 ------------------------------ ITEM_KEYWORD ------------------------------------
1614 PROCEDURE Add_ItemKeyword
1615 (
1616 p_api_version IN NUMBER,
1617 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1618 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1619 x_return_status OUT NOCOPY VARCHAR2,
1620 x_msg_count OUT NOCOPY NUMBER,
1621 x_msg_data OUT NOCOPY VARCHAR2,
1622 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1623 p_item_id IN NUMBER,
1624 p_keyword_varray IN AMV_CHAR_VARRAY_TYPE
1625 ) AS
1626 l_api_name CONSTANT VARCHAR2(30) := 'Add_ItemKeyword';
1627 l_api_version CONSTANT NUMBER := 1.0;
1628 l_resource_id NUMBER := -1;
1629 l_current_user_id NUMBER := -1;
1630 l_current_login_id NUMBER := -1;
1631 l_current_user_status VARCHAR2(80);
1632 --
1633 l_admin_flag VARCHAR2(1);
1634 l_char_tab JTF_AMV_ITEM_PUB.CHAR_TAB_TYPE;
1635 --
1636 BEGIN
1637 SAVEPOINT Add_ItemKeyword_Pub;
1638 --Initialize message list if p_init_msg_list is TRUE.
1639 IF FND_API.To_Boolean (p_init_msg_list) THEN
1640 FND_MSG_PUB.initialize;
1641 END IF;
1642 -- Initialize API return status to success
1643 x_return_status := FND_API.G_RET_STS_SUCCESS;
1644 -- Get the current (login) user id.
1645 AMV_UTILITY_PVT.Get_UserInfo(
1646 x_resource_id => l_resource_id,
1647 x_user_id => l_current_user_id,
1648 x_login_id => l_current_login_id,
1649 x_user_status => l_current_user_status
1650 );
1651 IF (p_check_login_user = FND_API.G_TRUE) THEN
1652 -- Check if user is login and has the required privilege.
1653 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1654 -- User is not login.
1655 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1656 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1657 FND_MSG_PUB.ADD;
1658 END IF;
1659 RAISE FND_API.G_EXC_ERROR;
1660 END IF;
1661 AMV_USER_PVT.Is_Administrator
1662 (
1663 p_api_version => 1.0,
1664 x_return_status => x_return_status,
1665 x_msg_count => x_msg_count,
1666 x_msg_data => x_msg_data,
1667 p_check_login_user => FND_API.G_FALSE,
1668 p_resource_id => l_resource_id,
1669 x_result_flag => l_admin_flag
1670 );
1671 IF (l_admin_flag <> FND_API.G_TRUE) THEN
1672 -- User is not an administrator.
1673 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1674 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
1675 FND_MSG_PUB.ADD;
1676 END IF;
1677 RAISE FND_API.G_EXC_ERROR;
1678 END IF;
1679 END IF;
1680 IF (p_keyword_varray IS NOT NULL) THEN
1681 l_char_tab := JTF_AMV_ITEM_PUB.CHAR_TAB_TYPE();
1682 FOR i IN 1..p_keyword_varray.COUNT LOOP
1683 l_char_tab.extend;
1684 l_char_tab(i) := initcap(p_keyword_varray(i));
1685 END LOOP;
1686 JTF_AMV_ITEM_PUB.Add_ItemKeyword
1687 (
1688 p_api_version => p_api_version,
1689 p_init_msg_list => FND_API.G_FALSE,
1690 p_commit => p_commit,
1691 x_return_status => x_return_status,
1692 x_msg_count => x_msg_count,
1693 x_msg_data => x_msg_data,
1694 p_item_id => p_item_id,
1695 p_keyword_tab => l_char_tab
1696 );
1697 ELSE
1698 FND_MSG_PUB.Count_And_Get (
1699 p_encoded => FND_API.G_FALSE,
1700 p_count => x_msg_count,
1701 p_data => x_msg_data
1702 );
1703 END IF;
1704 EXCEPTION
1705 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1706 ROLLBACK TO Add_ItemKeyword_Pub;
1707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1708 -- Standard call to get message count and if count=1, get the message
1709 FND_MSG_PUB.Count_And_Get (
1710 p_encoded => FND_API.G_FALSE,
1711 p_count => x_msg_count,
1712 p_data => x_msg_data
1713 );
1714 WHEN FND_API.G_EXC_ERROR THEN
1715 ROLLBACK TO Add_ItemKeyword_Pub;
1716 x_return_status := FND_API.G_RET_STS_ERROR;
1717 -- Standard call to get message count and if count=1, get the message
1718 FND_MSG_PUB.Count_And_Get (
1719 p_encoded => FND_API.G_FALSE,
1720 p_count => x_msg_count,
1721 p_data => x_msg_data
1722 );
1723 WHEN OTHERS THEN
1724 ROLLBACK TO Add_ItemKeyword_Pub;
1725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1726 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1727 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1728 END IF;
1729 -- Standard call to get message count and if count=1, get the message
1730 FND_MSG_PUB.Count_And_Get (
1731 p_encoded => FND_API.G_FALSE,
1732 p_count => x_msg_count,
1733 p_data => x_msg_data
1734 );
1735 END Add_ItemKeyword;
1736 --------------------------------------------------------------------------------
1737 PROCEDURE Add_ItemKeyword
1738 (
1739 p_api_version IN NUMBER,
1740 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1741 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1742 x_return_status OUT NOCOPY VARCHAR2,
1743 x_msg_count OUT NOCOPY NUMBER,
1744 x_msg_data OUT NOCOPY VARCHAR2,
1745 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1746 p_item_id IN NUMBER,
1747 p_keyword IN VARCHAR2
1748 ) AS
1749 l_char_varray AMV_CHAR_VARRAY_TYPE;
1750 BEGIN
1751 l_char_varray := AMV_CHAR_VARRAY_TYPE();
1752 l_char_varray.extend;
1753 l_char_varray(1) := p_keyword;
1754 --
1755 Add_ItemKeyword
1756 (
1757 p_api_version => p_api_version,
1758 p_init_msg_list => p_init_msg_list,
1759 p_commit => p_commit,
1760 x_return_status => x_return_status,
1761 x_msg_count => x_msg_count,
1762 x_msg_data => x_msg_data,
1763 p_check_login_user => p_check_login_user,
1764 p_item_id => p_item_id,
1765 p_keyword_varray => l_char_varray
1766 );
1767 END Add_ItemKeyword;
1768 --------------------------------------------------------------------------------
1769 PROCEDURE Delete_ItemKeyword
1770 (
1771 p_api_version IN NUMBER,
1772 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1773 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1774 x_return_status OUT NOCOPY VARCHAR2,
1775 x_msg_count OUT NOCOPY NUMBER,
1776 x_msg_data OUT NOCOPY VARCHAR2,
1777 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1778 p_item_id IN NUMBER,
1779 p_keyword_varray IN AMV_CHAR_VARRAY_TYPE
1780 ) AS
1781 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ItemKeyword';
1782 l_api_version CONSTANT NUMBER := 1.0;
1783 l_resource_id NUMBER := -1;
1784 l_current_user_id NUMBER := -1;
1785 l_current_login_id NUMBER := -1;
1786 l_current_user_status VARCHAR2(80);
1787 --
1788 l_admin_flag VARCHAR2(1);
1789 l_char_tab JTF_AMV_ITEM_PUB.CHAR_TAB_TYPE;
1790 BEGIN
1791 -- Standard call to check for call compatibility.
1792 SAVEPOINT Delete_ItemKeyword_Pub;
1793 --Initialize message list if p_init_msg_list is TRUE.
1794 IF FND_API.To_Boolean (p_init_msg_list) THEN
1795 FND_MSG_PUB.initialize;
1796 END IF;
1797 -- Initialize API return status to success
1798 x_return_status := FND_API.G_RET_STS_SUCCESS;
1799 -- Get the current (login) user id.
1800 AMV_UTILITY_PVT.Get_UserInfo(
1801 x_resource_id => l_resource_id,
1802 x_user_id => l_current_user_id,
1803 x_login_id => l_current_login_id,
1804 x_user_status => l_current_user_status
1805 );
1806 IF (p_check_login_user = FND_API.G_TRUE) THEN
1807 -- Check if user is login and has the required privilege.
1808 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1809 -- User is not login.
1810 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1811 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1812 FND_MSG_PUB.ADD;
1813 END IF;
1814 RAISE FND_API.G_EXC_ERROR;
1815 END IF;
1816 AMV_USER_PVT.Is_Administrator
1817 (
1818 p_api_version => 1.0,
1819 x_return_status => x_return_status,
1820 x_msg_count => x_msg_count,
1821 x_msg_data => x_msg_data,
1822 p_check_login_user => FND_API.G_FALSE,
1823 p_resource_id => l_resource_id,
1824 x_result_flag => l_admin_flag
1825 );
1826 IF (l_admin_flag <> FND_API.G_TRUE) THEN
1827 -- User is not an administrator.
1828 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1829 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
1830 FND_MSG_PUB.ADD;
1831 END IF;
1832 RAISE FND_API.G_EXC_ERROR;
1833 END IF;
1834 END IF;
1835 --
1836 IF (p_keyword_varray IS NULL) THEN
1837 l_char_tab := NULL;
1838 ELSE
1839 l_char_tab := JTF_AMV_ITEM_PUB.CHAR_TAB_TYPE();
1840 FOR i IN 1..p_keyword_varray.COUNT LOOP
1841 l_char_tab.extend;
1842 l_char_tab(i) := p_keyword_varray(i);
1843 END LOOP;
1844 END IF;
1845 -- Now call jtf procedure to do the job.
1846 JTF_AMV_ITEM_PUB.Delete_ItemKeyword
1847 (
1848 p_api_version => p_api_version,
1849 p_init_msg_list => FND_API.G_FALSE,
1850 p_commit => p_commit,
1851 x_return_status => x_return_status,
1852 x_msg_count => x_msg_count,
1853 x_msg_data => x_msg_data,
1854 p_item_id => p_item_id,
1855 p_keyword_tab => l_char_tab
1856 );
1857 EXCEPTION
1858 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1859 ROLLBACK TO Delete_ItemKeyword_Pub;
1860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1861 -- Standard call to get message count and if count=1, get the message
1862 FND_MSG_PUB.Count_And_Get (
1863 p_encoded => FND_API.G_FALSE,
1864 p_count => x_msg_count,
1865 p_data => x_msg_data
1866 );
1867 WHEN FND_API.G_EXC_ERROR THEN
1868 ROLLBACK TO Delete_ItemKeyword_Pub;
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870 -- Standard call to get message count and if count=1, get the message
1871 FND_MSG_PUB.Count_And_Get (
1872 p_encoded => FND_API.G_FALSE,
1873 p_count => x_msg_count,
1874 p_data => x_msg_data
1875 );
1876 WHEN OTHERS THEN
1877 ROLLBACK TO Delete_ItemKeyword_Pub;
1878 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1879 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1880 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1881 END IF;
1882 -- Standard call to get message count and if count=1, get the message
1883 FND_MSG_PUB.Count_And_Get (
1884 p_encoded => FND_API.G_FALSE,
1885 p_count => x_msg_count,
1886 p_data => x_msg_data
1887 );
1888 END Delete_ItemKeyword;
1889 --------------------------------------------------------------------------------
1890 PROCEDURE Delete_ItemKeyword
1891 (
1892 p_api_version IN NUMBER,
1893 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1894 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1895 x_return_status OUT NOCOPY VARCHAR2,
1896 x_msg_count OUT NOCOPY NUMBER,
1897 x_msg_data OUT NOCOPY VARCHAR2,
1898 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1899 p_item_id IN NUMBER,
1900 p_keyword IN VARCHAR2
1901 ) AS
1902 l_char_varray AMV_CHAR_VARRAY_TYPE;
1903 BEGIN
1904 l_char_varray := AMV_CHAR_VARRAY_TYPE();
1905 l_char_varray.extend;
1906 l_char_varray(1) := p_keyword;
1907 --
1908 Delete_ItemKeyword
1909 (
1910 p_api_version => p_api_version,
1911 p_init_msg_list => p_init_msg_list,
1912 p_commit => p_commit,
1913 x_return_status => x_return_status,
1914 x_msg_count => x_msg_count,
1915 x_msg_data => x_msg_data,
1916 p_check_login_user => p_check_login_user,
1917 p_item_id => p_item_id,
1918 p_keyword_varray => l_char_varray
1919 );
1920 END Delete_ItemKeyword;
1921 --------------------------------------------------------------------------------
1922 PROCEDURE Replace_ItemKeyword
1923 (
1924 p_api_version IN NUMBER,
1925 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1926 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1927 x_return_status OUT NOCOPY VARCHAR2,
1928 x_msg_count OUT NOCOPY NUMBER,
1929 x_msg_data OUT NOCOPY VARCHAR2,
1930 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1931 p_item_id IN NUMBER,
1932 p_keyword_varray IN AMV_CHAR_VARRAY_TYPE
1933 ) AS
1934 l_api_name CONSTANT VARCHAR2(30) := 'Replace_ItemKeyword';
1935 l_api_version CONSTANT NUMBER := 1.0;
1936 l_resource_id NUMBER := -1;
1937 l_current_user_id NUMBER := -1;
1938 l_current_login_id NUMBER := -1;
1939 l_current_user_status VARCHAR2(80);
1940 --
1941 l_admin_flag VARCHAR2(1);
1942 BEGIN
1943 -- Standard call to check for call compatibility.
1944 SAVEPOINT Replace_ItemKeyword_Pub;
1945 IF NOT FND_API.Compatible_API_Call (
1946 l_api_version,
1947 p_api_version,
1948 l_api_name,
1949 G_PKG_NAME) THEN
1950 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1951 END IF;
1952 --Initialize message list if p_init_msg_list is TRUE.
1953 IF FND_API.To_Boolean (p_init_msg_list) THEN
1954 FND_MSG_PUB.initialize;
1955 END IF;
1956 -- Initialize API return status to success
1957 x_return_status := FND_API.G_RET_STS_SUCCESS;
1958 -- Get the current (login) user id.
1959 AMV_UTILITY_PVT.Get_UserInfo(
1960 x_resource_id => l_resource_id,
1961 x_user_id => l_current_user_id,
1962 x_login_id => l_current_login_id,
1963 x_user_status => l_current_user_status
1964 );
1965 IF (p_check_login_user = FND_API.G_TRUE) THEN
1966 -- Check if user is login and has the required privilege.
1967 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1968 -- User is not login.
1969 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1970 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1971 FND_MSG_PUB.ADD;
1972 END IF;
1973 RAISE FND_API.G_EXC_ERROR;
1974 END IF;
1975 AMV_USER_PVT.Is_Administrator
1976 (
1977 p_api_version => 1.0,
1978 x_return_status => x_return_status,
1979 x_msg_count => x_msg_count,
1980 x_msg_data => x_msg_data,
1981 p_check_login_user => FND_API.G_FALSE,
1982 p_resource_id => l_resource_id,
1983 x_result_flag => l_admin_flag
1984 );
1985 IF (l_admin_flag <> FND_API.G_TRUE) THEN
1986 -- User is not an administrator.
1987 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1988 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
1989 FND_MSG_PUB.ADD;
1990 END IF;
1991 RAISE FND_API.G_EXC_ERROR;
1992 END IF;
1993 END IF;
1994 -- Delete all the item's original keyword
1995 JTF_AMV_ITEM_PUB.Delete_ItemKeyword
1996 (
1997 p_api_version => p_api_version,
1998 p_init_msg_list => FND_API.G_FALSE,
1999 p_commit => FND_API.G_FALSE,
2000 x_return_status => x_return_status,
2001 x_msg_count => x_msg_count,
2002 x_msg_data => x_msg_data,
2003 p_item_id => p_item_id,
2004 p_keyword_tab => NULL
2005 );
2006 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2008 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2010 END IF;
2011 -- now add the new keywords
2012 Add_ItemKeyword
2013 (
2014 p_api_version => p_api_version,
2015 p_init_msg_list => FND_API.G_FALSE,
2016 p_commit => p_commit,
2017 x_return_status => x_return_status,
2018 x_msg_count => x_msg_count,
2019 x_msg_data => x_msg_data,
2020 p_check_login_user => FND_API.G_FALSE,
2021 p_item_id => p_item_id,
2022 p_keyword_varray => p_keyword_varray
2023 );
2024 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2026 ELSIF ( x_return_status = FND_API.G_RET_STS_ERROR) THEN
2027 RAISE FND_API.G_EXC_ERROR;
2028 END IF;
2029 EXCEPTION
2030 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2031 ROLLBACK TO Replace_ItemKeyword_Pub;
2032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2033 -- Standard call to get message count and if count=1, get the message
2034 FND_MSG_PUB.Count_And_Get (
2035 p_encoded => FND_API.G_FALSE,
2036 p_count => x_msg_count,
2037 p_data => x_msg_data
2038 );
2039 WHEN FND_API.G_EXC_ERROR THEN
2040 ROLLBACK TO Replace_ItemKeyword_Pub;
2041 x_return_status := FND_API.G_RET_STS_ERROR;
2042 -- Standard call to get message count and if count=1, get the message
2043 FND_MSG_PUB.Count_And_Get (
2044 p_encoded => FND_API.G_FALSE,
2045 p_count => x_msg_count,
2046 p_data => x_msg_data
2047 );
2048 WHEN OTHERS THEN
2049 ROLLBACK TO Replace_ItemKeyword_Pub;
2050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2051 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2052 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2053 END IF;
2054 -- Standard call to get message count and if count=1, get the message
2055 FND_MSG_PUB.Count_And_Get (
2056 p_encoded => FND_API.G_FALSE,
2057 p_count => x_msg_count,
2058 p_data => x_msg_data
2059 );
2060 END Replace_ItemKeyword;
2061 --------------------------------------------------------------------------------
2062 PROCEDURE Get_ItemKeyword
2063 (
2064 p_api_version IN NUMBER,
2065 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2066 x_return_status OUT NOCOPY VARCHAR2,
2067 x_msg_count OUT NOCOPY NUMBER,
2068 x_msg_data OUT NOCOPY VARCHAR2,
2069 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2070 p_item_id IN NUMBER,
2071 x_keyword_varray OUT NOCOPY AMV_CHAR_VARRAY_TYPE
2072 ) AS
2073 l_api_name CONSTANT VARCHAR2(30) := 'Get_ItemKeyword';
2074 l_api_version CONSTANT NUMBER := 1.0;
2075 l_resource_id NUMBER := -1;
2076 l_current_user_id NUMBER := -1;
2077 l_current_login_id NUMBER := -1;
2078 l_current_user_status VARCHAR2(80);
2079 --
2080 l_char_tab JTF_AMV_ITEM_PUB.CHAR_TAB_TYPE;
2081 --
2082 BEGIN
2083 --Initialize message list if p_init_msg_list is TRUE.
2084 IF FND_API.To_Boolean (p_init_msg_list) THEN
2085 FND_MSG_PUB.initialize;
2086 END IF;
2087 -- Initialize API return status to success
2088 x_return_status := FND_API.G_RET_STS_SUCCESS;
2089 -- Get the current (login) user id.
2090 AMV_UTILITY_PVT.Get_UserInfo(
2091 x_resource_id => l_resource_id,
2092 x_user_id => l_current_user_id,
2093 x_login_id => l_current_login_id,
2094 x_user_status => l_current_user_status
2095 );
2096 IF (p_check_login_user = FND_API.G_TRUE) THEN
2097 -- Check if user is login and has the required privilege.
2098 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2099 -- User is not login.
2100 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2101 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2102 FND_MSG_PUB.ADD;
2103 END IF;
2104 RAISE FND_API.G_EXC_ERROR;
2105 END IF;
2106 END IF;
2107 -- Now call jtf procedure to do the job.
2108 JTF_AMV_ITEM_PUB.Get_ItemKeyword
2109 (
2110 p_api_version => p_api_version,
2111 p_init_msg_list => FND_API.G_FALSE,
2112 x_return_status => x_return_status,
2113 x_msg_count => x_msg_count,
2114 x_msg_data => x_msg_data,
2115 p_item_id => p_item_id,
2116 x_keyword_tab => l_char_tab
2117 );
2118 -- Get back the result in the OUT parameters.
2119 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2120 x_keyword_varray := AMV_CHAR_VARRAY_TYPE();
2121 FOR i IN 1..l_char_tab.COUNT LOOP
2122 x_keyword_varray.extend;
2123 x_keyword_varray(i) := l_char_tab(i);
2124 END LOOP;
2125 END IF;
2126 EXCEPTION
2127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2129 -- Standard call to get message count and if count=1, get the message
2130 FND_MSG_PUB.Count_And_Get (
2131 p_encoded => FND_API.G_FALSE,
2132 p_count => x_msg_count,
2133 p_data => x_msg_data
2134 );
2135 WHEN FND_API.G_EXC_ERROR THEN
2136 x_return_status := FND_API.G_RET_STS_ERROR;
2137 -- Standard call to get message count and if count=1, get the message
2138 FND_MSG_PUB.Count_And_Get (
2139 p_encoded => FND_API.G_FALSE,
2140 p_count => x_msg_count,
2141 p_data => x_msg_data
2142 );
2143 WHEN OTHERS THEN
2144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2145 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2146 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2147 END IF;
2148 -- Standard call to get message count and if count=1, get the message
2149 FND_MSG_PUB.Count_And_Get (
2150 p_encoded => FND_API.G_FALSE,
2151 p_count => x_msg_count,
2152 p_data => x_msg_data
2153 );
2154 END Get_ItemKeyword;
2155 --------------------------------------------------------------------------------
2156 ------------------------------ ITEM_AUTHOR -------------------------------------
2157 PROCEDURE Add_ItemAuthor
2158 (
2159 p_api_version IN NUMBER,
2160 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2161 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2162 x_return_status OUT NOCOPY VARCHAR2,
2163 x_msg_count OUT NOCOPY NUMBER,
2164 x_msg_data OUT NOCOPY VARCHAR2,
2165 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2166 p_item_id IN NUMBER,
2167 p_author_varray IN AMV_CHAR_VARRAY_TYPE
2168 ) AS
2169 l_api_name CONSTANT VARCHAR2(30) := 'Add_ItemAuthor';
2170 l_api_version CONSTANT NUMBER := 1.0;
2171 l_resource_id NUMBER := -1;
2172 l_current_user_id NUMBER := -1;
2173 l_current_login_id NUMBER := -1;
2174 l_current_user_status VARCHAR2(80);
2175 --
2176 l_admin_flag VARCHAR2(1);
2177 l_char_tab JTF_AMV_ITEM_PUB.CHAR_TAB_TYPE;
2178 --
2179 BEGIN
2180 -- Standard call to check for call compatibility.
2181 SAVEPOINT Add_ItemAuthor_Pub;
2182 --Initialize message list if p_init_msg_list is TRUE.
2183 IF FND_API.To_Boolean (p_init_msg_list) THEN
2184 FND_MSG_PUB.initialize;
2185 END IF;
2186 -- Initialize API return status to success
2187 x_return_status := FND_API.G_RET_STS_SUCCESS;
2188 -- Get the current (login) user id.
2189 AMV_UTILITY_PVT.Get_UserInfo(
2190 x_resource_id => l_resource_id,
2191 x_user_id => l_current_user_id,
2192 x_login_id => l_current_login_id,
2193 x_user_status => l_current_user_status
2194 );
2195 IF (p_check_login_user = FND_API.G_TRUE) THEN
2196 -- Check if user is login and has the required privilege.
2197 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2198 -- User is not login.
2199 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2200 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2201 FND_MSG_PUB.ADD;
2202 END IF;
2203 RAISE FND_API.G_EXC_ERROR;
2204 END IF;
2205 AMV_USER_PVT.Is_Administrator
2206 (
2207 p_api_version => 1.0,
2208 x_return_status => x_return_status,
2209 x_msg_count => x_msg_count,
2210 x_msg_data => x_msg_data,
2211 p_check_login_user => FND_API.G_FALSE,
2212 p_resource_id => l_resource_id,
2213 x_result_flag => l_admin_flag
2214 );
2215 IF (l_admin_flag <> FND_API.G_TRUE) THEN
2216 -- User is not an administrator.
2217 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2218 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
2219 FND_MSG_PUB.ADD;
2220 END IF;
2221 RAISE FND_API.G_EXC_ERROR;
2222 END IF;
2223 END IF;
2224 IF (p_author_varray IS NOT NULL) THEN
2225 l_char_tab := JTF_AMV_ITEM_PUB.CHAR_TAB_TYPE();
2226 FOR i IN 1..p_author_varray.COUNT LOOP
2227 l_char_tab.extend;
2228 l_char_tab(i) := initcap(p_author_varray(i));
2229 END LOOP;
2230 JTF_AMV_ITEM_PUB.Add_ItemAuthor
2231 (
2232 p_api_version => p_api_version,
2233 p_init_msg_list => FND_API.G_FALSE,
2234 p_commit => p_commit,
2235 x_return_status => x_return_status,
2236 x_msg_count => x_msg_count,
2237 x_msg_data => x_msg_data,
2238 p_item_id => p_item_id,
2239 p_author_tab => l_char_tab
2240 );
2241 ELSE
2242 FND_MSG_PUB.Count_And_Get (
2243 p_encoded => FND_API.G_FALSE,
2244 p_count => x_msg_count,
2245 p_data => x_msg_data
2246 );
2247 END IF;
2248 EXCEPTION
2249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2250 ROLLBACK TO Add_ItemAuthor_Pub;
2251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2252 -- Standard call to get message count and if count=1, get the message
2253 FND_MSG_PUB.Count_And_Get (
2254 p_encoded => FND_API.G_FALSE,
2255 p_count => x_msg_count,
2256 p_data => x_msg_data
2257 );
2258 WHEN FND_API.G_EXC_ERROR THEN
2259 ROLLBACK TO Add_ItemAuthor_Pub;
2260 x_return_status := FND_API.G_RET_STS_ERROR;
2261 -- Standard call to get message count and if count=1, get the message
2262 FND_MSG_PUB.Count_And_Get (
2263 p_encoded => FND_API.G_FALSE,
2264 p_count => x_msg_count,
2265 p_data => x_msg_data
2266 );
2267 WHEN OTHERS THEN
2268 ROLLBACK TO Add_ItemAuthor_Pub;
2269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2270 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2271 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2272 END IF;
2273 -- Standard call to get message count and if count=1, get the message
2274 FND_MSG_PUB.Count_And_Get (
2275 p_encoded => FND_API.G_FALSE,
2276 p_count => x_msg_count,
2277 p_data => x_msg_data
2278 );
2279 END Add_ItemAuthor;
2280 --------------------------------------------------------------------------------
2281 PROCEDURE Add_ItemAuthor
2282 (
2283 p_api_version IN NUMBER,
2284 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2285 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2286 x_return_status OUT NOCOPY VARCHAR2,
2287 x_msg_count OUT NOCOPY NUMBER,
2288 x_msg_data OUT NOCOPY VARCHAR2,
2289 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2290 p_item_id IN NUMBER,
2291 p_author IN VARCHAR2
2292 ) AS
2293 l_char_varray AMV_CHAR_VARRAY_TYPE;
2294 BEGIN
2295 l_char_varray := AMV_CHAR_VARRAY_TYPE();
2296 l_char_varray.extend;
2297 l_char_varray(1) := p_author;
2298 --
2299 Add_ItemAuthor
2300 (
2301 p_api_version => p_api_version,
2302 p_init_msg_list => p_init_msg_list,
2303 p_commit => p_commit,
2304 x_return_status => x_return_status,
2305 x_msg_count => x_msg_count,
2306 x_msg_data => x_msg_data,
2307 p_check_login_user => p_check_login_user,
2308 p_item_id => p_item_id,
2309 p_author_varray => l_char_varray
2310 );
2311 END Add_ItemAuthor;
2312 --------------------------------------------------------------------------------
2313 PROCEDURE Delete_ItemAuthor
2314 (
2315 p_api_version IN NUMBER,
2316 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2317 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2318 x_return_status OUT NOCOPY VARCHAR2,
2319 x_msg_count OUT NOCOPY NUMBER,
2320 x_msg_data OUT NOCOPY VARCHAR2,
2321 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2322 p_item_id IN NUMBER,
2323 p_author_varray IN AMV_CHAR_VARRAY_TYPE
2324 ) AS
2325 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ItemAuthor';
2326 l_api_version CONSTANT NUMBER := 1.0;
2327 l_resource_id NUMBER := -1;
2328 l_current_user_id NUMBER := -1;
2329 l_current_login_id NUMBER := -1;
2330 l_current_user_status VARCHAR2(80);
2331 --
2332 l_admin_flag VARCHAR2(1);
2333 l_char_tab JTF_AMV_ITEM_PUB.CHAR_TAB_TYPE;
2334 BEGIN
2335 -- Standard call to check for call compatibility.
2336 SAVEPOINT Delete_ItemAuthor_Pub;
2337 --Initialize message list if p_init_msg_list is TRUE.
2338 IF FND_API.To_Boolean (p_init_msg_list) THEN
2339 FND_MSG_PUB.initialize;
2340 END IF;
2341 -- Initialize API return status to success
2342 x_return_status := FND_API.G_RET_STS_SUCCESS;
2343 -- Get the current (login) user id.
2344 AMV_UTILITY_PVT.Get_UserInfo(
2345 x_resource_id => l_resource_id,
2346 x_user_id => l_current_user_id,
2347 x_login_id => l_current_login_id,
2348 x_user_status => l_current_user_status
2349 );
2350 IF (p_check_login_user = FND_API.G_TRUE) THEN
2351 -- Check if user is login and has the required privilege.
2352 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2353 -- User is not login.
2354 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2355 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2356 FND_MSG_PUB.ADD;
2357 END IF;
2358 RAISE FND_API.G_EXC_ERROR;
2359 END IF;
2360 AMV_USER_PVT.Is_Administrator
2361 (
2362 p_api_version => 1.0,
2363 x_return_status => x_return_status,
2364 x_msg_count => x_msg_count,
2365 x_msg_data => x_msg_data,
2366 p_check_login_user => FND_API.G_FALSE,
2367 p_resource_id => l_resource_id,
2368 x_result_flag => l_admin_flag
2369 );
2370 IF (l_admin_flag <> FND_API.G_TRUE) THEN
2371 -- User is not an administrator.
2372 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2373 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
2374 FND_MSG_PUB.ADD;
2375 END IF;
2376 RAISE FND_API.G_EXC_ERROR;
2377 END IF;
2378 END IF;
2379 IF (p_author_varray IS NULL) THEN
2380 l_char_tab := NULL;
2381 ELSE
2382 l_char_tab := JTF_AMV_ITEM_PUB.CHAR_TAB_TYPE();
2383 FOR i IN 1..p_author_varray.COUNT LOOP
2384 l_char_tab.extend;
2385 l_char_tab(i) := p_author_varray(i);
2386 END LOOP;
2387 END IF;
2388 -- Now call jtf procedure to do the job.
2389 JTF_AMV_ITEM_PUB.Delete_ItemAuthor
2390 (
2391 p_api_version => p_api_version,
2392 p_init_msg_list => FND_API.G_FALSE,
2393 p_commit => p_commit,
2394 x_return_status => x_return_status,
2395 x_msg_count => x_msg_count,
2396 x_msg_data => x_msg_data,
2397 p_item_id => p_item_id,
2398 p_author_tab => l_char_tab
2399 );
2400 EXCEPTION
2401 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2402 ROLLBACK TO Delete_ItemAuthor_Pub;
2403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
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 WHEN FND_API.G_EXC_ERROR THEN
2411 ROLLBACK TO Delete_ItemAuthor_Pub;
2412 x_return_status := FND_API.G_RET_STS_ERROR;
2413 -- Standard call to get message count and if count=1, get the message
2414 FND_MSG_PUB.Count_And_Get (
2415 p_encoded => FND_API.G_FALSE,
2416 p_count => x_msg_count,
2417 p_data => x_msg_data
2418 );
2419 WHEN OTHERS THEN
2420 ROLLBACK TO Delete_ItemAuthor_Pub;
2421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2422 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2423 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2424 END IF;
2425 -- Standard call to get message count and if count=1, get the message
2426 FND_MSG_PUB.Count_And_Get (
2427 p_encoded => FND_API.G_FALSE,
2428 p_count => x_msg_count,
2429 p_data => x_msg_data
2430 );
2431 END Delete_ItemAuthor;
2432 --------------------------------------------------------------------------------
2433 PROCEDURE Delete_ItemAuthor
2434 (
2435 p_api_version IN NUMBER,
2436 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2437 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2438 x_return_status OUT NOCOPY VARCHAR2,
2439 x_msg_count OUT NOCOPY NUMBER,
2440 x_msg_data OUT NOCOPY VARCHAR2,
2441 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2442 p_item_id IN NUMBER,
2443 p_author IN VARCHAR2
2444 ) AS
2445 l_char_varray AMV_CHAR_VARRAY_TYPE;
2446 BEGIN
2447 l_char_varray := AMV_CHAR_VARRAY_TYPE();
2448 l_char_varray.extend;
2449 l_char_varray(1) := p_author;
2450 --
2451 Delete_ItemAuthor
2452 (
2453 p_api_version => p_api_version,
2454 p_init_msg_list => p_init_msg_list,
2455 p_commit => p_commit,
2456 x_return_status => x_return_status,
2457 x_msg_count => x_msg_count,
2458 x_msg_data => x_msg_data,
2459 p_check_login_user => p_check_login_user,
2460 p_item_id => p_item_id,
2461 p_author_varray => l_char_varray
2462 );
2463 END Delete_ItemAuthor;
2464 --------------------------------------------------------------------------------
2465 PROCEDURE Replace_ItemAuthor
2466 (
2467 p_api_version IN NUMBER,
2468 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2469 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2470 x_return_status OUT NOCOPY VARCHAR2,
2471 x_msg_count OUT NOCOPY NUMBER,
2472 x_msg_data OUT NOCOPY VARCHAR2,
2473 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2474 p_item_id IN NUMBER,
2475 p_author_varray IN AMV_CHAR_VARRAY_TYPE
2476 ) AS
2477 l_api_name CONSTANT VARCHAR2(30) := 'Replace_ItemAuthor';
2478 l_api_version CONSTANT NUMBER := 1.0;
2479 l_resource_id NUMBER := -1;
2480 l_current_user_id NUMBER := -1;
2481 l_current_login_id NUMBER := -1;
2482 l_current_user_status VARCHAR2(80);
2483 --
2484 l_admin_flag VARCHAR2(1);
2485 BEGIN
2486 -- Standard call to check for call compatibility.
2487 SAVEPOINT Replace_ItemAuthor_Pub;
2488 IF NOT FND_API.Compatible_API_Call (
2489 l_api_version,
2490 p_api_version,
2491 l_api_name,
2492 G_PKG_NAME) THEN
2493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2494 END IF;
2495 --Initialize message list if p_init_msg_list is TRUE.
2496 IF FND_API.To_Boolean (p_init_msg_list) THEN
2497 FND_MSG_PUB.initialize;
2498 END IF;
2499 -- Initialize API return status to success
2500 x_return_status := FND_API.G_RET_STS_SUCCESS;
2501 -- Get the current (login) user id.
2502 AMV_UTILITY_PVT.Get_UserInfo(
2503 x_resource_id => l_resource_id,
2504 x_user_id => l_current_user_id,
2505 x_login_id => l_current_login_id,
2506 x_user_status => l_current_user_status
2507 );
2508 IF (p_check_login_user = FND_API.G_TRUE) THEN
2509 -- Check if user is login and has the required privilege.
2510 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2511 -- User is not login.
2512 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2513 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2514 FND_MSG_PUB.ADD;
2515 END IF;
2516 RAISE FND_API.G_EXC_ERROR;
2517 END IF;
2518 AMV_USER_PVT.Is_Administrator
2519 (
2520 p_api_version => 1.0,
2521 x_return_status => x_return_status,
2522 x_msg_count => x_msg_count,
2523 x_msg_data => x_msg_data,
2524 p_check_login_user => FND_API.G_FALSE,
2525 p_resource_id => l_resource_id,
2526 x_result_flag => l_admin_flag
2527 );
2528 IF (l_admin_flag <> FND_API.G_TRUE) THEN
2529 -- User is not an administrator.
2530 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2531 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
2532 FND_MSG_PUB.ADD;
2533 END IF;
2534 RAISE FND_API.G_EXC_ERROR;
2535 END IF;
2536 END IF;
2537 -- Check if item id is valid.
2538 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
2539 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2540 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
2541 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
2542 FND_MESSAGE.Set_Token('ID', TO_CHAR(p_item_id));
2543 FND_MSG_PUB.ADD;
2544 END IF;
2545 RAISE FND_API.G_EXC_ERROR;
2546 END IF;
2547 -- Delete all the item's original authors
2548 JTF_AMV_ITEM_PUB.Delete_ItemAuthor
2549 (
2550 p_api_version => p_api_version,
2551 p_init_msg_list => FND_API.G_FALSE,
2552 p_commit => FND_API.G_FALSE,
2553 x_return_status => x_return_status,
2554 x_msg_count => x_msg_count,
2555 x_msg_data => x_msg_data,
2556 p_item_id => p_item_id,
2557 p_author_tab => NULL
2558 );
2559 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2561 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2563 END IF;
2564 -- now add the new authors
2565 Add_ItemAuthor
2566 (
2567 p_api_version => p_api_version,
2568 p_init_msg_list => p_init_msg_list,
2569 p_commit => p_commit,
2570 x_return_status => x_return_status,
2571 x_msg_count => x_msg_count,
2572 x_msg_data => x_msg_data,
2573 p_check_login_user => p_check_login_user,
2574 p_item_id => p_item_id,
2575 p_author_varray => p_author_varray
2576 );
2577 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2578 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2579 ELSIF ( x_return_status = FND_API.G_RET_STS_ERROR) THEN
2580 RAISE FND_API.G_EXC_ERROR;
2581 END IF;
2582 EXCEPTION
2583 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2584 ROLLBACK TO Replace_ItemAuthor_Pub;
2585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2586 -- Standard call to get message count and if count=1, get the message
2587 FND_MSG_PUB.Count_And_Get (
2588 p_encoded => FND_API.G_FALSE,
2589 p_count => x_msg_count,
2590 p_data => x_msg_data
2591 );
2592 WHEN FND_API.G_EXC_ERROR THEN
2593 ROLLBACK TO Replace_ItemAuthor_Pub;
2594 x_return_status := FND_API.G_RET_STS_ERROR;
2595 -- Standard call to get message count and if count=1, get the message
2596 FND_MSG_PUB.Count_And_Get (
2597 p_encoded => FND_API.G_FALSE,
2598 p_count => x_msg_count,
2599 p_data => x_msg_data
2600 );
2601 WHEN OTHERS THEN
2602 ROLLBACK TO Replace_ItemAuthor_Pub;
2603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2604 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2605 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2606 END IF;
2607 -- Standard call to get message count and if count=1, get the message
2608 FND_MSG_PUB.Count_And_Get (
2609 p_encoded => FND_API.G_FALSE,
2610 p_count => x_msg_count,
2611 p_data => x_msg_data
2612 );
2613 END Replace_ItemAuthor;
2614 --------------------------------------------------------------------------------
2615 PROCEDURE Get_ItemAuthor
2616 (
2617 p_api_version IN NUMBER,
2618 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2619 x_return_status OUT NOCOPY VARCHAR2,
2620 x_msg_count OUT NOCOPY NUMBER,
2621 x_msg_data OUT NOCOPY VARCHAR2,
2622 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2623 p_item_id IN NUMBER,
2624 x_author_varray OUT NOCOPY AMV_CHAR_VARRAY_TYPE
2625 ) AS
2626 l_api_name CONSTANT VARCHAR2(30) := 'Get_ItemAuthor';
2627 l_api_version CONSTANT NUMBER := 1.0;
2628 l_resource_id NUMBER := -1;
2629 l_current_user_id NUMBER := -1;
2630 l_current_login_id NUMBER := -1;
2631 l_current_user_status VARCHAR2(80);
2632 --
2633 l_char_tab JTF_AMV_ITEM_PUB.CHAR_TAB_TYPE;
2634 --
2635 BEGIN
2636 --Initialize message list if p_init_msg_list is TRUE.
2637 IF FND_API.To_Boolean (p_init_msg_list) THEN
2638 FND_MSG_PUB.initialize;
2639 END IF;
2640 -- Initialize API return status to success
2641 x_return_status := FND_API.G_RET_STS_SUCCESS;
2642 -- Get the current (login) user id.
2643 AMV_UTILITY_PVT.Get_UserInfo(
2644 x_resource_id => l_resource_id,
2645 x_user_id => l_current_user_id,
2646 x_login_id => l_current_login_id,
2647 x_user_status => l_current_user_status
2648 );
2649 IF (p_check_login_user = FND_API.G_TRUE) THEN
2650 -- Check if user is login and has the required privilege.
2651 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2652 -- User is not login.
2653 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2654 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2655 FND_MSG_PUB.ADD;
2656 END IF;
2657 RAISE FND_API.G_EXC_ERROR;
2658 END IF;
2659 END IF;
2660 -- Now call jtf procedure to do the job.
2661 JTF_AMV_ITEM_PUB.Get_ItemAuthor
2662 (
2663 p_api_version => p_api_version,
2664 p_init_msg_list => FND_API.G_FALSE,
2665 x_return_status => x_return_status,
2666 x_msg_count => x_msg_count,
2667 x_msg_data => x_msg_data,
2668 p_item_id => p_item_id,
2669 x_author_tab => l_char_tab
2670 );
2671 -- Get back the result in the OUT parameters.
2672 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2673 x_author_varray := AMV_CHAR_VARRAY_TYPE();
2674 FOR i IN 1..l_char_tab.COUNT LOOP
2675 x_author_varray.extend;
2676 x_author_varray(i) := l_char_tab(i);
2677 END LOOP;
2678 END IF;
2679 EXCEPTION
2680 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2682 -- Standard call to get message count and if count=1, get the message
2683 FND_MSG_PUB.Count_And_Get (
2684 p_encoded => FND_API.G_FALSE,
2685 p_count => x_msg_count,
2686 p_data => x_msg_data
2687 );
2688 WHEN FND_API.G_EXC_ERROR THEN
2689 x_return_status := FND_API.G_RET_STS_ERROR;
2690 -- Standard call to get message count and if count=1, get the message
2691 FND_MSG_PUB.Count_And_Get (
2692 p_encoded => FND_API.G_FALSE,
2693 p_count => x_msg_count,
2694 p_data => x_msg_data
2695 );
2696 WHEN OTHERS THEN
2697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2698 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2699 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2700 END IF;
2701 -- Standard call to get message count and if count=1, get the message
2702 FND_MSG_PUB.Count_And_Get (
2703 p_encoded => FND_API.G_FALSE,
2704 p_count => x_msg_count,
2705 p_data => x_msg_data
2706 );
2707 END Get_ItemAuthor;
2708 --
2709 --------------------------------------------------------------------------------
2710 ------------------------------ ITEM_FILE ------------------------------------
2711 PROCEDURE Add_ItemFile
2712 (
2713 p_api_version IN NUMBER,
2714 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2715 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2716 x_return_status OUT NOCOPY VARCHAR2,
2717 x_msg_count OUT NOCOPY NUMBER,
2718 x_msg_data OUT NOCOPY VARCHAR2,
2719 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2720 p_application_id IN NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID,
2721 p_item_id IN NUMBER,
2722 p_file_id_varray IN AMV_NUMBER_VARRAY_TYPE
2723 ) AS
2724 l_api_name CONSTANT VARCHAR2(30) := 'Add_ItemFile';
2725 l_api_version CONSTANT NUMBER := 1.0;
2726 l_resource_id NUMBER := -1;
2727 l_current_user_id NUMBER := -1;
2728 l_current_login_id NUMBER := -1;
2729 l_current_user_status VARCHAR2(80);
2730 --
2731 l_admin_flag VARCHAR2(1);
2732 l_count NUMBER;
2733 l_return_status VARCHAR2(1);
2734 l_temp_number NUMBER;
2735 l_language_code VARCHAR2(4);
2736 l_act_attachment_rec JTF_AMV_ATTACHMENT_PUB.ACT_ATTACHMENT_REC_TYPE;
2737 --
2738 CURSOR Get_FileLanguage_csr (p_file_id IN VARCHAR2) IS
2739 SELECT
2740 NVL(language, USERENV('LANG'))
2741 FROM fnd_lobs
2742 WHERE file_id = p_file_id
2743 --And PROGRAM_NAME = 'MES'
2744 --And PROGRAM_TAG = 'MES'
2745 ;
2746 CURSOR Check_Itemfile_csr (p_file_id IN VARCHAR2) IS
2747 SELECT
2748 file_id
2749 FROM jtf_amv_attachments_v
2750 WHERE file_id = p_file_id
2751 AND attachment_used_by_id = p_item_id
2752 AND attachment_used_by = G_USED_BY_ITEM;
2753 --
2754 BEGIN
2755 -- Standard call to check for call compatibility.
2756 SAVEPOINT Add_ItemFile_Pub;
2757 IF NOT FND_API.Compatible_API_Call (
2758 l_api_version,
2759 p_api_version,
2760 l_api_name,
2761 G_PKG_NAME) THEN
2762 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2763 END IF;
2764 --Initialize message list if p_init_msg_list is TRUE.
2765 IF FND_API.To_Boolean (p_init_msg_list) THEN
2766 FND_MSG_PUB.initialize;
2767 END IF;
2768 -- Initialize API return status to success
2769 x_return_status := FND_API.G_RET_STS_SUCCESS;
2770 -- Get the current (login) user id.
2771 AMV_UTILITY_PVT.Get_UserInfo(
2772 x_resource_id => l_resource_id,
2773 x_user_id => l_current_user_id,
2774 x_login_id => l_current_login_id,
2775 x_user_status => l_current_user_status
2776 );
2777 IF (p_check_login_user = FND_API.G_TRUE) THEN
2778 -- Check if user is login and has the required privilege.
2779 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2780 -- User is not login.
2781 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2782 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2783 FND_MSG_PUB.ADD;
2784 END IF;
2785 RAISE FND_API.G_EXC_ERROR;
2786 END IF;
2787 AMV_USER_PVT.Is_Administrator
2788 (
2789 p_api_version => 1.0,
2790 x_return_status => x_return_status,
2791 x_msg_count => x_msg_count,
2792 x_msg_data => x_msg_data,
2793 p_check_login_user => FND_API.G_FALSE,
2794 p_resource_id => l_resource_id,
2795 x_result_flag => l_admin_flag
2796 );
2797 IF (l_admin_flag <> FND_API.G_TRUE) THEN
2798 -- User is not an administrator.
2799 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2800 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
2801 FND_MSG_PUB.ADD;
2802 END IF;
2803 RAISE FND_API.G_EXC_ERROR;
2804 END IF;
2805 END IF;
2806 -- This is to make database happy
2807 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2808 l_current_login_id := -1;
2809 l_current_user_id := -1;
2810 END IF;
2811 -- Check if item id is valid.
2812 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
2813 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2814 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
2815 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
2816 fnd_MESSAGE.Set_Token('ID', TO_CHAR(p_item_id));
2817 FND_MSG_PUB.ADD;
2818 END IF;
2819 RAISE FND_API.G_EXC_ERROR;
2820 END IF;
2821 l_count := p_file_id_varray.COUNT;
2822 FOR i IN 1..l_count LOOP
2823 -- Not only do we get language code, but also check if the file exists.
2824 OPEN Get_FileLanguage_csr( p_file_id_varray(i) );
2825 FETCH Get_FileLanguage_csr INTO l_language_code;
2826 IF (Get_FileLanguage_csr%NOTFOUND) THEN
2827 CLOSE Get_FileLanguage_csr;
2828 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2829 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
2830 FND_MESSAGE.Set_Token('RECORD', 'AMV_FILE_TK', TRUE);
2831 FND_MESSAGE.Set_Token('ID', TO_CHAR(p_file_id_varray(i)) );
2832 FND_MSG_PUB.ADD;
2833 END IF;
2834 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2835 x_return_status := FND_API.G_RET_STS_ERROR;
2836 END IF;
2837 ELSE
2838 CLOSE Get_FileLanguage_csr;
2839
2840 OPEN Check_Itemfile_csr( p_file_id_varray(i) );
2841 FETCH Check_Itemfile_csr INTO l_temp_number;
2842 IF (Check_Itemfile_csr%FOUND) THEN
2843 CLOSE Check_Itemfile_csr;
2844 x_return_status := FND_API.G_RET_STS_ERROR;
2845 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2846 FND_MESSAGE.Set_name('AMV','AMV_ENTITY_HAS_ATTR');
2847 FND_MESSAGE.Set_Token('ENTITY', 'AMV_ITEM_TK', TRUE);
2848 FND_MESSAGE.Set_Token('ENTID', TO_CHAR(p_item_id) );
2849 FND_MESSAGE.Set_Token('ATTRIBUTE', 'AMV_FILE_TK', TRUE);
2850 FND_MESSAGE.Set_Token('ATTRID', p_file_id_varray(i));
2851 FND_MSG_PUB.ADD;
2852 END IF;
2853 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2854 x_return_status := FND_API.G_RET_STS_ERROR;
2855 END IF;
2856 ELSE
2857 CLOSE Check_Itemfile_csr;
2858 l_act_attachment_rec.attachment_id := NULL;
2859 --l_act_attachment_rec.last_update_date := l_current_date;
2860 --l_act_attachment_rec.last_updated_by := l_current_user_id;
2861 --l_act_attachment_rec.creation_date := l_current_date;
2862 --l_act_attachment_rec.created_by := l_current_user_id;
2863 --l_act_attachment_rec.last_update_login := l_current_login_id;
2864 --l_act_attachment_rec.object_version_number := 1;
2865 l_act_attachment_rec.owner_user_id := NULL;
2866 l_act_attachment_rec.attachment_used_by_id := p_item_id;
2867 l_act_attachment_rec.attachment_used_by := G_USED_BY_ITEM;
2868 l_act_attachment_rec.version := NULL;
2869 l_act_attachment_rec.enabled_flag := 'Y';
2870 l_act_attachment_rec.can_fulfill_electronic_flag := 'N';
2871 l_act_attachment_rec.file_id := p_file_id_varray(i);
2872 l_act_attachment_rec.file_name := NULL;
2873 l_act_attachment_rec.file_extension := NULL;
2874 l_act_attachment_rec.keywords := NULL;
2875 l_act_attachment_rec.display_width := NULL;
2876 l_act_attachment_rec.display_height := NULL;
2877 l_act_attachment_rec.display_location := NULL;
2878 l_act_attachment_rec.link_to := NULL;
2879 l_act_attachment_rec.link_url := NULL;
2880 l_act_attachment_rec.send_for_preview_flag := 'N';
2881 l_act_attachment_rec.attachment_type := NULL;
2882 l_act_attachment_rec.language_code := l_language_code;
2883 l_act_attachment_rec.application_id := p_application_id;
2884 l_act_attachment_rec.description := NULL;
2885 l_act_attachment_rec.default_style_sheet := NULL;
2886 l_act_attachment_rec.display_url := NULL;
2887 l_act_attachment_rec.display_rule_id := NULL;
2888 l_act_attachment_rec.display_program := NULL;
2889 l_act_attachment_rec.attribute_category := NULL;
2890 l_act_attachment_rec.attribute1 := NULL;
2891 l_act_attachment_rec.attribute2 := NULL;
2892 l_act_attachment_rec.attribute3 := NULL;
2893 l_act_attachment_rec.attribute4 := NULL;
2894 l_act_attachment_rec.attribute5 := NULL;
2895 l_act_attachment_rec.attribute6 := NULL;
2896 l_act_attachment_rec.attribute7 := NULL;
2897 l_act_attachment_rec.attribute8 := NULL;
2898 l_act_attachment_rec.attribute9 := NULL;
2899 l_act_attachment_rec.attribute10 := NULL;
2900 l_act_attachment_rec.attribute11 := NULL;
2901 l_act_attachment_rec.attribute12 := NULL;
2902 l_act_attachment_rec.attribute13 := NULL;
2903 l_act_attachment_rec.attribute14 := NULL;
2904 l_act_attachment_rec.attribute15 := NULL;
2905 --
2906 jtf_amv_attachment_pub.create_act_attachment
2907 (
2908 p_api_version => p_api_version,
2909 x_return_status => l_return_status,
2910 x_msg_count => x_msg_count,
2911 x_msg_data => x_msg_data,
2912 p_act_attachment_rec => l_act_attachment_rec,
2913 x_act_attachment_id => l_temp_number
2914 );
2915 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2917 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
2918 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2919 x_return_status := FND_API.G_RET_STS_ERROR;
2920 END IF;
2921 END IF;
2922 END IF;
2923 END LOOP;
2924 --Standard check of commit
2925 IF FND_API.To_Boolean ( p_commit ) THEN
2926 COMMIT WORK;
2927 END IF;
2928 --Standard call to get message count and if count=1, get the message
2929 FND_MSG_PUB.Count_And_Get (
2930 p_encoded => FND_API.G_FALSE,
2931 p_count => x_msg_count,
2932 p_data => x_msg_data
2933 );
2934 EXCEPTION
2935 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2936 ROLLBACK TO Add_ItemFile_Pub;
2937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2938 -- Standard call to get message count and if count=1, get the message
2939 FND_MSG_PUB.Count_And_Get (
2940 p_encoded => FND_API.G_FALSE,
2941 p_count => x_msg_count,
2942 p_data => x_msg_data
2943 );
2944 WHEN FND_API.G_EXC_ERROR THEN
2945 ROLLBACK TO Add_ItemFile_Pub;
2946 x_return_status := FND_API.G_RET_STS_ERROR;
2947 -- Standard call to get message count and if count=1, get the message
2948 FND_MSG_PUB.Count_And_Get (
2949 p_encoded => FND_API.G_FALSE,
2950 p_count => x_msg_count,
2951 p_data => x_msg_data
2952 );
2953 WHEN OTHERS THEN
2954 ROLLBACK TO Add_ItemFile_Pub;
2955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2956 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2957 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2958 END IF;
2959 -- Standard call to get message count and if count=1, get the message
2960 FND_MSG_PUB.Count_And_Get (
2961 p_encoded => FND_API.G_FALSE,
2962 p_count => x_msg_count,
2963 p_data => x_msg_data
2964 );
2965 END Add_ItemFile;
2966 --------------------------------------------------------------------------------
2967 PROCEDURE Add_ItemFile
2968 (
2969 p_api_version IN NUMBER,
2970 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2971 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2972 x_return_status OUT NOCOPY VARCHAR2,
2973 x_msg_count OUT NOCOPY NUMBER,
2974 x_msg_data OUT NOCOPY VARCHAR2,
2975 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2976 p_application_id IN NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID,
2977 p_item_id IN NUMBER,
2978 p_file_id IN NUMBER
2979 ) AS
2980 l_number_varray AMV_NUMBER_VARRAY_TYPE;
2981 BEGIN
2982 l_number_varray := AMV_NUMBER_VARRAY_TYPE();
2983 l_number_varray.extend;
2984 l_number_varray(1) := p_file_id;
2985 Add_ItemFile
2986 (
2987 p_api_version => p_api_version,
2988 p_init_msg_list => p_init_msg_list,
2989 p_commit => p_commit,
2990 x_return_status => x_return_status,
2991 x_msg_count => x_msg_count,
2992 x_msg_data => x_msg_data,
2993 p_check_login_user => p_check_login_user,
2994 p_application_id => p_application_id,
2995 p_item_id => p_item_id,
2996 p_file_id_varray => l_number_varray
2997 );
2998 END Add_ItemFile;
2999 --------------------------------------------------------------------------------
3000 PROCEDURE Delete_ItemFile
3001 (
3002 p_api_version IN NUMBER,
3003 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3004 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3005 x_return_status OUT NOCOPY VARCHAR2,
3006 x_msg_count OUT NOCOPY NUMBER,
3007 x_msg_data OUT NOCOPY VARCHAR2,
3008 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3009 p_item_id IN NUMBER,
3010 p_file_id_varray IN AMV_NUMBER_VARRAY_TYPE
3011 ) AS
3012 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ItemFile';
3013 l_api_version CONSTANT NUMBER := 1.0;
3014 l_resource_id NUMBER := -1;
3015 l_current_user_id NUMBER := -1;
3016 l_current_login_id NUMBER := -1;
3017 l_current_user_status VARCHAR2(80);
3018 --
3019 l_admin_flag VARCHAR2(1);
3020 l_count NUMBER;
3021 l_temp_number NUMBER;
3022 l_object_version_number NUMBER;
3023 l_return_status VARCHAR2(1);
3024 --
3025 CURSOR Check_Itemfile_csr (p_file_id IN VARCHAR2) IS
3026 SELECT
3027 attachment_id, object_version_number
3028 FROM jtf_amv_attachments_v
3029 WHERE file_id = p_file_id
3030 AND attachment_used_by_id = p_item_id
3031 AND attachment_used_by = G_USED_BY_ITEM;
3032 --
3033 BEGIN
3034 -- Standard call to check for call compatibility.
3035 SAVEPOINT Delete_ItemFile_Pub;
3036 IF NOT FND_API.Compatible_API_Call (
3037 l_api_version,
3038 p_api_version,
3039 l_api_name,
3040 G_PKG_NAME) THEN
3041 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3042 END IF;
3043 --Initialize message list if p_init_msg_list is TRUE.
3044 IF FND_API.To_Boolean (p_init_msg_list) THEN
3045 FND_MSG_PUB.initialize;
3046 END IF;
3047 -- Initialize API return status to success
3048 x_return_status := FND_API.G_RET_STS_SUCCESS;
3049 -- Get the current (login) user id.
3050 AMV_UTILITY_PVT.Get_UserInfo(
3051 x_resource_id => l_resource_id,
3052 x_user_id => l_current_user_id,
3053 x_login_id => l_current_login_id,
3054 x_user_status => l_current_user_status
3055 );
3056 IF (p_check_login_user = FND_API.G_TRUE) THEN
3057 -- Check if user is login and has the required privilege.
3058 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
3059 -- User is not login.
3060 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3061 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3062 FND_MSG_PUB.ADD;
3063 END IF;
3064 RAISE FND_API.G_EXC_ERROR;
3065 END IF;
3066 AMV_USER_PVT.Is_Administrator
3067 (
3068 p_api_version => 1.0,
3069 x_return_status => x_return_status,
3070 x_msg_count => x_msg_count,
3071 x_msg_data => x_msg_data,
3072 p_check_login_user => FND_API.G_FALSE,
3073 p_resource_id => l_resource_id,
3074 x_result_flag => l_admin_flag
3075 );
3076 IF (l_admin_flag <> FND_API.G_TRUE) THEN
3077 -- User is not an administrator.
3078 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3079 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
3080 FND_MSG_PUB.ADD;
3081 END IF;
3082 RAISE FND_API.G_EXC_ERROR;
3083 END IF;
3084 END IF;
3085 -- Check if item id is valid.
3086 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
3087 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3088 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
3089 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
3090 FND_MESSAGE.Set_Token('ID', TO_CHAR(p_item_id));
3091 FND_MSG_PUB.ADD;
3092 END IF;
3093 RAISE FND_API.G_EXC_ERROR;
3094 END IF;
3095 IF (p_file_id_varray IS NOT NULL) THEN
3096 l_count := p_file_id_varray.COUNT;
3097 FOR i IN 1..l_count LOOP
3098 OPEN Check_ItemFile_csr( p_file_id_varray(i) );
3099 FETCH Check_ItemFile_csr INTO l_temp_number, l_object_version_number;
3100 IF (Check_ItemFile_csr%NOTFOUND) THEN
3101 CLOSE Check_ItemFile_csr;
3102 x_return_status := FND_API.G_RET_STS_ERROR;
3103 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3104 FND_MESSAGE.Set_name('AMV','AMV_ENTITY_HAS_NOT_ATTR');
3105 FND_MESSAGE.Set_Token('ENTITY', 'AMV_ITEM_TK', TRUE);
3106 FND_MESSAGE.Set_Token('ENTID', TO_CHAR(p_item_id) );
3107 FND_MESSAGE.Set_Token('ATTRIBUTE', 'AMV_FILE_TK', TRUE);
3108 FND_MESSAGE.Set_Token('ATTRID',
3109 TO_CHAR( p_file_id_varray(i) ) );
3110 FND_MSG_PUB.ADD;
3111 END IF;
3112 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3113 x_return_status := FND_API.G_RET_STS_ERROR;
3114 END IF;
3115 ELSE
3116 CLOSE Check_ItemFile_csr;
3117 jtf_amv_attachment_pub.delete_act_attachment
3118 (
3119 p_api_version => p_api_version,
3120 x_return_status => l_return_status,
3121 x_msg_count => x_msg_count,
3122 x_msg_data => x_msg_data,
3123 p_act_attachment_id => l_temp_number,
3124 p_object_version => l_object_version_number
3125 );
3126 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3128 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
3129 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3130 x_return_status := FND_API.G_RET_STS_ERROR;
3131 END IF;
3132 END IF;
3133 END LOOP;
3134 ELSE
3135 -- If no file ids specified, delete all the attached file of the item.
3136 DELETE FROM jtf_amv_attachments
3137 WHERE attachment_used_by_id = p_item_id
3138 AND attachment_used_by = G_USED_BY_ITEM;
3139 END IF;
3140 --Standard check of commit
3141 IF FND_API.To_Boolean ( p_commit ) THEN
3142 COMMIT WORK;
3143 END IF;
3144 --Standard call to get message count and if count=1, get the message
3145 FND_MSG_PUB.Count_And_Get (
3146 p_encoded => FND_API.G_FALSE,
3147 p_count => x_msg_count,
3148 p_data => x_msg_data
3149 );
3150 EXCEPTION
3151 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3152 ROLLBACK TO Delete_ItemFile_Pub;
3153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3154 -- Standard call to get message count and if count=1, get the message
3155 FND_MSG_PUB.Count_And_Get (
3156 p_encoded => FND_API.G_FALSE,
3157 p_count => x_msg_count,
3158 p_data => x_msg_data
3159 );
3160 WHEN FND_API.G_EXC_ERROR THEN
3161 ROLLBACK TO Delete_ItemFile_Pub;
3162 x_return_status := FND_API.G_RET_STS_ERROR;
3163 -- Standard call to get message count and if count=1, get the message
3164 FND_MSG_PUB.Count_And_Get (
3165 p_encoded => FND_API.G_FALSE,
3166 p_count => x_msg_count,
3167 p_data => x_msg_data
3168 );
3169 WHEN OTHERS THEN
3170 ROLLBACK TO Delete_ItemFile_Pub;
3171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3172 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3173 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3174 END IF;
3175 -- Standard call to get message count and if count=1, get the message
3176 FND_MSG_PUB.Count_And_Get (
3177 p_encoded => FND_API.G_FALSE,
3178 p_count => x_msg_count,
3179 p_data => x_msg_data
3180 );
3181 END Delete_ItemFile;
3182 --------------------------------------------------------------------------------
3183 PROCEDURE Delete_ItemFile
3184 (
3185 p_api_version IN NUMBER,
3186 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3187 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3188 x_return_status OUT NOCOPY VARCHAR2,
3189 x_msg_count OUT NOCOPY NUMBER,
3190 x_msg_data OUT NOCOPY VARCHAR2,
3191 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3192 p_item_id IN NUMBER,
3193 p_file_id IN NUMBER
3194 ) AS
3195 l_number_varray AMV_NUMBER_VARRAY_TYPE;
3196 BEGIN
3197 l_number_varray := AMV_NUMBER_VARRAY_TYPE();
3198 l_number_varray.extend;
3199 l_number_varray(1) := p_file_id;
3200 --
3201 Delete_ItemFile
3202 (
3203 p_api_version => p_api_version,
3204 p_init_msg_list => p_init_msg_list,
3205 p_commit => p_commit,
3206 x_return_status => x_return_status,
3207 x_msg_count => x_msg_count,
3208 x_msg_data => x_msg_data,
3209 p_check_login_user => p_check_login_user,
3210 p_item_id => p_item_id,
3211 p_file_id_varray => l_number_varray
3212 );
3213 END Delete_ItemFile;
3214 --------------------------------------------------------------------------------
3215 PROCEDURE Replace_ItemFile
3216 (
3217 p_api_version IN NUMBER,
3218 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3219 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3220 x_return_status OUT NOCOPY VARCHAR2,
3221 x_msg_count OUT NOCOPY NUMBER,
3222 x_msg_data OUT NOCOPY VARCHAR2,
3223 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3224 p_item_id IN NUMBER,
3225 p_file_id_varray IN AMV_NUMBER_VARRAY_TYPE
3226 ) AS
3227 l_api_name CONSTANT VARCHAR2(30) := 'Replace_ItemFile';
3228 l_api_version CONSTANT NUMBER := 1.0;
3229 l_resource_id NUMBER := -1;
3230 l_current_user_id NUMBER := -1;
3231 l_current_login_id NUMBER := -1;
3232 l_current_user_status VARCHAR2(80);
3233 --
3234 l_admin_flag VARCHAR2(1);
3235 l_count NUMBER;
3236 l_temp_number NUMBER;
3237 --
3238 BEGIN
3239 -- Standard call to check for call compatibility.
3240 SAVEPOINT Replace_ItemFile_Pub;
3241 IF NOT FND_API.Compatible_API_Call (
3242 l_api_version,
3243 p_api_version,
3244 l_api_name,
3245 G_PKG_NAME) THEN
3246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3247 END IF;
3248 --Initialize message list if p_init_msg_list is TRUE.
3249 IF FND_API.To_Boolean (p_init_msg_list) THEN
3250 FND_MSG_PUB.initialize;
3251 END IF;
3252 -- Initialize API return status to success
3253 x_return_status := FND_API.G_RET_STS_SUCCESS;
3254 -- Get the current (login) user id.
3255 AMV_UTILITY_PVT.Get_UserInfo(
3256 x_resource_id => l_resource_id,
3257 x_user_id => l_current_user_id,
3258 x_login_id => l_current_login_id,
3259 x_user_status => l_current_user_status
3260 );
3261 IF (p_check_login_user = FND_API.G_TRUE) THEN
3262 -- Check if user is login and has the required privilege.
3263 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
3264 -- User is not login.
3265 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3266 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3267 FND_MSG_PUB.ADD;
3268 END IF;
3269 RAISE FND_API.G_EXC_ERROR;
3270 END IF;
3271 AMV_USER_PVT.Is_Administrator
3272 (
3273 p_api_version => 1.0,
3274 x_return_status => x_return_status,
3275 x_msg_count => x_msg_count,
3276 x_msg_data => x_msg_data,
3277 p_check_login_user => FND_API.G_FALSE,
3278 p_resource_id => l_resource_id,
3279 x_result_flag => l_admin_flag
3280 );
3281 IF (l_admin_flag <> FND_API.G_TRUE) THEN
3282 -- User is not an administrator.
3283 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3284 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
3285 FND_MSG_PUB.ADD;
3286 END IF;
3287 RAISE FND_API.G_EXC_ERROR;
3288 END IF;
3289 END IF;
3290 -- Check if item id is valid.
3291 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
3292 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3293 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
3294 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
3295 FND_MESSAGE.Set_Token('ID', TO_CHAR(p_item_id));
3296 FND_MSG_PUB.ADD;
3297 END IF;
3298 RAISE FND_API.G_EXC_ERROR;
3299 END IF;
3300 -- Delete all the item's original files
3301 Delete_ItemFile
3302 (
3303 p_api_version => p_api_version,
3304 p_init_msg_list => p_init_msg_list,
3305 x_return_status => x_return_status,
3306 x_msg_count => x_msg_count,
3307 x_msg_data => x_msg_data,
3308 p_check_login_user => p_check_login_user,
3309 p_item_id => p_item_id,
3310 p_file_id_varray => NULL
3311 );
3312 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3313 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3314 ELSIF ( x_return_status = FND_API.G_RET_STS_ERROR) THEN
3315 RAISE FND_API.G_EXC_ERROR;
3316 END IF;
3317 -- now add (attach) the new files
3318 Add_ItemFile
3319 (
3320 p_api_version => p_api_version,
3321 p_commit => p_commit,
3322 x_return_status => x_return_status,
3323 x_msg_count => x_msg_count,
3324 x_msg_data => x_msg_data,
3325 p_check_login_user => p_check_login_user,
3326 p_item_id => p_item_id,
3327 p_file_id_varray => p_file_id_varray
3328 );
3329 EXCEPTION
3330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3331 ROLLBACK TO Replace_ItemFile_Pub;
3332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3333 -- Standard call to get message count and if count=1, get the message
3334 FND_MSG_PUB.Count_And_Get (
3335 p_encoded => FND_API.G_FALSE,
3336 p_count => x_msg_count,
3337 p_data => x_msg_data
3338 );
3339 WHEN FND_API.G_EXC_ERROR THEN
3340 ROLLBACK TO Replace_ItemFile_Pub;
3341 x_return_status := FND_API.G_RET_STS_ERROR;
3342 -- Standard call to get message count and if count=1, get the message
3343 FND_MSG_PUB.Count_And_Get (
3344 p_encoded => FND_API.G_FALSE,
3345 p_count => x_msg_count,
3346 p_data => x_msg_data
3347 );
3348 WHEN OTHERS THEN
3349 ROLLBACK TO Replace_ItemFile_Pub;
3350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3351 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3352 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
3353 END IF;
3354 -- Standard call to get message count and if count=1, get the message
3355 FND_MSG_PUB.Count_And_Get (
3356 p_encoded => FND_API.G_FALSE,
3357 p_count => x_msg_count,
3358 p_data => x_msg_data
3359 );
3360 END Replace_ItemFile;
3361 --------------------------------------------------------------------------------
3362 PROCEDURE Get_ItemFile
3363 (
3364 p_api_version IN NUMBER,
3365 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3366 x_return_status OUT NOCOPY VARCHAR2,
3367 x_msg_count OUT NOCOPY NUMBER,
3368 x_msg_data OUT NOCOPY VARCHAR2,
3369 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3370 p_item_id IN NUMBER,
3371 x_file_id_varray OUT NOCOPY AMV_NUMBER_VARRAY_TYPE
3372 ) AS
3373 l_api_name CONSTANT VARCHAR2(30) := 'Get_ItemFile';
3374 l_api_version CONSTANT NUMBER := 1.0;
3375 l_resource_id NUMBER := -1;
3376 l_current_user_id NUMBER := -1;
3377 l_current_login_id NUMBER := -1;
3378 l_current_user_status VARCHAR2(80);
3379 --
3380 l_fetch_count NUMBER := 0;
3381 CURSOR Get_File_id_csr IS
3382 SELECT
3383 File_id
3384 FROM jtf_amv_attachments_v
3385 WHERE attachment_used_by_id = p_item_id
3386 AND attachment_used_by = G_USED_BY_ITEM;
3387 --
3388 BEGIN
3389 -- Standard call to check for call compatibility.
3390 IF NOT FND_API.Compatible_API_Call (
3391 l_api_version,
3392 p_api_version,
3393 l_api_name,
3394 G_PKG_NAME) THEN
3395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3396 END IF;
3397 --Initialize message list if p_init_msg_list is TRUE.
3398 IF FND_API.To_Boolean (p_init_msg_list) THEN
3399 FND_MSG_PUB.initialize;
3400 END IF;
3401 -- Initialize API return status to success
3402 x_return_status := FND_API.G_RET_STS_SUCCESS;
3403 -- Get the current (login) user id.
3404 AMV_UTILITY_PVT.Get_UserInfo(
3405 x_resource_id => l_resource_id,
3406 x_user_id => l_current_user_id,
3407 x_login_id => l_current_login_id,
3408 x_user_status => l_current_user_status
3409 );
3410 IF (p_check_login_user = FND_API.G_TRUE) THEN
3411 -- Check if user is login and has the required privilege.
3412 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
3413 -- User is not login.
3414 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3415 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3416 FND_MSG_PUB.ADD;
3417 END IF;
3418 RAISE FND_API.G_EXC_ERROR;
3419 END IF;
3420 END IF;
3421 -- Check if item id is valid.
3422 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
3423 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3424 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
3425 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
3426 FND_MESSAGE.Set_Token('ID', TO_CHAR(p_item_id));
3427 FND_MSG_PUB.ADD;
3428 END IF;
3429 RAISE FND_API.G_EXC_ERROR;
3430 END IF;
3431 --Execute the SQL statements to get records
3432 x_file_id_varray := AMV_NUMBER_VARRAY_TYPE();
3433 FOR rec IN Get_File_id_csr LOOP
3434 l_fetch_count := l_fetch_count + 1;
3435 x_file_id_varray.extend;
3436 x_file_id_varray(l_fetch_count) := rec.file_id;
3437 END LOOP;
3438 --Standard call to get message count and if count=1, get the message
3439 FND_MSG_PUB.Count_And_Get (
3440 p_encoded => FND_API.G_FALSE,
3441 p_count => x_msg_count,
3442 p_data => x_msg_data
3443 );
3444 EXCEPTION
3445 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3446 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3447 -- Standard call to get message count and if count=1, get the message
3448 FND_MSG_PUB.Count_And_Get (
3449 p_encoded => FND_API.G_FALSE,
3450 p_count => x_msg_count,
3451 p_data => x_msg_data
3452 );
3453 WHEN FND_API.G_EXC_ERROR THEN
3454 x_return_status := FND_API.G_RET_STS_ERROR;
3455 -- Standard call to get message count and if count=1, get the message
3456 FND_MSG_PUB.Count_And_Get (
3457 p_encoded => FND_API.G_FALSE,
3458 p_count => x_msg_count,
3459 p_data => x_msg_data
3460 );
3461 WHEN OTHERS THEN
3462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3463 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3464 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3465 END IF;
3466 -- Standard call to get message count and if count=1, get the message
3467 FND_MSG_PUB.Count_And_Get (
3468 p_encoded => FND_API.G_FALSE,
3469 p_count => x_msg_count,
3470 p_data => x_msg_data
3471 );
3472 END Get_ItemFile;
3473 --
3474 --------------------------------------------------------------------------------
3475 --------------------------------------------------------------------------------
3476 PROCEDURE Get_UserMessage
3477 (
3478 p_api_version IN NUMBER,
3479 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3480 x_return_status OUT NOCOPY VARCHAR2,
3481 x_msg_count OUT NOCOPY NUMBER,
3482 x_msg_data OUT NOCOPY VARCHAR2,
3483 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3484 p_user_id IN NUMBER,
3485 x_item_id_varray OUT NOCOPY AMV_NUMBER_VARRAY_TYPE,
3486 x_message_varray OUT NOCOPY AMV_CHAR_VARRAY_TYPE
3487 ) AS
3488 l_api_name CONSTANT VARCHAR2(30) := 'Get_UserMessage';
3489 l_api_version CONSTANT NUMBER := 1.0;
3490 l_resource_id NUMBER := -1;
3491 l_current_user_id NUMBER := -1;
3492 l_current_login_id NUMBER := -1;
3493 l_current_user_status VARCHAR2(80);
3494 --
3495 l_count NUMBER;
3496 --
3497 CURSOR Get_Message_csr IS
3498 SELECT
3499 item.item_id,
3500 item.item_name
3501 FROM jtf_amv_items_vl item, amv_c_chl_item_match match,
3502 amv_u_my_channels mych
3503 WHERE item.item_type = 'MESSAGE_ITEM'
3504 AND item.item_id = match.item_id
3505 AND match.table_name_code = 'ITEM'
3506 AND match.approval_status_type = 'APPROVED'
3507 AND match.channel_id = mych.subscribing_to_id
3508 AND mych.subscribing_to_type = 'CHANNEL'
3509 AND mych.subscription_reason_type = 'ENFORCED'
3510 AND mych.user_or_group_type = 'USER'
3511 AND mych.user_or_group_id = p_user_id
3512 UNION
3513 SELECT
3514 item.item_id,
3515 item.item_name
3516 FROM jtf_amv_items_vl item, amv_c_chl_item_match match,
3517 amv_u_my_channels mych, jtf_rs_group_members mem,
3518 jtf_rs_groups_vl g
3519 WHERE item.item_type = 'MESSAGE_ITEM'
3520 AND item.item_id = match.item_id
3521 AND match.table_name_code = 'ITEM'
3522 AND match.approval_status_type = 'APPROVED'
3523 AND match.channel_id = mych.subscribing_to_id
3524 AND mych.user_or_group_type = 'GROUP'
3525 AND mych.subscribing_to_type = 'CHANNEL'
3526 AND mych.subscription_reason_type = 'ENFORCED'
3527 AND mych.user_or_group_id = mem.group_id
3528 AND mem.delete_flag <> 'Y'
3529 AND mem.resource_id = p_user_id
3530 AND mem.group_id = g.group_id
3531 AND g.start_date_active <= SYSDATE
3532 AND NVL(g.end_date_active, SYSDATE+1) > SYSDATE
3533 ;
3534 --
3535 BEGIN
3536 -- Standard call to check for call compatibility.
3537 IF NOT FND_API.Compatible_API_Call (
3538 l_api_version,
3539 p_api_version,
3540 l_api_name,
3541 G_PKG_NAME) THEN
3542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3543 END IF;
3544 --Initialize message list if p_init_msg_list is TRUE.
3545 IF FND_API.To_Boolean (p_init_msg_list) THEN
3546 FND_MSG_PUB.initialize;
3547 END IF;
3548 -- Initialize API return status to success
3549 x_return_status := FND_API.G_RET_STS_SUCCESS;
3550 -- Get the current (login) user id.
3551 AMV_UTILITY_PVT.Get_UserInfo(
3552 x_resource_id => l_resource_id,
3553 x_user_id => l_current_user_id,
3554 x_login_id => l_current_login_id,
3555 x_user_status => l_current_user_status
3556 );
3557 IF (p_check_login_user = FND_API.G_TRUE) THEN
3558 -- Check if user is login and has the required privilege.
3559 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
3560 -- User is not login.
3561 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3562 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3563 FND_MSG_PUB.ADD;
3564 END IF;
3565 RAISE FND_API.G_EXC_ERROR;
3566 END IF;
3567 END IF;
3568
3569 x_item_id_varray := AMV_NUMBER_VARRAY_TYPE();
3570 x_message_varray := AMV_CHAR_VARRAY_TYPE();
3571 l_count := 0;
3572 FOR cur IN Get_Message_csr LOOP
3573 l_count := l_count + 1;
3574 x_item_id_varray.extend;
3575 x_item_id_varray(l_count) := cur.item_id;
3576 x_message_varray.extend;
3577 x_message_varray(l_count) := cur.item_name;
3578 END LOOP;
3579 --Standard call to get message count and if count=1, get the message
3580 FND_MSG_PUB.Count_And_Get (
3581 p_encoded => FND_API.G_FALSE,
3582 p_count => x_msg_count,
3583 p_data => x_msg_data
3584 );
3585 EXCEPTION
3586 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3588 -- Standard call to get message count and if count=1, get the message
3589 FND_MSG_PUB.Count_And_Get (
3590 p_encoded => FND_API.G_FALSE,
3591 p_count => x_msg_count,
3592 p_data => x_msg_data
3593 );
3594 WHEN FND_API.G_EXC_ERROR THEN
3595 x_return_status := FND_API.G_RET_STS_ERROR;
3596 -- Standard call to get message count and if count=1, get the message
3597 FND_MSG_PUB.Count_And_Get (
3598 p_encoded => FND_API.G_FALSE,
3599 p_count => x_msg_count,
3600 p_data => x_msg_data
3601 );
3602 WHEN OTHERS THEN
3603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3604 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3605 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3606 END IF;
3607 -- Standard call to get message count and if count=1, get the message
3608 FND_MSG_PUB.Count_And_Get (
3609 p_encoded => FND_API.G_FALSE,
3610 p_count => x_msg_count,
3611 p_data => x_msg_data
3612 );
3613 END Get_UserMessage;
3614 --------------------------------------------------------------------------------
3615 PROCEDURE Get_UserMessage2
3616 (
3617 p_api_version IN NUMBER,
3618 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3619 x_return_status OUT NOCOPY VARCHAR2,
3620 x_msg_count OUT NOCOPY NUMBER,
3621 x_msg_data OUT NOCOPY VARCHAR2,
3622 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3623 p_user_id IN NUMBER,
3624 x_item_varray OUT NOCOPY AMV_SIMPLE_ITEM_OBJ_VARRAY
3625 ) AS
3626 l_api_name CONSTANT VARCHAR2(30) := 'Get_UserMessage';
3627 l_api_version CONSTANT NUMBER := 1.0;
3628 l_resource_id NUMBER := -1;
3629 l_current_user_id NUMBER := -1;
3630 l_current_login_id NUMBER := -1;
3631 l_current_user_status VARCHAR2(80);
3632 --
3633 l_count NUMBER;
3634 --
3635 CURSOR Get_Message_csr IS
3636 SELECT
3637 item.item_id,
3638 item.object_version_number,
3639 item.creation_date,
3640 item.created_by,
3641 item.last_update_date,
3642 item.last_updated_by,
3643 item.last_update_login,
3644 item.application_id,
3645 item.external_access_flag,
3646 item.item_name,
3647 item.description,
3648 item.text_string,
3649 item.language_code,
3650 item.status_code,
3651 item.effective_start_date,
3652 item.expiration_date,
3653 item.item_type,
3654 item.url_string,
3655 item.publication_date,
3656 item.priority,
3657 item.content_type_id,
3658 item.owner_id,
3659 item.default_approver_id,
3660 item.item_destination_type
3661 FROM jtf_amv_items_vl item, amv_c_chl_item_match match,
3662 amv_u_my_channels mych
3663 WHERE item.item_type = 'MESSAGE_ITEM'
3664 AND item.item_id = match.item_id
3665 AND match.table_name_code = 'ITEM'
3666 AND match.approval_status_type = 'APPROVED'
3667 AND match.channel_id = mych.subscribing_to_id
3668 AND mych.subscribing_to_type = 'CHANNEL'
3669 AND mych.subscription_reason_type = 'ENFORCED'
3670 AND mych.user_or_group_type = 'USER'
3671 AND mych.user_or_group_id = p_user_id
3672 UNION
3673 SELECT
3674 item.item_id,
3675 item.object_version_number,
3676 item.creation_date,
3677 item.created_by,
3678 item.last_update_date,
3679 item.last_updated_by,
3680 item.last_update_login,
3681 item.application_id,
3682 item.external_access_flag,
3683 item.item_name,
3684 item.description,
3685 item.text_string,
3686 item.language_code,
3687 item.status_code,
3688 item.effective_start_date,
3689 item.expiration_date,
3690 item.item_type,
3691 item.url_string,
3692 item.publication_date,
3693 item.priority,
3694 item.content_type_id,
3695 item.owner_id,
3696 item.default_approver_id,
3697 item.item_destination_type
3698 FROM jtf_amv_items_vl item, amv_c_chl_item_match match,
3699 amv_u_my_channels mych, jtf_rs_group_members mem,
3700 jtf_rs_groups_vl g
3701 WHERE item.item_type = 'MESSAGE_ITEM'
3702 AND item.item_id = match.item_id
3703 AND match.table_name_code = 'ITEM'
3704 AND match.approval_status_type = 'APPROVED'
3705 AND match.channel_id = mych.subscribing_to_id
3706 AND mych.user_or_group_type = 'GROUP'
3707 AND mych.subscribing_to_type = 'CHANNEL'
3708 AND mych.subscription_reason_type = 'ENFORCED'
3709 AND mych.user_or_group_id = mem.group_id
3710 AND mem.delete_flag <> 'Y'
3711 AND mem.resource_id = p_user_id
3712 AND mem.group_id = g.group_id
3713 AND g.start_date_active <= SYSDATE
3714 AND NVL(g.end_date_active, SYSDATE+1) > SYSDATE
3715 ;
3716 --
3717 BEGIN
3718 -- Standard call to check for call compatibility.
3719 IF NOT FND_API.Compatible_API_Call (
3720 l_api_version,
3721 p_api_version,
3722 l_api_name,
3723 G_PKG_NAME) THEN
3724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3725 END IF;
3726 --Initialize message list if p_init_msg_list is TRUE.
3727 IF FND_API.To_Boolean (p_init_msg_list) THEN
3728 FND_MSG_PUB.initialize;
3729 END IF;
3730 -- Initialize API return status to success
3731 x_return_status := FND_API.G_RET_STS_SUCCESS;
3732 -- Get the current (login) user id.
3733 AMV_UTILITY_PVT.Get_UserInfo(
3734 x_resource_id => l_resource_id,
3735 x_user_id => l_current_user_id,
3736 x_login_id => l_current_login_id,
3737 x_user_status => l_current_user_status
3738 );
3739 IF (p_check_login_user = FND_API.G_TRUE) THEN
3740 -- Check if user is login and has the required privilege.
3741 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
3742 -- User is not login.
3743 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3744 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3745 FND_MSG_PUB.ADD;
3746 END IF;
3747 RAISE FND_API.G_EXC_ERROR;
3748 END IF;
3749 END IF;
3750
3751 x_item_varray := AMV_SIMPLE_ITEM_OBJ_VARRAY();
3752 l_count := 0;
3753 FOR cur IN Get_Message_csr LOOP
3754 l_count := l_count + 1;
3755 x_item_varray.extend;
3756 x_item_varray(l_count).item_id := cur.item_id;
3757 x_item_varray(l_count).object_version_number := cur.OBJECT_VERSION_NUMBER;
3758 x_item_varray(l_count).creation_date := cur.CREATION_DATE;
3759 x_item_varray(l_count).created_by := cur.CREATED_BY;
3760 x_item_varray(l_count).last_update_date := cur.LAST_UPDATE_DATE;
3761 x_item_varray(l_count).last_updated_by := cur.LAST_UPDATED_BY;
3762 x_item_varray(l_count).last_update_login := cur.LAST_UPDATE_LOGIN;
3763 x_item_varray(l_count).application_id := cur.APPLICATION_ID;
3764 x_item_varray(l_count).external_access_flag := cur.EXTERNAL_ACCESS_FLAG;
3765 x_item_varray(l_count).item_name := cur.ITEM_NAME;
3766 x_item_varray(l_count).description := cur.DESCRIPTION;
3767 x_item_varray(l_count).text_string := cur.TEXT_STRING;
3768 x_item_varray(l_count).language_code := cur.LANGUAGE_CODE;
3769 x_item_varray(l_count).status_code := cur.STATUS_CODE;
3770 x_item_varray(l_count).effective_start_date := cur.EFFECTIVE_START_DATE;
3771 x_item_varray(l_count).expiration_date := cur.EXPIRATION_DATE;
3772 x_item_varray(l_count).item_type := cur.ITEM_TYPE;
3773 x_item_varray(l_count).url_string := cur.URL_STRING;
3774 x_item_varray(l_count).publication_date := cur.PUBLICATION_DATE;
3775 x_item_varray(l_count).priority := cur.PRIORITY;
3776 x_item_varray(l_count).content_type_id := cur.CONTENT_TYPE_ID;
3777 x_item_varray(l_count).owner_id := cur.OWNER_ID;
3778 x_item_varray(l_count).default_approver_id := cur.DEFAULT_APPROVER_ID;
3779 x_item_varray(l_count).item_destination_type := cur.ITEM_DESTINATION_TYPE;
3780 x_item_varray(l_count).file_id_list := ' ';
3781 x_item_varray(l_count).persp_id_list := ' ';
3782 x_item_varray(l_count).persp_name_list := ' ';
3783 x_item_varray(l_count).author_list := ' ';
3784 x_item_varray(l_count).keyword_list := ' ';
3785 END LOOP;
3786 --Standard call to get message count and if count=1, get the message
3787 FND_MSG_PUB.Count_And_Get (
3788 p_encoded => FND_API.G_FALSE,
3789 p_count => x_msg_count,
3790 p_data => x_msg_data
3791 );
3792 EXCEPTION
3793 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3795 -- Standard call to get message count and if count=1, get the message
3796 FND_MSG_PUB.Count_And_Get (
3797 p_encoded => FND_API.G_FALSE,
3798 p_count => x_msg_count,
3799 p_data => x_msg_data
3800 );
3801 WHEN FND_API.G_EXC_ERROR THEN
3802 x_return_status := FND_API.G_RET_STS_ERROR;
3803 -- Standard call to get message count and if count=1, get the message
3804 FND_MSG_PUB.Count_And_Get (
3805 p_encoded => FND_API.G_FALSE,
3806 p_count => x_msg_count,
3807 p_data => x_msg_data
3808 );
3809 WHEN OTHERS THEN
3810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3811 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3812 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3813 END IF;
3814 -- Standard call to get message count and if count=1, get the message
3815 FND_MSG_PUB.Count_And_Get (
3816 p_encoded => FND_API.G_FALSE,
3817 p_count => x_msg_count,
3818 p_data => x_msg_data
3819 );
3820 END Get_UserMessage2;
3821 --------------------------------------------------------------------------------
3822 --------------------------------------------------------------------------------
3823 -- Start of comments
3824 -- API name : Get_ChannelsPerItem
3825 -- Type : Public
3826 -- Pre-reqs : None
3827 -- Function : Query and return all the channels matched the specified item
3828 -- Parameters :
3829 -- IN p_api_version NUMBER Required
3830 -- p_init_msg_list VARCHAR2 Optional
3831 -- Default = FND_API.G_FALSE
3832 -- p_check_login_user VARCHAR2 Optional
3833 -- Default = FND_API.G_TRUE
3834 -- Flag for checking user privilege and if user is login.
3835 -- p_match_type VARCHAR2 Optional
3836 -- p_item_id NUMBER Required
3837 -- The item id to be add the files.
3838 -- OUT : x_return_status VARCHAR2
3839 -- x_msg_count NUMBER
3840 -- x_msg_data VARCHAR2
3841 -- x_channel_array AMV_NAMEID_VARRAY_TYPE
3842 -- file id array for all the files of the item.
3843 -- Notes :
3844 --
3845 -- End of comments
3846 --
3847 PROCEDURE Get_ChannelsPerItem
3848 (
3849 p_api_version IN NUMBER,
3850 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3851 x_return_status OUT NOCOPY VARCHAR2,
3852 x_msg_count OUT NOCOPY NUMBER,
3853 x_msg_data OUT NOCOPY VARCHAR2,
3854 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3855 p_item_id IN NUMBER,
3856 p_match_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
3857 x_channel_array OUT NOCOPY AMV_NAMEID_VARRAY_TYPE
3858 ) AS
3859
3860 l_api_name CONSTANT VARCHAR2(30) := 'Get_ChannelsPerItem';
3861 l_api_version CONSTANT NUMBER := 1.0;
3862 l_resource_id NUMBER := -1;
3863 l_current_user_id NUMBER := -1;
3864 l_current_login_id NUMBER := -1;
3865 l_current_user_status VARCHAR2(80);
3866 --
3867 l_channel_id NUMBER;
3868 l_channel_name VARCHAR2(80);
3869 l_count NUMBER := 1;
3870 l_all_match VARCHAR2(60) := ''''||AMV_UTILITY_PVT.G_MATCH||''','''||AMV_UTILITY_PVT.G_PUSH||'''';
3871 --
3872 CURSOR GetChannels_csr IS
3873 select c.channel_id
3874 , c.channel_name
3875 from amv_c_channels_vl c
3876 , amv_c_chl_item_match m
3877 where m.item_id = p_item_id
3878 and m.channel_id = c.channel_id
3879 and c.channel_type = amv_utility_pvt.g_content
3880 and c.access_level_type = amv_utility_pvt.g_public
3881 and m.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
3882 and m.table_name_code = amv_utility_pvt.g_table_name_code
3883 and decode(p_match_type, FND_API.G_MISS_CHAR, p_match_type, m.available_due_to_type) = p_match_type;
3884 --and m.available_due_to_type in (decode(p_match_type,FND_API.G_MISS_CHAR,l_all_match,p_match_type));
3885 --
3886 BEGIN
3887 -- Standard call to check for call compatibility.
3888 IF NOT FND_API.Compatible_API_Call (
3889 l_api_version,
3890 p_api_version,
3891 l_api_name,
3892 G_PKG_NAME) THEN
3893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3894 END IF;
3895 --Initialize message list if p_init_msg_list is TRUE.
3896 IF FND_API.To_Boolean (p_init_msg_list) THEN
3897 FND_MSG_PUB.initialize;
3898 END IF;
3899 -- Initialize API return status to success
3900 x_return_status := FND_API.G_RET_STS_SUCCESS;
3901 -- Get the current (login) user id.
3902 AMV_UTILITY_PVT.Get_UserInfo(
3903 x_resource_id => l_resource_id,
3904 x_user_id => l_current_user_id,
3905 x_login_id => l_current_login_id,
3906 x_user_status => l_current_user_status
3907 );
3908 IF (p_check_login_user = FND_API.G_TRUE) THEN
3909 -- Check if user is login and has the required privilege.
3910 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
3911 -- User is not login.
3912 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3913 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3914 FND_MSG_PUB.ADD;
3915 END IF;
3916 RAISE FND_API.G_EXC_ERROR;
3917 END IF;
3918 END IF;
3919
3920 --
3921 x_channel_array := AMV_NAMEID_VARRAY_TYPE();
3922 OPEN GetChannels_csr;
3923 LOOP
3924 FETCH GetChannels_csr INTO l_channel_id, l_channel_name;
3925 EXIT WHEN GetChannels_csr%NOTFOUND;
3926 x_channel_array.extend;
3927 x_channel_array(l_count).id := l_channel_id;
3928 x_channel_array(l_count).name := l_channel_name;
3929 l_count := l_count + 1;
3930 END LOOP;
3931 CLOSE GetChannels_csr;
3932 --
3933
3934 --Standard call to get message count and if count=1, get the message
3935 FND_MSG_PUB.Count_And_Get (
3936 p_encoded => FND_API.G_FALSE,
3937 p_count => x_msg_count,
3938 p_data => x_msg_data
3939 );
3940 EXCEPTION
3941 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3943 -- Standard call to get message count and if count=1, get the message
3944 FND_MSG_PUB.Count_And_Get (
3945 p_encoded => FND_API.G_FALSE,
3946 p_count => x_msg_count,
3947 p_data => x_msg_data
3948 );
3949 WHEN FND_API.G_EXC_ERROR THEN
3950 x_return_status := FND_API.G_RET_STS_ERROR;
3951 -- Standard call to get message count and if count=1, get the message
3952 FND_MSG_PUB.Count_And_Get (
3953 p_encoded => FND_API.G_FALSE,
3954 p_count => x_msg_count,
3955 p_data => x_msg_data
3956 );
3957 WHEN OTHERS THEN
3958 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3959 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3960 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3961 END IF;
3962 -- Standard call to get message count and if count=1, get the message
3963 FND_MSG_PUB.Count_And_Get (
3964 p_encoded => FND_API.G_FALSE,
3965 p_count => x_msg_count,
3966 p_data => x_msg_data
3967 );
3968 END Get_ChannelsPerItem;
3969 --------------------------------------------------------------------------------
3970 END amv_item_pub;