[Home] [Help]
106: ,p_qty IN NUMBER
107: ,p_qty_type IN NUMBER
108: ,p_trans_date IN DATE
109: ,p_backflush_rsrc_usg_ind IN NUMBER
110: ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
111: ,x_return_status OUT NOCOPY VARCHAR2) IS
112:
113: l_api_name CONSTANT VARCHAR2(30) := 'incremental_backflush';
114:
115: CURSOR Cur_fetch_incr_mat_dtl(v_batch_id NUMBER, v_matl_dtl_id NUMBER) IS
116: SELECT *
117: FROM gme_material_details
118: WHERE batch_id = v_batch_id
119: AND (release_type = gme_common_pvt.g_mtl_incremental_release
120: OR
121: (release_type = gme_common_pvt.g_mtl_manual_release AND material_detail_id = v_matl_dtl_id)
122: )
123: AND wip_plan_qty <> 0
117: FROM gme_material_details
118: WHERE batch_id = v_batch_id
119: AND (release_type = gme_common_pvt.g_mtl_incremental_release
120: OR
121: (release_type = gme_common_pvt.g_mtl_manual_release AND material_detail_id = v_matl_dtl_id)
122: )
123: AND wip_plan_qty <> 0
124: ORDER BY line_type,line_no;
125:
136: WHERE m.batch_id = V_batch_id
137: AND m.material_detail_id = V_material_detail_id
138: AND s.batch_id = m.batch_id
139: AND s.batchstep_id = m.batchstep_id
140: AND s.step_status = gme_common_pvt.g_step_wip;
141:
142: --FPBug#4667093 get the IB Factor settings
143: CURSOR Cur_ib_factor(V_org_id VARCHAR2) IS
144: SELECT ib_factor_ind
151: l_batch_header_rec gme_batch_header%ROWTYPE;
152: l_material_detail_rec gme_material_details%ROWTYPE;
153: l_in_material_detail_rec gme_material_details%ROWTYPE;
154: l_phantom_material_rec gme_material_details%ROWTYPE;
155: l_material_detail_tbl gme_common_pvt.material_details_tab;
156:
157: l_batch_step_rec gme_batch_steps%ROWTYPE;
158: l_in_batch_step_rec gme_batch_steps%ROWTYPE;
159: l_step_tbl gme_common_pvt.steps_tab;
155: l_material_detail_tbl gme_common_pvt.material_details_tab;
156:
157: l_batch_step_rec gme_batch_steps%ROWTYPE;
158: l_in_batch_step_rec gme_batch_steps%ROWTYPE;
159: l_step_tbl gme_common_pvt.steps_tab;
160:
161: l_incr_qty NUMBER;
162: l_decr_qty NUMBER;
163: l_incr_factor NUMBER;
206: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_trans_date IS NULL');
207: ELSE
208: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_trans_date='||to_char(p_trans_date, 'DD-MON-YYYY HH24:MI:SS'));
209: END IF;
210: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' gme_common_pvt.g_move_to_temp='||gme_common_pvt.g_move_to_temp);
211: END IF;
212:
213: /* Set the return status to success initially */
214: x_return_status := FND_API.G_RET_STS_SUCCESS;
433: ,p_backflush_rsrc_usg_ind => p_backflush_rsrc_usg_ind
434: ,x_exception_material_tbl => x_exception_material_tbl
435: ,x_return_status => l_return_status);
436:
437: IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
438: x_return_status := l_return_status;
439: RAISE error_phantom_backflush;
440: END IF;
441:
438: x_return_status := l_return_status;
439: RAISE error_phantom_backflush;
440: END IF;
441:
442: IF l_return_status = gme_common_pvt.g_exceptions_err THEN
443: x_return_status := gme_common_pvt.g_exceptions_err;
444: END IF;
445:
446: -- Bug 7709971 back out 7286054 as now phantom ingredient gets double posting.
439: RAISE error_phantom_backflush;
440: END IF;
441:
442: IF l_return_status = gme_common_pvt.g_exceptions_err THEN
443: x_return_status := gme_common_pvt.g_exceptions_err;
444: END IF;
445:
446: -- Bug 7709971 back out 7286054 as now phantom ingredient gets double posting.
447: -- Reinstated elsif since phantom ingredients are reconciled by the phantom prod yield.
457: gme_debug.put_line (g_pkg_name||'.'||l_api_name||' new actual = 0; calling gme_unrelease_batch_pvt.revert_material_full');
458: END IF;
459:
460: -- Bug 8751983 - Set to 2 if user is going negative.
461: IF gme_common_pvt.g_ib_timestamp_set = 1 THEN
462: gme_common_pvt.g_ib_timestamp_set := 2;
463: END IF;
464:
465: gme_unrelease_batch_pvt.revert_material_full
458: END IF;
459:
460: -- Bug 8751983 - Set to 2 if user is going negative.
461: IF gme_common_pvt.g_ib_timestamp_set = 1 THEN
462: gme_common_pvt.g_ib_timestamp_set := 2;
463: END IF;
464:
465: gme_unrelease_batch_pvt.revert_material_full
466: (p_material_detail_rec => l_material_detail_rec
469: ,x_actual_qty => l_actual_qty
470: ,x_exception_material_tbl => x_exception_material_tbl
471: ,x_return_status => l_return_status);
472:
473: IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
474: x_return_status := l_return_status;
475: RAISE error_revert_matl_full;
476: END IF;
477:
474: x_return_status := l_return_status;
475: RAISE error_revert_matl_full;
476: END IF;
477:
478: IF l_return_status = gme_common_pvt.g_exceptions_err THEN
479: x_return_status := gme_common_pvt.g_exceptions_err;
480: END IF;
481:
482: l_upd_material := TRUE;
475: RAISE error_revert_matl_full;
476: END IF;
477:
478: IF l_return_status = gme_common_pvt.g_exceptions_err THEN
479: x_return_status := gme_common_pvt.g_exceptions_err;
480: END IF;
481:
482: l_upd_material := TRUE;
483: ELSIF l_incr_qty < 0 THEN
487: gme_debug.put_line (g_pkg_name||'.'||l_api_name||' call revert_material_partial: incr_qty/decr_qty= '||l_incr_qty||'/'||l_decr_qty);
488: END IF;
489:
490: -- Bug 8751983 - Set to 2 if user is going negative.
491: IF gme_common_pvt.g_ib_timestamp_set = 1 THEN
492: gme_common_pvt.g_ib_timestamp_set := 2;
493: END IF;
494:
495: revert_material_partial
488: END IF;
489:
490: -- Bug 8751983 - Set to 2 if user is going negative.
491: IF gme_common_pvt.g_ib_timestamp_set = 1 THEN
492: gme_common_pvt.g_ib_timestamp_set := 2;
493: END IF;
494:
495: revert_material_partial
496: (p_material_detail_rec => l_material_detail_rec
500: ,p_lot_divisible_flag => l_lot_divisible_flag
501: ,x_actual_qty => l_actual_qty
502: ,x_exception_material_tbl => x_exception_material_tbl
503: ,x_return_status => l_return_status);
504: IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
505: x_return_status := l_return_status;
506: RAISE error_revert_matl_part;
507: END IF;
508:
505: x_return_status := l_return_status;
506: RAISE error_revert_matl_part;
507: END IF;
508:
509: IF l_return_status = gme_common_pvt.g_exceptions_err THEN
510: x_return_status := gme_common_pvt.g_exceptions_err;
511: END IF;
512: l_upd_material := TRUE;
513: ELSIF l_incr_qty = 0 THEN
506: RAISE error_revert_matl_part;
507: END IF;
508:
509: IF l_return_status = gme_common_pvt.g_exceptions_err THEN
510: x_return_status := gme_common_pvt.g_exceptions_err;
511: END IF;
512: l_upd_material := TRUE;
513: ELSIF l_incr_qty = 0 THEN
514: -- nothing to do
517: END IF;
518: l_upd_material := FALSE;
519: ELSE
520: -- consume or yield based on line type
521: IF l_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
522: IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
523: gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calling gme_release_batch_pvt.consume_material with target_qty= '||l_new_actual);
524: END IF;
525: gme_release_batch_pvt.consume_material
544: ,x_actual_qty => l_actual_qty
545: ,x_return_status => l_return_status);
546: END IF;
547:
548: IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_exceptions_err) THEN
549: x_return_status := l_return_status;
550: IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
551: gme_debug.put_line (g_pkg_name||'.'||l_api_name||' raising exception with x_return_status= '||x_return_status);
552: END IF;
552: END IF;
553: RAISE error_consum_yield;
554: END IF;
555:
556: IF l_return_status = gme_common_pvt.g_exceptions_err THEN
557: x_return_status := gme_common_pvt.g_exceptions_err;
558: END IF;
559: l_upd_material := TRUE;
560: END IF; -- IF l_new_actual = 0 THEN
553: RAISE error_consum_yield;
554: END IF;
555:
556: IF l_return_status = gme_common_pvt.g_exceptions_err THEN
557: x_return_status := gme_common_pvt.g_exceptions_err;
558: END IF;
559: l_upd_material := TRUE;
560: END IF; -- IF l_new_actual = 0 THEN
561:
581: END IF;
582:
583: UPDATE gme_material_details
584: SET actual_qty = l_actual_qty,
585: last_updated_by = gme_common_pvt.g_user_ident,
586: last_update_date = gme_common_pvt.g_timestamp,
587: last_update_login = gme_common_pvt.g_login_id
588: WHERE material_detail_id = l_batch_header_rec.parentline_id;
589: END IF;
582:
583: UPDATE gme_material_details
584: SET actual_qty = l_actual_qty,
585: last_updated_by = gme_common_pvt.g_user_ident,
586: last_update_date = gme_common_pvt.g_timestamp,
587: last_update_login = gme_common_pvt.g_login_id
588: WHERE material_detail_id = l_batch_header_rec.parentline_id;
589: END IF;
590: END IF; -- IF l_upd_material THEN
583: UPDATE gme_material_details
584: SET actual_qty = l_actual_qty,
585: last_updated_by = gme_common_pvt.g_user_ident,
586: last_update_date = gme_common_pvt.g_timestamp,
587: last_update_login = gme_common_pvt.g_login_id
588: WHERE material_detail_id = l_batch_header_rec.parentline_id;
589: END IF;
590: END IF; -- IF l_upd_material THEN
591: ELSE -- lab batches or non transactable materials
615:
616: -- Bug 8508788 - Let's update the parent ingredient with the correct actual qty.
617: UPDATE gme_material_details
618: SET actual_qty = l_new_actual,
619: last_updated_by = gme_common_pvt.g_user_ident,
620: last_update_date = gme_common_pvt.g_timestamp,
621: last_update_login = gme_common_pvt.g_login_id
622: WHERE material_detail_id = l_batch_header_rec.parentline_id;
623: END IF;
616: -- Bug 8508788 - Let's update the parent ingredient with the correct actual qty.
617: UPDATE gme_material_details
618: SET actual_qty = l_new_actual,
619: last_updated_by = gme_common_pvt.g_user_ident,
620: last_update_date = gme_common_pvt.g_timestamp,
621: last_update_login = gme_common_pvt.g_login_id
622: WHERE material_detail_id = l_batch_header_rec.parentline_id;
623: END IF;
624: -- END IF; -- IF l_batch_header_rec.laboratory_ind = 1
617: UPDATE gme_material_details
618: SET actual_qty = l_new_actual,
619: last_updated_by = gme_common_pvt.g_user_ident,
620: last_update_date = gme_common_pvt.g_timestamp,
621: last_update_login = gme_common_pvt.g_login_id
622: WHERE material_detail_id = l_batch_header_rec.parentline_id;
623: END IF;
624: -- END IF; -- IF l_batch_header_rec.laboratory_ind = 1
625: END IF; -- IF l_material_detail_rec.phantom_id IS NOT NULL THEN
674: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
675: RAISE ERROR_UPDATING_STEPS;
676: END IF;
677:
678: IF gme_common_pvt.g_ib_timestamp_set > 0 THEN
679: -- Bug 8508788 - Update any new transaction that was created for this item/step and all dependent steps
680: -- with the date value passed in by the user or sysdate if it is later than the start date of the resource.
681:
682: -- Bug 8751983 - Added DEL action to where clause so we stamp reversals also.
697:
698:
699: -- Bug 8751983 - We do not want to stamp new transactions caused by negative IB at this time.
700: -- New transaction should maintain original trans date being reversed, unless it is in a closed period.
701: IF gme_common_pvt.g_ib_timestamp_set = 1 THEN
702: update gme_resource_txns_gtmp
703: set trans_date = p_trans_date
704: where poc_trans_id in
705: (select t.poc_trans_id
712: AND t.action_code = 'ADD'
713: AND t.line_id = r.batchstep_resource_id
714: AND p_trans_date >= r.actual_start_date);
715: END IF;
716: END IF; -- IF gme_common_pvt.g_ib_timestamp_set > 0
717: END IF; -- IF l_in_batch_step_rec.batchstep_id IS NOT NULL THEN
718: END IF; -- IF l_batch_header_rec.automatic_step_calculation = 1 THEN
719:
720: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
722: END IF;
723:
724: EXCEPTION
725: WHEN error_fetch_batch OR error_fetch_matl OR error_update_row THEN
726: gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
727: x_return_status := FND_API.g_ret_sts_unexp_error;
728: WHEN error_cant_go_neg THEN
729: gme_common_pvt.log_message ('GME_API_ACTUAL_CANT_GO_NEG');
730: x_return_status := FND_API.g_ret_sts_error;
725: WHEN error_fetch_batch OR error_fetch_matl OR error_update_row THEN
726: gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
727: x_return_status := FND_API.g_ret_sts_unexp_error;
728: WHEN error_cant_go_neg THEN
729: gme_common_pvt.log_message ('GME_API_ACTUAL_CANT_GO_NEG');
730: x_return_status := FND_API.g_ret_sts_error;
731: WHEN error_derive_factor OR error_get_item OR error_phantom_backflush OR
732: error_revert_matl_full OR error_consum_yield OR error_revert_matl_part THEN
733: NULL;
934: -- If dependency is start to start and step is WIP then apply factor and process steps that
935: -- this step is dependent on, Pending and completed steps will not be touched
936: -- If dependency is finish to start and step is WIP then apply 100% of plan to actual and process steps that
937: -- this step is dependent on, Pending and completed steps will not be touched
938: IF (get_rec.step_status = gme_common_pvt.g_step_wip) THEN
939: IF (get_rec.dep_type = gme_common_pvt.g_dep_type_finish_start) THEN
940: l_backflush_factor := 1;
941: ELSE
942: l_backflush_factor := p_backflush_factor;
935: -- this step is dependent on, Pending and completed steps will not be touched
936: -- If dependency is finish to start and step is WIP then apply 100% of plan to actual and process steps that
937: -- this step is dependent on, Pending and completed steps will not be touched
938: IF (get_rec.step_status = gme_common_pvt.g_step_wip) THEN
939: IF (get_rec.dep_type = gme_common_pvt.g_dep_type_finish_start) THEN
940: l_backflush_factor := 1;
941: ELSE
942: l_backflush_factor := p_backflush_factor;
943: END IF;
987: ,p_lot_control_code IN NUMBER -- 1 = not lot control; 2 = lot control
988: ,p_create_resv_pend_lots IN NUMBER
989: ,p_lot_divisible_flag IN VARCHAR2
990: ,x_actual_qty OUT NOCOPY NUMBER
991: ,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
992: ,x_return_status OUT NOCOPY VARCHAR2) IS
993:
994: CURSOR cur_lot_qty
995: (v_item_id IN NUMBER
1003: AND m.inventory_item_id = v_item_id
1004: AND m.organization_id = v_organization_id
1005: AND m.transaction_source_id = v_batch_id
1006: AND m.trx_source_line_id = v_mat_det_id
1007: AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
1008: GROUP BY l.lot_number;
1009:
1010: TYPE lot_qty_tab IS TABLE OF NUMBER INDEX BY mtl_transaction_lot_numbers.lot_number%TYPE;
1011:
1033: -- Bug 13017256
1034: l_trans_date DATE;
1035:
1036: l_mmt_rec mtl_material_transactions%ROWTYPE;
1037: l_mmt_tbl gme_common_pvt.mtl_mat_tran_tbl;
1038:
1039: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
1040: l_mmln_tbl_orig gme_common_pvt.mtl_trans_lots_num_tbl;
1041: l_mmln_tbl_new gme_common_pvt.mtl_trans_lots_num_tbl;
1035:
1036: l_mmt_rec mtl_material_transactions%ROWTYPE;
1037: l_mmt_tbl gme_common_pvt.mtl_mat_tran_tbl;
1038:
1039: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
1040: l_mmln_tbl_orig gme_common_pvt.mtl_trans_lots_num_tbl;
1041: l_mmln_tbl_new gme_common_pvt.mtl_trans_lots_num_tbl;
1042:
1043: -- Bug 9072371 add this table to allow checking for one lot.
1036: l_mmt_rec mtl_material_transactions%ROWTYPE;
1037: l_mmt_tbl gme_common_pvt.mtl_mat_tran_tbl;
1038:
1039: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
1040: l_mmln_tbl_orig gme_common_pvt.mtl_trans_lots_num_tbl;
1041: l_mmln_tbl_new gme_common_pvt.mtl_trans_lots_num_tbl;
1042:
1043: -- Bug 9072371 add this table to allow checking for one lot.
1044: l_mmln_tbl_lot gme_common_pvt.mtl_trans_lots_num_tbl;
1037: l_mmt_tbl gme_common_pvt.mtl_mat_tran_tbl;
1038:
1039: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
1040: l_mmln_tbl_orig gme_common_pvt.mtl_trans_lots_num_tbl;
1041: l_mmln_tbl_new gme_common_pvt.mtl_trans_lots_num_tbl;
1042:
1043: -- Bug 9072371 add this table to allow checking for one lot.
1044: l_mmln_tbl_lot gme_common_pvt.mtl_trans_lots_num_tbl;
1045: ln mtl_transaction_lot_numbers.lot_number%TYPE;
1040: l_mmln_tbl_orig gme_common_pvt.mtl_trans_lots_num_tbl;
1041: l_mmln_tbl_new gme_common_pvt.mtl_trans_lots_num_tbl;
1042:
1043: -- Bug 9072371 add this table to allow checking for one lot.
1044: l_mmln_tbl_lot gme_common_pvt.mtl_trans_lots_num_tbl;
1045: ln mtl_transaction_lot_numbers.lot_number%TYPE;
1046: l_lot_number mtl_transaction_lot_numbers.lot_number%TYPE;
1047: /* Jalaj Srivastava Bug 5021522*/
1048: l_item_no varchar2(2000);
1073: l_qty := p_qty;
1074: l_total_decr_qty := 0;
1075:
1076: -- Bug 13017256 - Let's initialize the variable with the user entered date.
1077: l_trans_date := NVL(gme_common_pvt.g_ib_timestamp_date, gme_common_pvt.g_timestamp);
1078:
1079: -- Bug 8751983 - Added order by clause to fetch in reverse trans order.
1080: gme_transactions_pvt.get_mat_trans
1081: (p_mat_det_id => p_material_detail_rec.material_detail_id
1097: l_mmt_tbl(i).transaction_quantity := ABS(l_mmt_tbl(i).transaction_quantity);
1098: END LOOP;
1099:
1100: -- set the transaction type to consider for deleting/updating
1101: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_prod THEN
1102: l_trxn_type := gme_common_pvt.g_prod_completion;
1103: ELSIF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod THEN
1104: l_trxn_type := gme_common_pvt.g_byprod_completion;
1105: ELSE
1098: END LOOP;
1099:
1100: -- set the transaction type to consider for deleting/updating
1101: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_prod THEN
1102: l_trxn_type := gme_common_pvt.g_prod_completion;
1103: ELSIF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod THEN
1104: l_trxn_type := gme_common_pvt.g_byprod_completion;
1105: ELSE
1106: l_trxn_type := gme_common_pvt.g_ing_issue;
1099:
1100: -- set the transaction type to consider for deleting/updating
1101: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_prod THEN
1102: l_trxn_type := gme_common_pvt.g_prod_completion;
1103: ELSIF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod THEN
1104: l_trxn_type := gme_common_pvt.g_byprod_completion;
1105: ELSE
1106: l_trxn_type := gme_common_pvt.g_ing_issue;
1107: END IF;
1100: -- set the transaction type to consider for deleting/updating
1101: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_prod THEN
1102: l_trxn_type := gme_common_pvt.g_prod_completion;
1103: ELSIF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod THEN
1104: l_trxn_type := gme_common_pvt.g_byprod_completion;
1105: ELSE
1106: l_trxn_type := gme_common_pvt.g_ing_issue;
1107: END IF;
1108:
1102: l_trxn_type := gme_common_pvt.g_prod_completion;
1103: ELSIF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod THEN
1104: l_trxn_type := gme_common_pvt.g_byprod_completion;
1105: ELSE
1106: l_trxn_type := gme_common_pvt.g_ing_issue;
1107: END IF;
1108:
1109: /* Jalaj Srivastava Bug 5021522
1110: get item_no only for prod/byprod.
1108:
1109: /* Jalaj Srivastava Bug 5021522
1110: get item_no only for prod/byprod.
1111: In IB, there is no ing return transaction*/
1112: IF (l_trxn_type IN (gme_common_pvt.g_prod_completion, gme_common_pvt.g_byprod_completion)) THEN
1113: SELECT concatenated_segments
1114: INTO l_item_no
1115: FROM mtl_system_items_kfv
1116: WHERE inventory_item_id = p_material_detail_rec.inventory_item_id
1116: WHERE inventory_item_id = p_material_detail_rec.inventory_item_id
1117: AND organization_id = p_material_detail_rec.organization_id;
1118: END IF;
1119:
1120: IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
1121: IF p_lot_divisible_flag = 'N' THEN
1122: l_whole_qty := TRUE;
1123: IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1124: gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_whole_qty = TRUE because lot indivisible item');
1171: l_trxn_qty := l_mmt_rec.transaction_quantity;
1172: IF l_trxn_qty <= l_qty OR l_whole_qty THEN
1173: /* Jalaj Srivastava Bug 5021522
1174: call check_inv_negative only for prod/byprod. in IB, there is no ing return transaction */
1175: IF (l_trxn_type IN (gme_common_pvt.g_prod_completion, gme_common_pvt.g_byprod_completion)) THEN
1176: -- Bug 8639523 - No need to do negative inventory checking for phantom prods or ingredients.
1177: IF (NVL(p_material_detail_rec.phantom_line_id, 0) = 0) THEN
1178: IF gme_unrelease_batch_pvt.check_inv_negative
1179: ( p_mmt_rec => l_mmt_rec
1195: ,p_trans_date => l_trans_date
1196: ,p_txns_pair => NULL
1197: ,x_return_status => l_return_status);
1198:
1199: IF l_return_status = gme_common_pvt.g_not_transactable THEN
1200: -- do nothing... move on to the next, to try to reduce
1201: IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1202: gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; gme_transactions_pvt.delete_material_txn returned '||l_return_status);
1203: END IF;
1226: l_mmt_rec.secondary_transaction_quantity := NULL;
1227: l_mmt_rec.primary_quantity := NULL;
1228:
1229: -- Bug 13017256 - Let's stamp the trans record with user entered date.
1230: IF NOT gme_common_pvt.check_close_period(p_org_id => l_mmt_rec.organization_id
1231: ,p_trans_date => l_mmt_rec.transaction_date) THEN
1232:
1233: -- Let's default to timestamp and overwrite if the user entered a different date.
1234: l_mmt_rec.transaction_date := l_trans_date;
1236:
1237: /* Jalaj Srivastava Bug 5021522
1238: call check_inv_negative only for prod/byprod.
1239: in IB, there is no ing return transaction*/
1240: IF (l_trxn_type IN (gme_common_pvt.g_prod_completion, gme_common_pvt.g_byprod_completion)) THEN
1241: IF gme_unrelease_batch_pvt.check_inv_negative
1242: ( p_mmt_rec => l_mmt_rec
1243: ,p_mmln_tbl => l_mmln_tbl
1244: ,p_item_no => l_item_no) THEN
1255: (p_mmt_rec => l_mmt_rec
1256: ,p_mmln_tbl => l_mmln_tbl
1257: ,x_return_status => l_return_status);
1258:
1259: IF l_return_status = gme_common_pvt.g_not_transactable THEN
1260: -- do nothing... move on to the next, to try to reduce
1261: IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1262: gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; gme_transactions_pvt.update_material_txn returned '||l_return_status);
1263: END IF;
1384:
1385: -- Bug 9072371 - Check potential negative inventory for each specific lot.
1386: -- Continue to next lot if this one cannot be used.
1387: l_skip_lot := 0;
1388: IF (l_trxn_type IN (gme_common_pvt.g_prod_completion, gme_common_pvt.g_byprod_completion)) THEN
1389: l_mmln_tbl_lot(1) := l_mmln_tbl(j);
1390: IF gme_unrelease_batch_pvt.check_inv_negative
1391: ( p_mmt_rec => l_mmt_rec
1392: ,p_mmln_tbl => l_mmln_tbl_lot
1467:
1468: /* Jalaj Srivastava Bug 5021522
1469: call check_inv_negative only for prod/byprod.
1470: in IB, there is no ing return transaction*/
1471: IF (l_trxn_type IN (gme_common_pvt.g_prod_completion, gme_common_pvt.g_byprod_completion)) THEN
1472: IF gme_unrelease_batch_pvt.check_inv_negative
1473: ( p_mmt_rec => l_mmt_rec
1474: ,p_mmln_tbl => l_mmln_tbl_orig
1475: ,p_item_no => l_item_no) THEN
1489: ,p_txns_pair => NULL
1490: ,p_trans_date => l_trans_date
1491: ,x_return_status => l_return_status);
1492:
1493: IF l_return_status = gme_common_pvt.g_not_transactable THEN
1494: l_trxn_success := FALSE;
1495: -- do nothing... move on to the next, to try to reduce
1496: IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1497: gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; gme_transactions_pvt.delete_material_txn returned '||l_return_status);
1527: l_mmt_rec.secondary_transaction_quantity := NULL;
1528: l_mmt_rec.primary_quantity := NULL;
1529:
1530: -- Bug 13017256 - Let's stamp the trans record with user entered date.
1531: IF NOT gme_common_pvt.check_close_period(p_org_id => l_mmt_rec.organization_id
1532: ,p_trans_date => l_mmt_rec.transaction_date) THEN
1533:
1534: -- Let's default to timestamp and overwrite if the user entered a different date.
1535: l_mmt_rec.transaction_date := l_trans_date;
1537:
1538: /* Jalaj Srivastava Bug 5021522
1539: call check_inv_negative only for prod/byprod.
1540: in IB, there is no ing return transaction*/
1541: IF (l_trxn_type IN (gme_common_pvt.g_prod_completion, gme_common_pvt.g_byprod_completion)) THEN
1542: IF gme_unrelease_batch_pvt.check_inv_negative
1543: ( p_mmt_rec => l_mmt_rec
1544: ,p_mmln_tbl => l_mmln_tbl_new
1545: ,p_item_no => l_item_no) THEN
1569: (p_mmt_rec => l_mmt_rec
1570: ,p_mmln_tbl => l_mmln_tbl_new
1571: ,x_return_status => l_return_status);
1572:
1573: IF l_return_status = gme_common_pvt.g_not_transactable THEN
1574: l_trxn_success := FALSE;
1575: -- do nothing... move on to the next, to try to reduce
1576: IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1577: gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; gme_transactions_pvt.update_material_txn returned '||l_return_status);
1720: -- Set the return status to success initially
1721: x_return_status := FND_API.G_RET_STS_SUCCESS;
1722:
1723: -- IB not allowed for FPO
1724: IF p_batch_header_rec.batch_type = gme_common_pvt.g_doc_type_fpo THEN
1725: RAISE ERROR_INV_ACTION_FPO;
1726: END IF;
1727:
1728: --Bug#5111078 IB not allowed for lab batches with update inventory OFF
1735: RAISE error_inv_action_phantom;
1736: END IF;
1737:
1738: -- IB allowed if batch status is WIP or Complete
1739: IF p_batch_header_rec.batch_status NOT IN (gme_common_pvt.g_batch_wip
1740: ,gme_common_pvt.g_batch_completed) THEN
1741: RAISE ERROR_INV_BATCH_STATUS;
1742: END IF;
1743:
1736: END IF;
1737:
1738: -- IB allowed if batch status is WIP or Complete
1739: IF p_batch_header_rec.batch_status NOT IN (gme_common_pvt.g_batch_wip
1740: ,gme_common_pvt.g_batch_completed) THEN
1741: RAISE ERROR_INV_BATCH_STATUS;
1742: END IF;
1743:
1744: -- If batch is complete then proceed only if user wants to adjust qty
1741: RAISE ERROR_INV_BATCH_STATUS;
1742: END IF;
1743:
1744: -- If batch is complete then proceed only if user wants to adjust qty
1745: IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_completed THEN
1746: IF p_adjust_cmplt = fnd_api.g_false THEN
1747: RAISE ERROR_ADJ_CMPLT_NOT_ALLOW;
1748: END IF;
1749: END IF;
1753: RAISE ERROR_INV_WIP_PLAN_QTY;
1754: END IF;
1755:
1756: -- Check Release Type
1757: IF p_material_detail_rec.release_type IN (gme_common_pvt.g_mtl_auto_release
1758: ,gme_common_pvt.g_mtl_autobystep_release) THEN
1759: RAISE ERROR_INV_RELEASE_TYPE;
1760: END IF;
1761:
1754: END IF;
1755:
1756: -- Check Release Type
1757: IF p_material_detail_rec.release_type IN (gme_common_pvt.g_mtl_auto_release
1758: ,gme_common_pvt.g_mtl_autobystep_release) THEN
1759: RAISE ERROR_INV_RELEASE_TYPE;
1760: END IF;
1761:
1762: -- If the step associated with the material line is closed or cancelled
1762: -- If the step associated with the material line is closed or cancelled
1763: OPEN Cur_get_step_status (p_material_detail_rec.material_detail_id);
1764: FETCH Cur_get_step_status INTO l_step_no, l_step_status;
1765: CLOSE Cur_get_step_status;
1766: IF NVL(l_step_status, 0) = gme_common_pvt.g_step_closed THEN
1767: RAISE error_step_closed;
1768: END IF;
1769: IF NVL(l_step_status, 0) = gme_common_pvt.g_step_cancelled THEN
1770: RAISE error_step_cancelled;
1765: CLOSE Cur_get_step_status;
1766: IF NVL(l_step_status, 0) = gme_common_pvt.g_step_closed THEN
1767: RAISE error_step_closed;
1768: END IF;
1769: IF NVL(l_step_status, 0) = gme_common_pvt.g_step_cancelled THEN
1770: RAISE error_step_cancelled;
1771: END IF;
1772:
1773: IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1776:
1777: EXCEPTION
1778: WHEN ERROR_INV_ACTION_FPO THEN
1779: x_return_status := FND_API.G_RET_STS_ERROR;
1780: gme_common_pvt.log_message('GME_INV_ACTION_FPO');
1781: --Bug#5111078
1782: WHEN error_inv_action_lab THEN
1783: x_return_status := FND_API.G_RET_STS_ERROR;
1784: gme_common_pvt.log_message('GME_IB_FOR_UPDINV_NT_ALWD');
1780: gme_common_pvt.log_message('GME_INV_ACTION_FPO');
1781: --Bug#5111078
1782: WHEN error_inv_action_lab THEN
1783: x_return_status := FND_API.G_RET_STS_ERROR;
1784: gme_common_pvt.log_message('GME_IB_FOR_UPDINV_NT_ALWD');
1785: WHEN ERROR_INV_BATCH_STATUS THEN
1786: x_return_status := FND_API.G_RET_STS_ERROR;
1787: gme_common_pvt.log_message('GME_API_INV_BATCH_STATUS_PC');
1788: WHEN ERROR_INV_RELEASE_TYPE THEN
1783: x_return_status := FND_API.G_RET_STS_ERROR;
1784: gme_common_pvt.log_message('GME_IB_FOR_UPDINV_NT_ALWD');
1785: WHEN ERROR_INV_BATCH_STATUS THEN
1786: x_return_status := FND_API.G_RET_STS_ERROR;
1787: gme_common_pvt.log_message('GME_API_INV_BATCH_STATUS_PC');
1788: WHEN ERROR_INV_RELEASE_TYPE THEN
1789: x_return_status := FND_API.G_RET_STS_ERROR;
1790: gme_common_pvt.log_message('GME_API_INV_RELEASE_TYPE');
1791: WHEN ERROR_INV_WIP_PLAN_QTY THEN
1786: x_return_status := FND_API.G_RET_STS_ERROR;
1787: gme_common_pvt.log_message('GME_API_INV_BATCH_STATUS_PC');
1788: WHEN ERROR_INV_RELEASE_TYPE THEN
1789: x_return_status := FND_API.G_RET_STS_ERROR;
1790: gme_common_pvt.log_message('GME_API_INV_RELEASE_TYPE');
1791: WHEN ERROR_INV_WIP_PLAN_QTY THEN
1792: x_return_status := FND_API.G_RET_STS_ERROR;
1793: gme_common_pvt.log_message('GME_API_INV_WIP_PLAN_QTY_PC');
1794: WHEN ERROR_ADJ_CMPLT_NOT_ALLOW THEN
1789: x_return_status := FND_API.G_RET_STS_ERROR;
1790: gme_common_pvt.log_message('GME_API_INV_RELEASE_TYPE');
1791: WHEN ERROR_INV_WIP_PLAN_QTY THEN
1792: x_return_status := FND_API.G_RET_STS_ERROR;
1793: gme_common_pvt.log_message('GME_API_INV_WIP_PLAN_QTY_PC');
1794: WHEN ERROR_ADJ_CMPLT_NOT_ALLOW THEN
1795: x_return_status := FND_API.G_RET_STS_ERROR;
1796: gme_common_pvt.log_message('GME_API_ASK_ADJUST_CERTIFY');
1797: WHEN error_step_closed THEN
1792: x_return_status := FND_API.G_RET_STS_ERROR;
1793: gme_common_pvt.log_message('GME_API_INV_WIP_PLAN_QTY_PC');
1794: WHEN ERROR_ADJ_CMPLT_NOT_ALLOW THEN
1795: x_return_status := FND_API.G_RET_STS_ERROR;
1796: gme_common_pvt.log_message('GME_API_ASK_ADJUST_CERTIFY');
1797: WHEN error_step_closed THEN
1798: x_return_status := FND_API.G_RET_STS_ERROR;
1799: GME_COMMON_PVT.log_message ('GME_STEP_CLOSED_ERR', 'STEP_NO', l_step_no);
1800: WHEN error_step_cancelled THEN
1795: x_return_status := FND_API.G_RET_STS_ERROR;
1796: gme_common_pvt.log_message('GME_API_ASK_ADJUST_CERTIFY');
1797: WHEN error_step_closed THEN
1798: x_return_status := FND_API.G_RET_STS_ERROR;
1799: GME_COMMON_PVT.log_message ('GME_STEP_CLOSED_ERR', 'STEP_NO', l_step_no);
1800: WHEN error_step_cancelled THEN
1801: x_return_status := FND_API.G_RET_STS_ERROR;
1802: GME_COMMON_PVT.log_message ('GME_STEP_CANCELLED_ERR', 'STEP_NO', l_step_no);
1803: WHEN error_inv_action_phantom THEN
1798: x_return_status := FND_API.G_RET_STS_ERROR;
1799: GME_COMMON_PVT.log_message ('GME_STEP_CLOSED_ERR', 'STEP_NO', l_step_no);
1800: WHEN error_step_cancelled THEN
1801: x_return_status := FND_API.G_RET_STS_ERROR;
1802: GME_COMMON_PVT.log_message ('GME_STEP_CANCELLED_ERR', 'STEP_NO', l_step_no);
1803: WHEN error_inv_action_phantom THEN
1804: x_return_status := FND_API.G_RET_STS_ERROR;
1805: gme_common_pvt.log_message('PM_INVALID_PHANTOM_ACTION');
1806: WHEN OTHERS THEN
1801: x_return_status := FND_API.G_RET_STS_ERROR;
1802: GME_COMMON_PVT.log_message ('GME_STEP_CANCELLED_ERR', 'STEP_NO', l_step_no);
1803: WHEN error_inv_action_phantom THEN
1804: x_return_status := FND_API.G_RET_STS_ERROR;
1805: gme_common_pvt.log_message('PM_INVALID_PHANTOM_ACTION');
1806: WHEN OTHERS THEN
1807: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1808: IF g_debug <= gme_debug.g_log_procedure THEN
1809: gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
1866:
1867: EXCEPTION
1868: WHEN ERROR_INV_INCR_TYPE THEN
1869: x_return_status := FND_API.G_RET_STS_ERROR;
1870: gme_common_pvt.log_message( 'GME_API_INVALID_INCR_TYPE');
1871: WHEN ERROR_QTY_CREATE_NEG_ACTUAL THEN
1872: x_return_status := FND_API.G_RET_STS_ERROR;
1873: gme_common_pvt.log_message( 'GME_API_ACTUAL_CANT_GO_NEG');
1874: WHEN ERROR_QTY_CANT_BE_ZERO THEN
1869: x_return_status := FND_API.G_RET_STS_ERROR;
1870: gme_common_pvt.log_message( 'GME_API_INVALID_INCR_TYPE');
1871: WHEN ERROR_QTY_CREATE_NEG_ACTUAL THEN
1872: x_return_status := FND_API.G_RET_STS_ERROR;
1873: gme_common_pvt.log_message( 'GME_API_ACTUAL_CANT_GO_NEG');
1874: WHEN ERROR_QTY_CANT_BE_ZERO THEN
1875: x_return_status := FND_API.G_RET_STS_ERROR;
1876: gme_common_pvt.log_message( 'GME_API_QTY_CANT_BE_ZERO');
1877: WHEN OTHERS THEN
1872: x_return_status := FND_API.G_RET_STS_ERROR;
1873: gme_common_pvt.log_message( 'GME_API_ACTUAL_CANT_GO_NEG');
1874: WHEN ERROR_QTY_CANT_BE_ZERO THEN
1875: x_return_status := FND_API.G_RET_STS_ERROR;
1876: gme_common_pvt.log_message( 'GME_API_QTY_CANT_BE_ZERO');
1877: WHEN OTHERS THEN
1878: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1879: IF g_debug <= gme_debug.g_log_procedure THEN
1880: gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
1923: x_conv_qty := inv_convert.inv_um_convert
1924: (item_id => p_item_id
1925: ,lot_number => p_lot_number
1926: ,organization_id => p_org_id
1927: ,precision => gme_common_pvt.g_precision
1928: ,from_quantity => p_qty
1929: ,from_unit => p_from_um
1930: ,to_unit => p_to_um
1931: ,from_name => NULL
1930: ,to_unit => p_to_um
1931: ,from_name => NULL
1932: ,to_name => NULL);
1933:
1934: -- Note: -99999 should be in gme_common_pvt
1935: IF x_conv_qty = -99999 THEN
1936: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1937: gme_debug.put_line
1938: ( g_pkg_name