Wednesday, January 22, 2014

W-2 Other Codes for Box 12

 

calculator

With the W-2 season upon us, I thought now would be a good time to point out some items that should live in Box 12; but often don’t make it there. I’m not attempting to give any tax or accounting advice, just to share some information with you about Box 12.

Box 12 looks simple enough, but oh what a secret she keeps:
2013 form W-2

Recently, I posted an article about common errors regarding retirement account information. In this post, I’ll review the items that should be reported in box 12. In the 30 years I’ve been working with Forms W-2 in various capacities, I have never seen an employer follow all of the items the IRS says should be included in box 12. 

The table below comes from the W-2 instructions on the form itself:

Box 12 Code

Description

A

Uncollected Social Security or RRTA tax on tips

B

Uncollected Medicare tax on tips

C

Taxable cost of group-term life insurance over $50,000

D

Elective deferrals to a section 401(k) cash or deferred arrangement. Also includes deferrals under a SIMPLE retirement account that is part of a section 401(k) arrangement

E

Elective deferrals under section 403(b) salary reduction agreement with your employer

F

Elective deferrals under a section 408(k)(6) salary reduction SEP

G

Elective deferrals and employer contributions (including non-elective deferrals) to a section 457(b) deferred compensation plan

H

Elective deferrals to a section 501c(18)(D) tax-exempt organization plan

J

Nontaxable sick pay (information only, not included in boxes 1, 3, or 5)

K

Twenty percent (20%) excise tax on excess golden parachute payments

L

Substantiated employee business expense reimbursements (nontaxable)

M

Uncollected social security or RRTA tax on taxable cost of group-term life insurance over $50,000 (former employees only)

N

Uncollected Medicare tax on taxable cost of group-term life insurance over $50,000 (former employees only)

P

Excludable moving expense reimbursements paid directly to employee (not included in boxes 1, 3, or 5)

Q

Nontaxable combat pay

R

Employer contributions to your Archer MSA.

S

Employee salary reduction contributions under a section 408(p) SIMPLE plan (not included in box 1)

T

Adoption benefits (not included in box 1)

V

Income from exercise of non-statutory stock option(s) (included in boxes 1, 3 (up to social security wage base), and 5)

W

Employer contributions (including amounts the employee elected to contribute using a section 125 (cafeteria) plan) to your health savings account

Y

Deferrals under a section 409A nonqualified deferred compensation plan

Z

Income under section 409A on a nonqualified deferred compensation plan. This amount is also included in box 1. It is subject to an additional 20% tax plus interest.

AA

Designated Roth contributions under section 401(k) plan

BB

Designated Roth contributions under section 403(b) plan

DD

Cost of employer-sponsored health coverage (this is not taxable when Code DD)

EE

Designated Roth contributions under a governmental section 457(b) plan. This amount does not apply to contributions under a tax-exempt organization section 457(b) plan.

I have highlighted three of the items that I often see overlooked by employers.

Code C – taxable cost of group-term life insurance over $50,000

If an employer provides life insurance for their employees, and the death benefit is more than $50,000, an amount should be included in wages based an an IRS formula.

In most cases, the cost of up to $50,000 of group-term life insurance coverage provided by an employer is not included in income. However, the cost of employer-provided coverage over $50,000 should be included in the taxable income of the employee.

This taxable amount is subject to withholding for FICA and Medicare, but not Federal Income tax.

For example, let’s say a 50 year-old employee was provided a $200,000 life insurance policy. The first $50,000 of coverage is not taxable, however, the remaining $150,000 is taxed based on the IRS’s calculation. The IRS numbers are in the following table:

Age

Taxable Cost per $1,000, per month

Under 25

0.05

25 - 29

0.06

30 - 34

0.08

35 - 39

0.09

40 - 44

0.10

45 - 49

0.15

50 - 54

0.23

55 - 59

0.43

60 - 64

0.66

65 - 69

1.27

70 and older

2.06

Using the above table, this employee should have an additional $414.00 included in box 1 and box 12 with code C. He had an extra $150,000 of insurance for 12 months. The calculation would be 150 x 12 x .23 = 414.00.

Code L – substantiated employee business expense reimbursements

This one is real shocker to most people. Any time your employee submits an expense report that is supported with receipts, or conforms to the governmental per diem or mileage rates, the amount that you reimburse him or her should be included in box 12 of Form W-2 with code ‘L’. The amount is not taxable, and therefore not included in Box 1; but it should be reported in box 12. As I understand it, the theory behind this is that it gives the IRS a heads up should the employee try to deduct the expenses on Form 2106 (Employee Business Expense) on his personal 1040.

Code P – excludable moving expense reimbursements

The amount under code P is the amount you reimbursed your employee for ‘qualified’ moving expenses that were not includable in their taxable income. The amount in this box is not included in boxes 1, 3 or 5. If the employees ‘qualified’ moving expenses exceeded the code P amount, they may be able to deduct the extra expenses on Form 3903 filed with their Form 1040. The code P amount does not need to be included anywhere on the Form 1040 if Form 3903 is not filed with the return.

As in all IRS rules, there are pages of additional information regarding what should be reported where. The above is certainly not an authoritative document. If you think any of this might apply to you, contact your accounting/tax advisor for additional information.

Good luck with tax season.

Live the dream!

Leslie

Friday, January 17, 2014

Dynamics GP Excel Dashboard available now!

 

This comes to us from one of Dynamics GP’s finest, the infamous Mark Polino! Jump to  www.DynamicAccounting.net/Analytics to get more details.

This is the truly painless way to get started with Excel dashboards, have someone else do it for you Smile. Imagine, an Excel dashboard with all of the bells and whistles ready to go. We’ve all seen and heard how cool dashboards are, and wish we had the time to figure out how to create them. Well, the time has been reduced to just a few minutes. Seriously, the dashboard is easy to install; and, honestly, I do mean easy.

Simplicity is a word that appeals to me, and Mark’s Excel dashboard falls into that category. One of the things I like to point out during my development/tool classes is that you should ALWAYS look around to see if anyone else has already created the thing you are wanting to create. So many times, the answer is yes.  This is one of those yes times.

If you want to get started, but can’t carve out the hours it takes to develop this anew, this is for you.

Though the dashboard is perfectly fine just like it is, you have the option to change it however you want. We all know that changing something is far easier than creating it from scratch.

Can you say plug and play?  I am among the laziest people out there. I know the macro language so well because it saves me time and clicks.  If I can save clicks, I’m all for it! This is my kind of dashboard; just connect it to your data source and you’re ready to go. And fear not, if data sources are not your area of expertise, Mark makes that easy too.

So what’s available? Mark has released a dashboard for the Sales series. Here’s what it includes:

● Top Customers with corresponding costs               
● Top Items with corresponding costs               
● Top Salespeople, Territories and Sites               
● Sales over time with forward looking projection               
● Sales mix over time by item class               
● A full range of data filters to control information

Click the link and check it out.

How many of these do you think Mark can develop in 50 minutes?   

Until next post!

Leslie          

2014 Payroll Tax Tables–how to fill out manually

I’ve been getting quite a few calls lately about how to get the new 2014 tax tables if they can no longer get them from Microsoft. These folks are on versions 9 or 10, so they are out of luck on the download. Scandalous though it may be to remain on an unsupported version, they still have to update their tax tables. Fortunately, you can easily change the tax rates yourself using Dynamics GP.

After applying the current tax tables (as of 01-16-2014), this is what the windows look like:

FICAM 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button2014 FICAM no tear

 FICAS 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
image

EFICM

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
2014 EFICM no tear

EFICS 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] buttonimage

FED SINGLE 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status SINGLE
Push the [Tables] buttonPayroll Tax Tables FED SINGLE

FED MAR 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status MAR
Push the [Tables] buttonPayroll Tax Tables FED MAR

FED NRA 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NRA
Push the [Tables] buttonPayroll Tax Tables FED NRA

If you can’t read the screen shots, I also have the numbers for both the 2014 and 2013 in the tables below:

FICAM 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$200,000

$0.00

1.45%

$0.00

$200,000

$0.00

$0.00

2.35%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

FICAS 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$117,000

