This morning I an issue reported from one of my customers, they are getting the below message when they try to apply multi-currency transactions:
I went through a long investigation process, after few hours it turned to be orphaned records in GL100001 with JRNENTRY = 0!
Deleting these record fixed the problem! Hope that this helps.
Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo
I got a very interesting case few months back, one of my customers implemented the Management Reporter and tried to implement their existing reports, it went well but for the reports that uses the Analytical Accounting Budgets that has filters over specific dimensions, budget amounts were not pulled at all.
At that time I tried very hard to resolve the issue with no luck, and therefore I had to install the FRx and work out the reports there till getting this resolved by Microsoft.
During the last days I have noticed many articles by Microsoft that they has identified the case and fixed this issue with the product updates, therefore this morning I decided to install Management Reporter 2012 with the final update to see if the issue was resolved, I created a dummy report and tested the case with no luck! Same issue is still there while I couldn’t replicate the same issue on a clean database.
After many researches I have seen a question answered in the community and located here, it is the same issue I am currently investigating, and the provided cause of issue is the gap in serials for “Analytical Accounting Transaction Dimension IDs”, simply the data inside AAG00400 table should look like the below:
What if I been having one of the dimensions coded as “15” instead of “7” as the case I had? The proposed solution was to create dummy dimensions to cover the gap between 9 and 15 which is doable and safe solution.
The problem is what if I had one of the dimensions coded as “700”! I am sure that there is no was to create 684 dummy dimensions to cover the gap where I had to reindex the whole database.
The operation I followed is very risky and requires a real expert in the SQL, below steps I used to fix my issue:
1. I searched for the tables that I need to affect, simply these are all the tables that has “aaTrxDimID” column, went to the SQL and wrote the following query:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Column_Name = 'aaTrxDimID' AND COLUMN_DEFAULT IS NOT NULL
2. I didn’t take a backup of the database but you must take a full backup!
3. I took a backup for the tables that I need to update by running the following query:
| SELECT * INTO BAK_AAG00401 FROM AAG00401 |
| SELECT * INTO BAK_AAG00901 FROM AAG00901 |
| SELECT * INTO BAK_AAG01001 FROM AAG01001 |
| SELECT * INTO BAK_AAG01002 FROM AAG01002 |
| SELECT * INTO BAK_AAG02000 FROM AAG02000 |
| SELECT * INTO BAK_AAG02001 FROM AAG02001 |
| SELECT * INTO BAK_AAG40003BAK FROM AAG40003BAK |
| SELECT * INTO BAK_AAG30003BAK FROM AAG30003BAK |
| SELECT * INTO BAK_AAG04001 FROM AAG04001 |
| SELECT * INTO BAK_AAG20003 FROM AAG20003 |
| SELECT * INTO BAK_AAG10003 FROM AAG10003 |
| SELECT * INTO BAK_AAG00202 FROM AAG00202 |
| SELECT * INTO BAK_AAG30003 FROM AAG30003 |
| SELECT * INTO BAK_AAG00312 FROM AAG00312 |
| SELECT * INTO BAK_AAG00400 FROM AAG00400 |
| SELECT * INTO BAK_AAG00316 FROM AAG00316 |
| SELECT * INTO BAK_AAG00801 FROM AAG00801 |
| SELECT * INTO BAK_AAG40003 FROM AAG40003 |
| SELECT * INTO BAK_AAG00402 FROM AAG00402 |
| SELECT * INTO BAK_AAG00403 FROM AAG00403 |
| SELECT * INTO BAK_AAG00404 FROM AAG00404 |
| SELECT * INTO BAK_AAG00405 FROM AAG00405 |
| SELECT * INTO BAK_AAG00406 FROM AAG00406 |
| SELECT * INTO BAK_AAG00407 FROM AAG00407 |
4. I have identified what are the existing IDs and what it should be and ran the following set of commands for each record:
| UPDATE AAG00401 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00901 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG01001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG01002 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG02000 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG02001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG40003BAK SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG30003BAK SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG04001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG20003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG10003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00202 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG30003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00312 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00400 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00316 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00801 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG40003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00402 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00403 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00404 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00405 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00406 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00407 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
Viola! The budgets are now generating data and everything looks fine for the time being, I will keep you posted with any other issues that might occur due to this operation.
Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo
Yesterday one of my customers reported an issue that the analytical accounting report is not being printed after the “General Posting Journal” upon posting a GL Transaction.
First thing came to my mind that this report has been unmarked in “Posting Setup” then in moment I realized that the Posting Setup does not contains anything for AA!!
It has been said in the analytical accounting installation manual that YOU MUST CONFIGURE YOU POSTING SETUP BEFORE ACTIVATING THE PRODUCT:
Setting up posting options for Analytical Accounting
Once you’ve installed Analytical Accounting for your company, you must set up
the appropriate posting options for Analytical Accounting in the Microsoft
Dynamics GP Posting Setup window. Be sure that you’ve completed this task before
you activate Analytical Accounting.In order to collect analytical information for an account, the account must be linked
to an account class. Analytical Accounting does not support posting in summary.
So how to adjust this? Where can I find the posting setup for AA and how to configure my reports?
An hour of profiler trace returned what does GP actually do to load report, specially after activating the “Allow Transactions Posting” option and getting the error below on the report generation:
“Unable to obtain posting destination record.”
I my trace, I noticed that GP is running the following query to get report printed to the appropriate destination:
exec TWO.dbo.zDP_SY02200SS_1 2,'General Entry','General Posting Journal'
This script returned the following results:
Then I saw the following command which returned the following result:
exec TWO.dbo.zDP_SY02200SS_1 2,'General Entry','3180rpt_8'
At this moment I realized that this “3180rpt_8” report is being called without being exist in posting setup table and that’s why I used to get the above message and being able to get the report printed.
Resolution:
I just added the report row into SY02200 table and rerun the posting! That worked like a charm!
Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo
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.
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
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:
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
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, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
mohdaoud@gmail.com
mohdaoud.blogspot.com
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, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
mohdaoud@gmail.com
mohdaoud.blogspot.com
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, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
mohdaoud@gmail.com
mohdaoud.blogspot.com
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-:
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:
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:
7. Add new Report Catalog and link your row and column:
8. Print your report and enjoy having your dimensions and codes in the statement:
Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
mohdaoud@gmail.com
mohdaoud.blogspot.com
Personally I used to have allot of primary key issues when working with analytical accounting due to errors in the Analytical Accounting Index Master table in Dynamics database, and used to open this table manually and fill the correct indexes there.
Today @ Interesting Findings & Knowledge Sharing blog, “veeyeskay” posted an SQL script that updates this table Dynamically! Find the script below or visit the post for more information here.
USE [DYNAMICS]
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[sp_FixAARecordCount]')
AND type IN ( N'P', N'PC' ) )
DROP PROCEDURE [dbo].[sp_FixAARecordCount]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_FixAARecordCount]
AS
DECLARE @INTERID CHAR(5)
DECLARE @CMPANYID INT
DECLARE @SQL CHAR(8000)
DECLARE cr_Companies CURSOR
FOR SELECT INTERID,
CMPANYID
FROM DYNAMICS.dbo.SY01500
ORDER BY CMPANYID
OPEN cr_Companies
FETCH NEXT FROM cr_Companies INTO @INTERID, @CMPANYID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaAcctClassID),0) FROM '
+ @INTERID
+ '..AAG00201) WHERE aaTableID = 201 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaDistrQueryID),0) FROM '
+ @INTERID
+ '..AAG00301) WHERE aaTableID = 301 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaMLQueryID),0) FROM '
+ @INTERID
+ '..AAG00310) WHERE aaTableID = 310 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimID),0) FROM '
+ @INTERID
+ '..AAG00400) WHERE aaTableID = 400 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeID),0) FROM '
+ @INTERID
+ '..AAG00401) WHERE aaTableID = 401 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeNumID),0) FROM '
+ @INTERID
+ '..AAG00402) WHERE aaTableID = 402 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeBoolID),0) FROM '
+ @INTERID
+ '..AAG00403) WHERE aaTableID = 403 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTrxDimCodeBoolID),0) FROM '
+ @INTERID
+ '..AAG00404) WHERE aaTableID = 404 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaDateID),0) FROM '
+ @INTERID
+ '..AAG00500) WHERE aaTableID = 500 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaTreeID),0) FROM '
+ @INTERID
+ '..AAG00600) WHERE aaTableID = 600 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaNodeID),0) FROM '
+ @INTERID
+ '..AAG00601) WHERE aaTableID = 601 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaOption),0) FROM '
+ @INTERID
+ '..AAG00700) WHERE aaTableID = 700 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaAliasID),0) FROM '
+ @INTERID
+ '..AAG00800) WHERE aaTableID = 800 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaBudgetTreeID),0) FROM '
+ @INTERID
+ '..AAG00900) WHERE aaTableID = 900 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaUDFID),0) FROM '
+ @INTERID
+ '..AAG01000) WHERE aaTableID = 1000 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaGLWorkHdrID),0) FROM '
+ @INTERID
+ '..AAG10000) WHERE aaTableID = 10000 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaSubLedgerHdrID),0) FROM '
+ @INTERID
+ '..AAG20000) WHERE aaTableID = 20000 AND CMPANYID = '
+ @CMPANYID + ''
)
EXEC
( 'UPDATE DYNAMICS..AAG00102 SET aaRowID = (SELECT ISNULL(MAX(aaGLHdrID),0) FROM '
+ @INTERID
+ '..AAG30000) WHERE aaTableID = 30000 AND CMPANYID = '
+ @CMPANYID + ''
)
FETCH NEXT FROM cr_Companies INTO @INTERID, @CMPANYID
END
CLOSE cr_Companies
DEALLOCATE cr_Companies
GO
GRANT EXECUTE ON sp_FixAARecordCount TO DYNGRP
Regards,
--
Mohammad R. Daoud
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
CTO
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com
