PYKB_UNCFCHK_VW

(SQL View)
 Index Back

Unconfirmed Checks Prior Kiban

This view shows the unconfirmed paycheck for employees from a prior pay run.

SELECT D.COMPANY , D.PAYGROUP , D.PAY_END_DT , D.OFF_CYCLE , D.PAGE_NUM , D.LINE_NUM , E.SEPCHK , E.PAYCHECK_STATUS , D.EMPLID , D.EMPL_RCD , D.NAME , E.DEPTID , D.BUSINESS_UNIT , Z.RUN_ID , B.COUNTRY , B.INDUSTRY_SECTOR , A.PAY_BEGIN_DT FROM PS_PAY_CALENDAR A , PS_PAY_CALENDAR Z , PS_PAYGROUP_TBL B , PS_PY_WC_PAYGRP C , PS_PAY_LINE D , PS_PAY_CHECK E WHERE A.COMPANY = C.COMPANY AND A.PAYGROUP = C.PAYGROUP AND C.PY_INCL_PAYGRP = 'Y' AND A.PAY_END_DT = ( SELECT MAX(A1.PAY_END_DT) FROM PS_PAY_CALENDAR A1 WHERE A1.COMPANY = A.COMPANY AND A1.PAYGROUP = A.PAYGROUP AND A1.PAY_OFF_CYCLE_CAL = 'N' AND (A1.PAY_PRECALC_RUN = 'Y' OR A1.PAY_CALC_RUN = 'Y') AND A1.PAY_CONFIRM_RUN = 'N') AND Z.COMPANY = A.COMPANY AND Z.PAYGROUP = A.PAYGROUP AND Z.PAY_END_DT < A.PAY_END_DT AND Z.RUN_ID <> ' ' AND Z.PAY_CONFIRM_RUN = 'N' AND Z.PAY_OFF_CYCLE_CAL = 'N' AND D.COMPANY = Z.COMPANY AND D.PAYGROUP = Z.PAYGROUP AND D.PAY_END_DT = Z.PAY_END_DT AND D.CONFIRMED = 'N' AND D.COMPANY = E.COMPANY AND D.PAYGROUP = E.PAYGROUP AND D.PAY_END_DT = E.PAY_END_DT AND D.OFF_CYCLE = E.OFF_CYCLE AND D.PAGE_NUM = E.PAGE_NUM AND D.LINE_NUM = E.LINE_NUM AND Z.COMPANY = B.COMPANY AND Z.PAYGROUP = B.PAYGROUP AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_PAYGROUP_TBL B1 WHERE B1.COMPANY = B.COMPANY AND B1.PAYGROUP = B.PAYGROUP AND B1.EFFDT <= Z.PAY_END_DT)

# 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 SEPCHK Number(2,0) SMALLINT NOT NULL Separate check number.
8 PAYCHECK_STATUS Character(1) VARCHAR2(1) NOT NULL Paycheck status.
A=Adjusted
C=Calculated
F=Confirmed
R=Reversed
V=Reversing Check
9 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
10 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
11 NAME Character(50) VARCHAR2(50) NOT NULL Name
12 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
13 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
14 RUN_ID Character(10) VARCHAR2(10) NOT NULL Pay Run ID
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