Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SQL Script for Updatin...
Microsoft Dynamics GP (Archived)

SQL Script for Updating Payroll Type for Year-End

(0) ShareShare
ReportReport
Posted on by john.ellis 3,482

Hello:

One employee was set up with the Payroll Type of "Other", when he should have been set up with the Payroll Type of "Pension".

A 1099-R needs to be processed for this employee, here at year-end.  So, his Payroll Type needs to be changed to "Pension".

Normally, the following SQL script could be run to accomplish this, since PayType of 11 = "Pension" and PayType of 12 = "Other":

update UPR00400
set PayType = '11'
where EMPLOYID = 'DIAZ0001'
and PayType = '12'
and PAYRCORD = 'RETIRE'

Now, would we also have to use the Check Date from the UPR30300 table where CHEKDATE > 12/31/2016?  Or, will the above script work and simply allow for a 1099-R to be generated?

Also, should the UPR40600 table for "RETIRE" have its Pay Type changed to 11 ("Pension")?

Thanks!

John

*This post is locked for comments

  • john.ellis Profile Picture
    john.ellis 3,482 on at
    RE: SQL Script for Updating Payroll Type for Year-End

    Thank you, Leslie!  I really appreciate that!

    John

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: SQL Script for Updating Payroll Type for Year-End

    For sure that would need to be done, but when I was messing around with it I tried that too and I still didn't get any 1099R forms. I closed and reclosed payroll,  ran check links and reconcile and still no Forms 1099R. I just picked one of the TWO employees. None of them have an Other pay type set up, so it really wasn't a good test. I'll set up one of the other employees with an Other paycode, run through a few payrolls and see if I have better luck with the change.

    Kind regards,

    Leslie

  • john.ellis Profile Picture
    john.ellis 3,482 on at
    RE: SQL Script for Updating Payroll Type for Year-End

    Thanks, for looking into this, Leslie!

    I was hoping that changing the pay type at the employee level would work.

    Does that mean that an update against the UPR30300 table for the employee would need to be done, as well?

    John

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: SQL Script for Updating Payroll Type for Year-End

    John,

    I couldn't get it to work correctly using the script above and limiting the change just to the checkdate where year1 = 2017. No 1099Rs were created. Do you get it to create 1099Rs using these scripts?

    Leslie

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans