GPTH_TX_DISA_VW

(SQL View)
 Index Back

Tax Allowance disabled/incap

get disabled/incap persons

SELECT A.EMPLID ,%DateNull ,B.NATIONAL_ID ,C.NAME ,D.SEX ,D.RELATIONSHIP ,' ' ,' ' ,C.LAST_NAME ,C.FIRST_NAME ,C.MIDDLE_NAME FROM PS_DEP_BEN A LEFT OUTER JOIN PS_DEP_BENEF_NID B ON A.EMPLID = B.EMPLID AND A.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND B.COUNTRY = 'THA' AND B.PRIMARY_NID='Y' , PS_DEP_BEN_NAME C , PS_DEP_BEN_EFF D WHERE ( A.EMPLID = C.EMPLID AND A.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 A.EMPLID = D.EMPLID AND A.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_DEP_BEN_EFF D_ED WHERE D.EMPLID = D_ED.EMPLID AND D.DEPENDENT_BENEF = D_ED.DEPENDENT_BENEF AND D_ED.EFFDT <= %CurrentDateIn) ) AND D.DEP_BENEF_TYPE IN ('B','O') AND D.RELATIONSHIP IN ('P','PI','SP','RC','C','SC','O') AND D.DISABLED = 'Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSON

2 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

3 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
4 NAME Character(50) VARCHAR2(50) NOT NULL Name
5 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec

Default Value: M

6 RELATIONSHIP Character(2) VARCHAR2(2) NOT NULL Relationship to Employee
7 GPTH_TX_CLAIM Character(1) VARCHAR2(1) NOT NULL Tax claim

Y/N Table Edit

8 GPTH_TX_INCAP Character(1) VARCHAR2(1) NOT NULL Incapacitated

Y/N Table Edit

9 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
10 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
11 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME