1 -- Group: distributeInventory
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 id, type, locationname, defaultlocation,
8 location_netable, lotserial, f_expiration, expired,
9 qty, qtytagged, (qty + qtytagged) AS balance,
10 'qty' AS qty_xtnumericrole,
11 'qty' AS qtytagged_xtnumericrole,
12 'qty' AS balance_xtnumericrole,
13 CASE WHEN expired THEN 'error' END AS qtforegroundrole,
14 CASE WHEN expired THEN 'error'
15 WHEN defaultlocation AND expired = false THEN 'altemphasis'
16 ELSE null END AS defaultlocation_qtforegroundrole,
17 CASE WHEN expired THEN 'error'
18 WHEN qty > 0 AND expired = false THEN 'altemphasis'
19 ELSE null END AS qty_qtforegroundrole
21 <? if exists("cNoIncludeLotSerial") ?>
22 SELECT location_id AS id, <? value("locationType") ?> AS type,
23 formatLocationName(location_id) AS locationname,
24 CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
25 WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
26 WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
28 END AS defaultlocation,
30 TEXT('') AS lotserial,
31 TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
32 qtyLocation(location_id, NULL, NULL, NULL, itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
33 itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
34 FROM itemlocdist, location, itemsite
35 WHERE ( (itemlocdist_itemsite_id=itemsite_id)
36 AND (itemsite_loccntrl)
37 AND (itemsite_warehous_id=location_warehous_id)
38 AND (validLocation(location_id, itemsite_id))
39 AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
40 <? elseif exists("cIncludeLotSerial") ?>
41 SELECT itemloc_id AS id, <? value("itemlocType") ?> AS type,
42 COALESCE(formatLocationName(location_id),
43 <? value("undefined") ?>) AS locationname,
44 (location_id IS NOT NULL AND
45 CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
46 WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
47 WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
49 END) AS defaultlocation,
50 COALESCE(location_netable, false) AS location_netable,
51 ls_number AS lotserial,
52 CASE WHEN (itemsite_perishable) THEN formatDate(itemloc_expiration)
53 ELSE <? value("na") ?>
55 CASE WHEN (itemsite_perishable) THEN (itemloc_expiration < CURRENT_DATE)
58 qtyLocation(itemloc_location_id, itemloc_ls_id, itemloc_expiration, itemloc_warrpurc, itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
59 ( SELECT COALESCE(SUM(target.itemlocdist_qty), 0)
60 FROM itemlocdist AS target
61 WHERE ( (target.itemlocdist_source_type='I')
62 AND (target.itemlocdist_source_id=itemloc_id)
63 AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_id)) ) AS qtytagged
64 FROM itemlocdist AS source, itemsite, itemloc
65 LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
66 LEFT OUTER JOIN ls ON (itemloc_ls_id=ls_id)
67 WHERE ( (source.itemlocdist_itemsite_id=itemsite_id)
68 AND (itemloc_itemsite_id=itemsite_id)
69 AND (source.itemlocdist_id=<? value("itemlocdist_id") ?>) )
71 SELECT location_id AS id, <? value("locationType") ?> AS type,
72 formatLocationName(location_id) AS locationname,
73 CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
74 WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
75 WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
77 END AS defaultlocation,
79 TEXT('') AS lotserial,
80 TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
81 qtyLocation(location_id, NULL, NULL, NULL, itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
82 itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
83 FROM itemlocdist, location, itemsite
84 WHERE ( (itemlocdist_itemsite_id=itemsite_id)
85 AND (itemsite_loccntrl)
86 AND (itemsite_warehous_id=location_warehous_id)
87 AND (validLocation(location_id, itemsite_id))
88 AND (itemsite_id=<? value("itemsite_id") ?> )
89 AND (location_id NOT IN (SELECT DISTINCT itemloc_location_id FROM itemloc WHERE (itemloc_itemsite_id=itemsite_id)))
90 AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
94 <? if exists("showOnlyTagged") ?>
97 <? if exists("showQtyOnly") ?>
100 ) ORDER BY locationname;