DimOrgUnitLoad
 OLE_SRC Verification Temp table from COA_ORG_UNIT_FILE Staging (Component)
Home  Diagrams  Documents  Images  DimOrgUnitLoad  Executables  Connection Managers  Log Providers  Variables 
Current Path:  DimOrgUnitLoad   DFT Load Temp Org Unit 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 
       CoaOrgUnit.ORG_UNIT as OrgUnitAK
      ,Substring(CoaOrgUnit.ORG_UNIT,1,8) + '-' + Substring(CoaOrgUnit.ORG_UNIT,9,6) 
         + '-' + Substring(CoaOrgUnit.ORG_UNIT,15,2) as OrgUnit
      ,CoaOrgUnit.NAME as OrgUnitName
      ,CoaOrgUnit.ACTIVE_IND as OrgUnitStatusCode
      ,CAST(CASE 
            WHEN CoaOrgUnit.ACTIVE_IND = 'A' THEN 'Active'
            WHEN CoaOrgUnit.ACTIVE_IND = 'I' THEN 'In-Active'
            WHEN CoaOrgUnit.ACTIVE_IND = 'S' THEN 'Summary'
            ELSE 'N/A'
        END
        AS varchar(15)) AS OrgUnitStatus
      ,SUBSTRING(CoaOrgUnit.ORG_UNIT,1,1) as FundCode
      ,ISNULL(FundDesc.DESCRIPTION, 'N/A') AS Fund
      ,SUBSTRING(CoaOrgUnit.ORG_UNIT,1,2) as FunctionCode
      ,ISNULL(FunctionDesc.DESCRIPTION, 'N/A') AS     FunctionDescription  
      ,SUBSTRING(CoaOrgUnit.ORG_UNIT,1,3) as SubFunctionCode
      ,ISNULL(SubFunctionDesc.DESCRIPTION, 'N/A') AS     SubFunction
                    ,SUBSTRING(CoaOrgUnit.ORG_UNIT,1,8) as StateOrgUnit
      ,SUBSTRING(CoaOrgUnit.ORG_UNIT,15,2) as LocationCode
      ,ISNULL(SUBSTRING(LocationDesc.TABLE_DESC,1,30), 'N/A') AS     Location 
      ,SUBSTRING(CoaOrgUnit.ORG_UNIT,9,6) as Qual1
      ,Substring(CoaOrgUnit.CONTROL_ACCT_NO,1,8) + '-' + Substring(CoaOrgUnit.CONTROL_ACCT_NO,9,6) 
         + '-' + Substring(CoaOrgUnit.CONTROL_ACCT_NO,15,2) as ControlOrgUnit
      ,ISNULL(ControlOrg.NAME, 'N/A') as ControlOrgUnitName 
      ,ISNULL(CoaOrgUnit.BUDGET_MANAGER, 'N/A') as BudgetManagerId
      ,CAST(CASE
         WHEN (SecBudget.LAST_NAME IS NULL) and (SecBudget.FIRST_NAME IS NULL) THEN 'N/A'
         WHEN SecBudget.LAST_NAME IS NULL THEN 'N/A'
         WHEN (SecBudget.FIRST_NAME IS NULL) OR (SecBudget.FIRST_NAME = ' ') THEN SecBudget.LAST_NAME
         ELSE SecBudget.LAST_NAME + ', ' + SecBudget.FIRST_NAME
         END
        AS VARCHAR(30))
        AS BudgetManager
      ,ISNULL(CoaOrgUnit.USER_GROUP_ID, 'N/A') as UserGroupId
      ,ISNULL(SecGroup.USER_GROUP_NAME, 'N/A') as UserGroup
      ,ISNULL(CoaOrgUnit.PRINCIPAL_ADMIN, 'N/A') as PrincipalAdminId
      ,CAST(CASE
         WHEN (SecPrincipal.LAST_NAME IS NULL) and (SecPrincipal.FIRST_NAME IS NULL) THEN 'N/A'
         WHEN (SecPrincipal.LAST_NAME = ' ') and (SecPrincipal.FIRST_NAME = ' ') THEN 'N/A'         
         WHEN SecPrincipal.LAST_NAME IS NULL THEN 'N/A'
         WHEN SecPrincipal.LAST_NAME = ' ' THEN 'N/A'         
         WHEN (SecPrincipal.FIRST_NAME IS NULL) OR (SecPrincipal.FIRST_NAME = ' ') THEN SecPrincipal.LAST_NAME
         WHEN SecPrincipal.LAST_NAME <> ' ' AND SecPrincipal.FIRST_NAME <> ' ' THEN  
             SecPrincipal.LAST_NAME + ', ' + SecPrincipal.FIRST_NAME
         ELSE 'N/A'
         END
        AS VARCHAR(40)) AS PrincipalAdmin
      ,ISNULL(CoaOrgUnit.PROJECT_NO, 0) as ProjectNumber
      ,ISNULL(CoaOrgUnit.DISTRIBUTION, 'N/A') as ReportDistributionCode
      ,ISNULL(SUBSTRING(DistCode.TABLE_DESC,1,30), 'N/A') as ReportDistribution 
      ,ISNULL(CoaOrgUnit.BANK_CODE, 'N/A') as OrgUnitGrouping
      ,CAST(ISNULL(CoaOrgUnit.START_DATE, '1900-01-01') AS DATETIME) AS StartDate
      ,CAST(ISNULL(CoaOrgUnit.END_DATE, '2098-06-30') AS DATETIME) AS EndDate
  FROM COA_ORG_UNIT_FILE as CoaOrgUnit
  
  LEFT OUTER JOIN UTL_CODE_TABLE FundDesc
   ON (
       FundDesc.TABLE_NAME = 'F001' 
            AND FundDesc.CODE = SUBSTRING(CoaOrgUnit.ORG_UNIT,1,1) 
            AND FundDesc.STATUS = 'A'
       )
       
