PYAP_XCTAX_CAVW

(SQL View)
 Index Back

Unprocessed CA Canadian Taxes

View to find any unprocessed Commitment Accounting Canadian Tax deduction transactions

SELECT DISTINCT A.COMPANY , A.PAYGROUP , A.PAY_END_DT , E.RUN_ID FROM PS_PAY_TAX_CAN A , PS_PAY_CHECK B , PS_SET_CNTRL_REC C , PS_PAY_CALENDAR E WHERE A.AP_STATUS = ' ' 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.PAYCHECK_STATUS = 'F' AND C.SETCNTRLVALUE = B.BUSINESS_UNIT AND C.REC_GROUP_ID = 'HR_01' AND C.RECNAME = 'DEPT_TBL' AND E.COMPANY = A.COMPANY AND E.PAYGROUP = A.PAYGROUP AND E.PAY_END_DT = A.PAY_END_DT AND E.CA_GL_INTFC_RUN = 'N' AND E.PAY_CONFIRM_RUN = 'Y' AND EXISTS ( SELECT 'X' FROM PS_DEPT_TBL D WHERE D.SETID = C.SETID AND D.DEPTID = B.DEPTID AND D.EFFDT=( SELECT MAX(D2.EFFDT) FROM PS_DEPT_TBL D2 WHERE D2.SETID = D.SETID AND D2.DEPTID = D.DEPTID AND D2.EFF_STATUS = 'A' AND D2.EFFDT <= B.PAY_END_DT) AND D.USE_BUDGETS = 'Y')

# 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 RUN_ID Character(10) VARCHAR2(10) NOT NULL Pay Run ID