
--- updates the invfifo values for shipped and unitcost
-- here comes a fun problem


--- if it bases the incomming cost from the calculated cost, then it can go totally out of wack.
-- so it is better to find the unit cost from the last purcahse




CREATE OR REPLACE FUNCTION invfifo_update_from_credit_memo(integer)
    RETURNS  boolean
AS $BODY$
DECLARE    
i_invdetail_id  ALIAS FOR $1;
BEGIN
    RAISE EXCEPTION 'replaced with option version';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
------------------------------

CREATE OR REPLACE FUNCTION invfifo_update_from_credit_memo(integer, boolean)
    RETURNS  NUMERIC

AS $BODY$
DECLARE        
           
    i_invdetail_id  ALIAS FOR $1;
    i_update_after ALIAS FOR $2;
   
    r_invdetail RECORD;
    
    v_unitcost  numeric;
 
    v_new_after NUMERIC;
    v_diff NUMERIC;
    v_landed_unitcost NUMERIC;
    v_est_unitcost NUMERIC;
    v_cost_before NUMERIC;
    v_last_id INTEGER;
    v_cust_id INTEGER;
    

BEGIN
     
 
    SELECT
            *
        INTO
            r_invdetail
        FROM
            invdetailview  
        WHERE
            invdetail_id    = i_invdetail_id;
    
    
    IF NOT FOUND THEN
        RAISE EXCEPTION ' invfifo_update_from_creditmemo-- need to add invfifo gen code';
    END IF;
    
    IF (r_invdetail.invhist_transtype != 'RS' AND r_invdetail.invhist_ordtype != 'CM') THEN
        RAISE EXCEPTION 'invfifo_update_from_creditmemo called on non-purchased item';
    END IF;
     
    
    -- there is a situation where this decreases stock when it's voided.
    
    if (r_invdetail.invdetail_qty < 0) THEN
        SELECT invfifo_update_from_credit_memo_void(i_invdetail_id, i_update_after) INTO v_diff;
        RETURN v_diff;
    
    END IF;
     
     
        
    SELECT invfifo_unitcost_at_qty(r_invdetail.invhist_itemsite_id, r_invdetail.invdetail_location_id, r_invdetail.invfifo_qty_before) INTO
        v_est_unitcost;
        
        
        
     IF v_est_unitcost  = 0.0 THEN 
    -- at_date will return std cost if there are no purchases...
        SELECT invfifo_unitcost_at_date(r_invdetail.invhist_itemsite_id,   r_invdetail.invhist_transdate::date) INTO
           v_est_unitcost;
    END IF;
    
    


      v_unitcost = v_est_unitcost;
      v_landed_unitcost = v_est_unitcost;
    
    
    
       -- RAISE NOTICE 'Credit memo found where nothing was delivered to customer before.';
    --END IF;    
    
    
    
    SELECT invfifo_cost_before_in(r_invdetail.invfifo_qty_before, r_invdetail.invdetail_location_id ,r_invdetail.invhist_itemsite_id) INTO
                v_cost_before;
      
    
    -- THERE FOR
    -- update invfifo
    v_new_after := (v_landed_unitcost * r_invdetail.invdetail_qty) + v_cost_before;
    
    
    
    
    -- has it changed...
    IF (
             r_invdetail.invfifo_unitcost = v_unitcost
             AND
             r_invdetail.invfifo_landedunitcost = v_landed_unitcost
             AND
             r_invdetail.invfifo_cost_after = v_new_after
             AND
             r_invdetail.invfifo_cost_before= v_cost_before
             AND
            r_invdetail.invfifo_cust_id = v_cust_id
       ) THEN
        RETURN 0;
    END IF;
    
    
    
    UPDATE invfifo
        SET 
            invfifo_unitcost = v_unitcost,
            invfifo_landedunitcost = v_landed_unitcost,
            invfifo_cost_after = v_new_after,
            invfifo_cost_before = v_cost_before,
            invfifo_cust_id = v_cust_id
        WHERE
            invfifo_invdetail_id = r_invdetail.invdetail_id;
    
    v_diff = ROUND(v_new_after - r_invdetail.invfifo_cost_after,2);
    
    
    IF (v_diff = 0.0) THEN
        RETURN 0;
    END IF;
    
    -- update the values after this one..
    
      -- we can not skip this step, as outgoing stock that depends on it
    -- needs to have the right values.
    --IF NOT i_update_after THEN
    --    RETURN v_diff;
    --END IF;
    
    UPDATE invfifo
        SET 
           invfifo_cost_after = invfifo_cost_after + v_diff,
           invfifo_cost_before = invfifo_cost_before + v_diff
      
        FROM
            invdetail, invhist           
           -- unit costs and landed does not change on after..
        WHERE
            invfifo_invdetail_id = invdetail_id
            AND
            invhist_id = invdetail_invhist_id
            AND            
            invdetail_location_id =  r_invdetail.invdetail_location_id 
            AND
            invhist_itemsite_id = r_invdetail.invhist_itemsite_id 
            AND
            invfifo_qty_after >  r_invdetail.invfifo_qty_after
            AND
            invdetail_qty > 0
            AND
            invfifo_void = 0;
    
    
    -- FLAG The sales after this date as dirty..
    --UPDATE invfifo
    --    SET
    --       invfifo_recalc_queued = true
    --     
    --    FROM
    --        invdetail, invhist           
    --     WHERE
    --        invfifo_invdetail_id = invdetail_id
    --        AND
    --        invhist_id = invdetail_invhist_id
    --        AND            
    --        invdetail_location_id =  r_invdetail.invdetail_location_id 
    --        AND
    --        invhist_itemsite_id = r_invdetail.invhist_itemsite_id 
    --        AND
    --        -- stock outbound is greater than we started with.
    --        invfifo_qty_before >=  r_invdetail.invfifo_qty_before
    --        AND
    --        invdetail_qty < 0
    --        AND
    --        invfifo_void = 0;
    --        
            
    return v_diff;
 
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_update_from_credit_memo(integer,boolean)
  OWNER TO admin;
    