[Home] [Help]
PACKAGE BODY: APPS.EAM_PROCESS_ISOLATION_PVT
Source
1 PACKAGE BODY EAM_PROCESS_ISOLATION_PVT AS
2 /* $Header: EAMVISOB.pls 120.8 2011/06/15 01:12:40 mashah noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2009 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME: EAMVISOB.pls
9 --
10 -- DESCRIPTION: Body of package EAM_PROCESS_ISOLATION_PVT
11 --
12 -- NOTES
13 --
14 -- HISTORY
15
16 ***************************************************************************/
17
18 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_PROCESS_ISOLATION_PVT';
19 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EAMVISOB.pls';
20 G_DEBUG_FILENAME CONSTANT VARCHAR2(50) := 'EAM_SAFETY_DEBUG.log';
21
22
23 /*********************************************************
24 * Procedure : Validate_Transaction_Type
25 * Purpose : This procedure will check if the transaction type is valid
26 for a particular entity.
27 *********************************************************/
28
29 PROCEDURE VALIDATE_TRANSACTION_TYPE
30 (
31 p_validation_level IN NUMBER
32 , p_entity IN VARCHAR2
33 , x_return_status OUT NOCOPY VARCHAR2
34 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
35 )IS
36
37 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type ;
38 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
39 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type ;
40 l_other_message VARCHAR2(2000);
41 l_err_text VARCHAR2(2000);
42
43
44 BEGIN
45
46 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_PROCESS_ISOLATION_PVT.VALIDATE_TRANSACTION_TYPE'); end if;
47
48 IF (p_entity = 'ISOLATION') THEN
49 IF (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_CREATE)
50 OR (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_UPDATE) then
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52
53 ELSE
54 x_return_status := FND_API.G_RET_STS_ERROR;
55 END IF;
56 END IF;
57
58 END VALIDATE_TRANSACTION_TYPE;
59
60
61 /************************************************************
62 * Procedure : PROCESS_ISOLATION
63 * Purpose : This will process create/update/delete on Isolation
64 ************************************************************/
65
66 PROCEDURE PROCESS_ISOLATION
67 ( p_bo_identifier IN VARCHAR2 := 'EAM'
68 , p_api_version_number IN NUMBER := 1.0
69 , p_init_msg_list IN BOOLEAN := FALSE
70 , p_commit IN VARCHAR2
71 , p_iso_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type
72 , p_establish_iso_tbl IN EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type
73 , p_re_establish_iso_tbl IN EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type
74 , p_isolated_asset_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type
75 , x_isolation_id OUT NOCOPY NUMBER
76 , x_return_status OUT NOCOPY VARCHAR2
77 , x_msg_count OUT NOCOPY NUMBER
78 , p_debug IN VARCHAR2
79 , p_output_dir IN VARCHAR2
80 , p_debug_filename IN VARCHAR2
81 , p_debug_file_mode IN VARCHAR2
82 )IS
83
84 l_api_name CONSTANT VARCHAR2(30) := G_PKG_NAME;
85 l_validation_level NUMBER :=p_iso_header_rec.transaction_type;
86
87 l_return_status VARCHAR2(1);
88 l_msg_count NUMBER;
89 l_msg_data VARCHAR2(240);
90 lx_establish_iso_tbl EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type;
91 lx_re_establish_iso_tbl EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type;
92 lx_iso_header_rec EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type;
93 l_isolation_id NUMBER :=p_iso_header_rec.isolation_id;
94
95 l_isolated_asset_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
96 l_safety_assoc_rec EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type;
97
98 /* Error Handling Variables */
99 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type ;
100 l_error_level NUMBER :=EAM_ERROR_MESSAGE_PVT.G_BO_LEVEL;
101 l_entity_index NUMBER :=1;
102 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
103 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type ;
104 l_other_message VARCHAR2(2000);
105 l_err_text VARCHAR2(2000);
106
107
108 BEGIN
109
110 SAVEPOINT PROCESS_ISOLATION;
111
112 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_PROCESS_ISOLATION_PVT.PROCESS_ISOLATION'); end if;
113
114
115 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling ISOLATION from PROCESS_ISOLATION'); end if;
116
117
118
119 WORK_ISOLATION
120 (
121 p_validation_level => l_validation_level
122 , p_isolation_id => p_iso_header_rec.isolation_id
123 , p_organization_id => p_iso_header_rec.organization_id
124 , p_iso_header_rec => p_iso_header_rec
125 , x_iso_header_rec => lx_iso_header_rec
126 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
127 , x_return_status => l_return_status
128 );
129
130 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
131 THEN
132 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
133 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
134 , p_error_level => l_error_level
135 , p_entity_index => l_entity_index
136 );
137 raise fnd_api.g_exc_unexpected_error;
138 END IF;
139 l_isolation_id :=lx_iso_header_rec.isolation_id;
140 IF p_establish_iso_tbl.COUNT > 0 THEN
141 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling ESTABLISHMENT from PROCESS_ISOLATION'); end if;
142 ESTABLISHMENT
143 (
144 p_organization_id => p_iso_header_rec.organization_id
145 , p_establish_iso_tbl => p_establish_iso_tbl
146 , p_safety_source_id => l_isolation_id
147 , x_establish_iso_tbl => lx_establish_iso_tbl
148 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
149 , x_return_status => l_return_status
150 );
151
152 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
153 THEN
154 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
155 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
156 , p_error_level => l_error_level
157 , p_entity_index => l_entity_index
158 );
159 raise fnd_api.g_exc_unexpected_error;
160 END IF;
161
162 END IF;
163
164
165 IF p_re_establish_iso_tbl.COUNT > 0 THEN
166 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Re-ESTABLISHMENT from PROCESS_ISOLATION'); end if;
167 ESTABLISHMENT
168 (
169 p_organization_id => p_iso_header_rec.organization_id
170 , p_establish_iso_tbl => p_re_establish_iso_tbl
171 , p_safety_source_id => l_isolation_id
172 , x_establish_iso_tbl => lx_re_establish_iso_tbl
173 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
174 , x_return_status => l_return_status
175 );
176
177 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
178 THEN
179 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
180 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
181 , p_error_level => l_error_level
182 , p_entity_index => l_entity_index
183 );
184 raise fnd_api.g_exc_unexpected_error;
185 END IF;
186
187 END IF;
188
189 IF p_isolated_asset_tbl.COUNT > 0 THEN
190 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling ISOLATED_ASSETS from PROCESS_ISOLATION'); end if;
191
192 FOR i in p_isolated_asset_tbl.FIRST..p_isolated_asset_tbl.LAST LOOP
193
194 l_safety_assoc_rec := p_isolated_asset_tbl(i);
195 if l_safety_assoc_rec.source_id is null then
196 l_safety_assoc_rec.source_id := l_isolation_id;
197 end if;
198 l_isolated_asset_tbl(i) :=l_safety_assoc_rec;
199 END LOOP;
200
201 EAM_SAFETY_ASSOCIATION_PVT.ASSET_ISOLATION_ASSOCIATION
202 (
203 p_isolated_assets_tbl => l_isolated_asset_tbl
204 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
205 , x_return_status => l_return_status
206 );
207
208 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
209 THEN
210 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
211 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
212 , p_error_level => l_error_level
213 , p_entity_index => l_entity_index
214 );
215 raise fnd_api.g_exc_unexpected_error;
216 END IF;
217
218 END IF;
219 x_return_status :=l_return_status;
220 x_isolation_id :=lx_iso_header_rec.isolation_id;
221
222 IF (p_commit = FND_API.G_TRUE) THEN
223 COMMIT;
224 END IF;
225
226
227 EXCEPTION
228
229 WHEN fnd_api.g_exc_unexpected_error THEN
230 ROLLBACK TO PROCESS_ISOLATION;
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 x_msg_count := EAM_ERROR_MESSAGE_PVT.Get_Message_Count;
233
234 WHEN OTHERS THEN
235 ROLLBACK TO PROCESS_ISOLATION;
236 x_return_status := FND_API.G_RET_STS_ERROR;
237
238
239 END PROCESS_ISOLATION;
240
241
242
243 /************************************************************
244 * Procedure : ISOLATION
245 * Purpose : This will process isolation header
246 ************************************************************/
247 PROCEDURE WORK_ISOLATION
248 (
249 p_validation_level IN NUMBER
250 , p_isolation_id IN NUMBER :=NULL
251 , p_organization_id IN NUMBER :=NULL
252 , p_iso_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type
253 , x_iso_header_rec OUT NOCOPY EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type
254 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.MESG_TOKEN_TBL_TYPE
255 , x_return_status OUT NOCOPY VARCHAR2
256 )IS
257
258 CURSOR C IS SELECT EAM_ISOLATIONS_S.NEXTVAL FROM SYS.DUAL;
259
260 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
261 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
262 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
263 l_return_status VARCHAR2(1);
264
265 l_isolation_id NUMBER := p_iso_header_rec.isolation_id;
266 l_workflow_enabled VARCHAR2(1);
267
268 l_iso_header_rec EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type :=p_iso_header_rec;
269 l_old_iso_header_rec EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type;
270 l_out_iso_header_rec EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type;
271
272
273 l_maintenance_object_source NUMBER :=1; -- To check if work flow is enabled
274 l_status_pending_event VARCHAR2(100) := 'oracle.apps.eam.isolation.status.change.pending';
275 l_approval_required BOOLEAN := FALSE; --set the flag to 'false' initially
276 l_pending_workflow_name VARCHAR2(100);
277 l_pending_workflow_process VARCHAR2(200) ;
278 l_new_system_status NUMBER;
279 l_test VARCHAR2(200);
280
281 l_isolation_write_error EXCEPTION;
282 l_isolation_transaction_error EXCEPTION;
283 l_isolation_existence_error EXCEPTION;
284 l_isolation_required_error EXCEPTION;
285 l_isolation_attributes_error EXCEPTION;
286 l_err_status_change EXCEPTION;
287
288 BEGIN
289
290 SAVEPOINT WORK_ISOLATION;
291
292 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_PROCESS_ISOLATION_PVT.ISOLATION'); end if;
293
294 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling VALIDATE_TRANSACTION_TYPE'); end if;
295
296 EAM_PROCESS_ISOLATION_PVT.VALIDATE_TRANSACTION_TYPE
297 ( p_validation_level => p_validation_level
298 , p_entity => 'ISOLATION'
299 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
300 , x_return_Status => l_return_status
301 );
302
303 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
304 THEN
305 RAISE l_isolation_transaction_error;
306 END IF;
307
308 IF (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_CREATE) THEN
309 IF (l_isolation_id IS NULL) OR (l_isolation_id = FND_API.G_MISS_NUM) THEN
310 OPEN C;
311 FETCH C INTO l_isolation_id;
312 CLOSE C;
313 l_iso_header_rec.isolation_id :=l_isolation_id;
314 END IF;
315 END IF;
316
317 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Check_Existence'); end if;
318
319 EAM_ISOLATION_VALIDATE_PVT.Check_Existence
320 ( p_iso_header_rec => l_iso_header_rec
321 , x_iso_header_rec => l_old_iso_header_rec
322 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
323 , x_return_Status => l_return_status
324 );
325 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
326 THEN
327 RAISE l_isolation_existence_error;
328 END IF;
329
330 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Populate_NULL_Columns'); end if;
331 IF (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_UPDATE) THEN
332
333
334 EAM_ISOLATION_DEFAULT_PVT.Populate_NULL_Columns
335 ( p_eam_iso_header_rec => l_iso_header_rec
336 , p_old_eam_iso_header_rec => l_old_iso_header_rec
337 , x_eam_isolation_rec => l_out_iso_header_rec
338 );
339 l_iso_header_rec :=l_out_iso_header_rec;
340 END IF;
341
342 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Check_required'); end if;
343
344
345 EAM_ISOLATION_VALIDATE_PVT.CHECK_REQUIRED
346 ( p_iso_header_rec => l_iso_header_rec
347 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
348 , x_return_Status => l_return_status
349 );
350
351 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
352 THEN
353 RAISE l_isolation_required_error;
354 END IF;
355
356
357 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Check_attributes'); end if;
358
359 EAM_ISOLATION_VALIDATE_PVT.CHECK_ATTRIBUTES
360 ( p_iso_header_rec => l_iso_header_rec
361 , p_old_iso_header_rec => l_old_iso_header_rec
362 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
363 , x_return_Status => l_return_status
364 );
365
366 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
367 Then
368 RAISE l_isolation_attributes_error;
369 END IF;
370
371 -- GET WORKFLOW ENABLED
372
373 BEGIN
374 SELECT EAM_SAFETY_WORKFLOW_ENABLED
375 INTO l_workflow_enabled
376 FROM WIP_EAM_PARAMETERS
377 WHERE organization_id =p_organization_id;
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 l_workflow_enabled := 'N';
381 END;
382
383 IF(l_workflow_enabled = 'Y') THEN --if workflow is enabled
384 IF((l_iso_header_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_CREATE ) --created
385 OR (l_iso_header_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE -- updated
386 AND NVL(l_old_iso_header_rec.pending_flag,'N') = 'N' --old status is not pending
387 --and old status is not same as new status
388 AND (l_old_iso_header_rec.SYSTEM_STATUS <>l_iso_header_rec.SYSTEM_STATUS)
389 )
390 ) THEN
391 l_test := (WF_EVENT.TEST(l_status_pending_event));
392
393 IF(WF_EVENT.TEST(l_status_pending_event) <> 'NONE') THEN
394
395 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Calling iSOLATION Wkflow required check API...') ; END IF;
396
397
398 EAM_ISOLATION_WORKFLOW_PVT.Is_Approval_Required_Released
399 (p_old_isolation_rec => l_old_iso_header_rec,
400 p_new_isolation_rec => l_iso_header_rec,
401 x_approval_required => l_approval_required,
402 x_workflow_name => l_pending_workflow_name,
403 x_workflow_process => l_pending_workflow_process
404 );
405
406 IF(l_approval_required) THEN
407 l_iso_header_rec.pending_flag:='Y'; --if approval required set the pending flag and system status to previous status
408 l_new_system_status := l_iso_header_rec.SYSTEM_STATUS;
409 l_iso_header_rec.SYSTEM_STATUS := NVL(l_old_iso_header_rec.SYSTEM_STATUS,17);
410 -- l_iso_header_rec.USER_DEFINED_STATUS_ID := NVL(l_old_iso_header_rec.USER_DEFINED_STATUS_ID,17);
411
412 END IF;
413 END IF; --end of check for status event enabled
414
415 END IF;
416
417
418 END IF; --end of check for workflow enabled
419
420 -- Put isolation completion details if status is changed to complete
421
422 /* IF (l_iso_header_rec.SYSTEM_STATUS in (wip_constants.comp_chrg,wip_constants.comp_nochrg)) THEN
423
424 IF (l_old_iso_header_rec.SYSTEM_STATUS = wip_constants.draft) THEN
425 RAISE l_err_status_change;
426 ELSE
427 l_iso_header_rec.COMPLETION_DATE := NVL(l_iso_header_rec.COMPLETION_DATE,sysdate);
428 END IF;
429 END IF;*/
430
431 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Perform_Writes'); end if;
432
433 EAM_ISOLATION_UTILITY_PVT.PERFORM_WRITES
434 ( p_isolation_header_rec => l_iso_header_rec
435 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
436 , x_return_Status => l_return_status
437 );
438
439 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
440 THEN
441 RAISE l_isolation_write_error;
442 END IF;
443
444 x_iso_header_rec :=l_iso_header_rec;
445
446 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Created Isolation with isolation id: '||p_iso_header_rec.isolation_id); end if;
447
448 --If isolation is approved and no workflow then call the procedure CHANGE_ISOLATION_STATUS
449 IF((l_workflow_enabled = 'N')
450 OR (l_iso_header_rec.APPROVED_BY IS NOT NULL)
451 OR (l_iso_header_rec.APPROVED_BY <> FND_API.G_MISS_NUM))
452 AND (l_old_iso_header_rec.SYSTEM_STATUS <>l_iso_header_rec.SYSTEM_STATUS) THEN
453
454 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling CHANGE_ISOLATION_STATUS') ; END IF ;
455
456 EAM_ISOLATION_UTILITY_PVT.CHANGE_ISOLATION_STATUS
457 ( p_isolation_id => l_iso_header_rec.isolation_id
458 , p_organization_id => l_iso_header_rec.organization_id
459 , p_to_status_type => l_iso_header_rec.SYSTEM_STATUS
460 , p_user_id => l_iso_header_rec.user_id
461 , p_responsibility_id => l_iso_header_rec.responsibility_id
462 , p_transaction_type => l_iso_header_rec.transaction_type
463 , x_return_status => l_return_status
464 , x_Mesg_Token_Tbl => l_mesg_token_tbl
465 );
466
467 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Status Change Isolation completed with status ' || l_return_status) ; END IF ;
468 IF NVL(l_return_status, 'S') <> 'S' THEN
469 l_return_status := FND_API.G_RET_STS_ERROR;
470 RAISE l_err_status_change;
471
472 END IF;
473
474 END IF;
475
476 --If workflow is enabled and workflow approval is required call the procedure RAISE_WORKFLOW_EVENTS.
477
478 IF(l_workflow_enabled = 'Y')
479 AND (l_approval_required) THEN
480 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling the Raise_Workflow_Events procedure ...') ; END IF ;
481 Raise_Workflow_Events
482 ( p_api_version => 1.0,
483 p_validation_level => p_validation_level,
484 p_eam_iso_rec => l_iso_header_rec,
485 p_old_eam_iso_rec => l_old_iso_header_rec,
486 p_approval_required => l_approval_required,
487 p_new_system_status => l_new_system_status,
488 p_workflow_name => l_pending_workflow_name,
489 p_workflow_process => l_pending_workflow_process,
490 x_return_status => l_return_status,
491 x_mesg_token_tbl => l_mesg_token_tbl
492 );
493
494 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Raise_Workflow_Events procedure completed with status '||l_return_status) ; END IF ;
495 /* IF(l_return_status <> 'S') THEN
496 l_bo_return_status := l_return_status;
497 END IF;*/
498 END IF;
499
500 x_return_status :=l_return_status;
501
502
503 EXCEPTION
504
505 WHEN l_isolation_write_error then
506 ROLLBACK TO WORK_ISOLATION;
507 l_token_tbl(1).token_name := 'SAFETY_NAME';
508 l_token_tbl(1).token_value := l_iso_header_rec.ISOLATION_NAME;
509
510 l_out_mesg_token_tbl := l_mesg_token_tbl;
511 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
512 ( p_message_name => 'EAM_SAFETY_WRITE_ERROR'
513 , p_token_tbl => l_token_tbl
514 , p_mesg_token_tbl => l_mesg_token_tbl
515 , x_mesg_token_tbl => l_out_mesg_token_tbl
516 );
517 l_mesg_token_tbl := l_out_mesg_token_tbl;
518
519 x_return_status := FND_API.G_RET_STS_ERROR;
520 x_mesg_token_tbl := l_mesg_token_tbl ;
521
522 WHEN l_isolation_transaction_error then
523 ROLLBACK TO WORK_ISOLATION;
524 l_token_tbl(1).token_name := 'SAFETY_NAME';
525 l_token_tbl(1).token_value := l_iso_header_rec.ISOLATION_NAME;
526
527 l_out_mesg_token_tbl := l_mesg_token_tbl;
528 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
529 ( p_message_name => 'EAM_SAFETY_TRANSACTION_ERROR'
530 , p_token_tbl => l_token_tbl
531 , p_mesg_token_tbl => l_mesg_token_tbl
532 , x_mesg_token_tbl => l_out_mesg_token_tbl
533 );
534 l_mesg_token_tbl := l_out_mesg_token_tbl;
535
536 x_return_status := FND_API.G_RET_STS_ERROR;
537 x_mesg_token_tbl := l_mesg_token_tbl ;
538
539 WHEN l_isolation_existence_error then
540 ROLLBACK TO WORK_ISOLATION;
541 l_token_tbl(1).token_name := 'SAFETY_NAME';
542 l_token_tbl(1).token_value := l_iso_header_rec.ISOLATION_NAME;
543
544 l_out_mesg_token_tbl := l_mesg_token_tbl;
545 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
546 ( p_message_name => 'EAM_SAFETY_EXISTENCE_ERROR'
547 , p_token_tbl => l_token_tbl
548 , p_mesg_token_tbl => l_mesg_token_tbl
549 , x_mesg_token_tbl => l_out_mesg_token_tbl
550 );
551 l_mesg_token_tbl := l_out_mesg_token_tbl;
552
553 x_return_status := FND_API.G_RET_STS_ERROR;
554 x_mesg_token_tbl := l_mesg_token_tbl ;
555
556 WHEN l_isolation_required_error then
557 l_token_tbl(1).token_name := 'SAFETY_NAME';
558 l_token_tbl(1).token_value := l_iso_header_rec.ISOLATION_NAME;
559
560 ROLLBACK TO WORK_ISOLATION;
561 l_out_mesg_token_tbl := l_mesg_token_tbl;
562 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
563 ( p_message_name => 'EAM_SAFETY_REQUIRED_ERROR'
564 , p_token_tbl => l_token_tbl
565 , p_mesg_token_tbl => l_mesg_token_tbl
566 , x_mesg_token_tbl => l_out_mesg_token_tbl
567 );
568 l_mesg_token_tbl := l_out_mesg_token_tbl;
569
570 x_return_status := FND_API.G_RET_STS_ERROR;
571 x_mesg_token_tbl := l_mesg_token_tbl ;
572
573 WHEN l_isolation_attributes_error then
574 ROLLBACK TO WORK_ISOLATION;
575 l_token_tbl(1).token_name := 'SAFETY_NAME';
576 l_token_tbl(1).token_value := l_iso_header_rec.ISOLATION_NAME;
577
578 l_out_mesg_token_tbl := l_mesg_token_tbl;
579 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
580 ( p_message_name => 'EAM_SAFETY_ATTRIBUTE_ERROR'
581 , p_token_tbl => l_token_tbl
582 , p_mesg_token_tbl => l_mesg_token_tbl
583 , x_mesg_token_tbl => l_out_mesg_token_tbl
584 );
585 l_mesg_token_tbl := l_out_mesg_token_tbl;
586 x_return_status := FND_API.G_RET_STS_ERROR;
587 x_mesg_token_tbl := l_mesg_token_tbl ;
588
589 WHEN l_err_status_change then
590 ROLLBACK TO WORK_ISOLATION;
591 l_token_tbl(1).token_name := 'SAFETY_NAME';
592 l_token_tbl(1).token_value := l_iso_header_rec.ISOLATION_NAME;
593
594 l_out_mesg_token_tbl := l_mesg_token_tbl;
595 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
596 ( p_message_name => 'EAM_SAFETY_STATUS_CNG_ERROR'
597 , p_token_tbl => l_token_tbl
598 , p_mesg_token_tbl => l_mesg_token_tbl
599 , x_mesg_token_tbl => l_out_mesg_token_tbl
600 );
601 l_mesg_token_tbl := l_out_mesg_token_tbl;
602 x_return_status := FND_API.G_RET_STS_ERROR;
603 x_mesg_token_tbl := l_mesg_token_tbl ;
604
605
606 WHEN OTHERS THEN
607
608 ROLLBACK TO WORK_ISOLATION;
609 l_token_tbl(1).token_name := 'SAFETY_NAME';
610 l_token_tbl(1).token_value := l_iso_header_rec.ISOLATION_NAME;
611
612 l_out_mesg_token_tbl := l_mesg_token_tbl;
613 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
614 ( p_message_name => 'EAM_SAFETY_UNEXPECTED_ERROR'
615 , p_token_tbl => l_token_tbl
616 , p_mesg_token_tbl => l_mesg_token_tbl
617 , x_mesg_token_tbl => l_out_mesg_token_tbl
618 );
619 l_mesg_token_tbl := l_out_mesg_token_tbl;
620
621 x_return_status := FND_API.G_RET_STS_ERROR;
622 x_mesg_token_tbl := l_mesg_token_tbl ;
623
624
625
626 END WORK_ISOLATION;
627
628 /************************************************************
629 * Procedure: ESTABLISHMENT
630 * Purpose : This will process establishment records
631 ************************************************************/
632 PROCEDURE ESTABLISHMENT
633 ( p_organization_id IN NUMBER
634 , p_establish_iso_tbl IN EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type
635 , p_safety_source_id IN NUMBER
636 , x_establish_iso_tbl OUT NOCOPY EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type
637 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.MESG_TOKEN_TBL_TYPE
638 , x_return_status OUT NOCOPY VARCHAR2
639 ) IS
640
641 CURSOR C2 IS SELECT EAM_ESTABLISHMENT_DETAILS_S.NEXTVAL FROM SYS.DUAL;
642
643 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
644 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
645 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
646
647 l_return_status VARCHAR2(1);
648 L_count Number;
649 l_temp NUMBER;
650 l_establish_iso_rec EAM_PROCESS_SAFETY_PUB.eam_establishment_rec_type;
651 l_iso_seq NUMBER;
652
653 ISO_ESTABLISH_ERROR EXCEPTION;
654 SAF_ASSET_EXISTENCE_ERROR EXCEPTION;
655 L_estab_required_error Exception;
656 Saf_estab_seq_duplicate Exception;
657
658 BEGIN
659
660 SAVEPOINT ESTABLISHMENT;
661 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_PROCESS_ISOLATION_PVT.ESTABLISHMENT'); end if;
662
663
664 IF p_establish_iso_tbl.COUNT > 0 THEN
665 FOR i in p_establish_iso_tbl.FIRST..p_establish_iso_tbl.LAST LOOP
666
667 l_establish_iso_rec := p_establish_iso_tbl(i);
668
669 if l_establish_iso_rec.ESTABLISHMENT_SOURCE_ID is null then
670 l_establish_iso_rec.ESTABLISHMENT_SOURCE_ID := p_safety_source_id;
671 end if;
672
673 if l_establish_iso_rec.ORGANIZATION_ID is null then
674 l_establish_iso_rec.ORGANIZATION_ID :=p_organization_id;
675 end if;
676
677 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking asset existence for establishment'); end if;
678
679 SELECT COUNT(*)
680 INTO l_count
681 FROM csi_item_instances
682 WHERE instance_id = l_establish_iso_rec.maintenance_object_id
683 AND (last_vld_organization_id = p_organization_id
684 OR last_vld_organization_id IN (SELECT organization_id from mtl_parameters where MAINT_ORGANIZATION_ID = p_organization_id));
685
686 IF (l_count < 1)
687 THEN
688 RAISE SAF_ASSET_EXISTENCE_ERROR;
689 END IF;
690
691 If Eam_process_wo_pvt.Get_debug = 'Y' Then Eam_error_message_pvt.Write_debug(' Checking uniqueness for Sequence type'); End If;
692
693 SELECT COUNT(*)
694 Into L_count
695 From Eam_establishment_details
696 Where Establishment_source_id = L_establish_iso_rec.Establishment_source_id
697 And Sequence_type = L_establish_iso_rec.Sequence_type
698 And Organization_id= L_establish_iso_rec.Organization_id
699 And Sequence_number=L_establish_iso_rec.Sequence_number;
700
701 If L_establish_iso_rec.Transaction_type = Eam_process_wo_pvt.G_opr_create Then
702
703 IF (l_count > 0)
704 Then
705 Raise Saf_estab_seq_duplicate;
706 End If;
707 END IF;
708
709 IF l_establish_iso_rec.TRANSACTION_TYPE = EAM_PROCESS_WO_PVT.G_OPR_UPDATE THEN
710 BEGIN
711 Select Establishment_id
712 Into l_temp
713 From Eam_establishment_details
714 Where Establishment_source_id = L_establish_iso_rec.Establishment_source_id
715 And Sequence_type = L_establish_iso_rec.Sequence_type
716 And Organization_id= L_establish_iso_rec.Organization_id
717 And Sequence_number=L_establish_iso_rec.Sequence_number;
718 EXCEPTION
719 WHEN no_data_found THEN
720 l_temp :=null;
721 null;
722 END;
723
724 IF (l_temp <> L_establish_iso_rec.Establishment_id)
725 Then
726 Raise Saf_estab_seq_duplicate;
727 End If;
728
729 End If ;
730
731 IF l_establish_iso_rec.TRANSACTION_TYPE = EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
732 OPEN C2;
733 FETCH C2 INTO l_iso_seq;
734 CLOSE C2;
735 L_establish_iso_rec.Establishment_id :=L_iso_seq;
736 END IF;
737
738 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Checking Required attributes'); end if;
739
740 EAM_ESTABLISHMENT_UTILITY_PVT.CHECK_REQUIRED
741 ( p_safety_establishment_rec => l_establish_iso_rec
742 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
743 , x_return_Status => l_return_status
744 );
745 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
746 THEN
747 Raise L_estab_required_error;
748 End If;
749
750 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Perform_Writes'); end if;
751
752 EAM_ESTABLISHMENT_UTILITY_PVT.PERFORM_WRITES
753 ( p_safety_establishment_rec => l_establish_iso_rec
754 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
755 , x_return_Status => l_return_status
756 );
757
758 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
759 THEN
760 RAISE ISO_ESTABLISH_ERROR;
761 END IF;
762
763
764 END LOOP;
765 END IF;
766
767 x_return_status := l_return_status;
768
769 EXCEPTION
770 WHEN SAF_ASSET_EXISTENCE_ERROR THEN
771 ROLLBACK TO ESTABLISHMENT;
772
773 l_out_mesg_token_tbl := l_mesg_token_tbl;
774 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
775 ( p_message_name => 'SAF_ASSET_EXISTENCE_ERROR'
776 , p_token_tbl => l_token_tbl
777 , p_mesg_token_tbl => l_mesg_token_tbl
778 , x_mesg_token_tbl => l_out_mesg_token_tbl
779 );
780 l_mesg_token_tbl := l_out_mesg_token_tbl;
781 x_return_status := FND_API.G_RET_STS_ERROR;
782 x_mesg_token_tbl := l_mesg_token_tbl ;
783
784 WHEN l_estab_required_error THEN
785 ROLLBACK TO ESTABLISHMENT;
786
787 l_out_mesg_token_tbl := l_mesg_token_tbl;
788 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
789 ( p_message_name => 'l_estab_required_error'
790 , p_token_tbl => l_token_tbl
791 , p_mesg_token_tbl => l_mesg_token_tbl
792 , x_mesg_token_tbl => l_out_mesg_token_tbl
793 );
794 l_mesg_token_tbl := l_out_mesg_token_tbl;
795 x_return_status := FND_API.G_RET_STS_ERROR;
796 x_mesg_token_tbl := l_mesg_token_tbl ;
797
798 WHEN ISO_ESTABLISH_ERROR THEN
799 ROLLBACK TO ESTABLISHMENT;
800
801 l_out_mesg_token_tbl := l_mesg_token_tbl;
802 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
803 ( p_message_name => 'ISO_ESTABLISH_ERROR'
804 , p_token_tbl => l_token_tbl
805 , p_mesg_token_tbl => l_mesg_token_tbl
806 , x_mesg_token_tbl => l_out_mesg_token_tbl
807 );
808 l_mesg_token_tbl := l_out_mesg_token_tbl;
809 x_return_status := FND_API.G_RET_STS_ERROR;
810 X_mesg_token_tbl := L_mesg_token_tbl ;
811
812 When Saf_estab_seq_duplicate Then
813 ROLLBACK TO ESTABLISHMENT;
814 L_token_tbl(1).Token_name := 'SEQ_NUMBER';
815 L_TOKEN_TBL(1).TOKEN_VALUE := L_ESTABLISH_ISO_REC.SEQUENCE_NUMBER;
816
817 l_out_mesg_token_tbl := l_mesg_token_tbl;
818 Eam_error_message_pvt.Add_error_token
819 ( p_message_name => 'EAM_SAF_ESTAB_SEQ_DUPLICATE'
820 , p_token_tbl => l_token_tbl
821 , p_mesg_token_tbl => l_mesg_token_tbl
822 , x_mesg_token_tbl => l_out_mesg_token_tbl
823 );
824 l_mesg_token_tbl := l_out_mesg_token_tbl;
825 X_return_status := Fnd_api.G_ret_sts_error;
826 X_mesg_token_tbl := L_mesg_token_tbl ;
827
828 WHEN OTHERS THEN
829
830 ROLLBACK TO ESTABLISHMENT;
831 x_return_status := FND_API.G_RET_STS_ERROR;
832 END ESTABLISHMENT;
833
834 /********************************************************************
835 * Procedure: Raise_Workflow_Events
836 * Purpose: This procedure raises the workflow events for isolation release
837 *********************************************************************/
838
839
840 PROCEDURE RAISE_WORKFLOW_EVENTS
841 ( p_api_version IN NUMBER
842 , p_validation_level IN NUMBER
843 , p_eam_iso_rec IN EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type
844 , p_old_eam_iso_rec IN EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type
845 , p_approval_required IN BOOLEAN
846 , p_new_system_status IN NUMBER
847 , p_workflow_name IN VARCHAR2
848 , p_workflow_process IN VARCHAR2
849 , x_mesg_token_tbl IN OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
850 , x_return_status IN OUT NOCOPY VARCHAR2
851 ) IS
852
853 l_status_pending_event VARCHAR2(240);
854 l_event_name VARCHAR2(240);
855 l_parameter_list wf_parameter_list_t;
856 l_event_key VARCHAR2(200);
857 l_wf_event_seq NUMBER;
858 l_return_status VARCHAR2(1);
859 l_err_text VARCHAR2(2000);
860 l_msg_count NUMBER;
861 BEGIN
862
863
864 l_status_pending_event := 'oracle.apps.eam.isolation.status.change.pending';
865
866
867 --if status change needs approval
868 IF( p_approval_required AND (WF_EVENT.TEST(l_status_pending_event) <> 'NONE')) THEN
869
870 SELECT EAM_SAFETYWORKFLOW_EVENT_S.NEXTVAL
871 INTO l_wf_event_seq
872 FROM DUAL;
873
874 l_parameter_list := wf_parameter_list_t();
875 l_event_name := l_status_pending_event;
876
877 l_event_key := TO_CHAR(l_wf_event_seq);
878 INSERT INTO EAM_SAFETY_WORKFLOWS
879 (OBJECT_ID,TRANSACTION_ID,WORKFLOW_TYPE,LAST_UPDATE_DATE,LAST_UPDATED_BY,
880 CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
881 VALUES
882 (p_eam_iso_rec.isolation_id,l_wf_event_seq,1,SYSDATE,FND_GLOBAL.user_id,
883 SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id
884 );
885
886
887 WF_CORE.CONTEXT('Enterprise Asset Management...','Isolation Released change event','Building parameter list');
888 -- Add Parameters
889 Wf_Event.AddParameterToList(p_name =>'ISOLATION_ID',
890 p_value => TO_CHAR(p_eam_iso_rec.isolation_id),
891 p_parameterlist => l_parameter_list);
892 Wf_Event.AddParameterToList(p_name =>'ISOLATION_NAME',
893 p_value =>p_eam_iso_rec.isolation_name,
894 p_parameterlist => l_parameter_list);
895 Wf_Event.AddParameterToList(p_name =>'ORGANIZATION_ID',
896 p_value => TO_CHAR(p_eam_iso_rec.organization_id),
897 p_parameterlist => l_parameter_list);
898 Wf_Event.AddParameterToList(p_name =>'NEW_USER_STATUS',
899 p_value => TO_CHAR(p_eam_iso_rec.USER_DEFINED_STATUS),
900 p_parameterlist => l_parameter_list);
901 Wf_Event.AddParameterToList(p_name =>'OLD_SYSTEM_STATUS',
902 p_value => TO_CHAR(p_eam_iso_rec.SYSTEM_STATUS),
903 p_parameterlist => l_parameter_list);
904 Wf_Event.AddParameterToList(p_name =>'OLD_USER_STATUS',
905 p_value => TO_CHAR(p_old_eam_iso_rec.USER_DEFINED_STATUS),
906 p_parameterlist => l_parameter_list);
907 Wf_Event.AddParameterToList(p_name =>'NEW_SYSTEM_STATUS',
908 p_value => TO_CHAR(p_new_system_status),
909 p_parameterlist => l_parameter_list);
910 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_TYPE',
911 p_value => TO_CHAR(1),
912 p_parameterlist => l_parameter_list);
913 Wf_Event.AddParameterToList(p_name =>'REQUESTOR',
914 p_value =>FND_GLOBAL.USER_NAME ,
915 p_parameterlist => l_parameter_list);
916 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_NAME',
917 p_value => p_workflow_name,
918 p_parameterlist => l_parameter_list);
919 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_PROCESS',
920 p_value => p_workflow_process,
921 p_parameterlist => l_parameter_list);
922 Wf_Core.Context('Enterprise Asset Management...','Isolation Released Event','Raising event');
923
924 Wf_Event.Raise( p_event_name => l_event_name,
925 p_event_key => l_event_key,
926 p_parameters => l_parameter_list);
927 l_parameter_list.DELETE;
928 WF_CORE.CONTEXT('Enterprise Asset Management...','Isolation Released Event','After raising event');
929
930 END IF; --end of check for status change pending event
931
932 EXCEPTION
933 WHEN OTHERS THEN
934 WF_CORE.CONTEXT('Enterprise Asset Management...',l_event_name,'Exception during event construction and raise: ' || SQLERRM);
935 x_return_status := FND_API.G_RET_STS_ERROR;
936
937 END RAISE_WORKFLOW_EVENTS;
938
939 END EAM_PROCESS_ISOLATION_PVT;