Подсчет остатков с учётом субартикуов : Супермаг Плюс (Супермаг 2000)

█ 27.07.2007 12:04

недлю пытаюсь модифицировать обычный подсчёт остатков:

Код:

SELECT c.article as "Артикул",
       c.name as "Наименование",
       NVL(inprices.price, 0) as "Зак цен",
       NVL(buy.quantity, 0) as "Ко-во прихода",
       NVL(pr.subarticle, '--') as "СубАрт",
       NVL(inprices.price, 0)*NVL(buy.quantity, 0) as "Cумма прихода",
       --NVL(inclients.name, '--') as "Поставщик",
       --NVL(saleret.createdat, 0) as "Дата возврата",
       NVL(saleret.quantity, 0) as "Кол-во возврата",
       NVL(saleret.quantity, 0)*NVL(saleret.price, 0) as "$$$ возврата",
       NVL(q.quantity,0)-NVL(pr.quantity,0)+NVL(rs.quantity,0) as "Остаток",
       NVL(outprices.price,0) as "resprice",
       NVL((NVL(q.quantity,0)-NVL(pr.quantity,0)+NVL(rs.quantity,0))*
       NVL(outprices.price,0),0) as "$$$"
  FROM supermag.smcard c,
       supermag.smgoods q,
       supermag.sacardclass cc,
       (
        SELECT dc.article, ss.subarticle,
               SUM(ss.quantity) as quantity
                -- SUM(dc.itemprice) as price
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE ss.doctype=dc.doctype
           AND ss.docid=dc.docid
           AND not(ss.quantity = 0)
           AND ss.specitem = dc.specitem
           AND d.doctype=dc.doctype
           AND d.id=dc.docid
           AND d.docstate=3
           AND TO_DATE(d.createdat) >= to_date('01.07.2007', 'DD.MM.YYYY')
           AND d.locationto = (1)
        GROUP BY dc.article, ss.subarticle
       ) pr,
       (
        SELECT dc.article, ss.subarticle,
               SUM(ss.quantity) as quantity
                -- SUM(dc.itemprice) as price
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE ss.doctype=dc.doctype
           AND ss.docid=dc.docid
           AND not(ss.quantity = 0)
           AND ss.specitem = dc.specitem
           AND d.doctype=dc.doctype
           AND d.id=dc.docid
           AND d.docstate=3
           AND (d.opcode = (0)) -- 0 if sklad; 4 if tochka
           AND TO_DATE(d.createdat) between to_date('1.06.2007', 'DD.MM.YYYY') and to_date('01.07.2007', 'DD.MM.YYYY')
           AND d.locationto = (1)
         GROUP BY dc.article, ss.subarticle --dc.itemprice,
       ) buy,
       (
       SELECT his.PRICE, his.ARTICLE, his.STORELOC
            FROM SUPERMAG.SMPRICEHISTORY his, SUPERMAG.SMLOCPRICES prt
              WHERE his.PRICETYPE = prt.PRICETYPE
              AND prt.LOCID = his.STORELOC
              AND (bitand(prt.FLAGS, 2) = 2)
              AND his.RECID = (SELECT MAX(h.RECID)
                               FROM SUPERMAG.SMPRICEHISTORY h
                               WHERE h.STORELOC = his.STORELOC
                                 AND h.ARTICLE = his.ARTICLE
                                 AND h.PRICETYPE = his.PRICETYPE
                                 AND TRUNC(h.EVENTTIME) <= TRUNC(TO_DATE('01.07.2007', 'DD.MM.YYYY')))
        ) outprices,       
       (
       SELECT his.PRICE, his.ARTICLE
            FROM SUPERMAG.SMPRICES his
              WHERE his.PRICETYPE = 1
              AND his.STORELOC = 1
       ) inprices, 
       (
        SELECT dc.article, ss.subarticle,
               SUM(ss.quantity) as quantity,
               SUM(dc.itemprice) as price
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE ss.doctype=dc.doctype
           AND ss.docid=dc.docid
           AND not(ss.quantity = 0)
           AND ss.specitem = dc.specitem
           AND d.doctype=dc.doctype
           AND d.id=dc.docid
           AND d.docstate=3
           AND d.opcode = 3
           AND TO_DATE(d.createdat) >= to_date('01.06.2007', 'DD.MM.YYYY')
           AND d.locationto = (1)
           AND d.clientindex = 4 --client!!!
           GROUP BY dc.article, ss.subarticle           
       ) saleret,
       (
        SELECT dc.article, ss.subarticle,
               SUM(ss.quantity) as quantity,
               SUM(dc.itemprice) as price
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE ss.doctype=dc.doctype
           AND ss.docid=dc.docid
           AND not(ss.quantity = 0)
           AND ss.specitem = dc.specitem
           AND d.doctype=dc.doctype
           AND d.id=dc.docid
           AND d.docstate=3
           AND TO_DATE(d.createdat) >= to_date('01.07.2007', 'DD.MM.YYYY')
           AND d.locationfrom = (1)
        GROUP BY dc.article, ss.subarticle
       ) rs
 WHERE c.article=pr.article(+)
   AND c.article=rs.article(+)
   AND c.article=q.article(+)
   AND c.article=buy.article(+)
   AND c.article=outprices.article(+)
   AND q.storeloc=outprices.storeloc
   AND c.article=inprices.article(+)
   AND c.article=saleret.article(+)
   AND pr.subarticle = rs.subarticle
   --AND pr.subarticle = saleret.subarticle
   --AND pr.subarticle = buy.subarticle
   --AND c.accepted=1--active cards
   AND q.storeloc(+)=(1)
   AND c.idclass=cc.id(+)
   AND cc.tree like '1.1.1.%'
   AND c.name like '%Из Рук В Руки%'
   AND (NVL(q.quantity,0)-NVL(pr.quantity,0)+NVL(rs.quantity,0))>=0-- > 0 condition

