CREATE VIEW dbo.al_user_tenant_master_view (user_id, tenant, role_name)
AS
   
   /*
   *   Generated by SQL Server Migration Assistant for Oracle.
   *   Contact ora2sql@microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
   */
    (
      SELECT c.userid AS user_id,
         CASE
            WHEN rd.tenant_access_read = 0 THEN t0.ID
            WHEN rd.tenant_access_read = 1 THEN t1.ID
            WHEN rd.tenant_access_read = 2 THEN t2.ID
            WHEN rd.tenant_access_read = 3 THEN t3.ID
            WHEN rd.tenant_access_read = 4 THEN t4.ID
         END AS tenant, rd.ROLE_NAME
      FROM
         dbo.al_link_role_contact  AS lrc
            INNER JOIN dbo.al_role_def  AS rd
            ON rd.ROLE_ID = lrc.ROLE_ID
            INNER JOIN dbo.ca_contact  AS c
            ON c.CONTACT_UUID = lrc.contact_uuid
            LEFT OUTER JOIN dbo.ca_tenant  AS t0
            ON rd.tenant_access_read = 0
            LEFT OUTER JOIN dbo.ca_tenant  AS t1
            ON rd.tenant_access_read = 1 AND t1.ID = rd.single_tenant_read
            LEFT OUTER JOIN dbo.ca_tenant_group_member  AS tgmem2
            ON rd.tenant_access_read = 2 AND tgmem2.tenant_group = rd.tenant_group_read
            LEFT OUTER JOIN dbo.ca_tenant  AS t2
            ON t2.ID = tgmem2.TENANT_ID
            LEFT OUTER JOIN dbo.CA_TENANT  AS t3
            ON rd.TENANT_ACCESS_READ = 3 AND t3.ID = c.TENANT
            LEFT OUTER JOIN dbo.CA_CONTACT  AS sdc
            ON rd.TENANT_ACCESS_READ = 4 AND c.CONTACT_UUID = sdc.CONTACT_UUID
            LEFT OUTER JOIN dbo.CA_TENANT_GROUP_MEMBER  AS tgmem4
            ON tgmem4.TENANT_GROUP = sdc.TENANT_GROUP
            LEFT OUTER JOIN dbo.CA_TENANT  AS t4
            ON t4.ID = tgmem4.TENANT_ID
    )
GO