HR_GSS_POSN_SEC

(SQL View)
 Index Back

Positions by Oprid


SELECT DISTINCT SEC.OPRID , POSDATA.POSITION_NBR , POSDATA.EFFDT , POSDATA.DESCR , CASE WHEN (POSDATA.REPORTS_TO <> ' ') THEN B.NAME_DISPLAY ELSE ' ' END , POSDATA.BUSINESS_UNIT , POSDATA.DEPTID , POSDATA.JOBCODE , POSDATA.REPORTS_TO FROM PS_POSITION_DATA POSDATA , PS_DEPT_SEC_SRCH SEC , PS_PERSON_NAME B , PS_JOB A WHERE %EffdtCheck(POSITION_DATA POS_ED, POSDATA, %CurrentDateIn) AND POSDATA.EFF_STATUS = 'A' AND SEC.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC SC WHERE SC.SETCNTRLVALUE = POSDATA.BUSINESS_UNIT AND SC.RECNAME = 'DEPT_TBL') AND POSDATA.DEPTID = SEC.DEPTID AND A.EMPLID = ( SELECT A1.EMPLID FROM PS_JOB A1 WHERE A1.POSITION_NBR = POSDATA.POSITION_NBR AND A1.EMPL_STATUS IN ('A', 'P', 'L', 'S') AND A1.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = A1.EMPLID AND C.EMPL_RCD = A1.EMPL_RCD AND C.EFFDT <= %CurrentDateIn) AND A1.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = A1.EMPLID AND D.EMPL_RCD = A1.EMPL_RCD AND D.EFFDT = A1.EFFDT)) AND B.EMPLID = A.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
5 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country

Prompt Table: HR_GSS_NAME_VW

6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_HR

7 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

8 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

9 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number

Prompt Table: HR_GSS_POSN2_VW