[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_LOG_PVT
Source
1 PACKAGE BODY EAM_ASSET_LOG_PVT AS
2 /* $Header: EAMVALGB.pls 120.31 2012/01/27 10:52:00 vchidura ship $ */
3
4 g_pkg_name CONSTANT varchar2(30) := 'EAM_ASSET_LOG_PVT';
5
6 PROCEDURE insert_row(
7 p_log_id IN number := NULL,
8 p_api_version IN number := 1.0,
9 p_init_msg_list IN varchar2 := fnd_api.g_false,
10 p_commit IN varchar2 := fnd_api.g_false,
11 p_validation_level IN number := fnd_api.g_valid_level_full,
12 p_event_date IN date := sysdate,
13 p_event_type IN varchar2 := NULL,
14 p_event_id IN number := NULL,
15 p_organization_id IN number := NULL,
16 p_instance_id IN number,
17 p_comments IN varchar2 := NULL,
18 p_reference IN varchar2 := NULL,
19 p_ref_id IN number := NULL,
20 p_operable_flag IN number := NULL,
21 p_reason_code IN number := NULL,
22 p_resource_id IN number := NULL,
23 p_equipment_gen_object_id IN number := NULL,
24 p_source_log_id IN number := NULL,
25 p_instance_number IN varchar2 := NULL,
26 p_downcode IN number := NULL,
27 p_expected_up_date IN date := NULL,
28 p_employee_id IN number := NULL,
29 p_department_id IN number := NULL,
30 p_attribute_category IN varchar2 := NULL,
31 p_attribute1 IN varchar2 := NULL,
32 p_attribute2 IN varchar2 := NULL,
33 p_attribute3 IN varchar2 := NULL,
34 p_attribute4 IN varchar2 := NULL,
35 p_attribute5 IN varchar2 := NULL,
36 p_attribute6 IN varchar2 := NULL,
37 p_attribute7 IN varchar2 := NULL,
38 p_attribute8 IN varchar2 := NULL,
39 p_attribute9 IN varchar2 := NULL,
40 p_attribute10 IN varchar2 := NULL,
41 p_attribute11 IN varchar2 := NULL,
42 p_attribute12 IN varchar2 := NULL,
43 p_attribute13 IN varchar2 := NULL,
44 p_attribute14 IN varchar2 := NULL,
45 p_attribute15 IN varchar2 := NULL,
46 x_return_status OUT NOCOPY varchar2,
47 x_msg_count OUT NOCOPY number,
48 x_msg_data OUT NOCOPY varchar2)
49
50
51 IS
52 l_api_name CONSTANT varchar2(30) := 'insert_row';
53 l_api_version CONSTANT number := 1.0;
54 l_full_name CONSTANT varchar2(60) := g_pkg_name || '.' || l_api_name;
55 l_instance_id number;
56 l_instance_number varchar2(30);
57 l_log_id number;
58 l_organization_id number;
59 l_status varchar2(1);
60 l_var2 number;
61
62 BEGIN
63 -- Standard Start of API savepoint
64 SAVEPOINT EAM_ASSET_LOG_PVT_SV;
65
66 -- Standard call to check for call compatibility.
67 IF NOT fnd_api.compatible_api_call(
68 l_api_version
69 ,p_api_version
70 ,l_api_name
71 ,g_pkg_name) THEN
72 RAISE fnd_api.g_exc_unexpected_error;
73 END IF;
74
75 IF fnd_api.to_boolean(p_init_msg_list) THEN
76 fnd_msg_pub.initialize;
77 END IF;
78
79 x_return_status := fnd_api.g_ret_sts_success;
80
81 -- VALIDATION Loggable Asset for System Events
82 IF p_event_type='EAM_SYSTEM_EVENTS' THEN
83 BEGIN
84 SELECT mp.maint_organization_id, cii.operational_log_flag
85 INTO l_organization_id, l_status
86 FROM mtl_parameters mp, csi_item_instances cii
87 WHERE mp.organization_id = cii.last_vld_organization_id
88 AND cii.instance_id = p_instance_id;
89
90 IF (l_status = 'N' or l_status is NULL) THEN
91 RETURN;
92 END IF;
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95 fnd_message.set_name
96 ( application => 'EAM'
97 , name => 'EAM_INSTANCE_ID_INVALID'
98 );
99
100 fnd_msg_pub.add;
101 x_return_status:= fnd_api.g_ret_sts_error;
102 fnd_msg_pub.Count_And_Get
103 ( p_count => x_msg_count,
104 p_data => x_msg_data
105 );
106 RETURN;
107 END;
108
109 BEGIN
110 SELECT event_id INTO l_var2 FROM eam_control_event
111 WHERE event_type = p_event_type
112 AND event_id= p_event_id
113 AND organization_id=l_organization_id;
114
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 RETURN;
118 END;
119
120 END IF;
121
122 EAM_ASSET_LOG_PVT.validate_event(
123 p_event_date => p_event_date,
124 p_event_type => p_event_type,
125 p_event_id => p_event_id,
126 p_instance_id => p_instance_id,
127 p_instance_number => p_instance_number,
128 p_operable_flag => p_operable_flag,
129 p_reason_code => p_reason_code,
130 p_resource_id => p_resource_id,
131 p_downcode => p_downcode,
132 p_expected_up_date => p_expected_up_date,
133 p_equipment_gen_object_id => p_equipment_gen_object_id,
134 x_return_status => x_return_status,
135 x_msg_count => x_msg_count,
136 x_msg_data => x_msg_data);
137
138 IF x_return_status = fnd_api.g_ret_sts_success THEN
139
140 IF (p_log_id IS NULL) THEN
141 SELECT eam_asset_log_s.nextval INTO l_log_id FROM dual;
142 ELSE
143 l_log_id:=p_log_id;
144 END IF;
145
146 INSERT
147 INTO eam_asset_log
148 (
149 log_id,
150 event_date,
151 event_type,
152 event_id,
153 organization_id,
154 instance_id,
155 reference,
156 ref_id,
157 operable,
158 reason_code,
159 resource_id,
160 comments,
161 down_code,
162 resource_serial_number,
163 expected_up_date,
164 source_log_id,
165 employee_id,
166 department_id,
167 equipment_gen_object_id,
168 attribute_category,
169 attribute1,
170 attribute2,
171 attribute3,
172 attribute4,
173 attribute5,
174 attribute6,
175 attribute7,
176 attribute8,
177 attribute9,
178 attribute10,
179 attribute11,
180 attribute12,
181 attribute13,
182 attribute14,
183 attribute15,
184 created_by,
185 creation_date,
186 last_updated_by,
187 last_update_date,
188 last_update_login
189 )
190 VALUES
191 (
192 l_log_id,
193 p_event_date,
194 p_event_type,
195 p_event_id,
196 p_organization_id,
197 p_instance_id,
198 p_reference,
199 p_ref_id,
200 p_operable_flag,
201 p_reason_code,
202 p_resource_id,
203 p_comments,
204 p_downcode,
205 p_instance_number,
206 p_expected_up_date,
207 p_source_log_id,
208 p_employee_id,
209 p_department_id,
210 p_equipment_gen_object_id,
211 p_attribute_category,
212 p_attribute1,
213 p_attribute2,
214 p_attribute3,
215 p_attribute4,
216 p_attribute5,
217 p_attribute6,
218 p_attribute7,
219 p_attribute8,
220 p_attribute9,
221 p_attribute10,
222 p_attribute11,
223 p_attribute12,
224 p_attribute13,
225 p_attribute14,
226 p_attribute15,
227 fnd_global.user_id,
228 sysdate,
229 fnd_global.user_id,
230 sysdate,
231 fnd_global.login_id
232 )
233 ;
234
235 END IF;
236
237 -- End of API body.
238 -- Standard check of p_commit.
239 IF fnd_api.to_boolean(p_commit) THEN
240 COMMIT WORK;
241 END IF;
242 fnd_msg_pub.count_and_get(
243 p_count => x_msg_count
244 ,p_data => x_msg_data);
245 EXCEPTION
246 WHEN fnd_api.g_exc_error THEN
247 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
248 x_return_status := fnd_api.g_ret_sts_error;
249 fnd_msg_pub.count_and_get(
250 p_count => x_msg_count
251 ,p_data => x_msg_data);
252 WHEN fnd_api.g_exc_unexpected_error THEN
253 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
254 x_return_status := fnd_api.g_ret_sts_unexp_error;
255 fnd_msg_pub.count_and_get(
256 p_count => x_msg_count
257 ,p_data => x_msg_data);
258 WHEN OTHERS THEN
259 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
260 x_return_status := fnd_api.g_ret_sts_unexp_error;
261
262 IF fnd_msg_pub.check_msg_level(
263 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
264 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
265 END IF;
266
267 fnd_msg_pub.count_and_get(
268 p_count => x_msg_count,
269 p_data => x_msg_data);
270
271 END insert_row;
272
273 PROCEDURE validate_event(
274 p_api_version IN number := 1.0,
275 p_init_msg_list IN varchar2 := fnd_api.g_false,
276 p_commit IN varchar2 := fnd_api.g_false,
277 p_validation_level IN number := fnd_api.g_valid_level_full,
278 p_event_date IN date := sysdate,
279 p_event_type IN varchar2 := NULL,
280 p_event_id IN number := NULL,
281 p_instance_id IN number := NULL,
282 p_instance_number IN varchar2 := NULL,
283 p_operable_flag IN number := NULL,
284 p_reason_code IN number := NULL,
285 p_resource_id IN number := NULL,
286 p_equipment_gen_object_id IN number := NULL,
287 p_downcode IN number := NULL,
288 p_expected_up_date IN date := NULL,
289 x_return_status OUT NOCOPY varchar2,
290 x_msg_count OUT NOCOPY number,
291 x_msg_data OUT NOCOPY varchar2)
292 IS
293 l_api_name CONSTANT varchar2(30) := 'validate_event';
294 l_api_version CONSTANT number := 1.0;
295 l_full_name CONSTANT varchar2(60) := g_pkg_name || '.' || l_api_name;
296 l_status number;
297 l_organization_id number;
298 l_dummy number;
299
300 BEGIN
301 -- Standard Start of API savepoint
302 SAVEPOINT EAM_ASSET_LOG_PVT_SV;
303
304 -- Standard call to check for call compatibility.
305 IF NOT fnd_api.compatible_api_call(
306 l_api_version
307 ,p_api_version
308 ,l_api_name
309 ,g_pkg_name) THEN
310 RAISE fnd_api.g_exc_unexpected_error;
311 END IF;
312
313 IF fnd_api.to_boolean(p_init_msg_list) THEN
314 fnd_msg_pub.initialize;
315 END IF;
316
317 x_return_status := fnd_api.g_ret_sts_success;
318
319 -- VALIDATION 1 Event Type
320
321 IF (p_event_type IS NULL OR p_event_type NOT IN ('EAM_USER_EVENTS','EAM_OPERATIONAL_EVENTS'
322 , 'EAM_SYSTEM_EVENTS')) THEN
323 fnd_message.set_name
324 ( application => 'EAM'
325 , name => 'EAM_EVENT_TYPE_INVALID'
326 );
327 fnd_msg_pub.add;
328 x_return_status:= fnd_api.g_ret_sts_error;
329 fnd_msg_pub.Count_And_Get
330 ( p_count => x_msg_count,
331 p_data => x_msg_data
332 );
333
334 RETURN;
335 END IF;
336
337 -- VALIDATION 2 Event Date
338 IF (p_event_type <> 'EAM_SYSTEM_EVENTS') THEN
339 IF (p_event_date IS NULL OR p_event_date > sysdate) THEN
340 fnd_message.set_name
341 ( application => 'EAM'
342 , name => 'EAM_EVENT_DATE_INVALID'
343 );
344
345 fnd_msg_pub.add;
346 x_return_status:= fnd_api.g_ret_sts_error;
347 fnd_msg_pub.Count_And_Get
348 ( p_count => x_msg_count,
349 p_data => x_msg_data
350 );
351 return;
352 END IF;
353 END IF;
354
355 -- VALIDATION 3 Event Id
356 BEGIN
357
358 SELECT lookup_code INTO l_status FROM mfg_lookups
359 WHERE lookup_type = p_event_type AND
360 lookup_code = p_event_id AND
361 enabled_flag = 'Y' AND
362 p_event_date >= NVL(start_date_active, p_event_date) AND
363 p_event_date <= NVL(end_date_active,sysdate);
364
365 EXCEPTION
366 WHEN NO_DATA_FOUND THEN
367 fnd_message.set_name
368 ( application => 'EAM'
369 , name => 'EAM_EVENT_ID_INVALID'
370 );
371
372 fnd_msg_pub.add;
373 x_return_status:= fnd_api.g_ret_sts_error;
374 fnd_msg_pub.Count_And_Get
375 ( p_count => x_msg_count,
376 p_data => x_msg_data
377 );
378 RETURN;
379 END;
380
381 -- VALIDATION 4 Instance Id
382
383 IF p_instance_id IS NOT NULL THEN
384 BEGIN
385 SELECT instance_id INTO l_status FROM csi_item_instances
386 WHERE instance_id = p_instance_id;
387
388 EXCEPTION
389 WHEN NO_DATA_FOUND THEN
390 fnd_message.set_name
391 ( application => 'EAM'
392 , name => 'EAM_INSTANCE_ID_INVALID'
393 );
394
395 fnd_msg_pub.add;
396 x_return_status:= fnd_api.g_ret_sts_error;
397 fnd_msg_pub.Count_And_Get
398 ( p_count => x_msg_count,
399 p_data => x_msg_data
400 );
401 RETURN;
402 END;
403
404 END IF;
405
406 -- VALIDATION 5 Operable Flag
407
408 IF p_operable_flag IS NOT NULL THEN
409 BEGIN
410 SELECT lookup_code INTO l_status FROM mfg_lookups
411 WHERE lookup_type = 'SYS_YES_NO' AND
412 enabled_flag = 'Y' AND
413 p_event_date >= NVL(start_date_active, p_event_date) AND
414 p_event_date <= NVL(end_date_active,sysdate) AND
415 lookup_code = p_operable_flag;
416
417 EXCEPTION
418 WHEN NO_DATA_FOUND THEN
419 fnd_message.set_name
420 ( application => 'EAM'
421 , name => 'EAM_OPERABLE_INVALID'
422 );
423
424 fnd_msg_pub.add;
425 x_return_status:= fnd_api.g_ret_sts_error;
426 fnd_msg_pub.Count_And_Get
427 ( p_count => x_msg_count,
428 p_data => x_msg_data
429 );
430 RETURN;
431 END;
432 END IF;
433
434 -- VALIDATION 6 Reason Code
435
436 IF p_reason_code IS NOT NULL THEN
437 BEGIN
438 SELECT lookup_code INTO l_status FROM mfg_lookups
439 WHERE lookup_type = 'EAM_LOG_REASON_CODE' AND
440 enabled_flag = 'Y' AND
441 p_event_date >= NVL(start_date_active,p_event_date) AND
442 p_event_date <= NVL(end_date_active,sysdate) AND
443 lookup_code = p_reason_code;
444
445 EXCEPTION
446 WHEN NO_DATA_FOUND THEN
447 fnd_message.set_name
448 ( application => 'EAM'
449 , name => 'EAM_RET_MAT_INVALID_REASON'
450 );
451
452 fnd_msg_pub.add;
453 x_return_status:= fnd_api.g_ret_sts_error;
454 fnd_msg_pub.Count_And_Get
455 ( p_count => x_msg_count,
456 p_data => x_msg_data
457 );
458 RETURN;
459 END;
460 END IF;
461
462 -- VALIDATION 7 Operational Event Lookups
463
464 IF p_event_type='EAM_OPERATIONAL_EVENTS' THEN
465
466 -- VALIDATION for resourceid
467
468 IF p_resource_id is not null THEN
469
470 BEGIN
471 SELECT resource_id INTO l_status FROM bom_resources_v
472 WHERE resource_id = p_resource_id;
473 EXCEPTION
474 WHEN NO_DATA_FOUND THEN
475 fnd_message.set_name
476 ( application => 'EAM'
477 , name => 'EAM_RI_INSTANCE_INVALID'
478 );
479 fnd_msg_pub.add;
480 x_return_status:= fnd_api.g_ret_sts_error;
481 fnd_msg_pub.Count_And_Get
482 ( p_count => x_msg_count,
483 p_data => x_msg_data
484 );
485 RETURN;
486 END;
487
488 END IF;
489 /* Relaxing this validation to avoid patch failure due to dependancy #4522204
490 IF p_downcode IS NOT NULL THEN
491
492 BEGIN
493 SELECT downcode INTO l_status FROM bom_resource_downcodes
494 WHERE downcode = p_downcode AND
495 resource_id = p_resource_id ;
496 EXCEPTION
497 WHEN NO_DATA_FOUND THEN
498 fnd_message.set_name
499 ( application => 'EAM'
500 , name => 'EAM_DOWNCODE_INVALID'
501 );
502
503 fnd_msg_pub.add;
504 x_return_status:= fnd_api.g_ret_sts_error;
505 fnd_msg_pub.Count_And_Get
506 ( p_count => x_msg_count,
507 p_data => x_msg_data
508 );
509 RETURN;
510 END;
511
512 END IF;
513 */
514 END IF;
515
516 EXCEPTION
517 WHEN fnd_api.g_exc_error THEN
518 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
519 x_return_status := fnd_api.g_ret_sts_error;
520 fnd_msg_pub.count_and_get(
521 p_count => x_msg_count
522 ,p_data => x_msg_data);
523 WHEN fnd_api.g_exc_unexpected_error THEN
524 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
525 x_return_status := fnd_api.g_ret_sts_unexp_error;
526 fnd_msg_pub.count_and_get(
527 p_count => x_msg_count
528 ,p_data => x_msg_data);
529 WHEN OTHERS THEN
530 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
531 x_return_status := fnd_api.g_ret_sts_unexp_error;
532 IF fnd_msg_pub.check_msg_level(
533 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
534 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
535 END IF;
536
537 fnd_msg_pub.count_and_get(
538 p_count => x_msg_count,
539 p_data => x_msg_data);
540
541 END validate_event;
542
543 -- Procedure to Purge Log Transactions
544 PROCEDURE delete_row(
545 errbuf OUT NOCOPY varchar2,
546 retcode OUT NOCOPY number,
547 p_start_date IN varchar2,
548 p_end_date IN varchar2,
549 p_asset_group IN number,
550 p_instance_id IN number,
551 p_event_type IN varchar2,
552 p_event_id IN number,
553 p_resource_id IN number,
554 p_organization_id IN number,
555 p_equipment_gen_object_id IN number
556 )
557 IS
558 l_statement varchar2(2000);
559 l_start_date date := fnd_date.canonical_to_date(p_start_date);
560 l_end_date date := fnd_date.canonical_to_date(p_end_date);
561 l_organization_id number;
562 BEGIN
563 retcode:=0;
564
565 -- Standard Start of API savepoint
566 SAVEPOINT EAM_ASSET_LOG_PVT_SV;
567
568 begin
569 select nvl(maint_organization_id, p_organization_id) into l_organization_id
570 from mtl_parameters
571 where organization_id = p_organization_id;
572 exception
573 WHEN NO_DATA_FOUND THEN
574
575 RETURN;
576 end;
577
578 l_statement := 'DELETE FROM eam_asset_log eal WHERE eal.organization_id = :1';
579
580 IF p_start_date is not null then
581 l_statement := l_statement || ' AND eal.event_date >= '||''''||l_start_date||'''' ;
582 END IF;
583
584 IF p_end_date is not null then
585 l_statement := l_statement || ' AND eal.event_date <= '||''''||l_end_date||'''' ;
586 END IF;
587
588 IF p_asset_group is not null then
589
590 if p_instance_id is not null then
591 l_statement := l_statement || ' AND instance_id = '||p_instance_id;
592 else
593 l_statement := l_statement || ' AND EXISTS (SELECT cii.instance_id FROM csi_item_instances cii WHERE cii.inventory_item_id = '||p_asset_group||' AND cii.instance_id = eal.instance_id)';
594 end if;
595 else
596 if p_instance_id is not null then
597 l_statement := l_statement || ' AND instance_id = '||p_instance_id ;
598 end if;
599 end if;
600
601 IF p_event_type is not null then
602 l_statement := l_statement || ' AND event_type = '||''''||p_event_type||'''' ;
603 END IF;
604
605 IF p_event_id is not null then
606 l_statement := l_statement || ' AND event_id = '||p_event_id;
607 END IF;
608
609 IF p_resource_id is not null then
610 l_statement := l_statement || ' AND eal.resource_id = '||p_resource_id ;
611 end if;
612
613 IF p_equipment_gen_object_id is not null then
614 l_statement := l_statement || ' AND eal.equipment_gen_object_id = '||p_equipment_gen_object_id ;
615 end if;
616
617 EXECUTE IMMEDIATE l_statement USING l_organization_id;
618
619 COMMIT;
620
621 EXCEPTION
622 WHEN FND_API.G_EXC_ERROR THEN
623 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
624 fnd_message.set_name('EAM','EAM_PURGE_EVENT_LOG_FAILURE');
625 errbuf := fnd_message.get();
626 retcode := 2;
627 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
628 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
629 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
630 fnd_message.set_name('EAM','EAM_PURGE_EVENT_LOG_FAILURE');
631 errbuf := fnd_message.get();
632 retcode := 2;
633 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
634 WHEN OTHERS THEN
635 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
636 retcode := 2;
637 fnd_file.put_line(FND_FILE.LOG, SQLERRM);
638
639 END delete_row;
640
641 -- Procedure to Insert Log for Inventory Transactions
642 PROCEDURE instance_update_event(
643 p_api_version IN number := 1.0,
644 p_init_msg_list IN varchar2 := fnd_api.g_false,
645 p_commit IN varchar2 := fnd_api.g_false,
646 p_validation_level IN number := fnd_api.g_valid_level_full,
647 p_event_date IN date,
648 p_event_type IN varchar2 := 'EAM_SYSTEM_EVENTS',
649 p_event_id IN number := NULL,
650 p_instance_id IN number,
651 p_ref_id IN number,
652 p_organization_id IN number := NULL,
653 x_return_status OUT NOCOPY varchar2,
654 x_msg_count OUT NOCOPY number,
655 x_msg_data OUT NOCOPY varchar2)
656 IS
657 l_api_name CONSTANT varchar2(30) :='instance_update_event';
658 l_api_version CONSTANT number := 1.0;
659 l_association_id number;
660 l_validated boolean;
661 l_exists boolean;
662 l_instance_number varchar2(30);
663 l_organization_id number;
664 l_reference varchar2(80);
665 l_status number := NULL;
666 l_status1 number := NULL;
667 l_status2 number := NULL;
668 l_event_id number;
669 l_date date;
670 l_old_loc_name varchar2(240);
671 l_new_loc_name varchar2(240);
672 l_old_loc_name_sub varchar2(40);
673 l_new_loc_name_sub varchar2(40);
674 l_location_type_code varchar2(30);
675
676 BEGIN
677
678 /* Standard Start of API savepoint */
679 SAVEPOINT EAM_ASSET_LOG_PVT;
680
681 /* Standard call to check for call compatibility. */
682 IF NOT FND_API.Compatible_API_Call
683 ( l_api_version ,
684 p_api_version ,
685 l_api_name ,
686 G_PKG_NAME
687 )
688 THEN
689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
690 END IF;
691
692 /* Initialize message list if p_init_msg_list is set to TRUE. */
693 IF FND_API.to_Boolean( p_init_msg_list ) THEN
694 fnd_msg_pub.initialize;
695 END IF;
696
697 /* Initialize API return status to success */
698 x_return_status := FND_API.G_RET_STS_SUCCESS;
699
700 BEGIN
701 SELECT mp.maint_organization_id INTO l_organization_id
702 FROM mtl_parameters mp, csi_item_instances cii
703 WHERE mp.organization_id = cii.last_vld_organization_id
704 AND cii.instance_id = p_instance_id;
705
706 EXCEPTION
707 WHEN NO_DATA_FOUND THEN
708 fnd_message.set_name
709 ( application => 'EAM'
710 , name => 'EAM_INSTANCE_ID_INVALID'
711 );
712
713 fnd_msg_pub.add;
714 x_return_status:= fnd_api.g_ret_sts_error;
715 fnd_msg_pub.Count_And_Get
716 ( p_count => x_msg_count,
717 p_data => x_msg_data
718 );
719 null;
720 END;
721
722 /* VALIDATION 1 Instanceid*/
723 l_status := null;
724 IF p_event_type='EAM_SYSTEM_EVENTS' THEN
725 BEGIN
726 SELECT instance_history_id INTO l_status
727 FROM csi_item_instances_H
728 WHERE instance_history_id = p_ref_id
729 AND (nvl(old_location_id,1) <> nvl(new_location_id,1));
730
731 EXCEPTION
732 WHEN NO_DATA_FOUND THEN
733 null;
734 END;
735
736 BEGIN
737 SELECT instance_history_id INTO l_status1
738 FROM csi_item_instances_h
739 WHERE instance_history_id = p_ref_id AND
740 old_active_end_date IS NULL and
741 new_active_end_date IS NOT NULL;
742 EXCEPTION
743 WHEN NO_DATA_FOUND THEN
744 null;
745 END;
746
747 BEGIN
748 SELECT instance_history_id INTO l_status2
749 FROM csi_item_instances_h
750 WHERE instance_history_id = p_ref_id AND
751 old_active_end_date is not null
752 AND new_active_end_date IS NULL;
753 EXCEPTION
754 WHEN NO_DATA_FOUND THEN
755 null;
756 END;
757
758 IF l_status IS NOT NULL THEN
759 l_event_id := 13 ;
760
761 /*SELECT old_location_id||'->'||new_location_id INTO l_reference
762 FROM csi_item_instances_H
763 WHERE instance_history_id = p_ref_id; */ --commented for bug 12894933
764
765 --start for bug 12894933
766
767 select location_type_code into l_location_type_code
768 from csi_item_instances
769 where instance_id=p_instance_id;
770
771
772 --cpaturing the old Location Name
773
774 select Decode(nvl(ciih.OLD_LOCATION_TYPE_CODE,l_location_type_code) , 'HZ_PARTY_SITES', (SELECT hz.address1
775 ||','
776 ||hz.address2
777 ||','
778 ||hz.address3
779 ||','
780 ||hz.address4
781 ||','
782 ||hz.city
783 ||','
784 ||hz.state
785 ||','
786
787 ||hz.postal_code
788 ||','
789 ||hz.country
790 FROM
791 hz_locations hz,
792 hz_party_sites hps
793 WHERE
794 hz.location_id = hps.location_id
795 AND hps.party_site_id = ciih.old_location_id),
796 'HZ_LOCATIONS', (SELECT hz.address1
797 ||','
798 ||hz.address2
799 ||','
800 ||hz.address3
801 ||','
802 ||hz.address4
803 ||','
804 ||hz.city
805 ||','
806 ||hz.state
807 ||','
808
809 ||hz.postal_code
810 ||','
811 ||hz.country
812 FROM hz_locations hz
813 WHERE
814 hz.location_id = ciih.old_location_id),
815 'VENDOR_SITE', (SELECT
816 pvs.address_line1
817 ||','
818 ||pvs.address_line2
819 ||','
820 ||pvs.address_line3
821 ||','
822 ||pvs.city
823 ||','
824 ||pvs.state
825 ||','
826 ||pvs.zip
827 ||','
828 ||pvs.country
829 FROM po_vendor_sites_all pvs
830 WHERE pvs.vendor_site_id = ciih.old_location_id),
831 'INVENTORY', (SELECT hrl.address_line_1
832 ||','
833 ||hrl.address_line_2
834 ||','
835 ||hrl.address_line_3
836 ||','
837 ||hrl.region_1
838 ||','
839 ||hrl.postal_code
840 ||','
841 ||hrl.country
842 FROM hr_locations_all hrl
843 WHERE hrl.location_id = ciih.old_location_id),
844 'INTERNAL_SITE', (SELECT hrl.address_line_1
845 ||','
846 ||hrl.address_line_2
847 ||','
848 ||hrl.address_line_3
849 ||','
850 ||hrl.region_1
851 ||','
852 ||hrl.postal_code
853 ||','
854 ||hrl.country
855 FROM hr_locations_all hrl
856 WHERE hrl.location_id = ciih.old_location_id),
857 'WIP', (SELECT hz.address1
858 ||','
859 ||hz.address2
860 ||','
861 ||hz.address3
862 ||','
863 ||hz.address4
864 ||','
865 ||hz.city
866 ||','
867 ||hz.state
868 ||','
869 ||hz.postal_code
870 ||','
871 ||hz.country
872 FROM hz_locations hz
873 WHERE hz.location_id = ciih.old_location_id),
874 'PROJECT', (SELECT hz.address1
875 ||','
876 ||hz.address2
877 ||','
878 ||hz.address3
879 ||','
880 ||hz.address4
881 ||','
882 ||hz.city
883 ||','
884 ||hz.state
885 ||','
886 ||hz.postal_code
887 ||','
888 ||hz.country
889 FROM hz_locations hz
890 WHERE hz.location_id = ciih.old_location_id),
891 'IN_TRANSIT', (SELECT hz.address1
892 ||','
893 ||hz.address2
894 ||','
895 ||hz.address3
896 ||','
897 ||hz.address4
898 ||','
899 ||hz.city
900 ||','
901 ||hz.state
902 ||','
903 ||hz.postal_code
904 ||','
905 ||hz.country
906 FROM hz_locations hz
907 WHERE hz.location_id = ciih.old_location_id),
908 'PO', (SELECT hz.address1
909 ||','
910 ||hz.address2
911 ||','
912 ||hz.address3
913 ||','
914 ||hz.address4
915 ||','
916 ||hz.city
917 ||','
918 ||hz.state
919 ||','
920 ||hz.postal_code
921 ||','
922 ||hz.country
923 FROM hz_locations hz
924 WHERE
925 hz.location_id = ciih.old_location_id),
926 NULL) INTO l_old_loc_name
927 FROM csi_item_instances_H ciih
928 WHERE instance_history_id = p_ref_id;
929
930 --capturing the new location Name
931
932 select Decode(nvl(ciih.NEW_LOCATION_TYPE_CODE,l_location_type_code) , 'HZ_PARTY_SITES', (SELECT hz.address1
933 ||','
934 ||hz.address2
935 ||','
936 ||hz.address3
937 ||','
938 ||hz.address4
939 ||','
940 ||hz.city
941 ||','
942 ||hz.state
943 ||','
944
945 ||hz.postal_code
946 ||','
947 ||hz.country
948 FROM
949 hz_locations hz,
950 hz_party_sites hps
951 WHERE
952 hz.location_id = hps.location_id
953 AND hps.party_site_id = ciih.new_location_id),
954 'HZ_LOCATIONS', (SELECT hz.address1
955 ||','
956 ||hz.address2
957 ||','
958 ||hz.address3
959 ||','
960 ||hz.address4
961 ||','
962 ||hz.city
963 ||','
964 ||hz.state
965 ||','
966
967 ||hz.postal_code
968 ||','
969 ||hz.country
970 FROM hz_locations hz
971 WHERE
972 hz.location_id = ciih.new_location_id),
973 'VENDOR_SITE', (SELECT
974 pvs.address_line1
975 ||','
976 ||pvs.address_line2
977 ||','
978 ||pvs.address_line3
979 ||','
980 ||pvs.city
981 ||','
982 ||pvs.state
983 ||','
984 ||pvs.zip
985 ||','
986 ||pvs.country
987 FROM po_vendor_sites_all pvs
988 WHERE pvs.vendor_site_id = ciih.new_location_id),
989 'INVENTORY', (SELECT hrl.address_line_1
990 ||','
991 ||hrl.address_line_2
992 ||','
993 ||hrl.address_line_3
994 ||','
995 ||hrl.region_1
996 ||','
997 ||hrl.postal_code
998 ||','
999 ||hrl.country
1000 FROM hr_locations_all hrl
1001 WHERE hrl.location_id = ciih.new_location_id),
1002 'INTERNAL_SITE', (SELECT hrl.address_line_1
1003 ||','
1004 ||hrl.address_line_2
1005 ||','
1006 ||hrl.address_line_3
1007 ||','
1008 ||hrl.region_1
1009 ||','
1010 ||hrl.postal_code
1011 ||','
1012 ||hrl.country
1013 FROM hr_locations_all hrl
1014 WHERE hrl.location_id = ciih.new_location_id),
1015 'WIP', (SELECT hz.address1
1016 ||','
1017 ||hz.address2
1018 ||','
1019 ||hz.address3
1020 ||','
1021 ||hz.address4
1022 ||','
1023 ||hz.city
1024 ||','
1025 ||hz.state
1026 ||','
1027 ||hz.postal_code
1028 ||','
1029 ||hz.country
1030 FROM hz_locations hz
1031 WHERE hz.location_id = ciih.new_location_id),
1032 'PROJECT', (SELECT hz.address1
1033 ||','
1034 ||hz.address2
1035 ||','
1036 ||hz.address3
1037 ||','
1038 ||hz.address4
1039 ||','
1040 ||hz.city
1041 ||','
1042 ||hz.state
1043 ||','
1044 ||hz.postal_code
1045 ||','
1046 ||hz.country
1047 FROM hz_locations hz
1048 WHERE hz.location_id = ciih.new_location_id),
1049 'IN_TRANSIT', (SELECT hz.address1
1050 ||','
1051 ||hz.address2
1052 ||','
1053 ||hz.address3
1054 ||','
1055 ||hz.address4
1056 ||','
1057 ||hz.city
1058 ||','
1059 ||hz.state
1060 ||','
1061 ||hz.postal_code
1062 ||','
1063 ||hz.country
1064 FROM hz_locations hz
1065 WHERE hz.location_id = ciih.new_location_id),
1066 'PO', (SELECT hz.address1
1067 ||','
1068 ||hz.address2
1069 ||','
1070 ||hz.address3
1071 ||','
1072 ||hz.address4
1073 ||','
1074 ||hz.city
1075 ||','
1076 ||hz.state
1077 ||','
1078 ||hz.postal_code
1079 ||','
1080 ||hz.country
1081 FROM hz_locations hz
1082 WHERE
1083 hz.location_id = ciih.new_location_id),
1084 NULL) INTO l_new_loc_name
1085 FROM csi_item_instances_H ciih
1086 WHERE instance_history_id = p_ref_id;
1087
1088 --Now capturing the new and old location names after cutting down the length to the Reference Field
1089 if(l_old_loc_name is not null and length(l_old_loc_name) > 32) then
1090 l_old_loc_name_sub := concat(substr(l_old_loc_name , 1, 32), '...') ;
1091 elsif (l_old_loc_name is not null and length(l_old_loc_name) < 32) then
1092 l_old_loc_name_sub := l_old_loc_name ;
1093 end if;
1094
1095 if(l_new_loc_name is not null and length(l_new_loc_name) > 32) then
1096 l_new_loc_name_sub := concat(substr(l_new_loc_name , 1, 32), '...') ;
1097 elsif (l_new_loc_name is not null and length(l_new_loc_name) < 32) then
1098 l_new_loc_name_sub := l_new_loc_name ;
1099 end if;
1100
1101 l_reference := l_old_loc_name_sub ||'->'|| l_new_loc_name_sub ;
1102
1103
1104 ELSIF l_status1 IS NOT NULL THEN
1105 l_event_id := 2 ;
1106
1107 SELECT to_char(last_updated_by) INTO l_reference
1108 FROM csi_item_instances_h
1109 WHERE instance_history_id = p_ref_id;
1110
1111 ELSIF l_status2 IS NOT NULL THEN
1112 l_event_id := 1 ;
1113
1114 SELECT to_char(last_updated_by) INTO l_reference
1115 FROM csi_item_instances_H
1116 WHERE instance_history_id = p_ref_id;
1117
1118 END IF;
1119
1120 END IF;
1121
1122 IF l_event_id IS NOT NULL THEN
1123
1124 EAM_ASSET_LOG_PVT.insert_row(
1125 p_event_date => p_event_date,
1126 p_event_type => p_event_type,
1127 p_event_id => l_event_id,
1128 p_organization_id => l_organization_id,
1129 p_instance_id => p_instance_id,
1130 p_reference => l_reference,
1131 p_ref_id => p_ref_id,
1132 x_return_status => x_return_status,
1133 x_msg_count => x_msg_count,
1134 x_msg_data => x_msg_data
1135 );
1136 END IF;
1137
1138 /* Standard check of p_commit. */
1139
1140 IF FND_API.TO_BOOLEAN( P_COMMIT ) THEN
1141 COMMIT WORK;
1142 END IF;
1143
1144 -- Standard call to get message count and if count is 1, get message info.
1145
1146 fnd_msg_pub.get
1147 ( p_msg_index_out => x_msg_count ,
1148 p_data => x_msg_data
1149 );
1150 EXCEPTION
1151 WHEN FND_API.G_EXC_ERROR THEN
1152 ROLLBACK TO EAM_ASSET_LOG_PVT;
1153 x_return_status := FND_API.G_RET_STS_ERROR ;
1154 fnd_msg_pub.get
1155 ( p_msg_index_out => x_msg_count ,
1156 p_data => x_msg_data
1157 );
1158 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1159 ROLLBACK TO EAM_ASSET_LOG_PVT;
1160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1161 fnd_msg_pub.get
1162 ( p_msg_index_out => x_msg_count ,
1163 p_data => x_msg_data
1164 );
1165 WHEN OTHERS THEN
1166 ROLLBACK TO EAM_ASSET_LOG_PVT;
1167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1168
1169 IF fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
1170 fnd_msg_pub.add_exc_msg
1171 ( G_PKG_NAME ,
1172 l_api_name
1173 );
1174 END IF;
1175
1176 END instance_update_event;
1177
1178 -- Procedure to Log Meter Transactions
1179 PROCEDURE insert_meter_log(
1180 p_api_version IN number := 1.0,
1181 p_init_msg_list IN varchar2 := fnd_api.g_false,
1182 p_commit IN varchar2 := fnd_api.g_false,
1183 p_validation_level IN number := fnd_api.g_valid_level_full,
1184 p_event_date IN date := sysdate,
1185 p_instance_id IN number := NULL,
1186 p_ref_id IN number,
1187 p_attribute_category IN varchar2 := NULL,
1188 p_attribute1 IN varchar2 := NULL,
1189 p_attribute2 IN varchar2 := NULL,
1190 p_attribute3 IN varchar2 := NULL,
1191 p_attribute4 IN varchar2 := NULL,
1192 p_attribute5 IN varchar2 := NULL,
1193 p_attribute6 IN varchar2 := NULL,
1194 p_attribute7 IN varchar2 := NULL,
1195 p_attribute8 IN varchar2 := NULL,
1196 p_attribute9 IN varchar2 := NULL,
1197 p_attribute10 IN varchar2 := NULL,
1198 p_attribute11 IN varchar2 := NULL,
1199 p_attribute12 IN varchar2 := NULL,
1200 p_attribute13 IN varchar2 := NULL,
1201 p_attribute14 IN varchar2 := NULL,
1202 p_attribute15 IN varchar2 := NULL,
1203 x_return_status OUT NOCOPY varchar2,
1204 x_msg_count OUT NOCOPY number,
1205 x_msg_data OUT NOCOPY varchar2)
1206 IS
1207 l_api_name CONSTANT varchar2(30) := 'insert_meter_log';
1208 l_api_version CONSTANT number := 1.0;
1209 l_full_name CONSTANT varchar2(60) := g_pkg_name || '.' || l_api_name;
1210 l_instance_id number;
1211 l_instance_number varchar2(30);
1212 l_log_id number;
1213 l_event_type varchar2(30) := 'EAM_SYSTEM_EVENTS';
1214 l_event_id number := 3;
1215 l_reference varchar2(80);
1216 l_equipment_gen_object_id number;
1217 l_organization_id number;
1218 l_status number;
1219 l_mfg_org_id number;
1220
1221 CURSOR cmetid IS
1222 SELECT cii.instance_id, cii.instance_number, cii.last_vld_organization_id org_id
1223 FROM csi_counter_associations caa, csi_item_instances cii
1224 WHERE caa.counter_id = p_ref_id AND
1225 cii.instance_id = caa.source_object_id;
1226 BEGIN
1227 -- Standard Start of API savepoint
1228 SAVEPOINT EAM_ASSET_LOG_PVT_SV;
1229
1230 -- Standard call to check for call compatibility.
1231 IF NOT fnd_api.compatible_api_call(
1232 l_api_version
1233 ,p_api_version
1234 ,l_api_name
1235 ,g_pkg_name) THEN
1236 RAISE fnd_api.g_exc_unexpected_error;
1237 END IF;
1238
1239 IF fnd_api.to_boolean(p_init_msg_list) THEN
1240 fnd_msg_pub.initialize;
1241 END IF;
1242
1243 x_return_status := fnd_api.g_ret_sts_success;
1244 BEGIN
1245 SELECT counter_id INTO l_status
1246 FROM csi_counters_b
1247 WHERE counter_id = p_ref_id AND
1248 p_event_date >= NVL(start_date_active, p_event_date) AND
1249 p_event_date <= NVL(end_date_active, p_event_date);
1250
1251
1252 EXCEPTION
1253 WHEN NO_DATA_FOUND THEN
1254 fnd_message.set_name
1255 ( application => 'EAM'
1256 , name => 'EAM_COUNTER_ID_INVALID'
1257 );
1258
1259 fnd_msg_pub.add;
1260 x_return_status:= fnd_api.g_ret_sts_error;
1261 fnd_msg_pub.count_and_get(
1262 p_count => x_msg_count,
1263 p_data => x_msg_data);
1264 RETURN;
1265 END ;
1266
1267
1268 BEGIN
1269 SELECT name INTO l_reference
1270 FROM csi_counters_tl
1271 WHERE language = userenv('Lang')
1272 AND counter_id= p_ref_id;
1273 EXCEPTION
1274 WHEN NO_DATA_FOUND THEN
1275 fnd_message.set_name
1276 ( application => 'EAM'
1277 , name => 'EAM_COUNTER_ID_INVALID'
1278 );
1279
1280 fnd_msg_pub.add;
1281 x_return_status:= fnd_api.g_ret_sts_error;
1282 fnd_msg_pub.count_and_get(
1283 p_count => x_msg_count,
1284 p_data => x_msg_data);
1285 RETURN;
1286 END ;
1287
1288
1289 FOR l_cmetid IN cmetid LOOP
1290 l_instance_id := l_cmetid.instance_id;
1291 l_instance_number := l_cmetid.instance_number;
1292
1293 SELECT maint_organization_id INTO l_organization_id
1294 FROM mtl_parameters
1295 WHERE organization_id = l_cmetid.org_id;
1296
1297 EAM_ASSET_LOG_PVT.validate_event(
1298 p_event_date => p_event_date,
1299 p_event_type => l_event_type,
1300 p_event_id => l_event_id,
1301 p_instance_id => l_instance_id,
1302 p_instance_number => l_instance_number,
1303 x_return_status => x_return_status,
1304 x_msg_count => x_msg_count,
1305 x_msg_data => x_msg_data);
1306
1307
1308 IF x_return_status = fnd_api.g_ret_sts_success THEN
1309
1310 SELECT eam_asset_log_s.nextval INTO l_log_id FROM dual;
1311
1312 EAM_ASSET_LOG_PVT.insert_row(
1313 p_event_date => p_event_date,
1314 p_event_type => l_event_type,
1315 p_event_id => l_event_id,
1316 p_organization_id => l_organization_id,
1317 p_instance_id => l_instance_id,
1318 p_reference => l_reference,
1319 p_ref_id => p_ref_id,
1320 x_return_status => x_return_status,
1321 x_msg_count => x_msg_count,
1322 x_msg_data => x_msg_data
1323 );
1324
1325 END IF;
1326 END LOOP;
1327
1328 -- End of API body.
1329 -- Standard check of p_commit.
1330 IF fnd_api.to_boolean(p_commit) THEN
1331 COMMIT WORK;
1332 END IF;
1333
1334 fnd_msg_pub.count_and_get(
1335 p_count => x_msg_count
1336 ,p_data => x_msg_data);
1337
1338 EXCEPTION
1339 WHEN fnd_api.g_exc_error THEN
1340 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
1341 x_return_status := fnd_api.g_ret_sts_error;
1342 fnd_msg_pub.count_and_get(
1343 p_count => x_msg_count
1344 ,p_data => x_msg_data);
1345 WHEN fnd_api.g_exc_unexpected_error THEN
1346 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
1347 x_return_status := fnd_api.g_ret_sts_unexp_error;
1348 fnd_msg_pub.count_and_get(
1349 p_count => x_msg_count
1350 ,p_data => x_msg_data);
1351 WHEN OTHERS THEN
1352 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
1353 x_return_status := fnd_api.g_ret_sts_unexp_error;
1354
1355 IF fnd_msg_pub.check_msg_level(
1356 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1357 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1358 END IF;
1359
1360 fnd_msg_pub.count_and_get(
1361 p_count => x_msg_count,
1362 p_data => x_msg_data);
1363
1364 END insert_meter_log;
1365
1366 END EAM_ASSET_LOG_PVT;