GP ESP: Archiving Historical Data Results duplicates key on GPES_CRTLQE_HST table (Doc ID 2195350.1)

Last updated on OCTOBER 20, 2016

Applies to:

PeopleSoft Enterprise HCM Global Payroll Spain - Version 9.1 and later
Information in this document applies to any platform.

Symptoms

ACTUAL BEHAVIOR
------------------------
Running the standard Archive process delivered for GP results in duplicates key on GPES_CRTLQE_HST table.
Source table for archive PS_GPES_CRT_LQ_EE has RSLT_SEG_NUM as duplicate key, while PS_GPES_CRTLQE_HST has it as key. Despite the fact that the latter has other additional fields as key, this is producing duplicates.
As per archive definition, keys from the destination table should be = keys from original table + key from archive subrecord (PSARCHIVE_SBR).
This means, that while table PS_GPES_CRT_LQ_EE has keys (emplid, cal_run_id, empl_rcd, gp_paygroup, cal_id, orig_cal_run_id, rslt_seg_num **duplicated**), PS_GPES_CRTLQE_HST has the following keys (PSARCH_ID, PSARCH_BATCHNUM, emplid, cal_run_id, empl_rcd, gp_paygroup, cal_id, orig_cal_run_id, rslt_seg_num, ssn_employer, gpes_year, gpes_month, gpes_sstc_paymnt, gpes_crt_data_type, gpes_crt_code, slice_bgn_dt).

 
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1) navigate to PeopleTools/Data Archive Manager/Archive Data To History.
2) Create a run control for the GPESRSLT template, GP_CAL_RUN_MULT selective query. Choose the bind variables so to have all the cal_run_id's in the execution
3) run the 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