1 -- Group: substituteAvailability
3 -- Notes: used by dspSubstituteAvailabilityByItem
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.
7 SELECT s_itemsite_id, warehous_code, item_number, itemdescrip,
8 netableqoh, reorderlevel, leadtime, itemsub_rank,
9 allocated, ordered, available,
10 'qty' AS netableqoh_xtnumericrole,
11 'qty' AS allocated_xtnumericrole,
12 'qty' AS ordered_xtnumericrole,
13 'qty' AS reorderlevel_xtnumericrole,
14 'qty' AS available_xtnumericrole,
15 CASE WHEN (reorderlevel >= available) THEN 'error' END AS available_qtforegroundrole
16 FROM (SELECT sub.itemsite_id AS s_itemsite_id,
17 warehous_code, item_number,
18 (item_descrip1 || ' ' || item_descrip2) AS itemdescrip,
19 <? if exists("normalize") ?>
20 (qtyNetable(sub.itemsite_id) * itemsub_uomratio) AS netableqoh,
21 (CASE WHEN(sub.itemsite_useparams)
22 THEN sub.itemsite_reorderlevel
24 END * itemsub_uomratio) AS reorderlevel,
25 sub.itemsite_leadtime AS leadtime, itemsub_rank,
27 (qtyNetable(sub.itemsite_id)) AS netableqoh,
28 CASE WHEN(sub.itemsite_useparams) THEN sub.itemsite_reorderlevel
31 sub.itemsite_leadtime AS leadtime, itemsub_rank,
33 <? if exists("leadTime") ?>
34 <? if exists("normalize") ?>
35 (qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio) AS allocated,
36 (qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio) AS ordered,
37 ((qtyNetable(sub.itemsite_id) * itemsub_uomratio)
38 + (qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio)
39 - (qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio)) AS available
41 (qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) AS allocated,
42 (qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)) AS ordered,
43 (qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)
44 - qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) AS available
46 <? elseif exists("byDays") ?>
47 <? if exists("normalize") ?>
48 (qtyAllocated(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio) AS allocated,
49 (qtyOrdered(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio) AS ordered,
50 ((qtyNetable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio)
51 - (qtyAllocated(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio)) AS available
53 (qtyAllocated(sub.itemsite_id, <? value("days") ?>)) AS allocated,
54 (qtyOrdered(sub.itemsite_id, <? value("days") ?>)) AS ordered,
55 (qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, <? value("days") ?>)
56 - qtyAllocated(sub.itemsite_id, <? value("days") ?>)) AS available
58 <? elseif exists("byDate") ?>
59 <? if exists("normalize") ?>
60 (qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio) AS allocated,
61 (qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio) AS ordered,
62 ((qtyNetable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio)
63 - (qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio)) AS available
65 (qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE))) AS allocated,
66 (qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE))) AS ordered,
67 (qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id,
68 (<? value("date") ?> - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE))) AS available
71 FROM item, itemsite AS sub, itemsite AS root, whsinfo, itemsub
72 WHERE ((sub.itemsite_item_id=item_id)
73 AND (root.itemsite_item_id=itemsub_parent_item_id)
74 AND (sub.itemsite_item_id=itemsub_sub_item_id)
75 AND (root.itemsite_warehous_id=sub.itemsite_warehous_id)
76 AND (sub.itemsite_warehous_id=warehous_id)
77 AND (root.itemsite_item_id=<? value("item_id") ?>)
78 <? if exists("warehous_id") ?>
79 AND (root.itemsite_warehous_id=<? value("warehous_id") ?>)
83 ORDER BY itemsub_rank;