[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_STATUSES_PUB
Source
1 PACKAGE BODY EGO_ITEM_STATUSES_PUB AS
2 /* $Header: EGOITEMSTATUSB.pls 120.0 2005/05/26 22:41:27 appldev noship $ */
3
4 -- ------------------------------------------------------------
5 -- -------------- Global variables and constants --------------
6 -- ------------------------------------------------------------
7
8 g_pkg_name VARCHAR2(30) := 'EGO_ITEM_STATUSES_PUB';
9 g_current_user_id NUMBER := EGO_SCTX.Get_User_Id();
10 g_current_login_id NUMBER := FND_GLOBAL.Login_Id;
11
12 G_DUPLICATE_EXCEPTION EXCEPTION;
13
14 -- Character-set independent NEWLINE, TAB and WHITESPACE
15
16 NEWLINE CONSTANT VARCHAR2(4) := fnd_global.newline;
17 MAX_SEG_SIZE CONSTANT NUMBER := 200;
18
19 ----------------------------------------------------------------------
20
21 PROCEDURE Create_Item_Status
22 (
23 p_api_version IN NUMBER
24 , p_item_status_code IN VARCHAR2
25 , p_item_status_code_tl IN VARCHAR2
26 , p_description IN VARCHAR2
27 , p_inactive_date IN VARCHAR2
28 , p_attribute1 IN VARCHAR2
29 , p_attribute2 IN VARCHAR2
30 , p_attribute3 IN VARCHAR2
31 , p_attribute4 IN VARCHAR2
32 , p_attribute5 IN VARCHAR2
33 , p_attribute6 IN VARCHAR2
34 , p_attribute7 IN VARCHAR2
35 , p_attribute8 IN VARCHAR2
36 , p_attribute9 IN VARCHAR2
37 , p_attribute10 IN VARCHAR2
38 , p_attribute11 IN VARCHAR2
39 , p_attribute12 IN VARCHAR2
40 , p_attribute13 IN VARCHAR2
41 , p_attribute14 IN VARCHAR2
42 , p_attribute15 IN VARCHAR2
43 , p_attribute_category IN VARCHAR2
44 , p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
45 , p_commit IN VARCHAR2 := fnd_api.g_FALSE
46 , x_return_status OUT NOCOPY VARCHAR2
47 , x_errorcode OUT NOCOPY NUMBER
48 , x_msg_count OUT NOCOPY NUMBER
49 , x_msg_data OUT NOCOPY VARCHAR2
50 ) IS
51
52 l_api_name CONSTANT VARCHAR2(30) := 'Create_Item_Status';
53 l_api_version CONSTANT NUMBER := 1.0;
54 l_object_id fnd_objects.object_id%TYPE;
55
56 -- General variables
57
58 l_Sysdate DATE := Sysdate;
59 l_language VARCHAR2(4) := userenv('LANG');
60 l_count NUMBER;
61 l_rowid VARCHAR2(1000);
62 --------------------------------------------------
63
64 BEGIN
65
66 -- Standard Start of API savepoint
67 SAVEPOINT Create_Item_Status_PUB;
68
69 IF NOT FND_API.Compatible_API_Call (l_api_version,
70 p_api_version,
71 l_api_name,
72 G_PKG_NAME)
73 THEN
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
75 END IF;
76 -- Initialize API message list if necessary.
77 -- Initialize message list if p_init_msg_list is set to TRUE.
78 IF FND_API.to_Boolean(p_init_msg_list) THEN
79 FND_MSG_PUB.initialize;
80 END IF;
81
82 ------------------------------
83
84 -- Check if the internal name already exists on a NON-END-DATED item status
85
86 SELECT COUNT (*) INTO l_count
87 FROM
88 EGO_ITEM_STATUS_V
89 WHERE
90 ITEM_STATUS_CODE = p_item_status_code;
91
92 IF (l_count > 0)
93 THEN
94 RAISE G_DUPLICATE_EXCEPTION;
95 END IF;
96
97 MTL_ITEM_STATUS_PKG.INSERT_ROW(
98 X_ROWID => l_rowid,
99 X_INVENTORY_ITEM_STATUS_CODE => p_item_status_code,
100 X_DISABLE_DATE => p_inactive_date,
101 X_ATTRIBUTE_CATEGORY => p_attribute_category,
102 X_ATTRIBUTE1 => p_attribute1,
103 X_ATTRIBUTE2 => p_attribute2,
104 X_ATTRIBUTE3 => p_attribute3,
105 X_ATTRIBUTE4 => p_attribute4,
106 X_ATTRIBUTE5 => p_attribute5,
107 X_ATTRIBUTE6 => p_attribute6,
108 X_ATTRIBUTE7 => p_attribute7,
109 X_ATTRIBUTE8 => p_attribute8,
110 X_ATTRIBUTE9 => p_attribute9,
111 X_ATTRIBUTE10 => p_attribute10,
112 X_ATTRIBUTE11 => p_attribute11,
113 X_ATTRIBUTE12 => p_attribute12,
114 X_ATTRIBUTE13 => p_attribute13,
115 X_ATTRIBUTE14 => p_attribute14,
116 X_ATTRIBUTE15 => p_attribute15,
117 X_REQUEST_ID => NULL,
118 X_PROGRAM_APPLICATION_ID => NULL,
119 X_PROGRAM_ID => NULL,
120 X_PROGRAM_UPDATE_DATE => NULL,
121 X_INVENTORY_ITEM_STATUS_CODE_T=> p_item_status_code_tl,
122 X_DESCRIPTION => p_description,
123 X_CREATION_DATE=> l_Sysdate,
124 X_CREATED_BY => g_current_user_id,
125 X_LAST_UPDATE_DATE => l_Sysdate,
126 X_LAST_UPDATED_BY => g_current_user_id,
127 X_LAST_UPDATE_LOGIN => g_current_user_id);
128
129 /*MLS ITEM STATUS
130 INSERT INTO MTL_ITEM_STATUS
131 (
132 INVENTORY_ITEM_STATUS_CODE
133 , INVENTORY_ITEM_STATUS_CODE_TL
134 , DESCRIPTION
135 , DISABLE_DATE
136 , CREATED_BY
137 , CREATION_DATE
138 , LAST_UPDATED_BY
139 , LAST_UPDATE_DATE
140 , LAST_UPDATE_LOGIN
141 , ATTRIBUTE1
142 , ATTRIBUTE2
143 , ATTRIBUTE3
144 , ATTRIBUTE4
145 , ATTRIBUTE5
146 , ATTRIBUTE6
147 , ATTRIBUTE7
148 , ATTRIBUTE8
149 , ATTRIBUTE9
150 , ATTRIBUTE10
151 , ATTRIBUTE11
152 , ATTRIBUTE12
153 , ATTRIBUTE13
154 , ATTRIBUTE14
155 , ATTRIBUTE15
156 , ATTRIBUTE_CATEGORY
157 )
158 VALUES
159 (
160 p_item_status_code
161 , p_item_status_code_tl
162 , p_description
163 , p_inactive_date
164 , g_current_user_id
165 , l_Sysdate
166 , g_current_user_id
167 , l_Sysdate
168 , g_current_login_id
169 , p_attribute1
170 , p_attribute2
171 , p_attribute3
172 , p_attribute4
173 , p_attribute5
174 , p_attribute6
175 , p_attribute7
176 , p_attribute8
177 , p_attribute9
178 , p_attribute10
179 , p_attribute11
180 , p_attribute12
181 , p_attribute13
182 , p_attribute14
183 ,p_attribute15
184 ,p_attribute_category
185 );
186 */
187 ------------------------------
188
189 -- Standard check of p_commit
190 IF FND_API.To_Boolean(p_commit) THEN
191 COMMIT WORK;
192 END IF;
193
194 x_return_status := FND_API.G_RET_STS_SUCCESS;
195
196 ------------------------------
197
198 -- Standard call to get message count and if count is 1,
199 -- get message info.
200 -- The client will directly display the x_msg_data (which is already
201 -- translated) if the x_msg_count = 1;
202 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
203 -- Server-side procedure to access the messages, and consolidate them
204 -- and display (or) to display one message after another.
205
206 FND_MSG_PUB.Count_And_Get
207 ( p_count => x_msg_count,
208 p_data => x_msg_data
209 );
210
211 --------------------------------------------------
212
213 EXCEPTION
214 WHEN FND_API.G_EXC_ERROR THEN
215 ROLLBACK TO Create_Item_Status_PUB;
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 FND_MSG_PUB.Count_And_Get
218 ( p_count => x_msg_count,
219 p_data => x_msg_data
220 );
221
222 x_msg_data := 'Executing - ' || G_PKG_NAME || '.' || l_api_name || ' ' || SQLERRM;
223
224 WHEN G_DUPLICATE_EXCEPTION THEN
225 ROLLBACK TO Create_Item_Status_PUB;
226 x_return_status := FND_API.G_RET_STS_ERROR;
227 FND_MSG_PUB.Count_And_Get
228 ( p_count => x_msg_count,
229 p_data => x_msg_data
230 );
231
232 x_msg_data := 'EGO_ITEM_STATUS_CODE_EXISTS';
233
234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
235 ROLLBACK TO Create_Item_Status_PUB;
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
237 FND_MSG_PUB.Count_And_Get
238 ( p_count => x_msg_count,
239 p_data => x_msg_data
240 );
241 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
242 WHEN OTHERS THEN
243 ROLLBACK TO Create_Item_Status_PUB;
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
245 IF FND_MSG_PUB.Check_Msg_Level
246 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
247 THEN
248 FND_MSG_PUB.Add_Exc_Msg
249 ( G_PKG_NAME,
250 l_api_name
251 );
252 END IF;
253 FND_MSG_PUB.Count_And_Get
254 ( p_count => x_msg_count,
255 p_data => x_msg_data
256 );
257 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
258
259 END Create_Item_Status;
260
261 ----------------------------------------------------------------------
262
263
264 PROCEDURE Update_Item_Status
265 (
266 p_api_version IN NUMBER
267 , p_item_status_code IN VARCHAR2
268 , p_item_status_code_tl IN VARCHAR2
269 , p_description IN VARCHAR2
270 , p_inactive_date IN VARCHAR2
271 , p_attribute1 IN VARCHAR2
272 , p_attribute2 IN VARCHAR2
273 , p_attribute3 IN VARCHAR2
274 , p_attribute4 IN VARCHAR2
275 , p_attribute5 IN VARCHAR2
276 , p_attribute6 IN VARCHAR2
277 , p_attribute7 IN VARCHAR2
278 , p_attribute8 IN VARCHAR2
279 , p_attribute9 IN VARCHAR2
280 , p_attribute10 IN VARCHAR2
281 , p_attribute11 IN VARCHAR2
282 , p_attribute12 IN VARCHAR2
283 , p_attribute13 IN VARCHAR2
284 , p_attribute14 IN VARCHAR2
285 , p_attribute15 IN VARCHAR2
286 , p_attribute_category IN VARCHAR2
287 , p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
288 , p_commit IN VARCHAR2 := fnd_api.g_FALSE
289 , x_return_status OUT NOCOPY VARCHAR2
290 , x_errorcode OUT NOCOPY NUMBER
291 , x_msg_count OUT NOCOPY NUMBER
292 , x_msg_data OUT NOCOPY VARCHAR2
293 )
294 IS
295
296 l_api_name CONSTANT VARCHAR2(30) := 'Update_Item_Status';
297 l_api_version CONSTANT NUMBER := 1.0;
298
299 -- General variables
300
301 l_Sysdate DATE := Sysdate;
302 l_language VARCHAR2(4) := userenv('LANG');
303 l_creation_date DATE;
304
305 --------------------------------------------------
306
307 BEGIN
308
309 -- Standard Start of API savepoint
310 SAVEPOINT Update_Item_Status_PUB;
311
312 IF NOT FND_API.Compatible_API_Call (l_api_version,
313 p_api_version,
314 l_api_name,
315 G_PKG_NAME)
316 THEN
317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
318 END IF;
319 -- Initialize API message list if necessary.
320 -- Initialize message list if p_init_msg_list is set to TRUE.
321 IF FND_API.to_Boolean(p_init_msg_list) THEN
322 FND_MSG_PUB.initialize;
323 END IF;
324
325 ------------------------------
326 MTL_ITEM_STATUS_PKG.UPDATE_ROW(
327 X_INVENTORY_ITEM_STATUS_CODE => p_item_status_code,
328 X_DISABLE_DATE => p_inactive_date,
329 X_ATTRIBUTE_CATEGORY => p_attribute_category,
330 X_ATTRIBUTE1 => p_attribute1,
331 X_ATTRIBUTE2 => p_attribute2,
332 X_ATTRIBUTE3 => p_attribute3,
333 X_ATTRIBUTE4 => p_attribute4,
334 X_ATTRIBUTE5 => p_attribute5,
335 X_ATTRIBUTE6 => p_attribute6,
336 X_ATTRIBUTE7 => p_attribute7,
337 X_ATTRIBUTE8 => p_attribute8,
338 X_ATTRIBUTE9 => p_attribute9,
339 X_ATTRIBUTE10 => p_attribute10,
340 X_ATTRIBUTE11 => p_attribute11,
341 X_ATTRIBUTE12 => p_attribute12,
342 X_ATTRIBUTE13 => p_attribute13,
343 X_ATTRIBUTE14 => p_attribute14,
344 X_ATTRIBUTE15 => p_attribute15,
345 X_REQUEST_ID => NULL,
346 X_PROGRAM_APPLICATION_ID => NULL,
347 X_PROGRAM_ID => NULL,
348 X_PROGRAM_UPDATE_DATE => NULL,
349 X_INVENTORY_ITEM_STATUS_CODE_T=> p_item_status_code_tl,
350 X_DESCRIPTION => p_description,
351 X_LAST_UPDATE_DATE => l_Sysdate,
352 X_LAST_UPDATED_BY => g_current_user_id,
353 X_LAST_UPDATE_LOGIN => g_current_user_id);
354
355 /* MLS Item Status
356 UPDATE MTL_ITEM_STATUS
357 SET
358 DESCRIPTION = p_description
359 , DISABLE_DATE = p_inactive_date
360 , LAST_UPDATED_BY = g_current_user_id
361 , LAST_UPDATE_DATE = l_Sysdate
362 , LAST_UPDATE_LOGIN = g_current_login_id
363 , ATTRIBUTE1 = p_attribute1
364 , ATTRIBUTE2 = p_attribute2
365 , ATTRIBUTE3 = p_attribute3
369 , ATTRIBUTE7 = p_attribute7
366 , ATTRIBUTE4 = p_attribute4
367 , ATTRIBUTE5 = p_attribute5
368 , ATTRIBUTE6 = p_attribute6
370 , ATTRIBUTE8 = p_attribute8
371 , ATTRIBUTE9 = p_attribute9
372 , ATTRIBUTE10 = p_attribute10
373 , ATTRIBUTE11 = p_attribute11
374 , ATTRIBUTE12 = p_attribute12
375 , ATTRIBUTE13 = p_attribute13
376 , ATTRIBUTE14 = p_attribute14
377 , ATTRIBUTE15 = p_attribute15
378 , ATTRIBUTE_CATEGORY = p_attribute_category
379 WHERE
380 INVENTORY_ITEM_STATUS_CODE = p_item_status_code;
381 */
382 ------------------------------
383
384 -- Standard check of p_commit
385 IF FND_API.To_Boolean(p_commit) THEN
386 COMMIT WORK;
387 END IF;
388
389 x_return_status := FND_API.G_RET_STS_SUCCESS;
390
391 ------------------------------
392
393 -- Standard call to get message count and if count is 1,
394 -- get message info.
395 -- The client will directly display the x_msg_data (which is already
396 -- translated) if the x_msg_count = 1;
397 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
398 -- Server-side procedure to access the messages, and consolidate them
399 -- and display (or) to display one message after another.
400
401 FND_MSG_PUB.Count_And_Get
402 ( p_count => x_msg_count,
403 p_data => x_msg_data
404 );
405
406 --------------------------------------------------
407
408 EXCEPTION
409
410 WHEN OTHERS THEN
411 ROLLBACK TO Update_Item_Status_PUB;
412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
413 IF FND_MSG_PUB.Check_Msg_Level
414 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
415 THEN
416 FND_MSG_PUB.Add_Exc_Msg
417 ( G_PKG_NAME,
418 l_api_name
419 );
420 END IF;
421 FND_MSG_PUB.Count_And_Get
422 ( p_count => x_msg_count,
423 p_data => x_msg_data
424 );
425 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
426
427 END Update_Item_Status;
428
429 ----------------------------------------------------------------------
430 PROCEDURE Create_Item_Status_Attr_Values
431 (
432 p_api_version IN NUMBER
433 , p_item_status_code IN VARCHAR2
434 , p_attribute_name IN VARCHAR2
435 , p_attribute_value IN VARCHAR2
436 , p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
437 , p_commit IN VARCHAR2 := fnd_api.g_FALSE
438 , x_return_status OUT NOCOPY VARCHAR2
439 , x_errorcode OUT NOCOPY NUMBER
440 , x_msg_count OUT NOCOPY NUMBER
441 , x_msg_data OUT NOCOPY VARCHAR2
442 ) IS
443
444 l_api_name CONSTANT VARCHAR2(30) := 'Create_Item_Status';
445 l_api_version CONSTANT NUMBER := 1.0;
446 l_object_id fnd_objects.object_id%TYPE;
447
448 -- General variables
449
450 l_Sysdate DATE := Sysdate;
451 l_language VARCHAR2(4) := userenv('LANG');
452 l_count NUMBER;
453
454 --------------------------------------------------
455
456 BEGIN
457
458 -- Standard Start of API savepoint
459 SAVEPOINT Create_Item_Stat_Attr_Vals_Pub;
460
461 IF NOT FND_API.Compatible_API_Call (l_api_version,
462 p_api_version,
463 l_api_name,
464 G_PKG_NAME)
465 THEN
466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
467 END IF;
468 -- Initialize API message list if necessary.
469 -- Initialize message list if p_init_msg_list is set to TRUE.
470 IF FND_API.to_Boolean(p_init_msg_list) THEN
471 FND_MSG_PUB.initialize;
472 END IF;
473
474 ------------------------------
475
476 -- Check if the pk already exists
477
478 SELECT COUNT (*) INTO l_count
479 FROM
480 MTL_STATUS_ATTRIBUTE_VALUES
481 WHERE
482 INVENTORY_ITEM_STATUS_CODE = p_item_status_code
483 AND ATTRIBUTE_NAME = p_attribute_name;
484
485 IF (l_count > 0)
486 THEN
487 RAISE G_DUPLICATE_EXCEPTION;
488 END IF;
489
490 INSERT INTO MTL_STATUS_ATTRIBUTE_VALUES
491 (
492 INVENTORY_ITEM_STATUS_CODE
493 , ATTRIBUTE_NAME
494 , ATTRIBUTE_VALUE
495 , CREATED_BY
496 , CREATION_DATE
497 , LAST_UPDATED_BY
498 , LAST_UPDATE_DATE
499 , LAST_UPDATE_LOGIN
500 )
501 VALUES
502 (
503 p_item_status_code
504 , p_attribute_name
505 , p_attribute_value
506 , g_current_user_id
510 , g_current_login_id
507 , l_Sysdate
508 , g_current_user_id
509 , l_Sysdate
511 );
512
513 ------------------------------
514
515 -- Standard check of p_commit
516 IF FND_API.To_Boolean(p_commit) THEN
517 COMMIT WORK;
518 END IF;
519
520 x_return_status := FND_API.G_RET_STS_SUCCESS;
521
522 ------------------------------
523
524 -- Standard call to get message count and if count is 1,
525 -- get message info.
526 -- The client will directly display the x_msg_data (which is already
527 -- translated) if the x_msg_count = 1;
528 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
529 -- Server-side procedure to access the messages, and consolidate them
530 -- and display (or) to display one message after another.
531
532 FND_MSG_PUB.Count_And_Get
533 ( p_count => x_msg_count,
534 p_data => x_msg_data
535 );
536
537 --------------------------------------------------
538
539 EXCEPTION
540 WHEN FND_API.G_EXC_ERROR THEN
541 ROLLBACK TO Create_Item_Stat_Attr_Vals_Pub;
542 x_return_status := FND_API.G_RET_STS_ERROR;
543 FND_MSG_PUB.Count_And_Get
544 ( p_count => x_msg_count,
545 p_data => x_msg_data
546 );
547
548 x_msg_data := 'Executing - ' || G_PKG_NAME || '.' || l_api_name || ' ' || SQLERRM;
549
550 WHEN G_DUPLICATE_EXCEPTION THEN
551 ROLLBACK TO Create_Item_Stat_Attr_Vals_Pub;
552 x_return_status := FND_API.G_RET_STS_ERROR;
553 FND_MSG_PUB.Count_And_Get
554 ( p_count => x_msg_count,
555 p_data => x_msg_data
556 );
557
558 x_msg_data := 'EGO_ITEM_STAT_ATTR_VAL_EXISTS';
559
560 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
561 ROLLBACK TO Create_Item_Stat_Attr_Vals_Pub;
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
563 FND_MSG_PUB.Count_And_Get
564 ( p_count => x_msg_count,
565 p_data => x_msg_data
566 );
567 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
568 WHEN OTHERS THEN
569 ROLLBACK TO Create_Item_Stat_Attr_Vals_Pub;
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
571 IF FND_MSG_PUB.Check_Msg_Level
572 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
573 THEN
574 FND_MSG_PUB.Add_Exc_Msg
575 ( G_PKG_NAME,
576 l_api_name
577 );
578 END IF;
579 FND_MSG_PUB.Count_And_Get
580 ( p_count => x_msg_count,
581 p_data => x_msg_data
582 );
583 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
584
585 END Create_Item_Status_Attr_Values;
586
587 ----------------------------------------------------------------------
588
589 PROCEDURE Update_Item_Status_Attr_Values
590 (
591 p_api_version IN NUMBER
592 , p_item_status_code IN VARCHAR2
593 , p_attribute_name IN VARCHAR2
594 , p_attribute_value IN VARCHAR2
595 , p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
596 , p_commit IN VARCHAR2 := fnd_api.g_FALSE
597 , x_return_status OUT NOCOPY VARCHAR2
601 ) IS
598 , x_errorcode OUT NOCOPY NUMBER
599 , x_msg_count OUT NOCOPY NUMBER
600 , x_msg_data OUT NOCOPY VARCHAR2
602
603 l_api_name CONSTANT VARCHAR2(30) := 'Update_Item_Status_Attr_Values';
604 l_api_version CONSTANT NUMBER := 1.0;
605
606 -- General variables
607
608 l_Sysdate DATE := Sysdate;
609 l_language VARCHAR2(4) := userenv('LANG');
610 l_creation_date DATE;
611
612 --------------------------------------------------
613
614 BEGIN
615
616 -- Standard Start of API savepoint
617 SAVEPOINT Update_Item_Stat_Attr_Vals_Pub;
618
619 IF NOT FND_API.Compatible_API_Call (l_api_version,
620 p_api_version,
621 l_api_name,
622 G_PKG_NAME)
623 THEN
624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
625 END IF;
626 -- Initialize API message list if necessary.
627 -- Initialize message list if p_init_msg_list is set to TRUE.
628 IF FND_API.to_Boolean(p_init_msg_list) THEN
629 FND_MSG_PUB.initialize;
630 END IF;
631
632 ------------------------------
633
634 UPDATE MTL_STATUS_ATTRIBUTE_VALUES
635 SET
636 ATTRIBUTE_VALUE = p_attribute_value
637 , LAST_UPDATED_BY = g_current_user_id
638 , LAST_UPDATE_DATE = l_Sysdate
639 , LAST_UPDATE_LOGIN = g_current_login_id
640 WHERE
641 INVENTORY_ITEM_STATUS_CODE = p_item_status_code
642 AND ATTRIBUTE_NAME = p_attribute_name;
643
644 ------------------------------
645
646 -- Standard check of p_commit
647 IF FND_API.To_Boolean(p_commit) THEN
648 COMMIT WORK;
649 END IF;
650
651 x_return_status := FND_API.G_RET_STS_SUCCESS;
652
653 ------------------------------
654
655 -- Standard call to get message count and if count is 1,
656 -- get message info.
657 -- The client will directly display the x_msg_data (which is already
658 -- translated) if the x_msg_count = 1;
659 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
660 -- Server-side procedure to access the messages, and consolidate them
661 -- and display (or) to display one message after another.
662
663 FND_MSG_PUB.Count_And_Get
664 ( p_count => x_msg_count,
665 p_data => x_msg_data
666 );
667
668 --------------------------------------------------
669
670 EXCEPTION
671
672 WHEN OTHERS THEN
673 ROLLBACK TO Update_Item_Stat_Attr_Vals_Pub;
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
675 IF FND_MSG_PUB.Check_Msg_Level
676 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
677 THEN
678 FND_MSG_PUB.Add_Exc_Msg
679 ( G_PKG_NAME,
680 l_api_name
681 );
682 END IF;
683 FND_MSG_PUB.Count_And_Get
684 ( p_count => x_msg_count,
685 p_data => x_msg_data
686 );
687 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
688
689 END Update_Item_Status_Attr_Values;
690
691 ----------------------------------------------------------------------
692 END EGO_ITEM_STATUSES_PUB;