DIFOT.~sql

/*decalre variables These are being declared via the stored procedure - just need to redeclare cust so as we can get the rates*/
var cust varchar2(20)
exec :cust := 'CONNECTVIC'
var ordernum varchar2(20)
exec :ordernum := '1363806'
var stock varchar2(20)
exec :stock := 'COURIER'
var source varchar2(20)
exec :source := 'BSPRINTNSW'
var sAnalysis varchar2(20) /*VerbalOrderEntryFee*/
exec SELECT  RM_ANAL INTO :sAnalysis FROM RM where RM_CUST = :cust;
var anal varchar2(20)
exec :anal := '21VICP'
var start_date varchar2(20)
exec :start_date := To_Date('01-Feb-2014')
var end_date varchar2(20)
exec :end_date := To_Date('28-Feb-2014')
var cust2 varchar2(20)
exec :cust2 := 'BEYONDBLUE'
var warehouse varchar2(20)
exec :warehouse := 'SYDNEY'
var warehouse2 varchar2(20)
exec :warehouse2 := 'MELBOURNE'
var month_date varchar2(20)
exec :month_date := substr(:end_date,4,3)
var year_date varchar2(20)
exec :year_date := substr(:end_date,8,2)




/*Total Despatches by Month all custs grouped by warehouse/grouped cust */  --  1.5s  Total Count 15381

SELECT (
          CASE
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'S' THEN 'SYDNEY'
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'H' THEN 'SYDNEY'
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'R' THEN 'SYDNEY'
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'M' THEN 'MELBOURNE'
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'O' THEN 'OBSOLETE'
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'D' THEN 'DMMETLIFE'
            ELSE 'NOLOCN'
            END) AS Warehouse,
             sGroupCust,
            Count(*) AS Total,
            'B- Despatches' AS "Type"
FROM  PWIN175.SL s INNER JOIN SH h ON s.SL_ORDER = h.SH_ORDER
      LEFT JOIN Tmp_Group_Cust r ON r.sCust = h.SH_CUST
      --RIGHT JOIN SD d ON d.SD_ORDER = h.SH_ORDER
      INNER JOIN RM r2 ON r2.RM_CUST = h.SH_CUST
      INNER JOIN IL l ON l.IL_LOCN = s.SL_LOCN
      INNER JOIN ST t ON t.ST_PICK = s.SL_PICK
WHERE s.SL_EDIT_DATE >= :start_date AND s.SL_EDIT_DATE <= :end_date
AND s.SL_LINE = 1
AND s.SL_PSLIP IS NOT NULL
AND h.SH_STATUS <> 3
AND h.SH_CAMPAIGN NOT IN( 'ADMIN','OBSOLETE')
AND r2.RM_ACTIVE = 1   --This was the problem
GROUP BY ROLLUP ((CASE
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'S' THEN 'SYDNEY'
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'H' THEN 'SYDNEY'
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'R' THEN 'SYDNEY'
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'M' THEN 'MELBOURNE'
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'O' THEN 'OBSOLETE'
            WHEN Upper(SubStr(s.SL_LOCN,0,1)) = 'D' THEN 'DMMETLIFE'
            ELSE 'NOLOCN'
            END),
             sGroupCust )