[Home] [Help]
PACKAGE BODY: APPS.EAM_CLEARANCE_UTILITY_PVT
Source
1 PACKAGE BODY EAM_CLEARANCE_UTILITY_PVT AS
2 /* $Header: EAMVCLUB.pls 120.0 2011/03/04 00:53:48 mashah noship $ */
3
4 /***************************************************************************
5 --
6 -- Copyright (c) 2011 Oracle Corporation, Redwood Shores, CA, USA
7 -- All rights reserved.
8 --
9 -- FILENAME: EAMVCLUB.pls
10 --
11 -- DESCRIPTION: Body of package EAM_CLEARANCE_UTILITY_PVT
12 --
13 -- NOTES
14 --
15 -- HISTORY
16 --
17 --
18 ***************************************************************************/
19 -- g_debug_flag VARCHAR2(1) := 'N';
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_CLEARANCE_UTILITY_PVT';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EAMVCLUB.pls';
22
23
24 /*********************************************************************
25 * Procedure : QUERY_ROW
26 * Purpose : Procedure will query the database record
27 and return with those records.
28 ***********************************************************************/
29 PROCEDURE QUERY_ROW
30 ( p_work_clearance_id IN NUMBER
31 , p_organization_id IN NUMBER
32 , x_work_clearance_header_rec OUT NOCOPY EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type
33 , x_return_status OUT NOCOPY VARCHAR2
34 ) IS
35
36 l_work_clearance_header_rec EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type;
37
38 BEGIN
39
40 SELECT
41 WORK_CLEARANCE_ID
42 ,WORK_CLEARANCE_NAME
43 ,LAST_ISOLATION_ID
44 ,DESCRIPTION
45 ,ORGANIZATION_ID
46 ,PENDING_FLAG
47 ,SCHEDULED_ESTAB_START_DATE
48 ,SCHEDULED_ESTAB_END_DATE
49 ,SCHEDULED_REESTAB_START_DATE
50 ,SCHEDULED_REESTAB_END_DATE
51 ,USER_DEFINED_STATUS_ID
52 ,STATUS_TYPE
53 ,APPROVED_BY
54 ,COMPLETION_DATE
55 ,PERMIT_REQUIRED_FLAG
56 ,ATTRIBUTE_CATEGORY
57 ,ATTRIBUTE1
58 ,ATTRIBUTE2
59 ,ATTRIBUTE3
60 ,ATTRIBUTE4
61 ,ATTRIBUTE5
62 ,ATTRIBUTE6
63 ,ATTRIBUTE7
64 ,ATTRIBUTE8
65 ,ATTRIBUTE9
66 ,ATTRIBUTE10
67 ,ATTRIBUTE11
68 ,ATTRIBUTE12
69 ,ATTRIBUTE13
70 ,ATTRIBUTE14
71 ,ATTRIBUTE15
72 ,ATTRIBUTE16
73 ,ATTRIBUTE17
74 ,ATTRIBUTE18
75 ,ATTRIBUTE19
76 ,ATTRIBUTE20
77 ,ATTRIBUTE21
78 ,ATTRIBUTE22
79 ,ATTRIBUTE23
80 ,ATTRIBUTE24
81 ,ATTRIBUTE25
82 ,ATTRIBUTE26
83 ,ATTRIBUTE27
84 ,ATTRIBUTE28
85 ,ATTRIBUTE29
86 ,ATTRIBUTE30
87
88 INTO
89 l_work_clearance_header_rec.WORK_CLEARANCE_ID
90 ,l_work_clearance_header_rec.WORK_CLEARANCE_NAME
91 ,l_work_clearance_header_rec.ISOLATION_ID
92 ,l_work_clearance_header_rec.DESCRIPTION
93 ,l_work_clearance_header_rec.ORGANIZATION_ID
94 ,l_work_clearance_header_rec.PENDING_FLAG
95 ,l_work_clearance_header_rec.SCHEDULED_ESTAB_START_DATE
96 ,l_work_clearance_header_rec.SCHEDULED_ESTAB_END_DATE
97 ,l_work_clearance_header_rec.SCHEDULED_REESTAB_START_DATE
98 ,l_work_clearance_header_rec.SCHEDULED_REESTAB_END_DATE
99 ,l_work_clearance_header_rec.USER_DEFINED_STATUS
100 ,l_work_clearance_header_rec.SYSTEM_STATUS
101 ,l_work_clearance_header_rec.APPROVED_BY
102 ,l_work_clearance_header_rec.COMPLETION_DATE
103 ,l_work_clearance_header_rec.PERMIT_REQUIRED
104 ,l_work_clearance_header_rec.ATTRIBUTE_CATEGORY
105 ,l_work_clearance_header_rec.ATTRIBUTE1
106 ,l_work_clearance_header_rec.ATTRIBUTE2
107 ,l_work_clearance_header_rec.ATTRIBUTE3
108 ,l_work_clearance_header_rec.ATTRIBUTE4
109 ,l_work_clearance_header_rec.ATTRIBUTE5
110 ,l_work_clearance_header_rec.ATTRIBUTE6
111 ,l_work_clearance_header_rec.ATTRIBUTE7
112 ,l_work_clearance_header_rec.ATTRIBUTE8
113 ,l_work_clearance_header_rec.ATTRIBUTE9
114 ,l_work_clearance_header_rec.ATTRIBUTE10
115 ,l_work_clearance_header_rec.ATTRIBUTE11
116 ,l_work_clearance_header_rec.ATTRIBUTE12
117 ,l_work_clearance_header_rec.ATTRIBUTE13
118 ,l_work_clearance_header_rec.ATTRIBUTE14
119 ,l_work_clearance_header_rec.ATTRIBUTE15
120 ,l_work_clearance_header_rec.ATTRIBUTE16
121 ,l_work_clearance_header_rec.ATTRIBUTE17
122 ,l_work_clearance_header_rec.ATTRIBUTE18
123 ,l_work_clearance_header_rec.ATTRIBUTE19
124 ,l_work_clearance_header_rec.ATTRIBUTE20
125 ,l_work_clearance_header_rec.ATTRIBUTE21
126 ,l_work_clearance_header_rec.ATTRIBUTE22
127 ,l_work_clearance_header_rec.ATTRIBUTE23
128 ,l_work_clearance_header_rec.ATTRIBUTE24
129 ,l_work_clearance_header_rec.ATTRIBUTE25
130 ,l_work_clearance_header_rec.ATTRIBUTE26
131 ,l_work_clearance_header_rec.ATTRIBUTE27
132 ,l_work_clearance_header_rec.ATTRIBUTE28
133 ,l_work_clearance_header_rec.ATTRIBUTE28
134 ,l_work_clearance_header_rec.ATTRIBUTE30
135
136
137 FROM EAM_WORK_CLEARANCES ewc
138 WHERE ewc.WORK_CLEARANCE_ID = p_work_clearance_id
139 AND ewc.organization_id = p_organization_id;
140
141 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_FOUND;
142 x_work_clearance_header_rec:= l_work_clearance_header_rec;
143
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_NOT_FOUND;
147 x_work_clearance_header_rec:= l_work_clearance_header_rec;
148
149 WHEN OTHERS THEN
150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
151 x_work_clearance_header_rec:= l_work_clearance_header_rec;
152
153
154 END QUERY_ROW;
155
156
157 /********************************************************************
158 * Procedure : INSERT_ROW
159 * Purpose : Procedure will perfrom an insert into the table
160 *********************************************************************/
161 PROCEDURE INSERT_ROW
162 ( p_work_clearance_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type
163 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
164 , x_return_Status OUT NOCOPY VARCHAR2
165 )IS
166
167 BEGIN
168
169 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Writing clearance rec for ' || p_work_clearance_header_rec.WORK_CLEARANCE_NAME); END IF;
170
171
172 INSERT INTO EAM_WORK_CLEARANCES(
173 WORK_CLEARANCE_ID
174 ,WORK_CLEARANCE_NAME
175 ,LAST_ISOLATION_ID
176 ,DESCRIPTION
177 ,ORGANIZATION_ID
178 ,PENDING_FLAG
179 ,SCHEDULED_ESTAB_START_DATE
180 ,SCHEDULED_ESTAB_END_DATE
181 ,SCHEDULED_REESTAB_START_DATE
182 ,SCHEDULED_REESTAB_END_DATE
183 ,USER_DEFINED_STATUS_ID
184 ,STATUS_TYPE
185 ,COMPLETION_DATE
186 ,PERMIT_REQUIRED_FLAG
187 ,ATTRIBUTE_CATEGORY
188 ,ATTRIBUTE1
189 ,ATTRIBUTE2
190 ,ATTRIBUTE3
191 ,ATTRIBUTE4
192 ,ATTRIBUTE5
193 ,ATTRIBUTE6
194 ,ATTRIBUTE7
195 ,ATTRIBUTE8
196 ,ATTRIBUTE9
197 ,ATTRIBUTE10
198 ,ATTRIBUTE11
199 ,ATTRIBUTE12
200 ,ATTRIBUTE13
201 ,ATTRIBUTE14
202 ,ATTRIBUTE15
203 ,ATTRIBUTE16
204 ,ATTRIBUTE17
205 ,ATTRIBUTE18
206 ,ATTRIBUTE19
207 ,ATTRIBUTE20
208 ,ATTRIBUTE21
209 ,ATTRIBUTE22
210 ,ATTRIBUTE23
211 ,ATTRIBUTE24
212 ,ATTRIBUTE25
213 ,ATTRIBUTE26
214 ,ATTRIBUTE27
215 ,ATTRIBUTE28
216 ,ATTRIBUTE29
217 ,ATTRIBUTE30
218 ,APPROVED_BY
219 ,LAST_UPDATE_DATE
220 ,LAST_UPDATED_BY
221 ,CREATION_DATE
222 ,CREATED_BY
223 ,LAST_UPDATE_LOGIN)
224 VALUES
225 ( p_work_clearance_header_rec.WORK_CLEARANCE_ID
226 ,p_work_clearance_header_rec.WORK_CLEARANCE_NAME
227 ,p_work_clearance_header_rec.ISOLATION_ID
228 ,p_work_clearance_header_rec.DESCRIPTION
229 ,p_work_clearance_header_rec.ORGANIZATION_ID
230 ,p_work_clearance_header_rec.PENDING_FLAG
231 ,p_work_clearance_header_rec.SCHEDULED_ESTAB_START_DATE
232 ,p_work_clearance_header_rec.SCHEDULED_ESTAB_END_DATE
233 ,p_work_clearance_header_rec.SCHEDULED_REESTAB_START_DATE
234 ,p_work_clearance_header_rec.SCHEDULED_REESTAB_END_DATE
235 ,p_work_clearance_header_rec.USER_DEFINED_STATUS
236 ,p_work_clearance_header_rec.SYSTEM_STATUS
237 ,p_work_clearance_header_rec.COMPLETION_DATE
238 ,p_work_clearance_header_rec.PERMIT_REQUIRED
239 ,p_work_clearance_header_rec.ATTRIBUTE_CATEGORY
240 ,p_work_clearance_header_rec.ATTRIBUTE1
241 ,p_work_clearance_header_rec.ATTRIBUTE2
242 ,p_work_clearance_header_rec.ATTRIBUTE3
243 ,p_work_clearance_header_rec.ATTRIBUTE4
244 ,p_work_clearance_header_rec.ATTRIBUTE5
245 ,p_work_clearance_header_rec.ATTRIBUTE6
246 ,p_work_clearance_header_rec.ATTRIBUTE7
247 ,p_work_clearance_header_rec.ATTRIBUTE8
248 ,p_work_clearance_header_rec.ATTRIBUTE9
249 ,p_work_clearance_header_rec.ATTRIBUTE10
250 ,p_work_clearance_header_rec.ATTRIBUTE11
251 ,p_work_clearance_header_rec.ATTRIBUTE12
252 ,p_work_clearance_header_rec.ATTRIBUTE13
253 ,p_work_clearance_header_rec.ATTRIBUTE14
254 ,p_work_clearance_header_rec.ATTRIBUTE15
255 ,p_work_clearance_header_rec.ATTRIBUTE16
256 ,p_work_clearance_header_rec.ATTRIBUTE17
257 ,p_work_clearance_header_rec.ATTRIBUTE18
258 ,p_work_clearance_header_rec.ATTRIBUTE19
259 ,p_work_clearance_header_rec.ATTRIBUTE20
260 ,p_work_clearance_header_rec.ATTRIBUTE21
261 ,p_work_clearance_header_rec.ATTRIBUTE22
262 ,p_work_clearance_header_rec.ATTRIBUTE23
263 ,p_work_clearance_header_rec.ATTRIBUTE24
264 ,p_work_clearance_header_rec.ATTRIBUTE25
265 ,p_work_clearance_header_rec.ATTRIBUTE26
266 ,p_work_clearance_header_rec.ATTRIBUTE27
267 ,p_work_clearance_header_rec.ATTRIBUTE28
268 ,p_work_clearance_header_rec.ATTRIBUTE29
269 ,p_work_clearance_header_rec.ATTRIBUTE30
270 ,decode(p_work_clearance_header_rec.APPROVED_BY, FND_API.G_MISS_CHAR, NULL, p_work_clearance_header_rec.APPROVED_BY)
271 ,SYSDATE
272 ,FND_GLOBAL.user_id
273 ,SYSDATE
274 ,FND_GLOBAL.user_id
275 ,FND_GLOBAL.login_id);
276
277 x_return_status := FND_API.G_RET_STS_SUCCESS;
278
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 x_return_status := fnd_api.g_ret_sts_error;
283
284 END INSERT_ROW;
285
286 /********************************************************************
287 * Procedure : UPDATE_ROW
288 * Purpose : Procedure will perform an update on the table
289 *********************************************************************/
290 PROCEDURE UPDATE_ROW
291 ( p_work_clearance_header_rec IN EAM_PROCESS_SAFETY_PUB. eam_clearance_header_rec_type
292 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
293 , x_return_Status OUT NOCOPY VARCHAR2
294 )IS
295
296 BEGIN
297
298 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Updating Permit rec for ' || p_work_clearance_header_rec.WORK_CLEARANCE_NAME); END IF;
299
300 --Note : double check which of these columns are updeatable.
301
302 UPDATE EAM_WORK_CLEARANCES
303 SET WORK_CLEARANCE_NAME = p_work_clearance_header_rec.WORK_CLEARANCE_NAME
304 ,DESCRIPTION =p_work_clearance_header_rec.DESCRIPTION
305 ,LAST_ISOLATION_ID =p_work_clearance_header_rec.ISOLATION_ID
306 ,PENDING_FLAG =p_work_clearance_header_rec.PENDING_FLAG
307 ,SCHEDULED_ESTAB_START_DATE =p_work_clearance_header_rec.SCHEDULED_ESTAB_START_DATE
308 ,SCHEDULED_ESTAB_END_DATE =p_work_clearance_header_rec.SCHEDULED_ESTAB_END_DATE
309 ,SCHEDULED_REESTAB_START_DATE =p_work_clearance_header_rec.SCHEDULED_REESTAB_START_DATE
310 ,SCHEDULED_REESTAB_END_DATE =p_work_clearance_header_rec.SCHEDULED_REESTAB_END_DATE
311 ,STATUS_TYPE =p_work_clearance_header_rec.SYSTEM_STATUS
312 ,USER_DEFINED_STATUS_ID =p_work_clearance_header_rec.USER_DEFINED_STATUS
313 ,APPROVED_BY =p_work_clearance_header_rec.APPROVED_BY
314 ,COMPLETION_DATE =p_work_clearance_header_rec.COMPLETION_DATE
315 ,PERMIT_REQUIRED_FLAG =p_work_clearance_header_rec.PERMIT_REQUIRED
316 ,ATTRIBUTE_CATEGORY =p_work_clearance_header_rec.ATTRIBUTE_CATEGORY
317 ,ATTRIBUTE1 =p_work_clearance_header_rec.ATTRIBUTE1
321 ,ATTRIBUTE5 =p_work_clearance_header_rec.ATTRIBUTE5
318 ,ATTRIBUTE2 =p_work_clearance_header_rec.ATTRIBUTE2
319 ,ATTRIBUTE3 =p_work_clearance_header_rec.ATTRIBUTE3
320 ,ATTRIBUTE4 =p_work_clearance_header_rec.ATTRIBUTE4
322 ,ATTRIBUTE6 =p_work_clearance_header_rec.ATTRIBUTE6
323 ,ATTRIBUTE7 =p_work_clearance_header_rec.ATTRIBUTE7
324 ,ATTRIBUTE8 =p_work_clearance_header_rec.ATTRIBUTE8
325 ,ATTRIBUTE9 =p_work_clearance_header_rec.ATTRIBUTE9
326 ,ATTRIBUTE10 =p_work_clearance_header_rec.ATTRIBUTE10
327 ,ATTRIBUTE11 =p_work_clearance_header_rec.ATTRIBUTE11
328 ,ATTRIBUTE12 =p_work_clearance_header_rec.ATTRIBUTE12
329 ,ATTRIBUTE13 =p_work_clearance_header_rec.ATTRIBUTE13
330 ,ATTRIBUTE14 =p_work_clearance_header_rec.ATTRIBUTE14
331 ,ATTRIBUTE15 =p_work_clearance_header_rec.ATTRIBUTE15
332 ,ATTRIBUTE16 =p_work_clearance_header_rec.ATTRIBUTE16
333 ,ATTRIBUTE17 =p_work_clearance_header_rec.ATTRIBUTE17
334 ,ATTRIBUTE18 =p_work_clearance_header_rec.ATTRIBUTE18
335 ,ATTRIBUTE19 =p_work_clearance_header_rec.ATTRIBUTE19
336 ,ATTRIBUTE20 =p_work_clearance_header_rec.ATTRIBUTE20
337 ,ATTRIBUTE21 =p_work_clearance_header_rec.ATTRIBUTE21
338 ,ATTRIBUTE22 =p_work_clearance_header_rec.ATTRIBUTE22
339 ,ATTRIBUTE23 =p_work_clearance_header_rec.ATTRIBUTE23
340 ,ATTRIBUTE24 =p_work_clearance_header_rec.ATTRIBUTE24
341 ,ATTRIBUTE25 =p_work_clearance_header_rec.ATTRIBUTE25
342 ,ATTRIBUTE26 =p_work_clearance_header_rec.ATTRIBUTE26
343 ,ATTRIBUTE27 =p_work_clearance_header_rec.ATTRIBUTE27
344 ,ATTRIBUTE28 =p_work_clearance_header_rec.ATTRIBUTE28
345 ,ATTRIBUTE29 =p_work_clearance_header_rec.ATTRIBUTE29
346 ,ATTRIBUTE30 =p_work_clearance_header_rec.ATTRIBUTE30
347 ,LAST_UPDATE_DATE =SYSDATE
348 ,LAST_UPDATED_BY =FND_GLOBAL.user_id
349 ,LAST_UPDATE_LOGIN =FND_GLOBAL.login_id
350 WHERE WORK_CLEARANCE_ID = p_work_clearance_header_rec.WORK_CLEARANCE_ID
351 AND organization_id = p_work_clearance_header_rec.organization_id;
352
353 x_return_status := FND_API.G_RET_STS_SUCCESS;
354 EXCEPTION
355 WHEN OTHERS THEN
356 x_return_status := fnd_api.g_ret_sts_error;
357
358
359 END UPDATE_ROW;
360
361 /********************************************************************
362 * Procedure : PERFORM_WRITES
363 * Purpose : This is the only procedure that the user will have
364 access to when he/she needs to perform any kind of
365 writes to the table.
366 *********************************************************************/
367 PROCEDURE PERFORM_WRITES
368 ( p_work_clearance_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type
369 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
370 , x_return_Status OUT NOCOPY VARCHAR2
371 )IS
372 l_msg_data VARCHAR2(240);
373 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
374 BEGIN
375
376 IF p_work_clearance_header_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
377 THEN
378 Insert_Row
379 ( p_work_clearance_header_rec => p_work_clearance_header_rec
380 , x_mesg_token_Tbl => x_mesg_token_Tbl
381 , x_return_Status => l_return_status
382 );
383
384 ELSIF p_work_clearance_header_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
385 THEN
386 Update_Row
387 ( p_work_clearance_header_rec => p_work_clearance_header_rec
388 , x_mesg_token_Tbl => x_mesg_token_Tbl
389 , x_return_Status => l_return_status
390 );
391
392 END IF;
393
394 x_return_status := l_return_status;
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 x_return_status := fnd_api.g_ret_sts_error;
399
400
401 END PERFORM_WRITES;
402
403
404 /********************************************************************
405 * Procedure : CHANGE_ISOLATION_STATUS
406 * Purpose : This procedure performs different validations for status changes.
407 *********************************************************************/
408 PROCEDURE CHANGE_CLEARANCE_STATUS
409 ( p_clearance_id IN NUMBER
410 , p_organization_id IN NUMBER
411 , p_to_status_type IN NUMBER
412 , p_user_id IN NUMBER
413 , p_responsibility_id IN NUMBER
414 , p_transaction_type IN NUMBER
415 , x_return_status OUT NOCOPY VARCHAR2
416 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
417 )IS
418
419 l_clearance_id NUMBER := 0;
423 l_final_status NUMBER := 0;
420 l_current_status NUMBER := 0;
421 l_to_status_type NUMBER := 0;
422 l_organization_id NUMBER := 0;
424 l_user_id NUMBER :=0;
425 l_responsibility_id NUMBER :=0;
426
427 BEGIN
428
429 SAVEPOINT CHANGE_CLEARANCE_STATUS;
430
431 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Inside CHANGE_CLEARANCE_STATUS '); END IF;
432
433 l_clearance_id := p_clearance_id;
434 l_organization_id := p_organization_id;
435 l_to_status_type := p_to_status_type;
436 l_user_id := p_user_id;
437 l_responsibility_id := p_responsibility_id;
438
439 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating Status'); END IF;
440 -- Validate status_id
441
442 IF l_to_status_type NOT IN (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,WIP_CONSTANTS.COMP_NOCHRG,
443 WIP_CONSTANTS.CANCELLED, WIP_CONSTANTS.DRAFT)
444 THEN
445
446 raise fnd_api.g_exc_unexpected_error;
447
448 END IF;
449 -- Update status in clearance table
450 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Update status in clearance table'); END IF;
451 BEGIN
452
453 UPDATE EAM_WORK_CLEARANCES
454 SET STATUS_TYPE = l_to_status_type
455 WHERE ORGANIZATION_ID = l_organization_id
456 AND WORK_CLEARANCE_ID = l_clearance_id;
457
458 EXCEPTION
459 WHEN OTHERS THEN
460 raise fnd_api.g_exc_unexpected_error;
461 END;
462
463 EXCEPTION
464 WHEN fnd_api.g_exc_error THEN
465 ROLLBACK TO CHANGE_CLEARANCE_STATUS;
466 x_return_status := fnd_api.g_ret_sts_error;
467
468
469 WHEN OTHERS THEN
470 ROLLBACK TO CHANGE_CLEARANCE_STATUS;
471 x_return_status := fnd_api.g_ret_sts_unexp_error;
472
473 END CHANGE_CLEARANCE_STATUS;
474
475
476 /********************************************************************
477 * Procedure : INSERT_CLEARANCE_HISTORY_ROW
478 * Purpose : Procedure will insert records for CLEARANCE history
479 *********************************************************************
480 PROCEDURE CLEARANCE_ PERMIT_HISTORY_ROW
481 ( p_object_id IN NUMBER
482 , p_object_name IN VARCHAR2
483 , p_object_type IN NUMBER
484 , p_event_name IN VARCHAR2
485 , p_permit_status IN VARCHAR2
486 , p_event_desc IN VARCHAR2
487 , p_user_id IN NUMBER,
488 , p_event_date IN DATE
489 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
490 , x_return_Status OUT NOCOPY VARCHAR2
491 );*/
492 --Insert Work Clearance History record in to EAM_SAFETY_HISTORY table.
493
494 END EAM_CLEARANCE_UTILITY_PVT ;