EE_CNT_P_FRAVW

(SQL View)
 Index Back

Employee Other Contracts View


SELECT E.EMPLID , E.CONTRACT_NUM , E.EFFDT , E.MEMBERSHIP_NBR_FRA , E.COVERED_POP_CD_FRA , E.CONTCT_KIND_CD_FRA , E.POPULATION_CTG_FRA ,E.CODE_OPT_FRA , E.BEGIN_DT_FRA , E.END_DT_FRA FROM PS_EMPL_CNTRCT_FRA E , PS_CNTRCT_FUND_FRA A , PS_FUND_INST_FRA B WHERE E.MEMBERSHIP_NBR_FRA = A.MEMBERSHIP_NBR_FRA AND A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_CNTRCT_FUND_FRA WHERE MEMBERSHIP_NBR_FRA = A.MEMBERSHIP_NBR_FRA AND EFFDT <= E.EFFDT) AND A.FUND_INST_FRA = B.FUND_INST_FRA AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_FUND_INST_FRA WHERE FUND_INST_FRA = A.FUND_INST_FRA AND EFFDT <= A.EFFDT) AND B.EFF_STATUS = 'A' AND B.FND_INST_TYPE_FRA NOT IN ('A', 'C')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
3 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

4 MEMBERSHIP_NBR_FRA Character(20) VARCHAR2(20) NOT NULL Membership Number - France

Prompt Table: CT_FND_P_FRAVW

5 COVERED_POP_CD_FRA Character(2) VARCHAR2(2) NOT NULL Covered Population - FRA

Prompt Table: CVD_POP_FRA_TBL

6 CONTCT_KIND_CD_FRA Character(2) VARCHAR2(2) NOT NULL Kind of Contract - FRA

Prompt Table: CTC_KND_FRA_TBL

7 POPULATION_CTG_FRA Character(3) VARCHAR2(3) NOT NULL Population Category - FRA

Prompt Table: POP_CTG_EEFRA_V

8 CODE_OPT_FRA Character(30) VARCHAR2(30) NOT NULL Code Option FRA

Prompt Table: CODE_OPT_VW

9 BEGIN_DT_FRA Date(10) DATE Begin Date France
10 END_DT_FRA Date(10) DATE End Date France