micros net sales at clockout 1

[raphael75]

Hello,

We have a system that calculates the tip share for our employees. I created a custom Crystal Report and stored procedure in micros that runs when the server prints out their server report at the end of their shift. The report figure out what the server's net sales were, and calculates either 3% or 4% of the net sales depending on if the shift was lunch or diner. The Crystal Report shows the calculated value on a chit for the amount the server needs to turn in to the manager for their tip share.

We have a separate process that runs the next day after the micros End Of Night runs that extracts the net sales for each employee and displays it on an intranet site.

The system works for the most part, but we discovered that if the manager voided any items on a check and then closed the check, the value printed out on the chit would be different from what micros showed the next day. It appears that either when the Micros End Of Night runs, or at some point between when the server prints their server report and then, that Micros goes back through and corrects this issue, but we can't figure out when or what that is.

There may be other issues that cause the numbers to be different, but the manager void is the only one I have been able to identify so far.

The value we get the next day is always correct, but the value at the time the server prints their report is not.

Is there a place (maybe a stored procedure) that micros stores the current server net sales, or is there some way I can get the final calculated value at the time the server prints their report? Is there a stored procedure I need to run to get micros to calculate the final net sales value?

Thank you!

 

[hosehead78]

What version of Micros are you running?

 

[raphael75]

Version 5.2.

 

[raphael75]

Also, it's RES 3700 5.2.

 

[Wildbar]

We use something similar but based it on checks paid using tender amounts when the server cash's out.

 

[raphael75]

What table(s) is that stored in? Thank you!

 

[pmegan]

The first thing I would do is compare the void times to the report times and make sure they're not being done after the report has been run.
Micros doesn't really totals to post during the EON. Everything is held in the detail tables until being posted, which should be happening with an incremental posting procedure and when a report is run. I'd take a look at those things. Make sure your incremental posting includes Employee Sale & Tracking, and Employee Sales & Tracking by RVC. Also make sure that it's running frequently, I usually set it to go every 15 minutes or so.

 

[raphael75]

Thanks for the info! Can you help me find the void times? I'm not sure where to look. I've searched for the void information but I can't find it due to there being over 1000 tables.

Do you know what stored procedure(s) the Employee Sale & Tracking and the Employee Sales & Tracking by RVC call? Is it possible to have my stored procedure call those before the rest of it runs?

Here is the SQL I'm using:

