Move Expired SOP Quotes to History

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

Technorati Tags: SOP,quote,stored procedure,history

2013 Payroll Tax Update–Round 2

It’s that time of year again! Time to change the payroll tax tables, and then change them again.

Round 1 of the 2013 payroll tax tables adjusted the employee FICA percentage back to 6.2% but did not include changes to the withholding tables themselves. Terry Heley at Microsoft sent out an email yesterday evening letting us all know that they are still on track to release Round 2 of 2013 tax update today or tomorrow.

Another issue coming up is that the employer and employee FICA amounts are not coming out the same even though they are the same percentage. For example, I put in a gross pay of $1,000 and the system calculated the employer FICA at $62.00 and the employee FICA at $62.01. This is frustrating, but you are not doing anything wrong.

Terry tells us there is ‘rounding’ code written in the employee side, but not the employer. This issue will be addressed in a Hotfix due out at the end of the week ending 01/21/2013.

Round 2 of the 2013 tax tables will also be issued for version 10 customers, but that will be the last one. The January Hotfix will not be issued to version 10 customers.

Hope you are all having a fun year-end close!

Until next post

Leslie

Illegal address for field 'ICR State Employer Account Number'

 

Here’s another one for the ‘when things go wrong’ collection. You open the company setup window and you get the above message. The entire error message reads like this:

Unhandled script exception:

Illegal address for field 'ICR State Employer Account Number' in script 'ICR_SY_Company_Options_PRE'. Script terminated.

EXCEPTION_CLASS_SCRIPT_ADDRESSING

SCRIPT_CMD_LOAD_ATSI5

After you close the window,  you’ll get a trigger registration error. The culprit is the California DE542 product by Blue Moon Industries. It is easy to fix. All you need to do is go into the Alternate/Modified Forms and Reports window and then mark to use the California DE542 version of the Company Setup window. The screenshot below shows you want it looks like.

Until next post!

Leslie

Published at last! Developing Microsoft Dynamics GP Business Applications

 

If anyone has noticed my absence on the newsgroup, this is why. This book was published on Christmas Eve! – too late for Santa, but just in time for Valentine’s Day!

You can try out seven different development tools in this book. There’s a small project using each of the following:

• Dexterity
• VS Tools
• Modifier
• VBA
• SmartList Builder
• Extender
• Drill-down builder

If you’ve ever wanted to dabble, now’s your chance to work a little bit with each tool. There’s no other publication like it that includes a little about so many different tools; I hope you enjoy it.

I want to thank my technical reviewers, Mohammad R. Daoud, Jivtesh Singh, Vaidhyanathan Mohan, and Frank Hamelly, for their valuable time, ideas, and insights. This is a much better, more complete book because of them and their willingness to help and advise me. How could I miss with that kind of talent backing me up?

The book was only supposed to be 300 pages, but 300 pages just wasn’t enough. Even at 600 pages, there’s so much more I wanted to add. Please let me know if you have any suggestions on how I can make the book better next time.

I look forward to getting back with the Community!

Kind regards,

Leslie

Connection was refused by the server