Page 1 of 212

Archive for the ‘SQL Server 2000’ Category

SQL Script for Vendor EFT Information

Hi all

Today, I am posting a SQL Script for getting the EFT information of various vendors.

/******************************************************************
Created July 16, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view returns a returns the details of the EFT Information
for the various vendors setup in the company.
 
Tables used: 
 - PM00100 - Vendor Class Master
 - PM00200 - Vendor Master
 - PM00300 - Vendor Address Master
 - SY06000 - Address EFT Master
 ******************************************************************/
SELECT A.VENDORID ,
        A.VENDNAME ,
        A.VNDCHKNM ,
        A.VENDSHNM ,
        A.VNDCLSID ,
        C.VNDCLDSC ,
        B.ADRSCODE ,
        B.VNDCNTCT ,
        B.ADDRESS1 ,
        B.ADDRESS2 ,
        B.ADDRESS3 ,
        B.CITY ,
        B.STATE ,
        B.ZIPCODE ,
        B.COUNTRY ,
        B.PHNUMBR1 ,
        B.FAXNUMBR ,
        D.EFTBankType ,
        D.BANKNAME ,
        D.EFTBankAcct ,
        D.EFTBankBranch ,
        D.EFTBankCode ,
        D.EFTBankBranchCode ,
        D.EFTBankCheckDigit ,
        D.EFTTransitRoutingNo ,
        D.EFTTransferMethod ,
        D.CURNCYID ,
        D.EFTAccountType ,
        D.EFTPrenoteDate ,
        D.EFTTerminationDate ,
        D.BNKCTRCD ,
        D.SWIFTADDR ,
        D.IntlBankAcctNum
    FROM TWO.dbo.PM00200 A
        INNER JOIN TWO.dbo.PM00300 B
        ON A.VENDORID = B.VENDORID
        LEFT OUTER JOIN TWO.dbo.PM00100 C
        ON A.VNDCLSID = C.VNDCLSID 
        LEFT OUTER JOIN TWO.dbo.SY06000 D
        ON B.ADRSCODE = D.ADRSCODE
           AND B.VENDORID = D.CustomerVendor_ID
           AND D.SERIES = 4

Hope this helps the community…

Until next post!

July 16, 2011 · veeyeskay · 2 Comments
Tags: , ,  · Posted in: Accounts Payables, Dynamics, EFT For Payables, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005 Total Views: 3,691

SQL Script to Reconcile GL vs. AA Balances

Hi all

Having used Analytical Accounting for quite some time now, I have come across various issues in Analytical Accounting module, which has caused serious data discrepancy between the General Ledger tables and the Analytical Accounting tables.

So I had decided to write up a simple SQL script to reconcile the balances for every account currency combination between the General Ledger tables and the Analytical Accounting tables.

 
DECLARE @ASOFDATE AS DATETIME 
 
SET @ASOFDATE = '2010-12-31'
 
SELECT  SUMM.COMPANY ,
	SUMM.JOURNALENTRY ,
        SUMM.GPACCOUNTNO ,
        SUMM.CURNCYID ,
        SUMM.GLORIGAMT ,
        SUMM.GLFUNCAMT ,
        SUMM.AA30001ORIGAMT ,
        SUMM.AA30001FUNCAMT ,
        SUMM.AA30002ORIGAMT ,
        SUMM.AA30002FUNCAMT ,
        SUMM.GLAAORIGMATCH ,
        SUMM.GLAAFUNCMATCH
FROM    ( SELECT    'TWO' AS COMPANY ,
                    X.JRNENTRY AS JOURNALENTRY ,
                    RTRIM(X.ACTNUMST) AS GPACCOUNTNO ,
                    X.CURNCYID ,
                    X.GLORIGAMT ,
                    X.GLFUNCAMT ,
                    X.AA30001ORIGAMT ,
                    X.AA30001FUNCAMT ,
                    X.AA30002ORIGAMT ,
                    X.AA30002FUNCAMT ,
                    CASE WHEN ( ( X.GLORIGAMT = X.AA30002ORIGAMT )
                                AND ( X.GLORIGAMT = X.AA30001ORIGAMT )
                                AND ( X.AA30001ORIGAMT = X.AA30002ORIGAMT )
                              ) THEN 'TRUE'
                         ELSE 'FALSE'
                    END AS GLAAORIGMATCH ,
                    CASE WHEN ( ( X.GLFUNCAMT = X.AA30002FUNCAMT )
                                AND ( X.GLFUNCAMT = X.AA30001FUNCAMT )
                                AND ( X.AA30001FUNCAMT = X.AA30002FUNCAMT )
                              ) THEN 'TRUE'
                         ELSE 'FALSE'
                    END AS GLAAFUNCMATCH ,
                    ( X.GLORIGAMT - X.AA30002ORIGAMT ) ORIGDIFFERENCE ,
                    ( X.GLFUNCAMT - X.AA30002FUNCAMT ) FUNCDIFFERENCE
          FROM      ( SELECT    A.JRNENTRY ,
                                B.ACTNUMST ,
                                B.ACTINDX ,
                                A.CURNCYID ,
                                SUM(A.ORDBTAMT - A.ORCRDAMT) AS GLORIGAMT ,
                                SUM(A.DEBITAMT - A.CRDTAMNT) AS GLFUNCAMT ,
                                ( SELECT    ISNULL(SUM(C.ORDBTAMT - C.ORCRDAMT), 0)
                                  FROM      TWO..AAG30001 C
                                            INNER JOIN TWO..AAG30000 D ON C.aaGLHdrID = D.aaGLHdrID
                                  WHERE     D.GLPOSTDT <= @ASOFDATE
                                            AND C.ACTINDX = B.ACTINDX
                                            AND C.CURNCYID = A.CURNCYID
                                            AND D.JRNENTRY = A.JRNENTRY
                                ) AS AA30001ORIGAMT ,
                                ( SELECT    ISNULL(SUM(C.DEBITAMT - C.CRDTAMNT), 0)
                                  FROM      TWO..AAG30001 C
                                            INNER JOIN TWO..AAG30000 D ON C.aaGLHdrID = D.aaGLHdrID
                                  WHERE     D.GLPOSTDT <= @ASOFDATE
                                            AND C.ACTINDX = B.ACTINDX
                                            AND C.CURNCYID = A.CURNCYID
                                            AND D.JRNENTRY = A.JRNENTRY
                                ) AS AA30001FUNCAMT ,
                                ( SELECT    ISNULL(SUM(E.ORDBTAMT - E.ORCRDAMT), 0)
                                  FROM      TWO..AAG30002 E
                                            INNER JOIN TWO..AAG30001 C ON E.aaGLHdrID = C.aaGLHdrID
                                                              AND E.aaGLDistID = C.aaGLDistID
                                            INNER JOIN TWO..AAG30000 D ON C.aaGLHdrID = D.aaGLHdrID
                                            INNER JOIN TWO..AAG30003 F ON E.aaGLHdrID = F.aaGLHdrID
                                                              AND E.aaGLDistID = F.aaGLDistID
                                                              AND E.aaGLAssignID = F.aaGLAssignID
                                  WHERE     D.GLPOSTDT <= @ASOFDATE
                                            AND C.ACTINDX = B.ACTINDX
                                            AND C.CURNCYID = A.CURNCYID
                                            AND D.JRNENTRY = A.JRNENTRY
                                            AND F.aaTrxCodeID <> 0
                                ) AS AA30002ORIGAMT ,
                                ( SELECT    ISNULL(SUM(E.DEBITAMT - E.CRDTAMNT), 0)
                                  FROM      TWO..AAG30002 E
                                            INNER JOIN TWO..AAG30001 C ON E.aaGLHdrID = C.aaGLHdrID
                                                              AND E.aaGLDistID = C.aaGLDistID
                                            INNER JOIN TWO..AAG30000 D ON C.aaGLHdrID = D.aaGLHdrID
                                            INNER JOIN TWO..AAG30003 F ON E.aaGLHdrID = F.aaGLHdrID
                                                              AND E.aaGLDistID = F.aaGLDistID
                                                              AND E.aaGLAssignID = F.aaGLAssignID
                                  WHERE     D.GLPOSTDT <= @ASOFDATE
                                            AND C.ACTINDX = B.ACTINDX
                                            AND C.CURNCYID = A.CURNCYID
                                            AND D.JRNENTRY = A.JRNENTRY
                                            AND F.aaTrxCodeID <> 0
                                ) AS AA30002FUNCAMT
                      FROM      TWO..GL20000 A
                                INNER JOIN TWO..GL00105 B ON A.ACTINDX = B.ACTINDX
                      WHERE     A.TRXDATE <= @ASOFDATE
                      GROUP BY  A.JRNENTRY ,
                                B.ACTNUMST ,
                                B.ACTINDX ,
                                A.CURNCYID
                    ) X
        ) SUMM
WHERE (SUMM.GLAAORIGMATCH = 'FALSE' OR SUMM.GLAAFUNCMATCH = 'FALSE')

This script is free for use and you can modify this script to suit your business requirements.

Note: I have updated the above script to validate the balances in all the 3 tables (GL20000, AAG30001 & AAG30002) to be in sync with one another. This is a critical enhancement since the AAG30002 table is the important table from where the data is picked up for reporting in AA.

Hope this script is helpful for folks.

Until next post…

May 16, 2010 · veeyeskay · 6 Comments
Tags: , , ,  · Posted in: Analytical Accounting, Dynamics, General Ledger, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 4,787

SQL Script to delete stranded activity for a specific user

Hi all

In this article I am providing a script to delete the activity table records for a specific stranded user in Dynamics GP. This will enable us not to have all the users log out of GP in order to execute these scripts. This is typically handy when we have many users signed into the system from various regions and its a tough job to have all the users sign off the system for this maintenance to be done.

Note: Its always advisable to take a back up of the DYNAMICS database before you execute this script. :)

DECLARE @USERID CHAR(15)
DECLARE @CMPNYNAM CHAR(30)
 
SET @USERID = 'testuser'
SET @CMPNYNAM = 'Fabrikam, Inc.'
 
/*Deleting the user record from the DEX_LOCK table*/
DELETE  FROM tempdb.dbo.DEX_LOCK
WHERE   session_id = ( SELECT   SQLSESID
                       FROM     DYNAMICS.dbo.ACTIVITY
                       WHERE    USERID = @USERID
                                AND CMPNYNAM = RTRIM(@CMPNYNAM)
                     )
 
/*Deleting the user record from the DEX_SESSION table*/
DELETE  FROM tempdb.dbo.DEX_SESSION
WHERE   session_id = ( SELECT   SQLSESID
                       FROM     DYNAMICS.dbo.ACTIVITY
                       WHERE    USERID = @USERID
                                AND CMPNYNAM = RTRIM(@CMPNYNAM)
                     )
 
/*Deleting the user record from the SY00801 table*/
DELETE  FROM DYNAMICS.dbo.SY00801
WHERE   USERID = @USERID
        AND CMPANYID = ( SELECT CMPANYID
                         FROM   DYNAMICS.dbo.SY01500
                         WHERE  CMPNYNAM = RTRIM(@CMPNYNAM)
                       )
 
/*Deleting the user record from the SY00800 table*/
DELETE  FROM DYNAMICS.dbo.SY00800
WHERE   USERID = @USERID
        AND CMPNYNAM = RTRIM(@CMPNYNAM)
 
/*Deleting the user record from the ACTIVITY table*/
DELETE  FROM DYNAMICS.dbo.ACTIVITY
WHERE   USERID = @USERID
        AND CMPNYNAM = RTRIM(@CMPNYNAM)

I hope this script above would be useful to you folks…

Until next post…

May 16, 2010 · veeyeskay · 5 Comments
Tags: , , , , , , ,  · Posted in: Dynamics, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, System Total Views: 5,037

SQL Script to provide access to Transaction Dimension Codes to Users

Hi all

I had posted an article sometime back titled Macro to provide access to Transaction Dimension Codes to Users where I had provided a SQL script to create a macro code to provide access to all users to all transaction dimension codes setup in the company.

I had received a few comments from user community if the same task which is done by macro can be done using a SQL script. So I decided to write up a simple script to provide access to all users to all transaction dimensions and dimension codes setup in a company.

DECLARE @INTERID CHAR(5)
DECLARE @CMPANYID INT
DECLARE @CMPNYNAM CHAR(30)
 
DECLARE crCompanies CURSOR
    FOR SELECT  INTERID
        FROM    DYNAMICS..SY01500
        ORDER BY INTERID
 
OPEN crCompanies
 
FETCH NEXT FROM crCompanies INTO @INTERID
 
WHILE @@fetch_status = 0
    BEGIN
        PRINT '/* Updating AA Code Access in the database ' + RTRIM(@INTERID)
            + ' */'
        PRINT 'DELETE FROM ' + RTRIM(@INTERID) + '..AAG02000 '
        PRINT 'INSERT INTO ' + RTRIM(@INTERID)
            + '.dbo.AAG02000 (USERID, aaTrxDimID, aaTrxDimCodeID, aaDistribute, aaAdjust)
	(SELECT B.USERID, A.aaTrxDimID, A.aaTrxDimCodeID, 1, 1 FROM '
            + RTRIM(@INTERID)
            + '..AAG00401 A CROSS JOIN DYNAMICS..SY01400 B) '
 
        FETCH NEXT FROM crCompanies INTO @INTERID
    END
 
CLOSE crCompanies
 
DEALLOCATE crCompanies

Note that this script will delete any access records in the table and re-define access to all the users in that company to all the transaction dimension and codes.

This script is free for use and does not cover any warranties. As always, please have a backup of your DYNAMICS and company databases before you execute this script. Also make sure all users have logged out of GP to ensure no data discrepancy occurs. :)

Hope this script helps you all… Until next post…

May 11, 2010 · veeyeskay · 12 Comments
Tags: , ,  · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 3,819

SQL Script for Revaluation Process

Hi all

During this week, I just thought of writing a simple SQL script to calculate the gain (or) loss for a particular currency/account combination based on a specific date that has been provided. The script has been written for the following assumptions.

  • All accounts revalue at Period Balances
  • Revaluation is done using an Exchange Table ID defined with “AVG” prefix.
  • Exchange Rate is determined by the date that is passed into the procedure for the exchange table defined in the previous step.Here is the script.
DECLARE @ENDDATE AS DATETIME
 
SET @ENDDATE = '2017-04-12'
 
