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
42
43
44
| 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
,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,'') as ContactsIt
,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,'') as ContactsBo
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
left outer join T_Link_CartoEntry_Contact as TLCC on TLCC.CartoEntry = TCD.CartoEntry
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_ShortDesc_English LIKE '%carto%')
OR (TCD.CartoEntry_ShortDesc_French LIKE '%carto%')
OR (TCD.CartoEntry_LongDesc_English LIKE '%carto%')
OR (TCD.CartoEntry_LongDesc_French LIKE '%carto%'))
ORDER BY Code |
