Page 1 of 41234

Archive for the ‘Analytical Accounting’ Category

Post Through GL for Computer Checks – with Analytical Accounting

When you have Analytical Accounting module installed and configured in a GP company, the Post Through GL for Computer Checks gets automatically disabled in the specific company (even if it was turned on).

image

If we try to activate the same manually (with Analytical Accounting activated), we get the following error message and the system prevents us from turning on the Post Through GL functionality for Computer Checks.

image

However, when we login to another company on the same DYNAMICS instance, where Analytical Accounting is not activated, the system does not allow us to mark the Post Through GL option for Computer checks and gives the same error message as above. However, I had confirmed that Analytical Accounting was not installed by trying to open the Transaction Dimension window from Cards >> Financials >> Analytical Accounting >> Transaction Dimension, and I obtained the message below.

image

This indicated that Analytical Accounting was not installed or activated on this company and yet the system does not let me mark the Post Through GL option. (This issue happens on both Fabricam, Inc. and on a regular production company). This appears to be a genuine bug in Analytical Accounting, and I had tested this on GP v2010 R2 (11.0.1752)

As a work-around for this, I enabled the Post Through GL for Computer Checks in a company where Analytical Accounting was not activated by a SQL query, and “Eureka…”, it worked brilliantly and the computer check batches got posted through into the General Ledger successfully without any errors. The script I had used for updating the Posting Setup is given below. This script needs to be run on the specific company database where Analytical Accounting has not been activated.

UPDATE dbo.SY02300
    SET AUTPSTGL = 1
    WHERE SERIES = 4
        AND TRXSOURC = 'Computer Checks'

Hope this helps the community…

Until next post!

August 4, 2011 · veeyeskay · No Comments
Tags: , , , ,  · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 3,471

Restricting Dimension Codes to Specific Account Numbers

Hi all

In this article, I am going to explain yet another functionality in Analytical Accounting, where we can restrict a dimension code to a specific account number (or) a specific range of accounts.

When a dimension code is created, we need to grant access to the dimension codes to specific users who can use that dimension code at transactions. However, if a user has access to a transaction dimension code, he can use that code with any GL account that is linked to an accounting class (which has the transaction dimension option set to Required or Optional).

In order to restrict the use of a dimension code to a specific range of accounts, we can use the Account Access to Transaction Dimension Codes window, which can be accessed from Cards >> Financial >> Analytical Accounting >> Account Access.

image

After entering/selecting the transaction dimension and the transaction dimension code, choose the Account Access option as “Select Accounts” as shown below.

image

Then we can define a range of accounts by filtering on the following options.

  • Account
  • Segment
  • Accounting Class

Based on the above range options, we can define the range of accounts for which the specific transaction dimension code has access to and save the details to enforce the restriction. This helps the users to use the appropriate dimension codes with appropriate GL accounts.

Once these restrictions are defined, if the user tries to use the dimension code with another account which is not in the range defined in the above window, the system does not allow the user to process the transaction and receive the error message during the AA validation process and when printing the edit lists.

image

image

Hope this helps the community…

Until next post!

June 20, 2011 · veeyeskay · 4 Comments
Tags: , , , ,  · Posted in: Analytical Accounting, Dynamics, Great Plains Total Views: 2,516

SQL Script for Budgets vs. Actuals in Amounts in Analytical Accounting – By Budget Tree Node

Hi all

This script is in continuation to the previous article where I had posted a script for Budgets vs. Actuals in Amounts in Analytical Accounting – By Dimension Code.

This script is for viewing Budgets vs. Actuals (for amounts) at Budget Tree Node level.

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[CVA_aaGetLvlCodeString]')
                    AND TYPE IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ) ) 
    DROP FUNCTION [dbo].[CVA_aaGetLvlCodeString]
 
GO
 
