SQL View For Payables Transaction Distribution Accounts

Microsoft Dynamics GPOne of the odd limitations in SmartList is the inability to run a report and get the distribution accounts for payables transactions; there is a field available for the different distributions, but these fields are from the Vendor Card, not the transaction. This view was the result of a query from someone; it returns the account number and description for the payables distributions.

CREATE VIEW uv_AZRCRV_PayablesTransactionDistributionAccounts AS

SELECT
	['PM Distribution WORK OPEN HIST'].VCHRNMBR AS 'Voucher Number'
	,CASE WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 1 THEN
		'Cash'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 2 THEN
		'Payable'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 3 THEN
		'Discount Available'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 4 THEN
		'Discount Taken'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 5 THEN
		'Finance Charge'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 6 THEN
		'Purchase'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 7 THEN
		'Trade Discount'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 8 THEN
		'Miscellaneous Charge'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 9 THEN
		'Freight'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 10 THEN
		'Taxes'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 11 THEN
		'Writeoffs'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 12 THEN
		'Other'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 13 THEN
		'GST Disc'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 14 THEN
		'PPS Amount'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 16 THEN
		'Round'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 17 THEN
		'Realized Gain'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 18 THEN
		'Realized Loss'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 19 THEN
		'Due To'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 20 THEN
		'Due From'
	END AS 'Distribution Type'
	,['PM Distribution WORK OPEN HIST'].DistRef AS 'Distribution Reference'
	,['Account Index Master'].ACTNUMST AS 'Account Number'
	,['Account Master'].ACTDESCR AS 'Account Description'
FROM
	(SELECT
		VCHRNMBR
		,DISTTYPE
		,DSTINDX
		,DistRef
	FROM
		PM10100 AS ['PM Distribution WORK OPEN HIST']
	UNION ALL
		SELECT
			VCHRNMBR
			,DISTTYPE
			,DSTINDX
			,DistRef
		FROM
			PM30600 AS ['PM Distribution History File']
	) AS ['PM Distribution WORK OPEN HIST']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON ['Account Index Master'].ACTINDX = ['PM Distribution WORK OPEN HIST'].DSTINDX
INNER JOIN
	GL00100 AS ['Account Master']
		ON ['Account Master'].ACTINDX = ['Account Index Master'].ACTINDX
GO

GRANT SELECT ON uv_AZRCRV_PayablesTransactionDistributionAccounts TO DYNGRP
GO

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.
Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?

Your Name (required) -
Your Email (required) -

1 thought on “SQL View For Payables Transaction Distribution Accounts

Leave a Reply

Your email address will not be published. Required fields are marked *