--and kr.idk_reception=1

and pc.id_categorie=1

and s.d_end is null

and EM.ID_E = SDE.ID_E(+) and

EM.ID_E = SRA.ID_E(+) and SRA.IDK_RANK = KSRA.IDK_RANK(+)

and ((em.id_e=%p1[i]%) or (%p1[i]% is null))

order by fio

with s as (select e.surname||' '||e.name||' '||e.patronymic fio

,st.id_student,st.id_e, st.dp_end , sg.id_speciality,

st.d_start d_st , sg.spec_name, sg.id_d_institute, sg.course kurs,sg.name,

t.id_work_themes,

(

select max(o.d_start-1)

from orders_students_ o, paragraphs_ p, orders_ os

where o.id_student=st.id_student and o.id_paragraph = p.id_paragraph

and os.id_order = p.id_order

and p.idk_order in (201, 203, 206, 207, 211)

and os.hisdate is not null and p.cancellation is null) d_end

from students_ st, employees_ e, student_groups_ sg, themes_works_ t

where e.id_e=st.id_e and st.id_ed_group = sg.id_ed_group

and t.id_student(+)=st.id_student

and ((sg.id_d_institute=%p1[i]%) or (%p1[i]% is null))

and ((sg.id_speciality =%p2[i]%) or (%p2[i]% is null))

and extract(year from st.dp_end) =extract(year from sysdate)

)

select distinct

s.id_student,

s.fio,

s.name gruppa,

s.id_speciality,

s.spec_name,

s.dp_end,

s.id_work_themes

from s

where s.d_end is null

order by s.id_student

select /*+rule*/ distinct

a.abr,

s.name,s.qualifier,s.id_speciality,t.d

--,stg.id_d

--,d.abbreviation

--, stg.id_d_institute

from

speciality_ s

, student_groups_ stg

,division_ d

,(select min(stg1.id_d) d,s1.id_speciality s from speciality_ s1, student_groups_ stg1

where stg1.id_speciality = s1.id_speciality and

((stg1.id_d_institute =%p1[i]%) or (%p1[i]% is null))

group by s1.id_speciality) t

,(

select stg1.id_speciality s, max(stg1.abbreviation) abr

from student_groups_ stg1

group by stg1.id_speciality

) a

where ((stg.id_d_institute = %p1[i]%) or (%p1[i]% is null))

and stg.id_speciality = s.id_speciality

and d.id_d=stg.id_d

and t.s=s.id_speciality

and a.s=s.id_speciality

and s.id_speciality <> 41857 --класс

order by a.abr

with t as(

SELECT

em.id_e

,EM.SURNAME||' '||EM.NAME||' '||EM.PATRONYMIC fio

--,p.ID_POST

, p.NAME||', '||d.name dolznost

FROM

STAFF_ s, UNITS_STAFF_ us, LIST_STAFF_ ls, DIVISION_ d, POSTS_CATEGORIES_ pc, POST_ p,

KIND_RECEPTION_ kr,EMPLOYEES_ EM,

SCIENTIFIC_DEGREE_ SDE,

SCIENTIFIC_RANK_ SRA,

KIND_SCIENTIFIC_RANK_ KSRA

WHERE

s.id_e=em.id_e

AND

s.ID_UNITS=us.ID_UNITS AND us.id_lstaff=ls.id_lstaff AND ls.id_d=d.id_d AND

us.idg_post=pc.idg_post AND pc.id_post=p.id_post AND s.idk_reception=kr.idk_reception

--and kr.idk_reception=1

and pc.id_categorie=1

and s.d_end is null

and EM.ID_E = SDE.ID_E(+) and

EM.ID_E = SRA.ID_E(+) and SRA.IDK_RANK = KSRA.IDK_RANK(+)

--and ((em.id_e=3141) or (3141 is null))

)

select distinct co.id_commission

,cm.id_paragraph_start

,co.idk_commission

,case when (k.idk_commission=1)

then k.abbreviation

when (k.idk_commission=2 )

then k.abbreviation||'-'||co.commission_number

end

--,co.commission_number

,co.id_speciality

,cm.id_commission_member