ALTER PROCEDURE "custom"."sp_mdf_get_sale_dtl" ()
BEGIN
	declare num_days int;
	
	set num_days = -2;

	select

		if hour(c.chk_open_date_time) < 5 then
			date(dateadd(day, -1, date(c.chk_open_date_time)))
		else
			date(c.chk_open_date_time)
		endif as 'business_date',
		
		if(
			(hour(c.chk_open_date_time) >= 5) 
				and 
				(
					(hour(c.chk_open_date_time) <= 15)
					and
					(minute(c.chk_open_date_time) <= 59)
					and
					(second(c.chk_open_date_time) <= 59)
				)
		) then
			'AM'
		else
			'PM'
		endif as 'meridiem',
	
		c.chk_open_date_time,
		c.chk_clsd_date_time,
		
		sed.shift_start_time,
		sed.shift_end_time,
		sed.shift_seq,
		if(
			(hour(sed.shift_start_time) < 7) and (sed.shift_start_time is not null)
		) then
			1
		else
			2
		endif as 'shift_number',
	
		tdef.name as 'table_name',
		c.cov_cnt as 'cover_count',
	
		edef.first_name,
		edef.last_name,
		c.emp_seq,
		edef.obj_num,
		edef.payroll_id,
	
		/*s.trans_seq as 'trans_seq',*/
		/*t.chk_seq as 'check_sequence_id',*/
		c.chk_num as 'check_number',
	
		sum(s.net_sls_ttl) as 'net_sales_total',
		
	
	
		ifnull(tcdtl.job_name,jdef.name,tcdtl.job_name) as 'job_name',
		if c.ob_ccs04_chk_added = 'F' then 0 else 1 endif as 'check_added'/*,
	
		t.type as 'trans_type',
		xfer.xfer_type as 'chk_xfer_dtl_type'*/
	
	
	from
		micros.sale_dtl s
	left join
		micros.trans_dtl t on t.trans_seq = s.trans_seq
	left join
		micros.chk_xfer_dtl xfer on xfer.trans_seq = t.trans_seq
	left join
		micros.chk_dtl c on c.chk_seq = t.chk_seq
	left join
		micros.emp_def edef on edef.emp_seq = c.emp_seq
	left join
		micros.job_def jdef on jdef.job_seq = c.job_seq
	left join
		micros.tbl_def tdef on tdef.tbl_seq = c.tbl_seq
	left join
	(
		select
			tcdtl.emp_seq,
			tcdtl.job_seq 			as 'job_seq',
			jd.name 				as 'job_name',
			tcdtl.clk_in_date_tm 	as 'clock_in',
			tcdtl.clk_out_date_tm 	as 'clock_out'
	
		from
			micros.time_card_dtl tcdtl
		left join
			micros.job_def jd on jd.job_seq = tcdtl.job_seq
	
		where 
			/*clock_in between '2013-08-05' and '2013-08-05 23:59:59'*/
			clock_in > dateadd(day, num_days,now())

	
	)tcdtl on (tcdtl.emp_seq = c.emp_seq) and (c.chk_open_date_time > dateadd(second, -30,tcdtl.clock_in)) and (c.chk_open_date_time <= dateadd(second, 60,tcdtl.clock_out))
	left join
	(
		select
			sedi.emp_seq,
			-- if((sed.shift_end_time is null) and (hour(sed.shift_end_time) > 7)) then (select (max(shift_seq)) from micros.shift_emp_dtl) else ifnull(sed.shift_seq, (select max(shift_seq) from micros.shift_emp_dtl), shift_seq) endif as 'shift_seq', -- ifnull(sed.shift_end_time,-1, sed.shift_seq) as 'shift_seq',
			sedi.shift_seq,
			sedi.shift_start_time,
			ifnull(sedi.shift_end_time, dateadd(day, 1, sedi.shift_start_time), sedi.shift_end_time) as 'shift_end_time'
		from
			micros.shift_emp_dtl sedi
		where 
			/*clock_in between '2013-08-05' and '2013-08-05 23:59:59'*/
			sedi.shift_start_time > dateadd(day, num_days,now())
		order by
			emp_seq
	)sed on (sed.emp_seq = c.emp_seq) and (date(sed.shift_start_time) = business_date) and ((c.chk_open_date_time >= sed.shift_start_time) and (c.chk_open_date_time <= sed.shift_end_time))
	
	where
		/*c.chk_open_date_time between '2013-08-05' and '2013-08-05 23:59:59'*/
		c.chk_open_date_time > dateadd(day, num_days,now())
    group by
        check_number, c.chk_open_date_time,c.chk_clsd_date_time, sed.shift_start_time, sed.shift_end_time, sed.shift_seq, table_name, cover_count, edef.first_name, edef.last_name,
        c.emp_seq, edef.obj_num, edef.payroll_id, job_name, check_added
	
	order by
		business_date, meridiem, edef.last_name, edef.first_name, c.emp_seq		

END

 

[pmegan]

The way this is written, you can't get the void time. Those are transaction based and this is summing up by check number.
What you can do is add max(t.start_date_tm) as 'last_trans', to your select statement and ,[last_trans] to the order by clause. This will give you a column with the last time each check was accessed and sort each employee's checks by the last transaction time. When an employee's totals don't match on the two reports, you can look at the time on the end of shift report and see if any checks were accessed after that point.

As for the posting procedures, you can add procedures 116 and 140 to the autosequences that run your reports. That will bring the employee totals current before printing the report.

Just out of curiosity, does your stored procedure take a while to run? I always minimize my grouping to the bare minimum in temp or inline virtual tables to speed things up, but I started doing that with Res 2.0 so it may not be as much of an issue anymore.

 

[raphael75]

Just to verify, 116 on our system is "40Col - RVC Menu Item Group Sales Summary" and 140 is "Daily SYS Sales Detail, Order Types, And Labor". Are those correct?

The stored procedure takes just 2 or 3 seconds to run. It's very fast.

 

[raphael75]

Also, thank you for the info about the last accessed time!

 

[raphael75]

The stored procedures I see in those 2 reports are:

sp_R_sys_totals
sp_R_time_card_ttls
sp_R_family_group

