一个procedure案例

CREATE OR REPLACE PROCEDURE UPLOAD_PACKING_LIST_MC3090(DN_NO_I             IN VARCHAR2,
                                                       PACKING_LIST_NO_I   IN VARCHAR2,
                                                       SHIP_FULL_I         IN VARCHAR2,
                                                       POST_DATE_I         IN DATE,
                                                       PLANT_CODE_I        IN VARCHAR2,
                                                       SERIAL_NO_PROFILE_I IN VARCHAR2,
                                                       SCHEMA_NAME_I       IN VARCHAR2,
                                                       RES                 OUT VARCHAR2) AS


  TYPE IFSN_DETAIL_RECORD IS RECORD(
    DN_NO              VARCHAR2(100),
    DELTA_SERIAL_NO    VARCHAR2(100),
    CUSTOMER_SERIAL_NO VARCHAR2(100),
    MATERIAL_NO        VARCHAR2(100),
    MO_NUMBER          VARCHAR2(100),
    PALLET_NO          VARCHAR2(100),
    CARTON_NO          VARCHAR2(100),
    DATECODE           VARCHAR2(8),
    BARCODE            VARCHAR2(100),
    REVISION           VARCHAR2(4),
    SERIAL_NO          VARCHAR2(100),
    --PROD_PLANT         VARCHAR2(25),
    C_SN       VARCHAR2(400),
    SHIPPINGSN VARCHAR2(1000));
  TYPE IFSN_KEY_COMP_RECORD IS RECORD(
    DN_NO                  VARCHAR2(100),
    MATERIAL_NO            VARCHAR2(100),
    DELTA_SERIAL_NO        VARCHAR2(100),
    MOTHER_MATERIAL_NO     VARCHAR2(100),
    MOTHER_SERIAL_NO       VARCHAR2(100),
    KEY_MATERIAL_NO        VARCHAR2(100),
    KEY_SERIAL_NO          VARCHAR2(100),
    KEY_CUSTOMER_SERIAL_NO VARCHAR2(100),
    DATECODE               VARCHAR2(8),
    BARCODE                VARCHAR2(100),
    REVISION               VARCHAR2(4),
    LINK_LEVEL             VARCHAR2(4));


  TYPE IFSN_DETAIL_TYPE IS TABLE OF IFSN_DETAIL_RECORD;
  TYPE IFSN_KEY_COMP_TYPE IS TABLE OF IFSN_KEY_COMP_RECORD;
  WJPIV          CONSTANT VARCHAR2(40) := 'WJ_PI';
  WJPROJ         CONSTANT VARCHAR2(40) := 'WJ_PIV';
  /*WJPROJ         CONSTANT VARCHAR2(40) := 'WJ_PROJ';*/
  SN_KEY         CONSTANT VARCHAR2(10) := '#S/N#';
  PROJ_FLAG      CONSTANT VARCHAR2(40) := '58';
  DS_MODEL_START CONSTANT VARCHAR2(10) := 'VN-';


  SQLVAR                VARCHAR2(4000); --sql  語句存儲
  COUNT_TEMP            NUMBER;
  FLOWCODETEMP          NUMBER := 0;
  NODE_FLOWCODETEMP     NUMBER := 0;
  IFSN_DETAIL_TEMP      IFSN_DETAIL_TYPE;
  IFSN_KEY_COMP_TEMP    IFSN_KEY_COMP_TYPE;
  TEMPVAR               VARCHAR2(1000);
  prod_tmp              VARCHAR2(100);
  SCREEN_ACTUAL_MODEL_P VARCHAR2(30);
  LINK_LEVEL_P          NUMBER := 2;


  ifsn_key_comp_KEY_MATERIAL_NO  VARCHAR2(100);
  ifsn_key_comp_BARCODE          VARCHAR2(100);
  ifsn_key_comp_KEY_CUSTOMER_NO  VARCHAR2(100);
  ifsn_key_comp_KEY_SERIAL_NO    VARCHAR2(100); 


