Results for “variable_cost”

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

Overview

AMS_DM_OPT_TGT_METRICS is a table in the AMS (Marketing) schema of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It serves as the container for the costs and revenues associated with an Optimal Targeting Chart produced during a Scoring Run. Optimal Targeting Charts are used within Oracle Marketing's data-mining and scoring functionality to model expected profitability across a population of customers or prospects, allowing marketers to identify the segments and treatment thresholds that maximize return. This table persists the numeric inputs—fixed cost, variable cost, revenue, margin, and conversion percentage—that define each point or scenario on the chart, and associates them with the underlying scoring run and the campaign schedule under which the scoring executed.

From a heuristic Data Vault classification mined from the foreign-key structure, this object is best modeled as a link. It does not stand alone as a business hub; rather it resolves the association between a scoring run (AMS_DM_SCORES_ALL_B) and a campaign schedule (AMS_CAMPAIGN_SCHEDULES_B), carrying descriptive measures (cost, revenue, margin) alongside that relationship. Treated as a link, the table's metrics are attributes of the intersection of those two business entities rather than of either one independently.

Key Information Stored

The table contains 16 documented columns. The most significant are the following:

• OPT_TGT_METRIC_ID — The surrogate primary key (AMS_DM_OPT_TGT_METRICS_PK) uniquely identifying each metric row.
• SCORE_ID — Foreign key to AMS_DM_SCORES_ALL_B, identifying the scoring run that produced the optimal targeting chart. This column is also a business-key candidate via unique index AMS_DM_OPT_TGT_METRICS_U2.
• SCHEDULE_ID — Foreign key to AMS_CAMPAIGN_SCHEDULES_B, anchoring the metric to a specific campaign schedule.
• FIXED_COST, VARIABLE_COST — The cost components modeled for the chart point, distinguishing one-time versus per-unit (or per-response) expense.
• REVENUE_AMOUNT, MARGIN_AMOUNT — Expected revenue and resulting margin values derived from the scoring scenario.
• MARGIN_FLAG — Indicator of how margin is computed or applied within the chart.
• CONV_TO_ORDER_PCT — The assumed or modeled conversion-to-order percentage used in the profitability calculation.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-org/security-group access control.

Standard audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER) are also present, with OBJECT_VERSION_NUMBER supporting optimistic locking in the OAF/ADF framework.

Common Use Cases and Queries

Typical reporting needs center on reconstructing or comparing optimal targeting scenarios for a given scoring run. A frequent pattern joins the metric rows back to the score and schedule to present the full chart context:

• Retrieving all chart points for a scoring run: SELECT * FROM AMS.AMS_DM_OPT_TGT_METRICS WHERE SCORE_ID = :score_id;
• Comparing revenue versus margin across schedules: SELECT SCORE_ID, SCHEDULE_ID, REVENUE_AMOUNT, MARGIN_AMOUNT FROM AMS.AMS_DM_OPT_TGT_METRICS ORDER BY SCORE_ID, SCHEDULE_ID;
• Profitability ranking: aggregate MARGIN_AMOUNT - (FIXED_COST + VARIABLE_COST) by SCORE_ID to identify the most profitable targeting scenario.
• Security-filtered extracts: join to FND_SECURITY_GROUPS on SECURITY_GROUP_ID to restrict rows to the reporting user's security group.

The table is generally read-only for reporting; it is populated by the scoring engine rather than through direct user maintenance.

Related Objects

• AMS_DM_SCORES_ALL_B — joined on SCORE_ID; the parent scoring run definition.
• AMS_CAMPAIGN_SCHEDULES_B — joined on SCHEDULE_ID; the campaign schedule context.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; access-control parent.
• AMS_DM_OPT_TGT_METRICS_PK / _U1 / _U2 — primary key and unique indexes enforcing identity of OPT_TGT_METRIC_ID and SCORE_ID.

These relationships confirm the table's role as a link carrying profitability measures for optimal targeting analysis within Oracle Marketing.