Babacus

Navigation

Babacus Export Tool
Writing SQL queries
SQL field specification
Babacus Web Service

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
  • AFS 2001:1
  • AFS 2017:3
  • ANSI/ESD S20.20-2007
  • AQAP 2110
  • AQAP 2120
  • AS/EN/JISQ 9100
  • AS9120A
  • ASC
  • 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/OHSAS 18001:2008
  • ECM
  • EMAS
  • EN 1090-1:2009+A1:2011
  • EN 15085-2:2007
  • EN 15085-4:2007
  • EN 15224:2012
  • EN 15224:2017
  • EN 9120:2009
  • EN 9120:2010
  • 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 20121:2012
  • ISO 20400:2017
  • ISO 22000:2005
  • ISO 22301:2014
  • ISO 26000:2010
  • ISO 3834-2:2005
  • ISO 3834-3:2005
  • ISO 3834-4:2005
  • ISO 39001:2012
  • ISO 45001:2018
  • ISO 50001:2011
  • 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/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
  • 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 - ??

© Copyright Babacus AB 2004-2019