[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
validate_material_for_IB is changed for not allowing IB for Lab Batches with update inventory off. And
plan_qty = 0 check is replaced by wip_plan_qty
G. Muratore 12-Feb-2009 Bug 7709971
Back out and rework 7286054 as now phantom ingredient gets double posting. Reinstated elsif
phantom ingredients are reconciled by the phantom prod yield.
Note: We could not duplicate this problem with lot control items even with 7286054 in place.
New fix works for all item types.
G. Muratore 04-Mar-2009 Bug 8267588
Once a new_actual is derived for a given line, round it to 5 decimal places.
G. Muratore 21-May-2009 Bug 8508788
Update any new transaction that was created for this item/step and all dependent steps with the
trans_date passed in by the user or sysdate.
Also, reworked 8516257 which was an additional rework of bug Bug 7709971 and 7286054. Typo was corrected.
G. Muratore 02-Jun-2009 Bug 8508788 - Backout resource txn date piece of previous fix by commenting the lines.
We may need to reinstitute this code as part of a bigger fix down the road after getting full design.
G. Muratore 19-Jun-2009 Bug 8508788
Reinstating the original fix with PM approval. One modification. The trans_date passed in, or sysdate,
will be used only if it is later than the actual start date of the resource.
G. Muratore 19-MAR-2010 Bug 8751983
Changed order by clause to fetch material transactions so that for negative IB
they are processed in reverse trans order. Also, stamp resource transactions
affected by IB with user entered IB date. Additional issue addressed here is bug 9072371.
Do not reverse lot transactions if it will lead to a negative inventory balance.
PROCEDURE: revert_material_partial
G. Muratore 09-APR-2010 Bug 9560022
Round new actual before comparing to original actual.
G. Muratore 05-MAY-2010 Bug 9628831
Do not update the actual quantity for non transactable material items.
========================================================================================================*/
PROCEDURE incremental_backflush
(p_batch_header_rec IN GME_BATCH_HEADER%ROWTYPE
,p_material_detail_rec IN GME_MATERIAL_DETAILS%ROWTYPE
,p_qty IN NUMBER
,p_qty_type IN NUMBER
,p_trans_date IN DATE
,p_backflush_rsrc_usg_ind IN NUMBER
,x_exception_material_tbl IN OUT NOCOPY gme_common_pvt.exceptions_tab
,x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'incremental_backflush';
SELECT *
FROM gme_material_details
WHERE batch_id = v_batch_id
AND (release_type = gme_common_pvt.g_mtl_incremental_release
OR
(release_type = gme_common_pvt.g_mtl_manual_release AND material_detail_id = v_matl_dtl_id)
)
AND wip_plan_qty <> 0
ORDER BY line_type,line_no;
SELECT *
FROM gme_batch_steps
WHERE batchstep_id IN (SELECT DISTINCT batchstep_id
FROM gme_batch_step_items
WHERE batch_id = v_batch_id);
SELECT s.*
FROM gme_batch_step_items m, gme_batch_steps s
WHERE m.batch_id = V_batch_id
AND m.material_detail_id = V_material_detail_id
AND s.batch_id = m.batch_id
AND s.batchstep_id = m.batchstep_id
AND s.step_status = gme_common_pvt.g_step_wip;
SELECT ib_factor_ind
FROM gme_parameters
WHERE organization_id = V_org_id;
error_update_row EXCEPTION;
update_step_qty_error EXCEPTION;
ELSIF l_batch_header_rec.update_inventory_ind = 'Y' AND
l_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
IF l_new_actual = 0 THEN
-- full revert
IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' new actual = 0; calling gme_unrelease_batch_pvt.revert_material_full');
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calling update material actual qty= '||l_material_detail_rec.actual_qty);
IF NOT gme_material_details_dbl.update_row (l_material_detail_rec) THEN
RAISE error_update_row;
UPDATE gme_material_details
SET actual_qty = l_actual_qty,
last_updated_by = gme_common_pvt.g_user_ident,
last_update_date = gme_common_pvt.g_timestamp,
last_update_login = gme_common_pvt.g_login_id
WHERE material_detail_id = l_batch_header_rec.parentline_id;
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calling update material actual qty= '||l_material_detail_rec.actual_qty);
IF NOT gme_material_details_dbl.update_row (l_material_detail_rec) THEN
RAISE error_update_row;
UPDATE gme_material_details
SET actual_qty = l_new_actual,
last_updated_by = gme_common_pvt.g_user_ident,
last_update_date = gme_common_pvt.g_timestamp,
last_update_login = gme_common_pvt.g_login_id
WHERE material_detail_id = l_batch_header_rec.parentline_id;
gme_update_step_qty_pvt.update_step_qty
(p_batch_step_rec => l_step_tbl(i)
,x_message_count => l_msg_count
,x_message_list => l_msg_stack
,x_return_status => l_return_status
,x_batch_step_rec => l_batch_step_rec);
RAISE update_step_qty_error;
gme_update_step_qty_pvt.update_step_qty
(p_batch_step_rec => l_in_batch_step_rec
,p_backflush_factor => l_incr_factor_res/100
,x_message_count => l_msg_count
,x_message_list => l_msg_stack
,x_return_status => l_return_status
,x_batch_step_rec => l_batch_step_rec);
RAISE update_step_qty_error;
update_dependent_steps
(p_batchstep_id => l_in_batch_step_rec.batchstep_id
,p_backflush_factor => l_incr_factor_res/100
,x_return_status => l_return_status);
update gme_resource_txns_gtmp
set trans_date = p_trans_date
where poc_trans_id in
(select t.poc_trans_id
FROM gme_batch_steps s, gme_batch_step_activities a, gme_batch_step_resources r, gme_resource_txns_gtmp t
WHERE s.batch_id = l_batch_header_rec.batch_id
-- Comment out following update as original fix was just for one step.
-- AND a.batchstep_id = l_in_batch_step_rec.batchstep_id
AND a.batchstep_id = s.batchstep_id
AND r.batchstep_activity_id = a.batchstep_activity_id
AND t.action_code = 'DEL'
AND t.line_id = r.batchstep_resource_id
AND p_trans_date >= r.actual_start_date);
update gme_resource_txns_gtmp
set trans_date = p_trans_date
where poc_trans_id in
(select t.poc_trans_id
FROM gme_batch_steps s, gme_batch_step_activities a, gme_batch_step_resources r, gme_resource_txns_gtmp t
WHERE s.batch_id = l_batch_header_rec.batch_id
-- Comment out following update as original fix was just for one step.
-- AND a.batchstep_id = l_in_batch_step_rec.batchstep_id
AND a.batchstep_id = s.batchstep_id
AND r.batchstep_activity_id = a.batchstep_activity_id
AND t.action_code = 'ADD'
AND t.line_id = r.batchstep_resource_id
AND p_trans_date >= r.actual_start_date);
WHEN error_fetch_batch OR error_fetch_matl OR error_update_row THEN
gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
WHEN update_step_qty_error OR ERROR_UPDATING_STEPS THEN
x_return_status := l_return_status;
PROCEDURE update_dependent_steps(p_batchstep_id IN NUMBER
,p_backflush_factor IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_dependent_steps';
SELECT d.dep_step_id, d.dep_type, s.step_status
FROM gme_batch_step_dependencies d, gme_batch_steps s
WHERE d.batchstep_id = V_batchstep_id
AND s.batchstep_id = d.dep_step_id;
gme_update_step_qty_pvt.update_step_qty
(p_batch_step_rec => l_in_batch_step_rec
,x_message_count => l_message_count
,x_message_list => l_message_list
,x_return_status => x_return_status
,x_batch_step_rec => l_batch_step_rec
,p_backflush_factor => l_backflush_factor
,p_dependency_type => get_rec.dep_type);
update_dependent_steps
(p_batchstep_id => get_rec.dep_step_id
,p_backflush_factor => p_backflush_factor
,x_return_status => x_return_status);
END update_dependent_steps;
SELECT lot_number, SUM (l.transaction_quantity) sum_trx
FROM mtl_material_transactions m, mtl_transaction_lot_numbers l
WHERE l.transaction_id = m.transaction_id
AND m.inventory_item_id = v_item_id
AND m.organization_id = v_organization_id
AND m.transaction_source_id = v_batch_id
AND m.trx_source_line_id = v_mat_det_id
AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
GROUP BY l.lot_number;
SELECT concatenated_segments
INTO l_item_no
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_material_detail_rec.inventory_item_id
AND organization_id = p_material_detail_rec.organization_id;
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; calling gme_transactions_pvt.delete_material_txn for trxns_id='||l_mmt_rec.transaction_id);
gme_transactions_pvt.delete_material_txn
(p_transaction_id => l_mmt_rec.transaction_id
,p_trans_date => l_trans_date
,p_txns_pair => NULL
,x_return_status => l_return_status);
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; gme_transactions_pvt.delete_material_txn returned '||l_return_status);
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; calling gme_transactions_pvt.update_material_txn for trxns_id='||l_mmt_rec.transaction_id);
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; update trxn with qty='||l_mmt_rec.transaction_quantity);
gme_transactions_pvt.update_material_txn
(p_mmt_rec => l_mmt_rec
,p_mmln_tbl => l_mmln_tbl
,x_return_status => l_return_status);
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; gme_transactions_pvt.update_material_txn returned '||l_return_status);
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; calling gme_transactions_pvt.delete_material_txn with trxn_id='||l_mmt_tbl(i).transaction_id);
gme_transactions_pvt.delete_material_txn
(p_transaction_id => l_mmt_tbl(i).transaction_id
,p_txns_pair => NULL
,p_trans_date => l_trans_date
,x_return_status => l_return_status);
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; gme_transactions_pvt.delete_material_txn returned '||l_return_status);
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' calling gme_transactions_pvt.update_material_txn with updatd l_mmt_rec and new l_mmln_tbl_new');
gme_transactions_pvt.update_material_txn
(p_mmt_rec => l_mmt_rec
,p_mmln_tbl => l_mmln_tbl_new
,x_return_status => l_return_status);
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' in trxn loop; gme_transactions_pvt.update_material_txn returned '||l_return_status);
SELECT s.batchstep_no, step_status
FROM gme_batch_steps s, gme_batch_step_items i
WHERE s.batchstep_id = i.batchstep_id
AND i.material_detail_id = v_material_detail_id;
IF p_batch_header_rec.update_inventory_ind <> 'Y' THEN
RAISE error_inv_action_lab;