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

Read original post SQL Scripts for Microsoft Dynamics GP: List Tax Detail Transactions at azurecurve|Ramblings of an IT Professional