Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
DynDeveloper.com

DynDeveloper.com

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
missieh 11/1/2018 10:26:54 AM

You need to learn pivot tables in Excel and here's why

* This article, and all our great Dynamics GP documentation, Is available on the Dynamics GP menu

This very simple, yet very useful pivot table in Excel puts all the paycodes from Dynamics GP in a spreadsheet for a payroll expert to review.

We will take data directly from the UPR30300 paycheck detail table like this...

And turn it into this, one line per employee, showing all their aggregated payroll code data. Note that the pay codes are grouped, in the screen shot below you can see Benefits and Deductions

 

Start with copying this select statement into a SQL query window and run it.

Select
      d.EMPLOYID as 'Employee_ID',
      PAYROLCD as Payroll_Code,
      case when PYRLRTYP = '1' then 'RegularPay'
              when PYRLRTYP = '2' then 'Deductions'
              when PYRLRTYP = '3' then 'Benefits'
              when PYRLRTYP = '4' then 'Taxes'
              when PYRLRTYP = '5' then 'LocalTax'
              else '' end as Payroll_Type,
      UPRTRXAM as Amount,
      CHEKDATE as Check_Date
              from upr30300 d
                  where year(d.CHEKDATE) = year(GETDATE())

4Penny.net
Version: All
Section: Dynamics GP,SQL Scripts






Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables