ORACLE EBS 销售订单发运全流程及API详解（下）

最新推荐文章于 2025-03-21 15:36:19 发布

一只团子°呀

最新推荐文章于 2025-03-21 15:36:19 发布

阅读量2.4k

点赞数 15

分类专栏： EBS API 文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/qq_43649830/article/details/135262535

版权

EBS API 专栏收录该内容

8 篇文章

订阅专栏

三挑库确认

挑库发放完成后会生成对应的物料事务处理记录，物料从正式子库到临时存货子库。

四创建交货

4.1创建交货（页面）

第三步处理物料搬运单完成后，发运单的事务处理行状态变为分批发运/确认挑库，下一步为发运确认/关闭行程停靠站。

选择活动自动创建交货，点击执行，生成交货号。

4.1创建交货（API）

DECLARE
  Tbl_Line_Rows   Wsh_Util_Core.Id_Tab_Type;
  Tbl_Del_Rows    Wsh_Util_Core.Id_Tab_Type;
  x_Return_Status VARCHAR2(1);
  x_Msg_Count     NUMBER;
  x_Msg_Data      VARCHAR2(2000);
  l_Api_Version   NUMBER := 1;
BEGIN
  Fnd_Global.Apps_Initialize(User_Id      => 0
                            ,Resp_Id      => 51014
                            ,Resp_Appl_Id => 190);
  Tbl_Line_Rows(1) := 100139;--Delivery_Detail_Id
  --  Tbl_Line_Rows(2) := &Delivery_Detail_Id2;
  Wsh_Delivery_Details_Pub.Autocreate_Deliveries(p_Api_Version_Number => l_Api_Version
                                                ,p_Init_Msg_List      => Fnd_Api.g_True
                                                ,p_Commit             => Fnd_Api.g_False
                                                ,x_Return_Status      => x_Return_Status
                                                ,x_Msg_Count          => x_Msg_Count
                                                ,x_Msg_Data           => x_Msg_Data
                                                ,p_Line_Rows          => Tbl_Line_Rows
                                                ,x_Del_Rows           => Tbl_Del_Rows);
  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;
  Dbms_Output.Put_Line('=======================================================');
END;

4.3必要表信息

此时wnd表Wsh_New_Deliveries生成Delivery_Id

五发运确认

5.1发运确认（页面）

点击确定后行状态变为已连接，下一步为不适用。

5.2必要表信息

此时订单行表状态为SHIPPED

wdd表状态变为C（Shipped）

wnd表状态为CL（Delivery has arrived at the destination）

wnd表 Initial_Pickup_Date 日期为页面实际启用日期

SELECT Ooh.Flow_Status_Code
      ,Ool.Flow_Status_Code
      ,Ooh.Header_Id
      ,Ool.Line_Id
      ,Wdd.Released_Status
      ,Wdd.Delivery_Detail_Id
      ,Wdd.Move_Order_Line_Id
      ,Wnd.Delivery_Id
      ,Wnd.Status_Code
      ,Wdd.*
  FROM Oe_Order_Headers_All     Ooh
      ,Oe_Order_Lines_All       Ool
      ,Wsh_New_Deliveries       Wnd
      ,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 Wnd.Delivery_Id = Wda.Delivery_Id
   AND Wdd.Source_Line_Id = Ool.Line_Id
   AND Ooh.Order_Number = '1005188';

5.3释放保留（API)

页面点击按钮确认发运会自动释放保留，因为我们调用api去发运之前需要执行释放保留操作

--发运前先释放保留
DECLARE
  l_Rsv_Rec       Inv_Reservation_Global.Mtl_Reservation_Rec_Type;
  l_Serial_Number Inv_Reservation_Global.Serial_Number_Tbl_Type;
  l_Api_Version   NUMBER := 1.0;
  l_Init_Msg_List VARCHAR2(2) := Fnd_Api.g_True;

  x_Status                    VARCHAR2(2);
  x_Msg_Count                 NUMBER := 0;
  x_Msg_Data                  VARCHAR2(255);
  x_Error_Code                NUMBER := 0;
  x_Mtl_Reservation_Tbl       Inv_Reservation_Global.Mtl_Reservation_Tbl_Type;
  x_Mtl_Reservation_Tbl_Count NUMBER := 0;
  x_Primary_Relieved_Qty      NUMBER := 0;
  x_Primary_Remain_Qty        NUMBER := 0;
