

-- this is triggered on a transfer where it needs to look ahead to match qty          


CREATE OR REPLACE FUNCTION invfifo_update_from_invdetail_ahead(integer)
    RETURNS  NUMERIC
AS $BODY$
DECLARE    
    i_invdetail_id  ALIAS FOR $1;
    
    v_invhist_id INTEGER;
    v_location_id INTEGER;
    v_itemsite_id INTEGER;
    v_next_up INTEGER;
    v_end_qty NUMERIC;
    v_date DATE;
    
    v_ret NUMERIC;
    v_row NUMERIC;
    
    r_invdetail RECORD;
    
    invdetail_curs CURSOR (c_itemsite_id integer, c_first_transdate date,  c_invdetail_id integer, c_not_invhist_id integer) FOR
            SELECT
                invdetail_id,
                invfifo_qty_before,
                invdetail_location_id
                
            FROM
                invdetailview
            WHERE
                invhist_itemsite_id = c_itemsite_id
                AND
                invfifo_void = 0
                AND
                (
                    invhist_transdate > c_first_transdate
                    OR
                    (
                        invhist_transdate  = c_first_transdate
                        AND
                        invdetail_id  > c_invdetail_id
                    )
                )
                AND
                invhist_id != c_not_invhist_id
            ORDER BY
                invhist_transdate ASC,
                invdetail_id ASC;
            
         
    
    
    
 BEGIN   
    
    --RAISE NOTICE 'invfifo_update_from_invdetail_ahead: %', i_invdetail_id;
    
    SELECT
        invdetail_location_id,
        invhist_itemsite_id,
        invfifo_qty_after,
        invhist_id,
        invhist_transdate
    INTO
        v_location_id,
        v_itemsite_id,
        v_end_qty,
        v_invhist_id
        v_date
    FROM
        invdetailview
    WHERE
        invdetail_id = i_invdetail_id;
    
    
    
    -- is it worth looking ahead?
    
    SELECT
            invdetail_id
        INTO
            v_next_up
        FROM
            invdetailview
        WHERE
        
            invdetail_location_id = v_location_id
        AND
            invhist_itemsite_id = v_itemsite_id
        AND
            invfifo_qty_after >= v_end_qty
        AND
            invdetail_qty > 0
        AND
            invfifo_void = 0
            
        LIMIT 1;
    -- basically it's negative stock!
    IF NOT FOUND THEN
        RETURN 0;
    END IF;
        
    
        
    v_ret = 0;
 
    OPEN invdetail_curs(v_itemsite_id, v_date,i_invdetail_id,  v_invhist_id );
    
    FETCH invdetail_curs into r_invdetail;
    WHILE FOUND LOOP
    
        -- stop if we have enough stock to fullfill it...
        IF ( 
            r_invdetail.invdetail_location_id = v_location_id
            AND
            r_invdetail.invfifo_qty_before >=  v_end_qty
            
        ) THEN
            
            EXIT;
        END IF;
        
        SELECT ABS(invfifo_update_from_invdetail(r_invdetail.invdetail_id, false)) into v_row;
        
        v_ret = v_ret + v_row;
            
    
    END LOOP;
    CLOSE invdetail_curs;
    
    RETURN v_ret;
    
     
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION   invfifo_update_from_invdetail_ahead(integer)
  OWNER TO admin;
          
