[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update_history NUMBER;
Select *
from ic_tran_pnd
where trans_id = p_trans_id;
Select distinct whse_code
From gml_batch_so_reservations
Where batch_line_id = p_batch_line_id;
Select sum(reserved_qty), sum(reserved_qty2)
From gml_batch_so_reservations
Where batch_line_id = p_batch_line_id
and whse_code = p_whse_code
and delete_mark = 0;
Select abs(sum(nvl(trans_qty,0))), abs(sum(nvl(trans_qty2,0)))
From ic_tran_pnd
Where line_id = p_batch_line_id
and whse_code = p_whse_code
and doc_type in ('PROD', 'FPO')
and delete_mark = 0
and completed_ind = 0;
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'
;
Select abs(sum(trans_qty)),abs(sum(trans_qty2))
From ic_tran_pnd ic
, gml_batch_so_alloc_history his
Where his.batch_trans_id = p_gme_trans_id
and his.trans_id = ic.trans_id
and his.delete_mark = 0
and ic.delete_mark = 0
and ic.completed_ind = 0
;
Select alloc_rec_id
From gml_batch_so_alloc_history
Where batch_trans_id = p_gme_trans_id
and delete_mark = 0
;
Select scheduled_ship_date
From gml_batch_so_reservations
Where batch_line_id = p_batch_line_id
and delete_mark = 0
and reserved_qty <> 0
;
Select distinct batch_line_id
From gml_batch_so_reservations
Where batch_id = p_batch_id
and delete_mark = 0
and reserved_qty <> 0
;
l_update_history := 0;
GML_GME_API_PVT.g_not_to_delete := 0; -- always delete reservation when availability is 0
IF p_batch_action = 'DELETE' THEN
/* 1) cancel-terminate batch -- batch level done here
* 2) reserval batch at the trans level -- batch line level blocks below
*/
IF p_batch_line_rec.batch_id is not null
and p_batch_line_rec.batch_line_id is null
and p_batch_line_rec.trans_id is null
THEN
GMI_RESERVATION_UTIL.PrintLn(' Batch level actions: DELETE');
/* set g_not_to_delete to 1 if convertion is waiting for the lot status */
END IF;
IF p_batch_action = 'DELETE' THEN
/* actions at the batch side could be
* 1) cancel-terminate batch -- batch level, above
* 2) reserval batch at the trans level
*/
GMI_RESERVATION_UTIL.PrintLn(' In Process_om_reservations...... IF p_batch_action = DELETE');
* p_gme_trans_row.trans_id will be passed as the new (to be updated) trans_id
*/
IF p_batch_line_rec.trans_id is not null THEN
GMI_RESERVATION_UTIL.println('Fetch old gme trans '||p_batch_line_rec.trans_id);
* 2) update pending to complete
*/
IF p_batch_line_rec.batch_line_id is not null THEN
GMI_RESERVATION_UTIL.println('batch line level');
/* if planned qty becomes 0, delete all the res for this whse */
GMI_RESERVATION_UTIL.println(' global g_not_to_delete '||GML_GME_API_PVT.g_not_to_delete);
IF (nvl(l_planned_qty,0) <= 0 AND GML_GME_API_PVT.g_not_to_delete = 0) THEN
GML_BATCH_OM_RES_PVT.cancel_res_for_batch_line
(
P_Batch_line_id => p_batch_line_rec.batch_line_id
, P_whse_code => l_whse_code
, X_return_status => x_return_status
, X_msg_cont => l_msg_cont
, X_msg_data => l_msg_data
) ;
GMI_RESERVATION_UTIL.println('UPDP, update pending to complete');
GMI_RESERVATION_UTIL.println('UPDC, update the completed transaction');
/* update history ? */
/* increasing qty is to create neg orginal qty and create new trans with the new qty*/
l_update_history := 1;
l_update_history := 1;
GMI_TRANS_ENGINE_PUB.update_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 => l_msg_cont,
x_msg_data => l_msg_data
);
GMI_reservation_Util.PrintLn('update complete trans, alloc creation error');
l_update_history := 1;
GMI_TRANS_ENGINE_PUB.update_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 => l_msg_cont,
x_msg_data => l_msg_data
);
GMI_reservation_Util.PrintLn('update complete trans, alloc creation error');
l_update_history := 1;
GMI_RESERVATION_UTIL.println('Process_Om_reservations .....Completed batch tran is deleted');
/* we don't change the history because old trans are deleted and still
* linked with the old gme trans */
END IF;
/* the old trans is deleted and replaced by the new trans
* need to update the history table to keep the link alive
*/
/* just do the update */
/* update gml_batch_so_alloc_history
Set batch_trans_id = l_new_gme_trans_row.trans_id
Where batch_trans_id = l_old_gme_trans_row.trans_id
and nvl(trans_id,0) <> 0
and delete_mark = 0
;
IF l_update_history = 1 THEN
for his_rec in get_history_id (l_old_gme_trans_row.trans_id) LOOP
/* delete the old history record */
update gml_batch_so_alloc_history
Set delete_mark = 1
Where alloc_rec_id = his_rec.alloc_rec_id
;
GMI_RESERVATION_UTIL.println(' delete history.alloc_rec_id '||his_rec.alloc_rec_id);
GML_BATCH_OM_UTIL.insert_alloc_history
(
P_alloc_history_rec => l_history_rec
, X_return_status => x_return_status
, X_msg_cont => l_msg_cont
, X_msg_data => l_msg_data
);
GMI_RESERVATION_UTIL.println('UPDP, update complete to pending');
END IF; -- update complted to pending