From 7324e3e2b80bd9dc3588e9e5254c630b1931a5a0 Mon Sep 17 00:00:00 2001 From: garyhgohoos Date: Mon, 12 May 2014 14:11:09 -0400 Subject: [PATCH] initial commit --- .../functions/postbankreconciliation.sql | 72 +++++++++++++++++++ .../public/functions/togglebankreccleared.sql | 23 ++++-- .../public/tables/metasql/bankrec-checks.mql | 33 +++++++-- .../tables/metasql/bankrec-receipts.mql | 33 +++++++-- 4 files changed, 145 insertions(+), 16 deletions(-) diff --git a/foundation-database/public/functions/postbankreconciliation.sql b/foundation-database/public/functions/postbankreconciliation.sql index da8025ad8..ce4f9dfb5 100644 --- a/foundation-database/public/functions/postbankreconciliation.sql +++ b/foundation-database/public/functions/postbankreconciliation.sql @@ -8,6 +8,7 @@ DECLARE _sequence INTEGER; _gltransid INTEGER; _r RECORD; + _tax RECORD; BEGIN @@ -56,6 +57,77 @@ BEGIN END LOOP; + IF (fetchMetricBool('CashBasedTax')) THEN + -- Cash based tax distributions + -- GL Transactions + FOR _r IN SELECT * + FROM bankrecitem + WHERE ( (bankrecitem_source = 'GL') + AND (bankrecitem_cleared) + AND (bankrecitem_bankrec_id=pBankrecid) ) LOOP + -- first, debit the tax liability clearing account + -- and credit the tax liability distribution account + -- for each tax code + FOR _tax IN SELECT docnumber, custname, gltrans_date, + tax_sales_accnt_id, tax_dist_accnt_id, + ROUND(currToBase(currid, ROUND(SUM(taxhist_tax),2), taxhist_docdate) * percentpaid, 2) AS taxbasevalue + FROM (SELECT invchead_invcnumber AS docnumber, invchead_billto_name AS custname, + invchead_curr_id AS currid, gltrans_date, + (cashrcptitem_amount / calcInvoiceAmt(invchead_id)) AS percentpaid, + tax_sales_accnt_id, tax_dist_accnt_id, + taxhist_tax, taxhist_docdate + FROM gltrans JOIN cashrcpt ON ((gltrans_source='A/R') + AND (gltrans_doctype='CR') + AND (gltrans_misc_id=cashrcpt_id)) + JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id) + JOIN aropen ON (aropen_id=cashrcptitem_aropen_id) + JOIN invchead ON (invchead_invcnumber=aropen_docnumber) + JOIN cohist ON (cohist_invcnumber=invchead_invcnumber AND cohist_doctype='I') + JOIN cohisttax ON (taxhist_parent_id=cohist_id) + JOIN tax ON (tax_id=taxhist_tax_id) + WHERE (gltrans_id=_r.bankrecitem_source_id)) AS data + GROUP BY docnumber, custname, currid, gltrans_date, percentpaid, + tax_sales_accnt_id, tax_dist_accnt_id, taxhist_docdate + LOOP + RAISE NOTICE 'Posting GL Series for Document %', _tax.docnumber; + PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR', _tax.docnumber, + _tax.tax_dist_accnt_id, + _tax.taxbasevalue, + COALESCE(_r.bankrecitem_effdate, _tax.gltrans_date), _tax.custname ); + PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR', _tax.docnumber, + _tax.tax_sales_accnt_id, + (_tax.taxbasevalue * -1.0), + COALESCE(_r.bankrecitem_effdate, _tax.gltrans_date), _tax.custname ); + END LOOP; + + -- second, create a taxpay row for each taxhist + FOR _tax IN SELECT *, + ROUND(taxhist_tax * percentpaid, 2) AS taxpaid + FROM (SELECT *, + (cashrcptitem_amount / calcInvoiceAmt(invchead_id)) AS percentpaid + FROM gltrans JOIN cashrcpt ON ((gltrans_source='A/R') + AND (gltrans_doctype='CR') + AND (gltrans_misc_id=cashrcpt_id)) + JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id) + JOIN aropen ON (aropen_id=cashrcptitem_aropen_id) + JOIN invchead ON (invchead_invcnumber=aropen_docnumber) + JOIN cohist ON (cohist_invcnumber=invchead_invcnumber AND cohist_doctype='I') + JOIN cohisttax ON (taxhist_parent_id=cohist_id) + WHERE (gltrans_id=_r.bankrecitem_source_id)) AS data + LOOP + INSERT INTO taxpay + ( taxpay_taxhist_id, taxpay_apply_id, taxpay_distdate, taxpay_tax ) + VALUES + ( _tax.taxhist_id, _tax.aropen_id, COALESCE(_r.bankrecitem_effdate, _tax.gltrans_date), _tax.taxpaid ); + END LOOP; + + END LOOP; + + PERFORM postGLSeries(_sequence, fetchJournalNumber('GL-MISC')); + + END IF; + + -- Mark all the gltrans items that have been cleared as reconciled. UPDATE gltrans SET gltrans_rec = TRUE diff --git a/foundation-database/public/functions/togglebankreccleared.sql b/foundation-database/public/functions/togglebankreccleared.sql index 8627ad5a6..c591b0276 100644 --- a/foundation-database/public/functions/togglebankreccleared.sql +++ b/foundation-database/public/functions/togglebankreccleared.sql @@ -1,5 +1,18 @@ -CREATE OR REPLACE FUNCTION toggleBankrecCleared(INTEGER, TEXT, INTEGER, NUMERIC, NUMERIC) RETURNS BOOLEAN AS $$ +CREATE OR REPLACE FUNCTION toggleBankrecCleared(INTEGER, TEXT, INTEGER) RETURNS BOOLEAN AS $$ +-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. +-- See www.xtuple.com/CPAL for the full text of the software license. +DECLARE + pBankrecid ALIAS FOR $1; + pSource ALIAS FOR $2; + pSourceid ALIAS FOR $3; + +BEGIN + RETURN toggleBankrecCleared(pBankrecid, pSource, pSourceid, NULL, NULL, NULL); +END; +$$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION toggleBankrecCleared(INTEGER, TEXT, INTEGER, NUMERIC, NUMERIC, DATE) RETURNS BOOLEAN AS $$ -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. -- See www.xtuple.com/CPAL for the full text of the software license. DECLARE @@ -8,11 +21,11 @@ DECLARE pSourceid ALIAS FOR $3; pCurrrate ALIAS FOR $4; pAmount ALIAS FOR $5; + pDate ALIAS FOR $6; _cleared BOOLEAN; _r RECORD; BEGIN - SELECT bankrecitem_id, bankrecitem_cleared INTO _r FROM bankrecitem WHERE ( (bankrecitem_bankrec_id=pBankrecid) @@ -23,11 +36,13 @@ BEGIN INSERT INTO bankrecitem (bankrecitem_bankrec_id, bankrecitem_source, bankrecitem_source_id, bankrecitem_cleared, - bankrecitem_curr_rate, bankrecitem_amount) + bankrecitem_curr_rate, bankrecitem_amount, + bankrecitem_effdate) VALUES (pBankrecid, pSource, pSourceid, _cleared, - pCurrrate, pAmount); + pCurrrate, pAmount, + pDate); ELSE _cleared := FALSE; DELETE FROM bankrecitem diff --git a/foundation-database/public/tables/metasql/bankrec-checks.mql b/foundation-database/public/tables/metasql/bankrec-checks.mql index 0973a39c0..cb1e8121c 100644 --- a/foundation-database/public/tables/metasql/bankrec-checks.mql +++ b/foundation-database/public/tables/metasql/bankrec-checks.mql @@ -45,7 +45,7 @@ SELECT COALESCE(SUM(amount),0.0) AS cleared_amount SELECT gltrans_id AS id, 1 AS altid, COALESCE(bankrecitem_cleared, FALSE) AS cleared, - gltrans_date AS transdate, + COALESCE(bankrecitem_effdate, gltrans_date) AS transdate, gltrans_doctype AS doc_type, gltrans_docnumber AS doc_number, gltrans_notes AS notes, @@ -77,11 +77,18 @@ SELECT gltrans_id AS id, 1 AS altid, AND (NOT COALESCE(checkhead_void, false)) AND (gltrans_amount > 0) AND (gltrans_doctype != 'JP') - AND (bankaccnt_id=) ) + AND (bankaccnt_id=) + + AND ('GL' = ) + + + AND (gltrans_id = ) + + ) UNION ALL SELECT sltrans_id AS id, 2 AS altid, COALESCE(bankrecitem_cleared, FALSE) AS cleared, - sltrans_date AS transdate, + COALESCE(bankrecitem_effdate, sltrans_date) AS transdate, sltrans_doctype AS doc_type, sltrans_docnumber AS doc_number, sltrans_notes AS notes, @@ -111,11 +118,18 @@ SELECT sltrans_id AS id, 2 AS altid, AND (NOT sltrans_rec) AND (NOT COALESCE(checkhead_void, false)) AND (sltrans_amount > 0) - AND (bankaccnt_id=) ) + AND (bankaccnt_id=) + + AND ('SL' = ) + + + AND (sltrans_id = ) + + ) UNION ALL SELECT bankadj_id AS id, 3 AS altid, COALESCE(bankrecitem_cleared, FALSE) AS cleared, - bankadj_date AS transdate, + COALESCE(bankrecitem_effdate, bankadj_date) AS transdate, 'ADJ' AS doc_type, bankadj_docnumber AS doc_number, bankadjtype_name AS notes, @@ -135,6 +149,13 @@ SELECT bankadj_id AS id, 3 AS altid, WHERE ( (((bankadjtype_iscredit=true) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=false) AND (bankadj_amount < 0))) AND (bankadj_bankadjtype_id=bankadjtype_id) AND (NOT bankadj_posted) - AND (bankadj_bankaccnt_id=) ) + AND (bankadj_bankaccnt_id=) + + AND ('AD' = ) + + + AND (bankadj_id = ) + + ) ORDER BY sortdate; diff --git a/foundation-database/public/tables/metasql/bankrec-receipts.mql b/foundation-database/public/tables/metasql/bankrec-receipts.mql index 82053eb95..368a7902a 100644 --- a/foundation-database/public/tables/metasql/bankrec-receipts.mql +++ b/foundation-database/public/tables/metasql/bankrec-receipts.mql @@ -47,7 +47,7 @@ SELECT gltrans_id AS id, 1 AS altid, jrnluse_use AS use, gltrans_journalnumber AS jrnlnum, COALESCE(date(jrnluse_date), gltrans_date) AS f_jrnldate, COALESCE(bankrecitem_cleared, FALSE) AS cleared, - gltrans_date AS f_date, + COALESCE(bankrecitem_effdate, gltrans_date) AS f_date, gltrans_doctype AS doc_type, gltrans_docnumber AS docnumber, gltrans_notes AS notes, @@ -84,13 +84,20 @@ SELECT gltrans_id AS id, 1 AS altid, AND (NOT COALESCE(checkhead_void, false)) AND (gltrans_amount < 0) AND (gltrans_doctype != 'JP') - AND (bankaccnt_id=) ) + AND (bankaccnt_id=) + + AND ('GL' = ) + + + AND (gltrans_id = ) + + ) UNION ALL SELECT sltrans_id AS id, 2 AS altid, jrnluse_use AS use, sltrans_journalnumber AS jrnlnum, COALESCE(date(jrnluse_date), sltrans_date) AS f_jrnldate, COALESCE(bankrecitem_cleared, FALSE) AS cleared, - sltrans_date AS f_date, + COALESCE(bankrecitem_effdate, sltrans_date) AS f_date, sltrans_doctype AS doc_type, sltrans_docnumber AS docnumber, sltrans_notes AS notes, @@ -125,12 +132,19 @@ SELECT sltrans_id AS id, 2 AS altid, WHERE ((NOT sltrans_rec) AND (NOT COALESCE(checkhead_void, false)) AND (sltrans_amount < 0) - AND (bankaccnt_id=) ) + AND (bankaccnt_id=) + + AND ('SL' = ) + + + AND (sltrans_id = ) + + ) UNION ALL SELECT bankadj_id AS id, 3 AS altid, '' AS use, NULL AS jrnlnum, bankadj_date AS f_jrnldate, COALESCE(bankrecitem_cleared, FALSE) AS cleared, - bankadj_date AS f_date, + COALESCE(bankrecitem_effdate, bankadj_date) AS f_date, 'ADJ' AS doc_type, bankadj_docnumber AS docnumber, bankadjtype_name AS notes, @@ -150,6 +164,13 @@ SELECT bankadj_id AS id, 3 AS altid, WHERE ( (((bankadjtype_iscredit=false) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=true) AND (bankadj_amount < 0))) AND (bankadj_bankadjtype_id=bankadjtype_id) AND (NOT bankadj_posted) - AND (bankadj_bankaccnt_id=) ) + AND (bankadj_bankaccnt_id=) + + AND ('AD' = ) + + + AND (bankadj_id = ) + + ) ORDER BY jrnldate, jrnlnum, sortdate; -- 2.39.2