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

   1 CREATE OR REPLACE FUNCTION voidApopenVoucher(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   pApopenid ALIAS FOR $1;
   6 BEGIN
   7   RETURN voidApopenVoucher(pApopenid, fetchJournalNumber('AP-VO'));
   8 END;
   9 $$ LANGUAGE 'plpgsql';
  10
  11 CREATE OR REPLACE FUNCTION voidApopenVoucher(INTEGER, INTEGER) RETURNS INTEGER AS $$
  12 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
  13 -- See www.xtuple.com/CPAL for the full text of the software license.
  14 DECLARE
  15   pApopenid ALIAS FOR $1;
  16   pJournalNumber ALIAS FOR $2;
  17   _apopenid INTEGER;
  18   _apcreditapplyid INTEGER;
  19   _reference    TEXT;
  20   _result INTEGER;
  21   _sequence INTEGER;
  22   _totalAmount_base NUMERIC;
  23   _totalAmount NUMERIC;
  24   _itemAmount_base NUMERIC;
  25   _itemAmount NUMERIC;
  26   _test INTEGER;
  27   _a RECORD;
  28   _d RECORD;
  29   _g RECORD;
  30   _p RECORD;
  31   _n RECORD;
  32   _r RECORD;
  33   _costx RECORD;
  34   _pExplain BOOLEAN;
  35   _pLowLevel BOOLEAN;
  36   _exchGainFreight NUMERIC;
  37   _firstExchDateFreight DATE;
  38   _tmpTotal             NUMERIC;
  39   _glDate               DATE;
  40
  41 BEGIN
  42
  43   _totalAmount_base := 0;
  44   _totalAmount := 0;
  45   SELECT fetchGLSequence() INTO _sequence;
  46
  47 --  Cache APOpen Information
  48   SELECT apopen.* INTO _n
  49   FROM apopen
  50   WHERE ( (apopen_doctype='V')
  51     AND   (apopen_id=pApopenid) );
  52   IF (NOT FOUND) THEN
  53     RAISE EXCEPTION 'Cannot Void Voucher #% as apopen not found', pApopenid;
  54   END IF;
  55
  56 --  Cache Voucher Infomation
  57   SELECT vohead.*,
  58          vend_number || '-' || vend_name || ' ' || vohead_reference
  59                                                           AS glnotes,
  60          COALESCE(pohead_orderdate, vohead_docdate) AS pohead_orderdate,
  61          COALESCE(pohead_curr_id, vohead_curr_id) AS pohead_curr_id INTO _p
  62   FROM vohead JOIN vendinfo ON (vend_id=vohead_vend_id)
  63               LEFT OUTER JOIN pohead ON (vohead_pohead_id = pohead_id)
  64   WHERE (vohead_number=_n.apopen_docnumber);
  65   IF (NOT FOUND) THEN
  66     RAISE EXCEPTION 'Cannot Void Voucher #% as vohead not found', _n.apopen_docnumber;
  67   END IF;
  68
  69   _glDate := COALESCE(_p.vohead_gldistdate, _p.vohead_distdate);
  70
  71 -- there is no currency gain/loss on items, see issue 3892,
  72 -- but there might be on freight, which is first encountered at p/o receipt
  73   SELECT recv_date::DATE INTO _firstExchDateFreight
  74   FROM recv
  75   WHERE (recv_vohead_id = _p.vohead_id);
  76
  77 --  Start by handling taxes
  78   FOR _r IN SELECT tax_sales_accnt_id,
  79               round(sum(taxdetail_tax),2) AS tax,
  80               currToBase(_p.vohead_curr_id, round(sum(taxdetail_tax),2), _p.vohead_docdate) AS taxbasevalue
  81             FROM tax
  82              JOIN calculateTaxDetailSummary('VO', _p.vohead_id, 'T') ON (taxdetail_tax_id=tax_id)
  83             GROUP BY tax_id, tax_sales_accnt_id LOOP
  84
  85     PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', _p.vohead_number,
  86                                 _r.tax_sales_accnt_id,
  87                                 (_r.taxbasevalue * -1),
  88                                 _glDate, _p.glnotes );
  89
  90     _totalAmount_base := (_totalAmount_base - _r.taxbasevalue);
  91     _totalAmount := (_totalAmount - _r.tax);
  92
  93   END LOOP;
  94
  95 --  Loop through the vodist records for the passed vohead that
  96 --  are posted against a P/O Item
  97   FOR _g IN SELECT DISTINCT poitem_id, voitem_qty, poitem_expcat_id,
  98                             poitem_invvenduomratio,
  99                             COALESCE(itemsite_id, -1) AS itemsiteid,
 100                             COALESCE(itemsite_costcat_id, -1) AS costcatid,
 101                             COALESCE(itemsite_item_id, -1) AS itemsite_item_id,
 102                             (SELECT SUM(value)
 103                              FROM (
 104                                 SELECT SUM(recv_value) AS value
 105                                 FROM recv
 106                                 WHERE (recv_voitem_id=voitem_id)
 107                              UNION
 108                                 SELECT SUM(poreject_value)*-1 AS value
 109                                 FROM poreject
 110                                 WHERE (poreject_voitem_id=voitem_id)) as data)
 111                             AS value_base,
 112                             (poitem_freight_vouchered / poitem_qty_vouchered) * voitem_qty AS vouchered_freight,
 113                             currToBase(_p.pohead_curr_id, (poitem_freight_vouchered / poitem_qty_vouchered) * voitem_qty, _firstExchDateFreight ) AS vouchered_freight_base,
 114                             voitem_freight,
 115                             currToBase(_p.vohead_curr_id, voitem_freight,
 116                                        _p.vohead_distdate) AS voitem_freight_base
 117             FROM vodist, voitem,
 118                  poitem LEFT OUTER JOIN itemsite ON (poitem_itemsite_id=itemsite_id)
 119             WHERE ( (vodist_poitem_id=poitem_id)
 120              AND (voitem_poitem_id=poitem_id)
 121              AND (voitem_vohead_id=vodist_vohead_id)
 122              AND (vodist_vohead_id=_p.vohead_id)) LOOP
 123
 124 --  Grab the G/L Accounts
 125     IF (_g.costcatid = -1) THEN
 126       SELECT pp.accnt_id AS pp_accnt_id,
 127              lb.accnt_id AS lb_accnt_id INTO _a
 128       FROM expcat, accnt AS pp, accnt AS lb
 129       WHERE ( (expcat_purchprice_accnt_id=pp.accnt_id)
 130        AND (expcat_liability_accnt_id=lb.accnt_id)
 131        AND (expcat_id=_g.poitem_expcat_id) );
 132       IF (NOT FOUND) THEN
 133         RAISE EXCEPTION 'Cannot Void Voucher #% due to unassigned G/L Accounts.', _p.vohead_number;
 134       END IF;
 135     ELSE
 136       SELECT pp.accnt_id AS pp_accnt_id,
 137              lb.accnt_id AS lb_accnt_id INTO _a
 138       FROM costcat, accnt AS pp, accnt AS lb
 139       WHERE ( (costcat_purchprice_accnt_id=pp.accnt_id)
 140        AND (costcat_liability_accnt_id=lb.accnt_id)
 141        AND (costcat_id=_g.costcatid) );
 142       IF (NOT FOUND) THEN
 143         RAISE EXCEPTION 'Cannot Void Voucher #% due to unassigned G/L Accounts.', _p.vohead_number;
 144       END IF;
 145     END IF;
 146
 147 --  Clear the Item Amount accumulator
 148     _itemAmount_base := 0;
 149     _itemAmount := 0;
 150
 151 --  Figure out the total posted value for this line item
 152     FOR _d IN SELECT vodist_id, vodist_amount,
 153                      _p.vohead_curr_id, vodist_costelem_id,
 154                      currToBase(_p.vohead_curr_id, vodist_amount,
 155                                 _p.vohead_distdate) AS vodist_amount_base
 156               FROM vodist
 157               WHERE ( (vodist_vohead_id=_p.vohead_id)
 158                AND (vodist_poitem_id=_g.poitem_id) ) LOOP
 159
 160        _pExplain := TRUE;
 161        SELECT * INTO _costx
 162          FROM itemcost
 163         WHERE ( (itemcost_item_id = _g.itemsite_item_id)
 164           AND   (itemcost_costelem_id = _d.vodist_costelem_id) );
 165
 166        IF (FOUND) THEN
 167          _pExplain := _costx.itemcost_lowlevel;
 168        END IF;
 169
 170 --  Add the Distribution Amount to the Item Amount
 171       _itemAmount_base := _itemAmount_base + ROUND(_d.vodist_amount_base, 2);
 172       _itemAmount := _itemAmount + _d.vodist_amount;
 173
 174     END LOOP;
 175
 176 --  Distribute from the clearing account
 177     PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', text(_p.vohead_number),
 178                                 _a.lb_accnt_id,
 179                                 round(_g.value_base + _g.vouchered_freight_base, 2),
 180                                 _glDate, _p.glnotes );
 181
 182 --  Attribute the correct portion to currency gain/loss
 183     _exchGainFreight := 0;
 184     SELECT currGain(_p.pohead_curr_id, _g.vouchered_freight,
 185                     _firstExchDateFreight, _p.vohead_distdate )
 186                     INTO _exchGainFreight;
 187     IF (round(_exchGainFreight, 2) <> 0) THEN
 188       PERFORM insertIntoGLSeries(_sequence, 'A/P', 'VO',
 189                                  text(_p.vohead_number),
 190                                  getGainLossAccntId(_a.lb_accnt_id), round(_exchGainFreight, 2) * -1,
 191                                  _glDate, _p.glnotes);
 192     END IF;
 193
 194 --  Distribute the remaining variance to the Purchase Price Variance account
 195     IF (round(_itemAmount_base, 2) <> round(_g.value_base, 2)) THEN
 196       _tmpTotal := round(_itemAmount_base, 2) - round(_g.value_base, 2);
 197       PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', text(_p.vohead_number),
 198                                   _a.pp_accnt_id,
 199                                   _tmpTotal,
 200                                   _glDate, _p.glnotes );
 201     END IF;
 202
 203 --  Distribute the remaining freight variance to the Purchase Price Variance account
 204     IF (round(_g.voitem_freight_base + _exchGainFreight, 2) <> round(_g.vouchered_freight_base, 2)) THEN
 205       _tmpTotal := round(_g.voitem_freight_base + _exchGainFreight, 2) - round(_g.vouchered_freight_base, 2);
 206       PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', text(_p.vohead_number),
 207                                   _a.pp_accnt_id,
 208                                   _tmpTotal,
 209                                   _glDate, _p.glnotes );
 210     END IF;
 211
 212 --  Add the distribution amount to the total amount to distribute
 213     _totalAmount_base := (_totalAmount_base + _itemAmount_base + _g.voitem_freight_base);
 214     _totalAmount := (_totalAmount + _itemAmount + _g.voitem_freight);
 215
 216 --  Reverse the posting for all the Tagged Receivings for this P/O Item
 217     UPDATE recv
 218     SET recv_invoiced=FALSE,
 219         recv_recvcost_curr_id=basecurrid(),
 220         recv_recvcost=0,
 221         recv_vohead_id=NULL,
 222         recv_voitem_id=NULL
 223     FROM poitem
 224     WHERE ( (recv_orderitem_id=poitem_id)
 225       AND   (recv_order_type='PO')
 226       AND   (recv_orderitem_id=_g.poitem_id)
 227       AND   (recv_vohead_id=_p.vohead_id) );
 228
 229 --  Reverse the posting for all the Tagged Rejections for this P/O Item
 230     UPDATE poreject
 231     SET poreject_invoiced=FALSE,
 232         poreject_vohead_id=NULL,
 233         poreject_voitem_id=NULL
 234     WHERE ( (poreject_poitem_id=_g.poitem_id)
 235       AND   (poreject_vohead_id=_p.vohead_id) );
 236
 237 --  Update the qty and freight vouchered fields
 238     UPDATE poitem
 239        SET poitem_qty_vouchered = (poitem_qty_vouchered - _g.voitem_qty),
 240            poitem_freight_vouchered = (poitem_freight_vouchered - _g.voitem_freight)
 241     WHERE (poitem_id=_g.poitem_id);
 242
 243   END LOOP;
 244
 245 --  Loop through the vodist records for the passed vohead that
 246 --  are not posted against a P/O Item
 247 --  Skip the tax distributions
 248   FOR _d IN SELECT vodist_id,
 249                    currToBase(_p.vohead_curr_id, vodist_amount,
 250                               _p.vohead_distdate) AS vodist_amount_base,
 251                    vodist_amount,
 252                    vodist_accnt_id, vodist_expcat_id
 253             FROM vodist
 254             WHERE ( (vodist_vohead_id=_p.vohead_id)
 255               AND   (vodist_poitem_id=-1)
 256               AND   (vodist_tax_id=-1) ) LOOP
 257
 258 --  Distribute from the misc. account
 259     IF (_d.vodist_accnt_id = -1) THEN
 260       PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', text(_p.vohead_number),
 261                                   expcat_exp_accnt_id,
 262                                   round(_d.vodist_amount_base, 2),
 263                                   _glDate, _p.glnotes )
 264       FROM expcat
 265       WHERE (expcat_id=_d.vodist_expcat_id);
 266     ELSE
 267       PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', text(_p.vohead_number),
 268                                   _d.vodist_accnt_id,
 269                                   round(_d.vodist_amount_base, 2),
 270                                   _glDate, _p.glnotes );
 271     END IF;
 272
 273 --  Add the Distribution Amount to the Total Amount
 274     _totalAmount_base := _totalAmount_base + ROUND(_d.vodist_amount_base, 2);
 275     _totalAmount := _totalAmount + _d.vodist_amount;
 276
 277   END LOOP;
 278
 279   SELECT insertIntoGLSeries( _sequence, 'A/P', 'VO', text(vohead_number),
 280                              accnt_id, round(_totalAmount_base, 2) * -1,
 281                              _glDate, _p.glnotes ) INTO _test
 282   FROM vohead LEFT OUTER JOIN accnt ON (accnt_id=findAPAccount(vohead_vend_id))
 283   WHERE ( (findAPAccount(vohead_vend_id)=0 OR accnt_id > 0) -- G/L interface might be disabled
 284     AND   (vohead_id=_p.vohead_id) );
 285   IF (NOT FOUND) THEN
 286     RAISE EXCEPTION 'Cannot Void Voucher #% due to an unassigned A/P Account.', _p.vohead_number;
 287   END IF;
 288
 289   PERFORM postGLSeries(_sequence, pJournalNumber);
 290
 291 --  Create the A/P Open Item
 292   SELECT NEXTVAL('apopen_apopen_id_seq') INTO _apopenid;
 293   _reference := ('Void Voucher #' || _n.apopen_docnumber);
 294   INSERT INTO apopen
 295   ( apopen_id, apopen_username, apopen_journalnumber,
 296     apopen_vend_id, apopen_docnumber, apopen_doctype, apopen_ponumber,
 297     apopen_docdate, apopen_duedate, apopen_distdate, apopen_terms_id, apopen_curr_id,
 298     apopen_amount, apopen_paid, apopen_open, apopen_notes, apopen_discount, apopen_curr_rate )
 299   SELECT _apopenid, getEffectiveXtUser(), pJournalnumber,
 300          apopen_vend_id, apopen_docnumber, 'C', apopen_ponumber,
 301          _glDate, _glDate, _glDate, -1, apopen_curr_id,
 302          apopen_amount - apopen_paid, 0, TRUE, _reference, TRUE, apopen_curr_rate
 303     FROM apopen
 304    WHERE (apopen_id=_n.apopen_id);
 305
 306   SELECT apcreditapply_id INTO _apcreditapplyid
 307     FROM apcreditapply
 308    WHERE ( (apcreditapply_source_apopen_id=_apopenid)
 309      AND   (apcreditapply_target_apopen_id=_n.apopen_id) );
 310   IF (FOUND) THEN
 311     UPDATE apcreditapply
 312        SET apcreditapply_amount=_n.apopen_amount-_n.apopen_paid
 313      WHERE (apcreditapply_id=_apcreditapplyid);
 314   ELSE
 315     SELECT nextval('apcreditapply_apcreditapply_id_seq') INTO _apcreditapplyid;
 316     INSERT INTO apcreditapply
 317            ( apcreditapply_id, apcreditapply_source_apopen_id,
 318              apcreditapply_target_apopen_id, apcreditapply_amount,
 319              apcreditapply_curr_id )
 320     VALUES ( _apcreditapplyid, _apopenid, _n.apopen_id, _n.apopen_amount-_n.apopen_paid, _n.apopen_curr_id );
 321   END IF;
 322
 323   SELECT postAPCreditMemoApplication(_apopenid) INTO _result;
 324
 325   IF (_result < 0) THEN
 326     RAISE EXCEPTION 'Credit application failed with result %.', _result;
 327   END IF;
 328
 329 --  Reopen all of the P/O Items that were closed by this Voucher
 330   UPDATE poitem
 331   SET poitem_status='O'
 332   FROM voitem
 333   WHERE ( (voitem_poitem_id=poitem_id)
 334     AND   (voitem_close)
 335     AND   (voitem_vohead_id=_p.vohead_id) );
 336
 337 --  Reopen the P/O
 338   UPDATE pohead
 339   SET pohead_status='O'
 340   WHERE (pohead_id=_p.vohead_pohead_id);
 341
 342 --  Mark as voided
 343   UPDATE apopen
 344   SET apopen_void=TRUE
 345   WHERE (apopen_id=_n.apopen_id);
 346
 347   RETURN pJournalNumber;
 348
 349 END;
 350 $$ LANGUAGE 'plpgsql';