EPY: PYAP_VCHPOST Failed With an Unique Constraint (Doc ID 2869955.1)

Last updated on APRIL 15, 2024

Applies to:

Symptoms

We have exceeded the limit of AP_INVNO_LAST that field is char 7 and we just went to char 8.

The PYAP_VCHPOST is generating the following error:

ERROR
-----------------------
PSAESRV started service request at 16.42.49 2022-04-19


File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1738 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_DISTRIB_LN_STG) violated
Failed SQL stmt: INSERT INTO PS_DISTRIB_LN_STG (BUSINESS_UNIT, VOUCHER_ID, VOUCHER_LINE_NUM, DISTRIB_LINE_NUM, SUSPENSE_REF_NUM, BUSINESS_UNIT_GL, ACCOUNT, STATISTICS_CODE, FOREIGN_AMOUNT, TXN_CURRENCY_CD, MONETARY_AMOUNT, DSCNT_AMT_GL, FREIGHT_AMT_GL, MEMO_AMT_GL, MISC_AMT_GL, SALETX_AMT_GL, USETAX_AMT_GL, VAT_DSCNT_ADJ_GL, VAT_DSCNT_RCVR_GL, VAT_INV_AMT_GL, VAT_NONINV_AMT_GL, VAT_RCVRY_AMT_GL, VAT_NRCVR_AMT_GL, VAT_REBATE_AMT_GL, STATISTIC_AMOUNT, QTY_VCHR, JRNL_LN_REF, OPEN_ITEM_STATUS, DESCR, MERCHANDISE_AMT, CHART_EDIT_STATUS, ACCOUNTING_DT, PROCESS_INSTANCE, ERROR_FLAG, FINAL_REF_IND, REFERENCE_NUMBER, REF_CLOSED, REF_DIST_LINE, REF_LINE_NUMBER, BUSINESS_UNIT_PO, PO_ID, LINE_NBR, SCHED_NBR, PO_DIST_LINE_NUM, BCM_LINE_STATUS, BCM_POST_AMOUNT, BCM_POST_AMT_TOT, BUSINESS_UNIT_PC, ACTIVITY_ID, ANALYSIS_TYPE, RESOURCE_TYPE, RESOURCE_CATEGORY, RESOURCE_SUB_CAT, CUR_RT_TYPE, CUR_RATE_MULT, CUR_RATE_DIV, CURRENCY_CD, ASSET_FLG, BUSINESS_UNIT_AM, ASSET_ID, PROFILE_ID, COST_TYPE, VAT_TXN_TYPE_CD, FREIGHT_AMT, SALETX_AMT, USETAX_AMT, VAT_INV_AMT, VAT_NONINV_AMT, BUSINESS_UNIT_RECV, RECEIVER_ID, RECV_LN_NBR, RECV_SHIP_SEQ_NBR, RECV_DIST_LINE_NUM, VAT_RECOVERY_PCT, VAT_BASIS_AMT, VAT_CALC_AMT, VAT_RCVRY_AMT, VAT_NRCVR_AMT, VAT_DSCNT_ADJ, VAT_DSCNT_RCVRY, TAX_CD_VAT, TAX_CD_VAT_PCT, LEDGER_GROUP, DSCNT_AMT, ECTRANSID, ECQUEUEINSTANCE, ECTRANSINOUTSW, OPERATING_UNIT, DEPTID, PRODUCT, PROJECT_ID, AFFILIATE, ALTACCT, FUND_CODE, PROGRAM_CODE, CLASS_FLD, BUDGET_REF, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, AFFILIATE_INTRA1, AFFILIATE_INTRA2, WTHD_SW, WTHD_SETID, WTHD_CD, SHIPTO_ID, SUT_BASE_ID, TAX_CD_SUT, TAX_CD_SUT_PCT, SALETX_CALC_AMT


STEPS
-----------------------
The issue can be reproduced with the following steps:

1. Ensure the AP Invoice Last goes to 9999999 on the Installation Table before running AP Voucher Post (Set Up HCM>Install>Product Specific>Payroll for NA Installation)

2. Change the field size AP_INVNO_LAST in application designer from 7 char to 8 char 

3.. Run AP Voucher Post (Payroll for North America>Payroll Distribution>Accounts Payable Information>Send Vouchers to AP)

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.