VIEW: APPS.AST_EVENT_HISTORY_V

Source

View Text - Preformatted

SELECT aer.event_registration_id , aer.event_offer_id , aeov.event_offer_name , aer.order_header_id , aer.date_registration_placed , aer.registrant_party_id , hp1.party_name registrant_party , aer.registrant_contact_id , hp2.party_name registrant_contact , aer.attendant_party_id , hp3.party_name attendant_party , aer.attendant_contact_id , hp4.party_id attendant_contact_person_id , hp4.party_name attendant_contact , 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) attendant_contact_phone , aer.confirmation_code , aer.system_status_code , al1.meaning system_status , aer.object_version_number , aeov.inventory_item_id , ooh.transactional_curr_code , al2.meaning payment_status , ooh.payment_type_code , ooh.payment_amount , qlht.name price_list_name , aer.cancellation_reason_code , al3.meaning cancellation_reason , ppf.full_name event_coordinator , ppf.work_telephone coordinator_phone from ams_event_registrations aer , ams_event_offers_vl aeov , hz_parties hp1 , hz_parties hp2 , hz_parties hp3 , hz_parties hp4 , hz_contact_points hcp , ams_lookups al1 , ams_lookups al2 , ams_lookups al3 , oe_order_headers ooh , qp_list_headers_tl qlht , per_people_f ppf , jtf_rs_resource_extns jrre WHERE aer.registrant_party_id = aer.registrant_contact_id and aer.attendant_party_id = aer.attendant_contact_id and aer.event_offer_id = aeov.event_offer_id and aer.registrant_party_id = hp1.party_id and aer.registrant_party_id = aer.registrant_contact_id and aer.attendant_party_id = hp3.party_id and aer.attendant_contact_id = hp4.party_id and aer.registrant_contact_id = hp2.party_id and hp1.party_type = 'PERSON' and hcp.owner_table_name(+) = 'HZ_PARTIES' and hcp.owner_table_id(+) = hp4.party_id 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.lookup_type(+) = 'AMS_EVENT_REG_STATUS' and al1.enabled_flag(+) = 'Y' and aer.payment_status_code = al2.lookup_code(+) and al2.lookup_type(+) = 'AMS_EVENT_PAYMENT_STATUS' and al2.enabled_flag(+) = 'Y' and aer.cancellation_reason_code = al3.lookup_code(+) and al3.lookup_type(+) = 'AMS_EVENT_CANCEL_REASON' and al3.enabled_flag(+) = 'Y' and ooh.header_id (+) = aer.order_header_id and qlht.list_header_id(+) = ooh.price_list_id and aeov.owner_user_id = jrre.resource_id and jrre.source_id = ppf.person_id(+) and trunc(sysdate) between ppf.effective_start_date(+) and ppf.effective_end_date(+) and ppf.employee_number(+) is not null union all select aer.event_registration_id , aer.event_offer_id , aeov.event_offer_name , aer.order_header_id , aer.date_registration_placed , aer.registrant_party_id , hp1.party_name registrant_party , aer.registrant_contact_id , hp2.party_name||' - '||hp1.party_name registrant_contact , aer.attendant_party_id , hp3.party_name attendant_party , aer.attendant_contact_id , hp4.party_id attendant_contact_person_id , hp4.party_name attendant_contact , 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) attendant_contact_phone , aer.confirmation_code , aer.system_status_code , al1.meaning system_status , aer.object_version_number , aeov.inventory_item_id , ooh.transactional_curr_code , al2.meaning payment_status , ooh.payment_type_code , ooh.payment_amount , qlht.name price_list_name , aer.cancellation_reason_code , al3.meaning cancellation_reason , ppf.full_name event_coordinator , ppf.work_telephone coordinator_phone from ams_event_registrations aer , ams_event_offers_vl aeov , hz_parties hp1 , hz_parties hp2 , hz_parties hp3 , hz_parties hp4 , hz_relationships hpr1 , hz_relationships hpr2 , hz_contact_points hcp , ams_lookups al1 , ams_lookups al2 , ams_lookups al3 , oe_order_headers ooh , qp_list_headers_tl qlht , per_people_f ppf , jtf_rs_resource_extns jrre where aer.registrant_party_id != aer.registrant_contact_id and aer.attendant_party_id != aer.attendant_contact_id and aer.event_offer_id = aeov.event_offer_id and aer.registrant_party_id = hp1.party_id and aer.registrant_party_id = hpr1.object_id and aer.registrant_contact_id = hpr1.party_id and hpr1.subject_id = hp2.party_id and aer.attendant_party_id = hp3.party_id and aer.attendant_party_id = hpr2.object_id and aer.attendant_contact_id = hpr2.party_id and hpr2.subject_id = hp4.party_id and aer.attendant_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.lookup_type(+) = 'AMS_EVENT_REG_STATUS' and al1.enabled_flag(+) = 'Y' and aer.payment_status_code = al2.lookup_code (+) and al2.lookup_type (+) = 'AMS_EVENT_PAYMENT_STATUS' and al2.enabled_flag(+) = 'Y' and aer.cancellation_reason_code = al3.lookup_code(+) and al3.lookup_type(+) = 'AMS_EVENT_CANCEL_REASON' and al3.enabled_flag(+) = 'Y' and ooh.header_id (+) = aer.order_header_id and qlht.list_header_id (+) = ooh.price_list_id and aeov.owner_user_id = jrre.resource_id and jrre.source_id = ppf.person_id(+) and trunc(sysdate) between ppf.effective_start_date(+) and ppf.effective_end_date(+) and ppf.employee_number(+) is not null union all select aer.event_registration_id , aer.event_offer_id , aeov.event_offer_name , aer.order_header_id , aer.date_registration_placed , aer.registrant_party_id , hp1.party_name registrant_party , aer.registrant_contact_id , hp2.party_name||' - '||hp1.party_name registrant_contact , aer.attendant_party_id , hp3.party_name attendant_party , aer.attendant_contact_id , hp4.party_id attendant_contact_person_id , hp4.party_name attendant_contact , 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) attendant_contact_phone , aer.confirmation_code , aer.system_status_code , al1.meaning system_status , aer.object_version_number , aeov.inventory_item_id , ooh.transactional_curr_code , al2.meaning payment_status , ooh.payment_type_code , ooh.payment_amount , qlht.name price_list_name , aer.cancellation_reason_code , al3.meaning cancellation_reason , ppf.full_name event_coordinator , ppf.work_telephone coordinator_phone from ams_event_registrations aer , ams_event_offers_vl aeov , hz_parties hp1 , hz_parties hp2 , hz_parties hp3 , hz_parties hp4 , hz_relationships hpr1 , hz_contact_points hcp , ams_lookups al1 , ams_lookups al2 , ams_lookups al3 , oe_order_headers ooh , qp_list_headers_tl qlht , per_people_f ppf , jtf_rs_resource_extns jrre where aer.registrant_party_id != aer.registrant_contact_id and aer.attendant_party_id = aer.attendant_contact_id and aer.event_offer_id = aeov.event_offer_id and aer.registrant_party_id = hp1.party_id and aer.registrant_party_id = hpr1.object_id and aer.registrant_contact_id = hpr1.party_id and hpr1.subject_id = hp2.party_id and aer.attendant_party_id = hp3.party_id and aer.attendant_contact_id = hp4.party_id and aer.attendant_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.lookup_type(+) = 'AMS_EVENT_REG_STATUS' and al1.enabled_flag(+) = 'Y' and aer.payment_status_code = al2.lookup_code (+) and al2.lookup_type (+) = 'AMS_EVENT_PAYMENT_STATUS' and al2.enabled_flag(+) = 'Y' and aer.cancellation_reason_code = al3.lookup_code(+) and al3.lookup_type(+) = 'AMS_EVENT_CANCEL_REASON' and al3.enabled_flag(+) = 'Y' and ooh.header_id (+) = aer.order_header_id and qlht.list_header_id (+) = ooh.price_list_id and aeov.owner_user_id = jrre.resource_id and jrre.source_id = ppf.person_id(+) and trunc(sysdate) between ppf.effective_start_date(+) and ppf.effective_end_date(+) and ppf.employee_number(+) is not null union all select aer.event_registration_id , aer.event_offer_id , aeov.event_offer_name , aer.order_header_id , aer.date_registration_placed , aer.registrant_party_id , hp1.party_name registrant_party , aer.registrant_contact_id , hp2.party_name||' - '||hp1.party_name registrant_contact , aer.attendant_party_id , hp3.party_name attendant_party , aer.attendant_contact_id , hp4.party_id attendant_contact_person_id , hp4.party_name attendant_contact , 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) attendant_contact_phone , aer.confirmation_code , aer.system_status_code , al1.meaning system_status , aer.object_version_number , aeov.inventory_item_id , ooh.transactional_curr_code , al2.meaning payment_status , ooh.payment_type_code , ooh.payment_amount , qlht.name price_list_name , aer.cancellation_reason_code , al3.meaning cancellation_reason , ppf.full_name event_coordinator , ppf.work_telephone coordinator_phone from ams_event_registrations aer , ams_event_offers_vl aeov , hz_parties hp1 , hz_parties hp2 , hz_parties hp3 , hz_parties hp4 , hz_relationships hpr2 , hz_contact_points hcp , ams_lookups al1 , ams_lookups al2 , ams_lookups al3 , oe_order_headers ooh , qp_list_headers_tl qlht , per_people_f ppf , jtf_rs_resource_extns jrre where aer.registrant_party_id = aer.registrant_contact_id and aer.attendant_party_id != aer.attendant_contact_id and aer.event_offer_id = aeov.event_offer_id and aer.registrant_party_id = hp1.party_id and aer.registrant_contact_id = hp2.party_id and aer.attendant_party_id = hp3.party_id and aer.attendant_party_id = hpr2.object_id and aer.attendant_contact_id = hpr2.party_id and hpr2.subject_id = hp4.party_id and aer.attendant_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.lookup_type(+) = 'AMS_EVENT_REG_STATUS' and al1.enabled_flag(+) = 'Y' and aer.payment_status_code = al2.lookup_code (+) and al2.lookup_type (+) = 'AMS_EVENT_PAYMENT_STATUS' and al2.enabled_flag(+) = 'Y' and aer.cancellation_reason_code = al3.lookup_code(+) and al3.lookup_type(+) = 'AMS_EVENT_CANCEL_REASON' and al3.enabled_flag(+) = 'Y' and ooh.header_id (+) = aer.order_header_id and qlht.list_header_id (+) = ooh.price_list_id and aeov.owner_user_id = jrre.resource_id and jrre.source_id = ppf.person_id(+) and trunc(sysdate) between ppf.effective_start_date(+) and ppf.effective_end_date(+) and ppf.employee_number(+) is not null

