Update ASDK_INCIDENT
Set Inci_customer_id = isnull((Select top 1 codusuario From Usuarios 
                               Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_incident.inci_customer_id) 
                                 and username not like '%@%')
                              , Inci_customer_id)
Where Inci_customer_id in (Select usuarios.CodUsuario From USUARIOS
                           Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                 and usuarios.UserName like '%@%'
                                 and usuarios.CodUsuario = ASDK_INCIDENT.inci_customer_id );

Update ASDK_INCIDENT
Set inci_user_id_modifier = isnull((Select top 1 codusuario From Usuarios 
                               Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_incident.inci_user_id_modifier) 
                                 and username not like '%@%')
                              , inci_user_id_modifier)
Where inci_user_id_modifier in (Select usuarios.CodUsuario From USUARIOS
                                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                 and usuarios.UserName like '%@%'
                                 and usuarios.CodUsuario = ASDK_INCIDENT.inci_user_id_modifier );

Update ASDK_INCIDENT
Set Inci_codreceptor = isnull((Select top 1 codusuario From Usuarios 
                               Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_incident.inci_codreceptor) 
                                 and username not like '%@%')
                              , Inci_codreceptor)
Where Inci_codreceptor in (Select codusuario From Usuarios 
                           Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                 and usuarios.UserName like '%@%' 
                                 and codusuario = asdk_incident.inci_codreceptor );

Update ASDK_INCIDENT
Set Inci_author = isnull((Select top 1 codusuario From Usuarios 
                          Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_incident.inci_author) 
                            and username not like '%@%')
                         , Inci_author)
Where Inci_author in (Select codusuario From Usuarios 
                      Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                            and usuarios.UserName like '%@%' 
                            and codusuario = asdk_incident.inci_author );

Update ASDK_INCIDENT
Set Inci_responsible_id = isnull((Select top 1 codusuario From Usuarios 
                                  Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_incident.inci_responsible_id) 
                                    and username not like '%@%')
                           , Inci_author)
Where Inci_responsible_id in (Select codusuario From Usuarios 
                              Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                and usuarios.UserName like '%@%' 
                                and codusuario = ASDK_INCIDENT.inci_responsible_id );

Update ASDK_SERVICE_CALL
Set Serv_customer_id = isnull((Select top 1 codusuario From Usuarios 
                               Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_SERVICE_CALL.serv_customer_id) 
                                 and username not like '%@%')
                           , Serv_customer_id)
Where Serv_customer_id in (Select codusuario From Usuarios 
                           Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                 and usuarios.UserName like '%@%' 
                                 and codusuario = ASDK_SERVICE_CALL.serv_customer_id );

Update ASDK_SERVICE_CALL
Set serv_user_id_modifier = isnull((Select top 1 codusuario From Usuarios 
                               Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_SERVICE_CALL.serv_user_id_modifier) 
                                 and username not like '%@%')
                           , serv_user_id_modifier)
Where serv_user_id_modifier in (Select codusuario From Usuarios 
                                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                 and usuarios.UserName like '%@%' 
                                 and codusuario = ASDK_SERVICE_CALL.serv_user_id_modifier );

Update ASDK_SERVICE_CALL
Set Serv_codreceptor = isnull((Select top 1 codusuario From Usuarios 
                               Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_SERVICE_CALL.serv_codreceptor) 
                                 and username not like '%@%')
                              , Serv_customer_id)
Where Serv_codreceptor in (Select codusuario From Usuarios 
                           Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                 and usuarios.UserName like '%@%' 
                                 and codusuario = ASDK_SERVICE_CALL.serv_codreceptor );

Update ASDK_SERVICE_CALL
Set Serv_author = isnull((Select top 1 codusuario From Usuarios 
                          Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_SERVICE_CALL.serv_author) 
                            and username not like '%@%')
                           , Serv_author)
Where Serv_author in (Select codusuario From Usuarios 
                      Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                            and usuarios.UserName like '%@%' 
                            and codusuario = asdk_SERVICE_CALL.serv_author );

Update ASDK_SERVICE_CALL
Set Serv_responsible_id = isnull((Select top 1 codusuario From Usuarios 
                                  Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_SERVICE_CALL.serv_responsible_id) 
                                    and username not like '%@%')
                           , Serv_responsible_id)
