

-- fix the unit cost difference...
-- eg: receive item is $1
--     void is $2
-- add a transaction for the difference

CREATE OR REPLACE FUNCTION invfifo_pohead_fix_unitcost(i_pohead_number text)
    RETURNS  INTEGER
AS $BODY$
DECLARE    
    v_purchprice_accnt_id INTEGER;
    v_lb_accnt_id INTEGER;
    v_recorded NUMERIC;
    v_correct NUMERIC;
    v_result INTEGER;
    _r RECORD;
    v_date DATE;

    
BEGIN
   
    v_result := 0;
 
    SELECT 
            costcat_purchprice_accnt_id,  -- 148 Inventory Asset 
            costcat_liability_accnt_id   -- 141 Inventory Received Not Billed 
    INTO 
            v_purchprice_accnt_id,
            v_lb_accnt_id
    FROM 
            costcat 
    LIMIT 1;

 
    
    -- delete existing ???? == do this before we start calculating!?!?
    PERFORM
        deleteGlSeries( gltrans_sequence,  'Void Unit cost adjusted on ' || to_char(NOW(), 'Day Mon DD YYY') )

    FROM
            gltrans
    WHERE
            gltrans_docnumber = i_pohead_number
        AND
            gltrans_accnt_id = v_purchprice_accnt_id
        AND
            gltrans_doctype IN ('PO') 
        AND
            gltrans_date >= fetchMetricText('invfifo_start_date')::date
        AND 
            NOT gltrans_deleted
        AND
            gltrans_notes LIKE 'Unit cost adjusted for P/O%';

    

   -- how much... -- still not sure about this -  it's only been tested on a totally void purchase order.
    
    -- how much has been accounted for ?
    
    SELECT 
            COALESCE(SUM(gltrans_amount),0)
    INTO 
            v_recorded
    FROM
            gltrans
    LEFT JOIN
            invdetailview
    ON
            gltrans_misc_id = invhist_id
        -- AND
        --     invfifo_void = 0
        AND
            invhist_transtype = 'RP'
    
    WHERE
            invhist_id IS NOT NULL
        AND
            gltrans_accnt_id = (SELECT costcat_asset_accnt_id FROM costcat LIMIT 1)
        AND
            (gltrans_docnumber LIKE i_pohead_number || '-%' OR gltrans_docnumber = i_pohead_number )
        AND
            NOT gltrans_deleted
        AND
            gltrans_posted;

  
-- now show what should be created by just using the 'non-vo
    SELECT 
                COALESCE(SUM(gltrans_amount),0)
        INTO 
                v_correct
        FROM
                gltrans
        LEFT JOIN
                invdetailview
        ON
                gltrans_misc_id = invhist_id
            -- AND
            --     invfifo_void = 0
            AND
                invhist_transtype = 'RP'
        
        WHERE
                invhist_id IS NOT NULL
            AND
                invfifo_void= 0
            AND
                gltrans_accnt_id = (SELECT costcat_asset_accnt_id FROM costcat LIMIT 1)
            AND
                (gltrans_docnumber LIKE i_pohead_number || '-%' OR gltrans_docnumber = i_pohead_number )
            AND
                NOT gltrans_deleted
            AND
                gltrans_posted;

  RAISE NOTICE 'Unit cost diff : %', v_recorded - v_correct;

    
    SELECT 
            MAX(gltrans_date)
    INTO
            v_date
    FROM
            gltrans
    WHERE
            gltrans_accnt_id = v_purchprice_accnt_id
        AND
            gltrans_doctype IN ('PO') 
        AND
            gltrans_date >= fetchMetricText('invfifo_start_date')::date 
        AND
            NOT gltrans_deleted
        AND
            gltrans_docnumber LIKE i_pohead_number || '%' ;

  
    IF (ABS(v_recorded - v_correct) <> 0) THEN
        SELECT insertGLTransaction( fetchJournalNumber('GL-MISC'),
                                       'S/R', 'PO', i_pohead_number,
                                      'Unit cost adjusted for P/O ' || i_pohead_number || ' on ' || v_date,
                                      v_lb_accnt_id,
                                      v_purchprice_accnt_id, -1,
                                      v_recorded - v_correct,
                                      v_date::DATE, true ) INTO v_result;
    END IF;


    RETURN v_result;
    
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_pohead_fix_unitcost(text)
  OWNER TO admin;



 --
 --
 --SELECT gltrans_docnumber, gltrans_notes, gltrans_amount, invhist_id, invfifo_void FROM      gltrans
 --   LEFT JOIN
 --           invdetailview
 --   ON
 --           gltrans_misc_id = invhist_id
 --       -- AND
 --       --     invfifo_void = 0
 --       AND
 --           invhist_transtype = 'RP'
 --   
 --   WHERE
 --                       gltrans_accnt_id = (SELECT costcat_asset_accnt_id FROM costcat LIMIT 1)
 --       AND
 --           gltrans_docnumber LIKE '20018' || '-%'
 --       AND
 --           NOT gltrans_deleted
 --       AND
 --           gltrans_posted;
 --





