GPSG_CITIZEN_VW

(SQL View)
 Index Back

Citizenship Status

Citizenship Status as of the payroll calculation date

SELECT DISTINCT A.EMPLID , A.PERIOD_END_DATE , A.GP_PAYGROUP , A.BALANCE_GRP_NUM , A.GPSG_CPF_EMPLR_REF , A.CITIZENSHIP_STATUS FROM PS_GP_PYE_PRC_STAT C , PS_GP_PYE_SEG_STAT B , PS_GPSG_CPF_RSLTS A WHERE A.EMPLID = B.EMPLID AND A.CAL_RUN_ID = B.CAL_RUN_ID AND A.EMPL_RCD = B.EMPL_RCD AND A.GP_PAYGROUP = B.GP_PAYGROUP AND A.CAL_ID = B.CAL_ID AND A.ORIG_CAL_RUN_ID = B.ORIG_CAL_RUN_ID AND A.RSLT_SEG_NUM = B.RSLT_SEG_NUM AND B.EMPLID = C.EMPLID AND B.CAL_RUN_ID = C.CAL_RUN_ID AND B.EMPL_RCD = C.EMPL_RCD AND B.GP_PAYGROUP = C.GP_PAYGROUP AND B.CAL_ID = C.CAL_ID AND B.ORIG_CAL_RUN_ID = C.ORIG_CAL_RUN_ID AND B.PYE_CALC_STAT >= '60' AND C.CALC_TYPE = 'P' AND B.SEG_END_DT = ( SELECT MAX(B1.SEG_END_DT) FROM PS_GP_PYE_SEG_STAT B1 WHERE B1.EMPLID = B.EMPLID AND B1.CAL_RUN_ID = B.CAL_RUN_ID AND B1.EMPL_RCD = B.EMPL_RCD AND B1.GP_PAYGROUP = B.GP_PAYGROUP AND B1.CAL_ID = B.CAL_ID AND B1.ORIG_CAL_RUN_ID = B.ORIG_CAL_RUN_ID AND B1.RSLT_SEG_NUM = B.RSLT_SEG_NUM) AND B.GP_CALC_TS = ( SELECT MAX(B2.GP_CALC_TS) FROM PS_GP_PYE_SEG_STAT B2 WHERE B2.EMPLID = B.EMPLID AND B2.EMPL_RCD = B.EMPL_RCD AND B2.SEG_END_DT = B.SEG_END_DT AND B2.GP_PAYGROUP = B.GP_PAYGROUP AND B2.RSLT_SEG_NUM = B.RSLT_SEG_NUM AND B2.PYE_CALC_STAT >= '60')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 PERIOD_END_DATE Date(10) DATE Period End Date
3 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
4 BALANCE_GRP_NUM Character(3) VARCHAR2(3) NOT NULL This field will be used to determine the grouping of balances from the different jobs of the employee.
5 GPSG_CPF_EMPLR_REF Character(15) VARCHAR2(15) NOT NULL CPF Employer Reference no.
6 CITIZENSHIP_STATUS Character(1) VARCHAR2(1) NOT NULL Citizenship Status