Results for “ozf_resale_lines_all_u1”

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

Overview

OZF_RESALE_LINES_ALL is a transactional table in the Oracle E-Business Suite Trade Management (formerly ETRM) schema, OZF. It stores line-level detail for third-party resale and chargeback transactions, capturing each product movement associated with a resale header. Where OZF_RESALE_HEADERS_ALL holds the header or batch context, OZF_RESALE_LINES_ALL holds the granular item, pricing, quantity, and party information required for resale processing, chargeback calculation, and downstream settlement. The table resides in the APPS_TS_TX_DATA tablespace and is documented as VALID in both EBS 12.1.1 and 12.2.2.

Under the heuristic Data Vault classification supplied with the ETRM metadata, OZF_RESALE_LINES_ALL is described as satellite-leaning. In a Data Vault 2.0 interpretation, this object would most naturally be modeled as a satellite attached to the resale header hub, with the RESALE_LINE_ID acting as a dependent unique business key rather than an independent hub key. Practitioners should treat this classification as a modeling suggestion rather than a normative EBS construct.

Key Information Stored

The table contains 194 documented columns. The primary key, enforced by the unique index OZF_RESALE_LINES_ALL_U1, is RESALE_LINE_ID, a system-generated surrogate identifier. The index OZF_RESALE_LINES_ALL_U1 is also the documented business-key candidate, though in most deployments the true natural key is a composite of the header and originating system references.

• RESALE_LINE_ID — surrogate primary key; uniquely identifies the resale line.
• RESALE_HEADER_ID — foreign key to OZF_RESALE_HEADERS_ALL, tying the line to its parent transaction.
• RESALE_TRANSFER_TYPE — classifies the product transfer (for example, resale or chargeback movement).
• INVENTORY_ITEM_ID and ORIG_SYSTEM_ITEM_NUMBER — identify the item being resold, internally and as received from the originating system.
• QUANTITY, UOM_CODE, ORIG_SYSTEM_QUANTITY, ORIG_SYSTEM_UOM — quantify the transaction in both normalized and source-system units.
• CURRENCY_CODE, EXCHANGE_RATE, SELLING_PRICE, ACCTD_SELLING_PRICE, PURCHASE_PRICE, ACCTD_PURCHASE_PRICE — capture the commercial and accounting economics of the line.
• BILL_TO_CUST_ACCOUNT_ID, SHIP_TO_CUST_ACCOUNT_ID, SHIP_FROM_CUST_ACCOUNT_ID, SOLD_FROM_CUST_ACCOUNT_ID — reference the trading partners in each party role, joined to HZ_CUST_ACCOUNTS.
• DATE_ORDERED, ORDER_NUMBER, ORDER_LINE_NUMBER, INVOICE_NUMBER, DATE_INVOICED — supply the transaction timeline and external document referencing.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the flexible descriptive flexfield, used for customer-specific extensions.
• ORG_ID, SECURITY_GROUP_ID, DATA_SOURCE_CODE — multi-org and data-security qualifiers.

Common Use Cases and Queries

Typical usage includes reconciliation of supplier chargebacks, reporting on resale margin by item or customer, and validating invoice-to-line matching. A common pattern joins the header and line tables to reconstruct a full resale transaction:

SELECT h.header_number, l.resale_line_id, l.inventory_item_id,
       l.quantity, l.uom_code, l.selling_price, l.purchase_price,
       l.currency_code, l.date_ordered, l.order_number
FROM   ozf.ozf_resale_headers_all h,
       ozf.ozf_resale_lines_all  l
WHERE  h.resale_header_id = l.resale_header_id
AND    l.date_ordered BETWEEN :p_from AND :p_to;

Because the table carries INVENTORY_ITEM_SEGMENT1 through SEGMENT20 in addition to INVENTORY_ITEM_ID, reporting solutions frequently use these denormalized segments to avoid joining to MTL_SYSTEM_ITEMS_VL. The index OZF_RESALE_LINES_ALL_N2 on RESALE_HEADER_ID supports header-driven queries, while OZF_RESALE_LINES_ALL_N1 supports date- and item-range reporting across ORDER_NUMBER and QUANTITY.

Related Objects

OZF_RESALE_LINES_ALL is tightly bound to surrounding tables in the OZF schema and to Trading Community Architecture (HZ) reference data.

• OZF_RESALE_HEADERS_ALL — parent table, joined on RESALE_HEADER_ID.
• HZ_CUST_ACCOUNTS — source of bill-to, ship-to, ship-from, and sold-from account details via BILL_TO_CUST_ACCOUNT_ID, SHIP_TO_CUST_ACCOUNT_ID, SHIP_FROM_CUST_ACCOUNT_ID, and SOLD_FROM_CUST_ACCOUNT_ID.
• HZ_CUST_SITE_USES_ALL — supplies site-use context through BILL_TO_SITE_USE_ID and SHIP_TO_SITE_USE_ID.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID.
• OZF_RESALE_ADJUSTMENTS_ALL — child table referencing RESALE_LINE_ID for line-level adjustments.
• OZF_RESALE_BATCH_LINE_MAPS_ALL — child mapping table referencing RESALE_LINE_ID, used to associate batch processing with individual resale lines.

These relationships confirm that OZF_RESALE_LINES_ALL sits at the center of the Trade Management chargeback ecosystem, bridging batch headers, customer master data, and line-level adjustment records.