View: AST_EVENT_HISTORY_V

Product:	AST - TeleSales
Description:
Implementation/DBA Data:	APPS.AST_EVENT_HISTORY_V

View Text

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

Columns