USP_DATALIST_REVENUEDETAIL_LETTERS

Returns a list of 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_REVENUEDETAIL_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 MKTPACKAGE.ID = REVENUELETTER.MKTPACKAGEID
            left join dbo.COMMUNICATIONLETTER as EMAILLETTER on EMAILLETTER.EMAILPACKAGEID = MKTPACKAGE.ID
            left join dbo.COMMUNICATIONLETTER as MAILLETTER on MAILLETTER.MAILPACKAGEID = MKTPACKAGE.ID
          where
            REVENUELETTER.REVENUEID = @REVENUEID
                    order by
                        2;