----------------------------------------------------------------------------------------------------------------
----	DONNY																								----
----	CARTOFLEX_CONTACTS_PROSPECT_V.USERCRDH, 															----
----	CARTOFLEX_CONTACTS_PROSPECT_V.REPR_0001																----
----	PROSPECT.TIT, PROSPECT.ORIGCOD, T2.TIT,																----
----	AND (PROSPECT.USERCRDH BETWEEN '2022-11-01 00:00:01' AND '2023-03-01 23:30:00') 					----
----------------------------------------------------------------------------------------------------------------

SELECT DISTINCT 
PROSPECT.TIERS, 
PROSPECT."RAISON SOCIAL PROSPECT" AS 'COMPNAME_PREFIX',
PROSPECT.NOM AS 'COMPNAME',
PROSPECT.VIL,
PROSPECT.RUE, 
PROSPECT.LOC, 
PROSPECT.CPOSTAL,
PROSPECT.EMAIL, 
(SELECT DISTINCT TABLE_TITRE_RAISON_SOCIALE_V.LIBELLE FROM TABLE_TITRE_RAISON_SOCIALE_V WHERE TABLE_TITRE_RAISON_SOCIALE_V.TITRE = T2.TIT) AS 'CIVILITY', 
T2.PRENOM AS 'FIRSTNAME', 
T2.NOM AS 'LASTNAME', 
T2.EMAIL, 
T2.FCTCOD_0001, 
T2.FCTCOD_0002 
FROM CARTOFLEX_CONTACTS_PROSPECT_V PROSPECT, T2 T2, MOUV MOUV 
WHERE T2.TIERS = PROSPECT.TIERS 
AND T2.TIERS = PROSPECT.TIERS
AND PROSPECT.TIERS = MOUV.TIERS 
AND T2.TIERS = MOUV.TIERS 
AND PROSPECT.REPR_0001 = 'ACS' 
AND PROSPECT.DOS = MOUV.DOS AND ((PROSPECT.DOS='1') AND (MOUV.DVDT>'2018-01-01'))


----------------------------------------------------------------------------------------------------------------
----	DONNY																								----
----	Ne fonctionne pas car si TABLE_TITRE_RAISON_SOCIALE_V.LIBELLE est vide alors ça ne l'inclut pas		----
----	PRO.NAF, PRO.TIT AS 'PREFIX_CODE', T2.TIT AS 'CIVILITY_CODE' 										----
-----	AND T2.FCTCOD_0001 <> '3' 																			----
----------------------------------------------------------------------------------------------------------------

SELECT DISTINCT PRO.TIERS, PREFIX1.LIBELLE AS 'PREFIX', PRO.NOM AS 'COMPNAME', PRO.RUE, PRO.CPOSTAL AS 'POSTCODE', PRO.VIL AS 'CITY', PRO.EMAIL, 
PREFIX2.LIBELLE AS 'CIVILITY', T2.PRENOM, T2.NOM, T2.EMAIL, T2.FCTCOD_0001, T2.FCTCOD_0002 
FROM CARTOFLEX.dbo.PRO PRO, CARTOFLEX.dbo.TABLE_TITRE_RAISON_SOCIALE_V PREFIX1, CARTOFLEX.dbo.T2 T2, CARTOFLEX.dbo.TABLE_TITRE_RAISON_SOCIALE_V PREFIX2  
WHERE PREFIX1.DOSC = PRO.DOS 
AND PREFIX2.DOSC = PRO.DOS 
AND PREFIX1.TITRE = PRO.TIT 
AND PREFIX2.TITRE = T2.TIT 
AND PRO.DOS='1' 
AND T2.TIERS = PRO.TIERS 
AND PRO.REPR_0001 = 'ACS' 





SELECT DISTINCT PRO.TIERS, PRO.CPOSTAL, PRO.EMAIL, PRO.LIBELLECONTACT, PRO.LOC, PRO.RUE, PRO.TIERS, PRO.TIT, PRO.TITRE, 
PRO.TIT AS 'PREFIX_CODE', PREFIX1.LIBELLE AS 'PREFIX', PRO.NOM AS 'COMPNAME', PRO.RUE, PRO.CPOSTAL AS 'POSTCODE', PRO.VIL AS 'CITY', PRO.EMAIL, 
T2.TIT AS 'CIVILITY_CODE', T2.PRENOM, T2.NOM, T2.EMAIL, T2.FCTCOD_0001, T2.FCTCOD_0002 
FROM CARTOFLEX.dbo.PRO PRO, CARTOFLEX.dbo.TABLE_TITRE_RAISON_SOCIALE_V PREFIX1, CARTOFLEX.dbo.T2 T2 
WHERE PREFIX1.DOSC = PRO.DOS 
AND PREFIX1.TITRE = PRO.TIT 
AND PRO.DOS='1' 
AND T2.TIERS = PRO.TIERS 
AND PRO.REPR_0001 = 'ACS' 



PRO.CPOSTAL, PRO.EMAIL, PRO.FONCTION1, PRO.FONCTION2, PRO.LIBELLECONTACT, PRO.LOC, PRO.RUE, PRO.TIERS, PRO.TIT, PRO.TITRE,