EGL9.2: After Upgrading To 9.2, GLPPPOST COBOL SQL Program Fails At GLPPUNPS_I_OITEMP with Error "Column not found" When Trying To Unpost A Journal (Doc ID 2009399.1)

Last updated on SEPTEMBER 14, 2016

Applies to:

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

Symptoms

On : 9.2 version, Journal Post

GLPPPOST COBOL SQL Program is failing with below error after upgrading to 9.2 Image 5 when attempting to unpost a journal.

ERROR
-----------------------
Application Program Failed
 Action Type : SQL UPDATE
 In Pgm Section : SQLRT: EXECUTE-STMT
 With Return Code: 08010
 Error Message : Column not found
 Dynamic Stmt : GLPPUNPS_I_OITEMP
 SQL Statement : INSERT INTO PS_GL_OI_TMP (BUSINESS_UNIT,JOURNAL_ID,JOURNAL_D
  ATE,UNPOST_SEQ,JOURNAL_LINE,LEDGER,SPEEDCHART_KEY,SPEEDTYPE_
  KEY,ACCOUNT,ALTACCT,DEPTID,OPERATING_UNIT,PRODUCT,FUND_CODE,
  CLASS_FLD,PROGRAM_CODE,BUDGET_REF,AFFILIATE,AFFILIATE_INTRA1
  ,AFFILIATE_INTRA2,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,BOOK_C
  ODE,GL_ADJUST_TYPE,BUDGET_PERIOD,SCENARIO,CURRENCY_CD,BUSINE
  SS_UNIT_PC,PROJECT_ID,ACTIVITY_ID,RESOURCE_TYPE,RESOURCE_CAT
  EGORY,RESOURCE_SUB_CAT,ANALYSIS_TYPE,MONETARY_AMOUNT,MOVEMEN
  T_FLAG,LINE_DESCR,FOREIGN_CURRENCY,FOREIGN_AMOUNT,OPEN_ITEM_
  KEY,OPENITEM_RECON_NBR,OPEN_ITEM_STATUS,OPEN_DT,DOC_SEQ_NBR,
  PROCESS_INSTANCE) SELECT O.BUSINESS_UNIT,O.JOURNAL_ID,O.JOUR
  NAL_DATE,O.UNPOST_SEQ + 1,O.JOURNAL_LINE,O.LEDGER,O.SPEEDCHA
  RT_KEY,O.SPEEDTYPE_KEY,O.ACCOUNT,O.ALTACCT,O.DEPTID,O.OPERAT
  ING_UNIT,O.PRODUCT,O.FUND_CODE,O.CLASS_FLD,O.PROGRAM_CODE,O.
  BUDGET_REF,O.AFFILIATE,O.AFFILIATE_INTRA1,O.AFFILIATE_INTRA2
  ,O.CHARTFIELD1,O.CHARTFIELD2,O.CHARTFIELD3,O.BOOK_CODE,O.GL_
  ADJUST_TYPE,O.BUDGET_PERIOD,O.SCENARIO,O.CURRENCY_CD,O.BUSIN
  ESS_UNIT_PC,O.PROJECT_ID,O.ACTIVITY_ID,O.RESOURCE_TYPE,O.RES
  OURCE_CATEGORY,O.RESOURCE_SUB_CAT,O.ANALYSIS_TYPE,O.MONETARY
  _AMOUNT,O.MOVEMENT_FLAG,O.LINE_DESCR,O.FOREIGN_CURRENCY,O.FO
  REIGN_AMOUNT,O.OPEN_ITEM_KEY,O.OPENITEM_RECON_NBR,O.OPEN_ITE
  M_STATUS,O.JOURNAL_DATE,O.DOC_SEQ_NBR,0001282986 FROM PS_JRN
  L_HDR_TMP T, PS_OPEN_ITEM_GL O WHERE O.JOURNAL_ID = T.JOURNA
  L_ID AND O.JOURNAL_DATE = T.JOURNAL_DATE AND O.UNPOST_SEQ =
  T.UNPOST_SEQ AND O.BUSINESS_UNIT = T.BUSINESS_UNIT AND T.PRO
  CESS_INSTANCE = 0001282986
 
 
> 2015-04-22-15.22.11.380000 SEVERE ERROR IN PGM:(5830,7) PI(1282986) Program(GLPPUNPS)
PSPSQLRT error in Posting: GLPPUNPS-FJ000 INSERT OI TMP.

ANALYSIS
--------------------------
The reason for this SQL error is that the SPEEDCHART_KEY and SPEEDTYPE_KEY fields do not exist in either the PS_GL_OI_TMP table nor the PS_OPEN_ITEM_GL table.
It is verified these tables are identical to the DEMO 9.2 environment so the tables are built correctly.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Upgrade to 9.2 Image 5.
2. Run test on Journal Post and Unpost GLPPPOST COBOL program.
3. Journal Unpost fails with the reported error.

Changes

 Upgrade to 9.2.

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