UFN_DESIGNATION_BUILDNAME_BULK

CREATE function dbo.UFN_DESIGNATION_BUILDNAME_BULK ()
returns table
as
return (
    select DESIGNATION.ID
      ,convert(nvarchar(512), case 
          when (LEVEL5.[NAME] is not null)
            then isnull(LEVEL1.[NAME], '<Unspecified>') + ' \ ' + isnull(LEVEL2.[NAME], '<Unspecified>') + ' \ ' + isnull(LEVEL3.[NAME], '<Unspecified>') + ' \ ' + isnull(LEVEL4.[NAME], '<Unspecified>') + ' \ ' + LEVEL5.[NAME]
          when (LEVEL4.[NAME] is not null)
            then isnull(LEVEL1.[NAME], '<Unspecified>') + ' \ ' + isnull(LEVEL2.[NAME], '<Unspecified>') + ' \ ' + isnull(LEVEL3.[NAME], '<Unspecified>') + ' \ ' + LEVEL4.[NAME]
          when (LEVEL3.[NAME] is not null)
            then isnull(LEVEL1.[NAME], '<Unspecified>') + ' \ ' + isnull(LEVEL2.[NAME], '<Unspecified>') + ' \ ' + LEVEL3.[NAME]
          when (LEVEL2.[NAME] is not null)
            then isnull(LEVEL1.[NAME], '<Unspecified>') + ' \ ' + LEVEL2.[NAME]
          else LEVEL1.[NAME]
          end) as [NAME]
      ,case 
        when (DESIGNATIONLEVEL5ID is not null)
          then DESIGNATIONLEVEL4ID
        when (DESIGNATIONLEVEL4ID is not null)
          then DESIGNATIONLEVEL3ID
        when (DESIGNATIONLEVEL3ID is not null)
          then DESIGNATIONLEVEL2ID
        when (DESIGNATIONLEVEL2ID is not null)
          then DESIGNATIONLEVEL1ID
        else null
        end as PARENTDESIGNATIONLEVELID
      ,case 
        when (DESIGNATIONLEVEL5ID is not null)
          then COALESCE(CAST(DESIGNATIONLEVEL1ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL2ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL3ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL4ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL5ID as varchar(36)), '.')
        when (DESIGNATIONLEVEL4ID is not null)
          then COALESCE(CAST(DESIGNATIONLEVEL1ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL2ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL3ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL4ID as varchar(36)), '.')
        when (DESIGNATIONLEVEL3ID is not null)
          then COALESCE(CAST(DESIGNATIONLEVEL1ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL2ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL3ID as varchar(36)), '.')
        when (DESIGNATIONLEVEL2ID is not null)
          then COALESCE(CAST(DESIGNATIONLEVEL1ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL2ID as varchar(36)), '.')
        else COALESCE(CAST(DESIGNATIONLEVEL1ID as varchar(36)), '.')
        end as PATHID
      ,case 
        when (DESIGNATIONLEVEL5ID is not null)
          then COALESCE(CAST(DESIGNATIONLEVEL1ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL2ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL3ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL4ID as varchar(36)), '.')
        when (DESIGNATIONLEVEL4ID is not null)
          then COALESCE(CAST(DESIGNATIONLEVEL1ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL2ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL3ID as varchar(36)), '.')
        when (DESIGNATIONLEVEL3ID is not null)
          then COALESCE(CAST(DESIGNATIONLEVEL1ID as varchar(36)), '.') + COALESCE(CAST(DESIGNATIONLEVEL2ID as varchar(36)), '.')
        when (DESIGNATIONLEVEL2ID is not null)
          then COALESCE(CAST(DESIGNATIONLEVEL1ID as varchar(36)), '.')
        else null
        end as PARENTPATHID
      ,case 
        when (DESIGNATIONLEVEL5ID is not null)
          then 5
        when (DESIGNATIONLEVEL4ID is not null)
          then 4
        when (DESIGNATIONLEVEL3ID is not null)
          then 3
        when (DESIGNATIONLEVEL2ID is not null)
          then 2
        else 1
        end as level
      ,COALESCE(DESIGNATIONLEVEL5ID, DESIGNATIONLEVEL4ID, DESIGNATIONLEVEL3ID, DESIGNATIONLEVEL2ID, DESIGNATIONLEVEL1ID, null) DESIGNATIONLEVELID
      ,DESIGNATION.DESIGNATIONLEVELTYPEHIERARCHYITEMID
  ,DESIGNATION.ISREVENUEDESIGNATION
      ,case when DESIGNATION.SYSTEMGENERATED=1 then NULL else DESIGNATION.VANITYNAME end VANITYNAME
      ,DESIGNATION.DESIGNATIONLEVEL1ID ROOTDESIGNATIONLEVELID
      ,DESIGNATION.DESIGNATIONLEVEL1ID
      ,DESIGNATION.DESIGNATIONLEVEL2ID
      ,DESIGNATION.DESIGNATIONLEVEL3ID
      ,DESIGNATION.DESIGNATIONLEVEL4ID
      ,DESIGNATION.DESIGNATIONLEVEL5ID
      ,case when DESIGNATION.SYSTEMGENERATED=1 then NULL else DESIGNATION.USERID end USERID
      ,DESIGNATION.VSECATEGORYID
      ,DESIGNATION.VSESUBCATEGORYID
      ,
      --REVISIT is this logic valid for report codes?

      COALESCE(DESIGNATION.DESIGNATIONREPORT1CODEID, LEVEL5.DESIGNATIONREPORT1CODEID, LEVEL4.DESIGNATIONREPORT1CODEID, LEVEL3.DESIGNATIONREPORT1CODEID, LEVEL2.DESIGNATIONREPORT1CODEID, LEVEL1.DESIGNATIONREPORT1CODEID) as DESIGNATIONREPORT1CODEID
      ,COALESCE(DESIGNATION.DESIGNATIONREPORT2CODEID, LEVEL5.DESIGNATIONREPORT2CODEID, LEVEL4.DESIGNATIONREPORT2CODEID, LEVEL3.DESIGNATIONREPORT2CODEID, LEVEL2.DESIGNATIONREPORT2CODEID, LEVEL1.DESIGNATIONREPORT2CODEID) as DESIGNATIONREPORT2CODEID
      ,COALESCE(LEVEL5.SITEID, LEVEL4.SITEID, LEVEL3.SITEID, LEVEL2.SITEID, LEVEL1.SITEID) as DESIGNATIONLEVELSITEID
      ,DESIGNATION.DESIGNATIONUSECODEID
      ,DESIGNATION.ISACTIVE
      ,DESIGNATION.SYSTEMGENERATED
    from dbo.DESIGNATION
    left join dbo.DESIGNATIONLEVEL as LEVEL1 on DESIGNATION.DESIGNATIONLEVEL1ID = LEVEL1.ID
    left join dbo.DESIGNATIONLEVEL as LEVEL2 on DESIGNATION.DESIGNATIONLEVEL2ID = LEVEL2.ID
    left join dbo.DESIGNATIONLEVEL as LEVEL3 on DESIGNATION.DESIGNATIONLEVEL3ID = LEVEL3.ID
    left join dbo.DESIGNATIONLEVEL as LEVEL4 on DESIGNATION.DESIGNATIONLEVEL4ID = LEVEL4.ID
    left join dbo.DESIGNATIONLEVEL as LEVEL5 on DESIGNATION.DESIGNATIONLEVEL5ID = LEVEL5.ID
    )