_accntid INTEGER;
_sequence INTEGER;
_gltransid INTEGER;
+ _result INTEGER;
_r RECORD;
_tax RECORD;
IF (fetchMetricBool('CashBasedTax')) THEN
-- Cash based tax distributions
-- GL Transactions
+ SELECT fetchGLSequence() INTO _sequence;
FOR _r IN SELECT *
FROM bankrecitem
- WHERE ( (bankrecitem_source = 'GL')
- AND (bankrecitem_cleared)
+ WHERE ( (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,
+ FOR _tax IN SELECT docnumber, custname, distdate, source, doctype,
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,
+ FROM (
+ -- Cash receipt, gltrans
+ SELECT aropen_docnumber AS docnumber, cust_name AS custname,
+ aropen_curr_id AS currid, gltrans_date AS distdate,
+ (cashrcptitem_amount / aropen_amount) AS percentpaid,
+ gltrans_source AS source, gltrans_doctype AS doctype,
tax_sales_accnt_id, tax_dist_accnt_id,
taxhist_tax, taxhist_docdate
FROM gltrans JOIN cashrcpt ON ((gltrans_source='A/R')
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 custinfo ON (cust_id=aropen_cust_id)
+ JOIN cohist ON (cohist_invcnumber=aropen_docnumber AND cohist_doctype=aropen_doctype)
+ JOIN cohisttax ON (taxhist_parent_id=cohist_id)
+ JOIN tax ON (tax_id=taxhist_tax_id)
+ WHERE (gltrans_id=_r.bankrecitem_source_id)
+ -- Cash receipt, sltrans
+ UNION
+ SELECT aropen_docnumber AS docnumber, cust_name AS custname,
+ aropen_curr_id AS currid, sltrans_date AS distdate,
+ (cashrcptitem_amount / aropen_amount) AS percentpaid,
+ sltrans_source AS source, sltrans_doctype AS doctype,
+ tax_sales_accnt_id, tax_dist_accnt_id,
+ taxhist_tax, taxhist_docdate
+ FROM sltrans JOIN cashrcpt ON ((sltrans_source='A/R')
+ AND (sltrans_doctype='CR')
+ AND (sltrans_misc_id=cashrcpt_id))
+ JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
+ JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
+ JOIN custinfo ON (cust_id=aropen_cust_id)
+ JOIN cohist ON (cohist_invcnumber=aropen_docnumber AND cohist_doctype=aropen_doctype)
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,
+ WHERE (sltrans_id=_r.bankrecitem_source_id)
+ -- Cash payment, gltrans
+ UNION
+ SELECT apopen_docnumber AS docnumber, vend_name AS vendname,
+ apopen_curr_id AS currid, gltrans_date AS distdate,
+ (vohead_amount / apopen_amount) AS percentpaid,
+ gltrans_source AS source, gltrans_doctype AS doctype,
+ tax_sales_accnt_id, tax_dist_accnt_id,
+ taxhist_tax, taxhist_docdate
+ FROM gltrans JOIN checkhead ON ((gltrans_source='A/P')
+ AND (gltrans_doctype='CK')
+ AND (gltrans_misc_id=checkhead_id))
+ JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
+ JOIN apopen ON (apopen_id=checkitem_apopen_id)
+ JOIN vohead ON (vohead_number=apopen_docnumber)
+ JOIN vendinfo ON (vend_id=apopen_vend_id)
+ JOIN voheadtax ON (taxhist_parent_id=vohead_id)
+ JOIN tax ON (tax_id=taxhist_tax_id)
+ WHERE (gltrans_id=_r.bankrecitem_source_id)
+ UNION
+ SELECT apopen_docnumber AS docnumber, vend_name AS vendname,
+ apopen_curr_id AS currid, gltrans_date AS distdate,
+ (vohead_amount / apopen_amount) AS percentpaid,
+ gltrans_source AS source, gltrans_doctype AS doctype,
+ tax_sales_accnt_id, tax_dist_accnt_id,
+ taxhist_tax, taxhist_docdate
+ FROM gltrans JOIN checkhead ON ((gltrans_source='A/P')
+ AND (gltrans_doctype='CK')
+ AND (gltrans_misc_id=checkhead_id))
+ JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
+ JOIN apopen ON (apopen_id=checkitem_apopen_id)
+ JOIN vohead ON (vohead_number=apopen_docnumber)
+ JOIN vendinfo ON (vend_id=apopen_vend_id)
+ JOIN voitem ON (voitem_vohead_id=vohead_id)
+ JOIN voitemtax ON (taxhist_parent_id=voitem_id)
+ JOIN tax ON (tax_id=taxhist_tax_id)
+ WHERE (gltrans_id=_r.bankrecitem_source_id)
+ -- Cash payment, sltrans
+ UNION
+ SELECT apopen_docnumber AS docnumber, vend_name AS vendname,
+ apopen_curr_id AS currid, sltrans_date AS distdate,
+ (vohead_amount / apopen_amount) AS percentpaid,
+ sltrans_source AS source, sltrans_doctype AS doctype,
+ tax_sales_accnt_id, tax_dist_accnt_id,
+ taxhist_tax, taxhist_docdate
+ FROM sltrans JOIN checkhead ON ((sltrans_source='A/P')
+ AND (sltrans_doctype='CK')
+ AND (sltrans_misc_id=checkhead_id))
+ JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
+ JOIN apopen ON (apopen_id=checkitem_apopen_id)
+ JOIN vohead ON (vohead_number=apopen_docnumber)
+ JOIN vendinfo ON (vend_id=apopen_vend_id)
+ JOIN voheadtax ON (taxhist_parent_id=vohead_id)
+ JOIN tax ON (tax_id=taxhist_tax_id)
+ WHERE (sltrans_id=_r.bankrecitem_source_id)
+ UNION
+ SELECT apopen_docnumber AS docnumber, vend_name AS vendname,
+ apopen_curr_id AS currid, sltrans_date AS distdate,
+ (vohead_amount / apopen_amount) AS percentpaid,
+ sltrans_source AS source, sltrans_doctype AS doctype,
+ tax_sales_accnt_id, tax_dist_accnt_id,
+ taxhist_tax, taxhist_docdate
+ FROM sltrans JOIN checkhead ON ((sltrans_source='A/P')
+ AND (sltrans_doctype='CK')
+ AND (sltrans_misc_id=checkhead_id))
+ JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
+ JOIN apopen ON (apopen_id=checkitem_apopen_id)
+ JOIN vohead ON (vohead_number=apopen_docnumber)
+ JOIN vendinfo ON (vend_id=apopen_vend_id)
+ JOIN voitem ON (voitem_vohead_id=vohead_id)
+ JOIN voitemtax ON (taxhist_parent_id=voitem_id)
+ JOIN tax ON (tax_id=taxhist_tax_id)
+ WHERE (sltrans_id=_r.bankrecitem_source_id)
+ ) AS data
+ GROUP BY docnumber, custname, currid, distdate, percentpaid,
+ source, doctype,
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 );
+ SELECT insertIntoGLSeries( _sequence, _tax.source, _tax.doctype, _tax.docnumber,
+ _tax.tax_dist_accnt_id,
+ _tax.taxbasevalue,
+ COALESCE(_r.bankrecitem_effdate, _tax.distdate), _tax.custname ) INTO _result;
+ IF (_result < 0) THEN
+ RAISE EXCEPTION 'insertIntoGLSeries failed, result=%', _result;
+ END IF;
+ SELECT insertIntoGLSeries( _sequence, _tax.source, _tax.doctype, _tax.docnumber,
+ _tax.tax_sales_accnt_id,
+ (_tax.taxbasevalue * -1.0),
+ COALESCE(_r.bankrecitem_effdate, _tax.distdate), _tax.custname ) INTO _result;
+ IF (_result < 0) THEN
+ RAISE EXCEPTION 'insertIntoGLSeries failed, result=%', _result;
+ END IF;
END LOOP;
-- second, create a taxpay row for each taxhist
- FOR _tax IN SELECT *,
+ FOR _tax IN SELECT taxhist_id, applyid, distdate,
ROUND(taxhist_tax * percentpaid, 2) AS taxpaid
- FROM (SELECT *,
- (cashrcptitem_amount / calcInvoiceAmt(invchead_id)) AS percentpaid
+ FROM (
+ -- Cash receipt, gltrans
+ SELECT taxhist_id, aropen_id AS applyid, gltrans_date AS distdate, taxhist_tax,
+ (cashrcptitem_amount / aropen_amount) 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 cohist ON (cohist_invcnumber=aropen_docnumber AND cohist_doctype=aropen_doctype)
JOIN cohisttax ON (taxhist_parent_id=cohist_id)
- WHERE (gltrans_id=_r.bankrecitem_source_id)) AS data
+ WHERE (gltrans_id=_r.bankrecitem_source_id)
+ -- Cash receipt, sltrans
+ UNION
+ SELECT taxhist_id, aropen_id AS applyid, sltrans_date AS distdate, taxhist_tax,
+ (cashrcptitem_amount / aropen_amount) AS percentpaid
+ FROM sltrans JOIN cashrcpt ON ((sltrans_source='A/R')
+ AND (sltrans_doctype='CR')
+ AND (sltrans_misc_id=cashrcpt_id))
+ JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
+ JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
+ JOIN cohist ON (cohist_invcnumber=aropen_docnumber AND cohist_doctype=aropen_doctype)
+ JOIN cohisttax ON (taxhist_parent_id=cohist_id)
+ WHERE (sltrans_id=_r.bankrecitem_source_id)
+ -- Cash payment, gltrans
+ UNION
+ SELECT taxhist_id, apopen_id AS applyid, gltrans_date AS distdate, taxhist_tax,
+ (checkitem_amount / apopen_amount) AS percentpaid
+ FROM gltrans JOIN checkhead ON ((gltrans_source='A/P')
+ AND (gltrans_doctype='CK')
+ AND (gltrans_misc_id=checkhead_id))
+ JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
+ JOIN apopen ON (apopen_id=checkitem_apopen_id)
+ JOIN vohead ON (vohead_number=apopen_docnumber)
+ JOIN voheadtax ON (taxhist_parent_id=vohead_id)
+ WHERE (gltrans_id=_r.bankrecitem_source_id)
+ UNION
+ SELECT taxhist_id, apopen_id AS applyid, gltrans_date AS distdate, taxhist_tax,
+ (checkitem_amount / apopen_amount) AS percentpaid
+ FROM gltrans JOIN checkhead ON ((gltrans_source='A/P')
+ AND (gltrans_doctype='CK')
+ AND (gltrans_misc_id=checkhead_id))
+ JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
+ JOIN apopen ON (apopen_id=checkitem_apopen_id)
+ JOIN vohead ON (vohead_number=apopen_docnumber)
+ JOIN voitem ON (voitem_vohead_id=vohead_id)
+ JOIN voitemtax ON (taxhist_parent_id=voitem_id)
+ WHERE (gltrans_id=_r.bankrecitem_source_id)
+ -- Cash payment, sltrans
+ UNION
+ SELECT taxhist_id, apopen_id AS applyid, sltrans_date AS distdate, taxhist_tax,
+ (checkitem_amount / apopen_amount) AS percentpaid
+ FROM sltrans JOIN checkhead ON ((sltrans_source='A/P')
+ AND (sltrans_doctype='CK')
+ AND (sltrans_misc_id=checkhead_id))
+ JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
+ JOIN apopen ON (apopen_id=checkitem_apopen_id)
+ JOIN vohead ON (vohead_number=apopen_docnumber)
+ JOIN voheadtax ON (taxhist_parent_id=vohead_id)
+ WHERE (sltrans_id=_r.bankrecitem_source_id)
+ UNION
+ SELECT taxhist_id, apopen_id AS applyid, sltrans_date AS distdate, taxhist_tax,
+ (checkitem_amount / apopen_amount) AS percentpaid
+ FROM sltrans JOIN checkhead ON ((sltrans_source='A/P')
+ AND (sltrans_doctype='CK')
+ AND (sltrans_misc_id=checkhead_id))
+ JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
+ JOIN apopen ON (apopen_id=checkitem_apopen_id)
+ JOIN vohead ON (vohead_number=apopen_docnumber)
+ JOIN voitem ON (voitem_vohead_id=vohead_id)
+ JOIN voitemtax ON (taxhist_parent_id=voitem_id)
+ WHERE (sltrans_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 );
+ ( _tax.taxhist_id, _tax.applyid, COALESCE(_r.bankrecitem_effdate, _tax.distdate), _tax.taxpaid );
END LOOP;
END LOOP;
- PERFORM postGLSeries(_sequence, fetchJournalNumber('GL-MISC'));
+ SELECT postGLSeries(_sequence, fetchJournalNumber('GL-MISC')) INTO _result;
+ IF (_result < 0) THEN
+ RAISE EXCEPTION 'postGLSeries failed, result=%', _result;
+ END IF;
END IF;
------------BEGIN SALES--------------
-- All sales tax history including memos
SELECT
- taxhist_distdate, taxhist_journalnumber,
- tax_code AS tax, tax_descrip,
+ COALESCE(taxpay_distdate, taxhist_distdate) AS taxhist_distdate,
+ taxhist_journalnumber, tax_code AS tax, tax_descrip,
COALESCE(taxtype_name,<? value("none") ?>) AS taxtype, taxtype_descrip,
COALESCE(taxclass_code,<? value("none") ?>) AS taxclass, taxclass_descrip,
COALESCE(taxauth_code,<? value("none") ?>) AS taxauth, taxauth_name AS taxauth_descrip,
END AS freightbase,
CASE
WHEN (cohist_misc_type = 'F') THEN
- taxhist_tax / taxhist_curr_rate
+ COALESCE(taxpay_tax, taxhist_tax) / taxhist_curr_rate
ELSE 0
END AS freighttax,
0 AS purchasebase,
- taxhist_tax AS taxlocal,
- taxhist_tax / taxhist_curr_rate AS taxbase,
- taxhist_tax / taxhist_curr_rate AS salestaxbase,
+ COALESCE(taxpay_tax, taxhist_tax) AS taxlocal,
+ COALESCE(taxpay_tax, taxhist_tax) / taxhist_curr_rate AS taxbase,
+ COALESCE(taxpay_tax, taxhist_tax) / taxhist_curr_rate AS salestaxbase,
0 AS purchasetaxbase,
'qty' AS qty_xtnumericrole,
'saleprice' AS unitprice_xtnumericrole,
LEFT OUTER JOIN taxzone ON (cohist_taxzone_id=taxzone_id)
LEFT OUTER JOIN itemsite ON (cohist_itemsite_id=itemsite_id)
LEFT OUTER JOIN item ON (itemsite_item_id=item_id)
+ LEFT OUTER JOIN taxpay ON (taxpay_taxhist_id=taxhist_id)
+WHERE ((true)
<? if exists("distDate") ?>
-WHERE ((taxhist_distdate BETWEEN <? value("startDate") ?>
- AND <? value("endDate") ?>)
+ AND (taxhist_distdate BETWEEN <? value("startDate") ?>
+ AND <? value("endDate") ?>)
<? else ?>
-WHERE ((taxhist_docdate BETWEEN <? value("startDate") ?>
- AND <? value("endDate") ?>)
+ AND (taxhist_docdate BETWEEN <? value("startDate") ?>
+ AND <? value("endDate") ?>)
<? endif ?>
<? if exists("tax_id") ?>
AND (taxhist_tax_id=<? value("tax_id") ?>)
<? if exists("taxzone_id") ?>
AND (taxzone_id=<? value("taxzone_id") ?>)
<? endif ?>
+<? if exists("cashbasedtax") ?>
+ AND (taxpay_id IS NOT NULL)
+<? endif ?>
)
<? endif ?>
--------------END SALES--------------------
<? if exists("showPurchases") ?>
-- A/P Memo history
SELECT
- taxhist_distdate, taxhist_journalnumber,
- tax_code AS tax, tax_descrip,
+ COALESCE(taxpay_distdate, taxhist_distdate) AS taxhist_distdate,
+ taxhist_journalnumber, tax_code AS tax, tax_descrip,
COALESCE(taxtype_name,<? value("none") ?>) AS taxtype, taxtype_descrip,
COALESCE(taxclass_code,<? value("none") ?>) AS taxclass, taxclass_descrip,
COALESCE(taxauth_code,<? value("none") ?>) AS taxauth, taxauth_name AS taxauth_descrip,
0 AS freightbase,
0 AS freighttax,
apopen_amount / apopen_curr_rate AS purchasebase,
- taxhist_tax AS taxlocal,
- taxhist_tax / taxhist_curr_rate AS taxbase,
+ COALESCE(taxpay_tax, taxhist_tax) AS taxlocal,
+ COALESCE(taxpay_tax, taxhist_tax) / taxhist_curr_rate AS taxbase,
0 AS salestaxbase,
- taxhist_tax / taxhist_curr_rate AS purchasetaxbase,
+ COALESCE(taxpay_tax, taxhist_tax) / taxhist_curr_rate AS purchasetaxbase,
'qty' AS qty_xtnumericrole,
'purchaseprice' AS unitprice_xtnumericrole,
'extprice' AS extension_xtnumericrole,
JOIN curr_symbol ON (curr_id=taxhist_curr_id)
LEFT OUTER JOIN taxclass ON (tax_taxclass_id=taxclass_id)
LEFT OUTER JOIN taxauth ON (tax_taxauth_id=taxauth_id)
+ LEFT OUTER JOIN taxpay ON (taxpay_taxhist_id=taxhist_id)
+WHERE ((true)
<? if exists("distDate") ?>
-WHERE ((taxhist_distdate BETWEEN <? value("startDate") ?>
- AND <? value("endDate") ?>)
+ AND (taxhist_distdate BETWEEN <? value("startDate") ?>
+ AND <? value("endDate") ?>)
<? else ?>
-WHERE ((taxhist_docdate BETWEEN <? value("startDate") ?>
- AND <? value("endDate") ?>)
+ AND (taxhist_docdate BETWEEN <? value("startDate") ?>
+ AND <? value("endDate") ?>)
<? endif ?>
<? if exists("tax_id") ?>
AND (taxhist_tax_id=<? value("tax_id") ?>)
AND (false)
<? endif ?>
AND (taxhist_distdate IS NOT NULL)
+<? if exists("cashbasedtax") ?>
+ AND (taxpay_id IS NOT NULL)
+<? endif ?>
)
UNION ALL
--Voucher Header History
SELECT
- taxhist_distdate, taxhist_journalnumber,
- tax_code AS tax, tax_descrip,
+ COALESCE(taxpay_distdate, taxhist_distdate) AS taxhist_distdate,
+ taxhist_journalnumber, tax_code AS tax, tax_descrip,
COALESCE(taxtype_name,<? value("none") ?>) AS taxtype, taxtype_descrip,
COALESCE(taxclass_code,<? value("none") ?>) AS taxclass, taxclass_descrip,
COALESCE(taxauth_code,<? value("none") ?>) AS taxauth, taxauth_name AS taxauth_descrip,
0 AS freightbase,
0 AS freighttax,
0 AS purchasebase,
- taxhist_tax AS taxlocal,
- taxhist_tax / taxhist_curr_rate AS taxbase,
+ COALESCE(taxpay_tax, taxhist_tax) AS taxlocal,
+ COALESCE(taxpay_tax, taxhist_tax) / taxhist_curr_rate AS taxbase,
0 AS salestaxbase,
- taxhist_tax / taxhist_curr_rate AS purchasetaxbase,
+ COALESCE(taxpay_tax, taxhist_tax) / taxhist_curr_rate AS purchasetaxbase,
'qty' AS qty_xtnumericrole,
'purchaseprice' AS unitprice_xtnumericrole,
'extprice' AS amount_xtnumericrole,
LEFT OUTER JOIN taxclass ON (tax_taxclass_id=taxclass_id)
LEFT OUTER JOIN taxauth ON (tax_taxauth_id=taxauth_id)
LEFT OUTER JOIN taxzone ON (vohead_taxzone_id=taxzone_id)
+ LEFT OUTER JOIN taxpay ON (taxpay_taxhist_id=taxhist_id)
+WHERE ((true)
<? if exists("distDate") ?>
-WHERE ((taxhist_distdate BETWEEN <? value("startDate") ?>
- AND <? value("endDate") ?>)
+ AND (taxhist_distdate BETWEEN <? value("startDate") ?>
+ AND <? value("endDate") ?>)
<? else ?>
-WHERE ((taxhist_docdate BETWEEN <? value("startDate") ?>
- AND <? value("endDate") ?>)
+ AND (taxhist_docdate BETWEEN <? value("startDate") ?>
+ AND <? value("endDate") ?>)
<? endif ?>
<? if exists("tax_id") ?>
AND (taxhist_tax_id=<? value("tax_id") ?>)
AND (taxzone_id=<? value("taxzone_id") ?>)
<? endif ?>
AND (taxhist_distdate IS NOT NULL)
+<? if exists("cashbasedtax") ?>
+ AND (taxpay_id IS NOT NULL)
+<? endif ?>
)
UNION ALL
--Voucher Line Item
SELECT
- taxhist_distdate, taxhist_journalnumber,
- tax_code AS tax, tax_descrip,
+ COALESCE(taxpay_distdate, taxhist_distdate) AS taxhist_distdate,
+ taxhist_journalnumber, tax_code AS tax, tax_descrip,
COALESCE(taxtype_name,<? value("none") ?>) AS taxtype, taxtype_descrip,
COALESCE(taxclass_code,<? value("none") ?>) AS taxclass, taxclass_descrip,
COALESCE(taxauth_code,<? value("none") ?>) AS taxauth, taxauth_name AS taxauth_descrip,
0 AS freightbase,
0 AS freighttax,
currToBase(vohead_curr_id, COALESCE(SUM(vodist_amount),0), vohead_distdate) AS purchasebase,
- taxhist_tax AS taxlocal,
- taxhist_tax / taxhist_curr_rate AS taxbase,
+ COALESCE(taxpay_tax, taxhist_tax) AS taxlocal,
+ COALESCE(taxpay_tax, taxhist_tax) / taxhist_curr_rate AS taxbase,
0 AS salestaxbase,
- taxhist_tax / taxhist_curr_rate AS purchasetaxbase,
+ COALESCE(taxpay_tax, taxhist_tax) / taxhist_curr_rate AS purchasetaxbase,
'qty' AS qty_xtnumericrole,
'purchaseprice' AS unitprice_xtnumericrole,
'extprice' AS amount_xtnumericrole,
LEFT OUTER JOIN itemsite ON (poitem_itemsite_id=itemsite_id)
LEFT OUTER JOIN item ON (itemsite_item_id=item_id)
LEFT OUTER JOIN expcat ON (expcat_id=poitem_expcat_id)
+ LEFT OUTER JOIN taxpay ON (taxpay_taxhist_id=taxhist_id)
+WHERE ((true)
<? if exists("distDate") ?>
-WHERE ((taxhist_distdate BETWEEN <? value("startDate") ?>
- AND <? value("endDate") ?>)
+ AND (taxhist_distdate BETWEEN <? value("startDate") ?>
+ AND <? value("endDate") ?>)
<? else ?>
-WHERE ((taxhist_docdate BETWEEN <? value("startDate") ?>
- AND <? value("endDate") ?>)
+ AND (taxhist_docdate BETWEEN <? value("startDate") ?>
+ AND <? value("endDate") ?>)
<? endif ?>
<? if exists("tax_id") ?>
AND (taxhist_tax_id=<? value("tax_id") ?>)
AND (taxzone_id=<? value("taxzone_id") ?>)
<? endif ?>
AND (taxhist_distdate IS NOT NULL)
+<? if exists("cashbasedtax") ?>
+ AND (taxpay_id IS NOT NULL)
+<? endif ?>
)
-GROUP BY taxhist_id,taxhist_distdate,tax_code,tax_descrip,
+GROUP BY taxhist_id, taxpay_distdate,taxhist_distdate,tax_code,tax_descrip,
taxtype_name,taxtype_descrip,taxclass_code,taxclass_descrip,
taxauth_code,taxauth_descrip,taxzone,taxzone_descrip,curr_abbr,
vohead_number,pohead_number,item_number,item_descrip1,
vohead_curr_id,vohead_distdate,vohead_docdate,vend_name,
expcat_code,expcat_descrip,taxhist_tax,taxhist_curr_rate,
- voitem_qty, taxhist_journalnumber
+ voitem_qty, taxhist_journalnumber, taxpay_tax
<? endif ?>
-------------END PURCHASE--------------
ORDER BY docdate DESC, docnumber DESC
projects / xtuple / commitdiff