Product: | AMS - Marketing |
---|---|
Description: | This view returns the registration details of the participants to a marketing event. |
Implementation/DBA Data: | APPS.AMS_EVENT_REGISTRATIONS_V |
SELECT REG.EVENT_REGISTRATION_ID
, REG.LAST_UPDATE_DATE
, REG.LAST_UPDATED_BY
, REG.CREATION_DATE
, REG.CREATED_BY
, REG.LAST_UPDATE_LOGIN
, REG.OBJECT_VERSION_NUMBER
, REG.APPLICATION_ID
, REG.OWNER_USER_ID
, REG.CONFIRMATION_CODE
, REG.EVENT_OFFER_ID
, REG.ACTIVE_FLAG
, REG.DATE_REGISTRATION_PLACED
, REG.USER_STATUS_ID
, AUS.NAME
, REG.SYSTEM_STATUS_CODE
, REG.LAST_REG_STATUS_DATE
, REG.REG_SOURCE_TYPE_CODE
, REG.REGISTRATION_SOURCE_ID
, REG.SOURCE_CODE
, REG.REGISTRATION_GROUP_ID
, REG.REGISTRANT_PARTY_ID
, HZ.PARTY_NAME CUSTOMER_NAME
, REG.REGISTRANT_ACCOUNT_ID
, REG.REGISTRANT_CONTACT_ID
, HZ.PERSON_FIRST_NAME
, HZ.PERSON_LAST_NAME
, REG.ATTENDANT_PARTY_ID
, REG.ATTENDANT_CONTACT_ID
, REG.ATTENDANT_ACCOUNT_ID
, REG.ORIGINAL_REGISTRANT_CONTACT_ID
, REG.PROSPECT_FLAG
, REG.ATTENDED_FLAG
, REG.CONFIRMED_FLAG
, REG.EVALUATED_FLAG
, REG.ATTENDANCE_RESULT_CODE
, REG.WAITLISTED_PRIORITY
, REG.TARGET_LIST_ID
, REG.INBOUND_MEDIA_ID
, REG.INBOUND_CHANNEL_ID
, REG.CANCELLATION_CODE
, REG.CANCELLATION_REASON_CODE
, REG.ATTENDANCE_FAILURE_REASON
, REG.ATTENDANT_LANGUAGE
, REG.SALESREP_ID
, REG.ORDER_HEADER_ID
, REG.ORDER_LINE_ID
, REG.DESCRIPTION
, REG.MAX_ATTENDEE_OVERRIDE_FLAG
, REG.INVITE_ONLY_OVERRIDE_FLAG
, REG.PAYMENT_STATUS_CODE
, LKUP1.MEANING
, REG.EVENT_JOIN_TIME
, REG.EVENT_EXIT_TIME
, REG.MEETING_ENCRYPTION_KEY_CODE
FROM AMS_EVENT_REGISTRATIONS REG
, AMS_USER_STATUSES_VL AUS
, HZ_PARTIES HZ
, AMS_LOOKUPS LKUP1
WHERE REG.USER_STATUS_ID=AUS.USER_STATUS_ID
AND REG.REGISTRANT_PARTY_ID=HZ.PARTY_ID
AND REG.REGISTRANT_PARTY_ID = REG.REGISTRANT_CONTACT_ID
AND LKUP1.LOOKUP_TYPE(+)='AMS_EVENT_PAYMENT_STATUS'
AND LKUP1.LOOKUP_CODE(+)=REG.PAYMENT_STATUS_CODE UNION ALL SELECT REG.EVENT_REGISTRATION_ID
, REG.LAST_UPDATE_DATE
, REG.LAST_UPDATED_BY
, REG.CREATION_DATE
, REG.CREATED_BY
, REG.LAST_UPDATE_LOGIN
, REG.OBJECT_VERSION_NUMBER
, REG.APPLICATION_ID
, REG.OWNER_USER_ID
, REG.CONFIRMATION_CODE
, REG.EVENT_OFFER_ID
, REG.ACTIVE_FLAG
, REG.DATE_REGISTRATION_PLACED
, REG.USER_STATUS_ID
, AUS.NAME
, REG.SYSTEM_STATUS_CODE
, REG.LAST_REG_STATUS_DATE
, REG.REG_SOURCE_TYPE_CODE
, REG.REGISTRATION_SOURCE_ID
, REG.SOURCE_CODE
, REG.REGISTRATION_GROUP_ID
, REG.REGISTRANT_PARTY_ID
, HZ1.PARTY_NAME CUSTOMER_NAME
, REG.REGISTRANT_ACCOUNT_ID
, REG.REGISTRANT_CONTACT_ID
, HZ.PERSON_FIRST_NAME
, HZ.PERSON_LAST_NAME
, REG.ATTENDANT_PARTY_ID
, REG.ATTENDANT_CONTACT_ID
, REG.ATTENDANT_ACCOUNT_ID
, REG.ORIGINAL_REGISTRANT_CONTACT_ID
, REG.PROSPECT_FLAG
, REG.ATTENDED_FLAG
, REG.CONFIRMED_FLAG
, REG.EVALUATED_FLAG
, REG.ATTENDANCE_RESULT_CODE
, REG.WAITLISTED_PRIORITY
, REG.TARGET_LIST_ID
, REG.INBOUND_MEDIA_ID
, REG.INBOUND_CHANNEL_ID
, REG.CANCELLATION_CODE
, REG.CANCELLATION_REASON_CODE
, REG.ATTENDANCE_FAILURE_REASON
, REG.ATTENDANT_LANGUAGE
, REG.SALESREP_ID
, REG.ORDER_HEADER_ID
, REG.ORDER_LINE_ID
, REG.DESCRIPTION
, REG.MAX_ATTENDEE_OVERRIDE_FLAG
, REG.INVITE_ONLY_OVERRIDE_FLAG
, REG.PAYMENT_STATUS_CODE
, LKUP1.MEANING
, REG.EVENT_JOIN_TIME
, REG.EVENT_EXIT_TIME
, REG.MEETING_ENCRYPTION_KEY_CODE
FROM AMS_EVENT_REGISTRATIONS REG
, AMS_USER_STATUSES_VL AUS
, HZ_PARTIES HZ
, AMS_LOOKUPS LKUP1
, HZ_RELATIONSHIPS HR
, HZ_PARTIES HZ1
WHERE REG.USER_STATUS_ID=AUS.USER_STATUS_ID
AND REG.REGISTRANT_CONTACT_ID = HR.PARTY_ID
AND HR.SUBJECT_ID = HZ.PARTY_ID
AND HR.SUBJECT_TYPE = 'PERSON'
AND HR.OBJECT_TYPE = 'ORGANIZATION'
AND HR.OBJECT_ID = HZ1.PARTY_ID
AND HR.OBJECT_ID = REG.REGISTRANT_PARTY_ID
AND REG.REGISTRANT_PARTY_ID <> REG.REGISTRANT_CONTACT_ID
AND LKUP1.LOOKUP_TYPE(+)='AMS_EVENT_PAYMENT_STATUS'
AND LKUP1.LOOKUP_CODE(+)=REG.PAYMENT_STATUS_CODE