EGL92: Ledger Close for Closing Rule that uses BUDGET Ledger Template Fails With SQLRT error in GLPCPANL-FA000 INSERT (Doc ID 1906646.1)

Last updated on JUNE 23, 2016

Applies to:

PeopleSoft Enterprise FIN General Ledger - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

When attempting to run ledger Close, the following error occurs.

ERROR
-----------------------
Application Program Failed
Action Type : SQL UPDATE
In Pgm Section : SQLRT: EXECUTE-STMT
With Return Code: 00904
Error Message : ORA-00904: "A"."POSTED_TRAN_CR": invalid identifier
Dynamic Stmt : GLPCPANL_I_FA000
SQL Statement : INSERT INTO PS_CLO_LEDG_TMP001 (BUSINESS_UNIT,LEDGER,ACCOUNT
  ,ALTACCT,DEPTID,OPERATING_UNIT,PRODUCT,FUND_CODE,CLASS_FLD,P
  ROGRAM_CODE,BUDGET_REF,AFFILIATE,AFFILIATE_INTRA1,AFFILIATE_
  INTRA2,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,BOOK_CODE,GL_ADJU
  ST_TYPE,BUDGET_PERIOD,SCENARIO,CURRENCY_CD,PROJECT_ID,STATIS
  TICS_CODE,MONETARY_AMOUNT,FOREIGN_CURRENCY,FOREIGN_AMOUNT,PR
  OCESS_INSTANCE,DATE_CODE,FISCAL_YEAR,ACCOUNTING_PERIOD,POSTE
  D_TOTAL_AMT,POSTED_BASE_AMT,POSTED_TRAN_AMT,BASE_CURRENCY,DT
  TM_STAMP_SEC,BAL_SHEET_IND,CURRENCY_CD1,ADJUSTING_ENTRY,POST
  ED_TOTAL_DR,POSTED_TOTAL_CR,POSTED_TRAN_DR,POSTED_TRAN_CR,CL
  OS_PROC_FLG,SEQNUM,CF_GROUP_NBR,PERIOD_SEQUENCE) SELECT 'TUF
  TS',A.LEDGER,A.ACCOUNT,A.ALTACCT,A.DEPTID,A.OPERATING_UNIT,A
  .PRODUCT,A.FUND_CODE,A.CLASS_FLD,A.PROGRAM_CODE,A.BUDGET_REF
  ,A.AFFILIATE,A.AFFILIATE_INTRA1,A.AFFILIATE_INTRA2,A.CHARTFI
  ELD1,A.CHARTFIELD2,A.CHARTFIELD3,A.BOOK_CODE,A.GL_ADJUST_TYP
  E,A.BUDGET_PERIOD,A.SCENARIO,A.CURRENCY_CD,A.PROJECT_ID,A.ST
  ATISTICS_CODE, 0,' ', 0.0,0002017500,' ',2014, 0, -SUM(A.POS
  TED_TOTAL_AMT), -SUM(A.POSTED_BASE_AMT), -SUM(A.POSTED_TRAN_
  AMT),A.BASE_CURRENCY,TO_TIMESTAMP('2014-05-29-11.56.51.15000
  0', 'YYYY-MM-DD-HH24.MI.SS.FF'),' ',' ',' ', 0.0, 0.0, -SUM(
  A.POSTED_TRAN_DR), -SUM(A.POSTED_TRAN_CR), 0, 0, 0, 0 FROM P
  S_LEDGER_BUDG A,PS_CLO_ACCT_TMP001 C WHERE A.BUSINESS_UNIT='
  TUFTS' AND A.LEDGER='PROJ_GRT' AND A.FISCAL_YEAR=2014 AND A.
  ACCOUNTING_PERIOD>=1 AND A.ACCOUNTING_PERIOD  UNT=C.ACCOUNT AND C.BALANCE_FWD_SW='N' AND C.STATISTICS_ACCO
  UNT='N' GROUP BY A.LEDGER,A.CURRENCY_CD,A.ACCOUNT,A.ALTACCT,
  A.DEPTID,A.OPERATING_UNIT,A.PRODUCT,A.FUND_CODE,A.CLASS_FLD,
  A.PROGRAM_CODE,A.BUDGET_REF,A.AFFILIATE,A.AFFILIATE_INTRA1,A
  .AFFILIATE_INTRA2,A.CHARTFIELD1,A.CHARTFIELD2,A.CHARTFIELD3,
  A.PROJECT_ID,A.BUDGET_PERIOD,A.SCENARIO,A.BOOK_CODE,A.GL_ADJ
  UST_TYPE,A.STATISTICS_CODE,A.BASE_CURRENCY,C.BOOK_CODE


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Navigate to General Ledger>Close Ledgers>Closing Rules
2. Create Closing Rule for BUDGET Ledger template
3. Add Closing Rule to Process Group
4. Request Ledger Close ensure Ledger Group is Budget Ledger
5. Process fails with SQLRT error in GLPCPANL-FA000 INSERT

 

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