[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_material_line
(p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_material_detail_rec IN gme_material_details%ROWTYPE
,p_batch_step_rec IN gme_batch_steps%ROWTYPE
,p_trans_id IN NUMBER
,x_transacted OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'insert_material_line';
SELECT recipe_id
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
UPDATE gme_material_details
SET line_no = line_no + 1
,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 batch_id = p_material_detail_rec.batch_id
AND line_type = p_material_detail_rec.line_type
AND line_no >= p_material_detail_rec.line_no;
IF NOT gme_material_details_dbl.insert_row (p_material_detail_rec
,x_material_detail_rec) THEN
l_proc := 'gme_material_details_dbl.insert_row';
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' inserted material; material_detail_id= '
,p_insert => FND_API.g_true
,x_transacted => x_transacted
,x_return_status => x_return_status);
IF NOT gme_batch_step_items_dbl.insert_row
(p_batch_step_items => l_batchstep_items
,x_batch_step_items => l_batchstep_items) THEN
l_proc := 'gme_batch_step_items_dbl.insert_row';
/* Bug 4866700 added update inventory check */
IF x_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing AND
x_material_detail_rec.phantom_type = 0 AND
p_batch_header_rec.update_inventory_ind = 'Y' THEN
l_material_detail_tbl (1) := x_material_detail_rec;
IF NOT gme_material_details_dbl.update_row (x_material_detail_rec) THEN
l_proc := 'gme_material_details_dbl.update_row';
gme_debug.put_line (g_pkg_name||'.'||l_api_name||' updated material; material_detail_id= '
gme_update_step_qty_pvt.update_step_qty
(p_batch_step_rec => p_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);
l_proc := 'gme_update_step_qty_pvt.update_step_qty';
END insert_material_line;
,p_insert IN VARCHAR2
,x_transacted OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR item_no_cursor (v_org_id NUMBER, v_inventory_item_id NUMBER)
IS
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_org_id;
gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_insert='||p_insert);
,p_update_inventory_ind => p_batch_header_rec.update_inventory_ind
,p_batchstep_id => p_batch_step_rec.batchstep_id
,p_step_status => p_batch_step_rec.step_status
,p_lot_control_code => l_item_rec.lot_control_code
,p_location_control_code => NVL(l_item_rec.location_control_code,1)
,p_restrict_locators_code => l_item_rec.restrict_locators_code
,p_insert => p_insert);
gme_debug.put_line(g_pkg_name||'.'||l_api_name||': open_actual_qty returned -1; can not update actual qty');
IF p_batch_header_rec.update_inventory_ind = 'Y' AND
(l_trans_id > 0 OR
(l_trans_id = 0 AND p_material_detail_rec.actual_qty > 0)) THEN
process_actual_qty (p_batch_header_rec => p_batch_header_rec
,p_material_detail_rec => p_material_detail_rec
,p_batch_step_rec => p_batch_step_rec
,p_trans_id => l_trans_id
,p_item_rec => l_item_rec
,x_return_status => x_return_status);
* >0 => open actual qty: Yes with transaction id returned; must update existing transaction
,p_update_inventory_ind IN VARCHAR2
,p_batchstep_id IN NUMBER DEFAULT NULL
,p_step_status IN NUMBER DEFAULT NULL
,p_lot_control_code IN NUMBER DEFAULT NULL
,p_location_control_code IN NUMBER DEFAULT NULL
,p_restrict_locators_code IN NUMBER DEFAULT NULL
,p_insert IN VARCHAR2)
RETURN NUMBER
IS
CURSOR cur_get_trans (v_material_detail_id NUMBER, v_batch_id NUMBER)
IS
SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_source_id = v_batch_id
AND trx_source_line_id = v_material_detail_id
AND transaction_id NOT IN (
SELECT transaction_id1
FROM gme_transaction_pairs
WHERE batch_id = v_batch_id
AND material_detail_id = v_material_detail_id
AND pair_type = gme_common_pvt.g_pairs_reversal_type);
SELECT COUNT (1)
FROM mtl_transaction_lot_numbers
WHERE transaction_id = v_trans_id;
SELECT locator_type
FROM mtl_secondary_inventories
WHERE organization_id = v_org_id
AND secondary_inventory_name = v_subinventory;
SELECT lot_control_code, NVL(location_control_code, 1) location_control_code, restrict_locators_code,
mtl_transactions_enabled_flag
FROM mtl_system_items_b
WHERE organization_id = v_org_id
AND inventory_item_id = v_item_id;
SELECT step_status
FROM gme_batch_steps
WHERE batchstep_id = v_step_id;
SELECT revision_qty_control_code
FROM mtl_system_items_b
WHERE organization_id = v_org_id
AND inventory_item_id = v_item_id;
gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_update_inventory_ind='||p_update_inventory_ind);
gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_insert='||p_insert);
/* FPbug#4543872 removed 'p_insert = fnd_api.G_TRUE OR' from IF condition
because even if we are inserting new material line we need to check for other
conditions like lot control, locator control, subinventory availaility etc. anyway
no.of transactions will be zero for new material line that is being inserted
*/
IF ( p_update_inventory_ind = 'N') THEN
RETURN 0;
gme_transactions_pvt.delete_material_txn
(p_transaction_id => p_trans_id
,p_txns_pair => NULL
,x_return_status => x_return_status);
|| ' from gme_transactions_pvt.delete_material_txn');
|| 'deleted transaction for trans_id= '
|| TO_CHAR (p_trans_id) );
IF p_trans_id = 0 AND p_item_rec.mtl_transactions_enabled_flag = 'Y' THEN -- insert new txn
-- Bug 12563379
l_multiplier := 1;
SELECT count(*)
INTO l_cnt
FROM mtl_material_transactions_temp
WHERE transaction_source_id = p_batch_header_rec.batch_id
AND trx_source_line_id = p_material_detail_rec.material_detail_id
AND transaction_quantity < 0;
SELECT count(*)
INTO l_cnt
FROM mtl_material_transactions_temp
WHERE transaction_source_id = p_batch_header_rec.batch_id
AND trx_source_line_id = p_material_detail_rec.material_detail_id
AND transaction_quantity > 0;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = (l_multiplier * p_material_detail_rec.actual_qty),
primary_quantity = (l_multiplier * l_primary_qty),
secondary_transaction_quantity = l_secondary_qty
WHERE transaction_source_type_id = 5
AND transaction_source_id = p_batch_header_rec.batch_id
AND trx_source_line_id = p_material_detail_rec.material_detail_id
AND transaction_quantity < 0;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = (l_multiplier * p_material_detail_rec.actual_qty),
primary_quantity = (l_multiplier * l_primary_qty),
secondary_transaction_quantity = l_secondary_qty
WHERE transaction_source_type_id = 5
AND transaction_source_id = p_batch_header_rec.batch_id
AND trx_source_line_id = p_material_detail_rec.material_detail_id
AND transaction_quantity > 0;
gme_transactions_pvt.update_material_txn
(p_mmt_rec => l_mmt_rec
,p_mmln_tbl => l_mmln_tbl
,x_return_status => x_return_status);
SELECT *
FROM mtl_system_items_b
WHERE inventory_item_id = v_item_id
AND organization_id = v_org_id;
SELECT concatenated_segments
INTO l_segm
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
gme_common_pvt.log_message ('GME_INV_ITEM_INSERT', 'ITEM_NO', l_field);
SELECT 1
FROM mtl_item_revisions_b
WHERE inventory_item_id = v_item_id
AND organization_id = v_org_id
AND revision = v_revision;
SELECT 1
FROM gem_lookup_values
WHERE lookup_type = 'GMD_BY_PRODUCT_TYPE'
AND lookup_code = v_byprod_type;
SELECT max(line_no)
FROM gme_material_details
WHERE batch_id = v_batch_id
AND line_type = v_line_type;
SELECT disable_date
FROM mtl_units_of_measure
WHERE uom_code = v_uom_code;
SELECT 1
FROM gem_lookup_values
WHERE lookup_type = 'GMD_MATERIAL_RELEASE_TYPE'
AND lookup_code = v_rel_type;
SELECT 1
FROM gem_lookup_values
WHERE lookup_type = 'GMD_ROUNDING_DIRECTION'
AND lookup_code = v_round_dir;
SELECT 1
FROM gem_lookup_values
WHERE lookup_type = 'SCALE_TYPE'
AND lookup_code = v_scale_type;
SELECT steprelease_type,step_status
FROM gme_batch_step_items si, gme_batch_steps s
WHERE si.batchstep_id = s.batchstep_id
AND si.material_detail_id = v_material_detail_id;
SELECT batch_status
FROM gme_batch_header
WHERE batch_id = v_batch_id;
SELECT cost_alloc
FROM gme_material_details
WHERE material_detail_id = v_material_detail_id;
/* in insert */
IF p_material_detail_rec.cost_alloc <> 0 THEN
gme_common_pvt.log_message ('GME_INVALID_COST_ALLOC');
/* in update */
OPEN Cur_get_cost_alloc(l_material_detail_id);
SELECT 1
FROM gem_lookup_values
WHERE lookup_type = 'PHANTOM_TYPE'
AND lookup_code = v_phantom_type;
SELECT locator_type
FROM mtl_secondary_inventories
WHERE organization_id = v_org_id
AND secondary_inventory_name = v_subinventory;
PROCEDURE update_material_line (
p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_material_detail_rec IN gme_material_details%ROWTYPE
,p_stored_material_detail_rec IN gme_material_details%ROWTYPE
,p_batch_step_rec IN gme_batch_steps%ROWTYPE
,p_scale_phantom IN VARCHAR2 := fnd_api.g_false
,p_trans_id IN NUMBER
,x_transacted OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_material_line';
,p_insert => FND_API.g_false
,x_transacted => x_transacted
,x_return_status => x_return_status);
handle the rescheduling batch/step when update yield Type of the Child batch is done.*/
-- need to compare new and old of plan qty / wip plan qty for
-- 1. calculating factor to scale phantom batch if p_scale_phantom
-- is true
-- 2. if they are different, need to update any move order lines
-- with new qty
-- batch_status is used to decide whether to use plan or wip_plan
l_factor := 1;
l_mtl_dtl_rec.last_update_date := gme_common_pvt.g_timestamp;
--sunitha ch. bug 5566769 update the revision field of the phantom batch
l_ph_mtl_dtl_rec.revision := l_mtl_dtl_rec.revision;
/* 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. */
l_ph_batch_step_rec.batch_id:=l_db_mtl_dtl_rec.phantom_id;
SELECT batchstep_id INTO l_ph_batch_step_rec.batchstep_id
FROM gme_batch_step_items
WHERE batch_id = l_mtl_dtl_rec.phantom_id
AND material_detail_id = l_ph_mtl_dtl_rec.material_detail_id;
/*Sunitha Ch. Bug#5391396 rescheduling batch/step when update yield Type
of the Child batch is done */
IF ( l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
gme_common_pvt.is_material_assoc_to_step
(l_ph_mtl_dtl_rec.material_detail_id )) THEN
SELECT plan_start_date
INTO l_plan_cmplt_date
FROM gme_batch_steps
WHERE batch_id = l_ph_mtl_dtl_rec.batch_id
AND batchstep_id =
(SELECT batchstep_id
FROM gme_batch_step_items
WHERE batch_id = l_ph_mtl_dtl_rec.batch_id
AND material_detail_id =
l_ph_mtl_dtl_rec.material_detail_id );
SELECT plan_start_date
INTO l_plan_cmplt_date
FROM gme_batch_header
WHERE batch_id = l_ph_mtl_dtl_rec.batch_id;
/* 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. */
l_batch_step_rec:=p_batch_step_rec;
/* Sunitha REWORK Bug 5353941. We do not need to call fetch_row, as this will override the updated values of material_requirement_date, release_type, subinventory and locator that have been set above. */
SELECT last_update_date INTO l_ph_mtl_dtl_rec.last_update_date
FROM gme_material_details
WHERE batch_id = l_ph_mtl_dtl_rec.batch_id
AND material_detail_id = l_ph_mtl_dtl_rec.material_detail_id;
IF NOT gme_material_details_dbl.update_row (l_ph_mtl_dtl_rec) THEN
l_proc := 'gme_material_details_dbl.update_row';
gme_move_orders_pvt.update_move_order_lines
(p_batch_id => l_mtl_dtl_rec.batch_id
,p_material_detail_id => l_mtl_dtl_rec.material_detail_id
,p_new_qty => l_qty
,p_new_date => l_mtl_dtl_rec.material_requirement_date
,p_invis_move_line_id => NULL
,x_return_status => x_return_status);
l_proc := 'gme_move_orders_pvt.update_move_order_lines';
gme_move_orders_pvt.update_move_order_lines
(p_batch_id => l_mtl_dtl_rec.batch_id
,p_material_detail_id => l_mtl_dtl_rec.material_detail_id
,p_new_qty => l_qty
,p_new_date => l_mtl_dtl_rec.material_requirement_date
,p_invis_move_line_id => NULL
,x_return_status => x_return_status);
l_proc := 'gme_move_orders_pvt.update_move_order_lines';
gme_move_orders_pvt.delete_move_order_lines
(p_organization_id => l_mtl_dtl_rec.organization_id
,p_batch_id => l_mtl_dtl_rec.batch_id
,p_material_detail_id => l_mtl_dtl_rec.material_detail_id
,p_invis_move_line_id => NULL
,x_return_status => x_return_status);
l_proc := 'gme_move_orders_pvt.delete_move_order_lines';
gme_move_orders_pvt.delete_move_order_lines
(p_organization_id => p_batch_header_rec.organization_id
,p_batch_id => p_batch_header_rec.batch_id
,p_material_detail_id => l_mtl_dtl_rec.material_detail_id
,p_invis_move_line_id => l_mtl_dtl_rec.move_order_line_id
,x_return_status => x_return_status);
l_proc := 'gme_move_orders_pvt.delete_move_order_lines';
SELECT last_update_date INTO l_mtl_dtl_rec.last_update_date
FROM gme_material_details
WHERE batch_id = l_mtl_dtl_rec.batch_id
AND material_detail_id = l_mtl_dtl_rec.material_detail_id;
IF NOT gme_material_details_dbl.update_row (l_mtl_dtl_rec) THEN
l_proc := 'gme_material_details_dbl.update_row';
gme_common_pvt.get_who(x_user_ident => x_material_detail_rec.last_updated_by,
x_login_id => x_material_detail_rec.last_update_login,
x_timestamp => x_material_detail_rec.last_update_date,
x_return_status => x_return_status);
|| ' after gme_material_details_dbl.update_row');
|| ' successfully updated material_detail_id= '
|| TO_CHAR (l_mtl_dtl_rec.material_detail_id) );
gme_update_step_qty_pvt.update_step_qty
(p_batch_step_rec => p_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);
l_proc := 'gme_update_step_qty_pvt.update_step_qty';
|| ' after gme_update_step_qty_pvt.update_step_qty: successful');
END update_material_line;
CURSOR c_get_delete(v_org_id NUMBER) IS
SELECT delete_material_ind
FROM gme_parameters
WHERE organization_id = v_org_id;
SELECT COUNT (*)
FROM gme_material_details
WHERE batch_id = v_batch_id AND
line_type = v_line_type;
SELECT 1
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = v_rule_id
AND inventory_item_id = (SELECT inventory_item_id
FROM gme_material_details
WHERE material_detail_id = v_det_id);
SELECT 1
FROM sys.DUAL
WHERE EXISTS ( SELECT 1
FROM gme_batch_header h, gmd_recipe_validity_rules r
WHERE h.batch_id = v_batch_id
AND h.recipe_validity_rule_id = r.recipe_validity_rule_id
AND r.item_id = v_item_id
AND h.parentline_id > 0); */
l_delete_ind NUMBER;
OPEN c_get_delete(p_batch_header_rec.organization_id);
FETCH c_get_delete INTO l_delete_ind;
CLOSE c_get_delete;
l_delete_ind := NVL(l_delete_ind, 1);
(l_delete_ind = 2 AND
p_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip AND
p_batch_header_rec. automatic_step_calculation = 0)
) THEN
gme_common_pvt.log_message ('GME_INV_BATCH_STATUS_OPER');
/* if there is only one ingredient or product we should not the delete */
IF p_material_detail_rec.line_type IN (gme_common_pvt.g_line_type_ing,
gme_common_pvt.g_line_type_prod) THEN
OPEN num_detail_lines(p_batch_header_rec.batch_id,p_material_detail_rec.line_type);
PROCEDURE delete_material_line (
p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_material_detail_rec IN gme_material_details%ROWTYPE
,p_batch_step_rec IN gme_batch_steps%ROWTYPE
,x_transacted OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_message_count NUMBER;
l_api_name CONSTANT VARCHAR2 (30) := 'delete_material_line';
gme_pending_product_lots_pvt.delete_pending_product_lot
(p_material_detail_id => p_material_detail_rec.material_detail_id
,x_return_status => x_return_status);
l_proc := 'gme_pending_product_lots.delete_pending_product_lot';
gme_move_orders_pvt.delete_move_order_lines
(p_organization_id => p_batch_header_rec.organization_id
,p_batch_id => p_batch_header_rec.batch_id
,p_material_detail_id => p_material_detail_rec.material_detail_id
,p_invis_move_line_id => p_material_detail_rec.move_order_line_id
,x_return_status => x_return_status);
l_proc := 'gme_move_orders_pvt.delete_move_order_lines';
gme_reservations_pvt.delete_material_reservations (
p_organization_id => p_batch_header_rec.organization_id
,p_batch_id => p_batch_header_rec.batch_id
,p_material_detail_id => p_material_detail_rec.material_detail_id
,x_return_status => x_return_status);
gme_transactions_pvt.delete_material_txn (
p_transaction_id => l_mmt_tbl(i).transaction_id
,p_txns_pair => NULL
,x_return_status => x_return_status);
l_proc := 'gme_transactions_pvt.delete_material_txn';
gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoking delete_prod_supply_resv' );
gme_supply_res_pvt.delete_prod_supply_resv (
p_matl_dtl_rec => p_material_detail_rec
,x_msg_count => l_message_count
,x_msg_data => l_message_list
,x_return_status => x_return_status);
l_proc := 'gme_reservations_pvt.delete_prod_prod_supply_resv ';
IF NOT gme_material_details_dbl.delete_row (p_material_detail_rec) THEN
l_proc := 'gme_material_details_dbl.delete_row';
UPDATE gme_material_details
SET line_no = line_no - 1
,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 batch_id = p_material_detail_rec.batch_id
AND line_type = p_material_detail_rec.line_type
AND line_no >= p_material_detail_rec.line_no;
DELETE FROM gme_batch_step_items
WHERE material_detail_id =
p_material_detail_rec.material_detail_id
AND batchstep_id = p_batch_step_rec.batchstep_id;
gme_update_step_qty_pvt.update_step_qty
(p_batch_step_rec => p_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);
l_proc := 'gme_update_step_qty_pvt.update_step_qty';
|| ' after gme_update_step_qty_pvt.update_step_qty: successful');
END delete_material_line;