-- View: public.view_dis_romaneio_industria

-- DROP VIEW public.view_dis_romaneio_industria;

CREATE OR REPLACE VIEW public.view_dis_romaneio_industria AS


   SELECT filial,
		  dt_prev_entr,
          orcamento,
		  sequencia,
		  cliente,
		  nome_cli,
		  vendedor,
		  nome_ven,
		  faturado,
		  endereco_entrega,
		  uf,
		  cidade,
		  bairro,
		  produto,
		  descricao,
		  unidade,
		  tipo,
		  qtd_venda,
		  SUM(qtd_romaneio) AS qtd_romaneio,
		  SUM(qtd_entregue) AS qtd_entregue,
		  (qtd_venda - SUM(qtd_romaneio)) AS qtd_disponivel

      FROM (


   SELECT vnd_orcamento.filial_orc AS filial,
          vnd_orcamento.orcamento,
          vnd_orcamento_item.sequencia,
          vnd_orcamento.cliente,
		  cli_cliente.nome AS nome_cli,
          vnd_orcamento.vendedor,
		  bas_vendedor.nome AS nome_ven,
          vnd_orcamento.gerou_nf_caixa AS faturado,
          vnd_orcamento.endereco_entrega,
		  cli_endereco.uf,
		  cli_endereco.cidade,
		  cli_endereco.bairro,
          vnd_orcamento.data_previsao_entrega AS dt_prev_entr,
          vnd_orcamento_item.produto,
		  pro_produto.descricao,
		  pro_produto.unidade,
		  pro_produto.produto_servico AS tipo,
          vnd_orcamento_item.quantidade AS qtd_venda,
		  COALESCE(CASE WHEN dis_romaneio.situacao = 'A' THEN TRUNC(COALESCE(dis_romaneio_item.qtd_total,0.000000),6)
		       WHEN dis_romaneio.situacao = 'B' THEN TRUNC(COALESCE(dis_romaneio_item.qtd_entregue,0.000000),6)
		   END, 0.000000) AS qtd_romaneio,
		  TRUNC(COALESCE(dis_romaneio_item.qtd_entregue,0.000000),6) AS qtd_entregue
		   
     FROM vnd_orcamento
	 
LEFT JOIN vnd_orcamento_item
       ON vnd_orcamento_item.filial_orc = vnd_orcamento.filial_orc
      AND vnd_orcamento_item.orcamento  = vnd_orcamento.orcamento

LEFT JOIN dis_romaneio_item
       ON dis_romaneio_item.filial              = vnd_orcamento_item.filial_orc
      AND dis_romaneio_item.orcamento           = vnd_orcamento_item.orcamento
      AND dis_romaneio_item.seq_item_orcamento  = vnd_orcamento_item.sequencia

LEFT JOIN dis_romaneio
       ON dis_romaneio.filial   = dis_romaneio_item.filial
      AND dis_romaneio.romaneio = dis_romaneio_item.romaneio
		  
LEFT JOIN pro_produto 
       ON pro_produto.filial_pro = vnd_orcamento_item.filial_pro 
	  AND pro_produto.codigo     = vnd_orcamento_item.produto

LEFT JOIN cli_cliente 
       ON cli_cliente.filial = vnd_orcamento.filial_cli 
	  AND cli_cliente.codigo = vnd_orcamento.cliente

LEFT JOIN bas_vendedor 
       ON bas_vendedor.filial = vnd_orcamento.filial_ven
	  AND bas_vendedor.codigo = vnd_orcamento.vendedor
	  
LEFT JOIN cli_endereco 
       ON cli_endereco.filial    = vnd_orcamento.filial_cli 
	  AND cli_endereco.cliente   = vnd_orcamento.cliente 
	  AND cli_endereco.sequencia = vnd_orcamento.endereco_entrega

    WHERE vnd_orcamento.situacao <> 'C'
      AND vnd_orcamento.tipo     <> 'O'
	  AND vnd_orcamento.venda_sn = 'S'
	  AND vnd_orcamento.liberado = 'S'

	  AND (dis_romaneio_item.orcamento IS NULL 
		   OR ((dis_romaneio_item.qtd_total - dis_romaneio_item.qtd_entregUe) > 0)
		   OR (dis_romaneio.situacao  <> 'C')
		  )

) AS tabaux

GROUP BY filial,
		  dt_prev_entr,
          orcamento,
		  sequencia,
		  cliente,
		  nome_cli,
		  vendedor,
		  nome_ven,
		  faturado,
		  endereco_entrega,
		  uf,
		  cidade,
		  bairro,
		  produto,
		  descricao,
		  unidade,
		  tipo,
		  qtd_venda

ORDER BY filial,
         dt_prev_entr,
		 cliente,
		 orcamento,
		 sequencia;




