3d small people - team with the puzzles

SQL view for Variable Allocation Accounts in Dynamics GP


Below is a view that will return a list of the distribution and breakdown accounts for all active variable 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, specially since you have to click on each distribution account one at a time to see what breakdown account they are using.

Related code and table information:

create view view_Variable_Allocation_Accounts
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Variable_Allocation_Accounts
-- Created on Aug. 23, 2012 by Victoria Yudin - Flexible Solutions, Inc.
-- For updates please see http://victoriayudin.com/gp-reports/
-- Shows only active variable allocation accounts
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
an.ACTNUMST Variable_Allocation_Account,
a.ACTDESCR Variable_Allocation_Account_Name,
d.ACTNUMST Distribution_Account,
dn.ACTDESCR Distribution_Account_Name,
b.ACTNUMST Breakdown_Account,
bn.ACTDESCR Breakdown_Account_Name

from GL00100 a --account master

inner join GL00105 an --account number
on a.ACTINDX = an.ACTINDX

inner join GL00104 v --variable allocation account setup
on a.ACTINDX = v.ACTINDX

inner join GL00105 d --distribution acct number
on v.DSTINDX = d.ACTINDX

inner join GL00100 dn --distribution account name
on v.DSTINDX = dn.ACTINDX

inner join GL00105 b --breakdown account number
on v.BDNINDX = b.ACTINDX

inner join GL00100 bn --breakdown account name
on v.BDNINDX = bn.ACTINDX

where a.ACCTTYPE = 3 and a.ACTIVE = 1

go
grant select on view_Variable_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.

Trackbacks/Pingbacks

  1. SQL view for Fixed Allocation Accounts in Dynamics GP – 1/23, Victoria Yudin | Partner Compete - January 23, 2013

    […] Variable Allocation Accounts […]

    Like

  2. SQL view for Fixed Allocation Accounts in Dynamics GP : Interesting Findings & Knowledge Sharing - January 23, 2013

    […] Variable Allocation Accounts […]

    Like

  3. SQL view for Fixed Allocation Accounts in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - January 23, 2013

    […] Variable Allocation Accounts […]

    Like

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

    […] Variable Allocation Accounts […]

    Like

  5. SQL view for Variable Allocation Accounts in Dynamics GP | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - August 27, 2012

    […] Comments 0 Victoria Yuding gives us a SQL view for Variable Allocation Accounts in Dynamics GP […]

    Like

  6. SQL view for Variable Allocation Accounts in Dynamics GP : Interesting Findings & Knowledge Sharing - August 23, 2012

    […] from: SQL view for Variable Allocation Accounts in Dynamics GP VN:F [1.9.20_1166]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F […]

    Like

  7. SQL view for Variable Allocation Accounts in Dynamics GP – 8/23, Victoria Yudin | Partner Compete - August 23, 2012

    […] Continue reading on Source Blog […]

    Like

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,626 other followers

%d bloggers like this: