-- Invoices Missing Delivery Date
-- This report shows invoices that have items but no delivery date
-- FILTER: {
--   filter: "link",
--   column: "link",
--   "params": {
--         "blank": true,
--         "display": "View Invoice"
--     }
-- }
-- FILTER: {
--   filter: "link",
--   column: "explore",
--   "params": {
--         "blank": true,
--         "display": "Explore"
--     }
-- }
-- FILTER: {
--   filter: "date",
--   column: "create_date",
--   params: {
--     "format": "m/d/y"
--   }
-- }


select o.id,
       o.location_id,
       concat('https://simple123.com/business/locations/', o.location_id, '/invoices/', o.id) as link,
       concat('https://simple123.com/business/locations/', o.location_id, '/invoices/', o.id, '/explore') as explore


FROM simplebrsprod.deal_cart o,
     simplebrsprod.business_locationtoregion ltr
WHERE
        o."polyclassC" = 'Invoice'
  AND ltr."polyclassC" = 'Account'
  AND o.delivery_date IS NOT NULL
  AND o.location_id IS NOT NULL
  AND o.distributor_account_id = ltr.id
  AND ltr.employee_wholesale_id IS NOT NULL
  AND o.time_period_month is null;