USP_DATALIST_MERCHANDISEPRODUCTINSTANCEHISTORY

CREATE procedure dbo.USP_DATALIST_MERCHANDISEPRODUCTINSTANCEHISTORY
(
    @MERCHANDISEPRODUCTID uniqueidentifier,
    @ACTIONTYPECODE tinyint = null,
    @SOURCETYPECODE tinyint = null,
    @BATCHID uniqueidentifier = null,
    @MERCHANDISEADJUSTMENTREASONCODEID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @DATESELECTTYPE tinyint = 0,
    @FROMDATE date = null,
    @THROUGHDATE date = null
)
as
set nocount on;

declare @ISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)

select 
    mpi.ID as PARENTID,
    mpih.ACTIONTYPE as RELATIONALCAPTION,
    cast('3b76e98f-82a3-462e-8873-25984e93419c' as uniqueidentifier) as VIEWFORMID,
    mpih.ID as VIEWFORMCONTEXTID,
    mpi.ID as ITEMID,
    mpi.BARCODE,
    mpi.ITEMDETAILS,
    mpih.ID as ITEMHISTORYID,
    cast(mpih.ACTIONDATE as datetime) as ACTIONDATE,
    mpih.ACTIONTYPECODE,
    mpih.ACTIONTYPE,
    case mpih.SOURCETYPECODE when 0 then mpih.SOURCETYPE when 1 then mpih.SOURCETYPE + ' ' + BATCH.BATCHNUMBER end as SOURCELABEL,
    mpih.ONHANDQUANTITY,
    mpih.QUANTITY,
    case mpih.ACTIONTYPECODE 
        when 2 then mpih.QUANTITY - mpih.ONHANDQUANTITY
        else mpih.QUANTITY
    end as CHANGEQUANTITY,
    case mpih.ACTIONTYPECODE 
        when 2 then mpih.QUANTITY
        else mpih.ONHANDQUANTITY + mpih.QUANTITY
    end as RESULTINGQUANTITY,
    mpih.COST,
    mpih.AVERAGECOST,
    mpih.VENDORID as VENDORID,
    CONSTITUENT.NAME as VENDORNAME,
    mpih.MERCHANDISEADJUSTMENTREASONCODEID,
    marc.DESCRIPTION as REASON,
    mpih.BATCHID
from dbo.MERCHANDISEPRODUCTINSTANCE as mpi
inner join dbo.MERCHANDISEPRODUCTINSTANCEHISTORY as mpih
    on (mpi.MERCHANDISEPRODUCTID = @MERCHANDISEPRODUCTID) and (mpi.ID = mpih.MERCHANDISEPRODUCTINSTANCEID)
left outer join dbo.CONSTITUENT
    on mpih.VENDORID = CONSTITUENT.ID
left outer join dbo.MERCHANDISEADJUSTMENTREASONCODE as marc
    on mpih.MERCHANDISEADJUSTMENTREASONCODEID = marc.ID
left outer join dbo.BATCH
    on mpih.BATCHID = BATCH.ID
where ((@ACTIONTYPECODE is null) or (mpih.ACTIONTYPECODE = @ACTIONTYPECODE))
    and ((@SOURCETYPECODE is null) or (mpih.SOURCETYPECODE = @SOURCETYPECODE))    
    and ((@BATCHID is null) or (mpih.BATCHID = @BATCHID))
    and ((@MERCHANDISEADJUSTMENTREASONCODEID is null) or (mpih.MERCHANDISEADJUSTMENTREASONCODEID = @MERCHANDISEADJUSTMENTREASONCODEID))        
    and ((@ISSYSADMIN = 1) or (dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID, mpih.MERCHANDISEADJUSTMENTREASONCODEID) = 1))
    and ((@DATESELECTTYPE = 0) or ((not(@DATESELECTTYPE = 0)) and (ACTIONDATE between @FROMDATE and dateadd(day, 1, @THROUGHDATE))))    

union all

select distinct
    null as PARENTID,
    mpi.ITEMDETAILS as RELATIONALCAPTION,
    cast('539aa176-45b9-4ad9-af37-2a883a55ed04' as uniqueidentifier) as VIEWFORMID,
    mpi.ID as VIEWFORMCONTEXTID,
    mpi.ID as ITEMID,
    mpi.BARCODE,
    mpi.ITEMDETAILS,
    null as ITEMHISTORYID,
    null as ACTIONDATE,
    null as ACTIONTYPECODE,
    null as ACTIONTYPE,
    null as SOURCELABEL,
    null as ONHANDQUANTITY,
    null as QUANTITY,
    null as CHANGEQUANTITY,
    null as RESULTINGQUANTITY,
    null as COST,
    null as AVERAGECOST,
    null as VENDORID,
    null as VENDORNAME,
    null as MERCHANDISEADJUSTMENTREASONCODEID,
    null as REASON,
    null as BATCHID
from dbo.MERCHANDISEPRODUCTINSTANCE as mpi
inner join dbo.MERCHANDISEPRODUCTINSTANCEHISTORY as mpih
    on (mpi.MERCHANDISEPRODUCTID = @MERCHANDISEPRODUCTID) and (mpi.ID = mpih.MERCHANDISEPRODUCTINSTANCEID)
where ((@ACTIONTYPECODE is null) or (mpih.ACTIONTYPECODE = @ACTIONTYPECODE))
    and ((@SOURCETYPECODE is null) or (mpih.SOURCETYPECODE = @SOURCETYPECODE))    
    and ((@BATCHID is null) or (mpih.BATCHID = @BATCHID))
    and ((@MERCHANDISEADJUSTMENTREASONCODEID is null) or (mpih.MERCHANDISEADJUSTMENTREASONCODEID = @MERCHANDISEADJUSTMENTREASONCODEID))        
    and ((@ISSYSADMIN = 1) or (dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID, mpih.MERCHANDISEADJUSTMENTREASONCODEID) = 1))
    and ((@DATESELECTTYPE = 0) or ((not(@DATESELECTTYPE = 0)) and (ACTIONDATE between @FROMDATE and dateadd(day, 1, @THROUGHDATE))))    

order by ACTIONDATE desc