--АО4
select ra.full_name                                                               as "Наименование абонента",
       ra.inn                                                                     as "ИНН абонента",
       ra.kpp                                                                     as "КПП абонента",
       rpt.product_type_name                                                      as "Продукт",
       case when rp.abonent_email notnull then rp.abonent_email else ra.email end as "e-mail"
from ro_product rp
         join ro_abonent ra on rp.abonent_id = ra.abonent_id
         join ro_contract rc on rp.contract_id = rc.contract_id
         join ro_contract_tariff rct on rc.contract_id = rct.contract_id
         join ro_product_type rpt on rp.product_type_id = rpt.product_type_id
where rp.product_type_id = 1
  and rp.step_id = 5
  and rct.step_id = 5
  and rct.initial_date < now()
  and (rct.end_date + rct.waiting_days) > now();