使用记录组类型实例：接口导入

DECLARE --数据导入接口
vn_mas_pk_no number(20);
vn_item_pk_no number(20);
vs_p1 varchar2(256);
vs_p2 varchar2(256);
vs_p3 varchar2(256);
vs_p4 varchar2(256);
vs_p5 varchar2(256);
vs_p6 varchar2(256);
vs_p7 varchar2(256);
vs_p8 varchar2(256);
vs_p9 varchar2(256);
vs_p10 varchar2(256);
vs_delimiter varchar2(256);
type vc_arr is table of varchar2(2000) index by binary_integer;
vs_d vc_arr;
vn_no number;
vn_start number;
vs_line varchar2(4000);
vn_err_count number;
vs_mg varchar2(256) := null;
vn_dataline_start number := 1;
expt_dl exception;
– DEFINE OTHER VAR BELOW
vn_data_cols number := 56;
vn_cols_needed number := 8;
vb_line_data_err boolean;
vn_rownum number;
vn_line_length number;
vn_temp_string varchar2(4000);
vs_dum char(1);
type type_raw_data is table of vc_arr index by binary_integer;
v_r_d type_raw_data;
same_data_cols number;
vs_err_data varchar2(8) := ‘’;
------------ 3380 begin----------------
type rec_type is record (stk_c varchar2(32), mat_c varchar2(32), size_c varchar2(32));
type tab_type is table of rec_type index by binary_integer;
my_tb tab_type;
cn number := 0;

v_dp_stk number := 0;
v_dp_mat number := 0;
v_dp_size number := 0;
v_cn number:=0;
v_ndp_s varchar2(100);
v_ndp_m varchar2(100);
v_ndp_size varchar2(100);
------------ 3380 end------------------
BEGIN
– DO NOT REMOVE BELOW
vn_item_pk_no := 0;–####
begin
select i.mas_pk_no, i.rpt_para1, i.rpt_para2, i.rpt_para3, i.rpt_para4, i.rpt_para5, i.rpt_para6, i.rpt_para7, i.rpt_para8, i.rpt_para9, i.rpt_para10
into vn_mas_pk_no, vs_p1, vs_p2, vs_p3, vs_p4, vs_p5, vs_p6, vs_p7, vs_p8, vs_p9, vs_p10
from impexp_item i
where i.pk_no = vn_item_pk_no;
select col_title
into vs_delimiter
from impexp_mas
where pk_no = vn_mas_pk_no;
if vs_delimiter = ‘TAB’ then–数据导入时，数据文件中数据分隔默认符
vs_delimiter := chr(9);
end if;
exception
when others then
vs_p1 := null; vs_p2 := null; vs_p3 := null; vs_p4 := null; vs_p5 := null; vs_p6 := null; vs_p7 := null; vs_p8 := null; vs_p9 := null; vs_p10 := null;
end;
if vs_p1 is null then
add_impexp_log(vn_item_pk_no, ‘组织参数值无效’, ’ ', ’ ', null, null, null);
return;
end if;
vn_err_count := 0;
vn_rownum := 0;
for rec in (select d.mas_pk_no as mas_pk_no, d.pk_no as pk_no, d.col01 as col01, d.col02 as col02
from impexp_data d
where d.mas_pk_no =vn_item_pk_no
order by d.pk_no)
loop

