DimGlCodeLoad
 OLE_SRC Verification Temp table from COA_ORG_UNIT_FILE Staging (Component)
Home  Diagrams  Documents  Images  DimGlCodeLoad  Executables  Connection Managers  Log Providers  Variables 
Current Path:  DimGlCodeLoad   DFT Load TempGl Code Table for Verification   OLE_SRC Verification Temp table from COA_ORG_UNIT_FILE Staging
  Properties
Property Value
Name OLE_SRC Verification Temp table from COA_ORG_UNIT_FILE Staging
Description OLE DB Source
Up Stream Components none
Down Stream Components
ID 1
Component Class ID {BCEFE59B-6819-47F7-A125-63753B33ABB7}
Contact Info OLE DB Source;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7
Identification String component "OLE_SRC Verification Temp table from COA_ORG_UNIT_FILE Staging" (1)
Is Default Locale True
Pipeline Version 0
Uses Dispositions True
Validate External Metadata True
Version 7
  Connections
Name Connection Manager Description
OleDbConnection
  Custom Properties
Name Value
AccessMode
2
AlwaysUseDefaultCodePage
False
CommandTimeout
0
DefaultCodePage
1252
OpenRowset
OpenRowsetVariable
ParameterMapping
SqlCommand
SELECT 
       GlCode.GL_CODE as GlCodeAK
      ,SUBSTRING(GlCode.GL_CODE,1,5) AS Glc
      ,SUBSTRING(GlCode.GL_CODE,6,4) AS Qual2
      ,SUBSTRING(GlCode.GL_CODE,1,1) AS GlClassCode
      ,ISNULL(GlClassDesc.DESCRIPTION, 'N/A') as GlClass
      ,CAST(CASE
        WHEN SUBSTRING(GlCode.GL_CODE,1,1) = '5' THEN 'Expenses'
        WHEN SUBSTRING(GlCode.GL_CODE,1,1) = '6' THEN 'Expenses'
        WHEN SUBSTRING(GlCode.GL_CODE,1,1) = '7' THEN 'Expenses'
        ELSE ISNULL(GlClassDesc.DESCRIPTION, 'N/A')
        END AS Varchar(10)) AS     GlGroup  
      ,SUBSTRING(GlCode.GL_CODE,1,2) AS  GlCategoryCode
      ,ISNULL(GlCatDesc.DESCRIPTION, 'N/A') as GlCategory
      ,SUBSTRING(GlCode.GL_CODE,1,3) AS  GlSubCategoryCode
      ,ISNULL(GlSubCatDesc.DESCRIPTION, 'N/A') as GlSubCategory
      ,ISNULL(GlCode5.DESCRIPTION, 'N/A') as GlcDescription
      ,ISNULL(GlCode.DESCRIPTION, 'N/A') as GlCodeName
      ,CAST(ISNULL(GlSubTotalDesc.FIELD_VALUE, SUBSTRING(GlCode.GL_CODE,1,5)) as varchar(5)) as GlCodeSubTotalCode
      ,ISNULL(GlSubTotal.FIELD_VALUE, ISNULL(GlCode5.DESCRIPTION, 'N/A')) as GlCodeSubTotal
      ,CAST(ISNULL(GlTimeStatus.FIELD_VALUE, 'N/A') as varchar(30)) AS HrTimeStatus
      ,CAST(ISNULL(GlHrClassification.FIELD_VALUE, 'N/A') as varchar(10)) AS HrClassification       
      ,CAST(CASE
        WHEN SUBSTRING(GlCode.GL_CODE,1,1) = '1' THEN 1
        WHEN SUBSTRING(GlCode.GL_CODE,1,1) = '2' THEN -1
        WHEN SUBSTRING(GlCode.GL_CODE,1,1) = '4' THEN -1
        WHEN SUBSTRING(GlCode.GL_CODE,1,1) = '5' THEN 1
        WHEN SUBSTRING(GlCode.GL_CODE,1,1) = '6' THEN 1
        WHEN SUBSTRING(GlCode.GL_CODE,1,1) = '7' THEN 1
        WHEN (SUBSTRING(GlCode.GL_CODE,1,1) = '3' and SUBSTRING(GlCode.GL_CODE,1,2) = '34') THEN -1
        WHEN (SUBSTRING(GlCode.GL_CODE,1,1) = '3' and 
          (SUBSTRING(GlCode.GL_CODE,1,2) = '35' or SUBSTRING(GlCode.GL_CODE,1,2) = '36'
            or SUBSTRING(GlCode.GL_CODE,1,2) = '37')) THEN 1
        WHEN (SUBSTRING(GlCode.GL_CODE,1,1) = '3' and 
          (SUBSTRING(GlCode.GL_CODE,1,2) = '30' or SUBSTRING(GlCode.GL_CODE,1,2) = '31' 
             or SUBSTRING(GlCode.GL_CODE,1,2) = '32' or SUBSTRING(GlCode.GL_CODE,1,2) = '33'
            or SUBSTRING(GlCode.GL_CODE,1,2) = '38' or SUBSTRING(GlCode.GL_CODE,1,2) = '39')) THEN -    1         
        ELSE 1
        END AS INT) AS AccountSign
            
