Results for “as_event_letters_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AS_EVENT_LETTERS_V is a reporting view in the Oracle E-Business Suite Sales Foundation (AS) module. It presents event letter information by joining the base event letters table to promotion definitions and to two lookup references. The view exposes enrollment status and letter type both as raw codes and as translated meanings, which makes it directly usable in concurrent programs, BI Publisher data templates, and ad hoc SQL without requiring the caller to write the lookup joins manually.

In the context of a search for "enrollment_status," this view is the primary access point for the enrollment status of a generated event letter. The column ENROLLMENT_STATUS_CODE holds the stored code from the base table, while the derived column ENROLLMENT_STATUS resolves that code through AS_LOOKUPS using the lookup type EVENT_ENROLLMENT_STATUS. This distinction matters: the code column is used for filtering and joining, whereas the meaning column is used for display and reporting.

Underlying Base Objects

The view is defined over four objects:

• AS_EVENT_LETTERS — the driving base table, aliased EVNTLET. It supplies the event letter identifier, the foreign keys to the event and the letter, the type code, the enrollment status code, the completed flag, the mail date, the standard WHO columns, the concurrent request columns, and the fifteen descriptive flexfield attribute columns.
• AS_PROMOTIONS — aliased PROM. Joined to the base table on LETTER_ID = PROMOTION_ID, with the additional predicate PROM.TYPE = 'L' restricting the join to rows that represent letters rather than other promotion types.
• AS_LOOKUPS — aliased ASLKP1. Joined on TYPE_CODE = LOOKUP_CODE with LOOKUP_TYPE = 'EVENT_LETTER_TYPE', producing the TYPE column.
• AS_LOOKUPS — aliased ASLKP2. Joined on ENROLLMENT_STATUS_CODE = LOOKUP_CODE with LOOKUP_TYPE = 'EVENT_ENROLLMENT_STATUS', producing the ENROLLMENT_STATUS column.

Because both lookup joins are inner joins and the promotion join is also an inner join, a row is returned only when the letter exists in AS_PROMOTIONS with the correct type and both code values resolve to active lookup definitions. Rows with an enrollment status code that has no matching lookup entry are silently excluded from the result set.

Key Columns

• EVENT_LETTER_ID — primary key of the event letter record.
• EVENT_ID — identifier of the event to which the letter is attached.
• LETTER_ID — identifier of the letter promotion; joins to AS_PROMOTIONS.PROMOTION_ID.
• TYPE_CODE / TYPE — the stored event letter type code and its translated meaning from the EVENT_LETTER_TYPE lookup.
• ENROLLMENT_STATUS_CODE / ENROLLMENT_STATUS — the stored enrollment status code and its translated meaning from the EVENT_ENROLLMENT_STATUS lookup.
• COMPLETED_FLAG — indicates whether processing of the letter has finished.
• MAIL_DATE — the date the letter was or is scheduled to be mailed.
• LETTER_NAME / LETTER_CODE — the name and code of the source promotion record.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield context and segment values.
• WHO and request columns — LAST_UPDATE_DATE, CREATED_BY, REQUEST_ID, PROGRAM_ID, and related audit fields.

Common Use Cases and Queries

The most frequent use is operational tracking of letters by enrollment status, for example retrieving all letters that have not yet been mailed.

SELECT event_letter_id, letter_name, enrollment_status, mail_date
FROM   as_event_letters_v
WHERE  enrollment_status_code = 'PENDING'
AND    completed_flag = 'N';

A second pattern aggregates letter volume by status and type for a management report:

SELECT type, enrollment_status, COUNT(*)
FROM   as_event_letters_v
GROUP  BY type, enrollment_status;

A third pattern joins the view to an event source to correlate letters with the driving activity:

SELECT v.event_id, v.letter_code, v.enrollment_status, v.mail_date
FROM   as_event_letters_v v
WHERE  v.mail_date >= :start_date;

Two practical cautions apply. First, filtering should be performed on the code columns rather than the meaning columns, since meanings are translatable and environment-dependent. Second, because the lookup joins are inner joins, any enrollment status code lacking an active EVENT_ENROLLMENT_STATUS lookup definition will cause the row to be omitted entirely; queries intended to reconcile counts against the base table should therefore be written against AS_EVENT_LETTERS directly. ETRM notes this view as not implemented in the documented database, so availability should be confirmed in the target instance before use.