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

   1 CREATE OR REPLACE FUNCTION distributeToLocations(pItemlocdistid 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   _distCounter INTEGER;
   6   _itemlocdist RECORD;
   7   _itemlocid INTEGER;
   8   _runningQty NUMERIC;
   9   _tmp RECORD;
  10
  11 BEGIN
  12
  13   _distCounter := 0;
  14   _runningQty  := 0;
  15
  16 -- A scenario can occur where two people try to post distributions
  17 -- to the same itemsite against two or more lot/serial/mlc locations
  18 -- leading to a deadlock. This line tries to prevent that by locking
  19 -- ahead of time all the itemsites that the transaction will need
  20 -- before any of the other tables are locked individually.
  21   SELECT itemsite_id
  22     INTO _tmp
  23     FROM itemsite
  24    WHERE(itemsite_id in (SELECT DISTINCT itemlocdist_itemsite_id
  25                            FROM itemlocdist
  26                           WHERE(itemlocdist_id=pItemlocdistid)))
  27      FOR UPDATE;
  28
  29 --  March through all of the itemlocdist owned by the passed parent itemlocdist
  30   FOR _itemlocdist IN SELECT c.itemlocdist_id AS itemlocdistid,
  31                              c.itemlocdist_source_type AS type,
  32                              c.itemlocdist_source_id AS sourceid,
  33                              c.itemlocdist_qty AS qty,
  34                              p.itemlocdist_itemsite_id AS itemsiteid,
  35                              itemsite_freeze,
  36                              p.itemlocdist_invhist_id AS invhistid,
  37                              p.itemlocdist_ls_id AS lotserialid,
  38                              p.itemlocdist_expiration AS expiration,
  39                              p.itemlocdist_warranty AS warranty,
  40                              p.itemlocdist_order_type AS ordertype,
  41                              p.itemlocdist_order_id AS orderid,
  42                              p.itemlocdist_series AS series
  43                       FROM itemlocdist AS c, itemlocdist AS p, itemsite
  44                       WHERE ( (c.itemlocdist_itemlocdist_id=p.itemlocdist_id)
  45                        AND (p.itemlocdist_source_type='O')
  46                        AND (p.itemlocdist_itemsite_id=itemsite_id)
  47                        AND (p.itemlocdist_id=pItemlocdistid) ) LOOP
  48
  49     _distCounter := _distCounter + 1;
  50
  51 --  If the target for this itemlocdist is a location, check to see if the
  52 --  required itemloc already exists
  53     IF (_itemlocdist.type = 'L') THEN
  54       SELECT itemloc_id INTO _itemlocid
  55       FROM itemloc
  56       WHERE ( (itemloc_itemsite_id=_itemlocdist.itemsiteid)
  57        AND (itemloc_location_id=_itemlocdist.sourceid)
  58        AND (COALESCE(itemloc_ls_id, -1)=COALESCE(_itemlocdist.lotserialid, -1))
  59        AND (COALESCE(itemloc_expiration,endOfTime())=COALESCE(_itemlocdist.expiration,endOfTime()))
  60        AND (COALESCE(itemloc_warrpurc,endoftime())=COALESCE(_itemlocdist.warranty,endoftime())) );
  61
  62 --  Nope, make it
  63       IF (NOT FOUND) THEN
  64         SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _itemlocid;
  65         INSERT INTO itemloc
  66         ( itemloc_id, itemloc_itemsite_id,
  67           itemloc_location_id, itemloc_qty,
  68           itemloc_ls_id, itemloc_expiration,
  69           itemloc_warrpurc )
  70         VALUES
  71         ( _itemlocid, _itemlocdist.itemsiteid,
  72           _itemlocdist.sourceid, 0,
  73           _itemlocdist.lotserialid, _itemlocdist.expiration,
  74           _itemlocdist.warranty );
  75       END IF;
  76
  77     ELSE
  78 --  Yep, cache it
  79       _itemlocid = _itemlocdist.sourceid;
  80
  81       IF (_itemlocid IS NOT NULL AND (SELECT count(itemloc_id) = 0 FROM itemloc WHERE itemloc_id=_itemlocid)) THEN
  82         RAISE EXCEPTION 'No record to distribute against. Someone else may have already distributed this record.';
  83       END IF;
  84     END IF;
  85
  86 --  Record the invdetail for this itemlocdist
  87     INSERT INTO invdetail
  88     ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
  89       invdetail_qty, invdetail_qty_before, invdetail_qty_after, invdetail_expiration,
  90       invdetail_warrpurc )
  91     SELECT _itemlocdist.invhistid, itemloc_location_id, itemloc_ls_id,
  92            _itemlocdist.qty, itemloc_qty, (itemloc_qty + _itemlocdist.qty),
  93            itemloc_expiration,_itemlocdist.warranty
  94     FROM itemloc
  95     WHERE (itemloc_id=_itemlocid);
  96
  97 --  Update the parent invhist to indicate that it has invdetail records
  98     UPDATE invhist
  99     SET invhist_hasdetail=TRUE
 100     WHERE ((invhist_hasdetail=FALSE)
 101      AND (invhist_id=_itemlocdist.invhistid));
 102
 103 --  Update the itemloc_qty if its parent itemsite is not frozen
 104     IF (NOT _itemlocdist.itemsite_freeze) THEN
 105       UPDATE itemloc
 106       SET itemloc_qty = (itemloc_qty + _itemlocdist.qty)
 107       WHERE (itemloc_id=_itemlocid);
 108
 109       PERFORM postInvHist(_itemlocdist.invhistid);
 110
 111 --  Handle reservation data
 112       IF ( (SELECT fetchMetricBool('EnableSOReservationsByLocation')) AND
 113            (_itemlocdist.qty < 0) ) THEN
 114
 115 --  If a shipment on a sales order, record reservation change before updating
 116 --  so it can be reversed later if necessary
 117         IF (_itemlocdist.ordertype = 'SO') THEN
 118           INSERT INTO shipitemlocrsrv
 119           SELECT nextval('shipitemlocrsrv_shipitemlocrsrv_id_seq'),
 120             shipitem_id, itemloc_itemsite_id, itemloc_location_id,
 121             itemloc_ls_id, itemloc_expiration, itemloc_warrpurc,
 122             least((_itemlocdist.qty * -1.0), reserve_qty)
 123           FROM shipitem, itemloc
 124             JOIN reserve ON (itemloc_id=reserve_supply_id AND reserve_supply_type='I')
 125           WHERE ( (shipitem_invhist_id=_itemlocdist.invhistid)
 126             AND   (itemloc_id=_itemlocid)
 127             AND   (reserve_demand_type=_itemlocdist.ordertype)
 128             AND   (reserve_demand_id=_itemlocdist.orderid) );
 129         END IF;
 130
 131 --  Update the reservation
 132         UPDATE reserve
 133         SET reserve_qty = (reserve_qty + _itemlocdist.qty)
 134         WHERE ( (reserve_supply_id=_itemlocid)
 135           AND   (reserve_supply_type='I')
 136           AND   (reserve_demand_type=_itemlocdist.ordertype)
 137           AND   (reserve_demand_id=_itemlocdist.orderid) );
 138
 139 --  Delete reservation if fully distributed
 140         DELETE FROM reserve
 141         WHERE ( (reserve_supply_id=_itemlocid)
 142           AND   (reserve_supply_type='I')
 143           AND   (reserve_demand_type=_itemlocdist.ordertype)
 144           AND   (reserve_demand_id=_itemlocdist.orderid)
 145           AND   (reserve_qty=0) );
 146       END IF;
 147     END IF;
 148
 149 --  Cache the running qty.
 150     _runningQty := _runningQty + _itemlocdist.qty;
 151
 152 --  Dene with the child itemlocdist, so delete it
 153     DELETE FROM itemlocdist
 154     WHERE (itemlocdist_id=_itemlocdist.itemlocdistid);
 155
 156 --  If the target itemloc is now at qty=0, delete it if its parent
 157 --  itemsite is not frozen
 158     IF (NOT _itemlocdist.itemsite_freeze) THEN
 159       DELETE FROM itemloc
 160       WHERE ( (itemloc_qty=0)
 161        AND (itemloc_id=_itemlocid) );
 162     END IF;
 163
 164   END LOOP;
 165
 166 --  If the running qty for the detailed distributions is the same as the
 167 --  total qty to distribute indicated by the parent itemlocdist, then the
 168 --  parent itemlocdist has been fully distributed and should be deleted.
 169   IF ( ( SELECT itemlocdist_qty
 170          FROM itemlocdist
 171          WHERE (itemlocdist_id=pItemlocdistid) ) = _runningQty) THEN
 172     DELETE FROM itemlocdist
 173     WHERE (itemlocdist_id=pItemlocdistid);
 174   ELSE
 175 --  There is still some more qty to distribute in the parent itemlocdist.
 176 --  Update the qty to distribute with the qty that has been distributed.
 177     UPDATE itemlocdist
 178     SET itemlocdist_qty = (itemlocdist_qty - _runningQty)
 179     WHERE (itemlocdist_id=pItemlocdistid);
 180   END IF;
 181
 182   RETURN _distCounter;
 183
 184 END;
 185 $$ LANGUAGE plpgsql;