Search Results ozf_sd_batch_lines_all_n1

Overview

OZF.OZF_SD_BATCH_LINES_ALL is a transactional table in the Oracle Trade Management (formerly Oracle Channel Revenue Management) module of Oracle E-Business Suite, residing in the OZF schema and stored in the APPS_TS_TX_DATA tablespace. It stores the line-level detail of Supplier Ship and Debit (S&D) batches, which are the mechanisms by which a distributor submits claims against a vendor or supplier for price protection, inventory revaluation, or resale price reductions. Each row represents a single line item within a batch header, capturing the shipped item, the customer and ship-to information, the underlying order and invoice references, and the financial amounts that flow through the S&D approval, claim, and settlement lifecycle.

The table is classified as VALID and registered under FND Design Data as OZF.OZF_SD_BATCH_LINES_ALL. From a Data Vault modeling perspective, the heuristic classification is standalone, meaning the table does not decompose naturally into hub, link, and satellite constructs. In practice, the table behaves as a transaction satellite anchored to the batch header, with the BATCH_ID acting as the natural parent relationship and BATCH_LINE_ID serving as the surrogate identifier. This structure supports both batch-level aggregation and line-level reconciliation against utilization and order records.

Key Information Stored

The table contains 101 documented columns. The most operationally significant are listed below.

• BATCH_LINE_ID (NUMBER) — surrogate primary key, enforced by unique index OZF_SD_BATCH_LINES_ALL_U1. This is the single documented business-key candidate.
• OBJECT_VERSION_NUMBER — sequential counter used for optimistic locking when the S&D UI is rendered as HTML, essential for concurrent-update control in OA Framework pages.
• BATCH_ID — foreign key to the batch header; indexed non-uniquely by OZF_SD_BATCH_LINES_ALL_N1.
• BATCH_LINE_NUMBER — user-visible sequence number of the line within its batch.
• UTILIZATION_ID — references OZF_FUNDS_UTILIZED_ALL_B, linking the line to the specific fund consumption record.
• AGREEMENT_NUMBER and AGREEMENT_PRICE (with AGREEMENT_CURRENCY_CODE) — the vendor authorization identifier and agreed claim price.
• ITEM_ID, SHIPPED_QUANTITY_UOM, and QUANTITY_SHIPPED — the shipped product and quantity; ITEM_ID is indexed by OZF_SD_BATCH_LINES_ALL_N2 together with ORG_ID.
• ORDER_HEADER_ID, ORDER_LINE_ID, INVOICE_NUMBER, and INVOICE_LINE_NUMBER — source order and invoice traceability.
• STATUS_CODE — lifecycle state of the line within the approval and claim workflow.
• CLAIM_AMOUNT and CLAIM_AMOUNT_CURRENCY_CODE — the requested claim value and its currency.
• APPROVED_AMOUNT, APPROVED_CURRENCY_CODE, TOTAL_APPROVED_AMT, and TOTAL_APPROVED_QTY — the validated/approved figures used for settlement.
• AMOUNT_REMAINING and ACCTD_AMOUNT_REMAINING — open balances still subject to claim.
• ORG_ID and SECURITY_GROUP_ID — multi-org and data-security partitioning columns; SECURITY_GROUP_ID references FND_SECURITY_GROUPS.
• CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY — standard WHO audit columns.

Common Use Cases and Queries

Typical reporting scenarios include open-claim aging, batch reconciliation against invoices, and utilization-to-claim variance analysis.

• Retrieve all lines for a batch:

  SELECT batch_line_id, batch_line_number, item_id,
         quantity_shipped, claim_amount, status_code
  FROM   ozf.ozf_sd_batch_lines_all
  WHERE  batch_id = :p_batch_id
  ORDER  BY batch_line_number;

• Open approved amounts by organization and item:

  SELECT org_id, item_id, SUM(amount_remaining) remaining
  FROM   ozf.ozf_sd_batch_lines_all
  WHERE  status_code = 'APPROVED'
  GROUP  BY org_id, item_id;

• Join to utilization for fund-consumption reporting on UTILIZATION_ID = BATCH_LINE_ID of OZF_FUNDS_UTILIZED_ALL_B.
• Join to AS_QUOTES_ALL on QUOTE_ID (documented as future use) for quote-driven claims.
• Multi-org filtered queries should always constrain ORG_ID to satisfy MOAC policy.

Related Objects

• OZF.OZF_SD_BATCH_LINES_ALL_U1 — unique index on BATCH_LINE_ID.
• OZF.OZF_FUNDS_UTILIZED_ALL_B — referenced via UTILIZATION_ID.
• OZF_FUNDS_UTILIZED_ALL_TL — translations for the utilization base table.
• AS_QUOTES_ALL — referenced via QUOTE_ID.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID.
• Batch header table joined on BATCH_ID to OZF_SD_BATCHES_ALL (parent).
• OE_ORDER_HEADERS_ALL / OE_ORDER_LINES_ALL — joined on ORDER_HEADER_ID and ORDER_LINE_ID for order traceability.
• AP/AR invoice interfaces for settlement postings of APPROVED_AMOUNT.