Where Serv_responsible_id in (Select codusuario From Usuarios 
                              Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                and usuarios.UserName like '%@%' 
                                and codusuario = asdk_SERVICE_CALL.serv_responsible_id );

Update ASDK_CHANGE
Set Chan_customer_id = isnull((Select top 1 codusuario From Usuarios 
                               Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_CHANGE.Chan_customer_id) 
                                 and username not like '%@%')
                              , Chan_customer_id)
Where Chan_customer_id in (Select usuarios.CodUsuario From USUARIOS
                           Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                 and usuarios.UserName like '%@%'
                                 and usuarios.CodUsuario = ASDK_CHANGE.Chan_customer_id );

Update ASDK_CHANGE
Set Chan_codreceptor = isnull((Select top 1 codusuario From Usuarios 
                               Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_CHANGE.Chan_codreceptor) 
                                 and username not like '%@%')
                              , Chan_codreceptor)
Where Chan_codreceptor in (Select codusuario From Usuarios 
                           Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                 and usuarios.UserName like '%@%' 
                                 and codusuario = ASDK_CHANGE.Chan_codreceptor );

Update ASDK_CHANGE
Set Chan_author = isnull((Select top 1 codusuario From Usuarios 
                          Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_CHANGE.Chan_author) 
                            and username not like '%@%')
                         , Chan_author)
Where Chan_author in (Select codusuario From Usuarios 
                      Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                            and usuarios.UserName like '%@%' 
                            and codusuario = ASDK_CHANGE.Chan_author );

Update ASDK_CHANGE
Set Chan_responsible_id = isnull((Select top 1 codusuario From Usuarios 
                                  Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_CHANGE.Chan_responsible_id) 
                                    and username not like '%@%')
                           , Chan_author)
Where Chan_responsible_id in (Select codusuario From Usuarios 
                              Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                and usuarios.UserName like '%@%' 
                                and codusuario = ASDK_CHANGE.Chan_responsible_id );

Update ASDK_CHANGE
Set chan_user_id_modifier = isnull((Select top 1 codusuario From Usuarios 
                                  Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_CHANGE.chan_user_id_modifier) 
                                    and username not like '%@%')
                           , Chan_author)
Where chan_user_id_modifier in (Select codusuario From Usuarios 
                                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                  and usuarios.UserName like '%@%' 
                                  and codusuario = ASDK_CHANGE.chan_user_id_modifier );

Update ASDK_PROBLEM
Set Prob_codreceptor = isnull((Select top 1 codusuario From Usuarios 
                               Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_PROBLEM.Prob_codreceptor) 
                                 and username not like '%@%')
                              , Prob_codreceptor)
Where Prob_codreceptor in (Select codusuario From Usuarios 
                           Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                 and usuarios.UserName like '%@%' 
                                 and codusuario = ASDK_PROBLEM.Prob_codreceptor );

Update ASDK_PROBLEM
Set Prob_author = isnull((Select top 1 codusuario From Usuarios 
                          Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_PROBLEM.Prob_author) 
                            and username not like '%@%')
                         , Prob_author)
Where Prob_author in (Select codusuario From Usuarios 
                      Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                            and usuarios.UserName like '%@%' 
                            and codusuario = ASDK_PROBLEM.Prob_author );

Update ASDK_PROBLEM
Set Prob_responsible_id = isnull((Select top 1 codusuario From Usuarios 
                                  Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_PROBLEM.Prob_responsible_id) 
                                    and username not like '%@%')
                           , Prob_author)
Where Prob_responsible_id in (Select codusuario From Usuarios 
                              Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                and usuarios.UserName like '%@%' 
                                and codusuario = ASDK_PROBLEM.Prob_responsible_id );

Update ASDK_PROBLEM
Set Prob_user_id_modifier = isnull((Select top 1 codusuario From Usuarios 
                                  Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_PROBLEM.Prob_user_id_modifier) 
                                    and username not like '%@%')
                           , Prob_author)
