EAR 9.2: AR_BNKSTMT AE Program Abended At Step AR_PAYLOAD.BSHDR.BSTPAY (Action SQL) -- RC = 12899, Value Too Large For Column "PS_PD_PAYMNT_TAO4"."PAYMENT_ID" (Doc ID 2254168.1)

Last updated on APRIL 11, 2017

Applies to:

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

Symptoms

ISSUE:

Bank Statement lines which have references longer than 15 characters in length, will cause the Load Bank Statements AE Program (AR_BNKSTMT) to abend at Step AR_PAYLOAD.BSHDR.BSTPAY. From the analysis performed, there seems to be a difference in the length of the below 2 Fields:

     - Field RECON_REF_ID is 20 character in length.

     - Field PAYMENT_ID is only 15 character in length.

The value stored within Record Field PS_BANK_STMT_TBL.RECON_REF_ID will be eventually inserted into Record Field PS_PD_PAYMNT_TAO4.PAYMENT_ID, and should the Reconciliation Reference ID have more than 15 characters, it would cause the process to abend, because the target Field cannot accommodate the whole value.

REPLICATION STEPS:

     1.- Enter Bank Statement where lines have reference in length of 16 or more characters transaction code 'Trade Receipt'
     2.- Run Automatic Reconciliation to generate 'Not Found in System' exception
     3.- Run Load Bank Statements process, and it abends in Error Message

To gather more information concerning this scenario and its related problem, refer to the available Replication Steps Word Document here linked containing the complete configuration and the replication steps necessary to reproduce the issue.

ERROR MESSAGE:

     " -- 18:50:14.374 ....(AR_PAYLOAD.BSHDR.BSTPAY) (SQL)
       INSERT INTO PS_PD_PAYMNT_TAO4 (PROCESS_INSTANCE, LB_BANK_TRANS_ID, LOCKBOX_ID, LOCKBOX_BATCH_ID, PAYMENT_SEQ_NUM, EIP_CTL_ID, BNK_ID_NBR, BANK_ACCOUNT_NUM, RECON_CYCLE_NBR, RECORD_SEQ_NUMBER, DEPOSIT_BU, DEPOSIT_ID, PAYMENT_ID, PAYMENT_AMT, PAYMENT_STATUS, PAYMENT_METHOD, ENTRY_DT, ACCOUNTING_DT, ID_SEQ_CUST, ID_SEQ_ITEM, AMT_SEL, AMT_ADJ, AMT_REM, PP_SW, LOCKBOX_PYMNT_ID, PAYMENT_CURRENCY, PYMT_RT_TYPE, PYMT_RATE_MULT, PYMT_RATE_DIV, CURRENCY_CD, CASH_CNTRL_USE, CASH_CNTRL_DONE, REF_LEVEL, STTLMNT_DT_EST, DOC_TYPE, DOC_SEQ_NBR, DOC_SEQ_DATE, DOC_SEQ_STATUS, DFI_ID_QUAL, DFI_ID_NUM, EDI_TRACE_NBR, DATA_SOURCE, PAY_DUPLICATE, MATCH, ITEM_SW, CUST_SW, REMIT_FROM_SETID, REMIT_FROM_CUST_ID, MICR_ID, BANK_CURRENCY, RECEIVED_DT, BUSINESS_UNIT_GL, DEPOSIT_TYPE, BANK_SETID, BANK_CD, BANK_ACCT_KEY, BAD_CURR_FLG, DEPOSIT_BU_BNK, CASHCNTL_FLAG, ENTRY_EVENT, MISC_PAYMENT, ENTERED_DTTM, OPRID, LAST_UPDATE_DTTM, OPRID_LAST_UPDT) SELECT DISTINCT 115439 , ' ' , ' ' , ' ' , 0 , ' ' , STMT.BNK_ID_NBR , STMT.BANK_ACCOUNT_NUM , STMT.RECON_CYCLE_NBR , STMT.RECORD_SEQ_NUMBER , ACCT.DEPOSIT_BU , ' ' , STMT.RECON_REF_ID , STMT.RECON_TRAN_AMT , 'U' , STMT_CD.PYMNT_METHOD , TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') , STMT.RECON_BANK_DT , 0 , 0 , 0 , 0 , 0 , ACCT.PP_SW , ' ' , ACCT.CURRENCY_CD , ACCT.CUR_RT_TYPE , 1 , 1 , GL.BASE_CURRENCY ,ACCT.CASH_CNTL_USE_FLG , 'N' , 'S' , STMT.RECON_BANK_DT , ' ' , ' ' , TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') , ' ' , ' ' , '0' , 0 , 'BST' , 'N' , 'N' , 'N' , 'N' , ' ' , ' ' , ' ' , ACCT.CURRENCY_CD , TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') , AR.BUSINESS_UNIT_GL , ACCT.DEPOSIT_TYPE , BNK.SETID , BNK.BANK_CD , ACCT.BANK_ACCT_KEY , ' ' , ACCT.DEPOSIT_BU , ' ' , ' ' , 'N' , CAST(SYSTIMESTAMP AS TIMESTAMP) , 'VP1' , CAST(SYSTIMESTAMP AS TIMESTAMP) , 'VP1' FROM PS_PD_BNSTMT_TAO4 TAO , PS_BANK_STMT_TBL STMT , PS_BANK_CD_TBL BNK , PS_BANK_ACCT_TBL ACCT , PS_BUS_UNIT_TBL_AR AR , PS_BUS_UNIT_TBL_GL GL , PS_STMT_CODE_TBL STMT_CD WHERE STMT.RECON_TRANS_CODE='C' AND STMT.RECON_STATUS='NTF' AND BNK.BANK_TYPE='E' AND ACCT.AR='Y' AND TAO.PROCESS_INSTANCE = 115439 AND STMT.BNK_ID_NBR=TAO.BNK_ID_NBR AND STMT.BANK_ACCOUNT_NUM=TAO.BANK_ACCOUNT_NUM AND STMT.RECON_CYCLE_NBR=TAO.RECON_CYCLE_NBR AND STMT.BNK_ID_NBR=BNK.BNK_ID_NBR AND STMT.BANK_ACCOUNT_NUM=ACCT.BANK_ACCOUNT_NUM AND STMT.BANK_STMT_CODE = STMT_CD.BANK_STMT_CODE AND BNK.SETID=ACCT.SETID AND BNK.BANK_CD=ACCT.BANK_CD AND ACCT.DEPOSIT_BU = AR.BUSINESS_UNIT AND GL.BUSINESS_UNIT=AR.BUSINESS_UNIT_GL
       /
       -- Row(s) affected: 0
       -- 18:50:14.653 Process 115439 ABENDED at Step AR_PAYLOAD.BSHDR.BSTPAY (Action SQL) -- RC = 12899
       ROLLBACK
       /
       -- 18:50:14.677 SQL Error: ORA-12899: value too large for column "EMDBO"."PS_PD_PAYMNT_TAO4"."PAYMENT_ID" (actual: 16, maximum: 15) "

ACTUAL RESULT:

The Load Bank Statements AE Program abends, and prevents the Bank Statement Transaction Lines to be generated as new Deposit Groups, with Payment information.

EXPECTED BEHAVIOR:

The new Deposit Group Payment ID should only contain the first 15 characters of the Reconciliation Reference ID from Record Field PS_BANK_STMT_TBL.RECON_REF_ID.

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