HCM Upgrade - ORA-00904: "CNT_PT_PCT_ESP": invalid identifier error during Upgrade from HCM 9.0 to HCM 9.2 (Doc ID 2295654.1)

Last updated on AUGUST 09, 2017

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

When Customers are upgrading from HCM 9.0 to 9.2 , they are getting the following error :


ERROR
-----------------------
1.Stmt #: 1708 Error Position: 1694 Return: 904 - ORA-00904: "CNT_PT_PCT_ESP": invalid identifier
Failed SQL stmt: INSERT INTO PS_WKF_CNT_TYP_ESP (EMPLID ,CONTRACT_NUM ,EFFDT ,EFFSEQ ,CNT_ACTION_ESP ,CONTRACT_TYPE ,CNT_TEMPLATE_ID ,SETID ,CNT_TEMPLT_ID_ESP ,CNT_BGN_DT_ESP ,CNT_ORIG_BGNDT_ESP ,CNT_END_DT_ESP ,CNT_DURATION_ESP ,CNT_DURTN_UNIT_ESP ,CNT_T_DURATION_ESP ,EXT_BGN_DT_ESP ,EXT_END_DT_ESP ,EXT_DURATION_ESP ,EXT_T_DURATION_ESP ,EXT_DURTN_UNIT_ESP ,EXT_NUMBER_ESP ,CONTRACT_ID_ESP ,BC_MANAGEMNT_ESP ,BC_SIGN_TYPE_ESP ,CNT_INEM_DT_ESP ,CTTA_CONTRACT_ESP ,CTTA_EXTENSION_ESP ,CTTA_BASICCOPY_ESP ,CTTA_TRANSFORM_ESP ,CTTA_GROUP_ESP ,CTTA_CALLING_ESP ,XML_NODE_ID ,SERV_PRDTN_RSN_ESP ,BONIF_RSN_ESP ,BONIF_GROUP_ESP ,PRODCTN_RSN_ESP ,CNT_PROMOTION_ESP ,SUBSTN_RSN_ESP ,SUBSTN_EMPLID_ESP ,SUBSTN_JOBCODE_ESP ,RELV_CONTRCT_ESP ,RLV_WRK_TYPE_ESP ,COMPLMNTRY_HRS_ESP ,LAB_AGRMNT_OK_ESP ,PART_TIME_PERC_ESP ,CNT_SCHED_BGN_ESP ,CNT_SCHED_END_ESP ,CNT_SCHED_DIST_ESP ,CNT_VACATION_ESP ,VAC_DURTN_ESP ,VAC_DURTN_UNIT_ESP ,CNT_PROBATION_ESP ,PRB_DURTN_ESP ,PRB_DURTN_UNIT_ESP ,HIRE_CTR_CD_ESP ,COMP_RATE_DESCR ,SCHEME_ID_ESP ,CONTRIB_ID_ESP ,CNT_TRANSFORMT_ESP ,PART_TIME_HRS_ESP ,PART_TIME_MINS_ESP ,PART_TIME_FRQ_ESP ,PT_MAX_HRS_ESP ,PT_INTERMITTNT_ESP ,SCHD_HRS_TYPE_ESP ,LINKED_TO_AFI_ESP ,REPRESNT_ID_ESP ,LOCATN_CITY_CD_ESP ,COMPNY_CITY_CD_ESP ,CITY_CD_ESP ,TUTOR_ID_ESP ,REPRESNT_ROLE_ESP ,SIGNATURE_CITY ,SIGNATURE_DT ,CNT_LESS7DAYS_ESP ,STD_HOURS ,CNT_CHANGE_FLG_ESP ,CNT_TUTOR_FLG_ESP ,LOCATN_ADDRESS_ESP ,CNT_ADDTNL_CND_ESP ,ANEX_MANAGEMNT_ESP ,EMPLMNT_OFF_CD_ESP ,COMPLM_MAX_HRS_ESP ,COMPLM_PCT_HRS_ESP ,NOTIFCTN_DAYS_ESP ,EE_PREV_STATUS_ESP ,STAFF_TYPE_ESP ,CNT_BONIF_FLG_ESP ,CNT_ADDTNL_FLG_ESP ,CNO_ESP ,CNT_ADTNL_INFO_ESP ,CNT_TEMPLT_CLS_ESP ,RETIRED_EMPLID_ESP ,YWS_REG_DT_ESP ,BONIF_AMOUNT_ESP ,CNT_PT_PCT_ESP ,BC_TEXT_ESP) SELECT DISTINCT A.EMPLID , A.CONTRACT_NUM , A.EFFDT , 0 , CASE WHEN A.EXTEND_CONTRACT_SW = 'Y' THEN '003' ELSE '001' END , A.CONTRACT_TYPE , ' ' , 'ESP' , ' ' ,A.EFFDT ,A.EFFDT ,NULL ,' ' ,'M' ,' ' , CASE WHEN A.EXTEND_CONTRACT_SW = 'Y' THEN A.EFFDT ELSE NULL END , CASE WHEN A.EXTEND_CONTRACT_SW = 'Y' THEN TO_DATE('3000-01-01','YYYY-MM-DD') ELSE NULL END ,' ' ,' ' ,'M' ,' ' ,' ' ,' ' ,' ' ,NULL ,'N' ,'N' ,'N' ,'N' ,'N' ,'N' ,' ' ,' ' ,' ' ,' ' ,' ' ,'N' ,' ' ,' ' ,' ' ,'N' ,' ' ,' ' ,' ' ,0 ,' ' ,' ' ,' ' ,' ' ,30 ,'N' ,' ' ,7 ,'D' ,' ' ,' ' ,'0111' ,'001' ,' ' ,' ' ,' ' ,' ' ,0 ,'N' ,' ' ,'0' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,NULL ,' ' ,0 ,'N' ,'N' ,' ' ,' ' ,'N' ,' ' ,0 ,0 ,0 ,' ' ,' ' ,'N' ,'N' ,' ' ,' ' ,' ' ,' ' ,NULL ,0 ,0 ,' ' FROM PS_WKF_CNT_TYPE A , PS_CONTRACT_DATA B WHERE A.EMPLID = B.EMPLID AND A.CONTRACT_NUM = B.CONTRACT_NUM AND B.REG_REGION = 'ESP' AND NOT EXISTS ( SELECT 'X' FROM PS_WKF_CNT_TYP_ESP A1 WHERE A1.EFFDT = A.EFFDT AND A1.EMPLID = A.EMPLID AND A1.CONTRACT_NUM = A.CONTRACT_NUM)

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Data Conversion Step


Changes

 

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