[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_ROUTES_PUB
Source
1 PACKAGE BODY EAM_ASSET_ROUTES_PUB AS
2 /* $Header: EAMPAROB.pls 120.3.12020000.3 2012/11/15 11:28:54 srkotika ship $ */
3 -- Start of comments
4 -- API name : EAM_ASSET_ROUTES_PUB
5 -- Type : Public
6 -- Function : insert_asset_routes, update_asset_routes
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_ASSET_ROUTES_PUB';
42 /* for de-bugging */
43 /*g_sr_no number ;*/
44
45 PROCEDURE print_log(info varchar2) is
46 PRAGMA AUTONOMOUS_TRANSACTION;
47 l_dummy number;
48 BEGIN
49
50 FND_FILE.PUT_LINE(FND_FILE.LOG, info);
51
52 END;
53
54 /* function checking the item unique when both the 2 combinations are provided by the user */
55 FUNCTION check_item_unique (
56 p_maintenance_object_type NUMBER,
57 p_maintenance_object_id NUMBER,
58 p_asset_group_id NUMBER,
59 p_organization_id NUMBER,
60 p_asset_number VARCHAR2,
61 P_SERIAL_NUMBER VARCHAR2,
62 p_creation_organization_id NUMBER
63 )
64 RETURN boolean
65 IS
66 l_count_rec NUMBER := 0;
67 BEGIN
68 IF (p_maintenance_object_type = 1) THEN
69 IF ( p_asset_number IS NOT NULL ) THEN
70 SELECT count(*) INTO l_count_rec
71 FROM mtl_system_items MSI , mtl_serial_numbers MSN
72 WHERE MSN.serial_number = p_asset_number
73 AND MSN.gen_object_id = p_maintenance_object_id
74 AND MSN.inventory_item_id = MSI.inventory_item_id
75 AND MSI.inventory_item_id = p_asset_group_id
76 AND MSI.organization_id = p_creation_organization_id;
77 END IF;
78
79 ELSIF (p_maintenance_object_type = 2) THEN
80 IF ((p_asset_number IS NULL) AND
81 (p_maintenance_object_id = p_asset_group_id)) THEN
82 SELECT count(*) INTO l_count_rec
83 FROM mtl_system_items
84 WHERE inventory_item_id = p_asset_group_id
85 AND organization_id = p_creation_organization_id;
86 END IF;
87
88 END IF;
89
90 IF (l_count_rec > 0) THEN
91 RETURN true;
92 ELSE
93 RETURN false;
94 END IF;
95 END check_item_unique;
96
97 /* For raising error */
98 PROCEDURE RAISE_ERROR (ERROR VARCHAR2)
99 IS
100 BEGIN
101
102
103 FND_MESSAGE.SET_NAME ('EAM', ERROR);
104 FND_MSG_PUB.ADD;
105 RAISE FND_API.G_EXC_ERROR;
106 END;
107
108 PROCEDURE validate_object_id(p_organization_id in number,
109 p_object_id in number,
110 p_eam_item_type in number,
111 p_inventory_item_id in number ,
112 p_serial_number in string ,
113 x_return_status out NOCOPY VARCHAR2 ,
114 x_msg_count OUT NOCOPY NUMBER ,
115 x_msg_data OUT NOCOPY VARCHAR2
116 )
117 is
118
119 CURSOR c_val_object_id(p_organization_id number,
120 p_object_id number,
121 p_eam_item_type number,
122 p_inventory_item_id number ,
123 p_serial_number string)
124 IS
125 SELECT
126 msn.gen_object_id
127 FROM
128 mtl_serial_numbers msn,
129 mtl_system_items_b msi
130 WHERE
131 msn.inventory_item_id = p_inventory_item_id and
132 msn.serial_number= p_serial_number and
133 msn.current_organization_id = p_organization_id and
134 msi.inventory_item_id = msn.inventory_item_id and
135 msi.eam_item_type = 1 and
136 msi.organization_id = msn.current_organization_id and
137 msn.current_status=3;
138
139 l_gen_obj_id NUMBER;
140
141 begin
142 IF p_object_id IS NULL OR p_eam_item_type IS NULL OR p_organization_id IS NULL or p_inventory_item_id is null or p_serial_number is null
143 THEN
144 RETURN ;
145 END IF ;
146
147 if p_eam_item_type = 1
148 then
149 open c_val_object_id (p_organization_id , p_object_id , p_eam_item_type , p_inventory_item_id , p_serial_number );
150 fetch c_val_object_id into l_gen_obj_id;
151 if c_val_object_id%FOUND
152 THEN
153 IF P_OBJECT_ID <> L_GEN_OBJ_ID
154 THEN
155 fnd_message.set_name('EAM', 'EAM_ARO_INV_GENOBJ_NOTFOUND');
156 fnd_msg_pub.add;
157 RAISE fnd_api.g_exc_error;
158 END IF;
159 ELSE
160 fnd_message.set_name('EAM', 'EAM_ARO_INV_GENOBJ_NOTFOUND');
161 fnd_msg_pub.add;
162 RAISE fnd_api.g_exc_error;
163 END IF;
164
165 elsif p_eam_item_type = 2
166 then
167 fnd_message.set_name('EAM', 'EAM_ARO_INV_ASSET_TYPE');
168 fnd_msg_pub.add;
169 RAISE fnd_api.g_exc_error;
170 end if;
171
172 END;
173
174 PROCEDURE validate_record_for_route(p_object_id in number,
175 p_asset_route in VARCHAR2,
176 p_error_msg in VARCHAR2)
177 is
178 l_asset_route varchar2(1);
179 BEGIN
180 select network_asset_flag into l_asset_route from mtl_serial_numbers where gen_object_id = p_object_id;
181 IF (p_asset_route <> nvl(l_asset_route,'N')) then
182 raise_error(p_error_msg);
183 END IF;
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 raise_error(p_error_msg);
187 END validate_record_for_route;
188
189
190
191 procedure validate_dff_segments(
192 p_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
193 p_ATTRIBUTE1 IN VARCHAR2 default null,
194 p_ATTRIBUTE2 IN VARCHAR2 default null,
195 p_ATTRIBUTE3 IN VARCHAR2 default null,
196 p_ATTRIBUTE4 IN VARCHAR2 default null,
197 p_ATTRIBUTE5 IN VARCHAR2 default null,
198 p_ATTRIBUTE6 IN VARCHAR2 default null,
199 p_ATTRIBUTE7 IN VARCHAR2 default null,
200 p_ATTRIBUTE8 IN VARCHAR2 default null,
201 p_ATTRIBUTE9 IN VARCHAR2 default null,
202 p_ATTRIBUTE10 IN VARCHAR2 default null,
203 p_ATTRIBUTE11 IN VARCHAR2 default null,
204 p_ATTRIBUTE12 IN VARCHAR2 default null,
205 p_ATTRIBUTE13 IN VARCHAR2 default null,
206 p_ATTRIBUTE14 IN VARCHAR2 default null,
207 p_ATTRIBUTE15 IN VARCHAR2 default null
208 )
209 is
210 l_error_segments number;
211 l_error_message varchar2(4000);
212
213 begin
214 -- validate the desc. flex fields
215 if not EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field
216 (
217 p_app_short_name => 'INV',
218 p_desc_flex_name => 'MTL_EAM_NETWORK_ASSETS',
219 p_ATTRIBUTE_CATEGORY => p_ATTRIBUTE_CATEGORY,
220 p_ATTRIBUTE1 => p_ATTRIBUTE1,
221 p_ATTRIBUTE2 => p_ATTRIBUTE2,
222 p_ATTRIBUTE3 => p_ATTRIBUTE3,
223 p_ATTRIBUTE4 => p_ATTRIBUTE4,
224 p_ATTRIBUTE5 => p_ATTRIBUTE5,
225 p_ATTRIBUTE6 => p_ATTRIBUTE6,
226 p_ATTRIBUTE7 => p_ATTRIBUTE7,
227 p_ATTRIBUTE8 => p_ATTRIBUTE8,
228 p_ATTRIBUTE9 => p_ATTRIBUTE9,
229 p_ATTRIBUTE10 => p_ATTRIBUTE10,
230 p_ATTRIBUTE11 => p_ATTRIBUTE11,
231 p_ATTRIBUTE12 => p_ATTRIBUTE12,
232 p_ATTRIBUTE13 => p_ATTRIBUTE13,
233 p_ATTRIBUTE14 => p_ATTRIBUTE14,
234 p_ATTRIBUTE15 => p_ATTRIBUTE15,
235 x_error_segments => l_error_segments,
236 x_error_message => l_error_message
237 )
238 then
239 FND_MESSAGE.SET_NAME('EAM', 'EAM_INVALID_DESC_FLEX');
240 FND_MESSAGE.SET_TOKEN('ERROR_MSG', l_error_message);
241 FND_MSG_PUB.Add;
242 RAISE FND_API.G_EXC_ERROR;
243 end if;
244
245 end validate_dff_segments;
246
247
248 procedure VALIDATE_ROW_EXISTS( P_NETWORK_ITEM_ID IN NUMBER ,
249 P_NETWORK_SERIAL_NUMBER IN VARCHAR2 ,
250 P_INVENTORY_ITEM_ID IN NUMBER ,
251 P_SERIAL_NUMBER IN VARCHAR2 ,
252 P_ORGANIZATION_ID IN NUMBER,
253 p_create_flag in NUMBER,
254 p_network_association_id IN NUMBER := null)
255
256 is
257 l_count number;
258 BEGIN
259 SELECT COUNT(*) INTO l_count
260 FROM MTL_EAM_NETWORK_ASSETS
261 WHERE network_item_id= p_network_ITEM_id
262 AND network_serial_number= p_network_serial_number
263 AND inventory_item_id = p_inventory_item_id
264 AND serial_number= p_serial_number
265 AND organization_id = p_organization_id
266 AND decode(p_create_flag, 0, NETWORK_ASSOCIATION_ID,1) =
267 decode(p_create_flag, 0, p_network_association_id,1);
268 if (l_count = 0) then
269 if (p_create_flag = 0) then
270 fnd_message.set_name('EAM', 'EAM_NETWORK_REC_NOT_FOUND');
271 fnd_msg_pub.add;
272 RAISE fnd_api.g_exc_error;
273 END IF;
274 else
275 if (p_create_flag = 1) then
276 fnd_message.set_name('EAM', 'EAM_NETWORK_REC_EXISTS');
277 fnd_msg_pub.add;
278 RAISE fnd_api.g_exc_error;
279 END IF;
280 end if;
281 END;
282
283
284
285 PROCEDURE insert_asset_routes
286 (
287 p_api_version IN NUMBER ,
288 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE ,
289 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
290 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
291 x_return_status OUT NOCOPY VARCHAR2 ,
292 x_msg_count OUT NOCOPY NUMBER ,
293 x_msg_data OUT NOCOPY VARCHAR2 ,
294
295 P_ORGANIZATION_ID IN NUMBER ,
296 P_START_DATE_ACTIVE IN DATE default null,
297 P_END_DATE_ACTIVE IN DATE default null,
298 P_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
299 P_ATTRIBUTE1 IN VARCHAR2 default null,
300 P_ATTRIBUTE2 IN VARCHAR2 default null,
301 P_ATTRIBUTE3 IN VARCHAR2 default null,
302 P_ATTRIBUTE4 IN VARCHAR2 default null,
303 P_ATTRIBUTE5 IN VARCHAR2 default null,
304 P_ATTRIBUTE6 IN VARCHAR2 default null,
305 P_ATTRIBUTE7 IN VARCHAR2 default null,
306 P_ATTRIBUTE8 IN VARCHAR2 default null,
307 P_ATTRIBUTE9 IN VARCHAR2 default null,
308 P_ATTRIBUTE10 IN VARCHAR2 default null,
309 P_ATTRIBUTE11 IN VARCHAR2 default null,
310 P_ATTRIBUTE12 IN VARCHAR2 default null,
311 P_ATTRIBUTE13 IN VARCHAR2 default null,
312 P_ATTRIBUTE14 IN VARCHAR2 default null,
313 P_ATTRIBUTE15 IN VARCHAR2 default null,
314 P_NETWORK_ITEM_ID IN NUMBER ,
315 P_NETWORK_SERIAL_NUMBER IN VARCHAR2 ,
316 P_INVENTORY_ITEM_ID IN NUMBER ,
317 P_SERIAL_NUMBER IN VARCHAR2 ,
318 P_NETWORK_OBJECT_TYPE IN NUMBER default null ,
319 P_NETWORK_OBJECT_ID IN NUMBER default null ,
320 P_MAINTENANCE_OBJECT_TYPE IN NUMBER default null ,
321 P_MAINTENANCE_OBJECT_ID IN NUMBER default null ,
322 P_NETWORK_ASSET_NUMBER IN VARCHAR2 default null ,
323 P_ASSET_NUMBER IN VARCHAR2 default null
324 )
325 IS
326 l_api_name CONSTANT VARCHAR2(30) := 'APIname';
327 l_api_version CONSTANT NUMBER := 1.0;
328 l_boolean number;
329 l_return_status VARCHAR2(1);
330 l_msg_count NUMBER;
331 l_msg_data VARCHAR2(30);
332 l_network_id number;
333
334 l_object_found BOOLEAN;
335 l_network_object_type NUMBER;
336 l_network_object_id NUMBER;
337 l_maintenance_object_type NUMBER;
338 l_maintenance_object_id NUMBER;
339 l_creation_organization_id NUMBER;
340 l_asset_group_id NUMBER;
341 l_org_id NUMBER;
342 l_temp_org_id NUMBER;
343 l_serial_number VARCHAR2(100);
344 l_asset_number VARCHAR2(100);
345 l_validated boolean;
346 l_network_item_id NUMBER;
347 l_network_serial_number VARCHAR2(100);
348 l_item_id NUMBER;
349 l_count number;
350
351 BEGIN
352 -- Standard Start of API savepoint
353 SAVEPOINT insert_asset_routes;
354 -- Standard call to check for call compatibility.
355 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
356 p_api_version ,
357 l_api_name ,
358 G_PKG_NAME )
359 THEN
360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
361 END IF;
362 -- Initialize message list if p_init_msg_list is set to TRUE.
363 IF FND_API.to_Boolean( p_init_msg_list ) THEN
364 FND_MSG_PUB.initialize;
365 END IF;
366 -- Initialize API return status to success
367 x_return_status := FND_API.G_RET_STS_SUCCESS;
368 -- API body
369
370 /* Anand- for creation_organization_id = organization_id */
371
372 l_org_id := P_ORGANIZATION_ID;
373 l_creation_organization_id := P_ORGANIZATION_ID;
374
375
376 --ver eam enabled
377 EAM_COMMON_UTILITIES_PVT.verify_org(
378 p_resp_id =>NULL,
379 p_resp_app_id => 401,
380 p_org_id => l_org_id,
381 x_boolean => l_boolean,
382 x_return_status => l_return_status,
383 x_msg_count => l_msg_count ,
384 x_msg_data => l_msg_data);
385 if l_boolean = 0
386 then
387 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_ORG_ID');
388 fnd_msg_pub.add;
389 RAISE fnd_api.g_exc_error;
390 end if;
391
392
393 -- Bug # 3441956
394 IF (((p_network_item_id is null or p_network_serial_number is null) and
395 (p_network_object_id is null or p_network_object_type is null) and
396 (p_network_asset_number is null)) OR
397 (p_network_object_type is not null and p_network_object_type <> 3)) THEN
398 fnd_message.set_name('EAM', 'EAM_NETWORK_REC_NOT_FOUND');
399 fnd_msg_pub.add;
400 RAISE FND_API.G_EXC_ERROR;
401 END IF;
402
403
404 IF (((p_maintenance_object_type is null or p_maintenance_object_id is null) and
405 (p_inventory_item_id is null or p_serial_number is null) and
406 (p_asset_number is null)) OR
407 (p_maintenance_object_type is not null and p_maintenance_object_type <> 3)) THEN
408 fnd_message.set_name('EAM', 'EAM_EZWO_ASSET_BAD');
409 fnd_msg_pub.add;
410 RAISE FND_API.G_EXC_ERROR;
411 END IF;
412
413 l_network_object_type := p_network_object_type;
414 l_network_object_id := p_network_object_id;
415 l_network_item_id := p_network_item_id;
416 l_network_serial_number := p_network_serial_number;
417
418 if (p_network_asset_number is not null and l_network_object_id IS NULL ) THEN
419
420 begin
421 select instance_id into l_network_object_id
422 from csi_item_instances
423 where instance_number = p_network_asset_number;
424
425 exception when no_data_found then
426 raise_error('EAM_NETWORK_REC_NOT_FOUND');
427 end;
428
429 END IF;
430
431 IF (l_network_item_id IS NOT NULL AND l_network_serial_number IS NOT NULL
432 AND l_network_object_id IS NULL ) THEN
433
434 begin
435 select instance_id into l_network_object_id
436 from csi_item_instances
437 where serial_number = l_network_serial_number
438 and inventory_item_id = l_network_item_id;
439 exception when no_data_found then
440 raise_error('EAM_NETWORK_REC_NOT_FOUND');
441 end;
442 END IF;
443
444 IF ((l_network_serial_number is null or l_network_item_id is null ) and
445 (l_network_object_id is not null and l_network_object_type is not null)) THEN
446
447 begin
448 select cii.serial_number, cii.inventory_item_id
449 into l_network_serial_number, l_network_item_id
450 from csi_item_instances cii
451 where cii.instance_id = l_network_object_id;
452
453 exception when no_data_found then
454 raise_error('EAM_NETWORK_REC_NOT_FOUND');
455 end;
456
457 end if;
458
459
460 --validate the network object Id exists
461 begin
462 select count(*) into l_count
463 from csi_item_instances cii
464 where cii.instance_id = l_network_object_id
465 and cii.serial_number = l_network_serial_number
466 and cii.inventory_item_id = l_network_item_id
467 and nvl(cii.network_asset_flag, 'N') = 'Y';
468
469 if l_count < 1 then
470 raise_error('EAM_NETWORK_REC_NOT_FOUND');
471 end if;
472
473 exception when no_data_found then
474 raise_error('EAM_NETWORK_REC_NOT_FOUND');
475 end;
476
477
478
479 --validate start and end dates i.e. start date > end date
480 if p_start_date_active > nvl(p_end_date_active, p_start_date_active + 1)
481 then
482 fnd_message.set_name('EAM', 'EAM_IAA_INVALID_ACTIVE_DATE');
483 fnd_msg_pub.add;
484 RAISE FND_API.G_EXC_ERROR;
485 END IF;
486
487 validate_dff_segments(
488 p_ATTRIBUTE_CATEGORY ,
489 p_ATTRIBUTE1 ,
490 p_ATTRIBUTE2 ,
491 p_ATTRIBUTE3 ,
492 p_ATTRIBUTE4 ,
493 p_ATTRIBUTE5 ,
494 p_ATTRIBUTE6 ,
495 p_ATTRIBUTE7 ,
496 p_ATTRIBUTE8 ,
497 p_ATTRIBUTE9 ,
498 p_ATTRIBUTE10 ,
499 p_ATTRIBUTE11 ,
500 p_ATTRIBUTE12 ,
501 p_ATTRIBUTE13 ,
502 p_ATTRIBUTE14 ,
503 p_ATTRIBUTE15
504 );
505
506
507 --------------------------------------------------------------------------------------------
508 /* Anand- Validations for the item combinations supplied by the user */
509
510 l_maintenance_object_type := p_maintenance_object_type;
511 l_maintenance_object_id := p_maintenance_object_id;
512 l_asset_group_id := p_inventory_item_id;
513 l_serial_number := p_serial_number;
514
515 if (p_asset_number is not null and l_maintenance_object_id IS NULL ) THEN
516
517 begin
518 select instance_id into l_maintenance_object_id
519 from csi_item_instances
520 where instance_number = p_asset_number;
521
522 exception when no_data_found then
523 raise_error('EAM_NO_ITEM_FOUND');
524 end;
525
526 END IF;
527
528 IF (l_asset_group_id IS NOT NULL AND l_serial_number IS NOT NULL
529 AND l_maintenance_object_id IS NULL ) THEN
530
531 begin
532 select instance_id into l_maintenance_object_id
533 from csi_item_instances
534 where serial_number = l_serial_number
535 and inventory_item_id = l_asset_group_id;
536
537 exception when no_data_found then
538 raise_error('EAM_NO_ITEM_FOUND');
539 end;
540 END IF;
541
542 IF ((l_serial_number is null or l_asset_group_id is null ) and
543 (l_maintenance_object_id is not null and l_maintenance_object_type is not null)) THEN
544
545 begin
546 select serial_number, inventory_item_id into
547 l_serial_number, l_asset_group_id
548 from csi_item_instances
549 where instance_id = l_maintenance_object_id;
550
551 exception when no_data_found then
552 raise_error('EAM_NO_ITEM_FOUND');
553 end;
554
555 end if;
556
557
558 /* Check both the combinations are pointing to the same item / serial_number */
559
560 begin
561 select count(*) into l_count
562 from csi_item_instances cii
563 where cii.instance_id = l_maintenance_object_id
564 and cii.serial_number = l_serial_number
565 and cii.inventory_item_id = l_asset_group_id
566 and nvl(cii.network_asset_flag, 'N') = 'N';
567
568 if l_count < 1 then
569 raise_error ('EAM_ABO_INVALID_INV_ITEM_ID');
570 end if;
571
572 exception when no_data_found then
573 raise_error ('EAM_ABO_INVALID_INV_ITEM_ID');
574 END ;
575
576
577 --------------------------------------------------------------------------------------------
578 /* validate that the row does not already exist */
579
580 begin
581 select count(*) into l_count
582 from mtl_eam_network_assets mena
583 where mena.network_object_id = l_network_object_id
584 and mena.network_object_type = l_network_object_type
585 and mena.maintenance_object_id = l_maintenance_object_id
586 and mena.maintenance_object_type = l_maintenance_object_type;
587
588 if l_count > 0 then
589 fnd_message.set_name('EAM', 'EAM_NETWORK_REC_EXISTS');
590 fnd_msg_pub.add;
591 RAISE fnd_api.g_exc_error;
592 end if;
593
594 exception when no_data_found then
595 null;
596 END ;
597
598
599 select MTL_EAM_NETWORK_ASSETS_S.NEXTVAL into l_network_id from dual;
600
601
602 INSERT INTO MTL_EAM_NETWORK_ASSETS (
603 NETWORK_ASSOCIATION_ID ,
604 ORGANIZATION_ID ,
605 NETWORK_OBJECT_TYPE ,
606 NETWORK_OBJECT_ID ,
607 MAINTENANCE_OBJECT_TYPE ,
608 MAINTENANCE_OBJECT_ID ,
609 NETWORK_ITEM_ID ,
610 NETWORK_SERIAL_NUMBER ,
611 INVENTORY_ITEM_ID ,
612 SERIAL_NUMBER ,
613 START_DATE_ACTIVE ,
614 END_DATE_ACTIVE ,
615 ATTRIBUTE_CATEGORY ,
616 ATTRIBUTE1 ,
617 ATTRIBUTE2 ,
618 ATTRIBUTE3 ,
619 ATTRIBUTE4 ,
620 ATTRIBUTE5 ,
621 ATTRIBUTE6 ,
622 ATTRIBUTE7 ,
623 ATTRIBUTE8 ,
624 ATTRIBUTE9 ,
625 ATTRIBUTE10 ,
626 ATTRIBUTE11 ,
627 ATTRIBUTE12 ,
628 ATTRIBUTE13 ,
629 ATTRIBUTE14 ,
630 ATTRIBUTE15 ,
631 CREATED_BY ,
632 CREATION_DATE ,
633 LAST_UPDATE_LOGIN ,
634 LAST_UPDATE_DATE ,
635 LAST_UPDATED_BY
636 )VALUES
637 (
638 l_network_id ,
639 /*P_ORGANIZATION_ID ,*/
640 l_org_id,
641 l_NETWORK_OBJECT_TYPE ,
642 l_NETWORK_OBJECT_ID ,
643 /*P_MAINTENANCE_OBJECT_TYPE ,
644 P_MAINTENANCE_OBJECT_ID ,*/
645 l_maintenance_object_type,
646 l_maintenance_object_id ,
647 l_NETWORK_ITEM_ID ,
648 l_NETWORK_SERIAL_NUMBER ,
649 /*P_INVENTORY_ITEM_ID ,
650 P_SERIAL_NUMBER ,*/
651 l_asset_group_id,
652 l_serial_number ,
653 P_START_DATE_ACTIVE ,
654 P_END_DATE_ACTIVE ,
655 P_ATTRIBUTE_CATEGORY ,
656 P_ATTRIBUTE1 ,
657 P_ATTRIBUTE2 ,
658 P_ATTRIBUTE3 ,
659 P_ATTRIBUTE4 ,
660 P_ATTRIBUTE5 ,
661 P_ATTRIBUTE6 ,
662 P_ATTRIBUTE7 ,
663 P_ATTRIBUTE8 ,
664 P_ATTRIBUTE9 ,
665 P_ATTRIBUTE10 ,
666 P_ATTRIBUTE11 ,
667 P_ATTRIBUTE12 ,
668 P_ATTRIBUTE13 ,
669 P_ATTRIBUTE14 ,
670 P_ATTRIBUTE15 ,
671 fnd_global.user_id ,
672 sysdate ,
673 fnd_global.login_id ,
674 sysdate ,
675 fnd_global.user_id
676 );
677
678
679
680 -- End of API body.
681 -- Standard check of p_commit.
682 IF FND_API.To_Boolean( p_commit ) THEN
683 COMMIT WORK;
684 END IF;
685 -- Standard call to get message count and if count is 1, get message info.
686 FND_MSG_PUB.get
687 ( p_msg_index_out => x_msg_count ,
688 p_data => x_msg_data
689 );
690 EXCEPTION
691 WHEN FND_API.G_EXC_ERROR THEN
692 ROLLBACK TO insert_asset_routes;
693 x_return_status := FND_API.G_RET_STS_ERROR ;
694 FND_MSG_PUB.get
695 ( p_msg_index_out => x_msg_count ,
696 p_data => x_msg_data
697 );
698 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
699 ROLLBACK TO insert_asset_routes;
700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
701 FND_MSG_PUB.get
702 ( p_msg_index_out => x_msg_count ,
703 p_data => x_msg_data
704 );
705 WHEN OTHERS THEN
706 ROLLBACK TO insert_asset_routes;
707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
708 IF FND_MSG_PUB.Check_Msg_Level
709 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
710 THEN
711 FND_MSG_PUB.Add_Exc_Msg
712 ( G_PKG_NAME ,
713 l_api_name
714 );
715 END IF;
716 FND_MSG_PUB.get
717 ( p_msg_index_out => x_msg_count ,
718 p_data => x_msg_data
719 );
720 END insert_asset_routes;
721
722
723 PROCEDURE update_asset_routes
724 (
725 p_api_version IN NUMBER ,
726 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE ,
727 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
728 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
729 x_return_status OUT NOCOPY VARCHAR2 ,
730 x_msg_count OUT NOCOPY NUMBER ,
731 x_msg_data OUT NOCOPY VARCHAR2 ,
732
733 P_ORGANIZATION_ID IN NUMBER ,
734 P_START_DATE_ACTIVE IN DATE default null,
735 P_END_DATE_ACTIVE IN DATE default null,
736 P_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
737 P_ATTRIBUTE1 IN VARCHAR2 default null,
738 P_ATTRIBUTE2 IN VARCHAR2 default null,
739 P_ATTRIBUTE3 IN VARCHAR2 default null,
740 P_ATTRIBUTE4 IN VARCHAR2 default null,
741 P_ATTRIBUTE5 IN VARCHAR2 default null,
742 P_ATTRIBUTE6 IN VARCHAR2 default null,
743 P_ATTRIBUTE7 IN VARCHAR2 default null,
744 P_ATTRIBUTE8 IN VARCHAR2 default null,
745 P_ATTRIBUTE9 IN VARCHAR2 default null,
746 P_ATTRIBUTE10 IN VARCHAR2 default null,
747 P_ATTRIBUTE11 IN VARCHAR2 default null,
748 P_ATTRIBUTE12 IN VARCHAR2 default null,
749 P_ATTRIBUTE13 IN VARCHAR2 default null,
750 P_ATTRIBUTE14 IN VARCHAR2 default null,
751 P_ATTRIBUTE15 IN VARCHAR2 default null,
752 P_NETWORK_ITEM_ID IN NUMBER ,
753 P_NETWORK_SERIAL_NUMBER IN VARCHAR2 ,
754 P_INVENTORY_ITEM_ID IN NUMBER ,
755 P_SERIAL_NUMBER IN VARCHAR2 ,
756 P_NETWORK_ASSOCIATION_ID IN NUMBER ,
757 P_NETWORK_OBJECT_TYPE IN NUMBER default null ,
758 P_NETWORK_OBJECT_ID IN NUMBER default null ,
759 P_MAINTENANCE_OBJECT_TYPE IN NUMBER default null ,
760 P_MAINTENANCE_OBJECT_ID IN NUMBER default null ,
761 P_NETWORK_ASSET_NUMBER IN VARCHAR2 default null ,
762 P_ASSET_NUMBER IN VARCHAR2 default null
763 )
764 IS
765 l_api_name CONSTANT VARCHAR2(30) := 'APIname';
766 l_api_version CONSTANT NUMBER := 1.0;
767 l_boolean number;
768 l_return_status VARCHAR2(1);
769 l_msg_count NUMBER;
770 l_msg_data VARCHAR2(30);
771 l_dummy VARCHAR2(1);
772
773 l_object_found BOOLEAN;
774 l_network_object_type NUMBER;
775 l_network_object_id NUMBER;
776 l_maintenance_object_type NUMBER;
777 l_maintenance_object_id NUMBER;
778 l_creation_organization_id NUMBER;
779 l_asset_group_id NUMBER;
780 l_org_id NUMBER;
781 l_temp_org_id NUMBER;
782 l_asset_number VARCHAR2(100);
783 l_validated boolean;
784 l_network_item_id NUMBER;
785 l_network_serial_number VARCHAR2(100);
786 l_item_id NUMBER;
787 l_serial_number VARCHAR2(100);
788 l_count number;
789 BEGIN
790 -- Standard Start of API savepoint
791 SAVEPOINT update_asset_routes;
792 -- Standard call to check for call compatibility.
793 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
794 p_api_version ,
795 l_api_name ,
796 G_PKG_NAME )
797 THEN
798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
799 END IF;
800 -- Initialize message list if p_init_msg_list is set to TRUE.
801 IF FND_API.to_Boolean( p_init_msg_list ) THEN
802 FND_MSG_PUB.initialize;
803 END IF;
804 -- Initialize API return status to success
805 x_return_status := FND_API.G_RET_STS_SUCCESS;
806 -- API body
807
808 /* Anand- for creation_organization_id = organization_id */
809
810 l_org_id := P_ORGANIZATION_ID;
811 l_creation_organization_id := P_ORGANIZATION_ID;
812
813 --ver eam enabled
814 EAM_COMMON_UTILITIES_PVT.verify_org(
815 p_resp_id =>NULL,
816 p_resp_app_id => 401,
817 p_org_id => P_ORGANIZATION_ID,
818 x_boolean => l_boolean,
819 x_return_status => l_return_status,
820 x_msg_count => l_msg_count ,
821 x_msg_data => l_msg_data);
822
823 if l_boolean = 0
824 then
825 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_ORG_ID');
826 fnd_msg_pub.add;
827 RAISE fnd_api.g_exc_error;
828 end if;
829
830 -- Bug # 3441956
831 IF (((p_network_item_id is null or p_network_serial_number is null) and
832 (p_network_object_id is null or p_network_object_type is null) and
833 (p_network_asset_number is null)) OR
834 (p_network_object_type is not null and
835 ((p_network_object_type <> 1) AND (p_network_object_type <> 3)))) THEN -- change for bug # 13324075
836 fnd_message.set_name('EAM', 'EAM_NETWORK_REC_NOT_FOUND');
837 fnd_msg_pub.add;
838 RAISE FND_API.G_EXC_ERROR;
839 END IF;
840
841
842 IF (((p_maintenance_object_type is null or p_maintenance_object_id is null) and
843 (p_inventory_item_id is null or p_serial_number is null) and
844 (p_asset_number is null)) OR
845 (p_maintenance_object_type is not null and
846 ((p_network_object_type <> 1) AND (p_network_object_type <> 3)))) THEN -- change for bug # 13324075
847 fnd_message.set_name('EAM', 'EAM_EZWO_ASSET_BAD');
848 fnd_msg_pub.add;
849 RAISE FND_API.G_EXC_ERROR;
850 END IF;
851
852 l_network_object_type := p_network_object_type;
853 l_network_object_id := p_network_object_id;
854 l_network_item_id := p_network_item_id;
855 l_network_serial_number := p_network_serial_number;
856
857
858 if (p_network_asset_number is not null and l_network_object_id IS NULL ) THEN
859
860 begin
861 select instance_id into l_network_object_id
862 from csi_item_instances
863 where instance_number = p_network_asset_number;
864
865 exception when no_data_found then
866 raise_error('EAM_NETWORK_REC_NOT_FOUND');
867 end;
868
869 END IF;
870
871 IF (l_network_item_id IS NOT NULL AND l_network_serial_number IS NOT NULL
872 AND l_network_object_id IS NULL ) THEN
873
874 begin
875 select instance_id into l_network_object_id
876 from csi_item_instances
877 where serial_number = l_network_serial_number
878 and inventory_item_id = l_network_item_id;
879 exception when no_data_found then
880 raise_error('EAM_NETWORK_REC_NOT_FOUND');
881 end;
882 END IF;
883
884 IF ((l_network_serial_number is null or l_network_item_id is null ) and
885 (l_network_object_id is not null and l_network_object_type is not null)) THEN
886
887 begin
888 select serial_number, inventory_item_id into
889 l_network_serial_number, l_network_item_id
890 from csi_item_instances
891 where instance_id = l_network_object_id;
892
893 exception when no_data_found then
894 raise_error('EAM_NETWORK_REC_NOT_FOUND');
895 end;
896
897 end if;
898
899
900
901 --validate the network object Id exists
902 begin
903 select count(*) into l_count
904 from csi_item_instances cii
905 where cii.instance_id = l_network_object_id
906 and cii.serial_number = l_network_serial_number
907 and cii.inventory_item_id = l_network_item_id
908 and cii.network_asset_flag = 'Y';
909
910 if l_count < 1 then
911 raise_error('EAM_NETWORK_REC_NOT_FOUND');
912 end if;
913
914 exception when no_data_found then
915 raise_error('EAM_NETWORK_REC_NOT_FOUND');
916 end;
917
918
919 --validate start and end dates i.e. start date > end date
920 if p_start_date_active>p_end_date_active
921 then
922 fnd_message.set_name('EAM', 'EAM_IAA_INVALID_ACTIVE_DATE');
923 fnd_msg_pub.add;
924 RAISE FND_API.G_EXC_ERROR;
925 END IF;
926
927 validate_dff_segments(
928 p_ATTRIBUTE_CATEGORY ,
929 p_ATTRIBUTE1 ,
930 p_ATTRIBUTE2 ,
931 p_ATTRIBUTE3 ,
932 p_ATTRIBUTE4 ,
933 p_ATTRIBUTE5 ,
934 p_ATTRIBUTE6 ,
935 p_ATTRIBUTE7 ,
936 p_ATTRIBUTE8 ,
937 p_ATTRIBUTE9 ,
938 p_ATTRIBUTE10 ,
939 p_ATTRIBUTE11 ,
940 p_ATTRIBUTE12 ,
941 p_ATTRIBUTE13 ,
942 p_ATTRIBUTE14 ,
943 p_ATTRIBUTE15
944 );
945
946
947 --------------------------------------------------------------------------------------------
948 /* Anand- Validations for the item combinations supplied by the user */
949
950 l_maintenance_object_type := p_maintenance_object_type;
951 l_maintenance_object_id := p_maintenance_object_id;
952 l_asset_group_id := p_inventory_item_id;
953 l_serial_number := p_serial_number;
954
955 if (p_asset_number is not null and l_maintenance_object_id IS NULL ) THEN
956
957 begin
958 select instance_id into l_maintenance_object_id
959 from csi_item_instances
960 where instance_number = p_asset_number;
961
962 exception when no_data_found then
963 raise_error('EAM_NO_ITEM_FOUND');
964 end;
965
966 END IF;
967
968 IF (l_asset_group_id IS NOT NULL AND l_serial_number IS NOT NULL
969 AND l_maintenance_object_id IS NULL ) THEN
970
971 begin
972 select instance_id into l_maintenance_object_id
973 from csi_item_instances
974 where serial_number = l_serial_number
975 and inventory_item_id = l_asset_group_id;
976
977 exception when no_data_found then
978 raise_error('EAM_NO_ITEM_FOUND');
979 end;
980 END IF;
981
982 IF ((l_serial_number is null or l_asset_group_id is null ) and
983 (l_maintenance_object_id is not null and l_maintenance_object_type is not null)) THEN
984
985 begin
986 select serial_number, inventory_item_id into
987 l_serial_number, l_asset_group_id
988 from csi_item_instances
989 where instance_id = l_maintenance_object_id;
990
991 exception when no_data_found then
992 raise_error('EAM_NO_ITEM_FOUND');
993 end;
994
995 end if;
996
997
998 /* Check both the combinations are pointing to the same item / serial_number */
999
1000 begin
1001 select count(*) into l_count
1002 from csi_item_instances cii
1003 where cii.instance_id = l_maintenance_object_id
1004 and cii.serial_number = l_serial_number
1005 and cii.inventory_item_id = l_asset_group_id
1006 and nvl(cii.network_asset_flag,'N') = 'N'; -- added nvl check for bug 15850452
1007
1008 if l_count < 1 then
1009 raise_error ('EAM_ABO_INVALID_INV_ITEM_ID');
1010 end if;
1011
1012 exception when no_data_found then
1013 raise_error ('EAM_ABO_INVALID_INV_ITEM_ID');
1014 END;
1015
1016
1017 --------------------------------------------------------------------------------------------
1018 /* validate that the row already exists in MTL_EAM_NETWORK_ASSETS table */
1019
1020 begin
1021 select count(*) into l_count
1022 from mtl_eam_network_assets mena
1023 where mena.network_object_id = l_network_object_id
1024 and mena.network_object_type = l_network_object_type
1025 and mena.maintenance_object_id = l_maintenance_object_id
1026 and mena.maintenance_object_type = l_maintenance_object_type
1027 and mena.network_association_id = p_network_association_id;
1028
1029 if l_count < 1 then
1030 fnd_message.set_name('EAM', 'EAM_NETWORK_REC_NOT_FOUND');
1031 fnd_msg_pub.add;
1032 RAISE fnd_api.g_exc_error;
1033 end if;
1034
1035 exception when no_data_found then
1036 fnd_message.set_name('EAM', 'EAM_NETWORK_REC_NOT_FOUND');
1037 fnd_msg_pub.add;
1038 RAISE fnd_api.g_exc_error;
1039 END;
1040
1041 UPDATE MTL_EAM_NETWORK_ASSETS
1042 SET
1043 START_DATE_ACTIVE = P_START_DATE_ACTIVE ,
1044 END_DATE_ACTIVE = P_END_DATE_ACTIVE ,
1045 ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY ,
1046 ATTRIBUTE1 = P_ATTRIBUTE1 ,
1047 ATTRIBUTE2 = P_ATTRIBUTE2 ,
1048 ATTRIBUTE3 = P_ATTRIBUTE3 ,
1049 ATTRIBUTE4 = P_ATTRIBUTE4 ,
1050 ATTRIBUTE5 = P_ATTRIBUTE5 ,
1051 ATTRIBUTE6 = P_ATTRIBUTE6 ,
1052 ATTRIBUTE7 = P_ATTRIBUTE7 ,
1053 ATTRIBUTE8 = P_ATTRIBUTE8 ,
1054 ATTRIBUTE9 = P_ATTRIBUTE9 ,
1055 ATTRIBUTE10 = P_ATTRIBUTE10 ,
1056 ATTRIBUTE11 = P_ATTRIBUTE11 ,
1057 ATTRIBUTE12 = P_ATTRIBUTE12 ,
1058 ATTRIBUTE13 = P_ATTRIBUTE13 ,
1059 ATTRIBUTE14 = P_ATTRIBUTE14 ,
1060 ATTRIBUTE15 = P_ATTRIBUTE15 ,
1061
1062 LAST_UPDATE_LOGIN = fnd_global.login_id ,
1063 LAST_UPDATE_DATE = sysdate ,
1064 LAST_UPDATED_BY = fnd_global.user_id
1065
1066 WHERE NETWORK_ASSOCIATION_ID = P_NETWORK_ASSOCIATION_ID;
1067
1068
1069 -- End of API body.
1070 -- Standard check of p_commit.
1071 IF FND_API.To_Boolean( p_commit ) THEN
1072 COMMIT WORK;
1073 END IF;
1074 -- Standard call to get message count and if count is 1, get message info.
1075 FND_MSG_PUB.get
1076 ( p_msg_index_out => x_msg_count ,
1077 p_data => x_msg_data
1078 );
1079 EXCEPTION
1080 WHEN FND_API.G_EXC_ERROR THEN
1081 ROLLBACK TO update_asset_routes;
1082 x_return_status := FND_API.G_RET_STS_ERROR ;
1083 FND_MSG_PUB.get
1084 ( p_msg_index_out => x_msg_count ,
1085 p_data => x_msg_data
1086 );
1087 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1088 ROLLBACK TO update_asset_routes;
1089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1090 FND_MSG_PUB.get
1091 ( p_msg_index_out => x_msg_count ,
1092 p_data => x_msg_data
1093 );
1094 WHEN OTHERS THEN
1095 ROLLBACK TO update_asset_routes;
1096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1097 IF FND_MSG_PUB.Check_Msg_Level
1098 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1099 THEN
1100 FND_MSG_PUB.Add_Exc_Msg
1101 ( G_PKG_NAME ,
1102 l_api_name
1103 );
1104 END IF;
1105 FND_MSG_PUB.get
1106 ( p_msg_index_out => x_msg_count ,
1107 p_data => x_msg_data
1108 );
1109 END update_asset_routes;
1110
1111
1112 END EAM_ASSET_ROUTES_PUB;