[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 2006/05/18 05:41:25 sshahid noship $ */
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 OTHERS THEN
267 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_OBJECT_ID');
268 FND_MESSAGE.SET_TOKEN( token => 'ASSET_NUMBER'
269 ,value => l_eam_failure_entry_record.object_id
270 );
271 FND_MSG_PUB.Add;
272 RAISE FND_API.G_EXC_ERROR;
273 END;
274
275 IF (l_department_id IS NULL) THEN
276 l_department_id := l_msn_department_id;
277 END IF;
278
279 IF( l_eam_failure_entry_record.department_id IS NOT NULL
280 AND ( (l_department_id IS NULL AND l_eam_failure_entry_record.department_id IS NOT NULL)
281 OR l_department_id <> l_eam_failure_entry_record.department_id
282 )
283 )
284 THEN
285 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_DEPARTMENT');
286 FND_MESSAGE.SET_TOKEN( token => 'DEPARTMENT'
287 ,value => l_eam_failure_entry_record.department_id
288 );
289 FND_MSG_PUB.Add;
290 RAISE FND_API.G_EXC_ERROR;
291 ELSE
292 l_eam_failure_entry_record.department_id := l_department_id;
293 END IF;
294
295 IF( l_eam_failure_entry_record.area_id IS NOT NULL
296 AND ( (l_eam_location_id IS NULL AND l_eam_failure_entry_record.area_id IS NOT NULL)
297 OR (l_eam_location_id <> l_eam_failure_entry_record.area_id)
298 )
299 )
300 THEN
301 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_AREA');
302 FND_MESSAGE.SET_TOKEN( token => 'AREA'
303 ,value => l_eam_failure_entry_record.area_id
304 );
305 FND_MSG_PUB.Add;
306 RAISE FND_API.G_EXC_ERROR;
307 ELSE
308 l_eam_failure_entry_record.area_id := l_eam_location_id;
309 END IF;
310
311 END IF;
312
313 ELSIF l_eam_failure_entry_record.transaction_type = EAM_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
314
315 IF l_eam_failure_entry_record.failure_id IS NULL THEN
316
317 IF ( l_eam_failure_entry_record.source_type = 1
318 AND ( l_eam_failure_entry_record.source_id IS NOT NULL
319 OR ( l_eam_failure_entry_record.source_name IS NOT NULL
320 AND l_eam_failure_entry_record.maint_organization_id IS NOT NULL
321 )
322 )
323 )
324 THEN
325 BEGIN
326 IF l_eam_failure_entry_record.source_id IS NOT NULL THEN
327 SELECT failure_id
328 INTO l_eam_failure_entry_record.failure_id
329 FROM eam_asset_failures
330 WHERE source_id = l_eam_failure_entry_record.source_id
331 AND source_type = 1;
332 ELSE
333 SELECT failure_id
334 INTO l_eam_failure_entry_record.failure_id
335 FROM eam_asset_failures
336 WHERE source_id = ( SELECT wip_entity_id
337 FROM WIP_ENTITIES
338 WHERE WIP_ENTITY_NAME = l_eam_failure_entry_record.source_name
339 AND organization_id = l_eam_failure_entry_record.maint_organization_id
340 )
341 AND source_type = 1;
342 END IF;
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_WIP_ENTITY_ID');
346 FND_MESSAGE.SET_TOKEN( token => 'SOURCE_ID'
347 , value => l_eam_failure_entry_record.source_id||l_eam_failure_entry_record.source_name
348 );
349 FND_MSG_PUB.Add;
350 RAISE FND_API.G_EXC_ERROR;
351 END;
352 ELSE
353 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_FAILURE_ID');
354 FND_MSG_PUB.Add;
355 RAISE FND_API.G_EXC_ERROR;
356 END IF;
357 END IF;
358
359
360 BEGIN
361 SELECT object_type , object_id , source_id , source_type , failure_date , maint_organization_id , current_organization_id , area_id
362 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
363 FROM eam_asset_failures
364 WHERE failure_id = l_eam_failure_entry_record.failure_id;
365 l_eam_failure_entry_record.source_id := l_source_id;
366 EXCEPTION
367 WHEN NO_DATA_FOUND THEN
368 FND_MESSAGE.SET_NAME ('EAM', 'EAM_FAILURE_NOT_EXISTS');
369 FND_MESSAGE.SET_TOKEN( token => 'SOURCE_ID'
370 , value => 'Failure Id :'||l_eam_failure_entry_record.failure_id
371 );
372 FND_MSG_PUB.Add;
373 RAISE FND_API.G_EXC_ERROR;
374 END;
375
376 IF l_source_type = 1
377 THEN
378 /***************************************************************
379 Don't Validate the drived columns.
380 IF( ( l_eam_failure_entry_record.object_type IS NOT NULL
381 AND l_eam_failure_entry_record.object_type <> FND_API.G_MISS_NUM
382 AND ( l_object_type IS NULL
383 OR l_object_type <> l_eam_failure_entry_record.object_type
384 )
385 )
386 OR
387 ( l_eam_failure_entry_record.object_type = FND_API.G_MISS_NUM
388 AND l_object_type IS NOT NULL
389 )
390 )
391 THEN
392 FND_MESSAGE.SET_NAME ('EAM', 'EAM_CANNOT_UPDATE');
393 FND_MESSAGE.SET_TOKEN( token => 'ATTRIBUTE'
394 , value => 'OBJECT_TYPE'
395 );
396 FND_MSG_PUB.Add;
397 RAISE FND_API.G_EXC_ERROR;
398 END IF;
399
400 IF( ( l_eam_failure_entry_record.object_id IS NOT NULL
401 AND l_eam_failure_entry_record.object_id <> FND_API.G_MISS_NUM
402 AND ( l_object_id IS NULL
403 OR l_object_id <> l_eam_failure_entry_record.object_id
404 )
405 )
406 OR
407 ( l_eam_failure_entry_record.object_id = FND_API.G_MISS_NUM
408 AND l_object_id IS NOT NULL
409 )
410 )
411 THEN
412 FND_MESSAGE.SET_NAME ('EAM', 'EAM_CANNOT_UPDATE');
413 FND_MESSAGE.SET_TOKEN( token => 'ATTRIBUTE'
414 , value => 'OBJECT_ID'
415 );
416 FND_MSG_PUB.Add;
417 RAISE FND_API.G_EXC_ERROR;
418 END IF;
419
420 IF( ( l_eam_failure_entry_record.source_type IS NOT NULL
421 AND l_eam_failure_entry_record.source_type <> FND_API.G_MISS_NUM
422 AND ( l_source_type IS NULL
423 OR l_source_type <> l_eam_failure_entry_record.source_type
424 )
425 )
426 OR
427 ( l_eam_failure_entry_record.source_type = FND_API.G_MISS_NUM
428 AND l_source_type IS NOT NULL
429 )
430 )
431 THEN
432 FND_MESSAGE.SET_NAME ('EAM', 'EAM_CANNOT_UPDATE');
433 FND_MESSAGE.SET_TOKEN( token => 'ATTRIBUTE'
434 , value => 'SOURCE_TYPE'
435 );
436 FND_MSG_PUB.Add;
437 RAISE FND_API.G_EXC_ERROR;
438 END IF;
439
440 IF( ( l_eam_failure_entry_record.source_id IS NOT NULL
441 AND l_eam_failure_entry_record.source_id <> FND_API.G_MISS_NUM
442 AND ( l_source_id IS NULL
443 OR l_source_id <> l_eam_failure_entry_record.source_id
444 )
445 )
446 OR
447 ( l_eam_failure_entry_record.source_id = FND_API.G_MISS_NUM
448 AND l_source_id IS NOT NULL
449 )
450 )
451 THEN
452 FND_MESSAGE.SET_NAME ('EAM', 'EAM_CANNOT_UPDATE');
453 FND_MESSAGE.SET_TOKEN( token => 'ATTRIBUTE'
454 , value => 'SOURCE_ID'
455 );
456 FND_MSG_PUB.Add;
457 RAISE FND_API.G_EXC_ERROR;
458 ELSE
459 l_eam_failure_entry_record.source_id := l_source_id;
460 END IF;
461 ***************************************************************/
462
463 BEGIN
464 SELECT owning_department , organization_id
465 INTO l_department_id , l_organization_id
466 FROM WIP_DISCRETE_JOBS
467 WHERE wip_entity_id = l_source_id;
468 EXCEPTION
469 WHEN NO_DATA_FOUND THEN
470 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_WIP_ENTITY_ID');
471 FND_MESSAGE.SET_TOKEN( token => 'SOURCE_ID'
472 , value => l_eam_failure_entry_record.source_id
473 );
474 FND_MSG_PUB.Add;
475 RAISE FND_API.G_EXC_ERROR;
476 END;
477
478 IF( ( l_eam_failure_entry_record.maint_organization_id IS NOT NULL
479 AND l_eam_failure_entry_record.maint_organization_id <> FND_API.G_MISS_NUM
480 AND ( l_organization_id IS NULL
481 OR l_organization_id <> l_eam_failure_entry_record.maint_organization_id
482 )
483 )
484 OR
485 ( l_eam_failure_entry_record.maint_organization_id = FND_API.G_MISS_NUM
486 AND l_organization_id IS NOT NULL
487 )
488 )
489 THEN
490 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_MAINT_ORG');
491 FND_MESSAGE.SET_TOKEN( token => 'MAINT_ORG_ID'
492 , value => l_eam_failure_entry_record.maint_organization_id
493 );
494 FND_MSG_PUB.Add;
495 RAISE FND_API.G_EXC_ERROR;
496 ELSE
497 l_eam_failure_entry_record.maint_organization_id := l_organization_id;
498 END IF;
499
500 IF( ( l_eam_failure_entry_record.current_organization_id IS NOT NULL
501 AND l_eam_failure_entry_record.current_organization_id <> FND_API.G_MISS_NUM
502 AND ( l_current_organization_id IS NULL
503 OR l_current_organization_id <> l_eam_failure_entry_record.current_organization_id
504 )
505 )
506 OR
507 ( l_eam_failure_entry_record.current_organization_id = FND_API.G_MISS_NUM
508 AND l_current_organization_id IS NOT NULL
509 )
510 )
511 THEN
512 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_CURRENT_ORG');
513 FND_MESSAGE.SET_TOKEN( token => 'CURR_ORG_ID'
514 , value => l_eam_failure_entry_record.current_organization_id
515 );
516 FND_MSG_PUB.Add;
517 RAISE FND_API.G_EXC_ERROR;
518 ELSE
519 l_eam_failure_entry_record.current_organization_id := l_current_organization_id;
520 END IF;
521
522 BEGIN
523
524 SELECT eomd.owning_department_id,eomd.area_id
525 INTO l_msn_department_id, l_eam_location_id
526 FROM csi_item_instances cii,
527 eam_org_maint_defaults eomd,mtl_parameters mp
528 WHERE cii.instance_id = l_object_id
529 AND cii.instance_id = eomd.object_id(+)
530 AND eomd.object_type(+)= 50
531 AND cii.last_vld_organization_id = mp.organization_id
532 AND ( eomd.organization_id IS NULL OR
533 mp.maint_organization_id = eomd.organization_id);
534
535
536 EXCEPTION
537 WHEN OTHERS THEN
538 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_OBJECT_ID');
539 FND_MESSAGE.SET_TOKEN( token => 'ASSET_NUMBER'
540 ,value => l_object_id
541 );
542 FND_MSG_PUB.Add;
543 RAISE FND_API.G_EXC_ERROR;
544 END;
545
546 IF (l_department_id IS NULL) THEN
547 l_department_id := l_msn_department_id;
548 END IF;
549
550
551 IF( ( l_eam_failure_entry_record.department_id IS NOT NULL
552 AND l_eam_failure_entry_record.department_id <> FND_API.G_MISS_NUM
553 AND ( l_department_id IS NULL
554 OR l_department_id <> l_eam_failure_entry_record.department_id
555 )
556 )
557 OR
558 ( l_eam_failure_entry_record.department_id = FND_API.G_MISS_NUM
559 AND l_department_id IS NOT NULL
560 )
561 )
562 THEN
563 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_DEPARTMENT');
564 FND_MESSAGE.SET_TOKEN( token => 'DEPARTMENT'
565 ,value => l_eam_failure_entry_record.department_id
566 );
567 FND_MSG_PUB.Add;
568 RAISE FND_API.G_EXC_ERROR;
569 ELSE
570 l_eam_failure_entry_record.department_id := l_department_id;
571 END IF;
572
573
574 IF( ( l_eam_failure_entry_record.area_id IS NOT NULL
575 AND l_eam_failure_entry_record.area_id <> FND_API.G_MISS_NUM
576 AND ( l_eam_location_id IS NULL
577 OR l_eam_location_id <> l_eam_failure_entry_record.area_id
578 )
579 )
580 OR
581 ( l_eam_failure_entry_record.area_id = FND_API.G_MISS_NUM
582 AND l_eam_location_id IS NOT NULL
583 )
584 )
585 THEN
586 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_AREA');
587 FND_MESSAGE.SET_TOKEN( token => 'AREA'
588 ,value => l_eam_failure_entry_record.area_id
589 );
590 FND_MSG_PUB.Add;
591 RAISE FND_API.G_EXC_ERROR;
592 ELSE
593 l_eam_failure_entry_record.area_id := l_eam_location_id;
594 END IF;
595
596 ELSE
597 IF l_eam_failure_entry_record.maint_organization_id = FND_API.G_MISS_NUM THEN
598 l_eam_failure_entry_record.maint_organization_id := NULL;
599 ELSIF l_eam_failure_entry_record.maint_organization_id IS NULL THEN
600 l_eam_failure_entry_record.maint_organization_id := l_maint_organization_id;
601 END IF;
602
603 IF l_eam_failure_entry_record.current_organization_id = FND_API.G_MISS_NUM THEN
604 l_eam_failure_entry_record.current_organization_id := NULL;
605 ELSIF l_eam_failure_entry_record.current_organization_id IS NULL THEN
606 l_eam_failure_entry_record.current_organization_id := l_current_organization_id;
607 END IF;
608
609 IF l_eam_failure_entry_record.department_id = FND_API.G_MISS_NUM THEN
610 l_eam_failure_entry_record.department_id := NULL;
611 ELSIF l_eam_failure_entry_record.department_id IS NULL THEN
612 l_eam_failure_entry_record.department_id := l_department_id;
613 END IF;
614
615 IF l_eam_failure_entry_record.area_id = FND_API.G_MISS_NUM THEN
616 l_eam_failure_entry_record.area_id := NULL;
617 ELSIF l_eam_failure_entry_record.area_id IS NULL THEN
618 l_eam_failure_entry_record.area_id := l_maint_organization_id;
619 END IF;
620
621 END IF;
622
623 IF l_eam_failure_entry_record.failure_date = FND_API.G_MISS_DATE THEN
624 l_eam_failure_entry_record.failure_date := NULL;
625 ELSIF l_eam_failure_entry_record.failure_date IS NULL THEN
626 l_eam_failure_entry_record.failure_date := l_failure_date;
627 END IF;
628 END IF;
629
630 FOR i in 1..l_eam_failure_codes_tbl.count
631 LOOP
632
633 IF l_eam_failure_codes_tbl(i).transaction_type = EAM_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
634
635 l_eam_failure_codes_tbl(i).failure_id := l_eam_failure_entry_record.failure_id;
636
637 IF l_eam_failure_codes_tbl(i).failure_entry_id IS NULL
638 THEN
639
640 BEGIN
641 SELECT failure_entry_id , failure_code, cause_code, resolution_code, comments
642 INTO l_eam_failure_codes_tbl(i).failure_entry_id, l_failure_code, l_cause_code, l_resolution_code, l_comments
643 FROM eam_asset_failure_codes
644 WHERE failure_id = l_eam_failure_codes_tbl(i).failure_id;
645 EXCEPTION
646 WHEN NO_DATA_FOUND THEN
647 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_FAILURE');
648 FND_MESSAGE.SET_TOKEN( token => 'SOURCE_ID'
649 , value => l_eam_failure_codes_tbl(i).failure_id
650 );
651 FND_MSG_PUB.Add;
652 RAISE FND_API.G_EXC_ERROR;
653 END;
654
655 ELSE
656
657 BEGIN
658 SELECT failure_id , failure_code, cause_code, resolution_code, comments
659 INTO l_eam_failure_codes_tbl(i).failure_id, l_failure_code, l_cause_code, l_resolution_code, l_comments
660 FROM eam_asset_failure_codes
661 WHERE failure_entry_id = l_eam_failure_codes_tbl(i).failure_entry_id;
662 EXCEPTION
663 WHEN NO_DATA_FOUND THEN
664 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_FAILURE_ENTRY_ID');
665 FND_MSG_PUB.Add;
666 RAISE FND_API.G_EXC_ERROR;
667 END;
668
669 IF l_eam_failure_codes_tbl(i).failure_id <> l_eam_failure_entry_record.failure_id
670 THEN
671 FND_MESSAGE.SET_NAME ('EAM', 'EAM_CHILD_NOT_SYNC');
672 FND_MSG_PUB.Add;
673 RAISE FND_API.G_EXC_ERROR;
674 END IF;
675
676 END IF;
677
678 IF l_eam_failure_codes_tbl(i).failure_code = FND_API.G_MISS_CHAR THEN
679 l_eam_failure_codes_tbl(i).failure_code := NULL;
680 ELSIF l_eam_failure_codes_tbl(i).failure_code IS NULL THEN
681 l_eam_failure_codes_tbl(i).failure_code := l_failure_code;
682 END IF;
683
684 IF l_eam_failure_codes_tbl(i).cause_code = FND_API.G_MISS_CHAR THEN
685 l_eam_failure_codes_tbl(i).cause_code := NULL;
686 ELSIF l_eam_failure_codes_tbl(i).cause_code IS NULL THEN
687 l_eam_failure_codes_tbl(i).cause_code := l_cause_code;
688 END IF;
689
690 IF l_eam_failure_codes_tbl(i).resolution_code = FND_API.G_MISS_CHAR THEN
691 l_eam_failure_codes_tbl(i).resolution_code := NULL;
692 ELSIF l_eam_failure_codes_tbl(i).resolution_code IS NULL THEN
693 l_eam_failure_codes_tbl(i).resolution_code := l_resolution_code;
694 END IF;
695
696 IF l_eam_failure_codes_tbl(i).comments = FND_API.G_MISS_CHAR THEN
697 l_eam_failure_codes_tbl(i).comments := NULL;
698 ELSIF l_eam_failure_codes_tbl(i).comments IS NULL THEN
699 l_eam_failure_codes_tbl(i).comments := l_comments;
700 END IF;
701
702 ELSIF l_eam_failure_codes_tbl(i).transaction_type = EAM_Process_Failure_Entry_PUB.G_FE_CREATE THEN
703
704 l_eam_failure_codes_tbl(i).failure_id := l_eam_failure_entry_record.failure_id;
705
706 END IF;
707
708 END LOOP;
709
710 /* Client Side Validation
711 * For the Completed Work Orders, Will do the validation
712 * after calling the API. Since the Master Data has to
713 * be validated against the child data.
714 */
715 IF l_eam_failure_entry_record.source_type = 1
716 THEN
717
718 BEGIN
719 SELECT wdj.date_completed, nvl(edw.failure_code_required, 'N')
720 INTO l_date_completed , l_failure_code_required
721 FROM wip_discrete_jobs wdj, eam_work_order_details edw
722 WHERE wdj.wip_entity_id = edw.wip_entity_id
723 AND wdj.wip_entity_id = l_eam_failure_entry_record.source_id;
724 EXCEPTION
725 WHEN NO_DATA_FOUND THEN
726 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_WIP_ENTITY_ID');
727 FND_MESSAGE.SET_TOKEN( token => 'SOURCE_ID'
728 , value => l_eam_failure_entry_record.source_name
729 );
730 FND_MSG_PUB.Add;
731 RAISE FND_API.G_EXC_ERROR;
732 END;
733
734 /********************************
735 Following Validations for Complete Work Order are as per the 11510 design.
736 ********************************/
737 l_failure_date := l_eam_failure_entry_record.failure_date;
738 IF l_date_completed IS NOT NULL THEN
739 /* Completed Work Order */
740 IF l_eam_failure_codes_tbl.count = 1
741 THEN
742 l_failure_code := l_eam_failure_codes_tbl(1).failure_code;
743 l_cause_code := l_eam_failure_codes_tbl(1).cause_code;
744 l_resolution_code := l_eam_failure_codes_tbl(1).resolution_code;
745 l_comments := l_eam_failure_codes_tbl(1).comments;
746 ELSE
747 BEGIN
748 SELECT failure_code , cause_code , resolution_code , comments
749 INTO l_failure_code, l_cause_code, l_resolution_code, l_comments
750 FROM eam_asset_failure_codes
751 WHERE failure_id = l_eam_failure_entry_record.failure_id;
752 EXCEPTION
753 WHEN NO_DATA_FOUND THEN
754 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
755 FND_MSG_PUB.Add;
756 RAISE FND_API.G_EXC_ERROR;
757 END;
758 END IF;
759
760 IF l_failure_code_required = 'Y'
761 THEN
762 IF NOT( l_failure_date IS NOT NULL
763 AND l_failure_code IS NOT NULL
764 AND l_cause_code IS NOT NULL
765 AND l_resolution_code IS NOT NULL
766 )
767 THEN
768 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
769 FND_MSG_PUB.Add;
770 RAISE FND_API.G_EXC_ERROR;
771 END IF;
772 ELSE
773
774 IF l_comments IS NOT NULL THEN
775 IF ( l_failure_date IS NULL
776 OR l_failure_code IS NULL
777 OR l_cause_code IS NULL
778 OR l_resolution_code IS NULL
779 )
780 THEN
781 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
782 FND_MSG_PUB.Add;
783 RAISE FND_API.G_EXC_ERROR;
784 END IF;
785 ELSE
786 IF NOT ( ( l_failure_date IS NOT NULL
787 AND l_failure_code IS NOT NULL
788 AND l_cause_code IS NOT NULL
789 AND l_resolution_code IS NOT NULL
790 )
791 OR
792 ( l_failure_date IS NULL
793 AND l_failure_code IS NULL
794 AND l_cause_code IS NULL
795 AND l_resolution_code IS NULL
796 )
797 )
798 THEN
799 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
800 FND_MSG_PUB.Add;
801 RAISE FND_API.G_EXC_ERROR;
802 END IF;
803 END IF;
804
805 END IF;
806 ELSE /* Completed Date is null */
807 IF l_failure_date IS NULL THEN
808 IF ( l_eam_failure_codes_tbl.count = 1
809 AND l_eam_failure_codes_tbl(1).comments IS NOT NULL
810 )
811 THEN
812 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
813 FND_MSG_PUB.Add;
814 RAISE FND_API.G_EXC_ERROR;
815 ELSIF l_eam_failure_codes_tbl.count = 0
816 THEN
817 BEGIN
818 SELECT comments
819 INTO l_comments
820 FROM eam_asset_failure_codes
821 WHERE failure_id = l_eam_failure_entry_record.failure_id;
822 IF l_comments IS NOT NULL THEN
823 FND_MESSAGE.SET_NAME ('EAM', 'EAM_ENTER_FAILURE_INFO');
824 FND_MSG_PUB.Add;
825 RAISE FND_API.G_EXC_ERROR;
826 END IF;
827 EXCEPTION
828 WHEN NO_DATA_FOUND THEN
829 NULL;
830 END;
831 END IF;
832
833 END IF;
834 END IF;
835 END IF;
836
837 EAM_Process_Failure_Entry_PVT.Process_Failure_Entry
838 (
839 p_api_version => p_api_version
840 , p_init_msg_list => P_init_msg_list
841 , p_commit => p_commit
842 , p_eam_failure_entry_record => l_eam_failure_entry_record
843 , p_eam_failure_codes_tbl => l_eam_failure_codes_tbl
844 , x_return_status => x_return_status
845 , x_msg_count => l_out_msg_count
846 , x_msg_data => l_out_msg_count
847 , x_eam_failure_entry_record => l_out_eam_failure_entry_record
848 , x_eam_failure_codes_tbl => l_out_eam_failure_codes_tbl
849 );
850
851 IF nvl(x_return_status,'Q') <> 'S' THEN
852 rollback to Process_Failure_Entry_PUB;
853 x_return_status := FND_API.G_RET_STS_ERROR;
854 RAISE FND_API.G_EXC_ERROR;
855 END IF;
856
857 x_eam_failure_entry_record := l_out_eam_failure_entry_record;
858 x_eam_failure_codes_tbl := l_out_eam_failure_codes_tbl;
859
860 IF FND_API.to_Boolean( p_commit ) THEN
861 COMMIT;
862 END IF;
863
864 EXCEPTION
865
866 WHEN FND_API.G_EXC_ERROR THEN
867 ROLLBACK TO Process_Failure_Entry_PUB;
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_PUB;
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_PUB;
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 END;
899
900
901 END EAM_Process_Failure_Entry_PUB;