UFN_ACCOUNTSYSTEM_SITEID_MAPFROM_REVENUESPLITMISCELLANEOUSPAYMENT_BULK

CREATE function dbo.UFN_ACCOUNTSYSTEM_SITEID_MAPFROM_REVENUESPLITMISCELLANEOUSPAYMENT_BULK()
returns table 
as
return
(

    /* A single row is returned when revenuesplit is in the default account system. */
    /* For a revenue split in a non-default account system, a row is returned for each site in that system. */

    select
        FINANCIALTRANSACTIONLINEITEM.ID, 
        PDACCOUNTSYSTEMSITE.SITEID
    from 
        dbo.PDACCOUNTSYSTEMSITE as PDACCOUNTSYSTEMSITE
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMSITE.PDACCOUNTSYSTEMID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
        inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FINANCIALTRANSACTION.PDACCOUNTSYSTEMID
    where
        FINANCIALTRANSACTION.TYPECODE in (0,1,4,5,6,7)
        and REVENUESPLIT_EXT.APPLICATIONCODE = 11

    union all

    select
        FINANCIALTRANSACTIONLINEITEM.ID, 
        null as SITEID
    from 
        dbo.FINANCIALTRANSACTIONLINEITEM  
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FINANCIALTRANSACTION.PDACCOUNTSYSTEMID
    where
        FINANCIALTRANSACTION.TYPECODE in (0,1,4,5,6,7)
        and REVENUESPLIT_EXT.APPLICATIONCODE = 11
        and PDACCOUNTSYSTEM.ISDEFAULT = 1

)