This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script was written for a client to extract Payables transactions from all companies tnto a temporary table; the script is run from the system database (typically called DYNAMICS) and selects all linked company databases.
The start and end years can be specified by the user in the highlighted variables.
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE TABLE #SupplierExtract(
INTERID CHAR(50)
,Company_Name CHAR(100)
,Vendor_ID CHAR(15)
,Vendor_Name CHAR(65)
,Document_Type CHAR(20)
,Document_Date datetime
,Voucher_Number CHAR(21)
,Document_Number CHAR(21)
,Document_Amount NUMERIC(19,5)
,Document_Net NUMERIC(19,5)
,Document_Tax NUMERIC(19,5)
,Payment_Voucher_Number CHAR(21)
,Payment_Doc_Number CHAR(21)
,Payment_Date datetime
,Apply_Date datetime
,Apply_Amount NUMERIC(19,5)
,Payment_Type CHAR(20)
)
GO
DECLARE @StartYear CHAR(4) = 2017
DECLARE @EndYear CHAR(4) = 2024
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
SELECT
CHAR(13)
+'select
'''+INTERID+''' AS Database_Name,
'''+CMPNYNAM+''' AS Division,
P.VENDORID AS Vendor_ID,
PM.VENDNAME AS Vendor_Name,
case P.DOCTYPE
when 1 then ''Invoice''
when 2 then ''Finance Charge''
when 3 then ''Misc Charge''
when 4 then ''Return''
when 5 then ''Credit Memo''
when 6 then ''Payment''
else ''''
end AS Document_Type,
P.DOCDATE AS Document_Date,
P.VCHRNMBR AS Voucher_Number,
P.DOCNUMBR AS Document_Number,
P.DOCAMNT AS Document_Amount,
P.PRCHAMNT AS Document_Net,
P.TAXAMNT AS Document_Tax,
coalesce(PA.VCHRNMBR,'''') AS Payment_Voucher_Number,
coalesce(P2.DOCNUMBR,'''') AS Payment_Doc_Number,
coalesce(P2.DOCDATE,''1/1/1900'') AS Payment_Date,
coalesce(PA.DATE1,''1/1/1900'') AS Apply_Date,
PA.APPLDAMT AS Apply_Amount,
case PA.DOCTYPE
when 1 then ''Invoice''
when 2 then ''Finance Charge''
when 3 then ''Misc Charge''
when 4 then ''Return''
when 5 then ''Credit Memo''
when 6 then ''Payment''
else ''''
end AS Payment_Type
from
(select
VENDORID,
DOCTYPE,
DOCDATE,
VCHRNMBR,
DOCNUMBR,
DOCAMNT,
PRCHAMNT,
TAXAMNT,
VOIDED
from '+INTERID+'.dbo.PM30200 with (NoLock)
union all
select
VENDORID,
DOCTYPE,
DOCDATE,
VCHRNMBR,
DOCNUMBR,
DOCAMNT,
PRCHAMNT,
TAXAMNT,
VOIDED
from '+INTERID+'.dbo.PM20000 with (NoLock)) P
left outer join
(select
VENDORID,
APTVCHNM,
APTODCTY,
APFRDCNM,
DOCTYPE,
DOCDATE,
APPLDAMT,
VCHRNMBR,
DATE1,
GLPOSTDT,
case POSTED when 0 then ''Unposted'' else ''Posted'' end POSTED
from '+INTERID+'.dbo.PM10200 with (NoLock)
UNION
select
VENDORID,
APTVCHNM,
APTODCTY,
APFRDCNM,
DOCTYPE,
DOCDATE,
APPLDAMT,
VCHRNMBR,
DATE1,
GLPOSTDT,
''Posted'' AS POSTED
from '+INTERID+'.dbo.PM30300 with (NoLock)
UNION
select
VENDORID,
VCHRNMBR AS APTVCHNM,
DOCTYPE AS APTODCTY,
APTODCNM AS APFRDCNM,
APTODCTY AS DOCTYPE,
APTODCDT AS DOCDATE,
APPLDAMT,
APTVCHNM AS VCHRNMBR,
DATE1,
GLPOSTDT,
case POSTED when 0 then ''Unposted'' else ''Posted'' end POSTED
from '+INTERID+'.dbo.PM10200 with (NoLock)
union
select
VENDORID,
VCHRNMBR AS APTVCHNM,
DOCTYPE AS APTODCTY,
APTODCNM AS APFRDCNM,
APTODCTY AS DOCTYPE,
APTODCDT AS DOCDATE,
APPLDAMT,
APTVCHNM AS VCHRNMBR,
DATE1,
GLPOSTDT,
''Posted'' AS POSTED
from '+INTERID+'.dbo.PM30300 with (NoLock)) PA
on P.VCHRNMBR = PA.APTVCHNM and P.VENDORID = PA.VENDORID
and P.DOCTYPE = PA.APTODCTY
left outer join
(select
VCHRNMBR,
DOCTYPE,
DOCNUMBR,
DOCDATE
from '+INTERID+'.dbo.PM20000 with (NoLock)
union all
select
VCHRNMBR,
DOCTYPE,
DOCNUMBR,
DOCDATE
from '+INTERID+'.dbo.PM30200 with (NoLock)) P2
on P2.VCHRNMBR = PA.VCHRNMBR and P2.DOCTYPE = PA.DOCTYPE
left outer join '+INTERID+'.dbo.PM00200 PM with (NoLock)
on P.VENDORID = PM.VENDORID
where P.DOCTYPE in (1,2,3,4,5,6) and P.VOIDED = 0 AND (P.DOCDATE BETWEEN '''+@StartYear+'/04/01'' AND '''+@EndYear+'/03/31'')'
FROM
SY01500 with (NoLock)
WHERE CMPNYNAM NOT LIKE '%Test%'
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
INSERT INTO #SupplierExtract (
InterID,
Company_Name,
Vendor_ID,
Vendor_Name,
Document_Type,
Document_Date,
Voucher_Number,
Document_Number,
Document_Amount,
Document_Net,
Document_Tax,
Payment_Voucher_Number,
Payment_Doc_Number,
Payment_Date,
Apply_Date,
Apply_Amount,
Payment_Type)
EXEC sys.sp_executesql @SQL
GO
SELECT
*
FROM
#SupplierExtract
GO
DROP TABLE #SupplierExtract
GO
