Select
  o.OBJECTID as OBJECTID,
  fone.OBJECTID as ExternalRefId,
  o.GlobalID as OrganisationGlobalId,
  fone.ExternalId as UniqueId,
  ofl.lastEditedDate as LastModifiedDate,
  fone.CreatedDate as CreatedDate,
  ofl.FeatureLayerTypeId
from
  FeatureOne fone
  join OrgFeatureLayer ofl on ofl.FeatureLayerId = crd.GlobalID
  join Organisation o on ofl.OrganisationId = o.GlobalID
where
  ofl.GDB_ARCHIVE_OID IN (
    select
      MB.GDB_ARCHIVE_OID
    from
      (
        select
          GDB_ARCHIVE_OID,
          ROW_NUMBER() OVER (
            PARTITION BY objectid
            order by
              a.gdb_from_date DESC
          ) rn,
          a.GDB_IS_DELETE,
          a.GDB_BRANCH_ID
        from
          OrgFeatureLayer a
        WHERE
          a.GDB_BRANCH_ID = 0
      ) MB
    Where
      MB.rn = 1
      and MB.GDB_IS_DELETE = 0
  )
  AND (
    o.GDB_ARCHIVE_OID IN (
      select
        MB.GDB_ARCHIVE_OID
      from
        (
          select
            GDB_ARCHIVE_OID,
            ROW_NUMBER() OVER (
              PARTITION BY objectid
              order by
                a.gdb_from_date DESC
            ) rn,
            a.GDB_IS_DELETE,
            a.GDB_BRANCH_ID
          from
            Organisation a
          WHERE
            a.GDB_BRANCH_ID = 0
        ) MB
      Where
        MB.rn = 1
        and MB.GDB_IS_DELETE = 0
    )
    OR o.GDB_ARCHIVE_OID is NULL
  )
  AND (
    fone.GDB_ARCHIVE_OID IN (
      select
        MB.GDB_ARCHIVE_OID
      from
        (
          select
            GDB_ARCHIVE_OID,
            ROW_NUMBER() OVER (
              PARTITION BY objectid
              order by
                a.gdb_from_date DESC
            ) rn,
            a.GDB_IS_DELETE,
            a.GDB_BRANCH_ID
          from
            FeatureOne a
          WHERE
            a.GDB_BRANCH_ID = 0
        ) MB
      Where
        MB.rn = 1
        and MB.GDB_IS_DELETE = 0
    )
    OR fone.GDB_ARCHIVE_OID is NULL
  )
UNION ALL
Select
  o.OBJECTID as OBJECTID,
  ftwo.OBJECTID as ExternalRefId,
  o.GlobalID as OrganisationGlobalId,
  ftwo.ExternalId as UniqueId,
  ofl.lastEditedDate as LastModifiedDate,
  ftwo.CreatedDate as CreatedDate,
  ofl.FeatureLayerTypeId
from
  FeatureTwo ftwo
  join OrgFeatureLayer ofl on ofl.FeatureLayerId = cro.GlobalID
  join Organisation o on ofl.OrganisationId = o.GlobalID
where
  ofl.GDB_ARCHIVE_OID IN (
    select
      MB.GDB_ARCHIVE_OID
    from
      (
        select
          GDB_ARCHIVE_OID,
          ROW_NUMBER() OVER (
            PARTITION BY objectid
            order by
              a.gdb_from_date DESC
          ) rn,
          a.GDB_IS_DELETE,
          a.GDB_BRANCH_ID
        from
          OrgFeatureLayer a
        WHERE
          a.GDB_BRANCH_ID = 0
      ) MB
    Where
      MB.rn = 1
      and MB.GDB_IS_DELETE = 0
  )
  AND (
    o.GDB_ARCHIVE_OID IN (
      select
        MB.GDB_ARCHIVE_OID
      from
        (
          select
            GDB_ARCHIVE_OID,
            ROW_NUMBER() OVER (
              PARTITION BY objectid
              order by
                a.gdb_from_date DESC
            ) rn,
            a.GDB_IS_DELETE,
            a.GDB_BRANCH_ID
          from
            Organisation a
          WHERE
            a.GDB_BRANCH_ID = 0
        ) MB
      Where
        MB.rn = 1
        and MB.GDB_IS_DELETE = 0
    )
    OR o.GDB_ARCHIVE_OID is NULL
  )
  AND (
    cro.GDB_ARCHIVE_OID IN (
      select
        MB.GDB_ARCHIVE_OID
      from
        (
          select
            GDB_ARCHIVE_OID,
            ROW_NUMBER() OVER (
              PARTITION BY objectid
              order by
                a.gdb_from_date DESC
            ) rn,
            a.GDB_IS_DELETE,
            a.GDB_BRANCH_ID
          from
            FeatureTwo a
          WHERE
            a.GDB_BRANCH_ID = 0
        ) MB
      Where
        MB.rn = 1
        and MB.GDB_IS_DELETE = 0
    )
    OR ftwo.GDB_ARCHIVE_OID is NULL
  )
