--事务处理
--created by jenrry
DECLARE
l_iface_rec inv.mtl_transactions_interface%ROWTYPE;
l_iface_lot_rec inv.mtl_transaction_lots_interface%ROWTYPE;
l_cur_mfg_org_id NUMBER := 127; --Current Inv Organization
l_user_id NUMBER := 0; --User ID, Sysadmin here
l_outcome BOOLEAN;
l_timeout NUMBER := 100;
l_error_code VARCHAR2(4000);
l_error_explanation VARCHAR2(4000);
BEGIN
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => 51010,
resp_appl_id => 553
);
fnd_profile.put ('AFLOG_LEVEL', '1');
l_iface_rec.last_update_date := SYSDATE;
l_iface_rec.last_updated_by := l_user_id;
l_iface_rec.creation_date := SYSDATE;
l_iface_rec.created_by := l_user_id;
l_iface_rec.last_update_login := -1;
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;
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 := 35; --mtl_transaction_types
l_iface_rec.transaction_source_id := 408953; --wip_entity_id
l_iface_rec.wip_entity_type := 10;
--
l_iface_rec.organization_id := l_cur_mfg_org_id;
l_iface_rec.inventory_item_id := 623;
l_iface_rec.subinventory_code := 'Y02';
l_iface_rec.transaction_quantity := -1;
l_iface_rec.primary_quantity := -1;
l_iface_rec.transaction_uom := '个';
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;
l_iface_rec.locator_id := 3;
INSERT INTO inv.mtl_transactions_interface VALUES l_iface_rec;
l_iface_lot_rec.transaction_interface_id := l_iface_rec.transaction_interface_id;
l_iface_lot_rec.lot_number := '201308-001';
l_iface_lot_rec.transaction_quantity := l_iface_rec.transaction_quantity;
l_iface_lot_rec.source_code := l_iface_rec.source_code;
l_iface_lot_rec.source_line_id := l_iface_rec.source_line_id;
INSERT INTO inv.mtl_transaction_lots_interface VALUES l_iface_lot_rec;
l_timeout := 100;
l_outcome := mtl_online_transaction_pub.process_online(p_transaction_header_id => l_iface_rec.transaction_header_id,
p_timeout => l_timeout,
p_error_code => l_error_code,
p_error_explanation => l_error_explanation);
IF (l_outcome = FALSE) THEN
dbms_output.put_line('Failed to process the transaction');
dbms_output.put_line('Error code: ' || l_error_code);
dbms_output.put_line('Error message: ' || l_error_explanation);
DELETE inv.mtl_transactions_interface
WHERE transaction_header_id = l_iface_rec.transaction_header_id;
COMMIT;
ELSE
dbms_output.put_line('Transaction with header id ' ||
to_char(l_iface_rec.transaction_header_id) ||
' has been processed successfully');
COMMIT;
END IF;
END;