Dynamics Community Forum Thread Details

SP View Vendor Class

Use this query to make Smartlist Vendor Class Account Distribution

Create View PNI_Vendor_Class
as
select a.VNDCLSID as 'Class ID'
,a.VNDCLDSC as 'Class ID Description'

, COALESCE(b.ACTNUMST,'') 'Account Payable COA'

, COALESCE(c.ACTNUMST,'') 'Terms Discount Available'

, COALESCE(d.ACTNUMST,'') 'Terms Discount Taken'

, COALESCE(e.ACTNUMST,'') 'Finance Charges'

, COALESCE(f.ACTNUMST,'') 'Purchases'

, COALESCE(g.ACTNUMST,'') 'Trade Discount'

, COALESCE(h.ACTNUMST,'') 'Miscellaneous'

, COALESCE(i.ACTNUMST,'') 'Freight'

, COALESCE(j.ACTNUMST,'') 'Tax'

, COALESCE(k.ACTNUMST,'') 'Writeoffs'

, COALESCE(l.ACTNUMST,'') 'Accrued Purchases'

, COALESCE(m.ACTNUMST,'') 'Purchase Price Variance'

from PM00100 a
left join GL00105 b on a.PMAPINDX = b.ACTINDX
left join GL00105 c on a.PMDAVIDX = c.ACTINDX
left join GL00105 d on a.PMDTKIDX = d.ACTINDX
left join GL00105 e on a.PMFINIDX = e.ACTINDX
left join GL00105 f on a.PMPRCHIX = f.ACTINDX
left join GL00105 g on a.PMTDSCIX = g.ACTINDX
left join GL00105 h on a.PMMSCHIX = h.ACTINDX
left join GL00105 i on a.PMFRTIDX = i.ACTINDX
left join GL00105 j on a.PMTAXIDX = j.ACTINDX
left join GL00105 k on a.PMWRTIDX = k.ACTINDX
left join GL00105 l on a.ACPURIDX = l.ACTINDX
left join GL00105 m on a.PURPVIDX = m.ACTINDX