$0.00

6.20%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

EFICM 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$0.00

$0.00

1.45%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

EFICS 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$117,000

$0.00

6.20%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED SINGLE 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status SINGLE
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$2,250.00

$0.00

0.00%

$0.00

$2,250.00

$11,325.00

$0.00

10.00%

$2,250.00

$11,325.00

$39,150.00

$907.50

15.00%

$11,325.00

$39,150.00

$91,600.00

$5,081.25

25.00%

$39,150.00

$91,600.00

$188,600.00

$18,193.75

28.00%

$91,600.00

$188,600.00

$407,350.00

$45,353.75

33.00%

$188,600.00

$407,350.00

$409,000.00

$117,541.25

35.00%

$407,350.00

$409,000.00

$0.00

$118,118.75

39.60%

$409,000.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED MAR 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status MAR
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$8,450.00

$0.00

0.00%

$0.00

$8,450.00

$26,600.00

$0.00

10.00%

$8,450.00

$26,600.00

$82,250.00

$1,815.00

15.00%

$26,600.00

$82,250.00

$157,300.00

$10,162.50

25.00%

$82,250.00

$157,300.00

$188,600.00

$28,925.00

28.00%

$157,300.00

$235,300.00

$413,550.00

$50,765.00

33.00%

$235,300.00

$413,550.00

$466,050.00

$109,587.50

35.00%

$413,550.00

$466,050.00

$0.00

$127,962.50

39.60%

$466,050.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED NRA 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NRA
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$9,075.00

$0.00

10.00%

$0.00

$9,075.00

$36,900.00

$907.50

15.00%

$9075.00

$36,900.00

$89,350.00

$5,081.25

25.00%

$36,900.00

$89,350.00

$186,350.00

$18,193.75

28.00%

$89,350.00

$186,350.00

$405,100.00

$45,353.75

33.00%

$186,350.00

$405,100.00

$406,750.00

$117,541.25

35.00%

$405,100.00

$406,750.00

$0.00

$118,118.75

39.60%

$406,750.00

$0.00

$0.00

$0.00

0.00%

$0.00

Now for the 2013 Tax Tables:

FICAM 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$200,000

$0.00

1.45%

$0.00

$200,000

$0.00

$0.00

2.35%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

FICAS 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$113,700

$0.00

6.20%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

EFICM 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$0.00

$0.00

1.45%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

EFICS 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$113,700

$0.00

6.20%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED SINGLE 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status SINGLE
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$2,200.00

$0.00

0.00%

$0.00

$2,200.00

$11,125.00

$0.00

10.00%

$2,200.00

$11,125.00

$38,450.00

$892.50

15.00%

$11,125.00

$38,450.00

$90,050.00

$4,991.25

25.00%

$38,450.00

$90,050.00

$185,450.00

$17,891.25

28.00%

$90,050.00

$185,450.00

$400,550.00

$44,603.25

33.00%

$185,450.00

$400,550.00

$402,200.00

$115,586.25

35.00%

$400,550.00

$402,200.00

$0.00

$116,163.75

39.60%

$402,200.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED MAR 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status MAR
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$8,300.00

$0.00

0.00%

$0.00

$8,300.00

$26,150.00

$0.00

10.00%

$8,300.00

$26,150.00

$80,800.00

$1,785.00

15.00%

$26,150.00

$80,800.00

$154,700.00

$9,982.50

25.00%

$80,800.00

$154,700.00

$231,350.00

$28,457.50

28.00%

$154,700.00

$231,350.00

$406,650.00

$49,919.50

33.00%

$231,350.00

$406,650.00

$458,300.00

$107,768.50

35.00%

$406,650.00

$458,300.00

$0.00

$125,846.00

39.60%

$458,300.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED NRA 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NRA
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$8,925.00

$0.00

10.00%

$0.00

$8,925.00

$36,250.00

$892.50

15.00%

$9075.00

$36,250.00

$87,850.00

$4,991.25

25.00%

$36,900.00

$87,850.00

$183,250.00

$17,891.25

28.00%

$89,350.00

$183,250.00

$398,350.00

$44,603.25

33.00%

$186,350.00

$398,350.00

$400,000.00

$115,586.25

35.00%

$405,100.00

$400,000.00

$0.00

$116,163.75

39.60%

$406,750.00

$0.00

$0.00

$0.00

0.00%

$0.00

 

That should do it!

Until next post

Leslie

Sunday, January 12, 2014

There is an error with at least one vendor record, print the customer/vendor record for details

This is an error I thought I’d add to our ‘When Things Go Wrong’ section. My thanks goes to Alaa Ramadan, who both identified the error and discovered it’s solution.  You can find the thread here.

The error presented itself when the user was attempting to complete a refund check transaction in the RM module. (Sales | Transactions | Refund Checks).

As it turned out, the associated vendor record had been put on hold thus preventing the refund check creation.  Clearing the hold checkbox on the Vendor Maintenance window solved the problem.

Thanks Alaa!

Until next post,

Leslie

Wednesday, January 8, 2014

Form W-2 Common Errors - Codes for Retirement Plans

This information comes straight from our friends at the IRS, it describes common mistakes made on Forms W-2. I have run into many of these as our clients begin to prepare their annual filings.

If you find that you are missing any of these items, be sure to modify the payroll card rather than just editing the W-2. If you need to change the W-2 (and W-3), you can re-create the year-end wage file.

If you have already loaded the 2014 tax tables, change the FICA table to reflect the 2013 amounts before re-creating the files. Change them back when you are done. Listed below are the rates for 2013 and 2014
Code Year Rate Limit
EFICM 2013 1.45% no limit
EFICS 2013 6.2% $113,700
FICAM 2013 1.45% up to $200,000
FICAM 2013 2.35% over $200,000
FICAS 2013 6.2% $113,700
       
EFICM 2014 1.45% no limit
EFICS 2014 6.2% $117,000
FICAM 2014 1.45% up to $200,000
FICAM 2014 2.35% over $200,000
FICAS 2014 6.2% $117,000

Depending on how many you have to change, you could also edit the Year-end wage files.  Those files are:

Physical Name Technical Name
UPR10100

UPR_Year_End_WORK_HDR

UPR10101

UPR_Year_End_WORK_Wage

UPR10103

UPR_Year_End_WORK_Pension

UPR10104

UPR_Year_End_WORK_Special

UPR10105

UPR_Year_End_WORK_State

UPR10106

UPR_Year_End_WORK_Local

UPR10107

UPR_Year_End_WORK_Other

Here are the IRS rules:

You must ensure that the information on Form W-2, Wage and Tax Statement, and Form W-3, Transmittal of Wage & Tax Statements, you issue to your employees contains correct retirement plan information because:

  • employees need accurate information to determine the correct deductions and credits on their tax return, and
  • IRS agents use the information from these forms to determine whether employers are complying with income and employment tax reporting requirements.

Common mistakes
During Form 5500 examinations and EPCU projects, IRS agents found employers using incorrect codes in Box 12 of Form W-2, for example:

  • code D for 401(k) elective deferrals incorrectly included 403(b), 457, or non-qualified amounts.
  • code E for 403(b) contributions but did not have a 403(b) plan.
  • code H to incorrectly report health benefits; code H is for elective deferrals to a 501(c)(18)(D) tax-exempt organization plan. (In fact, a recent Employee Plan Compliance Unit project found that only 6 % of employers who used this code actually contributed to a 501(c)(18) plan.)
  • code S for a SIMPLE 401(k); the correct code for a SIMPLE 401(k) is code D.

Common codes used for Box 12

Letter code:
Used for:
Description:

D
401(k) contributions
Elective deferrals to a 401(k) cash or deferred arrangement, including SIMPLE 401(k)s

E
403(b) contributions
Elective deferrals made under a 403(b) salary reduction agreement

F
408(k)(6) contributions
Elective deferrals made under a SARSEP

G
457(b) contributions
Elective and nonelective deferrals made to a 457(b) deferred compensation plan

H
501(c)(18)(D) contributions
Elective deferrals to a Section 501(c)(18)(D) tax-exempt organization plan (Included in the "Wages, Tips, Comp." amount in Box 1)

