Vendor Statement with Analytical Accounting Information

 

one of my clients is a constructions firm whose not interested to activate the project accounting and wanted to distribute their project management over analytical accounting dimensions.

Therefore we have implemented Dynamics GP and created dimension for projects to distribute all expenses accounts of analytical accounting codes.

One of their requirements been to have a vendor statement per project which is reasonable for such a line of business, therefore I have created the below view that extracts the information including from AP, GL and AA and wanted to share the idea to avoid reinventing the weel for people who works with the same situation, below is the script I used:

DECLARE @FROMDATE    DATETIME
DECLARE
@TODATE DATETIME
DECLARE
@FROMVENDOR VARCHAR(MAX)
DECLARE @TOVENDOR VARCHAR(MAX)

SET @FROMDATE = '1900-01-01'
SET @TODATE = '2017-04-17'
SET @FROMVENDOR = 'ACETRAVE0001'
SET @TOVENDOR = 'BLOOMING0001'

SELECT
'Beginning Balance' AS TRXSOURCE,
@FROMDATE AS DOCDATE,
'Beginning Balance' AS DOCNUMBER,
VENDORID,
SUM(CREDIT) AS CREDIT,
SUM(DEBIT) AS DEBIT,
VENDNAME,
'Beginning Balance' AS VCHRNMBR,
'Beginning Balance' AS TRXDESC,
0 AS JRNENTRY,
0 AS aaGLHdrID,
aaTrxDimCode,
aaTrxDimCodeDescr,
'' AS LOCNCODE
FROM
(
SELECT DISTINCT
VENDORTRANSACTION.TRXSOURCE,
VENDORTRANSACTION.DOCDATE,
VENDORTRANSACTION.DOCNUMBER,
VENDORTRANSACTION.VENDORID,
AAG3000240002.CRDTAMNT AS CREDIT,
AAG3000240002.DEBITAMT AS DEBIT,
VENDORTRANSACTION.VENDNAME,
VENDORTRANSACTION.VCHRNMBR,
VENDORTRANSACTION.TRXDESC,
GL2000030000.JRNENTRY,
dbo.AAG30000.aaGLHdrID,
dbo.AAG00401.aaTrxDimCode,
dbo.AAG00401.aaTrxDimCodeDescr,
POP30310.LOCNCODE

FROM
(SELECT AAG30002.DEBITAMT, AAG30002.CRDTAMNT, AAG30002.DistRef, AAG30002.aaGLDistID, AAG30002.aaGLHdrID, aaGLASsignID FROM AAG30002
UNION
SELECT
AAG40002.DEBITAMT, AAG40002.CRDTAMNT, AAG40002.DistRef, AAG40002.aaGLDistID, AAG40002.aaGLHdrID, aaGLASsignID FROM AAG40002
) AS AAG3000240002
INNER JOIN
(
SELECT AAG30003.aaGLDistID, AAG30003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG30003
UNION
SELECT
AAG40003.aaGLDistID, AAG40003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG40003
) AS AAG3000340003
ON AAG3000240002.aaGLHdrID = AAG3000340003.aaGLHdrID AND AAG3000240002.aaGLDistID = AAG3000340003.aaGLDistID AND
AAG3000240002.aaGLASsignID = AAG3000340003.aaGLASsignID LEFT OUTER JOIN
dbo.AAG00401 ON AAG3000340003.aaTrxDimID = dbo.AAG00401.aaTrxDimID AND AAG3000340003.aaTrxCodeID = dbo.AAG00401.aaTrxDimCodeID
RIGHT OUTER JOIN
(
SELECT aaGLHdrID, aaGLDistID FROM AAG30001
UNION
SELECT
aaGLHdrID, aaGLDistID FROM AAG40001
) AS AAG3000140001
ON AAG3000240002.aaGLHdrID = AAG3000140001.aaGLHdrID AND AAG3000240002.aaGLDistID = AAG3000140001.aaGLDistID
RIGHT OUTER JOIN
dbo.AAG30000 ON AAG3000140001.aaGLHdrID = dbo.AAG30000.aaGLHdrID
RIGHT OUTER JOIN
(
SELECT
CASE
WHEN
dbo.PM20000.DOCTYPE = 1 THEN 'Invoice'
WHEN dbo.PM20000.DOCTYPE = 2 THEN 'Finance Charges'
WHEN dbo.PM20000.DOCTYPE = 3 THEN 'Mis Charges'
WHEN dbo.PM20000.DOCTYPE = 4 THEN 'Return'
WHEN dbo.PM20000.DOCTYPE = 5 THEN 'Credit Memo'
WHEN dbo.PM20000.DOCTYPE = 6 THEN 'Payment'
END
AS
TRXSOURCE,
'Open' AS Status,

CASE
WHEN
dbo.PM20000.TRXDSCRN = '' THEN '-'
ELSE dbo.PM20000.TRXDSCRN
END
AS
TRXDESC,
dbo.PM20000.DOCDATE,
dbo.PM20000.VCHRNMBR,
dbo.PM20000.VENDORID,
ISNULL(CASE
WHEN
dbo.PM20000.DOCTYPE = 1 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE = 2 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE = 3 THEN dbo.PM20000.DOCAMNT
END, 0)
AS Credit,
ISNULL(CASE
WHEN
dbo.PM20000.DOCTYPE = 4 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE = 5 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE = 6 THEN dbo.PM20000.DOCAMNT
END, 0)
AS Debit,
(
SELECT VENDNAME FROM dbo.PM00200 WHERE (VENDORID = dbo.PM20000.VENDORID)) AS VENDNAME,
dbo.PM20000.CURNCYID,
dbo.PM20000.DOCNUMBR AS DOCNUMBER,
ISNULL(CASE
WHEN
dbo.PM20000.DOCTYPE = 1 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT)
WHEN dbo.PM20000.DOCTYPE = 2 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT)
WHEN dbo.PM20000.DOCTYPE = 3 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT)
END, 0)
AS ORCREDIT,
ISNULL(CASE
WHEN
dbo.PM20000.DOCTYPE = 4 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT)
WHEN dbo.PM20000.DOCTYPE = 5 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT)
WHEN dbo.PM20000.DOCTYPE = 6 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT)
END, 0)
AS ORDEBIT
FROM dbo.PM20000
LEFT OUTER JOIN
dbo.MC020103 ON dbo.PM20000.DOCTYPE = dbo.MC020103.DOCTYPE AND dbo.PM20000.VCHRNMBR = dbo.MC020103.VCHRNMBR
WHERE (dbo.PM20000.VOIDED <> 1)

