--- updates the invfifo values for shipped and unitcost

-- purchase order return from shipping.. (note we just pop it of the stack as usual,
--even though it affects landed cost, as our landed cost
-- is affected by this.

--  note, we actually need this where single items are returned from shipments..
 

CREATE OR REPLACE FUNCTION invfifo_update_from_return_inventory(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_return_inventory(integer, boolean)
    RETURNS  NUMERIC
AS $BODY$
DECLARE        
           
    i_invdetail_id  ALIAS FOR $1;
    i_update_after ALIAS FOR $2;
   
    r_invdetail RECORD;
    r_invdetail_out RECORD;
    r_invadj RECORD;
   
    v_unitcost  NUMERIC;
    v_landedcost NUMERIC;
    v_est_unitcost NUMERIC;
    v_new_after NUMERIC;
    v_invfifo_cost_before NUMERIC;
    v_invfifo_cost_after NUMERIC;
    v_diff NUMERIC;
    v_last_id INTEGER;
    v_vend_id INTEGER;
    v_pohead_number TEXT;
  
BEGIN
     
 
    SELECT
            *
        INTO
            r_invdetail
        FROM
            invdetailview  
        WHERE
            invdetail_id    = i_invdetail_id;
    
    
    IF NOT FOUND THEN
        RAISE EXCEPTION ' invfifo_update_from_return_inventory -- need to add invfifo gen code';
    END IF;
    
     IF (r_invdetail.invhist_transtype != 'RP') THEN
        RAISE EXCEPTION 'invfifo_update_from_return_inventory called on non-inventory return';
    END IF;
    
    
    
      
    -- fetch the actual purchase price
    -- it should be the reverse of this.
    
    SELECT invfifo_cost_at_qty(r_invdetail.invhist_itemsite_id, r_invdetail.invdetail_location_id, r_invdetail.invfifo_qty_before) INTO
        v_invfifo_cost_before;
    
    SELECT invfifo_cost_at_qty(r_invdetail.invhist_itemsite_id, r_invdetail.invdetail_location_id, r_invdetail.invfifo_qty_after) INTO
        v_invfifo_cost_after;
    
    
    --RAISE NOTICE 'cost before is %,  cost after is %',v_invfifo_cost_before, v_invfifo_cost_after;
    
    IF v_invfifo_cost_before < 0 THEN
    
          SELECT invfifo_cost_before_out(r_invdetail.invfifo_qty_before, r_invdetail.invdetail_location_id ,r_invdetail.invhist_itemsite_id) INTO
                v_invfifo_cost_before;
        
            v_invfifo_cost_after = v_invfifo_cost_before +
                    ABS(r_invdetail.invdetail_qty) * r_invdetail.invhist_unitcost;
          
    
         
    END IF;
 
    IF v_invfifo_cost_after <= 0.0 THEN
        -- we are selling more than we have..
        -- we should find out what was the last purchase price and use that as the unitcost..
        v_invfifo_cost_after = v_invfifo_cost_before +
                    ABS(r_invdetail.invdetail_qty) * r_invdetail.invhist_unitcost;
    
    END IF;
 
     
    --RAISE NOTICE 'cost before is %,  cost after is %',v_invfifo_cost_before, v_invfifo_cost_after;
    v_unitcost = ABS((v_invfifo_cost_after - v_invfifo_cost_before) /  r_invdetail.invdetail_qty);
    v_landedcost = ABS((v_invfifo_cost_after - v_invfifo_cost_before) /  r_invdetail.invdetail_qty);
            
    IF (
        r_invdetail.invfifo_unitcost = v_unitcost
        AND
        r_invdetail.invfifo_landedunitcost = v_landedcost
        AND
        r_invdetail.invfifo_cost_after = v_invfifo_cost_after
        AND
        r_invdetail.invfifo_cost_before = v_invfifo_cost_before
        --- we might need these???
        --AND
        --r_invdetail.invfifo_cust_id = v_cust_id
        --AND
        --r_invdetail.invfifo_cohead_id = v_cohead_id
    ) THEN
        RETURN 0;
    END IF;
    
    
 
 
    UPDATE invfifo
        SET 
            invfifo_unitcost = v_unitcost,
            invfifo_landedunitcost = v_landedcost,
            invfifo_cost_after = v_invfifo_cost_after,
            invfifo_cost_before = v_invfifo_cost_before,
            invfifo_recalc_queued = false
        WHERE
            invfifo_invdetail_id = r_invdetail.invdetail_id;
    
    v_diff = ROUND(v_invfifo_cost_after - r_invdetail.invfifo_cost_after,2);
    
    IF (v_diff = 0.0) THEN
        RETURN 0;
    END IF;
    
    IF NOT i_update_after THEN
        RETURN v_diff;
    END IF;
    
    -- update the values after this one..
     -- FLAG The sales after this date as dirty.?? just in case?
     
    UPDATE invfifo
        SET 
            invfifo_cost_after = invfifo_cost_after + v_diff,
            invfifo_cost_before = invfifo_cost_before + v_diff
            --,
     --       invfifo_recalc_queued = 1
      
        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;
     
     
    return v_diff;
 
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_update_from_return_inventory(integer,boolean)
  OWNER TO admin;
    