yilv_new/AA/sql/采购入库单到中间.sql

--将采购入库单传递到中间库(委外订单不受约束)
if exists (select * from sysobjects where id = object_id('dbo.usp_transrdrecord01') )
        drop procedure dbo.usp_transrdrecord01
go

Create  PROCEDURE usp_transrdrecord01

@v_start			datetime,			--开始时间
@v_end   			datetime    		--结束时间

as

select Id into #t_01 from RdRecord01 r
where (dnmaketime>=@v_start and dnmaketime <= @v_end and ISNULL(cDefine5,0) = 0)
	or (exists (select Id  from RdRecord01  where cBusType='委外加工' and ID=r.ID)    )
	
if @@ROWCOUNT=0 return  
	   



delete from wtu..rdrecord01  where  exists (select id from #t_01 where ID=wtu..rdrecord01.ID)
delete from wtu..rdrecords01 where  exists (select id from #t_01 where ID=wtu..rdrecords01.ID)
----定义游标(解决排它锁),让游标指向sql查询的结果
--declare cursors cursor for
--	select id from #t_01
--open cursors
--	declare @num int
--	fetch next from cursors into @num
--	while(@@FETCH_STATUS=0)
--	begin
--	delete from wtu..RdRecord01  where  ID=@num
--	delete from wtu..rdrecords01 where ID=@num
--	fetch next from cursors into @num
--	end
--close cursors
--deallocate cursors --释放游标,释放完不能open

insert into wtu..RdRecord01
(ID,
bRdFlag,
cVouchType,
cBusType,
cSource,
cBusCode,
cWhCode,
dDate,
cCode,
cRdCode,
cDepCode,
cPersonCode,
cPTCode,
cSTCode,
cCusCode,
cVenCode,
cOrderCode,
cARVCode,
cBillCode,
cDLCode,
cProBatch,
cHandler,
cMemo,
bTransFlag,
cAccounter,
cMaker,
cDefine1,
cDefine2,
cDefine3,
cDefine4,
cDefine5,
cDefine6,
cDefine7,
cDefine8,
cDefine9,
cDefine10,
dKeepDate,
dVeriDate,
bpufirst,
biafirst,
iMQuantity,
dARVDate,
cChkCode,
dChkDate,
cChkPerson,
VT_ID,
bIsSTQc,
cDefine11,
cDefine12,
cDefine13,
cDefine14,
cDefine15,
cDefine16,
gspcheck,
ipurorderid,
ipurarriveid,
iarriveid,
isalebillid,

iTaxRate,
iExchRate,
cExch_Name,
bOMFirst,
bFromPreYear,
bIsLsQuery,
bIsComplement,
iDiscountTaxType,
ireturncount,
iverifystate,
iswfcontrolled,
cModifyPerson,
dModifyDate,
dnmaketime,
dnmodifytime,
dnverifytime,
bredvouch,
cVenPUOMProtocol,
dCreditStart,
iCreditPeriod,
dGatheringDate,
bCredit,
iFlowId,
cPZID,
cSourceLs,
cSourceCodeLs,
iPrintCount,
csysbarcode,
cCurrentAuditor,
cCheckSignFlag,
cGCRouteCode
)
select 
r.ID,
bRdFlag,
cVouchType,
cBusType,
cSource,
cBusCode,
cWhCode,
dDate,
cCode,
cRdCode,
cDepCode,
cPersonCode,
cPTCode,
cSTCode,
cCusCode,
cVenCode,
cOrderCode,
cARVCode,
cBillCode,
cDLCode,
cProBatch,
cHandler,
cMemo,
bTransFlag,
cAccounter,
cMaker,
cDefine1,
cDefine2,
cDefine3,
cDefine4,
cDefine5,
cDefine6,
cDefine7,
cDefine8,
cDefine9,
cDefine10,
dKeepDate,
dVeriDate,
bpufirst,
biafirst,
iMQuantity,
dARVDate,
cChkCode,
dChkDate,
cChkPerson,
VT_ID,
bIsSTQc,
cDefine11,
cDefine12,
cDefine13,
cDefine14,
cDefine15,
cDefine16,
gspcheck,
ipurorderid,
ipurarriveid,
iarriveid,
isalebillid,

iTaxRate,
iExchRate,
cExch_Name,
bOMFirst,
bFromPreYear,
bIsLsQuery,
bIsComplement,
iDiscountTaxType,
ireturncount,
iverifystate,
iswfcontrolled,
cModifyPerson,
dModifyDate,
dnmaketime,
dnmodifytime,
dnverifytime,
bredvouch,
cVenPUOMProtocol,
dCreditStart,
iCreditPeriod,
dGatheringDate,
bCredit,
iFlowId,
cPZID,
cSourceLs,
cSourceCodeLs,
iPrintCount,
csysbarcode,
cCurrentAuditor,
cCheckSignFlag,
cGCRouteCode
 
from RdRecord01 r where  exists(select ID from #t_01 t where t.ID=r.ID)


---------------------------------------------
insert into wtu..RdRecords01
(AutoID,
ID,
cInvCode,
iNum,
iQuantity,
iUnitCost,
iPrice,
iAPrice,
iPUnitCost,
iPPrice,
cBatch,
cVouchCode,
cInVouchCode,
cinvouchtype,
iSOutQuantity,
iSOutNum,
cFree1,
cFree2,
iFlag,
dSDate,
iTax,
iSQuantity,
iSNum,
iMoney,
iFNum,
iFQuantity,
dVDate,
cPosition,
cDefine22,
cDefine23,
cDefine24,
cDefine25,
cDefine26,
cDefine27,
cItem_class,
cItemCode,
iPOsID,
fACost,
cName,
cItemCName,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
cBarCode,
iNQuantity,
iNNum,
cAssUnit,
dMadeDate,
iMassDate,
cDefine28,
cDefine29,
cDefine30,
cDefine31,
cDefine32,
cDefine33,
cDefine34,
cDefine35,
cDefine36,
cDefine37,
iCheckIds,
cBVencode,
chVencode,
bGsp,
cGspState,
iArrsId,
cCheckCode,
iCheckIdBaks,
cRejectCode,
iRejectIds,
cCheckPersonCode,
dCheckDate,
iOriTaxCost,
iOriCost,
iOriMoney,
iOriTaxPrice,
ioriSum,
iTaxRate,
iTaxPrice,
iSum,
bTaxCost,
cPOID,
cMassUnit,
iMaterialFee,
iProcessCost,
iProcessFee,
dMSDate,
iSMaterialFee,
iSProcessFee,
iOMoDID,
strContractId,
strCode,
bChecked,
bRelated,
iOMoMID,
iMatSettleState,
iBillSettleCount,
bLPUseFree,
iOriTrackID,
coritracktype,
cbaccounter,
dbKeepDate,
bCosting,
iSumBillQuantity,
bVMIUsed,
iVMISettleQuantity,
iVMISettleNum,
cvmivencode,
iInvSNCount,
cwhpersoncode,
cwhpersonname,
impcost,
iIMOSID,
iIMBSID,
cbarvcode,
dbarvdate,
iinvexchrate,

comcode,
strContractGUID,
iExpiratDateCalcu



)
select 
AutoID,
r.ID,
cInvCode,
iNum,
iQuantity,
iUnitCost,
iPrice,
iAPrice,
iPUnitCost,
iPPrice,
cBatch,
cVouchCode,
cInVouchCode,
cinvouchtype,
iSOutQuantity,
iSOutNum,
cFree1,
cFree2,
iFlag,
dSDate,
iTax,
iSQuantity,
iSNum,
iMoney,
iFNum,
iFQuantity,
dVDate,
cPosition,
cDefine22,
cDefine23,
cDefine24,
cDefine25,
cDefine26,
cDefine27,
cItem_class,
cItemCode,
iPOsID,
fACost,
cName,
cItemCName,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
cBarCode,
iNQuantity,
iNNum,
cAssUnit,
dMadeDate,
iMassDate,
cDefine28,
cDefine29,
cDefine30,
cDefine31,
cDefine32,
cDefine33,
cDefine34,
cDefine35,
cDefine36,
cDefine37,
iCheckIds,
cBVencode,
chVencode,
bGsp,
cGspState,
iArrsId,
cCheckCode,
iCheckIdBaks,
cRejectCode,
iRejectIds,
cCheckPersonCode,
dCheckDate,
iOriTaxCost,
iOriCost,
iOriMoney,
iOriTaxPrice,
ioriSum,
iTaxRate,
iTaxPrice,
iSum,
bTaxCost,
cPOID,
cMassUnit,
iMaterialFee,
iProcessCost,
iProcessFee,
dMSDate,
iSMaterialFee,
iSProcessFee,
iOMoDID,
strContractId,
strCode,
bChecked,
bRelated,
iOMoMID,
iMatSettleState,
iBillSettleCount,
bLPUseFree,
iOriTrackID,
coritracktype,
cbaccounter,
dbKeepDate,
bCosting,
iSumBillQuantity,
bVMIUsed,
iVMISettleQuantity,
iVMISettleNum,
cvmivencode,
iInvSNCount,
cwhpersoncode,
cwhpersonname,
impcost,
iIMOSID,
iIMBSID,
cbarvcode,
dbarvdate,
iinvexchrate,
comcode,
strContractGUID,
iExpiratDateCalcu
from RdRecords01 r  where  exists(select ID from #t_01 t where t.ID=r.ID)
--释放临时表
drop table #t_01