Does PO_REQUISITION_LINES_ALL.MODIFIED_BY_AGENT_FLAG Affect The Purchase Requisition Amount? (Doc ID 1312442.1)

Last updated on AUGUST 02, 2017

Applies to:

Business Intelligence Applications Consumer - Version: 7.9.6.2 [AA 1830] and later   [Release: V7 and later ]
Procurement and Spend Analytics - Version: 7.9.6.2 [AA 1830] and later    [Release: V7 and later]
Information in this document applies to any platform.

Symptoms

On: Oracle BI Applications 7.9.6.2 (Procurement and Spend Analytics)
With source system: Oracle e-Business Suite R12.1.1

For User Acceptance Testing (UAT), the Business Users extracted Purchase Requisition data from the Oracle e-Business Suite (EBS) source system to compare it with the reports / data in Oracle BI Applications (OBIA).

It was observed that the Requisition Amounts were different / did not match ... and the discrepancy / difference was found to be for / with any Requisition where PO_REQUISITION_LINES_ALL.MODIFIED_BY_AGENT_FLAG was 'Y' in the EBS source system.

Such records in the EBS are not considered by OBIA when calculating Requisition Amounts.  For example:

See the SDE mappings set DELETE_FLG to 'Y' in the Staging Fact Tables whenever the MODIFIED_BY_AGENT_FLAG = 'Y':

Mapping: SDE_ORA_RequisitionLinesCostFact
Mapplet:  mplt_SA_ORA_RequisitionLineCostFact
Expression:  EXP_SAI_RQLN_COST
Port:  EXT_DELETE_FLAG
(IIF(ISNULL(INP_INTEGRATION_ID) AND ISNULL(INP_DATASOURCE_NUM_ID),'Y',IIF (INP_MODIFIED_AGENT_FLAG ='Y','Y','N'))

Mapping: SDE_ORA_PurchaseRequisitionLinesFact
Mapplet:  mplt_SA_ORA_PurchasePequisitionLinesFact
Expression:  EXP_SAI_PURCH_RQLN:
Port:   EXT_DELETE_FLAG
= (IIF(ISNULL(INP_INTEGRATION_ID) AND ISNULL(INP_DATASOURCE_NUM_ID),'Y',IIF (INP_MODIFIED_AGENT_FLAG ='Y','Y','N'))
 
See in the pre-built repository (OracleBIAnalyticsApps.rpd) that only records with DELETE_FLG = 'N' are considered:

BMM Layer: Core > Fact - Purchasing - Requisition > Sources
Source: Fact_W_PURCH_RQSTN_LINE_F
Properties > Content > Use this "WHERE Clause" filter to limit rows returned:
"Oracle Data Warehouse".Catalog.dbo.Fact_W_PURCH_RQSTN_LINE_F.DELETE_FLG = 'N'

BMM Layer: Core > Fact - Purchasing - Requisition > Sources
Source: Fact_W_RQSTN_LINE_COST_F
Properties > Content > Use this "WHERE Clause" filter to limit rows returned:
"Oracle Data Warehouse".Catalog.dbo.Fact_W_RQSTN_LINE_COST_F.DELETE_FLG = 'N'

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