[Home] [Help]
PACKAGE BODY: APPS.EAM_PROCESS_FAILURE_ENTRY_PVT
Source
1 PACKAGE BODY Eam_Process_Failure_Entry_PVT AS
2 /* $Header: EAMVFENB.pls 120.4.12020000.2 2012/08/13 19:05:21 rsandepo ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2005 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 -- EAMVFENS.pls
10 --
11 -- DESCRIPTION
12 -- This package defines private APIs
13 -- 1. Failure Information insertion/ updation at Work Order Level
14 --
15 -- NOTES
16 --
17 -- HISTORY
18 -- 03-JAN-2006 Bhushan Goel Initial Creation
19 ***************************************************************************/
20
21 G_PKG_NAME CONSTANT VARCHAR2(30):='Eam_Process_Failure_Entry_PVT';
22
23 /* Procedure to Validate the Failure Information Header Recrod */
24
25 PROCEDURE Validate_Failure_Entry_Record
26 (
27 p_eam_failure_entry_record IN EAM_Process_Failure_Entry_PUB.EAM_Failure_Entry_Record_Typ
28 , x_reason_failed OUT NOCOPY VARCHAR2
29 , x_token_name OUT NOCOPY VARCHAR2
30 , x_token_value OUT NOCOPY VARCHAR2
31 , x_return_status OUT NOCOPY BOOLEAN
32 ) IS
33
34 l_eam_failure_entry_record Eam_Process_Failure_Entry_PUB.Eam_Failure_Entry_Record_Typ;
35 l_eam_failure_codes_tbl Eam_Process_Failure_Entry_PUB.Eam_Failure_Codes_Tbl_Typ;
36 l_eam_failure_codes_record Eam_Process_Failure_Entry_PUB.Eam_Failure_Codes_Typ;
37
38 l_inventory_item_id NUMBER;
39 l_organization_id NUMBER;
40
41 l_valid_source_type NUMBER;
42 l_valid_source_id NUMBER;
43 l_valid_object_type NUMBER;
44 l_valid_object_id NUMBER;
45 l_valid_failure_codes_record BOOLEAN;
46
47 l_failure_code_required VARCHAR2(1);
48 l_reason_failed VARCHAR2(4000);
49
50 l_failure_id NUMBER;
51 l_failure_exists NUMBER;
52
53 BEGIN
54
55 /* We are not validating the following Failure Information Fields
56 * DEPARTMENT_ID
57 * AREA_ID
58 * MAINT_ORGANIZATION_ID
59 * CURRENT_ORGANIZATION_ID
60 */
61
62 x_token_name := NULL;
63 x_token_value := NULL;
64
65 l_valid_source_type := 0;
66 l_valid_source_id := 0;
67 l_valid_object_type := 0;
68 l_valid_object_id := 0;
69
70 l_eam_failure_entry_record := p_eam_failure_entry_record;
71
72 l_failure_exists := 0;
73
74 IF ( l_eam_failure_entry_record.transaction_type <> Eam_Process_Failure_Entry_PUB.G_FE_CREATE
75 AND l_eam_failure_entry_record.transaction_type <> Eam_Process_Failure_Entry_PUB.G_FE_UPDATE
76 )
77 THEN
78 /* Invalid Transaction Type */
79 x_reason_failed := 'EAM_FA_INVALID_TXN_TYPE';
80 x_return_status := false;
81 return ;
82 END IF;
83
84 IF ( l_eam_failure_entry_record.failure_date IS NOT NULL
85 AND l_eam_failure_entry_record.failure_date > SYSDATE
86 ) THEN
87 --Reported failure date can not be a future date
88 x_reason_failed := 'EAM_DATE_GREATER_SYSDATE';
89 x_return_status := false;
90 return ;
91 END IF;
92
93 IF l_eam_failure_entry_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_CREATE
94 THEN
95
96 IF ( l_eam_failure_entry_record.source_id IS NULL
97 OR l_eam_failure_entry_record.source_type IS NULL
98 OR l_eam_failure_entry_record.object_id IS NULL
99 OR l_eam_failure_entry_record.object_type IS NULL
100 ) THEN
101 -- Some of the following failure entry required parameters are null
102 -- source_id, source_typ, object_id, object_typ
103 x_reason_failed:='EAM_FAILURE_RECORD_NULL';
104 x_return_status := false;
105 return ;
106 END IF;
107
108 /* Validate object_typ and source_typ */
109 SELECT COUNT(1)
110 INTO l_valid_object_type
111 FROM MFG_LOOKUPS
112 WHERE lookup_type = 'EAM_ASSET_FAIL_SRC_TYPE'
113 AND lookup_code = l_eam_failure_entry_record.source_type;
114
115 IF l_valid_object_type = 0 THEN
116 x_reason_failed:='EAM_INVALID_SRC_TYPE';
117 x_token_name := 'SOURCE_TYPE';
118 x_token_value := l_eam_failure_entry_record.source_type;
119 x_return_status := false;
120 return ;
121 END IF;
122
123 SELECT COUNT(1)
124 INTO l_valid_object_type
125 FROM MFG_LOOKUPS
126 WHERE lookup_type = 'WIP_MAINTENANCE_OBJECT_TYPE'
127 AND lookup_code = l_eam_failure_entry_record.object_type;
128
129 IF l_valid_object_type = 0 THEN
130 x_reason_failed:='EAM_INVALID_OBJECT_TYPE';
131 x_token_name := 'OBJECT_TYPE';
132 x_token_value := l_eam_failure_entry_record.object_type;
133 x_return_status := false;
134 return ;
135 END IF;
136
137 IF l_eam_failure_entry_record.object_type = 1 THEN
138 SELECT COUNT(1)
139 INTO l_valid_object_id
140 FROM CSI_ITEM_INSTANCES
141 WHERE instance_id = l_eam_failure_entry_record.object_id;
142 IF l_valid_object_id = 0 THEN
143 -- Object_id is not a valid maintenace object
144 -- Not a Valid Maintenance Object Id
145 x_reason_failed:='EAM_INVALID_OBJECT_ID';
146 x_token_name := 'ASSET_NUMBER';
147 x_token_value := l_eam_failure_entry_record.object_id;
148 x_return_status := false;
149 return ;
150 END IF;
151 END IF;
152
153 IF l_eam_failure_entry_record.source_type = 1 THEN
154 SELECT COUNT(1)
155 INTO l_valid_source_id
156 FROM wip_discrete_jobs wdj, wip_entities we
157 WHERE wdj.wip_entity_id = we.wip_entity_id
158 AND wdj.wip_entity_id = l_eam_failure_entry_record.source_id
159 AND we.entity_type IN (6,7);
160
161 IF l_valid_source_id = 0 THEN
162 x_reason_failed:='EAM_INVALID_WIP_ENTITY_ID';
163 x_token_name := 'SOURCE_ID';
164 x_token_value := l_eam_failure_entry_record.source_id;
165 x_return_status := false;
166 return ;
167 END IF;
168 END IF;
169
170 /* Check if the record already exists corresponding to
171 the source_id, source_type */
172 SELECT COUNT(1)
173 INTO l_failure_exists
174 FROM eam_asset_failures
175 WHERE source_type = l_eam_failure_entry_record.source_type
176 AND source_id = l_eam_failure_entry_record.source_id;
177
178 IF l_failure_exists >= 1 THEN
179 x_reason_failed:='EAM_FAILURE_EXISTS';
180 x_token_name := 'SOURCE_ID';
181 x_token_value := l_eam_failure_entry_record.source_id;
182 x_return_status := false;
183 return ;
184 END IF;
185
186 ELSIF l_eam_failure_entry_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
187
188 /* Failure Date is Already Validated on top of the API for both Create/ Update */
189
190 SELECT COUNT(1)
191 INTO l_failure_exists
192 FROM eam_asset_failures
193 WHERE failure_id = l_eam_failure_entry_record.failure_id;
194
195 IF l_failure_exists = 0 THEN
196 x_reason_failed:='EAM_FAILURE_NOT_EXISTS';
197 x_token_name := 'SOURCE_ID';
198 x_token_value := l_eam_failure_entry_record.source_id;
199 x_return_status := false;
200 return ;
201 END IF;
202
203 END IF;
204
205 x_return_status := true;
206
207 END Validate_Failure_Entry_Record;
208
209
210 /* Procedure to Validate the Child Failure Information Record Table.
211 */
212 PROCEDURE Validate_Failure_Codes
213 (
214 p_eam_failure_codes_tbl_typ IN Eam_Process_Failure_Entry_Pub.Eam_Failure_Codes_Tbl_Typ
215 , x_reason_failed OUT NOCOPY VARCHAR2
216 , x_token_name OUT NOCOPY VARCHAR2
217 , x_token_value OUT NOCOPY VARCHAR2
218 , x_return_status OUT NOCOPY BOOLEAN
219 ) IS
220
221 l_eam_failure_codes_tbl Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ;
222 l_eam_failure_codes_record Eam_Process_Failure_Entry_PUB.eam_failure_codes_typ;
223
224 l_valid_header_record NUMBER;
225 l_valid_failure_code NUMBER;
226 l_valid_cause_code NUMBER;
227 l_valid_resolution_code NUMBER;
228
229 l_failure_codes_exists NUMBER;
230 l_validate_failure_codes BOOLEAN;
231
232 l_inventory_item_id NUMBER;
233
234 l_old_failure_code VARCHAR2(80);
235 l_old_cause_code VARCHAR2(80);
236 l_old_resolution_code VARCHAR2(80);
237
238 BEGIN
239
240 l_valid_header_record := 0;
241 l_valid_failure_code := 0;
242 l_valid_cause_code := 0;
243 l_valid_resolution_code := 0;
244
245 l_failure_codes_exists := 0;
246 l_validate_failure_codes := true;
247
248 l_eam_failure_codes_tbl := p_eam_failure_codes_tbl_typ;
249
250 /* Validate each Failure Codes Record One By One */
251 FOR i in 1..l_eam_failure_codes_tbl.count
252 LOOP
253
254 l_eam_failure_codes_record := l_eam_failure_codes_tbl(i);
255
256 IF l_eam_failure_codes_record.transaction_type IN (Eam_Process_Failure_Entry_PUB.G_FE_CREATE, Eam_Process_Failure_Entry_PUB.G_FE_UPDATE) THEN
257
258 SELECT count(1)
259 INTO l_valid_header_record
260 FROM eam_asset_failures eaf
261 WHERE eaf.failure_id = l_eam_failure_codes_record.failure_id;
262
263 IF l_valid_header_record = 0 THEN
264 x_reason_failed:= 'EAM_HEADER_REC_NOT_EXISTS';
265 x_return_status := false;
266 return ;
267 END IF;
268
269 IF l_eam_failure_codes_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
270 BEGIN
271 SELECT failure_code, cause_code, resolution_code
272 INTO l_old_failure_code, l_old_cause_code, l_old_resolution_code
273 FROM eam_asset_failure_codes
274 WHERE failure_entry_id = l_eam_failure_codes_record.failure_entry_id;
275 EXCEPTION
276 WHEN NO_DATA_FOUND THEN
277 x_reason_failed:='EAM_FAILURE_CHILD_NOT_EXISTS';
278 x_return_status := false;
279 return ;
280 END;
281
282 IF ( ( nvl(l_eam_failure_codes_record.failure_code,'NULL VALUE') = nvl(l_old_failure_code,'NULL VALUE')
283 AND nvl(l_eam_failure_codes_record.cause_code,'NULL VALUE') = nvl(l_old_cause_code,'NULL VALUE')
284 AND nvl(l_eam_failure_codes_record.resolution_code,'NULL VALUE') = nvl(l_old_resolution_code,'NULL VALUE')
285 ) /*OR
286 (
287 l_eam_failure_codes_record.failure_code IS NULL
288 AND l_eam_failure_codes_record.cause_code IS NULL
289 AND l_eam_failure_codes_record.resolution_code IS NULL
290 ) */
291 ) THEN
292 l_validate_failure_codes := false;
293
294 ELSE
295
296 SELECT count(1)
297 INTO l_failure_codes_exists
298 FROM eam_asset_failure_codes eafc
299 WHERE eafc.failure_id = l_eam_failure_codes_record.failure_id
300 AND nvl(eafc.failure_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.failure_code,'NULL VALUE')
301 AND nvl(eafc.cause_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.cause_code,'NULL VALUE')
302 AND nvl(eafc.resolution_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.resolution_code,'NULL VALUE');
303
304 IF l_failure_codes_exists <> 0 THEN
305 x_reason_failed:='EAM_FAILURE_ALREADY_EXISTS';
306 x_return_status := false;
307 return ;
308 END IF;
309
310 END IF;
311
312 ELSIF l_eam_failure_codes_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_CREATE THEN
313
314 IF ( l_eam_failure_codes_record.failure_code IS NULL
315 AND l_eam_failure_codes_record.cause_code IS NULL
316 AND l_eam_failure_codes_record.resolution_code IS NULL
317 AND l_eam_failure_codes_record.comments IS NULL
318 )
319 THEN
320 x_reason_failed:='EAM_FAILURE_CHILD_NULL';
321 x_return_status := false;
322 return ;
323 END IF;
324
325 IF ( l_eam_failure_codes_record.failure_code IS NOT NULL
326 OR l_eam_failure_codes_record.cause_code IS NOT NULL
327 OR l_eam_failure_codes_record.resolution_code IS NOT NULL
328 )
329 THEN
330
331 /* Check if the record already exists corresponding to the failure_id */
332 SELECT COUNT(1)
333 INTO l_failure_codes_exists
334 FROM eam_asset_failure_codes eafc
335 WHERE eafc.failure_id = l_eam_failure_codes_record.failure_id;
336
337 IF l_failure_codes_exists >= 1 THEN
338 x_reason_failed:='EAM_MULTIPLE_CHILD';
339 x_return_status := false;
340 return ;
341 END IF;
342
343 l_failure_codes_exists := 0;
344
345 /* Check for the Existing Record */
346 SELECT count(1)
347 INTO l_failure_codes_exists
348 FROM eam_asset_failure_codes eafc
349 WHERE eafc.failure_id = l_eam_failure_codes_record.failure_id
350 AND nvl(eafc.failure_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.failure_code,'NULL VALUE')
351 AND nvl(eafc.cause_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.cause_code,'NULL VALUE')
352 AND nvl(eafc.resolution_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.resolution_code,'NULL VALUE');
353
354 IF l_failure_codes_exists <> 0 THEN
355 x_reason_failed:='EAM_FAILURE_ALREADY_EXISTS';
356 x_return_status := false;
357 return ;
358 END IF;
359
360
361 /*************
362 Need to Verify Whether to Support Multiple Child Records with Comments Only
363 ELSE
364 SELECT count(1)
365 INTO l_failure_codes_exists
366 FROM eam_asset_failure_codes eafc
367 WHERE eafc.failure_id = l_eam_failure_codes_record.failure_id
368 *************/
369 END IF;
370 END IF; /* Create/ Update transaction type validation of failure codes */
371
372 ELSE
373 x_reason_failed:= 'EAM_FA_INVALID_TXN_TYPE';
374 x_return_status := false;
375 return ;
376 END IF;
377
378 /***************************************
379 No need to Validate Inventory Item Id Here, It has to validated
380 while creating the Header Record for the Failure Information.
381 In case of creating the Failure Information child record only,
382 we are validating the existance for the Failure Information Header
383 Required to Validate the Failure Codes.
384 */
385 BEGIN
386 SELECT inventory_item_id
387 INTO l_inventory_item_id
388 FROM CSI_ITEM_INSTANCES
389 WHERE instance_id = ( SELECT object_id FROM eam_asset_failures
390 WHERE failure_id = l_eam_failure_codes_record.failure_id
391 );
392 EXCEPTION
393 WHEN NO_DATA_FOUND THEN
394 -- Object_id is not a valid maintenace object
395 -- Not a Valid Maintenance Object Id
396 x_reason_failed:='EAM_INVALID_OBJECT_ID';
397 x_return_status := false;
398 return ;
399 END;
400
401
402 IF ( l_eam_failure_codes_record.failure_code IS NOT NULL
403 AND l_validate_failure_codes
404 ) THEN
405
406 --Modified for 13011472
407 SELECT COUNT(1)
408 INTO l_valid_failure_code
409 FROM eam_failure_combinations EFC,
410 eam_failure_set_associations EFSA
411 WHERE EFC.failure_code = l_eam_failure_codes_record.failure_code
412 AND EFSA.inventory_item_id = l_inventory_item_id
413 AND EFSA.effective_end_date is null
414 AND EFSA.set_id = EFC.set_id
415 AND exists ( SELECT /*+ no_unnest */ 'x'
416 FROM EAM_FAILURE_COMBINATIONS EFC2
417 WHERE NVL(EFC2.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
418 AND EFC2.SET_ID = EFSA.SET_ID
419 )
420 AND exists ( SELECT 'x'
421 FROM EAM_FAILURE_SETS EFS
422 WHERE NVL(EFS.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
423 AND EFS.SET_ID = EFSA.SET_ID
424 )
425 AND rownum = 1;
426 -- end of 13011472
427
428 IF l_valid_failure_code = 0 THEN
429 -- Failure code is not valid for this maintenance object id
430 -- Not a Valid Failure Code
431 x_reason_failed:='EAM_INVALID_FAILURE_CODE';
432 x_token_name := 'FAILURE_CODE';
433 x_token_value := l_eam_failure_codes_record.failure_code;
434 x_return_status := false;
435 return ;
436 END IF;
437
438 END IF;
439
440 IF ( l_eam_failure_codes_record.cause_code IS NOT NULL
441 AND l_validate_failure_codes
442 ) THEN
443
444 SELECT count(1)
445 INTO l_valid_cause_code
446 FROM eam_failure_combinations EFC,
447 eam_failure_set_associations EFSA
448 WHERE EFC.cause_code = l_eam_failure_codes_record.cause_code
449 AND EFSA.inventory_item_id = l_inventory_item_id
450 AND EFSA.effective_end_date IS NULL
451 AND EFSA.set_id = efc.set_id
452 AND efc.failure_code = l_eam_failure_codes_record.failure_code
453 AND SYSDATE <= ( SELECT min(nvl(EFC2.effective_end_date, sysdate))
454 FROM eam_failure_combinations EFC2
455 WHERE nvl(EFC2.effective_end_date, sysdate) >= sysdate
456 AND EFC2.set_id = EFSA.set_id
457 )
458 AND SYSDATE <= ( SELECT min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE))
459 FROM EAM_FAILURE_SETS EFS
460 WHERE nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
461 AND EFS.SET_ID = EFSA.SET_ID
462 );
463 IF l_valid_cause_code = 0 THEN
464 -- Cause code is not valid for this maintenance object id
465 -- Not a Valid Cause Code
466 x_reason_failed:='EAM_INVALID_CAUSE_CODE';
467 x_token_name := 'CAUSE_CODE';
468 x_token_value := l_eam_failure_codes_record.cause_code;
469 x_return_status := false;
470 return ;
471 END IF;
472
473 END IF;
474
475 IF ( l_eam_failure_codes_record.resolution_code IS NOT NULL
476 AND l_validate_failure_codes
477 ) THEN
478
479 SELECT COUNT(1)
480 INTO l_valid_resolution_code
481 FROM eam_failure_combinations EFC,
482 eam_failure_set_associations EFSA
483 WHERE EFC.resolution_code = l_eam_failure_codes_record.resolution_code
484 AND EFSA.inventory_item_id = l_inventory_item_id
485 AND EFSA.EFFECTIVE_END_DATE IS NULL
486 AND EFSA.set_id = efc.set_id
487 AND EFC.failure_code = l_eam_failure_codes_record.failure_code
488 AND EFC.cause_code = l_eam_failure_codes_record.cause_code
489 AND SYSDATE <= ( SELECT min(nvl(EFC2.effective_end_date, sysdate))
490 FROM eam_failure_combinations EFC2
491 WHERE nvl(EFC2.effective_end_date, sysdate) >= sysdate
492 AND EFC2.set_id = EFSA.set_id
493 )
494 AND SYSDATE <= ( SELECT min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE))
495 FROM EAM_FAILURE_SETS EFS
496 WHERE nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
497 AND EFS.SET_ID = EFSA.SET_ID
498 );
499
500 IF l_valid_resolution_code = 0 THEN
501 -- Resolution code is not valid for this maintenance object id
502 -- Not a Valid Resolution Code
503 x_reason_failed:='EAM_INVALID_RESOLUTION_CODE';
504 x_token_name := 'RESOLUTION_CODE';
505 x_token_value := l_eam_failure_codes_record.resolution_code;
506 x_return_status := false;
507 return ;
508 END IF;
509
510 END IF;
511 l_eam_failure_codes_record := NULL;
512
513 END LOOP;
514 x_return_status := true;
515
516 END;
517
518
519 /**************************************************************************
520 -- Start of comments
521 -- API name : Process_Failure_Entry
522 -- Type : Public.
523 -- Function : Insert/ Update Failure Information corresponding
524 -- to a work order
525 -- Pre-reqs : None.
526 -- Parameters :
527 -- IN : p_api_version IN NUMBER Required
528 -- p_init_msg_list IN VARCHAR2 Optional
529 -- Default = FND_API.G_FALSE
530 -- p_commit IN VARCHAR2 Optional
531 -- Default = FND_API.G_FALSE
532 -- p_eam_failure_entry_record IN
533 -- Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
534 -- p_eam_failure_codes_tbl IN
535 -- Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
536 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
537 -- x_msg_count OUT NOCOPY NUMBER
538 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
539 -- x_eam_failure_entry_record OUT NOCOPY
540 -- Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
541 -- x_eam_failure_codes_tbl OUT NOCOPY
542 -- Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
543 -- Version : Current version 1.0.
544 -- Initial version 1.0
545 -- End of comments
546 ***************************************************************************/
547
548 PROCEDURE Process_Failure_Entry
549 ( p_api_version IN NUMBER := 1.0
550 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
551 , p_commit IN VARCHAR2 := FND_API.G_FALSE
552 , p_eam_failure_entry_record IN Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
553 , p_eam_failure_codes_tbl IN Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
554 , x_return_status OUT NOCOPY VARCHAR2
555 , x_msg_count OUT NOCOPY NUMBER
556 , x_msg_data OUT NOCOPY VARCHAR2
557 , x_eam_failure_entry_record OUT NOCOPY Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
558 , x_eam_failure_codes_tbl OUT NOCOPY Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
559 ) IS
560
561 l_api_name CONSTANT VARCHAR2(30) := 'Process_Failure_Entry';
562 l_api_version CONSTANT NUMBER := 1.0;
563
564 l_eam_failure_entry_record Eam_Process_Failure_Entry_Pub.Eam_Failure_Entry_Record_Typ;
565 l_eam_failure_codes_tbl Eam_Process_Failure_Entry_Pub.Eam_Failure_Codes_Tbl_Typ;
566 l_eam_failure_codes_record Eam_Process_Failure_Entry_Pub.Eam_Failure_Codes_Typ;
567
568 l_eam_asset_failure_codes_rec Eam_Asset_Failure_Codes%ROWTYPE;
569
570 l_failure_id NUMBER;
571
572 l_inventory_item_id NUMBER;
573 l_organization_id NUMBER;
574
575 l_validate_failure_codes BOOLEAN;
576
577 l_return_status BOOLEAN ;
578
579 l_reason_failed VARCHAR2(4000);
580 l_message_name VARCHAR2(20000);
581
582 l_failure_entry_id NUMBER;
583
584 l_combination_id NUMBER;
585
586
587 l_token_name VARCHAR2(30);
588 l_token_value VARCHAR2(100);
589
590 BEGIN
591
592 /* dbms_output.put_line('Start Processing Process_Failure_Entry'); */
593
594 -- API savepoint
595 SAVEPOINT Process_Failure_Entry_PVT;
596
597 -- check for call compatibility.
598 IF NOT FND_API.Compatible_API_Call (l_api_version,
599 p_api_version,
600 l_api_name,
601 G_PKG_NAME )
602 THEN
603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
604 END IF;
605
606 -- Initialize message list if p_init_msg_list is set to TRUE.
607 IF FND_API.to_Boolean( p_init_msg_list ) THEN
608 FND_MSG_PUB.initialize;
609 END IF;
610
611 -- Initialize API return status to success
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613 l_token_name := NULL;
614 l_token_value := NULL;
615
616 l_eam_failure_entry_record := p_eam_failure_entry_record;
617 l_eam_failure_codes_tbl := p_eam_failure_codes_tbl;
618
619 IF l_eam_failure_entry_record.transaction_type IS NULL THEN
620 --Don't validate the header record
621 NULL;
622 ELSIF l_eam_failure_entry_record.transaction_type IN (Eam_Process_Failure_Entry_PUB.G_FE_CREATE, Eam_Process_Failure_Entry_PUB.G_FE_UPDATE) THEN
623 /* dbms_output.put_line('Befor Validate_failure_entry_record'); */
624 l_return_status := true;
625 validate_failure_entry_record(
626 p_eam_failure_entry_record => l_eam_failure_entry_record
627 , x_reason_failed => l_reason_failed
628 , x_token_name => l_token_name
629 , x_token_value => l_token_value
630 , x_return_status => l_return_status
631 );
632 /* dbms_output.put_line('After Validate_failure_entry_record'); */
633 IF (NOT l_return_status) THEN
634 /* dbms_output.put_line('failure_entry_record is not valid'); */
635 FND_MESSAGE.SET_NAME ('EAM', l_reason_failed);
636 IF ( l_token_name is not null AND l_token_value IS NOT NULL )
637 THEN
638 fnd_message.set_token
639 ( token => l_token_name
640 , value => l_token_value
641 );
642 END IF;
643 FND_MSG_PUB.Add;
644 RAISE FND_API.G_EXC_ERROR;
645 END IF;
646
647 ELSE
648 /* dbms_output.put_line('After Validate_failure_entry_record1'); */
649 --Please Enter a Valid Transaction typ:
650 --1: Failure Information Entry
651 --2: Failure Information Update
652 FND_MESSAGE.SET_NAME ('EAM', 'EAM_FA_INVALID_TXN_TYPE');
653 FND_MSG_PUB.Add;
654 RAISE FND_API.G_EXC_ERROR;
655 /* dbms_output.put_line('After Validate_failure_entry_record2'); */
656
657 END IF;
658
659 /* dbms_output.put_line('Just Before Inserting the data into eam_asset_failures00'); */
660
661 IF l_eam_failure_entry_record.transaction_type IS NOT NULL THEN
662
663 IF l_eam_failure_entry_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_CREATE THEN
664 /* dbms_output.put_line('Just Before Inserting the data into eam_asset_failures0'); */
665 SELECT eam_asset_failures_s.nextval
666 INTO l_failure_id
667 FROM DUAL;
668 l_eam_failure_entry_record.failure_id := l_failure_id;
669 /* dbms_output.put_line('Just Before Inserting the data into eam_asset_failures'); */
670 INSERT INTO eam_asset_failures
671 (
672 FAILURE_ID,
673 FAILURE_DATE,
674 SOURCE_TYPE,
675 SOURCE_ID,
676 OBJECT_TYPE,
677 OBJECT_ID,
678 MAINT_ORGANIZATION_ID,
679 CURRENT_ORGANIZATION_ID,
680 DEPARTMENT_ID,
681 AREA_ID,
682 CREATED_BY,
683 CREATION_DATE,
684 LAST_UPDATE_DATE,
685 LAST_UPDATED_BY,
686 LAST_UPDATE_LOGIN
687 )
688 VALUES
689 (
690 l_eam_failure_entry_record.failure_id,
691 l_eam_failure_entry_record.failure_date,
692 l_eam_failure_entry_record.source_type,
693 l_eam_failure_entry_record.source_id,
694 l_eam_failure_entry_record.object_type,
695 l_eam_failure_entry_record.object_id,
696 l_eam_failure_entry_record.maint_organization_id,
697 l_eam_failure_entry_record.current_organization_id,
698 l_eam_failure_entry_record.department_id,
699 l_eam_failure_entry_record.area_id,
700 FND_GLOBAL.user_id,
701 SYSDATE,
702 SYSDATE,
703 FND_GLOBAL.user_id,
704 FND_GLOBAL.user_id
705 );
706 /* dbms_output.put_line('Just After Inserting the data into eam_asset_failures'); */
707
708 FOR i IN 1..l_eam_failure_codes_tbl.count
709 LOOP
710 IF l_eam_failure_codes_tbl(i).transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_CREATE THEN
711 l_eam_failure_codes_tbl(i).failure_id := l_failure_id;
712 END IF;
713 END LOOP;
714
715 ELSIF l_eam_failure_entry_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
716 /* dbms_output.put_line('Just Before Updating the data into eam_asset_failures'); */
717 UPDATE eam_asset_failures
718 SET failure_date = l_eam_failure_entry_record.failure_date
719 /* ,source_typ = l_eam_failure_entry_record.source_typ
720 ,source_id = l_eam_failure_entry_record.source_id
721 ,object_typ = l_eam_failure_entry_record.object_typ
722 ,object_id = l_eam_failure_entry_record.object_id
723 ,maint_organization_id = l_eam_failure_entry_record.maint_organization_id
724 ,current_organization_id = l_eam_failure_entry_record.current_organization_id */
725 ,department_id = l_eam_failure_entry_record.department_id
726 ,area_id = l_eam_failure_entry_record.area_id
727 ,last_update_date = SYSDATE
728 ,last_updated_by = FND_GLOBAL.user_id
729 ,last_update_login= FND_GLOBAL.user_id
730 WHERE failure_id = l_eam_failure_entry_record.failure_id;
731 /* dbms_output.put_line('Just After Updating the data into eam_asset_failures'); */
732
733 END IF;
734 END IF;
735 /* dbms_output.put_line('Just Before Validating the data into eam_asset_failures_codes'); */
736 --Validate the child records
737 l_return_status := true;
738 validate_failure_codes(
739 p_eam_failure_codes_tbl_typ => l_eam_failure_codes_tbl
740 , x_reason_failed => l_reason_failed
741 , x_token_name => l_token_name
742 , x_token_value => l_token_value
743 , x_return_status => l_return_status
744 );
745 /* dbms_output.put_line('Just After Validating the data into eam_asset_failures_codes'); */
746 IF (NOT l_return_status ) THEN
747 FND_MESSAGE.SET_NAME ('EAM', l_reason_failed);
748 IF ( l_token_name is not null AND l_token_value IS NOT NULL )
749 THEN
750 fnd_message.set_token
751 ( token => l_token_name
752 , value => l_token_value
753 );
754 END IF;
755 FND_MSG_PUB.Add;
756 RAISE FND_API.G_EXC_ERROR;
757 END IF;
758
759 FOR i IN 1..l_eam_failure_codes_tbl.count
760 LOOP
761 l_eam_failure_codes_record := l_eam_failure_codes_tbl(i);
762 IF l_eam_failure_codes_record.transaction_type IS NOT NULL THEN
763
764 l_combination_id := NULL;
765 IF( l_eam_failure_codes_record.failure_code IS NOT NULL
766 AND l_eam_failure_codes_record.cause_code IS NOT NULL
767 AND l_eam_failure_codes_record.resolution_code IS NOT NULL
768 )
769 THEN
770 BEGIN
771
772 SELECT inventory_item_id
773 INTO l_inventory_item_id
774 FROM CSI_ITEM_INSTANCES
775 WHERE instance_id = ( SELECT object_id
776 FROM eam_asset_failures
777 WHERE failure_id = l_eam_failure_codes_record.failure_id
778 );
779 SELECT COMBINATION_ID
780 INTO l_combination_id
781 FROM eam_failure_combinations EFC,
782 eam_failure_set_associations EFSA
783 WHERE EFC.failure_code = l_eam_failure_codes_record.failure_code
784 AND EFC.cause_code = l_eam_failure_codes_record.cause_code
785 AND EFC.resolution_code = l_eam_failure_codes_record.resolution_code
786 AND EFSA.inventory_item_id = l_inventory_item_id
787 AND EFSA.effective_end_date is null
788 AND EFSA.set_id = EFC.set_id
789 AND SYSDATE <= ( SELECT min(nvl(EFC2.effective_end_date, sysdate))
790 FROM eam_failure_combinations EFC2
791 WHERE nvl(EFC2.effective_end_date, sysdate) >= sysdate
792 AND EFC2.set_id = EFSA.set_id
793 )
794 AND SYSDATE <= ( SELECT min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE))
795 FROM EAM_FAILURE_SETS EFS
796 WHERE nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
797 AND EFS.SET_ID = EFSA.SET_ID
798 );
799 EXCEPTION
800 WHEN NO_DATA_FOUND THEN
801 l_combination_id := NULL;
802 END;
803 END IF;
804 l_eam_failure_codes_record.combination_id := l_combination_id;
805
806 IF l_eam_failure_codes_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_CREATE THEN
807
808 /* dbms_output.put_line('Just Before Inserting the data into eam_asset_failure_codes'); */
809
810 SELECT eam_asset_failure_codes_s.nextval
811 INTO l_failure_entry_id
812 FROM DUAL;
813 l_eam_failure_codes_record.failure_entry_id := l_failure_entry_id;
814
815 INSERT INTO eam_asset_failure_codes
816 (
817 failure_id,
818 failure_entry_id,
819 combination_id,
820 failure_code,
821 cause_code,
822 resolution_code,
823 comments,
824 created_by,
825 creation_date,
826 last_update_date,
827 last_updated_by,
828 last_update_login
829 )
830 VALUES
831 (
832 l_eam_failure_codes_record.failure_id,
833 l_eam_failure_codes_record.failure_entry_id,
834 l_eam_failure_codes_record.combination_id,
835 l_eam_failure_codes_record.failure_code,
836 l_eam_failure_codes_record.cause_code,
837 l_eam_failure_codes_record.resolution_code,
838 l_eam_failure_codes_record.comments,
839 FND_GLOBAL.user_id,
840 SYSDATE,
841 SYSDATE,
842 FND_GLOBAL.user_id,
843 FND_GLOBAL.user_id
844 );
845 /* dbms_output.put_line('Just After Inserting the data into eam_asset_failure_codes'); */
846
847 ELSIF l_eam_failure_entry_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
848
849 UPDATE eam_asset_failure_codes
850 SET failure_code = l_eam_failure_codes_record.failure_code
851 ,cause_code = l_eam_failure_codes_record.cause_code
852 ,resolution_code = l_eam_failure_codes_record.resolution_code
853 ,combination_id = nvl( l_eam_failure_codes_record.combination_id, combination_id)
854 ,comments = l_eam_failure_codes_record.comments
855 ,last_update_date = SYSDATE
856 ,last_updated_by = FND_GLOBAL.user_id
857 ,last_update_login = FND_GLOBAL.user_id
858 WHERE failure_id = l_eam_failure_codes_record.failure_id
859 AND failure_entry_id = l_eam_failure_codes_record.failure_entry_id;
860
861 END IF;
862 END IF;
863 l_eam_failure_codes_tbl(i) := l_eam_failure_codes_record;
864 l_eam_failure_codes_record := NULL;
865 END LOOP;
866 x_eam_failure_entry_record := l_eam_failure_entry_record;
867 x_eam_failure_codes_tbl := l_eam_failure_codes_tbl;
868
869 EXCEPTION
870 WHEN FND_API.G_EXC_ERROR THEN
871 ROLLBACK TO Process_Failure_Entry_PVT;
872 x_return_status := FND_API.G_RET_STS_ERROR ;
873 /* FND_MSG_PUB.Count_And_Get
874 (
875 p_count => x_msg_count ,
876 p_data => x_msg_data
877 ); */
878 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
879 ROLLBACK TO Process_Failure_Entry_PVT;
880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
881 /* FND_MSG_PUB.Count_And_Get
882 (
883 p_count => x_msg_count,
884 p_data => x_msg_data
885 ); */
886 WHEN OTHERS THEN
887 ROLLBACK TO Process_Failure_Entry_PVT;
888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
889 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
890 THEN
891 FND_MSG_PUB.Add_Exc_Msg
892 (
893 G_PKG_NAME,
894 l_api_name
895 );
896 END IF;
897 /* FND_MSG_PUB.Count_And_Get
898 (
899 p_count => x_msg_count,
900 p_data => x_msg_data
901 ); */
902
903 END process_failure_entry;
904
905 PROCEDURE Delete_Failure_Entry
906 ( p_api_version IN NUMBER := 1.0
907 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
908 , p_commit IN VARCHAR2 := FND_API.G_FALSE
909 , p_source_id IN NUMBER
910 , x_return_status OUT NOCOPY VARCHAR2
911 , x_msg_count OUT NOCOPY NUMBER
912 , x_msg_data OUT NOCOPY VARCHAR2
913 ) IS
914 l_count NUMBER;
915 l_failure_id NUMBER;
916 l_api_version NUMBER := 1.0;
917 l_api_name VARCHAR2(200) := 'Delete_Failure_Entry';
918 BEGIN
919 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(G_PKG_NAME||' Delete_Failure_Entry : Start') ; END IF;
920
921 -- API savepoint
922 SAVEPOINT Delete_Failure_Entry_pvt;
923
924 -- check for call compatibility.
925 IF NOT FND_API.Compatible_API_Call (l_api_version,
926 p_api_version,
927 l_api_name,
928 G_PKG_NAME )
929 THEN
930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
931 END IF;
932
933 -- Initialize message list if p_init_msg_list is set to TRUE.
934 IF FND_API.to_Boolean( p_init_msg_list ) THEN
935 FND_MSG_PUB.initialize;
936 END IF;
937
938 -- Initialize API return status to success
939 x_return_status := FND_API.G_RET_STS_SUCCESS;
940
941 SELECT COUNT(1) INTO l_count FROM eam_asset_failures WHERE source_id = p_source_id;
942
943 IF l_count >=1 THEN
944
945 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(G_PKG_NAME||' Delete_Failure_Entry : Deleting the earlier failure data') ; END IF;
946
947 SELECT failure_id INTO l_failure_id FROM eam_asset_failures WHERE source_id = p_source_id;
948
949 DELETE FROM eam_asset_failure_codes eafc
950 WHERE eafc.failure_id=l_failure_id;
951
952 DELETE FROM eam_asset_failures eaf
953 WHERE eaf.failure_id=l_failure_id;
954 ELSE
955 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(G_PKG_NAME||' Delete_Failure_Entry : Failure information doesn''t exist') ; END IF;
956 END IF;
957
958 IF(p_commit = FND_API.G_TRUE) THEN
959 COMMIT;
960 END IF;
961
962 x_return_status := FND_API.G_RET_STS_SUCCESS;
963
964 EXCEPTION
965 WHEN OTHERS THEN
966 ROLLBACK TO Delete_Failure_Entry_pvt;
967 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(G_PKG_NAME||' Delete_Failure_Entry : Error in deleting the existing failure data:'||SQLERRM) ; END IF;
968 x_return_status := FND_API.G_RET_STS_ERROR;
969
970 END Delete_Failure_Entry;
971
972
973 END Eam_Process_Failure_Entry_PVT;