[Home] [Help]
PACKAGE BODY: APPS.GME_BATCH_STEPS_DBL
Source
1 PACKAGE BODY gme_batch_steps_dbl AS
2 /* $Header: GMEVGBSB.pls 120.3 2006/06/14 14:43:18 svgonugu noship $ */
3
4 /* Global Variables */
5 g_table_name VARCHAR2 (80) DEFAULT 'GME_BATCH_STEPS';
6 g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_batch_step_dbl';
7 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
8
9 /* ===========================================================================
10 | Copyright (c) 2001 Oracle Corporation |
11 | TVP, Reading, England |
12 | All rights reserved |
13 ===========================================================================
14 | FILENAME |
15 | GMEVGBSB.pls |
16 | |
17 | DESCRIPTION |
18 | |
19 | Body of package gme_batch_steps_dbl |
20 | |
21 | NOTES |
22 | HISTORY |
23 | |
24 | 13-Feb-01 Created |
25 | |
26 | - create_row |
27 | - fetch_row |
28 | - update_row |
29 | - delete_row |
30 | - lock_row |
31 | 07-Sep-01 Thomas Daniel |
32 | Added plan_mass_qty and plan_volume_qty in the procedures. |
33 | 30-AUG-02 Chandrashekar Tiruvidula Bug 2526710 |
34 | Added quality_status in insert/update/fetch |
35 | 07-Mar-03 Bharati Satpute Bug2804440 Added WHEN OTHERS exception |
36 | which were not defined
37 | Oct 2003 A. Newbury |
38 | - B3184949 Added terminate_ind for terminate batch enh. |
39 | 09-JUN-2006 SivakumarG Now fetch_row will fetch max_step_capacity |
40 | uom also.Bug#5231180 |
41 ===========================================================================
42 */
43
44 /* Api start of comments
45 +==========================================================================+
46 | FUNCTION NAME |
47 | insert_row |
48 | |
49 | TYPE |
50 | Private |
51 | |
52 | USAGE |
53 | insert_Row will insert a row in gme_batch_steps |
54 | |
55 | |
56 | DESCRIPTION |
57 | insert_Row will insert a row in gme_batch_steps |
58 | |
59 | |
60 | PARAMETERS |
61 | p_batch_step IN gme_batch_step%ROWTYPE |
62 | x_batch_steps IN OUT NOCOPY gme_batch_step%ROWTYPE |
63 | RETURNS |
64 | BOOLEAN |
65 | |
66 | HISTORY |
67 | 15-FEB-2001 fabdi Created |
68 | 02-APR-03 Bharati Satpute Bug2848936 Added minimum_transfer_qty to |
69 | complete MTQ functionality in APS |
70 | Oct 2003 A. Newbury Added terminated_ind for terminate batch |
71 | enh B3184949. |
72 | |
73 +==========================================================================+
74 Api end of comments
75 */
76 FUNCTION insert_row (
77 p_batch_step IN gme_batch_steps%ROWTYPE
78 ,x_batch_step IN OUT NOCOPY gme_batch_steps%ROWTYPE)
79 RETURN BOOLEAN
80 IS
81 l_api_name CONSTANT VARCHAR2 (30) := 'INSERT_ROW';
82 BEGIN
83 IF g_debug <= gme_debug.g_log_procedure THEN
84 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
85 || l_api_name);
86 END IF;
87
88 x_batch_step := p_batch_step;
89
90 INSERT INTO gme_batch_steps
91 (batch_id, batchstep_id
92 ,routingstep_id, batchstep_no
93 ,oprn_id, plan_step_qty
94 ,actual_step_qty
95 --, STEP_QTY_UOM
96 , backflush_qty
97 ,plan_start_date
98 ,actual_start_date, due_date
99 ,plan_cmplt_date
100 ,actual_cmplt_date
101 ,step_close_date, step_status
102 ,priority_code, priority_value
103 ,delete_mark, steprelease_type
104 ,max_step_capacity
105 --, MAX_STEP_CAPACITY_UOM
106 , plan_charges
107 ,actual_charges, text_code
108 ,last_update_date, creation_date
109 ,created_by, last_updated_by
110 ,last_update_login
111 ,attribute_category, attribute1
112 ,attribute2, attribute3
113 ,attribute4, attribute5
114 ,attribute6, attribute7
115 ,attribute8, attribute9
116 ,attribute10, attribute11
117 ,attribute12, attribute13
118 ,attribute14, attribute15
119 ,attribute16, attribute17
120 ,attribute18, attribute19
121 ,attribute20, attribute21
122 ,attribute22, attribute23
123 ,attribute24, attribute25
124 ,attribute26, attribute27
125 ,attribute28, attribute29
126 ,attribute30
127 --, MASS_REF_UOM
128 --, VOLUME_REF_UOM
129 , plan_mass_qty
130 ,plan_volume_qty
131 ,actual_mass_qty
132 ,actual_volume_qty
133 ,quality_status
134 ,minimum_transfer_qty, terminated_ind
135 ,step_qty_um
136 ,mass_ref_um
137 ,max_step_capacity_um
138 ,volume_ref_um)
139 VALUES (x_batch_step.batch_id, gme_batch_step_s.NEXTVAL
140 ,x_batch_step.routingstep_id, x_batch_step.batchstep_no
141 ,x_batch_step.oprn_id, x_batch_step.plan_step_qty
142 ,x_batch_step.actual_step_qty
143 --, x_batch_step.STEP_QTY_UOM
144 , x_batch_step.backflush_qty
145 ,x_batch_step.plan_start_date
146 ,x_batch_step.actual_start_date, x_batch_step.due_date
147 ,x_batch_step.plan_cmplt_date
148 ,x_batch_step.actual_cmplt_date
149 ,x_batch_step.step_close_date, x_batch_step.step_status
150 ,x_batch_step.priority_code, x_batch_step.priority_value
151 ,x_batch_step.delete_mark, x_batch_step.steprelease_type
152 ,x_batch_step.max_step_capacity
153 --, x_batch_step.MAX_STEP_CAPACITY_UOM
154 , x_batch_step.plan_charges
155 ,x_batch_step.actual_charges, x_batch_step.text_code
156 ,gme_common_pvt.g_timestamp, gme_common_pvt.g_timestamp
157 ,gme_common_pvt.g_user_ident, gme_common_pvt.g_user_ident
158 ,gme_common_pvt.g_login_id
159 ,x_batch_step.attribute_category, x_batch_step.attribute1
160 ,x_batch_step.attribute2, x_batch_step.attribute3
161 ,x_batch_step.attribute4, x_batch_step.attribute5
162 ,x_batch_step.attribute6, x_batch_step.attribute7
163 ,x_batch_step.attribute8, x_batch_step.attribute9
164 ,x_batch_step.attribute10, x_batch_step.attribute11
165 ,x_batch_step.attribute12, x_batch_step.attribute13
166 ,x_batch_step.attribute14, x_batch_step.attribute15
167 ,x_batch_step.attribute16, x_batch_step.attribute17
168 ,x_batch_step.attribute18, x_batch_step.attribute19
169 ,x_batch_step.attribute20, x_batch_step.attribute21
170 ,x_batch_step.attribute22, x_batch_step.attribute23
171 ,x_batch_step.attribute24, x_batch_step.attribute25
172 ,x_batch_step.attribute26, x_batch_step.attribute27
173 ,x_batch_step.attribute28, x_batch_step.attribute29
174 ,x_batch_step.attribute30
175 --, x_batch_step.MASS_REF_UOM
176 --, x_batch_step.VOLUME_REF_UOM
177 , x_batch_step.plan_mass_qty
178 ,x_batch_step.plan_volume_qty
179 ,x_batch_step.actual_mass_qty
180 ,x_batch_step.actual_volume_qty
181 ,x_batch_step.quality_status
182 ,x_batch_step.minimum_transfer_qty, 0
183 ,x_batch_step.step_qty_um
184 ,x_batch_step.mass_ref_um
185 ,x_batch_step.max_step_capacity_um
186 ,x_batch_step.volume_ref_um)
187 RETURNING batchstep_id
188 INTO x_batch_step.batchstep_id;
189
190 IF g_debug <= gme_debug.g_log_procedure THEN
191 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
192 END IF;
193
194 IF SQL%FOUND THEN
195 RETURN TRUE;
196 ELSE
197 RETURN FALSE;
198 END IF;
199 EXCEPTION
200 WHEN OTHERS THEN
201 x_batch_step.batchstep_id := NULL;
202 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
203 ,SQLERRM);
204 --Bug2804440
205 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
206 RETURN FALSE;
207 END insert_row;
208
209 /* Api start of comments
210 +==========================================================================+
211 | FUNCTION NAME |
212 | fetch_row |
213 | |
214 | TYPE |
215 | Private |
216 | |
217 | USAGE |
218 | fetch_Row will fetch a row in gme_batch_steps |
219 | |
220 | |
221 | DESCRIPTION |
222 | fetch_row will fetch a row in gme_batch_steps |
223 | |
224 | |
225 | PARAMETERS |
226 | p_batch_step IN gme_batch_steps%ROWTYPE |
227 | x_batch_steps IN OUT NOCOPY gme_batch_steps%ROWTYPE |
228 | RETURNS |
229 | BOOLEAN |
230 | |
231 | HISTORY |
232 | 12-FEB-2001 fabdi Created |
233 | Oct 2003 A. Newbury Added terminated_ind for terminate batch |
234 | enh B3184949. |
235 | 09-JUN-2006 SivakumarG Now fetch_row will fetch max_step_capacity |
236 | uom also.Bug#5231180 |
237 +==========================================================================+
238 Api end of comments
239 */
240 FUNCTION fetch_row (
241 p_batch_step IN gme_batch_steps%ROWTYPE
242 ,x_batch_step IN OUT NOCOPY gme_batch_steps%ROWTYPE)
243 RETURN BOOLEAN
244 IS
245 l_api_name CONSTANT VARCHAR2 (30) := 'FETCH_ROW';
246 BEGIN
247 IF g_debug <= gme_debug.g_log_procedure THEN
248 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
249 || l_api_name);
250 END IF;
251
252 IF p_batch_step.batchstep_id IS NOT NULL THEN
253 SELECT batch_id, batchstep_id
254 ,routingstep_id, batchstep_no
255 ,oprn_id, plan_step_qty
256 ,actual_step_qty
257 --, STEP_QTY_UOM
258 , backflush_qty
259 ,plan_start_date
260 ,actual_start_date, due_date
261 ,plan_cmplt_date
262 ,actual_cmplt_date
263 ,step_close_date, step_status
264 ,priority_code, priority_value
265 ,delete_mark, steprelease_type
266 ,max_step_capacity
267 --, MAX_STEP_CAPACITY_UOM
268 , plan_charges
269 ,actual_charges, text_code
270 ,last_update_date, creation_date
271 ,created_by, last_updated_by
272 ,last_update_login
273 ,attribute_category, attribute1
274 ,attribute2, attribute3
275 ,attribute4, attribute5
276 ,attribute6, attribute7
277 ,attribute8, attribute9
278 ,attribute10, attribute11
279 ,attribute12, attribute13
280 ,attribute14, attribute15
281 ,attribute16, attribute17
282 ,attribute18, attribute19
283 ,attribute20, attribute21
284 ,attribute22, attribute23
285 ,attribute24, attribute25
286 ,attribute26, attribute27
287 ,attribute28, attribute29
288 ,attribute30
289 --, MASS_REF_UOM
290 --, VOLUME_REF_UOM
291 , plan_mass_qty
292 ,plan_volume_qty, actual_mass_qty
293 ,actual_volume_qty, quality_status
294 ,minimum_transfer_qty
295 ,terminated_ind, step_qty_um
296 ,mass_ref_um, volume_ref_um
297 ,max_step_capacity_um --Bug#5231180
298 INTO x_batch_step.batch_id, x_batch_step.batchstep_id
299 ,x_batch_step.routingstep_id, x_batch_step.batchstep_no
300 ,x_batch_step.oprn_id, x_batch_step.plan_step_qty
301 ,x_batch_step.actual_step_qty
302 --, x_batch_step.STEP_QTY_UOM
303 , x_batch_step.backflush_qty
304 ,x_batch_step.plan_start_date
305 ,x_batch_step.actual_start_date, x_batch_step.due_date
306 ,x_batch_step.plan_cmplt_date
307 ,x_batch_step.actual_cmplt_date
308 ,x_batch_step.step_close_date, x_batch_step.step_status
309 ,x_batch_step.priority_code, x_batch_step.priority_value
310 ,x_batch_step.delete_mark, x_batch_step.steprelease_type
311 ,x_batch_step.max_step_capacity
312 --, x_batch_step.MAX_STEP_CAPACITY_UOM
313 , x_batch_step.plan_charges
314 ,x_batch_step.actual_charges, x_batch_step.text_code
315 ,x_batch_step.last_update_date, x_batch_step.creation_date
316 ,x_batch_step.created_by, x_batch_step.last_updated_by
317 ,x_batch_step.last_update_login
318 ,x_batch_step.attribute_category, x_batch_step.attribute1
319 ,x_batch_step.attribute2, x_batch_step.attribute3
320 ,x_batch_step.attribute4, x_batch_step.attribute5
321 ,x_batch_step.attribute6, x_batch_step.attribute7
322 ,x_batch_step.attribute8, x_batch_step.attribute9
323 ,x_batch_step.attribute10, x_batch_step.attribute11
324 ,x_batch_step.attribute12, x_batch_step.attribute13
325 ,x_batch_step.attribute14, x_batch_step.attribute15
326 ,x_batch_step.attribute16, x_batch_step.attribute17
327 ,x_batch_step.attribute18, x_batch_step.attribute19
328 ,x_batch_step.attribute20, x_batch_step.attribute21
329 ,x_batch_step.attribute22, x_batch_step.attribute23
330 ,x_batch_step.attribute24, x_batch_step.attribute25
331 ,x_batch_step.attribute26, x_batch_step.attribute27
332 ,x_batch_step.attribute28, x_batch_step.attribute29
333 ,x_batch_step.attribute30
334 --, x_batch_step.MASS_REF_UOM
335 --, x_batch_step.VOLUME_REF_UOM
336 , x_batch_step.plan_mass_qty
337 ,x_batch_step.plan_volume_qty, x_batch_step.actual_mass_qty
338 ,x_batch_step.actual_volume_qty, x_batch_step.quality_status
339 ,x_batch_step.minimum_transfer_qty
340 ,x_batch_step.terminated_ind, x_batch_step.step_qty_um
341 ,x_batch_step.mass_ref_um, x_batch_step.volume_ref_um
342 ,x_batch_step.max_step_capacity_um --Bug#5231180
343 FROM gme_batch_steps
344 WHERE batchstep_id = p_batch_step.batchstep_id;
345 ELSIF p_batch_step.batch_id IS NOT NULL
346 AND p_batch_step.batchstep_no IS NOT NULL THEN
347 SELECT batch_id, batchstep_id
348 ,routingstep_id, batchstep_no
349 ,oprn_id, plan_step_qty
350 ,actual_step_qty
351 --, STEP_QTY_UOM
352 , backflush_qty
353 ,plan_start_date
354 ,actual_start_date, due_date
355 ,plan_cmplt_date
356 ,actual_cmplt_date
357 ,step_close_date, step_status
358 ,priority_code, priority_value
359 ,delete_mark, steprelease_type
360 ,max_step_capacity
361 --, MAX_STEP_CAPACITY_UOM
362 , plan_charges
363 ,actual_charges, text_code
364 ,last_update_date, creation_date
365 ,created_by, last_updated_by
366 ,last_update_login
367 ,attribute_category, attribute1
368 ,attribute2, attribute3
369 ,attribute4, attribute5
370 ,attribute6, attribute7
371 ,attribute8, attribute9
372 ,attribute10, attribute11
373 ,attribute12, attribute13
374 ,attribute14, attribute15
375 ,attribute16, attribute17
376 ,attribute18, attribute19
377 ,attribute20, attribute21
378 ,attribute22, attribute23
379 ,attribute24, attribute25
380 ,attribute26, attribute27
381 ,attribute28, attribute29
382 ,attribute30
383 --, MASS_REF_UOM
384 --, VOLUME_REF_UOM
385 , plan_mass_qty
386 ,plan_volume_qty, actual_mass_qty
387 ,actual_volume_qty, quality_status
388 ,minimum_transfer_qty
389 ,terminated_ind, step_qty_um
390 ,mass_ref_um, volume_ref_um
391 ,max_step_capacity_um --Bug#5231180
392 INTO x_batch_step.batch_id, x_batch_step.batchstep_id
393 ,x_batch_step.routingstep_id, x_batch_step.batchstep_no
394 ,x_batch_step.oprn_id, x_batch_step.plan_step_qty
395 ,x_batch_step.actual_step_qty
396 --, x_batch_step.STEP_QTY_UOM
397 , x_batch_step.backflush_qty
398 ,x_batch_step.plan_start_date
399 ,x_batch_step.actual_start_date, x_batch_step.due_date
400 ,x_batch_step.plan_cmplt_date
401 ,x_batch_step.actual_cmplt_date
402 ,x_batch_step.step_close_date, x_batch_step.step_status
403 ,x_batch_step.priority_code, x_batch_step.priority_value
404 ,x_batch_step.delete_mark, x_batch_step.steprelease_type
405 ,x_batch_step.max_step_capacity
406 --, x_batch_step.MAX_STEP_CAPACITY_UOM
407 , x_batch_step.plan_charges
408 ,x_batch_step.actual_charges, x_batch_step.text_code
409 ,x_batch_step.last_update_date, x_batch_step.creation_date
410 ,x_batch_step.created_by, x_batch_step.last_updated_by
411 ,x_batch_step.last_update_login
412 ,x_batch_step.attribute_category, x_batch_step.attribute1
413 ,x_batch_step.attribute2, x_batch_step.attribute3
414 ,x_batch_step.attribute4, x_batch_step.attribute5
415 ,x_batch_step.attribute6, x_batch_step.attribute7
416 ,x_batch_step.attribute8, x_batch_step.attribute9
417 ,x_batch_step.attribute10, x_batch_step.attribute11
418 ,x_batch_step.attribute12, x_batch_step.attribute13
419 ,x_batch_step.attribute14, x_batch_step.attribute15
420 ,x_batch_step.attribute16, x_batch_step.attribute17
421 ,x_batch_step.attribute18, x_batch_step.attribute19
422 ,x_batch_step.attribute20, x_batch_step.attribute21
423 ,x_batch_step.attribute22, x_batch_step.attribute23
424 ,x_batch_step.attribute24, x_batch_step.attribute25
425 ,x_batch_step.attribute26, x_batch_step.attribute27
426 ,x_batch_step.attribute28, x_batch_step.attribute29
427 ,x_batch_step.attribute30
428 --, x_batch_step.MASS_REF_UOM
429 --, x_batch_step.VOLUME_REF_UOM
430 , x_batch_step.plan_mass_qty
431 ,x_batch_step.plan_volume_qty, x_batch_step.actual_mass_qty
432 ,x_batch_step.actual_volume_qty, x_batch_step.quality_status
433 ,x_batch_step.minimum_transfer_qty
434 ,x_batch_step.terminated_ind, x_batch_step.step_qty_um
435 ,x_batch_step.mass_ref_um, x_batch_step.volume_ref_um
436 ,x_batch_step.max_step_capacity_um --Bug#5231180
437 FROM gme_batch_steps
438 WHERE batch_id = p_batch_step.batch_id
439 AND batchstep_no = p_batch_step.batchstep_no;
440 ELSE
441 gme_common_pvt.log_message ('GME_NO_KEYS'
442 ,'TABLE_NAME'
443 ,g_table_name);
444 RETURN FALSE;
445 END IF;
446
447 IF g_debug <= gme_debug.g_log_procedure THEN
448 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
449 END IF;
450
451 RETURN TRUE;
452 EXCEPTION
453 WHEN NO_DATA_FOUND THEN
454 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
455 ,'TABLE_NAME'
456 ,g_table_name);
457 RETURN FALSE;
458 WHEN OTHERS THEN
459 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
460 ,SQLERRM);
461 --bug2804440
462 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
463 RETURN FALSE;
464 END fetch_row;
465
466 /* Api start of comments
467 +==========================================================================+
468 | FUNCTION NAME |
469 | delete_row |
470 | |
471 | TYPE |
472 | Private |
473 | |
474 | USAGE |
475 | delete_Row will delete a row in gme_batch_steps |
476 | |
477 | |
478 | DESCRIPTION |
479 | delete_row will delete a row in gme_batch_steps |
480 | |
481 | |
482 | PARAMETERS |
483 | p_batch_step IN gme_batch_steps%ROWTYPE |
484 | RETURNS |
485 | BOOLEAN |
486 | |
487 | HISTORY |
488 | 12-FEB-2001 fabdi Created |
489 | 23-AUG-2002 Bharati Satpute Bug 2404126 |
490 | Added message GME_RECORD_CHANGED |
491 | |
492 +==========================================================================+
493 Api end of comments
494 */
495 FUNCTION delete_row (p_batch_step IN gme_batch_steps%ROWTYPE)
496 RETURN BOOLEAN
497 IS
498 l_dummy NUMBER (5) := 0;
499 locked_by_other_user EXCEPTION;
500 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
501 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_ROW';
502 BEGIN
503 IF g_debug <= gme_debug.g_log_procedure THEN
504 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
505 || l_api_name);
506 END IF;
507
508 IF p_batch_step.batchstep_id IS NOT NULL THEN
509 SELECT 1
510 INTO l_dummy
511 FROM gme_batch_steps
512 WHERE batchstep_id = p_batch_step.batchstep_id
513 FOR UPDATE NOWAIT;
514
515 DELETE FROM gme_batch_steps
516 WHERE batchstep_id = p_batch_step.batchstep_id;
517 ELSIF p_batch_step.batch_id IS NOT NULL
518 AND p_batch_step.batchstep_no IS NOT NULL THEN
519 SELECT 1
520 INTO l_dummy
521 FROM gme_batch_steps
522 WHERE batch_id = p_batch_step.batch_id
523 AND batchstep_no = p_batch_step.batchstep_no
524 FOR UPDATE NOWAIT;
525
526 DELETE FROM gme_batch_steps
527 WHERE batch_id = p_batch_step.batch_id
528 AND batchstep_no = p_batch_step.batchstep_no;
529 ELSE
530 gme_common_pvt.log_message ('GME_NO_KEYS'
531 ,'TABLE_NAME'
532 ,g_table_name);
533 RETURN FALSE;
534 END IF;
535
536 IF SQL%FOUND THEN
537 RETURN TRUE;
538 ELSE
539 IF l_dummy = 0 THEN
540 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
541 ,'TABLE_NAME'
542 ,g_table_name);
543 ELSE
544 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
545 ,'TABLE_NAME'
546 ,g_table_name);
547 END IF;
548
549 RETURN FALSE;
550 END IF;
551 EXCEPTION
552 WHEN locked_by_other_user THEN
553 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
554 ,'TABLE_NAME'
555 ,g_table_name
556 ,'RECORD'
557 ,'Batchstep'
558 ,'KEY'
559 ,TO_CHAR (p_batch_step.batchstep_no) );
560 RETURN FALSE;
561 WHEN OTHERS THEN
562 IF g_debug <= gme_debug.g_log_unexpected THEN
563 gme_debug.put_line ( 'When others exception in '
564 || g_pkg_name
565 || '.'
566 || l_api_name
567 || ' Error is '
568 || SQLERRM);
569 END IF;
570
571 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
572 --bug2804440
573 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
574 RETURN FALSE;
575 END delete_row;
576
577 /* Api start of comments
578 +==========================================================================+
579 | FUNCTION NAME |
580 | update_row |
581 | |
582 | TYPE |
583 | Private |
584 | |
585 | USAGE |
586 | update_row will update a row in gme_batch_steps |
587 | |
588 | |
589 | DESCRIPTION |
590 | update_row will update a row in gme_batch_steps |
591 | |
592 | |
593 | PARAMETERS |
594 | p_batch_step IN gme_batch_steps%ROWTYPE |
595 | RETURNS |
596 | BOOLEAN |
597 | |
598 | HISTORY |
599 | 12-FEB-2001 fabdi Created |
600 | 23-AUG-2002 Bharati Satpute Bug 2404126 |
601 | Added message GME_RECORD_CHANGED |
602 | Oct 2003 A. Newbury Added terminated_ind for terminate batch |
603 | enh B3184949. |
604 | |
605 +==========================================================================+
606 Api end of comments
607 */
608 FUNCTION update_row (p_batch_step IN gme_batch_steps%ROWTYPE)
609 RETURN BOOLEAN
610 IS
611 l_dummy NUMBER := 0;
612 locked_by_other_user EXCEPTION;
613 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
614 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ROW';
615 BEGIN
616 IF g_debug <= gme_debug.g_log_procedure THEN
617 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
618 || l_api_name);
619 END IF;
620
621 IF p_batch_step.batchstep_id IS NOT NULL THEN
622 SELECT 1
623 INTO l_dummy
624 FROM gme_batch_steps
625 WHERE batchstep_id = p_batch_step.batchstep_id
626 FOR UPDATE NOWAIT;
627
628 UPDATE gme_batch_steps
629 SET routingstep_id = p_batch_step.routingstep_id
630 ,batchstep_no = p_batch_step.batchstep_no
631 ,oprn_id = p_batch_step.oprn_id
632 ,plan_step_qty = p_batch_step.plan_step_qty
633 ,actual_step_qty = p_batch_step.actual_step_qty
634 --,STEP_QTY_UOM=p_batch_step.step_qty_uom
635 , backflush_qty = p_batch_step.backflush_qty
636 ,plan_start_date = p_batch_step.plan_start_date
637 ,actual_start_date = p_batch_step.actual_start_date
638 ,due_date = p_batch_step.due_date
639 ,plan_cmplt_date = p_batch_step.plan_cmplt_date
640 ,actual_cmplt_date = p_batch_step.actual_cmplt_date
641 ,step_close_date = p_batch_step.step_close_date
642 ,step_status = p_batch_step.step_status
643 ,priority_code = p_batch_step.priority_code
644 ,priority_value = p_batch_step.priority_value
645 ,delete_mark = p_batch_step.delete_mark
646 ,steprelease_type = p_batch_step.steprelease_type
647 ,max_step_capacity = p_batch_step.max_step_capacity
648 --,MAX_STEP_CAPACITY_UOM=p_batch_step.max_step_capacity_uom
649 , plan_charges = p_batch_step.plan_charges
650 ,actual_charges = p_batch_step.actual_charges
651 ,text_code = p_batch_step.text_code
652 ,last_update_date = gme_common_pvt.g_timestamp
653 ,last_updated_by = gme_common_pvt.g_user_ident
654 ,last_update_login = gme_common_pvt.g_login_id
655 ,attribute_category = p_batch_step.attribute_category
656 ,attribute1 = p_batch_step.attribute1
657 ,attribute2 = p_batch_step.attribute2
658 ,attribute3 = p_batch_step.attribute3
659 ,attribute4 = p_batch_step.attribute4
660 ,attribute5 = p_batch_step.attribute5
661 ,attribute6 = p_batch_step.attribute6
662 ,attribute7 = p_batch_step.attribute7
663 ,attribute8 = p_batch_step.attribute8
664 ,attribute9 = p_batch_step.attribute9
665 ,attribute10 = p_batch_step.attribute10
666 ,attribute11 = p_batch_step.attribute11
667 ,attribute12 = p_batch_step.attribute12
668 ,attribute13 = p_batch_step.attribute13
669 ,attribute14 = p_batch_step.attribute14
670 ,attribute15 = p_batch_step.attribute15
671 ,attribute16 = p_batch_step.attribute16
672 ,attribute17 = p_batch_step.attribute17
673 ,attribute18 = p_batch_step.attribute18
674 ,attribute19 = p_batch_step.attribute19
675 ,attribute20 = p_batch_step.attribute20
676 ,attribute21 = p_batch_step.attribute21
677 ,attribute22 = p_batch_step.attribute22
678 ,attribute23 = p_batch_step.attribute23
679 ,attribute24 = p_batch_step.attribute24
680 ,attribute25 = p_batch_step.attribute25
681 ,attribute26 = p_batch_step.attribute26
682 ,attribute27 = p_batch_step.attribute27
683 ,attribute28 = p_batch_step.attribute28
684 ,attribute29 = p_batch_step.attribute29
685 ,attribute30 = p_batch_step.attribute30
686 --,MASS_REF_UOM=p_batch_step.mass_ref_uom
687 --,VOLUME_REF_UOM=p_batch_step.volume_ref_uom
688 , plan_mass_qty = p_batch_step.plan_mass_qty
689 ,plan_volume_qty = p_batch_step.plan_volume_qty
690 ,actual_mass_qty = p_batch_step.actual_mass_qty
691 ,actual_volume_qty = p_batch_step.actual_volume_qty
692 ,quality_status = p_batch_step.quality_status
693 ,minimum_transfer_qty = p_batch_step.minimum_transfer_qty
694 ,terminated_ind = p_batch_step.terminated_ind
695 ,step_qty_um = p_batch_step.step_qty_um
696 ,mass_ref_um = p_batch_step.mass_ref_um
697 ,volume_ref_um = p_batch_step.volume_ref_um
698 WHERE batchstep_id = p_batch_step.batchstep_id
699 AND last_update_date = p_batch_step.last_update_date;
700 ELSIF p_batch_step.batch_id IS NOT NULL
701 AND p_batch_step.batchstep_no IS NOT NULL THEN
702 SELECT 1
703 INTO l_dummy
704 FROM gme_batch_steps
705 WHERE batch_id = p_batch_step.batch_id
706 AND batchstep_no = p_batch_step.batchstep_no
707 FOR UPDATE NOWAIT;
708
709 UPDATE gme_batch_steps
710 SET routingstep_id = p_batch_step.routingstep_id
711 ,batchstep_no = p_batch_step.batchstep_no
712 ,oprn_id = p_batch_step.oprn_id
713 ,plan_step_qty = p_batch_step.plan_step_qty
714 ,actual_step_qty = p_batch_step.actual_step_qty
715 --,STEP_QTY_UOM=p_batch_step.step_qty_uom
716 , backflush_qty = p_batch_step.backflush_qty
717 ,plan_start_date = p_batch_step.plan_start_date
718 ,actual_start_date = p_batch_step.actual_start_date
719 ,due_date = p_batch_step.due_date
720 ,plan_cmplt_date = p_batch_step.plan_cmplt_date
721 ,actual_cmplt_date = p_batch_step.actual_cmplt_date
722 ,step_close_date = p_batch_step.step_close_date
723 ,step_status = p_batch_step.step_status
724 ,priority_code = p_batch_step.priority_code
725 ,priority_value = p_batch_step.priority_value
726 ,delete_mark = p_batch_step.delete_mark
727 ,steprelease_type = p_batch_step.steprelease_type
728 ,max_step_capacity = p_batch_step.max_step_capacity
729 --,MAX_STEP_CAPACITY_UOM=p_batch_step.max_step_capacity_uom
730 , plan_charges = p_batch_step.plan_charges
731 ,actual_charges = p_batch_step.actual_charges
732 ,text_code = p_batch_step.text_code
733 ,last_update_date = gme_common_pvt.g_timestamp
734 ,last_updated_by = gme_common_pvt.g_user_ident
735 ,last_update_login = gme_common_pvt.g_login_id
736 ,attribute_category = p_batch_step.attribute_category
737 ,attribute1 = p_batch_step.attribute1
738 ,attribute2 = p_batch_step.attribute2
739 ,attribute3 = p_batch_step.attribute3
740 ,attribute4 = p_batch_step.attribute4
741 ,attribute5 = p_batch_step.attribute5
742 ,attribute6 = p_batch_step.attribute6
743 ,attribute7 = p_batch_step.attribute7
744 ,attribute8 = p_batch_step.attribute8
745 ,attribute9 = p_batch_step.attribute9
746 ,attribute10 = p_batch_step.attribute10
747 ,attribute11 = p_batch_step.attribute11
748 ,attribute12 = p_batch_step.attribute12
749 ,attribute13 = p_batch_step.attribute13
750 ,attribute14 = p_batch_step.attribute14
751 ,attribute15 = p_batch_step.attribute15
752 ,attribute16 = p_batch_step.attribute16
753 ,attribute17 = p_batch_step.attribute17
754 ,attribute18 = p_batch_step.attribute18
755 ,attribute19 = p_batch_step.attribute19
756 ,attribute20 = p_batch_step.attribute20
757 ,attribute21 = p_batch_step.attribute21
758 ,attribute22 = p_batch_step.attribute22
759 ,attribute23 = p_batch_step.attribute23
760 ,attribute24 = p_batch_step.attribute24
761 ,attribute25 = p_batch_step.attribute25
762 ,attribute26 = p_batch_step.attribute26
763 ,attribute27 = p_batch_step.attribute27
764 ,attribute28 = p_batch_step.attribute28
765 ,attribute29 = p_batch_step.attribute29
766 ,attribute30 = p_batch_step.attribute30
767 --,MASS_REF_UOM=p_batch_step.mass_ref_uom
768 --,VOLUME_REF_UOM=p_batch_step.volume_ref_uom
769 , plan_mass_qty = p_batch_step.plan_mass_qty
770 ,plan_volume_qty = p_batch_step.plan_volume_qty
771 ,actual_mass_qty = p_batch_step.actual_mass_qty
772 ,actual_volume_qty = p_batch_step.actual_volume_qty
773 ,quality_status = p_batch_step.quality_status
774 ,minimum_transfer_qty = p_batch_step.minimum_transfer_qty
775 ,terminated_ind = p_batch_step.terminated_ind
776 ,step_qty_um = p_batch_step.step_qty_um
777 ,mass_ref_um = p_batch_step.mass_ref_um
778 ,volume_ref_um = p_batch_step.volume_ref_um
779 WHERE batch_id = p_batch_step.batch_id
780 AND batchstep_no = p_batch_step.batchstep_no;
781 ELSE
782 gme_common_pvt.log_message ('GME_NO_KEYS'
783 ,'TABLE_NAME'
784 ,g_table_name);
785 RETURN FALSE;
786 END IF;
787
788 IF SQL%FOUND THEN
789 RETURN TRUE;
790 ELSE
791 RAISE NO_DATA_FOUND;
792 END IF;
793 EXCEPTION
794 WHEN NO_DATA_FOUND THEN
795 IF l_dummy = 0 THEN
796 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
797 ,'TABLE_NAME'
798 ,g_table_name);
799 ELSE
800 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
801 ,'TABLE_NAME'
802 ,g_table_name);
803 END IF;
804
805 RETURN FALSE;
806 WHEN locked_by_other_user THEN
807 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
808 ,'TABLE_NAME'
809 ,g_table_name
810 ,'RECORD'
811 ,'Batchstep'
812 ,'KEY'
813 ,TO_CHAR (p_batch_step.batchstep_no) );
814 RETURN FALSE;
815 WHEN OTHERS THEN
816 IF g_debug <= gme_debug.g_log_unexpected THEN
817 gme_debug.put_line ( 'When others exception in '
818 || g_pkg_name
819 || '.'
820 || l_api_name
821 || ' Error is '
822 || SQLERRM);
823 END IF;
824
825 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
826 --Bug2804440
827 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
828 RETURN FALSE;
829 END update_row;
830
831 FUNCTION lock_row (p_batch_step IN gme_batch_steps%ROWTYPE)
832 RETURN BOOLEAN
833 IS
834 l_dummy NUMBER;
835 l_api_name CONSTANT VARCHAR2 (30) := 'lock_row';
836 BEGIN
837 IF g_debug <= gme_debug.g_log_procedure THEN
838 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
839 || l_api_name);
840 END IF;
841
842 IF p_batch_step.batchstep_id IS NOT NULL THEN
843 SELECT 1
844 INTO l_dummy
845 FROM gme_batch_steps
846 WHERE batchstep_id = p_batch_step.batchstep_id
847 FOR UPDATE NOWAIT;
848 END IF;
849
850 RETURN TRUE;
851 EXCEPTION
852 WHEN app_exception.record_lock_exception THEN
853 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
854 ,'TABLE_NAME'
855 ,g_table_name
856 ,'RECORD'
857 ,'Batchstep'
858 ,'KEY'
859 ,TO_CHAR (p_batch_step.batchstep_no) );
860 RETURN FALSE;
861 WHEN OTHERS THEN
862 IF g_debug <= gme_debug.g_log_unexpected THEN
863 gme_debug.put_line ( 'When others exception in '
864 || g_pkg_name
865 || '.'
866 || l_api_name
867 || ' Error is '
868 || SQLERRM);
869 END IF;
870
871 --Bug2804440
872 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
873 RETURN FALSE;
874 END;
875 END;