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

   1
   2 CREATE OR REPLACE FUNCTION postBankReconciliation(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   pBankrecid ALIAS FOR $1;
   7   _accntid INTEGER;
   8   _sequence INTEGER;
   9   _gltransid INTEGER;
  10   _r RECORD;
  11   _tax RECORD;
  12
  13 BEGIN
  14
  15 -- Check the accnt information to make sure it is valid
  16   SELECT accnt_id INTO _accntid
  17     FROM bankrec, bankaccnt, accnt
  18    WHERE ( (bankaccnt_accnt_id=accnt_id)
  19      AND   (bankrec_bankaccnt_id=bankaccnt_id)
  20      AND   (bankrec_id=pBankrecid) );
  21   IF ( NOT FOUND ) THEN
  22     RETURN -1;
  23   END IF;
  24
  25 -- Delete any bankrecitem records that are not marked as cleared for cleanliness
  26   DELETE FROM bankrecitem
  27    WHERE ( (NOT bankrecitem_cleared)
  28      AND   (bankrecitem_bankrec_id=pBankrecid) );
  29
  30 -- Post any bankadj items that were marked as cleared and convert the bankrecitem
  31   FOR _r IN SELECT bankrecitem_id, bankrecitem_source_id
  32               FROM bankrecitem, bankadj
  33              WHERE ( (bankrecitem_source = 'AD')
  34                AND   (bankrecitem_source_id=bankadj_id)
  35                AND   (bankrecitem_cleared)
  36                AND   (NOT bankadj_posted)
  37                AND   (bankrecitem_bankrec_id=pBankrecid) ) LOOP
  38
  39     SELECT postBankAdjustment(_r.bankrecitem_source_id) INTO _sequence;
  40
  41     IF (_sequence < 0) THEN
  42       RETURN -10;
  43     END IF;
  44
  45     SELECT gltrans_id INTO _gltransid
  46       FROM gltrans
  47      WHERE ( (gltrans_sequence=_sequence)
  48        AND   (gltrans_accnt_id=_accntid) );
  49     IF ( NOT FOUND ) THEN
  50       RETURN -11;
  51     END IF;
  52
  53     UPDATE bankrecitem
  54        SET bankrecitem_source = 'GL',
  55            bankrecitem_source_id=_gltransid
  56      WHERE (bankrecitem_id=_r.bankrecitem_id);
  57
  58   END LOOP;
  59
  60   IF (fetchMetricBool('CashBasedTax')) THEN
  61     -- Cash based tax distributions
  62     -- GL Transactions
  63     FOR _r IN SELECT *
  64               FROM bankrecitem
  65              WHERE ( (bankrecitem_source = 'GL')
  66                AND   (bankrecitem_cleared)
  67                AND   (bankrecitem_bankrec_id=pBankrecid) ) LOOP
  68       -- first, debit the tax liability clearing account
  69       -- and credit the tax liability distribution account
  70       -- for each tax code
  71       FOR _tax IN SELECT docnumber, custname, gltrans_date,
  72                          tax_sales_accnt_id, tax_dist_accnt_id,
  73                          ROUND(currToBase(currid, ROUND(SUM(taxhist_tax),2), taxhist_docdate) * percentpaid, 2) AS taxbasevalue
  74                   FROM (SELECT invchead_invcnumber AS docnumber, invchead_billto_name AS custname,
  75                                invchead_curr_id AS currid, gltrans_date,
  76                                (cashrcptitem_amount / calcInvoiceAmt(invchead_id)) AS percentpaid,
  77                                tax_sales_accnt_id, tax_dist_accnt_id,
  78                                taxhist_tax, taxhist_docdate
  79                         FROM gltrans JOIN cashrcpt  ON ((gltrans_source='A/R')
  80                                                     AND (gltrans_doctype='CR')
  81                                                     AND (gltrans_misc_id=cashrcpt_id))
  82                                      JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
  83                                      JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
  84                                      JOIN invchead ON (invchead_invcnumber=aropen_docnumber)
  85                                      JOIN cohist ON (cohist_invcnumber=invchead_invcnumber AND cohist_doctype='I')
  86                                      JOIN cohisttax ON (taxhist_parent_id=cohist_id)
  87                                      JOIN tax ON (tax_id=taxhist_tax_id)
  88                         WHERE (gltrans_id=_r.bankrecitem_source_id)) AS data
  89                   GROUP BY docnumber, custname, currid, gltrans_date, percentpaid,
  90                            tax_sales_accnt_id, tax_dist_accnt_id, taxhist_docdate
  91       LOOP
  92         RAISE NOTICE 'Posting GL Series for Document %', _tax.docnumber;
  93         PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR', _tax.docnumber,
  94                                     _tax.tax_dist_accnt_id,
  95                                     _tax.taxbasevalue,
  96                                     COALESCE(_r.bankrecitem_effdate, _tax.gltrans_date), _tax.custname );
  97         PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR', _tax.docnumber,
  98                                     _tax.tax_sales_accnt_id,
  99                                     (_tax.taxbasevalue * -1.0),
 100                                     COALESCE(_r.bankrecitem_effdate, _tax.gltrans_date), _tax.custname );
 101       END LOOP;
 102
 103       -- second, create a taxpay row for each taxhist
 104       FOR _tax IN SELECT *,
 105                          ROUND(taxhist_tax * percentpaid, 2) AS taxpaid
 106                   FROM (SELECT *,
 107                                (cashrcptitem_amount / calcInvoiceAmt(invchead_id)) AS percentpaid
 108                           FROM gltrans JOIN cashrcpt  ON ((gltrans_source='A/R')
 109                                                       AND (gltrans_doctype='CR')
 110                                                       AND (gltrans_misc_id=cashrcpt_id))
 111                                        JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
 112                                        JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
 113                                        JOIN invchead ON (invchead_invcnumber=aropen_docnumber)
 114                                        JOIN cohist ON (cohist_invcnumber=invchead_invcnumber AND cohist_doctype='I')
 115                                        JOIN cohisttax ON (taxhist_parent_id=cohist_id)
 116                           WHERE (gltrans_id=_r.bankrecitem_source_id)) AS data
 117       LOOP
 118         INSERT INTO taxpay
 119         ( taxpay_taxhist_id, taxpay_apply_id, taxpay_distdate, taxpay_tax )
 120         VALUES
 121         ( _tax.taxhist_id, _tax.aropen_id, COALESCE(_r.bankrecitem_effdate, _tax.gltrans_date), _tax.taxpaid );
 122       END LOOP;
 123
 124     END LOOP;
 125
 126     PERFORM postGLSeries(_sequence, fetchJournalNumber('GL-MISC'));
 127
 128   END IF;
 129
 130
 131 -- Mark all the gltrans items that have been cleared as reconciled.
 132   UPDATE gltrans
 133      SET gltrans_rec = TRUE
 134    WHERE ( (gltrans_id IN (SELECT bankrecitem_source_id
 135                              FROM bankrecitem
 136                             WHERE ((bankrecitem_source = 'GL')
 137                               AND  (bankrecitem_cleared)
 138                               AND  (bankrecitem_bankrec_id=pBankrecid) ) ) )
 139      AND   (gltrans_accnt_id=_accntid) ) ;
 140
 141 -- Mark all the sltrans items that have been cleared as reconciled.
 142   UPDATE sltrans
 143      SET sltrans_rec = TRUE
 144    WHERE ( (sltrans_id IN (SELECT bankrecitem_source_id
 145                              FROM bankrecitem
 146                             WHERE ((bankrecitem_source = 'SL')
 147                               AND  (bankrecitem_cleared)
 148                               AND  (bankrecitem_bankrec_id=pBankrecid) ) ) )
 149      AND   (sltrans_accnt_id=_accntid) ) ;
 150
 151 -- Mark the bankrec record as posted
 152   UPDATE bankrec SET
 153     bankrec_posted = TRUE,
 154     bankrec_postdate = now()
 155    WHERE (bankrec_id=pBankrecid);
 156
 157   RETURN pBankrecid;
 158 END;
 159 $$ LANGUAGE 'plpgsql';
 160