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