USP_DATAFORMTEMPLATE_ADD_AMPROIMPORTROW

The save procedure used by the add dataform template "AuctionMaestro Pro Batch Row Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@BATCHID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@ORIGINATINGBATCHROWID	uniqueidentifier	IN	Originating batch ID
@EVENTID	uniqueidentifier	IN	Event
@SEQUENCE	int	IN	Sequence
@ROWID	uniqueidentifier	IN	Row ID
@RECORDTYPECODE	tinyint	IN	Record type
@MESSAGES	xml	IN
@WASLOOKEDUP	bit	IN	Record was looked up at import time
@EVENT_NAME	nvarchar(100)	IN	Event name
@EVENT_STARTDATE	datetime	IN	Event start date
@CONSTITUENT_LINKID	uniqueidentifier	IN	Linked Constituent
@CONSTITUENT_MAPPINGID	int	IN	Constituent mapping id
@CONSTITUENT_UPDATEDTITLE	nvarchar(100)	IN	Title
@CONSTITUENT_UPDATEDFIRSTNAME	nvarchar(50)	IN	First name
@CONSTITUENT_UPDATEDKEYNAME	nvarchar(100)	IN	Last name
@CONSTITUENT_UPDATEDADDRESSBLOCK	nvarchar(150)	IN	Address block
@CONSTITUENT_UPDATEDCITY	nvarchar(50)	IN	City
@CONSTITUENT_UPDATEDPOSTCODE	nvarchar(12)	IN	Post code
@CONSTITUENT_UPDATEDSTATEID	uniqueidentifier	IN	State
@CONSTITUENT_UPDATEDCOUNTRYID	uniqueidentifier	IN	Country
@CONSTITUENT_UPDATEDPHONE	nvarchar(100)	IN	Phone
@CONSTITUENT_UPDATEDEMAILADDRESS	nvarchar(100)	IN	Email address
@CONSTITUENT_ISINDIVIDUAL	bit	IN	Constituent is an individual
@CONSTITUENT_LOOKUPID	nvarchar(255)	IN	Constituent lookup id
@CONSTITUENT_AUTOMATCHSTATUSCODE	tinyint	IN	Constituent automatch status
@CONSTITUENT_UPDATECONTACTINFO	bit	IN
@CONSTITUENT_NEWSECURITYATTRIBUTEID	uniqueidentifier	IN	Constituent security attribute
@CONSTITUENT_NEWSITEID	uniqueidentifier	IN	Constituent site
@REGISTRANT_CONSTITUENTID	uniqueidentifier	IN	Registrant constituent ID
@REGISTRANT_EVENTPRICEID	uniqueidentifier	IN	Registration event price ID
@REGISTRANT_AMOUNT	money	IN	Registration amount
@AUCTIONITEM_LINKID	uniqueidentifier	IN	Linked auction item
@AUCTIONITEM_MAPPINGID	int	IN	Auction item mapping id
@AUCTIONITEM_UPDATEDNAME	nvarchar(100)	IN	Name
@AUCTIONITEM_UPDATEDDESCRIPTION	nvarchar(255)	IN	Description
@AUCTIONITEM_UPDATEDEXPIRATIONDATE	datetime	IN	Expiration date
@AUCTIONITEM_UPDATEDDONATIONDATE	datetime	IN	Donation date
@AUCTIONITEM_UPDATEDCATEGORY	nvarchar(100)	IN	Category
@AUCTIONITEM_UPDATEDSUBCATEGORY	nvarchar(100)	IN	Subcategory
@AUCTIONITEM_UPDATEDMINIMUMBID	money	IN	Minimum bid
@AUCTIONITEM_UPDATEDVALUE	money	IN	Value
@AUCTIONITEM_CONSTITUENTID	uniqueidentifier	IN	Donor
@AUCTIONITEM_ADJUSTMENTDATE	datetime	IN	Adjustment date
@AUCTIONITEM_ADJUSTMENTPOSTDATE	datetime	IN	Adjustment post date
@AUCTIONITEM_ADJUSTMENTREASONCODEID	uniqueidentifier	IN	Adjustment reason
@AUCTIONITEM_ADJUSTMENTREASON	nvarchar(300)	IN	Adjustment description
@PURCHASE_CONSTITUENTID	uniqueidentifier	IN	Purchase constituent
@PURCHASE_BIDDERNUMBER	nvarchar(10)	IN	Purchase bidder number
@PURCHASE_DATE	datetime	IN	Purchase date
@PURCHASE_AMOUNT	money	IN	Purchase amount
@PURCHASE_POSTDATE	datetime	IN	Purchase post date
@PURCHASE_POSTSTATUSCODE	tinyint	IN	Purchase post status
@PURCHASE_PAYMENTMETHODCODE	tinyint	IN	Purchase payment method
@PURCHASE_CREDITTYPECODEID	uniqueidentifier	IN	Purchase credit card type
@PURCHASE_DETAIL	xml	IN	Purchase detail
@PACKAGE_LINKID	uniqueidentifier	IN	Linked package
@PACKAGE_MAPPINGID	int	IN	Package mapping id
@PACKAGE_NAME	nvarchar(100)	IN	Name
@PACKAGE_DESCRIPTION	nvarchar(100)	IN	Description
@PACKAGE_EXPIRATIONDATE	datetime	IN	Expiration date
@PACKAGE_CATEGORY	nvarchar(100)	IN	Category
@PACKAGE_SUBCATEGORY	nvarchar(100)	IN	Subcategory
@PACKAGE_MINIMUMBID	money	IN	Minimum bid

Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_AMPROIMPORTROW
                    (
                        @ID uniqueidentifier = null output,
                        @CHANGEAGENTID uniqueidentifier,
                        @BATCHID uniqueidentifier,
                        @ORIGINATINGBATCHROWID uniqueidentifier, 

                        @EVENTID uniqueidentifier = null,
                        @SEQUENCE int,
                        @ROWID uniqueidentifier = null,

                        @RECORDTYPECODE tinyint,
                        @MESSAGES xml = null,
                        @WASLOOKEDUP bit = 0, 



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

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


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

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

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

                        --Package Fields
                        @PACKAGE_LINKID uniqueidentifier = null,
                        @PACKAGE_MAPPINGID int = 0,
                        @PACKAGE_NAME nvarchar(100) = '',
                        @PACKAGE_DESCRIPTION nvarchar(100) = '',
                        @PACKAGE_EXPIRATIONDATE datetime = null,
                        @PACKAGE_CATEGORY nvarchar(100) = '',
                        @PACKAGE_SUBCATEGORY nvarchar(100) = '',
                        @PACKAGE_MINIMUMBID money = 0
                    )
                    as
                    set nocount on;

                    begin try
                        if @ID is null
                            set @ID = newid();

                        if @ORIGINATINGBATCHROWID is null
                            set @ORIGINATINGBATCHROWID = @ID;

                        if @CHANGEAGENTID is null
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        declare @CHANGEDATE datetime = getdate();
            declare @CHANGEDATEEARLIESTTIME datetime;
            select @CHANGEDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE);

                        if @EVENT_NAME is null
                            set @EVENT_NAME = N'';

                        if @CONSTITUENT_UPDATEDTITLE is null
                            set @CONSTITUENT_UPDATEDTITLE = N'';

                        if @CONSTITUENT_UPDATEDFIRSTNAME is null
                            set @CONSTITUENT_UPDATEDFIRSTNAME = N'';

                        if @CONSTITUENT_UPDATEDKEYNAME is null
                            set @CONSTITUENT_UPDATEDKEYNAME = N'';

                        if @CONSTITUENT_UPDATEDADDRESSBLOCK is null
                            set @CONSTITUENT_UPDATEDADDRESSBLOCK = N'';

                        if @CONSTITUENT_UPDATEDCITY is null
                            set @CONSTITUENT_UPDATEDCITY = N'';

                        if @CONSTITUENT_UPDATEDPOSTCODE is null
                            set @CONSTITUENT_UPDATEDPOSTCODE = N'';

                        if @CONSTITUENT_UPDATEDPHONE is null
                            set @CONSTITUENT_UPDATEDPHONE = N'';

                        if @CONSTITUENT_UPDATEDEMAILADDRESS is null
                            set @CONSTITUENT_UPDATEDEMAILADDRESS = N'';

                        if @CONSTITUENT_LOOKUPID is null
                            set @CONSTITUENT_LOOKUPID = N'';

                        if @CONSTITUENT_AUTOMATCHSTATUSCODE is null
                            set @CONSTITUENT_AUTOMATCHSTATUSCODE = 0;

                        if @REGISTRANT_AMOUNT is null
                            set @REGISTRANT_AMOUNT = 0;

                        if @AUCTIONITEM_UPDATEDNAME is null
                            set @AUCTIONITEM_UPDATEDNAME = N'';

                        if @AUCTIONITEM_UPDATEDDESCRIPTION is null
                            set @AUCTIONITEM_UPDATEDDESCRIPTION = N'';

                        if @AUCTIONITEM_UPDATEDCATEGORY is null
                            set @AUCTIONITEM_UPDATEDCATEGORY = N'';

                        if @AUCTIONITEM_UPDATEDSUBCATEGORY is null
                            set @AUCTIONITEM_UPDATEDSUBCATEGORY = N'';

                        if @AUCTIONITEM_UPDATEDMINIMUMBID is null
                            set @AUCTIONITEM_UPDATEDMINIMUMBID = 0;

                        if @AUCTIONITEM_UPDATEDVALUE is null
                            set @AUCTIONITEM_UPDATEDVALUE = 0;

                        if @AUCTIONITEM_ADJUSTMENTREASON is null
                            set @AUCTIONITEM_ADJUSTMENTREASON = N'';

                        if @PURCHASE_AMOUNT is null
                            set @PURCHASE_AMOUNT = 0;

                        if @PURCHASE_BIDDERNUMBER is null
                            set @PURCHASE_BIDDERNUMBER = N'';

                        if @PURCHASE_POSTSTATUSCODE is null
                            set @PURCHASE_POSTSTATUSCODE = 1;

                        if @PURCHASE_PAYMENTMETHODCODE is null
                            set @PURCHASE_PAYMENTMETHODCODE = 0;

                        if @PACKAGE_MAPPINGID Is null
                            set @PACKAGE_MAPPINGID = 0;

                        if @PACKAGE_NAME is null
                            set @PACKAGE_NAME = N'';

                        if @PACKAGE_DESCRIPTION is null
                            set @PACKAGE_DESCRIPTION = '';

                        if @PACKAGE_CATEGORY is null
                            set @PACKAGE_CATEGORY = N'';

                        if @PACKAGE_SUBCATEGORY is null
                            set @PACKAGE_SUBCATEGORY = N'';

                        if @PACKAGE_MINIMUMBID is null
                            set @PACKAGE_MINIMUMBID = 0;

                        insert into dbo.BATCHAMPROIMPORT(ID, BATCHID, ORIGINATINGBATCHROWID, SEQUENCE, RECORDTYPECODE, WASLOOKEDUP, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, EVENTID)
                            values(@ID, @BATCHID, @ORIGINATINGBATCHROWID, @SEQUENCE, @RECORDTYPECODE, @WASLOOKEDUP, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, @EVENTID);

                        if not @MESSAGES is null
                            insert into dbo.BATCHAMPROIMPORTMESSAGE(ID, AMPROIMPORTID, SEQUENCE, MESSAGETYPECODE, MESSAGE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                select 
                                    newid(),
                                    @ID,
                                    T.c.value('SEQUENCE[1]', 'int'),
                                    T.c.value('MESSAGETYPECODE[1]', 'tinyint'),
                                    T.c.value('MESSAGE[1]', 'nvarchar(2000)'),
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CHANGEDATE,
                                    @CHANGEDATE
                                from @MESSAGES.nodes('/MESSAGES/ITEM') T(c);

                        if @RECORDTYPECODE = 255
                        begin
                            raiserror('ERR_UNKNOWN_RECORDTYPE', 13, 1);
                        end
                        else if @RECORDTYPECODE = 1
                        begin
                            --Event
                            insert into dbo.BATCHAMPROIMPORTEVENT
                                        (ID, EVENT_NAME, EVENT_STARTDATE,
                                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values    (@ID, @EVENT_NAME, @EVENT_STARTDATE,
                                        @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                        end
                        else if @RECORDTYPECODE = 2
                        begin
                            --Constituent
                            insert into dbo.BATCHAMPROIMPORTCONSTITUENT 
                                        (ID, CONSTITUENT_LINKID, CONSTITUENT_MAPPINGID, CONSTITUENT_UPDATEDTITLE, 
                                        CONSTITUENT_UPDATEDFIRSTNAME, CONSTITUENT_UPDATEDKEYNAME, CONSTITUENT_UPDATEDADDRESSBLOCK,
                                        CONSTITUENT_UPDATEDCITY, CONSTITUENT_UPDATEDPOSTCODE, CONSTITUENT_UPDATEDSTATEID,
                                        CONSTITUENT_UPDATEDCOUNTRYID, CONSTITUENT_UPDATEDPHONE, CONSTITUENT_UPDATEDEMAILADDRESS,
                                        CONSTITUENT_ISINDIVIDUAL, CONSTITUENT_LOOKUPID, CONSTITUENT_AUTOMATCHSTATUSCODE,
                                        CONSTITUENT_NEWSECURITYATTRIBUTEID, CONSTITUENT_NEWSITEID,
                                        CONSTITUENT_UPDATECONTACTINFO, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values (@ID, @CONSTITUENT_LINKID, @CONSTITUENT_MAPPINGID, @CONSTITUENT_UPDATEDTITLE, 
                                        @CONSTITUENT_UPDATEDFIRSTNAME, @CONSTITUENT_UPDATEDKEYNAME, @CONSTITUENT_UPDATEDADDRESSBLOCK,
                                        @CONSTITUENT_UPDATEDCITY, @CONSTITUENT_UPDATEDPOSTCODE, @CONSTITUENT_UPDATEDSTATEID,
                                        @CONSTITUENT_UPDATEDCOUNTRYID, @CONSTITUENT_UPDATEDPHONE, @CONSTITUENT_UPDATEDEMAILADDRESS,
                                        @CONSTITUENT_ISINDIVIDUAL, @CONSTITUENT_LOOKUPID, @CONSTITUENT_AUTOMATCHSTATUSCODE,
                                        @CONSTITUENT_NEWSECURITYATTRIBUTEID, @CONSTITUENT_NEWSITEID,
                                        @CONSTITUENT_UPDATECONTACTINFO, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                        end
                        else if @RECORDTYPECODE = 3
                        begin
                            --Registrant
                            insert into dbo.BATCHAMPROIMPORTREGISTRANT
                                        (ID, REGISTRANT_CONSTITUENTID, REGISTRANT_EVENTPRICEID, REGISTRANT_AMOUNT,
                                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values (@ID, @REGISTRANT_CONSTITUENTID, @REGISTRANT_EVENTPRICEID, @REGISTRANT_AMOUNT, 
                                        @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                        end
                        else if @RECORDTYPECODE = 4
                        begin 
                            --Auction Item
              if @AUCTIONITEM_LINKID is not null
              begin
                if exists(
                  select 1 
                  from 
                    dbo.AUCTIONITEM 
                    inner join dbo.FINANCIALTRANSACTION on AUCTIONITEM.REVENUEAUCTIONDONATIONID = FINANCIALTRANSACTION.ID
                  where 
                    AUCTIONITEM.ID = @AUCTIONITEM_LINKID
                    and FINANCIALTRANSACTION.POSTSTATUSCODE = 2
                    and 
                    (
                      AUCTIONITEM.TRANSACTIONVALUE <> @AUCTIONITEM_UPDATEDVALUE
                      or FINANCIALTRANSACTION.DATE <> @AUCTIONITEM_UPDATEDDONATIONDATE
                      or FINANCIALTRANSACTION.CONSTITUENTID <> @AUCTIONITEM_CONSTITUENTID
                    )   
                ) 
                begin
                  select
                    @AUCTIONITEM_ADJUSTMENTDATE = @CHANGEDATEEARLIESTTIME,
                                @AUCTIONITEM_ADJUSTMENTPOSTDATE = @CHANGEDATEEARLIESTTIME,
                                @AUCTIONITEM_ADJUSTMENTREASONCODEID = ADJUSTMENTREASONCODE.ID,
                                @AUCTIONITEM_ADJUSTMENTREASON = ADJUSTMENTREASONCODE.CODE
                  from
                    dbo.ADJUSTMENTREASONCODE
                  where
                    ADJUSTMENTREASONCODE.ISAUCTIONIMPORTDEFAULT = 1;
                end
              end

                            insert into dbo.BATCHAMPROIMPORTITEM
                                        (ID, AUCTIONITEM_LINKID, AUCTIONITEM_MAPPINGID, AUCTIONITEM_UPDATEDNAME, AUCTIONITEM_UPDATEDDESCRIPTION, 
                                        AUCTIONITEM_UPDATEDEXPIRATIONDATE, AUCTIONITEM_UPDATEDDONATIONDATE, AUCTIONITEM_UPDATEDCATEGORY, AUCTIONITEM_UPDATEDSUBCATEGORY,
                                        AUCTIONITEM_UPDATEDMINIMUMBID, AUCTIONITEM_UPDATEDVALUE, AUCTIONITEM_CONSTITUENTID,
                                        AUCTIONITEM_ADJUSTMENTDATE, AUCTIONITEM_ADJUSTMENTPOSTDATE, AUCTIONITEM_ADJUSTMENTREASONCODEID, 
                                        AUCTIONITEM_ADJUSTMENTREASON,
                                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values    (@ID, @AUCTIONITEM_LINKID, @AUCTIONITEM_MAPPINGID, @AUCTIONITEM_UPDATEDNAME, @AUCTIONITEM_UPDATEDDESCRIPTION, 
                                        @AUCTIONITEM_UPDATEDEXPIRATIONDATE, @AUCTIONITEM_UPDATEDDONATIONDATE, @AUCTIONITEM_UPDATEDCATEGORY, @AUCTIONITEM_UPDATEDSUBCATEGORY,
                                        @AUCTIONITEM_UPDATEDMINIMUMBID, @AUCTIONITEM_UPDATEDVALUE, @AUCTIONITEM_CONSTITUENTID,
                                        @AUCTIONITEM_ADJUSTMENTDATE, @AUCTIONITEM_ADJUSTMENTPOSTDATE, @AUCTIONITEM_ADJUSTMENTREASONCODEID, 
                                        @AUCTIONITEM_ADJUSTMENTREASON,
                                        @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                        end
                        else if @RECORDTYPECODE = 5
                        begin
                            --Purchase
                            insert into dbo.BATCHAMPROIMPORTPURCHASE
                                        (ID, PURCHASE_CONSTITUENTID, 
                                        PURCHASE_BIDDERNUMBER, PURCHASE_DATE, PURCHASE_AMOUNT,
                                        PURCHASE_POSTDATE, PURCHASE_POSTSTATUSCODE, PURCHASE_PAYMENTMETHODCODE, PURCHASE_CREDITTYPECODEID,
                                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values    (@ID, @PURCHASE_CONSTITUENTID, 
                                        @PURCHASE_BIDDERNUMBER, coalesce(dbo.UFN_DATE_GETEARLIESTTIME(@PURCHASE_DATE), null), @PURCHASE_AMOUNT,
                                        coalesce(dbo.UFN_DATE_GETEARLIESTTIME(@PURCHASE_POSTDATE), null), @PURCHASE_POSTSTATUSCODE, @PURCHASE_PAYMENTMETHODCODE, @PURCHASE_CREDITTYPECODEID,
                                        @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

                            insert into dbo.BATCHAMPROIMPORTPURCHASEDETAIL
                                        (ID, PURCHASEDETAIL_PURCHASEID, PURCHASEDETAIL_ITEMID, PURCHASE_REGISTRANTID,
                                        PURCHASEDETAIL_AMOUNT, PURCHASEDETAIL_TRANSACTIONTYPECODE, PURCHASEDETAIL_DESIGNATIONID,
                                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                select 
                                    newid(),
                                    @ID,
                                    T.c.value('PURCHASEDETAIL_ITEMID[1]', 'uniqueidentifier'),
                                    T.c.value('PURCHASEDETAIL_REGISTRANTID[1]', 'uniqueidentifier'),
                                    T.c.value('PURCHASEDETAIL_AMOUNT[1]', 'money'),
                                    T.c.value('PURCHASEDETAIL_TRANSACTIONTYPECODE[1]', 'tinyint'),
                                    T.c.value('PURCHASEDETAIL_DESIGNATIONID[1]','uniqueidentifier'),
                                    @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                                from @PURCHASE_DETAIL.nodes('/PURCHASE_DETAIL/ITEM') T(c)
                        end
                        else if @RECORDTYPECODE = 6
                        begin
                            --Package
                            insert into dbo.BATCHAMPROIMPORTPACKAGE
                                        (ID, PACKAGE_LINKID, PACKAGE_MAPPINGID, PACKAGE_NAME, PACKAGE_DESCRIPTION, PACKAGE_EXPIRATIONDATE,
                                        PACKAGE_CATEGORY, PACKAGE_SUBCATEGORY, PACKAGE_MINIMUMBID,
                                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values (@ID, @PACKAGE_LINKID, @PACKAGE_MAPPINGID, @PACKAGE_NAME, @PACKAGE_DESCRIPTION, @PACKAGE_EXPIRATIONDATE,
                                        @PACKAGE_CATEGORY, @PACKAGE_SUBCATEGORY, @PACKAGE_MINIMUMBID,
                                        @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                        end
                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                        return 1; 
                    end catch

                    return 0;