EX_INACT_EE2_VW (SQL View)	Index Back
Inactive employee transactions Used in EX WorkCenter

EX_INACT_EE2_VW

(SQL View)

Inactive employee transactions

Used in EX WorkCenter

SELECT A.EMPLID ,B.EMPL_STATUS , A.ADV_STATUS , A.CURRENCY_CD , A.TOTAL_AMT , A.BALANCE ,A.ADVANCE_ID ,A.BUSINESS_PURPOSE ,A.ADVANCE_NAME ,A.CREATION_DT ,A.OPRID_LAST_UPDT ,A.LASTUPDDTTM FROM PS_EX_ADV_HDR A , PS_JOB B WHERE A.ADV_STATUS NOT IN ( 'RCN', 'CLS') AND A.EMPLID = B.EMPLID AND B.EMPL_STATUS IN ('D','I','R', 'Q','X', 'S','T','U','V')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERSONAL_DATA

2 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Employee Status
A=Active
D=Deceased
I=Inactive
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration

3 ADV_STATUS Character(3) VARCHAR2(3) NOT NULL Advance Status
APY=Approved for Payment
CLS=Closed
DEN=Denied
DNA=Denied by Approver
DNU=Denied by Auditor
HDA=On Hold, with Approver
HDU=On Hold, with Auditor
HLD=On Hold
PAR=Approvals in Process
PD=Paid
PND=Pending
PRO=In Process
PST=Past Due Balance
RAP=Approved
RCN=Reconciled
RRT=Reroute Transaction
SFA=Submission in Process
STG=Staged
SUB=Submitted for Approval
XML=Submitted, Pending Validation

4 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

5 TOTAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount

6 BALANCE Signed Number(28,3) DECIMAL(26,3) NOT NULL Balance

7 ADVANCE_ID Character(10) VARCHAR2(10) NOT NULL Advance ID
Default Value: NEXT

8 BUSINESS_PURPOSE Character(5) VARCHAR2(5) NOT NULL Business Purpose
Prompt Table: EX_PURPOSE_TBL

9 ADVANCE_NAME Character(30) VARCHAR2(30) NOT NULL Advance Description

10 CREATION_DT Date(10) DATE Creation Date
Default Value: %date

11 OPRID_LAST_UPDT Character(30) VARCHAR2(30) NOT NULL Last User to Update

12 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.
Default Value: %datetime

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSONAL_DATA
2	EMPL_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Employee Status A=Active D=Deceased I=Inactive L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration
3	ADV_STATUS	Character(3)	VARCHAR2(3) NOT NULL	Advance Status APY=Approved for Payment CLS=Closed DEN=Denied DNA=Denied by Approver DNU=Denied by Auditor HDA=On Hold, with Approver HDU=On Hold, with Auditor HLD=On Hold PAR=Approvals in Process PD=Paid PND=Pending PRO=In Process PST=Past Due Balance RAP=Approved RCN=Reconciled RRT=Reroute Transaction SFA=Submission in Process STG=Staged SUB=Submitted for Approval XML=Submitted, Pending Validation
4	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
5	TOTAL_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Total Amount
6	BALANCE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Balance
7	ADVANCE_ID	Character(10)	VARCHAR2(10) NOT NULL	Advance ID Default Value: NEXT
8	BUSINESS_PURPOSE	Character(5)	VARCHAR2(5) NOT NULL	Business Purpose Prompt Table: EX_PURPOSE_TBL
9	ADVANCE_NAME	Character(30)	VARCHAR2(30) NOT NULL	Advance Description
10	CREATION_DT	Date(10)	DATE	Creation Date Default Value: %date
11	OPRID_LAST_UPDT	Character(30)	VARCHAR2(30) NOT NULL	Last User to Update
12	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. Default Value: %datetime