Reporting Changes In Peoplesoft With SQR, Part 2

Last week, we considered change reporting based on the effective dates and action dates of PS_JOB. Other Peoplesoft tables track history differently. We need other approaches to report those changes.

Benefit Changes

Benefit carriers (i.e. health insurance companies, 401K administrators) usually want a monthly update to their databases of our employees. Some carriers want a full enrollment list. They probably use them as comparison tables (see below) to update their data. Other carriers want a change file. This is harder for us in the base benefit tables than it is in PS_JOB.

PS_HEALTH_BENEFIT tracks an employee’s enrollments in medical, dental, and vision plans. We use several date fields to select the rows to review for changes.

  • COVERAGE_ELECT_DT (similar to the ACTION_DT in PS_JOB) indicates when the employee chose the current details of the benefit plan.
  • EFFDT (which is labeled “Deduction Begin Date” on the data entry page) indicates when the current details go into effect, at which time the employee’s payroll deduction will change.
  • COVERAGE_BEGIN_DT indicates when the benefit begins. It is often the first day of the month following EFFDT.

Once we read a row, we look at the COVERAGE_ELECT flag (E – enrolled, T – terminated, W – waived). If the row is an enrollment, the BENEFIT_PLAN and COVRG_CD can differentiate it from a previous enrollment. If the row is a termination, perhaps the employee has terminated as well.

Some organizations give employees money if they waive certain benefits. In that case, they may have a plan called “WAIVE” rather than a “W” on a row. Some organizations have a row for every employee, others have a row only for enrolled employees. That’s important to remember before writing a join of PS_JOB and PS_HEALTH_BENEFIT.

Open enrollment may complicate the sequence of events if an employee makes a choice in November that takes effect in January, and then has a qualifying event (birth, marriage, divorce, etc.) take place in December.

After we consider changes to an employee’s enrollment, we need to consider changes to the employee’s dependents.

Often, organizations extend the health benefit to members of their employees’ families; the dependents. Peoplesoft describes the dependents in tables like PS_DEP_BEN and PS_DEP_BEN_EFF. The people in these tables can be dependents or beneficiaries of any combination of benefit plans. If they have health insurance, they will have rows in PS_HEALTH_DEPENDNT (notice the missing “E” in the table name).

The dates in PS_DEP_BEN are BIRTHDATE, DT_OF_DEATH, COBRA_EVENT_DT, MEDICARE_ENTLD_DT, LAST_UPDATE_DATE, and DEP_ADOPTED_DT_UK. LAST_UPDATE_DATE is probably a good substitute for ACTION_DT. There is no EFFDT, but we can look at all the other dates to see if anything important happened in the recent past. It all may be moot, because the most important question, is this person covered, is answered in PS_HEALTH_DEPENDNT.

PS_DEP_BEN_EFF has an EFFDT field, but nothing equivalent to ACTION_DT. It has some dates that are relevant to a dependent’s eligibility for coverage (in many benefit plans) like MAR_STATUS_DT, STUDENT_STATUS_DT, and DISABLED_STATUS_DT. But again, it all may be moot, because the most important question, is this person covered, is answered in PS_HEALTH_DEPENDNT.

PS_HEALTH_DEPENDNT is a child table of PS_HEALTH_BENEFIT. It shares the key fields EMPLID, EMPL_RCD, COBRA_EVENT_ID, PLAN_TYPE, BENEFIT_NBR, and EFFDT. It adds one more key after EFFDT, DEPENDENT_BENEF, which is a two character field that contains values like “01” and “02”. The record layout reminds us that any change in the list of dependents requires a new effective date in the parent table and the child table. We use COVERAGE_ELECT to end the employee’s enrollment but we use the absence of a row to end a dependent’s enrollment.

Consider the following INCORRECT where clause.

 from PS_HEALTH_DEPENDNT HD
where …
  and HD.EFFDT = (select max(EFFDT)
                    from PS_HEALTH_DEPENDNT
                   where EMPLID = HD.EMPLID
                     and EMPL_RCD = HD.EMPL_RCD
                     and COBRA_EVENT_ID = HD.COBRA_EVENT_ID
                     and PLAN_TYPE = HD.PLAN_TYPE
                     and BENEFIT_NBR = HD.BENEFIT_NBR
                     and EFFDT <= $asofdate
                     and DEPENDENT_BENEF = HD.DEPENDENT_BENEF)

Suppose there is employee with a dependent spouse (“01”) and a dependent child (“02”). On September 13, 2009, the child turns 24 and is no long eligible for the medical plan. We create a new row in PS_HEALTH_BENEFIT with EFFDT 09/13/2009. We create a new row in PS_HEALTH_DEPENDNT for the spouse with EFFDT 09/13/2009, but no new row for the adult child. The SQL above will still select a row for the adult child from a previous effective date. Peoplesoft did not give us an EFF_STATUS field to set to inactive.

The proper SQL gets the current effective date from the parent table. That’s the way to detect a deletion.

 from PS_HEALTH_DEPENDNT HD
where …
  and HD.EFFDT = (select max(EFFDT)
                    from PS_HEALTH_BENEFIT
                   where EMPLID = HD.EMPLID
                     and EMPL_RCD = HD.EMPL_RCD
                     and COBRA_EVENT_ID = HD.COBRA_EVENT_ID
                     and PLAN_TYPE = HD.PLAN_TYPE
                     and BENEFIT_NBR = HD.BENEFIT_NBR
                     and EFFDT <= $asofdate)

Record Audits

Peoplesoft HCM 8.9 is delivered with a handful of audited records. It can be hard to tell from record names, but it seems that my version of DEMO has ten audited records for Time & Labor, six for federal government personal-data tables, three for service industry tables, and one other. My organization has added four more. This is not a heavily used feature.

In the Application Designer, the Properties dialog box for records allows us to define a record audit. This definition will drive the PIA page processor to add rows to an audit table to keep track of changes to its data table.

We create the audit record with three keys, AUDIT_OPRID, AUDIT_STAMP, and AUDIT_ACTN. Those fields tell us who did the data entry, when it happened (date/time stamp), and what it was (add, change, or delete). We also add fields from the data record to the audit record to make the PIA page processor store the old or the new values of those fields.

Record audits give us “action dates” for any table. They are harder to use than the ACTION_DT in PS_JOB because we have to look to the data table for effective dates and the audit table for action dates. Also, field changes in the data table will generate two rows in audit table – old values and new values.

Comparison Tables

Another option is to create snapshot tables. We could have a table with all the interesting fields from our data table plus another key field, the ASOFDATE. We could write a program, or even a single SQL statement, to copy the relevant rows and columns of the data table to the snapshot table.

We could output the differences between the latest snapshot and the previous one. For greater flexibility, we could output the differences between any two snapshots for historical change reports or cumulative change reports.

Workflow

Change reports may be used for tracking the life of the organization; looking for trends or spotting unusual events. Often, it’s used to trigger a business process. Has an employee been hired? Let’s send a benefits enrollment package. Peoplesoft offers a built-in workflow facility to help us respond to these events more promptly, and to manage the work of those responses. When a customer asks for a report that will serve as a “to do” list, we ought to suggest workflow instead.

4 Comments

  1. Ken says:

    My second query is not reflective of the 3 records in the PS_HEALTH_BENEFIT table and I am unable to fix. Any suggestions?
    select
    count (a.EMPLID)
    from
    PS_JOB A inner join PS_PERSONAL_DATA B
    on A.EMPLID = B.EMPLID
    inner join PS_HEALTH_BENEFIT D
    on A.EMPLID = D.EMPLID
    – # inner join PS_RATE_SCHED_COVG C
    – # on D.COVRG_CD = C.COVRG_CD
    where A.emplid = ‘100806′;
    COUNT(A.EMPLID)
    —————
    3
    ————————————————-
    ————————————————-
    select
    count (a.EMPLID)
    from
    PS_JOB A inner join PS_PERSONAL_DATA B
    on A.EMPLID = B.EMPLID
    inner join PS_HEALTH_BENEFIT D
    on A.EMPLID = D.EMPLID
    inner join PS_RATE_SCHED_COVG C
    on D.COVRG_CD = C.COVRG_CD
    where A.emplid = ‘100806′;
    COUNT(A.EMPLID)
    —————
    13

  2. administrator says:

    Ken, when you have a mystery like this, it’s best to look at the data instead of selecting a count(). Select D.EFFDT and D.PLAN_TYPE, to see what’s in PS_HEALTH_BENEFIT, and the key fields from PS_RATE_SCHED_COVG. Are you assuming that there is only one row in PS_RATE_SCHED_COVG for each row in PS_HEALTH_BENEFIT? Maybe there’s more.

  3. Mike says:

    We’re looking at doing some reporting based on workflows that have already been setup regarding the PS_JOB table. Do you have any workflow query examples by any chance?

    Thanks – great blog!

  4. administrator says:

    Mike, I’ve never worked with workflow and I don’t even know what tables are involved. If you can view workflow data, press Ctrl-J on your browser to identify the PeopleSoft page you’re viewing. Open it in Application Designer and see what records are on it. That will give you a start. Good luck.