Round 2 2013 Payroll Tax Table update Released!

It’s time to update your payroll tax tables.

The Round 2 2013 tax tables have just been released! The update is available for versions 10 (last one for this version), GP 2010 and GP 2013.

Enjoy!

Leslie

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