SQL Scripts for Microsoft Dynamics GP: List Tax Detail Transactions

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

This script was created for a client to list out all tax detail transactions in the system; there is a SmartList which will show this information, but they wanted to build a report outside of Dynamics GP for a user who did not have access to GP.

CREATE VIEW [dbo].[uv_AZRCRV_TaxDetailTransactions] 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
	RTRIM(TX30000.TAXDTLID) AS 'Tax Detail',
	RTRIM(TX00201.TXDTLDSC) AS 'Tax Detail Description',
	CASE WHEN TX00201.TXDTLTYP = 1 THEN
		'Sales'
		
	ELSE
		'Purchases'
	END AS 'Tax Detail Type',
	CASE
		WHEN
			TX30000.TXDTLAMT > 0
		THEN
			TX30000.TXDTLAMT
		ELSE
			TX30000.TXDTLPCT
		END AS 'Tax Detail %',
	RTRIM(TX30000.DOCNUMBR) AS 'Document Number',
	RTRIM(TX30000.CustomerVendor_ID) AS 'Master ID',
	CASE
		WHEN TX30000.SERIES IN (1,2,3) THEN
			RM00101.CUSTNAME
		WHEN TX30000.SERIES IN (4,12) THEN
			PM00200.VENDNAME
		ELSE
			''
		END AS 'Master Name',
	TX30000.DOCDATE AS 'Document Date',
	TX30000.PSTGDATE AS 'Posting Date',
	TX30000.DOCAMNT + TX30000.Taxable_Amount AS 'Gross Amount',
	TX30000.DOCAMNT AS 'Net Amount',
	TX30000.Taxable_Amount AS 'Taxable Amount',
	TX30000.TAXAMNT AS 'Tax Amount',
	CASE WHEN TX30000.SERIES = 1 AND TX30000.DOCTYPE = 3 THEN
		'Invoice'
	WHEN TX30000.SERIES = 1 AND TX30000.DOCTYPE = 5 THEN
		'Return'
	WHEN TX30000.SERIES = 4 AND TX30000.DOCTYPE = 1 THEN
		'Invoice'
	WHEN TX30000.SERIES = 4 AND TX30000.DOCTYPE = 5 THEN
		'Credit Note'
	WHEN TX30000.SERIES = 4 AND TX30000.DOCTYPE = 6 THEN
		'Payment'
	END AS 'Document Type',
	TXGL00105.ACTNUMST AS 'Tax Account',
	TX30000.Tax_Date AS 'Tax Date',
	TX30000.ORTAXAMT AS 'Originating Tax Amount',
	TX30000.Originating_Taxable_Amt AS 'Originating Taxable Amount',
	TX30000.ORDOCAMT AS 'Originating Net Amount',
	CASE WHEN TX30000.VOIDSTTS = 0 THEN
		'No'
	ELSE
		'Yes'
	END AS 'Voided?',
	MC40200.CRNCYDSC AS 'Currency',
	CASE WHEN TX30000.SERIES = 1 THEN
		'Sales'
	ELSE
		'Purchases'
	END AS 'Series',
	CASE WHEN ISNULL(PMCCODE.ECFLAG,ISNULL(PMCCODE.ECFLAG, 0)) = 1 THEN
		'Yes'
	ELSE
		'No'
	END AS 'EU Member?'
	,TX30000.PERIODID
FROM
	TX30000 WITH (NOLOCK) --Tax History (TX30000)
INNER JOIN
	DYNAMICS..MC40200 WITH (NOLOCK) --Currency Setup (MC40200)
		ON
			MC40200.CURRNIDX = TX30000.CURRNIDX
INNER JOIN
	TX00201 WITH (NOLOCK) --Sales/Purchases Tax Master (TX00201)
		ON
			TX30000.TAXDTLID = TX00201.TAXDTLID
LEFT JOIN
	GL00105 AS TXGL00105 WITH (NOLOCK) --Account Index Master (GL00105)
		ON
			TXGL00105.ACTINDX = TX00201.ACTINDX
LEFT JOIN
	RM00101 WITH (NOLOCK) --RM Customer MSTR (RM00101)
		ON
			TX30000.CustomerVendor_ID = RM00101.CUSTNMBR
LEFT JOIN
	VAT10001 AS RMCCODE WITH (NOLOCK) --VAT Country Code MSTR (VAT10001)
		ON
			RMCCODE.CCode = RM00101.CCode
LEFT JOIN
	PM00200 WITH (NOLOCK) --PM Vendor Master File (PM00200)
		ON
			TX30000.CustomerVendor_ID = PM00200.VENDORID
LEFT JOIN
	VAT10001 AS PMCCODE WITH (NOLOCK) --VAT Country Code MSTR (VAT10001)
		ON
			PMCCODE.CCode = PM00200.CCode
GO

GRANT SELECT ON uv_AZRCRV_TaxDetailTransactions TO DYNGRP
GO