Are those the ones that will get the final net sales for the server?

 

[pmegan]

No, you're looking at the report templates. You need to look at the autosequence steps. You want stored procedure steps calling procedures 116 and 140 in your incremental and full posting autosequences, as well as any other employee posting procedures you may have running. The default setup changes from dealer to dealer, sometimes site to site.

The touchscreen button your staff hits to run the end of shift report at the workstation is linked to an autosequence. I generally set up those autosequences like this:
Step 1: stored procedure 116 (or 140, depending on the source table/view)
Step 2: report

This makes sure that everything is posted before running the report. Look at the report in Crystal. If the totals are coming from v_R_employee and v_R_employee_trk, use sp116. If they come from v_R_employee and v_R_employee_trk use sp140.

The old school way of posting, and the way the default Micros reports come in, is to call the posting procedure inside the report. Look at emp_101.rpt and you'll see 3 data sources: v_R_employee, v_R_employee_trk and sp_R_employee_sales_tracking. The last one is a posting procedure with one field, sp_error. Putting that into the report as a suppressed field causes the procedure to run before generating totals. It works great with the default reports, and modifications of them, but I've had problems using this method with reports written from scratch with custom tables & procedures. Those errors are pretty hard to nail down, especially with the staggeringly poor error messages in Res5+.
Also, Crystal Reports disables SQL Expressions in reports with a stored procedure, and I like using them much more than the SQLCall() function.

 

[pmegan]

sp_R_sys_totals = system totals
sp_R_time_card_ttls = time card totals
sp_R_family_group = family group totals

This may be causing your problem. Since you already have posting procedures in your report, try adding in sp_R_employee_sales_tracking or sp_R_rvc_employee_sales_tracking.

 

[raphael75]

I got with our Micros guy and he is adding those 2 stored procedures to the Autosequence Steps to run before my report. I will let you know if it fixes it. Thank you!!

 

[raphael75]

To manually call these, could I do something like this at the top of my SQL above?

call micros.sp_R_sys_totals;
call micros.sp_R_time_card_ttls;
call micros.sp_R_family_group;
call micros.sp_R_employee_sales_tracking;
call micros.sp_R_rvc_employee_sales_tracking;

Thanks!

 

[raphael75]

So if my stored procedure looks like this @ the top it should work?

ALTER PROCEDURE "custom"."sp_mdf_get_sale_dtl" ()
BEGIN
    
    
	declare num_days int;
	
 	set num_days = -15;

    call micros.sp_R_sys_totals;
    call micros.sp_R_time_card_ttls;
    call micros.sp_R_family_group;
    call micros.sp_R_employee_sales_tracking;
    call micros.sp_R_rvc_employee_sales_tracking;


	select

When I run it I get an error, but I can't see what the error is. Could it be a permissions issue? Thanks!

 

[pmegan]

You really have to find out what the error is. If the procedure works without the posting calls they're undoubtedly the cause, but you need to find out why. does it work if you call it from dbisql using the custom user? Dba user? That will tell you pretty quickly if it's a permissions issue.

Another possibility depends on what's being done with the procedure. Those posting procedures all return a single row/column result set. Assuming that the "select" at the bottom of your code box above is generating the data for a report or export, that may be where the problem is coming from. The way this is written, you're sp is returning 6 result sets. The first 5 are single rows with just an sp_error column, and the last is your data. If that's the case you can try doing it this way:

begin

    declare @dummy int;
    declare num_days int;

    set num_days = -15;

    select sp_error into @dummy from micros.sp_R_sys_totals();
    select sp_error into @dummy from micros.sp_R_time_card_ttls();
    select sp_error into @dummy from micros.sp_R_family_group();
    select sp_error into @dummy from micros.sp_R_employee_sales_tracking();
    select sp_error into @dummy from micros.sp_R_rvc_employee_sales_tracking();

    select

That will direct the results form those posting procedures into a dummy variable instead of back to the client as a result set.

 

[raphael75]

pmegan,

Thank you, that worked! Now to see if it fixes the posting issues we've been having...

 

[raphael75]

It appears that adding those 5 stored procedure calls fixed the issue at 2 of the stores we are testing at, but not at the other 3. Are there any other stored procedures I could call to make sure Micros has finished posting all totals before it calculates the server's net sales? Thanks!