BEGIN
  --暫時在這裡判斷它的產品廠是否為空
  SQLVAR := ' select distinct  T3.PROD_PLANT FROM ' || SCHEMA_NAME_I ||
            '.R_WIP_TRACKING_T T1 LEFT JOIN ' || SCHEMA_NAME_I ||
            '.R_MO_BASE_T T3' ||
            ' ON T1.MO_NUMBER=T3.MO_NUMBER AND T1.MODEL_NAME=T3.MODEL_NAME ' ||
            '  WHERE  SHIP_NO=:ship_no and rownum<2';
  EXECUTE IMMEDIATE (SQLVAR)
    INTO prod_tmp
    USING PACKING_LIST_NO_I;
  IF prod_tmp IS NOT NULL THEN
    IF (prod_tmp = 'MCIS') OR (prod_tmp = 'PVI') OR (prod_tmp = 'TPS') OR
       (prod_tmp = 'DS') THEN
      UPLOAD_PACKING_LIST_MC3090_NEW(DN_NO_I,
                                     PACKING_LIST_NO_I,
                                     SHIP_FULL_I,
                                     POST_DATE_I,
                                     PLANT_CODE_I,
                                     SERIAL_NO_PROFILE_I,
                                     SCHEMA_NAME_I,
                                     RES);
      RETURN;
    END IF;
  END IF;


  --IF SERIAL_NO_PROFILE_I ='Y' THEN
  IF SHIP_FULL_I <> '1' THEN
    res := 'PACKING出貨數未滿';
    RETURN;
  END IF;
  --查找是否存在著個DO號
  SQLVAR := 'SELECT count(0) FROM ' || SCHEMA_NAME_I ||
            '.IFSN_MASTER WHERE DN_NO=:1 AND SCHEMA_NAME=:2 AND ROWNUM=1';
  EXECUTE IMMEDIATE (SQLVAR)
    INTO COUNT_TEMP
    USING DN_NO_I, SCHEMA_NAME_I;
  --如果沒有數據
  IF COUNT_TEMP > 0 THEN
    RES := 'EXIST';
    RETURN;
  END IF;


  --如果沒有數據 插入一筆數據到ifsn_master
  SQLVAR := 'INSERT INTO ' || SCHEMA_NAME_I ||
            '.IFSN_MASTER(  dn_no , plant,posting  ,actual_posting_date , status ,
               sap_message , create_date , schema_name) VALUES(:1,:2,''Y'',
               TO_DATE(TO_CHAR(:3, ''yyyymmdd''), ''yyyy-mm-dd''),''-'','''',SYSDATE,:4)';
  EXECUTE IMMEDIATE SQLVAR
    USING DN_NO_I, PLANT_CODE_I, POST_DATE_I, SCHEMA_NAME_I;


  SQLVAR := 'select :do,T1.SHIPPING_SN,T1.SHIPPING_SN,T1.MODEL_NAME,T1.MO_NUMBER,' ||
            'PALLET_NO,CARTON_NO,NVL(T2.datecode,T4.datecode),T1.shipping_sn,NVL(T2.revision,T4.revision),
            T1.SERIAL_NUMBER,t1.SERIAL_NUMBER,T1.shipping_sn FROM ' ||
            SCHEMA_NAME_I || '.R_WIP_TRACKING_T T1 LEFT JOIN  ' ||
            SCHEMA_NAME_I ||
            '.r_custsn_datecode_t T2 ON T1.SHIPPING_SN=T2.SHIPPING_SN  LEFT JOIN ' ||
            SCHEMA_NAME_I ||
            '.r_custsn_datecode_t T4 ON T1.SERIAL_NUMBER=T4.SHIPPING_SN  ' ||
            ' WHERE  SHIP_NO=:ship_no';
  --全部查找數據批量插入到ifsn_detail_temp臨時存儲位置
  EXECUTE IMMEDIATE SQLVAR BULK COLLECT
    INTO IFSN_DETAIL_TEMP
    USING DN_NO_I, PACKING_LIST_NO_I;


  --循環數據
  FOR I IN IFSN_DETAIL_TEMP.FIRST .. IFSN_DETAIL_TEMP.LAST LOOP
    FLOWCODETEMP := FLOWCODETEMP + 1;
    --如果是wjproj的產品，開始就使用serialno替換掉客戶序號的值
    IF UPPER(SCHEMA_NAME_I) = WJPROJ THEN
      IF SUBSTR(IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO, 0, 2) = PROJ_FLAG THEN
        IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO := IFSN_DETAIL_TEMP(I).SERIAL_NO;
        IFSN_DETAIL_TEMP(I).CUSTOMER_SERIAL_NO := IFSN_DETAIL_TEMP(I)
                                                  .SERIAL_NO;
        IFSN_DETAIL_TEMP(I).BARCODE := IFSN_DETAIL_TEMP(I).SERIAL_NO;
      END IF;
    END IF;


    --如果是吳江DS廠，判斷機種是以'VN-'開頭，客戶序號的值為虛擬值，DN NO + 8位流水碼
      --ADD AZOTHER.NIE 2015/6/6
      IF (upper(SCHEMA_NAME_I) = WJPROJ) AND
        (SUBSTR(IFSN_DETAIL_TEMP(I).MATERIAL_NO, 0, 3) = DS_MODEL_START) THEN
        IFSN_DETAIL_TEMP(I).CUSTOMER_SERIAL_NO := IFSN_DETAIL_TEMP(I).DN_NO ||
                                               LPAD(TO_CHAR(FLOWCODETEMP), 8, 0);
        IFSN_DETAIL_TEMP(I).BARCODE := IFSN_DETAIL_TEMP(I).DN_NO ||
                                               LPAD(TO_CHAR(FLOWCODETEMP), 8, 0);
        IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO := IFSN_DETAIL_TEMP(I).DN_NO ||
                                               LPAD(TO_CHAR(FLOWCODETEMP), 8, 0);
       END IF;


    --如果輸入的schema為wj_piv
    IF UPPER(SCHEMA_NAME_I) = WJPIV THEN
      TEMPVAR := SUBSTR(IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO,
                        LENGTH(IFSN_DETAIL_TEMP(I).MATERIAL_NO) + 1,
                        LENGTH(IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO));
      IFSN_DETAIL_TEMP(I).BARCODE := TEMPVAR;


      IF LENGTH(TEMPVAR) > 18 THEN
        --除去all個機種，從第22位開始截取18位長度的數據作為customer sn
        IFSN_DETAIL_TEMP(I).CUSTOMER_SERIAL_NO := SUBSTR(TEMPVAR, 22, 18);
        --將do右邊填充8為流水數據作為delta_serial_no
        IF LENGTH(IFSN_DETAIL_TEMP(I).CUSTOMER_SERIAL_NO) > 18 THEN
          IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO := IFSN_DETAIL_TEMP(I)
                                                 .DN_NO || LPAD(TO_CHAR(FLOWCODETEMP),
                                                                8,
                                                                0);
        ELSE
          IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO := IFSN_DETAIL_TEMP(I)
                                                 .CUSTOMER_SERIAL_NO;
        END IF;
      ELSE
        IFSN_DETAIL_TEMP(I).CUSTOMER_SERIAL_NO := TEMPVAR;
        IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO := TEMPVAR;
      END IF;


    ELSE
      -- 如果其shipping sn 長度大於18位
      IF LENGTH(IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO) > 18 THEN


        --如果不為wj_piv，判斷是否為泰國的二維條碼
        IF INSTR(IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO, SN_KEY) > 0 THEN


          IFSN_DETAIL_TEMP(I).CUSTOMER_SERIAL_NO := SUBSTR(IFSN_DETAIL_TEMP(I)
                                                           .DELTA_SERIAL_NO,
                                                           INSTR(IFSN_DETAIL_TEMP(I)
                                                                 .DELTA_SERIAL_NO,
                                                                 SN_KEY) + 5,
                                                           18);
          IF LENGTH(IFSN_DETAIL_TEMP(I).CUSTOMER_SERIAL_NO) > 18 THEN
            IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO := IFSN_DETAIL_TEMP(I)
                                                   .DN_NO ||
                                                    LPAD(TO_CHAR(FLOWCODETEMP),
                                                         8,
                                                         0);
          ELSE
            IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO := IFSN_DETAIL_TEMP(I)
                                                   .CUSTOMER_SERIAL_NO;
          END IF;


        ELSE


          --將do右邊填充8為流水數據作為delta_serial_no
          IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO := IFSN_DETAIL_TEMP(I)
                                                 .DN_NO || LPAD(TO_CHAR(FLOWCODETEMP),
                                                                8,
                                                                0);
        END IF;
      END IF;


    END IF;


    SQLVAR := 'insert into ' || SCHEMA_NAME_I ||
              '.IFSN_DETAIL(  dn_no , delta_serial_no   , customer_serial_no ,material_no ,pallet_no          ,
  carton_no,datecode ,barcode ,revision) VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9)';
    EXECUTE IMMEDIATE SQLVAR
      USING IFSN_DETAIL_TEMP(I).DN_NO, IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO, IFSN_DETAIL_TEMP(I).CUSTOMER_SERIAL_NO, IFSN_DETAIL_TEMP(I).MATERIAL_NO, IFSN_DETAIL_TEMP(I).PALLET_NO, IFSN_DETAIL_TEMP(I).CARTON_NO, IFSN_DETAIL_TEMP(I).DATECODE, IFSN_DETAIL_TEMP(I).BARCODE, IFSN_DETAIL_TEMP(I).REVISION;


    --需要將每個臺達序號所Link的所有板子資料Insert到ifsn_key_comp表中


    SQLVAR := ' SELECT  :DO, :MODEL,:SHIPPING_SN,S.DELTA_MATERIAL_NO,S.DELTA_SERIAL_NO,S.KEY_MATERIAL_NO, '||
         ' S.KEY_SERIAL_NO, S.KEY_SERIAL_NO ,:DATECODE,S.KEY_SERIAL_NO,:REVISION,S.LINK_LEVEL FROM ( '||
         ' SELECT M.DELTA_MATERIAL_NO,M.DELTA_SERIAL_NO,M.KEY_MATERIAL_NO,M.KEY_SERIAL_NO,M.LINK_LEVEL FROM ( '||
         ' SELECT DISTINCT T.PARENT_SN AS "DELTA_SERIAL_NO",T.MAIN_MATERIAL_NO AS "DELTA_MATERIAL_NO", '||
         ' T.MODEL_NAME AS "KEY_MATERIAL_NO",T.SN AS "KEY_SERIAL_NO", '||
         ' LEVEL AS "LINK_LEVEL" FROM (SELECT T1.*,T2.SN PARENT_SN,T2.MODEL_NAME MAIN_MATERIAL_NO FROM '||
           SCHEMA_NAME_I || '.R_SN_LINK_RELATION_WIP_T T1  LEFT JOIN '||
           SCHEMA_NAME_I || '.R_SN_LINK_RELATION_WIP_T T2 ON T1.ID_SN=T2.ID) T CONNECT BY NOCYCLE PRIOR '||
         ' T.SN = T.PARENT_SN START WITH T.SN=:SN) M WHERE M.LINK_LEVEL>1) S order by S.LINK_LEVEL ';


    EXECUTE IMMEDIATE SQLVAR BULK COLLECT
      INTO IFSN_KEY_COMP_TEMP
      USING DN_NO_I, IFSN_DETAIL_TEMP(I).MATERIAL_NO, IFSN_DETAIL_TEMP(I).SHIPPINGSN, '', '', IFSN_DETAIL_TEMP(I).C_SN;


    IF IFSN_KEY_COMP_TEMP.COUNT = 0 THEN
      --判斷是否為'DS'廠    ADD AZOTHER.NIE
          if (upper(SCHEMA_NAME_I) = WJPROJ) AND
            --判斷大板機種是以'VN-'開頭，
           (SUBSTR(IFSN_DETAIL_TEMP(I).MATERIAL_NO, 0, 3) = DS_MODEL_START) then
           SQLVAR :='SELECT COUNT(0) FROM '|| SCHEMA_NAME_I || '.C_VW_SCREEN_MODEL_RELATION_T SCR WHERE '||
                     ' SCR.MODEL_NAME=:MODEL AND SCR.MO_NUMBER=:MO ';
            EXECUTE IMMEDIATE (SQLVAR)
              INTO COUNT_TEMP
            USING IFSN_DETAIL_TEMP(I).MATERIAL_NO,IFSN_DETAIL_TEMP(I).MO_NUMBER;
            IF COUNT_TEMP >0 THEN  
            SQLVAR :=' select SCR.ACTUAL_MODEL_NAME from ' || SCHEMA_NAME_I || '.C_VW_SCREEN_MODEL_RELATION_T SCR WHERE '||
                     ' SCR.MODEL_NAME=:MODEL AND SCR.MO_NUMBER=:MO ';
            EXECUTE IMMEDIATE (SQLVAR)
              INTO SCREEN_ACTUAL_MODEL_P
              USING IFSN_DETAIL_TEMP(I).MATERIAL_NO,IFSN_DETAIL_TEMP(I).MO_NUMBER;
            ELSE
                SQLVAR :=' select SCR.SCREEN_ACTUAL_MODEL from ' || SCHEMA_NAME_I || '.c_screen_model_relation_t SCR WHERE '||
                     ' SCR.SCREEN_SAP_MODEL=:MODEL ';
            EXECUTE IMMEDIATE (SQLVAR)
              INTO SCREEN_ACTUAL_MODEL_P
              USING IFSN_DETAIL_TEMP(I).MATERIAL_NO,IFSN_DETAIL_TEMP(I).MO_NUMBER;
             END IF;
           /* EXCEPTION 
              WHEN NO_DATA_FOUND then
                SQLVAR :=' select SCR.SCREEN_ACTUAL_MODEL from ' || SCHEMA_NAME_I || '.c_screen_model_relation_t SCR WHERE '||
                     ' SCR.SCREEN_SAP_MODEL=:MODEL ';
            EXECUTE IMMEDIATE (SQLVAR)
              INTO SCREEN_ACTUAL_MODEL_P
              USING IFSN_DETAIL_TEMP(I).MATERIAL_NO,IFSN_DETAIL_TEMP(I).MO_NUMBER;
              END;*/
              ifsn_key_comp_KEY_MATERIAL_NO := SCREEN_ACTUAL_MODEL_P;
              ifsn_key_comp_BARCODE := IFSN_DETAIL_TEMP(I).shippingsn;
             --KEY_CUSTOMER_SERIAL_NO的值為barcode值，如果有截取，用截取之後的值
              ifsn_key_comp_KEY_CUSTOMER_NO := ifsn_key_comp_BARCODE;


               --如果barcode的值大於18嗎，CUSTOMER_SERIAL_NO的值用虛擬值 ，否則用barcode的值
              if length(ifsn_key_comp_KEY_CUSTOMER_NO) > 18 then
                 ifsn_key_comp_KEY_SERIAL_NO := IFSN_DETAIL_TEMP(I)
                                                             .DN_NO ||
                                                              LPAD(TO_CHAR(FLOWCODETEMP),
                                                                   8,
                                                                   0);
              else
                 ifsn_key_comp_KEY_SERIAL_NO := ifsn_key_comp_KEY_CUSTOMER_NO;
              END IF;


              SQLVAR := 'insert into ' || SCHEMA_NAME_I ||
                        '.IFSN_KEY_COMP(dn_no ,material_no,
                        delta_serial_no , key_material_no  ,key_serial_no, key_customer_serial_no
                        ,datecode ,barcode, revision) values(:1,:2,:3,:4,:5,:6,:7,:8,:9)';


              --dbms_output.put_line(ifsn_key_comp_temp(i).delta_serial_no);
              EXECUTE IMMEDIATE SQLVAR
                USING dn_no_i, IFSN_DETAIL_TEMP(I).MATERIAL_NO, IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO, IFSN_KEY_COMP_KEY_MATERIAL_NO, IFSN_KEY_COMP_KEY_SERIAL_NO, ifsn_key_comp_KEY_CUSTOMER_NO, '', IFSN_KEY_COMP_BARCODE, '';
            commit;
          end if;
      NULL;
    ELSE
      --處理子序號裏面的數據，并插入到ifsn_key_comp


      FOR J IN IFSN_KEY_COMP_TEMP.FIRST .. IFSN_KEY_COMP_TEMP.LAST LOOP
        NODE_FLOWCODETEMP := NODE_FLOWCODETEMP + 1;
        --查找子板的機種是否是SAP機種(不再在IFSN_KEY_COMP_RECORD 上面添加?位，重新查找?据?，
        --?然不?优化，但是?据能保?准确)  --2015/8/28 Cancel Function of just sending SAP model keycompoment by olivia.huang
        /*sqlVar := 'select count(0)  from ' || SCHEMA_NAME_I ||
                  '.c_model_desc_t where model_name=:model_name and sap_model_flag=1';
        EXECUTE IMMEDIATE sqlVar
          INTO COUNT_TEMP
          USING IFSN_KEY_COMP_TEMP(J).KEY_MATERIAL_NO;
        IF COUNT_TEMP = 0 THEN
          NULL;
        ELSE*/ --2015/8/28 Cancel Function of just sending SAP model keycompoment by olivia.huang


          --barcode就是子板的條碼,保持查詢語句的數據不變


          --key_customer_serial_no 未來如果是二維需要截取（條件未定）；現在是一維條碼就是barcode


          --key_serial_no： 如果key_customer_serial_no長度>18則為虛擬條碼，右填充流水號碼加上do號碼，
          --else 則為key_customer_serial_no


          IF LENGTH(IFSN_KEY_COMP_TEMP(J).KEY_CUSTOMER_SERIAL_NO) > 18 THEN
            IFSN_KEY_COMP_TEMP(J).KEY_SERIAL_NO := LPAD(TO_CHAR(NODE_FLOWCODETEMP),
                                                        8,
                                                        0) || IFSN_KEY_COMP_TEMP(J)
                                                  .DN_NO;
          END IF;
          
          --DELTA_SERIAL_NO的長度>18則為虛擬條碼，右填充流水號碼加上do號碼
          --否則，用DELTA_SERIAL_NO。(不用barcode作為長度判斷是因為有子母板link時，第一條的DELTA_SERIAL_NO
          --不等於barcode，只有從第二條開始DELTA_SERIAL_NO才等於barcode)
          --add by azother.nie
          IF IFSN_KEY_COMP_TEMP(J).LINK_LEVEL = 2 THEN
            --如果是第二階link,取IFSN_DETAIL中的serial_no作為母板序號  azother.nie
             IFSN_KEY_COMP_TEMP(J).MOTHER_SERIAL_NO := IFSN_DETAIL_TEMP(I).DELTA_SERIAL_NO;
             IFSN_KEY_COMP_TEMP(J).MOTHER_MATERIAL_NO := IFSN_DETAIL_TEMP(I).MATERIAL_NO; 
          ELSE
            IF IFSN_KEY_COMP_TEMP(J).LINK_LEVEL = LINK_LEVEL_P THEN
              --如果link階層沒變，取上條母板序號做為母板序號   azother.nie
               IFSN_KEY_COMP_TEMP(J).MOTHER_SERIAL_NO := IFSN_KEY_COMP_TEMP(J-1).MOTHER_SERIAL_NO;
               IFSN_KEY_COMP_TEMP(J).MOTHER_MATERIAL_NO := IFSN_KEY_COMP_TEMP(J-1).MOTHER_MATERIAL_NO;
            ELSE
              IF IFSN_KEY_COMP_TEMP(J).LINK_LEVEL <> LINK_LEVEL_P THEN 
                --如果link階層變了，取上條的子板序號作為母板序號   azother.nie
               IFSN_KEY_COMP_TEMP(J).MOTHER_SERIAL_NO := IFSN_KEY_COMP_TEMP(J-1).KEY_SERIAL_NO;
               IFSN_KEY_COMP_TEMP(J).MOTHER_MATERIAL_NO := IFSN_KEY_COMP_TEMP(J-1).KEY_MATERIAL_NO;
              END IF;   
               LINK_LEVEL_P := IFSN_KEY_COMP_TEMP(J).LINK_LEVEL;
            END IF;   
          END IF;
              
          IF LENGTH(IFSN_KEY_COMP_TEMP(J).MOTHER_SERIAL_NO) > 18 THEN
            IFSN_KEY_COMP_TEMP(J).MOTHER_SERIAL_NO := LPAD(TO_CHAR(NODE_FLOWCODETEMP),
                                                        8,
                                                        0) || IFSN_KEY_COMP_TEMP(J)
                                                  .DN_NO;
          END IF;


          SQLVAR := 'insert into ' || SCHEMA_NAME_I ||
                    '.IFSN_KEY_COMP(dn_no ,material_no,
          delta_serial_no , key_material_no  ,key_serial_no, key_customer_serial_no
          ,datecode ,barcode, revision) values(:1,:2,:3,:4,:5,:6,:7,:8,:9)';


          --dbms_output.put_line(ifsn_key_comp_temp(i).delta_serial_no);
          EXECUTE IMMEDIATE SQLVAR
            USING IFSN_KEY_COMP_TEMP(J).DN_NO, IFSN_KEY_COMP_TEMP(J).MOTHER_MATERIAL_NO, IFSN_KEY_COMP_TEMP(J).MOTHER_SERIAL_NO, IFSN_KEY_COMP_TEMP(J).KEY_MATERIAL_NO, IFSN_KEY_COMP_TEMP(J).KEY_SERIAL_NO, IFSN_KEY_COMP_TEMP(J).KEY_CUSTOMER_SERIAL_NO, IFSN_KEY_COMP_TEMP(J).DATECODE, IFSN_KEY_COMP_TEMP(J).BARCODE, IFSN_KEY_COMP_TEMP(J).REVISION;
        --END IF;
      END LOOP;
    END IF;
  END LOOP;


  --超過7天的數據刪除
  SQLVAR := 'DELETE FROM ' || SCHEMA_NAME_I ||
            '.IFSN_DETAIL WHERE DN_NO IN (SELECT DN_NO FROM ' ||
            SCHEMA_NAME_I ||
            '.IFSN_MASTER WHERE SYSDATE-CREATE_DATE>7 AND SCHEMA_NAME=''' ||
            SCHEMA_NAME_I || ''' AND STATUS=''S'')';
  EXECUTE IMMEDIATE SQLVAR;
  SQLVAR := 'DELETE FROM ' || SCHEMA_NAME_I ||
            '.IFSN_MASTER WHERE SYSDATE-CREATE_DATE>7 AND SCHEMA_NAME=''' ||
            SCHEMA_NAME_I || ''' AND STATUS=''S''';
  EXECUTE IMMEDIATE SQLVAR;
  SQLVAR := 'DELETE FROM ' || SCHEMA_NAME_I ||
            '.IFSN_KEY_COMP WHERE DN_NO IN (SELECT DN_NO FROM ' ||
            SCHEMA_NAME_I ||
            '.IFSN_MASTER WHERE SYSDATE-CREATE_DATE>7 AND SCHEMA_NAME=''' ||
            SCHEMA_NAME_I || ''' AND STATUS=''S'')';
  EXECUTE IMMEDIATE SQLVAR;
  --all ok;
  RES := 'OK';
  COMMIT;
EXCEPTION
  --modified by xiaoq 2015.1.9
  --將信息添加在res
  WHEN OTHERS THEN
    IF length(SQLERRM) > 3000 THEN
      RES := substr(SQLERRM, 1, 3000);
    END IF;
    RES := 'FAIL_SF' || SQLCODE || ',' || RES || ',' ||
           dbms_utility.format_error_backtrace;
    ROLLBACK;
END;