oracle数组的使用

建类型

create or replace type char_table is table of varchar2(4000);--创建自定义类型脚本

 

包头

create or replace package PriceManager_Vassign is

  Procedure verify;
  Procedure saveData(formKind_in in varchar2, createuser_in in varchar2);

end PriceManager_Vassign;

 

包体

create or replace package body PriceManager_Vassign is

  /* =============================================
  *   FUNCTION 
  *   NAME :verifyCompany
  *
  *   DESCRIPTION: 验证公司代码
  *
  *   ARGUMENT:
  *
  *   
  *
  *   HISTORY:
  *     1.00   2011-11-02   cx   Creation
  * =============================================*/
  function verifyCompany(fv_companycode in varchar2) return varchar2 is
    re number;
  begin
    select count(*)
      into re
      from plant2item
     where companycode = fv_companycode;
    if re = 0 then
      return '公司：' || fv_companycode || '不存在;';
    else
      return '';
    end if;
  end verifyCompany;
  /* =============================================
  *   FUNCTION 
  *   NAME :verifyPlant
  *
  *   DESCRIPTION: 验证工厂代码
  *
  *   ARGUMENT:
  *
  *   
  *
  *   HISTORY:
  *     1.00   2011-11-02   cx   Creation
  * =============================================*/
  function verifyPlant(fv_plantcode in varchar2) return varchar2 is
    re number;
  begin
    select count(*) into re from plant where plantcode = fv_plantcode;
    if re = 0 then
      return '工厂：' || fv_plantcode || '不存在;';
    else
      return '';
    end if;
  end verifyPlant;
  /* \* =============================================
  *   FUNCTION 
  *   NAME :verifyItem
  *
  *   DESCRIPTION: 验证物料是否存在和是否重复
  *
  *   ARGUMENT:
  *
  *   
  *
  *   HISTORY:
  *     1.00   2011-11-02   cx   Creation
  * =============================================*\
  function verifyItem(fv_itemcode in varchar2) return varchar2 is
    re number;
  begin
    select count(*) into re from plant2item where itemcode = fv_itemcode;
    if re = 0 then
      return '该物料不存在于工厂物料;';
    else
      select count(*)
        into re
        from vendorvassignbatch vvb
       where vvb.itemcode = fv_itemcode
         and vvb.batchid =
             (select max(y.batchid) from vendorvassignbatch y);
      if re > 1 then
        return '物料有重复的供货比例;';
      else
        return '';
      end if;
    end if;
  end verifyItem;*/

  /* =============================================
  *   FUNCTION 
  *   NAME :verifyItem
  *
  *   DESCRIPTION: 验证物料是否存在和是否重复
  *
  *   ARGUMENT:
  *
  *   
  *
  *   HISTORY:
  *     1.00   2011-11-02   cx   Creation
  * =============================================*/
  function verifyItem(fv_itemcode in varchar2) return varchar2 is
    re              number;
    isUserMsg       varchar2(3);
    v_itemname      varchar2(400);
    v_itemnameCount number;
  begin
    select count(*) into re from plant2item where itemcode = fv_itemcode;
    if re = 0 then
      select count(*)
        into v_itemnameCount
        from vendorvassignbatch t
       where t.batchid = (select max(y.batchid) from vendorvassignbatch y)
         and t.itemcode = fv_itemcode;
      if v_itemnameCount <> 0 then
        v_itemname := '';
        select distinct t.itemname
          into v_itemname
          from vendorvassignbatch t
         where t.batchid =
               (select max(y.batchid) from vendorvassignbatch y)
           and t.itemcode = fv_itemcode;
      
        return '该物料，物料描述为【' || v_itemname || '】不存在于工厂物料;';
      else
        return '该物料不存在于工厂物料;';
      end if;
    else
      /* select bbkedu_po_pkg.get_itemvendorstatus_flag@BBKERP('' ||
                                                            fv_itemcode || '',
                                                            null,
                                                            106)
        into isUserMsg
        from dual;
      if isUserMsg = 'Y' then
        return '该物料被在erp中被禁用或限制，不能进行供货比例调整';
      else*/
      select count(*)
        into re
        from vendorvassignbatch vvb
       where vvb.itemcode = fv_itemcode
         and vvb.batchid =
             (select max(y.batchid) from vendorvassignbatch y);
      if re > 1 then
        return '物料有重复的供货比例;';
      else
        return '';
        /* end if;*/
      end if;
    end if;
  end verifyItem;
  /*
  function verifyItem(fv_itemcode in varchar2) return varchar2 is
      re number;
    begin
      select count(*) into re from plant2item where itemcode = fv_itemcode;
      if re = 0 then
        return '该物料不存在于工厂物料;';
      else
        select count(*)
          into re
          from vendorvassignbatch vvb
         where vvb.itemcode = fv_itemcode
           and vvb.batchid =
               (select max(y.batchid) from vendorvassignbatch y);
        if re > 1 then
          return '物料有重复的供货比例;';
        else
          return '';
        end if;
      end if;
    end verifyItem;*/

  /* =============================================
  *   FUNCTION 
  *   NAME :verifyItem
  *
  *   DESCRIPTION: 验证生效日期
  *
  *   ARGUMENT:
  *
  *   
  *
  *   HISTORY:
  *     1.00   2011-11-02   cx   Creation
  * =============================================*/
  function verifyEffectiveDate(fv_EffectiveDate in date) return varchar is
  begin
    if fv_EffectiveDate <
       to_date(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') then
      return '生效日期不能小于当前日期;';
    else
      return '';
    end if;
  end verifyEffectiveDate;

  /* =============================================
  *   FUNCTION 
  *   NAME :verifyItem
  *
  *   DESCRIPTION: 验证失效日期
  *
  *   ARGUMENT:
  *
  *   
  *
  *   HISTORY:
  *     1.00   2011-11-02   cx   Creation
  * =============================================*/
  function verifyIneffectiveDate(fv_EffectiveDate   in date,
                                 fv_ineffectivedate in date) return varchar is
  begin
    if fv_EffectiveDate > fv_ineffectivedate then
      return '生效日期大于失效日期;';
    else
      return '';
    end if;
  end verifyIneffectiveDate;
  /* =============================================
  *   FUNCTION 
  *   NAME :verifyBuyer
  *
  *   DESCRIPTION: 验证采购员
  *
  *   ARGUMENT:
  *
  *   
  *
  *   HISTORY:
  *     1.00   2011-11-02   cx   Creation
  * =============================================*/
  function verifyBuyer(fv_buyer in varchar2) return varchar is
    re number;
  begin
    select count(*)
      into re
      from sysuser sysu
     where sysu.USERNAME = fv_buyer
       and sysu.usertype = 'Buyer';
    if re = 0 then
      return '采购员' || fv_buyer || '不存在';
    else
      return '';
    end if;
  end verifyBuyer;
  /* =============================================
  *   FUNCTION 
  *   NAME :verifyHadVassign
  *
  *   DESCRIPTION: 验证供货比例是否写有供应商
  *
  *   ARGUMENT:
  *
  *   
  *
  *   HISTORY:
  *     1.00   2011-11-02   cx   Creation
  * =============================================*/
  function verifyHadVassign(fv_vassign in varchar2) return varchar is
    re number;
  begin
    re := 0;
  
    re := instr(fv_vassign, '=');
    if re < 1 then
      return '供货比例为空或者没有写等号;';
    else
      return '';
    end if;
  end verifyHadVassign;
  /* =============================================
  *   FUNCTION 
  *   NAME :verifyIsVendor
  *
  *   DESCRIPTION: 验证供应商是否存在于货源下
  *
  *   ARGUMENT:
  *
  *   
  *
  *   HISTORY:
  *     1.00   2011-11-02   cx   Creation
  * =============================================*/
  function verifyIsVendor(fv_vendorCode in varchar2,
                          fv_Itemcode   in varchar2) return number is
    re number;
  begin
    select count(vend.vendorcode)
      into re
      from plantitem2vendor          vend,
           po_vendors@bbkerp         pv,
           mtl_system_items_b@bbkerp msi
     where vend.vendorcode = fv_vendorCode
       and vend. ITEMCODE = fv_Itemcode
       and pv.segment1 = vend.vendorcode
       and msi.organization_id = 106
       and msi.segment1 = vend. ITEMCODE
       and not exists
     (SELECT 1
              FROM BBKEDU_MINPO_T@bbkerp BMT
             WHERE (BMT.RESTRICT_FLAG = 'Y' OR BMT.DISABLE_FLAG = 'Y')
               and BMT.Vendor_Id is not null
               and BMT.Vendor_Site_Id is not null
               and BMT.item_id = msi.inventory_item_id
               and BMT.vendor_id = pv.vendor_id);
    return re;
  end verifyIsVendor;

  /*  function verifyIsVendor(fv_vendorCode in varchar2,
                          fv_Itemcode   in varchar2) return number is
    re number;
  begin
    select count(*)
      into re
      from plantitem2vendor vend
     where vend.vendorcode = fv_vendorCode
       and vend. ITEMCODE = fv_Itemcode;
    return re;
  end verifyIsVendor;*/

  /* =============================================
  *   FUNCTION 
  *   NAME :verifyBuyer
  *
  *   DESCRIPTION: 验证供货比例格式，以及一个供应商的的清空下，比例是否漏写，供应商是否存在
  *
  *   ARGUMENT:
  *
  *   
  *
  *   HISTORY:
  *     1.00   2011-11-02   cx   Creation
  * =============================================*/
  function verifyVendor(fv_vassign in varchar2, fv_Itemcode in varchar2)
    return varchar is
    re           number;
    vendors      varchar2(100);
    Msg          varchar2(500);
    vendor_Total number;
    vendors_len  number;
    scales       varchar2(100);
    rev          varchar2(600);
  begin
    vendor_Total := 0;
    vendors_len  := 0;
    Msg          := verifyHadVassign(fv_vassign);
    rev          := '';
    if Msg is null then
    
      vendors := substr(fv_vassign, 1, instr(fv_vassign, '=') - 1);
      scales  := substr(fv_vassign,
                        instr(fv_vassign, '=') + 1,
                        length(fv_vassign));
      if scales is null then
        rev := rev || '供货比例有错：没有写比例';
      else
        if instr(scales, ':') = 0 then
          begin
            if to_number(scales) <> 100 then
              rev := rev || '供货比例有错：比例之和不等于100';
            end if;
          Exception
            When Others Then
              rev := rev || '有不为数字的比例';
          end;
        end if;
      end if;
      if vendors is null then
        rev := rev || '供货比例有错：没有写供应商';
      else
        if instr(vendors, ':') = 0 then
          --只有一个供应商
          --验证供应商是否存在货源下
          if verifyIsVendor(vendors, fv_Itemcode) < 1 then
            rev := rev || vendors || '供应商 被禁用限制下单,或不在货源下';
          end if;
        end if;
      end if;
    end if;
    return rev;
  
  end verifyVendor;

  /* =============================================
  *   FUNCTION 
  *   NAME :verifyBuyer
  *
  *   DESCRIPTION: 验证供验证多个供应商的情况
  *
  *   ARGUMENT:
  *
  *   
  *
  *   HISTORY:
  *     1.00   2011-11-02   cx   Creation
  * =============================================*/
  function verifyMoreVendor(fv_vassign  in varchar2,
                            fv_Itemcode in varchar2) return varchar is
    /*    TYPE vassign IS RECORD(
      venderCode varchar2(100),
      scale      varchar2(100));
     TYPE vassign_array IS TABLE OF vassign INDEX BY BINARY_INTEGER;
    
    v_vassign_array vassign_array;
    v_vassign       vassign;*/
    TYPE scale IS TABLE OF varchar2(100) INDEX BY BINARY_INTEGER;
    a_scale scale;
    TYPE vendor IS TABLE OF varchar2(100) INDEX BY BINARY_INTEGER;
    a_vendor     vendor;
    vendors      varchar2(100);
    scales       varchar2(100);
    str_vendors  varchar2(100);
    str_scales   varchar2(100);
    rev          varchar2(600);
    vendorsCount number;
    vendorsLen   number;
    scalesCount  number;
    scalesLen    number;
    sumscales    number;
  begin
    rev := '';
    if verifyHadVassign(fv_vassign) is null then
      str_vendors  := substr(fv_vassign, 1, instr(fv_vassign, '=') - 1);
      str_scales   := substr(fv_vassign,
                             instr(fv_vassign, '=') + 1,
                             length(fv_vassign));
      vendors      := str_vendors;
      scales       := str_scales;
      vendorsCount := 0;
      vendorsLen   := 0;
      scalesCount  := 0;
      scalesLen    := 0;
      if instr(str_vendors, ':') > 0 then
        loop
          vendorsCount := vendorsCount + 1;
        
          a_vendor(vendorsCount) := substr(str_vendors,
                                           1,
                                           instr(str_vendors, ':') - 1);
          vendorsLen := instr(str_vendors, ':');
          str_vendors := substr(str_vendors,
                                vendorsLen + 1,
                                length(str_vendors) - vendorsLen);
          Exit when instr(str_vendors, ':') = 0;
        end loop;
        vendorsCount := vendorsCount + 1;
        a_vendor(vendorsCount) := str_vendors;
      end if;
    
      if instr(str_scales, ':') > 0 then
        loop
          scalesCount := scalesCount + 1;
          scalesLen := scalesLen + 1;
          a_scale(scalesCount) := substr(str_scales,
                                         1,
                                         instr(str_scales, ':') - 1);
          scalesLen := instr(str_scales, ':');
          str_scales := substr(str_scales,
                               scalesLen + 1,
                               length(str_scales) - scalesLen);
          Exit when instr(str_scales, ':') = 0;
        end loop;
        scalesCount := scalesCount + 1;
        a_scale(scalesCount) := str_scales;
      end if;
      if instr(vendors, ':') > 0 then
        if instr(scales, ':') > 0 then
          if vendorsCount <> scalesCount then
            rev := rev || '供应商代码与比例不匹配';
          else
            begin
              sumscales := 0;
              FOR i IN 1 .. a_scale.count LOOP
              
                sumscales := sumscales + to_number(a_scale(i));
              
              end loop;
            Exception
              When Others Then
                rev := rev || '有不为数字的比例';
            end;
            if sumscales <> 100 then
              rev := rev || '比例之和不等于100';
            end if;
            FOR i IN 1 .. a_vendor.count LOOP
              if verifyIsVendor(a_vendor(i), fv_Itemcode) < 1 then
                rev := rev || '供应商' || a_vendor(i) || ' 被禁用限制下单,或不在货源下 ';
              end if;
            end loop;
          end if;
        end if;
      end if;
    
    end if;
    return rev;
  end verifyMoreVendor;

  /* =============================================
  *   FUNCTION / PROCEDURE
  *   NAME :verify
  *
  *   DESCRIPTION: 供货比例数据验证
  *
  *   ARGUMENT:
  *
  *   RETURN:
  *
  *   HISTORY:
  *      1.00   2011-10-14   cx   Creation
  * =============================================*/
  procedure verify is
    r_vendorvassignbatch vendorvassignbatch%ROWTYPE;
    Msg                  varchar2(1000);
    CURSOR c_vendorvassignbatch IS
      select t.*
        from vendorvassignbatch t
       where t.batchid = (select max(y.batchid) from vendorvassignbatch y);
  BEGIN
  
    OPEN c_vendorvassignbatch;
    LOOP
      Msg := '';
      FETCH c_vendorvassignbatch
        INTO r_vendorvassignbatch;
      Exit when(c_vendorvassignbatch%NOTFOUND);
      --验证公司代码
      Msg := Msg || verifyCompany(r_vendorvassignbatch.companycode);
      --验证工厂代码
      Msg := Msg || verifyPlant(r_vendorvassignbatch.plantcode);
      --验证物料是否存在和是否重复
      Msg := Msg || verifyItem(r_vendorvassignbatch.itemcode);
      --验证生效日期
      Msg := Msg || verifyEffectiveDate(r_vendorvassignbatch.effectivedate);
      --验证失效日期
      Msg := Msg ||
             verifyIneffectiveDate(r_vendorvassignbatch.effectivedate,
                                   r_vendorvassignbatch.ineffectivedate);
      --验证采购员
      Msg := Msg || verifyBuyer(r_vendorvassignbatch.buyer);
      --验证是否写供货比例
      Msg := Msg || verifyHadVassign(r_vendorvassignbatch.itemvassign);
      --验证供应商
      Msg := Msg || verifyVendor(r_vendorvassignbatch.itemvassign,
                                 r_vendorvassignbatch.itemcode);
      Msg := Msg || verifyMoreVendor(r_vendorvassignbatch.itemvassign,
                                     r_vendorvassignbatch.itemcode);
      --插入数据
      if Msg is null then
        update vendorvassignbatch vvb
           set vvb.VERIFYFLAG = 1, vvb.VERIFYMSG = Msg
         where vvb.batchid = r_vendorvassignbatch.batchid
           and vvb.ITEMCODE = r_vendorvassignbatch.itemcode;
      else
        update vendorvassignbatch vvb
           set vvb.VERIFYFLAG = 0, vvb.VERIFYMSG = Msg
         where vvb.batchid = r_vendorvassignbatch.batchid
           and vvb.ITEMCODE = r_vendorvassignbatch.itemcode;
      end if;
    END LOOP;
    CLOSE c_vendorvassignbatch;
  
    commit;
  end;

  Procedure setOldAssign(v_fromno in varchar2, v_itemcode in varchar2) is
  
    V_Result varchar2(255);
  
    vendor varchar2(255);
    scale  varchar2(255);
  
    r_vassignformdetail vassignformdetail%ROWTYPE;
  
    CURSOR c_vassignformdetail IS
      select *
        from vassignformdetail p
       where p.formid = (select max(vf.formid)
                           from vassignform vf
                           left join formhead fh
                             on vf.formno = fh.formno
                           left join flowdefine ff
                             on (ff.formkind = fh.formkind and
                                ff.signlevel = fh.currentlevel)
                          where vf.itemcode = v_itemcode
                            and fh.formstatus = 'Approve'
                            and fh.formkind = 'VassignForm'
                            and vf.formno <> v_fromno)
       order by SEQ;
  
  begin
    vendor := '';
    scale  := '';
    for r_vassignformdetail in c_vassignformdetail loop
      vendor := vendor || r_vassignformdetail.vendorcode;
      vendor := vendor || ':';
      scale  := scale || r_vassignformdetail.assignscale;
      scale  := scale || ':';
    end loop;
    vendor   := substr(vendor, 0, length(vendor) - 1);
    vendor   := vendor || '=';
    scale    := substr(scale, 0, length(scale) - 1);
    V_Result := vendor || scale;
    if V_Result = '=' then
      V_Result := '';
    end if;
    update vassignform v
       set v.oldvassign = V_Result
     where v.formno = v_fromno;
  
  end setOldAssign;

  /* =============================================
  *   FUNCTION / PROCEDURE
  *   NAME :CreateNewSignForm
  *
  *   DESCRIPTION: 送签新单据
  *
  *   ARGUMENT:
  *
  *   RETURN:
  *
  *   HISTORY:
  *      1.00   2011-10-14   Chase.Cai   Creation
  * =============================================*/

  procedure setNewAssign(v_fromno in varchar2) is
    V_Result varchar2(255);
  
    vendor varchar2(255);
    scale  varchar2(255);
  
    r_vassignformdetail vassignformdetail%ROWTYPE;
  
    CURSOR c_vassignformdetail IS
      select *
        from vassignformdetail vf
       where vf.formno = v_fromno
       order by SEQ;
  begin
    vendor := '';
    scale  := '';
    for r_vassignformdetail in c_vassignformdetail loop
      vendor := vendor || r_vassignformdetail.vendorcode;
      vendor := vendor || ':';
      scale  := scale || r_vassignformdetail.assignscale;
      scale  := scale || ':';
    end loop;
    vendor   := substr(vendor, 0, length(vendor) - 1);
    vendor   := vendor || '=';
    scale    := substr(scale, 0, length(scale) - 1);
    V_Result := vendor || scale;
    if V_Result = '=' then
      V_Result := '';
    end if;
    update vassignform v
       set v.newvassign = V_Result
     where v.formno = v_fromno;
  end setNewAssign;

  /* =============================================
  *   FUNCTION / PROCEDURE
  *   NAME :saveData
  *
  *   DESCRIPTION: 数据验证通过写入VASSIGNFORM 和VASSIGNFORMdetail表
  *
  *   ARGUMENT:
  *
  *   RETURN:
  *
  *   HISTORY:
  *      1.00   2011-10-14   cx   Creation
  * =============================================*/
  procedure saveData(formKind_in in varchar2, createuser_in in varchar2) is
    r_vendorvassignbatch vendorvassignbatch%ROWTYPE;
    r_VASSIGNFORMDETAIL  VASSIGNFORMDETAIL%ROWTYPE;
    ErrMsg               varchar2(1024);
    str_index            number;
    str_vendors          varchar2(500);
    str_vendor           varchar2(20);
    vendors_len          number;
    str_VASSIGNs         varchar2(500);
    str_VASSIGN          varchar2(20);
    VASSIGN_len          number;
    vendor_index         number;
    vassign_index        number;
    vendor_Total         number;
    vassign_total        number;
    vassign_sum          number;
    i                    number;
    t                    number;
    str_vassignformid    varchar2(40);
    str_ASSIGNCODE       varchar2(40);
    str_LINEID           varchar2(40);
    maxSEQ               number;
    countMaxSEQ          number;
    SEQIndex             number;
  
    TYPE scale IS TABLE OF varchar2(100) INDEX BY BINARY_INTEGER;
    a_scale scale;
    TYPE vendor IS TABLE OF varchar2(100) INDEX BY BINARY_INTEGER;
    a_vendor vendor;
    vendors  varchar2(100);
    scales   varchar2(100);
  
    str_scales   varchar2(100);
    rev          varchar2(600);
    vendorsCount number;
    vendorsLen   number;
    scalesCount  number;
    scalesLen    number;
    sumscales    number;
    v_dd         number;
    CURSOR c_vendorvassignbatch IS
      select t.*
        from vendorvassignbatch t
       where t.batchid = (select max(y.batchid) from vendorvassignbatch y)
         and verifyflag = 1;
  
    CURSOR c_temp(str_vassignformid2 varchar2, str_ASSIGNCODE2 varchar2) IS
      select t.*
        from VASSIGNFORMDETAIL t
       where FORMID = str_vassignformid2
         and FORMNO = str_ASSIGNCODE2
         and ASSIGNCODE = str_ASSIGNCODE2
       order by ASSIGNSCALE desc;
  BEGIN
  
    for r_vendorvassignbatch in c_vendorvassignbatch LOOP
      --插入主表数据   
      select seq_vassignformid.nextval into str_vassignformid from dual;
      str_ASSIGNCODE := 'VF' || to_char(sysdate, 'yymmdd') ||
                        str_vassignformid;
      insert into VASSIGNFORM
        (FORMID,
         FORMNO,
         VASSIGNCODE,
         EFFECTDATE,
         INVALIDDATE,
         COMPANYCODE,
         PLANTCODE,
         ITEMCODE,
         CREATEUSER,
         CREATEDATE,
         CREATETIME,
         COMMITDATE,
         COMMITTIME,
         FORMKIND,
         REMARK,
         BUYER,
         --REMARK1,
         REMARK2,
         linkitemcode)
      values
        (str_vassignformid,
         str_ASSIGNCODE,
         str_ASSIGNCODE,
         to_number(to_char(r_vendorvassignbatch.effectivedate, 'yyyymmdd')),
         to_number(to_char(r_vendorvassignbatch.ineffectivedate, 'yyyymmdd')),
         r_vendorvassignbatch.COMPANYCODE,
         r_vendorvassignbatch.PLANTCODE,
         r_vendorvassignbatch.ITEMCODE,
         createuser_in,
         to_number(to_char(sysdate, 'yyyymmdd')),
         to_number(to_char(sysdate, 'hh24MMSS')),
         to_number(to_char(sysdate, 'yyyymmdd')),
         to_number(to_char(sysdate, 'hh24MMSS')),
         formKind_in,
         r_vendorvassignbatch.remark1,
         --to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '批量导入',
         r_vendorvassignbatch.buyer,
         --r_vendorvassignbatch.remark1,
         r_vendorvassignbatch.remark2,
         r_vendorvassignbatch.linkitemcode);
    
      ---插入从表           
      str_vendors := substr(r_vendorvassignbatch.itemvassign,
                            1,
                            instr(r_vendorvassignbatch.itemvassign, '=') - 1);
      str_scales  := substr(r_vendorvassignbatch.itemvassign,
                            instr(r_vendorvassignbatch.itemvassign, '=') + 1,
                            length(r_vendorvassignbatch.itemvassign));
    
      if instr(str_vendors, ':') = 0 then
        --一个供应商
        select SEQ_VASSIGNLINEID.nextval into str_LINEID from dual;
        insert into VASSIGNFORMDETAIL
          (ASSIGNSCALE,
           VENDORCODE,
           ASSIGNCODE,
           ASSIGNPERCENT,
           CREATEUSER,
           CREATEDATE,
           CREATETIME,
           FORMID,
           FORMNO,
           LINEID)
        values
          (to_number(str_scales),
           str_vendors,
           str_ASSIGNCODE,
           decode(to_number(str_scales), 0, 0, to_number(str_scales) / 100), --要0.4而不是4
           createuser_in,
           to_number(to_char(sysdate, 'yyyymmdd')),
           to_number(to_char(sysdate, 'hh24MMSS')),
           str_vassignformid,
           str_ASSIGNCODE,
           str_LINEID);
      
      else
        vendorsCount := 0;
        vendorsLen   := 0;
        scalesCount  := 0;
        scalesLen    := 0;
        --清空数组
        a_vendor.DELETE;
        a_scale.DELETE;
        if instr(str_vendors, ':') > 0 then
          loop
            vendorsCount := vendorsCount + 1;
          
            a_vendor(vendorsCount) := substr(str_vendors,
                                             1,
                                             instr(str_vendors, ':') - 1);
            vendorsLen := instr(str_vendors, ':');
            str_vendors := substr(str_vendors,
                                  vendorsLen + 1,
                                  length(str_vendors) - vendorsLen);
            Exit when instr(str_vendors, ':') = 0;
          end loop;
          vendorsCount := vendorsCount + 1;
          a_vendor(vendorsCount) := str_vendors;
        end if;
      
        if instr(str_scales, ':') > 0 then
          loop
            scalesCount := scalesCount + 1;
            scalesLen := scalesLen + 1;
            a_scale(scalesCount) := substr(str_scales,
                                           1,
                                           instr(str_scales, ':') - 1);
            scalesLen := instr(str_scales, ':');
            str_scales := substr(str_scales,
                                 scalesLen + 1,
                                 length(str_scales) - scalesLen);
            Exit when instr(str_scales, ':') = 0;
          end loop;
          scalesCount := scalesCount + 1;
          a_scale(scalesCount) := str_scales;
        end if;
        FOR i IN 1 .. a_vendor.count LOOP
          select SEQ_VASSIGNLINEID.nextval into str_LINEID from dual;
          insert into VASSIGNFORMDETAIL
            (ASSIGNSCALE,
             VENDORCODE,
             ASSIGNCODE,
             ASSIGNPERCENT,
             CREATEUSER,
             CREATEDATE,
             CREATETIME,
             FORMID,
             FORMNO,
             LINEID)
          values
            (to_number(a_scale(i)),
             a_vendor(i),
             str_ASSIGNCODE,
             decode(to_number(a_scale(i)),
                    0,
                    0,
                    to_number(a_scale(i)) / 100),
             createuser_in,
             to_number(to_char(sysdate, 'yyyymmdd')),
             to_number(to_char(sysdate, 'hh24MMSS')),
             str_vassignformid,
             str_ASSIGNCODE,
             str_LINEID);
        
        end loop;
      
      end if;
      ---优先级
      SEQIndex := 1;
      for r_VASSIGNFORMDETAIL in c_temp(str_vassignformid, str_ASSIGNCODE) LOOP
      
        update VASSIGNFORMDETAIL
           set SEQ = SEQIndex
         where FORMID = str_vassignformid
           and FORMNO = str_ASSIGNCODE
           and ASSIGNCODE = str_ASSIGNCODE
           and LINEID = r_VASSIGNFORMDETAIL.Lineid;
        SEQIndex := SEQIndex + 1;
      
      END LOOP;
      ---送签
      insert into FormHead
        (formno,
         formkind,
         Formstatus,
         Createuser,
         Billcode,
         Createdate,
         Createtime,
         Currentlevel)
      values
        (str_ASSIGNCODE,
         formKind_in,
         'Checking',
         createuser_in,
         str_ASSIGNCODE,
         to_number(to_char(sysdate, 'yyyymmdd')),
         to_number(to_char(sysdate, 'hh24MMSS')),
         1);
      commit;
      setOldAssign(str_ASSIGNCODE, r_vendorvassignbatch.itemcode);
      -- dbms_output.put_line('44444444444444444444');
      commit;
      setNewAssign(str_ASSIGNCODE);
      commit;
    END LOOP;
  
    commit;
  
  end saveData;

 

-----------------------------------------
end PriceManager_Vassign;

 

转载于:https://www.cnblogs.com/windback/archive/2012/03/13/2393885.html