HR_MSS_CT_APRVW

(SQL View)
 Index Back

MSS CT Pending Transactions

This view is intended to select all rows that are in the approval process that have not yet reached final approval as well as the associated Approval Process Id

SELECT A.EMPLID ,A.EMPL_RCD ,A.ACTION_DT_SS ,A.EFFSEQ ,A.WF_STATUS ,A.ACTION ,A.ACTION_REASON_SS ,A.BUSINESS_UNIT ,A.POSITION_NBR ,A.DEPTID ,A.LOCATION ,A.JOBCODE ,A.SUPERVISOR_ID ,A.REPORTS_TO ,A.POSITION_FLAG ,A.ORIGINATORID ,A.TRANSACTION_NAME ,A.UPD_BY_USERID ,A.UPD_DTTM ,A.MANUAL_UPD_USERID ,A.MANUAL_UPD_DTTM ,A.SS_STAT_INDICATOR ,A.SS_WARNING_FLG ,A.SS_FUTURE_FLG ,A.HR_MSS_CT_NAME ,D.EOAWSTEP_STATUS ,D.OPRID ,D.EOAWORIG_OPRID ,%DatePart(D.EOAWDTTM_MODIFIED) ,C.EOAWTHREAD_ID ,D.EOAWDTTM_COMPLETE ,B.EOAWREQUESTOR_ID ,B.EOAWTHREAD_STATUS ,B.EOAWDTTM_MODIFIED ,C.EOAWPRCS_ID ,C.EOAWDEFN_ID ,C.EFFDT FROM PS_HR_MSS_CT_DAT A , PS_HR_MSS_CT_XREF B , PS_EOAW_STEPINST C , PS_EOAW_USERINST D WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.ACTION_DT_SS = B.ACTION_DT_SS AND A.EFFSEQ= B.EFFSEQ AND B.EOAWPRCS_ID = C.EOAWPRCS_ID AND B.EOAWDEFN_ID = C.EOAWDEFN_ID AND B.EOAWTHREAD_ID = C.EOAWTHREAD_ID AND D.EOAWSTEP_INSTANCE = C.EOAWSTEP_INSTANCE

# 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 ACTION_DT_SS Date(10) DATE Termination Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 WF_STATUS Character(1) VARCHAR2(1) NOT NULL Workflow Status
A=Approved
C=Cancelled
D=Denied
E=Error. Contact Administrator.
F=Awaiting final approval
I=In Approval Process
M=Administrator is Processing
N=Not Available
P=Rework
S=Submitted
V=Data Saved
6 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Prompt Table: ACTION_TBL

7 ACTION_REASON_SS Character(3) VARCHAR2(3) NOT NULL Action Reason field for use on Self Service panels.

Prompt Table: ACTN_REASON_TBL
Set Control Field: ACTION

8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_HR

9 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

Prompt Table: POSITION_BY_BUS

10 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

11 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

12 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

13 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

Prompt Table: HR_ACTIVE_DVW

14 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number

Prompt Table: POSITION_DATA

15 POSITION_FLAG Character(1) VARCHAR2(1) NOT NULL Position Flag
N=No
Y=Yes
16 ORIGINATORID Character(30) VARCHAR2(30) NOT NULL Originator Identifier
17 TRANSACTION_NAME Character(25) VARCHAR2(25) NOT NULL EDI Transaction Name
18 UPD_BY_USERID Character(30) VARCHAR2(30) NOT NULL Used on "_DAT" records for 8.3 Workflow Enhancements
19 UPD_DTTM DateTime(26) TIMESTAMP Used on "_DAT" records for 8.3 Workflow Enhancements
20 MANUAL_UPD_USERID Character(30) VARCHAR2(30) NOT NULL Used on "_DAT" records for 8.3 Workflow Enhancements
21 MANUAL_UPD_DTTM DateTime(26) TIMESTAMP Used on "_DAT" records for 8.3 Workflow Enhancements
22 SS_STAT_INDICATOR Character(1) VARCHAR2(1) NOT NULL Used on "_DAT" records for 8.3 Workflow Enhancements
A=Approved
B=CI failed - Errors & Warnings
C=Cancelled
D=Denied
E=Errors Encountered
I=In Approval Process
M=Administrator Action Required
S=Success
W=Warnings Encountered

Default Value: I

23 SS_WARNING_FLG Character(1) VARCHAR2(1) NOT NULL Used on "_DAT" records for 8.3 Workflow Enhancements

Y/N Table Edit

Default Value: N

24 SS_FUTURE_FLG Character(1) VARCHAR2(1) NOT NULL Used on "_DAT" records for 8.3 Workflow Enhancements

Y/N Table Edit

Default Value: N

25 HR_MSS_CT_NAME Character(25) VARCHAR2(25) NOT NULL Compound Transaction Name
26 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
27 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
28 EOAWORIG_OPRID Character(30) VARCHAR2(30) NOT NULL User ID
29 EOAWSTART_DT Date(10) DATE Start Date
30 EOAWTHREAD_ID Number(15,0) DECIMAL(15) NOT NULL Approval Framework Thread ID
31 EOAWDTTM_COMPLETE DateTime(26) TIMESTAMP Date/Time Completed
32 EOAWREQUESTOR_ID Character(30) VARCHAR2(30) NOT NULL Requestor id
33 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
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 Effective Date

Default Value: %date