EAP: Supplier / Vendor Archive does not archive the VENDOR_LANG table

(Doc ID 1953437.1)

Last updated on MARCH 18, 2016

Applies to:

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

Symptoms

Per PeopleBooks, Vendor Archiving will archive various Supplier tables that start with VENDOR and VNDR. However, one of the tables not listed is the VENDOR_LANG table.

Since the VENDOR_LANG table is not archived, this causes issues with the Populate Vendor Name History (VNDR_HST_POP) process.

When "Supplier Name History" is checked on the Set Controls-Supplier page, and the Populate Vendor Name History (VNDR_HST_POP) process is ran, the process will run to No Success with an error.

ERROR
File: e:\pt85308b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1623 Error Position: 179 Return: 1400 - ORA-01400: cannot insert NULL into ("SYSFS"."PS_VNDR_NAME_HS_LN"."EFFDT") Failed SQL stmt: INSERT INTO PS_VNDR_NAME_HS_LN (SETID , VENDOR_ID , EFFDT , LANGUAGE_CD , VENDOR_NA ME_SHORT , NAME1 , NAME2 , VNDR_NAME_SHRT_USR , OPRID_MODIFIED_BY) VALUES ('SHARE' , '134146' , NULL , 'JPN' , 'JPNGALILEO-001' , '株式会社ガリレオ' , ' ' , 'JPNGALILEO' , 'q723541')

The Populate Vendor Name History (VNDR_HST_POP) process failed due to archived Vendors being in the VENDOR_LANG table, but not in the VENDOR or VENDOR_ADDR tables. There appears to be no history (archive) record for the VENDOR_LANG table. Therefore, Vendors that are archived are now orphaned in the VENDOR_LANG table, and the VNDR_HST_POP process uses this VENDOR_LANG table during the population of the VENDOR_NAME_HST record.

STEPS
1. Log into a multi-language database, selecting the Language = Français du Canada
2. Navigate to Fournisseurs > Données fournisseur > Ajout et mise à jour > Fournisseur
3. Add a new Vendor
4. Save the Vendor
5. Through SQL, query the VENDOR_LANG table for the Vendor ID that was added in Step 3
6. Vendor ID from Step 3 should display on the VENDOR_LANG table
7. Navigate to PeopleTools > Archive Data > Find Data page
8. Find Archive Data for Vendor ID added in Step 3/4
Field Name = VENDOR_ID
Value to Match = Vendor ID added in Step 3/4
9. Click Find Data
10. The VENDOR_LANG table is found
11. Build the Archive Project
12. Archive Designer page will show the Archiving Record = VENDOR_LANG but it does not have a corresponding History Record
13. Run the Archiving process
14. Once the Vendor is archived, query the VENDOR_LANG table for the Vendor ID that was added in Step 3/4
15. Vendor ID still exists on the VENDOR_LANG table
16. Vendor does not exist on the VENDOR or VENDOR_ADDR table

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