UFN_BATCHTEMPLATE_GETORIGINATINGTEMPLATE

            create function [dbo].[UFN_BATCHTEMPLATE_GETORIGINATINGTEMPLATE]
            (
            )
            returns @ULTIMATEBATCHTEMPLATE table(
                ID uniqueidentifier,
                ORIGINATINGTEMPLATENAME nvarchar(200),
                ORIGINATINGTEMPLATEID uniqueidentifier
            )
            as
            begin

                --these batch template CTEs find the chain of batch templates, where each is created from the next and further down to the original batch template

                with cte_name(ID, LEVELS, ORIGINATINGTEMPLATEID, ORIGINATINGTEMPLATENAME, LOOKUPID)
                    as
                    (
                        select bt.ID, 0 as levels, bt.ID, bt2.NAME,  bt2.ID
                        from dbo.BATCHTEMPLATE bt 
                        left outer join dbo.BATCHTEMPLATE bt2 on bt.ORIGINATINGTEMPLATEID = bt2.ID    
                        union all
                        select n.ID, n.LEVELS + 1, bt3.ID, bt3.NAME, bt3.ORIGINATINGTEMPLATEID
                        from cte_name n 
                        inner join dbo.BATCHTEMPLATE bt3 on n.LOOKUPID = bt3.ID
                    )

                --Get the ID and name of the last link of batchtemplate in the chain and insert them into table

                insert into @ULTIMATEBATCHTEMPLATE
                    select n.ID, n.ORIGINATINGTEMPLATENAME, n.ORIGINATINGTEMPLATEID
                    from cte_name n 
                    inner join
                        ( 
                            select MAX(n.LEVELS) MAXLEVEL, n.ID
                            from cte_name n
                            group by id  
                        ) m on m.ID = n.ID and m.MAXLEVEL = n.LEVELS 
                return;
            end