CREATE OR REPLACE PROCEDURE ten_percent_sale(
 given_id    VARCHAR2,
 given_month DATE
)
AS
    CURSOR  service_cur IS
        SELECT  *
        FROM    n_accountservice
        WHERE   account_id = given_id
            AND service_id BETWEEN 101 AND 126;
    services_t service_cur%ROWTYPE;
    TYPE service_ntt IS TABLE OF services_t%TYPE;
    l_services service_ntt;

    subsidiy  NUMBER;
    first_clause NUMBER;
    second_clause NUMBER;
    factsaldo NUMBER; -- фактическое сальдо из n_balances
    calcsaldo NUMBER; -- сальдо из n_restructuring_service_list
    saldo     NUMBER;
    payment   NUMBER;
    isRestruct BOOLEAN := FALSE;
BEGIN
    -- FIRST
    SELECT COUNT(*)
        INTO first_clause
        FROM n_accountservice na
        WHERE na.account_id = given_id
            AND na.service_id BETWEEN 101 AND 126;
    IF first_clause > 0 THEN
        DBMS_OUTPUT.put_line('[ok]eсть 101..126 услуга, идём дальше');
    ELSE
        DBMS_OUTPUT.put_line('[BREAK]нет 101..126 услуг');
    END IF;

    -- SECOND если хоть одна 100я услуга по даному аккаунту имеет вид 2 - юзер имеет субсидию
    FOR service_ IN service_cur LOOP
        SELECT COUNT(*) INTO second_clause FROM n_payments p WHERE
        p.account_id = given_id AND p.service_id = service_.service_id AND vid = 2;
        IF second_clause > 0 THEN
            DBMS_OUTPUT.put_line('[BREAK]хотя бы одна 100я услуга по даному аккаунту имеет вид 2');
        ELSE
            DBMS_OUTPUT.put_line('[ok]ни одна 100я услуга по даному аккаунту не имеет вида 2');
        END IF;
    END LOOP;

    -- THIRD                       n_accountservice
    FOR service_ IN (SELECT * FROM n_balances        n WHERE n.account_id = given_id ) LOOP
        IF service_.service_id = 901 OR service_.service_id = 902 THEN
            isrestruct := TRUE;
            SELECT
                NVL(SUM(b.summ), 0) INTO factsaldo
                FROM
                n_balances b
                WHERE b.account_id = given_id
                AND   b.DATA = ADD_MONTHS(TRUNC(given_month,'mm'),-1);

--          SELECT
--              nvl(sum(rlist.summa), 0) INTO calcSaldo
--              FROM
--              n_balances b
--              JOIN n_restructuring_service rservice ON rservice.account_id = b.account_id
--              JOIN  n_restructuring_service_list rlist ON rlist.n_restructuring_service_id = b.service_id
--              WHERE b.account_id = given_id
--                  AND rlist.DATA = add_months(trunc(given_month,'mm'),-1);
            SELECT
                NVL(SUM(rservice.summa_pay), 0) INTO calcSaldo
                FROM
                n_restructuring_service rservice
                WHERE rservice.account_id = given_id
                    AND rservice.DATE_END >= ADD_MONTHS(TRUNC(given_month,'mm'),-1);

--              dbms_output.put_line('factsaldo '||factsaldo||', calcSaldo '||calcSaldo);
        END IF;
    END LOOP;
        IF isrestruct = FALSE THEN
            DBMS_OUTPUT.put_line('[ok]никакой реструкторизации');
        ELSE
            IF calcsaldo <= factsaldo THEN
                DBMS_OUTPUT.put_line('[ok]реструктуризация, n_balances <= factsaldo '||calcsaldo||' <= '||factsaldo);
            ELSE
                DBMS_OUTPUT.put_line('[BREAK]реструктуризация, calcsaldo > factsaldo '||calcsaldo||' > '||factsaldo);
            END IF;
        END IF;


    -- FOUR Проверяем оплату до 20 числа прошлого месяца
        SELECT NVL(SUM(p.summ),0) INTO saldo FROM n_payments p
        WHERE   p.account_id = given_id
            AND p.service_id  =100
            AND (p.vid = 1 OR p.vid = 9)
            AND TRUNC(p.DATA, 'mm') = TRUNC(given_month, 'mm')  -- текущий -1 месяц
            AND EXTRACT(DAY FROM  p.DATA) <= 20;                -- берём отплаты до 20го числа включительно

        SELECT NVL(SUM(nb.summ), 0) INTO payment FROM n_balances nb
        WHERE nb.account_id = given_id
            AND nb.service_id BETWEEN 101 AND 126
            AND TRUNC(nb.DATA,'mm') = ADD_MONTHS(TRUNC(given_month,'mm'), -1);
        IF (payment - saldo) <= 0 THEN
            DBMS_OUTPUT.put_line('[ok]разница платежа и сальдо <= 0! ('||payment||' - '||saldo||'='||(payment-saldo)||')');
        ELSE
            DBMS_OUTPUT.put_line('[BREAK]разница платежа и сальдо > 0! ('||payment||' - '||saldo||'='||(payment-saldo)||')');
        END IF;

END ten_percent_sale;