---

SELECT (SELECT l.main_doctor_fio_short FROM lpu l WHERE main_status=1 AND ROWNUM=1)
       ,' период с '||TO_CHAR(:DAT1,'dd.mm.yyyy')||' по '||TO_CHAR(:DAT2,'dd.mm.yyyy')
       ,(SELECT l.text FROM lpu l WHERE main_status=1 AND ROWNUM=1)
       ,(SELECT d.name FROM solution_apteka.dep d WHERE d.id=:DEP)
       ,(SELECT l.text
           FROM solution_apteka.mol m
           JOIN solution_apteka.lu l ON l.id=m.man_luid AND l.status=1 AND m.status=1 AND m.depid=:DEP AND m.code='STS'
         )
       ,(SELECT l.gb_fio_short FROM lpu l WHERE main_status=1 AND ROWNUM=1)
       ,(SELECT l.stext
           FROM solution_apteka.mol m
           JOIN solution_apteka.lu l ON l.id=m.man_luid AND l.status=1 AND m.status=1 AND m.depid=:DEP AND m.code='STS'
         )
  FROM DUAL


SELECT b.mol_to, TO_CHAR(a.dat,'dd.mm.yyyy')
,p.product_name
,p.form
,p.unit
,SUM(a.qty) AS qty
,b.mol_to
FROM solution_apteka.v_bill b
JOIN solution_apteka.act a ON a.bill_id=b.id AND b.type IN (1,6) AND b.dep_from_id=:DEP
JOIN solution_apteka.v_product p ON p.id=a.product_id
JOIN solution_apteka.product_type pt ON pt.id=p.product_type_id
WHERE a.dat BETWEEN :DAT1 AND :DAT2
AND (REPLACE(':FIN','''') IS NULL OR p.PAY_TYPE_ID IN (:FIN))
AND (REPLACE(':GR','''') IS NULL OR p.product_type_id  IN (:GR))
GROUP BY b.mol_to
,TO_CHAR(a.dat,'dd.mm.yyyy')
,p.product_name
,p.form
,p.unit
ORDER BY TO_CHAR(a.dat,'dd.mm.yyyy'),b.mol_to,p.product_name, p.form, p.unit

---

---

---