Я этот запрос вставляю в XML :
<?xml version="1.0" encoding="Windows-1251" ?>
<ReportTemplSchema xmlns="http://tempuri.org/ReportTemplSchema.xsd">
<QueryTempl QT_ID="DATEQ" QT_NAME="Query1">
<QT_SQL>
select
TO_CHAR(sysdate,'dd.mm.yyyy') as tek,
TO_CHAR(sysdate,'dd month yyyy') as tekp,
TO_CHAR(sysdate,'dd') as tekd,
TO_CHAR(sysdate,'month') as tekm,
TO_CHAR(sysdate,'yyyy') as tekp,
:DBEG as dbeg,
:DEND as dend,
isadm.is_commonfunc.getparamvalue(sys_context('IS_CONTEXT','MPDSysNo'),'AG_PST_N') as pstn,
isadm.is_commonfunc.getparamvalue(sys_context('IS_CONTEXT','MPDSysNo'),'AG_PST_R') as pstr,
isadm.is_commonfunc.getparamvalue(sys_context('IS_CONTEXT','MPDSysNo'),'AG_PIB_R') as pibr
from sys.dual
</QT_SQL>
</QueryTempl>
<QueryTempl QT_ID="ICD2Q" QT_NAME="Query2">
<QT_SQL>
select
icd.icd_dno as dno,
to_char(icd.icd_dsgn,'dd.mm.yyyy') as dbeg,
case when mpd.mpd_ctg='2' then mpd.mpd_name1||' '||mpd.mpd_name2||' '||mpd.mpd_name3
else mpd.mpd_name1 end as nm,
to_number(cpv.cpv_val) as val
from
isadm.is_icd icd
join isadm.is_ibt ibt on icd.icd_ibt_ref=ibt.ibt_sysno
join isadm.is_soc soc on icd.icd_sysno=soc.soc_icd_ref and soc.soc_tp='1'
join isadm.is_mpd mpd on mpd.mpd_sysno=soc.soc_mpd_ref
join (select a.iah_mpd_ref as iah from isadm.is_iah a
where a.iah_dout is null and a.iah_icode = 'I_000002'
and isadm.is_commonfunc.getfullrout(a.iah_route,a.iah_code)||'~' like
(select isadm.is_commonfunc.getfullrout(b.iah_route,b.iah_code) from isadm.is_iah b
where b.iah_dout is null
and b.iah_icode = 'I_000002'
and b.iah_mpd_ref = :MPD)||'~%') aa
on icd.icd_ampd_ref=aa.iah
join isadm.is_cpv cpv on cpv.cpv_icd_ref=icd.icd_sysno and cpv.cpv_pcode='2' and cpv.cpv_etp='TDOG' and cpv.cpv_dout is null
where
ibt.ibt_dout is null
and icd.icd_dout is null
and ibt.ibt_id='168'
and icd.icd_stat in ('30')
and mpd.mpd_dout is null
and soc.soc_dout is null
and trunc(icd.icd_dsgn) between
:DBEG and :DEND
order by icd.icd_dsgn
</QT_SQL>
</QueryTempl>
<QueryTempl QT_ID="SUMQ" QT_NAME="Query3">
<QT_SQL>
select
sum(cpv.cpv_val) as val
from
isadm.is_icd icd
join isadm.is_ibt ibt on icd.icd_ibt_ref=ibt.ibt_sysno
join isadm.is_soc soc on icd.icd_sysno=soc.soc_icd_ref and soc.soc_tp='1'
join isadm.is_mpd mpd on mpd.mpd_sysno=soc.soc_mpd_ref
join (select a.iah_mpd_ref as iah from isadm.is_iah a
where a.iah_dout is null and a.iah_icode = 'I_000002'
and isadm.is_commonfunc.getfullrout(a.iah_route,a.iah_code)||'~' like
(select isadm.is_commonfunc.getfullrout(b.iah_route,b.iah_code) from isadm.is_iah b
where b.iah_dout is null
and b.iah_icode = 'I_000002'
and b.iah_mpd_ref =:MPD)||'~%') aa
on icd.icd_ampd_ref=aa.iah
join isadm.is_cpv cpv on cpv.cpv_icd_ref=icd.icd_sysno and cpv.cpv_pcode='2' and cpv.cpv_etp='TDOG' and cpv.cpv_dout is null
where
ibt.ibt_dout is null
and icd.icd_dout is null
and ibt.ibt_id='168'
and icd.icd_stat in ('30')
and mpd.mpd_dout is null
and soc.soc_dout is null
</QT_SQL>
</QueryTempl>
<QueryTempl QT_ID="MPDQ" QT_NAME="Query4">
<QT_SQL>
<!-- Добавили код агента iahcode по которому определяем являеться ли он
главной дирекцией или филиалом-->
select mpd.mpd_name1 as name,
iah.iah_code as iahcode
from isadm.is_mpd mpd
join isadm.is_iah iah on mpd.mpd_sysno=iah.iah_mpd_ref
where mpd.mpd_sysno=:MPD
and iah.iah_dout is null
and mpd.mpd_dout is null
</QT_SQL>
</QueryTempl>
<QueryTempl QT_ID="ICDQ" QT_NAME="Query5">
<QT_SQL>
select
icd.icd_dno as dno,
icd.icd_srlce as ce,
icd.icd_srlno as nbr
from
isadm.is_icd icd
join isadm.is_ibt ibt on icd.icd_ibt_ref=ibt.ibt_sysno
join (select a.iah_mpd_ref as iah from isadm.is_iah a
where a.iah_dout is null and a.iah_icode = 'I_000002'
and isadm.is_commonfunc.getfullrout(a.iah_route,a.iah_code)||'~' like
(select isadm.is_commonfunc.getfullrout(b.iah_route,b.iah_code) from isadm.is_iah b
where b.iah_dout is null
and b.iah_icode = 'I_000002'
and b.iah_mpd_ref = :MPD)||'~%') aa
on icd.icd_ampd_ref=aa.iah
join isadm.is_cpv cpv on cpv.cpv_icd_ref=icd.icd_sysno and cpv.cpv_pcode='2' and cpv.cpv_etp='TDOG' and cpv.cpv_dout is null
where
ibt.ibt_dout is null
and icd.icd_dout is null
and ibt.ibt_id='168'
and icd.icd_stat in ('30')
and trunc(icd.icd_dsgn) between
:DBEG and :DEND
order by icd.icd_dbeg
</QT_SQL>
</QueryTempl>
<!-- Запрос возращает диапазоны испорченных бланков + их количество,
серия бланков
ce - серия бланков
minno - минимальное число в диапазоне
maxno - максимальное число в диапазоне
cnt - количество бланков в диапазоне
-->
<QueryTempl QT_ID="BRQ" QT_NAME="Query6">
<QT_SQL>
select
max(bbbb.srl_ce) as ce,
min(bbbb.srl_srno) as minno,
max(bbbb.srl_srno) as maxno,
count(*) as cnt
from
(
select aaaa.*,
isadm.is_ReportData.GetSrlGrpCommon(aaaa.srl_ce,aaaa.srl_srno) as GrpNo
from
(
select aaa.srl_ce,aaa.srl_srno
from isadm.is_srl aaa, isadm.is_srh bbb
where aaa.srl_dout is null and bbb.srh_dout is null
and aaa.srl_sysno = bbb.srh_srl_ref
and bbb.srh_stat = 'BR'
and aaa.srl_stat = 'BR'
and bbb.srh_din between to_date(:DBEG,'dd.mm.yyyy') and to_date(:DEND,'dd.mm.yyyy')+1
and aaa.srl_mpd_ref in
(
select aa.iah_mpd_ref
from isadm.is_iah aa
where aa.iah_dout is null
and isadm.is_commonfunc.GetFullRout(aa.iah_route,aa.iah_code)||'~' like
(
select min(isadm.is_commonfunc.GetFullRout(a.iah_route,a.iah_code)||'~%')
from isadm.is_iah a
where a.iah_dout is null
and a.iah_icode = 'I_000002'
and a.iah_mpd_ref = :MPD
)
and aaa.srl_ce in (select
bces.bces_ce
from
isadm.is_bces bces
join isadm.is_ibt ibt
on bces.bces_ibt_ref=ibt.ibt_sysno
where bces.bces_dout is null
and ibt.ibt_dout is null
and ibt.ibt_id='168')
)
order by aaa.srl_ce,TO_NUMBER(aaa.srl_srno)
) aaaa
) bbbb
group by GrpNo
</QT_SQL>
</QueryTempl>
<!-- Запрос возращает диапазоны возращенных бланков + их количество,
серия бланков
CE - серия бланков
MinNo - минимальное число в диапазоне
MaxNo - максимальное число в диапазоне
NoCount - количество бланков в диапазоне
-->
<QueryTempl QT_ID="RETQ" QT_NAME="Query7">
<QT_SQL>
select
max(bbbb.srl_ce) as CE,
min(bbbb.srl_srno) as MinNo,
max(bbbb.srl_srno) as MaxNo,
count(*) as NoCount
from
(
select aaaa.*,
isadm.is_ReportData.GetSrlGrpCommon(aaaa.srl_ce,aaaa.srl_srno) as GrpNo
from
(
select aaa.srl_ce,aaa.srl_srno
from isadm.is_srl aaa, isadm.is_srm bbb
where aaa.srl_dout is null
and aaa.srl_sysno = bbb.srm_srl_ref
and aaa.srl_stat = 'NU'
and bbb.srm_din between to_date(:DBEG,'dd.mm.yyyy') and to_date(:DEND,'dd.mm.yyyy')+1
and bbb.srm_mpd_toref in
(
select mpd.mpd_sysno from isadm.is_mpd mpd where mpd.mpd_dout is null and mpd.mpd_idcode='20033533'
)
and bbb.srm_mpd_frref in (
select aa.iah_mpd_ref
from isadm.is_iah aa
where aa.iah_dout is null
and isadm.is_commonfunc.GetFullRout(aa.iah_route,aa.iah_code)||'~' like
(
select min(isadm.is_commonfunc.GetFullRout(a.iah_route,a.iah_code)||'~%')
from isadm.is_iah a
where a.iah_dout is null
and a.iah_icode = 'I_000002'
and a.iah_mpd_ref =:MPD)
)
and aaa.srl_ce in
(select
bces.bces_ce
from
isadm.is_bces bces
join isadm.is_ibt ibt
on bces.bces_ibt_ref=ibt.ibt_sysno
where bces.bces_dout is null
and ibt.ibt_dout is null
and ibt.ibt_id='168')
order by aaa.srl_ce,TO_NUMBER(aaa.srl_srno)
) aaaa
) bbbb
group by GrpNo
</QT_SQL>
</QueryTempl>
<!--Возвращает имя пользователя системы тоесть логин-->
<QueryTempl QT_ID="OSOBAQ" QT_NAME="Query8">
<QT_SQL>
select
isadm.is_commonfunc.GetMPDName(sys_context('IS_CONTEXT','MPDSysNo'))
from sys.dual
</QT_SQL>
</QueryTempl>
<ParamTempl PT_ID="DATEBEGIN" PT_NAME="З (дд.мм.рррр)" PT_TYPE="S"></ParamTempl>
<ParamTempl PT_ID="DATEEND" PT_NAME="по (дд.мм.рррр)" PT_TYPE="S"></ParamTempl>
<ParamTempl PT_ID="IAH" PT_NAME="Дирекція" PT_TYPE="Q">
<PT_SQL>
select
mpd.mpd_sysno as KeyList,
mpd.mpd_name1 as PickList
from
isadm.is_mpd mpd
join isadm.is_iah iah on mpd.mpd_sysno=iah.iah_mpd_ref
where
iah.iah_dout is null
and mpd.mpd_dout is null
and iah.iah_code in ('050001','050002','050003','050004','050005','050006','050007','050008','050009'
,'050010','050011','050012','050013','050014','050015','050016','050017','050018','050019','050020'
,'050021','050022','050023','050024','050025','050026','050027','050029')
and iah.iah_dout is null
</PT_SQL></ParamTempl>
<ValueTempl VT_ID="DBEG" VT_QT_REF="DATEQ" VT_SRCTYPE="G" VT_PT_REF="DATEBEGIN"></ValueTempl>
<ValueTempl VT_ID="DEND" VT_QT_REF="DATEQ" VT_SRCTYPE="G" VT_PT_REF="DATEEND"></ValueTempl>
<ValueTempl VT_ID="MPD" VT_QT_REF="MPDQ" VT_SRCTYPE="G" VT_PT_REF="IAH"></ValueTempl>
<ValueTempl VT_ID="DBEG" VT_QT_REF="ICD2Q" VT_SRCTYPE="G" VT_PT_REF="DATEBEGIN"></ValueTempl>
<ValueTempl VT_ID="DEND" VT_QT_REF="ICD2Q" VT_SRCTYPE="G" VT_PT_REF="DATEEND"></ValueTempl>
<ValueTempl VT_ID="MPD" VT_QT_REF="ICD2Q" VT_SRCTYPE="G" VT_PT_REF="IAH"></ValueTempl>
<ValueTempl VT_ID="DBEG" VT_QT_REF="ICDQ" VT_SRCTYPE="G" VT_PT_REF="DATEBEGIN"></ValueTempl>
<ValueTempl VT_ID="DEND" VT_QT_REF="ICDQ" VT_SRCTYPE="G" VT_PT_REF="DATEEND"></ValueTempl>
<ValueTempl VT_ID="MPD" VT_QT_REF="ICDQ" VT_SRCTYPE="G" VT_PT_REF="IAH"></ValueTempl>
<ValueTempl VT_ID="DBEG" VT_QT_REF="BRQ" VT_SRCTYPE="G" VT_PT_REF="DATEBEGIN"></ValueTempl>
<ValueTempl VT_ID="DEND" VT_QT_REF="BRQ" VT_SRCTYPE="G" VT_PT_REF="DATEEND"></ValueTempl>
<ValueTempl VT_ID="MPD" VT_QT_REF="BRQ" VT_SRCTYPE="G" VT_PT_REF="IAH"></ValueTempl>
<ValueTempl VT_ID="DBEG" VT_QT_REF="RETQ" VT_SRCTYPE="G" VT_PT_REF="DATEBEGIN"></ValueTempl>
<ValueTempl VT_ID="DEND" VT_QT_REF="RETQ" VT_SRCTYPE="G" VT_PT_REF="DATEEND"></ValueTempl>
<ValueTempl VT_ID="MPD" VT_QT_REF="RETQ" VT_SRCTYPE="G" VT_PT_REF="IAH"></ValueTempl>
<ValueTempl VT_ID="DBEG" VT_QT_REF="SUMQ" VT_SRCTYPE="G" VT_PT_REF="DATEBEGIN"></ValueTempl>
<ValueTempl VT_ID="DEND" VT_QT_REF="SUMQ" VT_SRCTYPE="G" VT_PT_REF="DATEEND"></ValueTempl>
<ValueTempl VT_ID="MPD" VT_QT_REF="SUMQ" VT_SRCTYPE="G" VT_PT_REF="IAH"></ValueTempl>
</ReportTemplSchema>