EX_MAP_AW_VW

(SQL View)
 Index Back

Expense Report Header View


SELECT DISTINCT H.SHEET_ID , H.EMPLID , H.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.SHEET_NAME , H.SETID , H.BUSINESS_UNIT_GL , H.EMPL_RCD , H.ACCOUNTING_DT , H.BUSINESS_PURPOSE , H.OPRID_ENTERED_BY , H.TRAVEL_AUTH_ID , H.TOTAL_AMT , H.TOTAL_REIMB_AMT , H.CURRENCY_CD , 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_SHEET_HDR H , PS_EX_SHEET_AW X , PS_EOAW_STEPINST S , PS_EOAW_USERINST U , PS_EX_SHT_MAP_TMP P WHERE H.SHEET_ID = X.SHEET_ID AND H.SHEET_ID = P.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_SHEET_AW C1 WHERE H.SHEET_ID = C1.SHEET_ID AND C1.RECNAME = 'EX_ER_HDR_AW_VW' AND C1.EOAWTHREAD_STATUS = 'P') THEN ( SELECT MIN(X1.EOAWTHREAD_ID) FROM PS_EX_SHEET_AW X1 WHERE X1.SHEET_ID = X.SHEET_ID AND X1.RECNAME = 'EX_ER_HDR_AW_VW' AND X1.EOAWTHREAD_STATUS = 'P') WHEN EXISTS ( SELECT EOAWTHREAD_STATUS FROM PS_EX_SHEET_AW C1 WHERE H.SHEET_ID = C1.SHEET_ID AND C1.RECNAME = 'EX_ER_LN_AW_VW' AND C1.EOAWTHREAD_STATUS = 'P') THEN ( SELECT MAX(X1.EOAWTHREAD_ID) FROM PS_EX_SHEET_AW X1 WHERE X1.SHEET_ID = X.SHEET_ID AND X1.RECNAME = 'EX_ER_LN_AW_VW' AND X1.EOAWTHREAD_STATUS = 'P') END )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID

Default Value: NEXT

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 SHEET_STATUS Character(3) VARCHAR2(3) NOT NULL Report Status
APY=Approved for Payment
APZ=Approved For Zero Payment
CLS=Closed
DEN=Denied
DNA=Denied by Approver
DNU=Denied by Auditor
ESC=Escheated Payment
HDA=On Hold, with Approver
HDU=On Hold, with Auditor
HLD=On Hold
MFS=Marked for Submit
OPN=Open
PAR=Approvals in Process
PD=Paid
PND=Pending
PRO=In Process
RAP=Approved
SFA=Submission in Process
STG=Staged
SUB=Submitted for Approval
XML=Submitted, Pending Validation

Default Value: PND

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

Default Value: %date

6 APPROVAL_DT Date(10) DATE Date of Approval
7 SUBMISSION_DATE Date(10) DATE Submission Date
8 RESUBMISSION_DT Date(10) DATE Resubmission Date
9 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.
10 OPRID_LAST_UPDT Character(30) VARCHAR2(30) NOT NULL Last User to Update
11 SHEET_NAME Character(30) VARCHAR2(30) NOT NULL Report Description
12 SETID Character(5) VARCHAR2(5) NOT NULL SetID
13 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

Default Value: EX_AA_EE_ORG_VW.BUSINESS_UNIT_GL

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

Default Value: EX_AA_EE_ORG_VW.EMPL_RCD

15 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
16 BUSINESS_PURPOSE Character(5) VARCHAR2(5) NOT NULL Business Purpose

Prompt Table: EX_PURPOSE_TBL

17 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
18 TRAVEL_AUTH_ID Character(10) VARCHAR2(10) NOT NULL Travel Authorization Identification. Number used to define travel authorizations. Key field on most travel authorization records and exists at level 0 for travel authorization panels.
19 TOTAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount
20 TOTAL_REIMB_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Due Employee
21 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
22 PRIORITY_FLG Character(1) VARCHAR2(1) NOT NULL Priority Flag
1=High
2=Medium
3=Low
23 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
24 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
25 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
26 EOAWORIG_OPRID Character(30) VARCHAR2(30) NOT NULL User ID
27 EOAWSTART_DT Date(10) DATE Start Date
28 EOAWTHREAD_ID Number(15,0) DECIMAL(15) NOT NULL Approval Framework Thread ID
29 EOAWDTTM_COMPLETE DateTime(26) TIMESTAMP Date/Time Completed
30 EOAWREQUESTOR_ID Character(30) VARCHAR2(30) NOT NULL Requestor id
31 EOAWDTTM_MODIFIED DateTime(26) TIMESTAMP Datetime modified
32 EOAWPRCS_ID Character(30) VARCHAR2(30) NOT NULL Approval Process ID
33 EOAWDEFN_ID Character(30) VARCHAR2(30) NOT NULL Approval Framework Definition ID
34 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

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