with pharmscript_facilities AS (
                select id from facility where facility_category_id in (select id from facility_category where name = 'pharmscript')
            )

            update transaction set eligible_as_of = now()
            from new_distinct_transactions
            where new_distinct_transactions.id = transaction.id AND
                  not exists (select * from transaction t
                              left outer join recommendation_rule r on t.recommendation_rule_id = r.id
                              where t.id != new_distinct_transactions.id AND
                                    t.facility_id = new_distinct_transactions.facility_id AND
                                    t.resident_name = new_distinct_transactions.resident_name AND
                                    t.eligible_as_of is not null AND
                                    t.inactive_as_of is null AND
                                    (t.disregard is not true OR t.show_disregard is not true OR t.deleted is not true) AND
                                    (t.dispense_date < new_distinct_transactions.dispense_date OR (t.dispense_date = new_distinct_transactions.dispense_date AND t.id < new_distinct_transactions.id)) AND

                                    NOT (
                                        exists (select * from recommendation_rule where show_x_days_before_depleted is true AND t.recommendation_rule_id = recommendation_rule.id) AND
                                        t.status not in ('Resident Refused Change', 'MD Declined Change')
                                    ) AND

                                    (
                                     t.drug_name = new_distinct_transactions.drug_name OR
                                     (
                                        new_distinct_transactions.no_previous_orders_of is not null AND
                                        new_distinct_transactions.no_previous_orders_of != '' AND
                                        t.drug_name ilike '%' || new_distinct_transactions.no_previous_orders_of || '%'
                                     )
                                    )
                                    AND
                                        (t.facility_id not in pharmscript_facilities)