pgsql/vendinfo.sql

[Pman.Xtuple] / pgsql / x-fifo-invfifo-apply-gl-cmhead.sql

-- apply the fifo values to the general ledger for a credit memos.


-- cm's are invhist_ordtype = 'CM'

--Process:
-- for a specific cohead
-- fetch the total value of system created transactions
-- eg. Ship/return issue etc...

-- find the value we have created as an adjustment (if any.)

-- calculate the correct values
-- update or create a JE for this.

-- test:
-- SELECT invfifo_apply_gl_cmhead(cmhead_id) FROM cmhead ORDER BY cmhead_id ASC  ;

CREATE OR REPLACE FUNCTION invfifo_apply_gl_cmhead_all()
  RETURNS  TEXT
    
AS $BODY$
DECLARE        
    v_period_close_at_end INTEGER;
    
BEGIN

        SELECT
            period_temp_open(period_start)
        INTO
            v_period_close_at_end
        FROM
            period
        LEFT JOIN
            yearperiod
        ON
            period_yearperiod_id = yearperiod_id
        WHERE
            NOT yearperiod_closed
        ORDER BY
            period_start ASC
        LIMIT 1;


        PERFORM 
                invfifo_apply_gl_cmhead(cmhead_id) 
        FROM (
                SELECT cmhead_id FROM cmhead  ORDER BY cmhead_id ASC
        ) x;

        IF v_period_close_at_end > 0 THEN
            PERFORM period_temp_close(v_period_close_at_end);
        END IF;

        RETURN 'OK';
    
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_cmhead_all()
  OWNER TO admin; 


 
CREATE OR REPLACE FUNCTION invfifo_apply_gl_cmhead(integer)
  RETURNS  TEXT
    
AS $BODY$
DECLARE        
    i_cmhead_id  ALIAS FOR $1;
    v_tmp INTEGER;
    v_item_cnt NUMERIC;
    
    v_asset_accnt_id  INTEGER;
    v_cogs_accnt_id INTEGER;
    
    v_cogs_adj_total NUMERIC;
    v_ship_adj_total  NUMERIC;
    v_asset_adj_total NUMERIC;
    
    
    v_fifo_value NUMERIC;
    
    
    v_posted_cogs NUMERIC;
    v_posted_asset NUMERIC;
    
    v_assetdiff NUMERIC;
    v_cogsdiff NUMERIC;
    
    v_adj_cogs  NUMERIC;
    
    v_adj_asset  NUMERIC;
    
    v_cmhead_number TEXT;
    
    v_glsequence INTEGER;
    v_result INTEGER;
    v_num_ships INTEGER;
    v_num_no_purchase INTEGER;
    v_resultbool BOOLEAN;
    
    v_period_close_at_end INTEGER;
    v_first_open DATE;
    v_date DATE;
    v_start_date TEXT;
    
    
