PYKB_CN_RET_VW2

(SQL View)
 Index Back

Payroll Analytics CN Oth Earns

Payroll Analytics Canada Other Earnings View

SELECT A.COMPANY ,A.PAYGROUP ,A.PAY_END_DT ,A.OFF_CYCLE ,A.PAGE_NUM ,A.LINE_NUM ,A.SEPCHK ,B.ADDL_NBR ,'O' ,C.PY_CATEGORY_ID AS PY_CAT_ID ,B.ERNCD ,A.CHECK_DT ,A.BUSINESS_UNIT ,A.DEPTID ,A.EMPLID ,A.EMPL_RCD ,A.EMPL_TYPE ,A.LOCATION ,'Earnings' ,C.DESCR AS PY_CAT_ID_DESCR ,SUM(B.AMOUNT_DIFF) AS PY_RETRO_AMOUNT ,A.OFF_CYCLE AS PY_OFF_CYCLE_CAL FROM PS_PAY_CHECK A ,PS_RETROPAY_EARNS B ,PS_PY_CTGYSEL_TBL C WHERE A.COMPANY = B.COMPANY AND A.PAYGROUP = B.PAYGROUP AND A.PAY_END_DT = B.PAY_END_DT AND A.OFF_CYCLE = B.OFF_CYCLE AND A.PAGE_NUM = B.PAGE_NUM AND A.LINE_NUM = B.LINE_NUM AND A.SEPCHK = B.SEPCHK AND A.PAYCHECK_STATUS = 'F' AND A.COUNTRY = 'CAN' AND B.EARNS_AMT = 0 AND B.AMOUNT_DIFF <> 0 AND B.RETROPAY_PRCS_FLAG = 'F' AND C.PY_CATGRY_TYPE = 'E' AND C.COUNTRY = A.COUNTRY AND C.PY_CTGRY_USED_BY = 'K' AND C.PY_DATA_TYPE_CD IN ('Y','N') AND EXISTS ( SELECT 'X' FROM PS_PY_CAT_CODE_TBL D WHERE D.PY_CATGRY_TYPE = C.PY_CATGRY_TYPE AND D.COUNTRY = C.COUNTRY AND D.PY_CTGRY_USED_BY = C.PY_CTGRY_USED_BY AND D.PY_CATEGORY_ID = C.PY_CATEGORY_ID AND D.CODE_SEL = B.ERNCD) GROUP BY A.COMPANY ,A.PAYGROUP ,A.PAY_END_DT, A.OFF_CYCLE, A.PAGE_NUM, A.LINE_NUM, A.SEPCHK, B.ADDL_NBR, B.ERNCD, A.CHECK_DT, A.BUSINESS_UNIT, A.DEPTID, A.EMPLID, A.EMPL_RCD, A.EMPL_TYPE, A.LOCATION, C.PY_CATEGORY_ID, C.DESCR

# 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 ADDL_NBR Number(3,0) SMALLINT NOT NULL Additional Pay Line Nbr
9 PY_CAT_TYPE Character(1) VARCHAR2(1) NOT NULL Category Type
D=Deductions
E=Earnings
O=Other Earnings
T=Taxes
V=Overtime
10 PY_CAT_ID Character(6) VARCHAR2(6) NOT NULL Category ID
11 PY_ERNCD Character(3) VARCHAR2(3) NOT NULL Earnings Code
12 CHECK_DT Date(10) DATE Check date.
13 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
15 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
16 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
17 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried
18 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
19 PY_CAT_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Category Type Description
20 PY_CAT_ID_DESCR Character(30) VARCHAR2(30) NOT NULL Category ID Description
21 PY_RETRO_AMOUNT Signed Number(19,2) DECIMAL(17,2) NOT NULL Retro Amount
22 PY_OFF_CYCLE_CAL Character(1) VARCHAR2(1) NOT NULL Off-Cycle Calendar