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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105
| SELECT
INC.INC_ID_CONTRAT as CON_IdContratKey
,CON.CON_ID_CONTRAT as REN_IdRenouvKey
,INC.INC_ID_ADHESION as CON_IdContrat
,CNT.CNT_CD_CENTRE as CEN_CodeCentre
,CON.CON_NUMERO as CON_NumContrat
,PRD.PRD_CD_PRODUIT as PRD_CodeProduit
,INC.INC_DT_SOUSCRIPTION as CON_DateAdhesion
,INC.INC_DT_REALISATION as CON_DateRealisation
,CON.CON_DT_DEBUT as CON_DateDebutContrat
,CON.CON_DT_FIN as CON_DateFinDeContrat
,CON.CON_DUREE_CONTRAT as CON_DureeContrat
,CASE WHEN SUBSTRING(CMB.CMB_RESULTAT, 1, 2) = 'OK'
THEN 'OK'
ELSE CASE WHEN CMB.CMB_ID_CONTRAT IS null
THEN 'NA'
ELSE 'KO'
END
END as CON_StatutAppelBienvenue
,CON.CON_TOP_TACITE as CON_TopTacite
,CONVERT(CHAR(6), INC.INC_DT_SOUSCRIPTION, 112) as CON_EcheanceCourante
,CON.CON_NB_LIGNE - 1 as ECH_NbrRenouvCourante
,CASE WHEN CON.CON_NB_LIGNE = 1
THEN '-' ELSE 'M' END
as CON_TypeEcheanceCourante
,ISNULL(CNR.CNR_CODE,'-') as TRE_TypeRenouvCourante
,CONVERT(CHAR(6), INC.INC_DT_REALISATION, 112) as CON_AnneeMoisDeRealisation
--,CASE WHEN CRB.CRB_ID_COORDONNEES_BANCAIRES IS NOT NULL AND RGP3.RGP_LIB ='Eligible AP-LIV'
/*,CASE WHEN INC0.INC_TOP_RIB in ('1','T') -- 23/09/2015
AND RGP3.RGP_LIB ='Eligible AP-LIV'
THEN '1' ELSE '0' END CON_TopDossierComplet*/
-- 11/04/2016 -- TFS7743 - Revoir flag DC dans ODS (Tacite ou Tacite à venir)
,CASE WHEN CON0.CON_TOP_TACITE in ('1','2','3','5') AND RGP3.RGP_LIB ='Eligible AP-LIV' THEN '1' ELSE '0' END CON_TopDossierComplet
,CASE RSV.RSV_LIB WHEN 'Vente à distance' THEN '1' ELSE '0' END CON_TopVD
,INC.INC_NUM_REF_GED as CON_NumStock
,RSV_LIB CON_ReseauVente
,CON0.CON_NUM_REF_GED
,CON0.CON_OPTION_VINCI
,CNC_CD_USER+CNC_TYPE_CONTACT Code_APPAFF
FROM dbo.T_CONTRAT_CON CON with (nolock)
INNER join T_CONTRAT_CON CON0 with (nolock)
ON CON.INC_ID_CONTRAT=CON0.INC_ID_CONTRAT
and CON0.CON_NB_LIGNE=1
INNER JOIN dbo.T_INFOS_CONTRAT_INC INC with (nolock)
ON CON.INC_ID_CONTRAT = INC.INC_ID_CONTRAT
AND INC.INC_ID_ADHESION IS NOT NULL
INNER JOIN dbo.T_INFOS_CONTRAT_INC INC0 with (nolock)
ON CON0.INC_ID_CONTRAT = INC0.INC_ID_CONTRAT
AND INC0.INC_ID_ADHESION IS NOT NULL
INNER JOIN T_PRODUIT_PRD PRD with (nolock)
ON CON.PRD_ID_PRODUIT = PRD.PRD_ID_PRODUIT
LEFT JOIN TJ_REGROUPEMENT_PRODUIT_RGP RGP3 with (nolock)
ON PRD.PRD_ID_PRODUIT = RGP3.PRD_ID_PRODUIT
AND RGP3.RGR_ID_REGROUPEMENT = (SELECT RGR_ID_REGROUPEMENT FROM TR_REGROUPEMENT_RGR WHERE RGR_ID_SOURCE='3')
LEFT JOIN dbo.TR_CANAL_RENOUVELLEMENT_CNR CNR with (nolock)
ON CON.CNR_ID_CANAL_RENOU = CNR.CNR_ID_CANAL_RENOU
LEFT OUTER JOIN (
SELECT distinct CMB.CMB_ID_CONTRAT, CMB.CMB_RESULTAT
from adhexp.T_CAMPAGNE_BIENVENUE_CMB CMB with (nolock)
join (
select distinct CMB_ID_CONTRAT, MAX(CMB_DT_APPEL) as CMB_DT_APPEL
from adhexp.T_CAMPAGNE_BIENVENUE_CMB with (nolock)
group by CMB_ID_CONTRAT
) MCM
on CMB.CMB_ID_CONTRAT = MCM.CMB_ID_CONTRAT
and CMB.CMB_DT_APPEL = MCM.CMB_DT_APPEL
) CMB
ON INC.INC_ID_ADHESION = CMB.CMB_ID_CONTRAT
INNER JOIN dbo.TJ_CONTRAT_INDIVIDUS_CNI AS CNICENTRE with (nolock)
ON CNICENTRE.CON_ID_CONTRAT = CON.INC_ID_CONTRAT
AND CNICENTRE.CNI_TOP_DERNIER_ACTEUR_ROLE = '1'
AND CNICENTRE.RLI_ID_ROLE_INDIVIDU = (SELECT RLI_ID_ROLE_INDIVIDU FROM DBO.TR_ROLE_INDIVIDU_RLI WHERE RLI_LIB = 'Distributeur')
INNER JOIN dbo.T_INDIVIDU_IND IND with (nolock)
ON CNICENTRE.IND_ID_INDIVIDU = IND.IND_ID_INDIVIDU
INNER JOIN dbo.T_CENTRE_CNT CNT with (nolock)
ON CNT.CNT_ID_CENTRE = IND.CNT_ID_CENTRE
INNER JOIN dbo.TR_RESEAU_VENTE_RSV RSV with (nolock)
ON INC.RSV_ID_RESEAU_VENTE = RSV.RSV_ID_RESEAU_VENTE
INNER JOIN dbo.TJ_CONTRAT_INDIVIDUS_CNI AS CNIADH with (nolock)
ON CNIADH.CON_ID_CONTRAT = CON.INC_ID_CONTRAT
AND CNIADH.CNI_TOP_DERNIER_ACTEUR_ROLE = '1'
AND CNIADH.RLI_ID_ROLE_INDIVIDU = (SELECT RLI_ID_ROLE_INDIVIDU FROM DBO.TR_ROLE_INDIVIDU_RLI WHERE RLI_LIB = 'Adhérent')
LEFT OUTER JOIN dbo.T_COORDONNEES_BANCAIRES_CRB CRB with (nolock)
ON CRB.IND_ID_INDIVIDU = CNIADH.IND_ID_INDIVIDU
AND CRB.CRB_TOP_PRINCIPAL = '1'
left JOIN dbo.TJ_CONTRAT_INDIVIDUS_CNI AS ConAPPAFF with (nolock)
ON ConAPPAFF.CON_ID_CONTRAT = CON.INC_ID_CONTRAT
AND ConAPPAFF.CNI_TOP_DERNIER_ACTEUR_ROLE = '1'
AND ConAPPAFF.RLI_ID_ROLE_INDIVIDU = (SELECT RLI_ID_ROLE_INDIVIDU FROM DBO.TR_ROLE_INDIVIDU_RLI WHERE RLI_LIB = 'Apporteur d''affaire')
left JOIN dbo.T_INDIVIDU_IND APPAFF with (nolock)
ON ConAPPAFF.IND_ID_INDIVIDU = APPAFF.IND_ID_INDIVIDU
left join T_CONTACT_CENTRE_CNC CNC
ON CNC.CNC_ID_CONTACT_CENTRE=APPAFF.CNC_ID_CONTACT_CENTRE
WHERE
(CON.CON_DT_CREATION_SYS >= DATEADD(DAY, -90, GETDATE())
OR CON.CON_DT_MODIFICATION_SYS >= DATEADD(DAY, -90, GETDATE())
OR CNICENTRE.CNI_DT_CREATION_SYS >= DATEADD(DAY, -90, GETDATE())
OR CRB_DT_CREATION_SYS >= DATEADD(DAY, -90, GETDATE())
OR CRB.CRB_DT_MODIFICATION_SYS >= DATEADD(DAY, -90, GETDATE())
)
--CON.CON_ID_CONTRAT='7FDE079C-6DC1-449B-9626-75486D290B30'
AND
CON.CON_TOP_DERNIERE_LIGNE = '1' |
Partager