DWStaging
 usp_DimGlTransDetail_Select (Stored Procedure)
Home  Diagrams  Documents  Images  DWStaging  Storage  Users  Roles  Schemas  Tables  Views  Indexes  Triggers  Stored Procedures  Functions  Data Types  XML Schemas  Rules  Defaults  Fulltext Catalogs  Synonyms  Keys  Partitions  Assemblies 
Current Path:  DWStaging   Stored Procedures   usp_DimGlTransDetail_Select
  Properties
Property Value
Name usp_DimGlTransDetail_Select
Schema dbo
Is Encrypted False
Ansi Nulls Status True
Quoted Identifier Status True
Description
  Parameters
Name Data Type Direction Description
datetime(23, 3)
Input
datetime(23, 3)
Input
varchar(8)
Input
  Parent Dependencies (objects that usp_DimGlTransDetail_Select depends on)
Name Type
Table
Table
  Child Dependencies (objects that depend on usp_DimGlTransDetail_Select)
Name Type
TABLE
TABLE
  Extended Properties
Object Property Value
   Annotations
Object Property Value
  DDL
/****** Object: StoredProcedure [dbo].[usp_DimGlTransDetail_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/* Primary Student Source SELECT Statement including Incremental Modifications*/
/* Sophia Cowan 12/16/2013 Removed Reference to DWStaging from GL_TRANS Select */
/* Sophia Cowan 02/01/2016 Add StartCalendarPeriod */
CREATE PROC [dbo].[usp_DimGlTransDetail_Select]
@IncrementalBeginDtTm datetime, /* Date and Time to validate against RowUpdatedOn in Source Tables */
@IncrementalEndDtTm datetime,
@StartCalendarPeriod varchar(8)
AS 
SELECT 
       CAST(CAST(GlTrans.CALENDAR_PERIOD as varchar) + GlTrans.JOURNAL_TYPE + 
         REPLICATE('0',5-LEN(GlTrans.JOURNAL_NO))+CONVERT(VARCHAR,GlTrans.JOURNAL_NO) +
         REPLICATE('0',5-LEN(GlTrans.LINE_NO))+CONVERT(VARCHAR,GlTrans.LINE_NO) as Varchar(18))
         as GlDetailAK
      ,GlTrans.CALENDAR_PERIOD AS GlCalendarPeriod
      ,GlTrans.JOURNAL_NO AS JournalNumber
      ,GlTrans.LINE_NO AS LineNumber
      ,GlTrans.JOURNAL_TYPE as JournalTypeCode
      ,CAST(ISNULL(JournalDesc.TABLE_DESC, 'N/A') AS varchar(30)) AS JournalType
      ,CASE  
        WHEN GlTrans.JOURNAL_TYPE = 'EN' THEN 'Encumbrance'
        WHEN GlTrans.JOURNAL_TYPE = 'CO' THEN 'Commitments'
        WHEN GlTrans.JOURNAL_TYPE = 'BT' THEN 'Budget'
        ELSE 'Actuals'
        END as JournalGrouping
      ,CASE
        WHEN GlTrans.JOURNAL_TYPE = 'BT' THEN ISNULL(BUDGET_TYPE_CODE, 'N\A')
        ELSE 'N/A' 
       END as BudgetTypeCode
      ,CASE
         WHEN GlTrans.JOURNAL_TYPE = 'BT' THEN CAST(ISNULL(BudgetDesc.TABLE_DESC, 'N/A')AS varchar(30)) 
         ELSE 'N/A' 
       END AS BudgetType
  FROM GL_TRANS_FILE AS GlTrans
LEFT OUTER JOIN FIG_TABLES_FILE JournalDesc
   ON (
       JournalDesc.TABLE_NAME = 'FIGADM10' 
            AND JournalDesc.TABLE_CODE = GlTrans.JOURNAL_TYPE
       )              
  
LEFT OUTER JOIN FIG_TABLES_FILE BudgetDesc
   ON (
       BudgetDesc.TABLE_NAME = 'FIGADM9' 
            AND BudgetDesc.TABLE_CODE = GlTrans.BUDGET_TYPE_CODE
       )              
  
WHERE (GlTrans.ISN_GL_TRANS_FILE) IN
(
/* Select changed records from Student File */
SELECT ISN_GL_TRANS_FILE
FROM GL_TRANS_FILE
WHERE (
       GL_TRANS_FILE.RowUpdatedOn >= @IncrementalBeginDtTm  
       AND GL_TRANS_FILE.RowUpdatedOn <= @IncrementalEndDtTm
       AND GL_TRANS_FILE.CALENDAR_PERIOD >= @StartCalendarPeriod
      )
)      
Powered by BI Documenter