Product: | AS - Sales Foundation |
---|---|
Description: | Event merge view |
Implementation/DBA Data: | Not implemented in this database |
SELECT PROM.PROMOTION_ID
, TYPE.NAME
, PROM.CODE
, PROM.DESCRIPTION
, FAC.NAME
, FAC.ADDRESS1
, FAC.ADDRESS2
, FAC.ADDRESS3
, FAC.ADDRESS4
, FAC.CITY
, FAC.STATE
, FAC.POSTAL_CODE
, FAC.COUNTY
, TERR.TERRITORY_SHORT_NAME
, PERSON.FIRST_NAME
, PERSON.LAST_NAME
, PROM.EVENT_START_DATE
, PROM.EVENT_START_TIME
, PROM.EVENT_END_DATE
, PROM.EVENT_END_TIME
, PROM.EVENT_REG_TIME
, PROM.EVENT_ENROLLMENT_FEE
, ENROLL.PAYMENT_AMOUNT
, ENROLL.NUM_ENROLLED
, ENROLL.NUM_ATTENDED
, ENROLL.CUSTOMER_ID
, ENROLL.ADDRESS_ID
, ENROLL.CONTACT_ID
FROM AS_PROMOTIONS PROM
, AS_EVENT_FACILITIES FAC
, AS_EVENT_ENROLLMENTS ENROLL
, AS_EVENT_NAMES TYPE
, FND_TERRITORIES_TL TERR
, PER_ALL_PEOPLE_F PERSON
WHERE PROM.EVENT_FACILITY_ID = FAC.EVENT_FACILITY_ID(+)
AND PROM.PROMOTION_ID = ENROLL.EVENT_ID(+)
AND PROM.EVENT_NAME_ID = TYPE.EVENT_NAME_ID(+)
AND PROM.TYPE = 'E'
AND FAC.COUNTRY_CODE = TERR.TERRITORY_CODE(+)
AND TERR.LANGUAGE(+) = USERENV('LANG')
AND PROM.EVENT_COORDIN_PERSON_ID = PERSON.PERSON_ID(+)
AND TRUNC(SYSDATE) BETWEEN PERSON.EFFECTIVE_START_DATE(+)
AND PERSON.EFFECTIVE_END_DATE(+)