建类型

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;