SQL Scripts for Microsoft Dynamics GP: PO Commitment Detail

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 details of purchase orders and the related commitments.

/*
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

	['POE Commitment Setup Lines'].BUDGETID AS 'Budget ID'

	,['Budget Master'].BUDCOMNT AS 'Budget Description'

	,['POE Commitment Setup Lines'].YEAR1 AS 'Budget Year'

	,['Purchase Order Commitments'].PONUMBER AS 'PO Number'

	,['Purchase Order Commitments'].VENDORID AS 'Creditor ID'

	,['PM Creditor Master'].VENDNAME AS 'Creditor Name'

	,['Purchase Order Commitments'].Committed_Amount AS 'Committed Amount'

	,['Purchase Order Lines'].ITEMNMBR AS 'Item Number'

	,['Purchase Order Lines'].ITEMDESC AS 'Item Description'

	,['Account Index Master'].ACTNUMST AS 'Account Number'

	,['Account Master'].ACTDESCR AS 'Account Description'

	,['Account Index Master'].ACTNUMBR_2 AS 'Cost Centre'

	,['Segment Description Master'].DSCRIPTN AS 'Cost Centre Description'

	,['Purchase Order Work'].USER2ENT AS 'Created By User'

	,['Purchase Order Work'].DOCDATE AS 'Document Date'

	,['Purchase Order Lines'].REQSTDBY AS 'Requested By'

	,['Payables Transactions'].VCHRNMBR AS 'AP Voucher Number'

	,['Payables Transactions'].DOCAMNT AS 'AP Document Amount'

FROM

	SY40101 AS ['Financial Calendar Header'] WITH (NOLOCK) --Period Header (SY40101)
INNER JOIN

	CPO10110 AS ['Purchase Order Commitments'] WITH (NOLOCK) --CPOP_Line_Ctrl (CPO10110)
		ON

			['Purchase Order Commitments'].REQDATE BETWEEN ['Financial Calendar Header'].FSTFSCDY AND ['Financial Calendar Header'].LSTFSCDY

INNER JOIN

	PM00200 AS ['PM Creditor Master'] WITH (NOLOCK) --PM Vendor Master File (PM00200)
		ON

			['PM Creditor Master'].VENDORID = ['Purchase Order Commitments'].VENDORID

INNER JOIN

	POP10110 AS ['Purchase Order Lines'] WITH (NOLOCK) --Purchase Order Line (POP10110)
		ON

			['Purchase Order Lines'].PONUMBER = ['Purchase Order Commitments'].PONUMBER

		AND

			['Purchase Order Lines'].ORD = ['Purchase Order Commitments'].ORD

INNER JOIN

	GL00105 AS ['Account Index Master'] WITH (NOLOCK) --Account Index Master (GL00105)
		ON

			['Account Index Master'].ACTINDX = ['Purchase Order Commitments'].ACTINDX

LEFT JOIN

	GL00100 AS ['Account Master'] WITH (NOLOCK) --Breakdown Account Master (GL00100)
		ON

			['Account Master'].ACTINDX = ['Purchase Order Commitments'].ACTINDX

INNER JOIN

	GL40200 AS ['Segment Description Master'] WITH (NOLOCK) --Segment Description Master (GL40200)
		ON

			['Segment Description Master'].SGMTNUMB = 2

	AND

		['Segment Description Master'].SGMNTID = ['Account Index Master'].ACTNUMBR_2

INNER JOIN

	CPO40002 AS ['POE Commitment Setup Lines'] WITH (NOLOCK) --CPOP_Setup_LINE (CPO40002)
		ON

			['POE Commitment Setup Lines'].YEAR1 = ['Financial Calendar Header'].YEAR1

INNER JOIN

	GL00200 AS ['Budget Master'] WITH (NOLOCK) --Budget Master (GL00200)
		ON

			['Budget Master'].BUDGETID = ['POE Commitment Setup Lines'].BUDGETID

INNER JOIN

	POP10100 AS ['Purchase Order Work'] WITH (NOLOCK) --Purchase Order Work (POP10100)
		ON

			['Purchase Order Work'].PONUMBER = ['Purchase Order Lines'].PONUMBER

LEFT JOIN

	(

		SELECT

			VCHRNMBR

			,DOCAMNT

			,PONUMBER

		FROM

			PM20000 --PM Vendor Master File (PM00200)
		UNION

			SELECT

				VCHRNMBR

				,DOCAMNT

				,PONUMBER

			FROM

				PM30200 --PM Paid Transaction History File (PM30200)
	) AS ['Payables Transactions']
		ON

			['Payables Transactions'].PONUMBER = ['Purchase Order Lines'].PONUMBER

WHERE

	['Purchase Order Commitments'].Committed_Amount > 0

Read original post SQL Scripts for Microsoft Dynamics GP: PO Commitment Detail at azurecurve|Ramblings of an IT Professional