BEGIN
    --RAISE NOTICE 'START % ', clock_timestamp();
    -- need to determine what accounts are affected,
    -- generate a line item for all of them.
    -- then commit the gl entry for them..
    
    -- let's start with basics
    -- from cost categories
    -- 
    -- shipping asset, (number=1260) id = 96 -->  costcat_shipasset_accnt_id =96
    -- inventory asset (number = 1210 or 120?)  costcat_asset_accnt_id =194
    -- cogs (number = 121 or 5000) <<< from sales assigments COGS accnt.
    RAISE NOTICE 'cmhead_id = %', i_cmhead_id;
    
    SELECT
            cmhead_id
        INTO
            v_item_cnt
        FROM
            cmhead
        WHERE
            cmhead_id = i_cmhead_id
            AND
            cmhead_posted;
            
     if NOT FOUND THEN
        RETURN 'skipped = not posted';
    END IF;
    
    SELECT
            count(cmitem_id)
        INTO
            v_item_cnt
        FROM
            cmitem
        LEFT JOIN
            itemsite
        ON
            itemsite_id = cmitem_itemsite_id
        WHERE
            cmitem_cmhead_id = i_cmhead_id
        AND
            itemsite_stocked;
            
    if v_item_cnt < 1 THEN
        RETURN 'skipped = not an item based order';
    END IF;
      
     
    SELECT count(distinct salesaccnt_cos_accnt_id) INTO v_tmp FROM salesaccnt;
    IF v_tmp > 1 THEN
        RAISE EXCEPTION 'multiple cogs accounts not supported';
    END IF;
    
    SELECT DISTINCT(salesaccnt_cos_accnt_id) INTO v_cogs_accnt_id FROM salesaccnt;
    
    
    SELECT count(distinct costcat_id) INTO v_tmp FROM costcat;
    IF v_tmp > 1 THEN
        RAISE EXCEPTION 'multiple cost categories accounts not supported';
    END IF;
    
    SELECT
            costcat_asset_accnt_id 
        INTO
            v_asset_accnt_id
        FROM
            costcat
        LIMIT 1;
    
    
    SELECT
            cmhead_number
        INTO
            v_cmhead_number
        FROM
            cmhead
        WHERE
            cmhead_id = i_cmhead_id;
        
    
    --RAISE NOTICE 'cmhead_id = %', i_cmhead_id;
    /*
     gltrans_accnt_id |        accnt_descrip        
------------------+-----------------------------
              121 | Customer Credits
              123 | G/L Discrepancy
              135 | Sales
              148 | Inventory Asset  << relivant
              149 | Cost of Goods Sold << relivant
              159 | Accounts Receivable (HK HQ)

    */
    --RAISE NOTICE 'BEFORE TOTALS START % ', clock_timestamp();
    
    
    -- 0.632 ms without..
    SELECT
        ROUND(invfifo_apply_gl_cmhead_fifo_total(i_cmhead_id),2),
        
        ROUND(invfifo_apply_gl_cmhead_accnt_total(v_cmhead_number, v_cogs_accnt_id) ,2), -- together = 300ms
        ROUND(invfifo_apply_gl_cmhead_accnt_total(v_cmhead_number, v_asset_accnt_id),2),  
        
        ROUND(invfifo_apply_gl_cmhead_fetch_je_amount(v_cmhead_number,v_cogs_accnt_id) ,2), -- together = 300ms
        ROUND(invfifo_apply_gl_cmhead_fetch_je_amount(v_cmhead_number, v_asset_accnt_id),2) -- 0.166


         INTO
         v_fifo_value,
          
         v_posted_cogs,
         v_posted_asset,
         
         v_adj_cogs,
         v_adj_asset
         ;
    
    -- 600 ms iwth all of this..
    -- probably need to verify that cogs/inventory match..!?!?
    
    --RAISE NOTICE 'AFTER TOTALS START % ', clock_timestamp();
    v_cogsdiff = v_fifo_value - v_posted_cogs;
    v_assetdiff = (-1 * v_fifo_value) - v_posted_asset;
  
    RAISE NOTICE ' values: total  total issued=%    posted:  cogs(%)=%,  asset(%)=%   adjbefore cogs=%, asset=%',
         v_fifo_value,
       
         v_cogs_accnt_id,
         v_posted_cogs,
         v_asset_accnt_id,
         v_posted_asset,
         
         v_adj_cogs,
         v_adj_asset
         
         ;
    
    
    SELECT invfifo_apply_gl_cmhead_fetch_je_sequence(v_cmhead_number) INTO v_glsequence;
    
    
    IF (
        v_cogsdiff  = 0.0
        
        AND
        v_assetdiff = 0.0
    ) THEN
        -- delete old difference...
        
        IF v_glsequence > 0 THEN

            PERFORM invfifo_apply_gl_cmhead_revert_fifo(v_cmhead_number);
        
        END IF;
    
        RETURN 'NO FIFO DIFFERNECE';
    END IF;
    
    
    IF (v_adj_cogs = v_cogsdiff AND v_assetdiff = v_adj_asset ) THEN
        RETURN 'adjustment already exists';
    END IF;
    
    IF v_glsequence > 0 THEN

         PERFORM invfifo_apply_gl_cmhead_revert_fifo(v_cmhead_number);
     
     END IF;
    
    
    if ((v_cogsdiff  + v_assetdiff) != 0.0) THEN
        RAISE EXCEPTION 'adjustment does not balance';
    END IF;
    
    
    -- what date to post..
    
    -- what is the last shipment?
    SELECT
            MAX(cmhead_gldistdate)
        INTO
            v_date
        FROM
            cmhead
        WHERE
            cmhead_id = i_cmhead_id;
            

    IF NOT FOUND THEN
        RAISE EXCEPTION 'could not find a cmhead? for order %' , i_cmhead_id;
    END IF;
    
    RAISE NOTICE 'shipdate : %', v_date;
    
   SELECT fetchMetricText('invfifo_start_date') INTO v_start_date;
    IF v_start_date IS NULL OR LENGTH(v_start_date) < 1 THEN
        RAISE EXCEPTION 'PERFORM setmetric(''invfifo_start_date'',  ''YOUR START DATE'');  -- do this!';
    END IF;
    
    IF v_date <= v_start_date::date THEN
        RAISE NOTICE 'ignoring transaction before start date';
        RETURN 0;
    END IF;
    
    
    -- find the first open period..
   SELECT
            yearperiod_start
    INTO
            v_first_open
    FROM
            yearperiod
    WHERE
            NOT yearperiod_closed
        
    ORDER BY
            yearperiod_start ASC
    LIMIT 1;
    
        
    IF v_first_open >= v_date THEN
        v_date = v_first_open ;
    END IF;
    
    
    RAISE NOTICE 'shipdate after checking period: %', v_date;
    -- create new..
    
    
     SELECT period_temp_open(v_date) INTO v_period_close_at_end; 
 
    
    
    SELECT fetchGLSequence() INTO v_glsequence;
    
 
    -- fix the value
    
    
    SELECT insertIntoGLSeries(
                        v_glsequence,
                        'G/L',
                        'JE',
                       'FIFO-CMHEAD-' || v_cmhead_number, 
                        v_asset_accnt_id,  -- cogs
                         v_assetdiff  ,
                       v_date ) INTO v_result;

    if (v_result < 1) THEN
        
    
        RAISE EXCEPTION 'insertIntoGLSeries INV ASS failed';
    END IF;
    

    -- fix the value
    
    
    SELECT insertIntoGLSeries(
                        v_glsequence,
                        'G/L',
                        'JE',
                       'FIFO-CMHEAD-' || v_cmhead_number, 
                        v_cogs_accnt_id,  -- cogs
                        v_cogsdiff  ,
                       v_date ) INTO v_result;

    if (v_result < 1) THEN
        
    
        RAISE EXCEPTION 'insertIntoGLSeries INV ASS failed';
    END IF;
    
    UPDATE glseries
                SET glseries_notes='Fifo adjustment for ' || v_cmhead_number
                WHERE (glseries_sequence=v_glsequence);
    
    

    SELECT postGLSeriesNoSumm(v_glsequence,COALESCE(NULL,fetchJournalNumber('G/L'))) INTO v_result;
    
    if (v_result < 1) THEN
        RAISE EXCEPTION 'post GL seriese failed';
    END IF;
    
    IF v_period_close_at_end > 0 THEN
        PERFORM period_temp_close(v_period_close_at_end);
    END IF;
    
    
    RETURN 'CHANGE: cogs=' || v_cogsdiff  || ' asset=' ||  v_assetdiff;
    
    
    
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_cmhead(integer)
  OWNER TO admin;    
        
    
