SUB_INVENTORY_TRANSFER PROCEDURE
create or replace procedure SUBINVENTORY_TRANSFER (P_ITEM_ID in varchar2, --'985877'
P_ORGANIZATION_ID in number, -- 4
P_FROM_SUBINV in varchar2
P_FROM_LOC in varchar2,
P_LOT_NO in varchar2,
P_UOM_CODE in varchar2,
P_QUANTITY in number, --6 this should un reserved quantity then only sub inventory transfer will success
P_TRANSACTION_TYPE in varchar2, --'Subinventory Transfer'
P_TO_SUBINV in varchar2,
p_to_loc IN VARCHAR2,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2,
p_lpn_id IN NUMBER)
IS
l_transaction_id NUMBER;
l_loc_id NUMBER;
l_from_loc_id NUMBER;
l_to_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_expiration_date DATE;
l_trans_count NUMBER;
lc_result NUMBER;
BEGIN
BEGIN
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_id
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
l_transaction_id := NULL;
END;
BEGIN
SELECT transaction_type_id
INTO l_trn_type_id
FROM mtl_transaction_types
WHERE transaction_type_name = p_transaction_type;
EXCEPTION
WHEN OTHERS
THEN
l_trn_type_id := NULL;
END;
BEGIN
SELECT gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gcc.segment9,
gcc.segment10
INTO l_segment1,
l_segment2,
l_segment3,
l_segment4,
l_segment5,
l_segment6,
l_segment7,
l_segment8,
l_segment9,
l_segment10
FROM gl_code_combinations_kfv gcck, gl_code_combinations gcc
where GCC.CODE_COMBINATION_ID= 1669
--gcck.concatenated_segments = '01-00-12106-000000-00000-00000'
AND gcc.code_combination_id = gcck.code_combination_id;
EXCEPTION
WHEN OTHERS
THEN
l_segment1 := NULL;
l_segment2 := NULL;
l_segment3 := NULL;
l_segment4 := NULL;
l_segment5 := NULL;
l_segment6 := NULL;
l_segment7 := NULL;
l_segment8 := NULL;
l_segment9 := NULL;
l_segment10 := NULL;
END;
BEGIN
SELECT inventory_location_id
INTO l_from_loc_id
FROM mtl_item_locations_kfv
WHERE concatenated_segments = p_from_loc
AND organization_id = p_organization_id;
EXCEPTION
WHEN OTHERS
THEN
l_from_loc_id := NULL;
END;
BEGIN
SELECT inventory_location_id
INTO l_to_loc_id
FROM mtl_item_locations_kfv
WHERE concatenated_segments = p_to_loc
AND organization_id = p_organization_id;
EXCEPTION
WHEN OTHERS
THEN
l_to_loc_id := NULL;
END;
dbms_output.put_line ('l_to_loc_id: ' || l_to_loc_id); --p_log
dbms_output.put_line ('p_to_subinv: ' || p_to_subinv);
BEGIN
-- Insert MTI for inv open transactions.
INSERT INTO mtl_transactions_interface (transaction_uom,
transaction_date,
source_code,
source_line_id,
source_header_id,
process_flag,
transaction_mode,
lock_flag,
locator_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
inventory_item_id,
subinventory_code,
organization_id,
transaction_quantity,
primary_quantity,
transaction_type_id,
dst_segment1,
dst_segment2,
dst_segment3,
dst_segment4,
dst_segment5,
dst_segment9,
dst_segment6,
dst_segment7,
dst_segment8,
dst_segment10,
transfer_subinventory,
transfer_locator,
transaction_interface_id,
transaction_header_id,
content_lpn_id)
VALUES (p_uom_code, --transaction uom
SYSDATE, --transaction date
p_transaction_type, --source code
l_transaction_id, --source line id
l_transaction_id, --source header id
1, --process flag
3, --transaction mode
2, --lock flag
l_from_loc_id, --locator id
sysdate, --last update date
fnd_global.user_id, --last updated by
SYSDATE, --creation date
fnd_global.user_id, --created by
p_item_id, --inventory item id
p_from_subinv, --From subinventory code
p_organization_id, --organization id
p_quantity, --transaction quantity
p_quantity, --Primary quantity
L_TRN_TYPE_ID, --transaction type id
l_segment1, --segment1 account combination
l_segment2, --segment2 account combination
l_segment3, --segment3 account combination
l_segment4, --segment4 account combination
l_segment5, --segment5 account combination
l_segment6, --segment5 account combination
l_segment7, --segment5 account combination
l_segment8, --segment5 account combination
l_segment9, --segment5 account combination
L_SEGMENT10, --segment5 account combination
p_to_subinv,
l_to_loc_id,
l_transaction_id, --transaction interface id
l_transaction_id,
p_lpn_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;
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
l_expiration_date := NULL;
END;
BEGIN
--Insert lot number interface if the item is lot controlled.
INSERT
INTO MTL_TRANSACTION_LOTS_INTERFACE (
transaction_interface_id,
source_code,
source_line_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
lot_number,
lot_expiration_date,
transaction_quantity,
primary_quantity,
serial_transaction_temp_id)
VALUES (l_transaction_id,
p_transaction_type,
l_transaction_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
p_lot_no,
l_expiration_date,
p_quantity,
p_quantity,
l_transaction_id);
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;
END;
END IF;
COMMIT;
--call Inventory open transaction api to perform transfer of material from source locator to destination locator.
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 ('Return Status' || x_return_status);
IF NVL (x_return_status, 'N') != FND_API.g_ret_sts_success
THEN
dbms_output.put_line ('Return Status' || x_return_status);
COMMIT;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_msg_pub.count_and_get (p_count => x_msg_count,
p_data => x_msg_data,
p_encoded => fnd_api.g_false);
end SUBINVENTORY_TRANSFER;
/
No comments:
Post a Comment