EEX9.1+: AP_FG_PROC Process Fails with Unique Constraint Error on PS_AP_FGDTL_TMP5 at Step AP_FG_PROC.LOADSTG2.Step02 (SQL) (Doc ID 2124850.1)

Last updated on JUNE 01, 2023

Applies to:

Symptoms

The AP_FG_PROC process is failing with Unique Constraint error.

Steps:
-----------
1) Create 2 Expense Report for an Employee.
2) Stage the Expense Reports for Payment.
3) Run Pay Cycle.
4) Run AP_FG_PROC.
5) Process fails with following error message:

[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cannot insert duplicate key row in object 'dbo.PS_AP_FGDTL_TMP4' with unique index 'PS_AP_FGDTL_TMP4'. The duplicate key value is (18914, SHARE, USBNK, CHCK, 0000010073).
Failed SQL stmt: INSERT INTO PS_AP_FGDTL_TMP4 (BANK_SETID , BANK_CD , BANK_ACCT_KEY , PYMNT_ID , PAY_CYCLE , PAY_CYCLE_SEQ_NUM , PMT_MSG_ID , PMT_SEQ_NUM , PMT_SOURCE , BUSINESS_UNIT , AMOUNT , AMOUNT_CURRENCY_CD , PYMNT_DT , VALUE_DT , PMT_STATUS , PMT_TYPE , FROM_BNK_ID_NBR , FROM_BNK_ACCT_NUM , FROM_DFI_ID_NUM , FROM_DFI_ID_QUAL , PYMNT_METHOD , FORMAT_ID , PRENOTE_FLAG , PAY_IMMEDIATELY , COUNTRY , BANK_ID_QUAL , BNK_ID_NBR , BRANCH_ID , BANK_ACCT_TYPE , BANK_ACCOUNT_NUM , CHECK_DIGIT , DFI_ID_QUAL , DFI_ID_NUM , BANK_NAME , BANK_NAME_AC , BRANCH_DESCR , BRANCH_NAME_AC , IBAN_CHECK_DIGIT , IBAN_ID , PARTY_ID_TYPE , PARTY_SETID , PARTY_ID , VNDR_LOC , NAME1 , NAME1_AC , NAME2 , STD_ID_NUM , STD_ID_NUM_QUAL , EFT_PYMNT_FMT_CD , EFT_RTNG_FLG , EFT_RTNG_STATUS , EFT_TRANS_HANDLING , EFT_DOM_COSTS_CD , EFT_CORR_COSTS_CD , EFT_CROSSED_CHECK , EFT_CHECK_FWRD_CD , EFT_CHECK_DRAW_CD , EFT_PAY_INST_CD1 , EFT_PAY_INST_CD2 , EFT_PAY_INST_CD3 , EFT_PAY_INST_CD4 , EFT_PAY_INST_REF1 , EFT_PAY_INST_REF2 , ADDENDA_VALUE , REPETITVE_TRNFR_CD , DRAWDOWN_FLG , IN_PROCESS_FLG , PROCESS_INSTANCE , BATCH_NAME , SRC_REF_ID , PYMNT_ID_REF , ESTIMATED_DUE_DATE , CREATED_BY_USER , SINGLE_PYMNT_FLG) SELECT A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.PYMNT_ID , A.PAY_CYCLE , A.PAY_CYCLE_SEQ_NUM , ' ' , 0 , 'EX' , ' ' , A.PYMNT_AMT , A.CURRENCY_PYMNT , A.PYMNT_DT , A.VALUE_DT , 'N' , A.SOURCE_TXN , ' ' ,' ' ,' ' ,' ' ,A.PYMNT_METHOD , A.EFT_LAYOUT_CD , S.PRENOTE_FLAG , ' ' , B.COUNTRY , B.BANK_ID_QUAL , B.BNK_ID_NBR , B.BRANCH_ID , B.BANK_ACCT_TYPE , A.REMIT_BANK_ACCOUNT , B.CHECK_DIGIT , B.DFI_ID_QUAL , B.DFI_ID_NUM , B.BENEFICIARY_BANK , B.BENEFICIARY_BNK_AC , B.BENEF_BRANCH , B.BENEF_BRANCH_AC , B.IBAN_CHECK_DIGIT , B.IBAN_ID , 'EMPL' , ' ' , A.EMPLID , ' ' , A.NAME1 , ' ' , A.NAME2 , ' ' , ' ' , D.EFT_PYMNT_FMT_CD , D.EFT_RTNG_FLG , D.EFT_RTNG_STATUS , D.EFT_TRANS_HANDLING , D.EFT_DOM_COSTS_CD , D.EFT_CORR_COSTS_CD , D.EFT_CROSSED_CHECK , D.EFT_CHECK_FWRD_CD , D.EFT_CHECK_DRAW_CD , D.EFT_PAY_INST_CD1 , D.EFT_PAY_INST_CD2 , D.EFT_PAY_INST_CD3 , D.EFT_PAY_INST_CD4 , D.EFT_PAY_INST_REF1 , D.EFT_PAY_INST_REF2 , ' ' ,' ' ,' ' ,' ' ,18914 , S.BATCH_ID , A.PYMNT_ID_REF , A.PYMNT_ID_REF , A.STTLMNT_DT_EST , ' ' , S.SINGLE_PYMNT_FLG FROM PS_AP_FGPMT_TMP4 S , PS_PAYMENT_TBL A , PS_EX_EE_PYMNT_DTL B , PS_EX_EE_ORG_DTL C , PS_EX_EE_ORG_DTA D , PS_EX_SUMM_PYMNT E WHERE S.PROCESS_INSTANCE = 18914 AND S.SOURCE_TXN IN ('EXPN','EXAD') AND S.BANK_SETID = A.BANK_SETID AND S.BANK_CD = A.BANK_CD AND S.BANK_ACCT_KEY = A.BANK_ACCT_KEY AND S.PYMNT_ID = A.PYMNT_ID AND S.REMIT_VENDOR = ' ' AND A.EMPLID = C.EMPLID AND C.DFLT_EE_PROF_FLG = 'Y' AND B.EMPLID = C.EMPLID AND B.EMPL_RCD = C.EMPL_RCD AND B.DFLT_ACCT_FLG = 'Y' AND B.BANK_ACCOUNT_NUM <> ' ' AND A.EMPLID = D.EMPLID AND E.PYMNT_ID = A.PYMNT_ID AND E.EMPLID = C.EMPLID AND E.EMPL_RCD = B.EMPL_RCD

Process 18914 ABENDED at Step AP_FG_PROC.LOADSTG2.Step02 (SQL) -- RC = 805 (108,524)

Error in Oracle Database:

ORA-00001: unique constraint (EMDBO.PS_AP_FGDTL_TMP5) violated Failed SQL stmt: INSERT INTO PS_AP_FGDTL_TMP5 (BANK_SETID , BANK_CD , BANK_ACCT_KEY , PYMNT_ID , PAY _CYCLE , PAY_CYCLE_SEQ_NUM , PMT_MSG_ID , PMT_SEQ_NUM , PMT_SOURCE , BUSINESS_UNIT , AMOUNT , AMOUNT _CURRENCY_CD , PYMNT_DT , VALUE_DT , PMT_STATUS , PMT_TYPE , FROM_BNK_ID_NBR , FROM_BNK_ACCT_NUM , FROM_DFI_ID_NUM , FROM_DFI_ID_QUAL , PYMNT_METHOD , FORMAT_ID , PRENOTE_FLAG , PAY_IMMEDIATELY , COUN TRY , BANK_ID_QUAL , BNK_ID_NBR , BRANCH_ID ,
BANK_ACCT_TYPE , BANK_ACCOUNT_NUM , CHECK_DIGIT , DFI_ ID_QUAL , DFI_ID_NUM , BANK_NAME , BANK_NAME_AC , BRANCH_DESCR , BRANCH_NAME_AC , IBAN_CHECK_DIGIT , IBAN_ID , PARTY_ID_TYPE , PARTY_SETID , PARTY_ID , VNDR_LOC , NAME1 , NAME1_AC , NAME2 , STD_ID_NUM

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.