Results for “ozf_sd_request_lines_all”

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

Overview

The OZF_SD_REQUEST_LINES_ALL table is a core transaction table within the Oracle Trade Management (OZF) module, responsible for storing ship-and-debit request line information. Ship-and-debit is a trade promotion mechanism in which a vendor grants a distributor or reseller a discount or rebate on products purchased for resale, typically tied to proof of shipment to an end customer. Each row in this table represents a single line item within a ship-and-debit request, capturing the requested discount terms, the products and quantities involved, the approved amounts, and the corresponding vendor validation details. The table is owned by the OZF schema and is present and valid in both Oracle EBS 12.1.1 and 12.2.2.

The documented schema defines 63 columns and identifies the primary key as OZF_SD_REQUEST_LINES_ALL_PK, which is enforced on the REQUEST_LINE_ID column. A unique index, OZF_SD_REQUEST_LINES_ALL_U1, also exists on REQUEST_LINE_ID. In Data Vault modeling terms, the heuristic classification mined from the foreign key structure is standalone; the table may therefore be treated as a candidate hub or satellite in its own right, with limited outward reference relationships. Its only documented foreign key points to FND_SECURITY_GROUPS via SECURITY_GROUP_ID, reflecting the standard multi-org, security-group isolation pattern used throughout the E-Business Suite.

Key Information Stored

The following columns represent the most significant data held on each ship-and-debit request line:

• REQUEST_LINE_ID — the surrogate primary key and unique index column, uniquely identifying each request line.
• REQUEST_HEADER_ID and REQUEST_ID — links back to the parent ship-and-debit request header, establishing the header-to-line hierarchy.
• INVENTORY_ITEM_ID — the inventory item against which the ship-and-debit claim is made.
• PROD_CATG_ID and PRODUCT_CAT_SET_ID — the product category and category set used to qualify eligible products.
• REQUESTED_DISCOUNT_TYPE, REQUESTED_DISCOUNT_VALUE, and REQUESTED_DISCOUNT_CURRENCY — the discount terms originally requested by the claimant.
• APPROVED_DISCOUNT_TYPE, APPROVED_DISCOUNT_VALUE, APPROVED_AMOUNT, and APPROVED_DISCOUNT_CURRENCY — the discount terms ultimately approved.
• MAX_QTY, LIMIT_QTY, and APPROVED_MAX_QTY — quantity thresholds governing the claim.
• VENDOR_APPROVED_FLAG and VENDOR_ITEM_CODE — vendor-side validation and cross-reference of the item.
• SECURITY_GROUP_ID and ORG_ID — multi-org and security-group partitioning keys.
• START_DATE, END_DATE, and DESIGN_WIN — validity and design-window dates for the promotion.

The table also includes standard WHO columns (LAST_UPDATE_DATE, CREATED_BY, and so on), 15 descriptive flexfield attribute columns, currency columns for product cost and end-customer pricing, and a REJECTION_CODE used when a line is denied.

Common Use Cases and Queries

Typical reporting scenarios include reconciling requested versus approved discount amounts, tracking rejected lines by reason code, and auditing vendor approvals. A representative query to retrieve all lines for a given request header, joined to its approved values, would be:

SELECT REQUEST_LINE_ID, INVENTORY_ITEM_ID,
       REQUESTED_DISCOUNT_VALUE, APPROVED_DISCOUNT_VALUE,
       APPROVED_AMOUNT, VENDOR_APPROVED_FLAG
  FROM OZF.OZF_SD_REQUEST_LINES_ALL
 WHERE REQUEST_HEADER_ID = :p_header_id
   AND ORG_ID = :p_org_id;

A second common pattern aggregates approved amounts by item to support accrual and settlement reporting:

SELECT INVENTORY_ITEM_ID, SUM(APPROVED_AMOUNT) APPROVED_TOTAL
  FROM OZF.OZF_SD_REQUEST_LINES_ALL
 GROUP BY INVENTORY_ITEM_ID;

Queries should always filter on ORG_ID and SECURITY_GROUP_ID to respect multi-org access controls, and joins to the header table are required to obtain request-level status.

Related Objects

The table participates in a limited set of documented relationships, with the parent header table and the security-group table being the most significant. Key related objects include:

• OZF_SD_REQUEST_LINES_ALL header table (referenced via REQUEST_HEADER_ID / REQUEST_ID) — the parent ship-and-debit request.
• FND_SECURITY_GROUPS — the only documented foreign key target, joined on SECURITY_GROUP_ID.
• INVENTORY_ITEM_ID references to inventory item master tables used to resolve item descriptions and units.
• Trade Management ship-and-debit request APIs and concurrent programs that populate and process these lines during claim submission and approval.