ERE_EE_VW_ESP

(SQL View)
 Index Back

Permanent ERE employees

Employee population for a permanent ERE

SELECT A.ERE_ID_ESP , A.EMPLID , A.EMPL_RCD , A.TERMINATION_DT , A.TERMINATION_DT , 'P' , 'P' , A.UNEMPLOYMNT_OFFICE , A.UNEMPLYMT_TYPE_ESP , A.UMEMPL_RQST_DT_ESP , A.ERE_PRTIAL_RSN_ESP , A.ERE_EE_STATUS_ESP , A.REFERENCE_DT_ESP , A.UNEMPL_BNF_BGN_ESP , N.DATE1 , A.DAILY_CNTB_BS_ESP , A.CONTRIB_DAYS_ESP , A.CONTRIB_BASE_ESP , A.CONTRIB_CPBASE_ESP , D.ERE_PRCS_FLG_ESP , A.ACCOUNT_ID , A.ERE_WKD_RD_PCT_ESP , A.ERE_BENEFT_RSN_ESP , A.CNO_ESP , A.ERE_SPENT_DAYS_ESP , A.FTE_AVG_ESP , A.ERE_EVENT_TYPE_ESP , D.ERE_XPRCD_FLG_ESP , J.COMPANY , J.ESTABID , I.SSN_EMPLOYER , J.SETID_DEPT , J.DEPTID , ' ' FROM PS_ERE_PERM_EE_ESP A , PS_ERE_EE_PRCS_ESP D , PS_JOB J , PS_JOB_JR I , PS_NULL_DATE_ESP N WHERE A.EMPLID = D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD AND A.ERE_ID_ESP = D.ERE_ID_ESP AND A.EMPLID = J.EMPLID AND A.EMPL_RCD = J.EMPL_RCD AND J.EMPLID = I.EMPLID AND J.EMPL_RCD = I.EMPL_RCD AND J.EFFDT = I.EFFDT AND J.EFFSEQ = I.EFFSEQ AND J.EFFDT = ( SELECT MAX(ED.EFFDT) FROM PS_JOB ED WHERE ED.EMPLID = J.EMPLID AND ED.EMPL_RCD = J.EMPL_RCD AND ED.EFFDT <= A.TERMINATION_DT) AND J.EFFSEQ = ( SELECT MAX(ES.EFFSEQ) FROM PS_JOB ES WHERE ES.EMPLID = J.EMPLID AND ES.EMPL_RCD = J.EMPL_RCD AND ES.EFFDT = J.EFFDT) UNION SELECT B.ERE_ID_ESP , B.EMPLID , B.EMPL_RCD , B.ERE_BGNDT_ESP , B.ERE_ENDDT_ESP , 'S' , 'S' , B.UNEMPLOYMNT_OFFICE , B.UNEMPLYMT_TYPE_ESP , B.UMEMPL_RQST_DT_ESP , B.ERE_PRTIAL_RSN_ESP , B.ERE_EE_STATUS_ESP , B.REFERENCE_DT_ESP , B.UNEMPL_BNF_BGN_ESP , B.UNEMPL_BNF_END_ESP , B.DAILY_CNTB_BS_ESP , B.CONTRIB_DAYS_ESP , B.CONTRIB_BASE_ESP , B.CONTRIB_CPBASE_ESP , D.ERE_PRCS_FLG_ESP , B.ACCOUNT_ID , B.ERE_WKD_RD_PCT_ESP , B.ERE_BENEFT_RSN_ESP , B.CNO_ESP , B.ERE_SPENT_DAYS_ESP , B.FTE_AVG_ESP , B.ERE_EVENT_TYPE_ESP , D.ERE_XPRCD_FLG_ESP , J.COMPANY , J.ESTABID , I.SSN_EMPLOYER , J.SETID_DEPT , J.DEPTID , ' ' FROM PS_ERE_SUSP_EE_ESP B , PS_ERE_EE_PRCS_ESP D , PS_JOB J , PS_JOB_JR I WHERE B.EMPLID = D.EMPLID AND B.EMPL_RCD = D.EMPL_RCD AND B.ERE_ID_ESP = D.ERE_ID_ESP AND B.EMPLID = J.EMPLID AND B.EMPL_RCD = J.EMPL_RCD AND J.EMPLID = I.EMPLID AND J.EMPL_RCD = I.EMPL_RCD AND J.EFFDT = I.EFFDT AND J.EFFSEQ = I.EFFSEQ AND J.EFFDT = ( SELECT MAX(EDTEST.EFFDT) FROM PS_JOB EDTEST WHERE EDTEST.EMPLID = J.EMPLID AND EDTEST.EMPL_RCD = J.EMPL_RCD AND EDTEST.EFFDT <= B.ERE_BGNDT_ESP) AND J.EFFSEQ = ( SELECT MAX(ES.EFFSEQ) FROM PS_JOB ES WHERE ES.EMPLID = J.EMPLID AND ES.EMPL_RCD = J.EMPL_RCD AND ES.EFFDT = J.EFFDT) UNION SELECT C.ERE_ID_ESP , C.EMPLID , C.EMPL_RCD , C.ERE_BGNDT_ESP , C.ERE_ENDDT_ESP , 'R' , C.ERE_SCH_DST_ESP , C.UNEMPLOYMNT_OFFICE , C.UNEMPLYMT_TYPE_ESP , C.UMEMPL_RQST_DT_ESP , C.ERE_PRTIAL_RSN_ESP , C.ERE_EE_STATUS_ESP , C.REFERENCE_DT_ESP , C.UNEMPL_BNF_BGN_ESP , C.UNEMPL_BNF_END_ESP , C.DAILY_CNTB_BS_ESP , C.CONTRIB_DAYS_ESP , C.CONTRIB_BASE_ESP , C.CONTRIB_CPBASE_ESP , D.ERE_PRCS_FLG_ESP , C.ACCOUNT_ID , C.ERE_WKD_RD_PCT_ESP , C.ERE_BENEFT_RSN_ESP , C.CNO_ESP , C.ERE_SPENT_DAYS_ESP , C.FTE_AVG_ESP , C.ERE_EVENT_TYPE_ESP , D.ERE_XPRCD_FLG_ESP , J.COMPANY , J.ESTABID , I.SSN_EMPLOYER , J.SETID_DEPT , J.DEPTID , C.ERE_SCH_DST_ESP FROM PS_ERE_WSRD_EE_ESP C , PS_ERE_EE_PRCS_ESP D , PS_JOB J , PS_JOB_JR I WHERE C.EMPLID = D.EMPLID AND C.EMPL_RCD = D.EMPL_RCD AND C.ERE_ID_ESP = D.ERE_ID_ESP AND C.EMPLID = J.EMPLID AND C.EMPL_RCD = J.EMPL_RCD AND J.EMPLID = I.EMPLID AND J.EMPL_RCD = I.EMPL_RCD AND J.EFFDT = I.EFFDT AND J.EFFSEQ = I.EFFSEQ AND J.EFFDT = ( SELECT MAX(EDTEST.EFFDT) FROM PS_JOB EDTEST WHERE EDTEST.EMPLID = J.EMPLID AND EDTEST.EMPL_RCD = J.EMPL_RCD AND EDTEST.EFFDT <= C.ERE_BGNDT_ESP) AND J.EFFSEQ = ( SELECT MAX(ES.EFFSEQ) FROM PS_JOB ES WHERE ES.EMPLID = J.EMPLID AND ES.EMPL_RCD = J.EMPL_RCD AND ES.EFFDT = J.EFFDT)

