git.roojs.org Git - xtuple/blob - foundation-database/public/tables/metasql/inventoryAvailability-byCustOrSO.mql

   1 -- Group: inventoryAvailability
   2 -- Name:  byCustOrSO
   3 -- Notes: query to get item availability by either customer id/type/pattern
   4 --        or by cohead_id
   5 --        Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   6 --        See www.xtuple.com/CPAL for the full text of the software license.
   7
   8 SELECT id, altid, cohead_id,
   9        item_number, descrip, uom_name,
  10        qoh, sobalance, allocated, ordered,
  11        atshipping,
  12        CASE WHEN (xtindentrole=1) THEN
  13 <? if exists("useReservationNetting") ?>
  14                   coitem_qtyreserved
  15 <? else ?>
  16                   (qoh + ordered - sobalance)
  17 <? endif ?>
  18             WHEN (xtindentrole=2) THEN (qoh + ordered - sobalance)
  19        END AS orderavail,
  20        (qoh + ordered - allocated) AS totalavail,
  21        orderdate, duedate,
  22        reorderlevel,
  23 <? if exists("sohead_id") ?> (xtindentrole - 1) AS <? endif ?> xtindentrole,
  24        'qty' AS qoh_xtnumericrole,
  25        'qty' AS sobalance_xtnumericrole,
  26        'qty' AS allocated_xtnumericrole,
  27        'qty' AS ordered_xtnumericrole,
  28        'qty' AS orderavail_xtnumericrole,
  29        'qty' AS totalavail_xtnumericrole,
  30        'qty' AS atshipping_xtnumericrole,
  31        CASE WHEN (pack_id IS NOT NULL) THEN 'altemphasis'
  32        END AS qtforegroundrole,
  33        CASE WHEN (qoh < 0) THEN 'error'
  34             WHEN (qoh < reorderlevel) THEN 'warning'
  35        END AS qoh_qtforegroundrole,
  36        CASE WHEN (
  37 <? if exists("useReservationNetting") ?>
  38                   coitem_qtyreserved < 0
  39 <? else ?>
  40                   (qoh + ordered - sobalance) < 0
  41 <? endif ?>
  42                  ) THEN 'error'
  43             WHEN ((qoh + ordered - sobalance) <= reorderlevel) THEN 'warning'
  44        END AS orderavail_qtforegroundrole,
  45        CASE WHEN ((qoh + ordered - allocated) < 0) THEN 'error'
  46             WHEN ((qoh + ordered - allocated) <= reorderlevel) THEN 'warning'
  47        END AS totalavail_qtforegroundrole,
  48        CASE WHEN (xtindentrole = 2 AND (orderdate <= CURRENT_DATE)
  49               AND (descrip IN ('O','E','S','R'))) THEN 'error'
  50        END AS atshipping_qtforegroundrole,
  51        CASE WHEN (xtindentrole = 2 AND duedate<=CURRENT_DATE) THEN 'error'
  52        END AS orderdate_qtforegroundrole,
  53        CASE WHEN (xtindentrole = 2 AND duedate<=CURRENT_DATE) THEN 'error'
  54        END AS duedate_qtforegroundrole,
  55 <? if exists("useReservationNetting") ?>
  56        CASE WHEN (coitem_qtyreserved > 0
  57               AND sobalance > coitem_qtyreserved) THEN 'emphasis'
  58             WHEN ((sobalance <> 0)
  59               AND ((sobalance - coitem_qtyreserved) = 0)) THEN 'altemphasis'
  60        END AS qtforegroundrole,
  61 <? endif ?>
  62        CASE WHEN (xtindentrole = 2) THEN ''
  63        END AS sobalance_qtdisplayrole
  64 FROM (
  65 <? if not exists("sohead_id") ?>
  66     SELECT cohead_id AS id, -2 AS altid, cohead_id, cohead_number,
  67            cohead_number AS item_number,
  68            '' AS trueitem_number,
  69            (cust_number||'-'||cust_name) AS descrip,
  70            '' AS uom_name, CAST(NULL AS NUMERIC) AS qoh,
  71            CAST(NULL AS NUMERIC) AS sobalance,
  72            CAST(NULL AS NUMERIC) AS allocated,
  73            CAST(NULL AS NUMERIC) AS ordered,
  74            CAST(NULL AS NUMERIC) AS atshipping,
  75            CAST(NULL AS NUMERIC) AS coitem_qtyreserved,
  76            cohead_orderdate AS orderdate, MIN(coitem_scheddate) AS duedate,
  77            pack_id,
  78            CAST(NULL AS NUMERIC) AS reorderlevel,
  79            0 AS xtindentrole
  80      FROM cohead
  81      LEFT OUTER JOIN pack ON (pack_head_type='SO' AND pack_head_id=cohead_id)
  82      JOIN custinfo ON (cohead_cust_id=cust_id)
  83      JOIN coitem   ON (coitem_cohead_id=cohead_id)
  84      JOIN itemsite ON (coitem_itemsite_id=itemsite_id)
  85      WHERE ((coitem_status NOT IN ('C', 'X'))
  86   <? if exists("custtype_id") ?>
  87         AND (cust_custtype_id=<? value("custtype_id") ?>)
  88   <? elseif exists("custtype_pattern") ?>
  89         AND (cust_custtype_id IN (SELECT custtype_id
  90                                   FROM custtype
  91                                   WHERE(custtype_code ~ <? value("custtype_pattern") ?>)))
  92   <? endif ?>
  93       )
  94       GROUP BY cohead_id, item_number, cust_number,
  95                cust_name, cohead_orderdate, pack_id, coitem_scheddate
  96   <? if exists("onlyShowShortages") ?>
  97       HAVING (MIN(noNeg(qtyAvailable(itemsite_id)) +
  98                   qtyOrdered(itemsite_id, coitem_scheddate) -
  99                   qtyAllocated(itemsite_id, coitem_scheddate)) < 0
 100               OR MIN(noNeg(qtyAvailable(itemsite_id)) +
 101                   qtyOrdered(itemsite_id, coitem_scheddate) -
 102                   noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) < 0
 103            )
 104   <? endif ?>
 105     UNION
 106 <? endif ?>
 107     SELECT itemsite_id AS id, coitem_id AS altid, cohead_id, cohead_number,
 108             item_number,
 109             item_number AS trueitem_number,
 110             (item_descrip1 || ' ' || item_descrip2) AS descrip,
 111             uom_name, noNeg(qtyAvailable(itemsite_id)) AS qoh,
 112             noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,
 113             qtyAllocated(itemsite_id, coitem_scheddate) AS allocated,
 114             qtyOrdered(itemsite_id, coitem_scheddate) AS ordered,
 115             qtyatshipping(coitem_id) AS atshipping,
 116             itemuomtouom(item_id, coitem_qty_uom_id, null, coitem_qtyreserved) AS coitem_qtyreserved,
 117             CAST(NULL AS DATE) AS orderdate, coitem_scheddate AS duedate,
 118             CAST(NULL AS INTEGER) AS pack_id,
 119             CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel
 120                  ELSE 0.0 END AS reorderlevel,
 121             1 AS xtindentrole
 122      FROM cohead
 123 <? if not exists("sohead_id") ?> JOIN custinfo ON (cohead_cust_id=cust_id)<? endif ?>
 124           , itemsite, item, uom, site(), coitem
 125      WHERE ((coitem_cohead_id=cohead_id)
 126         AND (coitem_itemsite_id=itemsite_id)
 127         AND (itemsite_warehous_id=warehous_id)
 128         AND (itemsite_item_id=item_id)
 129         AND (item_inv_uom_id=uom_id)
 130         AND (coitem_status NOT IN ('C', 'X'))
 131 <? if exists("sohead_id") ?>
 132         AND (cohead_id=<? value("sohead_id") ?>)
 133 <? elseif exists("custtype_id") ?>
 134         AND (cust_custtype_id=<? value("custtype_id") ?>)
 135 <? elseif exists("custtype_pattern") ?>
 136         AND (cust_custtype_id IN (SELECT custtype_id
 137                                   FROM custtype
 138                                   WHERE(custtype_code ~ <? value("custtype_pattern") ?>)))
 139 <? endif ?>
 140 <? if exists("onlyShowShortages") ?>
 141         AND ((noNeg(qtyAvailable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
 142           OR (noNeg(qtyAvailable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) < 0))
 143 <? endif ?>
 144     )
 145 <? if exists("showWoSupply") ?>
 146     UNION
 147     SELECT  itemsite_id, -1, cohead_id, cohead_number,
 148             formatwonumber(wo_id),
 149             item_number AS trueitem_number,
 150             wo_status,
 151             NULL, NULL,
 152             noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,
 153             NULL,
 154             noNeg((wo_qtyord-wo_qtyrcv)) AS ordered,
 155             NULL,
 156             NULL,
 157             wo_startdate, wo_duedate,
 158             NULL AS pack_id,
 159             CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel
 160                  ELSE 0.0 END AS reorderlevel,
 161             2 AS xtindentrole
 162      FROM cohead
 163 <? if not exists("sohead_id") ?> JOIN custinfo ON (cohead_cust_id=cust_id)<? endif ?>
 164           , itemsite, item, uom, site(), coitem
 165           LEFT OUTER JOIN wo
 166            ON ((coitem_itemsite_id=wo_itemsite_id)
 167            AND (wo_status IN ('E','R','I'))
 168            AND (wo_qtyord-wo_qtyrcv > 0)
 169            AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) >
 170             (SELECT qtyAvailable(itemsite_id) FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
 171      WHERE ((coitem_cohead_id=cohead_id)
 172         AND (coitem_itemsite_id=itemsite_id)
 173         AND (itemsite_warehous_id=warehous_id)
 174         AND (itemsite_item_id=item_id)
 175         AND (item_inv_uom_id=uom_id)
 176         AND (coitem_status NOT IN ('C', 'X'))
 177   <? if exists("sohead_id") ?>
 178         AND (cohead_id=<? value("sohead_id") ?>)
 179   <? elseif exists("custtype_id") ?>
 180         AND (cust_custtype_id=<? value("custtype_id") ?>)
 181   <? elseif exists("custtype_pattern") ?>
 182         AND (cust_custtype_id IN (SELECT custtype_id
 183                                   FROM custtype
 184                                   WHERE(custtype_code ~ <? value("custtype_pattern") ?>)))
 185   <? endif ?>
 186   <? if exists("onlyShowShortages") ?>
 187         AND ((noNeg(qtyAvailable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
 188           OR (noNeg(qtyAvailable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) < 0))
 189   <? endif ?>
 190   )
 191 <? endif ?>
 192 ) AS data
 193 ORDER BY duedate, cohead_number, trueitem_number,
 194          xtindentrole;