/* Server version: WI-V6.3.4.18393 Firebird 2.1 
   SQLDialect: 3. ODS: 11.1. Forced writes: On. Sweep inteval: 20000.
   Page size: 8192. Cache pages: 9500 (76000 Kb). Read-only: False. */

SET SQL DIALECT 3;


/* Alter View (Drop, Create)... */
/* Drop altered view: SMETKA_HEAD_S */
DROP VIEW SMETKA_HEAD_S;

/* Create altered view: SMETKA_HEAD_S */
/* Create 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,
       coalesce(p.FISC_STATUS, 0),
       coalesce(p.REQUIRED_LEVEL, 0)
from   SMETKA_HEAD sh
       left outer join (select sp.ID_SMETKA_HEAD,
                               max(sp.FISC_STATUS) as fisc_status,
                               max(sl.REQUIRED_LEVEL) as REQUIRED_LEVEL
                        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.STATUS > 0
                        group by sp.ID_SMETKA_HEAD) p on p.ID_SMETKA_HEAD = sh.ID_SMETKA_HEAD
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)
;

