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

   1 CREATE OR REPLACE FUNCTION relocateInventory(INTEGER, INTEGER, INTEGER, NUMERIC, TEXT) 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 BEGIN
   5   RETURN relocateInventory($1, $2, $3, $4, $5, CURRENT_TIMESTAMP);
   6 END;
   7 $$ LANGUAGE plpgsql;
   8
   9 CREATE OR REPLACE FUNCTION relocateInventory(pSourceItemlocid INTEGER,
  10                                              pTargetLocationid INTEGER,
  11                                              pItemsiteid INTEGER,
  12                                              pQty NUMERIC,
  13                                              pComments TEXT,
  14                                              pGLDistTS TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS $$
  15 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
  16 -- See www.xtuple.com/CPAL for the full text of the software license.
  17 DECLARE
  18   _GlDistTS             TIMESTAMP WITH TIME ZONE;
  19   _targetItemlocid      INTEGER;
  20   _invhistid            INTEGER;
  21   _p                    RECORD;
  22   _rsrv                 RECORD;
  23   _qty                  NUMERIC;
  24   _qtyunreserved        NUMERIC := 0.0;
  25   _qtytomove            NUMERIC := 0.0;
  26   _result               INTEGER := -1;
  27   _itemlocSeries        INTEGER := NEXTVAL('itemloc_series_seq');
  28
  29 BEGIN
  30
  31     IF ((pGlDistTS IS NULL) OR (CAST(pGlDistTS AS date)=CURRENT_DATE)) THEN
  32       _GlDistTS := CURRENT_TIMESTAMP;
  33     ELSE
  34       _GLDistTS := pGLDistTS;
  35     END IF;
  36
  37 --  Make sure the passed itemsite points to a real item
  38   IF ( ( SELECT (item_type IN ('R', 'F') OR itemsite_costmethod = 'J')
  39          FROM itemsite, item
  40          WHERE ( (itemsite_item_id=item_id)
  41           AND (itemsite_id=pItemsiteid) ) ) ) THEN
  42     RETURN 0;
  43   END IF;
  44
  45 --  Cache some parameters
  46   SELECT itemloc_ls_id,
  47          itemloc_itemsite_id AS itemsiteid,
  48          itemloc_expiration,
  49          itemloc_warrpurc,
  50          itemloc_qty INTO _p
  51   FROM itemloc
  52   WHERE (itemloc_id=pSourceItemlocid);
  53
  54 --  Check to make sure the qty being transfered exists
  55   IF (_p.itemloc_qty < pQty) THEN
  56     RETURN -1;
  57   END IF;
  58
  59 --  Create the RL transaction
  60   SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
  61   INSERT INTO invhist
  62   ( invhist_id, invhist_itemsite_id,
  63     invhist_transtype, invhist_invqty,
  64     invhist_qoh_before, invhist_qoh_after,
  65     invhist_comments,   invhist_transdate,
  66     invhist_invuom, invhist_unitcost, invhist_costmethod,
  67     invhist_value_before, invhist_value_after, invhist_series)
  68   SELECT _invhistid, itemsite_id,
  69          'RL', 0,
  70          itemsite_qtyonhand, itemsite_qtyonhand,
  71          pComments, _GlDistTS,
  72          uom_name,
  73          CASE WHEN (itemsite_costmethod='A') THEN avgcost(itemsite_id)
  74               ELSE stdCost(item_id)
  75          END, itemsite_costmethod,
  76          itemsite_value, itemsite_value, _itemlocSeries
  77   FROM item, itemsite, uom
  78   WHERE ((itemsite_item_id=item_id)
  79    AND (item_inv_uom_id=uom_id)
  80    AND (itemsite_controlmethod <> 'N')
  81    AND (itemsite_id=pItemsiteid));
  82
  83 --  Relocate the inventory from the source and record the transactions
  84   INSERT INTO invdetail
  85   ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
  86     invdetail_qty, invdetail_qty_before, invdetail_qty_after,
  87     invdetail_expiration, invdetail_warrpurc )
  88   SELECT _invhistid, itemloc_location_id, itemloc_ls_id,
  89          (pQty * -1), itemloc_qty, (itemloc_qty - pQty),
  90          itemloc_expiration, itemloc_warrpurc
  91   FROM itemloc
  92   WHERE (itemloc_id=pSourceItemlocid);
  93
  94   UPDATE itemloc
  95   SET itemloc_qty=(itemloc_qty - pQty)
  96   FROM itemsite
  97   WHERE ( (itemloc_itemsite_id=itemsite_id)
  98    AND (NOT itemsite_freeze)
  99    AND (itemloc_id=pSourceItemlocid) );
 100
 101 --  Check to see if any of the current Lot/Serial #/Expiration exists at the target location
 102   SELECT itemloc_id INTO _targetItemlocid
 103   FROM itemloc
 104   WHERE ( (COALESCE(itemloc_ls_id, -1)=COALESCE(_p.itemloc_ls_id,-1))
 105    AND (COALESCE(itemloc_expiration,endOfTime())=COALESCE(_p.itemloc_expiration,endOfTime()))
 106    AND (COALESCE(itemloc_warrpurc,endOfTime())=COALESCE(_p.itemloc_warrpurc,endOfTime()))
 107    AND (itemloc_itemsite_id=pItemsiteid)
 108    AND (itemloc_location_id=pTargetLocationid) );
 109
 110   IF (NOT FOUND) THEN
 111     SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _targetItemlocid;
 112     INSERT INTO itemloc
 113     ( itemloc_id, itemloc_itemsite_id, itemloc_location_id,
 114       itemloc_ls_id, itemloc_expiration, itemloc_warrpurc, itemloc_qty )
 115     VALUES
 116     ( _targetItemlocid, pItemsiteid, pTargetLocationid,
 117       _p.itemloc_ls_id, _p.itemloc_expiration, _p.itemloc_warrpurc, 0 );
 118   END IF;
 119
 120 --  Relocate the inventory to the resultant target and record the transactions
 121   INSERT INTO invdetail
 122   ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
 123     invdetail_qty, invdetail_qty_before, invdetail_qty_after,
 124     invdetail_expiration, invdetail_warrpurc )
 125   SELECT _invhistid, pTargetLocationid, _p.itemloc_ls_id,
 126          pQty, itemloc_qty, (itemloc_qty + pQty),
 127          _p.itemloc_expiration, _p.itemloc_warrpurc
 128   FROM itemloc
 129   WHERE (itemloc_id=_targetItemlocid);
 130
 131   UPDATE itemloc
 132   SET itemloc_qty=(itemloc_qty + pQty)
 133   FROM itemsite
 134   WHERE ( (itemloc_itemsite_id=itemsite_id)
 135    AND (NOT itemsite_freeze)
 136    AND (itemloc_id=_targetItemlocid) );
 137
 138   UPDATE invhist
 139   SET invhist_hasdetail=TRUE
 140   WHERE (invhist_id=_invhistid);
 141
 142 --  Check to see if there is anything left at the target Itemloc and delete if not
 143 --  Could be zero if relocate increased a negative quantity to zero
 144   DELETE FROM itemloc
 145   WHERE ( (itemloc_qty=0)
 146    AND (itemloc_id=_targetItemlocid) );
 147
 148 --  Handle Reservations
 149   IF (fetchMetricBool('EnableSOReservationsByLocation')) THEN
 150     SELECT CASE WHEN (qtyReservedLocation(itemloc_id) > itemloc_qty)
 151                 THEN (qtyReservedLocation(itemloc_id) - itemloc_qty)
 152                 ELSE 0.0
 153                 END INTO _qtyunreserved
 154     FROM itemloc
 155     WHERE (itemloc_id=pSourceItemlocid);
 156     -- Move reservations as necessary
 157     WHILE (_qtyunreserved > 0.0) LOOP
 158       SELECT * INTO _rsrv
 159       FROM reserve
 160       WHERE ((reserve_supply_type='I')
 161         AND  (reserve_supply_id=pSourceItemlocid))
 162       ORDER BY reserve_qty;
 163       IF (NOT FOUND) THEN
 164         RAISE EXCEPTION 'Cannot find reservation to unreserve.';
 165       END IF;
 166       IF (_rsrv.reserve_qty > _qtyunreserved) THEN
 167         _qtytomove := _qtyunreserved;
 168       ELSE
 169         _qtytomove := _rsrv.reserve_qty;
 170       END IF;
 171       -- Unreserve Source Location
 172       SELECT unreserveSOLineQty(_rsrv.reserve_demand_id,
 173                                 _qtytomove,
 174                                 pSourceItemlocid) INTO _result;
 175       IF (_result < 0) THEN
 176         RAISE EXCEPTION 'unreserveSOLineQty failed with result=%, reserve_id=%, qty=%',
 177                         _result, _rsrv.reserve_id, _qtytomove;
 178       END IF;
 179       -- Reserve to new Location
 180       SELECT reserveSOLineQty(_rsrv.reserve_demand_id,
 181                               TRUE,
 182                               _qtytomove,
 183                               _targetItemlocid) INTO _result;
 184       IF (_result < 0) THEN
 185         RAISE EXCEPTION 'reserveSOLineQty failed with result=%, reserve_id=%, qty=%',
 186                         _result, _rsrv.reserve_id, _qtytomove;
 187       END IF;
 188       -- Calculate running total
 189       _qtyunreserved := _qtyunreserved - _qtytomove;
 190     END LOOP;
 191   END IF;
 192
 193 --  Check to see if there is anything left at the source Itemloc and delete if not
 194   DELETE FROM itemloc
 195   WHERE ( (itemloc_qty=0)
 196    AND (itemloc_id=pSourceItemlocid) );
 197
 198 --  Return the invhist_id
 199   RETURN _invhistid;
 200
 201 END;
 202 $$ LANGUAGE plpgsql;