BEGIN

  FOR C1 IN (SELECT Mr.Reservation_Id
                   ,Mr.Primary_Reservation_Quantity
                    
                   ,Mr.Demand_Source_Line_Id
                   ,Mr.Inventory_Item_Id
               FROM Mtl_Reservations Mr
              WHERE Mr.Organization_Id = 154
                AND Mr.Demand_Source_Type_Id = 2
                AND Mr.Subinventory_Code = 'STAGE'
                AND EXISTS
              (SELECT 1
                       FROM Oe_Order_Lines_All Oola
                      WHERE Oola.Line_Id = Mr.Demand_Source_Line_Id
                        AND Oola.Header_Id = 1122921)) LOOP
    Dbms_Output.Put_Line('Mr.Primary_Reservation_Quantity:' ||
                         C1.Primary_Reservation_Quantity);
    l_Rsv_Rec.Organization_Id              := 154;
    l_Rsv_Rec.Inventory_Item_Id            := C1.Inventory_Item_Id;
    l_Rsv_Rec.Primary_Reservation_Quantity := C1.Primary_Reservation_Quantity;
    l_Rsv_Rec.Reservation_Id               := C1.Reservation_Id;
  
    l_Rsv_Rec.Demand_Source_Line_Id := C1.Demand_Source_Line_Id;
    l_Rsv_Rec.Demand_Source_Type_Id := 2;
    l_Rsv_Rec.Subinventory_Code     := 'KCLPK';
  
    Inv_Reservation_Pub.Query_Reservation(p_Api_Version_Number        => l_Api_Version
                                         ,p_Init_Msg_Lst              => l_Init_Msg_List
                                         ,x_Return_Status             => x_Status
                                         ,x_Msg_Count                 => x_Msg_Count
                                         ,x_Msg_Data                  => x_Msg_Data
                                         ,p_Query_Input               => l_Rsv_Rec
                                         ,p_Lock_Records              => Fnd_Api.g_False
                                         ,p_Sort_By_Req_Date          => Inv_Reservation_Global.g_Query_No_Sort
                                         ,p_Cancel_Order_Mode         => Inv_Reservation_Global.g_Cancel_Order_No
                                         ,x_Mtl_Reservation_Tbl       => x_Mtl_Reservation_Tbl
                                         ,x_Mtl_Reservation_Tbl_Count => x_Mtl_Reservation_Tbl_Count
                                         ,x_Error_Code                => x_Error_Code);
    Dbms_Output.Put_Line('Mr.x_Status:' || x_Status);
    IF x_Status = Fnd_Api.g_Ret_Sts_Success THEN
      Inv_Reservation_Pub.Relieve_Reservation(p_Api_Version_Number        => l_Api_Version
                                             ,p_Init_Msg_Lst              => l_Init_Msg_List
                                             ,x_Return_Status             => x_Status
                                             ,x_Msg_Count                 => x_Msg_Count
                                             ,x_Msg_Data                  => x_Msg_Data
                                             ,p_Rsv_Rec                   => x_Mtl_Reservation_Tbl(1)
                                             ,p_Primary_Relieved_Quantity => C1.Primary_Reservation_Quantity
                                             ,p_Relieve_All               => Fnd_Api.g_False
                                             ,p_Original_Serial_Number    => l_Serial_Number
                                             ,p_Validation_Flag           => Fnd_Api.g_True
                                             ,x_Primary_Relieved_Quantity => x_Primary_Relieved_Qty
                                             ,x_Primary_Remain_Quantity   => x_Primary_Remain_Qty);
    
    END IF;
    Dbms_Output.Put_Line('x_Status:' || x_Status);
  END LOOP;
