git.roojs.org Git - xtuple/blob - foundation-database/public/functions/thawitemsite.sql

   1 CREATE OR REPLACE FUNCTION thawItemSite(INTEGER) RETURNS INTEGER AS $$
   2 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   3 -- See www.xtuple.com/CPAL for the full text of the software license.
   4 DECLARE
   5   pItemsiteid ALIAS FOR $1;
   6   _qoh            NUMERIC := 0;
   7   _netable_qoh    NUMERIC := 0;
   8   _nonnetable_qoh NUMERIC := 0;
   9   _value          NUMERIC := 0;
  10   _itemlocid INTEGER;
  11   _itemloc RECORD;
  12   _invhist RECORD;
  13   _coarse RECORD;
  14   _fine RECORD;
  15
  16 BEGIN
  17
  18   IF ( SELECT itemsite_freeze
  19        FROM itemsite
  20        WHERE (itemsite_id=pItemsiteid) ) THEN
  21
  22     SELECT invhist_id INTO _invhist
  23     FROM invhist
  24     WHERE ( (invhist_itemsite_id=pItemsiteid)
  25      AND (NOT invhist_posted) )
  26     LIMIT 1;
  27     IF (NOT FOUND) THEN
  28       UPDATE itemsite
  29       SET itemsite_freeze=FALSE
  30       WHERE (itemsite_id=pItemsiteid);
  31    END IF;
  32
  33 --  Run through any invdetail if this itemsite is still MLC and/or Lot/Serial
  34     IF ( SELECT ( (itemsite_loccntrl) OR
  35                   (itemsite_controlmethod IN ('L', 'S')) )
  36          FROM itemsite
  37          WHERE (itemsite_id=pItemsiteid) ) THEN
  38
  39 --  Grab all of the itemsite/location/lot/serial combinations
  40 --  that have unposted detail
  41       FOR _coarse IN SELECT DISTINCT invdetail_location_id, invdetail_ls_id,
  42                                      invdetail_expiration, invdetail_warrpurc
  43                      FROM invhist, invdetail
  44                      WHERE ( (invdetail_invhist_id=invhist_id)
  45                       AND (NOT invhist_posted)
  46                       AND (invhist_itemsite_id=pItemsiteid) )
  47                      ORDER BY invdetail_location_id, invdetail_ls_id LOOP
  48
  49 --  Cache the initial qty of the itemloc specified by the
  50 --  itemsite/location/lot/serial
  51         SELECT itemloc_id, itemloc_qty, COALESCE(location_netable, TRUE) AS location_netable
  52         INTO _itemloc
  53         FROM itemloc LEFT OUTER JOIN location ON (location_id=itemloc_location_id)
  54         WHERE ( (itemloc_itemsite_id=pItemsiteid)
  55          AND (itemloc_location_id=_coarse.invdetail_location_id)
  56          AND (COALESCE(itemloc_ls_id,-1)=COALESCE(_coarse.invdetail_ls_id,-1))
  57          AND (COALESCE(itemloc_expiration,endOfTime())=COALESCE(_coarse.invdetail_expiration,endOfTime()))
  58          AND (COALESCE(itemloc_warrpurc,endOfTime())=COALESCE(_coarse.invdetail_warrpurc,endOfTime())) );
  59
  60 --  If the itemloc in question cannot be found, create it
  61         IF (NOT FOUND) THEN
  62           SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _itemlocid;
  63           INSERT INTO itemloc
  64           ( itemloc_id, itemloc_itemsite_id,
  65             itemloc_location_id, itemloc_ls_id,
  66             itemloc_qty, itemloc_expiration )
  67           VALUES
  68           ( _itemlocid, pItemsiteid,
  69             _coarse.invdetail_location_id, _coarse.invdetail_ls_id,
  70             0, endOfTime() );
  71
  72         _qoh := 0.0;
  73         _netable_qoh := 0.0;
  74         _nonnetable_qoh := 0.0;
  75
  76         ELSE
  77           _itemlocid := _itemloc.itemloc_id;
  78           _qoh := _itemloc.itemloc_qty;
  79           IF (_itemloc.location_netable) THEN
  80             _netable_qoh := _itemloc.itemloc_qty;
  81           ELSE
  82             _nonnetable_qoh := _itemloc.itemloc_qty;
  83           END IF;
  84         END IF;
  85
  86 --  Now step through each unposted invdetail record for a given
  87 --  itemsite/location/lot/serial
  88         FOR _fine IN SELECT invdetail_id, invdetail_qty
  89                      FROM invhist, invdetail
  90                      WHERE ( (invdetail_invhist_id=invhist_id)
  91                       AND (NOT invhist_posted)
  92                       AND (invhist_itemsite_id=pItemsiteid)
  93                       AND (invdetail_location_id=_coarse.invdetail_location_id)
  94                       AND (COALESCE(invdetail_ls_id,-1)=COALESCE(_coarse.invdetail_ls_id,-1))
  95                       AND (COALESCE(invdetail_expiration,endOfTime())=COALESCE(_coarse.invdetail_expiration,endOfTime()))
  96                       AND (COALESCE(invdetail_warrpurc,endOfTime())=COALESCE(_coarse.invdetail_warrpurc,endOfTime())) )
  97                      ORDER BY invhist_transdate LOOP
  98
  99 --  Update the running qoh fields in the detail record
 100           UPDATE invdetail
 101           SET invdetail_qty_before = _qoh,
 102               invdetail_qty_after = (_qoh + invdetail_qty)
 103           WHERE (invdetail_id=_fine.invdetail_id);
 104
 105 --  Update the running qoh
 106           _qoh = (_qoh + _fine.invdetail_qty);
 107           IF (_itemloc.location_netable) THEN
 108             _netable_qoh := (_netable_qoh + _fine.invdetail_qty);
 109           ELSE
 110             _nonnetable_qoh := (_nonnetable_qoh + _fine.invdetail_qty);
 111           END IF;
 112
 113         END LOOP;
 114
 115 --  If the running qoh end up at 0, delete the itemloc in question
 116         IF (_qoh = 0) THEN
 117           DELETE FROM itemloc
 118           WHERE (itemloc_id=_itemlocid);
 119
 120 --  Otherwise, update the itemloc in question with the resultant qty
 121         ELSE
 122           UPDATE itemloc
 123           SET itemloc_qty=_qoh
 124           WHERE (itemloc_id=_itemlocid);
 125         END IF;
 126
 127       END LOOP;
 128
 129     END IF;
 130
 131 --  Cache the inital qoh of the itemsite
 132     SELECT itemsite_qtyonhand, itemsite_value INTO _qoh, _value
 133     FROM itemsite
 134     WHERE (itemsite_id=pItemsiteid);
 135
 136 --  We have to un-freeze the itemsite before update-ing its QOH
 137 --  so that that itemsite trigger won't block the QOH update.
 138 --  Also so the invhist trigger won't block the posted update.
 139
 140     UPDATE itemsite
 141     SET itemsite_freeze=FALSE
 142     WHERE (itemsite_id=pItemsiteid);
 143
 144     FOR _invhist IN SELECT invhist_id,
 145                            invhist_qoh_before, invhist_qoh_after,
 146                            invhist_value_before, invhist_value_after
 147                       FROM invhist
 148                      WHERE((invhist_itemsite_id=pItemsiteid)
 149                        AND (NOT invhist_posted))
 150                      ORDER BY invhist_transdate LOOP
 151
 152       UPDATE invhist
 153       SET invhist_qoh_before = _qoh,
 154           invhist_qoh_after = ( _qoh +
 155                                 _invhist.invhist_qoh_after -
 156                                 _invhist.invhist_qoh_before ),
 157           invhist_value_before = _value,
 158           invhist_value_after = ( _value +
 159                                   _invhist.invhist_value_after -
 160                                   _invhist.invhist_value_before ),
 161           invhist_posted = TRUE
 162       WHERE (invhist_id=_invhist.invhist_id);
 163
 164       _qoh := (_qoh + (_invhist.invhist_qoh_after - _invhist.invhist_qoh_before));
 165       _value := (_value + (_invhist.invhist_value_after - _invhist.invhist_value_before));
 166
 167     END LOOP;
 168
 169 -- _qoh can be used for the netable qoh because of the negative NN transactions
 170     UPDATE itemsite
 171        SET itemsite_qtyonhand = _qoh,
 172            itemsite_nnqoh = _nonnetable_qoh,
 173            itemsite_value = CASE WHEN ((itemsite_costmethod='A') AND (_value < 0.0)) THEN 0.0
 174                                  ELSE _value END
 175      WHERE(itemsite_id=pItemsiteid);
 176
 177   END IF;
 178
 179   RETURN pItemsiteid;
 180
 181 END;
 182 $$ LANGUAGE 'plpgsql';