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

   1 CREATE OR REPLACE FUNCTION postInvTrans(pItemsiteId    INTEGER,
   2                                         pTransType     TEXT,
   3                                         pQty           NUMERIC,
   4                                         pModule        TEXT,
   5                                         pOrderType     TEXT,
   6                                         pOrderNumber   TEXT,
   7                                         pDocNumber     TEXT,
   8                                         pComments      TEXT,
   9                                         pDebitid       INTEGER,
  10                                         pCreditid      INTEGER,
  11                                         pItemlocSeries INTEGER,
  12                                         pTimestamp     TIMESTAMP WITH TIME ZONE
  13                                                        DEFAULT CURRENT_TIMESTAMP,
  14                                         pCostOvrld     NUMERIC DEFAULT NULL,
  15                                         pInvhistid     INTEGER DEFAULT NULL)
  16   RETURNS INTEGER AS $$
  17 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
  18 -- See www.xtuple.com/CPAL for the full text of the software license.
  19 -- pInvhistid is the original transaction to be returned, reversed, etc.
  20 DECLARE
  21   _creditid          INTEGER;
  22   _debitid           INTEGER;
  23   _glreturn          INTEGER;
  24   _invhistid         INTEGER;
  25   _itemlocdistid     INTEGER;
  26   _r                 RECORD;
  27   _sense             INTEGER;  -- direction in which to adjust inventory QOH
  28   _t                 RECORD;
  29   _timestamp         TIMESTAMP WITH TIME ZONE;
  30   _xferwhsid         INTEGER;
  31
  32 BEGIN
  33
  34   --  Cache item and itemsite info
  35   SELECT CASE WHEN(itemsite_costmethod IN ('A','J')) THEN COALESCE(abs(pCostOvrld / pQty), avgcost(itemsite_id))
  36               ELSE stdCost(itemsite_item_id)
  37          END AS cost,
  38          itemsite_costmethod,
  39          itemsite_qtyonhand,
  40          itemsite_warehous_id,
  41          ( (item_type = 'R') OR (itemsite_controlmethod = 'N') ) AS nocontrol,
  42          (itemsite_controlmethod IN ('L', 'S')) AS lotserial,
  43          (itemsite_loccntrl) AS loccntrl,
  44          itemsite_freeze AS frozen INTO _r
  45   FROM itemsite JOIN item ON (item_id=itemsite_item_id)
  46   WHERE (itemsite_id=pItemsiteid);
  47
  48   --Post the Inventory Transactions
  49   IF (_r.nocontrol) THEN
  50     RETURN -1; -- non-fatal error so dont throw an exception?
  51   END IF;
  52
  53   IF (COALESCE(pItemlocSeries,0) = 0) THEN
  54     RAISE EXCEPTION 'Transaction series must be provided';
  55   END IF;
  56
  57   SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
  58
  59   IF ((pTimestamp IS NULL) OR (CAST(pTimestamp AS date)=CURRENT_DATE)) THEN
  60     _timestamp := CURRENT_TIMESTAMP;
  61   ELSE
  62     _timestamp := pTimestamp;
  63   END IF;
  64
  65   IF (pTransType = 'TS' OR pTransType = 'TR') THEN
  66     SELECT * INTO _t FROM tohead WHERE (tohead_number=pDocNumber);
  67     IF (pTransType = 'TS') THEN
  68       _xferwhsid := CASE
  69           WHEN (_t.tohead_src_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_trns_warehous_id
  70           WHEN (_t.tohead_trns_warehous_id=_r.itemsite_warehous_id AND pComments ~* 'recall') THEN _t.tohead_src_warehous_id
  71           WHEN (_t.tohead_trns_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_dest_warehous_id
  72           WHEN (_t.tohead_dest_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_trns_warehous_id
  73           ELSE NULL
  74           END;
  75     ELSIF (pTransType = 'TR') THEN
  76       _xferwhsid := CASE
  77           WHEN (_t.tohead_src_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_trns_warehous_id
  78           WHEN (_t.tohead_trns_warehous_id=_r.itemsite_warehous_id AND pComments ~* 'recall') THEN _t.tohead_dest_warehous_id
  79           WHEN (_t.tohead_trns_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_src_warehous_id
  80           WHEN (_t.tohead_dest_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_trns_warehous_id
  81           ELSE NULL
  82           END;
  83     END IF;
  84   END IF;
  85
  86
  87   -- increase inventory: AD RM RT RP RR RS RX RB TR
  88   -- decrease inventory: IM IB IT SH SI EX RI
  89   -- TS and TR are special: shipShipment and recallShipment should not change
  90   -- QOH at the Transfer Order src whs (as this was done by issueToShipping)
  91   -- but postReceipt should change QOH at the transit whs
  92   IF (pTransType='TS') THEN
  93     _sense := CASE WHEN (SELECT tohead_trns_warehous_id=_r.itemsite_warehous_id
  94                          FROM tohead
  95                          WHERE (tohead_number=pDocNumber)) THEN -1
  96                          ELSE 0
  97                          END;
  98   ELSIF (pTransType='TR') THEN
  99     _sense := CASE WHEN (SELECT tohead_src_warehous_id=_r.itemsite_warehous_id
 100                          FROM tohead
 101                          WHERE (tohead_number=pDocNumber)) THEN 0
 102                          ELSE 1
 103                          END;
 104   ELSIF (pTransType IN ('IM', 'IB', 'IT', 'SH', 'SI', 'EX', 'RI')) THEN
 105     _sense := -1;
 106
 107   ELSE
 108     _sense := 1;
 109   END IF;
 110
 111   IF((_r.itemsite_costmethod='A') AND (_r.itemsite_qtyonhand + round(_sense * pQty, 6)) < 0) THEN
 112     -- Can not let average costed itemsites go negative
 113     RAISE EXCEPTION 'This transaction will cause an Average Costed item to go negative which is not allowed [xtuple: postinvtrans, -2]';
 114   END IF;
 115
 116   INSERT INTO invhist
 117   ( invhist_id, invhist_itemsite_id, invhist_transtype, invhist_transdate,
 118       invhist_invqty, invhist_qoh_before,
 119       invhist_qoh_after,
 120       invhist_costmethod, invhist_value_before, invhist_value_after,
 121       invhist_ordtype, invhist_ordnumber, invhist_docnumber, invhist_comments,
 122       invhist_invuom, invhist_unitcost, invhist_xfer_warehous_id, invhist_posted,
 123       invhist_series )
 124   SELECT
 125     _invhistid, itemsite_id, pTransType, _timestamp,
 126     pQty, itemsite_qtyonhand,
 127     (itemsite_qtyonhand + (_sense * pQty)),
 128     itemsite_costmethod, itemsite_value,
 129     -- sanity check to ensure that value = 0 when qtyonhand = 0
 130     CASE WHEN ((itemsite_qtyonhand + (_sense * pQty)) + itemsite_nnqoh) = 0.0 THEN 0.0
 131          ELSE itemsite_value + (_r.cost * _sense * pQty)
 132     END,
 133     pOrderType, pOrderNumber, pDocNumber, pComments,
 134     uom_name, _r.cost, _xferwhsid, FALSE, pItemlocSeries
 135   FROM itemsite, item, uom
 136   WHERE ( (itemsite_item_id=item_id)
 137    AND (item_inv_uom_id=uom_id)
 138    AND (itemsite_id=pItemsiteid) );
 139
 140   IF (pCreditid IN (SELECT accnt_id FROM accnt)) THEN
 141     _creditid = pCreditid;
 142   ELSE
 143     SELECT warehous_default_accnt_id INTO _creditid
 144     FROM itemsite, whsinfo
 145     WHERE ( (itemsite_warehous_id=warehous_id)
 146       AND  (itemsite_id=pItemsiteid) );
 147   END IF;
 148
 149   IF (pDebitid IN (SELECT accnt_id FROM accnt)) THEN
 150     _debitid = pDebitid;
 151   ELSE
 152     SELECT warehous_default_accnt_id INTO _debitid
 153     FROM itemsite, whsinfo
 154     WHERE ( (itemsite_warehous_id=warehous_id)
 155       AND  (itemsite_id=pItemsiteid) );
 156   END IF;
 157
 158   --  Post the G/L Transaction
 159   IF (_creditid <> _debitid) THEN
 160     SELECT insertGLTransaction(pModule, pOrderType, pOrderNumber, pComments,
 161                                _creditid, _debitid, _invhistid,
 162                                (_r.cost * pQty), _timestamp::DATE, FALSE) INTO _glreturn;
 163   END IF;
 164
 165   --  Distribute this if this itemsite is controlled
 166   IF ( _r.lotserial OR _r.loccntrl ) THEN
 167
 168     _itemlocdistid := nextval('itemlocdist_itemlocdist_id_seq');
 169     INSERT INTO itemlocdist
 170     ( itemlocdist_id,
 171       itemlocdist_itemsite_id,
 172       itemlocdist_source_type,
 173       itemlocdist_reqlotserial,
 174       itemlocdist_distlotserial,
 175       itemlocdist_expiration,
 176       itemlocdist_qty,
 177       itemlocdist_series,
 178       itemlocdist_invhist_id,
 179       itemlocdist_order_type,
 180       itemlocdist_order_id )
 181     SELECT _itemlocdistid,
 182            pItemsiteid,
 183            'O',
 184            (((pQty * _sense) > 0)  AND _r.lotserial),
 185            ((pQty * _sense) < 0),
 186            endOfTime(),
 187            (_sense * pQty),
 188            pItemlocSeries,
 189            _invhistid,
 190            pOrderType,
 191            CASE WHEN pOrderType='SO' THEN getSalesLineItemId(pOrderNumber)
 192                 ELSE NULL
 193            END;
 194
 195     -- populate distributions if invhist_id parameter passed to undo
 196     IF (pInvhistid IS NOT NULL) THEN
 197       INSERT INTO itemlocdist
 198         ( itemlocdist_itemlocdist_id, itemlocdist_source_type, itemlocdist_source_id,
 199           itemlocdist_itemsite_id, itemlocdist_ls_id, itemlocdist_expiration,
 200           itemlocdist_qty, itemlocdist_series, itemlocdist_invhist_id )
 201       SELECT _itemlocdistid, 'L', COALESCE(invdetail_location_id, -1),
 202              invhist_itemsite_id, invdetail_ls_id,  COALESCE(invdetail_expiration, endoftime()),
 203              (invdetail_qty * -1.0), pItemlocSeries, _invhistid
 204       FROM invhist JOIN invdetail ON (invdetail_invhist_id=invhist_id)
 205       WHERE (invhist_id=pInvhistid);
 206
 207       IF ( _r.lotserial)  THEN
 208         INSERT INTO lsdetail
 209           ( lsdetail_itemsite_id, lsdetail_ls_id, lsdetail_created,
 210             lsdetail_source_type, lsdetail_source_id, lsdetail_source_number )
 211         SELECT invhist_itemsite_id, invdetail_ls_id, CURRENT_TIMESTAMP,
 212                'I', _itemlocdistid, ''
 213         FROM invhist JOIN invdetail ON (invdetail_invhist_id=invhist_id)
 214         WHERE (invhist_id=pInvhistid);
 215       END IF;
 216
 217       PERFORM distributeitemlocseries(pItemlocSeries);
 218
 219     END IF;
 220
 221   END IF;   -- end of distributions
 222
 223   -- These records will be used for posting G/L transactions to trial balance after records committed.
 224   -- If we try to do it now concurrency locking prevents any transactions while
 225   -- user enters item distribution information.  Cant have that.
 226   INSERT INTO itemlocpost ( itemlocpost_glseq, itemlocpost_itemlocseries)
 227   VALUES ( _glreturn, pItemlocSeries );
 228
 229   RETURN _invhistid;
 230
 231 END;
 232 $$ LANGUAGE 'plpgsql';