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
| SELECT
sc.CONTACT_ID,
sc.SOCIETE_ID,
sc.PERSON_ID,
nvl(pers.NOM_COMPLET, pers.NOM) NOM_COMPLET,
sc.B_PRINCIPAL,
sc.FONCTION_ID,
sc.FONCTION,
sc.LANG_ID, sc.NATIONALITE_ID,
sc.CONTACT_GROUPE_INIT_ID,
sa.ACTION_ID DERNIER_CONTACT_ID,
sa.DATE_SITUATION DERNIER_CONTACT,
sc.DATE_FIN,
sc.ACTIVITE,
sc.NOTES,
adr.ADRESSE_ID,
adr.ADR0,
adr.ADR1,
adr.CODEPOSTAL,
adr.VILLE,
adr.PAYS_ID,
tm1.NUM_TEL_MAIL NUM_TEL,
tm2.NUM_TEL_MAIL NUM_FAX,
tm3.NUM_TEL_MAIL NUM_MAIL
FROM
SOUSCRIPTEURS_CONTACTS sc,
ADRESSES adr,
TEL_MAIL tm1,
TEL_MAIL tm2,
TEL_MAIL tm3,
PERSONNES pers,
SOUSCRIPTEURS_ACTIONS sa
WHERE
sc.SOCIETE_ID = 30248202
and pers.PERSON_ID = sc.PERSON_ID
and sa.SOUSCRIPT_ID = 30248202
and sa.PERSON_ID = sc.PERSON_ID
and sa.DATE_SITUATION =
(SELECT max(sam.DATE_SITUATION)
FROM SOUSCRIPTEURS_ACTIONS sam
WHERE sam.SOUSCRIPT_ID = 30248202
and sam.PERSON_ID = sa.PERSON_ID
and sam.DATE_SITUATION <= trunc(SYSDATE)
)
and adr.OWNER_ID(+) = sc.PERSON_ID
and adr.PARDEFAUT(+) = 'Y'
and tm1.OWNER_ID(+) = sc.PERSON_ID
and tm1.CATEG_TEL(+) = 'T'
and tm1.PARDEFAUT(+) = 'Y'
and tm2.OWNER_ID(+) = sc.PERSON_ID
and tm2.CATEG_TEL(+) = 'F'
and tm2.PARDEFAUT(+) = 'Y'
and tm3.OWNER_ID(+) = sc.PERSON_ID
and tm3.CATEG_TEL(+) = 'E'
and tm3.PARDEFAUT(+) = 'Y'
ORDER BY
sc.B_PRINCIPAL desc,
NOM_COMPLET |
Partager