oracle子库存转移,子库存转移API Demo

--子库存转移

DECLARE

l_iface_rec inv.mtl_transactions_interface%ROWTYPE;

l_iface_serial_rec inv.mtl_serial_numbers_interface%ROWTYPE;

p_issue_txn_id NUMBER;

x_error_status VARCHAR2(1);

x_error_message VARCHAR2(2000);

l_return_status VARCHAR2(40);

l_msg_count NUMBER;

l_msg_data VARCHAR2(2000);

l_trans_count NUMBER;

l_request_id NUMBER;

begin

fnd_global.apps_initialize(user_id => 2411, resp_id => 50647, resp_appl_id => 20003);

--

l_iface_rec.last_update_date := SYSDATE;

l_iface_rec.last_updated_by := fnd_global.user_id;

l_iface_rec.creation_date := SYSDATE;

l_iface_rec.created_by := fnd_global.user_id;

l_iface_rec.last_update_login := -1;

SELECT mtl_material_transactions_s.nextval INTO l_iface_rec.transaction_interface_id FROM dual;

l_iface_rec.transaction_header_id := l_iface_rec.transaction_interface_id;

--

l_iface_rec.transaction_mode := 3;

l_iface_rec.process_flag := 1;

l_iface_rec.transaction_type_id := 2; --mtl_transaction_types

--l_iface_rec.transaction_source_id := 2; --置空

l_iface_rec.organization_id := 683;

l_iface_rec.inventory_item_id := 143116;

l_iface_rec.subinventory_code := '200-BX1';

l_iface_rec.TRANSFER_SUBINVENTORY := '200-EXP';

l_iface_rec.transaction_quantity := 2;

l_iface_rec.transaction_uom := 'UN';

l_iface_rec.transaction_date := SYSDATE;

l_iface_rec.source_code := 'Test Only';

l_iface_rec.source_header_id := 987654321;

l_iface_rec.source_line_id := 987654321;

INSERT INTO inv.mtl_transactions_interface VALUES l_iface_rec;

--

l_iface_serial_rec.transaction_interface_id := l_iface_rec.transaction_interface_id;

l_iface_serial_rec.source_code := 'lcy_wip_completion_txn';

l_iface_serial_rec.source_line_id := 987654321;

l_iface_serial_rec.last_update_date := SYSDATE;

l_iface_serial_rec.last_updated_by := fnd_global.user_id;

l_iface_serial_rec.creation_date := SYSDATE;

l_iface_serial_rec.created_by := fnd_global.user_id;

l_iface_serial_rec.program_update_date := SYSDATE;

l_iface_serial_rec.fm_serial_number := '0001';

l_iface_serial_rec.to_serial_number := '0002';

l_iface_serial_rec.process_flag := 1;

INSERT INTO inv.mtl_serial_numbers_interface VALUES l_iface_serial_rec;

--

l_request_id := inv_txn_manager_pub.process_transactions(p_api_version => 1,

p_init_msg_list => fnd_api.g_false,

p_commit => fnd_api.g_false,

p_validation_level => fnd_api.g_valid_level_full,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_trans_count => l_trans_count,

p_table => 1,

p_header_id => l_iface_rec.transaction_header_id);

x_error_status := l_return_status;

BEGIN

SELECT mti.error_code || ',' || mti.error_explanation || ',' || mli.error_code

INTO x_error_message

FROM mtl_transactions_interface mti,

mtl_transaction_lots_interface mli

WHERE mti.transaction_interface_id = l_iface_rec.transaction_interface_id

AND mli.transaction_interface_id(+) = mti.transaction_interface_id;

EXCEPTION

WHEN no_data_found THEN

NULL;

END;

dbms_output.put_line('x_error_status:' || x_error_status);

dbms_output.put_line('x_error_message:' || x_error_message);

end;