SELECT
vue_tiers.tie_code AS Code_Client,
vue_dirigeant.col_nom +' '+ vue_dirigeant.col_prenom AS Nom_Prenom_Dirigeant,
vue_tiers.mag_raisoc AS Raison_Social,
magasin.mag_denom_commer AS Denomination_Commercial,
vue_tiers.mag_adresse AS Adresse,
vue_tiers.mag_codepostal AS Code_Postal,
vue_tiers.mag_ville AS Ville,
vue_tiers.mag_telephone_1 AS Teplephone_1,
vue_tiers.mag_telephone_2 AS Telephone_2,
vue_tiers.mag_telephone_p AS Telephone_Portable,
vue_tiers.mag_fax AS Fax,
vue_tiers.mag_email AS Email,
CASE WHEN not EXISTS( SELECT 1 FROM contrat WHERE contrat.tie_des_id = vue_tiers.tie_id ) THEN
'JAMAIS'
WHEN EXISTS( SELECT 1 FROM contrat WHERE contrat.cont_statut = 2 AND contrat.tie_des_id = vue_tiers.tie_id ) THEN
'CONTRAT ACTIF'
ELSE
'CONTRATS INACTIF'
END AS statut,
( SELECT MAX( piece.pie_date ) FROM piece WHERE piece.tie_des_id = vue_tiers.tie_id AND piece.pie_type = 5 ) AS date_derniere_facture
FROM
vue_tiers,
vue_dirigeant,
magasin
WHERE
vue_tiers.tie_id = vue_dirigeant.tie_id AND
vue_tiers.tie_publisher = vue_dirigeant.tie_publisher AND
vue_tiers.tie_type_client = 1 AND
vue_tiers.tie_code <> '50' AND
magasin.mag_id = vue_tiers.mag_id AND
magasin.mag_publisher = vue_tiers.mag_publisher AND
omc_f_is_valide (magasin.mag_fin_validite, CURRENT timestamp) = 1
ORDER BY
CAST ( ( CASE WHEN isnumeric( Code_Client ) = 1 THEN Code_Client ELSE 0 END ) AS INTEGER);