[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM mtl_material_transactions
WHERE transaction_set_id = v_header_id;
PROCEDURE update_material_date (
p_material_detail_id IN NUMBER
,p_material_date IN DATE
,x_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
gme_common_pvt.set_who ;
this procedure may return R, B or M depends on whether reservations deleted
or MO allocations deleted or both
*/
IF x_return_status in ('R','B','M') THEN
x_return_status := fnd_api.g_ret_sts_success;
END update_material_date;
/* Verify that update_inventory is allowed for the Batch */
IF l_batch_header_rec.update_inventory_ind <> 'Y' THEN
gme_common_pvt.log_message('GME_INVENTORY_UPDATE_BLOCKED');
SELECT 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;
SELECT primary_uom_code
FROM mtl_system_items_b
WHERE organization_id = V_org_id
AND inventory_item_id = V_inventory_item_id;
PROCEDURE update_step_quality_status (
p_batchstep_id IN NUMBER
,p_org_id IN NUMBER
,p_quality_status IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_batch_step gme_batch_steps%ROWTYPE;
IF (NOT (gme_batch_steps_dbl.update_row (l_batch_step) ) ) THEN
RAISE expected_err;
,'UPDATE_STEP_QUALITY_STATUS');
END update_step_quality_status;
SELECT *
FROM mtl_reservations
WHERE reservation_id = v_reservation_id;
SELECT *
FROM mtl_system_items_b
WHERE organization_id = v_org_id
AND inventory_item_id = v_inventory_item_id;
/* Bug 5212569 Removed * and selecting only required columns */
CURSOR item_master_cursor (v_item_id NUMBER
,v_org_id NUMBER) IS
SELECT concatenated_segments, mtl_transactions_enabled_flag, process_execution_enabled_flag,
eng_item_flag, primary_uom_code, reservable_type
FROM mtl_system_items_kfv
WHERE inventory_item_id = v_item_id
AND organization_id = v_org_id;
SELECT m.material_detail_id, m.batch_id, h.batch_no, m.material_requirement_date, m.line_no,
m.formulaline_id, m.plan_qty, m.dtl_um, m.scale_multiple, h.formula_id,
m.inventory_item_id, m.move_order_line_id, i.concatenated_segments item_no, h.organization_id, h.batch_type
FROM gme_material_details m, gme_batch_header h, mtl_system_items_kfv i
WHERE m.batch_id = h.batch_id
AND h.organization_id = v_org_id
AND h.batch_status = 1
AND m.material_requirement_date >= v_start_date
AND m.material_requirement_date <= NVL (v_end_date, m.material_requirement_date)
AND m.line_type = -1
AND m.phantom_type = 0 -- Non phantom ingrdients
AND m.inventory_item_id = i.inventory_item_id
AND m.organization_id = i.organization_id
AND (m.inventory_item_id = v_item_id OR v_item_id IS NULL)
AND (h.batch_no >= v_from_batch_no OR v_from_batch_no is null)
AND (h.batch_no <= v_to_batch_no OR v_to_batch_no is null)
AND m.formulaline_id IS NOT NULL --only for the ingredients in the formula substitution happens
ORDER BY m.material_requirement_date, m.batch_id, m.material_detail_id;
SELECT b.*
FROM gme_batch_step_items a, gme_batch_steps b
WHERE a.batchstep_id = b.batchstep_id
AND a.material_detail_id = p_material_detail_id;
SELECT substrb(message_text,1,50)
FROM fnd_new_messages
WHERE application_id = 553
AND message_name = v_msg_name;
IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
-- Delete all reservations for this material line
gme_reservations_pvt.delete_material_reservations (
p_organization_id => rec.organization_id
,p_batch_id => rec.batch_id
,p_material_detail_id => rec.material_detail_id
,x_return_status => l_return_status);
gme_move_orders_pvt.delete_move_order_lines
(p_organization_id => rec.organization_id
,p_batch_id => rec.batch_id
,p_material_detail_id => rec.material_detail_id
,p_invis_move_line_id => rec.move_order_line_id
,x_return_status => l_return_status);
END IF; -- l_batch_header_rec.update_inventory_ind = 'Y'
IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
IF l_new_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
/* Insert Invisible Move Order Line */
l_material_details_tbl(1) := l_material_detail_rec;
END IF; -- IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
l_return := gme_material_details_dbl.update_row (l_material_detail_rec);
/* FPBug#4351032 update original primary qty field as this field dont get updated
using gme_material_details_dbl.update_row procedure */
UPDATE gme_material_details
SET original_primary_qty = l_material_detail_rec.original_primary_qty
WHERE material_detail_id = l_material_detail_rec.material_detail_id;
l_material_detail_rec.last_update_date := gme_common_pvt.get_timestamp;
IF l_batch_header_rec.update_inventory_ind = 'Y' AND
l_new_item_rec.mtl_transactions_enabled_flag = 'Y' AND
l_new_item_rec.reservable_type = 1
THEN
l_return_status := NULL;
END IF; -- update inventory ind
IF l_batch_header_rec.update_inventory_ind = 'Y' AND NOT l_trans_loaded THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ('Entered into loading txns');
END IF; -- update inventory ind
gme_update_step_qty_pvt.update_step_qty (p_batch_step_rec => l_batchstep_rec,
x_message_count => l_message_count,
x_message_list => l_message_list,
x_return_status => x_return_status,
x_batch_step_rec => x_batchstep_rec
);
gme_debug.put_line ('After update step qty, return status is: ' || x_return_status);
IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
IF l_batch_header_rec.automatic_step_calculation = 1 AND l_trans_loaded THEN
gme_resource_engine_pvt.consolidate_batch_resources (l_batch_header_rec.batch_id,
x_return_status
);
END IF; /* l_batch_header_rec.update_inventory_ind = 'Y' */
SELECT gm.dtl_um
FROM gmd_recipe_validity_rules vr, gme_material_details gm, gme_batch_header bh
WHERE bh.recipe_validity_rule_id = vr.recipe_validity_rule_id
AND bh.batch_id = gm.batch_id
AND vr.inventory_item_id = gm.inventory_item_id
AND gm.line_type = 1 /*FPBug# 4684029 rework */
AND bh.batch_id = v_batch_id
AND rownum = 1 ;
SELECT inventory_item_id, plan_qty, wip_plan_qty, actual_qty, dtl_um
FROM gme_material_details
WHERE batch_id = v_batch_id
AND line_type = v_line_type;
SELECT plan_start_date,plan_cmplt_date
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT material_requirement_date
FROM gme_material_details
WHERE material_detail_id = p_material_detail_id;
SELECT mr.*
FROM mtl_reservations mr
WHERE mr.organization_id = p_organization_id
AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
AND mr.demand_source_header_id = p_batch_id
AND mr.demand_source_line_id = p_mat_det_id
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id
AND organization_id = p_organization_id)
ORDER BY mr.requirement_date, mr.reservation_id;
SELECT *
FROM gme_pending_product_lots
WHERE material_detail_id = p_mat_det_id
ORDER BY sequence asc, lot_number asc;
SELECT *
FROM mtl_material_transactions mmt
WHERE trx_source_line_id = p_mat_det_id
AND transaction_source_id = p_batch_id
AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
AND NOT EXISTS (SELECT /*+ no_unnest */
transaction_id1
FROM gme_transaction_pairs
WHERE transaction_id1 = mmt.transaction_id
AND pair_type = gme_common_pvt.g_pairs_reversal_type)
ORDER BY mmt.transaction_id;
SELECT *
FROM mtl_transaction_lot_numbers
WHERE transaction_id = p_transaction_id;
PROCEDURE update_material_txn(p_transaction_id IN NUMBER,
p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_material_txn';
SELECT organization_id, transaction_source_id, trx_source_line_id, transaction_type_id
FROM mtl_material_transactions
WHERE transaction_id = v_transaction_id;
update_txn_fail EXCEPTION;
update_txn_mismatch EXCEPTION;
RAISE update_txn_mismatch;
gme_transactions_pvt.update_material_txn(p_transaction_id => p_transaction_id,
p_mmti_rec => p_mmti_rec,
p_mmli_tbl => p_mmli_tbl,
x_return_status => l_return_status);
RAISE update_txn_fail;
WHEN update_txn_mismatch THEN
gme_common_pvt.log_message('GME_TXN_UPDATE_MISMATCH');
WHEN update_txn_fail OR validate_txn_fail THEN
x_return_status := l_return_status;
END update_material_txn;
PROCEDURE delete_material_txn(p_organization_id IN NUMBER,
p_transaction_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_material_txn';
delete_txn_fail EXCEPTION;
gme_transactions_pvt.delete_material_txn(p_transaction_id => p_transaction_id,
x_return_status => l_return_status);
RAISE delete_txn_fail;
WHEN delete_txn_fail OR get_txn_fail OR const_txn_fail OR validate_txn_fail THEN
x_return_status := l_return_status;
END delete_material_txn;
rsrc_update_err EXCEPTION;
Gme_Update_Step_Qty_Pvt.reduce_pending_usage(p_batch_step_resources_rec => l_step_resources
,x_return_status => l_return_status);
IF NOT Gme_Batch_Step_Resources_Dbl.update_row(p_batch_step_resources => l_step_resources) THEN
RAISE rsrc_update_err;
WHEN setup_failed OR uom_conversion_err OR missing_profile_option OR rsrc_fetch_err OR rsrc_update_err THEN
x_return_status := FND_API.G_RET_STS_ERROR;
PROCEDURE update_resource_txn(p_rsrc_txn_gtmp_rec IN gme_resource_txns_gtmp%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_resource_txn';
update_txn_fail EXCEPTION;
gme_resource_engine_pvt.update_resource_trans(p_tran_rec => p_rsrc_txn_gtmp_rec
,x_return_status => l_return_status);
RAISE update_txn_fail;
gme_update_step_qty_pvt.reduce_pending_usage(p_batch_step_resources_rec => l_step_resources,
x_return_status => l_return_status);
IF NOT gme_batch_step_resources_dbl.update_row(p_batch_step_resources => l_step_resources) THEN
RAISE upd_rsrc_err;
WHEN update_txn_fail OR validate_txn_fail OR reduce_pend_usage_err THEN
x_return_status := l_return_status;
END update_resource_txn;
PROCEDURE delete_resource_txn(p_rsrc_txn_gtmp_rec IN gme_resource_txns_gtmp%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_resource_txn';
SELECT s.step_status, h.automatic_step_calculation
FROM gme_batch_steps s, gme_batch_step_activities a, gme_batch_step_resources r, gme_batch_header h
WHERE r.batchstep_resource_id = v_line_id
AND a.batchstep_activity_id = r.batchstep_activity_id
AND s.batchstep_id = a.batchstep_id
AND h.batch_id = s.batch_id;
delete_txn_fail EXCEPTION;
gme_resource_engine_pvt.delete_resource_trans(p_tran_rec => l_rsrc_txn_gtmp_rec
,x_return_status => l_return_status);
RAISE delete_txn_fail;
gme_update_step_qty_pvt.reduce_pending_usage(p_batch_step_resources_rec => l_step_resources,
x_return_status => l_return_status);
IF NOT gme_batch_step_resources_dbl.update_row(p_batch_step_resources => l_step_resources) THEN
RAISE upd_rsrc_err;
WHEN delete_txn_fail OR get_usage_fail OR reduce_pend_usage_err THEN
x_return_status := l_return_status;
END delete_resource_txn;