PY_IC_PI_SUM_VW(SQL View) |
Index Back |
|---|---|
WEB-PayChk Rvw Pay Chk SummaryView created to support WEB-enabled Paycheck Review. It is based on the PAY_CHECK record. It contains the summary of the result of the payroll confirmation process. |
| SELECT B.OPRID , A.FORM_ID , A.PAY_END_DT , A.PAYCHECK_NBR , A.EMPL_RCD , A.NAME , A.ADDRESS1 , A.ADDRESS2 , A.CITY , A.STATE , A.POSTAL , A.SSN , A.SIN , C.SETID_JOBCODE , C.SETID_LOCATION , C.SETID_DEPT , A.DEPTID , A.LOCATION , A.TOTAL_GROSS , A.TOTAL_TAXES , A.TOTAL_DEDUCTIONS , A.NET_PAY , A.EMPLID , A.COMPANY , A.COMPANY , A.PAYGROUP , A.OFF_CYCLE , A.PAGE_NUM , A.LINE_NUM , A.SEPCHK , A.CHECK_DT , A.PAYCHECK_STATUS , A.PAYCHECK_OPTION , D.DESCR , A.BENEFIT_RCD_NBR , A.PAYCHECK_NAME , A.BUSINESS_UNIT , A.UPDATE_DT , C.JOBCODE FROM PS_PAY_CHECK A , PSOPRDEFN B , PS_JOB C , PS_JOBCODE_TBL D WHERE A.PAYCHECK_STATUS = 'F' AND A.PAYCHECK_OPTION <> 'R' AND A.EMPLID = B.EMPLID AND B.EMPLID = C.EMPLID AND A.EMPL_RCD=C.EMPL_RCD AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_JOB C1 WHERE C1.EMPLID = C.EMPLID AND C1.EMPL_RCD=C.EMPL_RCD AND C1.EFFDT <= A.PAY_END_DT) AND C.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = C.EMPLID AND J1.EMPL_RCD = C.EMPL_RCD AND J1.EFFDT = C.EFFDT) AND C.JOBCODE = D.JOBCODE AND C.SETID_JOBCODE = D.SETID AND D.EFFDT = ( SELECT MAX(T1.EFFDT) FROM PS_JOBCODE_TBL T1 WHERE T1.SETID = D.SETID AND T1.JOBCODE = D.JOBCODE AND T1.EFFDT <= A.PAY_END_DT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | FORM_ID | Character(6) | VARCHAR2(6) NOT NULL | Form Identification |
| 3 | PAY_END_DT_ALT |
Date(10) | DATE NOT NULL |
Pay Period End Date
Prompt Table: PY_IC_PI_DT_VW |
| 4 | PAYCHECK_NBR | Number(15,0) | DECIMAL(15) NOT NULL | Paycheck number. |
| 5 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 6 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 7 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 8 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 9 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 10 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 11 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 12 | SSN | Character(9) | VARCHAR2(9) NOT NULL | Social Security # |
| 13 | SIN | Character(9) | VARCHAR2(9) NOT NULL | Social Insurance Nbr |
| 14 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code Set ID |
| 15 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location Set ID |
| 16 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 17 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 18 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 19 | TOTAL_EARNINGS | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Total Earnings |
| 20 | TOTAL_TAXES | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Total Taxes |
| 21 | TOTAL_DEDUCTIONS | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Total Deductions |
| 22 | NET_PAY | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Net Pay |
| 23 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 24 | COMPANY_ALT |
Character(3) | VARCHAR2(3) NOT NULL | Company |
| 25 | COMPANY |
Character(3) | VARCHAR2(3) NOT NULL | Company |
| 26 | PAYGROUP |
Character(3) | VARCHAR2(3) NOT NULL | Pay Group |
| 27 | OFF_CYCLE |
Character(1) | VARCHAR2(1) NOT NULL |
Off Cycle ?
Y/N Table Edit |
| 28 | PAGE_ALT |
Number(5,0) | INTEGER NOT NULL | Page # |
| 29 | LINE_NUM |
Number(3,0) | SMALLINT NOT NULL | Line Nbr |
| 30 | SEPCHK |
Number(2,0) | SMALLINT NOT NULL | Separate check number. |
| 31 | CHECK_DT | Date(10) | DATE | Check date. |
| 32 | PAYCHECK_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Paycheck status.
A=Adjusted C=Calculated F=Confirmed R=Reversed V=Reversing Check |
| 33 | PAYCHECK_OPTION | Character(1) | VARCHAR2(1) NOT NULL |
Paycheck Option
&=Check and Advice A=Advice C=Check M=Manual Check R=Check Reversal |
| 34 | JOBTITLE | Character(30) | VARCHAR2(30) NOT NULL | Job Title |
| 35 | BENEFIT_RCD_NBR | Number(3,0) | SMALLINT NOT NULL | Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number. |
| 36 | PAYCHECK_NAME | Character(50) | VARCHAR2(50) NOT NULL | Paycheck Name |
| 37 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 38 | UPDATE_DT | Date(10) | DATE | Last Updated |
| 39 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |