VIEW: APPS.AST_EV_HIS_ORG_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')