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

   1 CREATE OR REPLACE FUNCTION reverseCashReceipt(INTEGER, 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   pCashrcptid ALIAS FOR $1;
   6   pJournalNumber ALIAS FOR $2;
   7   _p RECORD;
   8   _r RECORD;
   9   _postToAR NUMERIC;
  10   _postToMisc NUMERIC;
  11   _posted_base NUMERIC := 0;
  12   _posted NUMERIC := 0;
  13   _sequence INTEGER;
  14   _aropenid INTEGER;
  15   _arMemoNumber TEXT;
  16   _arAccntid INTEGER;
  17   _closed BOOLEAN;
  18   _debitAccntid INTEGER;
  19   _exchGain NUMERIC;
  20   _comment      TEXT;
  21   _ccpayid  INTEGER;
  22   _cctype TEXT;
  23
  24 BEGIN
  25   _posted := 0;
  26   _posted_base := 0;
  27
  28   SELECT fetchGLSequence() INTO _sequence;
  29
  30   SELECT accnt_id INTO _arAccntid
  31   FROM cashrcpt, accnt, salescat
  32   WHERE ((cashrcpt_salescat_id=salescat_id)
  33     AND  (salescat_ar_accnt_id=accnt_id)
  34     AND  (cashrcpt_id=pCashrcptid));
  35   IF (NOT FOUND) THEN
  36     SELECT accnt_id INTO _arAccntid
  37     FROM cashrcpt, accnt
  38     WHERE ( (findARAccount(cashrcpt_cust_id)=accnt_id)
  39      AND (cashrcpt_id=pCashrcptid) );
  40     IF (NOT FOUND) THEN
  41       RETURN -5;
  42     END IF;
  43   END IF;
  44
  45   SELECT cashrcpt_cust_id, ('Reverse Cash Receipt posting for ' || cust_number||'-'||cust_name) AS custnote,
  46          cashrcpt_fundstype, cashrcpt_number, cashrcpt_docnumber,
  47          cashrcpt_distdate, cashrcpt_amount, cashrcpt_discount,
  48          (cashrcpt_amount / cashrcpt_curr_rate) AS cashrcpt_amount_base,
  49          (cashrcpt_discount / cashrcpt_curr_rate) AS cashrcpt_discount_base,
  50          cashrcpt_notes,
  51          cashrcpt_bankaccnt_id AS bankaccnt_id,
  52          accnt_id AS prepaid_accnt_id,
  53          cashrcpt_usecustdeposit,
  54          cashrcpt_curr_id, cashrcpt_curr_rate INTO _p
  55   FROM accnt, cashrcpt LEFT OUTER JOIN custinfo ON (cashrcpt_cust_id=cust_id)
  56   WHERE ( (findPrepaidAccount(cashrcpt_cust_id)=accnt_id)
  57    AND (cashrcpt_id=pCashrcptid) );
  58   IF (NOT FOUND) THEN
  59     RETURN -7;
  60   END IF;
  61
  62   IF (_p.cashrcpt_fundstype IN ('A', 'D', 'M', 'V')) THEN
  63     SELECT ccpay_id, ccpay_type INTO _ccpayid, _cctype
  64     FROM ccpay
  65     WHERE ((ccpay_r_ordernum IN (CAST(pCashrcptid AS TEXT), _p.cashrcpt_docnumber))
  66        AND (ccpay_status IN ('C', 'A')));
  67
  68     IF NOT FOUND THEN
  69       -- the following select seems to work except for xikar - bug 8848. why?
  70       -- raise warning so there is some visibility if people fall into this path.
  71       SELECT ccpay_id, ccpay_type INTO _ccpayid, _cctype
  72       FROM ccpay
  73       WHERE ((ccpay_order_number IN (CAST(pCashrcptid AS TEXT), _p.cashrcpt_docnumber))
  74          AND (ccpay_status IN ('C', 'A')));
  75       IF (NOT FOUND) THEN
  76         RETURN -8;
  77       ELSE
  78         RAISE NOTICE 'PostCashReceipt() found ccpay_id % for order number %/% (ref 8848).',
  79                       _ccpayid, pCashrcptid, _p.cashrcpt_docnumber;
  80       END IF;
  81     END IF;
  82
  83 -- If there is a ccpay entry and the card was charged directly, use the prepaid account
  84     IF (_cctype = 'C' ) THEN
  85       _debitAccntid := findPrepaidAccount(_p.cashrcpt_cust_id);
  86 -- If there is a ccpay entry and the card was preauthed and then charged, use the Bank account
  87     ELSE
  88       SELECT accnt_id INTO _debitAccntid
  89       FROM cashrcpt, bankaccnt, accnt
  90       WHERE ( (cashrcpt_bankaccnt_id=bankaccnt_id)
  91        AND (bankaccnt_accnt_id=accnt_id)
  92        AND (cashrcpt_id=pCashrcptid) );
  93       IF (NOT FOUND) THEN
  94         RETURN -6;
  95       END IF;
  96     END IF;
  97   ELSE
  98     SELECT accnt_id INTO _debitAccntid
  99     FROM cashrcpt, bankaccnt, accnt
 100     WHERE ( (cashrcpt_bankaccnt_id=bankaccnt_id)
 101      AND (bankaccnt_accnt_id=accnt_id)
 102      AND (cashrcpt_id=pCashrcptid) );
 103     IF (NOT FOUND) THEN
 104       RETURN -6;
 105     END IF;
 106   END IF;
 107
 108 --  Determine the amount to post to A/R Open Items
 109   SELECT COALESCE(SUM(cashrcptitem_amount),0) INTO _postToAR
 110   FROM cashrcptitem JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
 111   WHERE ((cashrcptitem_cashrcpt_id=pCashrcptid)
 112    AND (cashrcptitem_applied));
 113   IF (NOT FOUND) THEN
 114     _postToAR := 0;
 115   END IF;
 116
 117 --  Determine the amount to post to Misc. Distributions
 118   SELECT COALESCE(SUM(cashrcptmisc_amount),0) INTO _postToMisc
 119   FROM cashrcptmisc
 120   WHERE (cashrcptmisc_cashrcpt_id=pCashrcptid);
 121   IF (NOT FOUND) THEN
 122     _postToMisc := 0;
 123   END IF;
 124
 125 --  Check to see if the C/R is over applied
 126   IF ((_postToAR + _postToMisc) > _p.cashrcpt_amount) THEN
 127     RETURN -1;
 128   END IF;
 129
 130 --  Check to see if the C/R is positive amount
 131   IF (_p.cashrcpt_amount <= 0) THEN
 132     RETURN -2;
 133   END IF;
 134
 135 --  Distribute A/R Applications
 136   FOR _r IN SELECT aropen_id, aropen_doctype, aropen_docnumber, aropen_docdate,
 137                    aropen_duedate, aropen_curr_id, aropen_curr_rate,
 138                    round(aropen_amount - aropen_paid, 2) <=
 139                       round(aropen_paid +
 140                       currToCurr(_p.cashrcpt_curr_id, aropen_curr_id,abs(cashrcptitem_amount + cashrcptitem_discount),_p.cashrcpt_distdate),2)
 141                                AS closed,
 142                    cashrcptitem_id, cashrcptitem_amount, cashrcptitem_discount,
 143                    (cashrcptitem_amount / _p.cashrcpt_curr_rate) AS cashrcptitem_amount_base,
 144                    (cashrcptitem_discount / _p.cashrcpt_curr_rate) AS cashrcptitem_discount_base,
 145                    round(aropen_paid -
 146                       currToCurr(_p.cashrcpt_curr_id, aropen_curr_id,abs(cashrcptitem_amount),_p.cashrcpt_distdate),2) AS new_paid,
 147                    round(currToCurr(_p.cashrcpt_curr_id, aropen_curr_id,abs(cashrcptitem_discount),_p.cashrcpt_distdate),2) AS new_discount
 148             FROM cashrcptitem JOIN aropen ON (cashrcptitem_aropen_id=aropen_id)
 149             WHERE ((cashrcptitem_cashrcpt_id=pCashrcptid)
 150               AND (cashrcptitem_applied)) LOOP
 151
 152 --  Handle discount
 153     IF (_r.cashrcptitem_discount_base > 0) THEN
 154       PERFORM reverseCashReceiptDisc(_r.cashrcptitem_id, pJournalNumber);
 155     END IF;
 156
 157 --  Update the aropen item to post the paid amount
 158     UPDATE aropen
 159     SET aropen_paid = _r.new_paid - _r.new_discount,
 160         aropen_open = TRUE,
 161         aropen_closedate = NULL
 162     WHERE (aropen_id=_r.aropen_id);
 163
 164 --  Cache the running amount posted
 165     _posted_base := _posted_base + _r.cashrcptitem_amount_base;
 166     _posted := _posted + _r.cashrcptitem_amount;
 167
 168 --  Record the cashrcpt application
 169     IF (_r.aropen_doctype IN ('I','D')) THEN
 170       INSERT INTO arapply
 171       ( arapply_cust_id,
 172         arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
 173         arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
 174         arapply_fundstype, arapply_refnumber, arapply_reftype, arapply_ref_id,
 175         arapply_applied, arapply_closed,
 176         arapply_postdate, arapply_distdate, arapply_journalnumber, arapply_username,
 177         arapply_curr_id
 178        )
 179       VALUES
 180       ( _p.cashrcpt_cust_id,
 181         -1, 'K', _p.cashrcpt_number,
 182         _r.aropen_id, _r.aropen_doctype, _r.aropen_docnumber,
 183         _p.cashrcpt_fundstype, _p.cashrcpt_docnumber, 'CRA', _r.cashrcptitem_id,
 184         (round(_r.cashrcptitem_amount, 2) * -1.0), _r.closed,
 185         CURRENT_DATE, _p.cashrcpt_distdate, pJournalNumber, getEffectiveXtUser(), _p.cashrcpt_curr_id );
 186     ELSE
 187       INSERT INTO arapply
 188       ( arapply_cust_id,
 189         arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
 190         arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
 191         arapply_fundstype, arapply_refnumber, arapply_reftype, arapply_ref_id,
 192         arapply_applied, arapply_closed, arapply_postdate, arapply_distdate,
 193         arapply_journalnumber, arapply_username, arapply_curr_id )
 194       VALUES
 195       ( _p.cashrcpt_cust_id,
 196         _r.aropen_id, _r.aropen_doctype, _r.aropen_docnumber,
 197         -1, 'R', _p.cashrcpt_number,
 198         '', '', 'CRA', _r.cashrcptitem_id,
 199         (round(abs(_r.cashrcptitem_amount), 2) * -1.0), _r.closed,
 200         CURRENT_DATE, _p.cashrcpt_distdate, pJournalNumber, getEffectiveXtUser(), _p.cashrcpt_curr_id );
 201     END IF;
 202
 203     _exchGain := arCurrGain(_r.aropen_id,_p.cashrcpt_curr_id, abs(_r.cashrcptitem_amount),
 204                            _p.cashrcpt_distdate);
 205
 206     PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR',
 207                         (_r.aropen_doctype || '-' || _r.aropen_docnumber),
 208                         CASE WHEN _r.aropen_doctype != 'R' THEN _arAccntid
 209                         ELSE findDeferredAccount(_p.cashrcpt_cust_id) END,
 210                         (round(_r.cashrcptitem_amount_base + _exchGain, 2) * -1.0),
 211                         _p.cashrcpt_distdate, _p.custnote );
 212
 213     IF (_exchGain <> 0) THEN
 214         PERFORM insertIntoGLSeries(_sequence, 'A/R', 'CR',
 215                _r.aropen_doctype || '-' || _r.aropen_docnumber,
 216                getGainLossAccntId(
 217                CASE WHEN _r.aropen_doctype != 'R' THEN _arAccntid
 218                ELSE findDeferredAccount(_p.cashrcpt_cust_id) END
 219                ), round(_exchGain, 2),
 220                _p.cashrcpt_distdate, _p.custnote);
 221
 222     END IF;
 223
 224   END LOOP;
 225
 226 --  Distribute Misc. Applications
 227   FOR _r IN SELECT cashrcptmisc_id, cashrcptmisc_accnt_id, cashrcptmisc_amount,
 228                    (cashrcptmisc_amount / _p.cashrcpt_curr_rate) AS cashrcptmisc_amount_base,
 229                    cashrcptmisc_notes
 230             FROM cashrcptmisc
 231             WHERE (cashrcptmisc_cashrcpt_id=pCashrcptid)  LOOP
 232
 233 --  Cache the running amount posted
 234     _posted_base := (_posted_base + _r.cashrcptmisc_amount_base);
 235     _posted := (_posted + _r.cashrcptmisc_amount);
 236
 237 --  Record the cashrcpt application
 238     INSERT INTO arapply
 239     ( arapply_cust_id,
 240       arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
 241       arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
 242       arapply_fundstype, arapply_refnumber,
 243       arapply_applied, arapply_closed,
 244       arapply_postdate, arapply_distdate, arapply_journalnumber, arapply_username,
 245       arapply_curr_id, arapply_reftype, arapply_ref_id )
 246     VALUES
 247     ( _p.cashrcpt_cust_id,
 248       -1, 'K', '',
 249       -1, 'Misc.', '',
 250       _p.cashrcpt_fundstype, _p.cashrcpt_docnumber,
 251       (round(_r.cashrcptmisc_amount, 2) * -1.0), TRUE,
 252       CURRENT_DATE, _p.cashrcpt_distdate, pJournalNumber, getEffectiveXtUser(),
 253       _p.cashrcpt_curr_id, 'CRD', _r.cashrcptmisc_id );
 254
 255     PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR', _r.cashrcptmisc_notes,
 256                                 _r.cashrcptmisc_accnt_id,
 257                                 (round(_r.cashrcptmisc_amount_base, 2) * -1.0),
 258                                 _p.cashrcpt_distdate, _p.custnote );
 259
 260   END LOOP;
 261
 262 --  Post any remaining Cash to an A/R Debit Memo
 263 --  this credit memo may absorb an occasional currency exchange rounding error
 264   IF (round(_posted_base, 2) < round(_p.cashrcpt_amount_base, 2)) THEN
 265     _comment := ('Unapplied from ' || _p.cashrcpt_fundstype || '-' || _p.cashrcpt_docnumber);
 266     PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR',
 267                                 _comment,
 268                                 _p.prepaid_accnt_id,
 269                                 ((round(_p.cashrcpt_amount_base, 2) - round(_posted_base, 2)) * -1.0),
 270                                 _p.cashrcpt_distdate, _p.custnote );
 271     SELECT fetchArMemoNumber() INTO _arMemoNumber;
 272     -- Post A/R Debit Memo
 273     SELECT createARDebitMemo(NULL, _p.cashrcpt_cust_id, pJournalNumber, _arMemoNumber, '',
 274                               _p.cashrcpt_distdate, (_p.cashrcpt_amount - _posted),
 275                               _comment, -1, -1, -1, _p.cashrcpt_distdate, -1, NULL, 0,
 276                               _p.cashrcpt_curr_id) INTO _aropenid;
 277     -- Create Cash Receipt Item to capture posting
 278     INSERT INTO cashrcptitem
 279       ( cashrcptitem_cashrcpt_id, cashrcptitem_aropen_id, cashrcptitem_amount )
 280     VALUES
 281       ( pCashrcptid, _aropenid, ((_p.cashrcpt_amount - _posted) * 1.0) );
 282
 283   ELSIF (round(_posted_base, 2) > round(_p.cashrcpt_amount_base, 2)) THEN
 284     PERFORM insertIntoGLSeries(_sequence, 'A/R', 'CR',
 285                    'Currency Exchange Rounding - ' || _p.cashrcpt_docnumber,
 286                    getGainLossAccntId(_debitAccntid),
 287                    ((round(_posted_base, 2) - round((_p.cashrcpt_amount_base + _p.cashrcpt_discount_base), 2)) * 1.0),
 288                    _p.cashrcpt_distdate, _p.custnote);
 289   END IF;
 290
 291 --  Debit Cash
 292   PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR',
 293                     (_p.cashrcpt_fundstype || '-' || _p.cashrcpt_docnumber),
 294                      _debitAccntid, round(_p.cashrcpt_amount_base, 2),
 295                      _p.cashrcpt_distdate,
 296                      _p.custnote );
 297
 298   PERFORM postGLSeries(_sequence, pJournalNumber);
 299
 300 --  Update and void the posted cashrcpt
 301   UPDATE cashrcpt SET cashrcpt_posted=FALSE,
 302                       cashrcpt_posteddate=NULL,
 303                       cashrcpt_postedby=NULL,
 304                       cashrcpt_void=TRUE
 305   WHERE (cashrcpt_id=pCashrcptid);
 306
 307   RETURN 1;
 308
 309 END;
 310 $$ LANGUAGE 'plpgsql';