SO行销售员更新

CREATE OR REPLACE PACKAGE MEW_SO_SALESREPS_IMPORT_PKG IS
  --通过ADI 更新
  PROCEDURE MAIN(P_BATCH_ID        NUMBER
                ,P_OU_NAME         VARCHAR2
                ,P_ORDER_NUMBER    VARCHAR2
                ,P_SALESREP_NUMBER VARCHAR2 DEFAULT NULL
                ,P_ATTRIBUTE1      VARCHAR2 DEFAULT NULL
                ,P_ATTRIBUTE2      VARCHAR2 DEFAULT NULL
                ,P_ATTRIBUTE3      VARCHAR2 DEFAULT NULL
                ,P_ATTRIBUTE4      VARCHAR2 DEFAULT NULL
                ,P_ATTRIBUTE5      VARCHAR2 DEFAULT NULL
                ,P_ATTRIBUTE6      VARCHAR2 DEFAULT NULL);
  --通过此过程更新销售员
  PROCEDURE UPDATE_SO_SALESREP_PRC(P_BATCH_ID NUMBER
                                  ,P_ORG_ID   VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY MEW_SO_SALESREPS_IMPORT_PKG IS
  PROCEDURE MAIN(P_BATCH_ID        NUMBER
                ,P_OU_NAME         VARCHAR2
                ,P_ORDER_NUMBER    VARCHAR2
                ,P_SALESREP_NUMBER VARCHAR2 DEFAULT NULL
                ,P_ATTRIBUTE1      VARCHAR2 DEFAULT NULL --Line Num
                ,P_ATTRIBUTE2      VARCHAR2 DEFAULT NULL
                ,P_ATTRIBUTE3      VARCHAR2 DEFAULT NULL
                ,P_ATTRIBUTE4      VARCHAR2 DEFAULT NULL
                ,P_ATTRIBUTE5      VARCHAR2 DEFAULT NULL
                ,P_ATTRIBUTE6      VARCHAR2 DEFAULT NULL) IS
    V_OE_HEADERS_IFACE_REC OE_HEADERS_IFACE_ALL%ROWTYPE;
    V_OE_LINES_IFACE_REC   OE_LINES_IFACE_ALL%ROWTYPE;
    V_ORG_ID               NUMBER;
    V_SALESREP_ID          NUMBER;
    V_CURSALESREP_ID       NUMBER;
    V_HEADER_ID            NUMBER;
    CNT                    NUMBER;
  BEGIN
    BEGIN
      SELECT ORGANIZATION_ID
        INTO V_ORG_ID
        FROM HR_OPERATING_UNITS
       WHERE NAME = P_OU_NAME;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('OU NAME不存在');
    END;
 
    BEGIN
      SELECT SALESREP_ID
        INTO V_SALESREP_ID
        FROM JTF_RS_SALESREPS JRS
       WHERE JRS.SALESREP_NUMBER = P_SALESREP_NUMBER
         AND JRS.ORG_ID = V_ORG_ID;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('销售员不存在');
      WHEN TOO_MANY_ROWS THEN
        APP_EXCEPTION.RAISE_EXCEPTION('销售员编号在系统中存在重复');
    END;
 
    BEGIN
      SELECT OOH.ORIG_SYS_DOCUMENT_REF
            ,OOH.HEADER_ID
            ,OOH.ORDER_SOURCE_ID
            ,OOH.ORG_ID
        INTO V_OE_HEADERS_IFACE_REC.ORIG_SYS_DOCUMENT_REF
            ,V_HEADER_ID
            ,V_OE_HEADERS_IFACE_REC.ORDER_SOURCE_ID
            ,V_OE_HEADERS_IFACE_REC.ORG_ID
        FROM OE_ORDER_HEADERS_ALL OOH
       WHERE OOH.ORDER_NUMBER = P_ORDER_NUMBER
         AND OOH.ORG_ID = V_ORG_ID;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('订单编号不存在');
      WHEN TOO_MANY_ROWS THEN
        APP_EXCEPTION.RAISE_EXCEPTION('订单编号在系统中存在重复');
    END;
 
    BEGIN
      SELECT NVL(OOL.SALESREP_ID
                ,-2)
            ,OOL.ORG_ID
            ,OOL.ORDER_SOURCE_ID
            ,OOL.ORIG_SYS_DOCUMENT_REF
            ,OOL.ORIG_SYS_LINE_REF
            ,OOL.ORIG_SYS_SHIPMENT_REF
            ,OOL.LINE_ID
        INTO V_CURSALESREP_ID
            ,V_OE_LINES_IFACE_REC.ORG_ID
            ,V_OE_LINES_IFACE_REC.ORDER_SOURCE_ID
            ,V_OE_LINES_IFACE_REC.ORIG_SYS_DOCUMENT_REF
            ,V_OE_LINES_IFACE_REC.ORIG_SYS_LINE_REF
            ,V_OE_LINES_IFACE_REC.ORIG_SYS_SHIPMENT_REF
            ,V_OE_LINES_IFACE_REC.LINE_ID
        FROM OE_ORDER_LINES_ALL OOL
       WHERE OOL.HEADER_ID = V_HEADER_ID
         AND OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER = P_ATTRIBUTE1;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('销售订单行不存');
    END;
 
    IF V_CURSALESREP_ID = -2
    THEN
      APP_EXCEPTION.RAISE_EXCEPTION('订单行销售员信息未维护');
    END IF;
 
    IF V_CURSALESREP_ID <> 100004726 -- ='Hanate (PEWC)'
    THEN
      APP_EXCEPTION.RAISE_EXCEPTION('行销售员不等于Hanate (PEWC)');
    END IF;
 
    IF V_CURSALESREP_ID = V_SALESREP_ID
    THEN
      APP_EXCEPTION.RAISE_EXCEPTION('当前销售员与系统中一致！');
    END IF;
 
    SELECT COUNT(*)
      INTO CNT
      FROM OE_HEADERS_IFACE_ALL OHIA
     WHERE OHIA.BATCH_ID = P_BATCH_ID
       AND OHIA.HEADER_ID = V_HEADER_ID;
 
    IF NVL(CNT
          ,0) = 0
    THEN
      --OE_HEADERS_IFACE
      V_OE_HEADERS_IFACE_REC.OPERATION_CODE   := 'UPDATE';
      V_OE_HEADERS_IFACE_REC.CREATION_DATE    := SYSDATE;
      V_OE_HEADERS_IFACE_REC.CREATED_BY       := FND_GLOBAL.USER_ID;
      V_OE_HEADERS_IFACE_REC.LAST_UPDATE_DATE := SYSDATE;
      V_OE_HEADERS_IFACE_REC.LAST_UPDATED_BY  := FND_GLOBAL.USER_ID;
      V_OE_HEADERS_IFACE_REC.HEADER_ID        := V_HEADER_ID;
      V_OE_HEADERS_IFACE_REC.BATCH_ID         := P_BATCH_ID;
      INSERT INTO OE_HEADERS_IFACE_ALL VALUES V_OE_HEADERS_IFACE_REC;
    END IF;
 
    --OE_LINES_IFACE
    V_OE_LINES_IFACE_REC.OPERATION_CODE   := 'UPDATE';
    V_OE_LINES_IFACE_REC.CREATION_DATE    := SYSDATE;
    V_OE_LINES_IFACE_REC.CREATED_BY       := FND_GLOBAL.USER_ID;
    V_OE_LINES_IFACE_REC.LAST_UPDATE_DATE := SYSDATE;
    V_OE_LINES_IFACE_REC.LAST_UPDATED_BY  := FND_GLOBAL.USER_ID;
    V_OE_LINES_IFACE_REC.SALESREP_ID      := V_SALESREP_ID; --100006424 =Jo Wan (PEWC)
 
    INSERT INTO OE_LINES_IFACE_ALL VALUES V_OE_LINES_IFACE_REC;
 
  END;

  --通过此过程更新销售员
  PROCEDURE UPDATE_SO_SALESREP_PRC(P_BATCH_ID NUMBER
                                  ,P_ORG_ID   VARCHAR2) IS
    V_OE_HEADERS_IFACE_REC OE_HEADERS_IFACE_ALL%ROWTYPE;
    V_OE_LINES_IFACE_REC   OE_LINES_IFACE_ALL%ROWTYPE;
    V_ORG_ID               NUMBER;
    V_SALESREP_ID          NUMBER;
    V_CURSALESREP_ID       NUMBER;
    V_HEADER_ID            NUMBER;
    CNT                    NUMBER;
    CURSOR C1 IS
      SELECT *
        FROM MEWBACKUP.MEW_SO_SALESREPS_TEMP T
       WHERE T.BATCH_ID = P_BATCH_ID
         AND T.ERROR_CODE IS NULL
         AND t.order_number = '40106708';
    V_IMPORT_FLAG VARCHAR2(1) := 'Y';
  BEGIN
    /*    BEGIN
      SELECT ORGANIZATION_ID
        INTO V_ORG_ID
        FROM HR_OPERATING_UNITS
       WHERE NAME = P_OU_NAME;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('OU NAME不存在');
    END;*/
    FOR R1 IN C1
    LOOP
      V_IMPORT_FLAG := 'Y';
      BEGIN
        SELECT SALESREP_ID
          INTO V_SALESREP_ID
          FROM JTF_RS_SALESREPS JRS
         WHERE JRS.SALESREP_NUMBER = R1.SALESREP_NUMBER
           AND JRS.ORG_ID = P_ORG_ID;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
             SET T.ERROR_CODE    = 'E'
                ,T.ERROR_MESSAGE = '销售员不存在'
           WHERE T.BATCH_ID = P_BATCH_ID
             AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
             AND T.LINE_NUMBER = R1.LINE_NUMBER
             AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
          V_IMPORT_FLAG := 'N';
        WHEN TOO_MANY_ROWS THEN
          UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
             SET T.ERROR_CODE    = 'E'
                ,T.ERROR_MESSAGE = '销售员编号在系统中存在重复'
           WHERE T.BATCH_ID = P_BATCH_ID
             AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
             AND T.LINE_NUMBER = R1.LINE_NUMBER
             AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
          V_IMPORT_FLAG := 'N';
      END;
   
      BEGIN
        SELECT OOH.ORIG_SYS_DOCUMENT_REF
              ,OOH.HEADER_ID
              ,OOH.ORDER_SOURCE_ID
              ,OOH.ORG_ID
          INTO V_OE_HEADERS_IFACE_REC.ORIG_SYS_DOCUMENT_REF
              ,V_HEADER_ID
              ,V_OE_HEADERS_IFACE_REC.ORDER_SOURCE_ID
              ,V_OE_HEADERS_IFACE_REC.ORG_ID
          FROM OE_ORDER_HEADERS_ALL OOH
         WHERE OOH.ORDER_NUMBER = R1.ORDER_NUMBER
           AND OOH.ORG_ID = P_ORG_ID
           AND OOH.FLOW_STATUS_CODE NOT IN
               ('CANCELLED'
               ,'CLOSED');
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
             SET T.ERROR_CODE    = 'E'
                ,T.ERROR_MESSAGE = '订单编号不存在或已取消 关闭'
           WHERE T.BATCH_ID = P_BATCH_ID
             AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
             AND T.LINE_NUMBER = R1.LINE_NUMBER
             AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
          V_IMPORT_FLAG := 'N';
        WHEN TOO_MANY_ROWS THEN
          UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
             SET T.ERROR_CODE    = 'E'
                ,T.ERROR_MESSAGE = '订单编号在系统中存在重复'
           WHERE T.BATCH_ID = P_BATCH_ID
             AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
             AND T.LINE_NUMBER = R1.LINE_NUMBER
             AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
          V_IMPORT_FLAG := 'N';
       
      END;
   
      BEGIN
        SELECT NVL(OOL.SALESREP_ID
                  ,-2)
              ,OOL.ORG_ID
              ,OOL.ORDER_SOURCE_ID
              ,OOL.ORIG_SYS_DOCUMENT_REF
              ,OOL.ORIG_SYS_LINE_REF
              ,OOL.ORIG_SYS_SHIPMENT_REF
              ,OOL.LINE_ID
          INTO V_CURSALESREP_ID
              ,V_OE_LINES_IFACE_REC.ORG_ID
              ,V_OE_LINES_IFACE_REC.ORDER_SOURCE_ID
              ,V_OE_LINES_IFACE_REC.ORIG_SYS_DOCUMENT_REF
              ,V_OE_LINES_IFACE_REC.ORIG_SYS_LINE_REF
              ,V_OE_LINES_IFACE_REC.ORIG_SYS_SHIPMENT_REF
              ,V_OE_LINES_IFACE_REC.LINE_ID
          FROM OE_ORDER_LINES_ALL OOL
         WHERE OOL.HEADER_ID = V_HEADER_ID
           AND OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER =
               R1.LINE_NUMBER
           AND OOL.FLOW_STATUS_CODE NOT IN
               ('CANCELLED'
               ,'CLOSED');
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
             SET T.ERROR_CODE    = 'E'
                ,T.ERROR_MESSAGE = '销售订单行不存在或已取消或关闭'
           WHERE T.BATCH_ID = P_BATCH_ID
             AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
             AND T.LINE_NUMBER = R1.LINE_NUMBER
             AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
          V_IMPORT_FLAG := 'N';
      END;
   
      /*      IF V_CURSALESREP_ID = -2
      THEN
        UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
           SET T.ERROR_CODE    = 'E'
              ,T.ERROR_MESSAGE = '订单行销售员信息未维护'
         WHERE T.BATCH_ID = P_BATCH_ID
           AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
           AND T.LINE_NUMBER = R1.LINE_NUMBER
           AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
        V_IMPORT_FLAG := 'N';
      END IF;
     
      IF V_CURSALESREP_ID <> 100004726 -- ='Hanate (PEWC)'
      THEN
        UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
           SET T.ERROR_CODE    = 'E'
              ,T.ERROR_MESSAGE = '行销售员不等于Hanate (PEWC)'
         WHERE T.BATCH_ID = P_BATCH_ID
           AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
           AND T.LINE_NUMBER = R1.LINE_NUMBER
           AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
        V_IMPORT_FLAG := 'N';
      END IF;
     
      IF V_CURSALESREP_ID = V_SALESREP_ID
      THEN
        UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
           SET T.ERROR_CODE    = 'E'
              ,T.ERROR_MESSAGE = '当前销售员与系统中一致'
         WHERE T.BATCH_ID = P_BATCH_ID
           AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
           AND T.LINE_NUMBER = R1.LINE_NUMBER
           AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
        \*         RAISE_APPLICATION_ERROR(-20001
        ,V_SALESREP_ID || '-----' ||
         V_CURSALESREP_ID || '-----' ||
         R1.Order_Number || '----' ||
         R1.LINE_NUMBER);*\
        V_IMPORT_FLAG := 'N';
      END IF;*/
      IF V_IMPORT_FLAG = 'Y'
      THEN
        UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
           SET T.ERROR_CODE    = 'S'
              ,T.ERROR_MESSAGE = '成功导入接口表'
         WHERE T.BATCH_ID = P_BATCH_ID
           AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
           AND T.LINE_NUMBER = R1.LINE_NUMBER
           AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
        SELECT COUNT(*)
          INTO CNT
          FROM OE_HEADERS_IFACE_ALL OHIA
         WHERE OHIA.BATCH_ID = P_BATCH_ID
           AND OHIA.HEADER_ID = V_HEADER_ID;
        IF NVL(CNT
              ,0) = 0
        THEN
          --OE_HEADERS_IFACE
          V_OE_HEADERS_IFACE_REC.OPERATION_CODE   := 'UPDATE';
          V_OE_HEADERS_IFACE_REC.CREATION_DATE    := SYSDATE;
          V_OE_HEADERS_IFACE_REC.CREATED_BY       := FND_GLOBAL.USER_ID;
          V_OE_HEADERS_IFACE_REC.LAST_UPDATE_DATE := SYSDATE;
          V_OE_HEADERS_IFACE_REC.LAST_UPDATED_BY  := FND_GLOBAL.USER_ID;
          V_OE_HEADERS_IFACE_REC.HEADER_ID        := V_HEADER_ID;
          V_OE_HEADERS_IFACE_REC.BATCH_ID         := P_BATCH_ID;
          --   V_OE_HEADERS_IFACE_REC.SALESREP_ID      := V_SALESREP_ID;
       
          INSERT INTO OE_HEADERS_IFACE_ALL VALUES V_OE_HEADERS_IFACE_REC;
        END IF;
     
        --OE_LINES_IFACE
        V_OE_LINES_IFACE_REC.OPERATION_CODE   := 'UPDATE';
        V_OE_LINES_IFACE_REC.CREATION_DATE    := SYSDATE;
        V_OE_LINES_IFACE_REC.CREATED_BY       := FND_GLOBAL.USER_ID;
        V_OE_LINES_IFACE_REC.LAST_UPDATE_DATE := SYSDATE;
        V_OE_LINES_IFACE_REC.LAST_UPDATED_BY  := FND_GLOBAL.USER_ID;
        V_OE_LINES_IFACE_REC.SALESREP_ID      := V_SALESREP_ID; --100006424 =Jo Wan (PEWC)
     
        INSERT INTO OE_LINES_IFACE_ALL VALUES V_OE_LINES_IFACE_REC;
      END IF;
    END LOOP;
    COMMIT;
  END;
END;
/