RMA接收入库 Open Inerface最简单例子 – 直接入库
1、 接收前需要判断订单行状态必须是Awaiting Return。
2、 如果RMA接收路线是标准接收，那么下面例子routing_header_id改为1即可。
3、不需要调用oe_rma_receiving.push_receiving_info，接口跑完状态为Returned。
4、接收号看系统是设置的手工给还是自动给，我这里是manual，所以自定义接收号l_Receipt_Num，如果是automatic，不需要定义。
5、取当前组织的Location_Id传给行的Deliver_To_Location_Id。
-- RMA 接收入库
DECLARE
l_Rowid VARCHAR2(40);
l_Shipment_Header_Id NUMBER;
l_Interface_Transaction_Id NUMBER;
l_Group_Id NUMBER;
l_Receipt_Num VARCHAR2(30) := '4005014';
l_Shipment_Num VARCHAR2(30);
l_Bl_Result BOOLEAN;
g_Iface_Rcv_Rec Po.Rcv_Transactions_Interface%ROWTYPE;
l_Sub_Request_Id NUMBER;
l_Msg_Count NUMBER;
x_Return VARCHAR2(100);
x_Return_Message VARCHAR2(100);
x_Phase VARCHAR2(100);
x_Status VARCHAR2(100);
x_Dev_Phase VARCHAR2(100);
x_Dev_Status VARCHAR2(100);
x_Message VARCHAR2(100);
l_Exists_Error NUMBER;
BEGIN
Fnd_Global.Apps_Initialize(1479
,51014
,190);
SAVEPOINT Point1;
SELECT Rcv_Shipment_Headers_s.Nextval
INTO l_Shipment_Header_Id
FROM Sys.Dual;
Dbms_Output.Put_Line('l_Shipment_Header_Id' || l_Shipment_Header_Id);
SELECT Rcv_Transactions_Interface_s.Nextval
INTO l_Interface_Transaction_Id
FROM Sys.Dual;
SELECT Po.Rcv_Interface_Groups_s.Nextval
INTO l_Group_Id
FROM Sys.Dual;
-- 1) 创建接收头
Rcv_Shipment_Headers_Pkg.Insert_Row(x_Rowid => l_Rowid
,x_Shipment_Header_Id => l_Shipment_Header_Id
,x_Last_Update_Date => SYSDATE
,x_Last_Updated_By => Fnd_Global.User_Id
,x_Creation_Date => SYSDATE
,x_Created_By => Fnd_Global.User_Id
,x_Last_Update_Login => NULL
,x_Receipt_Source_Code => 'CUSTOMER'
,x_Vendor_Id => NULL
,x_Vendor_Site_Id => NULL
,x_Organization_Id => 154
, --oe_order_headers_all.ship_from_org_id
x_Ship_To_Org_Id => 154
, --oe_order_headers_all.ship_from_org_id
x_Shipment_Num => l_Shipment_Num
,x_Receipt_Num => l_Receipt_Num
,x_Ship_To_Location_Id => NULL
,x_Bill_Of_Lading => NULL
,x_Packing_Slip => NULL
,x_Shipped_Date => NULL
,x_Freight_Carrier_Code => NULL
,x_Expected_Receipt_Date => NULL
,x_Employee_Id => 926
, --fnd_user.employee_id
x_Num_Of_Containers => NULL
,x_Waybill_Airbill_Num => NULL
,x_Comments => NULL
,x_Attribute_Category => NULL
,x_Attribute1 => NULL
,x_Attribute2 => NULL
,x_Attribute3 => NULL
,x_Attribute4 => NULL
,x_Attribute5 => NULL
,x_Attribute6 => NULL
,x_Attribute7 => NULL
,x_Attribute8 => NULL
,x_Attribute9 => NULL
,x_Attribute10 => NULL
,x_Attribute11 => NULL
,x_Attribute12 => NULL
,x_Attribute13 => NULL
,x_Attribute14 => NULL
,x_Attribute15 => NULL
,x_Ussgl_Transaction_Code => NULL
,x_Government_Context => NULL
,x_Request_Id => NULL
,x_Program_Application_Id => NULL
,x_Program_Id => NULL
,x_Program_Update_Date => NULL
,x_Customer_Id => 4163
, --oe_order_headers_all.sold_to_org_id
x_Customer_Site_Id => NULL);
-- 2) 创建接收行
SELECT t.Location_Id
INTO g_Iface_Rcv_Rec.Deliver_To_Location_Id
FROM Hr_Organization_Units t
WHERE t.Organization_Id = 154;--ship_from_org_id
SELECT Mcb.Category_Id
INTO g_Iface_Rcv_Rec.Category_Id
FROM Mtl_System_Items_b Msi
,Mtl_Item_Categories Icat
,Mtl_Categories_b Mcb
WHERE Msi.Inventory_Item_Id = Icat.Inventory_Item_Id
AND Msi.Organization_Id = Icat.Organization_Id
AND Icat.Category_Id = Mcb.Category_Id
AND Icat.Category_Set_Id = 1100000041 --类别集
AND Msi.Inventory_Item_Id = 16950
AND Msi.Organization_Id = 90;--主组织
g_Iface_Rcv_Rec.Interface_Transaction_Id := l_Interface_Transaction_Id;
g_Iface_Rcv_Rec.Group_Id := l_Group_Id;
g_Iface_Rcv_Rec.Last_Update_Date := SYSDATE;
g_Iface_Rcv_Rec.Last_Updated_By := Fnd_Global.User_Id;
g_Iface_Rcv_Rec.Creation_Date := SYSDATE;
g_Iface_Rcv_Rec.Created_By := Fnd_Global.User_Id;
g_Iface_Rcv_Rec.Last_Update_Login := Fnd_Global.Login_Id;
g_Iface_Rcv_Rec.Transaction_Type := 'RECEIVE';
g_Iface_Rcv_Rec.Transaction_Date := SYSDATE;
g_Iface_Rcv_Rec.Processing_Status_Code := 'PENDING';
g_Iface_Rcv_Rec.Processing_Mode_Code := 'BATCH';
g_Iface_Rcv_Rec.Transaction_Status_Code := 'PENDING';
g_Iface_Rcv_Rec.Quantity := 10; --接收数量
g_Iface_Rcv_Rec.Unit_Of_Measure := 'PCS'; --单位
g_Iface_Rcv_Rec.Interface_Source_Code := '4005014'; --随便给--退货订单号
g_Iface_Rcv_Rec.Item_Id := 16950; --inventory_item_id
g_Iface_Rcv_Rec.Auto_Transact_Code := 'DELIVER';
g_Iface_Rcv_Rec.Shipment_Header_Id := l_Shipment_Header_Id; --接收头ID
g_Iface_Rcv_Rec.Primary_Quantity := 10;
g_Iface_Rcv_Rec.Primary_Unit_Of_Measure := 'PCS';
g_Iface_Rcv_Rec.Receipt_Source_Code := 'CUSTOMER';
g_Iface_Rcv_Rec.Customer_Id := 4163; --oe_order_headers_all.sold_to_org_id
g_Iface_Rcv_Rec.Customer_Site_Id := 6438; --oe_order_headers_all.ship_to_org_id
g_Iface_Rcv_Rec.To_Organization_Id := 154; --oe_order_headers_all.ship_from_org_id
g_Iface_Rcv_Rec.Routing_Header_Id := 3; --1-标准接收 3-直接 2-检验
g_Iface_Rcv_Rec.Routing_Step_Id := 1;
g_Iface_Rcv_Rec.Source_Document_Code := 'RMA';
g_Iface_Rcv_Rec.Oe_Order_Header_Id := 1109823; --oe_order_headers_all.header_id
g_Iface_Rcv_Rec.Oe_Order_Line_Id := 127002; --oe_order_lines_all.line_id
g_Iface_Rcv_Rec.Currency_Code := 'CNY'; --oe_order_headers_all.transactional_curr_code
g_Iface_Rcv_Rec.Inspection_Status_Code := 'NOT INSPECTED';
g_Iface_Rcv_Rec.Destination_Type_Code := 'INVENTORY';
g_Iface_Rcv_Rec.Expected_Receipt_Date := SYSDATE;
g_Iface_Rcv_Rec.Location_Id := NULL;
g_Iface_Rcv_Rec.Subinventory := 'KCLPK'; --子库存
g_Iface_Rcv_Rec.Locator_Id := NULL; --货位
g_Iface_Rcv_Rec.Destination_Context := 'INVENTORY';
g_Iface_Rcv_Rec.Item_Description := '功能模块-UNIS-RS5M1HYH2692M-2端口16Gb光纤通道HBA卡(带两个SFP+ 模块)(QLE2692)(CMCTO)-HYZF-国内版'; --mtl_system_items_b.description
g_Iface_Rcv_Rec.Comments := NULL;
g_Iface_Rcv_Rec.Currency_Conversion_Type := NULL; --oe_order_headers_all.conversion_type_code
g_Iface_Rcv_Rec.Currency_Conversion_Rate := NULL; --oe_order_headers_all.conversion_rate
g_Iface_Rcv_Rec.Currency_Conversion_Date := NULL; --oe_order_headers_all.conversion_rate_date
g_Iface_Rcv_Rec.Uom_Code := 'PCS';
g_Iface_Rcv_Rec.Item_Revision := NULL;
g_Iface_Rcv_Rec.Employee_Id := 926; --fnd_user.employee_id;
--数据表rcv_transactions
INSERT INTO Po.Rcv_Transactions_Interface
VALUES g_Iface_Rcv_Rec;
COMMIT;
Dbms_Output.Put_Line('INSERT S');
-- 3) 提交接收事务处理
l_Sub_Request_Id := Fnd_Request.Submit_Request('PO'
,'RVCTP'
,NULL
,NULL
,FALSE
,'BATCH'
,l_Group_Id
,153);
Dbms_Output.Put_Line('l_Group_Id:' || l_Group_Id);
Dbms_Output.Put_Line('l_Sub_Request_Id:' || l_Sub_Request_Id);
COMMIT;
-- 4) 等待请求结束
IF l_Sub_Request_Id > 0 THEN
l_Bl_Result := Fnd_Concurrent.Wait_For_Request(l_Sub_Request_Id
,10
,0
,x_Phase
,x_Status
,x_Dev_Phase
,x_Dev_Status
,x_Message);
END IF;
SELECT COUNT(1)
INTO l_Exists_Error
FROM Rcv_Transactions_Interface Rt
WHERE Rt.Group_Id = l_Group_Id
AND Rt.Transaction_Status_Code = 'ERROR';
Dbms_Output.Put_Line('x_Status:' || x_Status);
Dbms_Output.Put_Line('l_Exists_Error:' || l_Exists_Error);
Dbms_Output.Put_Line('x_Message:' || x_Message);
FOR r_Error IN (SELECT Poi.Interface_Transaction_Id Iface_Id
,Poi.Error_Message
FROM Po_Interface_Errors Poi
WHERE Poi.Batch_Id = l_Group_Id) LOOP
Dbms_Output.Put_Line(r_Error.Error_Message);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.Put_Line(SQLERRM || Dbms_Utility.Format_Error_Backtrace);
ROLLBACK TO Point1;
END;
2172
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
