-- Group: breederDistributionVariance
-- Name: detail
-- Notes: used by dspBreederDistributionVarianceByItem, dspBreederDistributionVarianceByWarehouse

SELECT brdvar_id, brdvar_postdate,
       pi.item_number AS pitem_number,
       ci.item_number AS citem_number,
       brdvar_stdqtyper,
       brdvar_stdqtyper * brdvar_wo_qty AS stdqty,
       brdvar_actqtyper,
       brdvar_actqtyper * brdvar_wo_qty AS actqty,
       brdvar_actqtyper-brdvar_stdqtyper AS qtypervariance,
       (brdvar_actqtyper-brdvar_stdqtyper) / brdvar_stdqtyper AS percentvariance,
       'qtyper'   AS brdvar_stdqtyper_xtnumericrole,
       'qty'     AS stdqty_xtnumericrole,
       'qtyper'  AS brdvar_actqtyper_xtnumericrole,
       'qty'     AS actqty_xtnumericrole,
       'qtyper'  AS qtypervariance_xtnumericrole,
       'percent' AS percentvariance_xtnumericrole 
FROM brdvar, itemsite AS ps, itemsite AS cs, item AS ci, item AS pi
WHERE ( (brdvar_parent_itemsite_id=ps.itemsite_id)
    AND (brdvar_itemsite_id=cs.itemsite_id)
<? if exists("item_id") ?>
    AND (ps.itemsite_item_id=<? value("item_id") ?>)
    AND (pi.item_id=ci.item_id)
<? else ?>
    AND (ps.itemsite_item_id=pi.item_id)
<? endif ?>
    AND (cs.itemsite_item_id=ci.item_id)
    AND (brdvar_postdate BETWEEN <? value("startDate") ?>
                             AND <? value("endDate") ?>)
<? if exists("warehous_id") ?>
    AND (ps.itemsite_warehous_id=<? value("warehous_id") ?>)
<? endif ?>
) 
ORDER BY brdvar_postdate, pitem_number
<? if not exists("item_id") ?>
     , citem_number 
<? endif ?>
;
