Fields expected by Babacus Certificate Export Tool

The Export Tool issues SQL queries using the specified ODBC or OleDB connection string to retrieve data from the certificate authority (CA) database. CA's should send as many fields as possible for proper quality assurance. Proper operation of the tool does require that certain fields be present in the data returned by the SQL statements. String-valued fields can be of any reasonable length unless otherwise specified.

The SQL queries must contain columns whose names are spelled exactly like the values in the "name of field" column in the tables below. The "type of field" describes the SQL data type of the field. The "criticality" column below indicates the measure of how essential a field is. The degrees of criticality are:

REQUIRED - These fields are critical for the export to work. Missing or incorrect fields generate an error diagnostic and the certificate records in which they occur are rejected by the Export tool.

Important - These fields are important in order to match the correct site from the CA with the corresponding site from PAR, and to correctly specify to what areas the certifications have been issued. Record with missing or incorrect fields may generate a warning diagnostic, but are accepted by the Export tool.

Optional - These fields are optional for each CA. Missing fields generate no warnings.

Finally, "description" is a description of the semantics (i.e. meaning or interpretation) of the data field.

The following fields are expected to exist as columns in the returned data table when issuing the certificate query:

Name of field	SQL type of field	Criticality	Description
certId	varchar(20)	REQUIRED	A unique identifier for the certification. Preferrably it is the certificate number of the certification in question. At the very least, it is a primary key column in a table in the CA database. For proper operation, the certification IDs are expected to be unique for each issued certification, and persistent from one import to the next. If multiple sites have been certified with the same certification, the sites should all have the same certID.
certType	varchar(50)	REQUIRED	The type of the certificate. Only the following strings are recognized, spelled exactly as shown (case sensitive comparison is used, and spaces are significant). An attempt has been made to keep these strings identical to the normative spelling of each certificate type. If the certification authority has a different internal representation for one of these certificate types, it is the responsibility of the certificate authority to make an appropriate conversion to one of the following types. This list may grow in the future to accomodate certificate types that Babacus isn't aware of at the time of writing. [If you notice that there are missing or misspelt certificates, please contact Babacus to address the issue.] 2003/87/EC 93/42/EEC 97/23/EC A3CR 012 Rev. 1 AFS 2001:1 AFS 2017:3 ANSI/ESD S20.20-2007 AQAP 2110 AQAP 2120 AS/EN/JISQ 9100 AS9120A ASC BEK 1409 af 26/09/2020 BEK nr 1191 af 09/10/2013 BEK nr 1510 af 14/12/2018 BF9K BRC - IoP Packaging BRC - Consumer BRC - Storage and Distribution BRC - Food CEN/TS 16555-1:2013 Codex HACCP DS 21001:2020 DS/OHSAS 18001:2008 ECM ECM förordningen EU 445/2011 ECM, (EU) 2019/779 ECM, förordningen (EU) 2019/779 ECM, regulation (EU) 2019/779 EMAS EN 1090-1:2009+A1:2011 EN 12620:2002/A1:2008 EN 13043:2002/AC:2004 EN 13139:2002/AC:2004 EN 13225:2013 EN 13242:2002/A1:2007 EN 13369:2018 EN 13450:2002/AC:2004 EN 13450:2002/AC:2012 EN 13747:2005+A2:2010 EN 14351-1:2006+A2:2016 EN 14843:2007 EN 14991:2007 EN 14992:2007+A1:2012 EN 15085-2:2007 EN 15085-2:2020 EN 15085-4:2007 EN 15224:2012 EN 15224:2017 EN 15258:2008 EN 16034:2014 EN 206:2013+A2:2021 EN 9120:2009 EN 9120:2010 EN 934-2:2009/A1:2012 EN 934-4:2009 EN 934-5:2007 EPD The Flower FAMI-QS FHV-Kravdokument FR2000 FSC FSSC 22000 GMP+ Grundcertifiering Gris GTP HACCP FS System IATF 16949:2016 IFS IFS - Logistics IP Livsmedel Swedish Seal IRIS ISO 12647-2:2016 ISO 13485:2003 ISO 13485:2012 ISO 13485:2016 ISO 14001:2004 ISO 14001:2015 ISO 15378:2011 ISO 15378:2015 ISO 15378:2017 ISO 18295-1:2017 ISO 20121:2012 ISO 20400:2017 ISO 22000:2005 ISO 22000:2019 ISO 22001:2019 ISO 22301:2014 ISO 22301:2019 ISO 26000:2010 ISO 26000:2021 ISO 3834-2:2005 ISO 3834-2:2021 ISO 3834-3:2005 ISO 3834-3:2021 ISO 3834-4:2005 ISO 3834-4:2021 ISO 39001:2012 ISO 44001:2017 ISO 44001:2017/NA ISO 45001:2018 ISO 45001:2023 ISO 50001:2011 ISO 50001:2018 ISO 55000:2014 ISO 9001:2008 ISO 9001:2015 ISO/IEC 17025:2017 ISO/IEC 20000-1:2011 ISO/IEC 27001:2005 ISO/IEC 27001:2006 ISO/IEC 27001:2013 ISO/IEC 27001:2014 ISO/IEC 27001:2017 ISO/IEC 27001:2022 ISO/IEC 27001:2023 ISO/TS 16949:2002 ISO/TS 16949:2009 KBA Keymark KRAV MDSAP MSC NFS 2005:10 OHSAS 18001:2007 PEFC Povel PS 9000:2011 Responsible Care REVAQ RSB SCC 2008/5.1 Smart-Quality Smart-Environment Smart-SAM SS 137003:2021+T1:2024 Swedish Environmental Base TickIT TickITplus TL 9000 Tryggare Mat VDA 6.1 VDA 6.4:2005 WLA-SCS:2006
certNumber	varchar(20)	Important	The certificate number of the certification, as it appears on the certification diploma. The certification number is only used for presentation. It may differ from site to site when multiple sites share a common certification. In this case the certID must be identical for all sites.
dateIssued	datetime	REQUIRED	The date when the certificate was issued -- not the date of the most recent revision.
dateExpired	datetime	REQUIRED	The date when the certificate ceased being valid or will cease to be valid. If for some reason an expiration date is not available, this field should the date 1 January 1900 (hours and minutes are ignored). The date information is used principally for quality assurance, and also for statistical and other purposes. Individual expiry dates are never displayed on the www.certification.nu web site.
disabled	bit or int	Optional	Indicates whether this certification has been temporarily withdrawn.
accreditationBody	nvarchar(30)	Optional	Accreditation body for the certification. Only the following values for this field are accepted: ACCREDIA ANAB ASI CAI CMDCAS DANAK ENAC FAMI-QS FINAS IATF NA RvA SWEDAC TAF UKAS UNIFE If you have additional accreditation bodies that are missing from the list above, please contact us.
division	nvarchar(100)	Important	The part of a site that is certified by the certification, e.g. the marketing division of a company, or a specific workshop in a factory or a sub-unit of a company. Leaving this field blank means that the certification applies to the entire site.
divisionEn	nvarchar(100)	Important	Division, written in English.
siteId	varchar(20)	REQUIRED	A unique identifier for the site the certificate has been issued to. This is probably a primary key column in the certificate authority database. For Exports to work, this siteId must be unique for each certified site, and persistent (i.e. this siteId will always be the same for a particular site from one day to the next). A Swedish CFAR number, as defined by Statistics Sweden, is a good example of a site ID (refer to SCB.se for more information on CFAR codes).
siteName	nvarchar(150)	REQUIRED	The name of the site for which the certificate has been issued.
orgNumber	nvarchar(30)	Important	A unique ID for the organization, often issued by the government of the country in which the organization operates. In Sweden this value should be the "organisationsnummer" of the organization in question.
contactEmail	varchar(50)	Optional	The email address of the contact person.
visitingAddress	nvarchar(80)	Important	The street address of the site.
visitingCity	nvarchar(50)	REQUIRED	The city or municipality in which the site is located. For instance: Göteborg.
countryCode	varchar(5)	REQUIRED	Country in which the site is located, specified as a two-character ISO 3166 country code. For instance, the codes for Sweden, Norway, and Finland are SE, NO, and FI respectively.
postalCode	varchar(15)	Important	Postal code to the site.
languageCode	varchar(15)	Important	The language that some of the following text strings are written in. The string must be in the format specified in the internet RFC 4646. Currently supported languages are: da-DK - Dansk de-DE - Deutsch en-GB - English et-EE - Eesti fi-FI - Suomi fr-FR - Français nb-NO - Bokmål nn-NO - Nynorsk pl-PL - Polski sv-SE - Svenska zh-CN - ??
scope	nvarchar(4000)	Important	Scope, or description of the activity of the certified site. The scope is written in the national language defined by the field languageCode defined above.
scopeEn	varchar(4000)	Important	Scope written in English.
limitations	nvarchar(400)	Optional	Limitations or exclusions applied to the certification. The limitation is written in the national language defined by the field languageCode defined above.
limitationsEn	nvarchar(400)	Optional	Limitations or exclusions, written in English.
groupEntity	nvarchar(80)	Optional	Organization or grouping to which the certified organization belongs. The group entity may or may not itself be certified.

