-- Group: countTag
-- Name: detail
-- Notes: used by dspCountTagEditList
-- 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 *,
       CASE WHEN (xtindentrole = 1) THEN NULL
     <? if exists("varianceValue") ?>
            WHEN (ABS(variancecost) >  <? value("varianceValue") ?>) THEN 'error'
     <? elseif exists("variancePercent") ?>
            WHEN (ABS(varianceprcnt * 100.0) >  <? value("variancePercent") ?>) THEN 'error'
     <? else ?>
            ELSE NULL
     <? endif ?>
       END AS qtforegroundrole,
       CASE WHEN (xtindentrole = 1) THEN NULL
            WHEN (qohafter IS NOT NULL) THEN 'emphasis'
       END AS qohafter_qtforegroundrole,
       CASE WHEN (xtindentrole = 0) THEN NULL ELSE '' END AS invcnt_priority_qtdisplayrole,
       'qty' AS qoh_xtnumericrole,
       'qty' AS qohafter_xtnumericrole,
       'qty' AS variance_xtnumericrole,
       'percent' AS varianceprcnt_xtnumericrole,
       'curr' AS variancecost_xtnumericrole
FROM (
      SELECT invcnt_id, -1 AS cntslip_id, invcnt_priority,
             COALESCE(invcnt_tagnumber, 'Misc.') AS tagnumber,
             invcnt_tagdate AS tagdate,
             item_number, (item_descrip1 || item_descrip2) AS item_descrip, warehous_code,
	     CASE WHEN (location_id IS NOT NULL) THEN
               formatLocationName(location_id)
               ELSE <? value("all") ?> END AS loc_specific,
             qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) AS qoh,
      <? if exists("showSlips") ?>
             calcTotalSlipQty(invcnt_id) AS qohafter,
             (calcTotalSlipQty(invcnt_id) - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id)) AS variance,
             CASE WHEN (calcTotalSlipQty(invcnt_id) IS NULL) THEN NULL
                  WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) > 0)) THEN 1
                  WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) < 0)) THEN -1
                  WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) = 0)) THEN 0
               ELSE ((1 - (calcTotalSlipQty(invcnt_id) / qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) * -1)
             END AS varianceprcnt,
             (itemCost(itemsite_id) * (calcTotalSlipQty(invcnt_id) - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) AS variancecost,
      <? else ?>
             COALESCE(invcnt_qoh_after, 0.0) AS qohafter,
             (invcnt_qoh_after - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id)) AS variance,
             CASE WHEN (invcnt_qoh_after IS NULL) THEN NULL
                  WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after > 0)) THEN 1
                  WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after < 0)) THEN -1
                  WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after = 0)) THEN 0
               ELSE ((1 - (invcnt_qoh_after / qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) * -1)
             END AS varianceprcnt,
             (itemCost(itemsite_id) * (invcnt_qoh_after - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) AS variancecost,
      <? endif ?>
             item_number AS orderby,
             0 AS xtindentrole
      FROM invcnt LEFT OUTER JOIN location ON (invcnt_location_id=location_id),
     item, whsinfo, itemsite
      WHERE ((invcnt_itemsite_id=itemsite_id)
         AND (itemsite_item_id=item_id)
         AND (itemsite_warehous_id=warehous_id)
         AND (NOT invcnt_posted)
      <? if exists("warehous_id") ?>
         AND (itemsite_warehous_id=<? value("warehous_id") ?>)
      <? endif ?>
      <? if exists("classcode_id") ?>
         AND (item_classcode_id=<? value("classcode_id") ?>)
      <? elseif exists("classcode_pattern") ?>
         AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ <? value("classcode_pattern") ?>)))
      <? elseif exists("plancode_id") ?>
         AND (itemsite_plancode_id=<? value("plancode_id") ?>)
      <? elseif exists("plancode_pattern") ?>
         AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ <? value("plancode_pattern") ?>)))
      <? endif ?>
      <? if exists("showSlips") ?>
            )
      UNION
      SELECT invcnt_id, cntslip_id, invcnt_priority,
             cntslip_number AS tagnumber,
             cntslip_entered AS tagdate,
             CASE WHEN (cntslip_posted) THEN <? value("posted") ?>
               ELSE <? value("unposted") ?>
             END AS item_number,
             '' AS item_descrip,
             '' AS warehous_code,
             '' AS loc_specific,
             NULL AS qoh,
             cntslip_qty AS qohafter,
             NULL AS variance, NULL AS varianceprcnt, 0 AS variancecost,
             item_number AS orderby,
             1 AS xtindentrole
      FROM cntslip, invcnt, itemsite, item
      WHERE ((cntslip_cnttag_id=invcnt_id)
         AND (invcnt_itemsite_id=itemsite_id)
         AND (itemsite_item_id=item_id)
         AND (NOT invcnt_posted)
       <? if exists("warehous_id") ?>
         AND (itemsite_warehous_id=<? value("warehous_id") ?>)
       <? endif ?>
       <? if exists("classcode_id") ?>
         AND (item_classcode_id=<? value("classcode_id") ?>)
       <? elseif exists("classcode_pattern") ?>
         AND (item_classcode_id IN (SELECT classcode_id FROM classcode WHERE (classcode_code ~ <? value("classcode_pattern") ?>)))
       <? elseif exists("plancode_id") ?>
         AND (itemsite_plancode_id=<? value("plancode_id") ?>)
       <? elseif exists("plancode_pattern") ?>
         AND (itemsite_plancode_id IN (SELECT plancode_id FROM plancode WHERE (plancode_code ~ <? value("plancode_pattern") ?>)))
       <? endif ?>
       <? endif ?>
            )
) AS dummy
ORDER BY invcnt_priority DESC, orderby, invcnt_id, cntslip_id;