Where Prob_user_id_modifier in (Select codusuario From Usuarios 
                                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                  and usuarios.UserName like '%@%' 
                                  and codusuario = ASDK_PROBLEM.Prob_user_id_modifier );

Update ASDK_ACTION_HIST
Set achi_author = isnull((Select top 1 codusuario From Usuarios 
                          Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_ACTION_HIST.achi_author) 
                            and username not like '%@%')
                         , achi_author)
Where achi_author in (Select codusuario From Usuarios 
                      Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                            and usuarios.UserName like '%@%' 
                            and codusuario = ASDK_ACTION_HIST.achi_author );

Update ASDK_HIST_Notes
Set hino_author = isnull((Select top 1 codusuario From Usuarios 
                          Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_HIST_Notes.hino_author) 
                            and username not like '%@%')
                         , hino_author)
Where hino_author in (Select codusuario From Usuarios 
                      Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                            and usuarios.UserName like '%@%' 
                            and codusuario = ASDK_HIST_Notes.hino_author );

Update ASDK_HIST_FILES
Set hifi_author = isnull((Select top 1 codusuario From Usuarios 
                          Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_HIST_FILES.hifi_author) 
                            and username not like '%@%')
                         , hifi_author)
Where hifi_author in (Select codusuario From Usuarios 
                      Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                            and usuarios.UserName like '%@%' 
                            and codusuario = ASDK_HIST_FILES.hifi_author );

Update ASDK_router_hist
Set rohi_old_user = isnull((Select top 1 codusuario From Usuarios 
                            Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_router_hist.rohi_old_user) 
                              and username not like '%@%')
                           , rohi_old_user)
Where rohi_old_user in (Select codusuario From Usuarios 
                        Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                              and usuarios.UserName like '%@%' 
                              and codusuario = ASDK_router_hist.rohi_old_user );

Update CI_HISTORICAL_ITEM
Set FL_INT_USER_COMMENT_ID = isnull((Select top 1 codusuario From Usuarios 
                                     Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = CI_HISTORICAL_ITEM.FL_INT_USER_COMMENT_ID) 
                                       and username not like '%@%')
                           , FL_INT_USER_COMMENT_ID)
Where FL_INT_USER_COMMENT_ID in (Select codusuario From Usuarios 
                        Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                              and usuarios.UserName like '%@%' 
                              and codusuario = CI_HISTORICAL_ITEM.FL_INT_USER_COMMENT_ID );

Update CI_CONFIGURATION_ITEM
Set conf_user_id_modifier = isnull((Select top 1 codusuario From Usuarios 
                            Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = CI_CONFIGURATION_ITEM.conf_user_id_modifier) 
                              and username not like '%@%')
                           , conf_user_id_modifier)
Where conf_user_id_modifier in (Select codusuario From Usuarios 
                                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                  and usuarios.UserName like '%@%' 
                                  and codusuario = CI_CONFIGURATION_ITEM.conf_user_id_modifier );

Update CI_CONFIGURATION_ITEM
Set conf_user_id_creation = isnull((Select top 1 codusuario From Usuarios 
                                    Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = CI_CONFIGURATION_ITEM.conf_user_id_creation) 
                                      and username not like '%@%')
                                   , conf_user_id_creation)
Where conf_user_id_creation in (Select codusuario From Usuarios 
                                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                  and usuarios.UserName like '%@%' 
                                  and codusuario = CI_CONFIGURATION_ITEM.conf_user_id_creation );

Update CI_CONFIGURATION_ITEM
Set FL_INT_USER_INCHARGE_ID = isnull((Select top 1 codusuario From Usuarios 
                                    Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = CI_CONFIGURATION_ITEM.FL_INT_USER_INCHARGE_ID) 
                                      and username not like '%@%')
                                   , FL_INT_USER_INCHARGE_ID)
Where FL_INT_USER_INCHARGE_ID in (Select codusuario From Usuarios 
                                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                  and usuarios.UserName like '%@%' 
                                  and codusuario = CI_CONFIGURATION_ITEM.FL_INT_USER_INCHARGE_ID );

Update ASDK_router_hist
Set rohi_new_user = isnull((Select top 1 codusuario From Usuarios 
                            Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_router_hist.rohi_new_user) 
                              and username not like '%@%')
                           , rohi_new_user)
