ALTER PROCEDURE CCCMoveSalesBufferDataToArchive
AS
BEGIN
DECLARE @findocs2Process TABLE (findoc INT);
DECLARE @finJustDel TABLE (findoc INT);
DECLARE @mdocJustDel TABLE (findoc INT);
DECLARE @mtlJustDel TABLE (findoc INT);
DECLARE @frowsToMove TABLE (findoc INT);
DECLARE @finJustDelCount INT;
DECLARE @mdocJustDelCount INT;
DECLARE @mtlJustDelCount INT;
DECLARE @finp INT;
DECLARE @tmp_fin INT;
print 'Deleting payment findocs';
DELETE FROM findoc WHERE trndate < dateadd(DAY, -3, GETDATE()) AND SOSOURCE = 1381;
-- Update MTRL table
print 'DELETING ROWS ON ArchiveSoft1DB.dbo.MTRL';
DELETE ArchiveSoft1DB.dbo.MTRL;
print 'INSERTING NEW ROWS ON ArchiveSoft1DB.dbo.MTRL';
SET identity_insert ArchiveSoft1DB.dbo.[MTRL] ON
INSERT INTO ArchiveSoft1DB.[dbo].[MTRL]( [COMPANY],[SODTYPE],[LOCKID],[MTRL],[CODE],[NAME],[CODE1],[CODE2],[RELITEM],[NAME1],[ISACTIVE],[MTRTYPE],[MTRTYPE1],[MTRACN],[MTRCATEGORY],[VAT],[MTRUNIT1],[MTRUNIT2],[MTRUNIT3],[MTRUNIT4],[MU21],[MU31],[MU41],[MU12MODE],[MU13MODE],[MU14MODE],[MTRGROUP],[MTRMANFCTR],[COUNTRY],[BUSUNITS],[COSTCNTR],[MTRDUTY],[MTRPCATEGORY],[MTRMODEL],[MTRMARK],[CHK],[COMMISION],[MTRSEASON],[SOCURRENCY],[WARNING],[KEPYO],[MTRDUN],[INTRASTAT],[MUMD],[VOLUME],[WEIGHT],[WEBPAGE],[MTRPLACE],[PRICEW],[PRICER],[MARKUPW],[MARKUPR],[REMAINMODE],[ACNMSK],[ACNMSK1],[ACNMSK2],[ACNMSK3],[ACNMSK4],[ACNMSK5],[PRICEW01],[PRICEW02],[PRICEW03],[PRICEW04],[PRICEW05],[PRICEW06],[PRICEW07],[PRICEW08],[PRICEW09],[PRICEW10],[PRICEW11],[PRICEW12],[PRICER01],[PRICER02],[PRICER03],[PRICER04],[PRICER05],[PRICER06],[PRICER07],[PRICER08],[PRICER09],[PRICER10],[PRICER11],[PRICER12],[DIM1],[DIM2],[DIM3],[DIMMD],[DIMMTRUNIT],[SALQTY],[PURQTY],[ITEQTY],[FROMVAL],[SODISCOUNT],[MAXPRCDISC],[REPLPUR],[REPLSAL],[REPLITE],[SPCS],[AUTOUPDPUR],[AUTOUPDSAL],[AUTOUPDITE],[PRINTPURMD],[PRINTSALMD],[PRINTITEMD],[UNIQSUB],[LOTCODEMASK],[MTRLOTUSE],[MTRSNUSE],[SNCODEMSK],[ISTOTSRVCARD],[MTRTHIRD],[USESTBIN],[MTRONORDER],[TURNOVR],[SETITEM],[REPLPRICE],[REPLEXP],[MTRRPLCODE],[MTRCUS],[MTRSUP],[MTRSUPBRANCH],[REMAINLIMMIN],[REMAINLIMMAX],[REORDERLEVEL],[EXPN1],[EXPN2],[EXPN3],[EXPVAL1],[EXPVAL2],[EXPVAL3],[LISOURCETYPE],[SOCLMNS],[PRDLEVEL],[REMARKS],[CDIMCATEG1],[CDIM1],[CDIMCATEG2],[CDIM2],[CDIMCATEG3],[CDIM3],[CDIMNUSE1],[CDIMNUSE2],[CDIMNUSE3],[MTRDEFWHS],[VISITUNIT],[VISITNUM],[VATMTRL],[ACNMSKS],[ACNMSKX],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[SXMSKK],[CDIMLEVEL],[ACNMSKSXD],[CRDCARDMODE],[MTRGASTYPE],[EXPN4],[EXPN5],[EXPVAL4],[EXPVAL5],[CHKMAXPRCDISC],[SOCLMNS1],[MTRPRJCBLD],[SOEXPTYPE],[ACNMSK6],[MINPRCMK],[MTRPACK],[CCCTerraRelItem],[calconcredit],[WEBNAME],[WEBVIEW],[CCCTerraOFFERSTARTDATE],[CCCTerraOFFERENDDATE],[CCCTerraOFFERPRICE],[ACNMSK9],[GUARTIME],[GSISVALUE],[CCCITEMDEPARTMENT],[CCCITEMWEIGHTED],[CCCITEMEXLUDEDISC],[CCCXATOPOS],[CCCPLU],[CCCPARELK],[CCCQPARELK],[CCCMMP],[CCCPMM],[CCCPMP],[CCCMKOD],[CCCMTRGRSUP],[CCCMTRSUP],[APVCODE],[SODISCOUNT1],[SODISCOUNT2],[SPCITESRV],[SOPAYVALUE],[MTRLA],[MTRLP])
SELECT [COMPANY],[SODTYPE],[LOCKID],[MTRL],[CODE],[NAME],[CODE1],[CODE2],[RELITEM],[NAME1],[ISACTIVE],[MTRTYPE],[MTRTYPE1],[MTRACN],[MTRCATEGORY],[VAT],[MTRUNIT1],[MTRUNIT2],[MTRUNIT3],[MTRUNIT4],[MU21],[MU31],[MU41],[MU12MODE],[MU13MODE],[MU14MODE],[MTRGROUP],[MTRMANFCTR],[COUNTRY],[BUSUNITS],[COSTCNTR],[MTRDUTY],[MTRPCATEGORY],[MTRMODEL],[MTRMARK],[CHK],[COMMISION],[MTRSEASON],[SOCURRENCY],[WARNING],[KEPYO],[MTRDUN],[INTRASTAT],[MUMD],[VOLUME],[WEIGHT],[WEBPAGE],[MTRPLACE],[PRICEW],[PRICER],[MARKUPW],[MARKUPR],[REMAINMODE],[ACNMSK],[ACNMSK1],[ACNMSK2],[ACNMSK3],[ACNMSK4],[ACNMSK5],[PRICEW01],[PRICEW02],[PRICEW03],[PRICEW04],[PRICEW05],[PRICEW06],[PRICEW07],[PRICEW08],[PRICEW09],[PRICEW10],[PRICEW11],[PRICEW12],[PRICER01],[PRICER02],[PRICER03],[PRICER04],[PRICER05],[PRICER06],[PRICER07],[PRICER08],[PRICER09],[PRICER10],[PRICER11],[PRICER12],[DIM1],[DIM2],[DIM3],[DIMMD],[DIMMTRUNIT],[SALQTY],[PURQTY],[ITEQTY],[FROMVAL],[SODISCOUNT],[MAXPRCDISC],[REPLPUR],[REPLSAL],[REPLITE],[SPCS],[AUTOUPDPUR],[AUTOUPDSAL],[AUTOUPDITE],[PRINTPURMD],[PRINTSALMD],[PRINTITEMD],[UNIQSUB],[LOTCODEMASK],[MTRLOTUSE],[MTRSNUSE],[SNCODEMSK],[ISTOTSRVCARD],[MTRTHIRD],[USESTBIN],[MTRONORDER],[TURNOVR],[SETITEM],[REPLPRICE],[REPLEXP],[MTRRPLCODE],[MTRCUS],[MTRSUP],[MTRSUPBRANCH],[REMAINLIMMIN],[REMAINLIMMAX],[REORDERLEVEL],[EXPN1],[EXPN2],[EXPN3],[EXPVAL1],[EXPVAL2],[EXPVAL3],[LISOURCETYPE],[SOCLMNS],[PRDLEVEL],[REMARKS],[CDIMCATEG1],[CDIM1],[CDIMCATEG2],[CDIM2],[CDIMCATEG3],[CDIM3],[CDIMNUSE1],[CDIMNUSE2],[CDIMNUSE3],[MTRDEFWHS],[VISITUNIT],[VISITNUM],[VATMTRL],[ACNMSKS],[ACNMSKX],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[SXMSKK],[CDIMLEVEL],[ACNMSKSXD],[CRDCARDMODE],[MTRGASTYPE],[EXPN4],[EXPN5],[EXPVAL4],[EXPVAL5],[CHKMAXPRCDISC],[SOCLMNS1],[MTRPRJCBLD],[SOEXPTYPE],[ACNMSK6],[MINPRCMK],[MTRPACK],[CCCTerraRelItem],[calconcredit],[WEBNAME],[WEBVIEW],[CCCTerraOFFERSTARTDATE],[CCCTerraOFFERENDDATE],[CCCTerraOFFERPRICE],[ACNMSK9],[GUARTIME],[GSISVALUE],[CCCITEMDEPARTMENT],[CCCITEMWEIGHTED],[CCCITEMEXLUDEDISC],[CCCXATOPOS],[CCCPLU],[CCCPARELK],[CCCQPARELK],[CCCMMP],[CCCPMM],[CCCPMP],[CCCMKOD],[CCCMTRGRSUP],[CCCMTRSUP],[APVCODE],[SODISCOUNT1],[SODISCOUNT2],[SPCITESRV],[SOPAYVALUE],[MTRLA],[MTRLP]
FROM [dbo].[MTRL]
WHERE sodtype = 51;
SET identity_insert ArchiveSoft1DB.dbo.[MTRL] off
-- Update TRDR table
print 'DELETING ROWS ON ArchiveSoft1DB.dbo.TRDR';
DELETE ArchiveSoft1DB.dbo.TRDR;
print 'INSERTING NEW ROWS ON ArchiveSoft1DB.dbo.TRDR';
SET identity_insert ArchiveSoft1DB.dbo.[TRDR] ON
INSERT INTO ArchiveSoft1DB.[dbo].[TRDR]( [COMPANY],[LOCKID],[SODTYPE],[TRDR],[CODE],[NAME],[AFM],[ISACTIVE],[ISPROSP],[COUNTRY],[SOCURRENCY],[BRANCH],[ADDRESS],[ZIP],[DISTRICT],[CITY],[AREAS],[PHONE01],[PHONE02],[FAX],[JOBTYPE],[JOBTYPETRD],[TRDGROUP],[WEBPAGE],[EMAIL],[TRDCATEGORY],[TRDBUSINESS],[SHIPMENT],[PAYMENT],[PRCCATEGORY],[BUSUNITS],[PRIORITY],[PRCPOLICY],[DSCPOLICY],[SOCARRIER],[TRUCKS],[ROUTING],[SALESMAN],[COLLECTOR],[CODE1],[IRSDATA],[VATSTS],[KEPYOSTS],[CMPMODE],[ACNMSK],[OPITMODE],[CRCONTROL],[CRDLINES1],[CRDLIMIT1],[CRDLINES2],[CRDLIMIT2],[DISCOUNT],[PAYHFROM],[PAYHTO],[PAYEVERY],[PAYDAY],[PAYWEEK],[WARNING],[EXPGROUP],[BANK],[BANKBRANCH],[BANKACCNUMBER],[BANKRATE1],[BANKRATE2],[ACNMSK1],[BANCTYPE],[MINBAL],[MAXBAL],[IBAN],[SPCCHAR],[BGBULSTAT],[REMARKS],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[CUSDATE],[CUSUSER],[ISCONTRACTOR],[SPCOST],[EFKFLAG],[DISTRICT1],[TRDTYPE1],[CONWHOUSE],[OLDEH],[OLAFM],[COMMISION],[CRDLINES3],[CRDLIMIT3],[CRDLINES4],[CRDLIMIT4],[CRDLINES5],[CRDLIMIT5],[MAXPRCDISC],[CHKMAXPRCDISC],[GASCUSTYPE],[kepyomd],[ISCMPT],[codelinemd],[ISVALCREDIT],[rowguid],[opitfindoc],[SOSCORE],[PRJCS],[SWIFT],[VISITEVERY],[CHKAFM],[EMAILACC],[RECEIPTCARD],[ECOLLABORATION],[VATPROVISIONS],[WASTETYPE],[S1INVMD],[GSISMD],[SOPAYCODE],[NUMCG],[INPAYVAT])
SELECT [COMPANY],[LOCKID],[SODTYPE],[TRDR],[CODE],[NAME],[AFM],[ISACTIVE],[ISPROSP],[COUNTRY],[SOCURRENCY],[BRANCH],[ADDRESS],[ZIP],[DISTRICT],[CITY],[AREAS],[PHONE01],[PHONE02],[FAX],[JOBTYPE],[JOBTYPETRD],[TRDGROUP],[WEBPAGE],[EMAIL],[TRDCATEGORY],[TRDBUSINESS],[SHIPMENT],[PAYMENT],[PRCCATEGORY],[BUSUNITS],[PRIORITY],[PRCPOLICY],[DSCPOLICY],[SOCARRIER],[TRUCKS],[ROUTING],[SALESMAN],[COLLECTOR],[CODE1],[IRSDATA],[VATSTS],[KEPYOSTS],[CMPMODE],[ACNMSK],[OPITMODE],[CRCONTROL],[CRDLINES1],[CRDLIMIT1],[CRDLINES2],[CRDLIMIT2],[DISCOUNT],[PAYHFROM],[PAYHTO],[PAYEVERY],[PAYDAY],[PAYWEEK],[WARNING],[EXPGROUP],[BANK],[BANKBRANCH],[BANKACCNUMBER],[BANKRATE1],[BANKRATE2],[ACNMSK1],[BANCTYPE],[MINBAL],[MAXBAL],[IBAN],[SPCCHAR],[BGBULSTAT],[REMARKS],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[CUSDATE],[CUSUSER],[ISCONTRACTOR],[SPCOST],[EFKFLAG],[DISTRICT1],[TRDTYPE1],[CONWHOUSE],[OLDEH],[OLAFM],[COMMISION],[CRDLINES3],[CRDLIMIT3],[CRDLINES4],[CRDLIMIT4],[CRDLINES5],[CRDLIMIT5],[MAXPRCDISC],[CHKMAXPRCDISC],[GASCUSTYPE],[kepyomd],[ISCMPT],[codelinemd],[ISVALCREDIT],[rowguid],[opitfindoc],[SOSCORE],[PRJCS],[SWIFT],[VISITEVERY],[CHKAFM],[EMAILACC],[RECEIPTCARD],[ECOLLABORATION],[VATPROVISIONS],[WASTETYPE],[S1INVMD],[GSISMD],[SOPAYCODE],[NUMCG],[INPAYVAT]
FROM [dbo].[TRDR]
WHERE sodtype = 13
SET identity_insert ArchiveSoft1DB.dbo.[TRDR] off
-- Fetch findocs to process
INSERT INTO @findocs2Process
SELECT top 10000 findoc
FROM findoc
WHERE CCCTerraHasExported = 1 AND trndate < dateadd(week, -1, GETDATE())
ORDER BY TRNDATE;
-- Fetch findocs that are already in archive
INSERT INTO @finJustDel
SELECT finS.findoc
FROM @findocs2Process finS
INNER JOIN ArchiveSoft1DB.dbo.FINDOC finA ON finA.FINDOC = finS.FINDOC;
-- Fetch mtrdocs that are already in archive
INSERT INTO @mdocJustDel
SELECT finP.findoc
FROM @findocs2Process finP
INNER JOIN ArchiveSoft1DB.dbo.MTRDOC mdocA ON mdocA.FINDOC = finP.FINDOC;
-- Fetch mtrlines that are already in archive
INSERT INTO @mtlJustDel
SELECT finP.findoc
FROM @findocs2Process finP
INNER JOIN ArchiveSoft1DB.dbo.MTRLINES mtlA ON mtlA.FINDOC = finP.FINDOC;
-- Finally, fetch rows to move
INSERT INTO @frowsToMove
SELECT findoc
FROM @findocs2Process
WHERE findoc NOT IN (SELECT findoc FROM @finJustDel);
-- DELETE already stored rows to ArchiveSoft1DB from Salesbuffer
SET @finJustDelCount = (SELECT COUNT(*) FROM @finJustDel);
SET @mdocJustDelCount = (SELECT COUNT(*) FROM @mdocJustDel);
SET @mtlJustDelCount = (SELECT COUNT(*) FROM @mtlJustDel);
IF @mdocJustDelCount > 0
BEGIN
print 'DELETING EXISTINGS ROWS On SalesBufferDB for MTRDOC';
DELETE FROM MTRDOC WHERE FINDOC IN (SELECT FINDOC FROM @mdocJustDel);
END;
IF @mtlJustDelCount > 0
BEGIN
print 'DELETING EXISTINGS ROWS On SalesBufferDB for MTRLINES';
DELETE FROM MTRLINES WHERE FINDOC IN (SELECT FINDOC FROM @mtlJustDel);
END;
IF @finJustDelCount > 0
BEGIN
print 'DELETING EXISTINGS ROWS On SalesBufferDB for FINDOC';
DELETE FROM Findoc WHERE FINDOC IN (SELECT FINDOC FROM @finJustDel);
END;
-- Insert findoc
SET identity_insert ArchiveSoft1DB.dbo.Findoc ON
INSERT INTO ArchiveSoft1DB.dbo.[FINDOC]( [COMPANY],[LOCKID],[FINDOC],[SOSOURCE],[SOREDIR],[TRNDATE],[FISCPRD],[PERIOD],[SERIES],[SERIESNUM],[FPRMS],[TFPRMS],[FINCODE],[BRANCH],[SODTYPE],[TRDR],[TRDBRANCH],[VATSTS],[SOCURRENCY],[TRDRRATE],[LRATE],[FINDOCS],[MTRLINESS],[FINDOCSRV],[FINDOCPAY],[FINDOCSX],[ORIGIN],[GLUPD],[SXUPD],[ACNEDIT],[PRDCOST],[PROCDEF],[COMMENTS],[COMMENTS1],[ISCANCEL],[ISPRINT],[ISREADONLY],[APPRVDATE],[APPRVUSER],[APPRV],[CONVMODE],[FULLYTRANSF],[FINSTATES],[SHIPKIND],[SHIPMENT],[BUSUNITS],[PAYMENT],[CRCONTROL],[PRIORITY],[INST],[PRJC],[SALESMAN],[COLLECTOR],[RSRC],[SOCASH],[FOLDER],[DEPART],[ACNMSK],[TRDRS],[TRDBRANCHS],[LTYPE1],[LTYPE2],[LTYPE3],[LTYPE4],[SOTIME],[TURNOVR],[TTURNOVR],[LTURNOVR],[VATAMNT],[TVATAMNT],[LVATAMNT],[EXPN],[TEXPN],[LEXPN],[DISC1PRC],[DISC1VAL],[TDISC1VAL],[LDISC1VAL],[DISC2PRC],[DISC2VAL],[TDISC2VAL],[LDISC2VAL],[NETAMNT],[TNETAMNT],[LNETAMNT],[SUMAMNT],[SUMTAMNT],[SUMLAMNT],[FXDIFFVAL],[KEPYOQT],[LKEPYOVAL],[CHANGEVAL],[PRCRULEDATA],[INTDATE],[INTRATE],[INTVAL],[INTEXPN],[INTVAT],[INTFDOCTYPE],[INTSHIPMENT],[ISTRIG],[BGSUBJECT],[BGOTHRVAT],[BGPARTVAT],[REMARKS],[VARCHAR01],[VARCHAR02],[NUM01],[NUM02],[DATE01],[DATE02],[INT01],[INT02],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[INSDATEN],[PRCPOLICY],[bgdocdate],[KEPYOHANDMD],[GASCUSTYPE],[CASHDEVICE],[BGINTSTATPROC],[BGINTDELIVERY],[BGINTDISTRICT],[KEPYOMD],[CCCIMPPOINTS],[CCCCARD],[CCCTerraHasExported],[CCCTerraRId],[CCCTerraFindocExistanceCheck],[cntr],[SOCURRENCYREF],[NUM03],[NUM04],[BOOL01],[BOOL02],[UFTBL01],[UFTBL02],[prjcstage],[bgdocdate1],[socashcls],[FINDOCG],[EXDIFFPPERIOD],[PRJLINES],[VATPROVISIONS],[CARDPOINTS],[NEGCARDPOINTS],[BONUSCARD],[GSISMD],[GSISQTY],[GSISNET],[GSISVAT],[GSISPACKAGES],[GSISFLG],[WASTETYPE],[SOPAYCODE],[COSTCNTR],[INPAYVAT])
SELECT [COMPANY],[LOCKID],[FINDOC],[SOSOURCE],[SOREDIR],[TRNDATE],[FISCPRD],[PERIOD],[SERIES],[SERIESNUM],[FPRMS],[TFPRMS],[FINCODE],[BRANCH],[SODTYPE],[TRDR],[TRDBRANCH],[VATSTS],[SOCURRENCY],[TRDRRATE],[LRATE],[FINDOCS],[MTRLINESS],[FINDOCSRV],[FINDOCPAY],[FINDOCSX],[ORIGIN],[GLUPD],[SXUPD],[ACNEDIT],[PRDCOST],[PROCDEF],[COMMENTS],[COMMENTS1],[ISCANCEL],[ISPRINT],[ISREADONLY],[APPRVDATE],[APPRVUSER],[APPRV],[CONVMODE],[FULLYTRANSF],[FINSTATES],[SHIPKIND],[SHIPMENT],[BUSUNITS],[PAYMENT],[CRCONTROL],[PRIORITY],[INST],[PRJC],[SALESMAN],[COLLECTOR],[RSRC],[SOCASH],[FOLDER],[DEPART],[ACNMSK],[TRDRS],[TRDBRANCHS],[LTYPE1],[LTYPE2],[LTYPE3],[LTYPE4],[SOTIME],[TURNOVR],[TTURNOVR],[LTURNOVR],[VATAMNT],[TVATAMNT],[LVATAMNT],[EXPN],[TEXPN],[LEXPN],[DISC1PRC],[DISC1VAL],[TDISC1VAL],[LDISC1VAL],[DISC2PRC],[DISC2VAL],[TDISC2VAL],[LDISC2VAL],[NETAMNT],[TNETAMNT],[LNETAMNT],[SUMAMNT],[SUMTAMNT],[SUMLAMNT],[FXDIFFVAL],[KEPYOQT],[LKEPYOVAL],[CHANGEVAL],[PRCRULEDATA],[INTDATE],[INTRATE],[INTVAL],[INTEXPN],[INTVAT],[INTFDOCTYPE],[INTSHIPMENT],[ISTRIG],[BGSUBJECT],[BGOTHRVAT],[BGPARTVAT],[REMARKS],[VARCHAR01],[VARCHAR02],[NUM01],[NUM02],[DATE01],[DATE02],[INT01],[INT02],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[INSDATEN],[PRCPOLICY],[bgdocdate],[KEPYOHANDMD],[GASCUSTYPE],[CASHDEVICE],[BGINTSTATPROC],[BGINTDELIVERY],[BGINTDISTRICT],[KEPYOMD],[CCCIMPPOINTS],[CCCCARD],[CCCTerraHasExported],[CCCTerraRId],[CCCTerraFindocExistanceCheck],[cntr],[SOCURRENCYREF],[NUM03],[NUM04],[BOOL01],[BOOL02],[UFTBL01],[UFTBL02],[prjcstage],[bgdocdate1],[socashcls],[FINDOCG],[EXDIFFPPERIOD],[PRJLINES],[VATPROVISIONS],[CARDPOINTS],[NEGCARDPOINTS],[BONUSCARD],[GSISMD],[GSISQTY],[GSISNET],[GSISVAT],[GSISPACKAGES],[GSISFLG],[WASTETYPE],[SOPAYCODE],[COSTCNTR],[INPAYVAT]
FROM [dbo].[FINDOC]
WHERE findoc IN (SELECT findoc FROM @frowsToMove);
SET identity_insert ArchiveSoft1DB.dbo.Findoc off
-- Insert MTRDOC
INSERT INTO ArchiveSoft1DB.dbo.[MTRDOC] ( [COMPANY],[FINDOC],[BRANCHSEC],[WHOUSESEC],[SOCARRIER],[TRUCKS],[ROUTING],[SHIPPINGADDR],[SHPZIP],[SHPDISTRICT],[SHPCITY],[SHIPDATE],[DELIVDATE],[ORDERTRDR],[ORDERTRDBRANCH],[SHIPTRDR],[SHIPTRDBRANCH],[BILLTRDR],[BILLTRDBRANCH],[MTRL],[SNCODE],[SPCS],[QTY],[QTY1],[QTY2],[QTY1S],[QTY1A],[WASTE],[COSTCOEF],[STAGE],[MTRSTS],[SALESCVAL],[QTY1H],[QTY2H],[PROCDOCS],[SOSIGNB],[BGINTCOUNTRY],[TRUCKSNO],[OLDEH],[OLKIND],[WHOUSE],[procdocs1],[SOSIGNE],[RECEIPTCARD],[FROMDATE],[FINALDATE],[DURATION],[TRDPRSN],[ACTSTATUS],[S1INV],[SORELDOC])
SELECT [COMPANY],[FINDOC],[BRANCHSEC],[WHOUSESEC],[SOCARRIER],[TRUCKS],[ROUTING],[SHIPPINGADDR],[SHPZIP],[SHPDISTRICT],[SHPCITY],[SHIPDATE],[DELIVDATE],[ORDERTRDR],[ORDERTRDBRANCH],[SHIPTRDR],[SHIPTRDBRANCH],[BILLTRDR],[BILLTRDBRANCH],[MTRL],[SNCODE],[SPCS],[QTY],[QTY1],[QTY2],[QTY1S],[QTY1A],[WASTE],[COSTCOEF],[STAGE],[MTRSTS],[SALESCVAL],[QTY1H],[QTY2H],[PROCDOCS],[SOSIGNB],[BGINTCOUNTRY],[TRUCKSNO],[OLDEH],[OLKIND],[WHOUSE],[procdocs1],[SOSIGNE],[RECEIPTCARD],[FROMDATE],[FINALDATE],[DURATION],[TRDPRSN],[ACTSTATUS],[S1INV],[SORELDOC]
FROM [dbo].[MTRDOC]
WHERE findoc IN (SELECT findoc FROM @frowsToMove);
-- Insert Mtrlines
INSERT INTO ArchiveSoft1DB.dbo.[MTRLINES]( [COMPANY],[FINDOC],[MTRLINES],[LINENUM],[SODTYPE],[MTRL],[PENDING],[RESTMODE],[SOSOURCE],[SOREDIR],[SCODE],[MTRTYPE],[SOTYPE],[WHOUSE],[WHOUSESEC],[MTRUNIT],[VAT],[PRJC],[SALESMAN],[RSRC],[MTRLOT],[ASSDEPR],[WHOUSEBIN],[WHOUSEBINSEC],[BUSUNITS],[MTRSEASON],[MTRCATEGORY],[SPCS],[QTY],[QTY1],[QTY2],[QTY1COV],[QTY1CANC],[QTY1FCOV],[SHIPDATE],[DELIVDATE],[WEIGHT],[VOLUME],[INTVAL],[INTVAT],[PLSMVAL],[PLSMVAT],[PRICE],[PRICE1],[LINEVAL],[LLINEVAL],[EXPVAL],[LEXPVAL],[DISC1PRC],[DISC2PRC],[DISC3PRC],[DISC1VAL],[LDISC1VAL],[DISC2VAL],[LDISC2VAL],[DISC3VAL],[LDISC3VAL],[NETLINEVAL],[LNETLINEVAL],[VATAMNT],[LVATAMNT],[SALESCVAL],[EFKVAL],[COMMENTS],[COMMENTS1],[DIMENSION1],[DIMENSION2],[DIMENSION3],[TRNLINEVAL],[LTRNLINEVAL],[PRCRULEDATA],[WASTE],[COSTCOEF],[CONVPRC],[STAGE],[STAGELINENUM],[ACNMSK],[ACNMSK1],[FINDOCS],[MTRLINESS],[SOANAL],[BGLEXCISE],[SXMSKK],[SXPERC],[prcpolicy],[autoprddoc],[BGINTCOUNTRY],[BGINTDISTRICT],[MTRPRJCBLD],[FINDOCL],[MTRLINESL],[ACNMSKS],[ACNMSKX],[SOEXPTYPE],[CCCScannedBarcode],[lexpval1],[lexpvatval1],[MTRPACK],[QTYP],[CCCTerraPRICE2],[CCCPOINTS],[CCCCARD1],[CCCCARD2],[INSTALMENTS],[DUTY],[COMMENTS2],[NUM01],[NUM02],[NUM03],[NUM04],[BOOL01],[BOOL02],[DATE01],[DATE02],[UFTBL01],[UFTBL02],[prjcstage],[costcntr],[ITECALCTYPE],[PRJLINES],[GUARTIME],[LVATNOEXM],[SRVDEBITTYPE],[PACKSTATUS],[PACKCODE1],[PACKCODE2])
SELECT [COMPANY],[FINDOC],[MTRLINES],[LINENUM],[SODTYPE],[MTRL],[PENDING],[RESTMODE],[SOSOURCE],[SOREDIR],[SCODE],[MTRTYPE],[SOTYPE],[WHOUSE],[WHOUSESEC],[MTRUNIT],[VAT],[PRJC],[SALESMAN],[RSRC],[MTRLOT],[ASSDEPR],[WHOUSEBIN],[WHOUSEBINSEC],[BUSUNITS],[MTRSEASON],[MTRCATEGORY],[SPCS],[QTY],[QTY1],[QTY2],[QTY1COV],[QTY1CANC],[QTY1FCOV],[SHIPDATE],[DELIVDATE],[WEIGHT],[VOLUME],[INTVAL],[INTVAT],[PLSMVAL],[PLSMVAT],[PRICE],[PRICE1],[LINEVAL],[LLINEVAL],[EXPVAL],[LEXPVAL],[DISC1PRC],[DISC2PRC],[DISC3PRC],[DISC1VAL],[LDISC1VAL],[DISC2VAL],[LDISC2VAL],[DISC3VAL],[LDISC3VAL],[NETLINEVAL],[LNETLINEVAL],[VATAMNT],[LVATAMNT],[SALESCVAL],[EFKVAL],[COMMENTS],[COMMENTS1],[DIMENSION1],[DIMENSION2],[DIMENSION3],[TRNLINEVAL],[LTRNLINEVAL],[PRCRULEDATA],[WASTE],[COSTCOEF],[CONVPRC],[STAGE],[STAGELINENUM],[ACNMSK],[ACNMSK1],[FINDOCS],[MTRLINESS],[SOANAL],[BGLEXCISE],[SXMSKK],[SXPERC],[prcpolicy],[autoprddoc],[BGINTCOUNTRY],[BGINTDISTRICT],[MTRPRJCBLD],[FINDOCL],[MTRLINESL],[ACNMSKS],[ACNMSKX],[SOEXPTYPE],[CCCScannedBarcode],[lexpval1],[lexpvatval1],[MTRPACK],[QTYP],[CCCTerraPRICE2],[CCCPOINTS],[CCCCARD1],[CCCCARD2],[INSTALMENTS],[DUTY],[COMMENTS2],[NUM01],[NUM02],[NUM03],[NUM04],[BOOL01],[BOOL02],[DATE01],[DATE02],[UFTBL01],[UFTBL02],[prjcstage],[costcntr],[ITECALCTYPE],[PRJLINES],[GUARTIME],[LVATNOEXM],[SRVDEBITTYPE],[PACKSTATUS],[PACKCODE1],[PACKCODE2]
FROM [dbo].[MTRLINES]
WHERE findoc IN (SELECT findoc FROM @frowsToMove);
-- delete rows from salesbuffer
DELETE FROM MTRDOC WHERE findoc IN (SELECT findoc FROM @frowsToMove);
DELETE FROM MTRLINES WHERE findoc IN (SELECT findoc FROM @frowsToMove);
DELETE FROM Findoc WHERE findoc IN (SELECT findoc FROM @frowsToMove);
-- Shring log file
DBCC SHRINKFILE (N'press_Log' , 1)
END;
Comments