[Home] [Help]
PACKAGE BODY: APPS.EAM_PROCESS_CLEARANCE_PVT
Source
1 PACKAGE BODY EAM_PROCESS_CLEARANCE_PVT AS
2 /* $Header: EAMVWCLB.pls 120.5 2011/07/15 11:42:39 vpasupur noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2011 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME: EAMVWCLB.pls
9 --
10 -- DESCRIPTION: Body of package EAM_PROCESS_CLEARANCE_PVT
11 --
12 -- NOTES
13 --
14 -- HISTORY
15 --
16 --
17 ***************************************************************************/
18 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_PROCESS_CLEARANCE_PVT';
19 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EAMVWCLB.pls';
20 G_DEBUG_FILENAME CONSTANT VARCHAR2(50) := 'EAM_SAFETY_DEBUG.log';
21
22
23 /**************************************************************************
24 * Procedure : PROCESS_WORK_CLEARANCE
25 * Purpose : This will process create/update work clearance
26 ***************************************************************************/
27 PROCEDURE PROCESS_WORK_CLEARANCE
28 ( p_bo_identifier IN VARCHAR2 := 'EAM'
29 , p_api_version_number IN NUMBER := 1.0
30 , p_init_msg_list IN BOOLEAN
31 , p_commit IN VARCHAR2
32 , p_work_clearance_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type
33 , p_clearance_establishment_tbl IN EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type
34 , p_clearance_re_estabish_tbl IN EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type
35 , p_clearance_workorder_asso_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type
36 , p_clearance_permit_asso_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type
37 , x_work_clearance_header_rec OUT NOCOPY EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type
38 , x_return_status OUT NOCOPY VARCHAR2
39 , x_msg_count OUT NOCOPY NUMBER
40 , p_debug IN VARCHAR2
41 , p_output_dir IN VARCHAR2
42 , p_debug_filename IN VARCHAR2
43 , p_debug_file_mode IN VARCHAR2
44 ) IS
45
46 l_api_name CONSTANT VARCHAR2(30) := G_PKG_NAME;
47 l_validation_level NUMBER :=p_work_clearance_header_rec.transaction_type;
48
49 l_return_status VARCHAR2(1);
50 l_msg_count NUMBER;
51 l_msg_data VARCHAR2(240);
52 lx_clearance_establishment_tbl EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type;
53 lx_clearance_re_estab_tbl EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type;
54 l_safety_wp_asso_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
55 l_safety_wo_asso_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
56 l_safety_assoc_rec EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type;
57
58 /* Error Handling Variables */
59 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type ;
60 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type ;
61 l_error_level NUMBER :=EAM_ERROR_MESSAGE_PVT.G_BO_LEVEL;
62 l_entity_index NUMBER :=1;
63 l_clearance_id NUMBER :=p_work_clearance_header_rec.work_clearance_id ;
64
65 BEGIN
66
67 SAVEPOINT PROCESS_WORK_CLEARANCE;
68
69 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_PROCESS_CLEARANCE_PVT.PROCESS_WORK_CLEARANCE'); end if;
70
71 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling WORK_CLEARANCE from PROCESS_WORK_CLEARANCE'); end if;
72
73 WORK_CLEARANCE
74 (
75 p_validation_level => l_validation_level
76 , p_work_clearance_id => p_work_clearance_header_rec.work_clearance_id
77 , p_organization_id => p_work_clearance_header_rec.organization_id
78 , p_work_clearance_header_rec => p_work_clearance_header_rec
79 , x_work_clearance_header_rec => x_work_clearance_header_rec
80 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
81 , x_return_status => l_return_status
82 );
83 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
84 THEN
85 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
86 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
87 , p_error_level => l_error_level
88 , p_entity_index => l_entity_index
89 );
90 raise fnd_api.g_exc_unexpected_error;
91 END IF;
92
93 l_clearance_id :=x_work_clearance_header_rec.work_clearance_id;
94
95
96 IF p_clearance_establishment_tbl.COUNT > 0 THEN
97 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling ESTABLISHMENT from PROCESS_WORK_CLEARANCE'); end if;
98
99 EAM_PROCESS_ISOLATION_PVT.ESTABLISHMENT
100 (
101 p_organization_id => p_work_clearance_header_rec.organization_id
102 , p_establish_iso_tbl => p_clearance_establishment_tbl
103 , p_safety_source_id => l_clearance_id
104 , x_establish_iso_tbl => lx_clearance_establishment_tbl
105 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
106 , x_return_status => l_return_status
107 );
108 END IF;
109
110 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
111 THEN
112 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
113 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
114 , p_error_level => l_error_level
115 , p_entity_index => l_entity_index
116 );
117 raise fnd_api.g_exc_unexpected_error;
118 END IF;
119
120 IF p_clearance_re_estabish_tbl.COUNT > 0 THEN
121 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Re-ESTABLISHMENT from PROCESS_WORK_CLEARANCE'); end if;
122
123 EAM_PROCESS_ISOLATION_PVT.ESTABLISHMENT
124 (
125 p_organization_id => p_work_clearance_header_rec.organization_id
126 , p_establish_iso_tbl => p_clearance_re_estabish_tbl
127 , p_safety_source_id => l_clearance_id
128 , x_establish_iso_tbl => lx_clearance_re_estab_tbl
129 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
130 , x_return_status => l_return_status
131 );
132 END IF;
133
134 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
135 THEN
136 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
137 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
138 , p_error_level => l_error_level
139 , p_entity_index => l_entity_index
140 );
141 raise fnd_api.g_exc_unexpected_error;
142 END IF;
143
144 IF p_clearance_permit_asso_tbl.COUNT > 0 THEN
145 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling PERMIT_CLEARANCE_ASSOCIATION from PROCESS_WORK_CLEARANCE'); end if;
146
147 FOR i in p_clearance_permit_asso_tbl.FIRST..p_clearance_permit_asso_tbl.LAST LOOP
148
149 l_safety_assoc_rec := p_clearance_permit_asso_tbl(i);
150
151 if l_safety_assoc_rec.target_ref_id is null then
152 l_safety_assoc_rec.target_ref_id := l_clearance_id;
153 end if;
154 l_safety_wp_asso_tbl(i) :=l_safety_assoc_rec;
155
156 END LOOP;
157
158
159
160 EAM_SAFETY_ASSOCIATION_PVT.PERMIT_CLEARANCE_ASSOCIATION
161 (
162 p_permit_clearance_asso_tbl => l_safety_wp_asso_tbl
163 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
164 , x_return_status => l_return_status
165 );
166
167 END IF;
168 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
169 THEN
170 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
171 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
172 , p_error_level => l_error_level
173 , p_entity_index => l_entity_index
174 );
175 raise fnd_api.g_exc_unexpected_error;
176 END IF;
177
178 IF p_clearance_workorder_asso_tbl.COUNT > 0 THEN
179 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling CLEARANCE_WO_ASSOCIATION from PROCESS_WORK_CLEARANCE'); end if;
180
181 FOR i in p_clearance_workorder_asso_tbl.FIRST..p_clearance_workorder_asso_tbl.LAST LOOP
182
183 l_safety_assoc_rec := p_clearance_workorder_asso_tbl(i);
184 if l_safety_assoc_rec.source_id is null then
185 l_safety_assoc_rec.source_id := l_clearance_id;
186 end if;
187 l_safety_wo_asso_tbl(i) :=l_safety_assoc_rec;
188
189 END LOOP;
190
191 EAM_SAFETY_ASSOCIATION_PVT.CLEARANCE_WO_ASSOCIATION
192 (
193 p_clearance_workorder_asso_tbl => l_safety_wo_asso_tbl
194 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
195 , x_return_status => l_return_status
196 );
197 END IF;
198
199
200 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
201 THEN
202 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
203 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
204 , p_error_level => l_error_level
205 , p_entity_index => l_entity_index
206 );
207 raise fnd_api.g_exc_unexpected_error;
208 END IF;
209
210 x_return_status :=l_return_status;
211
212 IF (p_commit = FND_API.G_TRUE) THEN
213 COMMIT;
214 END IF;
215
216
217 EXCEPTION
218
219 WHEN fnd_api.g_exc_unexpected_error THEN
220 ROLLBACK TO PROCESS_WORK_CLEARANCE;
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 x_msg_count := EAM_ERROR_MESSAGE_PVT.Get_Message_Count;
223
224 WHEN OTHERS THEN
225 ROLLBACK TO PROCESS_WORK_CLEARANCE;
226 x_return_status := FND_API.G_RET_STS_ERROR;
227
228
229 END PROCESS_WORK_CLEARANCE;
230
231
232 /**************************************************************************
233 * Procedure : WORK_CLEARANCE
234 * Purpose : This will process work clearance header
235 ***************************************************************************/
236 PROCEDURE WORK_CLEARANCE
237 ( p_validation_level IN NUMBER
238 , p_work_clearance_id IN NUMBER
239 , p_organization_id IN NUMBER
240 , p_work_clearance_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type
241 , x_work_clearance_header_rec OUT NOCOPY EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type
242 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.MESG_TOKEN_TBL_TYPE
243 , x_return_status OUT NOCOPY VARCHAR2
244 )IS
245
246 CURSOR C4 IS SELECT EAM_WORK_PERMIT_S.NEXTVAL FROM SYS.DUAL;
247
248 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
249 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
250 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
251 l_return_status VARCHAR2(1);
252
253
254 l_workflow_enabled VARCHAR2(1) :='N';
255 l_work_clearance_id NUMBER :=p_work_clearance_header_rec.WORK_CLEARANCE_ID;
256 l_work_clearance_header_rec EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type :=p_work_clearance_header_rec;
257 l_old_wc_header_rec EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type;
258 l_out_wc_header_rec EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type;
259
260
261 l_maintenance_object_source NUMBER :=1; -- To check if work flow is enabled
262 l_status_pending_event VARCHAR2(100) := 'oracle.apps.eam.clearance.status.change.pending';
263 l_approval_required BOOLEAN := FALSE; --set the flag to 'false' initially
264 l_pending_workflow_name VARCHAR2(100);
265 l_pending_workflow_process VARCHAR2(200) ;
266 l_new_system_status NUMBER;
267
268
269 l_clearance_write_error EXCEPTION;
270 l_clearance_transaction_error EXCEPTION;
271 l_clearance_existence_error EXCEPTION;
272 l_clearance_required_error EXCEPTION;
273 l_clearance_attributes_error EXCEPTION;
274 l_clearance_status_change EXCEPTION;
275
276 BEGIN
277
278 SAVEPOINT WORK_CLEARANCE;
279
280 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_PROCESS_CLEARANCE_PVT.WORK_CLEARANCE'); end if;
281
282 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling VALIDATE_TRANSACTION_TYPE'); end if;
283
284 EAM_PROCESS_CLEARANCE_PVT.VALIDATE_TRANSACTION_TYPE
285 ( p_validation_level => p_validation_level
286 , p_entity => 'WORK CLEARANCE'
287 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
288 , x_return_Status => l_return_status
289 );
290
291 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
292 THEN
293 RAISE l_clearance_transaction_error;
294 END IF;
295 -- test_mesg('status 1 :'||l_return_status);
296 IF (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_CREATE) THEN
297 IF (l_work_clearance_id IS NULL) OR (l_work_clearance_id = FND_API.G_MISS_NUM) THEN
298 OPEN C4;
299 FETCH C4 INTO l_work_clearance_id;
300 CLOSE C4;
301 l_work_clearance_header_rec.work_clearance_id :=l_work_clearance_id;
302 END IF;
303 END IF;
304 -- test_mesg('wc id :'||l_work_clearance_id);
305 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Check_Existence'); end if;
306
307 EAM_CLEARANCE_VALIDATE_PVT.Check_Existence
308 ( p_work_clearance_header_rec => p_work_clearance_header_rec
309 , x_clearance_header_rec => l_old_wc_header_rec
310 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
311 , x_return_Status => l_return_status
312 );
313 -- test_mesg('status 2 :'||l_return_status);
314 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
315 THEN
316 RAISE l_clearance_existence_error;
317 END IF;
318
319
320 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Populate_NULL_Columns'); end if;
321 IF (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_UPDATE) THEN
322
323 EAM_CLEARANCE_DEFAULT_PVT.Populate_NULL_Columns
324 ( p_eam_clearance_rec => l_work_clearance_header_rec
325 , p_old_eam_clearance_rec => l_old_wc_header_rec
326 , x_eam_clearance_rec => l_out_wc_header_rec
327 );
328 -- test_mesg('status 3 :'||l_return_status);
329 l_work_clearance_header_rec := l_out_wc_header_rec;
330 END IF;
331
332 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Check_required'); end if;
333
334 EAM_CLEARANCE_VALIDATE_PVT.CHECK_REQUIRED
335 ( p_work_clearance_header_rec => l_work_clearance_header_rec
336 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
337 , x_return_Status => l_return_status
338 );
339 -- test_mesg('status 4 :'||l_return_status);
340 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
341 THEN
342 RAISE l_clearance_required_error;
343 END IF;
344
345 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Check_attributes'); end if;
346
347 EAM_CLEARANCE_VALIDATE_PVT.CHECK_ATTRIBUTES
348 ( p_work_clearance_header_rec => l_work_clearance_header_rec
349 , p_old_clearance_header_rec => l_old_wc_header_rec
350 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
351 , x_return_Status => l_return_status
352 );
353 -- test_mesg('status 5 :'||l_return_status);
354 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
355 THEN
356 RAISE l_clearance_attributes_error;
357 END IF;
358
359 --Check if work flow is enabled and work permit requires an approval, set the pending flag to ?Y? in the work permit record.
360
361 -- GET WORKFLOW ENABLED
362
363 BEGIN
364 SELECT EAM_SAFETY_WORKFLOW_ENABLED
365 INTO l_workflow_enabled
366 FROM WIP_EAM_PARAMETERS
367 WHERE organization_id =p_organization_id;
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 l_workflow_enabled := 'N';
371 END;
372
373 IF(l_workflow_enabled = 'Y') THEN --if workflow is enabled
374 IF((p_work_clearance_header_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_CREATE ) --created
375 OR (p_work_clearance_header_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE --workorder updated
376 AND NVL(l_old_wc_header_rec.pending_flag,'N') = 'N' --old status is not pending
377 --and old status is not same as new status
378 AND (l_old_wc_header_rec.system_status <>l_work_clearance_header_rec.system_status)
379 )
380 ) THEN
381
382 IF(WF_EVENT.TEST(l_status_pending_event) <> 'NONE') THEN
383
384 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Calling Wkflow required check API...') ; END IF;
385
386
387 EAM_CLEARANCE_WORKFLOW_PVT.Is_Approval_Required_Released
388 (p_old_clearance_rec => l_old_wc_header_rec,
389 p_new_clearance_rec => l_work_clearance_header_rec,
390 x_approval_required => l_approval_required,
391 x_workflow_name => l_pending_workflow_name,
392 x_workflow_process => l_pending_workflow_process
393 );
394
395 IF(l_approval_required) THEN
396 l_work_clearance_header_rec.pending_flag:='Y'; --if approval required set the pending flag and system status to previous status
397 l_new_system_status := p_work_clearance_header_rec.system_status;
398 l_work_clearance_header_rec.system_status := NVL(l_old_wc_header_rec.system_status,17);
399
400 END IF;
401 END IF; --end of check for status event enabled
402
403 END IF;
404
405
406 END IF; --end of check for workflow enabled
407
408 -- Put clearance completion details if status is changed to complete
409
410 IF (l_work_clearance_header_rec.SYSTEM_STATUS in (wip_constants.comp_chrg,wip_constants.comp_nochrg)) THEN
411
412 IF (l_old_wc_header_rec.SYSTEM_STATUS = wip_constants.draft) THEN
413 RAISE l_clearance_status_change;
414 ELSE
415 l_work_clearance_header_rec.COMPLETION_DATE := NVL(p_work_clearance_header_rec.COMPLETION_DATE,sysdate);
416 END IF;
417 END IF;
418
419 -- Remove clearance completion details if status is changed to released
420 IF (l_work_clearance_header_rec.SYSTEM_STATUS = wip_constants.released) THEN
421
422 IF (l_old_wc_header_rec.SYSTEM_STATUS in (wip_constants.comp_chrg,wip_constants.comp_nochrg)) THEN
423 l_work_clearance_header_rec.COMPLETION_DATE := null;
424 END IF;
425 END IF;
426
427 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Perform_Writes'); end if;
428
429 EAM_CLEARANCE_UTILITY_PVT.PERFORM_WRITES
430 ( p_work_clearance_header_rec => l_work_clearance_header_rec
431 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
432 , x_return_Status => l_return_status
433 );
434 -- test_mesg('status 6 :'||l_return_status);
435 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
436 THEN
437 RAISE l_clearance_write_error;
438 END IF;
439
440 x_work_clearance_header_rec :=l_work_clearance_header_rec;
441
442 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Created Clearance with id: '||l_work_clearance_header_rec.WORK_CLEARANCE_ID); end if;
443
444 --If clearance is approved and no workflow then call the procedure CHANGE_WORK_PERMIT_STATUS
445 IF((l_workflow_enabled = 'N')
446 OR (p_work_clearance_header_rec.APPROVED_BY IS NOT NULL)
447 OR (p_work_clearance_header_rec.APPROVED_BY <> FND_API.G_MISS_NUM))
448 AND (l_old_wc_header_rec.system_status <>l_work_clearance_header_rec.system_status) THEN
449
450 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling CHANGE_WORK_PERMIT_STATUS') ; END IF ;
451
452 EAM_CLEARANCE_UTILITY_PVT.CHANGE_CLEARANCE_STATUS
453 ( p_clearance_id => l_work_clearance_header_rec.WORK_CLEARANCE_ID
454 , p_organization_id => p_work_clearance_header_rec.organization_id
455 , p_to_status_type => p_work_clearance_header_rec.system_status
456 , p_user_id => p_work_clearance_header_rec.user_id
457 , p_responsibility_id => p_work_clearance_header_rec.responsibility_id
458 , p_transaction_type => p_work_clearance_header_rec.transaction_type
459 , x_return_status => l_return_status
460 , x_Mesg_Token_Tbl => l_mesg_token_tbl
461 );
462
463 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Status Change completed with status ' || l_return_status) ; END IF ;
464 IF NVL(l_return_status, 'S') <> 'S' THEN
465 l_return_status := FND_API.G_RET_STS_ERROR;
466 RAISE l_clearance_status_change;
467
468 END IF;
469
470 END IF;
471
472
473 --If workflow is enabled and workflow approval is required call the procedure RAISE_WORKFLOW_EVENTS.
474
475 IF(l_workflow_enabled = 'Y')
476 AND (l_approval_required) THEN
477 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling the Raise_Workflow_Events procedure ...') ; END IF ;
478 Raise_Workflow_Events
479 ( p_api_version => 1.0,
480 p_validation_level => p_validation_level,
481 p_eam_wc_rec => l_work_clearance_header_rec,
482 p_old_eam_wc_rec => l_old_wc_header_rec,
483 p_approval_required => l_approval_required,
484 p_new_system_status => l_new_system_status,
485 p_workflow_name => l_pending_workflow_name,
486 p_workflow_process => l_pending_workflow_process,
487 x_return_status => l_return_status,
488 x_mesg_token_tbl => l_mesg_token_tbl
489 );
490
491 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 ;
492
493 END IF;
494
495 x_return_status :=l_return_status;
496
497 EXCEPTION
498
499 WHEN l_clearance_write_error then
500 l_token_tbl(1).token_name := 'SAFETY_NAME';
501 l_token_tbl(1).token_value := p_work_clearance_header_rec.WORK_CLEARANCE_NAME;
502
503 l_out_mesg_token_tbl := l_mesg_token_tbl;
504 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
505 ( p_message_name => 'EAM_SAFETY_WRITE_ERROR'
506 , p_token_tbl => l_token_tbl
507 , p_mesg_token_tbl => l_mesg_token_tbl
508 , x_mesg_token_tbl => l_out_mesg_token_tbl
509 );
510 l_mesg_token_tbl := l_out_mesg_token_tbl;
511
512 x_return_status := FND_API.G_RET_STS_ERROR;
513 x_mesg_token_tbl := l_mesg_token_tbl ;
514 ROLLBACK TO WORK_CLEARANCE;
515
516 WHEN l_clearance_transaction_error then
517 l_token_tbl(1).token_name := 'SAFETY_NAME';
518 l_token_tbl(1).token_value := p_work_clearance_header_rec.WORK_CLEARANCE_NAME;
519
520 l_out_mesg_token_tbl := l_mesg_token_tbl;
521 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
522 ( p_message_name => 'EAM_SAFETY_TRANSACTION_ERROR'
523 , p_token_tbl => l_token_tbl
524 , p_mesg_token_tbl => l_mesg_token_tbl
525 , x_mesg_token_tbl => l_out_mesg_token_tbl
526 );
527 l_mesg_token_tbl := l_out_mesg_token_tbl;
528
529 x_return_status := FND_API.G_RET_STS_ERROR;
530 x_mesg_token_tbl := l_mesg_token_tbl ;
531 ROLLBACK TO WORK_CLEARANCE;
532
533 WHEN l_clearance_existence_error then
534 l_token_tbl(1).token_name := 'SAFETY_NAME';
535 l_token_tbl(1).token_value := p_work_clearance_header_rec.WORK_CLEARANCE_NAME;
536
537 l_out_mesg_token_tbl := l_mesg_token_tbl;
538 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
539 ( p_message_name => 'EAM_SAFETY_EXISTENCE_ERROR'
540 , p_token_tbl => l_token_tbl
541 , p_mesg_token_tbl => l_mesg_token_tbl
542 , x_mesg_token_tbl => l_out_mesg_token_tbl
543 );
544 l_mesg_token_tbl := l_out_mesg_token_tbl;
545
546 x_return_status := FND_API.G_RET_STS_ERROR;
547 x_mesg_token_tbl := l_mesg_token_tbl ;
548 ROLLBACK TO WORK_CLEARANCE;
549 WHEN l_clearance_required_error then
550 l_token_tbl(1).token_name := 'SAFETY_NAME';
551 l_token_tbl(1).token_value := p_work_clearance_header_rec.WORK_CLEARANCE_NAME;
552
553 l_out_mesg_token_tbl := l_mesg_token_tbl;
554 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
555 ( p_message_name => 'EAM_SAFETY_REQUIRED_ERROR'
556 , p_token_tbl => l_token_tbl
557 , p_mesg_token_tbl => l_mesg_token_tbl
558 , x_mesg_token_tbl => l_out_mesg_token_tbl
559 );
560 l_mesg_token_tbl := l_out_mesg_token_tbl;
561
562 x_return_status := FND_API.G_RET_STS_ERROR;
563 x_mesg_token_tbl := l_mesg_token_tbl ;
564 ROLLBACK TO WORK_CLEARANCE;
565 WHEN l_clearance_attributes_error then
566 l_token_tbl(1).token_name := 'SAFETY_NAME';
567 l_token_tbl(1).token_value := p_work_clearance_header_rec.WORK_CLEARANCE_NAME;
568
569 l_out_mesg_token_tbl := l_mesg_token_tbl;
570 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
571 ( p_message_name => 'EAM_SAFETY_ATTRIBUTE_ERROR'
572 , p_token_tbl => l_token_tbl
573 , p_mesg_token_tbl => l_mesg_token_tbl
574 , x_mesg_token_tbl => l_out_mesg_token_tbl
575 );
576
577 l_mesg_token_tbl := l_out_mesg_token_tbl;
578 x_return_status := FND_API.G_RET_STS_ERROR;
579 x_mesg_token_tbl := l_mesg_token_tbl ;
580 ROLLBACK TO WORK_CLEARANCE;
581 WHEN l_clearance_status_change then
582 l_token_tbl(1).token_name := 'SAFETY_NAME';
583 l_token_tbl(1).token_value := p_work_clearance_header_rec.WORK_CLEARANCE_NAME;
584
585 l_out_mesg_token_tbl := l_mesg_token_tbl;
586 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
587 ( p_message_name => 'EAM_SAFETY_STATUS_CNG_ERROR'
588 , p_token_tbl => l_token_tbl
589 , p_mesg_token_tbl => l_mesg_token_tbl
590 , x_mesg_token_tbl => l_out_mesg_token_tbl
591 );
592 l_mesg_token_tbl := l_out_mesg_token_tbl;
593 x_return_status := FND_API.G_RET_STS_ERROR;
594 x_mesg_token_tbl := l_mesg_token_tbl ;
595 ROLLBACK TO WORK_CLEARANCE;
596
597 WHEN OTHERS THEN
598 l_token_tbl(1).token_name := 'SAFETY_NAME';
599 l_token_tbl(1).token_value := p_work_clearance_header_rec.WORK_CLEARANCE_NAME;
600 l_out_mesg_token_tbl := l_mesg_token_tbl;
601 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
602 ( p_message_name => 'EAM_SAFETY_UNEXPECTED_ERROR'
603 , p_token_tbl => l_token_tbl
604 , p_mesg_token_tbl => l_mesg_token_tbl
605 , x_mesg_token_tbl => l_out_mesg_token_tbl
606 );
607 l_mesg_token_tbl := l_out_mesg_token_tbl;
608
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 x_mesg_token_tbl := l_mesg_token_tbl ;
611 ROLLBACK TO WORK_CLEARANCE;
612
613 END WORK_CLEARANCE;
614
615
616 /********************************************************************
617 * Procedure: Raise_Workflow_Events
618 *Purpose: This procedure raises the workflow events for clearance release
619 *********************************************************************/
620 PROCEDURE RAISE_WORKFLOW_EVENTS
621 ( p_api_version IN NUMBER
622 , p_validation_level IN NUMBER
623 , p_eam_wc_rec IN EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type
624 , p_old_eam_wc_rec IN EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type
625 , p_approval_required IN BOOLEAN
626 , p_new_system_status IN NUMBER
627 , p_workflow_name IN VARCHAR2
628 , p_workflow_process IN VARCHAR2
629 , x_mesg_token_tbl IN OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
630 , x_return_status IN OUT NOCOPY VARCHAR2
631 ) IS
632 l_status_pending_event VARCHAR2(240);
633 l_event_name VARCHAR2(240);
634 l_parameter_list wf_parameter_list_t;
635 l_event_key VARCHAR2(200);
636 l_wf_event_seq NUMBER;
637 l_return_status VARCHAR2(1);
638 l_msg_count NUMBER;
639 BEGIN
640
641
642 l_status_pending_event := 'oracle.apps.eam.clearance.status.change.pending';
643
644
645 --if status change needs approval
646 IF( p_approval_required AND (WF_EVENT.TEST(l_status_pending_event) <> 'NONE')) THEN
647
648 SELECT EAM_SAFETYWORKFLOW_EVENT_S.NEXTVAL
649 INTO l_wf_event_seq
650 FROM DUAL;
651
652 l_parameter_list := wf_parameter_list_t();
653 l_event_name := l_status_pending_event;
654
655 l_event_key := TO_CHAR(l_wf_event_seq);
656
657 INSERT INTO EAM_SAFETY_WORKFLOWS
658 (OBJECT_ID,TRANSACTION_ID,WORKFLOW_TYPE,LAST_UPDATE_DATE,LAST_UPDATED_BY,
659 CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
660 VALUES
661 (p_eam_wc_rec.WORK_CLEARANCE_ID,l_wf_event_seq,2,SYSDATE,FND_GLOBAL.user_id,
662 SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id
663 );
664
665
666 WF_CORE.CONTEXT('Enterprise Asset Management...','Work Permit Released change event','Building parameter list');
667 -- Add Parameters
668 Wf_Event.AddParameterToList(p_name =>'WORK_CLEARANCE_ID',
669 p_value => TO_CHAR(p_eam_wc_rec.WORK_CLEARANCE_ID),
670 p_parameterlist => l_parameter_list);
671 Wf_Event.AddParameterToList(p_name =>'WORK_CLEARANCE_NAME',
672 p_value =>p_eam_wc_rec.WORK_CLEARANCE_NAME,
673 p_parameterlist => l_parameter_list);
674 Wf_Event.AddParameterToList(p_name =>'ORGANIZATION_ID',
675 p_value => TO_CHAR(p_eam_wc_rec.organization_id),
676 p_parameterlist => l_parameter_list);
677 Wf_Event.AddParameterToList(p_name =>'NEW_USER_STATUS',
678 p_value => TO_CHAR(p_eam_wc_rec.user_defined_status),
679 p_parameterlist => l_parameter_list);
680 Wf_Event.AddParameterToList(p_name =>'OLD_SYSTEM_STATUS',
681 p_value => TO_CHAR(p_eam_wc_rec.SYSTEM_STATUS),
682 p_parameterlist => l_parameter_list);
683 Wf_Event.AddParameterToList(p_name =>'OLD_USER_STATUS',
684 p_value => TO_CHAR(p_old_eam_wc_rec.user_defined_status),
685 p_parameterlist => l_parameter_list);
686 Wf_Event.AddParameterToList(p_name =>'NEW_SYSTEM_STATUS',
687 p_value => TO_CHAR(p_new_system_status),
688 p_parameterlist => l_parameter_list);
689 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_TYPE',
690 p_value => TO_CHAR(2),
691 p_parameterlist => l_parameter_list);
692 Wf_Event.AddParameterToList(p_name =>'REQUESTOR',
693 p_value =>FND_GLOBAL.USER_NAME ,
694 p_parameterlist => l_parameter_list);
695 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_NAME',
696 p_value => p_workflow_name,
697 p_parameterlist => l_parameter_list);
698 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_PROCESS',
699 p_value => p_workflow_process,
700 p_parameterlist => l_parameter_list);
701 Wf_Core.Context('Enterprise Asset Management...','Work Clearance Released Event','Raising event');
702
703 Wf_Event.Raise( p_event_name => l_event_name,
704 p_event_key => l_event_key,
705 p_parameters => l_parameter_list);
706 l_parameter_list.DELETE;
707 WF_CORE.CONTEXT('Enterprise Asset Management...','Work Clearance Released Event','After raising event');
708
709 END IF; --end of check for status change pending event
710
711 EXCEPTION
712 WHEN OTHERS THEN
713 WF_CORE.CONTEXT('Enterprise Asset Management...',l_event_name,'Exception during event construction and raise: ' || SQLERRM);
714 x_return_status := FND_API.G_RET_STS_ERROR;
715
716 END RAISE_WORKFLOW_EVENTS;
717
718 /*********************************************************
719 * Procedure : Validate_Transaction_Type
720 * Purpose : This procedure will check if the transaction type is valid
721 for a particular entity.
722 *********************************************************/
723
724 PROCEDURE VALIDATE_TRANSACTION_TYPE(
725 p_validation_level IN NUMBER
726 , p_entity IN VARCHAR2
727 , x_return_status OUT NOCOPY VARCHAR2
728 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
729 )IS
730 BEGIN
731
732 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_PROCESS_CLEARANCE_PVT.VALIDATE_TRANSACTION_TYPE'); end if;
733
734 IF (p_entity = 'WORK CLEARANCE') THEN
735 IF (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_CREATE)
736 OR (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_UPDATE) then
737 x_return_status := FND_API.G_RET_STS_SUCCESS;
738
739 ELSE
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 END IF;
742
743 END IF;
744 END VALIDATE_TRANSACTION_TYPE;
745
746 /**************************************************************************
747 * Procedure: COPY_WORK_CLEARANCE
748 * Purpose: Procedure to copy work clearance record.
749 *
750 ***************************************************************************/
751
752 PROCEDURE COPY_WORK_CLEARANCE(
753 p_bo_identifier IN VARCHAR2 := 'EAM'
754 , p_api_version_number IN NUMBER := 1.0
755 , p_init_msg_list IN BOOLEAN := FALSE
756 , p_commit IN VARCHAR2
757 , p_debug IN VARCHAR2
758 , p_output_dir IN VARCHAR2
759 , p_debug_filename IN VARCHAR2
760 , p_debug_file_mode IN VARCHAR2
761 , p_org_id IN NUMBER
762 , px_clearance_id IN OUT NOCOPY NUMBER
763 , x_return_status OUT NOCOPY VARCHAR2
764 , x_msg_count OUT NOCOPY NUMBER
765
766 )IS
767 CURSOR C IS SELECT EAM_CLEARANCE_NAME_S.NEXTVAL FROM SYS.DUAL;
768 l_api_name CONSTANT VARCHAR2(30) := G_PKG_NAME;
769 l_validation_level NUMBER := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
770
771 l_return_status VARCHAR2(1);
772 l_msg_count NUMBER;
773 l_clearance_id NUMBER:=px_clearance_id;
774 l_org_id NUMBER :=p_org_id;
775 -- l_permit_name_char VARCHAR2(240);
776 l_clearance_name NUMBER;
777
778 l_wc_header_rec EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type;
779 l_out_wc_header_rec EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type;
780
781 /* Error Handling Variables */
782 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type ;
783 l_error_level NUMBER :=EAM_ERROR_MESSAGE_PVT.G_BO_LEVEL;
784 l_entity_index number :=1;
785
786
787 --Variables for copying estimation details
788 l_safety_estab_rec EAM_PROCESS_SAFETY_PUB.eam_establishment_rec_type;
789 l_establish_tbl EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type;
790 lx_establish_tbl EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type;
791
792 CURSOR saf_establishment_cur IS
793 SELECT eed.*
794 FROM eam_establishment_details eed
795 WHERE ESTABLISHMENT_SOURCE_ID = l_clearance_id
796 AND organization_id = l_org_id
797 ORDER BY SEQUENCE_TYPE , SEQUENCE_NUMBER ;
798
799 l_safety_estab_cur_rec saf_establishment_cur%ROWTYPE;
800
801
802 BEGIN
803
804 SAVEPOINT COPY_WORK_CLEARANCE;
805 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Start of copy clearance '); end if;
806 EAM_CLEARANCE_UTILITY_PVT.QUERY_ROW
807 ( p_work_clearance_id => l_clearance_id
808 , p_organization_id => l_org_id
809 , x_work_clearance_header_rec => l_wc_header_rec
810 , x_return_Status => l_return_status
811 );
812
813 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
814 THEN
815 RAISE fnd_api.g_exc_unexpected_error;
816 END IF;
817 /*Get the next permit name sequence*/
818 OPEN C;
819 FETCH C INTO l_clearance_name;
820 CLOSE C;
821
822 l_wc_header_rec.WORK_CLEARANCE_ID :=null;
823 l_wc_header_rec.WORK_CLEARANCE_NAME :=to_char(l_clearance_name);
824 l_wc_header_rec.transaction_type :=EAM_PROCESS_WO_PVT.G_OPR_CREATE;
825 l_wc_header_rec.pending_flag := null;
826 l_wc_header_rec.completion_date := null;
827 l_wc_header_rec.approved_by := null;
828
829 IF l_wc_header_rec.SCHEDULED_ESTAB_START_DATE < SYSDATE THEN
830 l_wc_header_rec.SCHEDULED_ESTAB_START_DATE :=SYSDATE;
831 END IF;
832
833 IF l_wc_header_rec.SCHEDULED_ESTAB_END_DATE < SYSDATE THEN
834 l_wc_header_rec.SCHEDULED_ESTAB_END_DATE :=SYSDATE;
835 END IF;
836
837 IF l_wc_header_rec.SCHEDULED_REESTAB_START_DATE < SYSDATE THEN
838 l_wc_header_rec.SCHEDULED_REESTAB_START_DATE :=SYSDATE;
839 END IF;
840
841 IF l_wc_header_rec.SCHEDULED_REESTAB_END_DATE < SYSDATE THEN
842 l_wc_header_rec.SCHEDULED_REESTAB_END_DATE :=SYSDATE;
843 END IF;
844
845 -- IF (l_work_permit_header_rec.STATUS_TYPE = wip_constants.comp_chrg) THEN
846 l_wc_header_rec.SYSTEM_STATUS :=wip_constants.draft;
847 l_wc_header_rec.USER_DEFINED_STATUS :=217; --new user defined status for clearance as per changes made to eam_safety_usr_def_statuses_b
848 -- END IF;
849
850
851 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling WORK_CLEARANCE from COPY_WORK_CLEARANCE'); end if;
852 WORK_CLEARANCE
853 (
854 p_validation_level => l_validation_level
855 , p_work_clearance_id => l_clearance_id
856 , p_organization_id => l_org_id
857 , p_work_clearance_header_rec => l_wc_header_rec
858 , x_work_clearance_header_rec => l_out_wc_header_rec
859 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
860 , x_return_status => l_return_status
861 );
862
863 -- x_return_status :=l_return_status;
864
865 IF(l_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
866 px_clearance_id :=l_out_wc_header_rec.WORK_CLEARANCE_ID;
867 /* IF(p_commit = FND_API.G_TRUE) THEN
868 COMMIT;
869 END IF;*/
870 END IF;
871
872
873 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
874 THEN
875 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
876 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
877 , p_error_level => l_error_level
878 , p_entity_index => l_entity_index
879 );
880 raise fnd_api.g_exc_unexpected_error;
881 END IF;
882
883 -- Copy all establishment and re-establishment records
884 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Start of copy ESTABLISHMENT from copy clearance '); end if;
885
886 FOR l_safety_estab_cur_rec IN saf_establishment_cur
887 LOOP
888 -- l_safety_estab_rec :=l_safety_estab_cur_rec;
889 l_safety_estab_rec.ESTABLISHMENT_ID :=null;
890 l_safety_estab_rec.ESTABLISHMENT_SOURCE :=l_safety_estab_cur_rec.ESTABLISHMENT_SOURCE;
891 l_safety_estab_rec.ESTABLISHMENT_SOURCE_ID :=px_clearance_id;
892 l_safety_estab_rec.SEQUENCE_TYPE :=l_safety_estab_cur_rec.SEQUENCE_TYPE;
893 l_safety_estab_rec.SEQUENCE_NUMBER :=l_safety_estab_cur_rec.SEQUENCE_NUMBER;
894 l_safety_estab_rec.ORGANIZATION_ID :=l_safety_estab_cur_rec.ORGANIZATION_ID;
895 l_safety_estab_rec.DESCRIPTION :=l_safety_estab_cur_rec.DESCRIPTION;
896 l_safety_estab_rec.MAINTENANCE_OBJECT_ID :=l_safety_estab_cur_rec.MAINTENANCE_OBJECT_ID;
897 l_safety_estab_rec.TAG_POSITION :=l_safety_estab_cur_rec.TAG_POSITION;
898 l_safety_estab_rec.SAFETY_TAG :=l_safety_estab_cur_rec.SAFETY_TAG;
899 l_safety_estab_rec.LOCKOUT_DEVICE :=l_safety_estab_cur_rec.LOCKOUT_DEVICE;
900 l_safety_estab_rec.TRANSACTION_TYPE :=EAM_PROCESS_WO_PVT.G_OPR_CREATE;
901 l_safety_estab_rec.DEPT_ID :=l_safety_estab_cur_rec.DEPT_ID;
902 l_safety_estab_rec.RESOURCE_ID :=l_safety_estab_cur_rec.RESOURCE_ID;
903 l_safety_estab_rec.RES_INSTANCE_ID :=l_safety_estab_cur_rec.RES_INSTANCE_ID;
904 l_safety_estab_rec.LOCK_NUMBER :=l_safety_estab_cur_rec.LOCK_NUMBER;
905 l_safety_estab_rec.COMPLETION_DATE :=null;
906 l_safety_estab_rec.ATTRIBUTE_CATEGORY :=l_safety_estab_cur_rec.ATTRIBUTE_CATEGORY;
907 l_safety_estab_rec.ATTRIBUTE1 :=l_safety_estab_cur_rec.ATTRIBUTE1;
908 l_safety_estab_rec.ATTRIBUTE2 :=l_safety_estab_cur_rec.ATTRIBUTE2;
909 l_safety_estab_rec.ATTRIBUTE3 :=l_safety_estab_cur_rec.ATTRIBUTE3;
910 l_safety_estab_rec.ATTRIBUTE4 :=l_safety_estab_cur_rec.ATTRIBUTE4;
911 l_safety_estab_rec.ATTRIBUTE5 :=l_safety_estab_cur_rec.ATTRIBUTE5;
912 l_safety_estab_rec.ATTRIBUTE6 :=l_safety_estab_cur_rec.ATTRIBUTE6;
913 l_safety_estab_rec.ATTRIBUTE7 :=l_safety_estab_cur_rec.ATTRIBUTE7;
914 l_safety_estab_rec.ATTRIBUTE8 :=l_safety_estab_cur_rec.ATTRIBUTE8;
915 l_safety_estab_rec.ATTRIBUTE9 :=l_safety_estab_cur_rec.ATTRIBUTE9;
916 l_safety_estab_rec.ATTRIBUTE10 :=l_safety_estab_cur_rec.ATTRIBUTE10;
917 l_safety_estab_rec.ATTRIBUTE11 :=l_safety_estab_cur_rec.ATTRIBUTE11;
918 l_safety_estab_rec.ATTRIBUTE12 :=l_safety_estab_cur_rec.ATTRIBUTE12;
919 l_safety_estab_rec.ATTRIBUTE13 :=l_safety_estab_cur_rec.ATTRIBUTE13;
920 l_safety_estab_rec.ATTRIBUTE14 :=l_safety_estab_cur_rec.ATTRIBUTE14;
921 l_safety_estab_rec.ATTRIBUTE15 :=l_safety_estab_cur_rec.ATTRIBUTE15;
922 l_safety_estab_rec.ATTRIBUTE16 :=l_safety_estab_cur_rec.ATTRIBUTE16;
923 l_safety_estab_rec.ATTRIBUTE17 :=l_safety_estab_cur_rec.ATTRIBUTE17;
924 l_safety_estab_rec.ATTRIBUTE18 :=l_safety_estab_cur_rec.ATTRIBUTE18;
925 l_safety_estab_rec.ATTRIBUTE19 :=l_safety_estab_cur_rec.ATTRIBUTE19;
926 l_safety_estab_rec.ATTRIBUTE20 :=l_safety_estab_cur_rec.ATTRIBUTE20;
927 l_safety_estab_rec.ATTRIBUTE21 :=l_safety_estab_cur_rec.ATTRIBUTE21;
928 l_safety_estab_rec.ATTRIBUTE22 :=l_safety_estab_cur_rec.ATTRIBUTE22;
929 l_safety_estab_rec.ATTRIBUTE23 :=l_safety_estab_cur_rec.ATTRIBUTE23;
930 l_safety_estab_rec.ATTRIBUTE24 :=l_safety_estab_cur_rec.ATTRIBUTE24;
931 l_safety_estab_rec.ATTRIBUTE25 :=l_safety_estab_cur_rec.ATTRIBUTE25;
932 l_safety_estab_rec.ATTRIBUTE26 :=l_safety_estab_cur_rec.ATTRIBUTE26;
933 l_safety_estab_rec.ATTRIBUTE27 :=l_safety_estab_cur_rec.ATTRIBUTE27;
934 l_safety_estab_rec.ATTRIBUTE28 :=l_safety_estab_cur_rec.ATTRIBUTE28;
935 l_safety_estab_rec.ATTRIBUTE29 :=l_safety_estab_cur_rec.ATTRIBUTE29;
936 l_safety_estab_rec.ATTRIBUTE30 :=l_safety_estab_cur_rec.ATTRIBUTE30;
937 l_safety_estab_rec.CREATED_BY :=l_safety_estab_cur_rec.CREATED_BY;
938 l_safety_estab_rec.CREATION_DATE :=sysdate;
939
940 l_establish_tbl(l_establish_tbl.count + 1) :=l_safety_estab_rec;
941
942 END LOOP;
943
944 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling ESTABLISHMENT from COPY_WORK_CLEARANCE'); end if;
945 IF l_establish_tbl.count >0 then
946 EAM_PROCESS_ISOLATION_PVT.ESTABLISHMENT(
947 p_organization_id => l_org_id
948 , p_establish_iso_tbl => l_establish_tbl
949 , p_safety_source_id => px_clearance_id
950 , x_establish_iso_tbl => lx_establish_tbl
951 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
952 , x_return_status => l_return_status
953 );
954
955 END IF;
956
957 x_return_status :=l_return_status;
958
959 IF(x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
960 IF(p_commit = FND_API.G_TRUE) THEN
961 COMMIT;
962 END IF;
963 END IF;
964
965
966 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
967 THEN
968 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
969 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
970 , p_error_level => l_error_level
971 , p_entity_index => l_entity_index
972 );
973 raise fnd_api.g_exc_unexpected_error;
974 END IF;
975
976 EXCEPTION
977 WHEN fnd_api.g_exc_unexpected_error THEN
978 ROLLBACK TO COPY_WORK_CLEARANCE;
979 x_return_status := FND_API.G_RET_STS_ERROR;
980 x_msg_count := EAM_ERROR_MESSAGE_PVT.Get_Message_Count;
981 WHEN OTHERS THEN
982 ROLLBACK TO COPY_WORK_CLEARANCE;
983 x_return_status := FND_API.G_RET_STS_ERROR;
984
985 END COPY_WORK_CLEARANCE;
986
987
988
989 END EAM_PROCESS_CLEARANCE_PVT;