SELECT  A.CURNCYID,
        B.ACTNUMST,
        ( SELECT    C.XCHGRATE
          FROM      DYNAMICS.dbo.MC00100 C
                    INNER JOIN DYNAMICS.dbo.MC40300 D
                        ON C.EXGTBLID = D.EXGTBLID
                           AND C.EXGTBLID IN (
                           SELECT   EXGTBLID
                           FROM     DYNAMICS.dbo.MC60200
                           WHERE    CMPANYID = ( SELECT CMPANYID
                                                 FROM   DYNAMICS.dbo.SY01500
                                                 WHERE  INTERID = DB_NAME()
                                               ) )
                           AND C.CURNCYID = A.CURNCYID
                           AND C.EXGTBLID LIKE '%-AVG'
                           AND C.EXCHDATE = @ENDDATE
        ) AS MONTHENDRATE,
        AVERAGERATE = CASE WHEN SUM(A.ORDBTAMT - A.ORCRDAMT) <> 0
                                AND ( SELECT    D.RTCLCMTD
                                      FROM      DYNAMICS.dbo.MC00100 C
                                                INNER JOIN DYNAMICS.dbo.MC40300 D
                                                    ON C.EXGTBLID = D.EXGTBLID
                                                       AND C.EXGTBLID IN (
                                                       SELECT   EXGTBLID
                                                       FROM     DYNAMICS.dbo.MC60200
                                                       WHERE    CMPANYID = ( SELECT CMPANYID
                                                                             FROM   DYNAMICS.dbo.SY01500
                                                                             WHERE  INTERID = DB_NAME()
                                                                           ) )
                                                       AND C.CURNCYID = A.CURNCYID
                                                       AND C.EXGTBLID LIKE '%-AVG'
                                                       AND C.EXCHDATE = @ENDDATE
                                    ) = 0
                           THEN ROUND(( SUM(DEBITAMT - CRDTAMNT)
                                        / SUM(A.ORDBTAMT - A.ORCRDAMT) ), 7)
                           WHEN SUM(DEBITAMT - CRDTAMNT) <> 0
                                AND ( SELECT    D.RTCLCMTD
                                      FROM      DYNAMICS.dbo.MC00100 C
                                                INNER JOIN DYNAMICS.dbo.MC40300 D
                                                    ON C.EXGTBLID = D.EXGTBLID
                                                       AND C.EXGTBLID IN (
                                                       SELECT   EXGTBLID
                                                       FROM     DYNAMICS.dbo.MC60200
                                                       WHERE    CMPANYID = ( SELECT CMPANYID
                                                                             FROM   DYNAMICS.dbo.SY01500
                                                                             WHERE  INTERID = DB_NAME()
                                                                           ) )
                                                       AND C.CURNCYID = A.CURNCYID
                                                       AND C.EXGTBLID LIKE '%-AVG'
                                                       AND C.EXCHDATE = @ENDDATE
                                    ) = 1
                           THEN ROUND(( SUM(A.ORDBTAMT - A.ORCRDAMT)
                                        / SUM(DEBITAMT - CRDTAMNT) ), 7)
                           ELSE 0
                      END,
        SUM(A.ORDBTAMT - A.ORCRDAMT) AS ORIGBALANCE,
        SUM(A.DEBITAMT - A.CRDTAMNT) AS FUNCBALANCE,
        NEWFUNCBALANCE = CASE WHEN SUM(A.ORDBTAMT - A.ORCRDAMT) <> 0
                                   AND ( SELECT D.RTCLCMTD
                                         FROM   DYNAMICS.dbo.MC00100 C
                                                INNER JOIN DYNAMICS.dbo.MC40300 D
                                                    ON C.EXGTBLID = D.EXGTBLID
                                                       AND C.EXGTBLID IN (
                                                       SELECT   EXGTBLID
                                                       FROM     DYNAMICS.dbo.MC60200
                                                       WHERE    CMPANYID = ( SELECT CMPANYID
                                                                             FROM   DYNAMICS.dbo.SY01500
                                                                             WHERE  INTERID = DB_NAME()
                                                                           ) )
                                                       AND C.CURNCYID = A.CURNCYID
                                                       AND C.EXGTBLID LIKE '%-AVG'
                                                       AND C.EXCHDATE = @ENDDATE
                                       ) = 0
                              THEN ( SUM(A.ORDBTAMT - A.ORCRDAMT)
                                     * ( SELECT C.XCHGRATE
                                         FROM   DYNAMICS.dbo.MC00100 C
                                                INNER JOIN DYNAMICS.dbo.MC40300 D
                                                    ON C.EXGTBLID = D.EXGTBLID
                                                       AND C.EXGTBLID IN (
                                                       SELECT   EXGTBLID
                                                       FROM     DYNAMICS.dbo.MC60200
                                                       WHERE    CMPANYID = ( SELECT CMPANYID
                                                                             FROM   DYNAMICS.dbo.SY01500
                                                                             WHERE  INTERID = DB_NAME()
                                                                           ) )
                                                       AND C.CURNCYID = A.CURNCYID
                                                       AND C.EXGTBLID LIKE '%-AVG'
                                                       AND C.EXCHDATE = @ENDDATE
                                       ) )
                              WHEN SUM(A.ORDBTAMT - A.ORCRDAMT) <> 0
                                   AND ( SELECT D.RTCLCMTD
                                         FROM   DYNAMICS.dbo.MC00100 C
                                                INNER JOIN DYNAMICS.dbo.MC40300 D
                                                    ON C.EXGTBLID = D.EXGTBLID
                                                       AND C.EXGTBLID IN (
                                                       SELECT   EXGTBLID
                                                       FROM     DYNAMICS.dbo.MC60200
                                                       WHERE    CMPANYID = ( SELECT CMPANYID
                                                                             FROM   DYNAMICS.dbo.SY01500
                                                                             WHERE  INTERID = DB_NAME()
                                                                           ) )
                                                       AND C.CURNCYID = A.CURNCYID
                                                       AND C.EXGTBLID LIKE '%-AVG'
                                                       AND C.EXCHDATE = @ENDDATE
                                       ) = 1
                              THEN ( SUM(A.ORDBTAMT - A.ORCRDAMT)
                                     / ( SELECT C.XCHGRATE
                                         FROM   DYNAMICS.dbo.MC00100 C
                                                INNER JOIN DYNAMICS.dbo.MC40300 D
                                                    ON C.EXGTBLID = D.EXGTBLID
                                                       AND C.EXGTBLID IN (
                                                       SELECT   EXGTBLID
                                                       FROM     DYNAMICS.dbo.MC60200
                                                       WHERE    CMPANYID = ( SELECT CMPANYID
                                                                             FROM   DYNAMICS.dbo.SY01500
                                                                             WHERE  INTERID = DB_NAME()
                                                                           ) )
                                                       AND C.CURNCYID = A.CURNCYID
                                                       AND C.EXGTBLID LIKE '%-AVG'
                                                       AND C.EXCHDATE = @ENDDATE
                                       ) )
                              ELSE 0
                         END,
        GAINLOSS = ROUND(( ( CASE WHEN SUM(A.ORDBTAMT - A.ORCRDAMT) <> 0
                                       AND ( SELECT D.RTCLCMTD
                                             FROM   DYNAMICS.dbo.MC00100 C
                                                    INNER JOIN DYNAMICS.dbo.MC40300 D
                                                        ON C.EXGTBLID = D.EXGTBLID
                                                           AND C.EXGTBLID IN (
                                                           SELECT   EXGTBLID
                                                           FROM     DYNAMICS.dbo.MC60200
                                                           WHERE    CMPANYID = ( SELECT CMPANYID
                                                                                 FROM   DYNAMICS.dbo.SY01500
                                                                                 WHERE  INTERID = DB_NAME()
                                                                               ) )
                                                           AND C.CURNCYID = A.CURNCYID
                                                           AND C.EXGTBLID LIKE '%-AVG'
                                                           AND C.EXCHDATE = @ENDDATE
                                           ) = 0
                                  THEN ( SUM(A.ORDBTAMT - A.ORCRDAMT)
                                         * ( SELECT C.XCHGRATE
                                             FROM   DYNAMICS.dbo.MC00100 C
                                                    INNER JOIN DYNAMICS.dbo.MC40300 D
                                                        ON C.EXGTBLID = D.EXGTBLID
                                                           AND C.EXGTBLID IN (
                                                           SELECT   EXGTBLID
                                                           FROM     DYNAMICS.dbo.MC60200
                                                           WHERE    CMPANYID = ( SELECT CMPANYID
                                                                                 FROM   DYNAMICS.dbo.SY01500
                                                                                 WHERE  INTERID = DB_NAME()
                                                                               ) )
                                                           AND C.CURNCYID = A.CURNCYID
                                                           AND C.EXGTBLID LIKE '%-AVG'
                                                           AND C.EXCHDATE = @ENDDATE
                                           ) )
                                  WHEN SUM(A.ORDBTAMT - A.ORCRDAMT) <> 0
                                       AND ( SELECT D.RTCLCMTD
                                             FROM   DYNAMICS.dbo.MC00100 C
                                                    INNER JOIN DYNAMICS.dbo.MC40300 D
                                                        ON C.EXGTBLID = D.EXGTBLID
                                                           AND C.EXGTBLID IN (
                                                           SELECT   EXGTBLID
                                                           FROM     DYNAMICS.dbo.MC60200
                                                           WHERE    CMPANYID = ( SELECT CMPANYID
                                                                                 FROM   DYNAMICS.dbo.SY01500
                                                                                 WHERE  INTERID = DB_NAME()
                                                                               ) )
                                                           AND C.CURNCYID = A.CURNCYID
                                                           AND C.EXGTBLID LIKE '%-AVG'
                                                           AND C.EXCHDATE = @ENDDATE
                                           ) = 1
                                  THEN ( SUM(A.ORDBTAMT - A.ORCRDAMT)
                                         / ( SELECT C.XCHGRATE
                                             FROM   DYNAMICS.dbo.MC00100 C
                                                    INNER JOIN DYNAMICS.dbo.MC40300 D
                                                        ON C.EXGTBLID = D.EXGTBLID
                                                           AND C.EXGTBLID IN (
                                                           SELECT   EXGTBLID
                                                           FROM     DYNAMICS.dbo.MC60200
                                                           WHERE    CMPANYID = ( SELECT CMPANYID
                                                                                 FROM   DYNAMICS.dbo.SY01500
                                                                                 WHERE  INTERID = DB_NAME()
                                                                               ) )
                                                           AND C.CURNCYID = A.CURNCYID
                                                           AND C.EXGTBLID LIKE '%-AVG'
                                                           AND C.EXCHDATE = @ENDDATE
                                           ) )
                                  ELSE 0
                             END ) - SUM(A.DEBITAMT - A.CRDTAMNT) ), 2)
