PC_INTEREST ABENDED at Step PC_INTEREST.10000000.INSERT1 (Action SQL) -- RC = 1427 (SQL Error: ORA-01427: single-row subquery returns more than one row) (Doc ID 2167674.1)

Last updated on AUGUST 05, 2016

Applies to:

PeopleSoft Enterprise FIN Project Costing - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Calculate Interest

When attempting to run PC_INTEREST app engine
the following error occurs.

ERROR
-----------------------
Application Engine trace showing the abend

-- 15:08:18.835 ...(PC_INTEREST.10000000.INSERT1) (SQL)
INSERT INTO PS_PC_RES_INT_TAO
(BUSINESS_UNIT,PROJECT_ID,ACTIVITY_ID,RESOURCE_ID,RESOURCE_ID_FROM,
BUSINESS_UNIT_GL,JOURNAL_ID,JOURNAL_DATE,UNPOST_SEQ,JOURNAL_LINE,FISCAL_YEAR,
ACCOUNTING_PERIOD,ACCOUNT,ALTACCT,DEPTID,OPERATING_UNIT,PRODUCT,FUND_CODE,
CLASS_FLD,PROGRAM_CODE,BUDGET_REF,AFFILIATE,AFFILIATE_INTRA1,AFFILIATE_INTRA2,
CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,BUS_UNIT_GL_FROM,CURRENCY_CD,
STATISTICS_CODE,LEDGER_GROUP,ANALYSIS_TYPE,RESOURCE_TYPE,RESOURCE_CATEGORY,
RESOURCE_SUB_CAT,RES_USER1,RES_USER2,RES_USER3,RES_USER4,RES_USER5,TRANS_DT,
ACCOUNTING_DT,OPRID,DTTM_STAMP,JRNL_LN_REF,OPEN_ITEM_STATUS,LINE_DESCR,
JRNL_LINE_STATUS,JOURNAL_LINE_DATE,FOREIGN_CURRENCY,RT_TYPE,FOREIGN_AMOUNT,
RATE_MULT,RATE_DIV,CUR_EFFDT,PROCESS_INSTANCE,PC_DISTRIB_STATUS,
GL_DISTRIB_STATUS,PROJ_TRANS_TYPE,PROJ_TRANS_CODE,RESOURCE_STATUS,DESCR,
SYSTEM_SOURCE,UNIT_OF_MEASURE,EMPLID,EMPL_RCD,SEQ_NBR,TIME_RPTG_CD,JOBCODE,
COMPANY,BUSINESS_UNIT_AP,VENDOR_ID,VOUCHER_ID,VOUCHER_LINE_NUM,APPL_JRNL_ID,
PYMNT_CNT,DST_ACCT_TYPE,PO_DISTRIB_STATUS,BUSINESS_UNIT_PO,REQ_ID,
REQ_LINE_NBR,REQ_SCHED_NBR,REQ_DISTRIB_NBR,PO_ID,DUE_DATE,LINE_NBR,SCHED_NBR,
DISTRIB_LINE_NUM,AM_DISTRIB_STATUS,BUSINESS_UNIT_AM,ASSET_ID,PROFILE_ID,
COST_TYPE,BOOK,INCENTIVE_ID,MSTONE_SEQ,CONTRACT_NUM,CONTRACT_LINE_NUM,
CONTRACT_PPD_SEQ,BI_DISTRIB_STATUS,BUSINESS_UNIT_BI,BILLING_DATE,INVOICE,
REV_DISTRIB_STATUS,BUSINESS_UNIT_AR,CUST_ID,ITEM,ITEM_LINE,ITEM_SEQ_NUM,
DST_SEQ_NUM,BUSINESS_UNIT_IN,SCHED_LINE_NO,DEMAND_LINE_NO,INV_ITEM_ID,
PAY_END_DT,BUSINESS_UNIT_OM,ORDER_NO,ORDER_INT_LINE_NO,EX_DOC_ID,EX_DOC_TYPE,
RESOURCE_QUANTITY,RESOURCE_AMOUNT,BUDGET_HDR_STATUS,KK_AMOUNT_TYPE,
KK_TRAN_OVER_FLAG,KK_TRAN_OVER_OPRID,KK_TRAN_OVER_DTTM,BUDGET_OVER_ALLOW,
BUDGET_LINE_STATUS,BUDGET_DT,LEDGER,BD_DISTRIB_STATUS,BUSINESS_UNIT_BD,
FA_STATUS,TIME_SHEET_ID,SHEET_ID,DT_TIMESTAMP,VCHR_DIST_LINE_NUM,PM_REVIEWED,
PRICED_RATE,ACTIVITY_ID_DETAIL,CST_DISTRIB_STATUS,TXN_LMT_TRANS_ID,EVENT_NUM,
CA_FEE_STATUS,BUSINESS_UNIT_WO,WO_ID,WO_TASK_ID,RSRC_TYPE,RES_LN_NBR,
COMPRESS_ID,AMOUNT_IN_EXCESS,RECLAIMED_FROM_OL,FND_DIST_STATUS,SEQ_TRANS_ID,
DIST_TRANS_ID,ADJ_LINE_TYPE,FEEDER_SUM_ID,PRICE_SUM_ID,DEPOSIT_BU,DEPOSIT_ID,
PAYMENT_SEQ_NUM) SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , (
SELECT CASE WHEN LENGTH(z.rowid) < 40 THEN substr(z.rowid ,1 ,
LENGTH(z.rowid)) WHEN LENGTH(z.rowid) > 40 THEN substr(z.rowid ,
LENGTH(z.rowid)-40 , 40 ) END FROM PS_PROJ_INT_BU_TMP z WHERE z.BUSINESS_UNIT
= a.BUSINESS_UNIT AND A.PROJECT_ID = Z.PROJECT_ID AND A.ACTIVITY_ID =
Z.ACTIVITY_ID AND A.DEPTID = Z.DEPTID AND A.ALTACCT = Z.ALTACCT AND A.ACCOUNT
= Z.ACCOUNT AND A.BUSINESS_UNIT_GL = Z.BUSINESS_UNIT_GL AND A.CALC_TYPE =
Z.CALC_TYPE AND A.RESOURCE_TYPE = Z.RESOURCE_TYPE AND A.INT_ANALYSIS_TYPE =
Z.INT_ANALYSIS_TYPE AND A.RESOURCE_CATEGORY = Z.RESOURCE_CATEGORY AND
A.RT_TYPE = Z.RT_TYPE) , ' ' , A.BUSINESS_UNIT_GL , ' ' ,
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') , 0 , 0 , 0 , 0 ,
A.ACCOUNT ,A.ALTACCT ,A.DEPTID , MAX(A.OPERATING_UNIT), MAX(A.PRODUCT),
MAX(A.FUND_CODE), MAX(A.CLASS_FLD), MAX(A.PROGRAM_CODE), MAX(A.BUDGET_REF),
MAX(A.AFFILIATE), MAX(A.AFFILIATE_INTRA1), MAX(A.AFFILIATE_INTRA2),
MAX(A.CHARTFIELD1), MAX(A.CHARTFIELD2), MAX(A.CHARTFIELD3) ,
A.BUSINESS_UNIT_GL , C.CURRENCY_CD , ' ' , ' ' , A.INT_ANALYSIS_TYPE ,
A.RESOURCE_TYPE , A.RESOURCE_CATEGORY , A.RESOURCE_SUB_CAT , ' ' , ' ' , ' '
, ' ' , ' ' , TO_DATE('2015-07-31','YYYY-MM-DD') ,
TO_DATE('2015-07-31','YYYY-MM-DD') , 'SIROSH_KPMG' , CAST(SYSTIMESTAMP AS
TIMESTAMP) , ' ' , ' ' , ' ' , ' ' , NULL , C.CURRENCY_CD , A.RT_TYPE ,
SUM(A.AFUDC_INTEREST * B.SIGNED_VALUE) , 1.0 , 1.0 ,
TO_DATE('2015-07-31','YYYY-MM-DD') , 12168735 , 'N' , 'N' , 'INT' , ' ' , ' '
, MAX(A.DESCR1) , 'BPC' , ' ' , ' ' , 0 , 0 , ' ' , ' ' , ' ' , ' ' , ' ' , '
' , 0 , ' ' , 0 , ' ' , 'I' , ' ' , ' ' , 0 ,0 ,0 , ' ' , NULL , 0 , 0 , 0 ,
' ' , ' ' , ' ' , ' ' ,' ' , ' ' , ' ' , 0 ,' ' , 0 ,0 , 'I' , ' ' , NULL , '
' , 'I' , ' ' , ' ' , ' ' , 0 , 0 , 0 , ' ' , 0 , 0 , ' ' , NULL , ' ' , ' '
, 0 , ' ' , ' ' , 0 , SUM(A.AFUDC_INTEREST * B.SIGNED_VALUE) , ' ' , ' ' , '
' , 'SIROSH_KPMG' , CAST(SYSTIMESTAMP AS TIMESTAMP) , ' ' , ' ' , NULL , ' '
, 'N' , ' ' , 'N' , ' ' , ' ' , NULL , 0 , 'Y' , 0 , ' ' , 'I' , ' ' , 0 , '
' , ' ' , ' ' , 0 , ' ' , 0 , 0 , ' ' , ' ' , 'N' , 0 , 0 , ' ' , 0 , 0 , ' '
, ' ' , 0 FROM PS_BUS_UNIT_TBL_PC C , PS_PROJ_AN_GRP_MAP B ,
PS_PROJ_INT_BU_TMP A WHERE A.AFUDC_ACT_SW = 'Y' AND A.AFUDC_INTEREST <> 0 AND
((2016 <> 2016 AND ((A.FISCAL_YEAR = 2016 AND A.ACCOUNTING_PERIOD >= 1) OR
(A.FISCAL_YEAR > 2016 AND A.FISCAL_YEAR < 2016) OR (A.FISCAL_YEAR = 2016 AND
A.ACCOUNTING_PERIOD <= 1))) OR (2016 = 2016 AND A.FISCAL_YEAR = 2016 AND
A.ACCOUNTING_PERIOD BETWEEN 1 AND 1)) AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND 1=1 AND B.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC X WHERE X.RECNAME
= 'PROJ_AN_GRP_MAP' AND X.SETCNTRLVALUE = A.BUSINESS_UNIT) AND
A.ANALYSIS_GROUP = B.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = B.ANALYSIS_TYPE AND
A.BUSINESS_UNIT = 'UPMC1' AND A.SETID = 'UPMC1' AND A.CALENDAR_ID = 'FY'
GROUP BY A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , A.BUSINESS_UNIT_GL,
A.INT_ANALYSIS_TYPE, A.CALC_TYPE, A.RESOURCE_TYPE, A.RESOURCE_CATEGORY,
A.RESOURCE_SUB_CAT, A.RT_TYPE, C.CURRENCY_CD,A.ACCOUNT,A.ALTACCT,A.DEPTID
/
-- Row(s) affected: 0
-- 15:08:18.971 Process 12168735 ABENDED at Step PC_INTEREST.10000000.INSERT1 (Action SQL) -- RC = 1427
ROLLBACK
/
-- 15:08:18.992 SQL Error: ORA-01427: single-row subquery returns more than one row


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create the set up so that the Project/Activity is eligible for interest calculations
2. Create 2 transactions using "Add Transaction page that are eligible for interest calculation for March Period

Example: 2 ACT rows- All chartfields are same same except that one has accounting date of '3/22/2016' and the other '3/20/2106'

3. Run Project Costing > Utilities > Calculate Interest for March period only. The process abends with above error.

Another scanario that can cause issue

4. Using new run control, run PC_INTEREST process for Feb period and make sure that the process goes to success
5. Create 2 transactions using "Add Transaction page that are eligible for interest calculation for Feb Period

Example: 2 ACT rows- Accounting date on these transactions are same accounting date of '2/22/2016'. But make sure all other chartfields are same except Product/Fund or others.

6. Run Project Costing > Utilities > Calculate Interest for Feb period only. The process abends with above error.


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