Day: September 29, 2023

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

Ian Grieve7 minute read0 comment

Microsoft Dynamics GPThis 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

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

In Microsoft Dynamics 365 Business Central (Inventory and Warehouse Management), how do I… Process Multiple Inventory Put-aways Using a Batch Job

Ian Grieve3 minute read0 comment

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Inventory and Warehouse Management), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

In a previous article of this series on understanding inventory put-aways, I mentioned there was three ways in which an inventory put-away could be processed:

  1. Process an Inventory Put-away from the Source Document
  2. Process Multiple Inventory Put-aways Using a Batch Job
  3. Process an Inventory Put-away in Two Steps by Releasing the Source Document

In this article, I am going to take walk through processing inventory put-aways using a batch job for the BLUE location on which I enabled Require Put-away.

To use the batch job for creating inventory put-aways, search for the Create Inventory Put-away/Pick/Movement page.

The options on this page will need to be configured to restrict which incoming documents have inventory put-aways created. I have opted for all (released) purchase orders; click OK to create the put-aways or Schedule to schedule the batch job to run at a designated time:

Create Inventory Put-away/Pick/Movement

Continue reading “In Microsoft Dynamics 365 Business Central (Inventory and Warehouse Management), how do I… Process Multiple Inventory Put-aways Using a Batch Job”