ERE_ALL_EE_VW (SQL View)	Index Back
ERE Employee population Employee population for an ERE

ERE_ALL_EE_VW

(SQL View)

ERE Employee population

Employee population for an ERE

SELECT DISTINCT A.ERE_ID_ESP , A.EMPLID , A.EMPL_RCD , 'P' , A.TERMINATION_DT , A.ERE_INDM_DAYS_ESP , A.ERE_MAX_PRDS_ESP , ' ' , N.DATE1 , N.DATE1 , 0 , A.LST_ASGN_START_DT , B.ACTION , B.ACTION_REASON , B.ERE_TERM_RSN_ESP , B.PIN_TAKE_NUM , %subrec(ERE_EE_ESP_SBR, A) , A.ERE_JD_UPD_FLG , A.ERE_TE_UPD_FLG , 'N' , 'N' , A.ERE_BR_UPD_FLG , A.PROCESS_DTTM FROM PS_ERE_PERM_EE_ESP A , PS_ERE_UPD_ACT_ESP B , PS_NULL_DATE_ESP N WHERE B.ERE_U_ACTN_TYP_ESP = 'P' UNION SELECT DISTINCT A.ERE_ID_ESP , A.EMPLID , A.EMPL_RCD , 'S' , N.DATE1 , 0 , 0 , A.LACKING_IND_ESP , A.ERE_BGNDT_ESP , A.ERE_ENDDT_ESP , 0 , A.LST_ASGN_START_DT , B.ACTION , B.ACTION_REASON , B.ERE_TERM_RSN_ESP , B.PIN_TAKE_NUM , %subrec(ERE_EE_ESP_SBR, A) , A.ERE_JD_UPD_FLG , 'N' , A.ERE_CN_UPD_FLG , A.ERE_AB_UPD_FLG , A.ERE_BR_UPD_FLG , A.PROCESS_DTTM FROM PS_ERE_SUSP_EE_ESP A , PS_ERE_UPD_ACT_ESP B , PS_NULL_DATE_ESP N WHERE B.ERE_U_ACTN_TYP_ESP = 'S' UNION SELECT DISTINCT A.ERE_ID_ESP , A.EMPLID , A.EMPL_RCD , ERE_SCH_DST_ESP , N.DATE1 , 0 , 0 , A.LACKING_IND_ESP , A.ERE_BGNDT_ESP , A.ERE_ENDDT_ESP , A.PARTIAL_HRS , A.LST_ASGN_START_DT , B.ACTION , B.ACTION_REASON , B.ERE_TERM_RSN_ESP , B.PIN_TAKE_NUM , %subrec(ERE_EE_ESP_SBR, A) , A.ERE_JD_UPD_FLG , 'N' , A.ERE_CN_UPD_FLG , A.ERE_AB_UPD_FLG , A.ERE_BR_UPD_FLG , A.PROCESS_DTTM FROM PS_ERE_WSRD_EE_ESP A , PS_ERE_UPD_ACT_ESP B , PS_NULL_DATE_ESP N WHERE A.ERE_SCH_DST_ESP = B.ERE_U_ACTN_TYP_ESP AND B.ERE_U_ACTN_TYP_ESP IN ('W', 'D')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 ERE_ID_ESP Character(10) VARCHAR2(10) NOT NULL ERE ID

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERS_SRCH_GBL

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
Prompt Table: EMPLOYMENT

4 ERE_U_ACTN_TYP_ESP Character(1) VARCHAR2(1) NOT NULL ERE Update action type
D=Daily Hours Reduction
P=Permanent
S=Suspension
W=Work Day Reduction

5 TERMINATION_DT Date(10) DATE Termination Date
Default Value: ERE_DATA_ESP.TERMINATION_DT

6 ERE_INDM_DAYS_ESP Number(3,0) SMALLINT NOT NULL Days/Year
Default Value: ERE_DATA_ESP.ERE_INDM_DAYS_ESP

7 ERE_MAX_PRDS_ESP Number(3,0) SMALLINT NOT NULL Max. Periods
Default Value: ERE_DATA_ESP.ERE_MAX_PRDS_ESP

8 LACKING_IND_ESP Character(1) VARCHAR2(1) NOT NULL Benefit eligibility
N=Regular Benefit
S=Special Subsidy
Y=No Benefit
Default Value: N

