Below is a view that will return a list of the distribution accounts and percentages for all active fixed allocation accounts in your Dynamics GP. Nothing fancy, but sometimes it is easier to have a report of these rather than have to look at them on the screen, especially when you have a lot of them.
Related code and table information:
- General Ledger tables
- Variable Allocation Accounts
- GP Reports (there is a section for General Ledger reports under Dynamics GP SQL Scripts)
create view view_Fixed_Allocation_Accounts as -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- view_Fixed_Allocation_Accounts -- Created on Jan. 23, 2013 by Victoria Yudin - Flexible Solutions, Inc. -- For updates please see https://victoriayudin.com/gp-reports/ -- Shows only active fixed allocation accounts -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ select an.ACTNUMST Fixed_Allocation_Account, a.ACTDESCR Fixed_Allocation_Account_Name, da.ACTNUMST Distribution_Account, dn.ACTDESCR Distribution_Account_Name, f.PRCNTAGE Distribution_Percentage from GL00100 a --account master inner join GL00105 an --account number on a.ACTINDX = an.ACTINDX inner join GL00103 f --fixed allocation account setup on a.ACTINDX = f.ACTINDX inner join GL00105 da --distribution account on f.DSTINDX = da.ACTINDX inner join GL00100 dn --distribution account name on f.DSTINDX = dn.ACTINDX where a.ACCTTYPE = 3 and a.ACTIVE = 1 and a.ACTINDX not in (select ACTINDX from GL00104) go grant select on view_Fixed_Allocation_Accounts to DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
This is great Victoria,
Thanks so much for sharing your knowledge – you’re the best!
Kind regards,
Leslie
LikeLike