--将其他出库单传递到中间库 if exists (select * from sysobjects where id = object_id('dbo.usp_transrdrecord09') ) drop procedure dbo.usp_transrdrecord09 go Create PROCEDURE usp_transrdrecord09 @v_start datetime, --开始时间 @v_end datetime --结束时间 as --cDefine5=1 代表是从旺店通传递回来的数据，不需要回传 --其他出库音id select Id into #t_1 from RdRecord09 where dnmaketime>=@v_start and dnmaketime <= @v_end and ISNULL(cDefine5,0) = 0 and (cDefine1 <> '来源旺店通' or cDefine1 is null) --材料出库单id select Id into #t_01 from rdrecord11 r where (dnmaketime>=@v_start and dnmaketime <= @v_end and ISNULL(cDefine5,0) = 0) --if @@ROWCOUNT = 0 return --删除其他出库中间库已存在的资料 delete from wtu..rdrecord09 where exists (select id from #t_1 where ID=wtu..rdrecord09.ID) delete from wtu..rdrecords09 where exists (select id from #t_1 where ID=wtu..rdrecords09.ID) --删除材料出库中间库已经存在的资料 delete from wtu..rdrecord09 where exists (select id from #t_01 where ID=wtu..rdrecord09.ID ) and ( cBusType<>'其他出库' or cBusType<>'组装出库') delete from wtu..rdrecords09 where exists (select id from #t_01 where ID=wtu..rdrecords09.ID ) and text_flag=1 --其他出库主表数据 insert into wtu..RdRecord09( 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, cShipAddress, caddcode, bOMFirst, bFromPreYear, bIsLsQuery, bIsComplement, iDiscountTaxType, iBG_OverFlag, cBG_Auditor, cBG_AuditTime, ControlResult, ireturncount, iverifystate, iswfcontrolled, cModifyPerson, dModifyDate, dnmaketime, dnmodifytime, dnverifytime, bredvouch, iFlowId, cPZID, cSourceLs, cSourceCodeLs, iPrintCount, ctransflag, csysbarcode, cCurrentAuditor, cCheckSignFlag ) 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, cShipAddress, caddcode, bOMFirst, bFromPreYear, bIsLsQuery, bIsComplement, iDiscountTaxType, iBG_OverFlag, cBG_Auditor, cBG_AuditTime, ControlResult, ireturncount, iverifystate, iswfcontrolled, cModifyPerson, dModifyDate, dnmaketime, dnmodifytime, dnverifytime, bredvouch, iFlowId, cPZID, cSourceLs, cSourceCodeLs, iPrintCount, ctransflag, csysbarcode, cCurrentAuditor, cCheckSignFlag from RdRecord09 r where exists(select ID from #t_1 t where t.ID=r.ID) --其他出库子表数据 insert into wtu..rdrecords09 ( AutoID, ID, cInvCode, iNum, iQuantity, iUnitCost, iPrice, iAPrice, iPUnitCost, iPPrice, cBatch, cVouchCode, cInVouchCode, cinvouchtype, iSOutQuantity, iSOutNum, coutvouchid, coutvouchtype, iSRedOutQuantity, iSRedOutNum, cFree1, cFree2, iFlag, iFNum, iFQuantity, dVDate, iTrIds, cPosition, cDefine22, cDefine23, cDefine24, cDefine25, cDefine26, cDefine27, cItem_class, cItemCode, iDLsID, 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, bGsp, cGspState, cCheckCode, iCheckIdBaks, cRejectCode, iRejectIds, cCheckPersonCode, dCheckDate, cMassUnit, bChecked, iEqDID, bLPUseFree, iRSRowNO, iOriTrackID, coritracktype, cbaccounter, dbKeepDate, bCosting, bVMIUsed, iVMISettleQuantity, iVMISettleNum, cvmivencode, iInvSNCount, cwhpersoncode, cwhpersonname, cserviceoid, cbserviceoid, iinvexchrate, cbdlcode ) select AutoID, r.ID, cInvCode, iNum, iQuantity, iUnitCost, iPrice, iAPrice, iPUnitCost, iPPrice, cBatch, cVouchCode, cInVouchCode, cinvouchtype, iSOutQuantity, iSOutNum, coutvouchid, coutvouchtype, iSRedOutQuantity, iSRedOutNum, cFree1, cFree2, iFlag, iFNum, iFQuantity, dVDate, iTrIds, cPosition, cDefine22, cDefine23, cDefine24, cDefine25, cDefine26, cDefine27, cItem_class, cItemCode, iDLsID, 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, bGsp, cGspState, cCheckCode, iCheckIdBaks, cRejectCode, iRejectIds, cCheckPersonCode, dCheckDate, cMassUnit, bChecked, iEqDID, bLPUseFree, iRSRowNO, iOriTrackID, coritracktype, cbaccounter, dbKeepDate, bCosting, bVMIUsed, iVMISettleQuantity, iVMISettleNum, cvmivencode, iInvSNCount, cwhpersoncode, cwhpersonname, cserviceoid, cbserviceoid, iinvexchrate, cbdlcode from rdrecords09 r where exists(select ID from #t_1 t where t.ID=r.ID) --材料出库主表数据 --set identity_insert wtu..rdrecord09 ON--打开 insert into wtu..rdrecord09(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,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,0 from rdrecord11 r where exists(select ID from #t_01 t where t.ID=r.ID) --set identity_insert wtu..rdrecord09 OFF--关闭 --材料出库子表数据 --set identity_insert wtu..rdrecords09 ON--打开 insert into wtu..rdrecords09(AUTOID,ID,CINVCODE,INUM,IQUANTITY,IUNITCOST,IPRICE,IAPRICE,IPUNITCOST,IPPRICE,CBATCH,CVOUCHCODE,CINVOUCHCODE,CINVOUCHTYPE,ISOUTQUANTITY,ISOUTNUM,COUTVOUCHID,COUTVOUCHTYPE,ISREDOUTQUANTITY,ISREDOUTNUM,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,text_flag) select AUTOID,r.ID,CINVCODE,INUM,IQUANTITY,IUNITCOST,IPRICE,IAPRICE,IPUNITCOST,IPPRICE,CBATCH,CVOUCHCODE,CINVOUCHCODE,CINVOUCHTYPE,ISOUTQUANTITY,ISOUTNUM,COUTVOUCHID,COUTVOUCHTYPE,ISREDOUTQUANTITY,ISREDOUTNUM,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,1 from rdrecords11 as r where exists(select ID from #t_01 t where t.ID=r.ID) --set identity_insert wtu..rdrecords09 OFF--关闭 --释放临时表 drop table #t_1 drop table #t_01 --