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