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