USP_DATAFORMTEMPLATE_LOAD_AMPROIMPORTROW

The load procedure used by the edit dataform template "AuctionMaestro Pro Batch Row Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ORIGINATINGBATCHROWID	uniqueidentifier	INOUT	Originating batch ID
@EVENTID	uniqueidentifier	INOUT	Event
@SEQUENCE	int	INOUT	Sequence
@ROWID	uniqueidentifier	INOUT	Row ID
@RECORDTYPECODE	tinyint	INOUT	Record type
@MESSAGES	xml	INOUT
@WASLOOKEDUP	bit	INOUT	Record was looked up at import time
@AUTOMATCHSTATUS	nvarchar(max)	INOUT	Automatch status
@MESSAGETEXT	nvarchar(max)	INOUT	Import messages
@SUMMARY	nvarchar(max)	INOUT	Record summary
@EVENT_NAME	nvarchar(100)	INOUT	Event name
@EVENT_STARTDATE	datetime	INOUT	Event start date
@CONSTITUENT_LINKID	uniqueidentifier	INOUT	Linked Constituent
@CONSTITUENT_MAPPINGID	int	INOUT	Constituent mapping id
@CONSTITUENT_UPDATEDTITLE	nvarchar(100)	INOUT	Title
@CONSTITUENT_UPDATEDFIRSTNAME	nvarchar(50)	INOUT	First name
@CONSTITUENT_UPDATEDKEYNAME	nvarchar(100)	INOUT	Last name
@CONSTITUENT_UPDATEDADDRESSBLOCK	nvarchar(150)	INOUT	Address block
@CONSTITUENT_UPDATEDCITY	nvarchar(50)	INOUT	City
@CONSTITUENT_UPDATEDPOSTCODE	nvarchar(12)	INOUT	Post code
@CONSTITUENT_UPDATEDSTATEID	uniqueidentifier	INOUT	State
@CONSTITUENT_UPDATEDCOUNTRYID	uniqueidentifier	INOUT	Country
@CONSTITUENT_UPDATEDPHONE	nvarchar(100)	INOUT	Phone
@CONSTITUENT_UPDATEDEMAILADDRESS	nvarchar(100)	INOUT	Email address
@CONSTITUENT_ISINDIVIDUAL	bit	INOUT	Constituent is an individual
@CONSTITUENT_LOOKUPID	nvarchar(255)	INOUT	Constituent lookup id
@CONSTITUENT_AUTOMATCHSTATUSCODE	tinyint	INOUT	Constituent automatch status
@CONSTITUENT_UPDATECONTACTINFO	bit	INOUT
@CONSTITUENT_NEWSECURITYATTRIBUTEID	uniqueidentifier	INOUT	Constituent security attribute
@CONSTITUENT_NEWSITEID	uniqueidentifier	INOUT	Constituent site
@REGISTRANT_CONSTITUENTID	uniqueidentifier	INOUT	Registrant constituent ID
@REGISTRANT_EVENTPRICEID	uniqueidentifier	INOUT	Registration event price ID
@REGISTRANT_AMOUNT	money	INOUT	Registration amount
@AUCTIONITEM_LINKID	uniqueidentifier	INOUT	Linked auction item
@AUCTIONITEM_MAPPINGID	int	INOUT	Auction item mapping id
@AUCTIONITEM_UPDATEDNAME	nvarchar(100)	INOUT	Name
@AUCTIONITEM_UPDATEDDESCRIPTION	nvarchar(255)	INOUT	Description
@AUCTIONITEM_UPDATEDEXPIRATIONDATE	datetime	INOUT	Expiration date
@AUCTIONITEM_UPDATEDDONATIONDATE	datetime	INOUT	Donation date
@AUCTIONITEM_UPDATEDCATEGORY	nvarchar(100)	INOUT	Category
@AUCTIONITEM_UPDATEDSUBCATEGORY	nvarchar(100)	INOUT	Subcategory
@AUCTIONITEM_UPDATEDMINIMUMBID	money	INOUT	Minimum bid
@AUCTIONITEM_UPDATEDVALUE	money	INOUT	Value
@AUCTIONITEM_CONSTITUENTID	uniqueidentifier	INOUT	Donor
@AUCTIONITEM_ADJUSTMENTDATE	datetime	INOUT	Adjustment date
@AUCTIONITEM_ADJUSTMENTPOSTDATE	datetime	INOUT	Adjustment post date
@AUCTIONITEM_ADJUSTMENTREASONCODEID	uniqueidentifier	INOUT	Adjustment reason
@AUCTIONITEM_ADJUSTMENTREASON	nvarchar(300)	INOUT	Adjustment description
@PURCHASE_CONSTITUENTID	uniqueidentifier	INOUT	Purchase constituent
@PURCHASE_BIDDERNUMBER	nvarchar(10)	INOUT	Purchase bidder number
@PURCHASE_DATE	datetime	INOUT	Purchase date
@PURCHASE_AMOUNT	money	INOUT	Purchase amount
@PURCHASE_POSTDATE	datetime	INOUT	Purchase post date
@PURCHASE_POSTSTATUSCODE	tinyint	INOUT	Purchase post status
@PURCHASE_PAYMENTMETHODCODE	tinyint	INOUT	Purchase payment method
@PURCHASE_CREDITTYPECODEID	uniqueidentifier	INOUT	Purchase credit card type
@PURCHASE_DETAIL	xml	INOUT	Purchase detail
@PACKAGE_LINKID	uniqueidentifier	INOUT	Linked package
@PACKAGE_MAPPINGID	int	INOUT	Package mapping id
@PACKAGE_NAME	nvarchar(100)	INOUT	Name
@PACKAGE_DESCRIPTION	nvarchar(100)	INOUT	Description
@PACKAGE_EXPIRATIONDATE	datetime	INOUT	Expiration date
@PACKAGE_CATEGORY	nvarchar(100)	INOUT	Category
@PACKAGE_SUBCATEGORY	nvarchar(100)	INOUT	Subcategory
@PACKAGE_MINIMUMBID	money	INOUT	Minimum bid
@EVENTBASECURRENCYID	uniqueidentifier	INOUT	Event base currency
@TRANSACTIONTYPE	nvarchar(max)	INOUT

Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_LOAD_AMPROIMPORTROW
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @ORIGINATINGBATCHROWID uniqueidentifier = null output,
                        @EVENTID uniqueidentifier = null output,
                        @SEQUENCE int = null output,
                        @ROWID uniqueidentifier = null output,

                        @RECORDTYPECODE tinyint = null output,
                        @MESSAGES xml = null output,
                        @WASLOOKEDUP bit = null output, 
                        @AUTOMATCHSTATUS nvarchar(max) = null output,
                        @MESSAGETEXT nvarchar(max) = null output,
                        @SUMMARY nvarchar(max) = null output,

                        --Event fields
                        @EVENT_NAME nvarchar(100) = null output,
                        @EVENT_STARTDATE datetime = null output,

                        --Constituent Fields
                        @CONSTITUENT_LINKID uniqueidentifier = null output,
                        @CONSTITUENT_MAPPINGID int = null output, 
                        @CONSTITUENT_UPDATEDTITLE nvarchar(100) = null output,
                        @CONSTITUENT_UPDATEDFIRSTNAME nvarchar(50) = null output,
                        @CONSTITUENT_UPDATEDKEYNAME nvarchar(100) = null output,
                        @CONSTITUENT_UPDATEDADDRESSBLOCK nvarchar(150) = null output,
                        @CONSTITUENT_UPDATEDCITY nvarchar(50) = null output,
                        @CONSTITUENT_UPDATEDPOSTCODE nvarchar(12) = null output,
                        @CONSTITUENT_UPDATEDSTATEID uniqueidentifier = null output,
                        @CONSTITUENT_UPDATEDCOUNTRYID uniqueidentifier = null output,
                        @CONSTITUENT_UPDATEDPHONE nvarchar(100) = null output,
                        @CONSTITUENT_UPDATEDEMAILADDRESS nvarchar(100) = null output,
                        @CONSTITUENT_ISINDIVIDUAL bit = null output,
                        @CONSTITUENT_LOOKUPID nvarchar(255) = null output,
                        @CONSTITUENT_AUTOMATCHSTATUSCODE tinyint = null output,
                        @CONSTITUENT_UPDATECONTACTINFO bit = null output,
                        @CONSTITUENT_NEWSECURITYATTRIBUTEID uniqueidentifier = null output,
                        @CONSTITUENT_NEWSITEID uniqueidentifier = null output,

                        --Registrant Fields,
                        @REGISTRANT_CONSTITUENTID uniqueidentifier = null output,
                        @REGISTRANT_EVENTPRICEID uniqueidentifier = null output,
                        @REGISTRANT_AMOUNT money = null output,

                        --AuctionItem Fields
                        @AUCTIONITEM_LINKID uniqueidentifier = null output,
                        @AUCTIONITEM_MAPPINGID int = null output,
                        @AUCTIONITEM_UPDATEDNAME nvarchar(100) = null output,
                        @AUCTIONITEM_UPDATEDDESCRIPTION nvarchar(255) = null output,
                        @AUCTIONITEM_UPDATEDEXPIRATIONDATE datetime = null output,
                        @AUCTIONITEM_UPDATEDDONATIONDATE datetime = null output,
                        @AUCTIONITEM_UPDATEDCATEGORY nvarchar(100) = null output,
                        @AUCTIONITEM_UPDATEDSUBCATEGORY nvarchar(100) = null output,
                        @AUCTIONITEM_UPDATEDMINIMUMBID money = null output,
                        @AUCTIONITEM_UPDATEDVALUE money = null output,
                        @AUCTIONITEM_CONSTITUENTID uniqueidentifier = null output,
                        @AUCTIONITEM_ADJUSTMENTDATE datetime = null output,
                        @AUCTIONITEM_ADJUSTMENTPOSTDATE datetime = null output,
                        @AUCTIONITEM_ADJUSTMENTREASONCODEID uniqueidentifier = null output,
                        @AUCTIONITEM_ADJUSTMENTREASON nvarchar(300) = null output,

                        --Purchase Fields
                        @PURCHASE_CONSTITUENTID uniqueidentifier = null output,
                        @PURCHASE_BIDDERNUMBER nvarchar(10) = null output,
                        @PURCHASE_DATE datetime = null output,
                        @PURCHASE_AMOUNT money = null output,
                        @PURCHASE_POSTDATE datetime = null output,
                        @PURCHASE_POSTSTATUSCODE tinyint = null output,
                        @PURCHASE_PAYMENTMETHODCODE tinyint = null output,
                        @PURCHASE_CREDITTYPECODEID uniqueidentifier = null output,
                        @PURCHASE_DETAIL xml = null output,

                        --Package Fields
                        @PACKAGE_LINKID uniqueidentifier = null output,
                        @PACKAGE_MAPPINGID int = null output,
                        @PACKAGE_NAME nvarchar(100) = null output,
                        @PACKAGE_DESCRIPTION nvarchar(100) = null output,
                        @PACKAGE_EXPIRATIONDATE datetime = null output,
                        @PACKAGE_CATEGORY nvarchar(100) = null output,
                        @PACKAGE_SUBCATEGORY nvarchar(100) = null output,
                        @PACKAGE_MINIMUMBID money = null output,

                        @EVENTBASECURRENCYID uniqueidentifier = null output,
                        @TRANSACTIONTYPE nvarchar(max) = null output
                    )
                    as
                    set nocount on;

                    set @DATALOADED = 0;

                    declare @BATCHID uniqueidentifier = null;
                    declare @ISEXCEPTIONBATCH bit = 0;
                    declare @REALORIGINATINGBATCHROWID uniqueidentifier;

                    select
                        @ROWID = @ID,
                        @ORIGINATINGBATCHROWID = @ID, --Use our own batch ID here, so when an exception batch gets created, it gets created with its parent
                        @SEQUENCE = BATCHAMPROIMPORT.SEQUENCE,
                        @RECORDTYPECODE = BATCHAMPROIMPORT.RECORDTYPECODE,
                        @WASLOOKEDUP = BATCHAMPROIMPORT.WASLOOKEDUP,
                        @BATCHID = BATCH.ID,
                        @ISEXCEPTIONBATCH = case when BATCH.ORIGINATINGBATCHID is null then 0 else 1 end,
                        @REALORIGINATINGBATCHROWID = ORIGINATINGBATCHROWID
                    from dbo.BATCHAMPROIMPORT
                    inner join dbo.BATCH on BATCH.ID = BATCHAMPROIMPORT.BATCHID
                    where BATCHAMPROIMPORT.ID = @ID
                    and BATCHAMPROIMPORT.RECORDTYPECODE <> 255;

                    declare @REALBATCHID uniqueidentifier = @BATCHID;
                    while @ISEXCEPTIONBATCH = 1
                    begin
                        set @ISEXCEPTIONBATCH = 0;

                        set @REALBATCHID = @BATCHID;

                        select 
                            @ISEXCEPTIONBATCH = 1,
                            @BATCHID = BATCH.ORIGINATINGBATCHID
                        from dbo.BATCH where BATCH.ID = @BATCHID and ORIGINATINGBATCHID is not null;
                    end

                    select top 1 
                        @EVENTID = BATCHAMPRODATA.EVENTAUCTIONID,
                        @EVENTBASECURRENCYID = EVENT.BASECURRENCYID
                    from dbo.BATCHAMPRODATA
                    left join dbo.EVENT on EVENT.ID = BATCHAMPRODATA.EVENTAUCTIONID
                    where BATCHAMPRODATA.ID = @REALBATCHID;

                    select @MESSAGES = 
                        (
                            select ID, SEQUENCE, MESSAGETYPECODE, MESSAGE
                            from dbo.BATCHAMPROIMPORTMESSAGE
                            where AMPROIMPORTID = @ID
                            for xml raw('ITEM'),type,elements,root('MESSAGES'),binary base64
                        )

                    if @RECORDTYPECODE = 1
                    begin
                        --Event
                        select
                            @DATALOADED = 1,
                            @EVENT_NAME = EVENT_NAME,
                            @EVENT_STARTDATE = EVENT_STARTDATE
                        from dbo.BATCHAMPROIMPORTEVENT
                        where ID = @ID;
                    end
                    else if @RECORDTYPECODE = 2
                    begin
                        --Constituent 
                        select 
                            @DATALOADED = 1,
                            @CONSTITUENT_LINKID = CONSTITUENT_LINKID,
                            @CONSTITUENT_MAPPINGID = CONSTITUENT_MAPPINGID,
                            @CONSTITUENT_UPDATEDTITLE = CONSTITUENT_UPDATEDTITLE,
                            @CONSTITUENT_UPDATEDFIRSTNAME = CONSTITUENT_UPDATEDFIRSTNAME,
                            @CONSTITUENT_UPDATEDKEYNAME = CONSTITUENT_UPDATEDKEYNAME,
                            @CONSTITUENT_UPDATEDADDRESSBLOCK = CONSTITUENT_UPDATEDADDRESSBLOCK,
                            @CONSTITUENT_UPDATEDCITY = CONSTITUENT_UPDATEDCITY,
                            @CONSTITUENT_UPDATEDPOSTCODE = CONSTITUENT_UPDATEDPOSTCODE,
                            @CONSTITUENT_UPDATEDSTATEID = CONSTITUENT_UPDATEDSTATEID,
                            @CONSTITUENT_UPDATEDCOUNTRYID = CONSTITUENT_UPDATEDCOUNTRYID,
                            @CONSTITUENT_UPDATEDPHONE = CONSTITUENT_UPDATEDPHONE,
                            @CONSTITUENT_UPDATEDEMAILADDRESS = CONSTITUENT_UPDATEDEMAILADDRESS,
                            @CONSTITUENT_ISINDIVIDUAL = CONSTITUENT_ISINDIVIDUAL,
                            @CONSTITUENT_LOOKUPID = CONSTITUENT_LOOKUPID,
                            @CONSTITUENT_AUTOMATCHSTATUSCODE = CONSTITUENT_AUTOMATCHSTATUSCODE,
                            @CONSTITUENT_UPDATECONTACTINFO = CONSTITUENT_UPDATECONTACTINFO,
                            @CONSTITUENT_NEWSECURITYATTRIBUTEID = CONSTITUENT_NEWSECURITYATTRIBUTEID, 
                            @CONSTITUENT_NEWSITEID = CONSTITUENT_NEWSITEID
                        from dbo.BATCHAMPROIMPORTCONSTITUENT
                        where ID = @ID;

                    end
                    else if @RECORDTYPECODE = 3
                    begin
                        --Registrant
                        select 
                            @DATALOADED = 1,
                            @REGISTRANT_CONSTITUENTID = REGISTRANT_CONSTITUENTID,
                            @REGISTRANT_EVENTPRICEID = REGISTRANT_EVENTPRICEID,
                            @REGISTRANT_AMOUNT= REGISTRANT_AMOUNT
                        from dbo.BATCHAMPROIMPORTREGISTRANT
                        where ID = @ID;
                    end
                    else if @RECORDTYPECODE = 4
                    begin
                        --Auction Item
                        select
                            @DATALOADED = 1,
                            @AUCTIONITEM_LINKID = AUCTIONITEM_LINKID,
                            @AUCTIONITEM_MAPPINGID = AUCTIONITEM_MAPPINGID,
                            @AUCTIONITEM_UPDATEDNAME = AUCTIONITEM_UPDATEDNAME,
                            @AUCTIONITEM_UPDATEDDESCRIPTION = AUCTIONITEM_UPDATEDDESCRIPTION,
                            @AUCTIONITEM_UPDATEDEXPIRATIONDATE = AUCTIONITEM_UPDATEDEXPIRATIONDATE,
                            @AUCTIONITEM_UPDATEDDONATIONDATE = AUCTIONITEM_UPDATEDDONATIONDATE,
                            @AUCTIONITEM_UPDATEDCATEGORY = AUCTIONITEM_UPDATEDCATEGORY,
                            @AUCTIONITEM_UPDATEDSUBCATEGORY = AUCTIONITEM_UPDATEDSUBCATEGORY,
                            @AUCTIONITEM_UPDATEDMINIMUMBID = AUCTIONITEM_UPDATEDMINIMUMBID,
                            @AUCTIONITEM_UPDATEDVALUE = AUCTIONITEM_UPDATEDVALUE,
                            @AUCTIONITEM_CONSTITUENTID = AUCTIONITEM_CONSTITUENTID,
                            @AUCTIONITEM_ADJUSTMENTDATE = AUCTIONITEM_ADJUSTMENTDATE,
                            @AUCTIONITEM_ADJUSTMENTPOSTDATE = AUCTIONITEM_ADJUSTMENTPOSTDATE,
                            @AUCTIONITEM_ADJUSTMENTREASONCODEID = AUCTIONITEM_ADJUSTMENTREASONCODEID,
                            @AUCTIONITEM_ADJUSTMENTREASON = AUCTIONITEM_ADJUSTMENTREASON

                        from dbo.BATCHAMPROIMPORTITEM
                        where ID = @ID;
                    end
                    else if @RECORDTYPECODE = 5
                    begin
                        --Purchase
                        select
                            @DATALOADED = 1,
                            @PURCHASE_CONSTITUENTID = PURCHASE_CONSTITUENTID,
                            @PURCHASE_BIDDERNUMBER = PURCHASE_BIDDERNUMBER,
                            @PURCHASE_AMOUNT = PURCHASE_AMOUNT,
                            @PURCHASE_DATE = PURCHASE_DATE,
                            @PURCHASE_POSTDATE = PURCHASE_POSTDATE,
                            @PURCHASE_POSTSTATUSCODE = PURCHASE_POSTSTATUSCODE,
                            @PURCHASE_PAYMENTMETHODCODE = PURCHASE_PAYMENTMETHODCODE,
                            @PURCHASE_CREDITTYPECODEID = PURCHASE_CREDITTYPECODEID
                        from dbo.BATCHAMPROIMPORTPURCHASE
                        where ID = @ID;

                        select @PURCHASE_DETAIL = 
                            (
                                select    ID, PURCHASEDETAIL_ITEMID, PURCHASEDETAIL_AMOUNT, PURCHASE_REGISTRANTID as [PURCHASEDETAIL_REGISTRANTID],
                                        PURCHASEDETAIL_TRANSACTIONTYPECODE, PURCHASEDETAIL_DESIGNATIONID, @EVENTBASECURRENCYID as [PURCHASEDETAIL_CURRENCYID]
                                from dbo.BATCHAMPROIMPORTPURCHASEDETAIL
                                where PURCHASEDETAIL_PURCHASEID = @ID
                                for xml raw('ITEM'),type,elements,root('PURCHASE_DETAIL'),binary base64
                            )
                    end
                    else if @RECORDTYPECODE = 6
                    begin
                        --Package
                        select
                            @DATALOADED = 1,
                            @PACKAGE_LINKID = PACKAGE_LINKID,
                            @PACKAGE_MAPPINGID = PACKAGE_MAPPINGID,
                            @PACKAGE_NAME = PACKAGE_NAME,
                            @PACKAGE_DESCRIPTION = PACKAGE_DESCRIPTION,
                            @PACKAGE_EXPIRATIONDATE = PACKAGE_EXPIRATIONDATE,
                            @PACKAGE_CATEGORY = PACKAGE_CATEGORY,
                            @PACKAGE_SUBCATEGORY = PACKAGE_SUBCATEGORY,
                            @PACKAGE_MINIMUMBID = PACKAGE_MINIMUMBID
                        from dbo.BATCHAMPROIMPORTPACKAGE
                        where ID = @ID;
                    end
                    else if @RECORDTYPECODE = 254
                    begin
                        --Error
                        set @DATALOADED = 1;
                    end

                    --WI 102207 This is a weird batch because rows can refer to other rows in the batch. A purchase could be from 
                    --a constituent is going to be created in a previous row. When an exception batch gets created, the purchasing 
                    --constituent will still refer to a row in the committed batch and not the new exception batch. Using the 
                    --ORIGINATINGBATCHROWID, we can fix these IDs. Putting it here so when the exception batch gets loaded, it will 
                    --get loaded with the updated IDs. Should the row be saved later, the IDs will be updated. The SP is smart enough to 
                    --not update IDs which are already correct.
                    exec dbo.USP_AMPROIMPORT_FIXIDS @ID, @REALORIGINATINGBATCHROWID, 
                        @CONSTITUENT_LINKID output, @REGISTRANT_CONSTITUENTID output, @AUCTIONITEM_LINKID output, 
                        @AUCTIONITEM_CONSTITUENTID output, @PURCHASE_CONSTITUENTID output, @PURCHASE_DETAIL output, 
                        @PACKAGE_LINKID output