[Home] [Help]
PACKAGE BODY: APPS.EAM_SAFETY_UTILITY_PVT
Source
1 PACKAGE BODY EAM_SAFETY_UTILITY_PVT AS
2 /* $Header: EAMVSAUB.pls 120.3 2011/06/15 12:14:08 vboddapa noship $ */
3
4 /*********************************************************************
5 * Procedure : QUERY_SAFFETY_ASSOCIATION_ROWS
6 * Purpose : Procedure will query the database record
7 and return with those records.
8 ***********************************************************************/
9
10 PROCEDURE QUERY_SAFFETY_ASSOCIATION_ROWS
11 ( p_source_id IN NUMBER
12 , p_organization_id IN NUMBER
13 , p_association_type IN NUMBER
14 , x_safety_association_rec OUT NOCOPY EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type
15 , x_return_status OUT NOCOPY VARCHAR2
16 )IS
17
18 l_safety_association_rec EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type ;
19 BEGIN
20 SELECT
21 SAFETY_ASSOCIATION_ID
22 ,SOURCE_ID
23 ,TARGET_REF_ID
24 ,ASSOCIATION_TYPE
25 ,ORGANIZATION_ID
26 ,EFFECTIVE_FROM
27 ,EFFECTIVE_TO
28 ,ENABLED
29 ,COMMENTS
30 ,ATTRIBUTE_CATEGORY
31 ,ATTRIBUTE1
32 ,ATTRIBUTE2
33 ,ATTRIBUTE3
34 ,ATTRIBUTE4
35 ,ATTRIBUTE5
36 ,ATTRIBUTE6
37 ,ATTRIBUTE7
38 ,ATTRIBUTE8
39 ,ATTRIBUTE9
40 ,ATTRIBUTE10
41 ,ATTRIBUTE11
42 ,ATTRIBUTE12
43 ,ATTRIBUTE13
44 ,ATTRIBUTE14
45 ,ATTRIBUTE15
46 ,ATTRIBUTE16
47 ,ATTRIBUTE17
48 ,ATTRIBUTE18
49 ,ATTRIBUTE19
50 ,ATTRIBUTE20
51 ,ATTRIBUTE21
52 ,ATTRIBUTE22
53 ,ATTRIBUTE23
54 ,ATTRIBUTE24
55 ,ATTRIBUTE25
56 ,ATTRIBUTE26
57 ,ATTRIBUTE27
58 ,ATTRIBUTE28
59 ,ATTRIBUTE29
60 ,ATTRIBUTE30
61 ,CREATION_DATE
62 ,CREATED_BY
63
64 INTO
65 l_safety_association_rec.SAFETY_ASSOCIATION_ID
66 ,l_safety_association_rec.SOURCE_ID
67 ,l_safety_association_rec.TARGET_REF_ID
68 ,l_safety_association_rec.ASSOCIATION_TYPE
69 ,l_safety_association_rec.ORGANIZATION_ID
70 ,l_safety_association_rec.EFFECTIVE_FROM
71 ,l_safety_association_rec.EFFECTIVE_TO
72 ,l_safety_association_rec.ENABLED
73 ,l_safety_association_rec.COMMENTS
74 ,l_safety_association_rec.ATTRIBUTE_CATEGORY
75 ,l_safety_association_rec.ATTRIBUTE1
76 ,l_safety_association_rec.ATTRIBUTE2
77 ,l_safety_association_rec.ATTRIBUTE3
78 ,l_safety_association_rec.ATTRIBUTE4
79 ,l_safety_association_rec.ATTRIBUTE5
80 ,l_safety_association_rec.ATTRIBUTE6
81 ,l_safety_association_rec.ATTRIBUTE7
82 ,l_safety_association_rec.ATTRIBUTE8
83 ,l_safety_association_rec.ATTRIBUTE9
84 ,l_safety_association_rec.ATTRIBUTE10
85 ,l_safety_association_rec.ATTRIBUTE11
86 ,l_safety_association_rec.ATTRIBUTE12
87 ,l_safety_association_rec.ATTRIBUTE13
88 ,l_safety_association_rec.ATTRIBUTE14
89 ,l_safety_association_rec.ATTRIBUTE15
90 ,l_safety_association_rec.ATTRIBUTE16
91 ,l_safety_association_rec.ATTRIBUTE17
92 ,l_safety_association_rec.ATTRIBUTE18
93 ,l_safety_association_rec.ATTRIBUTE19
94 ,l_safety_association_rec.ATTRIBUTE20
95 ,l_safety_association_rec.ATTRIBUTE21
96 ,l_safety_association_rec.ATTRIBUTE22
97 ,l_safety_association_rec.ATTRIBUTE23
98 ,l_safety_association_rec.ATTRIBUTE24
99 ,l_safety_association_rec.ATTRIBUTE25
100 ,l_safety_association_rec.ATTRIBUTE26
101 ,l_safety_association_rec.ATTRIBUTE27
102 ,l_safety_association_rec.ATTRIBUTE28
103 ,l_safety_association_rec.ATTRIBUTE28
104 ,l_safety_association_rec.ATTRIBUTE30
105 ,l_safety_association_rec.CREATION_DATE
106 ,l_safety_association_rec.CREATED_BY
107
108 FROM EAM_SAFETY_ASSOCIATIONS esa
109 WHERE esa.SOURCE_ID = p_source_id
110 AND esa.organization_id = p_organization_id
111 AND esa.ASSOCIATION_TYPE = p_association_type;
112
113 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_FOUND;
114 x_safety_association_rec := l_safety_association_rec;
115
116 EXCEPTION
117 WHEN NO_DATA_FOUND THEN
118 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_NOT_FOUND;
119 x_safety_association_rec := l_safety_association_rec;
120
121 WHEN OTHERS THEN
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
123 x_safety_association_rec := l_safety_association_rec;
124
125
126 END QUERY_SAFFETY_ASSOCIATION_ROWS;
127
128
129
130
131
132 /********************************************************************
133 * Procedure : INSERT_ SAFFETY_ASSOCIATION _ROW
134 * Purpose : Procedure will perfrom an insert into the table
135 *********************************************************************/
136
137 PROCEDURE INSERT_SAFFETY_ASSOCIATION_ROW
138 ( p_safety_association_rec IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type
139 , p_association_type IN NUMBER
140 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
141 , x_return_Status OUT NOCOPY VARCHAR2
142 )IS
143
144 -- l_organization_id NUMBER;
145
146 BEGIN
147 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Inserting Safety Association Row'); END IF;
148
149 INSERT INTO EAM_SAFETY_ASSOCIATIONS(
150 SAFETY_ASSOCIATION_ID
151 ,SOURCE_ID
152 ,TARGET_REF_ID
153 ,ASSOCIATION_TYPE
154 ,ORGANIZATION_ID
155 ,EFFECTIVE_FROM
156 ,EFFECTIVE_TO
157 ,ENABLED
158 ,COMMENTS
159 ,ATTRIBUTE_CATEGORY
160 ,ATTRIBUTE1
161 ,ATTRIBUTE2
162 ,ATTRIBUTE3
163 ,ATTRIBUTE4
164 ,ATTRIBUTE5
165 ,ATTRIBUTE6
166 ,ATTRIBUTE7
167 ,ATTRIBUTE8
168 ,ATTRIBUTE9
169 ,ATTRIBUTE10
170 ,ATTRIBUTE11
171 ,ATTRIBUTE12
172 ,ATTRIBUTE13
173 ,ATTRIBUTE14
174 ,ATTRIBUTE15
175 ,ATTRIBUTE16
176 ,ATTRIBUTE17
177 ,ATTRIBUTE18
178 ,ATTRIBUTE19
179 ,ATTRIBUTE20
180 ,ATTRIBUTE21
181 ,ATTRIBUTE22
182 ,ATTRIBUTE23
183 ,ATTRIBUTE24
184 ,ATTRIBUTE25
185 ,ATTRIBUTE26
186 ,ATTRIBUTE27
187 ,ATTRIBUTE28
188 ,ATTRIBUTE29
189 ,ATTRIBUTE30
190 ,LAST_UPDATE_DATE
191 ,LAST_UPDATED_BY
192 ,CREATION_DATE
193 ,CREATED_BY
194 ,LAST_UPDATE_LOGIN )
195 VALUES
196 ( p_safety_association_rec.SAFETY_ASSOCIATION_ID
197 ,p_safety_association_rec.SOURCE_ID
198 ,p_safety_association_rec.TARGET_REF_ID
199 ,p_safety_association_rec.ASSOCIATION_TYPE
200 ,p_safety_association_rec.ORGANIZATION_ID
201 ,p_safety_association_rec.EFFECTIVE_FROM
202 ,p_safety_association_rec.EFFECTIVE_TO
203 ,p_safety_association_rec.ENABLED
204 ,p_safety_association_rec.COMMENTS
205 ,p_safety_association_rec.ATTRIBUTE_CATEGORY
206 ,p_safety_association_rec.ATTRIBUTE1
207 ,p_safety_association_rec.ATTRIBUTE2
208 ,p_safety_association_rec.ATTRIBUTE3
209 ,p_safety_association_rec.ATTRIBUTE4
210 ,p_safety_association_rec.ATTRIBUTE5
211 ,p_safety_association_rec.ATTRIBUTE6
212 ,p_safety_association_rec.ATTRIBUTE7
213 ,p_safety_association_rec.ATTRIBUTE8
214 ,p_safety_association_rec.ATTRIBUTE9
215 ,p_safety_association_rec.ATTRIBUTE10
216 ,p_safety_association_rec.ATTRIBUTE11
217 ,p_safety_association_rec.ATTRIBUTE12
218 ,p_safety_association_rec.ATTRIBUTE13
219 ,p_safety_association_rec.ATTRIBUTE14
220 ,p_safety_association_rec.ATTRIBUTE15
221 ,p_safety_association_rec.ATTRIBUTE16
222 ,p_safety_association_rec.ATTRIBUTE17
223 ,p_safety_association_rec.ATTRIBUTE18
224 ,p_safety_association_rec.ATTRIBUTE19
225 ,p_safety_association_rec.ATTRIBUTE20
226 ,p_safety_association_rec.ATTRIBUTE21
227 ,p_safety_association_rec.ATTRIBUTE22
228 ,p_safety_association_rec.ATTRIBUTE23
229 ,p_safety_association_rec.ATTRIBUTE24
230 ,p_safety_association_rec.ATTRIBUTE25
231 ,p_safety_association_rec.ATTRIBUTE26
232 ,p_safety_association_rec.ATTRIBUTE27
233 ,p_safety_association_rec.ATTRIBUTE28
234 ,p_safety_association_rec.ATTRIBUTE29
235 ,p_safety_association_rec.ATTRIBUTE30
236 ,SYSDATE
237 ,FND_GLOBAL.user_id
238 ,SYSDATE
239 ,FND_GLOBAL.user_id
240 ,FND_GLOBAL.login_id);
241
242 x_return_status := FND_API.G_RET_STS_SUCCESS;
243 EXCEPTION
244 WHEN OTHERS THEN
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
246
247 END INSERT_SAFFETY_ASSOCIATION_ROW;
248
249
250
251
252 /********************************************************************
253 * Procedure : UPDATE_ SAFFETY_ASSOCIATION _ROW
254 * Purpose : Procedure will perform an update on the table
255 *********************************************************************/
256
257
258 PROCEDURE UPDATE_SAFFETY_ASSOCIATION_ROW
259 ( p_safety_association_rec IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type
260 , p_association_type IN NUMBER
261 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
262 , x_return_Status OUT NOCOPY VARCHAR2
263 )IS
264
265
266 BEGIN
267 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Updating Safety Association Row'); END IF;
268
269 UPDATE EAM_SAFETY_ASSOCIATIONS
270 SET
271 TARGET_REF_ID =p_safety_association_rec.TARGET_REF_ID
272 ,ASSOCIATION_TYPE =p_safety_association_rec.ASSOCIATION_TYPE
273 ,COMMENTS =p_safety_association_rec.COMMENTS
274 ,EFFECTIVE_FROM =p_safety_association_rec.EFFECTIVE_FROM
275 ,EFFECTIVE_TO =p_safety_association_rec.EFFECTIVE_TO
276 ,ATTRIBUTE_CATEGORY =p_safety_association_rec.ATTRIBUTE_CATEGORY
277 ,ATTRIBUTE1 =p_safety_association_rec.ATTRIBUTE1
278 ,ATTRIBUTE2 =p_safety_association_rec.ATTRIBUTE2
279 ,ATTRIBUTE3 =p_safety_association_rec.ATTRIBUTE3
280 ,ATTRIBUTE4 =p_safety_association_rec.ATTRIBUTE4
281 ,ATTRIBUTE5 =p_safety_association_rec.ATTRIBUTE5
282 ,ATTRIBUTE6 =p_safety_association_rec.ATTRIBUTE6
283 ,ATTRIBUTE7 =p_safety_association_rec.ATTRIBUTE7
284 ,ATTRIBUTE8 =p_safety_association_rec.ATTRIBUTE8
285 ,ATTRIBUTE9 =p_safety_association_rec.ATTRIBUTE9
286 ,ATTRIBUTE10 =p_safety_association_rec.ATTRIBUTE10
287 ,ATTRIBUTE11 =p_safety_association_rec.ATTRIBUTE11
288 ,ATTRIBUTE12 =p_safety_association_rec.ATTRIBUTE12
289 ,ATTRIBUTE13 =p_safety_association_rec.ATTRIBUTE13
290 ,ATTRIBUTE14 =p_safety_association_rec.ATTRIBUTE14
291 ,ATTRIBUTE15 =p_safety_association_rec.ATTRIBUTE15
292 ,ATTRIBUTE16 =p_safety_association_rec.ATTRIBUTE16
293 ,ATTRIBUTE17 =p_safety_association_rec.ATTRIBUTE17
294 ,ATTRIBUTE18 =p_safety_association_rec.ATTRIBUTE18
295 ,ATTRIBUTE19 =p_safety_association_rec.ATTRIBUTE19
296 ,ATTRIBUTE20 =p_safety_association_rec.ATTRIBUTE20
297 ,ATTRIBUTE21 =p_safety_association_rec.ATTRIBUTE21
298 ,ATTRIBUTE22 =p_safety_association_rec.ATTRIBUTE22
299 ,ATTRIBUTE23 =p_safety_association_rec.ATTRIBUTE23
300 ,ATTRIBUTE24 =p_safety_association_rec.ATTRIBUTE24
301 ,ATTRIBUTE25 =p_safety_association_rec.ATTRIBUTE25
302 ,ATTRIBUTE26 =p_safety_association_rec.ATTRIBUTE26
303 ,ATTRIBUTE27 =p_safety_association_rec.ATTRIBUTE27
304 ,ATTRIBUTE28 =p_safety_association_rec.ATTRIBUTE28
305 ,ATTRIBUTE29 =p_safety_association_rec.ATTRIBUTE29
306 ,ATTRIBUTE30 =p_safety_association_rec.ATTRIBUTE30
307 ,LAST_UPDATE_DATE =SYSDATE
308 ,LAST_UPDATED_BY =FND_GLOBAL.user_id
309 ,LAST_UPDATE_LOGIN =FND_GLOBAL.login_id
310
311 WHERE SAFETY_ASSOCIATION_ID=p_safety_association_rec.SAFETY_ASSOCIATION_ID
312 AND ASSOCIATION_TYPE=p_safety_association_rec.ASSOCIATION_TYPE;
313
314 x_return_status := FND_API.G_RET_STS_SUCCESS;
315
316 EXCEPTION
317 WHEN OTHERS THEN
318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
319
320 END UPDATE_SAFFETY_ASSOCIATION_ROW;
321
322
323
324
325 /********************************************************************
326 * Procedure : DELETE SAFFETY_ASSOCIATION _ROW
327 * Purpose : This will perform delete on the table
328 *********************************************************************/
329
330 PROCEDURE DELETE_SAFFETY_ASSOCIATION_ROW
331 ( p_safety_association_rec IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type
332 , p_association_type IN NUMBER
333 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
334 , x_return_Status OUT NOCOPY VARCHAR2
335 )IS
336 BEGIN
337 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Deleting Safety Association Row'); END IF;
338
339 -- Deleting attachments before deleting any association
340
341 FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS(
342 X_entity_name => 'EAM_SAFETY_ASSOC',
343 X_pk1_value => p_safety_association_rec.ORGANIZATION_ID,
344 X_pk2_value => p_safety_association_rec.SOURCE_ID,
345 X_pk3_value => p_safety_association_rec.TARGET_REF_ID,
346 X_pk4_value => p_safety_association_rec.ASSOCIATION_TYPE,
347 X_pk5_value => null,
348 X_delete_document_flag => 'Y'
349 );
350
351 DELETE FROM EAM_SAFETY_ASSOCIATIONS
352 WHERE SAFETY_ASSOCIATION_ID =p_safety_association_rec.SAFETY_ASSOCIATION_ID
353 AND ASSOCIATION_TYPE = p_association_type;
354
355 x_return_status := FND_API.G_RET_STS_SUCCESS;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 x_return_status := FND_API.G_RET_STS_ERROR;
360
361 END DELETE_SAFFETY_ASSOCIATION_ROW;
362
363
364
365 /********************************************************************
366 * Procedure : WRITE SAFFETY_ASSOCIATION _ROW
367 * Purpose : This is the only procedure that the user will have
368 access to when he/she needs to perform any kind of writes to the table.
369 *********************************************************************/
370
371 PROCEDURE WRITE_SAFFETY_ASSOCIATION_ROW
372 ( p_safety_association_rec IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_rec_type
373 , p_association_type IN NUMBER
374 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
375 , x_return_Status OUT NOCOPY VARCHAR2
376 ) IS
377
378 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
379 BEGIN
380 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Processing Safety Association Row'); END IF;
381
382 IF p_safety_association_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
383 THEN
384 INSERT_SAFFETY_ASSOCIATION_ROW
385 ( p_safety_association_rec => p_safety_association_rec
386 , p_association_type => p_association_type
387 , x_mesg_token_Tbl => x_mesg_token_Tbl
388 , x_return_Status => l_return_status
389 );
390 ELSIF p_safety_association_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
391 THEN
392 UPDATE_SAFFETY_ASSOCIATION_ROW
393 ( p_safety_association_rec => p_safety_association_rec
394 , p_association_type => p_association_type
395 , x_mesg_token_Tbl => x_mesg_token_Tbl
396 , x_return_Status => l_return_status
397 );
398 ELSIF p_safety_association_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE
399 THEN
400 DELETE_SAFFETY_ASSOCIATION_ROW
401 ( p_safety_association_rec => p_safety_association_rec
402 , p_association_type => p_association_type
403 , x_mesg_token_Tbl => x_mesg_token_Tbl
404 , x_return_Status => l_return_status
405 );
406 END IF;
407
408 x_return_status := l_return_status;
409
410 END WRITE_SAFFETY_ASSOCIATION_ROW;
411
412 END EAM_SAFETY_UTILITY_PVT;