View: AST_EVENT_ENROLL_HISTORY_V

View Text

SELECT REG.EVENT_REGISTRATION_ID
, REG.EVENT_OFFER_ID
, EVT.EVENT_OFFER_NAME
, REG.ORDER_HEADER_ID
, REG.ORDER_LINE_ID
, REG.DATE_REGISTRATION_PLACED
, CST.PARTY_ID CUSTOMER_ID
, 'RELATIONSHIP' PARTY_TYPE
, CST1.PARTY_NAME MAIN_CONTACT_FULL_NAME
, REG.REGISTRANT_CONTACT_ID MAIN_CONTACT_ID
, REG.REGISTRANT_PARTY_ID MAIN_CONTACT_PARTY_ID
, REG.REGISTRANT_ACCOUNT_ID MAIN_CONTACT_ACCOUNT_ID
, REG.ORIGINAL_REGISTRANT_CONTACT_ID
, REG.ATTENDANT_PARTY_ID
, REG.ATTENDANT_CONTACT_ID
, REG.ATTENDANT_ACCOUNT_ID
, CST2.PARTY_NAME ENROLLEE_FULL_NAME
, REG.CONFIRMATION_CODE ENROLLEE_NUMBER
, APP.AREA_CODE || ' ' || APP.PHONE_NUMBER || ' ' || APP.EXTENSION ENROLLEE_PHONE_NUMBER
, REG.REGISTRATION_GROUP_ID
, REG.CONFIRMATION_CODE
, REG.SYSTEM_STATUS_CODE
, REG.REG_SOURCE_TYPE_CODE
, REG.OBJECT_VERSION_NUMBER
, EVT.INVENTORY_ITEM_ID
, OOL.UNIT_SELLING_PRICE
, OOH.TRANSACTIONAL_CURR_CODE
, REG.PAYMENT_STATUS_CODE
, LKU.MEANING PAYMENT_STATUS
, OOH.PAYMENT_TYPE_CODE
, OOH.PAYMENT_AMOUNT
, OOL.PRICE_LIST_ID
, QPH.NAME PRICE_LIST_NAME
FROM AMS_EVENT_REGISTRATIONS REG
, AMS_EVENT_OFFERS_VL EVT
, HZ_ORG_CONTACTS HOC
, HZ_PARTY_RELATIONSHIPS HPR
, HZ_PARTIES CST
, HZ_PARTIES CST1
, HZ_PARTIES CST2
, JTF_PARTY_PHONES_V APP
, AMS_LOOKUPS LKU
, OE_ORDER_HEADERS OOH
, OE_ORDER_LINES OOL
, QP_LIST_HEADERS_VL QPH
WHERE EVT.EVENT_OFFER_ID = REG.EVENT_OFFER_ID
AND REG.REGISTRANT_PARTY_ID = CST.PARTY_ID
AND CST.PARTY_TYPE IN ('ORGANIZATION'
, 'RELATIONSHIP')
AND HPR.PARTY_RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID(+)
AND CST.PARTY_ID = HPR.OBJECT_ID
AND CST1.PARTY_ID = HPR.SUBJECT_ID
AND CST1.PARTY_ID(+) = REG.REGISTRANT_CONTACT_ID
AND APP.CONTACT_ID (+) = HOC.ORG_CONTACT_ID
AND APP.PRIMARY_FLAG (+) = 'Y'
AND REG.ATTENDANT_PARTY_ID = CST2.PARTY_ID(+)
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 OOL.LINE_ID (+) = REG.ORDER_LINE_ID
AND QPH.LIST_HEADER_ID (+) = OOL.PRICE_LIST_ID UNION ALL ( SELECT REG.EVENT_REGISTRATION_ID
, REG.EVENT_OFFER_ID
, EVT.EVENT_OFFER_NAME
, REG.ORDER_HEADER_ID
, REG.ORDER_LINE_ID
, REG.DATE_REGISTRATION_PLACED
, CST.PARTY_ID CUSTOMER_ID
, CST.PARTY_TYPE PARTY_TYPE
, CST.PARTY_NAME MAIN_CONTACT_FULL_NAME
, REG.REGISTRANT_CONTACT_ID MAIN_CONTACT_ID
, REG.REGISTRANT_PARTY_ID MAIN_CONTACT_PARTY_ID
, REG.REGISTRANT_ACCOUNT_ID MAIN_CONTACT_ACCOUNT_ID
, REG.ORIGINAL_REGISTRANT_CONTACT_ID
, REG.ATTENDANT_PARTY_ID
, REG.ATTENDANT_CONTACT_ID
, REG.ATTENDANT_ACCOUNT_ID
, CST1.PARTY_NAME ENROLLEE_FULL_NAME
, REG.CONFIRMATION_CODE ENROLLEE_NUMBER
, HCP.PHONE_NUMBER ENROLLEE_PHONE_NUMBER
, REG.REGISTRATION_GROUP_ID
, REG.CONFIRMATION_CODE
, REG.SYSTEM_STATUS_CODE
, REG.REG_SOURCE_TYPE_CODE
, REG.OBJECT_VERSION_NUMBER
, EVT.INVENTORY_ITEM_ID
, OOL.UNIT_SELLING_PRICE
, OOH.TRANSACTIONAL_CURR_CODE
, REG.PAYMENT_STATUS_CODE
, LKU.MEANING PAYMENT_STATUS
, OOH.PAYMENT_TYPE_CODE
, OOH.PAYMENT_AMOUNT
, OOL.PRICE_LIST_ID
, QPH.NAME PRICE_LIST_NAME
FROM AMS_EVENT_REGISTRATIONS REG
, AMS_EVENT_OFFERS_VL EVT
, HZ_PARTIES CST
, HZ_PARTIES CST1
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS LKU
, OE_ORDER_HEADERS OOH
, OE_ORDER_LINES OOL
, QP_LIST_HEADERS_VL QPH
WHERE EVT.EVENT_OFFER_ID = REG.EVENT_OFFER_ID
AND CST.PARTY_ID = REG.REGISTRANT_PARTY_ID
AND CST.PARTY_TYPE = 'PERSON'
AND CST1.PARTY_ID = REG.ATTENDANT_PARTY_ID
AND CST1.PARTY_ID = HCP.OWNER_TABLE_ID(+)
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
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 OOL.LINE_ID(+) = REG.ORDER_LINE_ID
AND QPH.LIST_HEADER_ID (+) = OOL.PRICE_LIST_ID ) UNION ALL SELECT REG.EVENT_REGISTRATION_ID
, REG.EVENT_OFFER_ID
, EVT.EVENT_OFFER_NAME
, REG.ORDER_HEADER_ID
, REG.ORDER_LINE_ID
, REG.DATE_REGISTRATION_PLACED
, CST.PARTY_ID CUSTOMER_ID
, CST.PARTY_TYPE PARTY_TYPE
, CST.PARTY_NAME MAIN_CONTACT_FULL_NAME
, REG.REGISTRANT_CONTACT_ID MAIN_CONTACT_ID
, REG.REGISTRANT_PARTY_ID MAIN_CONTACT_PARTY_ID
, REG.REGISTRANT_ACCOUNT_ID MAIN_CONTACT_ACCOUNT_ID
, REG.ORIGINAL_REGISTRANT_CONTACT_ID
, REG.ATTENDANT_PARTY_ID
, REG.ATTENDANT_CONTACT_ID
, REG.ATTENDANT_ACCOUNT_ID
, CST1.PARTY_NAME ENROLLEE_FULL_NAME
, REG.CONFIRMATION_CODE ENROLLEE_NUMBER
, HCP.PHONE_NUMBER ENROLLEE_PHONE_NUMBER
, REG.REGISTRATION_GROUP_ID
, REG.CONFIRMATION_CODE
, REG.SYSTEM_STATUS_CODE
, REG.REG_SOURCE_TYPE_CODE
, REG.OBJECT_VERSION_NUMBER
, EVT.INVENTORY_ITEM_ID
, OOL.UNIT_SELLING_PRICE
, OOH.TRANSACTIONAL_CURR_CODE
, REG.PAYMENT_STATUS_CODE
, LKU.MEANING PAYMENT_STATUS
, OOH.PAYMENT_TYPE_CODE
, OOH.PAYMENT_AMOUNT
, OOL.PRICE_LIST_ID
, QPH.NAME PRICE_LIST_NAME
FROM AMS_EVENT_REGISTRATIONS REG
, AMS_EVENT_OFFERS_VL EVT
, HZ_PARTIES CST
, HZ_PARTIES CST1
, HZ_CONTACT_POINTS HCP
, AMS_LOOKUPS LKU
, OE_ORDER_HEADERS OOH
, OE_ORDER_LINES OOL
, QP_LIST_HEADERS_VL QPH
WHERE EVT.EVENT_OFFER_ID = REG.EVENT_OFFER_ID
AND CST.PARTY_ID = REG.REGISTRANT_PARTY_ID
AND CST.PARTY_TYPE = 'ORGANIZATION'
AND CST.PARTY_ID = REG.REGISTRANT_CONTACT_ID
AND CST1.PARTY_ID = REG.ATTENDANT_PARTY_ID
AND CST1.PARTY_ID = HCP.OWNER_TABLE_ID(+)
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
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 OOL.LINE_ID(+) = REG.ORDER_LINE_ID
AND QPH.LIST_HEADER_ID (+) = OOL.PRICE_LIST_ID

Columns