EMPLIDPrompt Table: PERSON
GPTH_BENEF_VW0(SQL View) |
Index Back |
|---|---|
EE Spouse Depts/BenefsGPTH_BENEF_VW0 is used to record an employee's dependents and beneficiaries. (Spouse only) |
| SELECT A.EMPLID , A.BIRTHDATE , B.RELATIONSHIP , C.NAME_TITLE , C.NAME_PREFIX ,%TrimSubstr(C.FIRST_NAME %Concat ' ' %Concat C.MIDDLE_NAME, 1, 61) , C.FIRST_NAME , C.MIDDLE_NAME , C.LAST_NAME , D.NATIONAL_ID , E.NATIONAL_ID , G.PROCESS_INSTANCE FROM PS_DEP_BEN A LEFT OUTER JOIN PS_DEP_BENEF_NID D ON (A.EMPLID = D.EMPLID AND A.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND D.COUNTRY = 'THA' AND D.NATIONAL_ID_TYPE = 'PID' ) LEFT OUTER JOIN PS_DEP_BENEF_NID E ON (A.EMPLID = E.EMPLID AND A.DEPENDENT_BENEF = E.DEPENDENT_BENEF AND E.COUNTRY = 'THA' AND E.NATIONAL_ID_TYPE = 'TAX ID' ) , PS_DEP_BEN_EFF B , PS_DEP_BEN_NAME C , PS_GPTH_RPT_G_VW G WHERE A.EMPLID=G.EMPLID AND A.EMPLID = B.EMPLID AND A.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND B.DEP_BENEF_TYPE IN ('B','O') AND B.RELATIONSHIP = 'SP' AND B.EMPLID=C.EMPLID AND B.DEPENDENT_BENEF=C.DEPENDENT_BENEF AND A.EMPLID = C.EMPLID AND A.DEPENDENT_BENEF = C.DEPENDENT_BENEF AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_DEP_BEN_EFF B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.DEPENDENT_BENEF = B_ED.DEPENDENT_BENEF AND B_ED.EFFDT<=G.PRD_END_DT) 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<=G.PRD_END_DT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON |
| 2 | BIRTHDATE | Date(10) | DATE | Date of Birth |
| 3 | RELATIONSHIP | Character(2) | VARCHAR2(2) NOT NULL | Relationship to Employee |
| 4 | NAME_TITLE | Character(30) | VARCHAR2(30) NOT NULL | Name Title |
| 5 | NAME_PREFIX | Character(4) | VARCHAR2(4) NOT NULL | Name Prefix |
| 6 | GPTH_NAME1 | Character(80) | VARCHAR2(80) NOT NULL | Tax-Payee's First Name |
| 7 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 8 | MIDDLE_NAME | Character(30) | VARCHAR2(30) NOT NULL | MIDDLE_NAME |
| 9 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 10 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 11 | GPTH_PER_TAX_ID | Character(20) | VARCHAR2(20) NOT NULL | Person Tax ID |
| 12 | PROCESS_INSTANCE | Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |