1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
| SELECT DISTINCT TCD.CartoEntry_Key AS CartoEntryId ,
TCD.CartoEntry AS Code ,
TCD.CartoEntry_CodBasicat AS CartoEntryBasicat ,
CASE
WHEN TRS.Label_FR IS NULL
THEN TRS.Label_EN
ELSE TRS.Label_FR
END AS CartoEntryStatus,
TCD.CartoEntry_Object_Type AS CartoEntryObjectType ,
TCD.CartoEntry_Origin AS CartoEntryOrigin ,
TRMD.Manager_Development_Department AS DevelopmentOrganizationOrganization ,
TRBOD.BO_Department_Department AS MainBusinessOwnerOrganization,
ITCONTACT = stuff((select tlcc.Contact_First_Name+' '+tlcc.Contact_Last_Name+','+tlcc.Contact_Email+','+tlcc.Contact_Department+','+tlcc.Contact_Role+','+trcr.Role_EN+';' from
T_Link_CartoEntry_Contact as tlcc inner join T_Ref_Contact_Role as trcr on tlcc.Contact_Role=trcr.Contact_Role
where tlcc.CartoEntry = TCD.CartoEntry and trcr.ShowInSummary=1
order by tlcc.Contact_Last_Name, tlcc.Contact_First_Name for xml path('')),1,0,''),
BOCONTACT = stuff((select tlccbo.Contact_Business_Owner_First_Name+' '+tlccbo.Contact_Business_Owner_Last_Name+','+tlccbo.Contact_Business_Owner_Email+','+tlccbo.Contact_Business_Owner_Department+','+tlccbo.Contact_Business_Owner_Role+','+trbor.Label_EN+';' from
T_Link_CartoEntry_Contact_Business_Owner as tlccbo inner join T_Ref_Business_Owner_Role as trbor on tlccbo.Contact_Business_Owner_Role=trbor.Business_Owner_Role
where tlccbo.CartoEntry = TCD.CartoEntry and trbor.ShowInSummary=1
order by tlccbo.Contact_Business_Owner_Last_Name, tlccbo.Contact_Business_Owner_First_Name for xml path('')),1,0,'')
FROM T_CartoEntry_Description AS TCD
INNER JOIN T_CartoEntry_Organization AS TCO
ON TCD.CartoEntry=TCO.CartoEntry
LEFT OUTER JOIN t_ref_business_subdomain AS TRBS
ON TCD.cartoentry_business_subdomain=TRBS.Business_subdomain
AND TCD.cartoentry_business_domain =TRBS.Business_domain
LEFT OUTER JOIN t_ref_business_domain AS TRBD
ON TRBS.business_domain=TRBD.Business_domain
LEFT OUTER JOIN T_REF_Status AS TRS
ON TCD.CartoEntry_Status = TRS.status
LEFT OUTER JOIN T_Ref_Business_Owner_Department AS TRBOD
ON TCO.CartoEntry_BO_Department = TRBOD.BO_Department_Email
LEFT OUTER JOIN T_Ref_Manager_Development AS TRMD
ON TCO.CartoEntry_Manager_Development = TRMD.Manager_Development_Email
WHERE TCD.CartoEntry_Status NOT LIKE 'Workspace'
AND (TCD.CartoEntry_Status = 'Development'
OR TCD.CartoEntry_Status = 'Think'
OR TCD.CartoEntry_Status = 'Production')
AND ((TCD.CartoEntry LIKE '%carto%')
OR (TCD.CartoEntry_Acronym_Explanation LIKE '%carto%'))
ORDER BY Code |
Partager