My Oracle Support Banner

CIS:The Field Order Download Extract Process Fails at CIPOXFON_S_LASTBIL (Doc ID 611241.1)

Last updated on MAY 29, 2007

Applies to:

PeopleSoft Enterprise Revenue Management Billing - Version: 8.9 - Release: 8.9

Information in this document applies to any platform.

This document was previously published as Customer Connection Solution 707895


The Field Order Download Extract Process (CIPOXFON) fails, when a customer has more than one bill for the same account on the same date(having theSame BILL_DT). The issue is encountered in the Store statement in CIPOXFON.DMS, where the account's Last Completed Bill is retrieved.....  CIPOXFON_S_LASTBIL
This statement was modified in incident R-STALAC-E656Y  to resolve a performance issue with this process.

This is the statement that fails in CIPOXFON.DMS:
-- Retrieve the last completed Bill for an Account
        ,PS_CI_BILL_SA B
     AND B.BILL_ID = (SELECT BILL_ID     ------------> As shown below, it is apparent that the subquery returns more than one row.
                         FROM PS_CI_BILL
                        WHERE ACCT_ID = :1
                          AND BILL_STAT_FLG = 'C'
                          AND BILL_DT = (SELECT MAX (BILL_DT)    
                                            FROM PS_CI_BILL
                                           WHERE ACCT_ID = :1
                                             AND BILL_STAT_FLG = 'C') )  

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Running the Subquery that causes the


To view full details, 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 a vibrant support community of peers and Oracle experts.