UNION ALL
Select
  o.OBJECTID as OBJECTID,
  fthree.OBJECTID as ExternalRefId,
  o.GlobalID as OrganisationGlobalId,
  cast (
    fthree.ExternalId as varchar(250)
  ) as UniqueId,
  ofl.lastEditedDate as LastModifiedDate,
  fthree.CreatedDate as CreatedDate,
  ofl.FeatureLayerTypeId
from
  FeatureThree fthree
  join OrgFeatureLayer ofl on ofl.FeatureLayerId = ebd.GlobalID
  join Organisation o on ofl.OrganisationId = o.GlobalID
where
  ofl.GDB_ARCHIVE_OID IN (
    select
      MB.GDB_ARCHIVE_OID
    from
      (
        select
          GDB_ARCHIVE_OID,
          ROW_NUMBER() OVER (
            PARTITION BY objectid
            order by
              a.gdb_from_date DESC
          ) rn,
          a.GDB_IS_DELETE,
          a.GDB_BRANCH_ID
        from
          OrgFeatureLayer a
        WHERE
          a.GDB_BRANCH_ID = 0
      ) MB
    Where
      MB.rn = 1
      and MB.GDB_IS_DELETE = 0
  )
  AND (
    o.GDB_ARCHIVE_OID IN (
      select
        MB.GDB_ARCHIVE_OID
      from
        (
          select
            GDB_ARCHIVE_OID,
            ROW_NUMBER() OVER (
              PARTITION BY objectid
              order by
                a.gdb_from_date DESC
            ) rn,
            a.GDB_IS_DELETE,
            a.GDB_BRANCH_ID
          from
            Organisation a
          WHERE
            a.GDB_BRANCH_ID = 0
        ) MB
      Where
        MB.rn = 1
        and MB.GDB_IS_DELETE = 0
    )
    OR o.GDB_ARCHIVE_OID is NULL
  )
  AND (
    ebd.GDB_ARCHIVE_OID IN (
      select
        MB.GDB_ARCHIVE_OID
      from
        (
          select
            GDB_ARCHIVE_OID,
            ROW_NUMBER() OVER (
              PARTITION BY objectid
              order by
                a.gdb_from_date DESC
            ) rn,
            a.GDB_IS_DELETE,
            a.GDB_BRANCH_ID
          from
            FeatureThree a
          WHERE
            a.GDB_BRANCH_ID = 0
        ) MB
      Where
        MB.rn = 1
        and MB.GDB_IS_DELETE = 0
    )
    OR fthree.GDB_ARCHIVE_OID is NULL
  )
UNION ALL
Select
  o.OBJECTID as OBJECTID,
  ffour.OBJECTID as ExternalRefId,
  o.GlobalID as OrganisationGlobalId,
  cast (
    ffour.ExternalId as varchar(250)
  ) as UniqueId,
  ofl.lastEditedDate as LastModifiedDate,
  ffour.CreatedDate as CreatedDate,
  ofl.FeatureLayerTypeId
from
  FeatureFour ffour
  join OrgFeatureLayer ofl on ofl.FeatureLayerId = stc.GlobalID
  join Organisation o on ofl.OrganisationId = o.GlobalID
