CREATE FUNCTION match_items(int4) RETURNS bool AS '
declare
  varmatchid alias for $1;
  varqty tblListInventoryItems_cache.Each%TYPE;
  -- request and shipment record loop vars
  rloop record;
  sloop record;
begin

  -- find (each itemuniqueid or varmatchid) with inventorylevelid=3, ie. does not have a location, locationid=0
  for rloop in select cache.*, r.requestdate 
    from tbllistinventoryitems_cache as cache, tbllistrequestsentitems as rsi, tbllistrequestitems as ri, tbllistrequests as r 
    where cache.itemuniqueid=rsi.itemuniqueid and rsi.requestitemid=ri.itemuniqueid and ri.requestid=r.requestid 
      and cache.inventorylevelid=3
      and (varmatchid=null or varmatchid=cache.itemuniqueid)
    order by cache.urgencyid, r.requestdate 
    loop

    varqty := 0 - rloop.each;
    raise notice ''looking for match for requestitem % qty %'',rloop.itemuniqueid,varqty;

    -- for each matching inventory item that is in the warehouse, ie. inventorylevelid=1
--XXX serial number
    for sloop in select sum(each), serialnumber, bestbefore, locationid
      from tbllistinventoryitems_cache
      where treeid=rloop.treeid and sc1optionid=rloop.sc1optionid and sc2optionid=rloop.sc2optionid 
        and sc3optionid=rloop.sc3optionid
--        and serialnumber=rloop.serialnumber
        and inventorylevelid<=2
      group by treeid, sc1optionid, sc2optionid, sc3optionid, serialnumber, bestbefore, locationid
      having sum(each)>0
      order by bestbefore, sum
      loop
raise notice ''sloop.serialnumber=%, rloop.serialnumber=%'',sloop.serialnumber,rloop.serialnumber;
if sloop.serialnumber=rloop.serialnumber then
  raise notice '' SN match'';
else
  raise notice '' SN mis-match'';
end if;
      -- the above sql found at least 1 match
      -- raise notice ''  available qty (sum(each)) = %, requestqty (varqty) = %'',sloop.sum,varqty;

      -- if qty available is less than requested qty, insert request for qty available at that location
      if sloop.sum < varqty then
        raise notice ''** insert identical where itemuniqueid=%, qty=%, locationid=%'', rloop.itemuniqueid, sloop.sum, sloop.locationid;
        insert into tbllistrequestsentitems select itemuniqueid, requestitemid, treeid,
            sc1optionid, sc2optionid, sc3optionid, serialnumber, bestbefore,
            sloop.sum, 4, sloop.locationid
          from tbllistrequestsentitems where itemuniqueid=rloop.itemuniqueid;
      else  -- qty available at this location equal to or greater then requested qty
        -- we can match everything
        raise notice ''** update where itemuniqueid=%, qty=%, unitid=4, locationid=%'',rloop.itemuniqueid,varqty,sloop.locationid;
        update tbllistrequestsentitems set qty=varqty, unitid=4, locationid=sloop.locationid where itemuniqueid=rloop.itemuniqueid;
      end if;
      -- decrement requested qty by available, may leave requested qty negative
      varqty := varqty - sloop.sum;
      -- stop searching shipment items (sloop), we are done with this request item, go to the next request (rloop)
      exit when varqty<=0;
    end loop;
    if FOUND then
      raise notice ''FOUND'';
    else
      raise notice ''not FOUND'';
    end if;
    if FOUND and varqty>0 then
      -- if the shipment loop (sloop) found anything we must have modified something so
      --   we need to check qty of remaining unmatched items
      --   if whats remaining>0 then we did not match everything, update whats left with no location
      --   note: this will trigger another call to match_items(), 
      --     this is why we need to check if the shipment loop modified anything
      raise notice ''** update where itemuniqueid=%, qty=%, unitid=4 (locationid implicit 0)'',rloop.itemuniqueid,varqty;
      update tbllistrequestsentitems set qty=varqty, unitid=4 where itemuniqueid=rloop.itemuniqueid;
    end if;
  end loop;

  return true;
end;
' LANGUAGE 'plpgsql';