PY_IC_PI_LST_V1(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 DISTINCT B.OPRID , A.FORM_ID , A.PAY_END_DT , A.PAYCHECK_NBR , A.EMPL_RCD , A.NAME , A.ADDRESS1 , A.CITY , A.STATE , A.POSTAL , A.SSN , A.SIN , 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 , A.BENEFIT_RCD_NBR , N.DESCR , PC.PAY_BEGIN_DT , 'N' , 0 , ' ' FROM PS_PAY_CHECK A , PSOPRDEFN B , PS_COMPANY_TBL N , PS_PAY_CALENDAR PC , PS_PY_SSP_OPTN_DTL OPT , PS_PAYGROUP_TBL PG WHERE A.PAYCHECK_STATUS = 'F' AND A.PAYCHECK_OPTION <> 'R' AND A.EMPLID = B.EMPLID AND A.COMPANY = N.COMPANY AND PG.COMPANY = A.COMPANY AND PG.PAYGROUP = A.PAYGROUP AND PG.EFFDT = ( SELECT MAX(PG1.EFFDT) FROM PS_PAYGROUP_TBL PG1 WHERE PG1.COMPANY = PG.COMPANY AND PG1.PAYGROUP = PG.PAYGROUP AND PG1.EFFDT <= A.PAY_END_DT) AND A.COMPANY = PC.COMPANY AND A.PAYGROUP = PC.PAYGROUP AND A.PAY_END_DT = PC.PAY_END_DT AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_COMPANY_TBL N1 WHERE N1.COMPANY = N.COMPANY AND N1.EFFDT <= A.PAY_END_DT) AND NOT EXISTS ( SELECT 'X' FROM PS_PY_SSP_GDE_VW GVW WHERE GVW.EMPLID = A.EMPLID AND GVW.PAYCHECK_NBR = A.PAYCHECK_NBR) AND ((OPT.PY_SSP_SETUP_ID = 'PNAUSA' AND PG.COUNTRY = 'USA') OR (OPT.PY_SSP_SETUP_ID = 'PNACAN' AND PG.COUNTRY <> 'USA')) AND OPT.COMPANY = A.COMPANY AND ((OPT.PAYGROUP = A.PAYGROUP AND EXISTS ( SELECT 'X' FROM PS_PY_SSP_OPTN_DTL OPT1 WHERE OPT1.PY_SSP_SETUP_ID = OPT.PY_SSP_SETUP_ID AND OPT1.COMPANY = A.COMPANY AND OPT1.PAYGROUP = A.PAYGROUP AND OPT1.EFFDT = ( SELECT MAX(OPTX.EFFDT) FROM PS_PY_SSP_OPTN_DTL OPTX WHERE OPTX.PY_SSP_SETUP_ID = OPT.PY_SSP_SETUP_ID AND OPTX.COMPANY = OPT1.COMPANY AND OPTX.PAYGROUP = OPT1.PAYGROUP AND OPTX.EFFDT <= %CurrentDateIn))) OR (OPT.PAYGROUP = ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_PY_SSP_OPTN_DTL OPT2 WHERE OPT2.PY_SSP_SETUP_ID = OPT.PY_SSP_SETUP_ID AND OPT2.COMPANY = A.COMPANY AND OPT2.PAYGROUP = A.PAYGROUP))) AND %DateAdd(A.CHECK_DT, OPT.PY_SSP_BGNSSDFLT) <= %CurrentDateIn |
| # | 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 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 9 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 10 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 11 | SSN | Character(9) | VARCHAR2(9) NOT NULL | Social Security # |
| 12 | SIN | Character(9) | VARCHAR2(9) NOT NULL | Social Insurance Nbr |
| 13 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 14 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 15 | TOTAL_EARNINGS | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Total Earnings |
| 16 | TOTAL_TAXES | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Total Taxes |
| 17 | TOTAL_DEDUCTIONS | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Total Deductions |
| 18 | NET_PAY | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Net Pay |
| 19 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 20 | COMPANY_ALT |
Character(3) | VARCHAR2(3) NOT NULL | Company |
| 21 | COMPANY |
Character(3) | VARCHAR2(3) NOT NULL | Company |
| 22 | PAYGROUP |
Character(3) | VARCHAR2(3) NOT NULL | Pay Group |
| 23 | OFF_CYCLE |
Character(1) | VARCHAR2(1) NOT NULL |
Off Cycle ?
Y/N Table Edit |
| 24 | PAGE_ALT |
Number(5,0) | INTEGER NOT NULL | Page # |
| 25 | LINE_NUM |
Number(3,0) | SMALLINT NOT NULL | Line Nbr |
| 26 | SEPCHK |
Number(2,0) | SMALLINT NOT NULL | Separate check number. |
| 27 | CHECK_DT | Date(10) | DATE | Check date. |
| 28 | PAYCHECK_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Paycheck status.
A=Adjusted C=Calculated F=Confirmed R=Reversed V=Reversing Check |
| 29 | PAYCHECK_OPTION | Character(1) | VARCHAR2(1) NOT NULL |
Paycheck Option
&=Check and Advice A=Advice C=Check M=Manual Check R=Check Reversal |
| 30 | 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. |
| 31 | COMPANY_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Company Descr |
| 32 | PAY_BEGIN_DT | Date(10) | DATE | Pay Period Begin Date |
| 33 | PY_SSP_PDF_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Self Service Paycheck in PDF
Y/N Table Edit |
| 34 | PY_SSP_PROC_INSTNC | Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |
| 35 | PY_SSP_BURST_VALUE | Character(40) | VARCHAR2(40) NOT NULL | This field can be use to identify individual self service paychecks |