POSN_PRI_SRC_VW (SQL View)	Index Back
Search Vw-Position Mgmt POSITION_SRCH_E is a search view which uses departmental security to grant access to the positions based on the departments to which they are assigned. Users will only see positions located in departments for which they have security clearance. It is used as the search record for the Position Data panels in Position Management in all access modes except for Add mode. If you wish to enforce departmental security when a user adds a position which is already in the database, this record should be used as the search record for Add mode as well.

POSN_PRI_SRC_VW

(SQL View)

Search Vw-Position Mgmt

POSITION_SRCH_E is a search view which uses departmental security to grant access to the positions based on the departments to which they are assigned. Users will only see positions located in departments for which they have security clearance. It is used as the search record for the Position Data panels in Position Management in all access modes except for Add mode. If you wish to enforce departmental security when a user adds a position which is already in the database, this record should be used as the search record for Add mode as well.

SELECT SEC.OPRID , A.POSITION_NBR ,A.EFFDT ,A.effseq ,UPPER(A.Descr) ,A.DESCR ,' ' ,' ' ,' ' ,A.BUSINESS_UNIT ,( SELECT DESCR FROM PS_BUS_UNIT_TBL_HR BUS WHERE BUS.BUSINESS_UNIT=A.BUSINESS_UNIT AND BUS.DEFAULT_SETID=SEC.SETID ) ,A.DEPTID ,A.JOBCODE ,( SELECT DESCR FROM PS_JOBCODE_TBL JO WHERE JO.JOBCODE=A.JOBCODE AND JO.SETID=SEC.SETID AND JO.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL C WHERE JO.JOBCODE=C.JOBCODE AND C.SETID=JO.SETID)) ,A.COMPANY ,A.LOCATION FROM PS_POSITION_DATA_E A ,PS_DEPT_SEC_SRCH SEC WHERE SEC.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC SC WHERE SC.SETCNTRLVALUE = A.BUSINESS_UNIT AND SC.RECNAME = 'DEPT_TBL') AND A.DEPTID = SEC.DEPTID AND A.EFFDT= ( SELECT MAX(D.EFFDT) FROM PS_POSITION_DATA_E D WHERE A.POSITION_NBR = D.POSITION_NBR ) AND A.EFFSEQ =( SELECT MAX(E.EFFSEQ) FROM PS_POSITION_DATA_E E WHERE A.POSITION_NBR = E.POSITION_NBR AND E.EFFDT=A.EFFDT ) AND NOT EXISTS ( SELECT 'Y' FROM PS_POSN_PRI_INCUMB B WHERE A.POSITION_NBR=B.POSITION_NBR AND B.POSN_PRI_START_DT<=%CurrentDateIn ) UNION SELECT SEC.OPRID , B.POSITION_NBR ,A.EFFDT ,A.effseq ,UPPER(A.Descr) ,A.DESCR ,B.EMPLID ,B.NAME_DISPLAY , B.SYSTEM_ASSIGNED ,A.BUSINESS_UNIT ,( SELECT DESCR FROM PS_BUS_UNIT_TBL_HR BUS WHERE BUS.BUSINESS_UNIT=A.BUSINESS_UNIT AND BUS.DEFAULT_SETID=SEC.SETID ) ,A.DEPTID ,A.JOBCODE ,( SELECT DESCR FROM PS_JOBCODE_TBL JO WHERE JO.JOBCODE=A.JOBCODE AND JO.SETID=SEC.SETID AND JO.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL C WHERE JO.JOBCODE=C.JOBCODE AND C.SETID=JO.SETID)) ,A.COMPANY ,A.LOCATION FROM PS_POSITION_DATA_E A ,PS_POSN_PRI_INCUMB B,PS_DEPT_SEC_SRCH SEC WHERE A.POSITION_NBR=B.POSITION_NBR AND SEC.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC SC WHERE SC.SETCNTRLVALUE = A.BUSINESS_UNIT AND SC.RECNAME = 'DEPT_TBL') AND B.POSN_PRI_START_DT=( SELECT MAX(B1.POSN_PRI_START_DT) FROM PS_POSN_PRI_INCUMB B1 WHERE B1.POSITION_NBR = B.POSITION_NBR AND B1.POSN_PRI_START_DT<=%CurrentDateIn ) AND A.EFFDT= ( SELECT MAX(D.EFFDT) FROM PS_POSITION_DATA_E D WHERE A.POSITION_NBR = D.POSITION_NBR ) AND A.EFFSEQ =( SELECT MAX(E.EFFSEQ) FROM PS_POSITION_DATA_E E WHERE A.POSITION_NBR = E.POSITION_NBR AND E.EFFDT=A.EFFDT )

# 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
Prompt Table: POSITION_SRCH_P

3 EFFDT Date(10) DATE Effective Date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

5 POSN_PRI_DESCR Character(50) VARCHAR2(50) NOT NULL Position Primmary Incumbent Description

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

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

8 NAME Character(50) VARCHAR2(50) NOT NULL Name

9 SYSTEM_ASSIGNED Character(3) VARCHAR2(3) NOT NULL System Assigned
N=No
N/A=Not Applicable
Y=Yes

10 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: BUS_UNIT_TBL_HR

11 POSN_BU_DESCR Character(100) VARCHAR2(100) NOT NULL Position Description

12 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: DEPT_TBL

13 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
Prompt Table: JOBCODE_TBL

14 POSN_JOBC_DESCR Character(100) VARCHAR2(100) NOT NULL Position Length 100 Description

15 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

16 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

#	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 Prompt Table: POSITION_SRCH_P
3	EFFDT	Date(10)	DATE	Effective Date
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	POSN_PRI_DESCR	Character(50)	VARCHAR2(50) NOT NULL	Position Primmary Incumbent Description
6	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
7	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
8	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
9	SYSTEM_ASSIGNED	Character(3)	VARCHAR2(3) NOT NULL	System Assigned N=No N/A=Not Applicable Y=Yes
10	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: BUS_UNIT_TBL_HR
11	POSN_BU_DESCR	Character(100)	VARCHAR2(100) NOT NULL	Position Description
12	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
13	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code Prompt Table: JOBCODE_TBL
14	POSN_JOBC_DESCR	Character(100)	VARCHAR2(100) NOT NULL	Position Length 100 Description
15	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
16	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code