My Oracle Support Banner

The Close Depreciation Process (AM_DPCLOSE) Fails at “AM_DPCLOSE.500250.Step11A” for Leases With Backdated Amendments With Error “Cannot Insert Duplicate Key Row in Object ‘dbo.PS_LEASE_DIST5_TMP’ With Unique Index ‘PS_LEASE_DIST5_TMP’” or "ORA-00001" (Doc ID 3014346.1)

Last updated on JULY 01, 2024

Applies to:

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

Symptoms

The Close Depreciation Process (AM_DPCLOSE) fails at "AM_DPCLOSE.500250.Step11A" for Leases with Backdated Amendments with error "Cannot insert duplicate key row in object 'dbo.PS_LEASE_DIST5_TMP' with unique index 'PS_LEASE_DIST5_TMP'" or "ORA-00001: unique constraint"



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

1. Ensure GASB 87 Accounting is turned on the Lease Administration (LA) Installation Options
Navigation: Set Up Financials/Supply Chain > Install > Installation Options | Lease Administration
2. Select a Lease Administration Business Unit that is set up as GASB 87
Navigation: Set Up Financials/Supply Chain > Business Unit Related > Lease Administration > LA Business Unit Definition
3. Enter an Arrears Lease with 2 Financial Terms. Base Rent and Miscellaneous Rent. (Both Start and End Dates are the same as the Lease Commencement and Termination Dates.)
Navigation: Lease Administration > Payables Leases > Create New Payables Lease
4. Activate the lease.
5. Calculate the Depreciation, Create the Accounting Entries, and Close the Depreciation(AM_DPCLOSE) for the Lease Accounting Date period (Period for the accounting date entered when the Lease was created)
Navigation: Asset Management > Depreciation > Processing > Calculate Depreciation
Navigation: Asset Management > Accounting Entries > Create Accounting Entries
Navigation: Asset Management > Accounting Entries > Close Depreciation
6. Amend the lease. (Transaction date = Lease Commencement Date and Accounting greater than the Accounting Date when the lease was added.)
Navigation: Lease Administration > Payables Leases > Maintain Payables Lease
7. Change one of the chartfields (ex: class field)
8. Activate the Amendment.
9. Calculate the Depreciation, Create the Accounting Entries, and Close the Depreciation(AM_DPCLOSE) for the Amendment Accounting Period (Period for the accounting date entered when the Lease was Amended)
10. Notice that the Close Depreciation Process goes to No Success with the following Message Log / .AET trace Errors:


Process Detail Message Log Error on Oracle Database:

File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1851 Error Position: 0 Return: 805 - ORA-00001: unique constraint (EMDBO.PS_LEASE_DIST5_TMP) violated Failed SQL stmt: INSERT INTO PS_LEASE_DIST5_TMP (PROCESS_INSTANCE, BUSINESS_UNIT, ASSET_ID, FISCAL_Y EAR, ACCOUNTING_PERIOD, CAP_LEASE_AMT, GUAR_RESIDUAL, TRANS_DT, ACCOUNTING_DT, DTTM_STAMP, LEASE_BOO K, INTEREST_EXPENSE, OBLIGATION_REDUCE, CURRENCY_CD, SCHED_PYMNT_TYPE, PYMNT_DT, TRANS_TYPE) SELECT DISTINCT 281409, LEASE_CHART2_VW.BUSINESS_UNIT, LEASE_CHART2_VW.ASSET_ID, LEASE_CHART2_VW.FISCAL_YE AR, LEASE_CHART2_VW.ACCOUNTING_PERIOD, LEASE_CHART2_VW.CAP_LEASE_AMT, LEASE_CHART2_VW.GUAR_RESIDUAL, LEASE_CHART2_VW.TRANS_DT, LEASE_CHART2_VW.ACCOUNTING_DT, NULL, LEASE_CHART2_VW.LEASE_BOOK, SUM(LEAS E_CHART2_VW.INTEREST_EXPENSE), SUM(LEASE_CHART2_VW.OBLIGATION_REDUCE), LEASE_CHART2_VW.CURRENCY_CD,

Oracle Database Process Log/Trace .AET trace file Error:

-- 2023-10-20 10:09:48.107 Process 281405 ABENDED at Step AM_DPCLOSE.500250.Step11A (Action SQL) -- RC = 805
ROLLBACK
/
-- 2023-10-20 10:09:48.184 SQL Error: ORA-00001: unique constraint (EMDBO.PS_LEASE_DIST5_TMP) violated

Process Detail Message Log Error on Microsoft SQL Server Database:

File: C:\PT860P06C_2305252140-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1851 Erro r Position: 0 Return: 805 - [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert dup licate key row in object 'dbo.PS_LEASE_DIST5_TMP' with unique index 'PS_LEASE_DIST5_TMP'. The duplic ate key value is (281274, US005, 000000000013, 2023, 1, Jan 1 2023 12:00AM). Failed SQL stmt: INSERT INTO PS_LEASE_DIST5_TMP (PROCESS_INSTANCE, BUSINESS_UNIT, ASSET_ID, FISCAL_Y EAR, ACCOUNTING_PERIOD, CAP_LEASE_AMT, GUAR_RESIDUAL, TRANS_DT, ACCOUNTING_DT, DTTM_STAMP, LEASE_BOO K, INTEREST_EXPENSE, OBLIGATION_REDUCE, CURRENCY_CD, SCHED_PYMNT_TYPE, PYMNT_DT, TRANS_TYPE) SELECT DISTINCT 281274, LEASE_CHART2_VW.BUSINESS_UNIT, LEASE_CHART2_VW.ASSET_ID, LEASE_CHART2_VW.FISCAL_YE AR, LEASE_CHART2_VW.ACCOUNTING_PERIOD, LEASE_CHART2_VW.CAP_LEASE_AMT, LEASE_CHART2_VW.GUAR_RESIDUAL,

 Microsoft SQL Server Database Process Log/Trace .AET trace file Error:

-- 2023-10-20 17:06:04.430 Process 281271 ABENDED at Step AM_DPCLOSE.500250.Step11A (Action SQL) -- RC = 805
ROLLBACK
/
-- 2023-10-20 17:06:05.170 SQL Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert duplicate key row in object 'dbo.PS_LEASE_DIST5_TMP' with unique index 'PS_LEASE_DIST5_TMP'. The duplicate key value is (281271, US005, 000000000013, 2023, 1, Jan 1 2023 12:00AM).

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.