USP_DATAFORMTEMPLATE_EDIT_PROGRAM

The save procedure used by the edit dataform template "Program Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@LOCATIONS	xml	IN	Location
@CAPACITY	int	IN	Capacity
@ISDAILYADMISSION	bit	IN	Is daily admission
@ONSALEINFORMATION	xml	IN	Available for sale
@PROGRAMCATEGORYCODEID	uniqueidentifier	IN	Category

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PROGRAM 
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @NAME nvarchar(100),
                    @DESCRIPTION nvarchar(255),
                    @CURRENTAPPUSERID uniqueidentifier,
                    @LOCATIONS xml,
                    @CAPACITY integer,
                    @ISDAILYADMISSION bit,
                    @ONSALEINFORMATION xml,
                    @PROGRAMCATEGORYCODEID uniqueidentifier
                )
                as    

                set nocount on;

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

                -- Default 0 for SEQUENCE since it is a new field and may not be passed in

                set @LOCATIONS = (
                    select
                        T.c.value('(CAPACITY)[1]','int') as 'CAPACITY',
                        T.c.value('(EVENTLOCATIONID)[1]','uniqueidentifier') as 'EVENTLOCATIONID',
                        T.c.value('(ID)[1]','uniqueidentifier') as 'ID',
                        isnull(T.c.value('(SEQUENCE)[1]','int'), 0) as 'SEQUENCE'
                    from
                        @LOCATIONS.nodes('/LOCATIONS/ITEM') T(c)
                    for xml raw('ITEM'),type,elements,root('LOCATIONS'),binary base64
                );

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate()

                begin try
                -- handle updating the data

                update dbo.PROGRAM set
                    NAME = @NAME,
                    DESCRIPTION = @DESCRIPTION,
                    CAPACITY=@CAPACITY,
                    PROGRAMCATEGORYCODEID = @PROGRAMCATEGORYCODEID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @ID;

                declare @contextCache varbinary(128);
                declare @e int;

                -- cache current context information 

                set @contextCache = CONTEXT_INFO();

                -- set CONTEXT_INFO to @CHANGEAGENTID 

                if not @CHANGEAGENTID is null
                    set CONTEXT_INFO @CHANGEAGENTID;


                if @ISDAILYADMISSION = 1
                begin
                    -- build a temporary table containing the values from the XML

                    declare @TempTbl table (
                       [SALESMETHODID] uniqueidentifier)

                    insert into @TempTbl select 
                        T.c.value('(SALESMETHODID)[1]','uniqueidentifier') 
                    from @ONSALEINFORMATION.nodes('/ONSALEINFORMATION/ITEM') T(c)
                    where T.c.value('(ISAVAILABLE)[1]','bit') = 1;

                    -- delete any items that no longer exist in the XML table

                    delete from dbo.[PROGRAMSALESMETHOD] where 
                        [PROGRAMID] = @ID
                        and [SALESMETHODID] not in (select SALESMETHODID from @TempTbl);

                    -- insert new items

                    insert into [PROGRAMSALESMETHOD] 
                        ([ID],
                        [PROGRAMID], 
                        [SALESMETHODID],                
                        ADDEDBYID, 
                        CHANGEDBYID, 
                        DATEADDED, 
                        DATECHANGED)
                    select newid(), 
                        @ID,
                        [SALESMETHODID], 
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CURRENTDATE, 
                        @CURRENTDATE
                    from @TempTbl as [temp]
                    where not exists (select ID from dbo.[PROGRAMSALESMETHOD] as data where data.SALESMETHODID = [temp].SALESMETHODID and data.PROGRAMID = @ID)
                end

                exec dbo.USP_PROGRAM_GETSEQUENCEDLOCATIONS_UPDATEFROMXML @ID, @LOCATIONS, @CHANGEAGENTID, @CURRENTDATE

                -- reset CONTEXT_INFO to previous value 

                if not @contextCache is null
                    set CONTEXT_INFO @contextCache;

                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR
                return 1
                end catch

                return 0;