USP_DATALIST_R68ONLINESUBMISSIONSETTINGSBYREFERENCENUMBER

                CREATE procedure dbo.USP_DATALIST_R68ONLINESUBMISSIONSETTINGSBYREFERENCENUMBER
                (
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                    set nocount on;

                    select distinct 
                            R68ONLINESUBMISSIONSETTINGS.ID,
                            R68ONLINESUBMISSIONSETTINGS.ORGANISATION,
                            R68ONLINESUBMISSIONSETTINGS.CCRNID,
                            CHARITYCLAIMREFERENCENUMBER.REFERENCENUMBER,
                            case R68ONLINESUBMISSIONSETTINGS.REGULATORCODE when 101 then R68ONLINESUBMISSIONSETTINGS.OTHERREGULATORNAME else R68ONLINESUBMISSIONSETTINGS.REGULATOR end as REGULATOR,
                            case R68ONLINESUBMISSIONSETTINGS.REGULATORCODE when 100 then '' else R68ONLINESUBMISSIONSETTINGS.REGULATORNUMBER end as REGULATORNUMBER
                    from dbo.R68ONLINESUBMISSIONSETTINGS
                    inner join dbo.CHARITYCLAIMREFERENCENUMBER on R68ONLINESUBMISSIONSETTINGS.CCRNID = CHARITYCLAIMREFERENCENUMBER.ID
                    left join dbo.CHARITYCLAIMREFERENCENUMBERSITE on CHARITYCLAIMREFERENCENUMBER.ID = CHARITYCLAIMREFERENCENUMBERSITE.CHARITYCLAIMREFERENCENUMBERID
                    left join SITE on SITE.ID = CHARITYCLAIMREFERENCENUMBERSITE.SITEID
                    where dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, CHARITYCLAIMREFERENCENUMBERSITE.SITEID)=1