UFN_REVENUETRANSACTION_GETPROPERTYDETAILGLDISTRIBUTION

            CREATE function dbo.UFN_REVENUETRANSACTION_GETPROPERTYDETAILGLDISTRIBUTION(@TRANSACTIONID uniqueidentifier) 
            returns @PROPERTYDETAILGLDISTRIBUTION table
            (
                ID uniqueidentifier null,
                PROPERTYDETAILID uniqueidentifier null,
                GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier not null,
                CREDITACCOUNT nvarchar(100) not null,
                DEBITACCOUNT nvarchar(100) not null,
                PROJECT nvarchar(100) null,
                AMOUNT money not null,
                REFERENCE nvarchar(100) not null
            )
            as 
            begin    
                /*If entry exists in PROPERTYDETAILGLDISTRIBUTION then get those (user has chosen to override the system generated entries */
                if exists(select PROPERTYDETAILGLDISTRIBUTION.ID from dbo.PROPERTYDETAILGLDISTRIBUTION inner join dbo.REVENUE on PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID = REVENUE.ID
                            where REVENUE.ID = @TRANSACTIONID)
                    begin
                        insert into @PROPERTYDETAILGLDISTRIBUTION (ID, PROPERTYDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, DEBITACCOUNT, CREDITACCOUNT, PROJECT, AMOUNT, REFERENCE)
                        select PROPERTYDETAILGLDISTRIBUTION.ID, REVENUEPAYMENTMETHOD.ID, PROPERTYDETAILGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID, PROPERTYDETAILGLDISTRIBUTION.DEBITACCOUNT, 
                                PROPERTYDETAILGLDISTRIBUTION.CREDITACCOUNT, PROPERTYDETAILGLDISTRIBUTION.PROJECT, PROPERTYDETAILGLDISTRIBUTION.AMOUNT, PROPERTYDETAILGLDISTRIBUTION.REFERENCE
                            from dbo.PROPERTYDETAILGLDISTRIBUTION 
                            inner join dbo.REVENUE
                                on PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID = REVENUE.ID
                            inner join dbo.REVENUEPAYMENTMETHOD
                                on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                            where REVENUE.ID = @TRANSACTIONID;
                    end
                else
                    begin
                        declare @Temp Table 
                            (GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier not null,
                             PROPERTYDETAILID uniqueidentifier not null,
                             DEBITCREDIT nvarchar(1) not null,
                             ACCOUNTSTRING nvarchar(100) not null,
                             PROJECT nvarchar(100) not null,
                             AMOUNT money not null,
                             REFERENCE nvarchar(100) not null,
                             GROUPID nvarchar(100) not null);

                        insert @Temp (PROPERTYDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, DEBITCREDIT, ACCOUNTSTRING, PROJECT, AMOUNT, REFERENCE, GROUPID)
                            select    REVENUEPAYMENTMETHOD.ID, 
                                    tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                                    tf.DEBITCREDIT, 
                                    case when tf.ERRORMESSAGE = '' then ACCOUNTSTRING else '' end as ACCOUNTSTRING, 
                                    tf.PROJECT, 
                                    tf.AMOUNT, 
                                    tf.REFERENCE,
                                    tf.GROUPID
                                from dbo.REVENUE
                                inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                                cross apply DBO.UFN_REVENUE_GENERATEPROPERTYDETAILGLDISTRIBUTION(REVENUE.ID) as tf
                                where REVENUE.ID = @TRANSACTIONID


                        insert into @PROPERTYDETAILGLDISTRIBUTION (ID, PROPERTYDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, CREDITACCOUNT, DEBITACCOUNT, PROJECT, AMOUNT, REFERENCE)
                        select NULL,
                            o.PROPERTYDETAILID,
                           (select GLPAYMENTMETHODREVENUETYPEMAPPINGID from @Temp as t where t.GROUPID = o.GROUPID and t.DEBITCREDIT = 'C') as GLPAYMENTMETHODREVENUETYPEMAPPINGID,                        
                           (select ACCOUNTSTRING from @Temp as t where t.GROUPID = o.GROUPID and t.DEBITCREDIT = 'C') as CREDITACCOUNT,
                           (select ACCOUNTSTRING from @Temp as t where t.GROUPID = o.GROUPID and t.DEBITCREDIT = 'D') as DEBITACCOUNT,
                           (select PROJECT from @Temp as t where t.GROUPID = o.GROUPID and t.DEBITCREDIT = 'C') as PROJECT,
                           (select AMOUNT from @Temp as t where t.GROUPID = o.GROUPID and t.DEBITCREDIT = 'C') as AMOUNT,
                           (select REFERENCE from @Temp as t where t.GROUPID = o.GROUPID and t.DEBITCREDIT = 'C') as REFERENCE
                        from @Temp as o        
                        group by o.GROUPID, o.PROPERTYDETAILID;
                    end
                return
            end