where
  ofl.GDB_ARCHIVE_OID IN (
    select
      MB.GDB_ARCHIVE_OID
    from
      (
        select
          GDB_ARCHIVE_OID,
          ROW_NUMBER() OVER (
            PARTITION BY objectid
            order by
              a.gdb_from_date DESC
          ) rn,
          a.GDB_IS_DELETE,
          a.GDB_BRANCH_ID
        from
          OrgFeatureLayer a
        WHERE
          a.GDB_BRANCH_ID = 0
      ) MB
    Where
      MB.rn = 1
      and MB.GDB_IS_DELETE = 0
  )
  AND (
    o.GDB_ARCHIVE_OID IN (
      select
        MB.GDB_ARCHIVE_OID
      from
        (
          select
            GDB_ARCHIVE_OID,
            ROW_NUMBER() OVER (
              PARTITION BY objectid
              order by
                a.gdb_from_date DESC
            ) rn,
            a.GDB_IS_DELETE,
            a.GDB_BRANCH_ID
          from
            Organisation a
          WHERE
            a.GDB_BRANCH_ID = 0
        ) MB
      Where
        MB.rn = 1
        and MB.GDB_IS_DELETE = 0
    )
    OR o.GDB_ARCHIVE_OID is NULL
  )
  AND (
    stc.GDB_ARCHIVE_OID IN (
      select
        MB.GDB_ARCHIVE_OID
      from
        (
          select
            GDB_ARCHIVE_OID,
            ROW_NUMBER() OVER (
              PARTITION BY objectid
              order by
                a.gdb_from_date DESC
            ) rn,
            a.GDB_IS_DELETE,
            a.GDB_BRANCH_ID
          from
            FeatureFour a
          WHERE
            a.GDB_BRANCH_ID = 0
        ) MB
      Where
        MB.rn = 1
        and MB.GDB_IS_DELETE = 0
    )
    OR ffour.GDB_ARCHIVE_OID is NULL
  )
UNION ALL
Select
  o.OBJECTID as OBJECTID,
  ffive.OBJECTID as ExternalRefId,
  o.GlobalID as OrganisationGlobalId,
  cast (
    ffive.ExternalId as varchar(250)
  ) as UniqueId,
  ofl.lastEditedDate as LastModifiedDate,
  ffive.CreatedDate as CreatedDate,
  ofl.FeatureLayerTypeId
from
  FeatureFive ffive
  join OrgFeatureLayer ofl on ofl.FeatureLayerId = tfp.GlobalID
  join Organisation o on ofl.OrganisationId = o.GlobalID
where
  ofl.GDB_ARCHIVE_OID IN (
    select
      MB.GDB_ARCHIVE_OID
    from
      (
        select
          GDB_ARCHIVE_OID,
          ROW_NUMBER() OVER (
            PARTITION BY objectid
            order by
              a.gdb_from_date DESC
          ) rn,
          a.GDB_IS_DELETE,
          a.GDB_BRANCH_ID
        from
          OrgFeatureLayer a
        WHERE
          a.GDB_BRANCH_ID = 0
      ) MB
    Where
      MB.rn = 1
      and MB.GDB_IS_DELETE = 0
  )
  AND (
    o.GDB_ARCHIVE_OID IN (
      select
        MB.GDB_ARCHIVE_OID
      from
        (
          select
            GDB_ARCHIVE_OID,
            ROW_NUMBER() OVER (
              PARTITION BY objectid
              order by
                a.gdb_from_date DESC
            ) rn,
            a.GDB_IS_DELETE,
            a.GDB_BRANCH_ID
          from
            Organisation a
          WHERE
            a.GDB_BRANCH_ID = 0
        ) MB
      Where
        MB.rn = 1
        and MB.GDB_IS_DELETE = 0
    )
    OR o.GDB_ARCHIVE_OID is NULL
  )
  AND (
    ffive.GDB_ARCHIVE_OID IN (
      select
        MB.GDB_ARCHIVE_OID
      from
        (
          select
            GDB_ARCHIVE_OID,
            ROW_NUMBER() OVER (
              PARTITION BY objectid
              order by
                a.gdb_from_date DESC
            ) rn,
            a.GDB_IS_DELETE,
            a.GDB_BRANCH_ID
          from
            FeatureFive a
          WHERE
            a.GDB_BRANCH_ID = 0
        ) MB
      Where
        MB.rn = 1
        and MB.GDB_IS_DELETE = 0
    )
    OR ffive.GDB_ARCHIVE_OID is NULL
  )