# 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 EFFDT Date(10) DATE Effective Date

Default Value: %date

5 END_EFFDT Date(10) DATE End Effdt
6 ERE_TYPE_ESP Character(1) VARCHAR2(1) NOT NULL ERE Type
P=Permanent
R=Schedule Reduction
S=Suspensive
7 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
8 UNEMPLOYMNT_OFFICE Character(30) VARCHAR2(30) NOT NULL Employment Office
9 UNEMPLYMT_TYPE_ESP Character(1) VARCHAR2(1) NOT NULL Unemployment type
F=Full
P=Partial

Default Value: F

10 UMEMPL_RQST_DT_ESP Date(10) DATE Unemployment request date
11 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
12 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

13 REFERENCE_DT_ESP Date(10) DATE Reference date
14 UNEMPL_BNF_BGN_ESP Date(10) DATE Unemployment benefit begin date
15 UNEMPL_BNF_END_ESP Date(10) DATE Unemployment benefit end date
16 DAILY_CNTB_BS_ESP Number(19,6) DECIMAL(18,6) NOT NULL Daily regulatory base (BRD: Base Reguladora Diaria)
17 CONTRIB_DAYS_ESP Number(4,0) SMALLINT NOT NULL Contribution Days
18 CONTRIB_BASE_ESP Number(19,6) DECIMAL(18,6) NOT NULL Common Contingency Base
19 CONTRIB_CPBASE_ESP Number(19,6) DECIMAL(18,6) NOT NULL Professional Contingency Base
20 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

21 ACCOUNT_ID Number(3,0) SMALLINT NOT NULL Account ID

Prompt Table: ERE_BANK_ESP_VW

22 ERE_WKD_RD_PCT_ESP Number(6,2) DECIMAL(5,2) NOT NULL Work schedule reduction percentage
23 ERE_BENEFT_RSN_ESP Character(2) VARCHAR2(2) NOT NULL Benefit ERE reason
70=Contributed Benefit
74=Replenish Benefit Entitlement

Default Value: 70

24 CNO_ESP Character(4) VARCHAR2(4) NOT NULL Occupational Code

Prompt Table: XML_CNO_ESP_VW

25 ERE_SPENT_DAYS_ESP Character(4) VARCHAR2(4) NOT NULL ERE Spent Benefit Days
26 FTE_AVG_ESP Number(8,6) DECIMAL(7,6) NOT NULL FTE Average

Default Value: 1

27 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

28 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

29 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
30 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
31 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
32 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
33 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
34 ERE_SCH_DST_ESP Character(1) VARCHAR2(1) NOT NULL Work schedule distribution
D=Daily Hours Reduction
W=Work Day Reduction

Default Value: ERE_DATA_ESP.ERE_SCH_DST_ESP