-- Список артикулов заказа с выводом всех ШК для артикула в одной строке
-- через аналитические ф-ии (в 1С 7.7 через ADODB почему-то не работает sys_xmlagg)
-- Количество по спецификации суммируется

SELECT article, TO_CHAR(quantity) quantity, shortname, ltrim(sys_connect_by_path(barcode, ';'), ';') as bar
  FROM
  ( SELECT  c.article, c.quantity, c.shortname, b.barcode,
            row_number() over (partition by c.article order by b.barcode) as id,
            count(*) over (partition by c.article order by b.barcode ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cnt
      FROM
        (  SELECT a.article, SUM(s.quantity) quantity, a.shortname
             FROM supermag.smspec s, supermag.smdocuments d, supermag.smcard a
            WHERE d.ID = 'ЗП40232' AND d.doctype = 'OR' AND d.ID = s.docid AND d.doctype = s.doctype
              AND s.article = a.article
         GROUP BY a.article, a.shortname   
        ) c, supermag.smstoreunits b
     WHERE c.article=b.article(+)
  )      
 WHERE id = cnt
 START WITH id = 1
 CONNECT BY id = prior id + 1 and article = prior article
;

    -- Возврат количества и списка артикулов в спецификации без признака 'грузить в кассу'
    select count(*), sys_xmlagg(xmlelement(col, sc.article ||', ')).extract('/ROWSET/COL/text()').getclobval()  
      from supermag.smspec sp, supermag.smcard sc
     where sp.doctype = :DocType and sp.docid = :ID 
       and sp.article=sc.article and sc.cashload=0;