UNION ALL

SELECT CASE
WHEN
dbo.PM30200.DOCTYPE = 1 THEN 'Invoice'
WHEN dbo.PM30200.DOCTYPE = 2 THEN 'Finance Charges'
WHEN dbo.PM30200.DOCTYPE = 3 THEN 'Mis Charges'
WHEN dbo.PM30200.DOCTYPE = 4 THEN 'Return'
WHEN dbo.PM30200.DOCTYPE = 5 THEN 'Credit Memo'
WHEN dbo.PM30200.DOCTYPE = 6 THEN 'Payment'
END
AS
TRXSOURCE,
'History' AS Status,

CASE
WHEN
dbo.PM30200.TRXDSCRN = '' THEN '-'
ELSE dbo.PM30200.TRXDSCRN
END
AS
TRXDESC,
DOCDATE,
VCHRNMBR,
VENDORID,

ISNULL(CASE
WHEN
dbo.PM30200.DOCTYPE = 1 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE = 2 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE = 3 THEN dbo.PM30200.DOCAMNT
END, 0)
AS Credit,
ISNULL(CASE
WHEN
dbo.PM30200.DOCTYPE = 4 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE = 5 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE = 6 THEN dbo.PM30200.DOCAMNT
END, 0)
AS Debit,
(
SELECT VENDNAME FROM dbo.PM00200 AS PM00200_1 WHERE (VENDORID = dbo.PM30200.VENDORID)) AS VENDNAME,
CURNCYID,
DOCNUMBR,
ISNULL(CASE
WHEN
dbo.PM30200.DOCTYPE = 1 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE = 2 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE = 3 THEN dbo.PM30200.DOCAMNT
END, 0)
AS ORCREDIT,
ISNULL(CASE
WHEN
dbo.PM30200.DOCTYPE = 4 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE = 5 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE = 6 THEN dbo.PM30200.DOCAMNT
END, 0)
AS ORDEBIT
FROM dbo.PM30200
WHERE (VOIDED <> 1)) AS VENDORTRANSACTION
LEFT OUTER JOIN dbo.POP30310
INNER JOIN dbo.POP30300
ON dbo.POP30310.POPRCTNM = dbo.POP30300.POPRCTNM
ON VENDORTRANSACTION.VCHRNMBR = dbo.POP30300.VCHRNMBR
LEFT OUTER JOIN
(
SELECT JRNENTRY, ORCTRNUM FROM GL20000
UNION
SELECT
JRNENTRY, ORCTRNUM FROM GL30000)
AS GL2000030000
ON dbo.POP30300.POPRCTNM = GL2000030000.ORCTRNUM OR VENDORTRANSACTION.VCHRNMBR = GL2000030000.ORCTRNUM ON
dbo.AAG30000.JRNENTRY = GL2000030000.JRNENTRY

