yilv_new/AA/sql/其他入库单到中间.sql

--将其他入库单传递到中间库
if exists (select * from sysobjects where id = object_id('dbo.usp_transrdrecord08') )
        drop procedure dbo.usp_transrdrecord08
go

Create  PROCEDURE usp_transrdrecord08

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

as
--cDefine5=1 代表是从旺店通传递回来的数据，不需要回传
--
select Id into #t_1 from RdRecord08 where dnmaketime>=@v_start and dnmaketime <= @v_end and ISNULL(cDefine5,0) = 0 and (cDefine1 <> '来源旺店通' or cDefine1 is null)
--if @@ROWCOUNT = 0 return
--采购订单id
select Id into #t_01 from RdRecord01 r 
where ((dnmaketime>=@v_start and dnmaketime <= @v_end  )) and (cDefine1 is null ) and (cBusType<>'普通采购')
--and ISNULL(cDefine5,0) = 0)
--	or (exists (select Id  from RdRecord01  where cBusType='委外加工' and ID=r.ID)    )
--删除其他入库中间库已存在的资料
delete from wtu..rdrecord08  where  exists (select id from #t_1 where ID=wtu..rdrecord08.ID)
delete from wtu..rdrecords08 where  exists (select id from #t_1 where ID=wtu..rdrecords08.ID)

--删除采购订单已存在的资料
delete from wtu..RdRecord08  where exists (select id from #t_01 where ID=wtu..rdrecord08.ID ) and cBusType<>'其他入库' --防止id重复
delete from wtu..RdRecords08  where exists (select id from #t_01 where ID=wtu..rdrecords08.ID ) and text_flag=1



--其他入库中间主表
insert into wtu..rdrecord08(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, iExchRate, cExch_Name, bOMFirst, bFromPreYear, bIsLsQuery, bIsComplement, iDiscountTaxType, ireturncount, iverifystate, iswfcontrolled, cModifyPerson, dModifyDate, dnmaketime, dnmodifytime, dnverifytime, bredvouch, iFlowId, cPZID, cSourceLs, cSourceCodeLs, iPrintCount, ctransflag, csysbarcode, cCurrentAuditor)
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, iExchRate, cExch_Name, bOMFirst, bFromPreYear, bIsLsQuery, bIsComplement, iDiscountTaxType, ireturncount, iverifystate, iswfcontrolled, cModifyPerson, dModifyDate, dnmaketime, dnmodifytime, dnverifytime, bredvouch, iFlowId, cPZID, cSourceLs, cSourceCodeLs, iPrintCount, ctransflag, csysbarcode, cCurrentAuditor 
from RdRecord08 r where  exists(select ID from #t_1 t where t.ID=r.ID)
--其他入库中间子表
insert into wtu..rdrecords08 
	  (AutoID, ID, cInvCode, iNum, iQuantity, iUnitCost, iPrice, iAPrice, iPUnitCost, iPPrice, cBatch, cVouchCode, cInVouchCode, cinvouchtype, iSOutQuantity, iSOutNum, cFree1, cFree2, iFlag, iFNum, iFQuantity, dVDate, iTrIds, cPosition, cDefine22, cDefine23, cDefine24, cDefine25, cDefine26, cDefine27, cItem_class, cItemCode, 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, cCheckCode, iCheckIdBaks, cRejectCode, iRejectIds, cCheckPersonCode, dCheckDate, cMassUnit, bChecked, bLPUseFree, iRSRowNO, iOriTrackID, coritracktype, cbaccounter, dbKeepDate, bCosting, bVMIUsed, iVMISettleQuantity, iVMISettleNum, cvmivencode, iInvSNCount, cwhpersoncode, cwhpersonname, cserviceoid, cbserviceoid, iinvexchrate)
select AutoID, r.ID, cInvCode, iNum, iQuantity, iUnitCost, iPrice, iAPrice, iPUnitCost, iPPrice, cBatch, cVouchCode, cInVouchCode, cinvouchtype, iSOutQuantity, iSOutNum, cFree1, cFree2, iFlag, iFNum, iFQuantity, dVDate, iTrIds, cPosition, cDefine22, cDefine23, cDefine24, cDefine25, cDefine26, cDefine27, cItem_class, cItemCode, 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, cCheckCode, iCheckIdBaks, cRejectCode, iRejectIds, cCheckPersonCode, dCheckDate, cMassUnit, bChecked, bLPUseFree, iRSRowNO, iOriTrackID, coritracktype, cbaccounter, dbKeepDate, bCosting, bVMIUsed, iVMISettleQuantity, iVMISettleNum, cvmivencode, iInvSNCount, cwhpersoncode, cwhpersonname, cserviceoid, cbserviceoid, iinvexchrate
from rdrecords08  r  where  exists(select ID from #t_1 t where t.ID=r.ID) 
--采购入库主表数据插入其他入库中间表
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 from RdRecord01 r ,#t_01 T where r.id=t.id

insert into wtu..RdRecord08(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)
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 from RdRecord01 r ,#t_01 T where r.id=t.id
--where  exists(select ID from #t_01 t where t.ID=r.ID) 
--采购出库子表数据插入其他出库中间表

select AUTOID,r.ID,CINVCODE,INUM,IQUANTITY,IUNITCOST,IPRICE,IAPRICE,IPUNITCOST,IPPRICE,CBATCH,CVOUCHCODE,CINVOUCHCODE,CINVOUCHTYPE,ISOUTQUANTITY,ISOUTNUM,CFREE1,CFREE2,IFLAG,IFNUM,IFQUANTITY,DVDATE,CPOSITION,CDEFINE22,CDEFINE23,CDEFINE24,CDEFINE25,CDEFINE26,CDEFINE27,CITEM_CLASS,CITEMCODE,CNAME,CITEMCNAME,CFREE3,CFREE4,CFREE5,CFREE6,CFREE7,CFREE8,CFREE9,CFREE10,CBARCODE,INQUANTITY,1    from RdRecords01 r ,#t_01 T where r.id=t.id

insert into wtu..RdRecords08(AUTOID,ID,CINVCODE,INUM,IQUANTITY,IUNITCOST,IPRICE,IAPRICE,IPUNITCOST,IPPRICE,CBATCH,CVOUCHCODE,CINVOUCHCODE,CINVOUCHTYPE,ISOUTQUANTITY,ISOUTNUM,CFREE1,CFREE2,IFLAG,IFNUM,IFQUANTITY,DVDATE,CPOSITION,CDEFINE22,CDEFINE23,CDEFINE24,CDEFINE25,CDEFINE26,CDEFINE27,CITEM_CLASS,CITEMCODE,CNAME,CITEMCNAME,CFREE3,CFREE4,CFREE5,CFREE6,CFREE7,CFREE8,CFREE9,CFREE10,CBARCODE,INQUANTITY,text_flag)
select AUTOID,r.ID,CINVCODE,INUM,IQUANTITY,IUNITCOST,IPRICE,IAPRICE,IPUNITCOST,IPPRICE,CBATCH,CVOUCHCODE,CINVOUCHCODE,CINVOUCHTYPE,ISOUTQUANTITY,ISOUTNUM,CFREE1,CFREE2,IFLAG,IFNUM,IFQUANTITY,DVDATE,CPOSITION,CDEFINE22,CDEFINE23,CDEFINE24,CDEFINE25,CDEFINE26,CDEFINE27,CITEM_CLASS,CITEMCODE,CNAME,CITEMCNAME,CFREE3,CFREE4,CFREE5,CFREE6,CFREE7,CFREE8,CFREE9,CFREE10,CBARCODE,INQUANTITY,1    from RdRecords01 r ,#t_01 T where r.id=t.id
--where  exists(select ID from #t_01 t where t.ID=r.ID) 

drop table #t_1
drop table #t_01