set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[I6GetPurOrderFromCRM] as begin
SET NOCOUNT ON
select *,ROW_NUMBER() OVER(ORDER BY orderid) AS rowNumber into #TempSenderCRMDetailTable from (select * from openquery(TOFANSKICRM,'select b.* from ERPOrderDetailSync b where feedback <> 1')) as tempData declare @max1 int select @max1=max(rowNumber) from #TempSenderCRMDetailTable declare @rowNo1 int set @rowNo1=1
while @rowNo1<=@max1 begin declare @dtlOrderId varchar(30) declare @dtlLineId int declare @dtlItemNo varchar(30) declare @dtlRanks varchar(5) declare @dtlMsUnit varchar(5) declare @dtlQty decimal(18,8) declare @dtlPrice decimal(18,8) declare @dtlOrderSum decimal(18,8) declare @dtlOrderCurType varchar(4) declare @dtlexchgRate decimal(18,8) declare @dtlReqDate datetime declare @dtlDeliverDate datetime declare @dtlTaxRate decimal(18,8) declare @dtlTaxSum decimal(18,8) declare @dtlRemarks varchar(180) -- declare @dtlpriCode varchar(10) declare @dtlDiscount decimal(18,8)
select @dtlOrderId=orderid,@dtlLineId=lineid,@dtlItemNo=itemno,@dtlRanks=ranks, @dtlMsUnit=msunit,@dtlQty=qty,@dtlPrice=price,@dtlOrderSum=ordersum,@dtlOrderCurType=curtype, @dtlexchgRate=exchgRate,@dtlReqDate=reqdate,@dtlDeliverDate=deliverdate,@dtlTaxRate=taxrate, @dtlTaxSum=taxsum,@dtlRemarks=remarks,@dtlDiscount=discount from #TempSenderCRMDetailTable where rowNumber = @rowNo1
insert into ec_ordersdtl (orderid,lineid,orderno,itemno,ranks,msunit,qty,price,ordersum,curtype,exchgrate,reqdate, deliverdate,taxrate,remarks,fprice,fordersum,pricode,childflg,parentid,sqty,makebalflg,discrate,noprice,notaxordsum, ordhwsum,fordhwsum,notaxordhwsum,taxsum,notaxdissum,fnotaxordsum,fnotaxordhwsum,ftaxsum,fnotaxdissum,fnoprice,discount,purseflg,sendflg, discsum,fdiscsum,unitchgn,chgbase,disdata,pricexs) values(@dtlOrderId,@dtlLineId,@dtlOrderId,@dtlItemNo,@dtlRanks,@dtlMsUnit,@dtlQty,@dtlPrice,@dtlOrderSum,@dtlOrderCurType,@dtlexchgRate, @dtlReqDate,@dtlDeliverDate,@dtlTaxRate,@dtlRemarks,@dtlPrice,@dtlOrderSum,'026',0,0,@dtlQty,0,@dtlDiscount,round(isnull(@dtlPrice,0)/1.17,8), round(@dtlOrderSum/1.17 ,2),round(@dtlOrderSum/@dtlDiscount,2),round(@dtlOrderSum/@dtlDiscount,2),round(isnull(@dtlQty,0)*isnull(@dtlPrice,0)/1.17,2), round(@dtlOrderSum/1.17*0.17,2),round(isnull(@dtlQty,0)*isnull(@dtlPrice,0)/1.17-@dtlOrderSum/1.17,2),round(@dtlOrderSum/1.17,2), round(isnull(@dtlQty,0)*isnull(@dtlPrice,0)/1.17,2), round(@dtlOrderSum-@dtlOrderSum/1.17,2), round(isnull(@dtlQty,0)*isnull(@dtlPrice,0)/1.17-@dtlOrderSum/1.17,2), round(isnull(@dtlPrice,0)/1.17,8), isnull(@dtlDiscount,0)*@dtlPrice,0,0,round(@dtlPrice*@dtlQty-@dtlOrderSum,2),round(@dtlPrice*@dtlQty-@dtlOrderSum,2),1,1,0,1)
--插入日志表 insert into crm_to_i6_log (dbtbname,orderid,dblineid,insertdate) values('ec_ordersdtl',@dtlOrderId,@dtlLineId,getdate())
-- update crm feedback=1 declare @dtlupdateC varchar(300) declare @dtlselectC varchar(200) set @dtlselectC = '''select * from ERPOrderDetailSync where orderId = '''''+@dtlOrderId+''''' and lineid = '+convert(varchar,@dtlLineId)+'''' set @dtlupdateC = 'update openquery(TOFANSKICRM, '+@dtlselectC+') set feedback = 1;' exec(@dtlupdateC)
set @rowNo1=@rowNo1+1 end drop table #TempSenderCRMDetailTable
select *,ROW_NUMBER() OVER(ORDER BY orderid) AS rowNumber into #TempCrmSenderTable from (select * from openquery(TOFANSKICRM,'select * from ERPOrderInfoSync ')where feedback <> 1) as tempData declare @max int select @max=max(rowNumber) from #TempCrmSenderTable declare @rowNo int set @rowNo=1 while @rowNo<=@max begin
declare @orderId varchar(30) declare @orderNo varchar(30) declare @recordDate datetime declare @recordMan varchar(15) declare @orderComp varchar(15) declare @orderDept varchar(15) declare @oCode varchar(20) declare @orderSum decimal(18,2) declare @orderType varchar(5) declare @salePre varchar(5) declare @recComp varchar(20) declare @recAddr varchar(180) declare @appMan varchar(20) declare @appDate datetime declare @deliverAddr varchar(80) declare @deliverDate datetime declare @curType varchar(3) declare @exchgRate decimal(18,8) declare @ordStatus int declare @disData int declare @endStatus int declare @docFrom char(1) declare @linkMan varchar(20) declare @cellPhone varchar(50) declare @listSum decimal(18,8) declare @orderCatgy char(2) declare @usercomp varchar(30) declare @rundept varchar(15) declare @runemp varchar(15)
--取数据 select @orderId=orderId,@recordDate=recordDate,@recordMan=recordman,@orderComp=ordercomp,@orderDept=orderDept, @oCode=ocode,@orderSum=orderSum,@orderType=orderType,@salePre=salepre,@recComp=reccomp,@recAddr=recAddr,@appMan =appMan,@appDate=appdate,@deliverAddr=deliverAddr,@deliverDate=deliverDate,@curType=curType,@exchgRate=exchgRate, @endStatus=ordStatus,@disData=disdata,@endStatus=endStatus,@docFrom=docFrom,@linkMan=linkman,@cellPhone=cellphone, @listSum=listSum,@orderCatgy=order_category from #TempCrmSenderTable where rowNumber = @rowNo
select @rundept = deptno,@runemp=empno from fg_customfile where compno=@ordercomp
if (@orderCatgy='02') begin set @orderNo= 'F_SN'+right(@orderId,11) set @usercomp = @orderComp update ec_ordersdtl set orderno=@orderNo where orderid=@orderId end else begin set @orderNo = @orderId end if (@orderType='01') begin set @orderType ='09' end else if (@orderType='03') begin set @orderType='01' end
insert into ec_ordersmst(orderid,orderno,recorddate,recordman,ordercomp,orderdept,ocode,ordersum,ordertype,salepre, reccomp,recaddr,appman,appdate,deliveraddr,deliverdate,curtype,exchgrate,ordstatus,disdata,endstatus,docfrom,fordhwsum, reclinkman,rectelephone,statcomp,user_001,paycomp,disctype,sendflg,appflg,endopt,priceget,sendwh,user_002,verdtm,rundept, runemp,usercomp,ordhwsum,orderdisc,discrate,fordersum,forderdisc,purseflg,fnotaxordsum,fnotaxordhwsum,ftaxsum,fnotaxdissum, notaxordsum,notaxordhwsum,taxsum,notaxdissum) values(@orderId,@orderNo,@recordDate,@recordMan,@orderComp,@orderDept,@oCode,@orderSum,@orderType,@salePre,@recComp, @recAddr,@appMan,@appDate,@deliverAddr,@deliverDate,@curType,@exchgRate,@endStatus,1,@endStatus,@docFrom,@listSum, @linkMan,@cellPhone,@orderComp,'sage',@orderComp,0,0,1,99,0,1001,@orderCatgy,getdate(),@rundept,@runemp,@usercomp,@listsum,@listsum-@ordersum,@ordersum/@listsum, @orderSum,@listsum-@ordersum,0,round(@ordersum/1.17,2),round(@listsum-@ordersum/1.17*0.17,2),round(@ordersum/1.17*0.17,2),round(@listsum-@ordersum/1.17*0.17-@ordersum/1.17-@ordersum/1.17*0.17,2), round(@ordersum/1.17,2),round(@listsum/1.17,2),round(@ordersum/1.17*0.17,2),round(@listsum-@ordersum/1.17*0.17-@ordersum/1.17-@ordersum/1.17*0.17,2))--
--插入日志表 insert into crm_to_i6_log (dbtbname,orderid,insertdate) values('ec_ordersmst',@orderId,getdate())
insert into ec_carryinfo(orderid,lineid,orderno) values( @orderid,1,@orderno)
--插入日志表 insert into crm_to_i6_log (dbtbname,orderid,insertdate) values('ec_carryinfo',@orderId,getdate())
-- update crm feedback=1 exec I6GetPurOrderFromCRM select * from fg_customfile declare @updateC varchar(300) declare @selectC varchar(200) print @orderId set @selectC = '''select * from ERPOrderInfoSync where orderId = '''''+@orderId+'''''''' set @updateC = 'update openquery(TOFANSKICRM, '+@selectC+') set feedback = 1;' -- select * from openquery(TOFANSKICRM,'select * from ERPOrderInfoSync' ) exec I6GetPurOrderFromCRM exec(@updateC)
set @rowNo=@rowNo+1 end drop table #TempCrmSenderTable
end
|
|
来自: tjmarm > 《Sql Server》