PC_WC_INACT_EMP

(SQL View)
 Index Back

Inactive Project Resources

This view has been created to fetch Inactive Project Resources present in Active Projects for WorkCenter exception.

SELECT P.BUSINESS_UNIT , P.PROJECT_ID , P.DESCR , P.START_DT , P.END_DT , PT.TEAM_MEMBER , A.NAME , PT.PLAN_PROJROLE , PT.PLAN_STARTDT , PT.PLAN_ENDDT , MP.OPRID , B.EFFDT FROM PS_PC_OPRID_PRJACT MP , PS_PERSONAL_DATA A , PS_JOB B , PS_PROJECT P , PS_PROJECT_TEAM PT WHERE MP.BUSINESS_UNIT = P.BUSINESS_UNIT AND MP.PROJECT_ID = P.PROJECT_ID AND P.BUSINESS_UNIT = PT.BUSINESS_UNIT AND P.PROJECT_ID = PT.PROJECT_ID AND PT.PLAN_ENDDT >= %CurrentDateIn AND P.EFF_STATUS = 'A' AND PT.TEAM_MEMBER = A.EMPLID AND A.EMPLID = B.EMPLID AND B.EMPL_STATUS NOT IN ('A', 'P', 'L', 'W') AND ((B.EFFDT >= PT.PLAN_STARTDT AND B.EFFDT <= PT.PLAN_ENDDT ) OR PT.PLAN_STARTDT > B.EFFDT ) AND NOT EXISTS ( SELECT 'X' FROM PS_JOB B WHERE B.EMPL_STATUS IN ('A', 'P', 'L', 'W') AND A.EMPLID = B.EMPLID AND B.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B.EMPLID = B2.EMPLID AND B.EMPL_RCD = B2.EMPL_RCD) AND B.EFFSEQ = ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B.EMPLID = B3.EMPLID AND B.EMPL_RCD = B3.EMPL_RCD AND B.EFFDT = B3.EFFDT) AND B.EMPL_RCD = ( SELECT MIN(B0.EMPL_RCD) FROM PS_JOB B0 WHERE B.EMPLID = B0.EMPLID AND B0.EMPL_STATUS IN ('A', 'P', 'L', 'W') AND B0.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B0.EMPLID AND B1.EMPL_RCD = B0.EMPL_RCD) AND B0.EFFSEQ = ( SELECT MAX(B4.EFFSEQ) FROM PS_JOB B4 WHERE B4.EMPLID = B0.EMPLID AND B4.EMPL_RCD = B0.EMPL_RCD AND B4.EFFDT = B0.EFFDT))) AND NOT EXISTS( SELECT 'X' FROM PS_PROJECT_MGR WHERE PS_PROJECT_MGR.BUSINESS_UNIT = PT.BUSINESS_UNIT AND PS_PROJECT_MGR.PROJECT_ID = PT.PROJECT_ID AND PS_PROJECT_MGR.PROJECT_MANAGER = PT.TEAM_MEMBER AND ((B.EFFDT >= PS_PROJECT_MGR.START_DT AND B.EFFDT <=PS_PROJECT_MGR.END_DT) OR PS_PROJECT_MGR.START_DT > B.EFFDT))

  • Parent record: PROJECT

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    4 START_DT Date(10) DATE Start Date
    5 END_DT Date(10) DATE End Date
    6 TEAM_MEMBER Character(30) VARCHAR2(30) NOT NULL Team Member
    7 NAME Character(50) VARCHAR2(50) NOT NULL Name
    8 PLAN_PROJROLE Character(15) VARCHAR2(15) NOT NULL Requirement Project Role

    Prompt Table: PROJ_ROLE

    9 PLAN_STARTDT Date(10) DATE Planned Start Date

    Default Value: PROJECT.START_DT

    10 PLAN_ENDDT Date(10) DATE Planned End Date

    Default Value: PROJECT.END_DT

    11 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    12 EFFDT Date(10) DATE Effective Date

    Default Value: %date