SQL Failure Running FUSION_EXTRACT Application Engine (Doc ID 2076233.1)

Last updated on JUNE 21, 2017

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.1 to 9.1 [Release 9]
Information in this document applies to any platform.

Symptoms

SQL Failure running FUSION_EXTRACT Application Engine

When running the Application Engine FUSION_EXTRACT (which is to be used to create flat files from PeopleSoft to be loaded into Fusion), the following error message occurs "Ambiguous column name 'PROCESS_INSTANCE"
The issues is that both tables PS_FT_WORKER_TAO4 and PS_FT_DVM11_TAO5 have the column PROCESS_INSTANCE so the SQL fails because there is no ALIAS on the statement 'WHERE PROCESS_INSTANCE = 316005'.

ERROR

08.40.14 ......(FT_COMMN_LIB.DvmMap.DvmField) (SQL)
File: e:\pt852-903-r1-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1620 Error Position: 0 Return: 8601 - [Microsoft][SQL Server Native Client 10.0][SQL Server]Ambiguous column name 'PROCESS_INSTANCE'.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. (SQLSTATE 37000) 8180
UPDATE PS_FT_WORKER_TAO4 SET FT_PROC_MARKER = '11d836d4-6127-11e5-835d-c3ac23e21002', PS_FT_WORKER_TAO4.FT_COUNTRYOF_BIRTH = SUBSTRING(A.FT_TARGET_VALUE, 1, 240) FROM PS_FT_DVM11_TAO5 A WHERE PROCESS_INSTANCE = 316005 AND PS_FT_WORKER_TAO4.FT_COUNTRYOF_BIRTH = A.FT_SOURCE_VALUE AND A.EOTF_COMMONELEMENT = ( SELECT MIN(A.EOTF_COMMONELEMENT) FROM PS_FT_DVM11_TAO5 A WHERE PROCESS_INSTANCE = 316005 AND PS_FT_WORKER_TAO4.FT_COUNTRYOF_BIRTH = A.FT_SOURCE_VALUE) AND EXISTS ( SELECT 'X' FROM PS_FT_DVM11_TAO5 A WHERE PROCESS_INSTANCE = 316005 AND PS_FT_WORKER_TAO4.FT_COUNTRYOF_BIRTH = A.FT_SOURCE_VALUE)

STEPS

The issue can be reproduced at will with the following steps:
1. Run "FUSION_EXTRACT" AE

Some other places where customer is getting SQL related issues, with different  error messages are:
FT_COMMN_LIBDvmMap Clear  S
FT_COMMN_LIBDvmMap DvmFieldS
FT_COMMN_LIBDvmMap ErrFlag S
FT_COMMN_LIBDvmMap ErrLog S
FT_COMMN_LIBDvmMap UpdStatsS
FT_COMP_CHNGPOS_MGRSDpt_MGR15
FT_COMP_CHNGPOS_MGRSDpt_MGR25
FT_DEPT   DeptDtl Update1 S
FT_JOB_PRCS MAIN  InEmpSe2S
FT_JOB_XFORMMAIN  DELROWS S
FT_JOB_XFORMMAIN  Extract2S
FT_JOB_XFORMMAIN  UpdDTAC2S
FT_JOB_XFORMMAIN  UpdDTACPS
FT_PERSON PER_ADDRUpdate S
FT_POS_LKP_SPVSR

 

Changes

 

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