Untitled

SELECT
    label
    || '-'
    || ( label +:interval ) AS name,
    value
FROM
    (
        SELECT
            label,
            day,
            COUNT(*) value
        FROM
            (
                SELECT
                    trunc(to_number(TO_CHAR(parent_creation_date,'HH24') ) /:interval) *:interval AS label,
                    trunc(parent_creation_date) AS day
                FROM
                    (
                        SELECT
                            parent_rcv_trx_id,
                            parent_creation_date,
                            MIN(rcv_creation_date) rcv_creation_date,
                            parent_creation_date - MIN(rcv_creation_date) time_process,
                            MAX(lev)
                        FROM
                            (
                                SELECT
                                    CONNECT_BY_ROOT transaction_id parent_rcv_trx_id,
                                    CONNECT_BY_ROOT rt.creation_date parent_creation_date,
                                    rt.creation_date rcv_creation_date,
                                    rt.qa_collection_id,
                                    rt.transaction_id,
                                    level lev,
                                    rt.transaction_type,
                                    rt.transaction_date,
                                    msi.segment1 item,
                                    rt.quantity,
                                    rt.location_id
                                FROM
                                    rcv_transactions rt
                                    JOIN rcv_shipment_lines rsl ON rt.shipment_line_id = rsl.shipment_line_id
                                    LEFT JOIN mtl_system_items_b msi ON msi.inventory_item_id = rsl.item_id
                                                                           AND msi.organization_id = rsl.to_organization_id
                                    JOIN fnd_user fu ON fu.user_id = rt.created_by
                                START WITH rt.transaction_type = 'DELIVER'
                                           AND (
                                    (
                                        :organization IS NOT NULL
                                        AND rt.organization_id IN (
                                            SELECT
                                                regexp_substr(:organization,'[^;]+',1,level)
                                            FROM
                                                dual
                                            CONNECT BY
                                                regexp_substr(:organization,'[^;]+',1,level) IS NOT NULL
                                        )
                                    )
                                    OR (
                                        :organization IS NULL
                                        AND 1 = 1
                                    )
                                )
                                           AND rt.transaction_date BETWEEN TO_DATE(:date_from,'YYYY-MM-DD HH24:MI') 
                                           AND TO_DATE(:date_to,'YYYY-MM-DD HH24:MI') CONNECT BY PRIOR rt.parent_transaction_id= rt.transaction_id
                                    AND (
                                    (
                                        :localization IS NOT NULL
                                        AND rt.location_id IN (
                                            SELECT
                                                regexp_substr(:localization,'[^;]+',1,level)
                                            FROM
                                                dual
                                            CONNECT BY
                                                regexp_substr(:localization,'[^;]+',1,level) IS NOT NULL
                                        )
                                    )
                                    OR (
                                        :localization IS NULL
                                        AND 1 = 1
                                    )
                                )
                                ORDER BY
                                    level DESC
                            )
                        GROUP BY
                            parent_creation_date,
                            parent_rcv_trx_id
                        ORDER BY
                            parent_creation_date
                    )
            )
        GROUP BY
            day,
            label
        ORDER BY
            day,
            label
    )