update conf set confrelease = '13201';
------------------------------------------------------------------------------------------------

-- 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

*/



--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_vbasecalcnumeric(14,4);

v_vissqnnumeric(14,4);

v_efetauto 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

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

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



select trim(confobsnfs) into v_confobsnfs from conf where confempparcod=p_empresa;

EXECUTE coalesce(v_confobsnfs,'select null') INTO v_obsnf USING p_servcod;



--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;



--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,

'CRC',--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

--*************************************************************

select empresa,layout,numero,serie into rec_seqnf from f_seqnf(rec_pvserv.servempparcod,rec_pvserv.servopnfcod);



--*************************************************************

-- 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;

update serv set servsta='FT',servacao = 'FATURAR' where servcod=rec_pvserv.servcod and servempparcod=rec_pvserv.servempparcod;



--*************************************************************

-- 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.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;



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;

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------