END;

5.4发运确认（API）

DECLARE
  x_Return_Status           VARCHAR2(1);
  x_Msg_Count               NUMBER;
  x_Msg_Data                VARCHAR2(2000);
  l_Sc_Action_Flag          VARCHAR2(2);
  l_Sc_Intransit_Flag       VARCHAR2(2);
  l_Sc_Close_Trip_Flag      VARCHAR2(2);
  l_Sc_Create_Bol_Flag      VARCHAR2(2);
  l_Sc_Stage_Del_Flag       VARCHAR2(2);
  l_Sc_Trip_Ship_Method     VARCHAR2(30);
  l_Sc_Actual_Dep_Date      DATE;
  l_Sc_Report_Set_Id        NUMBER;
  l_Sc_Report_Set_Name      VARCHAR2(100);
  l_Sc_Defer_Interface_Flag VARCHAR2(2);
  l_Sc_Send_945_Flag        VARCHAR2(2);
  l_Wv_Override_Flag        VARCHAR2(2);
  x_Trip_Id                 VARCHAR2(30);
  x_Trip_Name               VARCHAR2(30);
  l_Organization_Id         NUMBER;
  l_Delivery_Name           VARCHAR2(30);
BEGIN
 Fnd_Global.Apps_Initialize(User_Id      => 0
                            ,Resp_Id      => 51014
                            ,Resp_Appl_Id => 190); --没有会报错
  FOR Rec_Deli_Id IN (SELECT DISTINCT Wnd.Delivery_Id
                        FROM Oe_Order_Lines_All       Oola
                            ,Wsh_Delivery_Details     Wdd
                            ,Wsh_Delivery_Assignments Wda
                            ,Wsh_New_Deliveries       Wnd
                       WHERE Oola.Flow_Status_Code = 'AWAITING_SHIPPING'
                         AND Wdd.Released_Status = 'Y'
                         AND Wdd.Delivery_Detail_Id = Wda.Delivery_Detail_Id
                         AND Wnd.Delivery_Id = Wda.Delivery_Id
                         AND Oola.Line_Id = Wdd.Source_Line_Id(+)
                         AND Oola.Header_Id = 1122922
                         AND Wdd.Source_Code = 'OE') LOOP
  
    Wsh_Deliveries_Pub.Delivery_Action(p_Api_Version_Number      => 1.0
                                      ,p_Init_Msg_List           => Fnd_Api.g_False
                                      ,x_Return_Status           => x_Return_Status
                                      ,x_Msg_Count               => x_Msg_Count
                                      ,x_Msg_Data                => x_Msg_Data
                                      ,p_Action_Code             => 'CONFIRM'
                                      ,p_Delivery_Id             => Rec_Deli_Id.Delivery_Id
                                      ,p_Sc_Action_Flag          => 'S' --S发运输入的数量  A全部发运 B全部延交 C全部周期盘点
                                      ,p_Sc_Intransit_Flag       => 'Y' --设置在途交货
                                      ,p_Sc_Close_Trip_Flag      => 'Y' --关闭行程
                                      ,p_Sc_Actual_Dep_Date      => SYSDATE --实际启运日期
                                      ,p_Sc_Defer_Interface_Flag => 'N' --延迟连接---为‘Y’，手动运行连接停靠站                                   
                                      ,p_Sc_Stage_Del_Flag       => 'Y' --为分批发运数量创建交货
                                       --p_sc_rule_id              => l_ship_confirm_rule_id,--发运规则
                                       --p_sc_trip_ship_method     => l_ship_method_code,
                                       --p_sc_create_bol_flag      => 'Y',
                                      ,x_Trip_Id   => x_Trip_Id
                                      ,x_Trip_Name => x_Trip_Name);
    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('Error Message :' ||
                             Fnd_Msg_Pub.Get(i
                                            ,'F'));
      END LOOP;
    END IF;
  END LOOP;
  Dbms_Output.Put_Line('=======================================================');
END;

至此，销售订单从创建，登记，发放，挑库，交货，发运全流程结束。