[Home] [Help]
PACKAGE BODY: APPS.EAM_PROCESS_PERMIT_PVT
Source
1 PACKAGE BODY EAM_PROCESS_PERMIT_PVT AS
2 /* $Header: EAMVWPTB.pls 120.3 2011/03/25 11:34:37 vboddapa noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2009 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME: EAMVWPTB.pls
9 --
10 -- DESCRIPTION: Body of package EAM_PROCESS_PERMIT_PVT
11 --
12 -- NOTES
13 --
14 -- HISTORY
15 --
16 -- 25-JAN-2009 Madhuri Shah Initial Creation
17 ***************************************************************************/
18
19 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_PROCESS_PERMIT_PVT';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EAMVWPTB.pls';
21 G_DEBUG_FILENAME CONSTANT VARCHAR2(50) := 'EAM_SAFETY_DEBUG.log';
22
23
24 /*********************************************************
25 * Procedure : Validate_Transaction_Type
26 * Purpose : This procedure will check if the transaction type is valid
27 for a particular entity.
28 *********************************************************/
29
30 PROCEDURE VALIDATE_TRANSACTION_TYPE
31 (
32 p_validation_level IN NUMBER
33 , p_entity IN VARCHAR2
34 , x_return_status OUT NOCOPY VARCHAR2
35 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
36 )IS
37
38 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type ;
39 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
40 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type ;
41 l_other_message VARCHAR2(2000);
42 l_err_text VARCHAR2(2000);
43
44
45 BEGIN
46
47 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_PROCESS_PERMIT_PVT.VALIDATE_TRANSACTION_TYPE'); end if;
48
49 IF (p_entity = 'WORK PERMIT') THEN
50 IF (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_CREATE)
51 OR (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_UPDATE) then
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53
54 ELSE
55 x_return_status := FND_API.G_RET_STS_ERROR;
56 END IF;
57
58 END IF;
59
60 END VALIDATE_TRANSACTION_TYPE;
61
62
63
64 FUNCTION IS_WORKFLOW_ENABLED
65 (p_maint_obj_source IN NUMBER,
66 p_organization_id IN NUMBER
67 ) RETURN VARCHAR2
68 IS
69 l_workflow_enabled VARCHAR2(1);
70 BEGIN
71
72 /* BEGIN
73 SELECT enable_workflow
74 INTO l_workflow_enabled
75 FROM EAM_ENABLE_WORKFLOW
76 WHERE MAINTENANCE_OBJECT_SOURCE =p_maint_obj_source;
77 EXCEPTION
78 WHEN NO_DATA_FOUND THEN
79 l_workflow_enabled := 'N';
80 END; */
81
82 --IF EAM workorder,check if workflow is enabled for this organization or not
83 -- IF(l_workflow_enabled ='Y' AND p_maint_obj_source=1) THEN
84 BEGIN
85 SELECT EAM_SAFETY_WORKFLOW_ENABLED
86 INTO l_workflow_enabled
87 FROM WIP_EAM_PARAMETERS
88 WHERE organization_id =p_organization_id;
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 l_workflow_enabled := 'N';
92 END;
93
94
95 RETURN l_workflow_enabled;
96
97 -- END IF; --check for workflow enabled at org level
98
99 -- RETURN l_workflow_enabled;
100
101 END IS_WORKFLOW_ENABLED;
102
103 /************************************************************
104 * Procedure : PROCESS_WORK_PERMIT
105 * Purpose : This will process create/update/delete on work permit
106 ************************************************************/
107
108
109
110
111 PROCEDURE PROCESS_WORK_PERMIT
112 ( p_bo_identifier IN VARCHAR2 := 'EAM'
113 , p_api_version_number IN NUMBER := 1.0
114 , p_init_msg_list IN BOOLEAN := FALSE
115 , p_commit IN VARCHAR2
116 , p_work_permit_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type
117 , p_permit_wo_association_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type
118 , p_permit_clearance_asso_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type
119 , x_work_permit_header_rec OUT NOCOPY EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type
120 , x_return_status OUT NOCOPY VARCHAR2
121 , x_msg_count OUT NOCOPY NUMBER
122 , p_debug IN VARCHAR2
123 , p_output_dir IN VARCHAR2
124 , p_debug_filename IN VARCHAR2
125 , p_debug_file_mode IN VARCHAR2
126 )IS
127
128 l_api_name CONSTANT VARCHAR2(30) := G_PKG_NAME;
129 l_validation_level NUMBER :=p_work_permit_header_rec.transaction_type;
130
131 l_return_status VARCHAR2(1);
132 l_msg_count NUMBER;
133 l_msg_data VARCHAR2(240);
134 l_permit_wo_association_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
135 l_permit_clearance_asso_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
136 l_safety_assoc_rec EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type;
137 l_permit_id NUMBER;
138
139 /* Error Handling Variables */
140 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type ;
141 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
142 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type ;
143 l_other_message VARCHAR2(2000);
144 l_err_text VARCHAR2(2000);
145 l_error_level NUMBER :=EAM_ERROR_MESSAGE_PVT.G_BO_LEVEL;
146 l_entity_index NUMBER :=1;
147
148
149 BEGIN
150
151 SAVEPOINT PROCESS_PERMIT;
152
153 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_PROCESS_PERMIT_PVT.PROCESS_WORK_PERMIT'); end if;
154
155 -- 1) Work_Permit for processing of Work Permit header record
156
157 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling WORK_PERMIT from PROCESS_WORK_PERMIT'); end if;
158
159 WORK_PERMIT
160 (
161 p_validation_level => l_validation_level
162 , p_work_permit_id => p_work_permit_header_rec.permit_id
163 , p_organization_id => p_work_permit_header_rec.organization_id
164 , p_work_permit_header_rec => p_work_permit_header_rec
165 , x_work_permit_header_rec => x_work_permit_header_rec
166 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
167 , x_return_status => l_return_status
168 );
169 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
170 THEN
171 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
172 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
173 , p_error_level => l_error_level
174 , p_entity_index => l_entity_index
175 );
176 raise fnd_api.g_exc_unexpected_error;
177 END IF;
178
179 l_permit_id :=x_work_permit_header_rec.permit_id;
180
181 IF p_permit_clearance_asso_tbl.COUNT > 0 THEN
182 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling CLEARANCE_PERMIT_ASSOCIATION from PROCESS_WORK_PERMIT'); end if;
183
184
185 FOR i in p_permit_clearance_asso_tbl.FIRST..p_permit_clearance_asso_tbl.LAST LOOP
186
187 l_safety_assoc_rec := p_permit_clearance_asso_tbl(i);
188
189 if l_safety_assoc_rec.source_id is null then
190 l_safety_assoc_rec.source_id := l_permit_id;
191 end if;
192
193 l_permit_clearance_asso_tbl(i) := l_safety_assoc_rec;
194 END LOOP;
195
196 EAM_SAFETY_ASSOCIATION_PVT.PERMIT_CLEARANCE_ASSOCIATION
197 (
198 p_permit_clearance_asso_tbl => l_permit_clearance_asso_tbl
199 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
200 , x_return_status => l_return_status
201 );
202 END IF;
203
204
205 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
206 THEN
207 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
208 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
209 , p_error_level => l_error_level
210 , p_entity_index => l_entity_index
211 );
212 raise fnd_api.g_exc_unexpected_error;
213 END IF;
214
215 IF p_permit_wo_association_tbl.COUNT > 0 THEN
216 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling PERMIT_WORK_ORDER_ASSOCIATION from PROCESS_WORK_PERMIT'); end if;
217 FOR i in p_permit_wo_association_tbl.FIRST..p_permit_wo_association_tbl.LAST LOOP
218
219 l_safety_assoc_rec := p_permit_wo_association_tbl(i);
220
221 if l_safety_assoc_rec.source_id is null then
222 l_safety_assoc_rec.source_id := l_permit_id;
223 end if;
224
225 l_permit_wo_association_tbl(i) := l_safety_assoc_rec;
226 END LOOP;
227 EAM_SAFETY_ASSOCIATION_PVT.PERMIT_WORK_ORDER_ASSOCIATION
228 (
229 p_permit_wo_association_tbl => l_permit_wo_association_tbl
230 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
231 , x_return_status => l_return_status
232 );
233
234 END IF;
235
236
237 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
238 THEN
239 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
240 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
241 , p_error_level => l_error_level
242 , p_entity_index => l_entity_index
243 );
244 raise fnd_api.g_exc_unexpected_error;
245 END IF;
246
247 x_return_status :=l_return_status;
248
249 IF (p_commit = FND_API.G_TRUE) THEN
250 COMMIT;
251 END IF;
252
253
254 EXCEPTION
255
256 WHEN fnd_api.g_exc_unexpected_error THEN
257 ROLLBACK TO PROCESS_PERMIT;
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 x_msg_count := EAM_ERROR_MESSAGE_PVT.Get_Message_Count;
260
261 WHEN OTHERS THEN
262 ROLLBACK TO PROCESS_PERMIT;
263 x_return_status := FND_API.G_RET_STS_ERROR;
264
265
266 END PROCESS_WORK_PERMIT;
267
268
269
270 /************************************************************
271 * Procedure : WORK_PERMIT
272 * Purpose : This will process work permit header
273 ************************************************************/
274 PROCEDURE WORK_PERMIT
275 (
276 p_validation_level IN NUMBER
277 , p_work_permit_id IN NUMBER :=NULL
278 , p_organization_id IN NUMBER :=NULL
279 , p_work_permit_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type
280 , x_work_permit_header_rec OUT NOCOPY EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type
281 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.MESG_TOKEN_TBL_TYPE
282 , x_return_status OUT NOCOPY VARCHAR2
283 )IS
284
285 CURSOR C IS SELECT EAM_WORK_PERMIT_S.NEXTVAL FROM SYS.DUAL;
286
287 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
288 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
289 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
290 l_return_status VARCHAR2(1);
291
292 l_permit_id NUMBER := p_work_permit_header_rec.PERMIT_ID;
293 l_workflow_enabled VARCHAR2(1);
294
295 l_work_permit_header_rec EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type :=p_work_permit_header_rec;
296 l_old_work_permit_header_rec EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type;
297 l_out_work_permit_header_rec EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type;
298
299
300 l_maintenance_object_source NUMBER :=1; -- To check if work flow is enabled
301 l_status_pending_event VARCHAR2(100) := 'oracle.apps.eam.permit.status.change.pending';
302 l_approval_required BOOLEAN := FALSE; --set the flag to 'false' initially
303 l_pending_workflow_name VARCHAR2(100);
304 l_pending_workflow_process VARCHAR2(200) ;
305 l_new_system_status NUMBER;
306
307 l_permit_write_error EXCEPTION;
308 l_permit_transaction_error EXCEPTION;
309 l_permit_existence_error EXCEPTION;
310 l_permit_required_error EXCEPTION;
311 l_permit_attributes_error EXCEPTION;
315
312 l_err_status_change EXCEPTION;
313
314 BEGIN
316 SAVEPOINT WORK_PERMIT;
317
318 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_PROCESS_PERMIT_PVT.WORK_PERMIT'); end if;
319
320 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling VALIDATE_TRANSACTION_TYPE'); end if;
321
322 EAM_PROCESS_PERMIT_PVT.VALIDATE_TRANSACTION_TYPE
323 ( p_validation_level => p_validation_level
324 , p_entity => 'WORK PERMIT'
325 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
326 , x_return_Status => l_return_status
327 );
328
329 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
330 THEN
331 RAISE l_permit_transaction_error;
332 END IF;
333
334 IF (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_CREATE) THEN
335 IF (l_permit_id IS NULL) OR (l_permit_id = FND_API.G_MISS_NUM) THEN
336 OPEN C;
337 FETCH C INTO l_permit_id;
338 CLOSE C;
339 l_work_permit_header_rec.permit_id :=l_permit_id;
340 END IF;
341 END IF;
342
343 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Check_Existence'); end if;
344
345 EAM_PERMIT_VALIDATE_PVT.Check_Existence
346 ( p_work_permit_header_rec => l_work_permit_header_rec
347 , x_work_permit_header_rec => l_old_work_permit_header_rec
348 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
349 , x_return_Status => l_return_status
350 );
351
352
353 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
354 THEN
355 RAISE l_permit_existence_error;
356 END IF;
357
358
359 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Populate_NULL_Columns'); end if;
360 IF (p_validation_level = EAM_PROCESS_WO_PVT.G_OPR_UPDATE) THEN
361
362 EAM_PERMIT_DEFAULT_PVT.Populate_NULL_Columns
363 ( p_eam_wp_rec => l_work_permit_header_rec
364 , p_old_eam_wp_rec => l_old_work_permit_header_rec
365 , x_eam_wp_rec => l_out_work_permit_header_rec
366 );
367
368 l_work_permit_header_rec := l_out_work_permit_header_rec;
369
370
371 END IF;
372
373 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Check_required'); end if;
374
375
376 EAM_PERMIT_VALIDATE_PVT.CHECK_REQUIRED
377 ( p_work_permit_header_rec => l_work_permit_header_rec
378 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
379 , x_return_Status => l_return_status
380 );
381
382 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
383 THEN
384 RAISE l_permit_required_error;
385 END IF;
386
387
388 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Check_attributes'); end if;
389
390
391 EAM_PERMIT_VALIDATE_PVT.CHECK_ATTRIBUTES
392 ( p_work_permit_header_rec => l_work_permit_header_rec
393 , p_old_work_permit_header_rec => l_old_work_permit_header_rec
394 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
395 , x_return_Status => l_return_status
396 );
397
398 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
399 THEN
400 RAISE l_permit_attributes_error;
401 END IF;
402
403 --Check if work flow is enabled and work permit requires an approval, set the pending flag to ?Y? in the work permit record.
404 l_workflow_enabled := Is_Workflow_Enabled(l_maintenance_object_source, p_organization_id);
405
406 IF(l_workflow_enabled = 'Y') THEN --if workflow is enabled
407 IF((l_work_permit_header_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_CREATE ) --created
408 OR (l_work_permit_header_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE --workorder updated
409 AND NVL(l_old_work_permit_header_rec.pending_flag,'N') = 'N' --old status is not pending
410 --and old status is not same as new status
411 AND (l_old_work_permit_header_rec.status_type <>l_work_permit_header_rec.status_type)
412 )
413 ) THEN
414
415 IF(WF_EVENT.TEST(l_status_pending_event) <> 'NONE') THEN
416
417 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Calling Wkflow required check API...') ; END IF;
418
419
420 EAM_WORKPERMIT_WORKFLOW_PVT.Is_Approval_Required_Released
421 (p_old_wp_rec => l_old_work_permit_header_rec,
422 p_new_wp_rec => l_work_permit_header_rec,
423 x_approval_required => l_approval_required,
424 x_workflow_name => l_pending_workflow_name,
425 x_workflow_process => l_pending_workflow_process
426 );
427
428 IF(l_approval_required) THEN
429 l_work_permit_header_rec.pending_flag:='Y'; --if approval required set the pending flag and system status to previous status
430 l_new_system_status := l_work_permit_header_rec.status_type;
431 l_work_permit_header_rec.status_type := NVL(l_old_work_permit_header_rec.status_type,17);
432
433 END IF;
434 END IF; --end of check for status event enabled
435
436 END IF;
437
438
439 END IF; --end of check for workflow enabled
440
441 -- Put permit completion details if status is changed to complete
442
446 RAISE l_err_status_change;
443 IF (l_work_permit_header_rec.status_type in (wip_constants.comp_chrg,wip_constants.comp_nochrg)) THEN
444
445 IF (l_old_work_permit_header_rec.status_type = wip_constants.draft) THEN
447 ELSE
448 l_work_permit_header_rec.COMPLETION_DATE := NVL(l_work_permit_header_rec.COMPLETION_DATE,sysdate);
449 END IF;
450 END IF;
451
452
453 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Perform_Writes'); end if;
454
455 EAM_PERMIT_UTILITY_PVT.PERFORM_WRITES
456 ( p_work_permit_header_rec => l_work_permit_header_rec
457 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
458 , x_return_Status => l_return_status
459 );
460 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
461 THEN
462 RAISE l_permit_write_error;
463 END IF;
464
465 x_work_permit_header_rec :=l_work_permit_header_rec;
466
467 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Created Permit with permit id: '||p_work_permit_header_rec.permit_id); end if;
468
469 --If work permit is approved and no workflow then call the procedure CHANGE_WORK_PERMIT_STATUS
470 IF(l_workflow_enabled = 'N')
471 OR (l_work_permit_header_rec.APPROVED_BY IS NOT NULL)
472 OR (l_work_permit_header_rec.APPROVED_BY <> FND_API.G_MISS_NUM) THEN
473
474 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling CHANGE_WORK_PERMIT_STATUS') ; END IF ;
475
476 EAM_PERMIT_UTILITY_PVT.CHANGE_WORK_PERMIT_STATUS
477 ( p_permit_id => l_work_permit_header_rec.permit_id
478 , p_organization_id => l_work_permit_header_rec.organization_id
479 , p_to_status_type => l_work_permit_header_rec.status_type
480 , p_user_id => l_work_permit_header_rec.user_id
481 , p_responsibility_id => l_work_permit_header_rec.responsibility_id
482 , p_transaction_type => l_work_permit_header_rec.transaction_type
483 , x_return_status => l_return_status
484 , x_Mesg_Token_Tbl => l_mesg_token_tbl
485 );
486
487 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Status Change WO completed with status ' || l_return_status) ; END IF ;
488 IF NVL(l_return_status, 'S') <> 'S' THEN
489 l_return_status := FND_API.G_RET_STS_ERROR;
490 RAISE l_err_status_change;
491
492 END IF;
493
494 END IF;
495
496 --IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling INSERT_PERMIT_HISTORY_ROW'); end if;
497 --Call the procedure EAM_PERMIT_UTILITY_PVT.INSERT_PERMIT_HISTORY_ROW to insert the event details in the EAM_SAFETY_HISTORY table
498
499 --If workflow is enabled and workflow approval is required call the procedure RAISE_WORKFLOW_EVENTS.
500
501 IF(l_workflow_enabled = 'Y')
502 AND (l_approval_required) THEN
503 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling the Raise_Workflow_Events procedure ...') ; END IF ;
504 Raise_Workflow_Events
505 ( p_api_version => 1.0,
506 p_validation_level => p_validation_level,
507 p_eam_wp_rec => l_work_permit_header_rec,
508 p_old_eam_wp_rec => l_old_work_permit_header_rec,
509 p_approval_required => l_approval_required,
510 p_new_system_status => l_new_system_status,
511 p_workflow_name => l_pending_workflow_name,
512 p_workflow_process => l_pending_workflow_process,
513 x_return_status => l_return_status,
514 x_mesg_token_tbl => l_mesg_token_tbl
515 );
516
517 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 ;
518
519 END IF;
520
521 x_return_status :=l_return_status;
522
523
524 EXCEPTION
525
526 WHEN l_permit_write_error then
527 l_token_tbl(1).token_name := 'PERMIT_NAME';
528 l_token_tbl(1).token_value := p_work_permit_header_rec.PERMIT_NAME;
529
530 l_out_mesg_token_tbl := l_mesg_token_tbl;
531 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
532 ( p_message_name => 'EAM_PERMIT_WRITE_ERROR'
533 , p_token_tbl => l_token_tbl
534 , p_mesg_token_tbl => l_mesg_token_tbl
535 , x_mesg_token_tbl => l_out_mesg_token_tbl
536 );
537 l_mesg_token_tbl := l_out_mesg_token_tbl;
538
539 x_return_status := FND_API.G_RET_STS_ERROR;
540 x_mesg_token_tbl := l_mesg_token_tbl ;
541 ROLLBACK TO WORK_PERMIT;
542
543 WHEN l_permit_transaction_error then
544 l_token_tbl(1).token_name := 'PERMIT_NAME';
545 l_token_tbl(1).token_value := p_work_permit_header_rec.PERMIT_NAME;
546
547 l_out_mesg_token_tbl := l_mesg_token_tbl;
548 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
549 ( p_message_name => 'EAM_PERMIT_TRANSACTION_ERROR'
550 , p_token_tbl => l_token_tbl
551 , p_mesg_token_tbl => l_mesg_token_tbl
552 , x_mesg_token_tbl => l_out_mesg_token_tbl
553 );
557 x_mesg_token_tbl := l_mesg_token_tbl ;
554 l_mesg_token_tbl := l_out_mesg_token_tbl;
555
556 x_return_status := FND_API.G_RET_STS_ERROR;
558 ROLLBACK TO WORK_PERMIT;
559
560 WHEN l_permit_existence_error then
561 l_token_tbl(1).token_name := 'PERMIT_NAME';
562 l_token_tbl(1).token_value := p_work_permit_header_rec.PERMIT_NAME;
563
564 l_out_mesg_token_tbl := l_mesg_token_tbl;
565 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
566 ( p_message_name => 'EAM_PERMIT_EXISTENCE_ERROR'
567 , p_token_tbl => l_token_tbl
568 , p_mesg_token_tbl => l_mesg_token_tbl
569 , x_mesg_token_tbl => l_out_mesg_token_tbl
570 );
571 l_mesg_token_tbl := l_out_mesg_token_tbl;
572
573 x_return_status := FND_API.G_RET_STS_ERROR;
574 x_mesg_token_tbl := l_mesg_token_tbl ;
575 ROLLBACK TO WORK_PERMIT;
576 WHEN l_permit_required_error then
577 l_token_tbl(1).token_name := 'PERMIT_NAME';
578 l_token_tbl(1).token_value := p_work_permit_header_rec.PERMIT_NAME;
579
580 l_out_mesg_token_tbl := l_mesg_token_tbl;
581 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
582 ( p_message_name => 'EAM_PERMIT_REQUIRED_ERROR'
583 , p_token_tbl => l_token_tbl
584 , p_mesg_token_tbl => l_mesg_token_tbl
585 , x_mesg_token_tbl => l_out_mesg_token_tbl
586 );
587 l_mesg_token_tbl := l_out_mesg_token_tbl;
588
589 x_return_status := FND_API.G_RET_STS_ERROR;
590 x_mesg_token_tbl := l_mesg_token_tbl ;
591 ROLLBACK TO WORK_PERMIT;
592 WHEN l_permit_attributes_error then
593 l_token_tbl(1).token_name := 'PERMIT_NAME';
594 l_token_tbl(1).token_value := p_work_permit_header_rec.PERMIT_NAME;
595
596 l_out_mesg_token_tbl := l_mesg_token_tbl;
597 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
598 ( p_message_name => 'EAM_PERMIT_ATTRIBUTE_ERROR'
599 , p_token_tbl => l_token_tbl
600 , p_mesg_token_tbl => l_mesg_token_tbl
601 , x_mesg_token_tbl => l_out_mesg_token_tbl
602 );
603
604 l_mesg_token_tbl := l_out_mesg_token_tbl;
605 x_return_status := FND_API.G_RET_STS_ERROR;
606 x_mesg_token_tbl := l_mesg_token_tbl ;
607 ROLLBACK TO WORK_PERMIT;
608 WHEN l_err_status_change then
609 l_token_tbl(1).token_name := 'PERMIT_NAME';
610 l_token_tbl(1).token_value := p_work_permit_header_rec.PERMIT_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_PERMIT_STATUS_CNG_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 x_return_status := FND_API.G_RET_STS_ERROR;
621 x_mesg_token_tbl := l_mesg_token_tbl ;
622 ROLLBACK TO WORK_PERMIT;
623
624 WHEN OTHERS THEN
625 l_token_tbl(1).token_name := 'PERMIT_NAME';
626 l_token_tbl(1).token_value := p_work_permit_header_rec.PERMIT_NAME;
627 l_out_mesg_token_tbl := l_mesg_token_tbl;
628 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
629 ( p_message_name => 'EAM_PERMIT_UNEXPECTED_ERROR'
630 , p_token_tbl => l_token_tbl
631 , p_mesg_token_tbl => l_mesg_token_tbl
632 , x_mesg_token_tbl => l_out_mesg_token_tbl
633 );
634 l_mesg_token_tbl := l_out_mesg_token_tbl;
635
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 x_mesg_token_tbl := l_mesg_token_tbl ;
638 ROLLBACK TO WORK_PERMIT;
639
640
641 END WORK_PERMIT;
642
643 /************************************************************
644 * Procedure: PERMIT_WORK_ORDER_ASSOCIATION
645 * Purpose : This will process permit work order association
646 ************************************************************/
647
648 /*PROCEDURE PERMIT_WORK_ORDER_ASSOCIATION
649 ( p_validation_level IN NUMBER
650 , p_organization_id IN NUMBER
651 , p_permit_wo_association_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type
652 , p_work_permit_id IN NUMBER
653 , x_permit_wo_association_tbl OUT NOCOPY EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type
654 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.MESG_TOKEN_TBL_TYPE
655 , x_return_status OUT NOCOPY VARCHAR2
656 )IS
657 CURSOR C IS SELECT EAM_SAFETY_ASSOCIATIONS_S.NEXTVAL FROM SYS.DUAL;
658 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
659 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
660 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
661 l_return_status VARCHAR2(1);
662 l_safety_assoc_rec EAM_PROCESS_SAFETY_PUB.eam_wp_association_rec_type;
663 l_permit_status NUMBER;
664 l_wo_status NUMBER;
665 l_safety_assoc_id NUMBER;
666
667 l_wp_association_error EXCEPTION;
668
669
670
671 BEGIN
672
673 SAVEPOINT PERMIT_WORK_ORDER_ASSOCIATION;
674 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Inside EAM_PROCESS_PERMIT_PVT.PERMIT_WORK_ORDER_ASSOCIATION'); end if;
675
676
677 /*For each record in the table
678 -- Check if the parent work permit, work order exists.
679
680 IF p_permit_wo_association_tbl.COUNT > 0 THEN
684 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating status_type . . . '); END IF;
681 FOR i in p_permit_wo_association_tbl.FIRST..p_permit_wo_association_tbl.LAST LOOP
682
683 /* -- status_type
685
686 begin
687
688 select status_type
689 into l_wo_status
690 from wip_discrete_jobs
691 where wip_entity_id = p_permit_wo_association_tbl(i).TARGET_REF_ID
692 and organization_id =p_organization_id;
693
694 select status_type
695 into l_permit_status
696 from EAM_WORK_PERMITS
697 where permit_id =p_work_permit_id
698 and organization_id =p_organization_id;
699
700
701 if (p_permit_wo_association_tbl(i).transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE and
702 (l_wo_status not in (wip_constants.released, wip_constants.draft) or
703 l_permit_status not in (wip_constants.released, wip_constants.draft))) then
704
705 raise fnd_api.g_exc_unexpected_error;
706
707 elsif (p_permit_wo_association_tbl(i).transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE and
708 (l_wo_status not in (wip_constants.released, wip_constants.draft) or
709 l_permit_status not in (wip_constants.released, wip_constants.draft))) then
710
711 raise fnd_api.g_exc_unexpected_error;
712
713 end if;
714
715 l_return_status := FND_API.G_RET_STS_SUCCESS;
716
717
718
719 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
720 l_safety_assoc_rec := p_permit_wo_association_tbl(i);
721 if l_safety_assoc_rec.source_id is null then
722 l_safety_assoc_rec.source_id := p_work_permit_id;
723 end if;
724 EAM_PROCESS_PERMIT_PVT.VALIDATE_TRANSACTION_TYPE
725 ( p_validation_level => l_safety_assoc_rec.TRANSACTION_TYPE
726 , p_entity => 'ASSOCIATION'
727 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
728 , x_return_Status => l_return_status
729 );
730 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling Perform_Writes'); end if;
731 IF l_safety_assoc_rec.TRANSACTION_TYPE = EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
732 OPEN C;
733 FETCH C INTO l_safety_assoc_id;
734 CLOSE C;
735 l_safety_assoc_rec.SAFETY_ASSOCIATION_ID :=l_safety_assoc_id;
736 END IF;
737 -- Note: put i instead of 1.
738 EAM_SAFETY_UTILITY_PVT.WRITE_SAFFETY_ASSOCIATION_ROW
739 ( p_safety_association_rec => l_safety_assoc_rec
740 , p_association_type =>p_permit_wo_association_tbl(1).association_type
741 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
742 , x_return_Status => l_return_status
743 );
744 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
745 THEN
746 RAISE l_wp_association_error;
747 END IF;
748 -- END IF;
749
750 /* exception
751
752 when fnd_api.g_exc_unexpected_error then
753 ROLLBACK TO PERMIT_WORK_ORDER_ASSOCIATION;
754 --l_token_tbl(1).token_name := 'Status type';
755 --l_token_tbl(1).token_value := p_eam_wo_rec.status_type;
756
757 l_out_mesg_token_tbl := l_mesg_token_tbl;
758 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
759 ( p_message_name => 'EAM_PERMIT_WO_STATUS_TYPE'
760 , p_token_tbl => l_token_tbl
761 , p_mesg_token_tbl => l_mesg_token_tbl
762 , x_mesg_token_tbl => l_out_mesg_token_tbl
763 );
764 l_mesg_token_tbl := l_out_mesg_token_tbl;
765
766 x_return_status := FND_API.G_RET_STS_ERROR;
767 x_mesg_token_tbl := l_mesg_token_tbl ;
768 return;
769 WHEN l_wp_association_error then
770 ROLLBACK TO PERMIT_WORK_ORDER_ASSOCIATION;
771 -- l_token_tbl(1).token_name := 'Object Type';
772 -- l_token_tbl(1).token_value := p_eam_wo_rec.maintenance_object_type;
773
774 l_out_mesg_token_tbl := l_mesg_token_tbl;
775 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
776 ( p_message_name => 'EAM_PERMIT_ASSO_ERROR'
777 , p_token_tbl => l_token_tbl
778 , p_mesg_token_tbl => l_mesg_token_tbl
779 , x_mesg_token_tbl => l_out_mesg_token_tbl
780 );
781 l_mesg_token_tbl := l_out_mesg_token_tbl;
782
783 x_return_status := FND_API.G_RET_STS_ERROR;
784 x_mesg_token_tbl := l_mesg_token_tbl ;
785
786 return;
787 when no_data_found then
788 ROLLBACK TO PERMIT_WORK_ORDER_ASSOCIATION;
789 -- l_token_tbl(1).token_name := 'Organization Id';
790 -- l_token_tbl(1).token_value := p_eam_wo_rec.organization_id;
791
792 l_out_mesg_token_tbl := l_mesg_token_tbl;
793 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
794 ( p_message_name => 'EAM_PERMIT_WO_NOT_EXIST'
795 , p_token_tbl => l_token_tbl
796 , p_mesg_token_tbl => l_mesg_token_tbl
797 , x_mesg_token_tbl => l_out_mesg_token_tbl
798 );
799 l_mesg_token_tbl := l_out_mesg_token_tbl;
800
801 x_return_status := FND_API.G_RET_STS_ERROR;
802 x_mesg_token_tbl := l_mesg_token_tbl ;
803 return;
804
805 end;*/
809 x_return_status := l_return_status;
806 /* END LOOP;
807 END IF;
808
810
811 EXCEPTION
812
813
814 WHEN OTHERS THEN
815 /* l_token_tbl(1).token_name := 'WORKORDER';
816 l_token_tbl(1).token_value := l_eam_wo_rec.wip_entity_id;
817
818 l_out_mesg_token_tbl := l_mesg_token_tbl;
819 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
820 ( x_Mesg_token_tbl => l_out_Mesg_Token_Tbl
821 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
822 , p_message_name => 'EAM_POPULATE_REL_ERR'
823 , p_token_tbl => l_token_tbl
824 );
825 l_mesg_token_tbl := l_out_mesg_token_tbl;*/
826 /* ROLLBACK TO PERMIT_WORK_ORDER_ASSOCIATION;
827 x_return_status := FND_API.G_RET_STS_ERROR;
828
829 END PERMIT_WORK_ORDER_ASSOCIATION;*/
830
831
832 /********************************************************************
833 * Procedure: Raise_Workflow_Events
834 * Purpose: This procedure raises the workflow events for work permit release
835 *********************************************************************/
836
837
838 PROCEDURE RAISE_WORKFLOW_EVENTS
839 ( p_api_version IN NUMBER
840 , p_validation_level IN NUMBER
841 , p_eam_wp_rec IN EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type
842 , p_old_eam_wp_rec IN EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type
843 , p_approval_required IN BOOLEAN
844 , p_new_system_status IN NUMBER
845 , p_workflow_name IN VARCHAR2
846 , p_workflow_process IN VARCHAR2
847 , x_mesg_token_tbl IN OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
848 , x_return_status IN OUT NOCOPY VARCHAR2
849 ) IS
850
851 l_status_pending_event VARCHAR2(240);
852 l_event_name VARCHAR2(240);
853 l_parameter_list wf_parameter_list_t;
854 l_event_key VARCHAR2(200);
855 l_wf_event_seq NUMBER;
856 l_return_status VARCHAR2(1);
857 l_err_text VARCHAR2(2000);
858 l_msg_count NUMBER;
859 BEGIN
860
861
862 l_status_pending_event := 'oracle.apps.eam.permit.status.change.pending';
863
864
865 --if status change needs approval
866 IF( p_approval_required AND (WF_EVENT.TEST(l_status_pending_event) <> 'NONE')) THEN
867
868 SELECT EAM_SAFETYWORKFLOW_EVENT_S.NEXTVAL
869 INTO l_wf_event_seq
870 FROM DUAL;
871
872 l_parameter_list := wf_parameter_list_t();
873 l_event_name := l_status_pending_event;
874
875 l_event_key := TO_CHAR(l_wf_event_seq);
876 INSERT INTO EAM_SAFETY_WORKFLOWS
877 (OBJECT_ID,TRANSACTION_ID,WORKFLOW_TYPE,LAST_UPDATE_DATE,LAST_UPDATED_BY,
878 CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
879 VALUES
880 (p_eam_wp_rec.permit_id,l_wf_event_seq,3,SYSDATE,FND_GLOBAL.user_id,
881 SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id
882 );
883
884
885 WF_CORE.CONTEXT('Enterprise Asset Management...','Work Permit Released change event','Building parameter list');
886 -- Add Parameters
887 Wf_Event.AddParameterToList(p_name =>'PERMIT_ID',
888 p_value => TO_CHAR(p_eam_wp_rec.permit_id),
889 p_parameterlist => l_parameter_list);
890 Wf_Event.AddParameterToList(p_name =>'PERMIT_NAME',
891 p_value =>p_eam_wp_rec.permit_name,
892 p_parameterlist => l_parameter_list);
893 Wf_Event.AddParameterToList(p_name =>'ORGANIZATION_ID',
894 p_value => TO_CHAR(p_eam_wp_rec.organization_id),
895 p_parameterlist => l_parameter_list);
896 Wf_Event.AddParameterToList(p_name =>'NEW_PERMIT_STATUS',
897 p_value => TO_CHAR(p_eam_wp_rec.user_defined_status_id),
898 p_parameterlist => l_parameter_list);
899 Wf_Event.AddParameterToList(p_name =>'OLD_SYSTEM_STATUS',
900 p_value => TO_CHAR(p_eam_wp_rec.STATUS_TYPE),
901 p_parameterlist => l_parameter_list);
902 Wf_Event.AddParameterToList(p_name =>'OLD_PERMIT_STATUS',
903 p_value => TO_CHAR(p_old_eam_wp_rec.user_defined_status_id),
904 p_parameterlist => l_parameter_list);
905 Wf_Event.AddParameterToList(p_name =>'NEW_SYSTEM_STATUS',
906 p_value => TO_CHAR(p_new_system_status),
907 p_parameterlist => l_parameter_list);
908 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_TYPE',
909 p_value => TO_CHAR(3),
910 p_parameterlist => l_parameter_list);
911 Wf_Event.AddParameterToList(p_name =>'REQUESTOR',
912 p_value =>FND_GLOBAL.USER_NAME ,
913 p_parameterlist => l_parameter_list);
914 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_NAME',
915 p_value => p_workflow_name,
916 p_parameterlist => l_parameter_list);
917 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_PROCESS',
918 p_value => p_workflow_process,
919 p_parameterlist => l_parameter_list);
923 p_event_key => l_event_key,
920 Wf_Core.Context('Enterprise Asset Management...','Work Permit Released Event','Raising event');
921
922 Wf_Event.Raise( p_event_name => l_event_name,
924 p_parameters => l_parameter_list);
925 l_parameter_list.DELETE;
926 WF_CORE.CONTEXT('Enterprise Asset Management...','Work Permit Released Event','After raising event');
927
928 END IF; --end of check for status change pending event
929
930 EXCEPTION
931 WHEN OTHERS THEN
932 WF_CORE.CONTEXT('Enterprise Asset Management...',l_event_name,'Exception during event construction and raise: ' || SQLERRM);
933 x_return_status := FND_API.G_RET_STS_ERROR;
934
935 END RAISE_WORKFLOW_EVENTS;
936
937
938 /**************************************************************************
939 * Procedure: COPY_WORK_PERMIT
940 * Purpose: Procedure to copy work permit record.
941 * This procedure will be called from the public API
942 ***************************************************************************/
943
944 PROCEDURE COPY_WORK_PERMIT(
945 p_bo_identifier IN VARCHAR2 := 'EAM'
946 , p_api_version_number IN NUMBER := 1.0
947 , p_init_msg_list IN BOOLEAN := FALSE
948 , p_commit IN VARCHAR2
949 , p_debug IN VARCHAR2
950 , p_output_dir IN VARCHAR2
951 , p_debug_filename IN VARCHAR2
952 , p_debug_file_mode IN VARCHAR2
953 , p_org_id IN NUMBER
954 , px_permit_id IN OUT NOCOPY NUMBER
955 , x_return_status OUT NOCOPY VARCHAR2
956 , x_msg_count OUT NOCOPY NUMBER
957
958 )IS
959 CURSOR C IS SELECT EAM_PERMIT_NAME_S.NEXTVAL FROM SYS.DUAL;
960 l_api_name CONSTANT VARCHAR2(30) := G_PKG_NAME;
961 l_validation_level NUMBER := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
962
963 l_return_status VARCHAR2(1);
964 l_msg_count NUMBER;
965 l_permit_id NUMBER:=px_permit_id;
966 l_org_id NUMBER :=p_org_id;
967 -- l_permit_name_char VARCHAR2(240);
968 l_permit_name NUMBER;
969
970 l_work_permit_header_rec EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type;
971 l_out_work_permit_header_rec EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type;
972
973 /* Error Handling Variables */
974 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type ;
975 l_error_level NUMBER :=EAM_ERROR_MESSAGE_PVT.G_BO_LEVEL;
976 l_entity_index NUMBER :=1;
977
978 BEGIN
979
980 SAVEPOINT COPY_PERMIT;
981 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Start of copy permit '); end if;
982 EAM_PERMIT_UTILITY_PVT.QUERY_ROW
983 ( p_work_permit_id => l_permit_id
984 , p_organization_id => l_org_id
985 , x_work_permit_header_rec => l_work_permit_header_rec
986 , x_return_Status => l_return_status
987 );
988
989 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
990 THEN
991 RAISE fnd_api.g_exc_unexpected_error;
992 END IF;
993 /*Get the next permit name sequence*/
994 OPEN C;
995 FETCH C INTO l_permit_name;
996 CLOSE C;
997
998 l_work_permit_header_rec.permit_id :=null;
999 l_work_permit_header_rec.permit_name :=to_char(l_permit_name);
1000 l_work_permit_header_rec.transaction_type :=EAM_PROCESS_WO_PVT.G_OPR_CREATE;
1001 l_work_permit_header_rec.pending_flag := null;
1002 l_work_permit_header_rec.completion_date := null;
1003 l_work_permit_header_rec.approved_by := null;
1004
1005 IF l_work_permit_header_rec.VALID_FROM < SYSDATE THEN
1006 l_work_permit_header_rec.VALID_FROM :=SYSDATE;
1007 END IF;
1008
1009 IF l_work_permit_header_rec.VALID_TO < SYSDATE THEN
1010 l_work_permit_header_rec.VALID_TO :=NULL;
1011 END IF;
1012
1013 -- IF (l_work_permit_header_rec.STATUS_TYPE = wip_constants.comp_chrg) THEN
1014 l_work_permit_header_rec.STATUS_TYPE :=wip_constants.draft;
1015 l_work_permit_header_rec.USER_DEFINED_STATUS_ID :=wip_constants.draft;
1016 -- END IF;
1017
1018
1019 IF EAM_PROCESS_WO_PVT.get_debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.write_debug(' Calling WORK_PERMIT from PROCESS_WORK_PERMIT'); end if;
1020 WORK_PERMIT
1021 (
1022 p_validation_level => l_validation_level
1023 , p_work_permit_id => l_permit_id
1024 , p_organization_id => l_org_id
1025 , p_work_permit_header_rec => l_work_permit_header_rec
1026 , x_work_permit_header_rec => l_out_work_permit_header_rec
1027 , x_mesg_token_tbl => l_out_Mesg_Token_Tbl
1028 , x_return_status => l_return_status
1029 );
1030
1031 x_return_status :=l_return_status;
1032
1033 FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS(
1034 X_from_entity_name => 'EAM_WORK_PERMIT',
1035 X_from_pk1_value => l_org_id,
1036 X_from_pk2_value => l_permit_id,
1037 X_from_pk3_value => '',
1038 X_from_pk4_value => '',
1039 X_from_pk5_value => '',
1040 X_to_entity_name => 'EAM_WORK_PERMIT',
1041 X_to_pk1_value => l_org_id,
1042 X_to_pk2_value => l_out_work_permit_header_rec.permit_id,
1043 X_to_pk3_value => '',
1044 X_to_pk4_value => '',
1045 X_to_pk5_value => '',
1046 X_created_by => FND_GLOBAL.USER_ID,
1047 X_last_update_login => '',
1048 X_program_application_id=> '',
1049 X_program_id => '',
1050 X_request_id => ''
1051 );
1052
1053
1054 IF(x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1055 px_permit_id :=l_out_work_permit_header_rec.permit_id;
1056 IF(p_commit = FND_API.G_TRUE) THEN
1057 COMMIT;
1058 END IF;
1059 END IF;
1060
1061
1062 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1063 THEN
1064 EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
1065 ( p_mesg_token_Tbl => l_out_Mesg_Token_Tbl
1066 , p_error_level => l_error_level
1067 , p_entity_index => l_entity_index
1068 );
1069 raise fnd_api.g_exc_unexpected_error;
1070 END IF;
1071
1072 EXCEPTION
1073 WHEN fnd_api.g_exc_unexpected_error THEN
1074 ROLLBACK TO COPY_PERMIT;
1075 x_return_status := FND_API.G_RET_STS_ERROR;
1076 x_msg_count := EAM_ERROR_MESSAGE_PVT.Get_Message_Count;
1077 WHEN OTHERS THEN
1078 ROLLBACK TO COPY_PERMIT;
1079 x_return_status := FND_API.G_RET_STS_ERROR;
1080
1081 END COPY_WORK_PERMIT;
1082
1083 END EAM_PROCESS_PERMIT_PVT;
1084