EEX 9.0 / 9.1: Expense Process (EX_TRANS_PRCS) Abends at EX_LIBRARY.CF_INS.CF_INS_2 with Unique Constraint Error When Reconciling 2 Cash Advances With Different Set ID (Doc ID 1481106.1)

Last updated on MARCH 16, 2016

Applies to:

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

Symptoms

ISSUE:

Having reconciled several Cash Advances, owned by different Business Units, should those be using different Set ID values in their EX_01 Record Group settings, then the Post Liabilities option from the Expense Processing AE Program (EX_TRANS_PRCS) is abending with a Duplicate Constrain Error Message, preventing users from getting created the necessary Accounting Entries.

REPLICATION STEPS:

     - Navigate to: Travel and Expenses > Maintain Employee Information > Update Profiles
     - Have 2 Employees (KU0021 and KU0096) pertaining to two different GL Business Units, such as US001 and CAN01
     - Navigate to: PeopleTools > Utilities > Administration > TableSet Control
     - Have those Business Units (US001 and CAN01) point to different Set IDs on their EX_01 Record Group (such as SHARE and CAN01)
     - Navigate to: Employee Self-Service > Travel and Expenses Center > Cash Advances > Create
     - Create and submit one Cash Advance for each of the two Employees
     - Approve For Payment both submitted Cash Advances
     - Stage For Payment, and Post Liabilities for both transactions
     - Run the Accounts Payable Pay Cycle to generate respective Payments for the two affected Cash Advances
     - Run the Update Paid Statuses and Post Payments processes
     - Navigate to: Travel and Expenses > Manage Accounting > Reconcile Cash Advance
     - Manually reconcile both Cash Advances by adding respective Cash Advances by adding a new Payment Number for the same amount
     - Navigate to: Travel and Expenses > Process Expenses > Expense Processing
     - Create a new Run Control ID, select the option of 'Post Liabilities' and the two Business Units involved (US001 and CAN01)
     - The Expense Processing Application Engine Program (EX_TRAN_PRCS) 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:

  " (EX_LIBRARY.CF_INS.CF_INS_2) (SQL)
    INSERT INTO PS_EX_ACCTG_TMP4 (PROCESS_INSTANCE, EX_DOC_ID, EX_DOC_TYPE, LINE_NBR, DISTRIB_LINE_NUM, DST_ACCT_TYPE, APPL_JRNL_ID, UNPOST_SEQ, ACCOUNTING_DT, DOC_TYPE, DOC_SEQ_NBR, DOC_SEQ_DATE, ACCOUNTING_PERIOD, BUSINESS_UNIT,  BUSINESS_UNIT_GL, FISCAL_YEAR, JOURNAL_ID, JOURNAL_DATE, JOURNAL_LINE, GL_DISTRIB_STATUS, LEDGER_CATEGORY, LEDGER_GROUP, LEDGER, MONETARY_AMOUNT, CURRENCY_CD, FOREIGN_AMOUNT, FOREIGN_CURRENCY, RATE_MULT, RATE_DIV, RT_TYPE, EMPLID, BI_DISTRIB_STATUS, ACCOUNT, ALTACCT, DEPTID, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, PROJECT_ID, BUSINESS_UNIT_PC, ACTIVITY_ID, RESOURCE_CATEGORY, RESOURCE_TYPE, RESOURCE_SUB_CAT, ANALYSIS_TYPE, PC_DISTRIB_STATUS, VAT_DISTRIB_STATUS, MOVEMENT_FLAG, VAT_BASIS_AMT, VAT_BASIS_AMT_BSE, VAT_CALC_AMT, VAT_CALC_AMT_BSE, VAT_RCVRY_AMT, VAT_RCVRY_AMT_BSE, VAT_REBATE_AMT, VAT_REBATE_AMT_BSE, VAT_TRANS_AMT, VAT_TRANS_AMT_BSE, VAT_AMT, VAT_AMT_BSE, VAT_RCLM_AMT, VAT_RCLM_AMT_BSE, TXN_CURRENCY_CD1, CURRENCY_CD1, IU_ANCHOR_FLG, IU_TRAN_CD, IU_SYS_TRAN_CD, VAT_APPLICABILITY) SELECT  DISTINCT 9718 , A.ADVANCE_ID , 'A' , 0, A.DISTRIB_LINE_NUM, 'RCN' , C.APPL_JRNL_ID_ACCR , 0 , A.ACCOUNTING_DT , A.DOC_TYPE, A.DOC_SEQ_NBR, A.DOC_SEQ_DATE, 0 , A.BUSINESS_UNIT, A.BUSINESS_UNIT_GL, 0 , ' ' , TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') , 0 , 'N' , ' ', ' ' , ' ' , D.AMOUNT , A.CURRENCY_CD, D.AMOUNT , A.CURRENCY_CD , 1 , 1 , B.RT_TYPE , A.EMPLID, 'I' , E.ACCOUNT, E.ALTACCT, E.DEPTID, E.OPERATING_UNIT, E.PRODUCT, E.FUND_CODE, E.CLASS_FLD, E.PROGRAM_CODE, E.BUDGET_REF, E.AFFILIATE, E.AFFILIATE_INTRA1, E.AFFILIATE_INTRA2, E.CHARTFIELD1, E.CHARTFIELD2 ,
