Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 "."(".$loja.")"."
- 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 "."(".$loja.")"."
- 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"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement