pgsql/investigations/ar-creditmemo.sql

[Pman.Xtuple] / pgsql / x-dragon-locbal.sql

-- loc bal is disabled at present - it's slow, and does not help much..


CREATE SEQUENCE locbal_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
GRANT ALL ON SEQUENCE locbal_id_seq  TO xtrole;


CREATE TABLE locbal (
    locbal_id INTEGER NOT NULL DEFAULT nextval(('locbal_id_seq'::text)::regclass),
    locbal_period_id integer,
    locbal_location_id integer,
    
    locbal_itemsite_id integer,
    
    locbal_beginning numeric(20,2),
    locbal_ending numeric(20,2),
    
    locbal_credits numeric(20,2),
    locbal_debits numeric(20,2),
    
    CONSTRAINT locbal_itemsite_id_fkey FOREIGN KEY (locbal_itemsite_id)
        REFERENCES itemsite  (itemsite_id)  MATCH SIMPLE,
    
    
    CONSTRAINT locbal_period_id_fkey FOREIGN KEY (locbal_period_id)
        REFERENCES period (period_id)  MATCH SIMPLE,
        
    CONSTRAINT locbal_location_id_fkey FOREIGN KEY (locbal_location_id)
        REFERENCES location (location_id),
    CONSTRAINT locbal_id_pkey PRIMARY KEY (locbal_id)
);

CREATE UNIQUE INDEX locbal_locsite_idx  ON locbal USING btree  (locbal_period_id, locbal_location_id, locbal_itemsite_id );



ALTER TABLE public.locbal OWNER TO admin;
GRANT ALL ON TABLE locbal  TO xtrole;
COMMENT ON TABLE locbal IS 'Location Balance information';

 CREATE SEQUENCE loccurbal_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

GRANT ALL ON TABLE loccurbal_id_seq TO xtrole;

CREATE TABLE loccurbal (
    loccurbal_id INTEGER NOT NULL DEFAULT nextval(('loccurbal_id_seq'::text)::regclass),
    loccurbal_location_id integer,
    loccurbal_itemsite_id integer,
    loccurbal_ending numeric(20,2),
    
    CONSTRAINT loccurbal_itemsite_id_fkey FOREIGN KEY (loccurbal_itemsite_id)
        REFERENCES itemsite  (itemsite_id)  MATCH SIMPLE,
    CONSTRAINT loccurbal_location_id_fkey FOREIGN KEY (loccurbal_location_id)
        REFERENCES location (location_id),
    CONSTRAINT loccurbal_id_pkey PRIMARY KEY (loccurbal_id)
);

ALTER TABLE public.loccurbal OWNER TO admin;

GRANT ALL ON TABLE loccurbal TO xtrole;

COMMENT ON TABLE loccurbal IS 'Location Balance current';


CREATE UNIQUE INDEX loccurbal_locsite_idx  ON loccurbal USING btree  (loccurbal_location_id, loccurbal_itemsite_id );




-- update balance.


-- TESTING:
-- SELECT distinct(invhist_itemsite_id), invdetaillocation_id, NOW() FROM invdetailview GROUP BY invhist_itemsite_id LIMIT 100;

-- 

DROP FUNCTION locbal_update_location_itemsite(integer, integer, date );
CREATE OR REPLACE FUNCTION locbal_update_location_itemsite(integer, integer, date )
  RETURNS NUMERIC AS
$BODY$
-- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  i_location_id ALIAS FOR $1;
  i_itemsite_id ALIAS FOR $2;
  i_date ALIAS FOR $3;
  
  v_id INTEGER;
  v_chk INTEGER;
  v_ret NUMERIC;
  v_last NUMERIC;
BEGIN

    --RAISE NOTICE 'update locbal(a) % , %, %',i_location_id, i_itemsite_id,  i_date;
    SELECT
            period_id
        INTO
            v_id
        FROM
            period
        WHERE
            period_start <= i_date
            AND
            period_end >= i_date;
        
    IF NOT FOUND THEN
        RAISE NOTICE 'no period found';
        RETURN 0;
    END IF;
  
    -- make sure there are some transactions for that item/location
    SELECT
            invdetail_id
        INTO
            v_chk
        FROM
            invdetailview
        WHERE
            invhist_itemsite_id = i_itemsite_id
            AND
            invdetail_location_id = i_location_id
            AND
            invhist_posted
        LIMIT 1;
        
    IF NOT FOUND THEN
        RAISE NOTICE 'no transactions found';
        RETURN 0;
    END IF;
    
    
     --RAISE NOTICE 'locbal_update_location_itemsite_period(  % , %, %) ',i_location_id, i_itemsite_id, v_id;
   
    -- this handle current and before..
    SELECT
            locbal_update_location_itemsite_period(i_location_id, i_itemsite_id, v_id)
        INTO
            v_ret;
    
    -- we should update after...
    
 
    -- finally update the total..
    --RAISE NOTICE 'locbal_update_begin_end   %   ',i_date  ;
   
    PERFORM
           locbal_update_begin_end(locbal_id)
        FROM
            locbal
        LEFT JOIN
            period
        ON
            locbal_period_id = period_id
        WHERE
            period_start > i_date
            AND
            locbal_itemsite_id = i_itemsite_id
            AND
            locbal_location_id = i_location_id
        ORDER BY
            period_start ASC;
    
    
     --RAISE NOTICE 'loccurbal_update(  % , % ) ',i_location_id, i_itemsite_id ;
    ---- updates loccurbal find final locbal_id
    PERFORM loccurbal_update(i_location_id, i_itemsite_id); 
    RETURN v_ret;
  
  
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION locbal_update_location_itemsite(integer, integer, date )
  OWNER TO admin;
    



 
CREATE OR REPLACE FUNCTION locbal_update_begin_end(integer  )
  RETURNS NUMERIC AS
$BODY$
-- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
    i_id ALIAS FOR $1;
    v_location_id INTEGER;
    v_itemsite_id INTEGER;
   
    v_period_start DATE;
    v_balance NUMERIC;
    
BEGIN

    --0 get current.
    SELECT
            locbal_location_id,
            locbal_itemsite_id,
            period_start
        INTO
            v_location_id,
            v_itemsite_id,
            v_period_start
        FROM
            locbal
        LEFT JOIN
            period
        ON
            locbal_period_id = period_id
        WHERE
            locbal_id = i_id ;
        
    
    -- get prev
    
    SELECT
            locbal_ending
        INTO
            v_balance
        FROM
            locbal
        LEFT JOIN
            period
        ON
            locbal_period_id = period_id
        WHERE
            locbal_id != i_id
            AND
            period_start < v_period_start
            AND
            locbal_location_id = v_location_id
            AND 
            locbal_itemsite_id = v_itemsite_id
        ORDER BY
            period_start DESC
        LIMIT 1;
    
    IF NOT FOUND THEN
        -- RAISE NOTICE 'no more previous periods';
        v_balance := 0;
    END IF;

    UPDATE
        locbal
        SET
            locbal_beginning = v_balance,
            locbal_ending = v_balance + locbal_credits - locbal_debits
        WHERE
            locbal_id = i_id;
    
    RETURN v_balance;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION locbal_update_begin_end(integer  )
  OWNER TO admin;
    




 
CREATE OR REPLACE FUNCTION loccurbal_update(integer, integer)
  RETURNS NUMERIC AS
$BODY$
-- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
    
    i_location_id ALIAS FOR $1;
    i_itemsite_id ALIAS FOR $2;
   
    v_id INTEGER;
    v_balance NUMERIC;
    
BEGIN

    --0 get current.
     
    -- get last
    
    SELECT
            COALESCE(locbal_ending, 0)
        INTO
            v_balance
        FROM
            locbal
        LEFT JOIN
            period
        ON
            locbal_period_id = period_id
        WHERE
             
            locbal_location_id = i_location_id
            AND 
            locbal_itemsite_id = i_itemsite_id
        ORDER BY
            period_start DESC
        LIMIT 1;
    
    IF NOT FOUND  THEN
        v_balance := 0;
    END IF;
    
    SELECT
          loccurbal_id 
        INTO
            v_id
        FROM
            loccurbal
        WHERE
            loccurbal_location_id = i_location_id
            AND 
            loccurbal_itemsite_id = i_itemsite_id
        LIMIT 1;
    
    IF NOT FOUND THEN
        INSERT INTO loccurbal (
            loccurbal_location_id,
            loccurbal_itemsite_id,
            loccurbal_ending 
        ) VALUES (
            i_location_id,
            i_itemsite_id,
            v_balance
        );
        RETURN v_balance;
    END IF;
    
     
    UPDATE
        loccurbal
        SET
            loccurbal_ending = v_balance 
        WHERE
            loccurbal_id = v_id;
    
    RETURN v_balance;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION loccurbal_update(integer, integer)
  OWNER TO admin;
    

 

    
    
CREATE OR REPLACE FUNCTION locbal_update_location_itemsite_period(integer,integer, integer)
  RETURNS NUMERIC AS
$BODY$
-- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  i_location_id ALIAS FOR $1;
  i_itemsite_id ALIAS FOR $2;
  i_period_id ALIAS FOR $3;
  
 
  v_id INTEGER;
  v_prev_period_id INTEGER;
   
  v_credit NUMERIC;
  v_debit NUMERIC;
  v_balance NUMERIC;
  
  v_start DATE;
  v_end DATE;

BEGIN

    --RAISE NOTICE 'SELECT locbal_update_location_itemsite_period (% , %, %)',i_location_id, i_itemsite_id,  i_period_id;
    SELECT
            period_start,
            period_end
        INTO
            v_start,
            v_end
        FROM
            period
        WHERE
            period_id = i_period_id;

    -- check to see if we have a value for the previous period.


    SELECT
            period_id
        INTO
            v_prev_period_id
        FROM
            period
        WHERE
            period_start < v_start
            AND
            period_id != i_period_id
        ORDER BY
            period_start DESC
        LIMIT 1;
        
    IF NOT FOUND THEN
        -- RAISE NOTICE 'no more previous periods';
        v_balance := 0;
    ELSE
        -- find out if we have a balance for the previous period..
        
        SELECT
                locbal_ending
            INTO
                v_balance
            FROM
                locbal
            WHERE
                locbal_location_id = i_location_id
            AND
                locbal_itemsite_id = i_itemsite_id
            AND
                locbal_period_id = v_prev_period_id;
                
            
        IF NOT FOUND THEN
        
            
        
            SELECT
                locbal_update_location_itemsite_period(i_location_id,i_itemsite_id, v_prev_period_id)
            INTO
                v_balance;
                
        END IF;
    
    
    END IF;



    SELECT
            
            COALESCE(SUM( CASE WHEN invdetail_qty > 0 THEN invdetail_qty ELSE 0 END) , 0) ,
            COALESCE(SUM( CASE WHEN invdetail_qty < 0 THEN invdetail_qty * -1 ELSE 0 END) , 0) 
        INTO
            v_credit,
            v_debit
         
        FROM
            invdetailview
        WHERE
            invhist_transdate >= v_start
            AND
            invhist_transdate < v_end + INTERVAL '1 DAY'
            AND
            invhist_itemsite_id = i_itemsite_id 
            AND
            invdetail_location_id = i_location_id
            AND
            invhist_posted;
            
            
    IF NOT FOUND THEN
        v_credit := 0;
        v_debit := 0;
    END IF;
    
    -- do we have a current record...
      
    SELECT
            locbal_id
        INTO
            v_id
        FROM
            locbal
        WHERE
            locbal_location_id = i_location_id
        AND
            locbal_itemsite_id = i_itemsite_id
        AND
            locbal_period_id = i_period_id;
    
    IF NOT FOUND THEN
        --RAISE NOTICE 'adding locbal';
    
        INSERT INTO locbal
        ( 
            locbal_period_id  ,
            locbal_location_id  ,
            
            locbal_itemsite_id  ,
            
            locbal_beginning  ,
            locbal_ending  ,
            
            locbal_credits  ,
            locbal_debits
        ) VALUES (
            i_period_id,
            i_location_id,
            i_itemsite_id,
            
            v_balance,
            v_balance + v_credit - v_debit,
            
            v_credit,
            v_debit
        
        );
    ELSE
        --RAISE NOTICE 'updating locbal';
        UPDATE locbal
            SET 
                locbal_beginning  = v_balance,
                locbal_ending = v_balance + v_credit - v_debit ,
                
                locbal_credits  = v_credit,
                locbal_debits = v_debit
            WHERE
                locbal_id = v_id;
    END IF;
    
            
    RETURN v_balance + v_credit - v_debit ;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION locbal_update_location_itemsite_period(integer,integer, integer)
  OWNER TO admin;
    

-- TESTING
--
--SELECT locbal_update_location_itemsite(a,b,c)
--    FROM (
--        SELECT distinct(invhist_itemsite_id) b, invdetail_location_id a, MAX(invhist_transdate)::date c
--        FROM invdetailview
--        
--        GROUP BY invhist_itemsite_id, invdetail_location_id  ) d;
-- 
-- 
-- 

  

CREATE OR REPLACE FUNCTION _invhist_locbaltrigger()
  RETURNS trigger AS
$BODY$
DECLARE
  v_ret NUMERIC;

BEGIN
    IF (TG_OP = 'INSERT') THEN
        SELECT locbal_update_location_itemsite(NEW.invdetail_location_id, invhist_itemsite_id, invhist_transdate::date)
        INTO v_ret
            FROM invhist WHERE invhist_id = NEW.invdetail_invhist_id;
    
        RETURN NEW;
    END IF;
     IF (TG_OP = 'UPDATE') THEN
        
        SELECT locbal_update_location_itemsite(NEW.invdetail_location_id, invhist_itemsite_id, invhist_transdate::date)
        INTO v_ret
            FROM invhist WHERE invhist_id = NEW.invdetail_invhist_id;
        
        SELECT locbal_update_location_itemsite(OLD.invdetail_location_id, invhist_itemsite_id, invhist_transdate::date)
        INTO v_ret
            FROM invhist WHERE invhist_id = OLD.invdetail_invhist_id;
    
        RETURN NEW;
    END IF;
    
    IF (TG_OP = 'DELETE') THEN
        SELECT locbal_update_location_itemsite(OLD.invdetail_location_id, invhist_itemsite_id, invhist_transdate::date)
        INTO v_ret
            FROM invhist WHERE invhist_id = OLD.invdetail_invhist_id;
      
        RETURN OLD;
    END IF;
    
    RETURN NEW;
    
 END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION _invhist_locbaltrigger()
  OWNER TO admin;      
--    
--CREATE TRIGGER invhist_locbaltrigger
--  AFTER INSERT OR UPDATE OR DELETE
--  ON invdetail
--  FOR EACH ROW
--  EXECUTE PROCEDURE _invhist_locbaltrigger();

DROP TRIGGER invhist_locbaltrigger ON invdetail;