S
408(p) SIMPLE contributions
Deferrals made under a SIMPLE IRA plan

AA
Roth contributions
Designated Roth contributions under a 401(k) plan

BB
Roth contributions
Designated Roth contributions under a 403(b) plan

EE
Roth contributions
Designated Roth contributions under a governmental 457(b) plan (a tax-exempt organization’s 457(b) can’t have a designated Roth account)

See the Instructions for Forms W-2 and W-3 for a complete list of codes.

Form W-2, Box 13
The “Retirement plan” indicator in Box 13 shows whether an employee is an active participant in your company’s plan. If this box is checked, it lets the recipient know that depending on their filing status and modified adjusted gross income, they may not be entitled to a full deduction for their traditional IRA contributions. You should check the retirement plan box if an employee was an “active participant” for any part of the year in:

  • a qualified pension, profit-sharing, or stock-bonus plan under Internal Revenue Code Section 401(a) (including a 401(k) plan).
  • an annuity plan under IRC Section 403(a).
  • an annuity contract or custodial account under IRC Section 403(b).
  • a simplified employee pension (SEP) under IRC Section 408(k).
  • a SIMPLE retirement account under IRC Section 408(p).
  • a trust described in IRC Section 501(c)(18).
  • a plan for federal, state, or local government employees or by an agency or instrumentality thereof (other than a 457(b) plan).

Active participant
Generally, an employee is an active participant if covered by a:

  • defined contribution plan (for example, a 401(k) plan) for any tax year and is credited with any contributions or forfeitures, or
  • defined benefit plan for any tax year that the employee is eligible to participate.

Don’t check the retirement plan box if your company only has non-qualified or 457(b) plans.

Form W-3, Box b
Form W-3, Box b has checkboxes to specify the type of employer filing the form. You should check the appropriate box if you are a:

  • non-governmental tax-exempt 501(c) organization;
  • state or local government or instrumentality;
  • state or local government or instrumentality and have received a determination letter from the IRS indicating that you are also a 501(c)(3) tax-exempt organization; or
  • federal government entity or instrumentality.

Otherwise, you should check the “None apply” box. Only check one box.

Page Last Reviewed or Updated: 22-Nov-2013

Until next post!

Leslie Vail

Thursday, December 19, 2013

When things go wrong - 'Object reference not set to an instance of an object'

This must be one of my most hated error messages. Usually, I see it in Integration Manager, but this time it has shown up in Management Reporter.

Jake Friedl recently  posted a list of things to check when you receive this error. Thank you Jake! This is a great list:

  1. Check for missing ISO codes.
  2. Start GP and log into any company as 'sa'.
  3. Go to Microsoft Dynamics GP -> Tools -> Setup -> System -> Currency.
  4. You will need to select every single currency from the Currency ID lookup.
  5. Once selected, confirm a code is entered in the ISO Code field.
  6. Check for missing functional currencies.
  7. You will need to log into every single company in GP to check the following.
  8. Go to Microsoft Dynamics GP -> Tools -> Setup -> Financial -> Multicurrency.
  9. Confirm a currency is selected in the Functional Currency field.
  10. Check for a blank Budget ID.
  11. Start SQL Management Studio and log in as a SQL sysadmin.
  12. Run the following query against each GP company database: select * from gl00200
  13. If you find a blank value in the BUDGETID column, back up the GP database and clear the value with: delete gl00200 where BUDGETID= ''
  14. After going through the above, you will need to re-create the data mart integration if you are using the data mart provider:
  15. Close all instances of MR and back up the MR database.
  16. Start the Configuration Console and click on the integration under ERP Integrations.
  17. Click the Disable Integration button and wait a moment for that to take effect.
  18. Click the Remove link in the top-right.
  19. Stop the MR services at the top level of the Configuration Console and then delete the DDM database from SQL.
  20. In the Configuration Console, start the MR services and then click File -> Configure and deploy a new DDM integration.
  21. Enable the integration and wait for the initial load to complete.

Until next post!

Leslie

Thursday, October 3, 2013

Dexterity training in Dallas – Live or Live on-line

