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;
  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           AND (bankadj_bankaccnt_id=_b.bankaccnt_id);
  88         IF (NOT FOUND) THEN
  89           INSERT INTO bankadj
  90             (bankadj_bankaccnt_id, bankadj_bankadjtype_id, bankadj_date,
  91              bankadj_docnumber, bankadj_amount, bankadj_notes,
  92              bankadj_curr_id)
  93           VALUES
  94             (_b.bankaccnt_id, fetchMetricValue('ImportBankRecDebitAdj'), _r.bankrecimport_effdate,
  95              _r.bankrecimport_reference, _r.debit, 'Import Bankrec Adjustment',
  96              _b.bankaccnt_curr_id)
  97           RETURNING bankadj_id INTO _bankadjid;
  98         END IF;
  99
 100         IF (_bankadjid > 0) THEN
 101           SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
 102                                       1.0, _r.debit,
 103                                       _r.bankrecimport_effdate) INTO _cleared
 104           FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
 105                                                        bankrecitem_source_id=bankadj_id)
 106           WHERE (bankadj_id=_bankadjid)
 107             AND (NOT COALESCE(bankrecitem_cleared, FALSE));
 108         ELSE
 109           RAISE EXCEPTION 'bankadjtype not found';
 110         END IF;
 111       END IF;
 112
 113       -- done with receipts
 114
 115     ELSE
 116
 117       -- handle checks
 118
 119       SELECT checkhead_id INTO _docid
 120       FROM checkhead
 121       WHERE (checkhead_number::TEXT=_r.bankrecimport_reference)
 122         AND (checkhead_posted)
 123         AND (NOT checkhead_void);
 124       IF (FOUND) THEN
 125         SELECT toggleBankrecCleared(_b.bankrec_id, 'GL', gltrans_id,
 126                                     checkhead_curr_rate, _r.credit,
 127                                     _r.bankrecimport_effdate) INTO _cleared
 128         FROM checkhead JOIN gltrans ON ((gltrans_source='A/P')
 129                                     AND (gltrans_doctype='CK')
 130                                     AND (NOT gltrans_rec)
 131                                     AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
 132                                     AND (gltrans_misc_id=checkhead_id))
 133                        LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND
 134                                                        bankrecitem_source_id=gltrans_id)
 135         WHERE (checkhead_id=_docid)
 136           AND (NOT COALESCE(bankrecitem_cleared, FALSE));
 137       ELSE
 138
 139         -- create and toggle bank adjustment
 140         -- TODO define bank adjustment names
 141
 142         _bankadjid := -1;
 143         SELECT bankadj_id INTO _bankadjid
 144         FROM bankadj JOIN bankadjtype ON (bankadjtype_id=bankadj_bankadjtype_id)
 145         WHERE (bankadjtype_id=fetchMetricValue('ImportBankRecCreditAdj'))
 146           AND (bankadj_docnumber=_r.bankrecimport_reference);
 147         IF (NOT FOUND) THEN
 148           INSERT INTO bankadj
 149             (bankadj_bankaccnt_id, bankadj_bankadjtype_id, bankadj_date, bankadj_docnumber,
 150              bankadj_amount, bankadj_notes, bankadj_curr_id)
 151           SELECT
 152             _b.bankaccnt_id, bankadjtype_id, _r.bankrecimport_effdate, _r.bankrecimport_reference,
 153             _r.credit, 'Import Bankrec Adjustment', _b.bankaccnt_curr_id
 154           FROM bankadjtype
 155           WHERE (bankadjtype_id=fetchMetricValue('ImportBankRecCreditAdj'))
 156           RETURNING bankadj_id INTO _bankadjid;
 157         END IF;
 158
 159         IF (_bankadjid > 0) THEN
 160           SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
 161                                       1.0, _r.credit,
 162                                       _r.bankrecimport_effdate) INTO _cleared
 163           FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
 164                                                        bankrecitem_source_id=_bankadjid)
 165           WHERE (NOT COALESCE(bankrecitem_cleared, FALSE));
 166         ELSE
 167           RAISE EXCEPTION 'bankadjtype not found';
 168         END IF;
 169       END IF;
 170
 171       -- done with checks
 172
 173     END IF;
 174
 175   END LOOP;
 176
 177   RETURN _result;
 178 END;
 179 $$ LANGUAGE 'plpgsql';
 180