FROM COA_ORG_UNIT_FILE as GlCode
LEFT OUTER JOIN UTL_CODE_TABLE GlClassDesc
   ON (
       GlClassDesc.TABLE_NAME = 'A020' 
            AND GlClassDesc.CODE = SUBSTRING(GlCode.GL_CODE,1,1) 
            AND GlClassDesc.STATUS = 'A'
       )
      
LEFT OUTER JOIN UTL_CODE_TABLE GlCatDesc
   ON (
       GlCatDesc.TABLE_NAME = 'GLC2-DESC' 
            AND GlCatDesc.CODE = SUBSTRING(GlCode.GL_CODE,1,2) 
            AND GlCatDesc.STATUS = 'A'
       )
LEFT OUTER JOIN UTL_CODE_TABLE GlSubCatDesc
   ON (
       GlSubCatDesc.TABLE_NAME = 'GLC3-DESC' 
            AND GlSubCatDesc.CODE = SUBSTRING(GlCode.GL_CODE,1,3) 
            AND GlSubCatDesc.STATUS = 'A'
       )
LEFT OUTER JOIN UTL_CODE_TABLE GlCode5
   ON (
       GlCode5.TABLE_NAME = 'C050' 
            AND GlCode5.CODE = SUBSTRING(GlCode.GL_CODE,1,5) 
            AND GlCode5.STATUS = 'A'
       )
       
LEFT OUTER JOIN UTL_CODE_TABLE GlSubTotalCode
   ON (
       GlSubTotalCode.TABLE_NAME = 'GLC-DW-TOT' 
            AND SUBSTRING(GlCode.GL_CODE,1,5)  >= SUBSTRING(GlSubTotalCode.CODE,1,5) 
              AND SUBSTRING(GlCode.GL_CODE,1,5) <= SUBSTRING(GlSubTotalCode.CODE,6,10) 
            AND GlSubTotalCode.STATUS = 'A'
       )
LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC GlSubTotalDesc
   ON (
            GlSubTotalDesc.ISN_UTL_CODE_TABLE = GlSubTotalCode.ISN_UTL_CODE_TABLE
              AND GlSubTotalDesc.cnxarraycolumn = 0
       )       
   
LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC GlSubTotal
   ON (
            GlSubTotal.ISN_UTL_CODE_TABLE = GlSubTotalCode.ISN_UTL_CODE_TABLE
              AND GlSubTotal.cnxarraycolumn = 1
       )       
LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC GlTimeStatus
   ON (
            GlTimeStatus.ISN_UTL_CODE_TABLE = GlSubTotalCode.ISN_UTL_CODE_TABLE
              AND GlTimeStatus.cnxarraycolumn = 2
       )       
       
LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC GlHrClassification
   ON (
            GlHrClassification.ISN_UTL_CODE_TABLE = GlSubTotalCode.ISN_UTL_CODE_TABLE
              AND GlHrClassification.cnxarraycolumn = 3
       )       
       
                                          
WHERE GL_IND = 'Y'
SqlCommandVariable
User::GetStudentTermMods
  Input Columns
Name ID LineageID
  Output Columns
Name ID LineageID
AccountSign
2091
2091
ErrorCode
13
13
ErrorColumn
14
14
Glc
2055
2055
GlCategory
2070
2070
GlCategoryCode
2067
2067
GlcDescription
2079
2079
GlClass
2064
2064
GlClassCode
2061
2061
GlCodeAK
2052
2052
GlCodeName
2082
2082
GlCodeSubTotal
2088
2088
GlCodeSubTotalCode
2085
2085
GlGroup
2094
2094
GlSubCategory
2076
2076
GlSubCategoryCode
2073
2073
HrClassification
2117
2117
HrTimeStatus
2114
2114
Qual2
2058
2058
AccountSign
2090
2090
Glc
2054
2054
GlCategory
2069
2069
GlCategoryCode
2066
2066
GlcDescription
2078
2078
GlClass
2063
2063
GlClassCode
2060
2060
GlCodeAK
2051
2051
GlCodeName
2081
2081
GlCodeSubTotal
2087
2087
GlCodeSubTotalCode
2084
2084
GlGroup
2093
2093
GlSubCategory
2075
2075
GlSubCategoryCode
2072
2072
HrClassification
2116
2116
HrTimeStatus
2113
2113
Qual2
2057
2057
Powered by BI Documenter