View - dbo.smc_GlSummaryBalances

Property	Value
Name	smc_GlSummaryBalances
Schema	dbo
Quoted Identifier Status	True
Ansi Nulls Status	True
Returns View Metadata	False
Create Date	10/12/2015 11:52 AM
Description

Column Name	Data Type	Allow Nulls	Fulltext Indexed	Identity	Description / Computed Formula
Account	varchar(8)	True	False	False
Amount	numeric(17, 2)	False	False	False
BudgetRef	varchar(8)	True	False	False
CalendarPeriod	int(10, 0)	True	False	False
ChartField1	varchar(9)	True	False	False
ClassField	varchar(4)	True	False	False
Commitment	numeric(17, 2)	False	False	False
Course	varchar(8)	True	False	False
DeptId	varchar(6)	True	False	False
Encumbrance	numeric(17, 2)	False	False	False
FiscalYear	int(10, 0)	True	False	False
Fund	varchar(3)	True	False	False
OperatingUnit	varchar(4)	True	False	False
PoolAllocation	numeric(17, 2)	False	False	False
Project	int(10, 0)	True	False	False

Name

Insert

Update

Delete

Instead

Name

Clustered

Columns

Name	Type
COA_ORG_UNIT_FILE	Table
GENERAL_LEDGER_FILE	Table
GENERAL_LEDGER_FILE_MONTHLY_ACCUMS	Table
SMC_AccountMapping	Table

Name	Type
COA_ORG_UNIT_FILE	TABLE
GENERAL_LEDGER_FILE	TABLE
GENERAL_LEDGER_FILE_MONTHLY_ACCUMS	TABLE
SMC_AccountMapping	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: View [dbo].[smc_GlSummaryBalances] Script Date: 03/09/2017 16:47:09 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[smc_GlSummaryBalances]
AS
SELECT
       CAST(Ledger.FISCAL_YEAR AS Int) AS FiscalYear
      ,CASE
         WHEN Monthly.cnxarraycolumn = 1 THEN CAST(CAST((CAST(Ledger.FISCAL_YEAR as Int) - 1) as varchar(4)) + '07' AS int)
         WHEN Monthly.cnxarraycolumn = 2 THEN CAST(CAST((CAST(Ledger.FISCAL_YEAR as Int) - 1) as varchar(4)) + '08' AS int)
         WHEN Monthly.cnxarraycolumn = 3 THEN CAST(CAST((CAST(Ledger.FISCAL_YEAR as Int) - 1) as varchar(4)) + '09' AS int)
         WHEN Monthly.cnxarraycolumn = 4 THEN CAST(CAST((CAST(Ledger.FISCAL_YEAR as Int) - 1) as varchar(4)) + '10' AS int)
         WHEN Monthly.cnxarraycolumn = 5 THEN CAST(CAST((CAST(Ledger.FISCAL_YEAR as Int) - 1) as varchar(4)) + '11' AS int)
         WHEN Monthly.cnxarraycolumn = 6 THEN CAST(CAST((CAST(Ledger.FISCAL_YEAR as Int) - 1) as varchar(4)) + '12' AS int)
         WHEN Monthly.cnxarraycolumn = 7 THEN CAST(Ledger.FISCAL_YEAR + '01'  AS int)
         WHEN Monthly.cnxarraycolumn = 8 THEN CAST(Ledger.FISCAL_YEAR + '02'  AS int)
         WHEN Monthly.cnxarraycolumn = 9 THEN CAST(Ledger.FISCAL_YEAR + '03'  AS int)
         WHEN Monthly.cnxarraycolumn = 10 THEN CAST(Ledger.FISCAL_YEAR + '04' AS int)
         WHEN Monthly.cnxarraycolumn = 11 THEN CAST(Ledger.FISCAL_YEAR + '05' AS int)
         WHEN Monthly.cnxarraycolumn = 12 THEN CAST(Ledger.FISCAL_YEAR + '06' AS int)
         ELSE '190001'
       END as CalendarPeriod
      ,AM.FundCode AS Fund
      ,AM.ChartField1 AS ChartField1
      ,SUBSTRING(Ledger.GL_ACCT_NO,17,5) + '000' AS Account
      ,AM.DeptId AS DeptId
      ,AM.ClassField AS ClassField
      ,AM.OperatingUnit AS OperatingUnit
      ,AM.BudgetRef
      ,CASE
         WHEN SUBSTRING(Ledger.GL_ACCT_NO,17,1) = '6' THEN Coa.PROJECT_NO
         WHEN SUBSTRING(Ledger.GL_ACCT_NO,17,1) = '7' THEN Coa.PROJECT_NO
       END AS Project
      ,CASE
         WHEN LEFT(Ledger.GL_ACCT_NO,8) = '11320200' THEN '8715100'
         ELSE  AM.Course
       END AS Course
      ,ISNULL(Monthly.AMOUNT, 0) AS Amount
      ,ISNULL(Monthly.COMMITMENT, 0) AS Commitment
      ,ISNULL(Monthly.ENCUMBRANCE,0) AS Encumbrance
      ,ISNULL(POOL_ALLOCATION,0) AS PoolAllocation

  FROM GENERAL_LEDGER_FILE Ledger
  INNER JOIN SMC_AccountMapping AM
ON
(
    SUBSTRING(Ledger.GL_ACCT_NO, 9, 6) = AM.Qual1
)
LEFT OUTER JOIN COA_ORG_UNIT_FILE Coa
ON
(
    Coa.ORG_IND = 'Y'
    AND Coa.ORG_UNIT = LEFT(Ledger.GL_ACCT_NO,16)
)
INNER JOIN GENERAL_LEDGER_FILE_MONTHLY_ACCUMS Monthly
ON
   (Ledger.ISN_GENERAL_LEDGER_FILE = Monthly.ISN_GENERAL_LEDGER_FILE
    and cnxarraycolumn > 0
     AND
     (  Monthly.AMOUNT IS NOT NULL
        OR Monthly.ENCUMBRANCE IS NOT NULL
        OR Monthly.COMMITMENT IS NOT NULL
        OR Monthly.POOL_ALLOCATION IS NOT NULL
     )
)