Micros RES 3700 AutoSequences

[GAMicrosUser]

Hello, we have identified a reporting issue within the Micros system regarding till adjustments.

The till count data in the cm_count_var_detail table does not get updated after an adjustment is made until a till report is run. In some cases, the store level staff does not run a report so the data in this table is not updated to reflect the adjusted till counts.

This becomes an issue because our data warehouse is extracting the data from this table and utilizing it for reporting purposes.

We have identified a specific stored procedure that will update the table for us. We would like to schedule this stored procedure to run nightly via Autosequences.

Unfortunately this stored procedure (spcm_R_TillOverShort) is not in the list of stored procedures to be called when setting up the autosequences.

Can anyone give me some direction on how I can add stored procedures to the existing list of stored procedures that the autosequences can be set to run by default?

I have tried manually inserting into some tables but cannot get it to function properly.

Any help would be greatly appreciated.

 

[DrZogg]

Hi,

There are 3 ways to run a report. 1st by joining and running the data report runtime, and that may involve bringing several tables and joining them when you want the result. 2nd (my preferrred) is to create a view and extract the data from a single table. 3rd is to run a stored procedure to produce the data & extract is from the DB.

the name spcm_R_TillOverShort means Stored Procedure, Cash Management, Report ... You want to be able to call this Stored Procedure from Autosequences & Reports.

I wont tell you how to, but what you need to do is create the following:

1. Grant all on the spcm_r_ table to the required DB user groups
2. Insert into micros.stored_proc_def values (including your spcm_r_ in the values required by the stored procedure
definition)
3. Insert into micros.stored_proc_parm_def your first named paramaters (first values)
4. Insert into micros.stored_proc_parm_def your second named paramaters (second values)

To know what parameters to pass it you will need to know the workings of the stored procedure (count_seq, bus_date would be a fair guess)

 

[GAMicrosUser]

Thanks for your reply mrpinks3.

I have updated the permissions on the SP granting execute rights to everyone for testing purposes per step 1 above.

I have also inserted the SP in the micros.stored_proc_def table per step 2 above.

There are no parameters passed into the spcm_R_TillOverShort procedure so I skipped steps 3 and 4 above.

When I setup the procedure in AutoSequence steps via PosCfg it locks up my test machine as soon as I select the spcm_R_TillOverShort in the drop down list view.

You have any further info that might explain why the system locks up on configuring this procedure.

Any assistance is appreciated.

 

[MikeRose]

Can you create an autsequence that calls a bat file that runs the stored procedure. or possibly a sim that will run the procedure then process the report.

I know this is not the native way you were anticipating, but would solve your problem.

 

[GAMicrosUser]

Hey MikeRose, thanks for the reply.

I had considered the bat file approach but I run into an issue with permissions. The procedure I am trying to call requires DBA privellages. I do not have a user at our store level with DBA privellages.

I attempted to create a new SP using the Custom user but it does not have create procedure permissions.

I have changed the DBA password here in our lab so I can get everything to work but at the store level I am not going to have the luxury of a DBA user.

I may request the DBA password from our Micros rep and hide the connection string in a VB application or something.

Looks like a DBA connection string to at least create the new SP is going to be necessary.

 

[MikeRose]

Hawksfan,

Why is the DBA password a mystery? Do you still have sites on 3.x or does MICROS do all the DB maintenance? I am a bit confused why you wouldn't have DBA permissions. Hope all is well.