vs_mg := null;
vb_line_data_err := false;
vn_rownum := vn_rownum + 1;
if vn_rownum >= vn_dataline_start then
  begin 
    vs_line := ltrim(rtrim(rec.col01)) || vs_delimiter; 
    for x in 1..vn_data_cols loop
      vs_d(x) := null;
    end loop; 
    vn_no    := 0;
    vn_start := 1;
    vn_line_length   := length(vs_line);  
    for i in 1..vn_line_length loop
      if substr(vs_line,i,1) = vs_delimiter then
        vn_no := vn_no + 1;
        exit when vn_no > vn_data_cols;
        vs_d(vn_no) := substr(substr(vs_line,vn_start,i-vn_start),1,2000);
        vn_start := i+1;
      end if;
    end loop;
    if vn_no > vn_data_cols then
      vs_mg := '存在多余的数据';
      raise expt_dl;
    elsif vn_no < vn_cols_needed then
      vs_mg := '缺失必要的数据列';
      raise expt_dl;
    end if;
     
    vs_d(1) := rtrim(substrb(ltrim(rtrim(vs_d(1))),1,32));
    if vs_d(1) is null then
      vs_mg := '缺失数据列1(stk_c)'; 
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    
    vs_d(2) := rtrim(substrb(ltrim(rtrim(vs_d(2))),1,1));
    if vs_d(2) is null then
      vs_d(2) := 'S';
    elsif vs_d(2) not in ('S','N') then
      vs_mg := '列2(stk_flg)值无效"'||vs_d(2)||'"';
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    vs_d(3) := rtrim(substrb(ltrim(rtrim(vs_d(3))),1,256));
    if vs_d(3) is null then
      vs_mg := '缺失数据列3(name)';
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if;
    vs_d(3) := vs_d(3); 
    vs_d(4) := rtrim(substrb(ltrim(rtrim(vs_d(4))),1,64)); 
    vs_d(5) := rtrim(substrb(ltrim(rtrim(vs_d(5))),1,8));
    if vs_d(5) is null then
      vs_mg := '缺失数据列5(uom)';
    else
      vs_dum := 0; 
      begin
        select count('x') into vs_dum from stk_mas k where k.org_no = to_number(vs_p1) and k.stk_c = vs_d(1); 
        if vs_dum != 0 then 
           vs_dum := 0;
           begin
             select count('x') into vs_dum from stk_mas k where k.org_no = to_number(vs_p1) and k.stk_c = vs_d(1) and k.uom=vs_d(5); 
           exception
             when others then  vs_mg := '列5(uom)值无效"'||vs_d(5)||'"';   
           end; 
           if vs_dum = 0 then  vs_mg := '列5(uom)值无效"'||vs_d(1)||vs_d(5)||'"';
           end if;
        end if;
      exception
        when no_data_found then vs_mg := '列5(uom)值无效"'||vs_d(5)||'"';
      end;
    end if; 
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    vs_d(6) := rtrim(substrb(ltrim(rtrim(vs_d(6))),1,1));
    if vs_d(6) is null then vs_d(6) := 'F';
    elsif vs_d(6) not in ('F','S','R','M','O') then vs_mg := '列6(type)值无效"'||vs_d(6)||'"';
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(7) := rtrim(substrb(ltrim(rtrim(vs_d(7))),1,1));
    if vs_d(7) is null then vs_d(7) := 'P';
    elsif vs_d(7) not in ('P','M','O','C','T') then vs_mg := '列7(source)值无效"'||vs_d(7)||'"';
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(8) := rtrim(substrb(ltrim(rtrim(vs_d(8))),1,1));
    if vs_d(8) is null then vs_d(8) := 'A';
    elsif vs_d(8) not in ('A','B','S','F','L') then vs_mg := '列8(cost_type)值无效"'||vs_d(8)||'"';
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(9) := rtrim(substrb(ltrim(rtrim(vs_d(9))),1,1));
    if vs_d(9) is null then vs_d(9) := 'A';
    elsif vs_d(9) not in ('A','N','P') then vs_mg := '列9(status_flg)值无效"'||vs_d(9)||'"';
    end if;
    if vs_mg is not null then raise expt_dl; end if;           
    vs_d(10) := rtrim(substrb(ltrim(rtrim(vs_d(10))),1,16));
    if vs_d(10) is not null then
      vs_dum := null;
      begin
        select 'x' into vs_dum from stk_brand b where b.org_no = to_number(vs_p1) and b.brand_c = vs_d(10);
      exception
        when others then  vs_mg := '列10(brand_c)值无效"'||vs_d(10)||'"';
      end;
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(11) := rtrim(substrb(ltrim(rtrim(vs_d(11))),1,16));
    if vs_d(11) is not null then
      vs_dum := null;
      begin
        select 'x' into vs_dum from stk_cat c where c.org_no = to_number(vs_p1) and c.cat_c = vs_d(11);
      exception
        when others then
          vs_mg := '列11(cat_c)值无效"'||vs_d(11)||'"';
      end;
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(12) := rtrim(substrb(ltrim(rtrim(vs_d(12))),1,16));
    if vs_d(12) is not null then
      vs_dum := null;
      begin
        select 'x' into vs_dum from stk_cat2 c where c.org_no = to_number(vs_p1) and c.cat_c = vs_d(12);
      exception
        when others then
          vs_mg := '列12(cat_c2)值无效"'||vs_d(12)||'"';
      end;
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(13) := rtrim(substrb(ltrim(rtrim(vs_d(13))),1,16));
    if vs_d(13) is not null then
      vs_dum := null;
      begin
        select 'x' into vs_dum from stk_cat3 c where c.org_no = to_number(vs_p1) and c.cat_c = vs_d(13);
      exception
        when others then
          vs_mg := '列13(cat_c3)值无效"'||vs_d(13)||'"';
      end;
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(14) := rtrim(substrb(ltrim(rtrim(vs_d(14))),1,1));
    if vs_d(14) is not null then
      if vs_d(14) not in ('A','B','C') then vs_mg := '列14(abc_type)值无效"'||vs_d(14)||'"'; end if;
    else vs_d(14) := 'A';
    end if;
    if vs_mg is not null then raise expt_dl; end if;  
    vs_d(15) := rtrim(substrb(ltrim(rtrim(vs_d(15))),1,1));
    if vs_d(15) is null then vs_d(15) := 'Y';
    elsif vs_d(15) not in ('Y','N') then vs_mg := '列15(mat_flg)值无效"'||vs_d(15)||'"';
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(16) := rtrim(substrb(ltrim(rtrim(vs_d(16))),1,16));
    if vs_d(16) is not null then
      vs_dum := null;
      begin
        select 'x' into vs_dum from mat_code d where d.org_no = to_number(vs_p1) and d.mat_code = vs_d(16);
      exception
        when others then
          vs_mg := '列16(mat_code)值无效"'||vs_d(16)||'"';
      end;
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(17) := rtrim(substrb(ltrim(rtrim(vs_d(17))),1,1));
    if vs_d(17) is null then vs_d(17) := 'Y';
    elsif vs_d(17) not in ('Y','N') then vs_mg := '列17(size_flg)值无效"'||vs_d(17)||'"';
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(18) := rtrim(substrb(ltrim(rtrim(vs_d(18))),1,16));
    if vs_d(18) is not null then
      vs_dum := null;
      begin
        select 'x' into vs_dum from size_code d where d.org_no = to_number(vs_p1) and d.size_code = vs_d(18);
      exception
        when others then
          vs_mg := '列18(size_code)值无效"'||vs_d(18)||'"';
      end;
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(19) := rtrim(substrb(ltrim(rtrim(vs_d(19))),1,1));
    if vs_d(19) is null then vs_d(19) := 'N';
    elsif vs_d(19) not in ('Y','N') then vs_mg := '列19(color_flg)值无效"'||vs_d(19)||'"';
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    vs_d(20) := rtrim(substrb(ltrim(rtrim(vs_d(20))),1,1));
    if vs_d(20) is null then vs_d(20) := 'N';
    elsif vs_d(20) not in ('Y','N') then vs_mg := '列20(madein_flg)值无效"'||vs_d(20)||'"';
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    begin
      vs_d(26) := to_char(round(to_number(ltrim(rtrim(vs_d(26)))),6));
    exception
      when others then
        vs_mg := '列26(pos_list_price)值无效"'||vs_d(26)||'"';
    end;
    if vs_mg is not null then raise expt_dl; end if;
    begin
      vs_d(21) := to_char(round(to_number(ltrim(rtrim(nvl(vs_d(21),vs_d(26))))),6));
    exception
      when others then
        vs_mg := '列21(list_price)值无效"'||vs_d(21)||'"';
    end;
    if vs_mg is not null then raise expt_dl; end if; 
    begin
      vs_d(22) := to_char(round(to_number(ltrim(rtrim(nvl(vs_d(22),0)))),6)); 
        if to_number(vs_d(22)) < 0 or to_number(vs_d(22)) > 100 then
          vs_mg := '列22(disc_num)值无效"'||vs_d(22)||'"';
        end if; 
    exception
      when others then
        vs_mg := '列22(disc_num)值无效"'||vs_d(22)||'"';
    end;
    if vs_mg is not null then raise expt_dl; end if; 
    begin
      vs_d(23) := to_char(round(to_number(ltrim(rtrim(nvl(vs_d(23),vs_d(26))))),6));
    exception
      when others then
        vs_mg := '列23(net_price)值无效"'||vs_d(23)||'"';
    end;
    if vs_mg is not null then raise expt_dl; end if; 
    if vs_d(21) is null and (vs_d(22) is not null or vs_d(23) is not null) 
      or 
       vs_d(21) is not null and (vs_d(22) is null or vs_d(23) is null) 
    then
      vs_mg := '无效的list_price、disc_num或net_price值';
    elsif (vs_d(21) is not null and vs_d(22) is not null and vs_d(23) is not null) then
      if (to_number(vs_d(21)) * (100-to_number(vs_d(22))) / 100) <> to_number(vs_d(23)) then
        vs_mg := '数据列21、22、23(list_price、disc_num、net_price)三者换算数据不准确';
      end if;
    else   
      vs_d(21) := '0';
      vs_d(22) := '0';
      vs_d(23) := '0';
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    begin
      vs_d(24) := to_char(round(to_number(ltrim(rtrim(vs_d(24)))),6));
      if vs_d(24) is null then
        vs_d(24) := '0.0';
      elsif to_number(vs_d(24)) < 0.0 then
        vs_mg := '列24(min_price)值无效"'||vs_d(24)||'"';
      end if;
    exception
      when others then
        vs_mg := '列24(min_price)值无效"'||vs_d(24)||'"';
    end;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    begin
      vs_d(25) := to_char(round(to_number(ltrim(rtrim(vs_d(25)))),6));
      if vs_d(25) is null then
        vs_d(25) := '0.0';
      elsif to_number(vs_d(25)) < 0.0 then
        vs_mg := '列25(std_cost)值无效"'||vs_d(25)||'"';
      end if;
    exception
      when others then
        vs_mg := '列25(std_cost)值无效"'||vs_d(25)||'"';
    end;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    begin
      vs_d(26) := to_char(round(to_number(ltrim(rtrim(vs_d(26)))),6));
      if vs_d(26) is null then
        vs_d(26) := '0.0';
      elsif to_number(vs_d(26)) < 0.0 then
        vs_mg := '列26(pos_list_price)值无效"'||vs_d(26)||'"';
      end if;
    exception
      when others then
        vs_mg := '列26(pos_list_price)值无效"'||vs_d(26)||'"';
    end;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    begin
      vs_d(27) := to_char(round(to_number(ltrim(rtrim(nvl(vs_d(27),0)))),6));
      if to_number(vs_d(27)) < 0 or to_number(vs_d(27)) > 100 then
        vs_mg := '列27(pos_disc_num)值无效"'||vs_d(27)||'"';
      end if;
    exception
      when others then
        vs_mg := '列27(pos_disc_num)值无效"'||vs_d(27)||'"';
    end;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    begin
      vs_d(28) := to_char(round(to_number(ltrim(rtrim(nvl(vs_d(28),vs_d(26))))),6));
    exception
      when others then
        vs_mg := '列28(pos_net_price)值无效"'||vs_d(28)||'"';
    end;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    if vs_d(26) is null and (vs_d(27) is null or vs_d(28) is null) 
      or 
       vs_d(26) is null 
    then
      vs_mg := '无效的pos_list_price、pos_disc_num或pos_net_price值';
    elsif (vs_d(26) is not null and vs_d(27) is not null and vs_d(28) is not null) then
      if (to_number(vs_d(26)) * (100-to_number(vs_d(27))) / 100) <> to_number(vs_d(28)) then
        vs_mg := '列26、27、28(pos_list_price、pos_disc_num、pos_net_price)三者换算数据不准确';
      end if;
    else 
      vs_d(26) := '0';
      vs_d(27) := '0';
      vs_d(28) := '0';
    end if;
    if vs_mg is not null then raise expt_dl; end if; 
    begin
      vs_d(29) := to_char(round(to_number(ltrim(rtrim(nvl(vs_d(29),0)))),6)); 
        if to_number(vs_d(29)) < 0  then
          vs_mg := '列29(pos_min_price)值无效"'||vs_d(29)||'"';
        end if; 
    exception
      when others then
        vs_mg := '列29(pos_min_price)值无效"'||vs_d(29)||'"';
    end;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    vs_d(30) := rtrim(substrb(ltrim(rtrim(vs_d(30))),1,8));
    if vs_d(30) is not null then
      vs_dum := null;
      begin
        select 'x' into vs_dum from tax_mas x where x.org_no = to_number(vs_p1) and x.tax_type = 'O' and x.tax_code = vs_d(30);
      exception
        when others then
          vs_mg := '列30(tax_code)值无效"'||vs_d(30)||'"';
      end;
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    vs_d(31) := rtrim(substrb(ltrim(rtrim(vs_d(31))),1,8));
    if vs_d(31) is not null then
      vs_dum := null;
      begin
        select 'x' into vs_dum from tax_mas x where x.org_no = to_number(vs_p1) and x.tax_type = 'I' and x.tax_code = vs_d(31);
      exception
        when others then
          vs_mg := '列31(tax_code1)值无效"'||vs_d(30)||'"';
      end;
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
     if vs_d(33) is not null then
      vs_dum := null;
      begin
        select 'x' into vs_dum 
        from scuser u 
        where u.org_no = to_number(vs_p1) and u.purchaser_flg = 'Y'  and u.com_flg = 'Y'  and u.public_flg = 'Y'  and u.show_flg = 'Y';
      exception
        when others then
          vs_mg := '列33(old_code)值无效"'||vs_d(33)||'"';
      end;
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    vs_d(34) := rtrim(substrb(ltrim(rtrim(NVL(vs_d(34),'C'))),1,128)); 
    vs_d(35) := substrb(ltrim(rtrim(vs_d(35))),1,10);
    if vs_d(35) is not null then
      begin
        vs_d(35) := to_char(to_date(vs_d(35),'yyyy/mm/dd'),'yyyy/mm/dd');
      exception
        when others then
          vs_mg := '列35(shelf_date)值无效"'||vs_d(35)||'"';
      end;
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    vs_d(36) := rtrim(substrb(ltrim(rtrim(vs_d(36))),1,32));
    if vs_d(36) is not null then
      vs_dum := null;
      begin
        select 'x' into vs_dum from stk_mas where org_no = to_number(vs_p1) and stk_flg in ('C','P') and stk_c = vs_d(36);
      exception
        when others then
          vs_mg := '列36(hs_c)值无效"'||vs_d(36)||'"';
      end;
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    for i in 37..47 loop
      vs_d(i) := rtrim(substrb(ltrim(rtrim(vs_d(i))),1,64));
    end loop; 
    vs_d(48) := rtrim(substrb(ltrim(rtrim(vs_d(48))),1,1));
    if vs_d(48) is not null and vs_d(48) not in ('C','D','S','L') then
      vs_mg := '列48(ref12)值无效"'||vs_d(48)||'"';
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if; 

    vs_d(49) := rtrim(substrb(ltrim(rtrim(vs_d(49))),1,32));
    vs_d(56):=0; 
    if vs_d(49) is null then
      if vs_d(50) is not null or vs_d(51) is not null  then
          vs_mg := '列49(plu)值无效"'||vs_d(49)||'"';
      end if;
    else 
      vs_dum := 0;
      begin
        select count('x') into vs_dum from plu_mas where org_no = to_number(vs_p1) and plu_c = vs_d(49);
        if vs_dum !=0 then
          vs_d(56):=1;
          if vs_d(50) is not null or vs_d(51) is not null then
            vs_dum := 0;
            begin
              select count('x') into vs_dum from plu_mas where org_no = to_number(vs_p1) and plu_c = vs_d(49) and stk_c = vs_d(1) and (mat_c = vs_d(50) or vs_d(50) is null) and (size_c = vs_d(51) or vs_d(51) is null);
              if vs_dum !=0 then
                vs_mg := '列49(plu)值重复，颜色和尺码属性有差异"'||vs_d(49)||'颜色'||vs_d(50)||'尺码'||vs_d(49)||'"';
              end if;         
            exception
              when NO_DATA_FOUND then
                vs_d(56):=0;
            end;
          end if;
        end if;         
      exception
        when NO_DATA_FOUND then
          vs_d(56):=0;
      end;
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if; 
    vs_d(50) := rtrim(substrb(ltrim(rtrim(vs_d(50))),1,32));
    vs_d(54) :=0;
    if vs_d(50) is null then
      if vs_d(15) ='Y' or vs_d(15) is null  then
          vs_mg := '列50(mat_c)值无效"'||vs_d(50)||'"';
      end if; 
    else
      vs_dum := null; 
      begin
        select count('x') into vs_dum from mat_mas k where k.org_no = to_number(vs_p1) and k.mat_c=vs_d(50);  
        if vs_dum != 0 then 
           vs_d(54) :=1;
        end if;   
        vs_dum := 0;
        begin
          select count('x') into vs_dum from stk_al_mat k where k.org_no = to_number(vs_p1) and k.stk_c=vs_d(1) and k.mat_c=vs_d(50); 
          if vs_dum != 0 then 
             vs_d(54) :=2;
          end if;
        exception
          when no_data_found then
            vs_d(54) :=1;
        end;            
      exception
        when no_data_found then
          vs_d(54) :=0;
      end;  
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if;   
    vs_d(51) := rtrim(substrb(ltrim(rtrim(vs_d(51))),1,32));
    vs_d(55) :=0;
    if vs_d(51) is null then
      if vs_d(17) ='Y' or vs_d(17) is null  then
          vs_mg := '列51(size_c)值无效 "'||vs_d(51)||'"';
      end if; 
    else
      vs_dum := null;
      begin
        select count('x') into vs_dum from size_mas k where k.org_no = to_number(vs_p1) and k.size_c=vs_d(51); 
        if vs_dum != 0 then 
           vs_d(55) :=1;
        end if;
        vs_dum := 0;
        begin
          select count('x') into vs_dum from stk_al_size k where k.org_no = to_number(vs_p1) and k.stk_c=vs_d(1) and k.size_c=vs_d(51); 
          if vs_dum != 0 then 
             vs_d(55) :=2;
          end if;
        exception
          when no_data_found then
            vs_d(55) :=1;
        end;  
      exception
        when no_data_found then
          vs_d(55) :=0;
      end;  
    end if;
    if vs_mg is not null then
      raise expt_dl;
    end if;      
    --3380 begin 此处使用记录组类型
     cn := cn + 1; 
     if cn > 1 then              
      for i in 1..my_tb.count  loop           
        if my_tb(i).stk_c = vs_d(1) then  
           if my_tb(i).mat_c = vs_d(50) then
               v_dp_mat := i;
               v_ndp_s :=NULL;
               v_ndp_m :=NULL;  
               exit; 
            else
               v_ndp_s :=vs_d(1);
               v_ndp_m :=vs_d(50); 
            end if;  
          v_dp_stk := i;
          v_ndp_s :=NULL;
          v_ndp_m :=null;  
          exit; 
        else 
          v_ndp_s :=vs_d(1);
          v_ndp_m :=vs_d(50); 
        end if; 
      end loop; 
     else-- cn = 1      
        my_tb(cn).stk_c := vs_d(1); 
        my_tb(cn).mat_c := vs_d(50); 
        v_ndp_s :=vs_d(1);
        v_ndp_m :=vs_d(50);            
     end if;           
     v_cn :=my_tb.count+1;
      IF v_ndp_s IS NOT NULL and v_ndp_m is not null THEN      
         my_tb(v_cn).stk_c := v_ndp_s;
         my_tb(v_cn).mat_c := v_ndp_m; 
         vs_d(52) :=vs_d(1);
         vs_d(53) :=vs_d(50);  
      END IF;            
   --3380 end  
    for i in 1..vn_data_cols loop
      v_r_d(vn_rownum-vn_dataline_start+1)(i) := vs_d(i);
    end loop;  
  exception
    when expt_dl then
      vn_err_count := vn_err_count + 1;
      add_impexp_log(vn_item_pk_no, '[第'||vn_rownum||'行] '||vs_mg, rec.col01, rec.col02, null, null, null);
      vb_line_data_err := true;
      for i in 1..vn_data_cols loop
        v_r_d(vn_rownum-vn_dataline_start+1)(i) := vs_err_data;
      end loop;
  end;
