[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select distinct whse_code
From gml_batch_so_reservations
Where batch_line_id = p_batch_line_id;
Select material_detail_id
, item_id
From gme_material_details
where batch_id = p_batch_id
and line_type <> -1 -- not ingredient
;
Select *
From gml_batch_so_reservations
Where batch_line_id = p_batch_line_id
and whse_code = p_whse_code
and delete_mark = 0
and reserved_qty <> 0
Order by scheduled_ship_date
, shipment_priority
;
Select abs(sum(trans_qty)), abs(sum(trans_qty2))
From ic_tran_pnd
Where line_id = p_batch_line_id
and whse_code = p_whse_code
and doc_type = 'PROD'
and delete_mark = 0
and completed_ind = 0
;
Select count(1)
From gml_batch_so_reservations
Where batch_line_id = p_batch_line_id
and whse_code = p_whse_code
and delete_mark = 0
and reserved_qty <> 0
;
Select material_detail_id
From gme_material_details
Where batch_id = p_batch_id
and item_id = p_item_id
and line_type <> -1
;
Select plan_cmplt_date
From gme_batch_header
where batch_id = p_batch_id;
GML_BATCH_OM_UTIL.insert_reservation
(
P_Gme_om_reservation_rec => l_reservation_rec
, X_return_status => x_return_status
, X_msg_cont => x_msg_cont
, X_msg_data => x_msg_data
);
/* update the fpo reservation records */
Update gml_batch_so_reservations
Set reserved_qty = reserved_qty - l_reserved_qty
, reserved_qty2 = reserved_qty2 - l_reserved_qty2
Where batch_res_id = each_rec.batch_res_id;
Select res.scheduled_ship_date
, res.shipment_priority
, res.batch_res_id
, res.reserved_qty
, res.reserved_qty2
, res.batch_line_id
, res.so_line_id
, res.delivery_detail_id
, res.batch_id
From gml_batch_so_reservations res
, wsh_delivery_details wdd
Where res.batch_line_id = p_batch_line_id
and res.delete_mark = 0
and res.reserved_qty <> 0
and res.whse_code = p_whse_code
and res.delivery_detail_id = wdd.delivery_detail_id
and wdd.released_status in ('B', 'R', 'S') -- dont bother looking at the ones staged
Union
Select res.scheduled_ship_date
, res.shipment_priority
, res.batch_res_id
, res.reserved_qty
, res.reserved_qty2
, res.batch_line_id
, res.so_line_id
, res.delivery_detail_id
, res.batch_id
From gml_batch_so_reservations res
, oe_order_lines_all ol
Where res.batch_line_id = p_batch_line_id
and res.delete_mark = 0
and res.reserved_qty <> 0
and res.whse_code = p_whse_code
and res.so_line_id = ol.line_id
and ol.booked_flag = 'N'
Order by 1
, 2
;
Select status_ctl
From ic_item_mst
Where item_id = p_item_id;
Select abs(sum(trans_qty)), abs(sum(trans_qty2))
From ic_Tran_pnd
Where line_id = p_so_line_id
and doc_type = 'OMSO'
and delete_mark = 0
and (lot_id <> 0 or location <> GMI_Reservation_Util.G_DEFAULT_LOCT)
and completed_ind = 0
;
Select count(1)
Into l_res_count
From gml_batch_so_reservations
Where batch_line_id = p_batch_line_rec.batch_line_id
and delete_mark = 0
and reserved_qty <> 0
and whse_code = l_gme_trans_row.whse_code -- beta testing
;
Select lot_status
Into l_lot_status
From ic_loct_inv
Where lot_id = l_gme_trans_row.lot_id
AND location = l_gme_trans_row.location
AND whse_code = l_gme_trans_row.whse_code
AND item_id = l_gme_trans_row.item_id
And delete_mark = 0
;
Select order_proc_ind
Into l_orderable
From ic_lots_sts
Where lot_status = l_lot_status;
GML_GME_API_PVT.g_not_to_delete := 1;
GMI_RESERVATION_UTIL.println('Create alloc: global g_not_to_delete '||GML_GME_API_PVT.g_not_to_delete);
/* IF regenerate, set the current history to delete */
Update gml_batch_so_alloc_history
set delete_mark = 1
Where batch_res_id = res_rec.batch_res_id
And batch_trans_id = l_gme_trans_row.trans_id
And failure_reason is not null;
/* insert history record */
GML_BATCH_OM_UTIL.insert_alloc_history
(
P_alloc_history_rec => l_history_rec
, X_return_status => x_return_status
, X_msg_cont => x_msg_cont
, X_msg_data => x_msg_data
);
/* update the reservation record */
Update gml_batch_so_reservations
Set allocated_ind = 1
, reserved_qty = reserved_qty - abs(l_tran_row.trans_qty)
, reserved_qty2 = reserved_qty2 - abs(l_tran_row.trans_qty2)
Where batch_res_id = res_rec.batch_res_id;
/* delete the reservation record if over allocated */
IF l_over_alloc = 1 THEN
Update gml_batch_so_reservations
Set delete_mark = 1
Where batch_res_id = res_rec.batch_res_id;
Select ic.trans_id
From ic_tran_pnd ic
, gml_batch_so_alloc_history his
Where his.batch_trans_id = p_batch_trans_id
and his.trans_id = ic.trans_id
and ic.line_id = his.line_id
and ic.staged_ind = 0
and ic.delete_mark = 0
and ic.doc_type = 'OMSO'
;
/* call gmi api to delete this trans */
l_trans_id := alloc_rec.trans_id;
GMI_TRANS_ENGINE_PUB.delete_pending_transaction
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_NONE,
p_tran_rec => l_tran_rec,
x_tran_row => l_tran_row,
x_return_status => x_return_status,
x_msg_count => x_msg_cont,
x_msg_data => x_msg_data
);
GMI_reservation_Util.PrintLn('Delete OMSO trans for Batch trans');
Select ic.trans_id
From ic_tran_pnd ic
, gml_batch_so_alloc_history his
Where his.batch_id = p_batch_id
and his.trans_id = ic.trans_id
and ic.line_id = his.line_id
and ic.staged_ind = 0
and ic.completed_ind = 0
and ic.delete_mark = 0
and ic.doc_type = 'OMSO'
;
GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_NONE
,p_tran_rec => l_tran_rec
,x_tran_row => l_tran_row
,x_return_status => x_return_status
,x_msg_count => x_msg_cont
,x_msg_data => x_msg_data);
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d After DELETE_PENDING_TRANSACTION.');
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Delete_Transaction().');
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION');
FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
/* call gmi api to delete this trans */
end loop;
Select ic.trans_id
From ic_tran_pnd ic
, gml_batch_so_alloc_history his
Where his.batch_line_id = p_batch_line_id
and his.trans_id = ic.trans_id
and ic.line_id = his.line_id
and ic.staged_ind = 0
and ic.delete_mark = 0
and ic.doc_type = 'OMSO'
;
/* call gmi api to delete this trans */
end loop;
Update gml_batch_so_reservations
Set delete_mark = 1
Where batch_line_id = p_batch_line_id
and delete_mark = 0;
Update gml_batch_so_reservations
Set delete_mark = 1
Where batch_line_id = p_batch_line_id
and whse_code = p_whse_code
and delete_mark = 0;
Update gml_batch_so_reservations
Set delete_mark = 1
Where so_line_id = p_so_line_id
and delete_mark = 0;
Update gml_batch_so_reservations
Set delete_mark = 1
Where batch_id = p_batch_id
and delete_mark = 0;
l_last_updated_by NUMBER;
SELECT Distinct so_line_id
FROM gml_batch_so_reservations
WHERE batch_id = p_batch_id
and delete_mark = 0
and reserved_qty <> 0;
SELECT batch_type
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT Distinct so_line_id
FROM gml_batch_so_reservations
WHERE batch_line_id = p_batch_line_id
and delete_mark = 0
and reserved_qty <> 0;
SELECT gl.batch_id,gh.batch_type
FROM gme_material_details gl,
gme_batch_header gh
WHERE gl.material_detail_id = p_batch_line_id
and gl.batch_id = gh.batch_id;
Select distinct ictran.line_id
From ic_tran_pnd ictran
, gml_batch_so_alloc_history his
Where his.batch_trans_id = p_batch_trans_id
and his.trans_id = ictran.trans_id
and ictran.doc_type = 'OMSO'
and ictran.delete_mark = 0
and ictran.staged_ind = 0
and ictran.completed_ind = 0
;
SELECT batch_id
,batch_type
,batch_line_id
FROM gml_batch_so_alloc_history
WHERE batch_trans_id = p_batch_trans_id;
SELECT last_updated_by, created_by,header_id
FROM oe_order_lines_all
WHERE line_id = p_so_line_id;
Select count(*)
From ic_tran_pnd ic
, gml_batch_so_alloc_history his
Where his.batch_trans_id = p_batch_trans_id
and his.trans_id = ic.trans_id
and ic.line_id = his.line_id
and ic.staged_ind = 0
and ic.delete_mark = 0
and ic.doc_type = 'OMSO'
;
Select count(*)
From ic_tran_pnd ic
, gml_batch_so_alloc_history his
Where his.batch_trans_id = p_batch_trans_id
and his.trans_id = ic.trans_id
and ic.line_id = his.line_id
and ic.staged_ind = 1
and ic.delete_mark = 0
and ic.doc_type = 'OMSO'
;
Select distinct so_line_id
From gml_batch_so_reservations
Where created_by = p_user_id
and batch_id = p_batch_id;
Select distinct so_line_id
From gml_batch_so_reservations
Where created_by = p_user_id
and batch_id = p_batch_id
and batch_line_id = p_batch_line_id;
FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_so_header_id;
GMI_RESERVATION_UTIL.PrintLn('Notify CSR : l_last_updated_by is '||l_last_updated_by);
, p_approver => l_last_updated_by
, p_so_header_id=> l_so_header_id
, p_so_line_id => l_so_line_id
, p_batch_id => l_batch_id
, p_batch_line_id => NULL
, p_whse_code => l_whse_code
, p_action_code => l_action_code );
IF(l_last_updated_by <> l_created_by) THEN
GMI_RESERVATION_UTIL.PrintLn('Notify CSR : l_created_by is '||l_created_by);
for mul_line in check_mul_line_id1 (l_last_updated_by, l_batch_id ) Loop
l_old_header_id := l_so_header_id ;
FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_new_header_id ;
, p_approver => l_last_updated_by
, p_so_header_id=> l_so_header_id
, p_so_line_id => l_so_line_id
, p_batch_id => l_batch_id
, p_batch_line_id => NULL
, p_whse_code => l_whse_code
, p_action_code => l_action_code );
IF(l_last_updated_by <> l_created_by) THEN
GMI_RESERVATION_UTIL.PrintLn('Notify CSR : l_created_by is '||l_created_by);
for mul_line in check_mul_line_id2 (l_last_updated_by, l_batch_id, l_batch_line_id ) Loop
l_old_header_id := l_so_header_id ;
FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_new_header_id ;
, p_approver => l_last_updated_by
, p_so_header_id=> l_so_header_id
, p_so_line_id => l_so_line_id
, p_batch_id => l_batch_id
, p_batch_line_id => NULL
, p_whse_code => l_whse_code
, p_action_code => l_action_code );
IF(l_last_updated_by <> l_created_by) THEN
GMI_RESERVATION_UTIL.PrintLn('Notify CSR : l_created_by is '||l_created_by);
Select *
Into l_gme_trans_row
From ic_tran_pnd
Where trans_id = p_alloc_history_rec.batch_trans_id;
/* delete the history record because new history records are created */
update gml_batch_so_alloc_history
set delete_mark = 1
Where alloc_rec_id = p_alloc_history_rec.alloc_rec_id;
SELECT reserved_qty
, reserved_qty2
, batch_res_id
FROM gml_batch_so_reservations
WHERE so_line_id = p_old_source_line_id
AND delivery_detail_id = p_old_delivery_detail_id
AND delete_mark = 0
AND reserved_qty <> 0
ORDER BY 1 ; /* the smaller qty is at the top, keep in mind it is neg */
SELECT reserved_qty
, reserved_qty2
, batch_res_id
FROM gml_batch_so_reservations
WHERE so_line_id = p_old_source_line_id
AND delete_mark = 0
AND reserved_qty <> 0
ORDER BY 1; /* the smaller qty is at the top, keep in mind it is neg */
Update gml_batch_so_reservations
Set so_line_id = p_new_source_line_id
Where so_line_id = p_old_source_line_id
And delete_mark = 0
And reserved_qty <> 0;
Update gml_batch_so_reservtions
Set so_line_id = p_new_source_line_id
Where so_line_id = p_old_source_line_id
and delivery_detail_id = p_new_delivery_detail_id
and delte_mark = 0
and reserved_qty <> 0;
GMI_RESERVATION_UTIL.PrintLn('Updated Here');
update gml_batch_so_reservations
set reserved_qty = l_qty_to_fulfil
, reserved_qty2 = l_qty2_to_fulfil
Where batch_res_id = res_rec.batch_res_id;
GML_BATCH_OM_UTIL.insert_reservation
(
P_Gme_om_reservation_rec => l_reservation_rec
, X_return_status => x_return_status
, X_msg_cont => x_msg_count
, X_msg_data => x_msg_data
) ;
GMI_RESERVATION_UTIL.Println('in split_res, update res '||res_rec.batch_res_id);
update gml_batch_so_reservations
set delivery_detail_id = p_new_delivery_detail_id
, so_line_id = p_new_source_line_id
Where batch_res_id = res_rec.batch_res_id;
update gml_batch_so_reservations
set delivery_detail_id = p_delivery_detail_id
Where so_line_id = p_so_line_id
and delete_mark = 0;
Select line_detail_id
From ic_tran_pnd
Where delete_mark = 0
and trans_id in
(Select trans_id
From gml_batch_so_alloc_history
Where batch_trans_id = p_batch_trans_id
and delete_mark = 0
)
;
Select distinct wdd.move_order_line_id
From wsh_delivery_details wdd
, ic_tran_pnd ictran
Where wdd.delivery_detail_id = ictran.line_detail_id
and ictran.delete_mark = 0
and ictran.trans_id in
(Select trans_id
From gml_batch_so_alloc_history
Where batch_line_id = p_batch_line_id
and delete_mark = 0
)
;