/******************************************************************
Created June 16, 2011 by Sivakumar Venkataraman - Interdyn AKA
This function returns a string which contains the AA codes for 
a specific distribution line from the AA tables for a specific
budget tree.
 
Tables used: 
 - AAG00901 - AA Budget Tree Trx Dim Master
 - AAG30000 - aaGLHdr
 - AAG30001 - aaGLDist
 - AAG30002 - aaGLAssign
 - AAG30003 - aaGLCode
 ******************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CVA_aaGetLvlCodeString]
    (
      @aaBudgetTreeID INT ,
      @aaGLHdrID INT ,
      @aaGLDistID INT ,
      @aaGLAssignID INT
    )
RETURNS NVARCHAR(51)
AS 
    BEGIN
        DECLARE @aaLvlCodeString AS NVARCHAR(51)
        SELECT  @aaLvlCodeString = CASE WHEN @aaLvlCodeString IS NULL THEN ''
                                        ELSE @aaLvlCodeString + ','
                                   END + LTRIM(RTRIM(STR(A.aaTrxCodeID)))
        FROM    ( SELECT    E.aaOrder ,
                            D.aaTrxCodeID
                  FROM      dbo.AAG30000 A
                            INNER JOIN dbo.AAG30001 B ON A.aaGLHdrID = B.aaGLHdrID
                            INNER JOIN dbo.AAG30002 C ON B.aaGLHdrID = C.aaGLHdrID
                                                         AND B.aaGLDistID = C.aaGLDistID
                            INNER JOIN dbo.AAG30003 D ON C.aaGLHdrID = D.aaGLHdrID
                                                         AND C.aaGLDistID = D.aaGLDistID
                                                         AND C.aaGLAssignID = D.aaGLAssignID
                            INNER JOIN dbo.AAG00901 E ON D.aaTrxDimID = E.aaTrxDimID
                  WHERE     E.aaBudgetTreeID = @aaBudgetTreeID
                            AND D.aaGLHdrID = @aaGLHdrID
                            AND D.aaGLDistID = @aaGLDistID
                            AND D.aaGLAssignID = @aaGLAssignID
                ) A
        ORDER BY A.aaOrder
        RETURN @aaLvlCodeString
    END
 
/******************************************************************
Created June 16, 2011 by Sivakumar Venkataraman - Interdyn AKA
This script is to return budget vs. actual values from AA module
 
Tables used: 
 - AAG00905 - AA Budget Tree Account Balance
 - AAG00903 - AA Budget Master
 - AAG00902 - AA Budget Tree Trx Dim Code Master
 - AAG00900 - AA Budget Tree Master
 - AAG00401 - aaTrxDimCodeSetp
 - AAG30000 - aaGLHdr
 - AAG30001 - aaGLDist
 - AAG30002 - aaGLAssign
 - AAG30003 - aaGLCode
 - GL00100 - Account Master
 - GL00102 - Account Category Master
 - GL00105 - Account Index Master
 - SY40100 - Period Setup 
 
Custom Functions used:
 - CVA_aaGetLvlCodeString()
 ******************************************************************/
DECLARE @aaBudgetID INTEGER
 
--Define the Budget ID for analysis purposes
SET @aaBudgetID = 3
 
SELECT  B.aaBudget ,
        B.aaBudgetDescr ,
        C.aaBudgetTree ,
        C.aaBudgetTreeDescr ,
        J.YEAR1 AS glFiscalYear ,
        J.PERNAME AS glPeriodName ,
        CASE WHEN J.PERIODID <> 0
             THEN REPLICATE('0', 2 - LEN(J.PERIODID))
                  + ( LTRIM(RTRIM(STR(J.PERIODID))) + ':'
                      + ( LEFT({fn MONTHNAME(J.PERIODDT)}, 3) )
                      + '''' + SUBSTRING(LTRIM(RTRIM(STR(J.YEAR1))),
                                         3, 2) )
             ELSE '00:BBF''' + SUBSTRING(LTRIM(RTRIM(STR(J.YEAR1
                                                  - 1))), 3, 2)
        END AS glFiscalPeriod ,
        RTRIM(H.ACTNUMST) AS glAccountNo ,
        RTRIM(G.ACTDESCR) AS glAccountDesc ,
        RTRIM(I.ACCATDSC) AS glAccountCategory ,
        K.aaTrxDim AS aaTrxDimChild, 
        E.aaTrxDimCode AS aaTrxDimCodeChild ,
        ISNULL(L.aaTrxDim, '') AS aaTrxDimParent ,
        ISNULL(F.aaTrxDimCode, '') AS aaTrxDimCodeParent ,
        A.Balance AS aaBudgetAmount ,
        D.aaLvlCodeString ,
        ISNULL(( SELECT SUM(Z1.DEBITAMT - Z1.CRDTAMNT)
                 FROM   ( SELECT DISTINCT
                                    C1.DEBITAMT ,
                                    C1.CRDTAMNT
                          FROM      dbo.AAG30000 A1
                                    INNER JOIN dbo.AAG30001 B1 ON A1.aaGLHdrID = B1.aaGLHdrID
                                    INNER JOIN dbo.AAG30002 C1 ON B1.aaGLHdrID = C1.aaGLHdrID
                                                              AND B1.aaGLDistID = C1.aaGLDistID
                                    INNER JOIN dbo.AAG30003 D1 ON C1.aaGLHdrID = D1.aaGLHdrID
                                                              AND C1.aaGLDistID = D1.aaGLDistID
                                                              AND C1.aaGLAssignID = D1.aaGLAssignID
                                    INNER JOIN dbo.SY40100 E1 ON A1.GLPOSTDT >= E1.PERIODDT
                                                              AND A1.GLPOSTDT <= E1.PERDENDT
                          WHERE     E1.SERIES = 0
                                    AND E1.PERIODDT = A.PERIODDT
                                    AND B1.ACTINDX = A.ACTINDX
                                    AND dbo.CVA_aaGetLvlCodeString(C.aaBudgetTreeID,
                                                              C1.aaGLHdrID,
                                                              C1.aaGLDistID,
                                                              C1.aaGLAssignID) = D.aaLvlCodeString
                        ) Z1
               ), 0) AS aaActualAmount
FROM    dbo.AAG00905 A
        INNER JOIN dbo.AAG00903 B ON A.aaBudgetID = B.aaBudgetID
        INNER JOIN dbo.AAG00900 C ON B.aaBudgetTreeID = C.aaBudgetTreeID
        INNER JOIN dbo.AAG00902 D ON B.aaBudgetTreeID = D.aaBudgetTreeID
                                     AND A.aaCodeSequence = D.aaCodeSequence
        INNER JOIN dbo.AAG00401 E ON E.aaTrxDimCodeID = D.aaTrxDimCodeID
        LEFT OUTER JOIN dbo.AAG00401 F ON F.aaTrxDimCodeID = D.aaTrxDimParCodeID
        INNER JOIN dbo.AAG00400 K ON E.aaTrxDimID = K.aaTrxDimID
        LEFT OUTER JOIN dbo.AAG00400 L ON F.aaTrxDimID = L.aaTrxDimID
        INNER JOIN dbo.GL00100 G ON A.ACTINDX = G.ACTINDX
        INNER JOIN dbo.GL00105 H ON A.ACTINDX = H.ACTINDX
        INNER JOIN dbo.GL00102 I ON G.ACCATNUM = I.ACCATNUM
        INNER JOIN dbo.SY40100 J ON A.PERIODDT = J.PERIODDT
WHERE   J.SERIES = 0
        AND A.aaBudgetID = @aaBudgetID

Hope this script helps the community…

Until next post!

June 16, 2011 · veeyeskay · One Comment
Tags: , , ,  · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 3,287

SQL Script for Budgets vs. Actuals in Amounts in Analytical Accounting – By Dimension Code

Hi all

Today, I am posting a script for analyzing Budgets vs. Actuals (using Amounts) in Analytical Accounting. However, there are two methods of analyzing the budget information in Analytical Accounting. They are:

  • Node Level
  • Dimension Code Level

This script is for viewing Budgets vs. Actuals at Dimension Code level. I will be posting the script for analyzing budgets at Node level soon.

/******************************************************************
Created June 16, 2011 by Sivakumar Venkataraman - Interdyn AKA
This script is to return budget vs. actual values from AA module
 
Tables used: 
 - AAG00905 - AA Budget Tree Account Balance
 - AAG00903 - AA Budget Master
 - AAG00902 - AA Budget Tree Trx Dim Code Master
 - AAG00900 - AA Budget Tree Master
 - AAG00401 - aaTrxDimCodeSetp
 - AAG30000 - aaGLHdr
 - AAG30001 - aaGLDist
 - AAG30002 - aaGLAssign
 - AAG30003 - aaGLCode
 - GL00100 - Account Master
 - GL00102 - Account Category Master
 - GL00105 - Account Index Master
 - SY40100 - Period Setup 
******************************************************************/
SELECT  BvA.aaBudget ,
        BvA.glFiscalYear ,
        BvA.glFiscalPeriod ,
        Bva.glAccountCategory ,
        BvA.glAccountNo ,
        BvA.glAccountDesc ,
        BvA.aaTrxDim ,
        BvA.aaTrxDimCode ,
        BvA.aaTrxDimCodeDescr ,
        BvA.aaActualAmount AS aaActualAmount ,
        BvA.aaBudgetAmount AS aaBudgetAmount ,
        ( Bva.aaBudgetAmount - BvA.aaActualAmount ) AS aaVariance ,
        CASE WHEN BvA.aaBudgetAmount <> 0
             THEN CONVERT(NUMERIC(5, 2), ROUND(( ( ( Bva.aaBudgetAmount
                                                     - BvA.aaActualAmount )
                                                   / BvA.aaBudgetAmount )
                                                 * 100 ), 2))
             ELSE CONVERT(NUMERIC(19, 2), BvA.aaBudgetAmount)
        END AS aaVariancePercent
FROM    ( SELECT  DISTINCT
                    B.aaBudget ,
                    RTRIM(B.aaBudgetDescr) AS aaBudgetDescr ,
                    J.YEAR1 AS glFiscalYear ,
                    CASE WHEN J.PERIODID <> 0 THEN {fn MONTHNAME(J.PERIODDT)}
                         ELSE 'BBF'
                    END AS glPeriodName ,
                    CASE WHEN J.PERIODID <> 0
                         THEN REPLICATE('0', 2 - LEN(J.PERIODID))
                              + ( LTRIM(RTRIM(STR(J.PERIODID))) + ':'
                                  + ( LEFT({fn MONTHNAME(J.PERIODDT)}, 3) )
                                  + '''' + SUBSTRING(LTRIM(RTRIM(STR(J.YEAR1))),
                                                     3, 2) )
                         ELSE '00:BBF''' + SUBSTRING(LTRIM(RTRIM(STR(J.YEAR1
                                                              - 1))), 3, 2)
                    END AS glFiscalPeriod ,
                    RTRIM(H.ACTNUMST) AS glAccountNo ,
                    RTRIM(G.ACTDESCR) AS glAccountDesc ,
                    RTRIM(I.ACCATDSC) AS glAccountCategory ,
                    RTRIM(E1.aaTrxDim) AS aaTrxDim ,
                    RTRIM(E.aaTrxDimCode) AS aaTrxDimCode ,
                    RTRIM(E.aaTrxDimCodeDescr) AS aaTrxDimCodeDescr ,
                    A.Balance AS aaBudgetAmount ,
                    ISNULL(( SELECT SUM(C1.DEBITAMT - C1.CRDTAMNT)
                             FROM   dbo.AAG30000 A1
                                    INNER JOIN dbo.AAG30001 B1 ON A1.aaGLHdrID = B1.aaGLHdrID
                                    INNER JOIN dbo.AAG30002 C1 ON B1.aaGLHdrID = C1.aaGLHdrID
                                                              AND B1.aaGLDistID = C1.aaGLDistID
                                    INNER JOIN dbo.AAG30003 D1 ON C1.aaGLHdrID = D1.aaGLHdrID
                                                              AND C1.aaGLDistID = D1.aaGLDistID
                                                              AND C1.aaGLAssignID = D1.aaGLAssignID
                                    INNER JOIN dbo.SY40100 E1 ON A1.GLPOSTDT >= E1.PERIODDT
                                                              AND A1.GLPOSTDT <= E1.PERDENDT
                             WHERE  E1.SERIES = 0
                                    AND E1.PERIODDT = A.PERIODDT
                                    AND B1.ACTINDX = A.ACTINDX
                                    AND D1.aaTrxCodeID = D.aaTrxDimCodeID
                           ), 0) AS aaActualAmount
          FROM      dbo.AAG00905 A
                    INNER JOIN dbo.AAG00903 B ON A.aaBudgetID = B.aaBudgetID
                    INNER JOIN dbo.AAG00902 D ON B.aaBudgetTreeID = D.aaBudgetTreeID
                                                 AND A.aaCodeSequence = D.aaCodeSequence
                    INNER JOIN dbo.AAG00401 E ON E.aaTrxDimCodeID = D.aaTrxDimCodeID
                    INNER JOIN dbo.AAG00400 E1 ON E.aaTrxDimID = E1.aaTrxDimID
                    INNER JOIN dbo.GL00100 G ON A.ACTINDX = G.ACTINDX
                    INNER JOIN dbo.GL00105 H ON A.ACTINDX = H.ACTINDX
                    INNER JOIN dbo.GL00102 I ON G.ACCATNUM = I.ACCATNUM
                    INNER JOIN dbo.SY40100 J ON A.PERIODDT = J.PERIODDT
          WHERE     J.SERIES = 0
        ) BvA

Hope this script helps the community…

Until next post!

June 16, 2011 · veeyeskay · 7 Comments
Tags: , ,  · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 3,880

Analytical Accounting Information Flow from Purchase Order to Receipts

Hi all

This article is to explain how Analytical Accounting (AA) information is transferred from Purchase Orders to Receiving Transaction Entry.

The scenario explained below is on an environment where there is one transaction dimension configured in Analytical Accounting.

Create a Purchase Order with 2 line items as explained below.

PO Number Line Seq. No Item Number Quantity Unit Cost Extended Cost
PO2077 16384 128 SDRAM 10 $1200.00 $12000.00
PO2077 32768 256 SDRAM 5 $1400.00 $7000.00

Assign the dimension codes in the following way for the 2 lines in the PO as shown below.

PO Number Line Seq. No Item Number Inventory Account Amount Assignment % Trx. Dimension Trx Dimension Code
PO2077 16384 128 SDRAM 000-1300-01 $12000.00 100% D1 D1C001
PO2077 32768 256 SDRAM 000-1300-01 $2800.00 40% D1 D1C001
PO2077 32768 256 SDRAM 000-1300-01 $4200.00 60% D1 D1C002

So the amount on the first line is completely assigned to the dimension code D1C001. The amount on the second line is split between the dimension codes D1C001 and D1C002 in the ratio 40% and 60% respectively.

When this PO is received using the Receiving Transactions Entry, the AA distributions for the receipt get updated as follows for the inventory account.

Receipt No. Distribution Seq. No. Inventory Account Distribution Amount Assignment % Assignment Amount Trx. Dimension Trx. Dimension Code
RCT1166 16384 000-1300-01 $19000.00 63.16% $12000.00 D1 D1C001
RCT1166 16384 000-1300-01 $19000.00 14.74% $2800.00 D1 D1C001
RCT1166 16384 000-1300-01 $19000.00 22.10% $4200.00 D1 D1C002

Thus in case of a Purchase Order, the Sequence Number in the AAG20001 table is linked to the PO Line Sequence Number field (Ord) in the POP10110 table. This is because the GL account information (Inventory account) is stored at the PO line level and there are no distributions generated in case of a purchase order.

However, in case of a receipt, there are distributions which get posted into the General Ledger when posting the receipt. And in this case, the Sequence Number on the AAG20001 table is linked back to the Distribution Line Sequence Number in the POP10390 table for the inventory account (which is why you notice that for the receipts, the Line Sequence Number is the same for all the 3 lines.

Hope this explains how AA information is stored in the PO and transferred to the Receipts.

Until next post!

June 2, 2011 · veeyeskay · One Comment
Tags: , , ,  · Posted in: Analytical Accounting, Dynamics, Great Plains, Purchase Order Processing Total Views: 2,568

SQL Script for POP Receiving Transactions with AA Information

Hi all

My last post had a script for Purchase Orders with AA information.

Today, I am posting a script for the following POP transactions with AA information.

  1. Shipments
  2. Shipment/Invoice
  3. Invoice
IF EXISTS ( SELECT  *
            FROM    sys.views
            WHERE   object_id = OBJECT_ID(N'[dbo].[vw_POPRcptInformation]') ) 
    DROP VIEW [dbo].[vw_POPRcptInformation]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
Created May 20, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is to return the PO Receipt information (header and lines) 
from both work and history tables.
 
View Name: vw_POPRcptInformation
Tables used: 
 - POP10300 - POP Receipt Work Header 
 - POP10310 - POP Receipt Work Line 
 - POP10500 - POP Line Distribution
 - POP30300 - POP Receipt History Header 
 - POP30310 - POP Receipt History Line 
 ******************************************************************/
CREATE VIEW [dbo].[vw_POPRcptInformation]
AS  SELECT  A.POPRCTNM,
            A.VNDDOCNM,
            A.receiptdate,
            A.POPTYPE,
            A.VENDORID,
            A.VENDNAME,
            A.SUBTOTAL,
            A.TRDISAMT,
            A.FRTAMNT,
            A.MISCAMNT,
            A.TAXAMNT,
            A.PYMTRMID,
            A.DUEDATE,
            A.CURNCYID,
            A.RATETPID,
            A.EXGTBLID,
            A.XCHGRATE,
            A.EXCHDATE,
            A.ORSUBTOT,
            A.ORTDISAM,
            A.ORFRTAMT,
            A.ORMISCAMT,
            A.ORTAXAMT,
            RTRIM(C.ACTNUMST) AS GPACCOUNT,
            RTRIM(D.ACTDESCR) AS GPACCOUNTDESC,
            B.SEQNUMBR
    FROM    TWO.dbo.POP10300 A
            INNER JOIN TWO.dbo.POP10390 B ON A.POPRCTNM = B.POPRCTNM
            INNER JOIN TWO.dbo.GL00105 C ON B.ACTINDX = C.ACTINDX
            INNER JOIN TWO.dbo.GL00100 D ON B.ACTINDX = D.ACTINDX
    UNION ALL
    SELECT  A.POPRCTNM,
            A.VNDDOCNM,
            A.receiptdate,
            A.POPTYPE,
            A.VENDORID,
            A.VENDNAME,
            A.SUBTOTAL,
            A.TRDISAMT,
            A.FRTAMNT,
            A.MISCAMNT,
            A.TAXAMNT,
            A.PYMTRMID,
            A.DUEDATE,
            A.CURNCYID,
            A.RATETPID,
            A.EXGTBLID,
            A.XCHGRATE,
            A.EXCHDATE,
            A.ORSUBTOT,
            A.ORTDISAM,
            A.ORFRTAMT,
            A.ORMISCAMT,
            A.ORTAXAMT,
            RTRIM(C.ACTNUMST) AS GPACCOUNT,
            RTRIM(D.ACTDESCR) AS GPACCOUNTDESC,
            B.SEQNUMBR
    FROM    TWO.dbo.POP30300 A
            INNER JOIN TWO.dbo.POP30390 B ON A.POPRCTNM = B.POPRCTNM
            INNER JOIN TWO.dbo.GL00105 C ON B.ACTINDX = C.ACTINDX
            INNER JOIN TWO.dbo.GL00100 D ON B.ACTINDX = D.ACTINDX
 
GO
 
IF EXISTS ( SELECT  *
            FROM    sys.views
            WHERE   object_id = OBJECT_ID(N'[dbo].[vw_POPRcptAAInformation]') ) 
    DROP VIEW [dbo].[vw_POPRcptAAInformation]
 
GO	
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
Created May 20, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is to return the Analytical Accounting information 
for the POP Receipt lines (work and history).
 
View Name: vw_POPRcptAAInformation
Tables used: 
 - AAG20000 - AA SubLedger Header 
 - AAG20001 - AA SubLedger Distribution 
 - AAG20002 - AA SubLedger Distribution Assign 
 - AAG20003 - AA SubLedger Dimension Code 
 - AAG00400 - AA Transaction Dimension Master
 - AAG00401 - AA Transaction Dimension Code Master
 ******************************************************************/
CREATE VIEW [dbo].[vw_POPRcptAAInformation]
AS  SELECT  A.DOCNUMBR,
            B.SEQNUMBR,
            B.ACTINDX,
            B.CURNCYID,
            C.DEBITAMT,
            C.CRDTAMNT,
            C.ORDBTAMT,
            C.ORCRDAMT,
            E.aaTrxDim,
            E.aaTrxDimDescr,
            F.aaTrxDimCode,
            F.aaTrxDimCodeDescr
    FROM    TWO.dbo.AAG20000 A
            INNER JOIN TWO.dbo.AAG20001 B ON B.aaSubLedgerHdrID = A.aaSubLedgerHdrID
            INNER JOIN TWO.dbo.AAG20002 C ON B.aaSubLedgerHdrID = C.aaSubLedgerHdrID
                                             AND B.aaSubLedgerDistID = C.aaSubLedgerDistID
            INNER JOIN TWO.dbo.AAG20003 D ON C.aaSubLedgerHdrID = D.aaSubLedgerHdrID
                                             AND C.aaSubLedgerDistID = D.aaSubLedgerDistID
                                             AND C.aaSubLedgerAssignID = D.aaSubLedgerAssignID
            INNER JOIN TWO.dbo.AAG00400 E ON D.aaTrxDimID = E.aaTrxDimID
            INNER JOIN TWO.dbo.AAG00401 F ON F.aaTrxDimID = D.aaTrxDimID
                                             AND F.aaTrxDimCodeID = D.aaTrxCodeID
    WHERE   ( A.SERIES = 12
              AND A.DOCTYPE <> 0
            )
 
GO
 
IF EXISTS ( SELECT  *
            FROM    sys.views
            WHERE   object_id = OBJECT_ID(N'[dbo].[vw_POPRcptInformationwithAA]') ) 
    DROP VIEW [dbo].[vw_POPRcptInformationwithAA]
 
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
Created May 20, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is to return the POP Receipt information (header and lines) 
along with the transaction dimension information for each POP Receipt
Transaction.
 
View Name: vw_POPRcptInformationwithAA
Views used: 
 - vw_POPRcptInformation
 - vw_POPRcptAAInformation
 ******************************************************************/
CREATE VIEW [dbo].[vw_POPRcptInformationwithAA]
AS  SELECT  A.POPRCTNM,
            A.receiptdate,
            A.POPTYPE,
            A.VENDORID,
            A.VENDNAME,
            A.CURNCYID,
            A.ORSUBTOT,
            A.ORTDISAM,
            A.ORFRTAMT,
            A.ORMISCAMT,
            A.ORTAXAMT,
            A.GPACCOUNT,
            A.GPACCOUNTDESC,
            B.aaTrxDim,
            B.aaTrxDimDescr,
            B.aaTrxDimCode,
            B.aaTrxDimCodeDescr,
            B.ORDBTAMT,
            B.ORCRDAMT,
            B.DEBITAMT,
            B.CRDTAMNT
    FROM    dbo.vw_POPRcptInformation A
            LEFT OUTER JOIN dbo.vw_POPRcptAAInformation B ON A.POPRCTNM = B.DOCNUMBR
                                                             AND A.SEQNUMBR = B.SEQNUMBR

Hope this helps the community.

Until next post…

May 20, 2011 · veeyeskay · 3 Comments
Tags: , ,  · Posted in: Analytical Accounting, Dynamics, Great Plains, Purchase Order Processing, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 1,688

Updated SQL Script for fixing Primary Key Errors in Analytical Accounting

Hi all

I had posted an article in my blog some time back which updates the indexes in the AAG00102 table to fix the primary keys in analytical accounting. This article can be viewed here. Even though that was useful to update the account indexes in all companies at once to synchronize it, it does not work well if the key record needs to be inserted into the table. I realized this when I was assisting one of the community members Louella Thomas in her AA issue.

So I have created a variation of the same script with the insert functionality as well. However, this script can be used only in one company database at a time. But still I guess this will be a lot more efficient in terms of maintaining consistency in the data.

DECLARE @aaRowID INT
DECLARE @CMPANYID INT
 
SELECT  @CMPANYID = CMPANYID
FROM    DYNAMICS.dbo.SY01500
WHERE   INTERID = DB_NAME()
 
--Update the keys for AAG00201 table
SELECT  @aaRowID = ISNULL(MAX(aaAcctClassID), 0)
FROM    dbo.AAG00201
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 201
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 201
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 201, @CMPANYID, @aaRowID )
            END
    END	
 
--Update the keys for AAG00300 table
SELECT  @aaRowID = ISNULL(MAX(aaDistrQueryID), 0)
FROM    dbo.AAG00300
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 300
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 300
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        		
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 301, @CMPANYID, @aaRowID )
            END
    END	
 
--Update the keys for AAG00310 table
SELECT  @aaRowID = ISNULL(MAX(aaMLQueryID), 0)
FROM    dbo.AAG00310
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 310
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 310
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 310, @CMPANYID, @aaRowID )
            END
    END	
 