FROM    dbo.GL20000 A
        INNER JOIN dbo.GL00105 B
            ON A.ACTINDX = B.ACTINDX
        INNER JOIN dbo.MC00200 C
            ON A.ACTINDX = C.ACTINDX
               AND C.CURNCYID = ''
WHERE   A.TRXDATE <= @ENDDATE
        AND OPENYEAR = YEAR(@ENDDATE)
        AND A.CURNCYID <> ( SELECT  FUNLCURR
                            FROM    MC40000
                          )
        AND C.REVALUE = 1
GROUP BY A.CURNCYID,
        B.ACTNUMST
ORDER BY A.CURNCYID,
        B.ACTNUMST

This script can be modified to suit your requirements. Revaluation is a standard process in GP, but I just tried to write a script to replicate how revaluation works in Dynamics GP, and that resulted in the script above. I hope you find this script useful to understand how revaluation works in GP.

Until next post…

March 18, 2010 · veeyeskay · One Comment
Tags: , , , , , ,  · Posted in: Dynamics, General Ledger, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 2,019

Macro to provide access to Transaction Dimension Codes to Users

Hi all

In this article, I am going to publish a SQL script which will be helpful to many using Analytical Accounting module.

I have come across many clients who have multiple companies and they have hundreds of users spread across each of these companies. Assume the case when a new dimension code value has to be added to all these companies, you can add these codes using the UI. But the main part which is difficult here is to grant access to all the users in that company (assuming all users need access to the newly created dimension code). This task can be tiring if you need to login to each company and granting access to the specific dimension code to all the users in that company.

Note: My script below assumes that all users in a specific company has access to all the dimension codes that are setup in that company. And my script is based on the fact that there is only one dimension which has been setup in the company.

 
DECLARE @COMPANYID INT
DECLARE @COMPANYNAME CHAR(65)
DECLARE @RECORDCOUNT INT
DECLARE @USERID CHAR(15)
 
BEGIN
    SET @RECORDCOUNT = 0
    PRINT '# DEXVERSION=10.0.324.0 2 2'
    DECLARE cr_Companies CURSOR
        FOR SELECT  CMPANYID,
                    CMPNYNAM
            FROM    DYNAMICS.dbo.SY01500
            ORDER BY INTERID
 
    OPEN cr_Companies
 
    FETCH NEXT FROM cr_Companies INTO @COMPANYID, @COMPANYNAME
 
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @RECORDCOUNT = @RECORDCOUNT + 1
            IF @RECORDCOUNT > 1
                BEGIN
                    PRINT 'CloseWindow dictionary ''Analytical Accounting''  form aaUserAccToCodes window aaUserAccToCodes '
                    PRINT 'NewActiveWin dictionary ''default''  form sheLL window sheLL'
                    PRINT 'CommandExec dictionary ''default''  form ''Command_System'' command CompanyName '
                    PRINT 'NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company'' '
                    PRINT '  ClickHit field ''(L) Company Names'' item '
                        + LTRIM(RTRIM(STR(@COMPANYID))) + '  # '''
                        + LTRIM(RTRIM(@COMPANYNAME)) + ''' '
                    PRINT '  MoveTo field ''OK Button'' '
                    PRINT '  ClickHit field ''OK Button'''
                    PRINT 'NewActiveWin dictionary ''default''  form sheLL window sheLL '
                    PRINT '  CommandExec dictionary ''Analytical Accounting''  form ''Command_AA'' command aaUserAccess '
                    PRINT 'NewActiveWin dictionary ''Analytical Accounting''  form aaUserAccToCodes window aaUserAccToCodes'
                END
            ELSE
                BEGIN
                    PRINT 'NewActiveWin dictionary ''default''  form sheLL window sheLL'
                    PRINT 'CommandExec dictionary ''default''  form ''Command_System'' command CompanyName '
                    PRINT 'NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company'' '
                    PRINT '  ClickHit field ''(L) Company Names'' item '
                        + LTRIM(RTRIM(STR(@COMPANYID))) + '  # '''
                        + LTRIM(RTRIM(@COMPANYNAME)) + ''' '
                    PRINT '  MoveTo field ''OK Button'' '
                    PRINT '  ClickHit field ''OK Button'''
                    PRINT 'NewActiveWin dictionary ''default''  form sheLL window sheLL '
                    PRINT '  CommandExec dictionary ''Analytical Accounting''  form ''Command_AA'' command aaUserAccess '
                    PRINT 'NewActiveWin dictionary ''Analytical Accounting''  form aaUserAccToCodes window aaUserAccToCodes'
                END
            DECLARE cr_Users CURSOR
                FOR SELECT  USERID
                    FROM    DYNAMICS.dbo.SY60100
                    WHERE   CMPANYID = @COMPANYID
                    ORDER BY USERID
            OPEN cr_Users
            FETCH NEXT FROM cr_Users INTO @USERID
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    PRINT '  ClickHit field ''(L) UserOption'' item 2  # ''User ID'' '
                    PRINT '  MoveTo field ''User ID'' '
                    PRINT '  TypeTo field ''User ID'' , '''
                        + LTRIM(RTRIM(@USERID)) + ''''
                    PRINT '  MoveTo field aaTrxDimDescr2 '
                    PRINT '  TypeTo field aaTrxDimDescr2 , ''TESTDIMCODE'''
                    PRINT '  MoveTo field aaDistribute  # ''FALSE'''
                    PRINT '  ClickHit field aaDistribute  # ''TRUE'''
                    PRINT '  MoveTo field aaAdjust  # ''FALSE'''
                    PRINT '  ClickHit field aaAdjust  # ''TRUE'''
                    PRINT '  MoveTo field ''Save Button'' '
                    PRINT '  ClickHit field ''Save Button'''
                    FETCH NEXT FROM cr_Users INTO @USERID
                END
            CLOSE cr_Users
            DEALLOCATE cr_Users
            FETCH NEXT FROM cr_Companies INTO @COMPANYID, @COMPANYNAME
        END
    PRINT 'CloseWindow dictionary ''Analytical Accounting''  form aaUserAccToCodes window aaUserAccToCodes '
    PRINT 'NewActiveWin dictionary ''default''  form sheLL window sheLL'
    CLOSE cr_Companies
    DEALLOCATE cr_Companies
