15: l_api_name CONSTANT VARCHAR2 (30) := 'insert_material_line';
16: l_material_detail_rec gme_material_details%ROWTYPE;
17: l_batchstep_items gme_batch_step_items%ROWTYPE;
18: l_batch_step_rec gme_batch_steps%ROWTYPE;
19: l_out_material_detail_tbl gme_common_pvt.material_details_tab;
20: l_material_detail_tbl gme_common_pvt.material_details_tab;
21: l_recipe_id NUMBER;
22: l_message_count NUMBER;
23: l_message_list VARCHAR2 (2000);
16: l_material_detail_rec gme_material_details%ROWTYPE;
17: l_batchstep_items gme_batch_step_items%ROWTYPE;
18: l_batch_step_rec gme_batch_steps%ROWTYPE;
19: l_out_material_detail_tbl gme_common_pvt.material_details_tab;
20: l_material_detail_tbl gme_common_pvt.material_details_tab;
21: l_recipe_id NUMBER;
22: l_message_count NUMBER;
23: l_message_list VARCHAR2 (2000);
24: l_proc VARCHAR2 (100);
56:
57: -- renumber subsequent lines
58: UPDATE gme_material_details
59: SET line_no = line_no + 1
60: ,last_updated_by = gme_common_pvt.g_user_ident
61: ,last_update_date = gme_common_pvt.g_timestamp
62: ,last_update_login = gme_common_pvt.g_login_id
63: WHERE batch_id = p_material_detail_rec.batch_id
64: AND line_type = p_material_detail_rec.line_type
57: -- renumber subsequent lines
58: UPDATE gme_material_details
59: SET line_no = line_no + 1
60: ,last_updated_by = gme_common_pvt.g_user_ident
61: ,last_update_date = gme_common_pvt.g_timestamp
62: ,last_update_login = gme_common_pvt.g_login_id
63: WHERE batch_id = p_material_detail_rec.batch_id
64: AND line_type = p_material_detail_rec.line_type
65: AND line_no >= p_material_detail_rec.line_no;
58: UPDATE gme_material_details
59: SET line_no = line_no + 1
60: ,last_updated_by = gme_common_pvt.g_user_ident
61: ,last_update_date = gme_common_pvt.g_timestamp
62: ,last_update_login = gme_common_pvt.g_login_id
63: WHERE batch_id = p_material_detail_rec.batch_id
64: AND line_type = p_material_detail_rec.line_type
65: AND line_no >= p_material_detail_rec.line_no;
66:
82:
83: -- Bug 10046018 - set the defaults properly.
84: IF (x_material_detail_rec.subinventory IS NULL OR x_material_detail_rec.locator_id IS NULL) THEN
85: IF (x_material_detail_rec.line_type = -1) THEN
86: gme_common_pvt.get_supply_defaults
87: (p_organization_id => x_material_detail_rec.organization_id
88: ,p_inventory_item_id => x_material_detail_rec.inventory_item_id
89: ,x_subinventory => l_subinventory
90: ,x_locator_id => l_locator_id
89: ,x_subinventory => l_subinventory
90: ,x_locator_id => l_locator_id
91: ,x_return_status => l_return_status);
92: ELSE
93: gme_common_pvt.get_yield_defaults
94: (p_organization_id => x_material_detail_rec.organization_id
95: ,p_inventory_item_id => x_material_detail_rec.inventory_item_id
96: ,p_line_type => x_material_detail_rec.line_type
97: ,x_subinventory => l_subinventory
146: END IF;
147:
148: l_material_detail_rec := x_material_detail_rec;
149:
150: gme_common_pvt.calc_mtl_req_date
151: (p_batch_header_rec => p_batch_header_rec
152: ,p_batchstep_rec => p_batch_step_rec
153: ,p_mtl_dtl_rec => l_material_detail_rec
154: ,x_mtl_req_date => x_material_detail_rec.material_requirement_date
154: ,x_mtl_req_date => x_material_detail_rec.material_requirement_date
155: ,x_return_status => x_return_status);
156:
157: IF x_return_status <> fnd_api.g_ret_sts_success THEN
158: l_proc := 'gme_common_pvt.calc_mtl_req_date';
159: RAISE error_processing;
160: END IF;
161:
162: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
164: || TO_CHAR(x_material_detail_rec.material_requirement_date,
165: gme_material_detail_pvt.g_date_fmt));
166: END IF;
167: /* Bug 4866700 added update inventory check */
168: IF x_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing AND
169: x_material_detail_rec.phantom_type = 0 AND
170: p_batch_header_rec.update_inventory_ind = 'Y' THEN
171: l_material_detail_tbl (1) := x_material_detail_rec;
172:
172:
173: -- add material line into invisible move order
174: gme_move_orders_pvt.create_move_order_lines
175: (p_move_order_header_id => p_batch_header_rec.move_order_header_id
176: ,p_move_order_type => gme_common_pvt.g_invis_move_order_type
177: ,p_material_details_tbl => l_material_detail_tbl
178: ,x_material_details_tbl => l_out_material_detail_tbl
179: ,x_trolin_tbl => l_trolin_tbl
180: ,x_return_status => x_return_status);
288: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
289: gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': ' || l_proc|| ' error returned');
290: END IF;
291: WHEN error_dbl THEN
292: gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
293: x_return_status := FND_API.g_ret_sts_unexp_error;
294:
295: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
296: gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': '
297: || l_proc|| ' unexpected error: '|| SQLERRM);
298: END IF;
299:
300: WHEN error_dispensing THEN
301: gme_common_pvt.log_message ('GME_DISPENSE_NON_RESERVE');
302: x_return_status := l_return_status;
303: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
304: gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': '
305: || l_proc|| 'Dispensing error returned ');
333: x_return_status := FND_API.G_RET_STS_SUCCESS;
334:
335: --Bug#5078853 added check for invalid batch type
336: IF p_batch_header_rec.batch_type <> 0 THEN
337: gme_common_pvt.log_message ('GME_INV_BATCH_TYPE_OPER');
338: RAISE validation_error;
339: END IF;
340:
341: IF (p_batch_header_rec.batch_status NOT IN
338: RAISE validation_error;
339: END IF;
340:
341: IF (p_batch_header_rec.batch_status NOT IN
342: (gme_common_pvt.g_batch_pending
343: ,gme_common_pvt.g_batch_wip
344: ,gme_common_pvt.g_batch_completed )) THEN
345: gme_common_pvt.log_message ('GME_INV_BATCH_STATUS_OPER');
346: RAISE validation_error;
339: END IF;
340:
341: IF (p_batch_header_rec.batch_status NOT IN
342: (gme_common_pvt.g_batch_pending
343: ,gme_common_pvt.g_batch_wip
344: ,gme_common_pvt.g_batch_completed )) THEN
345: gme_common_pvt.log_message ('GME_INV_BATCH_STATUS_OPER');
346: RAISE validation_error;
347: END IF;
340:
341: IF (p_batch_header_rec.batch_status NOT IN
342: (gme_common_pvt.g_batch_pending
343: ,gme_common_pvt.g_batch_wip
344: ,gme_common_pvt.g_batch_completed )) THEN
345: gme_common_pvt.log_message ('GME_INV_BATCH_STATUS_OPER');
346: RAISE validation_error;
347: END IF;
348:
341: IF (p_batch_header_rec.batch_status NOT IN
342: (gme_common_pvt.g_batch_pending
343: ,gme_common_pvt.g_batch_wip
344: ,gme_common_pvt.g_batch_completed )) THEN
345: gme_common_pvt.log_message ('GME_INV_BATCH_STATUS_OPER');
346: RAISE validation_error;
347: END IF;
348:
349: IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
346: RAISE validation_error;
347: END IF;
348:
349: IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
350: IF (p_batch_step_rec.step_status NOT IN (gme_common_pvt.g_step_pending
351: ,gme_common_pvt.g_step_wip
352: ,gme_common_pvt.g_step_completed)) THEN
353: gme_common_pvt.log_message('PC_STEP_STATUS_ERR');
354: RAISE validation_error;
347: END IF;
348:
349: IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
350: IF (p_batch_step_rec.step_status NOT IN (gme_common_pvt.g_step_pending
351: ,gme_common_pvt.g_step_wip
352: ,gme_common_pvt.g_step_completed)) THEN
353: gme_common_pvt.log_message('PC_STEP_STATUS_ERR');
354: RAISE validation_error;
355: END IF;
348:
349: IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
350: IF (p_batch_step_rec.step_status NOT IN (gme_common_pvt.g_step_pending
351: ,gme_common_pvt.g_step_wip
352: ,gme_common_pvt.g_step_completed)) THEN
353: gme_common_pvt.log_message('PC_STEP_STATUS_ERR');
354: RAISE validation_error;
355: END IF;
356: END IF;
349: IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
350: IF (p_batch_step_rec.step_status NOT IN (gme_common_pvt.g_step_pending
351: ,gme_common_pvt.g_step_wip
352: ,gme_common_pvt.g_step_completed)) THEN
353: gme_common_pvt.log_message('PC_STEP_STATUS_ERR');
354: RAISE validation_error;
355: END IF;
356: END IF;
357:
445: RAISE val_error;
446: END IF;
447:
448: -- ByProduct_Type
449: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod THEN
450: validate_byproduct_type
451: (p_byproduct_type => p_material_detail_rec.by_product_type
452: ,x_return_status => x_return_status);
453:
486:
487: /* Bug#5078853 moved this code over here */
488: -- Release_Type
489: -- following should be validated in form
490: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod AND
491: p_material_detail_rec.by_product_type = 'S' THEN
492: x_material_detail_rec.release_type := 1;
493: ELSIF p_material_detail_rec.release_type IS NULL THEN
494: x_material_detail_rec.release_type := gme_common_pvt.g_release_type;
490: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod AND
491: p_material_detail_rec.by_product_type = 'S' THEN
492: x_material_detail_rec.release_type := 1;
493: ELSIF p_material_detail_rec.release_type IS NULL THEN
494: x_material_detail_rec.release_type := gme_common_pvt.g_release_type;
495: ELSE
496: validate_release_type
497: (p_material_detail_rec => p_material_detail_rec
498: ,p_release_type => p_material_detail_rec.release_type
505: END IF;
506:
507: -- Calculate status of material based on release type, association and batch/step status
508: l_status := p_batch_header_rec.batch_status;
509: IF p_material_detail_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
510: p_batch_step_rec.batchstep_id IS NOT NULL THEN
511: l_status := p_batch_step_rec.step_status;
512: END IF;
513:
511: l_status := p_batch_step_rec.step_status;
512: END IF;
513:
514: -- Plan_Qty
515: IF l_status = gme_common_pvt.g_batch_pending THEN
516: validate_plan_qty (p_plan_qty => p_material_detail_rec.plan_qty
517: ,x_return_status => x_return_status);
518:
519: IF x_return_status <> fnd_api.g_ret_sts_success THEN
524: x_material_detail_rec.plan_qty := 0;
525: END IF;
526:
527: -- WIP_Plan_Qty
528: IF l_status <> gme_common_pvt.g_batch_wip THEN
529: x_material_detail_rec.wip_plan_qty := NULL;
530: ELSE
531: validate_wip_plan_qty
532: (p_wip_plan_qty => p_material_detail_rec.wip_plan_qty
546: p_material_detail_rec.actual_qty = 0 THEN
547:
548: x_material_detail_rec.actual_qty := 0;
549: ELSE
550: IF l_status NOT IN ( gme_common_pvt.g_batch_wip,
551: gme_common_pvt.g_batch_completed ) THEN
552: gme_common_pvt.log_message ('GME_INV_STAT_UPD_ACT');
553: RAISE val_error;
554: END IF;
547:
548: x_material_detail_rec.actual_qty := 0;
549: ELSE
550: IF l_status NOT IN ( gme_common_pvt.g_batch_wip,
551: gme_common_pvt.g_batch_completed ) THEN
552: gme_common_pvt.log_message ('GME_INV_STAT_UPD_ACT');
553: RAISE val_error;
554: END IF;
555: validate_actual_qty (p_actual_qty => x_material_detail_rec.actual_qty
548: x_material_detail_rec.actual_qty := 0;
549: ELSE
550: IF l_status NOT IN ( gme_common_pvt.g_batch_wip,
551: gme_common_pvt.g_batch_completed ) THEN
552: gme_common_pvt.log_message ('GME_INV_STAT_UPD_ACT');
553: RAISE val_error;
554: END IF;
555: validate_actual_qty (p_actual_qty => x_material_detail_rec.actual_qty
556: ,x_return_status => x_return_status);
563:
564:
565: /* Bug#5078853 modified validation for scrap factor */
566: -- Scrap_Factor
567: IF p_batch_header_rec.batch_status <> gme_common_pvt.g_batch_pending OR
568: p_material_detail_rec.scrap_factor IS NULL OR
569: p_material_detail_rec.scrap_factor = 0 OR
570: p_material_detail_rec.line_type <> gme_common_pvt.g_line_type_ing THEN
571: x_material_detail_rec.scrap_factor := 0;
566: -- Scrap_Factor
567: IF p_batch_header_rec.batch_status <> gme_common_pvt.g_batch_pending OR
568: p_material_detail_rec.scrap_factor IS NULL OR
569: p_material_detail_rec.scrap_factor = 0 OR
570: p_material_detail_rec.line_type <> gme_common_pvt.g_line_type_ing THEN
571: x_material_detail_rec.scrap_factor := 0;
572: ELSE
573: validate_scrap_factor(p_scrap => p_material_detail_rec.scrap_factor
574: ,x_return_status => x_return_status);
634: x_material_detail_rec.rounding_direction := NULL;
635: END IF;
636:
637: -- Cost_Alloc
638: IF x_material_detail_rec.line_type = gme_common_pvt.g_line_type_prod THEN
639: -- validate 0 <= cost_alloc <= 1
640: -- at save_batch, check that sum(cost_alloc for all products) <= 1
641: validate_cost_alloc
642: (p_material_detail_rec => p_material_detail_rec
650: x_material_detail_rec.cost_alloc := NULL;
651: END IF;
652:
653: -- Phantom_Type
654: IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending AND
655: x_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
656: validate_phantom_type
657: (p_phantom_type => p_material_detail_rec.phantom_type
658: ,x_return_status => x_return_status);
651: END IF;
652:
653: -- Phantom_Type
654: IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending AND
655: x_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
656: validate_phantom_type
657: (p_phantom_type => p_material_detail_rec.phantom_type
658: ,x_return_status => x_return_status);
659:
665: x_material_detail_rec.phantom_type := 0;
666: END IF;
667:
668: -- Contribute_Yield_Ind
669: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
670: validate_contr_yield_ind
671: (p_contr_yield_ind => p_material_detail_rec.contribute_yield_ind
672: ,x_return_status => x_return_status);
673:
698: x_material_detail_rec.subinventory := NULL;
699: x_material_detail_rec.locator_id := NULL;
700: ELSIF p_material_detail_rec.subinventory IS NULL THEN
701: --Bug#5078853 Begin get the default sub inv and locator either from gme_parameters or from Item Master
702: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
703: gme_common_pvt.get_supply_defaults ( p_organization_id => x_material_detail_rec.organization_id
704: ,p_inventory_item_id => p_material_detail_rec.inventory_item_id
705: ,x_subinventory => x_material_detail_rec.subinventory
706: ,x_locator_id => x_material_detail_rec.locator_id
699: x_material_detail_rec.locator_id := NULL;
700: ELSIF p_material_detail_rec.subinventory IS NULL THEN
701: --Bug#5078853 Begin get the default sub inv and locator either from gme_parameters or from Item Master
702: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
703: gme_common_pvt.get_supply_defaults ( p_organization_id => x_material_detail_rec.organization_id
704: ,p_inventory_item_id => p_material_detail_rec.inventory_item_id
705: ,x_subinventory => x_material_detail_rec.subinventory
706: ,x_locator_id => x_material_detail_rec.locator_id
707: ,x_return_status => x_return_status );
709: l_val_proc := 'get_supply_defaults';
710: RAISE val_error;
711: END IF;
712: ELSE
713: gme_common_pvt.get_yield_defaults ( p_organization_id => x_material_detail_rec.organization_id
714: ,p_inventory_item_id => p_material_detail_rec.inventory_item_id
715: ,p_line_type => p_material_detail_rec.line_type
716: ,x_subinventory => x_material_detail_rec.subinventory
717: ,x_locator_id => x_material_detail_rec.locator_id
737: x_material_detail_rec.locator_id := NULL;
738: ELSIF p_material_detail_rec.locator_id IS NULL THEN
739: /* Bug#5078853 Begin if we come here then locator id is not being passed from public api
740: so get the default locator id to validate with passed sub inv */
741: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
742: gme_common_pvt.get_supply_defaults ( p_organization_id => x_material_detail_rec.organization_id
743: ,p_inventory_item_id => p_material_detail_rec.inventory_item_id
744: ,x_subinventory => l_subinventory
745: ,x_locator_id => x_material_detail_rec.locator_id
738: ELSIF p_material_detail_rec.locator_id IS NULL THEN
739: /* Bug#5078853 Begin if we come here then locator id is not being passed from public api
740: so get the default locator id to validate with passed sub inv */
741: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
742: gme_common_pvt.get_supply_defaults ( p_organization_id => x_material_detail_rec.organization_id
743: ,p_inventory_item_id => p_material_detail_rec.inventory_item_id
744: ,x_subinventory => l_subinventory
745: ,x_locator_id => x_material_detail_rec.locator_id
746: ,x_return_status => x_return_status );
748: l_val_proc := 'get_supply_defaults';
749: RAISE val_error;
750: END IF;
751: ELSE
752: gme_common_pvt.get_yield_defaults ( p_organization_id => x_material_detail_rec.organization_id
753: ,p_inventory_item_id => p_material_detail_rec.inventory_item_id
754: ,p_line_type => p_material_detail_rec.line_type
755: ,x_subinventory => l_subinventory
756: ,x_locator_id => x_material_detail_rec.locator_id
779: END IF; -- IF x_material_detail_rec.locator_id IS NOT NULL ...
780: END IF; -- IF x_material_detail_rec.subinventory = ...
781:
782: /* Bug#5078853 added the following call for flex field validation
783: gme_common_pvt.g_flex_validate_prof has to be set in public API to enforce flex field validation */
784: gme_validate_flex_fld_pvt.validate_flex_material_details
785: ( p_material_detail_rec => p_material_detail_rec
786: ,x_material_detail_rec => x_material_detail_rec
787: ,x_return_status => x_return_status);
947: OPEN item_no_cursor (p_batch_header_rec.organization_id, l_item_rec.inventory_item_id);
948: FETCH item_no_cursor INTO l_item_no;
949: CLOSE item_no_cursor;
950:
951: gme_common_pvt.log_message ('GME_UPD_ACTUAL_QTY_ERR'
952: ,'ITEM_NO'
953: ,l_item_no);
954: x_return_status := fnd_api.g_ret_sts_error;
955: WHEN open_actual_qty_unexp_error THEN
1001: SELECT transaction_id1
1002: FROM gme_transaction_pairs
1003: WHERE batch_id = v_batch_id
1004: AND material_detail_id = v_material_detail_id
1005: AND pair_type = gme_common_pvt.g_pairs_reversal_type);
1006:
1007: CURSOR cur_get_mtl_trxn_lot (v_trans_id NUMBER)
1008: IS
1009: SELECT COUNT (1)
1085: * lot - 1 transaction
1086: */
1087:
1088: l_release_type := l_mtl_dtl_rec.release_type;
1089: IF l_release_type = gme_common_pvt.g_mtl_autobystep_release AND
1090: p_batchstep_id IS NULL THEN
1091: l_release_type := gme_common_pvt.g_mtl_auto_release;
1092: END IF;
1093:
1087:
1088: l_release_type := l_mtl_dtl_rec.release_type;
1089: IF l_release_type = gme_common_pvt.g_mtl_autobystep_release AND
1090: p_batchstep_id IS NULL THEN
1091: l_release_type := gme_common_pvt.g_mtl_auto_release;
1092: END IF;
1093:
1094: -- if it's auto by step, ensure we have the step status...
1095: IF l_release_type = gme_common_pvt.g_mtl_autobystep_release THEN
1091: l_release_type := gme_common_pvt.g_mtl_auto_release;
1092: END IF;
1093:
1094: -- if it's auto by step, ensure we have the step status...
1095: IF l_release_type = gme_common_pvt.g_mtl_autobystep_release THEN
1096: IF p_step_status IS NULL THEN
1097: OPEN cur_get_step_status(p_batchstep_id);
1098: FETCH cur_get_step_status INTO l_step_status;
1099: CLOSE cur_get_step_status;
1105: -- Not open for products/byproducts that are not in completed state (auto) or
1106: -- wip/completed (manual/incremental)
1107:
1108: IF l_mtl_dtl_rec.line_type IN
1109: (gme_common_pvt.g_line_type_prod
1110: ,gme_common_pvt.g_line_type_byprod)
1111: AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1112: AND l_step_status <> gme_common_pvt.g_step_completed)
1113: OR (l_release_type = gme_common_pvt.g_mtl_auto_release
1106: -- wip/completed (manual/incremental)
1107:
1108: IF l_mtl_dtl_rec.line_type IN
1109: (gme_common_pvt.g_line_type_prod
1110: ,gme_common_pvt.g_line_type_byprod)
1111: AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1112: AND l_step_status <> gme_common_pvt.g_step_completed)
1113: OR (l_release_type = gme_common_pvt.g_mtl_auto_release
1114: AND p_batch_status <> gme_common_pvt.g_batch_completed)
1107:
1108: IF l_mtl_dtl_rec.line_type IN
1109: (gme_common_pvt.g_line_type_prod
1110: ,gme_common_pvt.g_line_type_byprod)
1111: AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1112: AND l_step_status <> gme_common_pvt.g_step_completed)
1113: OR (l_release_type = gme_common_pvt.g_mtl_auto_release
1114: AND p_batch_status <> gme_common_pvt.g_batch_completed)
1115: OR (l_release_type IN (gme_common_pvt.g_mtl_manual_release,
1108: IF l_mtl_dtl_rec.line_type IN
1109: (gme_common_pvt.g_line_type_prod
1110: ,gme_common_pvt.g_line_type_byprod)
1111: AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1112: AND l_step_status <> gme_common_pvt.g_step_completed)
1113: OR (l_release_type = gme_common_pvt.g_mtl_auto_release
1114: AND p_batch_status <> gme_common_pvt.g_batch_completed)
1115: OR (l_release_type IN (gme_common_pvt.g_mtl_manual_release,
1116: gme_common_pvt.g_mtl_incremental_release)
1109: (gme_common_pvt.g_line_type_prod
1110: ,gme_common_pvt.g_line_type_byprod)
1111: AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1112: AND l_step_status <> gme_common_pvt.g_step_completed)
1113: OR (l_release_type = gme_common_pvt.g_mtl_auto_release
1114: AND p_batch_status <> gme_common_pvt.g_batch_completed)
1115: OR (l_release_type IN (gme_common_pvt.g_mtl_manual_release,
1116: gme_common_pvt.g_mtl_incremental_release)
1117: AND p_batch_status NOT IN (gme_common_pvt.g_batch_wip
1110: ,gme_common_pvt.g_line_type_byprod)
1111: AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1112: AND l_step_status <> gme_common_pvt.g_step_completed)
1113: OR (l_release_type = gme_common_pvt.g_mtl_auto_release
1114: AND p_batch_status <> gme_common_pvt.g_batch_completed)
1115: OR (l_release_type IN (gme_common_pvt.g_mtl_manual_release,
1116: gme_common_pvt.g_mtl_incremental_release)
1117: AND p_batch_status NOT IN (gme_common_pvt.g_batch_wip
1118: ,gme_common_pvt.g_batch_completed))
1111: AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1112: AND l_step_status <> gme_common_pvt.g_step_completed)
1113: OR (l_release_type = gme_common_pvt.g_mtl_auto_release
1114: AND p_batch_status <> gme_common_pvt.g_batch_completed)
1115: OR (l_release_type IN (gme_common_pvt.g_mtl_manual_release,
1116: gme_common_pvt.g_mtl_incremental_release)
1117: AND p_batch_status NOT IN (gme_common_pvt.g_batch_wip
1118: ,gme_common_pvt.g_batch_completed))
1119: ) THEN
1112: AND l_step_status <> gme_common_pvt.g_step_completed)
1113: OR (l_release_type = gme_common_pvt.g_mtl_auto_release
1114: AND p_batch_status <> gme_common_pvt.g_batch_completed)
1115: OR (l_release_type IN (gme_common_pvt.g_mtl_manual_release,
1116: gme_common_pvt.g_mtl_incremental_release)
1117: AND p_batch_status NOT IN (gme_common_pvt.g_batch_wip
1118: ,gme_common_pvt.g_batch_completed))
1119: ) THEN
1120: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1113: OR (l_release_type = gme_common_pvt.g_mtl_auto_release
1114: AND p_batch_status <> gme_common_pvt.g_batch_completed)
1115: OR (l_release_type IN (gme_common_pvt.g_mtl_manual_release,
1116: gme_common_pvt.g_mtl_incremental_release)
1117: AND p_batch_status NOT IN (gme_common_pvt.g_batch_wip
1118: ,gme_common_pvt.g_batch_completed))
1119: ) THEN
1120: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1121: gme_debug.put_line
1114: AND p_batch_status <> gme_common_pvt.g_batch_completed)
1115: OR (l_release_type IN (gme_common_pvt.g_mtl_manual_release,
1116: gme_common_pvt.g_mtl_incremental_release)
1117: AND p_batch_status NOT IN (gme_common_pvt.g_batch_wip
1118: ,gme_common_pvt.g_batch_completed))
1119: ) THEN
1120: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1121: gme_debug.put_line
1122: ( g_pkg_name
1130: RETURN -1;
1131: END IF;
1132:
1133: -- Not open for ingredients that are not in WIP or completed state
1134: IF l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing
1135: AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1136: AND l_step_status NOT IN
1137: (gme_common_pvt.g_step_wip
1138: ,gme_common_pvt.g_step_completed) )
1131: END IF;
1132:
1133: -- Not open for ingredients that are not in WIP or completed state
1134: IF l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing
1135: AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1136: AND l_step_status NOT IN
1137: (gme_common_pvt.g_step_wip
1138: ,gme_common_pvt.g_step_completed) )
1139: -- following for manual, incremental and auto
1133: -- Not open for ingredients that are not in WIP or completed state
1134: IF l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing
1135: AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1136: AND l_step_status NOT IN
1137: (gme_common_pvt.g_step_wip
1138: ,gme_common_pvt.g_step_completed) )
1139: -- following for manual, incremental and auto
1140: OR (p_batch_status NOT IN
1141: (gme_common_pvt.g_batch_wip
1134: IF l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing
1135: AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1136: AND l_step_status NOT IN
1137: (gme_common_pvt.g_step_wip
1138: ,gme_common_pvt.g_step_completed) )
1139: -- following for manual, incremental and auto
1140: OR (p_batch_status NOT IN
1141: (gme_common_pvt.g_batch_wip
1142: ,gme_common_pvt.g_batch_completed) ) ) THEN
1137: (gme_common_pvt.g_step_wip
1138: ,gme_common_pvt.g_step_completed) )
1139: -- following for manual, incremental and auto
1140: OR (p_batch_status NOT IN
1141: (gme_common_pvt.g_batch_wip
1142: ,gme_common_pvt.g_batch_completed) ) ) THEN
1143: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1144: gme_debug.put_line
1145: ( g_pkg_name
1138: ,gme_common_pvt.g_step_completed) )
1139: -- following for manual, incremental and auto
1140: OR (p_batch_status NOT IN
1141: (gme_common_pvt.g_batch_wip
1142: ,gme_common_pvt.g_batch_completed) ) ) THEN
1143: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1144: gme_debug.put_line
1145: ( g_pkg_name
1146: || '.'
1310: OPEN cur_sub_control (l_mtl_dtl_rec.organization_id, l_mtl_dtl_rec.subinventory);
1311: FETCH cur_sub_control INTO l_sub_locator_type;
1312: CLOSE cur_sub_control;
1313:
1314: IF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing) THEN
1315: l_txn_action := gme_common_pvt.g_ing_issue_txn_action;
1316: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_prod) THEN
1317: l_txn_action := gme_common_pvt.g_prod_comp_txn_action;
1318: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_byprod) THEN
1311: FETCH cur_sub_control INTO l_sub_locator_type;
1312: CLOSE cur_sub_control;
1313:
1314: IF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing) THEN
1315: l_txn_action := gme_common_pvt.g_ing_issue_txn_action;
1316: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_prod) THEN
1317: l_txn_action := gme_common_pvt.g_prod_comp_txn_action;
1318: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_byprod) THEN
1319: l_txn_action := gme_common_pvt.g_byprod_comp_txn_action;
1312: CLOSE cur_sub_control;
1313:
1314: IF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing) THEN
1315: l_txn_action := gme_common_pvt.g_ing_issue_txn_action;
1316: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_prod) THEN
1317: l_txn_action := gme_common_pvt.g_prod_comp_txn_action;
1318: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_byprod) THEN
1319: l_txn_action := gme_common_pvt.g_byprod_comp_txn_action;
1320: END IF;
1313:
1314: IF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing) THEN
1315: l_txn_action := gme_common_pvt.g_ing_issue_txn_action;
1316: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_prod) THEN
1317: l_txn_action := gme_common_pvt.g_prod_comp_txn_action;
1318: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_byprod) THEN
1319: l_txn_action := gme_common_pvt.g_byprod_comp_txn_action;
1320: END IF;
1321: --Bug#5129153 If item is revision controlled and revision field is NULL then return -1. Start.
1314: IF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing) THEN
1315: l_txn_action := gme_common_pvt.g_ing_issue_txn_action;
1316: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_prod) THEN
1317: l_txn_action := gme_common_pvt.g_prod_comp_txn_action;
1318: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_byprod) THEN
1319: l_txn_action := gme_common_pvt.g_byprod_comp_txn_action;
1320: END IF;
1321: --Bug#5129153 If item is revision controlled and revision field is NULL then return -1. Start.
1322: IF l_rev_code = 2 AND l_mtl_dtl_rec.revision IS NULL THEN
1315: l_txn_action := gme_common_pvt.g_ing_issue_txn_action;
1316: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_prod) THEN
1317: l_txn_action := gme_common_pvt.g_prod_comp_txn_action;
1318: ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_byprod) THEN
1319: l_txn_action := gme_common_pvt.g_byprod_comp_txn_action;
1320: END IF;
1321: --Bug#5129153 If item is revision controlled and revision field is NULL then return -1. Start.
1322: IF l_rev_code = 2 AND l_mtl_dtl_rec.revision IS NULL THEN
1323: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1333: END IF;
1334: --Bug#5129153 End.
1335: /* Bug 5441643 Added NVL condition for location control code*/
1336: l_eff_locator_control :=
1337: gme_common_pvt.eff_locator_control
1338: (p_organization_id => l_mtl_dtl_rec.organization_id
1339: ,p_org_control => gme_common_pvt.g_org_locator_control
1340: ,p_subinventory => l_mtl_dtl_rec.subinventory
1341: ,p_sub_control => l_sub_locator_type
1335: /* Bug 5441643 Added NVL condition for location control code*/
1336: l_eff_locator_control :=
1337: gme_common_pvt.eff_locator_control
1338: (p_organization_id => l_mtl_dtl_rec.organization_id
1339: ,p_org_control => gme_common_pvt.g_org_locator_control
1340: ,p_subinventory => l_mtl_dtl_rec.subinventory
1341: ,p_sub_control => l_sub_locator_type
1342: ,p_item_control => NVL(l_location_control_code,1)
1343: ,p_item_loc_restrict => l_restrict_locators_code
1429:
1430: l_api_name CONSTANT VARCHAR2 (30) := 'PROCESS_ACTUAL_QTY';
1431:
1432: l_mmt_rec mtl_material_transactions%ROWTYPE;
1433: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
1434:
1435: l_mmti_rec mtl_transactions_interface%ROWTYPE;
1436: l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1437:
1432: l_mmt_rec mtl_material_transactions%ROWTYPE;
1433: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
1434:
1435: l_mmti_rec mtl_transactions_interface%ROWTYPE;
1436: l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1437:
1438: l_cnt NUMBER DEFAULT 0;
1439: l_secondary_qty NUMBER;
1440: l_primary_qty NUMBER;
1610: ( g_pkg_name
1611: || '.'
1612: || l_api_name
1613: ||' transaction header id after create mtl txn'
1614: || gme_common_pvt.g_transaction_header_id);
1615: END IF;
1616: END IF;
1617: ELSE
1618: /* Bug 5681997 added condition to touch txns only if enabled */
1725: x_mmti_rec.transaction_source_id := p_matl_dtl_rec.batch_id;
1726: x_mmti_rec.organization_id := p_matl_dtl_rec.organization_id;
1727:
1728: --getting transaction_type_id depends on the line type
1729: IF l_line_type = gme_common_pvt.g_line_type_ing THEN
1730: x_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1731: ELSIF l_line_type = gme_common_pvt.g_line_type_prod THEN
1732: x_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1733: ELSIF l_line_type = gme_common_pvt.g_line_type_byprod THEN
1726: x_mmti_rec.organization_id := p_matl_dtl_rec.organization_id;
1727:
1728: --getting transaction_type_id depends on the line type
1729: IF l_line_type = gme_common_pvt.g_line_type_ing THEN
1730: x_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1731: ELSIF l_line_type = gme_common_pvt.g_line_type_prod THEN
1732: x_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1733: ELSIF l_line_type = gme_common_pvt.g_line_type_byprod THEN
1734: x_mmti_rec.transaction_type_id := gme_common_pvt.g_byprod_completion;
1727:
1728: --getting transaction_type_id depends on the line type
1729: IF l_line_type = gme_common_pvt.g_line_type_ing THEN
1730: x_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1731: ELSIF l_line_type = gme_common_pvt.g_line_type_prod THEN
1732: x_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1733: ELSIF l_line_type = gme_common_pvt.g_line_type_byprod THEN
1734: x_mmti_rec.transaction_type_id := gme_common_pvt.g_byprod_completion;
1735: END IF;
1728: --getting transaction_type_id depends on the line type
1729: IF l_line_type = gme_common_pvt.g_line_type_ing THEN
1730: x_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1731: ELSIF l_line_type = gme_common_pvt.g_line_type_prod THEN
1732: x_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1733: ELSIF l_line_type = gme_common_pvt.g_line_type_byprod THEN
1734: x_mmti_rec.transaction_type_id := gme_common_pvt.g_byprod_completion;
1735: END IF;
1736: --x_mmti_rec.TRANSACTION_TYPE_ID := 44;
1729: IF l_line_type = gme_common_pvt.g_line_type_ing THEN
1730: x_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1731: ELSIF l_line_type = gme_common_pvt.g_line_type_prod THEN
1732: x_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1733: ELSIF l_line_type = gme_common_pvt.g_line_type_byprod THEN
1734: x_mmti_rec.transaction_type_id := gme_common_pvt.g_byprod_completion;
1735: END IF;
1736: --x_mmti_rec.TRANSACTION_TYPE_ID := 44;
1737: x_mmti_rec.subinventory_code := p_matl_dtl_rec.subinventory;
1730: x_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1731: ELSIF l_line_type = gme_common_pvt.g_line_type_prod THEN
1732: x_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1733: ELSIF l_line_type = gme_common_pvt.g_line_type_byprod THEN
1734: x_mmti_rec.transaction_type_id := gme_common_pvt.g_byprod_completion;
1735: END IF;
1736: --x_mmti_rec.TRANSACTION_TYPE_ID := 44;
1737: x_mmti_rec.subinventory_code := p_matl_dtl_rec.subinventory;
1738: x_mmti_rec.locator_id := p_matl_dtl_rec.locator_id;
1742:
1743: /*Bug#5394232 Begin
1744: if we don't pass any date to this procedure then we have to default the trans date*/
1745: IF x_mmti_rec.transaction_date IS NULL THEN
1746: gme_common_pvt.fetch_trans_date(
1747: p_material_detail_id => p_matl_dtl_rec.material_detail_id
1748: ,p_invoke_mode => 'T'
1749: ,x_trans_date => l_trans_date
1750: ,x_return_status => x_return_status );
1787: END IF;
1788:
1789: -- Calculate Transaction Date
1790: l_release_type := p_matl_dtl_rec.release_type;
1791: IF l_release_type = gme_common_pvt.g_mtl_autobystep_release AND
1792: p_batch_step_rec.batchstep_id IS NULL THEN
1793: l_release_type := gme_common_pvt.g_mtl_auto_release;
1794: END IF;
1795:
1789: -- Calculate Transaction Date
1790: l_release_type := p_matl_dtl_rec.release_type;
1791: IF l_release_type = gme_common_pvt.g_mtl_autobystep_release AND
1792: p_batch_step_rec.batchstep_id IS NULL THEN
1793: l_release_type := gme_common_pvt.g_mtl_auto_release;
1794: END IF;
1795:
1796: IF l_release_type = gme_common_pvt.g_mtl_autobystep_release THEN -- abs... dates come from step
1797: IF p_batch_step_rec.step_status = gme_common_pvt.g_step_completed THEN
1792: p_batch_step_rec.batchstep_id IS NULL THEN
1793: l_release_type := gme_common_pvt.g_mtl_auto_release;
1794: END IF;
1795:
1796: IF l_release_type = gme_common_pvt.g_mtl_autobystep_release THEN -- abs... dates come from step
1797: IF p_batch_step_rec.step_status = gme_common_pvt.g_step_completed THEN
1798: x_mmti_rec.transaction_date := p_batch_step_rec.actual_cmplt_date;
1799: ELSE -- must be WIP
1800: x_mmti_rec.transaction_date := p_batch_step_rec.actual_start_date;
1793: l_release_type := gme_common_pvt.g_mtl_auto_release;
1794: END IF;
1795:
1796: IF l_release_type = gme_common_pvt.g_mtl_autobystep_release THEN -- abs... dates come from step
1797: IF p_batch_step_rec.step_status = gme_common_pvt.g_step_completed THEN
1798: x_mmti_rec.transaction_date := p_batch_step_rec.actual_cmplt_date;
1799: ELSE -- must be WIP
1800: x_mmti_rec.transaction_date := p_batch_step_rec.actual_start_date;
1801: END IF;
1800: x_mmti_rec.transaction_date := p_batch_step_rec.actual_start_date;
1801: END IF;
1802: ELSE -- auto release... dates come from batch
1803: IF p_batch_hdr_rec.batch_status =
1804: gme_common_pvt.g_batch_completed THEN
1805: x_mmti_rec.transaction_date :=
1806: p_batch_hdr_rec.actual_cmplt_date;
1807: ELSE -- must be WIP
1808: x_mmti_rec.transaction_date :=
1925: x_conv_qty := inv_convert.inv_um_convert
1926: (item_id => p_item_id
1927: ,lot_number => p_lot_number
1928: ,organization_id => p_org_id
1929: ,precision => gme_common_pvt.g_precision
1930: ,from_quantity => p_qty
1931: ,from_unit => p_from_um
1932: ,to_unit => p_to_um
1933: ,from_name => NULL
1932: ,to_unit => p_to_um
1933: ,from_name => NULL
1934: ,to_name => NULL);
1935:
1936: -- Note: -99999 should be in gme_common_pvt
1937: IF x_conv_qty = -99999 THEN
1938: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1939: gme_debug.put_line
1940: ( g_pkg_name
2002:
2003: x_return_status := fnd_api.g_ret_sts_success;
2004:
2005: IF p_item_id IS NULL THEN
2006: gme_common_pvt.log_message ('GME_NO_KEYS', 'TABLE_NAME', 'mtl_system_items_b');
2007: RAISE error_get_item;
2008: END IF;
2009:
2010: OPEN cur_get_item_rec (p_org_id, p_item_id);
2011: FETCH cur_get_item_rec INTO x_item_rec;
2012: CLOSE cur_get_item_rec;
2013:
2014: IF x_item_rec.inventory_item_id IS NULL THEN -- not found
2015: gme_common_pvt.log_message ('PM_INVALID_ITEM');
2016:
2017: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2018: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' no record in mtl_system_items_b: ');
2019: gme_debug.put_line(g_pkg_name||'.'||l_api_name||'inventory_item_id = ' ||p_item_id );
2085: -- lab_batch 1 eng item Y OK
2086: -- lab_batch 0 eng item Y N
2087: -- lab_batch 1 eng item N OK
2088: -- lab_batch 0 eng item N OK
2089: IF gme_common_pvt.g_lab_ind = 0 AND x_item_rec.eng_item_flag = 'Y' THEN
2090: l_field := 'eng_item_flag';
2091: RAISE error_validate;
2092: END IF;
2093:
2125: FROM mtl_system_items_kfv
2126: WHERE inventory_item_id = p_item_id
2127: AND organization_id = p_org_id;
2128: --Bug#5078853
2129: gme_common_pvt.log_message ('GME_INV_ITEM_INSERT', 'ITEM_NO', l_field);
2130: x_return_status := fnd_api.g_ret_sts_error;
2131:
2132: WHEN OTHERS THEN
2133: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2181:
2182: -- revision_qty_control_code
2183: -- 1=No revision qty control; 2=Under revision qty control
2184: IF p_item_rec.revision_qty_control_code = 1 THEN
2185: gme_common_pvt.log_message ('GME_NOT_REV_CTRL');
2186: RAISE error_not_revision_control;
2187: END IF;
2188:
2189: OPEN cur_get_revision (p_item_rec.organization_id
2192: FETCH cur_get_revision INTO l_is_revision_found;
2193: CLOSE cur_get_revision;
2194:
2195: IF l_is_revision_found IS NULL OR l_is_revision_found <> 1 THEN
2196: gme_common_pvt.log_message ('GME_REV_NOT_DEFD');
2197: RAISE error_revision_not_found;
2198: END IF;
2199:
2200: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2238: -- value of 1 means Yes; byproducts are available for insert, update and delete
2239: -- value of 2 means No; byproducts are not available
2240: -- if this parameter is set to 2 and the material being inserted is a byproduct, then
2241: -- raise an error
2242: IF gme_common_pvt.g_byprod_active = 2 AND
2243: p_line_type = gme_common_pvt.g_line_type_byprod THEN
2244: fnd_message.set_name ('GMD', 'FM_BYPROD_INACTIVE');
2245: fnd_msg_pub.ADD;
2246: RAISE validation_error;
2239: -- value of 2 means No; byproducts are not available
2240: -- if this parameter is set to 2 and the material being inserted is a byproduct, then
2241: -- raise an error
2242: IF gme_common_pvt.g_byprod_active = 2 AND
2243: p_line_type = gme_common_pvt.g_line_type_byprod THEN
2244: fnd_message.set_name ('GMD', 'FM_BYPROD_INACTIVE');
2245: fnd_msg_pub.ADD;
2246: RAISE validation_error;
2247: END IF;
2246: RAISE validation_error;
2247: END IF;
2248:
2249: IF p_line_type NOT IN
2250: (gme_common_pvt.g_line_type_ing
2251: ,gme_common_pvt.g_line_type_prod
2252: ,gme_common_pvt.g_line_type_byprod) THEN
2253: gme_common_pvt.log_message ('GME_INVALID_LINE_TYPE');
2254: RAISE validation_error;
2247: END IF;
2248:
2249: IF p_line_type NOT IN
2250: (gme_common_pvt.g_line_type_ing
2251: ,gme_common_pvt.g_line_type_prod
2252: ,gme_common_pvt.g_line_type_byprod) THEN
2253: gme_common_pvt.log_message ('GME_INVALID_LINE_TYPE');
2254: RAISE validation_error;
2255: END IF;
2248:
2249: IF p_line_type NOT IN
2250: (gme_common_pvt.g_line_type_ing
2251: ,gme_common_pvt.g_line_type_prod
2252: ,gme_common_pvt.g_line_type_byprod) THEN
2253: gme_common_pvt.log_message ('GME_INVALID_LINE_TYPE');
2254: RAISE validation_error;
2255: END IF;
2256:
2249: IF p_line_type NOT IN
2250: (gme_common_pvt.g_line_type_ing
2251: ,gme_common_pvt.g_line_type_prod
2252: ,gme_common_pvt.g_line_type_byprod) THEN
2253: gme_common_pvt.log_message ('GME_INVALID_LINE_TYPE');
2254: RAISE validation_error;
2255: END IF;
2256:
2257: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2305: FETCH cur_byprod_type INTO l_exists;
2306: CLOSE cur_byprod_type;
2307:
2308: IF l_exists IS NULL OR l_exists <> 1 THEN
2309: gme_common_pvt.log_message ('GME_INVALID_BYPROD_TYPE');
2310: RAISE invalid_byproduct_type;
2311: END IF;
2312: END IF;
2313:
2360:
2361: x_return_status := fnd_api.g_ret_sts_success;
2362:
2363: IF p_line_no <= 0 THEN
2364: gme_common_pvt.log_message ('GME_INVALID_LINE_NUMBER');
2365: RAISE invalid_line_no;
2366: END IF;
2367:
2368: OPEN cur_last_line_no(p_batch_id, p_line_type);
2440: RAISE invalid_dtl_um;
2441: END IF;
2442: */
2443:
2444: IF l_disable_date <= gme_common_pvt.g_timestamp THEN
2445: gme_common_pvt.log_message ('GME_UM_DISABLED');
2446: RAISE disabled_dtl_um;
2447: END IF;
2448:
2441: END IF;
2442: */
2443:
2444: IF l_disable_date <= gme_common_pvt.g_timestamp THEN
2445: gme_common_pvt.log_message ('GME_UM_DISABLED');
2446: RAISE disabled_dtl_um;
2447: END IF;
2448:
2449: get_converted_qty (
2499:
2500: x_return_status := fnd_api.g_ret_sts_success;
2501:
2502: IF p_plan_qty < 0 THEN
2503: gme_common_pvt.log_message ('GME_INVALID_PLAN_QTY');
2504: RAISE val_error;
2505: END IF;
2506:
2507: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2540:
2541: x_return_status := fnd_api.g_ret_sts_success;
2542:
2543: IF p_wip_plan_qty < 0 THEN
2544: gme_common_pvt.log_message ('GME_INVALID_WIP_PLAN_QTY');
2545: RAISE val_error;
2546: END IF;
2547:
2548: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2581:
2582: x_return_status := fnd_api.g_ret_sts_success;
2583:
2584: IF p_actual_qty < 0 THEN
2585: gme_common_pvt.log_message ('GME_INVALID_ACTUAL_QTY');
2586: RAISE val_error;
2587: END IF;
2588:
2589: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2635: FETCH cur_rel_type INTO l_exists;
2636: CLOSE cur_rel_type;
2637:
2638: IF l_exists IS NULL OR l_exists <> 1 THEN
2639: gme_common_pvt.log_message ('GME_INVALID_RELEASE_TYPE');
2640: RAISE val_error;
2641: END IF;
2642:
2643: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2677:
2678: x_return_status := fnd_api.g_ret_sts_success;
2679:
2680: IF (p_scrap < 0 OR p_scrap > 1000000) THEN
2681: gme_common_pvt.log_message ('GME_INVALID_SCRAP_FACTOR');
2682: RAISE val_error;
2683: END IF;
2684:
2685: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2718:
2719: x_return_status := fnd_api.g_ret_sts_success;
2720:
2721: IF p_scale_mult <= 0 THEN
2722: gme_common_pvt.log_message ('GME_INVALID_SCALE_MULT');
2723: RAISE val_error;
2724: END IF;
2725:
2726: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2759:
2760: x_return_status := fnd_api.g_ret_sts_success;
2761:
2762: IF (p_scale_var < 0 OR p_scale_var > 100) THEN
2763: gme_common_pvt.log_message ('GME_INVALID_SCALE_ROUND_VAR');
2764: RAISE val_error;
2765: END IF;
2766:
2767: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2813: FETCH cur_round_dir INTO l_exists;
2814: CLOSE cur_round_dir;
2815:
2816: IF l_exists IS NULL OR l_exists <> 1 THEN
2817: gme_common_pvt.log_message ('GME_INVALID_ROUND_DIR');
2818: RAISE val_error;
2819: END IF;
2820:
2821: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2867: FETCH cur_scale_type INTO l_exists;
2868: CLOSE cur_scale_type;
2869:
2870: IF l_exists IS NULL OR l_exists <> 1 THEN
2871: gme_common_pvt.log_message ('GME_INVALID_SCALE_TYPE');
2872: RAISE val_error;
2873: END IF;
2874:
2875: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2936: OPEN Cur_get_batch_status(l_batch_id);
2937: FETCH Cur_get_batch_status INTO l_status;
2938: CLOSE Cur_get_batch_status;
2939:
2940: IF l_status = gme_common_pvt.g_batch_wip THEN
2941: OPEN Cur_get_step_status(l_material_detail_id);
2942: FETCH Cur_get_step_status INTO l_rel_type,l_step_status;
2943: CLOSE Cur_get_step_status;
2944: END IF;
2944: END IF;
2945:
2946: /* For the completed batches and the wip batches where the associated step is
2947: completed, the cost allocation is not updatable */
2948: IF l_status = gme_common_pvt.g_batch_completed OR
2949: ( l_status = gme_common_pvt.g_batch_wip AND l_rel_type=gme_common_pvt.g_mtl_autobystep_release AND
2950: l_step_status = gme_common_pvt.g_step_completed) THEN
2951: IF l_material_detail_id is NULL THEN
2952: /* in insert */
2945:
2946: /* For the completed batches and the wip batches where the associated step is
2947: completed, the cost allocation is not updatable */
2948: IF l_status = gme_common_pvt.g_batch_completed OR
2949: ( l_status = gme_common_pvt.g_batch_wip AND l_rel_type=gme_common_pvt.g_mtl_autobystep_release AND
2950: l_step_status = gme_common_pvt.g_step_completed) THEN
2951: IF l_material_detail_id is NULL THEN
2952: /* in insert */
2953: IF p_material_detail_rec.cost_alloc <> 0 THEN
2946: /* For the completed batches and the wip batches where the associated step is
2947: completed, the cost allocation is not updatable */
2948: IF l_status = gme_common_pvt.g_batch_completed OR
2949: ( l_status = gme_common_pvt.g_batch_wip AND l_rel_type=gme_common_pvt.g_mtl_autobystep_release AND
2950: l_step_status = gme_common_pvt.g_step_completed) THEN
2951: IF l_material_detail_id is NULL THEN
2952: /* in insert */
2953: IF p_material_detail_rec.cost_alloc <> 0 THEN
2954: gme_common_pvt.log_message ('GME_INVALID_COST_ALLOC');
2950: l_step_status = gme_common_pvt.g_step_completed) THEN
2951: IF l_material_detail_id is NULL THEN
2952: /* in insert */
2953: IF p_material_detail_rec.cost_alloc <> 0 THEN
2954: gme_common_pvt.log_message ('GME_INVALID_COST_ALLOC');
2955: RAISE val_error;
2956: END IF;
2957: ELSE
2958: /* in update */
2960: FETCH Cur_get_cost_alloc INTO l_cost_alloc;
2961: CLOSE Cur_get_cost_alloc;
2962: /*if the passed cost allocation is diff from stored cost alloc raise error */
2963: IF l_cost_alloc <> p_material_detail_rec.cost_alloc THEN
2964: gme_common_pvt.log_message ('GME_COST_ALLOC_CANNOT_UPD');
2965: RAISE val_error;
2966: END IF;
2967: END IF;
2968: END IF;
2967: END IF;
2968: END IF;
2969: --commented out the following lines
2970: /*IF p_cost_alloc < 0 OR p_cost_alloc > 1 THEN
2971: gme_common_pvt.log_message ('GME_INVALID_COST_ALLOC');
2972: RAISE val_error;
2973: END IF; */
2974: --FPBug#4524232 End
2975: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3018: FETCH cur_phantom_type INTO l_exists;
3019: CLOSE cur_phantom_type;
3020:
3021: IF l_exists IS NULL OR l_exists <> 1 THEN
3022: gme_common_pvt.log_message ('GME_INV_PHANTOM_TYPE');
3023: RAISE val_error;
3024: END IF;
3025:
3026: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3059:
3060: x_return_status := fnd_api.g_ret_sts_success;
3061:
3062: IF p_contr_yield_ind NOT IN ('Y', 'N') THEN
3063: gme_common_pvt.log_message ('GME_INVALID_CONTR_YIELD');
3064: RAISE val_error;
3065: END IF;
3066:
3067: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3101:
3102: x_return_status := fnd_api.g_ret_sts_success;
3103:
3104: IF p_contr_step_qty_ind NOT IN ('Y', 'N') THEN
3105: gme_common_pvt.log_message ('GME_INVALID_CONTR_STEP');
3106: RAISE val_error;
3107: END IF;
3108:
3109: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3144: x_return_status := fnd_api.g_ret_sts_success;
3145:
3146: IF p_subinv IS NULL THEN
3147: NULL; -- not required
3148: ELSIF gme_common_pvt.check_subinventory
3149: (p_organization_id => p_item_rec.organization_id
3150: ,p_subinventory => p_subinv
3151: ,p_inventory_item_id => p_item_rec.inventory_item_id
3152: ,p_restrict_subinv => p_item_rec.restrict_subinventories_code) THEN
3204: END IF;
3205:
3206: x_return_status := fnd_api.g_ret_sts_success;
3207:
3208: IF p_line_type = gme_common_pvt.g_line_type_ing THEN
3209: l_txn_action_id := gme_common_pvt.g_ing_issue_txn_action;
3210: ELSIF p_line_type = gme_common_pvt.g_line_type_prod THEN
3211: l_txn_action_id := gme_common_pvt.g_prod_comp_txn_action;
3212: ELSIF p_line_type = gme_common_pvt.g_line_type_byprod THEN
3205:
3206: x_return_status := fnd_api.g_ret_sts_success;
3207:
3208: IF p_line_type = gme_common_pvt.g_line_type_ing THEN
3209: l_txn_action_id := gme_common_pvt.g_ing_issue_txn_action;
3210: ELSIF p_line_type = gme_common_pvt.g_line_type_prod THEN
3211: l_txn_action_id := gme_common_pvt.g_prod_comp_txn_action;
3212: ELSIF p_line_type = gme_common_pvt.g_line_type_byprod THEN
3213: l_txn_action_id := gme_common_pvt.g_byprod_comp_txn_action;
3206: x_return_status := fnd_api.g_ret_sts_success;
3207:
3208: IF p_line_type = gme_common_pvt.g_line_type_ing THEN
3209: l_txn_action_id := gme_common_pvt.g_ing_issue_txn_action;
3210: ELSIF p_line_type = gme_common_pvt.g_line_type_prod THEN
3211: l_txn_action_id := gme_common_pvt.g_prod_comp_txn_action;
3212: ELSIF p_line_type = gme_common_pvt.g_line_type_byprod THEN
3213: l_txn_action_id := gme_common_pvt.g_byprod_comp_txn_action;
3214: END IF;
3207:
3208: IF p_line_type = gme_common_pvt.g_line_type_ing THEN
3209: l_txn_action_id := gme_common_pvt.g_ing_issue_txn_action;
3210: ELSIF p_line_type = gme_common_pvt.g_line_type_prod THEN
3211: l_txn_action_id := gme_common_pvt.g_prod_comp_txn_action;
3212: ELSIF p_line_type = gme_common_pvt.g_line_type_byprod THEN
3213: l_txn_action_id := gme_common_pvt.g_byprod_comp_txn_action;
3214: END IF;
3215:
3208: IF p_line_type = gme_common_pvt.g_line_type_ing THEN
3209: l_txn_action_id := gme_common_pvt.g_ing_issue_txn_action;
3210: ELSIF p_line_type = gme_common_pvt.g_line_type_prod THEN
3211: l_txn_action_id := gme_common_pvt.g_prod_comp_txn_action;
3212: ELSIF p_line_type = gme_common_pvt.g_line_type_byprod THEN
3213: l_txn_action_id := gme_common_pvt.g_byprod_comp_txn_action;
3214: END IF;
3215:
3216: IF p_locator_id IS NULL THEN
3209: l_txn_action_id := gme_common_pvt.g_ing_issue_txn_action;
3210: ELSIF p_line_type = gme_common_pvt.g_line_type_prod THEN
3211: l_txn_action_id := gme_common_pvt.g_prod_comp_txn_action;
3212: ELSIF p_line_type = gme_common_pvt.g_line_type_byprod THEN
3213: l_txn_action_id := gme_common_pvt.g_byprod_comp_txn_action;
3214: END IF;
3215:
3216: IF p_locator_id IS NULL THEN
3217: NULL; -- not required
3219: OPEN cur_sub_control (p_item_rec.organization_id, p_subinv);
3220: FETCH cur_sub_control INTO l_sub_locator_type;
3221: CLOSE cur_sub_control;
3222: /* Bug 5441643 Added NVL condition for location control code*/
3223: IF gme_common_pvt.check_locator
3224: (p_organization_id => p_item_rec.organization_id
3225: ,p_locator_id => p_locator_id
3226: ,p_subinventory => p_subinv
3227: ,p_inventory_item_id => p_item_rec.inventory_item_id
3224: (p_organization_id => p_item_rec.organization_id
3225: ,p_locator_id => p_locator_id
3226: ,p_subinventory => p_subinv
3227: ,p_inventory_item_id => p_item_rec.inventory_item_id
3228: ,p_org_control => gme_common_pvt.g_org_locator_control
3229: ,p_sub_control => l_sub_locator_type
3230: ,p_item_control => NVL(p_item_rec.location_control_code,1)
3231: ,p_item_loc_restrict => p_item_rec.restrict_locators_code
3232: ,p_org_neg_allowed => gme_common_pvt.g_allow_neg_inv
3228: ,p_org_control => gme_common_pvt.g_org_locator_control
3229: ,p_sub_control => l_sub_locator_type
3230: ,p_item_control => NVL(p_item_rec.location_control_code,1)
3231: ,p_item_loc_restrict => p_item_rec.restrict_locators_code
3232: ,p_org_neg_allowed => gme_common_pvt.g_allow_neg_inv
3233: ,p_txn_action_id => l_txn_action_id) THEN
3234: NULL;
3235: ELSE
3236: RAISE loc_not_valid;
3290: l_compare_qty NUMBER; -- 13076579
3291:
3292: l_ph_batch_header_rec gme_batch_header%ROWTYPE;
3293: l_batch_header_rec gme_batch_header%ROWTYPE;
3294: l_out_material_detail_tbl gme_common_pvt.material_details_tab;
3295: l_material_detail_tbl gme_common_pvt.material_details_tab;
3296: l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
3297:
3298: l_mtl_dtl_rec gme_material_details%ROWTYPE;
3291:
3292: l_ph_batch_header_rec gme_batch_header%ROWTYPE;
3293: l_batch_header_rec gme_batch_header%ROWTYPE;
3294: l_out_material_detail_tbl gme_common_pvt.material_details_tab;
3295: l_material_detail_tbl gme_common_pvt.material_details_tab;
3296: l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
3297:
3298: l_mtl_dtl_rec gme_material_details%ROWTYPE;
3299: l_ph_mtl_dtl_rec gme_material_details%ROWTYPE;
3298: l_mtl_dtl_rec gme_material_details%ROWTYPE;
3299: l_ph_mtl_dtl_rec gme_material_details%ROWTYPE;
3300: l_db_mtl_dtl_rec gme_material_details%ROWTYPE;
3301: l_batch_step_rec gme_batch_steps%ROWTYPE;
3302: l_exception_material_tbl gme_common_pvt.exceptions_tab;
3303: l_ph_batch_step_rec gme_batch_steps%ROWTYPE;
3304: l_phantom_batch_header_rec_out gme_batch_header%ROWTYPE;
3305: l_step_tbl gme_reschedule_step_pvt.step_tab;
3306: x_batch_step_rec gme_batch_steps%ROWTYPE;
3351: -- batch_status is used to decide whether to use plan or wip_plan
3352: l_factor := 1;
3353: l_batch_status := p_batch_header_rec.batch_status;
3354:
3355: IF l_batch_status = gme_common_pvt.g_batch_pending THEN
3356: l_qty := l_mtl_dtl_rec.plan_qty;
3357: --Bug#4965141 check for zero plan qty
3358: IF l_db_mtl_dtl_rec.plan_qty = 0 THEN
3359: l_eff_qty := 1;
3360: ELSE
3361: l_eff_qty := l_db_mtl_dtl_rec.plan_qty;
3362: END IF;
3363: l_factor := l_mtl_dtl_rec.plan_qty / l_eff_qty;
3364: ELSIF l_batch_status = gme_common_pvt.g_batch_wip THEN
3365: l_qty := l_mtl_dtl_rec.wip_plan_qty;
3366: --Bug#4965141 check for zero wip plan qty
3367: IF NVL(l_db_mtl_dtl_rec.wip_plan_qty,0) = 0 THEN
3368: l_eff_qty := 1;
3377: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_factor='||l_factor);
3378: END IF;
3379:
3380: l_status := p_batch_header_rec.batch_status;
3381: IF l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3382: p_batch_step_rec.batchstep_id IS NOT NULL THEN
3383: l_status := p_batch_step_rec.step_status;
3384: END IF;
3385:
3394: l_old_wip_plan := l_db_mtl_dtl_rec.wip_plan_qty;
3395: l_new_wip_plan := l_mtl_dtl_rec.wip_plan_qty;
3396:
3397: IF l_old_scrap <> l_new_scrap
3398: AND l_status = gme_common_pvt.g_batch_pending
3399: AND l_old_plan_qty = l_new_plan_qty THEN
3400: l_old_plan_qty := x_material_detail_rec.plan_qty
3401: / (1 + l_old_scrap);
3402: x_material_detail_rec.plan_qty :=
3402: x_material_detail_rec.plan_qty :=
3403: l_old_plan_qty
3404: * (1 + x_material_detail_rec.scrap_factor);
3405: ELSIF l_old_scrap <> l_new_scrap
3406: AND l_status = gme_common_pvt.g_batch_wip
3407: AND l_old_wip_plan = l_new_wip_plan THEN
3408: l_old_plan_qty :=
3409: x_material_detail_rec.wip_plan_qty
3410: / (1 + l_old_scrap);
3414: END IF;
3415: /* 5391396 moved the code up*/
3416: IF l_mtl_dtl_rec.release_type <> l_db_mtl_dtl_rec.release_type THEN
3417:
3418: gme_common_pvt.calc_mtl_req_date
3419: (p_batch_header_rec => p_batch_header_rec
3420: ,p_batchstep_rec => p_batch_step_rec
3421: ,p_mtl_dtl_rec => l_mtl_dtl_rec
3422: ,x_mtl_req_date => l_mtl_dtl_rec.material_requirement_date
3422: ,x_mtl_req_date => l_mtl_dtl_rec.material_requirement_date
3423: ,x_return_status => x_return_status);
3424:
3425: IF x_return_status <> fnd_api.g_ret_sts_success THEN
3426: l_proc := 'gme_common_pvt.calc_mtl_req_date';
3427: RAISE error_processing;
3428: END IF;
3429:
3430: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3430: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3431: gme_debug.put_line ( g_pkg_name
3432: || '.'
3433: || l_api_name
3434: || ' after gme_common_pvt.calc_mtl_req_date');
3435: gme_debug.put_line
3436: ( g_pkg_name
3437: || '.'
3438: || l_api_name
3442: ,'YYYY-MON-DD HH24:MI:SS') );
3443: END IF;
3444: /* Pawan Kumar bug 5127489 Changed so as to change the move order
3445: and reservation dates */
3446: gme_common_pvt.material_date_change (
3447: p_material_detail_id => l_mtl_dtl_rec.material_detail_id
3448: ,p_material_date => l_mtl_dtl_rec.material_requirement_date
3449: ,x_return_status => x_return_status );
3450:
3448: ,p_material_date => l_mtl_dtl_rec.material_requirement_date
3449: ,x_return_status => x_return_status );
3450:
3451: IF x_return_status <> fnd_api.g_ret_sts_success THEN
3452: l_proc := 'gme_common_pvt.material_date_change';
3453: RAISE error_processing;
3454: END IF;
3455: --Bug#5159393 Susruth D.
3456: l_mtl_dtl_rec.last_update_date := gme_common_pvt.g_timestamp;
3452: l_proc := 'gme_common_pvt.material_date_change';
3453: RAISE error_processing;
3454: END IF;
3455: --Bug#5159393 Susruth D.
3456: l_mtl_dtl_rec.last_update_date := gme_common_pvt.g_timestamp;
3457: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3458: gme_debug.put_line ( g_pkg_name
3459: || '.'
3460: || l_api_name
3457: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3458: gme_debug.put_line ( g_pkg_name
3459: || '.'
3460: || l_api_name
3461: || ' after gme_common_pvt.material_date_change');
3462: gme_debug.put_line
3463: ( g_pkg_name
3464: || '.'
3465: || l_api_name
3487: l_ph_mtl_dtl_rec.subinventory := l_mtl_dtl_rec.subinventory;
3488: l_ph_mtl_dtl_rec.locator_id := l_mtl_dtl_rec.locator_id;
3489: --sunitha ch. bug 5566769 update the revision field of the phantom batch
3490: l_ph_mtl_dtl_rec.revision := l_mtl_dtl_rec.revision;
3491: IF l_status = gme_common_pvt.g_batch_pending AND (l_mtl_dtl_rec.plan_qty <> l_db_mtl_dtl_rec.plan_qty) THEN
3492: IF (l_mtl_dtl_rec.dtl_um = l_ph_mtl_dtl_rec.dtl_um) THEN
3493: l_ph_mtl_dtl_rec.plan_qty := l_mtl_dtl_rec.plan_qty;
3494: ELSE
3495: l_ph_mtl_dtl_rec.plan_qty := inv_convert.inv_um_convert(item_id => l_mtl_dtl_rec.inventory_item_id
3493: l_ph_mtl_dtl_rec.plan_qty := l_mtl_dtl_rec.plan_qty;
3494: ELSE
3495: l_ph_mtl_dtl_rec.plan_qty := inv_convert.inv_um_convert(item_id => l_mtl_dtl_rec.inventory_item_id
3496: ,organization_id => l_mtl_dtl_rec.organization_id
3497: ,precision => gme_common_pvt.g_precision
3498: ,from_quantity => l_mtl_dtl_rec.plan_qty
3499: ,from_unit => l_mtl_dtl_rec.dtl_um
3500: ,to_unit => l_ph_mtl_dtl_rec.dtl_um
3501: ,from_name => NULL
3506: l_ph_mtl_dtl_rec.wip_plan_qty := l_mtl_dtl_rec.wip_plan_qty;
3507: ELSE
3508: l_ph_mtl_dtl_rec.wip_plan_qty := inv_convert.inv_um_convert(item_id => l_mtl_dtl_rec.inventory_item_id
3509: ,organization_id => l_mtl_dtl_rec.organization_id
3510: ,precision => gme_common_pvt.g_precision
3511: ,from_quantity => l_mtl_dtl_rec.wip_plan_qty
3512: ,from_unit => l_mtl_dtl_rec.dtl_um
3513: ,to_unit => l_ph_mtl_dtl_rec.dtl_um
3514: ,from_name => NULL
3517: END IF;
3518: /* End Bug 4867497 */
3519:
3520: IF l_db_mtl_dtl_rec.phantom_id IS NOT NULL THEN
3521: IF (l_db_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing ) THEN
3522: /*REWORK Sunitha ch. Bug 5353941 Check whether phantom material is associated to
3523: step and call reschedule batch if it is not associated to step or call
3524: rescedule step for that batch if it is associated to step*/
3525: l_ph_batch_header_rec.batch_id := l_db_mtl_dtl_rec.phantom_id;
3550: END IF;
3551: END IF; --IF l_factor <> 1
3552:
3553: IF(l_mtl_dtl_rec.material_requirement_date <> l_db_mtl_dtl_rec.material_requirement_date ) THEN
3554: IF(l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3555: gme_common_pvt.is_material_assoc_to_step
3556: (l_ph_mtl_dtl_rec.material_detail_id )) THEN
3557:
3558: /* REWORK Sunitha Bug 5353941. Following select statement will only execute for ingredient of parent batch and never for product of phantom batch as we have the check of line type to be ingredient. */
3551: END IF; --IF l_factor <> 1
3552:
3553: IF(l_mtl_dtl_rec.material_requirement_date <> l_db_mtl_dtl_rec.material_requirement_date ) THEN
3554: IF(l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3555: gme_common_pvt.is_material_assoc_to_step
3556: (l_ph_mtl_dtl_rec.material_detail_id )) THEN
3557:
3558: /* REWORK Sunitha Bug 5353941. Following select statement will only execute for ingredient of parent batch and never for product of phantom batch as we have the check of line type to be ingredient. */
3559:
3589: ,x_return_status => x_return_status);
3590: END IF;
3591: END IF; --l_mtl_dtl_rec.material_requirement_date <> l_db_mtl_dtl_rec.material_requirement_date
3592: /* End Bug 5353941 REWORK*/
3593: END IF;--IF l_db_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing
3594: ELSE -- if it is phantom Product
3595: /*Sunitha Ch. Bug#5391396 rescheduling batch/step when update yield Type
3596: of the Child batch is done */
3597: IF ( l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3593: END IF;--IF l_db_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing
3594: ELSE -- if it is phantom Product
3595: /*Sunitha Ch. Bug#5391396 rescheduling batch/step when update yield Type
3596: of the Child batch is done */
3597: IF ( l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3598: gme_common_pvt.is_material_assoc_to_step
3599: (l_ph_mtl_dtl_rec.material_detail_id )) THEN
3600: SELECT plan_start_date
3601: INTO l_plan_cmplt_date
3594: ELSE -- if it is phantom Product
3595: /*Sunitha Ch. Bug#5391396 rescheduling batch/step when update yield Type
3596: of the Child batch is done */
3597: IF ( l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3598: gme_common_pvt.is_material_assoc_to_step
3599: (l_ph_mtl_dtl_rec.material_detail_id )) THEN
3600: SELECT plan_start_date
3601: INTO l_plan_cmplt_date
3602: FROM gme_batch_steps
3614: WHERE batch_id = l_ph_mtl_dtl_rec.batch_id;
3615: END IF;
3616: l_mtl_dtl_rec.material_requirement_date:=l_plan_cmplt_date;
3617: l_ph_mtl_dtl_rec.material_requirement_date := l_mtl_dtl_rec.material_requirement_date;
3618: IF(l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3619: gme_common_pvt.is_material_assoc_to_step
3620: (l_mtl_dtl_rec.material_detail_id )) THEN
3621:
3622: /* Sunitha Bug 5391396 . Following select statement will only execute for ingredient of parent batch and never for product of phantom batch as we have the check of line type to be ingredient. */
3615: END IF;
3616: l_mtl_dtl_rec.material_requirement_date:=l_plan_cmplt_date;
3617: l_ph_mtl_dtl_rec.material_requirement_date := l_mtl_dtl_rec.material_requirement_date;
3618: IF(l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3619: gme_common_pvt.is_material_assoc_to_step
3620: (l_mtl_dtl_rec.material_detail_id )) THEN
3621:
3622: /* Sunitha Bug 5391396 . Following select statement will only execute for ingredient of parent batch and never for product of phantom batch as we have the check of line type to be ingredient. */
3623: l_batch_step_rec:=p_batch_step_rec;
3740:
3741: -- add material line into invisible move order
3742: gme_move_orders_pvt.create_move_order_lines
3743: (p_move_order_header_id => p_batch_header_rec.move_order_header_id
3744: ,p_move_order_type => gme_common_pvt.g_invis_move_order_type
3745: ,p_material_details_tbl => l_material_detail_tbl
3746: ,x_material_details_tbl => l_out_material_detail_tbl
3747: ,x_trolin_tbl => l_trolin_tbl
3748: ,x_return_status => x_return_status);
3787: l_proc := 'gme_material_details_dbl.update_row';
3788: RAISE error_dbl;
3789: -- nsinghi bug#5208923. added the else part.
3790: ELSE
3791: gme_common_pvt.get_who(x_user_ident => x_material_detail_rec.last_updated_by,
3792: x_login_id => x_material_detail_rec.last_update_login,
3793: x_timestamp => x_material_detail_rec.last_update_date,
3794: x_return_status => x_return_status);
3795: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3792: x_login_id => x_material_detail_rec.last_update_login,
3793: x_timestamp => x_material_detail_rec.last_update_date,
3794: x_return_status => x_return_status);
3795: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3796: l_proc := 'gme_common_pvt.get_who ';
3797: RAISE error_processing;
3798: END IF;
3799:
3800: END IF;
3852: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3853: gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': ' || l_proc|| ' error returned');
3854: END IF;
3855: WHEN error_dbl THEN
3856: gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
3857: x_return_status := FND_API.g_ret_sts_unexp_error;
3858:
3859: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3860: gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': '
3922: l_batch_status := p_batch_header_rec.batch_status;
3923: l_step_status := p_batch_step_rec.step_status;
3924:
3925: IF p_material_detail_rec.formulaline_id IS NOT NULL THEN
3926: gme_common_pvt.log_message ('GME_FORMID_CHG_NOT_ALLOWED');
3927: RAISE error_no_upd;
3928: END IF;
3929:
3930: IF p_material_detail_rec.inventory_item_id IS NOT NULL THEN
3927: RAISE error_no_upd;
3928: END IF;
3929:
3930: IF p_material_detail_rec.inventory_item_id IS NOT NULL THEN
3931: gme_common_pvt.log_message ('GME_ITEMID_CHG_NOT_ALLOWED');
3932: RAISE error_no_upd;
3933: END IF;
3934:
3935: --Bug#5078853
3936: IF p_material_detail_rec.phantom_line_id IS NOT NULL OR
3937: p_material_detail_rec.backordered_qty IS NOT NULL OR
3938: p_material_detail_rec.original_primary_qty IS NOT NULL OR
3939: p_material_detail_rec.move_order_line_id IS NOT NULL THEN
3940: gme_common_pvt.log_message ('GME_FIELD_CHG_NOT_ALLOWED');
3941: RAISE error_no_upd;
3942: END IF;
3943:
3944: get_item_rec
3967: END IF;
3968: END IF;
3969: END IF;
3970:
3971: IF x_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod THEN
3972: IF p_material_detail_rec.by_product_type IS NOT NULL THEN
3973: IF p_material_detail_rec.by_product_type = fnd_api.g_miss_char THEN
3974: x_material_detail_rec.by_product_type := NULL;
3975: ELSE
3991: x_material_detail_rec.by_product_type := NULL;
3992: END IF;
3993:
3994: IF p_material_detail_rec.release_type IS NOT NULL THEN
3995: IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending THEN
3996: IF p_material_detail_rec.release_type = fnd_api.g_miss_num THEN
3997: l_field := 'release_type';
3998: RAISE error_no_null;
3999: ELSE
4009: RAISE val_error;
4010: END IF;
4011: END IF;
4012: ELSE
4013: gme_common_pvt.log_message ('GME_INV_STAT_UPD_REL');
4014: RAISE expected_error;
4015: END IF;
4016: END IF;
4017:
4016: END IF;
4017:
4018: -- Set status of material based on release type, assocation and step status
4019: IF x_material_detail_rec.release_type =
4020: gme_common_pvt.g_mtl_autobystep_release
4021: AND p_batch_step_rec.batchstep_id IS NOT NULL THEN
4022: l_status := p_batch_step_rec.step_status;
4023: ELSE
4024: l_status := p_batch_header_rec.batch_status;
4024: l_status := p_batch_header_rec.batch_status;
4025: END IF;
4026:
4027: IF p_material_detail_rec.plan_qty IS NOT NULL THEN
4028: IF l_batch_status = gme_common_pvt.g_batch_pending THEN
4029: IF p_material_detail_rec.plan_qty = fnd_api.g_miss_num THEN
4030: l_field := 'plan_qty';
4031: RAISE error_no_null;
4032: ELSE
4041: RAISE val_error;
4042: END IF;
4043: END IF;
4044: ELSE
4045: gme_common_pvt.log_message ('GME_INV_STAT_UPD_PLAN_QTY');
4046: RAISE expected_error;
4047: END IF;
4048: END IF;
4049:
4047: END IF;
4048: END IF;
4049:
4050: IF p_material_detail_rec.dtl_um IS NOT NULL THEN
4051: IF l_batch_status = gme_common_pvt.g_batch_pending THEN
4052: IF p_material_detail_rec.dtl_um = fnd_api.g_miss_char THEN
4053: l_field := 'dtl_um';
4054: RAISE error_no_null;
4055: ELSE
4068: RAISE val_error;
4069: END IF;
4070: END IF;
4071: ELSE
4072: gme_common_pvt.log_message ('GME_INV_STAT_UPD_DTL_UM');
4073: RAISE expected_error;
4074: END IF;
4075: END IF;
4076:
4074: END IF;
4075: END IF;
4076:
4077: IF p_material_detail_rec.wip_plan_qty IS NOT NULL THEN
4078: IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip THEN
4079: IF p_material_detail_rec.wip_plan_qty = fnd_api.g_miss_num THEN
4080: l_field := 'wip_plan_qty';
4081: RAISE error_no_null;
4082: ELSE
4091: RAISE val_error;
4092: END IF;
4093: END IF;
4094: ELSE
4095: gme_common_pvt.log_message ('GME_INV_STAT_UPD_WIP_PLAN');
4096: RAISE expected_error;
4097: END IF;
4098: END IF;
4099:
4098: END IF;
4099:
4100: --Bug#5078853 modified validation for actual qty
4101: IF p_material_detail_rec.actual_qty IS NOT NULL THEN
4102: IF p_batch_header_rec.batch_status IN (gme_common_pvt.g_batch_wip,
4103: gme_common_pvt.g_batch_completed )THEN
4104: IF p_material_detail_rec.actual_qty = fnd_api.g_miss_num THEN
4105: l_field := 'actual_qty';
4106: RAISE error_no_null;
4099:
4100: --Bug#5078853 modified validation for actual qty
4101: IF p_material_detail_rec.actual_qty IS NOT NULL THEN
4102: IF p_batch_header_rec.batch_status IN (gme_common_pvt.g_batch_wip,
4103: gme_common_pvt.g_batch_completed )THEN
4104: IF p_material_detail_rec.actual_qty = fnd_api.g_miss_num THEN
4105: l_field := 'actual_qty';
4106: RAISE error_no_null;
4107: ELSE
4115: RAISE val_error;
4116: END IF;
4117: END IF;
4118: ELSE
4119: gme_common_pvt.log_message ('GME_INV_STAT_UPD_ACT');
4120: RAISE expected_error;
4121: END IF; /*status check */
4122: END IF;
4123:
4122: END IF;
4123:
4124: --Bug#5078853 allow scrap factor to be changed in pending
4125: -- don't allow NULL
4126: IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending AND
4127: p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
4128:
4129: IF p_material_detail_rec.scrap_factor IS NOT NULL THEN
4130: IF p_material_detail_rec.scrap_factor = fnd_api.g_miss_num THEN
4123:
4124: --Bug#5078853 allow scrap factor to be changed in pending
4125: -- don't allow NULL
4126: IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending AND
4127: p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
4128:
4129: IF p_material_detail_rec.scrap_factor IS NOT NULL THEN
4130: IF p_material_detail_rec.scrap_factor = fnd_api.g_miss_num THEN
4131: l_field := 'scrap_factor';
4155: END IF;
4156:
4157: --Bug#5078853 scale type can be changed in both pending and WIP
4158: IF p_material_detail_rec.scale_type IS NOT NULL THEN
4159: IF p_batch_header_rec.batch_status IN ( gme_common_pvt.g_batch_pending,
4160: gme_common_pvt.g_batch_wip ) THEN
4161: -- scale_type can be changed, but not to NULL
4162: IF p_material_detail_rec.scale_type = fnd_api.g_miss_num THEN
4163: l_field := 'scale_type';
4156:
4157: --Bug#5078853 scale type can be changed in both pending and WIP
4158: IF p_material_detail_rec.scale_type IS NOT NULL THEN
4159: IF p_batch_header_rec.batch_status IN ( gme_common_pvt.g_batch_pending,
4160: gme_common_pvt.g_batch_wip ) THEN
4161: -- scale_type can be changed, but not to NULL
4162: IF p_material_detail_rec.scale_type = fnd_api.g_miss_num THEN
4163: l_field := 'scale_type';
4164: RAISE error_no_null;
4215: x_material_detail_rec.scale_rounding_variance := NULL;
4216: x_material_detail_rec.rounding_direction := NULL;
4217: END IF; /* x_material_detail_rec.scale_type = 2 */
4218: ELSE
4219: gme_common_pvt.log_message ('GME_INV_STAT_UPD_SCALE_TYPE');
4220: RAISE expected_error;
4221: END IF; /* status check */
4222: END IF; /* p_material_detail_rec.scale_type IS NOT NULL */
4223:
4221: END IF; /* status check */
4222: END IF; /* p_material_detail_rec.scale_type IS NOT NULL */
4223:
4224: -- can change cost alloc for product; ignore other line types
4225: IF x_material_detail_rec.line_type = gme_common_pvt.g_line_type_prod THEN
4226: -- validate 0 <= cost_alloc <= 1
4227: -- at save_batch, check that sum(cost_alloc for all products) <= 1
4228: validate_cost_alloc
4229: (p_material_detail_rec => p_material_detail_rec
4239: --Bug#5078853 modified
4240: -- can change phantom type for ingredient only if not exploded;
4241: -- error otherwise
4242: IF p_material_detail_rec.phantom_type IS NOT NULL
4243: AND x_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing
4244: AND x_material_detail_rec.phantom_id IS NULL
4245: AND p_material_detail_rec.phantom_type <> x_material_detail_rec.phantom_type THEN
4246:
4247: IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending THEN
4243: AND x_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing
4244: AND x_material_detail_rec.phantom_id IS NULL
4245: AND p_material_detail_rec.phantom_type <> x_material_detail_rec.phantom_type THEN
4246:
4247: IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending THEN
4248: x_material_detail_rec.phantom_type := p_material_detail_rec.phantom_type;
4249:
4250: validate_phantom_type
4251: (p_phantom_type => x_material_detail_rec.phantom_type
4263: l_val_proc := 'validate_phantom_type_change';
4264: RAISE val_error;
4265: END IF;
4266: ELSE
4267: gme_common_pvt.log_message ('GME_INV_STAT_UPD_PHAN_TYPE');
4268: RAISE expected_error;
4269: END IF; /* batch status check */
4270: END IF;
4271:
4269: END IF; /* batch status check */
4270: END IF;
4271:
4272: -- can't update to NULL; only update for ingred, ignore other line type
4273: IF x_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
4274: validate_contr_yield_ind
4275: (p_contr_yield_ind => x_material_detail_rec.contribute_yield_ind
4276: ,x_return_status => x_return_status);
4277:
4353: END IF;
4354: END IF;
4355:
4356: /* Bug#5078853 added the following call for flex field validation
4357: gme_common_pvt.g_flex_validate_prof has to be set in public API to enforce flex field validation
4358: l_material_detail_rec will have flex field values passed from public API , if any*/
4359:
4360: l_material_detail_rec.material_detail_id := x_material_detail_rec.material_detail_id;
4361: gme_validate_flex_fld_pvt.validate_flex_material_details
4373: END IF;
4374:
4375: EXCEPTION
4376: WHEN error_no_null THEN
4377: gme_common_pvt.log_message ('GME_INVALID_VALUE_SPECIFIED'
4378: ,'FIELD_NAME'
4379: ,l_field);
4380: x_return_status := fnd_api.g_ret_sts_error;
4381: WHEN error_no_upd OR expected_error THEN
4454: x_return_status := fnd_api.g_ret_sts_success;
4455:
4456: /* validate batch for material deletion */
4457: IF p_batch_header_rec.batch_type <> 0 THEN
4458: gme_common_pvt.log_message('GME_INV_BATCH_TYPE_OPER');
4459: RAISE val_error;
4460: END IF;
4461:
4462: --Fetch allow material deletion profile
4465: CLOSE c_get_delete;
4466:
4467: l_delete_ind := NVL(l_delete_ind, 1);
4468:
4469: IF NOT( p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending OR
4470: (l_delete_ind = 2 AND
4471: p_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip AND
4472: p_batch_header_rec. automatic_step_calculation = 0)
4473: ) THEN
4467: l_delete_ind := NVL(l_delete_ind, 1);
4468:
4469: IF NOT( p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending OR
4470: (l_delete_ind = 2 AND
4471: p_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip AND
4472: p_batch_header_rec. automatic_step_calculation = 0)
4473: ) THEN
4474: gme_common_pvt.log_message ('GME_INV_BATCH_STATUS_OPER');
4475: RAISE val_error;
4470: (l_delete_ind = 2 AND
4471: p_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip AND
4472: p_batch_header_rec. automatic_step_calculation = 0)
4473: ) THEN
4474: gme_common_pvt.log_message ('GME_INV_BATCH_STATUS_OPER');
4475: RAISE val_error;
4476: END IF;
4477:
4478: /* check step status if material is assocaited to step */
4476: END IF;
4477:
4478: /* check step status if material is assocaited to step */
4479: IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
4480: IF (p_batch_step_rec.step_status NOT IN (gme_common_pvt.g_step_pending
4481: ,gme_common_pvt.g_step_wip)) THEN
4482: gme_common_pvt.log_message('PC_STEP_STATUS_ERR');
4483: RAISE val_error;
4484: END IF;
4477:
4478: /* check step status if material is assocaited to step */
4479: IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
4480: IF (p_batch_step_rec.step_status NOT IN (gme_common_pvt.g_step_pending
4481: ,gme_common_pvt.g_step_wip)) THEN
4482: gme_common_pvt.log_message('PC_STEP_STATUS_ERR');
4483: RAISE val_error;
4484: END IF;
4485: END IF; /* p_batch_step_rec.batchstep_id IS NOT NULL */
4478: /* check step status if material is assocaited to step */
4479: IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
4480: IF (p_batch_step_rec.step_status NOT IN (gme_common_pvt.g_step_pending
4481: ,gme_common_pvt.g_step_wip)) THEN
4482: gme_common_pvt.log_message('PC_STEP_STATUS_ERR');
4483: RAISE val_error;
4484: END IF;
4485: END IF; /* p_batch_step_rec.batchstep_id IS NOT NULL */
4486:
4484: END IF;
4485: END IF; /* p_batch_step_rec.batchstep_id IS NOT NULL */
4486:
4487: --line can't be deleted if line is ing and has an exploded phantom
4488: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing AND
4489: p_material_detail_rec.phantom_id IS NOT NULL THEN
4490: gme_common_pvt.log_message('GME_NO_DEL_PHANT_ING');
4491: RAISE val_error;
4492: END IF; /* exploded phantom check */
4486:
4487: --line can't be deleted if line is ing and has an exploded phantom
4488: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing AND
4489: p_material_detail_rec.phantom_id IS NOT NULL THEN
4490: gme_common_pvt.log_message('GME_NO_DEL_PHANT_ING');
4491: RAISE val_error;
4492: END IF; /* exploded phantom check */
4493:
4494: /* if there is only one ingredient or product we should not the delete */
4491: RAISE val_error;
4492: END IF; /* exploded phantom check */
4493:
4494: /* if there is only one ingredient or product we should not the delete */
4495: IF p_material_detail_rec.line_type IN (gme_common_pvt.g_line_type_ing,
4496: gme_common_pvt.g_line_type_prod) THEN
4497: OPEN num_detail_lines(p_batch_header_rec.batch_id,p_material_detail_rec.line_type);
4498: FETCH num_detail_lines INTO l_material_count;
4499: CLOSE num_detail_lines;
4492: END IF; /* exploded phantom check */
4493:
4494: /* if there is only one ingredient or product we should not the delete */
4495: IF p_material_detail_rec.line_type IN (gme_common_pvt.g_line_type_ing,
4496: gme_common_pvt.g_line_type_prod) THEN
4497: OPEN num_detail_lines(p_batch_header_rec.batch_id,p_material_detail_rec.line_type);
4498: FETCH num_detail_lines INTO l_material_count;
4499: CLOSE num_detail_lines;
4500: IF l_material_count = 1 THEN
4497: OPEN num_detail_lines(p_batch_header_rec.batch_id,p_material_detail_rec.line_type);
4498: FETCH num_detail_lines INTO l_material_count;
4499: CLOSE num_detail_lines;
4500: IF l_material_count = 1 THEN
4501: gme_common_pvt.log_message('GME_ONE_ING_PROD_REQD');
4502: RAISE val_error;
4503: END IF;
4504: END IF; /* number of lines check */
4505:
4503: END IF;
4504: END IF; /* number of lines check */
4505:
4506: -- Bug 10062802 - Add formulaline_id condition to validation.
4507: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_prod AND
4508: p_material_detail_rec.formulaline_id IS NOT NULL THEN
4509: --if product is primary product then do not allow deletion
4510: OPEN c_prim_prod(p_batch_header_rec.recipe_validity_rule_id, p_material_detail_rec.material_detail_id);
4511: FETCH c_prim_prod INTO l_dummy;
4510: OPEN c_prim_prod(p_batch_header_rec.recipe_validity_rule_id, p_material_detail_rec.material_detail_id);
4511: FETCH c_prim_prod INTO l_dummy;
4512: IF c_prim_prod%FOUND THEN
4513: CLOSE c_prim_prod;
4514: gme_common_pvt.log_message('GME_PRIM_PROD_NO_DEL');
4515: RAISE val_error;
4516: END IF;
4517: CLOSE c_prim_prod;
4518:
4519: /*OPEN cur_parent_phant(p_batch_header_rec.batch_id, p_material_detail_rec.inventory_item_id);
4520: FETCH cur_parent_phant INTO l_dummy;
4521: IF cur_parent_phant%FOUND THEN
4522: CLOSE cur_parent_phant;
4523: gme_common_pvt.log_message('GME_NO_DEL_PHANT_PROD');
4524: RAISE val_error;
4525: END IF;
4526: CLOSE cur_parent_phant; */
4527: END IF; /* line type check */
4565: IF gme_reservations_pvt.pending_reservations_exist
4566: (p_organization_id => p_material_detail_rec.organization_id
4567: ,p_batch_id => p_material_detail_rec.batch_id
4568: ,p_material_detail_id => p_material_detail_rec.material_detail_id) THEN
4569: gme_common_pvt.log_message ('GME_PENDING_RSRV_EXIST');
4570: RAISE val_error;
4571: END IF;
4572:
4573: IF gme_move_orders_pvt.pending_move_orders_exist
4573: IF gme_move_orders_pvt.pending_move_orders_exist
4574: (p_organization_id => p_material_detail_rec.organization_id
4575: ,p_batch_id => p_material_detail_rec.batch_id
4576: ,p_material_detail_id => p_material_detail_rec.material_detail_id) THEN
4577: gme_common_pvt.log_message ('GME_PENDING_MO_EXIST');
4578: RAISE val_error;
4579: END IF;
4580:
4581: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
4612: l_batch_step_rec gme_batch_steps%ROWTYPE;
4613: l_rsc_count NUMBER;
4614: l_proc VARCHAR2(100);
4615:
4616: l_mmt_tbl gme_common_pvt.mtl_mat_tran_tbl;
4617:
4618: error_processing EXCEPTION;
4619: error_dbl EXCEPTION;
4620:
4717:
4718: -- renumber subsequent lines
4719: UPDATE gme_material_details
4720: SET line_no = line_no - 1
4721: ,last_updated_by = gme_common_pvt.g_user_ident
4722: ,last_update_date = gme_common_pvt.g_timestamp
4723: ,last_update_login = gme_common_pvt.g_login_id
4724: WHERE batch_id = p_material_detail_rec.batch_id
4725: AND line_type = p_material_detail_rec.line_type
4718: -- renumber subsequent lines
4719: UPDATE gme_material_details
4720: SET line_no = line_no - 1
4721: ,last_updated_by = gme_common_pvt.g_user_ident
4722: ,last_update_date = gme_common_pvt.g_timestamp
4723: ,last_update_login = gme_common_pvt.g_login_id
4724: WHERE batch_id = p_material_detail_rec.batch_id
4725: AND line_type = p_material_detail_rec.line_type
4726: AND line_no >= p_material_detail_rec.line_no;
4719: UPDATE gme_material_details
4720: SET line_no = line_no - 1
4721: ,last_updated_by = gme_common_pvt.g_user_ident
4722: ,last_update_date = gme_common_pvt.g_timestamp
4723: ,last_update_login = gme_common_pvt.g_login_id
4724: WHERE batch_id = p_material_detail_rec.batch_id
4725: AND line_type = p_material_detail_rec.line_type
4726: AND line_no >= p_material_detail_rec.line_no;
4727:
4772: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4773: gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': ' || l_proc|| ' error returned');
4774: END IF;
4775: WHEN error_dbl THEN
4776: gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
4777: x_return_status := FND_API.g_ret_sts_unexp_error;
4778:
4779: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4780: gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': '