Micros Res 3700 - How/when do entries get added to hist_job_rate_dtl (pay rate history)

[raphael75]

I'm trying to figure out how & when data gets added to the hist_job_rate_dtl table. I thought maybe it was copied there from the job_rate_def table when the end of night runs for rows where the rate_effective_datetime matches the current date, but that doesn't appear to be it.

Does it come from a different table?

Any help is appreciated, thanks!

 

[Moregelen]

It is controlled through a trigger in the database:

[COLOR=#0000FF]ALTER[/color] [COLOR=#0000FF]TRIGGER[/color] "taU5_job_rate_def"
    [COLOR=#0000FF]AFTER[/color] [COLOR=#FF00FF]UPDATE[/color] [COLOR=#0000FF]ORDER[/color] 5 [COLOR=#0000FF]ON[/color] MICROS[COLOR=#808080].[/color]job_rate_def
    REFERENCING OLD [COLOR=#0000FF]AS[/color] OLD NEW [COLOR=#0000FF]AS[/color] "NEW"
    [COLOR=#0000FF]FOR[/color] EACH [COLOR=#0000FF]ROW[/color]
    [COLOR=#0000FF]BEGIN[/color]
      [COLOR=#0000FF]DECLARE[/color] @nowinuct [COLOR=#0000FF]TIMESTAMP[/color][COLOR=#808080];[/color]
      [COLOR=#0000FF]DECLARE[/color] @emp_seq SEQ_NUM[COLOR=#808080];[/color]
      [COLOR=#0000FF]DECLARE[/color] @hist_seq SEQ_NUM[COLOR=#808080];[/color]
      [COLOR=#0000FF]DECLARE[/color] @timeinuct [COLOR=#0000FF]TIMESTAMP[/color][COLOR=#808080];[/color]
      [COLOR=#0000FF]DECLARE[/color] @effective_gmt_datetime [COLOR=#0000FF]DATETIME[/color][COLOR=#808080];[/color]
      [COLOR=#0000FF]DECLARE[/color] @em_rest_type [COLOR=#0000FF]CHAR[/color][COLOR=#808080]([/color]1[COLOR=#808080]);[/color]
 
      [COLOR=#0000FF]CALL[/color] MICROS[COLOR=#808080].[/color]spem_GetStoreType[COLOR=#808080]([/color]@em_rest_type[COLOR=#808080]);[/color]
 
      [COLOR=#0000FF]IF[/color] @em_rest_type [COLOR=#808080]<>[/color] [COLOR=#FF0000]'C'[/color] [COLOR=#0000FF]THEN[/color]  
 
 
        [COLOR=#808080]//[/color][COLOR=#0000FF]If[/color] an effective [COLOR=#0000FF]date[/color] [COLOR=#808080]is[/color] [COLOR=#0000FF]set[/color] that [COLOR=#808080]is[/color] later than the [COLOR=#0000FF]current[/color] [COLOR=#0000FF]time[/color][COLOR=#808080],[/color] [COLOR=#0000FF]get[/color] the gmt
        [COLOR=#808080]//[/color]equivalent [COLOR=#0000FF]to[/color] [COLOR=#FF00FF]update[/color] the historical [COLOR=#0000FF]table[/color][COLOR=#808080].[/color]
        [COLOR=#0000FF]IF [/color][COLOR=#808080]([/color]"NEW"[COLOR=#808080].[/color]rate_effective_datetime [COLOR=#808080]>[/color] [COLOR=#0000FF]now[/color][COLOR=#808080](*))[/color][COLOR=#0000FF]THEN[/color]
          [COLOR=#0000FF]SET[/color] @effective_gmt_datetime [COLOR=#808080]=[/color] f_LaborLocalToUCT[COLOR=#808080]([/color]"NEW"[COLOR=#808080].[/color]rate_effective_datetime[COLOR=#808080]);[/color]
        [COLOR=#0000FF]END[/color] [COLOR=#0000FF]IF[/color][COLOR=#808080];[/color]
 
        [COLOR=#0000FF]SELECT[/color] emp_seq [COLOR=#0000FF]INTO[/color] @emp_seq [COLOR=#0000FF]FROM[/color] MICROS[COLOR=#808080].[/color]sysinfo_temp[COLOR=#808080];[/color]
 
        [COLOR=#0000FF]SET[/color] @nowinuct [COLOR=#808080]=[/color] f_LaborNowInUCT[COLOR=#808080]();[/color]
        [COLOR=#0000FF]SET[/color] @timeinuct [COLOR=#808080]=[/color] [COLOR=#FF00FF]ISNULL[/color][COLOR=#808080]([/color]@effective_gmt_datetime[COLOR=#808080],[/color] @nowinuct[COLOR=#808080]);[/color]
 
        [COLOR=#0000FF]DELETE[/color] [COLOR=#0000FF]FROM[/color] MICROS[COLOR=#808080].[/color]hist_job_rate_dtl 
         [COLOR=#0000FF]WHERE[/color] effective_gmt_datetime [COLOR=#808080]>[/color] @nowinuct
           [COLOR=#808080]AND[/color] job_seq [COLOR=#808080]=[/color] "NEW"[COLOR=#808080].[/color]job_seq
           [COLOR=#808080]AND[/color] emp_seq [COLOR=#808080]=[/color] "NEW"[COLOR=#808080].[/color]emp_seq[COLOR=#808080];[/color]
 
        [COLOR=#0000FF]SET[/color] @hist_seq [COLOR=#808080]=[/color] 1[COLOR=#808080];[/color]
 
        [COLOR=#0000FF]SELECT[/color] [COLOR=#FF00FF]max[/color][COLOR=#808080]([/color]hist_seq[COLOR=#808080])[/color] [COLOR=#808080]+[/color] 1
          [COLOR=#0000FF]INTO[/color] @hist_seq
          [COLOR=#0000FF]FROM[/color] MICROS[COLOR=#808080].[/color]hist_job_rate_dtl[COLOR=#808080];[/color]
 
        [COLOR=#0000FF]INSERT[/color] [COLOR=#0000FF]INTO[/color] MICROS[COLOR=#808080].[/color]hist_job_rate_dtl
[COLOR=#0000FF]          [/color][COLOR=#808080]([/color]emp_seq[COLOR=#808080],[/color] job_seq[COLOR=#808080],[/color] hist_seq[COLOR=#808080],[/color] job_skill_seq[COLOR=#808080],[/color] ob_primary_job[COLOR=#808080],[/color]
           override_reg_pay_rate[COLOR=#808080],[/color] effective_gmt_datetime[COLOR=#808080],[/color] change_by[COLOR=#808080],[/color] change_date[COLOR=#808080],[/color] pay_reason_seq[COLOR=#808080])[/color]
        [COLOR=#0000FF]VALUES[/color]
[COLOR=#0000FF]          [/color][COLOR=#808080]([/color]"NEW"[COLOR=#808080].[/color]emp_seq[COLOR=#808080],[/color] "NEW"[COLOR=#808080].[/color]job_seq[COLOR=#808080],[/color] @hist_seq[COLOR=#808080],[/color] "NEW"[COLOR=#808080].[/color]job_skill_seq[COLOR=#808080],[/color] "NEW"[COLOR=#808080].[/color]ob_primary_job[COLOR=#808080],[/color]
           "NEW"[COLOR=#808080].[/color]override_reg_pay_rate[COLOR=#808080],[/color] @timeinuct[COLOR=#808080],[/color] @emp_seq[COLOR=#808080],[/color] [COLOR=#0000FF]now[/color][COLOR=#808080](*),[/color] "NEW"[COLOR=#808080].[/color]pay_reason_seq[COLOR=#808080]);[/color]
 
        [COLOR=#0000FF]INSERT[/color] [COLOR=#0000FF]INTO[/color] MICROS[COLOR=#808080].[/color]hist_emp_job_otm_lvl_dtl
[COLOR=#0000FF]          [/color][COLOR=#808080]([/color]emp_seq[COLOR=#808080],[/color] job_seq[COLOR=#808080],[/color] otm_lvl_seq[COLOR=#808080],[/color] hist_seq[COLOR=#808080],[/color] override_otm_pay_rate[COLOR=#808080],[/color] effective_gmt_datetime[COLOR=#808080],[/color]
           change_by[COLOR=#808080],[/color] change_date[COLOR=#808080])[/color]
        [COLOR=#0000FF]SELECT[/color] emp_seq[COLOR=#808080],[/color] job_seq[COLOR=#808080],[/color] otm_lvl_seq[COLOR=#808080],[/color] @hist_seq[COLOR=#808080],[/color] override_otm_pay_rate[COLOR=#808080],[/color] @timeinuct[COLOR=#808080],[/color] @emp_seq[COLOR=#808080],[/color] [COLOR=#0000FF]now[/color][COLOR=#808080](*)[/color]
          [COLOR=#0000FF]FROM[/color] MICROS[COLOR=#808080].[/color]emp_job_otm_lvl_def
         [COLOR=#0000FF]WHERE[/color] emp_seq [COLOR=#808080]=[/color] "NEW"[COLOR=#808080].[/color]emp_seq
           [COLOR=#808080]AND[/color] job_seq [COLOR=#808080]=[/color] "NEW"[COLOR=#808080].[/color]job_seq[COLOR=#808080];[/color]
 
      [COLOR=#0000FF]END[/color] [COLOR=#0000FF]IF[/color][COLOR=#808080];[/color] [COLOR=#808080]//[/color]EM [COLOR=#0000FF]Check[/color]
 
    [COLOR=#0000FF]END[/color]

 

[raphael75]

Thank you so much! I'm not a Sybase expert so please bear with me. If I'm reading this right, this fires when an update is made in job_rate_def?

Does it grab the data from job_rate_def and immediately insert it into hist_job_rate_dtl?

It looks like it also copies data from hist_emp_job_otm_lvl_dtl?

I see rows in job_rate_def that I can't find in hist_job_rate_dtl. For example, if I do this:

[pre]select
date(change_date) as 'business_date',
hjrd.hist_seq,
hjrd.emp_seq,
hjrd.job_seq,
hjrd.override_reg_pay_rate,
hjrd.effective_gmt_datetime,
hjrd.change_by,
hjrd.change_date
from
micros.hist_job_rate_dtl hjrd
where
date(hjrd.change_date) between '1950-01-01' and '2016-06-14'
and
hjrd.emp_seq = 2116
order by
hjrd.job_seq,
hjrd.effective_gmt_datetime[/pre]

I get this:

[pre]site_id business_date hist_seq emp_seq job_seq override_reg_pay_rate effective_gmt_datetime change_by change_date
126 2016-06-03 11744 2116 40 13.500 1970-01-01 00:00:00.000000 2119 2016-06-03 17:05:55.822000
126 2016-06-03 11745 2116 40 13.500 2016-06-03 22:05:24.000000 2119 2016-06-03 17:05:55.904000
126 2013-11-15 9702 2116 43 11.500 2013-11-15 18:27:36.000000 1964 2013-11-15 12:28:05.453000
126 2013-11-15 9703 2116 43 11.500 2013-11-15 18:27:36.000000 1964 2013-11-15 12:28:05.468000
126 2015-06-17 11135 2116 43 12.000 2015-06-17 21:16:48.000000 2320 2015-06-17 16:17:12.151000
126 2015-06-17 11136 2116 43 12.000 2015-06-17 21:16:48.000000 2320 2015-06-17 16:17:12.174000
126 2015-10-04 11367 2116 43 12.500 2015-10-05 01:34:48.000000 2119 2015-10-04 20:34:49.158000
126 2015-10-04 11368 2116 43 12.500 2015-10-07 05:00:00.000000 2119 2015-10-04 20:34:49.203000
126 2016-01-07 11493 2116 43 12.500 2016-01-07 21:13:12.000000 2320 2016-01-07 15:13:39.070000
126 2013-05-25 9045 2116 48 7.250 2013-05-25 13:33:00.000000 1985 2013-05-25 08:33:07.234000
126 2014-12-15 10705 2116 50 11.500 2014-12-17 06:00:00.000000 1985 2014-12-15 17:06:10.732000
126 2013-05-25 9041 2116 53 1970-01-01 00:00:00.000000 1985 2013-05-25 08:32:44.421000
126 2015-06-17 11137 2116 53 2015-06-17 21:16:48.000000 2320 2015-06-17 16:17:12.250000
126 2016-01-07 11492 2116 53 2016-01-07 21:13:12.000000 2320 2016-01-07 15:13:38.933000[/pre]

If I do this:

[pre]select
*
from
micros.job_rate_def jrd
where
jrd.emp_seq = 2116[/pre]

it returns:

[pre]emp_seq job_seq pay_reason_seq override_reg_pay_rate override_job_clk_in job_skill_seq rate_effective_datetime ob_primary_job ob_inactive last_updated_by last_updated_date ob_omit_from_srg
2116 40 13.500 0 2016-06-15 00:00:00.000000 F F 2119 2016-06-03 00:00:00.000000 F
2116 43 12.500 0 2015-10-07 00:00:00.000000 F F 2119 2015-10-04 00:00:00.000000 F
2116 48 7.250 0 2013-05-29 00:00:00.000000 F F 1985 2013-05-25 00:00:00.000000 F
2116 50 11.500 0 2014-12-17 00:00:00.000000 F F 1985 2014-12-15 00:00:00.000000 F
2116 53 0 T F 2320 2016-01-07 00:00:00.000000 F[/pre]


I don't see the row in hist_job_rate_dtl that has the effective date of 6/15/16. Unless the effective_gmt_datetime in hist_job_rate_dtl and the rate_effective_datetime in job_rate_def are different.

Basically I'm trying to reconstruct the history of all previous, current, and upcoming/pending changes to each employee's pay rates (per job code). It seems that I can't find all the information in the hist_job_rate_dtl table, or am I mistaken?

I believe the current default rates are defined in job_def.

Thanks!