WHERE (VENDORTRANSACTION.VENDORID >= @FROMVENDOR) AND (VENDORTRANSACTION.VENDORID <= @TOVENDOR) AND(AAG3000340003.aaTrxDimID = 3) AND VENDORTRANSACTION.DOCDATE <@FROMDATE
) AS SOURCE
GROUP BY
VENDORID,
VENDNAME,
aaTrxDimCode,
aaTrxDimCodeDescr

UNION ALL

SELECT DISTINCT
VENDORTRANSACTION.TRXSOURCE,
VENDORTRANSACTION.DOCDATE,
VENDORTRANSACTION.DOCNUMBER,
VENDORTRANSACTION.VENDORID,
AAG3000240002.CRDTAMNT AS CREDIT,
AAG3000240002.DEBITAMT AS DEBIT,
VENDORTRANSACTION.VENDNAME,
VENDORTRANSACTION.VCHRNMBR,
VENDORTRANSACTION.TRXDESC,
GL2000030000.JRNENTRY,
dbo.AAG30000.aaGLHdrID,
dbo.AAG00401.aaTrxDimCode,
dbo.AAG00401.aaTrxDimCodeDescr,
POP30310.LOCNCODE
FROM
(SELECT AAG30002.DEBITAMT, AAG30002.CRDTAMNT, AAG30002.DistRef, AAG30002.aaGLDistID, AAG30002.aaGLHdrID, aaGLASsignID FROM AAG30002
UNION
SELECT
AAG40002.DEBITAMT, AAG40002.CRDTAMNT, AAG40002.DistRef, AAG40002.aaGLDistID, AAG40002.aaGLHdrID, aaGLASsignID FROM AAG40002
) AS AAG3000240002

