EBS po接收退货至供应商

一只团子°呀

已于 2024-05-28 15:17:27 修改

阅读量833

点赞数 5

CC 4.0 BY-SA版权

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

于 2024-05-28 15:13:18 首次发布

本文链接：https://blog.youkuaiyun.com/qq_43649830/article/details/139267863

ORACLE EBS Interface 专栏收录该内容

4 篇文章

订阅专栏

a)对于直接入库的接收，插接收事务处理的接口行表(rcv_transactions_interface)时的几个关键的状态字段：

rcv_transactions_interface.transaction_type := 'RECEIVE';

rcv_transactions_interface.auto_transact_code := 'DELIVER';

rcv_transactions_interface.destination_type_code := 'INVENTORY';

b)对于标准以及需检验的入库的接收，插接收事务处理的接口行表(rcv_transactions_interface)时的几个关键的状态字段：

rcv_transactions_interface.transaction_type := 'RECEIVE';

rcv_transactions_interface.auto_transact_code := NULL;

rcv_transactions_interface.destination_type_code := ' RECEIVING ';

c)对于接收甚至检验后的数据做入库时，插接收事务处理的接口行表(rcv_transactions_interface)时的几个关键的状态字段：

rcv_transactions_interface.transaction_type := 'DELIVER';

rcv_transactions_interface.auto_transact_code := NULL;

rcv_transactions_interface.destination_type_code := 'INVENTORY';

退回至供应商需要2步操作：

A) 退回至接收

TRANSACTION_TYPE = RETURN TO RECEIVING

PARENT_TRANSACTION_ID = (Transaction Id for DELIVER transaction)

B) 退回至供应商：

TRANSACTION_TYPE = RETURN TO VENDOR

PARENT_TRANSACTION_ID = (Transaction Id for RECEIVE transaction) 注意退回到供应商的parentid 是接收的ID

对于直接交货收据的退货至供应商，只需要1步退货

TRANSACTION_TYPE = RETURN TO VENDOR

PARENT_TRANSACTION_ID = (Transaction Id for DELIVER transaction)

示例：

DECLARE
  l_Iface_Hdr_Rec  Po.Rcv_Headers_Interface%ROWTYPE;
  l_Iface_Rcv_Rec  Po.Rcv_Transactions_Interface%ROWTYPE;
  l_Cur_Mfg_Org_Id NUMBER := 154; --Current Inv Organization
  l_User_Id        NUMBER := 0; --User ID?Sysadmin
  l_Req_Completed  BOOLEAN := FALSE; --请求返回结果
  Ln_Request_Id    NUMBER;
  Ln_Group_Id      NUMBER;
  --提交PO自动接收标准请求所用参数
  l_Phase   VARCHAR(240);
  l_Status  VARCHAR(240);
  l_Dphase  VARCHAR(240);
  l_Dstatus VARCHAR2(240);
  l_Message VARCHAR(240);
  l_Count   NUMBER;
  CURSOR Crh_Line IS
    SELECT DISTINCT Poh.Po_Header_Id
                   ,Pol.Po_Line_Id
                   ,Pll.Line_Location_Id
                   ,Pol.Item_Id
                   ,Poh.Segment1 Po_Number
                   ,Poh.Vendor_Id
                   ,Poh.Vendor_Site_Id
                   ,Poh.Ship_To_Location_Id
                   ,Poh.Bill_To_Location_Id
                   ,Poh.Authorization_Status
                   ,Pol.Quantity
                   ,Msi.Segment1
                   ,Pol.Unit_Meas_Lookup_Code
                   ,Pll.Cancel_Flag
                   ,Pll.Ship_To_Organization_Id
      FROM Po_Headers_All        Poh
          ,Po_Lines_All          Pol
          ,Po_Line_Locations_All Pll
          ,Po_Distributions_All  Pod --采购订单发运表
          ,Mtl_System_Items_b    Msi
     WHERE Poh.Po_Header_Id = Pol.Po_Header_Id --采购订单头表与采购订单行表关联
       AND Poh.Type_Lookup_Code = 'STANDARD' --标准采购订单
       AND Pol.Po_Line_Id = Pll.Po_Line_Id --采购订单行表与采购订单发运表关联
       AND Pll.Line_Location_Id = Pod.Line_Location_Id --采购订单发运表与采购订单分配表关联        
       AND Msi.Inventory_Item_Id = Pol.Item_Id
       AND Msi.Organization_Id = 154
       AND Pol.Item_Id = Msi.Inventory_Item_Id
       AND Poh.Segment1 = '20005690'
     ORDER BY Poh.Po_Header_Id
             ,Pol.Po_Line_Id;

