SQL Query to Find Mappings
USE [TRANSLUTION_UTILITY_CONTROL]
GO
/****** Object: StoredProcedure [dbo].[usp_SelectMapping] Script Date: 01 Nov 2022 15:58:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_SelectMapping]
@CompanyDB varchar (150),
@Value varchar(150)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL nvarchar(max)
SELECT @SQL =
'
USE ' + @CompanyDB + '
SELECT ''Custom Manipulation'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FCMPM.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_CUSTOM_MANIPULATION_PARAMETER_MAPPING] FCMPM
INNER JOIN [FLOW_STEP] FS ON FS.ID = FCMPM.FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FCMPM.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''Custom Validation'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FCVPM.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_CUSTOM_VALIDATION_PARAMETER_MAPPING] FCVPM
INNER JOIN [FLOW_STEP_RULE] FSR ON FSR.ID = FCVPM.FLOW_STEP_RULE_ID
INNER JOIN [FLOW_STEP] FS ON FS.ID = FSR.FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FCVPM.ACTIVE_INDICATOR = 1 AND FSR.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''Log Data'' AS [Map Type],FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FLDM.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_LOG_DATA_MAPPING] FLDM
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FLDM.FUNCTION_AREA_ID
INNER JOIN [FLOW_STEP] FS ON FS.ID = FLDM.LOG_FLOW_STEP_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FLDM.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''Print Parameter'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FPPM.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_PRINT_PARAMETER_MAPPING] FPPM
INNER JOIN [FLOW_STEP] FS ON FS.ID = FPPM.FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FPPM.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''Print Step Parameter'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FPSPM.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_PRINT_STEP_PRINTER_MAPPING] FPSPM
INNER JOIN [FLOW_STEP] FS ON FS.ID = FPSPM.FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FPSPM.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''Standard Manipulation'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FSMPM.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_STANDARD_MANIPULATION_PARAMETER_MAPPING] FSMPM
INNER JOIN [FLOW_STEP] FS ON FS.ID = FSMPM.FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FSMPM.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''Standard Validation'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FSVPM.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_STANDARD_VALIDATION_PARAMETER_MAPPING] FSVPM
INNER JOIN [FLOW_STEP_RULE] FSR ON FSR.ID = FSVPM.FLOW_STEP_RULE_ID
INNER JOIN [FLOW_STEP] FS ON FS.ID = FSR.FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FSVPM.ACTIVE_INDICATOR = 1 AND FSR.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''Create File'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FSCFM.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_STEP_CREATE_FILE_MAPPING] FSCFM
INNER JOIN [FLOW_STEP] FS ON FS.ID = FSCFM.FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FSCFM.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''Lookup'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FSLM.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_STEP_LOOKUP_MAPPING] FSLM
INNER JOIN [FLOW_STEP] FS ON FS.ID = FSLM.MAPPED_FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FSLM.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''Subroutine'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FSSM.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_STEP_SUBROUTINE_MAPPING] FSSM
INNER JOIN [FLOW_STEP] FS ON FS.ID = FSSM.FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FSSM.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''Suggestion'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FSSM2.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_STEP_SUGGESTION_MAPPING] FSSM2
INNER JOIN [FLOW_STEP] FS ON FS.ID = FSSM2.FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FSSM2.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''VBP'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], FSVM.[STANDARD_VALUE] AS [Standard Map]
FROM [FLOW_STEP_VBP_MAPPING] FSVM
INNER JOIN [FLOW_STEP] FS ON FS.ID = FSVM.FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
FSVM.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''Scanner Prompt'' AS [Map Type], FA.[DESCRIPTION] AS [Function], FS.[DESCRIPTION] AS [Step], SCM.[STANDARD_VALUE] AS [Standard Map]
FROM [SCANNER_PROMPT_MAPPING] SCM
INNER JOIN [FLOW_STEP] FS ON FS.ID = SCM.FLOW_STEP_ID
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = FS.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
SCM.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1 AND FS.ACTIVE_INDICATOR = 1
UNION ALL
SELECT ''VBP Subroutine'' AS [Map Type], FA.[DESCRIPTION] AS [Function], '''' AS [Step], VSPM.[STANDARD_VALUE] AS [Standard Map]
FROM [VBP_SUBROUTINE_PARAMETER_MAPPING] VSPM
INNER JOIN [FUNCTION_AREA] FA ON FA.ID = VSPM.FUNCTION_AREA_ID
WHERE STANDARD_VALUE LIKE ''' + @Value + '%'' AND
VSPM.ACTIVE_INDICATOR = 1 AND FA.ACTIVE_INDICATOR = 1
'
exec sp_sqlexec @SQL
END