9 ERE_BGNDT_ESP Date(10) DATE ERE Begin date
Default Value: ERE_DATA_ESP.ERE_BGNDT_ESP

10 ERE_ENDDT_ESP Date(10) DATE ERE End date
Default Value: ERE_DATA_ESP.ERE_ENDDT_ESP

11 PARTIAL_HRS Signed Number(10,5) DECIMAL(8,5) NOT NULL Partial Hours

12 LST_ASGN_START_DT Date(10) DATE Lastest start date of the specific EMPL_RCD assignment. This is different from the Person's organizational instance Last Hire Date.
Prompt Table: GPES_BG_DT_DVW

13 ACTION Character(3) VARCHAR2(3) NOT NULL Action
Prompt Table: ACTION_TBL

14 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
Prompt Table: ACTN_REASON_TBL

15 ERE_TERM_RSN_ESP Character(4) VARCHAR2(4) NOT NULL Termination Reason
AF97=Objective innap. after 97
BF97=Objective innap. before 97
COLL=Collective dismissal
COMP=Non-fulfillment by employer
DIIN=Disciplinary innappropiate
DISC=Disciplinary
OBJE=Objective
TEMP=End of temporary contract
VOLU=Voluntary

16 PIN_TAKE_NUM Number(8,0) INTEGER NOT NULL Absence Take
Prompt Table: GP_PIN_AT0_VW

17 UNEMPLOYMNT_OFFICE Character(30) VARCHAR2(30) NOT NULL Employment Office

18 UNEMPLYMT_TYPE_ESP Character(1) VARCHAR2(1) NOT NULL Unemployment type
F=Full
P=Partial
Default Value: F

19 UMEMPL_RQST_DT_ESP Date(10) DATE Unemployment request date

20 ERE_PRTIAL_RSN_ESP Character(2) VARCHAR2(2) NOT NULL Partial ERE reason
1=Full Time Schedule Reduction
2=Part Time Schedule Reduction
3=Part Time Contract
4=Change Full Time to Part Time
5=P Time Termination by Dismissl
6=Change Part Time to another PT
7=Part Time Termination
8=Change Part Time to another PT

21 ERE_EE_STATUS_ESP Character(2) VARCHAR2(2) NOT NULL Employee status during ERE procedure
01=Permanent
02=Temporary
03=FD without Activity Interruptn
04=FD with Activity Interruptn
05=Migrants for Less than 6 Month
06=Part-Time
07=Members of Cooperative
08=Contract Administration
09=Public Employee
10=High Charge of A.G.
11=Union Representative
12=City Council Member
Default Value: 01

22 REFERENCE_DT_ESP Date(10) DATE Reference date

23 UNEMPL_BNF_BGN_ESP Date(10) DATE Unemployment benefit begin date

24 UNEMPL_BNF_END_ESP Date(10) DATE Unemployment benefit end date

25 DAILY_CNTB_BS_ESP Number(19,6) DECIMAL(18,6) NOT NULL Daily regulatory base (BRD: Base Reguladora Diaria)

26 CONTRIB_DAYS_ESP Number(4,0) SMALLINT NOT NULL Contribution Days

27 CONTRIB_BASE_ESP Number(19,6) DECIMAL(18,6) NOT NULL Common Contingency Base

28 CONTRIB_CPBASE_ESP Number(19,6) DECIMAL(18,6) NOT NULL Professional Contingency Base

29 ERE_PRCS_FLG_ESP Character(1) VARCHAR2(1) NOT NULL Report as XML flag
0=Not Communicated
1=XML Generated
2=XML Sent/Confirmated
Default Value: 0

30 ACCOUNT_ID Number(3,0) SMALLINT NOT NULL Account ID
Prompt Table: ERE_BANK_ESP_VW

31 ERE_WKD_RD_PCT_ESP Number(6,2) DECIMAL(5,2) NOT NULL Work schedule reduction percentage

32 ERE_BENEFT_RSN_ESP Character(2) VARCHAR2(2) NOT NULL Benefit ERE reason
70=Contributed Benefit
74=Replenish Benefit Entitlement
Default Value: 70

33 CNO_ESP Character(4) VARCHAR2(4) NOT NULL Occupational Code
Prompt Table: XML_CNO_ESP_VW

34 ERE_SPENT_DAYS_ESP Character(4) VARCHAR2(4) NOT NULL ERE Spent Benefit Days

35 FTE_AVG_ESP Number(8,6) DECIMAL(7,6) NOT NULL FTE Average
Default Value: 1

