Category: GP SQL Views

Dimensions, Dynamics GP, GP SQL Views

Dimension Analysis for MDA – SQL View for Dynamics GP

Earlier I wrote about how to use MDA for dimension analysis rather than Analytical Accounting.  You can find that blog here.
To continue that thought, I have created a SQL view to help users get that dimensional analysis data easily!  The script captures commonly used data – if you feel I am missing something, please let me know and I will try to add it to the view for others.  I also like to push this data to Excel and Power BI – take a look at the Excel Report and Power BI reports I created at the bottom of this post!
The script below should be run against your company database – you can then use it to build a smartlist.  Instructions for adding the view to a smartlist using Smartlist Designer are included with GP LifeHacks #137.
Here is the view:
Continue reading “Dimension Analysis for MDA – SQL View for Dynamics GP” →

Dynamics GP, GP SQL Views

1099 Year End Edit – SQL View for Dynamics GP

There are many ways to look at 1099 data – here is my recent pass at looking at the actual 1099 Period Detail Table as well as having the 1099 Type and Box information.
Apply this to your company database and bring into SmartLists.

CREATE view [dbo].[TEN99_Audit_List]
as
SELECT A.VENDORID Vendor_ID,
B.VENDNAME Vendor_Name,
B.VNDCHKNM Vendor_Check_Name,
B.VNDCLSID Class_ID,
case B.VENDSTTS
when 1 then 'Active'
when 2 then 'Inactive'
when 3 then 'Temporary'
end Vendor_Status,
case A.TEN99TYPE
when 1 then 'Not a 1099 Vendor'
when 2 then 'Dividend'
when 3 then 'Interest'
when 4 then 'Miscellaneous'
when 5 then 'Nonemployee Compensation'
end Type, a.ten99type, a.ten99boxnumber,
case --Set 1099 Box Number Values
when A.TEN99TYPE = 1 then 'Not a 1099 Vendor'
when A.TEN99BOXNUMBER = 0 then 'Not a 1099 Vendor'
--1099 Type Dividend Box Numbers
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 1 then '1a - Ordinary Dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 2 then '1b - Qualified Dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 3 then '2a - Capital Gain Dist.'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 4 then '2b - Unrecap. 1250 Gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 5 then '2c - Section 1202 Gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 6 then '2d - 28% Rate Gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 7 then '3 - Nontaxable Dist.'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 8 then '4 - Federal Tax Withheld'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 9 then '5 - Section 199A dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 10 then '6 - Investment Expense'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 11 then '7 - Foreign Tax Paid'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 12 then '9 - Cash'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 13 then '10 - Noncash'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 14 then '11 - Exempt Interest Dividend'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 15 then '12 - Specified Private Activity'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 16 then '15 - State Tax Withheld'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 17 then 'XX - Unknown'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 18 then 'XX - Unknown'
--1099 Type Interest Box Numbers
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 1 then '1 - Interest'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 2 then '2 - Early Withdrawal'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 3 then '3 - U.S. Savings Bond Int.'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 4 then '4 - Federal Tax Withheld'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 5 then '5 - Investment Expense'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 6 then '6 - Foreign Tax Paid'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 7 then '8 - Tax-Exempt Interest'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 8 then '9 - Private Activity Bond Int.'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 9 then '10 - Market Discount'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 10 then '11 - Bond Premium'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 11 then '12 - Bond premium on Treasury obligations'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 12 then '13 - Bond premium on Tax-exempt bond'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 13 then '17 - State Tax Withheld'
--1099 Type Miscellaneous Box Numbers
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 1 then '1 - Rents'
When A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 2 then '2 - Royalties'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 3 then '3 - Other Income'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 4 then '4 - Federal Tax Withheld'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 5 then '5 - Fishing Boat Proceeds'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 6 then '6 - Medical Payments'
--The 1099 Box 7 is now a checkbox on the form. Update value in: Purchasing > Cards > 1099 Details
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 7 then '8 - Substitute Payments'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 8 then '9 – Crop Insurance'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 9 then '10 - Attorney Proceeds'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 10 then '12 - 409A Deferrals'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 11 then '13 - Golden Parachute'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 12 then '14 – Section 409A Income'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 13 then '15 - State Tax Withheld'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 14 then '18 - State Income'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 15 then 'XX - Unknown'
--1099 Type NEC Box Numbers
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 1 then '1 - Nonemployee Compensation'
When A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 2 then '2 - Federal Tax Withheld'
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 3 then '3 - State Tax'
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 4 then '4 - State Income'
end [1099_Box_Number],
B.PYMTRMID Payment_Terms_ID,
A.YEAR1 [Year],
sum(A.TEN99AMNT) Amount_Paid_1099,
--NEW DBA FIELD
B.VENDDBA Vendor_DBA,
B.TXIDNMBR Tax_ID,
B.ADDRESS1 Address_1,
B.ADDRESS2 Address_2,
B.ADDRESS3 Address_3,
B.CITY City,
B.[STATE] [State],
B.ZIPCODE Zip_Code,
B.COUNTRY Country
FROM PM00204 A
INNER JOIN PM00200 B ON A.VENDORID = B.VENDORID
GROUP BY A.VENDORID, B.VENDNAME, B.VNDCLSID,
B.VENDSTTS, A.TEN99TYPE, B.PYMTRMID, A.YEAR1, B.ADDRESS1, B.ADDRESS2,
B.ADDRESS3, B.CITY, B.[STATE], B.ZIPCODE, B.COUNTRY, B.TXIDNMBR, B.VNDCHKNM, A.TEN99BOXNUMBER,B.VENDDBA
GO
grant select on [TEN99_Audit_List] TO DYNGRP
Dynamics GP, GP SQL Views, HR/PR, Smartlists

