USP_AMPROIMPORT_FIXIDS

            CREATE procedure dbo.USP_AMPROIMPORT_FIXIDS
            (
                @BATCHROWID uniqueidentifier,
                @ORIGINATINGBATCHROWID uniqueidentifier,

                @CONSTITUENT_LINKID uniqueidentifier = null output,
                @REGISTRANT_CONSTITUENTID uniqueidentifier = null output,

                @AUCTIONITEM_LINKID uniqueidentifier = null output,
                @AUCTIONITEM_CONSTITUENTID uniqueidentifier = null output,

                @PURCHASE_CONSTITUENTID uniqueidentifier = null output,
                @PURCHASE_DETAIL xml = null output,

                @PACKAGE_LINKID uniqueidentifier = null output
            )
            as
            set nocount on;

            if @BATCHROWID = @ORIGINATINGBATCHROWID 
                return;

            declare @CURRENTBATCHID uniqueidentifier;
            declare @CURRENTBATCHISEXCEPTIONBATCH bit;

            declare @ORIGINALBATCHID uniqueidentifier;

            select
                @CURRENTBATCHID = BATCH.ID,
                @CURRENTBATCHISEXCEPTIONBATCH  = case when BATCH.ORIGINATINGBATCHID  is null then 0 else 1 end
            from dbo.BATCHAMPROIMPORT
            inner join dbo.BATCH on BATCH.ID = BATCHAMPROIMPORT.BATCHID
            where BATCHAMPROIMPORT.ID = @BATCHROWID;

            if @CURRENTBATCHID is null or @CURRENTBATCHISEXCEPTIONBATCH = 0
                return;

            exec dbo.USP_AMPROIMPORT_FIXCONSTITUENTID @CONSTITUENT_LINKID output, @CURRENTBATCHID;
            exec dbo.USP_AMPROIMPORT_FIXCONSTITUENTID @REGISTRANT_CONSTITUENTID output, @CURRENTBATCHID;

            exec dbo.USP_AMPROIMPORT_FIXITEMID @AUCTIONITEM_LINKID output, @CURRENTBATCHID;
            exec dbo.USP_AMPROIMPORT_FIXCONSTITUENTID @AUCTIONITEM_CONSTITUENTID output, @CURRENTBATCHID;

            exec dbo.USP_AMPROIMPORT_FIXCONSTITUENTID @PURCHASE_CONSTITUENTID output, @CURRENTBATCHID;

            exec dbo.USP_AMPROIMPORT_FIXITEMID @PACKAGE_LINKID output, @CURRENTBATCHID;

            declare @PURCHASE_DETAILS table
            (
                ID uniqueidentifier,
                PURCHASEDETAIL_TRANSACTIONTYPECODE tinyint,
                PURCHASEDETAIL_AMOUNT money,
                PURCHASEDETAIL_ITEMID uniqueidentifier,
                PURCHASEDETAIL_REGISTRANTID uniqueidentifier,
                PURCHASEDETAIL_DESIGNATIONID uniqueidentifier
            );

            if @PURCHASE_DETAIL is not null 
            begin
                insert into @PURCHASE_DETAILS
                    (ID, PURCHASEDETAIL_TRANSACTIONTYPECODE, PURCHASEDETAIL_AMOUNT, PURCHASEDETAIL_ITEMID, 
                        PURCHASEDETAIL_REGISTRANTID, PURCHASEDETAIL_DESIGNATIONID)
                    select
                        newid(),
                        T.c.value('PURCHASEDETAIL_TRANSACTIONTYPECODE[1]', 'tinyint'),
                        T.c.value('PURCHASEDETAIL_AMOUNT[1]', 'money'),
                        T.c.value('PURCHASEDETAIL_ITEMID[1]', 'uniqueidentifier'),
                        T.c.value('PURCHASEDETAIL_REGISTRANTID[1]', 'uniqueidentifier'),
                        T.c.value('PURCHASEDETAIL_DESIGNATIONID[1]','uniqueidentifier')
                    from @PURCHASE_DETAIL.nodes('/PURCHASE_DETAIL/ITEM') T(c)

                declare @PURCHASEDETAIL_ID uniqueidentifier;
                declare @PURCHASEDETAIL_TRANSACTIONTYPECODE tinyint;
                declare @PURCHASEDETAIL_ITEMID uniqueidentifier;
                declare @PURCHASEDETAIL_REGISTRANTID uniqueidentifier;

                declare PD_CURSOR cursor local fast_forward for
                    select ID, PURCHASEDETAIL_TRANSACTIONTYPECODE, PURCHASEDETAIL_ITEMID, PURCHASEDETAIL_REGISTRANTID
                    from @PURCHASE_DETAILS;

                open PD_CURSOR
                fetch next from PD_CURSOR into @PURCHASEDETAIL_ID, @PURCHASEDETAIL_TRANSACTIONTYPECODE, @PURCHASEDETAIL_ITEMID, @PURCHASEDETAIL_REGISTRANTID;

                while @@FETCH_STATUS = 0
                begin
                    if @PURCHASEDETAIL_TRANSACTIONTYPECODE = 2 --item purchase

                    begin
                        exec dbo.USP_AMPROIMPORT_FIXITEMID @PURCHASEDETAIL_ITEMID output, @CURRENTBATCHID;
                    end
                    else if @PURCHASEDETAIL_TRANSACTIONTYPECODE = 3 --event registration

                    begin
                        if @PURCHASEDETAIL_REGISTRANTID is not null and not exists(select ID from dbo.REGISTRANT where REGISTRANT.ID = @PURCHASEDETAIL_REGISTRANTID)
                        begin
                            if not exists(select ID from dbo.BATCHAMPROIMPORT where BATCHID = @CURRENTBATCHID and ID = @PURCHASEDETAIL_REGISTRANTID)
                            begin
                                if exists(select ID from dbo.BATCHAMPROIMPORT where BATCHID = @CURRENTBATCHID  and ORIGINATINGBATCHROWID = @PURCHASEDETAIL_REGISTRANTID)
                                    select top 1 @PURCHASEDETAIL_REGISTRANTID = ID from dbo.BATCHAMPROIMPORT where BATCHID = @CURRENTBATCHID  and ORIGINATINGBATCHROWID = @PURCHASEDETAIL_REGISTRANTID
                                else
                                    set @PURCHASEDETAIL_REGISTRANTID = null;
                            end
                        end
                    end

                    update @PURCHASE_DETAILS
                        set PURCHASEDETAIL_ITEMID = @PURCHASEDETAIL_ITEMID,
                            PURCHASEDETAIL_REGISTRANTID = @PURCHASEDETAIL_REGISTRANTID 
                    where ID = @PURCHASEDETAIL_ID;

                    fetch next from PD_CURSOR into @PURCHASEDETAIL_ID, @PURCHASEDETAIL_TRANSACTIONTYPECODE, @PURCHASEDETAIL_ITEMID, @PURCHASEDETAIL_REGISTRANTID;
                end

                --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                close PD_CURSOR;
                deallocate PD_CURSOR;

                set @PURCHASE_DETAIL = 
                    (
                        select    ID, PURCHASEDETAIL_ITEMID, PURCHASEDETAIL_AMOUNT, PURCHASEDETAIL_REGISTRANTID,
                                PURCHASEDETAIL_TRANSACTIONTYPECODE, PURCHASEDETAIL_DESIGNATIONID
                        from @PURCHASE_DETAILS
                        for xml raw('ITEM'),type,elements,root('PURCHASE_DETAIL'),binary base64
                    );
            end