My Oracle Support Banner

OBIA - Purchase Order Number Is Read From AP Tables Instead Of PO Tables (Doc ID 2001428.1)

Last updated on FEBRUARY 05, 2020

Applies to:

Business Intelligence Applications Consumer - Version and later
Information in this document applies to any platform.


You are implementing OBIA, Financial Analytics, Procurement and Spend Analytics, and Project Analytics.
When creating a new analysis from "Procurement and Spend - Purchase Orders" subject area, when you select Purchase Order Number from "Purchase Details" and try adding a filter on Purchase Order Number, all PO Numbers are not shown.
Looking at the session log you see that the PO Numbers are fetched from W_AP_XACT_F instead of W_PURCH_COST_F.  Hence, if PO is not associated with AP, it is not shown in the selection.

It is possible to put the value in the filter criteria instead of doing the search but the issue is not about adding the filter criteria for PO Number manually and not getting the results. The issue is about not able to see the PO Number when searched even though it exists.
This causes lot of confusion for the users since they frequently use the search button for selecting the values for a filter since this functionality is available in Answers.

 1.    Create a new Adhoc Report using «Procurement and Spend – Purchase Orders » Subject Area
 2.    Include the following columns in the report : "Purchase Details"."Purchase Order Number", "Fact - Purchase Orders"."PO Amount"


3.    Click on Results


4.    The Purchase Order 100085 is in the Analysis as shown in the above screen.
5.    Now add a filter to this Analysis on PO Number


6.    Click on the « Search » icon on right to see all PO Numbers for selecting the PO Number for adding a filter.
7.    PO Number 100085 is not shown in the Search List.


8.    Try using Search to search in the list
9.    Uncheck « Match Case » and click Search
10.  As seen from the above screen, PO Number 100085 is not found.
11.  The query in nqquery.log is as follows :

[<timestamp>] [OracleBIServerComponent] [TRACE:3] [USER-18] [] [ecid: <ecid>] [tid: <tid>] [requestid: <request_id>] [sessionid: <sessionid>] [username: <user>] --------------------
Sending query to database named Oracle Data Warehouse (id: <<id>>),
connection pool named Oracle Data Warehouse Connection Pool, logical request hash 38419bc, physical request hash b734fe0a: [[

select D1.c1 as c1 from ( select distinct T550392.PURCH_ORDER_NUM as c1
     W_AP_XACT_F  T550392 /* Fact_W_AP_XACT_F */
order by c1 ) D1 where rownum <= 65001

12.  The PO Number exists in W_PURCH_COST_F, but does not exist in W_AP_XACT_F. Both these tables are in
       Logical Table Source (LTS) for Purchase Details.





To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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