EAP: VAT_ACCT_TBL Distribution Line Number issue (Doc ID 1922537.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

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

Symptoms

Release 9.1 MP10 introduced a code change in the Record Peoplecode for VAT_DST_WRK1.ACTION_SAVED. SavePostChange.  It is allocating a unique Distribution Line Number to each row in the VAT_ACCT_TBL. 

SELECT MAX(DISTRIB_LINE_NUM) FROM PS_VAT_ACCT_TBL has around 200,000 rows and the max field length is 5 digits.  This gives a SQL error when checking the VAT accounting entries online.  The old code used to allocate the number using the defined values for certain VAT Entry Types and Transaction Types i.e. DISTRIB_LINE_NUM = B.DISTRIB_LINE_NUM + C.DISTRIB_LINE_NUM  (PS_VAT_TXN_CD B , PS_TAX_HEADER_TBL C ).

The above code change will result in an error on the Voucher Accounting Entries page when trying to display a VAT Voucher with 6 digits in DISTRIB_LINE_NUM.

ERROR
Error in PeopleCode ScrollSelect processing. (2,213) VCHR_ENTRY_WRK_BUSINESS_UNIT.FieldFormula Name:SearchExecStatement PCPC:6723 Statement 78
Called from VCHR_ACCTG_ENTRIES.GBL.VCHR_ENTRY_WRK.SEARCH_BUTTON.FieldChange Statement 1

STEPS
1.  Update VAT CODE BL1, BL2, BL3, FR1, FR2, FR3, FR4, FR5, FR6, FR7, FR8, GE1, GE2 and NL1

2. Check the VAT_ACCT_TBL – new rows will have been added for each VAT Code / VAT Accounting Entry Type / Transaction Type.  For each accounting entry type, 212 rows will have been created – each row with a new Distribution Line Number

If each new VAT code creates 6,148 unique numbered Distribution Line Number rows in VAT_ACCT_TBL, the system will quickly reach the 99,999 max field length.

3. After update several VAT codes, the DISTRIB_LINE_NUM for NL1 will have more than 5 digits

SELECT A.SETID, A.TAX_CD, A.VAT_DST_ACCT_TYPE, A.VAT_TXN_TYPE_CD, A.ACCOUNT, A.ALTACCT, A.DEPTID, A.OPERATING_UNIT, A.PRODUCT, A.FUND_CODE, A.CLASS_FLD, A.PROGRAM_CODE, A.BUDGET_REF, A.AFFILIATE, A.AFFILIATE_INTRA1, A.AFFILIATE_INTRA2, A.DISTRIB_LINE_NUM, A.PROJECT_ID
  FROM PS_VAT_ACCT_TBL A
  WHERE ( A.SETID = 'SHARE'
  AND A.TAX_CD = 'NL1' )

4. Create a Voucher for an EU Business Unit and a NL1 VAT registered customer
5. Check the VAT
6. Save the voucher
7. Run Voucher Posting
8. Try to display the Voucher through the Voucher Accounting Entries page
9. A SQL error will occur due to the 6 digits in DISTRIB_LINE_NUM

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