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
32 formatLocationName(location_id)
33 ELSE <? value("all") ?> END AS loc_specific,
34 qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) AS qoh,
35 <? if exists("showSlips") ?>
36 calcTotalSlipQty(invcnt_id) AS qohafter,
37 (calcTotalSlipQty(invcnt_id) - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id)) AS variance,
38 CASE WHEN (calcTotalSlipQty(invcnt_id) IS NULL) THEN NULL
39 WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) > 0)) THEN 1
40 WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) < 0)) THEN -1
41 WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) = 0)) THEN 0
42 ELSE ((1 - (calcTotalSlipQty(invcnt_id) / qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) * -1)
44 (itemCost(itemsite_id) * (calcTotalSlipQty(invcnt_id) - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) AS variancecost,
46 COALESCE(invcnt_qoh_after, 0.0) AS qohafter,
47 (invcnt_qoh_after - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id)) AS variance,
48 CASE WHEN (invcnt_qoh_after IS NULL) THEN NULL
49 WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after > 0)) THEN 1
50 WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after < 0)) THEN -1
51 WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after = 0)) THEN 0
52 ELSE ((1 - (invcnt_qoh_after / qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) * -1)
54 (itemCost(itemsite_id) * (invcnt_qoh_after - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) AS variancecost,
56 item_number AS orderby,
58 FROM invcnt LEFT OUTER JOIN location ON (invcnt_location_id=location_id),
59 item, whsinfo, itemsite
60 WHERE ((invcnt_itemsite_id=itemsite_id)
61 AND (itemsite_item_id=item_id)
62 AND (itemsite_warehous_id=warehous_id)
63 AND (NOT invcnt_posted)
64 <? if exists("warehous_id") ?>
65 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
67 <? if exists("classcode_id") ?>
68 AND (item_classcode_id=<? value("classcode_id") ?>)
69 <? elseif exists("classcode_pattern") ?>
70 AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ <? value("classcode_pattern") ?>)))
71 <? elseif exists("plancode_id") ?>
72 AND (itemsite_plancode_id=<? value("plancode_id") ?>)
73 <? elseif exists("plancode_pattern") ?>
74 AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ <? value("plancode_pattern") ?>)))
76 <? if exists("showSlips") ?>
79 SELECT invcnt_id, cntslip_id, invcnt_priority,
80 cntslip_number AS tagnumber,
81 cntslip_entered AS tagdate,
82 CASE WHEN (cntslip_posted) THEN <? value("posted") ?>
83 ELSE <? value("unposted") ?>
89 cntslip_qty AS qohafter,
90 NULL AS variance, NULL AS varianceprcnt, 0 AS variancecost,
91 item_number AS orderby,
93 FROM cntslip, invcnt, itemsite, item
94 WHERE ((cntslip_cnttag_id=invcnt_id)
95 AND (invcnt_itemsite_id=itemsite_id)
96 AND (itemsite_item_id=item_id)
97 AND (NOT invcnt_posted)
98 <? if exists("warehous_id") ?>
99 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
101 <? if exists("classcode_id") ?>
102 AND (item_classcode_id=<? value("classcode_id") ?>)
103 <? elseif exists("classcode_pattern") ?>
104 AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ <? value("classcode_pattern") ?>)))
105 <? elseif exists("plancode_id") ?>
106 AND (itemsite_plancode_id=<? value("plancode_id") ?>)
107 <? elseif exists("plancode_pattern") ?>
108 AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ <? value("plancode_pattern") ?>)))
113 ORDER BY invcnt_priority DESC, orderby, invcnt_id, cntslip_id;