Year End Payroll Data – SQL View for Dynamics GP

Year End is a busy time, especially for those using Dynamics GP Payroll!  Below is a SQL Script that can pull the year end wage file data into a smartlist!  COOL!!
It is built to pull in records for federal, state, local, special box and the other box.  Because of this, employees may have multiple rows if the employee has multiple states, special boxes, or other box data.  Using a pivot table cleans this right up!  Check it out!

CREATE VIEW [dbo].[Year_End_Wage_Data_Payroll]
AS
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Year End Wage Data
-- Run Against Company Database
-- Only provides data populated by the Year End Closing Routine
-- Visit http://lifehacks365.com for more cool GP Stuff!
-- Updated by: Shawn Dorward - As of 01/14/2019
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SELECT
A.RPTNGYR Year1,
A.LASTNAME Last_Name,
A.FRSTNAME First_Name,
A.EMPLOYID Employee_ID,
A.ADDRESS1 Address_1,
A.ADDRESS2 Address_2,
A.Address3 Address_3,
A.CITY City,
A.STATE State,
A.ZIPCODE Zip_Code,
A.WGTPCOMP Gross_Wages,
A.FEDITXWH Federal_Tax_Withheld,
A.SSECWAGS SS_Wages,
A.SSTXWHLD SS_Tax_Withheld,
A.MCRWGTPS Medicare_Wages,
A.MDCRTXWH Medicare_Tax_Withheld,
B.STATECD State_Tax_Code,
B.STATEWGS State_Wages,
B.STATINTX State_Tax_Withheld,
C.LOCLCODE Local_Tax_Code,
C.LOCLWGES Local_Wages,
C.LCLINTAX Local_Tax_Withheld,
D.SPCLCODE Special_Code,
D.SPCLBNUM Special_Box_Number,
D.SPCLAMNT Special_Amount,
E.OTHRCODE Other_Code,
E.OTHBXNUM Other_Box_Number,
E.OTHRAMNT Other_Amount
--Payroll Year End Table
FROM UPR10101 A
-- Adding Payroll Year End State Table
left outer join
UPR10105 B on A.RPTNGYR = B.RPTNGYR and A.EMPLOYID = B.EMPLOYID
-- Adding Payroll Year End Local Table
left outer join
UPR10106 C on A.RPTNGYR = C.RPTNGYR and A.EMPLOYID = C.EMPLOYID
-- Adding Payroll Year End Special Table
left outer join
UPR10104 D on A.RPTNGYR = D.RPTNGYR and A.EMPLOYID = D.EMPLOYID
-- Adding Payroll Year End Other Table
left outer join
UPR10107 E on A.RPTNGYR = E.RPTNGYR and A.EMPLOYID = E.EMPLOYID
go
grant select on [Year_End_Wage_Data_Payroll] TO DYNGRP
Company, Dynamics GP, General Ledger, GP SQL Views

Detailed Period Closing List – SQL View for Dynamics GP

 
CREATE VIEW [dbo].[Detailed_Period_Closing_List]
AS
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Detailed Period Close Data
-- Run Against Company Database
-- Provides Checklist for Fiscal Period Close Settings - Detailed
-- Visit http://lifehacks365.com for more cool GP Stuff!
-- Updated by: Shawn Dorward - As of 09/14/2017
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SELECT
YEAR1 Fiscal_Year,
PERNAME as Period,
PERIODDT as Period_Start_Date,
PERDENDT as Period_End_Date,
case sy40100.Series
 when 2 then 'Financial'
 when 3 then 'Sales'
 when 4 then 'Purchasing'
 when 5 then 'Inventory'
 when 6 then 'Payroll'
 when 7 then 'Project'
 else ''
 end Series,
ODESCTN as Origin_Name,
case sy40100.Closed
 when 0 then 'Open'
 When 1 then 'Closed'
 else ''
end Status,
DEX_ROW_ID as Dex_Row_ID
FROM sy40100
WHERE Pername <> 'Beginning Balance' and series <> 1 and series <> 0
-- Adding Permissions to DYNGRP
GO
GRANT SELECT on [Detailed_Period_Closing_List] to DYNGRP