git.roojs.org Git - Pman.Xtuple/blob - metasql/salesHistory.stockist.sql

   1
   2 -- Group: salesHistory
   3 -- Name: detail
   4 -- Notes:
   5 -- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple.
   6 -- See www.xtuple.com/CPAL for the full text of the software license.
   7
   8
   9
  10 -- JSON START
  11 -- {
  12
  13 -- 'cols' : [
  14
  15 --  { 'header' : 'Brand' ,  'dataIndex': 'item_brand' },
  16 --  { 'header' : 'Customer Name' ,  'dataIndex': 'cust_name' },
  17 --  { 'header' : 'Country' ,  'dataIndex': 'addr_country' },
  18 --  { 'header' : 'Post Code' ,  'dataIndex': 'addr_postalcode' },
  19 --  { 'header' : 'State' ,  'dataIndex': 'addr_state' },
  20 --  { 'header' : 'City' ,  'dataIndex': 'addr_city' },
  21 --  { 'header' : 'Qty' ,  'dataIndex': 'total_shipped' }
  22
  23
  24
  25
  26
  27
  28 -- ]
  29 -- }
  30 -- JSON END
  31
  32 SELECT
  33           item_brand,
  34           cust_name,
  35           addr_postalcode,
  36           addr_state,
  37           addr_city,
  38           addr_country,
  39
  40           SUM(cohist_qtyshipped) as total_shipped
  41
  42
  43
  44 FROM (
  45
  46         SELECT
  47           cust_id,
  48           cust_name,
  49           addr_country,
  50           cohist_ordernumber,
  51           cohead_orderdate,
  52           cohist_invcdate,
  53           to_char(cohist_invcdate, 'Mon YYYY') as invmonth,
  54           cohist_invcnumber,
  55           itemcharvalue(item_id, 'BRAND') as item_brand,
  56           COALESCE(item_number, cohist_misc_descrip) AS item_number,
  57           item_descrip1,
  58           cohist_qtyshipped,
  59           currConcat(cohist_curr_id) AS currAbbr,
  60           CASE WHEN cohist_unitcost = 0 THEN
  61             0
  62           ELSE
  63             cohist_unitprice
  64           END AS cohist_unitprice,
  65
  66           CASE WHEN cohist_unitcost = 0 THEN
  67             0
  68           ELSE
  69             round((cohist_qtyshipped * cohist_unitprice), 2)
  70           END AS extprice,
  71
  72           CASE WHEN cohist_unitcost = 0 THEN
  73             0
  74           ELSE
  75             currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)
  76           END AS baseunitprice,
  77
  78           CASE WHEN cohist_unitcost = 0 THEN
  79             0
  80           ELSE
  81             cohist_qtyshipped  * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)
  82           END AS extpricebase,
  83
  84
  85             (SELECT invfifo_landedunitcost * cohist_qtyshipped FROM invdetailview where
  86                     invfifo_void = 0
  87                 AND
  88                     invfifo_cohead_id = cohead_id
  89                 AND
  90                     invhist_itemsite_id = cohist_itemsite_id
  91                 AND
  92                     invhist_ordtype = 'SO'
  93                 AND
  94                     invfifo_cohead_id = cohead_id
  95             LIMIT 1) as basefifovalue,
  96
  97
  98
  99             0 as coitem_linenumber,
 100             0 as coitem_subnumber,
 101
 102             addr_postalcode,
 103           addr_state,
 104           addr_city,
 105           order_rep.salesrep_name AS order_salesrep_name,
 106           cust_rep.salesrep_name AS cust_salesrep_name
 107
 108
 109
 110
 111         FROM
 112             cohist
 113         LEFT JOIN
 114             cohead ON (cohead_number=cohist_ordernumber)
 115
 116
 117
 118         JOIN
 119             custinfo ON (cust_id=cohist_cust_id)
 120         LEFT
 121             JOIN cntct ON  cust_cntct_id = cntct_id
 122         LEFT
 123             JOIN addr  ON addr_id = cntct_addr_id
 124         LEFT
 125             JOIN invchead ON invchead_invcnumber = cohist_invcnumber
 126
 127         LEFT
 128             JOIN salesrep order_rep ON (order_rep.salesrep_id=cohist_salesrep_id)
 129         LEFT
 130             JOIN salesrep cust_rep ON (cust_rep.salesrep_id=cust_salesrep_id)
 131
 132     <? if exists("includeMisc") ?>
 133                   LEFT OUTER JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
 134                   LEFT OUTER JOIN site() ON (warehous_id=itemsite_warehous_id)
 135                   LEFT OUTER JOIN item ON (item_id=itemsite_item_id)
 136     <? else ?>
 137                  LEFT JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
 138                  LEFT JOIN site() ON (warehous_id=itemsite_warehous_id)
 139                  LEFT JOIN item ON (item_id=itemsite_item_id)
 140     <? endif ?>
 141
 142     <? if exists("shipzone_id") ?>
 143                 LEFT  JOIN shiptoinfo ON (shipto_id=cohist_shipto_id)
 144                 LEFT  JOIN shipzone ON (shipzone_id=shipto_shipzone_id)
 145     <? endif ?>
 146
 147
 148        --           JOIN coitem ON (coitem_cohead_id = cohead_id AND coitem_itemsite_id = cohist_itemsite_id
 149       --                              AND cohist_pos(cohist_id, cohist_ordernumber, cohist_itemsite_id) = coitem_pos(cohead_id, coitem_id, coitem_itemsite_id))
 150
 151
 152     WHERE ( (true)
 153         -- make sure the invoices are not void..
 154         AND invchead_void != true
 155
 156
 157     <? if exists("includeMisc") ?>
 158       AND  (COALESCE(cohist_misc_type, '') <> 'F')
 159       AND  (COALESCE(cohist_misc_type, '') <> 'T')
 160     <? endif ?>
 161
 162     <? if exists("item_id") ?>
 163       AND  (item_id=<? value("item_id") ?>)
 164     <? endif ?>
 165
 166     <? if exists("cohist_salesrep_id") ?>
 167       AND  (cohist_salesrep_id=<? value("cohist_salesrep_id") ?>)
 168     <? endif ?>
 169
 170     <? if exists("cust_salesrep_id") ?>
 171       AND  (cust_salesrep_id=<? value("cust_salesrep_id") ?>)
 172     <? endif ?>
 173
 174     <? if exists("addr_postalcode") ?>
 175       AND  (addr_postalcode=<? value("addr_postalcode") ?>)
 176     <? endif ?>
 177
 178     <? if exists("startDate") ?>
 179       AND  (cohist_invcdate >= <? value("startDate") ?>)
 180     <? endif ?>
 181
 182     <? if exists("endDate") ?>
 183       AND  (cohist_invcdate <= <? value("endDate") ?>)
 184     <? endif ?>
 185     <? if exists("shipStartDate") ?>
 186       AND  (cohist_shipdate >= <? value("shipStartDate") ?>)
 187     <? endif ?>
 188     <? if exists("shipEndDate") ?>
 189       AND  (cohist_shipdate <= <? value("shipEndDate") ?>)
 190     <? endif ?>
 191     <? if exists("shipto_id") ?>
 192       AND  (cohist_shipto_id=<? value("shipto_id") ?>)
 193     <? endif ?>
 194     <? if exists("billToName") ?>
 195       AND  (UPPER(cohist_billtoname) ~ UPPER(<? value("billToName") ?>))
 196     <? endif ?>
 197     <? if exists("cust_id") ?>
 198       AND  (cohist_cust_id=<? value("cust_id") ?>)
 199     <? endif ?>
 200     <? if exists("custtype_id") ?>
 201       AND  (cust_custtype_id=<? value("custtype_id") ?>)
 202     <? elseif exists("custtype_pattern") ?>
 203       AND  (cust_custtype_id IN (SELECT DISTINCT custtype_id
 204                                  FROM custtype
 205                                  WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
 206     <? endif ?>
 207     <? if exists("by_custgrp") ?>
 208       AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 209                        FROM custgrpitem))
 210     <? endif ?>
 211     <? if exists("custgrp_id") ?>
 212       AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 213                        FROM custgrpitem
 214                        WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
 215     <? endif ?>
 216     <? if exists("custgrp_pattern") ?>
 217       AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 218                        FROM custgrp, custgrpitem
 219                        WHERE ( (custgrpitem_custgrp_id=custgrp_id)
 220                          AND   (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
 221     <? endif ?>
 222
 223     <? if exists("item_id") ?>
 224       AND  (itemsite_item_id=<? value("item_id") ?>)
 225     <? endif ?>
 226     <? if exists("prodcat_id") ?>
 227       AND (item_prodcat_id=<? value("prodcat_id") ?>)
 228     <? endif ?>
 229     <? if exists("prodcat_pattern") ?>
 230       AND (item_prodcat_id IN (SELECT DISTINCT prodcat_id
 231                                FROM prodcat
 232                                WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
 233     <? endif ?>
 234
 235     <? if exists("warehous_id") ?>
 236       AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 237     <? endif ?>
 238     <? if exists("shipzone_id") ?>
 239       AND (shipzone_id=<? value("shipzone_id") ?>)
 240     <? endif ?>
 241     <? if exists("cohead_id") ?>
 242       AND (cohead_id=<? value("cohead_id") ?>)
 243     <? endif ?>
 244
 245     <? if exists("addr_country") ?>
 246       AND (addr_country=<? value("addr_country") ?>)
 247     <? endif ?>
 248           )
 249
 250     UNION ALL
 251
 252         SELECT
 253           cust_id,
 254           cust_name,
 255           addr_country,
 256           cohead_number AS cohist_ordernumber,
 257           cohead_orderdate,
 258           coitem_scheddate AS cohist_invcdate,
 259           to_char(coitem_scheddate, 'Mon YYYY') as invmonth,
 260 --           (SELECT invchead_invcnumber FROM invchead WHERE invchead_ordernumber = cohead_number) AS cohist_invcnumber,
 261           '' AS cohist_invcnumber,
 262           itemcharvalue(item_id, 'BRAND') as item_brand,
 263           item_number,
 264           item_descrip1,
 265           coitem_qtyshipped AS cohist_qtyshipped,
 266           currConcat(cohead_curr_id) AS currAbbr,
 267
 268           CASE WHEN (SELECT SUM(coitem_qtyshipped * coitem_unitcost)
 269                     FROM coitem WHERE coitem_cohead_id = cohead_id AND coitem_linenumber = join_coitem.coitem_linenumber) = 0 THEN
 270                 0
 271           ELSE
 272
 273             ((coitem_qtyshipped * coitem_unitcost) * ((coitem_qtyshipped * coitem_unitcost) /
 274             (SELECT SUM(coitem_qtyshipped * coitem_unitcost) FROM coitem WHERE
 275                     coitem_cohead_id = cohead_id AND coitem_linenumber = join_coitem.coitem_linenumber)))
 276           END AS cohist_unitprice,
 277
 278           CASE WHEN (SELECT SUM(coitem_qtyshipped * coitem_unitcost) FROM coitem WHERE coitem_cohead_id = cohead_id AND coitem_linenumber = join_coitem.coitem_linenumber) = 0 THEN
 279                 0
 280           ELSE
 281             round((coitem_qtyshipped *
 282             ((coitem_qtyshipped * coitem_unitcost) * ((coitem_qtyshipped * coitem_unitcost) /
 283             (SELECT SUM(coitem_qtyshipped * coitem_unitcost) FROM coitem WHERE coitem_cohead_id = cohead_id AND coitem_linenumber = join_coitem.coitem_linenumber)))
 284             ), 2)
 285           END AS extprice,
 286
 287           CASE WHEN (SELECT SUM(coitem_qtyshipped * coitem_unitcost) FROM coitem WHERE coitem_cohead_id = cohead_id AND coitem_linenumber = join_coitem.coitem_linenumber) = 0 THEN
 288                 0
 289           ELSE
 290             currtobase(cohead_curr_id, ((coitem_qtyshipped * coitem_unitcost) * ((coitem_qtyshipped * coitem_unitcost) /
 291             (SELECT SUM(coitem_qtyshipped * coitem_unitcost) FROM coitem WHERE coitem_cohead_id = cohead_id AND coitem_linenumber = join_coitem.coitem_linenumber)))
 292             , coitem_scheddate)
 293           END AS baseunitprice,
 294
 295
 296           CASE WHEN (SELECT SUM(coitem_qtyshipped * coitem_unitcost) FROM coitem WHERE coitem_cohead_id = cohead_id AND coitem_linenumber = join_coitem.coitem_linenumber) = 0 THEN
 297             0
 298           ELSE
 299             cohist_qtyshipped  *  (currtobase(cohead_curr_id, ((coitem_qtyshipped * coitem_unitcost) * ((coitem_qtyshipped * coitem_unitcost) /
 300             (SELECT SUM(coitem_qtyshipped * coitem_unitcost) FROM coitem WHERE coitem_cohead_id = cohead_id AND coitem_linenumber = join_coitem.coitem_linenumber)))
 301             , coitem_scheddate))
 302           END AS extpricebase,
 303
 304
 305             (SELECT  invfifo_landedunitcost * cohist_qtyshipped FROM invdetailview where
 306                     invfifo_void = 0
 307                 AND
 308                     invfifo_cohead_id = cohead_id
 309                     --invhist_ordnumber like  cohead_number || '-%'
 310                 AND
 311                     invhist_itemsite_id = cohist_itemsite_id
 312                 AND
 313                     invhist_ordtype = 'SO'
 314                 AND
 315                     invfifo_cohead_id = cohead_id
 316             LIMIT 1) as basefifovalue,
 317
 318         coitem_linenumber,
 319         coitem_subnumber,
 320
 321         addr_postalcode,
 322           addr_state,
 323           addr_city,
 324           order_rep.salesrep_name AS order_salesrep_name,
 325           cust_rep.salesrep_name AS cust_salesrep_name
 326
 327
 328
 329
 330
 331
 332     FROM cohist
 333         LEFT JOIN invchead ON invchead_invcnumber = cohist_invcnumber
 334         JOIN
 335             cohead ON (cohead_number=cohist_ordernumber)
 336         JOIN
 337             coitem AS join_coitem ON (
 338                 coitem_cohead_id = cohead_id
 339             AND
 340                 coitem_subnumber != 0
 341             AND
 342                 coitem_linenumber =
 343                 (SELECT
 344                     coitem_linenumber
 345                 FROM
 346                     coitem
 347                 WHERE
 348                     (SELECT item_type FROM item LEFT JOIN itemsite ON itemsite_item_id = item_id WHERE itemsite_id = cohist_itemsite_id) = 'K'
 349                 AND
 350                     coitem_cohead_id = cohead_id
 351                 AND
 352                     cohist_itemsite_id = coitem_itemsite_id
 353                 AND
 354                     cohist_pos(cohist_id, cohist_ordernumber, cohist_itemsite_id) = coitem_pos(cohead_id, coitem_id, coitem_itemsite_id)
 355                 )
 356             )
 357
 358         JOIN
 359             custinfo ON (cust_id=cohist_cust_id)
 360         LEFT JOIN
 361             cntct ON  cust_cntct_id = cntct_id
 362         LEFT JOIN
 363             addr  ON addr_id = cntct_addr_id
 364
 365         LEFT
 366             JOIN salesrep order_rep ON (order_rep.salesrep_id=cohist_salesrep_id)
 367         LEFT
 368             JOIN salesrep cust_rep ON (cust_rep.salesrep_id=cust_salesrep_id)
 369
 370
 371     <? if exists("includeMisc") ?>
 372                   LEFT OUTER JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
 373                   LEFT OUTER JOIN site() ON (warehous_id=itemsite_warehous_id)
 374                   LEFT OUTER JOIN item ON (item_id=itemsite_item_id)
 375     <? else ?>
 376                   JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
 377                   JOIN site() ON (warehous_id=itemsite_warehous_id)
 378                   JOIN item ON (item_id=itemsite_item_id)
 379     <? endif ?>
 380     <? if exists("shipzone_id") ?>
 381                   JOIN shiptoinfo ON (shipto_id=cohist_shipto_id)
 382                   JOIN shipzone ON (shipzone_id=shipto_shipzone_id)
 383     <? endif ?>
 384
 385     WHERE ( (true)
 386       AND cohist_unitcost = 0
 387       -- make sure the invoices are not void..
 388     AND invchead_void != true
 389
 390
 391
 392     <? if exists("includeMisc") ?>
 393       AND  (COALESCE(cohist_misc_type, '') <> 'F')
 394       AND  (COALESCE(cohist_misc_type, '') <> 'T')
 395     <? endif ?>
 396
 397     <? if exists("item_id") ?>
 398       AND  (item_id=<? value("item_id") ?>)
 399     <? endif ?>
 400
 401     <? if exists("cohist_salesrep_id") ?>
 402       AND  (cohist_salesrep_id=<? value("cohist_salesrep_id") ?>)
 403     <? endif ?>
 404
 405     <? if exists("cust_salesrep_id") ?>
 406       AND  (cust_salesrep_id=<? value("cust_salesrep_id") ?>)
 407     <? endif ?>
 408
 409     <? if exists("addr_postalcode") ?>
 410       AND  (addr_postalcode=<? value("addr_postalcode") ?>)
 411     <? endif ?>
 412
 413     <? if exists("startDate") ?>
 414       AND  (cohist_invcdate >= <? value("startDate") ?>)
 415     <? endif ?>
 416
 417     <? if exists("endDate") ?>
 418       AND  (cohist_invcdate <= <? value("endDate") ?>)
 419     <? endif ?>
 420     <? if exists("shipStartDate") ?>
 421       AND  (cohist_shipdate >= <? value("shipStartDate") ?>)
 422     <? endif ?>
 423     <? if exists("shipEndDate") ?>
 424       AND  (cohist_shipdate <= <? value("shipEndDate") ?>)
 425     <? endif ?>
 426     <? if exists("shipto_id") ?>
 427       AND  (cohist_shipto_id=<? value("shipto_id") ?>)
 428     <? endif ?>
 429     <? if exists("billToName") ?>
 430       AND  (UPPER(cohist_billtoname) ~ UPPER(<? value("billToName") ?>))
 431     <? endif ?>
 432     <? if exists("cust_id") ?>
 433       AND  (cohist_cust_id=<? value("cust_id") ?>)
 434     <? endif ?>
 435     <? if exists("custtype_id") ?>
 436       AND  (cust_custtype_id=<? value("custtype_id") ?>)
 437     <? elseif exists("custtype_pattern") ?>
 438       AND  (cust_custtype_id IN (SELECT DISTINCT custtype_id
 439                                  FROM custtype
 440                                  WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
 441     <? endif ?>
 442     <? if exists("by_custgrp") ?>
 443       AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 444                        FROM custgrpitem))
 445     <? endif ?>
 446     <? if exists("custgrp_id") ?>
 447       AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 448                        FROM custgrpitem
 449                        WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
 450     <? endif ?>
 451     <? if exists("custgrp_pattern") ?>
 452       AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
 453                        FROM custgrp, custgrpitem
 454                        WHERE ( (custgrpitem_custgrp_id=custgrp_id)
 455                          AND   (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
 456     <? endif ?>
 457
 458     <? if exists("item_id") ?>
 459       AND  (itemsite_item_id=<? value("item_id") ?>)
 460     <? endif ?>
 461     <? if exists("prodcat_id") ?>
 462       AND (item_prodcat_id=<? value("prodcat_id") ?>)
 463     <? endif ?>
 464     <? if exists("prodcat_pattern") ?>
 465       AND (item_prodcat_id IN (SELECT DISTINCT prodcat_id
 466                                FROM prodcat
 467                                WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
 468     <? endif ?>
 469
 470     <? if exists("warehous_id") ?>
 471       AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 472     <? endif ?>
 473     <? if exists("shipzone_id") ?>
 474       AND (shipzone_id=<? value("shipzone_id") ?>)
 475     <? endif ?>
 476     <? if exists("cohead_id") ?>
 477       AND (cohead_id=<? value("cohead_id") ?>)
 478     <? endif ?>
 479
 480     <? if exists("addr_country") ?>
 481       AND (addr_country=<? value("addr_country") ?>)
 482     <? endif ?>
 483           )
 484 ) AS data
 485 GROUP BY
 486
 487  cust_name,
 488           addr_postalcode,
 489           addr_state,
 490           addr_city,
 491           addr_country,
 492
 493           item_brand
 494 ORDER BY
 495     item_brand,
 496     cust_name,
 497           addr_postalcode,
 498           addr_state,
 499           addr_city,
 500           addr_country,
 501
 502           item_brand
 503