ORACLE EBS 销售订单发运全流程及API详解(上)

发布时间:2023年12月28日

一 创建订单

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

发放前wdd表状态为R(Ready to release),发放后状态为S(Released to Warehouse)

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
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。