PY_IC_LSTPYCHVW

(SQL View)
 Index Back

My Pay


SELECT A.EMPLID , A.CHECK_DT , A.PAYCHECK_OPTION , A.NET_PAY , A.TOTAL_GROSS , CASE WHEN ( SELECT COUNT(F.PAYCHECK_NBR) FROM PS_PY_SS_PSLP_GDE F WHERE A.EMPLID = F.EMPLID AND A.PAYCHECK_NBR = F.PAYCHECK_NBR AND F.PY_SS_PSLP_STATUS = 'ORIG' AND F.PY_PSLP_VIEW_DT <= %CurrentDateIn) > 0 THEN 'Y' ELSE 'N' END AS PDF FROM PS_PAY_CHECK A WHERE A.PAYCHECK_STATUS = 'F' AND A.PAYCHECK_OPTION <> 'R' AND (EXISTS ( SELECT 'X' FROM PS_PY_SS_PSLP_GDE B WHERE A.EMPLID = B.EMPLID AND A.PAYCHECK_NBR = B.PAYCHECK_NBR AND B.PY_SS_PSLP_STATUS = 'ORIG' AND B.PY_PSLP_VIEW_DT <= %CurrentDateIn) OR (NOT EXISTS ( SELECT 'X' FROM PS_PY_SS_PSLP_GDE C WHERE A.EMPLID = C.EMPLID AND A.PAYCHECK_NBR = C.PAYCHECK_NBR AND C.PY_SS_PSLP_STATUS = 'ORIG') AND %DateAdd(A.CHECK_DT, (SELECT %Coalesce(MAX(D.PY_SSP_BGNSSDFLT), 0) FROM PS_PY_SSP_OPTN_DTL D WHERE (D.PY_SSP_SETUP_ID = 'PNACAN' AND A.COUNTRY <> 'USA') OR (D.PY_SSP_SETUP_ID = 'PNAUSA' AND A.COUNTRY = 'USA') AND D.COMPANY = A.COMPANY AND D.PAYGROUP = A.PAYGROUP AND D.EFFDT = (SELECT MAX(E.EFFDT) FROM PS_PY_SSP_OPTN_HDR E WHERE (E.PY_SSP_SETUP_ID = 'PNACAN' AND A.COUNTRY <> 'USA') OR (E.PY_SSP_SETUP_ID = 'PNAUSA' AND A.COUNTRY = 'USA')))) <= %CurrentDateIn))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 PY_LAST_PAY_DT Date(10) DATE Last Pay Date
3 PAYCHECK_OPTION Character(1) VARCHAR2(1) NOT NULL Paycheck Option
&=Check and Advice
A=Advice
C=Check
M=Manual Check
R=Check Reversal
4 NET_PAY Signed Number(12,2) DECIMAL(10,2) NOT NULL Net Pay
5 TOTAL_GROSS Signed Number(12,2) DECIMAL(10,2) NOT NULL Total Gross
6 PY_SSP_PDF_FLG Character(1) VARCHAR2(1) NOT NULL Self Service Paycheck in PDF