USP_REPORT_MERCHANDISEINVENTORYSALES

create procedure dbo.USP_REPORT_MERCHANDISEINVENTORYSALES
(
    @DEPARTMENTID uniqueidentifier = null,
    @DEPARTMENTQUERY uniqueidentifier = null, 
    @VENDORID uniqueidentifier = null, 
    @FROMDATE date = null,
    @TODATE date = null, 
    @ONLYSHOWITEMSWITHSALES bit = 0 
)
as  
    set nocount on;

    with MERCHANDISEREFUNDS_CTE as
    (
        select
            MERCHANDISEPRODUCTINSTANCEID,
            sum(CREDITITEM.QUANTITY) as REFUNDEDQUANTITY
        from dbo.CREDIT
        inner join dbo.CREDITITEM on
                CREDITITEM.CREDITID = CREDIT.ID
        inner join dbo.SALESORDERITEMMERCHANDISE on
                SALESORDERITEMMERCHANDISE.ID = CREDITITEM.SALESORDERITEMID
        where convert(date, CREDIT.TRANSACTIONDATE) between @FROMDATE and @TODATE
        group by MERCHANDISEPRODUCTINSTANCEID
    ),
    MERCHANDISEWITHSALESORDERS_CTE as 
    (
        select 
            SALESORDERITEM.QUANTITY,
            SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID,
            SALESORDER.ID as SALESORDERID
        from dbo.SALESORDER
        inner join dbo.SALESORDERITEM on
                SALESORDER.ID = SALESORDERITEM.SALESORDERID
        inner join dbo.SALESORDERITEMMERCHANDISE on
                SALESORDERITEM.ID= SALESORDERITEMMERCHANDISE.ID
        where convert(date, SALESORDER.TRANSACTIONDATE) between @FROMDATE and @TODATE
                and SALESORDER.STATUSCODE = 1 -- Completed Orders Only
    )
    select
        MERCHANDISEPRODUCTINSTANCE.BARCODE BARCODE,
        MERCHANDISEPRODUCTINSTANCE.ITEMDETAILS DESCRIPTION,
        MERCHANDISEDEPARTMENT.NAME DEPARTMENT, 
        MERCHANDISEDEPARTMENTCATEGORY.NAME CATEGORY,
        CONSTITUENT.NAME VENDOR,
        MERCHANDISEPRODUCTINSTANCE.ONHANDQUANTITY QUANTITYONHAND,
        isnull(sum(MERCHANDISEWITHSALESORDERS_CTE.QUANTITY), 0) - isnull(MERCHANDISEREFUNDS_CTE.REFUNDEDQUANTITY, 0) as QUANTITYSOLD
    from dbo.MERCHANDISEPRODUCTINSTANCE
    inner join dbo.MERCHANDISEPRODUCT on
            MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID = MERCHANDISEPRODUCT.ID
    inner join dbo.MERCHANDISEDEPARTMENT on 
            MERCHANDISEPRODUCT.MERCHANDISEDEPARTMENTID = MERCHANDISEDEPARTMENT.ID
    inner join dbo.PRODUCTVENDOR on
            PRODUCTVENDOR.PRODUCTID = MERCHANDISEPRODUCT.ID
    left join dbo.CONSTITUENT on 
            CONSTITUENT.ID = PRODUCTVENDOR.VENDORID 
    left join dbo.MERCHANDISEDEPARTMENTCATEGORY on 
            MERCHANDISEPRODUCT.MERCHANDISEDEPARTMENTCATEGORYID = MERCHANDISEDEPARTMENTCATEGORY.ID
    left join MERCHANDISEWITHSALESORDERS_CTE on
            MERCHANDISEWITHSALESORDERS_CTE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEPRODUCTINSTANCE.ID
    left join MERCHANDISEREFUNDS_CTE on
            MERCHANDISEREFUNDS_CTE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEPRODUCTINSTANCE.ID
    where 
    ((@ONLYSHOWITEMSWITHSALES = 0) or (@ONLYSHOWITEMSWITHSALES = 1 
        and (MERCHANDISEWITHSALESORDERS_CTE.SALESORDERID is not null or MERCHANDISEREFUNDS_CTE.MERCHANDISEPRODUCTINSTANCEID is not null)))      
    and (@DEPARTMENTID is null or MERCHANDISEDEPARTMENT.ID = @DEPARTMENTID) 
    and (@DEPARTMENTQUERY is null or MERCHANDISEDEPARTMENT.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@DEPARTMENTQUERY))) 
    and ((@VENDORID is null) or (PRODUCTVENDOR.VENDORID = @VENDORID))
    group by MERCHANDISEPRODUCTINSTANCE.BARCODE,           
        MERCHANDISEPRODUCTINSTANCE.ITEMDETAILS, 
        MERCHANDISEDEPARTMENT.NAME, 
        MERCHANDISEDEPARTMENTCATEGORY.NAME,
        MERCHANDISEPRODUCTINSTANCE.ONHANDQUANTITY, 
        CONSTITUENT.NAME,
        MERCHANDISEREFUNDS_CTE.REFUNDEDQUANTITY  
    order by MERCHANDISEDEPARTMENT.NAME, MERCHANDISEPRODUCTINSTANCE.ITEMDETAILS