


-- find the qty available.. 
CREATE OR REPLACE FUNCTION invfifo_cost_at_qty(integer, integer, numeric(18, 6))
    RETURNS  numeric(18, 6) 
    
AS $BODY$
DECLARE
    i_itemsite_id  ALIAS FOR $1;
    i_location_id  ALIAS FOR $2;
    i_qty           ALIAS FOR $3;
    v_ret               numeric(18, 6);
    v_after              numeric(18, 6);
      
BEGIN
       
  
    v_after :=0;
   -- price at a specific qty.
    SELECT   
        invfifo_cost_before +
            ((i_qty - invfifo_qty_before ) * invfifo_landedunitcost),
            
        invfifo_qty_after
        INTO
            v_ret,
            v_after
        FROM
                invdetailview
        WHERE
            invfifo_qty_before <= i_qty
            AND
            invfifo_qty_after >= i_qty
            AND    
            invdetail_location_id = i_location_id
            AND
            invhist_itemsite_id = i_itemsite_id
            AND
            invdetail_qty > 0
            AND
            invfifo_void = 0
        LIMIT
            1;
    
    IF NOT FOUND THEN
        RETURN  -1;
    END IF;    
    IF v_after < 1 THEN
        RETURN -1;
    END IF;
    
    return v_ret;
    
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_cost_at_qty(integer, integer, numeric(18, 6))
  OWNER TO admin;
          

-- find the last purchase price if it exists..

CREATE OR REPLACE FUNCTION invfifo_cost_estimate(i_itemsite_id integer)
    RETURNS  numeric(18, 6) 
    
AS $BODY$
DECLARE
    
    v_ret numeric(18, 6);
      
BEGIN
       
   -- price at a specific qty.
    SELECT   
        invfifo_landedunitcost
        INTO
            v_ret
        FROM
            invdetailview
        WHERE
            invhist_itemsite_id = i_itemsite_id
            AND
            invdetail_qty > 0
            AND
            invfifo_void = 0
            AND
            invhist_transtype = 'RP'
            
        ORDER BY
            invfifo_qty_after DESC
        LIMIT
            1;
    
    IF NOT FOUND THEN
        RETURN  -1;
    END IF;    
    
    return v_ret;
    
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_cost_estimate(i_itemsite_id integer)
  OWNER TO admin;
                    