OBIA 11g : Amounts Showing As Null Due To LOC_EXCHANGE_RATE Being Null (Doc ID 1982779.1)

Last updated on JULY 11, 2022

Applies to:

Goal

You have run an ELT against an Oracle E-Business Suite R12.1.3 source, In HR Payroll reporting you see that several measures return null instead of the actual amount. Upon investigation in the RPD, you can see that the logical formula for these measures are multiplying the balance amount by the exchange rates. Particularly the Facts - Human Resources - Payroll.Balance Amount (Local Currency) uses the formula:-

"Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_PAY_RUN_BAL_DTL_F"."BALANCE_VALUE" * "Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_PAY_RUN_BAL_DTL_F"."LOC_EXCHANGE_RATE".

Having looked at the underlying W_PAY_RUN_BAL_DTL_F table, you can see that the LOC_EXCHANGE_RATE is null (as are the three global exchange rates), which is why the amount being returned logically is also null.

The table W_GLOBAL_EXXCH_RATE_G has no records, and additionally the E-Business Suite table GL_DAILY_RATES has no records.

This issue is similar to <Bug 17418723> W_AR_XACT_F GLOBAL EXCHANGE RATES COLUMNS ARE POPULATING AS NULL. However the bug suggests the insertion of a dummy record into the E-Business Suite source table gl_daily_rates.

Fixed version: 11.1.1.8.0

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.