_sequence INTEGER;
_gltransid INTEGER;
_r RECORD;
+ _tax RECORD;
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
-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
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)
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
<? else ?>
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,
AND (NOT COALESCE(checkhead_void, false))
AND (gltrans_amount > 0)
AND (gltrans_doctype != 'JP')
- AND (bankaccnt_id=<? value("bankaccntid") ?>) )
+ AND (bankaccnt_id=<? value("bankaccntid") ?>)
+<? if exists("source") ?>
+ AND ('GL' = <? value("source") ?>)
+<? endif ?>
+<? if exists("sourceid") ?>
+ AND (gltrans_id = <? value("sourceid") ?>)
+<? endif ?>
+ )
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,
AND (NOT sltrans_rec)
AND (NOT COALESCE(checkhead_void, false))
AND (sltrans_amount > 0)
- AND (bankaccnt_id=<? value("bankaccntid") ?>) )
+ AND (bankaccnt_id=<? value("bankaccntid") ?>)
+<? if exists("source") ?>
+ AND ('SL' = <? value("source") ?>)
+<? endif ?>
+<? if exists("sourceid") ?>
+ AND (sltrans_id = <? value("sourceid") ?>)
+<? endif ?>
+ )
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,
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=<? value("bankaccntid") ?>) )
+ AND (bankadj_bankaccnt_id=<? value("bankaccntid") ?>)
+<? if exists("source") ?>
+ AND ('AD' = <? value("source") ?>)
+<? endif ?>
+<? if exists("sourceid") ?>
+ AND (bankadj_id = <? value("sourceid") ?>)
+<? endif ?>
+ )
ORDER BY sortdate;
<? endif ?>
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,
AND (NOT COALESCE(checkhead_void, false))
AND (gltrans_amount < 0)
AND (gltrans_doctype != 'JP')
- AND (bankaccnt_id=<? value("bankaccntid") ?>) )
+ AND (bankaccnt_id=<? value("bankaccntid") ?>)
+<? if exists("source") ?>
+ AND ('GL' = <? value("source") ?>)
+<? endif ?>
+<? if exists("sourceid") ?>
+ AND (gltrans_id = <? value("sourceid") ?>)
+<? endif ?>
+ )
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,
WHERE ((NOT sltrans_rec)
AND (NOT COALESCE(checkhead_void, false))
AND (sltrans_amount < 0)
- AND (bankaccnt_id=<? value("bankaccntid") ?>) )
+ AND (bankaccnt_id=<? value("bankaccntid") ?>)
+<? if exists("source") ?>
+ AND ('SL' = <? value("source") ?>)
+<? endif ?>
+<? if exists("sourceid") ?>
+ AND (sltrans_id = <? value("sourceid") ?>)
+<? endif ?>
+ )
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,
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=<? value("bankaccntid") ?>) )
+ AND (bankadj_bankaccnt_id=<? value("bankaccntid") ?>)
+<? if exists("source") ?>
+ AND ('AD' = <? value("source") ?>)
+<? endif ?>
+<? if exists("sourceid") ?>
+ AND (bankadj_id = <? value("sourceid") ?>)
+<? endif ?>
+ )
ORDER BY jrnldate, jrnlnum, sortdate;
<? endif ?>
projects / xtuple / commitdiff