
-- run a whole itemsite
-- at present looks like the best way to update




-- alternative approaches.
-- loop each incomming
--> PO 's value as is.
--> transfers ... push active location to the one 
-->  loop through find all sales of it.. (or transfer outs..)
--> 

--> STACK?
 --> 




CREATE OR REPLACE FUNCTION invfifo_update_from_invdetail_all_force(integer)
    RETURNS  NUMERIC
AS $BODY$
DECLARE    
    i_itemsite_id  ALIAS FOR $1;
    v_ret NUMERIC;
BEGIN   
    DELETE FROM invfifopos WHERE invfifopos_itemsite_id = i_itemsite_id ;
    
    SELECT invfifo_update_from_invdetail_all(i_itemsite_id) INTO v_ret;
    
   RETURN v_ret;
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION   invfifo_update_from_invdetail_all_force(integer)
  OWNER TO admin;


DROP FUNCTION IF EXISTS invfifo_update_from_invdetail_all(integer);

CREATE OR REPLACE FUNCTION invfifo_update_from_invdetail_all(integer)
    RETURNS  NUMERIC
AS $BODY$
DECLARE    
    i_itemsite_id  ALIAS FOR $1;
    v_last_invdetail_id INTEGER;
    v_last_checked_id INTEGER;
    v_ret NUMERIC;
 BEGIN   
    -- do we need to do this...
    
    -- it basically check to see if the last run resulted in '0.0' adjustment.
    -- if so, no need to run again..
    
    SELECT
            COALESCE(max(invdetail_id),0)
        INTO
            v_last_invdetail_id
        FROM
            invdetailview
        where
            invhist_itemsite_id = i_itemsite_id;
            
            
    SELECT
            COALESCE(invfifopos_last_invdetail_id,0 )
        INTO
            v_last_checked_id
        FROM
            invfifopos
        where
            invfifopos_itemsite_id= i_itemsite_id
            AND
            invfifopos_lastadjustment = 0.0;
    
    IF v_last_checked_id = v_last_invdetail_id OR v_last_invdetail_id = 0 THEN
        RAISE NOTICE 'skipping  - max tx matches last run';
    
        RETURN 0;
    END IF;
    -- the transfer out must appear before the transfer in..
    -- the other way to handle this is to NOOP the transfer out
    -- and 
    SELECT SUM(ABS(invfifo_update_from_invdetail(invdetail_id, false)))
        INTO v_ret
    FROM (
        SELECT
                invdetail_id
            FROM
                invdetailview
            WHERE
                invhist_itemsite_id = i_itemsite_id
               -- AND
               -- invfifo_void = 0
            ORDER BY
                -- real recieved stock first..
            
               -- CASE WHEN invdetail_qty >0  and (invhist_transtype = 'RP' OR invhist_transtype = 'AD' OR invhist_transtype = 'RS' ) THEN 1
               -- ELSE -1 END DESC,
                invhist_transdate ASC,
                invdetail_id ASC
            
        ) x;
    
    
    --- finall balancing occurs by running RL a few times??? with update on??
     PERFORM  invfifopos_update(
            i_itemsite_id,
            COALESCE( (SELECT max(invdetail_id) FROM invdetailview where invhist_itemsite_id =  i_itemsite_id), 0),
            v_ret
    );
  
    RETURN v_ret;
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION   invfifo_update_from_invdetail_all(integer)
  OWNER TO admin;
