CREATE PROCEDURE credit_portpholio (
    end_date DATE)
AS
    type temp_table_type IS TABLE OF reply%ROWTYPE;
BEGIN
    SELECT pr_cred.num_dog AS "Номер договора",
        client.cl_name AS "ФИО клиента",
        pr_cred.date_begin AS "Дата начала договора",
        pr_cred.date_end AS "Дата окончания договора",
        fact_rest.rest_debt AS "Остаток ссудной задолженности",
        (plan_percent.p_perсent - fact_percent.f_perсent) AS "Сумма предстоящих процентов к погашению"
    INTO temp_table_type
    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;
END;