Dynamics Community Forum Thread Details

If you want to have the distributions in SmartList, you need to create a new SmartList object using a view. Victoria Yudin has created the perfect view and has shared that code with everyone in the community. God bless you Victoria!

Below is a copy of her code:

CREATE VIEW view_AP_Distributions

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--view_AP_Distributions

--Shows GL distributions for all AP transactions

--Created Nov 28 2008 by Victoria Yudin, Flexible Solutions

--For updates see victoriayudin.com/gp-reports

--Does not exclude voided transactions

--Transactions with no MC information will show

-- an exchange rate of 0

--Updated Feb 11, 2009 to correct link to GL account number

--Updated May 13, 2009 to add distribution reference

--Updated Nov 10, 2009 to add GP posting date and more

-- user friendly column names

--Updated Jan 22, 2010 to add Document Date

--Updated Mar 10, 2010 to add Batch ID, Trx Description,

-- and include unposted transactions

--Updated May 23, 2010 to add Voucher Number

--Updated Jun 25, 2010 to add additional distribution types,

-- currency, exchange rate and originating amounts

--Updated Jun 27, 2010 to fix copying issue

--Updated Jun 30, 2010 to add Voided field

--Updated Dec 17, 2010 to add Vendor Name, GL Account Name

--Updated Jan 31, 2011 to add ROUND distribution type

--Updated Mar 8, 2011 to add PM10300 and PM10400 tables

--Updated Dec 20, 2011 to add PO Number

--Updated Oct 14, 2014 to fix GL posting date for Work trx

--Updated Feb 17, 2015 to add 1099 amount

--Updated Sep 14, 2017 to change DOCDATE to pull from

-- transaction tables and to clean up the code a bit

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT D.VENDORID Vendor_ID

, N.VENDNAME Vendor_Name

, K.DOCNUMBR Document_Number

, T.PSTGDATE GL_Posting_Date

, T.DOCDATE Document_Date

, CASE K.DOCTYPE

WHEN 1

THEN 'Invoice'

WHEN 2

THEN 'Finance Charge'

WHEN 3

THEN 'Misc Charge'

WHEN 4

THEN 'Return'

WHEN 5

THEN 'Credit Memo'

WHEN 6

THEN 'Payment'

END Document_Type

, G.ACTNUMST Account_Number

, A.ACTDESCR Account_Name

, CASE D.DISTTYPE

WHEN 1

THEN 'Cash'

WHEN 2

THEN 'Payable'

WHEN 3

THEN 'Discount Available'

WHEN 4

THEN 'Discount Taken'

WHEN 5

THEN 'Finance Charge'

WHEN 6

THEN 'Purchase'

WHEN 7

THEN 'Trade Disc.'

WHEN 8

THEN 'Misc. Charge'

WHEN 9

THEN 'Freight'

WHEN 10

THEN 'Taxes'

WHEN 11

THEN 'Writeoffs'

WHEN 12

THEN 'Other'

WHEN 13

THEN 'GST Disc'

WHEN 14

THEN 'PPS Amount'

WHEN 16

THEN 'Round'

WHEN 17

THEN 'Realized Gain'

WHEN 18

THEN 'Realized Loss'

WHEN 19

THEN 'Due To'

WHEN 20

THEN 'Due From'

END Distribution_Type

, D.DEBITAMT Debit_Amount

, D.CRDTAMNT Credit_Amount

, D.DistRef Distribution_Reference

, T.BACHNUMB Batch_ID

, T.TRXDSCRN Trx_Description

, T.STAT Trx_Status

, D.VCHRNMBR Voucher_Number

, D.CURNCYID Currency_ID

, coalesce(D.XCHGRATE, 0) Exchange_Rate

, D.ORDBTAMT Originating_Debit_Amount

, D.ORCRDAMT Originating_Credit_Amount

, CASE T.VOIDED

WHEN 0

THEN 'No'

WHEN 1

THEN 'Yes'

END Voided

, T.PORDNMBR PO_Number

, T.TEN99AMNT [1099_Amount]

FROM

-- all open trx distributions

(

SELECT VENDORID

, VCHRNMBR

, CNTRLTYP

, DEBITAMT

, CRDTAMNT

, DSTINDX

, DISTTYPE

, DistRef

, PSTGDATE

, CURNCYID

, ORDBTAMT

, ORCRDAMT

, XCHGRATE

FROM PM10100

UNION ALL

-- all historical trx distributions

SELECT P.VENDORID

, P.VCHRNMBR

, P.CNTRLTYP

, P.DEBITAMT

, P.CRDTAMNT

, P.DSTINDX

, P.DISTTYPE

, P.DistRef

, P.PSTGDATE

, P.CURNCYID

, P.ORDBTAMT

, P.ORCRDAMT

, M.XCHGRATE

FROM PM30600 P

LEFT JOIN MC020103 M -- historical exchange rate

ON P.VCHRNMBR = M.VCHRNMBR

AND P.DOCTYPE = M.DOCTYPE

) D

-- add document number and type

LEFT JOIN PM00400 K ON D.VCHRNMBR = K.CNTRLNUM

AND D.CNTRLTYP = K.CNTRLTYP

--add GL account number

LEFT JOIN GL00105 G ON D.DSTINDX = G.ACTINDX

--add status, batch, GL/doc dates and trx description

LEFT JOIN (

SELECT VCHRNMBR

, CNTRLTYP

, BACHNUMB

, TRXDSCRN

, DOCDATE

, STAT = 'Work'

, VOIDED = 0

, PORDNMBR

, PSTGDATE

, TEN99AMNT

FROM PM10000

UNION

SELECT VCHRNMBR

, CNTRLTYP

, BACHNUMB

, TRXDSCRN

, DOCDATE

, STAT = 'Open'

, VOIDED

, PORDNMBR

, PSTGDATE

, TEN99AMNT

FROM PM20000

UNION

SELECT VCHRNMBR

, CNTRLTYP

, BACHNUMB

, TRXDSCRN

, DOCDATE

, STAT = 'History'

, VOIDED

, PORDNMBR

, PSTGDATE

, TEN99AMNT

FROM PM30200

UNION

SELECT PMNTNMBR

, CNTRLTYP = 1

, BACHNUMB

, COMMENT1

, DOCDATE

, STAT = 'Work'

, VOIDED = 0

, PORDNMBR = ''

, PSTGDATE

, 0

FROM PM10300

UNION

SELECT PMNTNMBR

, CNTRLTYP = 1

, BACHNUMB

, TRXDSCRN

, DOCDATE

, STAT = 'Work'

, VOIDED = 0

, PORDNMBR = ''

, PSTGDATE

, 0

FROM PM10400

) T ON T.VCHRNMBR = D.VCHRNMBR

AND T.CNTRLTYP = D.CNTRLTYP

--add GL account name

LEFT JOIN GL00100 A ON A.ACTINDX = D.DSTINDX

--add vendor name

LEFT JOIN PM00200 N ON N.VENDORID = D.VENDORID

--the following will grant permissions to this view to DYNGRP

--leave this section off if you do not want to grant permissions

GRANT SELECT

ON view_AP_Distributions

TO DYNGRP

Is this what you wanted?

Kind regards,

Leslie

smart list

Helpful resources

News and Announcements

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates

smart list

Helpful resources

News and Announcements

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Subscribe to

Leaderboard

Featured topics

Product updates