PY_MULT_PROV_VW

(SQL View)
 Index Back

Multiple province on Payline

The view captures payline for multiple province code on pay earnings for WorkCenter.

SELECT A.COMPANY , A.PAYGROUP , A.PAY_END_DT , B.OFF_CYCLE , B.PAGE_NUM , B.LINE_NUM , B.JOB_CHANGE , C.ADDL_NBR , B.EMPLID , B.EMPL_RCD , B.NAME , C.STATE , C.DEPTID , C.BUSINESS_UNIT , D.COUNTRY , D.INDUSTRY_SECTOR FROM PS_PAY_CALENDAR A , PS_PAY_LINE B , PS_PAY_EARNINGS C , PS_PAYGROUP_TBL D , PS_PY_WC_PAYGRP E WHERE A.COMPANY = E.COMPANY AND A.PAYGROUP = E.PAYGROUP AND E.PY_INCL_PAYGRP = 'Y' AND A.PAY_SHEETS_RUN = 'Y' AND A.PAY_CONFIRM_RUN = 'N' AND B.COMPANY = A.COMPANY AND B.PAYGROUP = A.PAYGROUP AND B.PAY_END_DT = A.PAY_END_DT AND C.SINGLE_CHECK_USE IN ('P', 'N') AND C.COMPANY = B.COMPANY AND C.PAYGROUP = B.PAYGROUP AND C.PAY_END_DT = B.PAY_END_DT AND C.OFF_CYCLE = B.OFF_CYCLE AND C.PAGE_NUM = B.PAGE_NUM AND C.LINE_NUM = B.LINE_NUM AND C.OK_TO_PAY = 'Y' AND EXISTS ( SELECT STATE FROM PS_PAY_EARNINGS C1 WHERE C1.COMPANY = C.COMPANY AND C1.PAYGROUP = C.PAYGROUP AND C1.PAY_END_DT = C.PAY_END_DT AND C1.OFF_CYCLE = C.OFF_CYCLE AND C1.PAGE_NUM = C.PAGE_NUM AND C1.LINE_NUM = C.LINE_NUM AND C1.EMPLID = C.EMPLID AND C1.OK_TO_PAY = C.OK_TO_PAY AND C1.STATE <> C.STATE) AND A.COMPANY = D.COMPANY AND A.PAYGROUP = D.PAYGROUP AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_PAYGROUP_TBL D1 WHERE D1.COMPANY = D.COMPANY AND D1.PAYGROUP = D.PAYGROUP AND D1.EFFDT <= A.PAY_END_DT) AND D.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 JOB_CHANGE Character(1) VARCHAR2(1) NOT NULL Job Pay Data Change
N=No
R=Calculation Required
Y=Yes
8 ADDL_NBR Number(3,0) SMALLINT NOT NULL Additional Pay Line Nbr
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 STATE Character(6) VARCHAR2(6) NOT NULL State
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