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   _cleared BOOLEAN;
   7   _b RECORD;
   8   _r RECORD;
   9
  10 BEGIN
  11   -- cache some information
  12   SELECT * INTO _b
  13   FROM bankrec JOIN bankaccnt ON (bankaccnt_id=bankrec_bankaccnt_id)
  14   WHERE (bankrec_id=pBankrecid);
  15   IF (NOT FOUND) THEN
  16     RAISE EXCEPTION 'bankrec not found';
  17   END IF;
  18   IF (_b.bankrec_posted) THEN
  19     RAISE EXCEPTION 'bankrec already posted';
  20   END IF;
  21
  22   -- loop thru bankrecimport and toggle cleared items
  23   FOR _r IN
  24   SELECT *,
  25          COALESCE(bankrecimport_debit_amount, 0.0) AS debit,
  26          COALESCE(bankrecimport_credit_amount, 0.0) AS credit
  27   FROM bankrecimport
  28 --  WHERE (bankrecimport_?=_b.bankaccnt=?)
  29   LOOP
  30
  31     IF ( (_r.debit > 0.0) AND (_r.credit > 0.0) ) THEN
  32       RAISE EXCEPTION 'cannot determine if debit or credit';
  33     END IF;
  34
  35     IF (_r.debit > 0.0) THEN
  36       -- check receipts
  37       SELECT gltrans_id AS id, 1 AS altid,
  38              jrnluse_use AS use, gltrans_journalnumber AS jrnlnum,
  39              COALESCE(date(jrnluse_date), gltrans_date) AS f_jrnldate,
  40              COALESCE(bankrecitem_cleared, FALSE) AS cleared,
  41              COALESCE(bankrecitem_effdate, gltrans_date) AS f_date,
  42              gltrans_doctype AS doc_type,
  43              gltrans_docnumber AS docnumber,
  44              gltrans_notes AS notes,
  45              currConcat(COALESCE(cashrcpt_curr_id, baseCurrId())) AS doc_curr,
  46              COALESCE(bankrecitem_curr_rate, cashrcpt_curr_rate, currRate(bankaccnt_curr_id, gltrans_date)) AS doc_exchrate,
  47              (gltrans_amount * -1.0) AS base_amount,
  48              CASE WHEN (bankaccnt_curr_id=cashrcpt_curr_id) THEN
  49                COALESCE( bankrecitem_amount,
  50                          cashrcpt_amount,
  51                          (currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) * -1.0) )
  52                   ELSE
  53                COALESCE( bankrecitem_amount,
  54                          (currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) * -1.0) )
  55              END AS amount,
  56              COALESCE(date(jrnluse_date), gltrans_date) AS jrnldate,
  57              gltrans_date AS sortdate
  58         FROM bankaccnt
  59         JOIN gltrans ON (bankaccnt_accnt_id=gltrans_accnt_id)
  60         LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='GL')
  61                                     AND (bankrecitem_source_id=gltrans_id)
  62                                     AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
  63                                     AND (bankrecitem_cleared))
  64         LEFT OUTER JOIN jrnluse ON (jrnluse_number=gltrans_journalnumber AND jrnluse_use='C/R')
  65         LEFT OUTER JOIN cashrcpt  ON ((gltrans_source='A/R')
  66                                   AND (gltrans_doctype='CR')
  67                                   AND (gltrans_misc_id=cashrcpt_id))
  68         LEFT OUTER JOIN checkhead ON ((gltrans_doctype='CK')
  69                                   AND (gltrans_misc_id=checkhead_id))
  70        WHERE ((NOT gltrans_deleted)
  71          AND (NOT gltrans_rec)
  72          AND (NOT COALESCE(checkhead_void, false))
  73          AND (gltrans_amount < 0)
  74          AND (gltrans_doctype != 'JP')
  75          AND (bankaccnt_id=<? value("bankaccntid") ?>)
  76        )
  77
  78
  79
  80
  81
  82
  83  UNION ALL
  84 SELECT sltrans_id AS id, 2 AS altid,
  85        jrnluse_use AS use, sltrans_journalnumber AS jrnlnum,
  86        COALESCE(date(jrnluse_date), sltrans_date) AS f_jrnldate,
  87        COALESCE(bankrecitem_cleared, FALSE) AS cleared,
  88        COALESCE(bankrecitem_effdate, sltrans_date) AS f_date,
  89        sltrans_doctype AS doc_type,
  90        sltrans_docnumber AS docnumber,
  91        sltrans_notes AS notes,
  92        currConcat(COALESCE(cashrcpt_curr_id, baseCurrId())) AS doc_curr,
  93        COALESCE(bankrecitem_curr_rate, cashrcpt_curr_rate, currRate(bankaccnt_curr_id, sltrans_date)) AS doc_exchrate,
  94        (sltrans_amount * -1.0) AS base_amount,
  95        CASE WHEN (bankaccnt_curr_id=cashrcpt_curr_id) THEN
  96          COALESCE( bankrecitem_amount,
  97                    cashrcpt_amount,
  98                    (currToLocal(bankaccnt_curr_id, sltrans_amount, sltrans_date) * -1.0) )
  99             ELSE
 100          COALESCE( bankrecitem_amount,
 101                    (currToLocal(bankaccnt_curr_id, sltrans_amount, sltrans_date) * -1.0) )
 102        END AS amount,
 103        COALESCE(date(jrnluse_date), sltrans_date) AS jrnldate,
 104        sltrans_date AS sortdate,
 105        'uomratio' AS doc_exchrate_xtnumericrole,
 106        'curr' AS base_amount_xtnumericrole,
 107        'curr' AS amount_xtnumericrole
 108   FROM bankaccnt
 109   JOIN sltrans ON (bankaccnt_accnt_id=sltrans_accnt_id)
 110   LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='SL')
 111                               AND (bankrecitem_source_id=sltrans_id)
 112                               AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
 113                               AND (bankrecitem_cleared))
 114   LEFT OUTER JOIN jrnluse ON (jrnluse_number=sltrans_journalnumber AND jrnluse_use='C/R')
 115   LEFT OUTER JOIN cashrcpt  ON ((sltrans_source='A/R')
 116                             AND (sltrans_doctype='CR')
 117                             AND (sltrans_misc_id=cashrcpt_id))
 118   LEFT OUTER JOIN checkhead ON ((sltrans_doctype='CK')
 119                             AND (sltrans_misc_id=checkhead_id))
 120  WHERE ((NOT sltrans_rec)
 121    AND (NOT COALESCE(checkhead_void, false))
 122    AND (sltrans_amount < 0)
 123    AND (bankaccnt_id=<? value("bankaccntid") ?>)
 124 <? if exists("source") ?>
 125    AND ('SL' = <? value("source") ?>)
 126 <? endif ?>
 127 <? if exists("sourceid") ?>
 128    AND (sltrans_id = <? value("sourceid") ?>)
 129 <? endif ?>
 130  )
 131  UNION ALL
 132 SELECT bankadj_id AS id, 3 AS altid,
 133        '' AS use, NULL AS jrnlnum, bankadj_date AS f_jrnldate,
 134        COALESCE(bankrecitem_cleared, FALSE) AS cleared,
 135        COALESCE(bankrecitem_effdate, bankadj_date) AS f_date,
 136        'ADJ' AS doc_type,
 137        bankadj_docnumber AS docnumber,
 138        bankadjtype_name AS notes,
 139        currConcat(bankadj_curr_id) AS doc_curr,
 140        1.0 AS doc_exchrate,
 141        CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1.0) ELSE bankadj_amount END AS base_amount,
 142        CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1.0) ELSE bankadj_amount END AS amount,
 143        bankadj_date AS jrnldate,
 144        bankadj_date AS sortdate,
 145        'uomratio' AS doc_exchrate_xtnumericrole,
 146        'curr' AS base_amount_xtnumericrole,
 147        'curr' AS amount_xtnumericrole
 148   FROM (bankadjtype CROSS JOIN bankadj)
 149                LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='AD')
 150                                            AND (bankrecitem_source_id=bankadj_id)
 151                                            AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>))
 152  WHERE ( (((bankadjtype_iscredit=false) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=true) AND (bankadj_amount < 0)))
 153    AND (bankadj_bankadjtype_id=bankadjtype_id)
 154    AND (NOT bankadj_posted)
 155    AND (bankadj_bankaccnt_id=<? value("bankaccntid") ?>)
 156 <? if exists("source") ?>
 157    AND ('AD' = <? value("source") ?>)
 158 <? endif ?>
 159 <? if exists("sourceid") ?>
 160    AND (bankadj_id = <? value("sourceid") ?>)
 161 <? endif ?>
 162  )
 163 ORDER BY jrnldate, jrnlnum, sortdate;
 164
 165
 166
 167
 168   SELECT bankrecitem_id, bankrecitem_cleared INTO _r
 169     FROM bankrecitem
 170    WHERE ( (bankrecitem_bankrec_id=pBankrecid)
 171      AND   (bankrecitem_source=pSource)
 172      AND   (bankrecitem_source_id=pSourceid) );
 173   IF ( NOT FOUND ) THEN
 174     _cleared := TRUE;
 175     INSERT INTO bankrecitem
 176     (bankrecitem_bankrec_id, bankrecitem_source,
 177      bankrecitem_source_id, bankrecitem_cleared,
 178      bankrecitem_curr_rate, bankrecitem_amount,
 179      bankrecitem_effdate)
 180     VALUES
 181     (pBankrecid, pSource,
 182      pSourceid, _cleared,
 183      pCurrrate, pAmount,
 184      pDate);
 185   ELSE
 186     _cleared := FALSE;
 187     DELETE FROM bankrecitem
 188     WHERE bankrecitem_id = _r.bankrecitem_id;
 189   END IF;
 190
 191   RETURN _cleared;
 192 END;
 193 $$ LANGUAGE 'plpgsql';
 194