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

Symptoms


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
STORE CIPOXFON_S_LASTBIL
  SELECT A.BILL_DT
        ,SUM(B.CUR_AMT)
    FROM PS_CI_BILL    A
        ,PS_CI_BILL_SA B
   WHERE A.BILL_ID = B.BILL_ID
     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') )  
GROUP BY A.BILL_DT;

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

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