Search Results priv_address2

Overview

AS_LETTER_MERGE_ENTRIES_V is a Sales Foundation (AS) view within Oracle EBS 12.1.1 and 12.2.2 that resolves the set of contacts to whom a letter or mailing is dispatched. It serves as the merge source for letter generation: rather than exposing the raw list of letter entries in AS_LIST_ENTRIES, the view joins the entry to the customer, contact, mailing address, promotion (letter), and optional picking header so that every row supplies a fully qualified recipient ready for merge-field substitution.

Because the view flattens contact identity, formatted mailing address, alternate private address, country territory, and title lookup into a single result set, it is consumed primarily by Oracle Reports and concurrent programs in the Sales Foundation letter-printing flow. It is also useful as a reporting surface for marketing and fulfillment teams auditing who received which letter. The view is documented as not implemented in the reference ETRM database, so behavior must be validated against the deployed instance before relying on it in production queries.

Underlying Base Objects

The view is defined over the following documented base objects:

• AS_LIST_ENTRIES — primary driving table; provides LIST_ENTRY_ID, LIST_ID, PERSON_ID, EVENT_ID, and the foreign keys to customer, address, contact, letter, and picking header.
• RA_CUSTOMERS — supplies CUSTOMER_ID and CUSTOMER_NAME.
• RA_CONTACTS — supplies contact attributes, including DO_NOT_MAIL_FLAG, MAILING_ADDRESS_ID, names, and EMAIL_ADDRESS.
• RA_ADDRESSES (ADDR1 / ADDR2) — ADDR1 is the list address; ADDR2 is the contact mailing address, joined with an outer join.
• AR_LOOKUPS — outer-joined lookup for CONTACT_TITLE.
• FND_TERRITORIES_VL (TERR1 / TERR2) — outer-joined territory names for the list and mailing addresses.
• SO_PICKING_HEADERS — outer join supplying PICK_SLIP_NUMBER.
• AS_PROMOTIONS — joined on LIST.LETTER_ID = PROMOTION_ID, identifying the letter.

Key Columns

• LIST_ENTRY_ID / LIST_ID — identifiers for the individual mailing entry and its parent list.
• CUSTOMER_ID / CUSTOMER_NAME — recipient account.
• CONTACT_ID, FIRST_NAME, LAST_NAME, SUFFIX, JOB_TITLE, DEPARTMENT, EMAIL_ADDRESS, DO_NOT_MAIL_FLAG — contact identity and suppression flags.
• ADDRESS_ID and ADDRESS1–PROVINCE, COUNTRY — the list mailing address (ADDR1).
• PRIV_ADDRESS1–PRIV_ADDRESS4 and USE_PRIV_ADD_FLAG — optional private mailing address (ADDR2) selected when USE_PVT_MAILING_ADDRESS is set.
• LETTER_ID, PROM.CODE — the promotion/letter being mailed.
• PICK_SLIP_NUMBER — picking header reference, where present.
• TITLE, TERRITORY names — lookup and territory decoding for presentation.

Common Use Cases and Queries

Typical uses include letter merge processing, auditing mailings against the do-not-mail flag, and reconciling list entries to picking headers. A common query counts recipients per letter, excluding suppressed contacts:

SELECT LETTER_ID, COUNT(*) recipient_count
FROM   AS_LETTER_MERGE_ENTRIES_V
WHERE  NVL(DO_NOT_MAIL_FLAG,'N') = 'N'
GROUP  BY LETTER_ID;

To retrieve the formatted mailing address for a specific list, using the private address when flagged:

SELECT LIST_ENTRY_ID, CUSTOMER_NAME, FIRST_NAME, LAST_NAME,
       CASE WHEN USE_PRIV_ADD_FLAG = 'Y' THEN PRIV_ADDRESS1 ELSE ADDRESS1 END addr1,
       CASE WHEN USE_PRIV_ADD_FLAG = 'Y' THEN PRIV_ADDRESS2 ELSE CITY END city
FROM   AS_LETTER_MERGE_ENTRIES_V
WHERE  LIST_ID = :p_list_id
ORDER  BY LAST_NAME, FIRST_NAME;

Fulfillment teams also join the view to AS_PROMOTIONS by LETTER_ID to report which promotions generated mail volume, and to SO_PICKING_HEADERS to tie letters to shipments. Because AS_LIST_ENTRIES drives the join, all letter entries appear even when optional addresses, territories, or picking headers are absent, thanks to the outer joins in the definition.