USP_DATAFORMTEMPLATE_VIEW_MERCHANDISEPRODUCTINSTANCE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MERCHANDISEPRODUCTINSTANCE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @BARCODE nvarchar(24) = null output,
    @ONHANDQUANTITY integer = null output,
    @AVGCOST money = null output,
    @PRICE money = null output,
    @ISACTIVE bit = null output,
    @COST money = null output,
    @LOOKUPCODE nvarchar(25) = null output,
    @VENDORID uniqueidentifier = null output,
    @VENDOR nvarchar(100) = null output,
    @LOOKUPITEMS xml = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select @DATALOADED = 1,
           @BARCODE = MERCHANDISEPRODUCTINSTANCE.BARCODE,
           @ONHANDQUANTITY = MERCHANDISEPRODUCTINSTANCE.ONHANDQUANTITY,
           @AVGCOST = MERCHANDISEPRODUCTINSTANCE.COST,
           @PRICE = MERCHANDISEPRODUCTINSTANCE.SALEPRICE,
           @ISACTIVE = MERCHANDISEPRODUCTINSTANCE.ISACTIVE,
           @LOOKUPCODE = MERCHANDISEPRODUCTINSTANCE.LOOKUPCODE,
           @COST = MERCHANDISEPRODUCTINSTANCEVENDOR.COST,
           @VENDORID = coalesce(LASTSHIPMENT.VENDORID, PRODUCTVENDOR.VENDORID),
           @VENDOR = coalesce(HISTORICALCONSTITUENT.NAME, CONSTITUENT.NAME),
           @LOOKUPITEMS = dbo.UFN_MERCHANDISEPRODUCTINSTANCE_GETLOOKUPITEMS_TOITEMLISTXML(@ID)
    from dbo.MERCHANDISEPRODUCTINSTANCE

    left outer join dbo.PRODUCTVENDOR
        on MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID = PRODUCTVENDOR.PRODUCTID
    left outer join dbo.CONSTITUENT
        on PRODUCTVENDOR.VENDORID = CONSTITUENT.ID
    inner join dbo.MERCHANDISEPRODUCTINSTANCEVENDOR on MERCHANDISEPRODUCTINSTANCEVENDOR.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEPRODUCTINSTANCE.ID
    outer apply dbo.UFN_MERCHANDISEPRODUCTINSTANCE_GETLASTHISTORYITEM(@ID, 0) LASTSHIPMENT
    left outer join dbo.CONSTITUENT HISTORICALCONSTITUENT
        on LASTSHIPMENT.VENDORID = HISTORICALCONSTITUENT.ID

    where MERCHANDISEPRODUCTINSTANCE.ID = @ID;

    return 0;