44 lines
2.1 KiB
Transact-SQL
44 lines
2.1 KiB
Transact-SQL
USE [DD_ECM]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PRDD_CUST_SUPPORT_ERR_7001012] Script Date: 4/19/2024 1:42:36 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: DD MS
|
|
-- Changed: 18.04.2024 MK/MP Profil 64 in Abfrage auf TBPM_PROFILE_FILES hinzugefügt
|
|
-- Changed: 18.04.2024 MK/MP Umstellung auf 'support-flow@digitaldata.works'
|
|
-- =================================================
|
|
CREATE PROCEDURE [dbo].[PRDD_CUST_SUPPORT_ERR_7001012]
|
|
AS
|
|
DECLARE @SUBJECT VARCHAR(100), @BODY VARCHAR(MAX),@JOB_ID As VARCHAR(900), @JOB_NAME VARCHAR(200), @STEP_NAME VARCHAR(200)
|
|
,@EMAIL_TO VARCHAR(100),@MAIL_FROM VARCHAR(200),@COMMENT VARCHAR(MAX), @SYS_DESCR VARCHAR(5)
|
|
SELECT @SYS_DESCR = STRING2 FROM TBDD_MODULES WHERE SHORT_NAME = 'FERDI'
|
|
SELECT @EMAIL_TO = CAT_STRING FROM IDB.dbo.TBIDB_CATALOG WHERE CAT_TITLE = 'DD_SUPPORT_EMAIL';
|
|
|
|
DECLARE @COUNT INT
|
|
|
|
BEGIN
|
|
SELECT @COUNT = COUNT(a.DOC_ID) From
|
|
(SELECT DISTINCT DOC_ID,PROFIL_ID FROM
|
|
TBPM_PROFILE_FILES PF where PROFIL_ID not in (12,48,64)
|
|
and not exists(select * from TBPM_CUST_WORKFLOW_OVERVIEW where DOC_ID = PF.DOC_ID and PROFIL_ID = PF.PROFIL_ID)) A
|
|
IF @COUNT > 0
|
|
BEGIN
|
|
SET @SUBJECT = 'WISAG - FERDinand - ' + @SYS_DESCR + ' - IDs missing in TBPM_CUST_WORKFLOW_OVERVIEW'
|
|
SET @BODY = 'ACHTUNG: In TBPM_CUST_WORKFLOW_OVERVIEW fehlen ' + CONVERT(VARCHAR(20), @COUNT) + ' DocID-Einträge!<br> '
|
|
|
|
SET @BODY += '<ul><li>Prüfen Sie den Insert in TBPM_PROFILE_FILES für folgende IDs <b> SELECT DISTINCT DOC_ID,PROFIL_ID FROM
|
|
TBPM_PROFILE_FILES PF where PROFIL_ID not in (12,48)
|
|
and not exists(select * from TBPM_CUST_WORKFLOW_OVERVIEW where DOC_ID = PF.DOC_ID and PROFIL_ID = PF.PROFIL_ID) </b></ul>'
|
|
SET @BODY += '<p>Erzeugt von Procedure: [PRDD_CUST_SUPPORT_ERR_7001012]'
|
|
|
|
IF NOT EXISTS(SELECT GUID FROM TBEMLP_EMAIL_OUT WHERE [EMAIL_ADRESS] = @EMAIL_TO AND REFERENCE_ID = 7001012 AND WF_ID = 7001012 AND CONVERT(DATE,ADDED_WHEN) = CONVERT(DATE,GETDATE())
|
|
)
|
|
BEGIN
|
|
EXEC PREMAIL_NEW_EMAIL 1,@EMAIL_TO,@SUBJECT,@BODY,7001012,7001012,7001012,'PRDD_CUST_SUPPORT_ERR_7001012'
|
|
END
|
|
END
|
|
END
|