VIEW: APPS.AMS_EVENT_REGISTRATIONS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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