CREATE            VIEW [dbo].[SII_vwYardsOnHandDueRpt]
AS
SELECT  isnull(wwr.greige_item, (SELECT top 1 item FROM item WHERE family_code = 'GREIGE' AND coi.item LIKE item.item + '%')) greige_item, 
        isnull(wwr.greige_yds, 0) greige_yds, 
        isnull(wwr.finished_item, (SELECT top 1 item FROM item WHERE family_code = 'FINISHED' AND coi.item LIKE item.item + '%')) finished_item, 
        isnull(wwr.finished_yds, 0) finished_yds, 
        isnull(wwr.packed_item, coi.item) packed_item, 
        isnull(wwr.packed_desc, it.description) packed_desc, 
        isnull(wwr.packed_qty, isnull((SELECT sum(qty_on_hand) FROM lot_loc WHERE item = coi.item AND loc LIKE 'P%'), 0)) packed_qty, 
        isnull(wwr.pack_uom, it.u_m) pack_uom, 
        coi.co_num, 
        coi.co_line, 
        coi.co_release, 
        coi.due_date, 
        (coi.qty_ordered - coi.qty_shipped) * dbo.SIIItemSalesUOM2(coi.co_num, coi.co_line, coi.co_release) qty_due, 
        coi.u_m sales_uom, 
        ca.[name] customer_name,
	dbo._SIIQtyDueInYards(coi.co_num, coi.co_line, coi.co_release) qty_due_yds
FROM    coitem coi WITH(NOLOCK)
INNER JOIN item it WITH(NOLOCK)
ON      it.item = coi.item 
LEFT OUTER JOIN co WITH(NOLOCK)
ON      co.co_num = coi.co_num 
LEFT OUTER JOIN custaddr ca WITH(NOLOCK)
ON      ca.cust_num = co.cust_num 
    AND ca.cust_seq = co.cust_seq 
LEFT OUTER JOIN SII_vwWipWebReport wwr 
ON      wwr.packed_item = coi.item 
WHERE   co.stat  IN ('O', 'S') 
    AND coi.stat IN ('O', 'S')

and (loc like 'P%' or loc like 'S%')