Вот тут сегодня родилось, сильно ногами не бить, но работает быстрее супермажного отчета.
Код:
SELECT tr, trn, art, artn, (SUM (pr1) + SUM (pr2) - SUM (rh1) - SUM (rh2)), max (mb)
FROM (
SELECT ca1.tree tr, ca1.NAME trn, c.article art, c.NAME artn,
0 pr1, 0 pr2, 0 rh1, 0 rh2, c.MESABBREV mb
FROM supermag.smcard c,
supermag.sacardclass ca1,
supermag.sacardclass ca2
WHERE ca1.tree LIKE :smrgroup
AND c.idclass = ca1.ID
AND ca2.tree = SUBSTR (ca1.tree, 1, INSTR (ca1.tree, '.'))
UNION ALL
SELECT ca1.tree tr, ca1.NAME trn, c1.article art, c1.NAME artn,
SUM (psp.altquantity) pr1, 0 pr2, 0 rh1, 0 rh2, '' mb
FROM supermag.smprodexpspec psp,
supermag.smdocuments d,
supermag.smcard c1,
supermag.sacardclass ca1,
supermag.sacardclass ca2
WHERE psp.docid = d.ID
AND psp.doctype = d.doctype
AND d.doctype = 'PE'
AND d.createdat <= :smrdatestart
AND d.docstate = 3
AND d.locationfrom = :smrstoreloc
AND c1.article = psp.artingredient
AND c1.idclass = ca1.ID
AND ca1.tree LIKE :smrgroup
AND ca2.tree = SUBSTR (ca1.tree, 1, INSTR (ca1.tree, '.'))
GROUP BY ca1.tree, ca1.NAME, c1.article, c1.NAME
UNION ALL
SELECT ca1.tree, ca1.NAME, c.article, c.NAME, 0,
SUM (sp.quantity), 0, 0, ''
FROM supermag.smdocuments d,
supermag.smspec sp,
supermag.smcard c,
supermag.sacardclass ca1,
supermag.sacardclass ca2
WHERE d.ID = sp.docid
AND d.doctype = sp.doctype
AND d.docstate > 1
AND d.doctype = 'PD'
AND d.createdat <= :smrdatestart
AND d.LOCATION = :smrstoreloc
AND sp.article = c.article
AND c.idclass = ca1.ID
AND ca1.tree LIKE :smrgroup
AND ca2.tree = SUBSTR (ca1.tree, 1, INSTR (ca1.tree, '.'))
GROUP BY ca1.tree, ca1.NAME, c.article, c.NAME
UNION ALL
SELECT ca1.tree, ca1.NAME, c.article, c.NAME, 0, 0,
SUM (sp.quantity), 0, ''
FROM supermag.smdocuments d,
supermag.smspec sp,
supermag.smcard c,
supermag.sacardclass ca1,
supermag.sacardclass ca2
WHERE d.ID = sp.docid
AND d.doctype = sp.doctype
AND d.docstate = 3
AND (d.doctype = 'PN' OR d.doctype = 'PO')
AND d.createdat <= :smrdatestart
AND d.locationto = :smrstoreloc
AND sp.article = c.article
AND c.idclass = ca1.ID
AND ca1.tree LIKE :smrgroup
AND ca2.tree = SUBSTR (ca1.tree, 1, INSTR (ca1.tree, '.'))
GROUP BY ca1.tree, ca1.NAME, c.article, c.NAME
UNION ALL
SELECT ca1.tree, ca1.NAME, c.article, c.NAME, 0, 0, 0,
SUM (psp.quantity), ''
FROM supermag.smdocuments d,
supermag.smprodactspecin psp,
supermag.smcard c,
supermag.sacardclass ca1,
supermag.sacardclass ca2
WHERE d.ID = psp.docid
AND d.doctype = psp.doctype
AND d.docstate > 1
AND d.doctype = 'PD'
AND d.createdat <= :smrdatestart
AND d.LOCATION = :smrstoreloc
AND psp.article = c.article
AND c.idclass = ca1.ID
AND ca1.tree LIKE :smrgroup
AND ca2.tree = SUBSTR (ca1.tree, 1, INSTR (ca1.tree, '.'))
GROUP BY ca1.tree, ca1.NAME, c.article, c.NAME)
GROUP BY tr, trn, art, artn
Если у кого появится конструктивная критика буду рад. Да сразу говорю, что остатки между цехами не собирался различать, поэтому и не рассматриваю их.