Declare
v_organization_id Number := 85; --组织ID
v_transaction_type_id Number := 119; --事务类型ID:Data Conversion Out
v_qty_x Number := -1; --出仓需负数
v_user_id Number := 3551; --用户ID
v_login_id Number := -1; --last_update_login
v_source_code Varchar2(20) := '批量处理'; --来源
v_transaction_reference Varchar2(30) := '批量打柴WP呆滞数据'; --参考
v_end_date Date := to_date('2023/10/01', 'rrrr/mm/dd'); --呆滞期
v_transaction_interface_id Number;
v_transaction_source_id Number;
l_return_status Varchar2(40);
l_msg_count Number;
l_msg_data Varchar2(2000);
l_trans_count Number;
l_request_id Number;
Begin
--获取事务来源ID
Select DISPOSITION_ID
Into v_transaction_source_id
From MTL_GENERIC_DISPOSITIONS
Where SEGMENT1 = '出仓' --事务来源
And ORGANIZATION_ID = v_organization_id;
--循环入接口处理
For r1 In (Select a.INVENTORY_ITEM_ID,
v.concatenated_segments,
v.primary_uom_code,
a.SUBINVENTORY_CODE,
a.LOT_NUMBER,
v_qty_x * Sum(a.TRANSACTION_QUANTITY) qty
From mtl_onhand_quantities_detail a, mtl_system_items_kfv v
Where a.ORGANIZATION_ID = v_organization_id
And a.SUBINVENTORY_CODE Like '%WP01'
And a.ORGANIZATION_ID = v.ORGANIZATION_ID
And a.INVENTORY_ITEM_ID = v.INVENTORY_ITEM_ID
And v.segment2 != 'WI'
And v.segment2 = 'ME'
And a.DATE_RECEIVED < v_end_date
Group By a.INVENTORY_ITEM_ID,
v.concatenated_segments,
v.primary_uom_code,
a.SUBINVENTORY_CODE,
a.LOT_NUMBER
Order By a.INVENTORY_ITEM_ID) Loop
If r1.qty <> 0 Then
--取transaction_interface_id
v_transaction_interface_id := Null;
Begin
Select mtl_material_transactions_s.nextval
Into v_transaction_interface_id
From dual;
Exception
When Others Then
v_transaction_interface_id := Null;
End;
--处理lot no
If r1.LOT_NUMBER Is Not Null Then
Begin
Insert Into mtl_transaction_lots_interface
(transaction_interface_id,
lot_number,
transaction_quantity,
primary_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
Values
(v_transaction_interface_id,
r1.LOT_NUMBER,
r1.qty,
r1.qty,
Sysdate,
v_user_id,
Sysdate,
v_user_id,
v_login_id);
Commit;
Exception
When Others Then
dbms_output.put_line('数据插入批次处理接口失败：' || Sqlerrm);
End;
End If;
Begin
Insert Into mtl_transactions_interface
(process_flag,
organization_id,
inventory_item_id,
transaction_quantity,
transaction_type_id,
subinventory_code,
locator_id,
transaction_date,
transaction_uom,
transaction_interface_id,
transaction_source_id,
source_code,
source_header_id,
source_line_id,
validation_required,
lock_flag,
transaction_mode,
TRANSACTION_REFERENCE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
Values
(1, -- process_flag：'1' for ready, '2' for not ready，3：fails
v_organization_id,
r1.inventory_item_id,
r1.qty,
v_transaction_type_id,
r1.SUBINVENTORY_CODE,
Null,
Sysdate,
r1.primary_uom_code,
v_transaction_interface_id,
v_transaction_source_id,
v_source_code,
-1,
-1,
1, -- validation_required
2, -- lock_flag ('1' for locked, '2' or NULL for not locked
3, -- transaction_mode 3-background 2-immediate
v_transaction_reference,
Sysdate,
v_user_id,
Sysdate,
v_user_id,
v_login_id);
Commit;
Exception
When Others Then
dbms_output.put_line('数据插入事务处理接口失败：' || Sqlerrm);
End;
End If;
--跑接口
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_true,
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 => v_transaction_interface_id);
Commit;
End Loop;
End;
