Establishing communication between a process and the individual planner

Introduction

The individual compensation planner is designed to work hand in hand with processes. In order for the individual planner to be used according to its intended function, a communication must be established between a process and the individual planner, so that the compensation values added, updated or deleted from either location are updated in the other (i.e., the data of the process and the individual planner must be synchronized).

This document describes the procedure to follow in order to establish this communication between the process and the individual planner using a Post Save stored procedure.

Stored Procedure Creation Governance Principles

Updating a process according to the new values entered in the individual planner means updating the k_m_values and k_m_values_histo tables, which store process-related data. When performing this operation, the following technical challenges must be taken into account:

The stored procedure provides uses the uidPayee parameter, but processes use the idPayee parameter. You can find the idPayee parameter from the uidPayee parameter in the py_payee table.

The k_m_values_histo table must be updated each time the k_m_values table is updated. As a normal behavior, the application automatically writes in the latest version of the record in k_m_values_histo each time a record is created or updated in k_m_values. This logic needs to be preserved to ensure consistency.

Calculated fields must be examined. If a field that is being updated by the stored procedure is used in a calculated field, its value must as well be saved in the k_m_values and k_m_values_histo for consistency purposes.

Conditional validation conditions must be examined. Some conditional validation rules might be violated when saving the field through a stored procedure.

Real-time calculation and data operations must be examined. When using processes in the normal beqom UI, there may exist custom configurations where the modification of a field can trigger a change in a different field.

Process data have a start date and an end date, stored in the k_m_plans_payees_steps table; these correspond to process steps. You must find the corresponding step before updating data using the stored procedure. If the comp round year is different from the year configured for the process, then the stored procedure should not be used to update data because of the year mismatch.

Each process has a security configuration. You need to make sure that the same security configurations are applied when it comes to the communication between the process and the individual planner:
- The first security gate of a process is the process status; if a process is defined as hidden for a given profile, the profile should then not be allowed to update the process using the stored procedure
- The second security gate of a process is the visibility; if a column is not visible for a profile, this profile should then not be allowed to update the process using the stored procedure
- The third security gate of a process is the hierarchy security; the fact that a user can access a payee in the individual planner does not automatically guarantee that they can access that same payee in the associated process. Therefore, this security gate must be taken into consideration as well.
- The fourth security gate of a process is the workflow permissions; if a user is not allowed to edit a payee because of the workflow step, the same procedure must be applied in the stored procedure.

The following table details the use of each column in the k_m_values table:

k_m_values Column	Description	Mapping with the Individual Planner
id_value	PRIMARY KEY. This should be automatically generated by the application.
id_ind	Corresponding value in the k_m_indicators table	Correspond to the comp element in the individual planner
id_field	Corresponding value in the k_m_fields table	Correspond to the comp element in the individual planner
id_step	Date range of the data stored in k_m_plans_payees_steps	Corresponds to the uidPayee, begin_date and end_date values in the comp value tables
input_value	String presentation of comp value data	Comp value expressed in string
input_value_numeric	Numeric presentation of comp value data	Comp value
input_date	Date of the insertion or update of the data	GETUTCDATE
id_user	Corresponding value in k_users > id_user	uidUser must be used to find id_user
comment_value	User comment, if any, when entering values	beqom recommends to define an automatic comment for update using the stored procedure, such as "Auto-generated from the individual planner"
value_type	1
idSim	0

Saving Individual Planner Values into A process

The following method will trigger the execution of the stored procedure:

POST /api/payee-compensation/{payeeId}/round/{roundId}/save-elements-values

For the update of process values from the individual planner to process, the Post Individual Planner Save operation is sufficient. This operation is not performed in the same transaction as the individual planner save operation. If the data id the individual planner are saved, but the Post Individual Planner Save operation fails, the application ignores the failure of the post save operation and still displays a success message (for data save).

Enabling the Synchronization of Data from Individual Planner to Process

Copy

CREATETYPE [dbo].[Kernel_Type_Comp_Values] ASTABLE(
    [uid_comp_value] [uniqueidentifier] NULL,
    [value] [numeric](12, 4) NULL,
    [description] [nvarchar](500) NULL,
    [date_begin] [datetime2](7) NULL,
    [date_end] [datetime2](7) NULL,
    [uid_comp_group] [uniqueidentifier] NULL,
    [uid_comp_element] [uniqueidentifier] NULL,
    [uid_currency] [uniqueidentifier] NULL,
    [effective_date] [date] NULL
)
GO

Copy

CREATEPROC [dbo].[Kernel_SP_CompPlanner_PostSave]
        @uidUser UNIQUEIDENTIFIER,
        @uidProfile UNIQUEIDENTIFIER,
        @uidPayee UNIQUEIDENTIFIER,
        @uidCompRound UNIQUEIDENTIFIER,
        @compValuesDataAS [dbo].[Kernel_Type_Comp_Values] READONLY
AS
    --Custom Logic
GO

Deleting Individual Planner Values

The following API method will trigger the execution of the stored procedure:

Service	Action	Endpoint
Compensation Planner	DELETE	api/payee-compensation/{payeeId}/round/{roundId}/value/{valueId}

