USP_REPORT_MERCHANDISEINVENTORYWORKSHEET

create procedure dbo.USP_REPORT_MERCHANDISEINVENTORYWORKSHEET
(
    @DEPARTMENTID uniqueidentifier = null,
    @DEPARTMENTQUERY uniqueidentifier = null,    
    @INCLUDEINACTIVE bit = 0
)
as
    set nocount on;

    declare @DEPARTMENTS table (
        ID uniqueidentifier,
        NAME nvarchar(100)    
    );

    if @DEPARTMENTID is null and @DEPARTMENTQUERY is null
        insert into @DEPARTMENTS
            select
                ID,
                NAME
            from dbo.MERCHANDISEDEPARTMENT;

    if @DEPARTMENTID is not null and @DEPARTMENTQUERY is null
        insert into @DEPARTMENTS 
            select
                ID,
                NAME
            from dbo.MERCHANDISEDEPARTMENT
            where ID = @DEPARTMENTID;

    if @DEPARTMENTQUERY is not null and @DEPARTMENTID is null
        insert into @DEPARTMENTS
            select 
                QUERYRESULTS.ID,
                MERCHANDISEDEPARTMENT.NAME
            from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@DEPARTMENTQUERY) QUERYRESULTS
            inner join dbo.MERCHANDISEDEPARTMENT
                on QUERYRESULTS.ID = MERCHANDISEDEPARTMENT.ID;

    select
        DEPARTMENTS.NAME DEPARTMENT,
        MERCHANDISEPRODUCTINSTANCE.BARCODE,
        MERCHANDISEPRODUCTINSTANCE.ITEMDETAILS DESCRIPTION,
        MERCHANDISEPRODUCTINSTANCE.ONHANDQUANTITY
    from dbo.MERCHANDISEPRODUCTINSTANCE
    inner join dbo.MERCHANDISEPRODUCT
        on MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID = MERCHANDISEPRODUCT.ID
    inner join @DEPARTMENTS DEPARTMENTS
        on MERCHANDISEPRODUCT.MERCHANDISEDEPARTMENTID = DEPARTMENTS.ID
    where (@INCLUDEINACTIVE = 1 or (MERCHANDISEPRODUCT.ISACTIVE = 1 and MERCHANDISEPRODUCTINSTANCE.ISACTIVE = 1));

    return 0;