-------------------- FIFO ADJ ---------------------------        

CREATE OR REPLACE FUNCTION invfifo_apply_gl_cmhead_fetch_je_amount(text, integer)
    RETURNS  NUMERIC
    
AS $BODY$
DECLARE        
    i_cmhead_number ALIAS FOR $1;
    i_accnt_id  ALIAS FOR $2;
    
    v_ret NUMERIC;
    
BEGIN  
    v_ret = 0;
    SELECT
            SUM(COALESCE(gltrans_amount,0))
        INTO
            v_ret
        FROM
            gltrans
        WHERE
            gltrans_docnumber = 'FIFO-CMHEAD-' || i_cmhead_number
        AND
            gltrans_accnt_id = i_accnt_id
        AND
            gltrans_source = 'G/L'
        AND
           gltrans_doctype = 'JE'
        AND
            NOT gltrans_deleted
        AND
            gltrans_posted;
           
    RETURN v_ret;
   
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_cmhead_fetch_je_amount(text, integer)
  OWNER TO admin;    
        
 
CREATE OR REPLACE FUNCTION invfifo_apply_gl_cmhead_fetch_je_sequence(text)
    RETURNS  INTEGER
    
AS $BODY$
DECLARE        
    i_cmhead_number ALIAS FOR $1;
     
    v_ret INTEGER;
    
