PY_SSP_HIST_VW

(SQL View)
 Index Back

WEB-PayChk Rvw Pay Chk Summary

View 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.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 , D.DESCR , A.BENEFIT_RCD_NBR ,N.DESCR FROM PS_PAY_CHECK A , PS_PAY_EARNINGS E , PSOPRDEFN B , PS_JOB C , PS_JOBCODE_TBL D ,PS_COMPANY_TBL N WHERE A.PAYCHECK_STATUS = 'F' AND A.PAYCHECK_OPTION <> 'R' AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND A.EMPL_RCD=C.EMPL_RCD AND A.COMPANY=E.COMPANY AND A.PAYGROUP=E.PAYGROUP AND A.PAY_END_DT=E.PAY_END_DT AND A.OFF_CYCLE=E.OFF_CYCLE AND A.PAGE_NUM=E.PAGE_NUM AND A.LINE_NUM=E.LINE_NUM AND A.SEPCHK=E.SEPCHK AND A.COMPANY = N.COMPANY 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) AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_COMPANY_TBL N1 WHERE N1.COMPANY = N.COMPANY AND N1.EFFDT <= A.PAY_END_DT)

  • Parent record: PAY_LINE

    • # 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 PAY_END_DT_ALT Date(10) DATE NOT NULL Pay Period End Date

    Prompt Table: PY_IC_PI_DT_VW

    3 PAYCHECK_NBR Number(15,0) DECIMAL(15) NOT NULL Paycheck number.
    4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
    5 NAME Character(50) VARCHAR2(50) NOT NULL Name
    6 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    7 CITY Character(30) VARCHAR2(30) NOT NULL City
    8 STATE Character(6) VARCHAR2(6) NOT NULL State
    9 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    10 SSN Character(9) VARCHAR2(9) NOT NULL Social Security #
    11 SIN Character(9) VARCHAR2(9) NOT NULL Social Insurance #
    12 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    13 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    14 TOTAL_EARNINGS Signed Number(12,2) DECIMAL(10,2) NOT NULL Total Earnings
    15 TOTAL_TAXES Signed Number(12,2) DECIMAL(10,2) NOT NULL Total Taxes
    16 TOTAL_DEDUCTIONS Signed Number(12,2) DECIMAL(10,2) NOT NULL Total Deductions
    17 NET_PAY Signed Number(12,2) DECIMAL(10,2) NOT NULL Net Pay
    18 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    19 COMPANY_ALT Character(3) VARCHAR2(3) NOT NULL Company
    20 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    21 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
    22 OFF_CYCLE Character(1) VARCHAR2(1) NOT NULL Off Cycle ?

    Y/N Table Edit

    23 PAGE_ALT Number(4,0) SMALLINT NOT NULL Page #
    24 LINE_NUM Number(2,0) SMALLINT NOT NULL Line #
    25 SEPCHK Number(1,0) SMALLINT NOT NULL Separate check number.
    26 CHECK_DT Date(10) DATE Check date.
    27 PAYCHECK_STATUS Character(1) VARCHAR2(1) NOT NULL Paycheck status.
    A=Adjusted
    C=Calculated
    F=Confirmed
    R=Reversed
    V=Reversing Check
    28 PAYCHECK_OPTION Character(1) VARCHAR2(1) NOT NULL Paycheck Option
    &=Check and Advice
    A=Advice
    C=Check
    M=Manual Check
    R=Check Reversal
    29 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title
    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