[Home] [Help]
PACKAGE BODY: APPS.EAM_ISOLATION_UTILITY_PVT
Source
1 PACKAGE BODY EAM_ISOLATION_UTILITY_PVT AS
2 /* $Header: EAMVISUB.pls 120.0 2011/03/04 23:29:10 mashah noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2011 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME: EAMVISUB.pls
9 --
10 -- DESCRIPTION: Body of package EAM_ISOLATION_UTILITY_PVT
11 --
12 -- NOTES
13 --
14 -- HISTORY
15 --
16 ***************************************************************************/
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_ISOLATION_UTILITY_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EAMVISUB.pls';
19 /*********************************************************************
20 * Procedure : QUERY_ROW
21 * Purpose : Procedure will query the database record
22 and return with those records.
23 ***********************************************************************/
24 PROCEDURE QUERY_ROW(
25 p_isolation_id IN NUMBER ,
26 p_organization_id IN NUMBER ,
27 x_isolation_header_rec OUT NOCOPY EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type ,
28 x_Return_status OUT NOCOPY VARCHAR2 )
29 IS
30 l_isolation_header_rec EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type;
31 BEGIN
32 SELECT iso.ISOLATION_ID ,
33 iso.ISOLATION_NAME ,
34 iso.ISOLATION_TYPE ,
35 iso.DESCRIPTION ,
36 iso.ORGANIZATION_ID ,
37 iso.PENDING_FLAG ,
38 iso.EFFECTIVE_START_DATE ,
39 iso.EFFECTIVE_END_DATE ,
40 iso.PERMIT_REQUIRED_FLAG ,
41 iso.APPROVED_BY ,
42 iso.USER_DEFINED_STATUS_ID ,
43 iso.STATUS_TYPE ,
44 iso.ATTRIBUTE_CATEGORY ,
45 iso.ATTRIBUTE1 ,
46 iso.ATTRIBUTE2 ,
47 iso.ATTRIBUTE3 ,
48 iso.ATTRIBUTE4 ,
49 iso.ATTRIBUTE5 ,
50 iso.ATTRIBUTE6 ,
51 iso.ATTRIBUTE7 ,
52 iso.ATTRIBUTE8 ,
53 iso.ATTRIBUTE9 ,
54 iso.ATTRIBUTE10 ,
55 iso.ATTRIBUTE11 ,
56 iso.ATTRIBUTE12 ,
57 iso.ATTRIBUTE13 ,
58 iso.ATTRIBUTE14 ,
59 iso.ATTRIBUTE15 ,
60 iso.ATTRIBUTE16 ,
61 iso.ATTRIBUTE17 ,
62 iso.ATTRIBUTE18 ,
63 iso.ATTRIBUTE19 ,
64 iso.ATTRIBUTE20 ,
65 iso.ATTRIBUTE21 ,
66 iso.ATTRIBUTE22 ,
67 iso.ATTRIBUTE23 ,
68 iso.ATTRIBUTE24 ,
69 iso.ATTRIBUTE25 ,
70 iso.ATTRIBUTE26 ,
71 iso.ATTRIBUTE27 ,
72 iso.ATTRIBUTE28 ,
73 iso.ATTRIBUTE29 ,
74 iso.ATTRIBUTE30 ,
75 iso.CREATION_DATE ,
76 iso.CREATED_BY
77 INTO l_isolation_header_rec.ISOLATION_ID ,
78 l_isolation_header_rec.ISOLATION_NAME ,
79 l_isolation_header_rec.ISOLATION_TYPE ,
80 l_isolation_header_rec.DESCRIPTION ,
81 l_isolation_header_rec.ORGANIZATION_ID ,
82 l_isolation_header_rec.PENDING_FLAG ,
83 l_isolation_header_rec.EFFECTIVE_START_DATE ,
84 l_isolation_header_rec.EFFECTIVE_END_DATE ,
85 l_isolation_header_rec.PERMIT_REQUIRED ,
86 l_isolation_header_rec.APPROVED_BY ,
87 l_isolation_header_rec.USER_DEFINED_STATUS ,
88 l_isolation_header_rec.SYSTEM_STATUS ,
89 l_isolation_header_rec.ATTRIBUTE_CATEGORY ,
90 l_isolation_header_rec.ATTRIBUTE1 ,
91 l_isolation_header_rec.ATTRIBUTE2 ,
92 l_isolation_header_rec.ATTRIBUTE3 ,
93 l_isolation_header_rec.ATTRIBUTE4 ,
94 l_isolation_header_rec.ATTRIBUTE5 ,
95 l_isolation_header_rec.ATTRIBUTE6 ,
96 l_isolation_header_rec.ATTRIBUTE7 ,
97 l_isolation_header_rec.ATTRIBUTE8 ,
98 l_isolation_header_rec.ATTRIBUTE9 ,
99 l_isolation_header_rec.ATTRIBUTE10 ,
100 l_isolation_header_rec.ATTRIBUTE11 ,
101 l_isolation_header_rec.ATTRIBUTE12 ,
102 l_isolation_header_rec.ATTRIBUTE13 ,
103 l_isolation_header_rec.ATTRIBUTE14 ,
104 l_isolation_header_rec.ATTRIBUTE15 ,
105 l_isolation_header_rec.ATTRIBUTE16 ,
106 l_isolation_header_rec.ATTRIBUTE17 ,
107 l_isolation_header_rec.ATTRIBUTE18 ,
108 l_isolation_header_rec.ATTRIBUTE19 ,
109 l_isolation_header_rec.ATTRIBUTE20 ,
110 l_isolation_header_rec.ATTRIBUTE21 ,
111 l_isolation_header_rec.ATTRIBUTE22 ,
112 l_isolation_header_rec.ATTRIBUTE23 ,
113 l_isolation_header_rec.ATTRIBUTE24 ,
114 l_isolation_header_rec.ATTRIBUTE25 ,
115 l_isolation_header_rec.ATTRIBUTE26 ,
116 l_isolation_header_rec.ATTRIBUTE27 ,
117 l_isolation_header_rec.ATTRIBUTE28 ,
118 l_isolation_header_rec.ATTRIBUTE29 ,
119 l_isolation_header_rec.ATTRIBUTE30 ,
120 l_isolation_header_rec.CREATION_DATE ,
121 l_isolation_header_rec.CREATED_BY
122 FROM EAM_ISOLATIONS iso
123 WHERE iso.isolation_id = p_isolation_id
124 AND iso.organization_id = p_organization_id;
125 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_FOUND;
126 x_isolation_header_rec := l_isolation_header_rec;
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_NOT_FOUND;
130 x_isolation_header_rec := l_isolation_header_rec;
131 WHEN OTHERS THEN
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
133 x_isolation_header_rec := l_isolation_header_rec;
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_isolation_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type ,
142 x_return_Status OUT NOCOPY VARCHAR2 )
143 IS
144 BEGIN
145 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
146 EAM_ERROR_MESSAGE_PVT.Write_Debug('Writing Isolation record for ' || p_isolation_header_rec.ISOLATION_NAME);
147 END IF;
148 INSERT
149 INTO EAM_ISOLATIONS
150 (
151 ISOLATION_ID ,
152 ISOLATION_NAME ,
153 ISOLATION_TYPE ,
154 DESCRIPTION ,
155 ORGANIZATION_ID ,
156 PENDING_FLAG ,
157 EFFECTIVE_START_DATE ,
158 EFFECTIVE_END_DATE ,
159 PERMIT_REQUIRED_FLAG ,
160 APPROVED_BY ,
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 LAST_UPDATE_DATE ,
195 LAST_UPDATED_BY ,
196 CREATION_DATE ,
197 CREATED_BY ,
198 LAST_UPDATE_LOGIN
199 )
200 VALUES
201 (
202 p_isolation_header_rec.ISOLATION_ID ,
203 p_isolation_header_rec.ISOLATION_NAME ,
204 p_isolation_header_rec.ISOLATION_TYPE ,
205 p_isolation_header_rec.DESCRIPTION ,
206 p_isolation_header_rec.ORGANIZATION_ID ,
207 p_isolation_header_rec.PENDING_FLAG ,
208 p_isolation_header_rec.EFFECTIVE_START_DATE ,
209 p_isolation_header_rec.EFFECTIVE_END_DATE ,
210 p_isolation_header_rec.PERMIT_REQUIRED ,
211 DECODE(p_isolation_header_rec.APPROVED_BY, FND_API.G_MISS_CHAR, NULL, p_isolation_header_rec.APPROVED_BY) ,
212 p_isolation_header_rec.USER_DEFINED_STATUS ,
213 p_isolation_header_rec.SYSTEM_STATUS ,
214 p_isolation_header_rec.ATTRIBUTE_CATEGORY ,
215 p_isolation_header_rec.ATTRIBUTE1 ,
216 p_isolation_header_rec.ATTRIBUTE2 ,
217 p_isolation_header_rec.ATTRIBUTE3 ,
218 p_isolation_header_rec.ATTRIBUTE4 ,
219 p_isolation_header_rec.ATTRIBUTE5 ,
220 p_isolation_header_rec.ATTRIBUTE6 ,
221 p_isolation_header_rec.ATTRIBUTE7 ,
222 p_isolation_header_rec.ATTRIBUTE8 ,
223 p_isolation_header_rec.ATTRIBUTE9 ,
224 p_isolation_header_rec.ATTRIBUTE10 ,
225 p_isolation_header_rec.ATTRIBUTE11 ,
226 p_isolation_header_rec.ATTRIBUTE12 ,
227 p_isolation_header_rec.ATTRIBUTE13 ,
228 p_isolation_header_rec.ATTRIBUTE14 ,
229 p_isolation_header_rec.ATTRIBUTE15 ,
230 p_isolation_header_rec.ATTRIBUTE16 ,
231 p_isolation_header_rec.ATTRIBUTE17 ,
232 p_isolation_header_rec.ATTRIBUTE18 ,
233 p_isolation_header_rec.ATTRIBUTE19 ,
234 p_isolation_header_rec.ATTRIBUTE20 ,
235 p_isolation_header_rec.ATTRIBUTE21 ,
236 p_isolation_header_rec.ATTRIBUTE22 ,
237 p_isolation_header_rec.ATTRIBUTE23 ,
238 p_isolation_header_rec.ATTRIBUTE24 ,
239 p_isolation_header_rec.ATTRIBUTE25 ,
240 p_isolation_header_rec.ATTRIBUTE26 ,
241 p_isolation_header_rec.ATTRIBUTE27 ,
242 p_isolation_header_rec.ATTRIBUTE28 ,
243 p_isolation_header_rec.ATTRIBUTE29 ,
244 p_isolation_header_rec.ATTRIBUTE30 ,
245 SYSDATE ,
246 FND_GLOBAL.user_id ,
247 SYSDATE ,
248 FND_GLOBAL.user_id ,
249 FND_GLOBAL.login_id
250 );
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252 EXCEPTION
253 WHEN OTHERS THEN
254 x_return_status := fnd_api.g_ret_sts_error;
255 END INSERT_ROW;
256
257 /********************************************************************
258 * Procedure : UPDATE_ROW
259 * Purpose : Procedure will perform an update on the table
260 *********************************************************************/
261 PROCEDURE UPDATE_ROW
262 (
263 p_isolation_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type ,
264 x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type ,
265 x_return_Status OUT NOCOPY VARCHAR2
266 )
267 IS
268 BEGIN
269 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Updating Isolation rec for ' || p_isolation_header_rec.ISOLATION_NAME);END IF;
270
271 UPDATE EAM_ISOLATIONS
272 SET DESCRIPTION =p_isolation_header_rec.DESCRIPTION ,
273 EFFECTIVE_START_DATE =p_isolation_header_rec.EFFECTIVE_START_DATE ,
274 EFFECTIVE_END_DATE =p_isolation_header_rec.EFFECTIVE_END_DATE ,
275 PENDING_FLAG =p_isolation_header_rec.PENDING_FLAG ,
276 PERMIT_REQUIRED_FLAG =p_isolation_header_rec.PERMIT_REQUIRED ,
277 STATUS_TYPE =p_isolation_header_rec.SYSTEM_STATUS ,
278 USER_DEFINED_STATUS_ID =p_isolation_header_rec.USER_DEFINED_STATUS ,
279 ATTRIBUTE_CATEGORY =p_isolation_header_rec.ATTRIBUTE_CATEGORY ,
280 ATTRIBUTE1 =p_isolation_header_rec.ATTRIBUTE1 ,
281 ATTRIBUTE2 =p_isolation_header_rec.ATTRIBUTE2 ,
282 ATTRIBUTE3 =p_isolation_header_rec.ATTRIBUTE3 ,
283 ATTRIBUTE4 =p_isolation_header_rec.ATTRIBUTE4 ,
284 ATTRIBUTE5 =p_isolation_header_rec.ATTRIBUTE5 ,
285 ATTRIBUTE6 =p_isolation_header_rec.ATTRIBUTE6 ,
286 ATTRIBUTE7 =p_isolation_header_rec.ATTRIBUTE7 ,
287 ATTRIBUTE8 =p_isolation_header_rec.ATTRIBUTE8 ,
288 ATTRIBUTE9 =p_isolation_header_rec.ATTRIBUTE9 ,
289 ATTRIBUTE10 =p_isolation_header_rec.ATTRIBUTE10 ,
290 ATTRIBUTE11 =p_isolation_header_rec.ATTRIBUTE11 ,
291 ATTRIBUTE12 =p_isolation_header_rec.ATTRIBUTE12 ,
292 ATTRIBUTE13 =p_isolation_header_rec.ATTRIBUTE13 ,
293 ATTRIBUTE14 =p_isolation_header_rec.ATTRIBUTE14 ,
294 ATTRIBUTE15 =p_isolation_header_rec.ATTRIBUTE15 ,
295 ATTRIBUTE16 =p_isolation_header_rec.ATTRIBUTE16 ,
296 ATTRIBUTE17 =p_isolation_header_rec.ATTRIBUTE17 ,
297 ATTRIBUTE18 =p_isolation_header_rec.ATTRIBUTE18 ,
298 ATTRIBUTE19 =p_isolation_header_rec.ATTRIBUTE19 ,
299 ATTRIBUTE20 =p_isolation_header_rec.ATTRIBUTE20 ,
300 ATTRIBUTE21 =p_isolation_header_rec.ATTRIBUTE21 ,
301 ATTRIBUTE22 =p_isolation_header_rec.ATTRIBUTE22 ,
302 ATTRIBUTE23 =p_isolation_header_rec.ATTRIBUTE23 ,
303 ATTRIBUTE24 =p_isolation_header_rec.ATTRIBUTE24 ,
304 ATTRIBUTE25 =p_isolation_header_rec.ATTRIBUTE25 ,
305 ATTRIBUTE26 =p_isolation_header_rec.ATTRIBUTE26 ,
306 ATTRIBUTE27 =p_isolation_header_rec.ATTRIBUTE27 ,
307 ATTRIBUTE28 =p_isolation_header_rec.ATTRIBUTE28 ,
308 ATTRIBUTE29 =p_isolation_header_rec.ATTRIBUTE29 ,
309 ATTRIBUTE30 =p_isolation_header_rec.ATTRIBUTE30 ,
310 APPROVED_BY =p_isolation_header_rec.APPROVED_BY ,
311 LAST_UPDATE_DATE =SYSDATE ,
312 LAST_UPDATED_BY =FND_GLOBAL.user_id ,
313 LAST_UPDATE_LOGIN =FND_GLOBAL.login_id
314 WHERE isolation_id = p_isolation_header_rec.isolation_id
315 AND organization_id = p_isolation_header_rec.organization_id;
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317 EXCEPTION
318 WHEN OTHERS THEN
319 x_return_status := fnd_api.g_ret_sts_error;
320 END UPDATE_ROW;
321 /********************************************************************
322 * Procedure : PERFORM_WRITES
323 * Purpose : This is the only procedure that the user will have
324 access to when he/she needs to perform any kind of writes to the table.
325 *********************************************************************/
326 PROCEDURE PERFORM_WRITES(
327 p_isolation_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type ,
328 x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type ,
329 x_return_Status OUT NOCOPY VARCHAR2 )
330 IS
331 l_msg_data VARCHAR2(240);
332 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
333 BEGIN
334 IF p_isolation_header_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE THEN
335 Insert_Row ( p_isolation_header_rec => p_isolation_header_rec
336 , x_return_Status => l_return_status
337 );
338 ELSIF p_isolation_header_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE THEN
339 Update_Row ( p_isolation_header_rec => p_isolation_header_rec
340 , x_mesg_token_Tbl => x_mesg_token_Tbl
341 , x_return_Status => l_return_status
342 );
343 END IF;
344 x_return_status := l_return_status;
345 EXCEPTION
346 WHEN OTHERS THEN
347 x_return_status := fnd_api.g_ret_sts_error;
348 END PERFORM_WRITES;
349
350 /********************************************************************
351 * Procedure : CHANGE_ISOLATION_STATUS
352 * Purpose : This procedure performs different validations for status changes.
353 *********************************************************************/
354 PROCEDURE CHANGE_ISOLATION_STATUS(
355 p_isolation_id IN NUMBER ,
356 p_organization_id IN NUMBER ,
357 p_to_status_type IN NUMBER ,
358 p_user_id IN NUMBER ,
359 p_responsibility_id IN NUMBER ,
360 p_transaction_type IN NUMBER ,
361 x_return_status OUT NOCOPY VARCHAR2 ,
362 x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type )
363 IS
364 l_isolation_id NUMBER := 0;
365 l_current_status NUMBER := 0;
366 l_to_status_type NUMBER := 0;
367 l_organization_id NUMBER := 0;
368 l_final_status NUMBER := 0;
369 l_user_id NUMBER :=0;
370 l_responsibility_id NUMBER :=0;
371 BEGIN
372 SAVEPOINT CHANGE_ISOLATION_STATUS;
373 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
374 EAM_ERROR_MESSAGE_PVT.Write_Debug('Inside CHANGE_ISOLATION_STATUS ');
375 END IF;
376 l_isolation_id := p_isolation_id;
377 l_organization_id := p_organization_id;
378 l_to_status_type := p_to_status_type;
379 l_user_id := p_user_id;
380 l_responsibility_id := p_responsibility_id;
381 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
382 EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating Status');
383 END IF;
384 -- Validate status_id
385 IF l_to_status_type NOT IN (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,WIP_CONSTANTS.COMP_NOCHRG, WIP_CONSTANTS.CANCELLED, WIP_CONSTANTS.DRAFT) THEN
386 raise fnd_api.g_exc_unexpected_error;
387 END IF;
388 -- Update status in tablesolation
389 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
390 EAM_ERROR_MESSAGE_PVT.Write_Debug('Update status in isolation table');
391 END IF;
392 BEGIN
393 UPDATE EAM_ISOLATIONS
394 SET STATUS_TYPE = l_to_status_type
395 WHERE ORGANIZATION_ID = l_organization_id
396 AND isolation_id = l_isolation_id;
397 EXCEPTION
398 WHEN OTHERS THEN
399 raise fnd_api.g_exc_unexpected_error;
400 END;
401 EXCEPTION
402 WHEN fnd_api.g_exc_error THEN
403 ROLLBACK TO CHANGE_ISOLATION_STATUS;
404 x_return_status := fnd_api.g_ret_sts_error;
405 WHEN OTHERS THEN
406 ROLLBACK TO CHANGE_isolation_STATUS;
407 x_return_status := fnd_api.g_ret_sts_unexp_error;
408 END CHANGE_isolation_STATUS;
409 /*
410 PROCEDURE INSERT_ISOLATION_HISTORY_ROW
411 ( p_object_id IN NUMBER
412 , p_object_name IN VARCHAR2
413 , p_object_type IN NUMBER :=3
414 , p_event IN VARCHAR2
415 , p_status IN VARCHAR2
416 , p_details IN VARCHAR2
417 , p_user_id IN NUMBER
418 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
419 , x_return_Status OUT NOCOPY VARCHAR2
420 )IS
421 BEGIN
422 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Inside INSERT_ISOLATION_HISTORY_ROW '); END IF;
423 END INSERT_ISOLATION_HISTORY_ROW; */
424 END EAM_ISOLATION_UTILITY_PVT;