end if;   

end loop;
if vn_err_count <> 0 or vn_rownum < vn_dataline_start then
return;
end if;
for i in 1…(vn_rownum-vn_dataline_start+1) loop
if v_r_d(i)(1) <> vs_err_data then
v_cn :=0;
begin
select count(‘x’) into v_cn from stk_mas where org_no = to_number(vs_p1) and stk_c = v_r_d(i)(1) and uom = v_r_d(i)(5);
exception
when others then v_cn :=0;
end;
if v_cn = 1 then
if v_r_d(i)(52) IS not NULL and v_r_d(i)(53) IS not NULL then --3380–
update stk_mas k
set stk_flg = v_r_d(i)(2), name = v_r_d(i)(3), modle = v_r_d(i)(4),
uom = v_r_d(i)(5), type = v_r_d(i)(6), source = v_r_d(i)(7), cost_type = v_r_d(i)(8), status_flg = v_r_d(i)(9), brand_c = v_r_d(i)(10), cat_c = v_r_d(i)(11), cat_c2 = v_r_d(i)(12), cat_c3 = v_r_d(i)(13), abc_type = v_r_d(i)(14),
mat_flg = v_r_d(i)(15), mat_code = v_r_d(i)(16), size_flg = v_r_d(i)(17), size_code = v_r_d(i)(18), color_flg = v_r_d(i)(19), madein_flg = v_r_d(i)(20), list_price = v_r_d(i)(21), disc_num = v_r_d(i)(22), net_price = v_r_d(i)(23), min_price = v_r_d(i)(24),
std_cost = v_r_d(i)(25), pos_list_price = v_r_d(i)(26), pos_disc_num = v_r_d(i)(27), pos_net_price = v_r_d(i)(28), pos_min_price = v_r_d(i)(29), tax_code = v_r_d(i)(30), tax_code1 = v_r_d(i)(31), remark = v_r_d(i)(32), old_code = v_r_d(i)(33), url_addr = ‘M’,
shelf_date = v_r_d(i)(35), hs_c = v_r_d(i)(36), ref1 = v_r_d(i)(37), ref2 = v_r_d(i)(38), ref3 = v_r_d(i)(39), ref4 = v_r_d(i)(40), ref5 = v_r_d(i)(41), ref6 = v_r_d(i)(42), ref7 = v_r_d(i)(43), ref8 = v_r_d(i)(44),
ref9 = v_r_d(i)(45), ref10 = v_r_d(i)(46), ref11 = v_r_d(i)(47), ref12 = v_r_d(i)(48),last_update=sysdate,ref_date2=sysdate
where k.org_no = to_number(vs_p1) and k.stk_c = v_r_d(i)(1) ;
if v_r_d(i)(54) =0 then
insert into mat_mas(org_no, mat_c, name) values(to_number(vs_p1), v_r_d(i)(50), v_r_d(i)(50));
insert into stk_al_mat(org_no, stk_c, mat_c) values(to_number(vs_p1), v_r_d(i)(1), v_r_d(i)(50));
elsif to_number(v_r_d(i)(54) )=1 then
insert into stk_al_mat(org_no, stk_c, mat_c) values(to_number(vs_p1), v_r_d(i)(1), v_r_d(i)(50));
end if;
end if;
if v_r_d(i)(56) =0 then
insert into plu_mas(org_no, plu_c, stk_c, mat_c, size_c) values(/to_number(/vs_p1/)/, v_r_d(i)(49),v_r_d(i)(1), v_r_d(i)(50),v_r_d(i)(51));
end if;
if v_r_d(i)(55) =0 then
insert into size_mas(org_no, size_c, name) values(to_number(vs_p1), v_r_d(i)(51), v_r_d(i)(51));
insert into stk_al_size(org_no, stk_c, size_c) values(to_number(vs_p1), v_r_d(i)(1), v_r_d(i)(51));
elsif v_r_d(i)(55) =1 then
insert into stk_al_size(org_no, stk_c, size_c) values(to_number(vs_p1), v_r_d(i)(1), v_r_d(i)(51));
end if;
elsif v_cn = 0 then
if v_r_d(i)(52) IS not NULL and v_r_d(i)(53) IS not NULL then --3380–
insert into stk_mas
(org_no, stk_c, stk_flg, name, modle, uom, type, source, cost_type, status_flg, brand_c, cat_c, cat_c2, cat_c3, abc_type, mat_flg, mat_code, size_flg, size_code, color_flg,
madein_flg, list_price, disc_num, net_price, min_price, std_cost, pos_list_price, pos_disc_num, pos_net_price, pos_min_price, tax_code, tax_code1, remark, old_code, url_addr,
shelf_date, hs_c, ref1, ref2, ref3, ref4, ref5, ref6, ref7, ref8, ref9, ref10, ref11, ref12,create_date,ref_date1)
select to_number(vs_p1), v_r_d(i)(1), v_r_d(i)(2), v_r_d(i)(3), v_r_d(i)(4),
v_r_d(i)(5), v_r_d(i)(6), v_r_d(i)(7), v_r_d(i)(8), v_r_d(i)(9), v_r_d(i)(10), v_r_d(i)(11), v_r_d(i)(12), v_r_d(i)(13), v_r_d(i)(14), v_r_d(i)(15), v_r_d(i)(16), v_r_d(i)(17), v_r_d(i)(18), v_r_d(i)(19), v_r_d(i)(20), v_r_d(i)(21), v_r_d(i)(22), v_r_d(i)(23), v_r_d(i)(24),
v_r_d(i)(25), v_r_d(i)(26), v_r_d(i)(27), v_r_d(i)(28), v_r_d(i)(29), v_r_d(i)(30), v_r_d(i)(31), v_r_d(i)(32), v_r_d(i)(33), v_r_d(i)(34), v_r_d(i)(35), v_r_d(i)(36), v_r_d(i)(37), v_r_d(i)(38), v_r_d(i)(39), v_r_d(i)(40), v_r_d(i)(41), v_r_d(i)(42), v_r_d(i)(43)
,v_r_d(i)(44), v_r_d(i)(45), v_r_d(i)(46), v_r_d(i)(47), v_r_d(i)(48), sysdate, sysdate
from dual
where not exists (select ‘x’ from stk_mas k where k.org_no = to_number(vs_p1) and k.stk_c = v_r_d(i)(1) );
if sql%rowcount = 0 then
update stk_mas k set stk_flg = v_r_d(i)(2), name = v_r_d(i)(3), modle = v_r_d(i)(4), uom = v_r_d(i)(5), type = v_r_d(i)(6), source = v_r_d(i)(7), cost_type = v_r_d(i)(8), status_flg = v_r_d(i)(9),
brand_c = v_r_d(i)(10), cat_c = v_r_d(i)(11), cat_c2 = v_r_d(i)(12), cat_c3 = v_r_d(i)(13), abc_type = v_r_d(i)(14), mat_flg = v_r_d(i)(15), mat_code = v_r_d(i)(16), size_flg = v_r_d(i)(17), size_code = v_r_d(i)(18), color_flg = v_r_d(i)(19),
madein_flg = v_r_d(i)(20), list_price = v_r_d(i)(21), disc_num = v_r_d(i)(22), net_price = v_r_d(i)(23), min_price = v_r_d(i)(24), std_cost = v_r_d(i)(25), pos_list_price = v_r_d(i)(26), pos_disc_num = v_r_d(i)(27), pos_net_price = v_r_d(i)(28), pos_min_price = v_r_d(i)(29),
tax_code = v_r_d(i)(30), tax_code1 = v_r_d(i)(31), remark = v_r_d(i)(32), old_code = v_r_d(i)(33), url_addr = ‘M’, shelf_date = v_r_d(i)(35), hs_c = v_r_d(i)(36), ref1 = v_r_d(i)(37), ref2 = v_r_d(i)(38), ref3 = v_r_d(i)(39),
ref4 = v_r_d(i)(40), ref5 = v_r_d(i)(41), ref6 = v_r_d(i)(42), ref7 = v_r_d(i)(43), ref8 = v_r_d(i)(44), ref9 = v_r_d(i)(45), ref10 = v_r_d(i)(46), ref11 = v_r_d(i)(47), ref12 = v_r_d(i)(48),last_update=sysdate,ref_date2=sysdate
where k.org_no = to_number(vs_p1) and k.stk_c = v_r_d(i)(1) ;
end if;
if v_r_d(i)(54) =0 then
insert into mat_mas(org_no, mat_c, name) values(to_number(vs_p1), v_r_d(i)(50), v_r_d(i)(50));
insert into stk_al_mat(org_no, stk_c, mat_c) values(to_number(vs_p1), v_r_d(i)(1), v_r_d(i)(50));
elsif v_r_d(i)(54) >=1 then
insert into stk_al_mat(org_no, stk_c, mat_c) values(to_number(vs_p1), v_r_d(i)(1), v_r_d(i)(50));
end if;
end if;
if v_r_d(i)(56) =0 then
insert into plu_mas(org_no, plu_c, stk_c, mat_c, size_c) values(vs_p1, v_r_d(i)(49),v_r_d(i)(1), v_r_d(i)(50),v_r_d(i)(51));
end if;
if v_r_d(i)(55) =0 then
insert into size_mas(org_no, size_c, name) values(to_number(vs_p1), v_r_d(i)(51), v_r_d(i)(51));
insert into stk_al_size(org_no, stk_c, size_c) values(to_number(vs_p1), v_r_d(i)(1), v_r_d(i)(51));
elsif v_r_d(i)(55) >=1 then
insert into stk_al_size(org_no, stk_c, size_c) values(to_number(vs_p1), v_r_d(i)(1), v_r_d(i)(51));
end if;
end if;
end if;
end loop;
commit;
exception
when others then
vs_mg := vs_mg||sqlerrm;
rollback;
add_impexp_log(vn_item_pk_no, vs_mg, null, null, null, null, null);
end;