ALTER TABLE public.view_dis_romaneio_industria OWNER TO postgres;
COMMENT ON VIEW public.view_dis_romaneio_industria IS 'View para Montar Romaneio da Industria';





--#########################################################################################################################################
--#########################################################################################################################################
--#########################################################################################################################################
--#########################################################################################################################################
--#########################################################################################################################################






-- Table: public.dis_aux_romaneio_industria

-- DROP TABLE public.dis_aux_romaneio_industria;

CREATE TABLE public.dis_aux_romaneio_industria
(
    terminal_ip character varying(15) COLLATE pg_catalog."default" NOT NULL,
    filial integer NOT NULL,
    orcamento integer NOT NULL,
    sequencia integer NOT NULL,
    qtd_distribuicao numeric(18,6) NOT NULL,
    qtd_digitado numeric(18,6) NOT NULL,
    CONSTRAINT pk_dis_aux_romaneio_industria PRIMARY KEY (terminal_ip, filial, orcamento, sequencia)
)
WITH (
    OIDS = FALSE 
)
TABLESPACE pg_default;

ALTER TABLE public.dis_aux_romaneio_industria OWNER to postgres;








--#########################################################################################################################################
--#########################################################################################################################################
--#########################################################################################################################################
--#########################################################################################################################################
--#########################################################################################################################################








INSERT INTO ctl_aplicativo 
SELECT 'cns_dis_romaneio_industria_seleciona', 'Seleciona Pedido para Montar Romaneio - Industria', 'DIS', 'C'
FROM ctl_aplicativo
WHERE aplinome = 'frm_bas_filial'
AND (SELECT aplinome FROM ctl_aplicativo WHERE aplinome = 'cns_dis_romaneio_industria_seleciona') IS NULL LIMIT 1;

INSERT INTO ctl_aplicativo 
SELECT 'frm_dis_romaneio_industria_grid', 'Grid de Manutencao Romaneio - Industria', 'DIS', 'C'
FROM ctl_aplicativo
WHERE aplinome = 'frm_bas_filial'
AND (SELECT aplinome FROM ctl_aplicativo WHERE aplinome = 'frm_dis_romaneio_industria_grid') IS NULL LIMIT 1;

INSERT INTO ctl_aplicativo 
SELECT 'frm_dis_romaneio_industria_gera', 'Gera Romaneio - Industria', 'DIS', 'C'
FROM ctl_aplicativo
WHERE aplinome = 'frm_bas_filial'
AND (SELECT aplinome FROM ctl_aplicativo WHERE aplinome = 'frm_dis_romaneio_industria_gera') IS NULL LIMIT 1;



INSERT INTO ctl_grupo_x_direito  (
SELECT grupousucod, 'cns_dis_romaneio_industria_seleciona', grupodireito
  FROM ctl_grupo_x_direito 
 WHERE grupousucod  = 1
   AND grupousucod NOT IN(SELECT grupousucod FROM ctl_grupo_x_direito  WHERE aplinome = 'cns_dis_romaneio_industria_seleciona')
GROUP BY grupousucod, grupodireito);

INSERT INTO ctl_grupo_x_direito  (
SELECT grupousucod, 'frm_dis_romaneio_industria_grid', grupodireito
  FROM ctl_grupo_x_direito 
 WHERE grupousucod  = 1
   AND grupousucod NOT IN(SELECT grupousucod FROM ctl_grupo_x_direito  WHERE aplinome = 'frm_dis_romaneio_industria_grid')
GROUP BY grupousucod, grupodireito);

INSERT INTO ctl_grupo_x_direito  (
SELECT grupousucod, 'frm_dis_romaneio_industria_gera', grupodireito
  FROM ctl_grupo_x_direito 
 WHERE grupousucod  = 1
   AND grupousucod NOT IN(SELECT grupousucod FROM ctl_grupo_x_direito  WHERE aplinome = 'frm_dis_romaneio_industria_gera')
GROUP BY grupousucod, grupodireito);




DELETE FROM ctl_menu_nivel_dependencia 
 WHERE aplicacao = 'cns_dis_romaneio_industria_seleciona';
INSERT INTO ctl_menu_nivel_dependencia VALUES('cns_dis_romaneio_industria_seleciona', 'cns_dis_romaneio_industria_seleciona');
INSERT INTO ctl_menu_nivel_dependencia VALUES('cns_dis_romaneio_industria_seleciona', 'frm_dis_romaneio_industria_grid');
INSERT INTO ctl_menu_nivel_dependencia VALUES('cns_dis_romaneio_industria_seleciona', 'frm_dis_romaneio_industria_gera');





DELETE FROM ctl_menu_nivel WHERE codigo = 5856;
INSERT INTO ctl_menu_nivel VALUES (5856, '01) Montar Entrega (Indústria)', 9999,'', '_self', 'cns_dis_romaneio_industria_seleciona', 5494, 1);