REPRESNT_ESP_VW

(SQL View)
 Index Back

Employee Company Rep VW ESP

This view retrieves the Representatives for Employee Company.

SELECT D.EMPLID , D.EFFDT , D.CONTRACT_NUM , J.COMPANY , A.REPRESNT_ID_ESP , A.REPRESNT_ROLE_ESP , B.NAME , C.NATIONAL_ID FROM PS_REPRESNTATV_ESP A , PS_NAMES B , PS_PERS_NID C , PS_WKF_CNT_TYP_ESP D , PS_JOB_CNT_ESP_VW J WHERE D.EMPLID = J.EMPLID AND D.CONTRACT_NUM = J.CONTRACT_NUM AND J.EFFDT = ( SELECT MAX (ED.EFFDT) FROM PS_JOB_CNT_ESP_VW ED WHERE J.EMPLID = ED.EMPLID AND J.CONTRACT_NUM = ED.CONTRACT_NUM AND ED.EFFDT <= D.EFFDT) AND D.REPRESNT_ID_ESP = A.REPRESNT_ID_ESP AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND C.COUNTRY = 'ESP' AND (C.NATIONAL_ID_TYPE = 'DNI' OR C.NATIONAL_ID_TYPE = 'NIE')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSON

2 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

3 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
4 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

5 REPRESNT_ID_ESP Character(11) VARCHAR2(11) NOT NULL Company Representative ID
6 REPRESNT_ROLE_ESP Character(50) VARCHAR2(50) NOT NULL Representative Role
7 NAME Character(50) VARCHAR2(50) NOT NULL Name
8 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID