create or replace function f_get_OSIA(plpcid number, inventorydate date)
  return number is
  res             number;
  v_plpcid        number;
  v_inventorydate date;
begin
  v_plpcid        := plpcid;
  v_inventorydate := inventorydate;
  select (case
           when a.SOILCOUNTMETHOD > 0 then
            (select COALESCE(sum(OSIA.QUANTITY), 0)
               from ONSITEINVENTORYADJUSTMENT OSIA, STANDARDDELIVERYLINE B
              where OSIA.PRODUCTLISTPERDISTRIBPOINT_ID =
                    B.PRODUCTLISTPERDISTRIBPOINT_ID
                and B.PRODUCTLISTPERCUSTOMER_ID = a.productlistpercustomer_id
                and OSIA.INVENTORYDATE <= v_inventorydate)
           else
            0
         end)
    into res
    from productlistpercustomer a
   where a.productlistpercustomer_id = v_plpcid;
  return res;
end f_get_OSIA;
/
