GPBR_PRS_NID_VW

(SQL View)
 Index Back

Person National ID BRA Vw

Person National ID view for digital CTPS in legal reports of Global Payroll BRA.

SELECT A.EMPLID , A.COUNTRY , A.NATIONAL_ID_TYPE , CASE WHEN A.NATIONAL_ID_TYPE = 'CPF' THEN %Substring(A.NATIONAL_ID , 1 , 7) ELSE A.NATIONAL_ID END , CASE WHEN A.NATIONAL_ID_TYPE = 'CPF' THEN %Substring(A.NATIONAL_ID , 8 , 4) WHEN B.CTPS_SERIES_BRA IS NULL THEN ' ' ELSE B.CTPS_SERIES_BRA END FROM PS_PERS_NID A LEFT OUTER JOIN PS_PERSON_BRA B ON B.EMPLID = A.EMPLID WHERE A.COUNTRY = 'BRA' AND (A.NATIONAL_ID_TYPE <> 'CPF' OR (A.NATIONAL_ID_TYPE = 'CPF' AND NOT EXISTS ( SELECT 'X' FROM PS_PERS_NID A1 WHERE A1.EMPLID = A.EMPLID AND A1.COUNTRY = A.COUNTRY AND A1.NATIONAL_ID_TYPE = 'CTPS')))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
3 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type
4 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
5 CTPS_SERIES_BRA Character(5) VARCHAR2(5) NOT NULL CTPS Series