Работакет это не так как надо.
А надо чтобы товары дифферинцировались не только по артикулу, но и по субартикулу. Помогите плз.

█ 27.07.2007 14:19

Получение остатков с учетом шкал должно выглядеть как-то так:

Код:

SELECT c.article as "Артикул",
       q.subarticle as "Субартикул",
       c.name as "Наименование",
       NVL(q.quantity,0)-NVL(pr.quantity,0)+NVL(rs.quantity,0) as "Остаток"
  FROM supermag.smcard c,
       (
        SELECT q.article,
               qs.subarticle,
               NVL(qs.quantity,q.quantity) as quantity
          FROM supermag.smgoods q,
               supermag.smgoodsscale qs
         WHERE q.article=qs.article(+)
           AND q.storeloc=qs.storeloc(+)
           AND q.storeloc=(4)
       ) q,
       (
        SELECT dc.article,
               ss.subarticle,
               SUM(NVL(ss.quantity,dc.quantity)) as quantity
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE d.doctype=dc.doctype
           AND d.doctype=ss.doctype(+)
           AND d.id=dc.docid
           AND d.id=ss.docid(+)
           AND d.docstate=3
           AND TO_DATE(d.createdat) >= ('03.07.2007')
           AND d.locationto = (4)
        GROUP BY dc.article, ss.subarticle
       ) pr,
       (
        SELECT dc.article,
               ss.subarticle,
               SUM(NVL(ss.quantity,dc.quantity)) as quantity
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE d.doctype=dc.doctype
           AND d.doctype=ss.doctype(+)
           AND d.id=dc.docid
           AND d.id=ss.docid(+)
           AND d.docstate=3
           AND TO_DATE(d.createdat) >= ('03.07.2007')
           AND d.locationfrom = (4)
        GROUP BY dc.article, ss.subarticle
       ) rs
 WHERE q.article=pr.article(+)
   AND q.article=pr.subarticle(+)
   AND c.article=rs.article(+)
   AND c.article=rs.subarticle(+)
   AND c.article=q.article(+)

Гарантии, что это работает правильно, дать не могу, в нашей базе шкалы не используются, написал на вскидку.

█ 30.07.2007 09:00

1) thnx
2)

Код:

AND c.article=rs.subarticle(+)

???

█ 30.07.2007 10:27

Цитата:

nekrom@nt.grave ➤ 1) thnx
2)

Код:

AND c.article=rs.subarticle(+)

???

Тьфу... обшибся, вот так должно быть:

Код:

SELECT c.article as "Артикул",
       q.subarticle as "Субартикул",
       c.name as "Наименование",
       NVL(q.quantity,0)-NVL(pr.quantity,0)+NVL(rs.quantity,0) as "Остаток"
  FROM supermag.smcard c,
       (
        SELECT q.article,
               qs.subarticle,
               NVL(qs.quantity,q.quantity) as quantity
          FROM supermag.smgoods q,
               supermag.smgoodsscale qs
         WHERE q.article=qs.article(+)
           AND q.storeloc=qs.storeloc(+)
           AND q.storeloc=(4)
       ) q,
       (
        SELECT dc.article,
               ss.subarticle,
               SUM(NVL(ss.quantity,dc.quantity)) as quantity
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE d.doctype=dc.doctype
           AND d.doctype=ss.doctype(+)
           AND d.id=dc.docid
           AND d.id=ss.docid(+)
           AND d.docstate>=2
           AND TO_DATE(d.createdat) >= ('03.07.2007')
           AND d.locationto = (4)
        GROUP BY dc.article, ss.subarticle
       ) pr,
       (
        SELECT dc.article,
               ss.subarticle,
               SUM(NVL(ss.quantity,dc.quantity)) as quantity
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE d.doctype=dc.doctype
           AND d.doctype=ss.doctype(+)
           AND d.id=dc.docid
           AND d.id=ss.docid(+)
           AND d.docstate>=1
           AND TO_DATE(d.createdat) >= ('03.07.2007')
           AND d.locationfrom = (4)
        GROUP BY dc.article, ss.subarticle
       ) rs
 WHERE q.article=pr.article(+)
   AND q.subarticle=pr.subarticle(+)
   AND q.article=rs.article(+)
   AND q.subarticle=rs.subarticle(+)
   AND c.article=q.article(+)

█ 30.07.2007 10:30

Еще, обрати внимание, что надо учитывать документы не только со статусом 3, но и со статусом 2 ("принят в кол-ве")

█ 30.07.2007 10:47

мне цены надо добавлять. Кстати, не подскажешь как к ценам шкалы пределать?

█ 30.07.2007 10:55

Цитата:

nekrom@nt.grave ➤ мне цены надо добавлять. Кстати, не подскажешь как к ценам шкалы пределать?

Какие цены нужны? (Закупочные/Розничные_текущие/Розничные_на_дату/...)

█ 30.07.2007 11:07

какие юзверь в настройках репорта ткнёт

█ 30.07.2007 12:16

Я так понимаю, цены не зависят от шкалы, поэтому делаешь подзапрос на получение цен и привязываешь его по артикулам.

Код:

WHERE ....
AND c.article=x.article(+) ....

где х - подзапрос на получение цен

█ 01.08.2007 10:31

картезианским джойном попахивает