--插入日志表
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