USP_EMAILJOB_DETAILS
Email job details.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @STATUSCODE | int | IN | |
| @EMAILTYPE | int | IN |
Definition
Copy
CREATE procedure dbo.USP_EMAILJOB_DETAILS
(
@STATUSCODE integer = null,
@EMAILTYPE integer = null
)
as
begin
set nocount on;
/*
Status to StatusCode mapping
StatusCode = 0 (Queued)
Status = 15
StatusCode = 1 (Processing)
Status = 1
Status = 2
Status = 7
Status = 10
StatusCode = 2 (Completed)
Status = 3
StatusCode = 3 (Canceled)
Status = 5
StatusCode = 4 (Deleted)
Status = 18
StatusCode = 5 (Stopped / Paused)
Status = 6
Status = 8
StatusCode = 6 (Error)
Status = 0
Status = 4
Status = 9
Status = 16
Status = 17
*/
if @STATUSCODE is not null
begin
select S.[ID] as STATUSID
,[EMAILQUEUEGENERALPURPOSEID]
,[MERGEROWSPERCHUNK]
,[LASTMERGEROWPROCESSED]
,S.[RETRYNUMBER]
,[STATUS]
,S.[EMAILHEADER]
,S.[EXTERNALJOBIDENTITY]
,S.[ADDEDBYID]
,S.[CHANGEDBYID]
,S.[DATEADDED]
,S.[DATECHANGED]
,[UNIQUEIDFIELDNAME]
,[EMAILADDRESSFIELDNAME]
,[EMAILDISPLAYNAMEFIELDNAME]
,[MERGETOKEN]
,[PRIORITY]
,[REQUESTEDSENT]
,[ACTUALSENT]
,Q.[ID] as QUEUEID
,[MERGESQLVIEWID]
,[CONTENTTEXT]
,[CONTENTHTML]
,S.[PROCESSAFTERDATE]
,[THREADID]
,[CHUNKPROCESSING]
,[MERGEROWSPROCESSED]
,[MAXRETRYNUMBER]
,[DATEADDEDTOQUEUE]
,[SOURCE]
,[CATEGORY]
,1 TYPE
,[INTERNALJOBIDENTITY]
--,G.NAME
,[STATUSMESSAGE]
,'' as RECIPIENTADDRESS
from [dbo].[EMAILSTATUSGENERALPURPOSE] S
left join [dbo].[EMAILQUEUEGENERALPURPOSE] Q on Q.ID = S.[EMAILQUEUEGENERALPURPOSEID]
--left join [dbo].[GLOBALCHANGE] G on S.INTERNALJOBIDENTITY = G.ID
where (@STATUSCODE = (case when [STATUS] = 15 then 0
when [STATUS] = 3 then 2
when [STATUS] = 18 then 4
when [STATUS] in (6,8) then 5
when [STATUS] in (1,2,7,10) then 1
else 6 end
)
or
(@STATUSCODE = 7 and [STATUS] in (1,2,7,10,15))
or
(@STATUSCODE = 8 and [STATUS] in (3,6,8,18)))
and
(@EMAILTYPE is null or @EMAILTYPE = 1)
union all
select S.[ID] as STATUSID
,[EMAILQUEUETRANSACTIONALID]
,0
,0
,S.[RETRYNUMBER]
,[STATUS]
,S.[EMAILHEADER]
,S.[EXTERNALJOBIDENTITY]
,S.[ADDEDBYID]
,S.[CHANGEDBYID]
,S.[DATEADDED]
,S.[DATECHANGED]
,[UNIQUEIDFIELDNAME]
,[EMAILADDRESSFIELDNAME]
,[EMAILDISPLAYNAMEFIELDNAME]
,[MERGETOKEN]
,1
,[REQUESTEDSENT]
,[ACTUALSENT]
,Q.[ID] as QUEUEID
,'00000000-0000-0000-0000-000000000000'
,[CONTENTTEXT]
,[CONTENTHTML]
,null
,[THREADID]
,0
,0
,3
,[DATEADDEDTOQUEUE]
,[SOURCE]
,0
,0 TYPE
,[INTERNALJOBIDENTITY]
--,G.NAME
,[STATUSMESSAGE]
,S.RECIPIENTADDRESS
from [dbo].[EMAILSTATUSTRANSACTIONAL] S
left join [dbo].[EMAILQUEUETRANSACTIONAL] Q on Q.ID = S.[EMAILQUEUETRANSACTIONALID]
--left join [dbo].[GLOBALCHANGE] G on S.INTERNALJOBIDENTITY = G.ID
where (@STATUSCODE = (case when [STATUS] = 15 then 0
when [STATUS] = 3 then 2
when [STATUS] = 18 then 4
when [STATUS] in (6,8) then 5
when [STATUS] in (1,2,7,10) then 1
else 6 end
)
or
(@STATUSCODE = 7 and [STATUS] in (1,2,7,10,15))
or
(@STATUSCODE = 8 and [STATUS] in (3,6,8,18)))
and
(@EMAILTYPE is null or @EMAILTYPE = 0)
order by [DATECHANGED] desc;
end
else
begin
select S.[ID] as STATUSID
,[EMAILQUEUEGENERALPURPOSEID]
,[MERGEROWSPERCHUNK]
,[LASTMERGEROWPROCESSED]
,S.[RETRYNUMBER]
,[STATUS]
,S.[EMAILHEADER]
,S.[EXTERNALJOBIDENTITY]
,S.[ADDEDBYID]
,S.[CHANGEDBYID]
,S.[DATEADDED]
,S.[DATECHANGED]
,[UNIQUEIDFIELDNAME]
,[EMAILADDRESSFIELDNAME]
,[EMAILDISPLAYNAMEFIELDNAME]
,[MERGETOKEN]
,[PRIORITY]
,[REQUESTEDSENT]
,[ACTUALSENT]
,Q.[ID] as QUEUEID
,[MERGESQLVIEWID]
,[CONTENTTEXT]
,[CONTENTHTML]
,S.[PROCESSAFTERDATE]
,[THREADID]
,[CHUNKPROCESSING]
,[MERGEROWSPROCESSED]
,[MAXRETRYNUMBER]
,[DATEADDEDTOQUEUE]
,[SOURCE]
,[CATEGORY]
,1 TYPE
,[INTERNALJOBIDENTITY]
--,G.NAME
,[STATUSMESSAGE]
,'' as RECIPIENTADDRESS
from [dbo].[EMAILSTATUSGENERALPURPOSE] S
left join [dbo].[EMAILQUEUEGENERALPURPOSE] Q on Q.ID = S.[EMAILQUEUEGENERALPURPOSEID]
--left join [dbo].[GLOBALCHANGE] G on S.INTERNALJOBIDENTITY = G.ID
where (@EMAILTYPE is null or @EMAILTYPE = 1)
union all
select S.[ID] as STATUSID
,[EMAILQUEUETRANSACTIONALID]
,0
,0
,S.[RETRYNUMBER]
,[STATUS]
,S.[EMAILHEADER]
,S.[EXTERNALJOBIDENTITY]
,S.[ADDEDBYID]
,S.[CHANGEDBYID]
,S.[DATEADDED]
,S.[DATECHANGED]
,[UNIQUEIDFIELDNAME]
,[EMAILADDRESSFIELDNAME]
,[EMAILDISPLAYNAMEFIELDNAME]
,[MERGETOKEN]
,1
,[REQUESTEDSENT]
,[ACTUALSENT]
,Q.[ID] as QUEUEID
,'00000000-0000-0000-0000-000000000000'
,[CONTENTTEXT]
,[CONTENTHTML]
,null
,[THREADID]
,0
,0
,3
,[DATEADDEDTOQUEUE]
,[SOURCE]
,0
,0 TYPE
,[INTERNALJOBIDENTITY]
--,G.NAME
,[STATUSMESSAGE]
,S.RECIPIENTADDRESS
from [dbo].[EMAILSTATUSTRANSACTIONAL] S
left join [dbo].[EMAILQUEUETRANSACTIONAL] Q on Q.ID = S.[EMAILQUEUETRANSACTIONALID]
--left join [dbo].[GLOBALCHANGE] G on S.INTERNALJOBIDENTITY = G.ID
where (@EMAILTYPE is null or @EMAILTYPE = 0)
order by [DATEADDED] desc;
end
end