Results for “ozf_claim_lines_v”

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

Overview

OZF_CLAIM_LINES_V is an APPS-owned reporting view in the Oracle Trade Management (OZF) module, a component of Oracle E-Business Suite releases 12.1.1 and 12.2.2. It presents a denormalized, business-friendly projection of claim line data drawn primarily from the OZF_CLAIM_LINES table, enriched with lookup meanings, system parameter context, and resource names. Claim lines represent the individual accrual, settlement, or payment entries associated with a customer claim — the mechanism by which Trade Management tracks vendor-funded promotional activity, deductions, and reimbursements.

The view exists to shield reporting tools, integration interfaces, and custom extensions from the normalization of the base claim line table. Rather than requiring callers to join to lookups and resource tables manually, OZF_CLAIM_LINES_V resolves the payment method description, the approver's resource name, and the settler's resource name inline. This is particularly valuable for Oracle Discoverer workbooks, BI Publisher reports, and OIC/legacy interfaces that need claim-level financial and settlement detail without replicating the module's join logic. The view is documented as VALID in the ETRM metadata.

Underlying Base Objects

The documented metadata identifies four referenced objects: OZF_CLAIM_LINES (synonym to the base claim line table), JTF_RS_RESOURCE_EXTNS (synonym, joined twice), OZF_LOOKUPS (view), and OZF_SYS_PARAMETERS (synonym).

The view text confirms the join structure: OZF_CLAIM_LINES is the driving table, aliased OCL. OZF_SYS_PARAMETERS (OSP) is joined on SET_OF_BOOKS_ID to restrict results to the active ledger context. AMS_LOOKUPS (LK_PM) is an outer join on lookup type 'AMS_PAYMENT_METHOD' to translate the payment method code into a meaningful description. JTF_RS_RESOURCE_EXTNS is outer-joined twice — once via APPROVED_BY (JRE1) and once via SETTLED_BY (JRE2) — to resolve the resource IDs of the approving and settling users to source names. All joins except the system parameters join use the Oracle outer-join syntax, so a claim line is returned even when its payment method, approver, or settler is not yet populated.

Key Columns

• CLAIM_LINE_ID — Primary key and the natural join target back to OZF_CLAIM_LINES.
• CLAIM_ID, LINE_NUMBER, SPLIT_FROM_CLAIM_LINE_ID — Parent claim reference, sequencing, and lineage when a line results from a split.
• AMOUNT, CLAIM_CURRENCY_AMOUNT, ACCTD_AMOUNT, CURRENCY_CODE — Entered amount, claim-currency equivalent, and the accounted (functional currency) amount, supporting multi-currency reconciliation.
• EXCHANGE_RATE_TYPE, EXCHANGE_RATE_DATE, EXCHANGE_RATE — Foreign exchange attributes applied to the line.
• VOUCHER_ID, VOUCHER_NUMBER — The Payables voucher reference created when the claim line is settled. This is the column most relevant to the user's search term: it links a Trade Management claim line to its corresponding AP invoice/voucher in Oracle Payables.
• PAYMENT_REFERENCE_ID, PAYMENT_REFERENCE_NUMBER, PAYMENT_REFERENCE_DATE — External payment instrument details.
• PAYMENT_STATUS, PAYMENT_METHOD, MEANING — Settlement state and the translated payment method lookup description.
• APPROVED_FLAG, APPROVED_DATE, APPROVED_BY, SOURCE_NAME (approver) — Approval audit trail with the approver's resolved resource name.
• SETTLED_DATE, SETTLED_BY, SOURCE_NAME (settler) — Settlement audit trail with the settler's resolved resource name.
• SET_OF_BOOKS_ID, ORG_ID — Ledger and operating unit security context.
• ATTRIBUTE1–15, ATTRIBUTE_CATEGORY — Descriptive flexfield columns for client-specific extensions.

Common Use Cases and Queries

The most frequent use of this view is reconciling settled Trade Management claims to their Payables vouchers, and reporting on outstanding claims by payment status or approver. A typical query searching by voucher_number is:

SELECT claim_line_id, claim_id, line_number, amount,
       currency_code, payment_status, voucher_id, voucher_number,
       approved_date, settled_date, source_name
FROM   apps.ozf_claim_lines_v
WHERE  voucher_number = :p_voucher_number;

Because the approver and settler names are unresolved onto a single SOURCE_NAME column from two aliases, reporting queries typically disambiguate by ordering or by selecting the columns positionally, or by wrapping the view in an inline query with explicit aliases. A common pattern for a settlement-detail report is:

SELECT claim_id, line_number, amount, voucher_number,
       payment_method, meaning payment_method_meaning,
       approved_date, settled_date
FROM   apps.ozf_claim_lines_v
WHERE  payment_status = 'SETTLED'
AND    settled_date BETWEEN :from_date AND :to_date
AND    org_id = :org_id;

Integration developers also use the view as a read-only source for extracting claim line data into external systems, leveraging VOUCHER_NUMBER as the cross-reference key to Oracle Payables while using CLAIM_LINE_ID and OBJECT_VERSION_NUMBER for downstream concurrency control.