My Oracle Support Banner

Duplicate Rows GME_BATCH_GENEALOGY Cause Error 'ORA-01427 Single-row Subquery Returns More One Row' When Running Subledger Period Close Exceptions Report (Doc ID 2655460.1)

Last updated on APRIL 02, 2020

Applies to:

Oracle Process Manufacturing Process Execution - Version 12.1.3 and later
Oracle Process Manufacturing Financials - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Other Issues

There are duplicate rows in the GME_BATCH_GENEALOGY table are causing the Subledger Period Close Exceptions Report to fail with the ORA-01427: single-row subquery returns more than one row error.
The GME_BATCH_GENEALOGY is one of the source tables in the GMF_XLA_PM_TXNS_V database view. The duplicate rows are causing duplicate transaction IDs to show up in the GMF_XLA_PM_TXNS_V.

This view includes a union with the GME_BATCH_HEADER_VW database view, which in turn consists of the GME_BATCH_GENEALOGY table.
The GME_BATCH_GENEALOGY table is included in the GME_BATCH_HEADER_VW database view as the following subquery (SELECT DISTINCT BATCH_ID, HEAT_NUMBER, BATCH_YIELD FROM GME_BATCH_GENEALOGY).
When parent batches are in the GME_BATCH_GENEALOGY table with different values for BATCH_YIELD, the subquery will return more than one row for the same BATCH_ID. This causes the report to fail with below error.

This does not happen for all batches.


ERROR
-----------------------
ORA-01427: single-row subquery returns more than one row error.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1 Responsibility: Production Management
2. Navigation: Batch Group Management
3. To see error in Subledger Period Close Exception report such concurrent request for Source Inventory and class Batch Close Variance.


Cause

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
Symptoms
Cause
Solution
References


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