From 7427d75363077f28c898b79117238d0c341faf13 Mon Sep 17 00:00:00 2001 From: Ben Thompson Date: Thu, 1 May 2014 16:03:29 -0400 Subject: [PATCH] No need to query on taxhist when we know the inherited table. --- .../functions/calculatetaxdetailline.sql | 18 +++--- .../functions/calculatetaxdetailsummary.sql | 62 +++++++++---------- 2 files changed, 37 insertions(+), 43 deletions(-) diff --git a/foundation-database/public/functions/calculatetaxdetailline.sql b/foundation-database/public/functions/calculatetaxdetailline.sql index df47bb400..b48cbc5ac 100644 --- a/foundation-database/public/functions/calculatetaxdetailline.sql +++ b/foundation-database/public/functions/calculatetaxdetailline.sql @@ -1,8 +1,8 @@ -CREATE OR REPLACE FUNCTION calculatetaxdetailline(text, integer) +CREATE OR REPLACE FUNCTION calculatetaxdetailline(text, integer) RETURNS SETOF taxdetail AS $BODY$ --- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. +-- 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 pOrderType ALIAS FOR $1; @@ -12,7 +12,7 @@ DECLARE _totaltax numeric; _y RECORD; _table text; - + BEGIN _totaltax=0.0; @@ -31,14 +31,12 @@ BEGIN ELSIF pOrderType = 'AP' THEN _table := 'apopentax'; END IF; - + _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, COALESCE(taxhist_sequence,0) AS taxhist_sequence - FROM taxhist - JOIN tax ON (taxhist_tax_id=tax_id) - JOIN pg_class ON (pg_class.oid=taxhist.tableoid) - WHERE ( (taxhist_parent_id = ' || pOrderId || ') - AND (relname=''' || _table || ''') );'; - + FROM ' || _table || ' + JOIN tax ON (taxhist_tax_id=tax_id) + WHERE ( (taxhist_parent_id = ' || pOrderId || ') );'; + FOR _y IN EXECUTE _qry LOOP _row.taxdetail_tax_id=_y.tax_id; diff --git a/foundation-database/public/functions/calculatetaxdetailsummary.sql b/foundation-database/public/functions/calculatetaxdetailsummary.sql index b4ffa5586..c21cb14ae 100644 --- a/foundation-database/public/functions/calculatetaxdetailsummary.sql +++ b/foundation-database/public/functions/calculatetaxdetailsummary.sql @@ -2,7 +2,7 @@ CREATE OR REPLACE FUNCTION calculatetaxdetailsummary(text, integer, text) RETURNS SETOF taxdetail AS $BODY$ --- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. +-- 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 pOrderType ALIAS FOR $1; @@ -15,44 +15,44 @@ DECLARE _x RECORD; _y RECORD; _table text; - + BEGIN _totaltax=0.0; IF pOrderType IN ('S','Q','RA','PO') THEN - + IF pOrderType = 'S' THEN _qry := 'SELECT ' || 'COALESCE(cohead_taxzone_id, -1) AS taxzone_id, cohead_orderdate AS order_date, cohead_curr_id AS curr_id, COALESCE(coitem_taxtype_id, -1) AS taxtype_id, ROUND((coitem_qtyord * coitem_qty_invuomratio) * (coitem_price / coitem_price_invuomratio),2) AS amount FROM cohead, coitem WHERE ( (coitem_cohead_id = ' || pOrderId || ') - AND (' || 'cohead_id = coitem_cohead_id) + AND (' || 'cohead_id = coitem_cohead_id) AND ( coitem_status != ''X'') )'; ELSEIF pOrderType = 'Q' THEN _qry := 'SELECT ' || 'COALESCE(quhead_taxzone_id, -1) AS taxzone_id, quhead_quotedate AS order_date, - quhead_curr_id AS curr_id, COALESCE(quitem_taxtype_id, -1) AS taxtype_id, + quhead_curr_id AS curr_id, COALESCE(quitem_taxtype_id, -1) AS taxtype_id, ROUND((quitem_qtyord * quitem_qty_invuomratio) * (quitem_price / quitem_price_invuomratio),2) AS amount - FROM quhead, quitem + FROM quhead, quitem WHERE ( (quitem_quhead_id = ' || pOrderId || ') - AND (quhead_id = quitem_quhead_id) )'; + AND (quhead_id = quitem_quhead_id) )'; ELSEIF pOrderType = 'RA' THEN _qry := 'SELECT ' || 'COALESCE(rahead_taxzone_id, -1) AS taxzone_id, rahead_authdate AS order_date, - rahead_curr_id AS curr_id, COALESCE(raitem_taxtype_id, -1) AS taxtype_id, + rahead_curr_id AS curr_id, COALESCE(raitem_taxtype_id, -1) AS taxtype_id, ROUND((raitem_qtyauthorized * raitem_qty_invuomratio) * (raitem_unitprice / raitem_price_invuomratio),2) AS amount - FROM rahead, raitem + FROM rahead, raitem WHERE ( (raitem_rahead_id = ' || pOrderId || ') AND (rahead_id = raitem_rahead_id) )'; ELSEIF pOrderType = 'PO' THEN _qry := 'SELECT ' || 'COALESCE(pohead_taxzone_id, -1) AS taxzone_id, pohead_orderdate AS order_date, - pohead_curr_id AS curr_id, COALESCE(poitem_taxtype_id, -1) AS taxtype_id, + pohead_curr_id AS curr_id, COALESCE(poitem_taxtype_id, -1) AS taxtype_id, ROUND(poitem_qty_ordered * poitem_unitprice, 2) AS amount - FROM pohead, poitem + FROM pohead, poitem WHERE ( (poitem_pohead_id = ' || pOrderId || ') - AND (pohead_id = poitem_pohead_id) )'; + AND (pohead_id = poitem_pohead_id) )'; END IF; FOR _x IN EXECUTE _qry - LOOP + LOOP _qry1 := 'SELECT * from calculatetaxdetail(' || _x.taxzone_id || ',' || _x.taxtype_id || ',''' || _x.order_date || ''',' || _x.curr_id || ',' || _x.amount || ')'; FOR _y IN EXECUTE _qry1 LOOP @@ -68,11 +68,11 @@ BEGIN END LOOP; IF pDisplayType = 'T' AND pOrderType <> 'PO' THEN - IF pOrderType = 'S' THEN + IF pOrderType = 'S' THEN _qry := 'SELECT COALESCE(cohead_taxzone_id, -1) AS taxzone_id, cohead_orderdate AS order_date, cohead_curr_id AS curr_id, cohead_freight AS freight FROM cohead WHERE cohead_id = ' || pOrderId ; - ELSEIF pOrderType = 'Q' THEN + ELSEIF pOrderType = 'Q' THEN _qry := 'SELECT COALESCE(quhead_taxzone_id, -1) AS taxzone_id, quhead_quotedate AS order_date, quhead_curr_id AS curr_id, COALESCE(quhead_freight,0) AS freight FROM quhead WHERE quhead_id = ' || pOrderId; @@ -98,7 +98,7 @@ BEGIN END LOOP; END LOOP; END IF; - + ELSEIF pOrderType IN ('I','B','CM', 'VO','TO') THEN IF (pOrderType='I') THEN _table := 'invcheadtax'; @@ -111,36 +111,36 @@ BEGIN ELSIF (pOrderType='TO') THEN _table := 'tohead'; END IF; - + IF pOrderType = 'I' AND (pDisplayType IN ('L','T')) THEN _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence FROM invchead, invcitemtax LEFT OUTER JOIN tax ON (taxhist_tax_id=tax_id) - LEFT OUTER JOIN invcitem ON (invcitem_id=taxhist_parent_id) - WHERE invcitem_invchead_id = ' || pOrderId || ' + LEFT OUTER JOIN invcitem ON (invcitem_id=taxhist_parent_id) + WHERE invcitem_invchead_id = ' || pOrderId || ' AND invchead_id = invcitem_invchead_id '; ELSIF pOrderType = 'B' AND (pDisplayType IN ('L','T')) THEN _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence FROM cobmisc, cobilltax LEFT OUTER JOIN tax ON (taxhist_tax_id=tax_id) LEFT OUTER JOIN cobill ON (cobill_id=taxhist_parent_id) - WHERE cobill_cobmisc_id = ' || pOrderId || ' + WHERE cobill_cobmisc_id = ' || pOrderId || ' AND cobmisc_id = cobill_cobmisc_id '; ELSIF pOrderType = 'CM' AND (pDisplayType IN ('L','T')) THEN _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence FROM cmhead, cmitemtax LEFT OUTER JOIN tax ON (taxhist_tax_id=tax_id) LEFT OUTER JOIN cmitem ON (cmitem_id=taxhist_parent_id) - WHERE cmitem_cmhead_id = ' || pOrderId || ' + WHERE cmitem_cmhead_id = ' || pOrderId || ' AND cmhead_id = cmitem_cmhead_id '; ELSIF pOrderType = 'VO' AND (pDisplayType IN ('L','T')) THEN _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence FROM vohead, voitemtax LEFT OUTER JOIN tax ON (taxhist_tax_id=tax_id) LEFT OUTER JOIN voitem ON (voitem_id=taxhist_parent_id) - WHERE voitem_vohead_id = ' || pOrderId || ' + WHERE voitem_vohead_id = ' || pOrderId || ' AND vohead_id = voitem_vohead_id '; ELSIF pOrderType = 'TO' AND (pDisplayType IN ('L','T')) THEN _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence FROM tohead, toitemtax LEFT OUTER JOIN tax ON (taxhist_tax_id=tax_id) LEFT OUTER JOIN toitem ON (toitem_id=taxhist_parent_id) - WHERE toitem_tohead_id = ' || pOrderId || ' + WHERE toitem_tohead_id = ' || pOrderId || ' AND tohead_id = toitem_tohead_id '; END IF; IF pDisplayType IN ('F','T') AND pOrderType <> 'VO' THEN @@ -148,24 +148,20 @@ BEGIN _qry := _qry || ' UNION ALL '; END IF; _qry := _qry || 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence - FROM taxhist + FROM ' || _table || ' JOIN tax ON (taxhist_tax_id=tax_id) - JOIN pg_class ON (pg_class.oid=taxhist.tableoid) - WHERE ( (taxhist_parent_id = ' || pOrderId || ') - AND (taxhist_taxtype_id=getfreighttaxtypeid()) - AND (relname=''' || _table || ''') )'; + WHERE ( (taxhist_parent_id = ' || pOrderId || ') + AND (taxhist_taxtype_id=getfreighttaxtypeid()) )'; END IF; IF pDisplayType IN ('A','T') THEN IF (length(_qry) > 0) THEN _qry := _qry || ' UNION ALL '; END IF; _qry := _qry || 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence - FROM taxhist + FROM ' || _table || ' JOIN tax ON (taxhist_tax_id=tax_id) - JOIN pg_class ON (pg_class.oid=taxhist.tableoid) - WHERE ( (taxhist_parent_id = ' || pOrderId || ') - AND (taxhist_taxtype_id=getadjustmenttaxtypeid()) - AND (relname=''' || _table || ''') )'; + WHERE ( (taxhist_parent_id = ' || pOrderId || ') + AND (taxhist_taxtype_id=getadjustmenttaxtypeid()) )'; END IF; FOR _y IN EXECUTE _qry -- 2.39.2