SQL Scripts for Microsoft Dynamics GP: Extract Payables Transactions from All Companies

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