关闭

文件数据导入

266人阅读 评论(0) 收藏 举报

CREATE OR REPLACE PACKAGE CUX_AMW_FILE_INTO IS

  /*==========================================================================+
  |             Copyright (c) 2009 Ares Huang                                 |
  |                   All rights reserved.                                    |
  +===========================================================================+
  |-- Author  : MIS HuangYunqiang                                             |
  |-- Created : 2009-05-14 10:08:22                                           |
  *****************************************************************************
  ****************************************************************************/
  PROCEDURE ME_OPT(P_FILE_ID IN NUMBER
                  ,P_ORG_ID  IN NUMBER);

  PROCEDURE NMCC_GZ(P_FILE_ID IN NUMBER
                   ,P_ORG_ID  IN NUMBER);

END CUX_AMW_FILE_INTO;
/
CREATE OR REPLACE PACKAGE BODY CUX_AMW_FILE_INTO IS

  /*==========================================================================+
  |             Copyright (c) 2009 Ares Huang                                 |
  |                   All rights reserved.                                    |
  +===========================================================================+
  |-- Author  : MIS HuangYunqiang                                             |
  |-- Created : 2009-05-14 10:08:22                                           |
  *****************************************************************************
  ****************************************************************************/
  PROCEDURE ME_OPT(P_FILE_ID IN NUMBER
                  ,P_ORG_ID  IN NUMBER) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    -- l_file_id NUMBER;
    ----------------------------
    --w_integer INTEGER;
    W_BLOB    BLOB;
    W_RAW     RAW(10);
    W_BUFF    VARCHAR2(30000);
    W_LINE    VARCHAR2(30000);
    W_LEN     INTEGER;
    EOFSW     BOOLEAN := FALSE;
    OFFSET    INTEGER;
    W_B_LEN   NUMBER := 0;
    W_NUM     NUMBER;
    W_SEQ     NUMBER := 0;
    V_LOOP    NUMBER := 0;
    V_TYPE    VARCHAR2(50);
    V_PD_NAME VARCHAR2(50);
    V_PD_SUB  VARCHAR2(50);
    V_RE      NUMBER;
    V_TMP     NUMBER;
    ----------------------------
    I       NUMBER;
    LINEBUF VARCHAR2(30000);
    TYPE COMMA_POS_TYPE IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
    T_COMMA_POS COMMA_POS_TYPE;
    V_NO        VARCHAR2(22);
  BEGIN
    SELECT TO_CHAR(SYSDATE, 'YYYYMM') || LPAD(TO_CHAR(NVL(COUNT(DISTINCT IN_NO), 0) + 1), 4, '0')
      INTO V_NO
      FROM CUX.CUX_AMW_ME_OPT T
     WHERE T.CREATION_DATE >= TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM') || '-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
       AND T.CREATION_DATE < TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYY-MM') || '-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
    SELECT FILE_DATA INTO W_BLOB FROM FND_LOBS WHERE FILE_ID = P_FILE_ID;
    OFFSET := 1;
    V_LOOP := 0;
    LOOP
      EXIT WHEN EOFSW;
      W_SEQ   := W_SEQ + 1;
      W_RAW   := UTL_RAW.CAST_TO_RAW(CHR(10));
      W_NUM   := DBMS_LOB.INSTR(W_BLOB, W_RAW, OFFSET, 1);
      W_LEN   := W_NUM - W_B_LEN;
      W_B_LEN := W_NUM;
      IF W_NUM = 0 THEN
        W_LEN := 20000;
        EOFSW := TRUE;
      END IF;
      BEGIN
        DBMS_LOB.READ(W_BLOB, W_LEN, OFFSET, W_BUFF);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        WHEN OTHERS THEN
          RAISE;
      END;
      W_LINE := UTL_RAW.CAST_TO_VARCHAR2(W_BUFF);
      SELECT REPLACE(W_LINE, CHR(10), NULL) INTO W_LINE FROM DUAL;
      SELECT REPLACE(W_LINE, CHR(13), NULL) INTO W_LINE FROM DUAL;
      ------------------   
      LINEBUF := W_LINE;
      I       := 1;
      WHILE INSTR(LINEBUF, ',', 1, I) > 0 LOOP
        T_COMMA_POS(I) := INSTR(LINEBUF, ',', 1, I);
        I := I + 1;
      END LOOP;
      IF V_LOOP > 0 THEN
        BEGIN
          INSERT INTO CUX.CUX_AMW_ME_OPT
            (FILE_ID
            ,ORG_ID
            ,M_NO
            ,E_NO
            ,JOB_NO
            ,E_QTY
            ,M_QTY
            ,M_ITEM_NAME
            ,PXH
            --,E_ITEM_NAME
            ,LAST_UPDATE_DATE
            ,LAST_UPDATED_BY
            ,CREATION_DATE
            ,CREATED_BY
            ,IN_NO)
          VALUES
            (P_FILE_ID
            ,P_ORG_ID
            ,TRIM(SUBSTR(LINEBUF, 1, T_COMMA_POS(1) - 1)) --M NO
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(1) + 1, T_COMMA_POS(2) - T_COMMA_POS(1) - 1)) --E NO
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(2) + 1, T_COMMA_POS(3) - T_COMMA_POS(2) - 1)) --JOB NO
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(3) + 1, T_COMMA_POS(4) - T_COMMA_POS(3) - 1)) --E QTY
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(4) + 1, T_COMMA_POS(5) - T_COMMA_POS(4) - 1)) --M ITEM
            ,0 --M QTY
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(5) + 1)) -- PXH
            --,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(5) + 1)) -- E ITEM
            ,SYSDATE
            ,APPS.FND_GLOBAL.USER_ID
            ,SYSDATE
            ,APPS.FND_GLOBAL.USER_ID
            ,V_NO);
        EXCEPTION
          WHEN OTHERS THEN
            FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
            FND_MESSAGE.SET_TOKEN('ERRNO', -20000);
            FND_MESSAGE.SET_TOKEN('REASON', '导入失败,请确认数据 ' || LINEBUF || SUBSTR(LINEBUF, 1, T_COMMA_POS(1) - 1));
            FND_MESSAGE.SET_TOKEN('ROUTINE', 'CUX_AMW_FILE_INTO');
            ROLLBACK;
            APP_EXCEPTION.RAISE_EXCEPTION;
        END;
      END IF;
      OFFSET := OFFSET + W_LEN;
      V_LOOP := V_LOOP + 1;
    END LOOP;
    COMMIT;
  END;

  PROCEDURE NMCC_GZ(P_FILE_ID IN NUMBER
                   ,P_ORG_ID  IN NUMBER) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    -- l_file_id NUMBER;
    ----------------------------
    --w_integer INTEGER;
    W_BLOB    BLOB;
    W_RAW     RAW(10);
    W_BUFF    VARCHAR2(30000);
    W_LINE    VARCHAR2(30000);
    W_LEN     INTEGER;
    EOFSW     BOOLEAN := FALSE;
    OFFSET    INTEGER;
    W_B_LEN   NUMBER := 0;
    W_NUM     NUMBER;
    W_SEQ     NUMBER := 0;
    V_LOOP    NUMBER := 0;
    V_TYPE    VARCHAR2(50);
    V_PD_NAME VARCHAR2(50);
    V_PD_SUB  VARCHAR2(50);
    V_RE      NUMBER;
    V_TMP     NUMBER;
    ----------------------------
    I       NUMBER;
    LINEBUF VARCHAR2(30000);
    TYPE COMMA_POS_TYPE IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
    T_COMMA_POS COMMA_POS_TYPE;
    V_NO        VARCHAR2(22);
  BEGIN
    DELETE MIS_WEIGHT.CUX_AMW_PD_INPUT WHERE PD_ID=-518 AND SID=-518;
    COMMIT;
    SELECT FILE_DATA INTO W_BLOB FROM FND_LOBS WHERE FILE_ID = P_FILE_ID;
    OFFSET := 1;
    V_LOOP := 0;
    LOOP
      EXIT WHEN EOFSW;
      W_SEQ   := W_SEQ + 1;
      W_RAW   := UTL_RAW.CAST_TO_RAW(CHR(10));
      W_NUM   := DBMS_LOB.INSTR(W_BLOB, W_RAW, OFFSET, 1);
      W_LEN   := W_NUM - W_B_LEN;
      W_B_LEN := W_NUM;
      IF W_NUM = 0 THEN
        W_LEN := 20000;
        EOFSW := TRUE;
      END IF;
      BEGIN
        DBMS_LOB.READ(W_BLOB, W_LEN, OFFSET, W_BUFF);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        WHEN OTHERS THEN
          RAISE;
      END;
      W_LINE := UTL_RAW.CAST_TO_VARCHAR2(W_BUFF);
      SELECT REPLACE(W_LINE, CHR(10), NULL) INTO W_LINE FROM DUAL;
      SELECT REPLACE(W_LINE, CHR(13), NULL) INTO W_LINE FROM DUAL;
      ------------------   
      LINEBUF := W_LINE;
      I       := 1;
      WHILE INSTR(LINEBUF, ',', 1, I) > 0 LOOP
        T_COMMA_POS(I) := INSTR(LINEBUF, ',', 1, I);
        I := I + 1;
      END LOOP;
      IF V_LOOP > 0 THEN
        BEGIN
          INSERT INTO MIS_WEIGHT.CUX_AMW_PD_INPUT
            (FILE_NAME
            ,PD_ID
            ,SID
            ,ITEM_NAME
            ,QTY_SYSTEM
            ,LAST_UPDATE_DATE
            ,LAST_UPDATED_BY
            ,CREATION_DATE
            ,CREATED_BY)
          VALUES
            (P_FILE_ID
            ,-518
            ,-518
            ,TRIM(SUBSTR(LINEBUF, 1, T_COMMA_POS(1) - 1)) --ITEM_NAME
            ,TRIM(SUBSTR(LINEBUF, T_COMMA_POS(1) + 1)) -- E ITEM
            ,SYSDATE
            ,APPS.FND_GLOBAL.USER_ID
            ,SYSDATE
            ,APPS.FND_GLOBAL.USER_ID);
        EXCEPTION
          WHEN OTHERS THEN
            FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
            FND_MESSAGE.SET_TOKEN('ERRNO', -20000);
            FND_MESSAGE.SET_TOKEN('REASON', '导入失败,请确认数据 ' || LINEBUF || SUBSTR(LINEBUF, 1, T_COMMA_POS(1) - 1));
            FND_MESSAGE.SET_TOKEN('ROUTINE', 'CUX_AMW_FILE_INTO');
            ROLLBACK;
            APP_EXCEPTION.RAISE_EXCEPTION;
        END;
      END IF;
      OFFSET := OFFSET + W_LEN;
      V_LOOP := V_LOOP + 1;
    END LOOP;
    COMMIT;
  END;

END CUX_AMW_FILE_INTO;
/

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:2932次
    • 积分:44
    • 等级:
    • 排名:千里之外
    • 原创:2篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章存档
    阅读排行
    评论排行