E.CHARTFIELD3, E.PROJECT_ID, ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , 'N' , 'I' , 'N', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', 'N', ' ', ' ', ' ' FROM PS_EX_ADV_VW A , PS_BUS_UNIT_TBL_EX C , PS_EX_ONLN_RCN_VW D , PS_BUS_UNIT_OPT_EX B, PS_EX_DSTACT_TAO4 E, PS_BUS_UNIT_TBL_GL F, PS_EXBUSET2_TAO4 G WHERE A.ADVANCE_ID = D.ADVANCE_ID AND C.BUSINESS_UNIT_GL = A.BUSINESS_UNIT_GL AND A.PROCESS_INSTANCE = 9718 AND A.PROCESS_INSTANCE = E.PROCESS_INSTANCE AND A.BUSINESS_UNIT = A.BUSINESS_UNIT_GL AND A.BUSINESS_UNIT_GL = E.BUSINESS_UNIT_GL AND A.DST_CNTRL_ID = E.DST_CNTRL_ID AND F.BUSINESS_UNIT = C.BUSINESS_UNIT_GL AND G.SETID = 'SHARE' AND E.DST_ACCT_TYPE = 'RCN' AND A.POST_STATUS_EX = 'P' AND B.SETID = ( SELECT S.SETID FROM PS_SET_CNTRL_REC S WHERE S.RECNAME = 'BUS_UNIT_OPT_EX' AND
S.SETCNTRLVALUE = C.BUSINESS_UNIT_GL) AND NOT EXISTS ( SELECT 'X' FROM PS_EX_SHEET_ADV SA , PS_EX_SHEET_HDR SHDR WHERE SA.EMPLID = A.EMPLID AND SA.ADVANCE_ID = A.ADVANCE_ID AND SHDR.SHEET_ID = SA.SHEET_ID AND SHDR.SHEET_STATUS <> 'PD')
    -- Row(s) affected: 0
    Process 9718 ABENDED at Step EX_LIBRARY.CF_INS.CF_INS_2 (Action SQL) -- RC = 805
    ROLLBACK
    SQL Error: ORA-00001: unique constraint (EMDBO.PS_EX_ACCTG_TMP4) violated "

BUSINESS IMPACT:

The Cash Advance Reconciliation Accounting Entries cannot be properly generated by the system for the reconciled transactions. This is affecting General Ledger bookings.

EXPECTED BEHAVIOR:

The Post Liabilities Application Engine Program should be able to handle Cash Advance Reconciliation Accounting Entry creation for as many General Ledger Business Units defined in the Run Control ID, as well as for whichever Set ID may they be using in the Expenses Options Business Unit Record Group (EX_01).

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