CNTRCT_EEFRA_VW

(SQL View)
 Index Back

View for EE Contract Inquiry


SELECT A.EMPLID , A.EMPL_RCD , N.NAME , A.PER_ORG , A.POI_TYPE , J.COMPANY , J.ESTABID , C.CONTRACT_NUM , JC.INSEE_CD_FRA FROM PS_PER_ORG_ASGN A , PS_JOB J , PS_JOB ORG , PS_CONTRACT_DATA C , PS_JOBCODE_TBL JC , PS_NAMES N WHERE A.PER_ORG='EMP' AND A.EMPLID = J.EMPLID AND A.EMPL_RCD = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE J.EMPLID = JOB2.EMPLID AND J.EMPL_RCD = JOB2.EMPL_RCD AND ((JOB2.EFFDT <= %CurrentDateIn) OR (JOB2.EFFDT > %CurrentDateIn AND %CurrentDateIn <( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD)))) AND J.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = J.EMPLID AND JOB3.EMPL_RCD = J.EMPL_RCD AND JOB3.EFFDT = J.EFFDT) AND A.EMPLID = ORG.EMPLID AND A.ORG_INSTANCE_ERN = ORG.EMPL_RCD AND ORG.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB ORG2 WHERE ORG.EMPLID = ORG2.EMPLID AND ORG.EMPL_RCD = ORG2.EMPL_RCD AND ((ORG2.EFFDT <= %CurrentDateIn) OR (ORG2.EFFDT > %CurrentDateIn AND %CurrentDateIn <( SELECT MIN(J2ORG.EFFDT) FROM PS_JOB J2ORG WHERE J2ORG.EMPLID = ORG.EMPLID AND J2ORG.EMPL_RCD = ORG.EMPL_RCD)))) AND ORG.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB ORG3 WHERE ORG3.EMPLID = ORG.EMPLID AND ORG3.EMPL_RCD = ORG.EMPL_RCD AND ORG3.EFFDT = ORG.EFFDT) AND C.EMPLID = A.EMPLID AND JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE = J.JOBCODE AND JC.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL WHERE SETID=JC.SETID AND JOBCODE=JC.JOBCODE AND EFFDT<=%CurrentDateIn) AND JC.EFF_STATUS = 'A' AND N.EMPLID=A.EMPLID AND N.NAME_TYPE='PRI' AND N.EFFDT <= ( SELECT MAX(EFFDT) FROM PS_NAMES WHERE EMPLID = N.EMPLID AND EFFDT <=%CurrentDateIn)

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

Prompt Table: PERSON

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

Default Value: 0

3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest

Prompt Table: PER_ORG_VW

5 POI_TYPE Character(5) VARCHAR2(5) NOT NULL Defines the Transactional usage of POIs in HRMS.

Prompt Table: POI_TYPE_TBL

6 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
7 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
8 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
9 INSEE_CD_FRA Character(4) VARCHAR2(4) NOT NULL Insee Code - FRA