{
  "facility_sql": "select /*FACILITY_TAG*/ \r\n       dt.SaleDate,\r\n       dt.TaxType, \r\n       dt.Account, \r\n       dt.Product, \r\n       dt.Amount, \r\n       dt.Tax\r\nfrom (\r\n    -- Taxable items\r\n    select \r\n        DATE(py.postdate) as SaleDate,\r\n        CONCAT(tt.TAX_NAME, ' - Taxable') AS TaxType, \r\n        CONCAT(p.REV_CATEGORY, if(LENGTH(p.REVENUE_SUBCATEGORY) > 0, CONCAT(':', p.REVENUE_SUBCATEGORY), '')) as Account, \r\n        p.description as Product,\r\n        convert(sum(ii.price), decimal(10,2)) as Amount,\r\n        convert(round(((tt.PERCENT100 + tt.INCLUSIVERATEHINT100)/100.0) * sum(ii.price) * 100) / 100, decimal(10,2)) as Tax  \r\n    from invoices i\r\n        join invoice_items ii on i.invoice_id = ii.invoice_id \r\n        join products p on ii.product_id = p.product_id  \r\n        join payments py on i.payment_id = py.payment_id  \r\n        join tax_types tt\r\n    where DATE(py.postdate) >= '/*START_DATE*/'\r\n      and DATE(py.postdate) <= '/*END_DATE*/'\r\n      and py.settled = 1  \r\n      and not ii.IS_TAX_ITEM\r\n      and i.payment_id > 0 \r\n      and i.voidedinvoice = 0 \r\n      and py.declined = 0 \r\n      and py.voidedpayment = 0 \r\n      and not ii.tax_exempt\r\n      and p.tax_type_list like CONCAT('% ', tt.TAXTYPE_ID, ' %')\r\n    group by tt.TAXTYPE_ID, i.invoice_id, ii.invoice_item_id, p.product_id, DATE(py.postdate)\r\n\r\n    UNION ALL\r\n\r\n    -- NonTaxable items\r\n    select \r\n        DATE(py.postdate) as SaleDate,\r\n        CONCAT(tt.TAX_NAME, ' - NonTaxable') AS TaxType, \r\n        CONCAT(p.REV_CATEGORY, if(LENGTH(p.REVENUE_SUBCATEGORY) > 0, CONCAT(':', p.REVENUE_SUBCATEGORY), '')) as Account, \r\n        p.description as Product,\r\n        convert(sum(ii.price), decimal(10,2)) as Amount,\r\n        null as Tax \r\n    from invoices i\r\n        join invoice_items ii on i.invoice_id = ii.invoice_id \r\n        join products p on ii.product_id = p.product_id  \r\n        join payments py on i.payment_id = py.payment_id  \r\n        join tax_types tt\r\n    where DATE(py.postdate) >= '/*START_DATE*/'\r\n      and DATE(py.postdate) <= '/*END_DATE*/'\r\n      and py.settled = 1  \r\n      and not ii.IS_TAX_ITEM\r\n      and i.payment_id > 0 \r\n      and i.voidedinvoice = 0 \r\n      and py.declined = 0 \r\n      and py.voidedpayment = 0 \r\n      and (\r\n            (not ii.tax_exempt) \r\n        and (isnull(p.tax_type_list) or not (p.tax_type_list like CONCAT('% ', tt.TAXTYPE_ID, ' %')))\r\n      )\r\n    group by tt.TAXTYPE_ID, i.invoice_id, ii.invoice_item_id, p.product_id, DATE(py.postdate)\r\n) dt\r\norder by dt.SaleDate, dt.TaxType DESC, dt.Account, dt.Product;",
  "merge_sql": "",
  "json": "{\r\n  \"facility_column_tag\": \"/*FACILITY_TAG*/\",\r\n  \"wants_start_date\": {\r\n    \"tag\": \"/*START_DATE*/\"\r\n  },\r\n  \"wants_end_date\": {\r\n    \"tag\": \"/*END_DATE*/\"\r\n  }\r\n}",
  "name": "Sales Tax Details Itemized"
}