ORACLE EBS 销售订单发运全流程及API详解（上）
2023-12-28 17:52:34
一 创建订单
1.1创建订单及登记订单（界面）
填写订单头和订单行信息，保存后登记订单
EBS创建订单涉及表：
oe_order_headers_all， oe_order_lines_all
当订单头的FLOW_STATUS_CODE 状态为Enter(已输入)时，
订单行中的FLOW_STATUS_CODE状态也为Enter。
1.2导入订单（API）
1.如果需要导入期初价格的订单，且无修改量及运费计算的，则设置行上的Calculate_Price_Flag=‘N’
2.如果导入已登记订单，一般不建议通过头的booked_flag的标记去导入，结果会只是行状态为已登记状态，但是头的状态为已输入状态。
DECLARE
x_Header_Rec Oe_Order_Pub.Header_Rec_Type;
x_Header_Val_Rec Oe_Order_Pub.Header_Val_Rec_Type;
x_Header_Adj_Tbl Oe_Order_Pub.Header_Adj_Tbl_Type;
x_Header_Adj_Val_Tbl Oe_Order_Pub.Header_Adj_Val_Tbl_Type;
x_Header_Price_Att_Tbl Oe_Order_Pub.Header_Price_Att_Tbl_Type;
x_Header_Adj_Att_Tbl Oe_Order_Pub.Header_Adj_Att_Tbl_Type;
x_Header_Adj_Assoc_Tbl Oe_Order_Pub.Header_Adj_Assoc_Tbl_Type;
x_Header_Scredit_Tbl Oe_Order_Pub.Header_Scredit_Tbl_Type;
x_Header_Scredit_Val_Tbl Oe_Order_Pub.Header_Scredit_Val_Tbl_Type;
x_Line_Tbl Oe_Order_Pub.Line_Tbl_Type;
x_Line_Val_Tbl Oe_Order_Pub.Line_Val_Tbl_Type;
x_Line_Adj_Tbl Oe_Order_Pub.Line_Adj_Tbl_Type;
x_Line_Adj_Val_Tbl Oe_Order_Pub.Line_Adj_Val_Tbl_Type;
x_Line_Price_Att_Tbl Oe_Order_Pub.Line_Price_Att_Tbl_Type;
x_Line_Adj_Att_Tbl Oe_Order_Pub.Line_Adj_Att_Tbl_Type;
x_Line_Adj_Assoc_Tbl Oe_Order_Pub.Line_Adj_Assoc_Tbl_Type;
x_Line_Scredit_Tbl Oe_Order_Pub.Line_Scredit_Tbl_Type;
x_Line_Scredit_Val_Tbl Oe_Order_Pub.Line_Scredit_Val_Tbl_Type;
x_Lot_Serial_Tbl Oe_Order_Pub.Lot_Serial_Tbl_Type;
x_Lot_Serial_Val_Tbl Oe_Order_Pub.Lot_Serial_Val_Tbl_Type;
x_Action_Request_Tbl Oe_Order_Pub.Request_Tbl_Type;
i NUMBER := 1;
l_Header_Rec Oe_Order_Pub.Header_Rec_Type;
l_Line_Tbl Oe_Order_Pub.Line_Tbl_Type;
l_Action_Request_Tbl Oe_Order_Pub.Request_Tbl_Type;
x_Return_Status VARCHAR2(1);
x_Msg_Count NUMBER;
x_Msg_Data VARCHAR2(255);
BEGIN
Fnd_Global.Apps_Initialize(User_Id => 0
,Resp_Id => 50877
,Resp_Appl_Id => 190);
Mo_Global.Init('CUX');
Oe_Msg_Pub.Initialize;
l_Header_Rec := Oe_Order_Pub.g_Miss_Header_Rec;
l_Header_Rec.Operation := Oe_Globals.g_Opr_Create;
l_Header_Rec.Org_Id := &Org_Id;
l_Header_Rec.Sold_To_Org_Id := &Customer_Id;
l_Header_Rec.Order_Type_Id := &Order_Type_Id;
l_Line_Tbl.Delete;
l_Line_Tbl(i) := Oe_Order_Pub.g_Miss_Line_Rec;
l_Line_Tbl(i).Operation := Oe_Globals.g_Opr_Create;
l_Line_Tbl(i).Inventory_Item_Id := &Inventory_Item_Id;
l_Line_Tbl(i).Ordered_Quantity := &Quantity;
l_Line_Tbl(i).Unit_Selling_Price := &Unit_Price;
l_Line_Tbl(i).Unit_List_Price := &Unit_Price;
l_Line_Tbl(i).Calculate_Price_Flag := 'N'; --如果没使用修改量或者运费作为价格,通过API导入价格必须导入冻结的价格
Oe_Order_Pub.Process_Order(p_Org_Id => &Org_Id
,p_Api_Version_Number => 1.0
,p_Init_Msg_List => NULL
,p_Return_Values => NULL
,p_Header_Rec => l_Header_Rec
,p_Action_Request_Tbl => l_Action_Request_Tbl
,p_Line_Tbl => l_Line_Tbl
,x_Header_Rec => x_Header_Rec
,x_Header_Val_Rec => x_Header_Val_Rec
,x_Header_Adj_Tbl => x_Header_Adj_Tbl
,x_Header_Adj_Val_Tbl => x_Header_Adj_Val_Tbl
,x_Header_Price_Att_Tbl => x_Header_Price_Att_Tbl
,x_Header_Adj_Att_Tbl => x_Header_Adj_Att_Tbl
,x_Header_Adj_Assoc_Tbl => x_Header_Adj_Assoc_Tbl
,x_Header_Scredit_Tbl => x_Header_Scredit_Tbl
,x_Header_Scredit_Val_Tbl => x_Header_Scredit_Val_Tbl
,x_Line_Tbl => x_Line_Tbl
,x_Line_Val_Tbl => x_Line_Val_Tbl
,x_Line_Adj_Tbl => x_Line_Adj_Tbl
,x_Line_Adj_Val_Tbl => x_Line_Adj_Val_Tbl
,x_Line_Price_Att_Tbl => x_Line_Price_Att_Tbl
,x_Line_Adj_Att_Tbl => x_Line_Adj_Att_Tbl
,x_Line_Adj_Assoc_Tbl => x_Line_Adj_Assoc_Tbl
,x_Line_Scredit_Tbl => x_Line_Scredit_Tbl
,x_Line_Scredit_Val_Tbl => x_Line_Scredit_Val_Tbl
,x_Lot_Serial_Tbl => x_Lot_Serial_Tbl
,x_Lot_Serial_Val_Tbl => x_Lot_Serial_Val_Tbl
,x_Action_Request_Tbl => x_Action_Request_Tbl
,x_Return_Status => x_Return_Status
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data);
IF x_Return_Status = Fnd_Api.g_Ret_Sts_Success THEN
Dbms_Output.Put_Line('订单导入成功!');
Dbms_Output.Put_Line('订单编号:' || x_Header_Rec.Order_Number);
ELSE
FOR l_Index IN 1 .. x_Msg_Count LOOP
Dbms_Output.Put_Line(Oe_Msg_Pub.Get(p_Msg_Index => l_Index
,p_Encoded => 'F'));
END LOOP;
END IF;
END;
1.3登记订单（API）
登记后订单头的FLOW_STATUS_CODE 状态为BOOKED，
订单行中的FLOW_STATUS_CODE状态为AWAITING_SHIPPING。
DECLARE
x_Header_Rec Oe_Order_Pub.Header_Rec_Type;
x_Header_Val_Rec Oe_Order_Pub.Header_Val_Rec_Type;
x_Header_Adj_Tbl Oe_Order_Pub.Header_Adj_Tbl_Type;
x_Header_Adj_Val_Tbl Oe_Order_Pub.Header_Adj_Val_Tbl_Type;
x_Header_Price_Att_Tbl Oe_Order_Pub.Header_Price_Att_Tbl_Type;
x_Header_Adj_Att_Tbl Oe_Order_Pub.Header_Adj_Att_Tbl_Type;
x_Header_Adj_Assoc_Tbl Oe_Order_Pub.Header_Adj_Assoc_Tbl_Type;
x_Header_Scredit_Tbl Oe_Order_Pub.Header_Scredit_Tbl_Type;
x_Header_Scredit_Val_Tbl Oe_Order_Pub.Header_Scredit_Val_Tbl_Type;
x_Line_Tbl Oe_Order_Pub.Line_Tbl_Type;
x_Line_Val_Tbl Oe_Order_Pub.Line_Val_Tbl_Type;
x_Line_Adj_Tbl Oe_Order_Pub.Line_Adj_Tbl_Type;
x_Line_Adj_Val_Tbl Oe_Order_Pub.Line_Adj_Val_Tbl_Type;
x_Line_Price_Att_Tbl Oe_Order_Pub.Line_Price_Att_Tbl_Type;
x_Line_Adj_Att_Tbl Oe_Order_Pub.Line_Adj_Att_Tbl_Type;
x_Line_Adj_Assoc_Tbl Oe_Order_Pub.Line_Adj_Assoc_Tbl_Type;
x_Line_Scredit_Tbl Oe_Order_Pub.Line_Scredit_Tbl_Type;
x_Line_Scredit_Val_Tbl Oe_Order_Pub.Line_Scredit_Val_Tbl_Type;
x_Lot_Serial_Tbl Oe_Order_Pub.Lot_Serial_Tbl_Type;
x_Lot_Serial_Val_Tbl Oe_Order_Pub.Lot_Serial_Val_Tbl_Type;
x_Action_Request_Tbl Oe_Order_Pub.Request_Tbl_Type;
i NUMBER := 1;
l_Header_Rec Oe_Order_Pub.Header_Rec_Type;
l_Line_Tbl Oe_Order_Pub.Line_Tbl_Type;
l_Action_Request_Tbl Oe_Order_Pub.Request_Tbl_Type;
x_Return_Status VARCHAR2(1);
x_Msg_Count NUMBER;
x_Msg_Data VARCHAR2(255);
BEGIN
Fnd_Global.Apps_Initialize(User_Id => 0
,Resp_Id => 50877
,Resp_Appl_Id => 190);
Mo_Global.Init('CUX'); --必须初始化MOAC，否则无法完成OU验证
Mo_Global.Set_Policy_Context('S'
,88);
Oe_Msg_Pub.Initialize;
i := 1;
l_Action_Request_Tbl(i).Request_Type := Oe_Globals.g_Book_Order;
l_Action_Request_Tbl(i).Entity_Code := Oe_Globals.g_Entity_Header;
l_Action_Request_Tbl(i).Entity_Id := &Header_Id; /*如果有多个订单增加record记录
i := i+1; l_action_request_tbl(i).request_type := oe_globals.g_book_order; l_action_request_tbl(i).entity_code := oe_globals.g_entity_header; l_action_request_tbl(i).entity_id := &header_id1;*/
Oe_Order_Pub.Process_Order(p_Api_Version_Number => 1.0
,p_Init_Msg_List => NULL
,p_Return_Values => NULL
,p_Action_Request_Tbl => l_Action_Request_Tbl
--out
,x_Header_Rec => x_Header_Rec
,x_Header_Val_Rec => x_Header_Val_Rec
,x_Header_Adj_Tbl => x_Header_Adj_Tbl
,x_Header_Adj_Val_Tbl => x_Header_Adj_Val_Tbl
,x_Header_Price_Att_Tbl => x_Header_Price_Att_Tbl
,x_Header_Adj_Att_Tbl => x_Header_Adj_Att_Tbl
,x_Header_Adj_Assoc_Tbl => x_Header_Adj_Assoc_Tbl
,x_Header_Scredit_Tbl => x_Header_Scredit_Tbl
,x_Header_Scredit_Val_Tbl => x_Header_Scredit_Val_Tbl
,x_Line_Tbl => x_Line_Tbl
,x_Line_Val_Tbl => x_Line_Val_Tbl
,x_Line_Adj_Tbl => x_Line_Adj_Tbl
,x_Line_Adj_Val_Tbl => x_Line_Adj_Val_Tbl
,x_Line_Price_Att_Tbl => x_Line_Price_Att_Tbl
,x_Line_Adj_Att_Tbl => x_Line_Adj_Att_Tbl
,x_Line_Adj_Assoc_Tbl => x_Line_Adj_Assoc_Tbl
,x_Line_Scredit_Tbl => x_Line_Scredit_Tbl
,x_Line_Scredit_Val_Tbl => x_Line_Scredit_Val_Tbl
,x_Lot_Serial_Tbl => x_Lot_Serial_Tbl
,x_Lot_Serial_Val_Tbl => x_Lot_Serial_Val_Tbl
,x_Action_Request_Tbl => x_Action_Request_Tbl
,x_Return_Status => x_Return_Status
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data);
IF (x_Msg_Count > 0) THEN
--这里不能按x_return_status的状态判断
FOR l_Index IN 1 .. x_Msg_Count LOOP
Dbms_Output.Put_Line(Oe_Msg_Pub.Get(p_Msg_Index => l_Index
,p_Encoded => 'F'));
END LOOP;
ELSE
Dbms_Output.Put_Line('登记成功!');
END IF;
END;
1.4必要表信息1
二 挑库发放
订单登记后，去发运事务处理界面查找订单，会发现发运单的行状态为
准备发放，下一步为挑库发放。
2.1必要表信息2
SELECT Ooh.Flow_Status_Code
,Ool.Flow_Status_Code
,Ool.Line_Id
,Wdd.Released_Status
,Wdd.Delivery_Detail_Id
--,wdd.*
FROM Oe_Order_Headers_All Ooh
,Oe_Order_Lines_All Ool
,Wsh_Delivery_Details Wdd --物料发运明细信息表
,Wsh_Delivery_Assignments Wda --发运交货分配表（中间表）
WHERE 1 = 1
AND Ooh.Header_Id = Ool.Header_Id
AND Wda.Delivery_Detail_Id = Wdd.Delivery_Detail_Id
AND Wdd.Source_Line_Id = Ool.Line_Id
AND Ooh.Order_Number = '88888';
2.2挑库发放（界面）
在挑库参数里面有三个参数通常情况上是我们比较关注的值
1自动创建交货 2自动确认挑库 3自动分配
为了一步一步演示，前两个参数这里我们都选择否，若选择是 后面步骤可省略。
2.3挑库发放（API)
挑库发放可以细分为2个步骤，第一创建挑库批次，第二启用挑库
--创建批并挑库发放
DECLARE
l_Line_Rows Wsh_Util_Core.Id_Tab_Type;
l_Del_Rows Wsh_Util_Core.Id_Tab_Type;
l_Delivery_Id NUMBER;
l_Batch_Info_Rec Wsh_Picking_Batches_Pub.Batch_Info_Rec;
l_New_Batch_Id NUMBER;
l_Request_Id NUMBER;
l_Count NUMBER := 0;
x_Error_Flag VARCHAR2(100);
x_Error_Msg VARCHAR2(5000);
x_Return_Status VARCHAR2(4);
x_Msg_Data VARCHAR2(5000);
x_Msg_Count VARCHAR2(5000);
l_Error_Msg VARCHAR2(5000);
l_Index_Out NUMBER;
CURSOR Csr_Line IS
SELECT Oola.Inventory_Item_Id
,Oola.Line_Id
,Oola.Ordered_Quantity
,Oola.Header_Id
,Oola.Sold_To_Org_Id
,Oola.Ship_From_Org_Id
,Wdd.Delivery_Detail_Id
,Wdd.Requested_Quantity
,Wdd.Requested_Quantity_Uom
,Substr(Msib.Segment1
,1
,4) Item_Code
FROM Oe_Order_Lines_All Oola
,Wsh_Delivery_Details Wdd
,Mtl_System_Items_b Msib
WHERE Oola.Line_Id = Wdd.Source_Line_Id(+)
AND Oola.Header_Id = 1122922
AND Msib.Organization_Id = 90
AND Msib.Inventory_Item_Id = Oola.Inventory_Item_Id
AND Oola.Flow_Status_Code = 'AWAITING_SHIPPING'
AND Wdd.Source_Code(+) = 'OE';
BEGIN
FOR Rec_Line IN Csr_Line LOOP
l_Count := l_Count + 1;
Dbms_Output.Put_Line('Rec_Line.Delivery_Detail_Id:' ||
Rec_Line.Delivery_Detail_Id);
l_Line_Rows(l_Count) := Rec_Line.Delivery_Detail_Id;
l_Batch_Info_Rec.Existing_Rsvs_Only_Flag := 'N';
l_Batch_Info_Rec.Customer_Id := Rec_Line.Sold_To_Org_Id;
l_Batch_Info_Rec.Order_Header_Id := Rec_Line.Header_Id;
l_Batch_Info_Rec.Organization_Id := Rec_Line.Ship_From_Org_Id;
l_Batch_Info_Rec.Delivery_Detail_Id := Rec_Line.Delivery_Detail_Id;
l_Batch_Info_Rec.Include_Planned_Lines := 'N'; --包括分配行
l_Batch_Info_Rec.Autocreate_Delivery_Flag := 'N'; --自动创建交货
l_Batch_Info_Rec.Autodetail_Pr_Flag := 'Y'; --自动分配标识
l_Batch_Info_Rec.Allocation_Method := 'I'; --分配方法 I--仅限于库存
l_Batch_Info_Rec.Auto_Pick_Confirm_Flag := 'N'; --自动确认挑库标识
l_Batch_Info_Rec.Autopack_Flag := 'N'; --自动包装标识
l_Batch_Info_Rec.Pick_From_Subinventory := 'KCLPK'; --挑库来源子库存
l_Batch_Info_Rec.Default_Stage_Subinventory := 'STAGE'; ---临时提货区子库存
Wsh_Picking_Batches_Pub.Create_Batch(p_Api_Version => 1.0
,p_Commit => Fnd_Api.g_False
,p_Init_Msg_List => Fnd_Api.g_False
,x_Return_Status => x_Return_Status
,x_Msg_Data => x_Msg_Data
,x_Msg_Count => x_Msg_Count
,p_Batch_Rec => l_Batch_Info_Rec
,x_Batch_Id => l_New_Batch_Id
,p_Rule_Id => NULL
,p_Rule_Name => NULL
,p_Batch_Prefix => NULL);
Dbms_Output.Put_Line('Create Batch:' || x_Return_Status ||
l_New_Batch_Id);
IF x_Return_Status <> Fnd_Api.g_Ret_Sts_Success THEN
x_Error_Flag := 'E';
Fnd_Msg_Pub.Count_And_Get(p_Count => x_Msg_Count
,p_Data => x_Msg_Data);
l_Error_Msg := 'Create Batch Error:' || x_Msg_Data;
FOR Iindx IN 1 .. x_Msg_Count LOOP
Fnd_Msg_Pub.Get(p_Msg_Index => Iindx
,p_Encoded => 'F'
,p_Data => x_Msg_Data
,p_Msg_Index_Out => l_Index_Out);
l_Error_Msg := Substr(l_Error_Msg || ' ' || x_Msg_Data
,1
,1000);
Dbms_Output.Put_Line(l_Error_Msg);
END LOOP;
x_Error_Flag := 'E';
x_Error_Msg := l_Error_Msg;
x_Return_Status := Fnd_Api.g_Ret_Sts_Success;
ELSE
Dbms_Output.Put_Line('RELEASE_BATCH');
Wsh_Picking_Batches_Pub.Release_Batch(p_Api_Version => 1.0
,p_Init_Msg_List => Fnd_Api.g_True
,p_Commit => Fnd_Api.g_False
,x_Return_Status => x_Return_Status
,x_Msg_Data => x_Msg_Data
,x_Msg_Count => x_Msg_Count
,p_Batch_Id => l_New_Batch_Id
,p_Batch_Name => NULL
,p_Log_Level => NULL
,p_Release_Mode => 'ONLINE'
,x_Request_Id => l_Request_Id);
Dbms_Output.Put_Line('Release_Batch:' || x_Return_Status ||
l_Request_Id);
IF x_Return_Status <> Fnd_Api.g_Ret_Sts_Success THEN
x_Error_Flag := 'E';
Fnd_Msg_Pub.Count_And_Get(p_Count => x_Msg_Count
,p_Data => x_Msg_Data);
l_Error_Msg := 'Release Batch Error:' || x_Msg_Data;
FOR Iindx IN 1 .. x_Msg_Count LOOP
Fnd_Msg_Pub.Get(p_Msg_Index => Iindx
,p_Encoded => 'F'
,p_Data => x_Msg_Data
,p_Msg_Index_Out => l_Index_Out);
Dbms_Output.Put_Line('x_Msg_Data:' || x_Msg_Data);
l_Error_Msg := Substr(l_Error_Msg || ' ' || x_Msg_Data
,1
,1000);
END LOOP;
x_Error_Flag := 'E';
x_Error_Msg := l_Error_Msg;
x_Return_Status := Fnd_Api.g_Ret_Sts_Success;
Dbms_Output.Put_Line(x_Error_Msg);
END IF;
END IF;
END LOOP;
END;
结束后状态为‘已发放至仓库’ 下一步为 处理‘物料搬运单’，且产生了一张物料搬运单供后续的挑库使用。
2.4必要表信息3.1
发放完成后，wdd表Released_Status由R变为S,且生成物料搬运但行id，Move_Order_Line_Id
2.4必要表信息3.2
SELECT Wpb.Batch_Id
,Wpb.Name
,Wpb.Backorders_Only_Flag --订单状态 'I'--全部 'M'-- 已完全补充 'O'--已延交 'E'--未发放
,Wpb.Auto_Pick_Confirm_Flag --自动确认挑库标识
,Wpb.Autocreate_Delivery_Flag --自动创建交货
,Wpb.Autodetail_Pr_Flag --自动分配标识
,Wpb.Allocation_Method --分配方法 I--仅限于库存
,Wpb.Include_Planned_Lines --包括分配行
,Wpb.Autopack_Flag --自动包装标识
FROM Wsh_Picking_Batches Wpb
,Oe_Order_Headers_All Ooh
WHERE Wpb.Order_Header_Id = Ooh.Header_Id
AND Ooh.Order_Number = '88888';
三 挑库确认
本质就是处理物料搬运单，把产品从一个仓库转移到需要出货的仓库。
3.1处理物料搬运单（界面）
【注意】点分配，保存，但未点处理之前，其实数据先写到临时表MTL_MATERIAL_TRANSACTIONS_TEMP 。
MTL_MATERIAL_TRANSACTIONS表不会有数据，因为实际产品库存还没有转移，等点了处理后，才会在 MTL_MATERIAL_TRANSACTIONS有记录。
3.2分配物料搬运单（API）
DECLARE
x_Return_Status VARCHAR2(2);
x_Msg_Count NUMBER := 0;
x_Msg_Data VARCHAR2(255); -- for detail
l_Move_Order_Type Mtl_Txn_Request_Headers.Move_Order_Type%TYPE := 3;
x_Detailed_Qty NUMBER := 5;
x_Number_Of_Rows NUMBER := 0;
x_Revision VARCHAR2(3);
x_Locator_Id NUMBER := 0;
x_Transfer_To_Location NUMBER := 0;
x_Lot_Number VARCHAR2(30);
x_Expiration_Date DATE;
x_Transaction_Temp_Id NUMBER := 0;
CURSOR Csr_Mo_Line IS
SELECT Tl.Line_Id Mo_Line_Id
,Tl.Quantity
FROM Wsh_Delivery_Details Wdd
,Mtl_Txn_Request_Lines Tl
,Mtl_Txn_Request_Headers Th
WHERE Wdd.Released_Status = 'S'
AND Wdd.Move_Order_Line_Id = Tl.Line_Id
AND Nvl(Tl.Quantity_Detailed
,0) < Tl.Quantity
AND Wdd.Source_Header_Id = &Oe_Header_Id
AND Tl.Header_Id = Th.Header_Id
GROUP BY Tl.Line_Id
,Tl.Quantity
ORDER BY Tl.Line_Id;
BEGIN
Fnd_Global.Apps_Initialize(User_Id => 0
,Resp_Id => 50877
,Resp_Appl_Id => 20004); -- Allocate each line of the Move Order
FOR Rec_Line IN Csr_Mo_Line LOOP
Inv_Replenish_Detail_Pub.Line_Details_Pub(p_Line_Id => Rec_Line.Mo_Line_Id
,x_Number_Of_Rows => x_Number_Of_Rows
,x_Detailed_Qty => x_Detailed_Qty
,x_Return_Status => x_Return_Status
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data
,x_Revision => x_Revision
,x_Locator_Id => x_Locator_Id
,x_Transfer_To_Location => x_Transfer_To_Location
,x_Lot_Number => x_Lot_Number
,x_Expiration_Date => x_Expiration_Date
,x_Transaction_Temp_Id => x_Transaction_Temp_Id
,p_Transaction_Header_Id => NULL
,p_Transaction_Mode => NULL
,p_Move_Order_Type => l_Move_Order_Type
,p_Serial_Flag => Fnd_Api.g_False
,p_Plan_Tasks => FALSE
,p_Auto_Pick_Confirm => FALSE
,p_Commit => FALSE --FND_API.G_FALSE
);
Dbms_Output.Put_Line('==========================================================');
Dbms_Output.Put_Line('Return Status: ' || x_Return_Status);
IF (x_Return_Status <> Fnd_Api.g_Ret_Sts_Success) THEN
Dbms_Output.Put_Line('Error Message :' || x_Msg_Data);
FOR i IN 1 .. Fnd_Msg_Pub.Count_Msg LOOP
Dbms_Output.Put_Line(Fnd_Msg_Pub.Get(i
,'F'));
END LOOP;
ELSE
IF x_Number_Of_Rows = 0
OR Nvl(x_Detailed_Qty
,0) < Rec_Line.Quantity THEN
Dbms_Output.Put_Line('not enough onhand quantity!');
END IF;
END IF;
Dbms_Output.Put_Line('==========================================================');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.Put_Line('Exception Occured :');
Dbms_Output.Put_Line(SQLCODE || ':' || SQLERRM);
Dbms_Output.Put_Line('=======================================================');
END;
3.2.1必要表信息4.1
SELECT Mmt.Trx_Source_Line_Id
,Mmt.*
FROM Mtl_Material_Transactions_Temp Mmt --分配后数据存入temp表
,Mtl_Sales_Orders Mso
WHERE 1 = 1
AND Mmt.Transaction_Source_Id = Mso.Sales_Order_Id
AND Mso.Segment1 = '1005188'--订单号
3.3处理物料搬运单（API）
--处理物料搬运单行
DECLARE
-- Common Declarations
l_Api_Version NUMBER := 1.0;
l_Init_Msg_List VARCHAR2(2) := Fnd_Api.g_True;
l_Commit VARCHAR2(2) := Fnd_Api.g_False;
x_Return_Status VARCHAR2(2);
x_Msg_Count NUMBER := 0;
x_Msg_Data VARCHAR2(255); -- API specific declarations
l_Move_Order_Type NUMBER := 1;
l_Transaction_Mode NUMBER := 1;
l_Trolin_Tbl Inv_Move_Order_Pub.Trolin_Tbl_Type;
l_Mold_Tbl Inv_Mo_Line_Detail_Util.g_Mmtt_Tbl_Type;
x_Mmtt_Tbl Inv_Mo_Line_Detail_Util.g_Mmtt_Tbl_Type;
x_Trolin_Tbl Inv_Move_Order_Pub.Trolin_Tbl_Type;
l_Transaction_Date DATE := SYSDATE;
l_User_Name VARCHAR2(30) := 'MFG';
l_Resp_Name VARCHAR2(30) := 'MFG_AND_DIST_SUPER_USER_APS';
--处理物料搬运单
CURSOR Cur_Moveorder IS
SELECT Mth.Header_Id
,Mtl.Line_Id
,Mth.Move_Order_Type
,Wdd.Delivery_Detail_Id
FROM Wsh_Delivery_Details Wdd
,Mtl_Txn_Request_Lines Mtl
,Mtl_Txn_Request_Headers Mth
WHERE Mtl.Line_Id = Wdd.Move_Order_Line_Id
AND Mtl.Header_Id = Mth.Header_Id
AND Wdd.Source_Header_Id = 1122922;
BEGIN
Fnd_Global.Apps_Initialize(User_Id => 0
,Resp_Id => 51014
,Resp_Appl_Id => 190);
--l_Trolin_Tbl(1).Line_Id := &Mo_Line_Id; -- call API to create move order header
FOR Rec_Line IN Cur_Moveorder LOOP
IF Rec_Line.Line_Id IS NOT NULL THEN
l_Trolin_Tbl := Inv_Trolin_Util.Query_Rows(p_Line_Id => Rec_Line.Line_Id);
l_Mold_Tbl := Inv_Mo_Line_Detail_Util.Query_Rows(p_Line_Id => Rec_Line.Line_Id);
Dbms_Output.Put_Line('=======================================================');
Dbms_Output.Put_Line('Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API');
Inv_Pick_Wave_Pick_Confirm_Pub.Pick_Confirm(p_Api_Version_Number => l_Api_Version
,p_Init_Msg_List => l_Init_Msg_List
,p_Commit => l_Commit
,x_Return_Status => x_Return_Status
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data
,p_Move_Order_Type => l_Move_Order_Type
,p_Transaction_Mode => l_Transaction_Mode
,p_Trolin_Tbl => l_Trolin_Tbl
,p_Mold_Tbl => l_Mold_Tbl
,x_Mmtt_Tbl => x_Mmtt_Tbl
,x_Trolin_Tbl => x_Trolin_Tbl
,p_Transaction_Date => l_Transaction_Date);
Dbms_Output.Put_Line('=======================================================');
Dbms_Output.Put_Line('Return Status: ' || x_Return_Status);
IF (x_Return_Status <> Fnd_Api.g_Ret_Sts_Success) THEN
Dbms_Output.Put_Line('Error Message :' || x_Msg_Data);
END IF;
END IF;
END LOOP;
Dbms_Output.Put_Line('=======================================================');
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.Put_Line('Exception Occured :');
Dbms_Output.Put_Line(SQLCODE || ':' || SQLERRM);
Dbms_Output.Put_Line('=======================================================');
END;
3.3.1必要表信息4.2
挑库完成后生成事务处理信息
SELECT Mmt.Transaction_Id
,Mmt.Transaction_Type_Id
,Mmt.Transaction_Quantity
,Moq.Transaction_Quantity
,mmt.*
FROM Mtl_Material_Transactions Mmt
,Mtl_Sales_Orders Mso
,Mtl_Onhand_Quantities Moq
WHERE 1 = 1
AND Mso.Sales_Order_Id = Mmt.Transaction_Source_Id --挑库后才会写入
AND Mmt.Transaction_Id = Moq.Create_Transaction_Id
AND Mso.Segment1 = '1005188' --订单号
3.3.2必要表信息4.3
wdd表状态变为Y（Staged）
3.4创建保留（页面）
处理完物料搬运单后系统会自动在中转库创建保留
（在页面处理和调用api处理都会自动创建，就不需要在程序中再调用api创建保留了）
3.5创建保留（API)
PROCEDURE Create_Reservation(p_Init_Msg_List IN VARCHAR2
,x_Return_Status OUT NOCOPY VARCHAR2
,x_Msg_Count OUT NOCOPY NUMBER
,x_Msg_Data OUT NOCOPY VARCHAR2
,p_Rsv_Rec Inv_Reservation_Global.Mtl_Reservation_Rec_Type) IS
l_Sub_Program VARCHAR2(100) := 'create_reservation';
l_Process VARCHAR2(4000);
l_Api_Name CONSTANT VARCHAR2(30) := 'create_reservation';
l_Savepoint_Name CONSTANT VARCHAR2(30) := 'create_reservation01';
l_Rsv_Rec Inv_Reservation_Global.Mtl_Reservation_Rec_Type;
l_Serial_Number Inv_Reservation_Global.Serial_Number_Tbl_Type;
x_Reservation_Id NUMBER;
l_Partial_Reservation_Flag VARCHAR2(1);
x_Quantity_Reserved NUMBER;
BEGIN
x_Return_Status := Cux_Api.Start_Activity(p_Pkg_Name => g_Pkg_Name
,p_Api_Name => l_Api_Name
,p_Savepoint_Name => l_Savepoint_Name
,p_Init_Msg_List => p_Init_Msg_List);
IF x_Return_Status = Fnd_Api.g_Ret_Sts_Error THEN
RAISE Fnd_Api.g_Exc_Error;
ELSIF x_Return_Status = Fnd_Api.g_Ret_Sts_Unexp_Error THEN
RAISE Fnd_Api.g_Exc_Unexpected_Error;
END IF;
l_Process := l_Sub_Program || 'Step:1 .开始创建INV保留.';
IF g_Debug = 'Y' THEN
Cux_Conc_Utl.Log_Msg(p_Msg => l_Process);
END IF;
l_Rsv_Rec := p_Rsv_Rec; --create reservation
BEGIN
Inv_Reservation_Pub.Create_Reservation(p_Api_Version_Number => 1.0
,p_Init_Msg_Lst => Fnd_Api.g_False
,x_Return_Status => x_Return_Status
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data
,p_Rsv_Rec => l_Rsv_Rec
,p_Serial_Number => l_Serial_Number
,x_Serial_Number => l_Serial_Number
,p_Partial_Reservation_Flag => l_Partial_Reservation_Flag
,p_Force_Reservation_Flag => Fnd_Api.g_False
,p_Validation_Flag => Fnd_Api.g_True
,x_Quantity_Reserved => x_Quantity_Reserved
,x_Reservation_Id => x_Reservation_Id);
IF x_Return_Status <> Fnd_Api.g_Ret_Sts_Success THEN
Raise_Exception(x_Return_Status);
END IF;
EXCEPTION
WHEN OTHERS THEN
Fnd_Msg_Pub.Count_And_Get(p_Encoded => Fnd_Api.g_False
,p_Count => x_Msg_Count
,p_Data => x_Msg_Data);
IF x_Msg_Count > 1 THEN
x_Msg_Data := Fnd_Msg_Pub.Get_Detail(p_Msg_Index => Fnd_Msg_Pub.g_First
,p_Encoded => Fnd_Api.g_False);
END IF;
x_Return_Status := Fnd_Api.g_Ret_Sts_Error;
x_Msg_Data := '创建保留出错.' || x_Msg_Data;
Init_Message(x_Msg_Data);
Raise_Exception(x_Return_Status);
END;
l_Process := l_Sub_Program || 'Step:2 .结束创建订单保留.';
IF g_Debug = 'Y' THEN
Cux_Conc_Utl.Log_Msg(p_Msg => l_Process);
END IF;
x_Return_Status := Cux_Api.End_Activity(p_Pkg_Name => g_Pkg_Name
,p_Api_Name => l_Api_Name
,p_Commit => Fnd_Api.g_True
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data);
EXCEPTION
WHEN Fnd_Api.g_Exc_Error THEN
x_Return_Status := Cux_Api.Handle_Exceptions(p_Pkg_Name => g_Pkg_Name
,p_Api_Name => l_Api_Name
,p_Savepoint_Name => l_Savepoint_Name
,p_Exc_Name => Cux_Api.g_Exc_Name_Error
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data);
WHEN Fnd_Api.g_Exc_Unexpected_Error THEN
x_Return_Status := Cux_Api.Handle_Exceptions(p_Pkg_Name => g_Pkg_Name
,p_Api_Name => l_Api_Name
,p_Savepoint_Name => l_Savepoint_Name
,p_Exc_Name => Cux_Api.g_Exc_Name_Unexp
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data);
WHEN OTHERS THEN
x_Return_Status := Cux_Api.Handle_Exceptions(p_Pkg_Name => g_Pkg_Name
,p_Api_Name => l_Api_Name
,p_Savepoint_Name => l_Savepoint_Name
,p_Exc_Name => Cux_Api.g_Exc_Name_Others
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data);
END Create_Reservation;
文章来源:https://blog.csdn.net/qq_43649830/article/details/135239062
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！