83: expected_error EXCEPTION;
84: invalid_text_code EXCEPTION; --nsinghi bug#5202811
85: invalid_oprn_effectivity EXCEPTION;
86: BEGIN
87: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
88: gme_debug.put_line ('Begin Insert_Batch_Step');
89: END IF;
90:
91: x_return_status := fnd_api.g_ret_sts_success;
84: invalid_text_code EXCEPTION; --nsinghi bug#5202811
85: invalid_oprn_effectivity EXCEPTION;
86: BEGIN
87: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
88: gme_debug.put_line ('Begin Insert_Batch_Step');
89: END IF;
90:
91: x_return_status := fnd_api.g_ret_sts_success;
92: /* Bug 2397077 Use only one batch_id when batch IDs passed in both parameters. */
160: ,x_oprn_act_out => l_recipe_rout_act
161: ,x_oprn_resc_rec => l_recipe_rout_resc
162: ,x_oprn_resc_proc_param_tbl => l_resc_parameters);
163:
164: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
165: gme_debug.put_line ( 'Inser_Batch_step:fetch_oprn returned:'
166: || l_return_status
167: || ' Return Code:'||l_return_code
168: || ' Error Msg: '
161: ,x_oprn_resc_rec => l_recipe_rout_resc
162: ,x_oprn_resc_proc_param_tbl => l_resc_parameters);
163:
164: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
165: gme_debug.put_line ( 'Inser_Batch_step:fetch_oprn returned:'
166: || l_return_status
167: || ' Return Code:'||l_return_code
168: || ' Error Msg: '
169: || l_message_data
168: || ' Error Msg: '
169: || l_message_data
170: || ' ERROR:'
171: || SQLERRM);
172: gme_debug.put_line
173: ( 'Insert_Batch_Step... GMD_FETCH_OPRN.FETCH_OPRN returned '
174: || l_recipe_rout_act.COUNT
175: || ' ACTIVITIES');
176: gme_debug.put_line
172: gme_debug.put_line
173: ( 'Insert_Batch_Step... GMD_FETCH_OPRN.FETCH_OPRN returned '
174: || l_recipe_rout_act.COUNT
175: || ' ACTIVITIES');
176: gme_debug.put_line
177: ( 'Insert_Batch_Step... GMD_FETCH_OPRN.FETCH_OPRN returned '
178: || l_recipe_rout_resc.COUNT
179: || ' RESOURCES');
180:
178: || l_recipe_rout_resc.COUNT
179: || ' RESOURCES');
180:
181: FOR i IN 1 .. l_recipe_rout_act.COUNT LOOP
182: gme_debug.put_line
183: ( 'Insert_Batch_Step... GMD_FETCH_OPRN.FETCH_OPRN returned '
184: || l_recipe_rout_act (i).oprn_id
185: || ' operation_id');
186: gme_debug.put_line
182: gme_debug.put_line
183: ( 'Insert_Batch_Step... GMD_FETCH_OPRN.FETCH_OPRN returned '
184: || l_recipe_rout_act (i).oprn_id
185: || ' operation_id');
186: gme_debug.put_line
187: ( 'Insert_Batch_Step... GMD_FETCH_OPRN.FETCH_OPRN returned '
188: || l_recipe_rout_act (i).minimum_transfer_qty
189: || ' minimum_transfer_qty at Activity '||l_recipe_rout_act (i).activity);
190: END LOOP;
194: RAISE error_gmd_fetch_oprn;
195: END IF;
196:
197: IF l_recipe_rout_act.COUNT < 1 THEN
198: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
199: gme_debug.put_line
200: ('Insert_Batch Step - GMD_FETCH_OPRN.FETCH_OPRN returned no activities');
201: END IF;
202: gme_common_pvt.log_message('GME_AT_LEAST_ONE_ACTIVITY');
195: END IF;
196:
197: IF l_recipe_rout_act.COUNT < 1 THEN
198: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
199: gme_debug.put_line
200: ('Insert_Batch Step - GMD_FETCH_OPRN.FETCH_OPRN returned no activities');
201: END IF;
202: gme_common_pvt.log_message('GME_AT_LEAST_ONE_ACTIVITY');
203: RAISE error_gmd_fetch_oprn;
203: RAISE error_gmd_fetch_oprn;
204: END IF;
205:
206: IF l_recipe_rout_resc.COUNT < 1 THEN
207: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
208: gme_debug.put_line
209: ('Insert_Batch Step - GMD_FETCH_OPRN.FETCH_OPRN returned no resources');
210: END IF;
211: gme_common_pvt.log_message('GME_AT_LEAST_ONE_RESOURCE');
204: END IF;
205:
206: IF l_recipe_rout_resc.COUNT < 1 THEN
207: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
208: gme_debug.put_line
209: ('Insert_Batch Step - GMD_FETCH_OPRN.FETCH_OPRN returned no resources');
210: END IF;
211: gme_common_pvt.log_message('GME_AT_LEAST_ONE_RESOURCE');
212: RAISE error_gmd_fetch_oprn;
299: x_return_status := FND_API.G_RET_STS_ERROR;
300: RAISE invalid_oprn_effectivity;
301: END IF;
302:
303: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
304: gme_debug.put_line (' Before Validate Flex ');
305: END IF;
306:
307: -- Added following code in order to set the flexfields and the ID fields
300: RAISE invalid_oprn_effectivity;
301: END IF;
302:
303: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
304: gme_debug.put_line (' Before Validate Flex ');
305: END IF;
306:
307: -- Added following code in order to set the flexfields and the ID fields
308: -- Before calling the validate_flex_fields
369: /* Bug 2397077 Added message to indicate success of program. */
370: gme_common_pvt.log_message ('GME_INSERT_BATCH_STEP_SUCCESS');
371:
372: /* End Bug 2397077 */
373: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
374: gme_debug.put_line ('End Insert_Batch_Step');
375: END IF;
376: EXCEPTION
377: WHEN error_inv_status_ins_step THEN
370: gme_common_pvt.log_message ('GME_INSERT_BATCH_STEP_SUCCESS');
371:
372: /* End Bug 2397077 */
373: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
374: gme_debug.put_line ('End Insert_Batch_Step');
375: END IF;
376: EXCEPTION
377: WHEN error_inv_status_ins_step THEN
378: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
374: gme_debug.put_line ('End Insert_Batch_Step');
375: END IF;
376: EXCEPTION
377: WHEN error_inv_status_ins_step THEN
378: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
379: gme_debug.put_line ('insert_step --> invalid batch status');
380: END IF;
381:
382: x_return_status := fnd_api.g_ret_sts_error;
375: END IF;
376: EXCEPTION
377: WHEN error_inv_status_ins_step THEN
378: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
379: gme_debug.put_line ('insert_step --> invalid batch status');
380: END IF;
381:
382: x_return_status := fnd_api.g_ret_sts_error;
383: gme_common_pvt.log_message ('GME_INV_STATUS_INSERT_STEP');
381:
382: x_return_status := fnd_api.g_ret_sts_error;
383: gme_common_pvt.log_message ('GME_INV_STATUS_INSERT_STEP');
384: WHEN error_no_oprn_defined THEN
385: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
386: gme_debug.put_line ('insert_step --> no oprn passed');
387: END IF;
388:
389: x_return_status := fnd_api.g_ret_sts_error;
382: x_return_status := fnd_api.g_ret_sts_error;
383: gme_common_pvt.log_message ('GME_INV_STATUS_INSERT_STEP');
384: WHEN error_no_oprn_defined THEN
385: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
386: gme_debug.put_line ('insert_step --> no oprn passed');
387: END IF;
388:
389: x_return_status := fnd_api.g_ret_sts_error;
390: gme_common_pvt.log_message ('GME_NO_OPRN_DEFINED');
390: gme_common_pvt.log_message ('GME_NO_OPRN_DEFINED');
391: WHEN error_gmd_fetch_oprn THEN
392: x_return_status := l_return_status;
393: WHEN error_step_qty_lthan_zero THEN
394: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
395: gme_debug.put_line ('insert_step --> step qty < zero');
396: END IF;
397:
398: x_return_status := fnd_api.g_ret_sts_error;
391: WHEN error_gmd_fetch_oprn THEN
392: x_return_status := l_return_status;
393: WHEN error_step_qty_lthan_zero THEN
394: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
395: gme_debug.put_line ('insert_step --> step qty < zero');
396: END IF;
397:
398: x_return_status := fnd_api.g_ret_sts_error;
399: gme_common_pvt.log_message ('GME_STEP_QTY_LTHAN_ZERO');
398: x_return_status := fnd_api.g_ret_sts_error;
399: gme_common_pvt.log_message ('GME_STEP_QTY_LTHAN_ZERO');
400: /* Punit Kumar */
401: WHEN error_no_organization_id THEN
402: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
403: gme_debug.put_line ('insert_step --> no ORGANIZATION_ID supplied');
404: END IF;
405:
406: x_return_status := fnd_api.g_ret_sts_error;
399: gme_common_pvt.log_message ('GME_STEP_QTY_LTHAN_ZERO');
400: /* Punit Kumar */
401: WHEN error_no_organization_id THEN
402: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
403: gme_debug.put_line ('insert_step --> no ORGANIZATION_ID supplied');
404: END IF;
405:
406: x_return_status := fnd_api.g_ret_sts_error;
407: WHEN error_create_batch_step THEN
404: END IF;
405:
406: x_return_status := fnd_api.g_ret_sts_error;
407: WHEN error_create_batch_step THEN
408: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
409: gme_debug.put_line
410: (fnd_msg_pub.get (p_encoded => fnd_api.g_false) );
411: END IF;
412:
405:
406: x_return_status := fnd_api.g_ret_sts_error;
407: WHEN error_create_batch_step THEN
408: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
409: gme_debug.put_line
410: (fnd_msg_pub.get (p_encoded => fnd_api.g_false) );
411: END IF;
412:
413: x_return_status := l_return_status;
416: WHEN invalid_text_code THEN -- nsinghi bug#5202911
417: x_return_status := fnd_api.g_ret_sts_error;
418: gme_common_pvt.log_message ('GME_INVALID_TEXT_CODE');
419: WHEN batch_step_fetch_error OR batch_step_update_error THEN
420: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
421: gme_debug.put_line
422: (fnd_msg_pub.get (p_encoded => fnd_api.g_false) );
423: END IF;
424:
417: x_return_status := fnd_api.g_ret_sts_error;
418: gme_common_pvt.log_message ('GME_INVALID_TEXT_CODE');
419: WHEN batch_step_fetch_error OR batch_step_update_error THEN
420: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
421: gme_debug.put_line
422: (fnd_msg_pub.get (p_encoded => fnd_api.g_false) );
423: END IF;
424:
425: x_return_status := fnd_api.g_ret_sts_error;
427: NULL;
428: WHEN expected_error OR invalid_oprn_effectivity THEN --Bug#6408612
429: x_return_status := fnd_api.g_ret_sts_error;
430: WHEN OTHERS THEN
431: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
432: gme_debug.put_line ( 'GME insert_step API -- when others '
433: || SQLERRM);
434: END IF;
435:
428: WHEN expected_error OR invalid_oprn_effectivity THEN --Bug#6408612
429: x_return_status := fnd_api.g_ret_sts_error;
430: WHEN OTHERS THEN
431: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
432: gme_debug.put_line ( 'GME insert_step API -- when others '
433: || SQLERRM);
434: END IF;
435:
436: x_return_status := fnd_api.g_ret_sts_unexp_error;
486: error_rsrc_diff_um_type EXCEPTION;
487: error_calc_charge_conv EXCEPTION; --Bug#5231180
488: l_temp_qty NUMBER; --Bug#5231180
489: BEGIN
490: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
491: gme_debug.put_line ('Insert_batch_step... BEGIN calc max cap');
492: END IF;
493:
494: x_return_status := fnd_api.g_ret_sts_success;
487: error_calc_charge_conv EXCEPTION; --Bug#5231180
488: l_temp_qty NUMBER; --Bug#5231180
489: BEGIN
490: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
491: gme_debug.put_line ('Insert_batch_step... BEGIN calc max cap');
492: END IF;
493:
494: x_return_status := fnd_api.g_ret_sts_success;
495: l_first := TRUE;
500: -- Initialize in case there is an error...
501: p_max_capacity := NULL;
502: p_capacity_uom := NULL;
503:
504: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
505: gme_debug.put_line ('Resource Count: '||p_recipe_rout_resc.COUNT);
506: END IF;
507:
508: FOR i IN 1 .. p_recipe_rout_resc.COUNT LOOP
501: p_max_capacity := NULL;
502: p_capacity_uom := NULL;
503:
504: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
505: gme_debug.put_line ('Resource Count: '||p_recipe_rout_resc.COUNT);
506: END IF;
507:
508: FOR i IN 1 .. p_recipe_rout_resc.COUNT LOOP
509: l_max_cap := NULL;
512: OPEN cur_get_rsrc (p_recipe_rout_resc (i).resources);
513: FETCH cur_get_rsrc INTO l_max_cap, l_cap_uom;
514: CLOSE cur_get_rsrc;
515:
516: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
517: gme_debug.put_line ('Max Capacity: '||l_max_cap);
518: gme_debug.put_line ('Resource Capacity UOM: '||l_cap_uom);
519: END IF;
520:
513: FETCH cur_get_rsrc INTO l_max_cap, l_cap_uom;
514: CLOSE cur_get_rsrc;
515:
516: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
517: gme_debug.put_line ('Max Capacity: '||l_max_cap);
518: gme_debug.put_line ('Resource Capacity UOM: '||l_cap_uom);
519: END IF;
520:
521: -- Did we find something? not deleted and capacity constraining...
514: CLOSE cur_get_rsrc;
515:
516: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
517: gme_debug.put_line ('Max Capacity: '||l_max_cap);
518: gme_debug.put_line ('Resource Capacity UOM: '||l_cap_uom);
519: END IF;
520:
521: -- Did we find something? not deleted and capacity constraining...
522: IF (l_max_cap IS NOT NULL AND l_cap_uom IS NOT NULL) THEN
542: END IF;
543: ELSE
544: -- can't compare the capacity... this will cause the max cap and cap_UOM to
545: -- be returned as NULL.
546: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
547: gme_debug.put_line('GME insert_step API calc_max_cap --> resources in diff UOM types...');
548: END IF;
549: --Bug#5231180 used gme_common_pvt to log the message
550: gme_common_pvt.log_message('GME_RSRC_DIFF_UM_TYPE');
543: ELSE
544: -- can't compare the capacity... this will cause the max cap and cap_UOM to
545: -- be returned as NULL.
546: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
547: gme_debug.put_line('GME insert_step API calc_max_cap --> resources in diff UOM types...');
548: END IF;
549: --Bug#5231180 used gme_common_pvt to log the message
550: gme_common_pvt.log_message('GME_RSRC_DIFF_UM_TYPE');
551: RAISE error_rsrc_diff_um_type;
582:
583: p_capacity_uom := p_step_qty_uom;
584: --Bug#5231180 End
585:
586: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
587: gme_debug.put_line('Max Capcity calculated: '||p_max_capacity);
588: gme_debug.put_line('Capcity UOM : '||p_capacity_uom);
589: gme_debug.put_line ('Insert_batch_step... END calc max cap');
590: END IF;
583: p_capacity_uom := p_step_qty_uom;
584: --Bug#5231180 End
585:
586: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
587: gme_debug.put_line('Max Capcity calculated: '||p_max_capacity);
588: gme_debug.put_line('Capcity UOM : '||p_capacity_uom);
589: gme_debug.put_line ('Insert_batch_step... END calc max cap');
590: END IF;
591: EXCEPTION
584: --Bug#5231180 End
585:
586: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
587: gme_debug.put_line('Max Capcity calculated: '||p_max_capacity);
588: gme_debug.put_line('Capcity UOM : '||p_capacity_uom);
589: gme_debug.put_line ('Insert_batch_step... END calc max cap');
590: END IF;
591: EXCEPTION
592: WHEN error_rsrc_diff_um_type OR error_calc_charge_conv THEN
585:
586: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
587: gme_debug.put_line('Max Capcity calculated: '||p_max_capacity);
588: gme_debug.put_line('Capcity UOM : '||p_capacity_uom);
589: gme_debug.put_line ('Insert_batch_step... END calc max cap');
590: END IF;
591: EXCEPTION
592: WHEN error_rsrc_diff_um_type OR error_calc_charge_conv THEN
593: x_return_status := fnd_api.g_ret_sts_error;
591: EXCEPTION
592: WHEN error_rsrc_diff_um_type OR error_calc_charge_conv THEN
593: x_return_status := fnd_api.g_ret_sts_error;
594: WHEN OTHERS THEN
595: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
596: gme_debug.put_line
597: ( 'GME insert_step API calc_max_cap --> when others '
598: || SQLERRM);
599: END IF;
592: WHEN error_rsrc_diff_um_type OR error_calc_charge_conv THEN
593: x_return_status := fnd_api.g_ret_sts_error;
594: WHEN OTHERS THEN
595: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
596: gme_debug.put_line
597: ( 'GME insert_step API calc_max_cap --> when others '
598: || SQLERRM);
599: END IF;
600: