DARTS : HR Census Tables

The census tables in HRPRD are updated four times a year (3/31, 6/30, 10/31 and 12/31). There are 4 SQRs to update two tables. The tables are PS_UM_F_EMP_CENSUS and PS_UM_F_DIS_CENSVW (which despite the name is really a table not a view).

Step-by-step guide

1. Open HRPRD in PL/SQL Developer and run this SQL. Make sure the current census date is NOT on these tables.

   select census_dt, count(emplid) from sysadm.PS_UM_F_EMP_CENSUS  
   group by census_dt
   order by census_dt desc;
   
   
   select census_dt, count(emplid) from sysadm.PS_UM_F_DIS_CENSVW 
   group by census_dt
   order by census_dt desc;

2. Run this SQL to check if the archive ran the night before.

   SELECT MAX(ARCHIVE_DT) FROM SYSADM.PS_UM_EMPLOYE_ARC;

3. Log in HRPRD on MaineStreet and go to Navigator, University of Maine System> General Options > Processes > Census Tables
   
   

4. The run control page requires a run control ID. Here is an example using “BATCH”, but anything will do. Change the "Census" date to the appropriate date (3/31, 6/30, 10/31 and 12/31).

   

5. Click "Run" and you get this.

   
   

6. If you run on the census day, check the boxes for UMANDISC and UMEMPCEN then click "OK". 

       

   If you run after the census day, check the 2 back-fill options; UMADNIBF and UMEMPCBF.  If the archive has not run the night before (check by running this SQL :  SELECT MAX(ARCHIVE_DT) FROM SYSADM.PS_UM_EMPLOYE_ARC ) , you can use the UMANDISC and UMEMPCEN, otherwise run UMANDIBF and UMEMPCBF which use the archived extracts .

      

7. Click "OK" to run the processes.

8. Go to the Process Monitor and wait till the "Run Status" change to "Success". (It will usually take less than 10 minutes.)

9. Run this SQL (same as #1) again to check the data. You should see the data for the most recent census date on these two tables.

   select census_dt, count(emplid) from sysadm.PS_UM_F_EMP_CENSUS  
   group by census_dt
   order by census_dt desc;
   
   
   select census_dt, count(emplid) from sysadm.PS_UM_F_DIS_CENSVW 
   group by census_dt
   order by census_dt desc;

10. After the tables have been refreshed, send notice to these groups and person: DARTS@maine.edu, hris@maine.edu, matt@maine.edu

    Sample Email

    These tables have been updated as of mm/dd/yyyy.

    • PS_UM_F_EMP_CENSUS
    • PS_UM_F_DIS_CENSVW

    Please let me know if you have any questions. Thank you.