INNER JOIN
(
SELECT AAG30003.aaGLDistID, AAG30003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG30003
UNION
SELECT
AAG40003.aaGLDistID, AAG40003.aaGLHdrID, aaGLASsignID, aaTrxDimID, aaTrxCodeID FROM AAG40003) AS AAG3000340003
ON AAG3000240002.aaGLHdrID = AAG3000340003.aaGLHdrID
AND AAG3000240002.aaGLDistID = AAG3000340003.aaGLDistID
AND AAG3000240002.aaGLASsignID = AAG3000340003.aaGLASsignID
LEFT OUTER JOIN
dbo.AAG00401 ON AAG3000340003.aaTrxDimID = dbo.AAG00401.aaTrxDimID
AND AAG3000340003.aaTrxCodeID = dbo.AAG00401.aaTrxDimCodeID
RIGHT OUTER JOIN
(
SELECT aaGLHdrID, aaGLDistID FROM AAG30001
UNION
SELECT
aaGLHdrID, aaGLDistID FROM AAG40001
) AS AAG3000140001
ON AAG3000240002.aaGLHdrID = AAG3000140001.aaGLHdrID AND AAG3000240002.aaGLDistID = AAG3000140001.aaGLDistID
RIGHT OUTER JOIN dbo.AAG30000
ON AAG3000140001.aaGLHdrID = dbo.AAG30000.aaGLHdrID
RIGHT OUTER JOIN
(
SELECT CASE WHEN dbo.PM20000.DOCTYPE = 1 THEN 'Invoice' WHEN dbo.PM20000.DOCTYPE = 2 THEN 'Finance Charges' WHEN dbo.PM20000.DOCTYPE = 3
THEN 'Mis Charges' WHEN dbo.PM20000.DOCTYPE = 4 THEN 'Return' WHEN dbo.PM20000.DOCTYPE = 5 THEN 'Credit Memo' WHEN dbo.PM20000.DOCTYPE
= 6 THEN 'Payment' END AS TRXSOURCE, 'Open' AS Status,
CASE WHEN dbo.PM20000.TRXDSCRN = '' THEN '-' ELSE dbo.PM20000.TRXDSCRN END AS TRXDESC, dbo.PM20000.DOCDATE,
dbo.PM20000.VCHRNMBR, dbo.PM20000.VENDORID,
ISNULL(CASE WHEN dbo.PM20000.DOCTYPE = 1 THEN dbo.PM20000.DOCAMNT WHEN dbo.PM20000.DOCTYPE = 2 THEN dbo.PM20000.DOCAMNT WHEN
dbo.PM20000.DOCTYPE = 3 THEN dbo.PM20000.DOCAMNT END, 0) AS Credit,
ISNULL(CASE WHEN dbo.PM20000.DOCTYPE = 4 THEN dbo.PM20000.DOCAMNT WHEN dbo.PM20000.DOCTYPE = 5 THEN dbo.PM20000.DOCAMNT WHEN
dbo.PM20000.DOCTYPE = 6 THEN dbo.PM20000.DOCAMNT END, 0) AS Debit,
(
SELECT VENDNAME
FROM dbo.PM00200
WHERE (VENDORID = dbo.PM20000.VENDORID)) AS VENDNAME, dbo.PM20000.CURNCYID, dbo.PM20000.DOCNUMBR AS DOCNUMBER,
ISNULL(CASE WHEN dbo.PM20000.DOCTYPE = 1 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT)
WHEN dbo.PM20000.DOCTYPE = 2 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT)
WHEN dbo.PM20000.DOCTYPE = 3 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT) END, 0) AS OrCredit,
ISNULL(CASE WHEN dbo.PM20000.DOCTYPE = 4 THEN ISNULL(dbo.MC020103.ORDOCAMT, dbo.PM20000.DOCAMNT)
WHEN dbo.PM20000.DOCTYPE = 5 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT)
WHEN dbo.PM20000.DOCTYPE = 6 THEN ISNULL(dbo.MC020103.ORDOCAMT, PM20000.DOCAMNT) END, 0) AS OrDebit
FROM dbo.PM20000 LEFT OUTER JOIN
dbo.MC020103 ON dbo.PM20000.DOCTYPE = dbo.MC020103.DOCTYPE AND dbo.PM20000.VCHRNMBR = dbo.MC020103.VCHRNMBR
WHERE (dbo.PM20000.VOIDED <> 1)
UNION ALL
SELECT CASE WHEN dbo.PM30200.DOCTYPE = 1 THEN 'Invoice' WHEN dbo.PM30200.DOCTYPE = 2 THEN 'Finance Charges' WHEN dbo.PM30200.DOCTYPE = 3
THEN 'Mis Charges' WHEN dbo.PM30200.DOCTYPE = 4 THEN 'Return' WHEN dbo.PM30200.DOCTYPE = 5 THEN 'Credit Memo' WHEN dbo.PM30200.DOCTYPE
= 6 THEN 'Payment' END AS TRXSOURCE, 'History' AS Status,
CASE WHEN dbo.PM30200.TRXDSCRN = '' THEN '-' ELSE dbo.PM30200.TRXDSCRN END AS TRXDESC, DOCDATE, VCHRNMBR, VENDORID,
ISNULL(CASE WHEN dbo.PM30200.DOCTYPE = 1 THEN dbo.PM30200.DOCAMNT WHEN dbo.PM30200.DOCTYPE = 2 THEN dbo.PM30200.DOCAMNT WHEN
dbo.PM30200.DOCTYPE = 3 THEN dbo.PM30200.DOCAMNT END, 0) AS Credit,
ISNULL(CASE WHEN dbo.PM30200.DOCTYPE = 4 THEN dbo.PM30200.DOCAMNT WHEN dbo.PM30200.DOCTYPE = 5 THEN dbo.PM30200.DOCAMNT WHEN
dbo.PM30200.DOCTYPE = 6 THEN dbo.PM30200.DOCAMNT END, 0) AS Debit,
(
SELECT VENDNAME
FROM dbo.PM00200 AS PM00200_1
WHERE (VENDORID = dbo.PM30200.VENDORID)) AS VENDNAME, CURNCYID, DOCNUMBR AS DOCNUMBER,
ISNULL(CASE WHEN dbo.PM30200.DOCTYPE = 1 THEN dbo.PM30200.DOCAMNT WHEN dbo.PM30200.DOCTYPE = 2 THEN dbo.PM30200.DOCAMNT WHEN
dbo.PM30200.DOCTYPE = 3 THEN dbo.PM30200.DOCAMNT END, 0) AS OrCredit,
ISNULL(CASE WHEN dbo.PM30200.DOCTYPE = 4 THEN dbo.PM30200.DOCAMNT WHEN dbo.PM30200.DOCTYPE = 5 THEN dbo.PM30200.DOCAMNT WHEN
dbo.PM30200.DOCTYPE = 6 THEN dbo.PM30200.DOCAMNT END, 0) AS OrDebit
FROM dbo.PM30200
WHERE (VOIDED <> 1)) AS VENDORTRANSACTION LEFT OUTER JOIN
dbo.POP30310 INNER JOIN
dbo.POP30300 ON dbo.POP30310.POPRCTNM = dbo.POP30300.POPRCTNM ON VENDORTRANSACTION.VCHRNMBR = dbo.POP30300.VCHRNMBR LEFT OUTER JOIN
(
SELECT JRNENTRY, ORCTRNUM FROM GL20000
UNION
SELECT
JRNENTRY, ORCTRNUM FROM GL30000)
AS GL2000030000
ON dbo.POP30300.POPRCTNM = GL2000030000.ORCTRNUM OR VENDORTRANSACTION.VCHRNMBR = GL2000030000.ORCTRNUM ON
dbo.AAG30000.JRNENTRY = GL2000030000.JRNENTRY
WHERE (VENDORTRANSACTION.VENDORID > = @FROMVENDOR) AND (VENDORTRANSACTION.VENDORID < = @TOVENDOR) AND (AAG3000340003.aaTrxDimID = 3) AND
VENDORTRANSACTION.DOCDATE > = @FROMDATE AND VENDORTRANSACTION.DOCDATE <= @TODATE
ORDER BY DOCDATE

