HCSC_SPVRPT_VW

(SQL View)
 Index Back

Supervisor Tree Inquiry View

This view is used to prompt for a new node when building a supervisor-based tree, and as such includes certain identifying data from the Department and Job Code tables. To accommodate the tree is node restrictions, this view has a composite key of EMPLID + EMPL_RCD

SELECT J.EMPL_ID_RCD ,J.effdt ,J.EMPLID ,J.EMPL_RCD ,%Substring(N.NAME_DISPLAY, 1, 30) FROM PS_WCS_ECM_JOB J , PS_PERSON_NAME N WHERE J.PER_ORG = 'EMP' AND J.HR_STATUS = 'A' AND J.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_JOB X WHERE X.EMPLID = J.EMPLID AND X.EMPL_RCD = J.EMPL_RCD AND X.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(X.EFFSEQ) FROM PS_JOB X WHERE X.EMPLID = J.EMPLID AND X.EMPL_RCD = J.EMPL_RCD AND X.EFFDT = J.EFFDT) AND N.EMPLID=J.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPL_ID_RCD Character(20) VARCHAR2(20) NOT NULL Job concatenated key.
2 EFFDT Date(10) DATE Effective Date

Default Value: %date

3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSON

4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

Prompt Table: PER_ORG_ASGN

5 DESCR Character(30) VARCHAR2(30) NOT NULL Description