GPCH_CA_DAT_VW

(SQL View)
 Index Back

Child Allowance View CHE

Child Allowance View CHE

SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.DEPENDENT_BENEF ,A.GPCH_CA_RELATION ,B.BIRTHDATE ,D.NAME ,A.STUDENT ,A.DISABLED ,A.GPCH_CA_EDU_END_DT ,A.GPCH_CA_CA_ELIG ,A.GPCH_CA_LEGAL_END ,A.GPCH_CA_BIRTH_CD ,A.GPCH_CA_BIRTH_PYDT ,A.GPCH_CA_FOREIGNER ,A.GPCH_CA_BALL_PCT ,A.GPCH_CA_FOR_AMOUNT ,A.CURRENCY_CD ,A.GPCH_CA_PCT FROM PS_GPCH_CA_DATA2 A ,PS_DEP_BEN B , PS_DEP_BEN_NAME D WHERE A.EMPLID=B.EMPLID AND A.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND D.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_DEP_BEN_NAME E WHERE E.EMPLID = A.EMPLID AND A.DEPENDENT_BENEF = E.DEPENDENT_BENEF AND E.EFFDT <=A.EFFDT) AND A.EMPLID = D.EMPLID AND A.DEPENDENT_BENEF = D.DEPENDENT_BENEF

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

Prompt Table: PERSON

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
3 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

4 DEPENDENT_BENEF Character(2) VARCHAR2(2) NOT NULL Dependent Benefit
01=default

Prompt Table: GPCH_CA_DATA_VW

5 GPCH_CA_RELATION Character(2) VARCHAR2(2) NOT NULL Field for Family Relationship.
10=Mother
11=Stepmother
12=FosterMother
13=Sister
14=GrandMother
20=Father
21=Stepfather
22=FosterFather
23=Brother
24=Grandfather
6 BIRTHDATE Date(10) DATE Date of Birth
7 NAME Character(50) VARCHAR2(50) NOT NULL Name
8 STUDENT Character(1) VARCHAR2(1) NOT NULL Student
N=No
Y=Yes

Y/N Table Edit

9 DISABLED Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) a dependent is a disabled.

Y/N Table Edit

10 GPCH_CA_EDU_END_DT Date(10) DATE Reduced End Date
11 GPCH_CA_CA_ELIG Character(1) VARCHAR2(1) NOT NULL Child Allowance

Y/N Table Edit

Default Value: Y

12 GPCH_CA_LEGAL_END Date(10) DATE Legal End Date
13 GPCH_CA_BIRTH_CD Character(1) VARCHAR2(1) NOT NULL Birth Allowance Type
1=Voluntary
2=Birth Statutory
3=Adoption Statutory
14 GPCH_CA_BIRTH_PYDT Date(10) DATE Birth Allowance Date
15 GPCH_CA_FOREIGNER Character(1) VARCHAR2(1) NOT NULL Child lives not in Switzerland

Y/N Table Edit

16 GPCH_CA_BALL_PCT Number(3,1) DECIMAL(2,1) NOT NULL Birth Allowance Factor
17 GPCH_CA_FOR_AMOUNT Number(9,2) DECIMAL(8,2) NOT NULL CA granted in foreign country
18 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
19 GPCH_CA_PCT Number(5,2) DECIMAL(4,2) NOT NULL Percent