GPCH_EO_ABR_VW1 (SQL View)	Index Back
EO Calculation EO Calculation Read with Children

GPCH_EO_ABR_VW1

(SQL View)

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 Record

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 Signed Number(6,0) DECIMAL(5) 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

#	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 Record
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	Signed Number(6,0)	DECIMAL(5) 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