ESP - Add Notifications to All Active Companies

/*
This script will add Notifications to all active companies
Please run in SQLAgent every 15 minutes.

Modified	Steven F Ling	2019-11-27
Modified	Steven F Ling	2023-08-14
*/
DECLARE @companyid  INT
DECLARE @name		VARCHAR(100)
DECLARE @regionName	VARCHAR(100)
DECLARE @not1id INT, @not2id INT, @not3id INT, @not4id INT, @not5id	INT

--##### User Variables
--##### Please set these variables before running this script
DECLARE @updateUser VARCHAR(100) = 'AddNotificationScript'
DECLARE @messageSystemID1 INT = (SELECT id FROM mscmessagesystem WHERE name = 'Invoice_Export')
DECLARE @messageSystemID2 INT = (SELECT id FROM mscmessagesystem WHERE name = 'espOrderExport')
DECLARE @messageSystemID3 INT = (SELECT id FROM mscmessagesystem WHERE name = 'ESP Docket Export')
DECLARE @messageSystemID4 INT = (SELECT id FROM mscmessagesystem WHERE name = 'CreditNote_Export')
DECLARE @messageSystemID5 INT = (SELECT id FROM mscmessagesystem WHERE name = 'espOrderExport')


-- Where clause added to look for only Active companies
DECLARE look CURSOR FOR SELECT c.id,c.name
						FROM orgcompany c
                        INNER JOIN orgaddress a ON c.billingAddressID=a.id
						LEFT Join orgNotification n on n.forCompanyID = c.id
                        WHERE companystatus='Active'
						and n.id is null
						--and n.deliverbyMessageSystemID != @messageSystemID3
                        ORDER BY c.id

						 

OPEN look
FETCH NEXT FROM look INTO @companyid, @name
WHILE @@FETCH_status=0
BEGIN

print ''
print '=====Starting with company and looking for Notifications: '+CONVERT(VARCHAR,@name)+'====='
print ''

--##### The notificationtype must be changed on each of the following lines to reflect the notifications being used
SELECT @not1id=(SELECT id FROM orgnotification WHERE forcompanyid=@companyid AND notificationtype ='InvoicePosted' and name ='Invoice')
SELECT @not2id=(SELECT id FROM orgnotification WHERE FORcompanyid=@companyid AND notificationtype ='OrderConfirmed')
SELECT @not3id=1 --(SELECT id FROM orgnotification WHERE FORcompanyid=@companyid AND notificationtype ='DocketImported')
SELECT @not4id=1 --(SELECT id FROM orgnotification WHERE forcompanyid=@companyid AND notificationtype ='InvoicePosted' and name ='Credit')
SELECT @not5id=(SELECT id FROM orgnotification WHERE FORcompanyid=@companyid AND notificationtype ='OrderCancelled')
--##### END notificationtype changes

prINT 'Notification adding process FOR: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

--Check to see if notification 1 exists the begin`
--IF @not1id IS NULL
--				 BEGIN

--				 print 'Adding Invoice Export FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)
                
--					 INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) 
--					 VALUES(GETDATE(),@updateUser,'InvoicePosted',0,2,'Invoice_Export',@companyid,@messageSystemID1,NULL,NULL,'SAPInvoiceExport',null)

--				 print 'Added'
--				 END
--				 Else
--				 print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

/*   Comment out this section if you are not using Order Confirmations  */
 IF @not2id IS NULL
                 BEGIN
                
                 print 'Adding the ESP_Order Confirmation Export NotIFication FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)
                
					INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) 
					VALUES(GETDATE(),@updateUser,'OrderConfirmed',0,2,'ESPOrder_Export',@companyid,@messageSystemID2,NULL,NULL,'OrderConfirmed', null) 

                 print 'Added'
                 END
				 Else
				 print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

/*  This is the Docket Export Section */ 
--IF @not3id IS NULL
--                BEGIN
--                print 'Added the ESP_Docket Export FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)
                
--					INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) 
--					VALUES(GETDATE(),@updateUser,'DocketFirstShipped',0,2,'ESP Docket Export',@companyid,@messageSystemID3,NULL,NULL,'ESP_Docket Export Notification', '[masterbilloflading.masterbolnumber]&"_"&[docketnumber]&"_"&[plant.plantcode]&"_"')               

--                print 'Added'
--                END
--				Else
--				print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

--Check to see if notification 4 exists the begin`
--IF @not4id IS NULL
--				 BEGIN

--				 print 'Adding the Invoice Export FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)
                
--				 INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) 
--				 VALUES(GETDATE(),@updateUser,'InvoicePosted',0,2,'SFR015_CreditNote_Export',@companyid,@messageSystemID4,NULL,NULL,'SAPCreditExport',null)

--				 print 'Added'
--				 END
--				 Else
--				 print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

--Check to see if notification 5 exists the begin`
IF @not5id IS NULL
                 BEGIN
                
                 print 'Adding the ESP_Order Cancelled Export NotIfication FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)
                
					INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) 
					VALUES(GETDATE(),@updateUser,'OrderCancelled',0,2,'ESPOrder_Export',@companyid,@messageSystemID2,NULL,NULL,'OrderCancelled', null) 

                 print 'Added'
                 END
				 Else
				 print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)
                
FETCH NEXT FROM look INTO @companyid,@name
print ''
print '=====Ended with '+'Company Name: '+CONVERT(VARCHAR,@name)+' ====='
print ''
print ''
END

CLOSE look
DEALLOCATE look