PY_WC_STATAX_VW

(SQL View)
 Index Back

State tax audit for WorkCenter

This shows employees with a state on payline not set up for the employee.

SELECT B.COMPANY , B.PAYGROUP , B.PAY_END_DT , B.OFF_CYCLE , B.PAGE_NUM , B.LINE_NUM , B.ADDL_NBR , B.EMPLID , B.EMPL_RCD , E.NAME , B.STATE , B.LOCALITY , B.DEPTID , B.BUSINESS_UNIT , C.COUNTRY , C.INDUSTRY_SECTOR FROM PS_PAY_CALENDAR A , PS_PAY_EARNINGS B , PS_PAYGROUP_TBL C , PS_PY_WC_PAYGRP D , PS_PAY_LINE E , PS_FED_TAX_DATA F WHERE A.COMPANY = D.COMPANY AND A.PAYGROUP = D.PAYGROUP AND D.PY_INCL_PAYGRP = 'Y' AND A.PAY_SHEETS_RUN = 'Y' AND A.PAY_CONFIRM_RUN = 'N' AND B.COMPANY = A.COMPANY AND B.PAYGROUP = A.PAYGROUP AND B.PAY_END_DT = A.PAY_END_DT AND A.PAYGROUP = C.PAYGROUP AND A.COMPANY = C.COMPANY AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_PAYGROUP_TBL C1 WHERE C1.COMPANY = C.COMPANY AND C1.PAYGROUP = C.PAYGROUP AND C1.EFFDT <= A.PAY_END_DT) AND C.COUNTRY <> 'CAN' AND F.EMPLID = B.EMPLID AND F.COMPANY = B.COMPANY AND F.EFFDT = ( SELECT MAX(F1.EFFDT) FROM PS_FED_TAX_DATA F1 WHERE F1.EMPLID = F.EMPLID AND F1.COMPANY = F.COMPANY AND F1.EFFDT <= B.PAY_END_DT ) AND NOT EXISTS ( SELECT 'X' FROM PS_STATE_TAX_DATA WHERE EMPLID = F.EMPLID AND COMPANY = F.COMPANY AND EFFDT = F.EFFDT AND STATE = B.STATE) AND E.COMPANY = A.COMPANY AND E.PAYGROUP = A.PAYGROUP AND E.PAY_END_DT = A.PAY_END_DT AND B.OFF_CYCLE = E.OFF_CYCLE AND B.PAGE_NUM = E.PAGE_NUM AND B.LINE_NUM = E.LINE_NUM AND B.OK_TO_PAY = 'Y' AND B.SINGLE_CHECK_USE IN ('P', 'N')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
2 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
3 PAY_END_DT Date(10) DATE Pay Period End Date
4 OFF_CYCLE Character(1) VARCHAR2(1) NOT NULL Off Cycle ?
5 PAGE_NUM Number(5,0) INTEGER NOT NULL Page Nbr
6 LINE_NUM Number(3,0) SMALLINT NOT NULL Line Nbr
7 ADDL_NBR Number(3,0) SMALLINT NOT NULL Additional Pay Line Nbr
8 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
9 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
10 NAME Character(50) VARCHAR2(50) NOT NULL Name
11 STATE Character(6) VARCHAR2(6) NOT NULL State
12 LOCALITY Character(10) VARCHAR2(10) NOT NULL Locality
13 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
14 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
15 COUNTRY_CD Character(3) VARCHAR2(3) NOT NULL Country Code
16 INDUSTRY_SECTOR Character(4) VARCHAR2(4) NOT NULL Industry Sector
CORE=Core
PBSC=Public Sector
USFG=US Federal Government