OBIA 7.9.6.4 - EBS - Buyer not Active when PO was created - OOTB SIL_PurcahseOrderFact does not capture these details (Doc ID 2107311.1)

Last updated on JULY 18, 2017

Applies to:

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

Symptoms

A BPA (Blanket Purchase Agreement) created through the Source system EBS.

The Buyer for this BPA is changed during the BPA’s lifecycle. A new Buyer is created which was not an employee at the time when this BPA record was created.

The SQ query for the OOTB mapping which loads the W_PURCH_ORDER_F table in the SIL_PurchaseOrderFact mapping has the following join condition for picking up data for the BUYER_ID:

W_PURCH_ORDER_FS
LEFT OUTER JOIN W_EMPLOYEE_D_BUYER ON
W_PURCH_ORDER_FS. BUYER_ID = W_EMPLOYEE_D_BUYER.INTEGRATION_ID AND
W_PURCH_ORDER_FS.DATASOURCE_NUM_ID = W_EMPLOYEE_D_BUYER.DATASOURCE_NUM_ID AND
W_PURCH_ORDER_FS.CREATED_ON_DT >= W_EMPLOYEE_D_BUYER.EFFECTIVE_FROM_DT AND
W_PURCH_ORDER_FS.CREATED_ON_DT < W_EMPLOYEE_D_BUYER.EFFECTIVE_TO_DT

 

For example =>

Consider a BPA created on 01/01/2000 and a Buyer created later which is an employee now and has the EFFECTIVE_FROM_DT as 03/03/2001

Thus with the example the OOTB query condition will be:

(W_PURCH_ORDER_FS.CREATED_ON_DT) – 01/01/2000 >= (W_EMPLOYEE_D.EFFECTIVE_FROM_DT) – 03/03/2001

Thus complete data is not getting picked up by this query.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms