FND Design Data [Home] [Help]

View: AST_EVENT_ENROLLEES_V

Product: AST - TeleSales
Description:
Implementation/DBA Data: ViewAPPS.AST_EVENT_ENROLLEES_V
View Text

SELECT AER.EVENT_REGISTRATION_ID
, AER.EVENT_OFFER_ID
, HP1.PARTY_ID CUSTOMER_ID
, HP1.PARTY_TYPE PARTY_TYPE
, HP1.PARTY_NAME CUSTOMER_NAME
, HP2.PARTY_NAME MAIN_CONTACT_FULL_NAME
, AER.ATTENDANT_ACCOUNT_ID
, HP4.PARTY_NAME ENROLLEE_FULL_NAME
, AL1.MEANING SYSTEM_STATUS_CODE
, AER.CONFIRMATION_CODE
, HP4.PARTY_NUMBER ENROLLEE_NUMBER
, HCP.PHONE_COUNTRY_CODE || DECODE(HCP.PHONE_AREA_CODE
, NULL
, NULL
, HCP.PHONE_AREA_CODE
, '('||HCP.PHONE_AREA_CODE||')')||HCP.PHONE_NUMBER||DECODE(HCP.PHONE_EXTENSION
, NULL
, NULL
, HCP.PHONE_EXTENSION
, ' EXT '||HCP.PHONE_EXTENSION) ENROLLEE_PHONE_NUMBER
, AL2.MEANING REG_SOURCE_TYPE_CODE
, HP1.PERSON_TITLE TITLE
, HP4.EMAIL_ADDRESS EMAIL_ADDRESS
, AER.ATTENDED_FLAG
FROM AMS_EVENT_REGISTRATIONS AER
, HZ_PARTIES HP1
, HZ_PARTIES HP2
, HZ_PARTIES HP4
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS AL1
, AMS_LOOKUPS AL2
WHERE AER.REGISTRANT_PARTY_ID = AER.REGISTRANT_CONTACT_ID
AND AER.ATTENDANT_PARTY_ID = AER.ATTENDANT_CONTACT_ID
AND AER.SYSTEM_STATUS_CODE IN ('REGISTERED'
, 'WAITLISTED'
, 'CANCELLED')
AND AER.REGISTRANT_PARTY_ID = HP1.PARTY_ID
AND HP1.PARTY_TYPE = 'PERSON'
AND AER.REGISTRANT_CONTACT_ID = HP2.PARTY_ID
AND HP4.PARTY_ID = AER.ATTENDANT_PARTY_ID
AND HP4.PARTY_ID = HCP.OWNER_TABLE_ID(+)
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.STATUS(+) = 'A'
AND HCP.PRIMARY_FLAG(+) = 'Y'
AND AER.SYSTEM_STATUS_CODE = AL1.LOOKUP_CODE(+)
AND AL1.ENABLED_FLAG(+) = 'Y'
AND AL1.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_STATUS'
AND AL2.LOOKUP_CODE(+) = AER.REG_SOURCE_TYPE_CODE
AND AL2.ENABLED_FLAG(+) = 'Y'
AND AL2.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_SOURCE' UNION ALL SELECT AER.EVENT_REGISTRATION_ID
, AER.EVENT_OFFER_ID
, HP1.PARTY_ID CUSTOMER_ID
, HP1.PARTY_TYPE PARTY_TYPE
, HP1.PARTY_NAME CUSTOMER_NAME
, HP2.PARTY_NAME||' - '||HP1.PARTY_NAME MAIN_CONTACT_FULL_NAME
, AER.ATTENDANT_ACCOUNT_ID
, HP4.PARTY_NAME ENROLLEE_FULL_NAME
, AL1.MEANING SYSTEM_STATUS_CODE
, AER.CONFIRMATION_CODE
, HP4.PARTY_NUMBER ENROLLEE_NUMBER
, HCP.PHONE_COUNTRY_CODE||DECODE(HCP.PHONE_AREA_CODE
, NULL
, NULL
, HCP.PHONE_AREA_CODE
, '('||HCP.PHONE_AREA_CODE||')')||HCP.PHONE_NUMBER||DECODE(HCP.PHONE_EXTENSION
, NULL
, NULL
, HCP.PHONE_EXTENSION
, ' EXT '||HCP.PHONE_EXTENSION) ENROLLEE_PHONE_NUMBER
, AL2.MEANING REG_SOURCE_TYPE_CODE
, HOC.JOB_TITLE TITLE
, HP4.EMAIL_ADDRESS EMAIL_ADDRESS
, AER.ATTENDED_FLAG
FROM AMS_EVENT_REGISTRATIONS AER
, HZ_ORG_CONTACTS HOC
, HZ_PARTIES HP1
, HZ_PARTIES HP2
, HZ_PARTIES HP4
, HZ_RELATIONSHIPS HPR1
, HZ_RELATIONSHIPS HPR2
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS AL1
, AMS_LOOKUPS AL2
WHERE AER.REGISTRANT_PARTY_ID != AER.REGISTRANT_CONTACT_ID
AND AER.ATTENDANT_PARTY_ID != AER.ATTENDANT_CONTACT_ID
AND AER.SYSTEM_STATUS_CODE IN ('REGISTERED'
, 'WAITLISTED'
, 'CANCELLED')
AND AER.REGISTRANT_PARTY_ID = HP1.PARTY_ID
AND AER.REGISTRANT_CONTACT_ID = HPR1.PARTY_ID
AND HPR1.OBJECT_TYPE = 'ORGANIZATION'
AND HPR1.SUBJECT_ID = HP2.PARTY_ID
AND AER.ATTENDANT_CONTACT_ID = HPR2.PARTY_ID
AND HPR2.OBJECT_TYPE = 'ORGANIZATION'
AND HPR2.SUBJECT_ID = HP4.PARTY_ID
AND HPR2.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID(+)
AND AER.ATTENDANT_CONTACT_ID = HCP.OWNER_TABLE_ID(+)
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.STATUS(+) = 'A'
AND HCP.PRIMARY_FLAG(+) = 'Y'
AND AER.SYSTEM_STATUS_CODE = AL1.LOOKUP_CODE(+)
AND AL1.ENABLED_FLAG(+) = 'Y'
AND AL1.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_STATUS'
AND AER.REG_SOURCE_TYPE_CODE = AL2.LOOKUP_CODE(+)
AND AL2.ENABLED_FLAG(+) = 'Y'
AND AL2.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_SOURCE' UNION ALL SELECT AER.EVENT_REGISTRATION_ID
, AER.EVENT_OFFER_ID
, HP1.PARTY_ID CUSTOMER_ID
, HP1.PARTY_TYPE PARTY_TYPE
, HP1.PARTY_NAME CUSTOMER_NAME
, HP2.PARTY_NAME||' - '||HP1.PARTY_NAME MAIN_CONTACT_FULL_NAME
, AER.ATTENDANT_ACCOUNT_ID
, HP4.PARTY_NAME ENROLLEE_FULL_NAME
, AL1.MEANING SYSTEM_STATUS_CODE
, AER.CONFIRMATION_CODE
, HP4.PARTY_NUMBER ENROLLEE_NUMBER
, HCP.PHONE_COUNTRY_CODE||DECODE(HCP.PHONE_AREA_CODE
, NULL
, NULL
, HCP.PHONE_AREA_CODE
, '('||HCP.PHONE_AREA_CODE||')')||HCP.PHONE_NUMBER||DECODE(HCP.PHONE_EXTENSION
, NULL
, NULL
, HCP.PHONE_EXTENSION
, ' EXT '||HCP.PHONE_EXTENSION) ENROLLEE_PHONE_NUMBER
, AL2.MEANING REG_SOURCE_TYPE_CODE
, HOC.JOB_TITLE TITLE
, HP4.EMAIL_ADDRESS EMAIL_ADDRESS
, AER.ATTENDED_FLAG
FROM AMS_EVENT_REGISTRATIONS AER
, HZ_ORG_CONTACTS HOC
, HZ_PARTIES HP1
, HZ_PARTIES HP2
, HZ_PARTIES HP4
, HZ_RELATIONSHIPS HPR1
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS AL1
, AMS_LOOKUPS AL2
WHERE AER.REGISTRANT_PARTY_ID != AER.REGISTRANT_CONTACT_ID
AND AER.ATTENDANT_PARTY_ID = AER.ATTENDANT_CONTACT_ID
AND AER.SYSTEM_STATUS_CODE IN ('REGISTERED'
, 'WAITLISTED'
, 'CANCELLED')
AND AER.REGISTRANT_PARTY_ID != AER.REGISTRANT_CONTACT_ID
AND AER.REGISTRANT_PARTY_ID = HP1.PARTY_ID
AND AER.REGISTRANT_CONTACT_ID = HPR1.PARTY_ID
AND HPR1.SUBJECT_ID = HP2.PARTY_ID
AND HPR1.OBJECT_TYPE = 'ORGANIZATION'
AND AER.ATTENDANT_CONTACT_ID = HP4.PARTY_ID
AND AER.ATTENDANT_CONTACT_ID = HOC.PARTY_RELATIONSHIP_ID(+)
AND AER.ATTENDANT_CONTACT_ID = HCP.OWNER_TABLE_ID(+)
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.STATUS(+) = 'A'
AND HCP.PRIMARY_FLAG(+) = 'Y'
AND AER.SYSTEM_STATUS_CODE = AL1.LOOKUP_CODE(+)
AND AL1.ENABLED_FLAG(+) = 'Y'
AND AL1.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_STATUS'
AND AER.REG_SOURCE_TYPE_CODE = AL2.LOOKUP_CODE(+)
AND AL2.ENABLED_FLAG(+) = 'Y'
AND AL2.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_SOURCE' UNION ALL SELECT AER.EVENT_REGISTRATION_ID
, AER.EVENT_OFFER_ID
, HP1.PARTY_ID CUSTOMER_ID
, HP1.PARTY_TYPE PARTY_TYPE
, HP1.PARTY_NAME CUSTOMER_NAME
, HP2.PARTY_NAME||' - '||HP1.PARTY_NAME MAIN_CONTACT_FULL_NAME
, AER.ATTENDANT_ACCOUNT_ID
, HP4.PARTY_NAME ENROLLEE_FULL_NAME
, AL1.MEANING SYSTEM_STATUS_CODE
, AER.CONFIRMATION_CODE
, HP4.PARTY_NUMBER ENROLLEE_NUMBER
, HCP.PHONE_COUNTRY_CODE||DECODE(HCP.PHONE_AREA_CODE
, NULL
, NULL
, HCP.PHONE_AREA_CODE
, '('||HCP.PHONE_AREA_CODE||')')||HCP.PHONE_NUMBER||DECODE(HCP.PHONE_EXTENSION
, NULL
, NULL
, HCP.PHONE_EXTENSION
, ' EXT '||HCP.PHONE_EXTENSION) ENROLLEE_PHONE_NUMBER
, AL2.MEANING REG_SOURCE_TYPE_CODE
, HOC.JOB_TITLE TITLE
, HP4.EMAIL_ADDRESS EMAIL_ADDRESS
, AER.ATTENDED_FLAG
FROM AMS_EVENT_REGISTRATIONS AER
, HZ_ORG_CONTACTS HOC
, HZ_PARTIES HP1
, HZ_PARTIES HP2
, HZ_PARTIES HP4
, HZ_RELATIONSHIPS HPR2
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS AL1
, AMS_LOOKUPS AL2
WHERE AER.REGISTRANT_PARTY_ID = AER.REGISTRANT_CONTACT_ID
AND AER.ATTENDANT_PARTY_ID != AER.ATTENDANT_CONTACT_ID
AND AER.SYSTEM_STATUS_CODE IN ('REGISTERED'
, 'WAITLISTED'
, 'CANCELLED')
AND AER.REGISTRANT_PARTY_ID = HP1.PARTY_ID
AND AER.REGISTRANT_CONTACT_ID = HP2.PARTY_ID
AND AER.ATTENDANT_CONTACT_ID = HPR2.PARTY_ID
AND HPR2.SUBJECT_ID = HP4.PARTY_ID
AND HPR2.OBJECT_TYPE = 'ORGANIZATION'
AND HPR2.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID(+)
AND AER.ATTENDANT_CONTACT_ID = HCP.OWNER_TABLE_ID(+)
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.STATUS(+) = 'A'
AND HCP.PRIMARY_FLAG(+) = 'Y'
AND AER.SYSTEM_STATUS_CODE = AL1.LOOKUP_CODE(+)
AND AL1.ENABLED_FLAG(+) = 'Y'
AND AL1.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_STATUS'
AND AER.REG_SOURCE_TYPE_CODE = AL2.LOOKUP_CODE(+)
AND AL2.ENABLED_FLAG(+) = 'Y'
AND AL2.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_SOURCE'

Columns

Name
EVENT_REGISTRATION_ID
EVENT_OFFER_ID
CUSTOMER_ID
PARTY_TYPE
CUSTOMER_NAME
MAIN_CONTACT_FULL_NAME
ATTENDANT_ACCOUNT_ID
ENROLLEE_FULL_NAME
SYSTEM_STATUS_CODE
CONFIRMATION_CODE
ENROLLEE_NUMBER
ENROLLEE_PHONE_NUMBER
REG_SOURCE_TYPE_CODE
TITLE
EMAIL_ADDRESS
ATTENDED_FLAG