EX_ED_PRJ_TIME

(SQL View)
 Index Back

Time Report Detail

This record contains all of the detail project information for a Time Card for regular time

SELECT DTL.TIME_SHEET_ID , DTL.VERSION_NUM , DTL.LINE_NBR , HDR.BUSINESS_UNIT_GL , HDR.PERIOD_END_DT , HDR.EMPLID , PER.NAME , HDR.TIME_SHEET_STATUS , XLAT.XLATLONGNAME , HDR.BILLING_ACTION , HDR.CREATION_DT , HDR.APPROVAL_DT , HDR.SUBMISSION_DATE , HDR.RESUBMISSION_DT , CASE WHEN HDR.SUBMISSION_DATE IS NOT NULL THEN %DateDiff(HDR.SUBMISSION_DATE, %CurrentDateIn) ELSE 0 END , DTL.TIME_QUANTITY , %subrec(CFS16_AN_SBR, DTL) , CASE WHEN %Sql(DEPTID, DTL.) <> ' ' THEN DPT.DESCR ELSE ' ' END , CASE WHEN %Sql(OPERATING_UNIT, DTL.) <> ' ' THEN OU.DESCR ELSE ' ' END , CASE WHEN %Sql(PRODUCT, DTL.) <> ' ' THEN PRD.DESCR ELSE ' ' END , CASE WHEN %Sql(FUND_CODE, DTL.) <> ' ' THEN FC.DESCR ELSE ' ' END , CASE WHEN %Sql(CLASS_FLD, DTL.) <> ' ' THEN CLASS.DESCR ELSE ' ' END , CASE WHEN %Sql(PROGRAM_CODE, DTL.) <> ' ' THEN PC.DESCR ELSE ' ' END , CASE WHEN %Sql(BUDGET_REF, DTL.) <> ' ' THEN BR.DESCR ELSE ' ' END , D.BUSINESS_UNIT_PC , D.PROJECT_ID , PRJ.DESCR , D.ACTIVITY_ID , CASE WHEN D.RESOURCE_TYPE <> ' ' THEN D.RESOURCE_TYPE ELSE 'N/A' END , CASE WHEN D.RESOURCE_CATEGORY <> ' ' THEN D.RESOURCE_CATEGORY ELSE 'N/A' END , CASE WHEN D.RESOURCE_SUB_CAT <> ' ' THEN D.RESOURCE_SUB_CAT ELSE 'N/A' END , %Sql(DEPTID, D.) , D.DESCR60 , HDR.COMMENTS , D.COMMENTS , D.BI_DISTRIB_STATUS , HDR.POST_STATUS_EX , HDR.LASTUPDDTTM FROM PS_EX_PRJTIME_VW DTL LEFT OUTER JOIN PS_DEPT_ALL_VW DPT ON DTL.SETID_DEPT = DPT.SETID AND %Sql(DEPTID, DTL.) = %Sql(DEPTID, DPT.) LEFT OUTER JOIN PS_OPERUNIT_ALL_VW OU ON DTL.SETID_OPER_UNIT = OU.SETID AND %Sql(OPERATING_UNIT, DTL.) = %Sql(OPERATING_UNIT, OU.) LEFT OUTER JOIN PS_PROD_ALL_VW PRD ON DTL.SETID1 = PRD.SETID AND %Sql(PRODUCT, DTL.) = %Sql(PRODUCT, PRD.) LEFT OUTER JOIN PS_FUND_ALL_VW FC ON DTL.SETID2 = FC.SETID AND %Sql(FUND_CODE, DTL.) = %Sql(FUND_CODE, FC.) LEFT OUTER JOIN PS_CLASS_CF_ALL_VW CLASS ON DTL.SETID3 = CLASS.SETID AND %Sql(CLASS_FLD, DTL.) = %Sql(CLASS_FLD, CLASS.) LEFT OUTER JOIN PS_PROGRAM_ALL_VW PC ON DTL.SETID4 = PC.SETID AND %Sql(PROGRAM_CODE, DTL.) = %Sql(PROGRAM_CODE, PC.) LEFT OUTER JOIN PS_BUDREF_ALL_VW BR ON DTL.SETID5 = BR.SETID AND %Sql(BUDGET_REF, DTL.) = %Sql(BUDGET_REF, BR.) , PS_EX_TIME_HDR HDR , PS_EX_TIME_DTL D , XLATTABLE_VW XLAT , PS_PERSONAL_DATA PER , PS_PROJECT PRJ , PS_EID_ETL_LASTRUN LR WHERE HDR.TIME_SHEET_ID = DTL.TIME_SHEET_ID AND HDR.VERSION_NUM = DTL.VERSION_NUM AND HDR.VERSION_NUM = ( SELECT MAX(X.VERSION_NUM) FROM PS_EX_TIME_HDR X WHERE HDR.TIME_SHEET_ID = X.TIME_SHEET_ID) AND HDR.TIME_SHEET_STATUS NOT IN ('DEN', 'DNA', 'DNU', 'MFS', 'PND') AND DTL.TIME_SHEET_ID = D.TIME_SHEET_ID AND DTL.VERSION_NUM = D.VERSION_NUM AND DTL.LINE_NBR = D.LINE_NBR AND XLAT.FIELDNAME = 'TIME_SHEET_STATUS' AND XLAT.FIELDVALUE = HDR.TIME_SHEET_STATUS AND PER.EMPLID = HDR.EMPLID AND PRJ.BUSINESS_UNIT = D.BUSINESS_UNIT_PC AND PRJ.PROJECT_ID = D.PROJECT_ID AND LR.EID_DS_ID = 'Expenses' AND HDR.LASTUPDDTTM >= LR.LAST_RUN_DTM AND %DateDiff(HDR.CREATION_DT, %CurrentDateIn) < 1826

  • Parent record: EX_TIME_HDR

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 TIME_SHEET_ID Character(10) VARCHAR2(10) NOT NULL Time Sheet Unique Identifier (System Generated)
    2 VERSION_NUM Number(3,0) SMALLINT NOT NULL Version Number

    Default Value: 1

    3 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]

    Default Value: 1

    4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    5 PERIOD_END_DT Date(10) DATE Represents the date termination point for a given time set utilized in cash worksheet processing.
    6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    7 NAME Character(50) VARCHAR2(50) NOT NULL Name
    8 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
    9 STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL Status Description
    10 BILLING_ACTION Character(1) VARCHAR2(1) NOT NULL Billing Action
    B=Billable
    I=Internal
    P=Personal
    U=Nonbillable

    Default Value: EX_TIME_HDR.BILLING_ACTION

    11 CREATION_DT Date(10) DATE Creation Date
    12 APPROVAL_DT Date(10) DATE Date of Approval
    13 SUBMISSION_DATE Date(10) DATE Submission Date
    14 RESUBMISSION_DT Date(10) DATE Resubmission Date
    15 DAYS_SINCE_SUBMIT Number(6,0) INTEGER NOT NULL Number of calendar days since timesheet was submitted.
    16 TIME_QUANTITY Signed Number(16,2) DECIMAL(14,2) NOT NULL Time Quantity
    17 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

    Prompt Table: %EDIT_OPER_UNIT

    18 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

    Prompt Table: %EDIT_PRODUCT

    19 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

    Prompt Table: %EDIT_FUND_CODE

    20 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

    Prompt Table: %EDIT_CLASS_FLD

    21 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

    Prompt Table: %EDIT_PROG_CODE

    22 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

    Prompt Table: %EDIT_BUD_REF

    23 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

    Prompt Table: AFFILIATE_VW

    24 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

    Prompt Table: %EDIT_INTRA01

    25 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

    Prompt Table: %EDIT_INTRA02

    26 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

    Prompt Table: %EDIT_CHARTFLD1

    27 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

    Prompt Table: %EDIT_CHARTFLD2

    28 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

    Prompt Table: %EDIT_CHARTFLD3

    29 DEPTID_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    30 OPER_UNIT_DESCR Character(30) VARCHAR2(30) NOT NULL Operating Unit Description
    31 PRODUCT_DESCR Character(30) VARCHAR2(30) NOT NULL Product Description
    32 CF_DESCR3 Character(30) VARCHAR2(30) NOT NULL
    33 CF_DESCR4 Character(30) VARCHAR2(30) NOT NULL
    34 CF_DESCR5 Character(30) VARCHAR2(30) NOT NULL
    35 CF_DESCR6 Character(30) VARCHAR2(30) NOT NULL
    36 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
    37 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    38 PROJ_DESCR Character(30) VARCHAR2(30) NOT NULL Project ID Description
    39 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
    40 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type
    41 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category
    42 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory
    43 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    44 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
    45 COMMENTS Long Character CLOB Comment
    46 COMMENTS_LONG Long Character CLOB Comment Text
    47 BI_DISTRIB_STATUS Character(1) VARCHAR2(1) NOT NULL Billing Distribution Status
    D=Distributed
    I=Ignore
    N=Not Distributed
    P=Priced
    U=Unbillable/Non-billable
    W=Billing Worksheet

    Default Value: N

    48 POST_STATUS_EX Character(1) VARCHAR2(1) NOT NULL Expenses Post Status
    C=Closed
    M=Marked for Unpost
    N=Not Applied
    O=Unpost In Process
    P=Posted
    S=Close In Process
    U=Unposted

    Default Value: N

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

    Default Value: %datetime