Category: General Ledger

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

GP #LifeHacks 103: Track JE's in #Excel – Paste into GP

I love Excel!  Who doesn’t, right?  As an accountant, I live in Excel.  This is the easiest place for me to track and create Journal Entries. But after all that work, how do we get it into our accounting system, Microsoft Dynamics GP? Easy, we just ‘Paste’ the entry. Simple, I know.

GP Life Hack #103
Dynamics GP offers the cool functionality of pasting in copied data from Excel.  This includes distribution reference, the GL Account number, Debit Amount and the Credit Amount:
1

GP Life Hack #103 … click to see original posting

GP #LifeHacks 121: Keep your AP & AR in balance with your General Ledger

I know your team works hard… day in and day out… and at month end, you reconcile your Accounts Payable or Accounts Receivable sub ledger to your GL Trial Balance with an optimistic approach that never seems to really pay off.  No matter how hard you try, there is always a reconciling adjustment needed in order to balance…  UGH!  Whether it’s a penny, 1,000.00 or 61,312.76… it’s downright frustrating.

What is causing this variance?  Usually it is a journal entry.  We all love them, but let’s be honest… not everyone makes journal entries with the same level of confidence as you do.  Let’s get this game of ‘tug of war’ fixed once and for all!

GP Life Hack #121

Here’s a little GP Life Hack that could save you hours and hours of research.  By using a setting on your Dynamics GP account maintenance window, you can restrict non system entries to certain accounts, specifically your AP or AR accounts.  After all, in order to balance the sub ledger to the general ledger, there should be strict continuity…. Right?  RIGHT!  So let’s get things in balance and keep them there once and for all.