Affordable Care Act (ACA) Extract fails when using Specified Day as Common As of Day in Month (Doc ID 2096604.1)

Last updated on MARCH 09, 2016

Applies to:

PeopleSoft Enterprise HCM Benefits Administration - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

When attempting to run the Affordable Care Act (ACA) Data Extract using 12 as the Specified Day for Common As of Day in Month, the following error occurs:

ERROR
-----------------------
File: e:\pt85416b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1703 Error Position: 712 Return: 1843 - ORA-01843: not a valid month
Failed SQL stmt: SELECT CASE WHEN MONTH < 10 THEN 'CHAR4_0' || TO_CHAR(MONTH) ELSE 'CHAR4_' || TO_CHAR(MONTH) END ACA_UPD_FIELD ,MONTH ,TO_CHAR(BEGIN_DT,'YYYY-MM-DD') ACA_BEGIN_MONTH ,TO_CHAR(END_DT,'YYYY-MM-DD') ACA_END_MONTH ,CASE WHEN ' ' = 'D1MN' THEN TO_CHAR(BEGIN_DT,'YYYY-MM-DD') WHEN ' ' = 'LDMN' THEN TO_CHAR(END_DT,'YYYY-MM-DD') WHEN ' ' = 'MMDD' THEN TO_CHAR(TO_DATE(TO_CHAR(2015 , '9999') || TO_CHAR(0 , '09') || TO_CHAR(0,'09') , 'YYYYMMDD'),'YYYY-MM-DD') ELSE TO_CHAR(END_DT,'YYYY-MM-DD') END ACA_MO_ASODT_SPC ,CASE WHEN 'MMDD' = 'D1MN' THEN TO_CHAR(BEGIN_DT,'YYYY-MM-DD') WHEN 'MMDD' = 'LDMN' THEN TO_CHAR(END_DT,'YYYY-MM-DD') WHEN 'MMDD' = 'MMDD' THEN TO_CHAR(TO_DATE(TO_CHAR(2015 , '9999') || TO_CHAR(0 , '09') || TO_CHAR(12,'09') , 'YYYYMMDD'),'YYYY-MM-DD') ELSE TO_CHAR(END_DT,'YYYY-MM-DD') END ACA_MO_ASOFDT_CMN FROM PS_ACA_MONTHLY_DT

Process 73115 ABENDED at Step ACA_EXTRACT.P100-CDX.Step010 (Do Select) -- RC = 1843 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

STEPS
-----------------------
1. Set the Common As of Day in Month to 12 on the ACA Data Extract Definition (Navigation: Set Up HCM > Common Definitions > Affordable Care Act > ACA Data Extract Definition).
2. Run the ACA Data Extract (Navigation: Benefits > ACA Annual Processing > Create ACA Form Data > ACA Data Extract).

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