MARSTACHG_DTVWL

(SQL View)
 Index Back

Marital Status Changes

Marital Status Change Detail View

SELECT NEW.EMPLID , NEW.ACTION_DT_SS , NEW.EFFSEQ , CURSXL.LANGUAGE_CD ,'' , %Sql(HR_HT_EMAIL_DATE,NEW.ACTION_DT_SS) ,CUR.MAR_STATUS , CURSXL.XLATLONGNAME ,NEW.MAR_STATUS ,NEWSXL.XLATLONGNAME , NEW.MAR_STATUS_DT , NEW.WF_STATUS , WFX.XLATSHORTNAME , NEW.ORIGINATORID ,EMP.NAME , NEW.ACTION , NEW.ACTION_REASON ,'X' ,NEW.APPROVAL_REQUIRED FROM PS_HR_MARSTAT_VW CUR , PS_HR_MAR_STAT_DAT NEW , PS_HR_MARITAL_STA STA , PSOPRDEFN OPR , PS_HR_XLAT_EFF_VW WF ,PS_PERSON_NAME D , PS_PERSON_NAME EMP , PSXLATITEMLANG CURSXL , PSXLATITEMLANG NEWSXL , PSXLATITEMLANG WFX WHERE NEW.EMPLID=CUR.EMPLID AND NEW.EMPLID=STA.EMPLID AND NEW.ACTION_DT_SS=STA.ACTION_DT_SS AND STA.SEQNO=1 AND STA.EFFSEQ=NEW.EFFSEQ AND WF.FIELDNAME = 'WF_STATUS' AND WF.FIELDVALUE = NEW.WF_STATUS AND NEW.ORIGINATORID = OPR.OPRID AND OPR.EMPLID=D.EMPLID AND D.EMPLID=NEW.EMPLID AND NEW.action_dt_ss = sta.action_dt_ss AND CURSXL.FIELDNAME = 'MAR_STATUS' AND CURSXL.FIELDVALUE=CUR.MAR_STATUS AND NEWSXL.FIELDNAME = 'MAR_STATUS' AND NEWSXL.FIELDVALUE=NEW.MAR_STATUS AND NEW.EMPLID = EMP.EMPLID AND WFX.FIELDNAME='HR_WF_ACTION' AND WFX.FIELDVALUE= STA.HR_WF_ACTION AND CURSXL.LANGUAGE_CD= NEWSXL.LANGUAGE_CD AND NEWSXL.LANGUAGE_CD=WFX.LANGUAGE_CD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 ACTION_DT_SS Date(10) DATE Termination Date
3 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
4 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
5 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
6 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
7 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
C=Common-Law
D=Divorced
E=Separated
H=Head of Household
L=DissDeclLost Civil Partner
M=Married
P=Civil Partnership
S=Single
T=Surviving Civil Partner
U=Unknown
V=Dissolved Civil Partnership
W=Widowed
8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
9 MAR_STATUS_NEW Character(1) VARCHAR2(1) NOT NULL New Marital Status
C=Common-Law
D=Divorced
E=Separated
H=Head of Household
L=DissDecILost Civil Partner
M=Married
P=Civil Partnership
S=Single
T=Surviving Civil Partner
U=Unknown
V=Dissolved Civil Partnership
W=Widowed
10 DESCR6 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
11 MAR_STATUS_DT Date(10) DATE This is the date that the employee's Marital status is/was effective. /* Marital Status date is stored redundantly on PERS_DATA_EFFDT for two reasons. 1. To allow the date to be entered prior to the first PERS_DATA_EFFDT so that the actual date for the marital status on the hire row can be entered. This is the only time that the field is enterable by the user. 2. To allow for easier reporting on the data via SQR and Crystal. This is denormalizing the PERS_DATA_EFFDT record, but the number of requests for this redundancy coupled with the need for #1, has provided the justification for denormalizing this information and taking on the added maintenance. */
12 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
13 XLATSHORTNAME Character(10) VARCHAR2(10) NOT NULL Translate Value Short Name
14 ORIGINATORID Character(30) VARCHAR2(30) NOT NULL Originator Identifier
15 NAME Character(50) VARCHAR2(50) NOT NULL Name
16 ACTION Character(3) VARCHAR2(3) NOT NULL Action
17 ACTION_REASON_SS Character(3) VARCHAR2(3) NOT NULL Action Reason field for use on Self Service panels.
18 DESCR_COMMENTS_HIS Long Character CLOB This field is used in a complex query that shows many descr fields.
19 APPROVAL_REQUIRED Character(1) VARCHAR2(1) NOT NULL Field for page WF_CO_CONFIG. Checkbox used for "Approval Required" on configuration page.
N=No Approval Required
Y=Approval Required