EX_TR_MAP_AW_VW

(SQL View)
 Index Back

Time Report Header View


SELECT H.TIME_SHEET_ID , H.VERSION_NUM , H.EMPLID , H.PERIOD_END_DT , H.TIME_SHEET_STATUS , H.PROJ_MGR_FLAG , H.CREATION_DT , H.APPROVAL_DT , H.SUBMISSION_DATE , H.RESUBMISSION_DT , H.LASTUPDDTTM , H.OPRID_LAST_UPDT , H.DTL_TOTAL , H.POL_TOTAL , H.STATE , H.COUNTRY , H.LOCALITY , H.BILLING_ACTION , H.COMMENTS , H.SETID , H.DEPTID , H.BUSINESS_UNIT_GL , H.EMPL_RCD , H.OPRID_ENTERED_BY , P.PRIORITY_FLG , X.EOAWTHREAD_STATUS , U.EOAWSTEP_STATUS , U.OPRID , U.EOAWORIG_OPRID ,%DatePart(U.EOAWDTTM_MODIFIED) ,S.EOAWTHREAD_ID ,U.EOAWDTTM_COMPLETE ,X.EOAWREQUESTOR_ID ,X.EOAWDTTM_MODIFIED ,S.EOAWPRCS_ID ,S.EOAWDEFN_ID ,S.EFFDT ,S.EOAWSTEP_NBR FROM PS_EX_TIME_HDR H , PS_EX_TIME_AW X , PS_EOAW_STEPINST S , PS_EOAW_USERINST U , PS_EX_TIME_MAP_TMP P WHERE H.TIME_SHEET_ID = X.TIME_SHEET_ID AND H.TIME_SHEET_ID = P.TIME_SHEET_ID AND X.EOAWTHREAD_ID = S.EOAWTHREAD_ID AND X.EOAWPRCS_ID = S.EOAWPRCS_ID AND X.EOAWDEFN_ID = S.EOAWDEFN_ID AND S.EOAWSTEP_INSTANCE = U.EOAWSTEP_INSTANCE AND X.EOAWTHREAD_ID = ( CASE WHEN EXISTS ( SELECT EOAWTHREAD_STATUS FROM PS_EX_TIME_AW C1 WHERE H.TIME_SHEET_ID = C1.TIME_SHEET_ID AND C1.RECNAME = 'EX_TR_HDR_AW_VW' AND C1.EOAWTHREAD_STATUS = 'P') THEN ( SELECT MIN(X1.EOAWTHREAD_ID) FROM PS_EX_TIME_AW X1 WHERE X1.TIME_SHEET_ID = X.TIME_SHEET_ID) WHEN EXISTS ( SELECT EOAWTHREAD_STATUS FROM PS_EX_TIME_AW C1 WHERE H.TIME_SHEET_ID = C1.TIME_SHEET_ID AND C1.RECNAME = 'EX_TR_LN_AW_VW' AND C1.EOAWTHREAD_STATUS = 'P') THEN ( SELECT MAX(X1.EOAWTHREAD_ID) FROM PS_EX_TIME_AW X1 WHERE X1.TIME_SHEET_ID = X.TIME_SHEET_ID) END ) AND H.VERSION_NUM = 1

# 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
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 PERIOD_END_DT Date(10) DATE Represents the date termination point for a given time set utilized in cash worksheet processing.
5 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

6 PROJ_MGR_FLAG Character(1) VARCHAR2(1) NOT NULL Project Manager Review
7 CREATION_DT Date(10) DATE Creation Date

Default Value: %date

8 APPROVAL_DT Date(10) DATE Date of Approval
9 SUBMISSION_DATE Date(10) DATE Submission Date
10 RESUBMISSION_DT Date(10) DATE Resubmission Date
11 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
12 OPRID_LAST_UPDT Character(30) VARCHAR2(30) NOT NULL Last User to Update
13 DTL_TOTAL Signed Number(7,2) DECIMAL(5,2) NOT NULL Total
14 POL_TOTAL Signed Number(7,2) DECIMAL(5,2) NOT NULL PolTotal
15 STATE Character(6) VARCHAR2(6) NOT NULL State
16 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
17 LOCALITY Character(10) VARCHAR2(10) NOT NULL Locality
18 BILLING_ACTION Character(1) VARCHAR2(1) NOT NULL Billing Action
B=Billable
I=Internal
P=Personal
U=Nonbillable
19 COMMENTS Long Character CLOB Comment
20 SETID Character(5) VARCHAR2(5) NOT NULL SetID
21 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
22 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

Default Value: EX_AA_EE_ORG_VW.BUSINESS_UNIT_GL

23 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

Default Value: EX_AA_EE_ORG_VW.EMPL_RCD

24 OPRID_ENTERED_BY Character(30) VARCHAR2(30) NOT NULL Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes
25 PRIORITY_FLG Character(1) VARCHAR2(1) NOT NULL Priority Flag
1=High
2=Medium
3=Low
26 EOAWTHREAD_STATUS Character(1) VARCHAR2(1) NOT NULL Thread Status
A=Approved
C=Complete
D=Denied
E=Pending Denial
H=Hard Deny
I=Initial
N=Not Active
P=Pending
S=Awaiting Further Approvals
T=Terminated
W=Canceled
X=Suspended/Pending Denial
27 EOAWSTEP_STATUS Character(1) VARCHAR2(1) NOT NULL Step Instance Status
A=Approved
B=Pushed back
C=Auto Approved
D=Denied
E=Administrator denied
F=Deleted
G=Bypassed
H=On Hold
I=No Action Taken
K=Skipped
L=Terminated
M=Administrator approved
N=Not Active
P=Pending
R=No longer in role
T=Terminated
U=Auto approved
X=Deleted
Z=Pending Denial
28 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
29 EOAWORIG_OPRID Character(30) VARCHAR2(30) NOT NULL User ID
30 EOAWSTART_DT Date(10) DATE Start Date
31 EOAWTHREAD_ID Number(15,0) DECIMAL(15) NOT NULL Approval Framework Thread ID
32 EOAWDTTM_COMPLETE DateTime(26) TIMESTAMP Date/Time Completed
33 EOAWREQUESTOR_ID Character(30) VARCHAR2(30) NOT NULL Requestor id
34 EOAWDTTM_MODIFIED DateTime(26) TIMESTAMP Datetime modified
35 EOAWPRCS_ID Character(30) VARCHAR2(30) NOT NULL Approval Process ID
36 EOAWDEFN_ID Character(30) VARCHAR2(30) NOT NULL Approval Framework Definition ID
37 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

38 EOAWSTEP_NBR Number(6,2) DECIMAL(5,2) NOT NULL Sequence number of step in its path