Search Results total_open_amount_prim

Overview

FII_AR_NET_REC_AGRT_MV_P_V is an APPS-owned, VALID database view belonging to the FII (Financial Intelligence) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes the contents of the materialized view FII_AR_NET_REC_AGRT_MV, presenting net receivables and receipts agreement metrics at a granular, multi-dimensional level. The "_P_V" naming convention indicates a presentation-layer view, typically consumed by Oracle Financial Intelligence / Daily Business Intelligence (DBI) dashboards, Discoverer workbooks, or XML Publisher reports rather than by transactional processing code.

The view is a straight projection over the materialized view, meaning no joins, filters, or transformations are applied — it simply surfaces a stable, named interface so reporting tools and custom code do not depend directly on the materialized view. Because the underlying object is a materialized view, the data is pre-aggregated and refreshed on a schedule, which makes the view suitable for high-volume analytical queries across the Receivables sub-ledger.

Underlying Base Objects

The documented base object is FII_AR_NET_REC_AGRT_MV, an FII materialized view. The ETRM metadata for this object lists no other referenced base objects. The materialized view itself aggregates from Oracle Receivables transactional tables such as RA_CUSTOMER_TRX_ALL, AR_CASH_RECEIPTS_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL, together with FII time and party dimensions (FII_TIME_* and trading-partner/party hierarchies).

Data currency therefore depends entirely on the refresh schedule defined for FII_AR_NET_REC_AGRT_MV. Users should be aware that the view reflects the last refresh point and not real-time transaction state.

Key Columns

• TIME_ID, PERIOD_TYPE_ID: The time dimension and period grain (e.g., day, week, month) for each aggregation row.
• PARENT_PARTY_ID, PARTY_ID, CUST_ACCOUNT_ID: Party and customer hierarchy keys, allowing drill-down from a parent trading partner to a specific customer account.
• COLLECTOR_ID, ORG_ID: Collector (credit/collections agent) and operating unit identifiers for organizational reporting.
• HEADER_FILTER_DATE: Date used to filter report headers, aligning the row to a reporting snapshot.
• CURRENT_BUCKET_1_AMOUNT_PRIM / _COUNT ... CURRENT_BUCKET_3: Aging buckets for currently due amounts, with amount and transaction count.
• PAST_DUE_BUCKET_1_AMOUNT_PRIM ... PAST_DUE_BUCKET_7_AMOUNT_PRIM (with matching _COUNT columns): Seven past-due aging buckets, each with amount and count.
• CURRENT_OPEN_AMOUNT_PRIM, CURRENT_OPEN_COUNT, PAST_DUE_OPEN_AMOUNT_PRIM, PAST_DUE_COUNT, TOTAL_OPEN_AMOUNT_PRIM, TOTAL_OPEN_COUNT: Open receivable balances and item counts.
• WTD_TERMS_OUT_OPEN_NUM_PRIM, WTD_TERMS_OUT_CURRENT_NUM_PRIM, WTD_DDSO_DUE_NUM_PRIM: Weighted terms-out and Days Sales Outstanding numerator components.
• INV_AMOUNT_PRIM, DM_AMOUNT_PRIM, CB_AMOUNT_PRIM, BR_AMOUNT_PRIM, DEP_AMOUNT_PRIM: Transaction-type amounts (invoice, debit memo, chargeback, bad debt, deposit).
• UNAPP_DEP_AMOUNT_PRIM, ON_ACCOUNT_CREDIT_AMOUNT_PRIM, ON_ACCOUNT_CASH_AMOUNT_PRIM, PREPAYMENT_AMOUNT_PRIM, CLAIM_AMOUNT_PRIM: Unapplied, on-account, prepayment, and claim amounts.
• APP_AMOUNT_PRIM, APP_COUNT, REV_AMOUNT_PRIM, REV_COUNT, TOTAL_RECEIPT_AMOUNT_PRIM, TOTAL_RECEIPT_COUNT: Application, reversal, and receipt totals.
• EARNED_DISCOUNT_AMOUNT_PRIM, UNEARNED_DISCOUNT_AMOUNT_PRIM: Discount amounts earned and unearned.
• AVG_DD_NUM_PRIM, WTD_DAYS_PAID_NUM_PRIM, WTD_TERMS_PAID_NUM_PRIM, BILLED_AMOUNT_PRIM, BILLING_ACTIVITY_AMOUNT_PRIM, BILLING_ACTIVITY_COUNT: Days-to-pay, billed amount, and billing activity metrics.
• GID, UMARKER: Internal FII identifiers used for change tracking and incremental refresh.

Common Use Cases and Queries

Typical uses include Receivables aging analysis, DSO computation, collector performance reporting, and customer exposure dashboards in Financial Intelligence.

SELECT TIME_ID,
       CUST_ACCOUNT_ID,
       CURRENT_OPEN_AMOUNT_PRIM,
       PAST_DUE_OPEN_AMOUNT_PRIM,
       TOTAL_OPEN_AMOUNT_PRIM
FROM   APPS.FII_AR_NET_REC_AGRT_MV_P_V
WHERE  ORG_ID = :p_org_id
  AND  PERIOD_TYPE_ID = :p_period_type
  AND  TIME_ID = :p_time_id;

Aging detail by bucket for a given collector:

SELECT COLLECTOR_ID,
       SUM(CURRENT_BUCKET_1_AMOUNT_PRIM) AS CUR_B1,
       SUM(PAST_DUE_BUCKET_1_AMOUNT_PRIM) AS PD_B1,
       SUM(PAST_DUE_BUCKET_2_AMOUNT_PRIM) AS PD_B2
FROM   APPS.FII_AR_NET_REC_AGRT_MV_P_V
WHERE  TIME_ID = :p_time_id
GROUP  BY COLLECTOR_ID;

DSO numerator extraction:

SELECT TIME_ID,
       WTD_DDSO_DUE_NUM_PRIM,
       TOTAL_OPEN_AMOUNT_PRIM
FROM   APPS.FII_AR_NET_REC_AGRT_MV_P_V
WHERE  PERIOD_TYPE_ID = :p_period_type;

Report logic should join this view to FII time and party dimension views where descriptive attributes (customer name, period name) are required, since the view exposes only key identifiers and measures.