ORACLE TECH CENTER: Miscellaneous Receipt

Inventory Miscellaneous Receipt or Miscellaneous issue scripts in oracle apps

Step1 : Run the Below Procedure

create or replace PROCEDURE PKMTL_TRANSACTION(p_item_id IN VARCHAR2,
p_organization_id IN NUMBER,
p_subinv IN VARCHAR2,
p_loc IN VARCHAR2,
p_lot_no IN VARCHAR2,
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,
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:=01;
l_segment1:='01';
l_segment2:='510';
l_segment3:='4110';
l_segment4:='0000';
l_segment5:='000';
l_segment6:=null;
l_segment7:=null;
l_segment8:=null;
l_segment9:=null;
l_segment10:=null;
/*SELECT gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gcc.segment9,
gcc.segment10
INTO l_segment11,
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= 19547
AND gcc.code_combination_id = gcck.code_combination_id;*/
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('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;
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.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 ;
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 PKMTL_TRANSACTION;

Note :In the above procedure change the l_segment values accordingly.

Step2 : Run the Below anonymous block and pass the parameters accordingly for "Miscellaneous issue"

declare
X_RETURN_STATUS varchar2(1000);
X_MSG_COUNT number;
X_MSG_DATA VARCHAR2(1000);

BEGIN

BEGIN
PKMTL_TRANSACTION(287955, 207, 'Stores',NULL, NULL, 'Ea', -1, 'Miscellaneous issue',
NULL, null, X_RETURN_STATUS, X_MSG_COUNT, 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;

Run the Below anonymous block and pass the parameters accordingly for "Miscellaneous receipt"

declare
X_RETURN_STATUS varchar2(1000);
X_MSG_COUNT number;
X_MSG_DATA VARCHAR2(1000);

BEGIN

BEGIN
PKMTL_TRANSACTION(287955, 207, 'Stores',NULL, NULL, 'Ea', 1, 'Miscellaneous receipt',
NULL, null, X_RETURN_STATUS, X_MSG_COUNT, 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;

ORACLE TECH CENTER

Monday, 18 July 2016

Inventory Miscellaneous Receipt or Miscellaneous issue scripts from backend in oracle apps