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

SELECT shiphead_id, lineitem_id,
       CASE WHEN (level=0) THEN order_number
            ELSE item_number
       END AS order_item,
       CASE WHEN (level=0) THEN customer
            ELSE itemdescription
       END AS cust_desc,
       shiphead_order_type,
       shiphead_number, 
       order_number, 
       customer,
       shiphead_shipdate,
       formatDate(shiphead_shipdate) AS f_shipdate,
       shiphead_tracknum,
       shiphead_freight,
       freight_curr_abbr,
       linenumber,
       item_number,
       itemdescription,
       warehous_code,
       qtyord,
       formatQty(qtyord) AS f_qtyord,
       qtyshipped,
       formatQty(qtyshipped) AS f_qtyshipped,
       notes,
       'qty' AS qtyord_xtnumericrole,
       'qty' AS qtyshipped_xtnumericrole,
       'curr' AS shiphead_freight_xtnumericrole,
       CASE WHEN (COALESCE(linenumber, 0) = 0) THEN '' END AS linenumber_qtdisplayrole,
       CASE WHEN (COALESCE(qtyord, 0) = 0) THEN '' END AS qtyord_qtdisplayrole,
       CASE WHEN (COALESCE(qtyshipped, 0) = 0) THEN '' END AS qtyshipped_qtdisplayrole,
       CASE WHEN (COALESCE(shiphead_freight, 0) = 0) THEN '' END AS shiphead_freight_qtdisplayrole,
       level AS xtindentrole
