[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSETATTR_VALUE_PUB
Source
1 PACKAGE BODY EAM_ASSETATTR_VALUE_PUB AS
2 /* $Header: EAMPAAVB.pls 120.5 2008/02/08 05:20:18 vboddapa ship $ */
3 -- Start of comments
4 -- API name : EAM_ASSETATTR_VALUE_PUB
5 -- Type : Public
6 -- Function : insert_assetattr_value, update_assetattr_value
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN : p_api_version IN NUMBER Required
10 -- p_init_msg_list IN VARCHAR2 Optional
11 -- Default = FND_API.G_FALSE
12 -- p_commit IN VARCHAR2 Optional
13 -- Default = FND_API.G_FALSE
14 -- p_validation_level IN NUMBER Optional
15 -- Default = FND_API.G_VALID_LEVEL_FULL
16 -- parameter1
17 -- parameter2
18 -- .
19 -- .
20 -- OUT : x_return_status OUT VARCHAR2(1)
21 -- x_msg_count OUT NUMBER
22 -- x_msg_data OUT VARCHAR2(2000)
23 -- parameter1
24 -- parameter2
25 -- .
26 -- .
27 -- Version : Current version x.x
28 -- Changed....
29 -- previous version y.y
30 -- Changed....
31 -- .
32 -- .
33 -- previous version 2.0
34 -- Changed....
35 -- Initial version 1.0
36 --
37 -- Notes : Note text
38 --
39 -- End of comments
40
41 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_ASSETATTR_VALUE_PUB';
42
43 /* for de-bugging */
44 /* g_sr_no number ;*/
45
46 PROCEDURE print_log(info varchar2) is
47 PRAGMA AUTONOMOUS_TRANSACTION;
48 l_dummy number;
49 BEGIN
50 /*
51 if (g_sr_no is null or g_sr_no<0) then
52 g_sr_no := 0;
53 end if;
54
55 g_sr_no := g_sr_no+1;
56
57 INSERT into temp_isetup_api(msg,sr_no)
58 VALUES (info,g_sr_no);
59
60 commit;
61 */
62 FND_FILE.PUT_LINE(FND_FILE.LOG, info);
63
64 END;
65
66
67 /* lllin: get item type. 1=assetgroup, 3=rebuildable */
68 FUNCTION get_item_type
69 (p_creation_organization_id in number,
70 p_inventory_item_id in number)
71 return number
72 is
73 l_eam_item_type number;
74 begin
75 select eam_item_type into l_eam_item_type
76 from mtl_system_items
77 where inventory_item_id=p_inventory_item_id
78 and rownum=1;
79
80 return l_eam_item_type;
81 exception
82 when no_data_found then
83 return null;
84 end get_item_type;
85
86
87
88 /* function checking the item unique when both the 2 combinations are provided by the user */
89 FUNCTION check_item_unique (
90 p_maintenance_object_type NUMBER,
91 p_maintenance_object_id NUMBER,
92 p_asset_group_id NUMBER,
93 p_organization_id NUMBER,
94 p_serial_number VARCHAR2,
95 p_creation_organization_id NUMBER
96 )
97 RETURN boolean
98 IS
99 l_count_rec NUMBER := 0;
100 BEGIN
101 /* As object type for assetnumbers in R12 is 3(earlier it was 1 in 11.5) */
102 IF (p_maintenance_object_type = 3) THEN
103 IF ( p_serial_number IS NOT NULL ) THEN
104
105 SELECT count(*) INTO l_count_rec
106 FROM mtl_system_items MSI , csi_item_instances CII
107 WHERE cii.serial_number = p_serial_number
108 AND cii.instance_id = p_maintenance_object_id
109 AND CII.inventory_item_id = MSI.inventory_item_id
110 AND MSI.inventory_item_id = p_asset_group_id;
111 END IF;
112
113 ELSIF (p_maintenance_object_type = 2) THEN
114 IF ((p_serial_number IS NULL) AND
115 (p_maintenance_object_id = p_asset_group_id)) THEN
116
117 SELECT count(*) INTO l_count_rec
118 FROM mtl_system_items
119 WHERE inventory_item_id = p_asset_group_id;
120 END IF;
121
122 END IF;
123
124 IF (l_count_rec > 0) THEN
125 RETURN true;
126 ELSE
127 RETURN false;
128 END IF;
129 END check_item_unique;
130
131 /* For raising error */
132 PROCEDURE RAISE_ERROR (ERROR VARCHAR2)
133 IS
134 BEGIN
135
136
137 FND_MESSAGE.SET_NAME ('EAM', ERROR);
138 FND_MSG_PUB.ADD;
139 RAISE FND_API.G_EXC_ERROR;
140 END;
141
142
143 /*
144 procedures for validation
145 */
146 procedure validate_application_id( P_APPLICATION_ID IN NUMBER)
147 is
148 l_appl_name varchar2(30) ;
149 BEGIN
150 if p_application_id is null or p_application_id <> 401
151 then
152 fnd_message.set_name('EAM', 'EAM_INVALID_APPLICATION_ID');
153 fnd_msg_pub.add;
154 RAISE fnd_api.g_exc_error;
155 end if;
156 END;
157
158 procedure validate_maintenance_object_id(p_organization_id in number, p_object_id in number, p_eam_item_type in varchar2)
159 is
160 l_count number;
161 begin
162
163 if p_eam_item_type = 1
164 then
165 select count(*) into l_count
166 from csi_item_instances
167 where instance_id=p_object_id;
168 elsif p_eam_item_type = 2
169 then
170 select count(*) into l_count
171 from mtl_system_items
172 where inventory_item_id=p_object_id
173 and organization_id=p_organization_id;
174 end if;
175
176
177 if l_count = 0 then
178 fnd_message.set_name('EAM', 'EAM_INVALID_MAINT_OBJ_ID');
179 fnd_msg_pub.add;
180 RAISE fnd_api.g_exc_error;
181 end if;
182
183 END;
184
185 procedure validate_assos_id( p_association_id in number,
186 p_creation_organization_id in number,
187 p_inventory_item_id in number,
188 p_attribute_category in varchar2,
189 p_eam_item_type in number)
190 is
191 l_count number;
192 begin
193 select count(*) into l_count
194 from mtl_eam_asset_attr_groups
195 where association_id = p_association_id and
196 /* removing this as creation_organization_id is not used */
197 /* decode(p_eam_item_type,1,creation_organization_id,1) = decode(p_eam_item_type,1,p_creation_organization_id,1) and */
198 inventory_item_id = p_inventory_item_id
199 and descriptive_flex_context_code=p_attribute_category;
200
201 if l_count = 0 then
202 fnd_message.set_name('EAM', 'EAM_INVALID_ASSOCIATION_GROUP');
203 fnd_msg_pub.add;
204 RAISE fnd_api.g_exc_error;
205 end if;
206 END;
207
208
209 procedure validate_descflexfield_name(P_DESCRIPTIVE_FLEXFIELD_NAME in varchar2)
210 is
211 BEGIN
212 -- Bug # 3518888.
213 if (P_DESCRIPTIVE_FLEXFIELD_NAME IS NULL) then
214 RAISE_ERROR('EAM_INVALID_DFF_NAME');
215 end if;
216 if P_DESCRIPTIVE_FLEXFIELD_NAME <> 'MTL_EAM_ASSET_ATTR_VALUES' then
217 RAISE_ERROR('EAM_INVALID_DFF_NAME');
218 end if;
219 END ;
220
221 procedure validate_descflex_context_code(P_ATTRIBUTE_CATEGORY in varchar2, P_APPLICATION_ID in NUMBER)
222 is
223 l_count number;
224 BEGIN
225
226
227 SELECT COUNT(*) INTO l_count
228 FROM FND_DESCR_FLEX_CONTEXTS_VL
229 WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'MTL_EAM_ASSET_ATTR_VALUES'
230 AND ENABLED_FLAG = 'Y'
231 AND APPLICATION_ID = p_application_id
232 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attribute_category;
233
234 if l_count = 0
235 then
236 fnd_message.set_name('EAM', 'EAM_AAV_INVALID_DFF_CONTEXT');
237 fnd_msg_pub.add;
238 RAISE fnd_api.g_exc_error;
239 end if;
240
241 END ;
242
243 procedure validate_dff_segments(
244 p_app_short_name IN VARCHAR:='EAM',
245 p_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
246 p_c_ATTRIBUTE1 IN VARCHAR2 default null,
247 p_c_ATTRIBUTE2 IN VARCHAR2 default null,
248 p_c_ATTRIBUTE3 IN VARCHAR2 default null,
249 p_c_ATTRIBUTE4 IN VARCHAR2 default null,
250 p_c_ATTRIBUTE5 IN VARCHAR2 default null,
251 p_c_ATTRIBUTE6 IN VARCHAR2 default null,
252 p_c_ATTRIBUTE7 IN VARCHAR2 default null,
253 p_c_ATTRIBUTE8 IN VARCHAR2 default null,
254 p_c_ATTRIBUTE9 IN VARCHAR2 default null,
255 p_c_ATTRIBUTE10 IN VARCHAR2 default null,
256 p_c_ATTRIBUTE11 IN VARCHAR2 default null,
257 p_c_ATTRIBUTE12 IN VARCHAR2 default null,
258 p_c_ATTRIBUTE13 IN VARCHAR2 default null,
259 p_c_ATTRIBUTE14 IN VARCHAR2 default null,
260 p_c_ATTRIBUTE15 IN VARCHAR2 default null,
261 p_c_ATTRIBUTE16 IN VARCHAR2 default null,
262 p_c_ATTRIBUTE17 IN VARCHAR2 default null,
263 p_c_ATTRIBUTE18 IN VARCHAR2 default null,
264 p_c_ATTRIBUTE19 IN VARCHAR2 default null,
265 p_c_ATTRIBUTE20 IN VARCHAR2 default null,
266 p_n_ATTRIBUTE1 IN NUMBER default null,
267 p_n_ATTRIBUTE2 IN NUMBER default null,
268 p_n_ATTRIBUTE3 IN NUMBER default null,
269 p_n_ATTRIBUTE4 IN NUMBER default null,
270 p_n_ATTRIBUTE5 IN NUMBER default null,
271 p_n_ATTRIBUTE6 IN NUMBER default null,
272 p_n_ATTRIBUTE7 IN NUMBER default null,
273 p_n_ATTRIBUTE8 IN NUMBER default null,
274 p_n_ATTRIBUTE9 IN NUMBER default null,
275 p_n_ATTRIBUTE10 IN NUMBER default null,
276 p_d_ATTRIBUTE1 IN DATE default null,
277 p_d_ATTRIBUTE2 IN DATE default null,
278 p_d_ATTRIBUTE3 IN DATE default null,
279 p_d_ATTRIBUTE4 IN DATE default null,
280 p_d_ATTRIBUTE5 IN DATE default null,
281 p_d_ATTRIBUTE6 IN DATE default null,
282 p_d_ATTRIBUTE7 IN DATE default null,
283 p_d_ATTRIBUTE8 IN DATE default null,
284 p_d_ATTRIBUTE9 IN DATE default null,
285 p_d_ATTRIBUTE10 IN DATE default null
286 )
287 is
288 l_error_segments number;
289 l_error_message varchar2(4000);
290 l_validated boolean;
291
292 begin
293 -- validate the desc. flex fields
294 FND_FLEX_DESCVAL.set_context_value(p_attribute_category);
295 fnd_flex_descval.set_column_value('C_ATTRIBUTE1', p_c_ATTRIBUTE1);
296 fnd_flex_descval.set_column_value('C_ATTRIBUTE2', p_c_ATTRIBUTE2);
297 fnd_flex_descval.set_column_value('C_ATTRIBUTE3', p_c_ATTRIBUTE3);
298 fnd_flex_descval.set_column_value('C_ATTRIBUTE4', p_c_ATTRIBUTE4);
299 fnd_flex_descval.set_column_value('C_ATTRIBUTE5', p_c_ATTRIBUTE5);
300 fnd_flex_descval.set_column_value('C_ATTRIBUTE6', p_c_ATTRIBUTE6);
301 fnd_flex_descval.set_column_value('C_ATTRIBUTE7', p_c_ATTRIBUTE7);
302 fnd_flex_descval.set_column_value('C_ATTRIBUTE8', p_c_ATTRIBUTE8);
303 fnd_flex_descval.set_column_value('C_ATTRIBUTE9', p_c_ATTRIBUTE9);
304 fnd_flex_descval.set_column_value('C_ATTRIBUTE10', p_c_ATTRIBUTE10);
305 fnd_flex_descval.set_column_value('C_ATTRIBUTE11', p_c_ATTRIBUTE11);
306 fnd_flex_descval.set_column_value('C_ATTRIBUTE12', p_c_ATTRIBUTE12);
307 fnd_flex_descval.set_column_value('C_ATTRIBUTE13', p_c_ATTRIBUTE13);
308 fnd_flex_descval.set_column_value('C_ATTRIBUTE14', p_c_ATTRIBUTE14);
309 fnd_flex_descval.set_column_value('C_ATTRIBUTE15', p_c_ATTRIBUTE15);
310 fnd_flex_descval.set_column_value('C_ATTRIBUTE16', p_c_ATTRIBUTE16);
311 fnd_flex_descval.set_column_value('C_ATTRIBUTE17', p_c_ATTRIBUTE17);
312 fnd_flex_descval.set_column_value('C_ATTRIBUTE18', p_c_ATTRIBUTE18);
313 fnd_flex_descval.set_column_value('C_ATTRIBUTE19', p_c_ATTRIBUTE19);
314 fnd_flex_descval.set_column_value('C_ATTRIBUTE20', p_c_ATTRIBUTE20);
315
316 fnd_flex_descval.set_column_value('N_ATTRIBUTE1', p_n_ATTRIBUTE1);
317 fnd_flex_descval.set_column_value('N_ATTRIBUTE2', p_n_ATTRIBUTE2);
318 fnd_flex_descval.set_column_value('N_ATTRIBUTE3', p_n_ATTRIBUTE3);
319 fnd_flex_descval.set_column_value('N_ATTRIBUTE4', p_n_ATTRIBUTE4);
320 fnd_flex_descval.set_column_value('N_ATTRIBUTE5', p_n_ATTRIBUTE5);
321 fnd_flex_descval.set_column_value('N_ATTRIBUTE6', p_n_ATTRIBUTE6);
322 fnd_flex_descval.set_column_value('N_ATTRIBUTE7', p_n_ATTRIBUTE7);
323 fnd_flex_descval.set_column_value('N_ATTRIBUTE8', p_n_ATTRIBUTE8);
324 fnd_flex_descval.set_column_value('N_ATTRIBUTE9', p_n_ATTRIBUTE9);
325 fnd_flex_descval.set_column_value('N_ATTRIBUTE10', p_n_ATTRIBUTE10);
326
327 fnd_flex_descval.set_column_value('D_ATTRIBUTE1', p_d_ATTRIBUTE1);
328 fnd_flex_descval.set_column_value('D_ATTRIBUTE2', p_d_ATTRIBUTE2);
329 fnd_flex_descval.set_column_value('D_ATTRIBUTE3', p_d_ATTRIBUTE3);
330 fnd_flex_descval.set_column_value('D_ATTRIBUTE4', p_d_ATTRIBUTE4);
331 fnd_flex_descval.set_column_value('D_ATTRIBUTE5', p_d_ATTRIBUTE5);
332 fnd_flex_descval.set_column_value('D_ATTRIBUTE6', p_d_ATTRIBUTE6);
333 fnd_flex_descval.set_column_value('D_ATTRIBUTE7', p_d_ATTRIBUTE7);
334 fnd_flex_descval.set_column_value('D_ATTRIBUTE8', p_d_ATTRIBUTE8);
335 fnd_flex_descval.set_column_value('D_ATTRIBUTE9', p_d_ATTRIBUTE9);
336 fnd_flex_descval.set_column_value('D_ATTRIBUTE10', p_d_ATTRIBUTE10);
337
338 l_validated:= FND_FLEX_DESCVAL.validate_desccols(
339 'INV',
340 'MTL_EAM_ASSET_ATTR_VALUES',
341 'I',
342 sysdate ) ;
343
344 if (not l_validated) then
345 l_error_segments:=FND_FLEX_DESCVAL.error_segment;
346 l_error_message:= substr(fnd_flex_descval.error_message,1,4000);
347 FND_MESSAGE.SET_NAME('EAM', 'EAM_INVALID_DESC_FLEX');
348 FND_MESSAGE.SET_TOKEN('ERROR_MSG', l_error_message);
349 FND_MSG_PUB.Add;
350 RAISE FND_API.G_EXC_ERROR;
351 end if;
352
353 end validate_dff_segments;
354
355 procedure VALIDATE_ROW_EXISTS(P_ATTRIBUTE_CATEGORY IN VARCHAR2,
356 P_ASSOCIATION_ID IN NUMBER,
357 P_INVENTORY_ITEM_ID IN NUMBER,
358 P_SERIAL_NUMBER IN VARCHAR2,
359 P_CREATION_ORGANIZATION_ID IN NUMBER,
360 p_create_flag in boolean,
361 p_eam_item_type in number)
362 is
363 l_count number;
364 BEGIN
365 SELECT COUNT(*) INTO l_count
366 FROM MTL_EAM_ASSET_ATTR_VALUES
367 WHERE association_id = p_association_id and
368 inventory_item_id = p_inventory_item_id and
369 serial_number = p_serial_number and
370 /* removing this as creation_organization_id is not used */
371 /* decode(p_eam_item_type,1,creation_organization_id,1) = decode(p_eam_item_type,1,p_creation_organization_id,1) and */
372 attribute_category = p_attribute_category;
373
374 if l_count = 0
375 then
376 if NOT p_create_flag
377 then
378 fnd_message.set_name('EAM', 'EAM_ATTR_VALUES_REC_NOT_FOUND');
379 fnd_msg_pub.add;
380 RAISE fnd_api.g_exc_error;
381 END IF;
382 else
383 if p_create_flag
384 then
385 fnd_message.set_name('EAM', 'EAM_ATTR_VALUES_REC_EXISTS');
386 fnd_msg_pub.add;
387 RAISE fnd_api.g_exc_error;
388 END IF;
389 end if;
390 END;
391
392
393
394 procedure insert_assetattr_value
395 (
396 p_api_version IN NUMBER ,
397 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE ,
398 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
399 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
400 x_return_status OUT NOCOPY VARCHAR2 ,
401 x_msg_count OUT NOCOPY NUMBER ,
402 x_msg_data OUT NOCOPY VARCHAR2 ,
403 P_ASSOCIATION_ID IN NUMBER ,
404 P_APPLICATION_ID IN NUMBER default 401,
405 P_DESCRIPTIVE_FLEXFIELD_NAME IN VARCHAR2 default 'MTL_EAM_ASSET_ATTR_VALUES' ,
406 P_INVENTORY_ITEM_ID IN NUMBER default null,
407 P_SERIAL_NUMBER IN VARCHAR2 default null,
408 P_ORGANIZATION_ID IN NUMBER ,
409 P_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
410 P_C_ATTRIBUTE1 IN VARCHAR2 default null,
411 P_C_ATTRIBUTE2 IN VARCHAR2 default null,
412 P_C_ATTRIBUTE3 IN VARCHAR2 default null,
413 P_C_ATTRIBUTE4 IN VARCHAR2 default null,
414 P_C_ATTRIBUTE5 IN VARCHAR2 default null,
415 P_C_ATTRIBUTE6 IN VARCHAR2 default null,
416 P_C_ATTRIBUTE7 IN VARCHAR2 default null,
417 P_C_ATTRIBUTE8 IN VARCHAR2 default null,
418 P_C_ATTRIBUTE9 IN VARCHAR2 default null,
419 P_C_ATTRIBUTE10 IN VARCHAR2 default null,
420 P_C_ATTRIBUTE11 IN VARCHAR2 default null,
421 P_C_ATTRIBUTE12 IN VARCHAR2 default null,
422 P_C_ATTRIBUTE13 IN VARCHAR2 default null,
423 P_C_ATTRIBUTE14 IN VARCHAR2 default null,
424 P_C_ATTRIBUTE15 IN VARCHAR2 default null,
425 P_C_ATTRIBUTE16 IN VARCHAR2 default null,
426 P_C_ATTRIBUTE17 IN VARCHAR2 default null,
427 P_C_ATTRIBUTE18 IN VARCHAR2 default null,
428 P_C_ATTRIBUTE19 IN VARCHAR2 default null,
429 P_C_ATTRIBUTE20 IN VARCHAR2 default null,
430 P_D_ATTRIBUTE1 IN DATE default null,
431 P_D_ATTRIBUTE2 IN DATE default null,
432 P_D_ATTRIBUTE3 IN DATE default null,
433 P_D_ATTRIBUTE4 IN DATE default null,
434 P_D_ATTRIBUTE5 IN DATE default null,
435 P_D_ATTRIBUTE6 IN DATE default null,
436 P_D_ATTRIBUTE7 IN DATE default null,
437 P_D_ATTRIBUTE8 IN DATE default null,
438 P_D_ATTRIBUTE9 IN DATE default null,
439 P_D_ATTRIBUTE10 IN DATE default null,
440 P_N_ATTRIBUTE1 IN NUMBER default null,
441 P_N_ATTRIBUTE2 IN NUMBER default null,
442 P_N_ATTRIBUTE3 IN NUMBER default null,
443 P_N_ATTRIBUTE4 IN NUMBER default null,
444 P_N_ATTRIBUTE5 IN NUMBER default null,
445 P_N_ATTRIBUTE6 IN NUMBER default null,
446 P_N_ATTRIBUTE7 IN NUMBER default null,
447 P_N_ATTRIBUTE8 IN NUMBER default null,
448 P_N_ATTRIBUTE9 IN NUMBER default null,
449 P_N_ATTRIBUTE10 IN NUMBER default null,
450 P_MAINTENANCE_OBJECT_TYPE IN VARCHAR2 default null ,
451 P_MAINTENANCE_OBJECT_ID IN NUMBER default null,
452 P_CREATION_ORGANIZATION_ID IN NUMBER default null
453 )
454 IS
455 l_api_name CONSTANT VARCHAR2(30) := 'insert_assetattr_value';
456 l_api_version CONSTANT NUMBER := 1.0;
457 l_error boolean;
458 l_boolean number;
459 l_return_status VARCHAR2(1);
460 l_msg_count NUMBER;
461 l_msg_data VARCHAR2(30);
462
463 l_error_segments VARCHAR2(5000);
464
465
466 l_object_found BOOLEAN;
467 l_maintenance_object_type NUMBER;
468 l_maintenance_object_id NUMBER;
469 l_creation_organization_id NUMBER;
470 l_asset_group_id NUMBER;
471 l_org_id NUMBER;
472 l_temp_org_id NUMBER;
473 l_validated boolean;
474 l_item_type number;
475 l_count number;
476 l_item_id NUMBER;
477 l_serial_number VARCHAR2(100);
478
479
480 BEGIN
481 -- Standard Start of API savepoint
482 SAVEPOINT INSERT_ASSETATTR_VALUE;
483 -- Standard call to check for call compatibility.
484 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
485 p_api_version ,
486 l_api_name ,
487 G_PKG_NAME )
488 THEN
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 END IF;
491 -- Initialize message list if p_init_msg_list is set to TRUE.
492 IF FND_API.to_Boolean( p_init_msg_list ) THEN
493 FND_MSG_PUB.initialize;
494 END IF;
495 -- Initialize API return status to success
496 x_return_status := FND_API.G_RET_STS_SUCCESS;
497 -- API body
498
499 /* Anand- for creation_organization_id = organization_id */
500 l_org_id := P_ORGANIZATION_ID;
501 if (P_CREATION_ORGANIZATION_ID IS NOT NULL) then
502 if P_CREATION_ORGANIZATION_ID <> P_ORGANIZATION_ID then
503 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_CR_ORG_ID');
504 fnd_msg_pub.add;
505 RAISE fnd_api.g_exc_error;
506 else
507
508 l_creation_organization_id := P_ORGANIZATION_ID;
509 end if;
510 else
514 if l_creation_organization_id is not null then
511 l_creation_organization_id := P_ORGANIZATION_ID;
512 end if;
513
515 EAM_COMMON_UTILITIES_PVT.verify_org(
516 p_resp_id => NULL,
517 p_resp_app_id => 401,
518 p_org_id => l_CREATION_ORGANIZATION_ID,
519 x_boolean => l_boolean,
520 x_return_status => l_return_status,
521 x_msg_count => l_msg_count ,
522 x_msg_data => l_msg_data);
523 if l_boolean = 0
524 then
525 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_ORG_ID');
526 fnd_msg_pub.add;
527 RAISE fnd_api.g_exc_error;
528 end if;
529 end if;
530
531 /* validate / populate inventory_item_id, serial_number, maintenance_object_id, maintenance_object_type */
532
533 IF ((p_maintenance_object_type is null or p_maintenance_object_id is null) and
534 (p_inventory_item_id is null or p_serial_number is null)) THEN
535 fnd_message.set_name('EAM', 'EAM_NOT_ENOUGH_PARAM');
536 fnd_msg_pub.add;
537 RAISE FND_API.G_EXC_ERROR;
538 END IF;
539
540 IF (p_maintenance_object_type is not null and p_maintenance_object_type <> 3) THEN
541 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_MAINT_OBJ_TYPE');
542 fnd_msg_pub.add;
543 RAISE FND_API.G_EXC_ERROR;
544 END IF;
545
546 l_maintenance_object_type := p_maintenance_object_type;
547 l_maintenance_object_id := p_maintenance_object_id;
548 l_asset_group_id := p_inventory_item_id;
549 l_serial_number := p_serial_number;
550 l_org_id := p_creation_organization_id;
551
552 IF (p_inventory_item_id IS NOT NULL) THEN
553
554 /* validate item id; get item type */
555 l_item_type:=get_item_type(p_creation_organization_id, l_asset_group_id);
556
557 ELSE
558 begin
559 select msi.eam_item_type into l_item_type
560 from mtl_system_items msi, csi_item_instances cii
561 where cii.inventory_item_id = msi.inventory_item_id
562 and cii.last_vld_organization_id = msi.organization_id
563 and cii.instance_id = p_maintenance_object_id;
564
565 exception
566 when no_data_found then
567 raise_error('EAM_ABO_INVLD_MT_GEN_OBJ_ID');
568 when too_many_rows then
569 raise_error('EAM_INVALID_MAINT_OBJ_ID');
570 END;
571 END IF;
572
573 if (l_item_type is null OR l_item_type NOT IN (1,3)) then
574 raise_error('EAM_ABO_INVALID_INV_ITEM_ID');
575 /* elsif (l_item_type=1 and p_organization_id is null) then
576 raise_error('EAM_ASSET_ORG_ID_REQ');
577 elsif (l_item_type=1 and p_organization_id <> p_creation_organization_id) then
578 raise_error('EAM_ORG_ID_INCONSISTENT');
579 elsif (l_item_type=3 and p_organization_id is not null) then
580 raise_error('EAM_REBUILD_ORG_ID_NOT_NULL');
581 */
582 end if;
583
584 if (l_item_type = 1) then
585
586 IF (p_inventory_item_id IS NOT NULL and p_serial_number is not null
587 and p_maintenance_object_id IS NULL ) THEN
588
589 begin
590 select instance_id, 3 into l_maintenance_object_id, l_maintenance_object_type
591 from csi_item_instances
592 where serial_number = p_serial_number
593 and inventory_item_id = p_inventory_item_id;
594
595 exception when no_data_found then
596 raise_error('EAM_NO_ITEM_FOUND');
597 end;
598
599
600 ELSIF ((p_inventory_item_id IS NULL or p_serial_number is null) and
601 (p_maintenance_object_type IS NOT NULL and p_maintenance_object_id IS NOT NULL)) THEN
602
603 begin
604 select cii.serial_number, cii.inventory_item_id
605 into l_serial_number, l_asset_group_id
606 from csi_item_instances cii
607 where cii.instance_id = p_maintenance_object_id;
608
609 exception when no_data_found then
610 raise_error('EAM_NO_ITEM_FOUND');
611 END;
612
613 END IF;
614
615 /* Check both the combinations are pointing to the same item / serial_number */
616
617 l_validated := check_item_unique (
618 l_maintenance_object_type ,
619 l_maintenance_object_id ,
620 l_asset_group_id ,
621 l_org_id ,
622 l_serial_number ,
623 l_org_id
624 );
625
626 IF NOT l_validated THEN
627 raise_error ('EAM_INVALID_COMBO');
628 END IF;
629
630 ELSE -- Rebuildable
631
632 IF (p_inventory_item_id IS NOT NULL and p_serial_number is not null
633 and p_maintenance_object_id IS NULL ) THEN
634
635 begin
636 select instance_id,3
637 into l_maintenance_object_id,l_maintenance_object_type
638 from csi_item_instances
639 where inventory_item_id=p_inventory_item_id
640 and serial_number=p_serial_number;
641
642 exception
643 when no_data_found then
644 raise_error('EAM_NO_ASSET_FOUND');
645 end;
646
647 ELSIF ((p_inventory_item_id IS NULL or p_serial_number is null) and
648 (p_maintenance_object_type IS NOT NULL and p_maintenance_object_type = 3)) THEN
649
650 begin
651 select inventory_item_id, serial_number, last_vld_organization_id
655
652 into l_asset_group_id , l_serial_number, l_temp_org_id
653 from csi_item_instances
654 where instance_id=p_maintenance_object_id;
656 IF (l_org_id is null) THEN
657 l_org_id := l_temp_org_id;
658 END IF;
659
660 exception
661 when no_data_found then
662 raise_error('EAM_NO_ASSET_FOUND');
663 end;
664
665 END IF;
666
667 /* Check both the combinations are pointing to the same item / serial_number */
668
669 select count(*) into l_count
670 from csi_item_instances
671 where instance_id=l_maintenance_object_id
672 and inventory_item_id=l_asset_group_id
673 and serial_number=l_serial_number;
674
675 if (l_count = 0) then
676 raise_error('EAM_INVALID_COMBO');
677 end if;
678
679 END IF;
680
681 validate_application_id( P_APPLICATION_ID);
682
683 validate_descflexfield_name(P_DESCRIPTIVE_FLEXFIELD_NAME);
684
685 validate_descflex_context_code(P_ATTRIBUTE_CATEGORY, P_APPLICATION_ID);
686
687 validate_dff_segments( 'EAM',
688 p_ATTRIBUTE_CATEGORY ,
689 p_c_ATTRIBUTE1 ,
690 p_c_ATTRIBUTE2 ,
691 p_c_ATTRIBUTE3 ,
692 p_c_ATTRIBUTE4 ,
693 p_c_ATTRIBUTE5 ,
694 p_c_ATTRIBUTE6 ,
695 p_c_ATTRIBUTE7 ,
696 p_c_ATTRIBUTE8 ,
697 p_c_ATTRIBUTE9 ,
698 p_c_ATTRIBUTE10 ,
699 p_c_ATTRIBUTE11 ,
700 p_c_ATTRIBUTE12 ,
701 p_c_ATTRIBUTE13 ,
702 p_c_ATTRIBUTE14 ,
703 p_c_ATTRIBUTE15 ,
704 p_c_ATTRIBUTE16 ,
705 p_c_ATTRIBUTE17 ,
706 p_c_ATTRIBUTE18 ,
707 p_c_ATTRIBUTE19 ,
708 p_c_ATTRIBUTE20 ,
709 p_n_ATTRIBUTE1 ,
710 p_n_ATTRIBUTE2 ,
711 p_n_ATTRIBUTE3 ,
712 p_n_ATTRIBUTE4 ,
713 p_n_ATTRIBUTE5 ,
714 p_n_ATTRIBUTE6 ,
715 p_n_ATTRIBUTE7 ,
716 p_n_ATTRIBUTE8 ,
717 p_n_ATTRIBUTE9 ,
718 p_n_ATTRIBUTE10 ,
719 p_d_ATTRIBUTE1 ,
720 p_d_ATTRIBUTE2 ,
721 p_d_ATTRIBUTE3 ,
722 p_d_ATTRIBUTE4 ,
723 p_d_ATTRIBUTE5 ,
724 p_d_ATTRIBUTE6 ,
725 p_d_ATTRIBUTE7 ,
726 p_d_ATTRIBUTE8 ,
727 p_d_ATTRIBUTE9 ,
728 p_d_ATTRIBUTE10
729 );
730
731
732 /*
733 IF not EAM_COMMON_UTILITIES_PVT.validate_serial_number(
734 p_creation_organization_id ,
735 l_asset_group_id,
736 l_serial_number,
737 l_item_type
738 )
739 THEN
740 fnd_message.set_name('EAM', 'EAM_EZWO_ASSET_BAD');
741 fnd_msg_pub.add;
742 RAISE fnd_api.g_exc_error;
743 END IF;
744 */
745
746 /*validate_maintenance_object_id(p_creation_organization_id , P_MAINTENANCE_OBJECT_ID, p_MAINTENANCE_OBJECT_TYPE );*/
747
748 validate_assos_id(p_association_id, p_creation_organization_id, l_asset_group_id, p_attribute_category, l_item_type);
749
750
751
752
753 VALIDATE_ROW_EXISTS(P_ATTRIBUTE_CATEGORY ,
754 P_ASSOCIATION_ID ,
755 /*P_INVENTORY_ITEM_ID ,*/
756 l_asset_group_id,
757 /*P_SERIAL_NUMBER ,*/
758 l_serial_number,
759 /*P_CREATION_ORGANIZATION_ID , */
760 p_creation_organization_id,
761 TRUE,
762 l_item_type);
763
764
765 INSERT INTO MTL_EAM_ASSET_ATTR_VALUES
766 (
767 ASSOCIATION_ID ,
768 INVENTORY_ITEM_ID ,
769 SERIAL_NUMBER ,
770 ORGANIZATION_ID ,
771 ATTRIBUTE_CATEGORY ,
772 C_ATTRIBUTE1 ,
773 C_ATTRIBUTE2 ,
774 C_ATTRIBUTE3 ,
775 C_ATTRIBUTE4 ,
776 C_ATTRIBUTE5 ,
777 C_ATTRIBUTE6 ,
778 C_ATTRIBUTE7 ,
779 C_ATTRIBUTE8 ,
780 C_ATTRIBUTE9 ,
781 C_ATTRIBUTE10 ,
782 C_ATTRIBUTE11 ,
783 C_ATTRIBUTE12 ,
784 C_ATTRIBUTE13 ,
785 C_ATTRIBUTE14 ,
786 C_ATTRIBUTE15 ,
787 C_ATTRIBUTE16 ,
788 C_ATTRIBUTE17 ,
789 C_ATTRIBUTE18 ,
790 C_ATTRIBUTE19 ,
791 C_ATTRIBUTE20 ,
792 D_ATTRIBUTE1 ,
793 D_ATTRIBUTE2 ,
794 D_ATTRIBUTE3 ,
795 D_ATTRIBUTE4 ,
796 D_ATTRIBUTE5 ,
797 D_ATTRIBUTE6 ,
798 D_ATTRIBUTE7 ,
799 D_ATTRIBUTE8 ,
800 D_ATTRIBUTE9 ,
801 D_ATTRIBUTE10 ,
802 N_ATTRIBUTE1 ,
803 N_ATTRIBUTE2 ,
804 N_ATTRIBUTE3 ,
805 N_ATTRIBUTE4 ,
806 N_ATTRIBUTE5 ,
807 N_ATTRIBUTE6 ,
808 N_ATTRIBUTE7 ,
809 N_ATTRIBUTE8 ,
810 N_ATTRIBUTE9 ,
811 N_ATTRIBUTE10 ,
812 APPLICATION_ID ,
813 DESCRIPTIVE_FLEXFIELD_NAME ,
814 MAINTENANCE_OBJECT_TYPE ,
815 MAINTENANCE_OBJECT_ID ,
816 CREATION_ORGANIZATION_ID ,
817
818 CREATED_BY ,
819 CREATION_DATE ,
820 LAST_UPDATE_LOGIN ,
821 LAST_UPDATE_DATE ,
822 LAST_UPDATED_BY
823 )
824 VALUES
825 (
829 P_ORGANIZATION_ID ,*/
826 P_ASSOCIATION_ID ,
827 /*P_INVENTORY_ITEM_ID ,
828 P_SERIAL_NUMBER ,
830 l_asset_group_id,
831 l_serial_number,
832 p_organization_id,
833 P_ATTRIBUTE_CATEGORY ,
834 P_C_ATTRIBUTE1 ,
835 P_C_ATTRIBUTE2 ,
836 P_C_ATTRIBUTE3 ,
837 P_C_ATTRIBUTE4 ,
838 P_C_ATTRIBUTE5 ,
839 P_C_ATTRIBUTE6 ,
840 P_C_ATTRIBUTE7 ,
841 P_C_ATTRIBUTE8 ,
842 P_C_ATTRIBUTE9 ,
843 P_C_ATTRIBUTE10 ,
844 P_C_ATTRIBUTE11 ,
845 P_C_ATTRIBUTE12 ,
846 P_C_ATTRIBUTE13 ,
847 P_C_ATTRIBUTE14 ,
848 P_C_ATTRIBUTE15 ,
849 P_C_ATTRIBUTE16 ,
850 P_C_ATTRIBUTE17 ,
851 P_C_ATTRIBUTE18 ,
852 P_C_ATTRIBUTE19 ,
853 P_C_ATTRIBUTE20 ,
854 P_D_ATTRIBUTE1 ,
855 P_D_ATTRIBUTE2 ,
856 P_D_ATTRIBUTE3 ,
857 P_D_ATTRIBUTE4 ,
858 P_D_ATTRIBUTE5 ,
859 P_D_ATTRIBUTE6 ,
860 P_D_ATTRIBUTE7 ,
861 P_D_ATTRIBUTE8 ,
862 P_D_ATTRIBUTE9 ,
863 P_D_ATTRIBUTE10 ,
864 P_N_ATTRIBUTE1 ,
865 P_N_ATTRIBUTE2 ,
866 P_N_ATTRIBUTE3 ,
867 P_N_ATTRIBUTE4 ,
868 P_N_ATTRIBUTE5 ,
869 P_N_ATTRIBUTE6 ,
870 P_N_ATTRIBUTE7 ,
871 P_N_ATTRIBUTE8 ,
872 P_N_ATTRIBUTE9 ,
873 P_N_ATTRIBUTE10 ,
874 P_APPLICATION_ID ,
875 P_DESCRIPTIVE_FLEXFIELD_NAME ,
876 /*P_MAINTENANCE_OBJECT_TYPE ,
877 P_MAINTENANCE_OBJECT_ID ,
878 P_CREATION_ORGANIZATION_ID ,*/
879 l_maintenance_object_type,
880 l_maintenance_object_id,
881 p_creation_organization_id,
882
883 fnd_global.user_id,
884 sysdate,
885 fnd_global.login_id,
886 sysdate ,
887 fnd_global.user_id
888 );
889
890 -- End of API body.
891 -- Standard check of p_commit.
892 IF FND_API.To_Boolean( p_commit ) THEN
893 COMMIT WORK;
894 END IF;
895 -- Standard call to get message count and if count is 1, get message info.
896 FND_MSG_PUB.get
897 ( p_msg_index_out => x_msg_count ,
898 p_data => x_msg_data
899 );
900 EXCEPTION
901 WHEN FND_API.G_EXC_ERROR THEN
902 ROLLBACK TO insert_assetattr_value;
903 x_return_status := FND_API.G_RET_STS_ERROR ;
904 FND_MSG_PUB.get
905 ( p_msg_index_out => x_msg_count ,
906 p_data => x_msg_data
907 );
908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
909 ROLLBACK TO insert_assetattr_value;
910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
911 FND_MSG_PUB.get
912 ( p_msg_index_out => x_msg_count ,
913 p_data => x_msg_data
914 );
915 WHEN OTHERS THEN
916 ROLLBACK TO insert_assetattr_value;
917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
918 IF FND_MSG_PUB.Check_Msg_Level
919 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
920 THEN
921 FND_MSG_PUB.Add_Exc_Msg
922 ( G_PKG_NAME ,
923 l_api_name
924 );
925 END IF;
926 FND_MSG_PUB.get
927 ( p_msg_index_out => x_msg_count ,
928 p_data => x_msg_data
929 );
930 END insert_assetattr_value;
931
932
933 procedure update_assetattr_value
934 (
935 p_api_version IN NUMBER ,
936 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE ,
937 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
938 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
939 x_return_status OUT NOCOPY VARCHAR2 ,
940 x_msg_count OUT NOCOPY NUMBER ,
941 x_msg_data OUT NOCOPY VARCHAR2 ,
942 P_ASSOCIATION_ID IN NUMBER ,
943 P_APPLICATION_ID IN NUMBER default 401,
944 P_DESCRIPTIVE_FLEXFIELD_NAME IN VARCHAR2 default 'MTL_EAM_ASSET_ATTR_VALUES' ,
945 P_INVENTORY_ITEM_ID IN NUMBER default null,
946 P_SERIAL_NUMBER IN VARCHAR2 default null ,
947 P_ORGANIZATION_ID IN NUMBER ,
948 P_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
949 P_C_ATTRIBUTE1 IN VARCHAR2 default null,
950 P_C_ATTRIBUTE2 IN VARCHAR2 default null,
951 P_C_ATTRIBUTE3 IN VARCHAR2 default null,
952 P_C_ATTRIBUTE4 IN VARCHAR2 default null,
953 P_C_ATTRIBUTE5 IN VARCHAR2 default null,
954 P_C_ATTRIBUTE6 IN VARCHAR2 default null,
955 P_C_ATTRIBUTE7 IN VARCHAR2 default null,
956 P_C_ATTRIBUTE8 IN VARCHAR2 default null,
957 P_C_ATTRIBUTE9 IN VARCHAR2 default null,
958 P_C_ATTRIBUTE10 IN VARCHAR2 default null,
959 P_C_ATTRIBUTE11 IN VARCHAR2 default null,
960 P_C_ATTRIBUTE12 IN VARCHAR2 default null,
961 P_C_ATTRIBUTE13 IN VARCHAR2 default null,
962 P_C_ATTRIBUTE14 IN VARCHAR2 default null,
963 P_C_ATTRIBUTE15 IN VARCHAR2 default null,
964 P_C_ATTRIBUTE16 IN VARCHAR2 default null,
965 P_C_ATTRIBUTE17 IN VARCHAR2 default null,
966 P_C_ATTRIBUTE18 IN VARCHAR2 default null,
967 P_C_ATTRIBUTE19 IN VARCHAR2 default null,
968 P_C_ATTRIBUTE20 IN VARCHAR2 default null,
969 P_D_ATTRIBUTE1 IN DATE default null,
970 P_D_ATTRIBUTE2 IN DATE default null,
971 P_D_ATTRIBUTE3 IN DATE default null,
972 P_D_ATTRIBUTE4 IN DATE default null,
973 P_D_ATTRIBUTE5 IN DATE default null,
974 P_D_ATTRIBUTE6 IN DATE default null,
975 P_D_ATTRIBUTE7 IN DATE default null,
976 P_D_ATTRIBUTE8 IN DATE default null,
977 P_D_ATTRIBUTE9 IN DATE default null,
981 P_N_ATTRIBUTE3 IN NUMBER default null,
978 P_D_ATTRIBUTE10 IN DATE default null,
979 P_N_ATTRIBUTE1 IN NUMBER default null,
980 P_N_ATTRIBUTE2 IN NUMBER default null,
982 P_N_ATTRIBUTE4 IN NUMBER default null,
983 P_N_ATTRIBUTE5 IN NUMBER default null,
984 P_N_ATTRIBUTE6 IN NUMBER default null,
985 P_N_ATTRIBUTE7 IN NUMBER default null,
986 P_N_ATTRIBUTE8 IN NUMBER default null,
987 P_N_ATTRIBUTE9 IN NUMBER default null,
988 P_N_ATTRIBUTE10 IN NUMBER default null,
989 P_MAINTENANCE_OBJECT_TYPE IN VARCHAR2 default null,
990 P_MAINTENANCE_OBJECT_ID IN NUMBER default null,
991 P_CREATION_ORGANIZATION_ID IN NUMBER default null
992 )
993 IS
994 l_api_name CONSTANT VARCHAR2(30) := 'update_assetattr_value';
995 l_api_version CONSTANT NUMBER := 1.0;
996
997 l_boolean number;
998 l_return_status VARCHAR2(1);
999 l_msg_count NUMBER;
1000 l_msg_data VARCHAR2(30);
1001
1002 l_object_found BOOLEAN;
1003 l_maintenance_object_type NUMBER;
1004 l_maintenance_object_id NUMBER;
1005 l_creation_organization_id NUMBER;
1006 l_asset_group_id NUMBER;
1007 l_org_id NUMBER;
1008 l_temp_org_id NUMBER;
1009 l_validated boolean;
1010 l_item_type number;
1011 l_count number;
1012 l_item_id NUMBER;
1013 l_serial_number VARCHAR2(100);
1014
1015 BEGIN
1016 -- Standard Start of API savepoint
1017 SAVEPOINT update_assetattr_value;
1018 -- Standard call to check for call compatibility.
1019 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1020 p_api_version ,
1021 l_api_name ,
1022 G_PKG_NAME )
1023 THEN
1024 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1025 END IF;
1026 -- Initialize message list if p_init_msg_list is set to TRUE.
1027 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1028 FND_MSG_PUB.initialize;
1029 END IF;
1030 -- Initialize API return status to success
1031 x_return_status := FND_API.G_RET_STS_SUCCESS;
1032 -- API body
1033
1034 l_org_id := P_ORGANIZATION_ID;
1035 if (P_CREATION_ORGANIZATION_ID IS NOT NULL) then
1036 if P_CREATION_ORGANIZATION_ID <> P_ORGANIZATION_ID then
1037 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_CR_ORG_ID');
1038 fnd_msg_pub.add;
1039 RAISE fnd_api.g_exc_error;
1040 else
1041
1042 l_creation_organization_id := P_ORGANIZATION_ID;
1043 end if;
1044 else
1045 l_creation_organization_id := P_ORGANIZATION_ID;
1046 end if;
1047
1048 if l_creation_organization_id is not null then
1049 EAM_COMMON_UTILITIES_PVT.verify_org(
1050 p_resp_id => NULL,
1051 p_resp_app_id => 401,
1052 p_org_id => l_CREATION_ORGANIZATION_ID,
1053 x_boolean => l_boolean,
1054 x_return_status => l_return_status,
1055 x_msg_count => l_msg_count ,
1056 x_msg_data => l_msg_data);
1057 if l_boolean = 0
1058 then
1059 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_ORG_ID');
1060 fnd_msg_pub.add;
1061 RAISE fnd_api.g_exc_error;
1062 end if;
1063 end if;
1064
1065
1066 /* validate / populate inventory_item_id, serial_number, maintenance_object_id, maintenance_object_type */
1067
1068 IF ((p_maintenance_object_type is null or p_maintenance_object_id is null) and
1069 (p_inventory_item_id is null or p_serial_number is null)) THEN
1070 fnd_message.set_name('EAM', 'EAM_NOT_ENOUGH_PARAM');
1071 fnd_msg_pub.add;
1072 RAISE FND_API.G_EXC_ERROR;
1073 END IF;
1074
1075 IF (p_maintenance_object_type is not null and p_maintenance_object_type <> 3) THEN
1076 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_MAINT_OBJ_TYPE');
1077 fnd_msg_pub.add;
1078 RAISE FND_API.G_EXC_ERROR;
1079 END IF;
1080
1081 l_maintenance_object_type := p_maintenance_object_type;
1082 l_maintenance_object_id := p_maintenance_object_id;
1083 l_asset_group_id := p_inventory_item_id;
1084 l_serial_number := p_serial_number;
1085 l_org_id := p_creation_organization_id;
1086
1087 IF (p_inventory_item_id IS NOT NULL) THEN
1088
1089 /* validate item id; get item type */
1090 l_item_type:=get_item_type(p_creation_organization_id, l_asset_group_id);
1091
1092 ELSE
1093 begin
1094 select msi.eam_item_type into l_item_type
1095 from mtl_system_items msi, csi_item_instances cii
1096 where cii.inventory_item_id = msi.inventory_item_id
1097 and cii.last_vld_organization_id = msi.organization_id
1098 and cii.instance_id = p_maintenance_object_id;
1099
1100 exception
1101 when no_data_found then
1102 raise_error('EAM_ABO_INVLD_MT_GEN_OBJ_ID');
1103 when too_many_rows then
1104 raise_error('EAM_INVALID_MAINT_OBJ_ID');
1105 END;
1106 END IF;
1107 if (l_item_type is null OR l_item_type NOT IN (1,3)) then
1108 raise_error('EAM_ABO_INVALID_INV_ITEM_ID');
1109 elsif (l_item_type=1 and p_organization_id is null) then
1113 elsif (l_item_type=3 and p_organization_id is not null) then
1110 raise_error('EAM_ASSET_ORG_ID_REQ');
1111 elsif (l_item_type=1 and p_organization_id <> p_creation_organization_id) then
1112 raise_error('EAM_ORG_ID_INCONSISTENT');
1114 raise_error('EAM_REBUILD_ORG_ID_NOT_NULL');
1115 end if;
1116
1117 if (l_item_type = 1) then
1118
1119 IF (p_inventory_item_id IS NOT NULL and p_serial_number is not null
1120 and p_maintenance_object_id IS NULL ) THEN
1121
1122 begin
1123 select instance_id, 3 into l_maintenance_object_id, l_maintenance_object_type
1124 from csi_item_instances
1125 where serial_number = p_serial_number
1126 and inventory_item_id = p_inventory_item_id;
1127
1128 exception when no_data_found then
1129 raise_error('EAM_NO_ITEM_FOUND');
1130 end;
1131
1132
1133 ELSIF ((p_inventory_item_id IS NULL or p_serial_number is null) and
1134 (p_maintenance_object_type IS NOT NULL and p_maintenance_object_id IS NOT NULL)) THEN
1135
1136 begin
1137 select cii.serial_number, cii.inventory_item_id
1138 into l_serial_number, l_asset_group_id
1139 from csi_item_instances cii
1140 where cii.instance_id = p_maintenance_object_id;
1141
1142 exception when no_data_found then
1143 raise_error('EAM_NO_ITEM_FOUND');
1144 END;
1145
1146 END IF;
1147
1148 /* Check both the combinations are pointing to the same item / serial_number */
1149
1150 l_validated := check_item_unique (
1151 l_maintenance_object_type ,
1152 l_maintenance_object_id ,
1153 l_asset_group_id ,
1154 l_org_id ,
1155 l_serial_number ,
1156 l_org_id
1157 );
1158
1159 IF NOT l_validated THEN
1160 raise_error ('EAM_INVALID_COMBO');
1161 END IF;
1162
1163 ELSE -- Rebuildable
1164
1165 IF (p_inventory_item_id IS NOT NULL and p_serial_number is not null
1166 and p_maintenance_object_id IS NULL ) THEN
1167
1168 begin
1169 select instance_id,3
1170 into l_maintenance_object_id,l_maintenance_object_type
1171 from csi_item_instances
1172 where inventory_item_id=p_inventory_item_id
1173 and serial_number=p_serial_number;
1174
1175 exception
1176 when no_data_found then
1177 raise_error('EAM_NO_ASSET_FOUND');
1178 end;
1179
1180 ELSIF ((p_inventory_item_id IS NULL or p_serial_number is null) and
1181 (p_maintenance_object_type IS NOT NULL and p_maintenance_object_type = 3)) THEN
1182
1183 begin
1184 select inventory_item_id, serial_number, last_vld_organization_id
1185 into l_item_id , l_serial_number, l_temp_org_id
1186 from csi_item_instances
1187 where instance_id=p_maintenance_object_id;
1188
1189 IF (l_org_id is null) THEN
1190 l_org_id := l_temp_org_id;
1191 END IF;
1192
1193 exception
1194 when no_data_found then
1195 raise_error('EAM_NO_ASSET_FOUND');
1196 end;
1197
1198 END IF;
1199
1200 /* Check both the combinations are pointing to the same item / serial_number */
1201
1202 select count(*) into l_count
1203 from csi_item_instances
1204 where instance_id=l_maintenance_object_id
1205 and inventory_item_id=l_asset_group_id
1206 and serial_number=l_serial_number;
1207
1208 if (l_count = 0) then
1209 raise_error('EAM_INVALID_COMBO');
1210 end if;
1211
1212 END IF;
1213
1214 validate_application_id( P_APPLICATION_ID);
1215
1216 validate_descflexfield_name(P_DESCRIPTIVE_FLEXFIELD_NAME);
1217
1218 validate_descflex_context_code(P_ATTRIBUTE_CATEGORY, P_APPLICATION_ID);
1219
1220 validate_assos_id(p_association_id, p_creation_organization_id, l_asset_group_id, p_attribute_category, l_item_type);
1221
1222 VALIDATE_ROW_EXISTS(P_ATTRIBUTE_CATEGORY ,
1223 P_ASSOCIATION_ID ,
1224 /*P_INVENTORY_ITEM_ID ,*/
1225 l_asset_group_id,
1226 /*P_SERIAL_NUMBER ,*/
1227 l_serial_number,
1228 /*P_CREATION_ORGANIZATION_ID , */
1229 p_creation_organization_id,
1230 FALSE,
1231 l_item_type);
1232
1233 validate_dff_segments( 'EAM',
1234 p_ATTRIBUTE_CATEGORY ,
1235 p_c_ATTRIBUTE1 ,
1236 p_c_ATTRIBUTE2 ,
1237 p_c_ATTRIBUTE3 ,
1238 p_c_ATTRIBUTE4 ,
1239 p_c_ATTRIBUTE5 ,
1240 p_c_ATTRIBUTE6 ,
1241 p_c_ATTRIBUTE7 ,
1242 p_c_ATTRIBUTE8 ,
1243 p_c_ATTRIBUTE9 ,
1244 p_c_ATTRIBUTE10 ,
1245 p_c_ATTRIBUTE11 ,
1246 p_c_ATTRIBUTE12 ,
1247 p_c_ATTRIBUTE13 ,
1248 p_c_ATTRIBUTE14 ,
1249 p_c_ATTRIBUTE15 ,
1250 p_c_ATTRIBUTE16 ,
1251 p_c_ATTRIBUTE17 ,
1252 p_c_ATTRIBUTE18 ,
1253 p_c_ATTRIBUTE19 ,
1254 p_c_ATTRIBUTE20 ,
1255 p_n_ATTRIBUTE1 ,
1256 p_n_ATTRIBUTE2 ,
1257 p_n_ATTRIBUTE3 ,
1258 p_n_ATTRIBUTE4 ,
1259 p_n_ATTRIBUTE5 ,
1260 p_n_ATTRIBUTE6 ,
1261 p_n_ATTRIBUTE7 ,
1262 p_n_ATTRIBUTE8 ,
1263 p_n_ATTRIBUTE9 ,
1267 p_d_ATTRIBUTE3 ,
1264 p_n_ATTRIBUTE10 ,
1265 p_d_ATTRIBUTE1 ,
1266 p_d_ATTRIBUTE2 ,
1268 p_d_ATTRIBUTE4 ,
1269 p_d_ATTRIBUTE5 ,
1270 p_d_ATTRIBUTE6 ,
1271 p_d_ATTRIBUTE7 ,
1272 p_d_ATTRIBUTE8 ,
1273 p_d_ATTRIBUTE9 ,
1274 p_d_ATTRIBUTE10
1275 );
1276
1277
1278 update MTL_EAM_ASSET_ATTR_VALUES
1279 set
1280 --ASSOCIATION_ID = P_ASSOCIATION_ID ,
1281 --INVENTORY_ITEM_ID= l_asset_group_id,/*P_INVENTORY_ITEM_ID ,*/
1282 --SERIAL_NUMBER = l_serial_number,/*P_SERIAL_NUMBER ,*/
1283 --ORGANIZATION_ID = P_ORGANIZATION_ID , do not update this field
1284 --ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY ,
1285 C_ATTRIBUTE1 = P_C_ATTRIBUTE1 ,
1286 C_ATTRIBUTE2 = P_C_ATTRIBUTE2 ,
1287 C_ATTRIBUTE3 = P_C_ATTRIBUTE3 ,
1288 C_ATTRIBUTE4 = P_C_ATTRIBUTE4 ,
1289 C_ATTRIBUTE5 = P_C_ATTRIBUTE5 ,
1290 C_ATTRIBUTE6 = P_C_ATTRIBUTE6 ,
1291 C_ATTRIBUTE7 = P_C_ATTRIBUTE7 ,
1292 C_ATTRIBUTE8 = P_C_ATTRIBUTE8 ,
1293 C_ATTRIBUTE9 = P_C_ATTRIBUTE9 ,
1294 C_ATTRIBUTE10 = P_C_ATTRIBUTE10 ,
1295 C_ATTRIBUTE11 = P_C_ATTRIBUTE11 ,
1296 C_ATTRIBUTE12 = P_C_ATTRIBUTE12 ,
1297 C_ATTRIBUTE13 = P_C_ATTRIBUTE13 ,
1298 C_ATTRIBUTE14 = P_C_ATTRIBUTE14 ,
1299 C_ATTRIBUTE15 = P_C_ATTRIBUTE15 ,
1300 C_ATTRIBUTE16 = P_C_ATTRIBUTE16 ,
1301 C_ATTRIBUTE17 = P_C_ATTRIBUTE17 ,
1302 C_ATTRIBUTE18 = P_C_ATTRIBUTE18 ,
1303 C_ATTRIBUTE19 = P_C_ATTRIBUTE19 ,
1304 C_ATTRIBUTE20 = P_C_ATTRIBUTE20 ,
1305 D_ATTRIBUTE1 = P_D_ATTRIBUTE1 ,
1306 D_ATTRIBUTE2 = P_D_ATTRIBUTE2 ,
1307 D_ATTRIBUTE3 = P_D_ATTRIBUTE3 ,
1308 D_ATTRIBUTE4 = P_D_ATTRIBUTE4 ,
1309 D_ATTRIBUTE5 = P_D_ATTRIBUTE5 ,
1310 D_ATTRIBUTE6 = P_D_ATTRIBUTE6 ,
1311 D_ATTRIBUTE7 = P_D_ATTRIBUTE7 ,
1312 D_ATTRIBUTE8 = P_D_ATTRIBUTE8 ,
1313 D_ATTRIBUTE9 = P_D_ATTRIBUTE9 ,
1314 D_ATTRIBUTE10 = P_D_ATTRIBUTE10 ,
1315 N_ATTRIBUTE1 = P_N_ATTRIBUTE1 ,
1316 N_ATTRIBUTE2 = P_N_ATTRIBUTE2 ,
1317 N_ATTRIBUTE3 = P_N_ATTRIBUTE3 ,
1318 N_ATTRIBUTE4 = P_N_ATTRIBUTE4 ,
1319 N_ATTRIBUTE5 = P_N_ATTRIBUTE5 ,
1320 N_ATTRIBUTE6 = P_N_ATTRIBUTE6 ,
1321 N_ATTRIBUTE7 = P_N_ATTRIBUTE7 ,
1322 N_ATTRIBUTE8 = P_N_ATTRIBUTE8 ,
1323 N_ATTRIBUTE9 = P_N_ATTRIBUTE9 ,
1324 N_ATTRIBUTE10 = P_N_ATTRIBUTE10 ,
1325 APPLICATION_ID = P_APPLICATION_ID ,
1326 DESCRIPTIVE_FLEXFIELD_NAME = P_DESCRIPTIVE_FLEXFIELD_NAME ,
1327 MAINTENANCE_OBJECT_TYPE = l_maintenance_object_type , /*P_MAINTENANCE_OBJECT_TYPE ,*/
1328 MAINTENANCE_OBJECT_ID = l_maintenance_object_id ,/*P_MAINTENANCE_OBJECT_ID ,*/
1329 CREATION_ORGANIZATION_ID = p_creation_organization_id ,/*P_CREATION_ORGANIZATION_ID ,*/
1330 CREATED_BY = fnd_global.user_id ,
1331 CREATION_DATE = sysdate ,
1332 LAST_UPDATE_LOGIN = fnd_global.login_id,
1333 LAST_UPDATE_DATE = sysdate ,
1334 LAST_UPDATED_BY = fnd_global.user_id
1335
1336 where
1337 ASSOCIATION_ID = P_ASSOCIATION_ID and
1338 INVENTORY_ITEM_ID = l_asset_group_id and
1339 SERIAL_NUMBER = l_serial_number and
1340 /* removing this as creation_organization_id is not used */
1341 /* decode(l_item_type,1,CREATION_ORGANIZATION_ID,1) = decode(l_item_type,1,p_creation_organization_id,1) and */
1342 ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY;
1343
1344
1345
1346
1347 -- End of API body.
1348 -- Standard check of p_commit.
1349 IF FND_API.To_Boolean( p_commit ) THEN
1350 COMMIT WORK;
1351 END IF;
1352 -- Standard call to get message count and if count is 1, get message info.
1353 FND_MSG_PUB.get
1354 ( p_msg_index_out => x_msg_count ,
1355 p_data => x_msg_data
1356 );
1357 EXCEPTION
1358 WHEN FND_API.G_EXC_ERROR THEN
1359 ROLLBACK TO update_assetattr_value;
1360 x_return_status := FND_API.G_RET_STS_ERROR ;
1361 FND_MSG_PUB.get
1362 ( p_msg_index_out => x_msg_count ,
1363 p_data => x_msg_data
1364 );
1365 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1366 ROLLBACK TO update_assetattr_value;
1367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1368 FND_MSG_PUB.get
1369 ( p_msg_index_out => x_msg_count ,
1370 p_data => x_msg_data
1371 );
1372 WHEN OTHERS THEN
1373 ROLLBACK TO update_assetattr_value;
1374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1375 IF FND_MSG_PUB.Check_Msg_Level
1376 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1377 THEN
1378 FND_MSG_PUB.Add_Exc_Msg
1379 ( G_PKG_NAME ,
1380 l_api_name
1381 );
1382 END IF;
1383 FND_MSG_PUB.get
1384 ( p_msg_index_out => x_msg_count ,
1385 p_data => x_msg_data
1386 );
1387 END update_assetattr_value;
1388
1389 END EAM_ASSETATTR_VALUE_PUB;