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://GPLifehacks.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

This site uses Akismet to reduce spam. Learn how your comment data is processed.