The site alias query is an optional query intended to support the notion of alternate unique identifiers for certified sites. An example of an alternate unique identifier system is HSA-ID, developed and used by Swedish County Councils (Landsting). Support for the site alias query is not required, but if the CA does support the query the following fields are expected to exist as columns in the returned data:

Name of field	SQL type of field	Criticality	Description
siteId	nvarchar(20)	REQUIRED	The CA-specific unique site identifier, as specified in the certificate query above. Records from the result of a site alias query that contain Site IDs not appearing in the result from the certificate query will be rejected with a diagnostic message.
aliasType	nvarchar(20)	REQUIRED	The type of alias or alternative site identifier. Currently, the only supported type of alias is HSA-ID.
aliasId	nvarchar(30)	REQUIRED	The alternative site identifier. For example, the record for a site with a valid HSA-ID would have an aliasId similar to "SE2321000016-10N6" (quotes not included). HSA-IDs are not unique to site IDs; i.e. multiple siteId's can share the same HSA ID aliasId.
extraData	nvarchar(256)	Optional	Reserved for future use. Do not pass any data in this field.

The certification extra data query is an optional query that allows the CA to provide supplementary information about certifications. For instance, an EN 1090-1:2009+A1:2011 certification may be issued with an extra classification value "EXC 2". In order to identify what the "EXC 2" is referring to, the CA also must provide a value name. The value names are specified and maintained by certification.nu, see "valueName" below for details.

