Product: | AMS - Marketing |
---|---|
Description: | This public view returns organization contacts which are not to be mailed. Used for list generation. |
Implementation/DBA Data: | Not implemented in this database |
SELECT 'Y' DO_NOT_MAIL
, C.CONTACT_RESTRICTION_ID
, C.CONTACT_TYPE
, C.RESTRICT_START_DATE
, C.RESTRICT_END_DATE
, C.RESTRICT_REASON
, C.CREATED_BY
, C.CREATION_DATE
, C.LAST_UPDATE_LOGIN
, C.LAST_UPDATE_DATE
, C.LAST_UPDATED_BY
, C.REQUEST_ID
, C.PROGRAM_APPLICATION_ID
, C.PROGRAM_ID
, C.PROGRAM_UPDATE_DATE
, C.WH_UPDATE_DATE
, C.SUBJECT_TABLE
, C.SUBJECT_ID
, C.SUBJECT_ID PARTY_ID
FROM HZ_CONTACT_RESTRICTIONS C
WHERE C.CONTACT_TYPE = 'MAIL'
AND SYSDATE BETWEEN NVL(C.RESTRICT_START_DATE
, SYSDATE)
AND NVL(C.RESTRICT_END_DATE
, SYSDATE)
AND C.SUBJECT_TABLE = 'HZ_PARTIES'
AND C.CONTACT_RESTRICTION_ID = (SELECT MAX(C1.CONTACT_RESTRICTION_ID)
FROM HZ_CONTACT_RESTRICTIONS C1
WHERE C.CONTACT_TYPE = 'MAIL'
AND SYSDATE BETWEEN NVL(C.RESTRICT_START_DATE
, SYSDATE)
AND NVL(C.RESTRICT_END_DATE
, SYSDATE)
AND C.SUBJECT_TABLE = 'HZ_PARTIES'
AND C1.SUBJECT_ID = C.SUBJECT_ID)