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

   1 CREATE OR REPLACE FUNCTION postCCcashReceipt(pCCpay   INTEGER,
   2                                              pdocid   INTEGER,
   3                                              pdoctype TEXT    DEFAULT NULL,
   4                                              pamount  NUMERIC DEFAULT NULL) RETURNS INTEGER AS
   5 $$
   6 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   7 -- See www.xtuple.com/CPAL for the full text of the software license.
   8 DECLARE
   9   _aropenid     INTEGER;
  10   _bankaccnt_id INTEGER;
  11   _c            RECORD;
  12   _ccOrderDesc  TEXT;
  13   _journal      INTEGER;
  14   _realaccnt    INTEGER;
  15   _return       INTEGER := 0;
  16
  17 BEGIN
  18   SELECT * INTO _c
  19      FROM ccpay
  20      JOIN custinfo ON ccpay_cust_id = cust_id
  21      WHERE (ccpay_id = pCCpay);
  22
  23   IF (NOT FOUND) THEN
  24     RAISE EXCEPTION 'Cannot find the Credit Card transaction information [xtuple: postCCcashReceipt, -11, %]',
  25                     pCCpay;
  26   END IF;
  27
  28   IF (pamount IS NOT NULL) THEN
  29     _c.ccpay_amount = pamount;
  30   END IF;
  31
  32   SELECT bankaccnt_id, bankaccnt_accnt_id INTO _bankaccnt_id, _realaccnt
  33   FROM ccbank
  34   JOIN bankaccnt ON (ccbank_bankaccnt_id=bankaccnt_id)
  35   WHERE (ccbank_ccard_type=_c.ccpay_card_type);
  36
  37   IF (_bankaccnt_id IS NULL) THEN
  38     RAISE EXCEPTION 'Cannot find the default Bank Account for this Credit Card [xtuple: postCCcredit, -1, %]',
  39                     _c.ccpay_card_type;
  40   END IF;
  41
  42   _ccOrderDesc := (_c.ccpay_card_type || '-' || _c.ccpay_order_number::TEXT ||
  43                   '-' || _c.ccpay_order_number_seq::TEXT);
  44
  45   _journal := fetchJournalNumber('C/R');
  46
  47   IF (pdoctype = 'cashrcpt') THEN
  48     IF (COALESCE(pdocid, -1) < 0) THEN
  49       INSERT INTO cashrcpt (
  50         cashrcpt_cust_id,   cashrcpt_amount,     cashrcpt_curr_id,
  51         cashrcpt_fundstype, cashrcpt_docnumber,  cashrcpt_notes,
  52         cashrcpt_distdate,  cashrcpt_bankaccnt_id,
  53         cashrcpt_usecustdeposit
  54       ) VALUES (
  55         _c.ccpay_cust_id,   _c.ccpay_amount,     _c.ccpay_curr_id,
  56         _c.ccpay_card_type,      _c.ccpay_r_ordernum, _ccOrderDesc,
  57         CURRENT_DATE,       _bankaccnt_id,
  58         fetchMetricBool('EnableCustomerDeposits'))
  59       RETURNING cashrcpt_id INTO _return;
  60     ELSE
  61       UPDATE cashrcpt
  62       SET cashrcpt_cust_id=_c.ccpay_cust_id,
  63           cashrcpt_amount=_c.ccpay_amount,
  64           cashrcpt_curr_id=_c.ccpay_curr_id,
  65           cashrcpt_fundstype=_c.ccpay_card_type,
  66           cashrcpt_docnumber=_c.ccpay_r_ordernum,
  67           cashrcpt_notes=_ccOrderDesc,
  68           cashrcpt_distdate=CURRENT_DATE,
  69           cashrcpt_bankaccnt_id=_bankaccnt_id
  70       WHERE (cashrcpt_id=pdocid);
  71       _return := pdocid;
  72     END IF;
  73
  74   ELSIF (pdoctype = 'cohead') THEN
  75     SELECT createARCreditMemo(NULL,               _c.ccpay_cust_id,
  76                              fetchArMemoNumber(), cohead_number,
  77                              CURRENT_DATE,        _c.ccpay_amount,
  78                              'Unapplied from ' || _ccOrderDesc,
  79                              NULL,                NULL, NULL,
  80                              CURRENT_DATE,        NULL,
  81                              cohead_salesrep_id,  NULL,
  82                              _journal,            _c.ccpay_curr_id,
  83                              NULL,                pCCpay) INTO _aropenid
  84       FROM cohead
  85      WHERE cohead_id = pdocid;
  86     IF (COALESCE(_aropenid, -1) < 0) THEN       -- coalesce handles not-found case
  87       RAISE EXCEPTION '[xtuple: createARCreditMemo, %]', _aropenid;
  88     END IF;
  89
  90     INSERT INTO payaropen (payaropen_ccpay_id, payaropen_aropen_id,
  91                            payaropen_amount,   payaropen_curr_id)
  92                   VALUES  (pccpay,             _aropenid,
  93                            _c.ccpay_amount,    _c.ccpay_curr_id);
  94     INSERT INTO aropenalloc (aropenalloc_aropen_id, aropenalloc_doctype, aropenalloc_doc_id,
  95                              aropenalloc_amount,    aropenalloc_curr_id)
  96                      VALUES (_aropenid, 'S',          pdocid,
  97                              _c.ccpay_amount,    _c.ccpay_curr_id);
  98     _return := _aropenid;
  99   END IF;
 100
 101   PERFORM insertGLTransaction(_journal, 'A/R', 'CR', _ccOrderDesc,
 102                               ('Cash Receipt from Credit Card ' || _c.cust_name),
 103                               findPrepaidAccount(_c.ccpay_cust_id),
 104                               _realaccnt,
 105                               NULL,
 106                               ROUND(currToBase(_c.ccpay_curr_id,
 107                                                _c.ccpay_amount,
 108                                                _c.ccpay_transaction_datetime::DATE),2),
 109                               CURRENT_DATE);
 110
 111   RETURN _return;
 112 END;
 113 $$ LANGUAGE 'plpgsql';