Отчет товар без движения. Fast Report : Супермаг Плюс (Супермаг 2000)

with prod as (Select l.name as loc,l.id as idp,rep_getcardclasspath(cc.tree,1,1) as grp,c.article as art,replace(c.name,chr(9),' ')as naim ,nvl(ttr.quantity,0) as ttr,nvl(cusf_last_wiprice(c.article,l.id,'"+Begin_.Value.ToString("dd.MM.yyyy")+"'),0) as wipr, p.price,nvl(ttr.quantity *cusf_last_wiprice(c.article,l.id,'"+Begin_.Value.ToString("dd.MM.yyyy")+"'),0) as sumprih,nvl(ttr.quantity * p.price,0) as sumprod,nvl(cusf_last_wiquant(c.article,l.id,'"+Begin_.Value.ToString("dd.MM.yyyy")+"'),0) as quant ,nvl(cusf_last_widate(c.article, l.id, '"+Begin_.Value.ToString("dd.MM.yyyy")+"'),null) as lastdat from ttremains ttr,smstorelocations l,smcard c,sacardclass cc, smprices p,smpricetypes pt 
      where cc.id = c.idclass 
      and p.pricetype = pt.id 
      and p.article = c.article 
      and p.storeloc = l.id 
      and c.article = ttr.article 
      and l.id = ttr.storeloc 
      and c.article not in (Select ssc.article 
        from smdocuments dsc, smspec ssc, smstorelocations lsc 
      where dsc.id = ssc.docid 
      and dsc.doctype = ssc.doctype 
      and dsc.createdat >= to_date('"+Begin_.Value.ToString("dd.MM.yyyy")+"', 'dd.mm.yyyy') 
      and dsc.createdat <= to_date('"+Begin_.Value.ToString("dd.MM.yyyy")+"', 'dd.mm.yyyy') 
      and dsc.doctype in ('CS', 'WO') 
      and lsc.id = dsc.locationfrom 
      and lsc.id in 
      (select ll.id 
        from smstorelocations ll 
      where upper(ll.name) like upper('%"+Mesto.Text+"%') and ll.id not in (-1,1,2,10,13,14,22,34,35,40,45,48,51,52,54,55,56,57,58,59,60)) 
      group by ssc.article) 
      and l.id in (select loc2.id 
        from smstorelocations loc2 
      where upper(loc2.name) like upper('%"+Mesto.Text+"%')))
Select prod.loc,prod.grp,prod.art,prod.naim,prod.ttr,prod.wipr,prod.price,prod.sumprih,prod.sumprod,prod.quant,prod.lastdat from prod
         where prod.art not in (Select swi.article
          from smdocuments dwi, smspec swi, smstorelocations lwi
         where dwi.id = swi.docid
           and dwi.doctype = swi.doctype
           and dwi.createdat >= to_date('&Начало',
                                        'dd.mm.yyyy')
           and dwi.createdat <= to_date('&Конец', 'dd.mm.yyyy')
           and dwi.doctype in ('WI')
           and lwi.id = dwi.locationto
           and lwi.id in
               (select loc.id
                  from smstorelocations loc
                 where upper(loc.name) like upper('%&Место%')
                   and loc.id not in (-1,1,2,10,13,14,22,34,35,40,45,48,51,52,54,55,56,57,58,59,60))
         group by swi.article)

RepHost host;
    private void btnOk_Click(object sender, EventArgs e)
    {
      host = new RepHost(Report);
      RepOracleRunner.ExecuteText(host.Connection,
      SqlFormat("begin supermag.remains.calcfromsqlplus(to_date('"+Begin_.Value.ToString("dd.MM.yyyy")+"','dd.mm.yyyy')); end;"));
      TableDataSource remains = Report.GetDataSource("rem") as TableDataSource;
      remains.SelectCommand = 
        "with prod as (Select l.name as loc,l.id as idp,rep_getcardclasspath(cc.tree,1,1) as grp,c.article as art,replace(c.name,chr(9),' ')as naim ,nvl(ttr.quantity,0) as ttr,nvl(cusf_last_wiprice(c.article,l.id,'"+Begin_.Value.ToString("dd.MM.yyyy")+"'),0) as wipr, p.price,nvl(ttr.quantity *cusf_last_wiprice(c.article,l.id,'"+Begin_.Value.ToString("dd.MM.yyyy")+"'),0) as sumprih,nvl(ttr.quantity * p.price,0) as sumprod,nvl(cusf_last_wiquant(c.article,l.id,'"+Begin_.Value.ToString("dd.MM.yyyy")+"'),0) as quant ,nvl(cusf_last_widate(c.article, l.id, '"+Begin_.Value.ToString("dd.MM.yyyy")+"'),null) as lastdat from ttremains ttr,smstorelocations l,smcard c,sacardclass cc, smprices p,smpricetypes pt "+ 
      "where cc.id = c.idclass "+ 
      "and p.pricetype = pt.id "+ 
      "and p.article = c.article "+ 
      "and p.storeloc = l.id "+ 
      "and c.article = ttr.article "+ 
      "and l.id = ttr.storeloc "+ 
      "and c.article not in (Select ssc.article "+ 
      "from smdocuments dsc, smspec ssc, smstorelocations lsc "+ 
      "where dsc.id = ssc.docid "+ 
      "and dsc.doctype = ssc.doctype "+ 
      "and dsc.createdat >= to_date('"+Begin_.Value.ToString("dd.MM.yyyy")+"', 'dd.mm.yyyy') "+ 
      "and dsc.createdat <= to_date('"+Begin_.Value.ToString("dd.MM.yyyy")+"', 'dd.mm.yyyy') "+ 
      "and dsc.doctype in ('CS', 'WO') "+ 
      "and lsc.id = dsc.locationfrom "+ 
      "and lsc.id in "+ 
      "(select ll.id "+ 
      "from smstorelocations ll "+ 
      "where upper(ll.name) like upper('%"+Mesto.Text+"%') and ll.id not in (-1,1,2,10,13,14,22,34,35,40,45,48,51,52,54,55,56,57,58,59,60)) "+ 
      "group by ssc.article) "+ 
      "and l.id in (select loc2.id "+ 
      "from smstorelocations loc2 "+ 
      "where upper(loc2.name) like upper('%"+Mesto.Text+"%'))) "+
      "Select prod.loc,prod.grp,prod.art,prod.naim,prod.ttr,prod.wipr,prod.price,prod.sumprih,prod.sumprod,prod.quant,prod.lastdat from prod "+
      "where prod.art not in (Select swi.article "+
      "from smdocuments dwi, smspec swi, smstorelocations lwi "+
      "where dwi.id = swi.docid "+
      "and dwi.doctype = swi.doctype "+
      "and dwi.createdat >= to_date('&Начало', "+
      "  'dd.mm.yyyy') "+
      "and dwi.createdat <= to_date('&Конец', 'dd.mm.yyyy') "+
      "and dwi.doctype in ('WI') "+
      "and lwi.id = dwi.locationto "+
      "and lwi.id in "+
      "(select loc.id "+
      "from smstorelocations loc "+
      "where upper(loc.name) like upper('%&Место%') "+
      "and loc.id not in (-1,1,2,10,13,14,22,34,35,40,45,48,51,52,54,55,56,57,58,59,60)) "+
      "group by swi.article) ";
    }