create or replace view V_TurnoverOnRouteLevel as
select
  plant.code PlantCode, plant.name plantName
, coalesce(depot.code, 'NULL') DepotCode, depot.description DepotDesc
, bu.code businessunitCode, bu.description businessunitDesc
, cu.customernumber, cu.name CustomerName, cu.customer_id, cu.debtor_id
, cast(( case CONCATENATEWITHBLANK(addressDlv.Street, addressDlv.Street2) when ' '
           then CONCATENATEWITHBLANK(addressCu.Street, addressCu.Street2)
             else CONCATENATEWITHBLANK(addressDlv.Street, addressDlv.Street2) end )
               as varchar2(255)) Street
, coalesce(addressDlv.City, addressCu.City) City, day.daynumber
, coalesce(addressDlv.Zipcode, addressCu.Zipcode) Zipcode, dsk.code DeliveryschemeCode
, route.routenumber, route.description RouteDesc, route.route_id
, (select day_desc.description from day_desc where day_desc.day_id = day.day_id
  and day_desc.language_id = languages.language_id) DayDesc
, rst.stopnumber, deliverypoint.code deliverypointCode
, emp.name Driver, truck.code TruckCode, truck.description TruckDesc
, buusg.usergroup_id UsergroupID, languages.code LanguageCode
, (coalesce((select sum(ih.totalamountexclvat) / f_GetDaysForLastMonthInvoices(ih.billtocustomer_link_id, day.daynumber)
   from invoiceheader ih, status st where ih.billtocustomer_link_id = cu.debtor_id --and ih.route_id = route.route_id
   and st.status_id = ih.status_id and st.code = '2' --Finalized
   and ih.invoicedate between add_months(trunc(sysdate,'mm'),-1) and last_day(add_months(trunc(sysdate,'mm'),-1))
   and ih.startdate < sysdate and ih.enddate < sysdate
   group by ih.billtocustomer_link_id), 0)) va1ue
, (select cur.invoiceamountdecimals from currency cur where cur.currency_id = cu.currency_id) invoiceamountdecimals
, (select count(distinct(cdd.day_id)) from customerdeliveryday cdd where cdd.customer_id = cu.customer_id) NrOfDeliveryDays
from --- observation: useful line are 'doubled' by UsergroupID
  deliveryschemesperstop dskt
  inner join customerdeliveryscheme cudsk
        inner join deliveryscheme dsk on cudsk.deliveryscheme_id = dsk.deliveryscheme_id
  on dskt.customer_id = cudsk.customer_id and dskt.deliveryscheme_id = cudsk.deliveryscheme_id and dskt.day_id = cudsk.day_id
  inner join routestop rst
        inner join customer cu
              inner join businessunit bu 
                    inner join businessunitsperusergroup buusg on bu.businessunit_id = buusg.businessunit_id
              on cu.defaultbusinessunit_id = bu.businessunit_id
              inner join address addressCu on cu.visitaddress_id = addressCu.address_id
        on rst.customer_id = cu.customer_id
        inner join route
              left outer join truck on route.truck_id = truck.truck_id
              left outer join depot on route.depot_id = depot.depot_id
              left outer join employee emp on route.driver_id = emp.employee_id
              inner join plant on route.plant_id = plant.plant_id
              inner join day on route.drivingday_id = day.day_id 
        on rst.route_id = route.route_id    
        left outer join deliverypoint 
             inner join address addressDlv on deliverypoint.deliveryaddress_id = addressDlv.address_id
        on deliverypoint.deliverypoint_id = rst.deliverypoint_id
  on dskt.routestop_id = rst.routestop_id and dskt.customer_id = rst.customer_id
, languages
where GETCURRENTDATETIME() between cu.dateactive and cu.dateinactive
      and route.drivingday_id = dskt.day_id  
      and route.active = 'Y'
      and rst.dateactive <= GetCurrentDate()
      and rst.dateinactive > GetCurrentDate()
/
