git.roojs.org Git - Pman.Xtuple/blob - pgsql/x-fifo-invfifo-invadj-void-flag.sql

   1
   2 -- clear
   3 --UPDATE invfifo set invfifo_void = 0 WHERE invfifo_invdetail_id IN (SELECT invdetail_id FROM invdetailview where invfifo_void !=0 AND  invhist_transtype = 'AD');
   4
   5
   6 -- fill
   7 -- SELECT invfifo_invadj_void_flag(invdetail_id) FROM invdetailview where invhist_transtype = 'AD' and invfifo_void = 0;
   8
   9
  10 CREATE OR REPLACE FUNCTION invfifo_invadj_void_flag(integer)
  11     RETURNS  boolean
  12
  13 AS $BODY$
  14 DECLARE
  15
  16
  17     i_invdetail_id  ALIAS FOR $1;
  18
  19     r_invdetail RECORD;
  20
  21     v_reverse_of INTEGER;
  22
  23     v_first_open DATE;
  24
  25 BEGIN
  26
  27     SELECT
  28             *
  29         INTO
  30             r_invdetail
  31         FROM
  32             invdetailview
  33         WHERE
  34             invdetail_id    = i_invdetail_id;
  35
  36
  37     IF NOT FOUND THEN
  38         RAISE EXCEPTION ' invfifo_invjadj_void_flag-- need to add invfifo gen code';
  39     END IF;
  40
  41     IF (r_invdetail.invhist_transtype != 'AD') THEN
  42         RAISE EXCEPTION 'invfifo_invjadj_void_flag called on non-purchased item';
  43     END IF;
  44
  45     -- is it already voided..
  46
  47     IF r_invdetail.invfifo_void != 0 THEN
  48         RETURN false;
  49     END IF;
  50
  51
  52     -- search for a previous matching adjustment that this would be the reverse of..
  53     -- from now on, we can not do this for closed periods.
  54
  55      SELECT
  56             period_start
  57         INTO
  58             v_first_open
  59         FROM
  60             period
  61         WHERE
  62             NOT period_closed
  63         AND
  64             NOT period_freeze
  65         ORDER BY
  66             period_start ASC
  67         LIMIT 1;
  68
  69
  70
  71     SELECT
  72             invdetail_id
  73         INTO
  74             v_reverse_of
  75         FROM
  76             invdetailview
  77         WHERE
  78             invdetail_location_id = r_invdetail.invdetail_location_id
  79             AND
  80             invhist_itemsite_id = r_invdetail.invhist_itemsite_id
  81             AND
  82             invhist_transtype = 'AD'
  83             AND
  84             invdetail_qty = -1 * r_invdetail.invdetail_qty
  85             AND
  86             invfifo_void = 0
  87             AND
  88             invdetail_id < i_invdetail_id
  89             AND
  90             invhist_transdate >= v_first_open
  91         LIMIT 1;
  92
  93     IF NOT FOUND THEN
  94         return false;
  95     END IF;
  96
  97
  98
  99     PERFORM invfifo_update_from_void(i_invdetail_id, v_reverse_of, true);
 100     PERFORM invfifo_update_from_void(v_reverse_of,i_invdetail_id, true);
 101
 102
 103     RETURN true;
 104
 105
 106
 107  END;
 108 $BODY$
 109   LANGUAGE plpgsql VOLATILE
 110   COST 100;
 111
 112 ALTER FUNCTION  invfifo_invadj_void_flag(integer  )
 113   OWNER TO admin;
 114
 115
 116
 117   -- code was used to sync data...
 118         --
 119         ---- this might not be needed...
 120         --SELECT
 121         --        count(invadj_id)
 122         --    INTO
 123         --        v_invadj_id
 124         --    FROM
 125         --        invadj
 126         --    WHERE
 127         --          invadj_posted
 128         --        AND
 129         --          invadj_transdate  = r_invdetail.invhist_transdate::date
 130         --        AND
 131         --          invadj_location_id = r_invdetail.invdetail_location_id
 132         --        AND
 133         --          invadj_itemsite_id  = r_invdetail.invhist_itemsite_id
 134         --        AND
 135         --            invadj_qty_by = r_invdetail.invdetail_qty;
 136         --
 137         --    IF v_invadj_id != 0 THEN
 138         --        RAISE EXCEPTION 'could not match adjustment for invdetail_id=%', i_invdetail_id;
 139         --    END IF;
 140         --
 141         --SELECT
 142         --        invadj_id,
 143         --        CASE
 144         --            WHEN invadj_voided_by_id !=0 THEN 1
 145         --            WHEN invadj_voids_id !=0 THEN 1
 146         --        ELSE 0
 147         --        END
 148         --
 149         --    INTO
 150         --        v_invadj_id,
 151         --        v_is_void
 152         --    FROM
 153         --        invadj
 154         --    WHERE
 155         --          invadj_posted
 156         --        AND
 157         --          invadj_transdate  = r_invdetail.invhist_transdate::date
 158         --        AND
 159         --          invadj_location_id = r_invdetail.invdetail_location_id
 160         --        AND
 161         --          invadj_itemsite_id  = r_invdetail.invhist_itemsite_id
 162         --        AND
 163         --            invadj_qty_by = r_invdetail.invdetail_qty;
 164         --
 165         --UPDATE
 166         --    invadj
 167         --        SET
 168         --        invadj_invdetail_id = i_invdetail_id
 169         --        WHERE
 170         --        invadj_id =v_invadj_id;
 171         --
 172         --