Код:
Select l.name Магазин,
rep_getcardclasspath(cc.tree, 1, 1) as Группа_товара,
c.article as Артикул_товара,
replace(c.name,CHR(9), ' ') as Наименование_товара,
nvl(get_goods_ondate2(c.article, l.id, '&Начало_периода'),0) as Колво_На_остатках,
nvl(cusf_last_wiprice(c.article,
l.id,
'&Начало_периода'),0) as Цена_закупа,
p.price as Цена_продажи,
nvl(get_goods_ondate2(c.article, l.id, '&Начало_периода') *
cusf_last_wiprice(c.article,
l.id,
'&Начало_периода'),0) as Сумма_в_закупе,
nvl(get_goods_ondate2(c.article, l.id, '&Начало_периода') * p.price,
0) as Сумма_в_продажах,
nvl(cusf_last_wiquant(c.article,
l.id,
'&Начало_периода'),0) as Колво_посл_прихода,
nvl(cusf_last_widate(c.article, l.id, '&Начало_периода'),null) as Дата_посл_прихода
From smstorelocations l,
sacardclass cc,
smcard c,
smprices p,
smpricetypes pt,
(Select ssc.article,dsc.createdat
from smdocuments dsc, smspec ssc, smstorelocations lsc
where dsc.id = ssc.docid
and dsc.doctype = ssc.doctype
and dsc.createdat >= to_date('&Начало_периода','dd.mm.yyyy')
and dsc.createdat <= to_date('&Конец_периода','dd.mm.yyyy')
and dsc.doctype in ('CS','WO')
and lsc.id = dsc.locationfrom
and upper(lsc.name) like upper('%&Mecmo_Хранения%')
and lsc.id not in (-1,1,2,10,13,14,22,34,35,40,45,48,51,52,54,55,56,57,58,59,60)
group by ssc.article,dsc.createdat) prod
Where cc.id = c.idclass
and c.article not in
(Select swi.article
from smdocuments dwi, smspec swi, smstorelocations lwi
where dwi.id = swi.docid
and dwi.doctype = swi.doctype
and dwi.createdat >= to_date('&Начало_периода','dd.mm.yyyy')
and dwi.createdat <= to_date('&Конец_периода','dd.mm.yyyy')
and dwi.doctype in ('WI')
and lwi.id = dwi.locationfrom
and upper(lwi.name) like upper('%&Mecmo_Хранения%')
and lwi.id not in (-1,1,2,10,13,14,22,34,35,40,45,48,51,52,54,55,56,57,58,59,60)
group by swi.article)
and c.article not in (prod.article)
and p.pricetype = pt.id
and p.article = c.article
and p.storeloc = l.id
and l.id in
(select loc.id
from smstorelocations loc
where upper(loc.name) like upper('%&Mecmo_Хранения%'))
and l.id not in (-1,1,2,10,13,14,22,34,35,40,45,48,51,52,54,55,56,57,58,59,60)
and prod.createdat >= to_date('&Начало_периода','dd.mm.yyyy') and prod.createdat <= to_date('&Конец_периода','dd.mm.yyyy')
group by l.name,cc.tree,c.article,l.id,p.price,c.name