EGL9.2: Ledger Close For Closing Rule With BUDGET Ledger Template Fails With SQLRT Error In GLPCPANL-FA000 INSERT (Doc ID 1906646.1)

Last updated on AUGUST 29, 2023

Applies to:

Symptoms

Issue:
-------

When attempting to run ledger Close for ledger with budget ledger template an 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

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.