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

   1 -- Group: pricelist
   2 -- Name:  detail
   3 -- Notes: This query displays price lists and the data used to perform
   4 --        price calculations. It avoids price calculations where possible.
   5 --        The UNION sets the sourcecode column to show how the price list entry
   6 --        was derived:  customer vs customer type vs customer type regex ..., etc.
   7 --        Then the outer query modifies the sourcecode to indicate
   8 --        item vs product category.
   9 --        TODO: possible improvements include removing OUTER JOINs with uom table
  10 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
  11 -- See www.xtuple.com/CPAL for the full text of the software license.
  12
  13 SELECT sourcecode + CASE WHEN source='P' THEN 10 ELSE 0 END AS source,
  14        sourceid, schedulename, type,
  15        invqty AS qty_break,
  16        CASE WHEN (qtybreak = -1) THEN <? value('na') ?>
  17        END             AS qty_break_qtdisplayrole,
  18        invuom.uom_name AS qty_uom,
  19        price,
  20        currConcat(curr_id) AS currency,
  21        priceuom.uom_name   AS price_uom,
  22        currToCurr(curr_id, <? value('curr_id') ?>,
  23                   price,   <? value('effective') ?>) AS base_price,
  24        discountpercent,
  25        discountfixed,
  26        CASE WHEN (price_type='N') THEN <? value('nominal') ?>
  27             WHEN (price_type='D') THEN <? value('discount') ?>
  28             WHEN (price_type='M') THEN <? value('markup') ?>
  29        END AS price_type,
  30        'qty'        AS qty_break_xtnumericrole,
  31        'salesprice' AS price_xtnumericrole,
  32        'salesprice' AS base_price_xtnumericrole,
  33        'percent'    AS discountpercent_xtnumericrole,
  34        'salesprice' AS discountfixed_xtnumericrole
  35 FROM (
  36
  37     SELECT 4 AS sourcecode,
  38            ipsitem_id AS sourceid,
  39            CASE WHEN (ipsitem_item_id=<? value('item_id') ?>) THEN 'I'
  40                 WHEN (ipsitem_prodcat_id=<? value('prodcat_id') ?>) THEN 'P'
  41                 ELSE 'Error'
  42            END AS source,
  43            ipshead_id,
  44            ipshead_name AS schedulename,
  45            (<? value('sale') ?> || '-' || sale_name) AS type,
  46            CASE WHEN (ipsitem_item_id=<? value('item_id') ?>) THEN itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak)
  47                 ELSE ipsitem_qtybreak
  48            END AS invqty,
  49            ipsitem_qtybreak AS qtybreak,
  50            CASE WHEN (ipsitem_type = 'N') THEN
  51                  (ipsitem_price * itemuomtouomratio(<? value('item_id') ?>, NULL, ipsitem_price_uom_id)) * iteminvpricerat(ipsitem_item_id)
  52                 WHEN (ipsitem_type = 'D') THEN
  53                  noNeg(<? value('item_listprice') ?> - (<? value('item_listprice') ?> * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount)
  54                 WHEN ((ipsitem_type = 'M') AND fetchMetricBool('Long30Markups')) THEN
  55                  (<? value('item_unitcost') ?> / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
  56                 WHEN (ipsitem_type = 'M') THEN
  57                  (<? value('item_unitcost') ?> + (<? value('item_unitcost') ?> * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
  58                 ELSE 0.00
  59            END AS price,
  60            ipsitem_qty_uom_id AS qtybreak_uom_id,
  61            ipsitem_price_uom_id AS price_uom_id,
  62            ipsitem_discntprcnt AS discountpercent,
  63            ipsitem_fixedamtdiscount AS discountfixed,
  64            ipshead_curr_id AS curr_id,
  65            ipsitem_type AS price_type
  66     FROM sale JOIN ipshead ON (ipshead_id=sale_ipshead_id)
  67               JOIN ipsiteminfo ON (ipsitem_ipshead_id=ipshead_id)
  68               JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  69    WHERE ((ipsitem_item_id=<? value('item_id') ?>) OR (ipsitem_prodcat_id=<? value('prodcat_id') ?>))
  70       AND (<? value('asof') ?> BETWEEN sale_startdate AND sale_enddate)
  71       AND ( ((COALESCE(ipsass_shipto_id, -1) > 0) AND (ipsass_shipto_id=<? value('shipto_id') ?>))
  72        OR   ((COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) AND (<? value('shipto_num') ?> ~ ipsass_shipto_pattern))
  73        OR   (ipsass_cust_id=<? value('cust_id') ?>)
  74        OR   (ipsass_custtype_id=<? value('custtype_id') ?>)
  75        OR   ((COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0) AND (<? value('custtype_code') ?> ~ ipsass_custtype_pattern)) )
  76
  77     UNION
  78
  79     SELECT CASE WHEN (COALESCE(ipsass_shipto_id, -1) > 0) THEN 6
  80                 WHEN (COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) THEN 7
  81                 WHEN (COALESCE(ipsass_cust_id, -1) > 0) THEN 1
  82                 WHEN (COALESCE(ipsass_custtype_id, -1) > 0) THEN 2
  83                 WHEN (COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0) THEN 3
  84                 ELSE 99
  85            END AS sourcecode,
  86            ipsitem_id AS sourceid,
  87            CASE WHEN (ipsitem_item_id=<? value('item_id') ?>) THEN 'I'
  88                 WHEN (ipsitem_prodcat_id=<? value('prodcat_id') ?>) THEN 'P'
  89                 ELSE 'Error'
  90            END AS source,
  91            ipshead_id,
  92            ipshead_name AS schedulename,
  93            CASE WHEN (COALESCE(ipsass_shipto_id, -1) > 0) THEN <? value('shipTo') ?>
  94                 WHEN (COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) THEN <? value('shipToPattern') ?>
  95                 WHEN (COALESCE(ipsass_cust_id, -1) > 0) THEN <? value('customer') ?>
  96                 WHEN (COALESCE(ipsass_custtype_id, -1) > 0) THEN <? value('custType') ?>
  97                 WHEN (COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0) THEN <? value('custTypePattern') ?>
  98                 ELSE 'Error'
  99            END AS type,
 100            CASE WHEN (ipsitem_item_id=<? value('item_id') ?>) THEN itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak)
 101                 ELSE ipsitem_qtybreak
 102            END AS invqty,
 103            ipsitem_qtybreak AS qtybreak,
 104            CASE WHEN ipsitem_type = 'N' THEN (ipsitem_price * itemuomtouomratio(<? value('item_id') ?>, NULL, ipsitem_price_uom_id)) *
 105                                               iteminvpricerat(ipsitem_item_id)
 106                 WHEN ipsitem_type = 'D' THEN noNeg(<? value('item_listprice') ?> - (<? value('item_listprice') ?> * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount)
 107                 WHEN ((ipsitem_type = 'M') AND fetchMetricBool('Long30Markups')) THEN
 108                  (<? value('item_unitcost') ?> / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
 109                 WHEN (ipsitem_type = 'M') THEN
 110                  (<? value('item_unitcost') ?> + (<? value('item_unitcost') ?> * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
 111                 ELSE 0.00
 112            END AS price,
 113            ipsitem_qty_uom_id AS qtybreak_uom_id,
 114            ipsitem_price_uom_id AS price_uom_id,
 115            ipsitem_discntprcnt AS discountpercent,
 116            ipsitem_fixedamtdiscount AS discountfixed,
 117            ipshead_curr_id AS curr_id,
 118            ipsitem_type AS price_type
 119     FROM ipsass JOIN ipshead ON (ipshead_id=ipsass_ipshead_id)
 120                 JOIN ipsiteminfo ON (ipsitem_ipshead_id=ipshead_id)
 121     WHERE ((ipsitem_item_id=<? value('item_id') ?>) OR (ipsitem_prodcat_id=<? value('prodcat_id') ?>))
 122       AND (<? value('asof') ?> BETWEEN ipshead_effective AND ipshead_expires)
 123       AND ( ((COALESCE(ipsass_shipto_id, -1) > 0) AND (ipsass_shipto_id=<? value('shipto_id') ?>))
 124        OR   ((COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) AND (<? value('shipto_num') ?> ~ ipsass_shipto_pattern))
 125        OR   (ipsass_cust_id=<? value('cust_id') ?>)
 126        OR   (ipsass_custtype_id=<? value('custtype_id') ?>)
 127        OR   ((COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0) AND (<? value('custtype_code') ?> ~ ipsass_custtype_pattern))
 128           )
 129
 130     UNION
 131
 132     SELECT 5 AS sourcecode,
 133            item_id AS sourceid,
 134            NULL AS source,
 135            NULL AS ipshead_id,
 136            '' AS schedulename,
 137            <? value('listPrice') ?> AS type,
 138            -1 AS invqty,
 139            -1 AS qtybreak,
 140            (item_listprice - (item_listprice * cust_discntprcnt)) AS price,
 141            item_inv_uom_id   AS qtybreak_uom_id,
 142            item_price_uom_id AS price_uom_id,
 143            cust_discntprcnt  AS discountpercent,
 144            NULL              AS discountfixed,
 145            baseCurrId() AS curr_id,
 146            NULL AS price_type
 147     FROM item, custinfo
 148     WHERE (item_sold
 149       AND (NOT item_exclusive)
 150        AND (item_id=<? value('item_id') ?>)
 151        AND (cust_id=<? value('cust_id') ?>) ) ) AS data
 152
 153   LEFT OUTER JOIN uom AS invuom ON (invuom.uom_id=qtybreak_uom_id)
 154   LEFT OUTER JOIN uom AS priceuom ON (priceuom.uom_id=price_uom_id)
 155
 156  ORDER BY price_uom_id, price;