-- aim is to flag the invfifo_void field with if
-- deliveries have been returned, so that fifo calcs will ignore any values.



-- ordtype = 'PO' => transtype = 'RP';




-- called with cohead_id and itemsite_id

--reset
-- update invfifo SET invfifo_void = 0 where  invfifo_invdetail_id IN (SELECT invdetail_id FROM invdetailview WHERE invhist_ordtype='PO' AND invfifo_void !=0);

-- fill
-- SELECT invfifo_pohead_void_flag_order(pohead_id) FROM pohead;

-- est
-- SELECT invfifo_pohead_void_flag_order(1286) ;



-- validation
-- total without voided should match total all together

-- SELECT SUM(invdetail_qty) FROM invdetailview where invhist_ordtype = 'PO';
-- SELECT SUM(invdetail_qty) FROM invdetailview where invhist_ordtype = 'PO' and invfifo_void = 0;
-- SELECT SUM(ABS(invdetail_qty)) FROM invdetailview where invhist_ordtype = 'PO' and invfifo_void != 0;
 
--
--SELECT invhist_ordnumber, aftervoid, beforevoid FROM
--
--    (SELECT invhist_ordnumber, SUM( CASE WHEN invfifo_void != 0 THEN 0 ELSE invdetail_qty END ) as aftervoid,
--        SUM(invdetail_qty) as beforevoid FROM invdetailview WHERE invhist_ordtype = 'PO'    
--        GROUP BY invhist_ordnumber
--    ) x WHERE aftervoid != beforevoid ;


--  

CREATE OR REPLACE FUNCTION invfifo_pohead_void_flag_order(integer)
    RETURNS  boolean
    
AS $BODY$
DECLARE        
          
    v_checksum NUMERIC;

    i_pohead_id  ALIAS FOR $1;
    
    v_pohead_number text;
BEGIN
    
    SELECT
            pohead_number
        INTO
            v_pohead_number
        FROM
            pohead
        WHERE
            pohead_id = i_pohead_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Error finding pohead_id %', i_pohead_id;
    END IF;
    
    PERFORM invfifo_pohead_void_flag_tx( invdetail_id, invhist_ordnumber, invhist_itemsite_id, invdetail_qty)
        FROM
            invdetailview
        WHERE 
            
                invhist_ordtype = 'PO'
            AND
            (
                invhist_ordnumber LIKE v_pohead_number || '-%'
                OR
                invhist_ordnumber = v_pohead_number
            )
             
            ORDER BY
                invdetail_id ASC;
 
    SELECT 
                COALESCE(SUM(invdetail_qty), 0.0)
            INTO
                v_checksum
             FROM 
                invdetailview
             WHERE 
                 
                     invhist_ordtype = 'PO'
                 AND
                 (
                     invhist_ordnumber LIKE v_pohead_number || '-%'
                     OR
                     invhist_ordnumber = v_pohead_number
                 )
                 AND
                     invfifo_void != 0  ;
    
    if (v_checksum != 0.0) THEN
         RAISE NOTICE 'order %  did not balance, %', v_pohead_number, v_checksum ;
    END IF;

 
 
    RETURN TRUE;
     
 END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_pohead_void_flag_order(integer  )
  OWNER TO admin;
          




 

CREATE OR REPLACE FUNCTION invfifo_pohead_void_flag(integer, integer)
    RETURNS  boolean
    
AS $BODY$
DECLARE        
          

    i_pohead_id  ALIAS FOR $1;
    i_itemsite_id  ALIAS FOR $2;
    v_checksum NUMERIC;
    v_pohead_number text;
