In this small technical article we will give SQL script to build up something like Crystal Report or even now becoming popular SRS (MS SQL Server Reporting Services) report. You can envelop this script into SQL stored procedure
Prior to the nice time when you will be looking and analyzing SQL script itself, it is good idea to give you some highlights on the join we utilize here. We have to unionize GP open and history PM tables. In Great Plains the posting logic moves unapplied Vendor Invoices to work table PM20000, in the same table all the payments, credit and debit memo go. When you apply completely payment to invoice – both documents go to history: PM30200 table. Now you see the reason to unionize historical and open tables. You also should be aware about PM20100 and PM30300 tables: Payment Apply To Open and History. If you would like to analyze Microsoft Dexterity tables descriptions, you should launch GP and go to: Tools->Resource Description->Tables. Please, note here, that we use DEX_ROW_ID as unique value to avoid union known line duplication issue
CREATE PROCEDURE SP_HistoricalAP
@CutoffDate datetime
AS
select
a.VCHRNMBR,
a.VENDORID,
a.DOCTYPE,
a.DOCDATE,
a.POSTEDDT,
a.DOCNUMBR,
a.DOCAMNT,
case
when b.APTVCHNM is null then a.DOCAMNT
else (a.DOCAMNT-b.APPLDAMT)
end as CURRAMT,
a.DEX_ROW_ID as DEX_ROW_ID1,
b.DEX_ROW_ID as DEX_ROW_ID2,
a.DISCAMNT,
a.DUEDATE,
a.PYMTRMID,
a.VOIDED
from
--PM30200
(
select
VCHRNMBR,
VENDORID,
DOCTYPE,
DOCDATE,
POSTEDDT,
DOCNUMBR,
DOCAMNT,
DEX_ROW_ID,
DISCAMNT,
DUEDATE,
PYMTRMID,
VOIDED
from PM30200
union
select
VCHRNMBR,
VENDORID,
DOCTYPE,
DOCDATE,
POSTEDDT,
DOCNUMBR,
DOCAMNT,
DEX_ROW_ID,
DISCAMNT,
DUEDATE,
PYMTRMID,
VOIDED
from PM20000
)
a
left join
--PM30300
(
select APTODCTY, APTVCHNM, sum(APPLDAMT) as APPLDAMT, max(ApplyToGLPostDate) as ApplyToGLPostDate, max(DEX_ROW_ID) as DEX_ROW_ID from PM30300 group by APTODCTY, APTVCHNM
union
select APTODCTY, APTVCHNM, sum(APPLDAMT) as APPLDAMT, max(ApplyToGLPostDate) as ApplyToGLPostDate, max(DEX_ROW_ID) as DEX_ROW_ID from PM20100 group by APTODCTY, APTVCHNM
)
b on a.VCHRNMBR=b.APTVCHNM and a.DOCTYPE=b.APTODCTY and a.DOCTYPE<=4 and a.VOIDED=0
where a.DOCDATE<=@CutoffDate and b.ApplyToGLPostDate<=@CutoffDate
union
select
a.VCHRNMBR,
a.VENDORID,
a.DOCTYPE,
a.DOCDATE,
a.POSTEDDT,
a.DOCNUMBR,
-a.DOCAMNT,
case
when b.VCHRNMBR is null then a.DOCAMNT
else a.DOCAMNT-b.APPLDAMT
end as CURRAMT,
a.DEX_ROW_ID as DEX_ROW_ID1,
b.DEX_ROW_ID as DEX_ROW_ID2,
a.DISCAMNT,
a.DUEDATE,
a.PYMTRMID,
a.VOIDED
from
--PM30200
(
select
VCHRNMBR,
VENDORID,
DOCTYPE,
DOCDATE,
POSTEDDT,
DOCNUMBR,
DOCAMNT,
DEX_ROW_ID,
DISCAMNT,
DUEDATE,
PYMTRMID,
VOIDED
from PM30200
union
select
VCHRNMBR,
VENDORID,
DOCTYPE,
DOCDATE,
POSTEDDT,
DOCNUMBR,
DOCAMNT,
DEX_ROW_ID,
DISCAMNT,
DUEDATE,
PYMTRMID,
VOIDED
from PM20000
)
a
left join
--PM30300
(
select DOCTYPE, VCHRNMBR, sum(APPLDAMT) as APPLDAMT, max(ApplyToGLPostDate) as ApplyToGLPostDate, max(DEX_ROW_ID) as DEX_ROW_ID from PM30300 group by DOCTYPE, VCHRNMBR
union
select DOCTYPE, VCHRNMBR, sum(APPLDAMT) as APPLDAMT, max(ApplyToGLPostDate) as ApplyToGLPostDate, max(DEX_ROW_ID) as DEX_ROW_ID from PM20100 group by DOCTYPE, VCHRNMBR
)
b on a.VCHRNMBR=b.VCHRNMBR and a.DOCTYPE=b.DOCTYPE and a.DOCTYPE>4 and a.VOIDED=0
where a.DOCDATE<=@CutoffDate and b.ApplyToGLPostDate<=@CutoffDate
Dynamics GP Chart of Accounts Copy From One Company to Another
When you are implementing new Corporate ERP system it is good idea to review or even maybe redesign your legacy Chart of Accounts. There are sample COA available for the company that you could load during the installation in Utilities (such as generic Corporation, Partnership), however they might look too generic.
Balances Brought Forward in Dynamics GP Trial Balance Report
If you are keeping past years open in Great Plains (current name Microsoft Dynamics GP) and do not close them in Financial Routines, you may feel a bit disappointed that Financial Trial Balance report doesn’t show beginning balance for the current year.
Sales Register Report: Great Plains Dynamics GP Reporting Samples
If you are controller or responsible for tying numbers from General Ledger back to Sales Journal, then you may like to have one place, where you see your Monthly or Quarterly Sales Ledger distribution in General Ledger.