BEGIN  
    
    SELECT
            COALESCE(gltrans_sequence,0)
        INTO
            v_ret
        FROM
            gltrans
        WHERE
            gltrans_docnumber = 'FIFO-CMHEAD-' || i_cmhead_number
        AND
            gltrans_source = 'G/L'
        AND
           gltrans_doctype = 'JE'
        AND
            NOT gltrans_deleted
        AND
            gltrans_posted
        LIMIT 1;
           
    RETURN v_ret;
   
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_cmhead_fetch_je_sequence(text )
  OWNER TO admin;    
        
 
 
CREATE OR REPLACE FUNCTION invfifo_apply_gl_cmhead_fetch_je_date(i_cmhead_number text)
    RETURNS  DATE
    
AS $BODY$
DECLARE        
      
    v_ret DATE;
    
BEGIN  
    
    SELECT
            MIN(gltrans_date)
        INTO
            v_ret
        FROM
            gltrans
        WHERE
            gltrans_docnumber = 'FIFO-CMHEAD-' || i_cmhead_number
        AND
            gltrans_source = 'G/L'
        AND
           gltrans_doctype = 'JE'
        AND
            NOT gltrans_deleted
        AND
            gltrans_posted
        LIMIT 1;
           
    RETURN v_ret;
   
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_cmhead_fetch_je_date(text )
  OWNER TO admin;    
     

CREATE OR REPLACE FUNCTION invfifo_apply_gl_cmhead_revert_fifo(i_cmhead_number text)
    RETURNS  INTEGER
    
AS $BODY$
DECLARE  
    v_first_open DATE;
    v_min_date DATE;
    v_max_date DATE;

    v_glsequence INTEGER;
    _r RECORD;
    v_result INTEGER;
    
    v_post BOOLEAN;
    v_period_close_at_end INTEGER;
    
    
    
BEGIN
    
    SELECT
        yearperiod_start
    INTO
        v_first_open
    FROM
        yearperiod
    WHERE
        NOT yearperiod_closed

    ORDER BY
        yearperiod_start ASC
    LIMIT 1;
    
    SELECT 
            MIN(gltrans_date),
            MAX(gltrans_date)
    INTO
            v_min_date,
            v_max_date
    FROM
            gltrans
    WHERE   
            gltrans_docnumber = 'FIFO-CMHEAD-' || i_cmhead_number
        AND
            gltrans_source = 'G/L'
        AND
           gltrans_doctype = 'JE'
        AND
            NOT gltrans_deleted
        AND
            gltrans_posted;
    
    
    IF (v_max_date >= v_first_open) THEN
        PERFORM invfifo_apply_gl_cmhead_delete_je(i_cmhead_number);
    END IF;
    
    IF (v_min_date < v_first_open) THEN
        
        SELECT period_temp_open(v_first_open)  INTO v_period_close_at_end;

        SELECT fetchGLSequence() INTO v_glsequence;

        v_post := false;

        FOR _r IN SELECT 
                        COALESCE(SUM(gltrans_amount * -1), 0) AS v_amount,
                        gltrans_accnt_id
                FROM
                        gltrans
                WHERE
                        gltrans_docnumber = 'FIFO-CMHEAD-' || i_cmhead_number
                    AND
                        gltrans_source = 'G/L'
                    AND
                       gltrans_doctype = 'JE'
                    AND
                        NOT gltrans_deleted
                    AND
                        gltrans_posted 
                    AND
                        gltrans_date < v_first_open

                GROUP BY gltrans_accnt_id LOOP

                IF (_r.v_amount <> 0) THEN

                    v_post := true;


                    SELECT insertIntoGLSeries(
                                v_glsequence,
                                'G/L',
                                'JE',
                                'FIFO-CMHEAD-' || i_cmhead_number, 
                                _r.gltrans_accnt_id,
                                _r.v_amount,
                               v_first_open ) INTO v_result;

                    IF (v_result < 1) THEN
                        RAISE EXCEPTION 'insertIntoGLSeries SHIP ASS  failed';
                    END IF;
                END IF;
        END LOOP;

        IF (NOT v_post) THEN
            RETURN -1;
        END IF;

        UPDATE glseries
                    SET glseries_notes='Fifo adjustment for ' || i_cmhead_number
                    WHERE (glseries_sequence=v_glsequence);



        SELECT postGLSeriesNoSumm(v_glsequence,COALESCE(NULL,fetchJournalNumber('G/L'))) INTO v_result;

        if (v_result < 1) THEN
            RAISE EXCEPTION 'post GL seriese failed.';
        END IF;

        IF v_period_close_at_end > 0 THEN
            PERFORM period_temp_close(v_period_close_at_end);
        END IF;
    END IF;
       
    RETURN 0;
   
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_cmhead_revert_fifo(text )
  OWNER TO admin;    

   
 
