UFN_AMPROIMPORT_ITEMDATA

            CREATE function dbo.UFN_AMPROIMPORT_ITEMDATA
            (
                @ITEMID uniqueidentifier
            )
            returns table
            as
            return
            (
                select
                    AUCTIONITEM.SEQUENCEID - 9999999 as [MAPPINGID],
                    AUCTIONITEM.NAME,
                    coalesce(AUCTIONITEMCATEGORY.NAME, N'') as [CATEGORY],
                    coalesce(AUCTIONITEMSUBCATEGORY.NAME, N'') as [SUBCATEGORY],
                    AUCTIONITEM.DESCRIPTION,
                    AUCTIONITEM.EXPIRATIONDATE,
                    REVENUE.DATE,
                    AUCTIONITEM.TRANSACTIONVALUE as [VALUE],
                    AUCTIONITEM.TRANSACTIONMINIMUMBID as [MINIMUMBID],
                    REVENUE.CONSTITUENTID as [DONORID],
                    case 
                        when exists (select ID from dbo.WRITEOFF where WRITEOFF.REVENUEID = REVENUE.ID) then cast(1 as bit)
                        else cast(0 as bit)
                    end as [WRITTENOFF],
                    case
                        when exists (select ID from dbo.AUCTIONITEMREVENUEPURCHASE where AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID) then cast(1 as bit)
                        else cast(0 as bit)
                    end as [PURCHASED],
                    coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID) as [SITEID],
                    AUCTIONITEM.EVENTAUCTIONID,
                    case when AUCTIONITEM.TYPECODE = 1 then cast(1 as bit) else cast(0 as bit) end as [ISPACKAGE],
                    case when exists(select ID from dbo.REVENUEPOSTED where REVENUEPOSTED.ID = REVENUE.ID) then cast(1 as bit) else cast(0 as bit)  end as [POSTED]
                from dbo.AUCTIONITEM
                left join dbo.REVENUE on REVENUE.ID = AUCTIONITEM.REVENUEAUCTIONDONATIONID
                left join dbo.AUCTIONITEMCATEGORY on AUCTIONITEMCATEGORY.ID = AUCTIONITEM.AUCTIONITEMCATEGORYID
                left join dbo.AUCTIONITEMSUBCATEGORY on AUCTIONITEMSUBCATEGORY.ID = AUCTIONITEM.AUCTIONITEMSUBCATEGORYID

                left join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                left join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                left join dbo.DESIGNATIONLEVEL DL1 on DL1.ID = DESIGNATION.DESIGNATIONLEVEL1ID
                left join dbo.DESIGNATIONLEVEL DL2 on DL2.ID = DESIGNATION.DESIGNATIONLEVEL2ID
                left join dbo.DESIGNATIONLEVEL DL3 on DL3.ID = DESIGNATION.DESIGNATIONLEVEL3ID
                left join dbo.DESIGNATIONLEVEL DL4 on DL4.ID = DESIGNATION.DESIGNATIONLEVEL4ID
                left join dbo.DESIGNATIONLEVEL DL5 on DL5.ID = DESIGNATION.DESIGNATIONLEVEL5ID

                where AUCTIONITEM.ID = @ITEMID
            )