Hope that this helps.


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Advertisements

Microsoft Dynamics GP Analytical Accounting Vs. Segments

 

I found an interesting question in the community asking what is the actual difference between analytical accounting and account segmentation? Below are the details:

“What are the real advantages of using Analytical Accounting over using segments in a chart of accounts? I have configured many, many GP implementations in the past, and have always used the account structure for segmenting departments, cost centers, locations, etc. One of the nice features about the Dimensions is that they can be a Yes/No, which is helpful for one of the types of information we need to track.”

My Answer:

Basically you need to keep in mind that nothing in AA cannot be done in dimensions, but it is a matter of how and what does it take, take the below scenario as an example:

Lets say that you have 10 Expenses accounts need to be allocated over 10 cost centers, and one of the expenses accounts is related to “Cars” that requires 10 subaccounts to identify expense type and another 50 accounts to identify “Cars”, do the calculation and check how many accounts will need to be added to your chart:

· Cost Center 1>> Expense Account 1>> Car 1 >> Oil Expenses

· Cost Center 1>> Expense Account 1>> Car 1 >> Cleaning Expenses

· Cost Center 1>> Expense Account 1>> Car 1 >> Maintenance Expenses

· Cost Center 1>> Expense Account 1>> Car 2 >> Oil Expenses

· Cost Center 1>> Expense Account 1>> Car 2 >> Cleaning Expenses

· Cost Center 1>> Expense Account 1>> Car 2 >> Maintenance Expenses

10 Expenses Accounts * 10 Cost Centers * 50 Cars * 10 Cars related Expenses Types= 50,000 Account! And calculate how much it takes to add a new expense type.

In AA all what you have to do is the below:

1. Add 10 Expenses accounts to your chart.

2. Create Dimension for cars.

3. Create Dimension for expenses types.

4. Enjoy!

This is the actual difference between traditional accounting and analytical accounting.

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

SQL View to extract Analytical Accounting Budget Data With Accounts in Dynamics GP

Have you ever wanted to extract your Analytical Accounting Budgets that were linked to accounts and dimensions from the database? I been in this situation today where the client wanted to export his budget in the following format:

image

Script below will do the task for you:

