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

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