SELECT codepto,departamento,codprod,descricao,embalagem,unidade,codauxiliar,loja,preco,imagem,estoque,escala
FROM
(
SELECT ROWNUM seq, a.*
FROM
 (
SELECT * FROM (
SELECT d.CODEPTO, d.descricao DEPARTAMENTO, p.CODPROD, NVL(p.DESCRICAO7,p.descricao) DESCRICAO,
NVL(p.descricaodinamica,p.EMBALAGEM) EMBALAGEM, p.UNIDADE, p.CODAUXILIAR,
t.numregiao LOJA,
NVL((SELECT pcp.precofixo
FROM pcprecoprom pcp
WHERE TRUNC(TO_DATE(SYSDATE)) BETWEEN pcp.dtiniciovigencia AND pcp.dtfimvigencia
AND pcp.codprod = p.codprod
AND pcp.numregiao = t.numregiao
AND pcp.codcli IS NULL
AND pcp.precofixo IN (SELECT MIN(pcx.precofixo)
                      FROM pcprecoprom pcx
                      WHERE pcx.codprod   = pcp.codprod
                      AND   pcx.numregiao = pcp.numregiao
                      AND   pcx.codcli IS NULL
                      AND   TRUNC(TO_DATE(SYSDATE)) BETWEEN pcx.dtiniciovigencia AND pcx.dtfimvigencia )
                      GROUP BY pcp.precofixo),t.pvenda1) preco,
'S:\winthor\sistema\Fotos\'||p.codprod||'.png' IMAGEM, e.qtestger estoque, p.numerosseriecontrolados escala
from pcprodut p, pcdepto d, pctabpr t, pcest e, pcprodfilial pf
where p.codepto = d.codepto
and   p.codprod = t.codprod
and   p.codprod = e.codprod
and   t.numregiao = e.codfilial
and   p.codprod = pf.codprod
and   t.numregiao = pf.codfilial
and   t.numregiao between 5 and 39
and   t.numregiao not in (1,2,3,4,6)
and   (e.qtestger-e.qtindeniz) > (e.qtgirodia*2)
and   p.dtexclusao is null
and   p.embalagem <> 'KG'
and   p.embalagem <> '25KG'
and   p.descricao not like '%(MC)%'
and   p.descricao not like 'PD%'
and   p.descricao not like 'LJ%'
and   p.codfornec not in (101385)
and   p.codprod  not in (647320,647344,647460,647469,647110,647468)
and   p.codprod not in (select codprod
                        from pcprodut
                        where descricao like '%GELADO%'
                        and descricao not like '%CONGELADO%'
                        and dtexclusao is null)
and   p.codepto not in (5,12,10000,10003,10002)
and   p.codsec not in (253,211,204,14,226,55,25)
and   p.codcategoria not in (55)
and   p.codsubcategoria not in (88,245,48)
and   pf.foralinha = 'N'
and   p.obs2 not like '%FL%'
and   t.pvenda1 > 0
and e.codfilial in (5,7,8,9,11,12,13,14,15,16,17,18,19,22,23,24)
group by d.CODEPTO, d.descricao, p.CODPROD, p.DESCRICAO7,p.descricao,
p.descricaodinamica,p.EMBALAGEM, p.UNIDADE, p.CODAUXILIAR,
t.numregiao, t.pvenda1, e.qtestger, p.numerosseriecontrolados

union all

select d.CODEPTO, d.descricao DEPARTAMENTO, p.CODPROD, nvl(p.DESCRICAO7,p.descricao) DESCRICAO,
nvl(p.descricaodinamica,p.EMBALAGEM) EMBALAGEM, p.UNIDADE, p.CODAUXILIAR,
t.numregiao LOJA,
nvl((select pcp.precofixo
from pcprecoprom pcp
where trunc(to_date(sysdate)) between pcp.dtiniciovigencia and pcp.dtfimvigencia
and pcp.codprod = p.codprod
and pcp.numregiao = t.numregiao
and pcp.codcli is null
and pcp.precofixo in (select min(pcx.precofixo)
                      from pcprecoprom pcx
                      where pcx.codprod   = pcp.codprod
                      and   pcx.numregiao = pcp.numregiao
                      and   pcx.codcli is null
                      and   trunc(to_date(sysdate)) between pcx.dtiniciovigencia and pcx.dtfimvigencia )
                      group by pcp.precofixo),t.pvenda1) preco,
'S:\winthor\sistema\Fotos\'||p.codprod||'.png' IMAGEM, e.qtestger estoque, p.numerosseriecontrolados escala
from pcprodut p, pcdepto d, pctabpr t, pcest e, pcprodfilial pf
where p.codepto = d.codepto
and   p.codprod = t.codprod
and   p.codprod = e.codprod
and   t.numregiao = e.codfilial
and   p.codprod = pf.codprod
and   t.numregiao = pf.codfilial
and   t.numregiao between 5 and 39
and   t.numregiao not in (1,2,3,4,6)
and   p.dtexclusao is null
and   p.descricao not like '%(MC)%'
and   p.descricao not like 'PD%'
and   p.descricao not like 'LJ%'
and   p.embalagem <> '25KG'
and   p.codfornec not in (101385)
and   p.codprod  not in (647320,647344,647460,647469,647110,647468)
and   p.codprod not in (select codprod
                        from pcprodut
                        where descricao like '%GELADO%'
                        and descricao not like '%CONGELADO%'
                        and dtexclusao is null)
and   p.codepto not in (5,12,10000,10003,10002)
and   p.codsec not in (253,211,204,14,226,55,25)
and   p.codcategoria not in (55)
and   p.codsubcategoria not in (88,245,48)
and   p.embalagem = 'KG'
and   pf.foralinha = 'N'
and   p.obs2 not like '%FL%'
and   t.pvenda1 > 0
and   e.qtestger <> 0
and   (nvl(e.qtvendmes,0) + nvl(e.qtvendmes1,0) + nvl(e.qtvendmes2,0) + nvl(e.qtvendmes3,0)) <> 0
and e.codfilial in (5,7,8,9,11,12,13,14,15,16,17,18,19,22,23,24)
group by d.CODEPTO, d.descricao, p.CODPROD, p.DESCRICAO7,p.descricao,
p.descricaodinamica,p.EMBALAGEM, p.UNIDADE, p.CODAUXILIAR,
t.numregiao, t.pvenda1, e.qtestger, p.numerosseriecontrolados

) app
order by app.loja, app.departamento, app.descricao
  ) a
order by a.loja, a.departamento, a.descricao
) b
where b.seq>=  ( 1 * 10 ) - 10
  and b.seq<     1 * 10