# Blog

Keep up to date with the latest information about Microsfot Dynamics GP through articles from the Perfect Image and azurecurve blog.

## SQL View For Payables Transaction Distribution Accounts

November 4th, 2015 by Ian Grieve

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
```

Source: azurecurve