Search Results completed_flag




Overview
Explore in AI Chat

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

Overview

AS_EVENT_LETTERS is a multi-organization reporting view owned by the APPS schema in Oracle E-Business Suite, belonging to the AS (Sales Foundation) product family. It presents event letter data — records that associate a business or marketing event with a letter generated for a contact or party — while enforcing the operating unit security model through a row-filtering WHERE clause on ORG_ID. Rather than exposing all rows, the view derives the current organization context from the session variable USERENV('CLIENT_INFO'), which Oracle EBS sets when a user selects an operating unit. This makes the view the standard, security-aware access point for event letter information in reports, concurrent programs, and integrations, as opposed to querying the underlying base table directly.

Underlying Base Objects

The documented view metadata identifies a single referenced base object: the synonym AS_EVENT_LETTERS_ALL, which points to the underlying multi-org table. AS_EVENT_LETTERS_ALL stores event letter rows for all operating units, without any organization filter. AS_EVENT_LETTERS is defined as a SELECT over that synonym, listing every column of the base table and appending a WHERE predicate:

NVL(ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'), 1, 1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'), 1, 10))), -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'), 1, 1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'), 1, 10))), -99)

This predicate extracts the first ten characters of CLIENT_INFO as the active organization identifier and compares it to the row's ORG_ID, treating a null ORG_ID as -99. Users therefore see only event letters belonging to the operating unit currently set in their session. The view is documented as VALID.

Key Columns

Common Use Cases and Queries

Typical uses include operational reporting on event letter status, reconciliation of mailings, and integration extracts. Because of the CLIENT_INFO filter, queries must run within a session whose operating unit context is initialized; a raw SQL*Plus session without initializing CLIENT_INFO returns only rows with ORG_ID matching the fallback value.

Find letters not yet completed for the current operating unit:

SELECT event_letter_id, event_id, letter_id, mail_date
FROM apps.as_event_letters
WHERE NVL(completed_flag, 'N') = 'N';

Count event letters by type and completion status:

SELECT type_code, completed_flag, COUNT(*) qty
FROM apps.as_event_letters
GROUP BY type_code, completed_flag;

Retrieve recent mailings for one event:

SELECT event_letter_id, letter_id, mail_date, completed_flag
FROM apps.as_event_letters
WHERE event_id = :event_id
ORDER BY mail_date DESC;

For cross-organization reporting, developers should query AS_EVENT_LETTERS_ALL and apply an explicit ORG_ID predicate rather than relying on the security-aware view.