FIM metaverse SQL query - employeeID contributing MA

set transaction isolation level read uncommitted

SELECT TOP 1000

    --lg.[object_id]

    mv.object_type

      --,lg.[displayName]

      --,lg.[employeeID]

      --,lg.[HR_USERID]

      --,lg.[HR_Joined]

    ,mv.employeeID as [employeeID]

    ,ma_empid.ma_name as [employeeID MA]

    ,mv.displayName as displayName

    ,ma_disp.ma_name as [displayName MA]

    ,mv.HR_USERID as HR_USERID

    ,ma_uid.ma_name as [HR_USERID MA]

    ,mv.HR_Joined as HR_Joined

    ,ma_hrj.ma_name as [HR_Joined MA]

  FROM [FIMSynchronizationService].[dbo].[mms_metaverse_lineageguid] lg

  join [FIMSynchronizationService].dbo.mms_metaverse mv

  on lg.object_id = mv.object_id

  left join [FIMSynchronizationService].[dbo].[mms_lineage_cross_reference] cr_empid

  on cr_empid.lineage_id = lg.employeeID

  left join [FIMSynchronizationService].[dbo].[mms_management_agent] ma_empid

  on ma_empid.ma_id = cr_empid.ma_id

  left join [FIMSynchronizationService].[dbo].[mms_lineage_cross_reference] cr_disp

  on cr_disp.lineage_id = lg.displayName

  left join [FIMSynchronizationService].[dbo].[mms_management_agent] ma_disp

  on ma_disp.ma_id = cr_disp.ma_id

  left join [FIMSynchronizationService].[dbo].[mms_lineage_cross_reference] cr_uid

  on cr_uid.lineage_id = lg.HR_USERID

  left join [FIMSynchronizationService].[dbo].[mms_management_agent] ma_uid

  on ma_uid.ma_id = cr_uid.ma_id

  left join [FIMSynchronizationService].[dbo].[mms_lineage_cross_reference] cr_hrj

  on cr_hrj.lineage_id = lg.HR_Joined

  left join [FIMSynchronizationService].[dbo].[mms_management_agent] ma_hrj

  on ma_hrj.ma_id = cr_hrj.ma_id

  where object_type = 'person'

  and (

        ma_empid.ma_name = 'FIM MA'

        or mv.employeeID is null

        --or mv.HR_Joined is null

    )