36 ERE_EVENT_TYPE_ESP Character(1) VARCHAR2(1) NOT NULL ERE Event Type A : New B : Delete M: Update
A=New
B=Delete
M=Update
Default Value: A

37 ERE_XPRCD_FLG_ESP Character(1) VARCHAR2(1) NOT NULL ERE XML Procedure Status 0 : No reported 1: XML Generated
0=No Communicated
1=XML Generated
Default Value: 0

38 ERE_JD_UPD_FLG Character(1) VARCHAR2(1) NOT NULL "ERE Data update" Job Data update flag.
Y/N Table Edit
Default Value: N

39 ERE_TE_UPD_FLG Character(1) VARCHAR2(1) NOT NULL "ERE Data Update" Manage termination update flag.
Y/N Table Edit
Default Value: N

40 ERE_CN_UPD_FLG Character(1) VARCHAR2(1) NOT NULL "ERE Data Update" Contract data update flag
Y/N Table Edit
Default Value: N

41 ERE_AB_UPD_FLG Character(1) VARCHAR2(1) NOT NULL "ERE Data Update" Absence event update flag.
Y/N Table Edit
Default Value: N

42 ERE_BR_UPD_FLG Character(1) VARCHAR2(1) NOT NULL BRD update flag
Y/N Table Edit
Default Value: N

