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…

VN:F [1.9.13_1145]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.13_1145]
Rating: 0 (from 0 votes)
SQL Script for Revaluation Process, 10.0 out of 10 based on 1 rating

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: 707