[Home] [Help]
PACKAGE BODY: APPS.GME_BATCH_HISTORY_DBL
Source
1 PACKAGE BODY gme_batch_history_dbl AS
2 /* $Header: GMEVGHSB.pls 120.1 2005/06/03 13:45:15 appldev $ */
3
4 /* Global Variables */
5 g_table_name VARCHAR2 (80) DEFAULT 'GME_BATCH_HISTORY';
6
7 /*============================================================================
8 | Copyright (c) 2001 Oracle Corporation
9 | TVP, Reading
10 | All rights reserved
11 =============================================================================
12 | FILENAME
13 | GMEVGHSB.pls
14 |
15 | DESCRIPTION
16 |
17 |
18 |
19 |
20 | NOTES
21 |
22 | HISTORY
23 | 12-MAR-01 Thomas Daniel Created
24 |
25 | - insert_row
26 | - fetch_row
27 | - update_row
28 | - lock_row
29 | - Delete_row
30 |
31 |
32 =============================================================================
33 */
34
35 /* Api start of comments
36 +============================================================================
37 | FUNCTION NAME
38 | insert_row
39 |
40 | TYPE
41 | Private
42 | USAGE
43 | Insert_Row will insert a row in gme_batch_history
44 |
45 |
46 | DESCRIPTION
47 | Insert_Row will insert a row in gme_batch_history
48 |
49 |
50 |
51 | PARAMETERS
52 | p_batch_history IN gme_batch_history%ROWTYPE
53 | x_batch_history IN OUT NOCOPY gme_batch_history%ROWTYPE
54 |
55 | RETURNS
56 | BOOLEAN
57 | HISTORY
58 | 12-MAR-01 Thomas Daniel Created
59 |
60 |
61 |
62 +=============================================================================
63 Api end of comments
64 */
65 FUNCTION insert_row (
66 p_batch_history IN gme_batch_history%ROWTYPE
67 ,x_batch_history IN OUT NOCOPY gme_batch_history%ROWTYPE)
68 RETURN BOOLEAN
69 IS
70 BEGIN
71 x_batch_history := p_batch_history;
72
73 INSERT INTO gme_batch_history
74 (event_id, batch_id
75 ,orig_status, new_status
76 ,orig_wip_whse
77 ,new_wip_whse
78 ,gl_posted_ind
79 ,last_updated_by, last_update_login
80 ,created_by, creation_date
81 ,last_update_date, program_id
82 ,request_id
83 ,program_update_date
84 ,program_application_id)
85 VALUES (gem5_batch_event_id_s.NEXTVAL, x_batch_history.batch_id
86 ,x_batch_history.orig_status, x_batch_history.new_status
87 ,x_batch_history.orig_wip_whse
88 ,x_batch_history.new_wip_whse
89 ,x_batch_history.gl_posted_ind
90 ,gme_common_pvt.g_user_ident, gme_common_pvt.g_login_id
91 ,gme_common_pvt.g_user_ident, gme_common_pvt.g_timestamp
92 ,gme_common_pvt.g_timestamp, x_batch_history.program_id
93 ,x_batch_history.request_id
94 ,x_batch_history.program_update_date
95 ,x_batch_history.program_application_id)
96 RETURNING event_id
97 INTO x_batch_history.event_id;
98
99 IF SQL%FOUND THEN
100 RETURN TRUE;
101 ELSE
102 RETURN FALSE;
103 END IF;
104 EXCEPTION
105 WHEN OTHERS THEN
106 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
107 ,SQLERRM);
108 RETURN FALSE;
109 END insert_row;
110
111 /* Api start of comments
112 +============================================================================
113 | FUNCTION NAME
114 | fetch_row
115 |
116 | TYPE
117 | Private
118 | USAGE
119 | Fetch_Row will fetch a row in gme_batch_history
120 |
121 |
122 | DESCRIPTION
123 | Fetch_Row will fetch a row in gme_batch_history
124 |
125 |
126 |
127 | PARAMETERS
128 | p_batch_history IN gme_batch_history%ROWTYPE
129 | x_batch_history IN OUT NOCOPY gme_batch_history%ROWTYPE
130 |
131 | RETURNS
132 | BOOLEAN
133 | HISTORY
134 | 12-MAR-01 Thomas Daniel Created
135 |
136 |
137 |
138 +=============================================================================
139 Api end of comments
140 */
141 FUNCTION fetch_row (
142 p_batch_history IN gme_batch_history%ROWTYPE
143 ,x_batch_history IN OUT NOCOPY gme_batch_history%ROWTYPE)
144 RETURN BOOLEAN
145 IS
146 BEGIN
147 IF p_batch_history.event_id IS NOT NULL THEN
148 SELECT event_id, batch_id
149 ,orig_status, new_status
150 ,orig_wip_whse, new_wip_whse
151 ,gl_posted_ind
152 ,last_updated_by
153 ,last_update_login
154 ,created_by, creation_date
155 ,last_update_date
156 ,program_id, request_id
157 ,program_update_date
158 ,program_application_id
159 INTO x_batch_history.event_id, x_batch_history.batch_id
160 ,x_batch_history.orig_status, x_batch_history.new_status
161 ,x_batch_history.orig_wip_whse, x_batch_history.new_wip_whse
162 ,x_batch_history.gl_posted_ind
163 ,x_batch_history.last_updated_by
164 ,x_batch_history.last_update_login
165 ,x_batch_history.created_by, x_batch_history.creation_date
166 ,x_batch_history.last_update_date
167 ,x_batch_history.program_id, x_batch_history.request_id
168 ,x_batch_history.program_update_date
169 ,x_batch_history.program_application_id
170 FROM gme_batch_history
171 WHERE event_id = p_batch_history.event_id;
172 ELSE
173 gme_common_pvt.log_message ('GME_NO_KEYS'
174 ,'TABLE_NAME'
175 ,g_table_name);
176 RETURN FALSE;
177 END IF;
178
179 RETURN TRUE;
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
183 ,'TABLE_NAME'
184 ,g_table_name);
185 RETURN FALSE;
186 WHEN OTHERS THEN
187 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
188 ,SQLERRM);
189 RETURN FALSE;
190 END fetch_row;
191
192 /* Api start of comments
193 +============================================================================
194 | FUNCTION NAME
195 | delete_row
196 |
197 | TYPE
198 | Private
199 | USAGE
200 | Delete_Row will delete a row in gme_batch_history
201 |
202 |
203 | DESCRIPTION
204 | Delete_Row will delete a row in gme_batch_history
205 |
206 |
207 |
208 | PARAMETERS
209 | p_batch_history IN gme_batch_history%ROWTYPE
210 |
211 | RETURNS
212 | BOOLEAN
213 | HISTORY
214 | 12-MAR-01 Thomas Daniel Created
215 | 26-AUG-01 Bharati Satpute Bug 2404126
216 | Added error message 'GME_RECORD_CHANGED'
217 |
218 |
219 +=============================================================================
220 Api end of comments
221 */
222 FUNCTION delete_row (p_batch_history IN gme_batch_history%ROWTYPE)
223 RETURN BOOLEAN
224 IS
225 l_dummy NUMBER := 0;
226 locked_by_other_user EXCEPTION;
227 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
228 BEGIN
229 IF p_batch_history.event_id IS NOT NULL THEN
230 SELECT 1
231 INTO l_dummy
232 FROM gme_batch_history
233 WHERE event_id = p_batch_history.event_id
234 FOR UPDATE NOWAIT;
235
236 DELETE FROM gme_batch_history
237 WHERE event_id = p_batch_history.event_id;
238 ELSE
239 gme_common_pvt.log_message ('GME_NO_KEYS'
240 ,'TABLE_NAME'
241 ,g_table_name);
242 RETURN FALSE;
243 END IF;
244
245 IF SQL%FOUND THEN
246 RETURN TRUE;
247 ELSE
248 IF l_dummy = 0 THEN
249 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
250 ,'TABLE_NAME'
251 ,g_table_name);
252 ELSE
253 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
254 ,'TABLE_NAME'
255 ,g_table_name);
256 END IF;
257
258 RETURN FALSE;
259 END IF;
260 EXCEPTION
261 WHEN NO_DATA_FOUND THEN
262 IF l_dummy = 0 THEN
263 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
264 ,'TABLE_NAME'
265 ,g_table_name);
266 ELSE
267 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
268 ,'TABLE_NAME'
269 ,g_table_name);
270 END IF;
271
272 RETURN FALSE;
273 WHEN locked_by_other_user THEN
274 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
275 ,'TABLE_NAME'
276 ,g_table_name
277 ,'RECORD'
278 ,'Batch'
279 ,'KEY'
280 ,TO_CHAR (p_batch_history.batch_id) );
281 RETURN FALSE;
282 WHEN OTHERS THEN
283 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
284 ,SQLERRM);
285 RETURN FALSE;
286 END delete_row;
287
288 /* Api start of comments
289 +============================================================================
290 | FUNCTION NAME
291 | update_row
292 |
293 | TYPE
294 | Private
295 | USAGE
296 | Update_Row will update a row in gme_batch_history
297 |
298 |
299 | DESCRIPTION
300 | Update_Row will update a row in gme_batch_history
301 |
302 |
303 |
304 | PARAMETERS
305 | p_batch_history IN gme_batch_history%ROWTYPE
306 |
307 | RETURNS
308 | BOOLEAN
309 | HISTORY
310 | 12-MAR-01 Thomas Daniel Created
311 | 26-AUG-02 Bharati Satpute Bug2404126
312 | Added Error message 'GME_RECORD_CHANGED'
313 |
314 |
315 +=============================================================================
316 Api end of comments
317 */
318 FUNCTION update_row (p_batch_history IN gme_batch_history%ROWTYPE)
319 RETURN BOOLEAN
320 IS
321 l_dummy NUMBER := 0;
322 locked_by_other_user EXCEPTION;
323 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
324 BEGIN
325 IF p_batch_history.event_id IS NOT NULL THEN
326 SELECT 1
327 INTO l_dummy
328 FROM gme_batch_history
329 WHERE event_id = p_batch_history.event_id
330 FOR UPDATE NOWAIT;
331
332 UPDATE gme_batch_history
333 SET batch_id = p_batch_history.batch_id
334 ,orig_status = p_batch_history.orig_status
335 ,new_status = p_batch_history.new_status
336 ,orig_wip_whse = p_batch_history.orig_wip_whse
337 ,new_wip_whse = p_batch_history.new_wip_whse
338 ,gl_posted_ind = p_batch_history.gl_posted_ind
339 ,last_updated_by = gme_common_pvt.g_user_ident
340 ,last_update_login = gme_common_pvt.g_login_id
341 ,last_update_date = gme_common_pvt.g_timestamp
342 ,program_id = p_batch_history.program_id
343 ,request_id = p_batch_history.request_id
344 ,program_update_date = p_batch_history.program_update_date
345 ,program_application_id =
346 p_batch_history.program_application_id
347 WHERE event_id = p_batch_history.event_id
348 AND last_update_date = p_batch_history.last_update_date;
349 ELSE
350 gme_common_pvt.log_message ('GME_NO_KEYS'
351 ,'TABLE_NAME'
352 ,g_table_name);
353 RETURN FALSE;
354 END IF;
355
356 IF SQL%FOUND THEN
357 RETURN TRUE;
358 ELSE
359 IF l_dummy = 0 THEN
360 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
361 ,'TABLE_NAME'
362 ,g_table_name);
363 ELSE
364 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
365 ,'TABLE_NAME'
366 ,g_table_name);
367 END IF;
368
369 RETURN FALSE;
370 END IF;
371 EXCEPTION
372 WHEN NO_DATA_FOUND THEN
373 IF l_dummy = 0 THEN
374 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
375 ,'TABLE_NAME'
376 ,g_table_name);
377 ELSE
378 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
379 ,'TABLE_NAME'
380 ,g_table_name);
381 END IF;
382
383 RETURN FALSE;
384 WHEN locked_by_other_user THEN
385 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
386 ,'TABLE_NAME'
387 ,g_table_name
388 ,'RECORD'
389 ,'Batch'
390 ,'KEY'
391 ,TO_CHAR (p_batch_history.batch_id) );
392 RETURN FALSE;
393 WHEN OTHERS THEN
394 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
395 ,SQLERRM);
396 RETURN FALSE;
397 END update_row;
398
399 /* Api start of comments
400 +============================================================================
401 | FUNCTION NAME
402 | lock_row
403 |
404 | TYPE
405 | Private
406 | USAGE
407 | Lock_Row will lock a row in gme_batch_history
408 |
409 |
410 | DESCRIPTION
411 | Lock_Row will lock a row in gme_batch_history
412 |
413 |
414 |
415 | PARAMETERS
416 | p_batch_history IN gme_batch_history%ROWTYPE
417 |
418 | RETURNS
419 | BOOLEAN
420 | HISTORY
421 | 12-MAR-01 Thomas Daniel Created
422 |
423 |
424 |
425 +=============================================================================
426 Api end of comments
427 */
428 FUNCTION lock_row (p_batch_history IN gme_batch_history%ROWTYPE)
429 RETURN BOOLEAN
430 IS
431 l_dummy NUMBER;
432 BEGIN
433 IF p_batch_history.event_id IS NOT NULL THEN
434 SELECT 1
435 INTO l_dummy
436 FROM gme_batch_history
437 WHERE event_id = p_batch_history.event_id
438 FOR UPDATE NOWAIT;
439 END IF;
440
441 RETURN TRUE;
442 EXCEPTION
443 WHEN app_exception.record_lock_exception THEN
444 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
445 ,'TABLE_NAME'
446 ,g_table_name
447 ,'RECORD'
448 ,'Batch'
449 ,'KEY'
450 ,TO_CHAR (p_batch_history.batch_id) );
451 RETURN FALSE;
452 WHEN OTHERS THEN
453 RETURN FALSE;
454 END lock_row;
455 END gme_batch_history_dbl;