GPCH_EO_ABR_VW1

(SQL View)
 Index Back

EO Calculation

EO Calculation Read with Children

SELECT EO.EMPLID , EO.EMPL_RCD , EO.EFFDT , EO.FROM_DT ,EO.TO_DATE , EO.THRUDATE , EO.ACTION_DT , EO.DAYS_DIFF , EO.GPCH_EO_SERV_TYPE , EO.GPCH_EO_CARD_TYPE , EO.DEP_BENEF_CNT , EO.CURRENCY_CD , EO.GPCH_EO_ANNUAL_AM , EO.GPCH_EO_DIFF_AM , EO.GPCH_EO_DAILY_RT , EO.GPCH_EO_CORR_IND , J.COMPANY , J.ANNUAL_RT , COUNT (*) , EO.ACCOUNT , EO.CONTROL_NUMBER , EO.GPCH_TX_CANTON , EO.COUNTRY , EO.GPCH_EO_IV_GUARANT , EO.MAR_STATUS , EO.GPCH_EO_REFNBR FROM PS_GPCH_EO_ABR EO , PS_GPCH_CA_DATA1 C1 , PS_GPCH_CA_DATA2 C2 , PS_JOB J , PS_DEPENDENT_BENEF D WHERE EO.EMPLID = C1.EMPLID AND EO.EMPL_RCD = C1.EMPL_RCD AND EO.EMPLID = C2.EMPLID AND EO.EMPL_RCD = C2.EMPL_RCD AND C1.EFFDT = C2.EFFDT AND C1.EFFDT = ( SELECT MAX(C3.EFFDT) FROM PS_GPCH_CA_DATA1 C3 WHERE C1.EMPLID = C3.EMPLID AND C3.EFFDT <= EO.FROM_DT ) AND C2.EMPLID = D.EMPLID AND C2.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND ((%Sql(GPCH_AL_TMPL_FUNC_YEARDIFF_U,D.BIRTHDATE,EO.FROM_DT ) < 25 AND C2.STUDENT = 'Y' AND C2.GPCH_CA_EDU_END_DT >= EO.FROM_DT) OR (%Sql(GPCH_AL_TMPL_FUNC_YEARDIFF_U,D.BIRTHDATE,EO.FROM_DT ) < 18) ) AND EO.EMPLID = J.EMPLID AND D.BIRTHDATE <= EO.THRUDATE AND EO.EMPL_RCD = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J.EMPLID = J1.EMPLID AND J.EMPL_RCD = J1.EMPL_RCD AND J1.EFFDT <= EO.FROM_DT ) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J.EMPLID = J2.EMPLID AND J.EMPL_RCD = J2.EMPL_RCD AND J.EFFDT = J2.EFFDT ) AND EO.FROM_DT > %DateIn('1900-01-01') GROUP BY EO.EMPLID , EO.EMPL_RCD , EO.EFFDT ,EO.TO_DATE, EO.THRUDATE , EO.ACTION_DT , EO.DAYS_DIFF , EO.GPCH_EO_SERV_TYPE , EO.GPCH_EO_CARD_TYPE , EO.FROM_DT , EO.DEP_BENEF_CNT , EO.CURRENCY_CD , EO.GPCH_EO_ANNUAL_AM , EO.GPCH_EO_DIFF_AM , EO.GPCH_EO_DAILY_RT , EO.GPCH_EO_CORR_IND , J.COMPANY , J.ANNUAL_RT, EO.ACCOUNT, EO.CONTROL_NUMBER, EO.GPCH_TX_CANTON , EO.COUNTRY , EO.GPCH_EO_IV_GUARANT , EO.MAR_STATUS, EO.GPCH_EO_REFNBR

# 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
4 FROM_DT Date(10) DATE From Date
5 TO_DATE Date(10) DATE Latest Date to be sent to PYR
6 THRUDATE Date(10) DATE End Date
7 ACTION_DT Date(10) DATE Action Date

Default Value: %Date

8 DAYS_DIFF Number(4,0) SMALLINT NOT NULL Days Difference
9 GPCH_EO_SERV_TYPE Character(2) VARCHAR2(2) NOT NULL Service Type
10=10 Military - Regular
11=11 Military Recruit
12=12 Military Promotion
13=13 Selection
14=14 Continued Service Uof
20=20 Civil defence
21=21 CivilS Recruit
22=22 Civils Mgr&Specialist
23=23 Civils&Serv Command
30=30 Trainer / J+S
40=40 Civil Service - regular
41=41 Civil Service - recruit
50=50 Young Rifleman Leader
10 GPCH_EO_CARD_TYPE Character(1) VARCHAR2(1) NOT NULL Card Type
1=1 EO Card
2=2 EO Replacement Card
4=4 Corrected Claim
11 DEP_BENEF_CNT Number(2,0) SMALLINT NOT NULL Dependent/Beneficiary Count
12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: OPR_DEF_TBL_HR.EXCHNG_TO_CURRENCY

13 GPCH_EO_ANNUAL_AM Signed Number(12,2) DECIMAL(10,2) NOT NULL Annual Amount
14 GPCH_EO_DIFF_AM Signed Number(12,2) DECIMAL(10,2) NOT NULL Amount Difference
15 GPCH_EO_DAILY_RT Signed Number(12,2) DECIMAL(10,2) NOT NULL Annual Amount
16 GPCH_EO_CORR_IND Character(1) VARCHAR2(1) NOT NULL Household Allowance
17 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
18 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
19 COUNTER Number(10,0) DECIMAL(10) NOT NULL Counter
20 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
21 CONTROL_NUMBER Character(12) VARCHAR2(12) NOT NULL Control Number
22 GPCH_TX_CANTON Character(2) VARCHAR2(2) NOT NULL Canton
23 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
24 GPCH_EO_IV_GUARANT Character(1) VARCHAR2(1) NOT NULL Indicator IV-Daily Payment for EO Page

Y/N Table Edit

Default Value: N

25 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
C=Common-Law
D=Divorced
E=Separated
H=Head of Household
L=DissDeclLost Civil Partner
M=Married
P=Civil Partnership
S=Single
T=Surviving Civil Partner
U=Unknown
V=Dissolved Civil Partnership
W=Widowed
26 GPCH_EO_REFNBR Number(8,0) INTEGER NOT NULL Unique reference number for Military data