GPSG_CPF_ETH_VW

(SQL View)
 Index Back

CPF Personal Data View

This view is used for CPF calculation in global payroll SGP. It is used in Singapore GP to return the ethnic category & religion for use in determining if the employee is required to contribute to CDAC, SINDA, ECF or MBMF.

SELECT PER.EMPLID ,RLG.RELIGION_CD ,B.ETHNIC_CATEGORY FROM PS_PER_ORG_ASGN PER , PS_DIVERS_RELIGION RLG , PS_REG_REGION_TBL A , PS_DIVERS_ETHNIC ETN , PS_ETHNIC_GRP_TBL B WHERE PER.PER_ORG IN ('EMP', 'CWR') AND RLG.EMPLID = PER.EMPLID AND A.COUNTRY = 'SGP' AND RLG.REG_REGION = A.REG_REGION AND ETN.EMPLID = PER.EMPLID AND ETN.REG_REGION = A.REG_REGION AND ETN.SETID = B.SETID AND ETN.ETHNIC_GRP_CD = B.ETHNIC_GRP_CD AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ETHNIC_GRP_TBL C WHERE C.SETID = B.SETID AND C.ETHNIC_GRP_CD = B.ETHNIC_GRP_CD AND C.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 RELIGION_CD Character(3) VARCHAR2(3) NOT NULL Religion Code
3 ETHNIC_CATEGORY Character(1) VARCHAR2(1) NOT NULL Ethnic Category
1=White
2=Black
3=Hispanic
4=Asian/Pacific Islander
5=American Indian/Alaskan Native
6=Not Applicable
7=Chinese
9=Indian
A=Aboriginal/Torres Strait Islnd
B=Aboriginal Origin
C=Torres Strait Islander Origin
E=Eurasian
I=Irianese
M=Malay
O=Maori
P=Pacific Islander