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;
    tmp           NUMBER;
    first_clause  BOOLEAN := FALSE;
    second_clause BOOLEAN := FALSE;
    third_clause  BOOLEAN := FALSE;
    fourth_clause BOOLEAN := FALSE;
    isRestruct    BOOLEAN := FALSE;
    factsaldo     NUMBER; -- фактическое сальдо из n_balances
    calcsaldo     NUMBER; -- сальдо из n_restructuring_service_list
    saldo         NUMBER;
    payment       NUMBER;

BEGIN
    -- FIRST
    SELECT COUNT(*)
        INTO tmp
        FROM n_accountservice na
        WHERE na.account_id = given_id
            AND na.service_id BETWEEN 101 AND 126;
    IF tmp > 0 THEN
        DBMS_OUTPUT.put_line('[ok]eсть 101..126 услуга, идём дальше');
        first_clause := TRUE;
    ELSE
        DBMS_OUTPUT.put_line('[BREAK]нет 101..126 услуг');
    END IF;

    -- SECOND если хоть одна 100я услуга по даному аккаунту имеет вид 2 - юзер имеет субсидию

    SELECT COUNT(*)
    INTO tmp
    FROM n_payments p
    WHERE
        p.account_id = given_id  AND vid = 2;

    IF tmp > 0 THEN
        DBMS_OUTPUT.put_line('[BREAK]хотя бы одна 100я услуга по даному аккаунту имеет вид 2');
    ELSE
        DBMS_OUTPUT.put_line('[ok]ни одна 100я услуга по даному аккаунту не имеет вида 2');
        second_clause := TRUE;
    END IF;

    -- 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
            b.summ INTO factsaldo
            FROM
            n_balances b
            WHERE b.account_id = given_id
                AND   b.DATA = TRUNC(given_month,'mm')
                AND   service_id = 100;

            SELECT
            SUM(b.summ) INTO factsaldo
            FROM
            n_balances b
            WHERE b.account_id = given_id
                AND   b.DATA = TRUNC(given_month,'mm')
                AND   (service_id = 901 OR service_id = 902);

            SELECT
            RSlist.summa_pay INTO calcSaldo
            FROM
            n_restructuring_service_list RSlist
            JOIN n_restructuring_service rs ON rs.ID = rslist.n_restructuring_service_id
            WHERE rs.account_id = given_id
            AND   rslist.DATA = TRUNC(given_month,'mm')
            AND   RS.service_id_out = 100;

        END IF;
    END LOOP;

    IF isRestruct = FALSE THEN
        DBMS_OUTPUT.put_line('[ok]никакой реструкторизации');
        third_clause := TRUE;
    ELSE
        IF calcsaldo <= factsaldo THEN
            DBMS_OUTPUT.put_line('[ok]реструктуризация, n_balances <= factsaldo '||calcsaldo||' <= '||factsaldo);
            third_clause := TRUE;
        ELSE
            DBMS_OUTPUT.put_line('[BREAK]реструктуризация, calcsaldo > factsaldo '||calcsaldo||' > '||factsaldo);
            third_clause := FALSE;
        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);

        SELECT NVL(SUM(na.summ), 0) INTO tmp FROM n_accruals na
        WHERE   na.account_id = given_id
            AND TRUNC(na.DATA,'mm') = TRUNC(given_month,'mm')
            AND na.service_id = 100
            AND na.summ <= 0;

        IF ((payment+tmp) - saldo) <= 0 THEN
            DBMS_OUTPUT.put_line('[ok]разница платежа и сальдо <= 0! ('||payment||'+('||tmp||') - '||saldo||'='||((payment+tmp) - saldo)||')');
            fourth_clause := TRUE;
        ELSE
            DBMS_OUTPUT.put_line('[BREAK]разница платежа и сальдо > 0! ('||payment||'+('||tmp||') - '||saldo||'='||((payment+tmp) - saldo)||')');
        END IF;

    IF      first_clause  = TRUE
        AND second_clause = TRUE
        AND third_clause  = TRUE
        AND fourth_clause = TRUE
    THEN
        DBMS_OUTPUT.put_line('[SUCCESS]');
    ELSE
        DBMS_OUTPUT.put_line('[FAIL]');
    END IF;
    DBMS_OUTPUT.put_line(' '||boolean_to_char(first_clause)
        ||' '||boolean_to_char(second_clause)
        ||' '||boolean_to_char(third_clause)
        ||' '||boolean_to_char(fourth_clause)
    );
END ten_percent_sale;