EGL: 8.9 To 9.1 Upgrade Data Conversion ABENDED at Step UPG_GL.GLZ01.Step01 Error ORA-01407: Cannot Update ("SMUADM"."PS_JRNL_LN"."PC_DISTRIB_STATUS") to NULL. (Doc ID 1090084.1)

Last updated on SEPTEMBER 14, 2016

Applies to:

PeopleSoft Enterprise FIN General Ledger - Version 9.1 and later
Information in this document applies to any platform.
****Checked for Currency on Jan-29-2013****
****Checked for Currency on Sept-05-2013****

Symptoms

Upgrade path: FSCM 8.9/PT 8.49 --->> FSCM 9.1/PT 8.50. (INIT pass).

Task 5-12-3 Performing Data Conversion Concurrently, process ABENDED at Step UPG_GL.GLZ01.Step01 with ORA-01407 error.

Error
---------------
SQL error. Stmt #: 1625 Error Position: 22 Return: 1407 - ORA-01407: cannot update ("SMUADM"."PS_JRNL_LN"."PC_DISTRIB_STATUS") to NULL
Failed SQL stmt:UPDATE PS_JRNL_LN SET PC_DISTRIB_STATUS = ( SELECT H.PC_DISTRIB_STATUS FROM PS_JRNL_HEADER H WHERE H.BUSINESS_UNIT = PS_JRNL_LN.BUSINESS_UNIT AND H.JOURNAL_ID = PS_JRNL_LN.JOURNAL_ID AND H.JOURNAL_DATE = PS_JRNL_LN.JOURNAL_DATE AND H.UNPOST_SEQ = PS_JRNL_LN.UNPOST_SEQ )

- Except this abend, no other error is observed in the data conversion log.
- If running same sql in a DB SQL query tool, same error returns.

Steps
---------------
By following these steps the issue can be reproduced On-Site or DEMO:
1. Upgrade FSCM 8.9 on PT 8.49 to FSCM 9.1 PT 8.50
2. Perform task 5-12-3 data conversion job at initial pass.
3. Data conversion abend at Step UPG_GL.GLZ01.Step01 with ORA-01407 error.

Changes

UPG_GL.GLZ01.Step01 is a new AE step introduced in the data conversion process for upgrade path to 9.1.

This step copies field value of PC_DISTRIB_STATUS from table JRNL_HEADER to table JRNL_LN.

PC_DISTRIB_STATUS is a new record field in JRNL_LN from release 9.1. Its value is populated by Upgrade Data Conversion program based on the value from same field in PS_JRNL_HEADER. This is becasue per record/field definition, NULL value is not allowed for this field.

The change is introduced to enable customers to create journals that pass transactions to Project Costing at individual line level.

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