GPTH_CNID_VW

(SQL View)
 Index Back

EE Dep/Ben with Child NID

Employee's dependents and beneficiaries.(Child only)

SELECT A.EMPLID , ROW_NUMBER () OVER (PARTITION BY A.EMPLID ,G.PROCESS_INSTANCE ORDER BY A.DEPENDENT_BENEF ASC ) SEQ , B.NATIONAL_ID , G.PROCESS_INSTANCE FROM PS_DEP_BEN A , PS_DEP_BENEF_NID B , PS_DEP_BEN_EFF D , PS_GPTH_RPT_G_VW G WHERE D.DEP_BENEF_TYPE ='B' AND D.RELATIONSHIP = 'C' AND B.COUNTRY = 'THA' AND B.NATIONAL_ID_TYPE = 'PID' AND D.STUDENT='N' AND A.EMPLID = B.EMPLID AND A.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND A.EMPLID =D.EMPLID AND A.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND A.EMPLID=G.EMPLID 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 <= G.PRD_END_DT ) ORDER BY A.DEPENDENT_BENEF ASC

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 GPTH_SEQ_NO Number(10,0) DECIMAL(10) NOT NULL GPTH_SEQ_NO
3 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
4 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance