Search Results dpp_currency_conversion_type

Overview

APPS.DPP_TXN_DASHBOARD_V is a reporting view in Oracle E-Business Suite that consolidates transaction-level information from the Oracle ETRM (Enterprise Trade and Transportation Management) / DPP (Duty, Preference, and Product compliance) module. It presents a denormalized, user-friendly projection of supplier claim transactions, combining transaction header data with supplier identities, operating unit names, lookup-code translations, and aggregated monetary amounts. The view is intended primarily to drive dashboard-style reporting — hence its name — where users require a single row per transaction header without needing to join the DPP transactional schema manually.

The view is owned by APPS and is queryable by any user or concurrent program with the appropriate APPS schema grants. Because it joins lookups and organizational units, it is well suited for OBIEE, Discoverer, or BI Publisher reports that need human-readable status and operating unit descriptions rather than raw lookup codes and IDs.

Underlying Base Objects

The documented base objects referenced by this view are:

• DPP_TRANSACTION_HEADERS (synonym) — the primary driving table, supplying transaction number, reference document, source, status, currency, creation and effective dates, org context, and vendor reference.
• DPP_TRANSACTION_LINES_ALL (synonym) — sourced via a scalar subquery to sum CLAIM_AMOUNT per transaction header and org, producing CLAIMED_AMOUNT.
• DPP_CUSTOMER_CLAIMS_ALL (synonym) — sourced via a scalar subquery that aggregates converted customer claim amounts into CUST_CLAIM_AMOUNT.
• AP_SUPPLIERS (synonym) — provides VENDOR_NAME linked by VENDOR_ID.
• FND_LOOKUP_VALUES_VL (view) — supplies the lookup MEANING for TRANSACTION_STATUS against lookup type DPP_TRANSACTION_STATUSES, constrained by effective dates.
• HR_ALL_ORGANIZATION_UNITS (synonym) — provides the operating unit NAME linked on ORGANIZATION_ID.
• FND_PROFILE (package) — referenced to retrieve the DPP_CURRENCY_CONVERSION_TYPE profile value for currency conversion.
• GL_CURRENCY_API (package) — used through CONVERT_CLOSEST_AMOUNT_SQL to translate customer claim amounts into the transaction currency.

Key Columns

• OPERATING_UNIT — the organization unit name from HR_ALL_ORGANIZATION_UNITS.
• REF_DOCUMENT_NUMBER — the referenced source document for the transaction.
• SUPPLIER_NAME — the vendor name from AP_SUPPLIERS.
• TRANSACTION_HEADER_ID — primary key of the underlying transaction header.
• TRANSACTION_NUMBER — user-facing transaction identifier.
• CREATION_DATE / EFFECTIVE_START_DATE — audit and effective dating information.
• TRANSACTION_SOURCE — origin of the transaction.
• TRANSACTION_STATUS — the raw lookup code, complemented by MEANING, the translated status description from lookup type DPP_TRANSACTION_STATUSES.
• CLAIMED_AMOUNT — sum of claim amounts from DPP_TRANSACTION_LINES_ALL.
• ONHAND_INV_CURRENCY / CLAIM_CURRENCY — both expose DAH.TRX_CURRENCY.
• CUST_CLAIM_AMOUNT — aggregated customer claim amounts converted into the transaction currency.

Common Use Cases and Queries

The view is typically used to list open or completed claim transactions by operating unit, supplier, or status. A sample query filtering for a specific status meaning might read:

SELECT operating_unit, transaction_number, supplier_name,
       meaning, claimed_amount, cust_claim_amount
  FROM apps.dpp_txn_dashboard_v
 WHERE meaning = 'Approved'
   AND operating_unit = :p_operating_unit
 ORDER BY creation_date DESC;

Because the view includes the outstanding claim totals and the converted customer claim totals, it also supports reconciliation reporting — for example, comparing CLAIMED_AMOUNT against CUST_CLAIM_AMOUNT per transaction. Reporting teams commonly join it back to DPP_TRANSACTION_HEADERS or AP_SUPPLIERS for drill-down, though the view already exposes most of the attributes required for a dashboard. Filtering by EFFECTIVE_START_DATE enables period-based trend reporting, and grouping by MEANING yields status distribution summaries useful for workflow monitoring.