SELECT
    DISTINCT
        if((`pa`.`TYPEMODELEVARIANTE` = '3'),substr(`pa`.`CodeArticle`,1,7),`pa`.`CodeArticle`) AS `Code Produit`,
        replace(replace(replace(replace(replace(`strip_tags`(`pacd`.`Valeur`,'br'),'&nbsp;',' '),'<BR>','<br>'),'<br />','<br>'),'<BR />','<br>'),'<br>','\n') AS `Descriptifs`,`sub`.`Normes` AS `Normes`,if((ifnull(`pacftf`.`Valeur`,'') <> ''),`pacftf`.`UpdatedDate`,NULL) AS `Date de mise à jour FT FR`,
        if((ifnull(`pacfte`.`Valeur`,'') <> ''),`pacfte`.`UpdatedDate`,NULL) AS `Date de mise à jour FT EN`,
        `pacap`.`Valeur` AS `Application`
FROM
    (
        (
            (
                (
                    (
                        (
                            (
                                `db_fs`.`cm_pre_art`  `pa`
                            LEFT OUTER JOIN
                                `db_fs`.`fs_codearticle_cataencours` `scc`
                            ON
                                (
                                    (
                                        (`scc`.`RefCat` = `pa`.`CodeArticle`)
                                    AND
                                        (`pa`.`CodeArticle` <> '')
                                    )
                                )
                            )
                        JOIN
                            `db_fs`.`cm_pre_art_carac` `pacs`
                        ON
                            (
                                (
                                    (`pa`.`IdPreArt` = `pacs`.`IdPreArt`)
                                AND
                                    (`pacs`.`IdPreArtDef` = 1354)
                                )
                            )
                        )
                    JOIN
                        `db_fs`.`cm_pre_art_carac` `pacd`
                    ON
                        (
                            (
                                (`pa`.`IdPreArt` = `pacd`.`IdPreArt`)
                            AND
                                (`pacd`.`IdPreArtDef` = 2687)
                            )
                        )
                    )
                JOIN
                    `db_fs`.`cm_pre_art_carac` `pacap`
                ON
                    (
                        (
                            (`pa`.`IdPreArt` = `pacap`.`IdPreArt`) and (`pacap`.`IdPreArtDef` = 2686)
                        )
                    )
                )
            JOIN
                `db_fs`.`cm_pre_art_carac` `pacftf`
            ON
                (
                    (
                        (`pa`.`IdPreArt` = `pacftf`.`IdPreArt`)
                    AND
                        (`pacftf`.`IdPreArtDef` = 2159)
                    )
                )
            )
        JOIN
            `db_fs`.`cm_pre_art_carac` `pacfte`
        ON
            (
                (
                    (`pa`.`IdPreArt` = `pacfte`.`IdPreArt`)
                AND
                    (`pacfte`.`IdPreArtDef` = 2160)
                )
            )
        )
    LEFT OUTER JOIN
        `db_fs`.`fs_extract_normes` `sub`
    ON
        (
            (`pa`.`IdPreArt` = `sub`.`IdPreArt`)
        )
    )
WHERE
    (
        (
            (`pa`.`TYPEMODELEVARIANTE` in ('0','2') )
        AND
        (
            (`scc`.`RefCat` is not null) or (ifnull(`pacs`.`Valeur`,'') like '99%')
        )
    )
    OR
    (
            (`pa`.`TYPEMODELEVARIANTE` = '3')
        AND
            (`scc`.`RefCat` is not null)
        AND
            ( not (`pa`.`IDPARENT` in (
                                        select
                                            distinct
                                            `pa`.`IdPreArt`
                                        from
                                            (
                                                (
                                                    (
                                                        (
                                                            (
                                                                `db_fs`.`cm_pre_art` `pa`
                                                            JOIN
                                                                `db_fs`.`cm_article` `a`
                                                            ON
                                                            (
                                                                (
                                                                    (`a`.`RefCat` = `pa`.`CodeArticle`)
                                                                AND
                                                                    (`pa`.`CodeArticle` <> '')
                                                                )
                                                            )
                                                        )
                                                    JOIN
                                                        `db_fs`.`cm_article_selection` `ase`
                                                    ON
                                                        (
                                                            (
                                                                (`a`.`IdArticle` = `ase`.`IdArticle`)
                                                            AND
                                                                (`ase`.`Selection` = 'O')
                                                            )
                                                        )
                                                    )
                                                JOIN
                                                    `db_fs`.`cm_edition` `e`
                                                ON
                                                    (
                                                        (`ase`.`IdEdition` = `e`.`IdEdition`)
                                                    )
                                                )
                                            JOIN
                                                `db_fs`.`cm_edition_carac` `ec`
                                            ON
                                                (
                                                    (
                                                        (`e`.`IdEdition` = `ec`.`IdEdition`)
                                                    AND
                                                        (`ec`.`Valeur` = 'CATA_GEN')
                                                    )
                                                )
                                            )
                                        JOIN
                                            `db_fs`.`cm_pre_art_def` `pade`
                                        ON
                                            (
                                                (
                                                    (`ec`.`IdPreArtDef` = `pade`.`IdPreArtDef`)
                                                AND
                                                    (`pade`.`CodeChamp` = 'EDITION_EN_COURS_MOVEX')
                                                )
                                            )
                                        )
                                    WHERE
                                        (`pa`.`TYPEMODELEVARIANTE` in ('0','2')
                                    )
                                )
                            )
                        )
                    )
                )
            order by `pa`.`CodeArticle`