For the update of process values from individual planner to process, the Post Individual Planner Delete operation is sufficient. This operation is not performed in the same transaction as the individual planner deletion operation. If the data id the individual planner are deleted, but the Post Individual Planner Delete operation fails, the application ignores the failure of the post delete operation and still displays a success message (for data deletion).

Enabling the Synchronization from Individual Planner to Process

Copy

CREATEPROC [dbo].[Kernel_SP_CompPlanner_PostDelete]
        @uidUser UNIQUEIDENTIFIER,
        @uidProfile UNIQUEIDENTIFIER,
        @uidPayee UNIQUEIDENTIFIER,
        @uidCompRound UNIQUEIDENTIFIER,
        @uidCompValue UNIQUEIDENTIFIER,
        @uidCompElement UNIQUEIDENTIFIER
AS
    --Custom Logic
GO

Stored Procedure Example

The following block provides an example of a Kernel_S_CompPlanner_PostSave stored procedure:

Copy

Kernel_SP_CompPlanner_PostSave

CREATETABLE [dbo].[x_CompPlanner_PostSave_Session](
    [id_val] [bigint] NULL,
    [input_value_numeric] [decimal](10, 2) NULL,
    [input_value] [nvarchar](max) NULL,
    [id_field] [int] NULL,
    [id_ind] [int] NULL,
    [id_step] [int] NULL,
    [date_create] [datetime2](7) NULL,
    [id_session] [uniqueidentifier] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTERTABLE [dbo].[x_CompPlanner_PostSave_Session] ADD  DEFAULT (getutcdate()) FOR [date_create]
GO

CREATETABLE [dbo].[x_CompPlanner_PostSave_Values](
    [uid_comp_value] [uniqueidentifier] NULL,
    [value] [numeric](12, 4) NULL,
    [description] [nvarchar](500) NULL,
    [date_begin] [datetime2](7) NULL,
    [date_end] [datetime2](7) NULL,
    [uid_comp_group] [uniqueidentifier] NULL,
    [uid_comp_round] [uniqueidentifier] NULL,
    [uid_comp_element] [uniqueidentifier] NULL,
    [uid_currency] [uniqueidentifier] NULL,
    [effective_date] [date] NULL,
    [create_date] [datetime2](7) NULL,
    [uid_user] [uniqueidentifier] NULL,
    [uid_profile] [uniqueidentifier] NULL,
    [uid_payee] [uniqueidentifier] NULL,
    [uid_session] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

CREATETABLE [dbo].[x_Comp_Planner_Process_Link](
    [uid_comp_round] [uniqueidentifier] NULL,
    [uid_comp_element] [uniqueidentifier] NULL,
    [uid_payee] [uniqueidentifier] NULL,
    [id_plan] [int] NULL,
    [id_indicator] [int] NULL,
    [id_field] [int] NULL
) ON [PRIMARY]
GO

CREATEPROC [dbo].[Kernel_SP_CompPlanner_PostSave]
        @uidUser UNIQUEIDENTIFIER,
        @uidProfile UNIQUEIDENTIFIER,
        @uidPayee UNIQUEIDENTIFIER,
        @uidCompRound UNIQUEIDENTIFIER,
        @compValuesDataAS [dbo].[Kernel_Type_Comp_Values] READONLY
AS
    DECLARE 
        @idPayeeINT, 
        @idStepINT,
        @startStep DATETIME2,
        @endStep DATETIME2,
        @idPlanINT,
        @profileIdINT,
        @userIdINT,
        @sessionId uniqueidentifier =NEWID();

DECLARE@Updatedtable(
        [id_value] [bigint] NOTNULL,
        [id_ind] [int] NULL,
        [id_field] [int] NULL,
        [id_step] [int] NULL,
        [input_value] [nvarchar](max) NULL,
        [input_value_int] [int] NULL,
        [input_value_numeric] [numeric](18, 4) NULL,
        [input_value_date] [datetime] NULL,
        [input_date] [datetime] NULL,
        [id_user] [int] NULL,
        [comment_value] [nvarchar](max) NULL,
        [source_value] [nvarchar](max) NULL,
        [value_type] [tinyint] NOTNULL,
        [idSim] [int] NOTNULL,
        [idOrg] [int] NULL,
        [typeModification] [int] NULL
    );

    --Custom Logic

    -- get @profileId from k_profiles
    SELECT@userId= id_user FROM k_users WHERE uid_user =@uidUser

    -- get @profileId from k_profiles
    SELECT@profileId= id_profile FROM k_profiles WHERE uid_profile =@uidProfile

    -- get idPayee from py_Payee
    SELECT@idPayee= idPayee FROM py_Payee WHERE uid_payee =@uidPayee;

    -- get plan id from link table based on uidCompRound
    SELECTTOP1@idPlan= id_plan FROM [x_Comp_Planner_Process_Link] WHERE uid_comp_round =@uidCompRound

    -- get start and end date
    SELECTTOP1@startStep= date_begin, @endStep= date_end FROM@compValuesData-- these dates should be alligned with step range
    SET@startStep='2017-01-01 00:00:00.000'
    SET@endStep='2017-12-31 23:59:59.000'

    -- get step id from k_m_plans_payees_steps
    SELECTTOP1@idStep= id_step FROM k_m_plans_payees_steps 
        WHERE id_plan =@idPlanANDCAST(start_step ASDATE) =CAST(@startStepASDATE) ANDCAST(end_step ASDATE) =CAST(@endStepASDATE)
        AND id_payee =@idPayee

    -- add update operation
    INSERTINTO x_CompPlanner_PostSave_Session(id_val, input_value_numeric, input_value, id_field, id_ind, id_step, id_session)
        SELECT id_value, compValue.value, CAST(compValue.valueAS nvarchar(MAX)), linkTable.id_field, linkTable.id_indicator, @idStep, @sessionId
    FROM@compValuesData compValue
    INNERJOIN [dbo].[x_Comp_Planner_Process_Link] linkTable ON linkTable.uid_comp_element = compValue.uid_comp_element AND linkTable.uid_comp_round =@uidCompRound
    JOIN k_m_values ON k_m_values.id_field = linkTable.id_field AND k_m_values.id_ind = linkTable.id_indicator AND k_m_values.id_step =@idStep

    -- add insert operation
    INSERTINTO x_CompPlanner_PostSave_Session(id_val, input_value_numeric, input_value, id_field, id_ind, id_step, id_session)
        SELECT0, compValue.value, CAST(compValue.valueAS nvarchar(MAX)), linkTable.id_field, linkTable.id_indicator, @idStep, @sessionId
        FROM@compValuesData compValue
        INNERJOIN [dbo].[x_Comp_Planner_Process_Link] linkTable ON linkTable.uid_comp_element = compValue.uid_comp_element AND linkTable.uid_comp_round =@uidCompRound
        JOIN k_m_indicators_fields t1 ON t1.id_field = linkTable.id_field AND t1.id_ind = linkTable.id_indicator
    JOIN k_m_plan_display_field t3 ON t3.id_indicator_field = t1.id_indicator_field
    JOIN k_m_plan_display t2 ON t3.id_plan_display = t2.id_plan_display
    WHERE t2.id_plan =@idPlanAND t2.id_profile =@profileId 
    ANDNOTEXISTS(SELECT*FROM x_CompPlanner_PostSave_Session WHERE id_field = linkTable.id_field AND id_ind = linkTable.id_indicator AND id_step =@idStepAND id_val >0)

    UPDATE k_m_values
    SET 
    input_value_numeric = t.input_value_numeric, input_value =CAST(t.input_value AS nvarchar(MAX))
    OUTPUT inserted.id_value, deleted.[id_ind], deleted.[id_field], deleted.[id_step], deleted.[input_value], deleted.[input_value_int], deleted.[input_value_numeric], 
    deleted.[input_value_date], deleted.[input_date], deleted.[id_user], deleted.[comment_value], deleted.[source_value], deleted.[value_type], deleted.[idSim], deleted.[idOrg], 
    deleted.[typeModification]
    INTO@Updated
    FROM x_CompPlanner_PostSave_Session t
    WHERE id_value = t.id_val and t.id_session =@sessionId;

    INSERTINTO k_m_values(id_ind, id_field, id_step, input_value, input_value_numeric)
        SELECT id_ind, id_field, id_step, input_value, input_value_numeric FROM x_CompPlanner_PostSave_Session WHERE id_val =0AND id_session =@sessionId;

        INSERTINTO k_m_values_histo([id_value], [id_ind], [id_field], [id_step], [input_value], [input_value_int], [input_value_numeric], 
    [input_value_date], [input_date], [id_user], [comment_value], [source_value], [value_type], [idSim], [idOrg], 
    [typeModification], [date_histo], [user_histo])
        SELECT [id_value], [id_ind], [id_field], [id_step], [input_value], [input_value_int], [input_value_numeric], 
    [input_value_date], [input_date], [id_user], [comment_value], [source_value], [value_type], [idSim], [idOrg], 
    [typeModification], GETUTCDATE(), @userIdFROM@Updated;

    INSERTINTO [x_CompPlanner_PostSave_Values]( 
    [uid_comp_value],
    [value],
    [description],
    [date_begin],
    [date_end],
    [uid_comp_round],
    [uid_comp_group],
    [uid_comp_element],
    [uid_currency],
    [effective_date],
    [create_date],
    [uid_user],
    [uid_profile],
    [uid_payee],
    [uid_session])
    SELECT [uid_comp_value],
    [value],
    [description],
    [date_begin],
    [date_end],
    @uidCompRound,
    [uid_comp_group],
    [uid_comp_element],
    [uid_currency],
    [effective_date],
    GETUTCDATE() AS [create_date], 
    @uidUserAS [uid_user], 
    @uidProfileAS [uid_profile],
    @uidPayeeAS [uid_payee],
    @sessionIdAS [uid_session]
    FROM@compValuesData;

Attachments

zd_v10-help.4.log-567539263.zip
10 KB Download