43 PROCESS_DTTM DateTime(26) TIMESTAMP Process Date/Time

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	ERE_ID_ESP	Character(10)	VARCHAR2(10) NOT NULL	ERE ID
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERS_SRCH_GBL
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record Prompt Table: EMPLOYMENT
4	ERE_U_ACTN_TYP_ESP	Character(1)	VARCHAR2(1) NOT NULL	ERE Update action type D=Daily Hours Reduction P=Permanent S=Suspension W=Work Day Reduction
5	TERMINATION_DT	Date(10)	DATE	Termination Date Default Value: ERE_DATA_ESP.TERMINATION_DT
6	ERE_INDM_DAYS_ESP	Number(3,0)	SMALLINT NOT NULL	Days/Year Default Value: ERE_DATA_ESP.ERE_INDM_DAYS_ESP
7	ERE_MAX_PRDS_ESP	Number(3,0)	SMALLINT NOT NULL	Max. Periods Default Value: ERE_DATA_ESP.ERE_MAX_PRDS_ESP
8	LACKING_IND_ESP	Character(1)	VARCHAR2(1) NOT NULL	Benefit eligibility N=Regular Benefit S=Special Subsidy Y=No Benefit Default Value: N
9	ERE_BGNDT_ESP	Date(10)	DATE	ERE Begin date Default Value: ERE_DATA_ESP.ERE_BGNDT_ESP
10	ERE_ENDDT_ESP	Date(10)	DATE	ERE End date Default Value: ERE_DATA_ESP.ERE_ENDDT_ESP
11	PARTIAL_HRS	Signed Number(10,5)	DECIMAL(8,5) NOT NULL	Partial Hours
12	LST_ASGN_START_DT	Date(10)	DATE	Lastest start date of the specific EMPL_RCD assignment. This is different from the Person's organizational instance Last Hire Date. Prompt Table: GPES_BG_DT_DVW
13	ACTION	Character(3)	VARCHAR2(3) NOT NULL	Action Prompt Table: ACTION_TBL
14	ACTION_REASON	Character(3)	VARCHAR2(3) NOT NULL	Reason Code Prompt Table: ACTN_REASON_TBL
15	ERE_TERM_RSN_ESP	Character(4)	VARCHAR2(4) NOT NULL	Termination Reason AF97=Objective innap. after 97 BF97=Objective innap. before 97 COLL=Collective dismissal COMP=Non-fulfillment by employer DIIN=Disciplinary innappropiate DISC=Disciplinary OBJE=Objective TEMP=End of temporary contract VOLU=Voluntary
16	PIN_TAKE_NUM	Number(8,0)	INTEGER NOT NULL	Absence Take Prompt Table: GP_PIN_AT0_VW
17	UNEMPLOYMNT_OFFICE	Character(30)	VARCHAR2(30) NOT NULL	Employment Office
18	UNEMPLYMT_TYPE_ESP	Character(1)	VARCHAR2(1) NOT NULL	Unemployment type F=Full P=Partial Default Value: F
19	UMEMPL_RQST_DT_ESP	Date(10)	DATE	Unemployment request date
20	ERE_PRTIAL_RSN_ESP	Character(2)	VARCHAR2(2) NOT NULL	Partial ERE reason 1=Full Time Schedule Reduction 2=Part Time Schedule Reduction 3=Part Time Contract 4=Change Full Time to Part Time 5=P Time Termination by Dismissl 6=Change Part Time to another PT 7=Part Time Termination 8=Change Part Time to another PT
21	ERE_EE_STATUS_ESP	Character(2)	VARCHAR2(2) NOT NULL	Employee status during ERE procedure 01=Permanent 02=Temporary 03=FD without Activity Interruptn 04=FD with Activity Interruptn 05=Migrants for Less than 6 Month 06=Part-Time 07=Members of Cooperative 08=Contract Administration 09=Public Employee 10=High Charge of A.G. 11=Union Representative 12=City Council Member Default Value: 01
22	REFERENCE_DT_ESP	Date(10)	DATE	Reference date
23	UNEMPL_BNF_BGN_ESP	Date(10)	DATE	Unemployment benefit begin date
24	UNEMPL_BNF_END_ESP	Date(10)	DATE	Unemployment benefit end date
25	DAILY_CNTB_BS_ESP	Number(19,6)	DECIMAL(18,6) NOT NULL	Daily regulatory base (BRD: Base Reguladora Diaria)
26	CONTRIB_DAYS_ESP	Number(4,0)	SMALLINT NOT NULL	Contribution Days
27	CONTRIB_BASE_ESP	Number(19,6)	DECIMAL(18,6) NOT NULL	Common Contingency Base
28	CONTRIB_CPBASE_ESP	Number(19,6)	DECIMAL(18,6) NOT NULL	Professional Contingency Base
29	ERE_PRCS_FLG_ESP	Character(1)	VARCHAR2(1) NOT NULL	Report as XML flag 0=Not Communicated 1=XML Generated 2=XML Sent/Confirmated Default Value: 0
30	ACCOUNT_ID	Number(3,0)	SMALLINT NOT NULL	Account ID Prompt Table: ERE_BANK_ESP_VW
31	ERE_WKD_RD_PCT_ESP	Number(6,2)	DECIMAL(5,2) NOT NULL	Work schedule reduction percentage
32	ERE_BENEFT_RSN_ESP	Character(2)	VARCHAR2(2) NOT NULL	Benefit ERE reason 70=Contributed Benefit 74=Replenish Benefit Entitlement Default Value: 70
33	CNO_ESP	Character(4)	VARCHAR2(4) NOT NULL	Occupational Code Prompt Table: XML_CNO_ESP_VW
34	ERE_SPENT_DAYS_ESP	Character(4)	VARCHAR2(4) NOT NULL	ERE Spent Benefit Days
35	FTE_AVG_ESP	Number(8,6)	DECIMAL(7,6) NOT NULL	FTE Average Default Value: 1
36	ERE_EVENT_TYPE_ESP	Character(1)	VARCHAR2(1) NOT NULL	ERE Event Type A : New B : Delete M: Update A=New B=Delete M=Update Default Value: A
37	ERE_XPRCD_FLG_ESP	Character(1)	VARCHAR2(1) NOT NULL	ERE XML Procedure Status 0 : No reported 1: XML Generated 0=No Communicated 1=XML Generated Default Value: 0
38	ERE_JD_UPD_FLG	Character(1)	VARCHAR2(1) NOT NULL	"ERE Data update" Job Data update flag. Y/N Table Edit Default Value: N
39	ERE_TE_UPD_FLG	Character(1)	VARCHAR2(1) NOT NULL	"ERE Data Update" Manage termination update flag. Y/N Table Edit Default Value: N
40	ERE_CN_UPD_FLG	Character(1)	VARCHAR2(1) NOT NULL	"ERE Data Update" Contract data update flag Y/N Table Edit Default Value: N
41	ERE_AB_UPD_FLG	Character(1)	VARCHAR2(1) NOT NULL	"ERE Data Update" Absence event update flag. Y/N Table Edit Default Value: N
42	ERE_BR_UPD_FLG	Character(1)	VARCHAR2(1) NOT NULL	BRD update flag Y/N Table Edit Default Value: N
43	PROCESS_DTTM	DateTime(26)	TIMESTAMP	Process Date/Time