Search Results rate_gain_lossformula
Overview
APPS.AR_ARXFXGL_XMLP_PKG is the generated PL/SQL package that backs the Oracle Receivables XML Publisher (BI Publisher) report commonly identified as the Foreign Exchange Gain/Loss report (ARXFXGL). In Oracle EBS 12.1.1 and 12.2.2, XML Publisher concurrent programs are typically built as a PL/SQL package combined with a data template and an RTF layout. The package supplies the SQL data definition, report-level parameters, and the computed formula columns consumed by the layout. Its business purpose is to report foreign currency receipt activity and to calculate realized exchange gain or loss amounts by comparing the base-currency value of the receipt allocation against the base-currency value of the transaction amount applied, across a selected set of books, date range, customer, site, currency, and exchange rate type.
The package is owned by APPS and classified generically as OTHER in the ETRM metadata. It exposes a set of public package variables that hold report parameters such as P_Customer_Id, P_Site_Use_Id, P_From_Receipt_Date, P_To_Receipt_Date, P_Receipt_Currency, P_Rate_Type, P_Conc_Request_Id, P_Set_Of_Books_Id, P_Customer_Number, P_Customer_Name, P_Location, P_Exchange_Rate_Type, Report_Name, Functional_Currency, Set_Of_Books_Name, and Functional_Precision, together with dynamically constructed WHERE clause fragments (Where_Customer, Where_Location, Where_Date, Where_Currency) that default to the neutral predicate AND 1 = 1.
Key Procedures and Functions
The documented interface contains 33 procedures and functions. The principal groups are:
- Report setup and lookup routines: BEFOREREPORT (the BeforeReport trigger), GET_REPORT_NAME, and GET_SOB_DETAILS, which resolve the report title and set of books context, including functional currency and precision.
- Dynamic WHERE clause builders: BUILD_CUSTOMER_DETAILS, BUILD_LOCATION_DETAILS, BUILD_RATE_TYPE_DETAILS, BUILD_RECEIPT_DATE_DETAILS, and BUILD_CURRENCY_DETAILS. Each populates one of the Where_* variables based on the corresponding report parameter.
- Amount and rate formula columns: CF_GAIN_LOSS_ACTUALFO, ALLOCATED_AMOUNT_RATEFORMULA, RATE_ALLOC_RECEIPT_AMT_BASEFOR, RATE_GAIN_LOSSFORMULA, ABSOLUTE_DIFFERENCEFORMULA, ACTUAL_GAINFORMULA, ACTUAL_RATE_LOSSFORMULA, RATE_GAINFORMULA, RATE_LOSSFORMULA, RATE_SYS_CURR_RATE_DFORMULA, ACTUAL_CROSS_CURR_RATE_DFORMUL(A), and SYS_CROSS_CURRENCYFORMULA. These compute base-currency allocations, cross-currency rate determination, and realized gain or loss amounts for each receipt line.
ABSOLUTE_DIFFERENCEFORMULA, the object referenced in the user's search, belongs to this second group. It is a formula function that returns the absolute difference between two numeric inputs, and is used within the gain/loss computation chain so that variance between allocated receipt amounts and applied transaction amounts is expressed as a positive magnitude. The remaining documented members follow the same convention: each *FORMULA or *FO function is bound to a named column in the XML Publisher data template, and is invoked once per row during report generation rather than by external callers.
Tables Accessed
The package reads the following tables through APPS synonyms:
- FND_CONCURRENT_REQUESTS — used to resolve the concurrent request context (P_Conc_Request_Id), including request and report identification.
- FND_CURRENCIES — supplies currency attributes such as precision for receipt and functional currencies.
- GL_DAILY_CONVERSION_TYPES — validates and describes the exchange rate type selected by the user.
- HZ_CUST_ACCOUNTS, HZ_CUST_SITE_USES_ALL, HZ_PARTIES — TCA customer, site use, and party tables used to build customer and location selection lists and to display customer and site names.
The metadata records no updates; the package functions as a read-only reporting component. Receipt, transaction, and allocation data are retrieved by the embedded report query rather than through these lookup tables.
Usage Notes
AR_ARXFXGL_XMLP_PKG is not intended for direct invocation by custom code. It is registered as the PL/SQL package for an XML Publisher concurrent program, and Oracle EBS calls BEFOREREPORT and the formula functions automatically when the program runs. The ETRM metadata shows it is referenced by zero other packages, confirming it sits at the top of its call graph. Customizations should normally be applied to the RTF layout or the XML data template rather than to this package body, because it is a generated artifact (header tag indicates an AR development build) and will be overwritten by patching. When troubleshooting the report in 12.1.1 or 12.2.2, the Where_* variables and the formula functions are the usual focal points, since the dynamic predicates and the gain/loss math determine the rows and amounts returned to the layout.