Results for “total_pastdue”

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

Overview

FII_AR_PASTDUE_REC_SUM_PMV is an internal database view owned by the APPS schema within the Financial Intelligence (FII) product family of Oracle E-Business Suite. It is documented as VALID in both ETRM 12.1.1 and 12.2.2 metadata. The view is specifically designed to support Risk Indicator Portlets — dashboard components that surface accounts receivable aging and past-due exposure to collections and credit management users. It acts as a denormalized, presentation-oriented layer that reshapes open installment balances into several named aging buckets and a past-due total, rather than exposing raw transactional rows.

The view embodies a pivoted summary pattern: a single base aging attribute (AGE_BUCKET) on the underlying receivable row is decoded into multiple discrete output columns, allowing downstream portlet queries to reference pre-classified buckets directly. This design shifts the bucketing logic out of the reporting layer and into a reusable view, which is a common FII technique for stabilizing portlet SQL across releases.

Underlying Base Objects

The view is defined over exactly one documented base object: FII_AR_OPEN_INSTALLMENT_F, an FII fact table storing open (unpaid) receivable installments, including customer, operating unit, and set of books surrogate keys, receivable amount in functional currency (RECEIVABLE_G), functional and invoice currency codes, customer name, and the AGE_BUCKET classification. No additional base tables are documented in the metadata.

The SELECT pivots FII_AR_OPEN_INSTALLMENT_F as follows: BUCKET1 returns RECEIVABLE_G only when AGE_BUCKET equals 1, otherwise 0; BUCKET2 returns RECEIVABLE_G only when AGE_BUCKET equals 4, otherwise 0; and the past-due column (mapped to TOTAL_PASTDUE) returns RECEIVABLE_G only when AGE_BUCKET equals 5. All other buckets fall through to zero. This decode pattern means each installment row yields a single non-zero bucket value, so aggregating across rows produces the bucket totals.

Note that the partition of AGE_BUCKET values used here (1, 4, and 5) is specific to this view; not all bucket values in the underlying fact table are represented, which is a design characteristic of the portlet it supports rather than a defect.

Key Columns

• SOB_ID — Set of Books identifier, sourced from SET_OF_BOOKS_FK_KEY; correlates results to a specific ledger.
• ORG_ID — Operating unit identifier from OPERATING_UNIT_FK_KEY; enforces multi-org data segregation.
• CUSTOMER_ID — Surrogate key of the customer from CUSTOMER_FK_KEY.
• CUSTOMER_NAME — Descriptive customer name carried directly from the fact table.
• TOTAL_PASTDUE — Receivable amount classified into the past-due bucket; this is the column most often searched for under the term total_pastdue.
• BUCKET1 — Receivable amount classified into aging bucket 1.
• BUCKET2 — Receivable amount classified into aging bucket 4.
• FUNCTIONAL_CURRENCY — Currency of the functional ledger; RECEIVABLE_G amounts are expressed in this currency.
• INVOICE_CURRENCY — Currency of the originating invoice, useful for foreign-currency exposure analysis.
• TOTAL — Aggregate receivable amount for the row.

Common Use Cases and Queries

The principal scenario is driving a Risk Indicator Portlet that displays past-due receivables by customer and operating unit. A typical query aggregates the bucket columns:

• SELECT sob_id, org_id, customer_id, customer_name, SUM(total_pastdue) pastdue_total FROM apps.fii_ar_pastdue_rec_sum_pmv GROUP BY sob_id, org_id, customer_id, customer_name ORDER BY pastdue_total DESC;
• SELECT org_id, functional_currency, SUM(bucket1) b1, SUM(bucket2) b2, SUM(total_pastdue) pastdue FROM apps.fii_ar_pastdue_rec_sum_pmv GROUP BY org_id, functional_currency;
• SELECT customer_name, total, total_pastdue FROM apps.fii_ar_pastdue_rec_sum_pmv WHERE total_pastdue > 0 AND org_id = :p_org_id;

Because TOTAL_PASTDUE is zero for any row whose AGE_BUCKET is not 5, filtering on total_pastdue > 0 is the standard method for isolating true past-due exposure. All queries should be constrained by ORG_ID and SOB_ID to preserve multi-org and ledger separation.