[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM * Do not delete invisible move orders if batch has been closed previously.
REM * G. Muratore 10-JAN-2013 Bug 16031581
REM * Load resource transactions into memory so that they get reversed.
REM * PROCEDURE: cancel_batch
REM **********************************************************************
*/
/*================================================================================
Procedure
Cancel_Batch
Description
This particular procedure call cancel the batch.
Parameters
p_batch_header_rec The batch header row to identify the batch
p_validation_level Errors to skip before returning - Default 100
x_batch_header_rec The batch header row to identify the batch
x_message_count The number of messages in the message stack
x_message_list message stack where the api writes its messages
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
History
G. Muratore 04-OCT-10 Bug 10100973
Remove orphan move order line records if they exist.
G. Muratore 10-JAN-2013 Bug 16031581
Load resource transactions into memory so that they get reversed.
================================================================================*/
PROCEDURE cancel_batch (
p_batch_header_rec IN gme_batch_header%ROWTYPE
,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR cur_get_steps (v_batch_id NUMBER)
IS
SELECT *
FROM gme_batch_steps
WHERE batch_id = v_batch_id
ORDER BY batchstep_id;
batch_hist_insert_err EXCEPTION;
reservation_delete_err EXCEPTION; -- 4944024
|| ' Call delete_prod_supply_resv');
gme_supply_res_pvt.delete_prod_supply_resv (
p_matl_dtl_rec => l_material_details_tab(i)
,x_msg_count => l_message_count
,x_msg_data => l_message_data
,x_return_status => x_return_status);
|| ' Return from delete_prod_supply_resv indicates '
|| x_return_status);
RAISE reservation_delete_err;
DELETE FROM mtl_txn_request_lines l
WHERE organization_id = x_batch_header_rec.organization_id
AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
AND txn_source_id = l_material_details_tab(i).batch_id
AND txn_source_line_id = l_material_details_tab(i).material_detail_id
AND line_status = 7
AND NOT EXISTS (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.header_id = l.header_id)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp mmtt
where l.line_id = mmtt.move_order_line_id);
IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
IF (g_debug <= gme_debug.g_log_procedure) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'calling purge exceptions for batch_id='
|| x_batch_header_rec.batch_id);
,p_delete_invis_mo => 'T'
,p_delete_reservations => 'T'
,p_delete_trans_pairs => 'T'
,x_return_status => l_return_status);
UPDATE MTL_TXN_REQUEST_Lines
SET line_status = 5
WHERE line_id in
(SELECT mtrl.line_id
FROM MTL_TXN_REQUEST_Lines mtrl, mtl_txn_request_headers mtrh
WHERE mtrl.TRANSACTION_SOURCE_TYPE_ID = 5
AND mtrl.LINE_STATUS = 7
AND mtrl.LPN_ID IS NOT NULL
AND mtrh.header_id = mtrl.header_id
AND mtrh.ORGANIZATION_ID = mtrl.organization_id
AND mtrl.txn_source_id = x_batch_header_rec.batch_id
AND mtrl.ORGANIZATION_ID = x_batch_header_rec.organization_id
AND mtrh.MOVE_ORDER_TYPE = 6);
/* Now update the batch step status to Cancel (5) */
-- Get batch_step_ids bulk collected for batch
IF (g_debug <= gme_debug.g_log_procedure) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'routing exists');
,p_update_inventory_ind => x_batch_header_rec.update_inventory_ind
,x_return_status => l_return_status
,x_batch_step_rec => l_batch_step_rec);
/* Update the batch status to the database */
IF (g_debug <= gme_debug.g_log_procedure) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'Call batch UPDATE_ROW'
|| x_batch_header_rec.batch_status);
IF NOT (gme_batch_header_dbl.update_row (x_batch_header_rec) ) THEN
RAISE batch_header_upd_err;
IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
-- Insert the event into the batch history table
IF NOT gme_common_pvt.create_history (x_batch_header_rec, -1) THEN
IF (g_debug <= gme_debug.g_log_procedure) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'create history');
RAISE batch_hist_insert_err;
/* Update the row who columns */
x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
WHEN batch_hist_insert_err THEN
IF (g_debug <= gme_debug.g_log_procedure) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'BATCH_HIST_INSERT_ERR.');
WHEN reservation_delete_err THEN
IF (g_debug <= gme_debug.g_log_procedure) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| ' DELETE_PROD_SUPPLY_RESV ERROR');
,p_delete_invis_mo IN VARCHAR2 := 'F'
,p_delete_reservations IN VARCHAR2 := 'F'
,p_delete_trans_pairs IN VARCHAR2 := 'F'
,p_recursive IN VARCHAR2 := 'N'
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_material_details_rec gme_material_details%ROWTYPE;
delete_reservations_err EXCEPTION;
delete_move_order_err EXCEPTION;
delete_trans_pair_err EXCEPTION;
delete_pend_prod_lots_err EXCEPTION;
SELECT count(*)
FROM gme_batch_history
WHERE batch_id = v_batch_id
AND new_status = 4;
l_delete_invis_mo VARCHAR2(1);
,p_delete_invis_mo => p_delete_invis_mo
,p_delete_reservations => p_delete_reservations
,p_delete_trans_pairs => p_delete_trans_pairs
,x_return_status => l_return_status);
l_delete_invis_mo := p_delete_invis_mo;
l_delete_invis_mo := 'F';
gme_move_orders_pvt.delete_batch_move_orders
(p_organization_id => l_batch_header_rec.organization_id
,p_batch_id => l_batch_header_rec.batch_id
,p_delete_invis => l_delete_invis_mo
,x_return_status => l_return_status);
RAISE delete_move_order_err;
IF p_delete_reservations = fnd_api.g_true THEN
-- delete all reservations
IF (g_debug <= gme_debug.g_log_statement) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'deleting reservations for batch_id:'
|| l_batch_header_rec.batch_id);
gme_reservations_pvt.delete_batch_reservations
(p_organization_id => l_batch_header_rec.organization_id
,p_batch_id => l_batch_header_rec.batch_id
,x_return_status => l_return_status);
RAISE delete_reservations_err;
IF p_delete_trans_pairs = fnd_api.g_true THEN
IF (g_debug <= gme_debug.g_log_statement) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'deleting trans pairs for batch_id:'
|| l_batch_header_rec.batch_id);
RAISE delete_trans_pair_err;
delete_pending_lots (p_batch_id => l_batch_header_rec.batch_id
,x_return_status => l_return_status);
RAISE delete_pend_prod_lots_err;
|| 'delete_move_order_err');
WHEN delete_move_order_err THEN
IF (g_debug <= gme_debug.g_log_procedure) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'delete_move_order_err');
WHEN delete_reservations_err THEN
IF (g_debug <= gme_debug.g_log_procedure) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'delete_reservations_err');
WHEN delete_trans_pair_err THEN
IF (g_debug <= gme_debug.g_log_procedure) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'delete_trans_pair_err');
WHEN delete_pend_prod_lots_err THEN
IF (g_debug <= gme_debug.g_log_procedure) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'delete_pend_prod_lots_err');
PROCEDURE delete_pending_lots (
p_batch_id IN NUMBER
,p_material_detail_id IN NUMBER DEFAULT NULL
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_batch_id NUMBER;
l_api_name CONSTANT VARCHAR2 (30) := 'delete_pending_lots';
DELETE FROM gme_pending_product_lots
WHERE batch_id = l_batch_id;
DELETE FROM gme_pending_product_lots
WHERE material_detail_id = l_material_detail_id;
DELETE FROM gme_pending_product_lots
WHERE batch_id = l_batch_id
AND material_detail_id = l_material_detail_id;
END delete_pending_lots;