WITH filtr_calls AS ( --минимальный запрос со всеми фильтрами
SELECT *
FROM calls cls
JOIN contacts ct
        ON ct.id_contact = cls.fid_contact
JOIN registries reg
        ON reg.id_registry = ct.fid_registry
JOIN d_script_scenarios dss
        ON ct.fid_scenario = dss.id_scenario


WHERE cls.start_time >= TO_TIMESTAMP( :i_init_time)
    AND cls.start_time < TO_TIMESTAMP( :i_finish_time)
--    AND (reg.id_registry = i_id_registry OR i_id_registry IS NULL)
    AND reg.id_registry = NVL( TO_NUMBER( :i_id_registry), reg.id_registry)
    AND reg.IS_HIDDEN = 0
    AND (:i_id_scenario IS NULL AND dss.scenario_code IN ('multioffer', 'kk', 'auto', 'mail', 'approved')
        OR :i_id_scenario = dss.scenario_code)
)
, all_leg_63 AS (
    SELECT
        cli.session_id AS session_id_0
        , gcl.*
    FROM filtr_calls cli
    LEFT JOIN
         TABLE(nc_core.pkg_api.GET_CALL_LINKS(I_DATE_START => '21.10.2019 00:00:00', --Дата начала отбора
                          I_DATE_END => '22.10.2019 00:00:00', --Дата окончания отбора
                          I_PROJECTS => 'corebofs000080000lqp8ou9gjub5jvo, corebofs000080000m4fhpvb8ufva1ig, corebofs000080000m4fj8fk660t8sp0, corebofs000080000m5brscdaq1ucsr8, corebofs000080000m74g3a4e2vmsr9c',--Список необходимых проектов
                          I_DIRECTION => NULL,--Список направлений
                          I_PLATFORM => NULL,--Список платформ
                          I_ABONENT => NULL--Список необходимых абонентов/операторов. Возможно использование символов % и _ для нечеткого поиска
--                          I_SELECT_ABONENTS NUMBER DEFAULT 1,--Флаг фильтрации абонентов/опереторов переданных в I_ABONENT. 0 - исключать; 1 - выбрать только переданных в I_ABONENT
--                          I_QUALITY_CONTROL NUMBER DEFAULT NULL
                          ) --Флаг необходимости выгружать IVR оценки качества: 1 - только IVR оценки качества; 0 - исключить IVR оценки качества; NULL - не использовать фильтр
        ) gcl
    ON gcl.session_id = cli.session_id
)
--,nau_info_63 AS
--(
    SELECT
        leg.session_id_0
--        , naucrm62.intervaltosec(nvl(al.abonent_connected
--                                              , al.abonent_ended) - dl.dialer_created)       AS answ_wait_time
        , naucrm62.intervaltosec(NVL(leg.ivr_connected
                                              , leg.ivr_ended) - leg.ivr_created)       AS answ_wait_time
        , CASE
                WHEN leg.operator_created IS NOT NULL
                    THEN 'Распределён на оператора'
                WHEN leg.ivr_created IS NOT NULL
                    THEN 'Потерян в очереди'
                ELSE 'Не распределялся в очередь'
        END                                                                   AS to_oper_transfered
        , CASE
                WHEN leg.operator_created IS NOT NULL
                    THEN CASE
                            WHEN leg.operator_connected IS NOT NULL
                                THEN 'Звонок принят'
                            ELSE 'Звонок потерян'
                         END
                    ELSE ''
        END                                                                   AS to_oper_connected
        , naucrm62.intervaltosec(NVL(leg.operator_created,leg.ivr_ended) - leg.ivr_connected)        AS queue_time
        , naucrm62.intervaltosec(NVL(leg.operator_connected
                                              , leg.operator_ended) - leg.operator_created)          AS ringing_time

        , naucrm62.intervaltosec(leg.operator_ended - leg.operator_connected)             AS talk_time
        , naucrm62.intervaltosec(leg.operator_ended - leg.operator_connected) -
              NVL(leg.operator_hold, 0)                                              AS talk_time_no_hold
        , NVL(leg.operator_hold, 0)                                                  AS hold_time
        , NVL(leg.operator_wrapup, 0)                                                AS acw_time
--        , COALESCE(ol.oper_voip_reason,il.ivr_voip_reason,al.abonent_voip_reason,dl.dialer_voip_reason) AS voip_reason
--        , COALESCE(ol.oper_internal_reason,il.ivr_internal_reason,al.abonent_internal_reason,dl.dialer_internal_reason) AS internal_reason
--        , COALESCE(ol.oper_text_reason,il.ivr_text_reason,al.abonent_text_reason,dl.dialer_text_reason) AS text_reason
    FROM all_leg_63 leg
--        JOIN dialer_leg_63 dl
--            ON leg.session_id_0 = dl.session_id
--        JOIN abonent_leg_63 al
--            ON leg.session_id_0 = al.session_id

--)