View Text - HTML Formatted

SELECT AER.EVENT_REGISTRATION_ID
, AER.EVENT_OFFER_ID
, AEOV.EVENT_OFFER_NAME
, AER.ORDER_HEADER_ID
, AER.DATE_REGISTRATION_PLACED
, AER.REGISTRANT_PARTY_ID
, HP1.PARTY_NAME REGISTRANT_PARTY
, AER.REGISTRANT_CONTACT_ID
, HP2.PARTY_NAME REGISTRANT_CONTACT
, AER.ATTENDANT_PARTY_ID
, HP3.PARTY_NAME ATTENDANT_PARTY
, AER.ATTENDANT_CONTACT_ID
, HP4.PARTY_ID ATTENDANT_CONTACT_PERSON_ID
, HP4.PARTY_NAME ATTENDANT_CONTACT
, 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) ATTENDANT_CONTACT_PHONE
, AER.CONFIRMATION_CODE
, AER.SYSTEM_STATUS_CODE
, AL1.MEANING SYSTEM_STATUS
, AER.OBJECT_VERSION_NUMBER
, AEOV.INVENTORY_ITEM_ID
, OOH.TRANSACTIONAL_CURR_CODE
, AL2.MEANING PAYMENT_STATUS
, OOH.PAYMENT_TYPE_CODE
, OOH.PAYMENT_AMOUNT
, QLHT.NAME PRICE_LIST_NAME
, AER.CANCELLATION_REASON_CODE
, AL3.MEANING CANCELLATION_REASON
, PPF.FULL_NAME EVENT_COORDINATOR
, PPF.WORK_TELEPHONE COORDINATOR_PHONE
FROM AMS_EVENT_REGISTRATIONS AER
, AMS_EVENT_OFFERS_VL AEOV
, HZ_PARTIES HP1
, HZ_PARTIES HP2
, HZ_PARTIES HP3
, HZ_PARTIES HP4
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS AL1
, AMS_LOOKUPS AL2
, AMS_LOOKUPS AL3
, OE_ORDER_HEADERS OOH
, QP_LIST_HEADERS_TL QLHT
, PER_PEOPLE_F PPF
, JTF_RS_RESOURCE_EXTNS JRRE
WHERE AER.REGISTRANT_PARTY_ID = AER.REGISTRANT_CONTACT_ID
AND AER.ATTENDANT_PARTY_ID = AER.ATTENDANT_CONTACT_ID
AND AER.EVENT_OFFER_ID = AEOV.EVENT_OFFER_ID
AND AER.REGISTRANT_PARTY_ID = HP1.PARTY_ID
AND AER.REGISTRANT_PARTY_ID = AER.REGISTRANT_CONTACT_ID
AND AER.ATTENDANT_PARTY_ID = HP3.PARTY_ID
AND AER.ATTENDANT_CONTACT_ID = HP4.PARTY_ID
AND AER.REGISTRANT_CONTACT_ID = HP2.PARTY_ID
AND HP1.PARTY_TYPE = 'PERSON'
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.OWNER_TABLE_ID(+) = HP4.PARTY_ID
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.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_STATUS'
AND AL1.ENABLED_FLAG(+) = 'Y'
AND AER.PAYMENT_STATUS_CODE = AL2.LOOKUP_CODE(+)
AND AL2.LOOKUP_TYPE(+) = 'AMS_EVENT_PAYMENT_STATUS'
AND AL2.ENABLED_FLAG(+) = 'Y'
AND AER.CANCELLATION_REASON_CODE = AL3.LOOKUP_CODE(+)
AND AL3.LOOKUP_TYPE(+) = 'AMS_EVENT_CANCEL_REASON'
AND AL3.ENABLED_FLAG(+) = 'Y'
AND OOH.HEADER_ID (+) = AER.ORDER_HEADER_ID
AND QLHT.LIST_HEADER_ID(+) = OOH.PRICE_LIST_ID
AND AEOV.OWNER_USER_ID = JRRE.RESOURCE_ID
AND JRRE.SOURCE_ID = PPF.PERSON_ID(+)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE(+)
AND PPF.EFFECTIVE_END_DATE(+)
AND PPF.EMPLOYEE_NUMBER(+) IS NOT NULL UNION ALL SELECT AER.EVENT_REGISTRATION_ID
, AER.EVENT_OFFER_ID
, AEOV.EVENT_OFFER_NAME
, AER.ORDER_HEADER_ID
, AER.DATE_REGISTRATION_PLACED
, AER.REGISTRANT_PARTY_ID
, HP1.PARTY_NAME REGISTRANT_PARTY
, AER.REGISTRANT_CONTACT_ID
, HP2.PARTY_NAME||' - '||HP1.PARTY_NAME REGISTRANT_CONTACT
, AER.ATTENDANT_PARTY_ID
, HP3.PARTY_NAME ATTENDANT_PARTY
, AER.ATTENDANT_CONTACT_ID
, HP4.PARTY_ID ATTENDANT_CONTACT_PERSON_ID
, HP4.PARTY_NAME ATTENDANT_CONTACT
, 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) ATTENDANT_CONTACT_PHONE
, AER.CONFIRMATION_CODE
, AER.SYSTEM_STATUS_CODE
, AL1.MEANING SYSTEM_STATUS
, AER.OBJECT_VERSION_NUMBER
, AEOV.INVENTORY_ITEM_ID
, OOH.TRANSACTIONAL_CURR_CODE
, AL2.MEANING PAYMENT_STATUS
, OOH.PAYMENT_TYPE_CODE
, OOH.PAYMENT_AMOUNT
, QLHT.NAME PRICE_LIST_NAME
, AER.CANCELLATION_REASON_CODE
, AL3.MEANING CANCELLATION_REASON
, PPF.FULL_NAME EVENT_COORDINATOR
, PPF.WORK_TELEPHONE COORDINATOR_PHONE
FROM AMS_EVENT_REGISTRATIONS AER
, AMS_EVENT_OFFERS_VL AEOV
, HZ_PARTIES HP1
, HZ_PARTIES HP2
, HZ_PARTIES HP3
, HZ_PARTIES HP4
, HZ_RELATIONSHIPS HPR1
, HZ_RELATIONSHIPS HPR2
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS AL1
, AMS_LOOKUPS AL2
, AMS_LOOKUPS AL3
, OE_ORDER_HEADERS OOH
, QP_LIST_HEADERS_TL QLHT
, PER_PEOPLE_F PPF
, JTF_RS_RESOURCE_EXTNS JRRE
WHERE AER.REGISTRANT_PARTY_ID != AER.REGISTRANT_CONTACT_ID
AND AER.ATTENDANT_PARTY_ID != AER.ATTENDANT_CONTACT_ID
AND AER.EVENT_OFFER_ID = AEOV.EVENT_OFFER_ID
AND AER.REGISTRANT_PARTY_ID = HP1.PARTY_ID
AND AER.REGISTRANT_PARTY_ID = HPR1.OBJECT_ID
AND AER.REGISTRANT_CONTACT_ID = HPR1.PARTY_ID
AND HPR1.SUBJECT_ID = HP2.PARTY_ID
AND AER.ATTENDANT_PARTY_ID = HP3.PARTY_ID
AND AER.ATTENDANT_PARTY_ID = HPR2.OBJECT_ID
AND AER.ATTENDANT_CONTACT_ID = HPR2.PARTY_ID
AND HPR2.SUBJECT_ID = HP4.PARTY_ID
AND AER.ATTENDANT_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.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_STATUS'
AND AL1.ENABLED_FLAG(+) = 'Y'
AND AER.PAYMENT_STATUS_CODE = AL2.LOOKUP_CODE (+)
AND AL2.LOOKUP_TYPE (+) = 'AMS_EVENT_PAYMENT_STATUS'
AND AL2.ENABLED_FLAG(+) = 'Y'
AND AER.CANCELLATION_REASON_CODE = AL3.LOOKUP_CODE(+)
AND AL3.LOOKUP_TYPE(+) = 'AMS_EVENT_CANCEL_REASON'
AND AL3.ENABLED_FLAG(+) = 'Y'
AND OOH.HEADER_ID (+) = AER.ORDER_HEADER_ID
AND QLHT.LIST_HEADER_ID (+) = OOH.PRICE_LIST_ID
AND AEOV.OWNER_USER_ID = JRRE.RESOURCE_ID
AND JRRE.SOURCE_ID = PPF.PERSON_ID(+)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE(+)
AND PPF.EFFECTIVE_END_DATE(+)
AND PPF.EMPLOYEE_NUMBER(+) IS NOT NULL UNION ALL SELECT AER.EVENT_REGISTRATION_ID
, AER.EVENT_OFFER_ID
, AEOV.EVENT_OFFER_NAME
, AER.ORDER_HEADER_ID
, AER.DATE_REGISTRATION_PLACED
, AER.REGISTRANT_PARTY_ID
, HP1.PARTY_NAME REGISTRANT_PARTY
, AER.REGISTRANT_CONTACT_ID
, HP2.PARTY_NAME||' - '||HP1.PARTY_NAME REGISTRANT_CONTACT
, AER.ATTENDANT_PARTY_ID
, HP3.PARTY_NAME ATTENDANT_PARTY
, AER.ATTENDANT_CONTACT_ID
, HP4.PARTY_ID ATTENDANT_CONTACT_PERSON_ID
, HP4.PARTY_NAME ATTENDANT_CONTACT
, 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) ATTENDANT_CONTACT_PHONE
, AER.CONFIRMATION_CODE
, AER.SYSTEM_STATUS_CODE
, AL1.MEANING SYSTEM_STATUS
, AER.OBJECT_VERSION_NUMBER
, AEOV.INVENTORY_ITEM_ID
, OOH.TRANSACTIONAL_CURR_CODE
, AL2.MEANING PAYMENT_STATUS
, OOH.PAYMENT_TYPE_CODE
, OOH.PAYMENT_AMOUNT
, QLHT.NAME PRICE_LIST_NAME
, AER.CANCELLATION_REASON_CODE
, AL3.MEANING CANCELLATION_REASON
, PPF.FULL_NAME EVENT_COORDINATOR
, PPF.WORK_TELEPHONE COORDINATOR_PHONE
FROM AMS_EVENT_REGISTRATIONS AER
, AMS_EVENT_OFFERS_VL AEOV
, HZ_PARTIES HP1
, HZ_PARTIES HP2
, HZ_PARTIES HP3
, HZ_PARTIES HP4
, HZ_RELATIONSHIPS HPR1
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS AL1
, AMS_LOOKUPS AL2
, AMS_LOOKUPS AL3
, OE_ORDER_HEADERS OOH
, QP_LIST_HEADERS_TL QLHT
, PER_PEOPLE_F PPF
, JTF_RS_RESOURCE_EXTNS JRRE
WHERE AER.REGISTRANT_PARTY_ID != AER.REGISTRANT_CONTACT_ID
AND AER.ATTENDANT_PARTY_ID = AER.ATTENDANT_CONTACT_ID
AND AER.EVENT_OFFER_ID = AEOV.EVENT_OFFER_ID
AND AER.REGISTRANT_PARTY_ID = HP1.PARTY_ID
AND AER.REGISTRANT_PARTY_ID = HPR1.OBJECT_ID
AND AER.REGISTRANT_CONTACT_ID = HPR1.PARTY_ID
AND HPR1.SUBJECT_ID = HP2.PARTY_ID
AND AER.ATTENDANT_PARTY_ID = HP3.PARTY_ID
AND AER.ATTENDANT_CONTACT_ID = HP4.PARTY_ID
AND AER.ATTENDANT_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.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_STATUS'
AND AL1.ENABLED_FLAG(+) = 'Y'
AND AER.PAYMENT_STATUS_CODE = AL2.LOOKUP_CODE (+)
AND AL2.LOOKUP_TYPE (+) = 'AMS_EVENT_PAYMENT_STATUS'
AND AL2.ENABLED_FLAG(+) = 'Y'
AND AER.CANCELLATION_REASON_CODE = AL3.LOOKUP_CODE(+)
AND AL3.LOOKUP_TYPE(+) = 'AMS_EVENT_CANCEL_REASON'
AND AL3.ENABLED_FLAG(+) = 'Y'
AND OOH.HEADER_ID (+) = AER.ORDER_HEADER_ID
AND QLHT.LIST_HEADER_ID (+) = OOH.PRICE_LIST_ID
AND AEOV.OWNER_USER_ID = JRRE.RESOURCE_ID
AND JRRE.SOURCE_ID = PPF.PERSON_ID(+)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE(+)
AND PPF.EFFECTIVE_END_DATE(+)
AND PPF.EMPLOYEE_NUMBER(+) IS NOT NULL UNION ALL SELECT AER.EVENT_REGISTRATION_ID
, AER.EVENT_OFFER_ID
, AEOV.EVENT_OFFER_NAME
, AER.ORDER_HEADER_ID
, AER.DATE_REGISTRATION_PLACED
, AER.REGISTRANT_PARTY_ID
, HP1.PARTY_NAME REGISTRANT_PARTY
, AER.REGISTRANT_CONTACT_ID
, HP2.PARTY_NAME||' - '||HP1.PARTY_NAME REGISTRANT_CONTACT
, AER.ATTENDANT_PARTY_ID
, HP3.PARTY_NAME ATTENDANT_PARTY
, AER.ATTENDANT_CONTACT_ID
, HP4.PARTY_ID ATTENDANT_CONTACT_PERSON_ID
, HP4.PARTY_NAME ATTENDANT_CONTACT
, 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) ATTENDANT_CONTACT_PHONE
, AER.CONFIRMATION_CODE
, AER.SYSTEM_STATUS_CODE
, AL1.MEANING SYSTEM_STATUS
, AER.OBJECT_VERSION_NUMBER
, AEOV.INVENTORY_ITEM_ID
, OOH.TRANSACTIONAL_CURR_CODE
, AL2.MEANING PAYMENT_STATUS
, OOH.PAYMENT_TYPE_CODE
, OOH.PAYMENT_AMOUNT
, QLHT.NAME PRICE_LIST_NAME
, AER.CANCELLATION_REASON_CODE
, AL3.MEANING CANCELLATION_REASON
, PPF.FULL_NAME EVENT_COORDINATOR
, PPF.WORK_TELEPHONE COORDINATOR_PHONE
FROM AMS_EVENT_REGISTRATIONS AER
, AMS_EVENT_OFFERS_VL AEOV
, HZ_PARTIES HP1
, HZ_PARTIES HP2
, HZ_PARTIES HP3
, HZ_PARTIES HP4
, HZ_RELATIONSHIPS HPR2
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS AL1
, AMS_LOOKUPS AL2
, AMS_LOOKUPS AL3
, OE_ORDER_HEADERS OOH
, QP_LIST_HEADERS_TL QLHT
, PER_PEOPLE_F PPF
, JTF_RS_RESOURCE_EXTNS JRRE
WHERE AER.REGISTRANT_PARTY_ID = AER.REGISTRANT_CONTACT_ID
AND AER.ATTENDANT_PARTY_ID != AER.ATTENDANT_CONTACT_ID
AND AER.EVENT_OFFER_ID = AEOV.EVENT_OFFER_ID
AND AER.REGISTRANT_PARTY_ID = HP1.PARTY_ID
AND AER.REGISTRANT_CONTACT_ID = HP2.PARTY_ID
AND AER.ATTENDANT_PARTY_ID = HP3.PARTY_ID
AND AER.ATTENDANT_PARTY_ID = HPR2.OBJECT_ID
AND AER.ATTENDANT_CONTACT_ID = HPR2.PARTY_ID
AND HPR2.SUBJECT_ID = HP4.PARTY_ID
AND AER.ATTENDANT_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.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_STATUS'
AND AL1.ENABLED_FLAG(+) = 'Y'
AND AER.PAYMENT_STATUS_CODE = AL2.LOOKUP_CODE (+)
AND AL2.LOOKUP_TYPE (+) = 'AMS_EVENT_PAYMENT_STATUS'
AND AL2.ENABLED_FLAG(+) = 'Y'
AND AER.CANCELLATION_REASON_CODE = AL3.LOOKUP_CODE(+)
AND AL3.LOOKUP_TYPE(+) = 'AMS_EVENT_CANCEL_REASON'
AND AL3.ENABLED_FLAG(+) = 'Y'
AND OOH.HEADER_ID (+) = AER.ORDER_HEADER_ID
AND QLHT.LIST_HEADER_ID (+) = OOH.PRICE_LIST_ID
AND AEOV.OWNER_USER_ID = JRRE.RESOURCE_ID
AND JRRE.SOURCE_ID = PPF.PERSON_ID(+)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE(+)
AND PPF.EFFECTIVE_END_DATE(+)
AND PPF.EMPLOYEE_NUMBER(+) IS NOT NULL