TL_PT_PG_VW

(SQL View)
 Index Back

Payable time view with periods

This view picks the payable time information with time periods like year, quarter, month, pay period etc.

SELECT P.EMPLID , P.EMPL_RCD , P.DUR , P.SEQ_NBR , %Sql(TL_SQL_YEAR, P.DUR) , %Sql(TL_SQL_YEAR, P.DUR) %Concat '-' %Concat %Sql(TL_SQL_QUARTER, P.DUR) , %Sql(TL_SQL_YEAR, P.DUR) %Concat '-' %Concat %Sql(TL_SQL_MONTH, P.DUR) , %Sql(TL_SQL_YEAR, P.DUR) %Concat '-' %Concat %Sql(TL_SQL_MONTH, P.DUR) %Concat '-W' %Concat %Sql(TL_SQL_WEEK, P.DUR) , CASE WHEN J.PAYGROUP <> ' ' THEN ( SELECT C.PAY_END_DT FROM PS_PAY_CALENDAR C WHERE C.COMPANY = J.COMPANY AND C.PAYGROUP = J.PAYGROUP AND C.PAY_END_DT >= P.DUR AND C.PAY_BEGIN_DT <= P.DUR) WHEN J.GP_PAYGROUP <> ' ' THEN ( SELECT PRD.PRD_END_DT FROM PS_GP_CALENDAR CAL , PS_GP_CAL_PRD PRD , PS_GP_RUN_TYPE RT WHERE CAL.GP_PAYGROUP = J.GP_PAYGROUP AND CAL.RUN_TYPE = RT.RUN_TYPE AND RT.CALC_TYPE = 'P' AND PRD.CAL_PRD_ID = CAL.CAL_PRD_ID AND PRD.PRD_END_DT >= P.DUR AND PRD.PRD_BGN_DT <= P.DUR) END, P.TRC, P.TL_QUANTITY, P.EST_GROSS, P.LBR_DIST_AMT, P.DILUTED_GROSS, P.PAYABLE_STATUS, P.PROJECT_ID, T.TRC_SUM_CATEGORY, %Coalesce((SELECT TC.DESCR FROM PS_TL_TRC_CAT_TBL TC WHERE TC.TRC_SUM_CATEGORY = T.TRC_SUM_CATEGORY), %Coalesce((SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 13504 AND MESSAGE_NBR = 11200),'Not Categorized')), P.FROZEN_DATE, P.ACCT_CD FROM PS_TL_PAYABLE_TIME P, PS_TL_JOB_EMPL_DAT J, PS_TL_TRC_TBL T WHERE P.EMPLID = J.EMPLID AND P.EMPL_RCD = J.EMPL_RCD AND P.TRC = T.TRC AND T.EFFDT = ( SELECT MAX(T1.EFFDT) FROM PS_TL_TRC_TBL T1 WHERE T1.TRC = T.TRC AND T1.EFF_STATUS = 'A')

  • Related Language Record: TL_PT_PG_LANG

    • # 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 Record
    3 DUR Date(10) DATE Date Under Report associates time being reported, scheduled or paid, to a specific date.
    4 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
    5 YEAR Character(4) VARCHAR2(4) NOT NULL Character field to represent a year
    6 QUARTER Character(8) VARCHAR2(8) NOT NULL Quarter
    7 YEAR_MON Character(8) VARCHAR2(8) NOT NULL Year Month
    8 YEAR_MON_WEEK Character(12) VARCHAR2(12) NOT NULL Year Month Week
    9 PAY_END_DT Date(10) DATE Pay Period End Date
    10 TRC Character(5) VARCHAR2(5) NOT NULL Time Reporting Code used to report time in Time and Labor
    11 TL_QUANTITY Signed Number(20,6) DECIMAL(18,6) NOT NULL This field represents the measure of Work in hrs,amount etc.,
    12 EST_GROSS Signed Number(20,6) DECIMAL(18,6) NOT NULL Estimated Gross
    13 LBR_DIST_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Labor Distribution Amount
    14 DILUTED_GROSS Signed Number(20,6) DECIMAL(18,6) NOT NULL Diluted Labor Distribution Amt
    15 PAYABLE_STATUS Character(2) VARCHAR2(2) NOT NULL Payable Status
    AP=Approved
    CL=Closed
    DL=Diluted
    DN=Denied
    ES=Estimated
    IG=Ignore
    NA=Needs Approval
    NP=No Pay
    OE=Online Estimate
    PB=Pushed Back
    PD=Distributed
    RP=Rejected by Payroll
    RV=Reversed Check
    SP=Sent to Payroll
    TP=Taken by Payroll
    16 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project/Grant
    17 TRC_SUM_CATEGORY Character(6) VARCHAR2(6) NOT NULL TRC Category
    18 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    19 FROZEN_DATE Date(10) DATE Frozen Date
    20 ACCT_CD Character(25) VARCHAR2(25) NOT NULL Combination Code