AA_SUMM_JPN_VW

(SQL View)
 Index Back

Additional Appt History View

Lists all Additional Appointment History for one Main Appointment.

SELECT A.EMPLID , A.EMPL_RCD , B.EMPL_RCD , B.DEPTID , A.PER_ORG , A.POI_TYPE , B.EMPL_STATUS , B.BUSINESS_UNIT , B.COMPANY , B.LOCATION , B.SUPV_LVL_ID , C.INTCP_COMPANY2 , C.INTCP_DEPTID2 , C.INTCP_SUPV_LVL_ID2 , C.START_DATE_JPN , C.EXP_END_DATE_JPN , C.END_DATE_JPN , B.EFFDT FROM PS_PER_ORG_ASGN A , PS_JOB B , PS_JOB_JR C WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.MAIN_APPT_NUM_JPN AND B.EMPLID = C.EMPLID AND B.EMPL_RCD = C.EMPL_RCD AND B.EFFDT = C.EFFDT AND B.EFFSEQ = C.EFFSEQ AND B.APPT_TYPE = '1' AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = B.EMPLID AND EMPL_RCD = B.EMPL_RCD) AND B.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = B.EMPLID AND EMPL_RCD = B.EMPL_RCD AND EFFDT <= B.EFFDT)

  • Parent Record Of AA_ONE_JPN_VW

    • # 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 Rcd Nbr
    3 EMPL_RCD_JPN Signed Number(4,0) DECIMAL(3) NOT NULL Tracks the empl_rcd of an additional appointment. This is used in records that also lists the main appointment empl_rcd.

    Default Value: -1

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

    Prompt Table: DEPT_TBL

    5 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
    CWR=Contingent Worker
    EMP=Employee
    POI=Person of Interest
    6 POI_TYPE Character(5) VARCHAR2(5) NOT NULL Defines the Transactional usage of POIs in HRMS.

    Prompt Table: POI_TYPE_TBL

    7 EMPL_STATUS_JPN Character(1) VARCHAR2(1) NOT NULL Employee Status
    A=Active
    D=Deceased
    I=Inactive
    L=Leave of Absence
    P=Leave With Pay
    Q=Retired With Pay
    R=Retired
    S=Suspended
    T=Terminated
    U=Terminated With Pay
    V=Terminated Pension Pay Out
    W=Short Work Break
    X=Retired-Pension Administration
    8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Prompt Table: BUSUNIT_HR_VW

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

    Prompt Table: COMPANY_TBL

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

    Prompt Table: LOCATION_TBL

    11 SUPV_LVL_ID Character(8) VARCHAR2(8) NOT NULL Supervisor Level ID for use in capability grade system
    12 INTCP_COMPANY2 Character(3) VARCHAR2(3) NOT NULL External Company

    Prompt Table: INTCPX_CPY_JPN

    13 INTCP_DEPTID2 Character(10) VARCHAR2(10) NOT NULL External DeptID

    Prompt Table: INTCPX_DPT_JPN

    14 INTCP_SUPV_LVL_ID2 Character(8) VARCHAR2(8) NOT NULL External Supv Lvl

    Prompt Table: INTCPX_SPV_JPN

    15 START_DATE_JPN Date(10) DATE Tracks the beginning date of an additional appointment assignment.
    16 EXP_END_DATE_JPN Date(10) DATE Tracks the date in the future when the additional appointment assignment is expected to be completed.
    17 END_DATE_JPN Date(10) DATE The actual date that an additional appointment assignment was completed.
    18 EFFDT Date(10) DATE Effective Date