SQL view for Variable Allocation Accounts in Dynamics GP

August 23, 2012 by Victoria Yudin 10 Comments

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:

General Ledger tables
Fixed Allocation Accounts
GP Reports (there is a section for General Ledger reports under Dynamics GP SQL Scripts)

create view view_Variable_Allocation_Accounts
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Variable_Allocation_Accounts
-- Created Aug 23, 2012 by Victoria Yudin - Flexible Solutions, Inc
-- For updates please see https://victoriayudin.com/gp-reports/
-- Shows only active variable allocation accounts
-- Updated Oct 22, 2016 to add Balance Calc 
--     (thanks to Sheila Jefferson-Ross!)
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

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,
case a.BALFRCLC
   when 0 then 'YTD'
   when 1 then 'PERIOD'
   else 'ERROR'
   end Balance_Calc

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.

Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts

General Ledger, GP Reports code, SQL code

SQL view for commissions details in Dynamics GP

SQL view for rolling 12 months of sales by item in Dynamics GP

10 Responses to “SQL view for Variable Allocation Accounts in Dynamics GP”

Sheila Jefferson-Ross Reply October 21, 2016 at 1:34 pm

Great post Victoria. I haven’t met you in person but it seems we are always talking about you at events such as GPUG or reIMAGINE. Of course nothing but good things are said. 🙂

I just used this script for variable accounts but added a section to pull the ‘Balance for Calculation’ field. This way they could also see if it was set to YTD or Trx Period calculation.

CASE a.BALFRCLC
WHEN 0 THEN ‘YTD’
WHEN 1 THEN ‘PERIOD’
ELSE ‘ERROR’
END AS Balance_Calc,

Thanks again for the starting point.
Sheila Jefferson-Ross

LikeLike
- Victoria Yudin Reply October 22, 2016 at 8:52 am
  
  Thanks Sheila!
  
  I really appreciate the kind words and also this idea! I have added your code to the view.
  
  -Victoria
  
  LikeLike
  - Sheila Jefferson-Ross Reply October 22, 2016 at 8:16 pm
    
    Great! Glad to contribute.
    Sheila
    
    LikeLike