USP_AMPROEXPORT_GETITEMDATA

            CREATE procedure dbo.USP_AMPROEXPORT_GETITEMDATA
            (
                @AUCTIONEVENTID uniqueidentifier
            )
            as
            set nocount on;

            declare @SEQUENCEIDOFFSET int = 9999999;

            with [CTE] as
            (
                select
                    AUCTIONITEM.SEQUENCEID - @SEQUENCEIDOFFSET as [SEQUENCEID],
                    AUCTIONITEM.ID as [AUCTIONITEMID],
                    AUCTIONITEM.LOOKUPID,
                    AUCTIONITEM.NAME,
                    (select top 1 NAME from AUCTIONITEMCATEGORY where ID = AUCTIONITEM.AUCTIONITEMCATEGORYID) as [CATEGORY],
                    (select top 1 NAME from AUCTIONITEMSUBCATEGORY where ID = AUCTIONITEM.AUCTIONITEMSUBCATEGORYID) as [SUBCATEGORY],
                    AUCTIONITEM.DESCRIPTION,
                    AUCTIONITEM.TRANSACTIONVALUE as [VALUE],
                    AUCTIONITEM.TRANSACTIONMINIMUMBID as [MINIMUMBID],
                    REVENUE.DATE,
                    AUCTIONITEM.EXPIRATIONDATE,
                    REVENUE.CONSTITUENTID,

                    CONSTITUENT.LOOKUPID as [CONSTITUENTLOOKUPID],
                    CONSTITUENT.SEQUENCEID - @SEQUENCEIDOFFSET as [CONSTITUENTSEQUENCEID],
                    CONSTITUENT.ISORGANIZATION as [CONSTITUENTISORGANIZATION],
                    (select DESCRIPTION from dbo.TITLECODE where TITLECODE.ID = CONSTITUENT.TITLECODEID) as [CONSTITUENTTITLE],
                    CONSTITUENT.FIRSTNAME as [CONSTITUENTFIRSTNAME],
                    CONSTITUENT.KEYNAME as [CONSTITUENTKEYNAME],
                    case 
                        when NAMEFORMAT.ID is null then CONSTITUENT.NAME 
                        else NAMEFORMAT.FORMATTEDNAME 
                    end as [CONSTITUENTPRIMARYSALUTATION],

                    coalesce((select DESCRIPTION from ADDRESSTYPECODE where ADDRESSTYPECODE.ID = [PRIMARYADDRESS].ADDRESSTYPECODEID), '') as [CONSTITUENTPRIMARYADDRESSTYPE],
                    coalesce([PRIMARYADDRESS].ADDRESSBLOCK, '') as [CONSTITUENTPRIMARYADDRESSBLOCK],
                    coalesce([PRIMARYADDRESS].CITY, '') as [CONSTITUENTPRIMARYADDRESSCITY],
                    coalesce((select STATE.ABBREVIATION from dbo.STATE where STATE.ID = [PRIMARYADDRESS].STATEID), '') as [CONSTITUENTPRIMARYADDRESSSTATE],
                    coalesce(PRIMARYADDRESS.POSTCODE, '') as [CONSTITUENTPRIMARYADDRESSPOSTCODE],
                    coalesce((select COUNTRY.DESCRIPTION from dbo.COUNTRY where COUNTRY.ID = [PRIMARYADDRESS].COUNTRYID), '') as [CONSTITUENTPRIMARYADDRESSCOUNTRY],

                    coalesce((select DESCRIPTION from dbo.PHONETYPECODE where PHONETYPECODE.ID = [PRIMARYPHONE].PHONETYPECODEID), '') as [CONSTITUENTPRIMARYPHONETYPE],
                    coalesce([PRIMARYPHONE].NUMBER, '') as [CONSTITUENTPRIMARYPHONE],

                    coalesce((select DESCRIPTION from dbo.EMAILADDRESSTYPECODE where EMAILADDRESSTYPECODE.ID = [PRIMARYEMAIL].EMAILADDRESSTYPECODEID), '') as [CONSTITUENTPRIMARYEMAILTYPE],
                    coalesce([PRIMARYEMAIL].EMAILADDRESS, '') as [CONSTITUENTPRIMARYEMAIL],

                    AUCTIONITEM.PACKAGEID,
                    (select top 1 SEQUENCEID - @SEQUENCEIDOFFSET from dbo.AUCTIONITEM [PACKAGE] where [PACKAGE].ID = AUCTIONITEM.PACKAGEID) as PACKAGESEQUENCEID,

                    case 
                        when AUCTIONITEM.PACKAGEID is null then
                            case when AUCTIONITEMRESERVATION.ID is not null or AUCTIONITEMREVENUEPURCHASE.ID is not null then 1 else 0 end
                        else
                            case when    (
                                            select count(*) from dbo.AUCTIONITEM [PACKAGE] 
                                            left join dbo.AUCTIONITEMRESERVATION on AUCTIONITEMRESERVATION.AUCTIONITEMID = [PACKAGE].ID
                                            left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID = [PACKAGE].ID
                                            where PACKAGE.ID = AUCTIONITEM.PACKAGEID 
                                                and (AUCTIONITEMRESERVATION.ID is not null or AUCTIONITEMREVENUEPURCHASE.ID is  not null)
                                        ) > 0 
                                then 1 
                                else 0 
                            end
                    end as [ISRESERVED]
                from dbo.AUCTIONITEM
                inner join dbo.REVENUE on REVENUE.ID = AUCTIONITEM.REVENUEAUCTIONDONATIONID
                inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                left join dbo.ADDRESS as [PRIMARYADDRESS] on [PRIMARYADDRESS].CONSTITUENTID = CONSTITUENT.ID and [PRIMARYADDRESS].ISPRIMARY = 1
                left join dbo.PHONE as [PRIMARYPHONE] on [PRIMARYPHONE].CONSTITUENTID = CONSTITUENT.ID and [PRIMARYPHONE].ISPRIMARY = 1
                left join dbo.EMAILADDRESS as [PRIMARYEMAIL] on [PRIMARYEMAIL].CONSTITUENTID = CONSTITUENT.ID and [PRIMARYEMAIL].ISPRIMARY = 1
                left join dbo.NAMEFORMAT on NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYSALUTATION = 1
                left join dbo.AUCTIONITEMRESERVATION on AUCTIONITEMRESERVATION.AUCTIONITEMID = AUCTIONITEM.ID
                left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
                left join dbo.WRITEOFF on REVENUE.ID = WRITEOFF.REVENUEID
                where 
                    AUCTIONITEM.TYPECODE = 0 
                    and EVENTAUCTIONID = @AUCTIONEVENTID
                    and WRITEOFF.ID is null
                )
                select
                    SEQUENCEID,
                    AUCTIONITEMID,
                    LOOKUPID,
                    NAME,
                    CATEGORY,
                    SUBCATEGORY,
                    DESCRIPTION,
                    VALUE,
                    MINIMUMBID,
                    DATE,
                    EXPIRATIONDATE,
                    CONSTITUENTID,
                    CONSTITUENTLOOKUPID,
                    CONSTITUENTSEQUENCEID,
                    CONSTITUENTISORGANIZATION,
                    CONSTITUENTTITLE,
                    CONSTITUENTFIRSTNAME,
                    CONSTITUENTKEYNAME,
                    CONSTITUENTPRIMARYSALUTATION,
                    CONSTITUENTPRIMARYADDRESSTYPE,
                    CONSTITUENTPRIMARYADDRESSBLOCK,
                    CONSTITUENTPRIMARYADDRESSCITY,
                    CONSTITUENTPRIMARYADDRESSSTATE,
                    CONSTITUENTPRIMARYADDRESSPOSTCODE,
                    CONSTITUENTPRIMARYADDRESSCOUNTRY,
                    CONSTITUENTPRIMARYPHONETYPE,
                    CONSTITUENTPRIMARYPHONE,
                    CONSTITUENTPRIMARYEMAILTYPE,
                    CONSTITUENTPRIMARYEMAIL,
                    PACKAGEID,
                    PACKAGESEQUENCEID
                from [CTE]
                where ISRESERVED = 0