Where rohi_new_user in (Select codusuario From Usuarios 
                        Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                              and usuarios.UserName like '%@%' 
                              and codusuario = ASDK_router_hist.rohi_new_user );

Update asdk_survey_cases
Set surv_user_id = isnull((Select top 1 codusuario From Usuarios 
                           Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = asdk_survey_cases.surv_user_id) 
                             and username not like '%@%')
                           , surv_user_id)
Where surv_user_id in (Select codusuario From Usuarios 
                       Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                             and usuarios.UserName like '%@%' 
                             and codusuario = asdk_survey_cases.surv_user_id );

Update ASS_ARTICLE
Set FL_INT_AUTOR_ID = isnull((Select top 1 codusuario From Usuarios 
                              Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASS_ARTICLE.FL_INT_AUTOR_ID) 
                                and username not like '%@%')
                             , FL_INT_AUTOR_ID)
Where FL_INT_AUTOR_ID in (Select codusuario From Usuarios 
                          Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                             and usuarios.UserName like '%@%' 
                             and codusuario = ASS_ARTICLE.FL_INT_AUTOR_ID );

Update ASS_ARTICLE
Set fl_int_responsible_id = isnull((Select top 1 codusuario From Usuarios 
                                    Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASS_ARTICLE.fl_int_responsible_id) 
                                      and username not like '%@%')
                                   , fl_int_responsible_id)
Where fl_int_responsible_id in (Select codusuario From Usuarios 
                                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                  and usuarios.UserName like '%@%' 
                                  and codusuario = ASS_ARTICLE.fl_int_responsible_id );

Update ASS_ARTICLE
Set ARTI_USER_ID_MODIFIER = isnull((Select top 1 codusuario From Usuarios 
                                    Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASS_ARTICLE.ARTI_USER_ID_MODIFIER) 
                                      and username not like '%@%')
                                   , ARTI_USER_ID_MODIFIER)
Where ARTI_USER_ID_MODIFIER in (Select codusuario From Usuarios 
                                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                  and usuarios.UserName like '%@%' 
                                  and codusuario = ASS_ARTICLE.ARTI_USER_ID_MODIFIER );

Update ASDK_SERVICE
Set FL_INT_RESPONSABLE_ID = isnull((Select top 1 codusuario From Usuarios 
                                    Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_SERVICE.FL_INT_RESPONSABLE_ID) 
                                      and username not like '%@%')
                                   , FL_INT_RESPONSABLE_ID)
Where FL_INT_RESPONSABLE_ID in (Select codusuario From Usuarios 
                                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                  and usuarios.UserName like '%@%' 
                                  and codusuario = ASDK_SERVICE.FL_INT_RESPONSABLE_ID );

Update ASDK_SERVICE
Set serv_author = isnull((Select top 1 codusuario From Usuarios 
                          Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_SERVICE.serv_author) 
                             and username not like '%@%')
                           , serv_author)
Where serv_author in (Select codusuario From Usuarios 
                      Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                             and usuarios.UserName like '%@%' 
                             and codusuario = ASDK_SERVICE.serv_author );

Update GROUP_SOFTWARE
Set codusuario = isnull((Select top 1 codusuario From Usuarios 
                         Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = GROUP_SOFTWARE.codusuario) 
                             and username not like '%@%')
                        , codusuario)
Where codusuario in (Select codusuario From Usuarios 
                     Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                           and usuarios.UserName like '%@%' 
                           and codusuario = GROUP_SOFTWARE.codusuario );

Update AQM_REPORT
Set CODUSUARIO_MODIFIED = isnull((Select top 1 codusuario From Usuarios 
                                  Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = AQM_REPORT.CODUSUARIO_MODIFIED) 
                                    and username not like '%@%')
                                 , CODUSUARIO_MODIFIED)
Where CODUSUARIO_MODIFIED in (Select codusuario From Usuarios 
                              Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                and usuarios.UserName like '%@%' 
                                and codusuario = AQM_REPORT.CODUSUARIO_MODIFIED );

