[Home] [Help]
PACKAGE BODY: APPS.GMO_OPER_CERT_TRANS_DBL
Source
1 PACKAGE BODY gmo_oper_cert_trans_dbl AS
2 /* $Header: GMOVGOCB.pls 120.1 2007/06/21 06:14:59 rvsingh noship $ */
3
4 /* Global Variables */
5 g_table_name VARCHAR2 (80) DEFAULT 'gmo_operator_cert_trans';
6
7 /*============================================================================
8 | Copyright (c) 2001 Oracle Corporation
9 | TVP, Reading
10 | All rights reserved
11 =============================================================================
12 | FILENAME
13 | GMOVGOCB.pls
14 |
15 | DESCRIPTION
16 | |
17 |
18 |
19 | NOTES
20 |
21 | HISTORY
22 | 12-MAR-07 Pawan Kumar Created
23 |
24 | - insert_row
25 | - fetch_row
26 | - update_row
27 | - lock_row
28 | - Delete_row
29 |
30 |
31 =============================================================================
32 */
33
34 /* Api start of comments
35 +============================================================================
36 | FUNCTION NAME
37 | insert_row
38 |
39 | TYPE
40 | Private
41 | USAGE
42 | Insert_Row will insert a row in gmo_operator_cert_trans
43 |
44 |
45 | DESCRIPTION
46 | Insert_Row will insert a row in gmo_operator_cert_trans
47 |
48 |
49 |
50 | PARAMETERS
51 | p_oper_cert_trans IN gmo_operator_cert_trans%ROWTYPE
52 | x_oper_cert_trans IN OUT NOCOPY gmo_operator_cert_trans%ROWTYPE
53 |
54 | RETURNS
55 | BOOLEAN
56 | HISTORY
57 | 12-MAR-07 Pawan Kumar Created
58 |
59 |
60 |
61 +=============================================================================
62 Api end of comments
63 */
64
65 PROCEDURE INSERT_ROW (
66 p_operator_CERTIFICATE_ID IN OUT NOCOPY NUMBER
67 ,P_HEADER_ID IN NUMBER
68 ,P_TRANSACTION_ID IN VARCHAR2
69 ,P_USER_ID IN NUMBER
70 ,P_comments IN VARCHAR2
71 ,P_OVERRIDER_ID IN NUMBER
72 ,P_User_key_label_product IN VARCHAR2
73 ,P_User_key_label_token IN VARCHAR2
74 ,P_User_key_value IN VARCHAR2
75 ,P_Erecord_id IN NUMBER
76 ,P_Trans_object_id IN NUMBER
77 ,P_STATUS IN VARCHAR2
78 ,P_event_name IN VARCHAR2
79 ,P_event_key IN VARCHAR2
80 ,P_CREATION_DATE IN DATE
81 ,P_CREATED_BY IN NUMBER
82 ,P_LAST_UPDATE_DATE IN DATE
83 ,P_LAST_UPDATED_BY IN NUMBER
84 ,P_LAST_UPDATE_LOGIN IN NUMBER
85 ,x_return_Status OUT NOCOPY VARCHAR2 ) IS
86
87
88 BEGIN
89 -- Initially let us assign the return status to success
90 x_return_status := fnd_api.g_ret_sts_success;
91
92
93
94
95 INSERT INTO gmo_operator_cert_trans
96 (operator_CERTIFICATE_ID
97 ,HEADER_ID
98 ,TRANSACTION_ID
99 ,USER_ID
100 ,comments
101 ,OVERRIDER_ID
102 ,User_key_label_product
103 ,User_key_label_token
104 ,User_key_value
105 ,Erecord_id
106 ,Trans_object_id
107 ,STATUS
108 ,EVENT_NAME
109 ,EVENT_KEY
110 ,CREATION_DATE
111 ,CREATED_BY
112 ,LAST_UPDATE_DATE
113 ,LAST_UPDATED_BY
114 ,LAST_UPDATE_LOGIN )
115 VALUES ( gmo_oc_object_trans_s.nextval
116 ,p_HEADER_ID
117 ,p_TRANSACTION_ID
118 ,p_USER_ID
119 ,p_comments
120 ,p_OVERRIDER_ID
121 ,p_User_key_label_product
122 ,p_User_key_label_token
123 ,p_User_key_value
124 ,p_Erecord_id
125 ,p_Trans_object_id
126 ,p_STATUS
127 ,p_EVENT_NAME
128 ,p_event_KEY
129 ,p_CREATION_DATE
130 ,p_CREATED_BY
131 ,p_LAST_UPDATE_DATE
132 ,p_LAST_UPDATED_BY
133 ,p_LAST_UPDATE_LOGIN
134
135 )
136 RETURNING operator_CERTIFICATE_ID
137 INTO p_operator_CERTIFICATE_ID ;
138
139 IF SQL%NOTFOUND THEN
140 x_return_status := fnd_api.g_ret_sts_error;
141 END IF;
142
143 EXCEPTION
144 WHEN FND_API.g_exc_error THEN
145 --dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
146 x_return_status := fnd_api.g_ret_sts_error;
147 WHEN OTHERS THEN
148 x_return_status := fnd_api.g_ret_sts_unexp_error;
149 --dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
150
151 END insert_row;
152
153
154 /* Api start of comments
155 +============================================================================
156 | FUNCTION NAME
157 | delete_row
158 |
159 | TYPE
160 | Private
161 | USAGE
162 | Delete_Row will delete a row in gmo_operator_cert_trans
163 |
164 |
165 | DESCRIPTION
166 | Delete_Row will delete a row in gmo_operator_cert_trans
167 |
168 |
169 |
170 | PARAMETERS
171 | p_oper_cert_trans IN gmo_operator_cert_trans%ROWTYPE
172 |
173 | RETURNS
174 | BOOLEAN
175 | HISTORY
176 | 12-MAR-07 Pawan Kumar Created
177 |
178 |
179 |
180 +=============================================================================
181 Api end of comments
182 */
183
184 PROCEDURE delete_row (p_oper_cert_id IN NUMBER
185 ,x_return_Status OUT NOCOPY VARCHAR2)
186
187 IS
188 l_dummy NUMBER := 0;
189 locked_by_other_user EXCEPTION;
190 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
191
192 BEGIN
193 -- Initially let us assign the return status to success
194 x_return_status := fnd_api.g_ret_sts_success;
195 IF p_oper_cert_ID IS NOT NULL THEN
196 SELECT 1
197 INTO l_dummy
198 FROM gmo_operator_cert_trans
199 WHERE operator_CERTIFICATE_ID = p_oper_cert_ID
200 FOR UPDATE NOWAIT;
201
202 DELETE FROM gmo_operator_cert_trans
203 WHERE operator_CERTIFICATE_ID = p_oper_cert_ID ;
204
205
206 IF (SQL%NOTFOUND) THEN
207 RAISE NO_DATA_FOUND;
208 END IF;
209 END IF;
210 EXCEPTION
211 WHEN NO_DATA_FOUND THEN
212
213 x_return_status := fnd_api.g_ret_sts_error;
214
215 WHEN OTHERS THEN
216 --dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
217 x_return_status := fnd_api.g_ret_sts_unexp_error;
218 END delete_row;
219
220 /* Api start of comments
221 +============================================================================
222 | FUNCTION NAME
223 | update_row
224 |
225 | TYPE
226 | Private
227 | USAGE
228 | Update_Row will update a row in gmo_operator_cert_trans
229 |
230 |
231 | DESCRIPTION
232 | Update_Row will update a row in gmo_operator_cert_trans
233 |
234 |
235 |
236 | PARAMETERS
237 | p_oper_cert_trans IN gmo_operator_cert_trans%ROWTYPE
238 |
239 | RETURNS
240 | BOOLEAN
241 | HISTORY
242 | 12-MAR-07 Pawan Kumar Created
243 |
244 |
245 |
246 +=============================================================================
247 Api end of comments
248 */
249 PROCEDURE UPDATE_ROW (
250 P_operator_CERTIFICATE_ID IN NUMBER
251 ,P_HEADER_ID IN NUMBER
252 ,P_TRANSACTION_ID IN VARCHAR2
253 ,P_USER_ID IN NUMBER
254 ,P_comments IN VARCHAR2
255 ,P_OVERRIDER_ID IN NUMBER
256 ,P_User_key_label_product IN VARCHAR2
257 ,P_User_key_label_token IN VARCHAR2
258 ,P_User_key_value IN VARCHAR2
259 ,P_Erecord_id IN NUMBER
260 ,P_Trans_object_id IN NUMBER
261 ,P_STATUS IN VARCHAR2
262 ,P_event_name IN VARCHAR2
263 ,p_event_key IN VARCHAR2
264 ,P_CREATION_DATE IN DATE
265 ,P_CREATED_BY IN NUMBER
266 ,P_LAST_UPDATE_DATE IN DATE
267 ,P_LAST_UPDATED_BY IN NUMBER
268 ,P_LAST_UPDATE_LOGIN IN NUMBER
269 ,x_return_Status OUT NOCOPY VARCHAR2 )
270
271 IS
272 l_dummy NUMBER := 0;
273 locked_by_other_user EXCEPTION;
274 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
275
276 BEGIN
277 x_return_status := fnd_api.g_ret_sts_success;
278
279 IF p_operator_CERTIFICATE_ID IS NOT NULL THEN
280 SELECT 1
281 INTO l_dummy
282 FROM gmo_operator_cert_trans
283 WHERE operator_CERTIFICATE_ID = p_operator_CERTIFICATE_ID
284 FOR UPDATE NOWAIT;
285
286 UPDATE gmo_operator_cert_trans
287 SET
288 TRANSACTION_ID = P_TRANSACTION_ID
289 ,comments = P_comments
290 ,OVERRIDER_ID = P_OVERRIDER_ID
291 ,User_key_label_product = P_User_key_label_product
292 ,User_key_label_token = P_User_key_label_token
293 ,User_key_value = P_User_key_value
294 ,Erecord_id = P_Erecord_id
295 ,Trans_object_id = P_Trans_object_id
296 ,STATUS = P_STATUS
297 ,event_name = P_event_name
298 ,event_key = P_event_key
299
300 ,LAST_UPDATE_DATE = P_LAST_UPDATE_DATE
301 ,LAST_UPDATED_BY = P_LAST_UPDATED_BY
302 ,LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
303 WHERE operator_CERTIFICATE_ID = p_operator_CERTIFICATE_ID;
304 END IF;
305 IF (SQL%NOTFOUND) THEN
306 RAISE NO_DATA_FOUND;
307 END IF;
308 EXCEPTION
309 WHEN NO_DATA_FOUND THEN
310 x_return_status := fnd_api.g_ret_sts_error;
311
312 WHEN OTHERS THEN
313 --dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
314 x_return_status := fnd_api.g_ret_sts_unexp_error;
315
316 END update_row;
317
318 /* Api start of comments
319 +============================================================================
320 | FUNCTION NAME
321 | lock_row
322 |
323 | TYPE
324 | Private
325 | USAGE
326 | Lock_Row will lock a row in gmo_operator_cert_trans
327 |
328 |
329 | DESCRIPTION
330 | Lock_Row will lock a row in gmo_operator_cert_trans
331 |
332 |
333 |
334 | PARAMETERS
335 | p_oper_cert_trans IN gmo_operator_cert_trans%ROWTYPE
336 |
337 | RETURNS
338 | BOOLEAN
339 | HISTORY
340 | 12-MAR-07 Pawan Kumar Created
341 |
342 |
343 |
344 +=============================================================================
345 Api end of comments
346 */
347 PROCEDURE lock_row (p_oper_cert_id IN NUMBER
348 ,x_return_Status OUT NOCOPY VARCHAR2)
349
350 IS
351 l_dummy NUMBER;
352 BEGIN
353 IF p_oper_cert_ID IS NOT NULL THEN
354 SELECT 1
355 INTO l_dummy
356 FROM gmo_operator_cert_trans
357 WHERE operator_CERTIFICATE_ID = p_oper_cert_ID
358 FOR UPDATE NOWAIT;
359 END IF;
360
361
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 x_return_status := fnd_api.g_ret_sts_error;
365 WHEN app_exception.record_lock_exception THEN
366 x_return_status := fnd_api.g_ret_sts_error;
367
368 WHEN OTHERS THEN
369 --dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
370 x_return_status := fnd_api.g_ret_sts_unexp_error;
371 END lock_row;
372 END gmo_oper_cert_trans_dbl;