update conf set confrelease = '13301'; ------------------------------------------------------------------------------------------------ -- DROP FUNCTION public.f_realizar_faturamento_nfs(int4, int4, int4, int4, timestamptz, timestamptz); CREATE OR REPLACE FUNCTION public.f_realizar_faturamento_nfs(integer, integer, integer, integer, timestamp with time zone, timestamp with time zone) RETURNS TABLE(frfatservcod integer, frfatnfnum integer, frfatnfserie text, frfatmens text) LANGUAGE plpgsql AS $function$ DECLARE /* 04/05/2022 - David - Adicionar efetivação automatica 08/05/2022 - David - Adicionar campos de IRRF 26/01/2022 - David - Tratar a tabela servplpg para lançar cprpg e doc 18/06/2024 - David - alimentar a docobs com a PROSERVDES 06/01/2024 - David - Tratar faturamento das notas tipo E */ --select * from f_realizar_faturamento_nfs(100,1,224,4,cast(now() as date),now()) p_usuario ALIAS FOR $1;--integer 1001 p_empresa ALIAS FOR $2;--integer 191 p_servcod ALIAS FOR $3;--integer 843 p_boleto ALIAS FOR $4;--integer 9 - CAIXA p_dtemissao ALIAS FOR $5;--date now() p_horariosaida ALIAS FOR $6;--timestamp rec_pvserv RECORD; rec_plpg RECORD; rec_seqnf RECORD; rec_mens RECORD; rec_efetauto RECORD; v_erro INTEGER; num_parc INTEGER; v_res INTEGER; v_cprcod INTEGER; v_agora TIMESTAMP; v_pedido INTEGER; v_inseriuCab BOOLEAN; v_confobsnfs TEXT; v_obsnf TEXT; v_nfidet RECORD; v_vbasecalc numeric(14,4); v_vissqn numeric(14,4); v_efetauto TEXT; v_cprtipo TEXT; BEGIN --************************************************************* -- VALIDAR DADOS ANTES DO FATURAMENTO --************************************************************* v_erro := 0; FOR rec_mens IN select servico,advertencia from f_validar_faturamento_nfs(p_empresa,p_servcod) LOOP RETURN QUERY SELECT 0::integer as servcod, 0::integer as servnfnum, ''::text as servnfserie, rec_mens.advertencia; v_erro := 1; END LOOP; IF v_erro > 0 THEN RETURN; END IF; --************************************************************* -- OBTER DATA HORA HOJE --************************************************************* select now() into v_agora; --************************************************************* -- OBTER DADOS PV --************************************************************* v_inseriuCab := false;/*Inserir nf somente 1 vez e não para cada item do laço. Dessa forma é possivel usar 1 sql. */ --************************************************************* -- OBTER DADOS serv/servI --************************************************************* FOR rec_pvserv IN select servempparcod,servcod,servsta,servplpgcod, case when cast(p_boleto as integer)>0 and (select tc_per_alt_fat from tc where tccod=servtccod)='S' then p_boleto else servtccod end as servtccod, servcliparcod,servopnfcod,servobs,proservdes,serviservempparcod,serviservcod,serviproservcod ,serviqtde, servivltotal,opnfsta,opnf_conta_id,opnf_cc_id,opnfcpr,opnfesp,opnftopnfcod, coalesce(opnftipoemissaonf,'P') as opnftipoemissaonf, case when opnfpiscofins='S' then opnfpis else servialiqpis end pisaliq, case when opnfpiscofins='S' then opnfpis_cst else servicstpiscofinscod end piscst, case when opnfpiscofins='S' then opnfpis_aliqvalor else servivlpautapis end pisvlpauta, case when opnfpiscofins='S' then opnfcofins else servialiqcofins end cofinsaliq, case when opnfpiscofins='S' then opnfcofins_cst else servicstpiscofinscod end cofinscst, case when opnfpiscofins='S' then opnfcofins_aliqvalor else servivlpautacofins end cofinsvlpauta, case when (coalesce(opnfpiscofins,'')='S' and coalesce(opnfpis_cst,'')='01') then servivltotal else 0 end as bcpis, case when (coalesce(opnfpiscofins,'')='S' and coalesce(opnfcofins_cst,'')='01') then servivltotal else 0 end as bccofins, case when coalesce(emppercirpj,0)>0 then servivltotal else 0 end as bcirrf, emppercirpj as aliqirrf, 0 as bcqtpis,--pendente para produto com pauta 0 as bcqtcofins,--pendente para produto com pauta gtcod,gtdes,gtaliqissqn, case when opnfcfop='S' and munemp.munufsigla = muncli.munufsigla then opnfcfopcode when opnfcfop='S' and munemp.munufsigla <> muncli.munufsigla and clicontricms='S' then opnfcfopcodi when opnfcfop='S' and munemp.munufsigla <> muncli.munufsigla and clicontricms<>'S' then opnfcfopcodincontricms else case when clicontricms='S' then gtcfopcod else gtncontribcfopcod end end as cfopcod, gtmensagem,gtlegenda,gtpercdescisencaocond,gtcsosncod,gtperccredicmssn as gtpCredSN, plpgdes,plpgcondicao, (select count(*) from (select regexp_split_to_table(plpgcondicao, ';')) p) as num_parc, servnfnum, servnfserie, opnfnat from serv join emp on empparcod = servempparcod join par paremp on paremp.parcod = empparcod join mun munemp on munemp.muncod = paremp.parmuncod join cli on servcliparcod =cliparcod join mun muncli on muncli.muncod = climuncodent join loc on cliloccod =loccod join opnf on servopnfcod =opnfcod join plpg on servplpgcod =plpgcod join servi on servcod =serviservcod and servempparcod=serviservempparcod join proserv on serviproservcod =proservcod left join gt on gtcod= case when munemp.munufsigla = muncli.munufsigla then opnfgtcode when munemp.munufsigla <> muncli.munufsigla then opnfgtcodi end where serviservempparcod=p_empresa and serviservcod=p_servcod LOOP --************************************************************* --INSERIR ITEM - NFI --************************************************************* --ISSQN v_vbasecalc:=0; v_vissqn:=0; v_vbasecalc := rec_pvserv.servivltotal; v_vissqn := round(v_vbasecalc*coalesce(rec_pvserv.gtaliqissqn,0)/100,4); update servi set servicfopcod = rec_pvserv.cfopcod, servialiqissqn = rec_pvserv.gtaliqissqn, servibasecalc = v_vbasecalc, servivlissqn = v_vissqn, servicstpiscofinscod = rec_pvserv.piscst,--nficstpiscofinscod, servialiqpis = rec_pvserv.pisaliq,--nfialiqpis, servivlpautapis = rec_pvserv.pisvlpauta,--nfivlpautapis, servivlpis = round(rec_pvserv.bcpis*rec_pvserv.pisaliq/100,2),--nfivlpis, servialiqcofins = rec_pvserv.cofinsaliq,--nfialiqcofins, servivlpautacofins = rec_pvserv.cofinsvlpauta,--nfivlpautacofins, servivlcofins = round(rec_pvserv.bccofins*rec_pvserv.cofinsaliq/100,2),--nfivlcofins, servibcpis = rec_pvserv.bcpis,--nfibcpis numeric(14,4), servibccofins = rec_pvserv.bccofins,--nfibccofins numeric(14,4), servibcqtpis = rec_pvserv.bcqtpis,--nfibcqtpis numeric(14,4), servibcqtcofins = rec_pvserv.bcqtcofins, --nfibcqtcofins numeric(14,4), servibcirrf = rec_pvserv.bcirrf, servialiqirrf = rec_pvserv.aliqirrf, servivlirrf = round(rec_pvserv.bcirrf*rec_pvserv.aliqirrf/100,2) where serviservcod = rec_pvserv.servcod and serviproservcod = rec_pvserv.serviproservcod; IF NOT v_inseriuCab THEN --Verifica se cab nf já foi inserido --************************************************************* --INSERIR CAB NF --************************************************************* --OBTER OBS NF v_confobsnfs:=''; if not rec_pvserv.opnfnat = 'E' then select trim(confobsnfs) into v_confobsnfs from conf where confempparcod=p_empresa; EXECUTE coalesce(v_confobsnfs,'select null') INTO v_obsnf USING p_servcod; end if; --INSERIR REGISTRO EM NFE insert into nfeserv ( nfeservcadusuparcod, nfeservcadhorario, nfeservaltusuparcod, nfeservalthorario, nfeservnfempparcod, nfeservnfparcod, nfeservnfnum, nfeservnfserie, nfeservservcod, nfeservobs ) values ( p_usuario, --nfecadusuparcod integer, v_agora, --nfecadhorario timestamp without time zone, null,--nfealtusuparcod integer, null,--nfealthorario timestamp without time zone, rec_pvserv.servempparcod,--nfenfempparcod integer NOT NULL, rec_pvserv.servcliparcod,--nfenfparcod integer NOT NULL, p_servcod,--nfenfnum integer NOT NULL, update - Recebe v_nfcod/seq provisoriamente ate gravar nfi, cpr e cprpg '0',--nfenfserie character varying(4) NOT NULL, update - Recebe 0 provisoriamente ate gravar nfi, cpr e cprpg p_servcod, --nfenfcod integer NOT NULL, v_obsnf -- nfeservobs text ); v_inseriuCab := TRUE; END IF;--IF NOT inseriuCab THEN END LOOP; --Se opnf gera conta IF rec_pvserv.opnfcpr='S' THEN --************************************************************* --OBTER PROX CODIGO/SEQ DE CPR - CAMPO CHAVE --************************************************************* select nextval('seq_cpr'||rec_pvserv.servempparcod::text) into v_cprcod; if not rec_pvserv.opnfnat = 'E' then v_cprtipo = 'CRC'; else v_cprtipo = 'CPO'; end if; --INSERIR CAB insert into cpr ( cprempparcod,-- integer NOT NULL, cprcod,-- integer NOT NULL, cprtipo,-- character(3), cprccpcgcccod,-- integer, cprccpcgpcgcod,-- integer, cprmodg,-- character(2), cprparcod,-- integer, cprdocnum,-- integer, cprdocserie,-- character varying(10), cprprev,-- character(1), cprdemi,-- timestamp with time zone, cprdlan,-- timestamp with time zone, cprvt,-- numeric(14,4) DEFAULT 0, cprvtpg,-- numeric(14,4) DEFAULT 0, cprfunparcod,-- integer, cprpcgcod,-- integer, cprnat,-- character(1), cprrcaparcod,-- integer, cprdevparcod,-- integer, cprobs,-- character varying(250), cpr_cart_codigo,-- integer, cpr_d_competencia,-- date, cpr_ccc_id,-- integer, cpr_conta_id,-- integer, cpr_cc_id,-- integer, cpr_notafiscal,-- character varying(20), cprservcod-- integer, ) select servempparcod,--cprempparcod integer NOT NULL, v_cprcod,--cprcod integer NOT NULL, v_cprtipo,--cprtipo character(3), null,--cprccpcgcccod integer, null,--cprccpcgpcgcod integer, 'FT',--cprmodg character(2), servcliparcod,--cprparcod integer, null,--update cprdocnum integer, null,--update cprdocserie character varying(10), 'N',--cprprev character(1), v_agora,--cprdemi timestamp with time zone, v_agora,--cprdlan timestamp with time zone, servvltotal,--cprvt numeric(14,4) DEFAULT 0, 0,--cprvtpg numeric(14,4) DEFAULT 0, p_usuario,--cprfunparcod integer, null,--cprpcgcod integer, null,--cprnat character(1), 0,--cprrcaparcod integer, servcliparcod,--cprdevparcod integer, 'Referente a Serviço Prestado'||' \ '||rec_pvserv.proservdes,--cprobs character varying(250), 1,--cpr_cart_codigo integer, DATE(v_agora),--cpr_d_competencia date, null,--cpr_ccc_id integer, rec_pvserv.opnf_conta_id,--cpr_conta_id integer, rec_pvserv.opnf_cc_id,--cpr_cc_id integer, null, --update cpr_notafiscal character varying(20), servcod --cprnfcod integer; from serv where servcod=p_servcod; /** --INSERIR CPRPG insert into cprpg( cprpgcprempparcod,-- integer NOT NULL, cprpgcprcod,-- integer NOT NULL, cprpgcod,-- integer NOT NULL, cprpgdvenc,-- date, cprpgv,-- numeric(14,4), cprpgmultav,-- numeric(14,4) DEFAULT 0, cprpgmultaperc,-- numeric(14,4) DEFAULT 0, cprpgmoradiav,-- numeric(14,4) DEFAULT 0, cprpgmoradiaperc,-- numeric(14,4) DEFAULT 0, cprpgdescv,-- numeric(14,4) DEFAULT 0, cprpgdescperc,-- numeric(14,4) DEFAULT 0, cprpgtccod,-- integer, cprpgobs,-- text, cprpgdbai,-- date, cprpgsta,-- character(1), cprpgcodo,-- integer, cprpgefetcod,-- integer, cprpgvbai,-- numeric(14,4) DEFAULT 0, cprpgdpag,-- date, cprpgfunparcod,-- integer, cprpgbancod,-- integer, cprpgag,-- character varying(10), cprpgnumdoc,-- character varying(18), cprpgcc,-- character varying(10), cprpgdsit,-- timestamp with time zone, cprpgsit,-- character(2), cprpgcxcod,-- integer, cprpgtdvchcod,-- integer, cprpgobssit,-- text, cprpgreapdvch,-- character(1), cprpgnn,-- character(20), cprpgdevv,-- numeric(14,4), cprpgdvenco,-- date, cprpgtccodo,-- integer, cprpgstsequifax,-- character(1), cprpg_d_lanc,-- timestamp without time zone, cprpg_d_competencia,-- date, cprpg_arq_remessa,-- character varying(80), cprpgobsbol,-- character varying(254), cprpgcedparcod,-- integer DEFAULT 0, cprpgcedbolagced,-- character varying(10), cprpgcedbolccced,-- character varying(10), cprpgcedboldvag,-- integer, cprpgcedboldvcc,-- integer, cprpgparc ) select cprempparcod,--cprpgcprempparcod integer NOT NULL, v_cprcod,--cprpgcprcod integer NOT NULL, parc,--cprpgcod integer NOT NULL, now()::date+prazo[parc]::integer,--cprpgdvenc date, cprvt/rec_pvserv.num_parc,--cprpgv numeric(14,4), null,--cprpgmultav numeric(14,4) DEFAULT 0, null,--cprpgmultaperc numeric(14,4) DEFAULT 0, null,--cprpgmoradiav numeric(14,4) DEFAULT 0, null,--cprpgmoradiaperc numeric(14,4) DEFAULT 0, null,--cprpgdescv numeric(14,4) DEFAULT 0, null,--cprpgdescperc numeric(14,4) DEFAULT 0, rec_pvserv.servtccod,--cprpgtccod integer, null,--cprpgobs text, null,--cprpgdbai date, 'A',--cprpgsta character(1), null,--cprpgcodo integer, null,--cprpgefetcod integer, null,--cprpgvbai numeric(14,4) DEFAULT 0, null,--cprpgdpag date, cprfunparcod,--cprpgfunparcod integer, null,--cprpgbancod integer, null,--cprpgag character varying(10), null,--cprpgnumdoc character varying(18), null,--cprpgcc character varying(10), null,--cprpgdsit timestamp with time zone, null,--cprpgsit character(2), null,--cprpgcxcod integer, null,--cprpgtdvchcod integer, null,--cprpgobssit text, null,--cprpgreapdvch character(1), null,--cprpgnn character(20), null,--cprpgdevv numeric(14,4), now()::date+prazo[parc]::integer,--cprpgdvenco date, rec_pvserv.servtccod,--cprpgtccodo integer, null,--cprpgstsequifax character(1), v_agora,--cprpg_d_lanc timestamp without time zone, cast(v_agora as date),--cprpg_d_competencia date, null,--cprpg_arq_remessa character varying(80), null,--cprpgobsbol character varying(254), null,--cprpgcedparcod integer DEFAULT 0, null,--cprpgcedbolagced character varying(10), null,--cprpgcedbolccced character varying(10), null,--cprpgcedboldvag integer, null,--cprpgcedboldvcc integer, parc from (select regexp_split_to_array(rec_pvserv.plpgcondicao, ';') as prazo) p join generate_series(1,rec_pvserv.num_parc) parc on 1=1 join cpr on cprempparcod=p_empresa and cprcod=v_cprcod; --verifica diferenca e joga na ultima parcela UPDATE cprpg a SET cprpgv = round(cprpgv,2)+p.dif FROM ( select cprempparcod, cprcod, cprvt-sum(round(coalesce(cprpgv,0),2)) as dif from cpr left join cprpg on cprcod=cprpgcprcod and cprpgcprempparcod=cprempparcod where cprcod=v_cprcod group by cprempparcod,cprcod,cprvt ) AS p WHERE cprpgcprcod=cprcod and cprpgcprempparcod=cprempparcod and cprpgcod=rec_pvserv.num_parc; */ --IF coalesce(rec_pv.qtparcplpgnegoc,0)>0 THEN --INSERIR CPRPG A PARTIR DE PVPLPG (PLANO NEGOCIADO) insert into cprpg ( cprpgcprempparcod,-- integer NOT NULL, cprpgcprcod,-- integer NOT NULL, cprpgcod,-- integer NOT NULL, cprpgdvenc,-- date, cprpgv,-- numeric(14,4), cprpgtccod,-- integer, cprpgsta,-- character(1), cprpgfunparcod,-- integer, cprpgdvenco,-- date, cprpgtccodo,-- integer, cprpg_d_lanc,-- timestamp without time zone, cprpg_d_competencia,-- date, cprpgparc-- varchar(250) ) select cprempparcod,--cprpgcprempparcod integer NOT NULL, v_cprcod,--cprpgcprcod integer NOT NULL, servplpgparc,--cprpgcod integer NOT NULL, servplpgdtvenc,--cprpgdvenc date, servplpgv,--cprpgv numeric(14,4), servplpgtccod,--cprpgtccod integer, 'A',--cprpgsta character(1), cprfunparcod,--cprpgfunparcod integer, servplpgdtvenc,--cprpgdvenco date, servplpgtccod,--cprpgtccodo integer, v_agora,--cprpg_d_lanc timestamp without time zone, cast(v_agora as date),--cprpg_d_competencia date, servplpgparc --cprpgparc varchar(250) from servplpg join serv on servplpgpvcod = servcod and servcod = p_servcod join cpr on cprempparcod=p_empresa and cprcod=v_cprcod; END IF;--IF rec_pvserv.opnfcpr='S' THEN --************************************************************* -- GERAR PROXIMO NUM NF --************************************************************* if not rec_pvserv.opnfnat = 'E' then select empresa,layout,numero,serie into rec_seqnf from f_seqnf(rec_pvserv.servempparcod,rec_pvserv.servopnfcod); else select rec_pvserv.servempparcod as empresa,'' as layout,rec_pvserv.servnfnum as numero,rec_pvserv.servnfserie as serie into rec_seqnf; end if; --************************************************************* -- ATUALIZAR PROXIMO NUM NF E DEMAIS DADOS --************************************************************* update serv set servnfnum=rec_seqnf.numero,servnfserie=rec_seqnf.serie where servcod=p_servcod; update nfeserv set nfeservnfnum=rec_seqnf.numero,nfeservnfserie=rec_seqnf.serie where nfeservservcod=p_servcod; update cpr set cprdocnum=rec_seqnf.numero,cprdocserie=rec_seqnf.serie,cpr_notafiscal=rec_seqnf.numero::text where cprcod=v_cprcod; if rec_pvserv.opnfnat = 'E' then update serv set servsta='SA',servacao = 'FATURAR ENTRADA' where servcod=rec_pvserv.servcod and servempparcod=rec_pvserv.servempparcod; else update serv set servsta='FT',servacao = 'FATURAR' where servcod=rec_pvserv.servcod and servempparcod=rec_pvserv.servempparcod; end if; --************************************************************* -- TRATAMENTO DA EFETIVACAO AUTOMATICA --************************************************************* --atualizar info sobre compras do cliente IF rec_pvserv.opnftopnfcod IN ('VE','VI','VV') THEN --Inserindo a Data da Primeira Compra update cli set clidpcomp=v_agora where cliparcod=rec_pvserv.servcliparcod and (clidpcomp is null); --Atualizando a Data da Ultima Compra update cli set cliducomp=v_agora,cliultnfnum=rec_seqnf.numero,cliultnfserie=rec_seqnf.serie where cliparcod=rec_pvserv.servcliparcod; END IF; --************************************************************* -- NFIDET --************************************************************* --select f_atualizar_nfidet(v_nfcod) into v_nfidet; --************************************************************* -- EFETIVACAO AUTOMATICA --************************************************************* if rec_pvserv.opnfnat = 'E' then select f_efetivar_automatico_nfs(p_servcod) into rec_efetauto; else IF rec_pvserv.opnfcpr='S' THEN select confefetivarauto into v_efetauto from conf where confempparcod=rec_pvserv.servempparcod; IF coalesce(v_efetauto,'N')='S' THEN select f_efetivar_automatico_nfs(p_servcod) into rec_efetauto; END IF; END IF; end if; RETURN QUERY SELECT p_servcod::integer as frfatservcod, rec_seqnf.numero::integer as frfatnfnum, rec_seqnf.serie::text as frfatnfserie, 'Faturamento realizado'::text; RETURN; END; $function$ ; -- Permissions ALTER FUNCTION public.f_realizar_faturamento_nfs(int4, int4, int4, int4, timestamptz, timestamptz) OWNER TO postgres; GRANT ALL ON FUNCTION public.f_realizar_faturamento_nfs(int4, int4, int4, int4, timestamptz, timestamptz) TO public; GRANT ALL ON FUNCTION public.f_realizar_faturamento_nfs(int4, int4, int4, int4, timestamptz, timestamptz) TO postgres; GRANT ALL ON FUNCTION public.f_realizar_faturamento_nfs(int4, int4, int4, int4, timestamptz, timestamptz) TO consulta; ------------------------------------------------------------------------------------------------ ALTER TABLE public.conf ADD conf_envia_email_copy_empresa bpchar NULL; ------------------------------------------------------------------------------------------------ -- ipedi.vw_cliente fonte CREATE OR REPLACE VIEW ipedi.vw_cliente AS SELECT rca.rcaparcod AS rcacod, cli.cliparcod AS clicod, limpastring(par.pardes)::character varying(60) AS clirazao, limpastring(par.parfan)::character varying(60) AS clifantasia, par.parcnpjcpf AS clicnpj, limpastring(par.parierg) AS cliie, limpastring(par.paremail) AS cli_email, CASE WHEN cli.cliruaent IS NULL OR cli.cliruaent::text = ''::text THEN ' '::character varying ELSE limpastring(cli.cliruaent)::character varying(30) END AS clirua, CASE WHEN cli.clibaient IS NULL OR cli.clibaient::text = ''::text THEN ' '::character varying ELSE limpastring(cli.clibaient)::character varying(30) END AS clibairro, CASE WHEN mun.muncod IS NULL OR mun.muncod = 0 THEN 0 ELSE mun.muncod END AS climuncod, CASE WHEN mun.mundes IS NULL OR mun.mundes::text = ''::text THEN ' '::character varying ELSE limpastring(mun.mundes)::character varying(30) END AS climundes, CASE WHEN mun.munufsigla IS NULL OR mun.munufsigla = ''::bpchar THEN ' '::bpchar ELSE mun.munufsigla END AS cliuf, CASE WHEN cli.clicepent IS NULL OR cli.clicepent::text = ''::text THEN ' '::character varying ELSE limpastring(cli.clicepent::character varying)::character varying(10) END AS clicep, CASE WHEN cli.clifoneent IS NULL OR cli.clifoneent::text = ''::text THEN '---'::character varying ELSE limpastring(cli.clifoneent) END AS clifone, CASE WHEN cli.clifaxent IS NULL OR cli.clifaxent::text = ''::text THEN CASE WHEN cli.clifoneent IS NULL OR cli.clifoneent::text = ''::text THEN '---'::character varying ELSE limpastring(cli.clifoneent) END ELSE limpastring(cli.clifaxent) END AS clifax, cli.clibloq, cli.clibloqobs, COALESCE(now()::date - cliposit.clidtuc, 0) AS clidsv, 'Z'::bpchar AS clirank, CASE WHEN tc.tctdoc IS NULL OR tc.tctdoc = ''::bpchar THEN 'N/A'::bpchar ELSE tc.tctdoc END AS clicob, CASE WHEN cli.clipz IS NULL THEN 0 ELSE cli.clipz END AS clipzmax, CASE WHEN cli.cliloccod IS NULL THEN 0 ELSE cli.cliloccod END AS cligrupo1cod, CASE WHEN loc.locdes IS NULL THEN ' '::character varying ELSE limpastring(loc.locdes) END AS cligrupo1des, loc.loccod AS cliloccod, CASE WHEN loc.locdes IS NULL THEN ' '::character varying ELSE limpastring(loc.locdes) END AS clilocdes, CASE WHEN cliposit.clidtuc IS NULL THEN '1900-01-01'::date ELSE cliposit.clidtuc END AS clidtuc, COALESCE(now()::date - cliposit.clidtuc, 0) AS clidiasuc, 0 AS clivluc, cli.cli_plano_pagamento, cli.cliracod, rca.rcasupparcod, cli.clilim, par.paremail AS cliemail, COALESCE(cli.cli_simplesnacional, 'N'::bpchar) AS clisimplesnacional, COALESCE(par.parcontricms, 'S'::bpchar) AS clicontricms FROM rca JOIN ( SELECT cli_1.clircaparcod AS cr_rcaparcod, cli_1.cliparcod AS cr_cliparcod FROM cli cli_1 UNION SELECT clirca.clircarcaparcod AS cr_rcaparcod, clirca.clircacliparcod AS cr_cliparcod FROM clirca) cr ON rca.rcaparcod = cr.cr_rcaparcod JOIN cli ON cr.cr_cliparcod = cli.cliparcod JOIN par ON cli.cliparcod = par.parcod AND par.parsit = 'A'::bpchar JOIN mun ON mun.muncod = par.parmuncod JOIN ra ON ra.racod = cli.cliracod JOIN loc ON loc.loccod = cli.cliloccod JOIN par rcapar ON rcapar.parcod = rca.rcaparcod LEFT JOIN clitc ON clitc.clitccliparcod = cli.cliparcod AND clitc.clitcseq = 1 LEFT JOIN tc ON tc.tccod = clitc.clitctccod LEFT JOIN ( SELECT nf.nfparcod AS cli_posit_cod, max(nf.nfcadhorario::date) AS clidtuc FROM nf GROUP BY nf.nfparcod) cliposit ON cliposit.cli_posit_cod = cli.cliparcod WHERE rca.rcaav = 'S'::bpchar AND rcapar.parsit = 'A'::bpchar AND CASE WHEN rca.rca_usa_roterizacao::text = 'S'::text THEN CASE WHEN cli.cli_tipo_visita = 0 THEN CASE WHEN cli.cli_ate_dom::text = 'S'::text AND date_part('dow'::text, now()) = 0::double precision THEN 1 = 1 WHEN cli.cli_ate_seg::text = 'S'::text AND date_part('dow'::text, now()) = 1::double precision THEN 1 = 1 WHEN cli.cli_ate_ter::text = 'S'::text AND date_part('dow'::text, now()) = 2::double precision THEN 1 = 1 WHEN cli.cli_ate_qua::text = 'S'::text AND date_part('dow'::text, now()) = 3::double precision THEN 1 = 1 WHEN cli.cli_ate_qui::text = 'S'::text AND date_part('dow'::text, now()) = 4::double precision THEN 1 = 1 WHEN cli.cli_ate_sex::text = 'S'::text AND date_part('dow'::text, now()) = 5::double precision THEN 1 = 1 WHEN cli.cli_ate_sab::text = 'S'::text AND date_part('dow'::text, now()) = 6::double precision THEN 1 = 1 ELSE NULL::boolean END WHEN cli.cli_tipo_visita = 1 AND mod(date_part('week'::text, now())::numeric, 2::numeric) <> 0::numeric THEN CASE WHEN cli.cli_ate_dom::text = 'S'::text AND date_part('dow'::text, now()) = 0::double precision THEN 1 = 1 WHEN cli.cli_ate_seg::text = 'S'::text AND date_part('dow'::text, now()) = 1::double precision THEN 1 = 1 WHEN cli.cli_ate_ter::text = 'S'::text AND date_part('dow'::text, now()) = 2::double precision THEN 1 = 1 WHEN cli.cli_ate_qua::text = 'S'::text AND date_part('dow'::text, now()) = 3::double precision THEN 1 = 1 WHEN cli.cli_ate_qui::text = 'S'::text AND date_part('dow'::text, now()) = 4::double precision THEN 1 = 1 WHEN cli.cli_ate_sex::text = 'S'::text AND date_part('dow'::text, now()) = 5::double precision THEN 1 = 1 WHEN cli.cli_ate_sab::text = 'S'::text AND date_part('dow'::text, now()) = 6::double precision THEN 1 = 1 ELSE NULL::boolean END WHEN cli.cli_tipo_visita = 2 AND mod(date_part('week'::text, now())::numeric, 2::numeric) = 0::numeric THEN CASE WHEN cli.cli_ate_dom::text = 'S'::text AND date_part('dow'::text, now()) = 0::double precision THEN 1 = 1 WHEN cli.cli_ate_seg::text = 'S'::text AND date_part('dow'::text, now()) = 1::double precision THEN 1 = 1 WHEN cli.cli_ate_ter::text = 'S'::text AND date_part('dow'::text, now()) = 2::double precision THEN 1 = 1 WHEN cli.cli_ate_qua::text = 'S'::text AND date_part('dow'::text, now()) = 3::double precision THEN 1 = 1 WHEN cli.cli_ate_qui::text = 'S'::text AND date_part('dow'::text, now()) = 4::double precision THEN 1 = 1 WHEN cli.cli_ate_sex::text = 'S'::text AND date_part('dow'::text, now()) = 5::double precision THEN 1 = 1 WHEN cli.cli_ate_sab::text = 'S'::text AND date_part('dow'::text, now()) = 6::double precision THEN 1 = 1 ELSE NULL::boolean END ELSE NULL::boolean END ELSE 1 = 1 END ORDER BY rca.rcaparcod, par.pardes; -- Permissions ALTER TABLE ipedi.vw_cliente OWNER TO postgres; GRANT ALL ON TABLE ipedi.vw_cliente TO postgres; GRANT SELECT ON TABLE ipedi.vw_cliente 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,'2025-01-15','now()',null) where dc_rca_cod = 4700 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 17/05/24 - David: Não calcular o bonus para produtos em promoção e desc por qtde 31/05/24 - David: tratar bonus para que seja somado caso de aumento mesmo se tiver promoção ou descqtde 15/01/24 - David: Corrigir case que trata o dc_pv_vldebcred 29/01/25 - David: Alterar bonus_pv_cred_vl para não ser considerado caso não tenha aumento no preco */ 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)-coalesce(pv_vl_bo,0)-coalesce(pv_vl_tr,0)) else (coalesce(pv_vl,0)-coalesce(pv_basedebcred,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(case when ((pvidescqtdecod = 0 or pvipromcod = 0) and bonus_pv_cred_vl <= 0)then 0 else bonus_pv_cred_vl end) 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, coalesce(pvidescqtdecod,0) as pvidescqtdecod, coalesce(pvipromcod,0) as pvipromcod, 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 left join pv on pv_cod = pvcod left join pvi on pvcod = pvipvcod and pv_pro_cod = pviprocod and pv_uve_cod = pviuvecod 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 --and (pvipromcod is null or pvipromcod = 0) --and (pvidescqtdecod is null or pvidescqtdecod = 0) group by pv_rca_cod,pv_cod,pvidescqtdecod,pvipromcod)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(case when ((pvidescqtdecod = 0 or pvipromcod = 0) and bonus_nf_cred_vl <= 0)then 0 else bonus_nf_cred_vl end) 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, coalesce(pvidescqtdecod,0) as pvidescqtdecod, coalesce(pvipromcod,0) as pvipromcod, 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 nf on fat_nf_cod = nfcod left join pv on nfpvcod = pvcod left join pvi on pvcod = pvipvcod and fat_pro_cod = pviprocod and fat_uve_cod = pviuvecod 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 --and (pvipromcod is null or pvipromcod = 0) --and (pvidescqtdecod is null or pvidescqtdecod = 0) group by fat_rca_cod,fat_nf_cod,pvidescqtdecod,pvipromcod)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; ------------------------------------------------------------------------------------------------