
 
       
CREATE OR REPLACE FUNCTION x_dragon_cohead_test_shipping_qty(integer)
    RETURNS  NUMERIC
    
AS $BODY$
DECLARE        
    i_cohead_id  ALIAS FOR $1;
   
    v_ret NUMERIC;
    v_cohead_number TEXT;
    
BEGIN

    SELECT cohead_number INTO v_cohead_number FROM cohead WHERE cohead_id = i_cohead_id;

    SELECT count(invhist_itemsite_id) into v_ret
      FROM
     (
    
            SELECT 
                invhist_itemsite_id,
                join_item.item_number as item_number,
                
                    COALESCE ((SELECT SUM(COALESCE(coitem_qtyreturned,0)) FROM coitem WHERE coitem_cohead_id = i_cohead_id and coitem_itemsite_id = invhist_itemsite_id
                ) ,0)  +
                
                (COALESCE ((SELECT SUM(COALESCE(coitem_qtyshipped,0)) FROM coitem WHERE coitem_cohead_id = i_cohead_id and coitem_itemsite_id = invhist_itemsite_id
                ), 0)  * -1 )  as rec_total,
                
                
                COALESCE(SUM(
    CASE WHEN invdetail_qty > 0 THEN 0 ELSE invdetail_qty END
    ),0) as tx_shipped,
    
    COALESCE(SUM(
    CASE WHEN invdetail_qty < 0 THEN 0 ELSE invdetail_qty END
    ),0) as tx_returned,
    
                COALESCE(SUM(invdetail_qty),0) as tx_total 
                
                
                
                
                FROM invdetail 
            
                LEFT JOIN invhist AS join_invhist ON
                    invdetail.invdetail_invhist_id = join_invhist.invhist_id
                LEFT JOIN invfifo AS join_invfifo ON
                    invdetail.invdetail_id = join_invfifo.invfifo_invdetail_id
                LEFT JOIN itemsite AS join_itemsite ON
                    join_invhist.invhist_itemsite_id = join_itemsite.itemsite_id
                LEFT JOIN item AS join_item ON
                    join_itemsite.itemsite_item_id = join_item.item_id 
                WHERE ( 
                    invhist_ordtype = 'SO'
                    AND 
                    (
                        invhist_ordnumber LIKE v_cohead_number || '-%'
                        OR
                        invhist_ordnumber = v_cohead_number
                    )
                ) 
                GROUP BY invhist_itemsite_id, join_item.item_number 
            
                ORDER BY join_item.item_number ASC
                
     ) x
     WHERE
        tx_total != rec_total;
    
    return v_ret;
                           
        RETURN v_ret;        
     END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  x_dragon_cohead_test_shipping_qty(integer)
  OWNER TO admin;
  
  
 
 