этот запрос отбирает данные для закладки остатки
Код:
SELECT A.*,
DECODE(A.PACKSIZE, -1, NVL(G.SALEQ, 0), NULL),
DECODE(A.PACKSIZE, -1, A.QUANTITY - NVL(G.SALEQ, 0), NULL) OPERQUANTITY,
DECODE(A.PACKSIZE, -1, A.FREEQUANTITY - NVL(G.SALEQ, 0), NULL) OPERFREE
FROM (SELECT L.ID STORELOC,
L.NAME STORELOCTITLE,
'00506' ARTICLE,
-1 PACKSIZE,
NVL(G.QUANTITY, 0) QUANTITY,
NVL(G.RESERVEDQUANTITY, 0) RESERVEDQUANTITY,
NVL(G.INCOMINGQUANTITY, 0) INCOMINGQUANTITY,
NVL(G.AWAITEDQUANTITY, 0) AWAITEDQUANTITY,
NVL(G.FOUNDQUANTITY, 0) FOUNDQUANTITY,
NVL(G.QUANTITY + G.FOUNDQUANTITY -
(G.RESERVEDQUANTITY + G.INCOMINGQUANTITY),
0) FREEQUANTITY
FROM SUPERMAG.SMGOODS G, SUPERMAG.SMSTORELOCATIONS L
WHERE L.ID = G.STORELOC(+)
AND G.ARTICLE(+) = '00506'
UNION
SELECT L.ID STORELOC,
L.NAME STORELOCTITLE,
G.ARTICLE,
G.PACKSIZE,
G.QUANTITY,
G.RESERVEDQUANTITY,
G.INCOMINGQUANTITY,
G.AWAITEDQUANTITY,
G.FOUNDQUANTITY,
G.QUANTITY + G.FOUNDQUANTITY -
(G.RESERVEDQUANTITY + G.INCOMINGQUANTITY) FREEQUANTITY
FROM SUPERMAG.SMGOODSPACKS G, SUPERMAG.SMSTORELOCATIONS L
WHERE L.ID = G.STORELOC
AND G.ARTICLE = '00506') A,
(SELECT G.LOCID, SUM(G.SALEQUANTITY - G.RETURNQUANTITY) SALEQ
FROM SUPERMAG.SMOPERGOODS G
WHERE G.ARTICLE = '00506'
GROUP BY G.LOCID) G
WHERE A.STORELOC = G.LOCID(+)
ORDER BY A.STORELOCTITLE, A.PACKSIZE из него видно что данные для расчёта "опер.оастатки" и "опер. доступно" берутся из SUPERMAG.SMOPERGOODS