PERS_NID_BRA_VW

(SQL View)
 Index Back

Person National ID BRA Vw

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

SELECT A.EMPLID , C.EMPL_RCD , 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 '0' %Concat %Substring(A.NATIONAL_ID , 8 , 4) WHEN A.NATIONAL_ID_TYPE = 'CTPS' THEN CASE WHEN B.CTPS_SERIES_BRA IS NULL THEN ' ' ELSE B.CTPS_SERIES_BRA END ELSE ' ' END , CASE WHEN A.NATIONAL_ID_TYPE = 'CPF' THEN D.STATE WHEN A.NATIONAL_ID_TYPE = 'CTPS' THEN CASE WHEN B.CTPS_STATE_BRA IS NULL THEN ' ' ELSE B.CTPS_STATE_BRA END ELSE ' ' END FROM PS_JOB C , PS_ESTAB_TBL D , 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'))) AND C.EMPLID = A.EMPLID AND C.ACTION = 'HIR' AND D.ESTABID = C.ESTABID AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_ESTAB_TBL D1 WHERE D1.ESTABID = D.ESTABID AND D1.EFFDT <= C.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
4 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type
5 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
6 CTPS_SERIES_BRA Character(5) VARCHAR2(5) NOT NULL CTPS Series
7 CTPS_STATE_BRA Character(6) VARCHAR2(6) NOT NULL CTPS State