建立透明网关

delete D_WARE_SALEPRICE_H1

insert into D_WARE_SALEPRICE_H1 d
(d.compid
,d.salegroupid
,d.warecode
— ,d.wareid
,d.saleprice
,d.minprice
,d.memprice
,d.maxsaleprice
,d.maxwhlprice
,d.memcardflag
,d.memminprice
,d.memprice2
,d.memminprice2
,d.memprice3
,d.memminprice3
,d.memprice4
,d.memminprice4
,d.memprice5
,d.memminprice5
,d.memprice6
,d.memminprice6
,d.memprice7
,d.memminprice7
,d.integral_cash
,d.lastpurprice)

select 2 as compid
,s.”groupid” as salegroupid
,s.”wareid” as warecode
,nvl(s.”saleprice”,0) as saleprice
,nvl(s.”minprice”,0) as minprice
,nvl(s.”memprice” ,0) as memprice
,nvl(s.”maxsaleprice”,0) as maxsaleprice
,nvl(s.”maxwhlprice”,0) as maxwhlprice
,nvl(s.”memcardflag”,0) as memcardflag
,nvl(s.”memminprice” ,0) as memminprice
,nvl(s.”memprice2″,0) as memprice2
,nvl(s.”memminprice2″,0) as memminprice2
,nvl(s.”memprice3″ ,0) as memprice3
,nvl(s.”memminprice3″,0) as memminprice3
,nvl(s.”memprice4″,0) as memprice4
,nvl(s.”memminprice4″,0) as memminprice4
,nvl(s.”memprice5″ ,0) as memprice5
,nvl(s.”memminprice5″,0) as memminprice5
,nvl(s.”memprice6″,0) as memprice6
,nvl(s.”memminprice6″,0) as memminprice6
,nvl(s.”memprice7″ ,0) as memprice7
,nvl(s.”memminprice7″,0) as memminprice7
,nvl(s.”integral_cash”,0) as integral_cash
,0 as lastpurprice
from u_ware_saleprice@ms_link1 s
— where s.”wareid” = ‘003001’

select count(*) from D_WARE_SALEPRICE_H1 where wareid is not null

update D_WARE_SALEPRICE_H1 d set d.wareid=(select t.wareid from t_ware_base t where t.warecode=d.warecode )

insert into cT_WARE_SALEPRICE_back select * from T_WARE_SALEPRICE;

delete T_WARE_SALEPRICE ;

insert into T_WARE_SALEPRICE d
(d.compid
,d.salegroupid
,d.wareid
,d.saleprice
,d.minprice
,d.memprice
,d.maxsaleprice
,d.maxwhlprice
,d.memcardflag
,d.memminprice
,d.memprice2
,d.memminprice2
,d.memprice3
,d.memminprice3
,d.memprice4
,d.memminprice4
,d.memprice5
,d.memminprice5
,d.memprice6
,d.memminprice6
,d.memprice7
,d.memminprice7
,d.integral_cash
,d.lastpurprice
,d.pile )
select h1.compid
,h1.salegroupid
,h1.wareid
,h1.saleprice
,h1.minprice
,h1.memprice
,h1.maxsaleprice
,h1.maxwhlprice
,h1.memcardflag
,h1.memminprice
,h1.memprice2
,h1.memminprice2
,h1.memprice3
,h1.memminprice3
,h1.memprice4
,h1.memminprice4
,h1.memprice5
,h1.memminprice5
,h1.memprice6
,h1.memminprice6
,h1.memprice7
,h1.memminprice7
,h1.integral_cash
,h1.lastpurprice
,0 as pile
from D_WARE_SALEPRICE_H1 h1
where h1.wareid is not null