CREATE OR REPLACE VIEW V_ROUTELIST AS
SELECT 1 linetype, --Packing Notes 
  d.routestop_id, GETYEAROFWEEK(A.DRIVINGDATE) drivingyear, GETISOWEEK(A.DRIVINGDATE) drivingweek,
  f.notenumber, p.customer_id,
  F_SHOW_ROUTESTOP_STD(d.stop_id, d.routestop_id, d.stoptype, s.plant_id) showstop,
  A.ROUTECONTROL_ID, D.DELIVERYPOINT_ID STOP_DELIVERYPOINT_ID, N.DELIVERYPOINT_ID PN_DELIVERYPOINT_ID,
  S.CODE PLANTCODE, B.ROUTENUMBER, B.DESCRIPTION ROUTEDESCRIPTION, A.DRIVINGDATE,
  C.CODE DEPOTCODE, C.DESCRIPTION DEPOTDESCRIPTION, A.LOADINGDOCK, A.STARTTIME,
  (CASE WHEN a.drivernumber = 0 then X.NAME
        else (select employee.name from employee where employee.employeenumber = a.drivernumber
             and employee.plant_id = s.plant_id)
   END) DRIVERNAME,
   coalesce (a.truckcode, Y.CODE) TRUCKCODE,
  (CASE WHEN a.truckcode is null then Y.DESCRIPTION
        ELSE (select truck.description from truck where truck.code = a.truckcode
             and truck.plant_id = s.plant_id)
   END) TRUCKDESCRIPTION, P.Vatnumber, 
  D.STOPNUMBER, P.CUSTOMERNUMBER, P.NAME CUSTOMERNAME,insertspace_after_x_characters(to_char(P.CUSTOMERNUMBER),3) custnr,
  T.STREET DELIVERYADDRESS1, T.STREET2 DELIVERYADDRESS2, T.CITY, T.STATE, T.ZIPCODE, t.housenumber, t.housenumberext,
  '' C_DELIVERYADDRESS1, '' C_DELIVERYADDRESS2, '' C_CITY, '' C_STATE, '' C_ZIPCODE, null c_housenumber, null c_housenumberext,
  d.routelistinstructions,
  Z.NAME CONTACTPERSONNAME, Z.PHONE CONTACTPERSONPHONE, z.mobilephone, P.PHONENO CUSTOMERPHONE, D.STOP_ID,
  O.DEPARTMENTNUMBER, M.DISTRIBUTIONPOINTNUMBER, N.CODE DELIVERYPOINTCODE,
  O.DESCRIPTION DEPARTMENTDESC, M.DESCRIPTION DISTRIBUTIONPOINTDESC, N.DESCRIPTION DELIVERYPOINTDESC,
  I.CODE PRODUCTCODE, J.CODE SIZECODE, R.CODE FINISHINGMETHODCODE, L.CODE DELIVERYSCHEMECODE,
  (SELECT w.description FROM PRODUCT_DESC W WHERE I.PRODUCT_ID = W.PRODUCT_ID AND W.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) PRODUCTDESCRIPTION,
  H.STARTDATE DELIVERYSCHEMESTARTDATE,
  H.DELIVEREDQTY QTYDELIVERED, 0 QTYEXPECTED, 'P' endcontract,
  LANGUAGES.CODE LANGUAGECODE, 1 detail, V.routenumber movedroutenumber, V.description movedroutedescription, d.status,
  P.COUNTRY_ID CUSTOMERCOUNTRYID, W.DESCRIPTION COUNTRY, S.COUNTRY_ID PLANTCOUNTRYID,
  (select sum(cdd.typeofmerchadise) from CUSTOMERDELIVERYDAY CDD where P.CUSTOMER_ID = CDD.CUSTOMER_ID
     AND coalesce((select rm.drivingday_id from route rm where E.Route_Id=rm.route_id), B.DRIVINGDAY_ID) = CDD.DAY_ID
     and cdd.typeofmerchadise >=1 and cdd.typeofmerchadise <=2) typeofmerchadise,
  B.ROUTE_ID, coalesce(d.stoptype, 0) stoptype, rp.reportparam_id,
  coalesce(clm.showlockeronroutelist, 'N') showlockeronroutelist,
  I.PUTUPTYPE PUTUPTYPE_PRODUCT, G.PUTUPTYPE PUTUPTYPE_PLPC,
  case when H.PACKINGNOTELINE_ID is null then 0 else 1 end STRAIGHTEVENEXCHANGE,
  G.DELIVEREDAMOUNT QTYDELIVERED_PNL, P.DELNOTEFORBULKPUTUPITEMS
  , (SELECT HH.STARTTIME FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = N.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_START_TIME
  , (SELECT HH.ENDTIME FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = N.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_END_TIME
  , n.breakstarttime breakstarttime, n.breakendtime breakendtime
  , B.STARTTIME ROUTE_START_TIME, E.TRAVELMINUTES STOP_TRAVELMINUTES, E.SERVICEMINUTES STOP_SERVICEMINUTES
  , p.dateactive, p.dateinactive
  , (SELECT to_char(HH.STARTTIME,'fmhhfm:MI:AM') FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = N.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_START_TIMECH
  , (SELECT to_char(HH.ENDTIME,'fmhhfm:MI:AM') FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = N.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_END_TIMECH
  , to_char(n.breakstarttime,'fmHH:MI:AM') breakstarttimeCH, to_char(n.breakendtime,'fmHH:MI:AM') breakendtimeCH
  , to_char(B.STARTTIME,'fmhhfm:MI:AM') ROUTE_START_TIMECH
  , to_char(B.STARTTIME,'fmhh24fm:MI') ROUTE_START_TIMECH24
  , to_char(A.STARTTIME,'fmhhfm:MI:AM') STARTTIMECH
  , cont.showonroutelist, cont.contactnotes, cont.status contactstatus, a.plant_id
 , cast((select GetSubstringOnLength(pl.email,0,40) from plant pl where pl.plant_id = a.plant_id) as varchar2(40)) plantemail
 , (select cri.routedescription from CustomerRouteInfo cri where cri.customer_id = p.customer_id) drivinginstructions
FROM reportparam rp
     inner join ROUTECONTROL A
           inner join  ROUTE B
                 left outer join DEPOT C ON B.DEPOT_ID = C.DEPOT_ID
                 left outer join EMPLOYEE X ON B.DRIVER_ID = X.EMPLOYEE_ID
                 left outer join TRUCK Y ON B.TRUCK_ID = Y.TRUCK_ID
           on A.ROUTE_ID = B.ROUTE_ID
           inner join STOP D
                 left outer join V_MOVEDOUTSTOPS V on D.STOP_ID = V.movedtostop_id
                 left outer join CONTACTPERSON Z ON D.CONTACTPERSON_ID = Z.CONTACTPERSON_ID
                 left outer join ROUTESTOP E ON D.ROUTESTOP_ID = E.ROUTESTOP_ID
                 inner join PACKINGNOTE F 
                       inner join PACKINGNOTELINE G 
                             left outer join V_PACKINGNOTELINEPERDELSCH_EXT H
                                  left outer join DELIVERYSCHEME L ON H.DELIVERYSCHEME_ID = L.DELIVERYSCHEME_ID
                             ON G.PACKINGNOTELINE_ID = H.PACKINGNOTELINE_ID
                             inner join PRODUCT I on G.PRODUCT_ID = I.PRODUCT_ID
                             inner join SIZEDEFINITION J on G.SIZEDEFINITION_ID = J.SIZEDEFINITION_ID 
                             inner join FINISHINGMETHOD R on G.FINISHINGMETHOD_ID = R.FINISHINGMETHOD_ID
                       on F.PACKINGNOTE_ID = G.PACKINGNOTE_ID  
                       inner join DISTRIBUTIONPOINT M 
                             inner join DEPARTMENT O ON M.DEPARTMENT_ID = O.DEPARTMENT_ID
                       on F.DISTRIBUTIONPOINT_ID = M.DISTRIBUTIONPOINT_ID
                       inner join DELIVERYPOINT N 
                             inner join ADDRESS T on N.DELIVERYADDRESS_ID = T.ADDRESS_ID
                       on F.DELIVERYPOINT_ID = N.DELIVERYPOINT_ID
                       inner join STATUS U on F.STATUS_ID = U.STATUS_ID
                 ON D.STOP_ID = F.STOP_ID
                 inner join CUSTOMER P 
                       left outer join customerlockermanagement clm ON p.customer_id = clm.customer_id
                       left outer join contact cont ON cont.contact_id = coalesce(f_getcontactonroutelist(p.customer_id), -1)
                       inner join COUNTRY W on P.COUNTRY_ID = W.COUNTRY_ID
                 on D.CUSTOMER_LINK_ID = P.CUSTOMER_ID
           on A.ROUTECONTROL_ID = D.ROUTECONTROL_ID
           inner join PLANT S on A.PLANT_ID = S.PLANT_ID
     on rp.NUMBER_VALUE = a.ROUTECONTROL_ID
     , LANGUAGES
WHERE U.STATUSTYPE = 'PN' AND U.CODE <> '40' and d.status in (0, 3, 4)
UNION ALL          -- 2 --
SELECT 2 linetype, -- Straight Even Return
  d.routestop_id, GETYEAROFWEEK(A.DRIVINGDATE) drivingyear, GETISOWEEK(A.DRIVINGDATE) drivingweek,
  0 notenumber, p.customer_id,
  F_SHOW_ROUTESTOP_STD(d.stop_id, d.routestop_id, d.stoptype, s.plant_id) showstop,
  A.ROUTECONTROL_ID, D.DELIVERYPOINT_ID STOPDELIVERYPOINT_ID, N.DELIVERYPOINT_ID PN_DELIVERYPOINT_ID,
  S.CODE PLANTCODE, B.ROUTENUMBER, B.DESCRIPTION ROUTEDESCRIPTION, A.DRIVINGDATE,
  C.CODE DEPOTCODE, C.DESCRIPTION DEPOTDESCRIPTION, A.LOADINGDOCK, A.STARTTIME,
  (CASE WHEN a.drivernumber = 0 then X.NAME
        else (select employee.name from employee where employee.employeenumber = a.drivernumber
             and employee.plant_id = s.plant_id)
   END) DRIVERNAME,
  coalesce (a.truckcode, Y.CODE) TRUCKCODE,
  (CASE WHEN a.truckcode is null then Y.DESCRIPTION
        ELSE (select truck.description from truck where truck.code = a.truckcode
             and truck.plant_id = s.plant_id)
   END) TRUCKDESCRIPTION, P.Vatnumber, 
  D.STOPNUMBER, P.CUSTOMERNUMBER, P.NAME CUSTOMERNAME,insertspace_after_x_characters(to_char(P.CUSTOMERNUMBER),3) custnr,
  T.STREET DELIVERYADDRESS1, T.STREET2 DELIVERYADDRESS2, T.CITY, T.STATE, T.ZIPCODE, t.housenumber, t.housenumberext,
  '' C_DELIVERYADDRESS1, '' C_DELIVERYADDRESS2, '' C_CITY, '' C_STATE, '' C_ZIPCODE, null c_housenumber, null c_housenumberext,
  d.routelistinstructions,
  Z.NAME CONTACTPERSONNAME, Z.PHONE CONTACTPERSONPHONE, z.mobilephone,P.PHONENO CUSTOMERPHONE,  D.STOP_ID,
  O.DEPARTMENTNUMBER, M.DISTRIBUTIONPOINTNUMBER, N.CODE DELIVERYPOINTCODE,
  O.DESCRIPTION DEPARTMENTDESC, M.DESCRIPTION DISTRIBUTIONPOINTDESC, N.DESCRIPTION DELIVERYPOINTDESC,
  I.CODE PRODUCTCODE, J.CODE SIZECODE, R.CODE FINISHINGMETHODCODE, L.CODE DELIVERYSCHEMECODE,
  (SELECT description FROM PRODUCT_DESC W WHERE I.PRODUCT_ID = W.PRODUCT_ID AND W.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) PRODUCTDESCRIPTION,
  NULL DELIVERYSCHEMESTARTDATE,
  0 QTYDELIVERED, K.QTYEXPECTED QTYEXPECTED, k.endcontract,
  LANGUAGES.CODE LANGUAGECODE, 1 detail, V.routenumber movedroutenumber, V.description movedroutedescription, d.status,
  P.COUNTRY_ID CUSTOMERCOUNTRYID, W.DESCRIPTION COUNTRY, S.COUNTRY_ID PLANTCOUNTRYID,
  (select sum(cdd.typeofmerchadise) from CUSTOMERDELIVERYDAY CDD where P.CUSTOMER_ID = CDD.CUSTOMER_ID
     AND coalesce((select rm.drivingday_id from route rm where E.Route_Id=rm.route_id), B.DRIVINGDAY_ID) = CDD.DAY_ID
     and cdd.typeofmerchadise >=1 and cdd.typeofmerchadise <=2) typeofmerchadise,
  B.ROUTE_ID, coalesce(d.stoptype, 0) stoptype, rp.reportparam_id,
  coalesce(clm.showlockeronroutelist, 'N') showlockeronroutelist,
  I.PUTUPTYPE PUTUPTYPE_PRODUCT, 0 PUTUPTYPE_PLPC, 1 STRAIGHTEVENEXCHANGE,
  0 QTYDELIVERED_PNL, P.DELNOTEFORBULKPUTUPITEMS
  , (SELECT HH.STARTTIME FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = N.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_START_TIME
  , (SELECT HH.ENDTIME  FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = N.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_END_TIME
  ,  n.breakstarttime breakstarttime, n.breakendtime breakendtime
  , B.STARTTIME ROUTE_START_TIME, E.TRAVELMINUTES STOP_TRAVELMINUTES, E.SERVICEMINUTES STOP_SERVICEMINUTES
  , p.dateactive, p.dateinactive
  , (SELECT to_char(HH.STARTTIME,'fmHHfm:MI:AM') FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = N.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_START_TIMECH
  , (SELECT to_char(HH.ENDTIME,'fmHHfm:MI:AM') FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = N.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_END_TIMECH
  , to_char(n.breakstarttime,'fmHHfm:MI:AM') breakstarttimeCH, to_char(n.breakendtime,'fmHH:MI:AM') breakendtimeCH
  , to_char(B.STARTTIME,'fmHHfm:MI:AM') ROUTE_START_TIMECH
  , to_char(B.STARTTIME,'fmHH24fm:MI') ROUTE_START_TIMECH24
  , to_char(A.STARTTIME,'fmHHfm:MI:AM') STARTTIMECH
  , cont.showonroutelist, cont.contactnotes, cont.status contactstatus, a.plant_id
 , cast((select GetSubstringOnLength(pl.email,0,40) from plant pl where pl.plant_id = a.plant_id) as varchar2(40)) plantemail
 , (select cri.routedescription from CustomerRouteInfo cri where cri.customer_id = p.customer_id) drivinginstructions
FROM reportparam rp 
     inner join ROUTECONTROL A
           inner join ROUTE B
                 left outer join DEPOT C ON B.DEPOT_ID = C.DEPOT_ID
                 left outer join EMPLOYEE X ON B.DRIVER_ID = X.EMPLOYEE_ID
                 left outer join TRUCK Y ON B.TRUCK_ID = Y.TRUCK_ID
           on A.ROUTE_ID = B.ROUTE_ID
           inner join STOP D
                 left outer join V_MOVEDOUTSTOPS V on D.STOP_ID = V.movedtostop_id
                 left outer join CONTACTPERSON Z ON D.CONTACTPERSON_ID = Z.CONTACTPERSON_ID
                 left outer join ROUTESTOP E ON D.ROUTESTOP_ID = E.ROUTESTOP_ID
                 inner join STRAIGHTEVENRETURN K 
                       inner join PRODUCT I on K.PRODUCT_ID = I.PRODUCT_ID
                       inner join SIZEDEFINITION J on K.SIZEDEFINITION_ID = J.SIZEDEFINITION_ID
                       inner join FINISHINGMETHOD R on K.FINISHINGMETHOD_ID = R.FINISHINGMETHOD_ID
                       inner join DELIVERYSCHEME L on K.DELIVERYSCHEME_ID = L.DELIVERYSCHEME_ID
                       inner join DISTRIBUTIONPOINT M 
                             inner join DELIVERYPOINT N 
                                   inner join ADDRESS T on N.DELIVERYADDRESS_ID = T.ADDRESS_ID
                             on M.DELIVERYPOINT_ID = N.DELIVERYPOINT_ID
                             inner join DEPARTMENT O on  M.DEPARTMENT_ID = O.DEPARTMENT_ID
                       on K.DISTRIBUTIONPOINT_ID = M.DISTRIBUTIONPOINT_ID 
                       inner join STATUS U on K.STATUS_ID = U.STATUS_ID
                 on D.STOP_ID = K.STOP_ID
                 inner join CUSTOMER P
                       left outer join customerlockermanagement clm ON p.customer_id = clm.customer_id
                       left outer join contact cont ON cont.contact_id = coalesce(f_getcontactonroutelist(p.customer_id), -1)
                       inner join COUNTRY W on P.COUNTRY_ID = W.COUNTRY_ID 
                 on D.CUSTOMER_LINK_ID = P.CUSTOMER_ID
           on A.ROUTECONTROL_ID = D.ROUTECONTROL_ID
           inner join PLANT S on A.PLANT_ID = S.PLANT_ID
     on rp.NUMBER_VALUE = a.ROUTECONTROL_ID
     , LANGUAGES 
WHERE U.STATUSTYPE = 'SR' AND U.CODE <> '30' and d.status in (0, 3, 4)  
UNION ALL  -- 3 --
SELECT 3 linetype, -- No Packing Notes - used only for HEADER in rpt
  d.routestop_id, GETYEAROFWEEK(A.DRIVINGDATE) drivingyear, GETISOWEEK(A.DRIVINGDATE) drivingweek,
  0 notenumber, p.customer_id,
  F_SHOW_ROUTESTOP_STD(d.stop_id, d.routestop_id, d.stoptype, s.plant_id) showstop,
  A.ROUTECONTROL_ID, D.DELIVERYPOINT_ID STOPDELIVERYPOINT_ID, 0 PN_DELIVERYPOINT_ID,
  S.CODE PLANTCODE, B.ROUTENUMBER, B.DESCRIPTION ROUTEDESCRIPTION, A.DRIVINGDATE,
  C.CODE DEPOTCODE, C.DESCRIPTION DEPOTDESCRIPTION, A.LOADINGDOCK, A.STARTTIME,
  (CASE WHEN a.drivernumber = 0 then X.NAME
        else (select employee.name from employee where employee.employeenumber = a.drivernumber
             and employee.plant_id = s.plant_id)
   END) DRIVERNAME,
  coalesce (a.truckcode, Y.CODE) TRUCKCODE,
  (CASE WHEN a.truckcode is null then Y.DESCRIPTION
        ELSE (select truck.description from truck where truck.code = a.truckcode
             and truck.plant_id = s.plant_id)
   END) TRUCKDESCRIPTION, P.Vatnumber, 
  D.STOPNUMBER, P.CUSTOMERNUMBER, P.NAME CUSTOMERNAME,insertspace_after_x_characters(to_char(P.CUSTOMERNUMBER),3) custnr,
  T.STREET DELIVERYADDRESS1, T.STREET2 DELIVERYADDRESS2, T.CITY, T.STATE, T.ZIPCODE, t.housenumber, t.housenumberext,
  T2.STREET C_DELIVERYADDRESS1, T2.STREET2 C_DELIVERYADDRESS2, T2.CITY C_CITY, T2.STATE C_STATE, T2.ZIPCODE C_ZIPCODE,
  t2.housenumber c_housenumber, t2.housenumberext c_housenumberext, d.routelistinstructions,
  Z.NAME CONTACTPERSONNAME, Z.PHONE CONTACTPERSONPHONE,z.mobilephone, P.PHONENO CUSTOMERPHONE, D.STOP_ID,
  -1 departmentnumber, -1 distributionpointnumber, dp.code deliverypointcode,
  '' departmentdesc, '' DISTRIBUTIONPOINTDESC, dp.description DELIVERYPOINTDESC,
  '' PRODUCTCODE,'' SIZECODE,'' FINISHINGMETHODCODE,'' DELIVERYSCHEMECODE,'' PRODUCTDESCRIPTION,null deliveryschemestartdate,0 QTYDELIVERED,0 QTYEXPECTED,'A' endcontract,
  LANGUAGES.CODE LANGUAGECODE, 0 detail, V.routenumber movedroutenumber, V.description movedroutedescription, d.status,
  P.COUNTRY_ID CUSTOMERCOUNTRYID, W.DESCRIPTION COUNTRY, S.COUNTRY_ID PLANTCOUNTRYID,
  (select sum(cdd.typeofmerchadise) from CUSTOMERDELIVERYDAY CDD where P.CUSTOMER_ID = CDD.CUSTOMER_ID
     AND coalesce((select rm.drivingday_id from route rm where E.Route_Id=rm.route_id), B.DRIVINGDAY_ID) = CDD.DAY_ID
     and cdd.typeofmerchadise >= 1 and cdd.typeofmerchadise <= 2) typeofmerchadise,
  B.ROUTE_ID, coalesce(d.stoptype, 0) stoptype, rp.reportparam_id,
  coalesce(clm.showlockeronroutelist, 'N') showlockeronroutelist,
  0 PUTUPTYPE_PRODUCT, 0 PUTUPTYPE_PLPC, 0 STRAIGHTEVENEXCHANGE,
  0 QTYDELIVERED_PNL, P.DELNOTEFORBULKPUTUPITEMS
  , (SELECT HH.STARTTIME FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = DP.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_START_TIME
  , (SELECT HH.ENDTIME  FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = DP.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_END_TIME
             ,dp.breakstarttime breakstarttime, dp.breakendtime breakendtime
  , B.STARTTIME ROUTE_START_TIME, E.TRAVELMINUTES ROUTESTOP_TRAVELMINUTES, E.SERVICEMINUTES ROUTESTOP_SERVICEMINUTES
  , p.dateactive, p.dateinactive
  , (SELECT to_char(HH.STARTTIME,'fmHHfm:MI:AM') FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = dp.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_START_TIMECH
  , (SELECT to_char(HH.ENDTIME,'fmHHfm:MI:AM') FROM deliverypointopeninghour HH
            WHERE HH.DELIVERYPOINT_ID = dp.DELIVERYPOINT_ID AND HH.DAY_ID = B.DRIVINGDAY_ID) DEL_POINT_END_TIMECH
  , to_char(dp.breakstarttime,'fmHHfm:MI:AM') breakstarttimeCH, to_char(dp.breakendtime,'fmHH:MI:AM') breakendtimeCH
  , to_char(B.STARTTIME,'fmHHfm:MI:AM') ROUTE_START_TIMECH
  , to_char(B.STARTTIME,'fmHH24fm:MI') ROUTE_START_TIMECH24
  , to_char(A.STARTTIME,'fmHHfm:MI:AM') STARTTIMECH
  , cont.showonroutelist, cont.contactnotes, cont.status contactstatus, a.plant_id
 , cast((select GetSubstringOnLength(pl.email,0,40) from plant pl where pl.plant_id = a.plant_id) as varchar2(40)) plantemail
 , (select cri.routedescription from CustomerRouteInfo cri where cri.customer_id = p.customer_id) drivinginstructions
FROM reportparam rp
     inner join ROUTECONTROL A
           inner join ROUTE B
                 left outer join DEPOT C ON B.DEPOT_ID = C.DEPOT_ID
                 left outer join EMPLOYEE X ON B.DRIVER_ID = X.EMPLOYEE_ID
                 left outer join TRUCK Y ON B.TRUCK_ID = Y.TRUCK_ID
           on A.ROUTE_ID = B.ROUTE_ID
           inner join STOP D
                 left outer join V_MOVEDOUTSTOPS V on D.STOP_ID = V.movedtostop_id
                 left outer join CONTACTPERSON Z ON D.CONTACTPERSON_ID = Z.CONTACTPERSON_ID
                 left outer join ROUTESTOP E ON D.ROUTESTOP_ID = E.ROUTESTOP_ID
                 left outer join DELIVERYPOINT DP 
                      inner JOIN ADDRESS T ON DP.DELIVERYADDRESS_ID = T.ADDRESS_ID
                 ON D.DELIVERYPOINT_ID = DP.DELIVERYPOINT_ID
                 inner join CUSTOMER P
                       left outer join customerlockermanagement clm ON p.customer_id = clm.customer_id
                       left outer join contact cont ON cont.contact_id = coalesce(f_getcontactonroutelist(p.customer_id), -1)
                       inner join COUNTRY W on P.COUNTRY_ID = W.COUNTRY_ID
                       inner join ADDRESS T2 on P.VISITADDRESS_ID = T2.ADDRESS_ID
                 ON D.CUSTOMER_LINK_ID = P.CUSTOMER_ID
           on A.ROUTECONTROL_ID = D.ROUTECONTROL_ID
           inner join PLANT S on A.PLANT_ID = S.PLANT_ID           
     on rp.NUMBER_VALUE = a.ROUTECONTROL_ID
     , LANGUAGES 
WHERE d.status in (0, 3, 4)
/