USP_BATCH_UPDATEPROJECTEDTOTALS

Executes the "Batch: Update Projected Totals" record operation.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the ID of the record being updated.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the update.

Definition

Copy



CREATE procedure dbo.USP_BATCH_UPDATEPROJECTEDTOTALS(@ID uniqueidentifier, @CHANGEAGENTID uniqueidentifier)
with EXECUTE AS OWNER
as 

    set NOCOUNT on;

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

    begin try
        declare @BASETABLENAME nvarchar(128);
        declare @AMOUNTCOLUMN nvarchar(100);

        select @BASETABLENAME=BASETABLENAME, @AMOUNTCOLUMN=AMOUNTCOLUMN from dbo.BATCHTYPECATALOG 
            inner join dbo.BATCHTEMPLATE on BATCHTYPECATALOG.ID = BATCHTEMPLATE.BATCHTYPECATALOGID
      inner join dbo.BATCH on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
      where BATCH.ID = @ID;

        if len(@BASETABLENAME) > 0 and len(@AMOUNTCOLUMN) > 0
            begin
                declare @sql nvarchar(516);
                declare @batchid nvarchar(36);
                set @batchid = Cast(@ID as nvarchar(36));
                set @sql = 'update dbo.BATCH set CHANGEDBYID = ''' + cast(@CHANGEAGENTID as nvarchar(36)) + ''', DATECHANGED = getdate(), PROJECTEDTOTALAMOUNT = (select coalesce(sum(' + @AMOUNTCOLUMN + '), 0) from dbo.' + @BASETABLENAME + ' where BATCHID = ''' + @batchid + '''), PROJECTEDNUMBEROFRECORDS=(select count(*) from dbo.' + @BASETABLENAME + ' where BATCHID = ''' + @batchid + ''') where BATCH.ID = ''' + @batchid + '''';
                exec(@sql);
            end    

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

    return 0;