CREATE OR REPLACE FUNCTION invfifo_apply_gl_cmhead_delete_je(i_cmhead_number text)
    RETURNS  INTEGER
    
AS $BODY$
DECLARE        
    v_first_open DATE;
    v_period_close_at_end INTEGER;
    v_ret INTEGER;
    
BEGIN  

    SELECT
        yearperiod_start
    INTO
        v_first_open
    FROM
        yearperiod
    WHERE
        NOT yearperiod_closed

    ORDER BY
        yearperiod_start ASC
    LIMIT 1;

    SELECT period_temp_open(v_first_open) INTO v_period_close_at_end; 
 
    PERFORM
        deleteGlSeries(gltrans_sequence, 'Journal edited by FIFO on ' || to_char(NOW(), 'Day Mon DD YYY') ) 
        FROM
            gltrans
        WHERE
            gltrans_docnumber = 'FIFO-CMHEAD-' || i_cmhead_number
        AND
            gltrans_source = 'G/L'
        AND
           gltrans_doctype = 'JE'
        AND
            NOT gltrans_deleted
        AND
            gltrans_posted
        AND
            gltrans_date >= v_first_open;
        
    IF v_period_close_at_end > 0 THEN
        PERFORM period_temp_close(v_period_close_at_end);
    END IF;
           
    RETURN 1;
   
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_cmhead_delete_je(text )
  OWNER TO admin;    
        

 
 
 
 
 
 
 
 
 
 
 
-------------------- FIFO VALUES ---------------------------        
          
CREATE OR REPLACE FUNCTION invfifo_apply_gl_cmhead_fifo_total(integer)
    RETURNS  NUMERIC
    
AS $BODY$
DECLARE        
    i_cmhead_id  ALIAS FOR $1;
    v_number TEXT;
    v_total NUMERIC;
    v_fifo_qty NUMERIC;
    v_fifo_total NUMERIC;
    v_tmp INTEGER;
    v_ret NUMERIC;
    
