SELECT DISTINCT AO.[AssignableObjectId] AS ID
      ,(SELECT AO2.name FROM dbo.[AssignableObject] AS AO2 WHERE AO2.AssignableObjectId = AO.[ParentAssignableObjectId]) AS category
      ,AO.[Name]

      ,REPLACE(REPLACE(AO.[Description], CHAR(13), ''), CHAR(10), '') AS 'Description'
      ,'N' AS IsProduct

      --,(CASE dep.Name WHEN 'Systeem' then 'alle' ELSE dep.[Name] END)as 'afdeling'
      , stuff(
    (SELECT ',' + (CASE dep.Name WHEN 'Systeem' THEN 'alle' ELSE dep.[Name] END)
     FROM  dbo.Asset_Department AS AD
     JOIN dbo.Department AS Dep ON AD.DepartmentId = Dep.DepartmentId
     WHERE AD.AssetId = AO.AssignableObjectId
     FOR xml path('')), 1,1,'') AS [Afdeling/ACL]

      ,CONCAT( (SELECT emp.userid +', ' FROM [dbo].[ActorRole] AS ARo
          JOIN dbo.Employee AS Emp ON emp.EmployeeId= ARo.EmployeeId
          WHERE  ARo.AssignableObjectId = AO.AssignableObjectId AND ARo.ActorType=0
          FOR XML PATH('')) , ' '

          ,stuff( (SELECT ',' +
            (CASE WF_P.Permissiongroup
            WHEN 4 THEN '[Afdelingsmanager]'
            WHEN 8 THEN '[HR Manager]'
            WHEN 64 THEN '[LocatieEigenaar]'
            WHEN 2 THEN '[ICT Manager]'
            ELSE '' END)-- AS ApprovalRol
          FROM [dbo].[Workflow] AS WF
          JOIN [dbo].[WorkflowRole_Permission] AS WF_P ON WF.WorkflowId = WF_P.WorkflowId
          WHERE ActorType=0   AND AssignableObjectId=AO.AssignableObjectId
          FOR xml path('')), 1,1,'')
          ) AS approver,

          (SELECT emp.userid +', ' FROM [dbo].[ActorRole] AS ARo
          JOIN dbo.ActorRole_Delegate AS ARD ON aro.ActorRoleId=ard.ActorRoleId
          JOIN dbo.Employee AS Emp ON emp.EmployeeId= ARD.EmployeeId
          WHERE  ARo.AssignableObjectId = AO.AssignableObjectId AND ARo.ActorType=0
          FOR XML PATH(''))
          AS delegatedApprovers

           ,AO.[Sid] AS 'ADGroupSid'
           ,AO.RunbookId
           , (SELECT ownedby.UserId
                      FROM [dbo].[ActorRole] AS AR
                      JOIN dbo.Employee AS ownedBy ON ar.EmployeeId = ownedBy.EmployeeId
                      WHERE workflowType=1 AND AssignableObjectId=AO.AssignableObjectId) AS diensteigenaar
            , (SELECT managedby.UserId
            FROM [dbo].[ActorRole] AS AR
            JOIN dbo.Employee AS managedby ON ar.EmployeeId = managedby.EmployeeId
            WHERE ar.workflowType=0 AND ar.ActorType=10 AND AssignableObjectId=AO.AssignableObjectId) AS ledenbeheerder
  FROM [dbo].[AssignableObject] AS AO

  JOIN dbo.Asset_Department AS AD ON AO.AssignableObjectId =AD.AssetId AND  ad.OnlyForRole = 0
  JOIN dbo.Department AS Dep ON AD.DepartmentId = Dep.DepartmentId  AND dep.DepartmentState=0

  WHERE IsContainer = 0
  AND AO.IsProduct = 0

UNION ALL

