Transcript Slide 1
Navigating Human Resources Data Files! Presented by: Mark McDonald – OA Solutions Tuesday July 14th, 2009 - 12:40am to 1:30am (Room U013) Basic Overview of where and how colleague stores data. A great session for HR/Payroll, support and Institutional Research Staff. • Data Storage Basics • Roadmap – Payroll History / Transactions – Payroll Accumulators – Human Resources • Data Storage Basics – The information that you enter on screens is saved to a database. – Currently supported databases are • Unibasic • SQL Server and Oracle – Actual layout is slightly different depending on database • Unibasic Data Storage – Filenames and fieldnames use a period – There can be multiple values stored in one field (Multivalued Database). • SQL Data Storage – Filenames use Underscores – Unibasic multivalues become subfiles pointing to the main file Payroll History / Transactions PAYTODAT (from beginning of colleague) - Primary transactional payroll history file. - Determine how a person was paid on a specific paycheck. - Position / Earntypes / Benefits / Taxes… • PAYTODAT – All transactions are in here – BIG file – Multipart Key (next slide) – Multivalued Associations for • • • • • Earnings PTDEARN PTDWEEK PTDDRDEP Taxes PTDTAXES PTDTAXEXP PTDTXBLBD Deductions PTDBNDED PTDBDEXP PTDDEFBD Leave PTDLEAVE PTDLVTKN PTDLVACC Posting PTDTXPST • PAYTODAT (Key) (*delimited) – – – – PTD.PERIOD.DATE PTD.PAY.CYCLE PTD.EMPLOYEE.ID PTD.SEQ.NO • Sequence Number is used if multiple transactions for the same payroll (eg. a reversal and correction). • Sequence ‘null’ points to the last Seq. used, but has no data in it. (field #1, PTD.FUND.SOURCES is the pointer with the last seq # used) • PAYPRDS – – – – The working version of PAYTODAT Used while payroll is being processed Copied to PAYTODAT at EHUP stage Fields all the same except prefix (PPD rather than PTD) – Key • • • • PPD.PERIOD.DATE PPD.PAY.CYCLE PPD.EMPLOYEE.ID PPD.RECORD.TYPE (rather than sequence) • PPD.RECORD.TYPE (part of key) – P = standard record – Mn = Manual check (n is sequential#) – R = Reversal – D = check modeling – A = Adjustment (EPAJ) – E = used for payroll encumbrances – T = used for total compensation (ptd.adj.comments) • PR.ADVICES – – – – – Key PAD.ADVICE.NO*PAD.BANK.CODE PAD.ADVICE.DATE PAD.ADVICE.AMOUNT PAD.EMPLOYEE.ID PAD.WEB.PAY.ADIVCES.ID (pointer) • WEB.PAY.ADVICES – – – – Key WEB.PAY.ADVICES.ID WPA.EARN.HRS .EARN.TYPES .EARN.PAYMENTS WPA.TOTAL.TAXES WPA.TOTAL.BENDEDS • PAYCNTRL – Holds data from PPPR (period initialization) like period start/end dates / days worked / cutoff date… – Keeps track of what steps have been run • PCL.LAST.PROGRAM • PCL.CURR.PROGRAM • PCL.TIME.ENTRY • PCL.LAST.PROGRAM (use MTPP) – 1 = Payroll prep has begun – 2 = CALC completed – 3 = APAD (checks and advices) • PLC.LAST.PROGRAM = 3I while running (not accepted) • PCL.CURR.PROGRAM = 3I while running – 4 = PSRP, Payroll information has been posted – 5 = EHUP, written to PAYTODAT – 9 = Manual cheques are being processed • Roadmap – Payroll History / Transactions –Payroll Accumulators – Human Resources • EMPLOYES file (yes it is spelled wrong) – Holds accumulators for things like • earn codes/amounts/hrs taxes, bendeds • Links to PERTAXCR (Canadian TD1s) and holds W2 exemption info. • Accumulates by QTD, YTD, FYR • Fields like: – – – – EMP.EARN.QTD.HOURS EMP.EARN.YTD.HOURS EMP.EARN.FYR.HOURS EMP.EARN.QTD.AMTS… • Link: EMP.WEB.PAY.ADVICES.IDS • Files related to EMPLOYES – Copy from EMPLOYES to BKUP then to others – filename.DTL (record per business number [Canadian]) – (CYEB) EMPBKUP (& .DTL) year end forms T4/W2 – Record per employee /quarter or year • Quarterly reporting EMPBKUP.QTLY (& .DTL.QTLY) • Calendar year EMPCYEAR (&.DTL) • Fiscal Year EMPFYEAR (&.DTL) • Roadmap – Payroll History / Transactions – Payroll Accumulators – Human Resources • Human Resources files (a sampling) – HRPER (HR Person) Person Centric switchboard for most other files. • Links to persons statuses positions, Benefits, leave plans… – PERSTAT • Person Status • Contains Primary Postion • POSITION RELATED – PERPOS (Person’s Position) • PERPOSWG (time specific) – STIPEND is an alternate view • POSITION (non person specific) – POSPAY (time specific) • BENEFIT AND DEDUCTIONS – PERBEN • PERBENCS • BENDED – BENDEDCS • Leave Plan related – PERLEAVE • PERLVACC (accrual record / time specific) • PERLVDTL (leave transactions) – LEAVPLAN (NON PERSON SPECIFIC) • LVPLNACC (date specific) Navigating Human Resources Data Files! Presented by: Mark McDonald – OA Solutions Tuesday July 14th, 2009 - 12:40am to 1:30am (Room U013)