期初开帐反算材料和多层制成

select * from tc_ecr_file

select * from tc_ecr_file

select * from ecu_file

select * from ecb_file

truncate table t_duble_ecm

select distinct t01_ta_ecm03,t01_ecm03par,t01_ecm01,ta_ecb01 from t01_ecm,ecb_file

where t01_ecm03par=ecb01 and ta_ecb02=t01_ta_ecm03 and ta_ecb02<>ta_ecb01 and t01_ecm01='5101-080700040'

 select distinct ecm03_par,ta_ecm03 

 from ecm_file,sfb_file 

 where ecm301>0 and sfb01=ecm01 and sfb87='Y' 

 group by ta_ecm03,ecm03_par;

 

--资料准备 

 create table t_duble_ecm as

 select distinct ecm03_par,ta_ecm03,'1' as sou ,ta_ecm03 as s_taecm03,ecm11 

 from ecm_file,sfb_file 

 where ecm301>0 and sfb01=ecm01 and sfb87='Y' 

 

  drop  table t_duble_ecm

  truncate  table t_duble_ecm

 

 insert into t_duble_ecm (ecm03_par,ta_ecm03,sou,s_taecm03,ecm11 )

 select distinct ecm03_par,ta_ecm03,'1' ,ta_ecm03 

 from ecm_file,sfb_file 

 where ecm301>0 and sfb01=ecm01 and sfb87='Y' --and ecm03_par='AA002002700'

 group by ta_ecm03,ecm03_par;

 

 commit

-- t_duble_ecm 多层展开

 insert into t_duble_ecm (ecm03_par,ta_ecm03,sou,s_taecm03,ecm11 )

 --select ecm03_par,ta_ecb01,'2',s_taecm03,ecb03,ta_ecb01,ta_ecb02 from t_duble_ecm,ecb_file

 --where ecm03_par=ecb01 and ta_ecb02=ta_ecm03 and ta_ecb02<>ta_ecb01 and sou='1'

 select ecm03_par,ta_ecu01,'2',s_taecm03,ecu02 --,ta_ecu01,ta_ecu02 

 from t_duble_ecm,ecu_file

 where ecm03_par=ecu01 and ta_ecu02=ta_ecm03 

 and sou='1' and ecm11=ecu02

 

 insert into t_duble_ecm (ecm03_par,ta_ecm03,sou,s_taecm03,ecm11 )

 select ecm03_par,ta_ecu01,'3',s_taecm03,ecu02 --,ta_ecu01,ta_ecu02 

 from t_duble_ecm,ecu_file

 where ecm03_par=ecu01 and ta_ecu02=ta_ecm03 

 and sou='2' and ecm11=ecu02

 

 commit;

 truncate table t02_ecm_ecr02;

 truncate table t01_ecm

  

 insert into t01_ecm 

 select ecm01,a.ta_ecm03,ecm03,b.ecm11,b.ecm03_par,ecm301,ta_ecm05/ta_ecm04,sou--,a.S_TAECM03

 from ecm_file b,sfb_file,t_duble_ecm a

 where a.ecm03_par=b.ecm03_par and b.ta_ecm03= a.s_taecm03

 and ecm301>0 and sfb01=ecm01 and sfb87='Y'  and sou='1'--and a.ta_ecm03<> b.ta_ecm03;

 

 commit;

--资料计算 

 declare

li_start number(8):=0;

ls_ecm01 varchar(16);

ld_ecm02 number(5);

ld_ecm03 number(5):=0;

ls_ecm03par varchar(40);

ls_taecm03 varchar(6);

ls_ecm11 varchar(6);

ld_qpa number(16,8);

ld_ecm301 number(15,3);

ls_ecr04 varchar(10) ;

ls_ecr05 varchar(40);

ld_ecr06 number(16,8);

ld_ecr07 number(16,8);

l_bmz02 varchar(20);

LD_SFA02 number(5) ;

ld_count number(5);

ld_ecm909 number(6,3);

ld_ecm908 number(6,3);

cursor cur_img is

  select t01_ecm01,t01_ta_ecm03,t01_ecm03,t01_ecm11,t01_ecm03par,t01_ecm301,qpa 

  from t01_ecm ;--where sou='1';--where t01_ecm01='5101-080700009';

  begin 

  open cur_img;

  loop

  fetch cur_img into ls_ecm01,ls_taecm03,ld_ecm03,ls_ecm11,ls_ecm03par,ld_ecm301,ld_qpa;

  exit when cur_img%notfound;

  ld_count:=0 ;

  select count(*) into ld_count from tc_ecr_file

  where tc_ecr01=ls_ecm03par and tc_ecr02=ls_ecm11 

  and tc_ecr03=ls_taecm03  and tc_ecr04<=ld_ecm03;

  

  if ld_count>0 then

  insert into t02_ecm_ecr02(t01_ecm01,t01_ta_ecm03,t01_ecm03,t01_ecm11,t01_ecm03par,t01_ecm301,qpa,t02_ecm03par,qpa2)

  select ls_ecm01,tc_ecr03,tc_ecr04,tc_ecr02,tc_ecr01,ld_ecm301,ld_qpa,tc_ecr05,tc_ecr06/tc_ecr07

  from tc_ecr_file

  where tc_ecr01=ls_ecm03par and tc_ecr02=ls_ecm11 

  and tc_ecr03=ls_taecm03  and tc_ecr04<=ld_ecm03;

  end if;

  

  end loop ;

  close cur_img ;

  commit;

end ;

alter table t01_ecm add ( sou varchar(6));

 

 truncate table t01_ecm ; 

 insert into t01_ecm 

 select ecm01,a.ta_ecm03,ecm03,ecm11,b.ecm03_par,ecm301,ta_ecm05/ta_ecm04,sou--,a.S_TAECM03

 from ecm_file b,sfb_file,t_duble_ecm a

 where a.ecm03_par=b.ecm03_par and b.ta_ecm03= a.s_taecm03

 and ecm301>0 and sfb01=ecm01 and sfb87='Y'  and sou='2'--and a.ta_ecm03<> b.ta_ecm03;

 

 commit;

 

 select count(*) 

  --t02_ecm_ecr02.*,sfa03,t01_ecm301*qpa*qpa2,sfa05 

 from t02_ecm_ecr02,sfa_file

 where sfa01=t01_ecm01

 and t02_ecm03par=sfa03

 

 select count(*) 

 from t02_ecm_ecr02 

 group by t01_ecm01,t02_ecm03par 

 --,ecm_file

 where t01_ecm01=ecm01 and t01_ta_ecm03<>ta_ecm03 ecm03_par;

 

--资料计算 

 declare

li_start number(8):=0;

ls_ecm01 varchar(16);

ld_ecm02 number(5);

ld_ecm03 number(5):=0;

ls_ecm03par varchar(40);

ls_taecm03 varchar(6);

ls_ecm11 varchar(6);

ld_qpa number(16,8);

ld_ecm301 number(15,3);

ls_ecr04 varchar(10) ;

ls_ecr05 varchar(40);

ld_ecr06 number(16,8);

ld_ecr07 number(16,8);

l_bmz02 varchar(20);

LD_SFA02 number(5) ;

ld_count number(5);

ld_ecm909 number(6,3);

ld_ecm908 number(6,3);

cursor cur_img is

  select t01_ecm01,t01_ta_ecm03,t01_ecm03,t01_ecm11,t01_ecm03par,t01_ecm301,qpa 

  from t01_ecm ;--where sou<>'1';--where t01_ecm01='5101-080700009';

  begin 

  open cur_img;

  loop

  fetch cur_img into ls_ecm01,ls_taecm03,ld_ecm03,ls_ecm11,ls_ecm03par,ld_ecm301,ld_qpa;

  exit when cur_img%notfound;

  ld_count:=0 ;

  select count(*) into ld_count from tc_ecr_file

  where tc_ecr01=ls_ecm03par and tc_ecr02=ls_ecm11 

  and tc_ecr03=ls_taecm03  and tc_ecr04<=ld_ecm03;

  

  if ld_count>0 then

  insert into t02_ecm_ecr02(t01_ecm01,t01_ta_ecm03,t01_ecm03,t01_ecm11,t01_ecm03par,t01_ecm301,qpa,t02_ecm03par,qpa2)

  select ls_ecm01,tc_ecr03,tc_ecr04,tc_ecr02,tc_ecr01,ld_ecm301,ld_qpa,tc_ecr05,tc_ecr06/tc_ecr07

  from tc_ecr_file

  where tc_ecr01=ls_ecm03par and tc_ecr02=ls_ecm11 

  and tc_ecr03=ls_taecm03  and tc_ecr04<=ld_ecm03;

  end if;

  

  end loop ;

  close cur_img ;

  commit;

end ;

select * from ecm_tmp1

where trim(ecm03_par) not in 

(select sfb05 from sfb_file )