FROM (
SELECT DISTINCT
       shiphead_id, -1 AS lineitem_id, cohead_number AS sortkey1, shiphead_number AS sortkey2, 0 AS level,
       shiphead_order_type,
       shiphead_number,
       cohead_number AS order_number, 
       (cust_number || '-' || cust_name) AS customer,
       shiphead_shipdate,
       shiphead_tracknum,
       shiphead_freight,
       currConcat(shiphead_freight_curr_id) AS freight_curr_abbr, 
       0 AS linenumber, '' AS item_number,
       '' AS itemdescription,
       '' AS warehous_code,
       0 AS qtyord,
       0 AS qtyshipped,
       firstline(shiphead_notes) AS notes
FROM shiphead JOIN cohead ON (cohead_id=shiphead_order_id)
              JOIN custinfo ON (cust_id=cohead_cust_id)
<? if exists("warehous_id") ?>
              JOIN shipitem ON (shipitem_shiphead_id=shiphead_id)
              JOIN coitem ON (coitem_id=shipitem_orderitem_id)
              JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
<? endif ?>
WHERE ( (shiphead_order_id=cohead_id)
 AND (cohead_cust_id=cust_id)
 AND (shiphead_shipped)
 AND (shiphead_order_type='SO')
<? if exists("startDate") ?>
 AND (shiphead_shipdate BETWEEN <? value("startDate") ?> and <? value("endDate") ?>)
<? endif ?>
<? if exists("warehous_id") ?>
 AND (itemsite_warehous_id = <? value("warehous_id") ?>)
<? endif ?>
<? if exists("sohead_id") ?>
 AND (cohead_id = <? value("sohead_id") ?>)
<? endif ?>
<? if exists("shiphead_id") ?>
 AND (shiphead_id = <? value("shiphead_id") ?>)
<? endif ?>
      ) 
UNION
SELECT shiphead_id, coitem_id AS lineitem_id, cohead_number AS sortkey1, shiphead_number AS sortkey2, 1 AS level,
       '' AS shiphead_order_type,
       '' AS shiphead_number,
       '' AS order_number, 
       '' AS customer,
       CAST(NULL AS DATE) AS shiphead_shipdate,
       '' AS shiphead_tracknum,
       0 AS shiphead_freight,
       '' AS freight_curr_abbr, 
       coitem_linenumber AS linenumber, item_number,
       (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
       warehous_code,
       coitem_qtyord AS qtyord,
       SUM(shipitem_qty) AS qtyshipped,
       '' AS notes
FROM shipitem, shiphead, coitem, cohead, custinfo, itemsite, item, whsinfo
WHERE ( (shipitem_shiphead_id=shiphead_id)
 AND (shipitem_orderitem_id=coitem_id)
 AND (coitem_itemsite_id=itemsite_id)
 AND (coitem_status <> 'X')
 AND (itemsite_item_id=item_id)
 AND (itemsite_warehous_id=warehous_id)
 AND (shiphead_order_id=cohead_id)
 AND (cohead_cust_id=cust_id)
 AND (shiphead_shipped)
 AND (shiphead_order_type='SO')
<? if exists("startDate") ?>
 AND (shiphead_shipdate BETWEEN <? value("startDate") ?> and <? value("endDate") ?>)
<? endif ?>
<? if exists("warehous_id") ?>
 AND (itemsite_warehous_id = <? value("warehous_id") ?>)
<? endif ?>
<? if exists("sohead_id") ?>
 AND (cohead_id = <? value("sohead_id") ?>)
<? endif ?>
<? if exists("shiphead_id") ?>
 AND (shiphead_id = <? value("shiphead_id") ?>)
<? endif ?>
      ) 
GROUP BY shiphead_id, coitem_id, shiphead_order_type, shiphead_number,
         cohead_number, cust_number, cust_name, shiphead_shipdate,
         coitem_linenumber, item_number, item_descrip1, item_descrip2,
         warehous_code, shiphead_tracknum, coitem_qtyord, 
         shiphead_freight, shiphead_freight_curr_id 
<? if exists("MultiWhs") ?>
UNION
SELECT shiphead_id, -1 AS lineitem_id, tohead_number AS sortkey1, shiphead_number AS sortkey2, 0 AS level,
       shiphead_order_type,
       shiphead_number,
       tohead_number AS order_number, 
       tohead_destname AS customer,
       shiphead_shipdate,
       shiphead_tracknum,
       shiphead_freight,
       currConcat(shiphead_freight_curr_id) AS freight_curr_abbr, 
       0 AS linenumber, '' AS item_number,
       '' AS itemdescription,
       '' AS warehous_code,
       0 AS qtyord,
       0 AS qtyshipped,
       firstline(shiphead_notes) AS notes
FROM shiphead, tohead 
WHERE ( (shiphead_order_id=tohead_id)
 AND (shiphead_shipped)
 AND (shiphead_order_type='TO')
<? if exists("startDate") ?>
 AND (shiphead_shipdate BETWEEN <? value("startDate") ?> and <? value("endDate") ?>)
<? endif ?>
<? if exists("warehous_id") ?>
 AND (tohead_src_warehous_id = <? value("warehous_id") ?>)
<? endif ?>
<? if exists("tohead_id") ?>
 AND (tohead_id = <? value("tohead_id") ?>)
<? endif ?>
<? if exists("shiphead_id") ?>
 AND (shiphead_id = <? value("shiphead_id") ?>)
<? endif ?>
      ) 
UNION
SELECT shiphead_id, toitem_id AS lineitem_id, tohead_number AS sortkey1, shiphead_number AS sortkey2, 1 AS level,
       '' AS shiphead_order_type,
       '' AS shiphead_number,
       '' AS order_number, 
       '' AS customer,
       CAST(NULL AS DATE) AS shiphead_shipdate,
       '' AS shiphead_tracknum,
       0 AS shiphead_freight,
       '' AS freight_curr_abbr, 
       toitem_linenumber AS linenumber, item_number,
       (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
       tohead_srcname AS warehous_code,
       toitem_qty_ordered AS qtyord,
       SUM(shipitem_qty) AS qtyshipped,
       '' AS notes
FROM shipitem, shiphead, toitem, tohead, item 
WHERE ( (shipitem_shiphead_id=shiphead_id)
 AND (shipitem_orderitem_id=toitem_id)
 AND (toitem_status <> 'X')
 AND (toitem_item_id=item_id)
 AND (shiphead_order_id=tohead_id)
 AND (shiphead_shipped)
 AND (shiphead_order_type='TO')
<? if exists("startDate") ?>
 AND (shiphead_shipdate BETWEEN <? value("startDate") ?> and <? value("endDate") ?>)
<? endif ?>
<? if exists("warehous_id") ?>
 AND (tohead_src_warehous_id = <? value("warehous_id") ?>)
<? endif ?>
<? if exists("tohead_id") ?>
 AND (tohead_id = <? value("tohead_id") ?>)
<? endif ?>
<? if exists("shiphead_id") ?>
 AND (shiphead_id = <? value("shiphead_id") ?>)
<? endif ?>
      ) 
GROUP BY shiphead_id, toitem_id, shiphead_order_type, shiphead_number,
         tohead_number, shiphead_shipdate,
         toitem_linenumber, item_number, item_descrip1, item_descrip2,
         tohead_srcname, shiphead_tracknum, toitem_qty_ordered, 
         shiphead_freight, shiphead_freight_curr_id, notes
<? endif ?>
   ) AS data
ORDER BY sortkey1, sortkey2, level, linenumber DESC;
