EAR9.1+:Payment Predictor VAT Error - Unique Constraint On PAYMNT_ITEM_VAT (Doc ID 2232763.1)

Last updated on FEBRUARY 11, 2017

Applies to:

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

Symptoms

Environment:
-------------------
FMS Financials 9.1 Production


Statement of the Issue:
---------------------------------
The Payment Predictor fails at Step AR_PREDICT2.PGENVAT.PAYVAT

Error Message:
-------------------------

File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1623 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_PAYMNT_ITEM_VAT) violated
Failed SQL stmt: INSERT INTO PS_PAYMNT_ITEM_VAT ( DEPOSIT_BU, DEPOSIT_ID, PAYMENT_SEQ_NUM, BUSINESS_UNIT, CUST_ID, ITEM, ITEM_LINE, AR_SEQ_NUM, VAT_APPLICABILITY, VAT_TXN_TYPE_CD, TAX_CD_VAT, VAT_SEQ_NUM, TAX_CD_VAT_PCT, VAT_DCLRTN_DT, VAT_BASIS_AMT, VAT_TRANS_AMT, VAT_TRANS_AMT_BSE, VAT_AMT, VAT_AMT_I, VAT_ERROR_FLAG, BAL_CURRENCY, VAT_DST_ACCT_TYPE, VAT_AMT_BASE, VAT_BASIS_AMT_BASE, VAT_AMT_BASE_I, VAT_TREATMENT, PHYSICAL_NATURE, COUNTRY_LOC_BUYER, STATE_LOC_BUYER, COUNTRY_LOC_SELLER, STATE_LOC_SELLER, VAT_SVC_SUPPLY_FLG, VAT_SERVICE_TYPE, COUNTRY_VAT_PERFRM, STATE_VAT_PERFRM, COUNTRY_VAT_SUPPLY, STATE_VAT_SUPPLY, STATE_SHIP_FROM, STATE_SHIP_TO, STATE_VAT_DEFAULT, VAT_DFLT_DONE_FLG, VAT_ENTRD_AMT, VAT_CALC_AMT) SELECT DISTINCT P.DEPOSIT_BU , P.DEPOSIT_ID , P.PAYMENT_SEQ_NUM , P.BUSINESS_UNIT , P.CUST_ID , P.ITEM , P.ITEM_LINE , P.AR_SEQ_NUM , L.VAT_APPLICABILITY , L.VAT_TXN_TYPE_CD , L.TAX_CD_VAT , L.VAT_SEQ_NUM , L.TAX_CD_VAT_PCT , L.VAT_DCLRTN_DT , ((C.VAT_BASIS_AMT) * ( -1)) , ((C.VAT_TRANS_AMT) * ( -1)) , ((C.VAT_TRANS_AMT_BSE) * ( -1)) , ((C.VAT_CALC_AMT) * ( -1)) , 0 , L.ERROR_FLAG , C.TXN_CURRENCY_CD , V.VAT_DST_ACCT_TYPE , ((C.VAT_CALC_AMT_BSE) * ( -1)) , ((C.VAT_BASIS_AMT_BSE) * ( -1)) , 0 , L.VAT_TREATMENT , L.PHYSICAL_NATURE , L.COUNTRY_LOC_BUYER , L.STATE_LOC_BUYER , L.COUNTRY_LOC_SELLER , L.STATE_LOC_SELLER , L.VAT_SVC_SUPPLY_FLG , L.VAT_SERVICE_TYPE , L.COUNTRY_VAT_PERFRM , L.STATE_VAT_PERFRM , L.COUNTRY_VAT_SUPPLY , ' ' , L.STATE_SHIP_FROM , L.STATE_SHIP_TO , L.STATE_VAT_DEFAULT , L.VAT_DFLT_DONE_FLG , 0 , ((C.VAT_CALC_AMT) * ( -1)) FROM PS_PAYMENT_ITEM P , PS_PP_ITEMVL_TAO4 L , PS_PP_VATCAL_TAO4 V , PS_VAT_CALC_TAO4 C WHERE L.PROCESS_INSTANCE = 17870938 AND P.ENTRY_USE_ID IN ('WS-04', 'WS-05') AND P.DEPOSIT_BU = L.DEPOSIT_BU AND P.DEPOSIT_ID = L.DEPOSIT_ID AND P.PAYMENT_SEQ_NUM = L.PAYMENT_SEQ_NUM AND L.PROCESS_INSTANCE = V.PROCESS_INSTANCE AND L.DEPOSIT_BU = V.DEPOSIT_BU AND L.DEPOSIT_ID = V.DEPOSIT_ID AND L.PAYMENT_SEQ_NUM = V.PAYMENT_SEQ_NUM AND L.BUSINESS_UNIT = V.BUSINESS_UNIT AND L.CUST_ID = V.CUST_ID AND L.ITEM = V.ITEM AND L.ITEM_LINE = V.ITEM_LINE AND L.AR_SEQ_NUM = V.AR_SEQ_NUM AND L.VAT_APPLICABILITY = V.VAT_APPLICABILITY AND L.VAT_TXN_TYPE_CD = V.VAT_TXN_TYPE_CD AND L.TAX_CD_VAT = V.TAX_CD_VAT AND L.VAT_SEQ_NUM = V.VAT_SEQ_NUM AND V.PROCESS_INSTANCE = C.PROCESS_INSTANCE AND V.BUSINESS_UNIT = C.BUSINESS_UNIT AND V.VAT_CALC_TXN_ID = C.VAT_CALC_TXN_ID

Process 17870938 ABENDED at Step AR_PREDICT2.PGENVAT.PAYVAT (SQL) -- RC = 805 (108,524)

Steps to Replicate:
----------------------------

  1. Load the Deposit via Lockbox and process it.
  2. Run Payment Predictor process.

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