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

Last updated on SEPTEMBER 03, 2023

Applies to:

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.

Changes

Cause

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