GP_WC_ITER_VW

(SQL View)
 Index Back

GP WC Retro

GP WC Retro

SELECT DISTINCT A.EMPLID ,( SELECT B.NAME FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_NAMES B1 WHERE B1.EMPLID = B.EMPLID AND B1.NAME_TYPE = B.NAME_TYPE AND B1.EFFDT <= %CurrentDateIn)) ,A.COUNTRY , A.TRGR_CREATE_TS , A.ITER_TRGR_SRC , A.ITER_TRGR_STATUS , A.RECNAME , A.FIELDNAME , (CASE WHEN A.TRGR_FLD_VAL_DT IS NULL THEN '' ELSE %Cast(A.TRGR_FLD_VAL_DT, Date,Character) END) %Concat (CASE WHEN A.TRGR_FLD_VAL_NUM = 0 THEN '' ELSE %NumToChar(A.TRGR_FLD_VAL_NUM) END) %Concat A.TRGR_FLD_VAL_CHAR , C.GP_PAYGROUP , C.CAL_RUN_ID ,C.CAL_ID,D.OFF_CYCLE ,B.COMPANY,B.DEPTID,B.HR_STATUS,B.EMPL_STATUS , CASE WHEN ( SELECT DISTINCT D1.RUN_OPEN_IND FROM PS_GP_CAL_RUN D1 WHERE D1.CAL_RUN_ID = D.CAL_RUN_ID AND RUN_FINALIZED_IND = 'N') = 'Y' THEN 'O' WHEN ( SELECT DISTINCT D.RUN_FINALIZED_IND FROM PS_GP_CAL_RUN D2 WHERE D2.CAL_RUN_ID = D.CAL_RUN_ID AND D2.PMT_SENT_IND = 'N') = 'Y' THEN 'F' WHEN ( SELECT DISTINCT D.RUN_FINALIZED_IND FROM PS_GP_CAL_RUN D3 WHERE D3.CAL_RUN_ID = C.CAL_RUN_ID AND D3.PMT_SENT_IND = 'Y') = 'Y' THEN 'B' END ,0, 0 FROM PS_GP_ITER_TRGR A , PS_JOB B , PS_GP_CAL_RUNOF_VW C ,PS_GP_CAL_RUN D 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 B.GP_PAYGROUP = C.GP_PAYGROUP AND A.CAL_RUN_ID = C.CAL_RUN_ID AND A.COUNTRY = C.COUNTRY AND C.CALC_TYPE = 'P' AND C.CAL_RUN_ID = D.CAL_RUN_ID AND C.COUNTRY = D.COUNTRY AND A.ITER_TRGR_STATUS = 'U' AND EXISTS ( SELECT E.GP_PAYGROUP FROM PS_GP_WC_USR_PYGRP E WHERE E.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 NAME Character(50) VARCHAR2(50) NOT NULL Name
3 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
4 TRGR_CREATE_TS DateTime(26) TIMESTAMP Trigger Creation Timestamp
5 ITER_TRGR_SRC Character(1) VARCHAR2(1) NOT NULL Iterative Trigger Source
B=Batch
F=Benefits Administration
O=Online
S=Mass Trigger
U=Uncancel
V=Unsuspend
W=Time & Labor
Z=Time & Labor Feed
6 ITER_TRGR_STATUS Character(1) VARCHAR2(1) NOT NULL Iterative Trigger Status
C=Cancelled
I=InProcess
P=Processed
U=UnProcessed
7 RECNAME Character(15) VARCHAR2(15) NOT NULL Record (Table) Name (see PSRECDEFN).
8 FIELDNAME Character(18) VARCHAR2(18) NOT NULL Field Name (see PSDBFIELD).
9 FIELD_VALUE Character(30) VARCHAR2(30) NOT NULL Field Value
10 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
11 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
12 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
13 GP_OFF_CYCLE Character(2) VARCHAR2(2) NOT NULL OFF CYCLE Translate values
N=No
Y=Yes

Y/N Table Edit

14 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
15 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
16 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive
17 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
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
18 GP_CAL_STATUS Character(1) VARCHAR2(1) NOT NULL Calculation status
B=Finalized with Banking
F=Finalized
O=Open
19 GP_WC_FINAL_DAYS Number(4,0) SMALLINT NOT NULL Finalized day count
20 GP_WC_LIMIT_DAY Number(4,0) SMALLINT NOT NULL Work item day count