Results for “retail_uom_code”

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

Overview

OZF_WORKSHEET_LINES is a Trade Management (OZF) transactional table in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the individual discount line records that make up a worksheet, which is the promotional planning construct used by Trade Management to model offers, accruals, and settlement activity across a customer or product hierarchy. Each row represents one discount definition or condition belonging to a worksheet header, capturing the arithmetic rule, product or product-attribute qualifier, volume or value thresholds, and financial values needed to evaluate the offer.

From a Data Vault modeling perspective, the metadata's heuristic classification is a link. The table exists primarily to associate a worksheet header with the detail-level attributes of a promotion line, and it carries a foreign key relationship back to OZF_WORKSHEET_HEADERS_B. That classification is a suggestion derived from the foreign key structure rather than an Oracle-documented designation; in a conventional relational model the table functions as a child detail entity of the worksheet header.

Key Information Stored

The table is owned by the OZF schema and contains 38 documented columns. The most significant are:

• WORKSHEET_LINE_ID — the surrogate primary key, enforced by OZF_WORKSHEET_LINES_PK and also carried by the unique index OZF_WORKSHEET_LINES_U1. It is the single documented business-key candidate and the column referenced by dependent records.
• WORKSHEET_HEADER_ID — foreign key to OZF_WORKSHEET_HEADERS_B, tying the line to its parent worksheet.
• PARENT_WORKSHEET_LINE_ID — self-referencing pointer that supports line hierarchies within a worksheet.
• ARITHMETIC_OPERATOR and OPERAND — define how the discount amount is calculated.
• PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTRIBUTE, and PRODUCT_ATTR_VALUE — the descriptive flexfield-style qualifier identifying which product attribute the line applies to.
• PRODUCT_CONCAT_VALUE and PRODUCT_UOM_CODE — concatenated product identifier and its unit of measure.
• FORECASTED_UNITS, QUANTITY, LIST_PRICE, ITEM_COST, and DISTRIBUTION_VALUE — the volume and financial drivers of the discount.
• MIN_ORDER_VALUE, MAX_ORDER_VALUE, MAX_UNITS_PER_ORDER, MAX_ORDERS_PER_CUSTOMER, MAX_UNITS_PER_RULE, and MAX_COMMITTED_AMT_PER_RULE — the constraint and eligibility thresholds governing the offer.
• OFFER_TYPE, SCAN_TYPE, SCAN_VALUE, and SCAN_UNIT_FORECAST — offer classification and scan-based accrual inputs.
• RETAIL_UOM_CODE, DESIRED_RETAIL_PRICE, RETAILERS_MARGIN, and ADJUST_BUDGET — retail pricing and budget adjustment attributes.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — the standard WHO columns supporting optimistic locking and audit.

Common Use Cases and Queries

Typical reporting retrieves all lines for a worksheet, reconciles forecasted versus committed volumes, or validates that discount rules do not exceed order and customer thresholds. A common pattern joins the line to its header on WORKSHEET_HEADER_ID:

• Line-level detail extraction: SELECT l.worksheet_line_id, l.worksheet_header_id, l.offer_type, l.list_price, l.quantity, l.forecasted_units FROM ozf.ozf_worksheet_lines l WHERE l.worksheet_header_id = :p_header_id;
• Header context join: ... FROM ozf.ozf_worksheet_lines l, ozf.ozf_worksheet_headers_b h WHERE l.worksheet_header_id = h.worksheet_header_id AND h.worksheet_header_id = :p_header_id;
• Threshold exception reporting: filter on MAX_UNITS_PER_ORDER, MAX_ORDERS_PER_CUSTOMER, or MAX_COMMITTED_AMT_PER_RULE to surface offers with unusually tight caps.
• Hierarchy traversal: use PARENT_WORKSHEET_LINE_ID to reproduce nested line structures for a worksheet.
• Retail margin analysis: compare DESIRED_RETAIL_PRICE, RETAILERS_MARGIN, and ITEM_COST to evaluate promotional profitability.

Because the table is transactional and carries OBJECT_VERSION_NUMBER, extracts used for downstream analytics should be date-filtered on LAST_UPDATE_DATE to keep incremental loads stable.

Related Objects

• OZF_WORKSHEET_HEADERS_B — parent header table; joined on WORKSHEET_HEADERS_B.WORKSHEET_HEADER_ID = OZF_WORKSHEET_LINES.WORKSHEET_HEADER_ID. This is the only documented foreign key relationship and the principal dependency.
• OZF_WORKSHEET_LINES (self) — referenced through PARENT_WORKSHEET_LINE_ID for line hierarchies.
• OZF_WORKSHEET_LINES_PK — the primary key constraint on WORKSHEET_LINE_ID, used to enforce and locate individual lines.
• OZF_WORKSHEET_LINES_U1 — unique index on WORKSHEET_LINE_ID and the documented business-key candidate.
• Trade Management worksheet maintenance and offer evaluation logic — the OZF module's promotion and accrual processes read these lines when qualifying transactions and computing accruals against a worksheet.

The metadata supplied documents only the header relationship and the primary key constraint, so any further dependencies should be confirmed against the deployed 12.1.1 or 12.2.2 schema before being relied upon.