SELECT DISTINCT AO.[AssignableObjectId] AS ID
      , stuff(
    (SELECT ',' + RT.VALUE
     FROM  Rev.ProductCategory_Products AS PCP
     JOIN Rev.ProductCategories AS PC ON PC.[ProductCategoryId] = PCP.[ProductCategory_ProductCategoryId]
     JOIN (SELECT RevTranslatableString_RevTranslatableStringId, MIN([Culture]) AS [Culture]
           FROM Rev.RevTranslations
           WHERE Culture IN ('en-US','nl-NL')
           GROUP BY RevTranslatableString_RevTranslatableStringId) AS t ON t.RevTranslatableString_RevTranslatableStringId = PC.Name_RevTranslatableStringId
     JOIN Rev.RevTranslations AS RT ON RT.RevTranslatableString_RevTranslatableStringId = t.RevTranslatableString_RevTranslatableStringId AND RT.Culture = t.Culture
     WHERE PCP.Product_ProductId = P.ProductId
     FOR xml path('')), 1,1,'') AS category

      ,(SELECT top 1 RT.VALUE
        FROM Rev.RevTranslations AS RT
        WHERE RevTranslatableString_RevTranslatableStringId = P.Name_RevTranslatableStringId
        AND RT.Culture IN ('en-US','nl-NL')
        ORDER BY RT.Culture)
      ,(SELECT top 1 REPLACE(REPLACE(RT.VALUE, CHAR(13), ''), CHAR(10), '')
        FROM Rev.RevTranslations AS RT
        WHERE RevTranslatableString_RevTranslatableStringId = P.Description_RevTranslatableStringId
        AND RT.Culture IN ('en-US','nl-NL')
        ORDER BY RT.Culture)
     ,'Y' AS IsProduct
      , ISNULL(stuff(
    (SELECT ',' + T.[Name]
     FROM  Rev.ProductAccessSids AS PAS
     INNER JOIN (SELECT [Sid], [Name] FROM Rev.AdGroups UNION ALL SELECT CONVERT(nvarchar(MAX),[Sid]), DisplayName FROM Rev.AdUsers) AS T
       ON T.Sid = CONVERT(nvarchar(MAX),PAS.Sid)
     WHERE PAS.ProductId = P.ProductId AND PAS.Allow = 1
     FOR xml path('')), 1,1,''),'Not published/not active in SSP') AS Afdeling

      ,CONCAT( (SELECT emp.userid +', ' FROM [dbo].[ActorRole] AS ARo
          JOIN dbo.Employee AS Emp ON emp.EmployeeId= ARo.EmployeeId
          WHERE  ARo.AssignableObjectId = AO.AssignableObjectId AND ARo.ActorType=0
          FOR XML PATH('')) , ' '

          ,stuff( (SELECT ',' +
            (CASE WF_P.Permissiongroup
            WHEN 4 THEN '[Afdelingsmanager]'
            WHEN 8 THEN '[HR Manager]'
            WHEN 64 THEN '[LocatieEigenaar]'
            WHEN 2 THEN '[ICT Manager]'
            ELSE '' END)-- AS ApprovalRol
          FROM [dbo].[Workflow] AS WF
          JOIN [dbo].[WorkflowRole_Permission] AS WF_P ON WF.WorkflowId = WF_P.WorkflowId
          WHERE ActorType=0   AND AssignableObjectId=AO.AssignableObjectId
          FOR xml path('')), 1,1,'')
          ) AS approver

          ,  (SELECT emp.userid +', ' FROM [dbo].[ActorRole] AS ARo
          JOIN dbo.ActorRole_Delegate AS ARD ON aro.ActorRoleId=ard.ActorRoleId
          JOIN dbo.Employee AS Emp ON emp.EmployeeId= ARD.EmployeeId
          WHERE  ARo.AssignableObjectId = AO.AssignableObjectId AND ARo.ActorType=0
          FOR XML PATH(''))
          AS delegatedApprovers
           ,AO.[Sid] AS 'ADGroupSid'
           ,AO.RunbookId
           , (SELECT ownedby.UserId
                      FROM [dbo].[ActorRole] AS AR
                      JOIN dbo.Employee AS ownedBy ON ar.EmployeeId = ownedBy.EmployeeId
                      WHERE workflowType=1 AND AssignableObjectId=AO.AssignableObjectId) AS diensteigenaar
            , (SELECT managedby.UserId
            FROM [dbo].[ActorRole] AS AR
            JOIN dbo.Employee AS managedby ON ar.EmployeeId = managedby.EmployeeId
            WHERE ar.workflowType=0 AND ar.ActorType=10 AND AssignableObjectId=AO.AssignableObjectId) AS ledenbeheerder
  FROM [dbo].[AssignableObject] AS AO
  JOIN Rev.Products AS P ON p.ProductId = AO.AssignableObjectId

  WHERE IsContainer = 0
  AND AO.IsProduct = 1

  ORDER BY Id