GPES_H_BG_DT_VW

(SQL View)
 Index Back

Hire date begin option

Hire date begin option. Retrieves Hire Date / Rehire Date / Contract Begin Date for an employee.

SELECT DISTINCT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.ACTION ,X.ACTION_DESCR ,A.COMPANY FROM PS_JOB A ,PS_ACTION_TBL X ,PS_ACTION_STAT_TBL Y WHERE X.ACTION = A.ACTION AND X.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ACTION_TBL WHERE ACTION = X.ACTION AND EFFDT <= A.EFFDT) AND X.EFF_STATUS <> 'I' AND Y.ACTION = X.ACTION AND (Y.SET_HIRE_DT = '2' OR Y.SET_ASGN_LST_DT = '2') UNION SELECT DISTINCT A.EMPLID , A.EMPL_RCD , B.CONTRACT_BEGIN_DT , Z.ACTION , Z.DESCR , A.COMPANY FROM PS_JOB A , PS_CONTRACT_DATA B , PS_ACTN_REASON_TBL Z WHERE A.EMPLID = B.EMPLID AND A.CONTRACT_NUM = B.CONTRACT_NUM AND Z.ACTION = 'DTA' AND Z.ACTION_REASON = 'CNT' AND Z.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ACTN_REASON_TBL WHERE ACTION = Z.ACTION AND ACTION_REASON = Z.ACTION_REASON AND EFFDT <= A.EFFDT) AND Z.EFF_STATUS <> 'I' AND B.CONTRACT_BEGIN_DT <> A.LST_ASGN_START_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 LST_ASGN_START_DT Date(10) DATE Lastest start date of the specific EMPL_RCD assignment. This is different from the Person's organizational instance Last Hire Date.
4 ACTION Character(3) VARCHAR2(3) NOT NULL Action
5 ACTION_DESCR Character(50) VARCHAR2(50) NOT NULL Action Description
6 COMPANY_SEC Character(3) VARCHAR2(3) NOT NULL Company