My Oracle Support Banner

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

Last updated on MARCH 12, 2019

Applies to:

PeopleSoft Enterprise Revenue Management Billing - Version 8.9 and later
Information in this document applies to any platform.

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

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

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