ACA_EMP_CH4DV (SQL View)	Index Back
ACA Employee data load prompt ACA Employee data load prompt view

ACA_EMP_CH4DV

(SQL View)

ACA Employee data load prompt

ACA Employee data load prompt view

SELECT DISTINCT A.ACA_EMP_DATA_ID , A.CHAR4_DEF FROM PS_ACA_DX_EMP_CH4D A , PS_ACA_ALE_LOAD B WHERE A.ACA_DAT_EXTRACT_ID = B.ACA_DAT_EXTRACT_ID AND A.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_ACA_DX_EMP_CH4D M WHERE A.ACA_DAT_EXTRACT_ID = M.ACA_DAT_EXTRACT_ID AND M.EFFDT <= %DateIn(%NumToChar(B.ACA_CALENDAR_YEAR) %CONCAT '-12-31')) AND B.ACA_CALENDAR_YEAR = ( SELECT MAX(B1.ACA_CALENDAR_YEAR) FROM PS_ACA_ALE_LOAD B1 WHERE B.ACA_DAT_EXTRACT_ID = B1.ACA_DAT_EXTRACT_ID ) AND A.EFFSEQ = ( SELECT MAX(M.EFFSEQ) FROM PS_ACA_DX_EMP_CH4D M WHERE A.ACA_DAT_EXTRACT_ID = M.ACA_DAT_EXTRACT_ID AND M.EFFDT = A.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 ACA_EMP_DATA_ID Character(4) VARCHAR2(4) NOT NULL ID for the employee ACA data
CDS1=Offer of Coverage Code
CDS2=SafeHarbor & OtherRelief Codes
HRZP=HRA Zip Code
LCST=Self-Only Lowest Premium Cost
LCSV=Self Low Cost Base Benefits
LCSX=Self Low Cost BA No Credit
LCSY=Self Low Cost BA With Credit
OHRA=HRA Silver Plan
OHRB=HRA Employer Contrib Base Ben
OHRD=HRA Employer Contrib Ben Admin
OHRR=HRA Empl Required Contribution

2 CHAR4_DEF Character(4) VARCHAR2(4) NOT NULL Monthly Default Value

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	ACA_EMP_DATA_ID	Character(4)	VARCHAR2(4) NOT NULL	ID for the employee ACA data CDS1=Offer of Coverage Code CDS2=SafeHarbor & OtherRelief Codes HRZP=HRA Zip Code LCST=Self-Only Lowest Premium Cost LCSV=Self Low Cost Base Benefits LCSX=Self Low Cost BA No Credit LCSY=Self Low Cost BA With Credit OHRA=HRA Silver Plan OHRB=HRA Employer Contrib Base Ben OHRD=HRA Employer Contrib Ben Admin OHRR=HRA Empl Required Contribution
2	CHAR4_DEF	Character(4)	VARCHAR2(4) NOT NULL	Monthly Default Value