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
|
SELECT ODS.INC_ID_ADHESION as CON_IdContrat,
DCH.CNC_CD_USER as CON_CodeVendeur
FROM adhimp.T_DECLARATION_CHALLENGE_DCH DCH
JOIN (
SELECT INC.INC_ID_ADHESION, PRD.PRD_CD_PRODUIT, CNT.CNT_CD_CENTRE, CON.CON_NUMERO
FROM dbo.T_INFOS_CONTRAT_INC INC
JOIN dbo.T_CONTRAT_CON CON
ON CON.INC_ID_CONTRAT = INC.INC_ID_CONTRAT
AND CON.CON_NB_LIGNE = 1
JOIN dbo.T_PRODUIT_PRD PRD
ON CON.PRD_ID_PRODUIT = PRD.PRD_ID_PRODUIT
JOIN TJ_CONTRAT_INDIVIDUS_CNI CNI
ON CNI.CON_ID_CONTRAT = CON.CON_ID_CONTRAT
JOIN TR_ROLE_INDIVIDU_RLI RLI
ON CNI.RLI_ID_ROLE_INDIVIDU = RLI.RLI_ID_ROLE_INDIVIDU
AND RLI.RLI_ID_SOURCE = 7
JOIN dbo.T_INDIVIDU_IND IND
ON CNI.IND_ID_INDIVIDU = IND.IND_ID_INDIVIDU
JOIN dbo.T_CENTRE_CNT CNT
ON IND.CNT_ID_CENTRE = CNT.CNT_ID_CENTRE
) as ODS
ON DCH.CON_NUMERO = ODS.CON_NUMERO
AND DCH.PRD_CD_PRODUIT = ODS.PRD_CD_PRODUIT
AND DCH.CNT_CD_CENTRE = ODS.CNT_CD_CENTRE
WHERE DCH.TCT_CD_TYPE_CONTACT = 'V' |
Partager