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

   1 CREATE OR REPLACE FUNCTION postCashReceipt(pCashrcptid    INTEGER,
   2                                            pJournalNumber INTEGER) RETURNS INTEGER AS $$
   3 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   4 -- See www.xtuple.com/CPAL for the full text of the software license.
   5 DECLARE
   6   _ccpayid  INTEGER;
   7   _cctype TEXT;
   8   _p RECORD;
   9   _r RECORD;
  10   _t RECORD;
  11   _v RECORD;
  12   _postToAR NUMERIC;
  13   _postToMisc NUMERIC;
  14   _postToCM NUMERIC;
  15   _posted_base NUMERIC := 0;
  16   _posted NUMERIC := 0;
  17   _sequence INTEGER;
  18   _aropenid INTEGER;
  19   _arMemoNumber TEXT;
  20   _arAccntid INTEGER;
  21   _closed BOOLEAN;
  22   _debitAccntid INTEGER;
  23   _exchGain NUMERIC;
  24   _comment TEXT;
  25   _predist BOOLEAN;
  26   _check INTEGER;
  27
  28 BEGIN
  29   _posted := 0;
  30   _posted_base := 0;
  31
  32   SELECT fetchGLSequence() INTO _sequence;
  33
  34   SELECT accnt_id INTO _arAccntid
  35   FROM cashrcpt, accnt, salescat
  36   WHERE ((cashrcpt_salescat_id=salescat_id)
  37     AND  (salescat_ar_accnt_id=accnt_id)
  38     AND  (cashrcpt_id=pCashrcptid));
  39   IF (NOT FOUND) THEN
  40     SELECT accnt_id INTO _arAccntid
  41     FROM cashrcpt LEFT OUTER JOIN accnt ON (accnt_id=findARAccount(cashrcpt_cust_id))
  42     WHERE ( (findARAccount(cashrcpt_cust_id)=0 OR accnt_id > 0) -- G/L interface might be disabled
  43      AND (cashrcpt_id=pCashrcptid) );
  44     IF (NOT FOUND) THEN
  45       RETURN -5;
  46     END IF;
  47   END IF;
  48
  49   SELECT cashrcpt_cust_id, (cust_number||'-'||cust_name) AS custnote,
  50          cashrcpt_fundstype, cashrcpt_number, cashrcpt_docnumber,
  51          cashrcpt_distdate, cashrcpt_amount, cashrcpt_discount,
  52          (cashrcpt_amount / cashrcpt_curr_rate) AS cashrcpt_amount_base,
  53          (cashrcpt_discount / cashrcpt_curr_rate) AS cashrcpt_discount_base,
  54          cashrcpt_notes,
  55          cashrcpt_bankaccnt_id AS bankaccnt_id,
  56          accnt_id AS prepaid_accnt_id,
  57          cashrcpt_usecustdeposit,
  58          COALESCE(cashrcpt_applydate, cashrcpt_distdate) AS applydate,
  59          cashrcpt_curr_id, cashrcpt_curr_rate, cashrcpt_posted, cashrcpt_void INTO _p
  60   FROM cashrcpt LEFT OUTER JOIN custinfo ON (cashrcpt_cust_id=cust_id)
  61                 LEFT OUTER JOIN accnt ON (accnt_id=findPrepaidAccount(cashrcpt_cust_id))
  62   WHERE ( (findPrepaidAccount(cashrcpt_cust_id)=0 OR accnt_id > 0) -- G/L interface might be disabled
  63      AND (cashrcpt_id=pCashrcptid) );
  64   IF (NOT FOUND) THEN
  65     RETURN -7;
  66   END IF;
  67
  68   IF (COALESCE(_p.cashrcpt_distdate > _p.applydate, false)) THEN
  69     RAISE EXCEPTION 'Cannot post cashrcpt % because application date is before distribution date.', _p.cashrcpt_docnumber;
  70   END IF;
  71
  72   IF (COALESCE(_p.cashrcpt_posted, false)) THEN
  73     RAISE EXCEPTION 'Cannot post cashrcpt % because the document has already been posted.', _p.cashrcpt_docnumber;
  74   END IF;
  75
  76   IF (COALESCE(_p.cashrcpt_void, false)) THEN
  77     RAISE EXCEPTION 'Cannot post cashrcpt % because the document has been voided.', _p.cashrcpt_docnumber;
  78   END IF;
  79
  80   _predist := COALESCE(_p.cashrcpt_distdate < _p.applydate, false);
  81
  82   IF (_p.cashrcpt_fundstype IN ('A', 'D', 'M', 'V')) THEN
  83     SELECT ccpay_id, ccpay_type INTO _ccpayid, _cctype
  84     FROM ccpay
  85     WHERE ((ccpay_r_ordernum IN (CAST(pCashrcptid AS TEXT), _p.cashrcpt_docnumber))
  86        AND (ccpay_status IN ('C', 'A')));
  87
  88     IF NOT FOUND THEN
  89       -- the following select seems to work except for xikar - bug 8848. why?
  90       -- raise warning so there is some visibility if people fall into this path.
  91       SELECT ccpay_id, ccpay_type INTO _ccpayid, _cctype
  92       FROM ccpay
  93       WHERE ((ccpay_order_number IN (CAST(pCashrcptid AS TEXT), _p.cashrcpt_docnumber))
  94          AND (ccpay_status IN ('C', 'A')));
  95       IF (NOT FOUND) THEN
  96         RETURN -8;
  97       ELSE
  98         RAISE NOTICE 'PostCashReceipt() found ccpay_id % for order number %/% (ref 8848).',
  99                       _ccpayid, pCashrcptid, _p.cashrcpt_docnumber;
 100       END IF;
 101     END IF;
 102
 103 -- If there is a ccpay entry and the card was charged directly, use the prepaid account
 104     IF (_cctype = 'C' ) THEN
 105       _debitAccntid := findPrepaidAccount(_p.cashrcpt_cust_id);
 106 -- If there is a ccpay entry and the card was preauthed and then charged, use the Bank account
 107     ELSE
 108       SELECT accnt_id INTO _debitAccntid
 109       FROM cashrcpt, bankaccnt, accnt
 110       WHERE ( (cashrcpt_bankaccnt_id=bankaccnt_id)
 111        AND (bankaccnt_accnt_id=accnt_id)
 112        AND (cashrcpt_id=pCashrcptid) );
 113       IF (NOT FOUND) THEN
 114         RETURN -6;
 115       END IF;
 116     END IF;
 117   ELSE
 118     SELECT accnt_id INTO _debitAccntid
 119     FROM cashrcpt, bankaccnt, accnt
 120     WHERE ( (cashrcpt_bankaccnt_id=bankaccnt_id)
 121      AND (bankaccnt_accnt_id=accnt_id)
 122      AND (cashrcpt_id=pCashrcptid) );
 123     IF (NOT FOUND) THEN
 124       RETURN -6;
 125     END IF;
 126   END IF;
 127
 128 --  Determine the amount to post to A/R Open Items
 129   SELECT COALESCE(SUM(cashrcptitem_amount), 0) INTO _postToAR
 130   FROM cashrcptitem JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
 131   WHERE (cashrcptitem_cashrcpt_id=pCashrcptid);
 132   IF (NOT FOUND) THEN
 133     _postToAR := 0;
 134   END IF;
 135
 136 --  Determine the amount to post to Misc. Distributions
 137   SELECT COALESCE(SUM(cashrcptmisc_amount), 0) INTO _postToMisc
 138   FROM cashrcptmisc
 139   WHERE (cashrcptmisc_cashrcpt_id=pCashrcptid);
 140   IF (NOT FOUND) THEN
 141     _postToMisc := 0;
 142   END IF;
 143
 144 --  Determine the amount to post to Discount Credit Memo
 145   SELECT COALESCE(SUM(cashrcptitem_discount), 0) INTO _postToCM
 146   FROM cashrcptitem JOIN aropen ON ( (aropen_id=cashrcptitem_aropen_id) AND (aropen_doctype IN ('I', 'D')) )
 147   WHERE (cashrcptitem_cashrcpt_id=pCashrcptid);
 148   IF (NOT FOUND) THEN
 149     _postToCM := 0;
 150   END IF;
 151
 152 --  Check to see if the C/R is over applied
 153   IF ((_postToAR + _postToMisc) > _p.cashrcpt_amount) THEN
 154     RETURN -1;
 155   END IF;
 156
 157 --  Check to see if the C/R is positive amount
 158   IF (_p.cashrcpt_amount <= 0) THEN
 159     RETURN -2;
 160   END IF;
 161
 162 --  Distribute A/R Applications
 163     FOR _r IN SELECT aropen_id, aropen_doctype, aropen_docnumber, aropen_docdate,
 164                      aropen_duedate, aropen_curr_id, aropen_curr_rate, aropen_amount,
 165                      round(aropen_amount - aropen_paid, 2) <=
 166                         round(currToCurr(_p.cashrcpt_curr_id, aropen_curr_id,abs(cashrcptitem_amount + cashrcptitem_discount),_p.cashrcpt_distdate),2)
 167                                  AS closed,
 168                      cashrcptitem_id, cashrcptitem_amount, cashrcptitem_discount,
 169                      (cashrcptitem_amount / _p.cashrcpt_curr_rate) AS cashrcptitem_amount_base,
 170                      (cashrcptitem_discount / _p.cashrcpt_curr_rate) AS cashrcptitem_discount_base,
 171                      round(aropen_paid +
 172                        currToCurr(_p.cashrcpt_curr_id, aropen_curr_id,abs(cashrcptitem_amount),_p.cashrcpt_distdate),2) AS new_paid,
 173                      round(currToCurr(_p.cashrcpt_curr_id, aropen_curr_id,cashrcptitem_discount,_p.cashrcpt_distdate),2) AS new_discount
 174               FROM cashrcptitem JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
 175               WHERE ((cashrcptitem_cashrcpt_id=pCashrcptid)
 176                AND (NOT _predist OR aropen_doctype IN ('C','R'))) LOOP
 177
 178   --  Handle discount
 179       IF (_r.cashrcptitem_discount_base > 0) THEN
 180         PERFORM postCashReceiptDisc(_r.cashrcptitem_id, pJournalNumber);
 181       END IF;
 182
 183   --  Update the aropen item to post the paid amount
 184       UPDATE aropen
 185       SET aropen_paid = _r.new_paid + _r.new_discount,
 186           aropen_open = (NOT _r.closed),
 187           aropen_closedate = CASE WHEN _r.closed THEN _p.cashrcpt_distdate END
 188       WHERE (aropen_id=_r.aropen_id);
 189
 190   --  Cache the running amount posted
 191       _posted_base := _posted_base + _r.cashrcptitem_amount_base;
 192       _posted := _posted + _r.cashrcptitem_amount;
 193
 194   --  Record the cashrcpt application
 195     IF (_r.aropen_doctype IN ('I','D')) THEN
 196       INSERT INTO arapply
 197       ( arapply_cust_id,
 198         arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
 199         arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
 200         arapply_fundstype, arapply_refnumber, arapply_reftype, arapply_ref_id,
 201         arapply_applied, arapply_closed,
 202         arapply_postdate, arapply_distdate, arapply_journalnumber, arapply_username,
 203         arapply_curr_id )
 204       VALUES
 205       ( _p.cashrcpt_cust_id,
 206         -1, 'K', _p.cashrcpt_number,
 207         _r.aropen_id, _r.aropen_doctype, _r.aropen_docnumber,
 208         _p.cashrcpt_fundstype, _p.cashrcpt_docnumber, 'CRA', _r.cashrcptitem_id,
 209         round(_r.cashrcptitem_amount, 2), _r.closed,
 210         _p.applydate, _p.cashrcpt_distdate, pJournalNumber, getEffectiveXtUser(), _p.cashrcpt_curr_id);
 211     ELSE
 212       INSERT INTO arapply
 213       ( arapply_cust_id,
 214         arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
 215         arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
 216         arapply_fundstype, arapply_refnumber, arapply_reftype, arapply_ref_id,
 217         arapply_applied, arapply_closed, arapply_postdate, arapply_distdate,
 218         arapply_journalnumber, arapply_username, arapply_curr_id )
 219       VALUES
 220       ( _p.cashrcpt_cust_id,
 221         _r.aropen_id, _r.aropen_doctype, _r.aropen_docnumber,
 222         -1, 'R', _p.cashrcpt_number,
 223         '', '', 'CRA', _r.cashrcptitem_id,
 224         round(abs(_r.cashrcptitem_amount), 2), _r.closed,
 225         _p.applydate, _p.cashrcpt_distdate, pJournalNumber, getEffectiveXtUser(), _p.cashrcpt_curr_id );
 226     END IF;
 227
 228       _exchGain := arCurrGain(_r.aropen_id,_p.cashrcpt_curr_id, abs(_r.cashrcptitem_amount),
 229                              _p.cashrcpt_distdate);
 230
 231        PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR',
 232                           (_r.aropen_doctype || '-' || _r.aropen_docnumber),
 233                           CASE WHEN _r.aropen_doctype != 'R' THEN _arAccntid
 234                           ELSE findDeferredAccount(_p.cashrcpt_cust_id) END,
 235                           round(_r.cashrcptitem_amount_base + _exchGain, 2),
 236                           _p.cashrcpt_distdate, _p.custnote, pCashrcptid );
 237
 238       IF (_exchGain <> 0) THEN
 239           PERFORM insertIntoGLSeries(_sequence, 'A/R', 'CR',
 240                  _r.aropen_doctype || '-' || _r.aropen_docnumber,
 241                  getGainLossAccntId(
 242                    CASE WHEN _r.aropen_doctype != 'R' THEN _arAccntid
 243                    ELSE findDeferredAccount(_p.cashrcpt_cust_id) END
 244                  ), round(_exchGain, 2) * -1,
 245                  _p.cashrcpt_distdate, _p.custnote, pCashrcptid);
 246       END IF;
 247
 248     END LOOP;
 249
 250 --  Distribute Misc. Applications
 251   FOR _r IN SELECT cashrcptmisc_id, cashrcptmisc_accnt_id, cashrcptmisc_amount,
 252                    (cashrcptmisc_amount / cashrcpt_curr_rate) AS cashrcptmisc_amount_base,
 253                    cashrcptmisc_notes, cashrcpt_curr_id
 254             FROM cashrcptmisc JOIN
 255                  cashrcpt ON (cashrcptmisc_cashrcpt_id = cashrcpt_id)
 256             WHERE (cashrcptmisc_cashrcpt_id=pCashrcptid)  LOOP
 257
 258 --  Cache the running amount posted
 259     _posted_base := (_posted_base + _r.cashrcptmisc_amount_base);
 260     _posted := (_posted + _r.cashrcptmisc_amount);
 261
 262 --  Record the cashrcpt application
 263     INSERT INTO arapply
 264     ( arapply_cust_id,
 265       arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
 266       arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
 267       arapply_fundstype, arapply_refnumber,
 268       arapply_applied, arapply_closed,
 269       arapply_postdate, arapply_distdate, arapply_journalnumber, arapply_username,
 270       arapply_curr_id, arapply_reftype, arapply_ref_id )
 271     VALUES
 272     ( _p.cashrcpt_cust_id,
 273       -1, 'K', '',
 274       -1, 'Misc.', '',
 275       _p.cashrcpt_fundstype, _p.cashrcpt_docnumber,
 276       round(_r.cashrcptmisc_amount, 2), TRUE,
 277       _p.applydate, _p.cashrcpt_distdate, pJournalNumber, getEffectiveXtUser(),
 278       _r.cashrcpt_curr_id, 'CRD', _r.cashrcptmisc_id );
 279     PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR', _r.cashrcptmisc_notes,
 280                                 _r.cashrcptmisc_accnt_id,
 281                                 round(_r.cashrcptmisc_amount_base, 2),
 282                                 _p.cashrcpt_distdate, _p.custnote, pCashrcptid );
 283
 284   END LOOP;
 285
 286 --  Post any remaining Cash to an A/R Cash Despoit (Credit Memo)
 287 --  this credit memo may absorb an occasional currency exchange rounding error
 288   IF (round(_posted_base, 2) < round(_p.cashrcpt_amount_base, 2)) THEN
 289     _comment := ('Unapplied from ' || _p.cashrcpt_fundstype || '-' || _p.cashrcpt_docnumber);
 290     PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR',
 291                                 _comment,
 292                                 _p.prepaid_accnt_id,
 293                                 round(_p.cashrcpt_amount_base, 2) -
 294                                                         round(_posted_base, 2),
 295                                 _p.cashrcpt_distdate, _p.custnote, pCashrcptid );
 296     SELECT fetchArMemoNumber() INTO _arMemoNumber;
 297     IF(_p.cashrcpt_usecustdeposit) THEN
 298       -- Post Customer Deposit
 299       SELECT createARCashDeposit(_p.cashrcpt_cust_id, _arMemoNumber, '',
 300                                  _p.cashrcpt_distdate, (_p.cashrcpt_amount - _posted),
 301                                  _comment, pJournalNumber, _p.cashrcpt_curr_id) INTO _aropenid;
 302     ELSE
 303       -- Post A/R Credit Memo
 304       _aropenid := createARCreditMemo(NULL, _p.cashrcpt_cust_id, _arMemoNumber, '',
 305                                 _p.cashrcpt_distdate, (_p.cashrcpt_amount - _posted),
 306                                 _comment, -1, -1, -1, _p.cashrcpt_distdate, -1, NULL, 0,
 307                                 pJournalNumber, _p.cashrcpt_curr_id, _arAccntid);
 308     END IF;
 309
 310     IF (_ccpayid IS NOT NULL) THEN
 311       INSERT INTO payaropen (payaropen_ccpay_id, payaropen_aropen_id,
 312                              payaropen_amount,   payaropen_curr_id
 313                    ) VALUES (_ccpayid,           _aropenid,
 314                              _p.cashrcpt_amount, _p.cashrcpt_curr_id);
 315     END IF;
 316
 317     -- Create Cash Receipt Item to capture posting
 318     IF (_predist=false) THEN
 319       INSERT INTO cashrcptitem
 320         ( cashrcptitem_cashrcpt_id, cashrcptitem_aropen_id, cashrcptitem_amount, cashrcptitem_applied )
 321       VALUES
 322         ( pCashrcptid, _aropenid, (_p.cashrcpt_amount - _posted), false );
 323     END IF;
 324
 325   ELSIF (round(_posted_base, 2) > round((_p.cashrcpt_amount_base), 2)) THEN
 326     PERFORM insertIntoGLSeries(_sequence, 'A/R', 'CR',
 327                    'Currency Exchange Rounding - ' || _p.cashrcpt_docnumber,
 328                    getGainLossAccntId(_debitAccntid),
 329                    round(_posted_base, 2) - round((_p.cashrcpt_amount_base + _p.cashrcpt_discount_base), 2),
 330                    _p.cashrcpt_distdate, _p.custnote, pCashrcptid);
 331   END IF;
 332
 333 --  Debit Cash
 334   PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR',
 335                     (_p.cashrcpt_fundstype || '-' || _p.cashrcpt_docnumber),
 336                      _debitAccntid, round(_p.cashrcpt_amount_base, 2) * -1,
 337                      _p.cashrcpt_distdate,
 338                      _p.custnote, pCashrcptid );
 339
 340   PERFORM postGLSeries(_sequence, pJournalNumber);
 341
 342   -- convert the cashrcptitem records to applications against the cm/cd if we are _predist
 343   IF(_predist=true) THEN
 344     FOR _r IN SELECT *
 345               FROM cashrcptitem
 346               WHERE ((cashrcptitem_cashrcpt_id=pCashrcptid)
 347                 AND (cashrcptitem_amount > 0)) LOOP
 348
 349       -- Handle discount if applicable
 350       IF (_r.cashrcptitem_discount > 0) THEN
 351         PERFORM postCashReceiptDisc(_r.cashrcptitem_id, pJournalNumber);
 352       END IF;
 353
 354       INSERT INTO arcreditapply (arcreditapply_source_aropen_id, arcreditapply_target_aropen_id,
 355                                  arcreditapply_amount, arcreditapply_curr_id)
 356                           VALUES(_aropenid, _r.cashrcptitem_aropen_id,
 357                                  _r.cashrcptitem_amount, _p.cashrcpt_curr_id);
 358       _posted := (_posted + _r.cashrcptitem_amount);
 359
 360     END LOOP;
 361
 362     PERFORM postArCreditMemoApplication(_aropenid, _p.applydate);
 363
 364     -- If there is any left over go ahead and create an additional cashrcptitem record for it with the amount
 365     IF (round(_posted, 2) < round(_p.cashrcpt_amount, 2)) THEN
 366       INSERT INTO cashrcptitem
 367         ( cashrcptitem_cashrcpt_id, cashrcptitem_aropen_id, cashrcptitem_amount, cashrcptitem_applied )
 368       VALUES
 369         ( pCashrcptid, _aropenid, (_p.cashrcpt_amount - _posted), false );
 370     END IF;
 371   END IF;
 372
 373 --  Update the posted cashrcpt
 374   UPDATE cashrcpt SET cashrcpt_posted=TRUE,
 375                       cashrcpt_posteddate=CURRENT_DATE,
 376                       cashrcpt_postedby=getEffectiveXtUser()
 377   WHERE (cashrcpt_id=pCashrcptid);
 378
 379   RETURN 1;
 380
 381 END;
 382 $$ LANGUAGE 'plpgsql';