One 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.
1 thought on “SQL View For Payables Transaction Distribution Accounts”