Archive for January 23, 2013

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 these.

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.

Filed under: Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts Tagged: featured, General Ledger, GP Reports code, SQL code

View article:
SQL view for Fixed Allocation Accounts in Dynamics GP

January 23, 2013 · Victoria Yudin · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 842

Interesting Info for Partners

In this article, I have decided to post some interesting information which will help partners reach out to the correct resource for the right information. For Operations Support In order to reach the Sales Operations Team, please call 1800-456-0025 and select Option 2.

See more here:
Interesting Info for Partners

January 23, 2013 · Sivakumar V · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 158