PY_YE_IN_CON_VW

(SQL View)
 Index Back

Insights Consent Details View

Insights Consent Details View

SELECT DISTINCT TB1.EMPLID , CASE WHEN ( SELECT B.YE_CONSENT_CURRENT FROM PS_PY_YE_CONS_USA B WHERE TB1.EMPLID=B.EMPLID AND B.LASTUPDDTTM = ( SELECT MAX(B1.LASTUPDDTTM) FROM PS_PY_YE_CONS_USA B1 WHERE B.EMPLID=B1.EMPLID)) = 'C' THEN 'C' WHEN ( SELECT B.YE_CONSENT_CURRENT FROM PS_PY_YE_CONS_USA B WHERE TB1.EMPLID=B.EMPLID AND B.LASTUPDDTTM = ( SELECT MAX(B1.LASTUPDDTTM) FROM PS_PY_YE_CONS_USA B1 WHERE B.EMPLID=B1.EMPLID)) = 'W' THEN 'W' WHEN ( SELECT B.YE_CONSENT_CURRENT FROM PS_PY_YE_CONS_USA B WHERE TB1.EMPLID=B.EMPLID AND B.LASTUPDDTTM = ( SELECT MAX(B1.LASTUPDDTTM) FROM PS_PY_YE_CONS_USA B1 WHERE B.EMPLID=B1.EMPLID)) = 'R' THEN 'R' ELSE 'N' END ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'YE_CONSENT_CURRENT' AND X.FIELDVALUE = ( CASE WHEN ( SELECT B.YE_CONSENT_CURRENT FROM PS_PY_YE_CONS_USA B WHERE TB1.EMPLID=B.EMPLID AND B.LASTUPDDTTM = ( SELECT MAX(B1.LASTUPDDTTM) FROM PS_PY_YE_CONS_USA B1 WHERE B.EMPLID=B1.EMPLID)) = 'C' THEN 'C' WHEN ( SELECT B.YE_CONSENT_CURRENT FROM PS_PY_YE_CONS_USA B WHERE TB1.EMPLID=B.EMPLID AND B.LASTUPDDTTM = ( SELECT MAX(B1.LASTUPDDTTM) FROM PS_PY_YE_CONS_USA B1 WHERE B.EMPLID=B1.EMPLID)) = 'W' THEN 'W' WHEN ( SELECT B.YE_CONSENT_CURRENT FROM PS_PY_YE_CONS_USA B WHERE TB1.EMPLID=B.EMPLID AND B.LASTUPDDTTM = ( SELECT MAX(B1.LASTUPDDTTM) FROM PS_PY_YE_CONS_USA B1 WHERE B.EMPLID=B1.EMPLID)) = 'R' THEN 'R' ELSE 'N' END) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_TAX_BALANCE TB1, PS_TAXFORM_TAX T1, PS_W2_COMPANY C1 WHERE C1.W2_REPORTING_CO IN ( SELECT A.W2_REPORTING_CO FROM PS_W2_COMPANY A WHERE A.CALENDAR_YEAR = ( SELECT BALANCE_YEAR FROM PS_TAXRPT_RUNCTL) AND A.W2_REPORTING_CO = A.COMPANY AND A.W2_REPORTING_CO <> ' ') AND C1.CALENDAR_YEAR = ( SELECT BALANCE_YEAR FROM PS_TAXRPT_RUNCTL) AND T1.TAXFORM_ID IN ('W' ,'I' ,'M' ,'P' ,'S') AND T1.EFFDT = ( SELECT MAX(TX1.EFFDT) FROM PS_TAXFORM_TBL TX1 WHERE TX1.TAXFORM_ID = T1.TAXFORM_ID AND TX1.EFFDT <= %CurrentDateIn) AND (T1.TAX_CLASS IN ('H','D','E','F','G','J','Q','T','Z','C','K','V','L','M','N','W','I','O','8','9','7','5','3','8A','9A','AA','AC') OR T1.TAX_CLASS IN ( SELECT T21.TAX_CLASS FROM PS_TAXFORM_TAX T21 WHERE T21.TAXFORM_ID = T1.TAXFORM_ID AND T21.EFFDT = T1.EFFDT AND T21.BOX = T1.BOX AND T21.JURISDICTION_TYPE = T1.JURISDICTION_TYPE AND T21.STATE = T1.STATE AND T21.LOCALITY = T1.LOCALITY AND (T21.BOX BETWEEN '14PA' AND '14PZ' OR T21.BOX BETWEEN '14QA' AND '14QZ' OR T21.BOX BETWEEN '14RA' AND '14RZ' OR T21.BOX BETWEEN '14SA' AND '14SZ' OR T21.BOX BETWEEN '14TA' AND '14TZ' OR T21.BOX BETWEEN '14UA' AND '14UN'))) AND TB1.COMPANY = C1.COMPANY AND TB1.BALANCE_ID = ( SELECT BALANCE_ID FROM PS_TAXRPT_RUNCTL) AND TB1.BALANCE_YEAR = ( SELECT BALANCE_YEAR FROM PS_TAXRPT_RUNCTL) AND T1.TAX_CLASS = TB1.TAX_CLASS AND ( (T1.JURISDICTION_TYPE = 'F' AND TB1.STATE LIKE '$U%') OR ( (T1.JURISDICTION_TYPE = 'S' AND TB1.STATE <> '$U' AND TB1.LOCALITY = ' ') AND (TB1.STATE = T1.STATE OR T1.STATE = ' ') ) OR ( (T1.JURISDICTION_TYPE = 'L' AND TB1.STATE <> '$U' AND TB1.LOCALITY <> ' ') AND (TB1.STATE = T1.STATE OR T1.STATE = ' ') AND (TB1.LOCALITY = T1.LOCALITY OR T1.LOCALITY = ' ') ) ) AND TB1.BALANCE_PERIOD = ( SELECT MAX(BALANCE_PERIOD) FROM PS_TAX_BALANCE WHERE EMPLID = TB1.EMPLID AND COMPANY = TB1.COMPANY AND BALANCE_YEAR = TB1.BALANCE_YEAR AND BALANCE_ID = TB1.BALANCE_ID AND STATE = TB1.STATE AND LOCALITY = TB1.LOCALITY AND WORK_PSD_CD = TB1.WORK_PSD_CD AND RES_PSD_CD = TB1.RES_PSD_CD AND TAX_CLASS = TB1.TAX_CLASS) AND ( (TB1.TXGRS_YTD <> 0 OR TB1.TAX_YTD <> 0) OR (TB1.TXGRS_YTD = 0 AND TB1.STATE = 'WI' AND TB1.TAX_CLASS = 'H' AND TB1.BALANCE_YEAR >= 2017 AND T1.BOX = '16') ) UNION SELECT DISTINCT A.EMPLID ,A.YE_CONSENT_CURRENT ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'YE_CONSENT_CURRENT' AND X.FIELDVALUE = A.YE_CONSENT_CURRENT AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , %CurrentDateTimeIn FROM PS_PY_YE_CONS_USA A WHERE A.LASTUPDDTTM = ( SELECT MAX(A1.LASTUPDDTTM) FROM PS_PY_YE_CONS_USA A1 WHERE A.EMPLID=A1.EMPLID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 YE_CONSENT_CURRENT Character(1) VARCHAR2(1) NOT NULL View Year-End Form Self-Service. This field will store the employees consent actions.
C=Consent Received
N=No Consent Received
R=Consent Reset by Employer
W=Consent Withdrawn
3 PY_CONSENT_DESCR Character(254) VARCHAR2(254) NOT NULL Consent Status
4 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.