Update AQM_FOLDER
Set CODUSUARIO_CREATED = isnull((Select top 1 codusuario From Usuarios 
                                 Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = AQM_FOLDER.CODUSUARIO_CREATED) 
                                   and username not like '%@%')
                                , CODUSUARIO_CREATED)
Where CODUSUARIO_CREATED in (Select codusuario From Usuarios 
                             Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                               and usuarios.UserName like '%@%' 
                               
                               and codusuario = AQM_FOLDER.CODUSUARIO_CREATED );

Update AQM_FOLDER
Set CODUSUARIO_MODIFIED = isnull((Select top 1 codusuario From Usuarios 
                                  Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = AQM_FOLDER.CODUSUARIO_MODIFIED) 
                                    and username not like '%@%')
                                 , CODUSUARIO_MODIFIED)
Where CODUSUARIO_MODIFIED in (Select codusuario From Usuarios 
                              Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                                and usuarios.UserName like '%@%' 
                                and codusuario = AQM_FOLDER.CODUSUARIO_MODIFIED );

Update ASDK_CALC_DATES_CONTROL
Set cdco_user = isnull((Select top 1 codusuario From Usuarios 
                        Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_CALC_DATES_CONTROL.cdco_user) 
                             and username not like '%@%')
                       , cdco_user)
Where cdco_user in (Select codusuario From Usuarios 
                    Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                          and usuarios.UserName like '%@%' 
                          and codusuario = ASDK_CALC_DATES_CONTROL.cdco_user );

Update GROUPHD
Set GRP_USER = isnull((Select top 1 codusuario From Usuarios 
                        Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = GROUPHD.GRP_USER) 
                             and username not like '%@%')
                       , GRP_USER)
Where GRP_USER in (Select codusuario From Usuarios 
                    Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                          and usuarios.UserName like '%@%' 
                          and codusuario = GROUPHD.GRP_USER );

Update ASDK_CATEGORY
Set FL_INT_COD_ESPECIALISTA = isnull((Select top 1 codusuario From Usuarios 
                        Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_CATEGORY.FL_INT_COD_ESPECIALISTA) 
                             and username not like '%@%')
                       , FL_INT_COD_ESPECIALISTA)
Where FL_INT_COD_ESPECIALISTA in (Select codusuario From Usuarios 
                    Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                          and usuarios.UserName like '%@%' 
                          and codusuario = ASDK_CATEGORY.FL_INT_COD_ESPECIALISTA );

Update USUARIOS
Set Codperfil = isnull((Select top 1 codperfil From Usuarios 
                        Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = USUARIOS.codusuario) 
                          and username not like '%@%')
                       , codperfil)
Where codusuario in (Select U2.codusuario From Usuarios U2
                     Where (Select U.codusuario from usuarios U where U.username = left(U2.username, charindex('@', U2.username)-1)) is not null
                          and U2.UserName like '%@%' 
                          and U2.codusuario = USUARIOS.codusuario );


Insert into ASDK_SLA_ULA_USER
Select TMP.suus_slul_id, TMP.IDusuBueno
From (Select distinct ASDK_SLA_ULA_USER.suus_slul_id
          , (Select top 1 codusuario From Usuarios 
             Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_SLA_ULA_USER.suus_user_id) 
               and username not like '%@%') IDusuBueno
      From ASDK_SLA_ULA_USER
      Where ASDK_SLA_ULA_USER.suus_user_id 
            in (Select codusuario From Usuarios 
                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                  and usuarios.UserName like '%@%' 
                  and codusuario = ASDK_SLA_ULA_USER.suus_user_id )
     ) TMP left join ASDK_SLA_ULA_USER on TMP.suus_slul_id = ASDK_SLA_ULA_USER.suus_slul_id 
                                          and TMP.IDusuBueno = ASDK_SLA_ULA_USER.suus_user_id
Where TMP.IDusuBueno is not null and ASDK_SLA_ULA_USER.suus_user_id is null;

Delete from ASDK_SLA_ULA_USER 
Where suus_user_id in (Select codusuario from Usuarios 
                       Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null 
                         and usuarios.UserName like '%@%' 
                           );

Insert into Role_users
Select TMP.ROL_ID, TMP.IDusuBueno
From (Select distinct Role_users.ROL_ID
          , (Select top 1 codusuario From Usuarios 
             Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Role_users.CODUSUARIO) 
               and username not like '%@%') IDusuBueno
      From Role_users
      Where Role_users.CODUSUARIO 
            in (Select codusuario From Usuarios 
                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                  and usuarios.UserName like '%@%' 
                  and codusuario = Role_users.CODUSUARIO )
     ) TMP left join Role_users on TMP.ROL_ID = Role_users.ROL_ID 
                                   and TMP.IDusuBueno = Role_users.CODUSUARIO
Where TMP.IDusuBueno is not null and Role_users.CODUSUARIO is null;

Delete from Role_users 
Where codusuario in (Select codusuario from Usuarios 
                     Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null 
                       and usuarios.UserName like '%@%' 
                         );

Insert into AQM_GROUP_USERS
Select TMP.GROUP_ID, TMP.IDusuBueno
From (Select distinct AQM_GROUP_USERS.GROUP_ID
          , (Select top 1 codusuario From Usuarios 
             Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = AQM_GROUP_USERS.CODUSUARIO) 
               and username not like '%@%') IDusuBueno
      From AQM_GROUP_USERS
      Where AQM_GROUP_USERS.CODUSUARIO 
            in (Select codusuario From Usuarios 
                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                  and usuarios.UserName like '%@%' 
                  and codusuario = AQM_GROUP_USERS.CODUSUARIO )
     ) TMP left join AQM_GROUP_USERS on TMP.GROUP_ID = AQM_GROUP_USERS.GROUP_ID 
                                   and TMP.IDusuBueno = AQM_GROUP_USERS.CODUSUARIO
Where TMP.IDusuBueno is not null and AQM_GROUP_USERS.CODUSUARIO is null;

Delete from AQM_GROUP_USERS 
Where codusuario in (Select codusuario from Usuarios 
                     Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null 
                       and usuarios.UserName like '%@%' 
                         );


Update Additionaldata_user
Set ADU_FIELD1 = Case When TMPmalos.ADU_FIELD1 is not null and ADDITIONALDATA_USER.ADU_FIELD1 is null Then TMPmalos.ADU_FIELD1 Else ADDITIONALDATA_USER.ADU_FIELD1 End
   ,ADU_FIELD2 = Case When TMPmalos.ADU_FIELD2 is not null and ADDITIONALDATA_USER.ADU_FIELD2 is null Then TMPmalos.ADU_FIELD2 Else ADDITIONALDATA_USER.ADU_FIELD2 End
   ,ADU_FIELD3 = Case When TMPmalos.ADU_FIELD3 is not null and ADDITIONALDATA_USER.ADU_FIELD3 is null Then TMPmalos.ADU_FIELD3 Else ADDITIONALDATA_USER.ADU_FIELD3 End
   ,ADU_FIELD4 = Case When TMPmalos.ADU_FIELD4 is not null and ADDITIONALDATA_USER.ADU_FIELD4 is null Then TMPmalos.ADU_FIELD4 Else ADDITIONALDATA_USER.ADU_FIELD4 End
   ,ADU_FIELD5 = Case When TMPmalos.ADU_FIELD5 is not null and ADDITIONALDATA_USER.ADU_FIELD5 is null Then TMPmalos.ADU_FIELD5 Else ADDITIONALDATA_USER.ADU_FIELD5 End
   ,ADU_FIELD6 = Case When TMPmalos.ADU_FIELD6 is not null and ADDITIONALDATA_USER.ADU_FIELD6 is null Then TMPmalos.ADU_FIELD6 Else ADDITIONALDATA_USER.ADU_FIELD6 End
   ,ADU_FIELD7 = Case When TMPmalos.ADU_FIELD7 is not null and ADDITIONALDATA_USER.ADU_FIELD7 is null Then TMPmalos.ADU_FIELD7 Else ADDITIONALDATA_USER.ADU_FIELD7 End
   ,ADU_FIELD8 = Case When TMPmalos.ADU_FIELD8 is not null and ADDITIONALDATA_USER.ADU_FIELD8 is null Then TMPmalos.ADU_FIELD8 Else ADDITIONALDATA_USER.ADU_FIELD8 End
   ,ADU_FIELD9 = Case When TMPmalos.ADU_FIELD9 is not null and ADDITIONALDATA_USER.ADU_FIELD9 is null Then TMPmalos.ADU_FIELD9 Else ADDITIONALDATA_USER.ADU_FIELD9 End
   ,ADU_FIELD10 = Case When TMPmalos.ADU_FIELD10 is not null and ADDITIONALDATA_USER.ADU_FIELD10 is null Then TMPmalos.ADU_FIELD10 Else ADDITIONALDATA_USER.ADU_FIELD10 End
