percent symbol

SQL view for Fixed Allocation Accounts in Dynamics GP


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:

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 http://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.

4 Responses to “SQL view for Fixed Allocation Accounts in Dynamics GP”

  1. This is great Victoria,

    Thanks so much for sharing your knowledge – you’re the best!

    Kind regards,
    Leslie

    Like this

Trackbacks/Pingbacks

  1. SQL view for Fixed Allocation Accounts in Dynamics GP | Victoria Yudin - DynamicAccounting.net - Microsoft Dynamics GP - Microsoft Dynamics Community - January 24, 2013

    [...] Victoria has a new SQL view for Fixed Allocation Accounts in Dynamics GP [...]

    Like this

  2. SQL view for Fixed Allocation Accounts in Dynamics GP | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - January 24, 2013

    [...] Victoria has a new SQL view for Fixed Allocation Accounts in Dynamics GP [...]

    Like this

  3. SQL view for Variable Allocation Accounts in Dynamics GP | Victoria Yudin - January 23, 2013

    [...] Fixed Allocation Accounts [...]

    Like this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,361 other followers

%d bloggers like this: