
-- short cut to get the line before


CREATE OR REPLACE FUNCTION invfifo_cost_before_in(NUMERIC, integer,integer)
    RETURNS  NUMERIC 
AS $BODY$
DECLARE        
           
    i_invfifo_qty_before  ALIAS FOR $1;
    i_location_id ALIAS FOR $2;
    i_itemsite_id ALIAS FOR $3;
    
   
    v_cost_before  NUMERIC;
     

BEGIN

   SELECT
            COALESCE(invfifo_cost_after,0.0)
        INTO
            v_cost_before
        FROM
            invdetailview
        WHERE
            invfifo_qty_after = i_invfifo_qty_before
            AND            
            invdetail_location_id = i_location_id
            AND
            invhist_itemsite_id = i_itemsite_id
            AND
            invdetail_qty > 0
            AND
            invfifo_void = 0
        ORDER BY
            invfifo_qty_after DESC
        LIMIT 1;
        
    RETURN COALESCE(v_cost_before,0);
   
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_cost_before_in(NUMERIC, integer,integer)
  OWNER TO admin;
     