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        availqoh, formatQty(availqoh) AS f_availqoh,
 163        nonavailqoh, formatQty(nonavailqoh) AS f_nonavailqoh,
 164        netqoh, formatQty(netqoh) AS f_netqoh,
 165        nonnetqoh, formatQty(nonnetqoh) AS f_nonnetqoh,
 166        cost, (cost * qoh) AS value,
 167        (cost * availqoh) AS availvalue,
 168        (cost * nonavailqoh) AS nonavailvalue,
 169        (cost * netqoh) AS netvalue,
 170        (cost * nonnetqoh) AS nonnetvalue,
 171        CASE WHEN(itemsite_costmethod='A') THEN 'Average'
 172             WHEN(itemsite_costmethod='S') THEN 'Standard'
 173             WHEN(itemsite_costmethod='J') THEN 'Job'
 174             WHEN(itemsite_costmethod='N') THEN 'None'
 175          ELSE 'UNKNOWN'
 176        END AS costmethod,
 177   <? if exists("showValue") ?>
 178        formatMoney(cost) AS f_cost, (cost * qoh) AS f_value,
 179        formatMoney(cost * availqoh) AS f_availvalue,
 180        formatMoney(cost * nonavailqoh) AS f_nonavailvalue,
 181        formatMoney(cost * netqoh) AS f_netvalue,
 182        formatMoney(cost * nonnetqoh) AS f_nonnetvalue,
 183        CASE WHEN(itemsite_costmethod='A') THEN 'Average'
 184             WHEN(itemsite_costmethod='S') THEN 'Standard'
 185             WHEN(itemsite_costmethod='J') THEN 'Job'
 186             WHEN(itemsite_costmethod='N') THEN 'None'
 187          ELSE 'UNKNOWN'
 188        END AS f_costmethod,
 189   <? endif ?>
 190        'qty' AS reorderlevel_xtnumericrole,
 191        'qty' AS qoh_xtnumericrole,
 192        'qty' AS availqoh_xtnumericrole,
 193        'qty' AS nonavailqoh_xtnumericrole,
 194        'qty' AS netqoh_xtnumericrole,
 195        'qty' AS nonnetqoh_xtnumericrole,
 196        0 AS qoh_xttotalrole,
 197        0 AS availqoh_xttotalrole,
 198        0 AS nonavailqoh_xttotalrole,
 199        0 AS netqoh_xttotalrole,
 200        0 AS nonnetqoh_xttotalrole,
 201        'cost' AS cost_xtnumericrole,
 202        'curr' AS value_xtnumericrole,
 203        'curr' AS availvalue_xtnumericrole,
 204        'curr' AS nonavailvalue_xtnumericrole,
 205        'curr' AS netvalue_xtnumericrole,
 206        'curr' AS nonnetvalue_xtnumericrole,
 207        0 AS value_xttotalrole,
 208        0 AS availvalue_xttotalrole,
 209        0 AS nonavailvalue_xttotalrole,
 210        0 AS netvalue_xttotalrole,
 211        0 AS nonnetvalue_xttotalrole,
 212        <? value("na") ?> AS availqoh_xtnullrole,
 213        <? value("na") ?> AS nonavailqoh_xtnullrole,
 214        <? value("na") ?> AS availvalue_xtnullrole,
 215        <? value("na") ?> AS nonavailvalue_xtnullrole,
 216        <? value("na") ?> AS netqoh_xtnullrole,
 217        <? value("na") ?> AS nonnetqoh_xtnullrole,
 218        <? value("na") ?> AS netvalue_xtnullrole,
 219        <? value("na") ?> AS nonnetvalue_xtnullrole,
 220        CASE WHEN (qoh < 0) THEN 'error' END AS qoh_qtforegroundrole,
 221        CASE WHEN (reorderlevel > qoh) THEN 'warning' END AS qoh_qtforegroundrole
 222 FROM (
 223       SELECT itemsite_id, itemsite_loccntrl, itemsite_costmethod,
 224              ((itemsite_loccntrl) OR (itemsite_controlmethod IN ('L', 'S')) ) AS detail,
 225              classcode_code, item_number, uom_name, item_descrip1, item_descrip2,
 226              costcat_code,
 227              CASE WHEN (NOT useDefaultLocation(itemsite_id)) THEN <? value("none") ?>
 228                ELSE defaultLocationName(itemsite_id)
 229              END AS defaultlocation,
 230              warehous_code,
 231              CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel,
 232   <? if exists("asOf") ?>
 233              COALESCE(invbal_qoh_ending,0) AS qoh,
 234              COALESCE(invbal_qoh_ending,0) AS availqoh,
 235              COALESCE(invbal_nn_ending,0) AS nonavailqoh,
 236              COALESCE(invbal_qoh_ending,0) AS netqoh,
 237              COALESCE(invbal_nn_ending,0) AS nonnetqoh,
 238   <? else ?>
 239              itemsite_qtyonhand AS qoh,
 240              qtyAvailable(itemsite_id) AS availqoh,
 241              qtyAvailable(itemsite_id, FALSE) AS nonavailqoh,
 242              qtyNetable(itemsite_id) AS netqoh,
 243              qtyNetable(itemsite_id, FALSE) AS nonnetqoh,
 244   <? endif ?>
 245   <? if exists("useStandardCosts") ?>
 246              stdcost(item_id) AS cost
 247   <? elseif exists("useActualCosts") ?>
 248              actcost(item_id) AS cost
 249   <? else ?>
 250     <? if exists("asOf") ?>
 251              COALESCE((invbal_value_ending / CASE WHEN(invbal_qoh_ending=0) THEN 1
 252                                                   ELSE invbal_qoh_ending END),0) AS cost
 253     <? else ?>
 254              (itemsite_value / CASE WHEN(itemsite_qtyonhand=0) THEN 1
 255                                     ELSE itemsite_qtyonhand END) AS cost
 256     <? endif ?>
 257   <? endif ?>
 258       FROM item, whsinfo, classcode, uom, costcat, itemsite
 259   <? if exists("asOf") ?>
 260         LEFT OUTER JOIN invbal ON ((itemsite_id=invbal_itemsite_id)
 261                                AND (invbal_period_id=<? value("asOf") ?>))
 262   <? endif ?>
 263       WHERE ((itemsite_item_id=item_id)
 264          AND (itemsite_warehous_id=warehous_id)
 265          AND (itemsite_active)
 266          AND (item_inv_uom_id=uom_id)
 267          AND (item_classcode_id=classcode_id)
 268          AND (itemsite_costcat_id=costcat_id)
 269   <? if exists("item_id") ?>
 270          AND (itemsite_item_id=<? value("item_id") ?>)
 271   <? endif ?>
 272   <? if exists("classcode_id") ?>
 273          AND (classcode_id=<? value("classcode_id") ?>)
 274   <? endif ?>
 275   <? if exists("classcode_pattern") ?>
 276          AND (classcode_id IN (SELECT classcode_id
 277                                FROM classcode
 278                                WHERE classcode_code ~ <? value("classcode_pattern") ?>))
 279   <? endif ?>
 280   <? if exists("costcat_id") ?>
 281          AND (costcat_id=<? value("costcat_id") ?>)
 282   <? endif ?>
 283   <? if exists("costcat_pattern") ?>
 284          AND (costcat_id IN (SELECT costcat_id
 285                                FROM costcat
 286                                WHERE costcat_code ~ <? value("costcat_pattern") ?>))
 287   <? endif ?>
 288   <? if exists("itemgrp_id") ?>
 289          AND (item_id IN (SELECT itemgrpitem_item_id
 290                           FROM itemgrpitem
 291                           WHERE (itemgrpitem_itemgrp_id=<? value("itemgrp_id") ?>)))
 292   <? endif ?>
 293   <? if exists("itemgrp_pattern") ?>
 294          AND (item_id IN (SELECT itemgrpitem_item_id
 295                           FROM itemgrpitem, itemgrp
 296                           WHERE ((itemgrpitem_itemgrp_id=itemgrp_id)
 297                              AND (itemgrp_name ~ <? value("itemgrp_pattern") ?>))))
 298   <? endif ?>
 299   <? if exists("showPositive") ?>
 300     <? if exists("asOf") ?>
 301        AND (COALESCE(invbal_qoh_ending,0) > 0)
 302     <? else ?>
 303        AND (itemsite_qtyonhand > 0)
 304     <? endif ?>
 305   <? elseif exists("showNegative") ?>
 306     <? if exists("asOf") ?>
 307        AND (COALESCE(invbal_qoh_ending,0) < 0)
 308     <? else ?>
 309        AND (itemsite_qtyonhand < 0)
 310     <? endif ?>
 311   <? endif ?>
 312   <? if exists("warehous_id") ?>
 313          AND (itemsite_warehous_id=<? value("warehous_id") ?>)
 314   <? endif ?>
 315             )
 316      ) AS data
 317 ORDER BY warehous_code, item_number;
 318 <? endif ?>