Results for “as_programs_v”

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

Overview

AS_PROGRAMS_V is a reporting and inquiry view in the Oracle E-Business Suite Sales Foundation module (product code AS). It exposes promotion master data maintained in the promotions entity, filtering specifically to records whose type is Program (TYPE = 'P'). In EBS 12.1.1 and 12.2.2 the view is documented as the "Promotions view" within order-to-cash promotions functionality. It presents a denormalized, reusable projection of promotion attributes—financial estimates, response and reach metrics, descriptive flexfields, and the translated territory name—that report writers and integration developers can consume without negotiating the base table structure directly.

The view is documented as not implemented in the reference database, meaning it may not be created in every environment; availability depends on whether the Sales Foundation promotions schema has been deployed and patched. Note also the naming convention: the view AS_PROGRAMS_V surfaces promotions where TYPE='P' (programs), which is distinct from other AS promotions views that may expose different promotion type codes.

Underlying Base Objects

Per the documented view text, the definition joins two objects:

• AS_PROMOTIONS — the promotions base table, aliased PROM, supplying all primary business and seed columns.
• FND_TERRITORIES_TL — the translated territories table, aliased FNDTERR, joined on PROM.COUNTRY = FNDTERR.TERRITORY_CODE to resolve the translated territory short name.

The join to FND_TERRITORIES_TL is an outer join (indicated by the (+) operator, characteristic of Oracle-style outer join syntax used in the 11g/12g code line), and is additionally restricted on TERRITORY_SHORT_NAME language by FNDTERR.LANGUAGE(+) = USERENV('LANG'). The USERENV('LANG') function ensures territory names are returned in the session's current language. The predicate PROM.TYPE = 'P' restricts the result set to promotion programs only.

Key Columns

• ROW_ID — the ROWID of the underlying AS_PROMOTIONS row, used for identifying records and enabling routed updates.
• PROMOTION_ID — primary key of the promotion; the correct join key to promotion-related detail tables.
• CODE, NAME, DESCRIPTION — business identifiers for the promotion program.
• TYPE — promotion type discriminator; always 'P' in this view.
• ENABLED_FLAG, STATUS — control whether the promotion is active and its lifecycle state.
• COUNTRY, COUNTRY (TERRITORY_SHORT_NAME) — the country/territory code and its translated short name from FND_TERRITORIES_TL.
• CURRENCY_CODE — currency in which the promotion's cost figures are expressed.
• EXPECTED_PROMOTION_COST, ACTUAL_PROMOTION_COST — budgeted versus realized promotion cost.
• EXPECTED_NUM_OF_RESPONSES, ACTUAL_NUM_OF_RESPONSES — forecast versus realized response counts.
• EXPECTED_REACH — the anticipated audience/coverage volume of the promotion; a planning metric central to promotion effectiveness analysis. This is the column most directly relevant to searches such as "expected_reach."
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 — descriptive flexfield segments for customer-extended promotion data.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical uses include promotion effectiveness reporting (comparing EXPECTED_REACH and EXPECTED_NUM_OF_RESPONSES against ACTUAL_NUM_OF_RESPONSES), cost variance analysis (EXPECTED versus ACTUAL_PROMOTION_COST), and inbound integration extraction of promotion master data.

-- All enabled promotion programs with cost and reach
SELECT promotion_id, code, name, country, currency_code,
       expected_reach, expected_num_of_responses,
       expected_promotion_cost, actual_promotion_cost
FROM   as_programs_v
WHERE  enabled_flag = 'Y';

-- Promotion effectiveness by country
SELECT country, SUM(expected_reach) expected_reach,
       SUM(actual_num_of_responses) actual_responses
FROM   as_programs_v
GROUP  BY country;

Because the view is a simple projection over AS_PROMOTIONS, it is generally read-only in practice: updates should be performed against the base table. When the view is absent, equivalent queries can be written directly against AS_PROMOTIONS joined to FND_TERRITORIES_TL.