HR_MSS_DATD_VW

(SQL View)
 Index Back

GSS VALIDATIONS

GSS VALIDATIONS FOR MULTI TRANSATIONS.

SELECT A.EMPLID , A.EMPL_RCD , A.ACTION_DT_SS , A.EFFSEQ , A.HR_MSS_CT_NAME , A.WF_STATUS , A.ACTION , A.ACTION_REASON_SS , A.BUSINESS_UNIT , A.ORIGINATORID , A.TRANSACTION_NAME FROM PS_HR_MSS_CT_DAT A WHERE EXISTS ( SELECT XL.FIELDVALUE FROM PSXLATITEM XL WHERE XL.FIELDNAME = 'WF_STATUS' AND XL.FIELDVALUE <> 'A' AND XL.FIELDVALUE <> 'D' AND XL.FIELDVALUE <> 'E' AND XL.FIELDVALUE <> 'P' AND XL.FIELDVALUE <> 'C' AND XL.FIELDVALUE <> 'V' AND XL.FIELDVALUE = A.WF_STATUS) AND A.ACTION_DT_SS = ( SELECT MAX(A_ED.ACTION_DT_SS) FROM PS_HR_MSS_CT_DAT A_ED WHERE A_ED.EMPLID = A.EMPLID AND A_ED.EMPL_RCD = A.EMPL_RCD AND A_ED.WF_STATUS = A.WF_STATUS) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_HR_MSS_CT_DAT A_ES WHERE A_ES.EMPLID = A.EMPLID AND A_ES.EMPL_RCD = A.EMPL_RCD AND A_ES.ACTION_DT_SS = A.ACTION_DT_SS) AND EXISTS ( SELECT CFG.HR_MSS_CT_NAME FROM PS_HR_MSS_CT_CFG CFG WHERE CFG.HR_MSS_CT_NAME = A.HR_MSS_CT_NAME AND CFG.STATUS = 'A' AND CFG.HR_MSS_CT_NAME <> 'HR_LOA_PAID' AND CFG.HR_MSS_CT_NAME <> 'HR_LOA_UNPAID' AND CFG.HR_MSS_CT_NAME <> 'HR_POSITION_CLONE')

# 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 HR_MSS_CT_NAME Character(25) VARCHAR2(25) NOT NULL Compound Transaction Name
6 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
7 ACTION Character(3) VARCHAR2(3) NOT NULL Action
8 ACTION_REASON_SS Character(3) VARCHAR2(3) NOT NULL Action Reason field for use on Self Service panels.
9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
10 ORIGINATORID Character(30) VARCHAR2(30) NOT NULL Originator Identifier
11 TRANSACTION_NAME Character(25) VARCHAR2(25) NOT NULL EDI Transaction Name