PO接收交货接口程序

 

DECLARE
  --采购未接收PO
  CURSOR C1 IS
    SELECT PLLA.QUANTITY
          ,PLLA.QUANTITY_RECEIVED
          ,PLLA.*
      FROM PO_HEADERS_ALL        PHA
          ,PO_LINES_ALL          PLA
          ,PO_LINE_LOCATIONS_ALL PLLA
          ,PO_DISTRIBUTIONS_ALL  PDA
     WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
       AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
       AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
       AND PHA.SEGMENT1 = 'SM21201D'
       AND PLA.ITEM_ID = 405844
       AND (PLLA.CANCEL_FLAG IS NULL OR PLLA.CANCEL_FLAG != 'Y')
       AND (PLLA.CLOSED_CODE IS NULL OR
           PLLA.CLOSED_CODE != 'FINALLY CLOSED')
       AND NVL(PLLA.QUANTITY
              ,0) - NVL(PLLA.QUANTITY_RECEIVED
                       ,0) > 0;

  V_RCV_HEADERS_IFACE     RCV_HEADERS_INTERFACE%ROWTYPE;
  V_RCV_TRANSACTION_IFACE RCV_TRANSACTIONS_INTERFACE%ROWTYPE;

  V_HEADER_INTERFACE_ID      RCV_HEADERS_INTERFACE.HEADER_INTERFACE_ID%TYPE;
  V_INTERFACE_GROUP_ID       NUMBER;
  V_INTERFACE_TRANSACTION_ID NUMBER;
BEGIN

  SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
    INTO V_INTERFACE_GROUP_ID
    FROM DUAL;

  --循环接收
  FOR R1 IN C1
  LOOP
 
    SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
      INTO V_HEADER_INTERFACE_ID
      FROM DUAL;
 
    --判断当前发运行是否接收过，若有则添加至接收，反之新建接收
    BEGIN
      SELECT RSL.SHIPMENT_HEADER_ID
            ,RSL.SHIPMENT_LINE_ID
        INTO V_SHIPMENT_HEADER_ID
            ,V_SHIPMENT_LINE_ID
        FROM RCV_SHIPMENT_LINES RSL
       WHERE RSL.PO_LINE_LOCATION_ID = R1.PO_LINE_LOCATION_ID
         AND ROWNUM = 1;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        V_SHIPMENT_HEADER_ID := NULL;
        V_SHIPMENT_LINE_ID   := NULL;
    END;
 
    --判断 新建接收或添加至接收
    IF V_SHIPMENT_HEADER_ID IS NOT NULL
    THEN
      V_RCV_HEADERS_IFACE.TRANSACTION_TYPE  := 'ADD';
      V_RCV_HEADERS_IFACE.RECEIPT_HEADER_ID := V_SHIPMENT_HEADER_ID;
    ELSE
      V_RCV_HEADERS_IFACE.TRANSACTION_TYPE  := 'NEW';
      V_RCV_HEADERS_IFACE.RECEIPT_HEADER_ID := NULL;
    END IF;
    --接收事务处理头表赋值
    V_RCV_HEADERS_IFACE.HEADER_INTERFACE_ID := V_HEADER_INTERFACE_ID;
    V_RCV_HEADERS_IFACE.LAST_UPDATE_DATE    := SYSDATE;
    V_RCV_HEADERS_IFACE.LAST_UPDATED_BY     := -1;
    V_RCV_HEADERS_IFACE.GROUP_ID            := V_INTERFACE_GROUP_ID;
 
    INSERT INTO RCV_HEADERS_INTERFACE VALUES V_RCV_HEADERS_IFACE;
 
    SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
      INTO V_INTERFACE_TRANSACTION_ID
      FROM DUAL;
 
    V_RCV_TRANSACTION_IFACE.HEADER_INTERFACE_ID      := V_HEADER_INTERFACE_ID;
    V_RCV_TRANSACTION_IFACE.INTERFACE_TRANSACTION_ID := V_INTERFACE_TRANSACTION_ID;
    V_RCV_TRANSACTION_IFACE.GROUP_ID                 := V_INTERFACE_GROUP_ID;
    V_RCV_TRANSACTION_IFACE.CREATION_DATE            := SYSDATE;
    V_RCV_TRANSACTION_IFACE.CREATED_BY               := -1;
    V_RCV_TRANSACTION_IFACE.LAST_UPDATE_DATE         := SYSDATE;
    V_RCV_TRANSACTION_IFACE.LAST_UPDATED_BY          := -1;
    V_RCV_TRANSACTION_IFACE.TRANSACTION_TYPE         := 'RECEIVE'; --待确认
    V_RCV_TRANSACTION_IFACE.TRANSACTION_DATE         := SYSDATE;
    V_RCV_TRANSACTION_IFACE.PROCESSING_STATUS_CODE   := 'PENDING';
    V_RCV_TRANSACTION_IFACE.PROCESSING_MODE_CODE     := 'IMMEDIATE';
    V_RCV_TRANSACTION_IFACE.TRANSACTION_STATUS_CODE  := 'PENDING';
 
  END LOOP;

END;