VIEW: APPS.AST_EVENT_ENROLLEES_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'