CIS:Need an ORDER BY clause in CIPBBLSG.DMS. (Doc ID 612065.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 713413

Symptoms


During conversion, accounts that were too recent to have been billed in customer's Legacy system had bill segments (PS_CI_BSEG table) generated with the same START_DT and END_DT.  When a new bill segment is generated during the billing process, the START_DT is obtained by selecting all bill segments for the current SA, sorted in START_DT descending order, and using the END_DT from the first row returned as the new START_DT.  In the second billing, when the START_DT and END_DT are equal, there is a possibility that the wrong END_DT will be used to get the new START_DT.  

Here is the SQL statement in STORE CIPBBLSG_S_PREVBS:
SELECT
       BSEG_ID,
       START_DT,
       END_DT,
       BILL_CYC_CD,
       WIN_START_DT,
       BSEG_STAT_FLG,
       CLOSING_BSEG_SW,
       CAN_BSEG_ID
  FROM
       PS_CI_BSEG
 WHERE
       SA_ID = :1
   AND START_DT < :2
   AND BSEG_STAT_FLG <> '60'
 ORDER BY
       START_DT DESC;
    

The following is an example of 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