SQL Scripts for Microsoft Dynamics GP: SQL View to Return 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 creates a view which returns details about current PO commitments.

The client in question has a second segment which contains the cost centre; the sections with these fields have been highlighted if you need to change them.

CREATE VIEW [dbo].[uv_AZRCRV_POCommitmentDetail] AS 
/*
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'
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'] --PM Vendor Master File (PM00200)
		ON
			['PM Creditor Master'].VENDORID = ['Purchase Order Commitments'].VENDORID
INNER JOIN
	POP10110 AS ['Purchase Order Lines'] --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
GO

GRANT SELECT ON uv_AZRCRV_POCommitmentDetail TO DYNGRP
GO