On and off I am working in SolvencyII projects (financial reporting to governments for companies in the insurance industry). There are a lot of QRT-reports (Quantitative Reporting Templates) that has to be reported to the local authorities, and a while ago Eiopa decided to change the names on all the reports.
Almost all my code in the SolvencyII projects is based on the old names, but all new and changed directives are based on the new names. And of course I keep forgetting the new names…
So I finally decided to summarize the QRT report names and store them in a more permanent way along with translations between old and new names. So here it is! Below in T-SQL and attached as a text file.
Based on this document.
Run this to create the table:
CREATE TABLE dbo.QRTTemplateNames ( TemplateCurrentCode CHAR(7) NOT NULL CONSTRAINT TemplateCurrentCode_pk PRIMARY KEY CLUSTERED, TemplateOldCode VARCHAR(20) NULL, TemplateName VARCHAR(250) NOT NULL ) GO
And run this to populate the table:
INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.01.01', NULL, N'Content of the submission') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.01.02', NULL, N'Basic information - General') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.01.03', NULL, N'Basic information - RFF and matching adjustment portfolios') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.02.01', N'BS-C1', N'Balance Sheet') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.02.02', N'BS-C1D', N'Assets and liabilities by currency') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.03.01', N'BS-C1B', N'Off-balance sheet items -general') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.03.02', N'BS-C1B', N'Off-balance sheet items - List of unlimited guarantees received by the undertaking') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.03.03', N'BS-C1B', N'Off-balance sheet items - List of unlimited guarantees provided by the undertaking') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.04.01', N'Country-K1', N'Activity by country') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.04.02', N'Country-K1', N'Information on class 10 in Part A of Annex I of Solvency II Directive, excluding carriers liability') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.05.01', N'Cover-A1A', N'Premiums, claims and expenses by line of business') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.05.02', N'Cover-A1A', N'Premiums, claims and expenses by country') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.06.01', N'Assets-D1Q', N'Summary of assets') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.06.02', N'Assets-D1', N'List of assets') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.06.03', N'Assets-D3', N'Collective investment undertakings - look-through approach') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.07.01', N'Assets-D1S', N'Structured products') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.08.01', N'Derivatives-D2O', N'Open derivatives') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.08.02', N'Derivatives-D2T', N'Derivatives Transactions') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.09.01', N'Profit and Loss', N'Income/gains and losses in the period') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.10.01', N'Assets-D5', N'Securities lending and repos') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.11.01', N'Assets-D6', N'Assets held as collateral') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.12.01', N'TP-F1', N'Life and Health SLT Technical Provisions') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.12.02', N'TP-F1', N'Life and Health SLT Technical Provisions - By country') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.12.03', NULL, N'Best estimate by currency and country - Life') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.13.01', N'TP-F2', N'Projection of future gross cash flows') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.14.01', N'TP-F3', N'Life obligations analysis') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.15.01', N'TP-F3A', N'Description of the guarantees of variable annuities') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.15.02', N'TP-F3B', N'Hedging of guarantees of variable annuities') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.16.01', N'TP-F4', N'Information on annuities stemming from Non-Life Insurance obligations') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.17.01', N'TP-E1', N'Non-Life Technical Provisions') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.17.02', N'TP-E1', N'Non-Life Technical Provisions - By country') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.17.03', NULL, N'Best estimate by currency and country - Non Life') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.18.01', N'TP-E2', N'Projection of future cash flows') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.19.01', N'TP-E3', N'Non-life insurance claims') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.20.01', N'TP-E4', N'Development of the distribution of the claims incurred ') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.21.01', N'TP-E6', N'Loss distribution risk profile') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.21.02', N'TP-E7A', N'Non-life underwriting peak risks') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.21.03', N'TP-E7B', N'Non-life underwriting mass risks') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.22.01', NULL, N'Impact of long term guarantees and transitional measures') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.22.02', NULL, N'Projection of future cash flows (Best Estimate - Matching portfolios)') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.22.03', NULL, N'Information on the matching adjustment calculation') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.22.04', NULL, N'Information on the transitional on interest rates calculation') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.22.05', NULL, N'Overall calculation of the transitional on technical provisions') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.23.01', N'OF-B1', N'Own Funds') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.23.02', N'OF-B1', N'Detailed information by tiers on own funds') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.23.03', N'OF-B1', N'Annual movements on own funds') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.23.04', N'OF-B1', N'List of items on own funds') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.24.01', N'Participations', N'Participations held') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.25.01', N'SCR-B2A', N'Solvency Capital Requirement - Only SF') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.25.02', N'SCR-B2B', N'Solvency Capital Requirement - SF and PIM') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.25.03', N'SCR-B2C', N'Solvency Capital Requirement - IM') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.26.01', N'SCR-B3A', N'Solvency Capital Requirement - Market risk') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.26.02', N'SCR-B3B', N'Solvency Capital Requirement - Counterparty default risk') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.26.03', N'SCR-B3C', N'Solvency Capital Requirement - Life underwriting risk') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.26.04', N'SCR-B3D', N'Solvency Capital Requirement - Health underwriting risk') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.26.05', N'SCR-B3E', N'Solvency Capital Requirement - Non-Life underwriting risk') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.26.06', N'SCR-B3G', N'Solvency Capital Requirement - Operational risk') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.26.07', NULL, N'Solvency Capital Requirement - Simplifications') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.27.01', N'SCR-B3F', N'Solvency Capital Requirement - Non-Life Catastrophe risk') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.28.01', N'MCR-B4A', N'Minimum Capital Requirement - Non-Composite') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.28.02', N'MCR-B4B', N'Minimum Capital Requirement - Composite') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.29.01', N'VA-C2A', N'Excess of Assets over Liabilities') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.29.02', N'VA-C2B', N'Excess of Assets over Liabilities - explained by investments and financial liabilities') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.29.03', N'VA-C2C', N'Excess of Assets over Liabilities - explained by technical provisions') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.29.04', N'VA-C2C', N'Detailed analysis per period - Technical flows versus Technical provisions') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.30.01', N'Re-J1 - Basic', N'Facultative covers for non-life and life business basic data') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.30.02', N'Re-J1 - Shares', N'Facultative covers for non-life and life business shares data') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.30.03', N'Re-J2 - Basic', N'Outgoing Reinsurance Program basic data') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.30.04', N'Re-J2 – Shares', N'Outgoing Reinsurance Program shares data') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.31.01', N'Re-J3', N'Share of reinsurers') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.31.02', N'Re-SPV', N'Special Purpose Vehicles ') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.32.01', N'G01', N'Undertakings in the scope of the group') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.33.01', N'G03', N'(Re)insurance individual requirements') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.34.01', N'G04', N'Non-(re)insurance individual requirements') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.35.01', N'G14', N'Contribution to group TP') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.36.01', N'IGT1', N'IGT - Equity-type transactions, debt and asset transfer ') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.36.02', N'IGT2', N'IGT - Derivatives') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.36.03', N'IGT3', N'IGT - Internal reinsurance ') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.36.04', N'IGT4', N'IGT - Cost Sharing, contingent liabilities, off BS and other items') INSERT dbo.QRTTemplateNames (TemplateCurrentCode, TemplateOldCode, TemplateName) VALUES (N'S.37.01', N'RC', N'Risk concentration')
