Search Results functional_amount

Overview

SO_FC_RETURNS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, defined within the Order Entry (OE) product family. Its documented description is "Cash Management returns outflow," which identifies its purpose as a reporting interface that feeds expected cash outflow amounts arising from returned goods processed through Order Management. The view consolidates return line activity into a single monetary amount per order line, denominated in the transaction currency and expressed as a negative value to reflect outflow direction.

The view is marked VALID in ETRM metadata and is available in both 12.1.1 and 12.2.2 releases. Because it is a view rather than a table, it contains no stored data; all values are derived at query time from the Return Merchandise Authorization (RMA) order data held in the Order Management base tables. It is commonly surfaced through Cash Management forecasting and cash-flow reporting, where expected receipts and disbursements are aggregated by date and currency.

Underlying Base Objects

SO_FC_RETURNS_V is defined over two synonyms in the APPS schema, both resolving to Order Management base tables:

• SO_HEADERS_ALL — the order header table, supplying order category, open status, currency, conversion rate, operating unit, and the order/requested dates.
• SO_LINES_ALL — the order line table, supplying ordered, cancelled, and invoiced quantities together with selling price and line-level requested date.

The two objects are joined on H.HEADER_ID = L.HEADER_ID. The view restricts rows to open orders (H.OPEN_FLAG = 'Y'), RMA order category (H.ORDER_CATEGORY = 'RMA'), header attribute S1 equal to 1 (NVL(H.S1,15) = 1), and line attribute S5 outside the values 8 and 9 (NVL(L.S5,18) NOT IN (9,8)). These descriptor flexfield conditions constrain the result set to return lines relevant to Cash Management outflow forecasting.

Key Columns

• AMOUNT — the expected outflow in transaction currency. Computed as -1 * (ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0) - NVL(INVOICED_QUANTITY,0)) * NVL(SELLING_PRICE,0). The negative sign denotes an outflow.
• DATE_RETURNED — derived from NVL(H.DATE_ORDERED, SYSDATE); the order date, defaulting to the current date when null.
• DATE_REQUESTED — derived from NVL(H.DATE_REQUESTED_CURRENT, NVL(L.DATE_REQUESTED_CURRENT, SYSDATE)); the requested date, resolved from the header first, then the line, then the current date.
• CURRENCY_CODE — the transaction currency from H.CURRENCY_CODE.
• ORG_ID — the operating unit identifier from H.ORG_ID, supporting multi-org reporting.
• FUNCTIONAL_AMOUNT — the transaction amount converted to the functional (ledger) currency, computed as AMOUNT * NVL(H.CONVERSION_RATE, 1). This is the column most frequently referenced when users search for "functional_amount," since it presents the outflow in the reporting set of books currency.

Common Use Cases and Queries

The primary use case is cash-flow forecasting, where functional-currency outflow amounts are grouped by currency, operating unit, and expected date. A typical query returning functional amounts is:

SELECT ORG_ID, CURRENCY_CODE, DATE_REQUESTED, FUNCTIONAL_AMOUNT
FROM APPS.SO_FC_RETURNS_V
WHERE ORG_ID = :p_org_id
ORDER BY DATE_REQUESTED;

Because the view already applies the RMA and S1/S5 filters, callers need not re-apply them. Analysts commonly aggregate results with SUM(FUNCTIONAL_AMOUNT) over a date range to reconcile expected return outflows against Cash Management forecast templates contained in CE (Cash Management). When transaction-currency analysis is required, AMOUNT is selected instead of FUNCTIONAL_AMOUNT, and the two are reconciled using the header conversion rate embedded in the functional calculation.