UFN_REVENUE_GENERATEGLDISTRIBUTION

            CREATE function dbo.UFN_REVENUE_GENERATEGLDISTRIBUTION(@REVENUEID uniqueidentifier = null)
            returns @DISTRIBUTIONS TABLE
            (
                REVENUEID uniqueidentifier,
                GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                TRANSACTIONTYPECODE tinyint,
                POSTDATE datetime,
                POSTSTATUSCODE tinyint,
                ACCOUNTSTRING nvarchar(255),
                PROJECT nvarchar(255),
                AMOUNT money,
                REFERENCE nvarchar(255),
                ERRORMESSAGE nvarchar(max),
                PAYMENTMETHODCODE tinyint,
                REVENUETRANSACTIONTYPECODE tinyint,
                REVENUESPLITID uniqueidentifier,
                MAPPEDVALUES xml
            )
            as begin

            -- If we have a revenue ID, use the UFN_REVENUE_GENERATEGLDISTRIBUTION_FORSINGLEREVENUE function, which is more efficient

            if @REVENUEID is not null
            begin
                insert into @DISTRIBUTIONS(REVENUEID,GLPAYMENTMETHODREVENUETYPEMAPPINGID,TRANSACTIONTYPECODE,POSTDATE,POSTSTATUSCODE,ACCOUNTSTRING,PROJECT,AMOUNT,REFERENCE,ERRORMESSAGE,PAYMENTMETHODCODE,REVENUETRANSACTIONTYPECODE,REVENUESPLITID,MAPPEDVALUES)
                select
                    REVENUEID,
                    GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    TRANSACTIONTYPECODE,
                    POSTDATE,
                    POSTSTATUSCODE,
                    ACCOUNTSTRING,
                    PROJECT,
                    AMOUNT,
                    REFERENCE,
                    ERRORMESSAGE,
                    PAYMENTMETHODCODE,
                    REVENUETRANSACTIONTYPECODE,
                    REVENUESPLITID,
                    MAPPEDVALUES
                from 
                    dbo.UFN_REVENUE_GENERATEGLDISTRIBUTION_FORSINGLEREVENUE(@REVENUEID);

                return;
            end

            else

            begin
                insert into @DISTRIBUTIONS(REVENUEID,GLPAYMENTMETHODREVENUETYPEMAPPINGID,TRANSACTIONTYPECODE,POSTDATE,POSTSTATUSCODE,ACCOUNTSTRING,PROJECT,AMOUNT,REFERENCE,ERRORMESSAGE,PAYMENTMETHODCODE,REVENUETRANSACTIONTYPECODE,REVENUESPLITID,MAPPEDVALUES)

                /* Getting 'Postable' Revenue            */
                select REVENUE.ID as REVENUEID, 
                    dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    tf.TRANSACTIONTYPECODE, 
                    case when ADJUSTMENT.ID is null then REVENUE.POSTDATE        -- same reason as above

                         else ADJUSTMENT.POSTDATE
                    end as POSTDATE,
                    case when ADJUSTMENT.ID is null then (case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end)        -- same reason as above. 

                         else ADJUSTMENT.POSTSTATUSCODE
                    end as POSTSTATUSCODE,
                    tf.ACCOUNTSTRING, 
                    tf.PROJECTCODE as PROJECT, 
                    REVENUESPLIT.AMOUNT, 
                    dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, REVENUEPAYMENTMETHOD.PAYMENTMETHOD, REVENUESPLIT.APPLICATION) as REFERENCE,
                    tf.ERRORMESSAGE,
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                    REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                    REVENUESPLIT.ID as REVENUESPLITID,
                    tf.MAPPEDVALUES
                from dbo.REVENUE with (nolock)
                left join dbo.REVENUEPOSTED with (nolock) on REVENUEPOSTED.ID = REVENUE.ID
                inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID 
                inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID 
                left join dbo.ADJUSTMENT with (nolock) on REVENUE.ID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE <> 0 
                cross apply UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE,REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, REVENUESPLIT.DESIGNATIONID) as tf
                where    (REVENUE.ID = @REVENUEID or @REVENUEID is null)
                        and (not ((REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = REVENUEPAYMENTMETHOD.ID)) or 
                            (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID and ISSOLD = 1)))  --special handling for sold stock/property

                        and (not REVENUE.TRANSACTIONTYPECODE = 1) --special handling for Pledge

                        and (not REVENUESPLIT.APPLICATIONCODE in (1,2,5)) --special handling for Pledge Payment and Event Registration and Membership

                        )                

        union all

                /*
                    We need special handling for sold stock and property. When a stock is sold it's paymentmethodcode changes from 4 to 7 (or 5 to 8 in case of property)
                    But even after selling we need to capture the unsold information and so need to pass in 4 and 5 to UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE
                    and UFN_REVENUE_GENERATEGLACCOUNT to get the correct journal reference and account numbers respectively.
                */

                select REVENUE.ID as REVENUEID, 
                    dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(SORP.PAYMENTMETHODCODE, REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,                
                    tf.TRANSACTIONTYPECODE, 
                    case when ADJUSTMENT.ID is null then REVENUE.POSTDATE        
                         else ADJUSTMENT.POSTDATE
                    end as POSTDATE,
                    case when ADJUSTMENT.ID is null then (case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end)
                         else ADJUSTMENT.POSTSTATUSCODE
                    end as POSTSTATUSCODE,
                    tf.ACCOUNTSTRING, 
                    tf.PROJECTCODE as PROJECT, 
                    coalesce(REVENUESPLIT.AMOUNT,REVENUE.AMOUNT), 
                    dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, SORP.PAYMENTMETHOD,REVENUESPLIT.APPLICATION) as REFERENCE,                 
                    tf.ERRORMESSAGE,
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                    REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                    REVENUESPLIT.ID as REVENUESPLITID,
                    tf.MAPPEDVALUES
                from dbo.REVENUE with (nolock)
                inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                left join dbo.REVENUEPOSTED with (nolock) on REVENUEPOSTED.ID = REVENUE.ID 
                left join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID 
                inner join 
                    (select ID, '4' as PAYMENTMETHODCODE, 'Stock' as PAYMENTMETHOD from dbo.STOCKDETAIL 
                        union all 
                     select ID, '5' as PAYMENTMETHODCODE, 'Property' as PAYMENTMETHOD from dbo.PROPERTYDETAIL) 
                    as SORP on REVENUEPAYMENTMETHOD.ID = SORP.ID 
                left join dbo.ADJUSTMENT with (nolock) on REVENUE.ID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE <> 0            
                cross apply UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE,REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, SORP.PAYMENTMETHODCODE, REVENUESPLIT.DESIGNATIONID) as tf 
                where    (REVENUE.ID = @REVENUEID or @REVENUEID is null)
                        and ((REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = REVENUEPAYMENTMETHOD.ID)) or 
                        (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID and ISSOLD = 1))) --special handling for sold stock/property


                union all
                /*
                    We need special handling for Pledge. Pledges can be created with Payment method of 'credit card', 'debit card' or 'none' but 
                    in the glpaymentmethodrevenuetypemapping table will get mapped to 'none'-'pledge' row.
                    So we always need to pass in 9 (None) into UFN_REVENUE_GENERATEGLACCOUNT instead of the actual paymentmethod
                */
                select REVENUE.ID as REVENUEID, 
                    dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(9, REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    tf.TRANSACTIONTYPECODE, 
                    case when ADJUSTMENT.ID is null then REVENUE.POSTDATE        -- same reason as above

                         else ADJUSTMENT.POSTDATE
                    end as POSTDATE,
                    case when ADJUSTMENT.ID is null then (case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end)    -- same reason as above. 

                         else ADJUSTMENT.POSTSTATUSCODE
                    end as POSTSTATUSCODE,
                    tf.ACCOUNTSTRING, 
                    tf.PROJECTCODE as PROJECT, 
                    REVENUESPLIT.AMOUNT, 
                    dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'None', 'Pledge') as REFERENCE,
                    tf.ERRORMESSAGE,
                    9,
                    REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                    REVENUESPLIT.ID as REVENUESPLITID,
                    tf.MAPPEDVALUES
                from dbo.REVENUE with (nolock)
                left join dbo.REVENUEPOSTED with (nolock) on REVENUEPOSTED.ID = REVENUE.ID 
                inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID 
                left join dbo.ADJUSTMENT with (nolock) on REVENUE.ID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE <> 0
                cross apply UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE,REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, 9, REVENUESPLIT.DESIGNATIONID) as tf 
                where    (REVENUE.ID = @REVENUEID or @REVENUEID is null)
                        and (REVENUE.TRANSACTIONTYPECODE = 1)                        --special handling for Pledge


                union all

                /*
                    We need special handling for Event Registration since they don't have Designations and have their own mapping table;
                    ...Split TYPECODE and DESIGNATIONID are now passed in for gifts as part of an event registration.
                */
                select REVENUE.ID as REVENUEID, 
                    dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.TYPECODE, 1, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    tf.TRANSACTIONTYPECODE, 
                    case when ADJUSTMENT.ID is null then REVENUE.POSTDATE        -- same reason as above

                         else ADJUSTMENT.POSTDATE
                    end as POSTDATE,
                    case when ADJUSTMENT.ID is null then (case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end)        -- same reason as above. 

                         else ADJUSTMENT.POSTSTATUSCODE
                    end as POSTSTATUSCODE,
                    tf.ACCOUNTSTRING, 
                    tf.PROJECTCODE as PROJECT, 
                    REVENUESPLIT.AMOUNT, 
                    dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, REVENUEPAYMENTMETHOD.PAYMENTMETHOD, REVENUESPLIT.APPLICATION) as REFERENCE,
                    tf.ERRORMESSAGE,
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                    REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                    REVENUESPLIT.ID as REVENUESPLITID,
                    tf.MAPPEDVALUES
                from dbo.REVENUE with (nolock)
                inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                left join dbo.REVENUEPOSTED with (nolock) on REVENUEPOSTED.ID = REVENUE.ID
                inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID 
                left join dbo.ADJUSTMENT with (nolock) on REVENUE.ID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE <> 0
                cross apply UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE,REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, REVENUESPLIT.DESIGNATIONID) as tf 
                where    (REVENUE.ID = @REVENUEID or @REVENUEID is null)
                        and (not ((REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = REVENUEPAYMENTMETHOD.ID)) or 
                        (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID and ISSOLD = 1))))  --special handling for sold stock/property

                        and (REVENUESPLIT.APPLICATIONCODE = 1)                --special handling for Event Registration            

                union all
                /*
                    We need special handling for Memberships since they don't have Designations and have their own mapping table
                */
                select REVENUE.ID as REVENUEID, 
                    dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, REVENUE.TRANSACTIONTYPECODE, 2, 5, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    tf.TRANSACTIONTYPECODE, 
                    case when ADJUSTMENT.ID is null then REVENUE.POSTDATE        -- same reason as above

                         else ADJUSTMENT.POSTDATE
                    end as POSTDATE,
                    case when ADJUSTMENT.ID is null then (case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end)        -- same reason as above. 

                         else ADJUSTMENT.POSTSTATUSCODE
                    end as POSTSTATUSCODE,
                    tf.ACCOUNTSTRING, 
                    tf.PROJECTCODE as PROJECT, 
                    REVENUESPLIT.AMOUNT, 
                    dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, REVENUEPAYMENTMETHOD.PAYMENTMETHOD, REVENUESPLIT.APPLICATION) as REFERENCE,
                    tf.ERRORMESSAGE,
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                    REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                    REVENUESPLIT.ID as REVENUESPLITID,
                    tf.MAPPEDVALUES
                from dbo.REVENUE with (nolock)
                inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                left join dbo.REVENUEPOSTED with (nolock) on REVENUEPOSTED.ID = REVENUE.ID
                inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID 
                left join dbo.ADJUSTMENT with (nolock) on REVENUE.ID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE <> 0
                cross apply UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE,REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, null) as tf 
                where    (REVENUE.ID = @REVENUEID or @REVENUEID is null)
                        and (not ((REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = REVENUEPAYMENTMETHOD.ID)) or 
                        (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 5 and exists(SELECT ID from dbo.PROPERTYDETAIL where PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID and ISSOLD = 1))))  --special handling for sold stock/property

                        and (REVENUESPLIT.APPLICATIONCODE = 5)                --special handling for Memberships

                union all
                /*    
                    Special handling for pledge payments - bookable and non-bookable
                    Pledge payments post different based on whether their pledges are marked as
                    'Do not post' or 'Not Posted'
                */    
                select distinct
                    REVENUE.ID as REVENUEID, 
                    dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, DETAIL.REVENUETRANSACTIONTYPECODE, DETAIL.SPLITTYPECODE, DETAIL.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    tf.TRANSACTIONTYPECODE, 
                    case when ADJUSTMENT.ID is null then REVENUE.POSTDATE        -- same reason as above

                         else ADJUSTMENT.POSTDATE
                    end as POSTDATE,
                    case when ADJUSTMENT.ID is null then (case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end)    -- same reason as above. 

                         else ADJUSTMENT.POSTSTATUSCODE
                    end as POSTSTATUSCODE,
                    tf.ACCOUNTSTRING, 
                    tf.PROJECTCODE as PROJECT, 
                    REVENUESPLIT.AMOUNT, 
                    dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, REVENUEPAYMENTMETHOD.PAYMENTMETHOD, DETAIL.TYPE) as REFERENCE,
                    tf.ERRORMESSAGE,
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                    REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                    REVENUESPLIT.ID as REVENUESPLITID,
                    convert(varchar(max),tf.MAPPEDVALUES)
                from dbo.REVENUE with (nolock)
                inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                left join dbo.REVENUEPOSTED with (nolock) on REVENUEPOSTED.ID = REVENUE.ID
                inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
                inner join dbo.INSTALLMENTPAYMENT with (nolock) on REVENUESPLIT.ID = INSTALLMENTPAYMENT.PAYMENTID
                inner join dbo.REVENUE as PLEDGE with (nolock) on INSTALLMENTPAYMENT.PLEDGEID = PLEDGE.ID
                left join dbo.REVENUEPOSTED PLEDGEPOSTED with (nolock) on PLEDGEPOSTED.ID = PLEDGE.ID
                cross apply dbo.UFN_POSTTOGLPROCESS_MAPPLEDGEPAYMENTS(PLEDGE.ID, case when PLEDGE.DONOTPOST = 1 then 2 when PLEDGEPOSTED.ID is not null then 0 else 1 end) as DETAIL
                left join dbo.ADJUSTMENT with (nolock) on REVENUE.ID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE <> 0
                cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, DETAIL.REVENUETRANSACTIONTYPECODE,DETAIL.SPLITTYPECODE, DETAIL.APPLICATIONCODE, REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, REVENUESPLIT.DESIGNATIONID) as tf 
                where    (REVENUE.ID = @REVENUEID or @REVENUEID is null)
                        and (not ((REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = REVENUEPAYMENTMETHOD.ID)) or 
                        (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID and ISSOLD = 1))))  --special handling for sold stock/property

                        and (REVENUESPLIT.APPLICATIONCODE = 2)                                        

                return;
            end

            return;
        end