BEGIN
  BEGIN
    Fnd_Global.Apps_Initialize(User_Id      => 0
                              ,Resp_Id      => 51014
                              ,Resp_Appl_Id => 190);
    Mo_Global.Init('M');
  END;
  l_Iface_Hdr_Rec.Last_Update_Date  := SYSDATE;
  l_Iface_Hdr_Rec.Last_Updated_By   := l_User_Id;
  l_Iface_Hdr_Rec.Creation_Date     := SYSDATE;
  l_Iface_Hdr_Rec.Created_By        := l_User_Id;
  l_Iface_Hdr_Rec.Last_Update_Login := l_User_Id;
  l_Iface_Rcv_Rec.Last_Update_Date  := SYSDATE;
  l_Iface_Rcv_Rec.Last_Updated_By   := l_User_Id;
  l_Iface_Rcv_Rec.Creation_Date     := SYSDATE;
  l_Iface_Rcv_Rec.Created_By        := l_User_Id;
  l_Iface_Rcv_Rec.Last_Update_Login := l_User_Id;
  SELECT Po.Rcv_Headers_Interface_s.Nextval
    INTO l_Iface_Hdr_Rec.Header_Interface_Id
    FROM Dual;
  SELECT Po.Rcv_Interface_Groups_s.Nextval
    INTO l_Iface_Hdr_Rec.Group_Id
    FROM Dual;
  Ln_Group_Id                            := l_Iface_Hdr_Rec.Group_Id;
  l_Iface_Hdr_Rec.Processing_Status_Code := 'PENDING';
  l_Iface_Hdr_Rec.Receipt_Source_Code    := 'VENDOR';
  l_Iface_Hdr_Rec.Transaction_Type       := 'NEW';
  l_Iface_Hdr_Rec.Auto_Transact_Code     := 'DELIVER'; --'RECEIVE'; --接收

  l_Iface_Hdr_Rec.Receipt_Num             := 20005690; --20005690; --Automatic Number
  l_Iface_Hdr_Rec.Vendor_Id               := 3002;
  l_Iface_Hdr_Rec.Vendor_Site_Id          := 17055;
  l_Iface_Hdr_Rec.Location_Id             := 4711; --PO头收单方地点
  l_Iface_Hdr_Rec.Expected_Receipt_Date   := SYSDATE;
  l_Iface_Hdr_Rec.Validation_Flag         := 'Y';
  l_Iface_Hdr_Rec.Ship_To_Organization_Id := 154;
  INSERT INTO Po.Rcv_Headers_Interface
  VALUES l_Iface_Hdr_Rec;

  FOR a IN Crh_Line LOOP
    SELECT Rcv_Transactions_Interface_s.Nextval
      INTO l_Iface_Rcv_Rec.Interface_Transaction_Id
      FROM Dual;
    l_Iface_Rcv_Rec.Header_Interface_Id     := l_Iface_Hdr_Rec.Header_Interface_Id;
    l_Iface_Rcv_Rec.Group_Id                := l_Iface_Hdr_Rec.Group_Id;
    l_Iface_Rcv_Rec.Processing_Status_Code  := 'PENDING';
    l_Iface_Rcv_Rec.Transaction_Status_Code := 'PENDING';
    l_Iface_Rcv_Rec.Processing_Mode_Code    := 'BATCH';
    l_Iface_Rcv_Rec.Validation_Flag         := 'Y';
    l_Iface_Rcv_Rec.Receipt_Source_Code     := 'VENDOR';
    l_Iface_Rcv_Rec.Vendor_Id               := a.Vendor_Id;
    l_Iface_Rcv_Rec.Vendor_Site_Id          := a.Vendor_Site_Id; --Optional
    l_Iface_Rcv_Rec.Source_Document_Code    := 'PO';
    l_Iface_Rcv_Rec.Po_Header_Id            := a.Po_Header_Id;
    l_Iface_Rcv_Rec.Po_Line_Id              := a.Po_Line_Id;
    l_Iface_Rcv_Rec.Po_Line_Location_Id     := a.Line_Location_Id;
    l_Iface_Rcv_Rec.Po_Release_Id           := NULL;
    /*  
    l_Iface_Rcv_Rec.Transaction_Type      := 'RETURN TO RECEIVING';
    l_Iface_Rcv_Rec.Auto_Transact_Code    := NULL; --'RECEIVE'; --'DELIVER';
    l_Iface_Rcv_Rec.Destination_Type_Code := 'INVENTORY';
    BEGIN
      SELECT Rt.Transaction_Id
        INTO l_Iface_Rcv_Rec.Parent_Transaction_Id
        FROM Rcv_Transactions Rt
       WHERE Rt.Po_Header_Id = a.Po_Header_Id
         AND Rt.Organization_Id = 154
         AND Rt.Transaction_Type = 'RECEIVE'
         AND Rt.Source_Document_Code = 'PO'
         AND Rt.Po_Line_Location_Id = a.Line_Location_Id
         AND Substr(Rt.Last_Update_Date
                   ,1
                   ,4) > '2024'
         AND Rownum = 1;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        Dbms_Output.Put_Line('接收单号不存在1' || SQLERRM);
    END;*/
  
    l_Iface_Rcv_Rec.Transaction_Type      := 'RETURN TO VENDOR';
    l_Iface_Rcv_Rec.Auto_Transact_Code    := 'RETURN TO VENDOR'; --'RECEIVE'; --'DELIVER';
    l_Iface_Rcv_Rec.Destination_Type_Code := 'RECEIVING'; --'EXPENSE'; --'INVENTORY';
    BEGIN
      SELECT Rt.Transaction_Id
        INTO l_Iface_Rcv_Rec.Parent_Transaction_Id
        FROM Rcv_Transactions Rt
       WHERE Rt.Po_Header_Id = a.Po_Header_Id
         AND Rt.Organization_Id = 154
         AND Rt.Transaction_Type = 'DELIVER'
         AND Rt.Source_Document_Code = 'PO'
         AND Rt.Po_Line_Location_Id = a.Line_Location_Id
         AND Substr(Rt.Last_Update_Date
                   ,1
                   ,4) > '2024'
         AND Rownum = 1;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        Dbms_Output.Put_Line('接收单号不存在' || SQLERRM);
    END;
  
    l_Iface_Rcv_Rec.Item_Id                 := a.Item_Id;
    l_Iface_Rcv_Rec.Transaction_Date        := SYSDATE;
    l_Iface_Rcv_Rec.Quantity                := a.Quantity;
    l_Iface_Rcv_Rec.Primary_Quantity        := a.Quantity;
    l_Iface_Rcv_Rec.Unit_Of_Measure         := a.Unit_Meas_Lookup_Code;
    l_Iface_Rcv_Rec.Primary_Unit_Of_Measure := a.Unit_Meas_Lookup_Code;
    l_Iface_Rcv_Rec.To_Organization_Id      := a.Ship_To_Organization_Id;
    l_Iface_Rcv_Rec.Ship_To_Location_Id     := a.Bill_To_Location_Id; --PO头收单方地点
    l_Iface_Rcv_Rec.Location_Id             := NULL; --a.Bill_To_Location_Id;
    l_Iface_Rcv_Rec.Interface_Source_Code   := 'Test';
    INSERT INTO Po.Rcv_Transactions_Interface
    VALUES l_Iface_Rcv_Rec;
  END LOOP;
  Dbms_Output.Put_Line('group_id:' || l_Iface_Rcv_Rec.Group_Id);
  Ln_Request_Id := Fnd_Request.Submit_Request(Application => 'PO'
                                             ,Program     => 'RVCTP'
                                             ,Description => NULL
                                             ,Start_Time  => NULL
                                             ,Sub_Request => FALSE
                                             ,Argument1   => 'BATCH'
                                             ,Argument2   => Ln_Group_Id
                                             ,Argument3   => Chr(0));
  IF Ln_Request_Id = 0 THEN
    Dbms_Output.Put_Line('创建不成功');
  ELSE
    Dbms_Output.Put_Line('Ln_Request_Id:' || Ln_Request_Id);
    COMMIT;
    l_Req_Completed := Fnd_Concurrent.Wait_For_Request(Request_Id => Ln_Request_Id
                                                      , --请求ID
                                                       INTERVAL   => 6
                                                      , --检查时间间隔
                                                       Max_Wait   => 0
                                                      , --最大等待时间
                                                       Phase      => l_Phase
                                                      ,Status     => l_Status
                                                      ,Dev_Phase  => l_Dphase
                                                      , --请求运行阶段
                                                       Dev_Status => l_Dstatus
                                                      , --各个阶段状态
                                                       Message    => l_Message); --运行完成后输出信息
    COMMIT;
    IF l_Req_Completed = TRUE
       AND l_Dstatus = 'NORMAL'
       AND l_Dphase = 'COMPLETE' THEN
      Dbms_Output.Put_Line('请求运行成功');
    ELSIF Upper(l_Dstatus) = 'ERROR'
          OR Upper(l_Status) = 'ERROR' THEN
    
      Dbms_Output.Put_Line('错误,提交 "接收事务处理处理器" 请求失败');
    ELSIF Upper(l_Dstatus) = 'WARNING'
          OR Upper(l_Status) = 'WARNING' THEN
      Dbms_Output.Put_Line('警告,提交 "接收事务处理处理器" 请求失败');
    END IF;
  END IF;
  --COMMIT;
END;