SQL Script to List Actual vs Budget for GL Accounts by Period

A few months back one of my customers asked me to create a script that shows net actual amount and total budget amount for a particular account and for a specific year. After working on the script, I thought about expanding the script and create a view to listing all General Ledger accounts with actual amount, budgeted amount and the variance by year and period.

The view below links GL20000 (GL_YTD_TRX_OPEN – Year-to-Date Transaction Open) and GL00201 (GL_Budget_SUM_MSTR – Budget Summary Master) and GL00105 (GL_Account_Index_MSTR – Account Index Master). The variance is a calculated field (actual – budget).

CREATE VIEW view_GL_Actual_vs_Budget_by_Period
AS
    -------------------------------------------------------------------------
    -- Created by    :    Samuel Mathew, Eclipse Computing, Inc.
    -- Date            :    04/08/2013
    -- Description    :    Displays Actual, budget and variance for all accounts
    --                    by year and period.
    -------------------------------------------------------------------------
    SELECT GL00105.ACTNUMST AS 'ACCOUNT',
           GL00201.YEAR1 AS 'YEAR',
           GL00201.PERIODID AS 'PERIOD',
           ISNULL(tSQL.ACTUAL, 0) AS 'ACTUAL',
           GL00201.BUDGETAMT AS 'BUDGET',
           (ISNULL(tSQL.ACTUAL, 0) - GL00201.BUDGETAMT) AS VARIANCE
    FROM   GL00201
           INNER JOIN
           GL00105
           ON GL00201.ACTINDX = GL00105.ACTINDX
           LEFT OUTER JOIN
           (SELECT   GL00105.ACTINDX,
                     GL20000.OPENYEAR,
                     GL20000.PERIODID AS 'PERIOD',
                     SUM(GL20000.DEBITAMT) - SUM(GL20000.CRDTAMNT) AS 'ACTUAL'
            FROM     GL20000
                     INNER JOIN
                     GL00105
                     ON GL20000.ACTINDX = GL00105.ACTINDX
            WHERE    (GL20000.SOURCDOC <> 'BBF')
            GROUP BY GL00105.ACTINDX, GL20000.OPENYEAR, GL20000.PERIODID) AS tSQL
           ON tSQL.PERIOD = GL00201.PERIODID
              AND tSQL.OPENYEAR = GL00201.YEAR1
              AND tSQL.ACTINDX = GL00201.ACTINDX;
 
 
GO
GRANT select
    ON view_GL_Actual_vs_Budget_by_Period TO DYNGRP;