[Home] [Help]
PACKAGE BODY: APPS.EAM_FAILURECODES_PVT
Source
1 PACKAGE BODY EAM_FailureCodes_PVT AS
2 /* $Header: EAMVFCPB.pls 120.0 2006/03/08 07:10:39 sshahid noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_FailureCodes_PVT';
4
5 G_LOCKROW_EXCEPTION EXCEPTION;
6 PRAGMA EXCEPTION_INIT (G_LOCKROW_EXCEPTION,-54);
7
8 -- Procedure for raising errors
9 PROCEDURE Raise_Error (p_error VARCHAR2, p_token VARCHAR2, p_token_value VARCHAR2)
10 IS
11 BEGIN
12
13 FND_MESSAGE.SET_NAME ('EAM', p_error);
14 IF (p_token IS NOT NULL) THEN
15 FND_MESSAGE.SET_TOKEN (p_token, p_token_value);
16 END IF;
17 FND_MSG_PUB.ADD;
18 RAISE FND_API.G_EXC_ERROR;
19 END Raise_Error;
20
21 -- Procedure to Validate Code Info passed in various modes
22 -- to Setup_Code API
23 PROCEDURE Validate_Code
24 (p_mode IN VARCHAR2,
25 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
26 p_failurecode_rec IN EAM_FailureCodes_PUB.eam_failurecode_rec_type)
27 IS
28 l_code_exists NUMBER;
29 l_eff_end_date DATE;
30 l_code_in_use NUMBER;
31
32 BEGIN
33
34 IF (p_failurecode_rec.code_type IS NULL OR
35 p_failurecode_rec.code IS NULL) THEN
36 Raise_Error ('EAM_FAILURECODE_MANDATORY', 'MAND_PARAM', 'code or code type');
37 END IF;
38
39 IF (p_failurecode_rec.code_type NOT IN
40 (EAM_FailureCodes_PUB.G_FAILURE_CODE,
41 EAM_FailureCodes_PUB.G_CAUSE_CODE,
42 EAM_FailureCodes_PUB.G_RESOLUTION_CODE)) THEN
43 Raise_Error ('EAM_FAILURECODE_TYPE_INVALID','CODE_TYPE', p_failurecode_rec.code_type);
44 END IF;
45
46 l_code_exists := 0;
47 BEGIN
48 IF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_FAILURE_CODE) THEN
49 SELECT effective_end_date
50 INTO l_eff_end_date
51 FROM eam_failure_codes
52 WHERE failure_code = p_failurecode_rec.code;
53 ELSIF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_CAUSE_CODE) THEN
54 SELECT effective_end_date
55 INTO l_eff_end_date
56 FROM eam_cause_codes
57 WHERE cause_code = p_failurecode_rec.code;
58 ELSIF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_RESOLUTION_CODE) THEN
59 SELECT effective_end_date
60 INTO l_eff_end_date
61 FROM eam_resolution_codes
62 WHERE resolution_code = p_failurecode_rec.code;
63 END IF;
64
65 l_code_exists := SQL%ROWCOUNT;
66
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 l_code_exists := 0;
70 END;
71
72 IF (p_mode = 'C' AND l_code_exists > 0) THEN
73 Raise_Error ('EAM_FAILURECODE_EXISTS', 'FAILURE_CODE', p_failurecode_rec.code);
74
75 ELSIF (p_mode = 'U') THEN
76
77 IF (l_code_exists = 0) THEN
78 Raise_Error ('EAM_FAILURECODE_NOT_EXISTS', 'FAILURE_CODE', p_failurecode_rec.code);
79 END IF;
80
81 /*
82 IF (l_eff_end_date IS NOT NULL AND
83 TRUNC(SYSDATE) > TRUNC(l_eff_end_date)) THEN
84 Raise_Error ('EAM_FAILURECODE_INACTIVE');
85 END IF;
86 */
87
88 ELSIF (p_mode = 'D') THEN
89
90 IF (l_code_exists = 0) THEN
91 Raise_Error ('EAM_FAILURECODE_NOT_EXISTS','FAILURE_CODE', p_failurecode_rec.code);
92 END IF;
93
94 l_code_in_use := 0;
95
96 IF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_FAILURE_CODE) THEN
97 SELECT count(1)
98 INTO l_code_in_use
99 FROM eam_failure_combinations
100 WHERE failure_code = p_failurecode_rec.code
101 AND rownum < 2;
102 ELSIF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_CAUSE_CODE) THEN
103 SELECT count(1)
104 INTO l_code_in_use
105 FROM eam_failure_combinations
106 WHERE cause_code = p_failurecode_rec.code
107 AND rownum < 2;
108 ELSIF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_RESOLUTION_CODE) THEN
109 SELECT count(1)
110 INTO l_code_in_use
111 FROM eam_failure_combinations
112 WHERE resolution_code = p_failurecode_rec.code
113 AND rownum < 2;
114 END IF;
115
116 IF (l_code_in_use > 0) THEN
117 Raise_Error ('EAM_FAILURECODE_USED','FAILURE_CODE', p_failurecode_rec.code);
118 END IF;
119 END IF;
120 END Validate_Code;
121
122 -- Procedure to Validate Code Combination Info passed in various modes
123 -- to Setup_Combination API
124 PROCEDURE Validate_Combination
125 (p_mode IN VARCHAR2,
126 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
127 p_combination_rec IN EAM_FailureCodes_PUB.eam_combination_rec_type,
128 x_set_id OUT NOCOPY NUMBER,
129 x_failure_exists OUT NOCOPY NUMBER,
130 x_cause_exists OUT NOCOPY NUMBER,
131 x_resolution_exists OUT NOCOPY NUMBER,
132 x_combination_id OUT NOCOPY NUMBER)
133 IS
134
135 l_set_id NUMBER;
136 l_failure_exists NUMBER;
137 l_cause_exists NUMBER;
138 l_resolution_exists NUMBER;
139 l_set_end_date DATE;
140 l_combination_exists NUMBER;
141 l_failure_end_date DATE;
142 l_cause_end_date DATE;
143 l_resolution_end_date DATE;
144 l_combination_id NUMBER;
145 l_combination_used NUMBER;
146
147 BEGIN
148 -- Ensure mandatory parameters are there
149 IF (p_combination_rec.failure_code IS NULL OR
150 p_combination_rec.cause_code IS NULL OR
151 p_combination_rec.resolution_code IS NULL) THEN
152 Raise_Error ('EAM_FAILURECODE_MANDATORY','MAND_PARAM','failure code or cause code or resolution code');
153 END IF;
154
155 -- Validate Failure Set
156 IF (p_combination_rec.set_id IS NOT NULL) THEN
157 l_set_id := p_combination_rec.set_id;
158 ELSIF (p_combination_rec.set_name IS NOT NULL) THEN
159 SELECT min(set_id)
160 INTO l_set_id
161 FROM eam_failure_sets
162 WHERE set_name = p_combination_rec.set_name;
163
164 IF (l_set_id IS NULL) THEN
165 Raise_Error ('EAM_FAILURESET_INVALID', 'FAILURE_SET', p_combination_rec.set_name);
166 END IF;
167 ELSE
168 Raise_Error ('EAM_FAILURESET_INVALID',NULL, NULL);
169 END IF;
170
171 SELECT min(effective_end_date)
172 INTO l_set_end_date
173 FROM eam_failure_sets
174 WHERE set_id = l_set_id;
175
176 IF (l_set_end_date IS NOT NULL AND
177 TRUNC(SYSDATE) > TRUNC(l_set_end_date)) THEN
178 Raise_Error ('EAM_FAILURESET_INACTIVE', 'FAILURE_SET', l_set_id || ' - ' || p_combination_rec.set_name);
179 END IF;
180
181 x_set_id := l_set_id;
182
183 -- Validate Failure, Cause and Resolution Codes
184 l_failure_exists := 1;
185 l_cause_exists := 1;
186 l_resolution_exists := 1;
187
188 BEGIN
189 SELECT effective_end_date
190 INTO l_failure_end_date
191 FROM eam_failure_codes
192 WHERE failure_code = p_combination_rec.failure_code;
193 l_failure_exists := SQL%ROWCOUNT;
194 EXCEPTION
195 WHEN NO_DATA_FOUND THEN
196 l_failure_exists := 0;
197 END;
198
199 BEGIN
200 SELECT effective_end_date
201 INTO l_cause_end_date
202 FROM eam_cause_codes
203 WHERE cause_code = p_combination_rec.cause_code;
204 l_cause_exists := SQL%ROWCOUNT;
205 EXCEPTION
206 WHEN NO_DATA_FOUND THEN
207 l_cause_exists := 0;
208 END;
209
210 BEGIN
211 SELECT effective_end_date
212 INTO l_resolution_end_date
213 FROM eam_resolution_codes
214 WHERE resolution_code = p_combination_rec.resolution_code;
215 l_resolution_exists := SQL%ROWCOUNT;
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 l_resolution_exists := 0;
219 END;
220
221 x_failure_exists := l_failure_exists ;
222 x_cause_exists := l_cause_exists ;
223 x_resolution_exists := l_resolution_exists;
224
225 l_combination_exists := 0;
226
227 IF (p_combination_rec.combination_id IS NOT NULL) THEN
228 SELECT count(1)
229 INTO l_combination_exists
230 FROM eam_failure_combinations
231 WHERE combination_id = p_combination_rec.combination_id;
232 l_combination_id := p_combination_rec.combination_id;
233 ELSE
234 BEGIN
235 SELECT combination_id
236 INTO l_combination_id
237 FROM eam_failure_combinations
238 WHERE set_id = l_set_id
239 AND failure_code = p_combination_rec.failure_code
240 AND cause_code = p_combination_rec.cause_code
241 AND resolution_code = p_combination_rec.resolution_code;
242 l_combination_exists := SQL%ROWCOUNT;
243 EXCEPTION
244 WHEN NO_DATA_FOUND THEN
245 l_combination_exists := 0;
246 END;
247
248 END IF;
249
250 IF (p_mode = 'C') THEN
251
252 IF (l_combination_exists > 0) THEN
253 Raise_Error ('EAM_COMBINATION_EXISTS', 'COMBINATION', p_combination_rec.failure_code || ' - ' ||
254 p_combination_rec.cause_code || ' - ' ||
255 p_combination_rec.resolution_code);
256 END IF;
257
258 IF (l_failure_end_date IS NOT NULL AND
259 TRUNC(SYSDATE) > TRUNC(l_failure_end_date)) THEN
260 Raise_Error ('EAM_FAILURECODE_INACTIVE','FAILURE_CODE',p_combination_rec.failure_code);
261 END IF;
262
263 IF (l_cause_end_date IS NOT NULL AND
264 TRUNC(SYSDATE) > TRUNC(l_cause_end_date)) THEN
265 Raise_Error ('EAM_FAILURECODE_INACTIVE','FAILURE_CODE',p_combination_rec.cause_code);
266 END IF;
267
268 IF (l_resolution_end_date IS NOT NULL AND
269 TRUNC(SYSDATE) > TRUNC(l_resolution_end_date)) THEN
270 Raise_Error ('EAM_FAILURECODE_INACTIVE', 'FAILURE_CODE',p_combination_rec.resolution_code);
271 END IF;
272
273 ELSIF (p_mode = 'U') THEN
274
275 IF (l_combination_exists = 0) THEN
276 Raise_Error ('EAM_COMBINATION_INVALID', 'COMBINATION', p_combination_rec.failure_code || ' - ' ||
277 p_combination_rec.cause_code || ' - ' ||
278 p_combination_rec.resolution_code);
279 END IF;
280
281 IF (p_combination_rec.effective_end_date = FND_API.G_MISS_DATE) THEN
282
283 IF (l_failure_end_date IS NOT NULL AND
284 TRUNC(SYSDATE) > TRUNC(l_failure_end_date)) THEN
285 Raise_Error ('EAM_FAILURECODE_INACTIVE','FAILURE_CODE',p_combination_rec.failure_code);
286 END IF;
287
288 IF (l_cause_end_date IS NOT NULL AND
289 TRUNC(SYSDATE) > TRUNC(l_cause_end_date)) THEN
290 Raise_Error ('EAM_FAILURECODE_INACTIVE','FAILURE_CODE',p_combination_rec.cause_code);
291 END IF;
292
293 IF (l_resolution_end_date IS NOT NULL AND
294 TRUNC(SYSDATE) > TRUNC(l_resolution_end_date)) THEN
295 Raise_Error ('EAM_FAILURECODE_INACTIVE', 'FAILURE_CODE',p_combination_rec.resolution_code);
296 END IF;
297
298 END IF;
299
300 ELSIF (p_mode = 'D') THEN
301 IF (l_combination_exists = 0) THEN
302 SELECT min(combination_id)
303 INTO l_combination_id
304 FROM eam_failure_combinations
305 WHERE set_id = l_set_id
306 AND failure_code = p_combination_rec.failure_code
307 AND cause_code = p_combination_rec.cause_code
308 AND resolution_code = p_combination_rec.resolution_code;
309 END IF;
310
311
312 l_combination_used := 0;
313 SELECT count(1)
314 INTO l_combination_used
315 FROM eam_asset_failure_codes
316 WHERE combination_id = l_combination_id
317 AND rownum < 2;
318
319 IF (l_combination_used > 0) THEN
320 Raise_Error ('EAM_COMBINATION_USED', 'COMBINATION', p_combination_rec.failure_code || ' - ' ||
321 p_combination_rec.cause_code || ' - ' ||
322 p_combination_rec.resolution_code);
323 END IF;
324
325
326 END IF;
327 x_combination_id := l_combination_id;
328 END Validate_Combination;
329
330 PROCEDURE Setup_Code
331 (p_api_version IN NUMBER ,
332 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
333 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
334 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
335 p_mode IN VARCHAR2 ,
336 p_failurecode_rec IN EAM_FailureCodes_PUB.eam_failurecode_rec_type,
337 x_return_status OUT NOCOPY VARCHAR2 ,
338 x_msg_count OUT NOCOPY NUMBER ,
339 x_msg_data OUT NOCOPY VARCHAR2
340 )
341 IS
342 l_api_name CONSTANT VARCHAR2(30) := 'Setup_Code';
343 l_api_version CONSTANT NUMBER := 1.0;
344 l_code VARCHAR2(80);
345
346 CURSOR lock_fc IS
347 SELECT description, effective_end_date
348 FROM eam_failure_codes
349 WHERE failure_code = l_code
350 FOR UPDATE NOWAIT;
351
352 CURSOR lock_cc IS
353 SELECT description, effective_end_date
354 FROM eam_cause_codes
355 WHERE cause_code = l_code
356 FOR UPDATE NOWAIT;
357
358 CURSOR lock_rc IS
359 SELECT description, effective_end_date
360 FROM eam_resolution_codes
361 WHERE resolution_code = l_code
362 FOR UPDATE NOWAIT;
363
364 BEGIN
365 -- API savepoint
366 SAVEPOINT Setup_Code_PVT;
367
368 -- check for call compatibility.
369 IF NOT FND_API.Compatible_API_Call (l_api_version,
370 p_api_version,
371 l_api_name,
372 G_PKG_NAME )
373 THEN
374 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
375 END IF;
376
377 -- Initialize message list if p_init_msg_list is set to TRUE.
378 IF FND_API.to_Boolean( p_init_msg_list ) THEN
379 FND_MSG_PUB.initialize;
380 END IF;
381
382 -- Initialize API return status to success
383 x_return_status := FND_API.G_RET_STS_SUCCESS;
384
385 -- Validate Failure Code Info passed
386 Validate_Code(p_mode, p_validation_level, p_failurecode_rec);
387
388 l_code := p_failurecode_rec.code;
389
390 IF (p_mode = 'C') THEN
391
392 IF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_FAILURE_CODE) THEN
393
394 -- Insert into eam failure codes
395 INSERT INTO eam_failure_codes
396 (failure_code ,
397 description ,
398 effective_end_date,
399 created_by ,
400 creation_date ,
401 last_update_date ,
402 last_updated_by ,
403 last_update_login)
404 VALUES (p_failurecode_rec.code ,
405 p_failurecode_rec.description ,
406 p_failurecode_rec.effective_end_date,
407 fnd_global.user_id,
408 SYSDATE,
409 SYSDATE,
410 fnd_global.user_id,
411 NULL);
412 ELSIF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_CAUSE_CODE) THEN
413
414 -- Insert into eam cause codes
415 INSERT INTO eam_cause_codes
416 (cause_code ,
417 description ,
418 effective_end_date,
419 created_by ,
420 creation_date ,
421 last_update_date ,
422 last_updated_by ,
423 last_update_login)
424 VALUES (p_failurecode_rec.code ,
425 p_failurecode_rec.description ,
426 p_failurecode_rec.effective_end_date,
427 fnd_global.user_id,
428 SYSDATE,
429 SYSDATE,
430 fnd_global.user_id,
431 NULL);
432 ELSIF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_RESOLUTION_CODE) THEN
433
434 -- Insert into eam resolution codes
435 INSERT INTO eam_resolution_codes
436 (resolution_code ,
437 description ,
438 effective_end_date,
439 created_by ,
440 creation_date ,
441 last_update_date ,
442 last_updated_by ,
443 last_update_login)
444 VALUES (p_failurecode_rec.code ,
445 p_failurecode_rec.description ,
446 p_failurecode_rec.effective_end_date,
447 fnd_global.user_id,
448 SYSDATE,
449 SYSDATE,
450 fnd_global.user_id,
451 NULL);
452 END IF;
453
454 ELSIF (p_mode = 'U') THEN
455
456 IF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_FAILURE_CODE) THEN
457 OPEN lock_fc;
458 UPDATE eam_failure_codes
459 SET description = decode(p_failurecode_rec.description,
460 NULL,description,
461 FND_API.G_MISS_CHAR, NULL,
462 p_failurecode_rec.description),
463 effective_end_date = decode(
464 p_failurecode_rec.effective_end_date,
465 NULL,effective_end_date,
466 FND_API.G_MISS_DATE,NULL,
467 p_failurecode_rec.effective_end_date)
468 WHERE failure_code = l_code;
469 CLOSE lock_fc;
470 ELSIF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_CAUSE_CODE) THEN
471 OPEN lock_cc;
472 UPDATE eam_cause_codes
473 SET description = decode(p_failurecode_rec.description,
474 NULL,description,
475 FND_API.G_MISS_CHAR, NULL,
476 p_failurecode_rec.description),
477 effective_end_date = decode(
478 p_failurecode_rec.effective_end_date,
479 NULL,effective_end_date,
480 FND_API.G_MISS_DATE,NULL,
484 ELSIF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_RESOLUTION_CODE) THEN
481 p_failurecode_rec.effective_end_date)
482 WHERE cause_code = l_code;
483 CLOSE lock_cc;
485 OPEN lock_rc;
486 UPDATE eam_resolution_codes
487 SET description = decode(p_failurecode_rec.description,
488 NULL,description,
489 FND_API.G_MISS_CHAR, NULL,
490 p_failurecode_rec.description),
491 effective_end_date = decode(
492 p_failurecode_rec.effective_end_date,
493 NULL,effective_end_date,
494 FND_API.G_MISS_DATE,NULL,
495 p_failurecode_rec.effective_end_date)
496 WHERE resolution_code = l_code;
497 CLOSE lock_rc;
498 END IF;
499
500 ELSIF (p_mode = 'D') THEN
501
502 IF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_FAILURE_CODE) THEN
503 DELETE FROM eam_failure_codes
504 WHERE failure_code = l_code;
505 ELSIF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_CAUSE_CODE) THEN
506 DELETE FROM eam_cause_codes
507 WHERE cause_code = l_code;
508 ELSIF (p_failurecode_rec.code_type = EAM_FailureCodes_PUB.G_RESOLUTION_CODE) THEN
509 DELETE FROM eam_resolution_codes
510 WHERE resolution_code = l_code;
511 END IF;
512
513 END IF;
514
515 -- Standard check of p_commit.
516 IF FND_API.To_Boolean( p_commit ) THEN
517 COMMIT WORK;
518 END IF;
519
520 -- call to get message count and if count is 1, get message info.
521 FND_MSG_PUB.Count_And_Get
522 (p_count => x_msg_count,
523 p_data => x_msg_data
524 );
525 EXCEPTION
526 WHEN FND_API.G_EXC_ERROR THEN
527 ROLLBACK TO Setup_Code_PVT;
528 x_return_status := FND_API.G_RET_STS_ERROR ;
529 FND_MSG_PUB.Count_And_Get
530 (p_count => x_msg_count ,
531 p_data => x_msg_data
532 );
533 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
534 ROLLBACK TO Setup_Code_PVT;
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
536 FND_MSG_PUB.Count_And_Get
537 (p_count => x_msg_count,
538 p_data => x_msg_data
539 );
540 WHEN G_LOCKROW_EXCEPTION THEN
541 ROLLBACK TO Setup_Code_PVT;
542 Raise_Error ('EAM_ROW_LOCKED',NULL,NULL);
543 WHEN OTHERS THEN
544 ROLLBACK TO Setup_Code_PVT;
545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
546 IF FND_MSG_PUB.Check_Msg_Level
547 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
548 THEN
549 FND_MSG_PUB.Add_Exc_Msg
550 (G_PKG_NAME,
551 l_api_name
552 );
553 END IF;
554 FND_MSG_PUB.Count_And_Get
555 (p_count => x_msg_count,
556 p_data => x_msg_data
557 );
558 END Setup_Code;
559
560 PROCEDURE Setup_Combination
561 (p_api_version IN NUMBER ,
562 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
563 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
564 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
565 p_mode IN VARCHAR2 ,
566 p_combination_rec IN EAM_FailureCodes_PUB.eam_combination_rec_type,
567 x_return_status OUT NOCOPY VARCHAR2 ,
568 x_msg_count OUT NOCOPY NUMBER ,
569 x_msg_data OUT NOCOPY VARCHAR2 ,
570 x_combination_id OUT NOCOPY NUMBER
571 )
572 IS
573
574 l_api_name CONSTANT VARCHAR2(30) := 'Setup_Combination';
575 l_api_version CONSTANT NUMBER := 1.0;
576
577 l_set_id NUMBER;
578 l_combination_id NUMBER;
579 l_failure_exists NUMBER;
580 l_cause_exists NUMBER;
581 l_resolution_exists NUMBER;
582 failure_rec EAM_FailureCodes_PUB.eam_failurecode_rec_type;
583 cause_rec EAM_FailureCodes_PUB.eam_failurecode_rec_type;
584 resolution_rec EAM_FailureCodes_PUB.eam_failurecode_rec_type;
585 l_failure_code_status VARCHAR2(1);
586 l_cause_code_status VARCHAR2(1);
587 l_resolution_code_status VARCHAR2(1);
588 l_msg_count NUMBER;
589 l_msg_data VARCHAR2(4000);
590 l_created_by NUMBER;
591 l_creation_date DATE;
592 l_last_update_date DATE;
593 l_last_updated_by NUMBER;
594 l_last_update_login NUMBER;
595
596 CURSOR lock_combination IS
597 SELECT effective_end_date
598 FROM eam_failure_combinations
599 WHERE combination_id = l_combination_id
600 FOR UPDATE NOWAIT;
601
602 BEGIN
603 -- API savepoint
604 SAVEPOINT Setup_Combination_PVT;
605
606 -- check for call compatibility.
607 IF NOT FND_API.Compatible_API_Call (l_api_version,
608 p_api_version,
609 l_api_name,
610 G_PKG_NAME )
611 THEN
612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
613 END IF;
614
615 -- Initialize message list if p_init_msg_list is set to TRUE.
616 IF FND_API.to_Boolean( p_init_msg_list ) THEN
617 FND_MSG_PUB.initialize;
618 END IF;
619
623 -- Validate Combination Info passed
620 -- Initialize API return status to success
621 x_return_status := FND_API.G_RET_STS_SUCCESS;
622
624 Validate_Combination (p_mode, p_validation_level, p_combination_rec,
625 l_set_id,l_failure_exists, l_cause_exists,
626 l_resolution_exists, l_combination_id);
627 IF (p_combination_rec.last_update_date is null) THEN
628 l_created_by := fnd_global.user_id;
629 l_creation_date := SYSDATE;
630 l_last_update_date := SYSDATE;
631 l_last_updated_by := fnd_global.user_id;
632 l_last_update_login := NULL;
633 ELSE
634 l_created_by := p_combination_rec.created_by;
635 l_creation_date := p_combination_rec.creation_date;
636 l_last_update_date := p_combination_rec.last_update_date;
637 l_last_updated_by := p_combination_rec.last_updated_by;
638 l_last_update_login := p_combination_rec.last_update_login;
639 END IF;
640
641 IF (p_mode = 'C') THEN
642
643 l_failure_code_status := FND_API.G_RET_STS_SUCCESS;
644 l_cause_code_status := FND_API.G_RET_STS_SUCCESS;
645 l_resolution_code_status := FND_API.G_RET_STS_SUCCESS;
646
647 /** Commented since dynamic code creation is not allowed ** Bug#5070342
648 IF (l_failure_exists = 0) THEN
649 -- create failure code
650 failure_rec.code_type := EAM_FailureCodes_PUB.G_FAILURE_CODE;
651 failure_rec.code := p_combination_rec.failure_code;
652 failure_rec.description:= p_combination_rec.failure_description;
653 Setup_Code (p_api_version => 1.0,
654 p_init_msg_list => p_init_msg_list,
655 p_commit => FND_API.G_FALSE,
656 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
657 p_mode => 'C',
658 p_failurecode_rec => failure_rec,
659 x_return_status => l_failure_code_status,
660 x_msg_count => l_msg_count,
661 x_msg_data => l_msg_data);
662 END IF;
663
664 l_cause_code_status := FND_API.G_RET_STS_SUCCESS;
665 IF (l_cause_exists = 0) THEN
666 -- create cause code
667 cause_rec.code_type := EAM_FailureCodes_PUB.G_CAUSE_CODE;
668 cause_rec.code := p_combination_rec.cause_code;
669 cause_rec.description:= p_combination_rec.cause_description;
670 Setup_Code (p_api_version => 1.0,
671 p_init_msg_list => p_init_msg_list,
672 p_commit => FND_API.G_FALSE,
673 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
674 p_mode => 'C',
675 p_failurecode_rec => cause_rec,
676 x_return_status => l_cause_code_status,
677 x_msg_count => l_msg_count,
678 x_msg_data => l_msg_data);
679 END IF;
680
681 l_resolution_code_status := FND_API.G_RET_STS_SUCCESS;
682 IF (l_resolution_exists = 0) THEN
683 -- create resolution code
684 resolution_rec.code_type := EAM_FailureCodes_PUB.G_RESOLUTION_CODE;
685 resolution_rec.code := p_combination_rec.resolution_code;
686 resolution_rec.description:= p_combination_rec.resolution_description;
687 Setup_Code (p_api_version => 1.0,
688 p_init_msg_list => p_init_msg_list,
689 p_commit => FND_API.G_FALSE,
690 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
691 p_mode => 'C',
692 p_failurecode_rec => resolution_rec,
693 x_return_status => l_resolution_code_status,
694 x_msg_count => l_msg_count,
695 x_msg_data => l_msg_data);
696 END IF;
697 **/
698
699 IF (l_failure_code_status = FND_API.G_RET_STS_SUCCESS AND
700 l_cause_code_status = FND_API.G_RET_STS_SUCCESS AND
701 l_resolution_code_status = FND_API.G_RET_STS_SUCCESS) THEN
702
703 -- Insert into eam failure combinations
704 INSERT INTO eam_failure_combinations
705 (combination_id ,
706 set_id ,
707 failure_code ,
708 cause_code ,
709 resolution_code ,
710 effective_end_date ,
711 created_by ,
712 creation_date ,
713 last_update_date ,
714 last_updated_by ,
715 last_update_login)
716 VALUES (eam_failure_combinations_s.nextval ,
717 l_set_id ,
718 p_combination_rec.failure_code ,
719 p_combination_rec.cause_code ,
720 p_combination_rec.resolution_code ,
721 p_combination_rec.effective_end_date,
722 l_created_by ,
723 l_creation_date ,
724 l_last_update_date ,
725 l_last_updated_by ,
726 l_last_update_login)
727 RETURNING combination_id INTO l_combination_id;
728
729 END IF;
730
731 ELSIF (p_mode = 'U') THEN
732 -- update eam failure combinations
733 BEGIN
734 OPEN lock_combination;
735
736 UPDATE eam_failure_combinations
737 SET effective_end_date =
738 decode(p_combination_rec.effective_end_date,
742 last_update_date = l_last_update_date,
739 NULL, effective_end_date,
740 FND_API.G_MISS_DATE, NULL,
741 p_combination_rec.effective_end_date),
743 last_updated_by = l_last_updated_by,
744 last_update_login = l_last_update_login
745 WHERE combination_id = l_combination_id;
746
747 CLOSE lock_combination;
748 EXCEPTION
749 WHEN G_LOCKROW_EXCEPTION THEN
750 Raise_Error ('EAM_ROW_LOCKED',NULL,NULL);
751 END;
752 ELSIF (p_mode = 'D') THEN
753 -- delete from eam failure combinations
754 DELETE FROM eam_failure_combinations
755 WHERE combination_id = l_combination_id;
756 END IF;
757
758 x_combination_id := l_combination_id;
759 -- Standard check of p_commit.
760 IF FND_API.To_Boolean( p_commit ) THEN
761 COMMIT WORK;
762 END IF;
763
764 -- call to get message count and if count is 1, get message info.
765 FND_MSG_PUB.Count_And_Get
766 (p_count => x_msg_count,
767 p_data => x_msg_data
768 );
769 EXCEPTION
770 WHEN FND_API.G_EXC_ERROR THEN
771 ROLLBACK TO Setup_Combination_PVT;
772 x_return_status := FND_API.G_RET_STS_ERROR ;
773 FND_MSG_PUB.Count_And_Get
774 (p_count => x_msg_count ,
775 p_data => x_msg_data
776 );
777 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
778 ROLLBACK TO Setup_Combination_PVT;
779 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
780 FND_MSG_PUB.Count_And_Get
781 (p_count => x_msg_count,
782 p_data => x_msg_data
783 );
784 WHEN OTHERS THEN
785 ROLLBACK TO Setup_Combination_PVT;
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
787 IF FND_MSG_PUB.Check_Msg_Level
788 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
789 THEN
790 FND_MSG_PUB.Add_Exc_Msg
791 (G_PKG_NAME,
792 l_api_name
793 );
794 END IF;
795 FND_MSG_PUB.Count_And_Get
796 (p_count => x_msg_count,
797 p_data => x_msg_data
798 );
799 END Setup_Combination;
800
801 PROCEDURE Copy_FailureSet
802 (p_api_version IN NUMBER ,
803 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
804 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
805 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
806 p_source_set_id IN NUMBER ,
807 p_destination_set_id IN NUMBER ,
808 x_return_status OUT NOCOPY VARCHAR2 ,
809 x_msg_count OUT NOCOPY NUMBER ,
810 x_msg_data OUT NOCOPY VARCHAR2
811 )
812 IS
813 l_api_name CONSTANT VARCHAR2(30) := 'Copy_FailureSet';
814 l_api_version CONSTANT NUMBER := 1.0;
815
816 BEGIN
817 -- API savepoint
818 SAVEPOINT Copy_FailureSet_PVT;
819
820 -- check for call compatibility.
821 IF NOT FND_API.Compatible_API_Call (l_api_version,
822 p_api_version,
823 l_api_name,
824 G_PKG_NAME )
825 THEN
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827 END IF;
828
829 -- Initialize message list if p_init_msg_list is set to TRUE.
830 IF FND_API.to_Boolean( p_init_msg_list ) THEN
831 FND_MSG_PUB.initialize;
832 END IF;
833
834 -- Initialize API return status to success
835 x_return_status := FND_API.G_RET_STS_SUCCESS;
836
837 -- Copy active combinations from source set to destination set
838 INSERT INTO eam_failure_combinations
839 (combination_id ,
840 set_id ,
841 failure_code ,
842 cause_code ,
843 resolution_code ,
844 effective_end_date ,
845 created_by ,
846 creation_date ,
847 last_update_date ,
848 last_updated_by ,
849 last_update_login)
850 SELECT eam_failure_combinations_s.nextval,
851 p_destination_set_id ,
852 efc.failure_code ,
853 efc.cause_code ,
854 efc.resolution_code ,
855 efc.effective_end_date,
856 fnd_global.user_id,
857 SYSDATE,
858 SYSDATE,
859 fnd_global.user_id,
860 NULL
861 FROM eam_failure_combinations efc
862 WHERE set_id = p_source_set_id
863 AND effective_end_date IS NULL
864 AND NOT EXISTS
865 (SELECT 1
866 FROM eam_failure_combinations efc2
867 WHERE efc2.set_id = p_destination_set_id
868 AND efc2.failure_code = efc.failure_code
869 AND efc2.cause_code = efc.cause_code
870 AND efc2.resolution_code = efc2.resolution_code);
871
872 -- Standard check of p_commit.
873 IF FND_API.To_Boolean( p_commit ) THEN
874 COMMIT WORK;
875 END IF;
876
877 -- call to get message count and if count is 1, get message info.
878 FND_MSG_PUB.Count_And_Get
879 (p_count => x_msg_count,
883 EXCEPTION
880 p_data => x_msg_data
881 );
882
884 WHEN FND_API.G_EXC_ERROR THEN
885 ROLLBACK TO Copy_FailureSet_PVT;
886 x_return_status := FND_API.G_RET_STS_ERROR ;
887 FND_MSG_PUB.Count_And_Get
888 (p_count => x_msg_count ,
889 p_data => x_msg_data
890 );
891 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
892 ROLLBACK TO Copy_FailureSet_PVT;
893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
894 FND_MSG_PUB.Count_And_Get
895 (p_count => x_msg_count,
896 p_data => x_msg_data
897 );
898 WHEN OTHERS THEN
899 ROLLBACK TO Copy_FailureSet_PVT;
900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
901 IF FND_MSG_PUB.Check_Msg_Level
902 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
903 THEN
904 FND_MSG_PUB.Add_Exc_Msg
905 (G_PKG_NAME,
906 l_api_name
907 );
908 END IF;
909 FND_MSG_PUB.Count_And_Get
910 (p_count => x_msg_count,
911 p_data => x_msg_data
912 );
913 END Copy_FailureSet;
914
915 PROCEDURE Setup_Code_JSP
916 (p_mode IN VARCHAR2,
917 p_code_type IN NUMBER ,
918 p_code IN VARCHAR2,
919 p_description IN VARCHAR2,
920 p_effective_end_date IN DATE ,
921 p_stored_last_upd_date IN DATE ,
922 x_return_status OUT NOCOPY VARCHAR2,
923 x_msg_count OUT NOCOPY NUMBER ,
924 x_msg_data OUT NOCOPY VARCHAR2
925 ) IS
926 l_failurecode_rec EAM_FailureCodes_PUB.eam_failurecode_rec_type;
927 BEGIN
928 l_failurecode_rec.code_type := p_code_type;
929 l_failurecode_rec.code := p_code;
930 l_failurecode_rec.stored_last_upd_date := p_stored_last_upd_date;
931
932
933 IF (p_mode = 'U' AND p_effective_end_date IS NULL) THEN
934 l_failurecode_rec.effective_end_date := FND_API.G_MISS_DATE;
935 ELSE
936 l_failurecode_rec.effective_end_date := p_effective_end_date;
937 END IF;
938
939 IF (p_mode = 'U' AND p_description IS NULL) THEN
940 l_failurecode_rec.description := FND_API.G_MISS_CHAR;
941 ELSE
942 l_failurecode_rec.description:= p_description;
943 END IF;
944
945 Setup_Code
946 (p_api_version => 1.0,
947 p_init_msg_list => FND_API.G_TRUE,
948 p_commit => FND_API.G_FALSE,
949 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
950 p_mode => p_mode,
951 p_failurecode_rec => l_failurecode_rec,
952 x_return_status => x_return_status,
953 x_msg_count => x_msg_count,
954 x_msg_data => x_msg_data
955 );
956 END Setup_Code_JSP;
957
958 PROCEDURE Setup_Combination_JSP
959 (p_mode IN VARCHAR2 ,
960 p_set_id IN NUMBER ,
961 p_set_name IN VARCHAR2 ,
962 p_failure_code IN VARCHAR2 ,
963 p_failure_description IN VARCHAR2 ,
964 p_cause_code IN VARCHAR2 ,
965 p_cause_description IN VARCHAR2 ,
966 p_resolution_code IN VARCHAR2 ,
967 p_resolution_description IN VARCHAR2 ,
968 p_effective_end_date IN DATE ,
969 p_combination_id IN NUMBER ,
970 p_stored_last_upd_date IN DATE ,
971 p_created_by IN NUMBER ,
972 p_creation_date IN DATE ,
973 p_last_update_date IN DATE ,
974 p_last_updated_by IN NUMBER ,
975 p_last_update_login IN NUMBER ,
976 x_return_status OUT NOCOPY VARCHAR2,
977 x_msg_count OUT NOCOPY NUMBER ,
978 x_msg_data OUT NOCOPY VARCHAR2,
979 x_combination_id OUT NOCOPY NUMBER
980 ) IS
981 l_combination_rec EAM_FailureCodes_PUB.eam_combination_rec_type;
982 BEGIN
983 l_combination_rec.set_id := p_set_id;
984 l_combination_rec.set_name := p_set_name;
985 l_combination_rec.failure_code := p_failure_code;
986 l_combination_rec.failure_description := p_failure_description;
987 l_combination_rec.cause_code := p_cause_code;
988 l_combination_rec.cause_description := p_cause_description;
989 l_combination_rec.resolution_code := p_resolution_code;
990 l_combination_rec.resolution_description := p_resolution_description;
991 l_combination_rec.combination_id := p_combination_id;
992 l_combination_rec.stored_last_upd_date := p_stored_last_upd_date;
993 l_combination_rec.created_by := p_created_by;
994 l_combination_rec.creation_date := p_creation_date;
995 l_combination_rec.last_update_date := p_last_update_date;
996 l_combination_rec.last_updated_by := p_last_updated_by;
997 l_combination_rec.last_update_login := p_last_update_login;
998
999 IF (p_mode = 'U' AND p_effective_end_date IS NULL) THEN
1000 l_combination_rec.effective_end_date := FND_API.G_MISS_DATE;
1001 ELSE
1002 l_combination_rec.effective_end_date := p_effective_end_date;
1003 END IF;
1004
1005 Setup_Combination
1006 (p_api_version => 1.0 ,
1007 p_init_msg_list => FND_API.G_TRUE ,
1008 p_commit => FND_API.G_FALSE ,
1009 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1010 p_mode => p_mode ,
1014 x_msg_data => x_msg_data ,
1011 p_combination_rec => l_combination_rec ,
1012 x_return_status => x_return_status ,
1013 x_msg_count => x_msg_count ,
1015 x_combination_id => x_combination_id
1016 );
1017 END Setup_Combination_JSP;
1018
1019 PROCEDURE Lock_Code_JSP
1020 (p_code_type IN NUMBER ,
1021 p_code IN VARCHAR2,
1022 p_last_update_date IN DATE ,
1023 x_return_status OUT NOCOPY VARCHAR2,
1024 x_msg_count OUT NOCOPY NUMBER ,
1025 x_msg_data OUT NOCOPY VARCHAR2
1026 ) IS
1027 CURSOR lock_fc IS
1028 SELECT description, effective_end_date, last_update_date
1029 FROM eam_failure_codes
1030 WHERE failure_code = p_code
1031 FOR UPDATE NOWAIT;
1032
1033 CURSOR lock_cc IS
1034 SELECT description, effective_end_date, last_update_date
1035 FROM eam_cause_codes
1036 WHERE cause_code = p_code
1037 FOR UPDATE NOWAIT;
1038
1039 CURSOR lock_rc IS
1040 SELECT description, effective_end_date, last_update_date
1041 FROM eam_resolution_codes
1042 WHERE resolution_code = p_code
1043 FOR UPDATE NOWAIT;
1044
1045 l_description VARCHAR2(200);
1046 l_end_date DATE;
1047 l_last_update_date DATE;
1048 l_rowcount NUMBER;
1049
1050 BEGIN
1051 FND_MSG_PUB.initialize;
1052
1053 -- Initialize API return status to success
1054 x_return_status := FND_API.G_RET_STS_SUCCESS;
1055 BEGIN
1056 IF (p_code_type = EAM_FailureCodes_PUB.G_FAILURE_CODE) THEN
1057 OPEN lock_fc;
1058 FETCH lock_fc
1059 INTO l_description, l_end_date, l_last_update_date;
1060 l_rowcount := lock_fc%ROWCOUNT;
1061 CLOSE lock_fc;
1062 ELSIF (p_code_type = EAM_FailureCodes_PUB.G_CAUSE_CODE) THEN
1063 OPEN lock_cc;
1064 FETCH lock_cc
1065 INTO l_description, l_end_date, l_last_update_date;
1066 l_rowcount := lock_cc%ROWCOUNT;
1067 CLOSE lock_cc;
1068 ELSIF (p_code_type = EAM_FailureCodes_PUB.G_RESOLUTION_CODE) THEN
1069 OPEN lock_rc;
1070 FETCH lock_rc
1071 INTO l_description, l_end_date, l_last_update_date;
1072 l_rowcount := lock_rc%ROWCOUNT;
1073 CLOSE lock_rc;
1074 END IF;
1075 IF (p_last_update_date <> l_last_update_date) THEN
1076 FND_MESSAGE.SET_NAME ('FND', 'FND_RECORD_CHANGED_ERROR');
1077 FND_MSG_PUB.ADD;
1078 RAISE FND_API.G_EXC_ERROR;
1079 END IF;
1080 IF (l_rowcount = 0) THEN
1081 FND_MESSAGE.SET_NAME ('FND', 'FND_RECORD_DELETED_ERROR');
1082 FND_MSG_PUB.ADD;
1083 RAISE FND_API.G_EXC_ERROR;
1084 END IF;
1085 EXCEPTION
1086 WHEN G_LOCKROW_EXCEPTION THEN
1087 FND_MESSAGE.SET_NAME ('FND', 'FND_LOCK_RECORD_ERROR');
1088 FND_MSG_PUB.ADD;
1089 RAISE FND_API.G_EXC_ERROR;
1090 END;
1091 EXCEPTION
1092 WHEN FND_API.G_EXC_ERROR THEN
1093 x_return_status := FND_API.G_RET_STS_ERROR ;
1094 WHEN OTHERS THEN
1095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1096 IF FND_MSG_PUB.Check_Msg_Level
1097 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1098 THEN
1099 FND_MSG_PUB.Add_Exc_Msg
1100 (G_PKG_NAME,
1101 'Lock_Code_JSP'
1102 );
1103 END IF;
1104 END Lock_Code_JSP;
1105
1106 PROCEDURE Lock_Combination_JSP
1107 (p_combination_id IN NUMBER ,
1108 p_last_update_date IN DATE ,
1109 x_return_status OUT NOCOPY VARCHAR2,
1110 x_msg_count OUT NOCOPY NUMBER ,
1111 x_msg_data OUT NOCOPY VARCHAR2
1112 ) IS
1113 CURSOR lock_combination IS
1114 SELECT effective_end_date,last_update_date
1115 FROM eam_failure_combinations
1116 WHERE combination_id = p_combination_id
1117 FOR UPDATE NOWAIT;
1118
1119 l_end_date DATE;
1120 l_last_update_date DATE;
1121 l_rowcount NUMBER;
1122
1123 BEGIN
1124 FND_MSG_PUB.initialize;
1125
1126 -- Initialize API return status to success
1127 x_return_status := FND_API.G_RET_STS_SUCCESS;
1128 BEGIN
1129 OPEN lock_combination;
1130 FETCH lock_combination
1131 INTO l_end_date, l_last_update_date;
1132 l_rowcount := lock_combination%ROWCOUNT;
1133 CLOSE lock_combination;
1134 IF (p_last_update_date <> l_last_update_date) THEN
1135 FND_MESSAGE.SET_NAME ('FND', 'FND_RECORD_CHANGED_ERROR');
1136 FND_MSG_PUB.ADD;
1137 RAISE FND_API.G_EXC_ERROR;
1138 END IF;
1139 IF (l_rowcount = 0) THEN
1140 FND_MESSAGE.SET_NAME ('FND', 'FND_RECORD_DELETED_ERROR');
1141 FND_MSG_PUB.ADD;
1142 RAISE FND_API.G_EXC_ERROR;
1143 END IF;
1144 EXCEPTION
1145 WHEN G_LOCKROW_EXCEPTION THEN
1146 FND_MESSAGE.SET_NAME ('FND', 'FND_LOCK_RECORD_ERROR');
1147 FND_MSG_PUB.ADD;
1148 RAISE FND_API.G_EXC_ERROR;
1149 END;
1150 EXCEPTION
1151 WHEN FND_API.G_EXC_ERROR THEN
1152 x_return_status := FND_API.G_RET_STS_ERROR ;
1153 WHEN OTHERS THEN
1154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1158 FND_MSG_PUB.Add_Exc_Msg
1155 IF FND_MSG_PUB.Check_Msg_Level
1156 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1157 THEN
1159 (G_PKG_NAME,
1160 'Lock_Code_JSP'
1161 );
1162 END IF;
1163 END Lock_Combination_JSP;
1164
1165 END EAM_FailureCodes_PVT;