Name of field	SQL type of field	Criticality	Description
certId	varchar(20)	REQUIRED	The CA-specific unique certification identifier, as specified in the certificate query above. Records from the results of hte certification extra data query that contain certification ID's not appearing in the result from the certificate query will be rejected with a diagnostic message.
certType	varchar(50)	REQUIRED	The normative certificate type of the certification.
siteId	nvarchar(20)	REQUIRED	The CA-specific unique site identifier, as specified in the certificate query above. Records from the result of a site alias query that contain Site IDs not appearing in the result from the certificate query will be rejected with a diagnostic message.
valueName	nvarchar(50)	REQUIRED	The kind of extra data that is being associated with this certification. It must be one of the following values: EN_1090_Basic_Materials EN_1090_CE_marking EN_1090_Certification_Level EN_1090_Dimensioning EN_1090_EXC_Class EN_1090_Welding Keymark_Air/Air Keymark_Direct_Expansion/Water Keymark_Flat_Plate_Solar_Thermal_Collectors Keymark_Outdoor_Air/Water Keymark_Solar_Thermal_Systems Keymark_Vacuum_Tube_Solar_Thermal_Collectors Keymark_Water/Water
value	nvarchar(255)	REQUIRED	The extra data that is being associated with this certification. If this data is language-specific, you need to also supply a cultureName (see below).
cultureName	nvarchar(20)	Optional	If the value above is not language specific, e.g. a currency code like "USD" or "SEK", then leave this field empty. If the is language specific, e.g. "Arbetsmiljö" or "Work environment", you must supply the language code. The string must be in the format specified in the internet RFC 4646. Currently supported languages are: da-DK - Dansk de-DE - Deutsch en-GB - English et-EE - Eesti fi-FI - Suomi fr-FR - Français nb-NO - Bokmål nn-NO - Nynorsk pl-PL - Polski sv-SE - Svenska zh-CN - ??