Search Results ptd_balance




Overview
Explore in AI Chat

Overview

APPS.AMS_DM_PARTY_PROFIT_V is a reporting view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the Oracle Marketing (AMS) schema and exposed through the APPS synonym layer. It belongs to the Oracle Trade Management / Enterprise Trade Reporting and Management (ETRM) analytics domain, where it functions as the presentation layer over the customer profit and profitability data warehouse. The view presents party-level (customer-level) profit metrics in a denormalized, reporting-friendly column layout that maps internal warehouse column names to standardized analytical column aliases.

Its principal role is to serve as the canonical source for customer profitability reporting, dashboard visualizations, and downstream extracts. The view reshapes a single physical profitability record into a wide, fixed-slot structure in which revenue, expense, profit, contribution after capital charge (CACC), balance, and period-balance buckets are repeated across five positional slots. This layout is designed to allow BI Publisher reports, Oracle Business Intelligence (OBIEE) models, and custom PL/SQL extracts to consume a uniform schema regardless of the underlying granularity. It is read-only in nature; no DML is performed against the view.

Underlying Base Objects

The view is defined over a single base object, fem_customer_profit, an ETRM/analytics fact-style table that stores customer profitability measures. Unlike many EBS views that join a header and lines table, AMS_DM_PARTY_PROFIT_V is a straightforward projection and aliasing of columns from fem_customer_profit, with no documented joins to additional base tables. Every column in the view traces directly back to a column in that table, and where the reporting schema requires five period slots (for example, ytd1 through ytd5), the same source column is repeated with identical values across all slots. This design implies that the view is intended to honor a positional contract expected by its consumers rather than to project genuinely distinct period values from the base table.

Key Columns

  • party_id — mapped from customer_id; the party or customer identifier used for joining to customer and party master data.
  • profit, profit_pct — profit contribution and profit percentile, mapped from profit_contrib and profit_percentile.
  • relationship_expense — relationship expense associated with the party.
  • total_equity, total_roe — allocated equity and return on equity, mapped from allocated_equity and return_on_equity.
  • total_gross_contrib — gross contribution, sourced from numeric_measure01.
  • contrib_after_cptl_chg — contribution after capital change, the basis for the CACC (cacc1–cacc5, cacc_total) columns.
  • partner_value_index — value index mapped from value_index1.
  • iso_currency_cd — currency of the reported measures, mapped from currency_code.
  • revenue1–revenue5, revenue_total — revenue buckets sourced from numeric_measure01 through numeric_measure05 and total_revenue.
  • expense1–expense5, expense_total — expense buckets and total expense.
  • profit1–profit5, profit_total — profit buckets and total profit.
  • balance1–balance5 — net book balance, mapped from cur_net_book_bal.
  • accounts1–accounts5 — primary accounts, mapped from pri_accounts.
  • ytd1–ytd5 — year-to-date balances, mapped from ptd_balance. This mapping is the object matched by the search term "ptd_balance": the source column ptd_balance is exposed five times as ytd1 through ytd5.
  • ltd1–ltd5 — life-to-date balances, mapped from ltd_balance.
  • transaction1–5, ratio1–5, value1–5 — placeholder slots populated with the literal 1.

Common Use Cases and Queries

The view is typically queried for customer profitability dashboards, party-level scorecards, and period-balance extracts. A common pattern selects the party, profit, and the year-to-date balance bucket, joining back to customer master data for names and classifications.

Example — customer profit and YTD balance:

  • SELECT party_id, profit, profit_pct, total_roe, total_equity, iso_currency_cd, ytd1, ltd1 FROM apps.ams_dm_party_profit_v WHERE iso_currency_cd = 'USD' ORDER BY profit DESC;

Example — revenue, expense, and profit totals by party:

  • SELECT party_id, revenue_total, expense_total, profit_total, contrib_after_cptl_chg FROM apps.ams_dm_party_profit_v WHERE revenue_total > 0;

Because ytd1 through ytd5 and ltd1 through ltd5 repeat the same ptd_balance and ltd_balance source values, queries that compare "period" slots will return identical figures. Analysts should treat these positional columns as schema-compatibility slots and obtain genuine time-series comparisons from the base warehouse or ETRM period tables instead.