医药网订单对接ERP软件订单接口

xiaoxiao2021-02-28  10

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Orderinterface] as create table #a --创建临时表#a储存查询的结果集 ( ID   int IDENTITY (0,1),--自增列从0开始每次增加1 o_id int,--医药网订单ID o_buyer_internal_code varchar(256),--采购客户ERP内码 o_add_time datetime, --订单创建时间 o_note varchar(512),--订单备注 );  --将查询结果集插入到临时表中 INSERT INTO #a select o_id,o_buyer_internal_code,o_add_time ,o_note from mph_order where is_fetch = '否' --创建临时变量 declare @ii int,--循环更新变量 @i int, @jj int,--循环更新变量 @j int, @Ordermaxid int,--千方OrderIndex表最大id @oo_id int--医药网订单id    set @ii=0   --从0开始 set @jj=0   --从0开始 --查询临时表a的ID总数赋值给i控制循环次数 select @i = count(ID) from #a; --创建临时变量 declare @BRec int,--采购客户ERP内码 @ERec int, @CheckEID int, @BillEID int, @KRec int, @BillDate smalldatetime,--订单创建时间 @ReachDate smalldatetime, @Billcode varchar(50), @billtype smallint, @totalmoney  money, @totalqty numeric(18, 6), @period tinyint, @checked tinyint, @BillOver tinyint, @Comment varchar(256), --订单备注 @explain char(256), @plan_id int, @gspid int, @Tax smallmoney, @ChangeID int, @YS_Weway varchar(200), @YS_ADDR varchar(200), @YS_DW varchar(200) set @ERec=120 set @CheckEID=107 set @BillEID=107 set @KRec=5 set @ReachDate=GETDATE() set @billtype=300 set @totalmoney=10 set @totalqty=10 set @period=1 set @checked=1 set @BillOver=0  set @explain='' set @plan_id=0  set @gspid=0 set @Tax=-10 set @ChangeID=1983  set @YS_Weway='' set @YS_ADDR=''  set @YS_DW='' create table #c --创建临时表#c储存千方订单表最大id ( ID   int IDENTITY (0,1),--自增列从0开始每次增加1 maxid int, ); create table #d --创建临时表#c储存千方订单表最大id ( ID   int IDENTITY (0,1),--自增列从0开始每次增加1 o_id int,--医药网订单ID ); --循环判断 WHILE @ii<@i      BEGIN select @Billcode= o_id from #a where ID=@ii  create table #b --创建临时表#b储存查询的结果集 ( ID   int IDENTITY (0,1),--自增列从0开始每次增加1 od_id int, od_g_in_sn varchar(128), maxid int, od_buy_number int, od_price decimal(14, 6), ); select @BRec = o_buyer_internal_code from #a where ID=@ii  select @BillDate = o_add_time from #a where ID=@ii select @Comment = o_note from #a where ID=@ii --千方订单插入数据 insert into [OrderIndex](BRec,ERec,CheckEID,BillEID,KRec,BillDate,ReachDate,  Billcode,billtype, totalmoney,  totalqty, period, checked, BillOver,  Comment, explain, plan_id, gspid, Tax,ChangeID, YS_Weway, YS_ADDR, YS_DW ) values(@BRec,@ERec,@CheckEID,@BillEID,@KRec,@BillDate,@ReachDate,  @Billcode,@billtype, @totalmoney,@totalqty,@period,@checked,@BillOver,  @Comment,@explain,@plan_id,@gspid,@Tax,@ChangeID,@YS_Weway,@YS_ADDR,@YS_DW) --千方订单表最大id select @Ordermaxid = max(BillID) from OrderIndex --医药网订单详情对应的订单id select @oo_id = o_id from #a where ID=@ii --修改中间表中ERP提取状态 UPDATE mph_order SET is_fetch = '是' WHERE o_id = @oo_id --医药网订单明细对应订单id插入到临时表#b中 INSERT INTO #b (od_id,od_g_in_sn,maxid,od_buy_number,od_price) select od_id,od_g_in_sn,0,od_buy_number,od_price from mph_order_detail where o_id=@oo_id  select * from #b select @j = count(ID) from #b; select  count(ID) from #b; -- if @j = 1      begin         INSERT INTO #c select max(BillID) from OrderIndex              end     UPDATE #b SET maxid = @Ordermaxid where maxid=0     select * from #b -- INSERT INTO OrderBill(BillID,PRec,KRec,qty,AssQty,price,AssPrice ,total,Tax,TaxPrice,TaxTotal,comment,mxord)  Select maxid,od_g_in_sn,1,od_buy_number,od_buy_number, od_price,od_price,(od_buy_number*od_price),17, ((od_price*0.17)+od_price),(((od_price*0.17)+od_price)*od_buy_number), od_id,1 from #b  drop table  #b SET @ii=@ii+1 --更新循环变量 END
转载请注明原文地址: https://www.6miu.com/read-2400009.html

最新回复(0)