Код:
SELECT (SELECT l.rgnid
FROM smstorelocations l
WHERE l.ID = s2.LOCATION) rgn, s2.LOCATION, (SELECT l.NAME
FROM smstorelocations l
WHERE l.ID = s2.LOCATION) locname, TRUNC (SUM (s2.quantity * s2.itemprice), 2) totalsum,
TRUNC (SUM (s2.quantity * s2.itemprice_out), 2) totalsum_out
FROM (SELECT s.LOCATION,
CASE
WHEN COUNT (ROWNUM) OVER (PARTITION BY s.LOCATION, s.article) = 1
THEN s.goods - s.goods_in
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods
THEN NULL
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) <= s.goods_in
AND ROW_NUMBER () OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) <
COUNT (ROWNUM) OVER (PARTITION BY s.LOCATION, s.article)
THEN NULL
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) = s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN s.quantity
ELSE s.goods - s.goods_in
END
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) =
SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article)
AND SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) < s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN s.quantity + (s.goods - SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex))
ELSE s.goods - s.goods_in
END
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) =
SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article)
AND SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) > s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN s.quantity - (SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.goods)
ELSE s.goods - s.goods_in
END
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) < s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN s.quantity
ELSE SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.goods_in
END
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) > s.goods
THEN CASE
WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
THEN s.quantity - (SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.goods)
ELSE s.goods - s.goods_in
END
ELSE s.quantity
END quantity,
NVL (s.totalprice / DECODE (s.quantity, 0, NULL, s.quantity),
NVL ((SELECT p.price
FROM smprices p
WHERE p.storeloc = s.LOCATION AND p.article = s.article AND p.pricetype = 6), 0)
) itemprice,
itemprice_out
FROM (SELECT s.LOCATION, s.article, d.clientindex, d.createdat, NVL (d.quantity, 0) quantity, s.goods_out + NVL (g.quantity, 0) goods,
NVL (g.quantity, 0) goods_in, NVL (d.totalprice, 0) totalprice, (s.totalprice_out / s.goods_out) itemprice_out
FROM (SELECT /*+ INDEX(D) INDEX(S)*/
d.locationfrom LOCATION, s.article, SUM (s.quantity) goods_out, SUM (s.totalprice) totalprice_out
FROM smspec s, smdocuments d
WHERE s.doctype = d.doctype
AND s.docid = d.ID
AND d.doctype = 'CS'
AND d.createdat BETWEEN TRUNC (ADD_MONTHS (TO_DATE (SYSDATE), -1), 'MON') AND LAST_DAY (ADD_MONTHS (TO_DATE (SYSDATE), -1))
GROUP BY d.locationfrom, s.article) s,
(SELECT storeloc, article, SUM (quantity) quantity
FROM (SELECT /*+ INDEX(D) INDEX(S)*/
NVL (d.locationfrom, d.locationto) storeloc, s.article,
DECODE (d.doctype, 'WI', -s.quantity, 'CR', -s.quantity, s.quantity) quantity
FROM smspec s, smdocuments d
WHERE s.doctype = d.doctype
AND s.docid = d.ID
AND d.doctype IN ('WI', 'WO', 'CS', 'CR')
AND d.createdat BETWEEN TRUNC (ADD_MONTHS (TO_DATE (SYSDATE), 0), 'MON') AND SYSDATE
UNION ALL
SELECT storeloc, article, quantity
FROM smgoods)
WHERE quantity > 0
GROUP BY storeloc, article) g,
(SELECT /*+ INDEX(D) INDEX(S)*/
d.locationto LOCATION, d.clientindex, d.createdat, s.article, SUM (s.quantity) quantity, SUM (s.totalprice) totalprice
FROM smdocuments d, smspec s
WHERE d.doctype = 'WI'
AND d.createdat BETWEEN TRUNC (SYSDATE - (90 + (TRUNC (SYSDATE) - TRUNC (ADD_MONTHS (TO_DATE (SYSDATE), -1), 'MON'))))
AND LAST_DAY (ADD_MONTHS (TO_DATE (SYSDATE), -1))
AND d.docstate = 3
AND d.opcode != 9
AND s.doctype = d.doctype
AND s.docid = d.ID
GROUP BY d.locationto, d.clientindex, d.createdat, s.article) d
WHERE s.LOCATION = g.storeloc(+) AND s.article = g.article(+) AND s.LOCATION = d.LOCATION(+) AND s.article = d.article(+)) s) s2
WHERE s2.quantity IS NOT NULL
GROUP BY s2.LOCATION