FROM ADDITIONALDATA_USER inner join USUARIOS on ADDITIONALDATA_USER.CODUSUARIO = USUARIOS.CodUsuario
     inner join (Select distinct left(U.username, charindex('@', U.username)-1) Username, ADDITIONALDATA_USER.*
                 From Usuarios U inner join Additionaldata_user on U.codusuario = Additionaldata_user.CODUSUARIO
                 Where (Select codusuario from usuarios where username = left(u.username, charindex('@', u.username)-1)) is not null
                       and U.UserName like '%@%' 
                ) TMPmalos on USUARIOS.UserName = TMPmalos.UserName
Where ADDITIONALDATA_USER.CODUSUARIO 
      in (Select codusuario From Usuarios 
          Where (Select U.codusuario from usuarios U 
                 where U.username like '%@%' and left(U.username, charindex('@', U.username)-1) = Usuarios.username) is not null
            and username not like '%@%' );

Delete from Additionaldata_user 
Where codusuario in (Select codusuario from Usuarios 
                     Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null 
                       and usuarios.UserName like '%@%' );


Insert into Asdk_service_user
Select TMP.seus_service_id, TMP.IDusuBueno
From (Select distinct Asdk_service_user.seus_service_id
          , (Select top 1 codusuario From Usuarios 
             Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_service_user.seus_user_id) 
               and username not like '%@%') IDusuBueno
      From Asdk_service_user
      Where Asdk_service_user.seus_user_id 
            in (Select codusuario From Usuarios 
                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                  and usuarios.UserName like '%@%' 
                  and codusuario = Asdk_service_user.seus_user_id )
     ) TMP left join Asdk_service_user on TMP.seus_service_id = Asdk_service_user.seus_service_id 
                                   and TMP.IDusuBueno = Asdk_service_user.seus_user_id
Where TMP.IDusuBueno is not null and Asdk_service_user.seus_user_id is null;

Delete from Asdk_service_user 
Where SEUS_USER_ID in (Select codusuario from Usuarios 
                       Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null 
                         and usuarios.UserName like '%@%' 
                           );

Insert into Group_users
Select TMP.GRP_ID, TMP.IDusuBueno, TMP.grus_cost, TMP.grus_price
From (Select distinct Group_users.GRP_ID
          , (Select top 1 codusuario From Usuarios 
             Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Group_users.CODUSUARIO) 
               and username not like '%@%') IDusuBueno
          , GROUP_USERS.grus_cost, GROUP_USERS.grus_price
      From Group_users
      Where Group_users.CODUSUARIO 
            in (Select codusuario From Usuarios 
                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                  and usuarios.UserName like '%@%' 
                  and codusuario = Group_users.CODUSUARIO )
     ) TMP left join Group_users on TMP.GRP_ID = Group_users.GRP_ID 
                                   and TMP.IDusuBueno = Group_users.CODUSUARIO
Where TMP.IDusuBueno is not null and Group_users.CODUSUARIO is null;

Delete from Group_users 
Where codusuario in (Select codusuario from Usuarios 
                     Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null 
                       and usuarios.UserName like '%@%' 
                         );

Insert into ADASH_USUARIOxGROUP
Select (select ISNULL(max(uxg_id), 0) NuevoID From ADASH_USUARIOxGROUP) + ROW_NUMBER() over (order by ADASH_USUARIOxGROUP.uxg_id) Consecutivo
      ,TMP.grou_id, TMP.IDusuBueno
From (Select distinct ADASH_USUARIOxGROUP.grou_id
          , (Select top 1 codusuario From Usuarios 
             Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ADASH_USUARIOxGROUP.CODUSUARIO) 
               and username not like '%@%') IDusuBueno
      From ADASH_USUARIOxGROUP
      Where ADASH_USUARIOxGROUP.CODUSUARIO 
            in (Select codusuario From Usuarios 
                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                  and usuarios.UserName like '%@%' 
                  and codusuario = ADASH_USUARIOxGROUP.CODUSUARIO )
     ) TMP left join ADASH_USUARIOxGROUP on TMP.grou_id = ADASH_USUARIOxGROUP.grou_id 
                                   and TMP.IDusuBueno = ADASH_USUARIOxGROUP.CODUSUARIO
Where TMP.IDusuBueno is not null and ADASH_USUARIOxGROUP.CODUSUARIO is null;

Delete from ADASH_USUARIOxGROUP 
Where codusuario in (Select codusuario from Usuarios 
                     Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null 
                       and usuarios.UserName like '%@%' 
                         );

Insert into Asdk_usuarioxproject
Select TMP.IDusuBueno, TMP.FL_INT_PROJECT_ID
From (Select distinct Asdk_usuarioxproject.FL_INT_PROJECT_ID
          , (Select top 1 codusuario From Usuarios 
             Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = Asdk_usuarioxproject.FL_INT_USUARIO_ID) 
               and username not like '%@%') IDusuBueno
      From Asdk_usuarioxproject
      Where Asdk_usuarioxproject.FL_INT_USUARIO_ID 
            in (Select codusuario From Usuarios 
                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                  and usuarios.UserName like '%@%' 
                  and codusuario = Asdk_usuarioxproject.FL_INT_USUARIO_ID )
     ) TMP left join Asdk_usuarioxproject on TMP.FL_INT_PROJECT_ID = Asdk_usuarioxproject.FL_INT_PROJECT_ID 
                                   and TMP.IDusuBueno = Asdk_usuarioxproject.FL_INT_USUARIO_ID
Where TMP.IDusuBueno is not null and Asdk_usuarioxproject.FL_INT_USUARIO_ID is null;

Delete from Asdk_usuarioxproject 
Where FL_INT_USUARIO_ID in (Select codusuario from Usuarios 
                            Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null 
                              and usuarios.UserName like '%@%' 
                                );

Insert into ASDK_USER_VENDOR
Select TMP.IDusuBueno, TMP.VENDOR_ID
From (Select distinct ASDK_USER_VENDOR.VENDOR_ID
          , (Select top 1 codusuario From Usuarios 
             Where username = (Select left(username, charindex('@', username)-1) From usuarios Where codusuario = ASDK_USER_VENDOR.USER_ID) 
               and username not like '%@%') IDusuBueno
      From ASDK_USER_VENDOR
      Where ASDK_USER_VENDOR.USER_ID 
            in (Select codusuario From Usuarios 
                Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null
                  and usuarios.UserName like '%@%' 
                  
                  and codusuario = ASDK_USER_VENDOR.USER_ID   )
     ) TMP left join ASDK_USER_VENDOR on TMP.VENDOR_ID = ASDK_USER_VENDOR.VENDOR_ID 
                                         and TMP.IDusuBueno = ASDK_USER_VENDOR.USER_ID
Where TMP.IDusuBueno is not null and ASDK_USER_VENDOR.USER_ID is null;

Delete from ASDK_USER_VENDOR 
Where USER_ID in (Select codusuario from Usuarios 
                  Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null 
                        and usuarios.UserName like '%@%' 
                          );


Delete From AFW_ADSYNCHRONIZATION_CONTROL
Where sync_userid in (Select codusuario from Usuarios 
                      Where (Select U.codusuario from usuarios U where U.username = left(usuarios.username, charindex('@', usuarios.username)-1)) is not null 
                            and usuarios.UserName like '%@%' 
                              );

DELETE FROM USUARIOS
Where CodUsuario in (Select U.codusuario from Usuarios U
                     Where (Select U2.codusuario from usuarios U2 where U2.username = left(U.username, charindex('@', U.username)-1)) is not null
                       and U.UserName like '%@%');
GO