Halloween season is here... the time for pumpkins, ghosts, candy & other fun. It's also a time that always reminds me of a project I worked on over 20 years ago for Spencer Gifts. In addition to the retail stores you see in the mall they also run the Spirit Halloween shops that are popping up all over the country. Everyone loves those stores... at least everyone I know. In June 1998, Spencer Gifts was owned by Universal Studios which was owned by Seagram's Corporation. The project was to integrate Spencers with the parent Seagram PeopleSoft HRMS/Payroll system while adhering to their existing business model. Due to the nature of the business - seasonal staff, high turnover rate, retirees, students, extraordinary volume, etc. - it was a condition of Go-Live to be able to automatically maintain employees/benefits/payroll via the on-site store managers & the sales registers. For a store that is known for marketing silly, quirky & outright absurd merchandise their enterprise is highly sophisticated & complex. This was the perfect project for my "particular set of skills"...
From my LinkedIn Projects List:
Remote Point-of-Sale Integration with PeopleSoft HR/Benefits/Payroll - Spencer Gifts/Spirit Halloween - June 1998
In addition to retail sales information the store terminals are used by managers to maintain their employee information - Hires, Rehires, Transfers, Promotions, Terminations, Leave of Absence, Tax Changes & general Name/Address updates. Time/Attendance information is also entered.
All the information, spread across North America, is polled each night & stored in the Spencer legacy database. The Employee Data & Hours, in their native layouts, would now be diverted & interfaced into PeopleSoft. My interface process SEAPOS01.SQR must create/update all employee tables, track job activity & automatically enroll the employees into the proper benefit programs. Data entry errors are identified, stored for online correction (by corporate personnel) & recycled into the interface process. (SEAPOS02.SQR handles the Hours portion).
Without question SQR was the hands down tool for this job - it was then & frankly, it would be now. The process, despite various business changes, reorganizations and version upgrades, is still running.
!**********************************************************************
!* *
!* MODULE: SEAPOS01.SQR *
!* AUTHOR: TONY DELIA. *
!* DATE: 06/30/1998. *
!* SYSTEM: SPENCER GIFTS - POS EMPLOYEE DATA INTERFACE. *
!* DESC: POINT OF SALE EMPLOYEE DATA INTERFACE. *
!* *
!**********************************************************************
!* *
!* TABLES: MISCELLANEOUS TABLES *
!* *
!* ps_sea_run_cntl_sg - Select *
!* ps_installation - Select, Update *
!* psdbfield - Select *
!* ps_pay_calendar - Select *
!* ps_company_tbl - Select *
!* ps_jobcode_tbl - Select *
!* ps_sal_grade_tbl - Select *
!* ps_state_names_tbl - Select *
!* ps_dept_tbl - Select *
!* *
!* POS PROCESSING/DEFINITION TABLES *
!* *
!* ps_sea_pos_data - Select, Insert, Update *
!* ps_sea_pos_errs - Delete, Insert *
!* ps_sea_pos_log - Select, Insert *
!* ps_sea_pos_jobcd - Select *
!* ps_sea_pos_req - Select *
!* ps_sea_pos_ben - Select *
!* ps_sea_pos_ben_pln - Select *
!* *
!* EMPLOYEE TABLES *
!* *
!* ps_personal_data - Select, Insert, Update *
!* ps_job - Select, Insert, Update *
!* ps_employment - Select, Insert, Update *
!* *
!* TAX TABLES *
!* *
!* ps_fed_tax_data - Select, Insert, Update *
!* ps_state_tax_data - Select, Insert, Update *
!* ps_local_tax_data - Select, Insert, Update *
!* ps_tax_distrib - Select, Insert, Update *
!* ps_tax_dist_effdt - Select, Insert, Update *
!* *
!* EMPLOYEE BENEFIT TABLES *
!* *
!* ps_ben_prog_partic - Select, Insert *
!* ps_benefit_partic - Select, Insert *
!* ps_health_benefit - Select, Insert *
!* ps_life_add_ben - Select, Insert *
!* ps_disability_ben - Select, Insert *
!* ps_savings_plan - Select, Insert *
!* ps_leave_plan - Select, Insert *
!* ps_fsa_benefit - Select, Insert *
!* ps_rtrmnt_plan - n/a *
!* ps_pension_plan - Select, Insert *
!* ps_vacation_ben - n/a *
!* *
!**********************************************************************
Breakdown of sub-modules within MAIN processing program:
#Include 'seaposer.sqc' !POS Error Message Routines
#Include 'seaposcv.sqc' !POS Conversion Matrix Routines
#Include 'seaposdb.sqc' !POS Database Activity (Inserts,Updates,etc.)
#Include 'seaposrq.sqc' !POS Required Field Routines
#Include 'seaposjm.sqc' !POS Jobcode Matrix Routines
#Include 'seapostx.sqc' !POS Tax Data Processing
#Include 'seaposbn.sqc' !POS Benefit Data Processing
#Include 'seapospr.sqc' !POS Print Routines
Sub-modules within seaposbn.sqc - Benefit Data Processing:
#Include 'seaposb0.sqc' !POS Benefit Processing - PROG/PARTIC
#Include 'seaposb1.sqc' !POS Benefit Processing - HEALTH_BENEFIT
#Include 'seaposb2.sqc' !POS Benefit Processing - LIFE_ADD_BEN
#Include 'seaposb3.sqc' !POS Benefit Processing - DISABILITY_BEN
#Include 'seaposb4.sqc' !POS Benefit Processing - SAVINGS_PLAN
#Include 'seaposb5.sqc' !POS Benefit Processing - LEAVE_PLAN
#Include 'seaposb6.sqc' !POS Benefit Processing - FSA_BENEFIT
#Include 'seaposb7.sqc' !POS Benefit Processing - RTRMNT_PLAN
#Include 'seaposb8.sqc' !POS Benefit Processing - PENSION_PLAN
#Include 'seaposb9.sqc' !POS Benefit Processing - VACATION_BEN
When I was assigned to this project I met with a very helpful & highly proficient Spencer HRMS team, collected the OS/Polling documentation/test files & built a complete functioning prototype on my laptop using the SQL*Base version of PeopleSoft. This was completed before the initial meetings with Seagram...
Before I stray off the Halloween & Spirit theme - here's a very quick 3D character montage I put together using DAZ Studio 4.11. I'm sure you can find all these costumes, props & more at Spirit Halloween shops.
The big nut to crack when creating a complete automated interface for PeopleSoft HRMS is how to regulate benefits enrollment. At Spencers, all benefit programs were issued by jobcode. I created a custom jobcode default table to hold the benefit program (and other attributes)...
...then another custom table that holds the benefit plans within each program. I also defined the methods to assign effective dates. The methods dictate the proper date to begin benefits, terminate or transfer them. When transferring from one jobcode to another the table entries are compared & a delta assigned indicating if benefits were added, dropped or the same (+, -, =). The program responds accordingly.
The routine Build-Benefit-Matrix performs the aforementioned delta comparison & creates two arrays - one that holds the Benefit Program New/Old values & the other as a sub-array to map the required Enrollment/Termination (based on the indicator +, - or =). The first entry below shows the blueprint for a benefit program upgrade to Z06 from Z07. All plans are = except for type 10 LTD which needs to be added. Conversely, when switching from Z06 to Z07 the type 10 LTD would be terminated. All "=" benefit plans remain untouched.
Idx Mode New Old BegX EndX
---- ---- --- --- ---- ----
0000 X Z06 Z07 0000 0013
Idx Mode Ind New Old Type Plan Action Beg Trm Xfr
---- ---- --- --- --- ---- ---- ------ --- --- ---
0000 X = Z06 Z07 10 T T4 X4
0001 X = Z06 Z07 11 T T4 X4
0002 X = Z06 Z07 20 LIF25 I B1 T1 X1
0003 X = Z06 Z07 22 ADD25 I B1 T1 X1
0004 X = Z06 Z07 27 TRV1X I B1 T1 X1
0005 X = Z06 Z07 30 STD67% I B2 T1 X1
0006 X + Z06 Z07 31 LTD I B2 T1
0007 X = Z06 Z07 40 T T3
0008 X = Z06 Z07 50 T T3 X1
0009 X = Z06 Z07 51 T T3 X1
0010 X = Z06 Z07 5Z HOL I B1 T3
0011 X = Z06 Z07 60 T T3 X1
0012 X = Z06 Z07 61 T T3 X1
0013 X = Z06 Z07 82 T T1
Idx Mode New Old BegX EndX
---- ---- --- --- ---- ----
0001 X Z06 Z08 0014 0027
Idx Mode Ind New Old Type Plan Action Beg Trm Xfr
---- ---- --- --- --- ---- ---- ------ --- --- ---
0014 X + Z06 Z08 10 T T4
0015 X + Z06 Z08 11 T T4
0016 X + Z06 Z08 20 LIF25 I B1 T1
0017 X + Z06 Z08 22 ADD25 I B1 T1
0018 X + Z06 Z08 27 TRV1X I B1 T1
0019 X + Z06 Z08 30 STD67% I B2 T1
0020 X + Z06 Z08 31 LTD I B2 T1
0021 X = Z06 Z08 40 T T3
0022 X + Z06 Z08 50 T T3
0023 X + Z06 Z08 51 T T3
0024 X + Z06 Z08 5Z HOL I B1 T3
0025 X + Z06 Z08 60 T T3
0026 X + Z06 Z08 61 T T3
0027 X = Z06 Z08 82 T T1
Idx Mode New Old BegX EndX
---- ---- --- --- ---- ----
0002 X Z07 Z06 0028 0041
Idx Mode Ind New Old Type Plan Action Beg Trm Xfr
---- ---- --- --- --- ---- ---- ------ --- --- ---
0028 X = Z07 Z06 10 T T4 X4
0029 X = Z07 Z06 11 T T4 X4
0030 X = Z07 Z06 20 LIF25 I B1 T1 X1
0031 X = Z07 Z06 22 ADD25 I B1 T1 X1
0032 X = Z07 Z06 27 TRV1X I B1 T1 X1
0033 X = Z07 Z06 30 STD67% I B2 T1 X1
0034 X - Z07 Z06 31 LTD I B2 T1 X1
0035 X = Z07 Z06 40 T T3
0036 X = Z07 Z06 50 T T3 X1
0037 X = Z07 Z06 51 T T3 X1
0038 X = Z07 Z06 5Z HOL I B1 T3
0039 X = Z07 Z06 60 T T3 X1
0040 X = Z07 Z06 61 T T3 X1
0041 X = Z07 Z06 82 T T1
<SNIP>
The Process-Benefit-Pointer routine coordinates the adding/removing of benefits for the employee using the Benefit Matrix.
!**********************************************************************
!* Process Benefit Pointer *
!**********************************************************************
begin-procedure Process-Benefit-Pointer
let #PTidx = 0
let $PTsw = 'N'
while #PTidx < #PTctr
let $PTmode = PTmtx.PTmode (#PTidx)
let $PTnew = PTmtx.PTnew (#PTidx)
let $PTold = PTmtx.PTold (#PTidx)
let #PTbeg = PTmtx.PTbeg (#PTidx)
let #PTend = PTmtx.PTend (#PTidx)
if $PTmode = $BENmode
and $PTnew = $BENnew
and $PTold = $BENold
let #PTidx = #PTctr
let $PTsw = 'Y'
end-if
let #PTidx = #PTidx + 1
end-while
if $PTsw <> 'Y'
let $ERRtxt = 'Benefit Matrix Error - Key(s) not found: '
let $ERRtxt = $ERRtxt || $BENmode || '/' || $BENnew || '/' || $BENold
do Build-Error-Matrix
else
do Calc-Benefit-Dates
let #BNidx = #PTbeg
while #BNidx <= #PTend
let $BNmode = BNmtx.BNmode (#BNidx)
let $BNind = BNmtx.BNind (#BNidx)
let $BNnew = BNmtx.BNnew (#BNidx)
let $BNold = BNmtx.BNold (#BNidx)
let $BNtype = BNmtx.BNtype (#BNidx)
let $BNplan = BNmtx.BNplan (#BNidx)
let $BNactn = BNmtx.BNactn (#BNidx)
let $BNmthB = BNmtx.BNmeth (#BNidx, #idxBEG) ! Method - Begin
let $BNmthT = BNmtx.BNmeth (#BNidx, #idxTRM) ! Method - Term
let $BNmthX = BNmtx.BNmeth (#BNidx, #idxXFR) ! Method - Xfer
let $BNmeth = BNmtx.BNmeth (#BNidx, #BENidx) ! Method - Specific
let $BNproc = 'Y'
! Bypass Conditions - Benefit Matrix
! A - No Begin/Terminate Method (Non-Transfer)
if $BNmode = 'Z'
and $BNmeth = ' '
! B - Begin Method without Benefit Plan (Non-Transfer)
or $BNmode = 'Z'
and $BNmeth = $BNmthB
and $BNplan = ' '
! C - Transfer New/Old yields EQUAL Benefit Attributes
or $BNind = '='
! D - Gain in Benefits without Begin Method (Non-Auto)
or $BNind = '+'
and $BNmthB = ' '
! E - Loss in Benefits without Terminate Method (Non-Auto)
or $BNind = '-'
and $BNmthX = ' '
let $BNproc = 'N'
end-if
if $BNproc = 'Y'
do Process-Benefit-Matrix
end-if
let #BNidx = #BNidx + 1
end-while
end-if
end-procedure
!**********************************************************************
In a prior blog post I used the SEAPOS01.SQR program as the basis for my SQR Background Processor. You can see a portion of the underlying Process Flowchart for the Benefits Handling.
This was a complex, well-designed interface I built from scratch. Despite supporting another client simultaneously in PeopleSoft Financials (AR, GL, AM, AP) and splitting my on-site time during the week (King of Prussia/Egg Harbor Township), it was always ahead of schedule in development & testing with the Go-Live being a smooth anticlimactic event.
Now let's pause for some more scary stuff... another scene I created with DAZ Studio.
I'll take a moment to acknowledge one of the outstanding resources at Spencer Gifts - Dave Bell (currently Sr. HRIS Manager). He's extremely knowledgeable & a valuable asset to his organization - and has the sense of humor you would expect from a Spencer Gifts employee. Working with Dave we also added the capability to enter online transactions so they may be processed in a similar fashion as the nightly polled items. Then he took that one step further with mass data inserts from his MS Access database into the POS staging tables for Annual Salary Planning. By utilizing the SEAPOS01 process he invoked the same logic & consistency for all updates.
The company headquarters is located in Egg Harbor Township, NJ just outside of Atlantic City - between my house & the shore. The commute from Marlton was one of the best I've ever had... Also, I was able to attend the Annual Spencer Gifts Sample Sale - all the vendor prototypes are put up for grabs to the employees (and guests). I picked up a talking alien doll for 50 cents (which I still have), alien blow-up doll for 25 cents (not what you think), alien in a capsule for a buck, a big styrofoam gargoyle for another buck... and a couple free lava lamps! To commemorate that bountiful day I created another DAZ 3D Studio scene - an alien with a lava lamp from Spencer Gifts.
HAPPY HALLOWEEN!
