SQL код:
SELECT sl.name mh_name, e.closedate, to_char(e.closedate, 'dd.mm.yyyy') closedate_char, A.desknum, to_char(A.znum) znum, A.checknum,
C.article, C.NAME ware_name, A.quantity, A.itemprice, A.totalsum,
dk.title discount_type,
round(DECODE(O.EXPENSETYPE, 1, B.PERCENT, to_number(null)), 0) discount_percent,
DECODE(O.EXPENSETYPE, 1, NVL(B.DISCSUM, 0), 0) discount_sum,
to_char(round(
sum(A.quantity*A.itemprice) over (partition by D.desknum, D.znum, A.checknum) -
sum(A.totalsum) over (partition by D.desknum, D.znum, A.checknum),
0
)) discount_sum_check
FROM SUPERMAG.SAOPERATION O,
SUPERMAG.SMCASHCHECKS D,
SUPERMAG.SMCASHCHECKITEMS A,
SUPERMAG.SMCASHDISC B,
SUPERMAG.SMCASHZ E,
SUPERMAG.SMDiscKind dk,
SUPERMAG.SMCARD c,
SUPERMAG.SMSTORELOCATIONS SL ,Supermag.SACardClass,Supermag.SACardClass R1
WHERE E.CLOSEDATE between to_date('1.3.2023', 'dd.mm.yyyy')
and to_date('15.3.2023', 'dd.mm.yyyy')
and 1 = 1 and E.locid in ( -1, 6.0)
AND E.LOCID = D.LOCID
AND E.DESKNUM = D.DESKNUM
AND E.ZNUM = D.ZNUM
AND D.OPCODE = O.ID
AND O.Expensetype = 1
AND D.LOCID = A.LOCID
AND D.DESKNUM = A.DESKNUM
AND D.ZNUM = A.ZNUM
AND D.CHECKNUM = A.CHECKNUM
AND A.LOCID = B.LOCID
AND A.DESKNUM = B.DESKNUM
AND A.ZNUM = B.ZNUM
AND A.CHECKNUM = B.CHECKNUM
AND A.ITEM = B.ITEM
--AND B.DISCKIND != 4
and a.article = c.article
and b.disckind = dk.id
and e.locid = sl.id
and Supermag.SACardClass.ID=C.IDClass
and C.IDClass=R1.ID
and ( R1.Tree like '26.49.%')
-- учитываем возвраты
and not exists (
select 1
from supermag.SMDOCUMENTS h, supermag.SMSPEC s
where h.doctype = 'CR'
and h.createdat between to_date('1.3.2023', 'dd.mm.yyyy') and to_date('15.3.2023', 'dd.mm.yyyy')
and h.doctype = s.doctype
and h.id = s.docid
and h.locationto = E.LOCID
and s.article = C.ARTICLE
)