[Home] [Help]
PACKAGE BODY: APPS.GME_BATCH_STEP_CHARGE_DBL
Source
1 PACKAGE BODY gme_batch_step_charge_dbl AS
2 /* $Header: GMEVGSCB.pls 120.1 2005/06/03 13:46:38 appldev $ */
3 g_table_name VARCHAR2 (80) DEFAULT 'GME_BATCH_STEP_CHARGES';
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_batch_step_charges_dbl';
5 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
6
7 /* Api start of comments
8 +==========================================================================+
9 | FUNCTION NAME |
10 | insert_row |
11 | |
12 | TYPE |
13 | Private |
14 | |
15 | DESCRIPTION |
16 | insert_Row will insert a row in gme_batch_step_charges |
17 | |
18 | |
19 | PARAMETERS |
20 | p_batch_step_charges_in IN gme_batch_step_charges%ROWTYPE |
21 | x_batch_step_charges IN OUT NOCOPY gme_batch_step_charges%ROWTYPE |
22 | RETURNS |
23 | BOOLEAN |
24 | |
25 | HISTORY |
26 | 04-05-2004 Rishi Varma bug 3307549 |
27 | Created |
28 | |
29 +==========================================================================+
30 Api end of comments
31 */
32 FUNCTION insert_row (
33 p_batch_step_charges_in IN gme_batch_step_charges%ROWTYPE
34 ,x_batch_step_charges IN OUT NOCOPY gme_batch_step_charges%ROWTYPE)
35 RETURN BOOLEAN
36 IS
37 l_api_name CONSTANT VARCHAR2 (30) := 'INSERT_ROW';
38 BEGIN
39 x_batch_step_charges := p_batch_step_charges_in;
40
41 INSERT INTO gme_batch_step_charges
42 (batch_id
43 ,batchstep_id
44 ,resources
45 ,charge_number
46 ,charge_quantity
47 ,activity_sequence_number
48 ,plan_start_date
49 ,plan_cmplt_date
50 ,last_update_date, creation_date
51 ,created_by, last_updated_by
52 ,last_update_login)
53 VALUES (x_batch_step_charges.batch_id
54 ,x_batch_step_charges.batchstep_id
55 ,x_batch_step_charges.resources
56 ,x_batch_step_charges.charge_number
57 ,x_batch_step_charges.charge_quantity
58 ,x_batch_step_charges.activity_sequence_number
59 ,x_batch_step_charges.plan_start_date
60 ,x_batch_step_charges.plan_cmplt_date
61 ,gme_common_pvt.g_timestamp, gme_common_pvt.g_timestamp
62 ,gme_common_pvt.g_user_ident, gme_common_pvt.g_user_ident
63 ,gme_common_pvt.g_login_id);
64
65 IF SQL%FOUND THEN
66 RETURN TRUE;
67 ELSE
68 RETURN FALSE;
69 END IF;
70 EXCEPTION
71 WHEN OTHERS THEN
72 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
73 ,SQLERRM);
74 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
75 RETURN FALSE;
76 END insert_row;
77
78 /* Api start of comments
79 +==========================================================================+
80 | FUNCTION NAME |
81 | fetch_row |
82 | |
83 | TYPE |
84 | Private |
85 | |
86 | USAGE |
87 | fetch_Row will fetch a row in gme_batch_step_charges |
88 | |
89 | |
90 | DESCRIPTION |
91 | fetch_row will fetch a row in gme_batch_step_charges |
92 | |
93 | |
94 | PARAMETERS |
95 | p_batch_step_charges_in IN gme_batch_step_charges%ROWTYPE |
96 | x_batch_steps IN OUT NOCOPY gme_batch_step_charges%ROWTYPE |
97 | RETURNS |
98 | BOOLEAN |
99 | |
100 | HISTORY |
101 | 04-05-2004 Rishi Varma Bug 3307549 |
102 | Created |
103 | |
104 +==========================================================================+
105 Api end of comments
106 */
107 FUNCTION fetch_row (
108 p_batch_step_charges_in IN gme_batch_step_charges%ROWTYPE
109 ,x_batch_step_charges IN OUT NOCOPY gme_batch_step_charges%ROWTYPE)
110 RETURN BOOLEAN
111 IS
112 l_api_name CONSTANT VARCHAR2 (30) := 'FETCH_ROW';
113 BEGIN
114 IF p_batch_step_charges_in.batchstep_id IS NOT NULL THEN
115 SELECT batch_id
116 ,batchstep_id
117 ,resources
118 ,activity_sequence_number
119 ,charge_number
120 ,charge_quantity
121 ,plan_start_date
122 ,plan_cmplt_date
123 ,last_update_date
124 ,creation_date
125 ,created_by
126 ,last_updated_by
127 ,last_update_login
128 INTO x_batch_step_charges.batch_id
129 ,x_batch_step_charges.batchstep_id
130 ,x_batch_step_charges.resources
131 ,x_batch_step_charges.activity_sequence_number
132 ,x_batch_step_charges.charge_number
133 ,x_batch_step_charges.charge_quantity
134 ,x_batch_step_charges.plan_start_date
135 ,x_batch_step_charges.plan_cmplt_date
136 ,x_batch_step_charges.last_update_date
137 ,x_batch_step_charges.creation_date
138 ,x_batch_step_charges.created_by
139 ,x_batch_step_charges.last_updated_by
140 ,x_batch_step_charges.last_update_login
141 FROM gme_batch_step_charges
142 WHERE batchstep_id = p_batch_step_charges_in.batchstep_id;
143 ELSIF p_batch_step_charges_in.batch_id IS NOT NULL
144 AND p_batch_step_charges_in.batchstep_id IS NOT NULL THEN
145 SELECT batch_id
146 ,batchstep_id
147 ,resources
148 ,activity_sequence_number
149 ,charge_number
150 ,charge_quantity
151 ,plan_start_date
152 ,plan_cmplt_date
153 ,last_update_date
154 ,creation_date
155 ,created_by
156 ,last_updated_by
157 ,last_update_login
158 INTO x_batch_step_charges.batch_id
159 ,x_batch_step_charges.batchstep_id
160 ,x_batch_step_charges.resources
161 ,x_batch_step_charges.activity_sequence_number
162 ,x_batch_step_charges.charge_number
163 ,x_batch_step_charges.charge_quantity
164 ,x_batch_step_charges.plan_start_date
165 ,x_batch_step_charges.plan_cmplt_date
166 ,x_batch_step_charges.last_update_date
167 ,x_batch_step_charges.creation_date
168 ,x_batch_step_charges.created_by
169 ,x_batch_step_charges.last_updated_by
170 ,x_batch_step_charges.last_update_login
171 FROM gme_batch_step_charges
172 WHERE batch_id = p_batch_step_charges_in.batch_id
173 AND batchstep_id = p_batch_step_charges_in.batchstep_id;
174 ELSE
175 gme_common_pvt.log_message ('GME_NO_KEYS'
176 ,'TABLE_NAME'
177 ,g_table_name);
178 RETURN FALSE;
179 END IF;
180
181 RETURN TRUE;
182 EXCEPTION
183 WHEN NO_DATA_FOUND THEN
184 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
185 ,'TABLE_NAME'
186 ,g_table_name);
187 RETURN FALSE;
188 WHEN OTHERS THEN
189 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
190 ,SQLERRM);
191 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
192 RETURN FALSE;
193 END fetch_row;
194
195 /* Api start of comments
196 +==========================================================================+
197 | FUNCTION NAME |
198 | update_row |
199 | |
200 | TYPE |
201 | Private |
202 | |
203 | USAGE |
204 | update_Row will update a row in gme_batch_step_charges |
205 | |
206 | DESCRIPTION |
207 | update_row will update a row in gme_batch_step_charges |
208 | |
209 | |
210 | PARAMETERS |
211 | p_batch_step_charges_in IN gme_batch_step_charges%ROWTYPE |
212 | RETURNS |
213 | BOOLEAN |
214 | |
215 | HISTORY |
216 | 04-NAY-2004 Rishi Varma Bug 3307549 |
217 | Created |
218 | |
219 +==========================================================================+
220 Api end of comments
221 */
222 FUNCTION update_row (
223 p_batch_step_charges_in IN gme_batch_step_charges%ROWTYPE)
224 RETURN BOOLEAN
225 IS
226 CURSOR cur_both_ids (v_batch_id NUMBER, v_batchstep_id NUMBER)
227 IS
228 SELECT 1
229 FROM gme_batch_step_charges
230 WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id
231 FOR UPDATE NOWAIT;
232
233 CURSOR cur_step_id (v_batchstep_id NUMBER)
234 IS
235 SELECT 1
236 FROM gme_batch_step_charges
237 WHERE batchstep_id = v_batchstep_id
238 FOR UPDATE NOWAIT;
239
240 l_both_ids_rec NUMBER := 0;
241 l_step_id_rec NUMBER := 0;
242 locked_by_other_user EXCEPTION;
243 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
244 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ROW';
245 BEGIN
246 IF p_batch_step_charges_in.batchstep_id IS NOT NULL THEN
247 OPEN cur_step_id (p_batch_step_charges_in.batchstep_id);
248
249 LOOP
250 FETCH cur_step_id
251 INTO l_step_id_rec;
252
253 IF cur_step_id%NOTFOUND THEN
254 CLOSE cur_step_id;
255
256 EXIT;
257 END IF;
258 END LOOP;
259
260 UPDATE gme_batch_step_charges
261 SET batch_id = p_batch_step_charges_in.batch_id
262 ,batchstep_id = p_batch_step_charges_in.batchstep_id
263 ,resources = p_batch_step_charges_in.resources
264 ,activity_sequence_number =
265 p_batch_step_charges_in.activity_sequence_number
266 ,charge_number = p_batch_step_charges_in.charge_number
267 ,charge_quantity = p_batch_step_charges_in.charge_quantity
268 ,plan_start_date = p_batch_step_charges_in.plan_start_date
269 ,plan_cmplt_date = p_batch_step_charges_in.plan_cmplt_date
270 ,last_update_date = gme_common_pvt.g_timestamp
271 ,last_updated_by = gme_common_pvt.g_user_ident
272 ,last_update_login = gme_common_pvt.g_login_id
273 WHERE batchstep_id = p_batch_step_charges_in.batchstep_id
274 AND last_update_date = p_batch_step_charges_in.last_update_date;
275 ELSIF p_batch_step_charges_in.batch_id IS NOT NULL
276 AND p_batch_step_charges_in.batchstep_id IS NOT NULL THEN
277 OPEN cur_both_ids (p_batch_step_charges_in.batch_id
278 ,p_batch_step_charges_in.batchstep_id);
279
280 LOOP
281 FETCH cur_both_ids
282 INTO l_both_ids_rec;
283
284 IF cur_both_ids%NOTFOUND THEN
285 CLOSE cur_both_ids;
286
287 EXIT;
288 END IF;
289 END LOOP;
290
291 UPDATE gme_batch_step_charges
292 SET batch_id = p_batch_step_charges_in.batch_id
293 ,batchstep_id = p_batch_step_charges_in.batchstep_id
294 ,resources = p_batch_step_charges_in.resources
295 ,activity_sequence_number =
296 p_batch_step_charges_in.activity_sequence_number
297 ,charge_number = p_batch_step_charges_in.charge_number
298 ,charge_quantity = p_batch_step_charges_in.charge_quantity
299 ,plan_start_date = p_batch_step_charges_in.plan_start_date
300 ,plan_cmplt_date = p_batch_step_charges_in.plan_cmplt_date
301 ,last_update_date = gme_common_pvt.g_timestamp
302 ,last_updated_by = gme_common_pvt.g_user_ident
303 ,last_update_login = gme_common_pvt.g_login_id
304 WHERE batchstep_id = p_batch_step_charges_in.batchstep_id
305 AND batch_id = p_batch_step_charges_in.batch_id
306 AND last_update_date = p_batch_step_charges_in.last_update_date;
307 ELSE
308 gme_common_pvt.log_message ('GME_NO_KEYS'
309 ,'TABLE_NAME'
310 ,g_table_name);
311 RETURN FALSE;
312 END IF;
313
314 IF SQL%FOUND THEN
315 RETURN TRUE;
316 ELSE
317 RAISE NO_DATA_FOUND;
318 END IF;
319 EXCEPTION
320 WHEN NO_DATA_FOUND THEN
321 IF (l_both_ids_rec = 0 AND l_step_id_rec = 0) THEN
322 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
323 ,'TABLE_NAME'
324 ,g_table_name);
325 ELSE
326 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
327 ,'TABLE_NAME'
328 ,g_table_name);
329 END IF;
330
331 RETURN FALSE;
332 WHEN locked_by_other_user THEN
333 gme_common_pvt.log_message
334 ('GME_RECORD_LOCKED'
335 ,'TABLE_NAME'
336 ,g_table_name
337 ,'RECORD'
338 ,'Batchstep id'
339 ,'KEY'
340 ,TO_CHAR (p_batch_step_charges_in.batchstep_id) );
341 RETURN FALSE;
342 WHEN OTHERS THEN
343 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
344 ,SQLERRM);
345 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
346 RETURN FALSE;
347 END update_row;
348
349 /* Api start of comments
350 +==========================================================================+
351 | FUNCTION NAME |
352 | delete_row |
353 | |
354 | TYPE |
355 | Private |
356 | |
357 | USAGE |
358 | delete_Row will delete a row in gme_batch_step_charges |
359 | |
360 | DESCRIPTION |
361 | delete_row will delete a row in gme_batch_step_charges |
362 | |
363 | |
364 | PARAMETERS |
365 | p_batch_step_charges_in IN gme_batch_step_charges%ROWTYPE |
366 | RETURNS |
367 | BOOLEAN |
368 | |
369 | HISTORY |
370 | 04-NAY-2004 Rishi Varma Bug 3307549 |
371 | Created |
372 | |
373 +==========================================================================+
374 Api end of comments
375 */
376 FUNCTION delete_row (
377 p_batch_step_charges_in IN gme_batch_step_charges%ROWTYPE)
378 RETURN BOOLEAN
379 IS
380 CURSOR cur_both_ids (v_batch_id NUMBER, v_batchstep_id NUMBER)
381 IS
382 SELECT 1
383 FROM gme_batch_step_charges
384 WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id
385 FOR UPDATE NOWAIT;
386
387 CURSOR cur_step_id (v_batchstep_id NUMBER)
388 IS
389 SELECT 1
390 FROM gme_batch_step_charges
391 WHERE batchstep_id = v_batchstep_id
392 FOR UPDATE NOWAIT;
393
394 CURSOR cur_batch_id (v_batch_id NUMBER)
395 IS
396 SELECT 1
397 FROM gme_batch_step_charges
398 WHERE batch_id = v_batch_id
399 FOR UPDATE NOWAIT;
400
401 l_both_ids_rec NUMBER := 0;
402 l_step_id_rec NUMBER := 0;
403 l_batch_id_rec NUMBER := 0;
404 locked_by_other_user EXCEPTION;
405 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
406 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_ROW';
407 BEGIN
408 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
409 gme_debug.put_line ( g_pkg_name
410 || '.'
411 || l_api_name
412 || 'dbl,batch,step ids are '
413 || p_batch_step_charges_in.batch_id
414 || p_batch_step_charges_in.batchstep_id);
415 END IF;
416
417 IF p_batch_step_charges_in.batchstep_id IS NOT NULL THEN
418 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
419 gme_debug.put_line ( g_pkg_name
420 || '.'
421 || l_api_name
422 || 'dbl,batchstep id is '
423 || p_batch_step_charges_in.batchstep_id);
424 END IF;
425
426 OPEN cur_step_id (p_batch_step_charges_in.batchstep_id);
427
428 LOOP
429 FETCH cur_step_id
430 INTO l_step_id_rec;
431
432 IF cur_step_id%NOTFOUND THEN
433 CLOSE cur_step_id;
434
435 EXIT;
436 END IF;
437 END LOOP;
438
439 DELETE FROM gme_batch_step_charges
440 WHERE batchstep_id = p_batch_step_charges_in.batchstep_id;
441 ELSIF p_batch_step_charges_in.batch_id IS NOT NULL
442 AND p_batch_step_charges_in.batchstep_id IS NOT NULL THEN
443 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
444 gme_debug.put_line ( g_pkg_name
445 || '.'
446 || l_api_name
447 || 'in delete dbl,batch,step id is'
448 || p_batch_step_charges_in.batch_id
449 ,p_batch_step_charges_in.batchstep_id);
450 END IF;
451
452 OPEN cur_both_ids (p_batch_step_charges_in.batch_id
453 ,p_batch_step_charges_in.batchstep_id);
454
455 LOOP
456 FETCH cur_both_ids
457 INTO l_both_ids_rec;
458
459 IF cur_both_ids%NOTFOUND THEN
460 CLOSE cur_both_ids;
461
462 EXIT;
463 END IF;
464 END LOOP;
465
466 DELETE FROM gme_batch_step_charges
467 WHERE batch_id = p_batch_step_charges_in.batch_id
468 AND batchstep_id = p_batch_step_charges_in.batchstep_id;
469 ELSIF p_batch_step_charges_in.batch_id IS NOT NULL
470 AND p_batch_step_charges_in.batchstep_id IS NULL THEN
471 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
472 gme_debug.put_line ( g_pkg_name
473 || '.'
474 || l_api_name
475 || 'in delete dbl,batch_id is'
476 || p_batch_step_charges_in.batch_id);
477 END IF;
478
479 OPEN cur_batch_id (p_batch_step_charges_in.batch_id);
480
481 LOOP
482 FETCH cur_batch_id
483 INTO l_batch_id_rec;
484
485 IF cur_batch_id%NOTFOUND THEN
486 CLOSE cur_batch_id;
487
488 EXIT;
489 END IF;
490 END LOOP;
491
492 DELETE FROM gme_batch_step_charges
493 WHERE batch_id = p_batch_step_charges_in.batch_id;
494 ELSE
495 gme_common_pvt.log_message ('GME_NO_KEYS'
496 ,'TABLE_NAME'
497 ,g_table_name);
498 RETURN FALSE;
499 END IF;
500
501 IF SQL%FOUND THEN
502 RETURN TRUE;
503 ELSE
504 IF (l_both_ids_rec = 0)
505 AND (l_step_id_rec = 0)
506 AND (l_batch_id_rec = 0) THEN
507 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
508 ,'TABLE_NAME'
509 ,g_table_name);
510 ELSE
511 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
512 ,'TABLE_NAME'
513 ,g_table_name);
514 END IF;
515
516 RETURN FALSE;
517 END IF;
518 EXCEPTION
519 WHEN locked_by_other_user THEN
520 gme_common_pvt.log_message
521 ('GME_RECORD_LOCKED'
522 ,'TABLE_NAME'
523 ,g_table_name
524 ,'RECORD'
525 ,'Batchstep'
526 ,'KEY'
527 ,TO_CHAR (p_batch_step_charges_in.batchstep_id) );
528 RETURN FALSE;
529 WHEN OTHERS THEN
530 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
531 ,SQLERRM);
532 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
533 RETURN FALSE;
534 END delete_row;
535 END;