TL_RPT_LNFL_VW

(SQL View)
 Index Back

Reported time Fluid approvals

Time and Labor Reported time Fluid approvals Lines Grid data record

SELECT RT.EMPLID ,RT.EMPL_RCD ,X.EOAWDEFN_ID ,X.PUNCH_TYPE ,X.TRANSACTIONID ,RT.DUR ,RT.DUR ,%Coalesce(%TrimSubstr(RT.TRC,1,5) , 'Z!X!Z') , 'Hours' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,SUM(RT.TL_QUANTITY) ,'PS_TIME_EXCEPTIONS_M_FL' ,'N' FROM PS_TL_RPTD_TIME RT ,PS_TL_APP_RPTD_XRF X WHERE X.EMPLID=RT.EMPLID AND X.EMPL_RCD=RT.EMPL_RCD AND X.DUR=RT.DUR AND X.SEQ_NBR=RT.SEQ_NBR AND X.EOAWPARENT_THREAD <> X.EOAWTHREAD_ID AND RT.DUR IS NOT NULL AND X.TRANSACTIONID = ( SELECT MAX(TRANSACTIONID) FROM PS_TL_APP_RPTD_XRF SUB WHERE SUB.EMPLID=X.EMPLID AND SUB.EMPL_RCD=X.EMPL_RCD AND SUB.DUR=X.DUR AND SUB.SEQ_NBR=X.SEQ_NBR AND SUB.PUNCH_TYPE=X.PUNCH_TYPE) AND X.EOAWTHREAD_ID = ( SELECT MAX(EOAWTHREAD_ID) FROM PS_TL_APP_RPTD_XRF SUB WHERE SUB.EMPLID=X.EMPLID AND SUB.EMPL_RCD=X.EMPL_RCD AND SUB.DUR=X.DUR AND SUB.SEQ_NBR=X.SEQ_NBR AND SUB.PUNCH_TYPE=X.PUNCH_TYPE AND SUB.TRANSACTIONID = X.TRANSACTIONID) AND NOT EXISTS ( SELECT 'X' FROM PS_TL_RPTD_TIME RT2 ,PS_TL_WRKGRP_TBL C ,PS_TL_EMPL_DATA B WHERE RT.EMPLID = B.EMPLID AND RT.EMPL_RCD = B.EMPL_RCD AND RT2.EMPLID=B.EMPLID AND RT2.EMPL_RCD=B.EMPL_RCD AND RT2.DUR=RT.DUR AND RT2.PUNCH_TYPE=RT.PUNCH_TYPE AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_TL_EMPL_DATA B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.EMPL_RCD = B_ED.EMPL_RCD AND B_ED.EFFDT <= RT2.DUR) AND C.WORKGROUP = B.WORKGROUP AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_TL_WRKGRP_TBL C_ED WHERE C.WORKGROUP = C_ED.WORKGROUP AND C_ED.EFFDT <=RT2.DUR) AND ( C.INCL_ML_BRK_FLG='1' AND RT2.PUNCH_TYPE IN ('3','4') OR ( C.INCL_ML_BRK_FLG='2' AND RT2.PUNCH_TYPE ='4') OR ( C.INCL_ML_BRK_FLG='3' AND RT2.PUNCH_TYPE ='3'))) GROUP BY RT.EMPLID ,RT.EMPL_RCD ,X.EOAWDEFN_ID ,X.PUNCH_TYPE ,X.TRANSACTIONID ,RT.DUR ,RT.DUR,RT.TRC UNION SELECT RT.EMPLID ,RT.EMPL_RCD ,X.EOAWDEFN_ID ,X.PUNCH_TYPE ,X.TRANSACTIONID ,RT.BGN_DT ,RT.END_DT ,'Z!X!Z' , 'Hours' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,0 ,'' ,'' FROM PS_TL_APP_RPT_LINE RT ,PS_TL_APP_RPTD_XRF X WHERE X.EMPLID=RT.EMPLID AND X.EMPL_RCD=RT.EMPL_RCD AND X.DUR=RT.DUR AND X.SEQ_NBR=RT.SEQ_NBR AND X.EOAWPARENT_THREAD <> X.EOAWTHREAD_ID AND RT.DUR IS NOT NULL AND X.PUNCH_TYPE='X' AND X.TRANSACTIONID = ( SELECT MAX(TRANSACTIONID) FROM PS_TL_APP_RPTD_XRF SUB WHERE SUB.EMPLID=X.EMPLID AND SUB.EMPL_RCD=X.EMPL_RCD AND SUB.DUR=X.DUR AND SUB.SEQ_NBR=X.SEQ_NBR AND SUB.PUNCH_TYPE=X.PUNCH_TYPE) AND X.EOAWTHREAD_ID = ( SELECT MAX(EOAWTHREAD_ID) FROM PS_TL_APP_RPTD_XRF SUB WHERE SUB.EMPLID=X.EMPLID AND SUB.EMPL_RCD=X.EMPL_RCD AND SUB.DUR=X.DUR AND SUB.SEQ_NBR=X.SEQ_NBR AND SUB.PUNCH_TYPE=X.PUNCH_TYPE AND SUB.TRANSACTIONID = X.TRANSACTIONID)

# 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 EOAWDEFN_ID Character(30) VARCHAR2(30) NOT NULL Approval Framework Definition ID
4 PUNCH_TYPE Character(2) VARCHAR2(2) NOT NULL Global Punch Types
0=Elapsed
1=In
2=Out
3=Meal
4=Break
5=Transfer
5 TRANSACTIONID Number(10,0) DECIMAL(10) NOT NULL Transaction Identifier
6 DUR Date(10) DATE Date Under Report associates time being reported, scheduled or paid, to a specific date.
7 END_DT Date(10) DATE end date
8 TRC Character(5) VARCHAR2(5) NOT NULL Time Reporting Code used to report time in Time and Labor
9 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
10 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
11 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
12 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
13 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
14 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
15 DESCR6 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
16 DESCR7 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
17 TL_QUANTITY Signed Number(20,6) DECIMAL(18,6) NOT NULL This field represents the measure of Work in hrs,amount etc.,
18 EOPCIMAGE_ID Character(30) VARCHAR2(30) NOT NULL Image
19 EOPCUSE_MAP_DIR Character(1) VARCHAR2(1) NOT NULL Use MAP Image Directory