update conf set confrelease = '13199'; ------------------------------------------------------------------------------------------------ INSERT INTO public.operacaogerimpr (opgerimprcodigo, opgerimprdescricao, opgerimprsigla) VALUES(nextval('operacaogerimpr_opgerimprcodigo_seq'::regclass), 'RELATORIO RECIBO CONTAS A PAGAR', 'RECIBO_CONTAS_PAGAR'); INSERT INTO public.operacaogerimpr (opgerimprcodigo, opgerimprdescricao, opgerimprsigla) VALUES(nextval('operacaogerimpr_opgerimprcodigo_seq'::regclass), 'RELATORIO RECIBO CONTAS A RECEBER', 'RECIBO_CONTAS_RECEBER'); ------------------------------------------------------------------------------------------------ -- DROP FUNCTION public.f_retornar_st(int4, int4, int4, numeric); CREATE OR REPLACE FUNCTION public.f_retornar_st(integer, integer, integer, numeric) RETURNS TABLE(frrstprocod integer, frrstvlst numeric, frrstaliqst numeric) LANGUAGE plpgsql AS $function$ DECLARE /* 16/06/2020 - DAVID: Criação da função f_retornar_st 23/06/2020 - DAVID: correção da função para não utilziar o pedido 22/09/2023 - David: Adiciona o valor do FCP ST no valor da ST 16/01/2023 - David: Correção do calculo da pauta 04/03/2024 - David: Adicionar o valor de FCP-ST 05/04/2024 - David: Correção do gtisento -- select * from f_retornar_st(3,7,2311,46.67) */ p_empresa ALIAS FOR $1; p_livro ALIAS FOR $2; p_produto ALIAS FOR $3; p_valor ALIAS for $4; rec_pv RECORD; v_visento NUMERIC(14,4); v_vbasecalc NUMERIC(14,4); v_vicms NUMERIC(14,4); v_voutros NUMERIC(14,4); v_vipi NUMERIC(14,4); v_vbasecalcST NUMERIC(14,4); v_vlicmsST NUMERIC(14,4); v_vbasecalc_efet NUMERIC(14,4); v_vicms_efet NUMERIC(14,4); v_vlfcp NUMERIC(14,4); v_vbcfcp NUMERIC(14,4); v_vbcfcpST NUMERIC(14,4); v_vlfcpST NUMERIC(14,4); v_VERSAO TEXT; BEGIN --************************************************************* -- VERSAO DA FUNCAO RETORNA ST --************************************************************* v_VERSAO = 'v2024.03.04.1'; FOR rec_pv IN select procod, gtcod, gtdes, coalesce(estvlpautaicms,0) as pauta, coalesce(gtpercredbc,0) as percrbcicms, coalesce(gtaliqicms,0) as aliqicms, coalesce(gtaliqefet,0) as aliqicmsefet, gtisento as isento, --IPI coalesce(estaliqipi,0) as percipi, --ST coalesce(estvlpautast,0) as pautaret, coalesce(gtstaliqicms,0) as aliqicmsret, coalesce(gtstpercredbc,0) as percrbcicmsret, coalesce(gtstiva,0) as aliqiva, gtstmodalidbcicms as modbcicmsret, --FCP case when coalesce(gtfcpaliqicms,0) > 0 then gtfcpaliqicms else 0 end as fcpaliqicms, case when coalesce(gtfcpaliqicms,0) > 0 then ((p_valor*gtfcpaliqicms)/100) else 0 end as fcpvlqicms, --FCP ST case when coalesce(gtfcpaliqicmsst,0) > 0 then gtfcpaliqicmsst else 0 end as fcpaliqicmsst, case when coalesce(gtfcpaliqicmsst,0) > 0 then ((p_valor*gtfcpaliqicmsst)/100) else 0 end as fcpvlqicmsst from pro join est on procod=estprocod join uve on uveprocod = procod left join emp on estempparcod=empparcod left join regtrib on empregtribcod=regtribcod --trib por produto left join tribpro on regtribcod=tribproregtribcod and tribprouforig=(select munufsigla from par join mun on parmuncod=muncod where parcod=estempparcod) and tribproufdest=(select lpuf from lp where lpcod=p_livro) and tribproprocod=procod --trib por ncm left join trib on regtribcod=tribregtribcod and tribuforig=(select munufsigla from par join mun on parmuncod=muncod where parcod=estempparcod) and tribufdest=(select lpuf from lp where lpcod=p_livro) and tribncm=proncm left join gt on gtcod=coalesce(tribprogtcod,tribgtcod) where estempparcod = p_empresa and procod = p_produto LOOP --ICMS NORMAL v_visento:=0; v_vbasecalc:=0; v_vicms:=0; v_voutros:=0; v_vipi:=0; v_vbasecalc_efet:=0; v_vicms_efet:=0; v_vbcfcp := 0; v_vbcfcpST:=0; v_vlfcp:=0; v_vlfcpST:=0; IF rec_pv.isento = 'S' THEN v_visento := p_valor; ELSE --Obter base de calculo IF rec_pv.pauta > 0 THEN v_vbasecalc := 1*rec_pv.pauta; ELSE IF rec_pv.percrbcicms > 0 THEN v_vbasecalc := round(p_valor*rec_pv.percrbcicms/100,2); ELSE --Compor base de calculo se aliq icms > 0 IF rec_pv.aliqicms > 0 THEN v_vbasecalc := p_valor; ELSE v_vbasecalc := 0; END IF; END IF; END IF; -- VBaseCalc recebe despesa de boleto no primeiro item caso configurado no cliente v_vbasecalc := coalesce(v_vbasecalc,0); v_vicms := round(v_vbasecalc*coalesce(rec_pv.aliqicms,0)/100,2); v_voutros := p_valor-v_vbasecalc; v_vbasecalc_efet := coalesce(v_vbasecalc,0); v_vicms_efet := round(v_vbasecalc*coalesce(rec_pv.aliqicmsefet,0)/100,2); END IF; --SUBSTITUICAO v_vbasecalcST := 0; v_vlicmsST := 0; v_vipi := round(p_valor*coalesce(rec_pv.percipi,0)/100,2); IF rec_pv.aliqicmsret > 0 THEN IF rec_pv.pautaret > 0 THEN v_vbasecalcST := rec_pv.pautaret; ELSE IF rec_pv.percrbcicmsret > 0 THEN --v_vbasecalcST := round((rec_pv.pvivltotal+v_vipi+frete)*rec_pv.percrbcicmsret/100,4); v_vbasecalcST := round((p_valor+v_vipi)*rec_pv.percrbcicmsret/100,2); ELSE --v_vbasecalcST := rec_pv.pvivltotal+v_vipi+frete; v_vbasecalcST := p_valor+v_vipi; END IF; END IF; v_vbasecalcST := round(v_vbasecalcST*(1+(coalesce(rec_pv.aliqiva,0)/100)),2); v_vlicmsST := round((v_vbasecalcST*rec_pv.aliqicmsret/100)-coalesce(v_vicms,0),2); END IF; --case when coalesce(gtfcpaliqicms,0) > 0 then ((pvivltotal*gtfcpaliqicms)/100) else 0 end as fcpvlqicms, IF rec_pv.fcpaliqicmsst > 0 then v_vlfcp:= ((p_valor*rec_pv.fcpaliqicms)/100); v_vbcfcpST := v_vbasecalcST; v_vlfcpST := round((v_vbcfcpST*rec_pv.fcpaliqicmsst/100),2); v_vlfcpST := round(v_vlfcpST-coalesce(rec_pv.fcpvlqicms,0),2); v_vbasecalcST := round(v_vbasecalcST+v_vbcfcpST,2); v_vlicmsST := round(v_vlicmsST+v_vlfcpST,2); end if; END LOOP; IF v_vlicmsST>0 THEN RETURN QUERY SELECT rec_pv.procod::integer as frrstprocod, v_vlicmsST::numeric as frrstvlst, rec_pv.aliqicmsret as frrstaliqst; ELSE RETURN QUERY SELECT 0::integer as frrstprocod, 0::numeric as frrstvlst, 0::numeric as frrstaliqst; END IF; RETURN; END; $function$ ; -- Permissions ALTER FUNCTION public.f_retornar_st(int4, int4, int4, numeric) OWNER TO postgres; GRANT ALL ON FUNCTION public.f_retornar_st(int4, int4, int4, numeric) TO public; GRANT ALL ON FUNCTION public.f_retornar_st(int4, int4, int4, numeric) TO postgres; -------------------------------------------------------------------------------------------------------- -- DROP FUNCTION public.f_retornar_st(int4, int4, int4, numeric, numeric); CREATE OR REPLACE FUNCTION public.f_retornar_st(integer, integer, integer, numeric, numeric) RETURNS TABLE(frrstprocod integer, frrstvlst numeric, frrstaliqst numeric) LANGUAGE plpgsql AS $function$ DECLARE /* 16/06/2020 - DAVID: Criação da função f_retornar_st 23/06/2020 - DAVID: correção da função para não utilziar o pedido 22/09/2023 - David: Adiciona o valor do FCP ST no valor da ST 16/01/2023 - David: Correção do calculo da pauta 04/03/2024 - David: Adicionar o valor de FCP-ST -- select * from f_retornar_st(3,5,2014,421.30,72) */ p_empresa ALIAS FOR $1; p_livro ALIAS FOR $2; p_produto ALIAS FOR $3; p_valor ALIAS for $4; p_qtde ALIAS FOR $5; rec_pv RECORD; v_visento NUMERIC(14,4); v_vbasecalc NUMERIC(14,4); v_vicms NUMERIC(14,4); v_voutros NUMERIC(14,4); v_vipi NUMERIC(14,4); v_vbasecalcST NUMERIC(14,4); v_vlicmsST NUMERIC(14,4); v_vbasecalc_efet NUMERIC(14,4); v_vicms_efet NUMERIC(14,4); v_vlfcp NUMERIC(14,4); v_vbcfcp NUMERIC(14,4); v_vbcfcpST NUMERIC(14,4); v_vlfcpST NUMERIC(14,4); v_VERSAO TEXT; BEGIN --************************************************************* -- VERSAO DA FUNCAO RETORNA ST --************************************************************* v_VERSAO = 'v2024.03.04.1'; FOR rec_pv IN select procod, gtcod, gtdes, coalesce(estvlpautaicms,0) as pauta, coalesce(gtpercredbc,0) as percrbcicms, coalesce(gtaliqicms,0) as aliqicms, coalesce(gtaliqefet,0) as aliqicmsefet, gtisento as isento, --IPI coalesce(estaliqipi,0) as percipi, --ST coalesce(estvlpautast,0) as pautaret, coalesce(gtstaliqicms,0) as aliqicmsret, coalesce(gtstpercredbc,0) as percrbcicmsret, coalesce(gtstiva,0) as aliqiva, gtstmodalidbcicms as modbcicmsret, --FCP case when coalesce(gtfcpaliqicms,0) > 0 then gtfcpaliqicms else 0 end as fcpaliqicms, case when coalesce(gtfcpaliqicms,0) > 0 then ((p_valor*gtfcpaliqicms)/100) else 0 end as fcpvlqicms, --FCP ST case when coalesce(gtfcpaliqicmsst,0) > 0 then gtfcpaliqicmsst else 0 end as fcpaliqicmsst, case when coalesce(gtfcpaliqicmsst,0) > 0 then ((p_valor*gtfcpaliqicmsst)/100) else 0 end as fcpvlqicmsst from pro join est on procod=estprocod join uve on uveprocod = procod left join emp on estempparcod=empparcod left join regtrib on empregtribcod=regtribcod --trib por produto left join tribpro on regtribcod=tribproregtribcod and tribprouforig=(select munufsigla from par join mun on parmuncod=muncod where parcod=estempparcod) and tribproufdest=(select lpuf from lp where lpcod=p_livro) and tribproprocod=procod --trib por ncm left join trib on regtribcod=tribregtribcod and tribuforig=(select munufsigla from par join mun on parmuncod=muncod where parcod=estempparcod) and tribufdest=(select lpuf from lp where lpcod=p_livro) and tribncm=proncm left join gt on gtcod=coalesce(tribprogtcod,tribgtcod) where estempparcod = p_empresa and procod = p_produto LOOP --ICMS NORMAL v_visento:=0; v_vbasecalc:=0; v_vicms:=0; v_voutros:=0; v_vipi:=0; v_vbasecalc_efet:=0; v_vicms_efet:=0; v_vbcfcp := 0; v_vbcfcpST:=0; v_vlfcp:=0; v_vlfcpST:=0; IF rec_pv.isento = 'S' THEN v_visento := p_valor; ELSE --Obter base de calculo IF rec_pv.pauta > 0 THEN v_vbasecalc := p_qtde*rec_pv.pauta; ELSE IF rec_pv.percrbcicms > 0 THEN v_vbasecalc := round(p_valor*rec_pv.percrbcicms/100,2); ELSE --Compor base de calculo se aliq icms > 0 IF rec_pv.aliqicms > 0 THEN v_vbasecalc := p_valor; ELSE v_vbasecalc := 0; END IF; END IF; END IF; -- VBaseCalc recebe despesa de boleto no primeiro item caso configurado no cliente v_vbasecalc := coalesce(v_vbasecalc,0); v_vicms := round(v_vbasecalc*coalesce(rec_pv.aliqicms,0)/100,2); v_voutros := p_valor-v_vbasecalc; v_vbasecalc_efet := coalesce(v_vbasecalc,0); v_vicms_efet := round(v_vbasecalc*coalesce(rec_pv.aliqicmsefet,0)/100,2); END IF; --SUBSTITUICAO v_vbasecalcST := 0; v_vlicmsST := 0; v_vipi := round(p_valor*coalesce(rec_pv.percipi,0)/100,2); IF rec_pv.aliqicmsret > 0 THEN IF rec_pv.pautaret > 0 THEN v_vbasecalcST := p_qtde*rec_pv.pautaret; ELSE IF rec_pv.percrbcicmsret > 0 THEN --v_vbasecalcST := round((rec_pv.pvivltotal+v_vipi+frete)*rec_pv.percrbcicmsret/100,4); v_vbasecalcST := round((p_valor+v_vipi)*rec_pv.percrbcicmsret/100,2); ELSE --v_vbasecalcST := rec_pv.pvivltotal+v_vipi+frete; v_vbasecalcST := p_valor+v_vipi; END IF; END IF; v_vbasecalcST := round(v_vbasecalcST*(1+(coalesce(rec_pv.aliqiva,0)/100)),2); v_vlicmsST := round((v_vbasecalcST*rec_pv.aliqicmsret/100)-coalesce(v_vicms,0),2); END IF; --case when coalesce(gtfcpaliqicms,0) > 0 then ((pvivltotal*gtfcpaliqicms)/100) else 0 end as fcpvlqicms, IF rec_pv.fcpaliqicmsst > 0 then v_vlfcp:= ((p_valor*rec_pv.fcpaliqicms)/100); v_vbcfcpST := v_vbasecalcST; v_vlfcpST := round((v_vbcfcpST*rec_pv.fcpaliqicmsst/100),2); v_vlfcpST := round(v_vlfcpST-coalesce(rec_pv.fcpvlqicms,0),2); v_vbasecalcST := round(v_vbasecalcST+v_vbcfcpST,2); v_vlicmsST := round(v_vlicmsST+v_vlfcpST,2); end if; END LOOP; IF v_vlicmsST>0 THEN RETURN QUERY SELECT rec_pv.procod::integer as frrstprocod, v_vlicmsST::numeric as frrstvlst, rec_pv.aliqicmsret as frrstaliqst; ELSE RETURN QUERY SELECT 0::integer as frrstprocod, 0::numeric as frrstvlst, 0::numeric as frrstaliqst; END IF; RETURN; END; $function$ ; -- Permissions ALTER FUNCTION public.f_retornar_st(int4, int4, int4, numeric, numeric) OWNER TO postgres; GRANT ALL ON FUNCTION public.f_retornar_st(int4, int4, int4, numeric, numeric) TO postgres; ------------------------------------------------------------------------------------------------ ALTER TABLE public.doc ADD docnsu bpchar(50) NULL; ------------------------------------------------------------------------------------------------ -- sd.vw_creceb source CREATE OR REPLACE VIEW sd.vw_creceb AS SELECT c.doccod AS creceb_cod, COALESCE(c.situacao, 'EFETIVADO'::character varying) AS creceb_situacao, c.docempparcod AS creceb_emp_cod, emp.pardes AS creceb_emp_des, emp.parfan AS creceb_emp_fan, c.doctipo AS creceb_tipo, CASE WHEN c.doctipo = 'CRC'::bpchar THEN 'CONTA REC CLIENTE'::text WHEN c.doctipo = 'CRO'::bpchar THEN 'CONTA REC OUTROS'::text WHEN c.doctipo = 'CRV'::bpchar THEN 'CONTA REC VALE'::text ELSE NULL::text END AS creceb_tipo_des, c.docsta AS creceb_sta, c.docdsta AS creceb_dtsta, fun.parcod AS creceb_fun_cod, fun.pardes AS creceb_fun_des, fun.parfan AS creceb_fun_fan, c.docdlan AS creceb_dtlan, c.docdemi AS creceb_dtemi, c.docdvenco AS creceb_dtvencorig, c.docdvenc AS creceb_dtvenc, date(c.docdpag) AS creceb_dtpag, c.docdbai AS creceb_dtbai, f_atraso(c.docdbai, c.docdvenc::timestamp without time zone, c.docdpag) AS creceb_atraso, round(( SELECT conf_1.conf_tx_juros FROM conf conf_1 WHERE conf_1.confempparcod = c.docempparcod), 2) AS creceb_txjuros, CASE WHEN c.docsta = 'BA'::bpchar THEN COALESCE(c.docmultab, 0::numeric) ELSE COALESCE(( SELECT f_calc_juros.fcalcjurosvlmora FROM f_calc_juros(c.docempparcod, c.docdvenc, c.docv, 'C'::bpchar, now()::date) f_calc_juros(fcalcjurosempresa, fcalcjurosdvenc, fcalcjurosvalor, fcalcjurosdpag, fcalcjurosvlmora, fcalcjurosvljuros, fcalcjurosvlmontante)), 0::numeric) END AS creceb_vlmulta, CASE WHEN c.docsta = 'BA'::bpchar THEN COALESCE(c.docv, 0::numeric) + COALESCE(c.docdesc, 0::numeric) - COALESCE(c.docjuros, 0::numeric) ELSE COALESCE(c.docv, 0::numeric) + COALESCE(c.docdesc, 0::numeric) + COALESCE(c.docdescb, 0::numeric) - COALESCE(c.docjuros, 0::numeric) - COALESCE(c.docjurosb, 0::numeric) END AS creceb_vlprinc, CASE WHEN c.docsta = 'BA'::bpchar THEN COALESCE(c.docv, 0::numeric) - COALESCE(c.docdesc, 0::numeric) + COALESCE(c.docjuros, 0::numeric) ELSE COALESCE(c.docv, 0::numeric) - COALESCE(c.docdesc, 0::numeric) - COALESCE(c.docdescb, 0::numeric) + COALESCE(c.docjuros, 0::numeric) + COALESCE(c.docjurosb, 0::numeric) END AS creceb_vlbol, CASE WHEN c.docsta = 'BA'::bpchar THEN c.docv - COALESCE(c.docdescb, 0::numeric) + COALESCE(c.docjurosb, 0::numeric) ELSE c.docv END AS creceb_vl, COALESCE(c.docdesc, 0::numeric) + COALESCE(c.docdescb, 0::numeric) AS creceb_vldesc, CASE WHEN c.docsta = 'BA'::bpchar THEN COALESCE(c.docjuros, 0::numeric) + COALESCE(c.docjurosb, 0::numeric) ELSE round(( SELECT f_calc_juros.fcalcjurosvljuros FROM f_calc_juros(c.docempparcod, c.docdvenc, c.docv, 'J'::bpchar, now()::date) f_calc_juros(fcalcjurosempresa, fcalcjurosdvenc, fcalcjurosvalor, fcalcjurosdpag, fcalcjurosvlmora, fcalcjurosvljuros, fcalcjurosvlmontante)), 4) + COALESCE(c.docjuros, 0::numeric) END AS creceb_vljuros, CASE WHEN c.docsta = 'BA'::bpchar THEN c.docv - COALESCE(c.docdescb, 0::numeric) + COALESCE(c.docjurosb, 0::numeric) ELSE COALESCE((( SELECT f_calc_juros.fcalcjurosvlmontante FROM f_calc_juros(c.docempparcod, c.docdvenc, c.docv, 'C'::bpchar, now()::date) f_calc_juros(fcalcjurosempresa, fcalcjurosdvenc, fcalcjurosvalor, fcalcjurosdpag, fcalcjurosvlmora, fcalcjurosvljuros, fcalcjurosvlmontante))) + (( SELECT f_calc_juros.fcalcjurosvlmora FROM f_calc_juros(c.docempparcod, c.docdvenc, c.docv, 'C'::bpchar, now()::date) f_calc_juros(fcalcjurosempresa, fcalcjurosdvenc, fcalcjurosvalor, fcalcjurosdpag, fcalcjurosvlmora, fcalcjurosvljuros, fcalcjurosvlmontante))) - COALESCE(c.docdesc, 0::numeric), 0::numeric) END AS creceb_vlatual, CASE WHEN c.docsta = 'BA'::bpchar THEN c.docv - COALESCE(c.docdescb, 0::numeric) + COALESCE(c.docjurosb, 0::numeric) ELSE 0.0 END::numeric(14,4) AS creceb_vlpag, c.doctccod AS creceb_tc_cod, tc.tcdes AS creceb_tc_des, tc.tctdoc AS creceb_tc_tdoc, tc.tcantecipavel AS creceb_tc_antecipavel, c.docdevparcod AS creceb_dev_cod, dev.pardes AS creceb_dev_des, dev.parfan AS creceb_dev_fan, dev.parcnpjcpf AS creceb_dev_cnpjcpf, CASE WHEN conf.conffantasiarazao = 'F'::bpchar THEN dev.parfan ELSE dev.pardes END AS creceb_dev_despadrao, c.doc_notafiscal AS creceb_nfnum, c.docparc AS creceb_parc, c.docnum AS creceb_docnum, c.docnn AS creceb_nossonum, c.docobs AS creceb_obs, c.docbancod AS creceb_cheq_ban_cod, ban.bandes AS creceb_ban_des, c.docag AS creceb_cheq_agencia, c.doccc AS creceb_cheq_contacorr, c.docrcaparcod AS creceb_rca_cod, ven.pardes AS creceb_rca_des, ven.parfan AS creceb_rca_fan, rca.rcasupparcod AS creceb_sup_cod, sup.pardes AS creceb_sup_des, sup.parfan AS creceb_sup_fan, c.doctdvchcod AS creceb_tdvch_cod, tdvch.tdvchdes AS creceb_tdvch_des, c.doc_conta_id AS creceb_conta_cod, conta.conta_descricao AS creceb_conta_des, grupo_conta.gc_id AS creceb_grupoconta_cod, grupo_conta.gc_descricao AS creceb_grupoconta_des, centro_custo.cc_id AS creceb_centrocusto_cod, centro_custo.cc_descricao AS creceb_centrocusto_des, c.doc_cart_codigo AS creceb_cart_cod, carteiras.cart_descricao AS creceb_cart_des, cli.cli_av_codigo AS creceb_av_cod, area_venda.av_descricao AS creceb_av_des, cli.cli_cg_codigo AS creceb_grupocli_cod, cliente_grupo.cg_descricao AS creceb_grupocli_des, cli.cliloccod AS creceb_loc_cod, loc.locdes AS creceb_loc_des, CASE WHEN c.docsta = 'BA'::bpchar THEN cxdono.cxcod ELSE NULL::integer END AS creceb_bai_cx_cod, CASE WHEN c.docsta = 'BA'::bpchar THEN cxdono.cxdes ELSE NULL::character varying END AS creceb_bai_cx_des, CASE WHEN c.docsta = 'BA'::bpchar THEN usudono.parcod ELSE NULL::integer END AS creceb_bai_usu_cod, CASE WHEN c.docsta = 'BA'::bpchar THEN usudono.parfan ELSE NULL::character varying END AS creceb_bai_usu_des, CASE WHEN c.docsta = 'BA'::bpchar THEN c.docdbai ELSE NULL::timestamp without time zone END AS creceb_bai_dt, c.doc_valor_protesto AS creceb_vlprotesto, c.doc_orgao_protesto AS creceb_orgaoprotesto, COALESCE(c.doctaxacartao, tc.tctaxacartao) AS creceb_taxacartao, clicart.clicartcod AS creceb_clicart_cod, clicart.clicartdes AS creceb_clicart_des, c.doccustoddt AS creceb_custoddt, c.docantecipdt AS creceb_antecipdt, c.doccheqdevdt AS creceb_cheqdevdt, c.docdepositdt AS creceb_depositdt, c.bancobcod AS creceb_tcban_cod, c.bancobdes AS creceb_tcban_des, c.doccarcod AS creceb_car_cod, c.doc_arq_remessa AS creceb_remessa, c.docavalistaparcod AS creceb_avalista, c.docrenparcod AS creceb_renparcod, c.docdtconc AS creceb_dtconc, CASE WHEN c.docsta = 'BA'::bpchar THEN COALESCE(c.docv, 0::numeric) - COALESCE(c.docdescb, 0::numeric) ELSE COALESCE(c.docv, 0::numeric) - COALESCE(c.docdesc, 0::numeric) - COALESCE(c.docdescb, 0::numeric) END AS creceb_vlcomdesc, CASE WHEN c.docsta = 'BA'::bpchar THEN COALESCE(c.docv, 0::numeric) ELSE COALESCE(c.docv, 0::numeric) END AS creceb_vloriginal, c.docnsu AS creceb_nsu FROM ( SELECT 'EFETIVADO'::character varying AS situacao, doc.doccod, doc.docempparcod, doc.docdevparcod, doc.docfunparcod, doc.doctccod, doc.docbancod, doc.docrcaparcod, doc.doctdvchcod, doc.doc_conta_id, doc.doc_cc_id, doc.doc_cart_codigo, doc.docdonocxcod, doc.docdonousuparcod, doc.doctipo, doc.docsta, doc.docdsta, doc.docdlan, doc.docdemi, doc.docdvenco, doc.docdvenc, doc.docdpag, doc.docdbai, doc.docv, doc.docdesc, doc.docjuros, doc.docdescb, doc.docjurosb, doc.doc_notafiscal, COALESCE(doc.docparc, '1'::character varying) AS docparc, doc.docnum, doc.docnn, doc.docobs, doc.docag, doc.doccc, doc.doc_valor_protesto, doc.doc_orgao_protesto, doc.doctaxacartao, doc.doccustoddt, doc.docantecipdt, doc.doccheqdevdt, doc.docdepositdt, ban_1.bancod AS bancobcod, ban_1.bandes AS bancobdes, doc.doccarcod, doc.doc_arq_remessa, doc.docavalistaparcod, doc.docrenparcod, CASE WHEN doc.doc_d_competencia IS NOT NULL THEN doc.doc_d_competencia::timestamp without time zone ELSE (doc.docdvenc + COALESCE(boleto.boldiasbaixa, 0))::timestamp without time zone END AS docdtconc, doc.docmultab, doc.docnsu FROM doc JOIN tc tc_1 ON doc.doctccod = tc_1.tccod LEFT JOIN ban ban_1 ON tc_1.tcban = ban_1.bancod LEFT JOIN tbe ON tbe.tbetccod = tc_1.tccod LEFT JOIN boleto ON tbe.tbebolcod = boleto.bolcod WHERE (doc.doctipo = ANY (ARRAY['CRC'::bpchar, 'CRO'::bpchar, 'CRV'::bpchar])) AND (doc.docsta <> ALL (ARRAY['EX'::bpchar, 'RN'::bpchar])) UNION SELECT 'TRANSITO'::character varying AS situacao, cpr.cprcod, cpr.cprempparcod, cpr.cprdevparcod, cpr.cprfunparcod, cprpg.cprpgtccod, cprpg.cprpgbancod, cpr.cprrcaparcod, cprpg.cprpgtdvchcod, cpr.cpr_conta_id, cpr.cpr_cc_id, cpr.cpr_cart_codigo, NULL::integer AS int4, NULL::integer AS int4, cpr.cprtipo, CASE WHEN cprpg.cprpgsta = 'A'::bpchar THEN 'LA'::text ELSE 'BA'::text END AS cprpgsta, cpr.cprdlan, cpr.cprdlan, cpr.cprdemi, cprpg.cprpgdvenc, cprpg.cprpgdvenc, NULL::timestamp without time zone AS "timestamp", NULL::timestamp without time zone AS "timestamp", cprpg.cprpgv, cprpg.cprpgdescv AS "numeric", NULL::numeric AS "numeric", NULL::numeric AS "numeric", NULL::numeric AS "numeric", cpr.cpr_notafiscal, COALESCE(cprpg.cprpgparc, '1'::character varying) AS cprpgparc, cprpg.cprpgnumdoc, cprpg.cprpgnn, cprpg.cprpgobs, cprpg.cprpgag, cprpg.cprpgcc, NULL::numeric AS "numeric", NULL::character varying AS "varchar", NULL::numeric AS "numeric", NULL::timestamp without time zone AS "timestamp", NULL::timestamp without time zone AS "timestamp", NULL::timestamp without time zone AS "timestamp", NULL::timestamp without time zone AS "timestamp", ban_1.bancod AS bancobcod, ban_1.bandes AS bancobdes, nf.nfcarcod, cprpg.cprpg_arq_remessa, 0 AS cpravalistaparcod, NULL::integer AS cprrenparcod, cprpg.cprpg_d_competencia + COALESCE(boleto.boldiasbaixa, 0) AS docdtconc, cprpg.cprpgmultav AS docmultab, ''::character(50) AS docnsu FROM cpr JOIN cprpg ON cpr.cprcod = cprpg.cprpgcprcod AND cpr.cprempparcod = cprpg.cprpgcprempparcod JOIN tc tc_1 ON cprpg.cprpgtccod = tc_1.tccod LEFT JOIN ban ban_1 ON tc_1.tcban = ban_1.bancod LEFT JOIN nf ON cpr.cprnfcod = nf.nfcod LEFT JOIN tbe ON tbe.tbetccod = tc_1.tccod LEFT JOIN boleto ON tbe.tbebolcod = boleto.bolcod WHERE (cpr.cprtipo = ANY (ARRAY['CRC'::bpchar, 'CRO'::bpchar, 'CRV'::bpchar])) AND cprpg.cprpgsta = 'A'::bpchar) c JOIN par emp ON c.docempparcod = emp.parcod JOIN par dev ON c.docdevparcod = dev.parcod JOIN par fun ON c.docfunparcod = fun.parcod LEFT JOIN tc ON c.doctccod = tc.tccod LEFT JOIN par ven ON c.docrcaparcod = ven.parcod LEFT JOIN rca ON c.docrcaparcod = rca.rcaparcod LEFT JOIN par sup ON rca.rcasupparcod = sup.parcod LEFT JOIN cli ON c.docdevparcod = cli.cliparcod LEFT JOIN cliente_grupo ON cli.cli_cg_codigo = cliente_grupo.cg_codigo LEFT JOIN area_venda ON cli.cli_av_codigo = area_venda.av_codigo LEFT JOIN loc ON cli.cliloccod = loc.loccod LEFT JOIN ban ON c.docbancod = ban.bancod LEFT JOIN tdvch ON c.doctdvchcod = tdvch.tdvchcod LEFT JOIN conta ON c.doc_conta_id = conta.conta_id LEFT JOIN grupo_conta ON conta.conta_gc_id = grupo_conta.gc_id LEFT JOIN centro_custo ON c.doc_cc_id = centro_custo.cc_id LEFT JOIN carteiras ON c.doc_cart_codigo = carteiras.cart_codigo LEFT JOIN cx cxdono ON c.docdonocxcod = cxdono.cxcod AND c.docempparcod = cxdono.cxempparcod LEFT JOIN par usudono ON c.docdonousuparcod = usudono.parcod LEFT JOIN conf ON c.docempparcod = conf.confempparcod LEFT JOIN clicart ON cli.cliclicartcod = clicart.clicartcod; -- Permissions ALTER TABLE sd.vw_creceb OWNER TO postgres; GRANT ALL ON TABLE sd.vw_creceb TO postgres; GRANT SELECT ON TABLE sd.vw_creceb TO consulta; ------------------------------------------------------------------------------------------------ -- DROP FUNCTION public.f_calc_debcred(int4, int4, date, date, numeric); CREATE OR REPLACE FUNCTION public.f_calc_debcred(integer, integer, date, date, numeric) RETURNS TABLE(dc_rca_cod integer, dc_limite numeric, dc_saldo numeric, dc_vltab numeric, dc_vl numeric, dc_vldebcred numeric, dc_percdebcred numeric, dc_pv_vltab numeric, dc_pv_vl numeric, dc_pv_vldebcred numeric, dc_pv_percdebcred numeric, dc_fat_vltab numeric, dc_fat_vl numeric, dc_fat_vldebcred numeric, dc_fat_percdebcred numeric, dc_devol_vltab numeric, dc_devol_vl numeric, dc_devol_vldebcred numeric, dc_devol_percdebcred numeric, dc_bonif_vltab numeric, dc_bonif_vl numeric, dc_bonif_vldebcred numeric, dc_bonif_percdebcred numeric, dc_troca_vltab numeric, dc_troca_vl numeric, dc_troca_vldebcred numeric, dc_troca_percdebcred numeric, dc_vlextra numeric, dc_bonus_pv_vl numeric, dc_bonus_nf_vl numeric) LANGUAGE plpgsql AS $function$ DECLARE /* select * from f_calc_debcred(1, null,'2024-05-14','now()',null) where dc_rca_cod = 6369 Descrição: Função criada para calcular deb cred do vendedor 05/11/13 - KAYTON: Criação da função f_calc_debcred 13/05/14 - KAYTON: Implementação parametro periodo 28/06/14 - KAYTON: Corrigir bug nos pedidos não faturados - Adicionar opnf tipo VI - Se rca_d_debcred é null então DATE(now()) 27/05/15 - KAYTON: Adicionar campos de Devolução ao cálculo 08/06/16 - KAYTON: Uso dos campos pvibasedebcred e nfibasedebcred no lugar de pvibasedebcred e nfibasedebcred respectivamente. 24/08/17 - David: correção da quantidade na base de devolução 30/08/17 - David: correção do saldo, para considerar pv negativo 03/01/19 - Kayton: atribuir valor inicial para p_data1 e p_data2 10/01/21 - David: considerar bonificação e troca no saldo do deb/cred 17/11/22 - David: adicionar BV e TR nos dados dos pedidos; correção do valor de bonificação / troca; correção do saldo final; 19/11/22 - David: inserir pedidos AP no calculo de deb/cred; 15/05/23 - David: correção do sinal no creb/cred de devolução 10/05/24 - David: tratar rca_debcred_perc_bonus para adicionar um % especial de acordo com os pedidos/notas sem desconto 15/05/24 - David: Correção do Bonus */ p_empresa ALIAS FOR $1; p_vendedor ALIAS FOR $2; p_data1 ALIAS FOR $3; p_data2 ALIAS FOR $4; p_limite ALIAS FOR $5; /* */ rec_dc RECORD; BEGIN p_data1:=case when p_data1 is not null then p_data1 else coalesce((select rca_d_debcred from rca where rcaparcod=p_vendedor),DATE(now())) end; p_data2:=case when p_data2 is not null then p_data2 else DATE(now()) end; FOR rec_dc IN select rcaparcod as dc_rca_cod, coalesce(p_limite,rcadclimite,0) as dc_limite, --SALDO --coalesce(p_limite,rcadclimite,0)+(coalesce(pv_vl,0)-coalesce(pv_basedebcred,0))+(coalesce(fat_vl,0)-coalesce(fat_basedebcred,0))-(coalesce(fat_basedebcred_dc,0)-coalesce(fat_vl_dc,0))+coalesce(vlextra,0) as dc_saldo, case when coalesce(rca_usa_bonif_troca_dc,'S')='S' then coalesce(p_limite,rcadclimite,0)+ (coalesce(pv_vl,0)-coalesce(pv_basedebcred,0))+ (coalesce(fat_vl,0)-coalesce(fat_basedebcred,0))- (coalesce(fat_vl_dc,0)-coalesce(fat_basedebcred_dc,0))- (coalesce(fat_vl_bo,0))- (coalesce(fat_vl_tr,0))+ coalesce(vlextra,0)+ coalesce(bonus_pv_cred_vl,0)+ coalesce(bonus_nf_cred_vl,0) else coalesce(p_limite,rcadclimite,0)+ (coalesce(pv_vl,0)-coalesce(pv_basedebcred,0))+ (coalesce(fat_vl,0)-coalesce(fat_basedebcred,0))- (coalesce(fat_vl_dc,0)-coalesce(fat_basedebcred_dc,0))+ coalesce(vlextra,0)+ coalesce(bonus_pv_cred_vl,0)+ coalesce(bonus_nf_cred_vl,0) end as dc_saldo, --TOTAL case when coalesce(rca_usa_bonif_troca_dc,'S')='S' then coalesce(pv_basedebcred,0)+coalesce(fat_basedebcred,0)-coalesce(fat_basedebcred_bo,0)-coalesce(fat_basedebcred_tr,0) else coalesce(pv_basedebcred,0)+coalesce(fat_basedebcred,0) end as dc_basedebcred, case when coalesce(rca_usa_bonif_troca_dc,'S')='S' then coalesce(pv_vl,0)+coalesce(fat_vl,0)-coalesce(fat_vl_bo,0)-coalesce(fat_vl_tr,0) else coalesce(pv_vl,0)+coalesce(fat_vl,0) end as dc_vl, case when coalesce(rca_usa_bonif_troca_dc,'S')='S' then (coalesce(pv_vl,0)+coalesce(fat_vl,0)-coalesce(fat_vl_bo,0)-coalesce(fat_vl_tr,0))-(coalesce(pv_basedebcred,0)+coalesce(fat_basedebcred,0)-coalesce(fat_basedebcred_bo,0)-coalesce(fat_basedebcred_tr,0)) else (coalesce(pv_vl,0)+coalesce(fat_vl,0))-(coalesce(pv_basedebcred,0)+coalesce(fat_basedebcred,0)) end as dc_vldebcred, case when coalesce(rca_usa_bonif_troca_dc,'S')='S' then case when (coalesce(pv_basedebcred,0)+coalesce(fat_basedebcred,0)-coalesce(fat_basedebcred_bo,0)-coalesce(fat_basedebcred_tr,0))>0 then round(((coalesce(pv_vl,0)+coalesce(fat_vl,0)-coalesce(fat_vl_bo,0)-coalesce(fat_vl_tr,0))-(coalesce(pv_basedebcred,0)+coalesce(fat_basedebcred,0)-coalesce(fat_basedebcred_bo,0)-coalesce(fat_basedebcred_tr,0)))/(coalesce(pv_basedebcred,0)+coalesce(fat_basedebcred,0)-coalesce(fat_basedebcred_bo,0)-coalesce(fat_basedebcred_tr,0))*100,2) else 0.00 end else case when (coalesce(pv_basedebcred,0)+coalesce(fat_basedebcred,0))>0 then round(((coalesce(pv_vl,0)+coalesce(fat_vl,0))-(coalesce(pv_basedebcred,0)+coalesce(fat_basedebcred,0)))/(coalesce(pv_basedebcred,0)+coalesce(fat_basedebcred,0))*100,2) else 0.00 end end as dc_percdebcred, --PV case when coalesce(rca_usa_bonif_troca_dc,'S')='S' then coalesce(pv_basedebcred,0)+coalesce(pv_basedebcred_bo,0)+coalesce(pv_basedebcred_tr,0) else coalesce(pv_basedebcred,0) end as dc_pv_basedebcred, case when coalesce(rca_usa_bonif_troca_dc,'S')='S' then coalesce(pv_vl,0)+coalesce(pv_vl_bo,0)+coalesce(pv_vl_tr,0) else coalesce(pv_vl,0) end as dc_pv_vl, case when coalesce(rca_usa_bonif_troca_dc,'S')='S' then (coalesce(pv_vl,0)-coalesce(pv_basedebcred,0)) else (coalesce(pv_vl,0)-coalesce(pv_basedebcred,0)-coalesce(pv_vl_bo,0)-coalesce(pv_vl_tr,0)) end as dc_pv_vldebcred, case when coalesce(rca_usa_bonif_troca_dc,'S')='S' then case when coalesce(pv_basedebcred,0)>0 then round((coalesce(pv_vl,0)-coalesce(pv_basedebcred,0))/coalesce(pv_basedebcred,0)*100,2) else 0.00 end else case when coalesce(pv_basedebcred,0)>0 then round((coalesce(pv_vl,0)-coalesce(pv_basedebcred,0)-coalesce(pv_vl_bo,0)-coalesce(pv_vl_tr,0))/coalesce(pv_basedebcred,0)*100,2) else 0.00 end end as dc_pv_percdebcred, --FAT coalesce(fat_basedebcred,0) as dc_fat_basedebcred, -- errado coalesce(fat_vl,0) as dc_fat_vl, (coalesce(fat_vl,0)-coalesce(fat_basedebcred,0)) as dc_fat_vldebcred, case when coalesce(fat_basedebcred,0)>0 then round((coalesce(fat_vl,0)-coalesce(fat_basedebcred,0))/coalesce(fat_basedebcred,0)*100,2) else 0.00 end as dc_fat_percdebcred, --DEVOL coalesce(fat_basedebcred_dc,0) as dc_devol_basedebcred, coalesce(fat_vl_dc,0) as dc_devol_vl, (coalesce(fat_vl_dc,0)-coalesce(fat_basedebcred_dc,0)) as dc_devol_vldebcred, --(coalesce(fat_basedebcred_dc,0)-coalesce(fat_vl_dc,0)) as dc_devol_vldebcred, --case when coalesce(fat_basedebcred_dc,0)>0 then round((coalesce(fat_vl_dc,0)-coalesce(fat_basedebcred_dc,0))/coalesce(fat_basedebcred_dc,0)*100,2) else 0.00 end as dc_devol_percdebcred, case when coalesce(fat_basedebcred_dc,0)>0 then round((coalesce(fat_basedebcred_dc,0)-coalesce(fat_vl_dc,0))/coalesce(fat_basedebcred_dc,0)*100,2) else 0.00 end as dc_devol_percdebcred, --BONIF coalesce(fat_basedebcred_bo,0) as dc_bonif_basedebcred, coalesce(fat_vl_bo,0) as dc_bonif_vl, (coalesce(fat_vl_bo,0)) as dc_bonif_vldebcred, case when coalesce(fat_basedebcred_bo,0)>0 then round((coalesce(fat_vl_bo,0)-coalesce(fat_basedebcred_bo,0))/coalesce(fat_basedebcred_bo,0)*100,2) else 0.00 end as dc_bonif_percdebcred, --TROCA coalesce(fat_basedebcred_tr,0) as dc_troca_basedebcred, coalesce(fat_vl_tr,0) as dc_troca_vl, (coalesce(fat_vl_tr,0)) as dc_troca_vldebcred, case when coalesce(fat_basedebcred_tr,0)>0 then round((coalesce(fat_vl_tr,0)-coalesce(fat_basedebcred_tr,0))/coalesce(fat_basedebcred_tr,0)*100,2) else 0.00 end as dc_troca_percdebcred, --EXTRA coalesce(vlextra,0) as dc_extra_vl, --Bonus coalesce(bonus_pv_cred_vl,0) as dc_bonus_pv_vl, coalesce(bonus_nf_cred_vl,0) as dc_bonus_nf_vl from rca --Pedido left join ( select pv_rca_cod, round(sum(pv_basedebcred),2) as pv_basedebcred, round(sum(pv_vl),2) as pv_vl from sd.vw_pv where DATE(pv_dtlan) between p_data1 and p_data2 and pv_opnf_tipo in ('VE','VI','VV') and pv_sta not in ('EX','FT') and case when p_vendedor is not null then pv_rca_cod = p_vendedor else 1=1 end group by pv_rca_cod ) as dcpv on rcaparcod=dcpv.pv_rca_cod --Pedido bonificação/troca left join ( select pv_rca_cod, round(sum(pv_basedebcred_bo),2) as pv_basedebcred_bo, round(sum(pv_vl_bo),2) as pv_vl_bo, round(sum(pv_basedebcred_tr),2) as pv_basedebcred_tr, round(sum(pv_vl_tr),2) as pv_vl_tr from sd.vw_pv where DATE(pv_dtlan) between p_data1 and p_data2 and pv_opnf_tipo in ('BV','TR') and pv_sta not in ('EX','FT') and case when p_vendedor is not null then pv_rca_cod = p_vendedor else 1=1 end group by pv_rca_cod ) as dcpvbotr on rcaparcod=dcpvbotr.pv_rca_cod --Faturado left join ( select fat_rca_cod, round(sum(fat_basedebcred),2) as fat_basedebcred, round(sum(fat_vl),2) as fat_vl, round(sum(fat_basedebcred_dc),2) as fat_basedebcred_dc, round(sum(fat_vl_dc),2) as fat_vl_dc from sd.vw_fat where fat_nf_sta not in ('EX') and fat_opnf_tipo in ('VE','VI','VV','DC') and DATE(fat_nf_dtemi) between p_data1 and p_data2 and case when p_vendedor is not null then fat_rca_cod = p_vendedor else 1=1 end group by fat_rca_cod ) as dcfat on rcaparcod=dcfat.fat_rca_cod --Bonificação / troca left join ( select fat_rca_cod as boniftr_rca_cod, round(sum(fat_basedebcred_bo),2) as fat_basedebcred_bo, round(sum(fat_vl_bo),2) as fat_vl_bo, round(sum(fat_basedebcred_tr),2) as fat_basedebcred_tr, round(sum(fat_vl_tr),2) as fat_vl_tr from sd.vw_fat where fat_nf_sta not in ('EX') and fat_opnf_tipo in ('BV','TR') and DATE(fat_nf_dtemi) between p_data1 and p_data2 and case when p_vendedor is not null then fat_rca_cod = p_vendedor else 1=1 end group by fat_rca_cod ) as dcfatbotc on rcaparcod=dcfatbotc.boniftr_rca_cod --Extra left join ( select rcamvrcaparcod, sum(case when rcamvnatureza='D' then -rcamvvalor else rcamvvalor end) as vlextra from rcamv join rca on rcamvrcaparcod=rcaparcod where rcamvsta='A' and DATE(rcamvdtlanc) between p_data1 and p_data2 and case when p_vendedor is not null then rcamvrcaparcod = p_vendedor else 1=1 end group by rcamvrcaparcod ) as dcextra on rcaparcod=dcextra.rcamvrcaparcod --Bonus PV left join ( select bonus_pv_rca_cod, sum(bonus_pv_cred_vl) as bonus_pv_cred_vl from( select pv_rca_cod as bonus_pv_rca_cod, round(sum(pv_basedebcred),2) as bonus_pv_basedebcred, round(sum(pv_vl),2) as bonus_pv_vl, case when (round(sum(pv_vl),2) - round(sum(pv_basedebcred),2) >= 0) then round(sum((pv_vl*rca_debcred_perc_bonus)/100),2) else 0 end as bonus_pv_cred_vl from sd.vw_pv join rca on pv_rca_cod=rcaparcod where DATE(pv_dtlan) between p_data1 and p_data2 and pv_opnf_tipo in ('VE','VI','VV') and pv_sta not in ('EX','FT') and case when p_vendedor is not null then pv_rca_cod = p_vendedor else 1=1 end group by pv_rca_cod,pv_cod)a group by bonus_pv_rca_cod ) as dcbonuspv on rcaparcod=dcbonuspv.bonus_pv_rca_cod --Bonus NF left join ( select bonus_fat_rca_cod, sum(bonus_nf_cred_vl) as bonus_nf_cred_vl from( select fat_rca_cod as bonus_fat_rca_cod, round(sum(fat_basedebcred),2) as bonus_fat_basedebcred, round(sum(fat_vl),2) as bonus_fat_vl, case when (round(sum(fat_vl),2) - round(sum(fat_basedebcred),2) >= 0) then round(sum((fat_vl*rca_debcred_perc_bonus)/100),2) else 0 end as bonus_nf_cred_vl from sd.vw_fat join rca on fat_rca_cod=rcaparcod where fat_nf_sta not in ('EX') and fat_opnf_tipo in ('VE','VI','VV','DC') and DATE(fat_nf_dtemi) between p_data1 and p_data2 and case when p_vendedor is not null then fat_rca_cod = p_vendedor else 1=1 end group by fat_rca_cod,fat_nf_cod)a group by bonus_fat_rca_cod ) as dcbonusnf on rcaparcod=dcbonusnf.bonus_fat_rca_cod where rcausadebcred = 'S' and case when p_vendedor is not null then rcaparcod = p_vendedor else 1=1 end order by rcaparcod LOOP RETURN QUERY SELECT rec_dc.dc_rca_cod ,rec_dc.dc_limite ,rec_dc.dc_saldo ,rec_dc.dc_basedebcred ,rec_dc.dc_vl ,rec_dc.dc_vldebcred ,rec_dc.dc_percdebcred ,rec_dc.dc_pv_basedebcred ,rec_dc.dc_pv_vl ,rec_dc.dc_pv_vldebcred ,rec_dc.dc_pv_percdebcred ,rec_dc.dc_fat_basedebcred ,rec_dc.dc_fat_vl ,rec_dc.dc_fat_vldebcred ,rec_dc.dc_fat_percdebcred ,rec_dc.dc_devol_basedebcred ,rec_dc.dc_devol_vl ,rec_dc.dc_devol_vldebcred ,rec_dc.dc_devol_percdebcred ,rec_dc.dc_bonif_basedebcred ,rec_dc.dc_bonif_vl ,rec_dc.dc_bonif_vldebcred ,rec_dc.dc_bonif_percdebcred ,rec_dc.dc_troca_basedebcred ,rec_dc.dc_troca_vl ,rec_dc.dc_troca_vldebcred ,rec_dc.dc_troca_percdebcred ,rec_dc.dc_extra_vl ,rec_dc.dc_bonus_pv_vl ,rec_dc.dc_bonus_nf_vl RETURN; END LOOP; RETURN; END; $function$ ; -- Permissions ALTER FUNCTION public.f_calc_debcred(int4, int4, date, date, numeric) OWNER TO postgres; GRANT ALL ON FUNCTION public.f_calc_debcred(int4, int4, date, date, numeric) TO public; GRANT ALL ON FUNCTION public.f_calc_debcred(int4, int4, date, date, numeric) TO postgres; ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------