USP_DATALIST_REVENUE_LETTERS

Returns a list of revenue letters for a revenue record.

Parameters

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

Definition

Copy



                CREATE procedure dbo.USP_DATALIST_REVENUE_LETTERS
                (
                    @REVENUEID uniqueidentifier
                )
                as
                    set nocount on;

                    select
                        REVENUELETTER.ID,
                        LETTERCODE.NAME,
                        REVENUELETTER.PROCESSDATE,
                        REVENUELETTER.ACKNOWLEDGEDATE
                    from
                        dbo.LETTERCODE
                      inner join dbo.REVENUELETTER on LETTERCODE.ID = REVENUELETTER.LETTERCODEID
                    where
                        REVENUELETTER.REVENUEID = @REVENUEID
          union
          select
            REVENUELETTER.ID,
            isnull(EMAILLETTER.NAME, MAILLETTER.NAME),
            REVENUELETTER.PROCESSDATE,
                        REVENUELETTER.ACKNOWLEDGEDATE
          from
            dbo.REVENUELETTER
            inner join dbo.MKTPACKAGE on REVENUELETTER.MKTPACKAGEID = MKTPACKAGE.ID            
                    left join dbo.COMMUNICATIONLETTER as MAILLETTER on MAILLETTER.MAILPACKAGEID = MKTPACKAGE.ID
            left join dbo.COMMUNICATIONLETTER as EMAILLETTER on EMAILLETTER.EMAILPACKAGEID = MKTPACKAGE.ID                    
                    order by
                        2;