 )
 WITH s AS
 (
+-- pick Primary LOINC Parts
 SELECT DISTINCT pl.PartNumber, p.PartDisplayName, pl.parttypename, p.status
 FROM sources.loinc_partlink pl -- contains links between LOINC Measurements/Observations AND LOINC Parts
 JOIN sources.loinc_part p -- contains LOINC Parts and defines their validity ('status' field)

  JOIN concept_relationship_stage r1 ON (concept_code_1,vocabulary_id_1) = (c.concept_code,c.vocabulary_id) -- LOINC Measurement
 AND c.vocabulary_id = 'LOINC' and c.domain_id = 'Measurement' and c.invalid_reason is null and c.standard_concept = 'S'
 AND concept_code_2 IN ('LP7057-5','LP21304-8','LP7068-2','LP185760-8','LP7536-8','LP7576-4','LP7578-0','LP7579-8','LP7567-3','LP7681-2','LP7156-5','LP7479-1','LP7604-4', 'LP7753-9', 'LP7067-4', 'LP7073-2', 'LP7751-3') -- list of needful LOINC Parts (System and Scale)
 AND r1.relationship_id in ( 'Has system', 'Has scale type')
),
-- AXIS 2: get links given by the source between LOINC Measurements and SNOMED Attributes
lc_sn AS

  AND pl.PartTypeName IN ('SYSTEM', 'METHOD', 'PROPERTY', 'TIME', 'COMPONENT', 'SCALE') -- list of Primary LOINC Parts

    UNION ALL

-- pick LOINC Hierarchy concepts (Attributive Panels, non-primary Parts and 427 Undefined attributes)
SELECT DISTINCT code,
       COALESCE(p.partdisplayname,code_text) AS PartDisplayName,
