Premium Retail Services

These requests usually come from the COE Team or Business Leaders. You should be given a Client (either name or id), Activity Sub Type (code or description), optional configuration name, and optional limit/alert cap. Client info may be 360 or MDM so be careful where you are looking.

1. Make sure clients are mapped properly in the Interations.acostamdm.MasterDataMapping table (EntityId = 360 Client Id, EntityType = 'Client', MappedId = Mdm Client Id, MappedType = 'Acosta Client'). Make note of the MdmClientId when looking this up as you will need it.
   
   

   SELECT *
   FROM [Integrations].[acostamdm].[MasterDataMapping]
   WHERE EntityType = 'Client' AND MappedType = 'Acosta Client'
   --AND EntityId = 1234 -- Use when given 360 Client Id
   --AND MappedId = 1234 -- Use when given Mdm Client Id
   AND IsActive = 1

   
   

2. Make sure that Activity Sub Type in 360 exists and has the code associated to it (QTrax.dbo.svQuestionsActivitySubType and QTrax.dbo.svQuestionsActivityTypeExternalKeyMapping).
   
   
3. If no configuration name is given, we are using the default of 'Client Name - Activity Sub Type Name'.
   
   
4. After this you should have Configuration Name, Mdm Client Id, Activity Sub Type Id, and optional limit/alert cap.
   
   
5. Run the script below providing your user identifier (ie email address). You can use this script to create multiple configuration given you insert each into the temp table. If no limit/alert cap is provided, use NULL in the temp table.
   
   

6. DECLARE @createdBy nvarchar(50);
   SET @createdBy = ''; --PROVIDE USER IDENTIFIER
   
   DECLARE @timestamp datetime2(7);
   SET @timestamp = SYSUTCDATETIME();
   
   CREATE TABLE #ConfigStaging (
     [ServiceRequestGroupConfigurationId] [uniqueidentifier] NOT NULL,
     [ServiceRequestGroupConfigurationName] [nvarchar](150) NOT NULL,
     [MdmClientId] [int] NOT NULL,
     [ActivitySubTypeId] [int] NOT NULL,
     [AlertCap] [int] NULL
   )
   
   --CREATE INSERTS BASED ON EXAMPLE BELOW RIGHT HERE
   --INSERT INTO #ConfigStaging VALUES (NEWID(),'Configuration Name', {MdmClientId}, {ActivitySubTypeId}, {Limit/AlertCap});
   
   INSERT INTO [coordinator].[ServiceRequestGroupConfiguration]
       ([Id], [Name], [Active], [EnableEffectiveDates], [EffectiveStartDate], [EffectiveEndDate], [CreatedBy], [CreatedDate] ,[ModifiedBy], [ModifiedDate])
     SELECT [ServiceRequestGroupConfigurationId], [ServiceRequestGroupConfigurationName], 1, 0, NULL, NULL, @createdBy, @timestamp, @createdBy, @timestamp
     FROM #ConfigStaging;
   
   INSERT INTO [coordinator].[ConfigurationClient]
       ([Id], [ServiceRequestGroupConfigurationId], [ClientId], [Active] ,[CreatedBy], [CreatedDate], [ModifiedBy], [ModifiedDate])
     SELECT NEWID(), A.[ServiceRequestGroupConfigurationId], B.[Id], 1, @createdBy, @timestamp, @createdBy, @timestamp
     FROM #ConfigStaging A
     JOIN [dbo].[Client] B ON A.[MdmClientId] = B.[MdmId] AND B.[Active] = 1 AND B.[ExecutionClient] = 1;
   
   INSERT INTO [coordinator].[ConfigurationParameter]
       ([Id], [EntityType], [EntityId], [Active], [ParameterName], [ParameterValue], [CreatedBy], [CreatedDate], [ModifiedBy], [ModifiedDate])
     SELECT NEWID(), 'ServiceRequestGroupConfiguration', [ServiceRequestGroupConfigurationId], 1, 'ActivitySubTypeId', CONCAT('', [ActivitySubTypeId]),
       @createdBy, @timestamp, @createdBy, @timestamp
     FROM #ConfigStaging
   
   INSERT INTO [coordinator].[ConfigurationParameter]
       ([Id], [EntityType], [EntityId], [Active], [ParameterName], [ParameterValue], [CreatedBy], [CreatedDate], [ModifiedBy], [ModifiedDate])
     SELECT NEWID(), 'ServiceRequestGroupConfiguration', [ServiceRequestGroupConfigurationId], 1, 'Limit', CONCAT('', [AlertCap]),
       @createdBy, @timestamp, @createdBy, @timestamp
     FROM #ConfigStaging
     WHERE [AlertCap] IS NOT NULL
   
   DROP TABLE #ConfigStaging;