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

Leave a Reply