LEFT OUTER JOIN UTL_CODE_TABLE FunctionDesc
   ON (
       FunctionDesc.TABLE_NAME = 'F002' 
            AND FunctionDesc.CODE = SUBSTRING(CoaOrgUnit.ORG_UNIT,1,2) 
            AND FunctionDesc.STATUS = 'A'
       )       
LEFT OUTER JOIN UTL_CODE_TABLE SubFunctionDesc
   ON (
       SubFunctionDesc.TABLE_NAME = 'F003' 
            AND SubFunctionDesc.CODE = SUBSTRING(CoaOrgUnit.ORG_UNIT,1,3) 
            AND SubFunctionDesc.STATUS = 'A'
       )      
       
LEFT OUTER JOIN FIG_TABLES_FILE LocationDesc
   ON (
       LocationDesc.TABLE_NAME = 'FIGADM6' 
            AND LocationDesc.TABLE_CODE = SUBSTRING(CoaOrgUnit.ORG_UNIT,15,2) 
       )              
  
LEFT OUTER JOIN COA_ORG_UNIT_FILE ControlOrg
   ON (
        ControlOrg.ORG_UNIT = SUBSTRING(CoaOrgUnit.CONTROL_ACCT_NO,1,16)
        and ControlOrg.ORG_IND = 'Y'
       )              
LEFT OUTER JOIN SECURITY_USER_A2 SecBudget
   ON (
        SecBudget.USER_ID = CoaOrgUnit.BUDGET_MANAGER
       )              
LEFT OUTER JOIN USER_GROUP_PROFILE_A SecGroup
   ON (
        SecGroup.USER_GROUP_ID = CoaOrgUnit.BUDGET_MANAGER
       )              
       
LEFT OUTER JOIN SECURITY_USER_A2 SecPrincipal
   ON (
        SecPrincipal.USER_ID = CoaOrgUnit.PRINCIPAL_ADMIN
       )              
LEFT OUTER JOIN FIG_TABLES_FILE DistCode
   ON (
       DistCode.TABLE_NAME = 'FIGADM13' 
            AND DistCode.TABLE_CODE = CoaOrgUnit.DISTRIBUTION
       )              
                    
  where CoaOrgUnit.ORG_IND = 'Y'
SqlCommandVariable
User::GetStudentTermMods
  Input Columns
Name ID LineageID
  Output Columns
Name ID LineageID
BudgetManager
1880
1880
BudgetManagerId
1877
1877
ControlOrgUnit
1871
1871
ControlOrgUnitName
1874
1874
EndDate
1910
1910
ErrorCode
13
13
ErrorColumn
14
14
FunctionCode
1850
1850
FunctionDescription
1853
1853
Fund
1847
1847
FundCode
1844
1844
Location
1865
1865
LocationCode
1862
1862
OrgUnit
1832
1832
OrgUnitAK
1829
1829
OrgUnitGrouping
1904
1904
OrgUnitName
1835
1835
OrgUnitStatus
1841
1841
OrgUnitStatusCode
1838
1838
PrincipalAdmin
1892
1892
PrincipalAdminId
1889
1889
ProjectNumber
1895
1895
Qual1
1868
1868
ReportDistribution
1901
1901
ReportDistributionCode
1898
1898
StartDate
1907
1907
StateOrgUnit
1915
1915
SubFunction
1859
1859
SubFunctionCode
1856
1856
UserGroup
1886
1886
UserGroupId
1883
1883
BudgetManager
1879
1879
BudgetManagerId
1876
1876
ControlOrgUnit
1870
1870
ControlOrgUnitName
1873
1873
EndDate
1909
1909
FunctionCode
1849
1849
FunctionDescription
1852
1852
Fund
1846
1846
FundCode
1843
1843
Location
1864
1864
LocationCode
1861
1861
OrgUnit
1831
1831
OrgUnitAK
1828
1828
OrgUnitGrouping
1903
1903
OrgUnitName
1834
1834
OrgUnitStatus
1840
1840
OrgUnitStatusCode
1837
1837
PrincipalAdmin
1891
1891
PrincipalAdminId
1888
1888
ProjectNumber
1894
1894
Qual1
1867
1867
ReportDistribution
1900
1900
ReportDistributionCode
1897
1897
StartDate
1906
1906
StateOrgUnit
1914
1914
SubFunction
1858
1858
SubFunctionCode
1855
1855
UserGroup
1885
1885
UserGroupId
1882
1882
Powered by BI Documenter