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

   1 -- Group: qoh
   2 -- Name: detail
   3 -- Notes:
   4 -- Quantity on Hand By Location
   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 <? if exists("byLocation") ?>
   9 SELECT itemloc_id, alt_id, warehous_code, item_number,
  10        f_descrip, f_lotserial, uom_name,
  11        qoh, reservedqty,
  12        'qty' AS qoh_xtnumericrole,
  13        'qty' AS reservedqty_xtnumericrole,
  14        level AS xtindentrole
  15 FROM (
  16       SELECT itemloc_id, 0 AS alt_id, 0 AS level, item_number AS sortkey, warehous_code, item_number,
  17              (item_descrip1 || ' ' || item_descrip2) AS f_descrip,
  18              formatlotserialnumber(itemloc_ls_id) AS f_lotserial, uom_name,
  19              itemloc_qty AS qoh,
  20   <? if exists("EnableSOReservationsByLocation") ?>
  21              qtyReservedLocation(itemloc_id) AS reservedqty
  22   <? else ?>
  23              0 AS reservedqty
  24   <? endif ?>
  25       FROM itemloc, itemsite, whsinfo, item, uom
  26       WHERE ((itemloc_itemsite_id=itemsite_id)
  27          AND (itemsite_item_id=item_id)
  28          AND (item_inv_uom_id=uom_id)
  29          AND (itemsite_warehous_id=warehous_id)
  30          AND (itemloc_location_id=<? value("location_id") ?>))
  31       UNION
  32       SELECT -1 AS itemloc_id, 0 AS alt_id, 0 AS level, item_number AS sortkey, warehous_code, item_number,
  33              (item_descrip1 || ' ' || item_descrip2) AS f_descrip,
  34              <? value("na") ?> AS f_lotserial, uom_name,
  35              itemsite_qtyonhand AS qoh,
  36              0 AS reservedqty
  37       FROM itemsite, whsinfo, item, uom
  38       WHERE ((itemsite_item_id=item_id)
  39          AND (item_inv_uom_id=uom_id)
  40          AND (itemsite_warehous_id=warehous_id)
  41          AND (NOT itemsite_loccntrl)
  42          AND (itemsite_location_id=<? value("location_id") ?>))
  43   <? if exists("EnableSOReservationsByLocation") ?>
  44       UNION
  45       SELECT itemloc_id, -1 AS alt_id, 1 AS level, item_number AS sortkey, '' AS warehous_code, '' AS item_number,
  46              (reserve_demand_type || '-' || formatSOItemNumber(reserve_demand_id)) AS f_descrip,
  47              '' AS f_lotserial, '' AS uom_name,
  48              NULL AS qoh,
  49              reserve_qty AS reservedqty
  50       FROM reserve, itemloc, itemsite, item
  51       WHERE ((reserve_supply_id=itemloc_id)
  52          AND (reserve_supply_type='I')
  53          AND (itemsite_id=itemloc_itemsite_id)
  54          AND(item_id=itemsite_item_id)
  55          AND(itemloc_location_id=<? value("location_id") ?>))
  56   <? endif ?>
  57   <? if exists("ShowDemand") ?>
  58       UNION
  59       SELECT itemloc_id, coitem_cohead_id AS alt_id, 1 AS level, item_number AS sortkey, '' AS warehous_code, '' AS item_number,
  60              (<? value("so") ?>|| '-' || formatSOItemNumber(coitem_id)) AS f_descrip,
  61              '' AS f_lotserial, '' AS uom_name,
  62              itemUOMtoUOM(item_id, coitem_qty_uom_id, NULL, (coitem_qtyord - coitem_qtyshipped - qtyAtShipping(coitem_id))) AS qoh,
  63              0 AS reservedqty
  64       FROM itemloc JOIN itemsite ON (itemsite_id=itemloc_itemsite_id)
  65                    JOIN item ON (item_id=itemsite_item_id)
  66                    JOIN coitem ON (coitem_itemsite_id=itemloc_itemsite_id AND coitem_status='O')
  67       WHERE ((coitem_qtyord > (coitem_qtyshipped + qtyAtShipping(coitem_id)))
  68          AND(itemloc_location_id=<? value("location_id") ?>))
  69       UNION
  70       SELECT itemloc_id, -1 AS alt_id, 1 AS level, item_number AS sortkey, '' AS warehous_code, '' AS item_number,
  71              (<? value("wo") ?>|| '-' || formatWONumber(womatl_wo_id)) AS f_descrip,
  72              '' AS f_lotserial, '' AS uom_name,
  73              (womatl_qtyreq - womatl_qtyiss) AS qoh,
  74              0 AS reservedqty
  75       FROM itemloc JOIN itemsite ON (itemsite_id=itemloc_itemsite_id)
  76                    JOIN item ON (item_id=itemsite_item_id)
  77                    JOIN womatl ON (womatl_itemsite_id=itemloc_itemsite_id)
  78       WHERE ((womatl_qtyreq > womatl_qtyiss)
  79          AND(itemloc_location_id=<? value("location_id") ?>))
  80       UNION
  81       SELECT itemloc_id, -1 AS alt_id, 1 AS level, item_number AS sortkey, '' AS warehous_code, '' AS item_number,
  82              (<? value("to") ?>|| '-' || formatTONumber(toitem_id)) AS f_descrip,
  83              '' AS f_lotserial, '' AS uom_name,
  84              (toitem_qty_ordered - toitem_qty_shipped - qtyAtShipping('TO', toitem_id)) AS qoh,
  85              0 AS reservedqty
  86       FROM itemloc JOIN itemsite ON (itemsite_id=itemloc_itemsite_id)
  87                    JOIN item ON (item_id=itemsite_item_id)
  88                    JOIN toitem ON (toitem_item_id=item_id AND toitem_status='O')
  89                    JOIN tohead ON (tohead_id=toitem_tohead_id AND tohead_src_warehous_id=itemsite_warehous_id)
  90       WHERE ((toitem_qty_ordered > (toitem_qty_shipped + qtyAtShipping('TO', toitem_id)))
  91          AND(itemloc_location_id=<? value("location_id") ?>))
  92   <? endif ?>
  93      ) AS data
  94 ORDER BY
  95          sortkey, itemloc_id, level;
  96 <? else ?>
  97 <? if exists("asOf") ?>
  98 SELECT forwardupdateinvbalance(invbal_id)
  99 FROM (
 100       SELECT DISTINCT ON (itemsite_id) invbal_id
 101       FROM invbal, itemsite, item, whsinfo, classcode, uom, costcat, period
 102       WHERE ((invbal_dirty)
 103          AND (invbal_period_id=period_id)
 104          AND (itemsite_item_id=item_id)
 105          AND (itemsite_warehous_id=warehous_id)
 106          AND (itemsite_active)
 107          AND (item_inv_uom_id=uom_id)
 108          AND (item_classcode_id=classcode_id)
 109          AND (itemsite_costcat_id=costcat_id)
 110   <? if exists("item_id") ?>
 111          AND (itemsite_item_id=<? value("item_id") ?>)
 112   <? endif ?>
 113   <? if exists("classcode_id") ?>
 114          AND (classcode_id=<? value("classcode_id") ?>)
 115   <? endif ?>
 116   <? if exists("classcode_pattern") ?>
 117          AND (classcode_id IN (SELECT classcode_id
 118                                FROM classcode
 119                                WHERE classcode_code ~ <? value("classcode_pattern") ?>))
 120   <? endif ?>
 121   <? if exists("costcat_id") ?>
 122          AND (costcat_id=<? value("costcat_id") ?>)
 123   <? endif ?>
 124   <? if exists("costcat_pattern") ?>
 125          AND (costcat_id IN (SELECT costcat_id
 126                                FROM costcat
 127                                WHERE costcat_code ~ <? value("costcat_pattern") ?>))
 128   <? endif ?>
 129   <? if exists("itemgrp_id") ?>
 130          AND (item_id IN (SELECT itemgrpitem_item_id
 131                           FROM itemgrpitem
 132                           WHERE (itemgrpitem_itemgrp_id=<? value("itemgrp_id") ?>)))
 133   <? endif ?>
 134   <? if exists("itemgrp_pattern") ?>
 135          AND (item_id IN (SELECT itemgrpitem_item_id
 136                           FROM itemgrpitem, itemgrp
 137                           WHERE ((itemgrpitem_itemgrp_id=itemgrp_id)
 138                              AND (itemgrp_name ~ <? value("itemgrp_pattern") ?>))))
 139   <? endif ?>
 140   <? if exists("showPositive") ?>
 141        AND (itemsite_qtyonhand > 0)
 142   <? elseif exists("showNegative") ?>
 143        AND (itemsite_qtyonhand < 0)
 144   <? endif ?>
 145   <? if exists("warehous_id") ?>
 146          AND (itemsite_warehous_id=<? value("warehous_id") ?>)
 147   <? endif ?>
 148             )
 149        ORDER BY itemsite_id, period_start
 150      ) AS data
 151 ;
 152 <? endif ?>
 153 --  Quantity on Hand
 154 SELECT itemsite_id, detail,warehous_code,
 155        classcode_code, item_number, uom_name,
 156        costcat_code,
 157        item_descrip1, item_descrip2,
 158        (item_descrip1 || ' ' || item_descrip2) AS itemdescrip,
 159        defaultlocation,
 160        reorderlevel, formatQty(reorderlevel) AS f_reorderlevel,
 161        qoh, formatQty(qoh) AS f_qoh,
 162        nnqoh, formatQty(nnqoh) AS f_nnqoh,
 163        CASE WHEN (itemsite_loccntrl) THEN nnqoh END AS f_nnqoh,
 164        cost, (cost * qoh) AS value,
 165        CASE WHEN (itemsite_loccntrl) THEN (cost * nnqoh) END AS nnvalue,
 166        CASE WHEN(itemsite_costmethod='A') THEN 'Average'
 167             WHEN(itemsite_costmethod='S') THEN 'Standard'
 168             WHEN(itemsite_costmethod='J') THEN 'Job'
 169             WHEN(itemsite_costmethod='N') THEN 'None'
 170          ELSE 'UNKNOWN'
 171        END AS costmethod,
 172   <? if exists("showValue") ?>
 173        formatMoney(cost) AS f_cost, (cost * qoh) AS f_value,
 174        CASE WHEN (itemsite_loccntrl) THEN (cost * nnqoh) END AS f_nnvalue,
 175        CASE WHEN(itemsite_costmethod='A') THEN 'Average'
 176             WHEN(itemsite_costmethod='S') THEN 'Standard'
 177             WHEN(itemsite_costmethod='J') THEN 'Job'
 178             WHEN(itemsite_costmethod='N') THEN 'None'
 179          ELSE 'UNKNOWN'
 180        END AS f_costmethod,
 181   <? endif ?>
 182        'qty' AS reorderlevel_xtnumericrole,
 183        'qty' AS qoh_xtnumericrole,
 184        'qty' AS f_nnqoh_xtnumericrole,
 185        0 AS qoh_xttotalrole,
 186        0 AS nnqoh_xttotalrole,
 187        'cost' AS cost_xtnumericrole,
 188        'curr' AS value_xtnumericrole,
 189        'curr' AS nnvalue_xtnumericrole,
 190        0 AS value_xttotalrole,
 191        0 AS nnvalue_xttotalrole,
 192        <? value("na") ?> AS nnqoh_xtnullrole,
 193        <? value("na") ?> AS nnvalue_xtnullrole,
 194        CASE WHEN (qoh < 0) THEN 'error' END AS qoh_qtforegroundrole,
 195        CASE WHEN (reorderlevel > qoh) THEN 'warning' END AS qoh_qtforegroundrole
 196 FROM (
 197       SELECT itemsite_id, itemsite_loccntrl, itemsite_costmethod,
 198              ((itemsite_loccntrl) OR (itemsite_controlmethod IN ('L', 'S')) ) AS detail,
 199              classcode_code, item_number, uom_name, item_descrip1, item_descrip2,
 200              costcat_code,
 201              CASE WHEN (NOT useDefaultLocation(itemsite_id)) THEN <? value("none") ?>
 202                ELSE defaultLocationName(itemsite_id)
 203              END AS defaultlocation,
 204              warehous_code,
 205              CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel,
 206   <? if exists("asOf") ?>
 207              COALESCE(invbal_qoh_ending,0) AS qoh,
 208              COALESCE(invbal_nn_ending,0) AS nnqoh,
 209   <? else ?>
 210              itemsite_qtyonhand AS qoh,
 211              itemsite_nnqoh AS nnqoh,
 212   <? endif ?>
 213   <? if exists("useStandardCosts") ?>
 214              stdcost(item_id) AS cost
 215   <? elseif exists("useActualCosts") ?>
 216              actcost(item_id) AS cost
 217   <? else ?>
 218     <? if exists("asOf") ?>
 219              COALESCE((invbal_value_ending / CASE WHEN(invbal_qoh_ending=0) THEN 1
 220                                                   ELSE invbal_qoh_ending END),0) AS cost
 221     <? else ?>
 222              (itemsite_value / CASE WHEN((itemsite_qtyonhand + itemsite_nnqoh)=0) THEN 1
 223                                     ELSE (itemsite_qtyonhand + itemsite_nnqoh) END) AS cost
 224     <? endif ?>
 225   <? endif ?>
 226       FROM item, whsinfo, classcode, uom, costcat, itemsite
 227   <? if exists("asOf") ?>
 228         LEFT OUTER JOIN invbal ON ((itemsite_id=invbal_itemsite_id)
 229                                AND (invbal_period_id=<? value("asOf") ?>))
 230   <? endif ?>
 231       WHERE ((itemsite_item_id=item_id)
 232          AND (itemsite_warehous_id=warehous_id)
 233          AND (itemsite_active)
 234          AND (item_inv_uom_id=uom_id)
 235          AND (item_classcode_id=classcode_id)
 236          AND (itemsite_costcat_id=costcat_id)
 237   <? if exists("item_id") ?>
 238          AND (itemsite_item_id=<? value("item_id") ?>)
 239   <? endif ?>
 240   <? if exists("classcode_id") ?>
 241          AND (classcode_id=<? value("classcode_id") ?>)
 242   <? endif ?>
 243   <? if exists("classcode_pattern") ?>
 244          AND (classcode_id IN (SELECT classcode_id
 245                                FROM classcode
 246                                WHERE classcode_code ~ <? value("classcode_pattern") ?>))
 247   <? endif ?>
 248   <? if exists("costcat_id") ?>
 249          AND (costcat_id=<? value("costcat_id") ?>)
 250   <? endif ?>
 251   <? if exists("costcat_pattern") ?>
 252          AND (costcat_id IN (SELECT costcat_id
 253                                FROM costcat
 254                                WHERE costcat_code ~ <? value("costcat_pattern") ?>))
 255   <? endif ?>
 256   <? if exists("itemgrp_id") ?>
 257          AND (item_id IN (SELECT itemgrpitem_item_id
 258                           FROM itemgrpitem
 259                           WHERE (itemgrpitem_itemgrp_id=<? value("itemgrp_id") ?>)))
 260   <? endif ?>
 261   <? if exists("itemgrp_pattern") ?>
 262          AND (item_id IN (SELECT itemgrpitem_item_id
 263                           FROM itemgrpitem, itemgrp
 264                           WHERE ((itemgrpitem_itemgrp_id=itemgrp_id)
 265                              AND (itemgrp_name ~ <? value("itemgrp_pattern") ?>))))
 266   <? endif ?>
 267   <? if exists("showPositive") ?>
 268     <? if exists("asOf") ?>
 269        AND (COALESCE(invbal_qoh_ending,0) > 0)
 270     <? else ?>
 271        AND (itemsite_qtyonhand > 0)
 272     <? endif ?>
 273   <? elseif exists("showNegative") ?>
 274     <? if exists("asOf") ?>
 275        AND (COALESCE(invbal_qoh_ending,0) < 0)
 276     <? else ?>
 277        AND (itemsite_qtyonhand < 0)
 278     <? endif ?>
 279   <? endif ?>
 280   <? if exists("warehous_id") ?>
 281          AND (itemsite_warehous_id=<? value("warehous_id") ?>)
 282   <? endif ?>
 283             )
 284      ) AS data
 285 ORDER BY warehous_code, item_number;
 286 <? endif ?>