UFN_REVENUE_GENERATEPROPERTYDETAILGLDISTRIBUTION

            CREATE function dbo.UFN_REVENUE_GENERATEPROPERTYDETAILGLDISTRIBUTION(@REVENUEID uniqueidentifier = null)
            returns table 
            as return 
            /*    Getting 'Postable' Property Sales    */
            select 
                REVENUE.ID as REVENUEID, 
                REVENUESPLIT.ID as REVENUESPLITID, 
                dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(CODES.PAYMENTMETHODCODE, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                tf.TRANSACTIONTYPECODE, 
                case when PROPERTYDETAILADJUSTMENT.ID is null then PROPERTYDETAIL.SALEPOSTDATE            --if no unposted adjustment then use Property Detail Post Date

                     else PROPERTYDETAILADJUSTMENT.POSTDATE
                end AS POSTDATE,                 
                case when PROPERTYDETAILADJUSTMENT.ID is null then PROPERTYDETAIL.SALEPOSTSTATUSCODE            --if no unposted adjustment then use Property Detail Post Status Code

                     else PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE
                end AS POSTSTATUSCODE,
                tf.ACCOUNTSTRING, 
                tf.PROJECTCODE as PROJECT, 
                case    
                    when CODES.PAYMENTMETHODCODE = 8 then coalesce(REVENUESPLIT.AMOUNT, REVENUE.AMOUNT) 
                    when CODES.PAYMENTMETHODCODE in (201, 203, 204) then SPLITS.AMOUNT
                end as AMOUNT, 
                dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE  (REVENUESPLIT.ID, CODES.PAYMENTMETHOD, CODES.REVENUETYPE) as REFERENCE, 
                tf.ERRORMESSAGE,
                REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                REVENUEPAYMENTMETHOD.ID as REVENUEPAYMENTMETHODID,
                tf.ACCOUNTID as ACCOUNTID, 
                case    
                    when CODES.PAYMENTMETHODCODE = 8 then coalesce(REVENUESPLIT.BASECURRENCYID, REVENUE.BASECURRENCYID) 
                    when CODES.PAYMENTMETHODCODE in (201, 203, 204) then SPLITS.BASECURRENCYID
                end as BASECURRENCYID,  
                case    
                    when CODES.PAYMENTMETHODCODE = 8 then coalesce(REVENUESPLIT.TRANSACTIONAMOUNT, REVENUE.TRANSACTIONAMOUNT) 
                    when CODES.PAYMENTMETHODCODE in (201, 203, 204) then SPLITS.SPLITTRANSACTIONAMOUNT
                end as TRANSACTIONAMOUNT,  
                case    
                    when CODES.PAYMENTMETHODCODE = 8 then coalesce(REVENUESPLIT.TRANSACTIONCURRENCYID, REVENUE.TRANSACTIONCURRENCYID) 
                    when CODES.PAYMENTMETHODCODE in (201, 203, 204) then SPLITS.TRANSACTIONCURRENCYID
                end as TRANSACTIONCURRENCYID,  
                case    
                    when CODES.PAYMENTMETHODCODE = 8 then coalesce(REVENUESPLIT.BASEEXCHANGERATEID, REVENUE.BASEEXCHANGERATEID) 
                    when CODES.PAYMENTMETHODCODE in (201, 203) then SPLITS.BASEEXCHANGERATEID
                    when CODES.PAYMENTMETHODCODE = 204 then PROPERTYDETAIL.BASEEXCHANGERATEID
                end as BASEEXCHANGERATEID,  
                case    
                    when CODES.PAYMENTMETHODCODE = 8 then coalesce(REVENUESPLIT.ORGANIZATIONAMOUNT, REVENUE.ORGANIZATIONAMOUNT) 
                    when CODES.PAYMENTMETHODCODE in (201, 203, 204) then SPLITS.ORGANIZATIONAMOUNT
                end as ORGANIZATIONAMOUNT,  
                case    
                    when CODES.PAYMENTMETHODCODE = 8 then coalesce(REVENUESPLIT.ORGANIZATIONEXCHANGERATEID, REVENUE.ORGANIZATIONEXCHANGERATEID) 
                    when CODES.PAYMENTMETHODCODE in (201, 203) then SPLITS.ORGANIZATIONEXCHANGERATEID
                    when CODES.PAYMENTMETHODCODE = 204 then PROPERTYDETAIL.ORGANIZATIONEXCHANGERATEID
                end as ORGANIZATIONEXCHANGERATEID,
                tf.MAPPEDVALUES
            from dbo.REVENUE with (nolock)            
                inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
                inner join dbo.PROPERTYDETAIL with (nolock) on REVENUEPAYMENTMETHOD.ID = PROPERTYDETAIL.ID
                left join dbo.PROPERTYDETAILADJUSTMENT with (nolock) on PROPERTYDETAIL.ID = PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID and PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE <> 0                
                cross join 
                    ( 
                        select '8' as PAYMENTMETHODCODE, 'Sold Property' as PAYMENTMETHOD, 'All' as REVENUETYPE, '200' as REVENUETYPECODE 
                            union all 
                        select '201' as PAYMENTMETHODCODE, 'Gain' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '200' as REVENUETYPECODE 
                            union all  
                        select '203' as PAYMENTMETHODCODE, 'Loss' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '200' as REVENUETYPECODE 
                            union all 
                        select '204' as PAYMENTMETHODCODE, 'Fees' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '200' as REVENUETYPECODE 
                    ) as CODES
                cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.PAYMENTMETHODCODE, REVENUESPLIT.DESIGNATIONID) as tf 
                outer apply dbo.UFN_REVENUE_GETPROPERTYDETAILSPLITSBYTRANSACTION(REVENUE.ID, CODES.PAYMENTMETHODCODE) as SPLITS
            where 
                (REVENUE.ID = @REVENUEID or @REVENUEID is NULL) 
                and ((CODES.PAYMENTMETHODCODE = 201 and PROPERTYDETAIL.TRANSACTIONSALEAMOUNT > REVENUE.TRANSACTIONAMOUNT) or    -- Property Gain

                     (CODES.PAYMENTMETHODCODE = 203 and PROPERTYDETAIL.TRANSACTIONSALEAMOUNT < REVENUE.TRANSACTIONAMOUNT) or    -- Property Loss

                     (CODES.PAYMENTMETHODCODE = 204 and PROPERTYDETAIL.TRANSACTIONBROKERFEE > 0) or                               -- Broker Fee

                     (CODES.PAYMENTMETHODCODE = 8))                                                                                -- Sold Property

                and (SPLITS.REVENUESPLITID = REVENUESPLIT.ID or (SPLITS.REVENUESPLITID is null and REVENUESPLIT.ID is null and REVENUESPLIT.APPLICATIONCODE = 1 and SPLITS.REVENUEID = REVENUE.ID))
                and (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID and ISSOLD = 1));