3 -- Notes: used by dspCountTagEditList
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.
8 CASE WHEN (xtindentrole = 1) THEN NULL
9 <? if exists("varianceValue") ?>
10 WHEN (ABS(variancecost) > <? value("varianceValue") ?>) THEN 'error'
11 <? elseif exists("variancePercent") ?>
12 WHEN (ABS(varianceprcnt * 100.0) > <? value("variancePercent") ?>) THEN 'error'
16 END AS qtforegroundrole,
17 CASE WHEN (xtindentrole = 1) THEN NULL
18 WHEN (qohafter IS NOT NULL) THEN 'emphasis'
19 END AS qohafter_qtforegroundrole,
20 CASE WHEN (xtindentrole = 0) THEN NULL ELSE '' END AS invcnt_priority_qtdisplayrole,
21 'qty' AS qoh_xtnumericrole,
22 'qty' AS qohafter_xtnumericrole,
23 'qty' AS variance_xtnumericrole,
24 'percent' AS varianceprcnt_xtnumericrole,
25 'curr' AS variancecost_xtnumericrole
27 SELECT invcnt_id, -1 AS cntslip_id, invcnt_priority,
28 COALESCE(invcnt_tagnumber, 'Misc.') AS tagnumber,
29 invcnt_tagdate AS tagdate,
30 item_number, (item_descrip1 || item_descrip2) AS item_descrip, warehous_code,
31 CASE WHEN (location_id IS NOT NULL) THEN
33 ELSE <? value("all") ?> END AS loc_specific,
34 CASE WHEN (invcnt_location_id IS NOT NULL)
35 THEN (SELECT SUM(itemloc_qty)
36 FROM itemloc JOIN location ON (location_id=itemloc_location_id)
37 WHERE ((itemloc_itemsite_id=itemsite_id)
38 AND (itemloc_location_id=invcnt_location_id)
39 AND (location_netable)))
40 ELSE itemsite_qtyonhand
42 CASE WHEN (invcnt_location_id IS NOT NULL)
43 THEN (SELECT SUM(itemloc_qty)
44 FROM itemloc JOIN location ON (location_id=itemloc_location_id)
45 WHERE ((itemloc_itemsite_id=itemsite_id)
46 AND (itemloc_location_id=invcnt_location_id)
47 AND (NOT location_netable)))
50 <? if exists("showSlips") ?>
51 calcTotalSlipQty(invcnt_id) AS qohafter,
52 (calcTotalSlipQty(invcnt_id) - (itemsite_qtyonhand + itemsite_nnqoh)) AS variance,
53 CASE WHEN (calcTotalSlipQty(invcnt_id) IS NULL) THEN NULL
54 WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (calcTotalSlipQty(invcnt_id) > 0)) THEN 1
55 WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (calcTotalSlipQty(invcnt_id) < 0)) THEN -1
56 WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (calcTotalSlipQty(invcnt_id) = 0)) THEN 0
57 ELSE ((1 - (calcTotalSlipQty(invcnt_id) / (itemsite_qtyonhand + itemsite_nnqoh))) * -1)
59 (itemCost(itemsite_id) * (calcTotalSlipQty(invcnt_id) - (itemsite_qtyonhand + itemsite_nnqoh))) AS variancecost,
61 COALESCE(invcnt_qoh_after, 0.0) AS qohafter,
62 (invcnt_qoh_after - (itemsite_qtyonhand + itemsite_nnqoh)) AS variance,
63 CASE WHEN (invcnt_qoh_after IS NULL) THEN NULL
64 WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (invcnt_qoh_after > 0)) THEN 1
65 WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (invcnt_qoh_after < 0)) THEN -1
66 WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (invcnt_qoh_after = 0)) THEN 0
67 ELSE ((1 - (invcnt_qoh_after / (itemsite_qtyonhand + itemsite_nnqoh))) * -1)
69 (itemCost(itemsite_id) * (invcnt_qoh_after - (itemsite_qtyonhand + itemsite_nnqoh))) AS variancecost,
71 item_number AS orderby,
73 FROM invcnt LEFT OUTER JOIN location ON (invcnt_location_id=location_id),
74 item, whsinfo, itemsite
75 WHERE ((invcnt_itemsite_id=itemsite_id)
76 AND (itemsite_item_id=item_id)
77 AND (itemsite_warehous_id=warehous_id)
78 AND (NOT invcnt_posted)
79 <? if exists("warehous_id") ?>
80 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
82 <? if exists("classcode_id") ?>
83 AND (item_classcode_id=<? value("classcode_id") ?>)
84 <? elseif exists("classcode_pattern") ?>
85 AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ <? value("classcode_pattern") ?>)))
86 <? elseif exists("plancode_id") ?>
87 AND (itemsite_plancode_id=<? value("plancode_id") ?>)
88 <? elseif exists("plancode_pattern") ?>
89 AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ <? value("plancode_pattern") ?>)))
91 <? if exists("showSlips") ?>
94 SELECT invcnt_id, cntslip_id, invcnt_priority,
95 cntslip_number AS tagnumber,
96 cntslip_entered AS tagdate,
97 CASE WHEN (cntslip_posted) THEN <? value("posted") ?>
98 ELSE <? value("unposted") ?>
105 cntslip_qty AS qohafter,
106 NULL AS variance, NULL AS varianceprcnt, 0 AS variancecost,
107 item_number AS orderby,
109 FROM cntslip, invcnt, itemsite, item
110 WHERE ((cntslip_cnttag_id=invcnt_id)
111 AND (invcnt_itemsite_id=itemsite_id)
112 AND (itemsite_item_id=item_id)
113 AND (NOT invcnt_posted)
114 <? if exists("warehous_id") ?>
115 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
117 <? if exists("classcode_id") ?>
118 AND (item_classcode_id=<? value("classcode_id") ?>)
119 <? elseif exists("classcode_pattern") ?>
120 AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ <? value("classcode_pattern") ?>)))
121 <? elseif exists("plancode_id") ?>
122 AND (itemsite_plancode_id=<? value("plancode_id") ?>)
123 <? elseif exists("plancode_pattern") ?>
124 AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ <? value("plancode_pattern") ?>)))
129 ORDER BY invcnt_priority DESC, orderby, invcnt_id, cntslip_id;