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