API tools faq
paste
Advertisement
mnakos

Diellas Move older-done POS rows to Archive

mnakos
Sep 13th, 2015
286
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 17.69 KB | None | 0 0
raw download clone embed print report
  1. ALTER PROCEDURE CCCMoveSalesBufferDataToArchive
  2. AS
  3. BEGIN
  4.     DECLARE @findocs2Process TABLE (findoc INT);
  5.     DECLARE @finJustDel TABLE (findoc INT);
  6.     DECLARE @mdocJustDel TABLE (findoc INT);
  7.     DECLARE @mtlJustDel TABLE (findoc INT);
  8.     DECLARE @frowsToMove TABLE (findoc INT);
  9.     DECLARE @finJustDelCount INT;
  10.     DECLARE @mdocJustDelCount INT;
  11.     DECLARE @mtlJustDelCount INT;
  12.     DECLARE @finp INT;
  13.     DECLARE @tmp_fin INT;
  14.  
  15.     print 'Deleting payment findocs';
  16.     DELETE FROM findoc WHERE trndate < dateadd(DAY, -3, GETDATE()) AND SOSOURCE = 1381;
  17.  
  18.     -- Update MTRL table
  19.     print 'DELETING ROWS ON ArchiveSoft1DB.dbo.MTRL';
  20.     DELETE ArchiveSoft1DB.dbo.MTRL;
  21.    
  22.     print 'INSERTING NEW ROWS ON ArchiveSoft1DB.dbo.MTRL';
  23.     SET identity_insert ArchiveSoft1DB.dbo.[MTRL] ON
  24.     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])
  25.     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]
  26.     FROM [dbo].[MTRL]
  27.     WHERE sodtype = 51;
  28.     SET identity_insert ArchiveSoft1DB.dbo.[MTRL] off
  29.  
  30.     -- Update TRDR table
  31.     print 'DELETING ROWS ON ArchiveSoft1DB.dbo.TRDR';
  32.     DELETE ArchiveSoft1DB.dbo.TRDR;
  33.    
  34.     print 'INSERTING NEW ROWS ON ArchiveSoft1DB.dbo.TRDR';
  35.     SET identity_insert ArchiveSoft1DB.dbo.[TRDR] ON
  36.     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])
  37.     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]
  38.     FROM [dbo].[TRDR]
  39.     WHERE sodtype = 13
  40.     SET identity_insert ArchiveSoft1DB.dbo.[TRDR] off
  41.    
  42.  
  43.  
  44.     -- Fetch findocs to process
  45.     INSERT INTO @findocs2Process
  46.     SELECT top 10000 findoc
  47.     FROM findoc
  48.     WHERE CCCTerraHasExported = 1 AND trndate < dateadd(week, -1, GETDATE())
  49.     ORDER BY TRNDATE;
  50.  
  51.     -- Fetch findocs that are already in archive
  52.     INSERT INTO @finJustDel
  53.     SELECT finS.findoc
  54.     FROM @findocs2Process finS
  55.     INNER JOIN ArchiveSoft1DB.dbo.FINDOC finA ON finA.FINDOC = finS.FINDOC;
  56.  
  57.     -- Fetch mtrdocs that are already in archive
  58.     INSERT INTO @mdocJustDel
  59.     SELECT finP.findoc
  60.     FROM @findocs2Process finP
  61.     INNER JOIN ArchiveSoft1DB.dbo.MTRDOC mdocA ON mdocA.FINDOC = finP.FINDOC;
  62.  
  63.     -- Fetch mtrlines that are already in archive
  64.     INSERT INTO @mtlJustDel
  65.     SELECT finP.findoc
  66.     FROM @findocs2Process finP
  67.     INNER JOIN ArchiveSoft1DB.dbo.MTRLINES mtlA ON mtlA.FINDOC = finP.FINDOC;
  68.  
  69.     -- Finally, fetch rows to move
  70.     INSERT INTO @frowsToMove
  71.     SELECT findoc
  72.     FROM @findocs2Process
  73.     WHERE findoc NOT IN (SELECT findoc FROM @finJustDel);
  74.  
  75.     -- DELETE already stored rows to ArchiveSoft1DB from Salesbuffer
  76.     SET @finJustDelCount = (SELECT COUNT(*) FROM @finJustDel);
  77.     SET @mdocJustDelCount = (SELECT COUNT(*) FROM @mdocJustDel);
  78.     SET @mtlJustDelCount = (SELECT COUNT(*) FROM @mtlJustDel);
  79.  
  80.     IF @mdocJustDelCount > 0
  81.     BEGIN
  82.         print 'DELETING EXISTINGS ROWS On SalesBufferDB for MTRDOC';
  83.         DELETE FROM MTRDOC WHERE FINDOC IN (SELECT FINDOC FROM @mdocJustDel);
  84.     END;
  85.  
  86.     IF @mtlJustDelCount > 0
  87.     BEGIN
  88.         print 'DELETING EXISTINGS ROWS On SalesBufferDB for MTRLINES';
  89.         DELETE FROM MTRLINES WHERE FINDOC IN (SELECT FINDOC FROM @mtlJustDel);
  90.     END;
  91.  
  92.     IF @finJustDelCount > 0
  93.     BEGIN
  94.         print 'DELETING EXISTINGS ROWS On SalesBufferDB for FINDOC';
  95.         DELETE FROM Findoc WHERE FINDOC IN (SELECT FINDOC FROM @finJustDel);
  96.     END;
  97.  
  98.     -- Insert findoc
  99.     SET identity_insert ArchiveSoft1DB.dbo.Findoc ON
  100.     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])
  101.     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]
  102.     FROM [dbo].[FINDOC]
  103.     WHERE findoc IN (SELECT findoc FROM @frowsToMove);
  104.     SET identity_insert ArchiveSoft1DB.dbo.Findoc off
  105.  
  106.     -- Insert MTRDOC
  107.     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])
  108.     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]
  109.     FROM [dbo].[MTRDOC]
  110.     WHERE findoc IN (SELECT findoc FROM @frowsToMove);
  111.  
  112.     -- Insert Mtrlines
  113.     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])
  114.     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]
  115.     FROM [dbo].[MTRLINES]
  116.     WHERE findoc IN (SELECT findoc FROM @frowsToMove);
  117.  
  118.     -- delete rows from salesbuffer
  119.     DELETE FROM MTRDOC WHERE findoc IN (SELECT findoc FROM @frowsToMove);
  120.     DELETE FROM MTRLINES WHERE findoc IN (SELECT findoc FROM @frowsToMove);
  121.     DELETE FROM Findoc WHERE findoc IN (SELECT findoc FROM @frowsToMove);
  122.  
  123.     -- Shring log file
  124.     DBCC SHRINKFILE (N'press_Log' , 1)
  125. END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!