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 lotserial_xtnullrole,
12 <? value("na") ?> AS expiration_xtnullrole,
13 <? value("na") ?> AS warranty_xtnullrole,
14 CASE WHEN (itemsite_perishable
15 AND itemloc_expiration <= CURRENT_DATE) THEN 'error'
16 WHEN (itemsite_warrpurc
17 AND itemloc_warrpurc <= CURRENT_DATE) THEN 'error'
18 END AS qtforegroundrole,
19 'qty' AS qoh_xtnumericrole
20 FROM (SELECT itemloc_id, 1 AS type, warehous_code,
21 itemsite_perishable, itemloc_expiration,
22 itemsite_warrpurc, itemloc_warrpurc,
23 CASE WHEN (location_id IS NOT NULL) THEN
24 (formatLocationName(location_id) || '-' || firstLine(location_descrip))
26 CASE WHEN (location_id IS NOT NULL) THEN location_netable
28 CASE WHEN (itemsite_controlmethod IN ('L', 'S')) THEN
29 formatlotserialnumber(itemloc_ls_id)
31 CASE WHEN (itemsite_perishable) THEN itemloc_expiration
33 CASE WHEN (itemsite_warrpurc) THEN itemloc_warrpurc
36 <? foreach("char_id_text_list") ?>
37 , charass_alias<? literal("char_id_text_list") ?>.charass_value AS char<? literal("char_id_text_list") ?>
39 <? foreach("char_id_list_list") ?>
40 , charass_alias<? literal("char_id_list_list") ?>.charass_value AS char<? literal("char_id_list_list") ?>
42 <? foreach("char_id_date_list") ?>
43 , CASE WHEN (charass_alias<? literal("char_id_date_list") ?>.charass_value = '') THEN NULL::date
44 ELSE charass_alias<? literal("char_id_date_list") ?>.charass_value::date
45 END AS char<? literal("char_id_date_list") ?>
47 FROM itemsite, whsinfo,
48 itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
49 <? foreach("char_id_text_list") ?>
50 LEFT OUTER JOIN charass charass_alias<? literal("char_id_text_list") ?> ON ((charass_alias<? literal("char_id_text_list") ?>.charass_target_type='LS')
51 AND (charass_alias<? literal("char_id_text_list") ?>.charass_target_id=itemloc_ls_id)
52 AND (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=<? value("char_id_text_list") ?>))
53 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)
55 <? foreach("char_id_list_list") ?>
56 LEFT OUTER JOIN charass charass_alias<? literal("char_id_list_list") ?> ON ((charass_alias<? literal("char_id_list_list") ?>.charass_target_type='LS')
57 AND (charass_alias<? literal("char_id_list_list") ?>.charass_target_id=itemloc_ls_id)
58 AND (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=<? value("char_id_list_list") ?>))
59 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)
61 <? foreach("char_id_date_list") ?>
62 LEFT OUTER JOIN charass charass_alias<? literal("char_id_date_list") ?> ON ((charass_alias<? literal("char_id_date_list") ?>.charass_target_type='LS')
63 AND (charass_alias<? literal("char_id_date_list") ?>.charass_target_id=itemloc_ls_id)
64 AND (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=<? value("char_id_date_list") ?>))
65 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)
67 WHERE ((itemsite_loccntrl OR (itemsite_controlmethod IN ('L', 'S')))
68 AND (itemloc_itemsite_id=itemsite_id)
69 AND (itemsite_warehous_id=warehous_id)
70 AND (itemsite_item_id=<? value("item_id") ?>)
71 <? if exists("warehous_id") ?>
72 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
74 <? if exists("site") ?>
75 AND (itemsite_warehous_id=<? value("site")?>)
77 <? if exists("lot_serial") ?>
78 AND (formatlotserialnumber(itemloc_ls_id)=<? value("lot_serial")?>)
80 <? if exists("_location_name") ?>
81 AND (formatLocationName(location_id)=<? value("_location_name")?>)
83 <? literal("charClause") ?>
86 SELECT itemsite_id, 2 AS type, warehous_code,
87 itemsite_perishable, NULL AS itemloc_expiration,
88 itemsite_warrpurc, NULL AS itemloc_warrpurc,
94 itemsite_qtyonhand AS qoh
95 <? foreach("char_id_text_list" ?>
96 , NULL AS char<? value("char_id_text_list") ?>
98 <? foreach("char_id_list_list" ?>
99 , NULL AS char<? value("char_id_list_list") ?>
101 <? foreach("char_id_date_list" ?>
102 , NULL AS char<? value("char_id_date_list") ?>
104 FROM itemsite, whsinfo
105 WHERE ((NOT itemsite_loccntrl)
106 AND (itemsite_controlmethod NOT IN ('L', 'S'))
107 AND (itemsite_warehous_id=warehous_id)
108 AND (itemsite_item_id=<? value("item_id") ?>)
109 <? if exists("warehous_id") ?>
110 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
114 ORDER BY warehous_code, locationname, lotserial;