create or replace view v_residualvaluecharges as
select distinct cu.customer_id,  cu.customernumber, cu.name customerName, address.city, rc.code reasonCode
, rc_d.description reasonDesc
, prd_lk.code prodectLKcode
, prd_lk_d.description prodectLKdesc
, GETDATEONLY(och.outofcircdate) outofcircdate, och.status
, och.primaryid, sum(och.amount) amount
, och.forbusinessunit_id, lan.code languageCode
, och.processbusinessunit_id
from
  languages lan
  inner join OutOfCirculationHistory och on och.chargetype = 1  
  inner join customer cu 
        inner join address on cu.visitaddress_id = address.address_id
  on och.customer_link_id = cu.customer_id
  inner join reasoncode rc on och.reasoncode_id = rc.reasoncode_id
  left outer join reasoncode_desc rc_d on rc_d.reasoncode_id = och.reasoncode_id and rc_d.language_id = lan.language_id
  inner join product_link prd_lk on och.product_link_id = prd_lk.product_link_id
  left outer join product_link_desc prd_lk_d on prd_lk_d.product_link_id = och.product_link_id and prd_lk_d.language_id = lan.language_id
where rc.garmentreason = 'Y'
        and rc.invoiceadjustment <> 1 -- no invoice header adjustment reasons
            and rc.inventorytrigger not in (4, 7, 8) -- no flat rate adj or kill loss charge
group by cu.customer_id,
  cu.customernumber, cu.name, address.city
, rc.code
, rc_d.description
, prd_lk.code, prd_lk_d.description
, GETDATEONLY(och.outofcircdate)
, och.status
, och.primaryid
, och.forbusinessunit_id--, languages.language_id
, lan.code
, och.processbusinessunit_id-- HAVING ( och.status = 1 AND  sum(och.amount)<>0 ) OR och.status <> 1 -- ! --
;
/