[Home] [Help]
PACKAGE BODY: APPS.EAM_SAFETY_ASSOCIATION_PVT
Source
1 PACKAGE BODY EAM_SAFETY_ASSOCIATION_PVT AS
2 /* $Header: EAMVSAAB.pls 120.4 2011/07/20 13:02:29 somitra noship $ */
3
4 /***************************************************************************
5 --
6 -- Copyright (c) 2011 Oracle Corporation, Redwood Shores, CA, USA
7 -- All rights reserved.
8 --
9 -- FILENAME: EAMVSAAB.pls
10 --
11 -- DESCRIPTION: Body of package EAM_SAFETY_ASSOCIATION_PVT
12 --
13 -- NOTES
14 --
15 -- HISTORY
16 --
17 --
18 ***************************************************************************/
19 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_PROCESS_ISO_PVT';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EAMVSAAB.pls';
21 G_DEBUG_FILENAME CONSTANT VARCHAR2(50) := 'EAM_SAFETY_DEBUG.log';
22
23 G_SAFETY_ASSOCIATION_ERROR EXCEPTION;
24 G_SAFETY_EXISTENCE_ERROR EXCEPTION;
25 G_SAFETY_TRANSACTION_ERROR EXCEPTION;
26 G_SAFETY_DUPLICATE_ASSO_ERROR EXCEPTION;
27
28 /**************************************************************************
29 * Procedure: CLEARANCE_PERMIT_ASSOCIATION
30 * Purpose : This will process clearance permit association
31 ***************************************************************************/
32 PROCEDURE PERMIT_CLEARANCE_ASSOCIATION
33 ( p_permit_clearance_asso_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type
34 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.MESG_TOKEN_TBL_TYPE
35 , x_return_status OUT NOCOPY VARCHAR2
36 )IS
37
38 CURSOR C2 IS SELECT EAM_SAFETY_ASSOCIATIONS_S.NEXTVAL FROM SYS.DUAL;
39
40 --Error handling variables
41 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
42 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
43 l_return_status VARCHAR2(1);
44 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
45
46 l_safety_assoc_rec EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type;
47 l_safety_assoc_id NUMBER;
48 l_count NUMBER;
49
50 BEGIN
51
52 SAVEPOINT PERMIT_CLEARANCE_ASSOCIATION;
53 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_SAFETY_ASSOCIATION_PVT.PERMIT_CLEARANCE_ASSOCIATION'); end if;
54
55 IF p_permit_clearance_asso_tbl.COUNT > 0 THEN
56 FOR i in p_permit_clearance_asso_tbl.FIRST..p_permit_clearance_asso_tbl.LAST LOOP
57
58 l_safety_assoc_rec := p_permit_clearance_asso_tbl(i);
59
60 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking clearance existence'); end if;
61
62 SELECT COUNT(*)
63 INTO l_count
64 FROM EAM_WORK_CLEARANCES
65 WHERE WORK_CLEARANCE_ID = l_safety_assoc_rec.TARGET_REF_ID
66 AND organization_id = l_safety_assoc_rec.organization_id;
67
68 IF (l_count < 1)
69 THEN
70 RAISE G_SAFETY_EXISTENCE_ERROR;
71 END IF;
72
73
74 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking permit existence'); end if;
75
76 SELECT COUNT(*)
77 INTO l_count
78 FROM EAM_WORK_PERMITS
79 WHERE PERMIT_ID = l_safety_assoc_rec.SOURCE_ID
80 AND organization_id = l_safety_assoc_rec.organization_id;
81
82 IF (l_count < 1)
83 THEN
84 RAISE G_SAFETY_EXISTENCE_ERROR;
85 END IF;
86
87 VALIDATE_TRANSACTION_TYPE
88 ( p_validation_level => l_safety_assoc_rec.TRANSACTION_TYPE
89 , p_entity => 'ASSOCIATION'
90 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
91 , x_return_Status => l_return_status
92 );
93
94 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
95 RAISE G_SAFETY_TRANSACTION_ERROR;
96 END IF;
97
98 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking for duplicate association'); end if;
99 IF l_safety_assoc_rec.TRANSACTION_TYPE = EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
100 SELECT COUNT(*)
101 INTO l_count
102 FROM EAM_SAFETY_ASSOCIATIONS
103 WHERE SOURCE_ID = l_safety_assoc_rec.SOURCE_ID
104 AND TARGET_REF_ID = l_safety_assoc_rec.TARGET_REF_ID
105 AND organization_id = l_safety_assoc_rec.organization_id;
106
107 IF (l_count > 0)
108 THEN
109 RAISE G_SAFETY_DUPLICATE_ASSO_ERROR;
110 END IF;
111 END IF;
112
113 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Perform_Writes within PERMIT_CLEARANCE_ASSOCIATION'); end if;
114
115 IF l_safety_assoc_rec.TRANSACTION_TYPE = EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
116 OPEN C2;
117 FETCH C2 INTO l_safety_assoc_id;
118 CLOSE C2;
119 l_safety_assoc_rec.SAFETY_ASSOCIATION_ID :=l_safety_assoc_id;
120 END IF;
121
122 EAM_SAFETY_UTILITY_PVT.WRITE_SAFFETY_ASSOCIATION_ROW
123 ( p_safety_association_rec => l_safety_assoc_rec
124 , p_association_type =>l_safety_assoc_rec.association_type
125 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
126 , x_return_Status => l_return_status
127 );
128
129 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
130 THEN
131 RAISE G_SAFETY_ASSOCIATION_ERROR;
132 END IF;
133
134
135 END LOOP;
136 END IF;
137
138 x_return_status := l_return_status;
139
140 EXCEPTION
141 WHEN G_SAFETY_EXISTENCE_ERROR THEN
142
143 l_out_mesg_token_tbl := l_mesg_token_tbl;
144 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
145 ( p_message_name => 'EAM_SAF_EXISTENCE_ERROR'
146 , p_token_tbl => l_token_tbl
147 , p_mesg_token_tbl => l_mesg_token_tbl
148 , x_mesg_token_tbl => l_out_mesg_token_tbl
149 );
150 l_mesg_token_tbl := l_out_mesg_token_tbl;
151
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 x_mesg_token_tbl := l_mesg_token_tbl ;
154 ROLLBACK TO PERMIT_CLEARANCE_ASSOCIATION;
155 WHEN G_SAFETY_TRANSACTION_ERROR THEN
156
157 l_out_mesg_token_tbl := l_mesg_token_tbl;
158 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
159 ( p_message_name => 'EAM_SAF_TRANSACTION_ERROR'
160 , p_token_tbl => l_token_tbl
161 , p_mesg_token_tbl => l_mesg_token_tbl
162 , x_mesg_token_tbl => l_out_mesg_token_tbl
163 );
164 l_mesg_token_tbl := l_out_mesg_token_tbl;
165
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 x_mesg_token_tbl := l_mesg_token_tbl ;
168 ROLLBACK TO PERMIT_CLEARANCE_ASSOCIATION;
169 WHEN G_SAFETY_ASSOCIATION_ERROR THEN
170
171 l_out_mesg_token_tbl := l_mesg_token_tbl;
172 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
173 ( p_message_name => 'EAM_SAF_ASSOCIATION_ERROR'
174 , p_token_tbl => l_token_tbl
175 , p_mesg_token_tbl => l_mesg_token_tbl
176 , x_mesg_token_tbl => l_out_mesg_token_tbl
177 );
178 l_mesg_token_tbl := l_out_mesg_token_tbl;
179
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 x_mesg_token_tbl := l_mesg_token_tbl ;
182 ROLLBACK TO PERMIT_CLEARANCE_ASSOCIATION;
183 WHEN G_SAFETY_DUPLICATE_ASSO_ERROR THEN
184
185 l_token_tbl(1).token_name := 'SOURCE';
186 SELECT WP.PERMIT_NAME into l_token_tbl(1).token_value
187 FROM EAM_WORK_PERMITS WP
188 WHERE WP.PERMIT_ID = l_safety_assoc_rec.SOURCE_ID
189 AND WP.organization_id = l_safety_assoc_rec.ORGANIZATION_ID;
190
191 l_token_tbl(2).token_name := 'TARGET';
192 SELECT EWC.WORK_CLEARANCE_NAME into l_token_tbl(2).token_value
193 FROM EAM_WORK_CLEARANCES EWC
194 WHERE EWC.WORK_CLEARANCE_ID = l_safety_assoc_rec.TARGET_REF_ID
195 AND EWC.organization_id = l_safety_assoc_rec.ORGANIZATION_ID;
196
197 l_out_mesg_token_tbl := l_mesg_token_tbl;
198 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
199 ( p_message_name => 'EAM_SAF_DUPLICATE_ASSO_ERROR'
200 , p_token_tbl => l_token_tbl
201 , p_mesg_token_tbl => l_mesg_token_tbl
202 , x_mesg_token_tbl => l_out_mesg_token_tbl
203 );
204 l_mesg_token_tbl := l_out_mesg_token_tbl;
205
206 x_return_status := FND_API.G_RET_STS_ERROR;
207 x_mesg_token_tbl := l_mesg_token_tbl ;
208 ROLLBACK TO PERMIT_CLEARANCE_ASSOCIATION;
209 WHEN OTHERS THEN
210
211 ROLLBACK TO PERMIT_CLEARANCE_ASSOCIATION;
212 x_return_status := FND_API.G_RET_STS_ERROR;
213
214 END PERMIT_CLEARANCE_ASSOCIATION;
215
216 /**************************************************************************
217 * Procedure: CLEARANCE_WORK_ORDER_ASSOCIATION
218 * Purpose : This will process clearance work order association
219 ***************************************************************************/
220 PROCEDURE CLEARANCE_WO_ASSOCIATION
221 ( p_clearance_workorder_asso_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type
222 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.MESG_TOKEN_TBL_TYPE
223 , x_return_status OUT NOCOPY VARCHAR2
224 )IS
225
226 CURSOR C1 IS SELECT EAM_SAFETY_ASSOCIATIONS_S.NEXTVAL FROM SYS.DUAL;
227
228 --Error handling variables
229 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
230 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
231 l_return_status VARCHAR2(1);
232 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
233
234 l_safety_assoc_rec EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type;
235 l_safety_assoc_id NUMBER;
236 l_count NUMBER;
237 l_error_level NUMBER :=EAM_ERROR_MESSAGE_PVT.G_BO_LEVEL;
238 l_entity_index NUMBER :=1;
239
240 BEGIN
241
242 SAVEPOINT CLEARANCE_WO_ASSOCIATION;
243
244 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_SAFETY_ASSOCIATION_PVT.CLEARANCE_WO_ASSOCIATION'); end if;
245
246 IF p_clearance_workorder_asso_tbl.COUNT > 0 THEN
247 FOR i in p_clearance_workorder_asso_tbl.FIRST..p_clearance_workorder_asso_tbl.LAST LOOP
248
249 l_safety_assoc_rec := p_clearance_workorder_asso_tbl(i);
250
251 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking clearance existence'); end if;
252
253 SELECT COUNT(*)
254 INTO l_count
255 FROM EAM_WORK_CLEARANCES
256 WHERE WORK_CLEARANCE_ID = l_safety_assoc_rec.source_id
257 AND organization_id = l_safety_assoc_rec.organization_id;
258
259 IF (l_count < 1)
260 THEN
261 RAISE G_SAFETY_EXISTENCE_ERROR;
262 END IF;
263 -- END IF;
264
265 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking wo existence'); end if;
266
267 SELECT COUNT(*)
268 INTO l_count
269 FROM WIP_ENTITIES
270 WHERE WIP_ENTITY_ID = l_safety_assoc_rec.TARGET_REF_ID
271 AND organization_id = l_safety_assoc_rec.organization_id;
272
273 IF (l_count < 1)
274 THEN
275 RAISE G_SAFETY_EXISTENCE_ERROR;
276 END IF;
277
278 VALIDATE_TRANSACTION_TYPE
279 ( p_validation_level => l_safety_assoc_rec.TRANSACTION_TYPE
280 , p_entity => 'ASSOCIATION'
281 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
282 , x_return_Status => l_return_status
283 );
284 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
285 RAISE G_SAFETY_TRANSACTION_ERROR;
286 END IF;
287
288
289 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking for duplicate association'); end if;
290 IF l_safety_assoc_rec.TRANSACTION_TYPE = EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
291 SELECT COUNT(*)
292 INTO l_count
293 FROM EAM_SAFETY_ASSOCIATIONS
294 WHERE SOURCE_ID = l_safety_assoc_rec.SOURCE_ID
295 AND TARGET_REF_ID = l_safety_assoc_rec.TARGET_REF_ID
296 AND organization_id = l_safety_assoc_rec.organization_id;
297
298 IF (l_count > 0)
299 THEN
300 RAISE G_SAFETY_DUPLICATE_ASSO_ERROR;
301 END IF;
302 END IF;
303
304 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Perform_Writes from within CLEARANCE_WO_ASSOCIATION'); end if;
305
306 IF l_safety_assoc_rec.TRANSACTION_TYPE = EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
307 OPEN C1;
308 FETCH C1 INTO l_safety_assoc_id;
309 CLOSE C1;
310 l_safety_assoc_rec.SAFETY_ASSOCIATION_ID :=l_safety_assoc_id;
311 END IF;
312
313 EAM_SAFETY_UTILITY_PVT.WRITE_SAFFETY_ASSOCIATION_ROW
314 ( p_safety_association_rec => l_safety_assoc_rec
315 , p_association_type =>l_safety_assoc_rec.association_type
316 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
317 , x_return_Status => l_return_status
318 );
319
320 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
321 THEN
322 RAISE G_SAFETY_ASSOCIATION_ERROR;
323 END IF;
324
325
326 END LOOP;
327 END IF;
328
329 x_return_status := l_return_status;
330
331 EXCEPTION
332 WHEN G_SAFETY_EXISTENCE_ERROR THEN
333
334 l_out_mesg_token_tbl := l_mesg_token_tbl;
335 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
336 ( p_message_name => 'EAM_SAF_EXISTENCE_ERROR'
337 , p_token_tbl => l_token_tbl
338 , p_mesg_token_tbl => l_mesg_token_tbl
339 , x_mesg_token_tbl => l_out_mesg_token_tbl
340 );
341 l_mesg_token_tbl := l_out_mesg_token_tbl;
342
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 x_mesg_token_tbl := l_mesg_token_tbl ;
345
346 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
347 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
348 , p_error_level => l_error_level
349 , p_entity_index => l_entity_index
350 );
351
352 ROLLBACK TO CLEARANCE_WO_ASSOCIATION;
353 WHEN G_SAFETY_TRANSACTION_ERROR THEN
354
355 l_out_mesg_token_tbl := l_mesg_token_tbl;
356 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
357 ( p_message_name => 'EAM_SAF_TRANSACTION_ERROR'
358 , p_token_tbl => l_token_tbl
359 , p_mesg_token_tbl => l_mesg_token_tbl
360 , x_mesg_token_tbl => l_out_mesg_token_tbl
361 );
362 l_mesg_token_tbl := l_out_mesg_token_tbl;
363
364 x_return_status := FND_API.G_RET_STS_ERROR;
365 x_mesg_token_tbl := l_mesg_token_tbl ;
366
367 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
368 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
369 , p_error_level => l_error_level
370 , p_entity_index => l_entity_index
371 );
372
373 ROLLBACK TO CLEARANCE_WO_ASSOCIATION;
374 WHEN G_SAFETY_ASSOCIATION_ERROR THEN
375
376 l_out_mesg_token_tbl := l_mesg_token_tbl;
377 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
378 ( p_message_name => 'EAM_SAF_ASSOCIATION_ERROR'
379 , p_token_tbl => l_token_tbl
380 , p_mesg_token_tbl => l_mesg_token_tbl
381 , x_mesg_token_tbl => l_out_mesg_token_tbl
382 );
383 l_mesg_token_tbl := l_out_mesg_token_tbl;
384
385 x_return_status := FND_API.G_RET_STS_ERROR;
386 x_mesg_token_tbl := l_mesg_token_tbl ;
387
388 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
389 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
390 , p_error_level => l_error_level
391 , p_entity_index => l_entity_index
392 );
393
394 ROLLBACK TO CLEARANCE_WO_ASSOCIATION;
395 WHEN G_SAFETY_DUPLICATE_ASSO_ERROR THEN
396
397 l_token_tbl(1).token_name := 'SOURCE';
398 SELECT EWC.WORK_CLEARANCE_NAME into l_token_tbl(1).token_value
399 FROM EAM_WORK_CLEARANCES EWC
400 WHERE EWC.WORK_CLEARANCE_ID = l_safety_assoc_rec.SOURCE_ID
401 AND EWC.organization_id = l_safety_assoc_rec.ORGANIZATION_ID;
402
403 l_token_tbl(2).token_name := 'TARGET';
404 SELECT WE.WIP_ENTITY_NAME into l_token_tbl(2).token_value
405 FROM WIP_ENTITIES we
406 WHERE we.WIP_ENTITY_ID = l_safety_assoc_rec.TARGET_REF_ID
407 AND WE.organization_id = l_safety_assoc_rec.ORGANIZATION_ID;
408
409 l_out_mesg_token_tbl := l_mesg_token_tbl;
410 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
411 ( p_message_name => 'EAM_SAF_DUPLICATE_ASSO_ERROR'
412 , p_token_tbl => l_token_tbl
413 , p_mesg_token_tbl => l_mesg_token_tbl
414 , x_mesg_token_tbl => l_out_mesg_token_tbl
415 );
416
417 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
418 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
419 , p_error_level => l_error_level
420 , p_entity_index => l_entity_index
421 );
422
423 l_mesg_token_tbl := l_out_mesg_token_tbl;
424
425 x_return_status := FND_API.G_RET_STS_ERROR;
426 x_mesg_token_tbl := l_mesg_token_tbl ;
427 ROLLBACK TO CLEARANCE_WO_ASSOCIATION;
428 WHEN OTHERS THEN
429
430 ROLLBACK TO CLEARANCE_WO_ASSOCIATION;
431 x_return_status := FND_API.G_RET_STS_ERROR;
432
433 END CLEARANCE_WO_ASSOCIATION;
434
435 /************************************************************
436 * Procedure: PERMIT_WORK_ORDER_ASSOCIATION
437 * Purpose : This will process permit work order association
438 ************************************************************/
439 PROCEDURE PERMIT_WORK_ORDER_ASSOCIATION
440 ( p_permit_wo_association_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type
441 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.MESG_TOKEN_TBL_TYPE
442 , x_return_status OUT NOCOPY VARCHAR2
443 )IS
444 CURSOR C IS SELECT EAM_SAFETY_ASSOCIATIONS_S.NEXTVAL FROM SYS.DUAL;
445
446 --Error handling variables
447 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
448 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
449 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
450 l_return_status VARCHAR2(1);
451
452 l_safety_assoc_rec EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type;
453 l_safety_assoc_id NUMBER;
454 l_count NUMBER;
455
456 BEGIN
457
458 SAVEPOINT PERMIT_WORK_ORDER_ASSOCIATION;
459 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_SAFETY_ASSOCIATION_PVT.PERMIT_WORK_ORDER_ASSOCIATION'); end if;
460
461 IF p_permit_wo_association_tbl.COUNT > 0 THEN
462 FOR i in p_permit_wo_association_tbl.FIRST..p_permit_wo_association_tbl.LAST LOOP
463
464 l_safety_assoc_rec := p_permit_wo_association_tbl(i);
465
466 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking permit existence'); end if;
467
468 SELECT COUNT(*)
469 INTO l_count
470 FROM EAM_WORK_PERMITS
471 WHERE permit_id = l_safety_assoc_rec.source_id
472 AND organization_id = l_safety_assoc_rec.ORGANIZATION_ID;
473
474
475 IF (l_count < 1)
476 THEN
477 RAISE G_SAFETY_EXISTENCE_ERROR;
478 END IF;
479
480 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking wo existence'); end if;
481
482 SELECT COUNT(*)
483 INTO l_count
484 FROM WIP_ENTITIES
485 WHERE WIP_ENTITY_ID = l_safety_assoc_rec.TARGET_REF_ID
486 AND organization_id = l_safety_assoc_rec.ORGANIZATION_ID;
487
488 IF (l_count < 1)
489 THEN
490 RAISE G_SAFETY_EXISTENCE_ERROR;
491 END IF;
492
493 VALIDATE_TRANSACTION_TYPE
494 ( p_validation_level => l_safety_assoc_rec.TRANSACTION_TYPE
495 , p_entity => 'ASSOCIATION'
496 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
497 , x_return_Status => l_return_status
498 );
499
500 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
501 RAISE G_SAFETY_TRANSACTION_ERROR;
502 END IF;
503
504 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking for duplicate association'); end if;
505 IF l_safety_assoc_rec.TRANSACTION_TYPE = EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
506 SELECT COUNT(*)
507 INTO l_count
508 FROM EAM_SAFETY_ASSOCIATIONS
509 WHERE SOURCE_ID = l_safety_assoc_rec.SOURCE_ID
510 AND TARGET_REF_ID = l_safety_assoc_rec.TARGET_REF_ID
511 AND organization_id = l_safety_assoc_rec.organization_id;
512
513 IF (l_count > 0)
514 THEN
515 RAISE G_SAFETY_DUPLICATE_ASSO_ERROR;
516 END IF;
517 END IF;
518
519 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Perform_Writes from within PERMIT_WORK_ORDER_ASSOCIATION'); end if;
520 IF l_safety_assoc_rec.TRANSACTION_TYPE = EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
521 OPEN C;
522 FETCH C INTO l_safety_assoc_id;
523 CLOSE C;
524 l_safety_assoc_rec.SAFETY_ASSOCIATION_ID :=l_safety_assoc_id;
525 END IF;
526
527 EAM_SAFETY_UTILITY_PVT.WRITE_SAFFETY_ASSOCIATION_ROW
528 ( p_safety_association_rec => l_safety_assoc_rec
529 , p_association_type => l_safety_assoc_rec.association_type
530 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
531 , x_return_Status => l_return_status
532 );
533
534 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
535 THEN
536 RAISE G_SAFETY_ASSOCIATION_ERROR;
537 END IF;
538
539 END LOOP;
540 END IF;
541
542 x_return_status := l_return_status;
543
544 EXCEPTION
545
546 WHEN G_SAFETY_EXISTENCE_ERROR THEN
547
548 l_out_mesg_token_tbl := l_mesg_token_tbl;
549 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
550 ( p_message_name => 'EAM_SAF_EXISTENCE_ERROR'
551 , p_token_tbl => l_token_tbl
552 , p_mesg_token_tbl => l_mesg_token_tbl
553 , x_mesg_token_tbl => l_out_mesg_token_tbl
554 );
555 l_mesg_token_tbl := l_out_mesg_token_tbl;
556
557 x_return_status := FND_API.G_RET_STS_ERROR;
558 x_mesg_token_tbl := l_mesg_token_tbl ;
559 ROLLBACK TO PERMIT_WORK_ORDER_ASSOCIATION;
560 WHEN G_SAFETY_TRANSACTION_ERROR THEN
561
562 l_out_mesg_token_tbl := l_mesg_token_tbl;
563 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
564 ( p_message_name => 'EAM_SAF_TRANSACTION_ERROR'
565 , p_token_tbl => l_token_tbl
566 , p_mesg_token_tbl => l_mesg_token_tbl
567 , x_mesg_token_tbl => l_out_mesg_token_tbl
568 );
569 l_mesg_token_tbl := l_out_mesg_token_tbl;
570
571 x_return_status := FND_API.G_RET_STS_ERROR;
572 x_mesg_token_tbl := l_mesg_token_tbl ;
573 ROLLBACK TO PERMIT_WORK_ORDER_ASSOCIATION;
574 WHEN G_SAFETY_ASSOCIATION_ERROR THEN
575
576 l_out_mesg_token_tbl := l_mesg_token_tbl;
577 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
578 ( p_message_name => 'EAM_SAF_ASSOCIATION_ERROR'
579 , p_token_tbl => l_token_tbl
580 , p_mesg_token_tbl => l_mesg_token_tbl
581 , x_mesg_token_tbl => l_out_mesg_token_tbl
582 );
583 l_mesg_token_tbl := l_out_mesg_token_tbl;
584
585 x_return_status := FND_API.G_RET_STS_ERROR;
586 x_mesg_token_tbl := l_mesg_token_tbl ;
587 ROLLBACK TO PERMIT_WORK_ORDER_ASSOCIATION;
588 WHEN G_SAFETY_DUPLICATE_ASSO_ERROR THEN
589
590 l_token_tbl(1).token_name := 'SOURCE';
591 SELECT WP.PERMIT_NAME into l_token_tbl(1).token_value
592 FROM EAM_WORK_PERMITS WP
593 WHERE WP.PERMIT_ID = l_safety_assoc_rec.SOURCE_ID
594 AND WP.organization_id = l_safety_assoc_rec.ORGANIZATION_ID;
595
596
597 l_token_tbl(2).token_name := 'TARGET';
598 SELECT WE.WIP_ENTITY_NAME into l_token_tbl(2).token_value
599 FROM WIP_ENTITIES we
600 WHERE we.WIP_ENTITY_ID = l_safety_assoc_rec.TARGET_REF_ID
601 AND WE.organization_id = l_safety_assoc_rec.ORGANIZATION_ID;
602
603 l_out_mesg_token_tbl := l_mesg_token_tbl;
604 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
605 ( p_message_name => 'EAM_SAF_DUPLICATE_ASSO_ERROR'
606 , p_token_tbl => l_token_tbl
607 , p_mesg_token_tbl => l_mesg_token_tbl
608 , x_mesg_token_tbl => l_out_mesg_token_tbl
609 );
610 l_mesg_token_tbl := l_out_mesg_token_tbl;
611
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 x_mesg_token_tbl := l_mesg_token_tbl ;
614 ROLLBACK TO PERMIT_WORK_ORDER_ASSOCIATION;
615 WHEN OTHERS THEN
616
617 ROLLBACK TO PERMIT_WORK_ORDER_ASSOCIATION;
618 x_return_status := FND_API.G_RET_STS_ERROR;
619
620 END PERMIT_WORK_ORDER_ASSOCIATION;
621
622 /************************************************************
623 * Procedure: ASSET_ISOLATION_ASSOCIATION
624 * Purpose : This will process asset isolation association
625 ************************************************************/
626 PROCEDURE ASSET_ISOLATION_ASSOCIATION
627 (
628 p_isolated_assets_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type
629 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.MESG_TOKEN_TBL_TYPE
630 , x_return_status OUT NOCOPY VARCHAR2
631 ) IS
632 CURSOR C IS SELECT EAM_SAFETY_ASSOCIATIONS_S.NEXTVAL FROM SYS.DUAL;
633
634 --Error handling variables
635 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
636 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
637 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
638 l_return_status VARCHAR2(1);
639
640 l_safety_assoc_rec EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type;
641 l_safety_assoc_id NUMBER;
642 l_iso_association_error EXCEPTION;
643 l_count NUMBER;
644 BEGIN
645
646 SAVEPOINT ASSET_ISOLATION_ASSOCIATION;
647 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_SAFETY_ASSOCIATION_PVT.ASSET_ISOLATION_ASSOCIATION'); end if;
648
649 IF p_isolated_assets_tbl.COUNT > 0 THEN
650 FOR i in p_isolated_assets_tbl.FIRST..p_isolated_assets_tbl.LAST LOOP
651
652 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating transaction type '); END IF;
653
654 l_safety_assoc_rec := p_isolated_assets_tbl(i);
655
656 VALIDATE_TRANSACTION_TYPE
657 ( p_validation_level => l_safety_assoc_rec.TRANSACTION_TYPE
658 , p_entity => 'ASSOCIATION'
659 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
660 , x_return_Status => l_return_status
661 );
662
663 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
664 RAISE G_SAFETY_TRANSACTION_ERROR;
665 END IF;
666
667 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking for duplicate association'); end if;
668 IF l_safety_assoc_rec.TRANSACTION_TYPE = EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
669 SELECT COUNT(*)
670 INTO l_count
671 FROM EAM_SAFETY_ASSOCIATIONS
672 WHERE SOURCE_ID = l_safety_assoc_rec.SOURCE_ID
673 AND TARGET_REF_ID = l_safety_assoc_rec.TARGET_REF_ID
674 AND organization_id = l_safety_assoc_rec.organization_id;
675
676 IF (l_count > 0)
677 THEN
678 RAISE G_SAFETY_DUPLICATE_ASSO_ERROR;
679 END IF;
680 END IF;
681
682 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Perform_Writes from within ASSET_ISOLATION_ASSOCIATION'); end if;
683 IF l_safety_assoc_rec.TRANSACTION_TYPE = EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
684 OPEN C;
685 FETCH C INTO l_safety_assoc_id;
686 CLOSE C;
687 l_safety_assoc_rec.SAFETY_ASSOCIATION_ID :=l_safety_assoc_id;
688 END IF;
689
690 EAM_SAFETY_UTILITY_PVT.WRITE_SAFFETY_ASSOCIATION_ROW
691 ( p_safety_association_rec => l_safety_assoc_rec
692 , p_association_type =>l_safety_assoc_rec.association_type
693 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
694 , x_return_Status => l_return_status
695 );
696
697 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
698 THEN
699 RAISE l_iso_association_error;
700 END IF;
701 END LOOP;
702 END IF;
703
704 x_return_status := l_return_status;
705
706 EXCEPTION
707 WHEN G_SAFETY_EXISTENCE_ERROR THEN
708
709 l_out_mesg_token_tbl := l_mesg_token_tbl;
710 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
711 ( p_message_name => 'EAM_SAF_EXISTENCE_ERROR'
712 , p_token_tbl => l_token_tbl
713 , p_mesg_token_tbl => l_mesg_token_tbl
714 , x_mesg_token_tbl => l_out_mesg_token_tbl
715 );
716 l_mesg_token_tbl := l_out_mesg_token_tbl;
717
718 x_return_status := FND_API.G_RET_STS_ERROR;
719 x_mesg_token_tbl := l_mesg_token_tbl ;
720 ROLLBACK TO ASSET_ISOLATION_ASSOCIATION;
721 WHEN G_SAFETY_TRANSACTION_ERROR THEN
722
723 l_out_mesg_token_tbl := l_mesg_token_tbl;
724 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
725 ( p_message_name => 'EAM_SAF_TRANSACTION_ERROR'
726 , p_token_tbl => l_token_tbl
727 , p_mesg_token_tbl => l_mesg_token_tbl
728 , x_mesg_token_tbl => l_out_mesg_token_tbl
729 );
730 l_mesg_token_tbl := l_out_mesg_token_tbl;
731
732 x_return_status := FND_API.G_RET_STS_ERROR;
733 x_mesg_token_tbl := l_mesg_token_tbl ;
734 ROLLBACK TO ASSET_ISOLATION_ASSOCIATION;
735 WHEN G_SAFETY_ASSOCIATION_ERROR THEN
736
737 l_out_mesg_token_tbl := l_mesg_token_tbl;
738 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
739 ( p_message_name => 'EAM_SAF_ASSOCIATION_ERROR'
740 , p_token_tbl => l_token_tbl
741 , p_mesg_token_tbl => l_mesg_token_tbl
742 , x_mesg_token_tbl => l_out_mesg_token_tbl
743 );
744 l_mesg_token_tbl := l_out_mesg_token_tbl;
745
746 x_return_status := FND_API.G_RET_STS_ERROR;
747 x_mesg_token_tbl := l_mesg_token_tbl ;
748 ROLLBACK TO ASSET_ISOLATION_ASSOCIATION;
749 WHEN G_SAFETY_DUPLICATE_ASSO_ERROR THEN
750
751 l_token_tbl(1).token_name := 'SOURCE';
752 SELECT iso.ISOLATION_NAME into l_token_tbl(1).token_value
753 FROM EAM_ISOLATIONS iso
754 WHERE iso.isolation_id = l_safety_assoc_rec.SOURCE_ID
755 AND iso.organization_id = l_safety_assoc_rec.ORGANIZATION_ID;
756
757
758 l_token_tbl(2).token_name := 'TARGET';
759 SELECT csi.instance_number into l_token_tbl(2).token_value
760 FROM csi_item_instances csi
761 WHERE csi.instance_id = l_safety_assoc_rec.TARGET_REF_ID
762 AND csi.last_vld_organization_id = l_safety_assoc_rec.ORGANIZATION_ID;
763
764 l_out_mesg_token_tbl := l_mesg_token_tbl;
765 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
766 ( p_message_name => 'EAM_SAF_DUPLICATE_ASSO_ERROR'
767 , p_token_tbl => l_token_tbl
768 , p_mesg_token_tbl => l_mesg_token_tbl
769 , x_mesg_token_tbl => l_out_mesg_token_tbl
770 );
771 l_mesg_token_tbl := l_out_mesg_token_tbl;
772
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 x_mesg_token_tbl := l_mesg_token_tbl ;
775 ROLLBACK TO ASSET_ISOLATION_ASSOCIATION;
776
777 WHEN OTHERS THEN
778 ROLLBACK TO ASSET_ISOLATION_ASSOCIATION;
779 x_return_status := FND_API.G_RET_STS_ERROR;
780 END ASSET_ISOLATION_ASSOCIATION;
781
782 /************************************************************
783 * Procedure: VALIDATE_TRANSACTION_TYPE
784 * Purpose : This will transaction type for safety association
785 ************************************************************/
786 PROCEDURE VALIDATE_TRANSACTION_TYPE(
787 p_validation_level IN NUMBER
788 , p_entity IN VARCHAR2
789 , x_return_status OUT NOCOPY VARCHAR2
790 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
791 )IS
792 BEGIN
793
794 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_SAFETY_ASSOCIATION_PVT.VALIDATE_TRANSACTION_TYPE'); end if;
795
796 IF (p_entity = 'ASSOCIATION') THEN
797 IF (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_CREATE)
798 OR (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_UPDATE)
799 OR (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_DELETE)then
800
801 x_return_status := FND_API.G_RET_STS_SUCCESS;
802
803 ELSE
804 x_return_status := FND_API.G_RET_STS_ERROR;
805 END IF;
806
807 END IF;
808 END VALIDATE_TRANSACTION_TYPE;
809
810 END EAM_SAFETY_ASSOCIATION_PVT;