Month: April 2023

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was written to return documents included in the selected payment batch (entered in the highlighted parameter) along with the apply information.

/*
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).
*/
DECLARE @BACHNUMB VARCHAR(15) = 'COMPUTER CHECKS'

SELECT
	['Payables Document Types'].DOCTYNAM
	,['PM Creditor Master'].VENDNAME
	,['PM Transactions'].*
	,['PM Payment Apply'].*
FROM
	(
		SELECT
			BACHNUMB
			,VCHRNMBR
			,VENDORID
			,DOCDATE AS PYMTDOCDATE
			,CHEKBKID
			,PSTGDATE
		FROM
			PM10300 AS ['PM Payment Work'] --PM Payment WORK (PM10300)
		UNION ALL
			SELECT
				BACHNUMB
				,VCHRNMBR
				,VENDORID
				,DOCDATE AS PYMTDOCDATE
				,CHEKBKID
				,PSTGDATE
			FROM
				PM30200 --PM Paid Transaction History File (PM30200)
			WHERE
				DOCTYPE = 6
		) AS ['PM Transactions']
INNER JOIN
	(
		SELECT
			DOCTYPE
			,PMNTNMBR
			,APFVCHNM
			,DOCDATE
			,CASE WHEN DOCTYPE >= 3 THEN
				DOCAMNT * -1
			ELSE
				DOCAMNT
			END AS DOCAMNT
			,CASE WHEN DOCTYPE >= 3 THEN
				0
			ELSE
				AMNTPAID
			END AS AMNTPAID
			,Net_Paid_Amount
		FROM
			PM10201 AS PM --PM Payment Apply To Work File (PM10201)
	) AS ['PM Payment Apply']
		ON
			['PM Payment Apply'].PMNTNMBR = ['PM Transactions'].VCHRNMBR
INNER JOIN
	PM40102 AS ['Payables Document Types'] --Payables Document Types (PM40102)
		ON
			['Payables Document Types'].DOCTYPE = ['PM Payment Apply'].DOCTYPE
INNER JOIN
	PM00200 AS ['PM Creditor Master'] --PM Vendor Master File (PM00200)
		ON
			['PM Creditor Master'].VENDORID = ['PM Transactions'].VENDORID
WHERE
	['PM Transactions'].BACHNUMB = @BACHNUMB
ORDER BY
	['PM Payment Apply'].APFVCHNM

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script creates a table function which will split a string based on the supplied delimiter; it was created for a user who split their item number into segments, separated with hyphens:

/*
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).
*/
IF OBJECT_ID('[dbo].[uf_AZRCRV_SplitString]') IS NOT NULL
BEGIN
	DROP FUNCTION [dbo].[uf_AZRCRV_SplitString];
END;
GO

CREATE FUNCTION [dbo].[uf_AZRCRV_SplitString](
    @String VARCHAR(8000),
    @Delimiter VARCHAR(10)
)
RETURNS @ReturnTable TABLE(
    id int IDENTITY(1,1) NOT NULL,
    part varchar(50) NULL
)
AS
BEGIN
	Declare @iSpaces int
	Declare @part varchar(50)
	
	Set @String = RTRIM(@String)
	
	--initialize spaces
	Select @iSpaces = charindex(@Delimiter, @String, 0)
	While @iSpaces > 0
	
	Begin
		Select @part = substring(@String, 0, charindex(@Delimiter, @String, 0))
		
		Insert Into @ReturnTable(part)
			Select @part
		
		Select @String = substring(@String,charindex(@Delimiter, @String, 0) + len(@Delimiter),len(@String) - charindex(' ', @String, 0))
		
		Select @iSpaces = charindex(@Delimiter, @String, 0)
	end
	
	If len(@String) > 0
		Insert Into @ReturnTable
			Select @String
	
    RETURN 
END
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns the top level BOM for a manufacturing order.

/*
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).
*/
SELECT
	['Mfg Order Master'].MANUFACTUREORDER_I
	,['Mfg Order Master'].ITEMNMBR
	,'CUSTOMER ITEM NUMBER' AS CustomerItemNumber
	,['Item Master'].ITEMDESC
	,['Mfg Order Master'].ENDQTY_I
	,['Inventory U of M Schedule Setup'].BASEUOFM
	,'DRAWING NUMBER' AS DrawingNumber
	,'REVISION' AS Revision
	,'SPEC NUMBER AND ISSUE' AS SpecificationAndIssue
	,['Bill Of Material Line File'].CPN_I AS ComponentS
FROM
	WO010032 AS ['Mfg Order Master'] --WO010032
INNER JOIN
	IV00101 AS ['Item Master'] --Item Master (IV00101)
		ON
			['Item Master'].ITEMNMBR = ['Mfg Order Master'].ITEMNMBR
INNER JOIN
	IV40201 AS ['Inventory U of M Schedule Setup'] --Inventory U of M Schedule Setup (IV40201)
		ON
			['Inventory U of M Schedule Setup'].UOMSCHDL = ['Item Master'].UOMSCHDL
INNER JOIN
	BM010115 AS ['Bill Of Material Line File'] --BM010115
		ON
			['Bill Of Material Line File'].PPN_I = ['Mfg Order Master'].ITEMNMBR