GPTH_BEN_VWCE3

(SQL View)
 Index Back

EE Child Depts/Benefs

It is used to record an employee's dependents and beneficiaries. (Child education only)

SELECT EMPLID , NAME , RELATIONSHIP , NATIONAL_ID , NAME_PREFIX , FIRST_NAME , LAST_NAME FROM ( SELECT A.EMPLID , ROW_NUMBER () OVER (PARTITION BY A.EMPLID ORDER BY A.DEPENDENT_BENEF ASC ) SEQ , C.NAME , D.RELATIONSHIP , B.NATIONAL_ID , C.NAME_PREFIX , C.FIRST_NAME , C.LAST_NAME FROM PS_DEP_BEN A , PS_DEP_BENEF_NID B , PS_DEP_BEN_NAME C , PS_DEP_BEN_EFF D WHERE D.DEP_BENEF_TYPE ='B' AND D.RELATIONSHIP = 'C' AND B.COUNTRY = 'THA' AND B.NATIONAL_ID_TYPE = 'PID' AND D.STUDENT='Y' AND A.EMPLID = B.EMPLID AND A.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND A.EMPLID = C.EMPLID AND A.DEPENDENT_BENEF = C.DEPENDENT_BENEF AND A.EMPLID =D.EMPLID AND A.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND D.EMPLID = C.EMPLID AND D.DEPENDENT_BENEF = C.DEPENDENT_BENEF AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_DEP_BEN_NAME C_ED WHERE C.EMPLID = C_ED.EMPLID AND C.DEPENDENT_BENEF = C_ED.DEPENDENT_BENEF AND C_ED.EFFDT <= %CurrentDateIn ) AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEP_BEN_EFF D1 WHERE D1.EMPLID = D.EMPLID AND D1.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND D1.EFFDT <= %CurrentDateIn ) ORDER BY A.DEPENDENT_BENEF ASC ) WHERE SEQ=3

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 NAME Character(50) VARCHAR2(50) NOT NULL Name
3 RELATIONSHIP Character(2) VARCHAR2(2) NOT NULL Relationship to Employee
4 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
5 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
6 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
7 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name