My Oracle Support Banner

ESOCIAL: Error ORA-00001: Unique Constraint (HR92HML.PS_GPBR_STG_DEP) Violated, Running Event S-2200. (Doc ID 2360108.1)

Last updated on MAY 11, 2018

Applies to:

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

Symptoms

Error in  GPBR_ES_MAP.EMPL_STG.EMP_STG2, when running eSocial Basic Initials for event S-2200.

ERROR
-----------------------
SQL error. Stmt #: 1708 Error Position: 0 Return: 805 - ORA-00001: unique constraint (HR92HML.PS_GPBR_STG_DEP) violated
Failed SQL stmt: INSERT INTO PS_GPBR_STG_DEP ( GPBR_DRIVER_SEQ ,GPBR_DRV_SPLIT_SEQ ,DEPENDENT_BENEF ,RELATIONSHIP ,GPBR_PERSON_NAME ,BIRTHDATE ,NATIONAL_ID ,GPBR_DEP_IR ,GPBR_DEP_SF ,GPBR_DEP_HEALTH ,GPBR_DISAB_MEN ) SELECT TMP.GPBR_DRIVER_SEQ ,TMP.GPBR_DRV_SPLIT_SEQ ,DEP.DEPENDENT_BENEF ,STP.GPBR_DEP_TYPE_CD ,CASE WHEN DEP.MIDDLE_NAME = ' ' THEN DEP.FIRST_NAME || ' ' || DEP.LAST_NAME ELSE DEP.FIRST_NAME || ' ' || DEP.MIDDLE_NAME || ' ' || DEP.LAST_NAME END ,BEN.BIRTHDATE ,CASE WHEN NID.NATIONAL_ID IS NULL THEN ' ' ELSE NID.NATIONAL_ID END ,CASE WHEN DFF.DEP_BENEF_TYPE IN ('D' , 'O') THEN 'S' ELSE 'N' END ,CASE WHEN (DFF.DEP_BENEF_TYPE IN ('B' , 'D' , 'O') AND DFF.RELATIONSHIP IN ('C' ,'SC' ,'FC' ,'AC') AND DBR.FAM_TERM_DT_BRA IS NULL) AND (ADD_MONTHS(BEN.BIRTHDATE, 14 *12) >= TMP.GPBR_ES_PS OR DBR.DISABLED_BRA = 1) THEN 'S' ELSE 'N' END , 'N' , CASE WHEN (DFF.DEP_BENEF_TYPE IN ('B' , 'D' , 'O') AND DBR.DISABLED_BRA = 1) THEN 'S' ELSE 'N' END FROM PS_GPBR_M_01_TMP4 TMP ,PS_DEP_BEN_NAME DEP LEFT OUTER JOIN PS_DEP_BENEF_NID NID ON NID.EMPLID = DEP.EMPLID AND NID.DEPENDENT_BENEF = DEP.DEPENDENT_BENEF AND NID.COUNTRY = 'BRA' AND NID.NATIONAL_ID_TYPE = 'CPF' ,PS_DEP_BEN BEN ,PS_DEP_BEN_EFF_BRA DBR ,PS_DEP_BEN_EFF DFF ,PS_GPBR_DEP_INFO STP WHERE TMP.PROCESS_INSTANCE = 469134 AND DEP.EMPLID = TMP.EMPLID AND BEN.EMPLID = DEP.EMPLID AND BEN.DEPENDENT_BENEF = DEP.DEPENDENT_BENEF AND DBR.EMPLID = BEN.EMPLID AND DBR.DEPENDENT_BENEF = BEN.DEPENDENT_BENEF AND DBR.EFFDT = ( SELECT MAX(EFFDT) FROM PS_DEP_BEN_EFF_BRA WHERE EMPLID = DBR.EMPLID AND DEPENDENT_BENEF = DBR.DEPENDENT_BENEF AND DBR.EFFDT <= TMP.GPBR_ES_PS) AND DFF.EMPLID = BEN.EMPLID AND DFF.DEPENDENT_BENEF = BEN.DEPENDENT_BENEF AND DFF.EFFDT = ( SELECT MAX(EFFDT) FROM PS_DEP_BEN_EFF WHERE EMPLID = DFF.EMPLID AND DEPENDENT_BENEF = DFF.DEPENDENT_BENEF AND DFF.EFFDT <= TMP.GPBR_ES_PS) AND STP.COUNTRY = 'BRA' AND STP.RELATIONSHIP = DFF.RELATIONSHIP AND STP.EFF_STATUS = 'A' AND (DBR.STUDENT_BRA = 1 OR DBR.STUDENT_BRA = 0) AND (DBR.DISABLED_BRA = 1 OR DBR.DISABLED_BRA = 0) AND STP.EFFDT = ( SELECT MAX(STP2.EFFDT) FROM ps_gpbr_dep_info STP2 WHERE STP.COUNTRY = STP2.COUNTRY AND STP.RELATIONSHIP = STP2.RELATIONSHIP AND STP.GPBR_DEP_TYPE_CD = STP2.GPBR_DEP_TYPE_CD AND STP2.EFFDT <= TMP.GPBR_ES_PS) AND DEP.EFFDT = ( SELECT MAX(DEP1.EFFDT) FROM PS_DEP_BEN_NAME DEP1 WHERE DEP1.EMPLID = DEP.EMPLID AND DEP1.DEPENDENT_BENEF = DEP.DEPENDENT_BENEF AND DEP1.EFFDT <= TMP.GPBR_ES_PS) AND NOT EXISTS ( SELECT 'Y' FROM PS_HEALTH_DEPENDNT HDP WHERE BEN.EMPLID = HDP.EMPLID AND TMP.EMPL_RCD = HDP.EMPL_RCD AND BEN.DEPENDENT_BENEF = HDP.DEPENDENT_BENEF AND HDP.EFFDT = ( SELECT MAX(HDP_ED.EFFDT) FROM PS_HEALTH_DEPENDNT HDP_ED WHERE HDP.EMPLID = HDP_ED.EMPLID AND HDP.EMPL_RCD = HDP_ED.EMPL_RCD AND HDP.COBRA_EVENT_ID = HDP_ED.COBRA_EVENT_ID AND HDP.PLAN_TYPE = HDP_ED.PLAN_TYPE AND HDP.BENEFIT_NBR = HDP_ED.BENEFIT_NBR AND HDP_ED.EFFDT <= TMP.GPBR_ES_PS)) AND TMP.GPBR_EVENT_CD = 'S-2200' AND NOT EXISTS ( SELECT 'Y' FROM PS_GPBR_DEP_TMP4 DTM WHERE DTM.PROCESS_INSTANCE = TMP.PROCESS_INSTANCE AND DTM.GPBR_DRIVER_SEQ = TMP.GPBR_DRIVER_SEQ AND DTM.GPBR_DRV_SPLIT_SEQ = TMP.GPBR_DRV_SPLIT_SEQ)


Changes

 

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.