PC_LATETIME_VW2

(SQL View)
 Index Back

Project Late Timesheets

In Progree and Missing Timesheets based on My Project Team Members.

SELECT A.OPRID , A.BUSINESS_UNIT , A.PROJECT_ID , A.TEAM_MEMBER , MGR.PROJECT_MANAGER , E.FIRST_NAME %Concat ' ' %Concat E.LAST_NAME , EMAIL.EMAIL_ADDR , C.DESCR , A.PERSON_NAME , A.EMAILID , A.START_DT , A.END_DT , A.PROJ_ROLE , B.TIME_SHEET_ID , B.VERSION_NUM , B.PERIOD_END_DT , B.TIME_SHEET_STATUS , B.TIME_QUANTITY , B.LASTUPDDTTM FROM PS_PC_PROJ_TEAM_VW A LEFT OUTER JOIN PS_PROJECT_MGR MGR ON A.BUSINESS_UNIT = MGR.BUSINESS_UNIT AND A.PROJECT_ID = MGR.PROJECT_ID LEFT OUTER JOIN PS_PERSONAL_DATA E ON MGR.PROJECT_MANAGER = E.EMPLID LEFT OUTER JOIN PS_EMAIL_ADDRESSES EMAIL ON E.EMPLID = EMAIL.EMPLID AND EMAIL.E_ADDR_TYPE = 'BUSN' , PS_PC_LATETIME_VW B , PS_PROJECT C , PS_PC_EX_TIME_PER D WHERE A.OPRID = D.OPRID AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.TEAM_MEMBER = B.EMPLID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PROJECT_ID = C.PROJECT_ID AND B.BUSINESS_UNIT_GL = D.BUSINESS_UNIT_GL AND B.PERIOD_END_DT = D.PERIOD_END_DT AND (MGR.EFFDT IS NULL OR MGR.EFFDT=( SELECT MAX(EFFDT) FROM PS_PROJECT_MGR A_ED WHERE A_ED.BUSINESS_UNIT = MGR.BUSINESS_UNIT AND A_ED.PROJECT_ID = MGR.PROJECT_ID AND A_ED.EFFDT <= %CurrentDateIn)) UNION SELECT A.OPRID , A.BUSINESS_UNIT , A.PROJECT_ID , A.TEAM_MEMBER , MGR.PROJECT_MANAGER , E.FIRST_NAME %Concat ' ' %Concat E.LAST_NAME , EMAIL.EMAIL_ADDR , C.DESCR , A.PERSON_NAME , A.EMAILID , A.START_DT , A.END_DT , A.PROJ_ROLE , ' ' , 0 , B.PERIOD_END_DT , ' ' , 0 , %DateTimeNull FROM PS_PC_PROJ_TEAM_VW A LEFT OUTER JOIN PS_PROJECT_MGR MGR ON A.BUSINESS_UNIT = MGR.BUSINESS_UNIT AND A.PROJECT_ID = MGR.PROJECT_ID LEFT OUTER JOIN PS_PERSONAL_DATA E ON MGR.PROJECT_MANAGER = E.EMPLID LEFT OUTER JOIN PS_EMAIL_ADDRESSES EMAIL ON E.EMPLID = EMAIL.EMPLID AND EMAIL.E_ADDR_TYPE = 'BUSN' , PS_PC_EX_TIME_PER B , PS_PROJECT C , PS_EX_EE_ORG_DTL D WHERE A.OPRID = B.OPRID AND C.END_DT >= %CurrentDateIn AND B.PERIOD_END_DT BETWEEN A.START_DT AND A.END_DT AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PROJECT_ID = C.PROJECT_ID AND A.TEAM_MEMBER = D.EMPLID AND B.BUSINESS_UNIT_GL = D.BUSINESS_UNIT_GL AND D.DFLT_EE_PROF_FLG = 'Y' AND (MGR.EFFDT IS NULL OR MGR.EFFDT=( SELECT MAX(EFFDT) FROM PS_PROJECT_MGR A_ED WHERE A_ED.BUSINESS_UNIT = MGR.BUSINESS_UNIT AND A_ED.PROJECT_ID = MGR.PROJECT_ID AND A_ED.EFFDT <= %CurrentDateIn)) AND EXISTS ( SELECT 'X' FROM PS_EX_TIME_HDR HDR , PS_EX_TIME_DTL DTL WHERE HDR.TIME_SHEET_ID = DTL.TIME_SHEET_ID AND HDR.VERSION_NUM = DTL.VERSION_NUM AND B.BUSINESS_UNIT_GL = HDR.BUSINESS_UNIT_GL AND B.PERIOD_END_DT = HDR.PERIOD_END_DT AND DTL.BUSINESS_UNIT_PC = A.BUSINESS_UNIT AND DTL.PROJECT_ID = A.PROJECT_ID AND NOT EXISTS ( SELECT 'X' FROM PS_EX_TIME_HDR E WHERE A.TEAM_MEMBER = E.EMPLID AND B.PERIOD_END_DT = E.PERIOD_END_DT))

  • Related Language Record: PC_LATETIME_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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Prompt Table: SP_BU_PC_NONVW

    3 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

    Prompt Table: PROJECT_ALL_VW

    4 TEAM_MEMBER Character(30) VARCHAR2(30) NOT NULL Team Member

    Prompt Table: PGM_TEAM_PRM_VW

    5 PROJECT_MANAGER Character(11) VARCHAR2(11) NOT NULL Project Manager
    6 PROJ_MGR_NAME Character(50) VARCHAR2(50) NOT NULL Project Manager Name
    7 EMAILID2 Character(70) VARCHAR2(70) NOT NULL Email ID
    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 PERSON_NAME Character(100) VARCHAR2(100) NOT NULL Name
    10 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
    11 START_DT Date(10) DATE Start Date
    12 END_DT Date(10) DATE End Date
    13 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role
    14 TIME_SHEET_ID Character(10) VARCHAR2(10) NOT NULL Time Sheet Unique Identifier (System Generated)
    15 VERSION_NUM Number(3,0) SMALLINT NOT NULL Version Number
    16 PERIOD_END_DT Date(10) DATE Represents the date termination point for a given time set utilized in cash worksheet processing.
    17 TIME_SHEET_STATUS Character(3) VARCHAR2(3) NOT NULL Time Sheet Status
    ADJ=Adjusted
    APR=Approved
    DEN=Denied
    DNA=Denied by Approver
    DNU=Denied by Project Manager
    HDA=Hold by Approver
    HDU=Hold by Project Manager
    HLD=Hold
    MFS=Marked for Submit
    PAR=Approvals in Process
    PND=Pending
    PRO=In Process
    SFA=Submission in Process
    SUB=Submitted for Approval
    XML=Submitted, Pending Validation
    18 TIME_QUANTITY Signed Number(16,2) DECIMAL(14,2) NOT NULL Time Quantity
    19 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.