update conf set confrelease = '13304'; ------------------------------------------------------------------------------------------------ alter table cprpg add column cprpgbolimpresso bpchar(1) DEFAULT 'N'::bpchar null; ALTER TABLE cpr DROP COLUMN cprbolimpresso ; ------------------------------------------------------------------------------------------------ INSERT INTO public.privacao(privacaodes, privacaodefinicao) VALUES('PERMITIR_RECEBER_MENSAGEM_CONTAS_A_PAGAR', 'Permitir receber mensagem de títulos em aberto a pagar diário'); ------------------------------------------------------------------------------------------------ alter table dep_pro_enderecamento_itens add column depproendiqtde numeric(14, 4) null; alter table dep_pro_enderecamento_itens add column depproendipeso numeric(14, 4) null; alter table dep_pro_enderecamento_itens add column depproendimetro numeric(14, 4) null; ------------------------------------------------------------------------------------------------ -- sd.vw_cxmv fonte CREATE OR REPLACE VIEW sd.vw_cxmv AS SELECT cxmv.cxmvempparcod AS cxmv_emp_cod, cxmv.cxmvcod AS cxmv_cod, cxmv.cxmvdlanc AS cxmv_dtlan, cxmv.cxmv_d_conc AS cxmv_dtconc, round(cxmv.cxmvv, 2) AS cxmv_vl, round( CASE WHEN cxmv.cxmvtipo::text = 'C'::text THEN cxmv.cxmvv ELSE - cxmv.cxmvv END, 2) AS cxmv_vlnat, CASE WHEN cxmv.cxmvtipo = 'C'::bpchar THEN 'CREDITO'::text ELSE 'DEBITO'::text END AS cxmv_natureza, cxmv.cxmvcxcod AS cxmv_cx_cod, cx.cxdes AS cxmv_cx_des, CASE WHEN cx.cxbancod IS NULL THEN 'CAIXA'::text ELSE 'BANCO'::text END AS cxmv_classif, conta.conta_id AS cxmv_conta_cod, conta.conta_descricao AS cxmv_conta_des, conta.contaclassif AS cxmv_conta_classif, grupo_conta.gc_id AS cxmv_grupoconta_cod, grupo_conta.gc_descricao AS cxmv_grupoconta_des, (COALESCE(doc.docobs, ''::bpchar::character varying::bpchar)::text || ' '::text) || COALESCE(replace(cxmv.cxmvobs, 'Movimento lançado referente à baixa'::text, ''::text), ''::text) AS cxmv_obs, cxmv.cxmvfunparcod AS cxmv_fun_cod, fun.pardes AS cxmv_fun_des, fun.parfan AS cxmv_fun_fan, cxmv.cxmvtccod AS cxmv_tc_cod, tc.tcdes AS cxmv_tc_des, tc.tctdoc AS cxmv_tc_tdoc, cxmv.cxmvdoccod AS cxmv_doc_cod, cxmv.cxmv_cheq_id_transf AS cxmv_cheq_cod, cheque.cheq_numero AS cxmv_cheqnum, cheque.cheq_valor AS cxmv_cheqvl, cheque.cheq_ban_id AS cxmv_cheq_ban_cod, cheque.cheq_agencia AS cxmv_cheq_agencia, cheque.cheq_ccorr AS cxmv_cheq_contacorr, cheque.cheq_emitente AS cxmv_cheq_emit, cheque.cheq_nominal AS cxmv_cheq_nominal, cxmv.cxmvnumdoc AS cxmv_numdoc, dev.parcod AS cxmv_par_cod, dev.pardes AS cxmv_par_des, dev.parfan AS cxmv_par_fan, CASE WHEN conf.conffantasiarazao = 'F'::bpchar THEN dev.parfan ELSE dev.pardes END AS cxmv_dev_despadrao, tcxmv.tcxmvcod AS cxmv_tcxmv_cod, tcxmv.tcxmvdes AS cxmv_tcxmv_desc, cxmv.cxmvtipo AS cxmv_tipo, cc.n2_cc_id AS cxmv_cc_cod, cc.n2_cc_descricao AS cxmv_cc_desc, doc.docparc AS cxmv_parc, cxmv.cxmvcheckdt AS cxmv_checkdt, cxmv.cxmvcheckusuparcod AS cxmv_check_par_cod, cxmv.cxmvsta, cx.cx_conta_contabil AS cxmv_cx_conta_contabil, COALESCE(( SELECT cli.clicodhistcont FROM cli WHERE cli.cliparcod = dev.parcod), ( SELECT forn.forcodhistcont FROM forn WHERE forn.forparcod = dev.parcod)) AS cxmv_dexion_hist_padrao, conta.conta_codigo AS cxmv_conta_codigo, doc.docdvenc AS cxmv_dvenc, doc.docdemi AS cxmv_demi, conta.conta_conta_contabil AS cxmv_conta_contabil FROM cxmv JOIN cx ON cxmv.cxmvcxcod = cx.cxcod JOIN par fun ON cxmv.cxmvfunparcod = fun.parcod LEFT JOIN conta ON cxmv.cxmv_conta_id = conta.conta_id LEFT JOIN grupo_conta ON conta.conta_gc_id = grupo_conta.gc_id LEFT JOIN ( SELECT n2.cc_id AS n2_cc_id, n0.cc_codigo AS n0_cc_codigo, n0.cc_descricao AS n0_cc_descricao, n1.cc_codigo AS n1_cc_codigo, n1.cc_descricao AS n1_cc_descricao, n2.cc_codigo AS n2_cc_codigo, n2.cc_descricao AS n2_cc_descricao FROM centro_custo n2 JOIN centro_custo n1 ON n2.cc_pai_id = n1.cc_id JOIN centro_custo n0 ON n1.cc_pai_id = n0.cc_id WHERE n2.cc_nivel = 2) cc ON cxmv.cxmv_cc_id = cc.n2_cc_id LEFT JOIN tc ON cxmv.cxmvtccod = tc.tccod LEFT JOIN doc ON cxmv.cxmvdoccod = doc.doccod AND cxmv.cxmvempparcod = doc.docempparcod LEFT JOIN par dev ON CASE WHEN cxmv.cxmvparcod IS NULL THEN dev.parcod = doc.docdevparcod ELSE dev.parcod = cxmv.cxmvparcod END LEFT JOIN cheque ON cxmv.cxmv_cheq_id_transf = cheque.cheq_id LEFT JOIN conf ON cxmv.cxmvempparcod = conf.confempparcod LEFT JOIN tcxmv ON cxmv.cxmvtcxmvcod = tcxmv.tcxmvcod WHERE cxmv.cxmvsta = 'B'::bpchar; -- Permissions ALTER TABLE sd.vw_cxmv OWNER TO postgres; GRANT ALL ON TABLE sd.vw_cxmv TO postgres; GRANT SELECT ON TABLE sd.vw_cxmv TO consulta; ------------------------------------------------------------------------------------------------ -- sd.vw_cxmv_dados fonte CREATE OR REPLACE VIEW sd.vw_cxmv_dados AS SELECT 1 AS cliente_id, date(now()) AS posicao, emp.parcod AS filial_id, emp.pardes AS filial, emp.parfan AS fantasiafilial, 'CAIXA'::text AS tipo, vw_cxmv.cxmv_cod AS id, vw_cxmv.cxmv_dtlan AS dtlan, vw_cxmv.cxmv_dtconc AS dtconc, vw_cxmv.cxmv_natureza AS natureza, vw_cxmv.cxmv_vl AS vl, vw_cxmv.cxmv_vlnat AS vlnat, vw_cxmv.cxmv_cx_cod AS caixa_id, vw_cxmv.cxmv_cx_des AS caixa, vw_cxmv.cxmv_grupoconta_cod AS grupoconta_id, vw_cxmv.cxmv_grupoconta_des AS grupoconta, vw_cxmv.cxmv_conta_cod AS conta_id, vw_cxmv.cxmv_conta_des AS conta, vw_cxmv.cxmv_conta_classif AS classifconta, vw_cxmv.cxmv_tc_cod AS cobranca_id, vw_cxmv.cxmv_tc_des AS cobranca, vw_cxmv.cxmv_tc_tdoc AS tipocobranca, vw_cxmv.cxmv_par_cod AS parceiro_id, vw_cxmv.cxmv_par_des AS parceiro, vw_cxmv.cxmv_par_fan AS fantasiaparceira, vw_cxmv.cxmv_parc AS parc, vw_cxmv.cxmv_numdoc AS numdocumento, vw_cxmv.cxmv_cc_cod AS cc_id, vw_cxmv.cxmv_cc_desc AS cc, vw_cxmv.cxmv_obs AS observacao, vw_cxmv.cxmv_fun_cod AS funcionario_id, vw_cxmv.cxmv_fun_des AS funcionario, vw_cxmv.cxmv_tcxmv_desc AS tipo_movimento, vw_cxmv.cxmv_checkdt AS checkdt, vw_cxmv.cxmv_check_par_cod AS chack_funcionario_id, vw_cxmv.cxmv_conta_codigo AS conta_codigo, vw_cxmv.cxmv_dvenc AS conta_dvenc, vw_cxmv.cxmv_demi AS conta_dtemi, vw_cxmv.cxmv_conta_contabil AS conta_contabil FROM sd.vw_cxmv JOIN par emp ON vw_cxmv.cxmv_emp_cod = emp.parcod UNION SELECT 1 AS cliente_id, date(now()) AS posicao, vw_creceb.creceb_emp_cod AS filial_id, emp.pardes AS filial, emp.parfan AS fantasiafilial, 'DOC'::text AS tipo, vw_creceb.creceb_cod AS id, vw_creceb.creceb_dtlan AS dtlan, vw_creceb.creceb_dtconc::date + 1 AS dtconc, 'CREDITO'::text AS natureza, vw_creceb.creceb_vl AS vl, vw_creceb.creceb_vl AS vlnat, 0 AS caixa_id, 'SEM CAIXA'::character varying AS caixa, grupo_conta.gc_id AS grupoconta_id, grupo_conta.gc_descricao AS grupoconta, vw_creceb.creceb_conta_cod AS conta_id, conta.conta_descricao AS conta, conta.contaclassif AS classifconta, vw_creceb.creceb_tc_cod AS cobranca_id, tc.tcdes AS cobranca, tc.tctdoc AS tipocobranca, vw_creceb.creceb_dev_cod AS parceiro_id, cli.pardes AS parceiro, cli.parfan AS fantasiaparceira, vw_creceb.creceb_parc AS parc, vw_creceb.creceb_docnum AS numdocumento, vw_creceb.creceb_grupoconta_cod AS cc_id, centro_custo.cc_descricao AS cc, vw_creceb.creceb_obs AS observacao, vw_creceb.creceb_fun_cod AS funcionario_id, fun.pardes AS funcionario, fun.parfan AS tipo_movimento, NULL::timestamp without time zone AS checkdt, NULL::integer AS chack_funcionario_id, conta.conta_codigo, vw_creceb.creceb_dtvenc AS conta_dvenc, vw_creceb.creceb_dtemi AS conta_dtemi, conta.conta_conta_contabil AS conta_contabil FROM sd.vw_creceb JOIN par emp ON vw_creceb.creceb_emp_cod = emp.parcod JOIN par cli ON vw_creceb.creceb_dev_cod = cli.parcod JOIN par fun ON vw_creceb.creceb_fun_cod = fun.parcod JOIN conta ON vw_creceb.creceb_conta_cod = conta.conta_id JOIN grupo_conta ON conta.conta_gc_id = grupo_conta.gc_id JOIN centro_custo ON vw_creceb.creceb_grupoconta_cod = centro_custo.cc_id JOIN tc ON vw_creceb.creceb_tc_cod = tc.tccod WHERE vw_creceb.creceb_bai_dt IS NULL AND vw_creceb.creceb_antecipdt IS NULL UNION SELECT 1 AS cliente_id, date(now()) AS posicao, vw_cpag.cpag_emp_cod AS filial_id, emp.pardes AS filial, emp.parfan AS fantasiafilial, 'DOC'::text AS tipo, vw_cpag.cpag_cod AS id, vw_cpag.cpag_dtlan AS dtlan, vw_cpag.cpag_dtvenc AS dtconc, 'DEBITO'::text AS natureza, vw_cpag.cpag_vl AS vl, vw_cpag.cpag_vl * '-1'::integer::numeric AS vlnat, 0 AS caixa_id, 'SEM CAIXA'::character varying AS caixa, grupo_conta.gc_id AS grupoconta_id, grupo_conta.gc_descricao AS grupoconta, vw_cpag.cpag_conta_cod AS conta_id, conta.conta_descricao AS conta, conta.contaclassif AS classifconta, vw_cpag.cpag_tc_cod AS cobranca_id, tc.tcdes AS cobranca, tc.tctdoc AS tipocobranca, vw_cpag.cpag_dev_cod AS parceiro_id, cli.pardes AS parceiro, cli.parfan AS fantasiaparceira, vw_cpag.cpag_parc AS parc, vw_cpag.cpag_docnum AS numdocumento, vw_cpag.cpag_grupoconta_cod AS cc_id, centro_custo.cc_descricao AS cc, vw_cpag.cpag_obs AS observacao, vw_cpag.cpag_fun_cod AS funcionario_id, fun.pardes AS funcionario, fun.parfan AS tipo_movimento, NULL::timestamp without time zone AS checkdt, NULL::integer AS chack_funcionario_id, conta.conta_codigo, vw_cpag.cpag_dtvenc AS conta_dvenc, vw_cpag.cpag_dtemi AS conta_dtemi, conta.conta_conta_contabil AS conta_contabil FROM sd.vw_cpag JOIN par emp ON vw_cpag.cpag_emp_cod = emp.parcod JOIN par cli ON vw_cpag.cpag_dev_cod = cli.parcod JOIN par fun ON vw_cpag.cpag_fun_cod = fun.parcod JOIN conta ON vw_cpag.cpag_conta_cod = conta.conta_id JOIN grupo_conta ON conta.conta_gc_id = grupo_conta.gc_id JOIN centro_custo ON vw_cpag.cpag_grupoconta_cod = centro_custo.cc_id JOIN tc ON vw_cpag.cpag_tc_cod = tc.tccod WHERE vw_cpag.cpag_bai_dt IS NULL; -- Permissions ALTER TABLE sd.vw_cxmv_dados OWNER TO postgres; GRANT ALL ON TABLE sd.vw_cxmv_dados TO postgres; GRANT SELECT ON TABLE sd.vw_cxmv_dados TO consulta;