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*@dtlQt