USP_KPIINSTANCE_CREATEORUPDATE

Adds a KPI instance to the database or update an instance that already exists.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@KPICATALOGID	uniqueidentifier	IN
@NAME	nvarchar(255)	IN
@DESCRIPTION	nvarchar(1000)	IN
@CONTEXTRECORDID	nvarchar(100)	IN
@GOALVALUE	decimal(19, 4)	IN
@GOALINDICATORTYPECODE	tinyint	IN
@GOALAIMTYPECODE	tinyint	IN
@GOALUPWARDLOWVALUE	decimal(19, 4)	IN
@GOALUPWARDMIDVALUE	decimal(19, 4)	IN
@GOALDOWNWARDMIDVALUE	decimal(19, 4)	IN
@GOALDOWNWARDHIGHVALUE	decimal(19, 4)	IN
@PARAMETERSXML	xml	IN
@HISTORYTYPECODE	tinyint	IN
@NUMBEROFITEMSTOSTORE	int	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SITEFILTERENABLEDFORINSTANCE	bit	IN
@SITESSELECTED	xml	IN
@FOLDERID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_KPIINSTANCE_CREATEORUPDATE(@ID uniqueidentifier = null output,
    @KPICATALOGID uniqueidentifier,
    @NAME nvarchar(255),
    @DESCRIPTION nvarchar(1000),
    @CONTEXTRECORDID nvarchar(100),
    @GOALVALUE decimal(19, 4) = null,
    @GOALINDICATORTYPECODE tinyint,
    @GOALAIMTYPECODE tinyint,
    @GOALUPWARDLOWVALUE decimal(19, 4),
    @GOALUPWARDMIDVALUE decimal(19, 4),
    @GOALDOWNWARDMIDVALUE decimal(19, 4),
    @GOALDOWNWARDHIGHVALUE decimal(19, 4),
    @PARAMETERSXML xml,
    @HISTORYTYPECODE tinyint,
    @NUMBEROFITEMSTOSTORE int,
    @CHANGEAGENTID uniqueidentifier,
    @SITEFILTERENABLEDFORINSTANCE bit,
    @SITESSELECTED xml = null,
  @FOLDERID uniqueidentifier = null
    )
with execute as caller
as
    set nocount on;

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

    -- Null out @SITESSELECTED, just to be safe

    if @SITEFILTERENABLEDFORINSTANCE = 0
        set @SITESSELECTED = null

    if exists (select ID from dbo.KPIINSTANCE where ID = @ID)
    begin
        update dbo.KPIINSTANCE set NAME = @NAME,
            DESCRIPTION = @DESCRIPTION,
            GOALVALUE = @GOALVALUE,
            CONTEXTRECORDID = @CONTEXTRECORDID,
            GOALINDICATORTYPECODE = @GOALINDICATORTYPECODE,
            GOALAIMTYPECODE = @GOALAIMTYPECODE,
            GOALUPWARDLOWVALUE = @GOALUPWARDLOWVALUE,
            GOALUPWARDMIDVALUE = @GOALUPWARDMIDVALUE,
            GOALDOWNWARDMIDVALUE = @GOALDOWNWARDMIDVALUE,
            GOALDOWNWARDHIGHVALUE = @GOALDOWNWARDHIGHVALUE,
            PARAMETERSXML = @PARAMETERSXML,
            HISTORYTYPECODE = @HISTORYTYPECODE,
            NUMBEROFITEMSTOSTORE = @NUMBEROFITEMSTOSTORE,
            SITEFILTERENABLEDFORINSTANCE = @SITEFILTERENABLEDFORINSTANCE,
      FOLDERID = @FOLDERID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID;

        exec dbo.USP_KPIINSTANCESITES_GET_UPDATEFROMXML @ID, @SITESSELECTED, @CHANGEAGENTID, @CURRENTDATE
    end
    else
    begin    

        if @ID is null
            set @ID = newid();

        insert into dbo.KPIINSTANCE (ID,
            KPICATALOGID,
            NAME,
            DESCRIPTION,
            CONTEXTRECORDID,
            GOALVALUE,
            GOALINDICATORTYPECODE,
            GOALAIMTYPECODE,
            GOALUPWARDLOWVALUE,
            GOALUPWARDMIDVALUE,
            GOALDOWNWARDMIDVALUE,
            GOALDOWNWARDHIGHVALUE,
            PARAMETERSXML,
            HISTORYTYPECODE,
            NUMBEROFITEMSTOSTORE,
            SITEFILTERENABLEDFORINSTANCE,
      FOLDERID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED)
        values (@ID,
            @KPICATALOGID,
            @NAME,
            @DESCRIPTION,
            @CONTEXTRECORDID,
            @GOALVALUE,
            @GOALINDICATORTYPECODE,
            @GOALAIMTYPECODE,
            @GOALUPWARDLOWVALUE,
            @GOALUPWARDMIDVALUE,
            @GOALDOWNWARDMIDVALUE,
            @GOALDOWNWARDHIGHVALUE,
            @PARAMETERSXML,
            @HISTORYTYPECODE,
            @NUMBEROFITEMSTOSTORE,
            @SITEFILTERENABLEDFORINSTANCE,
      @FOLDERID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE);

            exec dbo.USP_KPIINSTANCESITES_GET_ADDFROMXML @ID, @SITESSELECTED, @CHANGEAGENTID, @CURRENTDATE
    end;