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