UFN_QUERY_STANDINGORDERSEXPORTPROCESSSTANDARDOUTPUT

        CREATE function [dbo].[UFN_QUERY_STANDINGORDERSEXPORTPROCESSSTANDARDOUTPUT]
        (
            @PARAMETERSETID uniqueidentifier = null,
                    @APPUSERID uniqueidentifier
        )
        returns table
        as

        return 
        (                
                select
                    REVENUESCHEDULESTANDINGORDERPAYMENT.ID as REVENUEID,                                        
          CONSTITUENT.ID as CONSTITUENTID,
                    dbo.UFN_CONSTITUENT_BUILDNAME(CONSTITUENT.ID) as CONSTITUENTNAME,
                    (select ADDRESSBLOCK from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as ADDRESSBLOCK,
          (select CITY from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as CITY,
          (select STATE from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as COUNTY,
          (select POSTCODE from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as POSTCODE,
          (select COUNTRY from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as COUNTRY,
                    CONSTITUENTACCOUNT.ACCOUNTNUMBER as ACCOUNTNUMBER,
                    dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY(REVENUE.ID) as REFERENCENUMBER,
                    REVENUESCHEDULESTANDINGORDERPAYMENT.REFERENCEDATE as REFERENCEDATE,
                    FINANCIALINSTITUTION.FINANCIALINSTITUTION as BANKNAME,
                    FINANCIALINSTITUTION.BRANCHNAME as BRANCHNAME,
                    FINANCIALINSTITUTION.SORTCODE as SORTCODE,
                    FINANCIALINSTITUTION.FORMATTEDADDRESS as BRANCHADDRESS,
                    (case REVENUESCHEDULE.FREQUENCY 
                        when 'Single Installment' then 'Single Instalment'
                        else REVENUESCHEDULE.FREQUENCY
                     end) as INSTALMENTFREQUENCY,   
                    REVENUESCHEDULE.STARTDATE as INSTALMENTSTARTDATE,
                    (case REVENUE.TRANSACTIONTYPECODE
                        when 2 then NULL
                        else REVENUESCHEDULE.NUMBEROFINSTALLMENTS
                     end) as NUMBEROFINSTALMENTS,   

                    ((case dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('97d98e59-5b0b-446f-bf48-de8709f50afe') 
            when 0 then REVENUE.AMOUNT
            else REVENUE.TRANSACTIONAMOUNT
            end ) /                
                     (case
             when REVENUESCHEDULE.NUMBEROFINSTALLMENTS > 0 then REVENUESCHEDULE.NUMBEROFINSTALLMENTS 
             else 1 
            end)) as INSTALMENTAMOUNT,                
                    dbo.UFN_REVENUE_GETLASTTRANSACTIONDATE(REVENUE.ID) as INSTALMENTENDDATE,
                    CONSTITUENTACCOUNT.ACCOUNTNAME as NAMEONACCOUNT,
          TRANSACTIONCURRENCY.ID as [TRANSACTIONCURRENCYID]
        from
                    dbo.REVENUESCHEDULESTANDINGORDERPAYMENT 
                        inner join REVENUESCHEDULE on REVENUESCHEDULESTANDINGORDERPAYMENT.ID = REVENUESCHEDULE.ID
                        inner join REVENUE on REVENUESCHEDULE.ID = REVENUE.ID
                        inner join dbo.CONSTITUENTACCOUNT on REVENUESCHEDULESTANDINGORDERPAYMENT.CONSTITUENTACCOUNTID = CONSTITUENTACCOUNT.ID
                        inner join dbo.FINANCIALINSTITUTION on CONSTITUENTACCOUNT.FINANCIALINSTITUTIONID = FINANCIALINSTITUTION.ID
                        inner join dbo.CONSTITUENT on CONSTITUENTACCOUNT.CONSTITUENTID = CONSTITUENT.ID
                        left join dbo.CURRENCY as TRANSACTIONCURRENCY on TRANSACTIONCURRENCY.ID = REVENUE.TRANSACTIONCURRENCYID  
                where 
                    REVENUESCHEDULESTANDINGORDERPAYMENT.STANDINGORDERSETUP = 0 and
          (TRANSACTIONCURRENCY.ISO4217 = 'GBP' or
           dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('97d98e59-5b0b-446f-bf48-de8709f50afe') = 0) and
                    (
                        (dbo.UFN_APPUSER_ISSYSADMIN(@APPUSERID) = 1)
                         or
                        (
                            (
                                (
                                    (
                    --JamesWill WI138606 2011-01-25 Avoid calling in to UFN_SITEID_MAPFROM_REVENUEID which has 

                    --rather poor performance by getting the sites straight from the designation for these records. 

                    --The revenue records which support standing orders will always be secured via the designation

                    --so some of the special processing that UFN_SITEID_MAPFROM_REVENUEID does is unnecessary.

                                        select count(*) from dbo.REVENUESPLIT
                    where REVENUESPLIT.REVENUEID = REVENUE.ID 
                                        and
                                        dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE
                                        (
                                            /*userid*/ @APPUSERID,
                                            /*businessprocesscatalogid*/ 'E55B96E9-7FDA-4B19-AC2C-D38FE75469AF',
                                            /*siteid*/ dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(REVENUESPLIT.DESIGNATIONID)
                                        ) = 1
                                    ) > 0
                                )
                            )
                        )
                    )
            )