EAM9.2:Depr Reporting Table (AMDPREPT) Went to No Success With 'ORA-01407: cannot update ("EMDBO"."PS_DEPR_RP4_TMP4"."LOCATION") to NULL'
(Doc ID 2864038.1)
Last updated on OCTOBER 05, 2023
Applies to:
PeopleSoft Enterprise FIN Asset Management - Version 9.2 to 9.2 [Release 9]Information in this document applies to any platform.
*** Checked for currency on 28-Sep-2023 ***
Symptoms
After update to Image 41, Load Depr Reporting Table (AM_AMDPREPT) went to No Success with error -- 'ORA-01407: cannot update ("EMDBO"."PS_DEPR_RP4_TMP4"."LOCATION") to NULL'
This AE failed in the following step, which is newly added by PeopleSoft as part of PUM41 upgrade. This is when site runs all assets for a specific BU at a time.
Asset has been created with 2 location rows:
Location 1: Enter Effective Date and Location, but blank Effective
Location 2: Enter Effective Date, Effective Sequence and blank Location.
Error:
File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1811 Error Position: 28 Return: 14 07 - ORA-01407: cannot update
("EMDBO"."PS_DEPR_RP4_TMP4"."LOCATION") to NULL Failed SQL stmt: UPDATE PS_DEPR_RP4_TMP4 SET LOCATION = ( SELECT A.LOCATION
FROM PS_ASSET_LOCATION A WHERE A.BUSINESS_UNIT = PS_DEPR_RP4_TMP4.BUSINESS_UNIT AND A.ASSET_ID = PS_DEPR_RP4_TMP4.A SSET_ID
AND A.LOCATION <> ' ' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_ASSET_LOCATION B WHERE B.BUSINESS_UNIT = 'US001'
AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.ASSET_ID = A.ASSET_ID AND B.EFFDT < TO_DATE('2022-01-01','YYYY-MM-DD'))
AND A.EFFSEQ = (SELECT MAX(S.EFFSEQ) FRO M PS_ASSET_LOCATION S WHERE S.BUSINESS_UNIT = 'US001' AND S.BUSINESS_UNIT = A.BUSINESS_UNIT
AND S.ASSET_ID = A.ASSET_ID AND S.EFFDT = A.EFFDT)) WHERE PROCESS_INSTANCE = 255159 AND BUSINESS
Process 255159 ABENDED at Step AMDPREPT.DPRPT600.Step01B (SQL) -- RC = 1407
Steps to replicate:
1. Navigate to Asset Management > Asset Transactions > Owned Asset > Express Add, and populate fields. Then, Save.
Asset ID gets generated.
2. Navigate to Asset Management > Asset Transactions > Owned Asset > Basic Add, and access the Asset. Populate/change fields.
3. Go to Operation/Maintenance tab and populate fields.
4. Go to Asset Acquisition Detail tab to verify System Source.
5. Go to Location/Comment/Attributes tab. Click ‘Correct History’ button to make changes.
Location 1: Enter Effective Date and Location, but blank Effective
Location 2: Enter Effective Date, Effective Sequence and blank Location.
6. Then, Save.
7. Navigate to Asset Management > Financial Reports > Load Reporting Tables > Load Depr Reporting Tables.
Populate fields and Save.
8. Get a warning about processing for multiple years. Click OK on it.
9. Click Run button to run AMDPREPT process. Status = No Success
See replication document here.
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 |