ACA Merge Process Abends on Step ACA_MERGE.P60-MDEP.STEP020 on DB2 (Doc ID 2232991.1)

Last updated on MARCH 09, 2017

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

The Merge Other Data Sources process fails in step ACA_MERGE.P60-MDEP.Step020 with the following error:

ERROR
-----------------------
-- 14:38:53.865 ...(ACA_MERGE.P60-MDEP.Step020) (SQL)
INSERT INTO PS_ACA_MERGE_MSG (ACA_DAT_EXTRACT_ID, ACA_ALE_MEMBER,
ACA_CALENDAR_YEAR, EMPLID, MESSAGE_SET_NBR, MESSAGE_NBR) SELECT  DISTINCT :1,
:2, :3, M.EMPLID, 3004, 208 FROM PS_ACA_DEP_MO_CH1D M WHERE
M.ACA_DAT_EXTRACT_ID = :4 AND M.ACA_ALE_MEMBER= :5 AND M.ACA_CALENDAR_YEAR =
:6 AND M.DEPENDENT_BENEF > '00' AND NOT EXISTS ( SELECT 'X' FROM
PS_ACA_DEP_MO_CH1 X WHERE X.ACA_DAT_EXTRACT_ID = M.ACA_DAT_EXTRACT_ID AND
X.ACA_ALE_MEMBER = M.ACA_ALE_MEMBER AND X.ACA_CALENDAR_YEAR =
M.ACA_CALENDAR_YEAR AND X.EMPLID = M.EMPLID AND X.DEPENDENT_BENEF =
M.DEPENDENT_BENEF) AND NOT EXISTS ( SELECT 'X' FROM PS_ACA_DEPENDENT X WHERE
X.ACA_DAT_EXTRACT_ID = M.ACA_DAT_EXTRACT_ID AND X.ACA_ALE_MEMBER =
M.ACA_ALE_MEMBER AND X.ACA_CALENDAR_YEAR = M.ACA_CALENDAR_YEAR AND X.EMPLID =
M.EMPLID AND X.DEPENDENT_BENEF = M.DEPENDENT_BENEF) AND NOT EXISTS ( SELECT
'X' FROM PS_ACA_MERGE_MSG X WHERE X.ACA_DAT_EXTRACT_ID = :7 AND
X.ACA_ALE_MEMBER= :8 AND X.ACA_CALENDAR_YEAR = :9 AND X.EMPLID = M.EMPLID AND
X.MESSAGE_SET_NBR = 3004 AND X.MESSAGE_NBR = 208) AND EXISTS ( SELECT 'X'
FROM PS_ACA_MERGE_TMP1 X WHERE X.PROCESS_INSTANCE = :10 AND X.EMPLID =
M.EMPLID )
/
-- 14:38:54.714 Process 321850 ABENDED at Step ACA_MERGE.P60-MDEP.Step020 (Action SQL) -- RC = 8605
ROLLBACK
/
-- 14:38:55.011 SQL Error: [IBM][CLI Driver][DB2] SQL0418N  The statement was not processed because the statement contains an invalid use of one of the following: an untyped parameter marker, the DEFAULT keyword, or a null value.  SQLSTATE=42610

STEPS
-----------------------
1.  Add employee data to merge using the ExceltoCI.
2.  Run the Merge Other Data Sources process (Navigation:  Benefits >  ACA Annual Processing > Create ACA Form Data > Merge Other Data Sources).

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