JOB_ACTRSN_VW

(SQL View)
 Index Back

Job - Action Reason view

This view is a union of the following: 1) joins Job and Action Reason table for the Action Type attribute (delivered as Voluntary/Involuntary).. 2) joins Job and Action table to return the Action Type if no result was returned for 1)

SELECT JOB.EMPLID ,JOB.EMPL_RCD ,JOB.EFFDT ,JOB.EFFSEQ ,JOB.ACTION ,JOB.ACTION_REASON ,RSN.HR_ACTION_TYPE FROM PS_JOB JOB ,PS_ACTN_REASON_TBL RSN WHERE JOB.ACTION = RSN.ACTION AND JOB.ACTION_REASON = RSN.ACTION_REASON AND RSN.EFF_STATUS = 'A' AND RSN.EFFDT = ( SELECT MAX(RSN1.EFFDT) FROM PS_ACTN_REASON_TBL RSN1 WHERE RSN1.ACTION = RSN.ACTION AND RSN1.ACTION_REASON = RSN.ACTION_REASON AND RSN1.EFFDT <= JOB.EFFDT) UNION SELECT JOB.EMPLID ,JOB.EMPL_RCD ,JOB.EFFDT ,JOB.EFFSEQ ,JOB.ACTION ,' ' ,ACT.HR_ACTION_TYPE FROM PS_JOB JOB ,PS_ACTION_TBL ACT WHERE JOB.ACTION = ACT.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(ACT1.EFFDT) FROM PS_ACTION_TBL ACT1 WHERE ACT1.ACTION = ACT.ACTION AND ACT1.EFFDT <= JOB.EFFDT) AND NOT EXISTS ( SELECT 'X' FROM PS_ACTN_REASON_TBL RSN WHERE RSN.ACTION = JOB.ACTION AND RSN.ACTION_REASON = JOB.ACTION_REASON AND RSN.EFF_STATUS = 'A' AND RSN.EFFDT = ( SELECT MAX(RSN1.EFFDT) FROM PS_ACTN_REASON_TBL RSN1 WHERE RSN1.ACTION = RSN.ACTION AND RSN1.ACTION_REASON = RSN.ACTION_REASON AND RSN1.EFFDT <= JOB.EFFDT))

# 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 EFFDT Date(10) DATE Effective Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Prompt Table: ACTION_TBL

6 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

Prompt Table: ACTN_REASON_TBL

7 HR_ACTION_TYPE Character(1) VARCHAR2(1) NOT NULL Type of Action or Action Reason
I=Involuntary
V=Voluntary