CREATE OR REPLACE VIEW a.for_application_spr_vew (
id,
num_reg,
date_reg,
why_pay,
suma,
nds,
id_user_dep,
id_user_cfo,
id_user_ucfo,
login_us,
ostatok,
id_department,
id_r_s,
id_article,
id_division,
id_ucfo_art,
id_cfo,
id_parent_art,
id_user_ucfo_art,
id_smeta,
komm,
name_kontr,
kontr_id,
kontr,
r_s_id,
fio,
num_v_tgk,
reestr,
kvota,
dogovor,
sptk,
ostatt,
oplata )
AS
select application.id, application.num_reg, application.date_reg, application.why_pay, application.suma, application.nds,
department.id_user,cfo.id_user, ucfo.id_user, lower(application.avtor),application.ostatok,application.id_department,
application.id_r_s, application.id_article, application.id_division, article.id_ucfo,article.id_cfo, article.id_parent,
article.id_user_ucfo, application.id_smeta, application.komm, kontragent.name_kontr, kontragent.id,
kontragent.kontr, r_s.id, users.fio,application.num_v_tgk, application.reestr,application.kvota,
application.num_dog||' '||application.date_dog,application.sptk,
application.suma-(select nvl(sum(payment.sum-nvl(payment.sum_ret,0)),0) from payment where (id_orders=application.id)and(payment.date_vip is not null)/*and(payment.date_ret is null)*/),
(select nvl(sum(payment.sum-nvl(payment.sum_ret,0)),0) from payment where (id_orders=application.id)and(payment.date_vip is not null)/*and(payment.date_ret is null)*/)
from application, department, article, cfo, ucfo, kontragent, users,r_s
where (application.id_department=department.id)and(application.id_article=article.id)
and (article.id_cfo=cfo.id)and(cfo.id_ucfo=ucfo.id)and(r_s.id=application.id_r_s)
and(kontragent.id=r_s.id_kontr)and(users.login=application.avtor)
/
-- Grants for View
GRANT DELETE ON a.for_application_spr_vew TO paymaster
/
GRANT INSERT ON a.for_application_spr_vew TO paymaster
/
GRANT SELECT ON a.for_application_spr_vew TO paymaster
/
GRANT UPDATE ON a.for_application_spr_vew TO paymaster
/
GRANT DELETE ON a.for_application_spr_vew TO taxwork
/
GRANT DELETE ON a.for_application_spr_vew TO users
/
GRANT SELECT ON a.for_application_spr_vew TO users
/
GRANT SELECT ON a.for_application_spr_vew TO b
WITH GRANT OPTION
/