USP_DATALIST_ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUS

Retrieves process status data for the given Acknowledgement template.

Parameters

Parameter	Parameter Type	Mode	Description
@CONTEXTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUS
(
    @CONTEXTID uniqueidentifier
)
as
    set nocount on;

select 
    ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUS.ID,
    HTMLTEMPLATEMERGETODOCXOUTPUT.ID as HTMLTEMPLATEMERGETODOCXOUTPUTID,
    ACKNOWLEDGEMENTANDRECEIPTINGPROCESS.DATEADDED,
    BUSINESSPROCESSSTATUS.NUMBERPROCESSED,
    case 
        when DOCXFILE is null then 0
        else 1
    end as HASDOCXFILE,
    'DOCXFILE' as FILEFIELDID,
    'mailing.docx' as FILENAME,
    '9756667f-e340-4e2d-a926-f7b6223576ac' as VIEWFORMID,
    BUSINESSPROCESSOUTPUT.TABLEKEY,
    ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUSIDS.MKTSEGMENTATIONEXPORTPROCESSSTATUSID as EXPORTPROCESSSTATUSID,
    BUSINESSPROCESSSTATUS.STATUSCODE
from
    ACKNOWLEDGEMENTANDRECEIPTINGPROCESS
inner join    
    ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUS on ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUS.PARAMETERSETID = ACKNOWLEDGEMENTANDRECEIPTINGPROCESS.ID
inner join
    BUSINESSPROCESSSTATUS on BUSINESSPROCESSSTATUS.ID = ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUS.ID
inner join
    ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUSIDS on ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUSIDS.id = ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUS.ID
left join
    HTMLTEMPLATETODOCXMERGEPROCESSSTATUS on HTMLTEMPLATETODOCXMERGEPROCESSSTATUS.ID = ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUSIDS.HTMLTEMPLATETODOCXMERGEPROCESSSTATUSID
left join    
    HTMLTEMPLATEMERGETODOCXOUTPUT on HTMLTEMPLATETODOCXMERGEPROCESSSTATUS.ID = HTMLTEMPLATEMERGETODOCXOUTPUT.HTMLTEMPLATETODOCXMERGEPROCESSSTATUSID
left join
    BUSINESSPROCESSOUTPUT on BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID = ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUSIDS.MKTSEGMENTATIONEXPORTPROCESSSTATUSID and  upper([TABLEKEY]) != upper(convert(nvarchar(36), ACKNOWLEDGEMENTANDRECEIPTINGPROCESSSTATUSIDS.MKTSEGMENTATIONEXPORTPROCESSSTATUSID))
where
    ACKNOWLEDGEMENTANDRECEIPTINGPROCESS.ACKNOWLEDGEMENTMAILINGTEMPLATEID = @CONTEXTID