EEX8.9+: Payment Post (EX_POST_PMT) Abends at Step EX_LIBRARY.CF_INS.CF_INS_2 with Unique Constraint When Expense Report Payments Generated in HRMS Payroll (Doc ID 972617.1)

Last updated on SEPTEMBER 13, 2016

Applies to:

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

Symptoms

The Expenses Payment Post Application Engine Program (EX_POST_PMT) is abending with a Unique Constrain Error Message for those Expense Report Payments generated in HRMS Payroll, and for which the Expense Line Distribution details contain multiple Accounting Lines and ChartField Inheritance is enabled.

The process is abending at Step EX_LIBRARY.CF_INS.CF_INS_2 where the system is executing the codeline contained within SQL Object CAS_CRSHTPY1_SQL. It seems that the codeline in this SQL Object is forcing the system to insert a value of 1 into the DISTRIB_LINE_NUM field from PS_EX_ACCTG_TMP record. As the Expense Report Line contained two Distribution Lines for the single Expense Line, when trying to create the second Distribution Line accounting, the system is finding a Unique Constrain, as instead of placing a 2, the system still wants to set a 1.

-- 12.37.59 .....(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 5553, B.SHEET_ID , 'S' , B.LINE_NBR, 1 , 'CAS' ,
B.APPL_JRNL_ID_CASH , B.SEQ_NBR , B.ACCOUNTING_DT, B.DOC_TYPE, B.DOC_SEQ_NBR,
B.DOC_SEQ_DATE, 0 , B.BUSINESS_UNIT_GL , B.BUSINESS_UNIT_GL, 0 , ' ' ,
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') , 0 , 'N' , ' ' , ' ' , '
' , ((B.PAID_AMT_BNK_BSE) * ( -1)) , B.BASE_CURRENCY_BNK , ((B.PAID_AMT) * (
-1)) , B.CURRENCY_PYMNT , 1 , 1 , B.RT_TYPE_BNK , B.EMPLID, 'I' , B.ACCOUNT,
' ', A.DEPTID, B.OPERATING_UNIT, A.PRODUCT, A.FUND_CODE, A.CLASS_FLD,
A.PROGRAM_CODE, A.BUDGET_REF, B.AFFILIATE, B.AFFILIATE_INTRA1,
B.AFFILIATE_INTRA2, B.CHARTFIELD1, B.CHARTFIELD2, B.CHARTFIELD3,
A.PROJECT_ID, ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , 'N' , 'I' , 'N', 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', 'N', ' ', ' ', ' ' FROM
PS_EX_SH_PST_TAO4 B, PS_EX_ACCTLN_TAO4 A, PS_BUS_UNIT_TBL_GL C WHERE
B.EX_PYMNT_DEST = 'P' AND B.PYMNT_STATUS = 'N' AND B.CANCEL_ACTION = 'N' AND
B.PYMNT_SELCT_STATUS = 'P' AND B.POST_STATUS_AP = 'U' AND A.EX_DOC_ID =
B.SHEET_ID AND A.EX_DOC_TYPE = 'S' AND A.LINE_NBR = B.LINE_NBR AND
C.BUSINESS_UNIT = B.BUSINESS_UNIT_GL AND B.BUSINESS_UNIT_BANK =
B.BUSINESS_UNIT_GL AND B.PROCESS_INSTANCE = 5553 AND A.PROCESS_INSTANCE =
B.PROCESS_INSTANCE AND B.BANK_SETID = 'SHARE'
/
-- Row(s) affected: 0
-- 12.38.04 Process 5553 ABENDED at Step EX_LIBRARY.CF_INS.CF_INS_2 (Action SQL) -- RC = 805
ROLLBACK
/
-- 12.38.04 SQL Error: ORA-00001: unique constraint (EMDBO.PS_EX_ACCTG_TMP4) violated

This unique constrain Error Message in the Payment Post process prevents the system from correctly creating the EXPAYMENT Accounting Entries for Expense Reports satisfied by HRMS Payroll.

Multiple Distribution Lines for Payments generated from Accounts Payable use a different SQL Objects, such as CAS_CRSH2AP_SQL, that check the Distribution Line Number from a previous Temp Table PS_EX_ACCTLN_TAO. Even with this change in CAS_CRSHTPY1_SQL, the Amounts of the generated lines is not 100% correct, as they get the total amount instead of the allocated amount during the Expense Report creation.

The Payment Post process (Application Engine program EX_POST_PMT) should end up in success, and properly create the necessary EXPAYMENT Accounting Entries for Payroll related Expense Report Payments when multiple Distribution Accounting Lines are needed per Expense Line using ChartField Inheritance.

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