My Oracle Support Banner

EAM:The Allocation Process (AMALLOC) Abends at Step “AMALLOC.CHARTFLD.Step01” With Error “ORA-01427: single-row subquery returns more than one row” When Processing Assets With Multiple Chartfield Combinations Transferred to a Unique Chartfield Combination (Doc ID 2970932.1)

Last updated on NOVEMBER 01, 2023

Applies to:

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

Symptoms

The Allocation Process (AMALLOC) abends at step “AMALLOC.CHARTFLD.Step01” with error “ORA-01427: single-row subquery returns more than one row” when processing Assets with multiple chartfield combinations transferred to a unique chartfield combination.

 

Run Control > Message Log Error:

File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1811 Error Position: 37 Return: 14 27 - ORA-01427: single-row subquery returns more than one row Failed SQL stmt: UPDATE PS_ALLOC_TMP SET AFFILIATE = (select DIST.AFFILIATE from PS_DIST_LN_TEMP DIS T where DIST.BUSINESS_UNIT = PS_ALLOC_TMP.BUSINESS_UNIT AND DIST.ASSET_ID = PS_ALLOC_TMP.ASSET_ID AN D DIST.BOOK = PS_ALLOC_TMP.BOOK AND DIST.DTTM_STAMP = PS_ALLOC_TMP.DTTM_STAMP AND DIST.DISTRIBUTION_ TYPE = PS_ALLOC_TMP.DISTRIBUTION_TYPE AND DIST.ACCOUNTING_DT = PS_ALLOC_TMP.ACCOUNTING_DT AND DIST.T RANS_TYPE = PS_ALLOC_TMP.TRANS_TYPE AND DIST.CATEGORY = PS_ALLOC_TMP.CATEGORY AND DIST.COST_TYPE = P S_ALLOC_TMP.COST_TYPE AND DIST.CF_SEQNO = PS_ALLOC_TMP.CF_SEQNO_LAST AND DIST.PROCESS_INSTANCE = XXXXXX AND DIST.BUSINESS_UNIT = 'YYYYY') WHERE AFFILIATE = ' ' AND PROCESS_INSTANCE = XXXXXX AND BUSIN

Process XXXXXX ABENDED at Step AMALLOC.CHARTFLD.Step01 (SQL) -- RC = 1427

271003 AMALLOC CHARTFLD Step01 SQL 1427

AMALLOC .AET Trace file Error:

-- 2023-02-07 08:26:45.052 ....(AMALLOC.CHARTFLD.Step01) (SQL)
UPDATE PS_ALLOC_TMP SET AFFILIATE = (select DIST.AFFILIATE from
PS_DIST_LN_TEMP DIST where DIST.BUSINESS_UNIT = PS_ALLOC_TMP.BUSINESS_UNIT
AND DIST.ASSET_ID = PS_ALLOC_TMP.ASSET_ID AND DIST.BOOK = PS_ALLOC_TMP.BOOK
AND DIST.DTTM_STAMP = PS_ALLOC_TMP.DTTM_STAMP AND DIST.DISTRIBUTION_TYPE =
PS_ALLOC_TMP.DISTRIBUTION_TYPE AND DIST.ACCOUNTING_DT =
PS_ALLOC_TMP.ACCOUNTING_DT AND DIST.TRANS_TYPE = PS_ALLOC_TMP.TRANS_TYPE AND
DIST.CATEGORY = PS_ALLOC_TMP.CATEGORY AND DIST.COST_TYPE =
PS_ALLOC_TMP.COST_TYPE AND DIST.CF_SEQNO = PS_ALLOC_TMP.CF_SEQNO_LAST AND
DIST.PROCESS_INSTANCE = 271003 AND DIST.BUSINESS_UNIT = 'YYYYY') WHERE
AFFILIATE = ' ' AND PROCESS_INSTANCE = XXXXXX AND BUSINESS_UNIT = 'YYYYY'
/
-- Row(s) affected: 0
-- 2023-02-07 08:26:45.414 Process XXXXXX ABENDED at Step AMALLOC.CHARTFLD.Step01 (Action SQL) -- RC = 1427
ROLLBACK
/
-- 2023-02-07 08:26:45.646 SQL Error: ORA-01427: single-row subquery returns more than one row


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

1. Create a new Asset with multiple Chartfield Combinations (some with Quantity = 0)
Navigation: Asset Management > Asset Transactions > Owned Assets > Express Add
2. Add a new Chartfield combination with Quantity = 0
Navigation: Asset Management > Asset Transactions > Financial Transactions > Cost Adjust/Transfer Asset | Addition
3. Add a new Chartfield combination with Quantity = 1
Navigation: Asset Management > Asset Transactions > Financial Transactions > Cost Adjust/Transfer Asset | Addition
4. Calculate the Depreciation
Navigation: Asset Management > Depreciation > Processing > Calculate Depreciation
5. Create the Accounting Entries
Navigation: Asset Management > Accounting Entries > Create Accounting Entries
6. Make sure the asset is included in the Asset Allocation ID
Navigation: Asset Management > Accounting Entries > Allocate Depreciation Expense > Asset Allocations
7. Process the Allocation from Periods 1 to 4
Asset Management > Accounting Entries > Allocate Depreciation Expense > Process Allocation
8. Transfer all the Asset chartfield combinations to a unique chartfield combination
Navigation: Asset Management > Asset Transactions > Financial Transactions > Cost Adjust/Transfer Asset | Transfer
9. Calculate the Depreciation
Navigation: Asset Management > Depreciation > Processing > Calculate Depreciation
10. Create the Accounting Entries
Navigation: Asset Management > Accounting Entries > Create Accounting Entries
11. Process the Allocation for period 5
Navigation: Asset Management > Accounting Entries > Allocate Depreciation Expense > Process Allocation
12. Check the Run Control and notice that the process failed.

Detailed DEMO Replication Steps Attached

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
References


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