It must be in the air! Come to Dallas, or join us On-Line.
I’m doing another Dexterity Fundamentals class in Dallas, TX, December 9th-13th, 2013. This time we are offering it both on-site in Dallas and Live on-line!

At last, you can go to a fun-filled Dexterity training class without leaving the comfort of your own home or office!man with head set

For more information, or to sign up, call
ConexusSG at 469-828-3274 or email training@ConexusSG.com

What will you Learn?

The Dexterity Fundamentals class teaches you everything you need to know to get started developing integrating applications.  During this class, you will learn the Dexterity components and Dynamics GP programming standards. You will complete many hands-on projects including the following:

 

·         How to set up the development environment

·         Create a Maintenance and Lookup window adhering to the Dynamics GP user interface guidelines

·         Create an Item Entry and Item lookup window using techniques that can fast-track your development

·         Use integrated Debugging tools to resolve errors

·         Learn about multiuser processing and how optimistic locking works

·         Create an integrating application that interacts with existing Dynamics GP components

·         Create record notes, browse buttons, shrink/expand buttons, zooms and expansion buttons

·         Create menus to navigate to your application

·         Create and launch reports using Report Writer

·         Work with multiple tables, set ranges and create virtual keys

·         Add items ‘On the Fly’ and create ‘Find’ buttons

·         Call existing Dynamics GP functions

·         Modify a Dynamics GP window thereby creating an Alternate window

·         Use object triggers and techniques for cross-dictionary integration

·         Create SQL tables from Dexterity

·         Package your application and create a .cnk file

·         Learn how to update your application to a new release.

·         Other topics and procedures

How should You prepare?

Review the Quick Start manual that is included in the Dexterity documentation. You can access this manual from the Help menu of Dexterity: Help | Online Manuals | Quick Start

SNAGHTML454f195

Alternatively, after you install Dexterity, look for the QStart.pdf file in the following folder:

. . .\Microsoft Dexterity\Dex 12.0\Manuals

What do you need?

Each student must provide their own computer with the following software installed:

  • Dynamics GP 2013
  • Dexterity 2013
  • Dynamics GP 2013 SDK  - located on the GP 2013 DVD: \Tools\SDK\Dynamics GP\SDK.exe

Until next post!

Leslie


Tuesday, September 24, 2013

SQL datetime formatting function–convert to string

Illustration-Of-A-3d-Ivory-White-Man-Construction-Worker-Carrying-A-Wrench
I found this looking for something else on the Internet, but I thought it would be a great item for your toolbox. The function was originally written for 2005, but it has worked on everything I’ve tried.
Enjoy!
Many thanks to Anubhav Goyal , who provided this information to the SQL community on June 11, 2009  - You rock Anubhav! http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
/*-------------------------------------------------------------------------------------------*/
--SQL Server date formatting function --convert datetime to string
/*--------------------------------------------------------------------------------------------*/

--SQL datetime functions
--SQL Server date formats
--T-SQL convert dates
--Formatting dates sql server

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask
    IF (CHARINDEX ('YYYY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YYYY',
                         DATENAME(YY, @Datetime))
    IF (CHARINDEX ('YY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YY',
                         RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX ('Month',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Month',
                         DATENAME(MM, @Datetime))
    IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
       SET @StringDate = REPLACE(@StringDate, 'MON',
                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX ('Mon',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Mon',
                                     LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX ('MM',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'MM',
                  RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX ('M',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'M',
                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF (CHARINDEX ('DD',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'DD',
                         RIGHT('0'+DATENAME(DD, @Datetime),2))
    IF (CHARINDEX ('D',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'D',
                                     DATENAME(DD, @Datetime))  
RETURN @StringDate
END
GO

--Microsoft SQL Server date format function test
--MSSQL formatting dates

SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YYYY')           --01/03/2012
SELECT dbo.fnFormatDate (getdate(), 'DD/MM/YYYY')           --03/01/2012
SELECT dbo.fnFormatDate (getdate(), 'M/DD/YYYY')            --1/03/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YYYY')             --1/3/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YY')               --1/3/12
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YY')             --01/03/12
SELECT dbo.fnFormatDate (getdate(), 'MON DD, YYYY')         --JAN 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Mon DD, YYYY')         --Jan 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Month DD, YYYY')       --January 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'YYYY/MM/DD')           --2012/01/03
SELECT dbo.fnFormatDate (getdate(), 'YYYYMMDD')             --20120103
SELECT dbo.fnFormatDate (getdate(), 'YYYY-MM-DD')           --2012-01-03

--CURRENT_TIMESTAMP returns current system date and time in standard internal format
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,'YY.MM.DD')      --12.01.03
GO

Saturday, September 21, 2013

Linking a Sales Tax Detail to a Vendor

Follow this method and you can create PM Transactions to the taxing authorities to remit sales tax, no re-keying required.

Recently I responded to a post on the GP Community Forum from a user wanting to tie a vendor to the sales tax details so that he could automatically create a payables document similar to what is done when you pay something with a credit card.

I thought that was a really good idea and set out to find a way to do it. You need Integration Manager to accomplish it, but it’s easy to set up.

Prerequisites:

Tax Details

You must assign a general ledger account to each Tax Detail you use to calculate tax. The GL account must be unique per tax authority payee.  For example, if I pay the city of Dallas for one tax detail, and pay the county tax assessor for another, you must have two different accounts. This is needed because you are going to assign a vendor to the GL account assigned to the Tax Detail. Each account will represent a single tax collector.

You do not need a unique account for each Tax Detail, just a unique account for each vendor.

Account Maintenance

For each liability account to a Tax Detail, record the Vendor ID in one of the User Defined fields on the Account Maintenance window. I used UserDefined1 in my example.

The Join

You are going to create a SQL statement that matches the account on the Tax Details card with the Account on the Account Maintenance screen. You are also going to match UserDefined1 with the Vendor Master. This will marry a vendor to each tax detail. Now, include whatever tables are needed to retrieve the sales tax amount in whatever period you are working with and use the Integration Manager (or eConnect, etc) to create a payables transaction for the resulting amount.

I used the SQL statement below to retrieve the sales tax detail amount on historical SOP Invoices. I hard coded the date range to select documents with an invoice date between the first day of the previous month and the last day of the previous month, you wouldn’t want to do that. I just put it in here to show how it is done. Voided documents were excluded:

/* This query returns the sales tax amount on historical SOP Invoices with an invoice
   date between the first day of the previous month and the last date of the
   previous month. Voided documents are excluded.
  
   It uses the following tables:
  
   SOP30200    Sales Transaction History
   SOP10105    Sales Taxes Work and History
   GL00100    Account Master
   GL00105    Account Index Master
   PM00200    Vendor Master
   TX00201    Sales/Purchases Tax Master  
*/

SELECT    
  CASE SOP30200.SOPTYPE
     WHEN 1 THEN 'Quote'
     WHEN 2 THEN 'Order'
     WHEN 3 THEN 'Fulfillment Order'
     WHEN 4 THEN 'Invoice'
     WHEN 5 THEN 'Return'
  END AS Doc_Type
, SOP10105.SOPNUMBE AS SOP_Number
, SOP30200.DOCDATE AS Invoice_Date
, SOP30200.CUSTNMBR AS Customer_ID
, SOP30200.CUSTNAME AS Customer_Name
, GL00100.USERDEF1 AS User_Defined1
, PM00200.VENDNAME AS Vendor_Name
, GL00105.ACTNUMST AS GL_Account_Number
, SOP10105.TAXDTLID AS Tax_Detail_ID
, SOP10105.STAXAMNT AS Sales_Tax_Amt
, SOP10105.FRTTXAMT AS Tax_on_Freight
, SOP10105.MSCTXAMT AS Tax_on_Misc
, DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE())) as FirstDayPrevMo
, DATEADD (d,-DATEPART(d,GETDATE()),GETDATE()) as LastDayPrevMo

FROM

SOP10105 INNER JOIN
        SOP30200 ON SOP10105.SOPTYPE = SOP30200.SOPTYPE
    AND SOP10105.SOPNUMBE = SOP30200.SOPNUMBE
      INNER JOIN GL00100 ON SOP10105.ACTINDX = GL00100.ACTINDX INNER JOIN
                     TX00201 ON SOP10105.TAXDTLID = TX00201.TAXDTLID INNER JOIN
                     PM00200 ON GL00100.USERDEF1 = PM00200.VENDORID INNER JOIN
                     GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
WHERE    

(SOP30200.SOPTYPE in (3,4)) and  VOIDSTTS = 0 
GO
 
Until next post!

Leslie

Friday, September 20, 2013

First and Last day of Previous Month

In the accounting world I so often need to limit my selection criteria to the first and last day of the previous month. This is certainly not a secret formula, but I now have a place where I can look it up. Sadly, I have not memorized this formula

First Day of Previous Month:

DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE()))

Last Day of Previous Month:

DATEADD(d,-DATEPART(d,GETDATE()),GETDATE())

Example:

SELECT * FROM SOP30200
WHERE  

VOIDSTTS = 0 and DOCDATE between
DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE())) and
DATEADD(d,-DATEPART(d,GETDATE()),GETDATE())

