PC_WC_TEAM_VW

(SQL View)
 Index Back

Missing Managers in Projects

This view has been created to fetch Missing Managers in Projects for WorkCenter exception.

SELECT P.BUSINESS_UNIT , P.PROJECT_ID , P.DESCR , P.EFF_STATUS , P.START_DT , P.END_DT , MP.OPRID FROM PS_PC_OPRID_PRJACT MP , PS_PROJECT P WHERE MP.BUSINESS_UNIT = P.BUSINESS_UNIT AND MP.PROJECT_ID = P.PROJECT_ID AND P.EFF_STATUS = 'A' AND P.START_DT <= %CurrentDateIn AND NOT EXISTS ( SELECT 'X' FROM PS_JOB B , PS_PROJECT_MGR PM WHERE PM.business_unit = P.business_unit AND PM.project_id = P.PROJECT_ID AND PM.project_manager = B.EMPLID AND PM.start_dt <= %CurrentDateIn AND PM.END_DT >= %CurrentDateIn AND B.EMPL_STATUS IN ('A', 'P', 'L', 'W') AND B.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B.EMPLID = B2.EMPLID AND B.EMPL_RCD = B2.EMPL_RCD AND B2.EFFDT <= %CurrentDateIn ) 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 B1.EFFDT <= %CurrentDateIn ) 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 ) ))

  • 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 PROCESSING_STATUS Character(1) VARCHAR2(1) NOT NULL Processing status
    A=Active
    I=Inactive
    P=Pending
    5 START_DT Date(10) DATE Start Date
    6 END_DT Date(10) DATE End Date
    7 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).