--Update the keys for AAG00400 table
SELECT  @aaRowID = ISNULL(MAX(aaTrxDimID), 0)
FROM    dbo.AAG00400
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 400
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 400
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 400, @CMPANYID, @aaRowID )
            END
    END
 
--Update the keys for AAG00401 table
SELECT  @aaRowID = ISNULL(MAX(aaTrxDimCodeID), 0)
FROM    dbo.AAG00401
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 401
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 401
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 401, @CMPANYID, @aaRowID )
            END
    END
 
--Update the keys for AAG00402 table
SELECT  @aaRowID = ISNULL(MAX(aaTrxDimCodeNumID), 0)
FROM    dbo.AAG00402
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 402
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 402
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 402, @CMPANYID, @aaRowID )
            END
    END
 
--Update the keys for AAG00403 table
SELECT  @aaRowID = ISNULL(MAX(aaTrxDimCodeBoolID), 0)
FROM    dbo.AAG00403
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 403
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 403
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 403, @CMPANYID, @aaRowID )
            END
    END
 
--Update the keys for AAG00404 table
SELECT  @aaRowID = ISNULL(MAX(aaTrxDimCodeDateID), 0)
FROM    dbo.AAG00404
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 404
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 404
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 404, @CMPANYID, @aaRowID )
            END
    END
 
