C1-TXNSQ Cannot Create The Billable Charges (Doc ID 2294070.1)

Last updated on AUGUST 10, 2017

Applies to:

Oracle Financial Services Revenue Management and Billing - Version 2.5.0.1.0 and later
Information in this document applies to any platform.

Symptoms

On : 2.5.0.1.0 version, BT - Batch

C1-TXNSQ cannot create the billable charges.

Receive an error when executing C1-TXNSQ.

ERROR
-----------------------

Query to calculate SQI for Aggregation: SELECT S1.SUMMARY_ID,S1.PROCESSING_DT ,S1.CURR_CONV_ALG_CD,P.CURRENCY_CD,P.UDF_CURRENCY_CD_1,P.UDF_CURRENCY_CD_2,P.UDF_CURRENCY_CD_3,P.UDF_CURRENCY_CD_4,P.UDF_CURRENCY_CD_5,P.UDF_CURRENCY_CD_6,P.UDF_CURRENCY_CD_7,P.UDF_CURRENCY_CD_8,P.UDF_CURRENCY_CD_9,P.UDF_CURRENCY_CD_10 , SUM(TXN_AMT), SUM(TXN_VOL) FROM (SELECT * FROM CI_TXN_DTL_PRITM_SUMMARY S WHERE S.SUMMARY_ID >= ? AND S.SUMMARY_ID <=? AND S.STATUS = ' ' AND TXN_RATING_CRITERIA <> ? AND S.SW_PRM_CIS_DIVISION =? ) S1, CI_TXN_DTL_PRITM P WHERE S1.ACCT_ID = P.ACCT_ID AND S1.INIT_PRICEITEM_CD = P.INITIAL_PRICE_ITEM_CD AND S1.TOU_CD = P.INTIAL_TOU_CD AND S1.PRICEITEM_PARM_GRP_ID = P.PRICEITEM_PARM_GRP_ID AND S1.AGG_PARM_GRP_ID = P.AGG_PARM_GRP_ID AND S1.ORG_TXN_DATE = P.TXN_DTTM AND S1.PROCESSING_DT = P.PROCESSING_DT AND P.CURR_SYS_PRCS_DT = ? AND P.ORG_DIVISION =? AND P.TXN_PRITM_STATUS_CD=? GROUP BY S1.SUMMARY_ID, S1.PROCESSING_DT,S1.CURR_CONV_ALG_CD,P.CURRENCY_CD, P.UDF_CURRENCY_CD_1,P.UDF_CURRENCY_CD_2,P.UDF_CURRENCY_CD_3, P.UDF_CURRENCY_CD_4,P.UDF_CURRENCY_CD_5, P.UDF_CURRENCY_CD_6,P.UDF_CURRENCY_CD_7,P.UDF_CURRENCY_CD_8, P.UDF_CURRENCY_CD_9,P.UDF_CURRENCY_CD_10 ORDER BY S1.SUMMARY_ID
05:20:32,275 [DEFAULTWorker:3] WARN (org.hibernate.engine.jdbc.spi.SqlExceptionHelper) SQL Error: 1427, SQLState: 21000
05:20:32,275 [DEFAULTWorker:3] ERROR (org.hibernate.engine.jdbc.spi.SqlExceptionHelper) ORA-01427: single-row subquery returns more than one row


STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. Rollback Error Transaction : C1-TXNRB.
2. Flush Application Cache : F1-FLUSH.
3. Transaction Validation and Initial Product Determination : C1-TXNIP.
4. TFM gather stats batch : CMTFMGS.
5. Product Pricing Verification : C1-TXNVP.
6. Update Status : C1-TXNEX.
7. Service Quantity Calculation : C1-TXNSQ – ran with trace on.
8. Mark Completion : C1-TXNCM.
9. Clean Up : C1-TXNCU.





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