git.roojs.org Git - Pman.Xtuple/blob - pgsql/voidapopenvoucher.sql

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