Search Results ams_ar_deductions_v

Overview

AMS_AR_DEDUCTIONS_V is a marketing-module view in Oracle E-Business Suite (documented for ETRM 12.1.1 and 12.2.2) that exposes trade-promotion deduction claims alongside the corresponding customer account details. The view is classified under the AMS — Marketing product and carries the description "Deductions." Its principal purpose is to consolidate deduction-type claims into a single, reportable structure without requiring the developer or analyst to join the claims and customer-account tables manually.

The view is read-only by design and is oriented toward reporting, integration, and inquiry rather than transactional processing. Because it filters claims to the DEDUCTION class only, it presents a focused, business-relevant subset of the underlying claims data. For implementers searching for the object by its claim_number attribute, this is the primary entry point: CLAIM_NUMBER is directly exposed as the second column of the view. Note that the metadata shows the view as "Not implemented in this database" in the source instance, so it must be validated against the target schema before being referenced.

Underlying Base Objects

The view is defined over two documented base tables:

• OZF_CLAIMS (aliased as C) — the claims table that stores deduction records. The view filters this table with CLAIM_CLASS = 'DEDUCTION', so only deduction claims flow through.
• HZ_CUST_ACCOUNTS (aliased as A) — the Trading Community Architecture (TCA) customer account table, joined on C.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID to enrich each claim with the account name and account number.

No additional base objects are documented. The join is effectively an inner join: a deduction claim without a matching customer account will not appear in the result set. The accompanying ETRM metadata records the owner as unspecified and lists no further referenced base objects, so any extended dependencies should be confirmed directly in the database.

Key Columns

The view projects the full claims column set plus two account columns. Significant columns include:

• CLAIM_ID, CLAIM_NUMBER — the primary key and the human-readable claim identifier; the latter is the attribute most commonly used in searches and lookups.
• STATUS_CODE — the current lifecycle state of the deduction claim.
• CUST_ACCOUNT_ID, ACCOUNT_NAME, ACCOUNT_NUMBER — customer identification sourced from HZ_CUST_ACCOUNTS.
• CURRENCY_CODE, AMOUNT, EXCHANGE_RATE_TYPE, EXCHANGE_RATE, EXCHANGE_RATE_DATE — monetary value and currency-conversion context.
• RECEIPT_ID, RECEIPT_NUMBER — linkage back to the associated receipt in Receivables.
• SOURCE_OBJECT_ID, SOURCE_OBJECT_CLASS, SOURCE_OBJECT_TYPE_ID, SOURCE_OBJECT_NUMBER — the originating object from which the deduction arose.
• SET_OF_BOOKS_ID, ORG_ID — Multi-Org and ledger (Set of Books) context for reporting and access control.
• DEDUCTION_ATTRIBUTE_CATEGORY and DEDUCTION_ATTRIBUTE1 through DEDUCTION_ATTRIBUTE15 — the descriptive flexfield segments used to hold client-specific deduction detail.

Together these columns support both financial analysis (amount, currency, rate) and operational tracking (status, receipt, source object).

Common Use Cases and Queries

Typical uses include reconciliation of deductions against receipts, customer-level reporting of deduction activity, and extracting deduction data for downstream analytics or integration interfaces. All queries should include an ORG_ID or SET_OF_BOOKS_ID predicate where Multi-Org or ledger security applies.

Retrieve a deduction claim by the searched attribute:

SELECT claim_number, cust_account_id, account_name,
       currency_code, amount, status_code
  FROM ams_ar_deductions_v
 WHERE claim_number = :p_claim_number;

List all deductions for a given customer account:

SELECT claim_number, status_code, amount, receipt_number
  FROM ams_ar_deductions_v
 WHERE cust_account_id = :p_cust_account_id
 ORDER BY claim_number;

Summarize deduction amounts by status and currency:

SELECT status_code, currency_code, COUNT(*) claim_count,
       SUM(amount) total_amount
  FROM ams_ar_deductions_v
 WHERE org_id = :p_org_id
 GROUP BY status_code, currency_code;

Because the view restricts results to CLAIM_CLASS = 'DEDUCTION', queries against it never need to repeat that filter, and any claim returning no customer account match will be silently excluded by the underlying join.