oracle 物料属性批次过期,ORACLE EBS 物料事务处理开发接口案例 - 其物料启用了序列和批次...

DECLARE

l_iface_rec inv.mtl_transactions_interface% ROWTYPE;

l_iface_lot_rec inv.mtl_transaction_lots_interface%ROWTYPE ;

l_iface_serial_rec inv.mtl_serial_numbers_interface%ROWTYPE ;

l_cur_mfg_org_id NUMBER := fnd_global.org_id ; --Current Inv Organization

l_user_id NUMBER := fnd_global.user_id; --User ID, Sysadmin here

l_serial_seq_id NUMBER;

l_product_id NUMBER;

l_return_count NUMBER;

x_trans_count NUMBER;

x_return_status VARCHAR2(100 );

x_msg_count NUMBER;

x_msg_data VARCHAR2(2000 );

BEGIN

fnd_global.apps_initialize(user_id => fnd_global.user_id

,resp_id => fnd_global.resp_id

,resp_appl_id => fnd_global.resp_appl_id);

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 := fnd_global.last_update_login ;

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 := 42; --mtl_transaction_types 42->杂项接受 32-> 杂项发放

l_iface_rec.organization_id := l_cur_mfg_org_id;

l_iface_rec.inventory_item_id := 309978;

l_iface_rec.subinventory_code := 'RPK';

l_iface_rec.locator_id := 3877;

l_iface_rec.transfer_locator := 3900;

l_iface_rec.transfer_organization := l_cur_mfg_org_id;

l_iface_rec.transfer_subinventory := '11204';

l_iface_rec.transaction_quantity := 3;

l_iface_rec.transaction_uom := 'Ea';

l_iface_rec.transaction_date := SYSDATE;

l_iface_rec.source_code := 'Test Miscellaneous receipt API';

l_iface_rec.source_header_id := l_iface_rec.transaction_interface_id;

l_iface_rec.source_line_id := l_iface_rec.transaction_interface_id;

INSERT INTO inv.mtl_transactions_interface

VALUES l_iface_rec;

--第一批

l_serial_seq_id := mtl_material_transactions_s.nextval;

l_iface_lot_rec.last_update_date := SYSDATE;

l_iface_lot_rec.last_updated_by := l_user_id;

l_iface_lot_rec.creation_date := SYSDATE;

l_iface_lot_rec.created_by := l_user_id;

l_iface_lot_rec.last_update_login := - 1;

l_iface_lot_rec.lot_number := 'A112';

l_iface_lot_rec.transaction_interface_id := l_iface_rec.transaction_interface_id;

l_iface_lot_rec.transaction_quantity := 2;

l_iface_lot_rec.product_transaction_id := l_iface_rec.transaction_interface_id;

--启用虚列时必输，否则不能给值

l_iface_lot_rec.serial_transaction_temp_id := l_serial_seq_id;

INSERT INTO inv.mtl_transaction_lots_interface

VALUES l_iface_lot_rec;

l_iface_serial_rec.last_update_date := SYSDATE;

l_iface_serial_rec.last_updated_by := l_user_id;

l_iface_serial_rec.creation_date := SYSDATE;

l_iface_serial_rec.created_by := l_user_id;

l_iface_serial_rec.last_update_login := - 1;

l_iface_serial_rec.fm_serial_number := '10012';

l_iface_serial_rec.to_serial_number := '10013';

l_iface_serial_rec.transaction_interface_id := l_serial_seq_id;

INSERT INTO inv.mtl_serial_numbers_interface

VALUES l_iface_serial_rec;

---第二批

l_serial_seq_id := mtl_material_transactions_s.nextval;

l_iface_lot_rec.last_update_date := SYSDATE;

l_iface_lot_rec.last_updated_by := l_user_id;

l_iface_lot_rec.creation_date := SYSDATE;

l_iface_lot_rec.created_by := l_user_id;

l_iface_lot_rec.last_update_login := - 1;

l_iface_lot_rec.lot_number := 'A112';

l_iface_lot_rec.transaction_interface_id := l_iface_rec.transaction_interface_id;

l_iface_lot_rec.transaction_quantity := 1;

l_iface_lot_rec.product_transaction_id := l_iface_rec.transaction_interface_id;

--启用虚列时必输，否则不能给值

l_iface_lot_rec.serial_transaction_temp_id := l_serial_seq_id;

INSERT INTO inv.mtl_transaction_lots_interface

VALUES l_iface_lot_rec;

l_iface_serial_rec.last_update_date := SYSDATE;

l_iface_serial_rec.last_updated_by := l_user_id;

l_iface_serial_rec.creation_date := SYSDATE;

l_iface_serial_rec.created_by := l_user_id;

l_iface_serial_rec.last_update_login := - 1;

l_iface_serial_rec.fm_serial_number := '10017';

l_iface_serial_rec.to_serial_number := '10017';

l_iface_serial_rec.transaction_interface_id := l_serial_seq_id;

INSERT INTO inv.mtl_serial_numbers_interface

VALUES l_iface_serial_rec;

fnd_msg_pub.initialize;

x_return_status := fnd_api.g_ret_sts_success; --0 to indicate successful processing and value -1 to indicate failure processing

l_return_count := inv_txn_manager_pub.process_transactions(p_api_version => 1.0

,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 => x_return_status

,x_msg_count => x_msg_count

,x_msg_data => x_msg_data

,x_trans_count => x_trans_count

,p_table => 1

, --1==MTI,2==MMTT

p_header_id => l_iface_rec.transaction_interface_id);

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

IF l_return_count = - 1

OR x_return_status <> fnd_api.g_ret_sts_success THEN

FOR a_rec IN ( SELECT mti.transaction_interface_id

,mti.error_code

,mti.error_explanation

FROM mtl_transactions_interface mti

WHERE mti.transaction_header_id = l_iface_rec.transaction_header_id) LOOP

dbms_output.put_line('transaction_interface_id: ' || a_rec.transaction_interface_id);

dbms_output.put_line( 'error_code: ' || a_rec.error_code);

dbms_output.put_line( 'error_explanation: ' || a_rec.error_explanation);

END LOOP;

ROLLBACK;

END IF;

END;