[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_LOG_PVT
Source
1 PACKAGE BODY EAM_ASSET_LOG_PVT AS
2 /* $Header: EAMVALGB.pls 120.24 2006/09/12 06:37:40 sshahid noship $ */
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(30);
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
671 BEGIN
672
673 /* Standard Start of API savepoint */
674 SAVEPOINT EAM_ASSET_LOG_PVT;
675
676 /* Standard call to check for call compatibility. */
677 IF NOT FND_API.Compatible_API_Call
678 ( l_api_version ,
679 p_api_version ,
680 l_api_name ,
681 G_PKG_NAME
682 )
683 THEN
684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
685 END IF;
686
687 /* Initialize message list if p_init_msg_list is set to TRUE. */
688 IF FND_API.to_Boolean( p_init_msg_list ) THEN
689 fnd_msg_pub.initialize;
690 END IF;
691
692 /* Initialize API return status to success */
693 x_return_status := FND_API.G_RET_STS_SUCCESS;
694
695 BEGIN
696 SELECT mp.maint_organization_id INTO l_organization_id
697 FROM mtl_parameters mp, csi_item_instances cii
698 WHERE mp.organization_id = cii.last_vld_organization_id
699 AND cii.instance_id = p_instance_id;
700
701 EXCEPTION
702 WHEN NO_DATA_FOUND THEN
703 fnd_message.set_name
704 ( application => 'EAM'
705 , name => 'EAM_INSTANCE_ID_INVALID'
706 );
707
708 fnd_msg_pub.add;
709 x_return_status:= fnd_api.g_ret_sts_error;
710 fnd_msg_pub.Count_And_Get
711 ( p_count => x_msg_count,
712 p_data => x_msg_data
713 );
714 null;
715 END;
716
717 /* VALIDATION 1 Instanceid*/
718 l_status := null;
719 IF p_event_type='EAM_SYSTEM_EVENTS' THEN
720 BEGIN
721 SELECT instance_history_id INTO l_status
722 FROM csi_item_instances_H
723 WHERE instance_history_id = p_ref_id
724 AND (nvl(old_location_id,1) <> nvl(new_location_id,1));
725
726 EXCEPTION
727 WHEN NO_DATA_FOUND THEN
728 null;
729 END;
730
731 BEGIN
732 SELECT instance_history_id INTO l_status1
733 FROM csi_item_instances_h
734 WHERE instance_history_id = p_ref_id AND
735 old_active_end_date IS NULL and
736 new_active_end_date IS NOT NULL;
737 EXCEPTION
738 WHEN NO_DATA_FOUND THEN
739 null;
740 END;
741
742 BEGIN
743 SELECT instance_history_id INTO l_status2
744 FROM csi_item_instances_h
745 WHERE instance_history_id = p_ref_id AND
746 old_active_end_date is not null
747 AND new_active_end_date IS NULL;
748 EXCEPTION
749 WHEN NO_DATA_FOUND THEN
750 null;
751 END;
752
753 IF l_status IS NOT NULL THEN
754 l_event_id := 13 ;
755
756 SELECT old_location_id||'->'||new_location_id INTO l_reference
757 FROM csi_item_instances_H
758 WHERE instance_history_id = p_ref_id;
759
760 ELSIF l_status1 IS NOT NULL THEN
761 l_event_id := 2 ;
762
763 SELECT to_char(last_updated_by) INTO l_reference
764 FROM csi_item_instances_h
765 WHERE instance_history_id = p_ref_id;
766
767 ELSIF l_status2 IS NOT NULL THEN
768 l_event_id := 1 ;
769
770 SELECT to_char(last_updated_by) INTO l_reference
771 FROM csi_item_instances_H
772 WHERE instance_history_id = p_ref_id;
773
774 END IF;
775
776 END IF;
777
778 IF l_event_id IS NOT NULL THEN
779
780 EAM_ASSET_LOG_PVT.insert_row(
781 p_event_date => p_event_date,
782 p_event_type => p_event_type,
783 p_event_id => l_event_id,
784 p_organization_id => l_organization_id,
785 p_instance_id => p_instance_id,
786 p_reference => l_reference,
787 p_ref_id => p_ref_id,
788 x_return_status => x_return_status,
789 x_msg_count => x_msg_count,
790 x_msg_data => x_msg_data
791 );
792 END IF;
793
794 /* Standard check of p_commit. */
795
796 IF FND_API.TO_BOOLEAN( P_COMMIT ) THEN
797 COMMIT WORK;
798 END IF;
799
800 -- Standard call to get message count and if count is 1, get message info.
801
802 fnd_msg_pub.get
803 ( p_msg_index_out => x_msg_count ,
804 p_data => x_msg_data
805 );
806 EXCEPTION
807 WHEN FND_API.G_EXC_ERROR THEN
808 ROLLBACK TO EAM_ASSET_LOG_PVT;
809 x_return_status := FND_API.G_RET_STS_ERROR ;
810 fnd_msg_pub.get
811 ( p_msg_index_out => x_msg_count ,
812 p_data => x_msg_data
813 );
814 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
815 ROLLBACK TO EAM_ASSET_LOG_PVT;
816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
817 fnd_msg_pub.get
818 ( p_msg_index_out => x_msg_count ,
819 p_data => x_msg_data
820 );
821 WHEN OTHERS THEN
822 ROLLBACK TO EAM_ASSET_LOG_PVT;
823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
824
825 IF fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
826 fnd_msg_pub.add_exc_msg
827 ( G_PKG_NAME ,
828 l_api_name
829 );
830 END IF;
831
832 END instance_update_event;
833
834 -- Procedure to Log Meter Transactions
835 PROCEDURE insert_meter_log(
836 p_api_version IN number := 1.0,
837 p_init_msg_list IN varchar2 := fnd_api.g_false,
838 p_commit IN varchar2 := fnd_api.g_false,
839 p_validation_level IN number := fnd_api.g_valid_level_full,
840 p_event_date IN date := sysdate,
841 p_instance_id IN number := NULL,
842 p_ref_id IN number,
843 p_attribute_category IN varchar2 := NULL,
844 p_attribute1 IN varchar2 := NULL,
845 p_attribute2 IN varchar2 := NULL,
846 p_attribute3 IN varchar2 := NULL,
847 p_attribute4 IN varchar2 := NULL,
848 p_attribute5 IN varchar2 := NULL,
849 p_attribute6 IN varchar2 := NULL,
850 p_attribute7 IN varchar2 := NULL,
851 p_attribute8 IN varchar2 := NULL,
852 p_attribute9 IN varchar2 := NULL,
853 p_attribute10 IN varchar2 := NULL,
854 p_attribute11 IN varchar2 := NULL,
855 p_attribute12 IN varchar2 := NULL,
856 p_attribute13 IN varchar2 := NULL,
857 p_attribute14 IN varchar2 := NULL,
858 p_attribute15 IN varchar2 := NULL,
859 x_return_status OUT NOCOPY varchar2,
860 x_msg_count OUT NOCOPY number,
861 x_msg_data OUT NOCOPY varchar2)
862 IS
863 l_api_name CONSTANT varchar2(30) := 'insert_meter_log';
864 l_api_version CONSTANT number := 1.0;
865 l_full_name CONSTANT varchar2(60) := g_pkg_name || '.' || l_api_name;
866 l_instance_id number;
867 l_instance_number varchar2(30);
868 l_log_id number;
869 l_event_type varchar2(30) := 'EAM_SYSTEM_EVENTS';
870 l_event_id number := 3;
871 l_reference varchar2(30);
872 l_equipment_gen_object_id number;
873 l_organization_id number;
874 l_status number;
875 l_mfg_org_id number;
876
877 CURSOR cmetid IS
878 SELECT cii.instance_id, cii.instance_number, cii.last_vld_organization_id org_id
879 FROM csi_counter_associations caa, csi_item_instances cii
880 WHERE caa.counter_id = p_ref_id AND
881 cii.instance_id = caa.source_object_id;
882 BEGIN
883 -- Standard Start of API savepoint
884 SAVEPOINT EAM_ASSET_LOG_PVT_SV;
885
886 -- Standard call to check for call compatibility.
887 IF NOT fnd_api.compatible_api_call(
888 l_api_version
889 ,p_api_version
890 ,l_api_name
891 ,g_pkg_name) THEN
892 RAISE fnd_api.g_exc_unexpected_error;
893 END IF;
894
895 IF fnd_api.to_boolean(p_init_msg_list) THEN
896 fnd_msg_pub.initialize;
897 END IF;
898
899 x_return_status := fnd_api.g_ret_sts_success;
900 BEGIN
901 SELECT counter_id INTO l_status
902 FROM csi_counters_b
903 WHERE counter_id = p_ref_id AND
904 p_event_date >= NVL(start_date_active, p_event_date) AND
905 p_event_date <= NVL(end_date_active, p_event_date);
906
907
908 EXCEPTION
909 WHEN NO_DATA_FOUND THEN
910 fnd_message.set_name
911 ( application => 'EAM'
912 , name => 'EAM_COUNTER_ID_INVALID'
913 );
914
915 fnd_msg_pub.add;
916 x_return_status:= fnd_api.g_ret_sts_error;
917 fnd_msg_pub.count_and_get(
918 p_count => x_msg_count,
919 p_data => x_msg_data);
920 RETURN;
921 END ;
922
923
924 BEGIN
925 SELECT name INTO l_reference
926 FROM csi_counters_tl
927 WHERE language = userenv('Lang')
928 AND counter_id= p_ref_id;
929 EXCEPTION
930 WHEN NO_DATA_FOUND THEN
931 fnd_message.set_name
932 ( application => 'EAM'
933 , name => 'EAM_COUNTER_ID_INVALID'
934 );
935
936 fnd_msg_pub.add;
937 x_return_status:= fnd_api.g_ret_sts_error;
938 fnd_msg_pub.count_and_get(
939 p_count => x_msg_count,
940 p_data => x_msg_data);
941 RETURN;
942 END ;
943
944
945 FOR l_cmetid IN cmetid LOOP
946 l_instance_id := l_cmetid.instance_id;
947 l_instance_number := l_cmetid.instance_number;
948
949 SELECT maint_organization_id INTO l_organization_id
950 FROM mtl_parameters
951 WHERE organization_id = l_cmetid.org_id;
952
953 EAM_ASSET_LOG_PVT.validate_event(
954 p_event_date => p_event_date,
955 p_event_type => l_event_type,
956 p_event_id => l_event_id,
957 p_instance_id => l_instance_id,
958 p_instance_number => l_instance_number,
959 x_return_status => x_return_status,
960 x_msg_count => x_msg_count,
961 x_msg_data => x_msg_data);
962
963
964 IF x_return_status = fnd_api.g_ret_sts_success THEN
965
966 SELECT eam_asset_log_s.nextval INTO l_log_id FROM dual;
967
968 EAM_ASSET_LOG_PVT.insert_row(
969 p_event_date => p_event_date,
970 p_event_type => l_event_type,
971 p_event_id => l_event_id,
972 p_organization_id => l_organization_id,
973 p_instance_id => l_instance_id,
974 p_reference => l_reference,
975 p_ref_id => p_ref_id,
976 x_return_status => x_return_status,
977 x_msg_count => x_msg_count,
978 x_msg_data => x_msg_data
979 );
980
981 END IF;
982 END LOOP;
983
984 -- End of API body.
985 -- Standard check of p_commit.
986 IF fnd_api.to_boolean(p_commit) THEN
987 COMMIT WORK;
988 END IF;
989
990 fnd_msg_pub.count_and_get(
991 p_count => x_msg_count
992 ,p_data => x_msg_data);
993
994 EXCEPTION
995 WHEN fnd_api.g_exc_error THEN
996 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
997 x_return_status := fnd_api.g_ret_sts_error;
998 fnd_msg_pub.count_and_get(
999 p_count => x_msg_count
1000 ,p_data => x_msg_data);
1001 WHEN fnd_api.g_exc_unexpected_error THEN
1002 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
1003 x_return_status := fnd_api.g_ret_sts_unexp_error;
1004 fnd_msg_pub.count_and_get(
1005 p_count => x_msg_count
1006 ,p_data => x_msg_data);
1007 WHEN OTHERS THEN
1008 ROLLBACK TO EAM_ASSET_LOG_PVT_SV;
1009 x_return_status := fnd_api.g_ret_sts_unexp_error;
1010
1011 IF fnd_msg_pub.check_msg_level(
1012 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1013 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1014 END IF;
1015
1016 fnd_msg_pub.count_and_get(
1017 p_count => x_msg_count,
1018 p_data => x_msg_data);
1019
1020 END insert_meter_log;
1021
1022 END EAM_ASSET_LOG_PVT;