CREATE OR REPLACE TYPE record_cred AS OBJECT
    (n_dog NUMBER,
    name_client VARCHAR2(80),
    begin_date DATE,
    end_date DATE,
    rest_debt NUMBER,
    percents NUMBER);

CREATE OR REPLACE TYPE table_cred AS TABLE OF record_cred;

create FUNCTION credit_portpholio (end_date IN DATE) RETURN table_cred pipelined
    AS
    BEGIN
        for t_cred in (
        SELECT pr_cred.num_dog AS n_dog,
            client.cl_name AS name_client,
            pr_cred.date_begin AS begin_date,
            pr_cred.date_end AS end_date,
            fact_rest.rest_debt AS rest_debt,
            (plan_percent.p_perсent - fact_percent.f_perсent) AS percents
        FROM pr_cred
        JOIN client ON pr_cred.id_client = client.id
        JOIN (SELECT collection_id, 
                    SUM(CASE type_oper
                            WHEN 'Выдача кредита' THEN fact_oper.f_summa
                            WHEN 'Погашение кредита' THEN -fact_oper.f_summa
                        END) AS rest_debt
            FROM fact_oper
            WHERE f_date <= end_date
            GROUP BY collection_id) fact_rest
        ON pr_cred.collect_fact = fact_rest.collection_id
        JOIN (SELECT collection_id,
                SUM(CASE type_oper
                        WHEN 'Погашение процентов' THEN plan_oper.p_summa
                    END) AS p_perсent
             FROM plan_oper
             WHERE p_date <= end_date
            GROUP BY collection_id) plan_percent
        ON pr_cred.collect_plan = plan_percent.collection_id
        JOIN (SELECT collection_id,
                SUM(CASE type_oper
                        WHEN 'Погашение процентов' THEN fact_oper.f_summa
                    END) AS f_perсent
              FROM fact_oper
              WHERE f_date <= end_date
              GROUP BY collection_id) fact_percent
        ON pr_cred.collect_fact = fact_percent.collection_id)
        LOOP
            pipe row (record_cred(t_cred.n_dog, t_cred.name_client, t_cred.begin_date,
                                    t_cred.end_date, t_cred.rest_debt, t_cred.percents));
        END LOOP;
End credit_portpholio;


SELECT * FROM TABLE(CREDIT_PORTPHOLIO('18.01.2020'));