[Home] [Help]
PACKAGE BODY: APPS.EAM_PROCESS_FAILURE_ENTRY_PUB
Source
1 PACKAGE BODY EAM_Process_Failure_Entry_PUB AS
2 /* $Header: EAMPFENB.pls 120.1.12010000.2 2009/04/20 05:43:51 vchidura ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_Process_Failure_Entry_PUB';
4
5 /**************************************************************************
6 -- Start of comments
7 -- API name : Process_Failure_Entry
8 -- Type : Public.
9 -- Function : Insert/ Update Failure Information corresponding
10 -- to a work order
11 -- Pre-reqs : None.
12 -- Parameters :
13 -- IN : p_api_version IN NUMBER Required
14 -- p_init_msg_list IN VARCHAR2 Optional
15 -- Default = FND_API.G_FALSE
16 -- p_commit IN VARCHAR2 Optional
17 -- Default = FND_API.G_FALSE
18 -- p_eam_failure_entry_record IN
19 -- Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
20 -- p_eam_failure_codes_tbl IN
21 -- Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
22 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
23 -- x_msg_count OUT NOCOPY NUMBER
24 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
25 -- x_eam_failure_entry_record OUT NOCOPY
26 -- Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
27 -- x_eam_failure_codes_tbl OUT NOCOPY
28 -- Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
29 -- Version : Current version 1.0.
30 -- Initial version 1.0
31 -- End of comments
32 ***************************************************************************/
33
34 PROCEDURE Process_Failure_Entry
35 ( p_api_version IN NUMBER := 1.0
36 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
37 , p_commit IN VARCHAR2 := FND_API.G_FALSE
38 , p_eam_failure_entry_record IN Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
39 , p_eam_failure_codes_tbl IN Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
40 , x_return_status OUT NOCOPY VARCHAR2
41 , x_msg_count OUT NOCOPY NUMBER
42 , x_msg_data OUT NOCOPY VARCHAR2
43 , x_eam_failure_entry_record OUT NOCOPY Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ
44 , x_eam_failure_codes_tbl OUT NOCOPY Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ
45 ) IS
46
47 l_api_name CONSTANT VARCHAR2(30) := 'Process_Failure_Entry';
48 l_api_version CONSTANT NUMBER := 1.0;
49
50 l_eam_failure_entry_record Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ;
51 l_eam_failure_codes_tbl Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ;
52
53 l_out_msg_count NUMBER ;
54 l_out_msg_data VARCHAR2(4000);
55 l_out_eam_failure_entry_record Eam_Process_Failure_Entry_PUB.eam_failure_entry_record_typ;
56 l_out_eam_failure_codes_tbl Eam_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ;
57
58
59 l_object_type NUMBER;
60 l_object_id NUMBER;
61 l_source_id NUMBER;
62 l_source_type NUMBER;
63 l_failure_date DATE;
64 l_maint_organization_id NUMBER;
65 l_current_organization_id NUMBER;
66 l_area_id NUMBER;
67
68 l_department_id NUMBER;
69 l_organization_id NUMBER;
70
71 l_msn_department_id NUMBER;
72 l_eam_location_id NUMBER;
73
74 l_failure_code VARCHAR2(80);
75 l_cause_code VARCHAR2(80);
76 l_resolution_code VARCHAR2(80);
77 l_comments VARCHAR2(2000);
78
79 l_date_completed DATE;
80 l_failure_code_required VARCHAR2(1);
81
82 BEGIN
83
84 /* We Need to Validate the Client Side Validations Here in the Public API.
85 * 1. Failure Codes has to be Mandatory Entered for the 'Completed Work Order'
86 * if the Failure Code Required is YES
87 * 2.
88 */
89
90 -- API savepoint
91 SAVEPOINT Process_Failure_Entry_PUB;
92
93 -- check for call compatibility.
94 IF NOT FND_API.Compatible_API_Call (l_api_version,
95 p_api_version,
96 l_api_name,
97 G_PKG_NAME )
98 THEN
99 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
100 END IF;
101
102 -- Initialize message list if p_init_msg_list is set to TRUE.
103 IF FND_API.to_Boolean( p_init_msg_list ) THEN
104 FND_MSG_PUB.initialize;
105 END IF;
106
107 -- Initialize API return status to success
108 x_return_status := FND_API.G_RET_STS_SUCCESS;
109
110 l_eam_failure_entry_record := p_eam_failure_entry_record;
111 l_eam_failure_codes_tbl := p_eam_failure_codes_tbl;
112
113 /*******************************
114 Following Validations Are for 11510 Design.
115 ********************************/
116 IF (( l_eam_failure_entry_record.transaction_type <> Eam_Process_Failure_Entry_PUB.G_FE_CREATE
117 AND l_eam_failure_entry_record.transaction_type <> Eam_Process_Failure_Entry_PUB.G_FE_UPDATE)
118 OR l_eam_failure_entry_record.transaction_type IS NULL
119 )
120 THEN
121 /* Invalid Transaction Type */
122 FND_MESSAGE.SET_NAME ('EAM', 'EAM_FA_INVALID_TXN_TYPE');
123 FND_MSG_PUB.Add;
124 RAISE FND_API.G_EXC_ERROR;
125 ELSIF l_eam_failure_codes_tbl.count > 1
126 THEN
127 FND_MESSAGE.SET_NAME ('EAM', 'EAM_MULTIPLE_CHILD');
128 FND_MSG_PUB.Add;
129 RAISE FND_API.G_EXC_ERROR;
130 ELSIF ( l_eam_failure_codes_tbl.count = 1
131 AND l_eam_failure_codes_tbl(1).transaction_type <> Eam_Process_Failure_Entry_PUB.G_FE_CREATE
132 AND l_eam_failure_codes_tbl(1).transaction_type <> Eam_Process_Failure_Entry_PUB.G_FE_UPDATE
133 )
134 THEN
135 FND_MESSAGE.SET_NAME ('EAM', 'EAM_FA_INVALID_TXN_TYPE');
136 FND_MSG_PUB.Add;
137 RAISE FND_API.G_EXC_ERROR;
138 ELSIF ( l_eam_failure_codes_tbl.count = 1
139 AND l_eam_failure_codes_tbl(1).failure_id IS NOT NULL
140 AND l_eam_failure_codes_tbl(1).failure_id <> l_eam_failure_entry_record.failure_id
141 )
142 THEN
143 FND_MESSAGE.SET_NAME ('EAM', 'EAM_CHILD_NOT_SYNC');
144 FND_MSG_PUB.Add;
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147
148
149 IF l_eam_failure_entry_record.transaction_type = EAM_Process_Failure_Entry_PUB.G_FE_CREATE THEN
150
151 IF l_eam_failure_entry_record.source_type = 1
152 THEN
153
154 IF l_eam_failure_entry_record.source_id IS NULL THEN
155 BEGIN
156 SELECT wip_entity_id
157 INTO l_eam_failure_entry_record.source_id
158 FROM wip_entities
159 WHERE wip_entity_name = l_eam_failure_entry_record.source_name
160 AND organization_id = l_eam_failure_entry_record.maint_organization_id
161 AND entity_type in (6,7);
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_WIP_ENTITY_ID');
165 FND_MESSAGE.SET_TOKEN( token => 'SOURCE_ID'
166 , value => l_eam_failure_entry_record.source_name
167 );
168 FND_MSG_PUB.Add;
169 RAISE FND_API.G_EXC_ERROR;
170 END;
171 END IF;
172
173 BEGIN
174 SELECT maintenance_object_id, maintenance_object_type, owning_department, organization_id
175 INTO l_object_id , l_object_type , l_department_id , l_organization_id
176 FROM wip_discrete_jobs
177 WHERE wip_entity_id = l_eam_failure_entry_record.source_id;
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_WIP_ENTITY_ID');
181 fnd_message.set_token( token => 'SOURCE_ID'
182 , value => l_eam_failure_entry_record.source_id
183 );
184 FND_MSG_PUB.Add;
185 RAISE FND_API.G_EXC_ERROR;
186 END;
187
188 IF( l_eam_failure_entry_record.object_type IS NOT NULL
189 AND ( ( l_object_type IS NULL AND l_eam_failure_entry_record.object_type IS NOT NULL)
190 OR ( l_object_type <> l_eam_failure_entry_record.object_type)
191 )
192 )
193 THEN
194 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_OBJECT_TYPE');
195 FND_MESSAGE.SET_TOKEN( token => 'OBJECT_TYPE'
196 , value => l_eam_failure_entry_record.object_type
197 );
198 FND_MSG_PUB.Add;
199 RAISE FND_API.G_EXC_ERROR;
200 ELSE
201 l_eam_failure_entry_record.object_type := l_object_type;
202 END IF;
203
204 IF( l_eam_failure_entry_record.object_id IS NOT NULL
205 AND ( (l_object_id IS NULL AND l_eam_failure_entry_record.object_id IS NOT NULL)
206 OR (l_object_id <> l_eam_failure_entry_record.object_id)
207 )
208 )
209 THEN
210 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_OBJECT_ID');
211 FND_MESSAGE.SET_TOKEN( token => 'ASSET_NUMBER'
212 , value => l_eam_failure_entry_record.object_id
213 );
214 FND_MSG_PUB.Add;
215 RAISE FND_API.G_EXC_ERROR;
216 ELSE
217 l_eam_failure_entry_record.object_id := l_object_id;
218 END IF;
219
220 IF( l_eam_failure_entry_record.maint_organization_id IS NOT NULL
221 AND ( (l_organization_id IS NULL AND l_eam_failure_entry_record.maint_organization_id IS NOT NULL)
222 OR (l_organization_id <> l_eam_failure_entry_record.maint_organization_id)
223 )
224 )
225 THEN
226 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_MAINT_ORG');
227 FND_MESSAGE.SET_TOKEN( token => 'MAINT_ORG_ID'
228 , value => l_eam_failure_entry_record.maint_organization_id
229 );
230 FND_MSG_PUB.Add;
231 RAISE FND_API.G_EXC_ERROR;
232 ELSE
233 l_eam_failure_entry_record.maint_organization_id := l_organization_id;
234 END IF;
235
236 IF( l_eam_failure_entry_record.current_organization_id IS NOT NULL
237 AND ( (l_organization_id IS NULL AND l_eam_failure_entry_record.current_organization_id IS NOT NULL)
238 OR (l_organization_id <> l_eam_failure_entry_record.current_organization_id)
239 )
240 )
241 THEN
242 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_CURRENT_ORG');
243 FND_MESSAGE.SET_TOKEN( token => 'CURR_ORG_ID'
244 , value => l_eam_failure_entry_record.current_organization_id
245 );
246 FND_MSG_PUB.Add;
247 RAISE FND_API.G_EXC_ERROR;
248 ELSE
249 l_eam_failure_entry_record.current_organization_id := l_organization_id;
250 END IF;
251
252 BEGIN
253
254 SELECT eomd.owning_department_id,eomd.area_id
255 INTO l_msn_department_id, l_eam_location_id
256 FROM csi_item_instances cii,
257 eam_org_maint_defaults eomd,mtl_parameters mp
258 WHERE cii.instance_id = l_object_id
259 AND cii.instance_id = eomd.object_id(+)
260 AND eomd.object_type(+)= 50
261 AND cii.last_vld_organization_id = mp.organization_id
262 AND ( eomd.organization_id IS NULL OR
263 mp.maint_organization_id = eomd.organization_id);
264
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 l_msn_department_id := NULL;
268 l_eam_location_id := NULL;
269 WHEN OTHERS THEN
270 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_OBJECT_ID');
271 FND_MESSAGE.SET_TOKEN( token => 'ASSET_NUMBER'
272 ,value => l_eam_failure_entry_record.object_id
273 );
274 FND_MSG_PUB.Add;
275 RAISE FND_API.G_EXC_ERROR;
276 END;
277
278 IF (l_department_id IS NULL) THEN
279 l_department_id := l_msn_department_id;
280 END IF;
281
282 IF( l_eam_failure_entry_record.department_id IS NOT NULL
283 AND ( (l_department_id IS NULL AND l_eam_failure_entry_record.department_id IS NOT NULL)
284 OR l_department_id <> l_eam_failure_entry_record.department_id
285 )
286 )
287 THEN
288 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_DEPARTMENT');
289 FND_MESSAGE.SET_TOKEN( token => 'DEPARTMENT'
290 ,value => l_eam_failure_entry_record.department_id
291 );
292 FND_MSG_PUB.Add;
293 RAISE FND_API.G_EXC_ERROR;
294 ELSE
295 l_eam_failure_entry_record.department_id := l_department_id;
296 END IF;
297
298 IF( l_eam_failure_entry_record.area_id IS NOT NULL
299 AND ( (l_eam_location_id IS NULL AND l_eam_failure_entry_record.area_id IS NOT NULL)
300 OR (l_eam_location_id <> l_eam_failure_entry_record.area_id)
301 )
302 )
303 THEN
304 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_AREA');
305 FND_MESSAGE.SET_TOKEN( token => 'AREA'
306 ,value => l_eam_failure_entry_record.area_id
307 );
308 FND_MSG_PUB.Add;
309 RAISE FND_API.G_EXC_ERROR;
310 ELSE
311 l_eam_failure_entry_record.area_id := l_eam_location_id;
312 END IF;
313
314 END IF;
315
316 ELSIF l_eam_failure_entry_record.transaction_type = EAM_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
317
318 IF l_eam_failure_entry_record.failure_id IS NULL THEN
319
320 IF ( l_eam_failure_entry_record.source_type = 1
321 AND ( l_eam_failure_entry_record.source_id IS NOT NULL
322 OR ( l_eam_failure_entry_record.source_name IS NOT NULL
323 AND l_eam_failure_entry_record.maint_organization_id IS NOT NULL
324 )
325 )
326 )
327 THEN
328 BEGIN
329 IF l_eam_failure_entry_record.source_id IS NOT NULL THEN
330 SELECT failure_id
331 INTO l_eam_failure_entry_record.failure_id
332 FROM eam_asset_failures
333 WHERE source_id = l_eam_failure_entry_record.source_id
334 AND source_type = 1;
335 ELSE
336 SELECT failure_id
337 INTO l_eam_failure_entry_record.failure_id
338 FROM eam_asset_failures
339 WHERE source_id = ( SELECT wip_entity_id
340 FROM WIP_ENTITIES
341 WHERE WIP_ENTITY_NAME = l_eam_failure_entry_record.source_name
342 AND organization_id = l_eam_failure_entry_record.maint_organization_id
343 )
344 AND source_type = 1;
345 END IF;
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN
348 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_WIP_ENTITY_ID');
349 FND_MESSAGE.SET_TOKEN( token => 'SOURCE_ID'
350 , value => l_eam_failure_entry_record.source_id||l_eam_failure_entry_record.source_name
351 );
352 FND_MSG_PUB.Add;
353 RAISE FND_API.G_EXC_ERROR;
354 END;
355 ELSE
356 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_FAILURE_ID');
357 FND_MSG_PUB.Add;
358 RAISE FND_API.G_EXC_ERROR;
359 END IF;
360 END IF;
361
362
363 BEGIN
364 SELECT object_type , object_id , source_id , source_type , failure_date , maint_organization_id , current_organization_id , area_id
365 INTO l_object_type, l_object_id , l_source_id, l_source_type, l_failure_date, l_maint_organization_id, l_current_organization_id, l_area_id
366 FROM eam_asset_failures
367 WHERE failure_id = l_eam_failure_entry_record.failure_id;
368 l_eam_failure_entry_record.source_id := l_source_id;
372 FND_MESSAGE.SET_TOKEN( token => 'SOURCE_ID'
369 EXCEPTION
370 WHEN NO_DATA_FOUND THEN
371 FND_MESSAGE.SET_NAME ('EAM', 'EAM_FAILURE_NOT_EXISTS');
373 , value => 'Failure Id :'||l_eam_failure_entry_record.failure_id
374 );
375 FND_MSG_PUB.Add;
376 RAISE FND_API.G_EXC_ERROR;
377 END;
378
379 IF l_source_type = 1
380 THEN
381 /***************************************************************
382 Don't Validate the drived columns.
383 IF( ( l_eam_failure_entry_record.object_type IS NOT NULL
384 AND l_eam_failure_entry_record.object_type <> FND_API.G_MISS_NUM
385 AND ( l_object_type IS NULL
386 OR l_object_type <> l_eam_failure_entry_record.object_type
387 )
388 )
389 OR
390 ( l_eam_failure_entry_record.object_type = FND_API.G_MISS_NUM
391 AND l_object_type IS NOT NULL
392 )
393 )
394 THEN
395 FND_MESSAGE.SET_NAME ('EAM', 'EAM_CANNOT_UPDATE');
396 FND_MESSAGE.SET_TOKEN( token => 'ATTRIBUTE'
397 , value => 'OBJECT_TYPE'
398 );
399 FND_MSG_PUB.Add;
400 RAISE FND_API.G_EXC_ERROR;
401 END IF;
402
403 IF( ( l_eam_failure_entry_record.object_id IS NOT NULL
404 AND l_eam_failure_entry_record.object_id <> FND_API.G_MISS_NUM
405 AND ( l_object_id IS NULL
406 OR l_object_id <> l_eam_failure_entry_record.object_id
407 )
408 )
409 OR
410 ( l_eam_failure_entry_record.object_id = FND_API.G_MISS_NUM
411 AND l_object_id IS NOT NULL
412 )
413 )
414 THEN
415 FND_MESSAGE.SET_NAME ('EAM', 'EAM_CANNOT_UPDATE');
416 FND_MESSAGE.SET_TOKEN( token => 'ATTRIBUTE'
417 , value => 'OBJECT_ID'
418 );
419 FND_MSG_PUB.Add;
420 RAISE FND_API.G_EXC_ERROR;
421 END IF;
422
423 IF( ( l_eam_failure_entry_record.source_type IS NOT NULL
424 AND l_eam_failure_entry_record.source_type <> FND_API.G_MISS_NUM
425 AND ( l_source_type IS NULL
426 OR l_source_type <> l_eam_failure_entry_record.source_type
427 )
428 )
429 OR
430 ( l_eam_failure_entry_record.source_type = FND_API.G_MISS_NUM
431 AND l_source_type IS NOT NULL
432 )
433 )
434 THEN
435 FND_MESSAGE.SET_NAME ('EAM', 'EAM_CANNOT_UPDATE');
436 FND_MESSAGE.SET_TOKEN( token => 'ATTRIBUTE'
437 , value => 'SOURCE_TYPE'
438 );
439 FND_MSG_PUB.Add;
440 RAISE FND_API.G_EXC_ERROR;
441 END IF;
442
443 IF( ( l_eam_failure_entry_record.source_id IS NOT NULL
444 AND l_eam_failure_entry_record.source_id <> FND_API.G_MISS_NUM
445 AND ( l_source_id IS NULL
446 OR l_source_id <> l_eam_failure_entry_record.source_id
447 )
448 )
449 OR
450 ( l_eam_failure_entry_record.source_id = FND_API.G_MISS_NUM
451 AND l_source_id IS NOT NULL
452 )
453 )
454 THEN
455 FND_MESSAGE.SET_NAME ('EAM', 'EAM_CANNOT_UPDATE');
456 FND_MESSAGE.SET_TOKEN( token => 'ATTRIBUTE'
457 , value => 'SOURCE_ID'
458 );
459 FND_MSG_PUB.Add;
460 RAISE FND_API.G_EXC_ERROR;
461 ELSE
462 l_eam_failure_entry_record.source_id := l_source_id;
463 END IF;
464 ***************************************************************/
465
466 BEGIN
467 SELECT owning_department , organization_id
468 INTO l_department_id , l_organization_id
469 FROM WIP_DISCRETE_JOBS
470 WHERE wip_entity_id = l_source_id;
471 EXCEPTION
472 WHEN NO_DATA_FOUND THEN
473 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_WIP_ENTITY_ID');
474 FND_MESSAGE.SET_TOKEN( token => 'SOURCE_ID'
475 , value => l_eam_failure_entry_record.source_id
476 );
477 FND_MSG_PUB.Add;
478 RAISE FND_API.G_EXC_ERROR;
479 END;
480
481 IF( ( l_eam_failure_entry_record.maint_organization_id IS NOT NULL
482 AND l_eam_failure_entry_record.maint_organization_id <> FND_API.G_MISS_NUM
483 AND ( l_organization_id IS NULL
484 OR l_organization_id <> l_eam_failure_entry_record.maint_organization_id
485 )
486 )
487 OR
488 ( l_eam_failure_entry_record.maint_organization_id = FND_API.G_MISS_NUM
489 AND l_organization_id IS NOT NULL
490 )
491 )
492 THEN
493 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_MAINT_ORG');
494 FND_MESSAGE.SET_TOKEN( token => 'MAINT_ORG_ID'
495 , value => l_eam_failure_entry_record.maint_organization_id
496 );
497 FND_MSG_PUB.Add;
498 RAISE FND_API.G_EXC_ERROR;
499 ELSE
500 l_eam_failure_entry_record.maint_organization_id := l_organization_id;
501 END IF;
502
506 OR l_current_organization_id <> l_eam_failure_entry_record.current_organization_id
503 IF( ( l_eam_failure_entry_record.current_organization_id IS NOT NULL
504 AND l_eam_failure_entry_record.current_organization_id <> FND_API.G_MISS_NUM
505 AND ( l_current_organization_id IS NULL
507 )
508 )
509 OR
510 ( l_eam_failure_entry_record.current_organization_id = FND_API.G_MISS_NUM
511 AND l_current_organization_id IS NOT NULL
512 )
513 )
514 THEN
515 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_CURRENT_ORG');
516 FND_MESSAGE.SET_TOKEN( token => 'CURR_ORG_ID'
517 , value => l_eam_failure_entry_record.current_organization_id
518 );
519 FND_MSG_PUB.Add;
520 RAISE FND_API.G_EXC_ERROR;
521 ELSE
522 l_eam_failure_entry_record.current_organization_id := l_current_organization_id;
523 END IF;
524
525 BEGIN
526
527 SELECT eomd.owning_department_id,eomd.area_id
528 INTO l_msn_department_id, l_eam_location_id
529 FROM csi_item_instances cii,
530 eam_org_maint_defaults eomd,mtl_parameters mp
531 WHERE cii.instance_id = l_object_id
532 AND cii.instance_id = eomd.object_id(+)
533 AND eomd.object_type(+)= 50
534 AND cii.last_vld_organization_id = mp.organization_id
535 AND ( eomd.organization_id IS NULL OR
536 mp.maint_organization_id = eomd.organization_id);
537
538
539 EXCEPTION
540 WHEN NO_DATA_FOUND THEN
541 l_msn_department_id := NULL;
542 l_eam_location_id := NULL;
543 WHEN OTHERS THEN
544 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_OBJECT_ID');
545 FND_MESSAGE.SET_TOKEN( token => 'ASSET_NUMBER'
546 ,value => l_object_id
547 );
548 FND_MSG_PUB.Add;
549 RAISE FND_API.G_EXC_ERROR;
550 END;
551
552 IF (l_department_id IS NULL) THEN
553 l_department_id := l_msn_department_id;
554 END IF;
555
556
557 IF( ( l_eam_failure_entry_record.department_id IS NOT NULL
558 AND l_eam_failure_entry_record.department_id <> FND_API.G_MISS_NUM
559 AND ( l_department_id IS NULL
560 OR l_department_id <> l_eam_failure_entry_record.department_id
561 )
562 )
563 OR
564 ( l_eam_failure_entry_record.department_id = FND_API.G_MISS_NUM
565 AND l_department_id IS NOT NULL
566 )
567 )
568 THEN
569 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_DEPARTMENT');
570 FND_MESSAGE.SET_TOKEN( token => 'DEPARTMENT'
571 ,value => l_eam_failure_entry_record.department_id
572 );
573 FND_MSG_PUB.Add;
574 RAISE FND_API.G_EXC_ERROR;
575 ELSE
576 l_eam_failure_entry_record.department_id := l_department_id;
577 END IF;
578
579
580 IF( ( l_eam_failure_entry_record.area_id IS NOT NULL
581 AND l_eam_failure_entry_record.area_id <> FND_API.G_MISS_NUM
582 AND ( l_eam_location_id IS NULL
583 OR l_eam_location_id <> l_eam_failure_entry_record.area_id
584 )
585 )
586 OR
587 ( l_eam_failure_entry_record.area_id = FND_API.G_MISS_NUM
588 AND l_eam_location_id IS NOT NULL
589 )
590 )
591 THEN
592 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_AREA');
593 FND_MESSAGE.SET_TOKEN( token => 'AREA'
594 ,value => l_eam_failure_entry_record.area_id
595 );
596 FND_MSG_PUB.Add;
597 RAISE FND_API.G_EXC_ERROR;
598 ELSE
599 l_eam_failure_entry_record.area_id := l_eam_location_id;
600 END IF;
601
602 ELSE
603 IF l_eam_failure_entry_record.maint_organization_id = FND_API.G_MISS_NUM THEN
604 l_eam_failure_entry_record.maint_organization_id := NULL;
605 ELSIF l_eam_failure_entry_record.maint_organization_id IS NULL THEN
606 l_eam_failure_entry_record.maint_organization_id := l_maint_organization_id;
607 END IF;
608
609 IF l_eam_failure_entry_record.current_organization_id = FND_API.G_MISS_NUM THEN
610 l_eam_failure_entry_record.current_organization_id := NULL;
611 ELSIF l_eam_failure_entry_record.current_organization_id IS NULL THEN
612 l_eam_failure_entry_record.current_organization_id := l_current_organization_id;
613 END IF;
614
615 IF l_eam_failure_entry_record.department_id = FND_API.G_MISS_NUM THEN
616 l_eam_failure_entry_record.department_id := NULL;
617 ELSIF l_eam_failure_entry_record.department_id IS NULL THEN
618 l_eam_failure_entry_record.department_id := l_department_id;
619 END IF;
620
621 IF l_eam_failure_entry_record.area_id = FND_API.G_MISS_NUM THEN
622 l_eam_failure_entry_record.area_id := NULL;
623 ELSIF l_eam_failure_entry_record.area_id IS NULL THEN
624 l_eam_failure_entry_record.area_id := l_maint_organization_id;
625 END IF;
626
627 END IF;
628
629 IF l_eam_failure_entry_record.failure_date = FND_API.G_MISS_DATE THEN
630 l_eam_failure_entry_record.failure_date := NULL;
631 ELSIF l_eam_failure_entry_record.failure_date IS NULL THEN
632 l_eam_failure_entry_record.failure_date := l_failure_date;
633 END IF;
634 END IF;
638
635
636 FOR i in 1..l_eam_failure_codes_tbl.count
637 LOOP
639 IF l_eam_failure_codes_tbl(i).transaction_type = EAM_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
640
641 l_eam_failure_codes_tbl(i).failure_id := l_eam_failure_entry_record.failure_id;
642
643 IF l_eam_failure_codes_tbl(i).failure_entry_id IS NULL
644 THEN
645
646 BEGIN
647 SELECT failure_entry_id , failure_code, cause_code, resolution_code, comments
648 INTO l_eam_failure_codes_tbl(i).failure_entry_id, l_failure_code, l_cause_code, l_resolution_code, l_comments
649 FROM eam_asset_failure_codes
650 WHERE failure_id = l_eam_failure_codes_tbl(i).failure_id;
651 EXCEPTION
652 WHEN NO_DATA_FOUND THEN
653 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_FAILURE');
654 FND_MESSAGE.SET_TOKEN( token => 'SOURCE_ID'
655 , value => l_eam_failure_codes_tbl(i).failure_id
656 );
657 FND_MSG_PUB.Add;
658 RAISE FND_API.G_EXC_ERROR;
659 END;
660
661 ELSE
662
663 BEGIN
664 SELECT failure_id , failure_code, cause_code, resolution_code, comments
665 INTO l_eam_failure_codes_tbl(i).failure_id, l_failure_code, l_cause_code, l_resolution_code, l_comments
666 FROM eam_asset_failure_codes
667 WHERE failure_entry_id = l_eam_failure_codes_tbl(i).failure_entry_id;
668 EXCEPTION
669 WHEN NO_DATA_FOUND THEN
670 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_FAILURE_ENTRY_ID');
671 FND_MSG_PUB.Add;
672 RAISE FND_API.G_EXC_ERROR;
673 END;
674
675 IF l_eam_failure_codes_tbl(i).failure_id <> l_eam_failure_entry_record.failure_id
676 THEN
677 FND_MESSAGE.SET_NAME ('EAM', 'EAM_CHILD_NOT_SYNC');
678 FND_MSG_PUB.Add;
679 RAISE FND_API.G_EXC_ERROR;
680 END IF;
681
682 END IF;
683
684 IF l_eam_failure_codes_tbl(i).failure_code = FND_API.G_MISS_CHAR THEN
685 l_eam_failure_codes_tbl(i).failure_code := NULL;
686 ELSIF l_eam_failure_codes_tbl(i).failure_code IS NULL THEN
687 l_eam_failure_codes_tbl(i).failure_code := l_failure_code;
688 END IF;
689
690 IF l_eam_failure_codes_tbl(i).cause_code = FND_API.G_MISS_CHAR THEN
691 l_eam_failure_codes_tbl(i).cause_code := NULL;
692 ELSIF l_eam_failure_codes_tbl(i).cause_code IS NULL THEN
693 l_eam_failure_codes_tbl(i).cause_code := l_cause_code;
694 END IF;
695
696 IF l_eam_failure_codes_tbl(i).resolution_code = FND_API.G_MISS_CHAR THEN
697 l_eam_failure_codes_tbl(i).resolution_code := NULL;
698 ELSIF l_eam_failure_codes_tbl(i).resolution_code IS NULL THEN
699 l_eam_failure_codes_tbl(i).resolution_code := l_resolution_code;
700 END IF;
701
702 IF l_eam_failure_codes_tbl(i).comments = FND_API.G_MISS_CHAR THEN
703 l_eam_failure_codes_tbl(i).comments := NULL;
704 ELSIF l_eam_failure_codes_tbl(i).comments IS NULL THEN
705 l_eam_failure_codes_tbl(i).comments := l_comments;
706 END IF;
707
708 ELSIF l_eam_failure_codes_tbl(i).transaction_type = EAM_Process_Failure_Entry_PUB.G_FE_CREATE THEN
709
710 l_eam_failure_codes_tbl(i).failure_id := l_eam_failure_entry_record.failure_id;
711
712 END IF;
713
714 END LOOP;
715
716 /* Client Side Validation
717 * For the Completed Work Orders, Will do the validation
718 * after calling the API. Since the Master Data has to
719 * be validated against the child data.
720 */
721 IF l_eam_failure_entry_record.source_type = 1
722 THEN
723
724 BEGIN
725 SELECT wdj.date_completed, nvl(edw.failure_code_required, 'N')
726 INTO l_date_completed , l_failure_code_required
727 FROM wip_discrete_jobs wdj, eam_work_order_details edw
728 WHERE wdj.wip_entity_id = edw.wip_entity_id
729 AND wdj.wip_entity_id = l_eam_failure_entry_record.source_id;
730 EXCEPTION
731 WHEN NO_DATA_FOUND THEN
732 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_WIP_ENTITY_ID');
733 FND_MESSAGE.SET_TOKEN( token => 'SOURCE_ID'
734 , value => l_eam_failure_entry_record.source_name
735 );
736 FND_MSG_PUB.Add;
737 RAISE FND_API.G_EXC_ERROR;
738 END;
739
740 /********************************
741 Following Validations for Complete Work Order are as per the 11510 design.
742 ********************************/
743 l_failure_date := l_eam_failure_entry_record.failure_date;
744 IF l_date_completed IS NOT NULL THEN
745 /* Completed Work Order */
746 IF l_eam_failure_codes_tbl.count = 1
747 THEN
748 l_failure_code := l_eam_failure_codes_tbl(1).failure_code;
749 l_cause_code := l_eam_failure_codes_tbl(1).cause_code;
750 l_resolution_code := l_eam_failure_codes_tbl(1).resolution_code;
751 l_comments := l_eam_failure_codes_tbl(1).comments;
752 ELSE
753 BEGIN
754 SELECT failure_code , cause_code , resolution_code , comments
755 INTO l_failure_code, l_cause_code, l_resolution_code, l_comments
756 FROM eam_asset_failure_codes
757 WHERE failure_id = l_eam_failure_entry_record.failure_id;
758 EXCEPTION
759 WHEN NO_DATA_FOUND THEN
760 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
761 FND_MSG_PUB.Add;
762 RAISE FND_API.G_EXC_ERROR;
763 END;
764 END IF;
765
766 IF l_failure_code_required = 'Y'
767 THEN
768 IF NOT( l_failure_date IS NOT NULL
769 AND l_failure_code IS NOT NULL
770 AND l_cause_code IS NOT NULL
771 AND l_resolution_code IS NOT NULL
772 )
773 THEN
774 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
775 FND_MSG_PUB.Add;
776 RAISE FND_API.G_EXC_ERROR;
777 END IF;
778 ELSE
779
780 IF l_comments IS NOT NULL THEN
781 IF ( l_failure_date IS NULL
782 OR l_failure_code IS NULL
783 OR l_cause_code IS NULL
784 OR l_resolution_code IS NULL
785 )
786 THEN
787 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
788 FND_MSG_PUB.Add;
789 RAISE FND_API.G_EXC_ERROR;
790 END IF;
791 ELSE
792 IF NOT ( ( l_failure_date IS NOT NULL
793 AND l_failure_code IS NOT NULL
794 AND l_cause_code IS NOT NULL
795 AND l_resolution_code IS NOT NULL
796 )
797 OR
798 ( l_failure_date IS NULL
799 AND l_failure_code IS NULL
800 AND l_cause_code IS NULL
801 AND l_resolution_code IS NULL
802 )
803 )
804 THEN
805 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
806 FND_MSG_PUB.Add;
807 RAISE FND_API.G_EXC_ERROR;
808 END IF;
809 END IF;
810
811 END IF;
812 ELSE /* Completed Date is null */
813 IF l_failure_date IS NULL THEN
814 IF ( l_eam_failure_codes_tbl.count = 1
815 AND l_eam_failure_codes_tbl(1).comments IS NOT NULL
816 )
817 THEN
818 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
819 FND_MSG_PUB.Add;
820 RAISE FND_API.G_EXC_ERROR;
821 ELSIF l_eam_failure_codes_tbl.count = 0
822 THEN
823 BEGIN
824 SELECT comments
825 INTO l_comments
826 FROM eam_asset_failure_codes
827 WHERE failure_id = l_eam_failure_entry_record.failure_id;
828 IF l_comments IS NOT NULL THEN
829 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
830 FND_MSG_PUB.Add;
831 RAISE FND_API.G_EXC_ERROR;
832 END IF;
833 EXCEPTION
834 WHEN NO_DATA_FOUND THEN
835 NULL;
836 END;
837 END IF;
838
839 END IF;
840 END IF;
841 END IF;
842
843 EAM_Process_Failure_Entry_PVT.Process_Failure_Entry
844 (
845 p_api_version => p_api_version
846 , p_init_msg_list => P_init_msg_list
847 , p_commit => p_commit
848 , p_eam_failure_entry_record => l_eam_failure_entry_record
849 , p_eam_failure_codes_tbl => l_eam_failure_codes_tbl
850 , x_return_status => x_return_status
851 , x_msg_count => l_out_msg_count
852 , x_msg_data => l_out_msg_count
853 , x_eam_failure_entry_record => l_out_eam_failure_entry_record
854 , x_eam_failure_codes_tbl => l_out_eam_failure_codes_tbl
855 );
856
857 IF nvl(x_return_status,'Q') <> 'S' THEN
858 rollback to Process_Failure_Entry_PUB;
859 x_return_status := FND_API.G_RET_STS_ERROR;
860 RAISE FND_API.G_EXC_ERROR;
861 END IF;
862
863 x_eam_failure_entry_record := l_out_eam_failure_entry_record;
864 x_eam_failure_codes_tbl := l_out_eam_failure_codes_tbl;
865
866 IF FND_API.to_Boolean( p_commit ) THEN
867 COMMIT;
868 END IF;
869
870 EXCEPTION
871
872 WHEN FND_API.G_EXC_ERROR THEN
873 ROLLBACK TO Process_Failure_Entry_PUB;
874 x_return_status := FND_API.G_RET_STS_ERROR ;
875 FND_MSG_PUB.Count_And_Get
876 (
877 p_count => x_msg_count ,
878 p_data => x_msg_data
879 );
880 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
881 ROLLBACK TO Process_Failure_Entry_PUB;
882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
883 FND_MSG_PUB.Count_And_Get
884 (
885 p_count => x_msg_count,
886 p_data => x_msg_data
887 );
888 WHEN OTHERS THEN
889 ROLLBACK TO Process_Failure_Entry_PUB;
890 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
891 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
892 THEN
893 FND_MSG_PUB.Add_Exc_Msg
894 (
895 G_PKG_NAME,
896 l_api_name
897 );
898 END IF;
899 FND_MSG_PUB.Count_And_Get
900 (
901 p_count => x_msg_count,
902 p_data => x_msg_data
903 );
904 END;
905
906
907 END EAM_Process_Failure_Entry_PUB;