--SET SERVEROUTPUT ON
DECLARE
x_return_status VARCHAR2(1000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(1000);
l_segment1 VARCHAR2(150);
l_segment2 VARCHAR2(150);
l_segment3 VARCHAR2(150);
l_segment4 VARCHAR2(150);
l_segment5 VARCHAR2(150);
l_segment6 VARCHAR2(150);
l_segment7 VARCHAR2(150);
l_segment8 VARCHAR2(150);
CURSOR main_cur IS
SELECT
*
FROM
xxtlm.xxtlm_item_dtls_mi
WHERE
1 = 1
-- AND item_code IN ('CBB050E')
AND organization_id IN ( 85 --INA
, 97 --CA1
)
AND nvl(status_flag, 'N') <> 'S';
BEGIN
dbms_output.enable(100000000);
FOR rec IN main_cur LOOP
BEGIN
l_segment1 := '10';
l_segment2 := '00';
l_segment3 := CASE WHEN rec.is_consigned = 1 THEN '3205' ELSE '6060' END;
l_segment4 := '10';
l_segment5 := '01';
l_segment6 := CASE WHEN rec.planning_organization_id = 85 THEN '11' WHEN rec.planning_organization_id = 97 THEN '21' ELSE '00' END;
l_segment7 := '00';
l_segment8 := '0000';
xxtlm_pkmtl_transaction(p_item_id => rec.inventory_item_id, p_organization_id => rec.planning_organization_id,
p_owning_organization_id => NULL,
p_owning_tp_type => NULL,
p_planning_tp_type => NULL,
p_planning_organization_id => NULL,
p_subinv => rec.subinventory_code,
p_loc => rec.locators,
p_lot_no => NULL,
p_origination_date => NULL,
p_uom_code => rec.primary_uom_code,
p_quantity => rec.onhand_quantity * - 1,
p_transaction_type => 'Miscellaneous issue',
p_trans_type_ref => rec.organization_code
|| ' MISC ISSUE '
|| trunc(sysdate),
p_primary_qty => NULL,
p_clei_code => NULL, ---need to change logic
p_segment1 => l_segment1,
p_segment2 => l_segment2,
p_segment3 => l_segment3,
p_segment4 => l_segment4,
p_segment5 => l_segment5,
p_segment6 => l_segment6,
p_segment7 => l_segment7,
p_segment8 => l_segment8,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
dbms_output.put_line('X_RETURN_STATUS->'
|| x_return_status
|| ' X_MSG_COUNT->'
|| x_msg_count
|| ' X_MSG_DATA->'
|| x_msg_data);
END;
BEGIN
UPDATE xxtlm.xxtlm_item_dtls_mi
SET
status_flag = x_return_status,
ERROR_MSG=x_msg_data
WHERE
organization_id = rec.organization_id
AND inventory_item_id = rec.inventory_item_id
AND subinventory_code = rec.subinventory_code
AND locators = rec.locators
AND onhand_quantity = rec.onhand_quantity;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error while updating status for item: '
|| rec.item_code
|| '. locator: '
|| rec.locators);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unexpected Error in Main block: ' || sqlerrm);
END;
/
/***--Dependent Procedure---***/
create or replace PROCEDURE xxtlm_pkmtl_transaction (
p_item_id IN VARCHAR2,
p_organization_id IN NUMBER,
p_owning_organization_id IN mtl_onhand_quantities_detail.owning_organization_id%TYPE,
p_owning_tp_type IN mtl_onhand_quantities_detail.owning_tp_type%TYPE,
p_planning_tp_type IN mtl_onhand_quantities_detail.planning_tp_type%TYPE,
p_planning_organization_id IN mtl_onhand_quantities_detail.planning_organization_id%TYPE,
p_subinv IN VARCHAR2,
p_loc IN VARCHAR2,
p_lot_no IN VARCHAR2,
p_origination_date IN DATE,
p_uom_code IN VARCHAR2,
p_quantity IN NUMBER,
p_transaction_type IN VARCHAR2,
p_trans_type_ref IN VARCHAR2,
p_primary_qty IN NUMBER,
p_clei_code IN VARCHAR2,
p_segment1 IN VARCHAR2,
p_segment2 IN VARCHAR2,
p_segment3 IN VARCHAR2,
p_segment4 IN VARCHAR2,
p_segment5 IN VARCHAR2,
p_segment6 IN VARCHAR2,
p_segment7 IN VARCHAR2,
p_segment8 IN VARCHAR2,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
) IS
l_transaction_id NUMBER;
l_loc_id NUMBER;
l_trn_type_id NUMBER;
l_segment1 VARCHAR2(150);
l_segment2 VARCHAR2(150);
l_segment3 VARCHAR2(150);
l_segment4 VARCHAR2(150);
l_segment5 VARCHAR2(150);
l_segment6 VARCHAR2(150);
l_segment7 VARCHAR2(150);
l_segment8 VARCHAR2(150);
l_segment9 VARCHAR2(150);
l_segment10 VARCHAR2(150);
l_segment11 VARCHAR2(150);
l_expiration_date DATE;
l_trans_count NUMBER;
lc_result NUMBER;
l_subinv_code mtl_item_locations_kfv.subinventory_code%TYPE;
r_mtl_trns_rec mtl_transactions_interface%rowtype;
r_mtl_lot_rec mtl_transaction_lots_interface%rowtype;
g_l_transaction_id NUMBER;
g_l_lpn_id NUMBER := 0;
CURSOR cur_tns_rec IS
SELECT
mtt.*
FROM
mtl_transaction_types mtt
WHERE
mtt.transaction_type_name = p_transaction_type;
r_tns_rec cur_tns_rec%rowtype;
PRAGMA autonomous_transaction;
BEGIN
x_return_status := NULL;
x_msg_data := NULL;
dbms_output.put_line('================================================================================');
BEGIN
SELECT
mtl_material_transactions_s.NEXTVAL
INTO l_transaction_id
FROM
dual;
EXCEPTION
WHEN OTHERS THEN
l_transaction_id := NULL;
END;
OPEN cur_tns_rec;
FETCH cur_tns_rec INTO r_tns_rec;
CLOSE cur_tns_rec;
BEGIN
-- l_segment1 := '10';
-- l_segment2 := '00';
-- l_segment3 := '3205';
-- l_segment4 := '10';
-- l_segment5 := '01';
-- l_segment6 := '00';
-- l_segment7 := '00';
-- l_segment8 := '0000';
l_segment1 := p_segment1;
l_segment2 := p_segment2;
l_segment3 := p_segment3;
l_segment4 := p_segment4;
l_segment5 := p_segment5;
l_segment6 := p_segment6;
l_segment7 := p_segment7;
l_segment8 := p_segment8;
EXCEPTION
WHEN OTHERS THEN
l_segment1 := NULL;
l_segment2 := NULL;
l_segment6 := NULL;
l_segment3 := NULL;
l_segment4 := NULL;
l_segment5 := NULL;
l_segment7 := NULL;
l_segment8 := NULL;
l_segment9 := NULL;
l_segment10 := NULL;
END;
BEGIN
SELECT
inventory_location_id,
subinventory_code
INTO
l_loc_id,
l_subinv_code
FROM
mtl_item_locations_kfv
WHERE
concatenated_segments = p_loc
AND organization_id = p_organization_id;
EXCEPTION
WHEN OTHERS THEN
l_loc_id := NULL;
END;
dbms_output.put_line('p_loc: ' || p_loc);
dbms_output.put_line('l_loc_id: ' || l_loc_id);
dbms_output.put_line('p_lot_no: ' || p_lot_no);
dbms_output.put_line('Transaction_type: ' || r_tns_rec.transaction_type_name);
dbms_output.put_line('p_quantity: ' || p_quantity);
BEGIN
r_mtl_trns_rec := NULL;
g_l_transaction_id := NULL;
r_mtl_trns_rec.transaction_uom := p_uom_code;
r_mtl_trns_rec.transaction_date := sysdate;
r_mtl_trns_rec.source_code := p_transaction_type;
r_mtl_trns_rec.source_line_id := l_transaction_id; -- 422170
r_mtl_trns_rec.source_header_id := l_transaction_id; -- 167091
r_mtl_trns_rec.process_flag := 1;
r_mtl_trns_rec.transaction_mode := 3;
r_mtl_trns_rec.lock_flag := 2;
r_mtl_trns_rec.locator_id := l_loc_id;
r_mtl_trns_rec.last_update_date := sysdate;
r_mtl_trns_rec.last_updated_by := fnd_global.user_id;
r_mtl_trns_rec.creation_date := sysdate;
r_mtl_trns_rec.created_by := fnd_global.user_id;
r_mtl_trns_rec.inventory_item_id := p_item_id;
r_mtl_trns_rec.subinventory_code := nvl(p_subinv, l_subinv_code);
r_mtl_trns_rec.organization_id := p_organization_id;
r_mtl_trns_rec.transaction_quantity := p_quantity;
r_mtl_trns_rec.primary_quantity := p_primary_qty;
r_mtl_trns_rec.transaction_type_id := r_tns_rec.transaction_type_id;
r_mtl_trns_rec.transaction_source_type_id := r_tns_rec.transaction_source_type_id;
r_mtl_trns_rec.transaction_action_id := r_tns_rec.transaction_action_id;
--Added by Venky
r_mtl_trns_rec.owning_organization_id := p_owning_organization_id; --3091
r_mtl_trns_rec.owning_tp_type := p_owning_tp_type; --1
r_mtl_trns_rec.planning_organization_id := p_planning_organization_id;
r_mtl_trns_rec.planning_tp_type := p_planning_tp_type; --2
-- End Added by Venky
r_mtl_trns_rec.dst_segment1 := l_segment1;
r_mtl_trns_rec.dst_segment2 := l_segment2;
r_mtl_trns_rec.dst_segment3 := l_segment3;
r_mtl_trns_rec.dst_segment4 := l_segment4;
r_mtl_trns_rec.dst_segment5 := l_segment5;
r_mtl_trns_rec.dst_segment6 := l_segment6;
r_mtl_trns_rec.dst_segment7 := l_segment7;
r_mtl_trns_rec.dst_segment8 := l_segment8;
r_mtl_trns_rec.dst_segment9 := l_segment9;
r_mtl_trns_rec.dst_segment10 := l_segment10;
r_mtl_trns_rec.transaction_interface_id := l_transaction_id;
r_mtl_trns_rec.transaction_header_id := l_transaction_id;
r_mtl_trns_rec.transaction_reference := p_trans_type_ref;
IF g_l_lpn_id > 0 THEN
r_mtl_trns_rec.transfer_lpn_id := g_l_lpn_id;
dbms_output.put_line('g_l_lpn_id: ' || g_l_lpn_id);
END IF;
-- Insert MTI for inv open transactions.
INSERT INTO mtl_transactions_interface VALUES r_mtl_trns_rec;
dbms_output.put_line('Record inserted into mtl_transactions_interface with transaction_header_id: ' || l_transaction_id);
g_l_transaction_id := l_transaction_id;
--dbms_output.put_line('Inserted a record'||l_transaction_id);
x_return_status := fnd_api.g_ret_sts_success;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.output,
'in exception ---- inserting data into MTL_TRANSACTIONS_INTERFACE : ' || sqlerrm);
dbms_output.put_line('Error' || sqlerrm);
x_return_status := fnd_api.g_ret_sts_error;
x_msg_count := x_msg_count + 1;
x_msg_data := 'Error While Inserting data to MTL_TRANSACTIONS_INTERFACE :' || sqlerrm;
END;
dbms_output.put_line('STEP1');
IF x_return_status != fnd_api.g_ret_sts_error THEN --Successful return of first insert
IF p_lot_no IS NOT NULL THEN
--Derive lot expiration date for the lot number
BEGIN
SELECT
expiration_date
INTO l_expiration_date
FROM
mtl_lot_numbers
WHERE
lot_number = p_lot_no
AND inventory_item_id = p_item_id
AND organization_id = p_organization_id;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception in fetching lot expiration_date: ' || sqlerrm);
l_expiration_date := NULL;
dbms_output.put_line('STEP2');
END;
dbms_output.put_line('STEP3');
dbms_output.put_line('Expiration_date: ' || l_expiration_date);
dbms_output.put_line('STEP3.1');
BEGIN
dbms_output.put_line('STEP4');
--Insert lot number interface if the item is lot controlled.
r_mtl_lot_rec := NULL;
g_l_transaction_id := l_transaction_id;
r_mtl_lot_rec.transaction_interface_id := l_transaction_id;
r_mtl_lot_rec.source_code := p_transaction_type;
r_mtl_lot_rec.source_line_id := l_transaction_id;
r_mtl_lot_rec.last_update_date := sysdate;
r_mtl_lot_rec.last_updated_by := fnd_global.user_id;
r_mtl_lot_rec.creation_date := sysdate;
r_mtl_lot_rec.created_by := fnd_global.user_id;
r_mtl_lot_rec.last_update_login := fnd_global.login_id;
r_mtl_lot_rec.lot_number := p_lot_no;
r_mtl_lot_rec.origination_date := p_origination_date;
r_mtl_lot_rec.lot_expiration_date := l_expiration_date;
r_mtl_lot_rec.transaction_quantity := p_quantity;
r_mtl_lot_rec.primary_quantity := p_quantity;
r_mtl_lot_rec.serial_transaction_temp_id := l_transaction_id;
--New CLEI Code--
r_mtl_lot_rec.lot_attribute_category := '102';
r_mtl_lot_rec.c_attribute1 := p_clei_code;
--New CLEI Code--
dbms_output.put_line('STEP5');
INSERT INTO mtl_transaction_lots_interface VALUES r_mtl_lot_rec;
dbms_output.put_line('STEP6');
EXCEPTION
WHEN OTHERS THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_count := x_msg_count + 1;
x_msg_data := 'Error While Inserting data to MTL_TRANSACTION_LOTS_INTERFACE :' || sqlerrm;
dbms_output.put_line('STEP7');
END;
END IF;
dbms_output.put_line('STEP8');
COMMIT;
--call Inventory open transaction api to perform transfer of material from source locator to destination locator.
dbms_output.put_line('STEP9');
x_return_status := NULL;
x_msg_count := 0;
x_msg_data := NULL;
dbms_output.put_line('Before Calling process_transactions' || l_transaction_id);
lc_result := inv_txn_manager_pub.process_transactions(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_trans_count => l_trans_count,
p_table => 1,
p_header_id => l_transaction_id);
dbms_output.put_line('STEP10');
dbms_output.put_line('Return Status' || x_return_status);
IF nvl(x_return_status, 'N') <> fnd_api.g_ret_sts_success THEN
x_return_status := fnd_api.g_ret_sts_error;
dbms_output.put_line('Return Status: ' || x_return_status);
dbms_output.put_line('Message: ' || x_msg_data);
dbms_output.put_line('STEP11');
END IF;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('In Exception' || sqlerrm);
fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
dbms_output.put_line('STEP12');
ROLLBACK;
END xxtlm_pkmtl_transaction;