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