SELECT s.sku                  AS SKU,
       s.stktype_code         AS STKTYPE_CODE,
       i.sku_descr            AS SKU_DESCR,
       br.cluster_code        AS CLUSTER_CODE,
       br.cluster_descr       AS CLUSTER_DESCR,
       MAX(s.curr_rsp_cr)     AS PRICE,
       SUM(s.qty_on_hand)     AS SOH,
      SUM(s.qty_in_goods - s.qty_in_supp + s.qty_on_repair 
      ) AS PLUS_STOCK_IN,
      SUM(s.qty_out_goods)   AS LESS_STOCK_OUT,
      SUM(s.qty_dp)          AS LESS_DISPLAY,
      (SUM(s.qty_on_hand) + SUM(s.qty_in_goods - s.qty_in_supp + s.qty_on_repair      ) - sum(s.qty_out_goods) - sum(s.qty_dp)
      ) AS OVERSOLD,
      (select coalesce(sum(out_qty),0)::numeric 
       from gir_outstanding 
       where cluster_brn = br.cluster_code 
       and sku = s.sku 
       and stktype_code = s.stktype_code
      )  AS SC_STK_ON_ORDER,
      case when (sum(s.qty_on_hand) + 
                 sum(s.qty_in_goods - s.qty_in_supp + s.qty_on_repair) - 
                 sum(s.qty_dp) - 
                 sum(s.qty_out_goods) +
		 (select coalesce(sum(out_qty),0)::numeric
		 from gir_outstanding
                 where cluster_brn = br.cluster_code
                 and sku = s.sku
                 and stktype_code = s.stktype_code)
                ) < 0 
          then abs(sum(s.qty_on_hand) + 
               sum(s.qty_in_goods - s.qty_in_supp + s.qty_on_repair) - 
               sum(s.qty_dp) - 
               sum(s.qty_out_goods) +
               (select coalesce(sum(out_qty),0)::numeric
               from gir_outstanding
               where cluster_brn = br.cluster_code
               and sku = s.sku
               and stktype_code = s.stktype_code)
		  ) 
          else 0 
      end AS ORDER_RQRD,
      (   SELECT  coalesce(sum(k.qty_on_hand),0)
          FROM    stmst_sku k, 
                  master_branch_descr b
          WHERE   k.brn_code = b.brn_code
          AND     k.sku = s.sku
          AND     k.stktype_code = s.stktype_code
          AND     b.brn_code <>  b.cluster_code
          AND     b.cluster_code =  br.cluster_code
          AND     k.fpp_code = '200408'
      ) AS BR_SOH
FROM   stmst_sku s, 
       master_branch_descr br,
       master_sku_descr i,
       old_sku os
WHERE  s.sku = i.sku
AND    s.brn_code = br.brn_code
AND    s.fpp_code = '200408'
AND    s.group_code = i.group_code
AND    i.sku = os.sku
AND    br.cluster_code = os.cluster_brn
GROUP BY s.sku,
         s.stktype_code,
         i.sku_descr,
         br.cluster_code,
         br.cluster_descr
HAVING ((sum(s.qty_on_hand) + sum(s.qty_in_goods) - sum(s.qty_dp) - sum(s.qty_out_goods)) < 0);
