EX_HRBILL_AN_VW

(SQL View)
 Index Back

EX HOUR BILLABLE ANALYSIS

PIVOT GRID

SELECT A.EMPLID , D.FIRST_NAME %Concat ' ' %Concat D.LAST_NAME , A.BUSINESS_UNIT_GL , B1.DEPTID %Concat ' - ' %Concat E.DESCR , A.TIME_SHEET_ID , A.VERSION_NUM , A.TIME_SHEET_STATUS , A.PERIOD_END_DT , 'P' , SUM(C.TIME_QUANTITY) , B.TRC , B.DESCR60 FROM PS_EX_TIME_HDR A , PS_EX_TIME_POL B , PS_EX_TIME_DTL B1 , PS_EX_TIME_POL_DLY C , PS_PERSONAL_DATA D , PS_DEPT_TBL E WHERE A.TIME_SHEET_ID = B.TIME_SHEET_ID AND A.TIME_SHEET_ID = B1.TIME_SHEET_ID AND A.TIME_SHEET_ID = C.TIME_SHEET_ID AND A.VERSION_NUM = B.VERSION_NUM AND A.VERSION_NUM = B1.VERSION_NUM AND A.VERSION_NUM = C.VERSION_NUM AND B.LINE_NBR = C.LINE_NBR AND B.LINE_NBR = B1.LINE_NBR AND B.EX_LINE_STATUS NOT IN ('PND','DEN','DNA','DNU') AND A.EMPLID = D.EMPLID AND E.SETID = ( SELECT S.SETID FROM PS_SET_CNTRL_REC S WHERE S.SETCNTRLVALUE = A.BUSINESS_UNIT_GL AND S.REC_GROUP_ID = 'FS_06' AND S.RECNAME = 'DEPT_TBL') AND E.DEPTID = B1.DEPTID AND %EffdtCheck(DEPT_TBL, E, %CurrentDateIn) AND E.EFF_STATUS = 'A' GROUP BY A.EMPLID , D.FIRST_NAME %Concat ' ' %Concat D.LAST_NAME , A.BUSINESS_UNIT_GL ,B1.DEPTID %Concat ' - ' %Concat E.DESCR , A.TIME_SHEET_ID , A.VERSION_NUM , A.TIME_SHEET_STATUS , A.PERIOD_END_DT , B.TRC , B.DESCR60 UNION SELECT A.EMPLID , D.FIRST_NAME %Concat ' ' %Concat D.LAST_NAME , A.BUSINESS_UNIT_GL , B.DEPTID %Concat ' - ' %Concat E.DESCR , A.TIME_SHEET_ID , A.VERSION_NUM , B.EX_LINE_STATUS , A.PERIOD_END_DT , B.BILLING_ACTION ,SUM(C.TIME_QUANTITY) , ' ' , ' ' FROM PS_EX_TIME_HDR A , PS_EX_TIME_DTL B , PS_EX_TIME_DTL_DLY C , PS_PERSONAL_DATA D , PS_DEPT_TBL E WHERE A.TIME_SHEET_ID = B.TIME_SHEET_ID AND A.TIME_SHEET_ID = C.TIME_SHEET_ID AND A.VERSION_NUM = B.VERSION_NUM AND A.VERSION_NUM = C.VERSION_NUM AND B.LINE_NBR = C.LINE_NBR AND B.EX_LINE_STATUS NOT IN ('PND','DEN','DNA','DNU') AND A.EMPLID = D.EMPLID AND E.SETID = ( SELECT S.SETID FROM PS_SET_CNTRL_REC S WHERE S.SETCNTRLVALUE = A.BUSINESS_UNIT_GL AND S.REC_GROUP_ID = 'FS_06' AND S.RECNAME = 'DEPT_TBL') AND E.DEPTID = B.DEPTID AND %EffdtCheck(DEPT_TBL, E, %CurrentDateIn) AND E.EFF_STATUS = 'A' GROUP BY A.EMPLID , D.FIRST_NAME %Concat ' ' %Concat D.LAST_NAME , A.BUSINESS_UNIT_GL , B.DEPTID %Concat ' - ' %Concat E.DESCR , A.TIME_SHEET_ID , A.VERSION_NUM , B.EX_LINE_STATUS , A.PERIOD_END_DT , B.BILLING_ACTION

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 NAME_60 Character(61) VARCHAR2(61) NOT NULL Name
3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
4 DESCR_DEPT Character(45) VARCHAR2(45) NOT NULL Deptid + Description
5 TIME_SHEET_ID Character(10) VARCHAR2(10) NOT NULL Time Sheet Unique Identifier (System Generated)
6 VERSION_NUM Number(3,0) SMALLINT NOT NULL Version Number
7 TIME_SHEET_STATUS Character(3) VARCHAR2(3) NOT NULL Time Sheet Status
ADJ=Adjusted
APR=Approved
DEN=Denied
DNA=Denied by Approver
DNU=Denied by Project Manager
HDA=Hold by Approver
HDU=Hold by Project Manager
HLD=Hold
MFS=Marked for Submit
PAR=Approvals in Process
PND=Pending
PRO=In Process
SFA=Submission in Process
SUB=Submitted for Approval
XML=Submitted, Pending Validation

Default Value: PND

8 PERIOD_END_DT Date(10) DATE Represents the date termination point for a given time set utilized in cash worksheet processing.
9 BILLING_ACTION Character(1) VARCHAR2(1) NOT NULL Billing Action
B=Billable
I=Internal
P=Personal
U=Nonbillable
10 TIME_QUANTITY Signed Number(16,2) DECIMAL(14,2) NOT NULL Time Quantity
11 TRC Character(5) VARCHAR2(5) NOT NULL Time Reporting Code

Prompt Table: EX_TRC_MAP_VW

12 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description