Uninvoiced Receipts Report Erroring With Ora-01427 (Doc ID 1276756.1)

Last updated on JANUARY 27, 2014

Applies to:

Oracle Purchasing - Version 12.1.1 and later
Information in this document applies to any platform.
Report:POXPORRA.RDF - Uninvoiced Receipts Report


Symptoms

Uninvoiced Receipts Report completes with error when run in a period which overlaps with an Adjustment period.
The following error is seen in the Uninvoiced Receipts Report log file:

ORA-01427: single-row subquery returns more than one row in Package CST_UninvoicedReceipts_PVT Procedure Start_Process

There exist two periods that overlap. For example, DEC-10 Period with Start Date of '01-DEC-10' and End Date of '31-DEC-10', and ADJ-10 Adjustment Period with same  Start Date of '01-DEC-10' and End Date of '31-DEC-10'.

The report code has the following query which is returning more two rows for the above scenario.

SELECT gp.period_name, gp.start_date, gp.end_date
FROM gl_periods gp,
gl_sets_of_books sob
WHERE sob.set_of_books_id = :l_sob_id
AND sob.period_set_name = gp.period_set_name
AND sob.accounted_period_type = gp.period_type
AND gp.start_date <= TRUNC(SYSDATE)
AND gp.end_date >= TRUNC(SYSDATE)

The report does not generate any output when it completed with error.

Current file version
CST_UNINVOICEDRECEIPTS_PVT CSTVURRB.pls 120.7.12010000.5 or lower.

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