UFN_SITEID_MAPFROM_REVENUESPLITID_BULK

            CREATE function dbo.UFN_SITEID_MAPFROM_REVENUESPLITID_BULK()
            returns table as
            return 
            (
                /*Returns a row for every revenuesplit and its associated site */

                /* Site IDs are mapped from several locations depending on the application type */
                /* REVENUESPLIT of type Misc (APPLICATIONCODE = 11) may not return one-to-one, all others do. Take into consideration depending on your join, you may need to select distinct, or handle this type as a special case. */

                with CTE_RS as
                (
                    select 
            ID,APPLICATIONCODE,DESIGNATIONID,TYPECODE 
          from 
            dbo.REVENUESPLIT_EXT as REVENUESPLIT 
                ),
                CTE_EVENT as 
                (
                    select 
            CTE_RS.ID,EVENTSITE.SITEID 
          from 
            CTE_RS 
          inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = CTE_RS.ID
                    inner join dbo.REGISTRANT on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID 
                    left join dbo.EVENTSITE on REGISTRANT.EVENTID = EVENTSITE.EVENTID 
                    where CTE_RS.APPLICATIONCODE = 1
                ),
                CTE_MEMBERSHIPPROGRAM as 
                (
                    select 
            CTE_RS.ID,MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPPROGRAM.SITEID 
          from
            CTE_RS 
          inner join dbo.MEMBERSHIPTRANSACTION as MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPTRANSACTION  on CTE_RS.ID = MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPTRANSACTION.REVENUESPLITID
                    inner join dbo.MEMBERSHIPLEVEL MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPLEVEL on MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPLEVEL.ID
                    inner join dbo.MEMBERSHIPPROGRAM MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPPROGRAM on MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPPROGRAM.ID
                    where CTE_RS.APPLICATIONCODE = 5
                ),
                CTE_DESIGNATION as
                (
                    select
            CTE_RS.ID,DESIGNATION.SITEID 
          from 
            CTE_RS 
          inner join 
          (
                  select 
                      [DESIGNATION].ID,
                      coalesce(DL5.[SITEID], DL4.[SITEID], DL3.[SITEID], DL2.[SITEID], DL1.[SITEID]) as [SITEID],
                      coalesce(DL5.[ID], DL4.[ID], DL3.[ID], DL2.[ID], DL1.[ID]) as [DESIGNATIONID],
                      case 
                          when DL5.[SITEID] is not null then DL5.[DATEADDED]
                          when DL4.[SITEID] is not null then DL4.[DATEADDED]
                          when DL3.[SITEID] is not null then DL3.[DATEADDED]
                          when DL2.[SITEID] is not null then DL2.[DATEADDED]
                          when DL1.[SITEID] is not null then DL1.[DATEADDED]
                      end [DATEADDED],
                      case 
                          when DL5.[SITEID] is not null then DL5.[DATECHANGED]
                          when DL4.[SITEID] is not null then DL4.[DATECHANGED]
                          when DL3.[SITEID] is not null then DL3.[DATECHANGED]
                          when DL2.[SITEID] is not null then DL2.[DATECHANGED]
                          when DL1.[SITEID] is not null then DL1.[DATECHANGED]
                      end [DATECHANGED]
                  from dbo.[DESIGNATION] 
                  left join dbo.[DESIGNATIONLEVEL] DL1 on DL1.[ID] = [DESIGNATION].[DESIGNATIONLEVEL1ID]
                  left join dbo.[DESIGNATIONLEVEL] DL2 on DL2.[ID] = [DESIGNATION].[DESIGNATIONLEVEL2ID]
                  left join dbo.[DESIGNATIONLEVEL] DL3 on DL3.[ID] = [DESIGNATION].[DESIGNATIONLEVEL3ID]
                  left join dbo.[DESIGNATIONLEVEL] DL4 on DL4.[ID] = [DESIGNATION].[DESIGNATIONLEVEL4ID]
                  left join dbo.[DESIGNATIONLEVEL] DL5 on DL5.[ID] = [DESIGNATION].[DESIGNATIONLEVEL5ID]
            ) as [DESIGNATION] 
          on DESIGNATION.ID = CTE_RS.DESIGNATIONID and CTE_RS.APPLICATIONCODE not in (1,5,11) 
                ),
                CTE_MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM as
                (
                  select 
            CTE_RS.ID, MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM.SITEID 
          from 
            CTE_RS 
          inner join dbo.MEMBERSHIPADDON on CTE_RS.ID = MEMBERSHIPADDON.REVENUESPLITID 
          inner join dbo.MEMBERSHIP on MEMBERSHIPADDON.MEMBERSHIPID = MEMBERSHIP.ID
                  inner join dbo.MEMBERSHIPLEVEL as MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPLEVEL.ID
                  inner join dbo.MEMBERSHIPPROGRAM as MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM on MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM.ID
                  where CTE_RS.APPLICATIONCODE = 18 and CTE_RS.DESIGNATIONID is null
                ),
                CTE_MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE as 
                (
                    select 
            CTE_RS.ID,MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPPROGRAM.SITEID 
          from 
            CTE_RS 

          inner join
          (
            select distinct 
              FINANCIALTRANSACTIONLINEITEM.ID, RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
                      from 
              dbo.FINANCIALTRANSACTIONLINEITEM
                      inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                      inner join dbo.RECURRINGGIFTACTIVITY on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID
                      where REVENUESPLIT_EXT.TYPECODE = 2
          ) as RECURRINGMEMBERSHIPSPLIT 
          on CTE_RS.ID = RECURRINGMEMBERSHIPSPLIT.PAYMENTREVENUEID and CTE_RS.APPLICATIONCODE = 3

                    inner join 
          (
            select distinct 
              FINANCIALTRANSACTIONLINEITEM.ID as ID,INSTALLMENTSPLITPAYMENT.PAYMENTID as PAYMENTID
                    from 
              dbo.FINANCIALTRANSACTIONLINEITEM 
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID 
                    inner join dbo.INSTALLMENTSPLIT on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLIT.REVENUESPLITID 
                    inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
                    where REVENUESPLIT_EXT.TYPECODE = 2
          ) as INSTALLMENTMEMBERSHIPSPLIT 
          on CTE_RS.ID = INSTALLMENTMEMBERSHIPSPLIT.PAYMENTID and CTE_RS.APPLICATIONCODE = 19

                    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI 
                    on (FTLI.ID = RECURRINGMEMBERSHIPSPLIT.ID or FTLI.ID = INSTALLMENTMEMBERSHIPSPLIT.ID) and FTLI.DELETEDON is null and FTLI.TYPECODE <> 1

                    inner join dbo.MEMBERSHIPTRANSACTION as MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPTRANSACTION 
                    on FTLI.ID = MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPTRANSACTION.REVENUESPLITID

                    inner join dbo.MEMBERSHIPLEVEL as MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPLEVEL 
                    on MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPLEVEL.ID

                    inner join dbo.MEMBERSHIPPROGRAM as MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPPROGRAM 
                    on MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPPROGRAM.ID
                    where CTE_RS.TYPECODE = 2 and CTE_RS.APPLICATIONCODE IN (3,19) and CTE_RS.DESIGNATIONID is null

                )


                  select
                          CTE_EVENT.ID, 
                          CTE_EVENT.SITEID 
          from 
              CTE_EVENT

          union all

          select
                          CTE_MEMBERSHIPPROGRAM.ID, 
                          CTE_MEMBERSHIPPROGRAM.SITEID 
          from 
              CTE_MEMBERSHIPPROGRAM

                          union all

          select
                          CTE_DESIGNATION.ID, 
                          CTE_DESIGNATION.SITEID 
          from 
              CTE_DESIGNATION

                          union all

                  select
                          CTE_MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM.ID, 
                          CTE_MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM.SITEID 
          from 
              CTE_MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM

                      union all

          select
                          CTE_MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE.ID, 
                          CTE_MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE.SITEID 
          from 
              CTE_MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE

            union all

                    --Stopped calling the bulk function for account system sites on splits (UFN_ACCOUNTSYSTEM_SITEID_MAPFROM_REVENUESPLIT_BULK), as it was causing a large performance issue

                    --We were only calling this code for APPLICATIONCODE = 11 (Miscellaneous) and the parameter was not able to be passed down into the function,

                    --so we were returning all revenue & site combinations per account system.  The plan can now concentrate on only APPLICATIONCODE 11, which

                    --improves things drastically.  While bringing the code up a level, I also factored out a view that was being used (PDACCOUNTSYSTEMFORREVENUE).

                    select 
                        MISCELLANEOUS.ID,
                        MISCELLANEOUS.SITEID
                    from 
                        UFN_ACCOUNTSYSTEM_SITEID_MAPFROM_REVENUESPLITMISCELLANEOUSPAYMENT_BULK() as MISCELLANEOUS
            )