Results for “as_mail_blitzes_v”

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

Overview

AS_MAIL_BLITZES_V is a reporting and inquiry view within the Oracle E-Business Suite Sales Foundation (AS) module. It consolidates data drawn from the promotions and collateral request tables to present a unified picture of mail blitz activities. A mail blitz in EBS represents a direct-mail promotional campaign in which collateral such as letters, brochures, or printed materials is shipped to a targeted list of recipients. The view exposes both the planning attributes of the promotion (costs, expected reach, response counts, language, and currency) and the operational attributes of the associated collateral request (scheduled date, shipment method, list identifiers, and completion status).

Because the view joins descriptive lookup meanings, territory translations, list definitions, letter definitions, and word processor output paths, it serves as a convenient single source for reporting on mail blitz campaigns without requiring report authors to reconstruct the underlying joins. It is primarily used in Sales Foundation inquiry screens, concurrent program outputs, and ad hoc reporting where campaign performance and fulfillment status must be reviewed together.

Underlying Base Objects

The view text defines joins across the following base objects:

• AS_PROMOTIONS (aliased P) — the driving table, restricted to promotion type 'B' and blitz type 'MAIL'. It supplies the core campaign definition.
• AS_PROMOTIONS (aliased LET) — self-join providing letter-level promotion attributes such as code, name, and external file name.
• AS_COLLATERAL_REQ (aliased CR) — the collateral request joined to the promotion on PROMOTION_ID = MAIL_BLITZ_ID, supplying fulfillment and shipment details.
• AS_LOOKUPS (ASLKP1, ASLKP2, ASLKP3) — three lookup joins resolving blitz status, language, and a third coded meaning.
• FND_TERRITORIES_TL (TERR) — outer-joined territory translation filtered by USERENV('LANG').
• AS_LISTS (LIST) — the target list definition and its online flag.
• AS_WORD_PROCESSORS (WP) — outer-joined word processor configuration, including letter location path and startup command.

Note that the documented metadata records no referenced base objects and the view is not implemented in the reference database, so the structure above is derived entirely from the view text.

Key Columns

• PROMOTION_ID, CODE, NAME, TYPE, STATUS, ENABLED_FLAG — identity and lifecycle of the mail blitz promotion.
• BLITZ_TYPE, MAILBLITZ_DONOTMAIL_FLAG — classifies the blitz and controls suppression of mailings.
• EXPECTED_PROMOTION_COST, ACTUAL_PROMOTION_COST, EXPECTED_NUM_OF_RESPONSES, ACTUAL_NUM_OF_RESPONSES, EXPECTED_REACH — planned versus actual campaign performance metrics.
• COLLATERAL_REQUEST_ID, SCHEDULED_DATE, COMPLETION_DATE, SHIPMENT_METHOD, SHIPMENT_INSTRUCTIONS, PARTIAL_SHIP_FLAG — fulfillment execution details from AS_COLLATERAL_REQ.
• LIST_ID, LIST_ENTRY_ID, LETTER_ID, MAIL_BLITZ_ID — links to the target list, letter, and originating blitz.
• MEANING (ASLKP1–ASLKP3), TERRITORY_SHORT_NAME — decoded lookup values and territory name for display.
• PATH, STARTUP_COMMAND, LETTER_LOCATION_PATH — word processor output configuration for letter generation.
• ATTRIBUTE1–ATTRIBUTE15, ATTRIBUTE_CATEGORY — descriptive flexfield columns inherited from AS_PROMOTIONS.

Common Use Cases and Queries

Typical scenarios include auditing active mail blitz campaigns, reconciling expected against actual response counts, and reviewing collateral shipment status. A representative query is:

• SELECT promotion_id, code, name, status, expected_num_of_responses, actual_num_of_responses FROM as_mail_blitzes_v WHERE enabled_flag = 'Y';
• SELECT code, scheduled_date, shipment_method, partial_ship_flag FROM as_mail_blitzes_v WHERE status = 'COMPLETE';
• SELECT name, territory_short_name, list_id FROM as_mail_blitzes_v ORDER BY scheduled_date;

Because the view joins multiple lookup and translation tables, filter predicates on decoded meanings should be applied against the MEANING columns rather than raw codes to preserve readability.