PY_MOD_PYTX_VW1

(SQL View)
 Index Back

Paycheck Modeling Tax

This record contains paycheck modeling state tax results

SELECT A.PY_MOD_OPRID , A.PY_MOD_EMPLID , A.COMPANY , A.PAYGROUP , A.PAY_END_DT , A.OFF_CYCLE , A.PAGE_NUM , A.STATE , A.LOCALITY , A.TAX_CLASS , S.DESCR , SUM(A.TAX_CUR) FROM PS_MODEL_PAY_TAX A , PS_STATE_TBL S WHERE A.TAX_CUR <> 0 AND ((A.LOCALITY = ' ' AND A.STATE <> '$U') OR (A.LOCALITY = ' ' AND A.STATE = '$U' AND NOT EXISTS ( SELECT 'Y' FROM PS_MODEL_PAY_TAX B WHERE B.PY_MOD_OPRID = A.PY_MOD_OPRID AND B.PY_MOD_EMPLID = A.PY_MOD_EMPLID AND B.COMPANY = A.COMPANY AND B.PAYGROUP = A.PAYGROUP AND B.PAY_END_DT = A.PAY_END_DT AND B.OFF_CYCLE = A.OFF_CYCLE AND B.PAGE_NUM = A.PAGE_NUM AND B.LINE_NUM = A.LINE_NUM AND B.SEPCHK = A.SEPCHK AND B.STATE IN ('AS','GU','PR','VI')))) AND ((A.TAX_CLASS IN ('1','3','5','7', 'A','AA','AC','AE','D','F','G','H','I','L','M','N','O','T','V','W')) OR (A.TAX_CLASS IN ( SELECT AB.TAX_CLASS FROM PS_PY_PFF_STPG_DTL AA , PS_PY_PFF_STPG_CHD AB , PS_PY_PFF_PGID_CHD AC WHERE AA.STATUS='A' AND AC.PY_PFF_TAX_CAT = 'E' AND AB.STATE = A.STATE AND AA.EFFDT=( SELECT MAX(AB1.EFFDT) FROM PS_PY_PFF_STPG_DTL AB1 WHERE AA.STATE=AB1.STATE AND AA.PY_PFF_PROGID=AB1.PY_PFF_PROGID AND AB1.EFFDT <= %CurrentDateIn ) AND AA.EFFSEQ=( SELECT MAX(AB2.EFFSEQ) FROM PS_PY_PFF_STPG_DTL AB2 WHERE AB2.STATE=AA.STATE AND AB2.PY_PFF_PROGID=AA.PY_PFF_PROGID AND AB2.EFFDT=AA.EFFDT AND AA.EFFDT <= %CurrentDateIn ) AND AA.STATE=AB.STATE AND AA.PY_PFF_PROGID=AB.PY_PFF_PROGID AND AA.EFFDT=AB.EFFDT AND AA.EFFSEQ=AB.EFFSEQ AND AA.PY_PFF_PROGID=AC.PY_PFF_PROGID AND AC.EFFDT=( SELECT MAX(AC1.EFFDT) FROM PS_PY_PFF_PGID_CHD AC1 WHERE AC.PY_PFF_PROGID=AC1.PY_PFF_PROGID AND AC.TAX_CLASS=AC1.TAX_CLASS AND AC1.EFFDT <= %CurrentDateIn ) AND AC.EFFSEQ=( SELECT MAX(AC2.EFFSEQ) FROM PS_PY_PFF_PGID_CHD AC2 WHERE AC.PY_PFF_PROGID=AC2.PY_PFF_PROGID AND AC.TAX_CLASS=AC2.TAX_CLASS AND AC2.EFFDT=AC.EFFDT AND AC2.EFFDT <= %CurrentDateIn ) AND AB.TAX_CLASS=AC.TAX_CLASS ))) AND A.STATE = S.STATE AND S.COUNTRY = 'USA' GROUP BY A.PY_MOD_OPRID, A.PY_MOD_EMPLID, A.COMPANY, A.PAYGROUP, A.PAY_END_DT, A.OFF_CYCLE, A.PAGE_NUM, A.STATE, A.LOCALITY, A.TAX_CLASS, S.DESCR

  • Parent record: PAY_CHECK

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 PY_MOD_OPRID Character(30) VARCHAR2(30) NOT NULL PayCheck Modeler User Id
    2 PY_MOD_EMPLID Character(11) VARCHAR2(11) NOT NULL PayCheck Modeler Employee ID
    3 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    4 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group

    Prompt Table: PAYGROUP_TBL

    5 PAY_END_DT Date(10) DATE NOT NULL Pay Period End Date

    Prompt Table: PAY_CALENDAR

    6 OFF_CYCLE Character(1) VARCHAR2(1) NOT NULL Off Cycle ?

    Y/N Table Edit

    Default Value: Y

    7 PAGE_NUM Number(5,0) INTEGER NOT NULL Page Nbr
    8 STATE Character(6) VARCHAR2(6) NOT NULL State
    9 LOCALITY Character(10) VARCHAR2(10) NOT NULL Locality
    10 TAX_CLASS Character(3) VARCHAR2(3) NOT NULL Tax Balance Class

    Prompt Table: PY_TAX_CLASS

    11 PY_MOD_DESCR60_LBL Character(75) VARCHAR2(75) NOT NULL Tax Jurisdiction
    12 TAX_CUR Signed Number(12,2) DECIMAL(10,2) NOT NULL Current Tax