EAP 9.2: VAT Transaction Loader Abends At Step FS_VATUPDFS.DL000.DL000-1 With Unique Constrain On PS_VAT_TXN_TBL On Voucher Data (Doc ID 2195023.1)

Last updated on OCTOBER 20, 2016

Applies to:

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

Symptoms

ISSUE:

The VAT Transaction Loader Application Engine Program (FS_VATUPDFS) abends at Step FS_VATUPDFS.DL000.DL000-1 with a Unique Constrain Error Message when trying to insert transaction data into Record PS_VAT_TXN_TBL.

This seems to be related to the fact that the process may not be able to handle a Voucher transaction with multiple Invoice Lines, and at the same time multiple Distribution Lines, which result in having the below combinations in PS_VCHR_ACCTG_LINE:

     - Once Accrual Accounting Entry with DST_ACCT_TYPE = DST, and the combination of VOUCHER_LINE_NUM = 1 and DISTRIB_LINE_NUM = 10
     - Once Accrual Accounting Entry with DST_ACCT_TYPE = DST, and the combination of VOUCHER_LINE_NUM = 10 and DISTRIB_LINE_NUM = 1

That is to say, a VAT related Voucher having at least 10 Invoice Lines, out of which, the first one, has in its own right, at least 10 Distribution Lines.

REPLICATION STEPS:

     1.- Log into the FSCM Online Application as User ID VP1
     2.- Configure VAT functionality on a Payables Business Unit, such as FRAE1
     3.- Create a new Voucher ID on VAT enabled Business Unit FRAE1 with 12 Invoice Lines
     4.- Make sure Voucher Invoice Line #1 has 11 Distribution Lines
     5.- Ensure VAT information is present in Voucher Header, Invoice Line level, and Distribution Line level (VAT Applicability = Outside of Scope of VAT)
     6.- Approve the Voucher ID
     7.- Launch the Voucher Post process, to generate Accrual Accounting Entries in PS_VCHR_ACCTG_LINE Record
     8.- Create a brand new Pay Cycle targeting the Voucher created
     9.- Run Payment Selection
     10.- Launch Payment Creation, and all sub-processes until Pay Cycle is in Completed status
     11.- Proceed to launch the Payment Post process against the newly generated Payment ID
     12.- Run the VAT Transaction Loader Request for Business Unit FRAE1, Product Payables, and the affected VAT Entity
     13.- Confirm that the FS_VATUPDFS AE Program has ended up in No Success

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:

     " -- 15:44:09.160 ...(FS_VATUPDFS.DL000.DL000-1) (SQL)
       INSERT INTO PS_VAT_TXN_TBL (VAT_ENTITY, COUNTRY_VAT_RPTG, VAT_DCMNT_ID, LINE_NBR, VAT_TXN_SOURCE, VAT_DST_ACCT_TYPE, VAT_RPTG_CURR_FLG, TXN_CURRENCY_CD, BASE_CURRENCY, CURRENCY_RPTG, TAX_BASIS_AMT_TXN, TAX_BASIS_AMT_BSE, TAX_BASIS_AMT_RPTG, TAX_AMT_GROSS_TXN, TAX_AMT_GROSS_BSE, TAX_AMT_GROSS_RPTG, TAX_TRANS_AMT_TXN, TAX_TRANS_AMT_BSE, TAX_TRANS_AMT_RPTG, TAX_AMT_RCVRY_TXN, TAX_AMT_RCVRY_BSE, TAX_AMT_RCVRY_RPTG, TAX_AMT_RBT_TXN, TAX_AMT_RBT_BSE, TAX_AMT_RBT_RPTG, TAX_CD_VAT, VAT_TXN_TYPE_CD, TAX_CD_VAT_PCT, VAT_TREATMENT, VAT_APPLICABILITY, VAT_DCLRTN_DT, VAT_DCLRTN_DT_SRC, VAT_DCLRTN_POINT, CUR_EFFDT, RATE_MULT, RATE_DIV, VAT_RGSTR_RPT_DT, SETCNTRLVALUE, BUSINESS_UNIT_GL, VENDOR_SETID, VENDOR_ID, CUSTOMER_SETID, CUST_ID, VAT_RGSTRN_TPRTNR, COUNTRY_VAT_TPRTNR, COUNTRY_VAT_BILLFR, COUNTRY_VAT_BILLTO, COUNTRY_SHIP_TO, COUNTRY_SHIP_FROM, DOC_TYPE, DOC_SEQ_DATE, DOC_SEQ_NBR, PHYSICAL_NATURE, COUNTRY_VAT_SUPPLY, VAT_SVC_SUPPLY_FLG, VAT_SERVICE_TYPE, COUNTRY_VAT_PERFRM, STATE_VAT_DEFAULT, VAT_RPT_STATUS, ESL_RPT_STATUS, RCSL_RPT_STATUS, VAT_RGSTRN_ID, COUNTRY_2CHAR, COUNTRY_TPRT_2CHAR, CUR_RT_TYPE, EC_SALES_LIST_TYPE, EXCHNG_RT_SRC, SETID, UNPOST_SEQ, SYSTEM_DATE, IN_PROCESS_FLG, PROCESS_INSTANCE, VAT_RVCHG_MONTH) SELECT B.VAT_ENTITY, A.COUNTRY_VAT_RPTG, B.VAT_DCMNT_ID, B.LINE_NBR, A.VAT_TXN_SOURCE, A.VAT_DST_ACCT_TYPE, A.VAT_RPTG_CURR_FLG, A.TXN_CURRENCY_CD, A.BASE_CURRENCY, A.CURRENCY_RPTG, A.TAX_BASIS_AMT_TXN, A.TAX_BASIS_AMT_BSE, A.TAX_BASIS_AMT_RPTG, A.TAX_AMT_GROSS_TXN, A.TAX_AMT_GROSS_BSE, A.TAX_AMT_GROSS_RPTG, A.TAX_TRANS_AMT_TXN, A.TAX_TRANS_AMT_BSE, A.TAX_TRANS_AMT_RPTG, A.TAX_AMT_RCVRY_TXN, A.TAX_AMT_RCVRY_BSE, A.TAX_AMT_RCVRY_RPTG, A.TAX_AMT_RBT_TXN, A.TAX_AMT_RBT_BSE, A.TAX_AMT_RBT_RPTG, A.TAX_CD_VAT, A.VAT_TXN_TYPE_CD, A.TAX_CD_VAT_PCT, A.VAT_TREATMENT, A.VAT_APPLICABILITY, A.VAT_DCLRTN_DT, A.VAT_DCLRTN_DT_SRC, A.VAT_DCLRTN_POINT, A.CUR_EFFDT, A.RATE_MULT, A.RATE_DIV, A.VAT_RGSTR_RPT_DT, A.SETCNTRLVALUE, A.BUSINESS_UNIT_GL, A.VENDOR_SETID, A.VENDOR_ID, A.CUSTOMER_SETID, A.CUST_ID, A.VAT_RGSTRN_TPRTNR, A.COUNTRY_VAT_TPRTNR, A.COUNTRY_VAT_BILLFR, A.COUNTRY_VAT_BILLTO, A.COUNTRY_SHIP_TO, A.COUNTRY_SHIP_FROM, A.DOC_TYPE, A.DOC_SEQ_DATE, A.DOC_SEQ_NBR, A.PHYSICAL_NATURE, A.COUNTRY_VAT_SUPPLY, A.VAT_SVC_SUPPLY_FLG, A.VAT_SERVICE_TYPE, A.COUNTRY_VAT_PERFRM, A.STATE_VAT_DEFAULT, A.VAT_RPT_STATUS, A.ESL_RPT_STATUS, A.RCSL_RPT_STATUS, V.VAT_RGSTRN_ID, V.COUNTRY_2CHAR, ' ', ' ', ' ', ' ', ' ', 0, TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'), 'Y', 75619, '0' FROM PS_VAT_TX_AP_NT_VW A, PS_VAT_XREFVCH_TAO B, PS_VAT_UPD_BU_TAO C, PS_VAT_ENT_RGSTRN V WHERE B.PROCESS_INSTANCE = 75619 AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND A.UNPOST_SEQ = B.UNPOST_SEQ AND A.APPL_JRNL_ID = B.APPL_JRNL_ID AND A.POSTING_PROCESS = B.POSTING_PROCESS AND A.PYMNT_CNT = B.PYMNT_CNT AND A.VOUCHER_LINE_NUM = B.VOUCHER_LINE_NUM AND A.DISTRIB_LINE_NUM = B.DISTRIB_LINE_NUM AND
