Micros 3700 - tax report

[AM_WG]

We are looking for a report that shows the menu items sold and the taxes collected. Has anyone created something like this? Or is there a report that I am missing?

 

[Colin518]

I have only seen tax collected by revenue centers in the normal 3700 installs, someone may have written something custom. i too would love to see it. hunting down tax discrepancies is a pain in the ass.

 

[Wildbar]

What exactly do you want to see? Item by item and the total tax for that item?

 

[AM_WG]

We would like the date something was sold, the item, the price charged before tax, the tax and the total.It would be nice if it was grouped by the revenue center. Does that help?

 

[Colin518]

i think the closest you will get to something like that is using Transaction Analyser and filtering by the specific menu item, date range, and amount. It wont show you the tax collected but knowing the tax class associated you can do some quick math to get the total tax collected.

 

[Colin518]

Perhaps that and a combination of a the report like Consolidated SYS Menu Item Sales Summary would work out.

 

[AM_WG]

This report would be mainly for the Dept of Revenue in case they wanted to verify the taxes we collected. Trans analyser doesn't show taxes, I wish it did. The consolidated menu item sales summary groups items, I need to be able to show what we are charging for each item. Thanks for the replies.

 

[AM_WG]

I would be okay doing a sql query for this, but I'm having a hard time finding the table index that ties the item or menu class that ties into the tax table.

 

[Colin518]

It may be exclusive to my state but the Dep of revenue had never cared to drill down that far into tax collected for specific items. Generally the System balance reports that have the taxable and non taxable groups broken also have the total tax collected as a fixed percentage of a taxable department sales.

 

[Wildbar]

Going to need to join the menu item, menu item class, and tax tables together. You will probably have to so the tax math in the query as i dont think micros actually gives a per item tax total. Was going to throw together a custom report when i have some time.

 

[AM_WG]

You rock bro.

 

[AM_WG]

This might give you a head start

WhilePrintingRecords;

BooleanVar ActiveFlag1;

if ActiveFlag1 then
DecimalNumberToText(Sum ({v_R_rvc_menuitem_fam_grp.P1_sales_total}, {v_R_rvc_menuitem_fam_grp.menuitem_number}) +
Sum ({v_R_rvc_menuitem_fam_grp.P1_discount_total}, {v_R_rvc_menuitem_fam_grp.menuitem_number}))
else
"";


select * from micros.mi_def where obj_num='9230188'
select * from micros.mi_type_class_def where mi_type_seq='116'
select * from micros.tax_class_def where tax_class_seq='3'
select * from micros.tax_rate_def
select sum(percentage) from micros.tax_rate_def where tax_rate_seq between 1 and 2

select mi_type_seq from micros.mi_def where obj_num='9230188'




select sum(percentage) from micros.tax_rate_def where tax_rate_seq between 1 and 2{
select TaxRate.percentage from micros.mi_def where obj_num=%1

 

[Colin518]

I suck at crystal but if Wildbar is trying i would also kill for a report on that will produce this info. Hopefully ill figure out out to run in from Auto sequences and reports

 

[AM_WG]

I'm getting closer.

select mi_def.obj_num, mi_def.name_1, mi_def.mi_type_seq, mi_def.mlvl_class_seq, mi_type_class_def.tax_class_seq--, tax_rate_def.percentage
from micros.mi_def
full outer join micros.mi_type_class_def
on mi_type_class_def.obj_num=mi_def.obj_num
full outer join micros.tax_rate_def
on mi_type_class_def.obj_num=micros.mi_type_class_def.obj_num
where mi_def.obj_num is not NULL;

 

[AM_WG]

This is where is starts getting over my head. I need to check this table micros.mi_dtl and check for which of the tax flags, shown below, are active. Then I know which of the taxes to add together to get the total tax charged on an item. I am familiar with programming, but not sure of the syntax when doing it inside a query.

I have attached the schema as well, in case someone wants to check it out. I am trying to find the table(s) that link the menu information to the tax information.

 

[Wildbar]

Thanks for doing this. Just been holiday overwhelmed. Still trying to get to this.
Will need to use "if" and "else" (if ob_tax_1_active='T' then.... else....)
Think its the mi_type_seq you will need to follow to get the tax class.
Should be able to get to this on the weekend

 

[AM_WG]

Much appreciated Wildbar

 

[AM_WG]

Hey Wildbar, Did you have a chance to work on this?

 

[AM_WG]

Hey Wildbar,
Any updates?

 

[mvelazquez]

I hope this could help You:

SELECT MI.obj_num AS Obj_Num
		,name_1 AS Name
		,RVC.obj_num AS RVC_ID
        ,RVC.name AS RVC
		,SUM(D.rpt_cnt) AS Quantity
        ,SUM(D.rpt_ttl) AS SubTotal
        ,SUM(D.inclusive_tax_ttl) AS TAX
		,SUM(D.chk_ttl) AS Total
	FROM micros.trans_dtl T
	INNER JOIN micros.mi_dtl M ON T.trans_seq = M.trans_seq
	INNER JOIN micros.dtl D ON T.trans_seq = D.trans_seq
		AND D.dtl_seq = M.dtl_seq
	INNER JOIN micros.mi_def MI ON M.mi_seq = MI.mi_seq
    INNER JOIN micros.rvc_def RVC ON D.orig_rvc_seq = RVC.obj_num
	WHERE T.training_status = 0
		AND business_date between '20200110' and '20200115'
	GROUP BY 
		MI.obj_num
        ,MI.name_1
		,M.mi_seq
		,RVC.obj_num
        ,RVC.name
	ORDER BY MI.obj_num

RESULT:
Obj_Num,Name,RVC_ID,RVC,Quantity,SubTotal,TAX,Total
10,'PRODUCT X',1,'LOCAL',12,237.05,37.95,275.00
10,'PRODUCT X',2,'Drive Thru',4,47.41,7.59,55.00
11,'PRODUCT y',2,'Drive Thru',11,455.18,72.82,528.00
11,'PRODUCT y',1,'LOCAL',12,413.80,66.20,480.00