,cm.id_e

,e.surname||' '||e.name||' '||e.patronymic fio

,cm.id_post

,cm.d_start

,p.name

,s.name

,co.commission_number

,co.id_d

,t.dolznost

from commission_members_ cm

,commission_ co

,kind_commission_ k

,employees_ e

,post p

,speciality_ s

,specialities_division_ sd

,t

where co.id_commission=cm.id_commission(+)

and k.idk_commission=co.idk_commission

and e.id_e(+)=cm.id_e

--and co.id_d_specialitie=334835

--and ((co.id_paragraph=%p1[i]%) or (%p1[i]% is null))

and ((co.id_speciality =%p1[i]%) or (%p1[i]% is null))

and ((sd.id_d_institute=%p2[i]%) or (%p2[i]% is null))

and ((co.id_commission =%p3[i]%) or (%p3[i]% is null))

with t as(

SELECT

em.id_e

,EM.SURNAME||' '||EM.NAME||' '||EM.PATRONYMIC fio

--,p.ID_POST

, p.NAME||', '||d.name dolznost

FROM

STAFF_ s, UNITS_STAFF_ us, LIST_STAFF_ ls, DIVISION_ d, POSTS_CATEGORIES_ pc, POST_ p,

KIND_RECEPTION_ kr,EMPLOYEES_ EM,

SCIENTIFIC_DEGREE_ SDE,

SCIENTIFIC_RANK_ SRA,

KIND_SCIENTIFIC_RANK_ KSRA

WHERE

s.id_e=em.id_e

AND

s.ID_UNITS=us.ID_UNITS AND us.id_lstaff=ls.id_lstaff AND ls.id_d=d.id_d AND

us.idg_post=pc.idg_post AND pc.id_post=p.id_post AND s.idk_reception=kr.idk_reception

--and kr.idk_reception=1

and pc.id_categorie=1

and s.d_end is null

and EM.ID_E = SDE.ID_E(+) and

EM.ID_E = SRA.ID_E(+) and SRA.IDK_RANK = KSRA.IDK_RANK(+)

--and ((em.id_e=3141) or (3141 is null))

)

select distinct

t.id_work_themes

,t.d_start

,t.id_student

,t.id_paragraph

,e.surname||' '||e.name||' '||e.patronymic fio_st

,sg.name groupp

, sg.spec_name

, sg.id_d_institute

--, sg.course kurs

,t.theme

,stm.id_e

--,sg.id_speciality

,t.fio

,t.dolznost

from themes_works_ t

,students_ st

, employees_ e

,student_groups_ sg

,students_manual_ stm

,t

where e.id_e=st.id_e

and t.id_student=st.id_student

and st.id_ed_group = sg.id_ed_group

--and stm.id_paragraph(+)=t.id_paragraph

and stm.id_student(+)=t.id_student

and ((sg.id_d_institute=%p1[i]%) or (%p1[i]% is null))

and t.id_e(+)=stm.id_e

and ((sg.id_speciality=%p2[i]%) or (%p2[i]% is null))

order by groupp,fio_st

select stm.id_student

,stm.id_e

,stm.id_paragraph

,stm.idk_lesson

,stm.d_start

,stm.id_discipline

from students_manual_ stm

where stm.idk_lesson=12

with s as (select e.surname||' '||e.name||' '||e.patronymic fio

,st.id_student,st.id_e, st.dp_end , sg.id_speciality,

st.d_start d_st , sg.spec_name, sg.id_d_institute, sg.course kurs,sg.name,

(

select max(o.d_start-1)

from orders_students_ o, paragraphs_ p, orders_ os

where o.id_student=st.id_student and o.id_paragraph = p.id_paragraph

and os.id_order = p.id_order

and p.idk_order in (201, 203, 206, 207, 211) --

and os.hisdate is not null and p.cancellation is null) d_end

from students_ st, employees_ e, student_groups_ sg

where e.id_e=st.id_e and st.id_ed_group = sg.id_ed_group

and ((sg.id_d_institute=%p1[i]%) or (%p1[i]% is null))

and ((sg.id_speciality =%p2[i]%) or (%p2[i]% is null))

and extract(year from st.dp_end) =2008

)

,te

as(

SELECT

em.id_e

,EM.SURNAME||' '||EM.NAME||' '||EM.PATRONYMIC fio

--,p.ID_POST

, p.NAME||', '||d.name dolznost

FROM

STAFF_ s, UNITS_STAFF_ us, LIST_STAFF_ ls, DIVISION_ d, POSTS_CATEGORIES_ pc, POST_ p,

KIND_RECEPTION_ kr,EMPLOYEES_ EM,

SCIENTIFIC_DEGREE_ SDE,

SCIENTIFIC_RANK_ SRA,

KIND_SCIENTIFIC_RANK_ KSRA

WHERE

s.id_e=em.id_e

AND

s.ID_UNITS=us.ID_UNITS AND us.id_lstaff=ls.id_lstaff AND ls.id_d=d.id_d AND

us.idg_post=pc.idg_post AND pc.id_post=p.id_post AND s.idk_reception=kr.idk_reception

--and kr.idk_reception=1

and pc.id_categorie=1

and s.d_end is null

and EM.ID_E = SDE.ID_E(+) and

EM.ID_E = SRA.ID_E(+) and SRA.IDK_RANK = KSRA.IDK_RANK(+)

--and ((em.id_e=3141) or (3141 is null))

)

select

sr.id_session_report

,sr.id_commission

,sr.id_educational_session

,sr.id_paragraph

,sr.d_start

,sr.id_work_themes

,s.fio --фио студента

,s.id_student

,s.id_speciality

,s.spec_name

,s.name --группа

--,s.id_d_institute

,k.abbreviation||'-'||c.commission_number --имя комиссии

,t.theme

,stm.id_e

,te.fio

,te.dolznost

,es.estimation

from session_report_ sr

,s

,educational_session_ es

,commission_ c

,kind_commission_ k

,themes_works_ t

,students_manual_ stm

,te

where s.id_student=es.id_student

and sr.id_educational_session=es.id_educational_session

and c.id_commission=sr.id_commission

and k.idk_commission=c.idk_commission

and t.id_work_themes=sr.id_work_themes

and stm.id_student(+)=t.id_student

and te.id_e(+)=stm.id_e

select distinct

ps.Status_Text

,ps.name_Kind_paragraph

,ps.hisdate

--,ps.idk_order

, ps.id_paragraph

,ps.id_order

,ps.idk_order

,ps.id_series_paper

,ps.note

,d.abbreviation

from paragraphs_status_ ps, division_ d

where ps.idk_order in (%p3[i]%,%p4[i]%)

and ((ps.d_creation>=%p1[d]%) or (%p1[d]% is null))

and ((to_number(ps.note)=%p2[i]%) or (%p2[i]% is null))

and d.id_d= to_number(ps.note)

with t as(

SELECT

em.id_e

,EM.SURNAME||' '||EM.NAME||' '||EM.PATRONYMIC fio

--,p.ID_POST

, p.NAME||', '||d.name dolznost

FROM

STAFF_ s, UNITS_STAFF_ us, LIST_STAFF_ ls, DIVISION_ d, POSTS_CATEGORIES_ pc, POST_ p,

KIND_RECEPTION_ kr,EMPLOYEES_ EM,

SCIENTIFIC_DEGREE_ SDE,

SCIENTIFIC_RANK_ SRA,

KIND_SCIENTIFIC_RANK_ KSRA

WHERE

s.id_e=em.id_e

AND

s.ID_UNITS=us.ID_UNITS AND us.id_lstaff=ls.id_lstaff AND ls.id_d=d.id_d AND

us.idg_post=pc.idg_post AND pc.id_post=p.id_post AND s.idk_reception=kr.idk_reception

--and kr.idk_reception=1

and pc.id_categorie=1

and s.d_end is null

and EM.ID_E = SDE.ID_E(+) and

EM.ID_E = SRA.ID_E(+) and SRA.IDK_RANK = KSRA.IDK_RANK(+)

--and ((em.id_e=3141) or (3141 is null))

)

select distinct co.id_commission

