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