Until next post!

Thursday, August 29, 2013

GP2013 Time to Upgrade!!! Service Pack 2 is out!

megaphone

Yay!!
Service pack 2 for Dynamics GP 2013 has been released.

For everyone waiting for this momentous event, it is here.  Many folks hold out until Service Pack two on the theory that the fixes that didn’t make the ‘release to market’ version are fixed in Service Pack one, but it isn’t until Service Pack two where the real fixes are included that were discovered by actual users.

I am actually a member of the ‘Service Pack two’ crowd Smile.

For partners, you can find the DVD, full install code here: https://mbs.microsoft.com/partnersource/downloads/releases/MDGP2013_Release_Download

For customers, look here: https://mbs.microsoft.com/customersource/downloads/servicepacks/MDGP2013_Release_Download

For Partners, If all you’re looking for is the Service Pack 2 patch, look here: https://mbs.microsoft.com/partnersource/downloads/servicepack/MDGP2013_PatchReleases

For Customers, If all you’re looking for is the Service Pack 2 patch, look here: https://mbs.microsoft.com/customersource/downloads/servicepacks/MDGP2013_PatchReleases

The official ‘Fix List’ is here: https://mbs2.microsoft.com/fileexchange/?fileID=07a5d226-5811-48f2-b783-667dd58669f2

Enjoy!

Until next post!

Leslie

Wednesday, July 3, 2013

Bootstrapper Error

Dynamics GP Forum member named ‘BenefitElect’ shared this solution with us. This is a problem that’s otherwise impossible to track down. Thanks to BenefitElect and Microsoft Tech Support, it will be no problem for us.

A big THANK YOU to BenefitElect for sharing!

The Problem

Here’s the scenario, you’re installing Dynamics GP 2013 on a x64 workstation and you are greeted with the following:

"An error occurred while installing system components for Microsoft Dynamics GP 2013 Bootstrapper.  Setup cannot continue until all system components have been successfully installed."

When you click the details button, the following is displayed:

"Setup has detected that the file C:\Users\User\AppData\Local\Temp\VSDC0C4.tmp\Watson\dw20shared.msi' has either changed since it was initially published or may be corrupt."

The Solution

Here’s the solution from Microsoft Tech Support:

1. First, you will need to navigate to the folder where the setup.exe is for GP 2013. I would like you to copy the Watson folder out of the installation media and paste it into the C:\ root directory on the workstation. The Watson folder should contain three different files: dw20shared.msi, dw20sharedamd64.msi, and dw20sharedia64.msi.

2. Next, click Start > type in cmd and then right-click on the Command Prompt icon in the search results and then left-clicked on "Run as Administrator".

3. In the Command Prompt window, perform a change directory to the Watson folder:

cd\

cd C:\Watson

4. Next, you will need to execute the following command to install Dr. Watson for a 64-bit workstation.

msiexec /i dw20sharedamd64.msi APPGUID={91710409-8000-11D3-8CFE-0150048383C9} /qb+

If this runs successfully, you should be able to go back to the setup.exe to finish the bootstrap installation