SPR_TIM3_TRC_VW

(SQL View)
 Index Back

Invoice TRC View

This view fetches the TRC codes and total hours for each Invoice Line

SELECT A.TIME_SHEET_ID , A.LINE_NBR , A.TRC , W.UNIT_OF_MEASURE , T.FACTOR_MULT , H.PERIOD_END_DT , W.SP_PAY_RATE , W.SP_PAY_RATE_BSE , W.SP_FIXED_COST , W.SP_FIXED_COST_BSE , W.SP_VNDR_MARKUP , W.SP_VNDR_MARKUP_BSE , W.SP_3RDPARTY_MARKUP , W.SP_3RDPTY_MKUP_BSE , W.CURRENCY_CD , W.CURRENCY_CD_BASE FROM PS_SPA_TIME_HDR H , PS_SPA_TM_DTL_HRLY A , PS_SPA_TIME_DTL D , PS_SPF_WO_PAYRATE W , PS_TL_TRC_TBL T WHERE H.TIME_SHEET_ID = D.TIME_SHEET_ID AND D.TIME_LINE_STATUS IN('RAP', 'ADJ') AND D.TE_INVOICE_FLG = 'N' AND D.SPA_BILLING_ACTION = 'B' AND D.TIME_SHEET_ID = A.TIME_SHEET_ID AND D.LINE_NBR = A.LINE_NBR AND W.BUSINESS_UNIT = D.BUSINESS_UNIT AND W.WORK_ORDER_ID = D.WORK_ORDER_ID AND W.EFFDT = ( SELECT MAX(W1.EFFDT) FROM PS_SPF_WO_PAYRATE W1 WHERE W1.BUSINESS_UNIT = W.BUSINESS_UNIT AND W1.WORK_ORDER_ID = W.WORK_ORDER_ID AND W.EFFDT <= H.PERIOD_END_DT) AND W.EFF_STATUS = 'A' AND T.TRC = A.TRC AND T.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_TL_TRC_TBL B WHERE T.TRC = B.TRC AND T.EFFDT <= %CurrentDateIn) AND T.EFF_STATUS = 'A' GROUP BY A.TIME_SHEET_ID , A.LINE_NBR , A.TRC, W.UNIT_OF_MEASURE , T.FACTOR_MULT , H.PERIOD_END_DT ,W.SP_PAY_RATE, W.SP_PAY_RATE_BSE, W.SP_FIXED_COST, W.SP_FIXED_COST_BSE, W.SP_VNDR_MARKUP, W.SP_VNDR_MARKUP_BSE, W.SP_3RDPARTY_MARKUP, W.SP_3RDPTY_MKUP_BSE, W.CURRENCY_CD, W.CURRENCY_CD_BASE

# 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 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
3 TRC Character(5) VARCHAR2(5) NOT NULL Time Reporting Code
4 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
5 FACTOR_MULT Signed Number(11,4) DECIMAL(9,4) NOT NULL Multiplication Factor
6 PERIOD_END_DT Date(10) DATE Represents the date termination point for a given time set utilized in cash worksheet processing.
7 SP_PAY_RATE Number(15,4) DECIMAL(14,4) NOT NULL pay rate
8 SP_PAY_RATE_BSE Number(15,4) DECIMAL(14,4) NOT NULL pay rate in the base currency
9 SP_FIXED_COST Number(15,4) DECIMAL(14,4) NOT NULL fixed costs
10 SP_FIXED_COST_BSE Number(15,4) DECIMAL(14,4) NOT NULL fixed costs in the base currency
11 SP_VNDR_MARKUP Number(15,4) DECIMAL(14,4) NOT NULL vendor markup
12 SP_VNDR_MARKUP_BSE Number(15,4) DECIMAL(14,4) NOT NULL vendor markup in the base currency
13 SP_3RDPARTY_MARKUP Number(15,4) DECIMAL(14,4) NOT NULL third party markup amount
14 SP_3RDPTY_MKUP_BSE Number(15,4) DECIMAL(14,4) NOT NULL third party markup amount in the base currency
15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
16 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency