git.roojs.org Git - Pman.Xtuple/blob - pgsql/itemprice.sql

   1 -- modified to be 'stable'
   2
   3 -- Function: itemprice(integer, integer, integer, numeric, integer, integer, integer, date, date)
   4
   5 -- DROP FUNCTION itemprice(integer, integer, integer, numeric, integer, integer, integer, date, date);
   6
   7 CREATE OR REPLACE FUNCTION itemprice(integer, integer, integer, numeric, integer, integer, integer, date, date)
   8   RETURNS numeric    AS
   9 $BODY$
  10 DECLARE
  11   pItemid ALIAS FOR $1;
  12   pCustid ALIAS FOR $2;
  13   pShiptoid ALIAS FOR $3;
  14   pQty ALIAS FOR $4;
  15   pQtyUOM ALIAS FOR $5;
  16   pPriceUOM ALIAS FOR $6;
  17   pCurrid ALIAS FOR $7;
  18   pEffective ALIAS FOR $8;
  19   pAsOf ALIAS FOR $9;
  20   _price NUMERIC;
  21   _sales NUMERIC;
  22   _item RECORD;
  23   _iteminvpricerat NUMERIC;
  24   _qty NUMERIC;
  25   _asof DATE;
  26
  27 BEGIN
  28 -- Return the itemPrice in the currency passed in as pCurrid
  29   _qty := itemuomtouom(pItemid, pQtyUOM, NULL, pQty);
  30
  31 -- If no as of passed, use current date
  32   _asof := COALESCE(pAsOf, CURRENT_DATE);
  33
  34 -- Get a value here so we do not have to call the function several times
  35   SELECT itemuomtouomratio(pItemid, pPriceUOM, NULL) AS ratio
  36     INTO _iteminvpricerat;
  37
  38 -- First get a sales price if any so we when we find other prices
  39 -- we can determine if we want that price or this price.
  40 --  Check for a Sale Price
  41   SELECT currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) INTO _sales
  42   FROM (
  43   SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
  44          itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
  45          (ipsitem_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price,
  46          (ipsitem_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
  47     FROM ipsitem
  48    WHERE(ipsitem_item_id=pItemid)
  49    UNION
  50   SELECT ipsprodcat_ipshead_id AS ipsprice_ipshead_id,
  51          ipsprodcat_qtybreak AS ipsprice_qtybreak,
  52          noneg(CAST((item_listprice - (item_listprice * ipsprodcat_discntprcnt) - ipsprodcat_fixedamtdiscount)
  53          AS NUMERIC(16,4))) AS ipsprice_price,
  54          (item_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
  55     FROM ipsprodcat JOIN item ON (ipsprodcat_prodcat_id=item_prodcat_id)
  56    WHERE(item_id=pItemid)  ) AS
  57         ipsprice, ipshead, sale, custinfo
  58   WHERE ( (ipsprice_ipshead_id=ipshead_id)
  59    AND (sale_ipshead_id=ipshead_id)
  60    AND (_asof BETWEEN sale_startdate AND sale_enddate)
  61    AND (ipsprice_qtybreak <= _qty)
  62    AND (cust_id=pCustid) )
  63   ORDER BY uommatched DESC, ipsprice_qtybreak DESC, ipsprice_price ASC
  64   LIMIT 1;
  65
  66 --  Check for a Customer Shipto Price
  67   SELECT currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) INTO _price
  68   FROM (
  69   SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
  70          itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
  71          (ipsitem_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price,
  72          (ipsitem_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
  73     FROM ipsitem
  74    WHERE(ipsitem_item_id=pItemid)
  75    UNION
  76   SELECT ipsprodcat_ipshead_id AS ipsprice_ipshead_id,
  77          ipsprodcat_qtybreak AS ipsprice_qtybreak,
  78          noneg(CAST((item_listprice - (item_listprice * ipsprodcat_discntprcnt) - ipsprodcat_fixedamtdiscount)
  79          AS NUMERIC(16,4))) AS ipsprice_price,
  80          (item_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
  81     FROM ipsprodcat JOIN item ON (ipsprodcat_prodcat_id=item_prodcat_id)
  82    WHERE(item_id=pItemid)  ) AS
  83         ipsprice, ipshead, ipsass
  84   WHERE ( (ipsprice_ipshead_id=ipshead_id)
  85    AND (ipsass_ipshead_id=ipshead_id)
  86    AND (_asof BETWEEN ipshead_effective AND (ipshead_expires - 1))
  87    AND (ipsprice_qtybreak <= _qty)
  88    AND (ipsass_shipto_id != -1)
  89    AND (ipsass_shipto_id=pShiptoid) )
  90   ORDER BY uommatched DESC, ipsprice_qtybreak DESC, ipsprice_price ASC
  91   LIMIT 1;
  92
  93   IF (_price IS NOT NULL) THEN
  94     IF ((_sales IS NOT NULL) AND (_sales < _price)) THEN
  95       RETURN _sales;
  96     END IF;
  97     RETURN _price;
  98   END IF;
  99
 100 --  Check for a Customer Shipto Pattern Price
 101   SELECT currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) INTO _price
 102   FROM (
 103   SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
 104          itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
 105          (ipsitem_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price,
 106          (ipsitem_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
 107     FROM ipsitem
 108    WHERE(ipsitem_item_id=pItemid)
 109    UNION
 110   SELECT ipsprodcat_ipshead_id AS ipsprice_ipshead_id,
 111          ipsprodcat_qtybreak AS ipsprice_qtybreak,
 112          noneg(CAST((item_listprice - (item_listprice * ipsprodcat_discntprcnt) - ipsprodcat_fixedamtdiscount)
 113          AS NUMERIC(16,4))) AS ipsprice_price,
 114          (item_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
 115     FROM ipsprodcat JOIN item ON (ipsprodcat_prodcat_id=item_prodcat_id)
 116    WHERE(item_id=pItemid)  ) AS
 117         ipsprice, ipshead, ipsass, shipto
 118   WHERE ( (ipsprice_ipshead_id=ipshead_id)
 119    AND (ipsass_ipshead_id=ipshead_id)
 120    AND (_asof BETWEEN ipshead_effective AND (ipshead_expires - 1))
 121    AND (ipsprice_qtybreak <= _qty)
 122    AND (COALESCE(length(ipsass_shipto_pattern), 0) > 0)
 123    AND (shipto_num ~ ipsass_shipto_pattern)
 124    AND (ipsass_cust_id=pCustid)
 125    AND (shipto_id=pShiptoid) )
 126   ORDER BY uommatched DESC, ipsprice_qtybreak DESC, ipsprice_price ASC
 127   LIMIT 1;
 128
 129   IF (_price IS NOT NULL) THEN
 130     IF ((_sales IS NOT NULL) AND (_sales < _price)) THEN
 131       RETURN _sales;
 132     END IF;
 133     RETURN _price;
 134   END IF;
 135
 136 --  Check for a Customer Price
 137   SELECT currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) INTO _price
 138   FROM (
 139   SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
 140          itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
 141          (ipsitem_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price,
 142          (ipsitem_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
 143     FROM ipsitem
 144    WHERE(ipsitem_item_id=pItemid)
 145    UNION
 146   SELECT ipsprodcat_ipshead_id AS ipsprice_ipshead_id,
 147          ipsprodcat_qtybreak AS ipsprice_qtybreak,
 148          noneg(CAST((item_listprice - (item_listprice * ipsprodcat_discntprcnt) - ipsprodcat_fixedamtdiscount)
 149          AS NUMERIC(16,4))) AS ipsprice_price,
 150          (item_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
 151     FROM ipsprodcat JOIN item ON (ipsprodcat_prodcat_id=item_prodcat_id)
 152    WHERE(item_id=pItemid)  ) AS
 153         ipsprice, ipshead, ipsass
 154   WHERE ( (ipsprice_ipshead_id=ipshead_id)
 155    AND (ipsass_ipshead_id=ipshead_id)
 156    AND (_asof BETWEEN ipshead_effective AND (ipshead_expires - 1))
 157    AND (ipsprice_qtybreak <= _qty)
 158    AND (COALESCE(length(ipsass_shipto_pattern), 0) = 0)
 159    AND (ipsass_cust_id=pCustid) )
 160   ORDER BY uommatched DESC, ipsprice_qtybreak DESC, ipsprice_price ASC
 161   LIMIT 1;
 162
 163   IF (_price IS NOT NULL) THEN
 164     IF ((_sales IS NOT NULL) AND (_sales < _price)) THEN
 165       RETURN _sales;
 166     END IF;
 167     RETURN _price;
 168   END IF;
 169
 170 --  Check for a Customer Type Price
 171   SELECT currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) INTO _price
 172   FROM (
 173   SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
 174          itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
 175          (ipsitem_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price,
 176          (ipsitem_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
 177     FROM ipsitem
 178    WHERE(ipsitem_item_id=pItemid)
 179    UNION
 180   SELECT ipsprodcat_ipshead_id AS ipsprice_ipshead_id,
 181          ipsprodcat_qtybreak AS ipsprice_qtybreak,
 182          noneg(CAST((item_listprice - (item_listprice * ipsprodcat_discntprcnt) - ipsprodcat_fixedamtdiscount)
 183          AS NUMERIC(16,4))) AS ipsprice_price,
 184          (item_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
 185     FROM ipsprodcat JOIN item ON (ipsprodcat_prodcat_id=item_prodcat_id)
 186    WHERE(item_id=pItemid)  ) AS
 187         ipsprice, ipshead, ipsass, custinfo
 188   WHERE ( (ipsprice_ipshead_id=ipshead_id)
 189    AND (ipsass_ipshead_id=ipshead_id)
 190    AND (ipsass_custtype_id=cust_custtype_id)
 191    AND (_asof BETWEEN ipshead_effective AND (ipshead_expires - 1))
 192    AND (ipsprice_qtybreak <= _qty)
 193    AND (cust_id=pCustid) )
 194   ORDER BY uommatched DESC, ipsprice_qtybreak DESC, ipsprice_price ASC
 195   LIMIT 1;
 196
 197   IF (_price IS NOT NULL) THEN
 198     IF ((_sales IS NOT NULL) AND (_sales < _price)) THEN
 199       RETURN _sales;
 200     END IF;
 201     RETURN _price;
 202   END IF;
 203
 204 --  Check for a Customer Type Pattern Price
 205   SELECT currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) INTO _price
 206   FROM (
 207   SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
 208          itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
 209          (ipsitem_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price,
 210          (ipsitem_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
 211     FROM ipsitem
 212    WHERE(ipsitem_item_id=pItemid)
 213    UNION
 214   SELECT ipsprodcat_ipshead_id AS ipsprice_ipshead_id,
 215          ipsprodcat_qtybreak AS ipsprice_qtybreak,
 216          noneg(CAST((item_listprice - (item_listprice * ipsprodcat_discntprcnt) - ipsprodcat_fixedamtdiscount)
 217          AS NUMERIC(16,4))) AS ipsprice_price,
 218          (item_price_uom_id=COALESCE(pPriceUOM,-1)) AS uommatched
 219     FROM ipsprodcat JOIN item ON (ipsprodcat_prodcat_id=item_prodcat_id)
 220    WHERE(item_id=pItemid)  ) AS
 221         ipsprice, ipshead, ipsass, custtype, custinfo
 222   WHERE ( (ipsprice_ipshead_id=ipshead_id)
 223    AND (ipsass_ipshead_id=ipshead_id)
 224    AND (coalesce(length(ipsass_custtype_pattern), 0) > 0)
 225    AND (custtype_code ~ ipsass_custtype_pattern)
 226    AND (cust_custtype_id=custtype_id)
 227    AND (_asof BETWEEN ipshead_effective AND (ipshead_expires - 1))
 228    AND (ipsprice_qtybreak <= _qty)
 229    AND (cust_id=pCustid) )
 230   ORDER BY uommatched DESC, ipsprice_qtybreak DESC, ipsprice_price ASC
 231   LIMIT 1;
 232
 233   IF (_price IS NOT NULL) THEN
 234     IF ((_sales IS NOT NULL) AND (_sales < _price)) THEN
 235       RETURN _sales;
 236     END IF;
 237     RETURN _price;
 238   END IF;
 239
 240 -- If we have not found another price yet and we have a
 241 -- sales price we will use that.
 242   IF (_sales IS NOT NULL) THEN
 243     RETURN _sales;
 244   END IF;
 245
 246 --  Check for a list price
 247   SELECT MIN(currToLocal(pCurrid,
 248                        item_listprice - (item_listprice * COALESCE(cust_discntprcnt, 0)),
 249                        pEffective) * _iteminvpricerat) AS price,
 250          item_exclusive INTO _item
 251   FROM item LEFT OUTER JOIN custinfo ON (cust_id=pCustid)
 252   WHERE (item_id=pItemid)
 253   GROUP BY item_exclusive;
 254   IF (FOUND) THEN
 255     IF (NOT _item.item_exclusive) THEN
 256       IF (_item.price < 0) THEN
 257         RETURN 0;
 258       ELSE
 259         RETURN _item.price;
 260       END IF;
 261     ELSE
 262       RETURN -9999;
 263     END IF;
 264   ELSE
 265     RETURN -9999;
 266   END IF;
 267
 268 END;
 269 $BODY$
 270   LANGUAGE plpgsql STABLE
 271   COST 100;
 272 ALTER FUNCTION itemprice(integer, integer, integer, numeric, integer, integer, integer, date, date)
 273   OWNER TO admin;