[Home] [Help]
PACKAGE BODY: APPS.EAM_FAILURESETS_PVT
Source
1 PACKAGE BODY EAM_FailureSets_PVT AS
2 /* $Header: EAMVFSPB.pls 120.0 2006/03/08 07:16:28 sshahid noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_FailureSets_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 FND_MESSAGE.SET_NAME ('EAM', p_error);
13 IF (p_token IS NOT NULL) THEN
14 FND_MESSAGE.SET_TOKEN (p_token, p_token_value);
15 END IF;
16 FND_MSG_PUB.ADD;
17 RAISE FND_API.G_EXC_ERROR;
18 END Raise_Error;
19
20 -- Procedure to Validate FailureSet Info passed in various modes
21 -- to Setup_FailureSet API
22 PROCEDURE Validate_FailureSet
23 (p_mode IN VARCHAR2,
24 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
25 p_failureset_rec IN EAM_FailureSets_PUB.eam_failureset_rec_type,
26 x_set_id OUT NOCOPY NUMBER)
27 IS
28 l_set_count NUMBER;
29 l_old_description VARCHAR2(240);
30 l_old_eff_end_date DATE;
31 l_old_last_upd_date DATE;
32 l_set_id NUMBER;
33 l_inventory_item_id NUMBER;
34 l_item VARCHAR2(240);
35
36 BEGIN
37
38 -- Initialize
39 l_set_count := 0;
40
41 IF (p_mode = 'C') THEN
42
43 IF (p_failureset_rec.set_name IS NULL OR
44 p_failureset_rec.set_name = FND_API.G_MISS_CHAR) THEN
45 Raise_Error ('EAM_FAILURESET_INVALID', 'FAILURE_SET', NULL);
46 END IF;
47
48 SELECT count(1)
49 INTO l_set_count
50 FROM eam_failure_sets
51 WHERE set_name = p_failureset_rec.set_name;
52
53 IF (l_set_count > 0) THEN
54 Raise_Error ('EAM_FAILURESET_EXISTS', 'FAILURE_SET', p_failureset_rec.set_name);
55 END IF;
56
57 ELSIF (p_mode = 'U') THEN
58 IF (p_failureset_rec.set_id IS NOT NULL) THEN
59 l_set_id := p_failureset_rec.set_id ;
60 ELSIF (p_failureset_rec.set_name IS NOT NULL) THEN
61 SELECT set_id
62 INTO l_set_id
63 FROM eam_failure_sets
64 WHERE set_name = p_failureset_rec.set_name;
65 ELSE
66 Raise_Error ('EAM_FAILURESET_INVALID', 'FAILURE_SET', l_set_id || ' - ' || p_failureset_rec.set_name);
67 END IF;
68 x_set_id := l_set_id;
69 SELECT description, effective_end_date, last_update_date
70 INTO l_old_description, l_old_eff_end_date, l_old_last_upd_date
71 FROM eam_failure_sets
72 WHERE set_id = l_set_id;
73
74 /*
75 IF (l_old_eff_end_date IS NOT NULL AND
76 trunc(SYSDATE) > trunc(l_old_eff_end_date)) THEN
77 Raise_Error ('EAM_FAILURESET_INACTIVE');
78 END IF;
79 */
80
81 IF (p_failureset_rec.stored_last_upd_date IS NOT NULL AND
82 to_char(p_failureset_rec.stored_last_upd_date,'dd-mon-rrrr hh24:mi:ss') <>
83 to_char(l_old_last_upd_date,'dd-mon-rrrr hh24:mi:ss')) THEN
84 Raise_Error ('EAM_FAILURESET_CHANGED', 'FAILURE_SET', l_set_id || ' - ' || p_failureset_rec.set_name);
85 END IF;
86
87
88 IF ((p_failureset_rec.effective_end_date IS NOT NULL AND l_old_eff_end_date IS NULL) OR
89 (p_failureset_rec.effective_end_date = FND_API.G_MISS_DATE AND l_old_eff_end_date IS NOT NULL) OR
90 (p_failureset_rec.effective_end_date IS NOT NULL AND l_old_eff_end_date IS NOT NULL AND
91 p_failureset_rec.effective_end_date <> l_old_eff_end_date)) THEN
92 BEGIN
93 SELECT efsa.inventory_item_id, msik.concatenated_segments
94 INTO l_inventory_item_id, l_item
95 FROM eam_failure_set_associations efsa,
96 mtl_system_items_kfv msik
97 WHERE efsa.set_id = l_set_id
98 AND msik.inventory_item_id = efsa.inventory_item_id
99 AND EXISTS
100 (SELECT 1
101 FROM eam_failure_set_associations efsa1,
102 eam_failure_sets efs
103 WHERE efsa1.inventory_item_id = efsa.inventory_item_id
104 AND efsa1.set_id <> efsa.set_id
105 AND efs.set_id = efsa1.set_id
106 AND (efs.effective_end_date IS NULL OR
107 efs.effective_end_date > NVL(p_failureset_rec.effective_end_date, efs.effective_end_date - 1)))
108 AND rownum < 2;
109
110 Raise_Error ('EAM_SET_ASSOCIATION_EXISTS', 'ITEM', l_inventory_item_id || ' - ' || l_item);
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 l_item := NULL;
114 END;
115 END IF;
116 ELSE
117 Raise_Error ('Invalid Mode - Valid values are ''C'' and ''D''',NULL,NULL);
118 END IF;
119 EXCEPTION
120 WHEN NO_DATA_FOUND THEN
121 Raise_Error ('EAM_FAILURESET_INVALID', 'FAILURE_SET', l_set_id || ' - ' || p_failureset_rec.set_name);
122 END Validate_FailureSet;
123
124 -- Procedure to Validate SetAssociation Info passed in various modes
125 -- to Setup_SetAssociation API
126 PROCEDURE Validate_SetAssociation
127 (p_mode IN VARCHAR2,
128 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
129 p_association_rec IN EAM_FailureSets_PUB.eam_set_association_rec_type,
130 x_set_id OUT NOCOPY NUMBER)
131 IS
132
133 l_set_end_date DATE;
134 l_item_exists NUMBER;
135 l_association_exists NUMBER;
136 l_open_wo_exists NUMBER;
137 l_set_id NUMBER;
138 l_maintained_group VARCHAR2(800);
139
140 BEGIN
141 l_maintained_group := NULL;
142 -- Validate Failure Set
143 BEGIN
144 IF (p_association_rec.set_id IS NOT NULL) THEN
145 l_set_id := p_association_rec.set_id ;
146 ELSIF (p_association_rec.set_name IS NOT NULL) THEN
147 SELECT set_id
148 INTO l_set_id
149 FROM eam_failure_sets
150 WHERE set_name = p_association_rec.set_name;
151 END IF;
152
153 SELECT effective_end_date
154 INTO l_set_end_date
155 FROM eam_failure_sets
156 WHERE set_id = l_set_id;
157
158 IF (l_set_end_date IS NOT NULL AND
159 trunc(l_set_end_date) < trunc(sysdate)) THEN
160 Raise_Error ('EAM_FAILURESET_INACTIVE', 'FAILURE_SET', l_set_id || ' - ' || p_association_rec.set_name);
161 END IF;
162 x_set_id := l_set_id;
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN
165 Raise_Error ('EAM_FAILURESET_INVALID', 'FAILURE_SET', l_set_id || ' - ' || p_association_rec.set_name);
166 END;
167
168 -- Validate asset group/rebuildable
169 SELECT count(1)
170 INTO l_item_exists
171 FROM mtl_system_items
172 WHERE inventory_item_id = p_association_rec.inventory_item_id
173 AND eam_item_type IN (1,3)
174 AND rownum < 2;
175
176 BEGIN
177 SELECT concatenated_segments
178 INTO l_maintained_group
179 FROM mtl_system_items_kfv
180 WHERE inventory_item_id = p_association_rec.inventory_item_id
181 AND ROWNUM < 2;
182 EXCEPTION
183 WHEN NO_DATA_FOUND THEN
184 l_maintained_group := NULL;
185 END;
186
187 IF (l_item_exists = 0) THEN
188 IF l_maintained_group IS NULL THEN
189 Raise_Error ('EAM_INVALID_ITEM', 'ITEM_ID',p_association_rec.inventory_item_id );
190 ELSE
191 Raise_Error ('EAM_INVALID_ITEM', 'ITEM_ID',l_maintained_group );
192 END IF;
193 END IF;
194
195 -- check for existing set association
196 SELECT count(1)
197 INTO l_association_exists
198 FROM eam_failure_set_associations
199 WHERE set_id = l_set_id
200 AND inventory_item_id = p_association_rec.inventory_item_id
201 AND effective_end_date IS NULL;
202
203 IF (p_mode = 'C') THEN
204
205 IF (l_association_exists > 0) THEN
206 Raise_Error ('EAM_SET_ASSOCIATION_EXISTS','ASSOCIATION', l_set_id || ' - ' || p_association_rec.set_name
207 || ' - ' || p_association_rec.inventory_item_id);
208 END IF;
209
210 l_association_exists := 0;
211 SELECT count(1)
212 INTO l_association_exists
213 FROM eam_failure_set_associations efsa,
214 eam_failure_sets efs
215 WHERE efsa.inventory_item_id = p_association_rec.inventory_item_id
216 AND efsa.set_id <> l_set_id
217 AND efs.set_id = efsa.set_id
218 AND (efs.effective_end_date IS NULL OR
219 efs.effective_end_date >= SYSDATE)
220 AND (efsa.effective_end_date IS NULL OR
221 efsa.effective_end_date >= SYSDATE);
222
223 IF (l_association_exists > 0) THEN
224 IF l_maintained_group IS NULL THEN
225 Raise_Error ('EAM_ANOTHER_ASSOCIATION_EXISTS', 'ASSOCIATION',p_association_rec.inventory_item_id );
226 ELSE
227 Raise_Error ('EAM_ANOTHER_ASSOCIATION_EXISTS', 'ASSOCIATION',l_maintained_group );
228 END IF;
229 END IF;
230
231 ELSIF (p_mode IN ('U','D') ) THEN
232
233 IF (l_association_exists = 0) THEN
234 Raise_Error ('EAM_SET_ASSOCIATION_INVALID', 'ASSOCIATION', l_set_id || ' - ' || p_association_rec.set_name
235 || ' - ' || p_association_rec.inventory_item_id);
236 END IF;
237 -- Check if the failure set is used in any work order with mandatory flag
238 -- checked and status other than Complete, Complete No charges,
239 -- Cancelled, Closed, Failed close and Pending close else throw
240 -- a message EAM_SET_ASSOCIATION_USED
241 -- This exception would not result in an error, rather it would throw a
242 -- message.
243 l_open_wo_exists := 0;
244 IF (p_mode = 'D') THEN
245 SELECT count(1)
246 INTO l_open_wo_exists
247 FROM wip_discrete_jobs wdj,
248 eam_work_order_details ewod
249 WHERE (wdj.asset_group_id = p_association_rec.inventory_item_id
250 OR
251 wdj.rebuild_item_id = p_association_rec.inventory_item_id)
252 AND wdj.status_type NOT IN (4,5,7,12,14,15)
253 AND ewod.organization_id = wdj.organization_id
254 AND ewod.wip_entity_id = wdj.wip_entity_id
255 AND ewod.failure_code_required = 'Y'
256 AND rownum < 2;
257
258 IF (l_open_wo_exists = 1) THEN
259 FND_MESSAGE.SET_NAME ('EAM', 'EAM_SET_ASSOCIATION_USED');
260 FND_MSG_PUB.ADD;
261 END IF;
262
263 END IF;
264 END IF;
265 END Validate_SetAssociation;
266
267 PROCEDURE Setup_FailureSet
268 (p_api_version IN NUMBER ,
269 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
270 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
271 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
272 p_mode IN VARCHAR2 ,
273 p_failureset_rec IN EAM_FailureSets_PUB.eam_failureset_rec_type,
274 x_return_status OUT NOCOPY VARCHAR2 ,
275 x_msg_count OUT NOCOPY NUMBER ,
276 x_msg_data OUT NOCOPY VARCHAR2 ,
277 x_failureset_id OUT NOCOPY NUMBER
278 )
279 IS
280 l_api_name CONSTANT VARCHAR2(30) := 'Setup_FailureSet';
281 l_api_version CONSTANT NUMBER := 1.0;
282 l_failureset_id NUMBER;
283
284 CURSOR lock_set IS
285 SELECT description, effective_end_date
286 FROM eam_failure_sets
287 WHERE set_id = l_failureset_id
288 FOR UPDATE NOWAIT;
289
290 BEGIN
291 -- API savepoint
292 SAVEPOINT Setup_FailureSet_PVT;
293
294 -- check for call compatibility.
295 IF NOT FND_API.Compatible_API_Call (l_api_version,
296 p_api_version,
297 l_api_name,
298 G_PKG_NAME )
299 THEN
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301 END IF;
302
303 -- Initialize message list if p_init_msg_list is set to TRUE.
304 IF FND_API.to_Boolean( p_init_msg_list ) THEN
305 FND_MSG_PUB.initialize;
306 END IF;
307
308 -- Initialize API return status to success
309 x_return_status := FND_API.G_RET_STS_SUCCESS;
310
311 -- Validate Failure Set Info passed
312 Validate_FailureSet(p_mode, p_validation_level, p_failureset_rec, l_failureset_id);
313
314 IF (p_mode = 'C') THEN
315 -- Insert into eam failure sets
316 INSERT INTO eam_failure_sets
317 (set_id ,
318 set_name ,
319 description ,
320 effective_end_date,
321 created_by ,
322 creation_date ,
323 last_update_date ,
324 last_updated_by ,
325 last_update_login)
326 VALUES (eam_failuresets_s.nextval ,
327 p_failureset_rec.set_name ,
328 p_failureset_rec.description ,
329 p_failureset_rec.effective_end_date,
330 fnd_global.user_id,
331 SYSDATE,
332 SYSDATE,
333 fnd_global.user_id,
334 NULL)
335 RETURNING set_id INTO l_failureset_id ;
336
337 ELSIF (p_mode = 'U') THEN
338 -- update eam failure sets
339 BEGIN
340 OPEN lock_set;
341 UPDATE eam_failure_sets
342 SET description = decode(p_failureset_rec.description,
343 NULL, description,
344 FND_API.G_MISS_CHAR, NULL,
345 p_failureset_rec.description),
346 effective_end_date = decode(p_failureset_rec.effective_end_date,
347 NULL, effective_end_date,
348 FND_API.G_MISS_DATE, NULL,
349 p_failureset_rec.effective_end_date),
350 last_update_date = SYSDATE,
351 last_updated_by = fnd_global.user_id,
352 last_update_login = NULL
353 WHERE set_id = l_failureset_id;
354 CLOSE lock_set;
355 EXCEPTION
356 WHEN G_LOCKROW_EXCEPTION THEN
357 FND_MESSAGE.SET_NAME ('FND', 'FND_LOCK_RECORD_ERROR');
358 FND_MSG_PUB.ADD;
359 RAISE FND_API.G_EXC_ERROR;
360 END;
361 END IF;
362
363 x_failureset_id := l_failureset_id;
364 -- Standard check of p_commit.
365 IF FND_API.To_Boolean( p_commit ) THEN
366 COMMIT WORK;
367 END IF;
368
369 -- call to get message count and if count is 1, get message info.
370 FND_MSG_PUB.Count_And_Get
371 (p_count => x_msg_count,
372 p_data => x_msg_data
373 );
374 EXCEPTION
375 WHEN FND_API.G_EXC_ERROR THEN
376 ROLLBACK TO Setup_FailureSet_PVT;
377 x_return_status := FND_API.G_RET_STS_ERROR ;
378 FND_MSG_PUB.Count_And_Get
379 (p_count => x_msg_count ,
380 p_data => x_msg_data
381 );
382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
383 ROLLBACK TO Setup_FailureSet_PVT;
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
385 FND_MSG_PUB.Count_And_Get
386 (p_count => x_msg_count,
387 p_data => x_msg_data
388 );
389 WHEN OTHERS THEN
390 ROLLBACK TO Setup_FailureSet_PVT;
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
392 IF FND_MSG_PUB.Check_Msg_Level
393 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
394 THEN
395 FND_MSG_PUB.Add_Exc_Msg
396 (G_PKG_NAME,
397 l_api_name
398 );
399 END IF;
403 );
400 FND_MSG_PUB.Count_And_Get
401 (p_count => x_msg_count,
402 p_data => x_msg_data
404 END Setup_FailureSet;
405
406 PROCEDURE Setup_SetAssociation
407 (p_api_version IN NUMBER ,
408 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
409 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
410 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
411 p_mode IN VARCHAR2 ,
412 p_association_rec IN EAM_FailureSets_PUB.eam_set_association_rec_type,
413 x_return_status OUT NOCOPY VARCHAR2 ,
414 x_msg_count OUT NOCOPY NUMBER ,
415 x_msg_data OUT NOCOPY VARCHAR2
416 )
417 IS
418 l_api_name CONSTANT VARCHAR2(30) := 'Setup_SetAssociation';
419 l_api_version CONSTANT NUMBER := 1.0;
420 l_set_id NUMBER;
421 l_created_by NUMBER;
422 l_creation_date DATE;
423 l_last_update_date DATE;
424 l_last_updated_by NUMBER;
425 l_last_update_login NUMBER;
426
427 CURSOR lock_association(p_set_id NUMBER, p_item_id NUMBER) IS
428 SELECT failure_code_required, effective_end_date
429 FROM eam_failure_set_associations
430 WHERE set_id = p_set_id
431 AND inventory_item_id = p_item_id
432 AND effective_end_date IS NULL
433 FOR UPDATE NOWAIT;
434
435 BEGIN
436 -- API savepoint
437 SAVEPOINT Setup_SetAssociation_PVT;
438
439 -- check for call compatibility.
440 IF NOT FND_API.Compatible_API_Call (l_api_version,
441 p_api_version,
442 l_api_name,
443 G_PKG_NAME )
444 THEN
445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
446 END IF;
447
448 -- Initialize message list if p_init_msg_list is set to TRUE.
449 IF FND_API.to_Boolean( p_init_msg_list ) THEN
450 FND_MSG_PUB.initialize;
451 END IF;
452
453 -- Initialize API return status to success
454 x_return_status := FND_API.G_RET_STS_SUCCESS;
455
456 -- Validate Failure Set Association Info passed
457 Validate_SetAssociation(p_mode, p_validation_level, p_association_rec, l_set_id);
458
459 IF (p_association_rec.last_update_date is null) THEN
460 l_created_by := fnd_global.user_id;
461 l_creation_date := SYSDATE;
462 l_last_update_date := SYSDATE;
463 l_last_updated_by := fnd_global.user_id;
464 l_last_update_login := NULL;
465 ELSE
466 l_created_by := p_association_rec.created_by;
467 l_creation_date := p_association_rec.creation_date;
468 l_last_update_date := p_association_rec.last_update_date;
469 l_last_updated_by := p_association_rec.last_updated_by;
470 l_last_update_login := p_association_rec.last_update_login;
471 END IF;
472
473 IF (p_mode = 'C') THEN
474
475 UPDATE eam_failure_set_associations
476 SET effective_end_date = NULL,
477 last_update_date = l_last_update_date,
478 last_updated_by = l_last_updated_by,
479 last_update_login = l_last_update_login
480 WHERE set_id = l_set_id
481 AND inventory_item_id = p_association_rec.inventory_item_id;
482
483 IF (SQL%ROWCOUNT = 0) THEN
484 -- Insert into eam failure set associations
485 INSERT INTO eam_failure_set_associations
486 (set_id ,
487 inventory_item_id ,
488 failure_code_required,
489 created_by ,
490 creation_date ,
491 last_update_date ,
492 last_updated_by ,
493 last_update_login)
494 VALUES (l_set_id ,
495 p_association_rec.inventory_item_id ,
496 NVL(p_association_rec.failure_code_required,'N'),
497 l_created_by ,
498 l_creation_date ,
499 l_last_update_date ,
500 l_last_updated_by ,
501 l_last_update_login);
502 END IF;
503 ELSIF (p_mode IN ('U','D')) THEN
504 -- update eam failure set associations
505 BEGIN
506 OPEN lock_association(l_set_id, p_association_rec.inventory_item_id);
507 IF (p_mode = 'U') THEN
508
509 UPDATE eam_failure_set_associations
510 SET failure_code_required = decode(
511 p_association_rec.failure_code_required,
512 'Y','Y',
513 NULL, failure_code_required,
514 'N'),
515 last_update_date = l_last_update_date,
516 last_updated_by = l_last_updated_by,
517 last_update_login = l_last_update_login
518 WHERE set_id = l_set_id
519 AND inventory_item_id = p_association_rec.inventory_item_id
520 AND effective_end_date IS NULL;
521
522 ELSIF (p_mode = 'D') THEN
523
524 UPDATE eam_failure_set_associations
525 SET effective_end_date = SYSDATE,
526 last_update_date = l_last_update_date,
527 last_updated_by = l_last_updated_by,
528 last_update_login = l_last_update_login
529 WHERE set_id = l_set_id
530 AND inventory_item_id = p_association_rec.inventory_item_id;
531
535 WHEN G_LOCKROW_EXCEPTION THEN
532 END IF;
533 CLOSE lock_association;
534 EXCEPTION
536 FND_MESSAGE.SET_NAME ('FND', 'FND_LOCK_RECORD_ERROR');
537 FND_MSG_PUB.ADD;
538 RAISE FND_API.G_EXC_ERROR;
539 END;
540 END IF;
541
542 -- Standard check of p_commit.
543 IF FND_API.To_Boolean( p_commit ) THEN
544 COMMIT WORK;
545 END IF;
546
547 -- call to get message count and if count is 1, get message info.
548 FND_MSG_PUB.Count_And_Get
549 (p_count => x_msg_count,
550 p_data => x_msg_data
551 );
552 EXCEPTION
553 WHEN FND_API.G_EXC_ERROR THEN
554 ROLLBACK TO Setup_SetAssociation_PVT;
555 x_return_status := FND_API.G_RET_STS_ERROR ;
556 FND_MSG_PUB.Count_And_Get
557 (p_count => x_msg_count ,
558 p_data => x_msg_data
559 );
560 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
561 ROLLBACK TO Setup_SetAssociation_PVT;
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
563 FND_MSG_PUB.Count_And_Get
564 (p_count => x_msg_count,
565 p_data => x_msg_data
566 );
567 WHEN OTHERS THEN
568 ROLLBACK TO Setup_SetAssociation_PVT;
569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
570 IF FND_MSG_PUB.Check_Msg_Level
571 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
572 THEN
573 FND_MSG_PUB.Add_Exc_Msg
574 (G_PKG_NAME,
575 l_api_name
576 );
577 END IF;
578 FND_MSG_PUB.Count_And_Get
579 (p_count => x_msg_count,
580 p_data => x_msg_data
581 );
582 END Setup_SetAssociation;
583
584 PROCEDURE Setup_FailureSet_JSP
585 (p_mode IN VARCHAR2 ,
586 p_set_name IN VARCHAR2 ,
587 p_description IN VARCHAR2 ,
588 p_effective_end_date IN DATE ,
589 p_set_id IN NUMBER ,
590 p_stored_last_upd_date IN DATE ,
591 x_return_status OUT NOCOPY VARCHAR2,
592 x_msg_count OUT NOCOPY NUMBER ,
593 x_msg_data OUT NOCOPY VARCHAR2,
594 x_failureset_id OUT NOCOPY NUMBER
595 )
596 IS
597 l_failureset_rec EAM_FailureSets_PUB.eam_failureset_rec_type;
598 BEGIN
599 l_failureset_rec.set_name := p_set_name;
600 l_failureset_rec.set_id := p_set_id;
601 l_failureset_rec.stored_last_upd_date := p_stored_last_upd_date;
602
603 IF (p_mode = 'U' AND p_effective_end_date IS NULL) THEN
604 l_failureset_rec.effective_end_date := FND_API.G_MISS_DATE;
605 ELSE
606 l_failureset_rec.effective_end_date := p_effective_end_date;
607 END IF;
608 IF (p_mode = 'U' AND p_description IS NULL) THEN
609 l_failureset_rec.description := FND_API.G_MISS_CHAR;
610 ELSE
611 l_failureset_rec.description := p_description;
612 END IF;
613
614 Setup_FailureSet
615 (p_api_version => 1.0,
616 p_init_msg_list => FND_API.G_TRUE,
617 p_commit => FND_API.G_FALSE,
618 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
619 p_mode => p_mode ,
620 p_failureset_rec => l_failureset_rec,
621 x_return_status => x_return_status,
622 x_msg_count => x_msg_count,
623 x_msg_data => x_msg_data,
624 x_failureset_id => x_failureset_id
625 );
626
627 /* For updating the set_id for the copied rows from a failureset */
628 IF (p_mode = 'C')
629 THEN
630 UPDATE eam_failure_combinations
631 SET set_id = x_failureset_id
632 WHERE set_id = p_set_id;
633 END IF;
634
635 END Setup_FailureSet_JSP;
636
637 PROCEDURE Setup_SetAssociation_JSP
638 (p_mode IN VARCHAR2 ,
639 p_set_id IN NUMBER ,
640 p_set_name IN VARCHAR2 ,
641 p_inventory_item_id IN NUMBER ,
642 p_failure_code_required IN VARCHAR2 ,
643 p_effective_end_date IN DATE ,
644 p_stored_last_upd_date IN DATE ,
645 p_created_by IN NUMBER ,
646 p_creation_date IN DATE ,
647 p_last_update_date IN DATE ,
648 p_last_updated_by IN NUMBER ,
649 p_last_update_login IN NUMBER ,
650 x_return_status OUT NOCOPY VARCHAR2,
651 x_msg_count OUT NOCOPY NUMBER ,
652 x_msg_data OUT NOCOPY VARCHAR2
653 )
654 IS
655 l_association_rec EAM_FailureSets_PUB.eam_set_association_rec_type;
656 BEGIN
657 l_association_rec.set_id := p_set_id;
658 l_association_rec.set_name := p_set_name;
659 l_association_rec.inventory_item_id := p_inventory_item_id;
660 l_association_rec.failure_code_required := p_failure_code_required;
661 l_association_rec.effective_end_date := p_effective_end_date;
662 l_association_rec.stored_last_upd_date:= p_stored_last_upd_date;
663 l_association_rec.created_by := p_created_by;
664 l_association_rec.creation_date := p_creation_date;
665 l_association_rec.last_update_date := p_last_update_date;
666 l_association_rec.last_updated_by := p_last_updated_by;
667 l_association_rec.last_update_login := p_last_update_login;
668
669 Setup_SetAssociation
670 (p_api_version => 1.0,
671 p_init_msg_list => FND_API.G_TRUE,
672 p_commit => FND_API.G_FALSE,
673 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
674 p_mode => p_mode ,
675 p_association_rec => l_association_rec,
676 x_return_status => x_return_status,
677 x_msg_count => x_msg_count,
678 x_msg_data => x_msg_data
679 );
680 END Setup_SetAssociation_JSP;
681
682 PROCEDURE Lock_SetAssociation_JSP
683 (p_set_id IN NUMBER,
684 p_item_id IN NUMBER,
685 p_last_update_date IN DATE ,
686 x_return_status OUT NOCOPY VARCHAR2,
687 x_msg_count OUT NOCOPY NUMBER ,
688 x_msg_data OUT NOCOPY VARCHAR2
689 )
690 IS
691 CURSOR lock_association(c_set_id NUMBER, c_item_id NUMBER) IS
692 SELECT failure_code_required, effective_end_date, last_update_date
693 FROM eam_failure_set_associations
694 WHERE set_id = c_set_id
695 AND inventory_item_id = c_item_id
696 AND effective_end_date IS NULL
697 FOR UPDATE NOWAIT;
698 l_fcr VARCHAR2(1);
699 l_end_date DATE;
700 l_last_update_date DATE;
701 l_rowcount NUMBER;
702 BEGIN
703 FND_MSG_PUB.initialize;
704
705 -- Initialize API return status to success
706 x_return_status := FND_API.G_RET_STS_SUCCESS;
707 BEGIN
708 OPEN lock_association(p_set_id, p_item_id);
709 FETCH lock_association
710 INTO l_fcr, l_end_date, l_last_update_date;
711 l_rowcount := lock_association%ROWCOUNT;
712 CLOSE lock_association;
713 IF (p_last_update_date <> l_last_update_date) THEN
714 FND_MESSAGE.SET_NAME ('FND', 'FND_RECORD_CHANGED_ERROR');
715 FND_MSG_PUB.ADD;
716 RAISE FND_API.G_EXC_ERROR;
717 END IF;
718 IF (l_rowcount = 0) THEN
719 FND_MESSAGE.SET_NAME ('FND', 'FND_RECORD_DELETED_ERROR');
720 FND_MSG_PUB.ADD;
721 RAISE FND_API.G_EXC_ERROR;
722 END IF;
723 EXCEPTION
724 WHEN G_LOCKROW_EXCEPTION THEN
725 FND_MESSAGE.SET_NAME ('FND', 'FND_LOCK_RECORD_ERROR');
726 FND_MSG_PUB.ADD;
727 RAISE FND_API.G_EXC_ERROR;
728 END;
729 EXCEPTION
730 WHEN FND_API.G_EXC_ERROR THEN
731 x_return_status := FND_API.G_RET_STS_ERROR ;
732 WHEN OTHERS THEN
733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
734 IF FND_MSG_PUB.Check_Msg_Level
735 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
736 THEN
737 FND_MSG_PUB.Add_Exc_Msg
738 (G_PKG_NAME,
739 'Lock_SetAssociation_JSP'
740 );
741 END IF;
742 END Lock_SetAssociation_JSP;
743 END EAM_FailureSets_PVT;