BEGIN
    
    SELECT
            pohead_number
        INTO
            v_pohead_number
        FROM
            pohead
        WHERE
            pohead_id = i_pohead_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Error finding pohead_id %', i_pohead_id;
    END IF;
    
     
    PERFORM invfifo_pohead_void_flag_tx( invdetail_id, invhist_ordnumber, i_itemsite_id, invdetail_qty)
        FROM
            (SELECT 
                invdetail_id, invhist_ordnumber, invdetail_qty
                FROM 
                invdetailview
                WHERE 
                    
                        invhist_ordtype = 'PO'
                    AND
                    (
                        invhist_ordnumber LIKE v_pohead_number || '-%'
                        OR
                        invhist_ordnumber = v_pohead_number
                    )
                    AND
                        invhist_itemsite_id = i_itemsite_id 
                    ORDER BY
                        invdetail_id ASC
            ) x;
 
 
    -- verify that the totals are the same.
      SELECT 
                COALESCE(SUM(invdetail_qty), 0.0)
               INTO
                v_checksum
                FROM 
                invdetailview
                WHERE 
                    
                        invhist_ordtype = 'PO'
                    AND
                    (
                        invhist_ordnumber LIKE v_pohead_number || '-%'
                        OR
                        invhist_ordnumber = v_pohead_number
                    )
                    AND
                        invhist_itemsite_id = i_itemsite_id
                    AND
                        invfifo_void ! =0
                   ;
    
    if (v_checksum != 0.0) THEN
        RAISE EXCEPTION 'order %  did not balance, %', v_pohead_number, v_checksum ;
    END IF;
 
 
    RETURN TRUE;
     
 END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_pohead_void_flag(integer, integer )
  OWNER TO admin;
          

--- invdetail_id , order number, 
 
CREATE OR REPLACE FUNCTION invfifo_pohead_void_flag_tx(INTEGER, TEXT,INTEGER, NUMERIC)
    RETURNS  boolean
    
AS $BODY$
DECLARE        
          

    i_invdetail_id  ALIAS FOR $1;
    i_order_number ALIAS FOR $2;
    i_itemsite_id  ALIAS FOR $3;
    v_location_id INTEGER;
    v_rev_location_id INTEGER;
    i_qty ALIAS FOR $4;
    v_qty_balance NUMERIC;
    v_void_of INTEGER;
    
BEGIN
    
    
    if (i_qty < 0) THEN
        
        --- got return - look for issues
        
        -- then it is always void!!!
            --RAISE NOTICE 'flagging return as void %', i_invdetail_id;
    
    
            -- try and determine what it's trying to void..
            SELECT
                invfifo_void,
                invdetail_location_id
            INTO
                v_void_of,
                v_location_id
            FROM
                invdetailview
            WHERE
                invdetail_id = i_invdetail_id;
            
            IF v_void_of > 0 THEN
            
                SELECT
                    invdetail_location_id
                    INTO
                    v_rev_location_id
                    FROM
                    invdetailview
                    WHERE
                    invdetail_id = v_void_of;
                    
                IF (v_rev_location_id = v_location_id) THEN
            
                
                    PERFORM invfifo_update_from_void(v_void_of, i_invdetail_id,true);
                
                    PERFORM invfifo_update_from_void(i_invdetail_id, v_void_of,true);
                    return true;
                END IF;
            END IF;
             
            
            SELECT
                    invdetail_id
                INTO
                    v_void_of
                FROM
                    invdetailview
                WHERE
                
                    invhist_ordtype = 'PO'
                AND
                    invdetail_location_id = v_location_id
                AND
                    
                    invhist_ordnumber = i_order_number
                AND
                    invhist_itemsite_id = i_itemsite_id
                AND
                    invdetail_qty = -1 * i_qty
                AND
                    (invfifo_void = 0 OR invfifo_void = -1) 
                ORDER BY
                    invdetail_id ASC
                LIMIT 1;
            
            
            IF NOT FOUND THEN
                RETURN false;
            END IF;
            PERFORM invfifo_update_from_void(v_void_of, i_invdetail_id,true);
            
            PERFORM invfifo_update_from_void(i_invdetail_id, v_void_of,true);
            
             
            return true;
        
        
        
    END IF;
     
    
    RETURN false;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_pohead_void_flag_tx(INTEGER, TEXT,INTEGER, NUMERIC)
  OWNER TO admin;
          
-- see if it works.. 
--update invfifo set invfifo_void =0 WHERE invfifo_void != 0;
--
--SELECT invfifo_cohead_void_flag(10260, 3404);
--
--
--SELECT count(invdetail_id) FROM invdetailview where invfifo_void != 0;