EX_TR_LN_AW_VW2

(SQL View)
 Index Back

Time Report Detail 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 'Y' IN ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('Y')) THEN GL.DESCR WHEN 'Y' IN ( SELECT 'Y' FROM PS_BUS_UNIT_TBL_EX WHERE BUSINESS_UNIT_GL = H.BUSINESS_UNIT_GL AND SHOW_DESCR_OPT = 'Y' AND EXISTS ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('D'))) THEN GL.DESCR ELSE A.BUSINESS_UNIT_PC END ,CASE WHEN 'Y' IN ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('Y')) THEN PRJ.DESCR WHEN 'Y' IN ( SELECT 'Y' FROM PS_BUS_UNIT_TBL_EX WHERE BUSINESS_UNIT_GL = H.BUSINESS_UNIT_GL AND SHOW_DESCR_OPT = 'Y' AND EXISTS ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('D'))) THEN PRJ.DESCR ELSE A.PROJECT_ID END , CASE WHEN 'Y' IN ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('Y')) THEN ACT.DESCR WHEN 'Y' IN ( SELECT 'Y' FROM PS_BUS_UNIT_TBL_EX WHERE BUSINESS_UNIT_GL = H.BUSINESS_UNIT_GL AND SHOW_DESCR_OPT = 'Y' AND EXISTS ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('D'))) THEN ACT.DESCR ELSE A.ACTIVITY_ID END , (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, PS_EX_TIME_HDR H, PS_SP_BUEXGL_NONVW GL WHERE A.VERSION_NUM <= 1 AND A.TIME_SHEET_ID = B.TIME_SHEET_ID AND A.TIME_SHEET_ID = H.TIME_SHEET_ID AND A.BUSINESS_UNIT_GL = GL.BUSINESS_UNIT_GL AND A.BUSINESS_UNIT_PC = GL.BUSINESS_UNIT_PC 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 'Y' IN ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('Y')) THEN GL.DESCR WHEN 'Y' IN ( SELECT 'Y' FROM PS_BUS_UNIT_TBL_EX WHERE BUSINESS_UNIT_GL = H.BUSINESS_UNIT_GL AND SHOW_DESCR_OPT = 'Y' AND EXISTS ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('D'))) THEN GL.DESCR ELSE A.BUSINESS_UNIT_PC END , CASE WHEN 'Y' IN ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('Y')) THEN PRJ.DESCR WHEN 'Y' IN ( SELECT 'Y' FROM PS_BUS_UNIT_TBL_EX WHERE BUSINESS_UNIT_GL = H.BUSINESS_UNIT_GL AND SHOW_DESCR_OPT = 'Y' AND EXISTS ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('D'))) THEN PRJ.DESCR ELSE A.PROJECT_ID END , CASE WHEN 'Y' IN ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('Y')) THEN ACT.DESCR WHEN 'Y' IN ( SELECT 'Y' FROM PS_BUS_UNIT_TBL_EX WHERE BUSINESS_UNIT_GL = H.BUSINESS_UNIT_GL AND SHOW_DESCR_OPT = 'Y' AND EXISTS ( SELECT 'Y' FROM PS_EX_EE_USER_PREF WHERE EMPLID = H.EMPLID AND SHOW_DESCR_OPT IN ('D'))) THEN ACT.DESCR ELSE A.ACTIVITY_ID END, (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 DESCR40 Character(40) VARCHAR2(40) NOT NULL Description
11 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
12 DESCR50_MIXED Character(50) VARCHAR2(50) NOT NULL Description 50 characters, mixed case
13 PROJECT_ID_DESCR Character(30) VARCHAR2(30) NOT NULL Project ID Description
14 ACTIVITY_ID_DESCR Character(30) VARCHAR2(30) NOT NULL Activity ID Description
15 STD_HRS_DESCR Character(10) VARCHAR2(10) NOT NULL Standard Hours description
16 TIME_QUANTITY Signed Number(16,2) DECIMAL(14,2) NOT NULL Time Quantity