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