create or replace function get_maxinventory(p_customer customer.customer_id%type, dp_id distributionpoint.distributionpoint_id%type, p_product product.product_id%type,  p_size sizedefinition.sizedefinition_id%type, p_fm finishingmethod.finishingmethod_id%type, countsum varchar)
RETURN NUMBER
AS
res number:=0;
v_scode varchar2(30);
begin
  select sd.code into v_scode from sizedefinition sd where sd.sizedefinition_id=p_size;
 if countsum='sum' then
   if v_scode='*' then
     select /*+parallel*/ nvl( sum(weinv.maxinventory),0) into res from
     wearer wea inner join weareremployment wemp on wea.wearer_id= wemp.wearer_id
     inner join wearerinventory weinv on weinv.weareremployment_id=wemp.weareremployment_id
     where wea.customer_id = p_customer
     and weinv.product_id = p_product
     and weinv.finishingmethod_id = p_fm
     and wemp.distributionpoint_id=dp_id;
   end if;
   if v_scode<>'*' then
     select /*+parallel*/  nvl(sum(weinv.maxinventory),0) into res from
     wearer wea inner join weareremployment wemp on wea.wearer_id= wemp.wearer_id
     inner join wearerinventory weinv on weinv.weareremployment_id=wemp.weareremployment_id
     where wea.customer_id = p_customer
     and weinv.product_id = p_product
     and weinv.sizedefinition_id=p_size
     and weinv.finishingmethod_id = p_fm
     and wemp.distributionpoint_id=dp_id;
   end if;
   end if;
   if countsum='count' then
        if v_scode='*' then
       select /*+parallel*/ count(winv.wearerinventory_id) into res from wearerinventory winv, weareremployment were
        where winv.product_id = p_product
        and winv.finishingmethod_id = p_fm
        and winv.weareremployment_id = were.weareremployment_id and were.distributionpoint_id=dp_id ;
    end if;
             if v_scode<>'*' then
       select /*+parallel*/ count(winv.wearerinventory_id) into res from wearerinventory winv, weareremployment were
        where winv.product_id = p_product
        and winv.finishingmethod_id = p_fm
        and winv.sizedefinition_id=p_size
        and winv.weareremployment_id = were.weareremployment_id and were.distributionpoint_id=dp_id ;
     end if;

 end if;
 return res;
end;
/