Product: | AS - Sales Foundation |
---|---|
Description: | Displays the contacts to whom a letter is mailed. |
Implementation/DBA Data: | Not implemented in this database |
SELECT LIST.LIST_ENTRY_ID
, LIST.LIST_ID
, CUST.CUSTOMER_ID
, ADDR1.ADDRESS_ID
, CONT.CONTACT_ID
, PROM.PROMOTION_ID
, LIST.PERSON_ID
, LIST.PERSON_USER_ID
, LIST.EVENT_ID
, CONT.DO_NOT_MAIL_FLAG
, LIST.USE_PVT_MAILING_ADDRESS
, PICK.PICK_SLIP_NUMBER
, CUST.CUSTOMER_NAME
, CONT.FIRST_NAME
, CONT.LAST_NAME
, LOOK1.MEANING
, CONT.SUFFIX
, CONT.JOB_TITLE
, CONT.MAIL_STOP
, CONT.EMAIL_ADDRESS
, ADDR1.ADDRESS1
, ADDR1.ADDRESS2
, ADDR1.ADDRESS3
, ADDR1.ADDRESS4
, ADDR1.CITY
, ADDR1.STATE
, ADDR1.POSTAL_CODE
, ADDR1.COUNTY
, ADDR1.PROVINCE
, TERR1.TERRITORY_SHORT_NAME
, ADDR2.ADDRESS1
, ADDR2.ADDRESS2
, ADDR2.ADDRESS3
, ADDR2.ADDRESS4
, ADDR2.CITY
, ADDR2.STATE
, ADDR2.POSTAL_CODE
, ADDR2.COUNTY
, ADDR2.PROVINCE
, TERR2.TERRITORY_SHORT_NAME
, CONT.DEPARTMENT
, PROM.CODE
FROM RA_ADDRESSES ADDR1
, RA_CUSTOMERS CUST
, RA_CONTACTS CONT
, RA_ADDRESSES ADDR2
, AS_LIST_ENTRIES LIST
, AR_LOOKUPS LOOK1
, FND_TERRITORIES_VL TERR1
, FND_TERRITORIES_VL TERR2
, SO_PICKING_HEADERS PICK
, AS_PROMOTIONS PROM
WHERE LIST.CUSTOMER_ID = CUST.CUSTOMER_ID
AND LIST.ADDRESS_ID = ADDR1.ADDRESS_ID
AND LIST.CONTACT_ID = CONT.CONTACT_ID
AND CONT.MAILING_ADDRESS_ID = ADDR2.ADDRESS_ID(+)
AND CONT.TITLE = LOOK1.LOOKUP_CODE(+)
AND LOOK1.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND ADDR1.COUNTRY = TERR1.TERRITORY_CODE(+)
AND ADDR2.COUNTRY = TERR2.TERRITORY_CODE(+)
AND LIST.PICKING_HEADER_ID = PICK.PICKING_HEADER_ID(+)
AND LIST.LETTER_ID = PROMOTION_ID