DROP VIEW SMETKA_HEAD_S;

CREATE VIEW SMETKA_HEAD_S(
    ID_SMETKA_HEAD,
    ID_END_WORK,
    SMETKA_DATE,
    TABLE_NO,
    SMETKA_NUM,
    ID_OBJECT,
    ID_CONTRAGENT,
    ID_CONTRAGENT_OVERRULE,
    TOTAL_SUM,
    DISCOUNT_SUM,
    SURCHARGE_SUM,
    GRAND_TOTAL_SUM,
    CUSTOMER_CNT,
    USER_OWNER,
    START_DATE,
    START_TIME,
    STOP_DATETIME,
    ID_MANUAL_PRICE_LIST,
    ID_SURCHARGE,
    SURCHARGE_VALUE,
    ID_SMETKA_DISCOUNT,
    STATUS,
    END_WORK_USER,
    END_WORK_OBJECT,
    CARD_CODE,
    FISC_STATUS,
    REQUIRED_PAYMENT_SECURE_LEVEL)
AS
select sh.ID_SMETKA_HEAD,
       sh.ID_END_WORK,
       sh.SMETKA_DATE,
       sh.TABLE_NO,
       sh.SMETKA_NUM,
       sh.ID_OBJECT,
       sh.ID_CONTRAGENT,
       sh.ID_CONTRAGENT_OVERRULE,
       sh.TOTAL_SUM,
       sh.DISCOUNT_SUM,
       sh.SURCHARGE_SUM,
       sh.GRAND_TOTAL_SUM,
       sh.CUSTOMER_CNT,
       sh.USER_OWNER,
       sh.START_DATE,
       sh.START_TIME,
       sh.STOP_DATETIME,
       sh.ID_MANUAL_PRICE_LIST,
       sh.ID_SURCHARGE,
       sh.SURCHARGE_VALUE,
       sh.ID_SMETKA_DISCOUNT,
       sh.STATUS,
       sh.END_WORK_USER,
       sh.END_WORK_OBJECT,
       sh.CARD_CODE,
       (select coalesce(max(sp.FISC_STATUS), 0)
        from   SMETKA_PAYMENT SP
        where  sp.ID_SMETKA_HEAD = sh.ID_SMETKA_HEAD
               and sp.STATUS > 0) as FISC_STATUS,
       (select coalesce(max(sl.REQUIRED_LEVEL), 0)
        from   SMETKA_PAYMENT SP
               join NOM_PAYMENT_TYPE PT on pt.ID_PAYMENT_TYPE = sp.ID_PAYMENT_TYPE
               join RIGHT_SECURE_LEVEL sl on sl.ID_SECURE_LEVEL = pt.ID_SECURE_LEVEL
        where  sp.ID_SMETKA_HEAD = sh.ID_SMETKA_HEAD
               and sp.STATUS * sh.STATUS > 0) as REQUIRED_LEVEL
from   SMETKA_HEAD sh
where  not exists (select sp.ID_SMETKA_PAYMENT
                   from   SMETKA_PAYMENT SP
                          join NOM_PAYMENT_TYPE PT on pt.ID_PAYMENT_TYPE = sp.ID_PAYMENT_TYPE
                   where  sp.ID_SMETKA_HEAD = sh.ID_SMETKA_HEAD
                          and pt.FISCAL_ID = '99'
                          and (sp.STATUS * sh.STATUS) > 0)
;

CREATE INDEX SMETKA_STOCKOUT_IDX1 ON SMETKA_STOCKOUT (ID_STOCK);
CREATE INDEX SMETKA_STOCKOUT_IDX2 ON SMETKA_STOCKOUT (ID_OBJECT);

commit work;

