USP_DATALIST_REVENUETRANSACTION_LETTERS

Returns a list of letters associated with one transaction ID.
Parameters

Parameter	Parameter Type	Mode	Description
@TRANSACTIONID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_REVENUETRANSACTION_LETTERS
                (
                  @TRANSACTIONID uniqueidentifier,
                  @CURRENTAPPUSERID uniqueidentifier = null,
                  @SITEFILTERMODE tinyint = 0,
                  @SITESSELECTED xml = null,
                  @SECURITYFEATUREID uniqueidentifier = null,
                  @SECURITYFEATURETYPE tinyint = null
                )
                as 
                    set nocount on;         

                    with LETTER_CTE
                    (
                      LETTERID,
                      LETTERCODE,
                      LETTERPROCESSDATE,
                      LETTERACKNOWLEDGEDATE,
                      CONSTITUENTID,
                      LETTERTYPECODE,
                      LETTEROUTOFDATE,
                      SITEID,
                      LETTERCODEID
                    ) as
                    (
                      select
                        rl.ID,
                        lc.NAME,
                        rl.PROCESSDATE,
                        rl.ACKNOWLEDGEDATE,
                        rl.ACKNOWLEDGEEID,
                        0,
                        rl.OUTOFDATE,
                        LETTERSITES.SITEID,
                        lc.ID
                      from 
                        dbo.REVENUELETTER rl
                        inner join dbo.LETTERCODE lc on rl.LETTERCODEID = lc.ID
                        cross apply dbo.UFN_SITEID_MAPFROM_LETTERCODEID(lc.ID)as [LETTERSITES]
                      where 
                        rl.REVENUEID = @TRANSACTIONID            
                      union all            
                      select
                        REVENUELETTER.ID,
                        isnull(EMAILLETTER.NAME, MAILLETTER.NAME),
                        REVENUELETTER.PROCESSDATE,
                        REVENUELETTER.ACKNOWLEDGEDATE,
                        REVENUELETTER.ACKNOWLEDGEEID,
                        2,
                        REVENUELETTER.OUTOFDATE,
                        null,
                        isnull(EMAILLETTER.ID, MAILLETTER.ID)
                      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                        
                      where 
                        REVENUELETTER.REVENUEID = @TRANSACTIONID            
                      union all
                      select 
                        rtl.ID,
                        tlc.NAME,
                        rtl.PROCESSDATE,
                        rtl.ACKNOWLEDGEDATE,
                        rtl.CONSTITUENTID,
                        1,
                        null,
                        LETTERSITES.SITEID,
                        tlc.ID
                      from dbo.REVENUETRIBUTE rt
                        inner join dbo.REVENUETRIBUTELETTER rtl on rtl.REVENUETRIBUTEID = rt.ID
                        inner join dbo.TRIBUTELETTERCODE tlc on rtl.TRIBUTELETTERCODEID = tlc.ID
                        cross apply dbo.UFN_SITEID_MAPFROM_TRIBUTELETTERCODEID(tlc.ID) as [LETTERSITES]
                      where rt.REVENUEID = @TRANSACTIONID
                    )
                    select distinct
                      LETTER_CTE.LETTERID,
                        case LETTER_CTE.LETTERTYPECODE
                            when 1 then 'Tribute'
                            else 'Revenue'
                        end LETTERTYPE,
                      LETTER_CTE.LETTERCODE,
                      LETTER_CTE.LETTERPROCESSDATE,
                      LETTER_CTE.LETTERACKNOWLEDGEDATE,
                      NF.NAME,
                      LETTER_CTE.LETTERTYPECODE,
                      LETTER_CTE.LETTEROUTOFDATE,
                      case LETTER_CTE.LETTERTYPECODE 
                        when 0 then
                          ( 
                            select dbo.UDA_BUILDLIST(SITE.NAME)
                            from dbo.UFN_SITEID_MAPFROM_LETTERCODEID(LETTER_CTE.LETTERCODEID) LETTERCODESITES
                            inner join dbo.SITE on SITE.ID=LETTERCODESITES.SITEID
                          )  
                        when 1 then 
                          ( 
                            select dbo.UDA_BUILDLIST(SITE.NAME)
                            from dbo.UFN_SITEID_MAPFROM_TRIBUTELETTERCODEID(LETTER_CTE.LETTERCODEID) LETTERCODESITES
                            inner join dbo.SITE on SITE.ID=LETTERCODESITES.SITEID
                          )
                        else
                          ''
                        end SITES
                    from LETTER_CTE
                      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(LETTER_CTE.CONSTITUENTID) NF
                    where 
                      (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[LETTER_CTE].[SITEID] or (SITEID is null and [LETTER_CTE].[SITEID] is null)))
                        and
                      (
                        @SITEFILTERMODE = 0
                        or
                        exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = LETTER_CTE.SITEID)
                      );