USP_DATAFORMTEMPLATE_SAVE_INSERTPROGRAMPRICE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_SAVE_INSERTPROGRAMPRICE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @PRICELISTID uniqueidentifier
)
as
begin

    set nocount on;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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

        -- adapted from platform-generated USP_PROGRAM_GETPRICES_UPDATEFROMXML


        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;

        declare @PRICETYPES table(
            PROGRAMID uniqueidentifier, 
            FACEPRICE money, 
            ID uniqueidentifier,
            PRICETYPECODEID uniqueidentifier)

        insert into @PRICETYPES
        select @ID, 
            [FACEPRICE],
            newid(),
            [PRICETYPECODEID]
        from dbo.PRICE
        inner join dbo.PRICETYPECODE on 
            PRICETYPECODE.ID = PRICE.PRICETYPECODEID
        where PRICE.PRICELISTID = @PRICELISTID and PRICETYPECODE.ACTIVE = 1;

        -- Price types that have been used for daily sale configuration, and cannot be removed

        declare @PRICETYPESCONFIGUREDONBUTTONS table(PRICETYPECODEID uniqueidentifier)
        insert into @PRICETYPESCONFIGUREDONBUTTONS
        select PRICETYPECODEID
        from dbo.DAILYSALEITEMPROGRAM
        where PROGRAMID = @ID

        declare @HASMISSINGPRICETYPE bit = 0
        if exists 
        (
            select PRICETYPECODEID from @PRICETYPESCONFIGUREDONBUTTONS
            where PRICETYPECODEID not in (select PRICETYPECODEID from @PRICETYPES)
        )
            set @HASMISSINGPRICETYPE = 1

        if @HASMISSINGPRICETYPE = 1
            raiserror('BBERR_MISSINGPRICETYPE',13,1)

        -- delete any prices currently connected to the program

        delete from dbo.[PROGRAMPRICE] where [PROGRAMPRICE].PROGRAMID = @ID;

        select @e=@@error;

        -- reset CONTEXT_INFO to previous value 

        if not @contextCache is null
            set CONTEXT_INFO @contextCache;

        if @e <> 0
            return 1;

        insert into [PROGRAMPRICE] 
            ([PROGRAMID], 
            [FACEPRICE],
            [ID],
            [PRICETYPECODEID],        
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED)
        select
            [PROGRAMID],
            [FACEPRICE],
            [ID],
            [PRICETYPECODEID],
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE,
            @CURRENTDATE
        from @PRICETYPES

        update dbo.PROGRAM set
            PRICELISTID = @PRICELISTID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID;

    end try

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

    return 0;
end