VIEW: APPS.AST_EV_HIS_PER_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 , CST.PARTY_ID CUSTOMER_ID , cst.party_type party_type , cst.party_name main_contact_full_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 , REG.SYSTEM_STATUS_CODE , REG.OBJECT_VERSION_NUMBER , evt.INVENTORY_ITEM_ID , OOH.TRANSACTIONAL_CURR_CODE , LKU.MEANING PAYMENT_STATUS , OOH.PAYMENT_TYPE_CODE , OOH.PAYMENT_AMOUNT , QPH.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_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_contact_id and cst1.party_id = hcp.owner_table_id(+) and hcp.owner_table_name(+) = 'HZ_PARTIES' and hcp.contact_point_type(+) = 'PHONE' and hcp.primary_flag(+) = 'Y' and hcp. status(+) = 'A' 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

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
, CST.PARTY_ID CUSTOMER_ID
, CST.PARTY_TYPE PARTY_TYPE
, CST.PARTY_NAME MAIN_CONTACT_FULL_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
, REG.SYSTEM_STATUS_CODE
, REG.OBJECT_VERSION_NUMBER
, EVT.INVENTORY_ITEM_ID
, OOH.TRANSACTIONAL_CURR_CODE
, LKU.MEANING PAYMENT_STATUS
, OOH.PAYMENT_TYPE_CODE
, OOH.PAYMENT_AMOUNT
, QPH.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_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_CONTACT_ID
AND CST1.PARTY_ID = HCP.OWNER_TABLE_ID(+)
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.PRIMARY_FLAG(+) = 'Y'
AND HCP. STATUS(+) = 'A'
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