[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.0 2006/03/10 03:58:21 sshahid noship $ */
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 SELECT COUNT(1)
407 INTO l_valid_failure_code
408 FROM eam_failure_combinations EFC,
409 eam_failure_set_associations EFSA
410 WHERE EFC.failure_code = l_eam_failure_codes_record.failure_code
411 AND EFSA.inventory_item_id = l_inventory_item_id
412 AND EFSA.effective_end_date is null
413 AND EFSA.set_id = EFC.set_id
414 AND sysdate <= ( SELECT min(nvl(EFC2.EFFECTIVE_END_DATE, SYSDATE))
415 FROM EAM_FAILURE_COMBINATIONS EFC2
416 WHERE nvl(EFC2.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
417 AND EFC2.SET_ID = EFSA.SET_ID
418 )
419 AND sysdate <= ( SELECT min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE))
420 FROM EAM_FAILURE_SETS EFS
421 WHERE nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
422 AND EFS.SET_ID = EFSA.SET_ID
423 );
424 IF l_valid_failure_code = 0 THEN
425 -- Failure code is not valid for this maintenance object id
426 -- Not a Valid Failure Code
427 x_reason_failed:='EAM_INVALID_FAILURE_CODE';
428 x_token_name := 'FAILURE_CODE';
429 x_token_value := l_eam_failure_codes_record.failure_code;
430 x_return_status := false;
431 return ;
432 END IF;
433
434 END IF;
435
436 IF ( l_eam_failure_codes_record.cause_code IS NOT NULL
437 AND l_validate_failure_codes
438 ) THEN
439
440 SELECT count(1)
441 INTO l_valid_cause_code
442 FROM eam_failure_combinations EFC,
443 eam_failure_set_associations EFSA
444 WHERE EFC.cause_code = l_eam_failure_codes_record.cause_code
445 AND EFSA.inventory_item_id = l_inventory_item_id
446 AND EFSA.effective_end_date IS NULL
447 AND EFSA.set_id = efc.set_id
448 AND efc.failure_code = l_eam_failure_codes_record.failure_code
449 AND SYSDATE <= ( SELECT min(nvl(EFC2.effective_end_date, sysdate))
450 FROM eam_failure_combinations EFC2
451 WHERE nvl(EFC2.effective_end_date, sysdate) >= sysdate
452 AND EFC2.set_id = EFSA.set_id
453 )
454 AND SYSDATE <= ( SELECT min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE))
455 FROM EAM_FAILURE_SETS EFS
456 WHERE nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
457 AND EFS.SET_ID = EFSA.SET_ID
458 );
459 IF l_valid_cause_code = 0 THEN
460 -- Cause code is not valid for this maintenance object id
461 -- Not a Valid Cause Code
462 x_reason_failed:='EAM_INVALID_CAUSE_CODE';
463 x_token_name := 'CAUSE_CODE';
464 x_token_value := l_eam_failure_codes_record.cause_code;
465 x_return_status := false;
466 return ;
467 END IF;
468
469 END IF;
470
471 IF ( l_eam_failure_codes_record.resolution_code IS NOT NULL
472 AND l_validate_failure_codes
473 ) THEN
474
475 SELECT COUNT(1)
476 INTO l_valid_resolution_code
477 FROM eam_failure_combinations EFC,
478 eam_failure_set_associations EFSA
479 WHERE EFC.resolution_code = l_eam_failure_codes_record.resolution_code
480 AND EFSA.inventory_item_id = l_inventory_item_id
481 AND EFSA.EFFECTIVE_END_DATE IS NULL
482 AND EFSA.set_id = efc.set_id
483 AND EFC.failure_code = l_eam_failure_codes_record.failure_code
484 AND EFC.cause_code = l_eam_failure_codes_record.cause_code
485 AND SYSDATE <= ( SELECT min(nvl(EFC2.effective_end_date, sysdate))
486 FROM eam_failure_combinations EFC2
487 WHERE nvl(EFC2.effective_end_date, sysdate) >= sysdate
488 AND EFC2.set_id = EFSA.set_id
489 )
490 AND SYSDATE <= ( SELECT min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE))
491 FROM EAM_FAILURE_SETS EFS
492 WHERE nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
493 AND EFS.SET_ID = EFSA.SET_ID
494 );
495
496 IF l_valid_resolution_code = 0 THEN
497 -- Resolution code is not valid for this maintenance object id
498 -- Not a Valid Resolution Code
499 x_reason_failed:='EAM_INVALID_RESOLUTION_CODE';
500 x_token_name := 'RESOLUTION_CODE';
501 x_token_value := l_eam_failure_codes_record.resolution_code;
502 x_return_status := false;
503 return ;
504 END IF;
505
506 END IF;
507 l_eam_failure_codes_record := NULL;
508
509 END LOOP;
510 x_return_status := true;
511
512 END;
513
514
515 /**************************************************************************
516 -- Start of comments
517 -- API name : Process_Failure_Entry
518 -- Type : Public.
519 -- Function : Insert/ Update Failure Information corresponding
520 -- to a work order
521 -- Pre-reqs : None.
522 -- Parameters :
523 -- IN : p_api_version IN NUMBER Required
524 -- p_init_msg_list IN VARCHAR2 Optional
525 -- Default = FND_API.G_FALSE
526 -- p_commit IN VARCHAR2 Optional
527 -- Default = FND_API.G_FALSE
528 -- p_eam_failure_entry_record IN
529 -- Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
530 -- p_eam_failure_codes_tbl IN
531 -- Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
532 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
533 -- x_msg_count OUT NOCOPY NUMBER
534 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
535 -- x_eam_failure_entry_record OUT NOCOPY
536 -- Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
537 -- x_eam_failure_codes_tbl OUT NOCOPY
538 -- Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
539 -- Version : Current version 1.0.
540 -- Initial version 1.0
541 -- End of comments
542 ***************************************************************************/
543
544 PROCEDURE Process_Failure_Entry
545 ( p_api_version IN NUMBER := 1.0
546 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
547 , p_commit IN VARCHAR2 := FND_API.G_FALSE
548 , p_eam_failure_entry_record IN Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
549 , p_eam_failure_codes_tbl IN Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
550 , x_return_status OUT NOCOPY VARCHAR2
551 , x_msg_count OUT NOCOPY NUMBER
552 , x_msg_data OUT NOCOPY VARCHAR2
553 , x_eam_failure_entry_record OUT NOCOPY Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
554 , x_eam_failure_codes_tbl OUT NOCOPY Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
555 ) IS
556
557 l_api_name CONSTANT VARCHAR2(30) := 'Process_Failure_Entry';
558 l_api_version CONSTANT NUMBER := 1.0;
559
560 l_eam_failure_entry_record Eam_Process_Failure_Entry_Pub.Eam_Failure_Entry_Record_Typ;
561 l_eam_failure_codes_tbl Eam_Process_Failure_Entry_Pub.Eam_Failure_Codes_Tbl_Typ;
562 l_eam_failure_codes_record Eam_Process_Failure_Entry_Pub.Eam_Failure_Codes_Typ;
563
564 l_eam_asset_failure_codes_rec Eam_Asset_Failure_Codes%ROWTYPE;
565
566 l_failure_id NUMBER;
567
568 l_inventory_item_id NUMBER;
569 l_organization_id NUMBER;
570
571 l_validate_failure_codes BOOLEAN;
572
573 l_return_status BOOLEAN ;
574
575 l_reason_failed VARCHAR2(4000);
576 l_message_name VARCHAR2(20000);
577
578 l_failure_entry_id NUMBER;
579
580 l_combination_id NUMBER;
581
582
583 l_token_name VARCHAR2(30);
584 l_token_value VARCHAR2(100);
585
586 BEGIN
587
588 /* dbms_output.put_line('Start Processing Process_Failure_Entry'); */
589
590 -- API savepoint
591 SAVEPOINT Process_Failure_Entry_PVT;
592
593 -- check for call compatibility.
594 IF NOT FND_API.Compatible_API_Call (l_api_version,
595 p_api_version,
596 l_api_name,
597 G_PKG_NAME )
598 THEN
599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
600 END IF;
601
602 -- Initialize message list if p_init_msg_list is set to TRUE.
603 IF FND_API.to_Boolean( p_init_msg_list ) THEN
604 FND_MSG_PUB.initialize;
605 END IF;
606
607 -- Initialize API return status to success
608 x_return_status := FND_API.G_RET_STS_SUCCESS;
609 l_token_name := NULL;
610 l_token_value := NULL;
611
612 l_eam_failure_entry_record := p_eam_failure_entry_record;
613 l_eam_failure_codes_tbl := p_eam_failure_codes_tbl;
614
615 IF l_eam_failure_entry_record.transaction_type IS NULL THEN
616 --Don't validate the header record
617 NULL;
618 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
619 /* dbms_output.put_line('Befor Validate_failure_entry_record'); */
620 l_return_status := true;
621 validate_failure_entry_record(
622 p_eam_failure_entry_record => l_eam_failure_entry_record
623 , x_reason_failed => l_reason_failed
624 , x_token_name => l_token_name
625 , x_token_value => l_token_value
626 , x_return_status => l_return_status
627 );
628 /* dbms_output.put_line('After Validate_failure_entry_record'); */
629 IF (NOT l_return_status) THEN
630 /* dbms_output.put_line('failure_entry_record is not valid'); */
631 FND_MESSAGE.SET_NAME ('EAM', l_reason_failed);
632 IF ( l_token_name is not null AND l_token_value IS NOT NULL )
633 THEN
634 fnd_message.set_token
635 ( token => l_token_name
636 , value => l_token_value
637 );
638 END IF;
639 FND_MSG_PUB.Add;
640 RAISE FND_API.G_EXC_ERROR;
641 END IF;
642
643 ELSE
644 /* dbms_output.put_line('After Validate_failure_entry_record1'); */
645 --Please Enter a Valid Transaction typ:
646 --1: Failure Information Entry
647 --2: Failure Information Update
648 FND_MESSAGE.SET_NAME ('EAM', 'EAM_FA_INVALID_TXN_TYPE');
649 FND_MSG_PUB.Add;
650 RAISE FND_API.G_EXC_ERROR;
651 /* dbms_output.put_line('After Validate_failure_entry_record2'); */
652
653 END IF;
654
655 /* dbms_output.put_line('Just Before Inserting the data into eam_asset_failures00'); */
656
657 IF l_eam_failure_entry_record.transaction_type IS NOT NULL THEN
658
659 IF l_eam_failure_entry_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_CREATE THEN
660 /* dbms_output.put_line('Just Before Inserting the data into eam_asset_failures0'); */
661 SELECT eam_asset_failures_s.nextval
662 INTO l_failure_id
663 FROM DUAL;
664 l_eam_failure_entry_record.failure_id := l_failure_id;
665 /* dbms_output.put_line('Just Before Inserting the data into eam_asset_failures'); */
666 INSERT INTO eam_asset_failures
667 (
668 FAILURE_ID,
669 FAILURE_DATE,
670 SOURCE_TYPE,
671 SOURCE_ID,
672 OBJECT_TYPE,
673 OBJECT_ID,
674 MAINT_ORGANIZATION_ID,
675 CURRENT_ORGANIZATION_ID,
676 DEPARTMENT_ID,
677 AREA_ID,
678 CREATED_BY,
679 CREATION_DATE,
680 LAST_UPDATE_DATE,
681 LAST_UPDATED_BY,
682 LAST_UPDATE_LOGIN
683 )
684 VALUES
685 (
686 l_eam_failure_entry_record.failure_id,
687 l_eam_failure_entry_record.failure_date,
688 l_eam_failure_entry_record.source_type,
689 l_eam_failure_entry_record.source_id,
690 l_eam_failure_entry_record.object_type,
691 l_eam_failure_entry_record.object_id,
692 l_eam_failure_entry_record.maint_organization_id,
693 l_eam_failure_entry_record.current_organization_id,
694 l_eam_failure_entry_record.department_id,
695 l_eam_failure_entry_record.area_id,
696 FND_GLOBAL.user_id,
697 SYSDATE,
698 SYSDATE,
699 FND_GLOBAL.user_id,
700 FND_GLOBAL.user_id
701 );
702 /* dbms_output.put_line('Just After Inserting the data into eam_asset_failures'); */
703
704 FOR i IN 1..l_eam_failure_codes_tbl.count
705 LOOP
706 IF l_eam_failure_codes_tbl(i).transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_CREATE THEN
707 l_eam_failure_codes_tbl(i).failure_id := l_failure_id;
708 END IF;
709 END LOOP;
710
711 ELSIF l_eam_failure_entry_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
712 /* dbms_output.put_line('Just Before Updating the data into eam_asset_failures'); */
713 UPDATE eam_asset_failures
714 SET failure_date = l_eam_failure_entry_record.failure_date
715 /* ,source_typ = l_eam_failure_entry_record.source_typ
716 ,source_id = l_eam_failure_entry_record.source_id
717 ,object_typ = l_eam_failure_entry_record.object_typ
718 ,object_id = l_eam_failure_entry_record.object_id
719 ,maint_organization_id = l_eam_failure_entry_record.maint_organization_id
720 ,current_organization_id = l_eam_failure_entry_record.current_organization_id */
721 ,department_id = l_eam_failure_entry_record.department_id
722 ,area_id = l_eam_failure_entry_record.area_id
723 ,last_update_date = SYSDATE
724 ,last_updated_by = FND_GLOBAL.user_id
725 ,last_update_login= FND_GLOBAL.user_id
726 WHERE failure_id = l_eam_failure_entry_record.failure_id;
727 /* dbms_output.put_line('Just After Updating the data into eam_asset_failures'); */
728
729 END IF;
730 END IF;
731 /* dbms_output.put_line('Just Before Validating the data into eam_asset_failures_codes'); */
732 --Validate the child records
733 l_return_status := true;
734 validate_failure_codes(
735 p_eam_failure_codes_tbl_typ => l_eam_failure_codes_tbl
736 , x_reason_failed => l_reason_failed
737 , x_token_name => l_token_name
738 , x_token_value => l_token_value
739 , x_return_status => l_return_status
740 );
741 /* dbms_output.put_line('Just After Validating the data into eam_asset_failures_codes'); */
742 IF (NOT l_return_status ) THEN
743 FND_MESSAGE.SET_NAME ('EAM', l_reason_failed);
744 IF ( l_token_name is not null AND l_token_value IS NOT NULL )
745 THEN
746 fnd_message.set_token
747 ( token => l_token_name
748 , value => l_token_value
749 );
750 END IF;
751 FND_MSG_PUB.Add;
752 RAISE FND_API.G_EXC_ERROR;
753 END IF;
754
755 FOR i IN 1..l_eam_failure_codes_tbl.count
756 LOOP
757 l_eam_failure_codes_record := l_eam_failure_codes_tbl(i);
758 IF l_eam_failure_codes_record.transaction_type IS NOT NULL THEN
759
760 l_combination_id := NULL;
761 IF( l_eam_failure_codes_record.failure_code IS NOT NULL
762 AND l_eam_failure_codes_record.cause_code IS NOT NULL
763 AND l_eam_failure_codes_record.resolution_code IS NOT NULL
764 )
765 THEN
766 BEGIN
767
768 SELECT inventory_item_id
769 INTO l_inventory_item_id
770 FROM CSI_ITEM_INSTANCES
771 WHERE instance_id = ( SELECT object_id
772 FROM eam_asset_failures
773 WHERE failure_id = l_eam_failure_codes_record.failure_id
774 );
775 SELECT COMBINATION_ID
776 INTO l_combination_id
777 FROM eam_failure_combinations EFC,
778 eam_failure_set_associations EFSA
779 WHERE EFC.failure_code = l_eam_failure_codes_record.failure_code
780 AND EFC.cause_code = l_eam_failure_codes_record.cause_code
781 AND EFC.resolution_code = l_eam_failure_codes_record.resolution_code
782 AND EFSA.inventory_item_id = l_inventory_item_id
783 AND EFSA.effective_end_date is null
784 AND EFSA.set_id = EFC.set_id
785 AND SYSDATE <= ( SELECT min(nvl(EFC2.effective_end_date, sysdate))
786 FROM eam_failure_combinations EFC2
787 WHERE nvl(EFC2.effective_end_date, sysdate) >= sysdate
788 AND EFC2.set_id = EFSA.set_id
789 )
790 AND SYSDATE <= ( SELECT min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE))
791 FROM EAM_FAILURE_SETS EFS
792 WHERE nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
793 AND EFS.SET_ID = EFSA.SET_ID
794 );
795 EXCEPTION
796 WHEN NO_DATA_FOUND THEN
797 l_combination_id := NULL;
798 END;
799 END IF;
800 l_eam_failure_codes_record.combination_id := l_combination_id;
801
802 IF l_eam_failure_codes_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_CREATE THEN
803
804 /* dbms_output.put_line('Just Before Inserting the data into eam_asset_failure_codes'); */
805
806 SELECT eam_asset_failure_codes_s.nextval
807 INTO l_failure_entry_id
808 FROM DUAL;
809 l_eam_failure_codes_record.failure_entry_id := l_failure_entry_id;
810
811 INSERT INTO eam_asset_failure_codes
812 (
813 failure_id,
814 failure_entry_id,
815 combination_id,
816 failure_code,
817 cause_code,
818 resolution_code,
819 comments,
820 created_by,
821 creation_date,
822 last_update_date,
823 last_updated_by,
824 last_update_login
825 )
826 VALUES
827 (
828 l_eam_failure_codes_record.failure_id,
829 l_eam_failure_codes_record.failure_entry_id,
830 l_eam_failure_codes_record.combination_id,
831 l_eam_failure_codes_record.failure_code,
832 l_eam_failure_codes_record.cause_code,
833 l_eam_failure_codes_record.resolution_code,
834 l_eam_failure_codes_record.comments,
835 FND_GLOBAL.user_id,
836 SYSDATE,
837 SYSDATE,
838 FND_GLOBAL.user_id,
839 FND_GLOBAL.user_id
840 );
841 /* dbms_output.put_line('Just After Inserting the data into eam_asset_failure_codes'); */
842
843 ELSIF l_eam_failure_entry_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
844
845 UPDATE eam_asset_failure_codes
846 SET failure_code = l_eam_failure_codes_record.failure_code
847 ,cause_code = l_eam_failure_codes_record.cause_code
848 ,resolution_code = l_eam_failure_codes_record.resolution_code
849 ,combination_id = nvl( l_eam_failure_codes_record.combination_id, combination_id)
850 ,comments = l_eam_failure_codes_record.comments
851 ,last_update_date = SYSDATE
852 ,last_updated_by = FND_GLOBAL.user_id
853 ,last_update_login = FND_GLOBAL.user_id
854 WHERE failure_id = l_eam_failure_codes_record.failure_id
855 AND failure_entry_id = l_eam_failure_codes_record.failure_entry_id;
856
857 END IF;
858 END IF;
859 l_eam_failure_codes_tbl(i) := l_eam_failure_codes_record;
860 l_eam_failure_codes_record := NULL;
861 END LOOP;
862 x_eam_failure_entry_record := l_eam_failure_entry_record;
863 x_eam_failure_codes_tbl := l_eam_failure_codes_tbl;
864
865 EXCEPTION
866 WHEN FND_API.G_EXC_ERROR THEN
867 ROLLBACK TO Process_Failure_Entry_PVT;
868 x_return_status := FND_API.G_RET_STS_ERROR ;
869 /* FND_MSG_PUB.Count_And_Get
870 (
871 p_count => x_msg_count ,
872 p_data => x_msg_data
873 ); */
874 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
875 ROLLBACK TO Process_Failure_Entry_PVT;
876 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
877 /* FND_MSG_PUB.Count_And_Get
878 (
879 p_count => x_msg_count,
880 p_data => x_msg_data
881 ); */
882 WHEN OTHERS THEN
883 ROLLBACK TO Process_Failure_Entry_PVT;
884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
885 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
886 THEN
887 FND_MSG_PUB.Add_Exc_Msg
888 (
889 G_PKG_NAME,
890 l_api_name
891 );
892 END IF;
893 /* FND_MSG_PUB.Count_And_Get
894 (
895 p_count => x_msg_count,
896 p_data => x_msg_data
897 ); */
898
899 END process_failure_entry;
900
901 END Eam_Process_Failure_Entry_PVT;