CREATE TABLE IF NOT EXISTS `def_email_template` (
`TemplateCode` VARCHAR(64) NOT NULL,
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT 'ObjTemplate',
`Channel` VARCHAR(16) NOT NULL DEFAULT 'Email',
`Subject` VARCHAR(500) NOT NULL DEFAULT '',
`Body` LONGTEXT,
`Layout` VARCHAR(64) DEFAULT '',
`Description` VARCHAR(500) DEFAULT '',
`Active` CHAR(1) DEFAULT 'Y',
`TemplateEngine` VARCHAR(16) DEFAULT 'legacy',
`UpdateDate` DATETIME
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`TemplateCode`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
SELECT
TemplateCode, Channel, Subject,
Body, Layout, Description,
COALESCE(TemplateEngine, 'legacy')
FROM def_email_template WHERE TemplateCode = '{template_code}'
AND Package IN ('', 'CORE',
'{package}', '{archetype}')
AND Active = 'Y'
ORDER BY
CASE WHEN Package = '{package}'
THEN 0 ELSE 1 END
LIMIT 1
SELECT
TemplateCode, Channel, Subject,
Layout, Description, Active
FROM def_email_template WHERE Package IN ('', '{package}') ORDER BY TemplateCode