--requete renseigner contrat valide pour les contrat produit importés
UPDATE contratproduit_ c1
SET cr_contrat = (
    SELECT cr_contrat 
    FROM contratproduit_ c2
    WHERE 
        c2.cr_contrat IN (
            SELECT ct_id 
            FROM contrat_ 
            WHERE 
                ct_typecontrat = (
                    SELECT refid 
                    FROM refvalues 
                    WHERE refval = 'L' 
                    AND reftabid = (
                        SELECT tabid 
                        FROM "tables" 
                        WHERE tabradical = 'Cp_'
                    )
                )
                AND ct_statut = (
                    SELECT refid 
                    FROM refvalues 
                    WHERE refval = 'VAL' 
                    AND reftabid = (
                        SELECT tabid 
                        FROM "tables" 
                        WHERE tabradical = 'Cs_'
                    )
                )
        )
        AND c2.cr_tiers = c1.cr_tiers
    LIMIT 1
)
where date(cr_crdt )= 'DATE_D_IMPORT';