ERE_ID_ESPEMPLIDPrompt Table: PERS_SRCH_GBL
EMPL_RCDPrompt Table: EMPLOYMENT
EFFDTDefault Value: %date
ERE_DEPN_ESP_VW(SQL View) |
Index Back |
|---|---|
ERE Dependents ESP VWERE Dependents ESP VW |
| SELECT D.ERE_ID_ESP , D.EMPLID , D.EMPL_RCD , A.EFFDT , COUNT(*) FROM PS_GPES_PAYEE_DEPN A , PS_DEP_BEN_EFF B , PS_DEP_BEN C , PS_ERE_EE_VW_ESP D WHERE B.DEPENDENT_BENEF = A.DEPENDENT_BENEF AND B.EMPLID = A.EMPLID AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_GPES_PAYEE_DEPN A_ED WHERE A.EMPLID = A_ED.EMPLID AND A_ED.EFFDT <= D.EFFDT) AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_DEP_BEN_EFF B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.DEPENDENT_BENEF = B_ED.DEPENDENT_BENEF AND B_ED.EFFDT <= A.EFFDT) AND B.EMPLID = C.EMPLID AND B.DEPENDENT_BENEF = C.DEPENDENT_BENEF AND D.EMPLID = A.EMPLID AND (B.RELATIONSHIP IN ('C', 'FC', 'SC', 'RC') AND %DateDiff(C.BIRTHDATE, D.EFFDT) / 365 < 26 OR (B.RELATIONSHIP IN ('C', 'FC', 'SC', 'RC','AC') AND B.DISABLED = 'Y')) GROUP BY D.ERE_ID_ESP , D.EMPLID , D.EMPL_RCD , A.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 | DEP_BENEF_CNT | Number(2,0) | SMALLINT NOT NULL | Dependent/Beneficiary Count |