MICROS 9700/Simphony SQL Manual

[Chicken730]

In the 8700 there was a SQL Manual that showed the tables, fields and References. Does anyone have one for the 9700 and Symphony 1/2 systems?
Looking for the references for the status field in the Checks and Check_Detail tables in the MSRSPOS and SIMPPOS Database.
Also, Has anyone ran across the situation where items rung up on a check, then suspended, show up as sales in NetView even when the check is still open?

Thanks!

 

[Jackba]

suspended checks and reappearing of checks usually occurs on our simphony sites and the most Micros says about is that our network is not configured or is not good , f anyone has any ideas I would also be interested in knowing

 

[hosehead78]

The only one I know of was for the 9700 system and it was only for Location_Activity. I've never heard of one for the other tables, nor have I heard of one for Simphony. I'll keep an eye out for one.

@Chicken730, What specifically are you looking for?

@Jackba, What are you looking for as well, or are having issue with?

I've taken a look at the Column, and have also put out a request to Micros on this. See if I can get anything from their end.

 

[Chicken730]

I am looking for the Status Flag for the status field in the Checks and Check Detail Files. In the 8700 SQL manual it was in appendix C.
The Status Data looks like this.
000000000000000000000000000000000000001000000000
I think every 4 numbers is converted to a hex value of 8,4,2,1 and that cross refs to a status option. IE a Value of 8 would equal a previous Round Entry.
At least that is the way it worked in the 8700/9700


Thanks
Matt

 

[Jackba]

@hosehead78 My issue is with simphony 1.6 and 2.6 we keep on have reappearing checks...basically checks can be closed for one week the all of a sudden they reappear on the screens have you ever encountered such a scenario?

 

[hosehead78]

@Chicken730, Have a look at the 9700_MAN_SQL.pdf (

https://app.box.com/s/06k05x7ex75airsepmm078swckqrb3nu),

starting at page 287. Not sure at the moment, which goes with which. But it might help a little.

@Jackba, No I haven't seen or heard of this in Simphony. The site I work at is 9700 v4. I haven't had much time playing with Simphony v1 or v2. If the SQL is the same with 9700 as with Simphony you can run a open check report at the end of each work day before your EOD kicks off and see what checks are still open.

a new file named 'getOpenChecks.sh'. Copy the code below and save it. Then from the server, open cygwin or a cmd prompt and run the file.
This is export a list of all the open checks at the time the script is ran.

Remember, this uses the 8700sql.exe file, so there should be an equivalent on the Simphony server. I don't know off hand, as I dont have a Simphony lab up and running at the moment.

# Starting RVC Number; Be sure to change TNumRVC to the total number of RVCs. Example: 10 Revuenue Centers (RVC), TNumRVC = 10
RVC=1
TNumRVC=10

while [ $RVC -le $TNumRVC ]   
do
  echo "Be Patient, this may take a few minutes... Export for RVC" $RVC
  echo "OUTPUT_TO [PathToSaveFile]/open_chk_dtl_info[$RVC].txt" > cmd_file
  echo "FIELD_SEPARATOR , " >> cmd_file
  echo "SELECT * FROM open_chk_dtl_info.$RVC" >> cmd_file

  d:/micros/les/pos/9700/bin/8700sql -ef cmd_file
  
  (( RVC++ ))
done
sleep 1
rm cmd_file

Here's an SQL Query for 9700 system, you might be able to edit it for Simphony. I think I have documents on the Simphony system I can take a look at and see what else I can check. If you have them, let me know.

------------------------------------------------------------
-- Return Open Checks --
--	Author: Chad Freiling
--	Date: March 2015
--		Connection to MICROSDB
-- 
--	System: Micros 9700 v3.6 and above
-- 
--	Returns current Open Checks on the system
--		List by RVC, Emp. Last Name, Check Number
------------------------------------------------------------

SELECT
	rvc.ObjectNumber as RVC
	,emp.ObjectNumber as #
	,laEmp.lastname
	,laEmp.firstname
	,c.CashierID
	,c.CheckNumber
	,c.CheckOpen
	,c.TableOpen
	,c.CheckClose
	,c.Status
	,c.ClosedSeat
	,c.GroupNum
	,c.OrdTypeID
	,c.AlternateID
	,c.CloseStatus
	,c.CheckPostingTime

FROM MCRSPOS.microsdb.CHECKS c

Inner Join MCRSPOS.microsdb.REVENUE_CENTER rvc on c.RevCtrID = rvc.RevCtrID
join MCRSPOS.microsdb.EMPLOYEE emp on c.EmployeeID = emp.EmployeeID
join LOCATION_ACTIVITY_DB.dbo.EMPLOYEE laEmp on emp.ObjectNumber = laEmp.posRef

Where CheckClose is NULL
--And rvc.ObjectNumber in ('2','3')		-- If you want to Filter by By RVC only; If by single rvc use a single RVC number ('RVCNUM')

Order by rvc.ObjectNumber,laEmp.lastname, c.CheckNumber asc

 

[Jackba]

@hosehead78 Thanks I will Have look and see if I can get something on sim 1 or 2 with the code