PY_IN_FM_CRS_VW

(SQL View)
 Index Back

Insights W-2c Correction

Insights W-2c Correction Reason View

SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,1 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_YE_FNAME , A.W2C_FNAME , 'First Name Change' ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_YE_FNAME<>B.W2C_FNAME) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,2 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_YE_MIDDLE_NAME , A.W2C_MIDDLE_NAME , 'Middle Name Change' ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_YE_MIDDLE_NAME<>B.W2C_MIDDLE_NAME) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,3 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_YE_LNAME , A.W2C_LNAME , 'Last Name Change' ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_YE_LNAME<>B.W2C_LNAME) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,4 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_YE_SUFFIX , A.W2C_SUFFIX , 'Name Suffix Change' , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_YE_SUFFIX<>B.W2C_SUFFIX) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,5 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_ADDRESS1 , A.ADDRESS1 , 'Address1 Change' , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_ADDRESS1<>B.ADDRESS1) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,6 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_ADDRESS2 , A.ADDRESS2 , 'Address2 Change' , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_ADDRESS2<>B.ADDRESS2) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,7 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_CITY , A.CITY , 'City Change' , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_CITY<>B.CITY) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,8 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_STATE , A.STATE , 'State Change' , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_STATE<>B.STATE) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,9 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_COUNTRY , A.COUNTRY , 'Country Change' , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_COUNTRY<>B.COUNTRY) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,10 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_POSTAL , A.POSTAL , 'Postal Change' , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXL ATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_POSTAL<>B.POSTAL) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,11 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_STATUTORY_EE , A.STATUTORY_EE , 'Statutory Employee' , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_STATUTORY_EE<>B.STATUTORY_EE) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,12 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_PENSION_PLAN , A.PENSION_PLAN , 'Retirement Plan' , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_PENSION_PLAN<>B.PENSION_PLAN) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,13 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_THIRDPARTYSICK , A.THIRDPARTY_SICKPAY , 'Third Party Sick Plan' , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_THIRDPARTYSICK<>B.THIRDPARTY_SICKPAY) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,a.COMPANY ,a.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,a.W2C_BATCH_NBR ,14 ,' ' ,A.PRV_SSN ,A.STATE ,' ' ,A.W2C_TAXFORM_ID ,A.PRV_SSN , A.W2C_SSN , 'SSN Change' , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_DATA A WHERE EXISTS ( SELECT '1' FROM PS_YE_W2C_DATA B WHERE A.W2C_ADD_EMPLID = B.W2C_ADD_EMPLID AND A.COMPANY=B.COMPANY AND A.W2C_BATCH_NBR=B.W2C_BATCH_NBR AND A.CALENDAR_YEAR=B.CALENDAR_YEAR AND A.W2C_TAXFORM_ID=B.W2C_TAXFORM_ID AND (A.PRV_SSN<>B.W2C_SSN) ) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR ) UNION SELECT A.W2C_ADD_EMPLID ,A.COMPANY ,A.CALENDAR_YEAR ,CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ,A.W2C_BATCH_NBR ,1 ,A.BOX ,A.PRV_SSN ,A.STATE ,A.LOCALITY ,A.W2C_TAXFORM_ID ,TO_CHAR(A.PRV_AMOUNT ) ,TO_CHAR(A.CURRENT_AMOUNT) ,B.DESCR , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'TAXFORM_ID' AND X.FIELDVALUE = (CASE WHEN (A.W2C_TAXFORM_ID) = 'W' THEN 'X' WHEN (A.W2C_TAXFORM_ID) = 'I' THEN 'J' WHEN (A.W2C_TAXFORM_ID) = 'M' THEN 'N' WHEN (A.W2C_TAXFORM_ID) = 'P' THEN 'Q' WHEN (A.W2C_TAXFORM_ID) = 'S' THEN 'U' ELSE ' ' END ) AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) , A.COMPANY||' - '|| ( SELECT C1.DESCR FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = A.COMPANY AND %EffdtCheck(COMPANY_TBL C2, C1, %CurrentDateIn)) ,%CurrentDateTimeIn FROM PS_YE_W2C_AMOUNTS A, PS_TAXFORM_BOX B WHERE ( A.W2C_TAXFORM_ID = B.TAXFORM_ID AND A.BOX = B.BOX AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_TAXFORM_BOX B_ED WHERE B.TAXFORM_ID = B_ED.TAXFORM_ID AND B_ED.EFFDT <= %CurrentDateIn)) AND EXISTS ( SELECT 'X' FROM PS_PY_SS_USA_GDE A1 WHERE A.W2C_ADD_EMPLID=A1.EMPLID AND A.COMPANY=A1.W2_REPORTING_CO AND A.CALENDAR_YEAR=A1.PY_TAX_YEAR AND A.W2C_BATCH_NBR=A1.W2C_BATCH_NBR)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 W2_REPORTING_CO Character(3) VARCHAR2(3) NOT NULL W-2 Reporting Company
3 PY_TAX_YEAR Number(4,0) SMALLINT NOT NULL Represents the year for personal income tax purposes.
4 TAXFORM_ID Character(1) VARCHAR2(1) NOT NULL Tax Form Identification
A=T4A Slip
B=Business Payrolls Survey - CAN
C=WCB Assessable Earnings
E=Record of Employment
F=940 - Annual Employer's FUTA
I=W-2VI
J=W-2cVI
M=W-2GU
N=W-2cGU
P=W-2PR
Q=W-2cPR
R=RL-1 Slip
S=W-2AS
T=T4 Slip
U=W-2cAS
V=RL-2 Slip
W=W-2
X=W-2c
Y=1099-R
Z=1042-S
5 W2C_BATCH_NBR Number(4,0) SMALLINT NOT NULL W-2c Batch Number
6 SEQUENCE_NUMBER Number(2,0) SMALLINT NOT NULL Sequence Number
7 BOX Character(4) VARCHAR2(4) NOT NULL Box
8 PRV_SSN Character(9) VARCHAR2(9) NOT NULL or SSN

Default Value: W2C_DATA.PRV_SSN

9 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: W2C_STATE_NM_VW

10 LOCALITY Character(10) VARCHAR2(10) NOT NULL Locality

Prompt Table: W2C_LOCAL_TX_VW

11 W2C_TAXFORM_ID Character(1) VARCHAR2(1) NOT NULL Tax Form Identification
I=W-2VI
M=W-2GU
P=W-2PR
S=W-2AS
W=W-2
12 PREVIOUS_VALUE Character(250) VARCHAR2(250) NOT NULL Previous Value
13 UPDATED_VALUE Character(250) VARCHAR2(250) NOT NULL New Value
14 PY_COR_REASON Character(254) VARCHAR2(254) NOT NULL Correction Reason
15 TAXFORM_ID_DESCR Character(30) VARCHAR2(30) NOT NULL Taxform ID Description
16 PY_W2_CMP_DESCR Character(254) VARCHAR2(254) NOT NULL W-2 company description
17 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.