1 -- Group: inventoryAvailability
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 itemsite_id, altId,
8 item_number, (item_descrip1 || ' ' || item_descrip2) AS itemdescrip,
10 warehous_id, warehous_code, itemsite_leadtime,
11 qoh, allocated, noNeg(qoh - allocated) AS unallocated,
12 ordered, requests, reorderlevel, outlevel,
13 (qoh - allocated + ordered) AS available,
15 'qty' AS qoh_xtnumericrole,
16 'qty' AS allocated_xtnumericrole,
17 'qty' AS unallocated_xtnumericrole,
18 'qty' AS ordered_xtnumericrole,
19 'qty' AS requests_xtnumericrole,
20 'qty' AS reorderlevel_xtnumericrole,
21 'qty' AS outlevel_xtnumericrole,
22 'qty' AS available_xtnumericrole,
23 <? if exists("byDates") ?>
24 'grey' AS qoh_qtforegroundrole,
26 CASE WHEN ((qoh - allocated + ordered) < 0) THEN 'error'
27 WHEN ((qoh - allocated + ordered) <= reorderlevel) THEN 'warning'
28 END AS available_qtforegroundrole
30 <? if reExists("[vV]end") ?>
34 CASE WHEN (item_type IN ('P', 'O')) THEN 1
35 WHEN (item_type IN ('M')) THEN 2
38 item_number, item_descrip1, item_descrip2, item_inv_uom_id,
39 warehous_id, warehous_code, itemsite_leadtime,
40 itemsite_qtyonhand AS qoh,
41 CASE WHEN itemsite_useparams THEN itemsite_reorderlevel
44 CASE WHEN itemsite_useparams THEN itemsite_ordertoqty
47 <? if exists("byVend") ?>
52 <? if exists("byLeadTime") ?>
53 qtyAllocated(itemsite_id, itemsite_leadtime) AS allocated,
54 qtyOrdered(itemsite_id, itemsite_leadtime) AS ordered,
55 qtypr(itemsite_id, itemsite_leadtime) AS requests
56 <? elseif exists("byDays") ?>
57 qtyAllocated(itemsite_id, CAST(<? value("byDays") ?> AS INTEGER)) AS allocated,
58 qtyOrdered(itemsite_id, CAST(<? value("byDays") ?> AS INTEGER)) AS ordered,
59 qtypr(itemsite_id, CAST(<? value("byDays") ?> AS INTEGER)) AS requests
60 <? elseif exists("byDate") ?>
61 qtyAllocated(itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) AS allocated,
62 qtyOrdered(itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) AS ordered,
63 qtypr(itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) AS requests
64 <? elseif exists("byDates") ?>
65 qtyAllocated(itemsite_id, <? value("startDate") ?>, <? value("endDate") ?>) AS allocated,
66 qtyOrdered(itemsite_id, <? value("startDate") ?>, <? value("endDate") ?>) AS ordered,
67 qtypr(itemsite_id, <? value("startDate") ?>, <? value("endDate") ?>) AS requests
69 FROM item, itemsite, whsinfo
70 <? if reExists("[vV]end") ?>
71 , vendinfo JOIN itemsrc ON (itemsrc_vend_id=vend_id)
73 WHERE ( (itemsite_active)
74 AND (itemsite_item_id=item_id)
75 AND (itemsite_warehous_id=warehous_id)
76 <? if exists("warehous_id") ?>
77 AND (warehous_id=<? value("warehous_id") ?>)
79 <? if exists("item_id") ?>
80 AND (item_id=<? value("item_id") ?>)
81 <? elseif exists("classcode_id") ?>
82 AND (item_classcode_id=<? value("classcode_id") ?>)
83 <? elseif exists("classcode_pattern") ?>
84 AND (item_classcode_id IN (SELECT classcode_id
86 WHERE (classcode_code ~ <? value("classcode_pattern") ?>)))
87 <? elseif exists("plancode_id") ?>
88 AND (itemsite_plancode_id=<? value("plancode_id") ?>)
89 <? elseif exists("plancode_pattern") ?>
90 AND (itemsite_plancode_id IN (SELECT plancode_id
92 WHERE (plancode_code ~ <? value("plancode_pattern") ?>)))
93 <? elseif exists("itemgrp_id") ?>
94 AND (item_id IN (SELECT itemgrpitem_item_id
96 WHERE (itemgrpitem_itemgrp_id=<? value("itemgrp_id") ?>)))
97 <? elseif exists("itemgrp_pattern") ?>
98 AND (item_id IN (SELECT itemgrpitem_item_id
99 FROM itemgrpitem, itemgrp
100 WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id)
101 AND (itemgrp_name ~ <? value("itemgrp_pattern") ?>) ) ))
102 <? elseif exists("itemgrp") ?>
103 AND (item_id IN (SELECT DISTINCT itemgrpitem_item_id FROM itemgrpitem))
105 <? if reExists("[vV]end") ?>
106 AND (itemsrc_item_id=item_id)
108 <? if exists("vend_id") ?>
109 AND (vend_id=<? value("vend_id") ?>)
110 <? elseif exists("vendtype_id") ?>
111 AND (vend_vendtype_id=<? value("vendtype_id") ?>)
112 <? elseif exists("vendtype_pattern") ?>
113 AND (vend_vendtype_id IN (SELECT vendtype_id
115 WHERE (vendtype_code ~ <? value("vendtype_pattern") ?>)))
118 WHERE ((item_inv_uom_id=uom_id)
119 <? if exists("showReorder") ?>
120 AND ((qoh - allocated + ordered) <= reorderlevel)
121 <? if exists("ignoreReorderAtZero") ?>
122 AND NOT ( ((qoh - allocated + ordered) = 0) AND (reorderlevel = 0))
124 <? elseif exists("showShortages") ?>
125 AND ((qoh - allocated + ordered) < 0)
130 <? if exists("ListNumericItemNumbersFirst") ?>
131 toNumeric(item_number, 999999999999999),
132 <? elseif exists("byVend") ?>
135 item_number, warehous_code DESC;