USP_DATAFORMTEMPLATE_EDITLOAD_REGISTRATIONPACKAGE

The load procedure used by the edit dataform template "Registration Package 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.
@MAINEVENTID	uniqueidentifier	INOUT
@PRICES	xml	INOUT
@NAME	nvarchar(100)	INOUT	Name
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@PRICESDISPLAY	xml	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@MAINEVENTBASECURRENCYID	uniqueidentifier	INOUT	Main event currency ID
@LOCKEDPRICESDISPLAY	xml	INOUT
Definition

Copy

                    CREATE procedure USP_DATAFORMTEMPLATE_EDITLOAD_REGISTRATIONPACKAGE
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @MAINEVENTID uniqueidentifier = null output,
                        @PRICES xml = null output,
                        @NAME nvarchar(100) = null output,
                        @TSLONG bigint = 0 output,
                        @PRICESDISPLAY xml = null output,
                        @CURRENTAPPUSERID uniqueidentifier = null,
                        @MAINEVENTBASECURRENCYID uniqueidentifier = null output,
                        @LOCKEDPRICESDISPLAY xml = null output
                    )
                    as
                        set nocount on;

                        set @DATALOADED = 0;
                        set @TSLONG = 0;

                        select
                            @DATALOADED = 1,
                            @MAINEVENTID = EVENTID,
                            @NAME = NAME,
                            @TSLONG = TSLONG
                        from
                            dbo.REGISTRATIONPACKAGE
                        where
                            ID = @ID;

                        select
                            @MAINEVENTBASECURRENCYID = EVENT.BASECURRENCYID
                        from
                            dbo.EVENT
                        where
                            EVENT.ID = @MAINEVENTID;

                        set @PRICES = dbo.UFN_REGISTRATIONPACKAGEPRICE_GETPRICES_WITHSECURITY_TOITEMLISTXML(@ID, @CURRENTAPPUSERID);

                        create table #CHILDEVENTSWITHSITEACCESS (ID uniqueidentifier, [NAME] nvarchar(100));
                        insert into #CHILDEVENTSWITHSITEACCESS select ID, [NAME] from dbo.UFN_CHILDEVENTSWITHSITEACCESS(@MAINEVENTID, @CURRENTAPPUSERID);

                        set @PRICESDISPLAY = 
                            (
                                select
                                    REGISTRATIONPACKAGEPRICE.ID,
                                    EVENTPRICE.ID [EVENTPRICEID],
                                    REGISTRATIONPACKAGEPRICE.SEQUENCE,
                                    case
                                        when REGISTRATIONPACKAGEPRICE.ID is null then 0
                                        else 1
                                    end [INCLUDE],
                                    EVENTPRICE.NAME [EVENTPRICENAME],
                                    EVENTPRICE.AMOUNT,
                                    EVENTPRICE.RECEIPTAMOUNT,
                                        [EVENT].BASECURRENCYID,
                                    EVENTPRICE.REGISTRATIONCOUNT,
                                    EVENTPRICE.EVENTID,
                                    [EVENT].NAME [EVENTNAME],
                                    EVENTHIERARCHY.HIERARCHYPATH.GetLevel() EVENTLEVEL
                                from 
                                    dbo.EVENTPRICE                         
                                    inner join #CHILDEVENTSWITHSITEACCESS [EVENTS] on [EVENTS].ID = EVENTPRICE.EVENTID
                                    inner join dbo.EVENTREGISTRATIONTYPE on EVENTREGISTRATIONTYPE.ID = EVENTPRICE.EVENTREGISTRATIONTYPEID
                                    inner join dbo.[EVENT] on EVENTPRICE.EVENTID = [EVENT].ID
                                    inner join dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = EVENTPRICE.EVENTID
                                    left join dbo.REGISTRATIONPACKAGEPRICE on
                                        EVENTPRICE.ID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
                                        and REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID = @ID
                                order by
                                    EVENTLEVEL, [EVENTS].NAME, EVENTREGISTRATIONTYPE.DESCRIPTION
                                for xml raw('ITEM'),type,elements,root('PRICESDISPLAY'),binary base64
                            );

                            drop table #CHILDEVENTSWITHSITEACCESS;

                        set @LOCKEDPRICESDISPLAY = 
                            (
                                select 
                                    REGISTRATIONPACKAGEPRICE.ID,
                                    EVENTPRICE.ID [EVENTPRICEID],
                                    REGISTRATIONPACKAGEPRICE.SEQUENCE,
                                    EVENTPRICE.NAME [EVENTPRICENAME],
                                    EVENTPRICE.AMOUNT,
                                    EVENTPRICE.RECEIPTAMOUNT,
                                    [EVENT].BASECURRENCYID,
                                    EVENTPRICE.REGISTRATIONCOUNT,
                                    EVENTPRICE.EVENTID,
                                    [EVENT].NAME [EVENTNAME],
                                    EVENTHIERARCHY.HIERARCHYPATH.GetLevel() EVENTLEVEL
                                from
                                    dbo.REGISTRATIONPACKAGEPRICE
                                    inner join dbo.EVENTPRICE on EVENTPRICE.ID = REGISTRATIONPACKAGEPRICE.EVENTPRICEID
                                    inner join dbo.[EVENT] on EVENTPRICE.EVENTID = [EVENT].ID
                                    inner join dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = EVENTPRICE.EVENTID
                                where 
                                    REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID = @ID    
                                    and dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENTPRICE.EVENTID) = 0
                                for xml raw('ITEM'),type,elements,root('LOCKEDPRICESDISPLAY'),binary base64
                            )


                        return 0;