Previous Topic: MAIN_GROUPING_STUB_1Next Topic: iConsole Search Definition Reference Guide

iConsole Review Queue Guide › Customizations › Customizable Stored Procedures and Functions › MAND_GROUPING_STUB_1

MAND_GROUPING_STUB_1

Note: In SQL Server this function is standalone and named differently as described further down. There is no Stub version.

Purpose

Same purpose as for section MAIN_GROUPING_STUB_1 function described previously, but used to derive user defined data grouping for the category 3 metric -- Mandatory Selection.

Default implementation

Returns NULL.

SQL Server custom implementation

In SQL Server, the database stored function must always be named WGN_RQ_GROUPING_MAND_CUSTOM_n_1 where 'n' is the RQ Process number for which this function applies. If the name of the custom functions deviates from that stipulated above then the RQ process to populate the Review Queue will merely ignore them and it will subsequently fail to implement the intended custom grouping behavior.

The following code is an example of how to implement such a function for a mandatory selection of 'at least 1 event per user' and it assumes it applies for the default RQ process "1". It makes the assumption that the input parameter @userid can be guaranteed to uniquely identify a CA DataMinder user. This is an assumption that may or may not be valid depending on how CA DataMinder is deployed. If this is not true, then a combination of @useridm and @userid must be used (as they comprise the formal unique identifier of a CA DataMinder user in the database)> But to retain the simplicity of the example, that assumption is made here.

This function is invoked for each event participant who is a potential candidate for Review Queue. The parameters that are passed are the respective values for each event participant. Because it is generic in nature, it must return a value of type nvarchar. This is why the @userid must be cast.

SQL Server

CREATE FUNCTION WGN_RQ_GROUPING_MAND_CUSTOM_1_1 (	
@eventuid bigint ,
@eventtimestamp datetime ,
@ParticipantIndex int ,
@groupidm int ,
@groupid int ,
@useridm int ,
@userid int ,
@AddressUID1 bigint ,
@AddressUID2 bigint ,
@ParticipantNodeUID int  ,
@InterventionNodeUID int  ,
@eventmajortype int
)
RETURNS nVarchar(512)
AS
BEGIN
	return cast(@userid as nvarchar(512))
END

To implement 'at least 1 event per user', this requires a metric of category '3' which is the Mandatory selection metric. The interpretation of the metric attributes is identical to the other metric categories, so the example below is interpreted as 'Include a minimum of 1 event per data grouping from type A data'. However, the metric does not define how the data group is implemented (in this instance it is per user), that is done by the function above. Setting the value of Data Grouping to '1' is merely stating that the metric is to be applied using the defined data grouping, but not how that grouping is implemented.

Assuming that for the Group (1,105) we wish to apply the above metric then the implementation of that additional metric is shown below:

SQL Server

CREATE PROC WGN_RQ_SET_METRICS_CUSTOM_1
AS
BEGIN
      truncate table wgn3reviewmetrics

	/*
	Other metrics from previous example omitted
	*/
	
-- The metric below defines 1 per user for group 1,105
 EXEC WGN_RQ_METRIC_INSERT_1 1,105,3,1,1,null,'A',1,1

END
Oracle custom implementation

In Oracle, the custom function is contained within the stored package WGN_RQ_CUST and must always be named WGN_RQ_CUST.GROUPING_MAND. Additionally, the process number is passed to this function such that multiple implementations can be coded on a per process basis by using a PL/SQL CASE construct. The custom package body is contained within the supplied file WGN_RQ_BODY.sql and can be modified accordingly.

The following code is an example of how to implement such a function for a mandatory selection of 'at least 1 event per user' and it assumes it applies for the default RQ process '1'. It makes the assumption that the input parameter p_UserID can be guaranteed to uniquely identify a CA DataMinder user. This is an assumption that may or may not be valid, depending on how CA DataMinder is deployed. (If this were not true then a combination of p_UserIDM and p_UserID would need to be used because they comprise the formal unique identifier of a CA DataMinder user in the database, but to retain the simplicity of the example that assumption is made here.)

This function is invoked for each event participant who is a potential candidate for the Review Queue, and the parameters that are passed are the respective values for each event participant. Because it is generic, it has to return a value of type VARCHAR2, hence the reason why the p_UserID has to be cast.

Oracle

FUNCTION Grouping_Mand   ( p_EventUID            IN NUMBER,
                           p_EventTimeStamp      IN DATE,
                           p_ParticipantIndex    IN NUMBER,
                           p_GroupIDM            IN NUMBER,
                           p_GroupID             IN NUMBER,
                           p_UserIDM             IN NUMBER,
                           p_UserID              IN NUMBER,
                           p_AddressUID1         IN NUMBER,
                           p_AddressUID2         IN NUMBER,
                           p_ParticipantNodeUID  IN NUMBER,
                           p_InterventionNodeUID IN NUMBER,
                           p_EventMajorType      IN NUMBER,
                           p_Process             IN NUMBER DEFAULT 1 )
RETURN VARCHAR2
IS
BEGIN

  -- This procedure is customizable per process and therefore the following
  -- constructs must be strictly adhered to.

  CASE p_Process

     WHEN 1 THEN

       RETURN TO_CHAR(p_UserID);

     WHEN 2 THEN 

       -- To use custom code the default call below must be commented out
       -- and additional custom code provided in it's place.
      
       -- invoke default routine
       RETURN WGN_RQ.Grouping_Mand_Def_1(p_EventUID,p_EventTimeStamp,p_ParticipantIndex,
                       p_GroupIDM,p_GroupID,p_UserIDM,p_UserID,
                       p_AddressUID1,p_AddressUID2,
p_ParticipantNodeUID,p_InterventionNodeUID,p_EventMajorType);

     ELSE

       -- DO NOT CUSTOMIZE

       -- invoke default routine
       RETURN WGN_RQ.Grouping_Mand_Def_1(p_EventUID,p_EventTimeStamp,p_ParticipantIndex,
                       p_GroupIDM,p_GroupID,p_UserIDM,p_UserID,
                       p_AddressUID1,p_AddressUID2,                              p_ParticipantNodeUID,p_InterventionNodeUID,p_EventMajorType);

  END CASE;
 
END Grouping_Mand;

To implement 'at least 1 event per user' requires a metric of category '3' which is the Mandatory selection metric. The interpretation of the metric attributes is identical to the other metric categories, so the example below is interpreted as 'Include a minimum of 1 event per data grouping from type A data'. However the metric does not define how the data group is implemented (in this instance it is per user), that is done by the function above. Setting the value of Data Grouping to '1' is merely stating that the metric is to be applied using the defined data grouping, but not how that grouping is implemented.

Assuming that for the Group (1,105) we wish to apply the above metric then the implementation of that additional metric is shown below:

Oracle

PROCEDURE Set_Metrics
IS
BEGIN

  -- Default is to insert metric parameters for top-level group only.
  -- All other sub-groups inherit this.

  -- Wgn_RQ.Set_Metrics_Def_1;

  -- To use custom code the default call above must be commented out.
  -- Provide custom code as follows by always using the specific
  -- procedure/function calls where stated:

  -- purge existing metrics
  -- <<MANDATORY>>
  WGN_RQ_EXEC_DDL.Truncate_Table('Wgn3ReviewMetrics');

  -- insert new metrics
  -- <<OPTIONAL>>

 -- Other metrics from previous example omitted

-- The metric below defines 1 per user for group 1,105
 WGN_RQ.METRIC_INSERT_1(1,105,3,1,1,null,'A',1,1);

END Set_Metrics;

More information:

MAIN_GROUPING_STUB_1