BEGIN
    SELECT
        
        cmhead_number
    INTO
        v_number
    
    FROM
         
            cmhead
        WHERE
            cmhead_id = i_cmhead_id;
    
    
    -- we can only really tell if there is a problem, if cust_id was not set..
    
    select
            count(invdetail_id)
        INTO
            v_tmp
        FROM
            invdetailview
         WHERE
           
            invhist_ordnumber = v_number 
        AND
            invfifo_cust_id = 0
        AND
            -- cm's are not voided.!?!?
             invfifo_void = 0
        AND
           invhist_ordtype = 'CM';
        
    
    IF FOUND AND v_tmp > 0 THEN
        RAISE EXCEPTION 'FIFO values not calculated  : SELECT invhist_itemsite_id, invfifo_landedunitcost FROM invdetailview where invhist_ordnumber = ''%'' AND invfifo_landedunitcost = 0.0 AND    invhist_ordtype = ''CM'';', v_number;
    END IF;
    
    
    
    
    -- cmitem qty..
    
    SELECT
        -- factor in reserved for non-shpped?
        SUM(cmitem_qtyreturned)
    INTO
        v_total 
    FROM
        cmitem
         
    LEFT JOIN
        itemsite
    ON
        itemsite_id = cmitem_itemsite_id
    LEFT JOIN 
        cmhead
    ON
        cmhead_id = cmitem_cmhead_id
    WHERE
        cmitem_cmhead_id = i_cmhead_id
    AND
        NOT cmhead_void
    AND
        itemsite_stocked;
    
    -- fifo qty..
    
    SELECT
        COALESCE(SUM(invdetail_qty ),0) ,
        COALESCE(SUM(invdetail_qty   * invfifo_landedunitcost),0)
        INTO
            v_fifo_qty,
            v_fifo_total
        FROM
            invdetailview
        WHERE
           
            invhist_ordnumber = v_number 
             
        AND
            -- cm's are not voided.!?!?
             invfifo_void = 0
        AND
           invhist_ordtype = 'CM';
    
    
      IF v_fifo_qty != v_total THEN
        RAISE EXCEPTION 'docnum=%, fifo qty for cmhead_id = %  does not match fifo = %, shipped = %',
                v_number , i_cmhead_id, v_fifo_qty, v_total;
    END IF;
    
    return v_fifo_total;
     


END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_cmhead_fifo_total(integer)
  OWNER TO admin;    
    
    
--------------- GL account totals. ----------------------
  

CREATE OR REPLACE FUNCTION invfifo_apply_gl_cmhead_accnt_total(text, integer)
    RETURNS  NUMERIC
    
AS $BODY$
DECLARE        
          
    
    i_cmhead_number  ALIAS FOR $1;
    i_accnt_id  ALIAS FOR $2;
    v_ret NUMERIC;
    
BEGIN
       
        
       
      SELECT 
            COALESCE(SUM( gltrans_amount ),0)
        INTO
            v_ret
        FROM
            gltrans
        WHERE
                gltrans_accnt_id = i_accnt_id
            AND
                gltrans_docnumber = i_cmhead_number
            
            AND
                NOT gltrans_deleted
            AND
                gltrans_posted
            AND
                gltrans_doctype='CM';
            
    RETURN v_ret;    
            
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_cmhead_accnt_total(text, integer)
  OWNER TO admin;  




    
--------------- void CM Application WHERE IS THIS USED>>>----------------------
  
--
--CREATE OR REPLACE FUNCTION voidCMApplication(i_docnumber TEXT)
--    RETURNS  INTEGER
--    
--AS $BODY$
--DECLARE
--    v_sequence INTEGER;
--    v_resultbool BOOLEAN;
--
--BEGIN
--    SELECT 
--            gltrans_sequence
--    INTO
--            v_sequence
--    FROM
--            gltrans
--    WHERE
--            gltrans_docnumber = i_docnumber
--        AND
--            gltrans_notes = 'CM Application'
--        AND
--            NOT gltrans_deleted;
--
--
--    IF (NOT FOUND) THEN
--        RAISE EXCEPTION 'CM Application not found : %', i_docnumber;
--    END IF;
--
--    SELECT deleteGlSeries(v_sequence ,  'Void CM Application on ' || to_char(NOW(), 'Day Mon DD YYY') ) INTO v_resultbool;
--
--    RETURN v_sequence;
--            
--END;
--$BODY$
--  LANGUAGE plpgsql VOLATILE
--  COST 100;
--  
--ALTER FUNCTION  voidCMApplication(TEXT)
--  OWNER TO admin; 




-- time testing

-- SELECT invfifo_apply_gl_cmhead(cmhead_id) FROM cmhead;