[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE lock_formula_hdr (p_formula_id IN NUMBER, p_last_update_date IN DATE, X_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_lock_header IS
SELECT last_update_date
FROM fm_form_mst
WHERE formula_id = P_formula_id
FOR UPDATE OF LAST_UPDATE_DATE NOWAIT;
X_last_update_date DATE;
FETCH Cur_lock_header INTO X_last_update_date;
IF X_last_update_date <> P_last_update_date THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_RECORD_CHANGED');
fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_Formula_Hdr');
PROCEDURE lock_formula_dtl (P_formulaline_id IN NUMBER, P_last_update_date IN DATE,
X_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_lock_details IS
SELECT last_update_date
FROM fm_matl_dtl
WHERE formulaline_id = P_formulaline_id
FOR UPDATE OF LAST_UPDATE_DATE NOWAIT;
X_last_update_date DATE;
FETCH Cur_lock_details INTO X_last_update_date;
IF X_last_update_date <> P_last_update_date THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_RECORD_CHANGED');
fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_Formula_Dtl');
SELECT last_update_date
FROM gmd_material_header_gtmp
WHERE formula_id = P_formula_id;
SELECT formulaline_id, last_update_date
FROM gmd_material_details_gtmp
WHERE line_type <> 3
ORDER BY line_type, line_no;
l_last_update_date DATE;
FETCH Cur_get_hdr INTO l_last_update_date;
,P_last_update_date => l_last_update_date
,X_return_status => l_return_status);
,P_last_update_date => l_rec.last_update_date
,X_return_status => l_return_status);
fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_formula_Record');
PROCEDURE lock_batch_hdr (P_batch_id IN NUMBER, P_last_update_date IN DATE, X_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_lock_header IS
SELECT last_update_date
FROM gme_batch_header
WHERE batch_id = P_batch_id
FOR UPDATE OF LAST_UPDATE_DATE NOWAIT;
X_last_update_date DATE;
FETCH Cur_lock_header INTO X_last_update_date;
IF X_last_update_date <> P_last_update_date THEN
FND_MESSAGE.SET_NAME('GME', 'GME_RECORD_CHANGED');
fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_Batch_Hdr');
PROCEDURE lock_batch_dtl (P_material_detail_id IN NUMBER, P_last_update_date IN DATE,
X_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_lock_details IS
SELECT last_update_date
FROM gme_material_details
WHERE material_detail_id = P_material_detail_id
FOR UPDATE OF LAST_UPDATE_DATE NOWAIT;
X_last_update_date DATE;
FETCH Cur_lock_details INTO X_last_update_date;
IF X_last_update_date <> P_last_update_date THEN
FND_MESSAGE.SET_NAME('GME', 'GME_RECORD_CHANGED');
fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_Batch_Dtl');
SELECT last_update_date
FROM gmd_material_header_gtmp
WHERE batch_id = P_batch_id;
SELECT material_detail_id, last_update_date
FROM gmd_material_details_gtmp
WHERE line_type <> 3
ORDER BY line_type, line_no;
l_last_update_date DATE;
FETCH Cur_get_hdr INTO l_last_update_date;
,P_last_update_date => l_last_update_date
,X_return_status => l_return_status);
,P_last_update_date => l_rec.last_update_date
,X_return_status => l_return_status);
fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_Batch_Record');
# update_batch
# SYNOPSIS
# proc update_batch
# DESCRIPTION
# This procedure is used to update the batch.
# HISTORY
# 22-AUG-06 Kapil M Bug# 3927768
# Changed the IF condition to update lot for the items.
# 19-SEP-06 Kapil M Bug# 3927768
# Modified in deletion, updation and creation go product_pending_lots
###############################################################*/
PROCEDURE update_batch (P_batch_id IN NUMBER, X_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_batch IS
SELECT *
FROM gme_batch_header
WHERE batch_id = P_batch_id;
SELECT material_detail_id
FROM gme_material_details e
WHERE batch_id = P_batch_id
AND line_type <> 1
AND NOT EXISTS (SELECT 1
FROM gmd_material_details_gtmp g
WHERE line_type <> 3
AND g.material_detail_id = e.material_detail_id);
SELECT *
FROM gmd_material_details_gtmp
WHERE line_type <> 3
ORDER BY line_type, line_no;
SELECT 1
FROM gme_material_details
WHERE batch_id = P_batch_id
AND material_detail_id = V_material_detail_id;
SELECT *
FROM gmd_material_details_gtmp
WHERE line_type = 3
AND material_detail_id = V_material_detail_id;
SELECT quantity
FROM gme_pending_product_lots
WHERE batch_id = P_batch_id
AND pending_product_lot_id = p_lot_id;
SELECT material_detail_id, pending_product_lot_id
FROM gme_pending_product_lots e
WHERE batch_id = P_batch_id
AND material_detail_id = V_material_detail_id
AND NOT EXISTS (SELECT 1
FROM gmd_material_details_gtmp g
WHERE parent_line_id = V_material_detail_id
AND g.trans_id = e.pending_product_lot_id
AND line_type = 3);
SELECT pending_product_lot_id
FROM gme_pending_product_lots
WHERE batch_id = P_batch_id
AND material_detail_id = V_material_detail_id;
SELECT material_detail_id,text_code,orginal_text_code
FROM gmd_material_details_gtmp
WHERE text_code <> NVL(orginal_text_code,0);
update_alloc_err EXCEPTION;
insert_line_err EXCEPTION;
update_line_err EXCEPTION;
delete_line_err EXCEPTION;
SAVEPOINT update_batch;
/* First let us delete all the lines which existed in the batch */
/* but was deleted from the spreadsheet */
FOR l_del_matl_rec IN Cur_get_del_material LOOP
l_material_detail.material_detail_id := l_del_matl_rec.material_detail_id;
gme_material_detail_pvt.delete_material_line (p_batch_header_rec => l_batch_row
,p_material_detail_rec => l_material_detail
,p_batch_step_rec => l_batch_step
,x_transacted => l_transacted
,x_return_status => l_return_status);
RAISE delete_line_err;
/* This implies that this is a new line we need to first insert the material line */
l_material_detail.batch_id := p_batch_id;
gmd_debug.put_line(' Inserting line for batch:'||l_material_detail.batch_id||' Item:'||l_material_detail.inventory_item_id);
gme_material_detail_pvt.insert_material_line (p_batch_header_rec => l_batch_row
,p_material_detail_rec => l_material_detail
,p_batch_step_rec => l_batch_step
,p_trans_id => NULL
,x_transacted => l_transacted
,x_return_status => l_return_status
,x_material_detail_rec => l_material_out);
gmd_debug.put_line(' Insert material line error: Item:'||l_material_detail.inventory_item_id);
RAISE insert_line_err;
UPDATE gmd_material_details_gtmp
SET material_detail_id = l_matl_rec.material_detail_id,
parent_line_id = l_matl_rec.material_detail_id
WHERE parent_line_id = l_matl_rec.parent_line_id;
gme_material_detail_pvt.update_material_line (p_batch_header_rec => l_batch_row
,p_material_detail_rec => X_material
,p_stored_material_detail_rec => NULL
,p_batch_step_rec => l_batch_step
,p_scale_phantom => fnd_api.g_false
,p_trans_id => NULL
,x_transacted => l_transacted
,x_return_status => l_return_status
,x_material_detail_rec => l_material_out);
gmd_debug.put_line(' Insert material line error: Material id:'||l_material_detail.material_detail_id);
RAISE update_line_err;
IF (l_batch_row.update_inventory_ind = 'Y') THEN
update_allocation (P_plant_Id => l_batch_row.organization_id
,P_batch_id => l_batch_row.batch_id
,P_material_detail_id => l_matl_rec.material_detail_id
,P_line_type => l_matl_rec.line_type
,X_return_status => l_return_status);
RAISE update_alloc_err;
gme_api_pub.delete_pending_product_lot (p_api_version => 2.0,
x_message_count => l_message_count,
x_message_list => l_message_list,
x_return_status => l_return_status,
p_batch_header_rec => l_batch_rec,
p_org_code => NULL,
p_material_detail_rec => l_material_rec,
p_pending_product_lots_rec => l_pending_in_rec);
gmd_debug.put_line(' Insert pending lot error: Material id:'||l_matl_rec.material_detail_id ||
'lot_number: '||l_pending_in_rec.lot_number);
RAISE update_line_err;
l_pending_in_rec.last_updated_by := l_labrec.last_updated_by;
l_pending_in_rec.last_update_date := l_labrec.last_update_date;
/* Let us check the lab batch lot is already existing if yes update the line */
-- Bug# 3927768 Kapil M
-- Records fetched based on the pending_lot_id
OPEN Cur_get_lab_material(l_labrec.trans_id);
gme_api_pub.update_pending_product_lot (p_api_version => 2.0,
x_message_count => l_message_count,
x_message_list => l_message_list,
x_return_status => l_return_status,
p_batch_header_rec => l_batch_rec,
p_org_code => NULL,
p_material_detail_rec => l_material_rec,
p_pending_product_lots_rec => l_pending_in_rec,
x_pending_product_lots_rec => l_pending_out_rec);
gmd_debug.put_line(' Insert pending lot error: Material id:'||l_matl_rec.material_detail_id ||
'lot_number: '||l_pending_in_rec.lot_number);
RAISE update_line_err;
/* Let us check the lab batch lot is already existing if not insert the new line */
l_pending_in_rec.pending_product_lot_id := NULL;
gmd_debug.put_line(' Insert pending lot error: Material id:'||l_matl_rec.material_detail_id ||
'lot_number: '||l_pending_in_rec.lot_number);
RAISE update_line_err;
UPDATE gme_material_details
SET text_code = l_text_code
WHERE material_detail_id = l_rec.material_detail_id;
GMA_EDITTEXT_PKG.Delete_Text(l_rec.orginal_text_code,'GME_TEXT_TABLE_TL');
update gmd_material_details_gtmp a
set last_update_date = (select last_update_date
FROM gme_material_details
WHERE material_detail_id = a.material_detail_id);
ROLLBACK TO SAVEPOINT update_batch;
WHEN error_load_batch OR update_alloc_err OR insert_line_err OR update_line_err
OR delete_line_err OR lock_batch_err THEN
ROLLBACK TO SAVEPOINT update_batch;
ROLLBACK TO SAVEPOINT update_batch;
fnd_msg_pub.add_exc_msg (gmd_spreadsheet_update.g_pkg_name, 'Update_Batch');
END update_batch;
# update_allocation
# SYNOPSIS
# proc update_allocation
# DESCRIPTION
# This procedure is used to update the allocations for the batch.
###############################################################*/
PROCEDURE update_allocation (P_plant_id IN NUMBER,
P_batch_id IN NUMBER,
P_material_detail_id IN NUMBER,
P_line_type IN NUMBER,
X_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_del_lines (V_material_detail_id NUMBER) IS
SELECT *
FROM mtl_reservations e
WHERE demand_source_header_id = P_batch_id
AND demand_source_line_id = V_material_detail_id
AND demand_source_type_id = gme_common_pvt.g_txn_source_type
AND NOT EXISTS (SELECT 1
FROM gmd_material_details_gtmp g
WHERE parent_line_id = V_material_detail_id
AND line_type = 3
AND g.reservation_id = e.reservation_id);
SELECT *
FROM gmd_material_details_gtmp
WHERE parent_line_id = V_material_detail_id
AND line_type = 3
AND transaction_id IS NULL
ORDER BY line_no desc;
SELECT revision,inventory_item_id
FROM gmd_material_details_gtmp
WHERE material_detail_id = V_material_detail_id;
SELECT revision_qty_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = V_item_id
AND organization_id = P_plant_id;
SELECT revision
FROM mtl_item_revisions
WHERE inventory_item_id = V_item_id
AND organization_id = P_plant_id
ORDER BY CREATION_DATE DESC;
SELECT reservation_quantity
FROM mtl_reservations
WHERE reservation_id = V_reservation_id;
SELECT material_requirement_date
FROM gme_material_details
WHERE material_detail_id = P_material_detail_id;
SELECT qty,transaction_id,lot_number
FROM gmd_material_details_gtmp
WHERE material_detail_id = P_material_detail_id
AND lot_number IS NOT NULL;
SELECT transaction_quantity
FROM mtl_transaction_lot_numbers
WHERE transaction_id = V_transaction_id
AND lot_number = V_lot_number;
update_alloc_err EXCEPTION;
insert_alloc_err EXCEPTION;
delete_alloc_err EXCEPTION;
trans_update EXCEPTION;
RAISE trans_update;
/* First let us delete all the reservations which existed in the batch */
/* but was deleted from the spreadsheet. */
IF P_line_type <> 1 THEN
FOR l_del_rec IN Cur_get_del_lines (P_material_detail_id) LOOP
gmd_debug.put_line(' Material:'||P_material_detail_id||' trans:'||l_del_rec.reservation_id||' reserv qty:'||l_del_rec.reservation_quantity||' Lot:'||l_del_rec.lot_number);
gme_reservations_pvt.delete_reservation(p_reservation_id => l_del_rec.reservation_id
,x_return_status => l_return_status);
gmd_debug.put_line('Delete allocation error:'||l_del_rec.reservation_id);
RAISE delete_alloc_err;
/* Then let us update/insert the reservation lines associated with the material line */
FOR l_rec IN Cur_get_lines (P_material_detail_id) LOOP
OPEN Cur_req_date;
/* Let us update the existing reservations */
IF l_rec.reservation_id IS NOT NULL THEN
OPEN Cur_get_qty (l_rec.reservation_id);
gme_reservations_pvt.update_reservation (p_reservation_id => l_rec.reservation_id
,p_revision => l_revision
,p_subinventory => l_rec.subinventory_code
,p_locator_id => l_rec.locator_id
,p_lot_number => l_rec.lot_number
,p_new_qty => l_rec.qty
,p_new_sec_qty => ABS(l_rec.secondary_qty)
,p_new_uom => l_rec.detail_uom
,p_new_date => SYSDATE
,x_return_status => l_return_status);
gmd_debug.put_line('Update allocation error:'||l_rec.reservation_id);
RAISE update_alloc_err;
gmd_debug.put_line(' Insert alloc fail'||l_return_status);
RAISE insert_alloc_err;
WHEN update_alloc_err OR insert_alloc_err OR delete_alloc_err THEN
X_return_status := l_return_status;
WHEN trans_update THEN
gmd_api_grp.log_message('GMD_QTY_NO_UPDATE');
fnd_msg_pub.add_exc_msg (gmd_spreadsheet_update.g_pkg_name, 'Update_Allocation');
END update_allocation;