GPBR_INFPA_VW

(SQL View)
 Index Back

INFPA Register for DIRF 2017

INFPA register for DIRF report 2017 layout.

SELECT A.EMPLID , A.DEPENDENT_BENEF , CASE WHEN A.BIRTHDATE IS NULL THEN ' ' ELSE %Substring(%Cast(A.BIRTHDATE, Date, Character),1,4) %Concat %Substring(%Cast(A.BIRTHDATE, Date, Character),6,2) %Concat %Substring(%Cast(A.BIRTHDATE, Date, Character),9,2) END , CASE WHEN B.NATIONAL_ID IS NULL THEN ' ' ELSE B.NATIONAL_ID END , A.NAME_DISPLAY , CASE WHEN A.RELATIONSHIP = 'SP' THEN '03' WHEN A.RELATIONSHIP = 'C' THEN '04' WHEN A.RELATIONSHIP = 'RC' THEN '04' WHEN A.RELATIONSHIP = 'FC' THEN '06' WHEN A.RELATIONSHIP = 'OC' THEN '06' WHEN A.RELATIONSHIP = 'SC' THEN '06' WHEN A.RELATIONSHIP = 'P' THEN '08' WHEN A.RELATIONSHIP = 'SA' THEN '08' ELSE '10' END FROM PS_DEPENDENT_BENEF A LEFT OUTER JOIN PS_DEP_BENEF_NID B ON B.EMPLID = A.EMPLID AND B.DEPENDENT_BENEF = A.DEPENDENT_BENEF AND B.COUNTRY = 'BRA' AND B.NATIONAL_ID_TYPE = 'CPF'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 DEPENDENT_BENEF Character(2) VARCHAR2(2) NOT NULL Dependent Benefit
01=default
3 BIRTHDATE_CHAR Character(8) VARCHAR2(8) NOT NULL Birthdate Date in String Form.
4 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
5 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
6 GPBR_RECTYPE Character(2) VARCHAR2(2) NOT NULL Record Type for Brazil Reports