GP_WC_RETRO

(SQL View)
 Index Back

GP WC Retro

GP WC Retro

SELECT A.EMPLID , A.COUNTRY , A.TRGR_EFFDT , A.TRGR_CREATE_TS , A.RTO_TRGR_SRC , D.FIRST_NAME , D.LAST_NAME , B.EMPL_RCD , A.TRGR_EVENT_ID , E.DESCR , A.TRGR_STATUS , A.RECNAME , A.FIELDNAME , A.TRGR_FLD_VAL_DT || (CASE WHEN A.TRGR_FLD_VAL_NUM = 0 THEN '' ELSE %NumToChar(A.TRGR_FLD_VAL_NUM) END) || A.TRGR_FLD_VAL_CHAR , A.TRGR_DESCR , C.CAL_ID , C.GP_PAYGROUP , C.CAL_RUN_ID FROM ((PS_GP_RTO_TRGR A ) LEFT OUTER JOIN PS_GP_RTO_EVT E ON A.COUNTRY = E.COUNTRY AND A.TRGR_EVENT_ID = E.TRGR_EVENT_ID ) , ((PS_JOB B ) LEFT OUTER JOIN PS_PERSON_NAME D ON B.EMPLID = D.EMPLID ) , PS_GP_CAL_RUN_DTL C WHERE ( ( A.EMPLID = B.EMPLID AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.EMPL_RCD = B_ED.EMPL_RCD AND B_ED.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.EMPL_RCD = B_ES.EMPL_RCD AND B.EFFDT = B_ES.EFFDT) AND A.TRGR_STATUS = 'U' AND B.GP_PAYGROUP = C.GP_PAYGROUP AND A.COUNTRY = C.COUNTRY AND C.CALC_TYPE = 'P' AND A.TRGR_EFFDT < C.PRD_BGN_DT AND A.TRGR_CREATE_TS <= C.PRD_END_DT AND EXISTS ( SELECT F.GP_PAYGROUP FROM PS_GP_WC_USR_PYGRP F WHERE ( F.GP_PAYGROUP = B.GP_PAYGROUP )) ))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
3 TRGR_EFFDT Date(10) DATE Trigger Effective Date
4 TRGR_CREATE_TS DateTime(26) TIMESTAMP Trigger Creation Timestamp
5 RTO_TRGR_SRC Character(1) VARCHAR2(1) NOT NULL Retro Trigger Source
A=Automatically Generated
F=Benefits Administration
M=Manually Generated
S=Mass Trigger
U=Utility Generated
6 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
7 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
8 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
9 TRGR_EVENT_ID Character(10) VARCHAR2(10) NOT NULL Trigger Event ID
10 DESCR Character(30) VARCHAR2(30) NOT NULL Description
11 TRGR_STATUS Character(1) VARCHAR2(1) NOT NULL Status of a retro trigger. Contains code set (translate values)
C=Cancelled
I=In Process
P=Processed
U=Unprocessed
12 RECNAME Character(15) VARCHAR2(15) NOT NULL Record (Table) Name (see PSRECDEFN).
13 FIELDNAME Character(18) VARCHAR2(18) NOT NULL Field Name (see PSDBFIELD).
14 CHARACTER_VALUE Character(20) VARCHAR2(20) NOT NULL character value
15 TRGR_DESCR Character(30) VARCHAR2(30) NOT NULL Trigger Tag
16 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
17 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
18 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id