UFN_REVENUETRANSACTION_GETSTOCKDETAILGLDISTRIBUTION

            CREATE function dbo.UFN_REVENUETRANSACTION_GETSTOCKDETAILGLDISTRIBUTION(@TRANSACTIONID uniqueidentifier) 
            returns @STOCKDETAILGLDISTRIBUTION table
            (
                ID uniqueidentifier null,
                STOCKDETAILID 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 STOCKDETAILGLDISTRIBUTION then get those (user has chosen to override the system generated entries */
                if exists(select STOCKDETAILGLDISTRIBUTION.ID from dbo.STOCKDETAILGLDISTRIBUTION inner join dbo.REVENUEPAYMENTMETHOD on STOCKDETAILGLDISTRIBUTION.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
                            where REVENUEPAYMENTMETHOD.REVENUEID = @TRANSACTIONID)
                    begin
                        insert into @STOCKDETAILGLDISTRIBUTION (ID, STOCKDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, DEBITACCOUNT, CREDITACCOUNT, PROJECT, AMOUNT, REFERENCE)
                        select STOCKDETAILGLDISTRIBUTION.ID, REVENUEPAYMENTMETHOD.ID, STOCKDETAILGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID, STOCKDETAILGLDISTRIBUTION.DEBITACCOUNT, 
                                STOCKDETAILGLDISTRIBUTION.CREDITACCOUNT, STOCKDETAILGLDISTRIBUTION.PROJECT, STOCKDETAILGLDISTRIBUTION.AMOUNT, STOCKDETAILGLDISTRIBUTION.REFERENCE
                            from dbo.STOCKDETAILGLDISTRIBUTION 
                            inner join dbo.REVENUEPAYMENTMETHOD on STOCKDETAILGLDISTRIBUTION.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
                            where REVENUEPAYMENTMETHOD.REVENUEID = @TRANSACTIONID;
                    end
                else
                    begin
                        declare @Temp Table 
                            (GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier not null,
                             STOCKDETAILID 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 (STOCKDETAILID, 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_GENERATESTOCKDETAILGLDISTRIBUTION(REVENUE.ID) as tf
                                where REVENUE.ID = @TRANSACTIONID


                        insert into @STOCKDETAILGLDISTRIBUTION (ID, STOCKDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, CREDITACCOUNT, DEBITACCOUNT, PROJECT, AMOUNT, REFERENCE)
                        select NULL,
                            o.STOCKDETAILID,
                           (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.STOCKDETAILID;
                    end
                return
            end