Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- with
- classmerc as (
- select distinct(prod_codigo) as codprod,
- (select a.grup_codigo from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 2)) as coddpto,
- (select a.grup_codigo from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 4)) as codsecao,
- (select a.grup_codigo from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 6)) as codcat,
- prod_grup_codigo as codfamilia,
- (select a.grup_descricao from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 2)) as descdpto,
- (select a.grup_descricao from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 4)) as descsecao,
- (select a.grup_descricao from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 6)) as desccat,
- prod_grup_nome as descfamilia
- from produtos
- left join grupos z on (prod_grup_codigo=z.grup_codigo)
- ),
- vendas as (
- select distinct(t1.mprd_prod_codigo) codprod,
- coalesce(round(sum(t1.mprd_valor),2),0.00) as valor,
- coalesce(round(sum(t1.mprd_prcompra),2),0.00) as valor_prcompra
- from movprodd17 as t1
- where t1.mprd_datamvto between '2017-11-01' and '2017-11-30'
- and t1.mprd_unid_codigo='001'
- and t1.mprd_dcto_codigo IN ('7165','7320','7321','7325','7326','7327','7328','7329','7330','7331','7336','7337','7338','7339','7340')
- and t1.mprd_status='N'
- group by t1.mprd_prod_codigo
- ),
- pend as (
- select
- distinct(pest_prod_codigo) as codprod,
- sum(pest_qtde) - sum(pest_qtdebx) as pendentes
- from pendest
- where pest_status='P'
- and (pest_unid_origem='001' or pest_unid_destino='001')
- and pest_cpes_codigo in ('001','100','101','102','104','120','150','151','190','200','201','300','301','302','310','315','320','350','390','400','500','600','800','900')
- group by pest_prod_codigo
- )
- select
- distinct(t1.mprd_prod_codigo),
- prod_descricao,
- prod_complemento,
- prod_marca,
- prod_emb,
- case when prod_qemb='0' then '1' else prod_qemb end as prod_qemb,
- prod_grup_codigo,
- classmerc.descdpto,
- classmerc.descsecao,
- classmerc.desccat,
- prod_grup_nome,
- prod_comp_codigo,
- comp_nome,
- case when vendas.valor is not null then vendas.valor
- else '0.00'
- end as valor_vendas,
- case when vendas.valor_prcompra is not null then vendas.valor_prcompra
- else '0.00'
- end as valor_vendas_prcompra,
- coalesce(round((vendas.valor / sum(vendas.valor) over ())*100,4),0.0000) as part_vendas,
- coalesce(round((vendas.valor_prcompra / sum(vendas.valor_prcompra) over ())*100,4),0.0000) as part_vendas_prcompra,
- round(coalesce(((prun_estoque1+prun_estoque4)-pend.pendentes),0.00000),3) as estdisp_un,
- round(coalesce((((prun_estoque1+prun_estoque4)-pend.pendentes) / prod_qemb),0)) as esdisp_emb,
- round(prun_prvenda,3) as prvevenda_un,
- round(coalesce((prun_prvenda * prod_qemb),0.000),3) as prvenda_emb,
- round(coalesce((((prun_estoque1+prun_estoque4)-pend.pendentes)* prun_prvenda),0.00),2) as vr_estdisp_prvenda
- --round(coalesce(((vendas.valor / vendas.valor_prcompra) * 100),0.0000),4) as difpercvdcp,
- --(coalesce(vendas.valor,0.00) - coalesce(vendas.valor_prcompra,0.00)) as difvalorvdcp
- from movprodd17 as t1
- INNER JOIN produtos on (prod_codigo=t1.mprd_prod_codigo)
- INNER JOIN produn on (prun_prod_codigo=t1.mprd_prod_codigo and prun_unid_codigo='001')
- LEFT JOIN compradores on (comp_codigo=prod_comp_codigo)
- LEFT JOIN classmerc on (t1.mprd_prod_codigo=classmerc.codprod)
- LEFT JOIN vendas on (t1.mprd_prod_codigo=vendas.codprod)
- LEFT JOIN pend on (t1.mprd_prod_codigo=pend.codprod)
- where t1.mprd_datamvto between '2017-11-01' and '2017-11-30'
- and t1.mprd_unid_codigo='001'
- and t1.mprd_dcto_codigo IN ('7165','7320','7321','7325','7326','7327','7328','7329','7330','7331','7336','7337','7338','7339','7340')
- and t1.mprd_status='N'
- group by
- t1.mprd_prod_codigo,
- prod_descricao,
- prod_complemento,
- prod_marca,
- prod_emb,
- prod_qemb,
- prod_grup_codigo,
- classmerc.descdpto,
- classmerc.descsecao,
- classmerc.desccat,
- prod_grup_nome,
- prod_comp_codigo,
- comp_nome,
- vendas.valor,
- vendas.valor_prcompra,
- prun_estoque1,
- prun_estoque4,
- pend.pendentes,
- prun_prvenda,
- classmerc.coddpto,
- classmerc.codsecao,
- classmerc.codcat
- order by t1.mprd_prod_codigo
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement