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, location_usable, 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 defaultlocation AND expired = false THEN 'altemphasis'
15 WHEN expired THEN 'error'
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,
29 COALESCE(location_netable, true) AS location_netable,
30 COALESCE(location_usable, true) AS location_usable,
31 TEXT('') AS lotserial,
32 TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
33 qtyLocation(location_id, NULL, NULL, NULL,
34 itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
35 itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
36 FROM itemlocdist, location, itemsite
37 WHERE ( (itemlocdist_itemsite_id=itemsite_id)
38 AND (itemsite_loccntrl)
39 AND (itemsite_warehous_id=location_warehous_id)
40 AND (validLocation(location_id, itemsite_id))
41 AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
42 <? elseif exists("cIncludeLotSerial") ?>
43 SELECT itemloc_id AS id, <? value("itemlocType") ?> AS type,
44 COALESCE(formatLocationName(location_id),
45 <? value("undefined") ?>) AS locationname,
46 (location_id IS NOT NULL AND
47 CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
48 WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
49 WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
51 END) AS defaultlocation,
52 COALESCE(location_netable, true) AS location_netable,
53 COALESCE(location_usable, true) AS location_usable,
54 ls_number AS lotserial,
55 CASE WHEN (itemsite_perishable) THEN formatDate(itemloc_expiration)
56 ELSE <? value("na") ?>
58 CASE WHEN (itemsite_perishable) THEN (itemloc_expiration < CURRENT_DATE)
61 qtyLocation(itemloc_location_id, itemloc_ls_id, itemloc_expiration,
62 itemloc_warrpurc, itemsite_id, itemlocdist_order_type,
63 itemlocdist_order_id, itemlocdist_id) AS qty,
64 ( SELECT COALESCE(SUM(target.itemlocdist_qty), 0)
65 FROM itemlocdist AS target
66 WHERE ( (target.itemlocdist_source_type='I')
67 AND (target.itemlocdist_source_id=itemloc_id)
68 AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_id)) ) AS qtytagged
69 FROM itemlocdist AS source, itemsite, itemloc
70 LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
71 LEFT OUTER JOIN ls ON (itemloc_ls_id=ls_id)
72 WHERE ( (source.itemlocdist_itemsite_id=itemsite_id)
73 AND (itemloc_itemsite_id=itemsite_id)
74 AND (source.itemlocdist_id=<? value("itemlocdist_id") ?>) )
76 SELECT location_id AS id, <? value("locationType") ?> AS type,
77 formatLocationName(location_id) AS locationname,
78 CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
79 WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
80 WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
82 END AS defaultlocation,
83 COALESCE(location_netable, true) AS location_netable,
84 COALESCE(location_usable, true) AS location_usable,
85 TEXT('') AS lotserial,
86 TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
87 qtyLocation(location_id, NULL, NULL, NULL,
88 itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
89 itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
90 FROM itemlocdist, location, itemsite
91 WHERE ( (itemlocdist_itemsite_id=itemsite_id)
92 AND (itemsite_loccntrl)
93 AND (itemsite_warehous_id=location_warehous_id)
94 AND (validLocation(location_id, itemsite_id))
95 AND (itemsite_id=<? value("itemsite_id") ?> )
96 AND (location_id NOT IN (SELECT DISTINCT itemloc_location_id
98 WHERE (itemloc_itemsite_id=itemsite_id)))
99 AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
103 AND ( (<? value("transtype") ?> != 'I') OR (<? value("transtype") ?> = 'I' AND location_usable) )
104 <? if exists("showOnlyTagged") ?>
107 <? if exists("showQtyOnly") ?>
110 ) ORDER BY locationname;