-- Group: itemSites
-- Name: detail
-- Notes:
-- Notes:
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.

SELECT itemsite_id,warehous_code,itemsite_qtyonhand, itemsite_active, 
       CASE WHEN ( (itemsite_loccntrl) OR
                   (itemsite_controlmethod IN ('L', 'S')) ) THEN 1
            ELSE 0
       END AS altId,
       CASE WHEN itemsite_controlmethod='R' THEN <? value("regular") ?>
                           WHEN itemsite_controlmethod='N' THEN <? value("none") ?>
                           WHEN itemsite_controlmethod='L' THEN <? value("lot") ?>
                           WHEN itemsite_controlmethod='S' THEN <? value("serial") ?>
                      END AS controlmethod,
         itemsite_loccntrl,
         item_number,
         (item_descrip1 || ' ' || item_descrip2) AS description, 
         uom_name,
         CASE WHEN (itemsite_sold) THEN TEXT(itemsite_soldranking)
         END AS soldranking,
         CASE WHEN (itemsite_datelastcount=startOfTime()) THEN NULL
           ELSE itemsite_datelastcount
         END AS datelastcount,
         CASE WHEN (itemsite_datelastused=startOfTime()) THEN NULL
           ELSE itemsite_datelastused
         END AS datelastused,
         itemsite_abcclass, itemsite_cyclecountfreq,
         'qty' AS itemsite_qtyonhand_xtnumericrole,
        <? value("na") ?> AS soldranking_xtnullrole,
        <? value("never") ?> AS datelastused_xtnullrole,
        <? value("never") ?> AS datelastcount_xtnullrole             
FROM item, uom, itemsite, whsinfo
WHERE ((itemsite_warehous_id=warehous_id)
         AND (itemsite_item_id=item_id)
         AND(item_inv_uom_id=uom_id)
       <? if exists("search_pattern") ?>
         AND ((item_number ~* <? value("search_pattern") ?>)
           OR (COALESCE(item_descrip1,'') || ' ' || COALESCE(item_descrip2,'') ~* <? value("search_pattern") ?>)
           OR (warehous_code ~* <? value("search_pattern") ?>))
       <? endif ?>
       <? if exists("item_id") ?>
         AND (item_id=<? value("item_id") ?>)
       <? endif ?>
       <? if exists("classcode_id") ?>
         AND (item_classcode_id=<? value("classcode_id") ?>)
       <? endif ?>
       <? if exists("itemgrp_id") ?>
         AND (item_id IN (SELECT itemgrpitem_item_id 
                          FROM itemgrpitem 
                          WHERE (itemgrpitem_itemgrp_id=<? value("itemgrp_id") ?>)))
       <? endif ?>
       <? if exists("plancode_id") ?>
         AND (itemsite_plancode_id=<? value("plancode_id") ?>)
       <? endif ?>
       <? if exists("costcat_id") ?>
         AND (itemsite_costcat_id=<? value("costcat_id") ?>)
       <? endif ?>
       <? if exists("classcode_pattern") ?>
         AND (item_classcode_id IN (SELECT classcode_id 
                                    FROM classcode 
                                    WHERE (classcode_code ~ <? value("classcode_pattern") ?>)))
       <? endif ?>
       <? if exists("itemgrp_pattern") ?>
         AND (item_id IN (SELECT itemgrpitem_item_id 
                          FROM itemgrpitem, itemgrp 
                          WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id) 
                              AND (itemgrp_name ~ <? value("itemgrp_pattern") ?>) ) ))
       <? endif ?>
       <? if exists("plancode_pattern") ?>
         AND (itemsite_plancode_id IN (SELECT plancode_id 
                                       FROM plancode 
                                       WHERE (plancode_code ~ <? value("plancode_pattern") ?>)))
       <? endif ?>
       <? if exists("costcat_pattern") ?>
         AND (itemsite_costcat_id IN (SELECT costcat_id 
                                      FROM costcat 
                                      WHERE (costcat_code ~ <? value("costcat_pattern") ?>)))
       <? endif ?>
     <? if exists("warehous_id") ?>
       AND (warehous_id=<? value("warehous_id") ?>)
     <? endif ?>
     <? if exists("loc_cntrl") ?>
       AND (itemsite_loccntrl = <? value("loc_cntrl") ?>)
     <? endif ?>
   <? if not exists("showInactive") ?>
     AND (itemsite_active)
   <? endif ?>
       )
ORDER BY item_number, warehous_code;
