Retrieve Patron Notice Text (Email, Print, SMS)

Query that retrieves the current text (wording) for each notice type sent out by Polaris. This is the text stored in Polaris and does not include anything from LX Starter or 3rd party notice providers.

Select A.*
,Enabled.Enabled
,interval.Interval
From 
(
Select  *
From 
(
Select o.Abbreviation, O.OrganizationID
, SAD.Mnemonic
, CASE substring(Mnemonic,4,charindex('_',Mnemonic,4)-4)
	WHEN 'BILL' THEN 'Billing Notice'
	WHEN 'FINE' THEN 'Fine Notice'
	WHEN 'OVD' THEN '1st Overdue Notice'
	WHEN 'OVD2ND' THEN '2nd Overdue Notice'
	WHEN 'OVD3RD' THEN '3rd Overdue Notice'
	WHEN 'HOLD' THEN 'Hold Notice'
	WHEN 'EMAIL' THEN 'Email Footer'
	WHEN 'REMINDER' THEN 
		CASE substring(Mnemonic,13,charindex('_',Mnemonic,13)-13)
		WHEN 'EXPIRE' THEN 'Patron Expiration'
		WHEN 'INACTIVE' THEN 'Inactive Patron'
		WHEN 'OVD' THEN 'Almost Overdue'
		When 'Renew' THEN 'AutoRenew'
		END
	ELSE NULL
	END AS NoticeType
, CASE 
	WHEN Mnemonic LIKE '%[_]EM[_]%' THEN 'Email'
	WHEN Mnemonic LIKE '%[_]M[_]%' THEN 'Print'
	WHEN Mnemonic LIKE '%[_]SMS[_]%' THEN 'TXT'
	ELSE 'Email'
	END AS DeliveryFormat
, Polaris.SA_GetMultiLingualStringValue(7, 1033, o.OrganizationID, SAD.Mnemonic) AS VALUE
, CASE When Mnemonic LIKE '%[_]SMS[_]%' THEN LEN(Polaris.SA_GetMultiLingualStringValue(7, 1033, o.OrganizationID, SAD.Mnemonic)) ELSE NULL END AS TxtLen

From Polaris.organizations O (nolock)
CROSS JOIN Polaris.SA_DefaultMultiLingualStrings SAD (nolock)
WHERE ProductID = 7
and Mnemonic IN ('NT_BILL_EM_TEXT', 'NT_BILL_M_TEXT', 'NT_BILL_SMS_TEXT', 'NT_EMAIL_FOOTER', 'NT_FINE_EM_TEXT', 'NT_FINE_M_TEXT', 'NT_FINE_SMS_TEXT', 'NT_HOLD_EM_TEXT', 'NT_HOLD_M_TEXT', 'NT_HOLD_SMS_TEXT', 'NT_OVD_EM_TEXT', 'NT_OVD_M_TEXT', 'NT_OVD_SMS_TEXT', 'NT_OVD2ND_EM_TEXT', 'NT_OVD2ND_M_TEXT', 'NT_OVD3RD_EM_TEXT', 'NT_OVD3RD_M_TEXT', 'NT_REMINDER_EXPIRE_EM_TEXT', 'NT_REMINDER_EXPIRE_SMS_TEXT', 'NT_REMINDER_INACTIVE_EM_TEXT', 'NT_REMINDER_INACTIVE_SMS_TEXT', 'NT_REMINDER_OVD_EM_TEXT', 'NT_REMINDER_OVD_SMS_TEXT', 'NT_REMINDER_RENEW_EM_HEADER', 'NT_REMINDER_RENEW_SMS_TEXT')
and LanguageID = 1033
--AND O.OrganizationID IN (@GIS_MS_Organizations)
) AS A
 
) AS A

LEFT JOIN 
(
	Select O.OrganizationID, AA.Mnemonic
	, CASE  AA.Mnemonic
		WHEN 'NSPARMRMPATEXP' THEN 'Patron Expiration'
		WHEN 'NSPARMRMBFROVD' THEN 'Almost Overdue'
		WHEN 'NSPARMRMINACTV' THEN 'Inactive Patron'
		WHEN 'NSPARMRMOVRDUE' THEN '1st Overdue Notice'
		WHEN 'NSPARMRMBILLING' THEN 'Billing Notice'
		WHEN 'NSPARMRMREQUEST' THEN 'Hold Notice'
		WHEN 'NSPARM_EMN_FINES_ENABLE' THEN 'Fine Notice'
		END AS NoticeType
	, Polaris.fn_SA_GetValue(AA.Mnemonic,O.OrganizationID) AS Enabled
	From Polaris.Organizations O (nolock)
	Cross JOIN Polaris.AdminAttributes AA (nolock)
	WHERE AA.AttrID IN (1275, 1276, 1277, 1612, 1613, 1614, 1971)

	UNION

	Select O.OrganizationID, AA.Mnemonic
	, '2nd Overdue Notice' AS NoticeType
	, Polaris.fn_SA_GetValue(AA.Mnemonic,O.OrganizationID) AS Enabled
	From Polaris.Organizations O (nolock)
	Cross JOIN Polaris.AdminAttributes AA (nolock)
	WHERE AA.AttrID IN (1612)

	UNION

	Select O.OrganizationID, AA.Mnemonic
	, '3rd Overdue Notice' AS NoticeType
	, Polaris.fn_SA_GetValue(AA.Mnemonic,O.OrganizationID) AS Enabled
	From Polaris.Organizations O (nolock)
	Cross JOIN Polaris.AdminAttributes AA (nolock)
	WHERE AA.AttrID IN (1612)
) AS Enabled
ON A.OrganizationID = Enabled.OrganizationID
	AND A.NoticeType = Enabled.NoticeType

LEFT JOIN 
(
Select O.OrganizationID, AA.Mnemonic
, CASE  AA.Mnemonic
	WHEN 'PSPARMOVRDUEINTRVL1' THEN '1st Overdue Notice'
	WHEN 'PSPARMOVRDUEINTRVL2' THEN '2nd Overdue Notice'
	WHEN 'PSPARMOVRDUEINTRVL3' THEN '3rd Overdue Notice'
	WHEN 'PSPARMBILLINGINTRVL' THEN 'Billing Notice'
	WHEN 'NSPARMRMDAYSPATEXP' THEN 'Patron Expiration'
	WHEN 'NSPARMRMDAYSBFROVD' THEN 'Almost Overdue'
	WHEN 'NSPARMRMDAYSINACTV' THEN 'Inactive Patron'
	WHEN 'NSPARM_EMN_FINE_FINEAGE' THEN 'Fine Notice'
	END AS NoticeType
, Polaris.fn_SA_GetValue(AA.Mnemonic,O.OrganizationID) AS Interval
From Polaris.Organizations O (nolock)
Cross JOIN Polaris.AdminAttributes AA (nolock)
WHERE AA.AttrID IN (995, 996, 997, 998, 1269, 1270, 1271, 1973)
) AS Interval
	ON A.OrganizationID = Interval.OrganizationID
	AND A.NoticeType = Interval.NoticeType

ORDER BY DeliveryFormat, NoticeType, Abbreviation
1 Like