Results for “ozf_quota_alerts”

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

Overview

OZF_QUOTA_ALERTS is a Trade Management (OZF) transactional table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores quota alert records generated against customer accounts, sales resources, and products. Within the OZF schema, the table functions as the persistence layer for quota-related threshold notifications — tracking month-to-date, quarter-to-date, and year-to-date alert conditions alongside back-order and outstanding-order indicators. It supports the quota management and trade promotion analytics performed by sales administrators and channel managers who monitor whether assigned quotas are being met, exceeded, or lagging at a given point in time.

From a Data Vault modeling perspective, the heuristic classification for this table is standalone. In practical terms, OZF_QUOTA_ALERTS behaves as a satellite-like fact store keyed by a single surrogate identity, with a lightweight reference to HZ_CUST_ACCOUNTS. It does not act as a hub-and-link bridge; instead it records periodic alert snapshots per reporting date, making REPORT_DATE a natural time dimension for historical trending.

Key Information Stored

The table contains 13 documented columns. The most significant are:

• QUOTA_ALERT_ID — the surrogate primary key, defined by constraint OZF_QUOTA_ALERTS_PK and also enforced by the unique index OZF_QUOTA_ALERTS_U1. This is the sole documented business-key candidate; no composite natural key is exposed in the metadata.
• REPORT_DATE — the effective date of the alert snapshot, enabling period-over-period comparison of quota performance.
• RESOURCE_ID — identifies the sales resource (quota holder) to whom the alert pertains.
• ALERT_FOR — indicates the alert subject or category, distinguishing the type of quota condition being flagged.
• CUST_ACCOUNT_ID — foreign key to HZ_CUST_ACCOUNTS, anchoring the alert to a specific customer account.
• SHIP_TO_SITE_USE_ID — the ship-to site use, allowing alerts to be scoped to a delivery location.
• PRODUCT_ATTRIBUTE and PRODUCT_ATTR_VALUE — a flexible attribute/value pair used to filter or segment alerts by product characteristics.
• MTD_ALERT, QTD_ALERT, YTD_ALERT — period quota alert flags or measures for month-to-date, quarter-to-date, and year-to-date windows.
• BACK_ORDER_ALERT — flags quota exposure caused by back-ordered demand.
• OUTSTAND_ORDER_ALERT — flags quota impact from outstanding (booked but unshipped) orders.

Common Use Cases and Queries

Typical usage centers on quota monitoring dashboards, alert exception reports, and customer-level quota trend analysis. A common pattern retrieves the latest alert per resource and report date:

• Listing all alerts for a given period: SELECT quota_alert_id, report_date, resource_id, cust_account_id, mtd_alert, qtd_alert, ytd_alert FROM ozf.ozf_quota_alerts WHERE report_date BETWEEN :p_from AND :p_to ORDER BY report_date, resource_id;
• Joining to the customer master to resolve account names: SELECT q.quota_alert_id, hca.account_number, hca.account_name, q.mtd_alert, q.qtd_alert FROM ozf.ozf_quota_alerts q, hz_cust_accounts hca WHERE q.cust_account_id = hca.cust_account_id AND q.report_date = :p_date;
• Isolating supply-driven alerts: filter where BACK_ORDER_ALERT or OUTSTAND_ORDER_ALERT is set to identify quota attainment risk caused by fulfillment delays.
• Segmenting by product via PRODUCT_ATTRIBUTE / PRODUCT_ATTR_VALUE for category-level quota reporting.

Related Objects

• HZ_CUST_ACCOUNTS — referenced through OZF_QUOTA_ALERTS.CUST_ACCOUNT_ID; the primary external dependency.
• OZF_QUOTA_ALERTS_PK / OZF_QUOTA_ALERTS_U1 — the primary key constraint and unique index on QUOTA_ALERT_ID.
• OZF_QUOTAS and related OZF quota setup tables — provide the quota definitions against which alerts are raised.
• OZF_QUOTA_ALERTS_HIST / period snapshots — conceptually consumed alongside REPORT_DATE for trend reporting.
• HZ_CUST_SITE_USES_ALL — source of SHIP_TO_SITE_USE_ID resolution for site-level reporting.
• JTF_RS_RESOURCE_EXTNS — typical resolution target for RESOURCE_ID to obtain resource names.