Check scheme.plitempm for any orphaned entries against plpy tables, found to be caused by third party app not Sage Line 500.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:          Christopher Smith
-- Create date: 10/02/2022
-- Description:     Check scheme.plitempm for any orphaned entries
-- =============================================
-- Change Log
-- =============================================
-- 1.0 - First Version
-- =============================================
-- exec usp_email_orphan_payment_list_entries
-- =============================================
CREATE PROCEDURE usp_email_orphan_payment_list_entries
AS
BEGIN
       SET NOCOUNT ON;
--Populate Temp Table and format table name
select  'plpy' + MAX(payment_list) +'m' as payment_list,'n' as "tableexists", ROW_NUMBER() OVER(ORDER BY payment_list ASC) AS Rowno into #tempjobs from [scheme].[plitempm]
where payment_list != '' group by payment_list
--Check if table exists
declare @jobno INT = 0, @maxjobno int, @currenttable NVARCHAR(14), @sqlquery NVARCHAR(400)
set @maxjobno = (select COUNT(*) from #tempjobs)
WHILE @jobno <= @maxjobno
       BEGIN
             set @jobno = @jobno + 1
             set @currenttable = (select payment_list from #tempjobs where @jobno = Rowno)
             set @sqlquery = ('update #tempjobs set #tempjobs.tableexists = (select (CASE WHEN COUNT(*) > 0 THEN ''y'' ELSE ''n'' END) from dbo.sysobjects t1 INNER JOIN #tempjobs on t1.name = #tempjobs.payment_list where #tempjobs.payment_list LIKE ''' + @currenttable +''') where #tempjobs.payment_list LIKE ''' + @currenttable +'''')
             --print @sqlquery
                    EXEC(@sqlquery)    
       END
--Remove entries where the table exists
Delete from #tempjobs where tableexists = 'y'
--If there is more than one result email
if (select count(*) from #tempjobs) > 0 begin
--------------------------------------------------------------------------------------------------------------------------
----                                     Set variables
--------------------------------------------------------------------------------------------------------------------------
declare @email char(120), @ccmail char(120), @wh char(2), @header NVARCHAR(MAX), @body varchar(max), @footer varchar(max), @tableHTML  NVARCHAR(MAX)
--------------------------------------------------------------------------------------------------------------------------
----                                     Create Header
--------------------------------------------------------------------------------------------------------------------------
SET @header =
    N'<font size="4" face="arial" color="black"> Orphan entries found in scheme.plitempm on SGE_BEBV!</font>'  +
       N'<font size="4" face="arial" color="black"> Invoices will be missed from newly generated payment lists, check scheme.plitempm and change field payment_list to blank where the payment list is not found to exist.</font>' +
    N'<table border="1">' +
    N'<tr><th>Payment List</th></tr>'
--------------------------------------------------------------------------------------------------------------------------
----                                     Create Detail
--------------------------------------------------------------------------------------------------------------------------
select @body =
( select
td = rtrim(isnull((payment_list),'')),''
from #tempjobs
FOR XML PATH('tr')
)
--------------------------------------------------------------------------------------------------------------------------
----                                     Create Footer
--------------------------------------------------------------------------------------------------------------------------
select @footer =
N'</table>'  +
N'<p></p>' +
N'<p> Regards.</p>' +
N'<p><font size="2" face="calibri"><Name></p>' +
N'<p> </p>' +
N'<p>usp_email_orphan_payment_list_entries on sge_bebv</p>'
--------------------------------------------------------------------------------------------------------------------------
----                                     Add Formating
--------------------------------------------------------------------------------------------------------------------------
set @header = REPLACE(@header, '<th>', '<th align=center bgcolor=#EEEEF4><font size="2" face="calibri">')
set @header = REPLACE(@header, '</th>', '</font></th>')
set @body = REPLACE(@body, '<td>', '<td align=center bgcolor=#F8F8FD><font size="2" face="calibri">')
set @body = REPLACE(@body, '</td>', '</font></td>')
set @footer = REPLACE(@footer, '<p>', '<p> <font size="2" face="calibri">')
set @footer = REPLACE(@footer, '</p>', '</font></p> ')
set @tableHTML = @header + @body + @footer  + '</table></div>'
set @tableHTML = '<div style="color:Black; font-size:11pt; font-family:Calibri; width:100px;">' + @tableHTML + '</div>'
 
-----Send Email---------------------------------------------------------------------------------------------------------
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '<Insert email profile name>',
       @recipients = '<Insert Email Address>',
       @body = @tableHTML,
    @body_format = 'HTML',
       @subject = 'Orphaned Payment List Entries'
       end
--------------------------------------------------------------------------------------------------------------------------
----                                     Clearup temp tables
--------------------------------------------------------------------------------------------------------------------------
       drop table #tempjobs
END
GO