END

This script is written for GP v10 SP4, and it generates the contents of the macro, which logs into all the companies that have been setup in DYNAMICS and grants access to the dimension code added in all the companies to all the users belonging to that company. Once the script generates the macro code, copy the code and save it into a notepad file and save it as a file with a .MAC extension and open the macro file from GP, and sit back and relax. :) The macro would do all for you…

This macro code can be modified to suit individual business requirement and I am sure this will be a good starter for many… :)

Note:

GP 2010 has got a slight enhancement to this functionality but not what exactly what the script above achieves. A little write up on this below.

In Dynamics GP 2010, when a user tries to add a dimension code on the fly, the system automatically grants access to that particular user and not to all users in that company. And also, this happens only when the dimension code is added on the fly from a transaction window and not when a dimension code is created from the Transaction Dimension Code Maintenance window. Even in GP 2010, if the user creates a dimension code from the Maintenance window it does NOT grant access to that code to any users in that company.

The only enhancement is that when a user tries to add a new dimension code from the transaction on the fly, earlier versions did not give access by default even to the user who created it from the transaction level, which prevented the user from tabbing off the field on the AA transaction window, and the user has to go exclusively to the User Access window to grant him access so that he can tab off. This was a problem since not all users would have access to the User Access window in AA.

So this script of mine, would still be useful in the event of users starting to use GP 2010 and AA, since this script grants access to all dimension codes to all users in that company. :)

Hope this information is helpful.

Until next post…

March 3, 2010 · veeyeskay · 6 Comments
Tags: , , , ,  · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 4,036

SQL View for Payables Transactions with AA Codes

Guys

There is a smartlist object for AP transactions, but this smartlist does not show the analytical accounting information in it.

So in this post, I have decided to post a SQL view to generate a view to show AP transaction information along with the analytical accounting information.

 
IF EXISTS ( SELECT  *
            FROM    sys.views
            WHERE   object_id = OBJECT_ID(N'[dbo].[vw_AAG20000]') ) 
    DROP VIEW  [dbo].[vw_AAG20000]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [dbo].[vw_AAG20000]
AS  SELECT  [aaSubLedgerHdrID],
            [SERIES],
            [DOCTYPE] = CASE WHEN [DOCTYPE] = 0 THEN 1
                             WHEN [DOCTYPE] = 1 THEN 6
                             ELSE 5
                        END,
            [DOCNUMBR],
            [Master_ID],
            [aaHdrErrors],
            [DEX_ROW_ID]
    FROM    [dbo].[AAG20000]
 
GO
 
IF EXISTS ( SELECT  *
            FROM    sys.views
            WHERE   object_id = OBJECT_ID(N'[dbo].[vw_APTransactions]') ) 
    DROP VIEW  [dbo].[vw_APTransactions]
 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [dbo].[vw_APTransactions]
AS  SELECT  *,
            'Open' AS STATUS
    FROM    dbo.PM20000
    UNION ALL
    SELECT  *,
            'History' AS STATUS
    FROM    dbo.PM30200
 
GO
 
IF EXISTS ( SELECT  *
            FROM    sys.views
            WHERE   object_id = OBJECT_ID(N'[dbo].[vw_APAAInformation]') ) 
    DROP VIEW  [dbo].[vw_APAAInformation]
 
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [dbo].[vw_APAAInformation]
AS  SELECT  A.VENDORID,
            I.VENDNAME,
            I.VNDCLSID AS VENDORCLASS,
            J.VNDCLDSC AS VENDORCLASSDESC,
            A.VCHRNMBR AS VOUCHERNO,
            A.DOCNUMBR AS INVOICENO,
            A.DOCDATE AS DOCUMENTDATE,
            A.PSTGDATE AS POSTINGDATE,
            G.ACTNUMST AS GLACCOUNTNO,
            H.ACTDESCR AS GPACCOUNTDESC,
            F.aaTrxDimCode AS DIMCODE,
            F.aaTrxDimCodeDescr AS DIMCODEDESC,
            ( D.DEBITAMT - D.CRDTAMNT ) AS AMOUNT,
            YEAR(A.PSTGDATE) AS TRXYEAR,
            'Week ' + REPLICATE('0', 2 - LEN({fn WEEK(A.PSTGDATE)}))
            + LTRIM(RTRIM(STR({fn WEEK(A.PSTGDATE)}))) AS WEEKNO,
            REPLICATE('0', 2 - LEN(MONTH(A.PSTGDATE)))
            + ( LTRIM(RTRIM(STR(MONTH(A.PSTGDATE)))) + ':'
                + ( LEFT({fn  MONTHNAME(A.PSTGDATE)}, 3) ) + ''''
                + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.PSTGDATE)))), 3, 2) ) AS PERIOD,
            QUARTERNO = CASE {fn QUARTER(A.PSTGDATE)}
                          WHEN 1
                          THEN 'Q1:Jan' + ''''
                               + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.PSTGDATE)))),
                                           3, 2) + '-Mar' + ''''
                               + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.PSTGDATE)))),
                                           3, 2)
                          WHEN 2
                          THEN 'Q2:Apr' + ''''
                               + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.PSTGDATE)))),
                                           3, 2) + '-Jun' + ''''
                               + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.PSTGDATE)))),
                                           3, 2)
                          WHEN 3
                          THEN 'Q3:Jul' + ''''
                               + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.PSTGDATE)))),
                                           3, 2) + '-Sep' + ''''
                               + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.PSTGDATE)))),
                                           3, 2)
                          ELSE 'Q4:Oct' + ''''
                               + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.PSTGDATE)))),
                                           3, 2) + '-Dec' + ''''
                               + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.PSTGDATE)))),
                                           3, 2)
                        END,
            A.[STATUS] AS STATUS,
            DOCUMENTTYPE = CASE WHEN A.DOCTYPE = 1 THEN 'Invoices'
                                WHEN A.DOCTYPE = 5 THEN 'Credit Notes'
                                WHEN A.DOCTYPE = 6
                                     AND A.CHEKBKID = 'XXXXX' THEN 'Checks'
                                WHEN A.DOCTYPE = 6
                                     AND A.CHEKBKID = 'YYYYY' THEN 'ACH'
                                WHEN A.DOCTYPE = 6
                                     AND A.CHEKBKID = 'ZZZZZ' THEN 'Wires'
                                ELSE 'Miscellaneous'
                           END,
            A.CHEKBKID AS CHECKBOOK
    FROM    dbo.vw_APTransactions A
            INNER JOIN dbo.vw_AAG20000 B ON B.DOCNUMBR = A.VCHRNMBR
                                            AND B.DOCTYPE = A.DOCTYPE
            INNER JOIN dbo.AAG20001 C ON B.aaSubLedgerHdrID = C.aaSubLedgerHdrID
            INNER JOIN dbo.AAG20002 D ON C.aaSubLedgerHdrID = D.aaSubLedgerHdrID
                                         AND C.aaSubLedgerDistID = D.aaSubLedgerDistID
            INNER JOIN dbo.AAG20003 E ON D.aaSubLedgerHdrID = E.aaSubLedgerHdrID
                                         AND D.aaSubLedgerDistID = E.aaSubLedgerDistID
                                         AND D.aaSubLedgerAssignID = E.aaSubLedgerAssignID
            INNER JOIN dbo.AAG00401 F ON E.aaTrxDimID = F.aaTrxDimID
                                         AND E.aaTrxCodeID = F.aaTrxDimCodeID
            INNER JOIN dbo.GL00105 G ON C.ACTINDX = G.ACTINDX
            INNER JOIN dbo.GL00100 H ON H.ACTINDX = G.ACTINDX
            INNER JOIN dbo.PM00200 I ON I.VENDORID = A.VENDORID
            LEFT OUTER JOIN dbo.PM00100 J ON I.VNDCLSID = J.VNDCLSID
    WHERE   A.VOIDED <> 1
            AND A.DOCTYPE <> 6
 
GO
 
GRANT SELECT, INSERT, UPDATE, DELETE ON vw_AAG20000 TO DYNGRP
GRANT SELECT, INSERT, UPDATE, DELETE ON vw_APTransactions TO DYNGRP
GRANT SELECT, INSERT, UPDATE, DELETE ON vw_APAAInformation TO DYNGRP

Hope this post was helpful to you.

Until next post…

March 1, 2010 · veeyeskay · 11 Comments
Tags: , , , ,  · Posted in: Accounts Payables, Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 2,504