View: AST_EV_HIS_ORG_V

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

View Text

SELECT REG.EVENT_REGISTRATION_ID
, REG.EVENT_OFFER_ID
, EVT.EVENT_OFFER_NAME
, REG.ORDER_HEADER_ID
, REG.DATE_REGISTRATION_PLACED
, REG.REGISTRANT_PARTY_ID CUSTOMER_ID
, CST.PARTY_TYPE PARTY_TYPE
, CST.PARTY_NAME MAIN_CONTACT_FULL_NAME
, CST2.PARTY_ID REL_PARTY_ID
, CST2.PARTY_NAME REL_NAME
, REG.ATTENDANT_CONTACT_ID MAIN_CONTACT_ID
, CST1.PARTY_NAME ENROLLEE_FULL_NAME
, 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
, REG.CONFIRMATION_CODE
, AL.MEANING SYSTEM_STATUS_CODE
, REG.OBJECT_VERSION_NUMBER
, EVT.INVENTORY_ITEM_ID
, OOH.TRANSACTIONAL_CURR_CODE
, LKU.MEANING PAYMENT_STATUS
, NULL MEANING
, OOH.PAYMENT_TYPE_CODE
, OOH.PAYMENT_AMOUNT
, QLHT.NAME PRICE_LIST_NAME
, REG.CANCELLATION_REASON_CODE
FROM AMS_EVENT_REGISTRATIONS REG
, AMS_EVENT_OFFERS_VL EVT
, HZ_PARTIES CST
, HZ_PARTIES CST1
, HZ_PARTIES CST2
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS LKU
, OE_ORDER_HEADERS OOH
, QP_LIST_HEADERS_TL QLHT
, AMS_LOOKUPS AL
WHERE REG.EVENT_OFFER_ID = EVT.EVENT_OFFER_ID
AND REG.REGISTRANT_PARTY_ID = CST.PARTY_ID
AND REG.ATTENDANT_CONTACT_ID = CST1.PARTY_ID
AND REG.REGISTRANT_CONTACT_ID = REG.REGISTRANT_PARTY_ID
AND REG.REGISTRANT_CONTACT_ID = CST2.PARTY_ID
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.OWNER_TABLE_ID(+) = CST1.PARTY_ID
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.STATUS(+) = 'A'
AND HCP.PRIMARY_FLAG(+) = 'Y'
AND LKU.LOOKUP_TYPE(+)='AMS_EVENT_PAYMENT_STATUS'
AND LKU.LOOKUP_CODE(+)=REG.PAYMENT_STATUS_CODE
AND OOH.HEADER_ID (+) = REG.ORDER_HEADER_ID
AND QLHT.LIST_HEADER_ID (+) = OOH.PRICE_LIST_ID
AND REG.SYSTEM_STATUS_CODE = AL.LOOKUP_CODE(+)
AND AL.ENABLED_FLAG(+) = 'Y'
AND AL.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_STATUS' UNION ALL ( SELECT REG.EVENT_REGISTRATION_ID
, REG.EVENT_OFFER_ID
, EVT.EVENT_OFFER_NAME
, REG.ORDER_HEADER_ID
, REG.DATE_REGISTRATION_PLACED
, REG.REGISTRANT_PARTY_ID CUSTOMER_ID
, CST2.PARTY_TYPE PARTY_TYPE
, CST.PARTY_NAME MAIN_CONTACT_FULL_NAME
, CST2.PARTY_ID REL_PARTY_ID
, CST2.PARTY_NAME REL_NAME
, REG.ATTENDANT_CONTACT_ID MAIN_CONTACT_ID
, CST1.PARTY_NAME ENROLLEE_FULL_NAME
, 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
, REG.CONFIRMATION_CODE
, AL.MEANING SYSTEM_STATUS_CODE
, REG.OBJECT_VERSION_NUMBER
, EVT.INVENTORY_ITEM_ID
, OOH.TRANSACTIONAL_CURR_CODE
, LKU.MEANING PAYMENT_STATUS
, NULL MEANING
, OOH.PAYMENT_TYPE_CODE
, OOH.PAYMENT_AMOUNT
, QLHT.NAME PRICE_LIST_NAME
, REG.CANCELLATION_REASON_CODE
FROM AMS_EVENT_REGISTRATIONS REG
, AMS_EVENT_OFFERS_VL EVT
, HZ_PARTIES CST
, HZ_PARTIES CST1
, HZ_PARTIES CST2
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS LKU
, OE_ORDER_HEADERS OOH
, QP_LIST_HEADERS_TL QLHT
, AMS_LOOKUPS AL
WHERE REG.EVENT_OFFER_ID = EVT.EVENT_OFFER_ID
AND REG.REGISTRANT_PARTY_ID = CST.PARTY_ID
AND REG.REGISTRANT_PARTY_ID != REG.REGISTRANT_CONTACT_ID
AND REG.ATTENDANT_CONTACT_ID = CST1.PARTY_ID
AND REG.REGISTRANT_CONTACT_ID = CST2.PARTY_ID
AND CST2.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.OWNER_TABLE_ID(+) = CST1.PARTY_ID
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.STATUS(+) = 'A'
AND HCP.PRIMARY_FLAG(+) = 'Y'
AND LKU.LOOKUP_TYPE(+) = 'AMS_EVENT_PAYMENT_STATUS'
AND LKU.LOOKUP_CODE(+) = REG.PAYMENT_STATUS_CODE
AND OOH.HEADER_ID (+) = REG.ORDER_HEADER_ID
AND QLHT.LIST_HEADER_ID (+) = OOH.PRICE_LIST_ID
AND REG.SYSTEM_STATUS_CODE = AL.LOOKUP_CODE(+)
AND AL.ENABLED_FLAG(+) = 'Y'
AND AL.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_STATUS' ) UNION ALL ( SELECT REG.EVENT_REGISTRATION_ID
, REG.EVENT_OFFER_ID
, EVT.EVENT_OFFER_NAME
, REG.ORDER_HEADER_ID
, REG.DATE_REGISTRATION_PLACED
, REG.REGISTRANT_PARTY_ID CUSTOMER_ID
, CST2.PARTY_TYPE PARTY_TYPE
, CST.PARTY_NAME MAIN_CONTACT_FULL_NAME
, CST2.PARTY_ID REL_PARTY_ID
, CST2.PARTY_NAME REL_NAME
, REG.ATTENDANT_CONTACT_ID MAIN_CONTACT_ID
, CST1.PARTY_NAME ENROLLEE_FULL_NAME
, 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
, REG.CONFIRMATION_CODE
, AL.MEANING SYSTEM_STATUS_CODE
, REG.OBJECT_VERSION_NUMBER
, EVT.INVENTORY_ITEM_ID
, OOH.TRANSACTIONAL_CURR_CODE
, LKU.MEANING PAYMENT_STATUS
, NULL MEANING
, OOH.PAYMENT_TYPE_CODE
, OOH.PAYMENT_AMOUNT
, QLHT.NAME PRICE_LIST_NAME
, REG.CANCELLATION_REASON_CODE
FROM AMS_EVENT_REGISTRATIONS REG
, AMS_EVENT_OFFERS_VL EVT
, HZ_PARTIES CST
, HZ_PARTIES CST1
, HZ_PARTIES CST2
, HZ_PARTIES CST3
, HZ_RELATIONSHIPS HPR
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS LKU
, OE_ORDER_HEADERS OOH
, QP_LIST_HEADERS_TL QLHT
, AMS_LOOKUPS AL
WHERE REG.EVENT_OFFER_ID = EVT.EVENT_OFFER_ID
AND REG.REGISTRANT_PARTY_ID = CST.PARTY_ID
AND REG.REGISTRANT_PARTY_ID != REG.REGISTRANT_CONTACT_ID
AND REG.ATTENDANT_CONTACT_ID = CST1.PARTY_ID
AND REG.REGISTRANT_CONTACT_ID = CST3.PARTY_ID
AND CST3.PARTY_TYPE = 'PERSON'
AND CST3.PARTY_ID = HPR.SUBJECT_ID
AND HPR.PARTY_ID = CST2.PARTY_ID
AND HPR.OBJECT_ID = CST.PARTY_ID
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.OWNER_TABLE_ID(+) = CST1.PARTY_ID
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.STATUS (+) = 'A'
AND HCP.PRIMARY_FLAG (+) = 'Y'
AND LKU.LOOKUP_TYPE (+) = 'AMS_EVENT_PAYMENT_STATUS'
AND LKU.LOOKUP_CODE (+) = REG.PAYMENT_STATUS_CODE
AND OOH.HEADER_ID (+) = REG.ORDER_HEADER_ID
AND QLHT.LIST_HEADER_ID (+) = OOH.PRICE_LIST_ID
AND REG.SYSTEM_STATUS_CODE = AL.LOOKUP_CODE(+)
AND AL.ENABLED_FLAG(+) = 'Y'
AND AL.LOOKUP_TYPE(+) = 'AMS_EVENT_REG_STATUS')

Columns