git.roojs.org Git - xtuple/blob - foundation-database/public/tables/metasql/taxHistory-detail.mql

   1 -- Group: taxHistory
   2 -- Name: detail
   3 -- Notes:
   4 -----------BEGIN SUMMARY----------
   5 -- Notes:
   6 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   7 -- See www.xtuple.com/CPAL for the full text of the software license.
   8
   9 <? if exists("summary") ?>
  10 SELECT <? literal("groupBy") ?>,
  11   <? literal("groupBy") ?>_descrip AS description,
  12   SUM(salesbase) AS salesbase,
  13   SUM(freightbase) AS freightbase,
  14   CASE WHEN (SUM(freighttax) > 0) THEN true ELSE false END AS freighttax,
  15   SUM(salestaxbase) AS salestaxbase,
  16   SUM(purchasebase) AS purchasebase,
  17   SUM(purchasetaxbase) * -1 AS purchasetaxbase,
  18   SUM(salestaxbase) + SUM(purchasetaxbase) AS nettaxbase,
  19   'curr' AS salesbase_xtnumericrole,
  20   'curr' AS freightbase_xtnumericrole,
  21   'curr' AS salestaxbase_xtnumericrole,
  22   'curr' AS purchasebase_xtnumericrole,
  23   'curr' AS purchasetaxbase_xtnumericrole,
  24   'curr' AS nettaxbase_xtnumericrole,
  25   0 AS salestaxbase_xttotalrole,
  26   0 AS purchasetaxbase_xttotalrole,
  27   0 AS nettaxbase_xttotalrole
  28 FROM (
  29 <? endif ?>
  30 ------------END SUMMARY--------------
  31 <? if exists("showSales") ?>
  32 ------------BEGIN SALES--------------
  33 -- All sales tax history including memos
  34 SELECT
  35   taxhist_distdate, taxhist_journalnumber,
  36   tax_code AS tax, tax_descrip,
  37   COALESCE(taxtype_name,<? value("none") ?>) AS taxtype, taxtype_descrip,
  38   COALESCE(taxclass_code,<? value("none") ?>) AS taxclass, taxclass_descrip,
  39   COALESCE(taxauth_code,<? value("none") ?>) AS taxauth, taxauth_name AS taxauth_descrip,
  40   COALESCE(taxzone_code,<? value("none") ?>) AS taxzone, taxzone_descrip, curr_abbr,
  41   cohist_invcnumber AS docnumber,
  42   <? value("sales") ?> AS source,
  43   CASE
  44     WHEN (cohist_doctype='I') THEN
  45       <? value("invoice") ?>
  46     WHEN (cohist_doctype='C') THEN
  47       <? value("creditmemo") ?>
  48     WHEN (cohist_doctype='D') THEN
  49       <? value("debitmemo") ?>
  50     ELSE
  51       <? value("other") ?>
  52   END AS doctype,
  53   item_number, COALESCE(item_descrip1,cohist_misc_descrip) AS description,
  54   CASE
  55     WHEN (cohist_doctype != 'C') THEN
  56       cohist_ordernumber
  57   END AS ordernumber, cohist_invcdate AS docdate,
  58   cohist_billtoname AS name,
  59   cohist_qtyshipped AS qty,
  60   cohist_unitprice AS unitprice, (cohist_qtyshipped * cohist_unitprice) AS amount,
  61   CASE
  62     WHEN (cohist_misc_type IS NULL OR cohist_misc_type IN ('M','F')) THEN
  63       currToBase(cohist_curr_id, cohist_qtyshipped * cohist_unitprice, cohist_invcdate)
  64     ELSE 0
  65   END AS salesbase,
  66   CASE
  67     WHEN (cohist_misc_type = 'F') THEN
  68       currToBase(cohist_curr_id, cohist_qtyshipped * cohist_unitprice, cohist_invcdate)
  69     ELSE 0
  70   END AS freightbase,
  71   CASE
  72     WHEN (cohist_misc_type = 'F') THEN
  73       taxhist_tax / taxhist_curr_rate
  74     ELSE 0
  75   END AS freighttax,
  76   0 AS purchasebase,
  77   taxhist_tax AS taxlocal,
  78   taxhist_tax / taxhist_curr_rate AS taxbase,
  79   taxhist_tax / taxhist_curr_rate AS salestaxbase,
  80   0 AS purchasetaxbase,
  81   'qty' AS qty_xtnumericrole,
  82   'saleprice' AS unitprice_xtnumericrole,
  83   'extprice' AS amount_xtnumericrole,
  84   'curr' AS taxhist_tax_xtnumericrole,
  85   'curr' AS taxbase_xtnumericrole,
  86    0 AS taxbase_xttotalrole
  87 FROM cohisttax
  88  JOIN cohist ON (cohist_id=taxhist_parent_id)
  89  JOIN taxtype ON (taxtype_id=taxhist_taxtype_id)
  90  JOIN tax ON (tax_id=taxhist_tax_id)
  91  JOIN curr_symbol ON (curr_id=taxhist_curr_id)
  92  LEFT OUTER JOIN taxclass ON (tax_taxclass_id=taxclass_id)
  93  LEFT OUTER JOIN taxauth ON (tax_taxauth_id=taxauth_id)
  94  LEFT OUTER JOIN taxzone ON (cohist_taxzone_id=taxzone_id)
  95  LEFT OUTER JOIN itemsite ON (cohist_itemsite_id=itemsite_id)
  96  LEFT OUTER JOIN item ON (itemsite_item_id=item_id)
  97 <? if exists("distDate") ?>
  98 WHERE ((taxhist_distdate BETWEEN <? value("startDate") ?>
  99                              AND <? value("endDate") ?>)
 100 <? else ?>
 101 WHERE ((taxhist_docdate BETWEEN <? value("startDate") ?>
 102                              AND <? value("endDate") ?>)
 103 <? endif ?>
 104 <? if exists("tax_id") ?>
 105  AND (taxhist_tax_id=<? value("tax_id") ?>)
 106 <? endif ?>
 107 <? if exists("taxtype_id") ?>
 108  AND (taxhist_taxtype_id=<? value("taxtype_id") ?>)
 109 <? endif ?>
 110 <? if exists("taxclass_id") ?>
 111  AND (taxclass_id=<? value("taxclass_id") ?>)
 112 <? endif ?>
 113 <? if exists("taxauth_id") ?>
 114  AND (taxauth_id=<? value("taxauth_id") ?>)
 115 <? endif ?>
 116 <? if exists("taxzone_id") ?>
 117  AND (taxzone_id=<? value("taxzone_id") ?>)
 118 <? endif ?>
 119 )
 120 <? endif ?>
 121 --------------END SALES--------------------
 122 <? if exists("showPurchases") ?>
 123 <? if exists("showSales") ?>
 124 -- Union because sales and purchase shown together
 125 UNION ALL
 126 <? endif ?>
 127 <? endif ?>
 128 ------------BEGIN PURCHASE----------------
 129 <? if exists("showPurchases") ?>
 130 -- A/P Memo history
 131 SELECT
 132   taxhist_distdate, taxhist_journalnumber,
 133   tax_code AS tax, tax_descrip,
 134   COALESCE(taxtype_name,<? value("none") ?>) AS taxtype, taxtype_descrip,
 135   COALESCE(taxclass_code,<? value("none") ?>) AS taxclass, taxclass_descrip,
 136   COALESCE(taxauth_code,<? value("none") ?>) AS taxauth, taxauth_name AS taxauth_descrip,
 137   <? value("none") ?> AS taxzone,'' AS taxzone_descrip, curr_abbr,
 138   apopen_docnumber AS docnumber,
 139   <? value("purchase") ?> AS source,
 140   CASE
 141     WHEN (apopen_doctype='C') THEN
 142       <? value("creditmemo") ?>
 143     WHEN (apopen_doctype='D') THEN
 144       <? value("debitmemo") ?>
 145     ELSE
 146       <? value("other") ?>
 147   END AS doctype,
 148   '' AS item_number, apopen_notes AS description,
 149   apopen_ponumber AS ordernumber, apopen_docdate AS docdate,
 150   vend_name AS name,
 151   1 AS qty,
 152   apopen_amount AS unitprice, apopen_amount AS amount,
 153   0 AS salesbase,
 154   0 AS freightbase,
 155   0 AS freighttax,
 156   apopen_amount / apopen_curr_rate AS purchasebase,
 157   taxhist_tax AS taxlocal,
 158   taxhist_tax / taxhist_curr_rate AS taxbase,
 159   0 AS salestaxbase,
 160   taxhist_tax / taxhist_curr_rate AS purchasetaxbase,
 161   'qty' AS qty_xtnumericrole,
 162   'purchaseprice' AS unitprice_xtnumericrole,
 163   'extprice' AS extension_xtnumericrole,
 164   'curr' AS taxhist_tax_xtnumericrole,
 165   'curr' AS taxbase_xtnumericrole,
 166    0 AS taxbase_xttotalrole
 167 FROM apopentax
 168  JOIN apopen ON (apopen_id=taxhist_parent_id)
 169  JOIN vendinfo ON (apopen_vend_id=vend_id)
 170  JOIN taxtype ON (taxtype_id=taxhist_taxtype_id)
 171  JOIN tax ON (tax_id=taxhist_tax_id)
 172  JOIN curr_symbol ON (curr_id=taxhist_curr_id)
 173  LEFT OUTER JOIN taxclass ON (tax_taxclass_id=taxclass_id)
 174  LEFT OUTER JOIN taxauth ON (tax_taxauth_id=taxauth_id)
 175 <? if exists("distDate") ?>
 176 WHERE ((taxhist_distdate BETWEEN <? value("startDate") ?>
 177                              AND <? value("endDate") ?>)
 178 <? else ?>
 179 WHERE ((taxhist_docdate BETWEEN <? value("startDate") ?>
 180                              AND <? value("endDate") ?>)
 181 <? endif ?>
 182 <? if exists("tax_id") ?>
 183  AND (taxhist_tax_id=<? value("tax_id") ?>)
 184 <? endif ?>
 185 <? if exists("taxtype_id") ?>
 186  AND (taxhist_taxtype_id=<? value("taxtype_id") ?>)
 187 <? endif ?>
 188 <? if exists("taxclass_id") ?>
 189  AND (taxclass_id=<? value("taxclass_id") ?>)
 190 <? endif ?>
 191 <? if exists("taxauth_id") ?>
 192  AND (taxauth_id=<? value("taxauth_id") ?>)
 193 <? endif ?>
 194 <? if exists("taxzone_id") ?>
 195  AND (false)
 196 <? endif ?>
 197  AND (taxhist_distdate IS NOT NULL)
 198 )
 199 UNION ALL
 200 --Voucher Header History
 201 SELECT
 202   taxhist_distdate, taxhist_journalnumber,
 203   tax_code AS tax, tax_descrip,
 204   COALESCE(taxtype_name,<? value("none") ?>) AS taxtype, taxtype_descrip,
 205   COALESCE(taxclass_code,<? value("none") ?>) AS taxclass, taxclass_descrip,
 206   COALESCE(taxauth_code,<? value("none") ?>) AS taxauth, taxauth_name AS taxauth_descrip,
 207   COALESCE(taxzone_code,<? value("none") ?>) AS taxzone, taxzone_descrip, curr_abbr,
 208   vohead_number AS docnumber,
 209   <? value("purchase") ?> AS source,
 210   <? value("voucher") ?>  AS doctype,
 211   '', vohead_notes AS description,
 212   pohead_number AS ordernumber, vohead_docdate AS docdate,
 213   vend_name AS name,
 214   1 AS qty,
 215   0 AS unitprice, 0 AS amount,
 216   0 AS salesbase,
 217   0 AS freightbase,
 218   0 AS freighttax,
 219   0 AS purchasebase,
 220   taxhist_tax AS taxlocal,
 221   taxhist_tax / taxhist_curr_rate AS taxbase,
 222   0 AS salestaxbase,
 223   taxhist_tax / taxhist_curr_rate AS purchasetaxbase,
 224   'qty' AS qty_xtnumericrole,
 225   'purchaseprice' AS unitprice_xtnumericrole,
 226   'extprice' AS amount_xtnumericrole,
 227   'curr' AS taxhist_tax_xtnumericrole,
 228   'curr' AS taxbase_xtnumericrole,
 229    0 AS taxbase_xttotalrole
 230 FROM voheadtax
 231  JOIN vohead ON (vohead_id=taxhist_parent_id)
 232  LEFT OUTER JOIN pohead ON (vohead_pohead_id=pohead_id)
 233  JOIN vendinfo ON (vohead_vend_id=vend_id)
 234  JOIN taxtype ON (taxtype_id=taxhist_taxtype_id)
 235  JOIN tax ON (tax_id=taxhist_tax_id)
 236  JOIN curr_symbol ON (curr_id=taxhist_curr_id)
 237  LEFT OUTER JOIN taxclass ON (tax_taxclass_id=taxclass_id)
 238  LEFT OUTER JOIN taxauth ON (tax_taxauth_id=taxauth_id)
 239  LEFT OUTER JOIN taxzone ON (vohead_taxzone_id=taxzone_id)
 240 <? if exists("distDate") ?>
 241 WHERE ((taxhist_distdate BETWEEN <? value("startDate") ?>
 242                              AND <? value("endDate") ?>)
 243 <? else ?>
 244 WHERE ((taxhist_docdate BETWEEN <? value("startDate") ?>
 245                              AND <? value("endDate") ?>)
 246 <? endif ?>
 247 <? if exists("tax_id") ?>
 248  AND (taxhist_tax_id=<? value("tax_id") ?>)
 249 <? endif ?>
 250 <? if exists("taxtype_id") ?>
 251  AND (taxhist_taxtype_id=<? value("taxtype_id") ?>)
 252 <? endif ?>
 253 <? if exists("taxclass_id") ?>
 254  AND (taxclass_id=<? value("taxclass_id") ?>)
 255 <? endif ?>
 256 <? if exists("taxauth_id") ?>
 257  AND (taxauth_id=<? value("taxauth_id") ?>)
 258 <? endif ?>
 259 <? if exists("taxzone_id") ?>
 260  AND (taxzone_id=<? value("taxzone_id") ?>)
 261 <? endif ?>
 262  AND (taxhist_distdate IS NOT NULL)
 263 )
 264 UNION ALL
 265 --Voucher Line Item
 266 SELECT
 267   taxhist_distdate, taxhist_journalnumber,
 268   tax_code AS tax, tax_descrip,
 269   COALESCE(taxtype_name,<? value("none") ?>) AS taxtype, taxtype_descrip,
 270   COALESCE(taxclass_code,<? value("none") ?>) AS taxclass, taxclass_descrip,
 271   COALESCE(taxauth_code,<? value("none") ?>) AS taxauth, taxauth_name AS taxauth_descrip,
 272   COALESCE(taxzone_code,<? value("none") ?>) AS taxzone, taxzone_descrip, curr_abbr,
 273   vohead_number AS docnumber,
 274   <? value("purchase") ?> AS source,
 275   <? value("voucher") ?>  AS doctype,
 276   COALESCE(item_number,expcat_code), COALESCE(item_descrip1,expcat_descrip) AS description,
 277   pohead_number AS ordernumber, vohead_docdate AS docdate,
 278   vend_name AS name,
 279   voitem_qty AS qty,
 280   COALESCE(SUM(vodist_amount),0)/voitem_qty AS unitprice, COALESCE(SUM(vodist_amount),0) AS amount,
 281   0 AS salesbase,
 282   0 AS freightbase,
 283   0 AS freighttax,
 284   currToBase(vohead_curr_id, COALESCE(SUM(vodist_amount),0), vohead_distdate)  AS purchasebase,
 285   taxhist_tax AS taxlocal,
 286   taxhist_tax / taxhist_curr_rate AS taxbase,
 287   0 AS salestaxbase,
 288   taxhist_tax / taxhist_curr_rate AS purchasetaxbase,
 289   'qty' AS qty_xtnumericrole,
 290   'purchaseprice' AS unitprice_xtnumericrole,
 291   'extprice' AS amount_xtnumericrole,
 292   'curr' AS taxhist_tax_xtnumericrole,
 293   'curr' AS taxbase_xtnumericrole,
 294    0 AS taxbase_xttotalrole
 295 FROM voitemtax
 296  JOIN voitem ON (voitem_id=taxhist_parent_id)
 297  JOIN vohead ON (vohead_id=voitem_vohead_id)
 298  JOIN pohead ON (vohead_pohead_id=pohead_id)
 299  JOIN poitem ON (voitem_poitem_id=poitem_id)
 300  JOIN vodist ON ((vodist_poitem_id=poitem_id)
 301             AND  (vodist_vohead_id=vohead_id))
 302  JOIN vendinfo ON (vohead_vend_id=vend_id)
 303  JOIN taxtype ON (taxtype_id=taxhist_taxtype_id)
 304  JOIN tax ON (tax_id=taxhist_tax_id)
 305  JOIN curr_symbol ON (curr_id=taxhist_curr_id)
 306  LEFT OUTER JOIN taxclass ON (tax_taxclass_id=taxclass_id)
 307  LEFT OUTER JOIN taxauth ON (tax_taxauth_id=taxauth_id)
 308  LEFT OUTER JOIN taxzone ON (vohead_taxzone_id=taxzone_id)
 309  LEFT OUTER JOIN itemsite ON (poitem_itemsite_id=itemsite_id)
 310  LEFT OUTER JOIN item ON (itemsite_item_id=item_id)
 311  LEFT OUTER JOIN expcat ON (expcat_id=poitem_expcat_id)
 312 <? if exists("distDate") ?>
 313 WHERE ((taxhist_distdate BETWEEN <? value("startDate") ?>
 314                              AND <? value("endDate") ?>)
 315 <? else ?>
 316 WHERE ((taxhist_docdate BETWEEN <? value("startDate") ?>
 317                              AND <? value("endDate") ?>)
 318 <? endif ?>
 319 <? if exists("tax_id") ?>
 320  AND (taxhist_tax_id=<? value("tax_id") ?>)
 321 <? endif ?>
 322 <? if exists("taxtype_id") ?>
 323  AND (taxhist_taxtype_id=<? value("taxtype_id") ?>)
 324 <? endif ?>
 325 <? if exists("taxclass_id") ?>
 326  AND (taxclass_id=<? value("taxclass_id") ?>)
 327 <? endif ?>
 328 <? if exists("taxauth_id") ?>
 329  AND (taxauth_id=<? value("taxauth_id") ?>)
 330 <? endif ?>
 331 <? if exists("taxzone_id") ?>
 332  AND (taxzone_id=<? value("taxzone_id") ?>)
 333 <? endif ?>
 334  AND (taxhist_distdate IS NOT NULL)
 335 )
 336 GROUP BY taxhist_id,taxhist_distdate,tax_code,tax_descrip,
 337   taxtype_name,taxtype_descrip,taxclass_code,taxclass_descrip,
 338   taxauth_code,taxauth_descrip,taxzone,taxzone_descrip,curr_abbr,
 339   vohead_number,pohead_number,item_number,item_descrip1,
 340   vohead_curr_id,vohead_distdate,vohead_docdate,vend_name,
 341   expcat_code,expcat_descrip,taxhist_tax,taxhist_curr_rate,
 342   voitem_qty, taxhist_journalnumber
 343 <? endif ?>
 344 -------------END PURCHASE--------------
 345 ORDER BY docdate DESC, docnumber DESC
 346
 347 <? if exists("summary") ?>
 348 ) AS data
 349 GROUP BY <? literal("groupBy") ?>, <? literal("groupBy") ?>_descrip
 350 ORDER BY <? literal("groupBy") ?>
 351 <? endif ?>
 352