POSITION_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.

SELECT DISTINCT SEC.OPRID , A.POSITION_NBR ,A.EFFDT ,a.effseq ,A.DESCR ,A.BUSINESS_UNIT ,A.COMPANY ,A.DEPTID ,A.JOBCODE ,A.POSN_STATUS ,A.REPORTS_TO , %NumToChar( (SELECT COUNT(*) FROM PS_POSN_INCUMB_WS WS WHERE WS.POSITION_NBR=A.POSITION_NBR)) %Concat '/' %Concat %NumToChar(A.MAX_HEAD_COUNT) , A.MAX_HEAD_COUNT , A.EFF_STATUS 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 D.EFFDT<=%CurrentDateIn ) 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 ) OR (A.EFFDT>=%CurrentDateIn 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 )) )

  • Related Language Record: POSITION_SR_LVW

    • # 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 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Prompt Table: BUS_UNIT_TBL_HR

    7 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPT_TBL

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

    Prompt Table: JOBCODE_TBL

    10 POSN_STATUS Character(1) VARCHAR2(1) NOT NULL Position Status
    A=Approved
    P=Proposed
    R=Frozen
    11 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
    12 POS_HEAD_COUNT Character(15) VARCHAR2(15) NOT NULL Current Head Count for Fluid Position Management
    13 MAX_HEAD_COUNT Number(4,0) SMALLINT NOT NULL Max Head Count
    14 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive