USE [ArandaCO8] GO /****** Object: View [dbo].[V_ASDK_CASE_DETAILS] Script Date: 10/10/2018 1:51:41 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[V_ASDK_CASE_DETAILS] AS WITH cte_sc AS ( SELECT surv_case_id, COUNT(DISTINCT surv_case_id) surv_count FROM ASDK_SURVEY_CASES GROUP BY surv_case_id UNION ALL SELECT concept_item_id, COUNT(DISTINCT su.token) FROM V_AFW_SURVEY_USER su JOIN AFW_SURVEY_ANSWERS sa ON sa.token = su.token WHERE concept_item_id IS NOT NULL GROUP BY concept_item_id ), cte_scc AS ( SELECT surv_case_id, SUM(surv_count) surv_count FROM cte_sc GROUP BY surv_case_id ), cte_public_note_count AS ( SELECT hino_item_id, hino_row_type, COUNT(1) public_note_count FROM ASDK_HIST_NOTES WHERE hino_visible = 1 GROUP BY hino_item_id, hino_row_type ), cte_precases AS ( SELECT prin_serv_id ClassifiedCaseId, serv_id_by_project ClassifiedCaseIdByProject, prin_prec_id PrecId, 'SERVICE CALL' ClassifiedTo FROM ASDK_PRECASE_SERV_CALL JOIN ASDK_SERVICE_CALL ON serv_id = prin_serv_id UNION ALL SELECT prin_inci_id, inci_id_by_project ClassifiedCaseIdByProject, prin_prec_id, 'INCIDENT' FROM ASDK_PRECASE_INCIDENT JOIN ASDK_INCIDENT ON inci_id = prin_inci_id UNION ALL SELECT prin_chan_id, chan_id_by_project ClassifiedCaseIdByProject, prin_prec_id, 'CHANGE' FROM ASDK_PRECASE_CHANGE JOIN ASDK_CHANGE ON chan_id = prin_chan_id ) SELECT scl.AttentionDate, scl.AttentionDateExpected, scl.AuthorId, usa.uname AuthorName, scl.BuildingDate, scl.CABDate, scl.TesDate, scl.BopDate, act.fl_str_hierarchy CategoryHierarchy, scl.CategoryId, act.ctg_caption CategoryName, CauseId, cse.caus_title CauseName, scl.CiId, cci.fl_str_ci_name CiName, scl.ClosedDate, scl.Commentary, scl.CompanyId, vn.Company CompanyName, scl.ComposedId, scl.Cost, scl.CustomerId, usc.uname CustomerName, usc.email CustomerEmail, scl.Description, scl.Effort, /*msl.plap_emaildate*/ NULL EmailDate, scl.GroupId, grp.grp_name GroupName, NULL HasCurrentVoting, NULL HasKnownError, scs.number HasSurvey, scl.ItemId, scl.IdByProject, scl.ImpactId, aip.impa_description ImpactName, NULL ImplementationDate, InterfaceId, scl.IsOpened, ISNULL(provider, 0) IsStateProvider, scl.PriorityId, apt.prio_description PriorityName, scl.ProcedureId, apc.prname ProcedureName, scl.Progress, scl.ProjectId, apj.fl_str_project_name ProjectName, auc.sla_provider_resp ProviderId, auc_c.company ProviderName, ISNULL(scl.ReasonId, -1) ReasonId, ars.reas_description ReasonName, scl.ReceptorId, scl.RegistrationDate, scl.RegistryTypeId, rtp.ret_description RegistryTypeName, scl.ReviewDate, scl.RFCDate, NULL RoutingType, scl.ServiceId, asv.name ServiceName, scl.SlaId, asl.name SlaName, scl.SolutionDateExpected, scl.SolutionDateReal, scl.SpecialistId, usr.uname SpecialistName, scl.StageId, stg.stag_name StageName, scl.StateId, ast.stat_name StateName, scl.Subject, NULL TestingDate, Time, scl.CaseType, scl.UrgencyId, urg.urge_description UrgencyName, NULL VotingProcessId, NULL VotingProcessName, pnc.public_note_count HasPublicNote, knownerror, usc_fl.description floor, usc_bl.description building, usc_ct.description city, usc_cn.description country, usc_dp.nomdepto department, usc_hd.description headquarter, cpr.ClassifiedTo, cpr.ClassifiedCaseId, cpr.ClassifiedCaseIdByProject, scl.IsAnnulled, scl.StatusTime, scl.CompliesAttention CumpleAtencion, scl.CompliesSolution CumpleSolucion, scl.ComliesRfc CumpleRfc, scl.ComliesCab, scl.ComliesBui, scl.ComliesTes, scl.ComliesBop, scl.ComliesRev, scl.MaxRFCDate, scl.MaxBuildingDate, scl.MaxTestingDate, scl.MaxBopDate, scl.MaxReviewDate, scl.MaxCABDate, usc.identity_number CustomerIdentity, usc.UserName CustomerUserName, usc.Cha_id CustomerChargeId, usc.cityid CityId, usc.coddepto CodDepartment, usc.buildingid BuildingId, usc.NUMBERDEPID NumberDepId, usc.countryid CountryId, usc.floorid FloorId, usc.siteid SiteId, usc.companyid UserCompanyId, scl.AdditionalField1, scl.AdditionalField2, scl.AdditionalField3, scl.AdditionalField4, scl.AdditionalField5, scl.AdditionalField6, scl.AdditionalField7, scl.AdditionalField8, scl.AdditionalField9, scl.AdditionalField10, scl.AdditionalField11, scl.AdditionalField12, scl.AdditionalField13, scl.AdditionalField14, scl.AdditionalField15, scl.AdditionalField16, scl.AdditionalField17, scl.AdditionalField18, scl.AdditionalField19, scl.AdditionalField20, scl.AdditionalField21, scl.AdditionalField22, scl.AdditionalField23, scl.AdditionalField24, scl.AdditionalField25, scl.AdditionalField26, scl.AdditionalField27, scl.AdditionalField28, scl.AdditionalField29, scl.AdditionalField30, vn.Address1 VendorAddress1, vn.ad_vendor1 VendorAdditional1, usc_sg.group_id SpecialGroupId, scl.IsConfidential FROM ( SELECT inci_attention_real_date AttentionDate, inci_attention_esti_date AttentionDateExpected, inci_author AuthorId, NULL BuildingDate, NULL CABDate, NULL TesDate, NULL BopDate, inci_category_id CategoryId, NULL CauseId, inci_associated_ci CiId, inci_colsed_date ClosedDate, inci_commentary Commentary, inci_composed_id ComposedId, inci_cost Cost, inci_customer_id CustomerId, inci_description Description, inci_effort Effort, inci_responsible_group_id GroupId, inci_id ItemId, inci_id_by_project IdByProject, inci_impact_id ImpactId, NULL InterfaceId, inci_final_status IsOpened, inci_priority_id PriorityId, inci_procedure_id ProcedureId, inci_current_progress Progress, inci_fl_int_project_id ProjectId, inci_reason_id ReasonId, inci_codreceptor ReceptorId, inci_opened_date RegistrationDate, inci_registry_type_id RegistryTypeId, NULL ReviewDate, NULL RFCDate, inci_service_id ServiceId, inci_service_sla_id SlaId, inci_provider_uc_id UcId, inci_expired_date SolutionDateExpected, inci_solution_real_date SolutionDateReal, inci_responsible_id SpecialistId, inci_status_id StateId, inci_subject Subject, inci_current_time Time, 1 CaseType, inci_urgency_id UrgencyId, NULL KnownError, NULL IsAnnulled, inci_vendor_id CompanyId, NULL StageId, inci_status_time StatusTime, SIGN(inci_attention_time - COALESCE(inci_attention_real_time, inci_current_time, 0)) CompliesAttention, SIGN(inci_attention_time + inci_solution_time - COALESCE(inci_solution_real_time, inci_current_time, 0)) CompliesSolution, NULL ComliesRfc, NULL ComliesCab, NULL ComliesBui, NULL ComliesTes, NULL ComliesBop, NULL ComliesRev, NULL MaxRFCDate, NULL MaxCabDate, NULL MaxBuildingDate, NULL MaxTestingDate, NULL MaxBopDate, NULL MaxReviewDate, ADD_STR_1 AdditionalField1, ADD_STR_2 AdditionalField2, ADD_STR_3 AdditionalField3, ADD_STR_4 AdditionalField4, ADD_STR_5 AdditionalField5, ADD_STR_6 AdditionalField6, ADD_STR_7 AdditionalField7, ADD_STR_8 AdditionalField8, ADD_STR_9 AdditionalField9, ADD_STR_10 AdditionalField10, ADD_STR_11 AdditionalField11, ADD_STR_12 AdditionalField12, ADD_STR_13 AdditionalField13, ADD_STR_14 AdditionalField14, ADD_STR_15 AdditionalField15, ADD_STR_16 AdditionalField16, ADD_STR_17 AdditionalField17, ADD_STR_18 AdditionalField18, ADD_STR_19 AdditionalField19, ADD_STR_20 AdditionalField20, ADD_STR_21 AdditionalField21, ADD_STR_22 AdditionalField22, ADD_STR_23 AdditionalField23, ADD_STR_24 AdditionalField24, ADD_STR_25 AdditionalField25, ADD_STR_26 AdditionalField26, ADD_STR_27 AdditionalField27, ADD_STR_28 AdditionalField28, ADD_STR_29 AdditionalField29, ADD_STR_30 AdditionalField30, inci_confidential IsConfidential FROM ASDK_INCIDENT UNION ALL SELECT prob_attention_real_date AttentionDate, prob_attention_esti_date AttentionDateExpected, prob_author AuthorId, NULL BuildingDate, NULL CABDate, NULL TesDate, NULL BopDate, prob_category_id CategoryId, NULL CauseId, prob_associated_ci CiId, prob_colsed_date ClosedDate, prob_commentary Commentary, prob_composed_id ComposedId, prob_cost Cost, NULL CustomerId, prob_description Description, prob_effort Effort, prob_responsible_group_id GroupId, prob_id ItemId, prob_id_by_project IdByProject, prob_impact_id ImpactId, NULL InterfaceId, prob_final_status IsOpened, prob_priority_id PriorityId, prob_procedure_id ProcedureId, prob_current_progress Progress, prob_fl_int_project_id ProjectId, prob_reason_id ReasonId, prob_codreceptor ReceptorId, prob_opened_date RegistrationDate, prob_registry_type_id RegistryTypeId, NULL ReviewDate, NULL RFCDate, prob_service_id ServiceId, prob_service_sla_id SlaId, prob_provider_uc_id UcId, prob_expired_date SolutionDateExpected, prob_solution_real_date SolutionDateReal, prob_responsible_id SpecialistId, prob_status_id StateId, prob_subject Subject, prob_current_time Time, 2 CaseType, prob_urgency_id UrgencyId, prob_known_error KnownError, NULL IsAnnulled, NULL CompanyId, NULL StageId, prob_status_time StatusTime, SIGN(prob_attention_time - COALESCE(prob_attention_real_time, prob_current_time, 0)) CompliesAttention, SIGN(prob_attention_time + prob_solution_time - COALESCE(prob_solution_real_time, prob_current_time, 0)) CompliesSolution, NULL ComliesRfc, NULL ComliesCab, NULL ComliesBui, NULL ComliesTes, NULL ComliesBop, NULL ComliesRev, NULL MaxRFCDate, NULL MaxCabDate, NULL MaxBuildingDate, NULL MaxTestingDate, NULL MaxBopDate, NULL MaxReviewDate, ADD_STR_1 AdditionalField1, ADD_STR_2 AdditionalField2, ADD_STR_3 AdditionalField3, ADD_STR_4 AdditionalField4, ADD_STR_5 AdditionalField5, ADD_STR_6 AdditionalField6, ADD_STR_7 AdditionalField7, ADD_STR_8 AdditionalField8, ADD_STR_9 AdditionalField9, ADD_STR_10 AdditionalField10, ADD_STR_11 AdditionalField11, ADD_STR_12 AdditionalField12, ADD_STR_13 AdditionalField13, ADD_STR_14 AdditionalField14, ADD_STR_15 AdditionalField15, ADD_STR_16 AdditionalField16, ADD_STR_17 AdditionalField17, ADD_STR_18 AdditionalField18, ADD_STR_19 AdditionalField19, ADD_STR_20 AdditionalField20, ADD_STR_21 AdditionalField21, ADD_STR_22 AdditionalField22, ADD_STR_23 AdditionalField23, ADD_STR_24 AdditionalField24, ADD_STR_25 AdditionalField25, ADD_STR_26 AdditionalField26, ADD_STR_27 AdditionalField27, ADD_STR_28 AdditionalField28, ADD_STR_29 AdditionalField29, ADD_STR_30 AdditionalField30, 0 IsConfidential FROM ASDK_PROBLEM UNION ALL SELECT chan_attention_date AttentionDate, NULL AttentionDateExpected, chan_author AuthorId, chan_bui_sol_date BuildingDate, chan_cab_sol_date CABDate, chan_tes_sol_date TesDate, chan_bop_sol_date BopDate, chan_category_id CategoryId, NULL CauseId, chan_associated_ci CiId, chan_colsed_date ClosedDate, chan_commentary Commentary, chan_composed_id ComposedId, chan_cost Cost, chan_customer_id CustomerId, chan_description Description, chan_effort Effort, chan_responsible_group_id GroupId, chan_id ItemId, chan_id_by_project IdByProject, chan_impact_id ImpactId, chan_interface_id InterfaceId, chan_final_status IsOpened, chan_priority_id PriorityId, chan_procedure_id ProcedureId, chan_current_progress Progress, chan_fl_int_project_id ProjectId, chan_reason_id ReasonId, chan_codreceptor ReceptorId, chan_opened_date RegistrationDate, chan_registry_type_id RegistryTypeId, chan_rev_sol_date ReviewDate, chan_rfc_sol_date RFCDate, chan_service_id ServiceId, chan_service_sla_id SlaId, chan_provider_uc_id UcId, NULL SolutionDateExpected, NULL SolutionDateReal, chan_responsible_id SpecialistId, chan_status_id StateId, chan_subject Subject, chan_current_time Time, 3 CaseType, chan_urgency_id UrgencyId, NULL KnownError, NULL IsAnnulled, chan_vendor_id CompanyId, chan_current_stage_id StageId, chan_status_time StatusTime, NULL CompliesAttention, NULL CompliesSolution, SIGN(chan_rfc_att_time - COALESCE(chan_rfc_sol_time - chan_current_time, 0)) ComliesRfc, SIGN(chan_cab_sol_time - COALESCE(chan_cab_att_time + chan_rfc_att_time - chan_current_time, 0)) ComliesCab, SIGN(chan_bui_sol_time - COALESCE(chan_bui_att_time + chan_cab_att_time + chan_rfc_att_time - chan_current_time, 0)) ComliesBui, SIGN(chan_tes_sol_time - COALESCE(chan_tes_att_time + chan_bui_att_time + chan_cab_att_time + chan_rfc_att_time - chan_current_time, 0)) ComliesTes, SIGN(chan_bop_sol_time - COALESCE(chan_bop_att_time + chan_tes_att_time + chan_bui_att_time + chan_cab_att_time + chan_rfc_att_time - chan_current_time, 0)) ComliesBop, SIGN(chan_rev_sol_time - COALESCE(chan_rev_att_time + chan_bop_att_time + chan_tes_att_time + chan_bui_att_time + chan_cab_att_time + chan_rfc_att_time - chan_current_time, 0)) ComliesRev, chan_rfc_att_date MaxRFCDate, chan_cab_att_date MaxCabDate, chan_bui_att_date MaxBuildingDate, chan_tes_att_date MaxTestingDate, chan_bop_att_date MaxBopDate, chan_rev_att_date MaxReviewDate, ADD_STR_1 AdditionalField1, ADD_STR_2 AdditionalField2, ADD_STR_3 AdditionalField3, ADD_STR_4 AdditionalField4, ADD_STR_5 AdditionalField5, ADD_STR_6 AdditionalField6, ADD_STR_7 AdditionalField7, ADD_STR_8 AdditionalField8, ADD_STR_9 AdditionalField9, ADD_STR_10 AdditionalField10, ADD_STR_11 AdditionalField11, ADD_STR_12 AdditionalField12, ADD_STR_13 AdditionalField13, ADD_STR_14 AdditionalField14, ADD_STR_15 AdditionalField15, ADD_STR_16 AdditionalField16, ADD_STR_17 AdditionalField17, ADD_STR_18 AdditionalField18, ADD_STR_19 AdditionalField19, ADD_STR_20 AdditionalField20, ADD_STR_21 AdditionalField21, ADD_STR_22 AdditionalField22, ADD_STR_23 AdditionalField23, ADD_STR_24 AdditionalField24, ADD_STR_25 AdditionalField25, ADD_STR_26 AdditionalField26, ADD_STR_27 AdditionalField27, ADD_STR_28 AdditionalField28, ADD_STR_29 AdditionalField29, ADD_STR_30 AdditionalField30, chan_confidential IsConfidential FROM ASDK_CHANGE UNION ALL SELECT serv_attention_real_date AttentionDate, serv_attention_esti_date AttentionDateExpected, serv_author AuthorId, NULL BuildingDate, NULL CABDate, NULL TesDate, NULL BopDate, serv_category_id CategoryId, NULL CauseId, serv_associated_ci CiId, serv_colsed_date ClosedDate, serv_commentary Commentary, serv_composed_id ComposedId, serv_cost Cost, serv_customer_id CustomerId, serv_description Description, serv_effort Effort, serv_responsible_group_id GroupId, serv_id ItemId, serv_id_by_project IdByProject, serv_impact_id ImpactId, NULL InterfaceId, serv_final_status IsOpened, serv_priority_id PriorityId, serv_procedure_id ProcedureId, serv_current_progress Progress, serv_fl_int_project_id ProjectId, serv_reason_id ReasonId, serv_codreceptor ReceptorId, serv_opened_date RegistrationDate, serv_registry_type_id RegistryTypeId, NULL ReviewDate, NULL RFCDate, serv_service_id ServiceId, serv_service_sla_id SlaId, serv_provider_uc_id UcId, serv_expired_date SolutionDateExpected, serv_solution_real_date SolutionDateReal, serv_responsible_id SpecialistId, serv_status_id StateId, serv_subject Subject, serv_current_time Time, 4 CaseType, serv_urgency_id UrgencyId, NULL KnownError, NULL IsAnnulled, serv_vendor_id CompanyId, NULL StageId, serv_status_time StatusTime, SIGN(serv_attention_time - COALESCE(serv_attention_real_time, serv_current_time, 0)) CompliesAttention, SIGN(serv_attention_time + serv_solution_time - COALESCE(serv_solution_real_time, serv_current_time, 0)) CompliesSolution, NULL ComliesRfc, NULL ComliesCab, NULL ComliesBui, NULL ComliesTes, NULL ComliesBop, NULL ComliesRev, NULL MaxRFCDate, NULL MaxCabDate, NULL MaxBuildingDate, NULL MaxTestingDate, NULL MaxBopDate, NULL MaxReviewDate, ADD_STR_1 AdditionalField1, ADD_STR_2 AdditionalField2, ADD_STR_3 AdditionalField3, ADD_STR_4 AdditionalField4, ADD_STR_5 AdditionalField5, ADD_STR_6 AdditionalField6, ADD_STR_7 AdditionalField7, ADD_STR_8 AdditionalField8, ADD_STR_9 AdditionalField9, ADD_STR_10 AdditionalField10, ADD_STR_11 AdditionalField11, ADD_STR_12 AdditionalField12, ADD_STR_13 AdditionalField13, ADD_STR_14 AdditionalField14, ADD_STR_15 AdditionalField15, ADD_STR_16 AdditionalField16, ADD_STR_17 AdditionalField17, ADD_STR_18 AdditionalField18, ADD_STR_19 AdditionalField19, ADD_STR_20 AdditionalField20, ADD_STR_21 AdditionalField21, ADD_STR_22 AdditionalField22, ADD_STR_23 AdditionalField23, ADD_STR_24 AdditionalField24, ADD_STR_25 AdditionalField25, ADD_STR_26 AdditionalField26, ADD_STR_27 AdditionalField27, ADD_STR_28 AdditionalField28, ADD_STR_29 AdditionalField29, ADD_STR_30 AdditionalField30, serv_confidential IsConfidential FROM ASDK_SERVICE_CALL UNION ALL SELECT NULL AtentionDate, NULL AtentionDateExpected, prec_author AuthorId, NULL BuildingDate, NULL CABDate, NULL TesDate, NULL BopDate, NULL CategoryId, NULL CauseId, NULL CiId, NULL ClosedDate, NULL Commentary, prec_composed_id ComposedId, NULL Cost, prec_customer CustomerId, prec_description Description, NULL Effort, NULL GroupId, prec_id ItemId, prec_id_by_project IdByProject, NULL ImpactId, NULL InterfaceId, CASE WHEN prec_classified_to IS NULL AND prec_flag_annulled <> 1 THEN 1 ELSE 0 END IsOpened, NULL PriorityId, NULL ProcedureId, NULL Progress, prec_project_id ProjectId, prec_reason ReasonId, NULL ReceptorId, prec_opened RegistrationDate, prec_registry_type RegistryTypeId, NULL ReviewDate, NULL RFCDate, NULL ServiceId, NULL SlaId, NULL UcId, NULL SolutionDateExpected, NULL SolutionDateReal, NULL SpecialistId, prec_status StateId, prec_subject Subject, NULL Time, 10 CaseType, NULL UrgencyId, NULL KnownError, prec_flag_annulled IsAnnulled, prec_company CompanyId, NULL StageId, NULL StatusTime, NULL CompliesAttention, NULL CompliesSolution, NULL ComliesRfc, NULL ComliesCab, NULL ComliesBui, NULL ComliesTes, NULL ComliesBop, NULL ComliesRev, NULL MaxRFCDate, NULL MaxCabDate, NULL MaxBuildingDate, NULL MaxTestingDate, NULL MaxBopDate, NULL MaxReviewDate, ADD_STR_1 AdditionalField1, ADD_STR_2 AdditionalField2, ADD_STR_3 AdditionalField3, ADD_STR_4 AdditionalField4, ADD_STR_5 AdditionalField5, ADD_STR_6 AdditionalField6, ADD_STR_7 AdditionalField7, ADD_STR_8 AdditionalField8, ADD_STR_9 AdditionalField9, ADD_STR_10 AdditionalField10, ADD_STR_11 AdditionalField11, ADD_STR_12 AdditionalField12, ADD_STR_13 AdditionalField13, ADD_STR_14 AdditionalField14, ADD_STR_15 AdditionalField15, ADD_STR_16 AdditionalField16, ADD_STR_17 AdditionalField17, ADD_STR_18 AdditionalField18, ADD_STR_19 AdditionalField19, ADD_STR_20 AdditionalField20, ADD_STR_21 AdditionalField21, ADD_STR_22 AdditionalField22, ADD_STR_23 AdditionalField23, ADD_STR_24 AdditionalField24, ADD_STR_25 AdditionalField25, ADD_STR_26 AdditionalField26, ADD_STR_27 AdditionalField27, ADD_STR_28 AdditionalField28, ADD_STR_29 AdditionalField29, ADD_STR_30 AdditionalField30, 0 IsConfidential FROM ASDK_PRECASE ) scl LEFT JOIN USUARIOS usa ON usa.codusuario = scl.AuthorId LEFT JOIN ASDK_CATEGORY act ON act.ctg_index = scl.CategoryId LEFT JOIN CI_CONFIGURATION_ITEM cci ON cci.fl_int_ci_id = scl.CiId LEFT JOIN VENDOR vn ON vn.VendorId = scl.CompanyId LEFT JOIN USUARIOS usc ON usc.codusuario = scl.customerid LEFT JOIN FLOOR usc_fl WITH(NOEXPAND) ON usc_fl.floorid = usc.floorid LEFT JOIN BUILDING usc_bl WITH(NOEXPAND) ON usc_bl.buildingid = usc.buildingid LEFT JOIN CITY usc_ct WITH(NOEXPAND) ON usc_ct.cityid = usc.cityid LEFT JOIN COUNTRY usc_cn WITH(NOEXPAND) ON usc_cn.countryid = usc.countryid LEFT JOIN DEPARTAMENTOS usc_dp WITH(NOEXPAND) ON usc_dp.coddepto = usc.coddepto LEFT JOIN SITE usc_hd WITH(NOEXPAND) ON usc_hd.siteid = usc.siteid LEFT JOIN ASDK_SPECIAL_GROUP_USER usc_sg ON usc_sg.user_id = usc.codusuario --LEFT JOIN ACC_MAIL_SOURCE_LOG msl ON msl.plap_id_asdk = scl.ItemId LEFT JOIN GROUPHD grp ON grp.grp_id = scl.GroupId LEFT JOIN SURV_CASES scs WITH(NOEXPAND) ON scs.surv_case_id = scl.ItemId LEFT JOIN ASDK_IMPACT aip ON aip.impa_id = scl.ImpactId LEFT JOIN ASDK_PRIORITY apt ON apt.prio_id = scl.PriorityId LEFT JOIN ASDK_PROCEDURE apc ON apc.prid = scl.ProcedureId LEFT JOIN ASDK_PROJECTS apj ON apj.fl_int_id = scl.ProjectId LEFT JOIN ASDK_REASON ars ON ars.reas_id = scl.ReasonId LEFT JOIN REGISTRY_TYPE rtp WITH(NOEXPAND) ON rtp.ret_id = scl.RegistryTypeId LEFT JOIN ASDK_SERVICE asv ON asv.fl_int_service_id = scl.ServiceId LEFT JOIN ASDK_SLA asl ON asl.sla_id = scl.SlaId LEFT JOIN USUARIOS usr ON usr.codusuario = scl.SpecialistId LEFT JOIN AFW_STATUS ast ON ast.stat_id = scl.StateId LEFT JOIN ASDK_STATUS_BEHAVIOR asb ON asb.stat_id = scl.StateId LEFT JOIN ASDK_URGENCY urg ON urg.urge_id = scl.UrgencyId LEFT JOIN ASDK_STAGE stg ON stg.stag_id = scl.StageId LEFT JOIN ASDK_SLA auc ON auc.sla_id = scl.UcId LEFT JOIN VENDOR auc_c ON auc_c.vendorid = auc.sla_provider_resp LEFT JOIN cte_public_note_count pnc ON pnc.hino_item_id = scl.ItemId AND scl.CaseType = hino_row_type LEFT JOIN ASDK_CAUSE cse ON cse.caus_id = scl.CauseId LEFT JOIN cte_precases cpr ON cpr.PrecId = scl.ItemId AND scl.CaseType = 10 GO