A.DST_ACCT_TYPE = B.DST_ACCT_TYPE AND A.CF_BAL_LINE_NUM = B.CF_BAL_LINE_NUM AND A.LEDGER = B.LEDGER AND A.TAX_AUTHORITY_CD = B.TAX_AUTHORITY_CD AND B.VAT_DISTRIB_STATUS = 'P' AND C.PROCESS_INSTANCE = 75619 AND C.REQUEST_NBR = 1 AND C.VAT_SOURCE_DEFN = 'AP_VOUCHER' AND C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.BUSINESS_UNIT_GL = A.BUSINESS_UNIT_GL AND V.VAT_ENTITY = B.VAT_ENTITY AND V.COUNTRY = B.COUNTRY_VAT_RPTG
       /
       -- Row(s) affected: 0
       -- 15:44:09.390 Process 75619 ABENDED at Step FS_VATUPDFS.DL000.DL000-1 (Action SQL) -- RC = 805
       ROLLBACK
       /
       -- 15:44:09.422 SQL Error: ORA-00001: unique constraint (EMDBO.PS_VAT_TXN_TBL) violated "

EXPECTED BEHAVIOR:

No matter how many Invoice Lines, and Distribution Lines a Voucher transaction has, the VAT Transaction Loader Application Engine Program (FS_VATUPDFS) should be able to handle all the data.

 

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