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

   1
   2 CREATE OR REPLACE FUNCTION importBankrecCleared(pBankrecid 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   _bankrecid INTEGER;
   7   _result INTEGER := 0;
   8   _cleared BOOLEAN;
   9   _doctype TEXT;
  10   _docid INTEGER := -1;
  11   _bankadjid INTEGER := -1;
  12   _debitbankadjtypeid INTEGER := -1;
  13   _creditbankadjtypeid INTEGER := -1;
  14   _b RECORD;
  15   _r RECORD;
  16
  17 BEGIN
  18   -- cache some information
  19   IF (pBankrecid = -1) THEN
  20     _bankrecid := fetchMetricValue('ImportBankRecId');
  21   ELSE
  22     _bankrecid := pBankrecid;
  23   END IF;
  24
  25   SELECT * INTO _b
  26   FROM bankrec JOIN bankaccnt ON (bankaccnt_id=bankrec_bankaccnt_id)
  27   WHERE (bankrec_id=_bankrecid);
  28   IF (NOT FOUND) THEN
  29     RAISE EXCEPTION 'bankrec not found';
  30   END IF;
  31   IF (_b.bankrec_posted) THEN
  32     RAISE EXCEPTION 'bankrec already posted';
  33   END IF;
  34
  35    _debitbankadjtypeid := fetchMetricValue('ImportBankRecDebitAdj');
  36   IF (_debitbankadjtypeid = -1) THEN
  37     RAISE EXCEPTION 'Metric ImportBankRecDebitAdj not defined [xtuple: reconcileBankAccount, -1]';
  38   END IF;
  39
  40    _creditbankadjtypeid := fetchMetricValue('ImportBankRecCreditAdj');
  41   IF (_creditbankadjtypeid = -1) THEN
  42     RAISE EXCEPTION 'Metric ImportBankRecCreditAdj not defined [xtuple: reconcileBankAccount, -2]';
  43   END IF;
  44
  45   -- loop thru bankrecimport and toggle cleared items
  46   FOR _r IN
  47   SELECT *,
  48          COALESCE(bankrecimport_debit_amount, 0.0) AS debit,
  49          COALESCE(bankrecimport_credit_amount, 0.0) AS credit
  50   FROM bankrecimport
  51 -- TODO how to handle multiple bank accounts
  52 --  WHERE (bankrecimport_?=_b.bankaccnt=?)
  53   LOOP
  54
  55     -- TODO how to handle duplicate document numbers
  56     -- TODO how to handle amount differences
  57     -- TODO add support for Project Accounting (sltrans)
  58
  59     IF ( (_r.debit > 0.0) AND (_r.credit > 0.0) ) THEN
  60       RAISE NOTICE 'Bankrecimport % cannot determine if debit or credit', _r.bankrecimport_reference;
  61       CONTINUE;
  62     END IF;
  63
  64     IF (_r.debit > 0.0) THEN
  65
  66       -- handle receipts
  67
  68       SELECT cashrcpt_id INTO _docid
  69       FROM cashrcpt
  70       WHERE (cashrcpt_docnumber=_r.bankrecimport_reference)
  71         AND (cashrcpt_posted)
  72         AND (NOT cashrcpt_void)
  73       -- TODO workaround for duplicates
  74       ORDER BY cashrcpt_id DESC
  75       LIMIT 1;
  76       IF (FOUND) THEN
  77         SELECT toggleBankrecCleared(_b.bankrec_id, 'GL', gltrans_id,
  78                                     cashrcpt_curr_rate, _r.debit,
  79                                     _r.bankrecimport_effdate) INTO _cleared
  80         FROM gltrans LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND
  81                                                      bankrecitem_source_id=gltrans_id)
  82         WHERE (gltrans_source='A/R')
  83           AND (gltrans_doctype='CR')
  84           AND (gltrans_misc_id=_docid)
  85           AND (NOT gltrans_rec)
  86           AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
  87           AND (NOT COALESCE(bankrecitem_cleared, FALSE));
  88       ELSE
  89
  90         -- create and toggle bank adjustment
  91         -- TODO define bank adjustment names
  92
  93         _bankadjid := -1;
  94         SELECT bankadj_id INTO _bankadjid
  95         FROM bankadj JOIN bankadjtype ON (bankadjtype_id=bankadj_bankadjtype_id)
  96         WHERE (bankadjtype_id=_debitbankadjtypeid)
  97           AND (bankadj_docnumber=_r.bankrecimport_reference)
  98           AND (bankadj_bankaccnt_id=_b.bankaccnt_id);
  99         IF (NOT FOUND) THEN
 100           INSERT INTO bankadj
 101             (bankadj_bankaccnt_id, bankadj_bankadjtype_id, bankadj_date,
 102              bankadj_docnumber, bankadj_amount, bankadj_notes,
 103              bankadj_curr_id)
 104           VALUES
 105             (_b.bankaccnt_id, _debitbankadjtypeid, _r.bankrecimport_effdate,
 106              _r.bankrecimport_reference, _r.debit, 'Import Bankrec Adjustment',
 107              _b.bankaccnt_curr_id)
 108           RETURNING bankadj_id INTO _bankadjid;
 109         END IF;
 110
 111         SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
 112                                     1.0, _r.debit,
 113                                     _r.bankrecimport_effdate) INTO _cleared
 114         FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
 115                                                      bankrecitem_source_id=bankadj_id)
 116         WHERE (bankadj_id=_bankadjid)
 117           AND (NOT COALESCE(bankrecitem_cleared, FALSE));
 118       END IF;
 119
 120       -- done with receipts
 121
 122     ELSE
 123
 124       -- handle checks
 125
 126       SELECT checkhead_id INTO _docid
 127       FROM checkhead
 128       WHERE (checkhead_number::TEXT=_r.bankrecimport_reference)
 129         AND (checkhead_posted)
 130         AND (NOT checkhead_void);
 131       IF (FOUND) THEN
 132         SELECT toggleBankrecCleared(_b.bankrec_id, 'GL', gltrans_id,
 133                                     checkhead_curr_rate, _r.credit,
 134                                     _r.bankrecimport_effdate) INTO _cleared
 135         FROM gltrans LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND
 136                                                      bankrecitem_source_id=gltrans_id)
 137         WHERE (gltrans_source='A/P')
 138           AND (gltrans_doctype='CK')
 139           AND (gltrans_misc_id=_docid)
 140           AND (NOT gltrans_rec)
 141           AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
 142           AND (NOT COALESCE(bankrecitem_cleared, FALSE));
 143       ELSE
 144
 145         -- create and toggle bank adjustment
 146         -- TODO define bank adjustment names
 147
 148         _bankadjid := -1;
 149         SELECT bankadj_id INTO _bankadjid
 150         FROM bankadj JOIN bankadjtype ON (bankadjtype_id=bankadj_bankadjtype_id)
 151         WHERE (bankadjtype_id=_creditbankadjtypeid)
 152           AND (bankadj_docnumber=_r.bankrecimport_reference);
 153         IF (NOT FOUND) THEN
 154           INSERT INTO bankadj
 155             (bankadj_bankaccnt_id, bankadj_bankadjtype_id, bankadj_date,
 156              bankadj_docnumber, bankadj_amount, bankadj_notes,
 157              bankadj_curr_id)
 158           VALUES
 159             (_b.bankaccnt_id, _creditbankadjtypeid, _r.bankrecimport_effdate,
 160              _r.bankrecimport_reference, _r.credit, 'Import Bankrec Adjustment',
 161              _b.bankaccnt_curr_id)
 162           RETURNING bankadj_id INTO _bankadjid;
 163         END IF;
 164
 165         SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
 166                                     1.0, _r.credit,
 167                                     _r.bankrecimport_effdate) INTO _cleared
 168         FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
 169                                                      bankrecitem_source_id=_bankadjid)
 170         WHERE (NOT COALESCE(bankrecitem_cleared, FALSE));
 171       END IF;
 172
 173       -- done with checks
 174
 175     END IF;
 176
 177   END LOOP;
 178
 179   RETURN _result;
 180 END;
 181 $$ LANGUAGE 'plpgsql';
 182