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

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