1 -- Group: inventoryLocator
3 -- Notes: used by dspInventoryLocator
4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
5 -- Lot/Serial Characteristics additions by Specter Vision Solutions, Inc.
6 -- See www.xtuple.com/CPAL for the full text of the software license.
9 <? value("na") ?> AS locationname_xtnullrole,
10 <? value("na") ?> AS netable_xtnullrole,
11 <? value("na") ?> AS usable_xtnullrole,
12 <? value("na") ?> AS lotserial_xtnullrole,
13 <? value("na") ?> AS expiration_xtnullrole,
14 <? value("na") ?> AS warranty_xtnullrole,
15 CASE WHEN (itemsite_perishable
16 AND itemloc_expiration <= CURRENT_DATE) THEN 'error'
17 WHEN (itemsite_warrpurc
18 AND itemloc_warrpurc <= CURRENT_DATE) THEN 'error'
19 END AS qtforegroundrole,
20 'qty' AS qoh_xtnumericrole
21 FROM (SELECT itemloc_id, 1 AS type, warehous_code,
22 itemsite_perishable, itemloc_expiration,
23 itemsite_warrpurc, itemloc_warrpurc,
24 CASE WHEN (location_id IS NOT NULL) THEN
25 (formatLocationName(location_id) || '-' || firstLine(location_descrip))
27 CASE WHEN (location_id IS NOT NULL) THEN location_netable
29 CASE WHEN (location_id IS NOT NULL) THEN location_usable
31 CASE WHEN (itemsite_controlmethod IN ('L', 'S')) THEN
32 formatlotserialnumber(itemloc_ls_id)
34 CASE WHEN (itemsite_perishable) THEN itemloc_expiration
36 CASE WHEN (itemsite_warrpurc) THEN itemloc_warrpurc
39 <? foreach("char_id_text_list") ?>
40 , charass_alias<? literal("char_id_text_list") ?>.charass_value AS char<? literal("char_id_text_list") ?>
42 <? foreach("char_id_list_list") ?>
43 , charass_alias<? literal("char_id_list_list") ?>.charass_value AS char<? literal("char_id_list_list") ?>
45 <? foreach("char_id_date_list") ?>
46 , CASE WHEN (charass_alias<? literal("char_id_date_list") ?>.charass_value = '') THEN NULL::date
47 ELSE charass_alias<? literal("char_id_date_list") ?>.charass_value::date
48 END AS char<? literal("char_id_date_list") ?>
50 FROM itemsite, whsinfo,
51 itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
52 <? foreach("char_id_text_list") ?>
53 LEFT OUTER JOIN charass charass_alias<? literal("char_id_text_list") ?> ON ((charass_alias<? literal("char_id_text_list") ?>.charass_target_type='LS')
54 AND (charass_alias<? literal("char_id_text_list") ?>.charass_target_id=itemloc_ls_id)
55 AND (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=<? value("char_id_text_list") ?>))
56 LEFT OUTER JOIN char char_alias<? literal("char_id_text_list") ?> ON (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=char_alias<? literal("char_id_text_list") ?>.char_id)
58 <? foreach("char_id_list_list") ?>
59 LEFT OUTER JOIN charass charass_alias<? literal("char_id_list_list") ?> ON ((charass_alias<? literal("char_id_list_list") ?>.charass_target_type='LS')
60 AND (charass_alias<? literal("char_id_list_list") ?>.charass_target_id=itemloc_ls_id)
61 AND (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=<? value("char_id_list_list") ?>))
62 LEFT OUTER JOIN char char_alias<? literal("char_id_list_list") ?> ON (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=char_alias<? literal("char_id_list_list") ?>.char_id)
64 <? foreach("char_id_date_list") ?>
65 LEFT OUTER JOIN charass charass_alias<? literal("char_id_date_list") ?> ON ((charass_alias<? literal("char_id_date_list") ?>.charass_target_type='LS')
66 AND (charass_alias<? literal("char_id_date_list") ?>.charass_target_id=itemloc_ls_id)
67 AND (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=<? value("char_id_date_list") ?>))
68 LEFT OUTER JOIN char char_alias<? literal("char_id_date_list") ?> ON (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=char_alias<? literal("char_id_date_list") ?>.char_id)
70 WHERE ((itemsite_loccntrl OR (itemsite_controlmethod IN ('L', 'S')))
71 AND (itemloc_itemsite_id=itemsite_id)
72 AND (itemsite_warehous_id=warehous_id)
73 AND (itemsite_item_id=<? value("item_id") ?>)
74 <? if exists("warehous_id") ?>
75 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
77 <? if exists("site") ?>
78 AND (itemsite_warehous_id=<? value("site")?>)
80 <? if exists("lot_serial") ?>
81 AND (formatlotserialnumber(itemloc_ls_id)=<? value("lot_serial")?>)
83 <? if exists("_location_name") ?>
84 AND (formatLocationName(location_id)=<? value("_location_name")?>)
86 <? literal("charClause") ?>
89 SELECT itemsite_id, 2 AS type, warehous_code,
90 itemsite_perishable, NULL AS itemloc_expiration,
91 itemsite_warrpurc, NULL AS itemloc_warrpurc,
98 itemsite_qtyonhand AS qoh
99 <? foreach("char_id_text_list" ?>
100 , NULL AS char<? value("char_id_text_list") ?>
102 <? foreach("char_id_list_list" ?>
103 , NULL AS char<? value("char_id_list_list") ?>
105 <? foreach("char_id_date_list" ?>
106 , NULL AS char<? value("char_id_date_list") ?>
108 FROM itemsite, whsinfo
109 WHERE ((NOT itemsite_loccntrl)
110 AND (itemsite_controlmethod NOT IN ('L', 'S'))
111 AND (itemsite_warehous_id=warehous_id)
112 AND (itemsite_item_id=<? value("item_id") ?>)
113 <? if exists("warehous_id") ?>
114 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
118 ORDER BY warehous_code, locationname, lotserial;