[Home] [Help]
PACKAGE BODY: APPS.EAM_ITEM_PVT
Source
1 PACKAGE BODY EAM_ITEM_PVT AS
2 /* $Header: EAMPITMB.pls 120.7 2006/06/13 22:42:17 hkarmach noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_ITEM_PVT';
6
7 PROCEDURE insert_item
8 (
9 p_commit IN VARCHAR2:= FND_API.G_FALSE
10 , x_return_status OUT NOCOPY VARCHAR2
11 , x_msg_count OUT NOCOPY NUMBER
12 , x_msg_data OUT NOCOPY VARCHAR2
13 , x_item_id OUT NOCOPY NUMBER
14 , p_asset_group IN VARCHAR2
15 , p_segment1 IN VARCHAR2
16 , p_segment2 IN VARCHAR2
17 , p_segment3 IN VARCHAR2
18 , p_segment4 IN VARCHAR2
19 , p_segment5 IN VARCHAR2
20 , p_segment6 IN VARCHAR2
21 , p_segment7 IN VARCHAR2
22 , p_segment8 IN VARCHAR2
23 , p_segment9 IN VARCHAR2
24 , p_segment10 IN VARCHAR2
25 , p_segment11 IN VARCHAR2
26 , p_segment12 IN VARCHAR2
27 , p_segment13 IN VARCHAR2
28 , p_segment14 IN VARCHAR2
29 , p_segment15 IN VARCHAR2
30 , p_segment16 IN VARCHAR2
31 , p_segment17 IN VARCHAR2
32 , p_segment18 IN VARCHAR2
33 , p_segment19 IN VARCHAR2
34 , p_segment20 IN VARCHAR2
35 , P_SOURCE_TMPL_ID IN NUMBER
36 , p_template_name IN VARCHAR2
37 , p_organization_id IN NUMBER
38 , p_description IN VARCHAR2
39 , p_serial_generation IN NUMBER
40 , p_prefix_text IN VARCHAR2
41 , p_prefix_number IN VARCHAR2
42 , p_eam_item_type IN NUMBER
43 )
44 IS
45
46 l_create_item_ver NUMBER := 1.0;
47 l_x_return_status VARCHAR2(1);
48 l_x_msg_count NUMBER;
49 l_x_msg_data VARCHAR2(20000);
50
51 l_asset_group INV_Item_GRP.Item_Rec_Type;
52 l_x_inventory_item_id NUMBER;
53 l_master_org_id number;
54
55 l_api_name CONSTANT VARCHAR2(30) := 'insert_item';
56
57 l_module varchar2(200);
58 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
59 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
60 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
61 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
62
63 -- output variables of inv package
64 l_x_curr_item_rec INV_Item_GRP.Item_Rec_Type;
65 l_x_curr_item_return_status VARCHAR2(1);
66 l_x_curr_item_error_tbl INV_Item_GRP.Error_Tbl_Type;
67 l_x_master_item_rec INV_Item_GRP.Item_Rec_Type;
68 l_x_master_item_return_status VARCHAR2(1);
69 l_x_master_item_error_tbl INV_Item_GRP.Error_Tbl_Type;
70
71
72 v_row PLS_INTEGER;
73
74 BEGIN
75 if (l_ulog) then
76 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
77 end if;
78 l_asset_group.Description := p_description;
79 l_asset_group.SERIAL_NUMBER_CONTROL_CODE := p_serial_generation;
80 l_asset_group.AUTO_SERIAL_ALPHA_PREFIX := p_prefix_text;
81 l_asset_group.START_AUTO_SERIAL_NUMBER := p_prefix_number;
82
83 IF (l_plog) THEN
84 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
85 '==================== Entered EAM_ITEM_PVT.Insert_item ===================='
86 || 'p_organization_id = ' || p_organization_id
87 || 'p_eam_item_type = ' || p_eam_item_type||
88 'Comms_NL_Trackable_Flag: '||l_asset_group.comms_nl_trackable_flag||
89 ' Inventory_Item_Flag: '||l_asset_group.Inventory_item_flag);
90 END IF;
91
92 -- 1.1: Set up Item Number. Use segments if specified.
93 IF p_segment1 IS NOT NULL OR
94 p_segment2 IS NOT NULL OR
95 p_segment3 IS NOT NULL OR
96 p_segment4 IS NOT NULL OR
97 p_segment5 IS NOT NULL OR
98 p_segment6 IS NOT NULL OR
99 p_segment7 IS NOT NULL OR
100 p_segment8 IS NOT NULL OR
101 p_segment9 IS NOT NULL OR
102 p_segment10 IS NOT NULL OR
103 p_segment11 IS NOT NULL OR
104 p_segment12 IS NOT NULL OR
105 p_segment13 IS NOT NULL OR
106 p_segment14 IS NOT NULL OR
107 p_segment15 IS NOT NULL OR
108 p_segment16 IS NOT NULL OR
109 p_segment17 IS NOT NULL OR
110 p_segment18 IS NOT NULL OR
111 p_segment19 IS NOT NULL OR
112 p_segment20 IS NOT NULL
113 THEN
114 l_asset_group.Segment1 := p_segment1;
115 l_asset_group.Segment2 := p_segment2;
116 l_asset_group.Segment3 := p_segment3;
117 l_asset_group.Segment4 := p_segment4;
118 l_asset_group.Segment5 := p_segment5;
119 l_asset_group.Segment6 := p_segment6;
120 l_asset_group.Segment7 := p_segment7;
121 l_asset_group.Segment8 := p_segment8;
122 l_asset_group.Segment9 := p_segment9;
123 l_asset_group.Segment10 := p_segment10;
124 l_asset_group.Segment11 := p_segment11;
125 l_asset_group.Segment12 := p_segment12;
126 l_asset_group.Segment13 := p_segment13;
127 l_asset_group.Segment14 := p_segment14;
128 l_asset_group.Segment15 := p_segment15;
129 l_asset_group.Segment16 := p_segment16;
130 l_asset_group.Segment17 := p_segment17;
131 l_asset_group.Segment18 := p_segment18;
132 l_asset_group.Segment19 := p_segment19;
133 l_asset_group.Segment20 := p_segment20;
134 ELSE
135 l_asset_group.Item_Number := p_asset_group;
136 END IF;
137
138 --derive and assign master org
139 select master_organization_id into l_master_org_id
140 from mtl_parameters
141 where organization_id = p_organization_id;
142
143 l_asset_group.organization_id := l_master_org_id;
144
145 IF (l_plog) THEN
146 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
147 'l_asset_group.organization_id'||l_asset_group.organization_id);
148 END IF;
149
150 -- Set EAM attributes
151 l_asset_group.EAM_ITEM_TYPE := p_eam_item_type; -- EAM Asset Activity
152
153 if p_eam_item_type = 1 then
154 l_asset_group.COMMS_NL_TRACKABLE_FLAG := 'Y';
155 else
156 if p_serial_generation = 1 then
157 l_asset_group.COMMS_NL_TRACKABLE_FLAG := 'N';
158 else
159 l_asset_group.COMMS_NL_TRACKABLE_FLAG := 'Y';
160 end if;
161 end if;
162
163
164 IF (l_plog) THEN
165 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
166 'Calling INV_ITEM_GRP.Create_Item...'||
167 'Comms_NL_Trackable_Flag: '||l_asset_group.comms_nl_trackable_flag||
168 ' Inventory_Item_Flag: '||l_asset_group.Inventory_item_flag);
169 END IF;
170
171 INV_ITEM_GRP.Create_Item
172 (
173 p_Item_rec => l_asset_group,
174 p_Template_Id => p_source_tmpl_id,
175 p_Template_Name => p_template_name,
176 x_Item_rec => l_x_master_item_rec,
177 x_return_status => l_x_master_item_return_status,
178 x_Error_tbl => l_x_master_item_error_tbl
179 );
180
181 IF (l_plog) THEN
182 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
183 'Returned from INV_ITEM_GRP.Create_Item...'||
184 'Comms_NL_Trackable_Flag: '||l_x_master_item_rec.comms_nl_trackable_flag||
185 ' Inventory_Item_Flag: '||l_x_master_item_rec.Inventory_item_flag);
186 END IF;
187
188 IF (l_x_master_item_return_status = FND_API.G_RET_STS_SUCCESS) THEN
189
190 IF (l_master_org_id <> p_organization_id) THEN
191 l_asset_group.organization_id := p_organization_id;
192
193 IF (l_plog) THEN
194 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
195 'Calling INV_ITEM_GRP.Create_Item...'||
196 'Comms_NL_Trackable_Flag: '||l_asset_group.comms_nl_trackable_flag||
197 ' Inventory_Item_Flag: '||l_asset_group.Inventory_item_flag);
198 END IF;
199
200 INV_ITEM_GRP.Create_Item
201 (
202 p_Item_rec => l_asset_group,
203 x_Item_rec => l_x_curr_item_rec,
204 x_return_status => l_x_return_status,
205 x_Error_tbl => l_x_curr_item_error_tbl,
206 p_Template_Id => p_source_tmpl_id
207 );
208
209 IF (l_plog) THEN
210 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
211 'Returned from INV_ITEM_GRP.Create_Item...'||
212 'Comms_NL_Trackable_Flag: '||l_x_curr_item_rec.comms_nl_trackable_flag||
213 ' Inventory_Item_Flag: '||l_x_curr_item_rec.Inventory_item_flag);
214 END IF;
215
216
217 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
218 x_return_status := 'S';
219 ELSE
220
221 v_row := l_x_curr_item_error_tbl.FIRST;
222 LOOP
223 EXIT WHEN v_row IS NULL;
224
225 FND_MESSAGE.SET_NAME('INV', l_x_curr_item_error_tbl(v_row).message_name);
226 FND_MSG_PUB.ADD;
227 x_msg_data := x_msg_data || ' ' ||l_x_curr_item_error_tbl(v_row).unique_id||' '||l_x_curr_item_error_tbl(v_row).message_text;
228 v_row := l_x_curr_item_error_tbl.NEXT(v_row);
229 end loop;
230
231 x_return_status := 'E';
232 END IF;
233 END IF;
234 ELSE
235
236 v_row := l_x_master_item_error_tbl.FIRST;
237 LOOP
238 EXIT WHEN v_row IS NULL;
239
240 FND_MESSAGE.SET_NAME('INV', l_x_master_item_error_tbl(v_row).message_name);
241 FND_MSG_PUB.ADD;
242 x_msg_data := x_msg_data || ' ' ||l_x_master_item_error_tbl(v_row).unique_id||' '|| l_x_master_item_error_tbl(v_row).message_text;
243 v_row := l_x_master_item_error_tbl.NEXT(v_row);
244 end loop;
245
246 x_return_status := 'E';
247
248 END IF;
249
250 IF fnd_api.to_boolean(p_commit) THEN
251 commit;
252 END IF;
253
254 x_item_id := l_x_curr_item_rec.inventory_item_id;
255
256 IF (l_plog) THEN
257 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
258 '==================== Exiting EAM_ITEM_PVT.Insert_item ====================');
259 END IF;
260 END INSERT_ITEM;
261
262 PROCEDURE update_item
263 (
264 p_commit IN VARCHAR2:= FND_API.G_FALSE
265 , x_return_status OUT NOCOPY VARCHAR2
266 , x_msg_count OUT NOCOPY NUMBER
267 , x_msg_data OUT NOCOPY VARCHAR2
268 , p_inventory_item_id IN NUMBER
269 , P_SOURCE_TMPL_ID IN NUMBER
270 , p_template_name IN VARCHAR2
271 , p_organization_id IN NUMBER
272 , p_description IN VARCHAR2
273 , p_serial_generation IN NUMBER
274 , p_prefix_text IN VARCHAR2
275 , p_prefix_number IN VARCHAR2
276 )
277
278 IS
279
280 l_update_item_ver NUMBER := 1.0;
281 l_x_return_status VARCHAR2(1);
282 l_x_msg_count NUMBER;
283 l_x_msg_data VARCHAR2(20000);
284 l_master_org_id number;
285
286 l_asset_group INV_Item_GRP.Item_Rec_Type;
287 l_x_inventory_item_id NUMBER;
288
289 l_api_name CONSTANT VARCHAR2(30) := 'Update_item';
290
291 l_module varchar2(200);
292 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
293 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
294 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
295 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
296
297 -- output variables of inv package
298 l_x_curr_item_rec INV_Item_GRP.Item_Rec_Type;
299 l_x_curr_item_return_status VARCHAR2(1);
300 l_x_curr_item_error_tbl INV_Item_GRP.Error_Tbl_Type;
301 l_x_master_item_error_tbl INV_Item_GRP.Error_Tbl_Type;
302
303 v_row PLS_INTEGER;
304
305 BEGIN
306 if (l_ulog) then
307 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
308 end if;
309
310 l_asset_group.Description := p_description;
311 l_asset_group.SERIAL_NUMBER_CONTROL_CODE := p_serial_generation;
312 l_asset_group.AUTO_SERIAL_ALPHA_PREFIX := p_prefix_text;
313 l_asset_group.START_AUTO_SERIAL_NUMBER := p_prefix_number;
314 l_asset_group.organization_id := p_organization_id;
315 l_asset_group.inventory_item_id := p_inventory_item_id;
316
317 IF (l_plog) THEN
318 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
319 '==================== Entered EAM_ITEM_PVT.Update_item ===================='||
320 'Comms_NL_Trackable_Flag: '||l_asset_group.comms_nl_trackable_flag||
321 ' Inventory_Item_Flag: '||l_asset_group.Inventory_item_flag);
322
323 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
324 'Calling INV_ITEM_GRP.Update_Item...');
325 END IF;
326
327 if p_source_tmpl_id is not null or p_template_name is not null then
328
329 --derive and assign master org
330 select master_organization_id into l_master_org_id
331 from mtl_parameters
332 where organization_id = p_organization_id;
333
334 l_asset_group.organization_id := l_master_org_id;
335
336 INV_ITEM_GRP.Update_Item
337 (
338 p_Item_rec => l_asset_group,
339 p_Template_Id => p_source_tmpl_id,
340 p_Template_Name => p_template_name,
341 x_Item_rec => l_x_curr_item_rec,
342 x_return_status => l_x_return_status,
343 x_Error_tbl => l_x_curr_item_error_tbl
344 );
345
346 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
347 v_row := l_x_master_item_error_tbl.FIRST;
348 LOOP
349 EXIT WHEN v_row IS NULL;
350
351 FND_MESSAGE.SET_NAME('INV', l_x_master_item_error_tbl(v_row).message_name);
352 FND_MSG_PUB.ADD;
356
353 x_msg_data := x_msg_data || ' ' ||l_x_master_item_error_tbl(v_row).unique_id||' '|| l_x_master_item_error_tbl(v_row).message_text;
354 v_row := l_x_master_item_error_tbl.NEXT(v_row);
355 end loop;
357 x_return_status := 'E';
358
359 END IF;
360
361 end if;
362
363 l_asset_group.organization_id := p_organization_id;
364
365 INV_ITEM_GRP.Update_Item
366 (
367 p_Item_rec => l_asset_group,
368 x_Item_rec => l_x_curr_item_rec,
369 x_return_status => l_x_return_status,
370 x_Error_tbl => l_x_curr_item_error_tbl
371 );
372
373 IF (l_plog) THEN
374 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
375 'Returned from INV_ITEM_GRP.Update_Item...'||
376 'Comms_NL_Trackable_Flag: '||l_x_curr_item_rec.comms_nl_trackable_flag||
377 ' Inventory_Item_Flag: '||l_x_curr_item_rec.Inventory_item_flag);
378 END IF;
379
380 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
381 x_return_status := 'S';
382 ELSE
383
384 v_row := l_x_curr_item_error_tbl.FIRST;
385 LOOP
386 EXIT WHEN v_row IS NULL;
387
388 FND_MESSAGE.SET_NAME('INV', l_x_curr_item_error_tbl(v_row).message_name);
389 FND_MSG_PUB.ADD;
390 x_msg_data := x_msg_data || ' ' ||l_x_curr_item_error_tbl(v_row).unique_id||' '||l_x_curr_item_error_tbl(v_row).message_text;
391 v_row := l_x_curr_item_error_tbl.NEXT(v_row);
392 end loop;
393
394 x_return_status := 'E';
395 END IF;
396
397 IF fnd_api.to_boolean(p_commit) THEN
398 commit;
399 END IF;
400
401 IF (l_plog) THEN
402 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
403 '==================== Exiting EAM_ITEM_PVT.Update_item ====================');
404 END IF;
405 END UPDATE_ITEM;
406
407 PROCEDURE Lock_Item(
408 p_commit IN VARCHAR2:= FND_API.G_FALSE , x_return_status OUT NOCOPY VARCHAR2
409 , x_msg_count OUT NOCOPY NUMBER
410 , x_msg_data OUT NOCOPY VARCHAR2
411 , p_inventory_item_id IN NUMBER
412 , p_organization_id IN NUMBER
413 )
414
415
416 IS
417
418 l_x_curr_item_error_tbl INV_Item_GRP.Error_Tbl_Type;
419 l_x_return_status VARCHAR2(1);
420 l_api_name CONSTANT VARCHAR2(30) := 'Lock_item';
421
422 l_module varchar2(200);
423 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
424 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
425 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
426 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
427
428
429 BEGIN
430
431 -- Standard Start of API savepoint
432 SAVEPOINT lock_item;
433 if (l_ulog) then
434 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
435 end if;
436
437 IF (l_plog) THEN
438 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
439 '==================== Entered EAM_ITEM_PVT.Lock_item ===================='
440 || 'p_organization_id = ' || p_organization_id
441 || 'p_inventory_item_id = ' ||p_inventory_item_id);
442
443 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
444 'Calling INV_ITEM_GRP.Lock_Item...');
445 END IF;
446
447 INV_ITEM_GRP.Lock_Item
448 ( p_Item_ID => p_inventory_item_id
449 , p_Org_ID => p_organization_id
450 , x_return_status => l_x_return_status
451 , x_Error_tbl => l_x_curr_item_error_tbl
452 );
453
454
455 x_return_status := l_x_return_status;
456
457 IF (l_plog) THEN
458 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
459 'Returned from INV_ITEM_GRP.Lock_Item with l_x_return_status '||l_x_return_status);
460 END IF;
461 if nvl(l_x_return_status, 'S') not in ('E','U') then
462 return;
463 else
464 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
465 APP_EXCEPTION.Raise_Exception;
466 end if;
467
468 -- End of API body.
469 -- Standard check of p_commit.
470 IF fnd_api.to_boolean(p_commit) THEN
471 COMMIT WORK;
472 IF (l_plog) THEN
473 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
474 'Commiting Work');
475 END IF;
476 END IF;
477
478 -- Standard call to get message count and if count is 1, get message info.
479 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
480
481 EXCEPTION
482 WHEN fnd_api.g_exc_error THEN
483 ROLLBACK TO lock_ITEM;
484 IF (l_plog) THEN
485 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
486 'Rollback To Lock_Item.....');
487 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
488 '==============INV_ITEM_GRP.Lock_Item :EXPECTED ERROR===================');
489 END IF;
490 x_return_status := fnd_api.g_ret_sts_error;
491 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
492 WHEN fnd_api.g_exc_unexpected_error THEN
493 ROLLBACK TO lock_ITEM;
494 IF (l_plog) THEN
495 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
496 'Rollback To Lock_Item.....');
497 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
498 '==============INV_ITEM_GRP.Lock_Item :UNEXPECTED ERROR===================');
499 END IF;
500 x_return_status := fnd_api.g_ret_sts_unexp_error;
504 WHEN OTHERS THEN
501 fnd_msg_pub.count_and_get(
502 p_count => x_msg_count
503 ,p_data => x_msg_data);
505 ROLLBACK TO lock_ITEM;
506 IF (l_plog) THEN
507 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
508 'Rollback To Lock_Item.....');
509 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
510 '==============INV_ITEM_GRP.Lock_Item :OTHER ERROR===================');
511 END IF;
512 x_return_status := fnd_api.g_ret_sts_unexp_error;
513
514 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
515 fnd_msg_pub.add_exc_msg(g_pkg_name, 'LOCK_ITEM');
516 END IF;
517
518 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
519
520 IF (l_plog) THEN
521 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
522 '==================== Exiting EAM_ITEM_PVT.Lock_item ====================');
523 END IF;
524
525 END LOCK_ITEM;
526
527 END EAM_ITEM_PVT;