1 PACKAGE BODY jtf_amv_item_pub AS
2 /* $Header: jtfpitmb.pls 115.8 2002/11/26 19:15:41 stopiwal ship $ */
3 --
4 -- NAME
5 -- JTF_AMV_ITEM_PUB
6 --
7 -- HISTORY
8 -- 11/30/1999 PWU CREATED
9 --
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_AMV_ITEM_PUB';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'jtfpitmb.pls';
12 --
13 G_EMP_RES_CATE CONSTANT VARCHAR2(30) := 'EMPLOYEE';
14 G_USED_BY_ITEM CONSTANT VARCHAR2(30) := 'ITEM';
15 G_MES_APPL_ID CONSTANT NUMBER := 520;
16 -- G_ISTORE_APPL_ID CONSTANT NUMBER := 671; --short name 'IBE'
17 --
18 TYPE CursorType IS REF CURSOR;
19 --
20 --------------------------------------------------------------------------------
21 ------------------------------- Private Proceudre ------------------------------
22 FUNCTION CURRENT_USER_ID return number AS
23 BEGIN
24 return FND_GLOBAL.user_id;
25 END CURRENT_USER_ID;
26 --
27 FUNCTION CURRENT_LOGIN_ID return number AS
28 BEGIN
29 return FND_GLOBAL.conc_login_id;
30 END CURRENT_LOGIN_ID;
31
32 FUNCTION check_lookup_exists(
33 p_lookup_table_name IN VARCHAR2 := 'FND_LOOKUP_VALUES',
34 p_lookup_type IN VARCHAR2,
35 p_lookup_code IN VARCHAR2
36 ) Return VARCHAR2 AS
37 l_sql VARCHAR2(200);
38 l_count NUMBER;
39 BEGIN
40 l_sql := 'SELECT COUNT(*) FROM ' || p_lookup_table_name;
41 l_sql := l_sql || ' WHERE lookup_type = ''' || p_lookup_type ||'''';
42 l_sql := l_sql || ' AND lookup_code = ''' || p_lookup_code ||'''';
43 l_sql := l_sql || ' AND enabled_flag = ''Y''';
44
45 EXECUTE IMMEDIATE l_sql INTO l_count;
46
47 IF l_count = 0 THEN
48 RETURN FND_API.g_false;
49 ELSE
50 RETURN FND_API.g_true;
51 END IF;
52 END check_lookup_exists;
53
54 FUNCTION Is_ApplIdValid
55 (
56 p_application_id IN NUMBER
57 ) RETURN Boolean AS
58 --
59 CURSOR Check_ApplicationID_csr is
60 Select application_id
61 From fnd_application
62 where application_id = p_application_id;
63 l_valid_flag BOOLEAN := FALSE;
64 l_tmp_number NUMBER;
65 --
66 BEGIN
67 OPEN Check_ApplicationID_csr;
68 FETCH Check_ApplicationID_csr INTO l_tmp_number;
69 IF (Check_ApplicationID_csr%NOTFOUND) THEN
70 l_valid_flag := FALSE;
71 ELSE
72 l_valid_flag := TRUE;
73 END IF;
74 CLOSE Check_ApplicationID_csr;
75 return l_valid_flag;
76 END Is_ApplIdValid;
77 --------------------------------------------------------------------------------
78 FUNCTION Is_ItemIdValid
79 (
80 p_item_id IN NUMBER
81 ) RETURN Boolean AS
82 --
83 CURSOR Check_ItemID_csr is
84 Select item_id
85 From jtf_amv_items_b
86 where item_id = p_item_id;
87 l_valid_flag BOOLEAN := FALSE;
88 l_tmp_number NUMBER;
89 --
90 BEGIN
91 OPEN Check_ItemID_csr;
92 FETCH Check_ItemID_csr INTO l_tmp_number;
93 IF (Check_ItemID_csr%NOTFOUND) THEN
94 l_valid_flag := FALSE;
95 ELSE
96 l_valid_flag := TRUE;
97 END IF;
98 CLOSE Check_ItemID_csr;
99 return l_valid_flag;
100 END Is_ItemIdValid;
101 --------------------------------------------------------------------------------
102 --------------------------------------------------------------------------------
103 PROCEDURE Create_Item
104 (
105 p_api_version IN NUMBER,
106 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
107 p_commit IN VARCHAR2 := FND_API.G_FALSE,
108 x_return_status OUT NOCOPY VARCHAR2,
109 x_msg_count OUT NOCOPY NUMBER,
110 x_msg_data OUT NOCOPY VARCHAR2,
111 p_item_rec IN ITEM_REC_TYPE,
112 x_item_id OUT NOCOPY NUMBER
113 ) IS
114 l_api_name CONSTANT VARCHAR2(30) := 'Create_Item';
115 l_api_version CONSTANT NUMBER := 1.0;
116 --
117 l_row_id VARCHAR2(500);
118 l_current_date DATE;
119 l_item_rec ITEM_REC_TYPE := p_item_rec;
120 --
121 CURSOR Get_DateAndId_csr IS
122 select
123 JTF_AMV_ITEMS_B_S.nextval, sysdate
124 from dual;
125 --
126 BEGIN
127 -- Standard call to check for call compatibility.
128 SAVEPOINT Create_Item_Pub;
129 IF NOT FND_API.Compatible_API_Call (
130 l_api_version,
131 p_api_version,
132 l_api_name,
133 G_PKG_NAME) THEN
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135 END IF;
136 --Initialize message list if p_init_msg_list is TRUE.
137 IF FND_API.To_Boolean (p_init_msg_list) THEN
138 FND_MSG_PUB.initialize;
139 END IF;
140 -- Initialize API return status to success
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142 -- Check if application id of the item record is valid
143 IF (Is_ApplIdValid(l_item_rec.application_id) <> TRUE) THEN
144 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
145 FND_MESSAGE.Set_name('JTF','JTF_AMV_APPLICATIONID_INVALID');
146 FND_MESSAGE.Set_Token('ID',
147 to_char( nvl(l_item_rec.application_id, -1) ) );
148 FND_MSG_PUB.Add;
149 END IF;
150 RAISE FND_API.G_EXC_ERROR;
151 END IF;
152 -- Get the item id from sequence and date from current date.
153 OPEN Get_DateAndId_csr;
154 FETCH Get_DateAndId_csr Into l_item_rec.item_id, l_current_date;
155 CLOSE Get_DateAndId_csr;
156 -- set version number to 1.
157 l_item_rec.object_version_number := 1;
158 IF (l_item_rec.external_access_flag <> FND_API.G_TRUE OR
159 l_item_rec.external_access_flag IS NULL) THEN
160 l_item_rec.external_access_flag := FND_API.G_FALSE;
161 END IF;
162 IF (l_item_rec.item_name is null OR
163 l_item_rec.item_name = FND_API.G_MISS_CHAR) THEN
164 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
165 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_NAME_NULL');
166 FND_MSG_PUB.Add;
167 END IF;
168 RAISE FND_API.G_EXC_ERROR;
169 END IF;
170 IF (l_item_rec.description = FND_API.G_MISS_CHAR) THEN
171 l_item_rec.description := null;
172 END IF;
173 IF (l_item_rec.text_string = FND_API.G_MISS_CHAR) THEN
174 l_item_rec.text_string := null;
175 END IF;
176 IF (l_item_rec.language_code = FND_API.G_MISS_CHAR OR
177 l_item_rec.language_code IS NULL) THEN
178 l_item_rec.language_code := USERENV('LANG');
179 END IF;
180 IF (l_item_rec.status_code is null OR
181 l_item_rec.status_code = FND_API.G_MISS_CHAR) THEN
182 l_item_rec.status_code := 'ACTIVE';
183 END IF;
184 IF (l_item_rec.effective_start_date = FND_API.G_MISS_DATE) THEN
185 l_item_rec.effective_start_date := null;
186 END IF;
187 IF (l_item_rec.expiration_date = FND_API.G_MISS_DATE) THEN
188 l_item_rec.expiration_date := null;
189 END IF;
190 IF (l_item_rec.item_type = FND_API.G_MISS_CHAR) THEN
191 l_item_rec.item_type := null;
192 END IF;
193 IF (l_item_rec.url_string = FND_API.G_MISS_CHAR) THEN
194 l_item_rec.url_string := null;
195 END IF;
196 IF (l_item_rec.publication_date = FND_API.G_MISS_DATE) THEN
197 l_item_rec.publication_date := null;
198 END IF;
199 IF (l_item_rec.priority = FND_API.G_MISS_CHAR) THEN
200 l_item_rec.priority := null;
201 END IF;
202 IF (l_item_rec.content_type_id = FND_API.G_MISS_NUM) THEN
203 l_item_rec.content_type_id := null;
204 END IF;
205 IF (l_item_rec.owner_id = FND_API.G_MISS_NUM) THEN
206 l_item_rec.owner_id := null;
207 END IF;
208 IF (l_item_rec.default_approver_id = FND_API.G_MISS_NUM) THEN
209 l_item_rec.default_approver_id := null;
210 END IF;
211 IF (l_item_rec.item_destination_type = FND_API.G_MISS_CHAR) THEN
212 l_item_rec.item_destination_type := null;
213 END IF;
214 IF (l_item_rec.access_name = FND_API.G_MISS_CHAR) THEN
215 l_item_rec.access_name := null;
216 END IF;
217 IF (l_item_rec.deliverable_type_code = FND_API.G_MISS_CHAR) THEN
218 l_item_rec.deliverable_type_code := null;
219 END IF;
220 IF (l_item_rec.applicable_to_code = FND_API.G_MISS_CHAR) THEN
221 l_item_rec.applicable_to_code := null;
222 END IF;
223 -- If called from MES (MES has its own requirement)
224 IF (l_item_rec.application_id = G_MES_APPL_ID) THEN
225 -- Check if item type in the item record is null
226 IF (l_item_rec.item_type IS NULL ) THEN
227 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
228 FND_MESSAGE.Set_name('JTF','JTF_AMV_NULL_ITEM_TYPE');
229 FND_MSG_PUB.Add;
230 END IF;
231 RAISE FND_API.G_EXC_ERROR;
232 END IF;
233
234 -- Check if effective start date in the item object is null
235 -- If so, make it effective immediately.
236 IF (l_item_rec.effective_start_date is null) THEN
237 l_item_rec.effective_start_date := sysdate;
238 END IF;
239 -- Check if priority in the item object is null
240 -- Maybe we should check if the priority is valid
241 IF (l_item_rec.priority is null ) THEN
242 l_item_rec.priority := 'LOW';
243 END IF;
244 /*
245 ELSIF (l_item_rec.application_id = G_ISTORE_APPL_ID ) THEN
246 IF (l_item_rec.access_name is null ) THEN
247 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
248 FND_MESSAGE.Set_name('JTF','JTF_AMV_ACCESS_NAME_MISSING');
249 FND_MSG_PUB.Add;
250 END IF;
251 RAISE FND_API.G_EXC_ERROR;
252 END IF;
253 IF (l_item_rec.deliverable_type_code is null ) THEN
254 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
255 FND_MESSAGE.Set_name('JTF','JTF_AMV_TYPE_CODE_NULL');
256 FND_MSG_PUB.Add;
257 END IF;
258 RAISE FND_API.G_EXC_ERROR;
259 ELSE
260 IF check_lookup_exists
261 (
262 p_lookup_type => 'JTF_AMV_DELV_TYPE_CODE',
263 p_lookup_code => l_item_rec.deliverable_type_code
264 ) = FND_API.G_FALSE THEN
265 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
266 FND_MESSAGE.Set_name('JTF','JTF_AMV_TYPE_CODE_WRONG');
267 FND_MSG_PUB.Add;
268 END IF;
269 RAISE FND_API.G_EXC_ERROR;
270 END IF;
271 END IF;
272 IF (l_item_rec.applicable_to_code is null ) THEN
273 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
274 FND_MESSAGE.Set_name('JTF','JTF_AMV_APPLICABLE_CODE_NULL');
275 FND_MSG_PUB.Add;
276 END IF;
277 RAISE FND_API.G_EXC_ERROR;
278 ELSE
279 IF check_lookup_exists
280 (
281 p_lookup_type => 'JTF_AMV_APPLI_TO_CODE',
282 p_lookup_code => l_item_rec.applicable_to_code
283 ) = FND_API.G_FALSE THEN
284 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
285 FND_MESSAGE.Set_name('JTF','JTF_AMV_APPL_TO_CODE_WRONG');
286 FND_MSG_PUB.Add;
287 END IF;
288 RAISE FND_API.G_EXC_ERROR;
289 END IF;
290 END IF;
291 */
292 END IF; --END OF (OUTER) IF
293 --Do create the record now.
294 JTF_AMV_ITEMS_PKG.INSERT_ROW
295 (
296 X_ROWID => l_row_id,
297 X_ITEM_ID =>l_item_rec.item_id,
298 X_OBJECT_VERSION_NUMBER => 1,
299 X_CREATION_DATE => l_current_date,
300 X_CREATED_BY => CURRENT_USER_ID,
301 X_LAST_UPDATE_DATE => l_current_date,
302 X_LAST_UPDATED_BY => CURRENT_USER_ID,
303 X_LAST_UPDATE_LOGIN => CURRENT_LOGIN_ID,
304 X_APPLICATION_ID => l_item_rec.application_id,
305 X_EXTERNAL_ACCESS_FLAG => l_item_rec.external_access_flag,
306 X_ITEM_NAME => l_item_rec.item_name,
307 X_DESCRIPTION => l_item_rec.description,
308 X_TEXT_STRING => l_item_rec.text_string,
309 X_LANGUAGE_CODE => l_item_rec.language_code,
310 X_STATUS_CODE => l_item_rec.status_code,
311 X_EFFECTIVE_START_DATE => l_item_rec.effective_start_date,
312 X_EXPIRATION_DATE => l_item_rec.expiration_date,
313 X_ITEM_TYPE => l_item_rec.item_type,
314 X_URL_STRING => l_item_rec.url_string,
315 X_PUBLICATION_DATE => l_item_rec.publication_date,
316 X_PRIORITY => l_item_rec.priority,
317 X_CONTENT_TYPE_ID => l_item_rec.content_type_id,
318 X_OWNER_ID => l_item_rec.owner_id,
319 X_DEFAULT_APPROVER_ID => l_item_rec.default_approver_id,
320 X_ITEM_DESTINATION_TYPE => l_item_rec.item_destination_type,
321 X_ACCESS_NAME => l_item_rec.access_name,
322 X_DELIVERABLE_TYPE_CODE => l_item_rec.deliverable_type_code,
323 X_APPLICABLE_TO_CODE => l_item_rec.applicable_to_code,
324 X_ATTRIBUTE_CATEGORY => null,
325 X_ATTRIBUTE1 => null,
326 X_ATTRIBUTE2 => null,
327 X_ATTRIBUTE3 => null,
328 X_ATTRIBUTE4 => null,
329 X_ATTRIBUTE5 => null,
330 X_ATTRIBUTE6 => null,
331 X_ATTRIBUTE7 => null,
332 X_ATTRIBUTE8 => null,
333 X_ATTRIBUTE9 => null,
334 X_ATTRIBUTE10 => null,
335 X_ATTRIBUTE11 => null,
336 X_ATTRIBUTE12 => null,
337 X_ATTRIBUTE13 => null,
338 X_ATTRIBUTE14 => null,
339 X_ATTRIBUTE15 => null
340 );
341 -- pass back the item id.
342 x_item_id := l_item_rec.item_id;
343 --Standard check of commit
344 IF FND_API.To_Boolean ( p_commit ) THEN
345 COMMIT WORK;
346 END IF;
347 --Standard call to get message count and if count=1, get the message
348 FND_MSG_PUB.Count_And_Get (
349 p_encoded => FND_API.G_FALSE,
350 p_count => x_msg_count,
351 p_data => x_msg_data
352 );
353 EXCEPTION
354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
355 ROLLBACK TO Create_Item_Pub;
356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
357 -- Standard call to get message count and if count=1, get the message
358 FND_MSG_PUB.Count_And_Get (
359 p_encoded => FND_API.G_FALSE,
360 p_count => x_msg_count,
361 p_data => x_msg_data
362 );
363 WHEN FND_API.G_EXC_ERROR THEN
364 ROLLBACK TO Create_Item_Pub;
365 x_return_status := FND_API.G_RET_STS_ERROR;
366 -- Standard call to get message count and if count=1, get the message
367 FND_MSG_PUB.Count_And_Get (
368 p_encoded => FND_API.G_FALSE,
369 p_count => x_msg_count,
370 p_data => x_msg_data
371 );
372 WHEN OTHERS THEN
373 ROLLBACK TO Create_Item_Pub;
374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
375 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
376 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
377 END IF;
378 -- Standard call to get message count and if count=1, get the message
379 FND_MSG_PUB.Count_And_Get (
380 p_encoded => FND_API.G_FALSE,
381 p_count => x_msg_count,
382 p_data => x_msg_data
383 );
384 END Create_Item;
385 --------------------------------------------------------------------------------
386 PROCEDURE Delete_Item
387 (
388 p_api_version IN NUMBER,
389 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
390 p_commit IN VARCHAR2 := FND_API.G_FALSE,
391 x_return_status OUT NOCOPY VARCHAR2,
392 x_msg_count OUT NOCOPY NUMBER,
393 x_msg_data OUT NOCOPY VARCHAR2,
394 p_item_id IN NUMBER
395 ) AS
396 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Item';
397 l_api_version CONSTANT NUMBER := 1.0;
398 --
399 BEGIN
400 -- Standard call to check for call compatibility.
401 SAVEPOINT Delete_Item_Pub;
402 IF NOT FND_API.Compatible_API_Call (
403 l_api_version,
404 p_api_version,
405 l_api_name,
406 G_PKG_NAME) THEN
407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
408 END IF;
409 --Initialize message list if p_init_msg_list is TRUE.
410 IF FND_API.To_Boolean (p_init_msg_list) THEN
411 FND_MSG_PUB.initialize;
412 END IF;
413 -- Initialize API return status to success
414 x_return_status := FND_API.G_RET_STS_SUCCESS;
415 -- Check if item id is valid.
416 IF (Is_ItemIdValid(p_item_id) <> TRUE) THEN
417 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
418 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
419 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1) ));
420 FND_MSG_PUB.Add;
421 END IF;
422 RAISE FND_API.G_EXC_ERROR;
423 END IF;
424 -- Delete the item's authors.
425 Delete from jtf_amv_item_authors
426 where item_id = p_item_id;
427 -- Delete the item's keywords.
428 Delete from jtf_amv_item_keywords
429 where item_id = p_item_id;
430 -- Remove item's files.
431 Delete from jtf_amv_attachments
432 where attachment_used_by_id = p_item_id
433 and attachment_used_by = G_USED_BY_ITEM;
434 -- Finally delete the item itself.
435 JTF_AMV_ITEMS_PKG.DELETE_ROW ( p_item_id );
436 --Standard check of commit
437 IF FND_API.To_Boolean ( p_commit ) THEN
438 COMMIT WORK;
439 END IF;
440 --Standard call to get message count and if count=1, get the message
441 FND_MSG_PUB.Count_And_Get (
442 p_encoded => FND_API.G_FALSE,
443 p_count => x_msg_count,
444 p_data => x_msg_data
445 );
446 EXCEPTION
447 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
448 ROLLBACK TO Delete_Item_Pub;
449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
450 -- Standard call to get message count and if count=1, get the message
451 FND_MSG_PUB.Count_And_Get (
452 p_encoded => FND_API.G_FALSE,
453 p_count => x_msg_count,
454 p_data => x_msg_data
455 );
456 WHEN FND_API.G_EXC_ERROR THEN
457 ROLLBACK TO Delete_Item_Pub;
458 x_return_status := FND_API.G_RET_STS_ERROR;
459 -- Standard call to get message count and if count=1, get the message
460 FND_MSG_PUB.Count_And_Get (
461 p_encoded => FND_API.G_FALSE,
462 p_count => x_msg_count,
463 p_data => x_msg_data
464 );
465 WHEN OTHERS THEN
466 ROLLBACK TO Delete_Item_Pub;
467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
468 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
469 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
470 END IF;
471 -- Standard call to get message count and if count=1, get the message
472 FND_MSG_PUB.Count_And_Get (
473 p_encoded => FND_API.G_FALSE,
474 p_count => x_msg_count,
475 p_data => x_msg_data
476 );
477 END Delete_Item;
478 --------------------------------------------------------------------------------
479 PROCEDURE Update_Item
480 (
481 p_api_version IN NUMBER,
482 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
483 p_commit IN VARCHAR2 := FND_API.G_FALSE,
484 x_return_status OUT NOCOPY VARCHAR2,
485 x_msg_count OUT NOCOPY NUMBER,
486 x_msg_data OUT NOCOPY VARCHAR2,
487 p_item_rec IN ITEM_REC_TYPE
488 ) AS
489 l_api_name CONSTANT VARCHAR2(30) := 'Update_Item';
490 l_api_version CONSTANT NUMBER := 1.0;
491 --
492 l_item_id NUMBER;
493 l_current_date DATE;
494 l_new_item_rec ITEM_REC_TYPE := p_item_rec;
495 l_old_item_rec ITEM_REC_TYPE;
496 l_record_change_flag boolean := false;
497 --
498 BEGIN
499 -- Standard call to check for call compatibility.
500 SAVEPOINT Update_Item_Pub;
501 IF NOT FND_API.Compatible_API_Call (
502 l_api_version,
503 p_api_version,
504 l_api_name,
505 G_PKG_NAME) THEN
506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
507 END IF;
508 --Initialize message list if p_init_msg_list is TRUE.
509 IF FND_API.To_Boolean (p_init_msg_list) THEN
510 FND_MSG_PUB.initialize;
511 END IF;
512 -- Initialize API return status to success
513 x_return_status := FND_API.G_RET_STS_SUCCESS;
514 -- MAKE SURE THE PASSED ITEM RECORD HAS ALL THE RIGHT INFORMATION.
515 -- Get the original record data
516 Get_Item
517 (
518 p_api_version => p_api_version,
519 x_return_status => x_return_status,
520 x_msg_count => x_msg_count,
521 x_msg_data => x_msg_data,
522 p_item_id => p_item_rec.item_id,
523 x_item_rec => l_old_item_rec
524 );
525 -- Check if item id is valid.
526 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
527 RAISE FND_API.G_EXC_ERROR;
528 END IF;
529 -- Check to see if the record has been changed,
530 -- via compare object version number.
531 IF (l_old_item_rec.object_version_number =
532 l_new_item_rec.object_version_number) THEN
533 l_new_item_rec.object_version_number :=
534 l_new_item_rec.object_version_number +1;
535 ELSE
536 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
537 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_CHANGED');
538 FND_MESSAGE.Set_Token('ID',
539 to_char(nvl(l_new_item_rec.item_id,-1)) );
540 FND_MSG_PUB.Add;
541 END IF;
542 RAISE FND_API.G_EXC_ERROR;
543 END IF;
544 --Checking application id.
545 IF ( l_new_item_rec.application_id IS NULL OR
546 l_new_item_rec.application_id = FND_API.G_MISS_NUM OR
547 l_new_item_rec.application_id = l_old_item_rec.application_id) THEN
548 l_new_item_rec.application_id := l_old_item_rec.application_id;
549 ELSE
550 -- Check if application in the item object is valid
551 l_record_change_flag := true;
552 IF (Is_ApplIdValid(l_new_item_rec.application_id) <> TRUE) THEN
553 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
554 FND_MESSAGE.Set_name('JTF','JTF_AMV_APPLICATIONID_INVALID');
555 FND_MESSAGE.Set_Token('ID',
556 to_char( l_new_item_rec.application_id ) );
557 FND_MSG_PUB.Add;
558 END IF;
559 RAISE FND_API.G_EXC_ERROR;
560 END IF;
561 END IF;
562 --Checking external access flag
563 If (l_new_item_rec.external_access_flag is NULL OR
564 l_new_item_rec.external_access_flag =
565 l_old_item_rec.external_access_flag OR
566 l_new_item_rec.external_access_flag = FND_API.G_MISS_CHAR ) THEN
567 l_new_item_rec.external_access_flag :=
568 l_old_item_rec.external_access_flag;
569 ELSE
570 IF (l_new_item_rec.external_access_flag <> FND_API.G_TRUE) THEN
571 l_new_item_rec.external_access_flag := FND_API.G_FALSE;
572 END IF;
573 IF (l_new_item_rec.external_access_flag <>
574 l_old_item_rec.external_access_flag) THEN
575 l_record_change_flag := true;
576 END IF;
577 END IF;
578 --Checking item name, which is translatable so default to G_MISS
579 If (l_new_item_rec.item_name is NULL OR
580 l_new_item_rec.item_name = FND_API.G_MISS_CHAR OR
581 l_new_item_rec.item_name = l_old_item_rec.item_name) THEN
582 l_new_item_rec.item_name := FND_API.G_MISS_CHAR;
583 ELSE
584 l_record_change_flag := true;
585 END IF;
586 --Checking description which is translatable so default to G_MISS
587 IF ( l_new_item_rec.description = FND_API.G_MISS_CHAR OR
588 l_new_item_rec.description IS NULL AND
589 l_old_item_rec.description IS NULL OR
590 l_new_item_rec.description = l_old_item_rec.description) THEN
591 l_new_item_rec.description := FND_API.G_MISS_CHAR;
592 ELSE
593 l_record_change_flag := true;
594 END IF;
595 --Checking text string which is translatable so default to G_MISS
596 IF ( l_new_item_rec.text_string = FND_API.G_MISS_CHAR OR
597 l_new_item_rec.text_string IS NULL AND
598 l_old_item_rec.text_string IS NULL OR
599 l_new_item_rec.text_string = l_old_item_rec.text_string) THEN
600 l_new_item_rec.text_string := FND_API.G_MISS_CHAR;
601 ELSE
602 l_record_change_flag := true;
603 END IF;
604 --Checking language code
605 IF ( l_new_item_rec.language_code IS NULL OR
606 l_new_item_rec.language_code = FND_API.G_MISS_CHAR OR
607 l_new_item_rec.language_code = l_old_item_rec.language_code) THEN
608 l_new_item_rec.language_code := l_old_item_rec.language_code;
609 ELSE
610 l_record_change_flag := true;
611 END IF;
612 --Checking status code
613 IF ( l_new_item_rec.status_code IS NULL OR
614 l_new_item_rec.status_code = FND_API.G_MISS_CHAR OR
615 l_new_item_rec.status_code = l_old_item_rec.status_code) THEN
616 l_new_item_rec.status_code := l_old_item_rec.status_code;
617 ELSE
618 l_record_change_flag := true;
619 END IF;
620 --Checking starting date.
621 If ( l_new_item_rec.effective_start_date = FND_API.G_MISS_DATE OR
622 l_new_item_rec.effective_start_date is NULL AND
623 l_old_item_rec.effective_start_date is NULL OR
624 l_new_item_rec.effective_start_date =
625 l_old_item_rec.effective_start_date) THEN
626 l_new_item_rec.effective_start_date :=
627 l_old_item_rec.effective_start_date;
628 ELSE
629 l_record_change_flag := true;
630 END IF;
631 --Checking end date.
632 IF ( l_new_item_rec.expiration_date = FND_API.G_MISS_DATE OR
633 l_new_item_rec.expiration_date is NULL AND
634 l_old_item_rec.expiration_date is NULL OR
635 l_new_item_rec.expiration_date =
636 l_old_item_rec.expiration_date) THEN
637 l_new_item_rec.expiration_date := l_old_item_rec.expiration_date;
638 ELSE
639 l_record_change_flag := true;
640 END IF;
641 --Checking item type
642 IF (l_new_item_rec.item_type = FND_API.G_MISS_CHAR OR
643 l_new_item_rec.item_type is null and
644 l_old_item_rec.item_type is null OR
645 l_new_item_rec.item_type = l_old_item_rec.item_type) THEN
646 l_new_item_rec.item_type := l_old_item_rec.item_type;
647 ELSE
648 l_record_change_flag := true;
649 END IF;
650 --Checking URL
651 IF ( l_new_item_rec.url_string = FND_API.G_MISS_CHAR OR
652 l_new_item_rec.url_string IS NULL AND
653 l_old_item_rec.url_string IS NULL OR
654 l_new_item_rec.url_string = l_old_item_rec.url_string) THEN
655 l_new_item_rec.url_string := l_old_item_rec.url_string;
656 ELSE
657 l_record_change_flag := true;
658 END IF;
659 --Checking publication date
660 IF ( l_new_item_rec.publication_date = FND_API.G_MISS_DATE OR
661 l_new_item_rec.publication_date IS NULL AND
662 l_old_item_rec.publication_date IS NULL OR
663 l_new_item_rec.publication_date =
664 l_old_item_rec.publication_date) THEN
665 l_new_item_rec.publication_date := l_old_item_rec.publication_date;
666 ELSE
667 l_record_change_flag := true;
668 END IF;
669 --Checking priority
670 IF ( l_new_item_rec.priority = FND_API.G_MISS_CHAR OR
671 l_new_item_rec.priority IS NULL AND
672 l_old_item_rec.priority IS NULL OR
673 l_new_item_rec.priority = l_old_item_rec.priority) THEN
674 l_new_item_rec.priority := l_old_item_rec.priority;
675 ELSE
676 l_record_change_flag := true;
677 END IF;
678 --Checking content type id.
679 IF ( l_new_item_rec.content_type_id = FND_API.G_MISS_NUM OR
680 l_new_item_rec.content_type_id is NULL AND
681 l_old_item_rec.content_type_id is NULL OR
682 l_new_item_rec.content_type_id = l_old_item_rec.content_type_id) THEN
683 l_new_item_rec.content_type_id := l_old_item_rec.content_type_id;
684 ELSE
685 l_record_change_flag := true;
686 END IF;
687 --Checking owner user id.
688 IF ( l_new_item_rec.owner_id = FND_API.G_MISS_NUM OR
689 l_new_item_rec.owner_id IS NULL AND
690 l_old_item_rec.owner_id IS NULL OR
691 l_new_item_rec.owner_id = l_old_item_rec.owner_id) THEN
692 l_new_item_rec.owner_id := l_old_item_rec.owner_id;
693 ELSE
694 l_record_change_flag := true;
695 END IF;
696 --Checking default approver user id.
697 IF ( l_new_item_rec.default_approver_id = FND_API.G_MISS_NUM OR
698 l_new_item_rec.default_approver_id IS NULL AND
699 l_old_item_rec.default_approver_id IS NULL OR
700 l_new_item_rec.default_approver_id =
701 l_old_item_rec.default_approver_id) THEN
702 l_new_item_rec.default_approver_id := l_old_item_rec.default_approver_id;
703 ELSE
704 l_record_change_flag := true;
705 END IF;
706 --Checking destination type
707 IF ( l_new_item_rec.item_destination_type = FND_API.G_MISS_CHAR OR
708 l_new_item_rec.item_destination_type IS NULL AND
709 l_old_item_rec.item_destination_type IS NULL OR
710 l_new_item_rec.item_destination_type =
711 l_old_item_rec.item_destination_type) THEN
712 l_new_item_rec.item_destination_type :=
713 l_old_item_rec.item_destination_type;
714 ELSE
715 l_record_change_flag := true;
716 END IF;
717 --Checking access name
718 IF ( l_new_item_rec.access_name = FND_API.G_MISS_CHAR OR
719 l_new_item_rec.access_name IS NULL AND
720 l_old_item_rec.access_name IS NULL OR
721 l_new_item_rec.access_name =
722 l_old_item_rec.access_name) THEN
723 l_new_item_rec.access_name := l_old_item_rec.access_name;
724 ELSE
725 l_record_change_flag := true;
726 /*
727 -- Istore specific.
728 IF (l_new_item_rec.application_id = G_ISTORE_APPL_ID OR
729 l_new_item_rec.application_id = FND_API.G_MISS_CHAR and
730 l_old_item_rec.application_id = G_ISTORE_APPL_ID ) THEN
731 IF (l_new_item_rec.access_name IS NULL) THEN
732 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
733 FND_MESSAGE.Set_name('JTF','JTF_AMV_ACCESS_NAME_MISSING');
734 FND_MSG_PUB.Add;
735 END IF;
736 RAISE FND_API.G_EXC_ERROR;
737 END IF;
738 END IF;
739 */
740 END IF;
741 --Checking deliverable type code.
742 IF ( l_new_item_rec.deliverable_type_code = FND_API.G_MISS_CHAR OR
743 l_new_item_rec.deliverable_type_code IS NULL AND
744 l_old_item_rec.deliverable_type_code IS NULL OR
745 l_new_item_rec.deliverable_type_code =
746 l_old_item_rec.deliverable_type_code) THEN
747 l_new_item_rec.deliverable_type_code :=
748 l_old_item_rec.deliverable_type_code;
749 ELSE
750 l_record_change_flag := true;
751 /*
752 -- Istore specific.
753 IF (l_new_item_rec.application_id = G_ISTORE_APPL_ID) THEN
754 IF check_lookup_exists
755 (
756 p_lookup_type => 'JTF_AMV_DELV_TYPE_CODE',
757 p_lookup_code => l_new_item_rec.deliverable_type_code
758 ) = FND_API.G_FALSE THEN
759 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
760 FND_MESSAGE.Set_name('JTF','JTF_AMV_TYPE_CODE_WRONG');
761 FND_MSG_PUB.Add;
762 END IF;
763 RAISE FND_API.G_EXC_ERROR;
764 END IF;
765 END IF;
766 */
767 END IF;
768 --Checking applicable_to_code
769 IF ( l_new_item_rec.applicable_to_code = FND_API.G_MISS_CHAR OR
770 l_new_item_rec.applicable_to_code IS NULL AND
771 l_old_item_rec.applicable_to_code IS NULL OR
772 l_new_item_rec.applicable_to_code =
773 l_old_item_rec.applicable_to_code) THEN
774 l_new_item_rec.applicable_to_code :=
775 l_old_item_rec.applicable_to_code;
776 ELSE
777 l_record_change_flag := true;
778 /*
779 -- Istore specific.
780 IF (l_new_item_rec.application_id = G_ISTORE_APPL_ID) THEN
781 IF check_lookup_exists
782 (
783 p_lookup_type => 'JTF_AMV_APPLI_TO_CODE',
784 p_lookup_code => l_new_item_rec.applicable_to_code
785 ) = FND_API.G_FALSE THEN
786 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
787 FND_MESSAGE.Set_name('JTF','JTF_AMV_APPL_TO_CODE_WRONG');
788 FND_MSG_PUB.Add;
789 END IF;
790 RAISE FND_API.G_EXC_ERROR;
791 END IF;
792 END IF;
793 */
794 END IF;
795 -- Now update the item record.
796 IF (l_record_change_flag = TRUE) THEN
797 JTF_AMV_ITEMS_PKG.UPDATE_ROW
798 (
799 X_ITEM_ID =>l_new_item_rec.item_id,
800 X_OBJECT_VERSION_NUMBER => l_new_item_rec.object_version_number,
801 X_LAST_UPDATE_DATE => sysdate,
802 X_LAST_UPDATED_BY => CURRENT_USER_ID,
803 X_LAST_UPDATE_LOGIN => CURRENT_LOGIN_ID,
804 X_APPLICATION_ID => l_new_item_rec.application_id,
805 X_EXTERNAL_ACCESS_FLAG => l_new_item_rec.external_access_flag,
806 X_ITEM_NAME => l_new_item_rec.item_name,
807 X_DESCRIPTION => l_new_item_rec.description,
808 X_TEXT_STRING => l_new_item_rec.text_string,
809 X_LANGUAGE_CODE => l_new_item_rec.language_code,
810 X_STATUS_CODE => l_new_item_rec.status_code,
811 X_EFFECTIVE_START_DATE => l_new_item_rec.effective_start_date,
812 X_EXPIRATION_DATE => l_new_item_rec.expiration_date,
813 X_ITEM_TYPE => l_new_item_rec.item_type,
814 X_URL_STRING => l_new_item_rec.url_string,
815 X_PUBLICATION_DATE => l_new_item_rec.publication_date,
816 X_PRIORITY => l_new_item_rec.priority,
817 X_CONTENT_TYPE_ID => l_new_item_rec.content_type_id,
818 X_OWNER_ID => l_new_item_rec.owner_id,
819 X_DEFAULT_APPROVER_ID => l_new_item_rec.default_approver_id,
820 X_ITEM_DESTINATION_TYPE => l_new_item_rec.item_destination_type,
821 X_ACCESS_NAME => l_new_item_rec.access_name,
822 X_DELIVERABLE_TYPE_CODE => l_new_item_rec.deliverable_type_code,
823 X_APPLICABLE_TO_CODE => l_new_item_rec.applicable_to_code,
824
825 X_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
826 X_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
827 X_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
828 X_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
829 X_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
830 X_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
831 X_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
832 X_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
833 X_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
834 X_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
835 X_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
836 X_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
837 X_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
838 X_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
839 X_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
840 X_ATTRIBUTE15 => FND_API.G_MISS_CHAR
841 );
842 END IF;
843 --Standard check of commit
844 IF FND_API.To_Boolean ( p_commit ) THEN
845 COMMIT WORK;
846 END IF;
847 --Standard call to get message count and if count=1, get the message
848 FND_MSG_PUB.Count_And_Get (
849 p_encoded => FND_API.G_FALSE,
850 p_count => x_msg_count,
851 p_data => x_msg_data
852 );
853 EXCEPTION
854 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
855 ROLLBACK TO Update_Item_Pub;
856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
857 -- Standard call to get message count and if count=1, get the message
858 FND_MSG_PUB.Count_And_Get (
859 p_encoded => FND_API.G_FALSE,
860 p_count => x_msg_count,
861 p_data => x_msg_data
862 );
863 WHEN FND_API.G_EXC_ERROR THEN
864 ROLLBACK TO Update_Item_Pub;
865 x_return_status := FND_API.G_RET_STS_ERROR;
866 -- Standard call to get message count and if count=1, get the message
867 FND_MSG_PUB.Count_And_Get (
868 p_encoded => FND_API.G_FALSE,
869 p_count => x_msg_count,
870 p_data => x_msg_data
871 );
872 WHEN OTHERS THEN
873 ROLLBACK TO Update_Item_Pub;
874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
875 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
876 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
877 END IF;
878 -- Standard call to get message count and if count=1, get the message
879 FND_MSG_PUB.Count_And_Get (
880 p_encoded => FND_API.G_FALSE,
881 p_count => x_msg_count,
882 p_data => x_msg_data
883 );
884 END Update_Item;
885 --------------------------------------------------------------------------------
886 PROCEDURE Get_Item
887 (
888 p_api_version IN NUMBER,
889 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
890 x_return_status OUT NOCOPY VARCHAR2,
891 x_msg_count OUT NOCOPY NUMBER,
892 x_msg_data OUT NOCOPY VARCHAR2,
893 p_item_id IN NUMBER,
894 x_item_rec OUT NOCOPY ITEM_REC_TYPE
895 ) AS
896 l_api_name CONSTANT VARCHAR2(30) := 'Get_Item';
897 l_api_version CONSTANT NUMBER := 1.0;
898 l_current_user_id NUMBER := -1;
899 l_current_login_id NUMBER := -1;
900 l_current_user_status NUMBER;
901 --
902 CURSOR Get_Item_csr IS
903 Select
904 item_id,
905 CREATION_DATE,
906 CREATED_BY,
907 LAST_UPDATE_DATE,
908 LAST_UPDATED_BY,
909 LAST_UPDATE_LOGIN,
910 OBJECT_VERSION_NUMBER,
911 APPLICATION_ID,
912 EXTERNAL_ACCESS_FLAG,
913 ITEM_NAME,
914 DESCRIPTION,
915 TEXT_STRING,
916 LANGUAGE_CODE,
917 STATUS_CODE,
918 effective_start_date,
919 expiration_date,
920 ITEM_TYPE,
921 URL_STRING,
922 PUBLICATION_DATE,
923 PRIORITY,
924 CONTENT_TYPE_ID,
925 OWNER_ID,
926 DEFAULT_APPROVER_ID,
927 ITEM_DESTINATION_TYPE,
928 access_name,
929 deliverable_type_code,
930 applicable_to_code
931 From jtf_amv_items_vl
932 Where item_id = p_item_id;
933 --
934 BEGIN
935 -- Standard call to check for call compatibility.
936 IF NOT FND_API.Compatible_API_Call (
937 l_api_version,
938 p_api_version,
939 l_api_name,
940 G_PKG_NAME) THEN
941 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
942 END IF;
943 --Initialize message list if p_init_msg_list is TRUE.
944 IF FND_API.To_Boolean (p_init_msg_list) THEN
945 FND_MSG_PUB.initialize;
946 END IF;
947 -- Initialize API return status to success
948 x_return_status := FND_API.G_RET_STS_SUCCESS;
949 -- Now get the item data.
950 OPEN Get_Item_csr;
951 FETCH Get_Item_csr INTO x_item_rec;
952 IF (Get_Item_csr%NOTFOUND) THEN
953 CLOSE Get_Item_csr;
954 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
955 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
956 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1) ));
957 FND_MSG_PUB.Add;
958 END IF;
959 RAISE FND_API.G_EXC_ERROR;
960 END IF;
961 CLOSE Get_Item_csr;
962 --Standard call to get message count and if count=1, get the message
963 FND_MSG_PUB.Count_And_Get (
964 p_encoded => FND_API.G_FALSE,
965 p_count => x_msg_count,
966 p_data => x_msg_data
967 );
968 EXCEPTION
969 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
970 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
971 -- Standard call to get message count and if count=1, get the message
972 FND_MSG_PUB.Count_And_Get (
973 p_encoded => FND_API.G_FALSE,
974 p_count => x_msg_count,
975 p_data => x_msg_data
976 );
977 WHEN FND_API.G_EXC_ERROR THEN
978 x_return_status := FND_API.G_RET_STS_ERROR;
979 -- Standard call to get message count and if count=1, get the message
980 FND_MSG_PUB.Count_And_Get (
981 p_encoded => FND_API.G_FALSE,
982 p_count => x_msg_count,
983 p_data => x_msg_data
984 );
985 WHEN OTHERS THEN
986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
987 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
988 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
989 END IF;
990 -- Standard call to get message count and if count=1, get the message
991 FND_MSG_PUB.Count_And_Get (
992 p_encoded => FND_API.G_FALSE,
993 p_count => x_msg_count,
994 p_data => x_msg_data
995 );
996 END Get_Item;
997 --------------------------------------------------------------------------------
998 ------------------------------ ITEM_KEYWORD ------------------------------------
999 PROCEDURE Add_ItemKeyword
1000 (
1001 p_api_version IN NUMBER,
1002 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1003 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1004 x_return_status OUT NOCOPY VARCHAR2,
1005 x_msg_count OUT NOCOPY NUMBER,
1006 x_msg_data OUT NOCOPY VARCHAR2,
1007 p_item_id IN NUMBER,
1008 p_keyword_tab IN CHAR_TAB_TYPE
1009 ) AS
1010 l_api_name CONSTANT VARCHAR2(30) := 'Add_ItemKeyword';
1011 l_api_version CONSTANT NUMBER := 1.0;
1012 --
1013 l_current_user_id NUMBER := CURRENT_USER_ID;
1014 l_current_login_id NUMBER := CURRENT_LOGIN_ID;
1015 l_count NUMBER;
1016 l_temp_number NUMBER;
1017 l_date DATE;
1018 --
1019 CURSOR Check_Itemkeyword_csr (p_kword in VARCHAR2) IS
1020 Select
1021 item_keyword_id
1022 From jtf_amv_item_keywords
1023 Where keyword = p_kword
1024 And item_id = p_item_id;
1025 --
1026 CURSOR Get_IDandDate_csr is
1027 Select jtf_amv_item_keywords_s.nextval, sysdate
1028 From Dual;
1029 --
1030 BEGIN
1031 -- Standard call to check for call compatibility.
1032 SAVEPOINT Add_ItemKeyword_Pub;
1033 IF NOT FND_API.Compatible_API_Call (
1034 l_api_version,
1035 p_api_version,
1036 l_api_name,
1037 G_PKG_NAME) THEN
1038 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1039 END IF;
1040 --Initialize message list if p_init_msg_list is TRUE.
1041 IF FND_API.To_Boolean (p_init_msg_list) THEN
1042 FND_MSG_PUB.initialize;
1043 END IF;
1044 -- Initialize API return status to success
1045 x_return_status := FND_API.G_RET_STS_SUCCESS;
1046 -- Check if item id is valid.
1047 IF (Is_ItemIdValid(p_item_id) <> TRUE) THEN
1048 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1049 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
1050 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1) ));
1051 FND_MSG_PUB.Add;
1052 END IF;
1053 RAISE FND_API.G_EXC_ERROR;
1054 END IF;
1055 IF (p_keyword_tab is null) THEN
1056 l_count := 0;
1057 ELSE
1058 l_count := p_keyword_tab.count;
1059 END IF;
1060 FOR i IN 1..l_count LOOP
1061 OPEN Check_Itemkeyword_csr( p_keyword_tab(i) );
1062 FETCH Check_Itemkeyword_csr INTO l_temp_number;
1063 IF (Check_Itemkeyword_csr%FOUND) THEN
1064 CLOSE Check_Itemkeyword_csr;
1065 x_return_status := FND_API.G_RET_STS_ERROR;
1066 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1067 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_HAS_KEYWORD');
1068 FND_MESSAGE.Set_Token('ID', to_char(p_item_id) );
1069 FND_MESSAGE.Set_Token('KEYWORD', p_keyword_tab(i));
1070 FND_MSG_PUB.Add;
1071 END IF;
1072 ELSE
1073 CLOSE Check_Itemkeyword_csr;
1074 OPEN Get_IDandDate_csr;
1075 FETCH Get_IDandDate_csr Into l_temp_number, l_date;
1076 CLOSE Get_IDandDate_csr;
1077 Insert Into jtf_amv_item_keywords
1078 (
1079 ITEM_KEYWORD_ID,
1080 OBJECT_VERSION_NUMBER,
1081 LAST_UPDATE_DATE,
1082 LAST_UPDATED_BY,
1083 CREATION_DATE,
1084 CREATED_BY,
1085 LAST_UPDATE_LOGIN,
1086 ITEM_ID,
1087 KEYWORD
1088 ) VALUES
1089 (
1090 l_temp_number,
1091 1,
1092 l_date,
1093 l_current_user_id,
1094 l_date,
1095 l_current_user_id,
1096 l_current_login_id,
1097 p_item_id,
1098 p_keyword_tab(i)
1099 );
1100 END IF;
1101 END LOOP;
1102 --Standard check of commit
1103 IF FND_API.To_Boolean ( p_commit ) THEN
1104 COMMIT WORK;
1105 END IF;
1106 --Standard call to get message count and if count=1, get the message
1107 FND_MSG_PUB.Count_And_Get (
1108 p_encoded => FND_API.G_FALSE,
1109 p_count => x_msg_count,
1110 p_data => x_msg_data
1111 );
1112 EXCEPTION
1113 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1114 ROLLBACK TO Add_ItemKeyword_Pub;
1115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1116 -- Standard call to get message count and if count=1, get the message
1117 FND_MSG_PUB.Count_And_Get (
1118 p_encoded => FND_API.G_FALSE,
1119 p_count => x_msg_count,
1120 p_data => x_msg_data
1121 );
1122 WHEN FND_API.G_EXC_ERROR THEN
1123 ROLLBACK TO Add_ItemKeyword_Pub;
1124 x_return_status := FND_API.G_RET_STS_ERROR;
1125 -- Standard call to get message count and if count=1, get the message
1126 FND_MSG_PUB.Count_And_Get (
1127 p_encoded => FND_API.G_FALSE,
1128 p_count => x_msg_count,
1129 p_data => x_msg_data
1130 );
1131 WHEN OTHERS THEN
1132 ROLLBACK TO Add_ItemKeyword_Pub;
1133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1134 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1135 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1136 END IF;
1137 -- Standard call to get message count and if count=1, get the message
1138 FND_MSG_PUB.Count_And_Get (
1139 p_encoded => FND_API.G_FALSE,
1140 p_count => x_msg_count,
1141 p_data => x_msg_data
1142 );
1143 END Add_ItemKeyword;
1144 --------------------------------------------------------------------------------
1145 PROCEDURE Add_ItemKeyword
1146 (
1147 p_api_version IN NUMBER,
1148 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1149 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1150 x_return_status OUT NOCOPY VARCHAR2,
1151 x_msg_count OUT NOCOPY NUMBER,
1152 x_msg_data OUT NOCOPY VARCHAR2,
1153 p_item_id IN NUMBER,
1154 p_keyword IN VARCHAR2
1155 ) AS
1156 l_char_tab CHAR_TAB_TYPE;
1157 BEGIN
1158 l_char_tab := CHAR_TAB_TYPE();
1159 l_char_tab.extend;
1160 l_char_tab(1) := p_keyword;
1161 --
1162 Add_ItemKeyword
1163 (
1164 p_api_version => p_api_version,
1165 p_init_msg_list => p_init_msg_list,
1166 p_commit => p_commit,
1167 x_return_status => x_return_status,
1168 x_msg_count => x_msg_count,
1169 x_msg_data => x_msg_data,
1170 p_item_id => p_item_id,
1171 p_keyword_tab => l_char_tab
1172 );
1173 end Add_ItemKeyword;
1174 --------------------------------------------------------------------------------
1175 PROCEDURE Delete_ItemKeyword
1176 (
1177 p_api_version IN NUMBER,
1178 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1179 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1180 x_return_status OUT NOCOPY VARCHAR2,
1181 x_msg_count OUT NOCOPY NUMBER,
1182 x_msg_data OUT NOCOPY VARCHAR2,
1183 p_item_id IN NUMBER,
1184 p_keyword_tab IN CHAR_TAB_TYPE
1185 ) AS
1186 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ItemKeyword';
1187 l_api_version CONSTANT NUMBER := 1.0;
1188 --
1189 l_count NUMBER;
1190 l_temp_number NUMBER;
1191 l_date DATE;
1192 --
1193 CURSOR Check_Itemkeyword_csr (p_kword in VARCHAR2) IS
1194 Select
1195 item_keyword_id
1196 From jtf_amv_item_keywords
1197 Where keyword = p_kword
1198 And item_id = p_item_id;
1199 BEGIN
1200 -- Standard call to check for call compatibility.
1201 SAVEPOINT Delete_ItemKeyword_Pub;
1202 IF NOT FND_API.Compatible_API_Call (
1203 l_api_version,
1204 p_api_version,
1205 l_api_name,
1206 G_PKG_NAME) THEN
1207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1208 END IF;
1209 --Initialize message list if p_init_msg_list is TRUE.
1210 IF FND_API.To_Boolean (p_init_msg_list) THEN
1211 FND_MSG_PUB.initialize;
1212 END IF;
1213 -- Initialize API return status to success
1214 x_return_status := FND_API.G_RET_STS_SUCCESS;
1215 --
1216 -- Check if item id is valid.
1217 IF (Is_ItemIdValid(p_item_id) <> TRUE) THEN
1218 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1219 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
1220 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1) ));
1221 FND_MSG_PUB.Add;
1222 END IF;
1223 RAISE FND_API.G_EXC_ERROR;
1224 END IF;
1225 IF (p_keyword_tab IS NOT NULL) THEN
1226 l_count := p_keyword_tab.count;
1227 FOR i IN 1..l_count LOOP
1228 OPEN Check_Itemkeyword_csr( p_keyword_tab(i) );
1229 FETCH Check_Itemkeyword_csr INTO l_temp_number;
1230 IF (Check_Itemkeyword_csr%NOTFOUND) THEN
1231 CLOSE Check_Itemkeyword_csr;
1232 x_return_status := FND_API.G_RET_STS_ERROR;
1233 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1234 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_HASNOT_KEYWORD');
1235 FND_MESSAGE.Set_Token('ID', to_char(p_item_id) );
1236 FND_MESSAGE.Set_Token('KEYWORD', p_keyword_tab(i));
1237 FND_MSG_PUB.Add;
1238 END IF;
1239 ELSE
1240 CLOSE Check_Itemkeyword_csr;
1241 Delete from jtf_amv_item_keywords
1242 Where item_keyword_id = l_temp_number;
1243 END IF;
1244 END LOOP;
1245 ELSE
1246 -- If no keyword specified, delete all the keywords of the item.
1247 Delete from jtf_amv_item_keywords
1248 Where item_id = p_item_id;
1249 END IF;
1250 --Standard check of commit
1251 IF FND_API.To_Boolean ( p_commit ) THEN
1252 COMMIT WORK;
1253 END IF;
1254 --Standard call to get message count and if count=1, get the message
1255 FND_MSG_PUB.Count_And_Get (
1256 p_encoded => FND_API.G_FALSE,
1257 p_count => x_msg_count,
1258 p_data => x_msg_data
1259 );
1260 EXCEPTION
1261 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1262 ROLLBACK TO Delete_ItemKeyword_Pub;
1263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1264 -- Standard call to get message count and if count=1, get the message
1265 FND_MSG_PUB.Count_And_Get (
1266 p_encoded => FND_API.G_FALSE,
1267 p_count => x_msg_count,
1268 p_data => x_msg_data
1269 );
1270 WHEN FND_API.G_EXC_ERROR THEN
1271 ROLLBACK TO Delete_ItemKeyword_Pub;
1272 x_return_status := FND_API.G_RET_STS_ERROR;
1273 -- Standard call to get message count and if count=1, get the message
1274 FND_MSG_PUB.Count_And_Get (
1275 p_encoded => FND_API.G_FALSE,
1276 p_count => x_msg_count,
1277 p_data => x_msg_data
1278 );
1279 WHEN OTHERS THEN
1280 ROLLBACK TO Delete_ItemKeyword_Pub;
1281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1282 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1283 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1284 END IF;
1285 -- Standard call to get message count and if count=1, get the message
1286 FND_MSG_PUB.Count_And_Get (
1287 p_encoded => FND_API.G_FALSE,
1288 p_count => x_msg_count,
1289 p_data => x_msg_data
1290 );
1291 END Delete_ItemKeyword;
1292 --------------------------------------------------------------------------------
1293 PROCEDURE Delete_ItemKeyword
1294 (
1295 p_api_version IN NUMBER,
1296 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1297 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1298 x_return_status OUT NOCOPY VARCHAR2,
1299 x_msg_count OUT NOCOPY NUMBER,
1300 x_msg_data OUT NOCOPY VARCHAR2,
1301 p_item_id IN NUMBER,
1302 p_keyword IN VARCHAR2
1303 ) AS
1304 l_char_tab CHAR_TAB_TYPE;
1305 BEGIN
1306 l_char_tab := CHAR_TAB_TYPE(p_keyword);
1307 Delete_ItemKeyword
1308 (
1309 p_api_version => p_api_version,
1310 p_init_msg_list => p_init_msg_list,
1311 p_commit => p_commit,
1312 x_return_status => x_return_status,
1313 x_msg_count => x_msg_count,
1314 x_msg_data => x_msg_data,
1315 p_item_id => p_item_id,
1316 p_keyword_tab => l_char_tab
1317 );
1318 end Delete_ItemKeyword;
1319 --------------------------------------------------------------------------------
1320 PROCEDURE Replace_ItemKeyword
1321 (
1322 p_api_version IN NUMBER,
1323 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1324 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1325 x_return_status OUT NOCOPY VARCHAR2,
1326 x_msg_count OUT NOCOPY NUMBER,
1327 x_msg_data OUT NOCOPY VARCHAR2,
1328 p_item_id IN NUMBER,
1329 p_keyword_tab IN CHAR_TAB_TYPE
1330 ) AS
1331 l_api_name CONSTANT VARCHAR2(30) := 'Replace_ItemKeyword';
1332 l_api_version CONSTANT NUMBER := 1.0;
1333 l_current_user_id NUMBER := -1;
1334 l_current_login_id NUMBER := -1;
1335 l_current_user_status NUMBER;
1336 --
1337 l_count NUMBER;
1338 l_temp_number NUMBER;
1339 l_date DATE;
1340 --
1341 BEGIN
1342 -- Standard call to check for call compatibility.
1343 SAVEPOINT Replace_ItemKeyword_Pub;
1344 IF NOT FND_API.Compatible_API_Call (
1345 l_api_version,
1346 p_api_version,
1347 l_api_name,
1348 G_PKG_NAME) THEN
1349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1350 END IF;
1351 --Initialize message list if p_init_msg_list is TRUE.
1352 IF FND_API.To_Boolean (p_init_msg_list) THEN
1353 FND_MSG_PUB.initialize;
1354 END IF;
1355 -- Initialize API return status to success
1356 x_return_status := FND_API.G_RET_STS_SUCCESS;
1357 -- Check if item id is valid.
1358 IF (Is_ItemIdValid(p_item_id) <> TRUE) THEN
1359 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1360 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
1361 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1) ));
1362 FND_MSG_PUB.Add;
1363 END IF;
1364 RAISE FND_API.G_EXC_ERROR;
1365 END IF;
1366 -- Delete all the item's original keyword
1367 Delete from jtf_amv_item_keywords
1368 Where item_id = p_item_id;
1369 -- now add the new keywords
1370 Add_ItemKeyword
1371 (
1372 p_api_version => p_api_version,
1373 p_init_msg_list => p_init_msg_list,
1374 p_commit => p_commit,
1375 x_return_status => x_return_status,
1376 x_msg_count => x_msg_count,
1377 x_msg_data => x_msg_data,
1378 p_item_id => p_item_id,
1379 p_keyword_tab => p_keyword_tab
1380 );
1381 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1383 ELSIF ( x_return_status = FND_API.G_RET_STS_ERROR) THEN
1384 RAISE FND_API.G_EXC_ERROR;
1385 END IF;
1386 --Standard call to get message count and if count=1, get the message
1387 FND_MSG_PUB.Count_And_Get (
1388 p_encoded => FND_API.G_FALSE,
1389 p_count => x_msg_count,
1390 p_data => x_msg_data
1391 );
1392 EXCEPTION
1393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1394 ROLLBACK TO Replace_ItemKeyword_Pub;
1395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1396 -- Standard call to get message count and if count=1, get the message
1397 FND_MSG_PUB.Count_And_Get (
1398 p_encoded => FND_API.G_FALSE,
1399 p_count => x_msg_count,
1400 p_data => x_msg_data
1401 );
1402 WHEN FND_API.G_EXC_ERROR THEN
1403 ROLLBACK TO Replace_ItemKeyword_Pub;
1404 x_return_status := FND_API.G_RET_STS_ERROR;
1405 -- Standard call to get message count and if count=1, get the message
1406 FND_MSG_PUB.Count_And_Get (
1407 p_encoded => FND_API.G_FALSE,
1408 p_count => x_msg_count,
1409 p_data => x_msg_data
1410 );
1411 WHEN OTHERS THEN
1412 ROLLBACK TO Replace_ItemKeyword_Pub;
1413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1414 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1415 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1416 END IF;
1417 -- Standard call to get message count and if count=1, get the message
1418 FND_MSG_PUB.Count_And_Get (
1419 p_encoded => FND_API.G_FALSE,
1420 p_count => x_msg_count,
1421 p_data => x_msg_data
1422 );
1423 END Replace_ItemKeyword;
1424 --------------------------------------------------------------------------------
1425 PROCEDURE Get_ItemKeyword
1426 (
1427 p_api_version IN NUMBER,
1428 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1429 x_return_status OUT NOCOPY VARCHAR2,
1430 x_msg_count OUT NOCOPY NUMBER,
1431 x_msg_data OUT NOCOPY VARCHAR2,
1432 p_item_id IN NUMBER,
1433 x_keyword_tab OUT NOCOPY CHAR_TAB_TYPE
1434 ) AS
1435 l_api_name CONSTANT VARCHAR2(30) := 'Get_ItemKeyword';
1436 l_api_version CONSTANT NUMBER := 1.0;
1437 --
1438 l_fetch_count NUMBER := 0;
1439 CURSOR Get_Keyword_csr is
1440 Select
1441 KEYWORD
1442 from JTF_AMV_ITEM_KEYWORDS
1443 Where item_id = p_item_id;
1444 --
1445 BEGIN
1446 -- Standard call to check for call compatibility.
1447 IF NOT FND_API.Compatible_API_Call (
1448 l_api_version,
1449 p_api_version,
1450 l_api_name,
1451 G_PKG_NAME) THEN
1452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1453 END IF;
1454 --Initialize message list if p_init_msg_list is TRUE.
1455 IF FND_API.To_Boolean (p_init_msg_list) THEN
1456 FND_MSG_PUB.initialize;
1457 END IF;
1458 -- Initialize API return status to success
1459 x_return_status := FND_API.G_RET_STS_SUCCESS;
1460 -- Check if item id is valid.
1461 IF (Is_ItemIdValid(p_item_id) <> TRUE) THEN
1462 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1463 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
1464 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1) ));
1465 FND_MSG_PUB.Add;
1466 END IF;
1467 RAISE FND_API.G_EXC_ERROR;
1468 END IF;
1469 --Execute the SQL statements to get records
1470 x_keyword_tab := CHAR_TAB_TYPE();
1471 FOR kword in Get_Keyword_csr LOOP
1472 l_fetch_count := l_fetch_count + 1;
1473 x_keyword_tab.extend;
1474 x_keyword_tab(l_fetch_count) := kword.keyword;
1475 END LOOP;
1476 --Standard call to get message count and if count=1, get the message
1477 FND_MSG_PUB.Count_And_Get (
1478 p_encoded => FND_API.G_FALSE,
1479 p_count => x_msg_count,
1480 p_data => x_msg_data
1481 );
1482 EXCEPTION
1483 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1485 -- Standard call to get message count and if count=1, get the message
1486 FND_MSG_PUB.Count_And_Get (
1487 p_encoded => FND_API.G_FALSE,
1488 p_count => x_msg_count,
1489 p_data => x_msg_data
1490 );
1491 WHEN FND_API.G_EXC_ERROR THEN
1492 x_return_status := FND_API.G_RET_STS_ERROR;
1493 -- Standard call to get message count and if count=1, get the message
1494 FND_MSG_PUB.Count_And_Get (
1495 p_encoded => FND_API.G_FALSE,
1496 p_count => x_msg_count,
1497 p_data => x_msg_data
1498 );
1499 WHEN OTHERS THEN
1500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1501 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1502 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1503 END IF;
1504 -- Standard call to get message count and if count=1, get the message
1505 FND_MSG_PUB.Count_And_Get (
1506 p_encoded => FND_API.G_FALSE,
1507 p_count => x_msg_count,
1508 p_data => x_msg_data
1509 );
1510 END Get_ItemKeyword;
1511 --------------------------------------------------------------------------------
1512 ------------------------------ ITEM_AUTHOR -------------------------------------
1513 PROCEDURE Add_ItemAuthor
1514 (
1515 p_api_version IN NUMBER,
1516 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1517 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1518 x_return_status OUT NOCOPY VARCHAR2,
1519 x_msg_count OUT NOCOPY NUMBER,
1520 x_msg_data OUT NOCOPY VARCHAR2,
1521 p_item_id IN NUMBER,
1522 p_author_tab IN CHAR_TAB_TYPE
1523 ) AS
1524 l_api_name CONSTANT VARCHAR2(30) := 'Add_ItemAuthor';
1525 l_api_version CONSTANT NUMBER := 1.0;
1526 --
1527 l_current_user_id NUMBER := CURRENT_USER_ID;
1528 l_current_login_id NUMBER := CURRENT_LOGIN_ID;
1529 l_count NUMBER;
1530 l_temp_number NUMBER;
1531 l_date DATE;
1532 --
1533 CURSOR Check_Itemauthor_csr (p_author in VARCHAR2) IS
1534 Select
1535 item_author_id
1536 From jtf_amv_item_authors
1537 Where author = p_author
1538 And item_id = p_item_id;
1539 --
1540 CURSOR Get_IDandDate_csr is
1541 Select jtf_amv_item_authors_s.nextval, sysdate
1542 From Dual;
1543 --
1544 BEGIN
1545 -- Standard call to check for call compatibility.
1546 SAVEPOINT Add_ItemAuthor_Pub;
1547 IF NOT FND_API.Compatible_API_Call (
1548 l_api_version,
1549 p_api_version,
1550 l_api_name,
1551 G_PKG_NAME) THEN
1552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1553 END IF;
1554 --Initialize message list if p_init_msg_list is TRUE.
1555 IF FND_API.To_Boolean (p_init_msg_list) THEN
1556 FND_MSG_PUB.initialize;
1557 END IF;
1558 -- Initialize API return status to success
1559 x_return_status := FND_API.G_RET_STS_SUCCESS;
1560 -- Check if item id is valid.
1561 IF (Is_ItemIdValid(p_item_id) <> TRUE) THEN
1562 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1563 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
1564 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1) ));
1565 FND_MSG_PUB.Add;
1566 END IF;
1567 RAISE FND_API.G_EXC_ERROR;
1568 END IF;
1569 IF (p_author_tab is null) THEN
1570 l_count := 0;
1571 ELSE
1572 l_count := p_author_tab.count;
1573 END IF;
1574 FOR i IN 1..l_count LOOP
1575 OPEN Check_Itemauthor_csr( p_author_tab(i) );
1576 FETCH Check_Itemauthor_csr INTO l_temp_number;
1577 IF (Check_Itemauthor_csr%FOUND) THEN
1578 CLOSE Check_Itemauthor_csr;
1579 x_return_status := FND_API.G_RET_STS_ERROR;
1580 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1581 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_HAS_AUTHOR');
1582 FND_MESSAGE.Set_Token('ID', to_char(p_item_id) );
1583 FND_MESSAGE.Set_Token('AUTHOR', p_author_tab(i));
1584 FND_MSG_PUB.Add;
1585 END IF;
1586 ELSE
1587 CLOSE Check_Itemauthor_csr;
1588 OPEN Get_IDandDate_csr;
1589 FETCH Get_IDandDate_csr Into l_temp_number, l_date;
1590 CLOSE Get_IDandDate_csr;
1591 Insert Into jtf_amv_item_authors
1592 (
1593 ITEM_AUTHOR_ID,
1594 OBJECT_VERSION_NUMBER,
1595 LAST_UPDATE_DATE,
1596 LAST_UPDATED_BY,
1597 CREATION_DATE,
1598 CREATED_BY,
1599 LAST_UPDATE_LOGIN,
1600 ITEM_ID,
1601 AUTHOR
1602 ) VALUES
1603 (
1604 l_temp_number,
1605 1,
1606 l_date,
1607 l_current_user_id,
1608 l_date,
1609 l_current_user_id,
1610 l_current_login_id,
1611 p_item_id,
1612 p_author_tab(i)
1613 );
1614 END IF;
1615 END LOOP;
1616 --Standard check of commit
1617 IF FND_API.To_Boolean ( p_commit ) THEN
1618 COMMIT WORK;
1619 END IF;
1620 --Standard call to get message count and if count=1, get the message
1621 FND_MSG_PUB.Count_And_Get (
1622 p_encoded => FND_API.G_FALSE,
1623 p_count => x_msg_count,
1624 p_data => x_msg_data
1625 );
1626 EXCEPTION
1627 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1628 ROLLBACK TO Add_ItemAuthor_Pub;
1629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1630 -- Standard call to get message count and if count=1, get the message
1631 FND_MSG_PUB.Count_And_Get (
1632 p_encoded => FND_API.G_FALSE,
1633 p_count => x_msg_count,
1634 p_data => x_msg_data
1635 );
1636 WHEN FND_API.G_EXC_ERROR THEN
1637 ROLLBACK TO Add_ItemAuthor_Pub;
1638 x_return_status := FND_API.G_RET_STS_ERROR;
1639 -- Standard call to get message count and if count=1, get the message
1640 FND_MSG_PUB.Count_And_Get (
1641 p_encoded => FND_API.G_FALSE,
1642 p_count => x_msg_count,
1643 p_data => x_msg_data
1644 );
1645 WHEN OTHERS THEN
1646 ROLLBACK TO Add_ItemAuthor_Pub;
1647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1648 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1649 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1650 END IF;
1651 -- Standard call to get message count and if count=1, get the message
1652 FND_MSG_PUB.Count_And_Get (
1653 p_encoded => FND_API.G_FALSE,
1654 p_count => x_msg_count,
1655 p_data => x_msg_data
1656 );
1657 END Add_ItemAuthor;
1658 --------------------------------------------------------------------------------
1659 PROCEDURE Add_ItemAuthor
1660 (
1661 p_api_version IN NUMBER,
1662 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1663 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1664 x_return_status OUT NOCOPY VARCHAR2,
1665 x_msg_count OUT NOCOPY NUMBER,
1666 x_msg_data OUT NOCOPY VARCHAR2,
1667 p_item_id IN NUMBER,
1668 p_author IN VARCHAR2
1669 ) AS
1670 l_char_tab CHAR_TAB_TYPE;
1671 BEGIN
1672 l_char_tab := CHAR_TAB_TYPE(p_author);
1673 --
1674 Add_ItemAuthor
1675 (
1676 p_api_version => p_api_version,
1677 p_init_msg_list => p_init_msg_list,
1678 p_commit => p_commit,
1679 x_return_status => x_return_status,
1680 x_msg_count => x_msg_count,
1681 x_msg_data => x_msg_data,
1682 p_item_id => p_item_id,
1683 p_author_tab => l_char_tab
1684 );
1685 end Add_ItemAuthor;
1686 --------------------------------------------------------------------------------
1687 PROCEDURE Delete_ItemAuthor
1688 (
1689 p_api_version IN NUMBER,
1690 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1691 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1692 x_return_status OUT NOCOPY VARCHAR2,
1693 x_msg_count OUT NOCOPY NUMBER,
1694 x_msg_data OUT NOCOPY VARCHAR2,
1695 p_item_id IN NUMBER,
1696 p_author_tab IN CHAR_TAB_TYPE
1697 ) AS
1698 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ItemAuthor';
1699 l_api_version CONSTANT NUMBER := 1.0;
1700 --
1701 l_count NUMBER;
1702 l_temp_number NUMBER;
1703 l_date DATE;
1704 --
1705 CURSOR Check_Itemauthor_csr (p_author in VARCHAR2) IS
1706 Select
1707 item_author_id
1708 From jtf_amv_item_authors
1709 Where author = p_author
1710 And item_id = p_item_id;
1711 BEGIN
1712 -- Standard call to check for call compatibility.
1713 SAVEPOINT Delete_ItemAuthor_Pub;
1714 IF NOT FND_API.Compatible_API_Call (
1715 l_api_version,
1716 p_api_version,
1717 l_api_name,
1718 G_PKG_NAME) THEN
1719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1720 END IF;
1721 --Initialize message list if p_init_msg_list is TRUE.
1722 IF FND_API.To_Boolean (p_init_msg_list) THEN
1723 FND_MSG_PUB.initialize;
1724 END IF;
1725 -- Initialize API return status to success
1726 x_return_status := FND_API.G_RET_STS_SUCCESS;
1727 --
1728 -- Check if item id is valid.
1729 IF (Is_ItemIdValid(p_item_id) <> TRUE) THEN
1730 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1731 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
1732 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1) ));
1733 FND_MSG_PUB.Add;
1734 END IF;
1735 RAISE FND_API.G_EXC_ERROR;
1736 END IF;
1737 IF (p_author_tab IS NOT NULL) THEN
1738 l_count := p_author_tab.count;
1739 FOR i IN 1..l_count LOOP
1740 OPEN Check_Itemauthor_csr( p_author_tab(i) );
1741 FETCH Check_Itemauthor_csr INTO l_temp_number;
1742 IF (Check_Itemauthor_csr%NOTFOUND) THEN
1743 CLOSE Check_Itemauthor_csr;
1744 x_return_status := FND_API.G_RET_STS_ERROR;
1745 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1746 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_HASNOT_AUTHOR');
1747 FND_MESSAGE.Set_Token('ID', to_char(p_item_id) );
1748 FND_MESSAGE.Set_Token('AUTHOR', p_author_tab(i));
1749 FND_MSG_PUB.Add;
1750 END IF;
1751 ELSE
1752 CLOSE Check_Itemauthor_csr;
1753 Delete from jtf_amv_item_authors
1754 Where item_author_id = l_temp_number;
1755 END IF;
1756 END LOOP;
1757 ELSE
1758 -- If no author specified, delete all the authors of the item.
1759 Delete from jtf_amv_item_authors
1760 Where item_id = p_item_id;
1761 END IF;
1762 --Standard check of commit
1763 IF FND_API.To_Boolean ( p_commit ) THEN
1764 COMMIT WORK;
1765 END IF;
1766 --Standard call to get message count and if count=1, get the message
1767 FND_MSG_PUB.Count_And_Get (
1768 p_encoded => FND_API.G_FALSE,
1769 p_count => x_msg_count,
1770 p_data => x_msg_data
1771 );
1772 EXCEPTION
1773 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1774 ROLLBACK TO Delete_ItemAuthor_Pub;
1775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1776 -- Standard call to get message count and if count=1, get the message
1777 FND_MSG_PUB.Count_And_Get (
1778 p_encoded => FND_API.G_FALSE,
1779 p_count => x_msg_count,
1780 p_data => x_msg_data
1781 );
1782 WHEN FND_API.G_EXC_ERROR THEN
1783 ROLLBACK TO Delete_ItemAuthor_Pub;
1784 x_return_status := FND_API.G_RET_STS_ERROR;
1785 -- Standard call to get message count and if count=1, get the message
1786 FND_MSG_PUB.Count_And_Get (
1787 p_encoded => FND_API.G_FALSE,
1788 p_count => x_msg_count,
1789 p_data => x_msg_data
1790 );
1791 WHEN OTHERS THEN
1792 ROLLBACK TO Delete_ItemAuthor_Pub;
1793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1794 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1795 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1796 END IF;
1797 -- Standard call to get message count and if count=1, get the message
1798 FND_MSG_PUB.Count_And_Get (
1799 p_encoded => FND_API.G_FALSE,
1800 p_count => x_msg_count,
1801 p_data => x_msg_data
1802 );
1803 END Delete_ItemAuthor;
1804 --------------------------------------------------------------------------------
1805 PROCEDURE Delete_ItemAuthor
1806 (
1807 p_api_version IN NUMBER,
1808 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1809 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1810 x_return_status OUT NOCOPY VARCHAR2,
1811 x_msg_count OUT NOCOPY NUMBER,
1812 x_msg_data OUT NOCOPY VARCHAR2,
1813 p_item_id IN NUMBER,
1814 p_author IN VARCHAR2
1815 ) AS
1816 l_char_tab CHAR_TAB_TYPE;
1817 BEGIN
1818 l_char_tab := CHAR_TAB_TYPE(p_author);
1819 Delete_ItemAuthor
1820 (
1821 p_api_version => p_api_version,
1822 p_init_msg_list => p_init_msg_list,
1823 p_commit => p_commit,
1824 x_return_status => x_return_status,
1825 x_msg_count => x_msg_count,
1826 x_msg_data => x_msg_data,
1827 p_item_id => p_item_id,
1828 p_author_tab => l_char_tab
1829 );
1830 end Delete_ItemAuthor;
1831 --------------------------------------------------------------------------------
1832 PROCEDURE Replace_ItemAuthor
1833 (
1834 p_api_version IN NUMBER,
1835 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1836 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1837 x_return_status OUT NOCOPY VARCHAR2,
1838 x_msg_count OUT NOCOPY NUMBER,
1839 x_msg_data OUT NOCOPY VARCHAR2,
1840 p_item_id IN NUMBER,
1841 p_author_tab IN CHAR_TAB_TYPE
1842 ) AS
1843 l_api_name CONSTANT VARCHAR2(30) := 'Replace_ItemAuthor';
1844 l_api_version CONSTANT NUMBER := 1.0;
1845 l_current_user_id NUMBER := -1;
1846 l_current_login_id NUMBER := -1;
1847 l_current_user_status NUMBER;
1848 --
1849 l_count NUMBER;
1850 l_temp_number NUMBER;
1851 l_date DATE;
1852 --
1853 BEGIN
1854 -- Standard call to check for call compatibility.
1855 SAVEPOINT Replace_ItemAuthor_Pub;
1856 IF NOT FND_API.Compatible_API_Call (
1857 l_api_version,
1858 p_api_version,
1859 l_api_name,
1860 G_PKG_NAME) THEN
1861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1862 END IF;
1863 --Initialize message list if p_init_msg_list is TRUE.
1864 IF FND_API.To_Boolean (p_init_msg_list) THEN
1865 FND_MSG_PUB.initialize;
1866 END IF;
1867 -- Initialize API return status to success
1868 x_return_status := FND_API.G_RET_STS_SUCCESS;
1869 -- Check if item id is valid.
1870 IF (Is_ItemIdValid(p_item_id) <> TRUE) THEN
1871 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1872 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
1873 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1) ));
1874 FND_MSG_PUB.Add;
1875 END IF;
1876 RAISE FND_API.G_EXC_ERROR;
1877 END IF;
1878 -- Delete all the item's original author
1879 Delete from jtf_amv_item_authors
1880 Where item_id = p_item_id;
1881 -- now add the new authors
1882 Add_ItemAuthor
1883 (
1884 p_api_version => p_api_version,
1885 p_init_msg_list => p_init_msg_list,
1886 p_commit => p_commit,
1887 x_return_status => x_return_status,
1888 x_msg_count => x_msg_count,
1889 x_msg_data => x_msg_data,
1890 p_item_id => p_item_id,
1891 p_author_tab => p_author_tab
1892 );
1893 --Standard call to get message count and if count=1, get the message
1894 FND_MSG_PUB.Count_And_Get (
1895 p_encoded => FND_API.G_FALSE,
1896 p_count => x_msg_count,
1897 p_data => x_msg_data
1898 );
1899 EXCEPTION
1900 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1901 ROLLBACK TO Replace_ItemAuthor_Pub;
1902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1903 -- Standard call to get message count and if count=1, get the message
1904 FND_MSG_PUB.Count_And_Get (
1905 p_encoded => FND_API.G_FALSE,
1906 p_count => x_msg_count,
1907 p_data => x_msg_data
1908 );
1909 WHEN FND_API.G_EXC_ERROR THEN
1910 ROLLBACK TO Replace_ItemAuthor_Pub;
1911 x_return_status := FND_API.G_RET_STS_ERROR;
1912 -- Standard call to get message count and if count=1, get the message
1913 FND_MSG_PUB.Count_And_Get (
1914 p_encoded => FND_API.G_FALSE,
1915 p_count => x_msg_count,
1916 p_data => x_msg_data
1917 );
1918 WHEN OTHERS THEN
1919 ROLLBACK TO Replace_ItemAuthor_Pub;
1920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1921 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1922 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1923 END IF;
1924 -- Standard call to get message count and if count=1, get the message
1925 FND_MSG_PUB.Count_And_Get (
1926 p_encoded => FND_API.G_FALSE,
1927 p_count => x_msg_count,
1928 p_data => x_msg_data
1929 );
1930 END Replace_ItemAuthor;
1931 --------------------------------------------------------------------------------
1932 PROCEDURE Get_ItemAuthor
1933 (
1934 p_api_version IN NUMBER,
1935 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1936 x_return_status OUT NOCOPY VARCHAR2,
1937 x_msg_count OUT NOCOPY NUMBER,
1938 x_msg_data OUT NOCOPY VARCHAR2,
1939 p_item_id IN NUMBER,
1940 x_author_tab OUT NOCOPY CHAR_TAB_TYPE
1941 ) AS
1942 l_api_name CONSTANT VARCHAR2(30) := 'Get_ItemAuthor';
1943 l_api_version CONSTANT NUMBER := 1.0;
1944 --
1945 l_fetch_count NUMBER := 0;
1946 CURSOR Get_Author_csr is
1947 Select
1948 AUTHOR
1949 from JTF_AMV_ITEM_AUTHORS
1950 Where item_id = p_item_id;
1951 --
1952 BEGIN
1953 -- Standard call to check for call compatibility.
1954 IF NOT FND_API.Compatible_API_Call (
1955 l_api_version,
1956 p_api_version,
1957 l_api_name,
1958 G_PKG_NAME) THEN
1959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1960 END IF;
1961 --Initialize message list if p_init_msg_list is TRUE.
1962 IF FND_API.To_Boolean (p_init_msg_list) THEN
1963 FND_MSG_PUB.initialize;
1964 END IF;
1965 -- Initialize API return status to success
1966 x_return_status := FND_API.G_RET_STS_SUCCESS;
1967 -- Check if item id is valid.
1968 IF (Is_ItemIdValid(p_item_id) <> TRUE) THEN
1969 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1970 FND_MESSAGE.Set_name('JTF','JTF_AMV_ITEM_RECORD_MISSING');
1971 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1) ));
1972 FND_MSG_PUB.Add;
1973 END IF;
1974 RAISE FND_API.G_EXC_ERROR;
1975 END IF;
1976 --Execute the SQL statements to get records
1977 x_author_tab := CHAR_TAB_TYPE();
1978 FOR rec in Get_Author_csr LOOP
1979 l_fetch_count := l_fetch_count + 1;
1980 x_author_tab.extend;
1981 x_author_tab(l_fetch_count) := rec.author;
1982 END LOOP;
1983 --Standard call to get message count and if count=1, get the message
1984 FND_MSG_PUB.Count_And_Get (
1985 p_encoded => FND_API.G_FALSE,
1986 p_count => x_msg_count,
1987 p_data => x_msg_data
1988 );
1989 EXCEPTION
1990 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1991 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1992 -- Standard call to get message count and if count=1, get the message
1993 FND_MSG_PUB.Count_And_Get (
1994 p_encoded => FND_API.G_FALSE,
1995 p_count => x_msg_count,
1996 p_data => x_msg_data
1997 );
1998 WHEN FND_API.G_EXC_ERROR THEN
1999 x_return_status := FND_API.G_RET_STS_ERROR;
2000 -- Standard call to get message count and if count=1, get the message
2001 FND_MSG_PUB.Count_And_Get (
2002 p_encoded => FND_API.G_FALSE,
2003 p_count => x_msg_count,
2004 p_data => x_msg_data
2005 );
2006 WHEN OTHERS THEN
2007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2008 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2009 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2010 END IF;
2011 -- Standard call to get message count and if count=1, get the message
2012 FND_MSG_PUB.Count_And_Get (
2013 p_encoded => FND_API.G_FALSE,
2014 p_count => x_msg_count,
2015 p_data => x_msg_data
2016 );
2017 END Get_ItemAuthor;
2018 --
2019 --------------------------------------------------------------------------------
2020 --------------------------------------------------------------------------------
2021 END jtf_amv_item_pub;
[Home] [Help]