The install ran very smoothly and once complete the first full ETL was run. During initial testing of the system it was found that no data was being returned for any of the AP Invoice Aging reports.
Checking the Report
With my roots as a DBA, I generally find the best starting point when trying to diagnose a report that returns no data is to check the Data Warehouse. For example, is there fact data to report on, or is it just the report definition that’s being too selective? Or is it something more fundamental?
To find the physical table that the report is based on, I used the RPD and traced back from the Presentation layer to the Physical. In our case the presentation fact was ‘Fact – AP Balance Aging’:
This was checked in the RPD to see what physical table was being accessed:
So the report is being driven from W_AP_AGING_INVOICE_A and W_AP_AGING_SUPPLIER_A.
A quick check in SQLDeveloper showed these didn’t have any records in them so our problem is linked to the ETL not populating the data rather than the report definition.
Diagnosing the ETL
Aggregate tables are populated during the PLP phase of the ETL so to find out where the data in the tables comes from I examined the mappings related to the target:
The sql used for this mapping can be found in the Source Qualifier transformation (SQ_IA_AP_XACTS) and contains the following from and where clauses:
FROM W_AP_XACT_F, W_STATUS_D, W_XACT_TYPE_D, W_DAY_D, W_DAY_D DUEDT, W_DAY_D INVOICEDT WHERE W_AP_XACT_F.DOC_STATUS_WID = W_STATUS_D.ROW_WID AND W_AP_XACT_F.PAYMENT_DUE_DT_WID = DUEDT.ROW_WID AND W_AP_XACT_F.INVOICED_ON_DT_WID = INVOICEDT.ROW_WID AND W_STATUS_D.STATUS_CODE ='OPEN' AND W_AP_XACT_F.DOC_TYPE_WID = W_XACT_TYPE_D.ROW_WID AND W_DAY_D.W_CURRENT_CAL_DAY_CODE = 'Current' AND W_XACT_TYPE_D.W_XACT_TYPE_CODE = 'ORIGINAL' AND W_AP_XACT_F.DELETE_FLG='N' GROUP BY ..........
I used this piece of sql in SQLDeveloper and as expected it returned no rows. By checking all of the WHERE clause statements I found that the W_AP_XACT_F.DOC_TYPE_WID was causing the problem and this was set to zero in every record in the table.
Tracing back further the DOC_TYPE_WID is populated in the SIL mapping by joining the staging table to the dimension via:
W_AP_XACT_FS.doc_type_id=W_XACT_TYPE_D.integration_id
Checking what doc_type_id’s were present in the staging table resulted in just 2:
- ACCT_DOC~PAYABLES~DIFFERENCE
- ACCT_DOC~PAYABLES~MANUAL
Corresponding records were not present in the dimension table. And there were no records with an integration_id that matched ‘ACCT_DOC~PAYABLES%’ either. So the root of the problem seems to be the loading of the dimension.
Investigating the Dimension Load
As the dimension was missing data, the first check was the dimension stage table (W_XACT_TYPE_DS). This also didn’t contain any of the records for Payables so the load of this was looked at.
This is populated from two temporary tables…
- w_xacttype_aptype_tmp which contained 6 records
- w_xacttype_apsubtype_tmp which contained zero records
I compared these results to an internal system we have and the subtype temp table was populated.
Digging into the mapping for the subtype temporary table and the sql to populate this is:
SELECT CASE WHEN LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE' THEN 'INVDIST~'||AP_LOOKUP_CODES.LOOKUP_CODE ELSE AP_LOOKUP_CODES.LOOKUP_CODE END, AP_LOOKUP_CODES.DISPLAYED_FIELD, AP_LOOKUP_CODES.LOOKUP_TYPE FROM AP_LOOKUP_CODES WHERE LOOKUP_TYPE = 'PAYMENT METHOD' OR LOOKUP_TYPE = 'INVOICE TYPE' OR LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE'
When running this in SQLDeveloper it returned no rows.
This I considered odd so I tried on our internal system via SQLDeveloper and again this returned no rows, however when I tried using SQLPlus on the server with an Applications environment set it returned the expected results…
To find out why I looked at the view definition and it’s a language depndant view which uses the NLS setting of the session to derive the language to use:
SELECT LV.LOOKUP_TYPE,
LV.LOOKUP_CODE,
LV.MEANING,
LV.DESCRIPTION,
LV.ENABLED_FLAG,
LV.START_DATE_ACTIVE,
LV.END_DATE_ACTIVE
from FND_LOOKUP_VALUES LV
WHERE LV.LANGUAGE = userenv('LANG')
and LV.VIEW_APPLICATION_ID = 200
and LV.SECURITY_GROUP_ID =
fnd_global.lookup_security_group
(LV.LOOKUP_TYPE, LV.VIEW_APPLICATION_ID)
Checking the language in the session with:
select userenv('LANG') from dual;
Gave me a language of ‘GB’. Any records in E-Business Suite translated tables/views in English have a language code of ‘US’ thus the query was returning no rows in SQLDeveloper (which took the settings from my PC) but did return records on the server where $NLS_LANG was set to American_America.UTF8, thus giving a language code of US!!
The Solution
When Informatica runs it will use the environment on the Windows server. In this case it was set to an ‘English GB’ setting. To resolve the issue I changed all instances of NLS_LANG in the registry (all will be in Oracle Home entries under HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\) and to be doubly sure I added an environment variable to the user who runs the Informatica server to set NLS_LANG to Amercian_America.UTF8 (this value might be different for different character sets and locations).
As this was a proof of concept and we had no historical data (snapshots etc.) to worry about, I ran a full ETL and the problem dimension and aging tables were then populated correctly.