SELECT DISTINCT
                      dbo.AAG00903.YEAR1 AS [Budget Year], dbo.AAG00401.aaTrxDimCode AS [Dimension Code], dbo.AAG00401.aaTrxDimCodeDescr AS [Dimension Description],
                      dbo.GL00105.ACTNUMST AS [Account Number], dbo.GL00100.ACTDESCR AS [Account Description], dbo.aagMLFiscalView.aaFiscalPeriod AS Period,
                      dbo.AAG00905.Balance AS Budget
FROM         dbo.GL00100 INNER JOIN
                      dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
                      dbo.AAG00903 INNER JOIN
                      dbo.aagMLFiscalView ON dbo.AAG00903.YEAR1 = dbo.aagMLFiscalView.aaFiscalYear INNER JOIN
                      dbo.AAG00902 ON dbo.AAG00903.aaBudgetTreeID = dbo.AAG00902.aaBudgetTreeID INNER JOIN
                      dbo.AAG00901 ON dbo.AAG00902.aaBudgetTreeID = dbo.AAG00901.aaBudgetTreeID INNER JOIN
                      dbo.AAG00401 ON dbo.AAG00901.aaTrxDimID = dbo.AAG00401.aaTrxDimID AND dbo.AAG00902.aaTrxDimCodeID = dbo.AAG00401.aaTrxDimCodeID INNER JOIN
                      dbo.AAG00905 ON dbo.AAG00903.aaBudgetID = dbo.AAG00905.aaBudgetID AND dbo.AAG00902.aaCodeSequence = dbo.AAG00905.aaCodeSequence AND
                      dbo.aagMLFiscalView.aaFiscalPeriod = dbo.AAG00905.aaFiscalPeriod ON dbo.GL00100.ACTINDX = dbo.AAG00905.ACTINDX
WHERE     (dbo.AAG00902.aaCodeSequence <> 1) AND (dbo.AAG00905.Balance <> 0)

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
me@mohdaoud.com
www.mohdaoud.com

Analytical Accounting has not been installed. Access denied.

Vaidy posted a script to resolve this error, he’s enforcing GP to accept the fact that AA is installed, checkout his article here.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Auto grant access to users on newly added Analytical Accounting Codes

This feature is already added to GP 2010, but if you are looking to have such feature in your current GP, you might need to take a look at this macro generator.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

View for Account Payable Transactions with Analytical Accounting Information

If you are looking to combine such information in smart list builder, take a look to this article, it will save you allot of time.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Integrating FRx With Analytical Accounting

Guys,

Last few days, I been working with my colleague Ms. Samia  on integrating the the Analytical Accounting Dimensions with FRx Reports, actually it was not an easy task as expected but the results was Great 🙂

Basically as a start we followed the documents and worked on the steps below:

1. On FRx CD, we have installed the AddIn located under “Analytical Accounting Add-in\FRx 6.7 Add-In for Microsoft Dynamics GP Analytical Accounti.msi”.

2. We went to the following path “C:\Program Files\FRx Software\FRx 6.7\Analytical Accounting\Scripts\SQL Server” and found 2 scripts:

CreateTablesAA.sql and CreateProcsFunctionsAA.sql

First run CreateTablesAA against you Dynamics Database, which will generate another script to run on your companies’ databases, copy this script and run it on a new query window to update your database.

Then run CreateProcsFunctionsAA and perform the same steps you performed on the other script.

3. Go to the following path (or where you installed the FRx) “C:\Program Files\FRx Software\FRx 6.7\Analytical Accounting\” and run “AAWizard.exe”, which will in return will load your Analytical Accounting dimensions and codes into FRx databases. –You might face some errors in this stage, contact me if you face any error and I will help!-

4. Now we are ready to start, open FRx Report Designer the go to Company >> Information, create new company and Select “Analytical Accounting” for the accounting system –if you couldn’t select the Analytical Accounting then you will need to register some DLLs, search the knowledgebase for such article-:

clip_image002

5. Now the fun will begin!! Create New Row, and open the GL accounts lookup and make sure you selected “Full” in the account segment:

clip_image004

The first segment represents the GL account number, while the other segments are the dimensions added to the Dimension Structure you created in the wizard.

6. Add a new column and add Description and GL columns as needed:

clip_image006

7. Add new Report Catalog and link your row and column:

clip_image008

8. Print your report and enjoy having your dimensions and codes in the statement:

clip_image010

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com