ACA_MRG_EMPL_VW

(SQL View)
 Index Back

ACA Employee Selection View

ACA Employee Selection View

SELECT C.ACA_DAT_EXTRACT_ID ,C.ACA_ALE_MEMBER ,C.ACA_CALENDAR_YEAR ,C.EMPLID ,0 ,NM.NAME FROM PS_ACA_EMP_MO_CH4D C , PS_NAMES NM WHERE C.ACA_DATA_STATUS IN ('E','I','U','W','M') AND NM.EMPLID = C.EMPLID AND NM.NAME_TYPE = 'PRI' AND NM.EFF_STATUS = 'A' AND NM.EFFDT = ( SELECT MAX(NM1.EFFDT) FROM PS_NAMES NM1 WHERE NM.EMPLID = NM1.EMPLID AND NM.NAME_TYPE = NM1.NAME_TYPE AND NM1.EFFDT <= %CurrentDateIn) UNION SELECT C.ACA_DAT_EXTRACT_ID ,C.ACA_ALE_MEMBER ,C.ACA_CALENDAR_YEAR ,C.EMPLID ,0 ,NM.NAME FROM PS_ACA_EMP_MO_AMTD C , PS_NAMES NM WHERE C.ACA_DATA_STATUS IN ('E','I','U','W','M') AND NM.EMPLID = C.EMPLID AND NM.NAME_TYPE = 'PRI' AND NM.EFF_STATUS = 'A' AND NM.EFFDT = ( SELECT MAX(NM1.EFFDT) FROM PS_NAMES NM1 WHERE NM.EMPLID = NM1.EMPLID AND NM.NAME_TYPE = NM1.NAME_TYPE AND NM1.EFFDT <= %CurrentDateIn) UNION SELECT C.ACA_DAT_EXTRACT_ID ,C.ACA_ALE_MEMBER ,C.ACA_CALENDAR_YEAR ,C.EMPLID ,0 ,NM.NAME FROM PS_ACA_DEP_MO_CH1D C , PS_NAMES NM WHERE C.ACA_DATA_STATUS IN ('E','I','U','W','M') AND NM.EMPLID = C.EMPLID AND NM.NAME_TYPE = 'PRI' AND NM.EFF_STATUS = 'A' AND NM.EFFDT = ( SELECT MAX(NM1.EFFDT) FROM PS_NAMES NM1 WHERE NM.EMPLID = NM1.EMPLID AND NM.NAME_TYPE = NM1.NAME_TYPE AND NM1.EFFDT <= %CurrentDateIn) UNION SELECT C.ACA_DAT_EXTRACT_ID ,C.ACA_ALE_MEMBER ,C.ACA_CALENDAR_YEAR ,C.EMPLID ,0 ,NM.NAME FROM PS_ACA_EMP_MO_C20D C , PS_NAMES NM WHERE C.ACA_DATA_STATUS IN ('E','I','U','W','M') AND NM.EMPLID = C.EMPLID AND NM.NAME_TYPE = 'PRI' AND NM.EFF_STATUS = 'A' AND NM.EFFDT = ( SELECT MAX(NM1.EFFDT) FROM PS_NAMES NM1 WHERE NM.EMPLID = NM1.EMPLID AND NM.NAME_TYPE = NM1.NAME_TYPE AND NM1.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 ACA_DAT_EXTRACT_ID Character(10) VARCHAR2(10) NOT NULL Extract ID for the ACA data
2 ACA_ALE_MEMBER Character(3) VARCHAR2(3) NOT NULL Used to contain value of ALE Member name
3 ACA_CALENDAR_YEAR Number(4,0) SMALLINT NOT NULL 1094-C/1095-C Calendar Year
4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: ACA_SF_HRBR_PVW

5 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country