GPES_FDI_EE_VW (SQL View)	Index Back
Create FDI File ESP Create FDI File

GPES_FDI_EE_VW

(SQL View)

Create FDI File ESP

Create FDI File

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.BGN_DT , A.PIN_TAKE_NUM , A.END_DT , A.GPES_SICK_DATE , A.GPES_REASON_DISCHR , J.COMPANY , A.GPES_FDI_NOTE_TYPE , JR.SSN_EMPLOYER , B.ORIG_BEGIN_DT , B.ABSENCE_REASON , A.GPES_CONFIRM_SEND FROM PS_GPES_ABSEVT_NT A , PS_GP_ABS_EVENT B , PS_JOB J , PS_JOB_JR JR WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.BGN_DT = B.BGN_DT AND A.PIN_TAKE_NUM = B.PIN_TAKE_NUM AND A.END_DT = B.END_DT AND J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EMPLID = JR.EMPLID AND J.EMPL_RCD = JR.EMPL_RCD AND J.EFFDT = JR.EFFDT AND J.EFFSEQ = JR.EFFSEQ AND B.BGN_DT >= J.EFFDT AND J.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE J.EMPLID = C.EMPLID AND J.EMPL_RCD = C.EMPL_RCD AND B.BGN_DT >= C.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Default Value: PER_ORG_ASGN.EMPLID
Prompt Table: PERS_SRCH_GBL

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

3 BGN_DT Date(10) DATE NOT NULL Begin Date

4 PIN_TAKE_NUM Number(8,0) INTEGER NOT NULL Absence Take

5 END_DT Date(10) DATE NOT NULL end date

6 GPES_SICK_DATE Date(10) DATE Date to Sick note

7 GPES_REASON_DISCHR Character(2) VARCHAR2(2) NOT NULL Reason Discharge
01=Healing
02=Decease
03=Medical Inspection
04=Incapacity Proposal
05=Time Limit Exhaustion
06=Work Habitual
07=Absence
10=Control INSS (12 months)
17=Recovery Professional Capacity
18=Contracts Formation Absence

8 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
Prompt Table: COMPANY_TBL

9 GPES_FDI_NOTE_TYPE Character(2) VARCHAR2(2) NOT NULL Type
PA=Medical Discharge
PB=Sick Note
PC=Confirmation Sick Note
Default Value: PB

10 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
Prompt Table: ESTB_CCC_ALL_VW

11 ORIG_BEGIN_DT Date(10) DATE original begin date

12 GPES_FDI_ABS_RSN Character(3) VARCHAR2(3) NOT NULL FDI Absence Reason
1=Common illness
2=Non-Industrial accident
3=Industrial Accident
4=Professioanl illness
5=Prof Illness Observ Period

13 GPES_CONFIRM_SEND Character(1) VARCHAR2(1) NOT NULL Confirmed Send
1=Included in FDI
N=No Communicated
Y=FDI Sent Confirmed

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Default Value: PER_ORG_ASGN.EMPLID Prompt Table: PERS_SRCH_GBL
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
3	BGN_DT	Date(10)	DATE NOT NULL	Begin Date
4	PIN_TAKE_NUM	Number(8,0)	INTEGER NOT NULL	Absence Take
5	END_DT	Date(10)	DATE NOT NULL	end date
6	GPES_SICK_DATE	Date(10)	DATE	Date to Sick note
7	GPES_REASON_DISCHR	Character(2)	VARCHAR2(2) NOT NULL	Reason Discharge 01=Healing 02=Decease 03=Medical Inspection 04=Incapacity Proposal 05=Time Limit Exhaustion 06=Work Habitual 07=Absence 10=Control INSS (12 months) 17=Recovery Professional Capacity 18=Contracts Formation Absence
8	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company Prompt Table: COMPANY_TBL
9	GPES_FDI_NOTE_TYPE	Character(2)	VARCHAR2(2) NOT NULL	Type PA=Medical Discharge PB=Sick Note PC=Confirmation Sick Note Default Value: PB
10	SSN_EMPLOYER	Character(13)	VARCHAR2(13) NOT NULL	Employer Social Security number Prompt Table: ESTB_CCC_ALL_VW
11	ORIG_BEGIN_DT	Date(10)	DATE	original begin date
12	GPES_FDI_ABS_RSN	Character(3)	VARCHAR2(3) NOT NULL	FDI Absence Reason 1=Common illness 2=Non-Industrial accident 3=Industrial Accident 4=Professioanl illness 5=Prof Illness Observ Period
13	GPES_CONFIRM_SEND	Character(1)	VARCHAR2(1) NOT NULL	Confirmed Send 1=Included in FDI N=No Communicated Y=FDI Sent Confirmed