Expired quote documents do not move to the SOP history table on their own. If you want to move them to the SOP history table they need a little help. For my clients that want to keep their quotes, but do not want them to stay in the Work table, I create a stored procedure that runs periodically to clean up SOP Work file.
I use this SQL statement:
--copy line items from expired quotes to history
INSERT INTO SOP30300
(SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC, NONINVEN, DROPSHIP, UOFM, LOCNCODE, UNITCOST, ORUNTCST, UNITPRCE, ORUNTPRC, XTNDPRCE, OXTNDPRC, REMPRICE, OREPRICE, EXTDCOST, OREXTCST, MRKDNAMT, ORMRKDAM, MRKDNPCT, MRKDNTYP, INVINDX, CSLSINDX, SLSINDX, MKDNINDX, RTNSINDX, INUSINDX, INSRINDX, DMGDINDX, ITMTSHID, IVITMTXB, BKTSLSAM, ORBKTSLS, TAXAMNT, ORTAXAMT, TXBTXAMT, OTAXTAMT, BSIVCTTL, TRDISAMT, ORTDISAM, DISCSALE, ORDAVSLS, QUANTITY, ATYALLOC, QTYINSVC, QTYINUSE, QTYDMGED, QTYRTRND, QTYONHND, QTYCANCE, QTYCANOT, QTYORDER, QTYPRBAC, QTYPRBOO, QTYPRINV, QTYPRORD, QTYPRVRECVD, QTYRECVD, QTYREMAI, QTYREMBO, QTYTBAOR, QTYTOINV, QTYTORDR, QTYFULFI, QTYSLCTD, QTYBSUOM, EXTQTYAL, EXTQTYSEL, ReqShipDate, FUFILDAT, ACTLSHIP, SHIPMTHD, SALSTERR, SLPRSNID, PRCLEVEL, COMMNTID, BRKFLD1, BRKFLD2, BRKFLD3, CURRNIDX, TRXSORCE, SOPLNERR, ORGSEQNM, ITEMCODE, PURCHSTAT, DECPLQTY, DECPLCUR, ODECPLCU, EXCEPTIONALDEMAND, TAXSCHID, TXSCHSRC, PRSTADCD, ShipToName, CNTCPRSN, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIPCODE, CCode, COUNTRY, PHONE1, PHONE2, PHONE3, FAXNUMBR, Flags, CONTNBR, CONTLNSEQNBR, CONTSTARTDTE, CONTENDDTE, CONTITEMNBR, CONTSERIALNBR, ISLINEINTRA)
SELECT
SOP10200.SOPTYPE, SOP10200.SOPNUMBE, SOP10200.LNITMSEQ, SOP10200.CMPNTSEQ, SOP10200.ITEMNMBR, SOP10200.ITEMDESC, SOP10200.NONINVEN, SOP10200.DROPSHIP, SOP10200.UOFM, SOP10200.LOCNCODE, SOP10200.UNITCOST, SOP10200.ORUNTCST, SOP10200.UNITPRCE, SOP10200.ORUNTPRC, SOP10200.XTNDPRCE, SOP10200.OXTNDPRC, SOP10200.REMPRICE, SOP10200.OREPRICE, SOP10200.EXTDCOST, SOP10200.OREXTCST, SOP10200.MRKDNAMT, SOP10200.ORMRKDAM, SOP10200.MRKDNPCT, SOP10200.MRKDNTYP, SOP10200.INVINDX, SOP10200.CSLSINDX, SOP10200.SLSINDX, SOP10200.MKDNINDX, SOP10200.RTNSINDX, SOP10200.INUSINDX, SOP10200.INSRINDX, SOP10200.DMGDINDX, SOP10200.ITMTSHID, SOP10200.IVITMTXB, SOP10200.BKTSLSAM, SOP10200.ORBKTSLS, SOP10200.TAXAMNT, SOP10200.ORTAXAMT, SOP10200.TXBTXAMT, SOP10200.OTAXTAMT, SOP10200.BSIVCTTL, SOP10200.TRDISAMT, SOP10200.ORTDISAM, SOP10200.DISCSALE, SOP10200.ORDAVSLS, SOP10200.QUANTITY, SOP10200.ATYALLOC, SOP10200.QTYINSVC, SOP10200.QTYINUSE, SOP10200.QTYDMGED, SOP10200.QTYRTRND, SOP10200.QTYONHND, SOP10200.QTYCANCE, SOP10200.QTYCANOT, SOP10200.QTYORDER, SOP10200.QTYPRBAC, SOP10200.QTYPRBOO, SOP10200.QTYPRINV, SOP10200.QTYPRORD, SOP10200.QTYPRVRECVD, SOP10200.QTYRECVD, SOP10200.QTYREMAI, SOP10200.QTYREMBO, SOP10200.QTYTBAOR, SOP10200.QTYTOINV, SOP10200.QTYTORDR, SOP10200.QTYFULFI, SOP10200.QTYSLCTD, SOP10200.QTYBSUOM, SOP10200.EXTQTYAL, SOP10200.EXTQTYSEL, SOP10200.ReqShipDate, SOP10200.FUFILDAT, SOP10200.ACTLSHIP, SOP10200.SHIPMTHD, SOP10200.SALSTERR, SOP10200.SLPRSNID, SOP10200.PRCLEVEL, SOP10200.COMMNTID, SOP10200.BRKFLD1, SOP10200.BRKFLD2, SOP10200.BRKFLD3, SOP10200.CURRNIDX, SOP10200.TRXSORCE, SOP10200.SOPLNERR, SOP10200.ORGSEQNM, SOP10200.ITEMCODE, SOP10200.PURCHSTAT, SOP10200.DECPLQTY, SOP10200.DECPLCUR, SOP10200.ODECPLCU, SOP10200.EXCEPTIONALDEMAND, SOP10200.TAXSCHID, SOP10200.TXSCHSRC, SOP10200.PRSTADCD, SOP10200.ShipToName, SOP10200.CNTCPRSN, SOP10200.ADDRESS1, SOP10200.ADDRESS2, SOP10200.ADDRESS3, SOP10200.CITY, SOP10200.STATE, SOP10200.ZIPCODE, SOP10200.CCode, SOP10200.COUNTRY, SOP10200.PHONE1, SOP10200.PHONE2, SOP10200.PHONE3, SOP10200.FAXNUMBR, SOP10200.Flags, SOP10200.CONTNBR, SOP10200.CONTLNSEQNBR, SOP10200.CONTSTARTDTE, SOP10200.CONTENDDTE, SOP10200.CONTITEMNBR, SOP10200.CONTSERIALNBR, SOP10200.ISLINEINTRA
FROM SOP10100 LEFT OUTER JOIN
SOP10200 ON SOP10100.SOPTYPE = SOP10200.SOPTYPE AND SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
WHERE SOP10200.SOPTYPE = 1 AND (SOP10100.QUOEXPDA < GETDATE())
--HEADER
--copy header records from expired quotes to history
insert into SOP30200
(SOPTYPE, SOPNUMBE, ORIGTYPE, ORIGNUMB, DOCID, DOCDATE, GLPOSTDT, QUOTEDAT, QUOEXPDA, ORDRDATE, INVODATE, BACKDATE, RETUDATE, ReqShipDate, FUFILDAT, ACTLSHIP, DISCDATE, DUEDATE, REPTING, TRXFREQU, TIMEREPD, TIMETREP, DYSTINCR, DTLSTREP, DSTBTCH1, DSTBTCH2, USDOCID1, USDOCID2, DISCFRGT, ORDAVFRT, DISCMISC, ORDAVMSC, DISAVAMT, ORDAVAMT, DISCRTND, ORDISRTD, DISTKNAM, ORDISTKN, DSCPCTAM, DSCDLRAM, ORDDLRAT, DISAVTKN, ORDATKN, PYMTRMID, PRCLEVEL, LOCNCODE, BCHSOURC, BACHNUMB, CUSTNMBR, CUSTNAME, CSTPONBR, PROSPECT, MSTRNUMB, PCKSLPNO, PICTICNU, MRKDNAMT, ORMRKDAM, PRBTADCD, PRSTADCD, CNTCPRSN, ShipToName, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIPCODE, CCode, COUNTRY, PHNUMBR1, PHNUMBR2, PHONE3, FAXNUMBR, COMAPPTO, COMMAMNT, OCOMMAMT, CMMSLAMT, ORCOSAMT, NCOMAMNT, ORNCMAMT, SHIPMTHD, TRDISAMT, ORTDISAM, TRDISPCT, SUBTOTAL, ORSUBTOT, REMSUBTO, OREMSUBT, EXTDCOST, OREXTCST, FRTAMNT, ORFRTAMT, MISCAMNT, ORMISCAMT, TXENGCLD, TAXEXMT1, TAXEXMT2, TXRGNNUM, TAXSCHID, TXSCHSRC, BSIVCTTL, FRTSCHID, FRTTXAMT, ORFRTTAX, FRGTTXBL, MSCSCHID, MSCTXAMT, ORMSCTAX, MISCTXBL, BKTFRTAM, ORBKTFRT, BKTMSCAM, ORBKTMSC, BCKTXAMT, OBTAXAMT, TXBTXAMT, OTAXTAMT, TAXAMNT, ORTAXAMT, ECTRX, DOCAMNT, ORDOCAMT, PYMTRCVD, ORPMTRVD, DEPRECVD, ORDEPRVD, CODAMNT, ORCODAMT, ACCTAMNT, ORACTAMT, SALSTERR, SLPRSNID, UPSZONE, TIMESPRT, PSTGSTUS, VOIDSTTS, ALLOCABY, NOTEINDX, CURNCYID, CURRNIDX, RATETPID, EXGTBLID, XCHGRATE, DENXRATE, EXCHDATE, TIME1, RTCLCMTD, MCTRXSTT, TRXSORCE, SOPHDRE1, SOPHDRE2, SOPLNERR, SOPHDRFL, COMMNTID, REFRENCE, POSTEDDT, PTDUSRID, USER2ENT, CREATDDT, MODIFDT, Tax_Date, APLYWITH, WITHHAMT, SHPPGDOC, CORRCTN, SIMPLIFD, DOCNCORR, SEQNCORR, SALEDATE, EXCEPTIONALDEMAND, Flags, SOPSTATUS, SHIPCOMPLETE, DIRECTDEBIT, WorkflowApprStatCreditLm, WorkflowPriorityCreditLm, WorkflowApprStatusQuote, WorkflowPriorityQuote, ContractExchangeRateStat)
SELECT SOP10100.SOPTYPE, SOP10100.SOPNUMBE, SOP10100.ORIGTYPE, SOP10100.ORIGNUMB, SOP10100.DOCID, SOP10100.DOCDATE, SOP10100.GLPOSTDT, SOP10100.QUOTEDAT, SOP10100.QUOEXPDA, SOP10100.ORDRDATE, SOP10100.INVODATE, SOP10100.BACKDATE, SOP10100.RETUDATE, SOP10100.ReqShipDate, SOP10100.FUFILDAT, SOP10100.ACTLSHIP, SOP10100.DISCDATE, SOP10100.DUEDATE, SOP10100.REPTING, SOP10100.TRXFREQU, SOP10100.TIMEREPD, SOP10100.TIMETREP, SOP10100.DYSTINCR, SOP10100.DTLSTREP, SOP10100.DSTBTCH1, SOP10100.DSTBTCH2, SOP10100.USDOCID1, SOP10100.USDOCID2, SOP10100.DISCFRGT, SOP10100.ORDAVFRT, SOP10100.DISCMISC, SOP10100.ORDAVMSC, SOP10100.DISAVAMT, SOP10100.ORDAVAMT, SOP10100.DISCRTND, SOP10100.ORDISRTD, SOP10100.DISTKNAM, SOP10100.ORDISTKN, SOP10100.DSCPCTAM, SOP10100.DSCDLRAM, SOP10100.ORDDLRAT, SOP10100.DISAVTKN, SOP10100.ORDATKN, SOP10100.PYMTRMID, SOP10100.PRCLEVEL, SOP10100.LOCNCODE, SOP10100.BCHSOURC, SOP10100.BACHNUMB, SOP10100.CUSTNMBR, SOP10100.CUSTNAME, SOP10100.CSTPONBR, SOP10100.PROSPECT, SOP10100.MSTRNUMB, SOP10100.PCKSLPNO, SOP10100.PICTICNU, SOP10100.MRKDNAMT, SOP10100.ORMRKDAM, SOP10100.PRBTADCD, SOP10100.PRSTADCD, SOP10100.CNTCPRSN, SOP10100.ShipToName, SOP10100.ADDRESS1, SOP10100.ADDRESS2, SOP10100.ADDRESS3, SOP10100.CITY, SOP10100.STATE, SOP10100.ZIPCODE, SOP10100.CCode, SOP10100.COUNTRY, SOP10100.PHNUMBR1, SOP10100.PHNUMBR2, SOP10100.PHONE3, SOP10100.FAXNUMBR, SOP10100.COMAPPTO, SOP10100.COMMAMNT, SOP10100.OCOMMAMT, SOP10100.CMMSLAMT, SOP10100.ORCOSAMT, SOP10100.NCOMAMNT, SOP10100.ORNCMAMT, SOP10100.SHIPMTHD, SOP10100.TRDISAMT, SOP10100.ORTDISAM, SOP10100.TRDISPCT, SOP10100.SUBTOTAL, SOP10100.ORSUBTOT, SOP10100.REMSUBTO, SOP10100.OREMSUBT, SOP10100.EXTDCOST, SOP10100.OREXTCST, SOP10100.FRTAMNT, SOP10100.ORFRTAMT, SOP10100.MISCAMNT, SOP10100.ORMISCAMT, SOP10100.TXENGCLD, SOP10100.TAXEXMT1, SOP10100.TAXEXMT2, SOP10100.TXRGNNUM, SOP10100.TAXSCHID, SOP10100.TXSCHSRC, SOP10100.BSIVCTTL, SOP10100.FRTSCHID, SOP10100.FRTTXAMT, SOP10100.ORFRTTAX, SOP10100.FRGTTXBL, SOP10100.MSCSCHID, SOP10100.MSCTXAMT, SOP10100.ORMSCTAX, SOP10100.MISCTXBL, SOP10100.BKTFRTAM, SOP10100.ORBKTFRT, SOP10100.BKTMSCAM, SOP10100.ORBKTMSC, SOP10100.BCKTXAMT, SOP10100.OBTAXAMT, SOP10100.TXBTXAMT, SOP10100.OTAXTAMT, SOP10100.TAXAMNT, SOP10100.ORTAXAMT, SOP10100.ECTRX, SOP10100.DOCAMNT, SOP10100.ORDOCAMT, SOP10100.PYMTRCVD, SOP10100.ORPMTRVD, SOP10100.DEPRECVD, SOP10100.ORDEPRVD, SOP10100.CODAMNT, SOP10100.ORCODAMT, SOP10100.ACCTAMNT, SOP10100.ORACTAMT, SOP10100.SALSTERR, SOP10100.SLPRSNID, SOP10100.UPSZONE, SOP10100.TIMESPRT, SOP10100.PSTGSTUS, SOP10100.VOIDSTTS, SOP10100.ALLOCABY, SOP10100.NOTEINDX, SOP10100.CURNCYID, SOP10100.CURRNIDX, SOP10100.RATETPID, SOP10100.EXGTBLID, SOP10100.XCHGRATE, SOP10100.DENXRATE, SOP10100.EXCHDATE, SOP10100.TIME1, SOP10100.RTCLCMTD, SOP10100.MCTRXSTT, SOP10100.TRXSORCE, SOP10100.SOPHDRE1, SOP10100.SOPHDRE2, SOP10100.SOPLNERR, SOP10100.SOPHDRFL, SOP10100.COMMNTID, SOP10100.REFRENCE, SOP10100.POSTEDDT, SOP10100.PTDUSRID, SOP10100.USER2ENT, SOP10100.CREATDDT, SOP10100.MODIFDT, SOP10100.Tax_Date, SOP10100.APLYWITH, SOP10100.WITHHAMT, SOP10100.SHPPGDOC, SOP10100.CORRCTN, SOP10100.SIMPLIFD, SOP10100.DOCNCORR, SOP10100.SEQNCORR, SOP10100.SALEDATE, SOP10100.EXCEPTIONALDEMAND, SOP10100.Flags, SOP10100.SOPSTATUS, SOP10100.SHIPCOMPLETE, SOP10100.DIRECTDEBIT, SOP10100.WorkflowApprStatCreditLm, SOP10100.WorkflowPriorityCreditLm, SOP10100.WorkflowApprStatusQuote, SOP10100.WorkflowPriorityQuote, SOP10100.ContractExchangeRateStat
FROM SOP10100
WHERE SOPTYPE = 1 AND (SOP10100.QUOEXPDA < GETDATE())
--LINE
--delete line items of expired quotes from WORK
DELETE SOP10200
FROM SOP10100 LEFT OUTER JOIN
SOP10200 ON SOP10100.SOPTYPE = SOP10200.SOPTYPE AND SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
WHERE SOP10200.SOPTYPE = 1 AND (SOP10100.QUOEXPDA < GETDATE())
--HEADER
--delete header records of expired quotes from WORK
DELETE SOP10100
WHERE SOPTYPE = 1 AND (SOP10100.QUOEXPDA < GETDATE())
I feel certain there is a more elegant solution out there, but this one has been working for a long time.
Let me know what you think and help me approve this process.
Until next post.
Leslie
That's precisely what I am working on at the moment and this script has saved me almost 1/2 day. Mine is a bit more than a usual scenario though. I got to close down quotes that are lying for the past 3 years. So just the date criteria change and I am good to go. Thanks for the script and post.
ReplyDeleteLeslie,
ReplyDeleteAwesome script! I can see this being very useful for many companies. One thing to maybe add is a change in the VOIDSTTS - so that these show in history as voided. :-)
-Victoria
Thanks Vaidy,
ReplyDeleteI value your opinion so much, this is a big compliment for me.
Yay!!
Leslie
Victoria,
ReplyDeleteThat's a great idea. I'll change it this weekend!
Thank you
Leslie