,co.id_paragraph

,co.idk_commission

,case when (k.idk_commission=1)

then k.abbreviation

when (k.idk_commission=2 )

then k.abbreviation||'-'||co.commission_number

end

--,co.commission_number

,co.id_speciality

,cm.id_commission_member

,cm.id_e

,e.surname||' '||e.name||' '||e.patronymic fio

,cm.id_post

,cm.d_start

,p.name

,s.name

,co.commission_number

,co.id_d

,t.dolznost --должность по штатному расписанию

from commission_members_ cm

,commission_ co

,kind_commission_ k

,employees_ e

,post p

,speciality_ s

,t

-- ,specialities_division_ sd

where co.id_commission=cm.id_commission(+)

and k.idk_commission=co.idk_commission

and e.id_e(+)=cm.id_e

--and co.id_d_specialitie=334835

and ((p.id_post in (55000,55001)) or (p.id_post is null))

and ((co.id_paragraph=%p1[i]%) or (%p1[i]% is null))

and ((co.id_speciality=%p2[i]%) or (%p2[i]% is null))

--and co.id_paragraph=1148021

--and co.idk_commission=1

and p.id_post(+)=cm.id_post

--and s.id_speciality=sd.id_speciality

and s.id_speciality=co.id_speciality

and t.id_e(+)=cm.id_e

Select

/*+rule*/

d.id_d, s.idd_main, d.abbreviation, d.name, d.id_metaterm, mt.id_metaobject

From

-- Последние наименования подразделений по-новому

(Select d.id_d, d.id_metaterm, ad.abbreviation,

ts_.Get_DivisionName(d.id_d, to_date('12.05.2008'), 0) name

From division_ d, attributes_divisions_ ad

Where d.id_d = ad.id_d and ad.d_start <= to_date('12.05.2008') and (ad.d_end is null or ad.d_end >= to_date('12.05.2008'))) d,

metaterms_ mt,

-- Наименование заданной роли

(Select pr.name From programm_roles_ pr where pr.qualifier = '110') pr,

-- Все подчинения доступных ролей

(Select s.* From subordination_ s,

-- Доступные подразделения по заданной роли

(Select d.id_d

From p_roles_current_user_ a, division_ d

Where a.id_metaterm = d.id_metaterm and a.qualifier = '110'

UNION -- Доступно всё, если нет привилегий

Select d.id_d From division_ d

Where not exists

(Select d.id_d

From p_roles_current_user_ a, division_ d

Where a.id_metaterm = d.id_metaterm and a.qualifier = '110')

and exists (Select * from p_roles_current_user_ Where qualifier = '110')) a

-- Возьмём только последние подчинения

Where s.d_start <= to_date('12.05.2008')

and (s.d_end >= to_date('12.05.2008') or s.d_end is null)

and s.idd_subordinate = a.id_d) s

Where d.id_d = s.idd_subordinate and d.id_metaterm = mt.id_metaterm(+)

select c.id_speciality,max(c.commission_number)--,c.id_commission_main

from commission_ c

where ((c.id_speciality= %p1[i]%) or (%p1[i]% is null))

and c.idk_commission=2

group by c.id_speciality

select c.id_commission,

case when (k.idk_commission=1)

then k.abbreviation

when (k.idk_commission=2 )

then k.abbreviation||'-'||c.commission_number

end

from commission_ c, kind_commission_ k

where

((c.id_speciality=%p1[i]%) or (%p1[i]% is null))

and ((c.idk_commission=%p2[i]%) or (%p2[i]% is null))

and k.idk_commission=c.idk_commission

order by k.abbreviatio

select

c.id_commission

,c.id_paragraph

,c.idk_commission

,c.id_speciality

,c.d_start

,c.commission_number

,c.id_d

from commission_ c

where ((c.id_commission=%p1[i]%) or (%p1[i]% is null))

select p.id_post,p.name

from posts_categories_ pc,post_ p,categories_ c

where c.id_categorie=8

and c.id_categorie=pc.id_categorie

and pc.id_post=p.id_post

and p.id_post not in (55000,55001)

Размещено на Allbest.ru