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

   1 -- Group: runningAvailability
   2 -- Name: detail
   3 -- Notes: used by dspRunningAvailability, itemAvailabilityWorkbench
   4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   5 -- See www.xtuple.com/CPAL for the full text of the software license.
   6
   7 SELECT orderid, altorderid, ordertype, ordernumber, sequence, item_number,
   8        duedate, amount, qtyordered,  qtyreceived, balance,
   9        balance AS runningavail, balance AS runningnetable,
  10 <? if exists("isReport") ?>
  11        CASE WHEN duedate = startOfTime() THEN ''
  12             ELSE formatDate(duedate) END AS f_duedate,
  13        formatMoney(amount) AS f_amount,
  14        formatQty(qtyordered) AS f_qtyordered,
  15        formatQty(qtyreceived) AS f_qtyreceived,
  16        formatQty(balance) AS f_balance,
  17 <? endif ?>
  18        notes,
  19        1 AS runningavail_xtrunningrole,
  20        1 AS runningnetable_xtrunningrole,
  21        <? value("qoh") ?> AS runningavail_xtrunninginit,
  22        <? value("netableqoh") ?> AS runningnetable_xtrunninginit,
  23        CASE WHEN late THEN 'error' END AS duedate_qtforegroundrole,
  24        CASE WHEN duedate = startOfTime() THEN '' END AS duedate_qtdisplayrole,
  25        CASE WHEN ordertype ~ <? value("plannedPo") ?> OR
  26                  ordertype ~ <? value("plannedWo") ?> OR
  27                  ordertype ~ <? value("plannedTo") ?> THEN
  28           'emphasis' END AS qtforegroundrole,
  29       'currency' AS amount_xtnumericrole,
  30       'qty' AS qtyordered_xtnumericrole,
  31       'qty' AS qtyreceived_xtnumericrole,
  32       'qty' AS balance_xtnumericrole
  33 FROM (
  34 <? if exists("isReport") ?>
  35 SELECT -1 AS orderid, -1 AS altorderid, 'QOH' AS ordertype,
  36        NULL AS ordernumber,
  37        0 AS sequence,
  38        '' AS item_number,
  39        startOfTime() AS duedate,
  40        FALSE AS late,
  41        0 AS amount,
  42        0 AS qtyordered,
  43        0 AS qtyreceived,
  44        <? value("qoh") ?> AS balance,
  45        '' AS notes
  46 UNION
  47 <? endif ?>
  48 <? if exists("MultiWhs") ?>
  49 SELECT tohead_id AS orderid, toitem_id AS altorderid, 'T/O' AS ordertype,
  50        TEXT(tohead_number) AS ordernumber,
  51        1 AS sequence,
  52        tohead_srcname || '/' || tohead_destname AS item_number,
  53        toitem_duedate AS duedate,
  54        (toitem_duedate < CURRENT_DATE) AS late,
  55        (toitem_stdcost * toitem_qty_ordered) AS amount,
  56        toitem_qty_ordered AS qtyordered,
  57        toitem_qty_received AS qtyreceived,
  58        noNeg(toitem_qty_ordered - toitem_qty_received) AS balance,
  59        tohead_ordercomments AS notes
  60 FROM tohead, toitem
  61 WHERE ((toitem_tohead_id=tohead_id)
  62   AND  (toitem_status NOT IN ('C', 'X'))
  63   AND  (toitem_item_id=<? value("item_id") ?>)
  64   AND  (tohead_dest_warehous_id=<? value("warehous_id") ?>)
  65   AND  (toitem_qty_ordered > toitem_qty_received) )
  66
  67 UNION
  68 SELECT tohead_id AS orderid, toitem_id AS altorderid, 'T/O' AS ordertype,
  69        TEXT(tohead_number) AS ordernumber,
  70        1 AS sequence,
  71        tohead_srcname || '/' || tohead_destname AS item_number,
  72        toitem_duedate AS duedate,
  73        (toitem_duedate < CURRENT_DATE) AS late,
  74        (toitem_stdcost * toitem_qty_ordered) AS amount,
  75        toitem_qty_ordered AS qtyordered,
  76        toitem_qty_received AS qtyreceived,
  77        -1 * noNeg(toitem_qty_ordered - toitem_qty_shipped - qtyAtShipping('TO', toitem_id)) AS balance,
  78        tohead_ordercomments AS notes
  79        -- TODO: old code had ^^^^ for formatted balance and vvvv used to calc running avail! which is right?
  80        -- -1 * noNeg(toitem_qty_ordered - toitem_qty_received) AS balance
  81 FROM tohead, toitem
  82 WHERE ((toitem_tohead_id=tohead_id)
  83   AND  (toitem_status NOT IN ('C', 'X'))
  84   AND  (toitem_item_id=<? value("item_id") ?>)
  85   AND  (tohead_src_warehous_id=<? value("warehous_id") ?>)
  86   AND  (toitem_qty_ordered - toitem_qty_shipped - qtyAtShipping('TO', toitem_id)) > 0 )
  87
  88 UNION
  89 <? endif ?>
  90 SELECT wo_id AS orderid, -1 AS altorderid,
  91        'W/O' AS ordertype,
  92        formatWoNumber(wo_id) AS ordernumber,
  93        1 AS sequence,
  94        item_number,
  95        wo_duedate AS duedate,
  96        (wo_duedate < CURRENT_DATE) AS late,
  97        (itemCost(wo_itemsite_id) * wo_qtyord) AS amount,
  98        wo_qtyord AS qtyordered,
  99        wo_qtyrcv AS qtyreceived,
 100        noNeg(wo_qtyord - wo_qtyrcv) AS balance,
 101        wo_prodnotes AS notes
 102 FROM wo, itemsite, item
 103 WHERE ((wo_status<>'C')
 104   AND  (wo_itemsite_id=itemsite_id)
 105   AND  (itemsite_item_id=item_id)
 106   AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 107   AND  (itemsite_item_id=<? value("item_id") ?>)
 108   AND  (item_type NOT IN ('C', 'Y')))
 109
 110 UNION
 111
 112 -- Tools on work orders to be returned
 113 SELECT wo_id AS orderid, -1 AS altorderid,
 114        'W/O' AS ordertype,
 115        formatWoNumber(wo_id) AS ordernumber,
 116        1 AS sequence,
 117        item_number,
 118        wo_duedate AS duedate,
 119        (wo_duedate < CURRENT_DATE) AS late,
 120        (womatl_cost * womatl_qtyreq) AS amount,
 121        womatl_qtyreq AS qtyordered,
 122        COALESCE(SUM(abs(invhist_invqty)),0) AS qtyreceived,
 123        noNeg(womatl_qtyreq - COALESCE(SUM(abs(invhist_invqty)),0)) AS balance,
 124        wo_prodnotes AS notes
 125 FROM womatl
 126   JOIN wo ON (wo_id=womatl_wo_id)
 127   JOIN itemsite ON (womatl_itemsite_id=itemsite_id)
 128   JOIN item ON (itemsite_item_id=item_id)
 129   LEFT OUTER JOIN womatlpost ON (womatl_id=womatlpost_womatl_id)
 130   LEFT OUTER JOIN invhist ON ((womatlpost_invhist_id=invhist_id)
 131                           AND (invhist_invqty < 0))
 132 WHERE ((wo_status<>'C')
 133   AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 134   AND  (itemsite_item_id=<? value("item_id") ?>)
 135   AND  (item_type = 'T'))
 136 GROUP BY wo_id, wo_duedate, item_number, womatl_qtyreq, womatl_cost, wo_prodnotes
 137
 138 <? if exists("Manufacturing") ?>
 139 UNION
 140 SELECT wo_id AS orderid, -1 AS altorderid,
 141        'W/O' AS ordertype,
 142        formatWoNumber(wo_id) AS ordernumber,
 143        1 AS sequence,
 144        item_number,
 145        wo_duedate AS duedate,
 146        (wo_duedate < CURRENT_DATE) AS late,
 147        (itemCost(wo_itemsite_id) * wo_qtyord * brddist_stdqtyper) AS amount,
 148        (wo_qtyord * brddist_stdqtyper) AS qtyordered,
 149        (wo_qtyrcv * brddist_stdqtyper) AS qtyreceived,
 150        noNeg((wo_qtyord - wo_qtyrcv) * brddist_stdqtyper) AS balance,
 151        wo_prodnotes AS notes
 152 FROM xtmfg.brddist, wo, itemsite, item
 153 WHERE ((wo_status<>'C')
 154   AND  (brddist_wo_id=wo_id)
 155   AND  (wo_itemsite_id=itemsite_id)
 156   AND  (itemsite_item_id=item_id)
 157   AND  (brddist_itemsite_id=itemsite_id)
 158   AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 159   AND  (itemsite_item_id=<? value("item_id") ?>)
 160   AND  (item_type IN ('C', 'Y')) )
 161 <? endif ?>
 162
 163 UNION
 164 SELECT wo_id AS orderid, womatl_id AS altorderid,
 165       'W/O' AS ordertype,
 166       formatWoNumber(wo_id) AS ordernumber,
 167       2 AS sequence,
 168       woi.item_number,
 169       womatl_duedate AS duedate,
 170       FALSE AS late,
 171       (itemCost(womatl_itemsite_id) * itemuomtouom(womatlis.itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq)) AS amount,
 172       itemuomtouom(womatlis.itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq) AS qtyordered,
 173       itemuomtouom(womatlis.itemsite_item_id, womatl_uom_id, NULL, womatl_qtyiss) AS qtyreceived,
 174       itemuomtouom(womatlis.itemsite_item_id, womatl_uom_id, NULL, (noNeg(womatl_qtyreq - womatl_qtyiss) * -1)) AS balance,
 175       wo_prodnotes AS notes
 176 FROM womatl, wo, itemsite AS wois, item AS woi, itemsite AS womatlis, item AS womatli
 177 WHERE ((wo_status<>'C')
 178   AND  (wo_itemsite_id=wois.itemsite_id)
 179   AND  (wois.itemsite_item_id=woi.item_id)
 180   AND  (womatlis.itemsite_item_id=womatli.item_id)
 181   AND  (womatli.item_type != 'T')
 182   AND  (womatl_wo_id=wo_id)
 183   AND  (womatlis.itemsite_item_id=<? value("item_id") ?>)
 184   AND  (womatlis.itemsite_warehous_id=<? value("warehous_id") ?>)
 185   AND  (womatl_itemsite_id=womatlis.itemsite_id) )
 186
 187 UNION
 188 -- Special handling for tools
 189 SELECT wo_id AS orderid, womatl_id AS altorderid,
 190       'W/O' AS ordertype,
 191       formatWoNumber(wo_id) AS ordernumber,
 192       2 AS sequence,
 193       woi.item_number,
 194       womatl_duedate AS duedate,
 195       FALSE AS late,
 196       (womatl_cost * itemuomtouom(womatlis.itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq)) AS amount,
 197       itemuomtouom(womatlis.itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq) AS qtyordered,
 198       COALESCE(SUM(invhist_invqty),0) AS qtyreceived,
 199       (itemuomtouom(womatlis.itemsite_item_id, womatl_uom_id, NULL, (noNeg(womatl_qtyreq))) - COALESCE(SUM(invhist_invqty),0)) * -1 AS balance,
 200       wo_prodnotes AS notes
 201 FROM itemsite AS wois, item AS woi, itemsite AS womatlis, item AS womatli, wo, womatl
 202   LEFT OUTER JOIN womatlpost ON (womatl_id=womatlpost_womatl_id)
 203   LEFT OUTER JOIN invhist ON ((womatlpost_invhist_id=invhist_id)
 204                           AND (invhist_invqty > 0))
 205 WHERE ((wo_status<>'C')
 206   AND  (wo_itemsite_id=wois.itemsite_id)
 207   AND  (wois.itemsite_item_id=woi.item_id)
 208   AND  (womatlis.itemsite_item_id=womatli.item_id)
 209   AND  (womatli.item_type = 'T')
 210   AND  (womatl_wo_id=wo_id)
 211   AND  (womatlis.itemsite_item_id=<? value("item_id") ?>)
 212   AND  (womatlis.itemsite_warehous_id=<? value("warehous_id") ?>)
 213   AND  (womatl_itemsite_id=womatlis.itemsite_id) )
 214 GROUP BY wo_id, woi.item_number, womatl_id, womatl_duedate, womatlis.itemsite_item_id, womatl_uom_id, womatl_qtyreq, womatl_cost, wo_prodnotes
 215
 216 UNION
 217 SELECT pohead_id AS orderid, poitem_id AS altorderid,
 218       'P/O' AS ordertype,
 219       TEXT(pohead_number) AS ordernumber,
 220       1 AS sequence,
 221       vend_name AS item_number,
 222       poitem_duedate AS duedate,
 223       (poitem_duedate < CURRENT_DATE) AS late,
 224       (poitem_unitprice * poitem_qty_ordered) AS amount,
 225       (poitem_qty_ordered * poitem_invvenduomratio) AS qtyordered,
 226       (NoNeg(poitem_qty_received - poitem_qty_returned) * poitem_invvenduomratio) AS qtyreceived,
 227       (noNeg(poitem_qty_ordered - poitem_qty_received) * poitem_invvenduomratio) AS balance,
 228        pohead_comments AS notes
 229 FROM pohead, vendinfo, poitem, itemsite, item
 230 WHERE ((vend_id=pohead_vend_id)
 231   AND  (poitem_pohead_id=pohead_id)
 232   AND  (poitem_status <> 'C')
 233   AND  (poitem_itemsite_id=itemsite_id)
 234   AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 235   AND  (itemsite_item_id=item_id)
 236   AND  (item_id=<? value("item_id") ?>) )
 237
 238 UNION
 239 SELECT cohead_id AS orderid, coitem_id AS altorderid,
 240        'S/O' AS ordertype,
 241        TEXT(cohead_number) AS ordernumber,
 242        2 AS sequence,
 243        cust_name AS item_number,
 244        coitem_scheddate AS duedate,
 245        (coitem_scheddate < CURRENT_DATE) AS late,
 246        (coitem_price * coitem_qtyord) AS amount,
 247        (coitem_qtyord * coitem_qty_invuomratio) AS qtyordered,
 248        (coitem_qty_invuomratio * (coitem_qtyshipped - coitem_qtyreturned + qtyAtShipping(coitem_id))) AS qtyreceived,
 249        (coitem_qty_invuomratio * noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned - qtyAtShipping(coitem_id)) * -1) AS balance,
 250        cohead_ordercomments AS notes
 251 FROM coitem, cohead, custinfo, itemsite, item
 252 WHERE ((coitem_status='O')
 253   AND  (cohead_cust_id=cust_id)
 254   AND  (coitem_cohead_id=cohead_id)
 255   AND  (coitem_itemsite_id=itemsite_id)
 256   AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 257   AND  (itemsite_item_id=item_id)
 258   AND  (item_id=<? value("item_id") ?>))
 259
 260 <? if exists("showPlanned") ?>
 261 <? if exists("showMRPplan") ?>
 262 UNION
 263 SELECT planord_id AS orderid, -1 AS altorderid,
 264        CASE WHEN (planord_firm) THEN <? value("firmPo") ?>
 265            ELSE <? value("plannedPo") ?>
 266        END AS ordertype,
 267        CAST(planord_number AS text) AS ordernumber,
 268        1 AS sequence,
 269        '' AS item_number,
 270        planord_duedate AS duedate,
 271        FALSE AS late,
 272        (itemCost(planord_itemsite_id) * planord_qty) AS amount,
 273        planord_qty AS qtyordered,
 274        NULL AS qtyreceived,
 275        planord_qty AS balance,
 276        planord_comments AS notes
 277 FROM planord, itemsite
 278 WHERE ((planord_type='P')
 279   AND  (planord_itemsite_id=itemsite_id)
 280   AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 281   AND  (itemsite_item_id=<? value("item_id") ?>) )
 282
 283 UNION
 284 SELECT planord_id AS orderid, -1 AS altorderid,
 285        CASE WHEN (planord_firm) THEN <? value("firmWo") ?>
 286             ELSE <? value("plannedWo") ?>
 287        END AS ordertype,
 288        CAST(planord_number AS text) AS ordernumber,
 289        1 AS sequence,
 290        '' AS item_number,
 291        planord_duedate AS duedate,
 292        FALSE AS late,
 293        (itemCost(planord_itemsite_id) * planord_qty) AS amount,
 294        planord_qty AS qtyordered,
 295        NULL AS qtyreceived,
 296        planord_qty AS balance,
 297        planord_comments AS notes
 298 FROM planord, itemsite
 299 WHERE ((planord_type='W')
 300   AND  (planord_itemsite_id=itemsite_id)
 301   AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 302   AND  (itemsite_item_id=<? value("item_id") ?>) )
 303
 304 UNION
 305 -- Tools on Planned Work Orders
 306 SELECT planord_id AS orderid, -1 AS altorderid,
 307        CASE WHEN (planord_firm) THEN <? value("firmWo") ?>
 308             ELSE <? value("plannedWo") ?>
 309        END AS ordertype,
 310        CAST(planord_number AS text) AS ordernumber,
 311        1 AS sequence,
 312        item_number,
 313        planord_duedate AS duedate,
 314        FALSE AS late,
 315        (itemCost(planreq_itemsite_id) * planreq_qty) AS amount,
 316        planreq_qty AS qtyordered,
 317        NULL AS qtyreceived,
 318        planreq_qty AS balance,
 319        planord_comments AS notes
 320 FROM planreq
 321   JOIN itemsite ON (itemsite_id=planreq_itemsite_id)
 322   JOIN item ON (item_id=itemsite_item_id)
 323   JOIN planord ON ((planreq_source_id=planord_id)
 324               AND (planreq_source='P'))
 325 WHERE ((itemsite_warehous_id=<? value("warehous_id") ?>)
 326   AND  (itemsite_item_id=<? value("item_id") ?>)
 327   AND  (item_type='T') )
 328
 329 UNION
 330 SELECT planord_id AS orderid, -1 AS altorderid,
 331        CASE WHEN (planord_firm) THEN <? value("firmTo") ?>
 332             ELSE <? value("plannedTo") ?>
 333        END AS ordertype,
 334        CAST(planord_number AS text) AS ordernumber,
 335        1 AS sequence,
 336        srcwhsinfo.warehous_code || '/' || whsinfo.warehous_code AS item_number,
 337        planord_duedate AS duedate,
 338        FALSE AS late,
 339        (itemCost(planord_itemsite_id) * planord_qty) AS amount,
 340        planord_qty AS qtyordered,
 341        NULL AS qtyreceived,
 342        planord_qty AS balance,
 343        planord_comments AS notes
 344 FROM planord JOIN itemsite ON (itemsite.itemsite_id=planord_itemsite_id)
 345              JOIN whsinfo ON (whsinfo.warehous_id=itemsite.itemsite_warehous_id)
 346              JOIN itemsite srcitemsite ON (srcitemsite.itemsite_id=planord_supply_itemsite_id)
 347              JOIN whsinfo srcwhsinfo ON (srcwhsinfo.warehous_id=srcitemsite.itemsite_warehous_id)
 348 WHERE ((planord_type='T')
 349   AND  (itemsite.itemsite_warehous_id=<? value("warehous_id") ?>)
 350   AND  (itemsite.itemsite_item_id=<? value("item_id") ?>) )
 351
 352 UNION
 353 SELECT planord_id AS orderid, -1 AS altorderid,
 354        CASE WHEN (planord_firm) THEN <? value("firmTo") ?>
 355             ELSE <? value("plannedTo") ?>
 356        END AS ordertype,
 357        CAST(planord_number AS text) AS ordernumber,
 358        1 AS sequence,
 359        srcwhsinfo.warehous_code || '/' || whsinfo.warehous_code AS item_number,
 360        planord_duedate AS duedate,
 361        FALSE AS late,
 362        (itemCost(planord_itemsite_id) * planord_qty) AS amount,
 363        planord_qty AS qtyordered,
 364        NULL AS qtyreceived,
 365        (planord_qty * -1) AS balance,
 366        planord_comments AS notes
 367 FROM planord JOIN itemsite ON (itemsite.itemsite_id=planord_itemsite_id)
 368              JOIN whsinfo ON (whsinfo.warehous_id=itemsite.itemsite_warehous_id)
 369              JOIN itemsite srcitemsite ON (srcitemsite.itemsite_id=planord_supply_itemsite_id)
 370              JOIN whsinfo srcwhsinfo ON (srcwhsinfo.warehous_id=srcitemsite.itemsite_warehous_id)
 371 WHERE ((planord_type='T')
 372   AND  (srcitemsite.itemsite_warehous_id=<? value("warehous_id") ?>)
 373   AND  (srcitemsite.itemsite_item_id=<? value("item_id") ?>) )
 374
 375 UNION
 376 SELECT planreq_id AS orderid, -1 AS altorderid,
 377        CASE WHEN (planord_firm) THEN <? value("firmWoReq") ?>
 378             ELSE <? value("plannedWoReq") ?>
 379        END AS ordertype,
 380        CAST(planord_number AS text) AS ordernumber,
 381        1 AS sequence,
 382 --get the planned order number for the higher level demand
 383              (SELECT item_number
 384                 FROM item, itemsite
 385                WHERE((itemsite_item_id=item_id)
 386                  AND (itemsite_id=planord_itemsite_id))
 387              ) AS item_number,
 388        planord_startdate AS duedate,
 389        FALSE AS late,
 390        (itemCost(planreq_itemsite_id) * planreq_qty) AS amount,
 391        planreq_qty AS f_qtyordered,
 392        NULL AS qtyreceived,
 393        (planreq_qty * -1) AS balance,
 394        planreq_notes AS notes
 395 FROM planreq, planord, itemsite, item
 396 WHERE ((planreq_source='P')
 397   AND  (planreq_source_id=planord_id)
 398   AND  (planreq_itemsite_id=itemsite_id)
 399   AND  (itemsite_item_id=item_id)
 400   AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 401   AND  (itemsite_item_id=<? value("item_id") ?>) )
 402 <? endif ?>
 403
 404 UNION
 405 SELECT pr_id AS orderid, -1 AS altorderid,
 406        <? value("pr") ?> AS ordertype,
 407        CAST(pr_number AS text) AS ordernumber,
 408        1 AS sequence,
 409        '' AS item_number,
 410        pr_duedate AS duedate,
 411        FALSE AS late,
 412        (itemCost(pr_itemsite_id) * pr_qtyreq) AS amount,
 413        pr_qtyreq AS qtyordered,
 414        NULL AS qtyreceived,
 415        pr_qtyreq AS balance,
 416        pr_releasenote AS notes
 417 FROM pr, itemsite, item
 418 WHERE ((pr_itemsite_id=itemsite_id)
 419   AND  (itemsite_item_id=item_id)
 420   AND  (pr_itemsite_id=itemsite_id)
 421   AND  (itemsite_warehous_id=<? value("warehous_id") ?>)
 422   AND  (itemsite_item_id=<? value("item_id") ?>) )
 423 <? endif ?>
 424 ) AS dummy
 425
 426 ORDER BY duedate, sequence;
 427