PYKB_LOCTAX_VW

(SQL View)
 Index Back

Local tax audit for Insights

This shows payline with locality which is different from locality set up for an employee.

SELECT B.COMPANY , B.PAYGROUP , B.PAY_END_DT , B.OFF_CYCLE , B.PAGE_NUM , B.LINE_NUM , B.ADDL_NBR , B.EMPLID , D.EMPL_RCD , D.NAME , B.STATE , B.LOCALITY , B.DEPTID , B.BUSINESS_UNIT , E.COUNTRY , E.INDUSTRY_SECTOR , A.PAY_BEGIN_DT FROM PS_PAY_CALENDAR A , PS_PAY_EARNINGS B , PS_PY_WC_PAYGRP C , PS_PAY_LINE D , PS_PAYGROUP_TBL E , PS_FED_TAX_DATA F WHERE A.COMPANY = C.COMPANY AND A.PAYGROUP = C.PAYGROUP AND C.PY_INCL_PAYGRP = 'Y' AND A.PAY_SHEETS_RUN = 'Y' AND A.PAY_CONFIRM_RUN = 'N' AND A.PAY_OFF_CYCLE_CAL = 'N' AND B.COMPANY = A.COMPANY AND B.PAYGROUP = A.PAYGROUP AND B.PAY_END_DT = A.PAY_END_DT AND B.LOCALITY <> ' ' 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_LOCAL_TAX_DATA WHERE EMPLID = F.EMPLID AND COMPANY = F.COMPANY AND EFFDT = F.EFFDT AND STATE = B.STATE AND LOCALITY = B.LOCALITY ) AND D.COMPANY = B.COMPANY AND D.PAYGROUP = B.PAYGROUP AND D.PAY_END_DT = B.PAY_END_DT AND D.OFF_CYCLE = B.OFF_CYCLE AND D.PAGE_NUM = B.PAGE_NUM AND D.LINE_NUM = B.LINE_NUM AND B.OK_TO_PAY ='Y' AND B.SINGLE_CHECK_USE IN ('P', 'N') AND A.COMPANY = E.COMPANY AND A.PAYGROUP = E.PAYGROUP AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_PAYGROUP_TBL E1 WHERE E1.COMPANY = E.COMPANY AND E1.PAYGROUP = E.PAYGROUP AND E1.EFFDT <= A.PAY_END_DT) AND E.COUNTRY <> 'CAN'

# 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
17 PAY_BEGIN_DT Date(10) DATE Pay Period Begin Date