Search Results ozf_sd_batch_lines_all_pk

Overview

OZF_SD_BATCH_LINES_ALL is a Trade Management (OZF) transactional table that stores the individual line-level records belonging to a Supplier Ship and Debit (SD) batch. In Oracle EBS 12.1.1 and 12.2.2, Supplier Ship and Debit is the Trade Management process through which a distributor or reseller claims reimbursement from a supplier for price protection, ship-and-debit agreements, and similar incentive programs. Where the batch header captures the submission as a whole, this table carries the detail: the specific order, invoice, quote, item, quantities, prices, and claim amounts that make up each batch line, together with its workflow status and approval state. The table is owned by the OZF schema, is documented as VALID, and contains 101 columns in the 12.2.2 ETRM data model.

From a heuristic Data Vault modeling perspective, the FK structure mined from the metadata classifies this object as standalone. It carries a surrogate primary key and references parent dimension-like tables rather than being embedded within a hub-and-satellite chain, so it is best treated as an independent detail or transaction entity keyed by its own identifier.

Key Information Stored

The surrogate primary key is BATCH_LINE_ID, defined by the constraint OZF_SD_BATCH_LINES_ALL_PK. A unique index, OZF_SD_BATCH_LINES_ALL_U1, also exists on BATCH_LINE_ID, making it the only documented business-key candidate. The most operationally significant columns include:

• BATCH_ID – the parent Supplier Ship and Debit batch header to which the line belongs.
• BATCH_LINE_NUMBER – the line sequence within the batch.
• UTILIZATION_ID – links the line to the funds utilization record (OZF_FUNDS_UTILIZED_ALL_B) against which the claim draws.
• QUOTE_ID and QUOTE_LINE_NUMBER – the source quote (AS_QUOTES_ALL) and its line reference.
• ORDER_HEADER_ID, ORDER_LINE_ID, ORDER_DATE, ORDER_SOURCE – the originating order context for the claimed transaction.
• INVOICE_NUMBER, INVOICE_LINE_NUMBER – the invoice evidence supporting the claim.
• ITEM_ID and SHIPPED_QUANTITY_UOM – the product and unit of measure.
• QUANTITY_SHIPPED, QUANTITY_APPROVED – shipped versus approved quantities.
• CLAIM_AMOUNT, APPROVED_AMOUNT, ORIGINAL_CLAIM_AMOUNT – the claimed, approved, and original amounts, with companion currency codes.
• STATUS_CODE and COMPLETE_FLAG – workflow status and completion indicator.
• SHIP_TO_ORG_ID, SOLD_TO_CUSTOMER_ID, END_CUSTOMER_ID – the trading partners on the line.
• ORG_ID and SECURITY_GROUP_ID – multi-org and security context, the latter referencing FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER – optimistic locking for concurrent updates.

The table also includes 30 descriptive ATTRIBUTE columns (ATTRIBUTE_CATEGORY plus ATTRIBUTE1–ATTRIBUTE30) for extensibility, and WHO/audit columns such as CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY.

Common Use Cases and Queries

The table is the principal source for ship-and-debit claim reporting, batch reconciliation, and fund utilization analysis. Typical queries retrieve all lines for a batch, summarize claim versus approved amounts, or reconcile claims to their fund utilization records.

• Retrieving a batch's lines: SELECT batch_line_id, batch_line_number, item_id, quantity_shipped, claim_amount, status_code FROM ozf_sd_batch_lines_all WHERE batch_id = :batch_id ORDER BY batch_line_number;
• Reconciling claims to fund utilization: join BATCH_LINE_ID-based rows to OZF_FUNDS_UTILIZED_ALL_B on UTILIZATION_ID to confirm amounts remaining (AMOUNT_REMAINING, ACCTD_AMOUNT_REMAINING).
• Pipeline reporting: filter on STATUS_CODE or COMPLETE_FLAG to report approved versus open lines, aggregating APPROVED_AMOUNT by ORG_ID.
• Traceability: join to AS_QUOTES_ALL on QUOTE_ID and to order tables on ORDER_HEADER_ID/ORDER_LINE_ID to trace a claim back to its sales document.

Related Objects

The documented foreign keys and parent relationships identify the following significant related objects:

• OZF_FUNDS_UTILIZED_ALL_B – joined via UTILIZATION_ID; the accrual/utilization record the claim consumes.
• AS_QUOTES_ALL – joined via QUOTE_ID; the quote header underlying the transaction.
• FND_SECURITY_GROUPS – joined via SECURITY_GROUP_ID; supplies the row-level security grouping.
• Batch header table (OZF_SD_BATCHES_ALL and related _B/_TL objects) – the parent batch referenced through BATCH_ID.
• Order and invoice tables (OE_ORDER_HEADERS_ALL / OE_ORDER_LINES_ALL) – referenced through ORDER_HEADER_ID and ORDER_LINE_ID for order context.
• OZF_SD_BATCH_LINES_ALL_PK / _U1 indexes – enforce uniqueness of BATCH_LINE_ID.