We are trying to pull report data on project financial information from NBI_PROJECT_CURRENT_FACTS, specifically for COST_ETC_TOTAL, COST_BASE_TOTAL and COST_ACT_TOTAL, but there is no data in these fields. What do we need to do to get this data populated?
The data for COST_ETC_TOTAL, COST_BASE_TOTAL and COST_ACT_TOTAL columns in NBI_PROJECT_CURRENT_FACTS are calculated as follows:
This is the total actual cost of all the transactions in the PPA_WIP table for the project. The data is actually the sum of the TOTALCOST column from PPA_WIP_VALUES, for rows where CURRENCY_TYPE is 'Billing'. This value is then converted to the Datamart currency using the rate of exchange at the time Datamart extraction is executed. As long as you have actual transactions in PPA_WIP and PPA_WIP_VALUES and the correct exchange rates are defined, COST_ACT_TOTAL should show you the sum of the actual transactions in this Datamart table.
In a multi-currency system, check the following configuration pages to ensure you have the correct exchange rates defined:
- Administration, Datamart Settings: Make a note of the Currency Code defined here
- Project, Financial Properties page: Note the Billing Currency code defined on the project and the 'Exchange Rate Type' for EACH of the resource types : Labor, Materials, Equipment and Expense. The valid values for 'Exchange Rate Type' are Average, Fixed, Spot
- Administration, Finance: Setup, Exchange Rates: Check the exchange rates exist for BOTH directions (to/from) for each currency for the appropriate 'Exchange Rate Type' and it is active to cover the current date.
- Datamart Currency Code = USD
- Project Financial Properties, Billing Code = GBP
- Project Financial Properties, Exchange Rate Type = Average for all 4 resource types
- Make sure there is an Exchange Rate row for GBP to USD for Average that covers the current date
- Make sure there is an Exchange Rate row for USD to GBP for Average that covers the current date
In a multi-currency system, if there are no Exchange Rates active for the current date, the Datamart Extraction job will fail with the following message:
Error PMD application error java.sql.SQLException: [CAClarity][Oracle JDBC Driver][Oracle]
ORA-20000: Error in NBI_EXTRACT_SP -Calling NBI_EXTR_PCF_SP:
ORA-20000: Error in NBI_EXTR_PCF_SP - Extracting NBI_PROJECT_CURRENT_FACTS:
ORA-20000: Error in NBI_PROJECT_CURRENT_FACTS_SP - 5000000 - Applying the exchange rate on totalamount/cost/amountremaining:
ORA-01403: no data found
ORA-01403: no data found
This is the sum of the product of two columns, BASE_QTY and COST_RATE, from NBI_PROJECT_RES_TASK_FACTS for all the resources who are on this project. This value is then converted to the Datamart currency using the rate of exchange at the time Datamart extraction is executed.
This is calculated similar to the COST_BASE_TOTAL, except that ETC_QTY is used instead of BASE_QTY.
Reference TEC444494 : What is the difference between the use of 'Actual Cost' vs. 'Standard Cost'?
Reference TEC444466 : TECHNOTE: Worksheet Example for calculating PPA_WIP_VALUES?