


-- find the qty available.. 
CREATE OR REPLACE FUNCTION invfifo_unitcost_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);
    
      
BEGIN
       
   -- price at a specific qty.
    SELECT
            invfifo_landedunitcost
        
        INTO
            v_ret
        FROM
            invdetailview
        WHERE
            invfifo_qty_after <= i_qty
            AND    
            invdetail_location_id = i_location_id
            AND
            invhist_itemsite_id = i_itemsite_id
            AND
            invdetail_qty > 0
            AND
            invhist_transtype = 'RP' -- purchases only!
            AND
            invfifo_void = 0
        LIMIT
            1;
    
    IF  FOUND THEN
         return v_ret;
         
    END IF;    
    
    return 0;
    
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_unitcost_at_qty(integer, integer, numeric(18, 6))
  OWNER TO admin;
          
          -- find the qty available.. 
CREATE OR REPLACE FUNCTION invfifo_unitcost_at_date(integer,  date)
    RETURNS  numeric(18, 6) 
    
AS $BODY$
DECLARE
    i_itemsite_id  ALIAS FOR $1;
   
    i_date           ALIAS FOR $2;
    v_ret               numeric(18, 6);
    
      
BEGIN
       
   -- price at a specific qty.
    SELECT
        invfifo_landedunitcost
        
        INTO
            v_ret
        FROM
            invdetailview
        WHERE
            invhist_transdate < i_date
            AND
            invhist_itemsite_id = i_itemsite_id
            AND
            invhist_transtype = 'RP' -- purchases only!
            AND
            invdetail_qty > 0
            AND
            invfifo_void = 0
        ORDER BY
            invhist_transdate  DESC
        LIMIT
            1;
    
    IF  FOUND THEN
         return v_ret;
         
    END IF;
    
    
    
    -- not found, then try next purchase price..
    
    SELECT
        invfifo_landedunitcost
        
        INTO
            v_ret
        FROM
            invdetailview
        WHERE
            invhist_transdate >= i_date
            AND
            invhist_itemsite_id = i_itemsite_id
            AND
            invhist_transtype = 'RP' -- purchases only!
            AND
            invdetail_qty > 0
            AND
            invfifo_void = 0
        ORDER BY
            invhist_transdate  ASC
        LIMIT
            1;
    
    IF  FOUND THEN
         return v_ret;
         
    END IF;    
    
    -- if we really can not find any purchases, then we should use the stdcost?

    SELECT stdcost(itemsite_item_id) INTO v_ret FROM itemsite where itemsite_id = i_itemsite_id;

    return v_ret;


    --return 0;
    
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_unitcost_at_date(integer, date)
  OWNER TO admin;
          
          