EX_TJ_LN_AW_VW2

(SQL View)
 Index Back

Time Report Detail Adjust View

Used for Approval process

SELECT A.TIME_SHEET_ID , A.VERSION_NUM , A.LINE_NBR , A.BILLING_ACTION , (CASE WHEN A.STATE <> ' ' AND A.COUNTRY <> ' ' THEN A.COUNTRY || '/' || C.DESCR ELSE ' ' END) , (CASE WHEN A.LOCALITY <> ' ' THEN L.LOCALITY_NAME ELSE ' ' END) , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.ACTIVITY_ID , (CASE WHEN A.PROJECT_ID <> ' ' THEN PRJ.DESCR ELSE ' ' END) , (CASE WHEN A.ACTIVITY_ID <> ' ' THEN ACT.DESCR ELSE ' ' END) , %Substring(D.SHORTNAME, 1, 10) , SUM(B.TIME_QUANTITY) FROM PS_EX_TIME_DTL A LEFT OUTER JOIN PS_PROJECT PRJ ON A.BUSINESS_UNIT_PC = PRJ.BUSINESS_UNIT AND A.PROJECT_ID = PRJ.PROJECT_ID LEFT OUTER JOIN PS_PROJ_ACTIVITY ACT ON A.BUSINESS_UNIT_PC = ACT.BUSINESS_UNIT AND A.PROJECT_ID = ACT.PROJECT_ID AND A.ACTIVITY_ID = ACT.ACTIVITY_ID LEFT OUTER JOIN PS_STATE_TBL C ON A.COUNTRY = C.COUNTRY AND A.STATE = C.STATE LEFT OUTER JOIN PS_EX_CUR_LOCALITY L ON A.COUNTRY = L.COUNTRY AND A.STATE = L.STATE AND A.LOCALITY = L.LOCALITY , PS_EX_TIME_DTL_DLY B , PSDBFLDLABL D WHERE A.VERSION_NUM > 1 AND A.TIME_SHEET_ID = B.TIME_SHEET_ID AND A.VERSION_NUM = B.VERSION_NUM AND A.LINE_NBR = B.LINE_NBR AND D.FIELDNAME = 'FO_HOURS' AND D.DEFAULT_LABEL = 1 GROUP BY A.TIME_SHEET_ID, A.VERSION_NUM, A.LINE_NBR, A.BILLING_ACTION, (CASE WHEN A.STATE <> ' ' AND A.COUNTRY <> ' ' THEN A.COUNTRY || '/' || C.DESCR ELSE ' ' END), (CASE WHEN A.LOCALITY <> ' ' THEN L.LOCALITY_NAME ELSE ' ' END), A.BUSINESS_UNIT_PC, A.PROJECT_ID, A.ACTIVITY_ID, (CASE WHEN A.PROJECT_ID <> ' ' THEN PRJ.DESCR ELSE ' ' END), (CASE WHEN A.ACTIVITY_ID <> ' ' THEN ACT.DESCR ELSE ' ' END), D.SHORTNAME

# 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 BILLING_ACTION Character(1) VARCHAR2(1) NOT NULL Billing Action
B=Billable
I=Internal
P=Personal
U=Nonbillable
5 STATE_COUNTRY Character(40) VARCHAR2(40) NOT NULL Country/State
6 LOCALITY_NAME Character(40) VARCHAR2(40) NOT NULL Locality Name
7 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
8 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
9 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
10 PROJECT_ID_DESCR Character(30) VARCHAR2(30) NOT NULL Project ID Description
11 ACTIVITY_ID_DESCR Character(30) VARCHAR2(30) NOT NULL Activity ID Description
12 STD_HRS_DESCR Character(10) VARCHAR2(10) NOT NULL Standard Hours description
13 TIME_QUANTITY Signed Number(16,2) DECIMAL(14,2) NOT NULL Time Quantity