--Update the keys for AAG00500 table
SELECT  @aaRowID = ISNULL(MAX(aaDateID), 0)
FROM    dbo.AAG00500
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 500
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 500
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 500, @CMPANYID, @aaRowID )
            END
    END
 
--Update the keys for AAG00600 table
SELECT  @aaRowID = ISNULL(MAX(aaTreeID), 0)
FROM    dbo.AAG00600
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 600
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 600
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 600, @CMPANYID, @aaRowID )
            END
    END
 
--Update the keys for AAG00900 table
SELECT  @aaRowID = ISNULL(MAX(aaBudgetTreeID), 0)
FROM    dbo.AAG00900
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 900
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 900
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 900, @CMPANYID, @aaRowID )
            END
    END
 
--Update the keys for AAG10000 table
SELECT  @aaRowID = ISNULL(MAX(aaGLWorkHdrID), 0)
FROM    dbo.AAG10000
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 10000
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 10000
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 10000, @CMPANYID, @aaRowID )
            END
    END
 
--Update the keys for AAG20000 table
SELECT  @aaRowID = ISNULL(MAX(aaSubLedgerHdrID), 0)
FROM    dbo.AAG20000
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 20000
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 20000
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 20000, @CMPANYID, @aaRowID )
            END
    END
 
--Update the keys for AAG30000 table
SELECT  @aaRowID = ISNULL(MAX(aaGLHdrID), 0)
FROM    dbo.AAG30000
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 30000
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 30000
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 30000, @CMPANYID, @aaRowID )
            END
    END
 
--Update the keys for AAG40000 table
SELECT  @aaRowID = ISNULL(MAX(aaGLHdrID), 0)
FROM    dbo.AAG40000
IF EXISTS ( SELECT  *
            FROM    DYNAMICS.dbo.AAG00102
            WHERE   aaTableID = 40000
                    AND CMPANYID = @CMPANYID ) 
    BEGIN
        UPDATE  DYNAMICS.dbo.AAG00102
        SET     aaRowID = @aaRowID
        WHERE   aaTableID = 40000
                AND CMPANYID = @CMPANYID
    END
ELSE 
    BEGIN
        IF @aaRowID > 0 
            BEGIN		        				
                INSERT  INTO DYNAMICS.dbo.AAG00102
                        ( aaTableID, CMPANYID, aaRowID )
                VALUES  ( 40000, @CMPANYID, @aaRowID )
            END
    END

Note: Please ensure that all the users have logged out of GP before this script is executed.

Hope this script helps all to fix the primary key issues in analytical accounting.

Until next post!

May 15, 2011 · veeyeskay · 5 Comments
Tags: ,  · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 2,680