EGL : Equitization Program Fails With Date Code Error - ORA-01400: cannot insert NULL into ("PSOFT"."PS_EQTZPOOL_TMP001"."DATE_CODE") (Doc ID 1594271.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, Consolidation and Equitization

Equitization program fails with below Date code error. The date Code is a new field in Ledger table in 9.2.

ERROR
-----------------------
Program(GLPQPROC)
Processing Ownership Set 15V-15U .....
 
Application Program Failed
Action Type : SQL UPDATE
In Pgm Section : SQLRT: EXECUTE-STMT
With Return Code: 01400
Error Message : ORA-01400: cannot insert NULL into ("PSOFT"."PS_EQTZPOOL_TMP001"."DATE_CODE")
Dynamic Stmt : GLPQPOOL_I_POOLTMP
SQL Statement :
INTO PS_EQTZPOOL_TMP001
  (
    PROCESS_INSTANCE,
    LEDGER,
    BUSI NESS_UNIT,
    ACCOUNT,
    ALTACCT,
    DEPTID,
    OPERATING_UNIT,
    PRODUCT,
    FUND _CODE,
    CLASS_FLD,
    PROGRAM_CODE,
    BUDGET_REF,
    AFFILIATE,
    AFFILIATE_ INTRA1,
    AFFILIATE_INTRA2,
    CHARTFIELD1,
    CHARTFIELD2,
    CHARTFIELD3,
    PROJECT_ID,
    BOOK_CODE,
    GL_ADJUST_TYPE,
    CURRENCY_CD,
    STATISTICS_C ODE,
    POSTED_TOTAL_AMT,
    POSTED_BASE_AMT,
    BASE_CURRENCY
  )
SELECT 0 003273836,
  'ACTUALS',
  A.BUSINESS_UNIT,
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  A.BOOK_CODE,
  A.GL_ADJ UST_TYPE,
  A.CURRENCY_CD,
  A.STATISTICS_CODE,
  SUM(A.POSTED_TOTAL_ AMT),
  SUM(A.POSTED_TOTAL_AMT),
  'USD'
FROM PS_LEDGER A,
  PS_CFV_S EL006 B,
  PS_CFV_SEL006 C
WHERE A.BUSINESS_UNIT      ='15V'
AND A.LE DGER              ='ACTUALS'
AND A.FISCAL_YEAR          =2013
AND (A.ACCOUNTING_PERI OD >= 001
AND A.ACCOUNTING_PERIOD 'USD'
AND A.STATISTICS_CODE=' '
AND B.CFV_SET        ='EQTY_SRC_EQTY  
PICKUP'
AND B.CHARTFIELD     ='ACCOUNT'
AND B.SETID          ='NYK'
AND A.A CCOUNT       =B.CHARTFIELD_VALUE
AND C.CFV_SET        ='EQTY_SRC_EQTYPICKUP  
'
AND C.CHARTFIELD     ='BOOK_CODE'
AND C.SETID          ='NYK'
AND A.BOOK_ CODE     =C.CHARTFIELD_VALUE
GROUP BY A.BUSINESS_UNIT,A.BOOK_CODE ,A.GL_ADJUST_TYPE,A.CURRENCY_CD,A.STATISTICS_CODE

Sometimes the error may just indicate:  SQLRT error in GLPQPOOL-JA000 INSERT POOL TMP  without mentioning the DATE_CODE field.

The same error may be encountered when running consolidations or may be with other processes which creates journal(s) -

Application Program Failed
Action Type     : SQL UPDATE
In Pgm Section  : SQLRT: EXECUTE-STMT                                 
With Return Code: 01400
Error Message   : ORA-01400: cannot insert NULL into ("FNSYSADM"."PS_CONS_EQT_TMP002"."DATE_CODE")
Dynamic Stmt   : GLPOEQAC_I_EQTYTMP
SQL Statement :          

INSERT
INTO PS_CONS_EQT_TMP002
  (
    PROCESS_INSTANCE,
    REQUEST_NBR,
    SUB_ENTITY,
    OWNERSHIP_SET,
    ACCOUNT,
    ALTACCT,
    DEPTID,
    OPERATING_U NIT,
    PRODUCT,
    FUND_CODE,
    CLASS_FLD,
    PROGRAM_CODE,
    BUDGET_REF,
    AFFI LIATE,
    AFFILIATE_INTRA1,
    AFFILIATE_INTRA2,
    CHARTFIELD1,
    CHARTFIE LD2,
    CHARTFIELD3,
    PROJECT_ID,
    BOOK_CODE,
    GL_ADJUST_TYPE,
    CURRENCY _CD,
    STATISTICS_CODE,
    POSTED_TOTAL_AMT
  )
SELECT 0002227134,0001 ,
  'GLUSP',
  'SUBSI-I2A',
  A.ACCOUNT,
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  '                    
',
  A.AFFILIATE,
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  ' ',
  A.BOOK_CODE,
  A.GL_ADJU ST_TYPE,
  'USD',
  ' ',
  SUM(A.POSTED_TOTAL_AMT)
FROM PS_LEDGER A,
  P S_CFV_SEL001 B
WHERE A.BUSINESS_UNIT     ='GLUSP'
AND A.LEDGER              ='T                   
RNSL_USD'
AND A.FISCAL_YEAR         =2013
AND (A.ACCOUNTING_PERIOD >= 000
AND A.ACCOUNTING_PERIOD  <= 010)
AND A.CURRENCY_CD         ='USD'
AND A.STATISTICS_CODE     =' '
AND B.CFV_SET             ='EQUITY'
AND B.CHA RTFIELD         ='ACCOUNT'
AND B.SETID               ='SHARE'
AND A.ACCOUNT             =B.CHARTF IELD_VALUE
GROUP BY a.account,
  a.AFFILIATE,
  a.BOOK_CODE,
  a.GL_A DJUST_TYPE


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Enable Posting by Date_code options is not selected on the Ledger For A Unit page.
2. Run equitization process.
3. Process fails with above error.

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