时空212单据的EXCEL数据导入后自动分摊批号

时空212单据的EXCEL数据导入后自动分摊批号.自动核算赠品1,修改DJExcel配制文件如下:[212]; 数据路径=D:\cdmfd\ccerpexeout\; 文件名=采购导入模板.xls插入1=insert into tmp_excel_dj(gzid,spbh,spmch,shl,dw,dj,shpchd,shpgg,hshj,插入2=spid) IN [ODBC]插入3= [ODBC;Driver=SQLServer;UID=sa;PWD=:password;Server=:server;DataBase=:database]插入4=select :gzid, a.商品编号, a.商品名称,a.数量, a.包装单位, a.单价,a.生产厂商,插入5= a.商品规格,a.金额,a.商品内码插入6= from [a$] a查询1=exec wms_read_excel @action = 0, @djlx = 212, @gzid = :gzid删除1=exec wms_read_excel @action = 1, @djlx = 212, @gzid = :gzid2,修改wms_read_excel存储过程set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER procedure [dbo].[wms_read_excel]@action int = 0, -- 0 查询 1 删除@djlx char(3),@gzid char(11)ASBEGINSET NOCOUNT ONif @action = 0beginif @djlx in ('112', '120')begin-- declare @spid char(11),@glid char(13)-- set @spid=''-- set @glid=''/*declare spid_cursor insensitive cursorforselect distinct glid from tmp_excel_dj(nolock) where glid not in(select glid from spkfk(nolock) where beactive='是') and gzid = @gzidfor read onlyopen spid_cursorfetch next from spid_cursor into @glidwhile @@fetch_status <> -1beginexec SOF_getmaxbh 'SPH', 3, @spid output--print @spidinsert intospkfk(spid,spbh,spmch,shpgg,jlgg,shpchd,dw,glid,hscbj,itemlength,itemwidth,weight,pizhwh,tong ym,jixing,beizhu,itemheight,is_zhongyao)select top 1@spid,spbh,spmch,shpgg,jlgg,shpchd,dw,glid,hescbj,itemlength,itemwidth,weight,pizhwh,tongym ,jixing,beizhu,itemheight,casewhen @djlx=120 then '是' else '否' end from tmP_excel_djwhere glid=@glid and gzid = @gzidinsert into spkfjc(spid) values(@spid)fetch next from spid_cursor into @glidendclose spid_cursordeallocate spid_cursorselect * from (select b.spid,a.spbh,case when b.spid is null then '新品！'else b.spmch end as spmch,b.shpgg,b.shpchd,b.dw,b.jlgg,a.pihao,a.sxrq,case when isdate(a.baozhiqi) = 1 then convert(char(10), cast(a.baozhiqi as datetime), 20)else isnull(a.baozhiqi,'') end baozhiqi,sum(a.shl) as shl,cast((isnull(sum(a.je),0)/ case when isnull(sum(a.shl), 0) = 0 then 99999999999 else sum(a.shl) end) as decimal(14,5)) as dj,(isnull(sum(a.je),0) / case when isnull(sum(a.shl), 0) = 0 then 99999999999 else sum(a.shl) end ) as hshj,case when b.spid is null then 'clRed' else 'clBlack' end as __fcolor, 'default' as __bcolor,a.jixing,a.glid,a.hescbj,a.itemlength,a.itemheight,a.itemwidth ,a.weight,a.beizhu,a.pizhwh,a.tongymfrom tmp_excel_dj a (nolock) left join spkfk b (nolock) on a.glid = b.glid and b.beactive='是' and a.spmch = b.spmch and a.shpgg=b.shpgg and a.jlgg=b.jlggwhere a.gzid = @gzidgroup by b.spid, a.spbh,b.spmch,b.shpgg, b.shpchd, b.dw,b.jlgg,a.pihao,a.sxrq,a.baozhiqi,a.jixing,a.glid,a.hescbj,a.itemlength,a.itemheight,a.itemwidth,a.weight,a.beizhu,a.pi zhwh,a.tongym having sum(a.shl)<>0) a where a.shl <> 0order by a.spid */select a.spbh,a.tongym,a.spmch,a.shpgg,a.shpchd ,a.dw,isnull(b.hshj,0),isnull(b.dj,0),a.shlv, a.jlgg,a.zgjjxz,a.zjm,a.jj,a.ypbs,0 as shl ,'合格' as zhilqk ,a.leibie,a.jixing,a.chuffl,a.wdyq,a.pizhwh,a.zaobaoj,a.pfpj,a.zgshl,a.zdshj,c.chbdj,a.zhdcbj,a.spid,a.is_cyhydz,a.is_jbyw,'' as beizhu,0 as jiansh,'' as yanshjlfrom tmp_excel_dj b (nolock) , spkfk a (nolock) ,spkfjc c (nolock)where b.gzid = @gzid and a.spid = b.spid and a.beactive='是' and c.spid=b.spidorder by a.spbhendif @djlx in ('212')begin--需分摊品种库存表create table #t_kc(spid char(11),hw char(11),pihao char(15),baozhshl decimal(14,2),lingsshl decimal(14,2),shl decimal(14,2),sxrq char(10),baozhiqi char(10),jlgg int ,yyj int,gebjj decimal(14,2),huowname char(20))--整零表create table #t_alldd(spid char(11),shl decimal(14,2),jlgg int,biaoshi int)--整零库存表create table #t_allkc(spid char(11),hw char(11),pihao char(15),shl decimal(14,2),sxrq char(10),baozhiqi char(10),jlgg int,biaoshi int,is_mz int,yyj int,gebjj decimal(14,2), huowname char(20))--整货分摊表create table #t_ft_zj( spid char(11),hw char(11),pihao char(15),shl decimal(14,2),kcshl decimal(14,2), ftshl decimal(14,2), is_mz int,sxrq char(10),baozhiqi char(10), biaoshi int,gebjj decimal(14,2), huowname char(20) )--零货分摊表create table #t_ft_ls( spid char(11),hw char(11),pihao char(15),shl decimal(14,2),kcshl decimal(14,2), ftshl decimal(14,2), is_mz int,sxrq char(10),baozhiqi char(10), biaoshi int,gebjj decimal(14,2), huowname char(20) )--未分摊完成整货create table #t_wft_zj( spid char(11),shl decimal(14,2),ftshl decimal(14,2), wftshl decimal(14,2)-- 未分摊完成的数量+ 零散数量表create table #t_lsdd(spid char(11),shl decimal(14,2) ) --,biaoshi int)--分摊完成表create table #result(spid char(11),hw char(11),pihao char(15),shl decimal(14,2),sxrq char(10),baozhiqi char(10),gebjj decimal(14,2),huowname char(20))--待分摊品种select b.spid,floor(a.shl/b.jlgg) as baozhshl,(a.shl%b.jlgg) as lingsshl,a.shl,b.jlgg,a.dj,b.spbh,b.tongym,b.spmch,b.shpgg,b.shpchd,b.dw,b.koul,b.shlv,b.zgjjxz,b.zjm,b.jj,b.leibie,b.jixing,b.chuffl,b.wdyq,b.pizhwh,b.zaobaoj,b.pfpj,b.zgshl,b.zdshj,b.zhdcbj,b.is_cyhydz,b.is_jbywinto #t_ddfrom tmp_excel_dj a (nolock) join spkfk b (nolock) on a.spid=b.spidwhere a.gzid=@gzid and a.spid<>''-- =============================================--可销数量--库存批号数量select a.spid ,a.shl, a.hw,a.pihao,a.baozhiqi,a.sxrq ,a.gebjjinto #tmp_kcfrom sphwph a(nolock) join huoweizl b (nolock) on a.hw=b.hwjoin #t_dd c (nolock) on a.spid=c.spidwhere a.shl >0 and b.huowname not like '%区' and a.dangqzht='合格'-- 开票未出库数量select a.spid ,sum(a.shl) as shl , a.hw,a.pihao,a.baozhiqi,a.sxrqinto #tmp_kpwckfrom jzorder_mx a (nolock) join #t_dd b (nolock) on a.spid=b.spid ---销售开票未出库数量where a.djbh like 'XSG%' and a.is_zx='否'group by a.spid ,a.hw,a.pihao,a.baozhiqi,a.sxrq--采购退出开票未出库数量select a.spid ,sum(a.shl) as shl , a.hw,a.pihao,a.baozhiqi,a.sxrqinto #tmp_cgwckfrom jzorder_mx a (nolock) join #t_dd b (nolock) on a.spid=b.spidwhere a.djbh like 'JHT%' and is_zx='否'group by a.spid ,a.hw,a.pihao,a.baozhiqi,a.sxrq--采购退出正在开票数量select a.spid ,sum(a.shl) as shl , a.hw,a.pihao,a.baozhiqi,a.sxrqinto #tmp_kpzzkpfrom tmp_dj_jht114 a (nolock) join #t_dd b (nolock) on a.spid=b.spidgroup by a.spid ,a.hw,a.pihao,a.baozhiqi,a.sxrq--内部调拨正在开票数量select a.spid ,sum(a.shl) as shl , a.dchw as hw,a.pihao,a.baozhiqi,a.sxrqinto #tmp_dbzzkpfrom tmp_dj_CDB314 a (nolock) join #t_dd b (nolock) on a.spid=b.spidgroup by a.spid ,a.dchw,a.pihao,a.baozhiqi,a.sxrq--销售正在开票数量select a.spid ,sum(a.shl) as shl , a.hw,a.pihao,a.baozhiqi,a.sxrqinto #tmp_xszzkpfrom tmp_dj_XSG212 a (nolock) join #t_dd b (nolock) on a.spid=b.spidwhere a.hw<> '' and a.pihao<>''group by a.spid ,a.hw,a.pihao,a.baozhiqi,a.sxrqselect a.spid ,(isnull(a.shl,0)-isnull(b.shl,0)-isnull(c.shl,0)-isnull(d.shl,0)-isnull(e.shl,0)-isnull(f.s hl,0)) as shl , a.hw,a.pihao,a.baozhiqi,a.sxrq ,a.gebjjinto #tmp_kxshlfrom #tmp_kc a left join #tmp_kpwck b on a.spid=b.spid and a.hw=b.hw and a.pihao=b.pihao left join #tmp_cgwck c on a.spid=c.spid and a.hw=c.hw and a.pihao=c.pihaoleft join #tmp_kpzzkp d on a.spid=d.spid and a.hw=d.hw and a.pihao=d.pihaoleft join #tmp_dbzzkp e on a.spid=e.spid and a.hw=e.hw and a.pihao=e.pihaoleft join #tmp_xszzkp f on a.spid=f.spid and a.hw=f.hw and a.pihao=f.pihao--分摊品种库存insert #t_kc(spid,hw,pihao,baozhshl,lingsshl,shl,sxrq,baozhiqi,jlgg,yyj,gebjj,huowname )select a.spid,a.hw,a.pihao,case when c.yyj <3 and b.shl<b.jlgg then 0 else floor(a.shl/b.jlgg) end as baozhshl , --当零货时不计算包装数量case when c.yyj <3 and b.shl<b.jlgg then a.shl else a.shl % b.jlgg end as lingsshl, ----当零货时库存数量为零散数量a.shl,a.sxrq,a.baozhiqi ,b.jlgg,c.yyj,a.gebjj,c.huowname --货位优先顺序from #tmp_kxshl a,#t_dd b ,huoweizl cwhere a.spid=b.spid and a.hw=c.hw--删除临时表drop table #tmp_kc,#tmp_kpwck ,#tmp_cgwck , #tmp_kpzzkp , #tmp_dbzzkp , #tmp_xszzkp,#tmp_kxshl-- =============================================--整零分开insert #t_alldd(spid,shl,jlgg,biaoshi)select spid,baozhshl * jlgg as shl,jlgg,1 as biaoshi--into #t_allddfrom #t_dd where baozhshl <> 0union allselect spid,lingsshl as shl,jlgg,0 as biaoshi from #t_dd where lingsshl <> 0--分摊表insert #t_allkc(spid,hw,pihao,shl,sxrq,baozhiqi,jlgg,biaoshi,is_mz,yyj,gebjj,huowname )select a.spid,a.hw,a.pihao,(a.baozhshl * a.jlgg) as shl,a.sxrq,a.baozhiqi,a.jlgg,1 as biaoshi,1 as is_mz, a.yyj,a.gebjj,a.huownamefrom #t_kc a left join #t_dd b on a.spid = b.spidwhere a.baozhshl <> 0union allselect a.spid,a.hw,a.pihao,a.lingsshl as shl,a.sxrq,a.baozhiqi,a.jlgg,0 as biaoshi,0 as is_mz,a.yyj,a.gebjj,a.huownamefrom #t_kc a join #t_dd b on a.spid = b.spidwhere a.lingsshl <> 0--整件分摊insert #t_ft_zj(spid,hw,pihao,shl,kcshl,ftshl,biaoshi,is_mz,sxrq,baozhiqi,gebjj,huowname)select a.spid,b.hw,b.pihao,a.shl,b.shl as kcshl,0 as ftshl,b.biaoshi,b.is_mz,b.sxrq,b.baozhiqi,b.gebjj,b.huownamefrom #t_alldd ajoin #t_allkc b on a.spid = b.spid and a.biaoshi = b.biaoshiwhere a.biaoshi = 1order by b.spid,b.biaoshi,b.baozhiqi, b.yyj desc ,b.shl --按baozhiqi,yyj排序，整件货位出库优先declare@spid1 char(11),@xsshl decimal(14,2),@tmp_ftshl decimal(14,2)set @spid1 = '!@#'update #t_ft_zjset @tmp_ftshl = ftshl = case when @spid1 = spid thencase when @xsshl >= kcshl then kcshl else @xsshl endelsecase when shl >= kcshl then kcshl else shl endend,@xsshl = case when @spid1 = spid thencase when @xsshl >= kcshl then @xsshl - kcshl else 0 endelsecase when shl >= kcshl then shl - kcshl else 0 endend,@spid1 = spid-- 未分摊完成的整件数量insert #t_wft_zj(spid, shl, ftshl, wftshl)select a.spid, a.shl, sum(isnull(b.ftshl,0)) as ftshl, a.shl - sum(isnull(b.ftshl,0)) as wftshlfrom #t_alldd aleft join #t_ft_zj b on a.spid = b.spidwhere a.biaoshi = 1group by a.spid, a.shlhaving a.shl - sum(isnull(b.ftshl,0)) > 0-- 未分摊完成的数量+ 零散数量insert into #t_lsdd(spid,shl)select a.spid,sum(shl) as shlfrom (select spid,wftshl as shl from #t_wft_zjunion allselect spid,shl from #t_alldd where biaoshi = 0) agroup by a.spid-- 重新判断是否满足库存update a set is_mz = case when a.shl - isnull(c.ftshl,0) >= isnull(b.shl,0) then 0 else 1 endfrom #t_allkc aleft join #t_lsdd b on a.spid = b.spidleft join #t_ft_zj c on a.spid = c.spid and a.hw = c.hw and a.pihao = c.pihao and a.biaoshi = c.biaoshi-- 零散分摊insert #t_ft_ls(spid,hw,pihao,shl,kcshl,ftshl,biaoshi,is_mz,sxrq,baozhiqi,gebjj,huowname)select a.spid,b.hw,b.pihao,a.shl,b.shl - isnull(c.ftshl,0) as kcshl,0 as ftshl,b.biaoshi,b.is_mz,b.sxrq,b.baozhiqi,b.gebjj,b.huownamefrom #t_lsdd ajoin #t_allkc b on a.spid = b.spidleft join #t_ft_zj c on b.spid = c.spid and b.hw = c.hw and b.pihao = c.pihao and b.biaoshi=c.biaoshiorder by b.spid,b.yyj ,b.biaoshi,b.is_mz,b.baozhiqi,b.shl - isnull(c.ftshl,0)set @spid1 = '!@#'update #t_ft_lsset @tmp_ftshl = ftshl = case when @spid1 = spid thencase when @xsshl >= kcshl then kcshl else @xsshl endelsecase when shl >= kcshl then kcshlelse shl endend,@xsshl = case when @spid1 = spid thencase when @xsshl >= kcshl then @xsshl - kcshl else 0 endelsecase when shl >= kcshl then shl - kcshl else 0 endend,@spid1 = spidinsert #result(spid,hw,pihao,shl,gebjj,huowname,sxrq,baozhiqi)select spid,hw,pihao,ftshl as shl,gebjj,huowname,sxrq,baozhiqifrom #t_ft_zj where ftshl <> 0union allselect spid,hw,pihao,ftshl as shl,gebjj,huowname,sxrq,baozhiqifrom #t_ft_ls where ftshl <> 0order by spid,hw,pihao--汇总数据,保证同一批号,货位是一条记录select spid,hw,pihao,sum(shl) as shl,gebjj,baozhiqi,sxrq,huownameinto #result1from #result a (nolock)group by spid,hw,pihao,gebjj,baozhiqi,sxrq,huowname-------------------------核算赠品-----------------------核算赠品数量select floor(e.shl/a.min_shl)*a.shl as jiansh ,c.zengpmch,c.zengpgg,c.zengpcd ,c.zengptzs ,a.cuxiao ,c.zengpbh,c.zengpid as yanshjl,b.zengpzkcshl,e.pihao,e.hw,e.shl,e.spidinto #zp1from zpzcspmx a (nolock),zpkfk c (nolock) ,zpkfjc b (nolock), #result1 ewhere a.zengpid=c.zengpid and a.zengpid=b.zengpid and a.spid=e.spid and b.zengpzkcshl>0 and a.min_shl <= e.shl and a.end_date >=GETDATE() -- and charindex(rtrim(@jyfsh),a.zpkzdwlx)>0 --限制促销单类型order by e.spid,e.hw,e.pihao--正在开票赠品数量select sum(b.jiansh) as kpzpshl1,b.yanshjlinto #zp3from tmp_dj_XSG212 a (nolock) ,#zp1 bwhere --a.spid=@spid anda.yanshjl=b.yanshjl --and (a.gzid<>@gzid or ( a.dj_sn<>@dj_sn and a.gzid=@gzid ))GROUP BY b.yanshjl--销售开票未出库赠品数量select sum(a.jiansh) as wckzpshl ,c.yanshjlinto #zp4from jzorder_mx a,(select shenhe,djbh from jzorder_hz where shenhe='否') b ,#zp1 cwhere a.djbh=b.djbh and a.yanshjl=c.yanshjl and a.djbh like 'XSG%'GROUP BY c.yanshjl--可赠数量：select (a.zengpzkcshl-isnull(b.kpzpshl1,0)-isnull(c.wckzpshl,0)) as zpkzshl,a.yanshjl,a.zengpzkcshlinto #zp6from #zp1 a left outer join #zp3 b on a.yanshjl=b.yanshjl left outer join #zp4 c on a.yanshjl=c.yanshjl--赠品表并去掉重复的select distinct a.jiansh ,b.zpkzshl,a.zengpmch,a.zengpgg,a.cuxiao as beizhu,a.zengpcd ,a.zengptzs ,a.zengpbh,a.yanshjl ,a.pihao,a.hw,a.shl,a.spid, 0 as biaoshi into #zp7from #zp1 a left outer join #zp6 b on a.yanshjl=b.yanshjlwhere b.zpkzshl>=a.jianshorder by a.spid,a.hw,a.pihao--去掉重复的declare@hw1 char(11),@biaoshi int ,@pihao1 char(20)set @spid1 = '!@#'set @hw1 = '!@#'set @pihao1 = '!@#'update #zp7set @biaoshi=biaoshi = case when @spid1 = spid and @hw1=hw and @pihao1=pihao then 1 else 0 end,@spid1 = spid,@hw1=hw,@pihao1=pihao--去掉重复并生成赠品表select a.jiansh ,a.zpkzshl,a.zengpmch,a.zengpgg,a.beizhu,a.zengpcd ,a.zengptzs ,a.zengpbh,a.yan shjl ,a.pihao,a.hw,a.shl,a.spid, a.biaoshiinto #zpp7from #zp7 a where a.biaoshi=0--删除临时表drop table #zp1,#zp3,#zp4,#zp6,#zp7--------------------------------------------------------- 返回结果selectc.spbh,c.tongym,c.spmch,c.shpgg,c.shpchd,d.kcshl,c.dw,a.huowname ,a.pihao ,a.sxrq,a.baozhiqi,a. shl,c.koul,c.shlv,c.jlgg,c.zgjjxz,c.zjm,c.jj,c.leibie,c.jixing,c.chuffl,c.wdyq,c.pizhwh,c.zaobaoj,c.pfpj,c.zgshl,c.zdshj,d.chbdj,c.zhdcbj,a.spid,c.is_cyhydz,c.is_jbyw,g.jiansh,g.zpkzshl,g.zengpmch,g.zengpgg,g.zengpcd,g.zeng ptzs,g.beizhu,g.yanshjl,a.hw ,a.gebjj,c.dj as hshj,c.dj ,'合格' as zhilqkfrom #result1 a (nolock)join spkfjc d (nolock) on a.spid=d.spidjoin #t_dd c (nolock) on a.spid=c.spidleft outer join #zpp7 g on a.spid=g.spid and a.hw=g.hw and a.shl=g.shl and a.pihao=g.pihao----删除临时表drop table #t_dd, #t_kc, #t_alldd, #t_allkc, #t_ft_zj, #t_ft_ls, #t_wft_zj, #t_lsdd, #result , #result1, #zpp7endif @djlx in ('888')begin/* select a.* from (select b.spid,a.rq,a.ywy,a.djbh,a.spbh,case --when b.spid is null then '新品！'when a.spbh=b.spbh and (a.spmch!= b.spmch or a.shpgg!=b.shpgg or a.jlgg!=b.jlgg) then '需核对商品资料！'else b.spmch end as spmch,a.shpgg,a.shpchd,a.jlgg,a.dw,a.zhdcbj,a.huiytj,a.hscbj as lshj,a.pfpj,a.zdshj,a.fuzr,a.djbh as xgdjbh,convert(char(10),getdate(),21) as zhixrq,convert(char(8),getdate(),8) as ontime,b.shlv,b.hscbj,c.chbdj,case when /*b.spid is null*/a.spbh=b.spbh and (a.spmch!= b.spmch or a.shpgg!=b.shpgg or a.jlgg!=b.jlgg) then 'clRed' else 'clBlack' end as __fcolor, 'default' as__bcolorfrom tmp_excel_jgtz a (nolock) left join spkfk b (nolock) on a.spbh=b.spbh and b.beactive='是' --and a.spmch = b.spmch and a.shpgg=b.shpgg and a.jlgg=b.jlggand ( a.zhdcbj<>b.zhdcbj or a.huiytj<>b.huiytj or a.pfpj<>b.pfpj or a.zdshj<>b.zdshj)l eft join spkfjc c (nolock) on b.spid=c.spidwhere a.gzid = @gzid and b.spid is not null) a--where a.spbh not in(select spbh from spkfk b(nolock)where a.spmch<>'新品!' and a.zhdcbj=b.zhdcbj and a.huiytj=b.huiytj and a.pfpj=b.pfpj and a.zdshj=b.zdshj )order by a.spid*/select a.spbh,a.tongym,a.spmch,a.shpgg,a.shpchd ,a.dw,isnull(b.hshj,0),isnull(b.dj,0),a.shlv, a.jlgg,a.zgjjxz,a.zjm,a.jj,a.ypbs,0 as shl ,'合格' as zhilqk ,a.leibie,a.jixing,a.chuffl,a.wdyq,a.pizhwh,a.zaobaoj,a.pfpj,a.zgshl,a.zdshj,c.chbdj,a.zhdcbj,a.spid,a .is_cyhydz,a.is_jbyw,'' as beizhu,0 as jiansh,'' as yanshjlfrom tmp_excel_dj b (nolock) , spkfk a (nolock) ,spkfjc c (nolock)where b.gzid = @gzid and a.spid = b.spid and a.beactive='是' and c.spid=b.spidorder by a.spbhendif @djlx in ('003')beginselect spbh as fangabh,kcsl from tmp_execl_spkc (nolock) where gzid=@gzidendendif @action = 1beginif @djlx in ('112', '120','212')begindelete from tmp_excel_dj where gzid = @gzidendif @djlx in ('888')begindelete from tmp_excel_jgtc where gzid = @gzidendif @djlx in ('003')begindelete from tmp_execl_spkc where gzid = @gzidendendeND导入的计划,需要以时空资料为本,也可以用spbh,上面赠品需要相应的功能支持.如果你的时空里没有tmp_excel_dj这张表,就新建一个吧.存储过程有点复杂,服务器要结实CS单据中读取Excel文件函数说明：配置：1、在单据中增加ReadExcel函数调用；2、配置程序目录下的DJExcel.ini文件，配置方法请参照文件中相关说明；3、以插入语句使用为例描述此函数的工作流程：3.1、ReadExcel函数首先调用“插入x”语句将Excel表中的数据读入SQLServer的tmp_excel_dj表中（tmp_excel_dj表的结构根据使用状况确定，可临时调整）此处通过ODBC 将Excel表数据直接插入到指定的SQLServer中，例如：插入1=insert into tmp_excel_dj(gzid,spbh,spmch,shpgg,jlgg,shpchd,dw,shl,je,pihao,sxrq,插入2=baozhiqi,glid,hescbj,itemlength,itemheight,itemwidth,weight,beizhu,jixing,tongym,pizhwh) IN [ODBC]插入3= [ODBC;Driver=SQL Server;UID=sa;PWD=:password;Server=:server;DataBase=:database]插入4= select :gzid, a.药品编号, a.药品名称, a.药品规格, a.包装数量, a.产地, a.包装单位,插入5= a.数量, a.金额, a.批号, a.有效期至, a.生产日期,a.集团商品编码,a.核算成本价, 插入6= a.单品长,a.单品高,a.单品宽,a.单品重量,a.备注,a.剂型,a.通用名,a.批准文号插入7= from [Sheet1$] a变量：:gzid临时生成，用于区分本次插入到tmp_excel_dj表的数据内容；注意：ReadExcel函数执行时会提示读入的Excel文件，[Sheet1]是要读取的工作表，以'$'结尾；系统将第一行识别为字段名（请注意不要拼错字段名，关键字不能写错）；之后的处理都是与我们熟悉的与SQLServer打交道了；3.2、插入完成后，系统会调用“查询x”语句，根据刚才插入的:GZID变参进行读取，例如：查询1= exec wms_read_excel @action = 0, @djlx = 112, @gzid = :gzid变量：:gzid，是之前读取Excel表时生成的临时标识；注意：该语句执行完毕会调用wms_read_excel过程（需修改），到tmp_excel_dj表中读取刚才插入的数据内容，并在界面展现出来，此数据集作为单据的明细来源（wms_read_excel 过程的作用类似于单据提取中的明细数据组织，所以应在此将所有关键字段备齐，具体需要的字段可参照相关提取方案的明细数据组织部分）3.3、进行选择后，将选中的明细插入到单据明细中（同单据提取）3.4、插入完毕，系统调用“删除x”语句，根据刚才插入的:GZID，将tmp_excel_dj表中使用完毕的临时数据删除；例如：删除1=exec wms_read_excel @action = 1, @djlx = 112, @gzid = :gzid变量：:gzid，是之前读取Excel表时生成的临时标识；删除完毕后此变量已不存在；3.5、总结：这里的:gzid变参和单据中的gzid不是一回事，他们之间没有关联，要区别理解；先插入临时表再读取是因为通过ODBC直接读取Excel时，其能够支持得函数非常有限，往往不能满足需要；Excel文件中要有能够和当前系统相关联的字段，否则导入单据后可能造成无法识别时空产品成本核算的一点感悟存货计价，是对存货购进、生产和销售的入账价、发出价及库存价的确定。