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

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