Hey Everyone,
Our accounting dept wants to add the tax collected or percentage for an item to the Consolidated Menu Item Sales Detail report. I have some experience with SQL, but it's been a long time since I used it on a regular basis and could use some help putting this query together. Below are the queries I put together to determine what tables and fields I need to work with.
select * from micros.mi_def where obj_num='9230188' - Menu item table, var will be supplied by batch process. Obj_num is menu item #
select * from micros.mi_type_class_def where mi_type_seq='116' - mi_type_seq is the menu item class number. The class controls taxes charged for that item.
select * from micros.tax_class_def where tax_class_seq='3' - tax_class is the menu item tax classification
Result:
tax_class_seq,obj_num,name,ob_tax_1_active,ob_tax_2_active,ob_tax_3_active,ob_tax_4_active,ob_tax_5_active,ob_tax_6_active,ob_tax_7_active,ob_tax_8_active,ob_rsvd01,ob_rsvd02,ob_rsvd03,ob_rsvd04,ob_rsvd05,ob_rsvd06,ob_rsvd07,ob_rsvd08,last_updated_by,last_updated_date
3,102,'Wine/Liqu Tax = State+Liq','T','T','F','F','F','F','F','F','F','F','F','F','F','F','F','F',462,1997-06-16 16:06:18.437
the 'T''s about that are in bold say to charge each of those taxes defined in the tax_rate_def table
select * from micros.tax_rate_def - Table with tax rates.
Finally, sum the values that returned True. In this case, both rates in the field percentage from records that have seq 1 and 2 need to be totalled. The sum of the percentages that are marked as true is what I am trying to pull into the report.
select sum(percentage) from micros.tax_rate_def where tax_rate_seq between 1 and 2
We are using the Sybase DB that comes with Micros 5.7 build 5341. All help is appreciated. Thanks.
Andy
Our accounting dept wants to add the tax collected or percentage for an item to the Consolidated Menu Item Sales Detail report. I have some experience with SQL, but it's been a long time since I used it on a regular basis and could use some help putting this query together. Below are the queries I put together to determine what tables and fields I need to work with.
select * from micros.mi_def where obj_num='9230188' - Menu item table, var will be supplied by batch process. Obj_num is menu item #
select * from micros.mi_type_class_def where mi_type_seq='116' - mi_type_seq is the menu item class number. The class controls taxes charged for that item.
select * from micros.tax_class_def where tax_class_seq='3' - tax_class is the menu item tax classification
Result:
tax_class_seq,obj_num,name,ob_tax_1_active,ob_tax_2_active,ob_tax_3_active,ob_tax_4_active,ob_tax_5_active,ob_tax_6_active,ob_tax_7_active,ob_tax_8_active,ob_rsvd01,ob_rsvd02,ob_rsvd03,ob_rsvd04,ob_rsvd05,ob_rsvd06,ob_rsvd07,ob_rsvd08,last_updated_by,last_updated_date
3,102,'Wine/Liqu Tax = State+Liq','T','T','F','F','F','F','F','F','F','F','F','F','F','F','F','F',462,1997-06-16 16:06:18.437
the 'T''s about that are in bold say to charge each of those taxes defined in the tax_rate_def table
select * from micros.tax_rate_def - Table with tax rates.
Finally, sum the values that returned True. In this case, both rates in the field percentage from records that have seq 1 and 2 need to be totalled. The sum of the percentages that are marked as true is what I am trying to pull into the report.
select sum(percentage) from micros.tax_rate_def where tax_rate_seq between 1 and 2
We are using the Sybase DB that comes with Micros 5.7 build 5341. All help is appreciated. Thanks.
Andy
